-- ------------------------------------------------
-- SoD tables for auth. objects, classes and fields 
-- Created 20140514
-- ------------------------------------------------

CREATE TABLE IF NOT EXISTS `sod_auth_field` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `system` varchar(8) NOT NULL,
  `client` char(3) NOT NULL,
  `field` varchar(10) NOT NULL COMMENT 'Auth. field',
  `text` varchar(60) DEFAULT NULL COMMENT 'Text',
  `deleted` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `sys_cli_fld` (`system`,`client`,`field`),
  KEY `del` (`deleted`)
);

CREATE TABLE IF NOT EXISTS `sod_auth_class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `system` varchar(8) NOT NULL,
  `client` char(3) NOT NULL,
  `class` varchar(4) NOT NULL COMMENT 'Object class',
  `text` varchar(60) DEFAULT NULL COMMENT 'Text',
  `deleted` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `sys_cli_cls` (`system`,`client`,`class`),
  KEY `del` (`deleted`)
);

CREATE TABLE IF NOT EXISTS `sod_auth_object` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `system` varchar(8) NOT NULL,
  `client` char(3) NOT NULL,
  `class_id` int(11) NOT NULL COMMENT 'Object class id',
  `object` varchar(10) NOT NULL COMMENT 'Auth. object',
  `text` varchar(60) DEFAULT NULL COMMENT 'Text',
  `field1_id` int(11) DEFAULT NULL,
  `field2_id` int(11) DEFAULT NULL,
  `field3_id` int(11) DEFAULT NULL,
  `field4_id` int(11) DEFAULT NULL,
  `field5_id` int(11) DEFAULT NULL,
  `field6_id` int(11) DEFAULT NULL,
  `field7_id` int(11) DEFAULT NULL,
  `field8_id` int(11) DEFAULT NULL,
  `field9_id` int(11) DEFAULT NULL,
  `field10_id` int(11) DEFAULT NULL,
  `deleted` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `sys_cli_obj` (`system`,`client`,`object`),
  KEY `del` (`deleted`),
  FOREIGN KEY (class_id) 
    REFERENCES sod_auth_class(id),
  FOREIGN KEY (field1_id) 
    REFERENCES sod_auth_field(id),
  FOREIGN KEY (field2_id) 
    REFERENCES sod_auth_field(id),
  FOREIGN KEY (field3_id) 
    REFERENCES sod_auth_field(id),
  FOREIGN KEY (field4_id) 
    REFERENCES sod_auth_field(id),
  FOREIGN KEY (field5_id) 
    REFERENCES sod_auth_field(id),
  FOREIGN KEY (field6_id) 
    REFERENCES sod_auth_field(id),
  FOREIGN KEY (field7_id) 
    REFERENCES sod_auth_field(id),
  FOREIGN KEY (field8_id) 
    REFERENCES sod_auth_field(id),
  FOREIGN KEY (field9_id) 
    REFERENCES sod_auth_field(id),
  FOREIGN KEY (field10_id) 
    REFERENCES sod_auth_field(id)
);
