I will build in some general sql functions to next version of [[tkSQLiteGUI]].~
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:
foreach cmd [info command Sqlite::func::*] {
db function [namespace tail $cmd] $cmd
}
then
% db eval {SELECT MD5('md5 test')}
2E5F9458BCD27E3C2B5908AF0B91551A
*** Download [#n5ef0eb9]
http://reddog.s35.xrea.com/software/sqlitefunc.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 |
*** One Line Comment [#h4c6dd90]
#commente
----
[[CategoryTclTk]] [[tkSQLiteGUI]]
[[CategoryEnglish]]
HTML convert time: 0.005 sec.