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


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


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
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
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

Last-modified: 2015-05-10 (Sun) 08:55:23 (867d)
HTML convert time: 0.028 sec.