-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathSCHEMA.txt
667 lines (572 loc) · 50.2 KB
/
SCHEMA.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
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