LoginSignup
26
17

More than 5 years have passed since last update.

@unok(宇野 和彦)

テキストベースデータベース定義管理ツール erdm を作成しました

Posted at

テキストベースデータベース定義管理ツール erdm を作成しました

概要

特定の書式で記述したファイルを変換して、簡易 ER図や HTMLベースの定義書を作成できます。
また、DDL も作成出来ます。(PostgreSQL, SQLite 用)

ダウンロード

下記のページから最新のものをダウンロードして使ってください。
https://github.com/unok/erdm/releases

例

erdm ファイル

変換元になるテキストファイルです。

# Title: Shopping Site

users/顧客
    +id/顧客ID [bigserial][NN][U]
    name/名称 [varchar(128)][NN][='']
    password/パスワード [varchar(128)][='********']
        # sha1 でハッシュ化して登録
    mail_address/メールアドレス [varchar(256)][NN][U]
    updated/更新日時 [timestamp with time zone][NN][=now()][-erd]
    created/作成日時 [timestamp with time zone][NN][=now()][-erd]
    index i_users_mail_address (mail_address)

prefectures/都道府県
    +id/都道府県ID [int][NN][U]
    name/都道府県名称 [varchar(4)][NN][U]
    sort_order/ソート順 [int][NN][U]

address_types/住所種別
    +id/住所種別ID [int][NN][U]
    name/種別名称 [varchar(10)][NN][U]

addresses/住所
    +id/住所ID [bigserial][NN][U]
    user_id/顧客ID [bigint][NN] 0..*--1 users
    name/名称 [varchar(128)][NN]
    address_type_id/住所種別ID [bigint] 0..*--1 address_types
    post_code/郵便番号 [varchar(7)][NN]
    prefecture_id/都道府県コード [bigint][NN] 0..*--1 prefectures
    address1/住所1 [varchar(128)][NN]
    address2/住所2 [varchar(128)]
    updated/更新日時 [timestamp with time zone][NN][=now()][-erd]
    created/作成日時 [timestamp with time zone][NN][=now()][-erd]
    index i_addresses_user_id (user_id)
    index i_addresses_address_type_id (address_type_id)
    index i_addresses_prefecture_id (prefecture_id)

items/商品
    +id/商品ID [bigserial][NN][U]
    name/商品名 [varchar(128)][NN]
    description/詳細 [text]
    quantity/数量 [int][NN]
    price/単価 [numeric(10,2)][NN]
    updated/更新日時 [timestamp with time zone][NN][=now()][-erd]
    created/作成日時 [timestamp with time zone][NN][=now()][-erd]

carts/カート
    +id/カートID [bigserial][NN][U]
    user_id/顧客ID [bigint][NN] 0..*--1 users
    updated/更新日時 [timestamp with time zone][NN][=now()][-erd]
    created/作成日時 [timestamp with time zone][NN][=now()][-erd]
    index i_carts_user_id (user_id)

cart_items/カート内アイテム
    +id/カート内アイテムID [bigserial][NN][U]
    cart_id/カートID [bigint][NN] 0..*--1 carts
    item_id/商品ID [bigint][NN] 0..*--1 items
    category_id/分類ID [bigint][NN][U] 0..*--1 categories
    quantity/数量 [int][NN]
    updated/更新日時 [timestamp with time zone][NN][=now()][-erd]
    created/作成日時 [timestamp with time zone][NN][=now()][-erd]
    index i_cart_items_cart_id (cart_id)

orders/注文
    +id/注文ID [bigserial][NN][U]
    cart_id/カートID [bigint][NN] 0..*--1 carts
    order_status/注文状態 [int][NN][=0]
    order_time/発注日時 [timestamp with time zone][NN][=now()]
    shipment_time/出荷日時 [timestamp with time zone][NN][=now()]
    shipment_user_id/出荷担当者 [bigint] 0..*--1 administrators
    track_no/送り状番号 [varchar(64)]
    user_id/顧客ID [bigint][NN] 0..*--1 users
    item_id/商品ID [bigint][NN] 0..*--1 items
    category_id/分類ID [bigint][NN][U] 0..*--1 categories
    order_address_id/発注者住所 [bigint][NN] 0..*--1 addresses
    delivery_address_id/納品先住所 [bigint][NN] 0..*--1 addresses
    charge_address_id/請求先住所 [bigint][NN] 0..*--1 addresses
    updated/更新日時 [timestamp with time zone][NN][=now()][-erd]
    created/作成日時 [timestamp with time zone][NN][=now()][-erd]
    index i_orders_cart_id (cart_id)
    index i_orders_user_id (user_id)
    index i_orders_item_id (item_id)
    index i_orders_cart_id_item_id (cart_id, item_id) unique

