icel_mysql.tcl

TCL script that connects to a mysql database and can manipulates queryes. This script needs mysqltcl >= 2.5

Postat de	Copyright	Categorie	Review user	Vizualizari	Data
btc	Marco Ferra	activity	Cod netestat	392	2023-12-25 09:06:34

# $Id: icel_mysql.tcl v 0.04 2004/06/07 21:32:40 $
# Copyright (c) Marco Ferra, 2004
#
# Direct questions to Marco Ferra <mferra@users.sourceforge.net>
# icel: http://icel.sourceforge.net
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307

# usage details:
#
# !! <key> id
# !a <key> <def>
# !d <key> id
# !s <str1> str2 ...
# !h
#
# !a adds.  !d deletes.  !s searches.  !t stats.  !h helps
# id is an integer to get/delete a precise definition

# needs mysqltcl >= 2.5

# sql code (issue: ./ file.sql)

# create table iceldb_data
# (
# id int unsigned not null auto_increment primary key,
# tstp datetime,
# chan varchar(32),
# host varchar(96),
# nick varchar(32),
# unit varchar(32),
# defn varchar(216)
# );

load /usr/lib/mysqltcl-2.50/libmysqltcl2.50.so

set lfile(ver) "0.04"

# limits

# chars per definition
set lfile(chr) 216

# definitions per unit
set lfile(def) 10

# chars per unit
set lfile(uls) 32

# shared (1) or individual (0) databases
set lfile(cdb) 1

# mysql server location
set lfile(msl) "localhost"

# mysql user/pass
set lfile(usr) "username"
set lfile(psd) "password"

# database
set lfile(dbs) "db-name"

# table
set lfile(tbl) "iceldb_data"

# icel's dir

set lfile(dir) "icel"

# essential

set lflag(get) "-"
set lflag(add) "-"
set lflag(del) "-"
set lflag(sch) "-"
set lflag(hlp) "-"
set lflag(ttl) "-"

set ltrig(get) "!!"
set ltrig(add) "!a"
set ltrig(del) "!d"
set ltrig(sch) "!s"
set ltrig(hlp) "!h"
set ltrig(ttl) "!t"

bind pub $lflag(get) $ltrig(get) pub:learn:get
bind pub $lflag(add) $ltrig(add) pub:learn:add
bind pub $lflag(del) $ltrig(del) pub:learn:del
bind pub $lflag(sch) $ltrig(sch) pub:learn:sch
bind pub $lflag(hlp) $ltrig(hlp) pub:learn:hlp
bind pub $lflag(ttl) $ltrig(ttl) pub:learn:ttl

# interfaces

proc pub:learn:add {nick host hand chan text} {
  global lfile

  set pl [regexp -inline -all -- {\S+} $text]
  set hd 0

  if {[catch {mysqlconnect -host $lfile(msl) -user $lfile(usr) -password $lfile(psd) -db $lfile(dbs)} hd]} {
    puthelp "notice $nick :can't open database"
    return 0
  }

  set tsp1 [strftime %F]
  set tsp2 [strftime %T]
  set unit [mysqlescape $hd [lrange $pl 0 0]]
  set defn [mysqlescape $hd [lrange $pl 1 end]]

  if {[string length $defn] == 0} {
    mysqlclose $hd
    puthelp "notice $nick :invalid syntax"
    return 0
  } elseif {[string length $unit] > $lfile(uls)} {
    mysqlclose $hd
    puthelp "notice $nick :invalid key (+ $lfile(uls) chars)"
    return 0
  }

  if {[mysqlsel $hd "select * from $lfile(tbl) where unit = '$unit'"] == $lfile(def)} {
    mysqlclose $hd
    puthelp "notice $nick :table full ($lfile(def)).  not added"
    putlog "icel_mysql $nick on $chan did !a $text (table full)"
    return 0
  }

  if {[string length $defn] > $lfile(chr)} {
    mysqlclose $hd
    puthelp "notice $nick :key too long (+ $lfile(chr) chars)"
    putlog "icel_mysql $nick on $chan did !a $text (key too long)"
    return 0
  }

  mysqlexec $hd "insert into $lfile(tbl) values(null, '$tsp1 $tsp2', '$chan', '$host', '$nick', '$unit', '$defn')"
  mysqlclose $hd

  puthelp "notice $nick :key recorded"
  putlog "icel_mysql $nick on $chan did !a $text (successful)"
}

