major re-organisation of the different SQL files; cleanup and fixing of the postgres...

git-svn-id: https://svn.code.sf.net/p/infobot/code/trunk/blootbot@1312 c11ca15a-4712-0410-83d8-924469b57eb5

diff --git a/setup/pgsql/botmail.sql b/setup/pgsql/botmail.sql
new file mode 100644 (file)
index 0000000..c87c2e4
--- /dev/null
+++ b/setup/pgsql/botmail.sql
@@ -0,0 +1,12 @@
+CREATE TABLE botmail (
+    srcwho character varying(20) NOT NULL,
+    dstwho character varying(20) NOT NULL,
+    srcuh character varying(80) NOT NULL,
+    "time" numeric DEFAULT 0 NOT NULL,
+    msg text NOT NULL
+) WITHOUT OIDS;
+
+REVOKE ALL ON TABLE botmail FROM PUBLIC;
+
+ALTER TABLE ONLY botmail
+    ADD CONSTRAINT botmail_pkey PRIMARY KEY (srcwho, dstwho);

diff --git a/setup/pgsql/connections.sql b/setup/pgsql/connections.sql
new file mode 100644 (file)
index 0000000..d12244c
--- /dev/null
+++ b/setup/pgsql/connections.sql
@@ -0,0 +1,13 @@
+CREATE TABLE connections (
+    server character varying(30) NOT NULL,
+    port integer DEFAULT 6667 NOT NULL,
+    nick character varying(20) NOT NULL,
+    nickservpass character varying(8) NOT NULL,
+    ircname character varying(20) DEFAULT 'blootbot IRC bot'::character varying NOT NULL,
+    timeadded numeric DEFAULT 0
+) WITHOUT OIDS;
+
+REVOKE ALL ON TABLE connections FROM PUBLIC;
+
+ALTER TABLE ONLY connections
+    ADD CONSTRAINT connections_pkey PRIMARY KEY (server, port, nick);

diff --git a/setup/pgsql/factoids.sql b/setup/pgsql/factoids.sql
new file mode 100644 (file)
index 0000000..7fc8d79
--- /dev/null
+++ b/setup/pgsql/factoids.sql
@@ -0,0 +1,20 @@
+CREATE TABLE factoids (
+    factoid_key VARCHAR(64) NOT NULL,
+    requested_by VARCHAR(80) DEFAULT 'nobody' NOT NULL,
+    requested_time numeric(11) DEFAULT 0 NOT NULL,
+    requested_count numeric(5) DEFAULT 0 NOT NULL,
+    created_by VARCHAR(80),
+    created_time numeric(11) DEFAULT 0 NOT NULL,
+    modified_by VARCHAR(80),
+    modified_time numeric(11) DEFAULT 0 NOT NULL,
+    locked_by VARCHAR(80),
+    locked_time numeric(11) DEFAULT 0 NOT NULL,
+    factoid_value text NOT NULL
+) WITHOUT OIDS;
+
+REVOKE ALL ON TABLE factoids FROM PUBLIC;
+
+CREATE INDEX factoids_idx_fvalue ON factoids USING hash (factoid_value);
+
+ALTER TABLE ONLY factoids
+    ADD CONSTRAINT factoids_pkey_fkey PRIMARY KEY (factoid_key);

diff --git a/setup/pgsql/freshmeat.sql b/setup/pgsql/freshmeat.sql
new file mode 100644 (file)
index 0000000..873e2dd
--- /dev/null
+++ b/setup/pgsql/freshmeat.sql
@@ -0,0 +1,12 @@
+CREATE TABLE freshmeat (
+    projectname_short VARCHAR(64) NOT NULL,
+    latest_version VARCHAR(32) DEFAULT 'none'::VARCHAR NOT NULL,
+    license VARCHAR(32),
+    url_homepage VARCHAR(128),
+    desc_short VARCHAR(96) NOT NULL
+) WITHOUT OIDS;
+
+REVOKE ALL ON TABLE freshmeat FROM PUBLIC;
+
+ALTER TABLE ONLY freshmeat
+    ADD CONSTRAINT freshmeat_pkey PRIMARY KEY (projectname_short, latest_version);

