================================================================================================ 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 speedUnits Speed Units TINYINT UNSIGNED 5 distanceUnits Distance Units TINYINT UNSIGNED 6 volumeUnits Volume Units TINYINT UNSIGNED 7 pressureUnits Pressure Units TINYINT UNSIGNED 8 economyUnits Economy Units TINYINT UNSIGNED 9 temperatureUnits Temperature Units TINYINT UNSIGNED 10 latLonFormat Latitude/Longitude Format TINYINT UNSIGNED 11 geocoderMode Geocoder Mode TINYINT UNSIGNED 12 privateLabelName PrivateLabel Name VARCHAR(32) 13 isBorderCrossing Is BorderCrossing Enabled TINYINT 14 retainedEventAge Retained Event Age (sec) INT UNSIGNED 15 autoAddDevices AutoAdd Devices TINYINT 16 expirationTime Expiration Time INT UNSIGNED 17 defaultUser Default User ID VARCHAR(32) 18 password Password VARCHAR(32) 19 contactName Contact Name VARCHAR(64) [utf8] 20 contactPhone Contact Phone VARCHAR(32) 21 contactEmail Contact EMail Address VARCHAR(128) email 22 timeZone Time Zone VARCHAR(32) 23 passwdQueryTime Last Password Query Time INT UNSIGNED 24 lastLoginTime Last Login Time INT UNSIGNED 25 isActive Is Active TINYINT 26 displayName Display Name VARCHAR(40) [utf8] 27 description Description VARCHAR(128) [utf8] 28 notes Notes TEXT [utf8] 29 lastUpdateTime Last Update Time INT UNSIGNED 30 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 contactName Contact Name VARCHAR(64) [utf8] 8 contactPhone Contact Phone VARCHAR(32) 9 contactEmail Contact EMail Address VARCHAR(64) email 10 timeZone Time Zone VARCHAR(32) 11 firstLoginPageID First Login Page ID VARCHAR(24) 12 preferredDeviceID Preferred Device ID VARCHAR(32) 13 maxAccessLevel Maximum Access Level SMALLINT UNSIGNED 14 passwdQueryTime Last Password Query Time INT UNSIGNED 15 lastLoginTime Last Login Time INT UNSIGNED 16 isActive Is Active TINYINT 17 displayName Display Name VARCHAR(40) [utf8] 18 description Description VARCHAR(128) [utf8] 19 notes Notes TEXT [utf8] 20 lastUpdateTime Last Update Time INT UNSIGNED 21 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 vehicleID VIN VARCHAR(24) 6 licensePlate License Plate VARCHAR(24) 7 driverID Driver ID VARCHAR(32) 8 fuelCapacity Fuel Capacity DOUBLE 9 expirationTime Expiration Time INT UNSIGNED 10 uniqueID Unique ID VARCHAR(40) altIndex 11 deviceCode Server ID VARCHAR(24) 12 deviceType Device Type VARCHAR(24) 13 pushpinID Pushpin ID VARCHAR(32) 14 displayColor Display Color VARCHAR(16) 15 serialNumber Serial Number VARCHAR(24) 16 simPhoneNumber SIM Phone Number VARCHAR(24) 17 smsEmail SMS EMail Address VARCHAR(64) 18 imeiNumber IMEI Number VARCHAR(24) 19 dataKey Data Key TEXT 20 ignitionIndex Ignition I/O Index SMALLINT 21 codeVersion Code Version VARCHAR(32) 22 featureSet Feature Set VARCHAR(64) 23 ipAddressValid Valid IP Addresses VARCHAR(128) 24 lastTotalConnectTime Last Total Connect Time INT UNSIGNED 25 lastDuplexConnectTime Last Duplex Connect Time INT UNSIGNED 26 pendingPingCommand Pending Ping Command TEXT 27 lastPingTime Last 'Ping' Time INT UNSIGNED 28 totalPingCount Total 'Ping' Count SMALLINT UNSIGNED 29 maxPingCount Maximum 'Ping' Count SMALLINT UNSIGNED 30 expectAck Expecting an ACK TINYINT 31 lastAckCommand Last Command Expecting an ACK TEXT 32 lastAckTime Last Received 'ACK' Time INT UNSIGNED 33 dcsConfigMask DCS Configuration Mask INT UNSIGNED 34 supportsDMTP Supports DMTP TINYINT 35 supportedEncodings Supported Encodings TINYINT UNSIGNED 36 unitLimitInterval Accounting Time Interval Min SMALLINT UNSIGNED 37 maxAllowedEvents Max Events per Interval SMALLINT UNSIGNED 38 totalProfileMask Total Profile Mask BLOB 39 totalMaxConn Max Total Conn per Interval SMALLINT UNSIGNED 40 totalMaxConnPerMin Max Total Conn per Minute SMALLINT UNSIGNED 41 duplexProfileMask Duplex Profile Mask BLOB 42 duplexMaxConn Max Duplex Conn per Interval SMALLINT UNSIGNED 43 duplexMaxConnPerMin Max Duplex Conn per Minute SMALLINT UNSIGNED 44 ipAddressCurrent Current IP Address VARCHAR(32) 45 remotePortCurrent Current Remote Port SMALLINT UNSIGNED 46 listenPortCurrent Current Listen Port SMALLINT UNSIGNED 47 lastInputState Last Input State INT UNSIGNED 48 lastBatteryLevel Last Battery Level DOUBLE 49 lastValidLatitude Last Valid Latitude DOUBLE 50 lastValidLongitude Last Valid Longitude DOUBLE 51 lastGPSTimestamp Last Valid GPS Timestamp INT UNSIGNED 52 lastOdometerKM Last Odometer km DOUBLE 53 odometerOffsetKM Odometer Offset km DOUBLE 54 isActive Is Active TINYINT 55 displayName Display Name VARCHAR(40) [utf8] 56 description Description VARCHAR(128) [utf8] 57 notes Notes TEXT [utf8] 58 lastUpdateTime Last Update Time INT UNSIGNED 59 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 ignitionIndex Ignition I/O Index SMALLINT UNSIGNED 14 codeVersion Code Version VARCHAR(32) 15 featureSet Feature Set VARCHAR(64) 16 ipAddressValid Valid IP Addresses VARCHAR(128) 17 ipAddressCurrent Current IP Address VARCHAR(32) 18 remotePortCurrent Current Remote Port SMALLINT UNSIGNED 19 pendingPingCommand Pending Ping Command TEXT 20 lastPingTime Last 'Ping' Time INT UNSIGNED 21 totalPingCount Total 'Ping' Count SMALLINT UNSIGNED 22 maxPingCount Maximum 'Ping' Count SMALLINT UNSIGNED 23 expectAck Expecting an ACK TINYINT 24 lastAckCommand Last Command Expecting an ACK TEXT 25 lastAckTime Last Received 'ACK' Time INT UNSIGNED 26 supportsDMTP Supports DMTP TINYINT 27 supportedEncodings Supported Encodings TINYINT UNSIGNED 28 unitLimitInterval Accounting Time Interval Min SMALLINT UNSIGNED 29 maxAllowedEvents Max Events per Interval SMALLINT UNSIGNED 30 totalProfileMask Total Profile Mask BLOB 31 totalMaxConn Max Total Conn per Interval SMALLINT UNSIGNED 32 totalMaxConnPerMin Max Total Conn per Minute SMALLINT UNSIGNED 33 duplexProfileMask Duplex Profile Mask BLOB 34 duplexMaxConn Max Duplex Conn per Interval SMALLINT UNSIGNED 35 duplexMaxConnPerMin Max Duplex Conn per Minute SMALLINT UNSIGNED 36 lastTotalConnectTime Last Total Connect Time INT UNSIGNED 37 lastDuplexConnectTime Last Duplex Connect Time INT UNSIGNED 38 displayName Display Name VARCHAR(40) [utf8] 39 description Description VARCHAR(128) [utf8] 40 lastUpdateTime Last Update Time INT UNSIGNED 41 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 displayName Display Name VARCHAR(40) [utf8] 12 description Description VARCHAR(128) [utf8] 13 notes Notes TEXT [utf8] 14 lastUpdateTime Last Update Time INT UNSIGNED 15 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 KPH DOUBLE 9 heading Heading Degrees DOUBLE 10 altitude Altitude Meters DOUBLE 11 transportID Transport ID VARCHAR(32) 12 inputMask Input Mask INT UNSIGNED 13 address Full Address VARCHAR(90) [utf8] 14 dataSource Data Source VARCHAR(32) 15 rawData Raw Data TEXT 16 distanceKM Distance KM DOUBLE 17 odometerKM Odometer KM DOUBLE 18 geozoneIndex Geozone Index INT UNSIGNED 19 geozoneID Geozone ID VARCHAR(32) 20 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 reverseGeocode Reverse geocode TINYINT 9 arrivalZone Arrival Zone TINYINT 10 departureZone Departure Zone TINYINT 11 zoomRegion Zoom Region TINYINT 12 shapeColor Shape Color VARCHAR(12) 13 zoneType Zone Type TINYINT UNSIGNED 14 radius Radius Meters INT UNSIGNED 15 latitude1 Latitude 1 DOUBLE 16 longitude1 Longitude 1 DOUBLE 17 latitude2 Latitude 2 DOUBLE 18 longitude2 Longitude 2 DOUBLE 19 latitude3 Latitude 3 DOUBLE 20 longitude3 Longitude 3 DOUBLE 21 latitude4 Latitude 4 DOUBLE 22 longitude4 Longitude 4 DOUBLE 23 latitude5 Latitude 5 DOUBLE 24 longitude5 Longitude 5 DOUBLE 25 latitude6 Latitude 6 DOUBLE 26 longitude6 Longitude 6 DOUBLE 27 clientUpload Client Upload TINYINT 28 clientID Client ID INT UNSIGNED altIndex 29 streetAddress Street Address VARCHAR(90) [utf8] 30 city City VARCHAR(40) [utf8] 31 stateProvince State/Province VARCHAR(40) [utf8] 32 postalCode Postal Code VARCHAR(16) [utf8] 33 country Country VARCHAR(40) [utf8] 34 subdivision Subdivision VARCHAR(32) [utf8] 35 displayName Display Name VARCHAR(40) [utf8] 36 description Description VARCHAR(128) [utf8] 37 lastUpdateTime Last Update Time INT UNSIGNED 38 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(64) PRIMARY 3 title Title VARCHAR(70) [utf8] 4 type Type VARCHAR(16) 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 iconSelector Icon Selector VARCHAR(128) 6 iconName Icon Name VARCHAR(24) 7 description Description VARCHAR(128) [utf8] 8 lastUpdateTime Last Update Time INT UNSIGNED 9 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 [required] 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 [required] 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 [required] 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 [required] 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