mardi 15 mars 2016

Better tip when add column in table every update

I have an app that use sqlite database, the code that I use for creating my database is:

NSArray *dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsDir = dirPaths[0];

    databasePath = [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent:@"databases/db.sqlite"]];

        const char *dbpath = [databasePath UTF8String];
        if (sqlite3_open(dbpath, &myDatabase) == SQLITE_OK) {

            sqlite3_key(self.myDatabase, 0, (int)strlen(key));

            char *errMsg;

            const char *sql_base = "CREATE TABLE IF NOT EXISTS mydatabase (ID INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR, password VARCHAR)";

if (sqlite3_exec(myDatabase, sql_database, NULL, NULL, &errMsg) == SQLITE_OK ){

                NSLog(@"database created!");

                [self setDatabase];

            } else {
                NSLog(@"Error when creating database");
            }

            sqlite3_close(myDatabase);

This code is very simple to understand, he create a file called db.sqlite and create a table with tree columns (id, name, password).

Now we go to a small assumption, let's assume that 100 people have downloaded my app, it says about this above code was executed at least 100 times. Since I create a new version of my application in which I have to add one more column in my table as I do that?

I'll try to add a new column in that table, but if I do that the new people who downloaded the app will have 4 fields in the table, while people who update the app will continue with 3 fields in the table. (Because sqlite check first if that table not exists, and don't check if that table with that column exists)

So, how I can solve this? I can create a new syntax for sql like this:

const char *sql_base_update_1_1_0 = "ALTER TABLE mydatabase ADD COLUMN newColumn VARCHAR";

Now, imagine all the time I want to modify that table, my code will be:

const char *sql_base_update_1_1_0
const char *sql_base_update_1_1_1
const char *sql_base_update_1_1_2
const char *sql_base_update_1_1_3
const char *sql_base_update_1_1_4
....

I believe this is not the best way to solve it, anyone have any better tip?

Aucun commentaire:

Enregistrer un commentaire