A Complete Example

mIRC SQLite

A Complete Example
The following example demonstrates few common features and functions of mSQLite. The example is commented throughly, so it should be easy to follow.
Please read the Handling Errors and Writing Queries How To's for a more complete understanding and details.
Example
/*
** mIRC SQLite Demonstration Script
**              A greeting script
** --------------------------------
** Save as sqlite_example.mrc and do
** /load -rs sqlite_example.mrc
**
** This script demonstrates some
** features of the SQLite.
**
** Usage:
**  /greet add  nick   greet
**  /greet del  [nick]
**  /greet list [nick]
**  /greet find greet
**
** For del and list nick can be a wildcard.
** For find greet can be a wildcard.
*/


/*
** On start open connection and make sure the table exists
** You do not need to close the db connection explicitly
** mSQLite will close it when the mIRC exits
*/

on *:START:{
  set %greet_db $sqlite_open(greet.db)
  if (!%greet_db) {
    echo 4 -a Error: %sqlite_errstr
    return
  }
  sqlite_exec %greet_db CREATE TABLE IF NOT EXISTS greets (nick UNIQUE, greet)
}

/*
** When someone joins a channel, check if he has
** a greet set and display the greet if one exists
*/

on *:JOIN:#:{
  ; Make sure we are connected to db
  if (!%greet_db) { return }

  ; Check if the nick has a greet
  var %safe_nick = $sqlite_escape_string($nick)
  var %sql = SELECT greet FROM greets WHERE nick = ' $+ %safe_nick $+ '
  var %request = $sqlite_query(%greet_db, %sql)
  if (!%request) {
    echo 4 -a Error: %sqlite_errstr
    return
  }

  ; If the nick has a greet, display it
  if ($sqlite_num_rows(%request)) {
    var %greet = $sqlite_fetch_single(%request)
    msg $chan $nick $+ : %greet
  }

  ; Free the result
  sqlite_free %request
}

/*
** Greet alias
*/

alias greet {
  ; Make sure we are connected to db
  if (!%greet_db) {
    echo 4 -a Error: Not connected to greet.db
    return
  }

  ; OK! We're connected!
  var %cmd = $1
  if (%cmd != add && %cmd != del && %cmd != list && %cmd != find) {
    echo 4 -a Error: Unknown action.
    return
  }

  ; Do action
  if (%cmd == add) {
    ; Add greet
    var %nick = $2, %greet = $3-
    if (!%nick || !%greet) {
      echo 4 -a Error: Invalid arguments.
      return
    }

    ; Escape nick and greet and then execute query
    var %safe_nick = $sqlite_escape_string(%nick), %safe_greet = $sqlite_escape_string(%greet)
    sqlite_exec %greet_db REPLACE INTO greets (nick, greet) VALUES (' $+ %safe_nick $+ ', ' $+ %safe_greet $+ ')
    echo 3 -a Greet of %nick set to %greet
  }
  elseif (%cmd == del) {
    ; Delete greet
    var %nick = $2
    if (!%nick) {
      ; Make sure you really want to delete everything
      var %del_all = $?!="You're about delete all greets in the database. $crlf $+ Do you want to continue?"
      if (!%del_all) { return }
      %nick = *
    }

    ; Replace wildcard * with % and escape nick and finally execute query
    var %safe_nick = $replace($sqlite_escape_string(%nick),*,%,?,_)
    sqlite_exec %greet_db DELETE FROM greets WHERE nick LIKE ' $+ %safe_nick $+ '
    echo 3 -a $sqlite_changes(%db) greets deleted.
  }
  elseif (%cmd == list) {
    ; List greets
    var %nick = $2
    if (!%nick) { %nick = * }

    ; Replace wildcard * with % and escape nick
    var %safe_nick = $replace($sqlite_escape_string(%nick),*,%,?,_)

    ; Construct query and execute it
    var %sql = SELECT * FROM greets WHERE nick LIKE ' $+ %safe_nick $+ '
    var %request = $sqlite_query(%greet_db, %sql)
    if (!%request) {
      echo 4 -a Error: %sqlite_errstr
      return
    }

    ; List the greets
    echo -a -
    echo -a Total Greets Found: $sqlite_num_rows(%request)
    echo -a -
    ; For each row display the nick and greet
    while ($sqlite_fetch_row(%request, row)) {
      echo -a $hget(row, nick) - $hget(row, greet)
    }
    echo -a -

    ; Free the result
    sqlite_free %request
  }
  else {
    ; Find greets
    var %greet = $2-
    if (!%greet) {
      echo 4 -a Error: Invalid arguments.
      return
    }

    ; We want to search for text even if it's in middle of the quote
    %greet = $+(*,%greet,*)

    ; Replace wildcard * with % and escape greet
    var %safe_greet = $replace($sqlite_escape_string(%greet),*,%,?,_)

    ; Find text from greets
    var %sql = SELECT * FROM greets WHERE greet LIKE ' $+ %safe_greet $+ '
    var %request = $sqlite_query(%greet_db, %sql)
    if (!%request) {
      echo 4 -a Error: %sqlite_errstr
      return
    }

    ; List the greets
    echo -a -
    echo -a Total Greets Found: $sqlite_num_rows(%request)
    echo -a -
    ; For each row display the nick and greet
    while ($sqlite_fetch_row(%request, row)) {
      echo -a $hget(row, nick) - $hget(row, greet)
    }
    echo -a -

    ; Free the result
    sqlite_free %request
  }
}