/*!40101 SET NAMES utf8 */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET SQL_NOTES=0 */;

CREATE TABLE IF NOT EXISTS `apn` (
  `apnID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `type` varchar(50) NOT NULL,
  `aph` longblob,
  `lastChanged` datetime DEFAULT NULL,
  PRIMARY KEY (`apnID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `applicationBlock` (
  `applicationBlockID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `type` varchar(50) NOT NULL,
  `uid` varchar(100) NOT NULL,
  `terminalID` varchar(50) DEFAULT NULL,
  `localGroupID` varchar(5) DEFAULT NULL,
  `block` smallint(6) DEFAULT NULL,
  `installed` bit(1) DEFAULT NULL,
  `groupBy` varchar(250) DEFAULT NULL,
  `changedDate` datetime DEFAULT NULL,
  `inUse` bit(1) DEFAULT NULL,
  PRIMARY KEY (`applicationBlockID`),
  UNIQUE KEY `IX_applicationBlock_terminalID_uid` (`terminalID`,`localGroupID`,`uid`) USING BTREE,
  KEY `IX_applicationBlock_localGroupID_uid` (`localGroupID`,`uid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `Connector` (
  `connectorId` varchar(50) NOT NULL,
  `location` varchar(255) DEFAULT NULL,
  `displayName` varchar(255) DEFAULT NULL,
  `connectorState` varchar(255) DEFAULT NULL,
  `locked` bit(1) NOT NULL,
  `collaborationSystem` varchar(255) DEFAULT NULL,
  `userMode` varchar(255) DEFAULT NULL,
  `supportedUsersExp` varchar(255) DEFAULT NULL,
  `supportedGroup` varchar(255) DEFAULT NULL,
  `nodeMode` varchar(255) DEFAULT NULL,
  `serviceVersion` varchar(255) DEFAULT NULL,
  `dmeDomainName` varchar(255) DEFAULT NULL,
  `revision` int(11) DEFAULT NULL,
  PRIMARY KEY (`connectorId`),
  KEY `FK54EC142DB47D0252` (`dmeDomainName`),
  CONSTRAINT `FK54EC142DB47D0252` FOREIGN KEY (`dmeDomainName`) REFERENCES `DMEDomain` (`domainName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ConnectorFunction` (
  `function` varchar(128) NOT NULL,
  `connectorId` varchar(50) NOT NULL,
  `functionState` varchar(25) DEFAULT NULL,
  `acceptPoll` bit(1) DEFAULT NULL,
  PRIMARY KEY (`function`,`connectorId`),
  KEY `FK67C96F255D6CA4BC` (`connectorId`),
  CONSTRAINT `FK67C96F255D6CA4BC` FOREIGN KEY (`connectorId`) REFERENCES `Connector` (`connectorId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `ConnectorProperty` (
  `connectorId` varchar(50) NOT NULL,
  `keyName` varchar(128) NOT NULL,
  `propertyValue` varchar(2048) DEFAULT NULL,
  PRIMARY KEY (`connectorId`,`keyName`),
  KEY `FKDA47D9225D6CA4BC` (`connectorId`),
  CONSTRAINT `FKDA47D9225D6CA4BC` FOREIGN KEY (`connectorId`) REFERENCES `Connector` (`connectorId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ConnectorUser` (
  `function` varchar(128) NOT NULL,
  `userID` varchar(50) NOT NULL,
  `connectorId` varchar(50) NOT NULL,
  `supported` bit(1) DEFAULT NULL,
  `success` int(11) DEFAULT NULL,
  `error` int(11) DEFAULT NULL,
  PRIMARY KEY (`function`,`userID`,`connectorId`),
  KEY `FK1B6495985D6CA4BC` (`connectorId`),
  KEY `FK1B6495988159F4BB` (`userID`),
  CONSTRAINT `FK1B6495985D6CA4BC` FOREIGN KEY (`connectorId`) REFERENCES `Connector` (`connectorId`),
  CONSTRAINT `FK1B6495988159F4BB` FOREIGN KEY (`userID`) REFERENCES `usr` (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `SuspectedConnectors` (
  `connectorId` varchar(50) NOT NULL,
  `serverId` varchar(128) NOT NULL,
  `serverName` varchar(100) DEFAULT NULL,
  `suspectedTime` datetime DEFAULT NULL,
  PRIMARY KEY (`connectorId`,`serverId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dayScheme` (
  `daySchemeID` int(11) NOT NULL AUTO_INCREMENT,
  `startTime` time NOT NULL,
  `dayOfWeek` int(11) NOT NULL,
  `endTime` time DEFAULT NULL,
  `schemeID` int(11) NOT NULL,
  `syncInterval` int(11) NOT NULL,
  `fullSyncInterval` int(11) NOT NULL,
  `subscribed` bit DEFAULT 0 NOT NULL,
  PRIMARY KEY (`daySchemeID`),
  KEY `IX_dayScheme_schemeID` (`schemeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `defaultApplicationBlock` (
  `defaultApplicationBlockID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `type` varchar(50) NOT NULL,
  `uid` varchar(100) NOT NULL,
  `block` tinyint(4) DEFAULT NULL,
  `groupBy` varchar(250) DEFAULT NULL,
  `changedDate` datetime DEFAULT NULL,
  PRIMARY KEY (`defaultApplicationBlockID`),
  KEY `IX_defaultApplicationBlock_groupBy` (`groupBy`),
  KEY `IX_defaultApplicationBlock_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `defaultSettings` (
  `defaultSettingID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `value` varchar(2048) NOT NULL,
  `lockOnTerminal` smallint(6) DEFAULT NULL,
  `intervalSetting` varchar(250) DEFAULT NULL,
  `changedDate` datetime DEFAULT NULL,
  `lockOnSuperuser` smallint(6) DEFAULT NULL,
  `lockInterval` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`defaultSettingID`),
  KEY `IX_defaultSettings_changedDate` (`changedDate`),
  KEY `IX_defaultSettings_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `deviceInfo` (
  `deviceInfoID` int(11) NOT NULL AUTO_INCREMENT,
  `locked` bit(1) NOT NULL,
  `dmeVersionType` varchar(255) DEFAULT NULL,
  `dmManufacturer` varchar(250) DEFAULT NULL,
  `dmModel` varchar(250) DEFAULT NULL,
  `phoneModel` varchar(250) DEFAULT NULL,
  `pictureData` longblob,
  `picturePath` varchar(250) DEFAULT NULL,
  `pictureDimensions` varchar(15) DEFAULT NULL,
  `os` varchar(255) DEFAULT NULL,
  `platform` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`deviceInfoID`),
  KEY `IX_deviceInfo_deviceInfoID_locked` (`deviceInfoID`,`locked`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `deviceSignature` (
  `deviceID` varchar(50) NOT NULL,
  `status` int(11) DEFAULT NULL,
  `errorCount` int(11) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `creator` varchar(50) DEFAULT NULL,
  `encodedKey` text,
  PRIMARY KEY (`deviceID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `distinctValues` (
  `value` varchar(50) NOT NULL,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_action` (
  `actionID` int(11) NOT NULL AUTO_INCREMENT,
  `command` varchar(255) DEFAULT NULL,
  `actionPlan` int(11) DEFAULT NULL,
  `node_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`actionID`),
  KEY `FK723AF2AC8EEF687F` (`node_id`),
  KEY `FK723AF2ACA76DE723` (`actionPlan`),
  CONSTRAINT `FK723AF2AC8EEF687F` FOREIGN KEY (`node_id`) REFERENCES `dm_node` (`id`),
  CONSTRAINT `FK723AF2ACA76DE723` FOREIGN KEY (`actionPlan`) REFERENCES `dm_action_plan` (`actionPlanPK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_action_dm_action` (
  `dm_action_actionID` int(11) NOT NULL,
  `children_actionID` int(11) NOT NULL,
  UNIQUE KEY `children_actionID` (`children_actionID`),
  KEY `FKC5D878D9D04C3ECF` (`children_actionID`),
  KEY `FKC5D878D986039CC2` (`dm_action_actionID`),
  CONSTRAINT `FKC5D878D986039CC2` FOREIGN KEY (`dm_action_actionID`) REFERENCES `dm_action` (`actionID`),
  CONSTRAINT `FKC5D878D9D04C3ECF` FOREIGN KEY (`children_actionID`) REFERENCES `dm_action` (`actionID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_action_plan` (
  `actionPlanPK` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(128) NOT NULL,
  `model` varchar(128) NOT NULL,
  PRIMARY KEY (`actionPlanPK`),
  UNIQUE KEY `IX_Unique_dm_action_plan_type_model` (`type`,`model`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_config_server` (
  `name` varchar(255) NOT NULL,
  `value` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_connection_history` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `created` datetime DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  `device` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `FKB170BFA913CE9D71` (`device`),
  CONSTRAINT `FKB170BFA913CE9D71` FOREIGN KEY (`device`) REFERENCES `dm_devices` (`devicePK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_devices` (
  `devicePK` int(11) NOT NULL AUTO_INCREMENT,
  `deviceID` varchar(255) DEFAULT NULL,
  `manufacturer` varchar(255) DEFAULT NULL,
  `model` varchar(255) DEFAULT NULL,
  `dmVersion` varchar(255) DEFAULT NULL,
  `language` varchar(255) DEFAULT NULL,
  `digest` varchar(255) DEFAULT NULL,
  `client_nonce` tinyblob,
  `server_nonce` tinyblob,
  `deleted` bit(1) DEFAULT NULL,
  `phoneNumber` varchar(255) DEFAULT NULL,
  `bootID` varchar(255) DEFAULT NULL,
  `connectionState` varchar(255) DEFAULT NULL,
  `user` int(11) DEFAULT NULL,
  PRIMARY KEY (`devicePK`),
  UNIQUE KEY `IX_Unique_dm_devices_bootID` (`bootID`) USING BTREE,
  KEY `FK775BC72763FC4C84` (`user`),
  CONSTRAINT `FK775BC72763FC4C84` FOREIGN KEY (`user`) REFERENCES `dm_users` (`userPK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_device_tree` (
  `deviceID` varchar(50) NOT NULL,
  `nodeTree` longblob DEFAULT NULL,
  `createdDate` datetime DEFAULT NULL,
  PRIMARY KEY (`deviceID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_dme_jobs` (
  `jobID` varchar(255) NOT NULL,
  `deviceID` varchar(255) DEFAULT NULL,
  `groupID` varchar(255) DEFAULT NULL,
  `phoneNumber` varchar(255) DEFAULT NULL,
  `createdDate` datetime DEFAULT NULL,
  `jobRequest` longblob,
  `requestType` int(11) DEFAULT NULL,
  `status` longblob,
  `nodeJobs` longblob,
  `notifications` longblob,
  `waitForJobID` varchar(100) DEFAULT NULL,
  `waitSendIf` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`jobID`),
  KEY `IX_dm_dme_jobs_deviceID` (`deviceID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_error_devices` (
  `errorDeviceID` int(11) NOT NULL AUTO_INCREMENT,
  `phoneNumber` varchar(250) DEFAULT NULL,
  `deviceID` varchar(250) DEFAULT NULL,
  `errorState` varchar(255) DEFAULT NULL,
  `createdDate` datetime DEFAULT NULL,
  `dmJobID` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`errorDeviceID`),
  KEY `IX_dm_error_devices_deviceID` (`deviceID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_job` (
  `jobID` varchar(255) NOT NULL,
  `status` varchar(255) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `exploreJob` bit(1) DEFAULT NULL,
  `responseOnStatus` bit(1) DEFAULT NULL,
  `nextResend` datetime DEFAULT NULL,
  `resetCount` int(11) DEFAULT NULL,
  `blockDevice` bit(1) DEFAULT NULL,
  `device` int(11) DEFAULT NULL,
  PRIMARY KEY (`jobID`),
  KEY `FKB0D16B0713CE9D71` (`device`),
  CONSTRAINT `FKB0D16B0713CE9D71` FOREIGN KEY (`device`) REFERENCES `dm_devices` (`devicePK`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_node` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `target` varchar(512) DEFAULT NULL,
  `format` varchar(255) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  `nodeData` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK695DC7F85C4450FD` (`nodeData`),
  CONSTRAINT `FK695DC7F85C4450FD` FOREIGN KEY (`nodeData`) REFERENCES `dm_nodeData` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_nodeData` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `data` longblob,
  `checksum` tinyblob,
  PRIMARY KEY (`pk`),
  KEY `IX_dm_nodeData_checksum` (checksum(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_operation` (
  `operationID` int(11) NOT NULL AUTO_INCREMENT,
  `parent_operationID` int(11) DEFAULT NULL,
  `command` varchar(255) DEFAULT NULL,
  `statusCode` varchar(255) DEFAULT NULL,
  `jobNodeID` varchar(255) DEFAULT NULL,
  `task` int(11) DEFAULT NULL,
  `node_id` int(11) DEFAULT NULL,
  `resultNode_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`operationID`),
  KEY `FK712085318EEF687F` (`node_id`),
  KEY `FK712085311120390F` (`task`),
  KEY `FK71208531F592B5C2` (`resultNode_id`),
  CONSTRAINT `FK712085311120390F` FOREIGN KEY (`task`) REFERENCES `dm_task` (`id`),
  CONSTRAINT `FK712085318EEF687F` FOREIGN KEY (`node_id`) REFERENCES `dm_node` (`id`),
  CONSTRAINT `FK71208531F592B5C2` FOREIGN KEY (`resultNode_id`) REFERENCES `dm_node` (`id`),
  CONSTRAINT `FK71208531D328DF2F` FOREIGN KEY (`parent_operationID`) REFERENCES `dm_operation` (`operationID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_persisted_tree_structure` (
  `jobID` varchar(255) NOT NULL,
  `updatedDate` datetime NOT NULL,
  `treeStructure` longblob NOT NULL,
  PRIMARY KEY (`jobID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_path_exclusions` (
  `pathExclusionID` int(11) NOT NULL AUTO_INCREMENT,
  `path` varchar(255) NOT NULL,
  PRIMARY KEY (`pathExclusionID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_software_install` (
  `softwareInstallID` int(11) NOT NULL AUTO_INCREMENT,
  `requestedJobID` varchar(255) DEFAULT NULL,
  `defaultVersion` bit(1) DEFAULT NULL,
  `defaultVersionGroup` varchar(255) DEFAULT NULL,
  `updateFileID` varchar(255) DEFAULT NULL,
  `fallBackMethod` varchar(255) DEFAULT NULL,
  `serviceSMS` varchar(255) DEFAULT NULL,
  `serverPath` bit(1) DEFAULT NULL,
  `gprsSetting` varchar(255) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `fileType` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`softwareInstallID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_support` (
  `supportID` int(11) NOT NULL AUTO_INCREMENT,
  `deviceID` varchar(255) DEFAULT NULL,
  `supportType` varchar(255) DEFAULT NULL,
  `support` int(11) DEFAULT NULL,
  PRIMARY KEY (`supportID`),
  KEY `IX_dm_support_deviceID` (`deviceID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_support_param` (
  `paramID` int(11) NOT NULL AUTO_INCREMENT,
  `paramName` varchar(255) DEFAULT NULL,
  `paramValue` varchar(255) DEFAULT NULL,
  `support` int(11) DEFAULT NULL,
  PRIMARY KEY (`paramID`),
  KEY `FKAAA5EE74E3A3FE9` (`support`),
  CONSTRAINT `FKAAA5EE74E3A3FE9` FOREIGN KEY (`support`) REFERENCES `dm_support` (`supportID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_task` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `state` varchar(255) DEFAULT NULL,
  `actionPlan` int(11) NOT NULL,
  `job` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK69604F7BA76DE723` (`actionPlan`),
  KEY `FK69604F7BF84B14D5` (`job`),
  CONSTRAINT `FK69604F7BA76DE723` FOREIGN KEY (`actionPlan`) REFERENCES `dm_action_plan` (`actionPlanPK`),
  CONSTRAINT `FK69604F7BF84B14D5` FOREIGN KEY (`job`) REFERENCES `dm_job` (`jobID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_users` (
  `userPK` int(11) NOT NULL AUTO_INCREMENT,
  `userID` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`userPK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_ddf` (
  `ddfID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `fileName` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `xml` longtext DEFAULT NULL,
  `groupName` varchar(100) DEFAULT NULL,
  `changeDate` datetime NOT NULL,
  PRIMARY KEY (`ddfID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_ddf_conf` (
  `ddfConfID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `groupName` varchar(100) DEFAULT NULL,
  `changeDate` datetime NOT NULL,
  `ddfID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ddfConfID`),
  CONSTRAINT `FK235C92D32CDC2353` FOREIGN KEY (`ddfID`) REFERENCES `dm_ddf` (`ddfID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dm_ddf_values` (
  `ddfValueID` int(11) NOT NULL AUTO_INCREMENT,
  `value` longblob DEFAULT NULL,
  `priority` int(11) NOT NULL,
  `type` varchar(255) DEFAULT NULL,
  `path` varchar(1024) NOT NULL,
  `format` varchar(50) DEFAULT NULL,
  `operation` varchar(50) DEFAULT NULL,
  `ddfConfID` int(11) NOT NULL,
  PRIMARY KEY (`ddfValueID`),
  CONSTRAINT `FKDE2951114755E0BB` FOREIGN KEY (`ddfConfID`) REFERENCES `dm_ddf_conf` (`ddfConfID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `DME_Attachments` (
  `attachmentLinkID` varchar(50) NOT NULL,
  `fileName` varchar(250) NOT NULL,
  `data` longblob NOT NULL,
  `created` datetime DEFAULT NULL,
  `fingerPrint` varchar(50) NOT NULL,
  `fileSize` bigint(20) NOT NULL,
  PRIMARY KEY (`attachmentLinkID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dme_keystore_certs` (
  `alias` varchar(255) NOT NULL,
  `object` blob NOT NULL,
  `id` varchar(50) DEFAULT NULL,
  `created` datetime NOT NULL,
  `subjectdn` varchar(450) NOT NULL,
  `isCA` bit(1) DEFAULT NULL,
  `displaySubject` varchar(100) DEFAULT NULL,
  `displayIssuer` varchar(100) DEFAULT NULL,
  `expirationDate` datetime DEFAULT NULL,
  PRIMARY KEY (`alias`),
  KEY `IX_dme_keystore_certs_id` (`id`),
  KEY `IX_dme_keystore_certs_subjectdn` (`subjectdn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dme_keystore_crl_ap` (
  `issuer` varchar(255) NOT NULL,
  `ap` varchar(255) NOT NULL,
  PRIMARY KEY (`issuer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dme_keystore_crls` (
  `issuer` varchar(255) NOT NULL,
  `source` varchar(255) NOT NULL,
  `local` varchar(255) NOT NULL,
  `accesstype` int(11) NOT NULL,
  `nextUpdate` varchar(250) NOT NULL,
  PRIMARY KEY (`issuer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dme_keystore_emails` (
  `alias` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`alias`,`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dme_keystore_keys` (
  `alias` varchar(255) NOT NULL,
  `object` blob,
  `type` int(11) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`alias`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `DME_License` (
  `licenseID` varchar(100) NOT NULL,
  `license` blob NOT NULL,
  `numberOfLicenses` int(11) NOT NULL,
  `defaultLicense` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`licenseID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dme_report` (
  `reportID` int(11) NOT NULL AUTO_INCREMENT,
  `reportFile` text,
  `reportName` varchar(250) DEFAULT NULL,
  `reportCategory` varchar(250) DEFAULT NULL,
  `isRunnable` bit(1) DEFAULT NULL,
  PRIMARY KEY (`reportID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `DME_ServerConfiguration` (
  `propertyKey` varchar(250) NOT NULL,
  `propertyValue` text,
  PRIMARY KEY (`propertyKey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dme_servicemonitor_failures` (
  `failureID` int(11) NOT NULL AUTO_INCREMENT,
  `message` text,
  `host` varchar(250) DEFAULT NULL,
  `port` int(11) DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  `serviceType` varchar(250) DEFAULT NULL,
  `duration_seconds` int(11) DEFAULT NULL,
  PRIMARY KEY (`failureID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `DME_System` (
  `name` varchar(250) NOT NULL,
  `value` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dme_traffic` (
  `terminalID` varchar(50) NOT NULL,
  `callID` varchar(32) NOT NULL,
  `timeZone` tinyint(4) DEFAULT NULL,
  `bytesSent` bigint(20) DEFAULT NULL,
  `bytesReceived` bigint(20) DEFAULT NULL,
  `userID` varchar(50) DEFAULT NULL,
  `phoneNumber` varchar(35) DEFAULT NULL,
  `imsi` varchar(40) DEFAULT NULL,
  `trafficType` varchar(250) DEFAULT NULL,
  `started` datetime DEFAULT NULL,
  `isRoaming` bit(1) NOT NULL,
  `intMcc` smallint(6) DEFAULT NULL,
  `intMnc` smallint(6) DEFAULT NULL,
  `extMcc` smallint(6) DEFAULT NULL,
  `callType` tinyint(4) DEFAULT NULL,
  `duration_in` int(11) DEFAULT NULL,
  `duration_out` int(11) DEFAULT NULL,
  `registrated` datetime DEFAULT NULL,
  PRIMARY KEY (`terminalID`,`callID`),
  KEY `IX_dme_traffic_userId_trafficType` (`userID`,`trafficType`),
  KEY `IX_dme_traffic_terminalID_trafficType` (`terminalID`,`trafficType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `DME_UpdateFile` (
  `updateFileID` varchar(250) NOT NULL,
  `name` varchar(250) DEFAULT NULL,
  `fileType` varchar(255) NOT NULL,
  `major` int(11) NOT NULL,
  `minor` int(11) NOT NULL,
  `micro` varchar(250) DEFAULT NULL,
  `sortVersion` varchar(250) DEFAULT NULL,
  `releaseDate` datetime DEFAULT NULL,
  `description` text,
  `fileName` varchar(250) DEFAULT NULL,
  `fileSize` bigint(20) NOT NULL,
  `archive` bit(1) NOT NULL,
  `listed` bit(1) DEFAULT 1 NOT NULL,
  `extraDescription` longtext DEFAULT NULL,
  `orgFileName` varchar(50) DEFAULT NULL,
  `category` varchar(100) NOT NULL,
  `smsCode` varchar(50) DEFAULT NULL,
  `smsCodeType` varchar(250) DEFAULT NULL,
  `installFileType` varchar(255) DEFAULT NULL,
  `defaultVersion` bit(1) NOT NULL,
  `testVersion` bit(1) NOT NULL,
  `data` longblob,
  PRIMARY KEY (`updateFileID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dme_voice_locator` (
  `mcc` int(11) NOT NULL,
  `countryname` varchar(50) DEFAULT NULL,
  `cc` varchar(3) DEFAULT NULL,
  `idd` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`mcc`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dme_voice_locator_area` (
  `areacode` int(11) NOT NULL,
  `mcc` int(11) DEFAULT NULL,
  `cc` int(11) DEFAULT NULL,
  `areaname` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`areacode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dme_voice_locator_name` (
  `mcc` smallint(6) NOT NULL,
  `mnc` smallint(6) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`mcc`,`mnc`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `DMEDomain` (
  `domainName` varchar(255) NOT NULL,
  PRIMARY KEY (`domainName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dmFileStore` (
  `fileID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(195) DEFAULT NULL,
  `size` int(11) DEFAULT NULL,
  `path` varchar(255) DEFAULT NULL,
  `data` longblob,
  `access` varchar(3) DEFAULT NULL,
  `fingerprint` varchar(32) DEFAULT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`fileID`),
  UNIQUE KEY `IX_dmfilestore_path_name` (`path`,`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dmRules` (
  `ruleID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) DEFAULT NULL,
  `domain` varchar(100) DEFAULT NULL,
  `server` varchar(100) DEFAULT NULL,
  `terminalID` varchar(50) DEFAULT NULL,
  `syncMode` int(11) NOT NULL,
  `clientRoot` varchar(50) NOT NULL,
  `clientDestination` varchar(255) DEFAULT NULL,
  `groupID` int(11) DEFAULT NULL,
  `deviceType` varchar(150) DEFAULT NULL,
  `isMonitor` int(11) NOT NULL,
  `linkFolder` varchar(255) DEFAULT NULL,
  `linkFile` varchar(255) DEFAULT NULL,
  `authUser` varchar(50) DEFAULT NULL,
  `authCredentials` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`ruleID`),
  KEY `IX_dmrules_groupID` (`groupID`) USING BTREE,
  KEY `IX_dmrules_terminalID` (`terminalID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `dmWorkQueue` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `fingerprint` varchar(32) NOT NULL,
  `syncID` varchar(50) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `IX_dmworkqueue_syncID` (`syncID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `downloadLinks` (
  `id` varchar(250) NOT NULL,
  `expires` tinyint(4) DEFAULT NULL,
  `messageID` varchar(255) DEFAULT NULL,
  `terminalID` varchar(50) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `phoneNr` varchar(50) DEFAULT NULL,
  `updatefileID` varchar(250) DEFAULT NULL,
  `downloaded` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IX_downloadlinks_created` (`created`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `folderSettings` (
  `folderID` varchar(50) NOT NULL,
  `link` varchar(250) NOT NULL,
  `terminalID` varchar(50) NOT NULL,
  `daysBack` int(11) DEFAULT NULL,
  `includeInSync` tinyint(4) DEFAULT NULL,
  `includeInScan` tinyint(4) DEFAULT NULL,
  `isPseudoFolder` tinyint(4) DEFAULT NULL,
  `headersOnly` tinyint(4) DEFAULT NULL,
  `useDefaultSettings` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`folderID`),
  KEY `IX_folderSettings_terminalID_link` (`terminalID`,`link`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `groupGraph` (
  `groupID` int(11) NOT NULL AUTO_INCREMENT,
  `ldapGroup` varchar(250) NOT NULL,
  `ldapServer` varchar(250) NOT NULL,
  `lft` int(11) DEFAULT NULL,
  `rgt` int(11) DEFAULT NULL,
  PRIMARY KEY (`groupID`),
  KEY `IX_groupGraph_ldapGroup` (`ldapGroup`),
  KEY `IX_groupGraph_lft_rgt` (`lft`,`rgt`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `homeMobileNetworks` (
  `networkID` varchar(150) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `country` varchar(50) NOT NULL,
  `mcc` int(11) NOT NULL,
  `mnc` int(11) NOT NULL,
  PRIMARY KEY (`networkID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `localGroup` (
  `groupID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `priority` int(11) DEFAULT NULL,
  `ldapGroup` varchar(250) DEFAULT NULL,
  `changedDate` datetime DEFAULT NULL,
  `licenseID` varchar(100) DEFAULT NULL,
  `ldapServer` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`groupID`),
  UNIQUE KEY `IX_Unique_localgroup_priority` (`priority`) USING BTREE,
  KEY `IX_localGroup_ldapGroup` (`ldapGroup`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `localGroupRessourceNotification` (
  `localGroupID` int(11) NOT NULL,
  `ressourceID` int(11) NOT NULL,
  `schemeID` int(11) DEFAULT NULL,
  PRIMARY KEY (`localGroupID`,`ressourceID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `logTable` (
  `lineNo` int(11) NOT NULL AUTO_INCREMENT,
  `message` text,
  `category` varchar(50) DEFAULT NULL,
  `level` int(11) NOT NULL,
  `NDC` varchar(100) DEFAULT NULL,
  `terminalID` varchar(50) DEFAULT NULL,
  `userID` varchar(50) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `serverIdentity` varchar(250) DEFAULT NULL,
  `simpleMessage` varchar(250) DEFAULT NULL,
  `functionCategory` varchar(255) DEFAULT NULL,
  `exceptionId` varchar(32) DEFAULT NULL,
  `originatingId` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`lineNo`),
  KEY `IX_logTable_category` (`category`),
  KEY `IX_logTable_exceptionId` (`exceptionId`),
  KEY `IX_logTable_userID_terminalID_created` (`userID`,`terminalID`,`created`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=91 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `message` (
  `id` varchar(250) NOT NULL,
  `type` varchar(50) DEFAULT NULL,
  `obj` blob,
  `status` int(11) DEFAULT NULL,
  `lastUpdate` datetime DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `sentTo` varchar(40) DEFAULT NULL,
  `statusText` varchar(50) DEFAULT NULL,
  `notificationType` varchar(250) DEFAULT NULL,
  `dlr` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `NetworkStatistic` (
  `statisticID` int(11) NOT NULL AUTO_INCREMENT,
  `size` bigint(20) NOT NULL,
  `date` datetime DEFAULT NULL,
  `month` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `category` varchar(255) DEFAULT NULL,
  `duration` bigint(20) NOT NULL,
  `terminalID` varchar(50) DEFAULT NULL,
  `userID` varchar(50) DEFAULT NULL,
  `quarter` int(11) NOT NULL,
  `week` int(11) NOT NULL,
  `inSize` bigint(20) NOT NULL,
  `outSize` bigint(20) NOT NULL,
  PRIMARY KEY (`statisticID`),
  KEY `IX_NetworkStatistic_terminalID_category` (`terminalID`,`category`),
  KEY `IX_NetworkStatistic_userID_category` (`userID`,`category`),
  KEY `IX_NetworkStatistic_date_category` (`date`,`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `notificationRessource` (
  `ressourceID` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `type` varchar(100) DEFAULT NULL,
  `defaultSchemeID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ressourceID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `notificationScheme` (
  `schemeID` int(11) NOT NULL AUTO_INCREMENT,
  `priority` int(11) DEFAULT NULL,
  `enabled` bit(1) NOT NULL,
  `maxNotifications` int(11) DEFAULT NULL,
  `notificationCenter` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`schemeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `notificationSubscription` (
  `subscriptionID` varchar(100) NOT NULL,
  `terminalID` varchar(50) NOT NULL,
  `userID` varchar(50) NOT NULL,
  `resourceID` int(11) NOT NULL,
  `connectorID` varchar(50) NOT NULL,
  `keepaliveTimestamp` datetime NOT NULL,
  `daySchemeID` int(11) NOT NULL,
  PRIMARY KEY (`subscriptionID`),
  KEY `IX_notificationSubscription__terminalID__resourceID` (`terminalID`,`resourceID`),
  KEY `IX_notificationSubscription__created` (`keepaliveTimestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `onlineDevices` (
  `deviceID` varchar(50) NOT NULL,
  `serverName` varchar(100) NOT NULL,
  PRIMARY KEY (`deviceID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `OTA` (
  `name` varchar(250) NOT NULL,
  `category` varchar(250) DEFAULT NULL,
  `messageType` varchar(50) DEFAULT NULL,
  `xml` text,
  `smsCode` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `pendingNotifications` (
  `terminalID` varchar(50) NOT NULL,
  `ressourceID` int(11) NOT NULL,
  `status` int(11) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `extras` int(11) DEFAULT NULL,
  `SMSmessageID` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`terminalID`,`ressourceID`),
  UNIQUE KEY `IX_Unique_pendingNotifications_SMSmessageID` (`SMSmessageID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `preferredMobileNetworks` (
  `mcc` int(11) NOT NULL,
  `mnc` int(11) NOT NULL,
  `homenetworkID` varchar(150) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`mcc`,`mnc`,`homenetworkID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `role` (
  `roleID` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `webRole` smallint(6) DEFAULT NULL,
  `loginRoleID` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`roleID`),
  UNIQUE KEY `roleID` (`roleID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `roleFunction` (
  `roleID` varchar(100) NOT NULL,
  `functionID` varchar(30) NOT NULL,
  `allowed` int(11) NOT NULL,
  PRIMARY KEY (`roleID`,`functionID`,`allowed`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `RrdEntry` (
  `rrdEntryId` int(11) NOT NULL AUTO_INCREMENT,
  `data` blob DEFAULT NULL,
  `statisticKey` varchar(45) NOT NULL,
  `connectorId` varchar(50),
  `serverId` varchar(128),
  PRIMARY KEY (`rrdEntryId`),
  KEY `IX_RrdEntry_serverId` (`serverId`),
  KEY `IX_RrdEntry_connectorId` (`connectorId`),
  CONSTRAINT `IX_RrdEntry_serverId` FOREIGN KEY (`serverId`) REFERENCES `Server` (`serverId`),
  CONSTRAINT `IX_RrdEntry_connectorId` FOREIGN KEY (`connectorId`) REFERENCES `Connector` (`connectorId`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `scanTable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `link` text NOT NULL,
  `folder` varchar(250) NOT NULL,
  `terminalID` varchar(50) NOT NULL,
  `md5sum` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IX_scantable_terminalID_folder` (`terminalID`,`folder`) USING BTREE,
  KEY `IX_scantable_terminalID_link` (`terminalID`,`link`(512))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `sequence` (
  `name` varchar(64) NOT NULL,
  `last_val` int(11) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `Server` (
  `serverId` varchar(128) NOT NULL,
  `name` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`serverId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ServerSMS` (
  `serverSMSID` int(11) NOT NULL AUTO_INCREMENT,
  `message` text,
  `created` datetime DEFAULT NULL,
  `phoneNr` varchar(50) DEFAULT NULL,
  `sentdate` datetime DEFAULT NULL,
  `currentsms` int(11) DEFAULT NULL,
  `smscount` int(11) DEFAULT NULL,
  PRIMARY KEY (`serverSMSID`),
  KEY `IX_ServerSMS_phoneNr` (`phoneNr`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `settings` (
  `settingID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `value` varchar(2048) NOT NULL,
  `localGroupID` varchar(5) DEFAULT NULL,
  `lockOnTerminal` smallint(6) DEFAULT NULL,
  `intervalSetting` varchar(250) DEFAULT NULL,
  `changedDate` datetime DEFAULT NULL,
  `inUse` smallint(6) DEFAULT NULL,
  `noLicense` bit(1) DEFAULT NULL,
  `terminalID` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`settingID`),
  UNIQUE KEY `IX_Unique_settings_terminalID_name` (`terminalID`,`name`,`localGroupID`) USING BTREE,
  KEY `FK5582BC23490769CC` (`terminalID`),
  KEY `IX_settings_localGroupID` (`localGroupID`),
  CONSTRAINT `FK5582BC23490769CC` FOREIGN KEY (`terminalID`) REFERENCES `terminal` (`terminalID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `settingsHistory` (
  `settingID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `value` varchar(2048) NOT NULL,
  `terminalID` varchar(50) DEFAULT NULL,
  `localGroupID` varchar(5) DEFAULT NULL,
  `userID` varchar(50) DEFAULT NULL,
  `lockOnTerminal` int(11) DEFAULT NULL,
  `intervalSetting` varchar(250) DEFAULT NULL,
  `changedDate` datetime DEFAULT NULL,
  `inUse` int(11) DEFAULT NULL,
  PRIMARY KEY (`settingID`),
  KEY `IX_settingsHistory_localGroupID` (`localGroupID`) USING BTREE,
  KEY `IX_settingsHistory_terminalID_name` (`terminalID`,`name`) USING BTREE,
  KEY `IX_settingsHistory_userID_terminalID_name` (`userID`,`terminalID`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `settingsType` (
  `name` varchar(250) NOT NULL,
  `resource` varchar(250) DEFAULT NULL,
  `type` varchar(30) NOT NULL,
  `options` varchar(1024) DEFAULT NULL,
  `groupBy` varchar(100) DEFAULT NULL,
  `maxInterval` varchar(250) DEFAULT NULL,
  `cdata` tinyint(4) DEFAULT NULL,
  `belongsTo` varchar(50) DEFAULT NULL,
  `sortBy` int(11) DEFAULT NULL,
  `useInterval` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`name`),
  KEY `IX_settingsType_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `sim` (
  `simID` varchar(250) NOT NULL,
  `terminalID` varchar(50) DEFAULT NULL,
  `pin1` varchar(250) DEFAULT NULL,
  `pin2` varchar(250) DEFAULT NULL,
  `puk1` varchar(250) DEFAULT NULL,
  `puk2` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`simID`),
  KEY `IX_sim_terminalID` (`terminalID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `supportedFields` (
  `supportedFieldID` int(11) NOT NULL AUTO_INCREMENT,
  `field` varchar(50) NOT NULL,
  `method` varchar(30) NOT NULL,
  `type` varchar(30) NOT NULL,
  `terminalID` varchar(50) NOT NULL,
  PRIMARY KEY (`supportedFieldID`),
  KEY `IX_supportedFields_terminalID_type` (`terminalID`,`type`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `syncAttachmentTable` (
  `syncID` varchar(50) NOT NULL,
  `attachmentID` varchar(32) NOT NULL,
  `folder` varchar(100) DEFAULT NULL,
  `link` text NOT NULL,
  `tx` varchar(38) DEFAULT NULL,
  `modyfied` datetime DEFAULT NULL,
  PRIMARY KEY (`syncID`,`attachmentID`),
  KEY `IX_syncAttachmentTable_tx` (`tx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `syncTable` (
  `syncID` varchar(50) NOT NULL,
  `link` text NOT NULL,
  `metadata` blob,
  `folder` varchar(100) DEFAULT NULL,
  `terminalID` varchar(50) NOT NULL,
  `tx` varchar(38) DEFAULT NULL,
  `md5sum` varchar(32) NOT NULL,
  `modyfied` datetime DEFAULT NULL,
  PRIMARY KEY (`syncID`),
  KEY `IX_syncTable_terminalID_folder` (`terminalID`,`folder`),
  KEY `IX_syncTable_terminalID_link` (`terminalID`,`link`(512)) USING BTREE,
  KEY `IX_syncTable_tx` (`tx`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `tempData` (
  `keyField` varchar(255) NOT NULL,
  `dataField` blob,
  PRIMARY KEY (`keyField`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `terminal` (
  `terminalID` varchar(50) NOT NULL,
  `locked` bit(1) NOT NULL,
  `inUse` bit(1) NOT NULL,
  `localGroup` varchar(5) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `licenseID` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`terminalID`),
  KEY `IX_terminal_localGroup` (`localGroup`),
  KEY `IX_terminal_terminalID_locked` (`terminalID`,`locked`),
  KEY `IX_terminal_licenseID_inUse` (`licenseID`,`inUse`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `terminalAssetMgmt` (
  `terminalId` varchar(50) NOT NULL,
  `purchaseDate` datetime DEFAULT NULL,
  `warrantyExpirationDate` datetime DEFAULT NULL,
  `supplier` varchar(100) DEFAULT NULL,
  `invoiceNo` varchar(100) DEFAULT NULL,
  `purchaseOrder` varchar(100) DEFAULT NULL,
  `requisition` varchar(100) DEFAULT NULL,
  `purchasePrice` varchar(100) DEFAULT NULL,
  `serviceLog` longtext,
  `text1` longtext,
  `text2` longtext,
  `text3` longtext,
  `date1` datetime DEFAULT NULL,
  `date2` datetime DEFAULT NULL,
  `date3` datetime DEFAULT NULL,
  `shortText1` varchar(100) DEFAULT NULL,
  `shortText2` varchar(100) DEFAULT NULL,
  `shortText3` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`terminalId`),
  CONSTRAINT `FK1C341CB5490769CC` FOREIGN KEY (`terminalId`) REFERENCES `terminal` (`terminalID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `terminalGroupMembership` (
  `terminalID` varchar(50) NOT NULL,
  `localGroupID` int(11) DEFAULT NULL,
  `groupType` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`terminalID`),
  KEY `IX_terminalGroupMembership_localGroupID` (`localGroupID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `terminalGroupMembership_tmp` (
  `terminalID` varchar(50) NOT NULL,
  `localGroupID` int(11) DEFAULT NULL,
  `groupType` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`terminalID`),
  KEY `IX_terminalGroupMembershipTMP_localGroupID` (`localGroupID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `terminalOwner` (
  `terminalID` varchar(50) NOT NULL,
  `userID` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`terminalID`),
  KEY `IX_terminalOwner_userID_terminalID` (`userID`,`terminalID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `terminalOwnerHistory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `terminalID` varchar(50) NOT NULL,
  `userID` varchar(50) NOT NULL,
  `firstUsed` datetime NOT NULL,
  `lastUsed` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IX_terminalOwnerHistory_terminalID_userID` (`terminalID`,`userID`) USING BTREE,
  KEY `IX_terminalOwnerHistory_userID_terminalID` (`userID`,`terminalID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `terminalProperty` (
  `property` varchar(150) NOT NULL,
  `terminalID` varchar(50) NOT NULL,
  `value` varchar(250) DEFAULT NULL,
  `type` varchar(250) DEFAULT NULL,
  `changed` datetime DEFAULT NULL,
  PRIMARY KEY (`property`,`terminalID`),
  KEY `IX_terminalProperty_terminalID` (`terminalID`) USING BTREE,
  CONSTRAINT `FKD3B39871490769CC` FOREIGN KEY (`terminalID`) REFERENCES `terminal` (`terminalID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `terminalRessourceNotification` (
  `terminalID` varchar(50) NOT NULL,
  `ressourceID` int(11) NOT NULL,
  `schemeID` int(11) DEFAULT NULL,
  `calculatedNextPoll` datetime DEFAULT NULL,
  `lastPoll` datetime DEFAULT NULL,
  `lastSync` datetime DEFAULT NULL,
  `lastNotifications` varchar(255) DEFAULT NULL,
  `lastNotificationTimes` tinyblob DEFAULT NULL,
  `lastNotification` datetime DEFAULT NULL,
  `lastFullPoll` datetime DEFAULT NULL,
  `lastSubscriptionWatermark` varchar(60) DEFAULT NULL,
  `calculatedGroupSchemeID` int(11) DEFAULT NULL,
  `retries` int(11) DEFAULT 0 NOT NULL,  
  `outstanding` int(11) DEFAULT 0 NOT NULL,
  `nextScanServer` varchar(200) DEFAULT NULL,  
  `errorMessage` varchar(1024) DEFAULT NULL,
  `checkSubscriptionEvent` bit DEFAULT 0 NOT NULL,  
  PRIMARY KEY (`terminalID`,`ressourceID`),
  KEY `IX_terminalRessourceNotification_schemeID` (`schemeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ThirdPartyEvent` (
  `syncID` varchar(50) NOT NULL,
  `status` int(11) NOT NULL,
  `userID` int(11) NOT NULL,
  `eventValue` text NOT NULL,
  `thirdPartyID` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`syncID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ThirdPartyResponse` (
  `ThirdPartyResponsePK` int(11) NOT NULL AUTO_INCREMENT,
  `value` text NOT NULL,
  `type` varchar(25) NOT NULL,
  `syncID` varchar(50) NOT NULL,
  PRIMARY KEY (`ThirdPartyResponsePK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ThirdPartyStatus` (
  `ThirdPartyStatusPK` int(11) NOT NULL AUTO_INCREMENT,
  `message` text NOT NULL,
  `date` datetime NOT NULL,
  `status` varchar(25) NOT NULL,
  PRIMARY KEY (`ThirdPartyStatusPK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ThirdPartyUserIdentifier` (
  `ThirdPartyUserIdentifierPK` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(50) NOT NULL,
  `type` varchar(25) NOT NULL,
  `dmeUserID` varchar(50) NOT NULL,
  PRIMARY KEY (`ThirdPartyUserIdentifierPK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `tx_syncAttachmentTable` (
  `syncID` varchar(50) NOT NULL,
  `attachmentID` varchar(32) NOT NULL,
  `link` text NOT NULL,
  `folder` varchar(100) DEFAULT NULL,  
  `tx` varchar(38) DEFAULT NULL,
  `modyfied` datetime DEFAULT NULL,
  PRIMARY KEY (`syncID`,`attachmentID`),
  KEY `IX_tx_syncAttachmentTable_tx` (`tx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `tx_syncTable` (
  `syncID` varchar(50) NOT NULL,
  `link` text NOT NULL,
  `metadata` blob,
  `folder` varchar(100) DEFAULT NULL,
  `terminalID` varchar(50) NOT NULL,
  `tx` varchar(38) DEFAULT NULL,
  `md5sum` varchar(32) NOT NULL,
  `modyfied` datetime DEFAULT NULL,
  PRIMARY KEY (`syncID`),
  KEY `IX_tx_syncTable_terminalID_folder` (`terminalID`,`folder`),
  KEY `IX_tx_syncTable_terminalID_tx` (`terminalID`,`tx`) USING BTREE,
  KEY `IX_tx_synctable_terminalID_link` (`terminalID`,`link`(512))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `uniqueKey` (
  `keyValue` varchar(250) NOT NULL,
  `terminalID` varchar(50) DEFAULT NULL,
  `phoneNumber` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`keyValue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `updateProperty` (
  `updatePropertyID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) DEFAULT NULL,
  `value` varchar(250) DEFAULT NULL,
  `terminalID` varchar(50) DEFAULT NULL,
  `userID` varchar(50) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`updatePropertyID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `userFilterGroup` (
  `userID` varchar(50) NOT NULL,
  `groupID` varchar(250) NOT NULL,
  PRIMARY KEY (`userID`,`groupID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `userGroup` (
  `userID` varchar(50) NOT NULL,
  `roleID` varchar(100) NOT NULL,
  PRIMARY KEY (`userID`,`roleID`),
  KEY `FK12E9C174D33C29E0` (`roleID`),
  CONSTRAINT `FK12E9C1748159F4BB` FOREIGN KEY (`userID`) REFERENCES `usr` (`userID`),
  CONSTRAINT `FK12E9C174D33C29E0` FOREIGN KEY (`roleID`) REFERENCES `role` (`roleID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `userLdapGroup` (
  `userID` varchar(50) NOT NULL,
  `ldapGroup` varchar(250) NOT NULL,
  `ldapServer` varchar(250) NOT NULL,
  PRIMARY KEY (`userID`,`ldapGroup`),
  KEY `IX_userLdapGroup_ldapServer` (`ldapServer`),
  CONSTRAINT `FK645DE38D8159F4BB` FOREIGN KEY (`userID`) REFERENCES `usr` (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `userProperty` (
  `property` varchar(150) NOT NULL,
  `userID` varchar(50) NOT NULL,
  `value` varchar(250) DEFAULT NULL,
  `type` varchar(250) DEFAULT NULL,
  `changed` datetime DEFAULT NULL,
  PRIMARY KEY (`property`,`userID`),
  KEY `FK970832C08159F4BB` (`userID`),
  CONSTRAINT `FK970832C08159F4BB` FOREIGN KEY (`userID`) REFERENCES `usr` (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `userTerminal` (
  `terminalID` varchar(50) NOT NULL,
  `userID` varchar(50) NOT NULL,
  `lastUsed` datetime NOT NULL,
  `phoneNumber` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`terminalID`,`userID`,`lastUsed`),
  KEY `FK8BD1E7678159F4BB` (`userID`),
  CONSTRAINT `FK8BD1E767490769CC` FOREIGN KEY (`terminalID`) REFERENCES `terminal` (`terminalID`),
  CONSTRAINT `FK8BD1E7678159F4BB` FOREIGN KEY (`userID`) REFERENCES `usr` (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `usr` (
  `userID` varchar(50) NOT NULL,
  `locked` bit(1) NOT NULL,
  `password` varchar(250) DEFAULT NULL,
  `userName` varchar(250) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `loginFailures` int(11) DEFAULT NULL,
  `occupation` varchar(250) DEFAULT NULL,
  `collabPassword` varchar(250) DEFAULT NULL,
  `dmeDomainName` varchar(255) DEFAULT NULL,
  `userType` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`userID`),
  KEY `FK1C594B47D0252` (`dmeDomainName`),
  KEY `IX_usr_userID_locked` (`userID`,`locked`) USING BTREE,
  CONSTRAINT `FK1C594B47D0252` FOREIGN KEY (`dmeDomainName`) REFERENCES `DMEDomain` (`domainName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `prov_iphone_cacertificate` (
  `certificateID` int(11) NOT NULL AUTO_INCREMENT,
  `certificate` blob DEFAULT NULL,
  `defaultCA` bit(1) DEFAULT NULL,
  `encryptedkeyPair` blob DEFAULT NULL,
  `subjectdn` varchar(450) DEFAULT NULL,
  PRIMARY KEY (`certificateID`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `prov_iphone_enrollment` (
  `enrollmentID` varchar(40) NOT NULL,
  `apnInfo` blob DEFAULT NULL,
  `apnToken` varchar(255) DEFAULT NULL,
  `certificate` blob DEFAULT NULL,
  `challenge` varchar(100) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `deviceID` varchar(50) DEFAULT NULL,
  `lastState` varchar(60) DEFAULT NULL,
  `lastStatusDate` datetime DEFAULT NULL,
  `phoneNumber` varchar(50) DEFAULT NULL,
  `status` blob DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `caCertificate` int(11) DEFAULT NULL,
  PRIMARY KEY (`enrollmentID`),
  KEY `IX_pie_subject` (`subject`),
  KEY `IX_pie_apnToken` (`apnToken`),
  KEY `IX_pie_deviceID` (`deviceID`),
  KEY `IX_pie_lastStatusDate` (`lastStatusDate`),
  CONSTRAINT `FK1C594B47DF152` FOREIGN KEY (`caCertificate`) REFERENCES `prov_iphone_cacertificate` (`certificateID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE IF NOT EXISTS `prov_iphone_operations` (
  `operationID` int(11) NOT NULL AUTO_INCREMENT,
  `createdDate` datetime DEFAULT NULL,
  `lastStatus` varchar(50) DEFAULT NULL,
  `lastStatusDate` datetime DEFAULT NULL,
  `mdmRequest` longblob DEFAULT NULL,
  `meta` varchar(100) DEFAULT NULL,
  `responseObject` longblob DEFAULT NULL,
  `requestType` varchar(50) DEFAULT NULL,
  `requestUUID` varchar(60) DEFAULT NULL,
  `status` mediumblob DEFAULT NULL,
  `enrollment` varchar(40) DEFAULT NULL, 
  PRIMARY KEY (`operationID`),
  KEY `IX_pio_lastStatusDate` (`lastStatusDate`),
  CONSTRAINT `FK3837383939383` FOREIGN KEY (`enrollment`) REFERENCES `prov_iphone_enrollment` (`enrollmentID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `pre_cache_connector` ( 
  `terminalID` varchar(50) NOT NULL,
  `function` varchar(128) NOT NULL, 
  `preCacheState` varchar(50) NOT NULL, 
   connectorId varchar(50) NOT NULL, 
 PRIMARY KEY (`terminalID`, `function`)
 )  ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO DME_System (name,value) VALUES ('DATABASE_STRUCTURE_VERSION','8');

/* DONE */