iphone - iPhone 上的 SQLite 表无效？

Question

我一直在尝试通过我的 iphone 访问 SQLite3 数据库，但我一直收到“没有这样的表:user_info”的错误消息。

所以，这是我完成的步骤:

1. 通过命令行创建数据库:

   • sqlite3 users.sqlite
   • 创建表user_info(名称文本，信息文本)；
   • insert into user_info value('Name1', 'This is info for Name1');
   • 从 user_info 中选择 *；
   • [result]: Name1|这是Name1的信息
   • 从sqlite_master中选择*；
   • [结果]: table|user_info|user_info|3|CREATE TABLE user_info (name text, info text)

2. 将其复制到 XCode 中的资源文件夹中，并可选择将其复制到适当的目录。

3. 尝试访问它并收到错误“没有这样的表:user_info”。

好吧，那我怎么做 #3？好吧，我已经更新了一点，所以现在我尝试创建该表(如果它不存在)。这是我当前的代码:

static NSString *dbname = @"users.sqlite";

-(NSString *) dbFilePath {
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSArray *paths = NSSearchPathForDirectoriesInDomains(
        NSDocumentDirectory, NSUserDomainMask, YES
    );
    NSString *documentsDir = [paths objectAtIndex:0];
    NSString *dbPath = [documentsDir stringByAppendingPathComponent:dbname];
    BOOL success = [fileManager fileExistsAtPath:dbPath];
    if (!success) {
        fprintf(stderr, "Database is not writeable!\n");
    }
    return dbPath;
}

- (void)createEditableCopyOfDatabaseIfNeeded {
    BOOL success;
    NSError *error;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSString *writeableDBPath = [self dbFilePath];

    success = [fileManager fileExistsAtPath:writeableDBPath];
    if (success) {
        return;
    }

    NSString *defaultDBPath =
        [[[NSBundle mainBundle] resourcePath]
            stringByAppendingPathComponent:dbname];

    success = [fileManager
                    copyItemAtPath:defaultDBPath
                    toPath:writeableDBPath
                    error:&error];

    if (!success) {
        fprintf(stderr, "Failed to create writable database file\n");
    }
}

-(void) openDB {
    if (sqlite3_open([[self dbFilePath] UTF8String], &db) != SQLITE_OK) {
        sqlite3_close(db);
        fprintf(stderr, "Database failed to open.\n");
    }
}

-(void) createTableIfNeeded {
    NSString *result = @"";
    sqlite3_stmt *statement;

    char *sql = sqlite3_mprintf(
        "CREATE TABLE IF NOT EXISTS user_info (name text info text);"
    );

    dbresult = sqlite3_prepare_v2(db, sql, strlen(sql), &statement, NULL);
    if (SQLITE_OK != dbresult) {
        NSAssert1(0, "no user_info table!", nil);
        fprintf(
            stderr,
            "Error in preparation of query: %s\n", 
            sqlite3_errmsg(db)
        );
        sqlite3_close(db);
        return;
    }
    sqlite3_finalize(statement);
    sqlite3_free(sql);
}

- (NSString *)getDatabaseEntry:(NSString *)i_name {
    NSString *result = @"";
    sqlite3_stmt *statement;

    char *sql = sqlite3_mprintf(
        "SELECT 'info' FROM 'user_info' WHERE name='%q'",
        [i_name cStringUsingEncoding:NSASCIIStringEncoding]
    );

    dbresult = sqlite3_prepare_v2(db, sql, strlen(sql), &statement, NULL);
    if (SQLITE_OK != dbresult) {
        fprintf(
            stderr,
            "Error in preparation of query: %s\n", 
            sqlite3_errmsg(db)
        );
        sqlite3_close(db);
        return result;
    }
    dbresult = sqlite3_step(statement);
    if (SQLITE_ROW == dbresult) {
        char *nfo = (char *)sqlite3_column_text(statement, 0);
        result = [NSString stringWithUTF8String:nfo];
    }
    sqlite3_finalize(statement);
    sqlite3_free(sql);
    return result;
}

// viewDidAppear
- (void)viewDidAppear:(BOOL)animated {
    [self createEditableCopyOfDatabaseIfNeeded];
    [self openDB];
    [self createTableIfNeeded];
}

// Later, when I have a valid user:
- (BOOL)peoplePickerNavigationController:(ABPeoplePickerNavigationController *)peoplePicker
        shouldContinueAfterSelectingPerson:(ABRecordRef)person {
    ...
    NSString *nfo = [self getDatabaseEntry:name.text];
    ...
}

我已经反对这堵墙几天了，所以非常感谢任何帮助。

Answer 1

好吧，我修复了它，但我不确定如何修复。我创建了一个空白项目并按照此处的教程进行操作: http://www.raywenderlich.com/913/sqlite-101-for-iphone-developers-making-our-app并确保它有效。 (确实如此。)

然后我使用了这里的位: http://www.raywenderlich.com/725/how-to-read-and-write-xml-documents-with-gdataxml将数据库复制到可写的位置。我以前有非常相似的代码。

所以现在可以了。我专门用“sqlite3”扩展构建了一个新表。我不知道这是否真的有所作为。我还用过:

[[NSBundle mainBundle] pathForResource:@"users" ofType:@"sqlite3"];

相对于:

[[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:dbname];

同样，不知道这是否重要。

希望这对其他人有帮助!

//----------------------------------------------------------------------------
-(NSString *) dbFilePath {
    NSError *error;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,
                                                         NSUserDomainMask,
                                                         YES);
    NSString *documentsDir = [paths objectAtIndex:0];
    NSString *dbPath = [documentsDir stringByAppendingPathComponent:@"users.sqlite3"];
    BOOL success = [fileManager fileExistsAtPath:dbPath];
    if (!success) {
        fprintf(stderr, "Database is not writeable!  Attempting to create writeable database.\n");
        NSString *bundle_path = [[NSBundle mainBundle] pathForResource:@"users" ofType:@"sqlite3"];
        success = [fileManager copyItemAtPath:bundle_path toPath:dbPath error: &error];
        if (!success) {
            fprintf(stderr, "Failed to create writeable database file\n");
        }
    }
    return dbPath;
}

//----------------------------------------------------------------------------
-(void) openDB {
    if (sqlite3_open([[self dbFilePath] UTF8String], &db) != SQLITE_OK) {
        sqlite3_close(db);
        NSAssert(0, @"Database failed to open.");
        fprintf(stderr, "Database failed to open.\n");
    }
}

//----------------------------------------------------------------------------
- (NSString *)getDatabaseEntry:(NSString *)i_name {
    NSString *result = @"";
    sqlite3_stmt *statement;

    char *sql = sqlite3_mprintf(
        "SELECT info FROM user_info WHERE name='%q'",
        [i_name cStringUsingEncoding:NSASCIIStringEncoding]
    );
    dbresult = sqlite3_prepare_v2(db, sql, strlen(sql), &statement, NULL);
    if (SQLITE_OK != dbresult) {
        fprintf(stderr,
                "Error in preparation of query: %s\n",
                sqlite3_errmsg(db));
        sqlite3_close(db);
        sqlite3_free(sql);
        return result;
    }

    dbresult = sqlite3_step(statement);
    if (SQLITE_ROW == dbresult) {
        char *nfo = (char *)sqlite3_column_text(statement, 0);
        result = [NSString stringWithUTF8String:nfo];
    }
    sqlite3_finalize(statement);
    sqlite3_free(sql);

    return result;
}