Here's my patch for SQLite producer sucking. it could use some more tests,
but ENOTUITS. I've used itthough and it worked well for me if that counts
for anything.
--G
On Fri, Jan 16, 2009 at 6:56 AM, Ash Berlin <[email protected]> wrote:
>
> On 16 Jan 2009, at 11:44, Peter Rabbitson wrote:
>
> [email protected] wrote:
>>
>>> Patches to lib/SQL/Translator/Producer/SQLite.pm welcome :P
>>>>
>>>>
>>> Again, as file, previous mail had some additional line breaks
>>>
>>>
>>>
>> Patch seems reasonable. Please provide a diff including tests against
>> current trunk[1]. Fix the currently failing tests in t/48xml-to-sqlite
>> and t/30sqlt-new-diff-sqlite, by explicitly disabling quoting, and then
>> add new ones to test if quoting works correctly. I will bug maints to
>> apply this for you when it's ready.
>>
>> [1] https://sqlfairy.svn.sourceforge.net/svnroot/sqlfairy/trunk/sqlfairy
>>
>
> Sitting waiting for a patch. Let me know when you think its good to apply.
>
> -ash
>
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/[email protected]
>
--
Guillermo Roditi (groditi)
==== Patch <SQLite-field-table-quoting> level 1
Source: [No source]
Target: 43122cfd-0f3f-0410-9171-884ef7c37379:/trunk/sqlfairy:1464
(https://sqlfairy.svn.sourceforge.net/svnroot/sqlfairy)
Log:
properly quote fields and tables in SQLite
=== Changes
==================================================================
--- Changes (revision 1464)
+++ Changes (patch SQLite-field-table-quoting level 1)
@@ -2,6 +2,7 @@
# x.xxxxx xxxx-xx-xx
# ----------------------------------------------------------
* Properly quote absolute table names in the MySQL producer
+* Properly quote tables and fields for SQLite (groditi)
# ----------------------------------------------------------
# 0.09004 2009-02-13
=== lib/SQL/Translator/Producer/SQLite.pm
==================================================================
--- lib/SQL/Translator/Producer/SQLite.pm (revision 1464)
+++ lib/SQL/Translator/Producer/SQLite.pm (patch SQLite-field-table-quoting level 1)
@@ -62,6 +62,9 @@
my $producer_args = $translator->producer_args;
my $sqlite_version = $producer_args->{sqlite_version} || 0;
my $no_txn = $producer_args->{no_transaction};
+ my ($qt, $qf) = ('','');
+ $qt = '"' if $translator->quote_table_names;
+ $qf = '"' if $translator->quote_field_names;
debug("PKG: Beginning production\n");
@@ -70,13 +73,19 @@
$create[0] .= "\n\nBEGIN TRANSACTION" unless $no_txn;
for my $table ( $schema->get_tables ) {
- push @create, create_table($table, { no_comments => $no_comments,
- sqlite_version => $sqlite_version,
- add_drop_table => $add_drop_table,});
- }
+ push @create, create_table($table, {
+ no_comments => $no_comments,
+ quote_table_names => $qt,
+ quote_field_names => $qf,
+ sqlite_version => $sqlite_version,
+ add_drop_table => $add_drop_table,
+ });
+ }
for my $view ( $schema->get_views ) {
push @create, create_view($view, {
+ quote_table_names => $qt,
+ quote_field_names => $qf,
add_drop_view => $add_drop_table,
no_comments => $no_comments,
});
@@ -84,6 +93,8 @@
for my $trigger ( $schema->get_triggers ) {
push @create, create_trigger($trigger, {
+ quote_table_names => $qt,
+ quote_field_names => $qf,
add_drop_trigger => $add_drop_table,
no_comments => $no_comments,
});
@@ -139,8 +150,8 @@
sub create_view {
my ($view, $options) = @_;
my $add_drop_view = $options->{add_drop_view};
-
- my $view_name = $view->name;
+ my $qt = $options->{quote_table_names} || '';
+ my $view_name = quote_table_name($view->name, $qt);
debug("PKG: Looking at view '${view_name}'\n");
# Header. Should this look like what mysqldump produces?
@@ -164,8 +175,9 @@
sub create_table
{
my ($table, $options) = @_;
-
- my $table_name = $table->name;
+ my $qt = $options->{quote_table_names} || '';
+ my $qf = $options->{quote_field_names} || '';
+ my $table_name = quote_table_name($table->name, $qt);
my $no_comments = $options->{no_comments};
my $add_drop_table = $options->{add_drop_table};
my $sqlite_version = $options->{sqlite_version} || 0;
@@ -211,7 +223,7 @@
#
my ( @field_defs, $pk_set );
for my $field ( @fields ) {
- push @field_defs, create_field($field);
+ push @field_defs, create_field($field, {quote_table_names => $qt, quote_field_names => $qf});
}
if (
@@ -227,7 +239,7 @@
#
my $idx_name_default = 'A';
for my $index ( $table->get_indices ) {
- push @index_defs, create_index($index);
+ push @index_defs, create_index($index, {quote_table_names => $qt, quote_field_names => $qf});
}
#
@@ -236,7 +248,7 @@
my $c_name_default = 'A';
for my $c ( $table->get_constraints ) {
next unless $c->type eq UNIQUE;
- push @constraint_defs, create_constraint($c);
+ push @constraint_defs, create_constraint($c, {quote_table_names => $qt, quote_field_names => $qf});
}
$create_table .= join(",\n", map { " $_" } @field_defs ) . "\n)";
@@ -247,8 +259,10 @@
sub create_field
{
my ($field, $options) = @_;
+ $options ||= {};
+ my $qf = $options->{quote_field_names} || '';
- my $field_name = $field->name;
+ my $field_name = $qf.$field->name.$qf;
debug("PKG: Looking at field '$field_name'\n");
my $field_comments = $field->comments
? "-- " . $field->comments . "\n "
@@ -327,9 +341,11 @@
sub create_index
{
my ($index, $options) = @_;
-
+ $options ||= {};
+ my $qt = $options->{quote_table_names} || '';
+ my $qf = $options->{quote_field_names} || '';
my $name = $index->name;
- $name = mk_name($index->table->name, $name);
+ $name = $qf.mk_name($index->table->name, $name).$qf;
my $type = $index->type eq 'UNIQUE' ? "UNIQUE " : '';
@@ -338,8 +354,8 @@
(my $index_table_name = $index->table->name) =~ s/^.+?\.//; # table name may not specify schema
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 .
- ' (' . join( ', ', @fields ) . ')';
+ "CREATE ${type}INDEX $name ON " . quote_table_name($index_table_name,$qt) .
+ ' (' . join( ', ', map { $qf.$_.$qf } @fields ) . ')';
return $index_def;
}
@@ -347,24 +363,27 @@
sub create_constraint
{
my ($c, $options) = @_;
-
+ $options ||= {};
+ my $qt = $options->{quote_table_names} || '';
+ my $qf = $options->{quote_field_names} || '';
my $name = $c->name;
- $name = mk_name($c->table->name, $name);
+ $name = $qf.mk_name($c->table->name, $name).$qf;
my @fields = $c->fields;
(my $index_table_name = $c->table->name) =~ s/^.+?\.//; # table name may not specify schema
warn "removing schema name from '" . $c->table->name . "' to make '$index_table_name'\n" if $WARN;
my $c_def =
- "CREATE UNIQUE INDEX $name ON " . $index_table_name .
- ' (' . join( ', ', @fields ) . ')';
+ "CREATE UNIQUE INDEX $name ON " . quote_table_name($index_table_name, $qt) .
+ ' (' . join( ', ', map { $qf.$_.$qf } @fields ) . ')';
return $c_def;
}
sub create_trigger {
my ($trigger, $options) = @_;
+ $options ||= {};
my $add_drop = $options->{add_drop_trigger};
-
+ my $qt = $options->{quote_table_names} || '';
my $name = $trigger->name;
my @create;
@@ -397,7 +416,7 @@
push @create, "CREATE TRIGGER $name " .
$trigger->perform_action_when . " " .
$events->[0] .
- " on " . $trigger->on_table . " " .
+ " on " . quote_table_name($trigger->on_table, $qt) . " " .
$action;
return @create;
@@ -407,23 +426,24 @@
sub alter_table { } # Noop
sub add_field {
- my ($field) = @_;
-
- return sprintf("ALTER TABLE %s ADD COLUMN %s",
- $field->table->name, create_field($field))
+ my ($field, $options) = @_;
+ $options ||= {};
+ my $qt = $options->{quote_table_names} || '';
+ my $tbl = quote_table_name($field->table->name, $qt);
+ return sprintf("ALTER TABLE %s ADD COLUMN %s", $tbl, create_field($field));
}
sub alter_create_index {
- my ($index) = @_;
-
+ my ($index, $options) = @_;
+ $options ||= {};
# This might cause name collisions
- return create_index($index);
+ return create_index($index, $options);
}
sub alter_create_constraint {
- my ($constraint) = @_;
-
- return create_constraint($constraint) if $constraint->type eq 'UNIQUE';
+ my ($constraint, $options) = @_;
+ $options ||= {};
+ return create_constraint($constraint, $options) if $constraint->type eq 'UNIQUE';
}
sub alter_drop_constraint { alter_drop_index(@_) }
@@ -436,8 +456,10 @@
}
sub batch_alter_table {
- my ($table, $diffs) = @_;
-
+ my ($table, $diffs, $options) = @_;
+ $options ||= {};
+ my $qt = $options->{quote_table_names} || '';
+ my $qf = $options->{quote_field_names} || '';
# If we have any of the following
#
# rename_field
@@ -491,32 +513,44 @@
do {
local $table->{name} = $table_name . '_temp_alter';
# We only want the table - dont care about indexes on tmp table
- my ($table_sql) = create_table($table, {no_comments => 1, temporary_table => 1});
+ my ($table_sql) = create_table($table, {%$options, no_comments => 1, temporary_table => 1});
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]}",
- 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";
+ my $temp_tbl = $table_name."_temp_alter";
+ my $fields = join(', ', map { $qf.$_.$qf } $old_table->get_fields);
+ push(@sql,
+ "INSERT INTO ".quote_table_name($temp_tbl,$qt)." SELECT ${fields} FROM ".quote_table_name($old_table, $qt),
+ drop_table($old_table,$options),
+ create_table($table, { %$options, no_comments => 1 }),
+ "INSERT INTO ".quote_table_name($table_name,$qt)." SELECT ${fields} FROM ".quote_table_name($temp_tbl, $qt),
+ drop_table($temp_tbl, $options),
+ );
+
return @sql;
# return join("", @sql, "");
}
+sub quote_table_name {
+ my ($table_name, $qt) = @_;
+ $table_name =~ s/\./$qt.$qt/g;
+ return "${qt}${table_name}${qt}";
+}
+
+
sub drop_table {
- my ($table) = @_;
- return "DROP TABLE $table";
+ my ($table, $options) = @_;
+ $options ||= {};
+ my $qt = $options->{quote_table_names} || '';
+ return "DROP TABLE ".quote_table_name($table,$qt);
}
sub rename_table {
my ($old_table, $new_table, $options) = @_;
-
+ $options ||= {};
my $qt = $options->{quote_table_names} || '';
-
- return "ALTER TABLE $qt$old_table$qt RENAME TO $qt$new_table$qt";
-
+ return "ALTER TABLE ".quote_table_name($old_table, $qt)." RENAME TO ".quote_table_name($new_table,$qt);
}
# No-op. Just here to signify that we are a new style parser.
=== t/56-sqlite-producer.t
==================================================================
--- t/56-sqlite-producer.t (revision 1464)
+++ t/56-sqlite-producer.t (patch SQLite-field-table-quoting level 1)
@@ -2,7 +2,7 @@
# vim: set ft=perl:
use strict;
-use Test::More tests => 2;
+use Test::More tests => 4;
use Test::SQL::Translator qw(maybe_plan);
use FindBin qw/$Bin/;
@@ -33,4 +33,22 @@
my $view_sql_noreplace = "CREATE VIEW view_foo AS
SELECT id, name FROM thing";
is($view1_sql2, $view_sql_noreplace, 'correct "CREATE VIEW" SQL');
+
+
+ my $view1_sql_q = SQL::Translator::Producer::SQLite::create_view(
+ $view2, {%$create_opts, quote_table_names => '"', quote_field_names => '"'}
+ );
+ my $view_sql_noreplace_q = 'CREATE VIEW "view_foo" AS
+ SELECT id, name FROM thing';
+ is($view1_sql_q, $view_sql_noreplace_q, 'correct "CREATE VIEW" SQL');
}
+{
+
+}
+
+
+is(
+ SQL::Translator::Producer::SQLite::quote_table_name('foo.bar','"'),
+ '"foo"."bar"',
+ 'quote_table_name',
+);
==== BEGIN SVK PATCH BLOCK ====
Version: svk v2.0.2 (linux)
eJzNWFtr49gdF2Xpgwt9aAuFpdCDRsFO8U0XS7an43U644XMZDKziWd3SgvqsXSUqCtLjiTPTrC8
2AkttEMZytK3vvVpnwvTlv0IfSm0T92y7GdY9gP0f46ObTm2k8zshQ0hkfS//f7Xc3kz7N5sy0mr
VU0kuZocvn2v2XyIY+t4C94kPSG2GwehVEs88oR4kpp4wZGkJT7uE6DGODwicaslg3A9Fe4wgZkS
jero4TjwI6nBlJlxSIgkJ402GGgrSVsF2UQGajAgvhkGQQzWFF3XZKCaFJTlBRExqXBbS9o1yq/M
+B3XI5KR3D7G/hGJmKaZNGNUJVlLOW03JBZAO6VOuL1V1voaVjmJlxhViqjGEWXZKFhQw6h4MPBO
zZg8jW3ixZjBVZVEt0i9hhtGvaERHes9zarK1arl6Bq2VdVwJEOHEO4Lwsfe/6ScIUzvCX8T/itO
7/4EPQwBV+idopNhEBMU455HIoR9Gzku8ewIOUGIDt/ac4FYOAoDCJW7DcHKhI8FiiNxiGEoFq5V
DUvv2UqtV21g1ZZxFVuyYjQaqS/Ara0JiUGjVwFjLLpXBkadK6uti299pq3SDbEfeZhWGzV8pWJt
rlhfo1gx1iiuQBztoUVChuZKC7W5BWOp3NRLdVfSPJQHfQbtSiv6pZVj0HwRrY6tmuJgq6HrjiY3
iFWrq7pCqgq2HKMHiBq1tHY+N6Zno6nw547w/BfCZCycfff5zyfTx0Lvg62PhOndD97/SPjDoz+9
MTkWfvP4L9pk+iPh99/7sPxCOHv61/YLYbr/og5/7//925MT4Y/f+sdwMu0K57v/vDc5f004+/G/
gslj4Vf//ukkEH77xn92PxTGH78+OR8L0/c+eTyZviY8a336zuR3rwtnP/jsBoKf/ikqSCdxEUkn
zja6hQr5fDGf376Zo0QgwKe8mEeug6R4HshSixW5yYrcpIMmmgk4lwqwVkgFGH/64wemFfT7xI8j
dKuFpMx7McO2YpMxA/YVnoyZlMfJ8kQnHiTffELCyA18xrD8KcuMbdu0obVTu4x5+dOCecyidjXK
jRi/gChPZZowKRjE4EZUao1WVI5RkqB8Pk0XlXjikvcYCQQvchcYtdSiz7RC4u1Xt8MqY0Ui48qK
xELbWmjsOYutiFZhzCK4MXLjr1OKtQgPAXh7C43GrxKguciCSEVPnHL6JQ1KmWq81ODXkEOGDVEp
17fJ0xQZnxWclpIBfP9dnlnOupxg+m+b+rRaCYw/86XIKrU875o8TDVURr8OXL+A8kX628cDNEpD
ZrJAjVGbr9MgifLbszCHJB6GPodv2sS5mRvnctGwh6yQYABiQShg0Ll+nBvN3C5IVnEeJzpV2yb3
+ZuRC+t6ebC+SA7QqyRhTXwo8tnEXfZ4MYdD9+iIhHP2awdzzVBJVZVagc/HOx8tq9ncgPVlMsmm
XM9bO954J69MOSrHizIahFB2TkHc2et2DlB352d7HbQVoZ07d9DtB3uP7u/Dm1hkNoqzemV6ufrt
7Xk1Yy8mocl5WDLRKMeLmb1eMwQ3UPfYjVDfPTqOkYWHEUGsvqzA81y6uEYL/Flrq1bWQ1u02wLf
4ts1QS7bX4ivV/UVJv/aTby1AHNhtyQXUUz6gyDE4elikyKP+RZuMIyOURv2N0W+nsIjpYzZfGMF
CNJmWoWZJbcsmozAgi/O8PIReStt442jVAoAP69cOHymDtGM5lJABQZoNhrE3f3DzkEX7e53HyCx
vKYpOUI218siOuzsdW53kTRK9Y7RmwcP7q+VnOPgbTwzudi+ZXnmaZ7z8QrhnFzTCF2SDdgFvoRj
y0vWS7k2D8pmzzIsFzxLU8HboM1K4sb8neVWhLnBsoREcdGKF2EsenBl8s87JbuNex9FlV+WK8AA
lRJXjjLNKEqjk3gsjRbsY/ZFZMaZ9cw+fNnuV9ShM2R3Dh485NN1YxaL6WzmcQoJJV3Emq1GH3bd
G7F/qeizi8M1egSK8KCzv3O/gzYU7QI5c3ntFQY7EmuW3rBVWXPUnq4rslqrKTq2qj0dy726Yevz
o3tj6eiuKElcqeml9ExWGvAzezlm9z9XntXrl57VGxSYohhGr6bXdcXWbFuv2jVDN4iCdV22jKpu
S5rOr3memc9/+J3zm8KuMBWeff/u5LypzcYmPRnJdJqaJ5CTw7f2ms3FTUOzObtqaDbTu4Zmk88S
KldIc0wfFZgnWxKnQWKj4voTJJypNxwsgDLOsZbOHOgoLj8IycDDFmEI87cPOjvdDnp7t/MOEhmT
EwQi2jlkYPjgce1iul6zuRMfu/4RKyY3KmQ9Lq43A+uBFYQ0F0jMmBNpePKsO0Y51s2gDpReI2gr
5ZcH0OUeDvNF8Bv2ZaAmL1JHyiJ8hSDRDxel4CtYX1uprCAsx7F7DtZ0p+YQq9Yj2LDshiU3rLpq
Q7Gy+0VNSQbLd3x8NaR3fPy6z/X5FZ+kQAWxp1K6kUtXQyoHIW21lITWObuN7bIL2mbzke/S+wfs
bemJpCUDHB9LahKSJ/AyHLo2LehKHA79dysQdge7UO5aImu6Jql6oqmyoliOXao6qlOqanK11JAN
uVSva8QxLNVQjcb/AcxrI3g=
==== END SVK PATCH BLOCK ====
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
