A5:SQL Mk-2 とは

フリーのSQLGUIクライアントツールで、データベースの管理や操作を簡単に行うための機能、ER図の管理、スキーマの比較等の機能が豊富に備わっています。
A5:SQL Mk-2ホームページ
ツール上でER図とテーブル定義が書けて、テーブル定義のファイルはテキストの独自の形式なので、git等で差分がわかりやすく、
定義したテーブル定義からDDL文が出力でき、テーブル定義更新の際は、マクロ機能を使ってデータ移行もしてくれる。
また、納品物としてExcelのテーブル定義が求められる場合は、ツールからテーブル定義を元に、Excelのテーブル定義も出力してくれる。
スキーマ比較機能もあるので、例えば、開発環境と本番環境で、インデックス含めてテーブル定義に差異が無いことの確認によく使っている機能です。
ただ、Windows向けのツールで、Macで動作させるにはWine(Windows アプリケーションをMacで動かせる様にするツール)を使う必要があります。
2024/07/28 時点 の最新のベータ版 Version 2.20.0 beta 23 のAIアシスタント機能を使ってみたいと思います。

目次

GeminiのAPIキーの発行・取得

Google AI Studio のGemini API を発行します。
私はGoogle Cloud のアカウントを持っているgmailアカウントを持っているので、課金設定しているgmailアカウントを使って、Google AI Studio のアカウントを作成しました。

1.Google AI StudioのGemini APIのページにアクセスします。

2.左上の「Get API key」ボタンを押下

3.「キー APIキーを作成」ボタンを押下

後はポチポチ操作を進めてAPIキー作成、コピーします。

A5:SQL Mk-2のAIアシスタント機能有効化

2024/07/28 時点 の最新のベータ版 Version 2.20.0 beta 23 のA5:SQL Mk-2をインストールして、起動します。

1.メニュー – 設定 – オプションを選択します。

2.「AIアシスタント」タブを選択します。下記設定し「OK」ボタンを押下して設定を有効化します。

  • 「AIアシスタント機能を有効化する」：チェック
  • サービス：Gemini V1beta(Google)
  • APIキー：「GeminiのAPIキーの発行・取得」で発行したAPIキー
  • その他デフォルト

AIアシスタント設定

SQL文の作成指示してみた

AIアシスタント機能を使ってSQL文の作成指示をしてみます。

1.テスト用に簡単なテーブル定義を作成します。下記A5:SQL Mk-2 ER図ファイルを元にDDL文を出力してテーブルをデータベースに実行してテーブルを作成します。在庫管理に使えそうなテーブル定義です。

サンプルテーブル

# A5:ER FORMAT:18
# A5:ER ENCODING:UTF8
# A5:ER Mk-1 Copyright © 2007 m.matsubara
# A5:SQL Mk-2 Version 2.20.0 beta 23 Copyright © 1997 - 2024 m.matsubara
#  https://a5m2.mmatsubara.com

[Manager]
ProjectName=Test AI
Author=
MaxEntityRowShow=1000
ReadOnlyRecommend=0
Page=AIアシスタントテスト
PageInfo="AIアシスタントテスト",5,"A3Landscape",$FFFFFF
LogicalView=1
DecodeDomain=0
ViewModePageIndividually=1
ViewMode=5
ViewFormat=0
UseNondependenceDashLine=0
FontName=Tahoma
FontSize=6
PaperSize=A4Landscape
HeaderLeft=
HeaderCenter=
HeaderRight=
FooterLeft=
FooterCenter=
FooterRight=
ShowPageoutRelation=1
RDBMSType=6
RDBMSTypeName=MySQL
DefaultPkName=%0:s_PKC
DefaultPkIndexName=%0:s_PKI
DefaultIndexName=%0:s_IX%1:d
DefaultFkName=%0:s_FK%1:d
SqlSeparator=0
UpperCaseKeyword=0
ShowTag=1
ShowCommonAttributes=0
BugFixEntityWidth=1