proc pub:learn:del {nick host hand chan text} {
  global lfile

  set pl [regexp -inline -all -- {\S+} $text]
  set hd 0

  if {[catch {mysqlconnect -host $lfile(msl) -user $lfile(usr) -password $lfile(psd) -db $lfile(dbs)} hd]} {
    puthelp "notice $nick :can't open database"
    return 0
  }

  set unit [mysqlescape $hd [lrange $pl 0 0]]
  set indx [lrange $pl 1 1]

  set id 0

  if {[string length $indx] > 0} {
    if {[string is integer $indx]} {
      set id $indx
    }
  }

  set udx 0

  if {$lfile(cdb) == 1} {
    if {$id == 0} {
      set udx [mysqlsel $hd "select * from $lfile(tbl) where unit = '$unit'"]
    } else {
      set udx [mysqlsel $hd "select * from $lfile(tbl) where unit = '$unit' and id = '$id'"]
    }
  } else {
    if {$id == 0} {
      set udx [mysqlsel $hd "select * from $lfile(tbl) where unit = '$unit' and chan = '$chan'"]
    } else {
      set udx [mysqlsel $hd "select * from $lfile(tbl) where unit = '$unit' and id = '$id' and chan = '$chan'"]
    }
  }

  if {$udx == 0} {
    mysqlclose $hd
    puthelp "notice $nick :key not found"
    putlog "icel_mysql $nick on $chan did !d $unit $id (not found)"
    return 0
  }

  if {$lfile(cdb) == 1} {
    if {$id == 0} {
      mysqlsel $hd "delete from $lfile(tbl) where unit = '$unit'"
    } else {
      mysqlsel $hd "delete from $lfile(tbl) where unit = '$unit' and id = '$id'"
    }
  } else {
    if {$id == 0} {
      mysqlsel $hd "delete from $lfile(tbl) where unit = '$unit' and chan = '$chan'"
    } else {
      mysqlsel $hd "delete from $lfile(tbl) where unit = '$unit' and id = '$id' and chan = '$chan'"
    }
  }

  mysqlclose $hd

  puthelp "notice $nick :key deleted (defs: $udx)"
  putlog "icel_mysql $nick on $chan did !d $unit $id (successful)"
}

proc pub:learn:sch {nick host hand chan text} {
  global lfile

  set pl [regexp -inline -all -- {\S+} $text]
  set hd 0

  if {[catch {mysqlconnect -host $lfile(msl) -user $lfile(usr) -password $lfile(psd) -db $lfile(dbs)} hd]} {
    puthelp "notice $nick :can't open database"
    return 0
  }

  set unit [mysqlescape $hd [lrange $pl 0 end]]

  if {[string length $unit] == 0} {
    mysqlclose $hd
    puthelp "notice $nick :invalid syntax"
  }

  set ss01 [split $unit \040]
  set ss02 [lrange $ss01 0 0]

  set ss02a "defn like '% $ss02 %' or"
  set ss02b "defn like '%$ss02' or"
  set ss02c "defn like '$ss02%' or"
  set ss02d "unit like '$ss02'"

  set ss02 "($ss02a $ss02b $ss02c $ss02d)"
  set ss01 [split [lrange $ss01 1 end] \040]

  foreach ss03 $ss01 {
    set ss02a "defn like '% $ss03 %' or"
    set ss02b "defn like '%$ss03' or"
    set ss02c "defn like '$ss03%' or"
    set ss02d "unit like '$ss03'"

    set ss02 "$ss02 and ($ss02a $ss02b $ss02c $ss02d)"
  }

  if {$lfile(cdb) == 1} {
    set dfs [mysqlsel $hd "select * from $lfile(tbl) where $ss02 group by unit" -list]
  } else {
    set dfs [mysqlsel $hd "select * from $lfile(tbl) where $ss02 and chan = '$chan' group by unit" -list]
  }

  set udx [llength $dfs]

  set rss [list]

  foreach df $dfs {
    lappend rss [lrange $df 5 5]
  }

  if {$udx == 0} {
    puthelp "notice $nick :no results"
  } else {
    puthelp "notice $nick :found ($udx): [join $rss]"
  }

  mysqlclose $hd
  putlog "icel_mysql $nick on $chan did !s $text ((found: $udx) successful)"
}

