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キーの発行・取得
- A5:SQL Mk-2のAIアシスタント機能有効化
- SQL文の作成指示してみた
- インデックスの作成指示してみた
- カラム物理名の改善指示してみた
- テーブル物理名、カラム物理名のルールをチェックさせてみた
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キー
- その他デフォルト
SQL文の作成指示してみた
AIアシスタント機能を使ってSQL文の作成指示をしてみます。
1.テスト用に簡単なテーブル定義を作成します。下記A5:SQL Mk-2 ER図ファイルを元にDDL文を出力してテーブルをデータベースに実行してテーブルを作成します。在庫管理に使えそうなテーブル定義です。
001 002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020 021 022 023 024 025 026 027 028 029 030 031 032 033 034 035 036 037 038 039 040 041 042 043 044 045 046 047 048 049 050 051 052 053 054 055 056 057 058 059 060 061 062 063 064 065 066 067 068 069 070 071 072 073 074 075 076 077 078 079 080 081 082 083 084 085 086 087 088 089 090 091 092 093 094 095 096 097 098 099 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 | # 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アシスタント機能を選択します。
3.「あなたからの質問」に作成したいSQL文の指示を入力して、「AIに質問」ボタンを押下します。
回答は下記が返りました。
データベースに接続してテーブルのDDLもAIに送ってるらしく、正確な回答が返ってきました。
スポットの調査等サクッとSQL文作りたい時とか使えそう。
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 | ・あなたからの質問 製品名が「リンゴ」の仕入日が「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に質問」ボタンを押下します。
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | ・あなたからの質問 インデックスを設定していません。 テーブル構成から必要そうなインデックスを提案してください。 ・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に質問」ボタンを押下します。
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | ・あなたからの質問 英語が苦手なエンジニアがカラムの物理名を設定しました。 カラムの論理名にそぐわない英語が物理名として設定されている可能性があります。 改善案を提示してください。 ・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」の改善提案は良いですね。期待通りでした。また、変な提案も無く良さそう。
テーブル物理名、カラム物理名のルールをチェックさせてみた
最後に、テーブル物理名、カラム物理名のルールが現状のテーブル定義に適用されているかチェックして貰い、
ルールに沿っていないカラムを抽出して、改善案を提示して貰おうと思います。
001 002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020 021 022 023 024 025 026 027 028 029 030 031 032 033 034 035 036 037 038 039 040 041 042 043 044 045 046 047 048 049 050 051 052 053 054 055 056 057 058 059 060 061 062 063 064 065 066 067 068 069 070 071 072 073 074 075 076 077 078 079 080 081 082 083 084 085 086 087 088 089 090 091 092 093 094 095 096 097 098 099 100 | ・あなたからの質問 テーブル名とカラム名の物理名は下記ルールとします。 ルールに沿っていないカラムを抽出して、改善案を提示してください。 テーブル名の物理名のルール ・スネーク式 ・複数形にする カラム名の物理名のルール ・スネーク式 ・フラグの場合は先頭に「is_」を付ける。 ・DATE型のカラムには末尾に「_on」。 ・DATETIME型のカラムには末尾に「_at」。 ・自動採番のIDの場合は、bigint unsigned auto_increment ・各テーブルに必ず下記4つのカラムを付ける 物理名: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アシスタントもできる様になるとうれしいと思いました。
今後の修正に期待ですね。