- The added line is THIS COLOR.
- The deleted line is THIS COLOR.
I built in some general sql functions to [[TkSQLite]] ver0.3.~
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 [#eaf68944]
package require sqlite3
source sqlitefunc.tcl
sqlite db :memory:
Sqlite::func::install db
then
% db eval {SELECT MD5('md5 test')}
2E5F9458BCD27E3C2B5908AF0B91551A
*** Download [#n5ef0eb9]
http://reddog.s35.xrea.com/software/sqlitefunc.zip
http://reddog.s35.xrea.com/software/sqlitefunc0.4.zip
http://reddog.s35.xrea.com/software/sqlitefunc0.5.zip
***String Format Functions [#fa4b0f08]
|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. |
|left(str, n) | |
|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. |
|right(str, n) | |
|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 [#o12e785e]
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 [#fe31d122]
|now() |return localtime. same as datetime('now', 'localtime') |
|julianday |SQLite Built-in |
|datetime |SQLite Built-in |
|date |SQLite Built-in |
|strftime |SQLite Built-in |
*** Other Functions [#y6a88a8e]
|min |SQLite Built-in |
|max |SQLite Built-in |
|coalesce |SQLite Built-in |
|nullif |SQLite Built-in |
|ifnull |SQLite Built-in |
|last_insert_rowid |SQLite Built-in |
|quote |SQLite Built-in |
|regexp | |
|regsub | |
|user |return user name |
*** Crypt, Hash, etc [#ed5062cd]
NOTE: requires Tcllib
|md5(data) |MD5 |
|md5_hmac(key, data) |HMAC-MD5 |
|md5_crypt(pass, salt) |BSD compatible MD5 crypt |
|apr_crypt(pass, salt) |Apache compatible MD5 crypt |
|sha1(data) |SHA1 |
|sha1_hmac(key, data) |HMAC-SHA1 |
|aes_encrypt(key, data) |ASE encryption |
|aes_decrypt(key, data) |ASE decryption |
|blowfish_encrypt(key, data) |Blowfish encryption |
|blowfish_decrypt(key, data) |Blowfish decryption |
|des_encrypt(key, data) |DES encryption |
|des_decrypt(key, data) |DES decryption |
|base64_encode(data) |Base64 encode |
|base64_decode(data) |Base64 decode |
|uuid() |UUID |
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 [#m7e679f6]
|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 [#h4c6dd90]
-select MD5('ala') -- &new{2005-12-16 08:16:51};
-broken link: http://reddog.s35.xrea.com/software/sqlitefunc.zip -- Giorgio &new{2006-03-16 22:29:29};
-I fixed it. thanks. -- reddog &new{2006-03-17 01:23:19};
-select MD5('ala') -- 2005-12-16 08:16:51
-broken link: http://reddog.s35.xrea.com/software/sqlitefunc.zip -- Giorgio 2006-03-16 22:29:29
-I fixed it. thanks. -- reddog 2006-03-17 01:23:19
#comment
#commente
----
[[CategoryTclTk]] [[TkSQLite]]
[[CategoryEnglish]]
HTML convert time: 0.007 sec.