08-13-2022, 10:19 PM | #1 |
Junior Member
Posts: 5
Karma: 10
Join Date: Aug 2022
Device: Onyx Boox Nova 3
|
Does Calibre really use SQLite?
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? |
08-13-2022, 11:10 PM | #2 |
Evangelist
Posts: 484
Karma: 2267928
Join Date: Nov 2015
Device: none
|
SQLite doesn't really use the SQL types.
|
Advert | |
|
08-14-2022, 12:24 AM | #3 |
creator of calibre
Posts: 44,019
Karma: 22669822
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
|
Yes it really uses sqlite. sqlite doe snt use types, the types in the schema are for documentation, not for sqlite.
|
08-16-2022, 02:07 PM | #4 | |
Junior Member
Posts: 5
Karma: 10
Join Date: Aug 2022
Device: Onyx Boox Nova 3
|
Quote:
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. |
|
08-16-2022, 02:22 PM | #5 |
creator of calibre
Posts: 44,019
Karma: 22669822
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
|
No, calibre is based on sqlite3. Try this little experiment. Download the official sqlite3 binary. Run
Code:
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> |
Advert | |
|
08-16-2022, 04:37 PM | #6 | |
Junior Member
Posts: 5
Karma: 10
Join Date: Aug 2022
Device: Onyx Boox Nova 3
|
Quote:
|
|
08-16-2022, 04:45 PM | #7 |
Junior Member
Posts: 5
Karma: 10
Join Date: Aug 2022
Device: Onyx Boox Nova 3
|
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?
|
08-16-2022, 08:51 PM | #8 |
creator of calibre
Posts: 44,019
Karma: 22669822
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
|
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. |
08-17-2022, 12:37 AM | #9 |
Bibliophagist
Posts: 36,810
Karma: 147879470
Join Date: Jul 2010
Location: Vancouver
Device: Kobo Sage, Libra Colour, Lenovo M8 FHD, Paperwhite 4, Tolino epos
|
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).
Last edited by DNSB; 08-17-2022 at 12:40 AM. Reason: Edit: add sqlite 3 version for strict datatypes |
08-17-2022, 11:12 AM | #10 | |
Junior Member
Posts: 5
Karma: 10
Join Date: Aug 2022
Device: Onyx Boox Nova 3
|
Quote:
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. |
|
08-17-2022, 11:34 AM | #11 |
Resident Curmudgeon
Posts: 74,648
Karma: 130140792
Join Date: Nov 2006
Location: Roslindale, Massachusetts
Device: Kobo Libra 2, Kobo Aura H2O, PRS-650, PRS-T1, nook STR, PW3
|
Seems to me that we got the answer and that to me makes this thread very constructive.
|
08-17-2022, 11:46 AM | #12 |
creator of calibre
Posts: 44,019
Karma: 22669822
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
|
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.
|
08-19-2022, 03:31 PM | #13 | |
Grand Sorcerer
Posts: 5,323
Karma: 98809518
Join Date: Apr 2011
Device: pb360
|
Quote:
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). |
|
08-19-2022, 10:47 PM | #14 |
creator of calibre
Posts: 44,019
Karma: 22669822
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
|
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.
|
Tags |
schema, sql |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
calibre sqlite schema | underscore | Development | 1 | 03-21-2018 11:24 AM |
.sqlite in calibre | quarkpott | Calibre | 7 | 07-10-2017 10:06 AM |
SQLite FTS3/4 & Calibre 2.0+ | DaltonST | Development | 6 | 08-28-2014 11:02 AM |
SQLite | Meeky | Kindle Developer's Corner | 2 | 01-01-2013 11:02 AM |