SQLite – User-Defined Functions

測試環境為 CentSO 8 (虛擬機)

SQLite 無法像是 MySQL 或是 MariaDB 使用者自訂函數 ( User-Defined Functions ) ,不過我們可以透過 SQLite 的 extensions – https://github.com/nalgeon/sqlean 來完成.

首先在 https://github.com/nalgeon/sqlean/releases 下載 SQLite extensions 的 so 檔案.

[root@localhost ~]# wget https://github.com/nalgeon/sqlean/releases/download/0.19.1/sqlean-linux-x86.zip
--2023-02-14 16:48:45--  https://github.com/nalgeon/sqlean/releases/download/0.19.1/sqlean-linux-x86.zip
Resolving github.com (github.com)... 20.27.177.113
Connecting to github.com (github.com)|20.27.177.113|:443... connected.
HTTP request sent, awaiting response... 302 Found
Saving to: ‘sqlean-linux-x86.zip’

sqlean-linux-x86.zip          100%[=================================================>] 276.70K   244KB/s    in 1.1s

2023-02-14 16:48:48 (244 KB/s) - ‘sqlean-linux-x86.zip’ saved [283343/283343]

[root@localhost ~]# unzip sqlean-linux-x86.zip
  inflating: crypto.so
  inflating: define.so
  inflating: fileio.so
  inflating: fuzzy.so
  inflating: ipaddr.so
  inflating: json1.so
  inflating: math.so
  inflating: regexp.so
  inflating: stats.so
  inflating: text.so
  inflating: unicode.so
  inflating: uuid.so
  inflating: vsv.so

define.so 就提供了 User-Defined Functions , 使用說明 https://github.com/nalgeon/sqlean/blob/main/docs/define.md

select define(NAME, BODY)

範例為自行寫從 1+2 … n 的總合.

select define('sumn', ':n * (:n + 1) / 2');

select sumn(5);
15

傳入參數方式有 anonymous, positional, or named

• anonymous

  select define('subxy1', '? - ?');
  select subxy1(5, 1);
  4
  

• positional

  select define('subxy2', '?1 - ?2');
  select subxy2(5, 1);
  4
  

  select define('subxy3', '?2 - ?1');
  select subxy3(5, 1);
  -4
  

• named

  select define('subxy4', ':x - :y');
  select subxy4(5, 1);
  4
  

檢視已自訂函數.

select name, body from sqlean_define;
sumn|:n * (:n + 1) / 2

刪除自訂函數.

select undefine('sumn');
select name, body from sqlean_define;

Python

如果是要透過 Python 去使用,方式如下.

import sqlite3

con = sqlite3.connect("database.db")
con.enable_load_extension(True)
con.load_extension("./define")

cur = con.cursor()

cur.execute("select define('sumn', ':n * (:n + 1) / 2')")
data1=cur.execute("select sumn(5)")

執行結果.

list(data1)
[(15,)]

不知道為什麼在 close 時會出現錯誤.

 con.close()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: unable to close due to unfinalized statements or unfinished backups