\connect - postgres

SET search_path = public, pg_catalog;

CREATE TABLE group_access (
	cat_cd	character varying(10) NOT NULL,
	subcat_cd	character varying(10) NOT NULL,
	group_cd	character varying(10) NOT NULL,
	writeable	boolean NOT NULL
);

REVOKE ALL ON TABLE group_access FROM PUBLIC;
GRANT ALL ON TABLE group_access TO nobody;

CREATE TABLE facility_category (
        fac_cat_cd character varying(10) CONSTRAINT facility_category_fac_cat_cd PRIMARY KEY,
        fac_cat_name character varying(100) CONSTRAINT facility_category_fac_cat_name NOT NULL,
        enable boolean CONSTRAINT facility_category_enable NOT NULL
);

REVOKE ALL ON facility_category FROM PUBLIC;
GRANT ALL ON facility_category TO nobody;

ALTER TABLE facilities ADD COLUMN fac_cat_cd character varying(10);
ALTER TABLE facilities ALTER COLUMN fac_cat_cd SET DEFAULT '1';
ALTER TABLE ONLY facilities
    ADD CONSTRAINT facilities_fac_cat_cd FOREIGN KEY (fac_cat_cd) REFERENCES facility_category(fac_cat_cd) ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE group_master ADD CONSTRAINT group_master_group_cd PRIMARY KEY(group_cd);
ALTER TABLE group_master ADD COLUMN public_flg boolean;
ALTER TABLE group_master ALTER COLUMN public_flg SET DEFAULT 'f';

COPY "facility_category" FROM stdin;
1	Default	t
\.
UPDATE facilities SET fac_cat_cd = '1';
UPDATE group_master SET public_flg = 'f';
