数据库杂记

阅读量

0

阅读人次

0

SQLite

C/C++ API文档：https://www.sqlite.org/c3ref/intro.html

打开 一个已有或准备新创建的 SQLite 数据库的连接。sqlite3_open() 也是 sqlite3 的构造函数。

int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);

一个包装函数，它对一个或多个 SQL 语句执行 sqlite3_prepare()、sqlite3_step()、sqlite3_column() 和 sqlite3_finalize()函数。

int sqlite3_exec(
  sqlite3*,                                  /* An open database */
  const char *sql,                           /* SQL to be evaluated */
  int (*callback)(void*,int,char**,char**),  /* Callback function */
  void *,                                    /* 1st argument to callback */
  char **errmsg                              /* Error msg written here */
);

一个简单示例：

class Contact {
public:
    int64_t id;
    std::string name;
    int age;
    std::string phone;
    std::string address;
};

static int selectCallback(void *data, int argc, char **argv, char **columnName) {
    auto contacts = reinterpret_cast<std::vector<Contact> *>(data);
    Contact contact;
    for (int i = 0; i < argc; i++) {
        if (argv[i] == nullptr) continue;
        if (strcmp(columnName[i], "id") == 0) {
            contact.id = std::atol(argv[i]);
        } else if (strcmp(columnName[i], "name") == 0) {
            contact.name = argv[i];
        } else if (strcmp(columnName[i], "phone") == 0) {
            contact.phone = argv[i];
        } else if (strcmp(columnName[i], "address") == 0) {
            contact.address = argv[i];
        }
    }
    contacts->push_back(contact);
    return 0;
}

int main() {
    sqlite3 *database = nullptr;
    if (sqlite3_open("test.db", &database) != SQLITE_OK) {
        std::cerr << "sqlite3_open() failed: " << sqlite3_errmsg(database);
        return -1;
    }

    char *error = nullptr;
    const char *createTabelSql = R"(
        create table contact(
            id      INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
            name    TEXT    NOT NULL,
            age     INT     NOT NULL,
            phone   CHAR(11),
            address CHAR(50)
        );
    )";
    if (sqlite3_exec(database, createTabelSql, nullptr, nullptr, &error) != SQLITE_OK) {
        std::cerr << "sqlite3_exec() failed: " << error << std::endl;
        sqlite3_free(error);
    }

    const char *insertSql = R"(
        insert into contact(name, age, phone,ADDRESS)
        values ("amass", 27, "1521705" ,"xxx city xxx street");
    )";
    if (sqlite3_exec(database, insertSql, nullptr, nullptr, &error) != SQLITE_OK) {
        std::cerr << "sqlite3_exec() failed: " << error << std::endl;
        sqlite3_free(error);
    }

    std::vector<Contact> contacts;
    if (sqlite3_exec(database, "select * from contact", selectCallback, &contacts, &error) != SQLITE_OK) {
        std::cerr << "sqlite3_exec() failed: " << error << std::endl;
        sqlite3_free(error);
    }
    std::cout << "row size: " << contacts.size() << std::endl;

    sqlite3_close(database);
    return 0;
}