Bug #6406 for Alzabo: Alzabo adds '"' to tablename

Mon May 24 15:11:59 2004 Guest - Ticket created

Subject:

Alzabo adds '"' to tablename

Upon adding a new table(the execution of sql), '"' gets added to the tablename. So my table, named 'Table', is called ' "Table" ', in the database. This applies to Alzabo::GUI::Mason, not shure about other methods of creating tables, maybe its only in PostGreSQL too. This is the cause to a lot of problems, if you try to use the table via $schema->table('"Table"'), Alzabo then says there is no such table. I am very, very new to Alzabo, Perl and programming in general, so this may or may not make much sense. Please ask if you don't understand.

Mon May 24 15:14:07 2004 Guest - Correspondence added

[guest - Mon May 24 15:11:59 2004]: Show quoted text

> Upon adding a new table(the execution of sql), '"' gets added to the > tablename. So my table, named 'Table', is called ' "Table" ', in > the database. This applies to Alzabo::GUI::Mason, not shure about > other methods of creating tables, maybe its only in PostGreSQL too. > > This is the cause to a lot of problems, if you try to use the table > via $schema->table('"Table"'), Alzabo then says there is no such > table. > > I am very, very new to Alzabo, Perl and programming in general, so > this may or may not make much sense. Please ask if you don't > understand.

Forgot; Debian testing, perl 5.8.3, DBI 1.41-1,Postgresql 7.4.2-4

Mon May 24 16:35:41 2004 autarch [...] urth.org - Correspondence added

