S SQLite Expert
Changelog

Version history

Release History

This page provides a summary of changes to SQLite Expert.

Version 6.2

  • Index Advisor on the SQL tab: SQLite’s own advisor suggests indexes that would speed up a query, and shows the resulting plan.
  • Query History (SQL menu): every script run on the SQL tab, searchable, and reopened with a double-click.
  • One Find window for the current grid (Ctrl+F, SQL results included), a table, or a whole database (Find in the tree). Contains, whole value, starts with or regular expression; match case; whole words; chosen columns. All matches are listed and highlighted; Go to Row opens the row.
  • Recent searches are remembered. Esc or Clear Highlight removes the highlighting from a grid.
  • Shorter tree and grid menus: related commands are grouped under submenus.
  • Imports that create a table have a Column types option: descriptive types without sizes (the new default), STRICT type names, a STRICT table, or the old types with sizes. Floating-point columns are declared REAL.
  • Imports store a value longer than its column’s declared size whole, and the text import stores yes/no as 1 or 0.
  • The sample database uses SQLite’s type names (TEXT, REAL) without sizes.
  • The Record Editor sizes each box to its value; drag the strip under a box to resize it.
  • A new row appears in the grid at once, wherever it lands, and the cursor stays on it.
  • The Value Inspector opens on a value that does not match its declared type, and the text editor saves such a value as stored.
  • A tick box in a nullable column cycles through unchecked, null and checked.
  • Ctrl+C while editing a cell on the Design tab copies the selected text, not the row.
  • Fixed: an error on closing a database; a conversion error on arrow keys in yes/no and date cells; a blank row for a column added on the Design tab; Options failing on its second opening, and not reopening where it was closed.

Version 6.1

  • The Database page is now five pages: Databases, Connection, Functions, Collations and Modules. Connection carries compile_options, the definitive answer to what the loaded SQLite library was built with; Functions lists every routine the connection answers to by name, including anything a loaded extension registered.
  • The table designer names the views and triggers that use a column you are removing, before the change is applied rather than after it has quietly broken them.
  • Two new limits under Tools → Options → Grid contents: Max TEXT size to show in grids bounds how much of a long value is drawn, and Max cell height in grids bounds how tall one cell can make a row.
  • Options that were check boxes on forms and dialogs are now switches. Grids keep their check boxes.
  • A database now opens even when an extension loaded automatically fails to initialise, and the message says which extension and why. The extensions list names the database each one was left out of.
  • Compare follows the database selected in the tree when the pair it remembered is no longer open, instead of starting with neither side chosen.
  • The Options window’s page list keeps the keyboard, so the pages can be walked with the arrow keys after one is clicked.

