From: simonraven Date: Sun, 11 Mar 2007 21:29:22 +0000 (+0000) Subject: major re-organisation of the different SQL files; cleanup and fixing of the postgres... X-Git-Url: https://git.donarmstrong.com/?p=infobot.git;a=commitdiff_plain;h=6a1a712731036e75ed57a29bdcb44cb646c83e91 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/blootbot@1312 c11ca15a-4712-0410-83d8-924469b57eb5 --- diff --git a/setup/pgsql/botmail.sql b/setup/pgsql/botmail.sql new file mode 100644 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 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 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 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 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 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 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 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 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 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);