categories/分類
    +id/分類ID [bigserial][NN][U]
    name/タグ [varchar(128)][NN]
    description/説明 [text]
    parent_category_id/親分類ID [bigint] 0..*--0..1 categories
    updated/更新日時 [timestamp with time zone][NN][=now()][-erd]
    created/作成日時 [timestamp with time zone][NN][=now()][-erd]

item_categories/商品分類
    +id/商品分類ID [bigserial][NN][U]
    item_id/商品ID [bigint][NN] 0..*--1 items
    category_id/分類ID [bigint][NN][U] 0..*--1 categories

administrators/管理者
    +id/管理者ID [bigserial][NN][U]
    name/管理者名称 [varchar(64)][NN][U]
    login_name/ログイン名 [varchar(64)][NN][U]
    mail_address/メールアドレス [varchar(256)][NN][U]
    password/パスワード [varchar(128)][='********']
      # sha1 でハッシュ化して登録
    updated/更新日時 [timestamp with time zone][NN][=now()][-erd]
    created/作成日時 [timestamp with time zone][NN][=now()][-erd]

ER図画像

test_large_data_jp.png

定義書HTML

AngulerJS 1.2.x を使った検索機能付きのデータベース定義書です。

test_large_data_jp.html.png

DDL

PostgreSQL

DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS prefectures CASCADE;
DROP TABLE IF EXISTS address_types CASCADE;
DROP TABLE IF EXISTS addresses CASCADE;
DROP TABLE IF EXISTS items CASCADE;
DROP TABLE IF EXISTS carts CASCADE;
DROP TABLE IF EXISTS cart_items CASCADE;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS categories CASCADE;
DROP TABLE IF EXISTS item_categories CASCADE;
DROP TABLE IF EXISTS administrators CASCADE;


CREATE TABLE users (
    id bigserial UNIQUE NOT NULL,
    name varchar(128) NOT NULL DEFAULT '',
    password varchar(128) DEFAULT '********',
    mail_address varchar(256) UNIQUE NOT NULL,
    updated timestamp with time zone NOT NULL DEFAULT now(),
    created timestamp with time zone NOT NULL DEFAULT now(),

    PRIMARY KEY (id)
);
CREATE INDEX i_users_mail_address ON users (mail_address);

CREATE TABLE prefectures (
    id int UNIQUE NOT NULL,
    name varchar(4) UNIQUE NOT NULL,
    sort_order int UNIQUE NOT NULL,

    PRIMARY KEY (id)
);

CREATE TABLE address_types (
    id int UNIQUE NOT NULL,
    name varchar(10) UNIQUE NOT NULL,

    PRIMARY KEY (id)
);

CREATE TABLE addresses (
    id bigserial UNIQUE NOT NULL,
    user_id bigint NOT NULL,
    name varchar(128) NOT NULL,
    address_type_id bigint,
    post_code varchar(7) NOT NULL,
    prefecture_id bigint NOT NULL,
    address1 varchar(128) NOT NULL,
    address2 varchar(128),
    updated timestamp with time zone NOT NULL DEFAULT now(),
    created timestamp with time zone NOT NULL DEFAULT now(),

    PRIMARY KEY (id)
);
CREATE INDEX i_addresses_user_id ON addresses (user_id);
CREATE INDEX i_addresses_address_type_id ON addresses (address_type_id);
CREATE INDEX i_addresses_prefecture_id ON addresses (prefecture_id);

CREATE TABLE items (
    id bigserial UNIQUE NOT NULL,
    name varchar(128) NOT NULL,
    description text,
    quantity int NOT NULL,
    price numeric(10,2) NOT NULL,
    updated timestamp with time zone NOT NULL DEFAULT now(),
    created timestamp with time zone NOT NULL DEFAULT now(),

    PRIMARY KEY (id)
);

CREATE TABLE carts (
    id bigserial UNIQUE NOT NULL,
    user_id bigint NOT NULL,
    updated timestamp with time zone NOT NULL DEFAULT now(),
    created timestamp with time zone NOT NULL DEFAULT now(),

    PRIMARY KEY (id)
);
CREATE INDEX i_carts_user_id ON carts (user_id);