Version 6.0

  • A new Diagram page draws the whole database as an entity-relationship diagram: a box per table, view and virtual table, and a line between the columns each foreign key joins, marked with the cardinality worked out from the keys. Show table names alone, their key columns, or every column; arrange it automatically or place the tables yourself and the layout comes back with the file. It can be saved as a picture, exported to PDF tiled or as a single large page, and printed. A view or a virtual table is drawn in italic, with a tag naming what it is — View, or the module behind a virtual table, since fts5 says more about a box than “virtual table” would. Professional Edition.
  • A file can now be imported into a table: JSON, XML, an HTML page, or a spreadsheet in ODS, XLSX or XLS form. The destination is a new table, created from the columns the file was found to have, or an existing one, matched by name. One dialog reads all four and shows what it found before anything is written — the columns, their types and how many rows — and says whether the file stated its columns or they were worked out from the values. A file holding more than one table offers them by name. Professional Edition.
  • The JSON export asks what to do with a value that is itself JSON. Written as a string it comes out escaped, which is exactly what the column holds and reads like nothing anybody wanted; it can now be embedded in the file instead, so a JSON column reads as JSON. This covers a JSONB column and text holding a document alike. The same dialog carries the two options that were already there underneath: whether BLOB columns are included, and whether the file is formatted.
  • The HTML and XML exports each write two genuinely different files, and now let you choose. The styled and formatted routes follow the grid, carrying the columns you can see, laid out as you see them; the data routes follow the dataset, carrying every column with its type — and the XML data route is the one the new import reads back.
  • The export dialogs ask for the file name on the same form as the options, the way the text export always has, instead of in a Save dialog first.
  • Every export can show you the file it wrote, if you ask it to: Tools → Options → Data → Open the file after exporting, off by default. A file type with nothing registered against it opens its folder with the file selected, rather than Windows’ “How do you want to open this file?” picker.
  • The Options dialog has a Values page. How dates and times, booleans and GUIDs are stored moved off the Data page onto one of their own — three groups that answer the same question, and a Data page short enough to read without the dialog growing to fit it.
  • Clearing an encryption key is confirmed. An empty key at the Set/Change encryption key prompt removes the encryption, leaving the file readable by anyone who has it, so it now asks first and defaults to No. It asks only where a key is actually being taken away.
  • The database tree’s pop-up menu folds up: the extension commands under Extensions, and the import and transfer commands under Import/Export. It had grown long enough to run off the bottom of the screen.
  • Arithmetic that overflows answers the way SQLite means it to. select 9.0e+307 * 10.0, select exp(1000.0) and an avg() over very large values each reported “Floating point overflow” instead of returning an infinity: SQLite is C, where an overflow yields infinity rather than an error, and the trap Delphi arms on every thread was catching it on the way out.
  • Redesigned, rewritten help, now HTML-based, searchable and easier to read.
  • Updated the SQL parser and code generation to support and take advantage of the latest SQLite features.
  • Many fixes for issues uncovered by an extensive code review and a new unit-test suite.
  • Lua print now behaves as it does in any other Lua interpreter: arguments are tab-separated, the line is terminated, and values of every type are rendered properly. io.write writes without separators or a newline. Older scripts that end a print with an explicit "\n" argument still work, but now produce an extra blank line.
  • Fixed the scripting Output tab, which could show multi-argument print output in reverse order and silently drop repeated output.
  • Fixed FileAge in Pascal scripts, which was published with a TDateTime parameter but bound internally to a different function. The date must now be received in a variable declared TDateTime; earlier versions accepted only a Double.
  • Light and dark themes, with a mode picker on the toolbar. System follows the Windows setting as it changes, and is what a new installation starts on; each mode has a theme of its own, chosen once under Tools → Options → Themes. Two new themes ship with this release: DeepWhite, which a new installation uses for light mode, and DeepBlack for dark.
  • The bundled dbdemos sample database now installs under the shared application data folder rather than beside the program. Scripts that referred to the old location need their path updated.
  • A new Convert to STRICT command on one or more selected tables. A STRICT table refuses values that do not match its columns’ declared types instead of converting them, so the command is a wizard that reads the data first and shows what a conversion would do to it: the type each column would take, and how many rows would be refused, kept under a different type, or come back changed. Each column is offered the type that changes nothing, and ANY where no such type exists — a DECIMAL column holding both whole numbers and fractions, for instance. Nothing is altered until the last step, which shows the SQL before running it.
  • Exported data now says what a cell holds. SQLite does not enforce declared types, so a column declared INTEGER can hold the text "seven"; every export format wrote such a value as the conversion's default instead — 0, or a zero date — and an SQL script exported that way was not a copy of the table it came from. All formats now write the stored value, and a replayed script reproduces the storage classes of the original.
  • A column declared BOOLEAN holding text that is not a boolean now shows that text rather than an unticked checkbox, which stated a false value nobody had established and looked no different from a stored 0. Which text counts as TRUE and FALSE can be set under Tools → Options → Data → Boolean; the defaults are what earlier versions recognised.
  • Added JSON and JSONB to the type mappings. JSONB is SQLite's binary form of a JSON document, so a column declared JSONB is now read as a blob rather than as text, which also lets Show JSONB as readable JSON apply to it.
  • A table created from data rather than from SQL — by a text import, by the transfer wizard, or by a script — now declares its text columns TEXT rather than CHAR. CHAR(n) means fixed-width in standard SQL and these columns are not that; SQLite gives both names the same affinity, so nothing behaves differently. Existing databases are untouched.
  • A blob stored in a column declared as text — which SQLite allows, and which a JSONB value in a JSON column is — used to leave an empty cell in the grid, no different from an empty string, and was dropped altogether by the CSV, TSV, HTML and XML exports. It now reads (blob) in the grid, and each format writes it the way it writes any other blob.
  • An integer larger than 253 in a column read as a number — DECIMAL and NUMERIC columns keep whole numbers as integers — was shown and exported as the nearest value a floating-point number can hold: 9007199254740993 became 9007199254740992, and permanently so in an exported script. Such a value is now shown and written exactly.
  • Fixed: sorting a large table by clicking a column header left the pointer spinning for the rest of the session.
  • Fixed: a primary key declared as a table constraint with AUTOINCREMENT — PRIMARY KEY ("id" AUTOINCREMENT) — is now read as the column it names. It had been shown on the designer’s Primary Key page under Expression with the column name blank, and the column was not marked as part of the key.

