我在IOS編程使用的FMDatabase這個sqlite框架，需要客戶端與服務器做一些數據同步工作，有時得執行比較多的命令（增刪改表當然是手寫，但如果要手寫插入幾百上千條記錄是很不現實的，也只需要用php從mysql中讀取記錄便可），而且因為客戶端的緣故，mysql數據庫里的一些字段并不需要寫入到客戶端的sqlite中，所以我們可以用PHP寫一個接口頁面，以JSON傳遞我們希望傳達的數據，然后再在客戶端進行處理。
傳遞的數據有兩種格式，一種是直接執行的命令，我把它存放在“query”數組當中，另一種是要插入的記錄，把它存放在“record”當中。“query”中的的命令直接執行，而“record”里的記錄以“key”=>“value”的方式，在客戶端循環出SQL語句執行。
$update_array['database']['query'] = array(); $update_array['table']['wares_category']['query'] = array();
$update_array['table']['wares_category']['record'] = $all_wares_category; |
//客戶端的同步函數 -(void)databaseUpdate
{
FMDatabase *db = [self getDatabase];
NSURL *updateUrl = [[NSURL alloc]initWithString:[Api stringByAppendingPathComponent:@"databaseUpdate/databaseupdate"]];
NSData *updateData = [[NSData alloc]initWithContentsOfURL:updateUrl];
NSError *error = nil;
NSDictionary *updateDictionary = [NSJSONSerialization JSONObjectWithData:updateData options:NSJSONReadingMutableContainers error:&error];
int i;
//database數組
if([[[updateDictionary objectForKey:@"database"]objectForKey:@"query"] count] > 0){
for (i = 0; i < [[[updateDictionary objectForKey:@"database"]objectForKey:@"query"] count]; i++){
NSString *query = [[[updateDictionary objectForKey:@"database"]objectForKey:@"query"]objectAtIndex:i];
[db executeUpdate:query];
}
}
//table數組
if([[updateDictionary objectForKey:@"table"] count] > 0){
for(id tableName in [updateDictionary objectForKey:@"table"]){
if([[[[updateDictionary objectForKey:@"table"] objectForKey:tableName] objectForKey:@"query"] count] > 0){
for (i = 0; i < [[[[updateDictionary objectForKey:@"table"] objectForKey:tableName] objectForKey:@"query"] count]; i++){
NSString *query = [[[[updateDictionary objectForKey:@"table"] objectForKey:tableName] objectForKey:@"query"]objectAtIndex:i];
[db executeUpdate:query];
}
}
if([[[[updateDictionary objectForKey:@"table"] objectForKey:tableName] objectForKey:@"record"] count] > 0){
for (i = 0; i < [[[[updateDictionary objectForKey:@"table"] objectForKey:tableName] objectForKey:@"record"] count]; i++){
NSMutableArray *keys = [[NSMutableArray alloc] init];
NSMutableArray *values = [[NSMutableArray alloc] init];
for (id fieldsName in [[[[updateDictionary objectForKey:@"table"] objectForKey:tableName] objectForKey:@"record"]objectAtIndex:i]){
NSString *fieldsValue = [[[[[updateDictionary objectForKey:@"table"] objectForKey:tableName] objectForKey:@"record"]objectAtIndex:i]objectForKey:fieldsName];
[keys addObject:[NSString stringWithFormat:@"'%@'",fieldsName]];
if(fieldsValue == (NSString*)[NSNull null]){
fieldsValue = @"";
}
[values addObject:[NSString stringWithFormat:@"'%@'",fieldsValue]];
}
NSString *keyString = [keys componentsJoinedByString:@", "];
NSString *valueString = [values componentsJoinedByString:@", "];
NSString *sql = [NSString stringWithFormat:@"INSERT INTO %@ (%@) VALUES (%@)",tableName, keyString, valueString];
[self alertByString:sql];
[db executeUpdate:sql];
}
}
}
}
} |