From 0ef144e783640f511679d091e5371567bea14d8f Mon Sep 17 00:00:00 2001
From: dms <dms@c11ca15a-4712-0410-83d8-924469b57eb5>
Date: Sun, 13 May 2001 13:18:37 +0000
Subject: [PATCH] - added "setup/" directory with all the tables in separate
 files.

git-svn-id: https://svn.code.sf.net/p/infobot/code/trunk/blootbot@486 c11ca15a-4712-0410-83d8-924469b57eb5
---
 setup/factoids.sql  |  19 ++++
 setup/freshmeat.sql |  10 ++
 setup/karma.sql     |   6 ++
 setup/rootwarn.sql  |   9 ++
 setup/seen.sql      |  11 +++
 setup/setup.pl      | 218 ++++++++++++++++++++++++++++++++++++++++++++
 6 files changed, 273 insertions(+)
 create mode 100644 setup/factoids.sql
 create mode 100644 setup/freshmeat.sql
 create mode 100644 setup/karma.sql
 create mode 100644 setup/rootwarn.sql
 create mode 100644 setup/seen.sql
 create mode 100755 setup/setup.pl

diff --git a/setup/factoids.sql b/setup/factoids.sql
new file mode 100644
index 0000000..18dcc97
--- /dev/null
+++ b/setup/factoids.sql
@@ -0,0 +1,19 @@
+CREATE TABLE factoids (".
+	factoid_key VARCHAR(64) NOT NULL,
+
+	requested_by VARCHAR(64),
+	requested_time INT,
+	requested_count SMALLINT UNSIGNED,
+	created_by VARCHAR(64),
+	created_time INT,
+
+	modified_by VARCHAR(192),
+	modified_time INT,
+
+	locked_by VARCHAR(64),
+	locked_time INT,
+
+	factoid_value TEXT NOT NULL,
+
+	PRIMARY KEY (factoid_key)
+);
diff --git a/setup/freshmeat.sql b/setup/freshmeat.sql
new file mode 100644
index 0000000..d77e521
--- /dev/null
+++ b/setup/freshmeat.sql
@@ -0,0 +1,10 @@
+CREATE TABLE freshmeat (
+	projectname_short VARCHAR(64) NOT NULL,
+	latest_version VARCHAR(32) DEFAULT 'none' NOT NULL,
+	license VARCHAR(32),
+	url_deb VARCHAR(128),
+	url_homepage VARCHAR(128),
+	desc_short VARCHAR(96) NOT NULL,
+
+	PRIMARY KEY (projectname_short,latest_version)
+);
diff --git a/setup/karma.sql b/setup/karma.sql
new file mode 100644
index 0000000..21c5db7
--- /dev/null
+++ b/setup/karma.sql
@@ -0,0 +1,6 @@
+CREATE TABLE karma (
+	nick VARCHAR(20) NOT NULL,
+	karma SMALLINT,
+
+	PRIMARY KEY (nick)
+);
diff --git a/setup/rootwarn.sql b/setup/rootwarn.sql
new file mode 100644
index 0000000..bab4ec2
--- /dev/null
+++ b/setup/rootwarn.sql
@@ -0,0 +1,9 @@
+CREATE TABLE rootwarn (
+	nick VARCHAR(20) NOT NULL,
+	attempt SMALLINT UNSIGNED,
+	time INT NOT NULL,
+	host VARCHAR(80) NOT NULL,
+	channel VARCHAR(20) NOT NULL,
+
+	PRIMARY KEY (nick)
+);
diff --git a/setup/seen.sql b/setup/seen.sql
new file mode 100644
index 0000000..2672ba6
--- /dev/null
+++ b/setup/seen.sql
@@ -0,0 +1,11 @@
+CREATE TABLE seen (
+	nick VARCHAR(20) NOT NULL,
+	time INT NOT NULL,
+	channel VARCHAR(20) NOT NULL,
+	host VARCHAR(80) NOT NULL,
+	messagecount SMALLINT UNSIGNED,
+	hehcount SMALLINT UNSIGNED,
+	message TINYTEXT NOT NULL,
+
+	PRIMARY KEY (nick)
+);
diff --git a/setup/setup.pl b/setup/setup.pl
new file mode 100755
index 0000000..049122a
--- /dev/null
+++ b/setup/setup.pl
@@ -0,0 +1,218 @@
+#!/usr/bin/perl
+# setup_tables: setup MYSQL/PGSQL side of things for blootbot.
+# written by the xk.
+###
+
+require "src/core.pl";
+require "src/logger.pl";
+require "src/modules.pl";
+require "src/Misc.pl";
+require "src/interface.pl";
+
+$bot_src_dir = "./src/";
+
+# read param stuff from blootbot.config.
+&loadConfig("files/blootbot.config");
+&loadDBModules();
+my $dbname = $param{'DBName'};
+my $query;
+
+if ($dbname eq "") {
+  print "error: appears that teh config file was not loaded properly.\n";
+  exit 1;
+}
+
+if ($param{'DBType'} =~ /mysql/i) {
+    use DBI;
+
+    print "Enter root information...\n";
+    # username.
+    print "Username: ";
+    chop (my $adminuser = <STDIN>);
+
+    # passwd.
+    system "stty -echo";
+    print "Password: ";
+    chop(my $adminpass = <STDIN>);
+    print "\n";
+    system "stty echo";
+
+    if ($adminuser eq "" or $adminpass eq "") {
+	&ERROR("error: adminuser || adminpass is NULL.");
+	exit 1;
+    }
+
+    &openDB("mysql", $adminuser, $adminpass);
+
+    &status("Creating db ...");
+    &dbRaw("CREATE DATABASE $param{'DBName'}");
+
+    # Step 1.
+    &status("Step 1: Adding user information.");
+
+    # Step 2.
+    if (!&dbGet("user","user",$param{'SQLUser'},"user")) {
+	&status("  Adding user $param{'SQLUser'} $dbname/user table...");
+
+	$query = "INSERT INTO user VALUES ".
+		"('localhost', '$param{'SQLUser'}', ".
+		"password('$param{'SQLPass'}'), ";
+
+	$query .= "'Y','Y','Y','Y','Y','Y','N','N','N','N','N','N','N','N')";
+###	$query .= "'Y','Y','Y','Y','N','N','N','N','N','N')";
+
+	&dbRaw("create(user)", $query);
+    }
+
+    # Step 3. what's this for?
+    if (!&dbGet("db","db",$param{'SQLUser'},"db")) {
+	&status("  Adding 'db' entry");
+
+	$query = "INSERT INTO db VALUES ".
+		"('localhost', '$dbname', ".
+		"'$param{'SQLUser'}', ";
+
+	$query .= "'Y','Y','Y','Y','Y','Y','Y','N','N','N')";
+###	$query .= "'Y','Y','Y','Y','Y','N')";
+
+	&dbRaw("create(db)", $query);
+    }
+
+    # grant.
+    &status("  Granting user access to table.");
+    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);
+
+} elsif ($param{'DBType'} =~ /pgsql|postgres/i) {
+    if ($param{'DBType'} =~ /pgsql|postgres/i) { use Pg; } # for runtime.
+    my $dbh = Pg::connectdb("dbname=$dbname");
+
+    if (PGRES_CONNECTION_OK eq $conn->status) {
+	print "  opened mysql connection to $param{'mysqlHost'}\n";
+    } else {
+	print "  error: cannot connect to $param{'mysqlHost'}.\n";
+	print "  $conn->errorMessage\n";
+	exit 1;
+    }
+
+    # retrieve a list of db's from the server.
+    my %db;
+    foreach ($dbh->func('_ListTables')) {
+	$db{$_} = 1;
+    }
+
+    # Step 4.
+    print "Step 4: Creating the tables.\n";
+
+    # factoid db.
+    if (!exists $db{'factoids'}) {
+	print "  creating new table factoids...\n";
+
+	$query = "CREATE TABLE factoids (".
+		"factoid_key varying(64) NOT NULL,".
+
+		"requested_by varying(64),".
+		"requested_time numeric(11,0),".
+		"requested_count numeric(5,0),".
+		"created_by varying(64),".
+		"created_time numeric(11,0),".
+
+		"modified_by character varying(192),".
+		"modified_time numeric(11,0),".
+
+		"locked_by character varying(64),".
+		"locked_time numeric(11,0),".
+
+		"factoid_value text NOT NULL,".
+
+		"PRIMARY KEY (factoid_key)".
+	")";
+
+	&dbRaw("create(factoids)", $query);
+    }
+
+    # freshmeat.
+    if (!exists $db{'freshmeat'}) {
+	print "  creating new table freshmeat...\n";
+
+	$query = "CREATE TABLE freshmeat (".
+		"name charcter varying(64) NOT NULL,".
+		"stable character varying(32),".
+		"devel character varying(32),".
+		"section character varying(40),".
+		"license character varying(32),".
+		"homepage character varying(128),".
+		"download character varying(128),".
+		"changelog character varying(128),".
+		"deb character varying(128),".
+		"rpm character varying(128),".
+		"link character varying(55),".
+		"oneliner character varying(96) NOT NULL,".
+
+		"PRIMARY KEY (name)".
+	")";
+
+	&dbRaw("create(freshmeat)", $query);
+    }
+
+    # karma.
+    if (!exists $db{'karma'}) {
+	print "  creating new table karma...\n";
+
+	$query = "CREATE TABLE karma (".
+		"nick character varying(20) NOT NULL,".
+		"karma numeric(5,0),".
+
+		"PRIMARY KEY (nick)".
+	")";
+
+	&dbRaw("create(karma)", $query);
+    }
+
+    # rootwarn.
+    if (!exists $db{'rootwarn'}) {
+	print "  creating new table rootwarn...\n";
+
+	$query = "CREATE TABLE rootwarn (".
+		"nick character varying(20) NOT NULL,".
+		"attempt numeric(5,0),".
+		"time numeric(11,0) NOT NULL,".
+		"host character varying(80) NOT NULL,".
+		"channel character varying(20) NOT NULL,".
+
+		"PRIMARY KEY (nick)".
+	")";
+
+	&dbRaw("create(rootwarn)", $query);
+    }
+
+    # seen.
+    if (!exists $db{'seen'}) {
+	print "  creating new table seen...\n";
+
+	$query = "CREATE TABLE seen (".
+		"nick character varying(20) NOT NULL,".
+		"time numeric(11,0) NOT NULL,".
+		"channel character varying(20) NOT NULL,".
+		"host character varying(80) NOT NULL,".
+		"messagecount numeric(5,0),".
+		"hehcount numeric(5,0),".
+		"message text NOT NULL,".
+
+		"PRIMARY KEY (nick)".
+	")";
+
+	&dbRaw("create(seen)", $query);
+    }
+}
+
+print "Done.\n";
+
+&closeDB();
-- 
2.39.5