2 Replies Latest reply on Aug 5, 2008 6:55 PM by durbonix

    Distributing a pre-populated database - how & other questions

    DEfusion
      In the Strategies for working with SQL databases in the livedocs it talks about distributing a pre-populated database. I'd like to do this for a couple of reasons, but the main one is to keep all the table create and default data insert statements out of the app itself.

      I've created and populated a SQLite database using the command line sqlite3 tool, this is located within my src/ directory of my project, similar to this example I read (which can be seen in the download). However doing the following:

      _dbFile = File.applicationStorageDirectory.resolvePath("test.db");

      Open "a" database but it can't be the one I have in my src/ directory as it then complains about missing tables when I try any queries.

      I've looked at the compiler options and can't see anywhere to include it, any ideas?

      Also what is the strategy employed when doing application upgrades (say I add a table or column in future, but don't want to lose the users existing data), I don't expect anyone to write a massive answer - links to read would be good though.

      Thanks,

      -D
        • 1. Re: Distributing a pre-populated database - how & other questions
          Dr. Fred Mbogo Level 1
          The copy you put in src may get put in the application storage directory on installing, but it certainly won't be there during development. The application storage directory is buried deeply under the user's home folder, and predicting the actual path is difficult. If you have multiple versions of an application installed, each will get a different directory.

          Because of all this uncertainty, I gave up on this in my application. Instead, during startup, my program looks for a few data files it needs, and if they aren't there, it downloads them from the program's web site. The trickiest part of this is that you have to write your program so that it disables parts of the UI that depend on the data files until they arrive.

          An advantage of this is that you can update the canonical versions of the files on the server without shipping a new version of the program. If someone roaches their data file, you can tell them how to delete it, then have them relaunch the program to get a clean copy. Beats reinstalling it.
          • 2. Re: Distributing a pre-populated database - how & other questions
            durbonix
            Using Flex Builder 3, I have a support folder in which I have a pre-populated database. Upon firstrun, I look to see if the database is in the applicationStorageDirectory, and if not I copy it over:

            var db:File = File.applicationDirectory;
            db = db.resolvePath('presets' + File.separator + 'sqliteDatabase.db');
            var dest:File = File.applicationStorageDirectory;
            dest = dest.resolvePath('sqliteDatabase.db');
            if ( ! dest.exists )
            {
            db.copyTo(dest);
            }

            Another option would be to have the SQL for building the database in a text file, and execute it on first run.