MODULES:
- Seen System - http://www.hawkee.com/snippet/8954/
- Stats System - http://www.hawkee.com/snippet/8958/
- Live Status - http://www.hawkee.com/snippet/8962/
- Channel Peak - http://www.hawkee.com/snippet/9025/
- Human Verification - http://www.hawkee.com/snippet/9042/
(search everything, including 3rd party) http://www.hawkee.com/mirc/snippets/search/MySQL+MBC/

DOCUMENTATION:
[] - optional
$m.sqlb() - wraps text into `these`, which is useful in mysql queries
$m.sqle() - escapes string
$m.sqlq() - wraps text into 'quotes', which is useful in mysql queries
$m.dbres(field,database,where,value[,where2,value2]) - returns db result
$m.dbdel(database,where,value) - deletes entry from database
$m.user(field,id/username,value) - returns user information from `users` database
$m.logged(nickname) - returns user id if specified nickname is logged in
- 1.00 STABLE
$m.setting(setting) - returns setting value from `general` database
m.setting setting value - sets setting value in `general` database
$m.encrypt - variable that encrypts sensitive data (must be static, when active users exists)
- 1.12 STABLE
m.modules Module Name|!command|admin(int0/1)

COMMANDS:
!help - no description needed here
!register username password - create an account
!login username password - log in to account
!logout - log out from an account
!profile /nickname/ - profile info about specified nickname or yourself if not specified
!settings password|signature - sets your profile settings
!admin debug|restart|sesdel - administration options (by default level 5+)

* /something/ - optional setting
* Note: you can change command prefix in local $command aliases

REQUIREMENTS:
- http://reko.tiira.net/mmysql/ (mIRC MySQL by Reko Tiira)
- MySQL database with structure that's below.


CHANGELOG:
0.13 -> 0.14 (307 lines)
- Added delete option for message command.
- Added review option for message command.
- Added unread message checking upon login.
- Fixed hfree error in message reading.
- Added view unread option for message command.
- Commented (disabled) game profile (unused) notice in profile command.
0.14 -> 1.00 STABLE (328 lines)
- Added m.setting aliases.
- Added m.encrypt aliases.
- Two new databases `general` and `channels`.
- Updated m.sync aliases with general and channel information syncing.
- Updated register and login commands, encryption supported.
- Removed AUTO INCREMENT and updated CHARSET information in tables.
1.00 STABLE -> 1.01 STABLE (328 lines)
- Made global aliases from some local aliases, for module system.
- Commented out channels m.setting from m.sync, can be retrieved using COUNT(*) in MySQL
1.01 STABLE -> 1.02 STABLE (328 lines)
- Updated $m.dbres aliases, now accepting extra WHERE statement. (Check documentation)
1.01 STABLE -> 1.12 STABLE (338 lines)
- Added m.modules aliases for retrieving active modules.
- Updated m.sync command for syncing modules with the core.
1.12 STABLE -> 1.52 STABLE (361 lines)
- Added mysql_ping before syncronising.
- Added new mini-aliases $toggle.
- Reviewed and updated mysql connection related activities and checkings.
- Updated script startup.
- Added mini menu for setting and doing basic things in bot.
1.52 STABLE -> 1.53 STABLE (361 lines)
- Updated line 132, now checking if module has a command.

SHOUTOUTS:
- Of course Reko Tiira for the mIRC MySQL library and code.
- Thanks to TheImrac (Hawkee.com) for reminding me about password encryption. ; - tiny aliases
alias command return $+($chr(33),$1-)
alias bold return $+($chr(2),$1-,$chr(2))
alias uline return $+($chr(31),$1-,$chr(31))
alias quotes return $+($chr(34),$1-,$chr(34))
alias brackets return $+($chr(40),$1-,$chr(41))
alias brackets2 return $+($chr(91),$1-,$chr(93))
alias zero return $iif($1,$1-,0)
alias toggle return $iif($1,0,1)
alias mcomma {
var %a, %b = $regsub($ticks,$1,/\G([+-]?\d+?)(?=(?:\d{3})++(?=\.\d++$|$))/g,\1 $+ $chr(44),%a)
return %a
}

; - mysql related aliases
alias m.sqlb return $+($chr(96),$1-,$chr(96))
alias m.sqle return $mysql_escape_string($1-)
alias m.sqlq return $+($chr(39),$1-,$chr(39))

