SQLite Forum


33 forum posts by user knu occurring on or before 2020-10-14 13:37:08.

More ↑
13:37 Reply: Lack of abstraction ability in SQLite (artifact: a1b9ccc564 user: knu)
  • There is a way to add custom functions via the C API but no way to write custom functions in SQL (to reuse commonly used code, etc.) or a built-in procedural language.

Database procedures are especially useful in client/server database models, where they avoid the dataflow bottleneck between client and server by running procedures in the server. Dataflow between a host program and the SQLite library is orders of magnitude faster.

Think of SQLite not as a replacement for Oracle but as a replacement for fopen() .

Reuse of commonly used code can easily be obtained by using functions or macros in the host language, or by using some other macro processor (m4, anyone?).

  • There are virtual tables but no way to create writable views (creating triggers over views is impossible)

You can create INSTEAD OF triggers on views and make them writable that way.

  • There is a bytecode engine with powerful features such as coroutines but it lacks a stack (to have reentrancy, continuations, and abstractions) and is not exposed as an API to applications.

It is good enough to serve its sole purpose: a database engine.

It is, and should be, a black box, only exposed through higher abstractions like the C-API and SQL.

  • There is no way to create a "virtual database" that handles reads and writes its own way.

Couldn't that be achieved with virtual tables, and a :memory: database for just the schema?

The C API is lacking as well:

  • There is no way to create and register new storage engines. ... This makes it impossible for SQLite to read from and write to CSVs, Excel files, etc. without the special "virtual table" feature. (Ideally, the "virtual table" feature shouldn't be special.)

IMHO virtual tables are not more special than alternative storage engines. And good luck implementing an Excel storage engine. SQLite can be used the other way around, as a storage engine for a spreadsheet program, e.g. with an ODBC driver.

  • SQLite doesn't support the CREATE PROCEDURE syntax even just to pass the code to an external interpreter to run.

SQLite intents to replace fopen() in order to manage a structured data storage, accessible with SQL. As a database engine library, the expectation is that all procedural code is handled by the host program.

I think adding these features to SQLite, which is already modular by design, isn't difficult. ...

I think the strength of SQLite is that it is faithful to its scope and only implements features that can be maintained by the small team.

SQLite is a modular, well-designed, extensible data storage and processing library

I fully agree.

and if it is a little bit more modular and extensible it would take over the world.

It already has taken over the world. Anyone is free to fork the code and add features, and people have done that to some extent, but the results were never as successful as core SQLite itself.

However, I understand your enthusiasm about the concept, quality of implementation, and excellent support for SQLite, and your desire to apply that quality to a bigger scope.

Just my EUR 0.02

Kees Nuyt
13:41 Reply: In-memory database from sqlite file (artifact: e89bbf0b51 user: knu)

Even if you only append rows, you are in trouble when you happen to copy the database in the middle of a transaction, whether it is implicit or explicit.

Any insert, update or delete may affect more than one database page.

Rows are kept in a BTree structure, stored in pages.

Any modification will change a leaf page, some modifications will also change interior pages.

By copying in the middle of a transaction, you break the atomicity of the commit process. You may copy pages with states before and after modification.

So, the resulting copy may be corrupt.

Kees Nuyt
09:18 Reply: Which file(s) to download? (artifact: 51b3de32b6 user: knu)

The download page only lists three Precompiled Binaries for Windows.

For linking into your C program, you need one of the sqlite-dll-win*.zip .

To experiment with the command line tool, you need sqlite-tools-win32-x86-{versiondigits}.zip .

Unzip that zip file into a folder of your liking. Start any of the programs from a cmd.exe or powershell.exe command line.

See also the quick start page and the command line shell page .

Kees Nuyt
20:50 Reply: select * from t; -- but don't want virtual columns (artifact: 4224893b0b user: knu)

Try table_xinfo()

SELECT * FROM pragma_table_xinfo('tablename')

The virtual column gets attribute 'hidden'.


sqlite3 test.db \
)" \
".mode column" \
". echo on" \
"SELECT * FROM pragma_table_xinfo('t1')"
cid  name  type                      notnull  dflt_value  pk  hidden
---  ----  ------------------------  -------  ----------  --  ------
0    id    INTEGER                   1                    1   0
1    a     INTEGER                   0                    0   0
2    b     INTEGER                   0                    0   0
3    c     INTEGER GENERATED ALWAYS  0                    0   2
Kees Nuyt
18:39 Reply: Is date/time atomic per statement/transaction? (artifact: 775b7527e1 user: knu)

