LinqPad で SQL Server の System Table を利用して、SqlAlchemy: models.py のコードを生成

# 背景
alembic での Database first での モデル生成方法を調べる前にまずは、LinqPad でサクッとやれる方法を準備しておく

https://alembic.sqlalchemy.org/en/latest/

最終的には、AST か alembic で出来たらいいなとは・・

# 環境
LinqPad 7.4.9

# 概要
1. System Table から Table/Column/Index 情報を取得
1. python class テキスト整形

# 詳細
## System Table からの情報取得
LinqPad で System 情報を取得できるように設定変更

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/2f2b577a-0e4d-f0e9-7b99-c532ffa510d5.png)

これで以下のように System 関連が取得出来るようになる
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/a3b46b1e-0f0f-7271-0256-a0f2d927d60f.png)

## System Tables から Schema 情報取得
- sys.Tables
    - Table 一覧：Object_id で紐付け
- sys.Indexes
    - Key 取得用：Object_id で紐付け<br>![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/9e4a00ac-aa7f-a887-8191-a616b44259eb.png)
- sys.Index_columns
    - Key の Table 内Column との紐付け情報取得用：Object_id, Index_id で紐付け<br>![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/ffd10658-f77e-911b-a102-2e957f82ca10.png)
- sys.Columns
    - Table 内の Column 情報の取得用：Object_id, Column_id で紐付け<br>![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/5827abc3-92e4-a79d-98d9-18de1d54167e.png)

あとは、取得した Tables/Columns 情報をもって Model 用テキストを生成するだけ

```c#: 出力Sample
void Main()
{
	//var targetTableNames =　new List<string>(){"TestTable", "TestTable2"};
	
	var toPythonType = (string typeName, int length)=> {
		var result = "undefined";
		switch (typeName){
			case "char": 
			case "varchar":
			case "nvarchar":
				return $"String({length})";
			case "datetime":
				return $"DateTime";
			case "bigint":
			case "int":
				return $"Integer";
			default:
				break;
		}
		return result;
	};
	var targetTables = sys.Tables; //.Where(w=> targetTableNames.Contains(w.Name));	
	var indexes = targetTables
		.Join(sys.Indexes, table=> table.Object_id, index=> index.Object_id, (table, index)=> new {
			table.Object_id,
			index.Index_id
		})
		.Join(sys.Index_columns, index=>new {index.Object_id, index.Index_id}, indexColumn=> new {indexColumn.Object_id, indexColumn.Index_id}, (index, indexColumn)=> new {
			indexColumn.Object_id,
			indexColumn.Index_id,
			indexColumn.Column_id,
		})
		.Join(sys.Columns, index=> new {index.Object_id, index.Column_id}, column=> new {column.Object_id, column.Column_id}, (index, column)=> new {
			index.Object_id,
			index.Column_id,
			column.Name,
		});
	var tables = targetTables
		.Join(sys.Columns.Where(w=>! w.Name.StartsWith("×"))
			, table=> table.Object_id, column=> column.Object_id, (table,column)=> new {
		TableName = table.Name,
		table.Object_id,
		column.Column_id,
		ColumnName = column.Name,
		column.Max_length,
		ColumnTypeID = column.User_type_id,
		column.Is_nullable,
	})
	.GroupJoin(sys.Types, table=> table.ColumnTypeID, type=> type.User_type_id, (table, type)=> new {
		table.TableName,
		table.Column_id,
		table.ColumnName,
		table.Max_length,
		table.Is_nullable,
		table.Object_id,
		ColumnType = type.FirstOrDefault() == null?  "": type.First().Name ,
	})
	.GroupJoin(indexes, table=> new { table.Object_id, table.Column_id}, index=>new {index.Object_id, index.Column_id}, (table, index)=> new {
		table.TableName,
		table.ColumnName,
		table.Max_length,
		table.Is_nullable,
		table.ColumnType,
		table.Object_id,
		IsPrimaryKey = index.Any(),
	})
	.GroupBy(g=> g.TableName)
	.ToList();
	
	foreach (var table in tables){
		$"class {table.Key}(Base):\n\t__tablename__ = \"{table.Key}\"".Dump();
		var columns = table.Select(s=> $"{s.ColumnName} = Column({toPythonType(s.ColumnType, s.Max_length)}, nullable={s.Is_nullable}{((s.IsPrimaryKey)?", primary_key=True":"")})")
			.Aggregate("\t", (a,b)=> $"{a}\n\t{b}", result=> result + "\n\n")
			.Dump();
	}
	tables.Dump();
}
// You can define other methods, fields, classes and namespaces here

```

# あとがき
Migration を先に学習しておいた方が良いかもと思っていたら・・Docker も学んでおかないとあかんことに気付いた・・ :sweat: 

# 関連投稿
https://qiita.com/baku2san/items/4fc6bf962bb8efdd9dec

https://qiita.com/baku2san/items/1e2f31d63f3939533cf2