html/emaildb1.0.pl
1 #!/usr/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
52
53 use Irssi;
54 use DBI;
55 use 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
74 $d = ('database');
75 $u = ('user');
76 $p = ('password');
77
78
79 sub event_privmsg {
80 my ($server, $data, $nick, $mask, $target) =@_;
81 my ($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 $dbh = DBI->connect("DBI:mysql:$d","$u","$p")
91 or die "Couldn't connect to database: " . DBI->errstr;
92 $sth = $dbh->prepare("SELECT * FROM 13th where nickname like \"\%$nickname\%\";")
93 or die "Cant prepare $statement: $dbh->errstr\n";
94 $rv = $sth->execute
95 or die "cant execute the query: $sth->errstr\n";
96 if ($rv >= 1) {
97 my @row;
98 while ( @row = $sth->fetchrow_array( ) ) {
99 $n = "$row[0]\n";
100 $e = "$row[1]\n";
101 $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 }
107 else {
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
118 Irssi::signal_add('event privmsg', 'event_privmsg');