2 ms·

It'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. Th

It'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 ago
Agree. 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 ago
  I'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 ago
    Postgres 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.
bane 14y ago
Oh it's clearly some of the best/worst kind of mad-scientist type of madness. But it can often be tolerably handled with a row-level flag in the outer-database to handle "locking" the database if you are operating in a scanrio where that makes sense. There are other scenarios where users simply aren't writing anything to the inner SQLite databases, but you are simply using it as a storage medium. Imagine getting a SQLite database of publicly accessible government data instead of a csv dump or some unparsable XML file. Some organizations have already caught on to the idea, the NTSB for example gives out MS-Access databases of preliminary crash investigations. I just think it's a crazy fun hacker-style idea that I've seen from time-to-time as a replacement for even harder to work with custom binary file formats stuffed into a relational database (don't ask, there are oddball projects where that kind of thing gets specified -- like certain high-end modeling systems where the user might check out a model to work on in isolation of the parent system, the data might be stored as SQLite databases instead of some proprietary bit bucket, and then uploaded back to the server in total where the model data is yanked out for server side processing). The upside is that you can replace two sets of tools (relational tools and custom binary tooling) for working with data with one set of tools (just the relational) which ends up clarifying things quite a bit. The above scenario, where isolating the user's datapool so that they can take it offline, do work, then check it back in to the system is almost absurdly simple. But honestly, there's a very very limited number of use-cases where this makes sense and doing something more sane makes much more sense -- but when it works, it's ends up being one of those "so stupid it's brilliant" things.
- fusiongyro 14y ago
  > But it can often be tolerably handled with a row-level flag in the outer-database to handle "locking" the database if you are operating in a scanrio where that makes sense. You do that and you're just trading one race condition for another. > There are other scenarios where users simply aren't writing anything to the inner SQLite databases, but you are simply using it as a storage medium. It's a pretty labor-intensive method. Many databases have other ways of introducing unstructured data that aren't going to murder performance. PostgreSQL's hstore, for example. > the user might check out a model to work on in isolation of the parent system, the data might be stored as SQLite databases instead of some proprietary bit bucket, and then uploaded back to the server in total where the model data is yanked out for server side processing I have a friend who has used SQLite as an interchange format. It worked reasonably well, but he also didn't have to worry much about schema versions. Still, you can do that without making your backend pay the penalty. > The above scenario, where isolating the user's datapool so that they can take it offline, do work, then check it back in to the system is almost absurdly simple. It's still more work than using a file system for no advantage. Just having it in the database doesn't necessarily constitute a win—most DBAs would suggest you weigh the filesystem alternative against dumping files into BLOBs in the database before deciding which way to go. There will always be exceptions to any hard and fast rule, but I don't think the ones you've described above warrant this design.

neeleshs 14y ago

Agree. 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 ago
I'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 ago
  Postgres 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.

bane 14y ago

Oh it's clearly some of the best/worst kind of mad-scientist type of madness. But it can often be tolerably handled with a row-level flag in the outer-database to handle "locking" the database if you are operating in a scanrio where that makes sense. There are other scenarios where users simply aren't writing anything to the inner SQLite databases, but you are simply using it as a storage medium. Imagine getting a SQLite database of publicly accessible government data instead of a csv dump or some unparsable XML file. Some organizations have already caught on to the idea, the NTSB for example gives out MS-Access databases of preliminary crash investigations. I just think it's a crazy fun hacker-style idea that I've seen from time-to-time as a replacement for even harder to work with custom binary file formats stuffed into a relational database (don't ask, there are oddball projects where that kind of thing gets specified -- like certain high-end modeling systems where the user might check out a model to work on in isolation of the parent system, the data might be stored as SQLite databases instead of some proprietary bit bucket, and then uploaded back to the server in total where the model data is yanked out for server side processing). The upside is that you can replace two sets of tools (relational tools and custom binary tooling) for working with data with one set of tools (just the relational) which ends up clarifying things quite a bit. The above scenario, where isolating the user's datapool so that they can take it offline, do work, then check it back in to the system is almost absurdly simple. But honestly, there's a very very limited number of use-cases where this makes sense and doing something more sane makes much more sense -- but when it works, it's ends up being one of those "so stupid it's brilliant" things.

fusiongyro 14y ago
> But it can often be tolerably handled with a row-level flag in the outer-database to handle "locking" the database if you are operating in a scanrio where that makes sense. You do that and you're just trading one race condition for another. > There are other scenarios where users simply aren't writing anything to the inner SQLite databases, but you are simply using it as a storage medium. It's a pretty labor-intensive method. Many databases have other ways of introducing unstructured data that aren't going to murder performance. PostgreSQL's hstore, for example. > the user might check out a model to work on in isolation of the parent system, the data might be stored as SQLite databases instead of some proprietary bit bucket, and then uploaded back to the server in total where the model data is yanked out for server side processing I have a friend who has used SQLite as an interchange format. It worked reasonably well, but he also didn't have to worry much about schema versions. Still, you can do that without making your backend pay the penalty. > The above scenario, where isolating the user's datapool so that they can take it offline, do work, then check it back in to the system is almost absurdly simple. It's still more work than using a file system for no advantage. Just having it in the database doesn't necessarily constitute a win—most DBAs would suggest you weigh the filesystem alternative against dumping files into BLOBs in the database before deciding which way to go. There will always be exceptions to any hard and fast rule, but I don't think the ones you've described above warrant this design.