3 ms·
SQLite is a file format with a familiar API and uses SQL as the logic for searching/adding data to the file. Approach it exactly the same way you'd approach us
by bane 7y ago
SQLite is a file format with a familiar API and uses SQL as the logic for searching/adding data to the file.
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 https://www.sqlite.org/fileformat.html
Here's an example of somebody who's done this.
https://sqljet.com/ 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.
- catalogia 7y ago> No! SQLite is not an embedded RDBMS. It's a file format. 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);
- bane 7y agoYou can read/write CSV files, JSON, JPEG, WAV, MP3, MP4, etc. into memory as well. That doesn't make any of them an RDBMS. 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?
- catalogia 7y agoBeing a file format is only one aspect of what SQLite is. SQLite describes itself as "SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine." I think that is a better, more encompassing, description than "it's a file format" 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.
- bane 7y agoSure, I can get behind that. The logic that handles the read/writing and SQL parsing is all part of the library for sure. 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.
- cma 7y agoSQLite is a whole library with complicated locking protocol options etc. and works with multiple files of different formats (e.g. separate transaction log).
- bane 7y agoSince the file format specification is available, you can write your own code that directly reads/writes to SQLite files. You can't even have to use the logs and journaling options. You don't even have to use SQL.
- happymellon 7y agoFile formats don't have an API. > 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.
- bane 7y ago> File formats don't have an API. 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.
- deleted 7y ago[deleted]