Sqlite 删除指定的列

最近由于APP数据库的升级错误，需要写修复代码，由于Sqlite并没有提供直接删除列的函数，需要自己做实现，这里简单记录下。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/* 准备数据 */
drop table if exists YYTest;
drop table if exists YYTest_old;

/* 创建测试表，test_remove是我们要删除的 */
create table YYTest (id int, test_remove int);

/* 插入两个测试数据 */
insert into YYTest values (1, 1);
insert into YYTest values (2, 2);

/* stpe 1，重命名旧表 */
alter table YYTest rename to YYTest_old;
/* stpe 2，创建新表 */
create table YYTest (id int);
/* stpe 3，从旧表导数据到新表 */
insert into YYTest (id) select id from YYTest_old;
/* stpe 4，删除旧表 */
drop table YYTest_old;

上面是需要一个建表语句，可以写在一个 fix.sql 里面直接执行。但是有个坏处是需要多维护一份建表脚本(stpe 2)。

如果不想多维护这个建表脚本，可以在上层逻辑实现，而建表语句(scheme)可以通过 sqlite_master 导出。

代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
@implementation FMDatabase (Yoyo)

- (NSString *)yoyo_schemeOfTable:(NSString *)table {
    NSString* sql = [NSString stringWithFormat:@"select sql from sqlite_master where name=?"];
    NSString* scheme = nil;
    FMResultSet* rs = [self executeQuery:sql, table];
    if ([rs next]) {
        scheme = [rs stringForColumn:@"sql"];
    }
    [rs close];
    
    return scheme;
}

- (NSString *)yoyo_schemeByRemoveColumns:(NSArray *)columns scheme:(NSString *)scheme {
    NSRange beginRange = [scheme rangeOfString:@"("];
    NSRange endRange = [scheme rangeOfString:@")"];
    NSRange columnRange = NSMakeRange(beginRange.location + 1, endRange.location - beginRange.location - 1);
    NSString* columnsString = [scheme substringWithRange:columnRange];
    NSArray* columnComponents = [columnsString componentsSeparatedByString:@","];
    columnComponents = [columnComponents yoyo_copyIf:^BOOL(NSString* element) {
        for (NSString* column in columns) {
            if ([element rangeOfString:column].location != NSNotFound) {
                return NO;
            }
        }
        
        return YES;
    }];
    
    columnsString = [columnComponents componentsJoinedByString:@","];
    NSMutableString* result = [scheme mutableCopy];
    [result replaceCharactersInRange:columnRange withString:columnsString];
    
    return result;
}

- (BOOL)yoyo_removeColumns:(NSArray *)columns table:(NSString *)table {
    BOOL result = NO;
    NSArray* allColumns = [self yoyo_columnsOfTable:table];
    NSArray* removedColumns = [allColumns yoyo_arrayByRemoveElements:columns];
    
    // 1. dump 出表的 scheme
    NSString* tableScheme = [self yoyo_schemeOfTable:table];
    if (tableScheme.length <= 0) {
        return NO;
    }
    
    // 2. 删除对应列的语句
    tableScheme = [self yoyo_schemeByRemoveColumns:columns scheme:tableScheme];
    if (tableScheme.length <= 0) {
        return NO;
    }
    
    // 3. 改名成 table_old
    NSString* sql = [NSString stringWithFormat:@"alter table %@ rename to %@_old;", table, table];
    result = [self executeUpdate:sql];
    if (!result) {
        return NO;
    }
    
    // 4. 创建新表
    result = [self executeUpdate:tableScheme];
    if (!result) {
        return NO;
    }
    
    // 6. 将旧表数据导回去
    NSString* columnsString = [removedColumns componentsJoinedByString:@","];
    sql = [NSString stringWithFormat:@"insert into %@(%@) select %@ from %@_old;",
           table, columnsString, columnsString, table];
    result = [self executeUpdate:sql];
    if (!result) {
        return NO;
    }
    sql = [NSString stringWithFormat:@"drop table %@_old;", table];
    result = [self executeUpdate:sql];

    return result;
}

@end