Versioning SQLite databases with Subversion. Good idea or bad idea?

Question

So I have a web system where every user gets a separate SQLite database which acts as a sort of their workspace. All of the database files are in a directory with database names that match up to the user ids.

Many of these databases are created and rarely, if ever, edited. Others have many edits on a daily basis.

To manage backups on this directory would you recommend using Subversion set up with a cron process? One reason I am considering this approach is it would allow for the users to roll back their edited workspace easily.

Answer 1

Are SQLite database files clear-text files or binary? If they are binary I don't think you benefit from using Subversion that much (as IMHO it shouldn't be used as a backup system).

Instead if you want to allow them to restore an older version use a normal backup mechanism (rsync or the like) and save a daily backup for a certain period of time (depends on your actual requirements). If they want to restore a previous version they can approach you and you play it back in.

Answer 2

If you can run your own shell scripts you could dump every database to plain SQL file:

# sqlite3 user.db .dump .exit > user.sql

and then you could store the SQL file in Subversion repository

Creating database back from SQL file:

# sqlite3 -init user.sql user.db .exit

Answer 3

Many of these databases are created and rarely, if ever, edited.

If this is the case, then you will not get a whole lot of benefit out of Subversion. It is a version control system, not a backup system. You will not be wanting to do version control operations like diff, merge, etc on the objects, so I think you will get better mileage out of a traditional backup method. Compress the sqlite files (as much as you can) and archive them for a specified length of time. If you are running this on a Linux system, you can look at backup scripts like the logrotate scripts that do the same thing for system logs. Since sqlite databases are nice and cleanly packaged in a single file, restoring an old version can be easily scripted (just un-compress and copy to the correct location).

Depending on how much data each user is storing in their database, one additional alternative is to keep shadow copies of tables in your repository. When a change is made, a copy is made of the old table before it is modified. This way you can keep a copy of the previous (or even the last several) revision(s) in the database and can restore them with a simple database operation instead of having to go digging out old backups. This also lets you revert changes to part of the database without reverting the entire thing.

Answer 4

one thing you could do is:
* Always export your database to sql file in development time.
* Convert the sql file to database file either in an MSBuild Custom task or at any convenient initialization step when your application starts up.
* To import the sql file back into a database file, you could use C# similar to:

            string sql = System.IO.File.ReadAllText(SqlFile);
            SQLiteConnection.CreateFile(DatabaseFile);

            string ConnectionString = @"Data Source=" + DatabaseFile;

            using (SQLiteConnection cn = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(sql, cn))
                {
                    cn.Open();
                    cmd.ExecuteNonQuery();
                }//cmd
            }//cn

This way you can just keep you sql files in SVN, allowing you to have editable mergeable files and just generate your bin db files on the fly either in the build process or when the application starts.

If you chose to build the db binary file in your application start, you could also make it an in-memory database in case your application did not have enough permissions to write to disk. your connection string should then be:

Data Source=:memory:;Version=3;New=True;