[geeklog-hg] geeklog: Changed fixed-size char fields to varchar since Postgre...

[geeklog-cvs at lists.geeklog.net]

changeset 8761:1e0de7041078
url:  http://project.geeklog.net/cgi-bin/hgwebdir.cgi/geeklog/rev/1e0de7041078
user: Dirk Haun <dirk at haun-online.de>
date: Mon Jul 02 11:40:57 2012 +0200
description:
Changed fixed-size char fields to varchar since Postgres padds them with blanks, which has all sorts of interesting side-effects, e.g. bug #0001425 (HTML post mode not recognized)

diffstat:

 sql/pgsql_tableanddata.php           |  12 ++++++------
 sql/updates/pgsql_1.8.1_to_2.0.0.php |   8 ++++++++
 2 files changed, 14 insertions(+), 6 deletions(-)

diffs (61 lines):

diff -r 4ef61b9b61ed -r 1e0de7041078 sql/pgsql_tableanddata.php
--- a/sql/pgsql_tableanddata.php	Mon Jul 02 10:33:34 2012 +0200
+++ b/sql/pgsql_tableanddata.php	Mon Jul 02 11:40:57 2012 +0200
@@ -211,7 +211,7 @@
 $_SQL[] = "
 CREATE TABLE {$_TABLES['maillist']} (
   code SERIAL, 
-  name char(32) default NULL,
+  name varchar(32) default NULL,
   PRIMARY KEY (code)
 )
 ";
@@ -244,8 +244,8 @@
 
 $_SQL[] = "
 CREATE TABLE {$_TABLES['postmodes']} (
-  code char(10) NOT NULL default '',
-  name char(32) default NULL,
+  code varchar(10) NOT NULL default '',
+  name varchar(32) default NULL,
   PRIMARY KEY (code)
 )
 ";
@@ -267,8 +267,8 @@
 
 $_SQL[] = "
 CREATE TABLE {$_TABLES['sortcodes']} (
-  code char(4) NOT NULL default '0',
-  name char(32) default NULL,
+  code varchar(4) NOT NULL default '0',
+  name varchar(32) default NULL,
   PRIMARY KEY (code)
 )
 ";
@@ -288,7 +288,7 @@
 $_SQL[] = "
 CREATE TABLE {$_TABLES['statuscodes']} (
   code int NOT NULL default '0',
-  name char(32) default NULL,
+  name varchar(32) default NULL,
   PRIMARY KEY (code)
 )
 ";
diff -r 4ef61b9b61ed -r 1e0de7041078 sql/updates/pgsql_1.8.1_to_2.0.0.php
--- a/sql/updates/pgsql_1.8.1_to_2.0.0.php	Mon Jul 02 10:33:34 2012 +0200
+++ b/sql/updates/pgsql_1.8.1_to_2.0.0.php	Mon Jul 02 11:40:57 2012 +0200
@@ -27,6 +27,14 @@
 $_SQL[] = "ALTER TABLE {$_TABLES['users']} ADD algorithm varchar(12) NOT NULL default 0";
 $_SQL[] = "ALTER TABLE {$_TABLES['users']} ADD stretch int NOT NULL default 1";
 
+// use varchars, not fixed-size char fields
+$_SQL[] = "ALTER TABLE {$_TABLES['maillist']} ALTER COLUMN name TYPE varchar(32)";
+$_SQL[] = "ALTER TABLE {$_TABLES['postmodes']} ALTER COLUMN code TYPE varchar(10)";
+$_SQL[] = "ALTER TABLE {$_TABLES['postmodes']} ALTER COLUMN name TYPE varchar(32)";
+$_SQL[] = "ALTER TABLE {$_TABLES['sortcodes']} ALTER COLUMN code TYPE varchar(4)";
+$_SQL[] = "ALTER TABLE {$_TABLES['sortcodes']} ALTER COLUMN name TYPE varchar(32)";
+$_SQL[] = "ALTER TABLE {$_TABLES['statuscodes']} ALTER COLUMN name TYPE varchar(32)";
+
 /**
  * Create Story and Submission Topic assignments
  *