# HG changeset patch
# User Matti Hamalainen <ccr@tnsp.org>
# Date 1315328841 -10800
# Node ID 428f498b60308785493530956d4b59ab01a808ba
# Parent  a825a4e27627bd9ca9d7786be41147ea20a788f9
Refactor QuoteDB some more, for modularity. Bot commands are now aggregated
under one main command, private and public versions.

diff -r a825a4e27627 -r 428f498b6030 quotedb.tcl
--- a/quotedb.tcl	Tue Sep 06 20:06:34 2011 +0300
+++ b/quotedb.tcl	Tue Sep 06 20:07:21 2011 +0300
@@ -1,6 +1,6 @@
 ##########################################################################
 #
-# QuoteDB v1.0 by ccr/TNSP <ccr@tnsp.org>
+# QuoteDB v2.0 by ccr/TNSP <ccr@tnsp.org>
 # (C) Copyright 2003-2011 Tecnic Software productions (TNSP)
 #
 # Not for public use or distribution. If you happen to find this,
@@ -46,53 +46,26 @@
 ##########################################################################
 #-------------------------------------------------------------------------
 set qdb_name "QuoteDB"
-set qdb_version "1.0"
+set qdb_version "2.0"
 
 
 #-------------------------------------------------------------------------
 ### Binding initializations
-bind pub - !spedefind spede_pub_find
-bind pub - !spedeadd spede_pub_add
-bind pub - !spededel spede_pub_del
-bind pub - !spede spede_pub_get
-bind msg - spedefind spede_msg_find
-bind msg - spede spede_msg_get
-
-proc spede_pub_get  {unick uhost uhand uchan utext} { qdb_msg 0 $uchan "" [qdb_get "spededb" $unick $uhand $utext] }
-proc spede_msg_get  {unick uhost uhand utext}       { qdb_msg 0 $unick "" [qdb_get "spededb" $unick $uhand $utext] }
-proc spede_pub_add  {unick uhost uhand uchan utext} { qdb_add "spededb" $utext $uhand $unick $uhost $uchan 1 }
-proc spede_pub_del  {unick uhost uhand uchan utext} { qdb_del "spededb" $utext $uhand $unick $uchan 1 }
-proc spede_pub_find {unick uhost uhand uchan utext} { qdb_find "spededb" $unick $uhand $uchan $utext 1 }
-proc spede_msg_find {unick uhost uhand utext}       { qdb_find "spededb" $unick $uhand "" $utext 0 }
-
-bind pub - !mnfind mn_pub_find
-bind pub - !mnadd mn_pub_add
-bind pub - !mndel mn_pub_del
-bind pub - !mn mn_pub_get
-bind msg - mnfind mn_msg_find
-bind msg - mn mn_msg_get
+bind pub - !spede spede_pub_cmd
+bind msg - spede spede_msg_cmd
+proc spede_pub_cmd {unick uhost uhand uchan utext} { qdb_command "spededb" $unick $uhost $uhand $uchan $utext 1 }
+proc spede_msg_cmd {unick uhost uhand utext}       { qdb_command "spededb" $unick $uhost $uhand "" $utext 0 }
 
-proc mn_pub_get  {unick uhost uhand uchan utext} { qdb_msg 0 $uchan "" [qdb_get "mndb" $unick $uhand $utext] }
-proc mn_msg_get  {unick uhost uhand utext}       { qdb_msg 0 $unick "" [qdb_get "mndb" $unick $uhand $utext] }
-proc mn_pub_add  {unick uhost uhand uchan utext} { qdb_add "mndb" $utext $uhand $unick $uhost $uchan 1 }
-proc mn_pub_del  {unick uhost uhand uchan utext} { qdb_del "mndb" $utext $uhand $unick $uchan 1 }
-proc mn_pub_find {unick uhost uhand uchan utext} { qdb_find "mndb" $unick $uhand $uchan $utext 1 }
-proc mn_msg_find {unick uhost uhand utext}       { qdb_find "mndb" $unick $uhand "" $utext 0 }
-
+bind pub - !mn mn_pub_cmd
+bind msg - mn mn_msg_cmd
+proc mn_pub_cmd {unick uhost uhand uchan utext} { qdb_command "mndb" $unick $uhost $uhand $uchan $utext 1 }
+proc mn_msg_cmd {unick uhost uhand utext}       { qdb_command "mndb" $unick $uhost $uhand "" $utext 0 }
 
