Android SQLite Database, WHY drop table and recreate on upgrade

Question

In the tutorials I am following and a lot of more places I see this, onUpgrade -> drop table if exists, then recreate table.

What is the purpose of this?

private static class DbHelper extends SQLiteOpenHelper{

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" +
                KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                KEY_NAME + " TEXT NOT NULL, " +
                KEY_HOTNESS + " TEXT NOT NULL);"
        );  
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
        onCreate(db);
    }       
}

score 32 · Answer 1 · edited May 23 '17 at 12:10

32

Well, the most common method in android applications is to "relog" the user when a database upgrade is in order. And considering any local database should only be mirroring what is on the serverside application, it is much easier to just drop the database, recreate it and repopulate from the server than it is to carefully plan migrations from one version to the other.

It certainly isn't the best approach, but it is easier.

To make an example of how it would be implementing a migration (a change from an older version of a database to a newer one)

Lets say in your DbHelper class you define that your database is version 1, in a later version of your application (version 2), you need a few more columns in one of your tables.

So you would need to upgrade your table and add the columns via ALTER TABLE {tableName} ADD COLUMN COLNew {type};

Check this link for that -> Insert new column into table in sqlite ?

so your onUpgrade() method would have to reflect that change by adding:

 @Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    switch(oldVersion){
        case 1:
            db.execSQL("ALTER TABLE " + DATABASE_TABLE + " ADD COLUMN " + NEW_COLUMN_NAME + TYPE);
    }
}

edited May 23 '17 at 12:10

Community

1
1

answered Nov 05 '13 at 15:53

Aedis

413
3
6

i am not using server on my application and the tutorials did not have anything about the servers too. what do you mean about migrations from versions? – Esqarrouth Nov 05 '13 at 15:57
1

i'll edit my post to answer your question- it's too long for the comment :3 – Aedis Nov 05 '13 at 16:06
thanks, so in case i decided to use the classic drop, i would have to write that line of code for each table right? – Esqarrouth Nov 05 '13 at 16:36
1

right, you should have to check if the table exists and then drop it – Aedis Nov 05 '13 at 16:44
This gives "no such table: xx" error all the time when I upgrade the db version. What might be the reason you think? – stdout Sep 28 '15 at 10:22
You need to add space `+ " " +` between `NEW_COLUMN_NAME` to `TYPE` – Benny Jun 04 '17 at 15:33
+1 for the an easier approach. Also it can be the right one (aka the best) - considering the expences on migration planning, implementing, support and risks of something going wrong. – Luten Oct 05 '18 at 09:39

score 32 · Accepted Answer · answered Nov 07 '13 at 13:17

32

I agree when you upgrade you should be adding columns or adding tables to your database. Most of the onupgrade samples actually suck because why am I deleting all this data then recreating the table? I found this blog entry I call it the Adams Incremental Update Method. It also handles situations where users may have not upgraded your app with each release.

Here is a good blog on sqlite onupgrade that doesn't do drop table.

answered Nov 07 '13 at 13:17

danny117

5,581
1
26
35

3

Is there a particular reason for the loop in the link? What if you just leave out the break statements and let it fall through? It would always start with your earliest old version that matches, then upgrades incrementally from that version to your latest, unless I'm missing something here. – G_V Jan 06 '15 at 08:57
@G_V Absolutely it would be faster to let it drop through. The breaks and loop are there to assist with implementing a percent complete notification. – danny117 Jan 07 '15 at 16:43
Oh I see, yeah that could definitely be handy for large databases. The ones I've used so far were quite tiny so there was no point to showing progress but I'll definitely keep it in mind for future applications. – G_V Jan 09 '15 at 10:27

score 3 · Answer 3 · answered Nov 05 '13 at 16:09

Depends on the kind of approach you want to create and how important your data, what complexity of table it is. Example: If your app has been upgraded quite a lot of times and table structure have change enough times, then its better to drop table and re-create it, rather than writing code for changing structure for every version of db, in this approach you will have to make sure that you can backup any data that you have on server side, so that things remain ok.

From my exp: recreating is better if you can judge that there might further be changes in future, else it gets quite complicated

score 2 · Answer 4 · answered Nov 05 '13 at 15:59

2

