* Moved all VARCHAR fields to 100 size for easier legacy db upgrades (should handle...

* Removed pgsql specific REVOKE from PUBLIC sql (users should make that decision)
* Removed NOT NULL constraint from all but requested_count in factoids table
* Most fields now default to NULL to allow for easier finding of problems, DB cleanup and importing of inconsisting
DBs

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

diff --git a/setup/mysql/factoids.sql b/setup/mysql/factoids.sql
index 5b8e369e3300d31802d165070fbd806b82b44b9a..80c3a2ff3f6dfa1ac0bc8760b58b759ebc47718c 100644 (file)
--- a/setup/mysql/factoids.sql
+++ b/setup/mysql/factoids.sql
@@ -3,16 +3,16 @@
 --
 
 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',
+  `factoid_key` varchar(100) NOT NULL,
+  `requested_by` varchar(100) default NULL,
+  `requested_time` int(11) default NULL,
   `requested_count` smallint(5) unsigned NOT NULL default '0',
   `created_by` varchar(100) default NULL,
-  `created_time` int(11) NOT NULL default '0',
+  `created_time` int(11) default NULL,
   `modified_by` varchar(100) default NULL,
-  `modified_time` int(11) NOT NULL default '0',
+  `modified_time` int(11) default NULL,
   `locked_by` varchar(100) default NULL,
-  `locked_time` int(11) NOT NULL default '0',
+  `locked_time` int(11) default NULL,
   `factoid_value` text NOT NULL,
   PRIMARY KEY  (`factoid_key`)
 ) TYPE=MyISAM;

diff --git a/setup/pgsql/factoids.sql b/setup/pgsql/factoids.sql
index 7fc8d79231b957409f5d555c8cd889fc371f5bb3..b64924f2fec4fa02cef6fc02a0516e2f0af81d8e 100644 (file)
--- a/setup/pgsql/factoids.sql
+++ b/setup/pgsql/factoids.sql
@@ -1,19 +1,17 @@
 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,
+    factoid_key VARCHAR(100) NOT NULL,
+    requested_by VARCHAR(100) DEFAULT NULL,
+    requested_time numeric(11) DEFAULT 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,
+    created_by VARCHAR(100) DEFAULT NULL,
+    created_time numeric(11) DEFAULT NULL,
+    modified_by VARCHAR(100) DEFAULT NULL,
+    modified_time numeric(11) DEFAULT NULL,
+    locked_by VARCHAR(100) DEFAULT NULL,
+    locked_time numeric(11) DEFAULT 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

diff --git a/setup/sqlite/factoids.sql b/setup/sqlite/factoids.sql
index d5189d0d7a72c5e1342e94d8e1f311d0f1b27ce5..8eb38b61df4c02cde734c4f9a6b44f37eb6349b7 100644 (file)
--- a/setup/sqlite/factoids.sql
+++ b/setup/sqlite/factoids.sql
@@ -1,14 +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',
+ factoid_key VARCHAR(100) NOT NULL,
+ requested_by VARCHAR(100) DEFAULT NULL,
+ requested_time INT DEFAULT NULL,
  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',
+ created_by VARCHAR(100) DEFAULT NULL,
+ created_time INT DEFAULT NULL,
+ modified_by VARCHAR(100) DEFAULT NULL,
+ modified_time INT DEFAULT NULL,
+ locked_by VARCHAR(100) DEFAULT NULL,
+ locked_time INT DEFAULT NULL,
  factoid_value TEXT NOT NULL,
  PRIMARY KEY (factoid_key)
 );

diff --git a/setup/sqlite2/factoids.sql b/setup/sqlite2/factoids.sql
index 05862b8678fe651e10918de1c2813b961ccc8d1f..8eb38b61df4c02cde734c4f9a6b44f37eb6349b7 100644 (file)
--- a/setup/sqlite2/factoids.sql
+++ b/setup/sqlite2/factoids.sql
@@ -1,14 +1,14 @@
 CREATE TABLE factoids (
- factoid_key VARCHAR(64) NOT NULL,
- requested_by VARCHAR(64),
- requested_time INT NOT NULL DEFAULT '0',
+ factoid_key VARCHAR(100) NOT NULL,
+ requested_by VARCHAR(100) DEFAULT NULL,
+ requested_time INT DEFAULT NULL,
  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',
+ created_by VARCHAR(100) DEFAULT NULL,
+ created_time INT DEFAULT NULL,
+ modified_by VARCHAR(100) DEFAULT NULL,
+ modified_time INT DEFAULT NULL,
+ locked_by VARCHAR(100) DEFAULT NULL,
+ locked_time INT DEFAULT NULL,
  factoid_value TEXT NOT NULL,
  PRIMARY KEY (factoid_key)
 );