Does Calibre really use SQLite?

[inhaledesign]

Calibre's API docs state that metadata.db is an SQLite database. However, looking at the schema, there are types like BOOL and TIMESTAMP that are not supported by SQLite.

Is this not actually an SQLite database, or am I missing something?

[Sarmat89]

SQLite doesn't really use the SQL types.

[kovidgoyal]

Yes it really uses sqlite. sqlite doe snt use types, the types in the schema are for documentation, not for sqlite.

[inhaledesign]

Originally Posted by kovidgoyal

Yes it really uses sqlite. sqlite doe snt use types, the types in the schema are for documentation, not for sqlite.

That's a strange thing to say, given SQLite has a whole section of documentation on types they support and don't support (I provided a link in my original post).

I'm also currently trying to open the Calibre DB in an SQLite framework that won't accept it because I can't match the schema, so saying that "schemas are for documentation" is off the mark.

Anyways, I dug a bit deeper and found that while SQLite3 doesn't support those types, SQLite2 does. So I'm guessing that the Calibre DB is based on the latter.

[kovidgoyal]

No, calibre is based on sqlite3. Try this little experiment. Download the official sqlite3 binary. Run

sqlite3 :memory:
SQLite version 3.39.2 2022-07-21 15:24:47
Enter ".help" for usage hints.
sqlite> CREATE TABLE authors ( id   INTEGER PRIMARY KEY,
                              name TEXT NOT NULL COLLATE NOCASE,
                              sort TEXT COLLATE NOCASE,
                              link TEXT NOT NULL DEFAULT "",
                              UNIQUE(name)
                             );
sqlite> .schema
CREATE TABLE authors ( id   INTEGER PRIMARY KEY,
                              name TEXT NOT NULL COLLATE NOCASE,
                              sort TEXT COLLATE NOCASE,
                              link TEXT NOT NULL DEFAULT "",
                              UNIQUE(name)
                             );
sqlite>

Astounding!! There are types in that table and sqlite3 didnt complain!! Who'da thunk it!!

[inhaledesign]

Originally Posted by kovidgoyal

No, calibre is based on sqlite3. Try this little experiment. Download the official sqlite3 binary. Run

sqlite3 :memory:
SQLite version 3.39.2 2022-07-21 15:24:47
Enter ".help" for usage hints.
sqlite> CREATE TABLE authors ( id   INTEGER PRIMARY KEY,
                              name TEXT NOT NULL COLLATE NOCASE,
                              sort TEXT COLLATE NOCASE,
                              link TEXT NOT NULL DEFAULT "",
                              UNIQUE(name)
                             );
sqlite> .schema
CREATE TABLE authors ( id   INTEGER PRIMARY KEY,
                              name TEXT NOT NULL COLLATE NOCASE,
                              sort TEXT COLLATE NOCASE,
                              link TEXT NOT NULL DEFAULT "",
                              UNIQUE(name)
                             );
sqlite>

Astounding!! There are types in that table and sqlite3 didnt complain!! Who'da thunk it!!

Those are all types that are supported by SQLite3.

[inhaledesign]

So, maybe this is what's going on: you're using SQLite *the program* to interact with your database, but you're not using SQLite *the standard* as the basis for your database. Is this correct?

[kovidgoyal]

Nope, calibre uses the sqlite library, not the program. And there is no sqlite *standard*. And even if there was one, you cannot use a standard to do anything.

And finally, try this

CREATE TABLE authors ( id INTEGER PRIMARY KEY, name OH_MAN_WHAT_A_TYPE);

I highly doubt OH_MAN_WHAT_A_TYPE is supported by whatever sqlite *standard* you think you have found. And yet this table will succeed.

[DNSB]

Would it help to realize that the name of a table is not necessarily related to the datatype name? Or that sqlite has flexible types though sqlite 3 can use strict datatypes (as of sqlite 3.37).

[inhaledesign]

Originally Posted by kovidgoyal

Nope, calibre uses the sqlite library, not the program. And there is no sqlite *standard*. And even if there was one, you cannot use a standard to do anything.

And finally, try this

CREATE TABLE authors ( id INTEGER PRIMARY KEY, name OH_MAN_WHAT_A_TYPE);

I highly doubt OH_MAN_WHAT_A_TYPE is supported by whatever sqlite *standard* you think you have found. And yet this table will succeed.

Thank you for providing a relevant example this time.

Again, the standard I was referencing is the official SQLite documentation. I provided it as a source so their could be mutual context, which is helpful when having a constructive conversation.

According to the documentation, SQLite attempts to infer the type affinity from the type provided, so to say that the provided type is *merely* documentation is incorrect. It has practical consequences for how the data is stored, and this isn't irrelevant. I'm currently wrestling with issues related to this feature of SQLite and the Calibre schemas. While SQLite allows you to use whatever type you want in order to play nice with SQL broadly, at no point does it *recommend* using whatever types you want. If you're going to use SQLite to create a database from scratch, it'd be better to stick strictly to the defined types (again, the supported types are clearly stated in the link I provided).

Anyways, I have the answer to my question (through my own research), and this conversation has not at all been constructive.

[JSWolf]

Seems to me that we got the answer and that to me makes this thread very constructive.

[kovidgoyal]

A hint for the future. If you want to have "productive" conversations dont start out by telling someone that has been using sqlite for 15 years highly successfully their API docs are wrong. And that too based on a cursory reading of documentation (which is exactly that, not a standard). Read section 3.1 of the documentation you linked to and you will be able to tell the type affinity of every column in the calibre database. Which is anyway entirely irrelevant since sqlite actually uses types per value not per column. You can store text in integer columns and integers in blob columns if you want.

[j.p.s]

Originally Posted by kovidgoyal

A hint for the future. If you want to have "productive" conversations dont start out by telling someone that has been using sqlite for 15 years highly successfully their API docs are wrong. And that too based on a cursory reading of documentation (which is exactly that, not a standard). Read section 3.1 of the documentation you linked to and you will be able to tell the type affinity of every column in the calibre database. Which is anyway entirely irrelevant since sqlite actually uses types per value not per column. You can store text in integer columns and integers in blob columns if you want.

I was browsing sqlite documentation a few years ago and that really stood out to me.
A column can be nominally double precision, but if the content of that column in a given row is inserted or updated as 1.0 it will actually stored as a short integer (or maybe even byte).

[kovidgoyal]

Yes indeed that is a big gotcha about sqlite, especially if you are used to more strict RDBMSes. calibre is actually designed to do most data manipulation on an in-memory data structure, not via SQL operations, it just uses SQLITE as dumb store. The main exception to that being full text search, though even there the tokenization is actually done by cusotm code I wrote not the sqlite tokenizer.