Version 5.5

  • Added FastSpring integration.
  • Added parser support for latest SQLite features.
  • Added support for Windows 64-bit on ARM64 architecture.
  • Added on-demand initialization of the printing system.

Version 5.4

  • Added parser support for latest SQLite features.
  • Various bug fixes and performance enhancements.

Version 5.3

  • Added support for UPSERT clause.

Version 5.2

  • Added support for Unix datetime.
  • Added "Max BLOB size" option to limit the size of BLOB values that are loaded in the grid.
  • Added option to install generic collations on demand.

Version 5.1

  • Added "Run sqlite3_analyzer.exe" option.
  • Added support for BETWEEN and IN in filter expressions.
  • Added support for high DPI monitors.

Version 5.0

  • Due to user feedback, we have decided to bring back the DevExpress components used in version 3.x. This fixes some issues related to displaying and editing data in the grid, and improves the overall look and feel of the application by replacing the VCL Styles with the DevExpress Skin Library.
  • Added data export to JSON.

Version 4.2

  • Switched to subscription model.
  • Numerous bug fixes.

Version 4.1

  • Improved usability for table editor based on user feedback.

Version 4.0

  • Both Win32 and Win64 versions.
  • Redesigned table/view/virtual table editors, using a new recursive-descent SQL parser based on COCO/R.
  • Separate editors for table and column constraints.
  • The change script is available before applying changes to a database object.
  • Experimental SQL formatter on the SQL and DDL tabs.
  • Database schema compare.
  • Settings are now stored in a JSON file.

Version 3.5

  • Improved license registration process.
  • Added support for partial indexes.
  • Added support for tables without rowid.

Version 3.4

  • Updated some third party components.

Version 3.3

  • Added support for virtual tables including RTREE, FTS3 and FTS4.
  • Added database DDL compare tool.
  • Added database online backup option.
  • Added support for the ICU extension.

Version 3.2

  • Added database repair function.

Version 3.1

  • Added option to open multiple SQL tabs.
  • Added tab width option.
  • Added keyboard shortcut (CTRL+/) for commenting/uncommenting selected text in SQL and script editors.

Version 3.0

  • Added skin library.
  • Added printing library.
  • Added support for custom delimiters when exporting/importing to/from text files.
  • Added option to allow only one instance of the application.
  • Grid rows now resize height with the size of the text.

Version 2.4

  • Added option to hide tabs and menu items to prevent bloating the user interface.
  • Added code completion.
  • Added record editor.

Version 2.3

  • Added Find/Replace option in SQL/Lua/Pascal script editors.
  • Added option to associate custom file extensions with SQLite Expert.
  • Added ability to generate SELECT, INSERT, UPDATE and DELETE statements based on the visible fields on the current table.
  • Added option to limit the amount of memory used when displaying results as text. Default is 10 MB.
  • Added option to display the "rowid" on the Data tab.
  • Added Extensions tab.
  • Redesigned the Database tab to display or set pragma properties for all attached databases.

Version 2.2

  • Added "Reindex Table" and "Reindex All Tables" options.
  • Added support for auto extensions.
  • Added foreign key support.

Version 2.1

  • Added thumb tracking option for grid scrollbars.
  • Added options to set the selected cells to NULL or to a specific value.
  • Enabled editing triggers and constraints in Personal Edition.
  • Enabled cell select mode. Added option to switch between cell select and row select modes.
  • Modified query parameter prompt to accept string parameters not enclosed in quotes.
  • Added "Generate random data" function.
  • Added support for parameters in SQL statements.
  • Added support for images imported from Access prefixed with an OLE header.
  • Enabled multiple selection for Empty Table function.
  • Enabled multiple selection on tables and views for drag-drop, copy-paste and delete operations.
  • Added support for attached databases.
  • Added support for nested transactions.
  • Added ability to copy tables between databases using the clipboard (Professional Edition).
  • Added ability to copy tables between databases drag and drop (Professional Edition).
  • Applying changes when restructuring a table or a view now uses a nested transaction.

