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;
So SQL::Translator doesn't seem to like this constraint declaration. I
tried this with verion 0.07 and 0.08_4 along with CVS head of
SQL::Translator. I'm running on centos4 with version 7.4.17 of
postgres-server.
Has anyone seen this before? Is there a solution? SQL::Translator
works fine if I use a postgres schema not produced by a pg_dump (see
wine.sql).
Thanks for your help.
--Brian O'Connor
CREATE TABLE wine (
wine_id serial NOT NULL,
primary key (wine_id),
name varchar(50) NOT NULL,
year varchar(4) NOT NULL,
region varchar(50),
label varchar(100),
description text
);
CREATE TABLE vineyard (
vineyard_id serial NOT NULL,
primary key (vineyard_id),
name varchar(50) NOT NULL,
region varchar(50) NOT NULL,
country varchar(50) NOT NULL
);
CREATE TABLE review (
review_id serial NOT NULL,
primary key (review_id),
stars int NOT NULL,
text text,
location varchar(50)
);
CREATE TABLE inventory (
inventory_id serial NOT NULL,
primary key (inventory_id),
cases int NOT NULL,
lastorder date,
lastinvoice date
);
CREATE TABLE vineyard_wine (
vineyard_wine_id serial NOT NULL,
primary key(vineyard_wine_id),
vineyard_id int NOT NULL,
FOREIGN KEY (vineyard_id) REFERENCES vineyard (vineyard_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 vineyard_wine_const unique (vineyard_id,wine_id)
);
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)
);
CREATE TABLE inventory_wine (
inventory_wine_id serial NOT NULL,
primary key(inventory_wine_id),
inventory_id int NOT NULL,
FOREIGN KEY (inventory_id) REFERENCES inventory (inventory_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 inventory_wine_const unique (inventory_id,wine_id)
);
--
-- PostgreSQL database dump
--
SET client_encoding = 'SQL_ASCII';
SET check_function_bodies = false;
SET SESSION AUTHORIZATION 'postgres';
SET search_path = public, pg_catalog;
--
-- TOC entry 5 (OID 26895559)
-- Name: wine; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE wine (
wine_id serial NOT NULL,
name character varying(50) NOT NULL,
"year" character varying(4) NOT NULL,
region character varying(50),
label character varying(100),
description text
);
--
-- TOC entry 6 (OID 26895569)
-- Name: vineyard; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE vineyard (
vineyard_id serial NOT NULL,
name character varying(50) NOT NULL,
region character varying(50) NOT NULL,
country character varying(50) NOT NULL
);
--
-- TOC entry 7 (OID 26895576)
-- Name: review; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE review (
review_id serial NOT NULL,
stars integer NOT NULL,
text text,
"location" character varying(50)
);
--
-- TOC entry 8 (OID 26895586)
-- Name: inventory; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE inventory (
inventory_id serial NOT NULL,
cases integer NOT NULL,
lastorder date,
lastinvoice date
);
--
-- TOC entry 9 (OID 26895593)
-- Name: vineyard_wine; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE vineyard_wine (
vineyard_wine_id serial NOT NULL,
vineyard_id integer NOT NULL,
wine_id integer NOT NULL
);
--
-- TOC entry 10 (OID 26895610)
-- Name: review_wine; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE review_wine_id serial NOT NULL,
review_id integer NOT NULL,
wine_id integer NOT NULL
);
--
-- TOC entry 11 (OID 26895627)
-- Name: inventory_wine; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE inventory_wine (
inventory_wine_id serial NOT NULL,
inventory_id integer NOT NULL,
wine_id integer NOT NULL
);
--
-- TOC entry 12 (OID 26895565)
-- Name: wine_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY wine
ADD CONSTRAINT wine_pkey PRIMARY KEY (wine_id);
--
-- TOC entry 13 (OID 26895572)
-- Name: vineyard_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY vineyard
ADD CONSTRAINT vineyard_pkey PRIMARY KEY (vineyard_id);
--
-- TOC entry 14 (OID 26895582)
-- Name: review_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY review
ADD CONSTRAINT review_pkey PRIMARY KEY (review_id);
--
-- TOC entry 15 (OID 26895589)
-- Name: inventory_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY inventory
ADD CONSTRAINT inventory_pkey PRIMARY KEY (inventory_id);
--
-- TOC entry 17 (OID 26895596)
-- Name: vineyard_wine_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY vineyard_wine
ADD CONSTRAINT vineyard_wine_pkey PRIMARY KEY (vineyard_wine_id);
--
-- TOC entry 16 (OID 26895598)
-- Name: vineyard_wine_const; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY vineyard_wine
ADD CONSTRAINT vineyard_wine_const UNIQUE (vineyard_id, wine_id);
--
-- TOC entry 19 (OID 26895613)
-- Name: review_wine_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY review_wine
ADD CONSTRAINT review_wine_pkey PRIMARY KEY (review_wine_id);
--
-- TOC entry 18 (OID 26895615)
-- Name: review_wine_const; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY review_wine
ADD CONSTRAINT review_wine_const UNIQUE (review_id, wine_id);
--
-- TOC entry 21 (OID 26895630)
-- Name: inventory_wine_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY inventory_wine
ADD CONSTRAINT inventory_wine_pkey PRIMARY KEY (inventory_wine_id);
--
-- TOC entry 20 (OID 26895632)
-- Name: inventory_wine_const; Type: CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY inventory_wine
ADD CONSTRAINT inventory_wine_const UNIQUE (inventory_id, wine_id);
--
-- TOC entry 22 (OID 26895600)
-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY vineyard_wine
ADD CONSTRAINT "$1" FOREIGN KEY (vineyard_id) REFERENCES
vineyard(vineyard_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
--
-- TOC entry 23 (OID 26895604)
-- Name: $2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY vineyard_wine
ADD CONSTRAINT "$2" FOREIGN KEY (wine_id) REFERENCES wine(wine_id) ON
DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
--
-- TOC entry 24 (OID 26895617)
-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY review_wine
ADD CONSTRAINT "$1" FOREIGN KEY (review_id) REFERENCES review(review_id) ON
DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
--
-- TOC entry 25 (OID 26895621)
-- Name: $2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY review_wine
ADD CONSTRAINT "$2" FOREIGN KEY (wine_id) REFERENCES wine(wine_id) ON
DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
--
-- TOC entry 26 (OID 26895634)
-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY inventory_wine
ADD CONSTRAINT "$1" FOREIGN KEY (inventory_id) REFERENCES
inventory(inventory_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
--
-- TOC entry 27 (OID 26895638)
-- Name: $2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY inventory_wine
ADD CONSTRAINT "$2" FOREIGN KEY (wine_id) REFERENCES wine(wine_id) ON
DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
--
-- TOC entry 3 (OID 2200)
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--
COMMENT ON SCHEMA public IS 'Standard public schema';
-------------------------------------------------------------------------
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
