From 61a897b18eaf868a6a2a51bcc26228766eab3a8e Mon Sep 17 00:00:00 2001 From: simonraven Date: Sun, 11 Mar 2007 21:35:40 +0000 Subject: [PATCH] major re-organisation of the different SQL files; cleanup and fixing of the postgres stuff -- troubled is da man git-svn-id: https://svn.code.sf.net/p/infobot/code/trunk@1315 c11ca15a-4712-0410-83d8-924469b57eb5 --- blootbot/setup/mysql/botmail.sql | 8 ++++++++ blootbot/setup/mysql/connections.sql | 9 +++++++++ blootbot/setup/mysql/factoids.sql | 14 ++++++++++++++ blootbot/setup/mysql/freshmeat.sql | 8 ++++++++ blootbot/setup/mysql/news.sql | 7 +++++++ blootbot/setup/mysql/onjoin.sql | 14 ++++++++++++++ blootbot/setup/mysql/rootwarn.sql | 8 ++++++++ blootbot/setup/mysql/seen.sql | 8 ++++++++ blootbot/setup/mysql/stats.sql | 8 ++++++++ blootbot/setup/mysql/uptime.sql | 6 ++++++ 10 files changed, 90 insertions(+) create mode 100644 blootbot/setup/mysql/botmail.sql create mode 100644 blootbot/setup/mysql/connections.sql create mode 100644 blootbot/setup/mysql/factoids.sql create mode 100644 blootbot/setup/mysql/freshmeat.sql create mode 100644 blootbot/setup/mysql/news.sql create mode 100644 blootbot/setup/mysql/onjoin.sql create mode 100644 blootbot/setup/mysql/rootwarn.sql create mode 100644 blootbot/setup/mysql/seen.sql create mode 100644 blootbot/setup/mysql/stats.sql create mode 100644 blootbot/setup/mysql/uptime.sql diff --git a/blootbot/setup/mysql/botmail.sql b/blootbot/setup/mysql/botmail.sql new file mode 100644 index 0000000..2789338 --- /dev/null +++ b/blootbot/setup/mysql/botmail.sql @@ -0,0 +1,8 @@ +CREATE TABLE botmail ( + srcwho VARCHAR(20) NOT NULL, + dstwho VARCHAR(20) NOT NULL, + srcuh VARCHAR(80) NOT NULL, + time INT UNSIGNED DEFAULT 'UNIX_TIMESTAMP()', + msg TEXT NOT NULL, + PRIMARY KEY (srcwho,dstwho) +); diff --git a/blootbot/setup/mysql/connections.sql b/blootbot/setup/mysql/connections.sql new file mode 100644 index 0000000..00dbf49 --- /dev/null +++ b/blootbot/setup/mysql/connections.sql @@ -0,0 +1,9 @@ +CREATE TABLE connections ( + server VARCHAR(30) NOT NULL, + port INT NOT NULL DEFAULT '6667', + nick VARCHAR(20) NOT NULL, + nickservpass VARCHAR(8) NOT NULL, + ircname VARCHAR (20) NOT NULL DEFAULT 'blootbot experimental bot', + timeadded INT UNSIGNED DEFAULT 'UNIX_TIMESTAMP()', + PRIMARY KEY (server,port,nick) +); diff --git a/blootbot/setup/mysql/factoids.sql b/blootbot/setup/mysql/factoids.sql new file mode 100644 index 0000000..d5189d0 --- /dev/null +++ b/blootbot/setup/mysql/factoids.sql @@ -0,0 +1,14 @@ +CREATE TABLE factoids ( + factoid_key VARCHAR(64) NOT NULL, + requested_by VARCHAR(64) NOT NULL DEFAULT 'nobody', + requested_time INT NOT NULL DEFAULT '0', + requested_count SMALLINT UNSIGNED NOT NULL DEFAULT '0', + created_by VARCHAR(64), + created_time INT NOT NULL DEFAULT '0', + modified_by VARCHAR(192), + modified_time INT NOT NULL DEFAULT '0', + locked_by VARCHAR(64), + locked_time INT NOT NULL DEFAULT '0', + factoid_value TEXT NOT NULL, + PRIMARY KEY (factoid_key) +); diff --git a/blootbot/setup/mysql/freshmeat.sql b/blootbot/setup/mysql/freshmeat.sql new file mode 100644 index 0000000..4b4f42b --- /dev/null +++ b/blootbot/setup/mysql/freshmeat.sql @@ -0,0 +1,8 @@ +CREATE TABLE freshmeat ( + projectname_short VARCHAR(64) NOT NULL, + latest_version VARCHAR(32) DEFAULT 'none' NOT NULL, + license VARCHAR(32), + url_homepage VARCHAR(128), + desc_short VARCHAR(96) NOT NULL, + PRIMARY KEY (projectname_short,latest_version) +); diff --git a/blootbot/setup/mysql/news.sql b/blootbot/setup/mysql/news.sql new file mode 100644 index 0000000..ebfb0e2 --- /dev/null +++ b/blootbot/setup/mysql/news.sql @@ -0,0 +1,7 @@ +CREATE TABLE news ( + channel VARCHAR(16) NOT NULL, + id INT UNSIGNED DEFAULT '0', + key VARCHAR(16) NOT NULL, + value TEXT NOT NULL, # limit to ~450 or so. + PRIMARY KEY (channel,id,key) +); diff --git a/blootbot/setup/mysql/onjoin.sql b/blootbot/setup/mysql/onjoin.sql new file mode 100644 index 0000000..994cc54 --- /dev/null +++ b/blootbot/setup/mysql/onjoin.sql @@ -0,0 +1,14 @@ +CREATE TABLE onjoin ( + nick VARCHAR(20) NOT NULL, + channel VARCHAR(16) NOT NULL, + message VARCHAR(255) NOT NULL, + modified_by VARCHAR(20) NOT NULL DEFAULT 'nobody', + modified_time INT NOT NULL DEFAULT '0', + PRIMARY KEY (nick, channel) +); + +-- v.2 -> v.3 +-- ALTER TABLE onjoin ADD COLUMN modified_by VARCHAR(20) NOT NULL DEFAULT 'nobody'; +-- ALTER TABLE onjoin ADD COLUMN modified_time INT NOT NULL DEFAULT '0'; +-- ** the following doesn't work for sqlite ** +-- ALTER TABLE onjoin ADD PRIMARY KEY (nick, channel); diff --git a/blootbot/setup/mysql/rootwarn.sql b/blootbot/setup/mysql/rootwarn.sql new file mode 100644 index 0000000..afcee2c --- /dev/null +++ b/blootbot/setup/mysql/rootwarn.sql @@ -0,0 +1,8 @@ +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/blootbot/setup/mysql/seen.sql b/blootbot/setup/mysql/seen.sql new file mode 100644 index 0000000..d920f79 --- /dev/null +++ b/blootbot/setup/mysql/seen.sql @@ -0,0 +1,8 @@ +CREATE TABLE seen ( + nick VARCHAR(20) NOT NULL, + time INT NOT NULL, + channel VARCHAR(20) NOT NULL, + host VARCHAR(80) NOT NULL, + message TINYTEXT NOT NULL, + PRIMARY KEY (nick,channel) +); diff --git a/blootbot/setup/mysql/stats.sql b/blootbot/setup/mysql/stats.sql new file mode 100644 index 0000000..97f773c --- /dev/null +++ b/blootbot/setup/mysql/stats.sql @@ -0,0 +1,8 @@ +CREATE TABLE stats ( + nick VARCHAR(20) NOT NULL, + type VARCHAR(8) NOT NULL, + channel VARCHAR(16) NOT NULL DEFAULT "PRIVATE", + time INT UNSIGNED DEFAULT 'UNIX_TIMESTAMP()', + counter SMALLINT UNSIGNED DEFAULT '0', + PRIMARY KEY (nick,type,channel) +); diff --git a/blootbot/setup/mysql/uptime.sql b/blootbot/setup/mysql/uptime.sql new file mode 100644 index 0000000..373902a --- /dev/null +++ b/blootbot/setup/mysql/uptime.sql @@ -0,0 +1,6 @@ +CREATE TABLE uptime ( + uptime INT UNSIGNED DEFAULT '0', # start. + endtime INT UNSIGNED DEFAULT '0', # end. + string VARCHAR(128) NOT NULL, + PRIMARY KEY (uptime) +); -- 2.39.2