scripts/setup_tables.pl

   1 #!/usr/bin/perl
   2 # setup_tables: setup MYSQL/PGSQL side of things for blootbot.
   3 # written by the xk.
   4 ###
   5
   6 require "src/core.pl";
   7 require "src/logger.pl";
   8 require "src/modules.pl";
   9 require "src/Misc.pl";
  10
  11 $infobot_src_dir = "./src/";
  12
  13 # read param stuff from infobot.config.
  14 &loadConfig("files/infobot.config");
  15 &loadDBModules();
  16 my $dbname = $param{'DBName'};
  17 my $query;
  18
  19 if ($dbname eq "") {
  20   print "error: appears that teh config file was not loaded properly.\n";
  21   exit 1;
  22 }
  23
  24 if ($param{'DBType'} =~ /mysql/i) {
  25     use DBI;
  26     &openDB();
  27
  28     # retrieve a list of db's from the server.
  29     my %db;
  30     foreach ($dbh->func('_ListTables')) {
  31         $db{$_} = 1;
  32     }
  33
  34     # Step 4.
  35     print "Step 4: Creating the tables.\n";
  36
  37     # factoid db.
  38     if (!exists $db{'factoids'}) {
  39         print "  creating new table factoids...\n";
  40
  41         $query = "CREATE TABLE factoids (".
  42                 "factoid_key VARCHAR(64) NOT NULL,".
  43
  44                 "requested_by VARCHAR(64),".
  45                 "requested_time INT,".
  46                 "requested_count SMALLINT UNSIGNED,".
  47                 "created_by VARCHAR(64),".
  48                 "created_time INT,".
  49
  50                 "modified_by VARCHAR(192),".
  51                 "modified_time INT,".
  52
  53                 "locked_by VARCHAR(64),".
  54                 "locked_time INT,".
  55
  56                 "factoid_value TEXT NOT NULL,".
  57
  58                 "PRIMARY KEY (factoid_key)".
  59         ")";
  60
  61         &dbRaw("create(factoids)", $query);
  62     }
  63
  64     # freshmeat.
  65     if (!exists $db{'freshmeat'}) {
  66         print "  creating new table freshmeat...\n";
  67
  68         $query = "CREATE TABLE freshmeat (".
  69                 "name VARCHAR(64) NOT NULL,".
  70                 "stable VARCHAR(32),".
  71                 "devel VARCHAR(32),".
  72                 "section VARCHAR(40),".
  73                 "license VARCHAR(32),".
  74                 "homepage VARCHAR(128),".
  75                 "download VARCHAR(128),".
  76                 "changelog VARCHAR(128),".
  77                 "deb VARCHAR(128),".
  78                 "rpm VARCHAR(128),".
  79                 "link CHAR(55),".
  80                 "oneliner VARCHAR(96) NOT NULL,".
  81
  82                 "PRIMARY KEY (name)".
  83         ")";
  84
  85         &dbRaw("create(freshmeat)", $query);
  86     }
  87
  88     # karma.
  89     if (!exists $db{'karma'}) {
  90         print "  creating new table karma...\n";
  91
  92         $query = "CREATE TABLE karma (".
  93                 "nick VARCHAR(20) NOT NULL,".
  94                 "karma SMALLINT UNSIGNED,".
  95
  96                 "PRIMARY KEY (nick)".
  97         ")";
  98
  99         &dbRaw("create(karma)", $query);
 100     }
 101
 102     # rootwarn.
 103     if (!exists $db{'rootwarn'}) {
 104         print "  creating new table rootwarn...\n";
 105
 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,".
 112
 113                 "PRIMARY KEY (nick)".
 114         ")";
 115
 116         &dbRaw("create(rootwarn)", $query);
 117     }
 118
 119     # seen.
 120     if (!exists $db{'seen'}) {
 121         print "  creating new table seen...\n";
 122
 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,".
 131
 132                 "PRIMARY KEY (nick)".
 133         ")";
 134
 135         &dbRaw("create(seen)", $query);
 136     }
 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");
 140
 141     if (PGRES_CONNECTION_OK eq $conn->status) {
 142         print "  opened mysql connection to $param{'mysqlHost'}\n";
 143     } else {
 144         print "  error: cannot connect to $param{'mysqlHost'}.\n";
 145         print "  $conn->errorMessage\n";
 146         exit 1;
 147     }
 148
 149     # retrieve a list of db's from the server.
 150     my %db;
 151     foreach ($dbh->func('_ListTables')) {
 152         $db{$_} = 1;
 153     }
 154
 155     # Step 4.
 156     print "Step 4: Creating the tables.\n";
 157
 158     # factoid db.
 159     if (!exists $db{'factoids'}) {
 160         print "  creating new table factoids...\n";
 161
 162         $query = "CREATE TABLE factoids (".
 163                 "factoid_key varying(64) NOT NULL,".
 164
 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),".
 170
 171                 "modified_by character varying(192),".
 172                 "modified_time numeric(11,0),".
 173
 174                 "locked_by character varying(64),".
 175                 "locked_time numeric(11,0),".
 176
 177                 "factoid_value text NOT NULL,".
 178
 179                 "PRIMARY KEY (factoid_key)".
 180         ")";
 181
 182         &dbRaw("create(factoids)", $query);
 183     }
 184
 185     # freshmeat.
 186     if (!exists $db{'freshmeat'}) {
 187         print "  creating new table freshmeat...\n";
 188
 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,".
 202
 203                 "PRIMARY KEY (name)".
 204         ")";
 205
 206         &dbRaw("create(freshmeat)", $query);
 207     }
 208
 209     # karma.
 210     if (!exists $db{'karma'}) {
 211         print "  creating new table karma...\n";
 212
 213         $query = "CREATE TABLE karma (".
 214                 "nick character varying(20) NOT NULL,".
 215                 "karma numeric(5,0),".
 216
 217                 "PRIMARY KEY (nick)".
 218         ")";
 219
 220         &dbRaw("create(karma)", $query);
 221     }
 222
 223     # rootwarn.
 224     if (!exists $db{'rootwarn'}) {
 225         print "  creating new table rootwarn...\n";
 226
 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,".
 233
 234                 "PRIMARY KEY (nick)".
 235         ")";
 236
 237         &dbRaw("create(rootwarn)", $query);
 238     }
 239
 240     # seen.
 241     if (!exists $db{'seen'}) {
 242         print "  creating new table seen...\n";
 243
 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,".
 252
 253                 "PRIMARY KEY (nick)".
 254         ")";
 255
 256         &dbRaw("create(seen)", $query);
 257     }
 258 }
 259
 260 &closeDB();
 261
 262 print "Done.\n";