It is atomic per statement (not per transaction), as demonstrated by this experiment:

$ strace -c sqlite3 :memory: \
"SELECT date('now'),time('now')"
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
  0.20    0.000020          20         1           gettimeofday
------ ----------- ----------- --------- --------- ----------------
100.00    0.010088                   282         5 total

$ strace -c sqlite3 :memory: \
"SELECT date('now'),time('now')" \
"SELECT date('now'),time('now')"
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
  0.54    0.000052          26         2           gettimeofday
------ ----------- ----------- --------- --------- ----------------
100.00    0.009686                   284         5 total

$ strace -c sqlite3 :memory: \
"SELECT date('now'),time('now')" \
"SELECT date('now'),time('now')" \
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
  0.94    0.000052          26         2           gettimeofday
------ ----------- ----------- --------- --------- ----------------
100.00    0.005525                   284         5 total

Note: counts of other function calls removed for brevity.

Kees Nuyt
09:21 Reply: Getting no such column error for values to be inserted (artifact: 7078f643c2 user: knu)

I assume you want to insert a literal.

In SQL, string literals have to be quoted with single quotes.

INSERT INTO testTable VALUES ('lol');
Kees Nuyt
12:50 Reply: Help me please, Romanian Language script -> Error (artifact: 58490d1f8a user: knu)

Impossible to tell without seeing your database schema.

sqlite3 Complet.db .schema

Is column Numele_Complet defined in table Complet ?

Kees Nuyt
10:25 Reply: How to remove a column and guide from the docs (artifact: 9ec048bbde user: knu)

Find the other process that has your database open:

sudo lsof /path/to/your/database

(Unix, Linux)

Kees Nuyt
22:44 Reply: How to remove a column and guide from the docs (artifact: 5d053a100e user: knu)

Are there any recommendations how to implement migrations? I suppose people want to remove columns from time to time.

I script all my conversions. Conversion scripts always look like:

.timeout 20000
-- not necessary, it is the default, 
-- but might not always be the default:
PRAGMA foreign_keys=off; 
-- conversion SQL here
-- always use explcit name lists, never SELECT *

and I run the script with sqlite3 -bail database.sqlite <conversionscript

For renaming/adding/removing colums, I usually create a new database with the new schema, then populate it with something like:

ATTACH '/path/to/olddb.sqlite' AS olddb;
INSERT INTO main.newtable (col1,col2,...) SELECT col1,col2,... FROM olddb.oldtable ;
-- etcetera.
DETACH olddb;

If triggers would cause unwanted updates, I DROP them first and recreate them after the conversion.

A common "problem" is ownership and permissions of the database file, its -joourrnal, -shm, and/or -wal file and the directory they are in.

If you run a script with sqlite3 shell, this usually runs as another user than e.g. a webserver.

Kees Nuyt
01:43 Reply: Using WAL mode with multiple processes (artifact: 84d01c9108 user: knu)

The text for PRAGMA journal_mode=wal


The WAL journaling mode uses a write-ahead log instead of a rollback journal to implement transactions. The WAL journaling mode is persistent; after being set it stays in effect across multiple database connections and after closing and reopening the database. A database in WAL journaling mode can only be accessed by SQLite version 3.7.0 (2010-07-21) or later.

Typically, you set WAL mode during the initial creation of the database, and you never have to look back.

