Version: 2.5.1 ================================================================================================ This document contains database schema information for the tables defined within the OpenGTS system. Optional tables (if any) will be indicated by the term "[optional]" next to the table name. Additional information may be obtained by examining the source module for the specified class. The schema listing below should match the installed configuration, however, there may still be minor differences depending on the specific version installed, or changes that have been made to the configuration. The current schema configuration can be generated from the actual database configuration by executing the following command: (executed from within the OpenGTS directory) bin/dbAdmin.pl -schema Or, on Windows: bin\dbConfig.bat -schema ================================================================================================ Table: Account [required] Class: org.opengts.db.tables.Account This table defines the top level Account specific information. ## Column Description SQL Type Key --- ---------------------- ------------------------------ ---------------------------- -------- 1 accountID Account ID VARCHAR(32) PRIMARY 2 accountType Account Type SMALLINT UNSIGNED 3 notifyEmail Notification EMail Address VARCHAR(128) 4 allowNotify Allow Notification TINYINT 5 speedUnits Speed Units TINYINT UNSIGNED 6 distanceUnits Distance Units TINYINT UNSIGNED 7 volumeUnits Volume Units TINYINT UNSIGNED 8 pressureUnits Pressure Units TINYINT UNSIGNED 9 economyUnits Economy Units TINYINT UNSIGNED 10 temperatureUnits Temperature Units TINYINT UNSIGNED 11 latLonFormat Latitude/Longitude Format TINYINT UNSIGNED 12 geocoderMode Geocoder Mode TINYINT UNSIGNED 13 privateLabelName PrivateLabel Name VARCHAR(32) 14 isBorderCrossing Is BorderCrossing Enabled TINYINT 15 retainedEventAge Retained Event Age (sec) INT UNSIGNED 16 maximumDevices Maximum number of devices INT 17 totalPingCount Total 'Ping' Count SMALLINT UNSIGNED 18 maxPingCount Maximum 'Ping' Count SMALLINT UNSIGNED 19 autoAddDevices AutoAdd Devices TINYINT 20 dcsPropertiesID DCS Properties ID VARCHAR(32) 21 smsEnabled SMS Enabled TINYINT 22 smsProperties SMS Properties VARCHAR(200) 23 emailProperties EMail Properties VARCHAR(250) 24 expirationTime Expiration Time INT UNSIGNED 25 defaultUser Default User ID VARCHAR(32) 26 password Password VARCHAR(32) 27 contactName Contact Name VARCHAR(64) [utf8] 28 contactPhone Contact Phone VARCHAR(32) 29 contactEmail Contact EMail Address VARCHAR(128) email 30 timeZone Time Zone VARCHAR(32) 31 passwdChangeTime Last Password Change Time INT UNSIGNED 32 passwdQueryTime Last Password Query Time INT UNSIGNED 33 lastLoginTime Last Login Time INT UNSIGNED 34 isActive Is Active TINYINT 35 displayName Display Name VARCHAR(40) [utf8] 36 description Description VARCHAR(128) [utf8] 37 notes Notes TEXT [utf8] 38 lastUpdateTime Last Update Time INT UNSIGNED 39 creationTime Creation Time INT UNSIGNED ================================================================================================ Table: AccountString [required] Class: org.opengts.db.tables.AccountString This table defines Account specific customized String key/values. ## Column Description SQL Type Key --- ---------------------- ------------------------------ ---------------------------- -------- 1 accountID Account ID VARCHAR(32) PRIMARY 2 stringID String ID VARCHAR(32) PRIMARY 3 singularTitle Singular Title VARCHAR(64) 4 pluralTitle Plural Title VARCHAR(64) 5 description Description VARCHAR(128) [utf8] 6 lastUpdateTime Last Update Time INT UNSIGNED 7 creationTime Creation Time INT UNSIGNED ================================================================================================ Table: User [required] Class: org.opengts.db.tables.User This table defines Account specific Users. ## Column Description SQL Type Key --- ---------------------- ------------------------------ ---------------------------- -------- 1 accountID Account ID VARCHAR(32) PRIMARY 2 userID User ID VARCHAR(32) PRIMARY 3 userType User Type SMALLINT UNSIGNED 4 roleID User Role VARCHAR(32) role 5 password Password VARCHAR(32) 6 gender Gender TINYINT UNSIGNED 7 notifyEmail Notification EMail Address VARCHAR(128) 8 contactName Contact Name VARCHAR(64) [utf8] 9 contactPhone Contact Phone VARCHAR(32) 10 contactEmail Contact EMail Address VARCHAR(64) email 11 timeZone Time Zone VARCHAR(32) 12 firstLoginPageID First Login Page ID VARCHAR(24) 13 preferredDeviceID Preferred Device ID VARCHAR(32) 14 maxAccessLevel Maximum Access Level SMALLINT UNSIGNED 15 passwdChangeTime Last Password Change Time INT UNSIGNED 16 passwdQueryTime Last Password Query Time INT UNSIGNED 17 lastLoginTime Last Login Time INT UNSIGNED 18 isActive Is Active TINYINT 19 displayName Display Name VARCHAR(40) [utf8] 20 description Description VARCHAR(128) [utf8] 21 notes Notes TEXT [utf8] 22 lastUpdateTime Last Update Time INT UNSIGNED 23 creationTime Creation Time INT UNSIGNED ================================================================================================ Table: UserAcl [required] Class: org.opengts.db.tables.UserAcl This table defines User specific Access Control permissions. ## Column Description SQL Type Key --- ---------------------- ------------------------------ ---------------------------- -------- 1 accountID Account ID VARCHAR(32) PRIMARY 2 userID User ID VARCHAR(32) PRIMARY 3 aclID ACL ID VARCHAR(64) PRIMARY 4 accessLevel Access Level SMALLINT UNSIGNED 5 description Description VARCHAR(128) [utf8] 6 lastUpdateTime Last Update Time INT UNSIGNED 7 creationTime Creation Time INT UNSIGNED ================================================================================================ Table: GroupList [required] Class: org.opengts.db.tables.GroupList This table defines the authorized Groups that can be accessed by a given User. ## Column Description SQL Type Key --- ---------------------- ------------------------------ ---------------------------- -------- 1 accountID Account ID VARCHAR(32) PRIMARY 2 userID User ID VARCHAR(32) PRIMARY 3 groupID Device Group ID VARCHAR(32) PRIMARY 4 creationTime Creation Time INT UNSIGNED ================================================================================================ Table: Device [required] Class: org.opengts.db.tables.Device This table defines Device/Vehicle specific information for an Account. A 'Device' record typically represents something that is being 'tracked', such as a Vehicle. ## Column Description SQL Type Key --- ---------------------- ------------------------------ ---------------------------- -------- 1 accountID Account ID VARCHAR(32) PRIMARY 2 deviceID Device/Asset ID VARCHAR(32) PRIMARY 3 groupID Group ID VARCHAR(32) 4 equipmentType Equipment Type VARCHAR(40) 5 vehicleMake Vehicle Make VARCHAR(40) 6 vehicleModel Vehicle Model VARCHAR(40) 7 vehicleID VIN VARCHAR(24) 8 licensePlate License Plate VARCHAR(24) 9 driverID Driver ID VARCHAR(32) 10 driverStatus Driver Status INT UNSIGNED 11 fuelCapacity Fuel Capacity DOUBLE 12 fuelEconomy Approx. Fuel Economy DOUBLE 13 speedLimitKPH Max Speed km/h DOUBLE 14 planDistanceKM Planned Trip Distance DOUBLE 15 installTime Install Time INT UNSIGNED 16 resetTime Reset Time INT UNSIGNED 17 expirationTime Expiration Time INT UNSIGNED 18 uniqueID Unique ID VARCHAR(40) altIndex 19 deviceCode Server ID VARCHAR(24) 20 deviceType Device Type VARCHAR(24) 21 pushpinID Pushpin ID VARCHAR(32) 22 displayColor Display Color VARCHAR(16) 23 serialNumber Serial Number VARCHAR(24) 24 simPhoneNumber SIM Phone Number VARCHAR(24) 25 simID SIM ID VARCHAR(24) 26 smsEmail SMS EMail Address VARCHAR(64) 27 imeiNumber IMEI Number VARCHAR(24) 28 dataKey Data Key TEXT 29 ignitionIndex Ignition I/O Index SMALLINT 30 codeVersion Code Version VARCHAR(32) 31 featureSet Feature Set VARCHAR(64) 32 ipAddressValid Valid IP Addresses VARCHAR(128) 33 lastTotalConnectTime Last Total Connect Time INT UNSIGNED 34 lastDuplexConnectTime Last Duplex Connect Time INT UNSIGNED 35 pendingPingCommand Pending Ping Command TEXT 36 lastPingTime Last 'Ping' Time INT UNSIGNED 37 totalPingCount Total 'Ping' Count SMALLINT UNSIGNED 38 maxPingCount Maximum 'Ping' Count SMALLINT UNSIGNED 39 commandStateMask Command State Mask INT UNSIGNED 40 expectAck Expecting an ACK TINYINT 41 expectAckCode Expected ACK Status Code INT UNSIGNED 42 lastAckCommand Last Command Expecting ACK TEXT 43 lastAckTime Last Received 'ACK' Time INT UNSIGNED 44 dcsPropertiesID DCS Properties ID VARCHAR(32) 45 dcsCommandHost DCS Command Host VARCHAR(32) 46 dcsConfigMask DCS Configuration Mask INT UNSIGNED 47 supportsDMTP Supports DMTP TINYINT 48 supportedEncodings Supported Encodings TINYINT UNSIGNED 49 unitLimitInterval Accounting Time Interval Min SMALLINT UNSIGNED 50 maxAllowedEvents Max Events per Interval SMALLINT UNSIGNED 51 totalProfileMask Total Profile Mask BLOB 52 totalMaxConn Max Total Conn per Interval SMALLINT UNSIGNED 53 totalMaxConnPerMin Max Total Conn per Minute SMALLINT UNSIGNED 54 duplexProfileMask Duplex Profile Mask BLOB 55 duplexMaxConn Max Duplex Conn per Interval SMALLINT UNSIGNED 56 duplexMaxConnPerMin Max Duplex Conn per Minute SMALLINT UNSIGNED 57 lastTcpSessionID Last TCP Session ID VARCHAR(32) 58 ipAddressCurrent Current IP Address VARCHAR(32) 59 remotePortCurrent Current Remote Port SMALLINT UNSIGNED 60 listenPortCurrent Current Listen Port SMALLINT UNSIGNED 61 lastInputState Last Input State INT UNSIGNED 62 lastOutputState Last Output State INT UNSIGNED 63 statusCodeState StatusCode On/Off State INT UNSIGNED 64 lastBatteryLevel Last Battery Level DOUBLE 65 lastFuelLevel Last Fuel Level DOUBLE 66 lastFuelTotal Last Fuel Total Liters DOUBLE 67 lastOilLevel Last Oil Level DOUBLE 68 lastValidLatitude Last Valid Latitude DOUBLE 69 lastValidLongitude Last Valid Longitude DOUBLE 70 lastValidHeading Last Valid Heading DOUBLE 71 lastGPSTimestamp Last Valid GPS Timestamp INT UNSIGNED 72 lastEventTimestamp Last Event Timestamp INT UNSIGNED 73 lastCellServingInfo Last Serving Cell Info VARCHAR(100) 74 lastDistanceKM Last Distance km DOUBLE 75 lastOdometerKM Last Odometer km DOUBLE 76 odometerOffsetKM Odometer Offset km DOUBLE 77 lastEngineOnTime Last Engine On Time INT UNSIGNED 78 lastEngineOffTime Last Engine Off Time INT UNSIGNED 79 lastEngineHours Last Engine Hours DOUBLE 80 engineHoursOffset Engine Hours Offset DOUBLE 81 lastIgnitionOnTime Last Ignition On Time INT UNSIGNED 82 lastIgnitionOffTime Last Ignition Off Time INT UNSIGNED 83 lastIgnitionHours Last Ignition Hours DOUBLE 84 lastStopTime Last Stop Time INT UNSIGNED 85 lastStartTime Last Start Time INT UNSIGNED 86 lastMalfunctionLamp Last MIL TINYINT 87 lastFaultCode Last Fault Code VARCHAR(96) 88 isActive Is Active TINYINT 89 displayName Display Name VARCHAR(40) [utf8] 90 description Description VARCHAR(128) [utf8] 91 notes Notes TEXT [utf8] 92 lastUpdateTime Last Update Time INT UNSIGNED 93 creationTime Creation Time INT UNSIGNED ================================================================================================ Table: Transport [required] Class: org.opengts.db.tables.Transport This table defines the data transport specific information for an Asset/Device. A 'Transport' represents the datapath used to send data to a server. In some cases a single 'Device' can have more than one such datapath to the server, such as a device that incorporates both GPRS and satellite communications. ## Column Description SQL Type Key --- ---------------------- ------------------------------ ---------------------------- -------- 1 accountID Account ID VARCHAR(32) PRIMARY 2 transportID Transport ID VARCHAR(32) PRIMARY 3 assocAccountID Associated Account ID VARCHAR(32) device 4 assocDeviceID Associated Device ID VARCHAR(32) device 5 uniqueID Unique ID VARCHAR(40) altIndex 6 deviceCode Device Code VARCHAR(24) 7 deviceType Device Type VARCHAR(24) 8 serialNumber Serial Number VARCHAR(24) 9 simPhoneNumber SIM Phone Number VARCHAR(24) 10 smsEmail SMS EMail Address VARCHAR(64) 11 imeiNumber IMEI Number VARCHAR(24) 12 lastInputState Last Input State INT UNSIGNED 13 lastOutputState Last Output State INT UNSIGNED 14 ignitionIndex Ignition I/O Index SMALLINT UNSIGNED 15 codeVersion Code Version VARCHAR(32) 16 featureSet Feature Set VARCHAR(64) 17 ipAddressValid Valid IP Addresses VARCHAR(128) 18 ipAddressCurrent Current IP Address VARCHAR(32) 19 remotePortCurrent Current Remote Port SMALLINT UNSIGNED 20 listenPortCurrent Current Listen Port SMALLINT UNSIGNED 21 pendingPingCommand Pending Ping Command TEXT 22 lastPingTime Last 'Ping' Time INT UNSIGNED 23 totalPingCount Total 'Ping' Count SMALLINT UNSIGNED 24 maxPingCount Maximum 'Ping' Count SMALLINT UNSIGNED 25 expectAck Expecting an ACK TINYINT 26 lastAckCommand Last Command Expecting an ACK TEXT 27 lastAckTime Last Received 'ACK' Time INT UNSIGNED 28 supportsDMTP Supports DMTP TINYINT 29 supportedEncodings Supported Encodings TINYINT UNSIGNED 30 unitLimitInterval Accounting Time Interval Min SMALLINT UNSIGNED 31 maxAllowedEvents Max Events per Interval SMALLINT UNSIGNED 32 totalProfileMask Total Profile Mask BLOB 33 totalMaxConn Max Total Conn per Interval SMALLINT UNSIGNED 34 totalMaxConnPerMin Max Total Conn per Minute SMALLINT UNSIGNED 35 duplexProfileMask Duplex Profile Mask BLOB 36 duplexMaxConn Max Duplex Conn per Interval SMALLINT UNSIGNED 37 duplexMaxConnPerMin Max Duplex Conn per Minute SMALLINT UNSIGNED 38 lastTotalConnectTime Last Total Connect Time INT UNSIGNED 39 lastDuplexConnectTime Last Duplex Connect Time INT UNSIGNED 40 displayName Display Name VARCHAR(40) [utf8] 41 description Description VARCHAR(128) [utf8] 42 lastUpdateTime Last Update Time INT UNSIGNED 43 creationTime Creation Time INT UNSIGNED ================================================================================================ Table: UniqueXID [required] Class: org.opengts.db.tables.UniqueXID This table defines system-wide mapping of Transport Unique-IDs to a specific Account/Transport. ## Column Description SQL Type Key --- ---------------------- ------------------------------ ---------------------------- -------- 1 uniqueID Unique ID VARCHAR(40) PRIMARY 2 accountID Account ID VARCHAR(32) 3 transportID Transport ID VARCHAR(32) ================================================================================================ Table: DeviceGroup [required] Class: org.opengts.db.tables.DeviceGroup This table defines Account specific Device Groups. ## Column Description SQL Type Key --- ---------------------- ------------------------------ ---------------------------- -------- 1 accountID Account ID VARCHAR(32) PRIMARY 2 groupID Device Group ID VARCHAR(32) PRIMARY 3 displayName Display Name VARCHAR(40) [utf8] 4 description Description VARCHAR(128) [utf8] 5 notes Notes TEXT [utf8] 6 lastUpdateTime Last Update Time INT UNSIGNED 7 creationTime Creation Time INT UNSIGNED ================================================================================================ Table: DeviceList [required] Class: org.opengts.db.tables.DeviceList This table defines the membership of a given Device within a DeviceGroup. A Device may be defined in more than one DeviceGroup. ## Column Description SQL Type Key --- ---------------------- ------------------------------ ---------------------------- -------- 1 accountID Account ID VARCHAR(32) PRIMARY 2 groupID Device Group ID VARCHAR(32) PRIMARY 3 deviceID Device/Asset ID VARCHAR(32) PRIMARY 4 lastUpdateTime Last Update Time INT UNSIGNED 5 creationTime Creation Time INT UNSIGNED ================================================================================================ Table: Driver [required] Class: org.opengts.db.tables.Driver This table defines Account specific Vehicle Drivers. ## Column Description SQL Type Key --- ---------------------- ------------------------------ ---------------------------- -------- 1 accountID Account ID VARCHAR(32) PRIMARY 2 driverID Driver ID VARCHAR(32) PRIMARY 3 contactPhone Contact Phone VARCHAR(32) 4 contactEmail Contact EMail VARCHAR(128) 5 licenseType License Type VARCHAR(24) 6 licenseNumber License Number VARCHAR(32) 7 licenseExpire License Expiration Day INT UNSIGNED 8 badgeID Badge ID VARCHAR(32) 9 address Full Address VARCHAR(90) [utf8] 10 birthdate Driver Birthdate INT UNSIGNED 11 deviceID Device/Asset ID VARCHAR(32) 12 driverStatus Driver/Duty Status INT UNSIGNED 13 displayName Display Name VARCHAR(40) [utf8] 14 description Description VARCHAR(128) [utf8] 15 notes Notes TEXT [utf8] 16 lastUpdateTime Last Update Time INT UNSIGNED 17 creationTime Creation Time INT UNSIGNED ================================================================================================ Table: EventData [required] Class: org.opengts.db.tables.EventData This table contains events which have been generated by all client devices. ## Column Description SQL Type Key --- ---------------------- ------------------------------ ---------------------------- -------- 1 accountID Account ID VARCHAR(32) PRIMARY 2 deviceID Device/Asset ID VARCHAR(32) PRIMARY 3 timestamp Timestamp INT UNSIGNED PRIMARY 4 statusCode Status Code INT UNSIGNED PRIMARY 5 latitude Latitude DOUBLE 6 longitude Longitude DOUBLE 7 gpsAge GPS Fix Age INT UNSIGNED 8 speedKPH Speed DOUBLE 9 heading Heading DOUBLE 10 altitude Altitude DOUBLE 11 transportID Transport ID VARCHAR(32) 12 inputMask Input Mask INT UNSIGNED 13 outputMask Output Mask INT UNSIGNED 14 seatbeltMask Seatbelt Mask INT UNSIGNED 15 address Full Address VARCHAR(90) [utf8] 16 dataSource Data Source VARCHAR(32) 17 rawData Raw Data TEXT 18 distanceKM Distance KM DOUBLE 19 odometerKM Odometer Offset KM DOUBLE 20 odometerOffsetKM Odometer KM DOUBLE 21 geozoneIndex Geozone Index INT UNSIGNED 22 geozoneID Geozone ID VARCHAR(32) 23 creationTime Creation Time INT UNSIGNED ================================================================================================ Table: Geozone [required] Class: org.opengts.db.tables.Geozone This table defines Account specific geozones/geofences. ## Column Description SQL Type Key --- ---------------------- ------------------------------ ---------------------------- -------- 1 accountID Account ID VARCHAR(32) PRIMARY 2 geozoneID Geozone ID VARCHAR(32) PRIMARY 3 sortID Sort ID INT UNSIGNED PRIMARY 4 minLatitude Min Latitude DOUBLE bounds 5 maxLatitude Max Latitude DOUBLE bounds 6 minLongitude Min Longitude DOUBLE bounds 7 maxLongitude Max Longitude DOUBLE bounds 8 zonePurposeID Purpose ID VARCHAR(32) 9 reverseGeocode Reverse geocode TINYINT 10 arrivalZone Arrival Zone TINYINT 11 departureZone Departure Zone TINYINT 12 autoNotify Auto Notify TINYINT 13 zoomRegion Zoom Region TINYINT 14 shapeColor Shape Color VARCHAR(12) 15 zoneType Zone Type TINYINT UNSIGNED 16 radius Radius Meters INT UNSIGNED 17 latitude1 Latitude 1 DOUBLE 18 longitude1 Longitude 1 DOUBLE 19 latitude2 Latitude 2 DOUBLE 20 longitude2 Longitude 2 DOUBLE 21 latitude3 Latitude 3 DOUBLE 22 longitude3 Longitude 3 DOUBLE 23 latitude4 Latitude 4 DOUBLE 24 longitude4 Longitude 4 DOUBLE 25 latitude5 Latitude 5 DOUBLE 26 longitude5 Longitude 5 DOUBLE 27 latitude6 Latitude 6 DOUBLE 28 longitude6 Longitude 6 DOUBLE 29 latitude7 Latitude 7 DOUBLE 30 longitude7 Longitude 7 DOUBLE 31 latitude8 Latitude 8 DOUBLE 32 longitude8 Longitude 8 DOUBLE 33 latitude9 Latitude 9 DOUBLE 34 longitude9 Longitude 9 DOUBLE 35 latitude10 Latitude 10 DOUBLE 36 longitude10 Longitude 10 DOUBLE 37 clientUpload Client Upload TINYINT 38 clientID Client ID INT UNSIGNED altIndex 39 groupID DeviceGroup VARCHAR(32) 40 streetAddress Street Address VARCHAR(90) [utf8] 41 city City VARCHAR(40) [utf8] 42 stateProvince State/Province VARCHAR(40) [utf8] 43 postalCode Postal Code VARCHAR(16) [utf8] 44 country Country VARCHAR(40) [utf8] 45 subdivision Subdivision VARCHAR(32) [utf8] 46 displayName Display Name VARCHAR(40) [utf8] 47 description Description VARCHAR(128) [utf8] 48 lastUpdateTime Last Update Time INT UNSIGNED 49 creationTime Creation Time INT UNSIGNED ================================================================================================ Table: Resource [required] Class: org.opengts.db.tables.Resource This table defines Account specific text resources. ## Column Description SQL Type Key --- ---------------------- ------------------------------ ---------------------------- -------- 1 accountID Account ID VARCHAR(32) PRIMARY 2 resourceID Resource ID VARCHAR(80) PRIMARY 3 type Type VARCHAR(16) 4 title Title VARCHAR(70) [utf8] 5 properties Properties TEXT 6 value Value BLOB 7 displayName Display Name VARCHAR(40) [utf8] 8 description Description VARCHAR(128) [utf8] 9 lastUpdateTime Last Update Time INT UNSIGNED 10 creationTime Creation Time INT UNSIGNED ================================================================================================ Table: Role [required] Class: org.opengts.db.tables.Role This table defines Account specific Roles. ## Column Description SQL Type Key --- ---------------------- ------------------------------ ---------------------------- -------- 1 accountID Account ID VARCHAR(32) PRIMARY 2 roleID Role ID VARCHAR(32) PRIMARY 3 displayName Display Name VARCHAR(40) [utf8] 4 description Description VARCHAR(128) [utf8] 5 notes Notes TEXT [utf8] 6 lastUpdateTime Last Update Time INT UNSIGNED 7 creationTime Creation Time INT UNSIGNED ================================================================================================ Table: RoleAcl [required] Class: org.opengts.db.tables.RoleAcl This table defines Role specific Access Control permissions. ## Column Description SQL Type Key --- ---------------------- ------------------------------ ---------------------------- -------- 1 accountID Account ID VARCHAR(32) PRIMARY 2 roleID Role ID VARCHAR(32) PRIMARY 3 aclID ACL ID VARCHAR(64) PRIMARY 4 accessLevel Access Level SMALLINT UNSIGNED 5 description Description VARCHAR(128) [utf8] 6 lastUpdateTime Last Update Time INT UNSIGNED 7 creationTime Creation Time INT UNSIGNED ================================================================================================ Table: StatusCode [required] Class: org.opengts.db.tables.StatusCode This table defines Device specific StatusCode descriptions. ## Column Description SQL Type Key --- ---------------------- ------------------------------ ---------------------------- -------- 1 accountID Account ID VARCHAR(32) PRIMARY 2 deviceID Device/Asset ID VARCHAR(32) PRIMARY 3 statusCode Status Code INT UNSIGNED PRIMARY 4 statusName Status Name VARCHAR(18) 5 foregroundColor Foreground Color VARCHAR(10) 6 backgroundColor Background Color VARCHAR(10) 7 iconSelector Icon Selector VARCHAR(128) 8 iconName Icon Name VARCHAR(24) 9 description Description VARCHAR(128) [utf8] 10 lastUpdateTime Last Update Time INT UNSIGNED 11 creationTime Creation Time INT UNSIGNED ================================================================================================ Table: SystemProps [required] Class: org.opengts.db.tables.SystemProps This table defines system-wide installation property key/values. ## Column Description SQL Type Key --- ---------------------- ------------------------------ ---------------------------- -------- 1 propertyID Property ID VARCHAR(32) PRIMARY 2 value Value TEXT [utf8] 3 description Description VARCHAR(128) [utf8] 4 lastUpdateTime Last Update Time INT UNSIGNED 5 creationTime Creation Time INT UNSIGNED ================================================================================================ Table: EventTemplate [optional] Class: org.opengts.db.dmtp.EventTemplate This table contains DMTP event packet 'template's (Custom Event Packet Negotiation parse templates) which have been received from client devices. ## Column Description SQL Type Key --- ---------------------- ------------------------------ ---------------------------- -------- 1 accountID Account ID VARCHAR(32) PRIMARY 2 deviceID Device/Asset ID VARCHAR(32) PRIMARY 3 customType Custom Type TINYINT UNSIGNED PRIMARY 4 repeatLast Repeat Last TINYINT 5 template Template TEXT ================================================================================================ Table: PendingPacket [optional] Class: org.opengts.db.dmtp.PendingPacket This table contains configuration packets which are to be sent to the DMTP client device the next time it 'checks-in' with the server. ## Column Description SQL Type Key --- ---------------------- ------------------------------ ---------------------------- -------- 1 accountID Account ID VARCHAR(32) PRIMARY 2 deviceID Device/Asset ID VARCHAR(32) PRIMARY 3 queueTime Packet Queue Time INT UNSIGNED PRIMARY 4 sequence Sequence SMALLINT UNSIGNED PRIMARY 5 packetBytes Packet Bytes MEDIUMBLOB 6 autoDelete Delete after sending TINYINT ================================================================================================ Table: Property [optional] Class: org.opengts.db.dmtp.Property This table contains Device specific property information collected from client devices. ## Column Description SQL Type Key --- ---------------------- ------------------------------ ---------------------------- -------- 1 accountID Account ID VARCHAR(32) PRIMARY 2 deviceID Device/Asset ID VARCHAR(32) PRIMARY 3 propKey Property Key INT UNSIGNED PRIMARY 4 timestamp Timestamp INT UNSIGNED 5 binaryValue Binary Value BLOB ================================================================================================ Table: Diagnostic [optional] Class: org.opengts.db.dmtp.Diagnostic This table contains Device specific diagnostic information collected from client devices. ## Column Description SQL Type Key --- ---------------------- ------------------------------ ---------------------------- -------- 1 accountID Account ID VARCHAR(32) PRIMARY 2 deviceID Device/Asset ID VARCHAR(32) PRIMARY 3 isError Is Error TINYINT PRIMARY 4 codeKey Code Key INT UNSIGNED PRIMARY 5 timestamp Timestamp INT UNSIGNED PRIMARY 6 binaryValue Binary Value BLOB