在app的版本更新中,经常会遇到sqlite中的表需要增加字段。
为了便于旧版本app的升级使用,在对table增加字段时,首先要判断table中是否已经存在了这个字段,如果还未存在,则通过alter table (table name) add的方式增加字段。
没有找到直接的方法进行table中某字段是否存在的判断,只能使用折中的办法,获取这个表中所有的字段,逐个进行判断。
如下:
//判断某表中某字段是否存在
-(BOOL)isExistColumnInTable:(NSString *)tableName ColumnName:(NSString *)column{
//首先,数据库已经打开
if ((tableName == nil) || (column == nil)) return NO;
sqlite3_stmt *statement = nil;
//NSString * sql = @"PRAGMA table_info([MyAsk_table]) ";
NSString *sql = [NSString stringWithFormat:@"PRAGMA table_info(%@)", tableName];
if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL) != SQLITE_OK ) {
NSLog(@"Error: failed to prepare statement.");
[self closeDatabase];
return NO;
}
while (sqlite3_step(statement) == SQLITE_ROW) {
NSString *columntem = [[[NSString alloc] initWithCString:(char *)sqlite3_column_text(statement, 1) encoding:NSUTF8StringEncoding] autorelease];
NSLog(@"columntem = %@", columntem);
if ([column isEqualToString:columntem]) {
sqlite3_finalize(statement);
return YES;
}
}
sqlite3_finalize(statement);
return NO;
}
判断某表是否存在,可以直接用
sqlite3_stmt *statement = nil;
NSString * sql = @"select count(*) from sqlite_master where type = 'table' and name = 'MyAsk_table' ";
if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL) != SQLITE_OK ) {
NSLog(@"Error: failed to prepare statement.");
[self closeDatabase];
return NO;
}
while (sqlite3_step(statement) == SQLITE_ROW) {
NSString *count = [[[NSString alloc] initWithCString:(char *)sqlite3_column_text(statement, 0) encoding:NSUTF8StringEncoding] autorelease];
NSLog(@"count = %@", count);
if ([count integerValue] > 0) {
sqlite3_finalize(statement);
return YES;
}
}
sqlite3_finalize(statement);
return NO;