User Defined Functions

mIRC SQLite allows the users to register two kinds of functions, regular functions and aggregates. This section explains the details of both, and shows how to implement them.
NOTE. User defined functions are not limited to custom functions. You can override the core functions and aggregates by registering new functions with their names.

You can use sqlite_create_function to register a mIRC alias as a SQL function. Please look at the reference to see an explanation of the parameters.
Example of an user defined function:

Code ; 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) }

You would then register it with mSQLite:

Code sqlite_create_function %db md5_left md5_left 2

After you have registered it with mSQLite you can use it in queries:

Code ; Outputs 5ac749fb var %sql = SELECT md5_left('some string', 8) var %request = $sqlite_query(%db, %sql) if (%request) {   echo -a $sqlite_fetch_single(%request) }

But what if you wanted to make the length parameter optional? There are two ways for this.

Code ; Implementing optional arguments by registering the ; function twice with different amount of arguments alias md5_left {   ; Default length is 8   var %str = $1, %len = $iif($0 == 1, 8, $2)   return $left($md5(%str), %len) } ; Then register it twice with different amount of args sqlite_create_function %db md5_left md5_left 1 sqlite_create_function %db md5_left md5_left 2

Or you can check the number of arguments yourself and signal an error with sqlite_signal_error:

Code ; Implementing optional arguments by registering ; the function with any amount of arguments alias md5_left {   ; Make sure there are only 1 or 2 arguments, otherwise signal an error   if ($0 !isnum 1-2) {     return $sqlite_signal_error(wrong number of arguments to function md5_left)   }   var %str = $1, %len = $iif($0 == 1, 8, $2)   return $left($md5(%str), %len) } ; Then register it with any number of arguments sqlite_create_function %db md5_left md5_left -1

You can also take advantage of mIRC's built-in identifiers when registering user defined functions. For example you could implement base_convert as following:

Code ; convert_base(n, from, to) calls $base(n, from, to) sqlite_create_function %db base_convert base 3

Finally, here's an example of using custom property in an user defined function:

Code ; sin_r calls $sin(alpha) using radians sqlite_create sin_r sin 1 ; sin_d calls $sin(alpha).deg using degrees sqlite_create sin_d sin 1 deg

This a complete example of the above md5_left user defined function. The example also adds more error checking in the user defined function itself.

Example /* ** mIRC SQLite Demonstration Script **              MD5_LEFT Function ** -------------------------------- ** Save as sqlite_function.mrc and do ** /load -rs sqlite_function.mrc ** ** This script demonstrates the user ** defined functions of the SQLite. ** ** Usage: **  /pass_gen hash [ len ] ** Try with: **  /pass_gen **  /pass_gen s9j331almva **  /pass_gen +sfa9ufa?sa 8 **  /pass_gen fafsafa35os -1 ** ** The /pass_gen will generate a simple ** password with md5. The len argument ** is optional, and if omitted 8 is assumed. */ alias pass_gen {   var %db = $sqlite_open(empty.db)   if (!%db) {     echo 4 -a Error: %sqlite_errstr     return   }   var %hash = $sqlite_escape_string(%hash), %len = $2   ; Register the function with 1 or 2 args   sqlite_create_function %db md5_left md5_left 1   sqlite_create_function %db md5_left md5_left 2   ; Do query   if (%len !isnum) {     set %sql SELECT md5_left(' $+ %hash $+ ')   }   else {     set %sql SELECT md5_left(' $+ %hash $+ ', %len )   }   var %request = $sqlite_query(%db, %sql)   if (%request) {     var %pass = $sqlite_fetch_single(%request)     echo -a Generated password: %pass     sqlite_free %request   }   else {     echo 4 -a Error: %sqlite_errstr   }   sqlite_close %db } ; User defined function alias md5_left {   ; Make sure that has isn't NULL   var %str = $1   if (%str == $null) {     var %error = empty hash in md5_left()     return $sqlite_signal_error(%error)   }   ; See if len was specified and that it's valid   var %len = 8   if ($0 == 2) {     %len = $2     if (%len !isnum 1-32) {       var %error = invalid pass length in md5_left()       return $sqlite_signal_error(%error)     }   }   ; Return the generated pass   return $left($md5(%str), %len) }