Version 2.0

  • Added ability to Copy/Paste data, fields, indexes, constraints and triggers using the clipboard.
  • Added support for viewing and editing GIF images in image editor.
  • Added support for displaying GIF images in grids.
  • Added encoding and save preamble options when exporting to SQL script.
  • Added encoding detection when importing from SQL script.
  • Added save preamble option when exporting to text file.
  • Added option to remove the "dbo." prefix when importing from ADO.
  • Added Shift-F5 shortcut to execute the current line of SQL.
  • Improved Unicode support.
  • More encoding options on import/export from/to text files.
  • An encoding detection when importing a text file has been included that seems to work reasonably well in most cases.
  • Statically linked sqlite library is included.

Version 1.7

  • Added support for .ICO graphic format.
  • Added .sqlite file association.
  • Enabled word-wrapping on all SQL editors.
  • Added "Close all databases" menu option.
  • Added option to cancel import from CSV files.
  • Added option to switch between large and small toolbar icons.
  • Added support for encrypted databases.
  • Added "Schema" tab (Professional Edition).
  • Added global option to display or hide record numbers (in Tools/Options/Data).
  • Added option to show or hide filter rows on grids.
  • Added export to XML (experimental).
  • Enabled export functions for all grids.
  • Added type mapping BLOB_TEXT -> Blob.
  • Added grid filter option (experimental).
  • Added file association option.
  • Added "show cell hints" option.
  • Added custom themes support.
  • New option: auto check for updates.
  • New option: remember open databases.
  • New option: select grid background color.
  • New option: get record count for queries.

Version 1.6

  • Added label that displays whether a query is editable and which key fields are being used.
  • Added option for quoting identifiers with square brackets or double quotes.
  • Added "Empty table" menu option.
  • Added standard menu items (cut/copy/paste/delete/select all) to all the edit windows.
  • Added "Delete record" menu items to grid popup menu.
  • Program now remembers the directory where the last SQL script was saved.
  • Added "Clear SQL" option in SQL window pop-up menu.
  • Added "Clear script" option in script window pop-up menu.
  • Added "Export to Excel" (Professional Edition).
  • Added "Export to CSV" and "Import CSV file" (Professional Edition).
  • Added "Set to NULL" option to grid pop-up menu.

Version 1.5

  • Added UTF-8 support.
  • Added UTF-8 table translated in various languages in the demo database.
  • Added custom exception handler that displays more context information about the error.
  • Enabled multi-selection for all data grids
  • New DATE and DATETIME editors.
  • Modified the Query Builder to automatically generate field aliases. This is a workaround for a known issue in SQLite: invalid field names in views. For more information see "Known Issues" in the Help file.
  • Added Lua and Pascal scripting support.
  • Replaced the old Query Builder with Active Query Builder. The new Query Builder supports unions, sub-queries, join properties, as well as saving and loading SQL scripts.

Version 1.4

  • Added "Generate HTML Report" option in grid popup menu (Professional Edition).

Version 1.3

  • Added "Reopen Database..." menu item.
  • Grid column widths now adjust dynamically to text length.
  • Changed the option to limit the number of records to be enabled by default.
  • Changed the internal handling of date and time fields to recognize the data stored in the format 'YYYY-MM-DDTHH:MM:SS.SSS' as described in the SQLite documentation. The date and time fields will be stored in this format starting with version 1.3.2. However, for compatibility with older versions, the program will recognize date and time fields stored in the old Delphi TDateTime format (floating point). The integral part of a Delphi TDateTime value is the number of days that have passed since 12/30/1899. The fractional part of the TDateTime value is fraction of a 24 hour day that has elapsed.
  • Added "Select columns" dialog for data grids.
  • Changed data grids implementation to use a sliding window so they don't load all the data in memory, thus improving both performance and memory usage for large tables or queries.

Version 1.2

  • Added splash screen with progress bar.
  • Added support for precision in numeric fields.
  • New feature: added "Stop Query" button for interrupting the execution of long running queries.

Version 1.1

  • Added disabled icons.
  • Added support for unique and check constraints.

Version 1.0

  • Basic functionality. Edit tables and views.