SQLite 學習筆記之一 - 基本用法

一、支援的 SQL 語法

aggregate functions ALTER TABLE ANALYZE ATTACH DATABASE BEGIN TRANSACTION comment COMMIT TRANSACTION core functions CREATE INDEX CREATE TABLE CREATE TRIGGER CREATE VIEW

CREATE VIRTUAL TABLE date and time functions DELETE DETACH DATABASE DROP INDEX DROP TABLE DROP TRIGGER DROP VIEW END TRANSACTION EXPLAIN expression INDEXED BY

INSERT keywords ON CONFLICT clause PRAGMA REINDEX RELEASE SAVEPOINT REPLACE ROLLBACK TRANSACTION SAVEPOINT SELECT UPDATE VACUUM

二、不支援的 SQL 語法

1. OUTER JOIN
• 支援：LEFT OUTER JOIN。
• 不支援：RIGHT OUTER JOIN、FULL OUTER JOIN。
2. ALTER TABLE
• 支援：RENAME TABLE、ADD COLUMN。但內部其實是以「先建立新TABLE、然後再刪除舊 TABLE」的方式來處理。
• 不支援：DROP COLUMN、ALTER COLUMN、ADD CONSTRAINT。
3. TRIGGER
• 支援：FOR EACH ROW。
• 不支援：FOR EACH STATEMENT。
4. VIEW
• 在 SQLite 裡的 VIEW 都是唯讀的，所以不能對 VIEW 進行 DELETE、INSERT、UPDATE 操作，但可藉由 TRIGGER 的方式來模擬。
5. GRANT 和 REVOKE
• 不支援。因為 SQLite 讀寫磁碟上的檔案，所以其讀寫權限完全依據底層 OS 的檔案存取權限機制而定。另外，GRANT 和 REVOKE 對嵌入式的資料庫引擎也是無意義的。
6. TRUNCATE
• 不支援。替代方案是使用 DELETE FROM ${TABLE}

三、PRAGMA 指令

此指令是 SQLite 的 SQL 擴充（extension），是 SQLite 獨有的指令。主要用於修改 SQLite 內部運作方式或查詢內部資料。使用方式與一般的 SELECT、INSERT 指令一樣。但也有幾點重要的不同：

1. 在未來的新版本中，某些 PRAGMA 指令可能會被取消，也可能會加入新的指令。因此，無法保證與舊版的相容性。
2. 若執行 SQLite 不認識的 PRAGMA 命令，SQLite 將只會忽略、但不會回報任何錯誤訊息。
3. 有些 PRAGMA 命令只會在 SQL 的編譯階段起作用而已，而不是執行階段。
4. 此指令是 SQLite 特有的，所以極有可能不相容於其他資料庫引擎

application_id application_id auto_vacuum automatic_index busy_timeout cache_size case_sensitive_like checkpoint_fullfsync collation_list compile_options count_changes¹ data_store_directory¹ database_list default_cache_size¹ empty_result_callbacks¹ encoding foreign_key_check foreign_key_list foreign_keys freelist_count

full_column_names¹ fullfsync ignore_check_constraints incremental_vacuum index_info index_list integrity_check journal_mode journal_size_limit legacy_file_format locking_mode max_page_count mmap_size page_count page_size parser_trace² quick_check read_uncommitted recursive_triggers

reverse_unordered_selects schema_version secure_delete short_column_names¹ shrink_memory synchronous table_info temp_store temp_store_directory¹ user_version vdbe_addoptrace² vdbe_debug² vdbe_listing² vdbe_trace² wal_autocheckpoint wal_checkpoint writable_schema

 四、欄位類型（Storage Class / Data Type）

類型	說明
TEXT	文字（或字串），支援的編碼：UTF-8、UTF-16（big 或 little endian），最大長度限制為 1,000,000,000 bytes（編譯時期設定）。
INTEGER	有號整數，包含正整數與負整數，SQLite 根據數值的大小決定使用 1, 2, 3, 4, 6, 或 8 bytes 的儲存空間。支援最大的整數範圍（8 bytes）： {-9223372036854775808, -1, 0, 1, 9223372036854775807}。
REAL	實數，即帶小數的數值（如：12.38、-0.8625），SQLite 使用 8 byte 浮點數來儲存真數。
BLOB	其實任何類型的資料都可以算是 BLOB。最大長度限制為 1,000,000,000 bytes（編譯時期設定）。
NULL	未知值，SQLite 對於 NULL 具備完整的支援。

