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@1313 c11ca15a-4712-0410-83d8-924469b57eb5

diff --git a/setup/sqlite/botmail.sql b/setup/sqlite/botmail.sql
new file mode 100644 (file)
index 0000000..2789338
--- /dev/null
+++ b/setup/sqlite/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/setup/sqlite/connections.sql b/setup/sqlite/connections.sql
new file mode 100644 (file)
index 0000000..00dbf49
--- /dev/null
+++ b/setup/sqlite/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/setup/sqlite/factoids.sql b/setup/sqlite/factoids.sql
new file mode 100644 (file)
index 0000000..d5189d0
--- /dev/null
+++ b/setup/sqlite/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/setup/sqlite/freshmeat.sql b/setup/sqlite/freshmeat.sql
new file mode 100644 (file)
index 0000000..4b4f42b
--- /dev/null
+++ b/setup/sqlite/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/setup/sqlite/news.sql b/setup/sqlite/news.sql
new file mode 100644 (file)
index 0000000..ebfb0e2
--- /dev/null
+++ b/setup/sqlite/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/setup/sqlite/onjoin.sql b/setup/sqlite/onjoin.sql
new file mode 100644 (file)
index 0000000..994cc54
--- /dev/null
+++ b/setup/sqlite/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/setup/sqlite/rootwarn.sql b/setup/sqlite/rootwarn.sql
new file mode 100644 (file)
index 0000000..afcee2c
--- /dev/null
+++ b/setup/sqlite/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/setup/sqlite/seen.sql b/setup/sqlite/seen.sql
new file mode 100644 (file)
index 0000000..d920f79
--- /dev/null
+++ b/setup/sqlite/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/setup/sqlite/stats.sql b/setup/sqlite/stats.sql
new file mode 100644 (file)
index 0000000..97f773c
--- /dev/null
+++ b/setup/sqlite/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/setup/sqlite/uptime.sql b/setup/sqlite/uptime.sql
new file mode 100644 (file)
index 0000000..373902a
--- /dev/null
+++ b/setup/sqlite/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)
+);