我在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];
}
}
}
}
} |