A Complete 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
  }
}