「DBをきちんと設計した②」（DDLとスクリプト用意・第１正規化まで）

はじめに
DDLとは
1. 今回作成したDDL
確認

はじめに

前回までで、論理設計の部分を考えました。

正規化を実施し、ER図を作成しました。

今回は、実際にコードに起こしていきます。

DDLの実装です。

DDLとは

SQLは多くの命令を用いてデータベースを取り扱います。

命令は大きく3つに分類され、その中の一つがDDLです。

残りの２つはDMLとDCL。

DDL (Data Definition Language／データ定義言語) とは、データベースやテーブルの構造を定義・変更するために使う言語です。

テーブルやスキーマの作成（CREATE）、変更（ALTER）、削除（DROP）などを行います。

ちなみに、DMLとDCLについてはと言うと、

DML（Data Manipulation Language／データ操作言語）はデータベース内のデータを操作するために使います。操作というのは追加（INSERT）、取得（SELECT）、更新（UPDATE）、削除（DELETE）です。

DCL（Data Control Language／データ制御言語）はユーザーの権限やアクセス制御を管理するために使います。例えば権限の付与（GRANT）や取り消し（REVOKE）です。

今回作成したDDL

今回作成したDDLは以下です。（init.sql）

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS sakura_table;
DROP TABLE IF EXISTS sakura_season_table;
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE `sakura_table` (
       id INT NOT NULL,
       `spot_name` varchar(100),
       `common_season` varchar(100),
・・・【略】・・・
       `url` varchar(100), 
       `lat` float(20,10),
       `lng` float(20,10),
       `season_list` varchar(100),
       PRIMARY KEY (id)
);

LOAD DATA LOCAL INFILE '/tmp/dml/sakura_info.csv' INTO TABLE `sakura_table` FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES;

-- 後ほどpythonで値を追加する外部キーのあるtable
CREATE TABLE `sakura_season_table` (
    id INT,
    season INT,
    PRIMARY KEY (id, season),
    FOREIGN KEY (id) REFERENCES sakura_table(id)
);

idを主キーとしていて、csvファイルを読み込ませています。

外部キーを設定したtable（sakura_season_table）も用意しています。

この時点ではデータは入っていません。

DDLファイル内でデータを入れる方法も考えましたが、かなりコードが煩雑になってしまうことも考慮して、今回の場合は、pythonで追加するようにしました。

ここは運用するサービスによってはDDL内で全て完結させていい場合もあるので、サービスによって運用時のことも考慮して臨機応変に対応する必要があります。

pythonコードは以下です。(exec_1nf_season_spot.pyです。)

import MySQLdb

###########################
# sakura_tableの第1正規化
###########################
# データベース接続

db = MySQLdb.connect(
    user='user_name', 
    passwd='password', 
    host='127.0.0.1', 
    db='db_name'
)
cursor = db.cursor()

# sakura_table からデータを取得
cursor.execute("SELECT id, season_list FROM sakura_table")
rows = cursor.fetchall()

# データの分割と挿入
for row in rows:
    id, season_list = row
    seasons = season_list.split(',')

    for season in seasons:
        season_value = season.strip()
        if season_value.isdigit():
            cursor.execute("INSERT INTO sakura_season_table (id, season) VALUES (%s, %s)", (id, season_value))

今回はこれらDDL、pythonを用いてデータを構築するためのスクリプトファイルも用意しました。

以下のようなスクリプトファイル（setup.sh）です。

#!/bin/bash

# スクリプトのディレクトリを取得
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
echo "Script directory is: $SCRIPT_DIR"
# docker-compose.yml のパスを設定
COMPOSE_FILE="${SCRIPT_DIR}/../../docker-compose-stg.yml"
# 実行するサービス名とコマンドを指定して docker-compose exec を実行
cat ${SCRIPT_DIR}/../init.sql | docker compose -f "$COMPOSE_FILE" exec -T db mysql --user=user --password=passwd

python3 ${SCRIPT_DIR}/../python_codes/exec_1nf_season_spot.py

実行はbashで実施（shだとエラーがでると思います）

$ bash setup.sh

確認

実際に主キーがidに設定されているかを確認

mysql> SHOW INDEX FROM sakura_table WHERE Key_name = 'PRIMARY' \G
*************************** 1. row ***************************
        Table: sakura_table
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 100
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

sakura_season_tableの方も確認

mysql> SHOW INDEX FROM sakura_season_table WHERE Key_name = 'PRIMARY' \G
*************************** 1. row ***************************
        Table: sakura_season_table
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 99
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: sakura_season_table
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 2
  Column_name: season
    Collation: A
  Cardinality: 172
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.02 sec)

sakura_season_tableの方は外部キーが設定されているかも確認

mysql> SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'sakura_season_table' AND REFERENCED_TABLE_NAME IS NOT NULL \G
*************************** 1. row ***************************
       CONSTRAINT_NAME: sakura_season_table_ibfk_1
            TABLE_NAME: sakura_season_table
           COLUMN_NAME: id
 REFERENCED_TABLE_NAME: sakura_table
REFERENCED_COLUMN_NAME: id
1 row in set (0.01 sec)