Changing options

To configure SQLite Expert, choose Tools → Options from the main menu.

The settings are organized into the following groups:

• Themes — which theme is used in light mode and which in dark.
• General — the language, the look and feel, tooltips, the toolbar and its icon size, whether a second copy of the program opens its own window or reuses the first, and whether open databases and their encryption keys are remembered between sessions.
• Tree panel — what the tree shows: objects grouped by type or by attached database, how much detail each one carries, record counts, whether system and shadow tables appear at all, and whether databases are listed in order.
• Editors — auto-save, word wrap, tab width and smart tabs, for the SQL and script editors.
• Query History — whether runs on the SQL tab are recorded, whether runs against encrypted databases are, and how many are kept.
• Designer — whether the table designer shows constraint names.
• Fonts — three of them: the application font, used everywhere except the code editors; the menu font; and the editor font, which is the fixed-width one the code editors use.
• Data — whether a database opens under a default alias, read-only, and whether the connection options dialog is always shown; whether generic collations are installed automatically; whether results appear in a grid or as text; the maximum text size to display; and whether an exported file is opened once it has been written.
• Values — how a value of a particular kind is stored and read back: what DATETIME values are written as and how a numeric one is to be read, which text values mean TRUE and FALSE, and how GUIDs are stored (see Data types).
• Grids — how the data grid behaves: whether data refreshes automatically, the filter row, cell selection mode, whether an editor opens on the first keystroke, opening the record editor on double-click, whether the grid follows the scrollbar as it is dragged, cell auto-height and alignment.
• Grid contents — what a row and a cell show: record numbers, the rowid column on the Data tab, how nulls are shown (<null>), images, JSONB as readable JSON and whether that JSON is indented, hyperlinks, edit buttons, cell hints, highlighting values with a possibly incorrect storage class, the maximum BLOB size to load, and how much of a long value a cell shows — in characters and in lines.
• Code generation — how the table designer applies structural changes, keyword capitalization and quote characters for generated SQL, and whether to restore the quoting style in DDL rewritten by SQLite.
• Code completion — enable completion and auto-invoke, the auto-invoke delay, template completion, and the code templates themselves.
• SQLite library — which SQLite library DLL the program uses, with a Refresh button that reads the installation directory again so a DLL copied in since the program started can be chosen without restarting (see Using encrypted databases).
• Type mappings — how declared column types map to internal types (see Data types).
• File associations — the file extensions associated with SQLite Expert (by default .db, .db3, .sqlite, and .tooldb).
• Diagnostics — how much SQLite Expert writes down about its own behaviour so that a fault leaves evidence, and what it has written so far.
• Updates — how often SQLite Expert checks for updates.

Themes

SQLite Expert has a light mode and a dark mode, and the toolbar carries a picker to switch between them. A third choice, System, follows whatever Windows is set to, so the application turns dark when the desktop does. Every installation, new or upgraded from an earlier version, starts on System with DeepWhite for light mode and DeepBlack for dark. A skin chosen in an earlier version is not carried over, but it is still in the list; whatever you choose from then on is kept.

On System, SQLite Expert changes with Windows as it happens: turn the desktop dark while it is open and it turns dark with it, without being restarted and without losing what you were doing. Choosing Light or Dark pins it to that mode whatever Windows is set to.

Each mode has a theme of its own, and this page is where the two are chosen:

The point of setting them separately is that switching mode is then a single choice rather than two. Pick a light theme and a dark theme once, and the mode decides which is in use; the light one is never offered while you are choosing a dark one, because each list only holds the themes that belong to its mode.

Both are shown with a preview — a small grid and a line of SQL, rendered in the theme — since a name like Office2019Black or WXI says little about what it looks like on screen.

Two themes are new in this version and are what every installation starts on: DeepWhite for light mode and DeepBlack for dark.

Tooltips

A tooltip names what the pointer is over wherever the name is worth having: a toolbar is a row of icons, several of them the same picture used for different commands, and a button you cannot identify is a button you will not press. So every glyph says what it is — on the toolbar, on a grid's navigator, on the buttons beside a value editor.

Where the name is already on the button it is not repeated. The Vacuum button on the Database tab is captioned Vacuum, and a tooltip saying "Vacuum" over it tells you nothing you are not already reading. The Check button beside it is captioned Check and its tooltip says Integrity Check, which is the command's full name and more than the caption: that one shows.

General → Tooltips → Explain commands in tooltips governs the sentence under the name. It is on to begin with, and it is there for the commands whose name does not say what they do: the maintenance commands on the Database menu — what Vacuum rewrites, what Repair copies and what Recover reads instead — and the SQL and transaction commands. With it on, the Vacuum button explains itself like any other; turn it off and the tooltips shorten to the names alone, or to nothing where the name was the caption.

