/*
** mIRC SQLite v1.0.15
**
** Author: Reko Tiira [ ramirez ]
** E-mail: ramirez AT whahay DOT net
** Date: 30th July 2007
** IRC: ramirez AT irc.undernet.org [ #mircscripting ]
**
** Please see msqlite.chm for documentation.
**
** Any questions, suggestions and such should be
** e-mailed at the above e-mail.
*/
/*
** SQLite Constants
*/
alias SQLITE_OK return 0
alias SQLITE_ERROR return 1
alias SQLITE_INTERNAL return 2
alias SQLITE_PERM return 3
alias SQLITE_ABORT return 4
alias SQLITE_BUSY return 5
alias SQLITE_LOCKED return 6
alias SQLITE_NOMEM return 7
alias SQLITE_READONLY return 8
alias SQLITE_INTERRUPT return 9
alias SQLITE_IOERR return 10
alias SQLITE_CORRUPT return 11
alias SQLITE_NOTFOUND return 12
alias SQLITE_FULL return 13
alias SQLITE_CANTOPEN return 14
alias SQLITE_PROTOCOL return 15
alias SQLITE_EMPTY return 16
alias SQLITE_SCHEMA return 17
alias SQLITE_TOOBIG return 18
alias SQLITE_CONSTRAINT return 19
alias SQLITE_MISMATCH return 20
alias SQLITE_MISUSE return 21
alias SQLITE_NOLFS return 22
alias SQLITE_AUTH return 23
alias SQLITE_FORMAT return 24
alias SQLITE_RANGE return 25
alias SQLITE_NOTADB return 26
alias SQLITE_INVALIDARG return 200
alias SQLITE_NOMOREROWS return 201
alias SQLITE_NOTMEMORYDB return 202
alias SQLITE_BEG return 1
alias SQLITE_CUR return 2
alias SQLITE_END return 3
alias SQLITE_BOTH return 1
alias SQLITE_NUM return 2
alias SQLITE_ASSOC return 3
alias SQLITE_INTEGER return 1
alias SQLITE_FLOAT return 2
alias SQLITE_TEXT return 3
alias SQLITE_BLOB return 4
alias SQLITE_NULL return 5
alias SQLITE_DENY return 1
alias SQLITE_IGNORE return 2
alias SQLITE_CREATE_INDEX return 1
alias SQLITE_CREATE_TABLE return 2
alias SQLITE_CREATE_TEMP_INDEX return 3
alias SQLITE_CREATE_TEMP_TABLE return 4
alias SQLITE_CREATE_TEMP_TRIGGER return 5
alias SQLITE_CREATE_TEMP_VIEW return 6
alias SQLITE_CREATE_TRIGGER return 7
alias SQLITE_CREATE_VIEW return 8
alias SQLITE_DELETE return 9
alias SQLITE_DROP_INDEX return 10
alias SQLITE_DROP_TABLE return 11
alias SQLITE_DROP_TEMP_INDEX return 12
alias SQLITE_DROP_TEMP_TABLE return 13
alias SQLITE_DROP_TEMP_TRIGGER return 14
alias SQLITE_DROP_TEMP_VIEW return 15
alias SQLITE_DROP_TRIGGER return 16
alias SQLITE_DROP_VIEW return 17
alias SQLITE_INSERT return 18
alias SQLITE_PRAGMA return 19
alias SQLITE_READ return 20
alias SQLITE_SELECT return 21
alias SQLITE_TRANSACTION return 22
alias SQLITE_UPDATE return 23
alias SQLITE_ATTACH return 24
alias SQLITE_DETACH return 25
alias SQLITE_ALTER_TABLE return 26
alias SQLITE_REINDEX return 27
alias SQLITE_ANALYZE return 28
alias SQLITE_CREATE_VTABLE return 29
alias SQLITE_DROP_VTABLE return 30
alias SQLITE_FUNCTION return 31
/*
** On Load Event
*/
on *:LOAD:{
if ($version < 6.2) {
echo 4 -a Obsoloete mIRC version $version
echo 4 -a mSQLite requires at least mIRC 6.2
echo 4 -a Script wasn't loaded.
unload -rs $+(",$script,")
halt
}
echo 3 -a mIRC SQLite loaded successfully.
}
/*
** SQLite DLL Path
*/
alias -l sqlite_dll return $qt($+($scriptdir,msqlite.dll))
/*
** SQLite API
*/
;;; Returns the version of the SQLite library.
;;; $sqlite_libversion
;;; The version of the library.
;;;
;;; The returned version is delimited by periods and has 3 different numbers indicating the version: a major, a minor and a revision number.
;;; For example 1.2.3 means that major version is 1, minor 2 and the revision number is 3.
;;;
;;;
;;; ; Displays the SQLite version to an active window
;;; //echo -a SQLite Version: $sqlite_libversion
;;;
;;; ; Example output:
;;; ; SQLite Version: 3.3.6
;;;
;;; sqlite_dllversion
alias sqlite_libversion {
return $dll($sqlite_dll, msqlite_libversion, $null)
}
;;; Returns the version of the mIRC SQLite DLL.
;;; $sqlite_dllversion
;;; The version of the library.
;;;
;;; The returned version is delimited by periods and has 3 different numbers indicating the version: a major, a minor and a revision number.
;;; For example 1.2.3 means that major version is 1, minor 2 and the revision number is 3.
;;;
;;;
;;; ; Displays the DLL version to an active window
;;; //echo -a DLL Version: $sqlite_dllversion
;;;
;;; ; Example output:
;;; ; DLL Version: 1.0.0
;;;
;;; sqlite_libversion
alias sqlite_dllversion {
return $dll($sqlite_dll, msqlite_dllversion, $null)
}
;;; Returns a textual representation of an error code.
;;; $sqlite_error_string ( errcode )
;;; The error code to format.
;;; A formatted, textual representation of errcode.
;;;
;;; ; Displays textual representation of the latest error code which is stored in %sqlite_errno
;;; var %err = %sqlite_errno
;;; echo -a Last Error: $sqlite_error_string(%err)
;;;
;;; ; Example output:
;;; ; Last Error: not an error
;;;
;;; sqlite_last_error
alias sqlite_error_string {
return $dll($sqlite_dll, msqlite_error_string, $1)
}
;;; Opens a SQLite database.
;;; $sqlite_open ( [ db, [ from ] ] )
;;; The filename of the database to open. Optional, see remarks for details.
;;; The filename of the database to create memory database from. Optional, see remarks for details.
;;; A positive, numeric connection identifier if successful, or $null if there was an error.
;;;
;;; The db argument is optional, if it isn't specified, a transient database is opened in a temporary file. If specified, and the file db doesn't exist, an empty database will be created on that file.
;;;
;;; If db is equal to the special keyword :memory: a memory database is opened instead of a file database. If from is specified the memory database will contain a copy of the specified database, otherwise an empty memory database is created. If file from doesn't exist, an empty database will be created on that file.
;;; from is only valid when :memory: is used, otherwise an error is raised.
;;;
;;; If $null is returned you can determine the exact reason for the error by checking the value of %sqlite_errstr.
;;; For more information about error handling, see Handling Errors
;;;
;;;
;;; ; Opens a database and displays the status after. Closes the db if it was opened successfully.
;;; var %db = $sqlite_open(test.db)
;;; if (%db) {
;;; echo -a Database opened successfully.
;;; sqlite_close %db
;;; }
;;; else {
;;; echo -a Error opening database: %sqlite_errstr
;;; }
;;;
;;; sqlite_close
;;; sqlite_open_memory
;;; sqlite_write_to_file
alias sqlite_open {
return $dll($sqlite_dll, msqlite_open, $qt($1) $iif($0 == 2, $qt($2)))
}
;;; Opens a SQLite memory database.
;;; $sqlite_open_memory ( [ from ] )
;;; The filename of the database to create memory database from. Optional, see remarks for details.
;;; A positive, numeric connection identifier if successful, or $null if there was an error.
;;;
;;; This function is identical to using $sqlite_open with the first argument db set to special keyword :memory:. See its reference for details.
;;;
;;;
;;; ; Opens a database and displays the status after. Closes the db if it was opened successfully.
;;; var %db = $sqlite_open_memory()
;;; if (%db) {
;;; echo -a Memory database created and opened successfully.
;;; sqlite_close %db
;;; }
;;; else {
;;; echo -a Error opening a memory database: %sqlite_errstr
;;; }
;;;
;;; sqlite_open
;;; sqlite_close
;;; sqlite_write_to_file
alias sqlite_open_memory {
return $sqlite_open(:memory:, $1)
}
;;; Closes an open SQLite database connection.
;;; $sqlite_close ( conn )
;;; /sqlite_close conn
;;; The connection identifier.
;;; 1 if connection was closed successfully, or $null if there was an error.
;;; It is usually ok to ignore the return value of $sqlite_close because the only case an error is returned is when an invalid conn is specified.
;;;
;;; ; Opens a database and displays the status after. Closes the db if it was opened successfully.
;;; var %db = $sqlite_open(test.db)
;;; if (%db) {
;;; echo -a Database opened successfully.
;;; sqlite_close %db
;;; }
;;; else {
;;; echo -a Error opening database: %sqlite_errstr
;;; }
;;;
;;; sqlite_open
alias sqlite_close {
return $dll($sqlite_dll, msqlite_close, $1)
}
;;; Executes a result-less SQL query.
;;; $sqlite_exec ( conn, query )
;;; /sqlite_exec conn query
;;; The connection identifier.
;;; The query to execute.
;;; 1 on success, or $null if there was an error.
;;;
;;; If $null is returned you can determine the exact reason for the error by checking the value of %sqlite_errstr.
;;; For more information about error handling, see Handling Errors
;;;
;;; $sqlite_exec can execute multiple queries seperated by semicolons.
;;; To see guidelines for writing SQL queries with mIRC SQLite, see Writing Queries.
;;;
;;;
;;; ; Inserts data to a table, assumes that a db connection is already established
;;; var %sql = INSERT INTO table (key, value) VALUES ('version', '1.0.0')
;;; if ($sqlite_exec(%db, %sql)) {
;;; echo -a Query executed succesfully.
;;; }
;;; else {
;;; echo -a Error executing query: %sqlite_errstr
;;; }
;;;
;;; Handling Binary Data
;;; sqlite_query
alias sqlite_exec {
return $dll($sqlite_dll, msqlite_exec, $1-)
}
;;; Executes a SQL query and returns data returned by it.
;;; $sqlite_query ( conn, query )
;;; The connection identifier.
;;; The query to execute.
;;; A positive, numeric result identifier or 0 on success, or $null if there was an error.
;;;
;;; In case of 0 is returned, it means that $sqlite_query was used to execute a query that doesn't return any data, such as INSERT or UPDATE.
;;; A SELECT query always returns a result identifier on success, even if the query selected no rows. You can use $sqlite_num_rows to determine how many rows were returned.
;;;
;;; If $null is returned you can determine the exact reason for the error by checking the value of %sqlite_errstr.
;;; For more information about error handling, see Handling Errors
;;;
;;; $sqlite_query can execute multiple queries seperated by semicolons. The returned result is the data returned by the last SQL query.
;;; To see guidelines for writing SQL queries with mIRC SQLite, see Writing Queries.
;;;
;;;
;;; ; Selects data from a table and fetches it, assumes that a db connection is already established
;;; var %sql = SELECT * FROM table
;;; var %request = $sqlite_query(%db, %sql)
;;; if (%request) {
;;; echo -a Query executed successfully.
;;; sqlite_free %request
;;; }
;;; else {
;;; echo -a Error executing query: %sqlite_errstr
;;; }
;;;
;;; Handling Binary Data
;;; sqlite_exec
;;; sqlite_fetch_row
;;; sqlite_fetch_single
;;; sqlite_num_rows
;;; sqlite_free
alias sqlite_query {
return $dll($sqlite_dll, msqlite_query, $1-)
}
;;; Frees a result returned by query operation.
;;; $sqlite_free ( result )
;;; /sqlite_free result
;;; The result identifier.
;;; 1 if result was freed successfully, or $null if there was an error.
;;; It is usually ok to ignore the return value of $sqlite_free because the only case an error is returned is when an invalid result is specified.
;;;
;;; ; Selects data from a table and then frees it (unpractical, only shows usage)
;;; var %sql = SELECT * FROM table
;;; var %request = $sqlite_query(%db, %sql)
;;; if (%request) {
;;; echo -a Query executed succesfully. Freeing data.
;;; sqlite_free %request
;;; }
;;; else {
;;; echo -a Error executing query: %sqlite_errstr
;;; }
;;;
;;; sqlite_query
alias sqlite_free {
return $dll($sqlite_dll, msqlite_free, $1)
}
;;; Escapes a string for use as a query parameter.
;;; $sqlite_escape_string ( string )
;;; The string to escape.
;;; Escaped string.
;;;
;;; $sqlite_escape_string escapes the specific string so that it can be used safely in queries.
;;; You should always call $sqlite_escape_string on user input to avoid SQL injection.
;;;
;;;
;;; var %str = $?="Input a string:"
;;; var %sql = INSERT INTO table (value) VALUES (' $+ $sqlite_escape_string(%str) $+ ')
;;; ; %sql can now be safely executed
;;;
;;; sqlite_query
;;; sqlite_exec
;;; sqlite_qt
alias sqlite_escape_string {
return $replace($1-, ', '')
}
;;; Returns a number of rows in a result.
;;; $sqlite_num_rows ( result )
;;; The result identifier.
;;; The number of rows in the result on success, or $null if there was an error.
;;;
;;; ; This code assumes a connection is already established and stored in %db
;;; var %sql = SELECT * FROM table
;;; var %res = $sqlite_query(%db, %sql)
;;; if (%res) {
;;; echo -a Number of rows returned: $sqlite_num_rows(%res)
;;; sqlite_free %res
;;; }
;;; else {
;;; echo -a Error executing query: %sqlite_errstr
;;; }
;;;
;;; sqlite_query
;;; sqlite_num_fields
alias sqlite_num_rows {
return $dll($sqlite_dll, msqlite_num_rows, $1)
}
;;; Returns a number of fields in a result.
;;; $sqlite_num_fields ( result )
;;; The result identifier.
;;; The number of fields in the result on success, or $null if there was an error.
;;;
;;; ; This code assumes a connection is already established and stored in %db
;;; var %sql = SELECT * FROM table
;;; var %res = $sqlite_query(%db, %sql)
;;; if (%res) {
;;; echo -a Number of fields returned: $sqlite_num_fields(%res)
;;; sqlite_free %res
;;; }
;;; else {
;;; echo -a Error executing query: %sqlite_errstr
;;; }
;;;
;;; sqlite_query
;;; sqlite_num_rows
alias sqlite_num_fields {
return $dll($sqlite_dll, msqlite_num_fields, $1)
}
;;; Returns a number of affected rows of the last INSERT, UPDATE or DELETE query.
;;; $sqlite_changes ( conn )
;;; The connection identifier.
;;; The number of affected rows on success, or $null if there was an error.
;;;
;;; ; This code assumes a connection is already established and stored in %db
;;; var %sql = UPDATE publishers SET publisher = 'Square Enix' WHERE publisher = 'Squaresoft'
;;; if ($sqlite_exec(%db, %sql)) {
;;; echo -a Number of rows affected: $sqlite_changes(%db)
;;; sqlite_free %res
;;; }
;;; else {
;;; echo -a Error executing query: %sqlite_errstr
;;; }
;;;
;;; sqlite_exec
;;; sqlite_query
alias sqlite_changes {
return $dll($sqlite_dll, msqlite_changes, $1)
}
;;; Returns the row id of the most recently inserted row.
;;; $sqlite_last_insert_rowid ( conn )
;;; The connection identifier.
;;; The row id on success, or $null if there was an error.
;;;
;;; ; This code assumes a connection is already established and stored in %db
;;; var %sql = UPDATE publishers SET publisher = 'Square Enix' WHERE publisher = 'Squaresoft'
;;; if ($sqlite_exec(%db, %sql)) {
;;; echo -a Number of rows affected: $sqlite_changes(%db)
;;; sqlite_free %res
;;; }
;;; else {
;;; echo -a Error executing query: %sqlite_errstr
;;; }
;;;
;;; sqlite_exec
;;; sqlite_query
alias sqlite_last_insert_rowid {
return $dll($sqlite_dll, msqlite_last_insert_rowid, $1)
}
;;; Returns the name of the specified field.
;;; $sqlite_field_name ( result, field_index )
;;; The result identifier.
;;; The ordinal index of the field. The first field has an index of 1.
;;; The name of the field on success, or $null if there was an error.
;;;
;;; ; This code assumes a connection is already established and stored in %db
;;; var %sql = SELECT * FROM table
;;; var %res = $sqlite_query(%db, %sql)
;;; if (%res) {
;;; echo -a Name of the first field: $sqlite_field_name(%res, 1)
;;; sqlite_free %res
;;; }
;;; else {
;;; echo -a Error executing query: %sqlite_errstr
;;; }
;;;
;;; sqlite_query
alias sqlite_field_name {
return $dll($sqlite_dll, msqlite_field_name, $gettok($1, 1, 32) $2)
}
;;; Returns the type of the specified field.
;;; $sqlite_field_type ( result, field_index [, row_index ] )
;;; The result identifier.
;;; The ordinal index of the field. The first field has an index of 1.
;;; The row index to get the type for. Optional; If omitted current row is assumed.
;;; The type of the field on success, or $null if there was an error.
;;;
;;; The returned type can be one of the following values: $SQLITE_INTEGER, $SQLITE_FLOAT, $SQLITE_TEXT, $SQLITE_BLOB and $SQLITE_NULL.
;;;
;;; SQLite uses so called manifest typing, which means that field types aren't static.
;;; This means that every row can store any type for any field. $sqlite_field_type can be used to determine the type of the field for a specified row.
;;; There are exceptions to this, for details see Datatypes In SQLite Version 3.
;;;
;;; sqlite_query
alias sqlite_field_type {
return $dll($sqlite_dll, msqlite_field_type, $gettok($1, 1, 32) $2)
}
;;; Fetches the current row from a result and then advances to the next row.
;;; $sqlite_fetch_row ( result, hash_table [, result_type ] )
;;; The result identifier.
;;; The name of the hash table to where to store the row data.
;;; The type of the result. Optional, see remarks for more info.
;;; 1 on success; Otherwise 0 if there are no more rows available, or $null if there was an error.
;;;
;;; $sqlite_fetch_row fetches the next row from the result and stores the data in hash_table.
;;; If the hash table doesn't exist, it will be created; Otherwise it will be cleared before new data is stored.
;;;
;;; result_type specifies how the hash table is created, it can be one of the following: $SQLITE_NUM, $SQLITE_ASSOC or $SQLITE_BOTH. $SQLITE_BOTH is default.
;;; If $SQLITE_NUM is used, the hash table items will be field indexes, starting from index 1. If $SQLITE_ASSOC is used, the items will be field names. If $SQLIE_BOTH is used, both column indexes and names are used.
;;; In case of $SQLITE_BOTH, if some of the column names are identical to another columns' index, the index has priority and will be used as an item.
;;;
;;;
;;; ; This code assumes a connection is already established and stored in %db
;;; var %sql = SELECT first_name, last_name FROM contacts
;;; var %res = $sqlite_query(%db, %sql)
;;; if (%res) {
;;; echo -a Fetching results...
;;; echo -a -
;;; while ($sqlite_fetch_row(%res, row, $SQLITE_ASSOC)) {
;;; ; If you used $SQLITE_FETCH_NUM or $SQLITE_FETCH_ASSOC you could use 1 instead of first_name and 2 instead of last_name
;;; echo -a First name: $hget(row, first_name)
;;; echo -a Last name: $hget(row, last_name)
;;; echo -a -
;;; }
;;; sqlite_free %res
;;; }
;;; else {
;;; echo -a Error executing query: %sqlite_errstr
;;; }
;;;
;;; sqlite_query
;;; sqlite_fetch_single
alias sqlite_fetch_row {
return $dll($sqlite_dll, msqlite_fetch_row, $gettok($1, 1, 32) $gettok($2, 1, 32) $3)
}
;;; Fetches and returns the first column of the current row from a result and then advances to the next row.
;;; $sqlite_fetch_single ( result [, &binvar ] )
;;; The result identifier.
;;; The name of the binary variable to assign binary data to. Optional.
;;; The value of the first column of the fetched row if &binvar isn't specified, otherwise the size of the binary variable on success, $null if there are no more rows, or if there was an error.
;;;
;;; If &binvar is specified the behaviour of $sqlite_fetch_single changes slightly. Instead of returning the first column's value, it will assign it to a binvar and return 1 on success.
;;; In case the first column is not blob type, its text representation will be stored in the &binvar as sequential ascii values. If &binvar isn't set, but the first column is a blob, it will be converted to text.
;;; For more information about handling binary data in mSQLite, see Handling Binary Data
;;;
;;; In case of $null is returned it can mean three different things:
;;; 1. The returned value from SQLite database is NULL.
;;; 2. There are no more rows available.
;;; 3. There was an error.
;;;
;;; To determine the cause of $null, examine the %sqlite_errno variable after calling $sqlite_fetch_single. The returned value can be one of the following:
;;; 1. $SQLITE_OK if there was no error.
;;; 2. $SQLITE_NOMOREROWS if there are no more rows available.
;;; 3. Some other of the Error Codes if there was an error.
;;;
;;;
;;; ; This code assumes a connection is already established and stored in %db
;;; var %sql = SELECT COUNT(*) FROM contacts
;;; var %res = $sqlite_query(%db, %sql)
;;; if (%res) {
;;; echo -a Number of rows in contacts: $sqlite_fetch_single(%res)
;;; sqlite_free %res
;;; }
;;; else {
;;; echo -a Error executing query: %sqlite_errstr
;;; }
;;;
;;; sqlite_query
;;; sqlite_fetch_row
;;; sqlite_fetch_field
alias sqlite_fetch_single {
if ($0 < 2) {
return $dll($sqlite_dll, msqlite_fetch_single, $1)
}
else {
tokenize 32 $dll($sqlite_dll, msqlite_fetch_single, $gettok($1, 1, 32) $2)
if ($0 == 3) {
bread $1 0 $2 $3
return $bvar($3, 0)
}
return $null
}
}
;;; Fetches and returns the specified column of the current row from a result and then advances to the next row.
;;; $sqlite_fetch_field ( result, field [, &binvar ] ) [ .name ]
;;; The result identifier.
;;; The field index or name. See remarks for details.
;;; The name of the binary variable to assign binary data to. Optional.
;;; Forces field to be treated as name.
;;; The value of the specified column of the fetched row if &binvar isn't specified, otherwise the size of the binary variable on success, $null if there are no more rows, or if there was an error.
;;;
;;; $sqlite_fetch_field is identical to $sqlite_fetch_single with the only difference being that $sqlite_fetch_field returns a value of specified column, instead of the first column.
;;;
;;; If field is numeric it is treated as an ordinal index for the column, first column being 1, otherwise it is treated as the column's name. You can use the .name property to force the field to be treated as column name even if it's a number.
;;;
;;; See $sqlite_fetch_single for more details.
;;;
;;;
;;; ; This code assumes a connection is already established and stored in %db
;;; var %sql = SELECT 1, 'test' AS '1'
;;; var %res = $sqlite_query(%db, %sql)
;;; if (%res) {
;;; ; This treats 1 as field ordinal, thus first column is returned
;;; echo -a Fetch 1st field: $sqlite_fetch_field(%res, 1)
;;; ; Go back to previous row because $sqlite_fetch_field increments row counter
;;; sqlite_rewind %res
;;; ; This treats 1 as field nane because of the .name property, second column is returned
;;; echo -a Fetch 2nd field: $sqlite_fetch_field(%res, 1).name
;;; ; Free result
;;; sqlite_free %res
;;; }
;;; else {
;;; echo -a Error executing query: %sqlite_errstr
;;; }
;;;
;;; ; Output:
;;; ; Fetch 1st field: 1
;;; ; Fetch 2nd field: a
;;;
;;; sqlite_query
;;; sqlite_fetch_row
;;; sqlite_fetch_single
;;; sqlite_result
alias sqlite_fetch_field {
if ($0 < 3) {
return $dll($sqlite_dll, msqlite_fetch_field, $1 $iif($2 !isnum || $prop == name, 1, 0) $2 1)
}
else {
tokenize 32 $dll($sqlite_dll, msqlite_fetch_field, $1 $iif($2 !isnum || $prop == name, 1, 0) $2 1 $3)
if ($0 == 3) {
bread $1 0 $2 $3
return $bvar($3, 0)
}
return $null
}
}
;;; Returns whether or not more rows are available.
;;; $sqlite_has_more ( result )
;;; The result identifier.
;;; 1 if there are more rows available; Otherwise 0 if there are no more rows available, or $null if there was an error.
;;;
;;; ; This code assumes a connection is already established and stored in %db
;;; var %sql = SELECT * FROM table
;;; var %res = $sqlite_query(%db, %sql)
;;; if (%res) {
;;; ; Counts number of rows, unpractical as you could just use $sqlite_num_rows, this is only to show usage
;;; var %rows = 0
;;; while ($sqlite_has_more(%res)) {
;;; sqlite_next %res
;;; inc %rows
;;; }
;;; echo -a Number of rows returned: %rows
;;; sqlite_free %res
;;; }
;;; else {
;;; echo -a Error executing query: %sqlite_errstr
;;; }
;;;
;;; sqlite_query
;;; sqlite_num_rows
;;; sqlite_next
;;; sqlite_prev
;;; sqlite_has_prev
alias sqlite_has_more {
return $dll($sqlite_dll, msqlite_has_more, $1)
}
;;; Returns whether or not a previous row is available.
;;; $sqlite_has_prev ( result )
;;; The result identifier.
;;; 1 if there is a previous available; Otherwise 0 if there is no previous row available, or $null if there was an error.
;;; sqlite_query
;;; sqlite_num_rows
;;; sqlite_prev
;;; sqlite_next
;;; sqlite_has_more
alias sqlite_has_prev {
return $dll($sqlite_dll, msqlite_has_prev, $1)
}
;;; Seek to the next row number.
;;; $sqlite_next ( result )
;;; /sqlite_next result
;;; The result identifier.
;;; 1 on success; Otherwise 0 if there are no more rows available, or $null if there was an error.
;;; sqlite_query
;;; sqlite_num_rows
;;; sqlite_prev
;;; sqlite_has_more
;;; sqlite_has_prev
alias sqlite_next {
return $dll($sqlite_dll, msqlite_next, $1)
}
;;; Seek to the previous row number.
;;; $sqlite_prev ( result )
;;; /sqlite_prev result
;;; The result identifier.
;;; 1 on success; Otherwise 0 if there is no previous row available, or $null if there was an error.
;;; sqlite_query
;;; sqlite_num_rows
;;; sqlite_next
;;; sqlite_has_more
;;; sqlite_has_prev
alias sqlite_prev {
return $dll($sqlite_dll, msqlite_prev, $1)
}
;;; Seek to a particular row.
;;; $sqlite_seek ( result, row_index [, seek_type ] )
;;; /sqlite_seek result row_index [ seek_type ]
;;; The result identifier.
;;; The row to seek to.
;;; The seek type. Optional, see remarks for more info.
;;; 1 on success; Otherwise 0 if the row isn't seekable, or $null if there was an error.
;;;
;;; The optional seek_type parameter specifies the direction and offset of seek and can be one of the following: $SQLITE_BEG, $SQLITE_CUR or $SQLITE_END. $SQLITE_BEG is default.
;;; $SQLITE_BEG seeks forward from the start of the result. $SQLITE_CUR seeks forward from the current position of the result. $SQLITE_END seeks backwards from the end of the result.
;;;
;;; sqlite_query
;;; sqlite_key
alias sqlite_seek {
return $dll($sqlite_dll, msqlite_seek, $gettok($1, 1, 32) $2)
}
;;; Seeks to the first row.
;;; $sqlite_rewind ( result )
;;; /sqlite_rewind result
;;; The result identifier.
;;; 1 on success; Otherwise 0 if the result isn't rewindable, or $null if there was an error.
;;;
;;; $sqlite_rewind is equivalent to calling $sqlite_seek with row value of 1 and seek_type of $SQLITE_BEG.
;;;
;;; sqlite_query
;;; sqlite_key
;;; sqlite_rewind
alias sqlite_rewind {
return $sqlite_seek($1, 1, $SQLITE_BEG)
}
;;; Returns the current row number of a result.
;;; $sqlite_key ( result )
;;; The result identifier.
;;; The current row number on success, or $null if there was an error.
;;;
;;; $sqlite_rewind is equivalent to calling $sqlite_seek with row value of 1 and seek_type of $SQLITE_BEG.
;;;
;;; sqlite_query
;;; sqlite_key
;;; sqlite_rewind
alias sqlite_key {
return $dll($sqlite_dll, msqlite_key, $1)
}
;;; Fetches the current row from a result.
;;; $sqlite_current ( result, hash_table [, result_type ] )
;;; The result identifier.
;;; The name of the hash table to where to store the row data.
;;; The type of the result. Optional, see remarks for more info.
;;; 1 on success; Otherwise 0 if the current row position is beyond final row, or $null if there was an error.
;;;
;;; $sqlite_current is identical to $sqlite_fetch_row except it doesn't advance to the next row.
;;;
;;; sqlite_fetch_row
alias sqlite_current {
return $dll($sqlite_dll, msqlite_current, $gettok($1, 1, 32) $gettok($2, 1, 32) $3)
}
;;; Fetches and returns the specified column of the current row from a result.
;;; $sqlite_result ( result, field [, &binvar ] ) [ .name ]
;;; The result identifier.
;;; The field index or name. See remarks for details.
;;; The name of the binary variable to assign binary data to. Optional.
;;; Forces field to be treated as name.
;;; The value of the specified column of the fetched row if &binvar isn't specified, otherwise the size of the binary variable on success, $null if there are no more rows, or if there was an error.
;;;
;;; $sqlite_result is identical to $sqlite_fetch_field except it doesn't advance to the next row.
;;;
;;; sqlite_fetch_field
alias sqlite_result {
if ($0 < 3) {
return $dll($sqlite_dll, msqlite_fetch_field, $1 $iif($2 !isnum || $prop == name, 1, 0) $2 0)
}
else {
tokenize 32 $dll($sqlite_dll, msqlite_fetch_field, $1 $iif($2 !isnum || $prop == name, 1, 0) $2 0 $3)
if ($0 == 3) {
bread $1 0 $2 $3
return $bvar($3, 0)
}
return $null
}
}
;;; Sets the busy timeout duration or disables busy handlers.
;;; $sqlite_busy_timeout ( conn, milliseconds )
;;; /sqlite_busy_timeout conn milliseconds
;;; The connection identifier.
;;; The number of milliseconds.
;;; 1 on success, or $null if there was an error.
;;;
;;; Set the maximum time, in milliseconds, that SQLite will wait for a conn to become ready for use.
;;; Use 0 for milliseconds to disable busy handlers.
;;;
;;; You can set the default busy timeout in the mSQLite configuration file, see Configuring mIRC SQLite for details.
;;; It is usually ok to ignore the return value of $sqlite_busy_timeout because the only case an error is returned is when an invalid conn is specified.
;;;
alias sqlite_busy_timeout {
return $dll($sqlite_dll, msqlite_busy_timeout, $gettok($1, 1, 32) $2)
}
;;; Register an user defined function.
;;; $sqlite_create_function ( conn, func_name, func_alias [, num_args [, prop ] ] )
;;; /sqlite_create_function conn func_name func_alias [ num_args [ prop ] ]
;;; The connection identifier.
;;; The SQL function name to register.
;;; The mIRC alias to register as function.
;;; The number of arguments the function accepts. Optional.
;;; A custom property you want to use with alias name. Optional.
;;; 1 if the function was registered successfully, or $null if there was an error.
;;;
;;; $sqlite_create_function allows users to register their own functions in SQL.
;;; You can also use $sqlite_create_function to override default functionality of SQLite's core functions.
;;;
;;; The optional argument num_args can be used to hint SQLite if there's a predetermined amount of arguments.
;;; If an user defined function is used with different parameter count as what was instructed with num_args, the query will raise an SQL error.
;;; The default, -1, means that an arbitrary number of parameters can be passed to the function.
;;;
;;; It is usually ok to ignore the return value of $sqlite_create_function if you know that the parameters are valid.
;;; For more information about user defined functions, see User Defined Functions.
;;;
;;;
;;; ; Returns a specified amount of characters from left side of a md5 generated string
;;; alias md5_left {
;;; var %str = $1, %len = $2
;;; return $left($md5(%str), %len)
;;; }
;;;
;;; ; Registers the user defined function in an existing connection %db
;;; sqlite_create_function %db md5_left md5_left 2
;;;
;;; ; Registers mIRC's $base as convert_base (3 first parameters, no zeropadding or precision)
;;; sqlite_create_function %db convert_base base 3
;;;
;;; ; Example queries
;;; ; SELECT md5_left(value, 8) FROM strings
;;; ; SELECT convert_base(15, 10, 16)
;;;
;;; User Defined Functions
;;; Function Limitations
;;; sqlite_create_aggregate
alias sqlite_create_function {
return $dll($sqlite_dll, msqlite_create_function, $gettok($1, 1, 32) $gettok($2, 1, 32) $gettok($3, 1, 32) $gettok($4, 1, 32) $5)
}
;;; Register an user defined aggregate.
;;; $sqlite_create_aggregate ( conn, func_name, step_alias, finalize_alias [, num_args [, step_prop [, finalize_prop ] ] ] )
;;; /sqlite_create_aggregate conn func_name step_alias finalize_alias [ num_args [ step_prop [ finalize_prop ] ] ]
;;; The connection identifier.
;;; The SQL function name to register.
;;; The mIRC alias to register as step function.
;;; The mIRC alias to register as finalize function.
;;; The number of arguments the function accepts. Optional.
;;; A custom property you want to use for step alias. Optional.
;;; A custom property you want to use for finalize alias. Optional.
;;; 1 if the function was registered successfully, or $null if there was an error.
;;;
;;; $sqlite_create_aggregate allows users to register their own aggregates in SQL.
;;; You can also use $sqlite_create_aggregate to override default functionality of SQLite's core aggregates.
;;;
;;; Creating aggregate functions is similiar to creating ordinary functions.
;;; The key difference is that $sqlite_create_aggregate requires two mIRC aliases to be registered:
;;; step_alias is called for each row in a group, it is used to accumulate data.
;;; finalize_alias is called after all the rows are processed, it is used to return the result to the query.
;;;
;;; step_alias and finalize_alias always receive an aggregate context as first parameter.
;;; The step_alias can use the aggregate context to accumulate data. It should return the aggregate context at end.
;;; When finalize_alias is called it can use the aggregate context to determine the accumuluted result.
;;; Parameters passed to the function in the SQL query are passed after the aggregate context.
;;;
;;; The optional argument num_args can be used to hint SQLite if there's a predetermined amount of arguments.
;;; If an user defined function is used with different parameter count as what was instructed with num_args, the query will raise an SQL error.
;;; The default, -1, means that an arbitrary number of parameters can be passed to the function.
;;;
;;; It is usually ok to ignore the return value of $sqlite_create_function if you know that the parameters are valid.
;;; For more information about user defined aggregates, see User Defined Functions and User Defined Aggregates.
;;;
;;;
;;; ; Creates an aggregate to return the max length of a string
;;; alias max_len_step {
;;; var %context = $1, %len = $len($2)
;;; if (%context !isnum || %len > %context) {
;;; %context = %len
;;; }
;;; return %context
;;; }
;;;
;;; alias max_len_finalize {
;;; var %context = $1
;;; if (%context !isnum) {
;;; %context = 0
;;; }
;;; return %context
;;; }
;;;
;;; ; Registers the user defined function in an existing connection %db
;;; sqlite_create_aggregate %db max_len max_len_step max_len_finalize
;;;
;;; ; Example queries
;;; ; SELECT max_len(value) FROM strings
;;; ; SELECT max_len(first_name) FROM contacts GROUP BY last_name
;;;
;;; User Defined Functions
;;; User Defined Aggregates
;;; Function Limitations
;;; sqlite_create_function
alias sqlite_create_aggregate {
return $dll($sqlite_dll, msqlite_create_aggregate, $gettok($1, 1, 32) $gettok($2, 1, 32) $gettok($3, 1, 32) $gettok($4, 1, 32) $gettok($5, 1, 32) $gettok($6, 1, 32) $7)
}
;;; Allows user defined functions to signal an error.
;;; $sqlite_signal_error ( error )
;;; /sqlite_signal_error error
;;; The error to be signaled.
;;; 1 on success, or $null if there was an error.
;;;
;;; It is usually ok to ignore the return value of $sqlite_signal_error because the only case an error is returned it's called from an alias that isn't an user defined function.
;;;
;;;
;;; ; User defined function
;;; alias udf_error {
;;; sqlite_signal_error this function will always signal an error
;;; }
;;;
;;; ; Example, always outputs error
;;; sqlite_create_function udf_error udf_error -1
;;; sqlite_exec SELECT udf_error()
;;; if (%sqlite_errno != $SQLITE_OK) {
;;; echo 4 -a %sqlite_errstr
;;; }
;;;
;;;
;;; ; Another way to signal an error
;;; alias udf_another_error {
;;; return $sqlite_signal_error(this function will always signal an error)
;;; echo -a This part is never reached!
;;; }
;;;
;;; User Defined Functions
;;; User Defined Aggregates
;;; Function Limitations
alias sqlite_signal_error {
return $dll($sqlite_dll, msqlite_signal_error, $1-)
}
;;; Add single quotes around text.
;;; $sqlite_qt ( text )
;;; The text to be single quoted.
;;; The single quoted text.
;;;
;;; This is an auxiliary identifier that can be used to quote data prior to using them in queries.
;;; You should this instead of the mIRC's $qt identifier, the reason is explained in FAQ, under the question "Should I use single or double quotes for string literals?"
;;;
;;;
;;; ; Escape data, and then add quotes around it
;;; %data = $sqlite_qt($sqlite_escape_string(%data))
;;; ; Execute a query
;;; sqlite_exec %db INSERT INTO table (data) VALUES ( %data )
;;;
;;; sqlite_escape_string
alias sqlite_qt {
return $+(',$1-,')
}
;;; Retrieves meta information about a specific column.
;;; $sqlite_field_metadata ( conn [, database ], table, column, htable )
;;; The connection identifier.
;;; The database name where table exists. Optional, see remarks for details.
;;; The table name where column exists.
;;; The column to get meta information about.
;;; The hash table to assign results to.
;;; 1 on success, or $null if there was an error.
;;;
;;; This identifier fetches meta information about a specific column, and assigns the results to htable.
;;; The hash table will have the following items set:
;;; [pre]
;;; dattype The declared data type, eg. INTEGER
;;; collseq The collation sequence name, eg. BINARY
;;; notnull 1 if NOT NULL constraint exists, otherwise 0
;;; primkey 1 if column is part of PRIMARY KEY, otherwise 0
;;; autoinc 1 if column is AUTOINCREMENT, otherwise 0
;;; [/pre]
;;; The database argument is optional. If omitted all attached databases in conn will be searched for the specified table.
;;;
alias sqlite_field_metadata {
return $dll($sqlite_dll, msqlite_field_metadata, $1-)
}
;;; Loads a dynamic extension.
;;; $sqlite_load_extension ( conn, filename [, entrypoint ] )
;;; /sqlite_load_extension conn filename [ entrypoint ]
;;; The connection identifier.
;;; The filename of the extension.
;;; The entrypoint of the extension Optional, see remarks for details.
;;; 1 on success, or $null if there was an error.
;;;
;;; This function loads a dynamic extension filename. Dynamic extensions are useful for creating efficient custom functions and virtual table modules.
;;; The optional parameter entrypoint specifies the initialization function within the extension. If omitted, the entrypoint will default to sqlite3_extension_init, which is recommended. See SQLite Wiki's Loadable Extensions article for details and more information.
;;;
;;; NOTE. The loadable extension API, as well as the virtual table API, is still beta in SQLite, hence if there are any changes in the API in the future, the changes might also reflect in mSQLite.
;;;
alias sqlite_load_extension {
return $dll($sqlite_dll, msqlite_load_extension, $gettok($1, 1, 32) $gettok($2, 1, 32) $3)
}
;;; Checks if a database connection is valid.
;;; $sqlite_is_valid_conn ( conn )
;;; The connection identifier.
;;; 1 if conn is a valid connection, 0 if it's invalid, or $null if there was an error.
;;;
;;; It is usually ok to ignore if $sqlite_is_valid_conn returns $null because the only case an error is returned is when conn isn't specified.
;;;
;;; sqlite_is_valid_result
;;; sqlite_is_memory
alias sqlite_is_valid_conn {
return $dll($sqlite_dll, msqlite_is_valid_conn, $1)
}
;;; Checks if a result is valid.
;;; $sqlite_is_valid_result ( result )
;;; The result identifier.
;;; 1 if conn is a valid result, 0 if it's invalid, or $null if there was an error.
;;;
;;; It is usually ok to ignore if $sqlite_is_valid_result returns $null because the only case an error is returned is when result isn't specified.
;;;
;;; sqlite_is_valid_conn
;;; sqlite_is_memory
alias sqlite_is_valid_result {
return $dll($sqlite_dll, msqlite_is_valid_result, $1)
}
;;; Checks whether database is memory database or not.
;;; $sqlite_is_memory ( conn )
;;; The connection identifier.
;;; 1 if conn is a memory database, 0 if it's file database, or $null if there was an error.
;;; sqlite_is_valid_conn
;;; sqlite_is_valid_result
alias sqlite_is_memory {
return $dll($sqlite_dll, msqlite_is_memory, $1)
}
;;; Writes a memory database to a file.
;;; $sqlite_write_to_file ( conn, file )
;;; /sqlite_write_to_file conn file
;;; The connection identifier of a memory database.
;;; The filename to save the memory database to.
;;; 1 on success, or $null if there was an error.
;;;
;;; This function allows you to write a memory database to a file. It's useful if you want to work with a database on memory for vastly improved speed, but also keep a permanent copy on disk. Another common use is to debug errors by dumping a memory database to file so you can examine the contents with your favorite visual editor.
;;;
alias sqlite_write_to_file {
return $dll($sqlite_dll, msqlite_write_to_file, $gettok($1, 1, 32) $2)
}
;;; Reloads the configuration file.
;;; /sqlite_reload
;;;
;;; This command allows you to reload the configuration file in case you have made changes but don't want to reload the whole DLL.
;;;
alias sqlite_reload {
noop $dll($sqlite_dll, msqlite_reload, $null)
}
;;; Registers an authorizer for a connection.
;;; $sqlite_set_authorizer ( conn, authorizer_alias [, prop ] )
;;; /sqlite_set_authorizer conn authorizer_alias [ prop ]
;;; The connection identifier.
;;; The mIRC alias to register as an authorizer.
;;; A custom property you want to use with alias name. Optional.
;;; 1 if the authorizer was registered successfully, or $null if there was an error.
;;;
;;; An authorizer lets you to examine what kind of query SQLite is trying to run, and change the behavior of the result by either denying the operation, or ignoring it. The foremost purpose of an authorizer is to allow scripts to safely execute user-entered SQL queries, without compromising security of the database.
;;;
;;; Only one authorizer can be registered at a time for a connection. Setting a new authorizer for a connection will override the old authorizer, if one is registered. You can unregister the authorizer by passing $null for authorizer_alias.
;;;
;;; The authorizer should return $SQLITE_OK if the current action should be allowed, $SQLITE_DENY if it should be denied (generates an error) or $SQLITE_IGNORE if it should be ignored (treated as a no-op). You can use an authorizer to make certain columns or functions return NULL by returning $SQLITE_IGNORE for the corresponding action (see below). Returning any other value than the ones previously mentioned will auto-assume $SQLITE_OK.
;;;
;;; The authorizer alias will receive a few arguments. The first argument is the type of action SQLite is performing. The second and third argument provide additional information depending on what type of an action is in question. The fourth argument is the name of the database (eg. "main" or "temp") where applicable. The fifth argument is the name of the inner-most trigger or view that triggered the authorizer, or $null if the authorizer was triggered directly from code.
;;;
;;; You can see all the possible types of action that authorizer can be triggered for below, and the associated arguments for it (the 2nd and 3rd arguments of the authorizer alias).
;;; Type Value 2nd Arg 3rd Arg
$SQLITE_CREATE_INDEX 1 Index Name Table Name
;;; $SQLITE_CREATE_TABLE 2 Table Name $null
;;; $SQLITE_CREATE_TEMP_INDEX 3 Index Name Table Name
;;; $SQLITE_CREATE_TEMP_TABLE 4 Table Name $null
;;; $SQLITE_CREATE_TEMP_TRIGGER 5 Trigger Name Table name
;;; $SQLITE_CREATE_TEMP_VIEW 6 View Name $null
;;; $SQLITE_CREATE_TRIGGER 7 Trigger name Table Name
;;; $SQLITE_CREATE_VIEW 8 View Name $null
;;; $SQLITE_DELETE 9 Table Name $null
;;; $SQLITE_DROP_INDEX 10 Index Name Table Name
;;; $SQLITE_DROP_TABLE 11 Table Name $null
;;; $SQLITE_DROP_TEMP_INDEX 12 Index Name Table Name
;;; $SQLITE_DROP_TEMP_TABLE 13 Table Name $null
;;; $SQLITE_DROP_TEMP_TRIGGER 14 Trigger Name Table Name
;;; $SQLITE_DROP_TEMP_VIEW 15 View Name $null
;;; $SQLITE_DROP_TRIGGER 16 Trigger Name Table Name
;;; $SQLITE_DROP_VIEW 17 View Name $null
;;; $SQLITE_INSERT 18 Table Name $null
;;; $SQLITE_PRAGMA 19 Pragma Name 1st Arg or $null
;;; $SQLITE_READ 20 Table Name Column Name
;;; $SQLITE_SELECT 21 $null $null
;;; $SQLITE_TRANSACTION 22 $null $null
;;; $SQLITE_UPDATE 23 Table Name Column Name
;;; $SQLITE_ATTACH 24 Filename $null
;;; $SQLITE_DETACH 25 Database Name $null
;;; $SQLITE_ALTER_TABLE 26 Database Name Table Name
;;; $SQLITE_REINDEX 27 Index Name $null
;;; $SQLITE_ANALYZE 28 Table Name $null
;;; $SQLITE_CREATE_VTABLE 29 Table Name Module Name
;;; $SQLITE_DROP_VTABLE 30 Table Name Module Name
;;; $SQLITE_FUNCTION 31 Function Name $null
;;;
;;;
;;; ; The following example demonstrates how to create an authorizer that:
;;; ; 1) Disallows queries of other type than SELECT.
;;; ; 2) Disallows access to certain columns by making SQLite return NULL for them.
;;; alias my_authorizer {
;;; ; To make the function look cleaner, let's assign the tokens to variables.
;;; var %type = $1
;;; ; First deny queries other than SELECT
;;; if (%type != $SQLITE_SELECT && %type != $SQLITE_READ && %type != $SQLITE_FUNCTION) {
;;; ; Not authorized!
;;; return $SQLITE_DENY
;;; }
;;; ; Next make sure that we aren't trying to access a "secret" column, eg. a password in an users table.
;;; if (%type == $SQLITE_READ) {
;;; var %table = $2, %column = $3
;;; if (%table == users && %column == password) {
;;; ; By returning $SQLITE_IGNORE SQLite will return NULL for this column. It's a good idea to do this instead
;;; ; of denying the whole query, because in that case query such as "SELECT * FROM users" would fail as well.
;;; return $SQLITE_IGNORE
;;; }
;;; }
;;; ; Nothing special in this action, allow it.
;;; return $SQLITE_OK
;;; }
;;;
;;; ; To register the authorizer do (assumes that %db exists):
;;; sqlite_set_authorizer %db my_authorizer
;;;
alias sqlite_set_authorizer {
if ($2 != $null) {
return $dll($sqlite_dll, msqlite_set_authorizer, $gettok($1, 1, 32) $gettok($2, 1, 32) $3)
}
else {
return $dll($sqlite_dll, msqlite_set_authorizer, $1)
}
}