Parsing MySQL 8.0 ibd2sdi json output

ibd2sql.py

#!/usr/bin/env python3
import json
import sys

# not support list partition type
# only support default Btree index, no other index types tested

# only gb* latin1 utf8mb3 utf8mb4
char_collate = {
    248:["gb18030","gb18030_chinese_ci"],249:["gb18030","gb18030_bin"],250:["gb18030","gb18030_unicode_520_ci"],24:["gb2312","gb2312_chinese_ci"],86:["gb2312","gb2312_bin"],28:["gbk","gbk_chinese_ci"],87:["gbk","gbk_bin"],
    5: ["latin1", "latin1_german1_ci"], 8: ["latin1", "latin1_swedish_ci"], 15: ["latin1", "latin1_danish_ci"], 31: ["latin1", "latin1_german2_ci"], 47: ["latin1", "latin1_bin"], 48: ["latin1", "latin1_general_ci"], 49: ["latin1", "latin1_general_cs"], 94: ["latin1", "latin1_spanish_ci"],
    33:["utf8mb3","utf8mb3_general_ci"],76:["utf8mb3","utf8mb3_tolower_ci"],83:["utf8mb3","utf8mb3_bin"],192:["utf8mb3","utf8mb3_unicode_ci"],193:["utf8mb3","utf8mb3_icelandic_ci"],194:["utf8mb3","utf8mb3_latvian_ci"],195:["utf8mb3","utf8mb3_romanian_ci"],196:["utf8mb3","utf8mb3_slovenian_ci"],197:["utf8mb3","utf8mb3_polish_ci"],198:["utf8mb3","utf8mb3_estonian_ci"],199:["utf8mb3","utf8mb3_spanish_ci"],200:["utf8mb3","utf8mb3_swedish_ci"],201:["utf8mb3","utf8mb3_turkish_ci"],202:["utf8mb3","utf8mb3_czech_ci"],203:["utf8mb3","utf8mb3_danish_ci"],204:["utf8mb3","utf8mb3_lithuanian_ci"],205:["utf8mb3","utf8mb3_slovak_ci"],206:["utf8mb3","utf8mb3_spanish2_ci"],207:["utf8mb3","utf8mb3_roman_ci"],208:["utf8mb3","utf8mb3_persian_ci"],209:["utf8mb3","utf8mb3_esperanto_ci"],210:["utf8mb3","utf8mb3_hungarian_ci"],211:["utf8mb3","utf8mb3_sinhala_ci"],212:["utf8mb3","utf8mb3_german2_ci"],213:["utf8mb3","utf8mb3_croatian_ci"],214:["utf8mb3","utf8mb3_unicode_520_ci"],215:["utf8mb3","utf8mb3_vietnamese_ci"],223:["utf8mb3","utf8mb3_general_mysql500_ci"],
    45:["utf8mb4","utf8mb4_general_ci"],46:["utf8mb4","utf8mb4_bin"],224:["utf8mb4","utf8mb4_unicode_ci"],225:["utf8mb4","utf8mb4_icelandic_ci"],226:["utf8mb4","utf8mb4_latvian_ci"],227:["utf8mb4","utf8mb4_romanian_ci"],228:["utf8mb4","utf8mb4_slovenian_ci"],229:["utf8mb4","utf8mb4_polish_ci"],230:["utf8mb4","utf8mb4_estonian_ci"],231:["utf8mb4","utf8mb4_spanish_ci"],232:["utf8mb4","utf8mb4_swedish_ci"],233:["utf8mb4","utf8mb4_turkish_ci"],234:["utf8mb4","utf8mb4_czech_ci"],235:["utf8mb4","utf8mb4_danish_ci"],236:["utf8mb4","utf8mb4_lithuanian_ci"],237:["utf8mb4","utf8mb4_slovak_ci"],238:["utf8mb4","utf8mb4_spanish2_ci"],239:["utf8mb4","utf8mb4_roman_ci"],240:["utf8mb4","utf8mb4_persian_ci"],241:["utf8mb4","utf8mb4_esperanto_ci"],242:["utf8mb4","utf8mb4_hungarian_ci"],243:["utf8mb4","utf8mb4_sinhala_ci"],244:["utf8mb4","utf8mb4_german2_ci"],245:["utf8mb4","utf8mb4_croatian_ci"],246:["utf8mb4","utf8mb4_unicode_520_ci"],247:["utf8mb4","utf8mb4_vietnamese_ci"],255:["utf8mb4","utf8mb4_0900_ai_ci"],256:["utf8mb4","utf8mb4_de_pb_0900_ai_ci"],257:["utf8mb4","utf8mb4_is_0900_ai_ci"],258:["utf8mb4","utf8mb4_lv_0900_ai_ci"],259:["utf8mb4","utf8mb4_ro_0900_ai_ci"],260:["utf8mb4","utf8mb4_sl_0900_ai_ci"],261:["utf8mb4","utf8mb4_pl_0900_ai_ci"],262:["utf8mb4","utf8mb4_et_0900_ai_ci"],263:["utf8mb4","utf8mb4_es_0900_ai_ci"],264:["utf8mb4","utf8mb4_sv_0900_ai_ci"],265:["utf8mb4","utf8mb4_tr_0900_ai_ci"],266:["utf8mb4","utf8mb4_cs_0900_ai_ci"],267:["utf8mb4","utf8mb4_da_0900_ai_ci"],268:["utf8mb4","utf8mb4_lt_0900_ai_ci"],269:["utf8mb4","utf8mb4_sk_0900_ai_ci"],270:["utf8mb4","utf8mb4_es_trad_0900_ai_ci"],271:["utf8mb4","utf8mb4_la_0900_ai_ci"],273:["utf8mb4","utf8mb4_eo_0900_ai_ci"],274:["utf8mb4","utf8mb4_hu_0900_ai_ci"],275:["utf8mb4","utf8mb4_hr_0900_ai_ci"],277:["utf8mb4","utf8mb4_vi_0900_ai_ci"],278:["utf8mb4","utf8mb4_0900_as_cs"],279:["utf8mb4","utf8mb4_de_pb_0900_as_cs"],280:["utf8mb4","utf8mb4_is_0900_as_cs"],281:["utf8mb4","utf8mb4_lv_0900_as_cs"],282:["utf8mb4","utf8mb4_ro_0900_as_cs"],283:["utf8mb4","utf8mb4_sl_0900_as_cs"],284:["utf8mb4","utf8mb4_pl_0900_as_cs"],285:["utf8mb4","utf8mb4_et_0900_as_cs"],286:["utf8mb4","utf8mb4_es_0900_as_cs"],287:["utf8mb4","utf8mb4_sv_0900_as_cs"],288:["utf8mb4","utf8mb4_tr_0900_as_cs"],289:["utf8mb4","utf8mb4_cs_0900_as_cs"],290:["utf8mb4","utf8mb4_da_0900_as_cs"],291:["utf8mb4","utf8mb4_lt_0900_as_cs"],292:["utf8mb4","utf8mb4_sk_0900_as_cs"],293:["utf8mb4","utf8mb4_es_trad_0900_as_cs"],294:["utf8mb4","utf8mb4_la_0900_as_cs"],296:["utf8mb4","utf8mb4_eo_0900_as_cs"],297:["utf8mb4","utf8mb4_hu_0900_as_cs"],298:["utf8mb4","utf8mb4_hr_0900_as_cs"],300:["utf8mb4","utf8mb4_vi_0900_as_cs"],303:["utf8mb4","utf8mb4_ja_0900_as_cs"],304:["utf8mb4","utf8mb4_ja_0900_as_cs_ks"],305:["utf8mb4","utf8mb4_0900_as_ci"],306:["utf8mb4","utf8mb4_ru_0900_ai_ci"],307:["utf8mb4","utf8mb4_ru_0900_as_cs"],308:["utf8mb4","utf8mb4_zh_0900_as_cs"],309:["utf8mb4","utf8mb4_0900_bin"],310:["utf8mb4","utf8mb4_nb_0900_ai_ci"],311:["utf8mb4","utf8mb4_nb_0900_as_cs"],312:["utf8mb4","utf8mb4_nn_0900_ai_ci"],313:["utf8mb4","utf8mb4_nn_0900_as_cs"],314:["utf8mb4","utf8mb4_sr_latn_0900_ai_ci"],315:["utf8mb4","utf8mb4_sr_latn_0900_as_cs"],316:["utf8mb4","utf8mb4_bs_0900_ai_ci"],317:["utf8mb4","utf8mb4_bs_0900_as_cs"],318:["utf8mb4","utf8mb4_bg_0900_ai_ci"],319:["utf8mb4","utf8mb4_bg_0900_as_cs"],320:["utf8mb4","utf8mb4_gl_0900_ai_ci"],321:["utf8mb4","utf8mb4_gl_0900_as_cs"],322:["utf8mb4","utf8mb4_mn_cyrl_0900_ai_ci"],323:["utf8mb4","utf8mb4_mn_cyrl_0900_as_cs"]
}


