Get SQLite files Properties, Schema and contents

[Emile_Schwarz]

Hi all,

Like anyone else, I needed a tool to check what’s inside my own SQLite database file.

Then, at the end of june, I’ve made an error while creating the Table in one of my projects and was very upset because the symptom was the wrong encoding (if I can tell that).

A string Field was wrongly declared as INTEGER. Go figure.

Yes, it took me too much time to figure it. So, I started to think at this tool.

Then, one day I’ve made an extensive search on the internet and build this tool on June 1st (2015). But I do not knew how to get the Tables declarations that appears in each .sqlite file.

Then, today a kind soul (jean-yves pochez) send me in the correct direction and I found how to do that.

This is why I share this application only today, nearly two months after writing it.

You can use it with whatever .sqlite file you have, sqlite files fom the Xojo Examples folder or better, the Firefox downloads.sqlite * file !

Be sure to ckeck the information you will get about your .sqlite file in the other window !

Enjoy it,

Emile

Of course, you have to use Firefox, and have entries in the History window (the window that display the downloading progress).

To find the file in OS X: Make a search fo “downloads.sqlite” (without quotes) including the System files.

For Windows users: sorry, I do not know. But if one knows, please share.

Here you will find the two applications (OS X and Windows).

Oh ! The file with .app in its name is the OS X version; the other zip have Windows in its name.

[Emile_Schwarz]

BTW: Iif you fall into a trap, get a bug, whatever I can modify… Express yourself !

I will be happy to make the needed changes.

TIA

[Eli_Ott]

@210543:@Emile Schwarz

A string Field was wrongly declared as INTEGER.

You can store any string in an integer field in SQLite and it will work. See the docs (Datatypes In SQLite Version 3):

Any column in an SQLite version 3 database, except an INTEGER PRIMARY KEY column, may be used to store a value of any storage class.

[Eli_Ott]

[quote=210543:@Emile Schwarz]I do not knew how to get the Tables declarations that appears in each .sqlite file.[/quote]The Xojo database class has methods to gather information about the database structure:

Database.TableSchema() As RecordSet Database.FieldSchema (tableName as String) As RecordSet Database.IndexSchema (tableName as String) As RecordSet
So what you want to do is:

Dim allTableNames As RecordSet = YourDatabase.TableSchema() Do Until allTablenames.EOF System.DebugLog allTablenames.Field("TableName").StringValue allTablenames.MoveNext() Loop

[Emile_Schwarz]

@210552:@Eli Ott

You can store any string in an integer field in SQLite and it will work. See the docs (Datatypes In SQLite Version 3 ):

That is what I’ve done, but once the Fields are read and added to a ListBox (AddRow), there was an encoding issue.

That said, why will one would store strings in an INTEGER field ?

[Emile_Schwarz]

@210553:@Eli Ott

The Xojo database class has methods to gather information about the database structure:

Yes, I know, but that is not what I’ve done in the last Table (the table name which is always the same).

I read the sql string directly from the .sqlite file: I do not build it using the *Schema instructions.

I use the *Schema for getting all Tables names (excepted the Table called sqlite_master): I do a simple AddRow "sqlite_master".

To be sure, load a .sqlite file of yours and check it (with the colored window).
You may also double check using .sqlite files from the Xojo Example folder or create a brand new one and take a look at it.

Have fun…

BTW: did you checked what you get with the second window ?
(SQLite File Infiormation

PS: Here is the sql code Firefox used to create their downloads.sqlite file

“CREATE TABLE moz_downloads (id INTEGER PRIMARY KEY, name TEXT, source TEXT, target TEXT, tempPath TEXT, startTime INTEGER, endTime INTEGER, state INTEGER, referrer TEXT, entityID TEXT, currBytes INTEGER NOT NULL DEFAULT 0, maxBytes INTEGER NOT NULL DEFAULT -1, mimeType TEXT, preferredApplication TEXT, preferredAction INTEGER NOT NULL DEFAULT 0, autoResume INTEGER NOT NULL DEFAULT 0)”