Hey Ben Thanks for looking into this!! Is there going to be a new SQL::Translator release soon that will include this bug fix?
--Brian 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/ >_______________________________________________ >Turnkey-devel mailing list >[EMAIL PROTECTED] >https://lists.sourceforge.net/lists/listinfo/turnkey-devel > > ------------------------------------------------------------------------- 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
