Distributed databases
SebastianEngel opened this issue · 9 comments
Currently it is possible to provide some SQL scripts for database setup.
Do you also plan to support the usage of distributed databases?
Could you elaborate?
Sure. Referencing the wiki, the developer using the the lib can create the database with provided migration scripts, or "Alternatively if you don't create an SQL file you can create your database programatically with the normal Android librarys ( n.b. this is what the SQLiteProvider library is doing for you automagically."
Sometimes there are cases when you need to distribute the app with a pre-filled database. A database that is setup and has some values in it, so that the application doesn't need to download a bunch of data at first startup. In some cases you could argue, that this can be done with insert-scripts. That's true for static, non-changing data. But in some cases the data you want to deliver with the app, is not static and coming from a server and just provided to avoid a full initial download at app start.
That would mean to provide a ready-to-use prepared .db file instead of some setup scripts.
There is a project on Github that does just that (https://github.com/jgilfelt/android-sqlite-asset-helper), but I could imagine that it could be comfortable to have that functionality it in your lib too. Maybe you could use the sql scripts for db setup when those are existing in the asset folder. If there are no sql scripts but a *.db file, just using that and doing no database setup.
What do you think?
@SebastianEngel Sorry for taking so long. I read through your suggestion and it seems to be a potential fit. Then again there's already the android-sqlite-asset-helper you mentioned with a lot of overlapping codebase for this potential feature.
We need to discuss if this bigger change is within the scope of this lib but it's an interesting request for sure.
A comment @blundell @malmstein @devisnik @charroch ?
Mhh pre-filled database .. potential as a feature but then would people want more features on top of it? My initial thoughts where if another library does it extremely well, do we need to also do it?
@blundell exactly my thoughts. I guess the benefit of using the prefilled db through our provider is still attractive.
Maybe it would be possible to build an additional package next to core which bridges the functionality of the asset helper library to our one. That way we wouldn't need to duplicate and maintain functionality while offering a one-stop solution for this use case.
Would you think it would be worth exploring?
One issue with prepackaged DB is SQLite versioning. In the early days, the version used could change between OS versions and manufacturers. Foreign key support was added via the pragma later and behaviour of one SQLite version would differ to the other.
I would suggest to run the insert as part of the migration:
CREATE TABLE user(_id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO user(1, "Halbert");
Playing with transactions could help as well potentially to speed things up.
Alternatively, we could have a dump in and out function (sqlite hello.db .dump > whole_db.db and sqlite hello.db < cat whole_db.db)
The easiest way to deliver this functionality is to add an optional field that hints to the path of the "custom" db. If this field is present the migrations would run against this database rather than an autocreated one.
This way one could (maybe?) use both libraries alongside each other - this asset helper for deployment/maintenance and the SqliteProvider for easy access.
Did I miss something with this solution?
Distributed Databases sounds exciting! Are you talking about syncing federated datastores (...) or merely delivering an app with a prepopulated database?
Allowing .db files as well as .sql files to bootstrap a database sounds only natural (a pull request).
It basically means copying a file on first launch https://github.com/jgilfelt/android-sqlite-asset-helper/blob/master/library/src/main/java/com/readystatesoftware/sqliteasset/SQLiteAssetHelper.java#L453-L455 Do I miss something here?
Why would you want to do it?
Speed? Copy and open a file is probably faster than bulk inserting sql statements. How much faster?
Size? Is a (zipped) db file smaller than a (zipped) sql text file? With precalculated indexes I could imagine it being even larger. Since the apk is already zipped does it make a difference to also zip an asset file?
In any case a database that is large enough for such benefits to be worth it would make the apk file unnecessarily large (and difficult to update). After extracting and copying all data sits in three places.
On the other hand downloading prepopulated db files could make a ton(!) of sense.
It might be THE most efficient way to bulk download+insert lots(!) of sqlite data. A server needs to precompute such .db files only once (for all the clients), they zip really well and can be modularily downloaded on-demand and attached to an opened database http://www.sqlite.org/lang_attach.html ...
Closing as this repository is no longer under maintenance and it's about to be archived