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. | | 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 †
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') | | julianday | SQLite Built-in | | datetime | SQLite Built-in | | date | SQLite Built-in | | strftime | SQLite Built-in |
Other Functions †
| 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 †
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 |
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
|