1. 相對於一般的資料庫系統，SQLite 是「動態型別」的（dynamic type） ，故所有欄位均可儲存任何型別的資料，所以在創建 TABLE 時，也不需要指定欄位類型。除了「 INTEGER PRIMARY KEY」類型的欄位之外，它會在使用時自動判斷、轉換。
2. 若某欄位被宣告為「 INTEGER PRIMARY KEY」，則該欄位會成為 AUTOINCREMENT 欄位。
3. SQLite 沒有 Boolean 類型，但 Boolean 值可以儲存為 INTEGER（0: false / 1 : true）。
4. SQLite 沒有 Date 或 Time 類型，但是內建的 Date / Time 函式仍可處理 TEXT、REAL、INTEGER 類型的欄位：date()、time()、datetime()、julianday()、strftime()。

五、ROWID

1. SQLite 中的每張表格裡的每一列都會有一個「ROWID」欄位，該欄位的值是 64 位元的有號整數（singed integer）。在同一張表格裡的每列 ROWID 值都是唯一的。
2. 您可用 ROWID、_ROWID_ 或 OID 來存取這些欄位，但如果您在建立表格時使用了這些欄位名稱，那您以後就無法參照到 SQLite 內部真正的 ROWID 。
3. 如果表格內有一個「INTEGER PRIMARY KEY」類型的欄位，那這個欄位就會變成 ROWID 的別稱（alias），也就是說日後您可以用這四種不同的名稱去存取 ROWID。
4. 當要插入新資料列到表格時，您可以不指定 ROWID 欄位，因為 SQLite 會自動配發一個新的值給  ROWID 您也可以明確地指定 ROWID  欄位的值：
   
      CREATE TABLE test1(a INT, b TEXT);
      INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello');
5. 如果 INSERT 指令裡未指定 ROWID 的值、或 ROWID 的值是 NULL，那 SQLite  便會自動地產生一個合適的 ROWID 。作法通常是取表格內最大的 ROWID 值再加 1，即為新的 ROWID。
6. 如果是剛開始的空表格，那 ROWID 就會是 1。如果 ROWID 已經等於最大整數值 (9223372036854775807) 了，那 SQLite 就會隨機產生一組正整數值，直到找到之前沒有用過的。如果在嘗試幾次後，仍找不到未使用過的 ROWID，那 SQLite 就會回報 SQLITE_FULL 錯誤。除非明確地指定負數 ROWID ，否則自動產生的 ROWID 一定都是大於零的整數。
7. ROWID 的演算法如上所述，也就是每次都遞增 1，但前提是表格中沒有用到 ROWID 的最大值。如果表格中已經使用到  ROWID 最大值，那 SQLite 就會重複使用之前用過的 ROWID （從被刪除掉的資料列），而且新的 ROWID 將不再符合由小到大的順序。

六、REINDEX

1. REINDEX 指令用來刪除並重建索引。若改變了 collation sequence（整理序列），必須再重新執行此指令。
2. 若 REINDEX 後面沒有給定任何參數，則所有連接（attached）的 DB 的索引均會被重建。
3. 若 REINDEX 後面是一個 collation sequence 名稱，則在目前所有連接的 DB 之中、是使用該 collation sequence 的索引均會被重建。
4. 若 REINDEX 後面指定了某 DB 的某表格，則該表格相關的索引均會被重建。若指定了某 DB 的某索引，則只有該索引會被重建。
5. SQLite  內建支援三種 collation sequences： BINARY、NOCASE、以及 RTRIM。預設值是 BINARY。collation sequence 是 SQLite 在比較兩個字串是否相等、何者較大的一組規則定義。

七、VACUUM

