Table of Contents
Access Server Database Schema
The Access Server contains two database schemas. One for the “master” database and one for each “customer” database.
Master Database
The Access Server has one master database that contains all data that needs to be accessed without knowing the customer context. This includes the list of customers, the list of users, TMM configuration and device assignment. All data that belongs to a specific customer is placed in the customer database. The list of users may seem like customer specific data but until a user authenticates we don't know what customer they belong to so we need the list in the master database. Devices are similar in that even though they are assigned to customers, we don't know which customer ntil the device connects to the server and a lookup is performed in the master database.
Customer Table (customer)
The customer table contains the list of all customers supported by the Access Server.
| Column | Description | Comments |
|---|---|---|
| handle | An integer value that uniquely identifies the customer | |
| id | The human readable customer id | We should use the customer id in the Customer Names MSAccess database |
| description | The customer company name | |
| dbhost | The host name of the computer containing the customer database | |
| dbname | The name of the customer database | Since this name has to be unique by default we should just use the customer id |
| storepath | The file system path to the directory to store the GPS data | The directory must be on the local machine (it can be NFS mounted) |
| status | The customer status | Not exactly sure what values will be in here |
User Table (user)
The user table contains the list all of the users across all the customers. This table is placed in the master database rather than having an individual user table for each customer because we use the user id to look up the customer context during authentication.
| Column | Description | Comments |
|---|---|---|
| handle | An integer value that uniquely identifies the user | |
| customer | The handle of the customer the user is associated with | |
| id | The human readable user id | To guarantee this is unique we should use the user's e-mail address |
| admin | A flag to indicate the user has admin privileges | |
| authtype | The authentication type | Currently we support authenticating through Box.net or locally |
| token | The authentication token | If authtype is Box.net this is the Box.net token, if authtype is local this is an MD5 encoded password |
Device Table (device)
The device table contains the list of all known GPS devices that upload without authenticating. When the device connects, this table is used to determine which customer the GPS data belongs to.
| Column | Description | Comments |
|---|---|---|
| handle | An integer value that uniquely identifies the device | |
| customer | The handle of the customer the device is assigned to | |
| serial | A globally unique serial number assigned to the device | I have changed this field to be variable length up to 32 characters long |
TMM Configuration Table (tmm)
The TMM configuration table stores the TMM parameter block data returned after a successful upload. This data is used to control TMM behavior including sample interval, upload host and port and firmware version. On the old Trackwork Server this was part of the modules table.
| Column | Description | Comments |
|---|---|---|
| serial | The TMM serial number | |
| use_defaults | Flag to indicate whether to use default configuration information | We have found in practice that this is problematic so this flag is almost always set to false (0) |
| sample_interval | GPS position sample interval in seconds | |
| upload_interval | GPS position upload interval in seconds | |
| tag_interval | ? | |
| a2d_interval | A2D sample interval in seconds | |
| motion_timeout | Device motion timeout in seconds | |
| firmware_version | Firmware version number | |
| hostname | Name of server to connect to | This value will probably never be changed with the Access Server |
| port | Server socket port to connect to | This value will probably never be changed with the Access Server |
Database Server Table (dbserver)
The database server table contains the list of servers that are running MySQL and are available to have new customers databases stored on them. Once a database server is at capacity it should be removed from the list. This table is really only used by the admin tools to determine what resources to assign to new customers.
| Column | Description | Comments |
|---|---|---|
| handle | An integer value that uniquely identifies the server | |
| host | The host name of the server |
File System Storage Table (storepath)
The file system storage table contains a list of directories that have space available for storing customer data AND are NFS mounted on all the machines running Access Server instances. Once a directory approaches capacity it should be removed from the list. This table is really only used by the admin tools to determine what resources to assign to new customers.
| Column | Description | Comments |
|---|---|---|
| handle | An integer value that uniquely identifies the directory | |
| directory | The full path to the local or NFS mounted directory |
Customer Database
The Access Server creates a customer database for each Agtek customer that subscribes to the Access product. All customer specific data is stored in the customer database. This includes project definitions, GPS tracks, vehicles, etc.
Asset ID Table (assetid)
The Asset ID table is used to create integer values that are unique for an individual customer. These values can then be used to create unique ids for assets such as vehicles. The table contains only one row, that of the last generated value.
| Column | Description | Comments |
|---|---|---|
| handle | A unique integer value |
Asset/Device Association Table (association)
The association table stores the list of times a GPS device is associated with an asset such as a vehicle.
| Column | Description | Comments |
|---|---|---|
| handle | An integer value that uniquely identifies the association | |
| serial | A device serial number | |
| assetid | An asseit id | |
| start_time | The time the association began | |
| end_time | The time the association ended |
GPS Statistics Table (gps)
The GPS statistics table maintains statistics for each GPS device the customer has received data from. This table was part of the modules table in the old Trackwork server.
| Column | Description | Comments |
|---|---|---|
| serial | The GPS serial number | This has been expanded to 32 characters instead of 8 |
| time | The last time the GPS connected to the server | |
| total_points | The total number of positions uploaded from the GPS | |
| last_lat | The last valid latitude received from the GPS | |
| last_lon | The last valid longitude received from the GPS | |
| last_alt | The last valid altitude received from the GPS | |
| last_time | The last valid time received from the GPS | |
| last_version | The last known version of the GPS firmware | Not all devices report firmware version |
| rejected_dop | The number of positions that have been rejected because the position accuracy was poor | |
| rejected_time | The number of positions that have been rejected because the position time was invalid | |
| cleared_time | The last time the statistics for this entry were cleared (0 if never cleared) |
RTK Statistics Table (rtk)
The RTK statistics table maintains statistics for each RTK GPS device the customer has received data from.
| Column | Description | Comments |
|---|---|---|
| serial | The GPS serial number | This has been expanded to 32 characters instead of 8 |
| time | The last time the RTK system connected to the server | |
| total_points | The total number of positions uploaded from the GPS | |
| last_Northing | The last valid Northing received from the GPS | |
| last_Easting | The last valid Easting received from the GPS | |
| last_Elev | The last valid elevation received from the GPS | |
| last_time | The last valid time received from the GPS | |
| cleared_time | The last time the statistics for this entry were cleared (0 if never cleared) |
Project Table (project)
The project table contains information including the physical boundaries of customer projects.
| Column | Description | Comments |
|---|---|---|
| handle | An integer value that uniquely identifies the project | |
| name | The name of the project | |
| description | A description of the project | |
| start_date | The project start date | |
| status | Project status | 0=Undefined, 1=New, 2=Active, 3=Closed |
| ne_lat | The latitude of the north-east corner | |
| ne_lon | The longitude of the north-east corner | |
| sw_lat | The latitude of the south-west corner | |
| sw_lon | The longitude of the south-west corner |
GPS Track Table (track)
The track table contains information about GPS tracks. A track is supposed to correspond to the work done during a single work period, typically a day. The server does not know what the customer's work day is so it attempts to create tracks by grouping together all GPS data that is within a specified maximum “gap” time. That time is currently defined as four hours.
| Column | Description | Comments |
|---|---|---|
| handle | An integer value that uniquely identifies the track | |
| project | The handle to the project the track belongs to, 0 if not associated with a project | |
| serial | The serial number of the GPS that generated the track | |
| start_time | The time of the first point in the track | |
| end_time | The time of the last point in the track | |
| discovery_time | The time the first valid data associated with this track was received | |
| total_points | The total number of points the track contains | |
| gap_count | The number of gaps in the track | |
| largest_gap | The size of the largest gap in seconds |
RTK Track Table (rtktrack)
The track table contains information about RTK GPS tracks. A track is supposed to correspond to the work done during a single work period, typically a day. The server does not know what the customer's work day is so it attempts to create tracks by grouping together all GPS data that is within a specified maximum “gap” time. That time is currently defined as four hours. (note: this behavior follows the GPS track model with specific exceptions for finding projects)
| Column | Description | Comments |
|---|---|---|
| handle | An integer value that uniquely identifies the track | |
| project | The handle to the project the track belongs to, 0 if not associated with a project | |
| serial | The serial number of the GPS that generated the track | |
| start_time | The time of the first point in the track | |
| end_time | The time of the last point in the track | |
| discovery_time | The time the first data associated with this track was received | |
| total_points | The total number of points the track contains | |
| gap_count | The number of gaps in the track | |
| largest_gap | The size of the largest gap in seconds |
Vehicle Table (vehicle)
The vehicle table contains information about customer vehicles. When a new vehicle is added the asset id is automatically generated by the server using the Asset ID table. The unique integer generated by the Asset ID table is used zero padding it to 8 characters. For example, if the Asset ID table generates the unique value “33” the vehicle asset id will be “00000033”.
| Column | Description | Comments |
|---|---|---|
| assetid | The unique asset id of the vehicle | |
| description | The vehicle description | |
| type | The vehicle type | No values defined for this yet, not currently used |
| color | The color used to display tracks associated with the vehicle | Not currently used |
| avgload | The average load for earth moving vehicles | Not currently used |
| status | The vehicle status | No values defined for this yet, not currently used |
GPS File Contents
The GPS data is stored in flat files rather than an SQL database for scalability.
For each GPS position the following information is stored:
| Description | Comments |
|---|---|
| Time of position | GMT timestamp as seconds offset from unix epoch |
| Latitude | Integer (latitude * 10000000) |
| Longitude | Integer (longitude * 10000000) |
| Altitude | Integer meteres (meters * 1000) |
| Horizontal accuracy | Integer (accuracy * 1000) |
| Vertical accuracy | Integer (accuracy * 1000) |
RTK File Contents
The RTK data is stored in flat files rather than an SQL database for scalability.
For each RTK position the following information is stored:
| Description | Comments |
|---|---|
| Time of position | GMT timestamp as seconds offset from unix epoch |
| Easting | ? Float Integer (range is +/- 99,999,999.9999) |
| Northing | ? Float Integer (range is +/- 99,999,999.9999) |
| Elevation | Integer feet (range is +/- 32,000.0000) |
| Units | Boolean? (Assumed feet if Yes ?) |
| Quality | Integer (0,1,2,3,4,5 corresponds to common NMEA flag standards) |
