From: Peter Rabbitson Date: Thu, 3 Jun 2010 09:09:02 +0000 (+0000) Subject: Awesome non-quoted numeric default patch by Stephen Clouse X-Git-Tag: v0.11008~41 X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits%2FSQL-Translator.git;a=commitdiff_plain;h=06baeb2195df54daf9e50033a2050b473271b07d Awesome non-quoted numeric default patch by Stephen Clouse --- diff --git a/AUTHORS b/AUTHORS index 306c7cf..7c1186d 100644 --- a/AUTHORS +++ b/AUTHORS @@ -27,6 +27,7 @@ The following people have contributed to the SQLFairy project: - Ross Smith II - Ryan D Johnson - Sam Angiuoli +- Stephen Clouse - Wallace Reis - Ying Zhang diff --git a/Changes b/Changes index 31a0d6e..d02aacd 100644 --- 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 --- 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 --- 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 --- 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 --- 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 --- 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 --- 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 --- 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 --- 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 --- 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 --- 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 --- 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)',