SITools2

DROP TABLE IF EXISTS "sitools"."USER_GROUP";

DROP TABLE IF EXISTS "sitools"."USERS";

DROP TABLE IF EXISTS "sitools"."GROUPS";

DROP TABLE IF EXISTS "sitools"."USER_PROPERTIES";

DROP SCHEMA IF EXISTS "sitools";

CREATE SCHEMA "sitools";

CREATE TABLE IF NOT EXISTS "sitools"."USERS" (

  identifier varchar(64)  NOT NULL,

  firstname varchar(32)  DEFAULT NULL,

  lastname varchar(32)  DEFAULT NULL,

  email varchar(128)  DEFAULT NULL,

  secret varchar(40)  DEFAULT NULL,

  PRIMARY KEY (identifier)

);

INSERT INTO "sitools"."USERS" (identifier, firstname, lastname, email, secret) VALUES

('admin', 'firstname', 'lastname', 'admin-sitools@cnes.fr', 'admin');

CREATE TABLE IF NOT EXISTS "sitools"."GROUPS" (

  name varchar(32) NOT NULL,

  description varchar(128)  DEFAULT NULL,

  PRIMARY KEY (name)

);

--

-- Contenu de la table 'GROUPS'

--

INSERT INTO "sitools"."GROUPS" (name, description) VALUES

('register', 'Group of registered persons'),

('administrator', 'Group of persons managing the archive system');

CREATE TABLE IF NOT EXISTS "sitools"."USER_GROUP" (

  identifier varchar(64)  NOT NULL,

  name varchar(32)  NOT NULL,

  PRIMARY KEY (identifier,name),

  FOREIGN KEY (name) REFERENCES "sitools"."GROUPS" (name),

  FOREIGN KEY (identifier) REFERENCES "sitools"."USERS" (identifier)

);

--

-- Contenu de la table 'USER_GROUP'

--

INSERT INTO "sitools"."USER_GROUP" (identifier, name) VALUES

('admin', 'administrator');

CREATE TABLE IF NOT EXISTS "sitools"."USER_PROPERTIES" (

  key varchar(64)  NOT NULL,

  value varchar(256)  DEFAULT NULL,

  identifier varchar(64)  DEFAULT NULL,

  scope varchar(8)  DEFAULT NULL,

  PRIMARY KEY (key)

);

ALTER USER "sitools" SET PASSWORD "sitools";