[Entity]
PName=sales
LName=売上
Comment=
TableInnerOption=
TableOption=
Page=AIアシスタントテスト
Left=950
Top=150
Field="売上ID","sale_id","BIGINT UNSIGNED AUTO_INCREMENT","NOT NULL",0,"","",$FFFFFFFF,""
Field="仕入ID","purchase_id","BIGINT UNSIGNED","NOT NULL",,"","",$FFFFFFFF,""
Field="売上日","sale_date","DATE","NOT NULL",,"","",$FFFFFFFF,""
Field="売上数量","volume","INT","NOT NULL",,"","",$FFFFFFFF,""
Field="作成者","created_by","varchar(30)",,,"null","",$FFFFFFFF,""
Field="作成日時","created_at","datetime","NOT NULL",,"now()","",$FFFFFFFF,""
Field="更新者","updated_by","varchar(30)",,,"null","",$FFFFFFFF,""
Field="更新日時","updated_at","datetime","NOT NULL",,"now()","",$FFFFFFFF,""
EffectMode=None
Color=$000000
BkColor=$FFFFFF
ModifiedDateTime=20240728005110
Position="AIアシスタントテスト",950,150
ZOrder=1

[Entity]
PName=purchases
LName=仕入
Comment=
TableInnerOption=
TableOption=
Page=AIアシスタントテスト
Left=550
Top=150
Field="仕入ID","purchase_id","BIGINT UNSIGNED AUTO_INCREMENT","NOT NULL",0,"","",$FFFFFFFF,""
Field="製品ID","product_id","BIGINT UNSIGNED","NOT NULL",,"","",$FFFFFFFF,""
Field="仕入日","purchase_date","DATE","NOT NULL",,"","",$FFFFFFFF,""
Field="仕入数量","volume","INT","NOT NULL",,"","",$FFFFFFFF,""
Field="作成者","created_by","varchar(30)",,,"null","",$FFFFFFFF,""
Field="作成日時","created_at","datetime","NOT NULL",,"now()","",$FFFFFFFF,""
Field="更新者","updated_by","varchar(30)",,,"null","",$FFFFFFFF,""
Field="更新日時","updated_at","datetime","NOT NULL",,"now()","",$FFFFFFFF,""
EffectMode=None
Color=$000000
BkColor=$FFFFFF
ModifiedDateTime=20240728005000
Position="AIアシスタントテスト",550,150
ZOrder=2

[Entity]
PName=products
LName=製品マスター
Comment=
TableInnerOption=
TableOption=
Page=AIアシスタントテスト
Left=200
Top=150
Field="製品ID","product_id","BIGINT UNSIGNED AUTO_INCREMENT","NOT NULL",0,"","",$FFFFFFFF,""
Field="製品名","name","varchar(50)",,,"null","",$FFFFFFFF,""
Field="作成者","created_by","varchar(30)",,,"null","",$FFFFFFFF,""
Field="作成日時","created_at","datetime","NOT NULL",,"now()","",$FFFFFFFF,""
Field="更新者","updated_by","varchar(30)",,,"null","",$FFFFFFFF,""
Field="更新日時","updated_at","datetime","NOT NULL",,"now()","",$FFFFFFFF,""
EffectMode=None
Color=$000000
BkColor=$FFFFFF
ModifiedDateTime=20240728004839
Position="AIアシスタントテスト",200,150
ZOrder=3

[Relation]
Entity1=products
Entity2=purchases
RelationType1=2
RelationType2=3
Fields1=product_id
Fields2=product_id
Cardinarity1=
Cardinarity2=
Position="AIアシスタントテスト",0,1868,8132,1750,R,R,""
Dependence=0
Caption=
PName=
LineMode=0
Bar1=187
Bar2=813
Bar3=175
TermPos1=R
TermPos2=R
ZOrder=4

[Relation]
Entity1=purchases
Entity2=sales
RelationType1=2
RelationType2=3
Fields1=purchase_id
Fields2=purchase_id
Cardinarity1=
Cardinarity2=
Position="AIアシスタントテスト",0,1868,8132,1868,R,R,""
Dependence=0
Caption=
PName=
LineMode=0
Bar1=187
Bar2=813
Bar3=187
TermPos1=R
TermPos2=R
ZOrder=5

