From 1a335c53cb37dd0b8b793914a55bddc0e911b6f5 Mon Sep 17 00:00:00 2001 From: djmcgrath Date: Tue, 18 Mar 2008 21:50:09 +0000 Subject: [PATCH] * Moved all VARCHAR fields to 100 size for easier legacy db upgrades (should handle max freenode sizes and then some) * 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 --- setup/mysql/factoids.sql | 12 ++++++------ setup/pgsql/factoids.sql | 20 +++++++++----------- setup/sqlite/factoids.sql | 18 +++++++++--------- setup/sqlite2/factoids.sql | 18 +++++++++--------- 4 files changed, 33 insertions(+), 35 deletions(-) diff --git a/setup/mysql/factoids.sql b/setup/mysql/factoids.sql index 5b8e369..80c3a2f 100644 --- 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 7fc8d79..b64924f 100644 --- 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 d5189d0..8eb38b6 100644 --- 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 05862b8..8eb38b6 100644 --- 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) ); -- 2.39.5