Well, first we have to fix the problem... ;) But this is a question I'm not really knowlegeable enough to answer. I'll put the question to the list in later today.
Ben On Thu, 2007-11-08 at 12:48 -0800, Brian O'Connor wrote: > 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
