indicatorTree-v10练习（有问题）

目标：设计数据库表表格式，将“indicatorTree-v10.json”导入到数据库，再从数据库读取写为JSON文件。

其他要求：数据库要求为mysql数据库；编程语言暂时限定为C；JSON解析使用本文件夹中的cJSON.c和cJSON.h；代码编写风格严格按照“TDYTH编程规范_20220527_C.doc”。

提示：可使用递归写法，也可以自己构建栈结构。gcc编译的Makefile文件参考“Makefile参考”。

数据库表设计

CREATE TABLE `Indicator` (`id` varchar(10) NOT NULL,`abilityName` varchar(255) DEFAULT NULL,`level` int DEFAULT NULL,`defaultWeight` float DEFAULT NULL,`area` text,`devType` text,`technologicalSystem` text,`enable` tinyint(1) DEFAULT NULL,`parent_id` varchar(10) DEFAULT NULL,PRIMARY KEY (`id`),KEY `parent_id` (`parent_id`),CONSTRAINT `Indicator_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `Indicator` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;CREATE TABLE `CalculateParams` (`id` int NOT NULL AUTO_INCREMENT,`indicator_id` varchar(10) DEFAULT NULL,`param_key` varchar(255) DEFAULT NULL,`param_value` text,PRIMARY KEY (`id`),KEY `indicator_id` (`indicator_id`),CONSTRAINT `CalculateParams_ibfk_1` FOREIGN KEY (`indicator_id`) REFERENCES `Indicator` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;CREATE TABLE `IndicatorSystem` (`id` varchar(10) NOT NULL,`name` varchar(255) DEFAULT NULL,`description` text,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

C 代码示例

使用cJSON库来解析JSON文件并将数据插入到数据库中。

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql/mysql.h>
#include "cJSON.h"// 插入 Indicator 表
void insert_indicator(cJSON *indicator, MYSQL *conn, const char *parent_id) {cJSON *id = cJSON_GetObjectItem(indicator, "id");cJSON *abilityName = cJSON_GetObjectItem(indicator, "abilityName");cJSON *level = cJSON_GetObjectItem(indicator, "level");cJSON *defaultWeight = cJSON_GetObjectItem(indicator, "defaultWeight");cJSON *enable = cJSON_GetObjectItem(indicator, "enable");char query[2048];char parent_id_str[64];if (parent_id) {snprintf(parent_id_str, sizeof(parent_id_str), "'%s'", parent_id);} else {snprintf(parent_id_str, sizeof(parent_id_str), "NULL");}snprintf(query, sizeof(query),"INSERT INTO Indicator (id, abilityName, level, defaultWeight, enable, parent_id) ""VALUES ('%s', '%s', %d, %f, %d, %s) ""ON DUPLICATE KEY UPDATE abilityName=VALUES(abilityName), level=VALUES(level), ""defaultWeight=VALUES(defaultWeight), enable=VALUES(enable), parent_id=VALUES(parent_id)",cJSON_GetStringValue(id), cJSON_GetStringValue(abilityName), level->valueint,defaultWeight->valuedouble, enable->valueint, parent_id_str);printf("Executing query: %s\n", query);if (mysql_query(conn, query)) {fprintf(stderr, "Insert error: %s\n", mysql_error(conn));exit(1);}cJSON *children = cJSON_GetObjectItem(indicator, "indicator");if (cJSON_IsArray(children)) {cJSON *child;cJSON_ArrayForEach(child, children) {insert_indicator(child, conn, cJSON_GetStringValue(id));}}
}// 插入 CalculateParams 表
void insert_calculate_params(cJSON *params, MYSQL *conn) {cJSON *id = cJSON_GetObjectItem(params, "id");cJSON *indicator_id = cJSON_GetObjectItem(params, "indicator_id");cJSON *param_key = cJSON_GetObjectItem(params, "param_key");cJSON *param_value = cJSON_GetObjectItem(params, "param_value");char query[1024];snprintf(query, sizeof(query),"INSERT INTO CalculateParams (id, indicator_id, param_key, param_value) ""VALUES ('%s', '%s', '%s', '%s') ""ON DUPLICATE KEY UPDATE param_key=VALUES(param_key), param_value=VALUES(param_value)",cJSON_GetStringValue(id), cJSON_GetStringValue(indicator_id),cJSON_GetStringValue(param_key), cJSON_GetStringValue(param_value));if (mysql_query(conn, query)) {fprintf(stderr, "Insert error: %s\n", mysql_error(conn));exit(1);}
}// 插入 IndicatorSystem 表
void insert_indicator_system(cJSON *system, MYSQL *conn) {cJSON *id = cJSON_GetObjectItem(system, "id");cJSON *name = cJSON_GetObjectItem(system, "name");cJSON *description = cJSON_GetObjectItem(system, "description");char query[1024];snprintf(query, sizeof(query),"INSERT INTO IndicatorSystem (id, name, description) ""VALUES ('%s', '%s', '%s') ""ON DUPLICATE KEY UPDATE name=VALUES(name), description=VALUES(description)",cJSON_GetStringValue(id), cJSON_GetStringValue(name), cJSON_GetStringValue(description));if (mysql_query(conn, query)) {fprintf(stderr, "Insert error: %s\n", mysql_error(conn));exit(1);}
}// 从数据库中获取 Indicator 数据
cJSON* fetch_indicators(MYSQL *conn, const char *parent_id) {char query[1024];snprintf(query, sizeof(query), "SELECT id, abilityName, level, defaultWeight, enable FROM Indicator WHERE parent_id %s",parent_id ? "= '" : "IS NULL");if (parent_id) {strcat(query, parent_id);strcat(query, "'");}if (mysql_query(conn, query)) {fprintf(stderr, "Select error: %s\n", mysql_error(conn));exit(1);}MYSQL_RES *result = mysql_store_result(conn);if (result == NULL) {fprintf(stderr, "mysql_store_result() failed\n");exit(1);}MYSQL_ROW row;cJSON *json_array = cJSON_CreateArray();while ((row = mysql_fetch_row(result))) {cJSON *indicator = cJSON_CreateObject();cJSON_AddStringToObject(indicator, "id", row[0]);cJSON_AddStringToObject(indicator, "abilityName", row[1]);cJSON_AddNumberToObject(indicator, "level", atoi(row[2]));cJSON_AddNumberToObject(indicator, "defaultWeight", atof(row[3]));cJSON_AddBoolToObject(indicator, "enable", atoi(row[4]));cJSON *children = fetch_indicators(conn, row[0]);cJSON_AddItemToObject(indicator, "indicator", children);cJSON_AddItemToArray(json_array, indicator);}mysql_free_result(result);return json_array;
}int main() {MYSQL *conn = mysql_init(NULL);if (conn == NULL) {fprintf(stderr, "mysql_init() failed\n");return EXIT_FAILURE;}if (mysql_real_connect(conn, "192.168.84.1", "root", "***", "***", 0, NULL, 0) == NULL) {fprintf(stderr, "mysql_real_connect() failed\n");mysql_close(conn);return EXIT_FAILURE;}FILE *file = fopen("indicatorTree-v10.json", "r");if (!file) {fprintf(stderr, "Could not open file\n");return EXIT_FAILURE;}fseek(file, 0, SEEK_END);long length = ftell(file);fseek(file, 0, SEEK_SET);char *data = malloc(length);if (data == NULL) {fprintf(stderr, "Memory allocation failed\n");return EXIT_FAILURE;}fread(data, 1, length, file);fclose(file);cJSON *json = cJSON_Parse(data);if (!json) {fprintf(stderr, "Error parsing JSON\n");free(data);return EXIT_FAILURE;}cJSON *indicatorList = cJSON_GetObjectItem(json, "indicator");if (cJSON_IsArray(indicatorList)) {cJSON *indicator;cJSON_ArrayForEach(indicator, indicatorList) {insert_indicator(indicator, conn, NULL);}}cJSON *calculateParamsList = cJSON_GetObjectItem(json, "calculateParams");if (cJSON_IsArray(calculateParamsList)) {cJSON *param;cJSON_ArrayForEach(param, calculateParamsList) {insert_calculate_params(param, conn);}}cJSON *indicatorSystemsList = cJSON_GetObjectItem(json, "indicatorSystems");if (cJSON_IsArray(indicatorSystemsList)) {cJSON *system;cJSON_ArrayForEach(system, indicatorSystemsList) {insert_indicator_system(system, conn);}}free(data);cJSON_Delete(json);cJSON *json_array = fetch_indicators(conn, NULL);char *json_string = cJSON_Print(json_array);printf("%s\n", json_string);cJSON_Delete(json_array);free(json_string);mysql_close(conn);return EXIT_SUCCESS;
}