I built in some general sql functions to TkSQLite ver0.4.
Here is the list of the functions. You can blild in these functions to your database freely. NOTE: This is under developed, so these may be changed.

How to build in the functions to your db

package require sqlite3
source sqlitefunc.tcl
sqlite db :memory:
Sqlite::func::install db

then

% db eval {SELECT MD5('md5 test')}
2E5F9458BCD27E3C2B5908AF0B91551A

Download

http://reddog.s35.xrea.com/software/sqlitefunc0.4.zip http://reddog.s35.xrea.com/software/sqlitefunc0.5.zip

String Format Functions

ascii(char)return ascii code
char(code)return char from ascii code
concat(arg ?, arg ...?)join args
concat_ws(sep, arg ?, arg?)join args with sep
convert(str, from, to)encoding converter. from and to must be tcl's encoding name.
elt(n, arg ?, arg ...?)MySQL's elt.
hex(n)return hex value.
initcap(str)Oracle's initcap.
insert(s, pos, len, ns)
instr(str, sstr, ?st ?n??)search sstr in str.
length(str)SQLite Built-in
locate(sstr, str, pos)return first sstr position in str. start from pos.
lower(str)SQLite Built-in
lpad(str, len ?, pad?)
ltrim(str ?, chars?)trimleft chars(or space) from str.
mid(str, pos, len)
position(sstr, IN, str)return first sstr position in str.
repeat(str, n)
replace(str, from, to)replace from to to.
reverse(str)reverse string.
rpad(str, n ?, pad?)
rtrim(str ?, chars?)trimright chars(or space) from str.
space(n)return space.
to_char(num, fmt)Support only B,FM,S,G,D,",",9,0. Only number format. If you want to format datetime, use some sqlite built-in functions like strftime or datetime.
translate(str, from, to)PostgreSQL's translate.
trim(str ?, chars?)This is not SQL99 trim. trim chars(or space) from str.

Math Functions

acos                    exp                 radians                    
asin                    floor               rand                       
atan                    greatest            random(SQLite Built-in)    
atan2                   least               round(SQLite Built-in)     
avg(SQLite Built-in)    log                 sign                       
ceil                    log10               sin                        
cos                     mod                 sqrt                       
cot                     pi                  tan                        
degrees                 pow                 trunc                      

Date Time

now()return localtime. same as datetime('now', 'localtime')
juliandaySQLite Built-in
datetimeSQLite Built-in
dateSQLite Built-in
strftimeSQLite Built-in

Other Functions

minSQLite Built-in
maxSQLite Built-in
coalesceSQLite Built-in
nullifSQLite Built-in
ifnullSQLite Built-in
last_insert_rowidSQLite Built-in
quoteSQLite Built-in
regexp
regsub
userreturn user name

Crypt, Hash, etc

NOTE: requires Tcllib and Trf

md5(data ?, binary?)MD5
md5_hmac(key, data ?, binary?)HMAC-MD5
md5_crypt(pass, salt ?, binary?)BSD compatible MD5 crypt
apr_crypt(pass, salt ?, binary?)Apache compatible MD5 crypt
sha1(data ?, binary?)SHA1
sha1_hmac(key, data ?, binary?)HMAC-SHA1
aes_encrypt(key, data ?, binary?)ASE encryption
aes_decrypt(key, data ?, binary?)ASE decryption
blowfish_encrypt(key, data ?, binary?)Blowfish encryption
blowfish_decrypt(key, data ?, binary?)Blowfish decryption
des_encrypt(key, data ?, binary?)DES encryption
des_decrypt(key, data ?, binary?)DES decryption
base64_encode(data ?, binary?)Base64 encode
base64_decode(data ?, binary?)Base64 decode
compress(data ?, binary?)compress data by zip
decompress(data ?, binary?)decompress data by zip
uuid()UUID
exec(string)execute command and return result

the binary option must set 1 for the BLOB data. default is 0. if binary is omitted (binary = 0), data is handled as text. key is handled as text even if binary is 1.

File IO

write_file(path, data ?,binary?)Base64 decode
read_file(path ?,binary?)Base64 encode

path is file path.
the binary option must set 1 for the BLOB data. default is 0. if binary is omitted (binary = 0), data is handled as text.

One Line Comment


CategoryTclTk TkSQLite CategoryEnglish


|New|Edit|Freeze|Diff|Backup|Attach|Copy|Rename|
Last-modified: 2015-05-10 (Sun) 08:55:23 (924d)
HTML convert time: 0.011 sec.