• Home
• com.butor
• butor-auth-dao
• 1.0.0
• source code

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

or-auth-dao.1.0.0.source-code.butor-auth-db.sql Maven / Gradle / Ivy

DROP TABLE IF EXISTS secFunc;
CREATE  TABLE secFunc (
  func VARCHAR(250) NOT NULL,
  sys VARCHAR(25) NOT NULL,
  description VARCHAR(250) NULL,
  stamp TIMESTAMP NOT NULL,
  revNo INT NOT NULL,
  userId VARCHAR(250) NOT NULL,
  PRIMARY KEY (func,sys))
ENGINE = InnoDB;

DROP TABLE IF EXISTS secDesc;
CREATE  TABLE secDesc (
  id VARCHAR(250) NOT NULL,
  idType VARCHAR(25) NOT NULL,
  description VARCHAR(250) NULL,
  stamp TIMESTAMP NOT NULL,
  revNo INT NOT NULL,
  userId VARCHAR(250) NOT NULL,
  PRIMARY KEY (id,idType))
ENGINE = InnoDB;

DROP TABLE IF EXISTS secRole;
CREATE  TABLE secRole (
  roleId VARCHAR(250) NOT NULL,
  func VARCHAR(250) NOT NULL,
  sys VARCHAR(25) NOT NULL,
  stamp TIMESTAMP NOT NULL,
  revNo INT NOT NULL,
  userId VARCHAR(250) NOT NULL,
  PRIMARY KEY (roleId,func,sys))
ENGINE = InnoDB;

DROP TABLE IF EXISTS secGroup;
CREATE  TABLE secGroup (
  groupId VARCHAR(250) NOT NULL,
  member VARCHAR(250) NOT NULL,
  stamp TIMESTAMP NOT NULL,
  revNo INT NOT NULL,
  userId VARCHAR(250) NOT NULL,
  PRIMARY KEY (groupId,member))
ENGINE = InnoDB;
CREATE INDEX secGroup_member ON secGroup (member);

DROP TABLE IF EXISTS secData;
CREATE  TABLE secData (
  dataId INT NOT NULL AUTO_INCREMENT,
  sys VARCHAR(25) NOT NULL,
  dataType VARCHAR(50) NOT NULL,
  d1 VARCHAR(100) NOT NULL default '',
  d2 VARCHAR(100) NOT NULL default '',
  d3 VARCHAR(100) NOT NULL default '',
  d4 VARCHAR(100) NOT NULL default '',
  d5 VARCHAR(100) NOT NULL default '',
  stamp TIMESTAMP NOT NULL,
  revNo INT NOT NULL,
  userId VARCHAR(250) NOT NULL,
  PRIMARY KEY (dataId,sys,dataType,d1,d2,d3,d4,d5))
ENGINE = InnoDB;
CREATE INDEX XSysDataType ON secData (sys,dataType,d1,d2,d3,d4,d5);


DROP TABLE IF EXISTS secAuth;
CREATE  TABLE secAuth (
  authId INT NOT NULL AUTO_INCREMENT,
  who VARCHAR(250) NOT NULL,
  whoType VARCHAR(25) NOT NULL,
  what VARCHAR(250) NOT NULL,
  whatType VARCHAR(25) NOT NULL,
  sys VARCHAR(25) NOT NULL DEFAULT '',
  dataId INT NOT NULL,
  mode int NOT NULL,
  startDate DATETIME NULL,
  endDate DATETIME NULL,
  stamp TIMESTAMP NOT NULL,
  revNo INT NOT NULL,
  userId VARCHAR(250) NOT NULL,
  PRIMARY KEY (authId))
ENGINE = InnoDB;
ALTER TABLE secAuth
ADD UNIQUE INDEX i2 (who,whoType,what,whatType,sys,dataId,mode ASC);
ALTER TABLE secAuth
ADD INDEX i3 (dataId);

DROP TABLE IF EXISTS secDescHist;
CREATE  TABLE secDescHist (
  histId bigInt unsigned not null comment 'Historic Id' auto_increment ,
  histOperation varchar(12) not null comment 'Historic Operation',
  histStamp timestamp not null comment 'Historic Timestamp',
  histUserId varchar(250) not null comment 'Historic User Id',
  id VARCHAR(250) NOT NULL,
  idType VARCHAR(25) NOT NULL,
  description VARCHAR(250) NULL,
  stamp TIMESTAMP NOT NULL,
  revNo INT NOT NULL,
  userId VARCHAR(250) NOT NULL,
  PRIMARY KEY (histId))
