Row Status and Row Owner
The Synchronisation process uses a specified SQL database on a SQL server (see Synchronisation Setup , this is known as the “Sync database”.
The Sync database has the data tables for each of the logging profiles which synchronise via the Sync Database (many logging profiles may use the same sync database, and there may be many different Sync databases on many servers (as many as are needed to facilitate your work processes).
Note: The sync database should not be modified outside of GM’s sync mechanism. Any modification to the Geobank Mobile sync database cannot be supported. In particular, custom triggers may cause data corruption.
Table Names
Previous releases of Geobank Mobile used a less than optimal mechanism to ensure that table names were unique across all profiles created using different devices. In Geobank Mobile 2018 a new table naming convention was applied. The new convention applies prefixes to the table names as before; however, when profiles are created by multiple devices, these prefix numbers are no longer guaranteed to match the prefix numbers of the tables on other devices.
For example:
Device A creates the first profile and the prefix number is “1_”. Similarly, Device B creates its first profile and the prefix number is also “1_”.
If Device B is first to sync to the SQL server, the profile that gets created on the server will also have the prefix number “1_” (this assumes that the database has just been created). However, when Device A syncs to the same database on the server, its profile number will be “2_”.
Since each profile can be identified by a GUID, having different prefixes on the devices, and on the server, is not an issue.
This change resolves the sluggish performance of Geobank Mobile on some devices.
Each of the data tables has various system columns including the Row Status (GMBRowStatus) and Row Owner (GBMRowOwner) columns. Information about these columns is provided below:
Row Owner
Since the sync database collects and distributes data from multiple users, this column is the means by which the data of each user is identified and protected. Each device uses a unique identifier in this column (an un-intelligible ‘guide’) to identify rows which were created on the device.
Since the Geobank Mobile application on each device knows what its own code is, it is able to prevent the user from editing data which originated from other loggers; likewise, data produced on the device is protected when it is visible to other users on other devices.
If your work processes require that a supervisor, such as a senior geologist or DBA, needs to be able to either edit the data generated by others, or process this data through the workflow, it is possible to set a ‘Master password’ which the supervisor can use to ‘unlock’ the data as needed. See: Password Protection
Row Status
The Row status column (GBMRowStatus) is used to indicate at what stage (state) in the workflow, a particular record is in at the current time. The numeric value in this column is the sum of the following status bit values:
| # | State | Description |
|---|---|---|
|
1 |
New |
This is assigned to rows that have been manually added. |
|
2 |
Imported |
This is assigned to rows that have been imported. |
|
4 |
Modified |
This is assigned to rows during bulk operations such as Increment or Replicate. |
|
8 |
Approved |
This is assigned to rows that have been approved (only valid data can be approved). |
|
16 |
Exported |
This is assigned to rows that have been Handed Over using an Export. |
|
32 |
GbLocked |
This is custom state which can be used by external tools. It is known as “GBLocked” because processes in the Geobank Data Management Solution can be set up to manage this status flag; so it is “a Lock flag set by Geobank or another third-party system”. When set, it places a permanent lock on the data and enables it to be separately purged. See more detail below. |
Using and Managing Row Status Externally
The reason for wanting to manage Row Status externally, would be facilitate an admin-managed data handover with ‘back-end’ (the SQL server databases) automation, as an alternative to a user-managed handover involving manual exports, files and imports. The possible admin-managed workflows are:
Collecting approved data
This is the simplest workflow for the user(logger) but comes with a risk. The workflow is:
- When the logger has completed logging a set of data, they approve it (temporary lock of valid data) to indicate that the data is complete and ready for collection.
- They then synchronise to communicate the data and status to the sync database.
- The admin periodically (or on an automated schedule) runs a configured process which copies the approved, but not previously collected data, from the GM sync database to the Geobank database buffer tables (or to whichever system is taking custody of the data) and simultaneously sets the GBLocked status flag to true to permanently lock the collected data so that the logger can still see it but cannot change it.
- Periodically the logger can then Purge the Locked data, once they no longer need it on their device for reference.
The risk in this workflow, is that if a user has approved then synchronised their data which is then collected and locked by the admin on the sync server, if the user were then to unapproved and change some data prior to the next sync, these latest changes would not be collected on the next collection. If you use this workflow, ensure users synchronise before any unapproval action.
Collecting exported data
This is a more robust workflow but involves an extra step for the user:
- As needed, the logger can approve valid data to temporary lock it – it is not ready for collection as they may come back to it before completing it.
- When the user has completed logging a set of data, they export it as a handover step, this locks it permanently and indicates that it is ready for collection (the exported files need not be used, but act as an extra backup/audit).
- They then synchronise to communicate the data and status to the sync database.
- The admin periodically (or on an automated schedule) runs a configured process which copies the exported, handed-over but not previously collected data, from the GM sync database to the Geobank database buffer tables (or to whichever system is taking custody of the data) and simultaneously sets the GBLocked status flag to true to indicate that it has been collected - the logger can still see it but cannot change it.
- Periodically the logger can then Purge the Locked data, once they no longer need it on their device for reference.
The section below describes the technical TSQL details of how to read and set Row Status bit values, so that a configured collection process to suit your needs can be set up. Should you need advice on such a process, or wish to lodge a work request for the MICROMINE team to work with you in building this process, please contact support.
Row Status is a sum of binary status bit flags; querying it to find whether a particular status is true or false is best done using the SQL “&” (bitwise AND) operator. If bits at any location are both 1, the result at that location is 1. To test if the bit flag of value 8 (Approved) is true, use the condition: (GBMRowStatus & 8) > 0 and to test if it is false use (GBMRowStatus & 8) = 0
To externally set the GBLocked flag from false to true without affecting any other flags, use the SQL "|" (bitwise OR) operator:
Use a condition to filter the records to those for which you wish to set the GBMRowStatus flag to true;
For example, exported rows:
WHERE (GBMRowStatus & 16) = 1
It is not necessary, but is good practice to also filter to only those records where the GBMRowStatus is false:
AND (GBMRowStatus & 32) = 0)
then update the Row Status value using:
GBMRowStatus = GBMRowStatus | 32
(this will set the GBMRowStatus (32) flag to true if it was false, and will leave it as true if it was already true)
When the GBLocked flag is true, the user:
- cannot edit or unapprove (unlock) the data row
- can purge the externally locked data using the Purge tool with the Locked check box:
Database Schema
Check the Default schema for the user or group to which the database user belongs.
This provides further technical details for Database administrators to gain a better understanding of the SQL server Sync database built by Geobank Mobile. Geobank Mobile is currently designed to work on the default 'dbo' schema. Other schemas may work, however they are not currently supported by Geobank Mobile.
Under some circumstances, SQL server will use a schema other than 'dbo' when new tables or views are added. If Geobank Mobile provides a warning to this effect:
The following setting may be needed so that SQL server creates the table/view in the 'dbo' schema:
Add 'dbo' as default schema if blank as below:
