/*
** 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
*/
;;; <summary>Returns the version of the SQLite library.</summary>
;;; <syntax>$sqlite_libversion</syntax>
;;; <returns>The version of the library.</returns>
;;; <remarks>
;;; The returned version is delimited by periods and has 3 different numbers indicating the version: a <b>major</b>, a <b>minor</b> and a <b>revision</b> number.
;;; For example 1.2.3 means that major version is 1, minor 2 and the revision number is 3.
;;; </remarks>
;;; <example>
;;; ; Displays the SQLite version to an active window
;;; //echo -a SQLite Version: $sqlite_libversion
;;;
;;; ; Example output:
;;; ; SQLite Version: 3.3.6
;;; </example>
;;; <seealso>sqlite_dllversion</seealso>
alias sqlite_libversion {
return $dll($sqlite_dll, msqlite_libversion, $null)
}
;;; <summary>Returns the version of the mIRC SQLite DLL.</summary>
;;; <syntax>$sqlite_dllversion</syntax>
;;; <returns>The version of the library.</returns>
;;; <remarks>
;;; The returned version is delimited by periods and has 3 different numbers indicating the version: a <i>major</i>, a <i>minor</i> and a <i>revision</i> number.
;;; For example 1.2.3 means that major version is 1, minor 2 and the revision number is 3.
;;; </remarks>
;;; <example>
;;; ; Displays the DLL version to an active window
;;; //echo -a DLL Version: $sqlite_dllversion
;;;
;;; ; Example output:
;;; ; DLL Version: 1.0.0
;;; </example>
;;; <seealso>sqlite_libversion</seealso>
alias sqlite_dllversion {
return $dll($sqlite_dll, msqlite_dllversion, $null)
}
;;; <summary>Returns a textual representation of an error code.</summary>
;;; <syntax>$sqlite_error_string ( errcode )</syntax>
;;; <param name="errcode">The error code to format.</param>
;;; <returns>A formatted, textual representation of <i>errcode</i>.</returns>
;;; <example>
;;; ; 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
;;; </example>
;;; <seealso>sqlite_last_error</seealso>
alias sqlite_error_string {
return $dll($sqlite_dll, msqlite_error_string, $1)
}
;;; <summary>Opens a SQLite database.</summary>
;;; <syntax>$sqlite_open ( [ db, [ from ] ] )</syntax>
;;; <param name="db">The filename of the database to open. Optional, see remarks for details.</param>
;;; <param name="from">The filename of the database to create memory database from. Optional, see remarks for details.</param>
;;; <returns>A positive, numeric connection identifier if successful, or <b>$null</b> if there was an error.</returns>
;;; <remarks>
;;; The <i>db</i> argument is optional, if it isn't specified, a transient database is opened in a temporary file. If specified, and the file <i>db</i> doesn't exist, an empty database will be created on that file.
;;;
;;; If <i>db</i> is equal to the special keyword <b>:memory:</b> a memory database is opened instead of a file database. If <i>from</i> is specified the memory database will contain a copy of the specified database, otherwise an empty memory database is created. If file <i>from</i> doesn't exist, an empty database will be created on that file.
;;; <i>from</i> is only valid when <b>:memory:</b> is used, otherwise an error is raised.
;;;
;;; If <b>$null</b> is returned you can determine the exact reason for the error by checking the value of <b>%sqlite_errstr</b>.
;;; For more information about error handling, see <a href="errors.html">Handling Errors</a>
;;; </remarks>
;;; <example>
;;; ; 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
;;; }
;;; </example>
;;; <seealso>sqlite_close</seealso>
;;; <seealso>sqlite_open_memory</seealso>
;;; <seealso>sqlite_write_to_file</seealso>
alias sqlite_open {
return $dll($sqlite_dll, msqlite_open, $qt($1) $iif($0 == 2, $qt($2)))
}
;;; <summary>Opens a SQLite memory database.</summary>
;;; <syntax>$sqlite_open_memory ( [ from ] )</syntax>
;;; <param name="from">The filename of the database to create memory database from. Optional, see remarks for details.</param>
;;; <returns>A positive, numeric connection identifier if successful, or <b>$null</b> if there was an error.</returns>
;;; <remarks>
;;; This function is identical to using <a href="sqlite_open.html">$sqlite_open</a> with the first argument <i>db</i> set to special keyword <b>:memory:</b>. See its reference for details.
;;; </remarks>
;;; <example>
;;; ; 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
;;; }
;;; </example>
;;; <seealso>sqlite_open</seealso>
;;; <seealso>sqlite_close</seealso>
;;; <seealso>sqlite_write_to_file</seealso>
alias sqlite_open_memory {
return $sqlite_open(:memory:, $1)
}
;;; <summary>Closes an open SQLite database connection.</summary>
;;; <syntax>$sqlite_close ( conn )</syntax>
;;; <syntax>/sqlite_close conn</syntax>
;;; <param name="conn">The connection identifier.</param>
;;; <returns><b>1</b> if connection was closed successfully, or <b>$null</b> if there was an error.</returns>
;;; <remarks>It is usually ok to ignore the return value of $sqlite_close because the only case an error is returned is when an invalid <i>conn</i> is specified.</remarks>
;;; <example>
;;; ; 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
;;; }
;;; </example>
;;; <seealso>sqlite_open</seealso>
alias sqlite_close {
return $dll($sqlite_dll, msqlite_close, $1)
}
;;; <summary>Executes a result-less SQL query.</summary>
;;; <syntax>$sqlite_exec ( conn, query )</syntax>
;;; <syntax>/sqlite_exec conn query</syntax>
;;; <param name="conn">The connection identifier.</param>
;;; <param name="query">The query to execute.</param>
;;; <returns><b>1</b> on success, or <b>$null</b> if there was an error.</returns>
;;; <remarks>
;;; If <b>$null</b> is returned you can determine the exact reason for the error by checking the value of <b>%sqlite_errstr</b>.
;;; For more information about error handling, see <a href="errors.html">Handling Errors</a>
;;;
;;; $sqlite_exec can execute multiple queries seperated by semicolons.
;;; To see guidelines for writing SQL queries with mIRC SQLite, see <a href="queries.html">Writing Queries</a>.
;;; </remarks>
;;; <example>
;;; ; 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
;;; }
;;; </example>
;;; <seealso href="binary.html">Handling Binary Data</seealso>
;;; <seealso>sqlite_query</seealso>
alias sqlite_exec {
return $dll($sqlite_dll, msqlite_exec, $1-)
}
;;; <summary>Executes a SQL query and returns data returned by it.</summary>
;;; <syntax>$sqlite_query ( conn, query )</syntax>
;;; <param name="conn">The connection identifier.</param>
;;; <param name="query">The query to execute.</param>
;;; <returns>A positive, numeric result identifier or <b>0</b> on success, or <b>$null</b> if there was an error.</returns>
;;; <remarks>
;;; In case of <b>0</b> 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 <b>$sqlite_num_rows</b> to determine how many rows were returned.
;;;
;;; If <b>$null</b> is returned you can determine the exact reason for the error by checking the value of <b>%sqlite_errstr</b>.
;;; For more information about error handling, see <a href="errors.html">Handling Errors</a>
;;;
;;; $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 <a href="queries.html">Writing Queries</a>.
;;; </remarks>
;;; <example>
;;; ; 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
;;; }
;;; </example>
;;; <seealso href="binary.html">Handling Binary Data</seealso>
;;; <seealso>sqlite_exec</seealso>
;;; <seealso>sqlite_fetch_row</seealso>
;;; <seealso>sqlite_fetch_single</seealso>
;;; <seealso>sqlite_num_rows</seealso>
;;; <seealso>sqlite_free</seealso>
alias sqlite_query {
return $dll($sqlite_dll, msqlite_query, $1-)
}
;;; <summary>Frees a result returned by query operation.</summary>
;;; <syntax>$sqlite_free ( result )</syntax>
;;; <syntax>/sqlite_free result</syntax>
;;; <param name="result">The result identifier.</param>
;;; <returns><b>1</b> if result was freed successfully, or <b>$null</b> if there was an error.</returns>
;;; <remarks>It is usually ok to ignore the return value of $sqlite_free because the only case an error is returned is when an invalid <i>result</i> is specified.</remarks>
;;; <example>
;;; ; 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
;;; }
;;; </example>
;;; <seealso>sqlite_query</seealso>
alias sqlite_free {
return $dll($sqlite_dll, msqlite_free, $1)
}
;;; <summary>Escapes a string for use as a query parameter.</summary>
;;; <syntax>$sqlite_escape_string ( string )</syntax>
;;; <param name="item">The string to escape.</param>
;;; <returns>Escaped string.</returns>
;;; <remarks>
;;; $sqlite_escape_string escapes the specific <i>string</i> so that it can be used safely in queries.
;;; You should always call $sqlite_escape_string on user input to avoid SQL injection.
;;; </remarks>
;;; <example>
;;; var %str = $?="Input a string:"
;;; var %sql = INSERT INTO table (value) VALUES (' $+ $sqlite_escape_string(%str) $+ ')
;;; ; %sql can now be safely executed
;;; </example>
;;; <seealso>sqlite_query</seealso>
;;; <seealso>sqlite_exec</seealso>
;;; <seealso>sqlite_qt</seealso>
alias sqlite_escape_string {
return $replace($1-, ', '')
}
;;; <summary>Returns a number of rows in a result.</summary>
;;; <syntax>$sqlite_num_rows ( result )</syntax>
;;; <param name="result">The result identifier.</param>
;;; <returns>The number of rows in the result on success, or <b>$null</b> if there was an error.</returns>
;;; <example>
;;; ; 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
;;; }
;;; </example>
;;; <seealso>sqlite_query</seealso>
;;; <seealso>sqlite_num_fields</seealso>
alias sqlite_num_rows {
return $dll($sqlite_dll, msqlite_num_rows, $1)
}
;;; <summary>Returns a number of fields in a result.</summary>
;;; <syntax>$sqlite_num_fields ( result )</syntax>
;;; <param name="result">The result identifier.</param>
;;; <returns>The number of fields in the result on success, or <b>$null</b> if there was an error.</returns>
;;; <example>
;;; ; 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
;;; }
;;; </example>
;;; <seealso>sqlite_query</seealso>
;;; <seealso>sqlite_num_rows</seealso>
alias sqlite_num_fields {
return $dll($sqlite_dll, msqlite_num_fields, $1)
}
;;; <summary>Returns a number of affected rows of the last INSERT, UPDATE or DELETE query.</summary>
;;; <syntax>$sqlite_changes ( conn )</syntax>
;;; <param name="conn">The connection identifier.</param>
;;; <returns>The number of affected rows on success, or <b>$null</b> if there was an error.</returns>
;;; <example>
;;; ; 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
;;; }
;;; </example>
;;; <seealso>sqlite_exec</seealso>
;;; <seealso>sqlite_query</seealso>
alias sqlite_changes {
return $dll($sqlite_dll, msqlite_changes, $1)
}
;;; <summary>Returns the row id of the most recently inserted row.</summary>
;;; <syntax>$sqlite_last_insert_rowid ( conn )</syntax>
;;; <param name="conn">The connection identifier.</param>
;;; <returns>The row id on success, or <b>$null</b> if there was an error.</returns>
;;; <example>
;;; ; 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
;;; }
;;; </example>
;;; <seealso>sqlite_exec</seealso>
;;; <seealso>sqlite_query</seealso>
alias sqlite_last_insert_rowid {
return $dll($sqlite_dll, msqlite_last_insert_rowid, $1)
}
;;; <summary>Returns the name of the specified field.</summary>
;;; <syntax>$sqlite_field_name ( result, field_index )</syntax>
;;; <param name="result">The result identifier.</param>
;;; <param name="field_index">The ordinal index of the field. The first field has an index of 1.</param>
;;; <returns>The name of the field on success, or <b>$null</b> if there was an error.</returns>
;;; <example>
;;; ; 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
;;; }
;;; </example>
;;; <seealso>sqlite_query</seealso>
alias sqlite_field_name {
return $dll($sqlite_dll, msqlite_field_name, $gettok($1, 1, 32) $2)
}
;;; <summary>Returns the type of the specified field.</summary>
;;; <syntax>$sqlite_field_type ( result, field_index [, row_index ] )</syntax>
;;; <param name="result">The result identifier.</param>
;;; <param name="field_index">The ordinal index of the field. The first field has an index of 1.</param>
;;; <param name="row_index">The row index to get the type for. Optional; If omitted current row is assumed.</param>
;;; <returns>The type of the field on success, or <b>$null</b> if there was an error.</returns>
;;; <remarks>
;;; The returned type can be one of the following values: <b>$SQLITE_INTEGER</b>, <b>$SQLITE_FLOAT</b>, <b>$SQLITE_TEXT</b>, <b>$SQLITE_BLOB</b> and <b>$SQLITE_NULL</b>.
;;;
;;; 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 <a href="http://www.sqlite.org/datatype3.html" target="_blank">Datatypes In SQLite Version 3</a>.
;;; </remarks>
;;; <seealso>sqlite_query</seealso>
alias sqlite_field_type {
return $dll($sqlite_dll, msqlite_field_type, $gettok($1, 1, 32) $2)
}
;;; <summary>Fetches the current row from a result and then advances to the next row.</summary>
;;; <syntax>$sqlite_fetch_row ( result, hash_table [, result_type ] )</syntax>
;;; <param name="result">The result identifier.</param>
;;; <param name="hash_table">The name of the hash table to where to store the row data.</param>
;;; <param name="result_type">The type of the result. Optional, see remarks for more info.</param>
;;; <returns><b>1</b> on success; Otherwise <b>0</b> if there are no more rows available, or <b>$null</b> if there was an error.</returns>
;;; <remarks>
;;; $sqlite_fetch_row fetches the next row from the <i>result</i> and stores the data in <i>hash_table</i>.
;;; If the hash table doesn't exist, it will be created; Otherwise it will be cleared before new data is stored.
;;;
;;; <i>result_type</i> specifies how the hash table is created, it can be one of the following: <b>$SQLITE_NUM</b>, <b>$SQLITE_ASSOC</b> or <b>$SQLITE_BOTH</b>. $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.
;;; </remarks>
;;; <example>
;;; ; 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
;;; }
;;; </example>
;;; <seealso>sqlite_query</seealso>
;;; <seealso>sqlite_fetch_single</seealso>
alias sqlite_fetch_row {
return $dll($sqlite_dll, msqlite_fetch_row, $gettok($1, 1, 32) $gettok($2, 1, 32) $3)
}
;;; <summary>Fetches and returns the first column of the current row from a result and then advances to the next row.</summary>
;;; <syntax>$sqlite_fetch_single ( result [, &binvar ] )</syntax>
;;; <param name="result">The result identifier.</param>
;;; <param name="binvar">The name of the binary variable to assign binary data to. Optional.</param>
;;; <returns>The value of the first column of the fetched row if <i>&binvar</i> isn't specified, otherwise the size of the binary variable on success, <b>$null</b> if there are no more rows, or if there was an error.</returns>
;;; <remarks>
;;; If <i>&binvar</i> 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 <i>&binvar</i> as sequential ascii values. If <i>&binvar</i> 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 <a href="binary.html">Handling Binary Data</a>
;;;
;;; In case of <b>$null</b> is returned it can mean three different things:
;;; <b>1.</b> The returned value from SQLite database is NULL.
;;; <b>2.</b> There are no more rows available.
;;; <b>3.</b> There was an error.
;;;
;;; To determine the cause of $null, examine the <b>%sqlite_errno</b> variable after calling $sqlite_fetch_single. The returned value can be one of the following:
;;; <b>1.</b> <b>$SQLITE_OK</b> if there was no error.
;;; <b>2.</b> <b>$SQLITE_NOMOREROWS</b> if there are no more rows available.
;;; <b>3.</b> Some other of the <a href="errors.html">Error Codes</a> if there was an error.
;;; </remarks>
;;; <example>
;;; ; 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
;;; }
;;; </example>
;;; <seealso>sqlite_query</seealso>
;;; <seealso>sqlite_fetch_row</seealso>
;;; <seealso>sqlite_fetch_field</seealso>
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
}
}
;;; <summary>Fetches and returns the specified column of the current row from a result and then advances to the next row.</summary>
;;; <syntax>$sqlite_fetch_field ( result, field [, &binvar ] ) [ .name ]</syntax>
;;; <param name="result">The result identifier.</param>
;;; <param name="field">The field index or name. See remarks for details.</param>
;;; <param name="binvar">The name of the binary variable to assign binary data to. Optional.</param>
;;; <prop name="name">Forces field to be treated as name.</prop>
;;; <returns>The value of the specified column of the fetched row if <i>&binvar</i> isn't specified, otherwise the size of the binary variable on success, <b>$null</b> if there are no more rows, or if there was an error.</returns>
;;; <remarks>
;;; $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 <i>field</i> 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 <b>.name</b> property to force the field to be treated as column name even if it's a number.
;;;
;;; See <a href="sqlite_fetch_single.html">$sqlite_fetch_single</a> for more details.
;;; </remarks>
;;; <example>
;;; ; 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
;;; </example>
;;; <seealso>sqlite_query</seealso>
;;; <seealso>sqlite_fetch_row</seealso>
;;; <seealso>sqlite_fetch_single</seealso>
;;; <seealso>sqlite_result</seealso>
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
}
}
;;; <summary>Returns whether or not more rows are available.</summary>
;;; <syntax>$sqlite_has_more ( result )</syntax>
;;; <param name="result">The result identifier.</param>
;;; <returns><b>1</b> if there are more rows available; Otherwise <b>0</b> if there are no more rows available, or <b>$null</b> if there was an error.</returns>
;;; <example>
;;; ; 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
;;; }
;;; </example>
;;; <seealso>sqlite_query</seealso>
;;; <seealso>sqlite_num_rows</seealso>
;;; <seealso>sqlite_next</seealso>
;;; <seealso>sqlite_prev</seealso>
;;; <seealso>sqlite_has_prev</seealso>
alias sqlite_has_more {
return $dll($sqlite_dll, msqlite_has_more, $1)
}
;;; <summary>Returns whether or not a previous row is available.</summary>
;;; <syntax>$sqlite_has_prev ( result )</syntax>
;;; <param name="result">The result identifier.</param>
;;; <returns><b>1</b> if there is a previous available; Otherwise <b>0</b> if there is no previous row available, or <b>$null</b> if there was an error.</returns>
;;; <seealso>sqlite_query</seealso>
;;; <seealso>sqlite_num_rows</seealso>
;;; <seealso>sqlite_prev</seealso>
;;; <seealso>sqlite_next</seealso>
;;; <seealso>sqlite_has_more</seealso>
alias sqlite_has_prev {
return $dll($sqlite_dll, msqlite_has_prev, $1)
}
;;; <summary>Seek to the next row number.</summary>
;;; <syntax>$sqlite_next ( result )</syntax>
;;; <syntax>/sqlite_next result</syntax>
;;; <param name="result">The result identifier.</param>
;;; <returns><b>1</b> on success; Otherwise <b>0</b> if there are no more rows available, or <b>$null</b> if there was an error.</returns>
;;; <seealso>sqlite_query</seealso>
;;; <seealso>sqlite_num_rows</seealso>
;;; <seealso>sqlite_prev</seealso>
;;; <seealso>sqlite_has_more</seealso>
;;; <seealso>sqlite_has_prev</seealso>
alias sqlite_next {
return $dll($sqlite_dll, msqlite_next, $1)
}
;;; <summary>Seek to the previous row number.</summary>
;;; <syntax>$sqlite_prev ( result )</syntax>
;;; <syntax>/sqlite_prev result</syntax>
;;; <param name="result">The result identifier.</param>
;;; <returns><b>1</b> on success; Otherwise <b>0</b> if there is no previous row available, or <b>$null</b> if there was an error.</returns>
;;; <seealso>sqlite_query</seealso>
;;; <seealso>sqlite_num_rows</seealso>
;;; <seealso>sqlite_next</seealso>
;;; <seealso>sqlite_has_more</seealso>
;;; <seealso>sqlite_has_prev</seealso>
alias sqlite_prev {
return $dll($sqlite_dll, msqlite_prev, $1)
}
;;; <summary>Seek to a particular row.</summary>
;;; <syntax>$sqlite_seek ( result, row_index [, seek_type ] )</syntax>
;;; <syntax>/sqlite_seek result row_index [ seek_type ]</syntax>
;;; <param name="result">The result identifier.</param>
;;; <param name="row_index">The row to seek to.</param>
;;; <param name="seek_type">The seek type. Optional, see remarks for more info.</param>
;;; <returns><b>1</b> on success; Otherwise <b>0</b> if the row isn't seekable, or <b>$null</b> if there was an error.</returns>
;;; <remarks>
;;; The optional <i>seek_type</i> parameter specifies the direction and offset of seek and can be one of the following: <b>$SQLITE_BEG</b>, <b>$SQLITE_CUR</b> or <b>$SQLITE_END</b>. $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.
;;; </remarks>
;;; <seealso>sqlite_query</seealso>
;;; <seealso>sqlite_key</seealso>
alias sqlite_seek {
return $dll($sqlite_dll, msqlite_seek, $gettok($1, 1, 32) $2)
}
;;; <summary>Seeks to the first row.</summary>
;;; <syntax>$sqlite_rewind ( result )</syntax>
;;; <syntax>/sqlite_rewind result</syntax>
;;; <param name="result">The result identifier.</param>
;;; <returns><b>1</b> on success; Otherwise <b>0</b> if the result isn't rewindable, or <b>$null</b> if there was an error.</returns>
;;; <remarks>
;;; $sqlite_rewind is equivalent to calling <b>$sqlite_seek</b> with row value of 1 and seek_type of <b>$SQLITE_BEG</b>.
;;; </remarks>
;;; <seealso>sqlite_query</seealso>
;;; <seealso>sqlite_key</seealso>
;;; <seealso>sqlite_rewind</seealso>
alias sqlite_rewind {
return $sqlite_seek($1, 1, $SQLITE_BEG)
}
;;; <summary>Returns the current row number of a result.</summary>
;;; <syntax>$sqlite_key ( result )</syntax>
;;; <param name="result">The result identifier.</param>
;;; <returns>The current row number on success, or <b>$null</b> if there was an error.</returns>
;;; <remarks>
;;; $sqlite_rewind is equivalent to calling <b>$sqlite_seek</b> with row value of 1 and seek_type of <b>$SQLITE_BEG</b>.
;;; </remarks>
;;; <seealso>sqlite_query</seealso>
;;; <seealso>sqlite_key</seealso>
;;; <seealso>sqlite_rewind</seealso>
alias sqlite_key {
return $dll($sqlite_dll, msqlite_key, $1)
}
;;; <summary>Fetches the current row from a result.</summary>
;;; <syntax>$sqlite_current ( result, hash_table [, result_type ] )</syntax>
;;; <param name="result">The result identifier.</param>
;;; <param name="hash_table">The name of the hash table to where to store the row data.</param>
;;; <param name="result_type">The type of the result. Optional, see remarks for more info.</param>
;;; <returns><b>1</b> on success; Otherwise <b>0</b> if the current row position is beyond final row, or <b>$null</b> if there was an error.</returns>
;;; <remarks>
;;; $sqlite_current is identical to <a href="sqlite_fetch_row.html">$sqlite_fetch_row</a> except it doesn't advance to the next row.
;;; </remarks>
;;; <seealso>sqlite_fetch_row</seealso>
alias sqlite_current {
return $dll($sqlite_dll, msqlite_current, $gettok($1, 1, 32) $gettok($2, 1, 32) $3)
}
;;; <summary>Fetches and returns the specified column of the current row from a result.</summary>
;;; <syntax>$sqlite_result ( result, field [, &binvar ] ) [ .name ]</syntax>
;;; <param name="result">The result identifier.</param>
;;; <param name="field">The field index or name. See remarks for details.</param>
;;; <param name="binvar">The name of the binary variable to assign binary data to. Optional.</param>
;;; <prop name="name">Forces field to be treated as name.</prop>
;;; <returns>The value of the specified column of the fetched row if <i>&binvar</i> isn't specified, otherwise the size of the binary variable on success, <b>$null</b> if there are no more rows, or if there was an error.</returns>
;;; <remarks>
;;; $sqlite_result is identical to <a href="sqlite_fetch_field.html">$sqlite_fetch_field</a> except it doesn't advance to the next row.
;;; </remarks>
;;; <seealso>sqlite_fetch_field</seealso>
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
}
}
;;; <summary>Sets the busy timeout duration or disables busy handlers.</summary>
;;; <syntax>$sqlite_busy_timeout ( conn, milliseconds )</syntax>
;;; <syntax>/sqlite_busy_timeout conn milliseconds</syntax>
;;; <param name="conn">The connection identifier.</param>
;;; <param name="milliseconds">The number of milliseconds.</param>
;;; <returns><b>1</b> on success, or <b>$null</b> if there was an error.</returns>
;;; <remarks>
;;; Set the maximum time, in milliseconds, that SQLite will wait for a <i>conn</i> to become ready for use.
;;; Use <b>0</b> for <i>milliseconds</i> to disable busy handlers.
;;;
;;; You can set the default busy timeout in the mSQLite configuration file, see <a href="conf.html">Configuring mIRC SQLite</a> 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 <i>conn</i> is specified.
;;; </remarks>
alias sqlite_busy_timeout {
return $dll($sqlite_dll, msqlite_busy_timeout, $gettok($1, 1, 32) $2)
}
;;; <summary>Register an user defined function.</summary>
;;; <syntax>$sqlite_create_function ( conn, func_name, func_alias [, num_args [, prop ] ] )</syntax>
;;; <syntax>/sqlite_create_function conn func_name func_alias [ num_args [ prop ] ]</syntax>
;;; <param name="conn">The connection identifier.</param>
;;; <param name="func_name">The SQL function name to register.</param>
;;; <param name="func_alias">The mIRC alias to register as function.</param>
;;; <param name="num_args">The number of arguments the function accepts. Optional.</param>
;;; <param name="prop">A custom property you want to use with alias name. Optional.</param>
;;; <returns><b>1</b> if the function was registered successfully, or <b>$null</b> if there was an error.</returns>
;;; <remarks>
;;; $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 <i>num_args</i> 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 <i>num_args</i>, the query will raise an SQL error.
;;; The default, <b>-1</b>, 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 <a href="functions.html">User Defined Functions</a>.
;;; </remarks>
;;; <example>
;;; ; 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)
;;; </example>
;;; <seealso href="functions.html">User Defined Functions</seealso>
;;; <seealso href="limits.html">Function Limitations</seealso>
;;; <seealso>sqlite_create_aggregate</seealso>
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)
}
;;; <summary>Register an user defined aggregate.</summary>
;;; <syntax>$sqlite_create_aggregate ( conn, func_name, step_alias, finalize_alias [, num_args [, step_prop [, finalize_prop ] ] ] )</syntax>
;;; <syntax>/sqlite_create_aggregate conn func_name step_alias finalize_alias [ num_args [ step_prop [ finalize_prop ] ] ]</syntax>
;;; <param name="conn">The connection identifier.</param>
;;; <param name="func_name">The SQL function name to register.</param>
;;; <param name="step_alias">The mIRC alias to register as step function.</param>
;;; <param name="finalize_alias">The mIRC alias to register as finalize function.</param>
;;; <param name="num_args">The number of arguments the function accepts. Optional.</param>
;;; <param name="step_prop">A custom property you want to use for step alias. Optional.</param>
;;; <param name="finalize_prop">A custom property you want to use for finalize alias. Optional.</param>
;;; <returns><b>1</b> if the function was registered successfully, or <b>$null</b> if there was an error.</returns>
;;; <remarks>
;;; $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:
;;; <i>step_alias</i> is called for each row in a group, it is used to accumulate data.
;;; <i>finalize_alias</i> is called after all the rows are processed, it is used to return the result to the query.
;;;
;;; <i>step_alias</i> and <i>finalize_alias</i> always receive an aggregate context as first parameter.
;;; The <i>step_alias</i> can use the aggregate context to accumulate data. It should return the aggregate context at end.
;;; When <i>finalize_alias</i> 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 <i>num_args</i> 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 <i>num_args</i>, the query will raise an SQL error.
;;; The default, <b>-1</b>, 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 <a href="functions.html">User Defined Functions</a> and <a href="aggregates.html">User Defined Aggregates</a>.
;;; </remarks>
;;; <example>
;;; ; 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
;;; </example>
;;; <seealso href="functions.html">User Defined Functions</seealso>
;;; <seealso href="aggregates.html">User Defined Aggregates</seealso>
;;; <seealso href="limits.html">Function Limitations</seealso>
;;; <seealso>sqlite_create_function</seealso>
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)
}
;;; <summary>Allows user defined functions to signal an error.</summary>
;;; <syntax>$sqlite_signal_error ( error )</syntax>
;;; <syntax>/sqlite_signal_error error</syntax>
;;; <param name="error">The error to be signaled.</param>
;;; <returns><b>1</b> on success, or <b>$null</b> if there was an error.</returns>
;;; <remarks>
;;; 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.
;;; </remarks>
;;; <example>
;;; ; 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
;;; }
;;; </example>
;;; <example>
;;; ; 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!
;;; }
;;; </example>
;;; <seealso href="functions.html">User Defined Functions</seealso>
;;; <seealso href="aggregates.html">User Defined Aggregates</seealso>
;;; <seealso href="limits.html">Function Limitations</seealso>
alias sqlite_signal_error {
return $dll($sqlite_dll, msqlite_signal_error, $1-)
}
;;; <summary>Add single quotes around text.</summary>
;;; <syntax>$sqlite_qt ( text )</syntax>
;;; <param name="text">The text to be single quoted.</param>
;;; <returns>The single quoted text.</returns>
;;; <remarks>
;;; 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 <i>$qt</i> identifier, the reason is explained in <a href="faq.html">FAQ</a>, under the question "Should I use single or double quotes for string literals?"
;;; </remarks>
;;; <example>
;;; ; 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 )
;;; </example>
;;; <seealso>sqlite_escape_string</seealso>
alias sqlite_qt {
return $+(',$1-,')
}
;;; <summary>Retrieves meta information about a specific column.</summary>
;;; <syntax>$sqlite_field_metadata ( conn [, database ], table, column, htable )</syntax>
;;; <param name="conn">The connection identifier.</param>
;;; <param name="database">The database name where table exists. Optional, see remarks for details.</param>
;;; <param name="table">The table name where column exists.</param>
;;; <param name="column">The column to get meta information about.</param>
;;; <param name="htable">The hash table to assign results to.</param>
;;; <returns><b>1</b> on success, or <b>$null</b> if there was an error.</returns>
;;; <remarks>
;;; This identifier fetches meta information about a specific column, and assigns the results to <i>htable</i>.
;;; The hash table will have the following items set:
;;; [pre]
;;; <b>dattype</b> The declared data type, eg. <i>INTEGER</i>
;;; <b>collseq</b> The collation sequence name, eg. <i>BINARY</i>
;;; <b>notnull</b> 1 if <i>NOT NULL</i> constraint exists, otherwise 0
;;; <b>primkey</b> 1 if column is part of <i>PRIMARY KEY</i>, otherwise 0
;;; <b>autoinc</b> 1 if column is <i>AUTOINCREMENT</i>, otherwise 0
;;; [/pre]
;;; The <i>database</i> argument is optional. If omitted all attached databases in <i>conn</i> will be searched for the specified table.
;;; </remarks>
alias sqlite_field_metadata {
return $dll($sqlite_dll, msqlite_field_metadata, $1-)
}
;;; <summary>Loads a dynamic extension.</summary>
;;; <syntax>$sqlite_load_extension ( conn, filename [, entrypoint ] )</syntax>
;;; <syntax>/sqlite_load_extension conn filename [ entrypoint ]</syntax>
;;; <param name="conn">The connection identifier.</param>
;;; <param name="filename">The filename of the extension.</param>
;;; <param name="entrypoint">The entrypoint of the extension Optional, see remarks for details.</param>
;;; <returns><b>1</b> on success, or <b>$null</b> if there was an error.</returns>
;;; <remarks>
;;; This function loads a dynamic extension <i>filename</i>. Dynamic extensions are useful for creating efficient custom functions and <a href="http://www.sqlite.org/cvstrac/wiki?p=VirtualTables" target="_blank">virtual table modules</a>.
;;; The optional parameter <i>entrypoint</i> specifies the initialization function within the extension. If omitted, the entrypoint will default to <b>sqlite3_extension_init</b>, which is recommended. See SQLite Wiki's <a href="http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions" target="_blank">Loadable Extensions</a> article for details and more information.
;;;
;;; <b>NOTE.</b> 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.
;;; </remarks>
alias sqlite_load_extension {
return $dll($sqlite_dll, msqlite_load_extension, $gettok($1, 1, 32) $gettok($2, 1, 32) $3)
}
;;; <summary>Checks if a database connection is valid.</summary>
;;; <syntax>$sqlite_is_valid_conn ( conn )</syntax>
;;; <param name="conn">The connection identifier.</param>
;;; <returns><b>1</b> if <i>conn</i> is a valid connection, <b>0</b> if it's invalid, or <b>$null</b> if there was an error.</returns>
;;; <remarks>
;;; It is usually ok to ignore if $sqlite_is_valid_conn returns $null because the only case an error is returned is when <i>conn</i> isn't specified.
;;; </remarks>
;;; <seealso>sqlite_is_valid_result</seealso>
;;; <seealso>sqlite_is_memory</seealso>
alias sqlite_is_valid_conn {
return $dll($sqlite_dll, msqlite_is_valid_conn, $1)
}
;;; <summary>Checks if a result is valid.</summary>
;;; <syntax>$sqlite_is_valid_result ( result )</syntax>
;;; <param name="result">The result identifier.</param>
;;; <returns><b>1</b> if <i>conn</i> is a valid result, <b>0</b> if it's invalid, or <b>$null</b> if there was an error.</returns>
;;; <remarks>
;;; It is usually ok to ignore if $sqlite_is_valid_result returns $null because the only case an error is returned is when <i>result</i> isn't specified.
;;; </remarks>
;;; <seealso>sqlite_is_valid_conn</seealso>
;;; <seealso>sqlite_is_memory</seealso>
alias sqlite_is_valid_result {
return $dll($sqlite_dll, msqlite_is_valid_result, $1)
}
;;; <summary>Checks whether database is memory database or not.</summary>
;;; <syntax>$sqlite_is_memory ( conn )</syntax>
;;; <param name="conn">The connection identifier.</param>
;;; <returns><b>1</b> if <i>conn</i> is a memory database, <b>0</b> if it's file database, or <b>$null</b> if there was an error.</returns>
;;; <seealso>sqlite_is_valid_conn</seealso>
;;; <seealso>sqlite_is_valid_result</seealso>
alias sqlite_is_memory {
return $dll($sqlite_dll, msqlite_is_memory, $1)
}
;;; <summary>Writes a memory database to a file.</summary>
;;; <syntax>$sqlite_write_to_file ( conn, file )</syntax>
;;; <syntax>/sqlite_write_to_file conn file </syntax>
;;; <param name="conn">The connection identifier of a memory database.</param>
;;; <param name="file">The filename to save the memory database to.</param>
;;; <returns><b>1</b> on success, or <b>$null</b> if there was an error.</returns>
;;; <remarks>
;;; 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.
;;; </remarks>
alias sqlite_write_to_file {
return $dll($sqlite_dll, msqlite_write_to_file, $gettok($1, 1, 32) $2)
}
;;; <summary>Reloads the configuration file.</summary>
;;; <syntax>/sqlite_reload</syntax>
;;; <remarks>
;;; This command allows you to reload the configuration file in case you have made changes but don't want to reload the whole DLL.
;;; </remarks>
alias sqlite_reload {
noop $dll($sqlite_dll, msqlite_reload, $null)
}
;;; <summary>Registers an authorizer for a connection.</summary>
;;; <syntax>$sqlite_set_authorizer ( conn, authorizer_alias [, prop ] )</syntax>
;;; <syntax>/sqlite_set_authorizer conn authorizer_alias [ prop ]</syntax>
;;; <param name="conn">The connection identifier.</param>
;;; <param name="authorizer_alias">The mIRC alias to register as an authorizer.</param>
;;; <param name="prop">A custom property you want to use with alias name. Optional.</param>
;;; <returns><b>1</b> if the authorizer was registered successfully, or <b>$null</b> if there was an error.</returns>
;;; <remarks>
;;; 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 <b>$null</b> for <i>authorizer_alias</i>.
;;;
;;; The authorizer should return <b>$SQLITE_OK</b> if the current action should be allowed, <b>$SQLITE_DENY</b> if it should be denied (generates an error) or <b>$SQLITE_IGNORE</b> 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 <b>$null</b> 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).
;;; <pre style="font-weight: bold;">Type Value 2nd Arg 3rd Arg</pre><pre>$SQLITE_CREATE_INDEX 1 Index Name Table Name
;;; $SQLITE_CREATE_TABLE 2 Table Name <i>$null</i>
;;; $SQLITE_CREATE_TEMP_INDEX 3 Index Name Table Name
;;; $SQLITE_CREATE_TEMP_TABLE 4 Table Name <i>$null</i>
;;; $SQLITE_CREATE_TEMP_TRIGGER 5 Trigger Name Table name
;;; $SQLITE_CREATE_TEMP_VIEW 6 View Name <i>$null</i>
;;; $SQLITE_CREATE_TRIGGER 7 Trigger name Table Name
;;; $SQLITE_CREATE_VIEW 8 View Name <i>$null</i>
;;; $SQLITE_DELETE 9 Table Name <i>$null</i>
;;; $SQLITE_DROP_INDEX 10 Index Name Table Name
;;; $SQLITE_DROP_TABLE 11 Table Name <i>$null</i>
;;; $SQLITE_DROP_TEMP_INDEX 12 Index Name Table Name
;;; $SQLITE_DROP_TEMP_TABLE 13 Table Name <i>$null</i>
;;; $SQLITE_DROP_TEMP_TRIGGER 14 Trigger Name Table Name
;;; $SQLITE_DROP_TEMP_VIEW 15 View Name <i>$null</i>
;;; $SQLITE_DROP_TRIGGER 16 Trigger Name Table Name
;;; $SQLITE_DROP_VIEW 17 View Name <i>$null</i>
;;; $SQLITE_INSERT 18 Table Name <i>$null</i>
;;; $SQLITE_PRAGMA 19 Pragma Name 1st Arg or <i>$null</i>
;;; $SQLITE_READ 20 Table Name Column Name
;;; $SQLITE_SELECT 21 <i>$null</i> <i>$null</i>
;;; $SQLITE_TRANSACTION 22 <i>$null</i> <i>$null</i>
;;; $SQLITE_UPDATE 23 Table Name Column Name
;;; $SQLITE_ATTACH 24 Filename <i>$null</i>
;;; $SQLITE_DETACH 25 Database Name <i>$null</i>
;;; $SQLITE_ALTER_TABLE 26 Database Name Table Name
;;; $SQLITE_REINDEX 27 Index Name <i>$null</i>
;;; $SQLITE_ANALYZE 28 Table Name <i>$null</i>
;;; $SQLITE_CREATE_VTABLE 29 Table Name Module Name
;;; $SQLITE_DROP_VTABLE 30 Table Name Module Name
;;; $SQLITE_FUNCTION 31 Function Name <i>$null</i></pre>
;;; </remarks>
;;; <example>
;;; ; 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
;;; </example>
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)
}
}