This is an old revision of the document!
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)
| Column | Description | Comments |
| | | |
GPS Statistics Table (gps)
| Column | Description | Comments |
| | | |
Project Table (project)
| Column | Description | Comments |
| | | |
GPS Track Table (track)
| Column | Description | Comments |
| | | |
Vehicle Table (vehicle)
| Column | Description | Comments |
| | | |
