From b8547480b7c7158bbc50f29ab665a2b5c3090523 Mon Sep 17 00:00:00 2001 From: stronk7 Date: Sat, 23 Sep 2006 09:37:53 +0000 Subject: [PATCH] Moving a bunch of database module fields to NULLable (http://docs.moodle.org/en/XMLDB_Problems#NOT_NULL_fields_using_a_DEFAULT_.27.27_clause) --- mod/data/db/install.xml | 50 +++++++++++++------------- mod/data/db/mysql.php | 31 +++++++++++++++- mod/data/db/mysql.sql | 48 ++++++++++++------------- mod/data/db/postgres7.php | 76 ++++++++++++++++++++++++++++++++++++++- mod/data/db/postgres7.sql | 48 ++++++++++++------------- 5 files changed, 178 insertions(+), 75 deletions(-) diff --git a/mod/data/db/install.xml b/mod/data/db/install.xml index 7ae8515696..ba49e9f8db 100644 --- a/mod/data/db/install.xml +++ b/mod/data/db/install.xml @@ -1,5 +1,5 @@ - @@ -19,15 +19,15 @@ - - - - - - - - - + + + + + + + + + @@ -46,16 +46,16 @@ - - - - - - - - - - + + + + + + + + + + @@ -82,11 +82,11 @@ - - - - - + + + + + diff --git a/mod/data/db/mysql.php b/mod/data/db/mysql.php index 354d0a189c..6980635574 100644 --- a/mod/data/db/mysql.php +++ b/mod/data/db/mysql.php @@ -107,8 +107,37 @@ function data_upgrade($oldversion) { modify_database('', 'ALTER TABLE prefix_data DROP COLUMN ratings;'); } + + if ($oldversion < 2006092302) { // Changing some TEXT fields to NULLable and no default + execute_sql("ALTER TABLE {$CFG->prefix}data CHANGE singletemplate singletemplate text NULL AFTER rssarticles"); + execute_sql("ALTER TABLE {$CFG->prefix}data CHANGE listtemplate listtemplate text NULL AFTER singletemplate"); + execute_sql("ALTER TABLE {$CFG->prefix}data CHANGE listtemplateheader listtemplateheader text NULL AFTER listtemplate"); + execute_sql("ALTER TABLE {$CFG->prefix}data CHANGE listtemplatefooter listtemplatefooter text NULL AFTER listtemplateheader"); + execute_sql("ALTER TABLE {$CFG->prefix}data CHANGE addtemplate addtemplate text NULL AFTER listtemplatefooter"); + execute_sql("ALTER TABLE {$CFG->prefix}data CHANGE rsstemplate rsstemplate text NULL AFTER addtemplate"); + execute_sql("ALTER TABLE {$CFG->prefix}data CHANGE rsstitletemplate rsstitletemplate text NULL AFTER rsstemplate"); + execute_sql("ALTER TABLE {$CFG->prefix}data CHANGE csstemplate csstemplate text NULL AFTER rsstitletemplate"); + execute_sql("ALTER TABLE {$CFG->prefix}data CHANGE jstemplate jstemplate text NULL AFTER csstemplate"); + + execute_sql("ALTER TABLE {$CFG->prefix}data_fields CHANGE param1 param1 text NULL AFTER description"); + execute_sql("ALTER TABLE {$CFG->prefix}data_fields CHANGE param2 param2 text NULL AFTER param1"); + execute_sql("ALTER TABLE {$CFG->prefix}data_fields CHANGE param3 param3 text NULL AFTER param2"); + execute_sql("ALTER TABLE {$CFG->prefix}data_fields CHANGE param4 param4 text NULL AFTER param3"); + execute_sql("ALTER TABLE {$CFG->prefix}data_fields CHANGE param5 param5 text NULL AFTER param4"); + execute_sql("ALTER TABLE {$CFG->prefix}data_fields CHANGE param6 param6 text NULL AFTER param5"); + execute_sql("ALTER TABLE {$CFG->prefix}data_fields CHANGE param7 param7 text NULL AFTER param6"); + execute_sql("ALTER TABLE {$CFG->prefix}data_fields CHANGE param8 param8 text NULL AFTER param7"); + execute_sql("ALTER TABLE {$CFG->prefix}data_fields CHANGE param9 param9 text NULL AFTER param8"); + execute_sql("ALTER TABLE {$CFG->prefix}data_fields CHANGE param10 param10 text NULL AFTER param9"); + + execute_sql("ALTER TABLE {$CFG->prefix}data_content CHANGE content content longtext NULL AFTER recordid"); + execute_sql("ALTER TABLE {$CFG->prefix}data_content CHANGE content1 content1 longtext NULL AFTER content"); + execute_sql("ALTER TABLE {$CFG->prefix}data_content CHANGE content2 content2 longtext NULL AFTER content1"); + execute_sql("ALTER TABLE {$CFG->prefix}data_content CHANGE content3 content3 longtext NULL AFTER content2"); + execute_sql("ALTER TABLE {$CFG->prefix}data_content CHANGE content4 content4 longtext NULL AFTER content3"); + } return true; } -?> \ No newline at end of file +?> diff --git a/mod/data/db/mysql.sql b/mod/data/db/mysql.sql index 6e71b25026..5e03cf6cc9 100755 --- a/mod/data/db/mysql.sql +++ b/mod/data/db/mysql.sql @@ -24,15 +24,15 @@ CREATE TABLE prefix_data ( requiredentriestoview int(8) unsigned NOT NULL default '0', maxentries int(8) unsigned NOT NULL default '0', rssarticles int(4) unsigned NOT NULL default '0', - singletemplate text NOT NULL default '', - listtemplate text NOT NULL default '', - listtemplateheader text NOT NULL default '', - listtemplatefooter text NOT NULL default '', - addtemplate text NOT NULL default '', - rsstemplate text NOT NULL default '', - rsstitletemplate text NOT NULL default '', - csstemplate text NOT NULL default '', - jstemplate text NOT NULL default '', + singletemplate text, + listtemplate text, + listtemplateheader text, + listtemplatefooter text, + addtemplate text, + rsstemplate text, + rsstitletemplate text, + csstemplate text, + jstemplate text, approval tinyint(4) unsigned NOT NULL default '0', scale int(10) NOT NULL default '0', assessed int(10) unsigned NOT NULL default '0', @@ -47,11 +47,11 @@ CREATE TABLE prefix_data_content ( id int(10) unsigned NOT NULL auto_increment, fieldid int(10) unsigned NOT NULL default '0', recordid int(10) unsigned NOT NULL default '0', - content longtext NOT NULL default '', - content1 longtext NOT NULL default '', - content2 longtext NOT NULL default '', - content3 longtext NOT NULL default '', - content4 longtext NOT NULL default '', + content longtext, + content1 longtext, + content2 longtext, + content3 longtext, + content4 longtext, PRIMARY KEY (id) ) TYPE=MyISAM; @@ -62,16 +62,16 @@ CREATE TABLE prefix_data_fields ( type varchar(255) NOT NULL default '', name varchar(255) NOT NULL default '', description text NOT NULL default '', - param1 text NOT NULL default '', - param2 text NOT NULL default '', - param3 text NOT NULL default '', - param4 text NOT NULL default '', - param5 text NOT NULL default '', - param6 text NOT NULL default '', - param7 text NOT NULL default '', - param8 text NOT NULL default '', - param9 text NOT NULL default '', - param10 text NOT NULL default '', + param1 text, + param2 text, + param3 text, + param4 text, + param5 text, + param6 text, + param7 text, + param8 text, + param9 text, + param10 text, PRIMARY KEY (id) ) TYPE=MyISAM; diff --git a/mod/data/db/postgres7.php b/mod/data/db/postgres7.php index f72426c726..78fe6fb55f 100644 --- a/mod/data/db/postgres7.php +++ b/mod/data/db/postgres7.php @@ -191,8 +191,82 @@ function data_upgrade($oldversion) { modify_database('', 'ALTER TABLE prefix_data DROP COLUMN ratings;'); } + + if ($oldversion < 2006092302) { // Changing some TEXT fields to NULLable and no default + execute_sql("ALTER TABLE {$CFG->prefix}data ALTER COLUMN singletemplate DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data ALTER COLUMN singletemplate DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data ALTER COLUMN listtemplate DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data ALTER COLUMN listtemplate DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data ALTER COLUMN listtemplateheader DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data ALTER COLUMN listtemplateheader DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data ALTER COLUMN listtemplatefooter DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data ALTER COLUMN listtemplatefooter DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data ALTER COLUMN addtemplate DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data ALTER COLUMN addtemplate DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data ALTER COLUMN rsstemplate DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data ALTER COLUMN rsstemplate DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data ALTER COLUMN rsstitletemplate DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data ALTER COLUMN rsstitletemplate DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data ALTER COLUMN csstemplate DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data ALTER COLUMN csstemplate DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data ALTER COLUMN jstemplate DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data ALTER COLUMN jstemplate DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data_fields ALTER COLUMN param1 DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data_fields ALTER COLUMN param1 DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data_fields ALTER COLUMN param2 DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data_fields ALTER COLUMN param2 DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data_fields ALTER COLUMN param3 DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data_fields ALTER COLUMN param3 DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data_fields ALTER COLUMN param4 DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data_fields ALTER COLUMN param4 DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data_fields ALTER COLUMN param5 DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data_fields ALTER COLUMN param5 DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data_fields ALTER COLUMN param6 DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data_fields ALTER COLUMN param6 DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data_fields ALTER COLUMN param7 DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data_fields ALTER COLUMN param7 DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data_fields ALTER COLUMN param8 DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data_fields ALTER COLUMN param8 DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data_fields ALTER COLUMN param9 DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data_fields ALTER COLUMN param9 DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data_fields ALTER COLUMN param10 DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data_fields ALTER COLUMN param10 DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data_content ALTER COLUMN content DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data_content ALTER COLUMN content DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data_content ALTER COLUMN content1 DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data_content ALTER COLUMN content1 DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data_content ALTER COLUMN content2 DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data_content ALTER COLUMN content2 DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data_content ALTER COLUMN content3 DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data_content ALTER COLUMN content3 DROP DEFAULT"); + + execute_sql("ALTER TABLE {$CFG->prefix}data_content ALTER COLUMN content4 DROP NOT NULL"); + execute_sql("ALTER TABLE {$CFG->prefix}data_content ALTER COLUMN content4 DROP DEFAULT"); + } return true; } -?> \ No newline at end of file +?> diff --git a/mod/data/db/postgres7.sql b/mod/data/db/postgres7.sql index 045d205315..e5413dca8d 100755 --- a/mod/data/db/postgres7.sql +++ b/mod/data/db/postgres7.sql @@ -13,15 +13,15 @@ CREATE TABLE prefix_data ( requiredentriestoview integer NOT NULL default '0', maxentries integer NOT NULL default '0', rssarticles integer NOT NULL default '0', - singletemplate text NOT NULL default '', - listtemplate text NOT NULL default '', - listtemplateheader text NOT NULL default '', - listtemplatefooter text NOT NULL default '', - addtemplate text NOT NULL default '', - rsstemplate text NOT NULL default '', - rsstitletemplate text NOT NULL default '', - csstemplate text NOT NULL default '', - jstemplate text NOT NULL default '', + singletemplate text, + listtemplate text, + listtemplateheader text, + listtemplatefooter text, + addtemplate text, + rsstemplate text, + rsstitletemplate text, + csstemplate text, + jstemplate text, approval integer NOT NULL default '0', scale integer NOT NULL default '0', assessed integer NOT NULL default '0', @@ -36,11 +36,11 @@ CREATE TABLE prefix_data_content ( id SERIAL PRIMARY KEY, fieldid integer NOT NULL default '0', recordid integer NOT NULL default '0', - content text NOT NULL default '', - content1 text NOT NULL default '', - content2 text NOT NULL default '', - content3 text NOT NULL default '', - content4 text NOT NULL default '' + content text, + content1 text, + content2 text, + content3 text, + content text4 ); @@ -50,16 +50,16 @@ CREATE TABLE prefix_data_fields ( type varchar(255) NOT NULL default '', name varchar(255) NOT NULL default '', description text NOT NULL default '', - param1 text NOT NULL default '', - param2 text NOT NULL default '', - param3 text NOT NULL default '', - param4 text NOT NULL default '', - param5 text NOT NULL default '', - param6 text NOT NULL default '', - param7 text NOT NULL default '', - param8 text NOT NULL default '', - param9 text NOT NULL default '', - param10 text NOT NULL default '' + param1 text, + param2 text, + param3 text, + param4 text, + param5 text, + param6 text, + param7 text, + param8 text, + param9 text, + param10 text ); CREATE TABLE prefix_data_records ( -- 2.39.5