Monday, August 29, 2011

Prepopulate SQLite DataBase in PhoneGap Application

It is really hard to wait for too much time when a phonegap application runs for first time and create all its database stuff, which gives a really bad impression to the user who just downloaded the app for its iPhone or Android device. I have a little approach to save the long time taken for database creation at very first run of the app, that is; we should copy a Pre-Populated Database file to the native location of device.

You can create a basic database for app with the help of tools like SQLite Database Browser or if you already have a database in your app you can directly get the database files (you need Databases.db and file__0/0000000000000001.db files) from the following ways:


In case of Android: in File Explore(in Eclipse) go to  data > data > [YOUR APP NAME] > app_database


In case of iOS: go to Finder (in MAC) go to Library > Application Support > iPhone Simulator > [YOUR SIMULATOR VERSION] > Applications > [YOUR UNIQUE APP ID] > Library > WebKit > Databases

NOTE: for iOS and Android apps both of files are same we don't need to grab both files for same phonegap apps.


With these files all what you need to do is; just put them in to bundle of app that is assets folder in case of Android and Root folder in case of iOS.
in Eclipse: in XCode:


Now you have to copy the files at the native location of application at the time of first boot of application, and make sure that the files should copy before your very first SQLite query. To copy these files you can use the following code snippet based on your environment. JAVA (Android)
//Use this code in your bootstrapping steps like in onCreate()
        try
     {
         String pName = this.getClass().getPackage().getName();
         this.copy("Databases.db","/data/data/"+pName+"/app_database/");
   this.copy("0000000000000001.db","/data/data/"+pName+"/app_database/file__0/");
 }
     catch (IOException e)
 {
  e.printStackTrace();
 }


        //Copy Paste this function in the class where you used above part
 void copy(String file, String folder) throws IOException 
 {

  File CheckDirectory;
  CheckDirectory = new File(folder);
  if (!CheckDirectory.exists())
  { 
   CheckDirectory.mkdir();
  }

     InputStream in = getApplicationContext().getAssets().open(file);
     OutputStream out = new FileOutputStream(folder+file);

     // Transfer bytes from in to out
     byte[] buf = new byte[1024];
     int len; while ((len = in.read(buf)) > 0) out.write(buf, 0, len);
     in.close(); out.close();
     
 }


Objective C (iOS)
//For PhoneGapDelegate.m
- (void)webViewDidStartLoad:(UIWebView *)theWebView 
{
 NSString *databaseName = @"0000000000000001.db";
 NSString *masterName = @"Databases.db"; 
 
 NSArray *libraryPaths = NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES);
 NSString *libraryDir = [libraryPaths objectAtIndex:0];
 
 NSString *masterPath = [libraryDir stringByAppendingPathComponent:@"WebKit/Databases/"];
 NSString *databasePath = [libraryDir stringByAppendingPathComponent:@"WebKit/Databases/file__0/"];
 NSString *masterFile = [masterPath stringByAppendingPathComponent:masterName];
 NSString *databaseFile = [databasePath stringByAppendingPathComponent:databaseName];

 BOOL success;
 NSFileManager *fileManager = [NSFileManager defaultManager];
 
 success = [fileManager fileExistsAtPath:databasePath];
 if(success) return;
 
 NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];
 NSString *masterPathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:masterName];
 
 [fileManager createDirectoryAtPath:databasePath withIntermediateDirectories:YES attributes:nil error:NULL];
 [fileManager copyItemAtPath:databasePathFromApp toPath:databaseFile error:nil];
 [fileManager copyItemAtPath:masterPathFromApp toPath:masterFile error:nil];
 [fileManager release];
}

