Awesome non-quoted numeric default patch by Stephen Clouse

diff --git a/AUTHORS b/AUTHORS
index 306c7cf..7c1186d 100644 (file)
--- a/AUTHORS
+++ b/AUTHORS
@@ -27,6 +27,7 @@ The following people have contributed to the SQLFairy project:
 -   Ross Smith II <rossta@users.sf.net>
 -   Ryan D Johnson <ryan@innerfence.com>
 -   Sam Angiuoli <angiuoli@users.sourceforge.net>
+-   Stephen Clouse <stephenclouse@gmail.com>
 -   Wallace Reis <wreis@cpan.org>
 -   Ying Zhang <zyolive@yahoo.com>
 

diff --git a/Changes b/Changes
index 31a0d6e..d02aacd 100644 (file)
--- a/Changes
+++ b/Changes
@@ -8,6 +8,8 @@
 * Fix Producer::MySQL wrapping extra ()s around VIEW SELECT-ors (RT#56419)
 * Fix Field::default_value to behave like a real accessor (allow undef as
   an unsetting argument)
+* Fix Mysql/Pg/SQLite/MSSQL producers to properly *not* quote numeric default
+  values (RT#57059)
 * Producer::Oracle tests now use Test::Differences
 * Prettify output of SQLite producer (less bogus newlines)
 * Augment SQLite and Pg producers to accept a perl-formatted (%d.%03d%03d)

diff --git a/lib/SQL/Translator/Parser/SQLServer.pm b/lib/SQL/Translator/Parser/SQLServer.pm
index 98c7fe1..38cf64b 100644 (file)
--- a/lib/SQL/Translator/Parser/SQLServer.pm
+++ b/lib/SQL/Translator/Parser/SQLServer.pm
@@ -323,6 +323,8 @@ default_val : /default/i /null/i
     { $return = 'null' }
        | /default/i /'[^']*'/ 
     { $item[2]=~ s/'//g; $return = $item[2] }
+       | /default/i WORD
+    { $return = $item[2] }
 
 auto_inc : /identity/i { 1 }
 

diff --git a/lib/SQL/Translator/Producer.pm b/lib/SQL/Translator/Producer.pm
index b2ba10e..9119da6 100644 (file)
--- a/lib/SQL/Translator/Producer.pm
+++ b/lib/SQL/Translator/Producer.pm
@@ -20,6 +20,7 @@ package SQL::Translator::Producer;
 
 use strict;
 use vars qw($VERSION);
+use Scalar::Util ();
 $VERSION = '1.59';
 
 sub produce { "" }
@@ -32,7 +33,9 @@ sub produce { "" }
 ## They are special per Producer, and provide support for the old 'now()'
 ## default value exceptions
 sub _apply_default_value {
-  my (undef, $field_ref, $default, $exceptions) = @_;
+  my (undef, $field, $field_ref, $exceptions) = @_;
+  my $default = $field->default_value;
+  return if !defined $default;
 
   if ($exceptions and ! ref $default) {
     for (my $i = 0; $i < @$exceptions; $i += 2) {
@@ -47,8 +50,14 @@ sub _apply_default_value {
     }
   }
 
+  my $type = lc $field->data_type;
+  my $is_numeric_datatype = ($type =~ /^(?:(?:big|medium|small|tiny)?int(?:eger)?|decimal|double|float|num(?:ber|eric)?|real)$/);
+
   if (ref $default) {
       $$field_ref .= " DEFAULT $$default";
+  } elsif ($is_numeric_datatype && Scalar::Util::looks_like_number ($default) ) {
+    # we need to check the data itself in addition to the datatype, for basic safety
+      $$field_ref .= " DEFAULT $default";
   } else {
       $$field_ref .= " DEFAULT '$default'";
   }

diff --git a/lib/SQL/Translator/Producer/MySQL.pm b/lib/SQL/Translator/Producer/MySQL.pm
index f261a9f..db7f7c8 100644 (file)
--- a/lib/SQL/Translator/Producer/MySQL.pm
+++ b/lib/SQL/Translator/Producer/MySQL.pm
@@ -582,17 +582,14 @@ sub create_field
     # Null?
     $field_def .= ' NOT NULL' unless $field->is_nullable;
 
-    # Default?  XXX Need better quoting!
-    my $default = $field->default_value;
-    if ( defined $default ) {
-        SQL::Translator::Producer->_apply_default_value(
-          \$field_def,
-          $default, 
-          [
-            'NULL'       => \'NULL',
-          ],
-        );
-    }
+    # Default?
+    SQL::Translator::Producer->_apply_default_value(
+      $field,
+      \$field_def,
+      [
+        'NULL'       => \'NULL',
+      ],
+    );
 
     if ( my $comments = $field->comments ) {
         $field_def .= qq[ comment '$comments'];

diff --git a/lib/SQL/Translator/Producer/PostgreSQL.pm b/lib/SQL/Translator/Producer/PostgreSQL.pm
index dd96e9d..a881689 100644 (file)
--- a/lib/SQL/Translator/Producer/PostgreSQL.pm
+++ b/lib/SQL/Translator/Producer/PostgreSQL.pm
@@ -505,18 +505,15 @@ sub create_view {
         #
         # Default value 
         #
-        my $default = $field->default_value;
-        if ( defined $default ) {
-            SQL::Translator::Producer->_apply_default_value(
-              \$field_def,
-              $default,
-              [
-                'NULL'              => \'NULL',
-                'now()'             => 'now()',
-                'CURRENT_TIMESTAMP' => 'CURRENT_TIMESTAMP',
-              ],
-            );
-        }
+        SQL::Translator::Producer->_apply_default_value(
+          $field,
+          \$field_def,
+          [
+            'NULL'              => \'NULL',
+            'now()'             => 'now()',
+            'CURRENT_TIMESTAMP' => 'CURRENT_TIMESTAMP',
+          ],
+        );
 
         #
         # Not null constraint

diff --git a/lib/SQL/Translator/Producer/SQLServer.pm b/lib/SQL/Translator/Producer/SQLServer.pm
index 11cee5f..8affe1a 100644 (file)
--- a/lib/SQL/Translator/Producer/SQLServer.pm
+++ b/lib/SQL/Translator/Producer/SQLServer.pm
@@ -253,16 +253,13 @@ sub produce {
             #
             # Default value
             #
-            my $default = $field->default_value;
-            if ( defined $default ) {
-                SQL::Translator::Producer->_apply_default_value(
-                  \$field_def,
-                  $default, 
-                  [
-                    'NULL'       => \'NULL',
-                  ],
-                );
-            }
+            SQL::Translator::Producer->_apply_default_value(
+              $field,
+              \$field_def,
+              [
+                'NULL'       => \'NULL',
+              ],
+            );
 
             push @field_defs, $field_def;            
         }

diff --git a/lib/SQL/Translator/Producer/SQLite.pm b/lib/SQL/Translator/Producer/SQLite.pm
index a2c6c32..2e7aa6a 100644 (file)
--- a/lib/SQL/Translator/Producer/SQLite.pm
+++ b/lib/SQL/Translator/Producer/SQLite.pm
@@ -305,19 +305,16 @@ sub create_field
     # Null?
     $field_def .= ' NOT NULL' unless $field->is_nullable;
 
-    # Default?  XXX Need better quoting!
-    my $default = $field->default_value;
-    if (defined $default) {
-        SQL::Translator::Producer->_apply_default_value(
-            \$field_def,
-            $default, 
-            [
-             'NULL'              => \'NULL',
-             'now()'             => 'now()',
-             'CURRENT_TIMESTAMP' => 'CURRENT_TIMESTAMP',
-            ],
-        );
-    }
+    # Default?
+    SQL::Translator::Producer->_apply_default_value(
+        $field,
+        \$field_def,
+        [
+         'NULL'              => \'NULL',
+         'now()'             => 'now()',
+         'CURRENT_TIMESTAMP' => 'CURRENT_TIMESTAMP',
+        ],
+    );
 
     return $field_def;
 

diff --git a/t/30sqlt-new-diff-mysql.t b/t/30sqlt-new-diff-mysql.t
index a3ed806..9ed3108 100644 (file)
--- a/t/30sqlt-new-diff-mysql.t
+++ b/t/30sqlt-new-diff-mysql.t
@@ -72,15 +72,15 @@ ALTER TABLE employee DROP COLUMN job_title;
 
 ALTER TABLE new_name ADD COLUMN new_field integer;
 
-ALTER TABLE person ADD COLUMN is_rock_star tinyint(4) DEFAULT '1';
+ALTER TABLE person ADD COLUMN is_rock_star tinyint(4) DEFAULT 1;
 
 ALTER TABLE person CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment;
 
 ALTER TABLE person CHANGE COLUMN name name varchar(20) NOT NULL;
 
-ALTER TABLE person CHANGE COLUMN age age integer(11) DEFAULT '18';
+ALTER TABLE person CHANGE COLUMN age age integer(11) DEFAULT 18;
 
-ALTER TABLE person CHANGE COLUMN iq iq integer(11) DEFAULT '0';
+ALTER TABLE person CHANGE COLUMN iq iq integer(11) DEFAULT 0;
 
 ALTER TABLE person CHANGE COLUMN description physical_description text;
 
@@ -128,11 +128,11 @@ ALTER TABLE old_name RENAME TO new_name,
                      ADD COLUMN new_field integer;
 
 ALTER TABLE person DROP INDEX UC_age_name,
-                   ADD COLUMN is_rock_star tinyint(4) DEFAULT '1',
+                   ADD COLUMN is_rock_star tinyint(4) DEFAULT 1,
                    CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment,
                    CHANGE COLUMN name name varchar(20) NOT NULL,
-                   CHANGE COLUMN age age integer(11) DEFAULT '18',
-                   CHANGE COLUMN iq iq integer(11) DEFAULT '0',
+                   CHANGE COLUMN age age integer(11) DEFAULT 18,
+                   CHANGE COLUMN iq iq integer(11) DEFAULT 0,
                    CHANGE COLUMN description physical_description text,
                    ADD UNIQUE UC_person_id (person_id),
                    ADD UNIQUE UC_age_name (age, name),
@@ -198,11 +198,11 @@ ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E,
 
 ALTER TABLE person DROP INDEX UC_age_name,
                    DROP INDEX u_name,
-                   ADD COLUMN is_rock_star tinyint(4) DEFAULT '1',
+                   ADD COLUMN is_rock_star tinyint(4) DEFAULT 1,
                    CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment,
                    CHANGE COLUMN name name varchar(20) NOT NULL,
-                   CHANGE COLUMN age age integer(11) DEFAULT '18',
-                   CHANGE COLUMN iq iq integer(11) DEFAULT '0',
+                   CHANGE COLUMN age age integer(11) DEFAULT 18,
+                   CHANGE COLUMN iq iq integer(11) DEFAULT 0,
                    CHANGE COLUMN description physical_description text,
                    ADD UNIQUE INDEX unique_name (name),
                    ADD UNIQUE UC_person_id (person_id),

diff --git a/t/30sqlt-new-diff-pgsql.t b/t/30sqlt-new-diff-pgsql.t
index 3a2c957..052f58d 100644 (file)
--- a/t/30sqlt-new-diff-pgsql.t
+++ b/t/30sqlt-new-diff-pgsql.t
@@ -66,7 +66,7 @@ ALTER TABLE employee DROP COLUMN job_title;
 
 ALTER TABLE new_name ADD COLUMN new_field integer;
 
-ALTER TABLE person ADD COLUMN is_rock_star smallint DEFAULT '1';
+ALTER TABLE person ADD COLUMN is_rock_star smallint DEFAULT 1;
 
 ALTER TABLE person ALTER COLUMN person_id TYPE serial;
 
@@ -121,7 +121,7 @@ ALTER TABLE employee DROP COLUMN job_title;
 
 ALTER TABLE new_name ADD COLUMN new_field integer;
 
-ALTER TABLE person ADD COLUMN is_rock_star smallint DEFAULT '1';
+ALTER TABLE person ADD COLUMN is_rock_star smallint DEFAULT 1;
 
 ALTER TABLE person ALTER COLUMN person_id TYPE serial;
 

diff --git a/t/30sqlt-new-diff-sqlite.t b/t/30sqlt-new-diff-sqlite.t
index 143f6a5..5d6ad92 100644 (file)
--- a/t/30sqlt-new-diff-sqlite.t
+++ b/t/30sqlt-new-diff-sqlite.t
@@ -60,7 +60,7 @@ DROP INDEX u_name;
 
 ALTER TABLE new_name ADD COLUMN new_field int;
 
-ALTER TABLE person ADD COLUMN is_rock_star tinyint(4) DEFAULT '1';
+ALTER TABLE person ADD COLUMN is_rock_star tinyint(4) DEFAULT 1;
 
 -- SQL::Translator::Producer::SQLite cant alter_field;
 
@@ -122,10 +122,10 @@ ALTER TABLE new_name ADD COLUMN new_field int;
 CREATE TEMPORARY TABLE person_temp_alter (
   person_id INTEGER PRIMARY KEY NOT NULL,
   name varchar(20) NOT NULL,
-  age int(11) DEFAULT '18',
+  age int(11) DEFAULT 18,
   weight double(11,2),
-  iq int(11) DEFAULT '0',
-  is_rock_star tinyint(4) DEFAULT '1',
+  iq int(11) DEFAULT 0,
+  is_rock_star tinyint(4) DEFAULT 1,
   physical_description text
 );
 
@@ -136,10 +136,10 @@ DROP TABLE person;
 CREATE TABLE person (
   person_id INTEGER PRIMARY KEY NOT NULL,
   name varchar(20) NOT NULL,
-  age int(11) DEFAULT '18',
+  age int(11) DEFAULT 18,
   weight double(11,2),
-  iq int(11) DEFAULT '0',
-  is_rock_star tinyint(4) DEFAULT '1',
+  iq int(11) DEFAULT 0,
+  is_rock_star tinyint(4) DEFAULT 1,
   physical_description text
 );
 

diff --git a/t/46xml-to-pg.t b/t/46xml-to-pg.t
index 519e806..e3b9995 100644 (file)
--- a/t/46xml-to-pg.t
+++ b/t/46xml-to-pg.t
@@ -44,7 +44,7 @@ CREATE TABLE "Basic" (
   "explicitemptystring" character varying DEFAULT '',
   -- Hello emptytagdef
   "emptytagdef" character varying DEFAULT '',
-  "another_id" integer DEFAULT '2',
+  "another_id" integer DEFAULT 2,
   "timest" timestamp,
   PRIMARY KEY ("id"),
   CONSTRAINT "emailuniqueindex" UNIQUE ("email"),

diff --git a/t/48xml-to-sqlite.t b/t/48xml-to-sqlite.t
index ef14da4..8f38fec 100644 (file)
--- a/t/48xml-to-sqlite.t
+++ b/t/48xml-to-sqlite.t
@@ -47,7 +47,7 @@ CREATE TABLE Basic (
   explicitemptystring varchar DEFAULT '',
   -- Hello emptytagdef
   emptytagdef varchar DEFAULT '',
-  another_id int(10) DEFAULT '2',
+  another_id int(10) DEFAULT 2,
   timest timestamp
 );
 
@@ -104,7 +104,7 @@ eq_or_diff(\@sql,
   explicitemptystring varchar DEFAULT \'\',
   -- Hello emptytagdef
   emptytagdef varchar DEFAULT \'\',
-  another_id int(10) DEFAULT \'2\',
+  another_id int(10) DEFAULT 2,
   timest timestamp
 )',
           'CREATE INDEX titleindex ON Basic (title)',