A few controls carry an explanation and no name, because their name is already on the screen beside them — the Compare data box and the Integrity check list on the Compare window, the state of a grid on the strip beside it. Those have nothing left to show once the explanations are off, and show nothing.

Information is a third thing again, and is never turned off. On the Diagram tab a box lists its columns and a line names the two it joins, neither of which the picture itself can show.

A grid's own cell hints are a separate matter, left to Grid contents → Show Cell Hints.

A tooltip stays up for ten seconds, which is long enough to read a sentence.

How table changes are applied

Under Code Generation → Compatibility you choose how the table designer applies a structural change to a table — adding, removing or renaming a column, and everything else the Design tab can do to a table's structure.

Use ALTER TABLE when supported (recommended, faster) is the default. The edit is applied in place wherever SQLite can express it as ALTER TABLE: nothing is copied, so the change takes the same moment on a table of ten rows and one of ten million, and the table's stored definition keeps the formatting you gave it.

Where an edit has no ALTER TABLE form — reordering columns, changing a column's type, most changes to constraints — or where the SQLite library in use is too old to understand the statement, SQLite Expert restructures the table instead. That fallback is automatic, and choosing this option never risks a statement your SQLite library cannot run.

Always rebuild the table (legacy, slower) restructures the table for every structural change, whether or not ALTER TABLE could have applied it. This is how version 5 and earlier always worked. Choose it if you would rather read the one change script you are used to; expect it to be slower on large tables, because every change copies the table's whole contents.

The choice applies to structural changes only. Renaming a table on its own, and a change confined to indexes or triggers, never restructured the table under either setting.

Note

If you change this while an edit is already pending on the Design tab, the Change Script tab is regenerated to match — so what you see there is always the script that would run. The same is true of the other Code Generation settings.

Displaying results as text

Under Tools → Options → Data → Results, Display results chooses between In grid, As text and As text, one field per line. In grid is the usual view and the one the rest of this manual describes. The setting applies to the Data tab and to query results alike.

As text draws the same rows as fixed-width columns in the editor font, the way a console client would — useful for reading a wide result without dragging column edges about, and for copying a block of output as it stands. A cell holds one line of a value and at most sixty characters of it: a value spread over several lines is folded onto one — each run of spaces and line breaks becomes a single space — and anything past the width ends in ..., so a column of indented JSON reads as the beginning of each document rather than as a column of opening braces.

As text, one field per line writes each record as a block instead: a rule naming it, then one line per field. The demo database's country_json table holds each country twice, as indented JSON text and as JSONB:

Nothing is folded and nothing is cut here, because no width has to be agreed between one record and the next. A value keeps its own lines, so a stored document is shown as the document it is — which is the point of the layout. A line of the columns above is a whole record, so a value there can only ever be one line of itself at any width, and a table of documents comes out as a table of beginnings.

In either layout a blob has no text form, so its cell says what is there instead: (BMP, 38886 bytes), (blob, 118 bytes). JSONB is the exception, being the one binary form that does have a text form, and is shown as JSON — here whatever the grid option of almost the same name is set to, since that one governs painted cells only. One field per line indents it, so that a JSONB column and a JSON text column holding the same document look alike. A value left in the file by Max BLOB size to load in grids is not fetched back merely to be measured, and still reads (blob).

Boolean values stored as text

SQLite has no boolean type. TRUE and FALSE are keywords for the integers 1 and 0, and that is what a column declared BOOLEAN normally holds — a checkbox in the grid. Text can end up in one anyway, from an import or from an INSERT that quoted the value, and BOOLEAN gives the column NUMERIC affinity, so 'yes' stays text.

Under Tools → Options → Values → Boolean, two lists say which text is read as which boolean:

Setting	Default
Text values meaning TRUE	1;true;t
Text values meaning FALSE	0;false;f

Semicolon-separated, not case sensitive, surrounding spaces ignored. Add whatever your databases use — yes;y;on, ja, oui — and cells holding those words become ordinary checkboxes.

A value in neither list is not read as a boolean. Rather than an unticked box, which would assert a false nobody established, the cell shows the text it actually holds, shaded as a value whose storage class does not suit its column. That is why the defaults are narrow: an unfamiliar value is put in front of you instead of being quietly interpreted.

The lists apply only to text. A column holding the integers SQLite itself writes is unaffected. See Data types.

Show JSONB as readable JSON in the grid

A JSONB value is a blob, so a grid shows it as (blob) like any other. Turn this option on, under Tools → Options → Grid contents, and cells holding JSONB are painted as readable JSON instead.

It is off by default because it is not free: every JSONB cell that appears on screen is decoded as it is painted, which is what the "(slower)" in the option's name refers to. On a grid of small JSON values you are unlikely to notice; on large ones you may.

Two things worth knowing:

• Cells shown this way cannot be typed into directly, because what you see is a rendering of the blob rather than the value itself. Use the JSON Editor to change one.
• A value larger than Max BLOB size to load in grids is only partly loaded, and a partial JSONB value is not valid JSONB, so such a cell still shows as (blob). Raise that setting if you want to see it.

The demo database's orders_json table is a good place to try it. For querying JSON rather than looking at it, see Working with JSON.

Indent the JSON shown for JSONB values

On by default, and only meaningful while the option above is on. SQLite's json() returns a document on a single line, which reads as a long unbroken string in a cell and on a printed page. With this on, the value is laid out the way the JSON Editor shows it, so the three agree.

Turning it off is a matter of taste rather than speed. Indenting is done here rather than by SQLite and costs a small fraction of the decoding that has already happened — on a 2 KB document, 0.010 ms to indent against 0.155 ms to decode — and the laid-out text is remembered along with the decoded value, so scrolling back over a cell pays neither again.

Cells become taller when the value runs to several lines. Cell auto height, under Tools → Options → Grids, is what lets a row grow to show all of them.

Max TEXT size to show in grids

Under Tools → Options → Grid contents, and the companion to Max BLOB size to load in grids above it. That one bounds what is read out of the database; this one bounds what is drawn out of what was read.

A cell whose text is longer than this is painted up to the limit and then (...). The value itself is untouched — nothing is truncated in the database, in an export, or on the clipboard — and double-clicking the cell opens the whole of it in a window you can scroll and read.

The default is 1024 characters. There is a limit at all because a grid measures and draws every cell you can see, every time it paints one, and because Cell auto height lets a row grow to hold what its tallest cell says: without a cut-off, one cell holding a page of text becomes a row a page tall, and the rows around it are pushed off the screen. Raise it if your columns are wide enough to be worth it; the highest it goes is 100000.

Max cell height in grids

The other half of the same answer, under Tools → Options → Grid contents. The cut-off above is in characters, which bounds a row only through the width of its column: the same thousand characters is four lines in a wide column and forty in a narrow one. This one bounds the row itself.

The default is 16 lines. A row is never made taller than that to hold what a cell says, however much the cell has to say — and without a ceiling one cell decides the whole table, because a row taller than the grid is the only row there is room to show.

It only matters while Cell auto height, under Tools → Options → Grids, is on. With that off every row is one line whatever this says.

As with the cut-off, nothing is lost: the value is whole in the cell and whole in the window a double click opens. Only how much of it a row shows is bounded.

Query history

Under Tools → Options → Query History, for the query history of the SQL tab.

• Record runs — whether each run is recorded at all. On by default. The Record new runs switch in the Query History window is the same setting, so turning it off in either place turns it off in both.
• Record runs against encrypted databases — off by default. The history is a plain text file, and the values in a query can be exactly what the encryption was protecting, so a run against a database opened with a key is left out unless this is on.
• Runs to keep — how many runs the history holds, from 100 to 100000; 5000 by default. Lowering it drops the oldest runs straight away.

Open the file after exporting

Off by default, under Tools → Options → Data → Import/Export. Turn it on and every export — text, JSON, XML, HTML, Excel, ODS, SQL — shows you the file it has just written.

It is off to begin with because an export is a request to write a file rather than to open one, and doing anything further with it unasked is a decision that belongs to you.

Turned on, a file type with a program registered against it opens in that program. A type with nothing registered — .json and .sql often have nothing — opens its folder with the file selected, rather than putting Windows' "How do you want to open this file?" picker in front of you. That picker is a question about file types, and the answer you were after was the file.

Restore quoting style in DDL rewritten by SQLite

Under Code Generation → Quote identifiers you choose whether SQLite Expert writes identifiers as [name] or "name". That setting governs the SQL that SQLite Expert generates — but not everything in your database is written by SQLite Expert.

When you rename a column, SQLite updates the stored CREATE statement of every object that refers to it: the table itself, its indexes and triggers, and any view or trigger elsewhere that mentions the column. Those are the statements SQLite Expert shows you as an object's DDL, and SQLite always writes the substituted name in double quotes. So after a rename, part of your schema reads in SQLite's style rather than yours.

Turn this option on and SQLite Expert reads those statements back once the rename has been applied and restores your quoting style in the names SQLite substituted. Only names that the rename itself wrote are touched — a name that was already in the text, including one inside a string literal, is left exactly as it stands.

The option is off by default. What it changes is how the DDL reads, never what any object means, and it costs an extra pass over the schema after a rename.

Note

With the option on, the Change Script tab can no longer show the whole of what will run: the correction depends on the schema as it stands after the rename, so it cannot be written in advance. The change script explains this where the correction will happen, and the Last Change Script tab afterwards shows everything that actually ran. With the option off, the change script is complete and exact.