SQLite is a great embedded database (part 1)

I decided to still write an article about SQLite, in which I want to summarize my 3 years experience of using this database under Windows. I see that the topic is popular, but there is little information.

Part 2
Part 3

A little introductory.
')
This article is not for novice programmers.
It is not a SQL tutorial.
It does not agitate to use SQLite.
It does not agitate not to use SQLite.
The article is written in the form of questions from a hypothetical novice in SQLite and answers to them (since there is a lot of information and it is at least a little easier to structure it).

What is SQLite?

SQLite is an embedded cross-platform database that supports a fairly complete set of SQL commands and is available in source codes (in C).

SQLite source codes are in the public domain, that is, there are no restrictions on usage at all.

Site (with excellent documentation in English): http://sqlite.org

Current version: 3.7.13

SQLite can be compiled by myself, but I download it already compiled as a Windows DLL.

Download page: http://sqlite.org/download.html

For own assembly usually download so-called. "Amalgamation" ,
those. SQLite sources in a single C + sqlite3.h file.

To reduce the size of the SQlite code, throwing out unnecessary nishtyaki, use any DEFINE.

How popular is SQLite?

In short: it is everywhere. At least on any smartphone.

How reliable is it?

Highly. When a version is released, it passes through a series of serious automatic tests (~ 2 million tests are performed), the code covers 100% of tests (since August 2009).

What other tools do the developers give?

A console utility for working with databases (sqlite3.exe, “a command-line shell for accessing and modifying SQLite databases”) is available.

And all?

Yes, from the main developers - everything. However, other people write all sorts of managers, etc.
Personally, I have not found the perfect and use the console.

What does “fairly complete set of SQL” mean?

As you know, in its development SQL rushed in different directions. Large manufacturers began to cram all sorts of extensions. And although all standards are accepted (SQL 92), in real life all large databases do not support standards completely + have something of their own. So, SQLite tries to live by the principle of “minimal but complete set”. It does not support complex things, but largely corresponds to SQL 92.
And introduces some of its features that are very convenient, but - not standard.

What exactly in SQL support may cause confusion?

You cannot delete or change a column in a table (ALTER TABLE DROP COLUMN ..., ALTER TABLE ALTER COLUMN ...).
There are triggers, but not as powerful as those of large RDBMS.
There is support for foreign key, but by default it is DISABLED.
There is no built-in support for UNICODE (but, in general, it is not difficult to achieve).
No stored procedures.

What is your good or unusual?

a) each record contains a virtual column rowid, which is equal to a 64-bit number (unique for the table).
You can declare your INTEGER PRIMARY KEY column and then this column will become rowid (with its name, the name rowid still works).
When inserting a record, you can specify a rowid, or you can - do not specify (and the system will then insert a unique one).
Details: www.sqlite.org/autoinc.html
b) you can easily organize a database in memory (this is very convenient and I will tell you more later);
c) easy to transfer: by default, the database is one file (in a cross-platform format);
d) the type of the column does not determine the type of the stored value in this field of the record, that is, any value can be entered in any column;
e) many built-in functions (which can be used in SQL): www.sqlite.org/lang_corefunc.html;

I do not understand - what's up with the type? Why do we need a column type then at all?

The type of a column determines how to compare values ​​(you need to reduce them to a single type when comparing, say, within an index).
But it does not oblige to enter values ​​of this type in the column. Something like weak typing.

Suppose we declared a column as "A INTEGER".
SQlite allows you to add values ​​of any type to this column (999, “abc”, “123”, 678.525).
If the inserted value is not an integer, then SQlite tries to bring it to the whole.
Those. the string “123” turns into a whole 123, and the remaining values ​​are written “as is”.

So it is possible not to set column type at all?

Very often this is done: CREATE TABLE foo (a, b, c, d) .

What about architecture? There is no server?

There is no server, the application itself is a server. Access to the database occurs via the “connections” to the database (something like the OS file handle), which we open by calling the corresponding DLL function. When opening, the name of the database file is indicated. If there is no such thing, it is automatically created.
It is permissible to open multiple connections to the same database (via the file name) in the same or different applications.
The system uses file-level locking mechanisms for the OS to make it all work.
(these mechanisms usually do not work well on network drives, so it is not recommended to use SQlite with a file on the network).
Initially, SQlite worked on the principle of "many read - one writes."
That is, only one connection writes to the database at a given time. If other connections try to write too, they get the error SQLITE_BUSY.
You can, however, enter a timeout of operations. Then the connection, faced with the employment of the database, will wait N seconds before falling off with an SQLITE_BUSY error.

And how to be?

Either one connection and all queries through it, or proceed from a possible timeout and provide for repeating the execution of SQL.
There is one more possibility: not so long ago a new kind of SQlite log appeared: Write Ahead Log, WAL .
If you enable this particular log mode for the database, then several connections will be able to simultaneously modify the database.
But in this mode, the database already takes several files.

Well now it is clear why SQLite is terrible, because it does not have a GLOBAL CASH?

Indeed, all modern RDBMS are unthinkable without a global shared cache, which can store all sorts of nishtyaki like compiled parameterized queries. This is a busy server, which is not here. However, within a single application, SQlite can share the cache between multiple connections (read here: www.sqlite.org/sharedcache.html ) and save some memory.

And why does everyone complain that SQLite slows down?

Two reasons. The first is the default settings. They work for reliability, not performance.
The second is a lack of understanding of the mechanism for fixing transactions. By default, after any SQlite command, it will commit the transaction (that is, wait until the database is in a consistent state to turn off the power). Depending on the paranoia mode, SQLite will spend from 50 to 300 ms on it (waiting for the data to be written to disk to finish).

What to do? I need to insert 100 thousand records and quickly!

Delete indexes, turn on OFF (or NORMAL) synchronization mode, insert in portions of N thousands (N - pick up, first take 5000). Before inserting portions make BEGIN TRANSACTION, after - COMMIT.

But I found a mistake! How to report?

No

The fact is that the popularity of SQLite is terrible - it is everywhere. It is not joke.
And the developers were faced with a shaft of error messages that were either caused by misunderstanding, or were hidden feature request. They, in fact, closed the direct reception of reports with errors.
So you should subscribe to the mailing list and describe the problem there and hope for the best.

Personally, I had a situation that I interpreted as a SQLIte defect. I described this in the mailing list. In the next version, the SQLite behavior was fixed.

A handy utility to play around with SQLite.

To be continued.