--
-- Selected TOC Entries:
--
\connect - joan

--
-- TOC Entry ID 2 (OID 25960)
--
-- Name: persones_id_seq Type: SEQUENCE Owner: joan
--

CREATE SEQUENCE "persones_id_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 3 (OID 25960)
--
-- Name: persones_id_seq Type: ACL Owner: 
--

REVOKE ALL on "persones_id_seq" from PUBLIC;
GRANT ALL on "persones_id_seq" to "joan";
GRANT SELECT,UPDATE on "persones_id_seq" to "iaeste-lc";

--
-- TOC Entry ID 14 (OID 25962)
--
-- Name: persones Type: TABLE Owner: joan
--

CREATE TABLE "persones" (
	"id" integer DEFAULT nextval('"persones_id_seq"'::text) NOT NULL,
	"nom" character varying(50) NOT NULL,
	"email" character varying(50) NOT NULL,
	"tlf" character varying(20),
	"mob" integer,
	"ts" timestamp with time zone DEFAULT 'now',
	Constraint "persones_pkey" Primary Key ("id")
);

--
-- TOC Entry ID 15 (OID 25962)
--
-- Name: persones Type: ACL Owner: 
--

REVOKE ALL on "persones" from PUBLIC;
GRANT ALL on "persones" to "joan";
GRANT INSERT,SELECT,UPDATE on "persones" to "iaeste-lc";

--
-- TOC Entry ID 4 (OID 25965)
--
-- Name: centres_id_seq Type: SEQUENCE Owner: joan
--

CREATE SEQUENCE "centres_id_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 5 (OID 25965)
--
-- Name: centres_id_seq Type: ACL Owner: 
--

REVOKE ALL on "centres_id_seq" from PUBLIC;
GRANT ALL on "centres_id_seq" to "joan";
GRANT SELECT,UPDATE on "centres_id_seq" to "iaeste-lc";

--
-- TOC Entry ID 16 (OID 25967)
--
-- Name: centres Type: TABLE Owner: joan
--

CREATE TABLE "centres" (
	"id" integer DEFAULT nextval('"centres_id_seq"'::text) NOT NULL,
	"ref" character(4) NOT NULL,
	"nom" character varying(50) NOT NULL,
	"beca1" integer NOT NULL,
	"becan" integer NOT NULL,
	"tlf" integer NOT NULL,
	"fax" integer,
	"adr" text,
	"refdelegat" integer NOT NULL,
	"refprofessor" integer NOT NULL,
	"ts" timestamp with time zone DEFAULT 'now',
	Constraint "centres_pkey" Primary Key ("id")
);

--
-- TOC Entry ID 17 (OID 25967)
--
-- Name: centres Type: ACL Owner: 
--

REVOKE ALL on "centres" from PUBLIC;
GRANT ALL on "centres" to "joan";
GRANT INSERT,SELECT,UPDATE on "centres" to "iaeste-lc";

--
-- TOC Entry ID 6 (OID 25973)
--
-- Name: empreses_id_seq Type: SEQUENCE Owner: joan
--

CREATE SEQUENCE "empreses_id_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 7 (OID 25973)
--
-- Name: empreses_id_seq Type: ACL Owner: 
--

REVOKE ALL on "empreses_id_seq" from PUBLIC;
GRANT ALL on "empreses_id_seq" to "joan";
GRANT SELECT,UPDATE on "empreses_id_seq" to "iaeste-lc";

--
-- TOC Entry ID 18 (OID 25975)
--
-- Name: empreses Type: TABLE Owner: joan
--

CREATE TABLE "empreses" (
	"id" integer DEFAULT nextval('"empreses_id_seq"'::text) NOT NULL,
	"nom" character varying(50) NOT NULL,
	"adr" text NOT NULL,
	"fax" integer,
	"refcontacte" integer NOT NULL,
	"ts" timestamp with time zone DEFAULT 'now',
	Constraint "empreses_pkey" Primary Key ("id")
);

--
-- TOC Entry ID 19 (OID 25975)
--
-- Name: empreses Type: ACL Owner: 
--

REVOKE ALL on "empreses" from PUBLIC;
GRANT ALL on "empreses" to "joan";
GRANT INSERT,SELECT,UPDATE on "empreses" to "iaeste-lc";

--
-- TOC Entry ID 8 (OID 25981)
--
-- Name: fulls_grocs_id_seq Type: SEQUENCE Owner: joan
--

CREATE SEQUENCE "fulls_grocs_id_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 9 (OID 25981)
--
-- Name: fulls_grocs_id_seq Type: ACL Owner: 
--

