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;
}