* Fix SQL for broken MySQL schema

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

diff --git a/setup/mysql/botmail.sql b/setup/mysql/botmail.sql
index 2789338defddc531df50ea1701b74ce0e1dd3d5c..53031729d517ec0e0bcb837e7c2255fed219cccf 100644 (file)
--- a/setup/mysql/botmail.sql
+++ b/setup/mysql/botmail.sql
@@ -1,8 +1,12 @@
-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)
-);
+--
+-- Table structure for table `botmail`
+--
+
+CREATE TABLE `botmail` (
+  `srcwho` varchar(20) NOT NULL default '',
+  `dstwho` varchar(20) NOT NULL default '',
+  `srcuh` varchar(80) NOT NULL default '',
+  `time` int(10) unsigned default '0',
+  `msg` text NOT NULL,
+  PRIMARY KEY  (`srcwho`,`dstwho`)
+) TYPE=MyISAM;

diff --git a/setup/mysql/connections.sql b/setup/mysql/connections.sql
index d1256c17c258a0c2007026f7c920a7bf84f6868a..51ab71e6e3c62d2b5e1742319f453ed157aa942b 100644 (file)
--- a/setup/mysql/connections.sql
+++ b/setup/mysql/connections.sql
@@ -1,9 +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 'infobot experimental bot',
- timeadded INT UNSIGNED DEFAULT 'UNIX_TIMESTAMP()',
+ `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 'infobot',
+ `timeadded` INT UNSIGNED DEFAULT '0',
  PRIMARY KEY (server,port,nick)
 );

diff --git a/setup/mysql/factoids.sql b/setup/mysql/factoids.sql
index d5189d0d7a72c5e1342e94d8e1f311d0f1b27ce5..5b8e369e3300d31802d165070fbd806b82b44b9a 100644 (file)
--- a/setup/mysql/factoids.sql
+++ b/setup/mysql/factoids.sql
@@ -1,14 +1,18 @@
-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)
-);
+--
+-- Table structure for table `factoids`
+--
+
+CREATE TABLE `factoids` (
+  `factoid_key` varchar(64) NOT NULL,
+  `requested_by` varchar(100) NOT NULL default 'nobody',
+  `requested_time` int(11) NOT NULL default '0',
+  `requested_count` smallint(5) unsigned NOT NULL default '0',
+  `created_by` varchar(100) default NULL,
+  `created_time` int(11) NOT NULL default '0',
+  `modified_by` varchar(100) default NULL,
+  `modified_time` int(11) NOT NULL default '0',
+  `locked_by` varchar(100) default NULL,
+  `locked_time` int(11) NOT NULL default '0',
+  `factoid_value` text NOT NULL,
+  PRIMARY KEY  (`factoid_key`)
+) TYPE=MyISAM;

diff --git a/setup/mysql/freshmeat.sql b/setup/mysql/freshmeat.sql
index 4b4f42bed577df317f0156ae4c88f570c0a6885d..fc634188aaba163970aad47d09238cf7c9feb72a 100644 (file)
--- a/setup/mysql/freshmeat.sql
+++ b/setup/mysql/freshmeat.sql
@@ -1,8 +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)
+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/mysql/news.sql b/setup/mysql/news.sql
index 274e656499ffe4d02915e5c9a46526c5f34c784b..e2231f8f7c22babd9237a37c634db5edd20f3d86 100644 (file)
--- a/setup/mysql/news.sql
+++ b/setup/mysql/news.sql
@@ -1,7 +1,7 @@
-CREATE TABLE news (
- channel VARCHAR(30) 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)
+CREATE TABLE `news` (
+ `channel` VARCHAR(16) NOT NULL,
+ `id` INT UNSIGNED DEFAULT '0',
+ `key` VARCHAR(16) NOT NULL,
+ `value` TEXT NOT NULL,
+ PRIMARY KEY (`channel`,`id`,`key`)
 );

diff --git a/setup/mysql/onjoin.sql b/setup/mysql/onjoin.sql
index d3eb6d5760067b937e32ead1505d8d1bef45559d..63f351468404235295d9454aadc3aba9c7431c4b 100644 (file)
--- a/setup/mysql/onjoin.sql
+++ b/setup/mysql/onjoin.sql
@@ -1,14 +1,12 @@
-CREATE TABLE onjoin (
-       nick VARCHAR(20) NOT NULL,
-       channel VARCHAR(30) 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)
-);
+--
+-- Table structure for table `onjoin`
+--
 
--- 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);
+CREATE TABLE `onjoin` (
+  `nick` varchar(20) NOT NULL default '',
+  `channel` varchar(30) NOT NULL default '',
+  `message` varchar(255) NOT NULL default '',
+  `modified_by` varchar(20) NOT NULL default 'nobody',
+  `modified_time` int(11) NOT NULL default '0',
+  PRIMARY KEY  (`nick`,`channel`)
+) TYPE=MyISAM;

diff --git a/setup/mysql/rootwarn.sql b/setup/mysql/rootwarn.sql
index d3ea91282aa5aefa0d2c001166bd565c950e3e29..f5479eda05b6aadf63695027a1be61ad8b710104 100644 (file)
--- a/setup/mysql/rootwarn.sql
+++ b/setup/mysql/rootwarn.sql
@@ -1,8 +1,12 @@
-CREATE TABLE rootwarn (
- nick VARCHAR(20) NOT NULL,
- attempt SMALLINT UNSIGNED,
- time INT NOT NULL,
- host VARCHAR(80) NOT NULL,
- channel VARCHAR(30) NOT NULL,
- PRIMARY KEY (nick)
-);
+--
+-- Table structure for table `rootwarn`
+--
+
+CREATE TABLE `rootwarn` (
+  `nick` varchar(20) NOT NULL default '',
+  `attempt` smallint(5) unsigned default NULL,
+  `time` int(11) NOT NULL default '0',
+  `host` varchar(64) NOT NULL default '',
+  `channel` varchar(30) NOT NULL default '',
+  PRIMARY KEY  (`nick`)
+) TYPE=MyISAM;

diff --git a/setup/mysql/seen.sql b/setup/mysql/seen.sql
index 339803808448342c69dd965d025137f02add30b4..831e64dd73605154e3f55f43bc4e4f7426c8d178 100644 (file)
--- a/setup/mysql/seen.sql
+++ b/setup/mysql/seen.sql
@@ -1,8 +1,12 @@
-CREATE TABLE seen (
- nick VARCHAR(20) NOT NULL,
- time INT NOT NULL,
- channel VARCHAR(30) NOT NULL,
- host VARCHAR(80) NOT NULL,
- message TINYTEXT NOT NULL,
- PRIMARY KEY (nick,channel)
-);
+--
+-- Table structure for table `seen`
+--
+
+CREATE TABLE `seen` (
+  `nick` varchar(20) NOT NULL default '',
+  `time` int(11) NOT NULL default '0',
+  `channel` varchar(30) NOT NULL default '',
+  `host` varchar(64) NOT NULL default '',
+  `message` tinytext NOT NULL,
+  PRIMARY KEY  (`nick`,`channel`)
+) TYPE=MyISAM;

diff --git a/setup/mysql/stats.sql b/setup/mysql/stats.sql
index d738dc0d46b8c742a27bc6b54c113d74553235c0..442817f3ce7e8cfd6bf7b63412f7625915d3b2c8 100644 (file)
--- a/setup/mysql/stats.sql
+++ b/setup/mysql/stats.sql
@@ -1,8 +1,12 @@
-CREATE TABLE stats (
- nick VARCHAR(20) NOT NULL,
- type VARCHAR(8) NOT NULL,
- channel VARCHAR(30) NOT NULL DEFAULT "PRIVATE",
- time INT UNSIGNED DEFAULT 'UNIX_TIMESTAMP()',
- counter SMALLINT UNSIGNED DEFAULT '0',
- PRIMARY KEY (nick,type,channel)
-);
+--
+-- Table structure for table `stats`
+--
+
+CREATE TABLE `stats` (
+  `nick` varchar(20) NOT NULL default '',
+  `type` varchar(8) NOT NULL default '',
+  `channel` varchar(30) NOT NULL default 'PRIVATE',
+  `time` int(10) unsigned default '0',
+  `counter` smallint(5) unsigned default '0',
+  PRIMARY KEY  (`nick`,`type`,`channel`)
+) TYPE=MyISAM;

diff --git a/setup/mysql/uptime.sql b/setup/mysql/uptime.sql
index 373902ad7d2aec7b4b5d866edf9a670103c58684..79ebab70ac6bb8bd72dd7e233d77e69d65caeb2f 100644 (file)
--- a/setup/mysql/uptime.sql
+++ b/setup/mysql/uptime.sql
@@ -1,6 +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)
+CREATE TABLE `uptime` (
+ `uptime` INT UNSIGNED DEFAULT '0', # start.
+ `endtime` INT UNSIGNED DEFAULT '0', # end.
+ `string` VARCHAR(128) NOT NULL,
+ PRIMARY KEY (`uptime`)
 );