json2sql (mysql8)

[kukuze]

What type of request is this?

New tool idea

Clear and concise description of the feature you are proposing

Statistic JSONArray field properties and generate SQL table building statements based on them.

First

function analyzeJsonArray(jsonArray) {
    const stats = {};

    // 初始化每个属性的统计对象
    jsonArray.forEach(item => {
        for (const key in item) {
            if (!stats[key]) {
                stats[key] = {
                    type: typeof item[key],
                    count: 0 // 用于计算平均值和平均长度
                };
                // 初始化数字属性
                if (typeof item[key] === 'number') {
                    stats[key].isFloat = !Number.isInteger(item[key]);
                    stats[key].sum = 0;
                    stats[key].max = item[key];
                    stats[key].min = item[key];
                }
                // 初始化字符串属性
                if (typeof item[key] === 'string') {
                    stats[key].totalLength = 0;
                    stats[key].maxLength = item[key].length;
                    stats[key].minLength = item[key].length;
                }
                // 初始化布尔属性
                if (typeof item[key] === 'boolean') {
                    stats[key].trueCount = 0;
                    stats[key].falseCount = 0;
                }
            }
        }
    });

    // 遍历每个对象以更新统计信息
    jsonArray.forEach(item => {
        for (const key in item) {
            const value = item[key];
            const stat = stats[key];
            stat.count += 1;

            if (stat.type === 'number') {
                stat.sum += value;
                stat.max = Math.max(stat.max, value);
                stat.min = Math.min(stat.min, value);
                // 更新是否为浮点数
                stat.isFloat = stat.isFloat || !Number.isInteger(value);
            }

            if (stat.type === 'string') {
                stat.totalLength += value.length;
                stat.maxLength = Math.max(stat.maxLength, value.length);
                stat.minLength = Math.min(stat.minLength, value.length);
            }

            if (stat.type === 'boolean') {
                if (value) {
                    stat.trueCount += 1;
                } else {
                    stat.falseCount += 1;
                }
            }
        }
    });

    // 计算平均值和平均长度并删除中间变量
    for (const key in stats) {
        const stat = stats[key];
        if (stat.type === 'number') {
            stat.average = stat.sum / stat.count;
            delete stat.sum; // 删除不再需要的属性
        }
        if (stat.type === 'string') {
            stat.averageLength = stat.totalLength / stat.count;
            delete stat.totalLength; // 删除不再需要的属性
        }
    }

    return stats;
}

// 示例数据
const jsonArray = [
    {
      "id": 100001,
      "name": "2010 Codeforces Beta Round #1 (training)",
      "type": "ICPC",
      "phase": "FINISHED",
      "frozen": false,
      "durationSeconds": 7200,
      "description": "This is the only contest for testing Codeforces::Gym. As you participate in any other training, you guarantee that you solve problems without assistance and that you do not send other people\u0027s solutions.",
      "difficulty": 3,
      "kind": "Training Contest",
      "season": "2010-2011"
    },
    {
      "id": 100003,
      "name": "2008-2009 Всероссийская командная олимпиада школьников по программированию (ВКОШП 08)",
      "type": "ICPC",
      "phase": "FINISHED",
      "frozen": false,
      "durationSeconds": 18000,
      "startTimeSeconds": 1453514400,
      "relativeTimeSeconds": 262084696,
      "preparedBy": "Edvard",
      "difficulty": 3,
      "kind": "Official School Contest",
      "country": "Russia",
      "city": "Saint Petersburg",
      "season": "2008-2009"
    },
    {
      "id": 100002,
      "name": "2002-2003 ACM-ICPC Northeastern European Regional Contest (NEERC 02)",
      "type": "ICPC",
      "phase": "FINISHED",
      "frozen": false,
      "durationSeconds": 18000,
      "preparedBy": "MikeMirzayanov",
      "difficulty": 4,
      "kind": "Official ICPC Contest",
      "icpcRegion": "Northeastern Europe Region",
      "country": "Russia",
      "city": "Saint Petersburg",
      "season": "2002-2003"
    },
    {
      "id": 100005,
      "name": "2010-2011 Цикл интернет-олимпиад. Вторая командная олимпиада (9 октября 2010). Усложненный уровень.",
      "type": "ICPC",
      "phase": "FINISHED",
      "frozen": false,
      "durationSeconds": 18000,
      "preparedBy": "PavelKunyavskiy",
      "difficulty": 3,
      "kind": "Training Contest",
      "country": "Russia",
      "season": "2010-2011"
    }
  ];