-bind pub - !tuksufind tuksu_pub_find
-bind pub - !tuksuadd tuksu_pub_add
-bind pub - !tuksudel tuksu_pub_del
-bind pub - !tuksu tuksu_pub_get
-bind msg - tuksufind tuksu_msg_find
-bind msg - tuksu tuksu_msg_get
+bind pub - !tuksu tuksu_pub_cmd
+bind msg - tuksu tuksu_msg_cmd
+proc tuksu_pub_cmd {unick uhost uhand uchan utext} { qdb_command "tuksudb" $unick $uhost $uhand $uchan $utext 1 }
+proc tuksu_msg_cmd {unick uhost uhand utext}       { qdb_command "tuksudb" $unick $uhost $uhand "" $utext 0 }
 
-proc tuksu_pub_get  {unick uhost uhand uchan utext} { qdb_msg 0 $uchan "" [qdb_get "tuksudb" $unick $uhand $utext] }
-proc tuksu_msg_get  {unick uhost uhand utext}       { qdb_msg 0 $unick "" [qdb_get "tuksudb" $unick $uhand $utext] }
-proc tuksu_pub_add  {unick uhost uhand uchan utext} { qdb_add "tuksudb" $utext $uhand $unick $uhost $uchan 1 }
-proc tuksu_pub_del  {unick uhost uhand uchan utext} { qdb_del "tuksudb" $utext $uhand $unick $uchan 1 }
-proc tuksu_pub_find {unick uhost uhand uchan utext} { qdb_find "tuksudb" $unick $uhand $uchan $utext 1 }
-proc tuksu_msg_find {unick uhost uhand utext}       { qdb_find "tuksudb" $unick $uhand "" $utext 0 }
 
 
 #-------------------------------------------------------------------------
@@ -154,18 +127,21 @@
 
 
 #-------------------------------------------------------------------------
