2 # setup_tables: setup MYSQL/PGSQL side of things for blootbot.
7 require "src/logger.pl";
8 require "src/modules.pl";
11 $bot_src_dir = "./src/";
13 # read param stuff from blootbot.config.
14 &loadConfig("files/blootbot.config");
16 my $dbname = $param{'DBName'};
20 print "error: appears that teh config file was not loaded properly.\n";
24 if ($param{'DBType'} =~ /mysql/i) {
28 # retrieve a list of db's from the server.
30 foreach ($dbh->func('_ListTables')) {
35 print "Step 4: Creating the tables.\n";
38 if (!exists $db{'factoids'}) {
39 print " creating new table factoids...\n";
41 $query = "CREATE TABLE factoids (".
42 "factoid_key VARCHAR(64) NOT NULL,".
44 "requested_by VARCHAR(64),".
45 "requested_time INT,".
46 "requested_count SMALLINT UNSIGNED,".
47 "created_by VARCHAR(64),".
50 "modified_by VARCHAR(192),".
53 "locked_by VARCHAR(64),".
56 "factoid_value TEXT NOT NULL,".
58 "PRIMARY KEY (factoid_key)".
61 &dbRaw("create(factoids)", $query);
65 if (!exists $db{'freshmeat'}) {
66 print " creating new table freshmeat...\n";
68 $query = "CREATE TABLE freshmeat (".
69 "name VARCHAR(64) NOT NULL,".
70 "stable VARCHAR(32),".
72 "section VARCHAR(40),".
73 "license VARCHAR(32),".
74 "homepage VARCHAR(128),".
75 "download VARCHAR(128),".
76 "changelog VARCHAR(128),".
80 "oneliner VARCHAR(96) NOT NULL,".
85 &dbRaw("create(freshmeat)", $query);
89 if (!exists $db{'karma'}) {
90 print " creating new table karma...\n";
92 $query = "CREATE TABLE karma (".
93 "nick VARCHAR(20) NOT NULL,".
94 "karma SMALLINT UNSIGNED,".
99 &dbRaw("create(karma)", $query);
103 if (!exists $db{'rootwarn'}) {
104 print " creating new table rootwarn...\n";
106 $query = "CREATE TABLE rootwarn (".
107 "nick VARCHAR(20) NOT NULL,".
108 "attempt SMALLINT UNSIGNED,".
109 "time INT NOT NULL,".
110 "host VARCHAR(80) NOT NULL,".
111 "channel VARCHAR(20) NOT NULL,".
113 "PRIMARY KEY (nick)".
116 &dbRaw("create(rootwarn)", $query);
120 if (!exists $db{'seen'}) {
121 print " creating new table seen...\n";
123 $query = "CREATE TABLE seen (".
124 "nick VARCHAR(20) NOT NULL,".
125 "time INT NOT NULL,".
126 "channel VARCHAR(20) NOT NULL,".
127 "host VARCHAR(80) NOT NULL,".
128 "messagecount SMALLINT UNSIGNED,".
129 "hehcount SMALLINT UNSIGNED,".
130 "message TINYTEXT NOT NULL,".
132 "PRIMARY KEY (nick)".
135 &dbRaw("create(seen)", $query);
137 } elsif ($param{'DBType'} =~ /pgsql|postgres/i) {
138 if ($param{'DBType'} =~ /pgsql|postgres/i) { use Pg; } # for runtime.
139 my $dbh = Pg::connectdb("dbname=$dbname");
141 if (PGRES_CONNECTION_OK eq $conn->status) {
142 print " opened mysql connection to $param{'mysqlHost'}\n";
144 print " error: cannot connect to $param{'mysqlHost'}.\n";
145 print " $conn->errorMessage\n";
149 # retrieve a list of db's from the server.
151 foreach ($dbh->func('_ListTables')) {
156 print "Step 4: Creating the tables.\n";
159 if (!exists $db{'factoids'}) {
160 print " creating new table factoids...\n";
162 $query = "CREATE TABLE factoids (".
163 "factoid_key varying(64) NOT NULL,".
165 "requested_by varying(64),".
166 "requested_time numeric(11,0),".
167 "requested_count numeric(5,0),".
168 "created_by varying(64),".
169 "created_time numeric(11,0),".
171 "modified_by character varying(192),".
172 "modified_time numeric(11,0),".
174 "locked_by character varying(64),".
175 "locked_time numeric(11,0),".
177 "factoid_value text NOT NULL,".
179 "PRIMARY KEY (factoid_key)".
182 &dbRaw("create(factoids)", $query);
186 if (!exists $db{'freshmeat'}) {
187 print " creating new table freshmeat...\n";
189 $query = "CREATE TABLE freshmeat (".
190 "name charcter varying(64) NOT NULL,".
191 "stable character varying(32),".
192 "devel character varying(32),".
193 "section character varying(40),".
194 "license character varying(32),".
195 "homepage character varying(128),".
196 "download character varying(128),".
197 "changelog character varying(128),".
198 "deb character varying(128),".
199 "rpm character varying(128),".
200 "link character varying(55),".
201 "oneliner character varying(96) NOT NULL,".
203 "PRIMARY KEY (name)".
206 &dbRaw("create(freshmeat)", $query);
210 if (!exists $db{'karma'}) {
211 print " creating new table karma...\n";
213 $query = "CREATE TABLE karma (".
214 "nick character varying(20) NOT NULL,".
215 "karma numeric(5,0),".
217 "PRIMARY KEY (nick)".
220 &dbRaw("create(karma)", $query);
224 if (!exists $db{'rootwarn'}) {
225 print " creating new table rootwarn...\n";
227 $query = "CREATE TABLE rootwarn (".
228 "nick character varying(20) NOT NULL,".
229 "attempt numeric(5,0),".
230 "time numeric(11,0) NOT NULL,".
231 "host character varying(80) NOT NULL,".
232 "channel character varying(20) NOT NULL,".
234 "PRIMARY KEY (nick)".
237 &dbRaw("create(rootwarn)", $query);
241 if (!exists $db{'seen'}) {
242 print " creating new table seen...\n";
244 $query = "CREATE TABLE seen (".
245 "nick character varying(20) NOT NULL,".
246 "time numeric(11,0) NOT NULL,".
247 "channel character varying(20) NOT NULL,".
248 "host character varying(80) NOT NULL,".
249 "messagecount numeric(5,0),".
250 "hehcount numeric(5,0),".
251 "message text NOT NULL,".
253 "PRIMARY KEY (nick)".
256 &dbRaw("create(seen)", $query);