----------------------------------------------------- -- To HDOSE users: -- -- The following (Postgres) SQL commands should -- generate the database tables for HDOSE. -- -- Please check that -- * the database user name ('dbusername'), -- * the public server user ('postgres' by default), -- * the name of the database ('dbname'), -- correspond to your actual names. -- -- Some warning may appear. Just check that no errors -- have occurred and that the script successfully -- terminates. ----------------------------------------------------- -- -- PostgreSQL database dump -- SET client_encoding = 'UNICODE'; SET check_function_bodies = false; SET SESSION AUTHORIZATION 'dbusername'; -- -- TOC entry 2 (OID 0) -- Name: dbname; Type: DATABASE; Schema: -; Owner: dbusername -- --CREATE DATABASE dbname WITH TEMPLATE = template0 ENCODING = 'UNICODE'; SET client_encoding = 'UNICODE'; SET check_function_bodies = false; SET SESSION AUTHORIZATION 'postgres'; -- -- TOC entry 4 (OID 2200) -- Name: public; Type: ACL; Schema: -; Owner: postgres -- REVOKE ALL ON SCHEMA public FROM PUBLIC; GRANT ALL ON SCHEMA public TO PUBLIC; SET SESSION AUTHORIZATION 'dbusername'; SET search_path = public, pg_catalog; -- -- TOC entry 21 (OID 17152) -- Name: wsfragment; Type: TABLE; Schema: public; Owner: dbusername -- CREATE TABLE wsfragment ( idwsfragment integer DEFAULT nextval('wsfragment_idwsfragment_seq'::text) NOT NULL, uri character varying(2047) NOT NULL, xpath character varying(2047) NOT NULL ); -- -- TOC entry 5 (OID 17162) -- Name: wsauthor_idwsauthor_seq; Type: SEQUENCE; Schema: public; Owner: dbusername -- CREATE SEQUENCE wsauthor_idwsauthor_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; -- -- TOC entry 22 (OID 17164) -- Name: wsauthor; Type: TABLE; Schema: public; Owner: dbusername -- CREATE TABLE wsauthor ( idwsauthor integer DEFAULT nextval('wsauthor_idwsauthor_seq'::text) NOT NULL, name character varying(127) NOT NULL ); -- -- TOC entry 7 (OID 17171) -- Name: wsanntype_idwsanntype_seq; Type: SEQUENCE; Schema: public; Owner: dbusername -- CREATE SEQUENCE wsanntype_idwsanntype_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; -- -- TOC entry 23 (OID 17173) -- Name: wsanntype; Type: TABLE; Schema: public; Owner: dbusername -- CREATE TABLE wsanntype ( idwsanntype integer DEFAULT nextval('wsanntype_idwsanntype_seq'::text) NOT NULL, tname character varying(127) NOT NULL ); -- -- TOC entry 9 (OID 17180) -- Name: wstopic_idwstopic_seq; Type: SEQUENCE; Schema: public; Owner: dbusername -- CREATE SEQUENCE wstopic_idwstopic_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; -- -- TOC entry 24 (OID 17197) -- Name: wstopic; Type: TABLE; Schema: public; Owner: dbusername -- CREATE TABLE wstopic ( idwstopic integer DEFAULT nextval('wstopic_idwstopic_seq'::text) NOT NULL, tname character varying(127) NOT NULL, lang character(2) ); -- -- TOC entry 25 (OID 17204) -- Name: wsannotations; Type: TABLE; Schema: public; Owner: dbusername -- CREATE TABLE wsannotations ( idwsfragment integer NOT NULL, idwstopic integer NOT NULL, idwsauthor integer NOT NULL, idwstype integer NOT NULL, weight double precision, date timestamp without time zone ); -- -- TOC entry 26 (OID 17208) -- Name: wsviewannotations; Type: TABLE; Schema: public; Owner: dbusername -- CREATE TABLE wsviewannotations ( idwsfragment integer NOT NULL, idwstopic integer NOT NULL, idwsauthor integer NOT NULL, idwstype integer NOT NULL, weight double precision, date timestamp without time zone ); -- -- TOC entry 11 (OID 25787) -- Name: wsfragment_idwsfragment_seq; Type: SEQUENCE; Schema: public; Owner: dbusername -- CREATE SEQUENCE wsfragment_idwsfragment_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; -- -- TOC entry 13 (OID 25789) -- Name: wsannotations_idwsannotations_seq; Type: SEQUENCE; Schema: public; Owner: dbusername -- CREATE SEQUENCE wsannotations_idwsannotations_seq START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; -- -- TOC entry 15 (OID 25791) -- Name: wsviewannotations_idwsviewannotations_seq; Type: SEQUENCE; Schema: public; Owner: dbusername -- CREATE SEQUENCE wsviewannotations_idwsviewannotations_seq START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; -- -- TOC entry 17 (OID 40705) -- Name: wssynset_idsynset_seq; Type: SEQUENCE; Schema: public; Owner: dbusername -- CREATE SEQUENCE wssynset_idsynset_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; -- -- TOC entry 27 (OID 40707) -- Name: wssynset; Type: TABLE; Schema: public; Owner: dbusername -- CREATE TABLE wssynset ( idwssynset integer DEFAULT nextval('wssynset_idsynset_seq'::text) NOT NULL, idwstopic integer NOT NULL, lang character(2), content character varying(255) ); -- -- TOC entry 19 (OID 40718) -- Name: wsmword_idwsmword_seq; Type: SEQUENCE; Schema: public; Owner: dbusername -- CREATE SEQUENCE wsmword_idwsmword_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; -- -- TOC entry 28 (OID 40720) -- Name: wsmword; Type: TABLE; Schema: public; Owner: dbusername -- CREATE TABLE wsmword ( idwsmword integer DEFAULT nextval('wsmword_idwsmword_seq'::text) NOT NULL, content character varying(255) ); -- -- TOC entry 29 (OID 40725) -- Name: wsoccour; Type: TABLE; Schema: public; Owner: dbusername -- CREATE TABLE wsoccour ( idwssynset integer NOT NULL, idwsmword integer NOT NULL, occourence integer DEFAULT 0, weight double precision DEFAULT 0 ); -- -- Data for TOC entry 44 (OID 17152) -- Name: wsfragment; Type: TABLE DATA; Schema: public; Owner: dbusername -- -- -- Data for TOC entry 45 (OID 17164) -- Name: wsauthor; Type: TABLE DATA; Schema: public; Owner: dbusername -- -- -- Data for TOC entry 46 (OID 17173) -- Name: wsanntype; Type: TABLE DATA; Schema: public; Owner: dbusername -- -- -- Data for TOC entry 47 (OID 17197) -- Name: wstopic; Type: TABLE DATA; Schema: public; Owner: dbusername -- -- -- Data for TOC entry 48 (OID 17204) -- Name: wsannotations; Type: TABLE DATA; Schema: public; Owner: dbusername -- -- -- Data for TOC entry 50 (OID 40707) -- Name: wssynset; Type: TABLE DATA; Schema: public; Owner: dbusername -- -- -- Data for TOC entry 51 (OID 40720) -- Name: wsmword; Type: TABLE DATA; Schema: public; Owner: dbusername -- -- -- Data for TOC entry 52 (OID 40725) -- Name: wsoccour; Type: TABLE DATA; Schema: public; Owner: dbusername -- -- -- TOC entry 31 (OID 17158) -- Name: wsfragment_pkey; Type: CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wsfragment ADD CONSTRAINT wsfragment_pkey PRIMARY KEY (idwsfragment); -- -- TOC entry 30 (OID 17160) -- Name: wsfragment_chk; Type: CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wsfragment ADD CONSTRAINT wsfragment_chk UNIQUE (uri, xpath); -- -- TOC entry 33 (OID 17167) -- Name: wsauthor_pkey; Type: CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wsauthor ADD CONSTRAINT wsauthor_pkey PRIMARY KEY (idwsauthor); -- -- TOC entry 32 (OID 17169) -- Name: wsauthor_chk; Type: CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wsauthor ADD CONSTRAINT wsauthor_chk UNIQUE (name); -- -- TOC entry 35 (OID 17176) -- Name: wsanntype_pkey; Type: CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wsanntype ADD CONSTRAINT wsanntype_pkey PRIMARY KEY (idwsanntype); -- -- TOC entry 34 (OID 17178) -- Name: wsanntype_chk; Type: CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wsanntype ADD CONSTRAINT wsanntype_chk UNIQUE (tname); -- -- TOC entry 37 (OID 17200) -- Name: wstopic_pkey; Type: CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wstopic ADD CONSTRAINT wstopic_pkey PRIMARY KEY (idwstopic); -- -- TOC entry 36 (OID 17202) -- Name: wstopic_chk; Type: CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wstopic ADD CONSTRAINT wstopic_chk UNIQUE (tname); -- -- TOC entry 38 (OID 17206) -- Name: wsannotations_chk; Type: CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wsannotations ADD CONSTRAINT wsannotations_chk UNIQUE (idwsfragment, idwstopic, idwsauthor, idwstype); -- -- TOC entry 39 (OID 17210) -- Name: wsviewannotations_chk; Type: CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wsviewannotations ADD CONSTRAINT wsviewannotations_chk UNIQUE (idwsfragment, idwstopic, idwsauthor, idwstype); -- -- TOC entry 41 (OID 40710) -- Name: wssynset_pkey; Type: CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wssynset ADD CONSTRAINT wssynset_pkey PRIMARY KEY (idwssynset); -- -- TOC entry 40 (OID 40712) -- Name: wssynset_lock; Type: CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wssynset ADD CONSTRAINT wssynset_lock UNIQUE (idwstopic, lang); -- -- TOC entry 42 (OID 40723) -- Name: wsmword_pkey; Type: CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wsmword ADD CONSTRAINT wsmword_pkey PRIMARY KEY (idwsmword); -- -- TOC entry 43 (OID 40729) -- Name: wsoccour_pkey; Type: CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wsoccour ADD CONSTRAINT wsoccour_pkey PRIMARY KEY (idwssynset, idwsmword); -- -- TOC entry 53 (OID 17216) -- Name: wsfragment_ann_ref; Type: FK CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wsannotations ADD CONSTRAINT wsfragment_ann_ref FOREIGN KEY (idwsfragment) REFERENCES wsfragment(idwsfragment) ON DELETE CASCADE; -- -- TOC entry 54 (OID 17220) -- Name: wstopic_ann_ref; Type: FK CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wsannotations ADD CONSTRAINT wstopic_ann_ref FOREIGN KEY (idwstopic) REFERENCES wstopic(idwstopic) ON DELETE CASCADE; -- -- TOC entry 55 (OID 17224) -- Name: wsauthor_ann_ref; Type: FK CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wsannotations ADD CONSTRAINT wsauthor_ann_ref FOREIGN KEY (idwsauthor) REFERENCES wsauthor(idwsauthor) ON DELETE CASCADE; -- -- TOC entry 56 (OID 17228) -- Name: wstype_ann_ref; Type: FK CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wsannotations ADD CONSTRAINT wstype_ann_ref FOREIGN KEY (idwstype) REFERENCES wsanntype(idwsanntype) ON DELETE CASCADE; -- -- TOC entry 57 (OID 17232) -- Name: wsfragment_viewann_ref; Type: FK CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wsviewannotations ADD CONSTRAINT wsfragment_viewann_ref FOREIGN KEY (idwsfragment) REFERENCES wsfragment(idwsfragment) ON DELETE CASCADE; -- -- TOC entry 58 (OID 17236) -- Name: wstopic_viewann_ref; Type: FK CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wsviewannotations ADD CONSTRAINT wstopic_viewann_ref FOREIGN KEY (idwstopic) REFERENCES wstopic(idwstopic) ON DELETE CASCADE; -- -- TOC entry 59 (OID 17240) -- Name: wsauthor_viewann_ref; Type: FK CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wsviewannotations ADD CONSTRAINT wsauthor_viewann_ref FOREIGN KEY (idwsauthor) REFERENCES wsauthor(idwsauthor) ON DELETE CASCADE; -- -- TOC entry 60 (OID 17244) -- Name: wstype_viewann_ref; Type: FK CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wsviewannotations ADD CONSTRAINT wstype_viewann_ref FOREIGN KEY (idwstype) REFERENCES wsanntype(idwsanntype) ON DELETE CASCADE; -- -- TOC entry 61 (OID 40714) -- Name: wssynset_viewtopic_ref; Type: FK CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wssynset ADD CONSTRAINT wssynset_viewtopic_ref FOREIGN KEY (idwstopic) REFERENCES wstopic(idwstopic) ON DELETE CASCADE; -- -- TOC entry 62 (OID 40731) -- Name: wsoccour_idwssynset_ref; Type: FK CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wsoccour ADD CONSTRAINT wsoccour_idwssynset_ref FOREIGN KEY (idwssynset) REFERENCES wssynset(idwssynset) ON DELETE CASCADE; -- -- TOC entry 63 (OID 40735) -- Name: wsoccour_idwsmword_ref; Type: FK CONSTRAINT; Schema: public; Owner: dbusername -- ALTER TABLE ONLY wsoccour ADD CONSTRAINT wsoccour_idwsmword_ref FOREIGN KEY (idwsmword) REFERENCES wsmword(idwsmword) ON DELETE CASCADE; -- -- DATA -- INSERT INTO wsauthor (idwsauthor, name) VALUES (0, 'unknown'); INSERT INTO wsauthor (idwsauthor, name) VALUES (1, 'default'); INSERT INTO wsanntype (idwsanntype, tname) VALUES (0, 'unknown'); INSERT INTO wsanntype (idwsanntype, tname) VALUES (1, 'default'); INSERT INTO wsanntype (idwsanntype, tname) VALUES (2, 'simple'); -- -- TOC entry 6 (OID 17162) -- Name: wsauthor_idwsauthor_seq; Type: SEQUENCE SET; Schema: public; Owner: dbusername -- SELECT pg_catalog.setval('wsauthor_idwsauthor_seq', 2, true); -- -- TOC entry 8 (OID 17171) -- Name: wsanntype_idwsanntype_seq; Type: SEQUENCE SET; Schema: public; Owner:dbusername -- SELECT pg_catalog.setval('wsanntype_idwsanntype_seq', 2, true); -- -- TOC entry 10 (OID 17180) -- Name: wstopic_idwstopic_seq; Type: SEQUENCE SET; Schema: public; Owner: dbusername -- SELECT pg_catalog.setval('wstopic_idwstopic_seq', 2, true); -- -- TOC entry 12 (OID 25787) -- Name: wsfragment_idwsfragment_seq; Type: SEQUENCE SET; Schema: public; Owner: dbusername -- SELECT pg_catalog.setval('wsfragment_idwsfragment_seq', 2, true); -- -- TOC entry 14 (OID 25789) -- Name: wsannotations_idwsannotations_seq; Type: SEQUENCE SET; Schema: public; Owner: dbusername -- SELECT pg_catalog.setval('wsannotations_idwsannotations_seq', 1, false); -- -- TOC entry 16 (OID 25791) -- Name: wsviewannotations_idwsviewannotations_seq; Type: SEQUENCE SET; Schema: public; Owner: dbusername -- SELECT pg_catalog.setval('wsviewannotations_idwsviewannotations_seq', 1, false); -- -- TOC entry 18 (OID 40705) -- Name: wssynset_idsynset_seq; Type: SEQUENCE SET; Schema: public; Owner: dbusername -- SELECT pg_catalog.setval('wssynset_idsynset_seq', 2, true); -- -- TOC entry 20 (OID 40718) -- Name: wsmword_idwsmword_seq; Type: SEQUENCE SET; Schema: public; Owner: dbusername -- SELECT pg_catalog.setval('wsmword_idwsmword_seq', 2, true); SET SESSION AUTHORIZATION 'postgres'; -- -- TOC entry 3 (OID 2200) -- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres -- COMMENT ON SCHEMA public IS 'Standard public schema'; -- -- END OF SCRIPT ---------------------------------------