WAL journal mode supports one writer and many readers at the same time. A second writer will have to wait until the first write transaction is committed or rolled back. More info can be found in the WAL documentation

Kees Nuyt
15:44 Reply: Drop caches & Sqlite cache management (artifact: 729f1c7faf user: knu)

The SQLite page cache has the advantage of "inside information", it can give priority to certain types of pages, like the root page and BTree index pages of any table and index ever used in the connection.

Indeed, even if a page is in the OS cache, the code path to retrieve it is much longer than when it is in the SQLite cache.

My rules of thumb :

  • For small databases I size the cache so the database fits in the cache completely.
  • For bigger databases at least the sum of :
    • the size of sqlite_master
    • twice the sum of the number of index pages of all tables and indeces (can be retrieved with sqlite3_analyzer)
    • a reasonable estimate of the number of leaf pages modified by "typical transactions" (to prevent avoidable cache spills)

I don't consider this the most optimal method, and if the platform allows, I happily categorize a 30 MByte database as "small" and give it a 30 MByte cache.

Kees Nuyt
13:50 Reply: Drop caches & Sqlite cache management (artifact: c2b041ba8f user: knu)

Is there any flag or any utility to know when sqlite3's page cache is ready to use?

The internal sqlite page cache will be automatically be used by the sqlite engine as soon as a database connection is opened.

So, When I call drop_caches, there could not be performance penalty.

The drop_caches call drops the Operating System's file caches, any buffered page from any file that was in the OS page cache will have to be re-read from the physical filesystem as soon as they are required by an program.

The OS does not make any difference between pages from a database or any other cached file.

So you will definitely experience a performance penalty for the system as a whole.

May I ask why you are so eager to drop the caches?

Kees Nuyt
13:56 Reply: Query planner fails to use obvious index (artifact: ab94f3ad9f user: knu)

Did you run ANALYZE after populating the table and before running the test?

19:27 Reply: temp storage within triggers (artifact: 59a43c145a user: knu)

It should be safe, even with multiple connections, because a trigger is always used in the context of an implicit or explicit transaction.

In all journal modes, there is always only one writer. Triggers launch for insert, update, or delete, which are all writers.

So, the normal table you use as 'tempstore' will only be accessed by one connection at any time.

Kees Nuyt
13:31 Reply: current_date, etc are functions? (artifact: 45d9b988a9 user: knu)

In SQLite, you can use functions in default values, but you have to surround them with parenthesis :

sqlite> create table t1(a, b default current_date);
sqlite> create table t2(a, b default strftime('%Y','now'));
Error: near "(": syntax error
sqlite> create table t2(a, b default (strftime('%Y','now')));
sqlite> insert into t2(a) values(1); select * from t2;

Kees Nuyt
15:02 Reply: Feature Request: consistent .dump order (artifact: a1f29bc7f0 user: knu)

Actually, the above does NOT do the trick, but results in a corrupted database

Yeah, tables should be defined before anything that refers to them.

You need a different sort order, illustrated by this script:

.mode column
.headers on
.width -5 9 32 32 -8 -6
SELECT ROWID,type,name,tbl_name,rootpage,
	WHEN type=='table' THEN 1
	WHEN type=='index' THEN 2
	WHEN type=='trigger' THEN 3
END AS srtord
FROM sqlite_master 
ORDER BY srtord,tbl_name,name;

In reality, things are even more complicated of you want to insert in dependency order, with

PRAGMA foreign_keys=on
Parent tables have to be loaded befor children.

Kees Nuyt
14:56 Reply: Bug?: CREATE TABLE with unsatisfied FK and DROP TABLE results in Error (artifact: b2bfd3151f user: knu)

The error message is generated because somewhere else you issued :

PRAGMA foreign_keys=on;
, possibly by setting SQLITE_DEFAULT_FOREIGN_KEYS=1 during compilation or by including it in ~/.sqliterc .