49 comments:

  1. hi,

    so, how do we execute opening a SQLite Db in our js files?

    ReplyDelete
  2. hello,

    could you please help me in showing how to access Sqlite in your js file? tnx

    ReplyDelete
  3. can you please show us your html file as well. I keep getting sqlite error that says cannot open the file.

    ReplyDelete
  4. I use your method but I keep getting sqlite error that cannot open file. Can you please show your javascript code as well? thanks

    ReplyDelete
  5. Hi Gaurav, I have a problem. I have pre-populated a .db file thru android native code. I have renamed the db file as 0000000000000001.db. I have placed this file and Databases.db under my Assets folder. Now when I am copying these 2 files to "data/data/"+pName+"/app_database/file__0/" folder, Phonegap is unable to find this file. And a new file is creatd each time Phonegap application loads. the name of this file is 0000000000000002.db in the same folder and it is a blank database.
    So am I missing something? or did I do something wrong.

    regards
    santu ghosh

    ReplyDelete
  6. Hi Gaurav, I have a problem. I have pre-populated a .db file thru android native code. I have renamed the db file as 0000000000000001.db. I have placed this file and Databases.db under my Assets folder. Now when I am copying these 2 files to "data/data/"+pName+"/app_database/file__0/" folder, Phonegap is unable to find this file. And a new file is creatd each time Phonegap application loads. the name of this file is 0000000000000002.db in the same folder and it is a blank database.
    So am I missing something? or did I do something wrong.

    regards
    santu ghosh

    ReplyDelete
  7. Santu. I think you need to copy DB files to the different filders.

    this.copy("Databases.db","/data/data/"+pName+"/app_database/");

    but

    this.copy("0000000000000001.db","/data/data/"+pName+"/app_database/file__0/");

    ReplyDelete
    Replies
    1. Please tell the steps to convert a native DB file to PhoneGap like style(0000000000000001.db and Databases.db)?

      Delete
  8. Please tell the steps to convert the native Android DB file to phonegap like style(0000000000000001.db and Databases.db)?

    ReplyDelete
  9. thanks gaurav for writing this post. its really very useful. keep it up.

    ReplyDelete
  10. Dear Gaurav,

    I am having prepopulated database in SQLITE
    When I am Pushing it to Device, I can see it in File Explorer.
    But, I am not getting how to use this pushed database in javascript code.
    Whenever I am using
    window.openDatabase("DBName", "1.0", "New DB Demo", 200000);

    It creates new databse with the name app_database:DBName

    Any idea about why this is happening?

    Thanks,
    Amol

    ReplyDelete
  11. Dear Gaurav,

    I am having prepopulated database in SQLITE
    When I am Pushing it to Device, I can see it in File Explorer.
    But, I am not getting how to use this pushed database in javascript code.
    Whenever I am using
    window.openDatabase("DBName", "1.0", "New DB Demo", 200000);

    It creates new databse with the name app_database:DBName

    Any idea about why this is happening?

    Thanks,
    Amol

    ReplyDelete
  12. Hi Gaurav, This article is very useful but now i have a problem. I have being developed phonegap application for iOS that is used pre populated .db file. This solution is working fine for below iOS 5.1. But I upgraded to IOS 5.1 , this solution does not to work.
    Can you give me a solution for that. I am stuck now.
    Thank you

    ReplyDelete
  13. Hi Gaurav!

    Thank you very much for share useful code. I have a question, how I can add rows and save in the Prepopulate database?

    ReplyDelete
  14. Thank you Gaurav!
    How I can insert aditional data into a Prepopulate database, and use them later close and open the app?

    ReplyDelete
  15. Hi Gourav, your trick is working fine for high end devices & android 2.3.
    But i have problem low end devices & android 2.2.
    Can you help me?

    ReplyDelete
  16. Hi... Can you tell me where can I put the code listed above. I am developing for Android without any prior knowledge in JAVA. Can you explain in specific details ? Thanks

    ReplyDelete
  17. Thanks for this post.. I'm getting a FileNotFound exception... Please help...

    ReplyDelete
  18. Thanks for this post.. I'm getting a FileNotFound exception... Please help...

    ReplyDelete
  19. Found my 'bug'... Placed the DB files in a wrong folder.. Placed them in 'assets/www' instead of 'assets'... Awesome way to pre-populate a database! Thanks!!!

    ReplyDelete
  20. Is is possible to do the same with BlackBerry WebWorks

    ReplyDelete
  21. Is it possible to do the same with BlackBerry WebWorks?

    ReplyDelete
  22. Hi Gaurav, how do i achieve same task for blackberry webworks platform. plz reply

    ReplyDelete
    Replies
    1. did you find a solution to ur problem
      i also need to do the same thing
      use a pre populated db for blackberry webworks
      any help would be highly appreciated
      thanks

      Delete
    2. did you find a solution to ur problem
      i also need to do the same thing
      use a pre populated db for blackberry webworks
      any help would be highly appreciated
      thanks

      Delete
    3. were you able to do the same thing using blackberry???

      Delete
    4. were you able to do the same thing using blackberry webworks>???

      Delete
  23. Gaurav, this is very nice. Thank you. For your objective C code, can this run every time the app runs or do you somehow need to tell it to only run the first time the app loads?

    ReplyDelete
  24. Hi Guarav,
    This is really great.

    For the Objective C code, do i need to tell this code to only run the first time, or does it automatically know to only run this code when the app first runs?

    ReplyDelete
  25. A more up to date version that I worked with and made a guest post on Ray Camden's website:
    http://www.raymondcamden.com/index.cfm/2012/7/27/Guest-Blog-Post-Shipping-a-populated-SQLite-DB-with-PhoneGap

    ReplyDelete
  26. A more recent version of the methods described here:
    http://www.raymondcamden.com/index.cfm/2012/7/27/Guest-Blog-Post-Shipping-a-populated-SQLite-DB-with-PhoneGap

    ReplyDelete
  27. Hello Gaurav,
    I have integrated above code in my Phonegap application for iOS. Database is successfully loading in my case, as I have compared 0000000000000001.db with my original database and both are same. In javascript, I have open database like this:
    function onBodyLoad() {
    var db = window.openDatabase("0000000000000001", "1.0", "MDStand DB", 1000000);
    db.transaction(queryDB,errorDB);
    }
    function queryDB(tx) {
    tx.executeSql('SELECT * FROM mds_menu',[],querySuccess,errorDB);
    }

    function querySuccess(tx, results) {
    alert('success');
    }
    function errorDB(err) {
    alert("Error processing SQL: "+err.code);
    }

    Everytime it shows me alert "Error processing SQL : undefined"

    Let me know, if I am missing anything over here.

    Thank You.

    ReplyDelete
  28. I have integrated above code in Phonegap app for iOS. In my case, database is successfully loading as 0000000000000001.db is exactly the same as my original database. I am stuck here with retrieving the records from database. Below is my code, I have used for the same:
    function onBodyLoad() {
    var db = window.openDatabase("0000000000000001", "1.0", "MDStand DB", 1000000);
    db.transaction(queryDB,null);
    }
    function queryDB(tx) {
    tx.executeSql('SELECT * FROM mds_menu',[],querySuccess,errorDB);
    }

    function querySuccess(tx, results) {
    alert('success');
    }

    function errorDB(err) {
    alert("Error processing SQL: "+err.code);
    }

    Everytime, I am getting erro alert "Error processing SQL: undefined".

    Please help me out in this. Let me know, if I am missing anything over here.

    Thank You.

    ReplyDelete
  29. Is anyone using a prepopulated database on iOS 5.1 and if so, how are you doing it? Thank you!

    ReplyDelete
  30. Is anyone using a prepopulated database on iOS 5.1 and if so, how are you doing it? Thank you!

    ReplyDelete
  31. Muy interesante este tema, como seria si si estoy trabajando con html5 js y css3 como podria hacer esto mismo algún manual o algo que me recomiendes

    ReplyDelete
  32. how to upload 000000001.db file to server by using file transfer api of phonegap?

    i am uploading 000000001.db file to server by using file transfer api of phonegap but it gives me error
    file not found error code=1
    can anyone help me out how to get rid of this thing.

    ReplyDelete
  33. do you know how to connect remote database with phonegap??can you give an example plssss

    ReplyDelete
  34. its not worked for me for android can u sepicified any method for android

    ReplyDelete
  35. its not worked for me in android can u please specified the methods clearly or any other

    ReplyDelete
  36. Where do you add the Java code, the whole point of using PhoneGap is to avoid using Java, can you at least mention where this code is to be added.

    ReplyDelete
  37. Hi Gaurav,
    I have a problem while using the db file in ios 5.1 and later, I'm not able fetch the records from the db using.

    ReplyDelete
  38. Hi Gaurav,
    I have a problem while using the db file in ios 5.1 and later, I'm not able fetch the records from the db using.

    ReplyDelete
  39. Hi Gaurav,
    I'm unable to fetch the records from the .db file in ios5.1 and later, even though my db is successfully copied to webkit.
    Thanks in Advance

    ReplyDelete
  40. Great post . It takes me almost half an hour to read the whole post. Definitely this one of the informative and useful post to me. Thanks for the share.wordpress development in delhi Elesoftech is a leading offshare web development,mobile application, iphone application

    ReplyDelete
  41. Hi,
    Everyone who has posted questions...
    Can u provide a sample example code for Android and iOS ..??
    So others can get help and no need to post anymore questions..
    Any help is appreciated.

    Thanks in Advance

    ReplyDelete
  42. Good able to copy the database and easily accessible

    ReplyDelete