「そのSQLに祝福を」SQL-Bless 開発スレ

hymkor

hymkor/sqlbless: The Command-line Database Client とは

マルチDB（Oracle/SQL Server/PostgreSQL/MySQL）に対応した、ターミナル用データベースクライアント
複数行対応 ReadLine を内蔵、巨大な SQL でもインラインで編集可能（上矢印キーで入力済みの前の行にカーソルを移動して修正できる）
SELECT の結果は、ロールアップ・ダウン可能な内蔵CSVビューアで、無理なく閲覧できる（その場で CSV 形式でファイル保存も可能）

hymkor

次のステップ

CSVエディターで、Microsoft Access みたいにレコードを編集できるようにする。
- EDIT テーブル名 [WHERE 条件]というコマンドを実行すると、ヒットした行を CSVエディターで開く。
- エディターで修正した内容は、UPDATE/INSERT/DELETE文で表現して、それを実行してよいか、適宜ユーザに確認する。
- 実行した後も ROLLBACK 可能（デフォルトで AUTO COMMIT オフでトランザクションで実行するようになっているため）

hymkor

進捗

EDIT文自体は実装済み。テストコードが実装しきれていない
更新時の日時の型は変換関数が各DBバラバラで対応しきれていない
- 読みだす時は結構雑でも大丈夫が、書き出す時は厳密にしなくてはいけない
- 普通のクライアントであれば、バインド変数を使えばよい（使うべき）
- だが、本ツールでは SQL を目視確認させた上で、それを実行するという形をとっているため、フルテキスト化した SQL と実際に実行させる SQL の差異があるのはよくないということで、どうしても一旦フルテキストな SQL にしなくてはいけない。
- DATE, TIME, DATETIME, TIMESTAMP…。これらのためのテストデータを作成する SQL を４DBそれぞれのために作らないといけない（げっそり）

hymkor

なんで、EDIT文を作るために、急に日時表現をガチでやらないといけないかというと

