FMDB database upgrade - add table fields

Posted by kalinkap on Mon, 30 Mar 2020 22:49:24 +0200

Reprinted from:


Upgrading the database is a very tedious and seemingly troublesome thing. In an interview, the interviewer asked about adding fields to update the database and upgrading it. I replied that I could make a database migration copy
Every time I judge the updated version, the interviewer says that he is puzzled. Why is it so complicated? He repeatedly asked me several times, maybe waiting for a better answer;

You need to add one or more fields to an existing table. The idea should be this way;
First, determine whether the added field exists. If it does not exist, insert it:

#import "FMDatabaseAdditions.h" // Import header file

// judge
if (![db columnExists:@"Fields to be added" inTableWithName:@"Table name"]){  

If not, insert:

NSString *alertStr = [NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ INTEGER",@"Table name",@"New field"];  
BOOL worked = [db executeUpdate:alertStr];  
    NSLog(@"Insert success");
    NSLog(@"Insert failure");

The upgrade sequence is as follows:

// Get Documents directory path
    NSString *documentsPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    // File path
    NSString *sqlitePath = [NSString stringWithFormat:@"MLChatDataBase%@.sqlite", [MLSettingTool objectForKey:ML_UserId]];
    NSString *filePath = [documentsPath stringByAppendingPathComponent:sqlitePath];

// Instantiate FMDataBase object
    _db = [FMDatabase databaseWithPath:filePath];
    [_db open];
    // Initialize data table
    NSString *userInfoSql = @"CREATE TABLE IF NOT EXISTS UserInfoData (userId VARCHAR(255) PRIMARY KEY NOT NULL, realName VARCHAR(255),headImg VARCHAR(255),mobile VARCHAR(255))";
    [_db executeUpdate:userInfoSql];

// Determine whether to include table fields
      if (![db columnExists:@"age" inTableWithName:@"UserInfoData"]){  
          NSString *alertStr = [NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ INTEGER",@"UserInfoData",@"age"];  
          BOOL worked = [db executeUpdate:alertStr];  
                 NSLog(@"Insert success");
                  NSLog(@"Insert failure");

     [_db close];

By Mary_
Source: Jianshu
The copyright belongs to the author. For commercial reprint, please contact the author for authorization. For non-commercial reprint, please indicate the source.

Topics: Database SQLite Mobile