geofence db structure

geofence.sql

BEGIN;

CREATE SCHEMA geofence;

CREATE TABLE geofence.gf_gfuser(
    id bigint NOT NULL,
    datecreation timestamp without time zone,
    emailaddress character varying(255),
    enabled boolean NOT NULL,
    extid character varying(255),
    fullname character varying(255),
    name character varying(255) NOT NULL,
    password character varying(255),
		CONSTRAINT gf_gfuser_pkey PRIMARY KEY (id),
    CONSTRAINT gf_gfuser_extid_key UNIQUE (extid),
    CONSTRAINT gf_gfuser_name_key UNIQUE (name)
);

CREATE TABLE geofence.gf_gsinstance(
    id bigint NOT NULL,
    baseurl character varying(255) NOT NULL,
    datecreation timestamp without time zone,
    description character varying(255),
    name character varying(255) NOT NULL,
    password character varying(255) NOT NULL,
    username character varying(255) NOT NULL,
		CONSTRAINT gf_gsinstance_pkey PRIMARY KEY (id)
);

CREATE TABLE geofence.gf_gsuser(
  id bigint NOT NULL,
  admin boolean NOT NULL,
  datecreation timestamp without time zone,
  emailaddress character varying(255),
  enabled boolean NOT NULL,
  extid character varying(255),
  fullname character varying(255),
  name character varying(255) NOT NULL,
  password character varying(255),
  CONSTRAINT gf_gsuser_pkey PRIMARY KEY (id),
  CONSTRAINT gf_gsuser_extid_key UNIQUE (extid),
  CONSTRAINT gf_gsuser_name_key UNIQUE (name)
);


CREATE TABLE geofence.gf_usergroup(
  id bigint NOT NULL,
  datecreation timestamp without time zone,
  enabled boolean NOT NULL,
  extid character varying(255),
  name character varying(255) NOT NULL,
  CONSTRAINT gf_usergroup_pkey PRIMARY KEY (id),
  CONSTRAINT gf_usergroup_extid_key UNIQUE (extid),
  CONSTRAINT gf_usergroup_name_key UNIQUE (name)
);

CREATE TABLE geofence.gf_rule(
  id bigint NOT NULL,
  grant_type character varying(255) NOT NULL,
  layer character varying(255),
  priority bigint NOT NULL,
  request character varying(255),
  service character varying(255),
  workspace character varying(255),
  gsuser_id bigint,
  instance_id bigint,
  usergroup_id bigint,
  ip_high bigint,
  ip_low bigint,
  ip_size integer,
  rolename character varying(255),
  username character varying(255),
  CONSTRAINT gf_rule_pkey PRIMARY KEY (id),
  CONSTRAINT fk_rule_instance FOREIGN KEY (instance_id)
      REFERENCES geofence.gf_gsinstance (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_rule_user FOREIGN KEY (gsuser_id)
      REFERENCES geofence.gf_gsuser (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_rule_usergroup FOREIGN KEY (usergroup_id)
      REFERENCES geofence.gf_usergroup (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT gf_rule_gsuser_id_usergroup_id_instance_id_service_request__key UNIQUE (gsuser_id, usergroup_id, instance_id, service, request, workspace, layer)
);

CREATE TABLE geofence.gf_layer_details(
  id bigint NOT NULL,
  area geometry,
  cqlfilterread character varying(4000),
  cqlfilterwrite character varying(4000),
  defaultstyle character varying(255),
  areametadatafield character varying(255),
  type character varying(255),
  rule_id bigint NOT NULL,
  catalog_mode character varying(255),
  CONSTRAINT gf_layer_details_pkey PRIMARY KEY (id),
  CONSTRAINT fk_details_rule FOREIGN KEY (rule_id)
      REFERENCES geofence.gf_rule (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT gf_layer_details_rule_id_key UNIQUE (rule_id)
);



CREATE TABLE geofence.gf_layer_attributes(
  details_id bigint NOT NULL,
  access_type character varying(255),
  data_type character varying(255),
  name character varying(255) NOT NULL,
  CONSTRAINT gf_layer_attributes_pkey PRIMARY KEY (details_id, name),
  CONSTRAINT fk_attribute_layer FOREIGN KEY (details_id)
      REFERENCES geofence.gf_layer_details (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);


CREATE TABLE geofence.gf_layer_custom_props(
  details_id bigint NOT NULL,
  propvalue character varying(255),
  propkey character varying(255) NOT NULL,
  CONSTRAINT gf_layer_custom_props_pkey PRIMARY KEY (details_id, propkey),
  CONSTRAINT fk_custom_layer FOREIGN KEY (details_id)
      REFERENCES geofence.gf_layer_details (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE geofence.gf_layer_styles(
  details_id bigint NOT NULL,
  stylename character varying(255),
  CONSTRAINT fk_styles_layer FOREIGN KEY (details_id)
      REFERENCES geofence.gf_layer_details (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE geofence.gf_rule_limits(
  id bigint NOT NULL,
  area geometry,
  rule_id bigint NOT NULL,
  catalog_mode character varying(255),
  CONSTRAINT gf_rule_limits_pkey PRIMARY KEY (id),
  CONSTRAINT fk_limits_rule FOREIGN KEY (rule_id)
      REFERENCES geofence.gf_rule (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT gf_rule_limits_rule_id_key UNIQUE (rule_id)
);

CREATE TABLE geofence.gf_user_usergroups(
  user_id bigint NOT NULL,
  group_id bigint NOT NULL,
  CONSTRAINT gf_user_usergroups_pkey PRIMARY KEY (user_id, group_id),
  CONSTRAINT fk_uug_group FOREIGN KEY (group_id)
      REFERENCES geofence.gf_usergroup (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_uug_user FOREIGN KEY (user_id)
      REFERENCES geofence.gf_gsuser (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE geofence.gf_adminrule(
  id bigint NOT NULL,
  grant_type character varying(255) NOT NULL,
  ip_high bigint,
  ip_low bigint,
  ip_size integer,
  priority bigint NOT NULL,
  rolename character varying(255),
  username character varying(255),
  workspace character varying(255),
  instance_id bigint,
  CONSTRAINT gf_adminrule_pkey PRIMARY KEY (id),
  CONSTRAINT fk_adminrule_instance FOREIGN KEY (instance_id)
      REFERENCES geofence.gf_gsinstance (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT gf_adminrule_username_rolename_instance_id_workspace_key UNIQUE (username, rolename, instance_id, workspace)
);

CREATE SEQUENCE geofence.hibernate_sequence;

COMMIT;