2 # setup_tables: setup MYSQL/PGSQL side of things for blootbot.
7 require "src/logger.pl";
8 require "src/modules.pl";
10 require "src/interface.pl";
12 $bot_src_dir = "./src/";
14 # read param stuff from blootbot.config.
15 &loadConfig("files/blootbot.config");
17 my $dbname = $param{'DBName'};
21 print "error: appears that teh config file was not loaded properly.\n";
25 if ($param{'DBType'} =~ /mysql/i) {
28 print "Enter root information...\n";
31 chop (my $adminuser = <STDIN>);
36 chop(my $adminpass = <STDIN>);
40 if ($adminuser eq "" or $adminpass eq "") {
41 &ERROR("error: adminuser || adminpass is NULL.");
46 &openDB($dbname, $adminuser, $adminpass);
48 # retrieve a list of db's from the server.
50 foreach ($dbh->func('_ListTables')) {
55 print "Step 4: Creating the tables.\n";
58 if (!exists $db{'factoids'}) {
59 print " creating new table factoids...\n";
61 $query = "CREATE TABLE factoids (".
62 "factoid_key VARCHAR(64) NOT NULL,".
64 "requested_by VARCHAR(64),".
65 "requested_time INT,".
66 "requested_count SMALLINT UNSIGNED,".
67 "created_by VARCHAR(64),".
70 "modified_by VARCHAR(192),".
73 "locked_by VARCHAR(64),".
76 "factoid_value TEXT NOT NULL,".
78 "PRIMARY KEY (factoid_key)".
81 &dbRaw("create(factoids)", $query);
85 if (!exists $db{'freshmeat'}) {
86 print " creating new table freshmeat...\n";
88 $query = "CREATE TABLE freshmeat (".
89 "name VARCHAR(64) NOT NULL,".
90 "stable VARCHAR(32),".
92 "section VARCHAR(40),".
93 "license VARCHAR(32),".
94 "homepage VARCHAR(128),".
95 "download VARCHAR(128),".
96 "changelog VARCHAR(128),".
100 "oneliner VARCHAR(96) NOT NULL,".
102 "PRIMARY KEY (name)".
105 &dbRaw("create(freshmeat)", $query);
109 if (!exists $db{'karma'}) {
110 print " creating new table karma...\n";
112 $query = "CREATE TABLE karma (".
113 "nick VARCHAR(20) NOT NULL,".
114 "karma SMALLINT UNSIGNED,".
116 "PRIMARY KEY (nick)".
119 &dbRaw("create(karma)", $query);
123 if (!exists $db{'rootwarn'}) {
124 print " creating new table rootwarn...\n";
126 $query = "CREATE TABLE rootwarn (".
127 "nick VARCHAR(20) NOT NULL,".
128 "attempt SMALLINT UNSIGNED,".
129 "time INT NOT NULL,".
130 "host VARCHAR(80) NOT NULL,".
131 "channel VARCHAR(20) NOT NULL,".
133 "PRIMARY KEY (nick)".
136 &dbRaw("create(rootwarn)", $query);
140 if (!exists $db{'seen'}) {
141 print " creating new table seen...\n";
143 $query = "CREATE TABLE seen (".
144 "nick VARCHAR(20) NOT NULL,".
145 "time INT NOT NULL,".
146 "channel VARCHAR(20) NOT NULL,".
147 "host VARCHAR(80) NOT NULL,".
148 "messagecount SMALLINT UNSIGNED,".
149 "hehcount SMALLINT UNSIGNED,".
150 "message TINYTEXT NOT NULL,".
152 "PRIMARY KEY (nick)".
155 &dbRaw("create(seen)", $query);
160 &openDB("mysql", $adminuser, $adminpass);
163 &status("Step 1: Adding user information.");
166 if (!&dbGet("user","user",$param{'SQLUser'},"user")) {
167 &status(" Adding user $param{'SQLUser'} $dbname/user table...");
169 $query = "INSERT INTO user VALUES ".
170 "('localhost', '$param{'SQLUser'}', ".
171 "password('$param{'SQLPass'}'), ";
173 $query .= "'Y','Y','Y','Y','N','N','N','N','N','N','N','N','N','N')";
174 ### $query .= "'Y','Y','Y','Y','N','N','N','N','N','N')";
176 &dbRaw("create(user)", $query);
179 # Step 3. what's this for?
180 if (!&dbGet("db","db",$param{'SQLUser'},"db")) {
181 &status(" Adding 'db' entry");
183 $query = "INSERT INTO db VALUES ".
184 "('localhost', '$dbname', ".
185 "'$param{'SQLUser'}', ";
187 $query .= "'Y','Y','Y','Y','Y','N','N','N','N','N')";
188 ### $query .= "'Y','Y','Y','Y','Y','N')";
190 &dbRaw("create(db)", $query);
194 &status(" Granting user access to table.");
195 $query = "GRANT SELECT,INSERT,UPDATE,DELETE ON $dbname TO $param{'SQLUser'}";
196 &dbRaw("??", $query);
199 &status("Flushing privileges...");
200 $query = "FLUSH PRIVILEGES"; # DOES NOT WORK on slink?
201 &dbRaw("mysql(flush)", $query);
204 &status("Creating database $param{'DBName'}...");
205 $query = "CREATE DATABASE $param{'DBName'}";
206 &dbRaw("create(db $param{'DBName'})", $query);
208 } elsif ($param{'DBType'} =~ /pgsql|postgres/i) {
209 if ($param{'DBType'} =~ /pgsql|postgres/i) { use Pg; } # for runtime.
210 my $dbh = Pg::connectdb("dbname=$dbname");
212 if (PGRES_CONNECTION_OK eq $conn->status) {
213 print " opened mysql connection to $param{'mysqlHost'}\n";
215 print " error: cannot connect to $param{'mysqlHost'}.\n";
216 print " $conn->errorMessage\n";
220 # retrieve a list of db's from the server.
222 foreach ($dbh->func('_ListTables')) {
227 print "Step 4: Creating the tables.\n";
230 if (!exists $db{'factoids'}) {
231 print " creating new table factoids...\n";
233 $query = "CREATE TABLE factoids (".
234 "factoid_key varying(64) NOT NULL,".
236 "requested_by varying(64),".
237 "requested_time numeric(11,0),".
238 "requested_count numeric(5,0),".
239 "created_by varying(64),".
240 "created_time numeric(11,0),".
242 "modified_by character varying(192),".
243 "modified_time numeric(11,0),".
245 "locked_by character varying(64),".
246 "locked_time numeric(11,0),".
248 "factoid_value text NOT NULL,".
250 "PRIMARY KEY (factoid_key)".
253 &dbRaw("create(factoids)", $query);
257 if (!exists $db{'freshmeat'}) {
258 print " creating new table freshmeat...\n";
260 $query = "CREATE TABLE freshmeat (".
261 "name charcter varying(64) NOT NULL,".
262 "stable character varying(32),".
263 "devel character varying(32),".
264 "section character varying(40),".
265 "license character varying(32),".
266 "homepage character varying(128),".
267 "download character varying(128),".
268 "changelog character varying(128),".
269 "deb character varying(128),".
270 "rpm character varying(128),".
271 "link character varying(55),".
272 "oneliner character varying(96) NOT NULL,".
274 "PRIMARY KEY (name)".
277 &dbRaw("create(freshmeat)", $query);
281 if (!exists $db{'karma'}) {
282 print " creating new table karma...\n";
284 $query = "CREATE TABLE karma (".
285 "nick character varying(20) NOT NULL,".
286 "karma numeric(5,0),".
288 "PRIMARY KEY (nick)".
291 &dbRaw("create(karma)", $query);
295 if (!exists $db{'rootwarn'}) {
296 print " creating new table rootwarn...\n";
298 $query = "CREATE TABLE rootwarn (".
299 "nick character varying(20) NOT NULL,".
300 "attempt numeric(5,0),".
301 "time numeric(11,0) NOT NULL,".
302 "host character varying(80) NOT NULL,".
303 "channel character varying(20) NOT NULL,".
305 "PRIMARY KEY (nick)".
308 &dbRaw("create(rootwarn)", $query);
312 if (!exists $db{'seen'}) {
313 print " creating new table seen...\n";
315 $query = "CREATE TABLE seen (".
316 "nick character varying(20) NOT NULL,".
317 "time numeric(11,0) NOT NULL,".
318 "channel character varying(20) NOT NULL,".
319 "host character varying(80) NOT NULL,".
320 "messagecount numeric(5,0),".
321 "hehcount numeric(5,0),".
322 "message text NOT NULL,".
324 "PRIMARY KEY (nick)".
327 &dbRaw("create(seen)", $query);