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')) {
51 &status("table => $_");
56 if (!scalar keys %db) {
57 &status("Creating database $param{'DBName'}...");
58 $query = "CREATE DATABASE $param{'DBName'}";
59 &dbRaw("create(db $param{'DBName'})", $query);
63 print "Step 4: Creating the tables.\n";
66 if (!exists $db{'factoids'}) {
67 print " creating new table factoids...\n";
69 $query = "CREATE TABLE factoids (".
70 "factoid_key VARCHAR(64) NOT NULL,".
72 "requested_by VARCHAR(64),".
73 "requested_time INT,".
74 "requested_count SMALLINT UNSIGNED,".
75 "created_by VARCHAR(64),".
78 "modified_by VARCHAR(192),".
81 "locked_by VARCHAR(64),".
84 "factoid_value TEXT NOT NULL,".
86 "PRIMARY KEY (factoid_key)".
89 &dbRaw("create(factoids)", $query);
93 if (!exists $db{'freshmeat'}) {
94 print " creating new table freshmeat...\n";
96 $query = "CREATE TABLE freshmeat (".
97 "projectname_short VARCHAR(64) NOT NULL,".
98 "latest_version VARCHAR(32) DEFAULT 'none' NOT NULL,".
99 "license VARCHAR(32),".
100 "url_deb VARCHAR(128),".
101 "url_homepage VARCHAR(128),".
102 "desc_short VARCHAR(96) NOT NULL,".
104 "PRIMARY KEY (projectname_short,latest_version)".
107 &dbRaw("create(freshmeat)", $query);
111 if (!exists $db{'karma'}) {
112 print " creating new table karma...\n";
114 $query = "CREATE TABLE karma (".
115 "nick VARCHAR(20) NOT NULL,".
118 "PRIMARY KEY (nick)".
121 &dbRaw("create(karma)", $query);
125 if (!exists $db{'rootwarn'}) {
126 print " creating new table rootwarn...\n";
128 $query = "CREATE TABLE rootwarn (".
129 "nick VARCHAR(20) NOT NULL,".
130 "attempt SMALLINT UNSIGNED,".
131 "time INT NOT NULL,".
132 "host VARCHAR(80) NOT NULL,".
133 "channel VARCHAR(20) NOT NULL,".
135 "PRIMARY KEY (nick)".
138 &dbRaw("create(rootwarn)", $query);
142 if (!exists $db{'seen'}) {
143 print " creating new table seen...\n";
145 $query = "CREATE TABLE seen (".
146 "nick VARCHAR(20) NOT NULL,".
147 "time INT NOT NULL,".
148 "channel VARCHAR(20) NOT NULL,".
149 "host VARCHAR(80) NOT NULL,".
150 "messagecount SMALLINT UNSIGNED,".
151 "hehcount SMALLINT UNSIGNED,".
152 "message TINYTEXT NOT NULL,".
154 "PRIMARY KEY (nick)".
157 &dbRaw("create(seen)", $query);
162 &openDB("mysql", $adminuser, $adminpass);
165 &status("Step 1: Adding user information.");
168 if (!&dbGet("user","user",$param{'SQLUser'},"user")) {
169 &status(" Adding user $param{'SQLUser'} $dbname/user table...");
171 $query = "INSERT INTO user VALUES ".
172 "('localhost', '$param{'SQLUser'}', ".
173 "password('$param{'SQLPass'}'), ";
175 $query .= "'Y','Y','Y','Y','N','N','N','N','N','N','N','N','N','N')";
176 ### $query .= "'Y','Y','Y','Y','N','N','N','N','N','N')";
178 &dbRaw("create(user)", $query);
181 # Step 3. what's this for?
182 if (!&dbGet("db","db",$param{'SQLUser'},"db")) {
183 &status(" Adding 'db' entry");
185 $query = "INSERT INTO db VALUES ".
186 "('localhost', '$dbname', ".
187 "'$param{'SQLUser'}', ";
189 $query .= "'Y','Y','Y','Y','Y','N','N','N','N','N')";
190 ### $query .= "'Y','Y','Y','Y','Y','N')";
192 &dbRaw("create(db)", $query);
196 &status(" Granting user access to table.");
197 foreach ("factoids","seen","freshmeat") {
198 $query = "GRANT SELECT,INSERT,UPDATE,DELETE ON $dbname.$_ TO $param{'SQLUser'}";
199 &dbRaw("GRANT", $query);
203 &status("Flushing privileges...");
204 $query = "FLUSH PRIVILEGES"; # DOES NOT WORK on slink?
205 &dbRaw("mysql(flush)", $query);
207 } elsif ($param{'DBType'} =~ /pgsql|postgres/i) {
208 if ($param{'DBType'} =~ /pgsql|postgres/i) { use Pg; } # for runtime.
209 my $dbh = Pg::connectdb("dbname=$dbname");
211 if (PGRES_CONNECTION_OK eq $conn->status) {
212 print " opened mysql connection to $param{'mysqlHost'}\n";
214 print " error: cannot connect to $param{'mysqlHost'}.\n";
215 print " $conn->errorMessage\n";
219 # retrieve a list of db's from the server.
221 foreach ($dbh->func('_ListTables')) {
226 print "Step 4: Creating the tables.\n";
229 if (!exists $db{'factoids'}) {
230 print " creating new table factoids...\n";
232 $query = "CREATE TABLE factoids (".
233 "factoid_key varying(64) NOT NULL,".
235 "requested_by varying(64),".
236 "requested_time numeric(11,0),".
237 "requested_count numeric(5,0),".
238 "created_by varying(64),".
239 "created_time numeric(11,0),".
241 "modified_by character varying(192),".
242 "modified_time numeric(11,0),".
244 "locked_by character varying(64),".
245 "locked_time numeric(11,0),".
247 "factoid_value text NOT NULL,".
249 "PRIMARY KEY (factoid_key)".
252 &dbRaw("create(factoids)", $query);
256 if (!exists $db{'freshmeat'}) {
257 print " creating new table freshmeat...\n";
259 $query = "CREATE TABLE freshmeat (".
260 "name charcter varying(64) NOT NULL,".
261 "stable character varying(32),".
262 "devel character varying(32),".
263 "section character varying(40),".
264 "license character varying(32),".
265 "homepage character varying(128),".
266 "download character varying(128),".
267 "changelog character varying(128),".
268 "deb character varying(128),".
269 "rpm character varying(128),".
270 "link character varying(55),".
271 "oneliner character varying(96) NOT NULL,".
273 "PRIMARY KEY (name)".
276 &dbRaw("create(freshmeat)", $query);
280 if (!exists $db{'karma'}) {
281 print " creating new table karma...\n";
283 $query = "CREATE TABLE karma (".
284 "nick character varying(20) NOT NULL,".
285 "karma numeric(5,0),".
287 "PRIMARY KEY (nick)".
290 &dbRaw("create(karma)", $query);
294 if (!exists $db{'rootwarn'}) {
295 print " creating new table rootwarn...\n";
297 $query = "CREATE TABLE rootwarn (".
298 "nick character varying(20) NOT NULL,".
299 "attempt numeric(5,0),".
300 "time numeric(11,0) NOT NULL,".
301 "host character varying(80) NOT NULL,".
302 "channel character varying(20) NOT NULL,".
304 "PRIMARY KEY (nick)".
307 &dbRaw("create(rootwarn)", $query);
311 if (!exists $db{'seen'}) {
312 print " creating new table seen...\n";
314 $query = "CREATE TABLE seen (".
315 "nick character varying(20) NOT NULL,".
316 "time numeric(11,0) NOT NULL,".
317 "channel character varying(20) NOT NULL,".
318 "host character varying(80) NOT NULL,".
319 "messagecount numeric(5,0),".
320 "hehcount numeric(5,0),".
321 "message text NOT NULL,".
323 "PRIMARY KEY (nick)".
326 &dbRaw("create(seen)", $query);