Version: 2.6.5 ================================================================================================ 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 currencyUnits Currency Units VARCHAR(8) 12 fuelCostPerLiter Fuel Cost Per Liter DOUBLE 13 latLonFormat Latitude/Longitude Format TINYINT UNSIGNED 14 geocoderMode Geocoder Mode TINYINT UNSIGNED 15 privateLabelName PrivateLabel Name VARCHAR(32) 16 isBorderCrossing BorderCrossing Enabled TINYINT 17 retainedEventAge Retained Event Age (sec) INT UNSIGNED 18 maximumDevices Maximum number of devices INT 19 totalPingCount Total 'Ping' Count SMALLINT UNSIGNED 20 maxPingCount Maximum 'Ping' Count SMALLINT UNSIGNED 21 autoAddDevices AutoAdd Devices TINYINT 22 dcsPropertiesID DCS Properties ID VARCHAR(32) 23 smsEnabled SMS Enabled TINYINT 24 smsProperties SMS Properties VARCHAR(400) 25 smtpProperties SMTP Properties VARCHAR(400) 26 expirationTime Expiration Time INT UNSIGNED 27 suspendUntilTime Suspend Until Time INT UNSIGNED 28 allowWebService Allow Web-Service TINYINT 29 defaultUser Default User ID VARCHAR(32) 30 password Password VARCHAR(32) 31 tempPassword Temporary Password VARCHAR(32) 32 lastPasswords Prior Passwords VARCHAR(300) 33 contactName Contact Name VARCHAR(64) [utf8] 34 contactPhone Contact Phone VARCHAR(32) 35 contactEmail Contact EMail Address VARCHAR(128) email 36 timeZone Time Zone VARCHAR(32) 37 preferDateFormat Date Format VARCHAR(16) 38 preferTimeFormat Time Format VARCHAR(16) 39 passwdChangeTime Last Password Change Time INT UNSIGNED 40 passwdQueryTime Last Password Query Time INT UNSIGNED 41 lastLoginTime Last Login Time INT UNSIGNED 42 isActive Is Active TINYINT 43 displayName Display Name VARCHAR(40) [utf8] 44 description Description VARCHAR(128) [utf8] 45 notes Notes TEXT [utf8] 46 lastUpdateTime Last Update Time INT UNSIGNED 47 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 tempPassword Temporary Password VARCHAR(32) 7 lastPasswords Prior Passwords VARCHAR(300) 8 gender Gender TINYINT UNSIGNED 9 notifyEmail Notification EMail Address VARCHAR(128) 10 contactName Contact Name VARCHAR(64) [utf8] 11 contactPhone Contact Phone VARCHAR(32) 12 contactEmail Contact EMail Address VARCHAR(64) email 13 timeZone Time Zone VARCHAR(32) 14 firstLoginPageID First Login Page ID VARCHAR(24) 15 preferredDeviceID Preferred Device ID VARCHAR(32) 16 maxAccessLevel Maximum Access Level SMALLINT UNSIGNED 17 passwdChangeTime Last Password Change Time INT UNSIGNED 18 passwdQueryTime Last Password Query Time INT UNSIGNED 19 expirationTime Expiration Time INT UNSIGNED 20 suspendUntilTime Suspend Until Time INT UNSIGNED 21 lastLoginTime Last Login Time INT UNSIGNED 22 isActive Is Active TINYINT 23 displayName Display Name VARCHAR(40) [utf8] 24 description Description VARCHAR(128) [utf8] 25 notes Notes TEXT [utf8] 26 lastUpdateTime Last Update Time INT UNSIGNED 27 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 sequence Sequence INT 5 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 equipmentStatus Equipment Status VARCHAR(24) 6 vehicleMake Vehicle Make VARCHAR(40) 7 vehicleModel Vehicle Model VARCHAR(40) 8 vehicleColor Vehicle Color VARCHAR(20) 9 vehicleYear Vehicle Year SMALLINT UNSIGNED 10 vehicleID VIN VARCHAR(24) 11 licensePlate License Plate VARCHAR(24) 12 licenseExpire License Expiration Day INT UNSIGNED 13 insuranceExpire Insurance Expiration Day INT UNSIGNED 14 driverID Driver ID VARCHAR(32) 15 driverStatus Driver Status INT UNSIGNED 16 fuelCapacity Fuel Capacity DOUBLE 17 fuelCapacity2 Fuel Capacity DOUBLE 18 fuelEconomy Approx. Fuel Economy DOUBLE 19 fuelRatePerHour Approx. Fuel Rate per Hour DOUBLE 20 fuelCostPerLiter Approx. Fuel Cost per Liter DOUBLE 21 fuelTankProfile Fuel Tank Profile VARCHAR(320) 22 fuelTankProfile2 Fuel Tank Profile VARCHAR(320) 23 speedLimitKPH Max Speed km/h DOUBLE 24 maximumRpm Max RPM INT UNSIGNED 25 planDistanceKM Planned Trip Distance DOUBLE 26 installTime Install Time INT UNSIGNED 27 resetTime Reset Time INT UNSIGNED 28 expirationTime Expiration Time INT UNSIGNED 29 uniqueID Unique ID VARCHAR(40) altIndex 30 deviceCode Server ID VARCHAR(24) 31 deviceType Device Type VARCHAR(24) 32 pushpinID Pushpin ID VARCHAR(32) 33 displayColor Display Color VARCHAR(16) 34 serialNumber Serial Number VARCHAR(24) 35 simPhoneNumber SIM Phone Number VARCHAR(24) 36 simID SIM ID VARCHAR(24) 37 smsEmail SMS EMail Address VARCHAR(64) 38 imeiNumber IMEI Number VARCHAR(24) 39 dataKey Data Key TEXT 40 ignitionIndex Ignition I/O Index SMALLINT 41 codeVersion Code Version VARCHAR(32) 42 featureSet Feature Set VARCHAR(64) 43 ipAddressValid Valid IP Addresses VARCHAR(128) 44 lastTotalConnectTime Last Total Connect Time INT UNSIGNED 45 lastDuplexConnectTime Last Duplex Connect Time INT UNSIGNED 46 pendingPingCommand Pending Ping Command TEXT 47 lastPingTime Last 'Ping' Time INT UNSIGNED 48 totalPingCount Total 'Ping' Count SMALLINT UNSIGNED 49 maxPingCount Maximum 'Ping' Count SMALLINT UNSIGNED 50 commandStateMask Command State Mask INT UNSIGNED 51 expectAck Expecting an ACK TINYINT 52 expectAckCode Expected ACK Status Code INT UNSIGNED 53 lastAckCommand Last Command Expecting ACK TEXT 54 lastAckTime Last Received 'ACK' Time INT UNSIGNED 55 dcsPropertiesID DCS Properties ID VARCHAR(32) 56 dcsConfigMask DCS Configuration Mask INT UNSIGNED 57 dcsConfigString DCS Configuration String VARCHAR(80) 58 dcsCommandHost DCS Command Host VARCHAR(32) 59 dcsCommandState Command State VARCHAR(64) 60 fixedTcpSessionID Fixed TCP Session ID VARCHAR(32) 61 lastTcpSessionID Last TCP Session ID VARCHAR(32) 62 ipAddressCurrent Current IP Address VARCHAR(32) 63 remotePortCurrent Current Remote Port SMALLINT UNSIGNED 64 listenPortCurrent Current Listen Port SMALLINT UNSIGNED 65 lastInputState Last Input State INT UNSIGNED 66 lastOutputState Last Output State INT UNSIGNED 67 statusCodeState StatusCode On/Off State INT UNSIGNED 68 lastBatteryLevel Last Internal Battery Level DOUBLE 69 lastBatteryVolts Last Internal Battery Volts DOUBLE 70 lastVBatteryVolts Last Vehicle Battery Volts DOUBLE 71 lastFuelLevel Last Fuel Level DOUBLE 72 lastFuelLevel2 Last Fuel Level #2 DOUBLE 73 lastFuelTotal Last Fuel Total Liters DOUBLE 74 lastOilLevel Last Oil Level DOUBLE 75 lastValidLatitude Last Valid Latitude DOUBLE 76 lastValidLongitude Last Valid Longitude DOUBLE 77 lastValidHeading Last Valid Heading DOUBLE 78 lastValidSpeedKPH Last Valid SpeedKPH DOUBLE 79 lastGPSTimestamp Last Valid GPS Timestamp INT UNSIGNED 80 lastEventTimestamp Last Event Timestamp INT UNSIGNED 81 lastEventStatusCode Last Event StatusCode INT UNSIGNED 82 lastCellServingInfo Last Serving Cell Info VARCHAR(100) 83 lastDistanceKM Last Distance km DOUBLE 84 lastOdometerKM Last Odometer km DOUBLE 85 odometerOffsetKM Odometer Offset km DOUBLE 86 lastEngineOnHours Last Engine On Hours DOUBLE 87 lastEngineOnTime Last Engine On Time INT UNSIGNED 88 lastEngineOffTime Last Engine Off Time INT UNSIGNED 89 lastEngineHours Last Engine Hours DOUBLE 90 engineHoursOffset Engine Hours Offset DOUBLE 91 lastIgnitionOnHours Last Ignition On Hours DOUBLE 92 lastIgnitionOnTime Last Ignition On Time INT UNSIGNED 93 lastIgnitionOffTime Last Ignition Off Time INT UNSIGNED 94 lastIgnitionHours Last Ignition Hours DOUBLE 95 lastStopTime Last Stop Time INT UNSIGNED 96 lastStartTime Last Start Time INT UNSIGNED 97 lastMalfunctionLamp Last MIL TINYINT 98 lastFaultCode Last Fault Code VARCHAR(96) 99 lastPtoOnHours Last PTO On Hours DOUBLE 100 lastPtoOnTime Last PTO On Time INT UNSIGNED 101 lastPtoOffTime Last PTO Off Time INT UNSIGNED 102 lastPtoHours Last PTO Hours DOUBLE 103 isActive Is Active TINYINT 104 displayName Display Name VARCHAR(40) [utf8] 105 description Description VARCHAR(128) [utf8] 106 notes Notes TEXT [utf8] 107 lastUpdateTime Last Update Time INT UNSIGNED 108 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 cardID Card ID VARCHAR(32) card 10 address Full Address VARCHAR(90) [utf8] 11 birthdate Driver Birthdate INT UNSIGNED 12 deviceID Device/Asset ID VARCHAR(32) 13 driverStatus Driver Status INT UNSIGNED 14 dutyStatus Duty Status SMALLINT 15 displayName Display Name VARCHAR(40) [utf8] 16 description Description VARCHAR(128) [utf8] 17 notes Notes TEXT [utf8] 18 lastUpdateTime Last Update Time INT UNSIGNED 19 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 ignitionState Ignition State TINYINT 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 KM DOUBLE 20 odometerOffsetKM Odometer Offset 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 arrivalStatusCode Arrival Code INT UNSIGNED 12 departureZone Departure Zone TINYINT 13 departureStatusCode Departure Code INT UNSIGNED 14 autoNotify Auto Notify TINYINT 15 zoomRegion Zoom Region TINYINT 16 shapeColor Shape Color VARCHAR(12) 17 iconName Pushpin ID VARCHAR(24) 18 zoneType Zone Type TINYINT UNSIGNED 19 radius Radius Meters INT UNSIGNED 20 vertices Vertices TEXT 21 latitude1 Latitude 1 DOUBLE 22 longitude1 Longitude 1 DOUBLE 23 latitude2 Latitude 2 DOUBLE 24 longitude2 Longitude 2 DOUBLE 25 latitude3 Latitude 3 DOUBLE 26 longitude3 Longitude 3 DOUBLE 27 latitude4 Latitude 4 DOUBLE 28 longitude4 Longitude 4 DOUBLE 29 latitude5 Latitude 5 DOUBLE 30 longitude5 Longitude 5 DOUBLE 31 latitude6 Latitude 6 DOUBLE 32 longitude6 Longitude 6 DOUBLE 33 latitude7 Latitude 7 DOUBLE 34 longitude7 Longitude 7 DOUBLE 35 latitude8 Latitude 8 DOUBLE 36 longitude8 Longitude 8 DOUBLE 37 latitude9 Latitude 9 DOUBLE 38 longitude9 Longitude 9 DOUBLE 39 latitude10 Latitude 10 DOUBLE 40 longitude10 Longitude 10 DOUBLE 41 clientUpload Client Upload TINYINT 42 clientID Client ID INT UNSIGNED altIndex 43 groupID DeviceGroup VARCHAR(32) 44 streetAddress Street Address VARCHAR(90) [utf8] 45 city City VARCHAR(40) [utf8] 46 stateProvince State/Province VARCHAR(40) [utf8] 47 postalCode Postal Code VARCHAR(16) [utf8] 48 country Country VARCHAR(40) [utf8] 49 subdivision Subdivision VARCHAR(32) [utf8] 50 contactPhone Contact Phone VARCHAR(32) 51 isActive Is Active TINYINT 52 displayName Display Name VARCHAR(40) [utf8] 53 description Description VARCHAR(128) [utf8] 54 lastUpdateTime Last Update Time INT UNSIGNED 55 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(64) PRIMARY 2 dataType Data Type VARCHAR(80) 3 value Value TEXT [utf8] 4 description Description VARCHAR(128) [utf8] 5 lastUpdateTime Last Update Time INT UNSIGNED 6 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