Tag: sqlite

使用 SQLite 實作出 API 相容 Amazon SQS 的 SmoothMQ

在「Show HN: Drop-in SQS replacement based on SQLite (github.com/poundifdef)」這邊看到的，就如同標題所提到的，是個 Amazon SQS 的相容 API 實作，專案在 GitHub 上的「SmoothMQ」這邊。

底層是 SQLite，實作語言是用 Go，然後有 web interface 可以觀察與管理。

不過 Amazon SQS 的 API 層有特別優秀嗎...？而且大多數的情況都不是直接呼叫，應該是透過 message queue sdk 處理，像是作者自己寫的 sample code 用 Celery？如果是 Celery 的話後面應該有很多 backend 可以抽換...

也許在測試的角度用的到？

SQLite 官方提供的網頁版 playground

Hacker News 上看到「Sqlite3 Utility in the Browser (sqlite.org)」這個，看了一下是官方提供的 playground：「SQLite3 Fiddle」。

從 https://sqlite.org/fiddle/fiddle.js 這邊可以看到 2022 年就有的東西，在 Internet Archive 上也可以看到也是差不多時間被記錄下來的：「Saved 21 times between August 12, 2022 and January 24, 2024.」。

看起來是用 WebAssembly 包起來的，不過如果是自己的機器，本機跑 sqlite3 好像會方便一些...

讓 git diff 可以直接顯示 SQLite3 裡面的差異

從「Tracking SQLite Database Changes in Git」這邊看到的，然後作者 Simon Willison 又是從 Lobste.rs 的「Tracking SQLite Database Changes in Git databases」這邊看到的，而原文在「Tracking SQLite Database Changes in Git」。

一般 SQLite 檔案的 diff 會出現這樣：

diff --git a/a.sqlite3 b/a.sqlite3
index a4a8cfa..714f34a 100644
Binary files a/a.sqlite3 and b/a.sqlite3 differ

作者想要透過 sqlite3 的指令加工，讓 git-diff 的演算法可以展現出像是這樣的指令：

diff --git a/a.sqlite3 b/a.sqlite3
index a4a8cfa..714f34a 100644
--- a/a.sqlite3
+++ b/a.sqlite3
@@ -3,4 +3,5 @@ BEGIN TRANSACTION;
 CREATE TABLE tbl (id SERIAL, username TEXT, password TEXT, created_at INT, updated_at INT);
 INSERT INTO tbl VALUES(NULL,'gslin','$1$yRgoNPev$nOc5Hpr5JZAYISbHjp7LA/',0,0);
 INSERT INTO tbl VALUES(NULL,'dk','$1$yRgoNPev$nOc5Hpr5JZAYISbHjp7LA/',0,0);
+INSERT INTO tbl VALUES(NULL,'darkkiller','$1$yRgoNPev$nOc5Hpr5JZAYISbHjp7LA/',0,0);
 COMMIT;

方法是先設定 diff 工具部分，這個可以放到 ~/.gitconfig 裡面：

[diff "sqlite3"]
    binary = true
    textconv = "echo '.dbconfig trusted_schema no\n.dump' | sqlite3"

這邊跟原文不太一樣，主要是參考了 SQLite 官方網站上「Defense Against The Dark Arts」這邊的「Untrusted SQLite Database Files」部分，增加了 .dbconfig trusted_schema no 的設定加減擋一下...

然後我依照「Where should I place my global 'gitattributes' file?」這邊的問題與解答，把 attributes 設定放在 ~/.config/git/attributes 裡面：

*.sqlite diff=sqlite3
*.sqlite3 diff=sqlite3

這樣就會對這個使用者所有的 git repository 都生效。

作者原文提到的方法也可以用，不過主要是在單一 repository 裡面設定，針對 *.db 這類只有在 repository 內才會知道規則的告訴 git 要怎麼認。

SQLite 官方自己搞的 Cloud Backed SQLite

SQLite 自己搞了一套使用雲端空間為儲存空間的技術：「Cloud Backed SQLite」，對應的 Hacker News 討論可以看「Cloud Backed SQLite (sqlite.org)」這邊。

