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/ -- sqlfairy-developers mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/sqlfairy-developers
