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     # create database.
  55     &status("Creating database $param{'DBName'}...");
  56     $query = "CREATE DATABASE $param{'DBName'}";
  57     &dbRaw("create(db $param{'DBName'})", $query);
  58
  59     # Step 4.
  60     print "Step 4: Creating the tables.\n";
  61
  62     # factoid db.
  63     if (!exists $db{'factoids'}) {
  64         print "  creating new table factoids...\n";
  65
  66         $query = "CREATE TABLE factoids (".
  67                 "factoid_key VARCHAR(64) NOT NULL,".
  68
  69                 "requested_by VARCHAR(64),".
  70                 "requested_time INT,".
  71                 "requested_count SMALLINT UNSIGNED,".
  72                 "created_by VARCHAR(64),".
  73                 "created_time INT,".
  74
  75                 "modified_by VARCHAR(192),".
  76                 "modified_time INT,".
  77
  78                 "locked_by VARCHAR(64),".
  79                 "locked_time INT,".
  80
  81                 "factoid_value TEXT NOT NULL,".
  82
  83                 "PRIMARY KEY (factoid_key)".
  84         ")";
  85
  86         &dbRaw("create(factoids)", $query);
  87     }
  88
  89     # freshmeat.
  90     if (!exists $db{'freshmeat'}) {
  91         print "  creating new table freshmeat...\n";
  92
  93         $query = "CREATE TABLE freshmeat (".
  94                 "name VARCHAR(64) NOT NULL,".
  95                 "stable VARCHAR(32),".
  96                 "devel VARCHAR(32),".
  97                 "section VARCHAR(40),".
  98                 "license VARCHAR(32),".
  99                 "homepage VARCHAR(128),".
 100                 "download VARCHAR(128),".
 101                 "changelog VARCHAR(128),".
 102                 "deb VARCHAR(128),".
 103                 "rpm VARCHAR(128),".
 104                 "link CHAR(55),".
 105                 "oneliner VARCHAR(96) NOT NULL,".
 106
 107                 "PRIMARY KEY (name)".
 108         ")";
 109
 110         &dbRaw("create(freshmeat)", $query);
 111     }
 112
 113     # karma.
 114     if (!exists $db{'karma'}) {
 115         print "  creating new table karma...\n";
 116
 117         $query = "CREATE TABLE karma (".
 118                 "nick VARCHAR(20) NOT NULL,".
 119                 "karma SMALLINT UNSIGNED,".
 120
 121                 "PRIMARY KEY (nick)".
 122         ")";
 123
 124         &dbRaw("create(karma)", $query);
 125     }
 126
 127     # rootwarn.
 128     if (!exists $db{'rootwarn'}) {
 129         print "  creating new table rootwarn...\n";
 130
 131         $query = "CREATE TABLE rootwarn (".
 132                 "nick VARCHAR(20) NOT NULL,".
 133                 "attempt SMALLINT UNSIGNED,".
 134                 "time INT NOT NULL,".
 135                 "host VARCHAR(80) NOT NULL,".
 136                 "channel VARCHAR(20) NOT NULL,".
 137
 138                 "PRIMARY KEY (nick)".
 139         ")";
 140
 141         &dbRaw("create(rootwarn)", $query);
 142     }
 143
 144     # seen.
 145     if (!exists $db{'seen'}) {
 146         print "  creating new table seen...\n";
 147
 148         $query = "CREATE TABLE seen (".
 149                 "nick VARCHAR(20) NOT NULL,".
 150                 "time INT NOT NULL,".
 151                 "channel VARCHAR(20) NOT NULL,".
 152                 "host VARCHAR(80) NOT NULL,".
 153                 "messagecount SMALLINT UNSIGNED,".
 154                 "hehcount SMALLINT UNSIGNED,".
 155                 "message TINYTEXT NOT NULL,".
 156
 157                 "PRIMARY KEY (nick)".
 158         ")";
 159
 160         &dbRaw("create(seen)", $query);
 161     }
 162
 163     ### USER SETUP.
 164     &closeDB();
 165     &openDB("mysql", $adminuser, $adminpass);
 166
 167     # Step 1.
 168     &status("Step 1: Adding user information.");
 169
 170     # Step 2.
 171     if (!&dbGet("user","user",$param{'SQLUser'},"user")) {
 172         &status("  Adding user $param{'SQLUser'} $dbname/user table...");
 173
 174         $query = "INSERT INTO user VALUES ".
 175                 "('localhost', '$param{'SQLUser'}', ".
 176                 "password('$param{'SQLPass'}'), ";
 177
 178         $query .= "'Y','Y','Y','Y','N','N','N','N','N','N','N','N','N','N')";
 179 ###     $query .= "'Y','Y','Y','Y','N','N','N','N','N','N')";
 180
 181         &dbRaw("create(user)", $query);
 182     }
 183
 184     # Step 3. what's this for?
 185     if (!&dbGet("db","db",$param{'SQLUser'},"db")) {
 186         &status("  Adding 'db' entry");
 187
 188         $query = "INSERT INTO db VALUES ".
 189                 "('localhost', '$dbname', ".
 190                 "'$param{'SQLUser'}', ";
 191
 192         $query .= "'Y','Y','Y','Y','Y','N','N','N','N','N')";
 193 ###     $query .= "'Y','Y','Y','Y','Y','N')";
 194
 195         &dbRaw("create(db)", $query);
 196     }
 197
 198     # grant.
 199     &status("  Granting user access to table.");
 200     foreach ("factoids","seen","freshmeat") {
 201         $query = "GRANT SELECT,INSERT,UPDATE,DELETE ON $dbname.$_ TO $param{'SQLUser'}";
 202         &dbRaw("GRANT", $query);
 203     }
 204
 205     # flush.
 206     &status("Flushing privileges...");
 207     $query = "FLUSH PRIVILEGES";                # DOES NOT WORK on slink?
 208     &dbRaw("mysql(flush)", $query);
 209
 210 } elsif ($param{'DBType'} =~ /pgsql|postgres/i) {
 211     if ($param{'DBType'} =~ /pgsql|postgres/i) { use Pg; } # for runtime.
 212     my $dbh = Pg::connectdb("dbname=$dbname");
 213
 214     if (PGRES_CONNECTION_OK eq $conn->status) {
 215         print "  opened mysql connection to $param{'mysqlHost'}\n";
 216     } else {
 217         print "  error: cannot connect to $param{'mysqlHost'}.\n";
 218         print "  $conn->errorMessage\n";
 219         exit 1;
 220     }
 221
 222     # retrieve a list of db's from the server.
 223     my %db;
 224     foreach ($dbh->func('_ListTables')) {
 225         $db{$_} = 1;
 226     }
 227
 228     # Step 4.
 229     print "Step 4: Creating the tables.\n";
 230
 231     # factoid db.
 232     if (!exists $db{'factoids'}) {
 233         print "  creating new table factoids...\n";
 234
 235         $query = "CREATE TABLE factoids (".
 236                 "factoid_key varying(64) NOT NULL,".
 237
 238                 "requested_by varying(64),".
 239                 "requested_time numeric(11,0),".
 240                 "requested_count numeric(5,0),".
 241                 "created_by varying(64),".
 242                 "created_time numeric(11,0),".
 243
 244                 "modified_by character varying(192),".
 245                 "modified_time numeric(11,0),".
 246
 247                 "locked_by character varying(64),".
 248                 "locked_time numeric(11,0),".
 249
 250                 "factoid_value text NOT NULL,".
 251
 252                 "PRIMARY KEY (factoid_key)".
 253         ")";
 254
 255         &dbRaw("create(factoids)", $query);
 256     }
 257
 258     # freshmeat.
 259     if (!exists $db{'freshmeat'}) {
 260         print "  creating new table freshmeat...\n";
 261
 262         $query = "CREATE TABLE freshmeat (".
 263                 "name charcter varying(64) NOT NULL,".
 264                 "stable character varying(32),".
 265                 "devel character varying(32),".
 266                 "section character varying(40),".
 267                 "license character varying(32),".
 268                 "homepage character varying(128),".
 269                 "download character varying(128),".
 270                 "changelog character varying(128),".
 271                 "deb character varying(128),".
 272                 "rpm character varying(128),".
 273                 "link character varying(55),".
 274                 "oneliner character varying(96) NOT NULL,".
 275
 276                 "PRIMARY KEY (name)".
 277         ")";
 278
 279         &dbRaw("create(freshmeat)", $query);
 280     }
 281
 282     # karma.
 283     if (!exists $db{'karma'}) {
 284         print "  creating new table karma...\n";
 285
 286         $query = "CREATE TABLE karma (".
 287                 "nick character varying(20) NOT NULL,".
 288                 "karma numeric(5,0),".
 289
 290                 "PRIMARY KEY (nick)".
 291         ")";
 292
 293         &dbRaw("create(karma)", $query);
 294     }
 295
 296     # rootwarn.
 297     if (!exists $db{'rootwarn'}) {
 298         print "  creating new table rootwarn...\n";
 299
 300         $query = "CREATE TABLE rootwarn (".
 301                 "nick character varying(20) NOT NULL,".
 302                 "attempt numeric(5,0),".
 303                 "time numeric(11,0) NOT NULL,".
 304                 "host character varying(80) NOT NULL,".
 305                 "channel character varying(20) NOT NULL,".
 306
 307                 "PRIMARY KEY (nick)".
 308         ")";
 309
 310         &dbRaw("create(rootwarn)", $query);
 311     }
 312
 313     # seen.
 314     if (!exists $db{'seen'}) {
 315         print "  creating new table seen...\n";
 316
 317         $query = "CREATE TABLE seen (".
 318                 "nick character varying(20) NOT NULL,".
 319                 "time numeric(11,0) NOT NULL,".
 320                 "channel character varying(20) NOT NULL,".
 321                 "host character varying(80) NOT NULL,".
 322                 "messagecount numeric(5,0),".
 323                 "hehcount numeric(5,0),".
 324                 "message text NOT NULL,".
 325
 326                 "PRIMARY KEY (nick)".
 327         ")";
 328
 329         &dbRaw("create(seen)", $query);
 330     }
 331 }
 332
 333 print "Done.\n";
 334
 335 &closeDB();