updated to accomodate new freshmeat table

[infobot.git] / scripts / setup_sql.pl

diff --git a/scripts/setup_sql.pl b/scripts/setup_sql.pl
index f7c66e39971e452101e0cb4ae6432387f45136c9..ec9a9107678778771b8df013269e62eb0d77612a 100755 (executable)
--- a/scripts/setup_sql.pl
+++ b/scripts/setup_sql.pl
@@ -48,9 +48,17 @@ if ($param{'DBType'} =~ /mysql/i) {
     # retrieve a list of db's from the server.
     my %db;
     foreach ($dbh->func('_ListTables')) {
+       &status("table => $_");
        $db{$_} = 1;
     }
 
+    # create database.
+    if (!scalar keys %db) {
+       &status("Creating database $param{'DBName'}...");
+       $query = "CREATE DATABASE $param{'DBName'}";
+       &dbRaw("create(db $param{'DBName'})", $query);
+    }
+
     # Step 4.
     print "Step 4: Creating the tables.\n";
 
@@ -86,20 +94,14 @@ if ($param{'DBType'} =~ /mysql/i) {
        print "  creating new table freshmeat...\n";
 
        $query = "CREATE TABLE freshmeat (".
-               "name VARCHAR(64) NOT NULL,".
-               "stable VARCHAR(32),".
-               "devel VARCHAR(32),".
-               "section VARCHAR(40),".
+               "projectname_short VARCHAR(64) NOT NULL,".
+               "latest_version VARCHAR(32) DEFAULT 'none' NOT NULL,".
                "license VARCHAR(32),".
-               "homepage VARCHAR(128),".
-               "download VARCHAR(128),".
-               "changelog VARCHAR(128),".
-               "deb VARCHAR(128),".
-               "rpm VARCHAR(128),".
-               "link CHAR(55),".
-               "oneliner VARCHAR(96) NOT NULL,".
+               "url_deb VARCHAR(128),".
+               "url_homepage VARCHAR(128),".
+               "desc_short VARCHAR(96) NOT NULL,".
 
-               "PRIMARY KEY (name)".
+               "PRIMARY KEY (projectname_short,latest_version)".
        ")";
 
        &dbRaw("create(freshmeat)", $query);
@@ -111,7 +113,7 @@ if ($param{'DBType'} =~ /mysql/i) {
 
        $query = "CREATE TABLE karma (".
                "nick VARCHAR(20) NOT NULL,".
-               "karma SMALLINT UNSIGNED,".
+               "karma SMALLINT,".
 
                "PRIMARY KEY (nick)".
        ")";
@@ -192,19 +194,16 @@ if ($param{'DBType'} =~ /mysql/i) {
 
     # grant.
     &status("  Granting user access to table.");
-    $query = "GRANT SELECT,INSERT,UPDATE,DELETE ON $dbname TO $param{'SQLUser'}";
-    &dbRaw("??", $query);
+    foreach ("factoids","seen","freshmeat") {
+       $query = "GRANT SELECT,INSERT,UPDATE,DELETE ON $dbname.$_ TO $param{'SQLUser'}";
+       &dbRaw("GRANT", $query);
+    }
 
     # flush.
     &status("Flushing privileges...");
     $query = "FLUSH PRIVILEGES";               # DOES NOT WORK on slink?
     &dbRaw("mysql(flush)", $query);
 
-    # create database.
-    &status("Creating database $param{'DBName'}...");
-    $query = "CREATE DATABASE $param{'DBName'}";
-    &dbRaw("create(db $param{'DBName'})", $query);
-
 } elsif ($param{'DBType'} =~ /pgsql|postgres/i) {
     if ($param{'DBType'} =~ /pgsql|postgres/i) { use Pg; } # for runtime.
     my $dbh = Pg::connectdb("dbname=$dbname");