4 ms·
Sqlite is really just one of those rare software projects that simply makes the world a better place. No more thinking about file storage formats, searching and
by bane 14y ago
Sqlite is really just one of those rare software projects that simply makes the world a better place. No more thinking about file storage formats, searching and indexing strategies, long term file readability (it's almost as future proofed as ASCII), extensible, flexible, fast and all around awesome.
The number of projects where sqlite turned a difficult project from impossible or very hard to doable is almost uncountable.
Here's a fun use case. Generate sqlite databases for each user, let them do whatever they want in that database from whatever interface you are letting them use. Store the sqlite file as a binary blob in a "real" database with associated metadata for fast look up and retrieval, plus whatever other things your database gives you (replication, redundancy etc.) And now you basically get sand boxed work spaces for your users almost for free.
- ahi 14y agoI am interested in more details/criticisms of the above use case. Better approaches? or "Holy balls, that's idiocy!"
- simcop2387 14y agoI would hope it would be simpler to manage via some kind of clustered filesystem instead nested databases.
- mark_story 14y agoI really hope you never have to do a schema migration. That's going to be hell with your matryoshka database.
- columbo 14y agoHrm... an online application perhaps like autocad where you have hundreds/thousands of bits of information for every user's "file"? Instead of saving all that data in xml/json you could keep it in a database. This would also allow you to release in versions of the main software by keeping older users and their files on the previous version. Even as I type this I start to see massive alarm bells but it was something that came to mind. Another option is an external application (like survey monkey) that publishes a sqlite file that you could then use in your system to store data for that external form without modifying your database... again much pain could be had down this road.
- bane 14y agoThat's almost exactly the use case I've seen it used for. A custom modeling software with collaboration features. Modellers built models in local client software which stored the model as a SQlite database and u/l them to a central MySQL database for distribution. When changes were made to the model, a new one was simply uploaded and a flag was set in the old one to take it off line (or was it only the newer versions were available unless the user requested an older one...I can't remember to be honest). The metadata about the model gave details like the user who created it, dates and times of upload, name of the model, various key words about the model etc. "Replacing" an older version with a newer version just involved uploading the new version into a new row in the database and copying over a dozen or so meta-data fields. Once the model user downloaded a model, it was as easy to work with on their end with a variety of tools as new tooling simply had to be written to the schema not to some bizarro binary format or parse some gargantuan text file into a data structure. Plus elements in the model were already indexed in the SQLite file so internal searches for model elements was trivial. There simply wasn't a case where two users would be writing to the same SQLite inner database, but dozens could read them without problem as that was all handled by the outer database, and it preserved old versions of the models without issue. It also meant migrating to a better outer database meant only migrating a fairly simple meta-data schema and extracting out the SQLite database then just writing it all back into the new database. It took a few days to migrate a few TB but a very short time to write all the migration scripts...an hour or so. The plus side is a modeler could take their model off-line with them and work on them at their leisure, then upload them when they came back into work. I was never personally involved in the project, but thought it was one of the craziest MacGuyver-esque things I'd ever seen. But once I stopped thinking about the SQLite inner databases as databases and more as just file blobs with a convenient to work with standard format it all made sense. The outer database was never responsible for searching/reading/writing the inner databases, that all happened at the client end.
- DrJokepu 14y agoIf you do this that would mean that you could have no shared locks safely on the SQLite database while you have a reserved lock, that is, if one process is planning to write to the database, other processes cannot even read it. Even then, you would have to be very very careful if you want to avoid losing data. Basically, unless you can (and willing to) write your own SQLite VFS that is designed to handle this safely, I wouldn't do it.
- fusiongyro 14y agoIt's a clever idea. Please don't do it. SQLite has one very limited write scenario: one writer at a time. Your real database isn't going to understand that. The real database will happily hand out the inner SQLite database to multiple connections. At best, you'll be converting SQLite's locking into a bunch of transaction failures for conflicting updates; at worst, you'll have a race condition where only the last of several edits gets persisted. On top of that you have the disk churn associated with rewriting the database on every change. Suppose your SQLite database is 20 MB—every write to that means 20 MB from the real database to your application and back, which means 20 MB of disk thrashing too. Guess how many users need to do this before your database is spending all day rewriting essentially unchanged bytes. Depending on your storage backend, you can't even necessarily expect that the old ones are overwritten—with MVCC, the database may wind up just allocating new blocks, in which case you're creating a lot of garbage collection work and wasting a lot of space. Every real database has a facility to let you isolate schemas from each other. In MySQL, you can create separate databases for each user; in PostgreSQL you can do that or just create separate schemas. There are better solutions to this problem that don't have the drawbacks identified above. It's a cute idea, but in practice I don't think it will work out at all well.
- neeleshs 14y agoAgree. I have seen similar irritating problems in the past with huge JSON blobs stored in a real database and application acting upon the JSON. The concurrency issues on writing this blob and the contention was terrible.
- bane 14y agoI've seen a bunch of similar ones where they just dumped XML blobs in as well. It's a terribly common usage pattern that doesn't work well in the general sense, but can work in some specific scenarios. It's a shame databases don't provide better support for this kind of pattern.
- fusiongyro 14y agoPostgres has a nice XML type. The advantage to doing this is at least you can rely on the database to guarantee well-formedness and use XPath functions to do things like query the XML from SQL and build indexes on stuff buried within. Postgres 9.2 will have JSON support as well, but lacking accompanying functions it will be of limited utility.
- malkia 14y agoPerfect replacement for what you usually have in game dev studios - lots of small loose files (we have about 380,000 of those) - materials, textures, shaders, models, animations, etc. No easier format than just reusing SQLite.
- netshade 14y agoDepending on the goal you're trying to achieve, it could be a great tool, but I wouldn't diss the filesystem too much. You lose the discoverability of all your fs tools, a whole suite of APIs no longer becomes available (async IO, say), and you throw another access layer in front of your data w/ a corresponding performance drop. Not to say that SQLite wouldn't offer lots of advantages, but I'm not sure I'd be in a huge rush to start packing all my game assets into a db unless I had some pretty convincing wins. I'd be much more likely to stick w/ the 380k in development and then pack into contiguous level binfiles before shipping. ( I'm totally armchair coding though, I admit :) )
- gouranga 14y agoThat's a filesystem isn't it (not NFS as locks don't work properly on NFS filesystems)? 1. One per user - in home directory. Check. 2. Real database with metadata - filesystem. Check. 3. Replication - filesystem (block replication). Check. 4. Redundancy - filesystem (RAID). Check. So technically it already does that without any need for further abstraction. You can use UNIX perms to set it read only for all and read-write for user, then you can share it :) For reference, I've built a HUGE amount of stuff with sqlite3 - you are right it does make the world a better place! We use it for: 1. unit test back end for NHibernate as it's fast and in memory. 2. issue/tickets/wiki (trac) 3. data processing. 4. archival datasets. 5. general swiss army knife when you can't be bothered to do it in SSIS.