REVOKE ALL on "fulls_grocs_id_seq" from PUBLIC;
GRANT ALL on "fulls_grocs_id_seq" to "joan";
GRANT SELECT,UPDATE on "fulls_grocs_id_seq" to "iaeste-lc";

--
-- TOC Entry ID 20 (OID 25983)
--
-- Name: fulls_grocs Type: TABLE Owner: joan
--

CREATE TABLE "fulls_grocs" (
	"id" integer DEFAULT nextval('"fulls_grocs_id_seq"'::text) NOT NULL,
	"ref" character(10) NOT NULL,
	"transport" text NOT NULL,
	"english" integer,
	"spanish" integer,
	"french" integer,
	"german" integer,
	"portuguese" integer,
	"nationality" text,
	"previous_experience" text,
	"kind_of_work" text,
	"length_of_period" interval NOT NULL,
	"gross_pay" numeric NOT NULL,
	"status" character(1) NOT NULL,
	"refempresa" integer NOT NULL,
	"refcentre" integer NOT NULL,
	"ts" timestamp with time zone DEFAULT 'now',
	Constraint "fulls_grocs_pkey" Primary Key ("id")
);

--
-- TOC Entry ID 21 (OID 25983)
--
-- Name: fulls_grocs Type: ACL Owner: 
--

REVOKE ALL on "fulls_grocs" from PUBLIC;
GRANT ALL on "fulls_grocs" to "joan";
GRANT INSERT,SELECT,UPDATE on "fulls_grocs" to "iaeste-lc";

--
-- TOC Entry ID 10 (OID 25989)
--
-- Name: allotjaments_id_seq Type: SEQUENCE Owner: joan
--

CREATE SEQUENCE "allotjaments_id_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 11 (OID 25989)
--
-- Name: allotjaments_id_seq Type: ACL Owner: 
--

REVOKE ALL on "allotjaments_id_seq" from PUBLIC;
GRANT ALL on "allotjaments_id_seq" to "joan";
GRANT SELECT,UPDATE on "allotjaments_id_seq" to "iaeste-lc";

--
-- TOC Entry ID 22 (OID 25991)
--
-- Name: allotjaments Type: TABLE Owner: joan
--

CREATE TABLE "allotjaments" (
	"id" integer DEFAULT nextval('"allotjaments_id_seq"'::text) NOT NULL,
	"adr" text NOT NULL,
	"preu" numeric NOT NULL,
	"entrada" date NOT NULL,
	"sortida" date NOT NULL,
	"refamfitrio" integer NOT NULL,
	"ts" timestamp with time zone DEFAULT 'now',
	Constraint "allotjaments_pkey" Primary Key ("id")
);

--
-- TOC Entry ID 23 (OID 25991)
--
-- Name: allotjaments Type: ACL Owner: 
--

REVOKE ALL on "allotjaments" from PUBLIC;
GRANT ALL on "allotjaments" to "joan";
GRANT INSERT,SELECT,UPDATE on "allotjaments" to "iaeste-lc";

--
-- TOC Entry ID 24 (OID 25999)
--
-- Name: vw_centres Type: VIEW Owner: joan
--

CREATE VIEW "vw_centres" as SELECT c.ref AS ref_centre, c.nom AS nom_centre, c.beca1, c.becan, c.tlf AS tlf_centre, c.fax AS fax_centre, c.adr AS adr_centre, c.ts, d.nom AS nom_delegat, d.email AS email_delegat, d.tlf AS tlf_delegat, d.mob AS mob_delegat, p.nom AS nom_professor, p.email AS email_professor, p.tlf AS tlf_professor, p.mob AS mob_professor FROM centres c, persones d, persones p WHERE ((c.refdelegat = d.id) AND (c.refprofessor = p.id));

--
-- TOC Entry ID 25 (OID 25999)
--
-- Name: vw_centres Type: ACL Owner: 
--

REVOKE ALL on "vw_centres" from PUBLIC;
GRANT ALL on "vw_centres" to "joan";
GRANT INSERT,SELECT,UPDATE on "vw_centres" to "iaeste-lc";

--
-- TOC Entry ID 12 (OID 26000)
--
-- Name: n_5_id_seq Type: SEQUENCE Owner: joan
--

CREATE SEQUENCE "n_5_id_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

--
-- TOC Entry ID 13 (OID 26000)
--
-- Name: n_5_id_seq Type: ACL Owner: 
--

REVOKE ALL on "n_5_id_seq" from PUBLIC;
GRANT ALL on "n_5_id_seq" to "joan";
GRANT SELECT,UPDATE on "n_5_id_seq" to "iaeste-lc";

--
-- TOC Entry ID 26 (OID 26002)
--
-- Name: n_5 Type: TABLE Owner: joan
--

