indicatorTree-v10练习

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

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

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

数据库表设计

1. indicator_tree

   • id (VARCHAR(10), PRIMARY KEY)
   • indicator_system_name (VARCHAR(255))
   • description (TEXT)

2. indicator

   • id (VARCHAR(10), PRIMARY KEY)
   • ability_name (VARCHAR(255))
   • level (INT)
   • default_weight (FLOAT)
   • area (TEXT)
   • dev_type (TEXT)
   • technological_system (TEXT)
   • enable (BOOLEAN)
   • parent_id (VARCHAR(10), FOREIGN KEY REFERENCES indicator_tree(id))

3. indicator_details

   • id (VARCHAR(10), PRIMARY KEY)
   • indicator_id (VARCHAR(10), FOREIGN KEY REFERENCES indicator(id))
   • q_analysis (INT)
   • enable_aborted (BOOLEAN)
   • calculate_method (TEXT)
   • calculate_formula (TEXT)
   • calculate_formula_code (INT)
   • indicator_range (VARCHAR(50))
   • indicator_source (INT)
   • aggregation_alg (INT)
   • enable (BOOLEAN)
   • task_type (VARCHAR(50))
   • hole_type (VARCHAR(50))
   • scene_type (VARCHAR(50))

建表代码

-- 创建 indicator_tree 表
CREATE TABLE indicator_tree (
    id VARCHAR(10) PRIMARY KEY,
    indicator_system_name VARCHAR(255),
    description TEXT
);

-- 创建 indicator 表
CREATE TABLE indicator (
    id VARCHAR(10) PRIMARY KEY,
    ability_name VARCHAR(255),
    level INT,
    default_weight FLOAT,
    area TEXT,
    dev_type TEXT,
    technological_system TEXT,
    enable BOOLEAN,
    parent_id VARCHAR(10),
    FOREIGN KEY (parent_id) REFERENCES indicator_tree(id)
);

-- 创建 indicator_details 表
CREATE TABLE indicator_details (
    id VARCHAR(10) PRIMARY KEY,
    indicator_id VARCHAR(10),
    q_analysis INT,
    enable_aborted BOOLEAN,
    calculate_method TEXT,
    calculate_formula TEXT,
    calculate_formula_code INT,
    indicator_range VARCHAR(50),
    indicator_source INT,
    aggregation_alg INT,
    enable BOOLEAN,
    task_type VARCHAR(50),
    hole_type VARCHAR(50),
    scene_type VARCHAR(50),
    FOREIGN KEY (indicator_id) REFERENCES indicator(id)
);

C 代码示例

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

导入数据

#include <stdio.h>
#include <stdlib.h>
#include <mysql/mysql.h>
#include "cJSON.h"

void insert_indicator_tree(MYSQL *conn, cJSON *tree) {
    const char *id = cJSON_GetObjectItem(tree, "indicatorId")->valuestring;
    const char *name = cJSON_GetObjectItem(tree, "indicatorSystemName")->valuestring;
    const char *description = cJSON_GetObjectItem(tree, "description")->valuestring;

    char query[1024];
    snprintf(query, sizeof(query), "INSERT INTO indicator_tree (id, indicator_system_name, description) VALUES ('%s', '%s', '%s')", id, name, description);
    
    if (mysql_query(conn, query)) {
        fprintf(stderr, "Insert error: %s\n", mysql_error(conn));
    }
}

void insert_indicators(MYSQL *conn, cJSON *indicators, const char *parent_id) {
    cJSON *indicator;
    cJSON_ArrayForEach(indicator, indicators) {
        const char *id = cJSON_GetObjectItem(indicator, "id")->valuestring;
        const char *ability_name = cJSON_GetObjectItem(indicator, "abilityName")->valuestring;
        int level = cJSON_GetObjectItem(indicator, "level")->valueint;
        float default_weight = (float)cJSON_GetObjectItem(indicator, "defaultWeight")->valuedouble;

        char query[1024];
        snprintf(query, sizeof(query), "INSERT INTO indicator (id, ability_name, level, default_weight, parent_id) VALUES ('%s', '%s', %d, %f, '%s')", id, ability_name, level, default_weight, parent_id);
        
        if (mysql_query(conn, query)) {
            fprintf(stderr, "Insert error: %s\n", mysql_error(conn));
        }

        // 递归插入子指标
        cJSON *sub_indicators = cJSON_GetObjectItem(indicator, "indicator");
        if (sub_indicators) {
            insert_indicators(conn, sub_indicators, id);
        }
    }
}

int main() {
    MYSQL *conn = mysql_init(NULL);
    if (conn == NULL) {
        fprintf(stderr, "mysql_init() failed\n");
        return EXIT_FAILURE;
    }

    if (mysql_real_connect(conn, "localhost", "user", "password", "database", 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);
    fread(data, 1, length, file);
    fclose(file);

    cJSON *json = cJSON_Parse(data);
    if (!json) {
        fprintf(stderr, "JSON parse error\n");
        free(data);
        return EXIT_FAILURE;
    }

    insert_indicator_tree(conn, json);
    cJSON *indicators = cJSON_GetObjectItem(json, "indicator");
    insert_indicators(conn, indicators, NULL);

    cJSON_Delete(json);
    free(data);
    mysql_close(conn);
    return EXIT_SUCCESS;
}

导出数据

导出数据的代码可以类似于上面的插入代码，但需要从数据库中读取数据并构建JSON对象。这里是一个简单的导出示例：

void export_to_json(MYSQL *conn) {
    MYSQL_RES *res;
    MYSQL_ROW row;

    res = mysql_query(conn, "SELECT * FROM indicator_tree");
    while ((row = mysql_fetch_row(res))) {
        // 构建JSON对象
        // ...
    }
    mysql_free_result(res);
}

Makefile 示例

CC = gcc
CFLAGS = -I. -L. -lcjson -lmysqlclient
OBJ = main.o

all: main

main: $(OBJ)
$(CC) -o main $(OBJ) $(CFLAGS)

clean:
rm -f main $(OBJ)

原文地址：https://blog.csdn.net/beta___/article/details/142863730

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！