Best practices for creating a SQLite database on Android
<p>I have read through a lot of the posts on copying the database file over from the assets or raw folders to the <code>/data/data/APP/databases</code> folder, but that would leave two copies of the DB on the device taking up valuable space.  I am thinking of building a solution with a slightly smaller footprint and allowing for more flexible schema management by storing the database SQL creation text file in the raw folder and using the standard <code>on_create / on_update</code> process in the DBhelper class.  However I am a little confused because the examples that copy the database over bypass the <code>on_create and on_update</code> methods.  </p>

<p>Is this the recommended way if you are not building the db from strings in the code?  </p>

<p>My solution would simulate the running scripts from code method by having the scripts all in one file.  The reason I am looking at building the db this way is that my DB will have close to 100 tables when the application is complete, so I need the schema to be manageable.</p>

<p>Any guidance is welcome as I am still learning the best practices and patterns for Android.</p>

<p>Here is an example of my code:</p>

<pre><code>public class DatabaseHelper extends SQLiteOpenHelper {
    private final String DATABASE_NAME = "mydb";
    private final int DATABASE_VERSION = 1;
    private final Context myCtx;
    private String DATABASE_CREATE_SCRIPT = null;

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        DATABASE_CREATE_SCRIPT = getLoadFile();

        // Create all tables and populate the lookup tables
        db.execSQL(DATABASE_CREATE_SCRIPT);
        db.execSQL(VIEW_CREATE_V_PERSON);
    }

    private String getLoadFile(){
        InputStream inputStream = myCtx.getResources().openRawResource(resIdofmyfile);

        InputStreamReader inputreader = new InputStreamReader(inputStream);
        BufferedReader buffreader = new BufferedReader(inputreader);
        String line;
        StringBuilder text = new StringBuilder();

        try {
        while (( line = buffreader.readLine()) != null) {
           text.append(line);
           text.append('\n');
         }
        } catch (IOException e) {
           // We have an error to look up
           return null;
        }
        return text.toString();
    } 

    /**
     *  onUpgrade will check the version of the database and update the database 
     *  if a newer version is available.
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Will set conditional upgrade checks
        //Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which may destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS CONTEXT_LOAD");
            db.execSQL("DROP TABLE IF EXISTS ISSUES");
            db.execSQL("DROP TABLE IF EXISTS GOALS");
            db.execSQL("DROP TABLE IF EXISTS PERSON");

            db.execSQL("DROP VIEW IF EXISTS V_PERSON");
        onCreate(db);
    }


}
</code></pre>