What you see here is that the existence of the parent table "table" is not checked during CREATE TABLE, but is properly checked during any ather action, like INSERT or DROP. This behavior is likely necessary to allow tables referencing each other in both ways, or to allow self-referencing tables.

In some cases, foreign key checks can be postponed until COMMIT. Note the remark on PRAGMA defer_foreign_keys.

Kees Nuyt
14:45 Reply: PRAGMA INTEGRITY_CHECK takes very long (artifact: 9d9e63a8d4 user: knu)

I am starting to wonder if there is a problem with my system.

There is nothing wrong with your system.

In all of those runs, the same amount of work has to be done, hence user and sys time will be approximately the same.

In the first run,. the database is not in the OS filesystem cache (AKA disk cache), so a lot of time is spent waiting for I/O (read from disk).

In all subsequent runs a significant part of the database will be in the cache, especially often used database pages.

You can experiment with PRAGMA cache_size=...; but this will not make a lot of difference (I tried), because it only moves part of the caching from the OS disk cache to the SQLite page cache.

You can stop worrying.

Kees Nuyt
14:18 Reply: Efficient array data type (artifact: 99a33767e8 user: knu)

Hi, I'm trying to figure out if sqlite, or one of its extensions, supports efficient storage of arrays of various data types. For the problem at hand, of numbers and dates, but in general, of any SQLite supported data type.

Arrays are not supported as datatype for a column value in SQLite.

In relational databases, arrays are genrally stored as rows with as many key columns as the array has dimensions, and one value column.

Your question suggests you think that is not efficient, but doesn't have to be true. Just try it.

Alternatives I can think of:

  • You could serrialize an array into a blob, but you will have to build that by yourself
  • JSON arrays, yes they are probably much less space efficient than storing them the SQL way.
  x INTEGER NOT NULL -- dimension 1
, y INTEGER NOT NULL -- dimension 2
, z INTEGER NOT NULL -- dimension 3
, v REAL -- the proper type for julianday() dat3e/time stamps
, PRIMARY KEY (x,y,z)
Kees Nuyt
23:34 Reply: Integer becomes blob (artifact: d0f25da1ce user: knu)

Please take it as meaning nothing more than that (and stating some SQLite facts, of course.)

I got your point, no offense taken!

Thanks for motivating the OP to investigate some more.

Kees Nuyt
22:01 Reply: Integer becomes blob (artifact: bea8b8e36a user: knu)


21:50 Reply: Integer becomes blob (artifact: 392b48aafc user: knu)

As can be seen in the doc on data-types, section 3.1, INT and INTEGER in the putative [a] type position of a create table statement mean the same thing. Also, SQLite is agnostic as the case of keywords, so the above two code fragments are equivalent as far as the parser is concerned.

For SQLite, that's all true. My hunch was that Python might want to interpret the column type to determine which binding to use. Python might be less forgiving than SQLite.

Kees Nuyt
14:53 Reply: Integer becomes blob (artifact: 7813b136f5 user: knu)

Your CREATE TABLE statement uses column type int instead of the prescribed INTEGER keyword.

Python might not understand what you mean.

Try to change:

                                        id integer PRIMARY KEY,
                                        item_name text,
                                        item_id int,
                                        ts int,
etcetera, into:

                                        id INTEGER PRIMARY KEY,
                                        item_name TEXT,
                                        item_id INTEGER,
                                        ts INTEGER,


I hope this helps

Kees Nuyt
14:26 Reply: Integer becomes blob (artifact: 49dceb2cb9 user: knu)

i dont see where item_name missing in my statements, the order is diffrent but that shoudnt matter right?

Sorry, I overlooked that.

Kees Nuyt
20:01 Reply: Integer becomes blob (artifact: 5125596e52 user: knu)

My python knowledge is limited and it is the first time I see colab, so I will not pretend I am able to spot any errors or misuse.

The SQL looks reasonable but your schema and the datafile contain "item_name", the insert statement doesn't.