他說目前支援 Azure Blob Storage 與 Google Cloud Storage，這點比較有趣，沒有提到 Amazon S3：

The system currently supports Azure Blob Storage and Google Cloud Storage. It also features an API that may be used to implement support to other cloud storage systems.

跟之前的 sql.js 專案不太一樣，sql.js 的作法是用 HTTP range 存取現有的 SQLite 資料庫檔案，而這次的這個專案則是改變底層架構，去配合雲端環境的特點。

雲端的 storage 因為每個 access 都會有很高的 latency (相比於本地的空間)，所以要避免太多 random access，儘量以 sequential access 為主，這個特性像是以前在處理傳統磁頭硬碟時的技巧。

另外一個特點是雲端空間有多檔案的概念，所以也可以利用這個方式設計資料結構。

還蠻有趣的計畫，而且是官方搞的...

SQLite 的 HC-tree 計畫

Hacker News 首頁上看到的新計畫：「HC-tree is an experimental high-concurrency database back end for SQLite (sqlite.org)」，SQLite 弄了一個實驗性質的 backend，叫做 HC-tree：

The HC-tree (hctree) project is an attempt to develop a new database backend that improves upon regular SQLite as follows:

他列了幾個重點，其中「Improved concurrency」這點題到了可以讓多個 writer 同時寫入運作，這點算是 SQLite 很大的改變，目前希望可以做到在 single-threaded 情況下不輸現有的 SQLite：

An implicit goal is that hctree must be as fast or faster than stock SQLite for all single-threaded cases. There is no point in running dozens of concurrent writers if each of them is an order of magnitude slower than a single writer writing to a legacy database.

另外一方面，這算是 SQLite 真正要面對資料庫的 isolation 的問題了，比起現在的版本，同時間從只有一個 writer 的架構要變成支援多個 writer 的架構，所以在「Concurrency Model」這邊也帶了一下他預期可以做到的事情。

然後這邊可以看到在解釋裡面有提到 table 與 index 還是 b-tree，這樣應該可以猜測 hctree 的實做方式應該還是在市場上已經很成熟的 MVCC 那套方法：

If no other client has modified any b-tree (table or index) entry or range that the transaction being committed accessed by a range or stabbing query, then the transaction is valid.

另外一個蠻大的改變是「Support for replication」，現有的 SQLite 可以透過 extension 的方式加掛支援 replication 功能，現在則是讓底層的 backend 直接支援。

底層支援新的 backend 以後看起來會有不少變化可以玩，第一個想到的當然是變成 server 類型的服務，也就是像 MySQL 或是 PostgreSQL 這樣的方式，另外一種方向是包裝成 distributed database，讓應用程式可以簡單跨機器使用。

目前還不知道會往什麼方向走就是了，也有可能 SQLite 這邊只實做 backend，上面讓大家發揮...

WordPress 打算要支援 SQLite 作為後端資料庫

目前 WordPress 只有支援 MySQL，而昨天在 Hacker News 上看到 WordPress 有打算要支援 SQLite 作為後端資料庫的消息：「WordPress testing official SQLite Support (github.com/wordpress)」，原文在 GitHub 上：「Implement new experimental SQLite integration module」。

理論上對使用者會更方便，但對 extension 開發者會麻煩一些 (或是直接標不支援？)，尤其是用到 MySQL 特有的語法就要注意了。

實質上 PHP + MySQL hosting 其實蠻常見的，這個作法有多少幫助就不知道了。

但突然想到，如果做一個 read-only 版本的 WordPress 站台，然後把 SQLite 的讀取部份改用 sql.js 之類的計畫，再把一堆 server side rendering 的部份變成 client side rendering，好像有機會可以整包直接上 GitHub Pages 之類的服務？雖然這樣有點拖褲子放屁...

SQLite 官方自己下來搞 WASM/JS 計畫

