3 ms·

I am interested in more details/criticisms of the above use case. Better approaches? or "Holy balls, that's idiocy!"

by ahi 14y ago

simcop2387 14y ago
I would hope it would be simpler to manage via some kind of clustered filesystem instead nested databases.
mark_story 14y ago
I really hope you never have to do a schema migration. That's going to be hell with your matryoshka database.
columbo 14y ago
Hrm... 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 ago
  That'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.

I am interested in more details/criticisms of the above use case. Better approaches? or "Holy balls, that's idiocy!"

by ahi 14y ago

I am interested in more details/criticisms of the above use case. Better approaches? or "Holy balls, that's idiocy!"

simcop2387 14y ago

I would hope it would be simpler to manage via some kind of clustered filesystem instead nested databases.

mark_story 14y ago

I really hope you never have to do a schema migration. That's going to be hell with your matryoshka database.

columbo 14y ago

Hrm... 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 ago
That'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.