> SQLite is a file format with a familiar API and uses SQL as the logic for searching/adding data to the file.
That description is for a library, not a file format. SQLite is a library, that saves to a convenient format and allows you to query the file using the SQL syntax. File formats dont have "logic".
You are arguing the equivalent that Word is a file format. While there is a Word file format, Word itself is an application.
So when you read/write to any other file format, you just read/write bytes directly to/from disk and re-implement the parsing and read/write logic in your own code every time?
> File formats don't have "logic".
Every file format has logic otherwise it's just random entropy in an arbitrarily long byte stream on a disk. How to read/parse and interact with that format depends entirely on the logic and scheme for that file. For example, many file formats have an index of some kind that you must read and parse before you can figure out where the other data lives, compressed file formats often store a dictionary, image formats often have a compression/decompression logic that must be followed for reading/writing to them.
> You are arguing the equivalent that Word is a file format. While there is a Word file format, Word itself is an application.
.docx is the file format for Word documents. There are many APIs and programs that can read/write to/from .docx files.
You keep saying it's a file format, but it's quite possible to use sqlite without persisting anything to a file at all.
rc = sqlite3_open(":memory:", &db);SQLite is a file format. It has a nice API and uses SQL as the domain language for read/write logic. If it didn't use SQL for the logic, would you still be confused?
Edit:
If SQLite lacked any ability to persist data to disk, it would still be very useful as an in-process SQL engine for many sorts of problems. Certainly not as useful as it currently is, but nevertheless still useful.
I'd say the file format without the SQL engine, or the SQL engine without the file format, would be like peanut-butter without jelly. Certainly not pointless, but the real magic comes from the combination of the two.
But that library is absolutely not required nor is SQL. One could build their own library that read/write to SQLite files entirely without any of that if they wished. Some people have done ground up rewrites in other languages other than C, but have more or less stuck with the same internal logic and the use of SQL as the read/write logic.
SQLite is obviously a library that stores it's data to a file like millions of other libraries. SQLite is to it's data file the way LAME is to MP3. LAME is not the file format, it's the library.
No they don't. There are many RDBMS (and other DBMSs) that do not store their information inside files.
> I can take a mdb file from SQL Server and copy it to another server and attach it there.
Yes, SQL Server is an RDBMS.
MDB files are files defined by a file format. This format defines something called a database. There are many libraries and other pieces of software than can read/write mdb files (https://jackcess.sourceforge.io/)
SQLite files are files defined by a file format (https://www.sqlite.org/fileformat.html). You can write code that reads and writes to a SQLite database file without using the library. So long as you follow the format specification, you will produce or be able to read from an aribtrary sqlite file that is produced by any code that implements the specification.
The SQLite library is a reference implementation of the specification as well as some sample logic for reading/writing to the files and using SQL to define the interactions with the file. The SQLite library is not required for this, nor is SQL, nor is the logic for ACID compliance.
SQLite is not an RDBMS and offers almost nothing that an RDBMS (or DBMS) might offer. If you wish to use SQLite files with an RDBMS you either have to build the RDBMS yourself, or find somebody else who's done so.
The more you consider the SQLite a file format, the easier it becomes to work with and understand. The more you try to consider it an RDBMS the less it makes sense.
Just because SQL is involved, doesn't mean it's an RDBMS.
You got it! SQLite is a file format.
> SQLite itself is a library that can deal with multiple files in different formats (transaction log and database file). It also reads .sqliterc and maybe others.
Yup you also got it! SQLite is not an RDBMS and shouldn't be approached that way.
The more you try to ram RDBMS ideas into what SQLite is, the more it won't be that. The more you try to treat it like a file format, the more it will be what you want.
Approach it exactly the same way you'd approach using a CSV file and all the confusion and overthinking about it goes away. Approach it as a stripped down RDBMS and you end up with all kinds of questions about support for this or that RDBMS familiar service.
You can write your own SQLite file reader/writer. Here's the specs (includes the specs for the Journal and WAL files and semantics as well) https://www.sqlite.org/fileformat.html
Here's an example of somebody who's done this. https://sqljet.com/ - this is not a wrapper on the sqlite C code, this is a re-implementation of that code that is binary compatible with SQLite files.
The Journal file only exists as a temporary file until transactions complete. The .sqlite file you make is the entire atomic file that follows the SQLite file format. The Journal has its own file format. Same goes for the Write-Ahead log.
RDBMSs also manage connection queues, account management, rights and permissions, and so on. Many overcome various OS limitations by providing their own entire file management, fopen(), virtual memory and other subsystems that are tuned to their workloads.
SQLite is a file format. SQLite uses familiar relational paradigms to make it easy to read/write data to the format without having to learn yet another API and domain language. The API code is extraordinarily well tested, and it makes simple complex logic like transaction journaling, indexing and so on.
>Consider: it’s totally possible to strip down Postgres until all you have left is an embedded RDBMS of the style of SQLite.
No! SQLite is not an embedded RDBMS. It's a file format.
If there was a library you could import, and it provided methods to read and write directly to files that PostgreSQL could read/write to and there was nothing else to install, no runtime, no daemons, no servers, etc., then we could pass around self-contained PostgreSQL files to each other. Then PostgreSQL files would be a file format as well.
Have you ever used a library to read/write from a CSV, JSON, JPEG? It's no different than doing so for a SQLite file!
SQLite is a file format.