The $1 was a red herring. (The parser doesn't handle that "correctly"
but that wasn't why it was giving an error. More on that later in a
later msg.)
It turned out that the "deferrable" definition was requiring a "not"
when that wasn't needed.
I've included a patch to the PostgreSQL parser that fixes that issue.
Later today, I'll commit the change to CVS (unless there are
complaints).
Ben
On Thu, 2007-11-08 at 13:14 -0600, Ben Faga wrote:
> After briefly looking at this, I believe that "$1" as the constraint
> name is automatically interpreted by postgres as a unique constraint
> name.
>
> I suspect that was used to avoid collisions when printing out foreign
> key constraint names (which were not supplied in the original).
>
> NOTE: This is not the behavior of postgres 8.0, it just makes up a name
> from the table/column name in the form of TABLENAME_COLUMNNAME_fkey
> (such as vineyard_wine_vineyard_id_fkey).
>
> I'm guessing that is how Translator should handle it.
>
> As an aside, you could use 8.0 but that seems to have a different issue
> that I'm going to look into.
>
> Ben
>
> On Mon, 2007-08-13 at 04:37 -0700, Brian O'Connor wrote:
> > Hi Jess,
> >
> > Thanks for your help. Please see my comments below...
> >
> > Jess Robinson wrote:
> >
> > >Hi Brian,
> > >
> > >On Mon, 6 Aug 2007, Brian O'Connor wrote:
> > >
> > >
> > >
> > >>Hi,
> > >>
> > >>I'm trying to use pg_dump output with SQL::Translator and I keep running
> > >>into
> > >>the same problem. I create a schema file, such as the attached file
> > >>wine_pg_dump.sql, using the "pg_dump -sx" command. When I try to run
> > >>SQL::Translator on this file I get the following error:
> > >>
> > >>sqlt -f PostgreSQL -t MySQL /tmp/wine_pg_dump.sql > wine_pg_dump.mysql
> > >>
> > >>
> > >> ERROR (line 198): Invalid field: Was expecting data type but found
> > >> "$1"
> > >> FOREIGN KEY (vineyard_id) REFERENCES
> > >> vineyard(vineyard_id) ON DELETE CASCADE DEFERRABLE
> > >> INITIALLY DEFERRED;" instead
> > >>
> > >> ERROR (line 197): Invalid statement: Was expecting create, or comment
> > >> on
> > >> table, or comment on column, or comment on other, or
> > >> comment, or alter, or grant, or revoke, or drop, or
> > >> insert, or connect, or update, or set
> > >>Error: translate: Error with parser
> > >>'SQL::Translator::Parser::PostgreSQL':
> > >>Parse failed.
> > >>
> > >>Lines 197 and 198 are:
> > >>
> > >>ALTER TABLE ONLY vineyard_wine
> > >> ADD CONSTRAINT "$1" FOREIGN KEY (vineyard_id) REFERENCES
> > >>vineyard(vineyard_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
> > >>
> > >>
> > >>
> > >
> > >
> > >I suspect this is because the grammar for Postgres SQL parsing specifies
> > >\w for the names of tables, constraints etc, which does not include the
> > >character $. I can't seem to find documentation on the postgresql.org site
> > >that tells me which characters are allowed in names, anyone else see it
> > >anywhere?
> > >
> > >
> > I couldn't find any documentation on this other than:
> >
> > http://www.postgresql.org/docs/8.1/interactive/sql-createoperator.html
> >
> > but it's not really appropriate.
> >
> > >Is that some magical construct, or is it really the name of the
> > >constraint.. it's very strange looking.. ;)
> > >
> > >
> > It does look strange doesn't it! It seems to be something postgres is
> > doing internally when the DB is created. For example, the schema file I
> > use to load a table looks like:
> >
> > CREATE TABLE review_wine (
> > review_wine_id serial NOT NULL,
> > primary key(review_wine_id),
> > review_id int NOT NULL,
> > FOREIGN KEY (review_id) REFERENCES review (review_id) ON DELETE
> > cascade INITIALLY DEFERRED,
> > wine_id int NOT NULL,
> > FOREIGN KEY (wine_id) REFERENCES wine (wine_id) ON DELETE cascade
> > INITIALLY DEFERRED,
> > constraint review_wine_const unique (review_id,wine_id)
> > );
> >
> > When I load this in postgres and do a \d on the table I see:
> >
> > wine=# \d review_wine;
> > Table "public.review_wine"
> > Column | Type |
> > Modifiers
> > ----------------+---------+-------------------------------------------------------------------------
> > review_wine_id | integer | not null default
> > nextval('public.review_wine_review_wine_id_seq'::text)
> > review_id | integer | not null
> > wine_id | integer | not null
> > Indexes:
> > "review_wine_pkey" primary key, btree (review_wine_id)
> > "review_wine_const" unique, btree (review_id, wine_id)
> > Foreign-key constraints:
> > "$1" FOREIGN KEY (review_id) REFERENCES review(review_id) ON DELETE
> > CASCADE DEFERRABLE INITIALLY DEFERRED
> > "$2" FOREIGN KEY (wine_id) REFERENCES wine(wine_id) ON DELETE
> > CASCADE DEFERRABLE INITIALLY DEFERRED
> >
> > And when I dump using pg_dump I get:
> >
> > ...
> > ALTER TABLE ONLY review_wine
> > ADD CONSTRAINT "$1" FOREIGN KEY (review_id) REFERENCES
> > review(review_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
> > ...
> >
> > Postgres seems to add $1 as a constraint name automatically since it
> > wasn't in my original schema file. Other people must be running into
> > this since pg_dump is producing this strange output.
> >
> > >I'll fix this if someone can point out which characters Pg does allow.
> > >
> > >
> > Has anyone gotten back to you? I'd really like to see this change get
> > checked in since I'm dead in the water without the ability to parse
> > pg_dump output.
> >
> > Thanks very much for your help!!
> >
> > --Brian
> >
> > >Jess
> > >
> > >PS 0.08 is the latest version, not the 0.08_X series.
> > >
> > >
> > >
> > >-------------------------------------------------------------------------
> > >This SF.net email is sponsored by: Splunk Inc.
> > >Still grepping through log files to find problems? Stop.
> > >Now Search log events and configuration files using AJAX and a browser.
> > >Download your FREE copy of Splunk now >> http://get.splunk.com/
> > >
> > >
> >
> >
> > -------------------------------------------------------------------------
> > This SF.net email is sponsored by: Splunk Inc.
> > Still grepping through log files to find problems? Stop.
> > Now Search log events and configuration files using AJAX and a browser.
> > Download your FREE copy of Splunk now >> http://get.splunk.com/
>
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Splunk Inc.
> Still grepping through log files to find problems? Stop.
> Now Search log events and configuration files using AJAX and a browser.
> Download your FREE copy of Splunk now >> http://get.splunk.com/
--- /usr/local/src/sqlfairy/lib/SQL/Translator/Parser/PostgreSQL.pm 2006-08-26 06:34:49.000000000 -0500
+++ /usr/lib/perl5/site_perl/5.8.8/SQL/Translator/Parser/PostgreSQL.pm 2007-11-09 08:36:57.000000000 -0600
@@ -653,7 +653,7 @@ table_constraint_type : /primary key/i '
}
}
-deferrable : /not/i /deferrable/i
+deferrable : not(?) /deferrable/i
{
$return = ( $item[1] =~ /not/i ) ? 0 : 1;
}
@@ -806,6 +806,8 @@ alter_nullable : SET not_null
not_null : /not/i /null/i
+not : /not/i
+
add_column : ADD COLUMN(?)
alter_table : ALTER TABLE ONLY(?)
-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
--
sqlfairy-developers mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlfairy-developers
