ability to do a “hard” sql migration

[semafor]

Should we require to make changes to the rpiparticle sql, all we should have to do is

1. bump a sql schema version
2. release a new python-friskby version

A hard migration will then take place, where any existing sql database will be dropped.

An attempt at uploading any non-uploaded data should be made.

[pgdr]

Good idea, but why drop the existing db? Can't we migrate the db and continue using it?

[semafor]

Then you need to introduce migration semantics, which is maybe overkill for these data?

In the worst case, how much data can be lost? Given that an upgrade normally happens when there's an Internet connection (which means data can be uploaded before “migrating”).

[pgdr]

That's pretty easy to implement, and we don't really need any scheme version. We just let the update manager delete (or move to .bakup) the sql file, and then the Dao will construct it the next time it's invoked.

[pgdr]

I think this is relatively easy™ to backport this functionality, and there are several ways:

1. Your way; keep a file with a scheme and a version (and table name?), and the dao uses this
2. The update manager deletes the sql file if told, then the dao will automatically construct a new database according to updated specs
3. The dao deletes the sql file unless the scheme matches its own
4. Update the table name from samples to samples_v2 , and perhaps add a table current_scheme which tells us that samples_v2 is the one in use. The dao will know which to use, but if other users want to read the sql file without going through the dao, they might wanna know which is the current version.

Probably many other ways of doing this; not sure if there is one correct way but feel free to pick one; patches are very welcome.

Nota bene: we should make the dao pandas-friendly; either the possibility of exporting to pandas (with a conditional import), or at least make a row iterator, and columns (.keys()) function.