2.A5:SQL Mk-2 で 新規のSQLエディタを起動し、AIアシスタント機能を選択します。

AIアシスタント起動

3.「あなたからの質問」に作成したいSQL文の指示を入力して、「AIに質問」ボタンを押下します。

SQL文作成指示

回答は下記が返りました。
データベースに接続してテーブルのDDLもAIに送ってるらしく、正確な回答が返ってきました。
スポットの調査等サクッとSQL文作りたい時とか使えそう。

・あなたからの質問
製品名が「リンゴ」の仕入日が「2024/07/01」～「2024/07/31」の間の在庫数を求めるSQL文を考えてください。

・AIからの回答
SELECT
  SUM(p.volume) - SUM(s.volume) AS stock_quantity
FROM purchases AS p
JOIN products AS pr
  ON p.product_id = pr.product_id
LEFT JOIN sales AS s
  ON p.purchase_id = s.purchase_id
WHERE
  pr.name = 'リンゴ' AND p.purchase_date BETWEEN '2024-07-01' AND '2024-07-31';

**説明**

1. **purchasesテーブルとproductsテーブルを結合**: `product_id` をキーとして結合し、仕入情報と製品情報を取得します。
2. **salesテーブルを左結合**: `purchase_id` をキーとして左結合し、売上情報を取得します。
3. **条件**: `pr.name = 'リンゴ'` で製品名が「リンゴ」であることを指定し、`p.purchase_date BETWEEN '2024-07-01' AND '2024-07-31'` で仕入日が「2024/07/01」～「2024/07/31」の間であることを指定します。
4. **在庫数計算**: `SUM(p.volume) - SUM(s.volume)` で仕入数量から売上数量を引くことで在庫数を計算します。

このSQL文は、指定された期間における「リンゴ」の仕入数量から売上数量を差し引くことで、在庫数を計算します。

インデックスの作成指示してみた


続いて、忘れがちなインデックスの作成をAI指示して考えて貰いました。
「あなたからの質問」に下記指示を入力して、「AIに質問」ボタンを押下します。

・あなたからの質問
インデックスを設定していません。
テーブル構成から必要そうなインデックスを提案してください。

・AIからの回答
## インデックス提案

以下のインデックスを提案します。

**1. products テーブル**

* **product_id**: 主キーなので、自動的にインデックスが設定されます。
* **name**: 製品名で検索する機会が多いと想定されるため、インデックスを作成します。

**2. purchases テーブル**

* **purchase_id**: 主キーなので、自動的にインデックスが設定されます。
* **product_id**: 製品IDで仕入情報を検索する機会が多いと想定されるため、インデックスを作成します。
* **purchase_date**: 仕入日で検索する機会が多いと想定されるため、インデックスを作成します。

**3. sales テーブル**

* **sale_id**: 主キーなので、自動的にインデックスが設定されます。
* **purchase_id**: 仕入IDで売上情報を検索する機会が多いと想定されるため、インデックスを作成します。
* **sale_date**: 売上日で検索する機会が多いと想定されるため、インデックスを作成します。

**インデックス作成例**

-- products テーブル
ALTER TABLE products ADD INDEX (name);

-- purchases テーブル
ALTER TABLE purchases ADD INDEX (product_id);
ALTER TABLE purchases ADD INDEX (purchase_date);

-- sales テーブル
ALTER TABLE sales ADD INDEX (purchase_id);
ALTER TABLE sales ADD INDEX (sale_date);

**補足**

* インデックスは、テーブルのサイズや検索頻度などを考慮して適切に設定する必要があります。
* 上記は一般的な提案であり、実際の運用状況に合わせて調整する必要がある場合があります。
* 特定のクエリのパフォーマンスを向上させるために、複合インデックスを作成することも検討できます。

まともな回答返ってきてますね。
必要以上にインデックスの提案されてますが、参考意見としては十分だと思います。

カラム物理名の改善指示してみた


続いて、忘れがちなインデックスの作成をAI指示して考えて貰いました。
「あなたからの質問」に下記指示を入力して、「AIに質問」ボタンを押下します。