It's a clean start. Method onCreate has the currently valid database structure. Users that install the app will execute this method. For the users that are upgrading, method onUpgrade will be executed, and DROP TABLE IF EXIST is a clean start - just in case the structure of the old and the new table is different - drop it and then recreate it in onCreate method. Hope this helps! :)

answered Nov 05 '13 at 15:59

MSquare

6,311
4
31
37

thanks, this process seems like it will delete the the previously stored values in the database, does that happen? is there a chance for the user to lose their data? – Esqarrouth Nov 05 '13 at 16:03
2

If DROP is used, yes the data is gone. If the table structure is not changing, in onCreate method the table can be created as CREATE TABLE IF NOT EXISTS, without dropping it in onUpgrade method. – MSquare Nov 05 '13 at 16:08

hyena · Answer 5 · 2017-06-05T20:22:12.663

if you want to maintain the table data, you can dump it into CSV files then INSERT it back after the tables are created. (no need for dumping the data into a server) here is a sample code for dumping data. you can set the file name to be the table name. the cursor is created from a getAll statement

public boolean createCsvSaveToFile(Cursor cursor, String fileName) throws IOException {
    String csv = "";
    int colunmCount = cursor.getColumnCount();
    /* check if extarnal drive is readerble */
    if (!isExternalStorageWritable()) {
        fileError = "can not save to external storage";
        fileinfo = "Please mount your SD card";
        return false;
    } else {
        /* create the CSV */
        for (int i = 0; i < colunmCount; i++) {
            csv += QUOTES + cursor.getColumnName(i).toString() + QUOTES + INNER_DELIMITER;
        }
        csv = csv.replaceAll(",$", "");
        csv += LINE_END;
        if (cursor.moveToFirst()) {
            do {
                for (int i = 0; i < colunmCount; i++) {//GET COLUNM values
                    csv += QUOTES + cursor.getString(i) + QUOTES + INNER_DELIMITER;
                }
                csv = csv.replaceAll(",$", "");
                csv += LINE_END;
            } while (cursor.moveToNext());
        }
        /* save file */
        File file = getDataDir(fileName);
        FileOutputStream out = new FileOutputStream(file);
        out.write(csv.getBytes());
        out.flush();
        out.close();
        return true;
    }
}

here is a sample code for inserting the data. assuming the CSV file name is the same as the table name

private void readFromFile(SQLiteDatabase database, File file) {
    boolean hasColunms = false;
    String tableName = file.getName().replaceAll(".csv$", "");
    String sql;
    String colunmNames = "";
    String colunmValues;
    try {
        BufferedReader br = new BufferedReader(new FileReader(file));
        String line;
        while ((line = br.readLine()) != null) {
            if (!hasColunms ) {
                /* get column names */
                line = line.replaceAll("\"", "");
                colunmNames = line;
                hasColunms = true;
            } else {
                line = line.replaceAll("\"", "'");
                colunmValues = line;
                sql = "INSERT INTO " + tableName + " (" + colunmNames + ") VALUES (" + colunmValues + ")";
                database.execSQL(sql);
            }
        }
        br.close();
    } catch (IOException e) {
        database.close();
        /* You may need to add proper error handling here
    }

to loop through many csv files one can use the following code

public boolean csvTodatabase(SQLiteDatabase database) {
    FileStuff f = new FileStuff();
    File time;
    /* check if extarnal drive is readerble */
    if (!f.isExternalStorageReadable()) {
        f.fileError = "can not read external storage";
        f.fileinfo = "Please remount your SD card";
        return false;
    } else {
        /* get all files from extarnal drive data */
        ArrayList<File> files = new ArrayList<File>();
        File directory = new File(FileStuff.DATA_DIRECTORY);
        if (!directory.exists()) {
            return false;
        }
        File[] fList = directory.listFiles();
        for (File file : fList) {
            if (file.isFile()) {
                files.add(file);
            }
        }  
        for (File csvfile : files) {
            readFromFile(database, csvfile);
        }
        return true;
    }
}

NOTE: readFromFile(database, csvfile); is the previous function

Seems like it would be better to copy the data to a temporary table rather than a cvs file. I would just use cvs files for exporting data. — Suragch, Feb 26 '18 at 06:34

Android SQLite Database, WHY drop table and recreate on upgrade

5 Answers5

Linked