我在IOS編程使用的FMDatabase這個(gè)sqlite框架，需要客戶端與服務(wù)器做一些數(shù)據(jù)同步工作，有時(shí)得執(zhí)行比較多的命令（增刪改表當(dāng)然是手寫，但如果要手寫插入幾百上千條記錄是很不現(xiàn)實(shí)的，也只需要用php從mysql中讀取記錄便可），而且因?yàn)榭蛻舳说木壒剩琺ysql數(shù)據(jù)庫里的一些字段并不需要寫入到客戶端的sqlite中，所以我們可以用PHP寫一個(gè)接口頁面，以JSON傳遞我們希望傳達(dá)的數(shù)據(jù)，然后再在客戶端進(jìn)行處理。
傳遞的數(shù)據(jù)有兩種格式，一種是直接執(zhí)行的命令，我把它存放在“query”數(shù)組當(dāng)中，另一種是要插入的記錄，把它存放在“record”當(dāng)中。“query”中的的命令直接執(zhí)行，而“record”里的記錄以“key”=>“value”的方式，在客戶端循環(huán)出SQL語句執(zhí)行。
$update_array['database']['query'] = array(); $update_array['table']['wares_category']['query'] = array();
$update_array['table']['wares_category']['record'] = $all_wares_category; |
//客戶端的同步函數(shù) -(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數(shù)組
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數(shù)組
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];
}
}
}
}
} |