; - general aliases
alias -l config {
if ($1 = server) return irc.somewhat.org
else if ($1 = nick) return MySQL
else if ($1 = chan) return #mysqlchan
else if ($1 = fullname) return MySQL Bot Core (ver1.52 STABLE)
else if ($1 = network) return Networkname
else if ($1 = identd) return mysql
else if ($1 = email) return pats@neisen.lv
else if ($1 = mysql) {
if ($2 = hostname) return 127.0.0.1
else if ($2 = username) return root
else if ($2 = password) return
else if ($2 = database) return bot
}
}

alias -l m.encrypt return $md5($md5($encode($1-,m)))
alias -l m.amsg { $iif(!$2,return) | msg $1- | notice $1- }
alias -l m.notify {
if ($1 = error) return $bold(Error:)
else if ($1 = syntax) return $bold(Syntax:)
else if ($1 = example) return $bold(Example:)
else if ($1 = success) return $bold(Success:)
else if ($1 = info) return $bold(Information:)
else if ($1 = note) return $bold(Note:)
else if ($1 = login) return Please log in to your account or $command(register) a new one before you can use this command.
else return $bold($1-)
}

alias -l m.group {
if (!$1) || ($1 !isnum) || ($1 < 1) return User
else if ($1 = 1) return Premium
else if ($1 = 2) return VIP
else if ($1 < 5) return Moderator
else if ($1 < 8) return Administrator
else return Main Administrator
}

; - system based aliases
alias -l m.system {
if ($me !ison $config(chan)) join $v2
if ($me != $config(nick)) nick $v2
if (!$mysql_is_valid_connection(%m.sql)) {
mysql_close %m.sql | unset %m.sql
;msg $config(chan) $m.notify(info) Currently system is down... Running in offline mode.
}
$iif(%m.clearall,clearall) | $iif(%m.closemsg,closemsg)
}
alias -l m.sync {
; pinging mysql connection
mysql_ping %m.sql

; general statistic and information updating
m.setting uptime $uptime
m.setting nickname $me
m.setting network $network
;m.setting channels $chan(0)
m.setting lastupdate $ctime

; channel statistic and information updating
var %channels = $chan(0)
while %channels {
var %chan = $chan(%channels)
var %sql = REPLACE INTO `channels` (`channel`,`users`,`regulars`,`voices`,`halfops`,`operators`,`topic`,`mode`) VALUES (?,?,?,?,?,?,?,?)
noop $mysql_query(%m.sql, %sql, %chan, $nick(%chan,0), $nick(%chan,0,r), $nick(%chan,0,v), $nick(%chan,0,h), $nick(%chan,0,o), $m.sqle($chan(%channels).topic), $chan(%channels).mode)
dec %channels
}

; module system syncing
unset %m.commands
unset %m.admin
.signal m.modules
}

alias m.restart { mysql_close %m.sql | exit -rn }
alias m.dbres {
var %sql = SELECT $m.sqlb($1) FROM $m.sqlb($2) WHERE $m.sqlb($3) = ? $iif($6 != $null,AND $m.sqlb($5) = ?)
return $mysql_fetch_single($mysql_query(%m.sql, %sql, $m.sqle($4), $iif($6 != $null,$v1)))
}
alias m.dbdel {
var %sql = DELETE FROM $m.sqlb($1) WHERE $m.sqlb($2) = ?
if ($3) noop $mysql_query(%m.sql, %sql, $m.sqle($3))
}
alias m.user {
var %sql = SELECT $m.sqlb($1) FROM `users` WHERE $iif($2 isnum,id,username) = ?
return $mysql_fetch_single($mysql_query(%m.sql, %sql, $m.sqle($2)))
}
alias m.setting {
if ($2 != $null) {
var %sql = REPLACE INTO `general` (`setting`,`value`) VALUES (?,?)
noop $mysql_query(%m.sql, %sql, $1, $m.sqle($2-))
}
else return $m.dbres(value, general, setting, $1)
}
alias m.logged {
var %sql = SELECT * FROM `sessions` WHERE `nickname` = ?
var %res = $mysql_query(%m.sql, %sql, $m.sqle($1))
noop $mysql_fetch_row(%res, logged) | mysql_free %res
if ($hget(logged, status)) {
var %address = $hget(logged, address), %uid = $hget(logged, uid) | hfree logged
if (%address != $address($1,2)) {
msg $1 $m.notify(info) You've been logged out of the account due address mismatch.
noop $m.dbdel(sessions, nickname, $1) | return $false
}
else return %uid
}
else return $false
}
alias m.modules {
tokenize 124 $1-
if ($3) set %m.admin $addtok(%m.admin,$2,32)
else $iif($2,set %m.commands $addtok(%m.commands,$v1,32))
}

