SQLite3 uses dynamic typing rather than static typing, in contrast to other flavors of SQL. The SQLite website reads:
Most SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static, rigid typing. With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored.
SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container.
It seems to me that this is exactly what you don't want, as it lets you store, for example, strings in integer columns.
The page continues:
...the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases.
I have two questions:
...the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases.
(10) Does SQLite support a BLOB type? SQLite allows you to store BLOB data in any column, even columns that are declared to hold some other type. BLOBs can even be used as PRIMARY KEYs.
A BLOB (large binary object) is an SQLite data type that stores large objects, typically large files such as images, music, videos, documents, pdf, etc. We need to convert our files and images into binary data (byte array in Python) to store it into SQLite database.
SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true). SQLite recognizes the keywords "TRUE" and "FALSE", as of version 3.23.
This is called type affinity in SQLite.
According to the SQLite website, they have done this "in order to maximize compatibility between SQLite and other database engines." (see the above link)
SQLite supports the concept of "type affinity" on columns. The type affinity of a column is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required. Any column can still store any type of data. It is just that some columns, given the choice, will prefer to use one storage class over another. The preferred storage class for a column is called its "affinity".
My understanding is that SQLite is exactly what it's named for - a very lightweight, minimalistic database engine. The overhead associated with strong typing is probably beyond the scope of the project, and best left to the application that uses SQLite.
But again, according to their website, they've done this to maximize compatibility with other DB engines.
If you look at, say, Firefox's "about:config" page, I believe these settings are actually stored in an SQlite database (I'm not 100% sure, though). The benefit of using SQlite's dynamic typing is that each value in the settings can be strong-typed (e.g. the "alerts.totalOpenTime" setting is an integer, while "app.update.channel" is a string) without having to have one separate column per type.
It's basically the same argument as for programming languages, in the end: why have dynamic typing in a programming language over static typing?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With