3 ms·

I'm not downplaying anybody's work. Many people approach SQLite as something in the RDBMS territory. It's not. Almost all of the confusion I've ever seen relate

by bane 7y ago

I'm not downplaying anybody's work. Many people approach SQLite as something in the RDBMS territory. It's not. Almost all of the confusion I've ever seen related to SQLite comes from starting from that basis. If one simply thinks of it as an alternative to fopen() then it makes very simple and intuitive sense. The people in this thread seem to be very resistant to this simple clarity of thought, but whatever, they can stay confused and keep coming up with feature comparisons of SQLite vs Redshift vs Elasticsearch or some such. If one were to draw a spectrum: file-format:<-x----------------------------->:DBMS SQLite is the x on this line and .txt files are about the only thing that any further left on it.

imtringued 7y ago
There is no such spectrum. SQLite is a piece of software that implements some but not all commonly expected RDBMS features. Software is not a file format but software may be written with the expectation that a given file follows the requirements of a certain file format and the software may be written in a way that it produces files that follow the file format specification. Since SQLite - the software - is an RDBMS the files it produces can be considered to be databases.
- bane 7y ago
  I just drew the spectrum. It exists now. SQLite is a file format. You can read/write SQLite databases without the SQLite library. You can write your own custom reader/writer/creator. You don't have to use SQL. You don't have to be ACID compliant. I can make for you right now a SQLite database that did not touch any SQLite software, put data into it and you can open it with another piece of software that implements the SQLite file format specification. Likewise, you can use the SQLite library software to create a SQLite database file, put data into it, and I can read it/update it using any other software that implements the SQLite file format specification. The SQLite library offers some very very basic features, such as ACID compliance, and so on, but those are not part of or guarantees of the file format or the database files. The software that you write that implements the SQLite file format specification does not have to do any of these things to work with or produce a valid SQLite file. An RDBMS is a kind of DBMS for managing relational databases and providing access to the databases (for example users and permissions). Modern RDBMSs offer extensive features (look at an Oracle or MSSQL Server spec sheet) that are not even hinted at with the SQLite library software. This is because SQLite is not an RDBMS, it's a file format.
Izkata 7y ago
The impression given here is that you only use it as a dumb store of data. My experience is that it's more like: file-format:<------------------------x------>:DBMS
- bane 7y ago
  Why, because it offers SQL support? That just makes it a relational database that supports SQL. MS Access supports SQL. If you were to draw up a feature list of Oracle, PostgreSQL, MS SQL and SQLite, SQLite would have almost none of the features of any of the actual RDBMSs. Here's some examples from MS SQL: - Support to PMEM devices and bypassing OS storage mechanisms for optimal file read/write access - Availability Groups and synchronous replica pairs - Users and permissions - Secure Enclaves - Certificate management functionality - BI tools - Database tuning advisor - Machine Learning services - Service Broker - Replication services - Analysis Services - Reporting services - Notification services - Integration services and so on. Draw up a set of features for JSON files and jq and compare to SQLite. Is it closer to MS SQL or JSON?

by bane 7y ago

imtringued 7y ago

There is no such spectrum. SQLite is a piece of software that implements some but not all commonly expected RDBMS features. Software is not a file format but software may be written with the expectation that a given file follows the requirements of a certain file format and the software may be written in a way that it produces files that follow the file format specification. Since SQLite - the software - is an RDBMS the files it produces can be considered to be databases.

bane 7y ago
I just drew the spectrum. It exists now. SQLite is a file format. You can read/write SQLite databases without the SQLite library. You can write your own custom reader/writer/creator. You don't have to use SQL. You don't have to be ACID compliant. I can make for you right now a SQLite database that did not touch any SQLite software, put data into it and you can open it with another piece of software that implements the SQLite file format specification. Likewise, you can use the SQLite library software to create a SQLite database file, put data into it, and I can read it/update it using any other software that implements the SQLite file format specification. The SQLite library offers some very very basic features, such as ACID compliance, and so on, but those are not part of or guarantees of the file format or the database files. The software that you write that implements the SQLite file format specification does not have to do any of these things to work with or produce a valid SQLite file. An RDBMS is a kind of DBMS for managing relational databases and providing access to the databases (for example users and permissions). Modern RDBMSs offer extensive features (look at an Oracle or MSSQL Server spec sheet) that are not even hinted at with the SQLite library software. This is because SQLite is not an RDBMS, it's a file format.

Izkata 7y ago

The impression given here is that you only use it as a dumb store of data. My experience is that it's more like: file-format:<------------------------x------>:DBMS

bane 7y ago
Why, because it offers SQL support? That just makes it a relational database that supports SQL. MS Access supports SQL. If you were to draw up a feature list of Oracle, PostgreSQL, MS SQL and SQLite, SQLite would have almost none of the features of any of the actual RDBMSs. Here's some examples from MS SQL: - Support to PMEM devices and bypassing OS storage mechanisms for optimal file read/write access - Availability Groups and synchronous replica pairs - Users and permissions - Secure Enclaves - Certificate management functionality - BI tools - Database tuning advisor - Machine Learning services - Service Broker - Replication services - Analysis Services - Reporting services - Notification services - Integration services and so on. Draw up a set of features for JSON files and jq and compare to SQLite. Is it closer to MS SQL or JSON?