2,617 karma · joined October 16, 2012
Well, there is this: https://www.loc.gov/preservation/resources/rfs/data.html
Also, the on-disk format for SQLite has been extended, but has not fundamentally changed since version 3.0.0 was released on 2004-06-18. SQLite version 3.0.0 can still read and write database files created by the latest release, as long as the database does not use any of the newer features. And, of course, the latest release of SQLite can read/write any database. There are over a trillion SQLite databases in active use in the wild, and so it is important to maintain backwards compatibility. We do test for that.
The on-disk format is well-documented (https://sqlite.org/fileformat2.html) and multiple third parties have used that document to independently create software that both reads and writes SQLite database files. (We know this because they have brought ambiguities and omissions to our attention - all of which have now been fixed.)
The expression "CAST('321a' AS INTEGER)" will do as you suggest and ignore the trailing 'a' character, yielding an integer 123 result. But that only happens for an explicit CAST. Automatic type conversions must be reversible. That means that '321a' is inserted as a string in an INTEGER column, but '321' (without the trailing 'a') will be converted into an integer 123.
PostgreSQL, MySQL, and SQL Server do exactly the same thing for the '321' case. For the '321a' case, the other three throw an error whereas SQLite just cancels the type conversion and inserts the original string.
People have been appending ZIP archives to executables, in order to hold non-code resources, for time out of mind. The appendvfs extension makes the same thing possible for SQLite databases. An SQLite database has advantages over a ZIP archive in that SQLite supports a far richer data model, is far faster for random access, and has a query language. Both ZIP and SQLite are well-defined and very widely deployed formats. Many developers are more familiar with ZIP, but there are more than 1 trillion SQLite database files in circulation, and SQLite is a recommended storage format according to the US Library of Congress (https://www.sqlite.org/locrsf.html) so SQLite should not be dismissed as being too unfamiliar.
Example use cases:
(1) We have experimented with (but not published) putting all the SQLite documentation into an SQLite database and appending it to a special webserver app. Download the "EXE" and double-click on it and the SQLite docs automatically pop up in your web browser. This is better than a pile-of-HTML-files in that it can use server-side computing for things like the "Search".
(2) Not yet published or documented, but you can do "make sqltclsh" from the SQLite source tarball and generate a TCL interpreter with SQLite built in. If you also append an SQLite database to this interpreter, it reads its scripts from the database. Use this to build stand-alone Tcl/Tk/SQLite applications.
(3) By 3rd-party user request: the Fossil version control system allows a Fossil repository (which is just an SQLite database) to the end of the "fossil.exe" binary. This is being used (I am told) to provide a rich package of read-only but versioned content to non-technical users. The non-techies just put the "document.exe" file on there windows desktop and double-click, and a webserver pops up showing the reports they need, with complete historical versioning provided by Fossil.
The standard build of SQLite uses an OS, but there is a compile-time option to omit the OS dependency. It then falls to the developer to implement about a dozen methods on an object that will read/write from whatever storage system is used by the device. People do this. We know it works. We once had a customer use SQLite as the filesystem on their tiny little machine.
Likewise, the use of malloc() is enabled by default but can be disabled at compile-time. Without malloc(), your application has to provide SQLite a chunk of memory to use at startup. But that is all the memory that SQLite will ever use, guaranteed. Internally, SQLite subdivides and allocates the big chunk of memory, but we have mathematical proof that this can be done without ever encountering a memory allocation error. (Details are too long for this reply, but are covered in the SQLite documentation.)
Finally, we do have proof that SQLite can continue after a power cycle - assuming certain semantics provided by the storage layer. Hence, the proof depends on your underlying hardware and those methods you write to access the hardware for (1) above. But assuming those all behave as advertised, SQLite is proof against data loss following an unexpected power cut. We have demonstrated this by both code analysis, and experimentally.
So probably you were correct to write your own database in this case. My point is that SQLite did not miss your requirements by quite as big a margin as you suppose. If you had had a bigger hardware budget (SQLite needs about 0.5MB of code space and a similar amount of RAM, though the more RAM you feed it the faster it runs) then you might have been able to save yourself about two years of development effort.
I don't know if SQLite would be faster or slower in the context suggested here. But I'd like to suggest that perhaps it is worth running the experiment before reaching a conclusion.
I wrote Fossil specifically to support SQLite development. If Fossil does nothing else other than support SQLite, then it is a success. Any other use of Fossil is just gravy. That we were conservative in moving the main SQLite source code into Fossil does not negate that fact.
We do also use Fossil for dogfooding SQLite. See, for example, item 15 on the release-testing checklist: https://www.sqlite.org/checklists/3230000/index
I just wrote the referenced article last night. Normally, I takes months or years before something like this gets picked up and discussed on HN, and I have more time to refine the text. This one snuck up on me. Come back in a month or two and the article will probably be much improved. You are reading an initial draft.
On the other hand - it is a funny cartoon, don't you think? And it does kind of capture how most people use Git in a snarky kind of way, doesn't it? :-)
The README.md file is the homepage. It is intended to be displayed using the URL https://wapp.tcl.tk/index.html/doc/trunk/README.md and the hyperlinks are relative to that URL. When you click on the File menu, it shows the README.md file using a different URL - http://wapp.tcl.tk/index.html/dir?ci=tip - and the hyperlinks don't work for that URL.
Perhaps the right solution is to rename the README.md file to something different so that it is not displayed by default from the Files menu...
Also, SQLite is embedded, not client/server. That means that the application needs to run on the same machine that holds the data. And because there is no server to coordinate access, concurrency is necessarily limited.
On the other hand, SQLite arrived just in time to get picked by smart phones, and is consequently the most-deployed database software in the world. There are far more instances of SQLite running today than there are MySQL instances.
That screen contains all and only the information I want to see. (No surprise, since I wrote that screen.) I would very much like to read more details from Xwattt about what he (or she) finds messy, unimportant, and inadequate about the timeline view of Fossil and to perhaps see examples of better presentations of development history.
I wonder if Xwattt has tried clicking on two of the check-in circles in the graph from the link above, in order to get a diff between the two selected check-ins? Is that information not useful?
What of the filtering options in the sub-menu? Is clicking on "Files" to see all the individual files changes in each check-in not helpful?
Is clicking on a branch-name tag to see a timeline of just that one branch not something that other people ever want to do?
Seriously - I'm not trolling here. I honestly what to grok what it is that Xwattt finds inadequate about the Fossil web interface, as understanding this will help to make the interface better.
That's my theory, too. I never needed to run "bisect" until I had the capability to do so. Now I can't seem to live without it.
The other day, I had 60 minutes of free time between events and so I brainstormed a few ideas for improving Fossil while sitting in a Starbucks, and those unedited, spur-of-the-moment notes trigger a big discussion on HN... Yikes! I do appreciate the feedback. Seriously. Your comments are very, very helpful. But let's not attach too much weight to my musings over coffee.
Should I interpret the response here to mean that there is latent demand for a new-and-improved VCS in the world. Does this mean that Git is ripe for disruption?
Some Issues I Have With Git
(1) A Git repository is a pile-of-files and/or a bespoke key/value store (packfiles). The format of the repository is underdocumented. (Proof sketch: try to write a utility that reads content out of a git repository without first studying the git source code.) The repository format is also brittle, as evidenced by the difficulty the Git developers have had trying to add support for hash algorithms other than SHA1.
(2) The key/value design of Git limits the information you can extract from the repository. Example: It is difficult to find the descendants of a check-in in Git - so difficulty that nobody ever does it. You can find ancestors easily, but finding all the descendants of a check-in is very hard. In addition to depriving the user of useful information, the inability to find descendants of a check-in leads directly to the "disconnected head" problem. That one deficiency is a show-stopper for me. And this is but one example of the limitations imposed by the key/value design of Git.
(3) For people who don't want to put their trust in GitHub, setting up a Git server is way too difficult.
(4) Git requires the user to remember too much state information. Git users should be cognizant of (a) the current check-out, (b) the "index" or staging area, (c) the local head, (d) the local copy of the remote head, and (e) the actual remote head. The more mental power users must to devote to keeping track of Git, the less there is available to work on their own code.
(5) Git only allows one check-out per repository. (I am told there are resent extensions to git to try to address this deficiency, but I am also told they do not work very well.)
(6) Git does not do a good job of remembering branch history. In particular, branches are unnamed in Git.
(7) Git is for file versioning only. Other important project information, such as bug tracking, must be handled separately.
Fossil is an effort to address the problems above. I do not claim that Fossil is perfect, just that it is better than Git. I am keen to make Fossil even better. Your feedback is appreciated.
The origin is Linus Torvalds on the Git mailing list. Here is a copy from LWN: https://lwn.net/Articles/193245/
The quote is ironic considering that Git uses a bespoke and not particularly well-designed key/value database, which has resulted in notorious usability problems in Git.
The cut-down SQL would include just the basics: CREATE TABLE, CREATE INDEX, INSERT, UPDATE, DELETE, SELECT. No triggers or views. No support for partial indexes or indexes on expressions, common table expressions, or other such features. There would be a minimal set of built-in SQL functions, but with support for application-defined SQL functions written in javascript.
A competing implementation would not need to understand the SQLite file format nor the complete SQLite language nor would it need to implement the SQLite API. All it would need to do is support the basic SQL subset defined by the spec.
If you get the browser vendors on-board with this idea, and I'll write the spec.
Because ZIP is a key/value store and SQLite is relational.
(Later:) Here is a slide comparing SQLite to ZIP from a talk I'm scheduled to give on Monday: https://sqlite.org/tmp/sqlite-v-zip.jpg
Both SQLite and ZIP will store files and both are well-established open formats with a trillion instances in the wild.
SQLite does everything ZIP does but also gives you transaction, a rich query language, a schema, and the ability to store small (1-8 byte) objects and to translate objects into the appropriate byte-order for the reader.
Or, you could do it several dozen other ways. For example, maybe you have an archiver tool for SQLite that works like "zip" - https://www.sqlite.org/sqlar/doc/trunk/README.md
About this much we agree: The objective is to make the content of the document more accessible and easier to use. The article intended to show that using an SQLite database rather than a pile-of-files in a ZIP archive achieves that goal. If my presentation were stored in an SQLite database, I might (depending on how the schema was structured of course) be able to ask interesting questions of the presentation, such as:
* How many pages are in this presentation?
* Which pages contain the word "discombobulated"?
* How many images are in the presentation?
* Does this presentation use a prepackaged background?
* Does the presentation begin with my companies standard "start" slide?
I have 104 historical presentations in a folder and I want to find some slide I wrote years ago. Right now, I have to laboriously open each presentation and manually search for the slide I want. If the format were SQLite, I could perhaps do the same with a query, which if run from a shell script, would quickly search all 104 presentations for me.
So, Yes, the whole point is to make the content more easily accessible and usable. Relative to a pile-of-files in a ZIP archive, SQLite does exactly that.
Pen-testers do still occasionally find minor problems. See https://www.sqlite.org/src/info/04925dee41a21ffc for the latest example. But generally speaking, it is safe to open an SQLite database received from an untrusted source. If you are extra paranoid, activate the "PRAGMA cell_size_check=ON" feature and/or run "PRAGMA integrity_check" to verify the database before use.
The SQLite database file format has been unchanged (except by adding extensions through well-defined mechanisms) since 2004. And we developers have pledged to continue that 100% compatibility through at least the year 2050. The file format is cross-platform and byte-order independent. We have an extensive test suite to help us guarantee that each new version of SQLite is completely file compatible with all those that came before. The file format is documented (https://sqlite.org/fileformat.html) to sufficient detail that the spec alone can and has been used to create a compatible reader/writer. (I know this because the authors of the compatible reader/writer software pointed out deficiencies in the spec which I subsequently corrected.) The file format is relatively simple. A reader for SQLite could be written from the spec with no more effort than it would take to implement a ZIP archive reader with its decompressor from spec.
There is approximately 1 trillion (10^12) SQLite database files in active use today. Even without the promises from developers, the huge volume of legacy database files suggests that the SQLite database file format will be around for a long time yet.
The latest version of SQLite will happily read and write any SQLite3 database written since the file format was designed in 2004. Ancient legacy versions of the SQLite library will read and write databases created yesterday, as long as those databases do not use any of the extensions that were added later.
See https://www.sqlite.org/pragma.html#pragma_secure_delete for further information.
It is fine to use SQLite as the local storage in the implementation of some custom server like Fossil, as there is a separate server (Fossil) that sits in between the client and the data. In fact, SQLite excels at this and usually works better than traditional client/server databases. The scenario you want to avoid is where the client attempts to access SQLite data directly across the network, with no intermediary server. The proverb is: "Always send your query to the data, not the data to the query."
It will work to access an SQLite database across a network filesystem. Just remember that the amount of information that flows between the SQL engine and the storage medium is much greater than the amount of information that flows between the SQL engine and the application. So if the storage is separated by from the application by a relatively slow network, you want to position that network on the low-bandwidth link to obtain the best performance. That means that the SQL engine needs to be on the same side of the network as the storage - hence a client/server database. If you use SQLite to access a network file, the SQL engine will be on the application side of the network and a lot more content will need to traverse the relatively slow network link.
And so, while SQLite will work in a client/server situation, you might be disappointed by the resulting network load and/or performance of the system.
In the case of Fossil, Fossil itself is the "client" for SQLite and it is on the same machine as the data, which is exactly what you want with SQLite. The client of the Fossil server might well be across a network, but that does not matter to SQLite.