How to programmatically fill a database

Question

Hi There,

I currently have an iPhone app that reads data from an external XML file at start-up, and then writes this data to the database (it only reads/writes data that the user's app has not seen before, though)

My concern is that there is going to be a back catalogue of data of several years, and that the first time the user runs the app it will have to read this in and be atrociously slow.

Our proposed solution is to include this data "pre-built" into the applications database, so that it doesn't have to load in the archival data on first-load - that is already in the app when they purchase it.

My question is whether there is a way to automatically populate this data with data from, say, an XML file or something. The database is in SQLite. I would populate it by hand, but obviously this will take a very long time, so I was just wondering if anybody had a more...programmatic solution...

Answer 1

Not to take Jason's answering thunder, I can't comment yet so it has to be here.

The nice thing is that you can access the filesystem of the simulator right on your Mac. I am away from mine at the moment or I could tell you exactly where to look, but I just find it by putting the name of the db file into searchlight and just running with that.

You also do not need to wright any code to populate the db since you can use the command line tool to do the initial setup if that is more convenient.

You will need to copy it over though since resources are stored in the read only signed portion of the app bundle.

Answer 2

I had the same problem of you using sqlite, on massive insert it's really slow. So the best way it's provide directly a filled sqlite database.

You have another way, instead of INSERT INTO, to populate a sqlite db. You can produce a csv file for each table and load into the tables using your computer and the sqlite shell:

Just 2 simple commands:

.separator SEPARATOR
.import FILE TABLE 

Example:

adslol:~ user$ sqlite3 
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .separator ;
sqlite> .import myData.csv nameOfMyTable
sqlite> .quit

I hope it's what you was looking for :)

If you need a good client for sqlite3 try SQLite Manager, it's a Firefox add-ons.

Answer 3

I'm going to flesh out Jason's answer, I've marked my post as a community wiki so I shouldn't get any points for this.

He's not talking about a dummy app - write the app as you normally would, but check to see if the database exists in your main bundle before you call the code that populates the plist. You run that in the simulator, pull out the generated sqllite database, and add it to your project - if you only need to read from it, you can read it from the main bundle directory. If you need to do further writes then copy it into the writable documents area, and use it from there. So basically for the main user, the code to populate the DB would never be called...

The only downside is you also end up including the plist files you are reading from, even though you only need the database. You could make a different build target that was a copy of the main one with the only difference being that it held the plist files, while the main target you built for the app store did not.