Skip to content
little-brother edited this page Jun 5, 2023 · 198 revisions

Distributive versions

There are 5 different versions: 32 and 64-bit, with encryption support and without and 64-bit version with scripting/encription.
The encryption versions use modified sqlite3.dll. The no-encryption versions use standard library. Both sqlite3.dll libraries are built from source by mingw64-compiler. The last version contains additional extension inja.dll.

Windows 10 has incompatible library (e.g. #25, #123, #125) for editor control. That is why msftedit.dll from Windows 7 was added to distributives.

Feature comparison

sqlite-gui vs DB Browser for SQLite vs Sqlite Studio
Feature sqlite-gui DB4S Sqlite Studio
Syntax highlighting + + +
Autocomplete + + +
Edit table structure -/+ + +
Charts + + -
Edit multiple databases at the same time +/- + +
Parallel queries + +/- +/-
User-defined functions +/- - +
Encrypted database support + + +
Multi-languages - + +
Cross-platformed - + +
Code auto-formatting +/- - +
Data generator + - +
Query history + - +
Parametrized queries + - +
Edit data in resultset - + +
BLOB support + + +
Extensions + + +
Unique features
  • Multiple results for one tab
  • Pinned resultsets
  • Resultset filters
  • Resultset transposition
  • Resultset heatmap
  • Resultset comparison
  • Show referenced data
  • Enable/Disable indeces and triggers
  • Edit views with instead of-triggers
  • Help for standard functions and pragmas
  • Compare query with clipboard content
  • Colorized data grid by value types
  • Scripting
  • Terminal mode
  • Custom shortcuts
  • REST API web-server
  • Plugins
  • Themes
  • User collations
  • Custom hot keys

ℹ️ The major disadvantages of sqlite-gui are bolded.

Each software use own way to work with data/resultset with over thousands rows:

  • sqlite-gui cuts rows by limit. The default value is 10 000. Can be increase in settings.
  • DB4S wraps an original query to with t as (...) limit N offset K to get data only for a visible part. Plus executes an query to get row count with t as (...) select count(*) from t. If the original query is complex (with joins and materialized sub-results), it hit the performance by executing query multiple times. For a simple query e.g. select * from t where id between 30 and 6000 (without order-clause) it's a fastest solution.
  • SQLiteStudio uses the same technique as DB4S for paggination by 1000 rows.

Charts

The query result can be viewed as a chart. There are several types of the chart: lines, dots, areas, histogram and bars. For example, the data below can be viewed as lines with sale_dates as axis-X and items and revenue as Y-values or as bars group by city and bars - items and revenue.

city sale_date items revenue comment
Moscow 2021-05-29 10 100 aaa
London 2021-05-30 7 200 bbb
Paris 2021-05-27 14 150 NULL
Berlin 2021-05-31 20 250 ccc
Cairo 2021-05-24 18 NULL NULL
Try examples for yourself
select 'Moscow' city, '2021-05-29' sale_dates, 10 items, 100 revenue, 'aaa' comment
union all
select 'London', '2021-05-30', 7, 200, 'bbb'
union all
select 'Paris', '2021-05-27', 14, 150, NULL
union all
select 'Berlin', '2021-05-31', 20, 250, 'ccc'
union all
select 'Cairo', '2021-05-24', 18, NULL, NULL

Keep in mind

  • The text columns are ignored
  • Dates should have YYYY-MM-DD or YYYY-MM-DD HH:MI (the separator can be any: ., / or -) format.
  • A chart is builded by a grid data and therefore is limited to 10 000 points.

Pinned resultsets

You can keep a resultset between queries or even app runs. Just pin a required resultset for it.
Use -- <caption> before an query to give the pinned tab a special name.

Pinned resultsets

The data of resultsets is stored in prefs.sqlite on app closing.

Quick references

If you have id in table B that referenced to a row in an another table A then you can view this row by Alt + Click with a simple preparation.

Open refs-table in prefs.sqlite and click by it in an editor.

Column Description
dbname Database filename e.g. test.sqlite
schema Schema of table B. Usually it is main.
tblname Table B name
colname Table B colname
refname Hint title
query Query that will be executed by Alt + Click. Commonly the query returns all columns from table A.
Example: select * from "main"."customers" t where t."id" = ?1

Some of rows are builded automatically by foreign keys references. Check video to see it in action.

⚠️ Since version 1.7.2 refs format has been slightly changed. The reference name was moved to refname-column and the query-column should returns only necessary data.

Query parameters

In an query literals may be replaced by a parameter that matches one of following templates: ?, ?N (e.g. ?1, ?2), :a, @b and $c.

select * from t where id > @id;
select * from t where name like '%' || ?txt || '%';

The binding dialog will be appeared on the query execution.

Query shortcuts

You can create shortcuts for commonly used queries in editor. There are three predefined shortcuts: Alt + F1 returns a table info, Ctrl + 1 - first 100 rows and Ctrl + 2 - row count. You can create new shortcuts for your own queries or redefine these by Query > Shortcuts in the main menu. After you hit a shortcut keys, application will get selection (or word under caret), substitute the selection instead of $SUB$ and execute the resulting query. Herewith, the text in the editor will not be changed.
Check the next video for SSMS with the similar feature, if you have any questions.

Custom functions

These functions are allowed to extend SQLite within the application. Each function has a name and a SQL code implementation (select-statement with one returning value). Use ?1, ?2, ... as function arguments e.g.

select max(?1, ?2) + max(?2, ?3)

You can use exec-extension to call a non-SQL code

select exec('powershell -nologo ""Get-Content ?1""')
select exec('python ?1.py ?2 ?3')

At this time only scalar functions are supported.

Jinja scripting

The scripting extends SQL by Jinja-like templating. This can be especially useful given that SQLite doesn't have any procedural extension. This feature requires inja-extension, which is distributed separately because experimental. Below are examples of how you can use the scripting (input the code as usual in SQL editor).

{# 1. Share parameters between queries #}
{% set id = 10 %} 
select * from books where id = {{ id }};
select * from orders where book_id = {{ id }};

{# 2. Data generation #}
{% for i in range(20) %}
    insert into t (id, value) values ({{ i }}, abs(random() % 10)); 
{% endfor %}

{# 3. Logic implementation #}
{% for row in query("select id, fullname, position from employees") %}
     {% if row.position == "manager" %}
          -- {{ row.fullname }}
          select * from orders where employee_id = {{ row.id }};
     {% endif %}
{% endfor %}
Dive deeper into scripting

Popular tool dbt for data transformation already uses this way to extends SQL-scripts. It looks like more convenient than lua-scripting in SQlite Manager or Tcl-scripting in SQLite Studio. The jinja-like templates are widely distributed in many products because the syntax is a very simple and intuitive but expandable.

There are only three operators which use {% ... %} braces: set, for and if.

{# Set local variable; Use double-quote to quote string #}
{% set name = "Alex" %} 

{# And then use {{ ... }} to substitute a variable value #}
select '{{ name }}'; --> select 'Alex'

{# Loop over array or object #}
{% for i in [3, 4, 5] %} ... {% endfor %}
{% for key, value in {"x": 10, "y": 20} %} ... {% endfor %}

{# Conditional #}
{% if x == 10 %} ... {% else %} ... {% endif %}

The fourth operator is a macro and it can be used as custom function e.g.

{% macro f (a, b) %} {{ a }} + {{ b }} * 2 {% endmacro %}
select {{ f(1, 2) }}; --> select 5;
select {{ f(2, 3) }}; --> select 8;

Unfortunately inja-extension has only a basic support of Jinja templates by a library limitation and it doesn't support macros, filters and some another features yet. But inja-extension provides a special function call(code, data) for it:

{% set f = "{{ a }} + {{ b }} * 2" %}
select {{ call(f, {"a": 1, "b": 2}) }}; --> select 5;
select {{ call(f, {"a": 2, "b": 3}) }}; --> select 8;

There are 3 another extension-specific functions to fetch data from SQLite to variable

{% set val = query_value("select 12 + 3;") %}
select {{ val }}; --> select 15;
select {{ query_value("insert into t (name) values ('AAA') returning id;") }}; --> select <auto-id>;

{% set row = query_row("select * from books where id = " + 10) %} {# returns an object #}
select {{ row.id}}, '{{ row.title }}' as title; --> 10, Catherine Ryan Hyde

{% set dataset = query("select * from books") %} {# returns an array #}
{% for row in dataset %}
    update books set price = price + 10 where id = {{ row.id }};
{% endfor %}

-- Arguments passing
{% set val = query_value("select ?1 + ?2;", 12, 3) %}
{% set row = query_row("select ?1 + ?2 as val;", [12, 3]) %}
{% set rows = query("select * from books where id = @id", {"@id": 5}) %}

Also there are 3 helper functions tojson, fromjson and quote

{% set row = query_row("select ?1 + ?2 as x, ?1 - ?2 as y;", [12, 3]) %}
select {{ quote(tojson(row)) }}; --> {"x":"15","y":"9"}

{% set books = fromjson(query_value("select books from v_json_orders where id = ?1", 1)) %}
{% for row in books %}
select {{ quote(row.title) }} title;
{% endfor %}

And one function raise_error to stop the script and show a message

{% if value < 10 %}
{{ raise_error("Too small") }}
{% endif %}

There is no magic in how it works. The app reads a template from an editor, passes the template to inja-function that generating a plain SQL-code and this code is running as usual. Therefore if you need to execute any DML-command that depending on previous steps then you should use query, query_row and query_value instead of plain SQL insertions.

{# Incorrect usage #}
insert into t (id) values (1);
{% set id = query_value("select id from t where id = 1") %} {# id will be empty! #}

{# Correct usage #}
{% set id = query("insert into t (id) values (1) returning id") %}
Scripting in a terminal mode

In this case the script has a different behaviour. It will be evaluated and outputed without executing e.g.

{# Use additional "-" to remove white-space characters in a result #}
{% for i in range(3) -%}
select {{i}};
{%- for %}

Returns

select 1;
select 2;
select 3;

Data generator

You can define your own "type" in a dropdown box on the right side of column name e.g. animal with values cat, dog, hare and fox. Open generators-table in prefs.sqlite. You need to add four rows with type equals to animal and value one of cat, dog, hare and fox. That's all.

Extensions

The simplest way to extend SQLite is a using extensions. The application is distributed with a pack of extensions.
Extensions from /extensions/vfs are loaded always regardless of the Autoload extensions-option.

Json1 (standard)

Check documentation here.

Regexp (standard)

Check examples in the code.

Fileio (standard)

Implements SQL functions readfile and writefile, and eponymous virtual type fsdir.

select readfile('D:/data/1.txt')
select writefile('D:/data/' || id, data) from myblobs
select * from dsdir('D:/data')
Series (standard)

Provides a one table function generate_series

select * from generate_series(0, 100, 5)
Uuid (standard)

Implements uuid(), uuid_str(X) and uuid_blob(X) functions that handling RFC-4122 UUIDs.

Ora (custom)
  • rownum (startBy)
    Returns a row number starting from a passed argument

    select *, rownum(0) from mytable
    
  • concat (str1, str2, ...)
    Concatenates strings. Equals to str1 || str2 || ...

    select concat(str1, str2, str3) from mytable
    
  • decode (expr, key1, value1, ke2, value2, ..., defValue)
    Compares expr to each key one by one. If expr is equal to a key, then returns the corresponding value.
    If no match is found, then returns defValue. If defValue is omitted, then returns null.

    decode(1 < 2, false, 'NO', true, 'YES', '???') --> YES
    decode(1 > 2, false, 'NO', true, 'YES', '???') --> NO
    
  • crc32 (str)
    Calculate crc32 checksum

  • md5 (str)
    Calculate md5 checksum

  • base64_encode (str)
    Encodes the given string with base64.

    select base64_encode('foobar') --> Zm9vYmFy
    
  • base64_decode (str)
    Decodes a base64 encoded string.

    select base64_encode('Zm9vYmFy') --> foobar
    
  • strpart (str, delimiter, partno)
    Returns substring for a delimiter and a part number

    select strpart('ab-cd-ef', '-', 2); --> cd
    select strpart('20.01.2021', '.', 3); --> 2021
    select strpart('20-01.2021', '-.', -1); --> 2021
    
  • conv (num, from_base, to_base)
    Converts a number from one numeric base number system to another numeric base number system. After the conversion, the function returns a string representation of the number. The minimum base is 2 and the maximum base is 36. Only positive numbers are supported.

    select conv(15, 10, 2) --> 1111
    
  • tosize (nBytes)
    Returns a human readable size

    select tosize(1024) --> 1.00KB
    select tosize(2 * 1024 * 1024) --> 2.00MB
    
  • levenshtein(str1, str2)
    Calculates Levenshtein distance between two strings

    select levenshtein('9128 LEEWARD CIR, INDIANAPOLIS, IN', upper('9128 Leeward Circle, Indianapolis, IN')) --> 3
    
XML (custom) Implements functions are useful for managing XML content stored in an SQLite database as text. Uses XPath 1.0 to obtain a data.
  • xml_valid(xml)
    Returns 1 if the argument is well-formed XML and 0, otherwise.

    select xml_valid('<a>A</a>'); --> 1
    select xml_valid('<a>A/a>'); --> 0
    
  • xml_extract(xml, xpath, delim = "")
    Extracts a node content or an attribute value.

    select xml_extract('<a>A</a>', 'a'); --> <a>A</a>
    select xml_extract('<a>A</a>', 'a/text()'); --> A
    select xml_extract('<a>A</a><a>B</a>', 'a/text()', ','); --> A,B
    select xml_extract('<a id = "1">A</a>', 'a/@id'); --> 1
    
  • xml_append(xml, xpath, insertion, pos = after)
    Appends a node or an attribute based on the pos (one of first, before, after, last, child or child first/child last).
    The child is ignored for the attribute. The insertion should be valid (there is no check).

    select xml_append('<a>A</a><a>B</a><a>C</a>', 'a[2]', '<b>D</b>', 'after') xml; 
    --> <a>A</a><a>B</a><b>D</b><a>C</a>
    
    select xml_append('<a>A</a><a>B</a><a>C</a>', 'a[2]', '<b>D</b>', 'child') xml; 
    --> <a>A</a><a>B<b>D</b></a><a>C</a>
    
    select xml_append('<a>A</a><a id="1">B</a><a id="2">C</a>', 'a/@id', 'x="2"', 'first') xml; 
    --> <a>A</a><a x="2" id="1">B</a><a x="2" id="2">C</a>
    
  • xml_update(xml, xpath, replacement)
    Updates nodes or attributes. The replacement should be valid (there is no check). If the replacement is NULL then the call equals to xml_remove(xml, path).

    select xml_update('<a>A</a><a id="1">B</a><a id="2">C</a>', 'a[2]', '<b>D</b>');
    --> <a>A</a><b>D</b><a id="2">C</a>
    
    select xml_update('<a>A</a><a id="1">B</a><a id="2">C</a>', 'a/@id', '3');
    --> <a>A</a><a id="3">B</a><a id="3">C</a>
    
  • xml_remove(xml, xpath)
    Remove nodes or attributes.

    select xml_remove('<a>A</a><a id="1">B</a><a id="2">C</a>', 'a[2]');
    --> <a>A</a><a id="2">C</a>
    
    select xml_remove('<a>A</a><a id="1">B</a><a id="2">C</a>', 'a/@id');
    --> <a>A</a><a>B</a><a>C</a>
    
  • xml_each(xml, xpath)
    It's a table-valued function.

    select * from xml_each('<a>A</a><a>B</a><a>C</a>', 'a/text()'); --> Three rows: A, B and C
    

    ℹ️ You can use exec-extension to import XML data from files

    create table xmlData as 
    select line file, exec('powershell Get-Content D:/xmlFolder/' || line || ' -Encoding UTF8') xml
    from exec('powershell Get-ChildItem -Path D:/xmlFolder -Name')
    -- or 'powershell Get-ChildItem -Path D:/xmlFolder -recurse | select fullname -expandproperty fullname'
    
ODBC (custom) This extension uses to work with an external data.
  • odbc_read(connectionString, query, target)
    Read data via ODBC from external source and write it to SQLite table. If the target table doesn't exist, it'll be created.
    Use TABLES as the query to obtain a table list.

    select odbc_read('Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=D:/csv/', 'select * from animals.csv', 'animals')
    
  • odbc_write(query, connectionString, target)
    Upload an query resultset from SQLite to external database. The target table must exists.

    select odbc_write('select * from animals where family = "Felidae"', 'DSN=Zoo.mdb', 'cats')
    
  • odbc_query(connectionString, query)
    Execute an query on external database e.g. to create or purge a target table.

    select odbc_query('DSN=Zoo', 'create table cats (id integer, name varchar2(50))')
    
  • odbc_dsn()
    Returns local DSN list as json array: {"result": ["MyData", "Csv", ...], ...}

    select odbc_dsn()
    

Remarks

  • A result of a function is a json: {"result": "ok", ...} on done and {"error": "<msg>"} on error.
  • Use 32bit ODBC manager C:\Windows\SysWOW64\odbcad32.exe to define a local DSN.
  • You should install appropriate ODBC driver to gain access to MySQL, PosgreSQL, Mongo, etc.
Exec (custom) This extension is used to run any external app and grab its output e.g. get remote data by curl.
  • exec(cmd, codepage = 'UTF16')
    Executes shell command and returns console output as result.
    Codepage defines code page of command output and is a one of: ANSI, CP437, UTF7, UTF8, UTF16. If cmd starts from powershell and codepage is empty then CP437 is used.
    select exec('powershell -nologo "Get-Content C:/data.txt"');
    select * from exec('powershell Get-Content C:/data.txt -Encoding UTF8', 'CP437');
    
inja (custom) This extension is used for scripting features.
  • inja(template, arg1, arg2, ...)
    Parse jinia-like templates.
    select inja('{% for i in range (2) %}select {{ i }}; {% endfor %}'); --> select 1; select 2;
    
Icu (third party)

Adds partial support for national symbols by modifiсation lower and upper functions.

select lower('Ы'); --> `ы`
Vsv (third party)

SQLite virtual table for reading VSV (Variably Separated Values), which are like CSV files, but subtly different.
Check source to get more details.

create virtual table temp.vsv using vsv(...);
select * from vsv;

Check sqlean project to get more extensions.

Export/Import data via ODBC

The app provides easy way to work with external data through ODBC-extension.The extension uses 32-bit drivers and supports only two data types text and numeric (double). Also each ODBC driver has own oddity/limitation that couldn't be fixed easily.

When configuring a 32 bit ODBC datasource for an Excel file created in Microsoft Office 2010 or above you need to have the driver for .xlsx files. The 32 bit version of this driver is not installed by default with Office 2013 or above. To add this driver you need to install the Microsoft Access Database Engine 2010 Redistributable.

Excel

  • delete from <table> returns succeeded-status without real deleting data.
  • An table could be referenced as <tblname>$B23 that means worksheet <tblname> and cell B23.
  • SQLExecDirect that is called in app raises run-time error if the document is opened.
  • Don't forget remove "Read-only" flag in connection parameters.

CSV

  • The driver works in read-only mode.

Multithreading to execute queries in parallel

SQLite supports three different threading modes: single-thread, serialized and multi-thread. The first two imply that an application uses a one (shared) connection to access to a database and all queries are executed sequentially. That is not a problem for simple queries that are usually used. The most popular SQLite tools are using one connection. So their interface will be blocking when you try to execute a longer query and do something else at once e.g.

with t(x) as (select 1 union all select x + 1 from t limit 10000), t2(x) as (select x from t order by random())
select avg(t.x * t2.x) from t, t2;

The using multi-thread mode (when each tab uses own connection) allowed to:

  • execute queries in parallel
  • interrupt queries independently
  • make UI more responsive

⚠️ The app uses a one auxilary connection to fetch meta data and to work in tools and a separate connection for each editor tab (include the teminal tab). There are issues related with it:

  • attach, detach and pragma commands will only affect the tab that called them.
  • each tab (connection) has own temp-schema. So if you want share in-memory data between tabs you should use TEMP2-schema instead.

The another one option to more parallel opportunities is a using Write-Ahead Logging (WAL). WAL provides more concurrency as readers do not block writers and a writer does not block reader (reading and writing can proceed concurrently).

Database encryption

The app supports encryption through a vfs-extension provided by SQLite3 Multiple Ciphers. This extension is built into sqlite3.dll. If you don't need encryption, use *-no-ciphers distributive version with original sqlite3.dll.

REST API web server

This feature allows to manipulate (do CRUD operations) in SQLite database via http-requests. It can be useful for those who need to emulate site backend or a microservice. Available links are built automatically based on metadata. The feature is disabled by default in Settings. After turning on, the server will be available at http:\\127.0.0.1:3000.
Limitation: A table should have a single column primary key for CRUD.

Method Url Description
GET /api/:table?:tail Returns all rows by query select * from :table :tail
GET /api/:table/:id Get row from :table with primary key equals to :id
GET /api/:table/:id/:fktable Get linked rows in :fktable by foreign key
POST /api/:table Insert a new row by a request body. Keys without existent columns will be ignored.
PUT /api/:table/:id Update row. Keys without existent columns will be ignored.
DELETE /api/:table/:id Delete row by :id

POST, PUT and DELETE methods return an object id e.g. {"id": 10} on success.

Request examples

Method Url Body
GET /api/books?limit 100
GET /api/books?where price > 10
GET /api/books/10
GET /api/employees/7/orders
POST /api/books {"title": "ABC", "price": 5}
PUT /api/books/10 {"title": "NEW"}
DELETE /api/books/10

prefs.sqlite

Table Description
prefs User preferences such a window position, font attributes and etc.
recents Recently opened databased.
history Succeeded queries
gists User saved queries
generators Values for a data generator
functions Custom functions
refs Quick references. Some of rows are builded automatically by foreign keys.
disabled All disabled index and triggers are stored here
encryption Encryption profiles for databases
cli Succeeded queries in a terminal
diagrams Table's positions on diagrams
query_params The values used for parametrized queries early
search_history Last 20 search strings in Search dialogs
shortcuts Query shortcuts
functions Custom functions
help Strings for popup help.

You can add own strings to help table. But don't change existing rows because the table will be updated automatically by new release and you'll lose your changes. The best way to change them or add something is just let me know.

Number values of prefs-table are stored in memory and overrided on exit. So their editing does not affect.
You can change prefs-values via terminal.

Advanced settings

There are couple settings which can be changed only trougth the terminal by .set-command e.g. .set cli-font-size 10.

Option Default Description
cli-font-size 8 The terminal's output font size
cli-row-limit 10 Row limit in query result in the terminal output
backup-prefs 0 Backup prefs.sqlite on start
use-highlight 1 Enable/Disable text highlighting in editors
use-legacy-rename 0 Equals to pragma legacy_alter_table = ?1
max-column-width 400 Max column width in a resultset. Set to 0 to turn off this limitation.
last-update-check 0 Last check date in YYMMDD-format
http-server-debug 0 Debug REST API web server to http-debug.txt
format-keyword-case
format-function-case
1 Define how query formatter works with keywords and functions
0 - don't change
1 - use lower case
2 - use upper case
ignore-readonly-prefs 0 Don't alert user if prefs.sqlite is read only
extended-query-plan 0 Show a full execution plan for an query

Command line arguments

sqlite-gui [--profile <file>.sqlite] [--readonly] [database [<table or view>]] [--csv-import <input>.csv <tablename>] [--csv-export <output>.csv <query>]

The order of the individual commands is not important.

Examples

  • Open the specified database at startup
    sqlite-gui D:/databases/bookstore.sqlite 
    
  • Open only the specified table/view for editing at startup
    sqlite-gui D:/databases/bookstore.sqlite books
    
  • Import CSV file with default settings
    sqlite-gui D:/databases/bookstore.sqlite --import-csv "D:/data/my file.csv" my_table > import-result.txt
    
  • Export query result to CSV file with default settings
    sqlite-gui D:/databases/bookstore.sqlite --export-csv D:/export/books.csv "select * from books" > export-result.txt
    

Use quotes if a database path or a table name contains a space or another special symbols.

How to build

  • sqlite3.dll + sqlite3.def
    // original
    gcc -shared -Wl,--output-def=sqlite3.def sqlite3.c -o sqlite3.dll -DSQLITE_ENABLE_DBSTAT_VTAB -DSQLITE_ENABLE_COLUMN_METADATA -DSQLITE_THREADSAFE=2 -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_GEOPOLY -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_MATH_FUNCTIONS -s -O2 -static
    
    // SQLite3 Multiple Ciphers version, mingw64-32 is required
    gcc -shared sqlite3mc.c -o sqlite3.dll -DSQLITE_SECURE_DELETE -DSQLITE_ENABLE_DBSTAT_VTAB -DSQLITE_ENABLE_COLUMN_METADATA -DSQLITE_THREADSAFE=2 -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_GEOPOLY -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_MATH_FUNCTIONS -D_WINDOWS -DWIN32 -DNDEBUG -DSQLITE_API=__declspec(dllexport) -m32 -s -O2 -static -msse4 -maes
    
  • libsqlite3.a
    cd include 
    dlltool -d sqlite3.def -l libsqlite3.a -D sqlite3.dll
    copy libsqlite3.a lib/libsqlite3.a
    
  • Extensions e.g. json1 (pure-C) or xml (C++)
    cd extensions
    gcc -I ../include -shared json1.c -o json1.dll -s -static-libgcc
    g++ -I ../include -shared xml.cpp ../include/pugixml.cpp -o xml.dll -s -static -DPUGIXML_NO_STL -Os
    
  • Application x64
    mkdir bin
    mkdir obj
    
    set PATH=C:\mingw64\mingw64\bin;%PATH%
    del bin\sqlite-gui.exe
    
    windres.exe   -J rc -O coff -i src\resource.rc -o obj\resource.res
    g++ -Wall -std=c++11 -m32 -DUNICODE -D_UNICODE -D_WIN32_WINNT=0x0501 -Os -std=c++11 -m64 -U__STRICT_ANSI__ -Iinclude -c src\dialogs.cpp -o obj\dialogs.o
    g++ -Wall -std=c++11 -m32 -DUNICODE -D_UNICODE -D_WIN32_WINNT=0x0501 -Os -std=c++11 -m64 -U__STRICT_ANSI__ -Iinclude -c src\http.cpp -o obj\http.o
    g++ -Wall -std=c++11 -m32 -DUNICODE -D_UNICODE -D_WIN32_WINNT=0x0501 -Os -std=c++11 -m64 -U__STRICT_ANSI__ -Iinclude -c src\main.cpp -o obj\main.o
    g++ -Wall -std=c++11 -m32 -DUNICODE -D_UNICODE -D_WIN32_WINNT=0x0501 -Os -std=c++11 -m64 -U__STRICT_ANSI__ -Iinclude -c src\prefs.cpp -o obj\prefs.o
    g++ -Wall -std=c++11 -m32 -DUNICODE -D_UNICODE -D_WIN32_WINNT=0x0501 -Os -std=c++11 -m64 -U__STRICT_ANSI__ -Iinclude -c src\tools.cpp -o obj\tools.o
    g++ -Wall -std=c++11 -m32 -DUNICODE -D_UNICODE -D_WIN32_WINNT=0x0501 -Os -std=c++11 -m64 -U__STRICT_ANSI__ -Iinclude -c src\utils.cpp -o obj\utils.o
    g++ -Llib -o bin\sqlite-gui.exe obj\dialogs.o obj\http.o obj\main.o obj\prefs.o obj\tools.o obj\utils.o  obj\resource.res -static -m32 -s -static -m64  -lgdi32 -luser32 -lkernel32 -lcomctl32 -lgdiplus -lcomdlg32 -lshlwapi -lmsimg32 -lwininet -lws2_32 -lole32 -luuid lib\x64\libsqlite3.a -mwindows
    
    or use Code::Blocks 17 with installed mingw64 (MingW-W64-builds). To support Windows 10 -static linker flag is required.

Virus warnings

Some antiviruses don't like a small exe/dlls. These are a known issues 1, 2.
Below example for standard SQLite extensions are builded by gcc -I ../include -shared x.c -o x.dll -s

This problem occurs for default Code::Block 17 gcc compiler (5.1.0). To build dlls less/without warnings the compiler needs to be updated to latest version (9.2.0). But sometimes that's not enough: there is opinion that antiviruses don't like a non-msvcrt (gcc) entry point.
It is worth noting: dll with a new unique name makes VirusTotal less suspicious. The next check will use a file hash.

Third-party software

  • SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine;
  • SQLite3 Multiple Ciphers implements an encryption extension for SQLite with support for multiple ciphers;
  • inja is a template engine for modern C++, loosely inspired by Jinja for Python;
  • JSON for Modern C++ is a json library. Used by inja;
  • pugixml is a C++ XML processing library;
  • uthash is C-library for hash-tables. Used by XML-plugin;
  • dmp is modified diff-match-patch. Used for query comparison;
  • Extensions: vsv, icu.

Are you need more topics?

Get into contact with me by email [email protected]

Clone this wiki locally