|
DROP TABLE IF EXISTS annotation CASCADE;
|
|
DROP TABLE IF EXISTS annotationmeta CASCADE;
|
|
DROP TABLE IF EXISTS concretevideo CASCADE;
|
|
DROP TABLE IF EXISTS containers CASCADE;
|
|
DROP TABLE IF EXISTS corpus CASCADE;
|
|
DROP TABLE IF EXISTS corpusmeta CASCADE;
|
|
DROP TABLE IF EXISTS format CASCADE;
|
|
DROP TABLE IF EXISTS metaconcerns CASCADE;
|
|
DROP TABLE IF EXISTS metadata CASCADE;
|
|
DROP TABLE IF EXISTS montage CASCADE;
|
|
DROP TABLE IF EXISTS montagemeta CASCADE;
|
|
DROP TABLE IF EXISTS resolution CASCADE;
|
|
DROP TABLE IF EXISTS rights CASCADE;
|
|
DROP TABLE IF EXISTS rule CASCADE;
|
|
DROP TABLE IF EXISTS role CASCADE;
|
|
DROP TABLE IF EXISTS session CASCADE;
|
|
DROP TABLE IF EXISTS sessionmeta CASCADE;
|
|
DROP TABLE IF EXISTS site CASCADE;
|
|
DROP TABLE IF EXISTS sitemeta CASCADE;
|
|
DROP TABLE IF EXISTS type CASCADE;
|
|
DROP TABLE IF EXISTS userannotationaccess CASCADE;
|
|
DROP TABLE IF EXISTS usercorpusaccess CASCADE;
|
|
DROP TABLE IF EXISTS usersessionaccess CASCADE;
|
|
DROP TABLE IF EXISTS usersiteaccess CASCADE;
|
|
DROP TABLE IF EXISTS uservideoaccess CASCADE;
|
|
DROP TABLE IF EXISTS userviewaccess CASCADE;
|
|
DROP TABLE IF EXISTS video CASCADE;
|
|
DROP TABLE IF EXISTS videometa CASCADE;
|
|
DROP TABLE IF EXISTS view CASCADE;
|
|
DROP TABLE IF EXISTS viewmeta CASCADE;
|
|
DROP TABLE IF EXISTS wanda_user CASCADE;
|
|
DROP TABLE IF EXISTS workflow CASCADE;
|
|
|
|
|
|
-- Table: workflow
|
|
-- DROP TABLE workflow;
|
|
CREATE TABLE workflow
|
|
(
|
|
idworkflow serial NOT NULL,
|
|
name character varying,
|
|
description character varying,
|
|
CONSTRAINT workflow_pkey PRIMARY KEY (idworkflow)
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE workflow
|
|
OWNER TO postgres;
|
|
|
|
-- Table: resolution
|
|
-- DROP TABLE resolution;
|
|
|
|
CREATE TABLE resolution
|
|
(
|
|
idresolution serial NOT NULL,
|
|
nom character varying,
|
|
description text,
|
|
CONSTRAINT resolution_pkey PRIMARY KEY (idresolution)
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE resolution
|
|
OWNER TO postgres;
|
|
|
|
|
|
-- Table: rights
|
|
-- DROP TABLE rights;
|
|
|
|
CREATE TABLE rights
|
|
(
|
|
idrights serial NOT NULL,
|
|
description character varying,
|
|
name character varying,
|
|
minauth integer,
|
|
CONSTRAINT rights_pkey PRIMARY KEY (idrights)
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE rights
|
|
OWNER TO postgres;
|
|
|
|
-- Table: role
|
|
-- DROP TABLE role;
|
|
|
|
CREATE TABLE role
|
|
(
|
|
idrole integer NOT NULL,
|
|
authlvl integer,
|
|
description text,
|
|
name character varying,
|
|
CONSTRAINT role_pkey PRIMARY KEY (idrole)
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE role
|
|
OWNER TO postgres;
|
|
|
|
|
|
-- Table: rule
|
|
-- DROP TABLE rule;
|
|
|
|
CREATE TABLE rule
|
|
(
|
|
idrule serial NOT NULL,
|
|
name character varying,
|
|
description character varying,
|
|
CONSTRAINT rule_pkey PRIMARY KEY (idrule)
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE rule
|
|
OWNER TO postgres;
|
|
|
|
-- Table: containers
|
|
-- DROP TABLE containers;
|
|
|
|
CREATE TABLE containers
|
|
(
|
|
table_name character varying NOT NULL,
|
|
CONSTRAINT "Containers_pkey" PRIMARY KEY (table_name)
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE containers
|
|
OWNER TO postgres;
|
|
|
|
-- Table: type
|
|
-- DROP TABLE type;
|
|
|
|
CREATE TABLE type
|
|
(
|
|
idtype serial NOT NULL,
|
|
name character varying,
|
|
description text,
|
|
CONSTRAINT type_pkey PRIMARY KEY (idtype)
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE type
|
|
OWNER TO postgres;
|
|
|
|
-- Table: format
|
|
-- DROP TABLE format;
|
|
|
|
CREATE TABLE format
|
|
(
|
|
idformat serial NOT NULL,
|
|
nom character varying,
|
|
extension character varying,
|
|
CONSTRAINT format_pkey PRIMARY KEY (idformat)
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE format
|
|
OWNER TO postgres;
|
|
|
|
-- Table: metadata
|
|
-- DROP TABLE metadata;
|
|
|
|
CREATE TABLE metadata
|
|
(
|
|
idmetadata serial NOT NULL,
|
|
name character varying,
|
|
description text,
|
|
hoover character varying,
|
|
obligation boolean,
|
|
CONSTRAINT metadata_pkey PRIMARY KEY (idmetadata)
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE metadata
|
|
OWNER TO postgres;
|
|
|
|
-- Table: site
|
|
-- DROP TABLE site;
|
|
|
|
CREATE TABLE site
|
|
(
|
|
idsite serial NOT NULL,
|
|
rule integer NOT NULL,
|
|
name character varying,
|
|
CONSTRAINT site_pkey PRIMARY KEY (idsite),
|
|
CONSTRAINT site_rule_fkey FOREIGN KEY (rule)
|
|
REFERENCES rule (idrule) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE site
|
|
OWNER TO postgres;
|
|
|
|
-- Index: sfk_rule
|
|
|
|
-- DROP INDEX sfk_rule;
|
|
|
|
CREATE INDEX sfk_rule
|
|
ON site
|
|
USING btree
|
|
(rule);
|
|
|
|
-- Table: wanda_user
|
|
-- DROP TABLE wanda_user;
|
|
|
|
CREATE TABLE wanda_user
|
|
(
|
|
certificate serial NOT NULL,
|
|
role integer NOT NULL,
|
|
name character varying,
|
|
forename character varying,
|
|
creator integer,
|
|
mail character varying,
|
|
CONSTRAINT wanda_user_pkey PRIMARY KEY (certificate),
|
|
CONSTRAINT wanda_user_creator_fkey FOREIGN KEY (creator)
|
|
REFERENCES wanda_user (certificate) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT wanda_user_role_fkey FOREIGN KEY (role)
|
|
REFERENCES role (idrole) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE wanda_user
|
|
OWNER TO postgres;
|
|
|
|
-- Index: wufk_role
|
|
|
|
-- DROP INDEX wufk_role;
|
|
|
|
CREATE INDEX wufk_role
|
|
ON wanda_user
|
|
USING btree
|
|
(role);
|
|
|
|
-- Table: corpus
|
|
-- DROP TABLE corpus;
|
|
|
|
CREATE TABLE corpus
|
|
(
|
|
idcorpus serial NOT NULL,
|
|
rule integer NOT NULL,
|
|
_site integer,
|
|
owner integer NOT NULL,
|
|
_corpus integer,
|
|
name character varying,
|
|
CONSTRAINT corpus_pkey PRIMARY KEY (idcorpus),
|
|
CONSTRAINT "CCFK" FOREIGN KEY (_corpus)
|
|
REFERENCES corpus (idcorpus) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT corpus_owner_fkey FOREIGN KEY (owner)
|
|
REFERENCES wanda_user (certificate) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT corpus_rule_fkey FOREIGN KEY (rule)
|
|
REFERENCES rule (idrule) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT corpus_site_fkey FOREIGN KEY (_site)
|
|
REFERENCES site (idsite) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE corpus
|
|
OWNER TO postgres;
|
|
|
|
-- Index: cfk_creator
|
|
|
|
-- DROP INDEX cfk_creator;
|
|
|
|
CREATE INDEX cfk_creator
|
|
ON corpus
|
|
USING btree
|
|
(owner);
|
|
|
|
-- Index: cfk_rule
|
|
|
|
-- DROP INDEX cfk_rule;
|
|
|
|
CREATE INDEX cfk_rule
|
|
ON corpus
|
|
USING btree
|
|
(rule);
|
|
|
|
-- Index: cfk_site
|
|
|
|
-- DROP INDEX cfk_site;
|
|
|
|
CREATE INDEX cfk_site
|
|
ON corpus
|
|
USING btree
|
|
(_site);
|
|
|
|
-- Index: "fki_CCFK"
|
|
|
|
-- DROP INDEX "fki_CCFK";
|
|
|
|
CREATE INDEX "fki_CCFK"
|
|
ON corpus
|
|
USING btree
|
|
(_corpus);
|
|
|
|
-- Table: session
|
|
-- DROP TABLE session;
|
|
|
|
CREATE TABLE session
|
|
(
|
|
idsession serial NOT NULL,
|
|
rule integer NOT NULL,
|
|
_corpus integer NOT NULL,
|
|
owner integer NOT NULL,
|
|
name character varying,
|
|
CONSTRAINT session_pkey PRIMARY KEY (idsession),
|
|
CONSTRAINT session_corpus_fkey FOREIGN KEY (_corpus)
|
|
REFERENCES corpus (idcorpus) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT session_owner_fkey FOREIGN KEY (owner)
|
|
REFERENCES wanda_user (certificate) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT session_rule_fkey FOREIGN KEY (rule)
|
|
REFERENCES rule (idrule) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE session
|
|
OWNER TO postgres;
|
|
|
|
-- Index: ssfk_corpus
|
|
|
|
-- DROP INDEX ssfk_corpus;
|
|
|
|
CREATE INDEX ssfk_corpus
|
|
ON session
|
|
USING btree
|
|
(_corpus);
|
|
|
|
-- Index: ssfk_creator
|
|
|
|
-- DROP INDEX ssfk_creator;
|
|
|
|
CREATE INDEX ssfk_creator
|
|
ON session
|
|
USING btree
|
|
(owner);
|
|
|
|
-- Index: ssfk_rule
|
|
|
|
-- DROP INDEX ssfk_rule;
|
|
|
|
CREATE INDEX ssfk_rule
|
|
ON session
|
|
USING btree
|
|
(rule);
|
|
|
|
-- Table: montage
|
|
-- DROP TABLE montage;
|
|
CREATE TABLE montage
|
|
(
|
|
idmontage serial NOT NULL,
|
|
owner integer NOT NULL,
|
|
_session integer,
|
|
link character varying,
|
|
CONSTRAINT montage_pkey PRIMARY KEY (idmontage),
|
|
CONSTRAINT montage_owner_fkey FOREIGN KEY (owner)
|
|
REFERENCES wanda_user (certificate) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE montage
|
|
OWNER TO postgres;
|
|
-- Index: mfk_owner
|
|
-- DROP INDEX mfk_owner;
|
|
CREATE INDEX mfk_owner
|
|
ON montage
|
|
USING btree
|
|
(owner);
|
|
|
|
|
|
-- Table: view
|
|
-- DROP TABLE view;
|
|
CREATE TABLE view
|
|
(
|
|
idview serial NOT NULL,
|
|
type integer NOT NULL,
|
|
rule integer NOT NULL,
|
|
owner integer NOT NULL,
|
|
_session integer NOT NULL,
|
|
_montage integer,
|
|
nom character varying,
|
|
CONSTRAINT view_pkey PRIMARY KEY (idview),
|
|
CONSTRAINT "VMFK" FOREIGN KEY (_montage)
|
|
REFERENCES montage (idmontage) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT view_rule_fkey FOREIGN KEY (rule)
|
|
REFERENCES rule (idrule) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT view_session_fkey FOREIGN KEY (_session)
|
|
REFERENCES session (idsession) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT view_type_fkey FOREIGN KEY (type)
|
|
REFERENCES type (idtype) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT view_wanda_user_fkey FOREIGN KEY (owner)
|
|
REFERENCES wanda_user (certificate) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE view
|
|
OWNER TO postgres;
|
|
|
|
-- Index: "fki_VMFK"
|
|
|
|
-- DROP INDEX "fki_VMFK";
|
|
|
|
CREATE INDEX "fki_VMFK"
|
|
ON view
|
|
USING btree
|
|
(_montage);
|
|
|
|
-- Index: vwfk_owner
|
|
|
|
-- DROP INDEX vwfk_owner;
|
|
|
|
CREATE INDEX vwfk_owner
|
|
ON view
|
|
USING btree
|
|
(owner);
|
|
|
|
-- Index: vwfk_rule
|
|
|
|
-- DROP INDEX vwfk_rule;
|
|
|
|
CREATE INDEX vwfk_rule
|
|
ON view
|
|
USING btree
|
|
(rule);
|
|
|
|
-- Index: vwfk_session
|
|
|
|
-- DROP INDEX vwfk_session;
|
|
|
|
CREATE INDEX vwfk_session
|
|
ON view
|
|
USING btree
|
|
(_session);
|
|
|
|
-- Index: vwfk_type
|
|
|
|
-- DROP INDEX vwfk_type;
|
|
|
|
CREATE INDEX vwfk_type
|
|
ON view
|
|
USING btree
|
|
(type);
|
|
|
|
|
|
-- Table: video
|
|
-- DROP TABLE video;
|
|
CREATE TABLE video
|
|
(
|
|
idvideo serial NOT NULL,
|
|
workflow integer NOT NULL,
|
|
_view integer NOT NULL,
|
|
owner integer NOT NULL,
|
|
nom character varying,
|
|
CONSTRAINT video_pkey PRIMARY KEY (idvideo),
|
|
CONSTRAINT video_view_fkey FOREIGN KEY (_view)
|
|
REFERENCES view (idview) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT video_wanda_user_fkey FOREIGN KEY (owner)
|
|
REFERENCES wanda_user (certificate) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT video_workflow_fkey FOREIGN KEY (workflow)
|
|
REFERENCES workflow (idworkflow) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE video
|
|
OWNER TO postgres;
|
|
|
|
-- Index: vfk_owner
|
|
|
|
-- DROP INDEX vfk_owner;
|
|
|
|
CREATE INDEX vfk_owner
|
|
ON video
|
|
USING btree
|
|
(owner);
|
|
|
|
-- Index: vfk_view
|
|
|
|
-- DROP INDEX vfk_view;
|
|
|
|
CREATE INDEX vfk_view
|
|
ON video
|
|
USING btree
|
|
(_view);
|
|
|
|
-- Index: vfk_workflow
|
|
|
|
-- DROP INDEX vfk_workflow;
|
|
|
|
CREATE INDEX vfk_workflow
|
|
ON video
|
|
USING btree
|
|
(workflow);
|
|
|
|
|
|
-- Table: annotation
|
|
-- DROP TABLE annotation;
|
|
|
|
CREATE TABLE annotation
|
|
(
|
|
idannotation serial NOT NULL,
|
|
link character varying NOT NULL,
|
|
workflow integer NOT NULL,
|
|
owner integer NOT NULL,
|
|
video integer,
|
|
_view integer,
|
|
name character varying,
|
|
CONSTRAINT annotation_pkey PRIMARY KEY (idannotation),
|
|
CONSTRAINT "AVFK" FOREIGN KEY (video)
|
|
REFERENCES video (idvideo) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT "AVWFK" FOREIGN KEY (_view)
|
|
REFERENCES view (idview) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT annotation_owner_fkey FOREIGN KEY (owner)
|
|
REFERENCES wanda_user (certificate) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT annotation_workflow_fkey FOREIGN KEY (workflow)
|
|
REFERENCES workflow (idworkflow) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE annotation
|
|
OWNER TO postgres;
|
|
|
|
-- Index: afk_owner
|
|
|
|
-- DROP INDEX afk_owner;
|
|
|
|
CREATE INDEX afk_owner
|
|
ON annotation
|
|
USING btree
|
|
(owner);
|
|
|
|
-- Index: afk_workflow
|
|
|
|
-- DROP INDEX afk_workflow;
|
|
|
|
CREATE INDEX afk_workflow
|
|
ON annotation
|
|
USING btree
|
|
(workflow);
|
|
|
|
-- Index: "fki_AVFK"
|
|
|
|
-- DROP INDEX "fki_AVFK";
|
|
|
|
CREATE INDEX "fki_AVFK"
|
|
ON annotation
|
|
USING btree
|
|
(video);
|
|
|
|
-- Index: "fki_AVWFK"
|
|
|
|
-- DROP INDEX "fki_AVWFK";
|
|
|
|
CREATE INDEX "fki_AVWFK"
|
|
ON annotation
|
|
USING btree
|
|
(_view);
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-- Table: annotationmeta
|
|
-- DROP TABLE annotationmeta;
|
|
|
|
CREATE TABLE annotationmeta
|
|
(
|
|
annotation integer NOT NULL,
|
|
metadata integer NOT NULL,
|
|
content text,
|
|
CONSTRAINT annotationmeta_pkey PRIMARY KEY (metadata, annotation),
|
|
CONSTRAINT annotationmeta_annotation_fkey FOREIGN KEY (annotation)
|
|
REFERENCES annotation (idannotation) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT annotationmeta_metadata_fkey FOREIGN KEY (metadata)
|
|
REFERENCES metadata (idmetadata) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE annotationmeta
|
|
OWNER TO postgres;
|
|
|
|
-- Table: concretevideo
|
|
|
|
-- DROP TABLE concretevideo;
|
|
|
|
CREATE TABLE concretevideo
|
|
(
|
|
video integer NOT NULL,
|
|
resolution integer NOT NULL,
|
|
format integer NOT NULL,
|
|
link character varying,
|
|
CONSTRAINT concretevideo_pkey PRIMARY KEY (video, resolution, format),
|
|
CONSTRAINT concretevideo_format_fkey FOREIGN KEY (format)
|
|
REFERENCES format (idformat) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT concretevideo_resolution_fkey FOREIGN KEY (resolution)
|
|
REFERENCES resolution (idresolution) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT concretevideo_video_fkey FOREIGN KEY (video)
|
|
REFERENCES video (idvideo) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE concretevideo
|
|
OWNER TO postgres;
|
|
|
|
-- Index: cvfk_format
|
|
|
|
-- DROP INDEX cvfk_format;
|
|
|
|
CREATE INDEX cvfk_format
|
|
ON concretevideo
|
|
USING btree
|
|
(format);
|
|
|
|
-- Index: cvfk_resolution
|
|
|
|
-- DROP INDEX cvfk_resolution;
|
|
|
|
CREATE INDEX cvfk_resolution
|
|
ON concretevideo
|
|
USING btree
|
|
(resolution);
|
|
|
|
-- Index: cvfk_video
|
|
|
|
-- DROP INDEX cvfk_video;
|
|
|
|
CREATE INDEX cvfk_video
|
|
ON concretevideo
|
|
USING btree
|
|
(video);
|
|
|
|
|
|
|
|
|
|
-- Table: corpusmeta
|
|
-- DROP TABLE corpusmeta;
|
|
|
|
CREATE TABLE corpusmeta
|
|
(
|
|
corpus integer NOT NULL,
|
|
metadata integer NOT NULL,
|
|
content text,
|
|
CONSTRAINT corpusmeta_pkey PRIMARY KEY (corpus, metadata),
|
|
CONSTRAINT corpusmeta_corpus_fkey FOREIGN KEY (corpus)
|
|
REFERENCES corpus (idcorpus) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT corpusmeta_metadata_fkey FOREIGN KEY (metadata)
|
|
REFERENCES metadata (idmetadata) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE corpusmeta
|
|
OWNER TO postgres;
|
|
|
|
-- Index: cmfk_corpus
|
|
|
|
-- DROP INDEX cmfk_corpus;
|
|
|
|
CREATE INDEX cmfk_corpus
|
|
ON corpusmeta
|
|
USING btree
|
|
(corpus);
|
|
|
|
-- Index: cmfk_meta
|
|
|
|
-- DROP INDEX cmfk_meta;
|
|
|
|
CREATE INDEX cmfk_meta
|
|
ON corpusmeta
|
|
USING btree
|
|
(metadata);
|
|
|
|
|
|
|
|
-- Table: metaconcerns
|
|
|
|
-- DROP TABLE metaconcerns;
|
|
|
|
CREATE TABLE metaconcerns
|
|
(
|
|
idmetadata serial NOT NULL,
|
|
concerns character varying NOT NULL,
|
|
CONSTRAINT metaconcerns_pkey PRIMARY KEY (idmetadata, concerns),
|
|
CONSTRAINT metaconcerns_idmetadata_fkey FOREIGN KEY (idmetadata)
|
|
REFERENCES metadata (idmetadata) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE metaconcerns
|
|
OWNER TO postgres;
|
|
|
|
|
|
|
|
-- Table: montagemeta
|
|
|
|
-- DROP TABLE montagemeta;
|
|
|
|
CREATE TABLE montagemeta
|
|
(
|
|
montage integer NOT NULL,
|
|
metadata integer NOT NULL,
|
|
content text,
|
|
CONSTRAINT montagemeta_pkey PRIMARY KEY (montage, metadata),
|
|
CONSTRAINT montagemeta_metadata_fkey FOREIGN KEY (metadata)
|
|
REFERENCES metadata (idmetadata) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT montagemeta_montage_fkey FOREIGN KEY (montage)
|
|
REFERENCES montage (idmontage) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE montagemeta
|
|
OWNER TO postgres;
|
|
|
|
-- Index: mmfk_metadata
|
|
|
|
-- DROP INDEX mmfk_metadata;
|
|
|
|
CREATE INDEX mmfk_metadata
|
|
ON montagemeta
|
|
USING btree
|
|
(metadata);
|
|
|
|
-- Index: mmfk_montage
|
|
|
|
-- DROP INDEX mmfk_montage;
|
|
|
|
CREATE INDEX mmfk_montage
|
|
ON montagemeta
|
|
USING btree
|
|
(montage);
|
|
|
|
|
|
|
|
|
|
|
|
-- Table: sessionmeta
|
|
|
|
-- DROP TABLE sessionmeta;
|
|
|
|
CREATE TABLE sessionmeta
|
|
(
|
|
session integer NOT NULL,
|
|
metadata integer NOT NULL,
|
|
content text,
|
|
CONSTRAINT sessionmeta_pkey PRIMARY KEY (session, metadata),
|
|
CONSTRAINT sessionmeta_metadata_fkey FOREIGN KEY (metadata)
|
|
REFERENCES metadata (idmetadata) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT sessionmeta_session_fkey FOREIGN KEY (session)
|
|
REFERENCES session (idsession) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE sessionmeta
|
|
OWNER TO postgres;
|
|
|
|
-- Index: ssmfk_meta
|
|
|
|
-- DROP INDEX ssmfk_meta;
|
|
|
|
CREATE INDEX ssmfk_meta
|
|
ON sessionmeta
|
|
USING btree
|
|
(metadata);
|
|
|
|
-- Index: ssmfk_session
|
|
|
|
-- DROP INDEX ssmfk_session;
|
|
|
|
CREATE INDEX ssmfk_session
|
|
ON sessionmeta
|
|
USING btree
|
|
(session);
|
|
|
|
|
|
|
|
-- Table: sitemeta
|
|
|
|
-- DROP TABLE sitemeta;
|
|
|
|
CREATE TABLE sitemeta
|
|
(
|
|
site integer NOT NULL,
|
|
metadata integer NOT NULL,
|
|
content text,
|
|
CONSTRAINT sitemeta_pkey PRIMARY KEY (site, metadata),
|
|
CONSTRAINT sitemeta_metadata_fkey FOREIGN KEY (metadata)
|
|
REFERENCES metadata (idmetadata) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT sitemeta_site_fkey FOREIGN KEY (site)
|
|
REFERENCES site (idsite) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE sitemeta
|
|
OWNER TO postgres;
|
|
|
|
-- Index: smfk_meta
|
|
|
|
-- DROP INDEX smfk_meta;
|
|
|
|
CREATE INDEX smfk_meta
|
|
ON sitemeta
|
|
USING btree
|
|
(metadata);
|
|
|
|
-- Index: smfk_site
|
|
|
|
-- DROP INDEX smfk_site;
|
|
|
|
CREATE INDEX smfk_site
|
|
ON sitemeta
|
|
USING btree
|
|
(site);
|
|
|
|
|
|
|
|
|
|
-- Table: userannotationaccess
|
|
|
|
-- DROP TABLE userannotationaccess;
|
|
|
|
CREATE TABLE userannotationaccess
|
|
(
|
|
wanda_user integer NOT NULL,
|
|
annotation integer NOT NULL,
|
|
rights integer NOT NULL,
|
|
lastacess date,
|
|
CONSTRAINT userannotationaccess_pkey PRIMARY KEY (wanda_user, annotation),
|
|
CONSTRAINT userannotationaccess_annotation_fkey FOREIGN KEY (annotation)
|
|
REFERENCES annotation (idannotation) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT userannotationaccess_rights_fkey FOREIGN KEY (rights)
|
|
REFERENCES rights (idrights) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT userannotationaccess_wanda_user_fkey FOREIGN KEY (wanda_user)
|
|
REFERENCES wanda_user (certificate) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE userannotationaccess
|
|
OWNER TO postgres;
|
|
|
|
-- Index: uaafk_annotation
|
|
|
|
-- DROP INDEX uaafk_annotation;
|
|
|
|
CREATE INDEX uaafk_annotation
|
|
ON userannotationaccess
|
|
USING btree
|
|
(annotation);
|
|
|
|
-- Index: uaafk_rights
|
|
|
|
-- DROP INDEX uaafk_rights;
|
|
|
|
CREATE INDEX uaafk_rights
|
|
ON userannotationaccess
|
|
USING btree
|
|
(rights);
|
|
|
|
-- Index: uaafk_wanda_user
|
|
|
|
-- DROP INDEX uaafk_wanda_user;
|
|
|
|
CREATE INDEX uaafk_wanda_user
|
|
ON userannotationaccess
|
|
USING btree
|
|
(wanda_user);
|
|
|
|
-- Table: usercorpusaccess
|
|
|
|
-- DROP TABLE usercorpusaccess;
|
|
|
|
CREATE TABLE usercorpusaccess
|
|
(
|
|
wanda_user integer NOT NULL,
|
|
corpus integer NOT NULL,
|
|
rights integer NOT NULL,
|
|
CONSTRAINT usercorpusaccess_pkey PRIMARY KEY (wanda_user, corpus),
|
|
CONSTRAINT usercorpusaccess_corpus_fkey FOREIGN KEY (corpus)
|
|
REFERENCES corpus (idcorpus) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT usercorpusaccess_rights_fkey FOREIGN KEY (rights)
|
|
REFERENCES rights (idrights) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT usercorpusaccess_wanda_user_fkey FOREIGN KEY (wanda_user)
|
|
REFERENCES wanda_user (certificate) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE usercorpusaccess
|
|
OWNER TO postgres;
|
|
|
|
-- Index: ucafk_corpus
|
|
|
|
-- DROP INDEX ucafk_corpus;
|
|
|
|
CREATE INDEX ucafk_corpus
|
|
ON usercorpusaccess
|
|
USING btree
|
|
(corpus);
|
|
|
|
-- Index: ucafk_rights
|
|
|
|
-- DROP INDEX ucafk_rights;
|
|
|
|
CREATE INDEX ucafk_rights
|
|
ON usercorpusaccess
|
|
USING btree
|
|
(rights);
|
|
|
|
-- Index: ucafk_wanda_user
|
|
|
|
-- DROP INDEX ucafk_wanda_user;
|
|
|
|
CREATE INDEX ucafk_wanda_user
|
|
ON usercorpusaccess
|
|
USING btree
|
|
(wanda_user);
|
|
|
|
-- Table: usersessionaccess
|
|
|
|
-- DROP TABLE usersessionaccess;
|
|
|
|
CREATE TABLE usersessionaccess
|
|
(
|
|
wanda_user integer NOT NULL,
|
|
session integer NOT NULL,
|
|
rights integer NOT NULL,
|
|
CONSTRAINT usersessionaccess_pkey PRIMARY KEY (wanda_user, session),
|
|
CONSTRAINT usersessionaccess_rights_fkey FOREIGN KEY (rights)
|
|
REFERENCES rights (idrights) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT usersessionaccess_session_fkey FOREIGN KEY (session)
|
|
REFERENCES session (idsession) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT usersessionaccess_wanda_user_fkey FOREIGN KEY (wanda_user)
|
|
REFERENCES wanda_user (certificate) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE usersessionaccess
|
|
OWNER TO postgres;
|
|
|
|
-- Index: ussafk_rights
|
|
|
|
-- DROP INDEX ussafk_rights;
|
|
|
|
CREATE INDEX ussafk_rights
|
|
ON usersessionaccess
|
|
USING btree
|
|
(rights);
|
|
|
|
-- Index: ussafk_session
|
|
|
|
-- DROP INDEX ussafk_session;
|
|
|
|
CREATE INDEX ussafk_session
|
|
ON usersessionaccess
|
|
USING btree
|
|
(session);
|
|
|
|
-- Index: ussafk_wanda_user
|
|
|
|
-- DROP INDEX ussafk_wanda_user;
|
|
|
|
CREATE INDEX ussafk_wanda_user
|
|
ON usersessionaccess
|
|
USING btree
|
|
(wanda_user);
|
|
|
|
-- Table: usersiteaccess
|
|
|
|
-- DROP TABLE usersiteaccess;
|
|
|
|
CREATE TABLE usersiteaccess
|
|
(
|
|
wanda_user integer NOT NULL,
|
|
site integer NOT NULL,
|
|
rights integer NOT NULL,
|
|
CONSTRAINT usersiteaccess_pkey PRIMARY KEY (wanda_user, site),
|
|
CONSTRAINT usersiteaccess_rights_fkey FOREIGN KEY (rights)
|
|
REFERENCES rights (idrights) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT usersiteaccess_site_fkey FOREIGN KEY (site)
|
|
REFERENCES site (idsite) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT usersiteaccess_wanda_user_fkey FOREIGN KEY (wanda_user)
|
|
REFERENCES wanda_user (certificate) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE usersiteaccess
|
|
OWNER TO postgres;
|
|
|
|
-- Index: usafk_rights
|
|
|
|
-- DROP INDEX usafk_rights;
|
|
|
|
CREATE INDEX usafk_rights
|
|
ON usersiteaccess
|
|
USING btree
|
|
(rights);
|
|
|
|
-- Index: usafk_site
|
|
|
|
-- DROP INDEX usafk_site;
|
|
|
|
CREATE INDEX usafk_site
|
|
ON usersiteaccess
|
|
USING btree
|
|
(site);
|
|
|
|
-- Index: usafk_wanda_user
|
|
|
|
-- DROP INDEX usafk_wanda_user;
|
|
|
|
CREATE INDEX usafk_wanda_user
|
|
ON usersiteaccess
|
|
USING btree
|
|
(wanda_user);
|
|
|
|
-- Table: uservideoaccess
|
|
|
|
-- DROP TABLE uservideoaccess;
|
|
|
|
CREATE TABLE uservideoaccess
|
|
(
|
|
wanda_user integer NOT NULL,
|
|
video integer NOT NULL,
|
|
rights integer NOT NULL,
|
|
lastaccess date,
|
|
CONSTRAINT uservideoaccess_pkey PRIMARY KEY (wanda_user, video),
|
|
CONSTRAINT uservideoaccess_rights_fkey FOREIGN KEY (rights)
|
|
REFERENCES rights (idrights) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT uservideoaccess_video_fkey FOREIGN KEY (video)
|
|
REFERENCES video (idvideo) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT uservideoaccess_wanda_user_fkey FOREIGN KEY (wanda_user)
|
|
REFERENCES wanda_user (certificate) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE uservideoaccess
|
|
OWNER TO postgres;
|
|
|
|
-- Index: uvafk_rights
|
|
|
|
-- DROP INDEX uvafk_rights;
|
|
|
|
CREATE INDEX uvafk_rights
|
|
ON uservideoaccess
|
|
USING btree
|
|
(rights);
|
|
|
|
-- Index: uvafk_video
|
|
|
|
-- DROP INDEX uvafk_video;
|
|
|
|
CREATE INDEX uvafk_video
|
|
ON uservideoaccess
|
|
USING btree
|
|
(video);
|
|
|
|
-- Index: uvafk_wanda_user
|
|
|
|
-- DROP INDEX uvafk_wanda_user;
|
|
|
|
CREATE INDEX uvafk_wanda_user
|
|
ON uservideoaccess
|
|
USING btree
|
|
(wanda_user);
|
|
|
|
-- Table: userviewaccess
|
|
|
|
-- DROP TABLE userviewaccess;
|
|
|
|
CREATE TABLE userviewaccess
|
|
(
|
|
wanda_user integer NOT NULL,
|
|
view integer NOT NULL,
|
|
rights integer NOT NULL,
|
|
CONSTRAINT userviewaccess_pkey PRIMARY KEY (wanda_user, view),
|
|
CONSTRAINT userviewaccess_rights_fkey FOREIGN KEY (rights)
|
|
REFERENCES rights (idrights) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT userviewaccess_view_fkey FOREIGN KEY (view)
|
|
REFERENCES view (idview) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT userviewaccess_wanda_user_fkey FOREIGN KEY (wanda_user)
|
|
REFERENCES wanda_user (certificate) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE userviewaccess
|
|
OWNER TO postgres;
|
|
|
|
-- Index: uvwafk_rights
|
|
|
|
-- DROP INDEX uvwafk_rights;
|
|
|
|
CREATE INDEX uvwafk_rights
|
|
ON userviewaccess
|
|
USING btree
|
|
(rights);
|
|
|
|
-- Index: uvwafk_view
|
|
|
|
-- DROP INDEX uvwafk_view;
|
|
|
|
CREATE INDEX uvwafk_view
|
|
ON userviewaccess
|
|
USING btree
|
|
(view);
|
|
|
|
-- Index: uvwafk_wanda_user
|
|
|
|
-- DROP INDEX uvwafk_wanda_user;
|
|
|
|
CREATE INDEX uvwafk_wanda_user
|
|
ON userviewaccess
|
|
USING btree
|
|
(wanda_user);
|
|
|
|
|
|
CREATE TABLE videometa
|
|
(
|
|
video integer NOT NULL,
|
|
metadata integer NOT NULL,
|
|
content text,
|
|
CONSTRAINT videometa_pkey PRIMARY KEY (metadata, video),
|
|
CONSTRAINT videometa_metadata_fkey FOREIGN KEY (metadata)
|
|
REFERENCES metadata (idmetadata) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT videometa_video_fkey FOREIGN KEY (video)
|
|
REFERENCES video (idvideo) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE videometa
|
|
OWNER TO postgres;
|
|
|
|
|
|
-- Table: viewmeta
|
|
-- DROP TABLE viewmeta;
|
|
|
|
CREATE TABLE viewmeta
|
|
(
|
|
view integer NOT NULL,
|
|
metadata integer NOT NULL,
|
|
content text,
|
|
CONSTRAINT viewmeta_pkey PRIMARY KEY (view, metadata),
|
|
CONSTRAINT viewmeta_metadata_fkey FOREIGN KEY (metadata)
|
|
REFERENCES metadata (idmetadata) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION,
|
|
CONSTRAINT viewmeta_view_fkey FOREIGN KEY (view)
|
|
REFERENCES view (idview) MATCH SIMPLE
|
|
ON UPDATE NO ACTION ON DELETE NO ACTION
|
|
)
|
|
WITH (
|
|
OIDS=FALSE
|
|
);
|
|
ALTER TABLE viewmeta
|
|
OWNER TO postgres;
|
|
|
|
-- Index: vwmfk_meta
|
|
|
|
-- DROP INDEX vwmfk_meta;
|
|
|
|
CREATE INDEX vwmfk_meta
|
|
ON viewmeta
|
|
USING btree
|
|
(metadata);
|
|
|
|
-- Index: vwmfk_view
|
|
|
|
-- DROP INDEX vwmfk_view;
|
|
|
|
CREATE INDEX vwmfk_view
|
|
ON viewmeta
|
|
USING btree
|
|
(view);
|
|
|
|
|