; - raws
raw *:*:{
; successful connection
if ($numeric = 001) {
if ($network == $config(network)) {
if ($me !ison $config(chan)) join $config(chan)
}
}
}

; - events
on *:START:{
; setting defaults
.titlebar $config(fullname) | .fullname $config(fullname)
.identd on $config(identd)
.nick $config(nick) | .anick $+($config(nick),_)

; creating mysql connection
set %m.sql $mysql_connect($config(mysql,hostname), $config(mysql,username), $config(mysql,password))
if (!%m.sql) {
echo -st $m.notify(error) MySQL connection has failed.
echo -st - %mysql_errstr
halt
}
else if ($mysql_select_db(%m.sql, $config(mysql,database))) {
; removing previously active sessions (security reasons)
var %sql = DELETE FROM `sessions` | noop $mysql_query(%m.sql, %sql)
; setting "correct" (better) charset
mysql_set_charset %m.sql utf8
}

; starting connection
server $config(server) -j $config(chan)

; creating timers
.timer 0 120 m.system
.timer 0 $iif(%m.syncdelay isnum,$v1,30) m.sync
}

on *:JOIN:#:{
if ($chan = $config(chan)) {
if ($nick = $me) who $chan
}
}

on *:NICK:{
var %sql = UPDATE `sessions` SET `nickname` = $m.sqlq($m.sqle($newnick)) WHERE `nickname` = $m.sqlq($m.sqle($nick))
noop $mysql_query(%m.sql, %sql)
}