CREATE TABLE "n_5" (
	"id" integer DEFAULT nextval('"n_5_id_seq"'::text) NOT NULL,
	"arribada" date NOT NULL,
	"reffulls_grocs" integer NOT NULL,
	"refallotjaments" integer,
	Constraint "n_5_pkey" Primary Key ("id")
);

--
-- TOC Entry ID 27 (OID 26002)
--
-- Name: n_5 Type: ACL Owner: 
--

REVOKE ALL on "n_5" from PUBLIC;
GRANT ALL on "n_5" to "joan";
GRANT INSERT,SELECT,UPDATE on "n_5" to "iaeste-lc";

--
-- TOC Entry ID 28 (OID 26005)
--
-- Name: "persones_nom_key" Type: INDEX Owner: joan
--

CREATE UNIQUE INDEX persones_nom_key ON persones USING btree (nom);

--
-- TOC Entry ID 29 (OID 26006)
--
-- Name: "centres_ref_key" Type: INDEX Owner: joan
--

CREATE UNIQUE INDEX centres_ref_key ON centres USING btree (ref);

--
-- TOC Entry ID 30 (OID 26007)
--
-- Name: "n_5_refallotjaments_key" Type: INDEX Owner: joan
--

CREATE UNIQUE INDEX n_5_refallotjaments_key ON n_5 USING btree (refallotjaments);

--
-- TOC Entry ID 39 (OID 26009)
--
-- Name: "RI_ConstraintTrigger_26008" Type: TRIGGER Owner: joan
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "centres"  FROM "persones" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'centres', 'persones', 'UNSPECIFIED', 'refdelegat', 'id');

--
-- TOC Entry ID 31 (OID 26011)
--
-- Name: "RI_ConstraintTrigger_26010" Type: TRIGGER Owner: joan
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "persones"  FROM "centres" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'centres', 'persones', 'UNSPECIFIED', 'refdelegat', 'id');

--
-- TOC Entry ID 32 (OID 26013)
--
-- Name: "RI_ConstraintTrigger_26012" Type: TRIGGER Owner: joan
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "persones"  FROM "centres" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'centres', 'persones', 'UNSPECIFIED', 'refdelegat', 'id');

--
-- TOC Entry ID 40 (OID 26015)
--
-- Name: "RI_ConstraintTrigger_26014" Type: TRIGGER Owner: joan
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "centres"  FROM "persones" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'centres', 'persones', 'UNSPECIFIED', 'refprofessor', 'id');

--
-- TOC Entry ID 33 (OID 26017)
--
-- Name: "RI_ConstraintTrigger_26016" Type: TRIGGER Owner: joan
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "persones"  FROM "centres" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'centres', 'persones', 'UNSPECIFIED', 'refprofessor', 'id');

--
-- TOC Entry ID 34 (OID 26019)
--
-- Name: "RI_ConstraintTrigger_26018" Type: TRIGGER Owner: joan
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "persones"  FROM "centres" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'centres', 'persones', 'UNSPECIFIED', 'refprofessor', 'id');

--
-- TOC Entry ID 43 (OID 26021)
--
-- Name: "RI_ConstraintTrigger_26020" Type: TRIGGER Owner: joan
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "empreses"  FROM "persones" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'empreses', 'persones', 'UNSPECIFIED', 'refcontacte', 'id');

--
-- TOC Entry ID 35 (OID 26023)
--
-- Name: "RI_ConstraintTrigger_26022" Type: TRIGGER Owner: joan
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "persones"  FROM "empreses" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'empreses', 'persones', 'UNSPECIFIED', 'refcontacte', 'id');

--
-- TOC Entry ID 36 (OID 26025)
--
-- Name: "RI_ConstraintTrigger_26024" Type: TRIGGER Owner: joan
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "persones"  FROM "empreses" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'empreses', 'persones', 'UNSPECIFIED', 'refcontacte', 'id');

--
-- TOC Entry ID 46 (OID 26027)
--
-- Name: "RI_ConstraintTrigger_26026" Type: TRIGGER Owner: joan
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "fulls_grocs"  FROM "empreses" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'fulls_grocs', 'empreses', 'UNSPECIFIED', 'refempresa', 'id');

--
-- TOC Entry ID 44 (OID 26029)
--
-- Name: "RI_ConstraintTrigger_26028" Type: TRIGGER Owner: joan
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "empreses"  FROM "fulls_grocs" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'fulls_grocs', 'empreses', 'UNSPECIFIED', 'refempresa', 'id');

--
-- TOC Entry ID 45 (OID 26031)
--
-- Name: "RI_ConstraintTrigger_26030" Type: TRIGGER Owner: joan
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "empreses"  FROM "fulls_grocs" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'fulls_grocs', 'empreses', 'UNSPECIFIED', 'refempresa', 'id');

