iPhone 上的 SQLite 表无效？

Question

我一直在尝试通过我的 iPhone 访问 SQLite3 数据库，但我不断收到“no such table: user_info”错误。

因此，以下是我所经历的步骤：

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

   • sqlite3 users.sqlite
   • 创建表user_info（名称文本，信息文本）；
   • 插入 user_info value('Name1', '这是 Name1 的信息');
   • 从用户信息中选择*；
   • [结果]：Name1|这是 Name1 的信息
   • 从sqlite_master中选择*；
   • [结果]：表|user_info|user_info|3|创建表user_info（名称文本，信息文本）

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

3. 尝试访问它并收到错误“no such table: 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/如何读取和写入-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;
}