Sqlite3Explorer

Download here.

Get Latest changes here


OVERVIEW

This is a simple program for managing sqlite3 databases. It requires the latest version of sqlite3.dll, at least 3.2.7. I do not have time to write full documentation,sorry. So, here are a few pointers to its usage,and the more "cryptic" aspects of it.

Opening databases

SQ3E assumes that databases have the extension .DB3, but will be happy to open any other extension, it is just the opendialog that assumes that. It will also open databases in memory. It accepts a single command-line parameter, which is the path to the database file to open. so, by associating your favorite extension with the program (via open-with), you can double-click it and sq3e runs. make sure it can find sqlite3.dll somewhere on the path.

Beware : it will NOT open version 2 databases. There is a separate version of sqlite for version 2 databases, which is (slightly) different.

Schema

The left hand side shows a schema in tree-view mode. You have a number of right-mouse actions on the schema items. Most actions require that you select a table, a view or an index. Experiment, using common sense. Note that the schema is not automatically refreshed, so if you make changes to the database make sure that you refresh the schema, by hitting F5. The only exception is "Design table", which does this automatically before executing, to make sure it has current info. "Design table" only works for modifying tables, not creating them, yet. It presents you with the original create statement, allows you to modify it, and then makes a best effort to convert your table to the new format, without losing the data. One case it cannot handle is the deletion of a column, and the insertion of a new column AT THE SAME TIME. in that case, it will assume you are renaming the column, and produce the wrong results. Get a backback first!

In version 2.00, you can also do an ALTER TABLE ADD COLUMN action from the right-click menu.

Edit menu

"copy result to clipboard" copies the current result set, in TAB-separated format, so that it can be pasted into Excel directly.
"Copy Database schema to clipboard" Exports an XML representation of the current schema to the clipboard.
"Create insert statements for Result set" creates and pastes text that, if executed against a database, will enter the same records that you see in the Grid (the result set). This is not the same as Exporting, because it only exports what you see, not a whole table.

View menu

Obvious stuff here.

Actions menu

The "run" action (F9) will execute the *selected text*, if there is one, or the whole text, if there is no selection. This is useful, because you can use the text pane as a sort of "history". you type an sql statement, select it with the mouse and hit F9. then, you type some more (without erasing the first), select it again and hit F9. the first text is still there to be executed again, if needed.

You can also re-encrypt a database, if you have the encrypting version of the sqlite3.dll

Options menu

"Auto Display result set" displays the results on the RHS grid, if the option "Auto display result set" is not checked. Why? When I use the program to evaluate the efficiency of queries, I do not want the display process to add to the perceived time, so I may turn the display of the result OFF.

Here you can set various pragmas, mainly.

The "fetch size" option is interesting. SQ3E uses the new compile-step api. Thus, it can simulate "server-side" cursor logic. If this option is set to 0, when you execute a query, the whole result set is fetched from the engine to a memory copy, as the case used to be with version 2. However, if you set it to "N" anything greater that 0, it will prepare the quesy , but only STEP for this number. in this case, the RHS grid has a button "next page", that will repeatedly fetch pages of "N" rows. so, for example, the query "select * from bla" where bla has a million rows, may take a long time to run, since it will attempt to fetch and copy to memory everything. But if you set fetch size to 100, it will execute very fast, and will hold an "open" cursor to the result set. note that this is not the same as LIMIT, as it does not limit the result set, only the fetching. note also that, while having an "open" cursor, you cannot update that table, because sqlite3 holds the table LOCKED while stepping a result set.

Use of datatypes

SQ3E contains a sophisticated resolver that allows you to update "typed" tables, using extensive validations. It can also work in "untyped" mode, but you will lose some of the grid functionality. The typing is controlled by the "Format Settings" menu selection.

When working in "typed" mode, set the "data types" to eirher "Standard" or "Custom". In Custom mode, the program will show you the type map it uses to recognize datatypes, and the method of recogntion. Basically, it works like this. The type map is an array of strings, a recognition rule (the pattern kind), and a resulting type.

The most important element here is the Pattern kind. It can have the following values:

PkExact = the string must much the declared type exactly (except for case)
PkSubStr= the string must be a substring of the declared type
PkRegExp=the string is a regular expression that must much the declared data type

The program uses this map , from top to bottom, to find a match for a declared type. The first match stops the loop, so be careful with ordering the definitions. Place the exact definitions FIRST in the map.
The last row of the map serves as the default type map, and its text does not matter, and is not actually matched against anything.  If a declaration does not match any of the (n-1) entries, it will default to the last one.

Example:

Given the map :