on *:TEXT:*:*:{
if (!%m.sql) halt
var %c = $chan, %n = $nick, %a = $iif(%c,notice,msg) %n
if ($1 == $command(help)) || ($1 == $command(commands)) {
var %uid = $m.logged(%n)
if !%uid {
%a $bold(Commands:) Use $command(register) to create a new account or $command(login) $brackets($uline(in my query)) to log in to your account.
%a $m.notify(note) If you've any problems with your account - you can mail us at $uline($config(email))
}
else {
%a $bold(Commands:) $command(logout) $command(profile) $command(settings) $command(messages) %m.commands $iif($zero($m.user(group,$m.logged(%n))) >= 5,$command(admin) %m.admin)
}
}
else if ($1 == $command(register)) {
if (%c) msg %n $m.notify(error) Sorry, but this command can be used only in query. $iif($3,Please use $uline(different) password now.)
else if ($m.logged(%n)) msg %n $m.notify(info) You're already logged in as $bold($m.user(username, $v1)) $+ .
else {
if (!$3) { msg %n $m.notify(syntax) $command(register) [username] [password] $m.notify(example) $command(register) %n thismahcoolpass }
else if ($2 isnum) { msg %n $m.notify(error) Username cannot be numeric. }
else if ($len($3) < 5) || ($3 isnum) || ($3 == thismahcoolpass) { msg %n $m.notify(error) Sorry, password must be at least 5 symbols long, it cannot be only numeric and it can't be the one in preview. }
else if ($m.dbres(password, users, username, $2) != $null) { msg %n $m.notify(error) Sorry, this username is already registered. }
else {
var %sql = INSERT INTO `users` (`username`,`password`,`registered`,`active`) VALUES (?,?,NOW(),1)
noop $mysql_query(%m.sql, %sql, $m.sqle($strip($2)), $m.sqle($strip($m.encrypt($3))))
msg %n $m.notify(success) You're now registered with username $bold($2) $+ $chr(44) please remember your password and keep it safe.
msg %n $m.notify(info) You can now login using $bold($command(login) $2 $3)
}
}
}
else if ($1 == $command(login)) {
if (%c) msg %n $m.notify(error) Sorry, but this command can be used only in query. $iif($3,Please login ASAP and $uline(change your password) $+ .)
else if ($m.logged(%n)) msg %n $m.notify(info) You're already logged in as $bold($m.user(username, $v1)) $+ .
else {
var %sql = SELECT `password` FROM `users` WHERE `username` = ?
if (!$3) { msg %n $m.notify(syntax) $command(login) [username] [password] $m.notify(example) $command(login) %n thismahcoolpass }
else {
var %password = $m.dbres(password, users, username, $2)
if (%password = $null) msg %n $m.notify(error) Username $quotes($2) is not registered.
else if (%password != $m.encrypt($3)) msg %n $m.notify(error) Incorrect password.
else {
var %uid = $m.user(id,$2)
noop $m.dbdel(sessions, uid, %uid)
var %sql = INSERT INTO `sessions` (`uid`,`status`,`address`,`nickname`) VALUES (?,?,?,?)
noop $mysql_query(%m.sql, %sql, %uid, 1, $address(%n,2), %n)
msg %n $m.notify(info) You're now logged in as $bold($2) $+ .
msg %n $m.notify(note) Your session will expire $uline(only) if your address $brackets($address(%n,2)) changes.

; message module - checking for unread messages
var %sql = SELECT `time` FROM `messages` WHERE `receiver` = ? AND `status` = '0' LIMIT 1
var %res = $mysql_query(%m.sql, %sql, $m.logged(%n))
if ($mysql_fetch_single(%res)) msg %n *** You have new messages, please review them using $bold($command(message) review)
}
}
}
}
else if ($1 == $command(settings)) {
var %uid = $m.logged(%n)
if (!%uid) %a $m.notify(login)
else {
if (!$2) %a $bold(Settings:) password signature
else if ($2 == password) {
if (%c) msg %n $m.notify(error) This command cannot be used in channel.
else if (!$4) msg %n $m.notify(syntax) $command(settings) password $brackets2(current password) $brackets2(new password)
else if ($4 isnum) || ($len($4) < 5) || ($4 == thismahcoolpass) { msg %n $m.notify(error) Sorry, password must be at least 5 symbols long, it cannot be only numeric and it can't be the one in preview. }
else if ($m.dbres(password, users, id, %uid) != $3) { msg %n $m.notify(error) Incorrect current password. }
else {
var %sql = UPDATE users SET `password` = ? WHERE `id` = %uid
noop $mysql_query(%m.sql, %sql, $4)
msg %n $m.notify(info) Your password has been changed successfully.
}
}
else if ($2 == signature) {
if (!$3) || ($len($3) > 100) %a $bold(Signature:) Your personal signature in length of 100 characters.
else {
var %sql = UPDATE `users` SET `signature` = $m.sqlq($m.sqle($strip($3-))) WHERE `id`= $m.sqlq(%uid)
noop $mysql_query(%m.sql, %sql)
%a $m.notify(info) Your signature has been updated.
}
}
}
}
else if ($1 == $command(logout)) {
if (!$m.logged(%n)) %a $m.notify(info) You're not logged in yet.
else {
var %uid = $m.logged(%n)
noop $m.dbdel(sessions, uid, %uid)
%a $m.notify(info) You're no longer logged in as $bold($m.user(username,%uid)) $+ .
}
}
else if ($1 == $command(profile)) {
var %user = $iif($2,$v1,%n), %uid = $m.logged(%user)
%a $brackets2(%user) $bold(Status:) $iif(%uid,Logged in as $m.user(username,$v1) $brackets(ID: %uid) $bold(Registered:) $m.user(registered,%uid) $bold(Group:) $m.group($m.user(group,%uid)),Not logged in yet.)
if %uid {
;%a - $brackets2(Game Profile) $bold(Level:) $zero($m.user(level,%uid)) $bold(Points:) $m.user(points,%uid)
if ($m.user(signature,%uid)) { %a - $brackets2(Signature) $v1 }
if ($m.user(group,$m.logged(%n)) >= 4) {
%a - $brackets2(Admin Info) $bold(Session ID:) $m.dbres(id, sessions, uid, %uid) $bold(Logged Address:) $m.dbres(address, sessions, uid, %uid)
}
}
}
else if ($1 == $command(admin)) {
if (!$m.logged(%n)) %a $m.notify(login)
else if ($zero($m.user(group,$m.logged(%n))) < 5) %a $m.notify(error) Permission denied. Lack of privileges.
else if (!$2) %a $bold(Admin:) debug restart sesdel
else if ($2 == debug) {
var %msql.con = $mysql_is_valid_connection(%m.sql), %msql.ver = $mysql_get_server_info(%m.sql)
%a $bold(MySQL:) $config(mysql,hostname) u: $config(mysql,username) d: $config(mysql,database) $iif(%msql.ver,v: $v1) / status: $iif(%msql.con != 1,not) connected
}
else if ($2 == restart) { %a $m.notify(info) Restart in progress... | .timer 1 1 m.restart }
else if ($2 == sesdel) && ($3 isnum) {
noop $m.dbdel(sessions, id, $3)
%a $m.notify(info) Session with ID $3 has been cleared out.
}
}
else if ($1 == $command(message)) || ($1 == $command(messages)) {
if (!$m.logged(%n)) %a $m.notify(login)
else if ($2 == send) {
if (!$4) %a $m.notify(syntax) $command(message) send [username] [text]
;else if ($3 == %n) %a $m.notify(error) Why?
else if (!$m.user(id,$3)) %a $m.notify(error) Such user as $quotes($3) doesn't exist.
else if ($len($4-) > 120) %a $m.notify(error) Message cannot be longer than 120 symbols.
else {
var %sql = INSERT INTO `messages` (`sender`,`receiver`,`message`,`time`) VALUES (?,?,?,NOW())
noop $mysql_query(%m.sql, %sql, $m.logged(%n), $m.user(id,$3), $m.sqlq($m.sqle($strip($4-))))
%a $m.notify(info) Your message has been successfully sent to $+($3,.)
}
}
else if ($2 == read) || ($2 == view) {
if ($3 !isnum && $3 != unread) %a $m.notify(syntax) $command(message) read [identificator]
else {
var %sql = SELECT * FROM `messages` WHERE `receiver` = ? $iif($3 == unread,AND `status` = '0') ORDER BY `id` ASC $iif($3 != unread,LIMIT $+($calc($3 - 1),$chr(44),1))
var %res = $mysql_query(%m.sql, %sql, $m.logged(%n))
noop $mysql_fetch_row(%res, msg) | mysql_free %res
if ($hget(msg, time)) {
%a $+($bold($m.user(username, $hget(msg, sender))),$chr(58)) $right($left($hget(msg, message),-1),-1)
%a - sent: $hget(msg, time) status: $iif(!$hget(msg, status),un) $+ read -- $command(message) delete $hget(msg, id)
if (!$hget(msg, status)) noop $mysql_query(%m.sql, UPDATE `messages` SET `status` = 1 WHERE `id` = $hget(msg, id))
hfree msg
}
else %a $m.notify(error) $iif($3 == unread,Sorry $+ $chr(44) you have no unread messages.,Inexistant message.)
}
}
else if ($2 == delete) {
if ($3 !isnum) %a $m.notify(syntax) $command(message) delete [identificator]
else {
var %sql = DELETE FROM `messages` WHERE `receiver` = ? AND `id` = ?
var %res = $mysql_query(%m.sql, %sql, $m.logged(%n), $3)
%a $m.notify(info) If existing and yours, message $+($chr(35),$3) removed.
}
}
else if ($2 == review) {
var %sql = SELECT COUNT(*) FROM `messages` WHERE `receiver` = ? AND `status` = '0'
var %count = $zero($mysql_fetch_single($mysql_query(%m.sql, %sql, $m.logged(%n))))
%a You have $iif(%count,$v1,no) unread message $+ $iif(%count > 1,s) $+ $iif(!%count,s) $+ . $iif(%count,View $iif(%count > 1,them,it) using $bold($command(message) view unread))
}
else %a $m.notify(syntax) $command(message) read/send/delete/review - to see your mailbox, use $bold($command(message) review)
}
}

; menu
menu * {
MySQL Bot &Core
.&Perfomance
..$iif(%m.clearall,$style(1)) Automatic Clear&ALL:set %m.clearall $toggle(%m.clearall)
..$iif(%m.closemsg,$style(1)) Automatic Close&MSG:set %m.closemsg $toggle(%m.closemsg)
..&Sync Delay $chr(9) $iif(%m.syncdelay,$v1,30) sec.:set %m.syncdelay $$?="Enter the preferred delay of syncronising between the bot and database in seconds:"
.&Actions
..&Synchronise:m.sync
..Re&start:m.restart
.-
.&Connection Status:echo -at Currently the connection is $bold($iif($mysql_is_valid_connection(%m.sql),valid,invalid))
.&Support:url -na http://www.hawkee.com/profile/6484/
}