きっかけ
・ログや一時的な大量のデータ（機械学習のためのスクレイピングしたデータなど）を置いておきたい！テキストファイルで保管は嫌だ！でも、テーブルをいちいち作るのもめんどくさい！
・DocumentDBだと出来ないことがある、そしてお手軽なものが無い！
・Interfaceを使ってみたかった（個人的な理由）
というわけで、jsonを入れると動的にRDBのテーブル・カラムを作成してくれるライブラリを作ってみました
現在対応してるDBは
・SQLite
・PostgreSQL
です
実際に使ってみる
使用するJson
{
"category": "human",
"first_name": "Jirou",
"last_name": "Tanaka",
"birthday": "1999-04-23T19:38:02.929Z",
"live": true
}
プログラム
var executer = new DynamicRDBService(new SQLiteCreator(), new SqliteRepository(new SqliteDBConfig().OpendSQLiteConnection()));
var startupPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
//json読み込み
JObject jObject = ReadJsonFile(Path.Combine(startupPath, "test.json"));
//category要素をテーブル名とする
string tableName = jObject["category"].ToString();
jObject.Remove("category");
var dbobjects = new DBobjectConverter().JsonToDBObject(jObject);
//Insertするデータと、テーブル名を指定する
executer.DynamicInsert(jObject, tableName);
private static JObject ReadJsonFile(string path)
{
var jsonStr = ReadFile(path);
return JObject.Parse(jsonStr);
}
結果
jsonのcategoryの値であった、「human」がテーブル名として、そのほかの要素がカラム名となってデータがInsertされました！
また、カラムの型もjsonと対応する型になっています
動的にカラムも作れる！
同じく使用するJsonと、今度はxmlも使用してみます
{
"category": "animal",
"classification": "dog",
"name": "poti",
"age": 5,
"weight": 6.5
}
{
"category": "animal",
"classification": "cat",
"name": "mary",
"beard_length": 18,
"cute":true
}
<xml>
<category>animal</category>
<classification>Tiger</classification>
<name>tora</name>
<age>14</age>
<weight>59</weight>
<cute>false</cute>
</xml>
プログラム
var executer = new DynamicRDBService(new SQLiteCreator(), new SqliteRepository(new SqliteDBConfig().OpendSQLiteConnection()));
var startupPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
jObject = ReadJsonFile(Path.Combine(startupPath, "test2.json"));
info = CreateInfo(jObject);
executer.DynamicInsert(info.Item1, info.Item2);
//AddColumn
jObject = ReadJsonFile(Path.Combine(startupPath, "test3.json"));
info = CreateInfo(jObject);
executer.DynamicInsert(info.Item1, info.Item2);
//xml（XMLを読込んで、Jsonに変換）
XmlDocument doc = new XmlDocument();
var str = ReadFile(Path.Combine(startupPath, "test4.xml"));
doc.LoadXml(str);
string jsonText = JsonConvert.SerializeXmlNode(doc);
var jObjectTemp = JObject.Parse(jsonText);
jObject = new JObject();
foreach (var j in jObjectTemp["xml"].Children())
jObject.Add(j);
info = CreateInfo(jObject);
executer.DynamicInsert(info.Item1, info.Item2);
private static JObject ReadJsonFile(string path)
{
var jsonStr = ReadFile(path);
return JObject.Parse(jsonStr);
}
private static string ReadFile(string path)
{
string str = string.Empty;
using (StreamReader sr = new StreamReader(path))
{
str = sr.ReadToEnd();
}
return str;
}
private static (IEnumerable<DBObject>, string) CreateInfo(JObject json)
{
string tableName = json["category"].ToString();
json.Remove("category");
var dbobjects = new DBobjectConverter().JsonToDBObject(json);
return (dbobjects, tableName);
}
結果
test2.jsonがDynamicInsertされた時点でのDB
それに続き、test3.json、test4.xmlがDynamicInsertされた時点でのDB
test2.jsonには無く、test3.json、test4.xmlに存在した要素「beard_length」、「cute」がカラムとして追加されています
マルチインサートも対応しています
何故かというと、DynamicInsertはInsertのたびにテーブル存在チェックとカラム存在チェックをしているので、動作が遅いからです
DynamicMultiInsertでは、配列の1番目を元にテーブル、カラムを作成し、それ以降のテーブル変更はありません
（なので、バラバラの構造の配列の場合エラーになる）
使用するJson
{
"array": [
{
"category": "fruits",
"name": "apple",
"price": 150,
"sweetness": 5.5
},
{
"category": "fruits",
"name": "orange",
"price": 120,
"sweetness": 3.5
},
{
"category": "fruits",
"name": "ruby-roman",
"price": 15000,
"sweetness": 24
}
]
}
プログラム
jObject = ReadJsonFile(Path.Combine(startupPath, "test5.json"));
List<IEnumerable<DBObject>> dBObjects = new List<IEnumerable<DBObject>>();
string dbName = string.Empty;
foreach (JObject j in jObject["array"].Children()){
var dbinfo = CreateInfo(j);
dBObjects.Add(dbinfo.Item1);
dbName = dbinfo.Item2;
}
executer.DynamicMultiInsert(dBObjects, dbName);
課題
一通りの機能はできましたが、まだ課題がたくさんあります
・SQLは大文字小文字区別されないため、存在チェックでエラーになる場合がある
・SQLインジェクションはめちゃくちゃ出来る状態
・Jsonの入れ子は対応していない。再帰処理使えば出来ると思うがあまりやる気が起きない
・内部にTableDefinitionのリストを持って、DBに接続せずカラム存在チェックをしよう
・TableDefinitionの際、テーブル名が大文字だと取得できなかったりする？
→インサートの時にテーブル名が勝手に小文字にってる
最後に
課題はたくさんありますが、意外とサクッと出来ました
データベースを作成するDDLやら、interfaceやら（複数DB対応したので使った）中々書いたり使ったりする機会がないので
それらを経験できたのは良かったなーっておもいます
ここで紹介したライブラリのgitリポジトリは下記になります（DynamicRDBExampleにここで書かれてるコードがあります）
https://github.com/HawkClaws/DynamicRDB