WebSQL for PhoneGap Application

Question

I'm looking to use a WebSQL to store data in an Android PhoneGap app but dunno where to start. There is no need to retrieve data from the web as the application is offline, and I want to have a prepopulated database that can be queried. A few questions: How do you create a pre-populated database? (what kind of file format should it be) If there are two factors can be changed, how do I get the result of a combination of these factors from the database using SQL. Will the database be wiped if the user clears app data from the Settings menu on an Android device? Grateful for any help. thanks

Answer 1

Prepopulate SQLite DataBase in PhoneGap Application

1) You can create a basic database for app with the help of tools like SQLite Manager or if you already have a database in your app you can directly get the database files.

2) Then you need to install Cordova/PhoneGap SQLitePlugin by CLI . (Cordova/PhoneGap SQLitePlugin)

cordova plugin add https://github.com/brodysoft/Cordova-SQLitePlugin

3) Addd following script file in your html.

<script src="plugins/com.brodysoft.sqlitePlugin/www/SQLitePlugin.js"></script>

4) If you are working for emulator then copy your databas using command prompt, I have added myDB.sqlite in asset folder then run .

adb push myDB.sqlite /data/data/com.my_app.my_app/databases/myDB.sqlite

5) For device, just put them in to bundle of app that is assets folder in case of Android . you need myDB.sqlite and file__0/0000000000000001.sqlite files.

6) 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). More...

    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        super.init();
        try {
            String pName = this.getClass().getPackage().getName();
            this.copy("myDB.sqlite", "/data/data/" + pName + "/databases/");
            this.copy("0000000000000001.sqlite", "/data/data/" + pName + "/app_database/file__0/");
        } catch (IOException e) {
            e.printStackTrace();
        }

        super.loadUrl(Config.getStartUrl());
    }

    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();

    }

7) Then add snippet in your js file.

    $(document).ready(function(e){      
        document.addEventListener("deviceready", dbConnection, false);            
    }) 

    function dbConnection(){

        db = window.sqlitePlugin.openDatabase("myDB.sqlite", "1.0", "DB", 2000000);
        db.transaction(function(tx) {  
            tx.executeSql("SELECT * from table", [], function(tx, res) {                           
                for(var i=0; i<res.rows.length; i++){                        ;
                    console.log("RESULT:" + res.rows.item(i)['field_name']);
                }            
            });
        });
    }

More Detail...