dbsearch.tcl

A tcl script for eggdrop bots to search a specified MySQL database by some fields. Requires libmysqltcl.so!!

Postat de	Copyright	Categorie	Review user	Vizualizari	Data
btc	SevenRains	stats	Cod netestat	470	2023-12-25 00:26:53

###############################################################
###
##
## dbsearch.tcl - (C) SevenRains (Corcalciuc V. Horia)
##                              [email protected]
##
## A tcl script for eggdrop bots to search a specified 
## MySQL database by some fields. 
## Requires libmysqltcl.so!!
##
## Inspired from QuoteEngine.
###
##############################################################


# define database parameters
set dbhost "database_hostname_or_ip"
set dbuser "database_user"
set dbpass "database_password"
set dbname "database_name"

# set-up the columns and the
# table of the database we will
# be searching.
set parameter_1 "first_column"
set parameter_2 "second_column"
set table_name "table_name"

# set dbsearch to the flag you want channels
# to have that allow searching your database
# then use .chanset #channel [+/-] dbsearch to
# enable or disable individual channels.
set chanflag "dbsearch"

# bind commands are by default !db_search
# and respectively !db_version in channel 
# window to search the database. users with
# flags m, o and v are allowed to search.
# set to "-|-" to allow everybody.
bind pub "m|ov" !db_search db_search
bind pub "m|ov" !db_version fl_version
set help_search "!db_search"

# a user with this flag(s) can't use the script
set quote_noflags "B|B"

# maximum number of matches sent to channel before
# sending the rest to the nick via /msg.
set quote_chanmax 5



### code starts here (no need to edit stuff below currently)

set fl_version "0.1"
set db_handle [mysqlconnect -host $dbhost -user $dbuser -password $dbpass -db $dbname]
setudef flag $chanflag

################################################################################
# db_search
#   Searches the database by two parameters, like this:
#   !db_search <parameter_1> [parameter_2] using SQL wildcards.
################################################################################
proc db_search { nick host handle channel text} {
  global db_handle php_page quote_noflags quote_chanmax chanflag parameter_1 parameter_2 table_name

  set sub1 ""
  set sub2 ""
  set sub3 ""

  if {![channel get $channel $chanflag]} {
    return 0
  }

  if [matchattr $handle $quote_noflags] { return 0 }

  if {$text == ""} {
    puthelp "PRIVMSG $nick :Use: !db_search <parameter_1> \[parameter_2\]"
    return 0
  }

  regexp -- {([A-Za-z]+) +([A-Za-z]+)} $text sub1 sub2 sub3
  if {$sub3 != ""} {
    set where_clause "AND $parameter_2 LIKE '%[mysqlescape $sub3]%'"
  }

  if {$sub1 == ""} {
    set sql "SELECT * FROM $table_name WHERE $parameter_1 LIKE '%[mysqlescape $text]%' ORDER BY RAND()"
  } else {
    set sql "SELECT * FROM $table_name WHERE $parameter_1 LIKE '%[mysqlescape $sub2]%' $where_clause ORDER BY RAND()"
  }

  putlog "Query: $sql"
  putloglev d * "QuoteEngine: executing $sql"

  if {[mysqlsel $db_handle $sql] > 0} {

    set count 0
    mysqlmap $db_handle {cnp name surname street streetnr block scara etaj apartament sector} {       if {$count == $quote_chanmax} {
        puthelp "PRIVMSG $nick :Rest of matches for your search '$text' follow in private:"
      }
      
      if {$count < 5} {
        puthelp "PRIVMSG $channel :\[$cnp] Nume: $name Prenume: $surname Strada: $street Nr.: $streetnr Bl.: $block Sc.: $scara Etaj: $etaj Ap.: $apartament Sector: $sector"
      } else {
        puthelp "PRIVMSG $nick :\[$cnp] Nume: $name Prenume: $surname Strada: $street Nr.: $streetnr Bl.: $block Sc.: $scara Etaj: $etaj Ap.: $apartament Sector: $sector"
      }
      incr count
    }

    set remaining [mysqlresult $db_handle rows?]
    if {$remaining > 0} {
      regsub "#" $channel "" chan
      puthelp "PRIVMSG $nick :Plus $remaining other matches"
    } else {
      if {$count == 1} {
        puthelp "PRIVMSG $nick :(All of 1 match)"
      } else {
        puthelp "PRIVMSG $nick :(All of $count matches)"
      }
    }
  } else {
    puthelp "PRIVMSG $nick :No matches"
  }
}

################################################################################
## fl_version
##   Gives the version of the script
################################################################################
proc fl_version { nick host handle channel text } {
  global fl_version quote_noflags

  if [matchattr $handle $quote_noflags] { return 0 }

  puthelp "PRIVMSG $nick :Database search version $fl_version by SevenRains"
  return 0
}