From 0ef144e783640f511679d091e5371567bea14d8f Mon Sep 17 00:00:00 2001 From: dms 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 = ); + + # passwd. + system "stty -echo"; + print "Password: "; + chop(my $adminpass = ); + 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