CREATE TABLE cart_items (
    id bigserial UNIQUE NOT NULL,
    cart_id bigint NOT NULL,
    item_id bigint NOT NULL,
    category_id bigint UNIQUE NOT NULL,
    quantity int NOT NULL,
    updated timestamp with time zone NOT NULL DEFAULT now(),
    created timestamp with time zone NOT NULL DEFAULT now(),

    PRIMARY KEY (id)
);
CREATE INDEX i_cart_items_cart_id ON cart_items (cart_id);

CREATE TABLE orders (
    id bigserial UNIQUE NOT NULL,
    cart_id bigint NOT NULL,
    order_status int NOT NULL DEFAULT 0,
    order_time timestamp with time zone NOT NULL DEFAULT now(),
    shipment_time timestamp with time zone NOT NULL DEFAULT now(),
    shipment_user_id bigint,
    track_no varchar(64),
    user_id bigint NOT NULL,
    item_id bigint NOT NULL,
    category_id bigint UNIQUE NOT NULL,
    order_address_id bigint NOT NULL,
    delivery_address_id bigint NOT NULL,
    charge_address_id bigint NOT NULL,
    updated timestamp with time zone NOT NULL DEFAULT now(),
    created timestamp with time zone NOT NULL DEFAULT now(),

    PRIMARY KEY (id)
);
CREATE INDEX i_orders_cart_id ON orders (cart_id);
CREATE INDEX i_orders_user_id ON orders (user_id);
CREATE INDEX i_orders_item_id ON orders (item_id);
CREATE UNIQUE INDEX i_orders_cart_id_item_id ON orders (cart_id, item_id);

CREATE TABLE categories (
    id bigserial UNIQUE NOT NULL,
    name varchar(128) NOT NULL,
    description text,
    parent_category_id bigint,
    updated timestamp with time zone NOT NULL DEFAULT now(),
    created timestamp with time zone NOT NULL DEFAULT now(),

    PRIMARY KEY (id)
);

CREATE TABLE item_categories (
    id bigserial UNIQUE NOT NULL,
    item_id bigint NOT NULL,
    category_id bigint UNIQUE NOT NULL,

    PRIMARY KEY (id)
);

CREATE TABLE administrators (
    id bigserial UNIQUE NOT NULL,
    name varchar(64) UNIQUE NOT NULL,
    login_name varchar(64) UNIQUE NOT NULL,
    mail_address varchar(256) UNIQUE NOT NULL,
    password varchar(128) DEFAULT '********',
    updated timestamp with time zone NOT NULL DEFAULT now(),
    created timestamp with time zone NOT NULL DEFAULT now(),

    PRIMARY KEY (id)
);

SQLite

DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS prefectures;
DROP TABLE IF EXISTS address_types;
DROP TABLE IF EXISTS addresses;
DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS carts;
DROP TABLE IF EXISTS cart_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS item_categories;
DROP TABLE IF EXISTS administrators;


CREATE TABLE users (
    id bigserial UNIQUE NOT NULL,
    name varchar(128) NOT NULL DEFAULT '',
    password varchar(128) DEFAULT '********',
    mail_address varchar(256) UNIQUE NOT NULL,
    updated timestamp with time zone NOT NULL DEFAULT now(),
    created timestamp with time zone NOT NULL DEFAULT now(),

    PRIMARY KEY (id)
);
CREATE INDEX i_users_mail_address ON users (mail_address);

CREATE TABLE prefectures (
    id int UNIQUE NOT NULL,
    name varchar(4) UNIQUE NOT NULL,
    sort_order int UNIQUE NOT NULL,

    PRIMARY KEY (id)
);

CREATE TABLE address_types (
    id int UNIQUE NOT NULL,
    name varchar(10) UNIQUE NOT NULL,

    PRIMARY KEY (id)
);

CREATE TABLE addresses (
    id bigserial UNIQUE NOT NULL,
    user_id bigint NOT NULL,
    name varchar(128) NOT NULL,
    address_type_id bigint,
    post_code varchar(7) NOT NULL,
    prefecture_id bigint NOT NULL,
    address1 varchar(128) NOT NULL,
    address2 varchar(128),
    updated timestamp with time zone NOT NULL DEFAULT now(),
    created timestamp with time zone NOT NULL DEFAULT now(),

    PRIMARY KEY (id)
);
CREATE INDEX i_addresses_user_id ON addresses (user_id);
CREATE INDEX i_addresses_address_type_id ON addresses (address_type_id);
CREATE INDEX i_addresses_prefecture_id ON addresses (prefecture_id);

