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         &status("table => $_");
  52         $db{$_} = 1;
  53     }
  54
  55     # create database.
  56     if (!scalar keys %db) {
  57         &status("Creating database $param{'DBName'}...");
  58         $query = "CREATE DATABASE $param{'DBName'}";
  59         &dbRaw("create(db $param{'DBName'})", $query);
  60     }
  61
  62     # Step 4.
  63     print "Step 4: Creating the tables.\n";
  64
  65     # factoid db.
  66     if (!exists $db{'factoids'}) {
  67         print "  creating new table factoids...\n";
  68
  69         $query = "CREATE TABLE factoids (".
  70                 "factoid_key VARCHAR(64) NOT NULL,".
  71
  72                 "requested_by VARCHAR(64),".
  73                 "requested_time INT,".
  74                 "requested_count SMALLINT UNSIGNED,".
  75                 "created_by VARCHAR(64),".
  76                 "created_time INT,".
  77
  78                 "modified_by VARCHAR(192),".
  79                 "modified_time INT,".
  80
  81                 "locked_by VARCHAR(64),".
  82                 "locked_time INT,".
  83
  84                 "factoid_value TEXT NOT NULL,".
  85
  86                 "PRIMARY KEY (factoid_key)".
  87         ")";
  88
  89         &dbRaw("create(factoids)", $query);
  90     }
  91
  92     # freshmeat.
  93     if (!exists $db{'freshmeat'}) {
  94         print "  creating new table freshmeat...\n";
  95
  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,".
 103
 104                 "PRIMARY KEY (projectname_short,latest_version)".
 105         ")";
 106
 107         &dbRaw("create(freshmeat)", $query);
 108     }
 109
 110     # karma.
 111     if (!exists $db{'karma'}) {
 112         print "  creating new table karma...\n";
 113
 114         $query = "CREATE TABLE karma (".
 115                 "nick VARCHAR(20) NOT NULL,".
 116                 "karma SMALLINT,".
 117
 118                 "PRIMARY KEY (nick)".
 119         ")";
 120
 121         &dbRaw("create(karma)", $query);
 122     }
 123
 124     # rootwarn.
 125     if (!exists $db{'rootwarn'}) {
 126         print "  creating new table rootwarn...\n";
 127
 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,".
 134
 135                 "PRIMARY KEY (nick)".
 136         ")";
 137
 138         &dbRaw("create(rootwarn)", $query);
 139     }
 140
 141     # seen.
 142     if (!exists $db{'seen'}) {
 143         print "  creating new table seen...\n";
 144
 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,".
 153
 154                 "PRIMARY KEY (nick)".
 155         ")";
 156
 157         &dbRaw("create(seen)", $query);
 158     }
 159
 160     ### USER SETUP.
 161     &closeDB();
 162     &openDB("mysql", $adminuser, $adminpass);
 163
 164     # Step 1.
 165     &status("Step 1: Adding user information.");
 166
 167     # Step 2.
 168     if (!&dbGet("user","user",$param{'SQLUser'},"user")) {
 169         &status("  Adding user $param{'SQLUser'} $dbname/user table...");
 170
 171         $query = "INSERT INTO user VALUES ".
 172                 "('localhost', '$param{'SQLUser'}', ".
 173                 "password('$param{'SQLPass'}'), ";
 174
 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')";
 177
 178         &dbRaw("create(user)", $query);
 179     }
 180
 181     # Step 3. what's this for?
 182     if (!&dbGet("db","db",$param{'SQLUser'},"db")) {
 183         &status("  Adding 'db' entry");
 184
 185         $query = "INSERT INTO db VALUES ".
 186                 "('localhost', '$dbname', ".
 187                 "'$param{'SQLUser'}', ";
 188
 189         $query .= "'Y','Y','Y','Y','Y','N','N','N','N','N')";
 190 ###     $query .= "'Y','Y','Y','Y','Y','N')";
 191
 192         &dbRaw("create(db)", $query);
 193     }
 194
 195     # grant.
 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);
 200     }
 201
 202     # flush.
 203     &status("Flushing privileges...");
 204     $query = "FLUSH PRIVILEGES";                # DOES NOT WORK on slink?
 205     &dbRaw("mysql(flush)", $query);
 206
 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");
 210
 211     if (PGRES_CONNECTION_OK eq $conn->status) {
 212         print "  opened mysql connection to $param{'mysqlHost'}\n";
 213     } else {
 214         print "  error: cannot connect to $param{'mysqlHost'}.\n";
 215         print "  $conn->errorMessage\n";
 216         exit 1;
 217     }
 218
 219     # retrieve a list of db's from the server.
 220     my %db;
 221     foreach ($dbh->func('_ListTables')) {
 222         $db{$_} = 1;
 223     }
 224
 225     # Step 4.
 226     print "Step 4: Creating the tables.\n";
 227
 228     # factoid db.
 229     if (!exists $db{'factoids'}) {
 230         print "  creating new table factoids...\n";
 231
 232         $query = "CREATE TABLE factoids (".
 233                 "factoid_key varying(64) NOT NULL,".
 234
 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),".
 240
 241                 "modified_by character varying(192),".
 242                 "modified_time numeric(11,0),".
 243
 244                 "locked_by character varying(64),".
 245                 "locked_time numeric(11,0),".
 246
 247                 "factoid_value text NOT NULL,".
 248
 249                 "PRIMARY KEY (factoid_key)".
 250         ")";
 251
 252         &dbRaw("create(factoids)", $query);
 253     }
 254
 255     # freshmeat.
 256     if (!exists $db{'freshmeat'}) {
 257         print "  creating new table freshmeat...\n";
 258
 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,".
 272
 273                 "PRIMARY KEY (name)".
 274         ")";
 275
 276         &dbRaw("create(freshmeat)", $query);
 277     }
 278
 279     # karma.
 280     if (!exists $db{'karma'}) {
 281         print "  creating new table karma...\n";
 282
 283         $query = "CREATE TABLE karma (".
 284                 "nick character varying(20) NOT NULL,".
 285                 "karma numeric(5,0),".
 286
 287                 "PRIMARY KEY (nick)".
 288         ")";
 289
 290         &dbRaw("create(karma)", $query);
 291     }
 292
 293     # rootwarn.
 294     if (!exists $db{'rootwarn'}) {
 295         print "  creating new table rootwarn...\n";
 296
 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,".
 303
 304                 "PRIMARY KEY (nick)".
 305         ")";
 306
 307         &dbRaw("create(rootwarn)", $query);
 308     }
 309
 310     # seen.
 311     if (!exists $db{'seen'}) {
 312         print "  creating new table seen...\n";
 313
 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,".
 322
 323                 "PRIMARY KEY (nick)".
 324         ")";
 325
 326         &dbRaw("create(seen)", $query);
 327     }
 328 }
 329
 330 print "Done.\n";
 331
 332 &closeDB();