diff --git a/setup/pgsql/news.sql b/setup/pgsql/news.sql
new file mode 100644 (file)
index 0000000..2924c61
--- /dev/null
+++ b/setup/pgsql/news.sql
@@ -0,0 +1,11 @@
+CREATE TABLE news (
+    channel VARCHAR(16) NOT NULL,
+    id numeric DEFAULT 0 NOT NULL,
+    "key" VARCHAR(16) NOT NULL,
+    value text NOT NULL
+) WITHOUT OIDS;
+
+REVOKE ALL ON TABLE news FROM PUBLIC;
+
+ALTER TABLE ONLY news
+    ADD CONSTRAINT news_pkey PRIMARY KEY (channel, id, "key");

diff --git a/setup/pgsql/onjoin.sql b/setup/pgsql/onjoin.sql
new file mode 100644 (file)
index 0000000..2e7ed75
--- /dev/null
+++ b/setup/pgsql/onjoin.sql
@@ -0,0 +1,12 @@
+CREATE TABLE onjoin (
+    nick VARCHAR(20) NOT NULL,
+    channel VARCHAR(16) NOT NULL,
+    message VARCHAR(255) NOT NULL,
+    modified_by VARCHAR(20) DEFAULT 'nobody' NOT NULL,
+    modified_time numeric DEFAULT 0 NOT NULL
+) WITHOUT OIDS;
+
+REVOKE ALL ON TABLE onjoin FROM PUBLIC;
+
+ALTER TABLE ONLY onjoin
+    ADD CONSTRAINT onjoin_pkey PRIMARY KEY (nick, channel);

diff --git a/setup/pgsql/rootwarn.sql b/setup/pgsql/rootwarn.sql
new file mode 100644 (file)
index 0000000..6a843d8
--- /dev/null
+++ b/setup/pgsql/rootwarn.sql
@@ -0,0 +1,12 @@
+CREATE TABLE rootwarn (
+    nick VARCHAR(20) NOT NULL,
+    attempt numeric,
+    "time" numeric NOT NULL,
+    host VARCHAR(80) NOT NULL,
+    channel VARCHAR(20) NOT NULL
+) WITHOUT OIDS;
+
+REVOKE ALL ON TABLE rootwarn FROM PUBLIC;
+
+ALTER TABLE ONLY rootwarn
+    ADD CONSTRAINT rootwarn_pkey PRIMARY KEY (nick);

diff --git a/setup/pgsql/seen.sql b/setup/pgsql/seen.sql
new file mode 100644 (file)
index 0000000..550f5bf
--- /dev/null
+++ b/setup/pgsql/seen.sql
@@ -0,0 +1,14 @@
+CREATE TABLE seen (
+    nick VARCHAR(20) NOT NULL,
+    "time" numeric NOT NULL,
+    channel VARCHAR(20) NOT NULL,
+    host VARCHAR(80) NOT NULL,
+    message text NOT NULL,
+    hehcount numeric DEFAULT 0 NOT NULL,
+    messagecount numeric DEFAULT 0 NOT NULL
+) WITHOUT OIDS;
+
+REVOKE ALL ON TABLE seen FROM PUBLIC;
+
+ALTER TABLE ONLY seen
+    ADD CONSTRAINT seen_pkey PRIMARY KEY (nick, channel);

diff --git a/setup/pgsql/stats.sql b/setup/pgsql/stats.sql
new file mode 100644 (file)
index 0000000..4af863d
--- /dev/null
+++ b/setup/pgsql/stats.sql
@@ -0,0 +1,12 @@
+CREATE TABLE stats (
+    nick VARCHAR(20) NOT NULL,
+    "type" VARCHAR(8) NOT NULL,
+    channel VARCHAR(16) DEFAULT 'PRIVATE' NOT NULL,
+    "time" numeric DEFAULT 0 NOT NULL,
+    counter numeric DEFAULT 0
+) WITHOUT OIDS;
+
+REVOKE ALL ON TABLE stats FROM PUBLIC;
+
+ALTER TABLE ONLY stats
+    ADD CONSTRAINT stats_pkey PRIMARY KEY (nick, "type", channel);

diff --git a/setup/pgsql/uptime.sql b/setup/pgsql/uptime.sql
new file mode 100644 (file)
index 0000000..49bcd63
--- /dev/null
+++ b/setup/pgsql/uptime.sql
@@ -0,0 +1,10 @@
+CREATE TABLE uptime (
+    uptime numeric DEFAULT 0,
+    endtime numeric DEFAULT 0,
+    string VARCHAR(128) NOT NULL
+) WITHOUT OIDS;
+
+REVOKE ALL ON TABLE uptime FROM PUBLIC;
+
+ALTER TABLE ONLY uptime
+    ADD CONSTRAINT uptime_pkey PRIMARY KEY (uptime);