1 #import "ViewController.h"
2
3 #import "FMDBMigrationManager.h"
4 #import "FMDB.h"
5
6 #define PATH_OF_DOCUMENT [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0]
7 #define CREATE_TEACHER_TAB @"CREATE TABLE IF NOT EXISTS %@(content text,time text)"
8 //插入基础元素
9 #define INSERT_SQL @"insert into %@(content, time) values(?,?)"
10
11 @interface ViewController () {
12
13 FMDatabaseQueue *dbQueue;
14 }
15
16 @end
17
18 @implementation ViewController
19
20 - (void)viewDidLoad {
21 [super viewDidLoad];
22 // Do any additional setup after loading the view, typically from a nib.
23
24 NSString * doc = PATH_OF_DOCUMENT;
25 NSString * path = [doc stringByAppendingPathComponent:@"teacherCache.sqlite"];
26
27 if (!dbQueue) {
28
29 dbQueue = [FMDatabaseQueue databaseQueueWithPath:path];
30 }
31
32 [self createTable:@"user"];
33 }
34
35 - (NSString *)databaseFilePath {
36
37 NSString * doc = PATH_OF_DOCUMENT;
38 NSString * path = [doc stringByAppendingPathComponent:@"teacherCache.sqlite"];
39 return path;
40 }
41
42 - (void)didReceiveMemoryWarning {
43 [super didReceiveMemoryWarning];
44 // Dispose of any resources that can be recreated.
45 }
46
47 - (BOOL)isTableExist:(NSString *)tableName {
48
49 FMDatabase *db = [FMDatabase databaseWithPath:[self databaseFilePath]];
50 if ([db open]) {
51
52 FMResultSet *rs = [db executeQuery:@"select count(*) as 'count' from sqlite_master where type ='table' and name = ?", tableName];
53 while ([rs next]) {
54
55 // just print out what we've got in a number of formats.
56 NSInteger count = [rs intForColumn:@"count"];
57
58 [db close];
59
60 if (count <= 0) {
61
62 return NO;
63 }else {
64
65 return YES;
66 }
67 }
68 [db close];
69 }else {
70
71 NSLog(@"error when open db");
72 }
73
74 return NO;
75 }
76
77 - (void)createTable:(NSString*)table {
78
79 if ([self isTableExist:table]) {
80
81 NSLog(@"table exist already");
82
83 NSLog(@"alter now");
84 FMDatabase *db = [FMDatabase databaseWithPath:[self databaseFilePath]];
85 if ([db open]) {
86 if (![db columnExists:@"noe" inTableWithName:table]) {
87
88 [self alterTable:table];
89 }
90
91 [db close];
92 }
93
94 return;
95 }
96 FMDatabase *db = [FMDatabase databaseWithPath:[self databaseFilePath]];
97 if ([db open]) {
98
99 NSString *sSQL = [NSString stringWithFormat:CREATE_TEACHER_TAB, table];
100 BOOL res = [db executeUpdate:sSQL];
101 if (!res) {
102
103 NSLog(@"error when creating db table");
104 }else {
105
106 NSLog(@"succ to creating db table");
107
108 [self insertContentTable:table Content:@"ssfdsf" Time:@"2015-04-05"];
109 }
110 [db close];
111
112 }else {
113
114 NSLog(@"error when open db");
115 }
116 }
117
118 - (void)insertContentTable:(NSString*)table Content:(NSString*)content Time:(NSString *)time
119 {
120 [dbQueue inDatabase:^(FMDatabase *db)
121 {
122 NSString *sql = [NSString stringWithFormat:INSERT_SQL,table];
123
124 BOOL res = [db executeUpdate:sql,content,time];
125 if (!res)
126 {
127 NSLog(@"error when insertContentTable");
128 }
129 else
130 {
131 NSLog(@"succ to insertContentTable");
132 }
133 }];
134 }
135
136 - (void)alterTable:(NSString*)table {
137
138 [dbQueue inDatabase:^(FMDatabase *db) {
139
140 NSString *sql = [NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ text",table, @"noe"];
141
142 BOOL res = [db executeUpdate:sql];
143 if (!res)
144 {
145 NSLog(@"error when alterTable");
146 }
147 else
148 {
149 NSLog(@"succ to alterTable");
150 }
151 }];
152 }
