linux c sqlite 数据库 使用

linux c sqlite 使用

biometric-authenticationd.c

sqlite3 *bio_sto_connect_db()
{
	sqlite3 *db = NULL;
	int ret = -1;
	bool need_create = false;

	ret = access(DB_PATH, F_OK);
	if (ret != 0)
	{
		need_create = true;

		ret = access(DB_DIR, F_OK);
		if (ret != 0)
		{
			internal_create_dir(DB_DIR);
		}

		/* 创建数据库 */
		ret = sqlite3_open_v2(DB_PATH, &db,
							  SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
		if (ret != SQLITE_OK)
		{
			bio_print_error(_("sqlite3 prepare err: %s\n"), sqlite3_errmsg(db));
			return NULL;
		}
		sqlite3_close_v2(db);

		/* 设置默认权限：0600（只允许root用户读写） */
		ret = chmod(DB_PATH, S_IRUSR | S_IWUSR);
		if (ret != 0)
			bio_print_error(_("Unable to set database(%s) permissions. ERROR"
							  "[%d]: %s\n"), DB_PATH, errno, strerror(errno));
	}

	ret = sqlite3_open_v2(DB_PATH, &db,
							  SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
							  NULL);
	if (ret != SQLITE_OK) {
		bio_print_error(_("sqlite3 prepare err: %s\n"), sqlite3_errmsg(db));
		return NULL;
	}

	if (need_create) {
		ret = bio_sto_create_table(db);
		if (ret != 0) {
			sqlite3_close_v2(db);
			return NULL;
		}
	}

	return db;
}

22

feature_info *bio_sto_get_feature_info(sqlite3 *db,
													   int uid,
													   int biotype,
													   char *driver,
													   int index_start,
													   int index_end)
{
	feature_info *info_list = NULL;
	feature_info *info = NULL;
	feature_sample *sample = NULL;

	sqlite3_stmt *stmt = NULL;
	char uuid[UUID_BUF_LEN] = {0};
	char user_uuid[UUID_BUF_LEN] = {0};
	int ret = -1;
	int l = 0;

	char old_uuid[UUID_BUF_LEN] = {0};
	int old_uid = -1;
	int old_bio_type = -1;
	char *old_driver = NULL;
	int idx = -1;
	int old_idx = -1;


	char *base_sql_str = "SELECT ID, UID, UUID, BioType, Driver, EigenIndex, "
						 "  EigenIndexName, SampleNo, EigenData "
						 "FROM EIGEN_INFO "
						 "WHERE EigenIndex >= :index_start";
	char *uid_sql_str = " AND UID = :uid ";
	char *biotype_sql_str = " AND BioType = :biotype ";
	char *driver_sql_str = " AND Driver = :driver ";
	char *idx_end_sql_str = " AND EigenIndex <= :index_end ";
	char *order_sql_str = " ORDER BY UID, UUID, BioType, Driver, EigenIndex, "
						  "EigenIndexName, SampleNo ";
	char *all_sql_str = NULL;


    bio_print_debug ("%s start uid = %d\n",__func__,uid);    

	l = strlen(base_sql_str) + strlen(uid_sql_str) + strlen(biotype_sql_str) +
			strlen(driver_sql_str) + strlen(idx_end_sql_str) +
			strlen(order_sql_str) + 1;
	all_sql_str = malloc(l);
	if (all_sql_str == NULL) {
		bio_print_error(_("Unable to allocate memory\n"));
		return NULL;
	}
	memset(all_sql_str, 0, l);

	l = sprintf(all_sql_str, "%s", base_sql_str);
	if (uid != -1)
		l += sprintf(all_sql_str + l, "%s", uid_sql_str);
	if (biotype != -1)
		l += sprintf(all_sql_str + l, "%s", biotype_sql_str);
	if (driver != NULL)
		l += sprintf(all_sql_str + l, "%s", driver_sql_str);
	if (index_end != -1)
		l += sprintf(all_sql_str + l, "%s", idx_end_sql_str);
	l += sprintf(all_sql_str + l, "%s", order_sql_str);
	bio_print_debug("get sql : %s\n", all_sql_str);

	ret = sqlite3_prepare_v2(db, all_sql_str, -1, &stmt, NULL);
	free(all_sql_str);
	if (ret != SQLITE_OK) {
		bio_print_error(_("sqlite3 prepare err: %s\n"), sqlite3_errmsg(db));
		return NULL;
	}

	idx = sqlite3_bind_parameter_index(stmt, ":uid");
	sqlite3_bind_int64(stmt, idx, uid);

	idx = sqlite3_bind_parameter_index(stmt, ":biotype");
	sqlite3_bind_int64(stmt, idx, biotype);

	idx = sqlite3_bind_parameter_index(stmt, ":driver");
	sqlite3_bind_text(stmt, idx, driver, -1, SQLITE_TRANSIENT);

	idx = sqlite3_bind_parameter_index(stmt, ":index_start");
	sqlite3_bind_int64(stmt, idx, index_start);

	idx = sqlite3_bind_parameter_index(stmt, ":index_end");
	sqlite3_bind_int64(stmt, idx, index_end);

	/* 为了之后处理方便，创建表头空白项 */
	info_list = malloc(sizeof(feature_info));
	if (info_list == NULL) {
		bio_print_error(_("Unable to allocate memory\n"));
		sqlite3_finalize(stmt);
		return NULL;
	}
	memset(info_list, 0, sizeof(feature_info));
	info = info_list;

	while( sqlite3_step(stmt) == SQLITE_ROW ) {
		uid = sqlite3_column_int(stmt, 1);
		strncpy(uuid, (char *)sqlite3_column_text(stmt, 2), UUID_BUF_LEN);
		biotype = sqlite3_column_int(stmt, 3);
		driver = (char *)sqlite3_column_text(stmt, 4);
		idx = sqlite3_column_int(stmt, 5);

		/* 条件判断成功，代表uid、biotype、driver或index任意一个值有变化，
		 * 需要新建表项存储 */
		if ( (old_uid < uid) || (old_bio_type < biotype) ||
			 (strcmp(old_driver, driver) != 0) || (old_idx < idx) ||
			 (strcmp(old_uuid, uuid) != 0) )
		{
			if (old_uid != uid)
			{
				// UID变化，UUID也需要跟着变化
				internal_get_uuid_by_uid(uid, user_uuid);
				old_uid = uid;
			}

			// 检测数据库中的UUID是否与当前UID的UUID匹配，不匹配则弃用该条记录
			if (strcmp(uuid, user_uuid) != 0)
			{
				continue;
			}

			old_uid = uid;
			old_bio_type = biotype;
			old_driver = internal_newstr(driver);
			old_idx = idx;
			strncpy(old_uuid, uuid, UUID_BUF_LEN);

			info->next = malloc(sizeof(feature_info));
			info = info->next;
			if (info == NULL) {
				/* malloc失败，释放资源 */
				bio_sto_free_feature_info_list(info_list);
				return NULL;
			}
			memset(info, 0, sizeof(feature_info));

			info->uid = uid;
			info->biotype = biotype;
			info->driver = old_driver;
			info->index = idx;
			info->index_name = internal_newstr((char *)sqlite3_column_text(stmt, 6));

			sample = malloc(sizeof(feature_sample));
			if (sample == NULL) {
				bio_sto_free_feature_info_list(info_list);
				return NULL;
			}
			memset(sample, 0, sizeof(feature_sample));
			info->sample = sample;
		} else {
			/* 检测数据库中的UUID是否与当前UID的UUID匹配 */
			if (strcmp(uuid, user_uuid) != 0)
			{
				continue;
			}

			sample->next = malloc(sizeof(feature_sample));
			sample = sample->next;
			if (sample == NULL) {
				bio_sto_free_feature_info_list(info_list);
				return NULL;
			}
			memset(sample, 0, sizeof(feature_sample));
		}

		sample->dbid = sqlite3_column_int64(stmt, 0);
		sample->no = sqlite3_column_int(stmt, 7);
		sample->data = internal_newstr((char *)sqlite3_column_text(stmt, 8));
	}

	/* 移除表头空白项 */
	info = info_list->next;
	free(info_list);
	info_list = info;

	sqlite3_finalize(stmt);
	return info_list;
}