console.log(analyzeJsonArray(jsonArray));

Second

## 根据统计信息生成sql
const schema = {
    id: {
        type: 'number',
        count: 1921,
        isFloat: false,
        max: 105163,
        min: 100001,
        average: 102398.88339406559
    },
    name: {
        type: 'string',
        count: 1921,
        maxLength: 160,
        minLength: 8,
        averageLength: 56.9453409682457
    },
    type: {
        type: 'string',
        count: 1921,
        maxLength: 4,
        minLength: 3,
        averageLength: 3.8828735033836543
    },
    phase: {
        type: 'string',
        count: 1921,
        maxLength: 8,
        minLength: 8,
        averageLength: 8
    },
    frozen: { type: 'boolean', count: 1921, trueCount: 0, falseCount: 1921 },
    durationSeconds: {
        type: 'number',
        count: 1921,
        isFloat: false,
        max: 31622400,
        min: 600,
        average: 42743.33159812597
    },
    description: {
        type: 'string',
        count: 391,
        maxLength: 672,
        minLength: 9,
        averageLength: 270.4117647058824
    },
    difficulty: {
        type: 'number',
        count: 1742,
        isFloat: false,
        max: 5,
        min: 1,
        average: 3.4402985074626864
    },
    kind: {
        type: 'string',
        count: 1793,
        maxLength: 42,
        minLength: 15,
        averageLength: 25.717791411042946
    },
    season: {
        type: 'string',
        count: 1762,
        maxLength: 9,
        minLength: 9,
        averageLength: 9
    },
    startTimeSeconds: {
        type: 'number',
        count: 784,
        isFloat: false,
        max: 1715490000,
        min: 1327159800,
        average: 1574621590.1785715
    },
    relativeTimeSeconds: {
        type: 'number',
        count: 784,
        isFloat: false,
        max: 388439296,
        min: 109096,
        average: 140977505.82142857
    },
    preparedBy: {
        type: 'string',
        count: 1920,
        maxLength: 24,
        minLength: 3,
        averageLength: 8.588541666666666
    },
    country: {
        type: 'string',
        count: 1669,
        maxLength: 21,
        minLength: 4,
        averageLength: 7.005392450569203
    },
    city: {
        type: 'string',
        count: 1406,
        maxLength: 16,
        minLength: 3,
        averageLength: 9.344950213371266
    },
    icpcRegion: {
        type: 'string',
        count: 328,
        maxLength: 33,
        minLength: 11,
        averageLength: 23.567073170731707
    },
    websiteUrl: {
        type: 'string',
        count: 965,
        maxLength: 116,
        minLength: 12,
        averageLength: 32.22487046632124
    }
};

function generateSQL(schema) {
    let sql = 'CREATE TABLE YourTableName (\n';

    for (let key in schema) {
        let field = schema[key];
        let fieldType = field.type;
        let sqlType = '';

        if (fieldType === 'string') {
            if (field.maxLength >= 255) {
                sqlType = 'TEXT';
            } else {
                sqlType = `VARCHAR(255)`;
            }
        } else if (fieldType === 'number') {
            if (field.max > 10000) {
                sqlType = 'BIGINT';
            } else {
                sqlType = 'INT';
            }
        } else if (fieldType === 'boolean') {
            sqlType = 'TINYINT(1)';
        }

        sql += `    ${key} ${sqlType},\n`;
    }

    sql = sql.trim().slice(0, -1); // Remove the last comma
    sql += '\n) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;';

    return sql;
}

console.log(generateSQL(schema));

Is their example of this tool in the wild?

I thought it myself

Additional context

No response

Validations

• Check the feature is not already implemented in the project.
• Check that there isn't already an issue that request the same feature to avoid creating a duplicate.
• Check that the feature can be implemented in a client side only app (IT-Tools is client side only, no server).