"Date" pkExact    ftDateTime
"char"   pkSubStr ftString
"int"    pkSubStr    ftInteger
"ignore" pksubstr ftString

the following declaration

Create table x(
id integer,
d1 charDate, -- this is parsed as char, because the defintion of date is "exact"
d2 Date,
t3 foo           -- this is parsed as ftString because of the last line of the map
)

will result in a table with an Integer, a character, and a Date column.
 

String types (ftString) allow you to declare a size, as in VARCHAR(20), TEXT(10) etc

Datatypes are important, because they govern how SQ3E interprets the data in the db, and also how it will update them., by generating the correct BIND statements, and parameters for them.

You can save the datatype map in the database, by checking the relevant checkbox, and the next time the progra,m opens the database, it will reload the format settings. It does this by creating a table named "z_sqlite3Explorer_options". Dropping the table will result in clearing the stored format settings.

If you choose "Standard DataTypes", the program will use an internal default map. If you want to see what this is, switch momentarily to "Custom", and press "Defaults".

IF you work in untyped mode, set the format to "Map All to VarChar", and all fields will be displayed and updated as strings. This is NOT a good idea, if *your* program accesses the database in typed mode, i.e it used the BIND apis.

Note that, when datatypes are used, the program will use the BIND apis to store the data in its native form. This may have implications with existing data. for example, if you have stored DOUBLES as text in an existing database, and you open them with SQ3E, it will try to interpet the text as floating point numbers, which may fail depending on the format that you stored, and the current regional settings (decimal points etc). so, if you get unexplained errors with existing data, switch datatypes off.

There is a built-in simple blob editor. edit blobs by double-clicking the "Blob" token in the grid. type blobs as hex values, separated by a single space.

CLOBS are treated as "memo" fields, stored as text, but edited in a pop-up, like BLOBS.

DATETIME SUPPORT

This is one of the most controversial areas of sqlite3, and consequently of SQ3E. Since sqlite3 does not define a standard way of storing dates, programmers can choose what to do with this. The most usual choices are:

SQ3E can parse and update all of the above formats, with the appropriate format settings. Experiment until you get the correct dates displayed. If you can see your dates, chances are you can also update them via the grid.

Note on date support:

If you choose to store dates as NUMBERS (of any format as above), as opposed to TEXT, you should also tell sqlite3 that this is so, to avoid unnecessary and sometimes dangereous conversion to/from text. so, if you use numbers, declare your dates as type DATE, which tells sqlite3 to use numeric affinity, and tells SQ3E to use a datetime presentation. If you use text, declare them as DATETEXT, which will tell sqlite3 that storage affinity is TEXT (because of the TEXT substring), and tells SQ3E that these are dates anyway (the substring DATE, assuming standard type mapping). You still need to set the corresponding format in the SQ3E settings, though, it will not deduce it from the type declaration.

BUILT-IN Functions

SQ3E registers a number of user functions.

UPPER replaces the standard UPPER with its ANSI equivalent, so it works for non-US text. Its functionality depends on your active code page.

same for LOWER

SUBSTRING works like substr, but the name is compatible with MS SQL server.

Note: since version 1.3, all date/time functions can take either canonical date strings or floats as their arguments, and will return either floats or strings, depending on the dateStyle setting.

mkNOW() returns the current date and time.

mkDATE() returns the date part only

mkTime() returns the time part only.

mkJulian() takes a windows(Delphi) floating-point date, and converts to julian date.

SQ3E also registers a couple of collating sequences:

ANSINOCASE uses ANSI-case insensitive (code-page dependent) MIXED uses a weird Greek/English collating sequence, useful for nobody outside Greece.

But, beware that creating tables with these collating sequences will make these tables non-functional outside of this program, so avoid this! you *can* use them, however, as temporary overrides in ORDER BY clauses etc.

Finally, SQ3E registers a RegExp handler, which is the PERL regular expression parser, in case-insensitive mode.

Query Builder

There is a visual query builder. This is not my work, so I cannot vouch as to its functionality. it seems to work reasonably well in most cases. Experiment with it.

Report Generator

SSQ3E interfaces with a report generator created with FastReports. From version 2.0, the report generator is distributed as a separate DLL, to keep the distribution size low. If you plan to create reports, get the dll from here, and put it in the path, or in the same directory as SQ3E.

Usage is simple:

Version 2.x Compatibility

The "old" 2.x-compatible version is no longer under development. You can still pick up a copy here. It should be stable, and work with version 2.x dlls

THATS ALL

Enjoy! I can provide simple support, via the sqlite3 mailing list. pls dont ask for too much...