scripts/setup_sql.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 require "src/interface.pl";
  11
  12 $bot_src_dir = "./src/";
  13
  14 # read param stuff from blootbot.config.
  15 &loadConfig("files/blootbot.config");
  16 &loadDBModules();
  17 my $dbname = $param{'DBName'};
  18 my $query;
  19
  20 if ($dbname eq "") {
  21   print "error: appears that teh config file was not loaded properly.\n";
  22   exit 1;
  23 }
  24
  25 if ($param{'DBType'} =~ /mysql/i) {
  26     use DBI;
  27
  28     print "Enter root information...\n";
  29     # username.
  30     print "Username: ";
  31     chop (my $adminuser = <STDIN>);
  32
  33     # passwd.
  34     system "stty -echo";
  35     print "Password: ";
  36     chop(my $adminpass = <STDIN>);
  37     print "\n";
  38     system "stty echo";
  39
  40     if ($adminuser eq "" or $adminpass eq "") {
  41         &ERROR("error: adminuser || adminpass is NULL.");
  42         exit 1;
  43     }
  44
  45     # open the db.
  46     &openDB($dbname, $adminuser, $adminpass);
  47
  48     # retrieve a list of db's from the server.
  49     my %db;
  50     foreach ($dbh->func('_ListTables')) {
  51         $db{$_} = 1;
  52     }
  53
  54     # Step 4.
  55     print "Step 4: Creating the tables.\n";
  56
  57     # factoid db.
  58     if (!exists $db{'factoids'}) {
  59         print "  creating new table factoids...\n";
  60
  61         $query = "CREATE TABLE factoids (".
  62                 "factoid_key VARCHAR(64) NOT NULL,".
  63
  64                 "requested_by VARCHAR(64),".
  65                 "requested_time INT,".
  66                 "requested_count SMALLINT UNSIGNED,".
  67                 "created_by VARCHAR(64),".
  68                 "created_time INT,".
  69
  70                 "modified_by VARCHAR(192),".
  71                 "modified_time INT,".
  72
  73                 "locked_by VARCHAR(64),".
  74                 "locked_time INT,".
  75
  76                 "factoid_value TEXT NOT NULL,".
  77
  78                 "PRIMARY KEY (factoid_key)".
  79         ")";
  80
  81         &dbRaw("create(factoids)", $query);
  82     }
  83
  84     # freshmeat.
  85     if (!exists $db{'freshmeat'}) {
  86         print "  creating new table freshmeat...\n";
  87
  88         $query = "CREATE TABLE freshmeat (".
  89                 "name VARCHAR(64) NOT NULL,".
  90                 "stable VARCHAR(32),".
  91                 "devel VARCHAR(32),".
  92                 "section VARCHAR(40),".
  93                 "license VARCHAR(32),".
  94                 "homepage VARCHAR(128),".
  95                 "download VARCHAR(128),".
  96                 "changelog VARCHAR(128),".
  97                 "deb VARCHAR(128),".
  98                 "rpm VARCHAR(128),".
  99                 "link CHAR(55),".
 100                 "oneliner VARCHAR(96) NOT NULL,".
 101
 102                 "PRIMARY KEY (name)".
 103         ")";
 104
 105         &dbRaw("create(freshmeat)", $query);
 106     }
 107
 108     # karma.
 109     if (!exists $db{'karma'}) {
 110         print "  creating new table karma...\n";
 111
 112         $query = "CREATE TABLE karma (".
 113                 "nick VARCHAR(20) NOT NULL,".
 114                 "karma SMALLINT UNSIGNED,".
 115
 116                 "PRIMARY KEY (nick)".
 117         ")";
 118
 119         &dbRaw("create(karma)", $query);
 120     }
 121
 122     # rootwarn.
 123     if (!exists $db{'rootwarn'}) {
 124         print "  creating new table rootwarn...\n";
 125
 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,".
 132
 133                 "PRIMARY KEY (nick)".
 134         ")";
 135
 136         &dbRaw("create(rootwarn)", $query);
 137     }
 138
 139     # seen.
 140     if (!exists $db{'seen'}) {
 141         print "  creating new table seen...\n";
 142
 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,".
 151
 152                 "PRIMARY KEY (nick)".
 153         ")";
 154
 155         &dbRaw("create(seen)", $query);
 156     }
 157
 158     ### USER SETUP.
 159     &closeDB();
 160     &openDB("mysql", $adminuser, $adminpass);
 161
 162     # Step 1.
 163     &status("Step 1: Adding user information.");
 164
 165     # Step 2.
 166     if (!&dbGet("user","user",$param{'SQLUser'},"user")) {
 167         &status("  Adding user $param{'SQLUser'} $dbname/user table...");
 168
 169         $query = "INSERT INTO user VALUES ".
 170                 "('localhost', '$param{'SQLUser'}', ".
 171                 "password('$param{'SQLPass'}'), ";
 172
 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')";
 175
 176         &dbRaw("create(user)", $query);
 177     }
 178
 179     # Step 3. what's this for?
 180     if (!&dbGet("db","db",$param{'SQLUser'},"db")) {
 181         &status("  Adding 'db' entry");
 182
 183         $query = "INSERT INTO db VALUES ".
 184                 "('localhost', '$dbname', ".
 185                 "'$param{'SQLUser'}', ";
 186
 187         $query .= "'Y','Y','Y','Y','Y','N','N','N','N','N')";
 188 ###     $query .= "'Y','Y','Y','Y','Y','N')";
 189
 190         &dbRaw("create(db)", $query);
 191     }
 192
 193     # grant.
 194     &status("  Granting user access to table.");
 195     $query = "GRANT SELECT,INSERT,UPDATE,DELETE ON $dbname TO $param{'SQLUser'}";
 196     &dbRaw("??", $query);
 197
 198     # flush.
 199     &status("Flushing privileges...");
 200     $query = "FLUSH PRIVILEGES";                # DOES NOT WORK on slink?
 201     &dbRaw("mysql(flush)", $query);
 202
 203     # create database.
 204     &status("Creating database $param{'DBName'}...");
 205     $query = "CREATE DATABASE $param{'DBName'}";
 206     &dbRaw("create(db $param{'DBName'})", $query);
 207
 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");
 211
 212     if (PGRES_CONNECTION_OK eq $conn->status) {
 213         print "  opened mysql connection to $param{'mysqlHost'}\n";
 214     } else {
 215         print "  error: cannot connect to $param{'mysqlHost'}.\n";
 216         print "  $conn->errorMessage\n";
 217         exit 1;
 218     }
 219
 220     # retrieve a list of db's from the server.
 221     my %db;
 222     foreach ($dbh->func('_ListTables')) {
 223         $db{$_} = 1;
 224     }
 225
 226     # Step 4.
 227     print "Step 4: Creating the tables.\n";
 228
 229     # factoid db.
 230     if (!exists $db{'factoids'}) {
 231         print "  creating new table factoids...\n";
 232
 233         $query = "CREATE TABLE factoids (".
 234                 "factoid_key varying(64) NOT NULL,".
 235
 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),".
 241
 242                 "modified_by character varying(192),".
 243                 "modified_time numeric(11,0),".
 244
 245                 "locked_by character varying(64),".
 246                 "locked_time numeric(11,0),".
 247
 248                 "factoid_value text NOT NULL,".
 249
 250                 "PRIMARY KEY (factoid_key)".
 251         ")";
 252
 253         &dbRaw("create(factoids)", $query);
 254     }
 255
 256     # freshmeat.
 257     if (!exists $db{'freshmeat'}) {
 258         print "  creating new table freshmeat...\n";
 259
 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,".
 273
 274                 "PRIMARY KEY (name)".
 275         ")";
 276
 277         &dbRaw("create(freshmeat)", $query);
 278     }
 279
 280     # karma.
 281     if (!exists $db{'karma'}) {
 282         print "  creating new table karma...\n";
 283
 284         $query = "CREATE TABLE karma (".
 285                 "nick character varying(20) NOT NULL,".
 286                 "karma numeric(5,0),".
 287
 288                 "PRIMARY KEY (nick)".
 289         ")";
 290
 291         &dbRaw("create(karma)", $query);
 292     }
 293
 294     # rootwarn.
 295     if (!exists $db{'rootwarn'}) {
 296         print "  creating new table rootwarn...\n";
 297
 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,".
 304
 305                 "PRIMARY KEY (nick)".
 306         ")";
 307
 308         &dbRaw("create(rootwarn)", $query);
 309     }
 310
 311     # seen.
 312     if (!exists $db{'seen'}) {
 313         print "  creating new table seen...\n";
 314
 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,".
 323
 324                 "PRIMARY KEY (nick)".
 325         ")";
 326
 327         &dbRaw("create(seen)", $query);
 328     }
 329 }
 330
 331 print "Done.\n";
 332
 333 &closeDB();