先前在「把 SQLite 的 VFS 掛上 WebTorrent 的 PoC Demo」有提過 sql.js 這個專案，把 SQLite 移植到網頁上，這些都算是非官方的社群弄出來的專案。

現在官方直接跳下來玩，宣佈自己也要搞 WASM/JS 了：「sqlite3 wasm docs: About the sqlite3 WASM/JS Subproject」。

Folks have been building sqlite3 for the web since as far back as 2012 but this subproject is the first effort "officially" associated with the SQLite project, created with the goal of making WASM builds of the library first-class members of the family of supported SQLite deliverables.

但不太確定 D. Richard Hipp 的想法，官方支援 WASM/JS 的目的會是什麼？放給社群繼續發展有什麼問題嗎...

SQLite 的 zstd extension

看到「sqlite-zstd」這個實驗性質的專案，可以針對 SQLite 的 row-level 層壓縮：

Extension for sqlite that provides transparent dictionary-based row-level compression for sqlite. This basically allows you to compress entries in a sqlite database almost as well as if you were compressing the whole DB file, but while retaining random access.

看起來在空間上有很不錯的成果：

作者在他自己的 blog 上面有給了一篇比較完整的說明，除了空間上的優勢以外，還包含了效能上的分析：「sqlite-zstd: Transparent dictionary-based row-level compression for SQLite」。

在文章裡面測出來的數據看起來在效能上就不一定有比較好的結果，本來的 SQLite 就已經處理的還不錯了。

但看起來是個還蠻有趣的東西，舉例來說，如果可以透過 WebAssembly 編譯，再配合之前的「把 SQLite 的 VFS 掛上 WebTorrent 的 PoC Demo」，可以省下不少傳輸的量...

可以看看後續會不會真的有人這樣幹 XD

直接用 SQLite 查詢 Excel 檔案的 XLite (還有 dsq)

在 Hacker News 上看到「Xlite: Query Excel and Open Document spreadsheets as SQLite virtual tables (github.com/x2bool)」這個專案，就如同說明，是一個支援讀取 Excel 檔案的 SQLite extension，原網站在 x2bool/xlite 這邊。

依照說明支援舊的 .xls 與新的 .xlsx 的格式，但不知道公式運算支援到什麼程度...

先 load extension：

sqlite3 # will open SQLite CLI
> .load libxlite

接著是建立 virtual table：

CREATE VIRTUAL TABLE class_data USING xlite(
    FILENAME './path/to/example.xlsx',
    WORKSHEET 'Class Data',
    RANGE 'A2:F'
);

接下來就可以搞事了：

SELECT COUNT(*), D FROM class_data GROUP BY D ORDER BY COUNT(*);

看起來是用 Rust + C 寫的，然後作者有提醒這是寫興趣的專案：

This project is experimental, use at your own risk. The project is developed in my free time as a way to learn Rust and database systems.

反倒是 Hacker News 討論串裡面提到了 multiprocessio/dsq 這個專案，看起來發展的比較久，支援度也比較完整了：

Commandline tool for running SQL queries against JSON, CSV, Excel, Parquet, and more

不過就不是綁 SQLite 了 (雖然還是有關)，從範例可以看到他是獨立的程式：

$ dsq testdata.json "SELECT * FROM {} WHERE x > 10"

Under the hood dsq uses DataStation as a library and under that hood DataStation uses SQLite to power these kinds of SQL queries on arbitrary (structured) data.

如果是真的要用的話，這套看起來應該會好一些...

在 Shell 下一行用 SQLite 查詢 CSV 內的資料

從 Simon Willison 這邊看到 command line 下用 SQLite 的技巧：「One-liner for running queries against CSV files with SQLite」。

範例指令是這樣 (整理了一下排版)：

sqlite3 :memory: \
    -cmd '.import -csv taxi.csv taxi' \
    'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'

可以看出來這個方式是將 csv 檔先讀到 in-memory database (:memory:)，再用 SQLite 下指令處理，另外也可以自己變化，應該可以透過 /dev/stdin 這樣的方式讀 pipe 的東西。

拿來簡單跑一些東西應該還不賴？