Date:	Mon, 24 May 2004 15:35:53 -0500 (CDT)
From:	Dave Rolsky <autarch [...] urth.org>
To:	Guest via RT <bug-Alzabo [...] rt.cpan.org>
Subject:	Re: [cpan #6406] Alzabo adds '"' to tablename
RT-Send-Cc:

On Mon, 24 May 2004, Guest via RT wrote: Show quoted text

> Upon adding a new table(the execution of sql), '"' gets added to the > tablename. So my table, named 'Table', is called ' "Table" ', in the > database. This applies to Alzabo::GUI::Mason, not shure about other > methods of creating tables, maybe its only in PostGreSQL too. > > This is the cause to a lot of problems, if you try to use the table via > $schema->table('"Table"'), Alzabo then says there is no such table.

This is just quoting the name of the table, in case you name it something like "user", which is a reserved word for Postgres. The name of the table, as far as Alzabo is concerned, is whatever you gave it. I'll close this report, as it isn't a bug, unless the quoting itself is causing problems in the SQL, which I would be surprised to hear. -dave /*======================= House Absolute Consulting www.houseabsolute.com =======================*/

Mon May 24 16:36:58 2004 DROLSKY [...] cpan.org - Ticket deleted

Mon May 24 16:44:43 2004 Guest - Correspondence added

[autarch@urth.org - Mon May 24 16:35:41 2004]: Show quoted text

> On Mon, 24 May 2004, Guest via RT wrote: >

> > Upon adding a new table(the execution of sql), '"' gets added to the > > tablename. So my table, named 'Table', is called ' "Table" ', in the > > database. This applies to Alzabo::GUI::Mason, not shure about other > > methods of creating tables, maybe its only in PostGreSQL too. > > > > This is the cause to a lot of problems, if you try to use the table

> via

> > $schema->table('"Table"'), Alzabo then says there is no such table.

> > This is just quoting the name of the table, in case you name it > something > like "user", which is a reserved word for Postgres. The name of the > table, as far as Alzabo is concerned, is whatever you gave it. > > I'll close this report, as it isn't a bug, unless the quoting itself > is > causing problems in the SQL, which I would be surprised to hear.

Yes, exactly. Adding the table works fine, but the actually name in the database is ' "Table" '. The problem arises when I try to use this table in a application. $schema->table('Table') sets the table to 'Table', yes? But there isn't such a table as 'Table', its called '"Table"'(with the doble quotes, without the single quotes). Show quoted text

> > > -dave > > /*======================= > House Absolute Consulting > www.houseabsolute.com > =======================*/

Mon May 24 16:44:44 2004 The RT System itself - Status changed from 'dead' to 'open'

Mon May 24 21:33:29 2004 autarch [...] urth.org - Correspondence added

Date:	Mon, 24 May 2004 20:33:43 -0500 (CDT)
From:	Dave Rolsky <autarch [...] urth.org>
To:	Guest via RT <bug-Alzabo [...] rt.cpan.org>
Subject:	Re: [cpan #6406] Alzabo adds '"' to tablename
RT-Send-Cc:

On Mon, 24 May 2004, Guest via RT wrote: Show quoted text

> > I'll close this report, as it isn't a bug, unless the quoting itself > > is causing problems in the SQL, which I would be surprised to hear.

> Yes, exactly. Adding the table works fine, but the actually name in the database is ' "Table" '. > The problem arises when I try to use this table in a application. > > $schema->table('Table') sets the table to 'Table', yes? But there isn't such a table as 'Table', > its called '"Table"'(with the doble quotes, without the single quotes).

I'm confused, can you send me an example of some output that looks wrong to you, or an error, or something? -dave /*======================= House Absolute Consulting www.houseabsolute.com =======================*/

Tue May 25 02:29:46 2004 Guest - Correspondence added

From:

knuti [...] granvin.org

Show quoted text

> I'm confused, can you send me an example of some output that looks > wrong > to you, or an error, or something?

Generated using Alzabo::GUI::Mason CREATE TABLE "Test" ( "column1" INTEGER NOT NULL) ; With the psql-client: SELECT * FROM Test; ERROR: relation "test" doesn't exists SELECT * FROM "Test"; column1 --------- (0 rows)

Tue May 25 13:09:31 2004 autarch [...] urth.org - Correspondence added

Date:	Tue, 25 May 2004 12:09:45 -0500 (CDT)
From:	Dave Rolsky <autarch [...] urth.org>
To:	Guest via RT <bug-Alzabo [...] rt.cpan.org>
Subject:	Re: [cpan #6406] Alzabo adds '"' to tablename
RT-Send-Cc:

On Tue, 25 May 2004, Guest via RT wrote: Show quoted text

> Generated using Alzabo::GUI::Mason > > CREATE TABLE "Test" ( > "column1" INTEGER NOT NULL) > ; > > With the psql-client: > > SELECT * FROM Test; > ERROR: relation "test" doesn't exists > > SELECT * FROM "Test"; > column1 > --------- > (0 rows)

That's cause Postgres, by default, converts all table names to lower case, unless you quote the table name. -dave /*======================= House Absolute Consulting www.houseabsolute.com =======================*/

Tue May 25 16:02:47 2004 Guest - Correspondence added

From:

knuti [...] granvin.org

[autarch@urth.org - Tue May 25 13:09:31 2004]: Show quoted text

> That's cause Postgres, by default, converts all table names to lower > case, > unless you quote the table name.

But the SQL, generated by $schema->make_sql, is like that! When I try to edit a table, Alzabo::GUI::Mason says Postgresql says 'Table, no such relation'(or something similar), because its named ' "Table" ', in Postgresql. Is this a bug? If no, how can I change my copy of Alzabo to _not_ include this doble-quotes? / knuti

Tue May 25 16:28:07 2004 autarch [...] urth.org - Correspondence added

Date:	Tue, 25 May 2004 15:28:18 -0500 (CDT)
From:	Dave Rolsky <autarch [...] urth.org>
To:	Guest via RT <bug-Alzabo [...] rt.cpan.org>
Subject:	Re: [cpan #6406] Alzabo adds '"' to tablename
RT-Send-Cc:

On Tue, 25 May 2004, Guest via RT wrote: Show quoted text

> But the SQL, generated by $schema->make_sql, is like that! > > When I try to edit a table, Alzabo::GUI::Mason says Postgresql says 'Table, no such relation'(or > something similar), because its named ' "Table" ', in Postgresql.

Could you send me the actual error? Show quoted text

> Is this a bug?

I'm not sure. Show quoted text

> If no, how can I change my copy of Alzabo to _not_ include this doble-quotes?

You can't, but there's no reason to turn this off. It should just work. I think you're a bit confused about the tables names. The name is not '"Table"', with the quotes. The double-quotes are used to quote identifier names in Postgres, and Postgres interprets them appropriately. The only difference is that with the quotes "Table" is not the same as "table", and you can use reserved words as names. -dave /*======================= House Absolute Consulting www.houseabsolute.com =======================*/

Wed May 26 14:03:10 2004 Guest - Correspondence added

From:

knuti [...] granvin.org

[autarch@urth.org - Tue May 25 16:28:07 2004]: Show quoted text

> Could you send me the actual error?

The script I use to create a table, BlogEntries, to store blog entries: use Alzabo::Create::Schema; my $schema = Alzabo::Create::Schema->new(name => 'cype', rdbms => 'PostgreSQL'); my $table = $schema->make_table(name => 'BlogEntries'); my $id_c = $table->make_column(name => 'id', type => 'int', nullable => 0, sequenced => 1); ... $schema->create(user => '...', password => '...'); This executes fine and, as you tell me, I find a table ' "BlogEntries" '(without the single- quotes) in the database. Here's my script to get entries: use Alzabo::Runtime; my $schema = Alzabo::Runtime::Schema->load_from_file(name => 'cype'); $schema->connect(user => '...', password => '...'); my $entry = $schema->table('BlogEntries'); ... When I run this script, I get this error: DBD::Pg::st execute failed: ERROR: relation "blogentries" does not exist at /usr/local/share/ perl/5.8.3/Alzabo/Driver.pm line 521. SQL: SELECT BlogEntries.id AS id, BlogEntries.title AS title, BlogEntries.author AS author, BlogEntries.created AS created, BlogEntries.updated AS updated, BlogEntries.intro AS intro, BlogEntries.data AS data FROM BlogEntries AS BlogEntries WHERE BlogEntries.id LIKE ? BIND: 1 Trace begun at /usr/local/share/perl/5.8.3/Alzabo/Driver.pm line 529 Alzabo::DriverStatement::execute('Alzabo::DriverStatement=HASH(0x866b0a4)') called at / usr/local/share/perl/5.8.3/Alzabo/Driver.pm line 476 Alzabo::DriverStatement::new('Alzabo::DriverStatement', 'dbh', 'DBI::db=HASH(0x8659aa0)', 'sql', 'SELECT BlogEntries.id AS id, BlogEntries.title AS title, BlogEntries.author AS author, BlogEntries.created AS created, BlogEntries.updated AS updated, BlogEntries.intro AS intro, BlogEntries.data AS data FROM BlogEntries AS BlogEntries WHERE BlogEntries.id LIKE ?', 'bind', 'ARRAY(0x865f12c)', 'limit', undef) called at /usr/local/share/perl/5.8.3/Alzabo/Driver.pm line 285 Alzabo::Driver::statement('Alzabo::Driver::PostgreSQL=HASH(0x8173e0c)', 'sql', 'SELECT BlogEntries.id AS id, BlogEntries.title AS title, BlogEntries.author AS author, BlogEntries.created AS created, BlogEntries.updated AS updated, BlogEntries.intro AS intro, BlogEntries.data AS data FROM BlogEntries AS BlogEntries WHERE BlogEntries.id LIKE ?', 'bind', 'ARRAY(0x865f12c)', 'limit', undef) called at /usr/local/share/perl/5.8.3/Alzabo/Runtime/ Table.pm line 370 Alzabo::Runtime::Table::_cursor_by_sql(undef, 'where', 'ARRAY(0x864c44c)', 'sql', 'Alzabo:: SQLMaker::PostgreSQL=HASH(0x865f174)') called at /usr/local/share/perl/5.8.3/Alzabo/ Runtime/Table.pm line 281 Alzabo::Runtime::Table::rows_where('Alzabo::Runtime::Table=HASH(0x8173e18)', 'where', 'ARRAY(0x864c44c)') called at fetch.pl line 23 The doble-quoted name obviosly crashes with the not-quoted tablename used in fetch.pl. Is it possible to change fetch.pl to use quoted tablenames? Or another solution? /knuti

Wed May 26 14:05:26 2004 autarch [...] urth.org - Correspondence added

Date:	Wed, 26 May 2004 13:05:43 -0500 (CDT)
From:	Dave Rolsky <autarch [...] urth.org>
To:	Guest via RT <bug-Alzabo [...] rt.cpan.org>
Subject:	Re: [cpan #6406] Alzabo adds '"' to tablename
RT-Send-Cc:

On Wed, 26 May 2004, Guest via RT wrote: Show quoted text

> use Alzabo::Runtime; > > my $schema = Alzabo::Runtime::Schema->load_from_file(name => 'cype'); > $schema->connect(user => '...', password => '...'); > > my $entry = $schema->table('BlogEntries'); > ... > > When I run this script, I get this error: > > DBD::Pg::st execute failed: ERROR: relation "blogentries" does not exist at /usr/local/share/ > perl/5.8.3/Alzabo/Driver.pm line 521.

Ah, I see the problem. Try adding this before doing any SQL-y stuff: $schema->set_quote_identifiers(1); Alternately, name all of your tables with lower-case (argh, Postgres is dumb). I need to add something to the docs about this, I think. -dave /*======================= House Absolute Consulting www.houseabsolute.com =======================*/

Wed May 26 14:12:11 2004 Guest - Correspondence added

From:

knuti [...] granvin.org

[guest - Wed May 26 14:03:10 2004]: Probably forgot something important here: Show quoted text

> use Alzabo::Runtime; > > my $schema = Alzabo::Runtime::Schema->load_from_file(name => 'cype'); > $schema->connect(user => '...', password => '...'); > > my $entry = $schema->table('BlogEntries');

my $row_cursor = $entry->rows_where( where => [$entry->column('id'), 'LIKE', 1]); /knuti

Fri May 28 08:44:59 2004 Guest - Correspondence added

From:

knuti [...] granvin.org

[autarch@urth.org - Wed May 26 14:05:26 2004]: Show quoted text

> Ah, I see the problem. Try adding this before doing any SQL-y stuff: > > $schema->set_quote_identifiers(1); > > Alternately, name all of your tables with lower-case (argh, Postgres > is > dumb). > > I need to add something to the docs about this, I think.

Yes. Thanks for your patience and help. :-) /knuti

Fri May 28 08:44:59 2004 Guest - Correspondence added

[autarch@urth.org - Wed May 26 14:05:26 2004]: Show quoted text

> Ah, I see the problem. Try adding this before doing any SQL-y stuff: > > $schema->set_quote_identifiers(1); > > Alternately, name all of your tables with lower-case (argh, Postgres > is > dumb). > > I need to add something to the docs about this, I think.

Yes. Thanks for your patience and help. :-) /knuti

Thu Sep 09 09:40:11 2004 DROLSKY [...] cpan.org - Fixed in 0.83 added

Thu Sep 09 09:40:11 2004 DROLSKY [...] cpan.org - Status changed from 'open' to 'resolved'

Bug #6406 for Alzabo: Alzabo adds '"' to tablename

Maintainer(s)' notes