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.
package require sqlite3 source sqlitefunc.tcl sqlite db :memory: Sqlite::func::install db
then
% db eval {SELECT MD5('md5 test')}
2E5F9458BCD27E3C2B5908AF0B91551A
http://reddog.s35.xrea.com/software/sqlitefunc0.4.zip http://reddog.s35.xrea.com/software/sqlitefunc0.5.zip
| 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. |
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
| now() | return localtime. same as datetime('now', 'localtime') |
| julianday | SQLite Built-in |
| datetime | SQLite Built-in |
| date | SQLite Built-in |
| strftime | SQLite Built-in |
| 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 |
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.
| 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.