1. VACUMM 指令可用來重建整個 DB 檔案。通常基於下列理由，您可能需要執行此指令：
• 除非 SQLite 是執行於「auto_vacuum=FULL」的模式，否則從 DB 檔案中刪除大量資料後，不僅 DB 檔案大小不會改變、還會留下許多冗餘空間或閒置的 DB 分頁。這將使 DB 檔案變得虛胖，並超過實際需要的空間。執行這個指令可讓 OS 回收此類空間、並減少 DB 檔案的大小。
• 經常性地執行新增、修改以及刪除操作以後，將使 DB 檔案變得碎片化（fragmented），這意味著 表格或索引的資料散落於 DB 檔案內部各處，造成效能低落。執行本指令可使表格和索引都盡量儲存於連續性空間。在某些狀況下，VACUUM 甚至也會減少部分填滿的分頁（partially filled page）數量，進而減少 DB 檔案大小。
• 若想改變 DB 的 auto-vacuum 模式，必須先執行「PRAGMA auto_vacuum=xxx」、然後再執行「VACUUM」指令。
2. VACUUM 指令只會作用在「main」DB 上，對其他連接（attached）的 DB 檔案並無效。
3. VACUUM 指令實際上是先將原 DB 檔案內容複製到另外一個暫存檔，然後再用此暫存檔覆寫掉原檔案。當覆寫原檔案時，就像一般的 DB 交易一樣，也會使用到所謂的「回滾日誌檔」（rollback journal）或「預寫日誌檔」（write-ahead log）。這也表示：當執行 VACUUM 時，至少需要原檔案兩倍的磁碟空間。
4. 如果在一張表格內沒有將某欄位類型明確地設定為「INTEGER PRIMARY KEY」，則 VACUUM 可能會改變 ROWID 的值。
5. 如果目前有交易或 SQL 正在執行中、則 VACUUM 將會失敗。
6. 自 3.1 版以後，SQLite 提供了「auto-vacuum」模式。在此模式中，只要資料被刪除，SQLite 就會釋放掉未使用到的 DB 分頁給 OS，並縮減檔案大小。但使用此模式也可能會使 DB 檔案內部變得更為碎裂（不連續）。因為 auto-vacuum 並不像 VACUUM 一樣會將部分填滿的分頁緊湊地排列在一起。

八、Auto Vacuum

• PRAGMA auto_vacuum;
• PRAGMA auto_vacuum = 0 | NONE | 1 | FULL | 2 | INCREMENTAL;
• 本指令可用來查詢或設定 DB 中的 auto-vacuum 狀態，詳細說明請參考「SQLite官網說明」。

模式	說明
0 或 none	在此模式下，當資料被刪除時，DB 檔案大小不會改變。而這些未使用的 DB 分頁會被加入 freelist 之中，以供後續的 INSERT 使用。在此模式下，可使用「VACUUM 」指令來重建整個 DB 檔案，以使 OS 可回收這些未使用的磁碟空間（※ 注意：這是 SQLite 的預設值）。
1 或 full	在此模式下，當資料被刪除時， freelist 分頁會被先移到 DB 檔案末端，然後在每一次交易 commit 的時候，DB 檔案會被截短（truncate）以移除 freelist 分頁（所以在此模式下 freelist 中應該不會有任何的分頁）。請注意 auto vacuum 只會從檔案中移除 freelist 分頁，並不會像 VACUUM 指令會進行碎片清理（defragment）與 DB 分頁重整。所以 auto-vacuum 只是在檔案之中進行分頁的搬移而已，所以實際上可能會使碎片化（defragment）的狀況更為嚴重！此外，為了要讓原來引用這些分頁的參照者還能追蹤到更新後的參照，SQLite 還必須先儲存一些額外的資訊，所以這個模式也會增加  SQLite 一些處理時間。
2 或 incremental	在此模式下，要用來執行 auto-vacuum 的額外資訊會先儲存在 DB 檔案之中，但是 auto-vacuum 的動作並不會像「full」模式在 commit 時就自動執行，必須事後手動執行「PRAGMA incremental_vacuum(N) 」指令。

1. 除非在編譯 SQLite 時，有設定「SQLITE_DEFAULT_AUTOVACUUM」選項，否則 auto-vacuum 預設是關閉的（亦即 0 或 none）。
2. 任一 DB 連線均可在「full」和「incremental」模式之間自由切換。
3. 若要從「none」切換為「full」或「incremental」，或者從「full」或「incremental」切換回「none」，必須先執行「PRAGMA auto_vacuum=full」或「PRAGMA auto_vacuum=incremental」、然後再執行「VACUMM」指令才行。
4. 如果想查詢目前的 auto-vacuum 模式，可執行「PRAGMA auto_vacuum」指令。