Somewhere the input values is bound to the statement as a blob. The code to try to convert back to integer is in "reading+ cleaning".

Kees Nuyt
14:24 Reply: Select Query will not work in PHP program (artifact: 71f5df00cb user: knu)

As Tim told you, $db is not known in the context of the insertRecord() function.

It is out of scope of that function. It is the same problem as the first one you posted, where $fatabase was out of scope of the MyDB class constructor.

Stephan is correct, your problem has nothing to do with SQL or SQLite. It is not even a problem of the PHP wrapper around SQLite, but purely misunderstanding of PHP variable scope.

The error message is quite clear.

 Uncaught Error: Call to a member function exec() on null 
Kees Nuyt
16:30 Reply: Integer becomes blob (artifact: fcf3ad21fc user: knu)

It is hard to tell at first glance what could have gone wrong.

Please show us the schema for the problem table and the insert statement you used to fill it.

Is there just one row with that problem? You can verify with something like:

SELECT .... WHERE typeof() == 'blob' .... 

Can you spot any relation between the problem row and its source input line?

You can verify the integrity of the physical database file with :

sqlite3 /path/to/database.sqlite "PRAGMA integrity_check"
Kees Nuyt
13:59 Reply: Feature Request: consistent .dump order (artifact: 6bf8ca5877 user: knu)

The .dump command accepts a table name as an optional argument. It's not overly difficult to script it any way you like:

  for tbl in \
$(sqlite3 $db "SELECT name FROM sqlite_master WHERE type=='table' ORDER BY name")
    sqlite3 $db ".dump $tbl"
  done >yourdumpfile

Hope this helps.

Kees Nuyt
22:21 Reply: can not subscribe (artifact: 142d8852b1 user: knu)

I have an email with @outlook.com and none of these get to my inbox. They go to the spam folder.

That would mean sqlite.org is not blocked completely at the IP level, mail is apparently accepted by outlook.com.

I have written rules, I have set the from address to safe senders, nothing works.

Also weird. There are two addresses you would havbe to mark as trusted (or put in your address book :

The sender address

and the envelope-from address:

I'm glad my mail-provider-for-mailing-lists-and-notifications doesn't cause that kind of trouble.

Kees Nuyt
16:27 Reply: Select Query will not work in PHP program (artifact: 5031dff62b user: knu)

At first sight, I would say:


is defined, but not in the context of the constructor.

To make it visible to the constructor, try code like this:

// ===database ===

  class MyDB extends SQLite3 {
    function __construct() {
      global $dataabse;
      $this->open($database); // optumRx.db
Kees Nuyt
02:09 Reply: Rename an INDEX using sqlite_master (artifact: c49a93c226 user: knu)

I think

ANALYZE sqlite_master;

will do the trick.

Kees Nuyt
19:14 Reply: Forum emails are all going to the SPAM folder (artifact: 4f5bbc9971 user: knu)

For the mailing list, the headers had Sender: sqlite-users sqlite-users-bounces@mailinglists.sqlite.org


Forum emails don't seem to have that sender line anywhere. Maybe just putting that back would be the simplest solution.

That sounds like a very good idea!


Kees Nuyt
00:05 Reply: Forum emails are all going to the SPAM folder (artifact: b6ba13e1ea user: knu)
> I email outlook.com and they said that I need to add the
> address of the forum to my address book, but, the problem
> is that every email has a different address like
> noreplyxxxxxxxxx@sqlite.org

You could try to add the envelope-from address  root@sqlite.org
to your address book. That address is the permitted sender.

I don't know about outlook, but my email provider recognizes the SPF :

Received-SPF: pass (filter02asd2.se.isp-net.nl: domain of sqlite.org designates as permitted sender) client-ip=; envelope-from=root@sqlite.org; helo=sqlite.org;
X-SPF-Result: filter02asd2.se.isp-net.nl: domain of sqlite.org designates as permitted sender


Kees Nuyt