・あなたからの質問
英語が苦手なエンジニアがカラムの物理名を設定しました。
カラムの論理名にそぐわない英語が物理名として設定されている可能性があります。
改善案を提示してください。

・AIからの回答
## カラム名改善案

以下の表は、カラムの物理名と論理名、そして改善案を示しています。

| 物理名 | 論理名 | 改善案 | 説明 |
|---|---|---|---|
| product_id | 製品ID | product_id |  変更不要。IDを表す一般的な命名規則に沿っています。 |
| name | 製品名 | product_name |  "name" は一般的な単語で、製品名を表すのに適しています。 |
| created_by | 作成者 | created_by |  変更不要。作成者を表す一般的な命名規則に沿っています。 |
| created_at | 作成日時 | created_at |  変更不要。作成日時を表す一般的な命名規則に沿っています。 |
| updated_by | 更新者 | updated_by |  変更不要。更新者を表す一般的な命名規則に沿っています。 |
| updated_at | 更新日時 | updated_at |  変更不要。更新日時を表す一般的な命名規則に沿っています。 |
| purchase_id | 仕入ID | purchase_id |  変更不要。IDを表す一般的な命名規則に沿っています。 |
| product_id | 製品ID | product_id |  変更不要。IDを表す一般的な命名規則に沿っています。 |
| purchase_date | 仕入日 | purchase_date |  変更不要。日付を表す一般的な命名規則に沿っています。 |
| volume | 仕入数量 | purchase_quantity |  "volume" は容量を表す単語で、仕入数量を表すには適切ではありません。 |
| created_by | 作成者 | created_by |  変更不要。作成者を表す一般的な命名規則に沿っています。 |
| created_at | 作成日時 | created_at |  変更不要。作成日時を表す一般的な命名規則に沿っています。 |
| updated_by | 更新者 | updated_by |  変更不要。更新者を表す一般的な命名規則に沿っています。 |
| updated_at | 更新日時 | updated_at |  変更不要。更新日時を表す一般的な命名規則に沿っています。 |
| sale_id | 売上ID | sale_id |  変更不要。IDを表す一般的な命名規則に沿っています。 |
| purchase_id | 仕入ID | purchase_id |  変更不要。IDを表す一般的な命名規則に沿っています。 |
| sale_date | 売上日 | sale_date |  変更不要。日付を表す一般的な命名規則に沿っています。 |
| volume | 売上数量 | sale_quantity |  "volume" は容量を表す単語で、売上数量を表すには適切ではありません。 |
| created_by | 作成者 | created_by |  変更不要。作成者を表す一般的な命名規則に沿っています。 |
| created_at | 作成日時 | created_at |  変更不要。作成日時を表す一般的な命名規則に沿っています。 |
| updated_by | 更新者 | updated_by |  変更不要。更新者を表す一般的な命名規則に沿っています。 |
| updated_at | 更新日時 | updated_at |  変更不要。更新日時を表す一般的な命名規則に沿っています。 |

**変更点:**

* `volume` を `purchase_quantity` と `sale_quantity` に変更しました。これは、数量を表すより適切な単語です。

**その他:**

* カラム名は、可能な限り簡潔で、意味が明確であるように命名する必要があります。
* 英語が苦手な場合は、日本語でカラム名を記述し、それを英語に翻訳するツールを使用することもできます。

これらの変更により、データベースの可読性と保守性が向上します。

「volume 」→「quantity」の改善提案は良いですね。期待通りでした。また、変な提案も無く良さそう。

テーブル物理名、カラム物理名のルールをチェックさせてみた


最後に、テーブル物理名、カラム物理名のルールが現状のテーブル定義に適用されているかチェックして貰い、
ルールに沿っていないカラムを抽出して、改善案を提示して貰おうと思います。

・あなたからの質問
テーブル名とカラム名の物理名は下記ルールとします。
ルールに沿っていないカラムを抽出して、改善案を提示してください。

テーブル名の物理名のルール
・スネーク式
・複数形にする

