ddl-generator で json を ddl に変換する

json から、SQL の create table を作成できるツールを探していて、
catherinedevlin/ddl-generator: Guesses table DDL based on dataを見つけました。インストールして、使ってみた結果を記載します。

インストール、 DDL 生成

catherinedevlin/ddl-generator: Guesses table DDL based on dataのREADME.md を参考にして、インストール、 DDL生成を実施します。

インストール

pip install Requires Python3 ですので、python3 で pip install を実施します。

% python3 -m pip install ddlgenerator
.....
Installing collected packages: python-dateutil, sqlalchemy, argparse, dateutils, pyyaml, data-dispenser, ddlgenerator
Successfully installed argparse-1.4.0 data-dispenser-0.2.5.1 dateutils-0.6.6 ddlgenerator-0.1.9 python-dateutil-2.6.1 pyyaml-3.12 sqlalchemy-1.1.13

ddlgenerator/reshape.py の修正
現在[2017/08/27]、pip install でダウンロードできる version だと、私の環境では以下エラーが発生しました。
```
RuntimeError: OrderedDict mutated during iteration
```
以下、pull request を適用したところ、動作しました。
Fix for https://github.com/catherinedevlin/ddl-generator/issues/24 by mdarmetko · Pull Request #26 · catherinedevlin/ddl-generator

DDL 生成

sqlite で実行可能な形式のDDL を生成する
ddlgenerator sqlite '[json_文字列]' の形式でコマンドを実行します。

ddlgenerator sqlite '[{"actions": ["comment", "assign", "set_tags", "assign_to_me", "set_type", "set_severity"], "message": "Make this IP \"1.0.0.0\" address configurable.", "transitions": ["confirm", "resolve", "falsepositive", "wontfix"], "debt": "30min", "severity": "MINOR", "author": "......", "comments": [], "type": "VULNERABILITY", "status": "OPEN", "tags": ["cert"], "component": "....", "flows": [], "key": "AV35L2OLjUnKGdCyfvMx", "line": 19, "creationDate": "2015-06-28T23:20:42+0900", "effort": "30min", "rule": "squid:S1313", "project": ".....", "updateDate": "2017-08-19T15:29:42+0900", "subProject": "....", "organization": "default-organization", "textRange": {"endLine": 19, "startLine": 19, "startOffset": 16, "endOffset": 77}}]'

以下のようなOUTPUTがコンソールに出力されます。

OUTPUT

WARNING:root:Primary key table0.table0_id not requested, but nesting demands it
WARNING:root:in flows: nested values! example:
'[]'
WARNING:root:in comments: nested values! example:
'[]'
CREATE TABLE table0 (
    flows VARCHAR(2) NOT NULL, 
    creationdate DATETIME NOT NULL, 
    component VARCHAR(4) NOT NULL, 
    message VARCHAR(44) NOT NULL, 
    debt VARCHAR(5) NOT NULL, 
    status VARCHAR(4) NOT NULL, 
    project VARCHAR(5) NOT NULL, 
    _key VARCHAR(20) NOT NULL, 
    effort VARCHAR(5) NOT NULL, 
    updatedate DATETIME NOT NULL, 
    severity VARCHAR(5) NOT NULL, 
    rule VARCHAR(11) NOT NULL, 
    subproject VARCHAR(4) NOT NULL, 
    comments VARCHAR(2) NOT NULL, 
    type VARCHAR(13) NOT NULL, 
    author VARCHAR(6) NOT NULL, 
    line INTEGER NOT NULL, 
    organization VARCHAR(20) NOT NULL, 
    textrange_endoffset INTEGER NOT NULL, 
    textrange_startoffset INTEGER NOT NULL, 
    textrange_startline INTEGER NOT NULL, 
    textrange_endline INTEGER NOT NULL, 
    table0_id BOOLEAN NOT NULL, 
    PRIMARY KEY (table0_id), 
    CHECK (table0_id IN (0, 1))
);
-- in comments: nested values! example: '[]'

-- in flows: nested values! example: '[]'

CREATE TABLE transitions (
    transitions VARCHAR(13) NOT NULL, 
    table0_id BOOLEAN NOT NULL, 
    CHECK (table0_id IN (0, 1)), 
    FOREIGN KEY(table0_id) REFERENCES table0 (table0_id)
);


CREATE TABLE actions (
    table0_id BOOLEAN NOT NULL, 
    actions VARCHAR(12) NOT NULL, 
    CHECK (table0_id IN (0, 1)), 
    FOREIGN KEY(table0_id) REFERENCES table0 (table0_id)
);


CREATE TABLE tags (
    table0_id BOOLEAN NOT NULL, 
    tags VARCHAR(4) NOT NULL, 
    CHECK (table0_id IN (0, 1)), 
    FOREIGN KEY(table0_id) REFERENCES table0 (table0_id)
);