ENGINE = InnoDB;
CREATE INDEX secDesc_role ON secDescHist (id,idType);

DROP TABLE IF EXISTS secRoleHist;
CREATE  TABLE secRoleHist (
  histId bigInt unsigned not null comment 'Historic Id' auto_increment ,
  histOperation varchar(12) not null comment 'Historic Operation',
  histStamp timestamp not null comment 'Historic Timestamp',
  histUserId varchar(250) not null comment 'Historic User Id',
  roleId VARCHAR(250) NOT NULL,
  func VARCHAR(250) NOT NULL,
  sys VARCHAR(25) NOT NULL,
  stamp TIMESTAMP NOT NULL,
  revNo INT NOT NULL,
  userId VARCHAR(250) NOT NULL,
  PRIMARY KEY (histId))
ENGINE = InnoDB;
CREATE INDEX secRole_role ON secRoleHist (roleId,func,sys);

DROP TABLE IF EXISTS secGroupHist;
CREATE  TABLE secGroupHist (
  histId bigInt unsigned not null comment 'Historic Id' auto_increment ,
  histOperation varchar(12) not null comment 'Historic Operation',
  histStamp timestamp not null comment 'Historic Timestamp',
  histUserId varchar(250) not null comment 'Historic User Id',
  groupId VARCHAR(250) NOT NULL,
  member VARCHAR(250) NOT NULL,
  stamp TIMESTAMP NOT NULL,
  revNo INT NOT NULL,
  userId VARCHAR(250) NOT NULL,
  PRIMARY KEY (histId))
ENGINE = InnoDB;
CREATE INDEX secGroup_group ON secGroupHist (groupId,member);
CREATE INDEX secGroup_member ON secGroupHist (member);

DROP TABLE IF EXISTS secDataHist;
CREATE  TABLE secDataHist (
  histId bigInt unsigned not null comment 'Historic Id' auto_increment ,
  histOperation varchar(12) not null comment 'Historic Operation',
  histStamp timestamp not null comment 'Historic Timestamp',
  histUserId varchar(250) not null comment 'Historic User Id',
  dataId INT NOT NULL,
  sys VARCHAR(25) NOT NULL,
  dataType VARCHAR(50) NOT NULL,
  d1 VARCHAR(100) NOT NULL default '',
  d2 VARCHAR(100) NOT NULL default '',
  d3 VARCHAR(100) NOT NULL default '',
  d4 VARCHAR(100) NOT NULL default '',
  d5 VARCHAR(100) NOT NULL default '',
  stamp TIMESTAMP NOT NULL,
  revNo INT NOT NULL,
  userId VARCHAR(250) NOT NULL,
  PRIMARY KEY (histId))
ENGINE = InnoDB;
CREATE INDEX secData_data ON secDataHist (dataId,sys,dataType,d1,d2,d3,d4,d5);
CREATE INDEX XSysDataType ON secDataHist (sys,dataType,d1,d2,d3,d4,d5);

DROP TABLE IF EXISTS secAuthHist;
CREATE  TABLE secAuthHist (
  histId bigInt unsigned not null comment 'Historic Id' auto_increment ,
  histOperation varchar(12) not null comment 'Historic Operation',
  histStamp timestamp not null comment 'Historic Timestamp',
  histUserId varchar(250) not null comment 'Historic User Id',
  authId INT NOT NULL,
  who VARCHAR(250) NOT NULL,
  whoType VARCHAR(25) NOT NULL,
  what VARCHAR(250) NOT NULL,
  whatType VARCHAR(25) NOT NULL,
  sys VARCHAR(25) NOT NULL DEFAULT '',
  dataId INT NOT NULL,
  mode int NOT NULL,
  startDate DATETIME NULL,
  endDate DATETIME NULL,
  stamp TIMESTAMP NOT NULL,
  revNo INT NOT NULL,
  userId VARCHAR(250) NOT NULL,
  PRIMARY KEY (histId))
ENGINE = InnoDB;
CREATE INDEX secAuth_authId ON secAuthHist (authId);
CREATE INDEX secAuth_i2 ON secAuthHist (who,whoType,what,whatType,sys,dataId,mode);