-proc qdb_add {utable utext uhand unick uhost uchan upublic} {
+proc qdb_add {utable unick uhost uhand uchan utext upublic} {
   global quotedb
 
-  if {"x$utext" == "x" || "$uhand" == "x" || $uhand == {}} {
+  if {$uhand == "" || $uhand == {}} {
     qdb_msg $upublic $unick $uchan "pyh."
     return 0
   }
 
+  if {$utext == "" || $utext == {}} {
+    qdb_msg $upublic $unick $uchan "wtf! add <teksti>"
+    return 0
+  }
+
   set usql "INSERT INTO $utable (utime,utext,user,host,chan) VALUES ([unixtime], '[qdb_escape $utext]', '[qdb_escape $uhand]', '[qdb_escape $uhost]', '[qdb_escape $uchan]')"
-  if {[catch {quotedb eval $usql} uerrmsg]} {
-    qdb_log "$uerrmsg on SQL:\n$sql"
-    qdb_msg $upublic $unick $uchan "virhe sörkittäessä tietokantaa. uliskaa."
+  if {![qdb_sql_exec $upublic $unick $uchan $usql]} {
     return 0
   }
 
@@ -174,7 +150,7 @@
 #  set numQuotes [quotedb ]
 
   ### Log some data
-  qdb_log "Added SPEDE #$quoteID ($unick/$uhand@$uchan): $utext"
+  qdb_log "Added $utable #$quoteID ($unick/$uhand@$uchan): $utext"
 
   ### Report success to user
   qdb_msg $upublic $unick $uchan "tietokantaa sörkitty (#$quoteID / $numQuotes), kiitos."
@@ -183,63 +159,137 @@
 
 
 #-------------------------------------------------------------------------
-proc qdb_del {utable utext uhand unick uchan upublic} {
+proc qdb_delete {utable unick uhand uchan utext upublic} {
   global quotedb qdb_max_deltime
+  set maxdiff [expr $qdb_max_deltime * 60]
 
-  if {"x$utext" == "x" || "$uhand" == "x" || $uhand == {}} {
+  if {$uhand == "" || $uhand == {}} {
     qdb_msg $upublic $unick $uchan "pyh."
     return 0
   }
 
-  set maxdiff [expr $qdb_max_deltime * 60]
-
-  if {[regexp "(\[0-9\]+)" $utext umatch unum]} {
-    set quoteID $unum
-    set usql "SELECT id AS quoteID, utime AS utime, utext AS utext, user AS uuser, host AS uhost FROM $utable WHERE id=$unum AND user='[qdb_escape $uhand]'"
-  } else {
-    qdb_msg $upublic $unick $uchan "ID # annettava."
+  if {![regexp {^\s*([0-9]+)$} $utext umatch unum]} {
+    qdb_msg $upublic $unick $uchan "del <id>"
     return 0
   }
   
+  set usql "SELECT id AS quoteID, utime AS utime, utext AS utext, user AS uuser, host AS uhost FROM $utable WHERE id=$unum AND user='[qdb_escape $uhand]'"
   quotedb eval $usql {
     set udiff [expr [unixtime] - $utime]
     if {$udiff < $maxdiff} {
       set usql "DELETE FROM $utable WHERE id=$quoteID AND user='[qdb_escape $uhand]'"
-      if {[catch {quotedb eval $usql} uerrmsg]} {
-        qdb_log "$uerrmsg on SQL:\n$sql"
-        qdb_msg $upublic $unick $uchan "virhe sörkittäessä tietokantaa. uliskaa."
+      if {![qdb_sql_exec $upublic $unick $uchan $usql]} {
+        return 0
       } else {
         qdb_msg $upublic $unick $uchan "$utable ID #$quoteID poistettu."
+        return 1
       }
-      return 0
     } else {
       qdb_msg $upublic $unick $uchan "$utable quote #$quoteID vanhempi kuin $qdb_max_deltime minuuttia, ei poisteta."
       return 0
     }
   }
 
-  qdb_msg $upublic $unick $uchan "#$quoteID ei löydy, tai se ei kuulu käyttäjälle '$uhand'."
+  qdb_msg $upublic $unick $uchan "#$unum ei löydy, tai se ei kuulu käyttäjälle '$uhand'."
+  return 0
+}
+
+
+#-------------------------------------------------------------------------
+proc qdb_toplist {utable unick uchan unum upublic} {
+  global quotedb
+
+  # DEBUG!
+  return 0
+  
+  if {$unum < 3 || $unum > 10} {
+    qdb_msg $upublic $unick $uchan "top-listan pituus oltava 3-10."
+    return 0
+  }
+
+  set uresults 0
+  set usql "SELECT total(${utable}_votes.vote) AS uvalue, ${utable}.id AS quoteID, ${utable}.utext AS utext, ${utable}.utime AS utime,${utable}.user AS uuser, ${utable}.id AS uid FROM ${utable}_votes INNER JOIN $utable ON ${utable}_votes.urlid = ${utable}.id ORDER BY uvalue,utime DESC LIMIT $unum"
+  quotedb eval $usql {
+    incr uresults
+    qdb_msg $upublic $unick $uchan "${uresults}. #${quoteID}: $utext ($uuser, $uvalue)"
+  }
+
+  return 0
 }
 
 
 #-------------------------------------------------------------------------
-proc qdb_get {utable unick uhand uindex} {
-  global quotedb 
-  
-  set usql "SELECT id AS quoteID, utime AS utime, utext AS utext, user AS uuser, host AS uhost FROM $utable"
-  if {[regexp "(\[0-9\]+)" $uindex umatch unum]} {
-    set usql "$usql WHERE id=$unum"
-    qdb_log "$unick/$uhand searched $utable #$unum"
+proc qdb_vote {utable unick uhand uchan utext upublic} {
+  global quotedb
+
+  if {$uhand == "" || $uhand == {}} {
+    qdb_msg $upublic $unick $uchan "pyh."
+    return 0
+  }
+
+  set uvote 1
+  if {$utext == "" || [regexp {^\s*-1$} $utext umatch uvote]} {
+    set usql "SELECT max(id) AS qid FROM ${utable}"
+    set uid 0
+    quotedb eval $usql { set uid $qid }
+  } elseif {[regexp {^\s*([0-9]+)$} $utext umatch uid]} {
+  } elseif {![regexp {^\s*([0-9]+)\s+(-1|1)$} $utext umatch uid uvote]} {
+    qdb_msg $upublic $unick $uchan "vote <id> \[1 | -1\]"
+    return 0
+  }
+
+  # Check if given quote ID exists.
+  set uresults 0
+  set usql "SELECT id AS qid FROM ${utable} WHERE id=$uid"
+  quotedb eval $usql { incr uresults }
+  if {$uresults == 0} {
+    qdb_msg $upublic $unick $uchan "quotea #$uid ei ole."
+    return 0
+  }
+
+  # Check if user has previously voted this item
+  set uresults 0
+  set usql "SELECT id AS qid FROM ${utable}_votes WHERE user='[qdb_escape $uhand]' AND urlid=$uid"
+  quotedb eval $usql {
+    incr uresults
+    # Yes, update the previous vote
+    set usql "UPDATE ${utable}_votes SET vote=$uvote WHERE id=$qid"
+    if {[qdb_sql_exec $upublic $unick $uchan $usql]} {
+      qdb_msg $upublic $unick $uchan "ääni päivitetty (#$uid -> $uvote)"
+      return 1
+    } else {
+      return 0
+    }
+  }
+
+  if {$uresults == 0} {
+    # No previous votes, insert new
+    set usql "INSERT INTO ${utable}_votes (user,urlid,vote) VALUES ('[qdb_escape $uhand]',$uid,$uvote)"
+    if {[qdb_sql_exec $upublic $unick $uchan $usql]} {
+      qdb_msg $upublic $unick $uchan "kiitos äänestäsi (#$uid -> $uvote)"
+    }
+  }
+}
+
+
+#-------------------------------------------------------------------------
+proc qdb_get {utable unick uchan uindex upublic} {
+  global quotedb
+
+  set usql "SELECT id AS quoteID, utext AS utext, user AS uuser FROM $utable"
+  if {$uindex >= 0} {
+    set usql "$usql WHERE id=$uindex"
+    qdb_log "$unick searched $utable #$uindex"
   } else {
     set usql "$usql ORDER BY RANDOM() LIMIT 1"
-    qdb_log "$unick/$uhand get random $utable"
+    qdb_log "$unick get random $utable"
   }
 
+  set uresults 0
   quotedb eval $usql {
-    return "#${quoteID}: $utext ($uuser)"
+    incr uresults
+    qdb_msg $upublic $unick $uchan "#${quoteID}: $utext ($uuser)"
   }
-
-  return "ei löydy."
 }
 
 
@@ -278,8 +328,9 @@
     set fquery ""
   }
 
+  ### Query the database and output results
   set uresults 0
-  set usql "SELECT id AS quoteID, utime AS utime, utext AS utext, user AS uuser, host AS uhost FROM $utable $fquery ORDER BY utime DESC LIMIT $ulimit"
+  set usql "SELECT id AS quoteID, utime AS utime, utext AS utext, user AS uuser FROM $utable $fquery ORDER BY utime DESC LIMIT $ulimit"
   quotedb eval $usql {
     incr uresults
     qdb_msg $upublic $unick $uchan "#${quoteID}: $utext ($uuser@[qdb_ctime $utime])"
@@ -287,8 +338,40 @@
   
   if {$uresults == 0} {
     qdb_msg $upublic $unick $uchan "ei löydy."
+    return 0
   }
-  return 0
+  return 1
+}
+
+
+#-------------------------------------------------------------------------
+proc qdb_command {utable unick uhost uhand uchan utext upublic} {
+  set utext [string trim [join $utext " "]]
+
+  if {$utext == "" || $utext == {}} {
+    # No arguments, assume random query
+    qdb_get $utable $unick $uchan -1 $upublic
+  } elseif {[regexp {^([0-9]+)$} $utext umatch unum]} {
+    # Numeric argument, assume index query
+    qdb_get $utable $unick $uchan $unum $upublic
+  } elseif {[regexp {^top\s*([0-9]+)$} $utext umatch unum]} {
+    # Toplist of quotes
+    qdb_toplist $utable $unick $uchan $unum $upublic
+  } elseif {[regexp {^add\s*(.*)$} $utext umatch unum]} {
+    # Add quote
+    qdb_add $utable $unick $uhost $uhand $uchan $unum $upublic
+  } elseif {[regexp {^del\s*([0-9]*)$} $utext umatch unum]} {
+    # Delete quote
+    qdb_delete $utable $unick $uhand $uchan $unum $upublic
+  } elseif {[regexp {^find\s*(.*)$} $utext umatch unum]} {
+    # Find quote(s)
+    qdb_find $utable $unick $uhand $uchan $unum $upublic
+  } elseif {[regexp {^vote\s*(.*)$} $utext umatch unum]} {
+    # Vote
+    qdb_vote $utable $unick $uhand $uchan $unum $upublic
+  } else {
+#    qdb_msg $upublic $unick $uchan "invaliidi! '$utext'"
+  }
 }
 
 #-------------------------------------------------------------------------