--
-- TOC Entry ID 47 (OID 26033)
--
-- Name: "RI_ConstraintTrigger_26032" Type: TRIGGER Owner: joan
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "fulls_grocs"  FROM "centres" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'fulls_grocs', 'centres', 'UNSPECIFIED', 'refcentre', 'id');

--
-- TOC Entry ID 41 (OID 26035)
--
-- Name: "RI_ConstraintTrigger_26034" Type: TRIGGER Owner: joan
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "centres"  FROM "fulls_grocs" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'fulls_grocs', 'centres', 'UNSPECIFIED', 'refcentre', 'id');

--
-- TOC Entry ID 42 (OID 26037)
--
-- Name: "RI_ConstraintTrigger_26036" Type: TRIGGER Owner: joan
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "centres"  FROM "fulls_grocs" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'fulls_grocs', 'centres', 'UNSPECIFIED', 'refcentre', 'id');

--
-- TOC Entry ID 50 (OID 26039)
--
-- Name: "RI_ConstraintTrigger_26038" Type: TRIGGER Owner: joan
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "allotjaments"  FROM "persones" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'allotjaments', 'persones', 'UNSPECIFIED', 'refamfitrio', 'id');

--
-- TOC Entry ID 37 (OID 26041)
--
-- Name: "RI_ConstraintTrigger_26040" Type: TRIGGER Owner: joan
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "persones"  FROM "allotjaments" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'allotjaments', 'persones', 'UNSPECIFIED', 'refamfitrio', 'id');

--
-- TOC Entry ID 38 (OID 26043)
--
-- Name: "RI_ConstraintTrigger_26042" Type: TRIGGER Owner: joan
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "persones"  FROM "allotjaments" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'allotjaments', 'persones', 'UNSPECIFIED', 'refamfitrio', 'id');

--
-- TOC Entry ID 51 (OID 26045)
--
-- Name: "RI_ConstraintTrigger_26044" Type: TRIGGER Owner: joan
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "n_5"  FROM "fulls_grocs" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'n_5', 'fulls_grocs', 'UNSPECIFIED', 'reffulls_grocs', 'id');

--
-- TOC Entry ID 48 (OID 26047)
--
-- Name: "RI_ConstraintTrigger_26046" Type: TRIGGER Owner: joan
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "fulls_grocs"  FROM "n_5" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'n_5', 'fulls_grocs', 'UNSPECIFIED', 'reffulls_grocs', 'id');

--
-- TOC Entry ID 49 (OID 26049)
--
-- Name: "RI_ConstraintTrigger_26048" Type: TRIGGER Owner: joan
--

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "fulls_grocs"  FROM "n_5" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'n_5', 'fulls_grocs', 'UNSPECIFIED', 'reffulls_grocs', 'id');

--
-- TOC Entry ID 52 (OID 26050)
--
-- Name: vw_centres_upd Type: RULE Owner: joan
--

CREATE RULE vw_centres_upd AS ON UPDATE TO vw_centres DO INSTEAD (INSERT INTO persones (nom, email, tlf, mob) VALUES (new.nom_delegat, new.email_delegat, new.tlf_delegat, new.mob_delegat); INSERT INTO persones (nom, email, tlf, mob) VALUES (new.nom_professor, new.email_professor, new.tlf_professor, new.mob_professor); INSERT INTO centres (ref, nom, beca1, becan, tlf, fax, adr, refdelegat, refprofessor) VALUES (new.ref_centre, new.nom_centre, new.beca1, new.becan, new.tlf_centre, new.fax_centre, new.adr_centre, int4((currval('persones_id_seq'::text) - 1)), int4(currval('persones_id_seq'::text))); );
--
-- TOC Entry ID 53 (OID 26051)
--
-- Name: vw_centres_ins Type: RULE Owner: joan
--

CREATE RULE vw_centres_ins AS ON INSERT TO vw_centres DO INSTEAD (INSERT INTO persones (nom, email, tlf, mob) VALUES (new.nom_delegat, new.email_delegat, new.tlf_delegat, new.mob_delegat); INSERT INTO persones (nom, email, tlf, mob) VALUES (new.nom_professor, new.email_professor, new.tlf_professor, new.mob_professor); INSERT INTO centres (ref, nom, beca1, becan, tlf, fax, adr, refdelegat, refprofessor) VALUES (new.ref_centre, new.nom_centre, new.beca1, new.becan, new.tlf_centre, new.fax_centre, new.adr_centre, int4((currval('persones_id_seq'::text) - 1)), int4(currval('persones_id_seq'::text))); );