DROP TABLE IF EXISTS secUser;
CREATE  TABLE secUser (
  id VARCHAR(128) NOT NULL,
  email VARCHAR(256) NOT NULL,
  firstName VARCHAR(128) NOT NULL,
  lastName VARCHAR(128) NOT NULL,
  displayName VARCHAR(256) NOT NULL,
  fullName VARCHAR(256) NOT NULL,
  phone VARCHAR(50) NULL,
  firmId INT NOT NULL,
  pwd VARCHAR(256) NULL,
  q1 VARCHAR(256) NULL,
  r1 VARCHAR(256) NULL,
  q2 VARCHAR(256) NULL,
  r2 VARCHAR(256) NULL,
  q3 VARCHAR(256) NULL,
  r3 VARCHAR(256) NULL,
  avatar VARCHAR(128) NULL,
  lastQ INT NULL,
  resetInProgress BOOLEAN NOT NULL,
  missedLogin INT NOT NULL,
  active BOOLEAN NOT NULL,
  attributes TEXT,
  creationDate DATETIME NOT NULL,
  lastLoginDate DATETIME NULL,
  stamp TIMESTAMP NOT NULL,
  revNo INT NOT NULL,
  userId VARCHAR(128) NOT NULL,
  PRIMARY KEY (id))
ENGINE = InnoDB;
ALTER TABLE secUser 
ADD INDEX email (email ASC),
ADD INDEX fie (firmId, id ASC),
ADD INDEX dn (displayName ASC);

DROP TABLE IF EXISTS secUserHist;
CREATE  TABLE secUserHist (
  histId bigInt unsigned not null comment 'Historic Id' auto_increment ,
  histOperation varchar(12) not null comment 'Historic Operation',
  histStamp timestamp not null comment 'Historic Timestamp',
  histUserId varchar(250) not null comment 'Historic User Id',
  id VARCHAR(128) NOT NULL,
  email VARCHAR(256) NOT NULL,
  firstName VARCHAR(128) NOT NULL,
  lastName VARCHAR(128) NOT NULL,
  displayName VARCHAR(256) NOT NULL,
  fullName VARCHAR(256) NOT NULL,
  phone VARCHAR(50) NULL,
  firmId INT NOT NULL,
  pwd VARCHAR(256) NULL,
  q1 VARCHAR(256) NULL,
  r1 VARCHAR(256) NULL,
  q2 VARCHAR(256) NULL,
  r2 VARCHAR(256) NULL,
  q3 VARCHAR(256) NULL,
  r3 VARCHAR(256) NULL,
  avatar VARCHAR(128) NULL,
  lastQ INT NULL,
  resetInProgress BOOLEAN NOT NULL,
  missedLogin INT NOT NULL,
  active BOOLEAN NOT NULL,
  attributes TEXT,
  creationDate DATETIME NOT NULL,
  lastLoginDate DATETIME NULL,
  stamp TIMESTAMP NOT NULL,
  revNo INT NOT NULL,
  userId VARCHAR(128) NOT NULL,
  PRIMARY KEY (histId))
ENGINE = InnoDB;
ALTER TABLE secUserHist 
ADD INDEX id (id ASC),
ADD INDEX email (email ASC),
ADD INDEX fie (firmId, id ASC),
ADD INDEX dn (displayName ASC);


DROP TABLE IF EXISTS secFirm;
CREATE  TABLE secFirm (
  firmId INT NOT NULL AUTO_INCREMENT,
  firmName VARCHAR(250) NOT NULL,
  contactName VARCHAR(250) NOT NULL,
  contactPhone VARCHAR(50) NOT NULL,
  active BOOLEAN NOT NULL,
  attributes TEXT NOT NULL,
  theme VARCHAR(50) NOT NULL,
  creationDate DATETIME NOT NULL,
  stamp TIMESTAMP NOT NULL,
  revNo INT NOT NULL,
  userId VARCHAR(250) NOT NULL,
  PRIMARY KEY (firmId),UNIQUE INDEX (firmName ASC))
ENGINE = InnoDB;

DROP TABLE IF EXISTS secFirmHist;
CREATE TABLE secFirmHist (
  histId bigInt unsigned not null comment 'Historic Id' auto_increment ,
  histOperation varchar(12) not null comment 'Historic Operation',
  histStamp timestamp not null comment 'Historic Timestamp',
  histUserId varchar(250) not null comment 'Historic User Id',
  firmId INT NOT NULL,
  firmName VARCHAR(250) NOT NULL,
  contactName VARCHAR(250) NOT NULL,
  contactPhone VARCHAR(50) NOT NULL,
  active BOOLEAN NOT NULL,
  attributes TEXT NOT NULL,
  theme VARCHAR(50) NOT NULL,
  creationDate DATETIME NOT NULL,
  stamp TIMESTAMP NOT NULL,
  revNo INT NOT NULL,
  userId VARCHAR(250) NOT NULL,

PRIMARY KEY (histId),
INDEX (firmId),
INDEX (firmName)
)
ENGINE = InnoDB;