compression function

(1) By anonymous on 2026-04-24 07:32:58 [link] [source]

Hello

It is possible in sqlite to save arbitrary data as blob. I also have read about compressed sqlite db in https://www.sqlite.org/zipvfs/doc/trunk/www/readme.wiki And I know I could compress/uncompress data on the user side.

What about a compress/uncompress sqlite simple function? The code to do these tasks is already in sqlite. It would be nice if we could it use in sql like:

create table mytable (integer id, blob data); insert into mytable values(1,compress(:1)); select from mytable uncompress(data) where id=1;

Regards Rene

(2) By Stephan Beal (stephan) on 2026-04-24 07:47:12 in reply to 1 [link] [source]

What about a compress/uncompress sqlite simple function?

src:/finfo?name=ext/misc/compress.c

It's been said that one should "never say never", but i feel pretty confident that that will never be part of the core library for the simple reason that it requires a third-party library dependency.

(3) By anonymous on 2026-04-24 08:49:04 in reply to 2 [link] [source]

Mmh, in tcl you have "zlib compress" and "zlib uncompress". Is these functionality not in sqlite?

(4.1) By Stephan Beal (stephan) on 2026-04-24 08:58:28 edited from 4.0 in reply to 3 [link] [source]

Is these functionality not in sqlite?

Not in the core library, no. The core library has no third-party library dependencies beyond necessary system-level ones, e.g. pthreads and Windows-specific APIs.

Edit:

Mmh, in tcl you have "zlib compress" and "zlib uncompress".

The SQLite Tcl pieces are an optional extension, not part of the core library.

(5) By anonymous on 2026-04-24 09:01:40 in reply to 4.1 [link] [source]

Thank you for the explanation. At least I could define a new function using "zlib ..".

Regards Rene

(6) By cj (sqlitening) on 2026-04-24 17:24:18 in reply to 5 [source]

> At least I could define a new function using "zlib ..".

Definitely
SQLitening uses zlib and a "C" is added to second parameter to compress in the binding function

'Do not compress integer columns as they become blob
'C = compress
'U = uncompress
--------------------------------------------------------------------------------------

 slexe     "CREATE TABLE t1(c1 TEXT, c2 INTEGER, c3 TEXT)"

 sbind =   slbuildbinddat("1" ,"TC")   '// text compress   column 1
 sbind+=   slbuildbinddat("2" ,"I")    '// integer         column 2
 sbind+=   slbuildbinddat("3" ,"TC")   '// text compress   column 3

 slexebind "INSERT INTO t1(c1,c2,c3) VALUES(?,?,?)",sbind

 slselary  "SELECT * FROM t1",rs(),"Q9 U1,3" '// (Q9 = tab delimit 1-dimension array) (U1,3 = uncompress columns 1 and 3)
 MSGBOX    JOIN$(rs(),$CRLF)                 '// result  1 2 3

(7) By cj (sqlitening) on 2026-04-25 04:00:34 in reply to 6 [link] [source]

"ORDER BY" clause does not return data in uncompressed order.  Seek professional routines.

(8) By anonymous on 2026-04-27 06:32:50 in reply to 6 [link] [source]

Nice trick with appending the desired action. I still use plain sqlite and tcl. The following lines work with my example from above:

$db function compress {::zlib compress} $db function uncompress {::zlib decompress}

Regards rene