きっかけ

・ログや一時的な大量のデータ（機械学習のためのスクレイピングしたデータなど）を置いておきたい！テキストファイルで保管は嫌だ！でも、テーブルをいちいち作るのもめんどくさい！
・DocumentDBだと出来ないことがある、そしてお手軽なものが無い！
・Interfaceを使ってみたかった（個人的な理由）

というわけで、jsonを入れると動的にRDBのテーブル・カラムを作成してくれるライブラリを作ってみました

現在対応してるDBは
・SQLite
・PostgreSQL
です

実際に使ってみる

使用するJson

test.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も使用してみます

test2.json

{
    "category": "animal",
    "classification": "dog",
    "name": "poti",
    "age": 5,
    "weight": 6.5
}

test3.json

{
    "category": "animal",
    "classification": "cat",
    "name": "mary",
    "beard_length": 18,
    "cute":true
}

test4.xml

<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

test5.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

jsonを入れるだけで、動的にRDBのテーブル・カラムを作成してくれるライブラリ「DynamicRDB」を作ってみた

きっかけ

実際に使ってみる

結果

動的にカラムも作れる！

結果

マルチインサートも対応しています

課題

最後に