proc pub:learn:get {nick host hand chan text} {
  global lfile

  set pl [regexp -inline -all -- {\S+} $text]
  set hd 0

  if {[catch {mysqlconnect -host $lfile(msl) -user $lfile(usr) -password $lfile(psd) -db $lfile(dbs)} hd]} {
    puthelp "notice $nick :can't open database"
    return 0
  }

  set unit [mysqlescape $hd [lrange $pl 0 0]]
  set indx [lrange $pl 1 1]

  set id 0

  if {[string length $indx] > 0} {
    if {[string is integer $indx]} {
      set id $indx
    }
  }

  set dfs 0

  if {$lfile(cdb) == 1} {
    if {$id == 0} {
      set dfs [mysqlsel $hd "select * from $lfile(tbl) where unit = '$unit'" -list]
    } else {
      set dfs [mysqlsel $hd "select * from $lfile(tbl) where unit = '$unit' and id = '$id'" -list]
    }
  } else {
    if {$id == 0} {
      set dfs [mysqlsel $hd "select * from $lfile(tbl) where unit = '$unit' and chan = '$chan'" -list]
    } else {
      set dfs [mysqlsel $hd "select * from $lfile(tbl) where unit = '$unit' and id = '$id' and chan = '$chan'" -list]
    }
  }

  set udx [llength $dfs]

  if {$udx == 0} {
    mysqlclose $hd
    puthelp "notice $nick :key not found"
    putlog "icel_mysql $nick on $chan did !! $unit $id (not found)"
    return 0
  }

  set idx 1

  foreach df $dfs {
    puthelp "privmsg $chan :\[$idx/$udx Id: [lindex $df 0]\] [join [join [lrange $df 5 end]]]"
    incr idx
  }

  mysqlclose $hd

  putlog "icel_mysql $nick on $chan did !! $unit $id (successful)"
}

proc pub:learn:hlp {nick host hand chan text} {
  global lfile

  puthelp "notice $nick :!! <key> id"
  puthelp "notice $nick :!a <key> <def>"
  puthelp "notice $nick :!d <key> id"
  puthelp "notice $nick :!s <str1> str2 ..."
  puthelp "notice $nick :!t"
  puthelp "notice $nick :icel_mysql (ver $lfile(ver))"
  putlog "icel_mysql $nick on $chan did !h (successful)"
}

proc pub:learn:ttl {nick host hand chan text} {
  global lfile

  set pl [regexp -inline -all -- {\S+} $text]
  set hd 0

  if {[catch {mysqlconnect -host $lfile(msl) -user $lfile(usr) -password $lfile(psd) -db $lfile(dbs)} hd]} {
    puthelp "notice $nick :can't open database"
    return 0
  }

  if {$lfile(cdb) == 1} {
    set udx [mysqlsel $hd "select * from $lfile(tbl)"]
    set idx [mysqlsel $hd "select * from $lfile(tbl) group by unit"]
    set adx [mysqlsel $hd "select * from $lfile(tbl) where nick = '$nick'"]
    set bdx [mysqlsel $hd "select * from $lfile(tbl) where nick = '$nick' group by unit"]
  } else {
    set udx [mysqlsel $hd "select * from $lfile(tbl) where chan = '$chan'"]
    set idx [mysqlsel $hd "select * from $lfile(tbl) where chan = '$chan' group by unit"]
    set adx [mysqlsel $hd "select * from $lfile(tbl) where nick = '$nick' and chan = '$chan'"]
    set bdx [mysqlsel $hd "select * from $lfile(tbl) where nick = '$nick' and chan = '$chan'group by unit"]
  }

  set cdx [format "%0.2f" [expr (($adx + 0.00) * 100) / $udx]]

  puthelp "privmsg $chan :DB totals \[Keys/Defs\]: $idx/$udx.  You \[Keys/Defs\]: $bdx/$adx.  Contributed: $cdx%"

  mysqlclose $hd

  putlog "icel_mysql $nick on $chan did !t (successful)"

}

putlog "tcl: icel_mysql ($lfile(ver)) loaded"