CREATE TABLE items (
    id bigserial UNIQUE NOT NULL,
    name varchar(128) NOT NULL,
    description text,
    quantity int NOT NULL,
    price numeric(10,2) NOT NULL,
    updated timestamp with time zone NOT NULL DEFAULT now(),
    created timestamp with time zone NOT NULL DEFAULT now(),

    PRIMARY KEY (id)
);

CREATE TABLE carts (
    id bigserial UNIQUE NOT NULL,
    user_id bigint NOT NULL,
    updated timestamp with time zone NOT NULL DEFAULT now(),
    created timestamp with time zone NOT NULL DEFAULT now(),

    PRIMARY KEY (id)
);
CREATE INDEX i_carts_user_id ON carts (user_id);

CREATE TABLE cart_items (
    id bigserial UNIQUE NOT NULL,
    cart_id bigint NOT NULL,
    item_id bigint NOT NULL,
    category_id bigint UNIQUE NOT NULL,
    quantity int NOT NULL,
    updated timestamp with time zone NOT NULL DEFAULT now(),
    created timestamp with time zone NOT NULL DEFAULT now(),

    PRIMARY KEY (id)
);
CREATE INDEX i_cart_items_cart_id ON cart_items (cart_id);

CREATE TABLE orders (
    id bigserial UNIQUE NOT NULL,
    cart_id bigint NOT NULL,
    order_status int NOT NULL DEFAULT 0,
    order_time timestamp with time zone NOT NULL DEFAULT now(),
    shipment_time timestamp with time zone NOT NULL DEFAULT now(),
    shipment_user_id bigint,
    track_no varchar(64),
    user_id bigint NOT NULL,
    item_id bigint NOT NULL,
    category_id bigint UNIQUE NOT NULL,
    order_address_id bigint NOT NULL,
    delivery_address_id bigint NOT NULL,
    charge_address_id bigint NOT NULL,
    updated timestamp with time zone NOT NULL DEFAULT now(),
    created timestamp with time zone NOT NULL DEFAULT now(),

    PRIMARY KEY (id)
);
CREATE INDEX i_orders_cart_id ON orders (cart_id);
CREATE INDEX i_orders_user_id ON orders (user_id);
CREATE INDEX i_orders_item_id ON orders (item_id);
CREATE UNIQUE INDEX i_orders_cart_id_item_id ON orders (cart_id, item_id);

CREATE TABLE categories (
    id bigserial UNIQUE NOT NULL,
    name varchar(128) NOT NULL,
    description text,
    parent_category_id bigint,
    updated timestamp with time zone NOT NULL DEFAULT now(),
    created timestamp with time zone NOT NULL DEFAULT now(),

    PRIMARY KEY (id)
);

CREATE TABLE item_categories (
    id bigserial UNIQUE NOT NULL,
    item_id bigint NOT NULL,
    category_id bigint UNIQUE NOT NULL,

    PRIMARY KEY (id)
);

CREATE TABLE administrators (
    id bigserial UNIQUE NOT NULL,
    name varchar(64) UNIQUE NOT NULL,
    login_name varchar(64) UNIQUE NOT NULL,
    mail_address varchar(256) UNIQUE NOT NULL,
    password varchar(128) DEFAULT '********',
    updated timestamp with time zone NOT NULL DEFAULT now(),
    created timestamp with time zone NOT NULL DEFAULT now(),

    PRIMARY KEY (id)
);

実行方法

まず、Graphviz をインストールしてパスを通してください。
そのあと、下記のコマンドを実行すると出力先のフォルダに、変換後の各種ファイルが作成されます。

erdm -output_dir 出力先フォルダパス erdmファイルパス

利用技術

Go 言語で実装しています。erdmファイルのパーサとしてPEGを利用しています。
ER図の作成には Graphviz を利用しています。

開発動機

このソフトウェアを開発した動機として以下の物があります。

  • ソースコードのように差分管理したかった
  • Go言語で何か作ってみたかった
  • PEG 使ってみたかった
  • DSL定義の練習したかった

TODO

  • 定型カラムのグループ化(created, creator, updated, updater のようなグループ)
  • ER図をテーブル名のみにするオプション(大抵テーブル数が増えるので可読性を上げる)
  • ER図のグループ化および分割(特定のグループ単位でリレーションを表示)
  • パースエラーの内容をわかりやすくしたい
  • HTMLのデザイン誰か…

最後に

作りたてほやほやのソフトウェアなのでバグどしどし登録してください。
PRもお待ちしております。

26
17
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
26
17