Large seed data sets in Appcelerator Titanium

The problem of updating and versioning database tables in Apcelerators’ Titanium API is made a little trixy by the nature of the number of inherent floors with what’s available, and a lack of comprehensive documentation. I’d already dealt with versioning within my model classes previously, but had glossed over the seed data.

My biggest problem was that one of the tables had to have just over 9000 rows (and perhaps more once the application is distributed) inserted. The only options available according to the documentation were to read the file in, explode on a new line char into an array, then munch through the array keys inserting the data as you go.

Possibly fine if the data is small, but will bomb the device as we’re talking about 2.2MB of text.

The solution it seemed was to use the new buffers object available I think since version 1.7 of the SDK. The solution is a bit kludgy and rather slow (so needs a some improvement), but it actually works, and most importantly the device survives the process.

So here’s the code:

var seedDataFile			 =	Ti.Filesystem.getFile(Ti.Filesystem.resourcesDirectory + "data/", model.table + ".sql");

if (seedDataFile.exists()) {
					
	var fileStream			 = 	seedDataFile.open(Ti.Filesystem.MODE_READ);
	var buffer 				 = 	Ti.createBuffer({ length: 1024 });
	var bytesRead			 = 	0;
	
	while ((length = fileStream.read(buffer, bytesRead, 1)) > 0) {
		
		bytesRead 			+= 	length;
		
		var str 			 = 	Ti.Codec.decodeString({
			
			source			 : 	buffer,
			charset			 : 	Ti.Codec.CHARSET_UTF8,
			length			 : 	bytesRead,
			position		 : 	0
		
		});
		 
		var eol 			 = 	str.charAt(str.length - 1);
		 
		if (eol == ";") {
		 	
		 	Model._db.execute(str);
		 	
		 	bytesRead 		 = 	0;
		 	buffer.clear();
		 	
		}
		
		str					 =	null;
		
	}
					
	fileStream.close();
	buffer.clear();
	buffer.release();
	
}

Basically it does the following:

1. Open the file into a fileStream object
2. Read one byte at a time into the buffer (in this case the buffer is 1Kb – so hopefully I won’t have an insert query longer than that).
3. Check to see if we’ve read in a semi-colon (could expand this to an nl char using regex, but I wanted to save on processing time).
4. If there’s a match, take the contents of the buffer, and use that as the query
5. Reset everything, and clear the buffer.
6. Continue on, and on etc…

Now, this worked fine in iOS, taking about 7 minutes to do the 9000 odd rows. Android, well that sorry state of affairs took nearly two hours. I’m thinking that there’s room for improvement there, but it will work for most cases. Hopefully this will help someone out.