こんにちは。CData Software Japan リードエンジニアの杉本です。
「Cacco」というサービスはご存知でしょうか？
Caccoとは、フローチャートからワイヤーフレーム、プレゼン資料まで作れる「オンライン作図ツール」です。
作図ツールとしてもすごく使いやすいのですが、CaccoはAWSの構成図を自動生成できたりと、なかなか意欲的な機能が多いサービスです。
そんなCaccoで最近、以下の新機能を発表したというプレスリリースを見ました！
ざっくり言うと、以下のようなデータベーススキーマを自動的に生成してくれる機能が追加されたというものです！
使い方は簡単で、画面からCaccoデータベーススキーマ生成用のSQLを使用して、CSV保存したものを、アップロードするだけです。
基本的にはMySQL、PostgreSQLが対象と書かれているのですが、以下のBlogでBigQueryから生成したりしているのも発見！
おおお！最終的にCSV出力すればいい、というわかりやすさから、汎用性が高い感じでいいですね！
とすると・・・
「CData Driverを使えば、Dynamics365やSalesforce、kintoneなど、200種類近くあるデータソースラインナップからも同様に生成できるのではないか？」
と思いたち、早速試してみました！
アプローチ
現在Coccoで使われているSQLは以下のような感じです。これをそのまま使えればいいのですが、これはMySQLやPostgreSQLでサポートされている構文のため、そのままではCData DriverのODBCやJDBCでは実行できません。
SELECT 'mysql' dbms, t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME, c.ORDINAL_POSITION, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH, n.CONSTRAINT_TYPE, k.REFERENCED_TABLE_SCHEMA, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.TABLES t LEFT JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_SCHEMA=c.TABLE_SCHEMA AND t.TABLE_NAME=c.TABLE_NAME LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON c.TABLE_SCHEMA=k.TABLE_SCHEMA AND c.TABLE_NAME=k.TABLE_NAME AND c.COLUMN_NAME=k.COLUMN_NAME LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS n ON k.CONSTRAINT_SCHEMA=n.CONSTRAINT_SCHEMA AND k.CONSTRAINT_NAME=n.CONSTRAINT_NAME AND k.TABLE_SCHEMA=n.TABLE_SCHEMA AND k.TABLE_NAME=n.TABLE_NAME WHERE t.TABLE_TYPE='BASE TABLE' AND t.TABLE_SCHEMA=database()
なので、この構文をベースにして、CDataが提供する汎用的なシステムテーブルでクエリできる形式に変換してみました。
CData Driver ではシステムテーブル郡が以下のように定義されているので、これを置き換えていくイメージです。
最終的に出来上がったのは、以下のようなクエリです。
SELECT 'mysql' dbms, t.SchemaName TABLE_SCHEMA, t.TableName TABLE_NAME, c.ColumnName COLUMN_NAME, c.Ordinal ORDINAL_POSITION, c.DataTypeName DATA_TYPE, c.Length CHARACTER_MAXIMUM_LENGTH, CASE WHEN k.IsKey THEN 'PRIMARY KEY' WHEN k.IsForeignKey THEN 'FOREIGN KEY' ELSE null END CONSTRAINT_TYPE, k.ReferencedSchemaName REFERENCED_TABLE_SCHEMA, k.ReferencedTableName REFERENCED_TABLE_NAME, k.ReferencedColumnName REFERENCED_COLUMN_NAME FROM sys_tables t LEFT JOIN sys_tablecolumns c ON t.SchemaName=c.SchemaName AND t.TableName=c.TableName LEFT JOIN sys_keycolumns k ON c.SchemaName=k.SchemaName AND c.TableName=k.TableName AND c.ColumnName=k.ColumnName
実際にDynamics365からテーブルスキーマを取得する
それでは実際に上記SQLを用いて、Caccoの取り込みCSVを作成してみます。
データソースはなんでもかまいませんが、今回はリレーションがわかりやすく、私自身も案件でこのような構成図の作成に度々苦労した経験がある Dynamics 365 Salesを選んでみました。ちなみに、PowerApps Common Data Service でもいけます。
利用するテクノロジーはODBCを選んでいますが、JDBCでも同様に実行可能です。
https://www.cdata.com/jp/drivers/d365sales/odbc/
このODBCドライバーをインストールし、DSNの接続構成を行います。Dynamics 365 の場合は、以下のように接続先となるOrganization Urlを入力して、接続のテストを実施してください。OAuthが働いて、ブラウザが立ち上がるので、ログインすればOKです。
次にこのODBC DSNを実行してCSVを取得しますが、今回は手軽に再現しやすいようにPowerShellのスクリプトを書きました。
以下のGithubで公開しています。
[void][System.Reflection.Assembly]::LoadWithPartialName("System.Data") # 任意のDSNを指定 $connectionsString = "DSN=CData D365Sales Source" $odbcCon = New-Object System.Data.Odbc.OdbcConnection($connectionsString) $odbcCon.Open(); $odbcCmd = New-Object System.Data.Odbc.OdbcCommand $odbcCmd.Connection = $odbcCon # コマンド実行（SELECT） $odbcCmd.CommandText = " SELECT 'mysql' dbms, t.SchemaName TABLE_SCHEMA, t.TableName TABLE_NAME, c.ColumnName COLUMN_NAME, c.Ordinal ORDINAL_POSITION, c.DataTypeName DATA_TYPE, c.Length CHARACTER_MAXIMUM_LENGTH, CASE WHEN k.IsKey THEN 'PRIMARY KEY' WHEN k.IsForeignKey THEN 'FOREIGN KEY' ELSE null END CONSTRAINT_TYPE, k.ReferencedSchemaName REFERENCED_TABLE_SCHEMA, k.ReferencedTableName REFERENCED_TABLE_NAME, k.ReferencedColumnName REFERENCED_COLUMN_NAME FROM sys_tables t LEFT JOIN sys_tablecolumns c ON t.SchemaName=c.SchemaName AND t.TableName=c.TableName LEFT JOIN sys_keycolumns k ON c.SchemaName=k.SchemaName AND c.TableName=k.TableName AND c.ColumnName=k.ColumnName WHERE t.TableName IN ('accounts','contacts','opportunities'); " #SQLを実行し結果をDataSetやDataTableに格納 $odbcDataAdapter = New-Object -TypeName System.Data.Odbc.OdbcDataAdapter($odbcCmd) $dataSet = New-Object -TypeName System.Data.DataSet $odbcDataAdapter.Fill($dataSet) > $null # 実行結果を破棄する #データセットのデータテーブルをCSV出力する $dataSet.Tables[0] | export-csv export_csvFile.csv -notypeinformation -Encoding Default # コマンドオブジェクト破棄 $odbcCmd.Dispose() # DB切断 $odbcCon.Close() $odbcCon.Dispose()
DSN名は各ドライバーに合わせて変更してください。
また、標準のDynamics 365 はエンティティが大量に存在するので、全部取り込もうとすると、かなりわかりづらくなります。そのため、以下のWhere句の部分で対象とするスキーマを限定するのがおすすめです。
WHERE t.TableName IN ('accounts','contacts','opportunities');
実行すると、以下のようなCSVファイルが得られます。
CSVファイルを生成したら、後はCaccoに取り込むだけです。
作図のメニューから「データベーススキーマ」を選択し
CSVファイルはすでにあるので、「次へ」をクリック
生成したCSVファイルを読み込んで「OK」をクリックすれば、取り込まれます。
暫くすると以下のような形でデータベーススキーマが表示されました！ちなみに対象テーブルを絞り込んだせいで、リレーション先のテーブルが足りないとエラーメッセージが色々と出ますが、とりあえず正常に読み込めている感じです。
なお、型や桁数はCData Driverを介したものが表示されるので、クラウドサービス側の方とは厳密には異なりますので要注意です。
各ドライバーは30日間のトライアルも提供しているので、ぜひお好みのデータソースで試してみてください。