djangoのModelを生成する
個人的にこの機能が使用したかったので使ってみています。
ddlgenerator django '[json_文字列]' の形式でコマンドを実行します。

ddlgenerator sqlite '[{"actions": ["comment", "assign", "set_tags", "assign_to_me", "set_type", "set_severity"], "message": "Make this IP \"1.0.0.0\" address configurable.", "transitions": ["confirm", "resolve", "falsepositive", "wontfix"], "debt": "30min", "severity": "MINOR", "author": "......", "comments": [], "type": "VULNERABILITY", "status": "OPEN", "tags": ["cert"], "component": "....", "flows": [], "key": "AV35L2OLjUnKGdCyfvMx", "line": 19, "creationDate": "2015-06-28T23:20:42+0900", "effort": "30min", "rule": "squid:S1313", "project": ".....", "updateDate": "2017-08-19T15:29:42+0900", "subProject": "....", "organization": "default-organization", "textRange": {"endLine": 19, "startLine": 19, "startOffset": 16, "endOffset": 77}}]'

以下のようなOUTPUTがコンソールに出力されます。

OUTPUT

WARNING:root:Primary key table0.table0_id not requested, but nesting demands it
WARNING:root:in comments: nested values! example:
'[]'
WARNING:root:in flows: nested values! example:
'[]'
# This is an auto-generated Django model module.
# You'll have to do the following manually to clean this up:
#   * Rearrange models' order
#   * Make sure each model has one field with primary_key=True
#   * Make sure each ForeignKey has `on_delete` set to the desired behavior.
#   * Remove `managed = False` lines if you wish to allow Django to create, modify, and delete the table
# Feel free to rename the models, but don't rename db_table values or field names.
from __future__ import unicode_literals

from django.db import models


class Actions(models.Model):
    table0 = models.ForeignKey('Table0', models.DO_NOTHING)
    actions = models.CharField(max_length=12)

    class Meta:
        managed = False
        db_table = 'actions'


class Table0(models.Model):
    comments = models.CharField(max_length=2)
    severity = models.CharField(max_length=5)
    status = models.CharField(max_length=4)
    rule = models.CharField(max_length=11)
    organization = models.CharField(max_length=20)
    project = models.CharField(max_length=63)
    effort = models.CharField(max_length=5)
    line = models.IntegerField()
    author = models.CharField(max_length=21)
    type = models.CharField(max_length=13)
    updatedate = models.TextField()  # This field type is a guess.
    subproject = models.CharField(max_length=63)
    creationdate = models.TextField()  # This field type is a guess.
    flows = models.CharField(max_length=2)
    debt = models.CharField(max_length=5)
    component = models.CharField(max_length=147)
    field_key = models.CharField(db_column='_key', max_length=20)  # Field renamed because it started with '_'.
    message = models.CharField(max_length=44)
    textrange_endline = models.IntegerField()
    textrange_startoffset = models.IntegerField()
    textrange_startline = models.IntegerField()
    textrange_endoffset = models.IntegerField()
    table0_id = models.BooleanField(primary_key=True)

    class Meta:
        managed = False
        db_table = 'table0'


class Tags(models.Model):
    tags = models.CharField(max_length=4)
    table0 = models.ForeignKey(Table0, models.DO_NOTHING)

    class Meta:
        managed = False
        db_table = 'tags'


class Transitions(models.Model):
    table0 = models.ForeignKey(Table0, models.DO_NOTHING)
    transitions = models.CharField(max_length=13)

    class Meta:
        managed = False
        db_table = 'transitions'

WARNING内容の説明

WARNINGがいくつか出力されていたので、その内容を説明します。

WARNING:root:Primary key table0.table0_id not requested, but nesting demands it
これは、table0の主キーを生成した旨のWARNINGになります。このケースだと複数テーブルが出力されたので、子テーブル側のPKとして生成した主キーが使用されています。
WARNING:root:in comments: nested values! example: ‘[]’
これは、JSON文字列に空の配列を持つフィールドがあったため出力されています。空の配列フィールドはDDL生成時には無視されるようです。
値が設定されているJSON文字列を使用するとこのフィールドに対応したDDLが生成されるのかと思います。

Web API の戻り値を取り込む際は、MongoDB等 JSON をそのまま登録できるミドルウェアを使うほうが個人的にはいいと思います。
ただ、後続のデータ集計、加工時にSQLで使いたい場合が少なからずあり、その際、便利に使えるライブラリかなと思いました。
以上です。

ddl-generator で json を ddl に変換する

インストール、 DDL 生成

インストール

DDL 生成

WARNING内容の説明

コメント

カテゴリー

最近の投稿

インストール 、 DDL 生成

インストール

DDL 生成

WARNING内容の説明

コメント

カテゴリー

関連投稿

最近の投稿

インストール、 DDL 生成