SQLite notes
For some introduction, see [[]]
Errors
String or BLOB exceeded size limit seems to mean that one or both of:
- a query size exceeded SQLITE_MAX_SQL_LENGTH (1MB by default)
- you can avoid this by using parametric values (binding) instead of literal query strings
- a row, text field, or blob field exceeded SQLITE_MAX_LENGTH (1GB by default)
(possibly other limits)
You can set these these while compiling. SQLITE_MAX_LENGTH may be 2^31-1, and SQLITE_MAX_SQL_LENGTH may be no larger than SQLITE_MAX_LENGTH (and possibly also no larger than 1GB?(verify) -- not that wanting a 2GB query is very sane...
Multiple processes
Multiple processes can read the same database. Only one can write, as attempts to write will locks the entire database file. It's up to clients to decide to error out complaining about that lock, or wait and retry.(verify)
Note that locking will not work over NFS, and there are issues with SMB / windows file sharing you probably want to know about.
Note you should not carry an open connection though a fork.
Say the writers: "SQLite is not designed to replace Oracle. It is designed to replace fopen()"
Multiple threads
SQLite can be compiled to be thread-safe, and usually is (the reason you can compile it without that is that that removes mutexes and is slightly faster for cases you know will always be single-threaded) You can query that compile option from the database if you want to be sure.
This page points out there is a further distinction between multi-threaded (where threads must not share a connection) and serialized (where that appears to be fine).
Some libraries may have their own decisions - e.g. python3's sqlite3 will complain SQLite objects created in a thread can only be used in that same thread
though that seems to be overzealous
On SQLite typing
This page mentions a lot of what you want to know.
For contrast: where most RDBMSes are statically typed (converts data to the column's type on storage, fails if it can't)...
...SQLite is more like dynamic typing in that SQLite decides the type from the value you give it, and only somewhat cares about the according column's type.
A little more precisely, the schema defines a type affinity, not a rigid type.
- 'type affinity' here meaning 'the value may still be of any type, but we prefer this one if that works'
The SQL types map to the affinity of INTEGER, REAL, NUMERIC, TEXT, BLOB, or NULL
- e.g. INTEGER is split into seven specific-sized things -- on disk, anyway; in memory all are loaded into int64(verify)
- TEXT is stored as UTF-8, UTF-16LE, or UTF-16BE (according to the database encoding) (verify)
For example(verify)
- if you store into a numeric column, it will do something like
- if not well formed as an integer or real, it's stored as TEXT
- if it's a real number (with digit) and store as REAL if (seems like float64 so if there was more precision in the text that will be lost)
- if it's an integer, and find the smallest integer type that will store it
- if it's an integer larger than int64 can store, it will try REAL
- hex is stored as text
Assume that the schema is not used for conversions going out
AFAICT, what you get out is whatever type that got stored, and it is the logic on insert that is interesting.
...at least, that's what synamically typed languages tend to do with it. Statically typed languages (including SQLite's onw API) may get you to convert it, and/or makes you test for it(verify).
On SQLite concurrency
See also: