创建具有外键约束的五层关联表
使用自增主键
外键引用完整性
批量插入效率优化
索引优化
数据一致性校验
CHECKZERO(sqlite3_exec(db, "PRAGMA foreign_keys = ON", 0, 0, 0));
const char* createProvinceChStr = "CREATE TABLE IF NOT EXISTS TBL_CxhfCodeProvince(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL,FullName TEXT NOT NULL, Code TEXT NOT NULL);";
const char* createCityChStr = "CREATE TABLE IF NOT EXISTS TBL_CxhfCodeCity(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL, FullName TEXT NOT NULL, Code TEXT NOT NULL, province_id INTEGER, FOREIGN KEY (province_id) REFERENCES TBL_CxhfCodeProvince(id));";
const char* createDistrictChStr = "CREATE TABLE IF NOT EXISTS TBL_CxhfCodeDistrict(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, FullName TEXT NOT NULL, Code TEXT NOT NULL, city_id INTEGER, FOREIGN KEY (city_id) REFERENCES TBL_CxhfCodeCity(id));";
const char* createStreetChStr = "CREATE TABLE IF NOT EXISTS TBL_CxhfCodeStreet(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, FullName TEXT NOT NULL, Code TEXT NOT NULL, district_id INTEGER, FOREIGN KEY (district_id) REFERENCES TBL_CxhfCodeDistrict(id));";
const char* createCommunityChStr = "CREATE TABLE IF NOT EXISTS TBL_CxhfCodeCommunity(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, FullName TEXT NOT NULL, Code TEXT NOT NULL, street_id INTEGER, FOREIGN KEY (street_id) REFERENCES TBL_CxhfCodeStreet(id));";
// 不存在就插入OR IGNORE 不存在就更新OR REPLACE
const char* Provincesql = "INSERT OR IGNORE INTO TBL_CxhfCodeProvince(id, name, FullName,Code) VALUES (?, ?, ?, ?);";
const char* Citysql = "INSERT OR IGNORE INTO TBL_CxhfCodeCity(id, name, FullName, Code, province_id) VALUES (?, ?, ?, ?, ?);";
const char* Districtsql = "INSERT OR IGNORE INTO TBL_CxhfCodeDistrict(id, name, FullName, Code, city_id) VALUES (?, ?, ?, ?, ?);";
const char* Streetsql = "INSERT OR IGNORE INTO TBL_CxhfCodeStreet(id, name, FullName, Code, district_id) VALUES (?, ?, ?, ?, ?);";
const char* Communitysql = "INSERT OR IGNORE INTO TBL_CxhfCodeCommunity(id, name, FullName, Code, street_id) VALUES (?, ?, ?, ?, ?);";
sqlite3_exec(db, "BEGIN TRANSACTION", 0, 0, &errMsg);
// 准备插入省份的预编译语句
sqlite3_stmt* stmt;
const char* Provincesql = "INSERT OR IGNORE INTO TBL_CxhfCodeProvince(name, FullName, Code) VALUES (?, ?, ?);";
sqlite3_prepare_v2(db, Provincesql, -1, &stmt, nullptr);
for (int i = 0; i < provinceCount; ++i) {
sqlite3_bind_text(stmt, 1, provinces[i].name.c_str(), -1, SQLITE_STATIC);
sqlite3_bind_text(stmt, 2, provinces[i].fullName.c_str(), -1, SQLITE_STATIC);
sqlite3_bind_text(stmt, 3, provinces[i].code.c_str(), -1, SQLITE_STATIC);
sqlite3_step(stmt);
sqlite3_reset(stmt);
}
sqlite3_finalize(stmt);
// 提交事务
sqlite3_exec(db, "COMMIT", 0, 0, &errMsg);
能否封装成CreateTable函数