1#!/usr/local/bin/perl
2# Please note that some MySQL experience is required to use this script.
3#
4# You must have the appropriate tools installed for MySQL and Perl to "talk"
5# to each other... specifically perl dbi and DBD::mysql
6#
7# You must set up the following table in your mysql database:
8#
9# +----------+-------------+------+-----+---------+-------+
10# | Field    | Type        | Null | Key | Default | Extra |
11# +----------+-------------+------+-----+---------+-------+
12# | nickname | varchar(40) | YES  |     | NULL    |       |
13# | email    | varchar(40) | YES  |     | NULL    |       |
14# | birthday | varchar(40) | YES  |     | NULL    |       |
15# +----------+-------------+------+-----+---------+-------+
16#
17# I suggest you set up a separate user in the mysql database to use this script
18# with only permission to SELECT from this database.
19# </paranoia>
20#
21# In the script you must replace the following variables with your information:
22#
23#    $d = database name
24#    $u = user login for database
25#    $p = user password
26#
27# if you choose to make this accessible by users on a user-list only, create
28# a text file called "users" in your home .irssi directory, add the nicknames
29# of users you wish to give access in this format:
30#
31# PrincessLeia2
32# R2D2
33# Time
34#
35# AND uncomment the 3 sections indicatated in the script
36#
37# I never created an interface to add new nicknames, email, and birthday,
38# so you will need to manually insert this information into the database
39#
40# This script allows a user to search the database by using the command ~search nickname
41# (in channel, or in pm) it will respond with a private message. It will match full and
42# partial nicknames while it does it's search (if you search for 't' it will give you
43# results of any nicknames with a 't' i nthem)
44#
45# Personally, I run this in an ircbot, as the owner of this script cannot use
46# the ~search command themselves
47#
48#
49# ... That's about it, enjoy!
50#
51
52use strict;
53use Irssi;
54use DBI;
55use vars qw($VERSION %IRSSI);
56
57$VERSION = "1.0";
58%IRSSI = (
59    authors => 'PrincessLeia2',
60    contact => 'lyz\@princessleia.com ',
61    name => 'emaildb',
62    description => 'a script for accessing an email mysql database through irc',
63    license => 'GNU GPL v2 or later',
64    url => 'http://www.princessleia.com/'
65);
66
67
68# uncomment the following commented (and replace '/home/user' with your home directory) lines for user restricted access.
69#
70# open ( LIST, "</home/lyz/.irssi/users" ) or die "can't open users:$!\n";
71#  chomp( @user = <LIST> );
72#         close LIST;
73
74my $d = ('database');
75my $u = ('user');
76my $p = ('password');
77
78
79sub event_privmsg {
80my ($server, $data, $nick, $mask, $target) =@_;
81my ($target, $text) = $data =~ /^(\S*)\s:(.*)/;
82  if ($text =~ /^~search */i ) {
83
84# Uncomment the following commented lines for user restricted access
85#    foreach $person (@user) {
86#      if ($nick =~ /^$person$/i) {
87
88		my ($nickname) = $text =~ /^~search (.*)/;
89
90        my $dbh = DBI->connect("DBI:mysql:$d","$u","$p")
91                or die "Couldn't connect to database: " . DBI->errstr;
92        my $sth = $dbh->prepare("SELECT * FROM 13th where nickname like \"\%$nickname\%\";")
93                or die "Cant prepare statement: $dbh->errstr\n";
94        my $rv = $sth->execute
95                or die "cant execute the query: $sth->errstr\n";
96if ($rv >= 1) {
97  my @row;
98  while ( @row = $sth->fetchrow_array(  ) ) {
99	my $n = "$row[0]\n";
100 	my $e = "$row[1]\n";
101 	my $b = "$row[2]\n";
102                $server->command ( "msg $nick Nickname : $n" );
103                $server->command ( "msg $nick Email : $e" );
104                $server->command ( "msg $nick Birthday : $b" );
105}
106}
107else    {
108           $server->command ( "msg $nick Sorry, No Results Match Your Query\n" );
109	}
110
111# Uncomment the following commented lines for user restricted access
112#}
113#}
114
115}
116}
117
118Irssi::signal_add('event privmsg', 'event_privmsg');
119