CREATE DATABASE IF NOT EXISTS `audit` CHARACTER SET utf8 COLLATE utf8_bin;

USE audit;

-- -----------------------------------------------------

-- Table `auditaccess`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `auditaccess` (

`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,

`objectid` VARCHAR(56) NOT NULL ,

`activitydate` VARCHAR(29) NOT NULL COMMENT 'Date format: 2011-09-09T14:58:17.654+02:00' ,

`eventname` VARCHAR(255) ,

`transactionid` VARCHAR(255) NOT NULL ,

`userid` VARCHAR(255) ,

`trackingids` TEXT,

`server_ip` VARCHAR(40) ,

`server_port` VARCHAR(5) ,

`client_ip` VARCHAR(40) ,

`client_port` VARCHAR(5) ,

`request_protocol` VARCHAR(255) NULL ,

`request_operation` VARCHAR(255) NULL ,

`request_detail` TEXT NULL ,

`http_request_secure` VARCHAR(255) NULL ,

`http_request_method` VARCHAR(255) NULL ,

`http_request_path` VARCHAR(255) NULL ,

`http_request_queryparameters` TEXT NULL ,

`http_request_headers` TEXT NULL ,

`http_request_cookies` TEXT NULL ,

`http_response_headers` TEXT NULL ,

`response_status` VARCHAR(255) NULL ,

`response_statuscode` VARCHAR(255) NULL ,

`response_elapsedtime` VARCHAR(255) NULL ,

`response_elapsedtimeunits` VARCHAR(255) NULL ,

`roles` TEXT NULL ,

INDEX `idx_auditrecon_objectid` (`objectid` ASC),

INDEX `idx_auditrecon_activitydate` (`activitydate` ASC),

INDEX `idx_auditaccess_status` (`response_status` ASC) )

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `auditauthentication`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `auditauthentication` (

`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,

`objectid` VARCHAR(56) NOT NULL ,

`transactionid` VARCHAR(255) NOT NULL ,

`activitydate` VARCHAR(29) NOT NULL COMMENT 'Date format: 2011-09-09T14:58:17.654+02:00' ,

`userid` VARCHAR(255) NULL ,

`eventname` VARCHAR(50) NULL ,

`result` VARCHAR(255) NULL ,

`principals` TEXT ,

`context` TEXT ,

`entries` TEXT ,

`trackingids` TEXT,

INDEX `idx_auditrecon_objectid` (`objectid` ASC),

INDEX `idx_auditrecon_activitydate` (`activitydate` ASC)

)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `auditactivity`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `auditactivity` (

`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,

`objectid` VARCHAR(56) NOT NULL ,

`activitydate` VARCHAR(29) NOT NULL COMMENT 'Date format: 2011-09-09T14:58:17.654+02:00' ,

`eventname` VARCHAR(255) NULL ,

`transactionid` VARCHAR(255) NOT NULL ,

`userid` VARCHAR(255) NULL ,

`trackingids` MEDIUMTEXT,

`runas` VARCHAR(255) NULL ,

`activityobjectid` VARCHAR(255) NULL ,

`operation` VARCHAR(255) NULL ,

`subjectbefore` MEDIUMTEXT NULL ,

`subjectafter` MEDIUMTEXT NULL ,

`changedfields` VARCHAR(255) NULL ,

`subjectrev` VARCHAR(255) NULL ,

`passwordchanged` VARCHAR(5) NULL ,

`message` TEXT NULL,

`status` VARCHAR(20) ,

INDEX `idx_auditrecon_objectid` (`objectid` ASC),

INDEX `idx_auditrecon_activitydate` (`activitydate` ASC),

INDEX `idx_auditactivity_transactionid` (`transactionid` ASC)

)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `auditrecon`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `auditrecon` (

`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,

`objectid` VARCHAR(56) NOT NULL ,

`transactionid` VARCHAR(255) NOT NULL ,

`activitydate` VARCHAR(29) NOT NULL COMMENT 'Date format: 2011-09-09T14:58:17.654+02:00' ,

`eventname` VARCHAR(50) NULL ,

`userid` VARCHAR(255) NULL ,

`trackingids` MEDIUMTEXT ,

`activity` VARCHAR(24) NULL ,

`exceptiondetail` TEXT NULL ,

`linkqualifier` VARCHAR(255) NULL ,

`mapping` VARCHAR(511) NULL ,

`message` TEXT NULL ,

`messagedetail` MEDIUMTEXT NULL ,

`situation` VARCHAR(24) NULL ,

`sourceobjectid` VARCHAR(511) NULL ,

`status` VARCHAR(20) NULL ,

`targetobjectid` VARCHAR(511) NULL ,

`reconciling` VARCHAR(12) NULL ,

`ambiguoustargetobjectids` MEDIUMTEXT NULL ,

`reconaction` VARCHAR(36) NULL ,

`entrytype` VARCHAR(7) NULL ,

`reconid` VARCHAR(56) NULL ,

INDEX `idx_auditrecon_objectid` (`objectid` ASC),

INDEX `idx_auditrecon_reconid` (`reconid` ASC),

INDEX `idx_auditrecon_targetobjectid` (`targetobjectid`(28) ASC),

INDEX `idx_auditrecon_sourceobjectid` (`sourceobjectid`(28) ASC),

INDEX `idx_auditrecon_activitydate` (`activitydate` ASC),

INDEX `idx_auditrecon_mapping` (`mapping`(255) ASC),

INDEX `idx_auditrecon_entrytype` (`entrytype` ASC),

INDEX `idx_auditrecon_situation` (`situation` ASC),

INDEX `idx_auditrecon_status` (`status` ASC) )

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `auditsync`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `auditsync` (

`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,

`objectid` VARCHAR(56) NOT NULL ,

`transactionid` VARCHAR(255) NOT NULL ,

`activitydate` VARCHAR(29) NOT NULL COMMENT 'Date format: 2011-09-09T14:58:17.654+02:00' ,

`eventname` VARCHAR(50) NULL ,

`userid` VARCHAR(255) NULL ,

`trackingids` MEDIUMTEXT ,

`activity` VARCHAR(24) NULL ,

`exceptiondetail` TEXT NULL ,

`linkqualifier` VARCHAR(255) NULL ,

`mapping` VARCHAR(511) NULL ,

`message` TEXT NULL ,

`messagedetail` MEDIUMTEXT NULL ,

`situation` VARCHAR(24) NULL ,

`sourceobjectid` VARCHAR(511) NULL ,

`status` VARCHAR(20) NULL ,

`targetobjectid` VARCHAR(511) NULL ,

INDEX `idx_auditrecon_objectid` (`objectid` ASC),

INDEX `idx_auditrecon_activitydate` (`activitydate` ASC)

)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `auditconfig`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `auditconfig` (

`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,

`objectid` VARCHAR(56) NOT NULL ,

`activitydate` VARCHAR(29) NOT NULL COMMENT 'Date format: 2011-09-09T14:58:17.654+02:00' ,

`eventname` VARCHAR(255) NULL ,

`transactionid` VARCHAR(255) NOT NULL ,

`userid` VARCHAR(255) NULL ,

`trackingids` MEDIUMTEXT,

`runas` VARCHAR(255) NULL ,

`configobjectid` VARCHAR(255) NULL ,

`operation` VARCHAR(255) NULL ,

`beforeObject` MEDIUMTEXT NULL ,

`afterObject` MEDIUMTEXT NULL ,

`changedfields` VARCHAR(255) NULL ,

`rev` VARCHAR(255) NULL,

INDEX `idx_auditrecon_objectid` (`objectid` ASC),

INDEX `idx_auditrecon_activitydate` (`activitydate` ASC),

INDEX `idx_auditactivity_transactionid` (`transactionid` ASC)

)

ENGINE = InnoDB;

grant all on audit.* to root@'%' IDENTIFIED BY 'password';