レコードを更新する際には、普通、レコードを一意にする主キーを把握しておかなくてはいけない(Microsoft Access でリンクテーブルを作る際にもいちいち主キーを指定しなければいけない）
マルチDB対応となると、主キーをいちいち調べるのもたいへん（プログラムもユーザ、どっちがやるにしてもたいへん）
別に主キーだけで指定しなくても、変更前の全カラムについて WHERE で指定すれば、主キー調べなくてもいいじゃん（俺天才）
そうすると、日時を含むテーブルを編集する際に、 … AND 日時のカラム = TO_DATE('変更前の日付','YYYY-MM-DD HH24:MI:SS') (ORACLEの場合)みたいに、日時を正確に一致させる等式を書かなくてはいけない

というわけ

hymkor

SQL-Bless 製作中に把握が必要になった各DBの違い（随時更新） - 標準愚痴出力

今も随時更新中

hymkor

sqlbless/README_ja.md at master · hymkor/sqlbless

日本語版の README 追加

hymkor

csviのオートパイロットに、SQL-Bless（厳密にはgo-readline-ny）のオートパイロットを連結するのに成功。ようやく自動テストが書ける。

自動テストではキーボード操作を外部から挿入してやらんといけないので、オートパイロット的な仕組みが必要なんだが、各パッケージによって微妙に使用が違うんだよな。だからアダプタ的なタイプを作らないといけなかった。

hymkor

RDBMS	日付+時刻	日付	時刻
Oracle	Date	x	x
PostgreSQL	TimeStamp	Date	Time
SQL Server	DateTime	Date	Time
MySQL	DateTime	Date	Time

hymkor

日時リテラル表現 ~~（検証中）~~

RDBMS	種類	型	<2006年1月2日15時4分5秒>を表す表現
Oracle	日時	DATE	`TO_DATE('2006-01-02 15:04:05','YYYY-MM-DD HH24:MI:SS')`
PostgreSQL	日時	TIMESTAMP	`TIMESTAMP '2006-01-02 15:04:05'` ^[1]
	日付	DATE	`DATE '2006-01-02'`
	時刻	TIME	`TIME '15:04:05'`
SQL Server	日時	DATETIME	`CONVERT(DATETIME,'2006-01-02 15:04:05',120)` ^[2]
	日付	DATE	`CONVERT(DATE,'2006-01-02',23)`
	時刻	TIME	`CONVERT(TIME,'15:04:05',108)`
MySQL	日時	DATETIME	`STR_TO_DATE('2006-01-02 15:04:05','%Y-%m-%d %H:%i:%s')` ^[3]
			`DATETIME '2006-01-02 15:04:05'` ^[4]
		TIMESTAMP	`TIMESTAMP '2006-01-02 15:04:05'`
	日付	DATE	`STR_TO_DATE('2006-01-02','%Y-%m-%d')`
			`DATE '2006-01-02'`
	時刻	TIME	`STR_TO_DATE('15:04:05',%H:%i:%s')`
			`TIME '15:04:05'`

MySQL :: MySQL 8.0 Reference Manual :: 13.2.6 Fractional Seconds in Time Values
他にも１秒未満を表現可能な型があるが、省略（やってられん）

脚注

hymkor

DB が起動しているかどうかチェック

Oracle
- oracle.exe というプロセスが一つ起動している
PostgreSQL
- postgres.exe というプロセスが6つ起動している
SQL Server
- sqlservr.exe , SQLAGENT.EXE というプロセスが起動している
MySQL
- mysqld.exe というプロセスが３つ起動している

hymkor

5/25 進捗 v0.11 への道

{Oracle,PostgreSQL,SQL Server,MySQL} × {日付時刻,日付,時刻} の組み合わせを EDIT 文で取り扱うテストコードの実装
EDIT文を使うデモンストレーションGIFアニメーション（まだ）
csviのオートパイロットに、SQL-Bless（厳密にはgo-readline-ny）のオートパイロットを連結
⇒ 一応できたものの、不具合があり、それを直した結果、汚い実装になってしまった。技術的負債化
csvi → SQL-Bless へ戻る際のキーやメッセージを検討
- cキー：Apply the changes ? [y/n] : y で反映
- q キー：反映を行わずに終了
~~コマンドラインパラメーターの改善（今のsqlbless DB種類 URL という形式は、DB種類ごといバラバラでちょっと分かりづらい可能性がある）~~ ちょっと今回は見送り
README改訂（_README.md で作成してリリース直前で差し替え）
~~セミコロン以外のスラッシュ、GOなどをターミネーターにする~~ 今回は見送り
-tsv などのオプションを指定した時でも、EDIT はちゃんと動くか？
→ SELECT,EDIT ともに列がおかしくなっていた。修正
入力しかけの状態から、ヒストリ過去→ヒストリ未来とすると、最初に入力したものが消えてしまうのはつらいから復元できるようにする（bash だとそういう動作になってた：本当は最新以外のヒストリも編集したら残るようだが、そこまでエミュレートするのは go-readline-ny のヒストリの仕様では難しい）

hymkor

Release v0.11.0 · hymkor/sqlbless

リリース！

hymkor

[FR] Support SQLite3 · Issue #1 · hymkor/sqlbless

SQLite3 サポート依頼。着手

hymkor

（１）"database/sql" ドライバーの import
（２）sqlbless だけで起動したときの説明テキスト
（３）テーブルの一覧を得る SQL を用意 (DESC 文で表示する)
（４）テーブルのカラム一覧を得る SQL を用意 (DESC テーブル名文で表示する）
→ SELECT文ではなく PRAGMA 文を使う。テーブル名を引数にするプレースホルダが使えなかったので、そこだけ直接文字列を置換する方法で実装
~~（５）日付＋日時・日付・時刻の型名と time.Time からリテラルへの変換する関数（EDIT 文で UPDATE するときに必要）~~
→ SQLite に日付・時刻型はないので不要
~~（６）（５）のテストスクリプト（共通処理は既存のスクリプトがそのまま使えるはず）~~
→ （５）が不要なので、不要

hymkor

scoop install sqlite でインストールした sqlite3 で作成したDBファイル、普通に読み書き出来たので、これで対応完了かな。

リクエストあっての対応だったけど、おかげで簡単な動作検証のためだけに Oracle インスタンスの起動をいちいちしなくてもよくなったのは嬉しい。

hymkor

実行ファイルが巨大なので、これ、パッケージサイズ大丈夫かなと思って、ためしにmake dist でパッケージを仮に作ってみようとしたら、エラー発生

Windows の 32bit版のビルドの際、シンボル未定義エラーが出てる（64bit版なら作れてる）

＃なお、パッケージサイズは許容範囲だった

hymkor

glebarez/go-sqlite のかわりに、mattn/go-sqlite3 に切り替えてみたが、32bit版の gcc がローカルマシンにインストールされていないので、こっちはこっちでビルドできねぇ…
（64bit 版だと、テストも一通り通ったんだけどねぇ）

hymkor

sqlite3 package - github.com/mattn/go-sqlite3 - Go Packages

Open a terminal for the TDM-GCC toolchain, which can be found in the Windows Start menu.

For example the TDM-GCC Toolchain can be found here

あー、TDM-GCC がないといけないのかー。ネイティブ版Luaを使ってたころお世話になったヤツだ。

hymkor

$ scoop uninstall gcc
Uninstalling 'gcc' (11.2.0).
Unlinking ~\scoop\apps\gcc\current
Removing ~\scoop\apps\gcc\current\bin from your path.

$ scoop install tdm-gcc
Couldn't find manifest for 'tdm-gcc'.

$ scoop bucket add versions
Checking repo... OK
The versions bucket was added successfully.

$ scoop install versions/tdm-gcc
Installing 'tdm-gcc' (10.3.0) [64bit] from 'versions' bucket
tdm64-gcc-10.3.0-2.exe (76.7 MB) [==============================================] 100%
Checking hash of tdm64-gcc-10.3.0-2.exe ... ok.
Extracting tdm64-gcc-10.3.0-2.exe#dl.7z ... done.
Running pre_install script...
Linking ~\scoop\apps\tdm-gcc\current => ~\scoop\apps\tdm-gcc\10.3.0
Adding ~\scoop\apps\tdm-gcc\current\bin to your path.
'tdm-gcc' (10.3.0) was installed successfully!

$ GOARCH=386 make
go fmt
set "CGO_ENABLED=1" && go build -ldflags "-s -w -X main.version=v0.11.0-9-g572344e"

$ pwsh test.ps1 tstdblst
# SQL-Bless v0.11.0-9-g572344e-windows-386 by go1.22.3
：（中略）
SQL> EXIT
--> OK

hymkor

SQLite3 用のドライバーのチョイスだけど、結局

OS	386	amd64
Linux	glebarez/go-sqlite	glebarez/go-sqlite
Windows	mattn/go-sqlite3	glebarez/go-sqlite

というチャンポンにしちゃったヨ。TDM-GCC だと、Linux 用バイナリを作れないみたいなんだ…

hymkor

リリース！

Release v0.12.0 · hymkor/sqlbless

hymkor

今後の課題は

テストコードが PowerShell(pwsh.exe)だが、Linux でもテストできるようにした方がよいかも
DB固有の細かいカラム型にも対応

ってところだろうか

hymkor

wsl の Ubuntu に PowerShell 7.4 をインストールして、SQL-Bless の SQLite3 モードのテストスクリプト（ pwsh ./test/test-sqlite3.ps1 ）を動かしてみたが、とくに問題ないから、ヨシ！
(SQLite3 、メモリーDBがあるから、テストに最適なんだよな）

hymkor

【解説】究極のターミナル用 DBクライアントSQL-Bless v0.12.0 を公開しました - 標準愚痴出力

いつものように解説記事かいた。

hymkor

あー、Edit したときの SQL が Spool で記録されていないなぁ。これはよくない。よくないぞぉぉぉ

hymkor

SQL-Bless のポリシーは、Csvi と同様「本番環境で使えるツール」なので

スプールするだけで、作業のエビデンスが全部残る（実行した日時時刻・発行したSQL・CSV化された SELECT 結果）

というのが重要なんデス

hymkor

spool 対応完了。

hymkor

o や O を入力すると、変更セルが常に新レコードの最初のセルになってしまう。カーソルを同じ列でないとまずい。
→ Csvi側の問題なので、Csvi側で対応（リビジョンはまだ上げず、SQL-Bless側で go get -u github.com/hymkor/csvi@master で取り込み）
（主に csvi の話) ヘッダー行を編集できてしまうのもまずい。ヘッダー行を READONLY にすべき。案が複数ある
- -ph (protect-headerフラグ) 。ヘッダー行を変更不能にするオプションを追加。柔軟性があるが、面倒くさい
- -h num オプションを使ったとき、自動的にヘッダー行を READONLY にする。現在、ヘッダー行はデフォルトで１行になっており、ユーザが特に意識していない時に編集不可能になっているのはよくない
  - オプション指定していない時はヘッダー１行だが、READONLY にしないようにする。
    - デフォルト値をマイナスなどにして、指定した時と区別が必要だが、ヘルプで表示されるデフォルト値が不自然
  - -h num のデフォルト値をゼロ行にしてしまう。そうすれば整合性はあう
    - README の例を書き直す必要がある

hymkor

でも、CSVエディターとしては見出しが編集できてしまっても別に問題ないか…

データベースのテーブルエディターでは変更できてしまうと誤動作の元なので、それは別途プロテクトフラグを設けるか…

hymkor

結局、-p オプションで、ヘッダ行を編集できないようにできるようにした。
パッケージとしては csvi.Config.ProtectHeader bool というフィールドを設けた。

hymkor

EDIT で編集した後、SQL でエラーが発生したときの処理を改善
- 複数修正レコードがある場合、c: Continue(残りも実行), a: Abort(全部中断) を選べるようにした。
- Abort を選んだ時、残りの SQL は実行しないが、キャンセルした旨を spool ファイル・標準エラー出力に記録するようにした。
将来的に、エディター側での入力チェックを設けてもよいかもしれない（要csviパッケージ修正：直近のリリースでは見送る）
今のところ、EDIT文で不適切な SQL を生成する可能性が結構ある。そこはユーザによる目視チェックでカバーするとして、それゆえに「全てのレコードについて、各SQLをノーチェックで実行する」という選択肢は当面設けないことにする

hymkor

「エディター側での入力チェック」実装しちゃった

hymkor

SQL-Bless で、データベースの NULL を U+2400 で表現することを検討したが、フォントの要素もあるが、ちょっと見えにくいねぇ（なお、カスタマイズ可能）

# 入力時は x や d を押下すると、NULL化できるようにした。なお、NOT NULL チェックは忘れている模様

hymkor

エディター上での入力時のNULLチェックも実装はしたが、ドライバーの仕様で効かない場合があるので「チェックできたらチェックする」になった。
（SQL実行時にエラーを吐いてもらうしかない）

hymkor

glebarez/go-sqlite: pure-Go SQLite driver for Go (SQLite embedded)

The driver has Go-based implementation of SQLite embedded in itself

Reenable windows/386 support (#169) · イシュー · cznic / sqlite · GitLab

ChatGTPによる翻訳：

私の記憶が正しければ、windows/386のサポートは一度も動作しませんでした。再度有効にして、windows/386用の適切なクロスコンパイラがインストールされたLinuxマシンでジェネレータを実行してどこで失敗するか確認することはできます。しかし、ccgo/v3はSQLite 3.41で停滞しています。詳細は#154（閉鎖済み）を参照してください。

この問題を解決する過程で、現在サポートされているすべてのターゲットをccgo/v4を使用して生成する進展があります。現時点では、windows/amd64とwindows/arm64だけが未完了です。進捗状況はmodernc.org/{libc,libz,libtcl8.6,libsqlite3}リポジトリのそれぞれのwinブランチで追跡できます。

したがって、windows/386サポートを追加する努力は、ccgo/v4ライブラリのために行うべきだと思います。この点でのご協力は大変ありがたいです。

現時点では、libtcl8.6のwindows/amd64サポートの完成に非常に近づいており、その後にlibsqlite3が依存しています。libsqlite3もwindows/amd64をサポートするようになったら、windows/386の追加に取り組むのが適切なタイミングになるでしょう。

hymkor

全DB小数秒対応完了

SQL Server が一番難産
- 日付時刻フォーマットの選択を定数（マジックナンバー）でやるところが分かりにくい
- さりげに小数点に . ではなく : を使っているフォーマットが混ざっている（ヨーロッパ向け仕様）
- SMALLDATETIME型、分までしか記録しない型だと知らなかったので、なぜ秒がクリアされるんだろうと小一時間悩んだ
MySQL、今までリテラル書くのに STR_TO_DATE 関数を通していたが、PostgreSQL 同様に 型名 'リテラル' という表現があったのに気づいたので、全部修正
Oracle、データディクショナリ―(ALL_TAB_COLUMNS)で出る TIMESTAMP型の名前が TIMESTAMP(精度) になっていて変則的（精度は別カラムに格納して、名前は　‘TIMESTAMP‘ だけにするのが普通）

一応、日付・時刻型はすべてサポートできたはず。残すは時差・TZ対応

hymkor

タイムゾーン対応

Oracle

DATE型にはなし

型名	リテラル表現
`TIMESTAMP`	`TO_TIMESTAMP_TZ('%s','YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')"`

PostgreSQL

型名	リテラル表現
`TIMESTAMPTZ`	`TIMESTAMP WITH TIME ZONE '2006-01-02 15:04:05.999999999 -07:00'`
`TIMETZ`	`TIME WITH TIME ZONE '15:04:05.999999999 -07:00'`

hymkor

MySQL

'2020-01-01 10:10:10+05:30'

Go言語のドライバーはデフォルトでは time.Time ではなく、[]byte 型へ展開してしまう^[1] → SELECT した時刻のタイムゾーン成分が +00:00 になってしまう
タイムゾーンは TIMESTAMP型にはあるが、DATETIME型にはない。^[2]

中途半端なタイムゾーン対応をすると、WHERE句のマッチングが動作しなくなってしまう。今回は見送りすべきか

参考

脚注

hymkor

timestamp with time zone型はタイムゾーン情報を持っていない #MySQL - Qiita

よい記事

OracleおよびPostgreSQLのtimestamp with time zone型が格納する値はその名前に反し、絶対時刻です。タイムゾーン情報はまったく含まれていません

しっくりくる話だ。なぜなら、世界時の12時と、同じ日の日本時の21時は表現が違うだけで同じ時刻だからイコール関係にないとおかしいはず。だから、格納する時に添えられたタイムゾーン情報から世界時に変換して格納する、取得時にクライアントのタイムゾーン情報に合わせて現地時に直すという動作は期待どおりでヨシ！

SQL Serverでは
datetime型やdatetime2型が概念時刻を格納し、datetimeoffset型が、なんと概念時刻＋タイムゾーンを格納します。

datetime,datetime2型は OK（概念時間＝タイムゾーン情報はない）。datetimeoffset型って、これも時刻だったのか。time.Duration みたいな時間差型かと勘違いしてたわ

hymkor

go-sql-driver/mysqlと日時データ型とタイムゾーン

parseTimeはtrue

locに指定するタイムゾーンとMySQLのタイムゾーンを一致させる

後者についてはこれらがずれてしまっていると、例えばTIMESTAMP型に内部的に保存されている値は通常UTCで解釈すべき値ですが、JSTとして解釈しないといけない値が入ってしまっているといった事態が発生します

これ、マジでそうだった。接続文字列に ?parseTime=true&loc=Asia%2FTokyo を指定するようにしたら、今まで WHERE で TIMESTAMP が全然イコールで一致させられなかったのが、期待どおり動くようになった。

hymkor

SQL-Bless とgo-sql-driver/mysql と日時データ型とタイムゾーン - 標準愚痴出力

hymkor

MySQL のGoドライバー、loc=Asia%2FTokyo と書かねばならないところを、loc=Local でも一応期待動作したんだけど、これ大丈夫なのかしら…

hymkor

Goでの time.Location のあれこれ #Go - Qiita

サーバーのローカルタイムゾーン情報は、以下のように明示的にタイムゾーン名を指定せずに、"Local" を引数とすると取得できる

マジか（「サーバー」という表現が気になったが、プログラムを実行してるマシンだよね）

hymkor

GoのMySQLドライバーと loc パラメーター "Local" とSQL-Bless - 標準愚痴出力

hymkor

接続文字列にフィルターをかませられるようにして、短く書けるようにした（一応、上書き可能）

sqlbless mysql "root:@/mydb?parseTime=true&loc=Asia%2FTokyo"
　↓
sqlbless mysql "root:@/mydb?parseTime=true&loc=Local"
　↓
sqlbless mysql "root:@/mydb"

hymkor

SQL-Bless の競合を調べてみた - xo/usql - 標準愚痴出力

hymkor

標準SQLとSQLの歴史！SQLの互換性と規格化の流れを解説 | アンドエンジニア

末尾のリンク集が有用

hymkor

danvergara/dblab: The database client every command line junkie deserves.

PostgreSQL, MySQL, SQLite3 and Oracle 対応の TUI クライアント。オプション体系が参考になる

hymkor

TeamSQL もいいけど、 DBeaver もいいぞ #MySQL - Qiita