カラム名の物理名のルール
・スネーク式
・フラグの場合は先頭に「is_」を付ける。
・DATE型のカラムには末尾に「_on」。
・DATETIME型のカラムには末尾に「_at」。
・自動採番のIDの場合は、bigint unsigned auto_increment
・各テーブルに必ず下記４つのカラムを付ける
　物理名：created_by、型：VARCHAR(50)
　物理名：created_at、型：datetime
　物理名：updated_by、型：VARCHAR(50)
　物理名：updated_at、型：datetime

・AIからの回答
## テーブル・カラム名改善提案

以下のテーブル・カラム名がルールに沿っていません。

**products テーブル**

| 現在のカラム名 | 改善案 | 説明 |
|---|---|---|
| product_id | product_id | ルールに準拠 |
| name | product_name | スネーク式、名詞複数形 |
| created_by | created_by | ルールに準拠 |
| created_at | created_at | ルールに準拠 |
| updated_by | updated_by | ルールに準拠 |
| updated_at | updated_at | ルールに準拠 |

**purchases テーブル**

| 現在のカラム名 | 改善案 | 説明 |
|---|---|---|
| purchase_id | purchase_id | ルールに準拠 |
| product_id | product_id | ルールに準拠 |
| purchase_date | purchase_date_on | DATE型なので末尾に_on |
| volume | purchase_volume | スネーク式 |
| created_by | created_by | ルールに準拠 |
| created_at | created_at | ルールに準拠 |
| updated_by | updated_by | ルールに準拠 |
| updated_at | updated_at | ルールに準拠 |

**sales テーブル**

| 現在のカラム名 | 改善案 | 説明 |
|---|---|---|
| sale_id | sale_id | ルールに準拠 |
| purchase_id | purchase_id | ルールに準拠 |
| sale_date | sale_date_on | DATE型なので末尾に_on |
| volume | sale_volume | スネーク式 |
| created_by | created_by | ルールに準拠 |
| created_at | created_at | ルールに準拠 |
| updated_by | updated_by | ルールに準拠 |
| updated_at | updated_at | ルールに準拠 |

**改善後のテーブル定義**

-- 製品マスター
CREATE TABLE products (
  product_id bigint unsigned auto_increment -- 製品ID
  , product_name varchar(50)    -- 製品名
  , created_by varchar(50)  -- 作成者
  , created_at datetime -- 作成日時
  , updated_by varchar(50)  -- 更新者
  , updated_at datetime -- 更新日時
  , primary key (product_id)
);

-- 仕入
CREATE TABLE purchases (
  purchase_id bigint unsigned auto_increment    -- 仕入ID
  , product_id bigint unsigned  -- 製品ID
  , purchase_date_on date   -- 仕入日
  , purchase_volume int -- 仕入数量
  , created_by varchar(50)  -- 作成者
  , created_at datetime -- 作成日時
  , updated_by varchar(50)  -- 更新者
  , updated_at datetime -- 更新日時
  , primary key (purchase_id)
);

-- 売上
CREATE TABLE sales (
  sale_id bigint unsigned auto_increment    -- 売上ID
  , purchase_id bigint unsigned -- 仕入ID
  , sale_date_on date   -- 売上日
  , sale_volume int -- 売上数量
  , created_by varchar(50)  -- 作成者
  , created_at datetime -- 作成日時
  , updated_by varchar(50)  -- 更新者
  , updated_at datetime -- 更新日時
  , primary key (sale_id)
);

DATE型のカラムの末尾に_onを付けてくれたのは期待通りです。
また、改善案に出力されていませんが、改善案のDDL文のcreated_by、updated_byの型がvarchar(30)→varchar(50)に変えてくれてます。

A5:SQL Mk-2のAIアシスタント機能良いですね。
個人的には、カラム物理名の英語チェックと、ルールのチェックは使えそうと思いました。

まだbeta版で今後更に修正されること期待できそうですが、
作成済みのテーブルに対するAIアシスタントだけでなく、
ER図作成中のER図ファイルを使ったAIアシスタントもできる様になるとうれしいと思いました。
今後の修正に期待ですね。