Hello,
The issues with Brian's Postgres 7.3 dump have been resolved (I believe)
but dumping his schema with Postgres 8.2.4 results in a file that SQLT
is unable to parse.
There are five different problems that I've laid out in this msg. I'm
not that familiar with the internals of Translator and I need help
(ranging from "Is it cool if I do this?" to "How on earth is it supposed
to handle this?").
Any suggestions or fixes are appriciated. I've attached the dump file
that I used for my testing (the dump of Brian's schema).
1. Schema-Qualified Table Names
In the Postgres 8.2.4 dump, some table names are schema-qualified. An
example is the line:
ALTER TABLE public.inventory OWNER TO faga;
The schema is "public" and the table is "inventory". Each of the parts
can be quoted (ie, "public"."inventory" is valid).
Modifying the table_name definition as follows keeps it from dieing BUT
THROWS AWAY THE SCHEMA QUALIFICATION. I'm not sure if that is the
correct thing to do.
table_name : schema_qualification(?) name_with_opt_quotes { $item[2] }
schema_qualification : name_with_opt_quotes '.'
2. "ALTER SEQUENCE"
The "ALTER SEQUENCE" command isn't defined in the parser. The following
causes an error.
ALTER SEQUENCE inventory_inventory_id_seq OWNED BY inventory.inventory_id;
A simple fix is to add definition catch this case but I am unsure what
to do with it. Should it just be discarded?
Possible definition:
alter : ALTER /sequence/i /[^;]+/ ';' { 1 }
Alternative definition as catch-all:
alter : ALTER WORD /[^;]+/ ';' { 1 }
3. SELECT that calls a function.
In the Postgres 8.2.4 dump, there is a SELECT statement that calls a
function in Postgres.
SELECT pg_catalog.setval('inventory_inventory_id_seq', 1, false);
This isn't currently handled at all. The only possibilities that I can
think of are to either simply ignore it or to implement definitions for
each possible Postgres function.
Thoughts?
4. COPY statement and "\."
Also not handled are copy statements.
pg_dump produces the following two statements:
COPY inventory (inventory_id, cases, lastorder, lastinvoice) FROM stdin;
\.
I'm not even sure how this snippet is supposed to be used. I'm guessing
that the "\." slurps lines from stdin. The PosgreSQL parser doesn't
handle that.
5. REVOKE/GRANT on Schema
There is no definition for revoking or granting permissions on a schema
as in the following lines.
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
Any insight to any of these issues would be very much appreciated.
Thanks,
Ben
--
-- PostgreSQL database dump
--
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--
COMMENT ON SCHEMA public IS 'Standard public schema';
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: inventory; Type: TABLE; Schema: public; Owner: faga; Tablespace:
--
CREATE TABLE inventory (
inventory_id integer NOT NULL,
cases integer NOT NULL,
lastorder date,
lastinvoice date
);
ALTER TABLE public.inventory OWNER TO faga;
--
-- Name: inventory_inventory_id_seq; Type: SEQUENCE; Schema: public; Owner: faga
--
CREATE SEQUENCE inventory_inventory_id_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE public.inventory_inventory_id_seq OWNER TO faga;
--
-- Name: inventory_inventory_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: faga
--
ALTER SEQUENCE inventory_inventory_id_seq OWNED BY inventory.inventory_id;
--
-- Name: inventory_inventory_id_seq; Type: SEQUENCE SET; Schema: public; Owner: faga
--
SELECT pg_catalog.setval('inventory_inventory_id_seq', 1, false);
--
-- Name: inventory_wine; Type: TABLE; Schema: public; Owner: faga; Tablespace:
--
CREATE TABLE inventory_wine (
inventory_wine_id integer NOT NULL,
inventory_id integer NOT NULL,
wine_id integer NOT NULL
);
ALTER TABLE public.inventory_wine OWNER TO faga;
--
-- Name: inventory_wine_inventory_wine_id_seq; Type: SEQUENCE; Schema: public; Owner: faga
--
CREATE SEQUENCE inventory_wine_inventory_wine_id_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE public.inventory_wine_inventory_wine_id_seq OWNER TO faga;
--
-- Name: inventory_wine_inventory_wine_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: faga
--
ALTER SEQUENCE inventory_wine_inventory_wine_id_seq OWNED BY inventory_wine.inventory_wine_id;
--
-- Name: inventory_wine_inventory_wine_id_seq; Type: SEQUENCE SET; Schema: public; Owner: faga
--
SELECT pg_catalog.setval('inventory_wine_inventory_wine_id_seq', 1, false);
--
-- Name: review; Type: TABLE; Schema: public; Owner: faga; Tablespace:
--
CREATE TABLE review (
review_id integer NOT NULL,
stars integer NOT NULL,
text text,
"location" character varying(50)
);
ALTER TABLE public.review OWNER TO faga;
--
-- Name: review_review_id_seq; Type: SEQUENCE; Schema: public; Owner: faga
--
CREATE SEQUENCE review_review_id_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE public.review_review_id_seq OWNER TO faga;
--
-- Name: review_review_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: faga
--
ALTER SEQUENCE review_review_id_seq OWNED BY review.review_id;
--
-- Name: review_review_id_seq; Type: SEQUENCE SET; Schema: public; Owner: faga
--
SELECT pg_catalog.setval('review_review_id_seq', 1, false);
--
-- Name: review_wine; Type: TABLE; Schema: public; Owner: faga; Tablespace:
--
CREATE TABLE review_wine (
review_wine_id integer NOT NULL,
review_id integer NOT NULL,
wine_id integer NOT NULL
);
ALTER TABLE public.review_wine OWNER TO faga;
--
-- Name: review_wine_review_wine_id_seq; Type: SEQUENCE; Schema: public; Owner: faga
--
CREATE SEQUENCE review_wine_review_wine_id_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE public.review_wine_review_wine_id_seq OWNER TO faga;
--
-- Name: review_wine_review_wine_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: faga
--
ALTER SEQUENCE review_wine_review_wine_id_seq OWNED BY review_wine.review_wine_id;
--
-- Name: review_wine_review_wine_id_seq; Type: SEQUENCE SET; Schema: public; Owner: faga
--
SELECT pg_catalog.setval('review_wine_review_wine_id_seq', 1, false);
--
-- Name: vineyard; Type: TABLE; Schema: public; Owner: faga; Tablespace:
--
CREATE TABLE vineyard (
vineyard_id integer NOT NULL,
name character varying(50) NOT NULL,
region character varying(50) NOT NULL,
country character varying(50) NOT NULL
);
ALTER TABLE public.vineyard OWNER TO faga;
--
-- Name: vineyard_vineyard_id_seq; Type: SEQUENCE; Schema: public; Owner: faga
--
CREATE SEQUENCE vineyard_vineyard_id_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE public.vineyard_vineyard_id_seq OWNER TO faga;
--
-- Name: vineyard_vineyard_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: faga
--
ALTER SEQUENCE vineyard_vineyard_id_seq OWNED BY vineyard.vineyard_id;
--
-- Name: vineyard_vineyard_id_seq; Type: SEQUENCE SET; Schema: public; Owner: faga
--
SELECT pg_catalog.setval('vineyard_vineyard_id_seq', 1, false);
--
-- Name: vineyard_wine; Type: TABLE; Schema: public; Owner: faga; Tablespace:
--
CREATE TABLE vineyard_wine (
vineyard_wine_id integer NOT NULL,
vineyard_id integer NOT NULL,
wine_id integer NOT NULL
);
ALTER TABLE public.vineyard_wine OWNER TO faga;
--
-- Name: vineyard_wine_vineyard_wine_id_seq; Type: SEQUENCE; Schema: public; Owner: faga
--
CREATE SEQUENCE vineyard_wine_vineyard_wine_id_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE public.vineyard_wine_vineyard_wine_id_seq OWNER TO faga;
--
-- Name: vineyard_wine_vineyard_wine_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: faga
--
ALTER SEQUENCE vineyard_wine_vineyard_wine_id_seq OWNED BY vineyard_wine.vineyard_wine_id;
--
-- Name: vineyard_wine_vineyard_wine_id_seq; Type: SEQUENCE SET; Schema: public; Owner: faga
--
SELECT pg_catalog.setval('vineyard_wine_vineyard_wine_id_seq', 1, false);
--
-- Name: wine; Type: TABLE; Schema: public; Owner: faga; Tablespace:
--
CREATE TABLE wine (
wine_id integer NOT NULL,
name character varying(50) NOT NULL,
"year" character varying(4) NOT NULL,
region character varying(50),
label character varying(100),
description text
);
ALTER TABLE public.wine OWNER TO faga;
--
-- Name: wine_wine_id_seq; Type: SEQUENCE; Schema: public; Owner: faga
--
CREATE SEQUENCE wine_wine_id_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE public.wine_wine_id_seq OWNER TO faga;
--
-- Name: wine_wine_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: faga
--
ALTER SEQUENCE wine_wine_id_seq OWNED BY wine.wine_id;
--
-- Name: wine_wine_id_seq; Type: SEQUENCE SET; Schema: public; Owner: faga
--
SELECT pg_catalog.setval('wine_wine_id_seq', 1, false);
--
-- Name: inventory_id; Type: DEFAULT; Schema: public; Owner: faga
--
ALTER TABLE inventory ALTER COLUMN inventory_id SET DEFAULT nextval('inventory_inventory_id_seq'::regclass);
--
-- Name: inventory_wine_id; Type: DEFAULT; Schema: public; Owner: faga
--
ALTER TABLE inventory_wine ALTER COLUMN inventory_wine_id SET DEFAULT nextval('inventory_wine_inventory_wine_id_seq'::regclass);
--
-- Name: review_id; Type: DEFAULT; Schema: public; Owner: faga
--
ALTER TABLE review ALTER COLUMN review_id SET DEFAULT nextval('review_review_id_seq'::regclass);
--
-- Name: review_wine_id; Type: DEFAULT; Schema: public; Owner: faga
--
ALTER TABLE review_wine ALTER COLUMN review_wine_id SET DEFAULT nextval('review_wine_review_wine_id_seq'::regclass);
--
-- Name: vineyard_id; Type: DEFAULT; Schema: public; Owner: faga
--
ALTER TABLE vineyard ALTER COLUMN vineyard_id SET DEFAULT nextval('vineyard_vineyard_id_seq'::regclass);
--
-- Name: vineyard_wine_id; Type: DEFAULT; Schema: public; Owner: faga
--
ALTER TABLE vineyard_wine ALTER COLUMN vineyard_wine_id SET DEFAULT nextval('vineyard_wine_vineyard_wine_id_seq'::regclass);
--
-- Name: wine_id; Type: DEFAULT; Schema: public; Owner: faga
--
ALTER TABLE wine ALTER COLUMN wine_id SET DEFAULT nextval('wine_wine_id_seq'::regclass);
--
-- Data for Name: inventory; Type: TABLE DATA; Schema: public; Owner: faga
--
COPY inventory (inventory_id, cases, lastorder, lastinvoice) FROM stdin;
\.
--
-- Data for Name: inventory_wine; Type: TABLE DATA; Schema: public; Owner: faga
--
COPY inventory_wine (inventory_wine_id, inventory_id, wine_id) FROM stdin;
\.
--
-- Data for Name: review; Type: TABLE DATA; Schema: public; Owner: faga
--
COPY review (review_id, stars, text, "location") FROM stdin;
\.
--
-- Data for Name: review_wine; Type: TABLE DATA; Schema: public; Owner: faga
--
COPY review_wine (review_wine_id, review_id, wine_id) FROM stdin;
\.
--
-- Data for Name: vineyard; Type: TABLE DATA; Schema: public; Owner: faga
--
COPY vineyard (vineyard_id, name, region, country) FROM stdin;
\.
--
-- Data for Name: vineyard_wine; Type: TABLE DATA; Schema: public; Owner: faga
--
COPY vineyard_wine (vineyard_wine_id, vineyard_id, wine_id) FROM stdin;
\.
--
-- Data for Name: wine; Type: TABLE DATA; Schema: public; Owner: faga
--
COPY wine (wine_id, name, "year", region, label, description) FROM stdin;
\.
--
-- Name: inventory_pkey; Type: CONSTRAINT; Schema: public; Owner: faga; Tablespace:
--
ALTER TABLE ONLY inventory
ADD CONSTRAINT inventory_pkey PRIMARY KEY (inventory_id);
--
-- Name: inventory_wine_const; Type: CONSTRAINT; Schema: public; Owner: faga; Tablespace:
--
ALTER TABLE ONLY inventory_wine
ADD CONSTRAINT inventory_wine_const UNIQUE (inventory_id, wine_id);
--
-- Name: inventory_wine_pkey; Type: CONSTRAINT; Schema: public; Owner: faga; Tablespace:
--
ALTER TABLE ONLY inventory_wine
ADD CONSTRAINT inventory_wine_pkey PRIMARY KEY (inventory_wine_id);
--
-- Name: review_pkey; Type: CONSTRAINT; Schema: public; Owner: faga; Tablespace:
--
ALTER TABLE ONLY review
ADD CONSTRAINT review_pkey PRIMARY KEY (review_id);
--
-- Name: review_wine_const; Type: CONSTRAINT; Schema: public; Owner: faga; Tablespace:
--
ALTER TABLE ONLY review_wine
ADD CONSTRAINT review_wine_const UNIQUE (review_id, wine_id);
--
-- Name: review_wine_pkey; Type: CONSTRAINT; Schema: public; Owner: faga; Tablespace:
--
ALTER TABLE ONLY review_wine
ADD CONSTRAINT review_wine_pkey PRIMARY KEY (review_wine_id);
--
-- Name: vineyard_pkey; Type: CONSTRAINT; Schema: public; Owner: faga; Tablespace:
--
ALTER TABLE ONLY vineyard
ADD CONSTRAINT vineyard_pkey PRIMARY KEY (vineyard_id);
--
-- Name: vineyard_wine_const; Type: CONSTRAINT; Schema: public; Owner: faga; Tablespace:
--
ALTER TABLE ONLY vineyard_wine
ADD CONSTRAINT vineyard_wine_const UNIQUE (vineyard_id, wine_id);
--
-- Name: vineyard_wine_pkey; Type: CONSTRAINT; Schema: public; Owner: faga; Tablespace:
--
ALTER TABLE ONLY vineyard_wine
ADD CONSTRAINT vineyard_wine_pkey PRIMARY KEY (vineyard_wine_id);
--
-- Name: wine_pkey; Type: CONSTRAINT; Schema: public; Owner: faga; Tablespace:
--
ALTER TABLE ONLY wine
ADD CONSTRAINT wine_pkey PRIMARY KEY (wine_id);
--
-- Name: inventory_wine_inventory_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: faga
--
ALTER TABLE ONLY inventory_wine
ADD CONSTRAINT inventory_wine_inventory_id_fkey FOREIGN KEY (inventory_id) REFERENCES inventory(inventory_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
--
-- Name: inventory_wine_wine_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: faga
--
ALTER TABLE ONLY inventory_wine
ADD CONSTRAINT inventory_wine_wine_id_fkey FOREIGN KEY (wine_id) REFERENCES wine(wine_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
--
-- Name: review_wine_review_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: faga
--
ALTER TABLE ONLY review_wine
ADD CONSTRAINT review_wine_review_id_fkey FOREIGN KEY (review_id) REFERENCES review(review_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
--
-- Name: review_wine_wine_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: faga
--
ALTER TABLE ONLY review_wine
ADD CONSTRAINT review_wine_wine_id_fkey FOREIGN KEY (wine_id) REFERENCES wine(wine_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
--
-- Name: vineyard_wine_vineyard_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: faga
--
ALTER TABLE ONLY vineyard_wine
ADD CONSTRAINT vineyard_wine_vineyard_id_fkey FOREIGN KEY (vineyard_id) REFERENCES vineyard(vineyard_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
--
-- Name: vineyard_wine_wine_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: faga
--
ALTER TABLE ONLY vineyard_wine
ADD CONSTRAINT vineyard_wine_wine_id_fkey FOREIGN KEY (wine_id) REFERENCES wine(wine_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
--
-- PostgreSQL database dump complete
--
-------------------------------------------------------------------------
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
