CRUGE

CREATE TABLE cruge_system (
  idsystem serial,
  name VARCHAR(45) NULL ,
  largename VARCHAR(45) NULL ,
  sessionmaxdurationmins INTEGER NULL DEFAULT 30 ,
  sessionmaxsameipconnections INTEGER NULL DEFAULT 10 ,
  sessionreusesessions INTEGER NULL DEFAULT 1,
  sessionmaxsessionsperday INTEGER NULL DEFAULT -1 ,
  sessionmaxsessionsperuser INTEGER NULL DEFAULT -1 ,
  systemnonewsessions INTEGER NULL DEFAULT 0,
  systemdown INTEGER NULL DEFAULT 0 ,
  registerusingcaptcha INTEGER NULL DEFAULT 0 ,
  registerusingterms INTEGER NULL DEFAULT 0 ,
  terms VARCHAR(4096) ,
  registerusingactivation INTEGER NULL DEFAULT 1 ,
  defaultroleforregistration VARCHAR(64) NULL ,
  registerusingtermslabel VARCHAR(100) NULL ,
  registrationonlogin INTEGER NULL DEFAULT 1 ,
  PRIMARY KEY (idsystem) )
;
DELETE FROM cruge_system;
INSERT INTO cruge_system (idsystem,name,largename,sessionmaxdurationmins,sessionmaxsameipconnections,sessionreusesessions,sessionmaxsessionsperday,sessionmaxsessionsperuser,systemnonewsessions,systemdown,registerusingcaptcha,registerusingterms,terms,registerusingactivation,defaultroleforregistration,registerusingtermslabel,registrationonlogin) VALUES
 (1,'default',NULL,30,10,1,-1,-1,0,0,0,0,'',0,'','',1);

CREATE TABLE cruge_session (
  idsession serial,
  iduser INT NOT NULL ,
  created BIGINT NULL ,
  expire BIGINT NULL ,
  STATUS INTEGER NULL DEFAULT 0 ,
  ipaddress VARCHAR(45) NULL ,
  usagecount INTEGER NULL DEFAULT 0 ,
  lastusage BIGINT NULL ,
  logoutdate BIGINT NULL ,
  ipaddressout VARCHAR(45) NULL ,
  PRIMARY KEY (idsession)
  )
;

CREATE  TABLE cruge_user (
  iduser  serial,
  personal_id          int4                  NULL,
  regdate BIGINT NULL ,
  actdate BIGINT NULL ,
  logondate BIGINT NULL ,
  username VARCHAR(64) NULL ,
  email VARCHAR(45) NULL ,
  password VARCHAR(64) NULL,
  authkey VARCHAR(100) NULL,
  state INTEGER NULL DEFAULT 0 ,
  totalsessioncounter INTEGER NULL DEFAULT 0 ,
  currentsessioncounter INTEGER NULL DEFAULT 0 ,
  PRIMARY KEY (iduser)
  )
;



DELETE FROM cruge_user;
INSERT INTO cruge_user(username, email, password, state) VALUES
 ('admin', 'admin@tucorreo.com','admin',1)
 ,('invitado', 'invitado','nopassword',1)
;


CREATE  TABLE cruge_field (
  idfield  serial,
  fieldname VARCHAR(20) NOT NULL ,
  longname VARCHAR(50) NULL ,
  POSITION INTEGER NULL DEFAULT 0 ,
  required INTEGER NULL DEFAULT 0 ,
  fieldtype INTEGER NULL DEFAULT 0 ,
  fieldsize INTEGER NULL DEFAULT 20 ,
  maxlength INTEGER NULL DEFAULT 45 ,
  showinreports INTEGER NULL DEFAULT 0 ,
  useregexp VARCHAR(512) NULL ,
  useregexpmsg VARCHAR(512) NULL ,
  predetvalue VARCHAR(4096),
  PRIMARY KEY (idfield)
  );

CREATE  TABLE cruge_fieldvalue (
  idfieldvalue  serial,
  iduser INT NOT NULL ,
  idfield INT NOT NULL ,
  VALUE VARCHAR(4096),
  PRIMARY KEY (idfieldvalue) ,
  CONSTRAINT fk_cruge_fieldvalue_cruge_user1
    FOREIGN KEY (iduser )
    REFERENCES cruge_user (iduser )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT fk_cruge_fieldvalue_cruge_field1
    FOREIGN KEY (idfield )
    REFERENCES cruge_field (idfield )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
 ;

CREATE TABLE cruge_authitem (
  name VARCHAR(64) NOT NULL ,
  TYPE INTEGER NOT NULL ,
  description TEXT NULL DEFAULT NULL ,
  bizrule TEXT NULL DEFAULT NULL ,
  DATA TEXT NULL DEFAULT NULL ,
  PRIMARY KEY (name) )
;

CREATE TABLE cruge_authassignment (
  userid INT NOT NULL ,
  bizrule TEXT NULL DEFAULT NULL ,
  DATA TEXT NULL DEFAULT NULL ,
  itemname VARCHAR(64) NOT NULL ,
  PRIMARY KEY (userid, itemname) ,
  CONSTRAINT fk_cruge_authassignment_cruge_authitem1
    FOREIGN KEY (itemname )
    REFERENCES cruge_authitem (name )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_cruge_authassignment_user
    FOREIGN KEY (userid )
    REFERENCES cruge_user (iduser )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
;


CREATE TABLE cruge_authitemchild (
  parent VARCHAR(64) NOT NULL ,
  child VARCHAR(64) NOT NULL ,
  PRIMARY KEY (parent, child) ,
  CONSTRAINT crugeauthitemchild_ibfk_1
    FOREIGN KEY (parent )
    REFERENCES cruge_authitem (name )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT crugeauthitemchild_ibfk_2
    FOREIGN KEY (child )
    REFERENCES cruge_authitem (name )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
;