続 SQLite3 + JSON

ちょっと実用的な情報が少ないと感じたのでせめて使えるレベルまでには持ってゆきたいと。
そう思いつつも、実際は情報をまとめている方々はちらほら見当たるのだけれどもその目的によってかなり使い方が異なるなーなどと、自分なりにまとめておかないとなと思った次第で。
（それくらいの努力はしたくなる程度に魅力的なのがJSON対応）

フリーダムなEXCELシートをよく目にする機会があるとか、ペーパーレスって何ですか？おいしいものですか？　みたいな現場とか案件とか抱えていたり抱えそうになっていたりというあなたには絶対役立つソリューション。> SQLite3 + JSON

プライマリーキー問題 +

これは設計というよりは運用の話。
以前のポストにも書いた通りでレコードのJSON文字列の中の一要素に絞ってキーを指定するなどという芸当は出来なさそうなのでプライマリキーだけは列を分けると便利。だけど、だからと言ってJSON文字列にそのキーが含まれていないとこれまたスーパー不便なのでプライマリキー列とJSON文字列両方にプライマリキーのデータが入っていると便利。

create table if not exists "{tablename}" 
(
    "id" INTEGER, 
    "jsondata" TEXT, 
    PRIMARY KEY("id" AUTOINCREMENT)
)

このようなテーブル設計でプライマリキーを分ける。

データ的に次のようにしたい場合、

id	jsondata
0	'{"invoice": 123}, {"quantity": 100}'

敢えて以下の様に idをjsondataの中にも入れる。

id	jsondata
0	'{"id": 0}, "invoice": 123}, {"quantity": 100}'

データが重複していてとてもキモい。キモいけどこれでものすごい楽になる。

でもあまりにもキモいのでせめて jsondata の中の "id" がプライマリキーに同期するようにしたい場合は以下のように参照する。

select 
	json_set(jsondata, "$.id", id) as row 
from {tablename}

※キモいだけではなくデータ量の話とかにもつながってくるので実運用ではJSON列に挿入したidがDB上のJSON列に残らないようにした方が良さげ。

おさらいの JSON → 列

列はバラバラではなくレコード一行づつJSONとして返してくれたほうが楽なのだけど、どっちにしてもWhere句とかでは必要になるので json_extract は必須。

json_extract(<列名>, "$.<JSONのキー>")

具体的にはこんな風

select * from SHIPPING
where json_extract(jsondata, "$.invoice") = 123

そして脇道

しかし、JSONの入っている列は、SQLite的にはただのテキストの列。つまり…。

select * from SHIPPING
where jsondata like '%invoice": 123%'

等ということが出来、例えばキーに対する値がしっかり意味を成している（かつ状態がユニークな）場合、列をまたいでデータを検索できることになるのでちょっと面白い。（あまり褒められた使い方ではない）

例えば「エラー」という文字がどこかに入っているレコードを探す。等。

部分更新

JSON列を更新したい場合、一度取り出してプライマリキーを指定して。という手順で更新。
で、JSONの一部項目だけを指定して更新したいなどの場合にどうすんだってとこ。
※さすがにselectしてupdateしてみたいなのを繰り返すとハチャメチャ遅い。

replace into {tablename}
select id, json_patch(jsondata, '{data}') 
from jsontable
where id={dataid}

要するにプライマリキーで引っ張りだしたJSONに新たに追加（または更新）したいJSON文字列をマージしてアップデートしてやるという感じ。

この手順を守っていればもともと入っていたデータがどこかに旅立ってしまった等の事故は防げるはず。

Python向けクラスの修正

で、以上を踏まえて前回作ったデータベースクラスのメソッド(Python)の面倒も見ておく。

旧

##    def get(self, tablename, where={}, condition="and", column="jsondata"):
##        sql = f"select * from {tablename}"
##        sql = self.queryString(sql, tablename, where, condition, column)
##        for row in self.fetch(sql):
##            yield {"id": row[0], "data": json.loads(row[1])}

##    def set(self, tablename, data={}, dataid="NULL"):
##        data = str(data).replace("'", '"')
##        sql = f"""replace into {tablename} values({dataid}, '{data}')"""
##        self.query(sql)

新

    def get(self, tablename, where={}, condition="and", column="jsondata"):
        sql = f"""select json_set({column}, "$.id", id) from {tablename}"""
        sql = self.queryString(sql, tablename, where, condition, column)
        for row in self.fetch(sql):
            yield row[0]
            
    def set(self, tablename, data={}, where={}, dataid="NULL", condition="and", column="jsondata"):
        if "id" in data:
            del data["id"]
        data = str(data).replace("'", '"')
        if len(where) == 0 and dataid == "NULL":
            sql = f"""replace into {tablename} values({dataid}, '{data}')"""
        else:
            sql = f"""replace into {tablename} select id, json_patch({column}, '{data}') """
            sql += f"""from {tablename}"""
        if dataid != "NULL":
            sql = " where ".join((sql, f"id={dataid}"))
        else:
            sql = self.queryString(sql, tablename, where, condition, column)
        self.query(sql)

set はこのような感じで使える。

with database.JsonDB("jsondb.db") as db:
	db.set("names", data={"name": "json", "age": 4}) # 新規で挿入
	db.set("names", data={"delete": "False", "name": "JSON"}, where={"name": "json"}) # 更新

条件を指定しない場合新規レコードに登録される手前、全件を更新するには別途 update のメソッドが必要かと思う。
けれど、思い返せど思い返せどDBに対してそんな更新をかけた記憶がさっぱり無いのでレアケースとして今のところは実装を見送っておく。

続 SQLite3 + JSON (+ Python)