+* Proper quoting support in SQLite
* Support for triggers in PostgreSQL producer and parser
* Correct Data Type in SQLT::Parser::DBI::PostgreSQL (patch from Andrew Pam)
* Fix index issue in SQLT::Parser::DBI::PostgreSQL
}
}
-ref_def : /(\w+)\s*\((\w+)\)/
- { $return = { reference_table => $1, reference_fields => $2 } }
+ref_def : table_name parens_field_list
+ { $return = { reference_table => $item[1]{name}, reference_fields => $item[2] } }
table_name : qualified_name
use Data::Dumper;
use SQL::Translator::Schema::Constants;
use SQL::Translator::Utils qw(debug header_comment parse_dbms_version);
+use SQL::Translator::ProducerUtils;
+my $util = SQL::Translator::ProducerUtils->new( quote_chars => q(') );
our ( $DEBUG, $WARN );
-
our $VERSION = '1.59';
$DEBUG = 0 unless defined $DEBUG;
$WARN = 0 unless defined $WARN;
}
$scope->{ $name }++;
- return $name;
+ return $util->quote($name);
}
sub create_view {
my ($view, $options) = @_;
my $add_drop_view = $options->{add_drop_view};
- my $view_name = $view->name;
- $global_names{$view_name} = 1;
+ my $view_name = $util->quote($view->name);
+ $global_names{$view->name} = 1;
debug("PKG: Looking at view '${view_name}'\n");
{
my ($table, $options) = @_;
- my $table_name = $table->name;
- $global_names{$table_name} = 1;
+ my $table_name = $util->quote($table->name);
+ $global_names{$table->name} = 1;
my $no_comments = $options->{no_comments};
my $add_drop_table = $options->{add_drop_table};
||
( @pk_fields && !grep /INTEGER PRIMARY KEY/, @field_defs )
) {
- push @field_defs, 'PRIMARY KEY (' . join(', ', @pk_fields ) . ')';
+ push @field_defs, 'PRIMARY KEY (' . join(', ', map $util->quote($_), @pk_fields ) . ')';
}
#
sub create_foreignkey {
my $c = shift;
- my $fk_sql = "FOREIGN KEY($c->{fields}[0]) REFERENCES ";
- $fk_sql .= ( $c->{reference_table} || '' )."(".( $c->{reference_fields}[0] || '' ).")";
+ my $field = $util->quote($c->{fields}[0]);
+ my $table = $c->{reference_table} ? $util->quote($c->{reference_table}) : '';
+ my $ref = $c->{reference_fields}[0] ? $util->quote($c->{reference_fields}[0]) : '';
+ my $fk_sql = "FOREIGN KEY($field) REFERENCES ";
+ $fk_sql .= "$table($ref)";
return $fk_sql;
}
{
my ($field, $options) = @_;
- my $field_name = $field->name;
+ my $field_name = $util->quote($field->name);
debug("PKG: Looking at field '$field_name'\n");
my $field_comments = $field->comments
? "-- " . $field->comments . "\n "
my $type = $index->type eq 'UNIQUE' ? "UNIQUE " : '';
# strip any field size qualifiers as SQLite doesn't like these
- my @fields = map { s/\(\d+\)$//; $_ } $index->fields;
+ my @fields = map { s/\(\d+\)$//; $util->quote($_) } $index->fields;
(my $index_table_name = $index->table->name) =~ s/^.+?\.//; # table name may not specify schema
+ $index_table_name = $util->quote($index_table_name);
warn "removing schema name from '" . $index->table->name . "' to make '$index_table_name'\n" if $WARN;
my $index_def =
"CREATE ${type}INDEX $name ON " . $index_table_name .
my $name = $c->name;
$name = mk_name($name);
- my @fields = $c->fields;
+ my @fields = map $util->quote($_), $c->fields;
(my $index_table_name = $c->table->name) =~ s/^.+?\.//; # table name may not specify schema
+ $index_table_name = $util->quote($index_table_name);
warn "removing schema name from '" . $c->table->name . "' to make '$index_table_name'\n" if $WARN;
my $c_def =
"creating trigger '$trig_name' for the '$evt' event.\n" if $WARN;
}
+ $trig_name = $util->quote($trig_name);
push @statements, "DROP TRIGGER IF EXISTS $trig_name" if $add_drop;
$trig_name,
$trigger->perform_action_when,
$evt,
- $trigger->on_table,
+ $util->quote($trigger->on_table),
$action
);
}
my ($field) = @_;
return sprintf("ALTER TABLE %s ADD COLUMN %s",
- $field->table->name, create_field($field))
+ $util->quote($field->table->name), create_field($field))
}
sub alter_create_index {
my ($constraint) = @_;
return sprintf("DROP INDEX %s",
- $constraint->name);
+ $util->quote($constraint->name));
}
sub batch_alter_table {
push @sql,$table_sql;
};
- push @sql, "INSERT INTO @{[$table_name]}_temp_alter SELECT @{[ join(', ', $old_table->get_fields)]} FROM @{[$old_table]}",
- "DROP TABLE @{[$old_table]}",
+ push @sql, "INSERT INTO @{[$util->quote($table_name.'_temp_alter')]} SELECT @{[ join(', ', map $util->quote($_), $old_table->get_fields)]} FROM @{[$util->quote($old_table)]}",
+ "DROP TABLE @{[$util->quote($old_table)]}",
create_table($table, { no_comments => 1 }),
- "INSERT INTO @{[$table_name]} SELECT @{[ join(', ', $old_table->get_fields)]} FROM @{[$table_name]}_temp_alter",
- "DROP TABLE @{[$table_name]}_temp_alter";
+ "INSERT INTO @{[$util->quote($table_name)]} SELECT @{[ join(', ', map $util->quote($_), $old_table->get_fields)]} FROM @{[$util->quote($table_name.'_temp_alter')]}",
+ "DROP TABLE @{[$util->quote($table_name.'_temp_alter')]}";
return @sql;
# return join("", @sql, "");
sub drop_table {
my ($table) = @_;
+ $table = $util->quote($table);
return "DROP TABLE $table";
}
sub rename_table {
my ($old_table, $new_table, $options) = @_;
- my $qt = $options->{quote_table_names} || '';
+ $old_table = $util->quote($old_table);
+ $new_table = $util->quote($new_table);
- return "ALTER TABLE $qt$old_table$qt RENAME TO $qt$new_table$qt";
+ return "ALTER TABLE $old_table RENAME TO $new_table";
}
BEGIN;
-CREATE TABLE added (
- id int(11)
+CREATE TABLE 'added' (
+ 'id' int(11)
);
-ALTER TABLE old_name RENAME TO new_name;
+ALTER TABLE 'old_name' RENAME TO 'new_name';
-DROP INDEX FK5302D47D93FE702E;
+DROP INDEX 'FK5302D47D93FE702E';
-DROP INDEX UC_age_name;
+DROP INDEX 'UC_age_name';
-DROP INDEX u_name;
+DROP INDEX 'u_name';
-- SQL::Translator::Producer::SQLite cant drop_field;
-ALTER TABLE new_name ADD COLUMN new_field int;
+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;
-- SQL::Translator::Producer::SQLite cant rename_field;
-CREATE UNIQUE INDEX unique_name ON person (name);
+CREATE UNIQUE INDEX 'unique_name' ON 'person' ('name');
-CREATE UNIQUE INDEX UC_person_id ON person (person_id);
+CREATE UNIQUE INDEX 'UC_person_id' ON 'person' ('person_id');
-CREATE UNIQUE INDEX UC_age_name ON person (age, name);
+CREATE UNIQUE INDEX 'UC_age_name' ON 'person' ('age', 'name');
-DROP TABLE deleted;
+DROP TABLE 'deleted';
COMMIT;
BEGIN;
-CREATE TABLE added (
- id int(11)
+CREATE TABLE 'added' (
+ 'id' int(11)
);
-CREATE TEMPORARY TABLE employee_temp_alter (
- position varchar(50) NOT NULL,
- employee_id int(11) NOT NULL,
- PRIMARY KEY (position, employee_id),
- FOREIGN KEY(employee_id) REFERENCES person(person_id)
+CREATE TEMPORARY TABLE 'employee_temp_alter' (
+ 'position' varchar(50) NOT NULL,
+ 'employee_id' int(11) NOT NULL,
+ PRIMARY KEY ('position', 'employee_id'),
+ FOREIGN KEY('employee_id') REFERENCES 'person'('person_id')
);
-INSERT INTO employee_temp_alter SELECT position, employee_id FROM employee;
+INSERT INTO 'employee_temp_alter' SELECT 'position', 'employee_id' FROM 'employee';
-DROP TABLE employee;
+DROP TABLE 'employee';
-CREATE TABLE employee (
- position varchar(50) NOT NULL,
- employee_id int(11) NOT NULL,
- PRIMARY KEY (position, employee_id),
- FOREIGN KEY(employee_id) REFERENCES person(person_id)
+CREATE TABLE 'employee' (
+ 'position' varchar(50) NOT NULL,
+ 'employee_id' int(11) NOT NULL,
+ PRIMARY KEY ('position', 'employee_id'),
+ FOREIGN KEY('employee_id') REFERENCES 'person'('person_id')
);
-INSERT INTO employee SELECT position, employee_id FROM employee_temp_alter;
+INSERT INTO 'employee' SELECT 'position', 'employee_id' FROM 'employee_temp_alter';
-DROP TABLE employee_temp_alter;
+DROP TABLE 'employee_temp_alter';
-ALTER TABLE old_name RENAME TO new_name;
+ALTER TABLE 'old_name' RENAME TO 'new_name';
-ALTER TABLE new_name ADD COLUMN new_field int;
+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,
- weight double(11,2),
- iq int(11) DEFAULT 0,
- is_rock_star tinyint(4) DEFAULT 1,
- physical_description text
+CREATE TEMPORARY TABLE 'person_temp_alter' (
+ 'person_id' INTEGER PRIMARY KEY NOT NULL,
+ 'name' varchar(20) NOT NULL,
+ 'age' int(11) DEFAULT 18,
+ 'weight' double(11,2),
+ 'iq' int(11) DEFAULT 0,
+ 'is_rock_star' tinyint(4) DEFAULT 1,
+ 'physical_description' text
);
-INSERT INTO person_temp_alter SELECT person_id, name, age, weight, iq, is_rock_star, physical_description FROM person;
+INSERT INTO 'person_temp_alter' SELECT 'person_id', 'name', 'age', 'weight', 'iq', 'is_rock_star', 'physical_description' FROM 'person';
-DROP TABLE person;
+DROP TABLE 'person';
-CREATE TABLE person (
- person_id INTEGER PRIMARY KEY NOT NULL,
- name varchar(20) NOT NULL,
- age int(11) DEFAULT 18,
- weight double(11,2),
- iq int(11) DEFAULT 0,
- is_rock_star tinyint(4) DEFAULT 1,
- physical_description text
+CREATE TABLE 'person' (
+ 'person_id' INTEGER PRIMARY KEY NOT NULL,
+ 'name' varchar(20) NOT NULL,
+ 'age' int(11) DEFAULT 18,
+ 'weight' double(11,2),
+ 'iq' int(11) DEFAULT 0,
+ 'is_rock_star' tinyint(4) DEFAULT 1,
+ 'physical_description' text
);
-CREATE UNIQUE INDEX unique_name02 ON person (name);
+CREATE UNIQUE INDEX 'unique_name02' ON 'person' ('name');
-CREATE UNIQUE INDEX UC_person_id02 ON person (person_id);
+CREATE UNIQUE INDEX 'UC_person_id02' ON 'person' ('person_id');
-CREATE UNIQUE INDEX UC_age_name02 ON person (age, name);
+CREATE UNIQUE INDEX 'UC_age_name02' ON 'person' ('age', 'name');
-INSERT INTO person SELECT person_id, name, age, weight, iq, is_rock_star, physical_description FROM person_temp_alter;
+INSERT INTO 'person' SELECT 'person_id', 'name', 'age', 'weight', 'iq', 'is_rock_star', 'physical_description' FROM 'person_temp_alter';
-DROP TABLE person_temp_alter;
+DROP TABLE 'person_temp_alter';
-DROP TABLE deleted;
+DROP TABLE 'deleted';
COMMIT;
eq_or_diff($sql, << "SQL");
BEGIN TRANSACTION;
-DROP TABLE Basic;
-
-CREATE TABLE Basic (
- id INTEGER PRIMARY KEY NOT NULL,
- title varchar(100) NOT NULL DEFAULT 'hello',
- description text DEFAULT '',
- email varchar(500),
- explicitnulldef varchar,
- explicitemptystring varchar DEFAULT '',
+DROP TABLE 'Basic';
+
+CREATE TABLE 'Basic' (
+ 'id' INTEGER PRIMARY KEY NOT NULL,
+ 'title' varchar(100) NOT NULL DEFAULT 'hello',
+ 'description' text DEFAULT '',
+ 'email' varchar(500),
+ 'explicitnulldef' varchar,
+ 'explicitemptystring' varchar DEFAULT '',
-- Hello emptytagdef
- emptytagdef varchar DEFAULT '',
- another_id int(10) DEFAULT 2,
- timest timestamp,
- FOREIGN KEY(another_id) REFERENCES Another()
+ 'emptytagdef' varchar DEFAULT '',
+ 'another_id' int(10) DEFAULT 2,
+ 'timest' timestamp,
+ FOREIGN KEY('another_id') REFERENCES 'Another'()
);
-CREATE INDEX titleindex ON Basic (title);
+CREATE INDEX 'titleindex' ON 'Basic' ('title');
-CREATE UNIQUE INDEX emailuniqueindex ON Basic (email);
+CREATE UNIQUE INDEX 'emailuniqueindex' ON 'Basic' ('email');
-CREATE UNIQUE INDEX very_long_index_name_on_title_field_which_should_be_truncated_for_various_rdbms ON Basic (title);
+CREATE UNIQUE INDEX 'very_long_index_name_on_title_field_which_should_be_truncated_for_various_rdbms' ON 'Basic' ('title');
-DROP TABLE Another;
+DROP TABLE 'Another';
-CREATE TABLE Another (
- id INTEGER PRIMARY KEY NOT NULL,
- num numeric(10,2)
+CREATE TABLE 'Another' (
+ 'id' INTEGER PRIMARY KEY NOT NULL,
+ 'num' numeric(10,2)
);
-DROP VIEW IF EXISTS email_list;
+DROP VIEW IF EXISTS 'email_list';
-CREATE VIEW email_list AS
+CREATE VIEW 'email_list' AS
SELECT email FROM Basic WHERE (email IS NOT NULL);
-DROP TRIGGER IF EXISTS foo_trigger;
+DROP TRIGGER IF EXISTS 'foo_trigger';
-CREATE TRIGGER foo_trigger after insert on Basic BEGIN update modified=timestamp(); END;
+CREATE TRIGGER 'foo_trigger' after insert on 'Basic' BEGIN update modified=timestamp(); END;
-DROP TRIGGER IF EXISTS bar_trigger_insert;
+DROP TRIGGER IF EXISTS 'bar_trigger_insert';
-CREATE TRIGGER bar_trigger_insert before insert on Basic BEGIN update modified2=timestamp(); END;
+CREATE TRIGGER 'bar_trigger_insert' before insert on 'Basic' BEGIN update modified2=timestamp(); END;
-DROP TRIGGER IF EXISTS bar_trigger_update;
+DROP TRIGGER IF EXISTS 'bar_trigger_update';
-CREATE TRIGGER bar_trigger_update before update on Basic BEGIN update modified2=timestamp(); END;
+CREATE TRIGGER 'bar_trigger_update' before update on 'Basic' BEGIN update modified2=timestamp(); END;
COMMIT;
SQL
eq_or_diff(\@sql,
[
'BEGIN TRANSACTION',
- 'DROP TABLE Basic',
- 'CREATE TABLE Basic (
- id INTEGER PRIMARY KEY NOT NULL,
- title varchar(100) NOT NULL DEFAULT \'hello\',
- description text DEFAULT \'\',
- email varchar(500),
- explicitnulldef varchar,
- explicitemptystring varchar DEFAULT \'\',
+ q<DROP TABLE 'Basic'>,
+ q<CREATE TABLE 'Basic' (
+ 'id' INTEGER PRIMARY KEY NOT NULL,
+ 'title' varchar(100) NOT NULL DEFAULT 'hello',
+ 'description' text DEFAULT '',
+ 'email' varchar(500),
+ 'explicitnulldef' varchar,
+ 'explicitemptystring' varchar DEFAULT '',
-- Hello emptytagdef
- emptytagdef varchar DEFAULT \'\',
- another_id int(10) DEFAULT 2,
- timest timestamp,
- FOREIGN KEY(another_id) REFERENCES Another()
-)',
- 'CREATE INDEX titleindex ON Basic (title)',
- 'CREATE UNIQUE INDEX emailuniqueindex ON Basic (email)',
- 'CREATE UNIQUE INDEX very_long_index_name_on_title_field_which_should_be_truncated_for_various_rdbms ON Basic (title)',
- 'DROP TABLE Another',
- 'CREATE TABLE Another (
- id INTEGER PRIMARY KEY NOT NULL,
- num numeric(10,2)
-)',
- 'DROP VIEW IF EXISTS email_list',
- 'CREATE VIEW email_list AS
- SELECT email FROM Basic WHERE (email IS NOT NULL)',
- 'DROP TRIGGER IF EXISTS foo_trigger',
- 'CREATE TRIGGER foo_trigger after insert on Basic BEGIN update modified=timestamp(); END',
- 'DROP TRIGGER IF EXISTS bar_trigger_insert',
- 'CREATE TRIGGER bar_trigger_insert before insert on Basic BEGIN update modified2=timestamp(); END',
- 'DROP TRIGGER IF EXISTS bar_trigger_update',
- 'CREATE TRIGGER bar_trigger_update before update on Basic BEGIN update modified2=timestamp(); END',
+ 'emptytagdef' varchar DEFAULT '',
+ 'another_id' int(10) DEFAULT 2,
+ 'timest' timestamp,
+ FOREIGN KEY('another_id') REFERENCES 'Another'()
+)>,
+ q<CREATE INDEX 'titleindex' ON 'Basic' ('title')>,
+ q<CREATE UNIQUE INDEX 'emailuniqueindex' ON 'Basic' ('email')>,
+ q<CREATE UNIQUE INDEX 'very_long_index_name_on_title_field_which_should_be_truncated_for_various_rdbms' ON 'Basic' ('title')>,
+ q<DROP TABLE 'Another'>,
+ q<CREATE TABLE 'Another' (
+ 'id' INTEGER PRIMARY KEY NOT NULL,
+ 'num' numeric(10,2)
+)>,
+ q<DROP VIEW IF EXISTS 'email_list'>,
+ q<CREATE VIEW 'email_list' AS
+ SELECT email FROM Basic WHERE (email IS NOT NULL)>,
+ q<DROP TRIGGER IF EXISTS 'foo_trigger'>,
+ q<CREATE TRIGGER 'foo_trigger' after insert on 'Basic' BEGIN update modified=timestamp(); END>,
+ q<DROP TRIGGER IF EXISTS 'bar_trigger_insert'>,
+ q<CREATE TRIGGER 'bar_trigger_insert' before insert on 'Basic' BEGIN update modified2=timestamp(); END>,
+ q<DROP TRIGGER IF EXISTS 'bar_trigger_update'>,
+ q<CREATE TRIGGER 'bar_trigger_update' before update on 'Basic' BEGIN update modified2=timestamp(); END>,
'COMMIT',
], 'SQLite translate in list context matches');
my $create_opts = { no_comments => 1 };
my $view1_sql1 = [ SQL::Translator::Producer::SQLite::create_view($view1, $create_opts) ];
- my $view_sql_replace = [ "CREATE TEMPORARY VIEW IF NOT EXISTS view_foo AS
+ my $view_sql_replace = [ "CREATE TEMPORARY VIEW IF NOT EXISTS 'view_foo' AS
SELECT id, name FROM thing" ];
is_deeply($view1_sql1, $view_sql_replace, 'correct "CREATE TEMPORARY VIEW" SQL');
sql => 'SELECT id, name FROM thing',);
my $view1_sql2 = [ SQL::Translator::Producer::SQLite::create_view($view2, $create_opts) ];
- my $view_sql_noreplace = [ "CREATE VIEW view_foo AS
+ my $view_sql_noreplace = [ "CREATE VIEW 'view_foo' AS
SELECT id, name FROM thing" ];
is_deeply($view1_sql2, $view_sql_noreplace, 'correct "CREATE VIEW" SQL');
}
my $view1_sql1 =
[ SQL::Translator::Producer::SQLite::create_view( $view1, $create_opts ) ];
- my $view_sql_replace = [ "CREATE TEMPORARY VIEW IF NOT EXISTS view_foo AS
+ my $view_sql_replace = [ "CREATE TEMPORARY VIEW IF NOT EXISTS 'view_foo' AS
SELECT id, name FROM thing" ];
is_deeply( $view1_sql1, $view_sql_replace, 'correct "CREATE TEMPORARY VIEW" SQL' );
my $view1_sql2 =
[ SQL::Translator::Producer::SQLite::create_view( $view2, $create_opts ) ];
- my $view_sql_noreplace = [ "CREATE VIEW view_foo AS
+ my $view_sql_noreplace = [ "CREATE VIEW 'view_foo' AS
SELECT id, name FROM thing" ];
is_deeply( $view1_sql2, $view_sql_noreplace, 'correct "CREATE VIEW" SQL' );
}
projects / dbsrgits/SQL-Translator.git / commitdiff