def idx_keys(idx, column_order):
    keys = []
    for k in idx["elements"]:
        if k["hidden"] is False:
            keys.append(column_order[k["column_opx"]])
    return keys


def main():
    if not sys.stdin.isatty():
        f = sys.stdin
    else:
        if len(sys.argv) < 2:
            print("Usage: ./ibd2sql.py sdi.json OR ibd2sdi xx.ibd | ./ibd2sql.py")
            exit(1)
        f = open(sys.argv[1])
    data = json.load(f)

    tablename = data[1]["object"]["dd_object"]["name"]
    schema_name = data[1]["object"]["dd_object"]["schema_ref"]
    table_id = data[1]["object"]["dd_object"]["indexes"][0]["se_private_data"]

    print("CREATE TABLE %s.%s (" % (schema_name, tablename))

    table_char_id = data[1]["object"]["dd_object"]["collation_id"]
    table_char = ""
    if table_char_id in char_collate:
        table_char = "DEFAULT CHARSET=%s COLLATE=%s" % (char_collate[table_char_id][0],char_collate[table_char_id][1])
    # column
    column_info = data[1]["object"]["dd_object"]["columns"]
    column_attr = ""
    column_order = {}
    outline = []
    for col in column_info:
        # column_type_utf8 include unsigned

        c_o_key = col["ordinal_position"] - 1
        column_order[c_o_key] = col["name"]

        # column character and collate
        if col["collation_id"] in char_collate and col["collation_id"] != table_char_id:
            column_attr += "CHARACTER SET %s COLLATE %s" % (char_collate[col["collation_id"]][0],char_collate[col["collation_id"]][1])

        if col["default_value_null"] is False:
            column_attr += " NOT NULL"
        else:
            column_attr += " DEFAULT NULL"

        if col["default_value_utf8_null"] is False:
            column_attr += " DEFAULT '%s'" % col["default_value_utf8"]

        if col["is_auto_increment"] is True:
            column_attr += " AUTO_INCREMENT"

        if len(col["comment"]) != 0:
            column_attr += " COMMENT '%s'" % col["comment"]

        head = ["DB_ROW_ID", "DB_TRX_ID", "DB_ROLL_PTR"]
        if not any(x in col["name"] for x in head):
            # column datatype default comment
            outline.append("  %s %s %s" %
                           (col["name"], col["column_type_utf8"], column_attr))
        column_attr = ""

    # index
    index_info = data[1]["object"]["dd_object"]["indexes"]
    for idx in index_info:
        if idx["hidden"] is False and idx["name"] == "PRIMARY":
            outline.append("  PRIMARY KEY (%s)" %
                           ",".join(idx_keys(idx, column_order)))
        if idx["hidden"] is False and idx["type"] == 2:
            outline.append("  UNIQUE KEY %s (%s)" %
                           (idx["name"], ",".join(idx_keys(idx, column_order))))
        if idx["hidden"] is False and idx["type"] == 3:
            outline.append("  KEY %s (%s)" %
                           (idx["name"], ",".join(idx_keys(idx, column_order))))
    for out in outline[:-1]:
        print("%s," % out)
    print(outline[-1])

    # partition
    outline_p = []
    part_type = ""
    if data[1]["object"]["dd_object"]["default_partitioning"] != 0:
        part_col = data[1]["object"]["dd_object"]["partition_expression_utf8"]

        if data[1]["object"]["dd_object"]["partition_type"] == 7:
            part_type = "RANGE"
        if data[1]["object"]["dd_object"]["partition_type"] == 9:
            part_type = "RANGE COLUMNS"

        if data[1]["object"]["dd_object"]["partition_type"] == 1:
            part_type = "HASH"

        if data[1]["object"]["dd_object"]["partition_type"] == 4:
            part_type = "LINEAR HASH"

        if data[1]["object"]["dd_object"]["partition_type"] == 3:
            part_type = "KEY"

        if data[1]["object"]["dd_object"]["partition_type"] == 6:
            part_type = "LINEAR KEY"

        print("/* ) PARTITION BY %s (%s)" % (part_type, part_col))

        if "RANGE" in part_type:
            for part in data[1]["object"]["dd_object"]["partitions"]:
                outline_p.append("PARTITION %s VALUES LESS THAN (%s)" % (
                    part["name"], part["description_utf8"]))
        else:
            outline_p.append("PARTITIONS %s" % len(
                data[1]["object"]["dd_object"]["partitions"]))

        for out in outline_p[1:-1]:
            print("%s," % out)
        print("%s */" % outline_p[-1])

    # table Attribute

    table_comment = data[1]["object"]["dd_object"]["comment"]
    row_format = data[1]["object"]["dd_object"]["row_format"]
    if row_format == 2:
        row_format = "DYNAMIC"
    print(") ENGINE=InnoDB %s ROW_FORMAT=%s COMMENT '%s'; /* %s */" %
          (table_char,row_format, table_comment, table_id))


if __name__ == '__main__':
    main()