//应用包内的内容是不可写的，所以需要把应用包内的数据库拷贝一个副本到资源路径去

- (void)createEditableDatabase{

    BOOL success;

    NSFileManager *manager = [NSFileManager defaultManager];

    NSError *error;

    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    NSString *documentDir = [paths objectAtIndex:];

    NSString *writableDB = [documentDir stringByAppendingPathComponent:@"catalog.db"];

    success = [manager fileExistsAtPath:writableDB];

    if (success) {

        NSLog(@"已经存在");

        return;

    }

    NSString *defaultPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"catalog.db"];

    success = [manager copyItemAtPath:defaultPath toPath:writableDB error:&error];

    if (!success) {

        NSAssert1(, @"Failed to create writable database file:'%@'.", [error localizedDescription]);

    }else NSLog(@"成功写入");

}

- (void)initDatabase{

    NSString *path = [[NSBundle mainBundle] pathForResource:@"catalog" ofType:@"db"];

    //NSLog(@"bundle = %@\npath = %@",[NSBundle mainBundle],path);

    if (sqlite3_open([path UTF8String],&database) == SQLITE_OK) {

        NSLog(@"Opening Database");

    }else{

        sqlite3_close(database);

        NSAssert1(, @"Failed to open database:'%s'.", sqlite3_errmsg(database));

    }

}

//execute sql statement

- (NSMutableArray *)getAllProducts{

    NSMutableArray *products = [NSMutableArray new];

    //    const char *sql = "SELECT product.ID,product.Name,Manufacturer.name,product.details,product.price,product.quantityonhand,country.country,product.image FROM Product,Manufacturer,Country WHERE manufacturer.manufacturerID = product.manufacturerID and product.countryoforiginID = ?";

    //配合sqlite3_bind_int(stmt,1,2)可使用参数化sql语句查询

    const char *sql = "SELECT product.ID,product.Name,Manufacturer.name,product.details,product.price,product.quantityonhand,country.country,product.image FROM Product,Manufacturer,Country WHERE manufacturer.manufacturerID = product.manufacturerID and product.countryoforiginID = country.countryID";

    NSLog(@"\nsql = %s",sql);

    sqlite3_stmt *stmt;

    int sqlResult = sqlite3_prepare_v2(database,sql,-,&stmt,NULL);

    //sqlite3_bind_int(stmt,1,2);//sql语句参数查询，语句、参数索引、参数值

    if (sqlResult == SQLITE_OK) {

        NSLog(@"Ready to print sth");

        int time = ;

        while (sqlite3_step(stmt) == SQLITE_ROW) {

            Product *product = [Product new];

            char *name = (char*)sqlite3_column_text(stmt,);

            char *manufacturer = (char*)sqlite3_column_text(stmt,);

            char *details = (char*)sqlite3_column_text(stmt, );

            char *countryOfOrigin = (char*)sqlite3_column_text(stmt, );

            char *image = (char*)sqlite3_column_text(stmt, );

            NSLog(@"%d,name = %s \n",time++,name);

            product.ID = sqlite3_column_int(stmt,);

            product.name = (name)?[NSString stringWithUTF8String:name]:@"";

            product.manufacturer = (manufacturer)?[NSString stringWithUTF8String:manufacturer]:@"";

            product.details = (details)?[NSString stringWithUTF8String:details]:@"";

            product.price = sqlite3_column_double(stmt,);

            product.quantity = sqlite3_column_int(stmt,);

            product.countryOfOrigin = (countryOfOrigin)?[NSString stringWithUTF8String:countryOfOrigin]:@"";

            product.image = (image)?[NSString stringWithUTF8String:image]:@"";

            [products addObject:product];

        }

        sqlite3_finalize(stmt);

    }else{

        NSLog(@"read failed:%d",sqlResult);

    }

    return products;

}

- (void)closeDatabase{

    if (sqlite3_close(database) != SQLITE_OK) {

        NSAssert1(, @"Error:failed to close database:'%s'.", sqlite3_errmsg(database));

    }

}