Comment on page
Database Sync and Merge
Database Sync is a highly advanced feature allowing you to automatically pull all character, license, and vehicle registration data from your own in-game database directly to Sonoran CAD.
Database Sync is a highly advanced feature allowing you to automatically pull all character, license, and vehicle registration data from your own in-game database directly to Sonoran CAD.
This feature is specifically designed for frameworks like ESX and QBus/QBCore, but is compatible with any MySQL database.
Configuring Sonoran CAD's Database Sync may seem complicated, but you are simply specifying your table names and column values where your in-game data is stored.
REQUIRED fields in the CAD are shown in red. All other fields are optional and are not required to work properly.
In order for Sonoran CAD to connect to your SQL database, connection credentials must be configured properly.
Sonoran CAD requires an external connection to your database.
View our guide on creating a new read-only SQL user for external use.
Expand the
SQL Connection Credentials section of the configuration, and toggle on Database Sync and the optional Database Merge feature.Database Merge is an additional feature that allows you to save off additional, manually specified data in the CAD.
Example:
DB Merge pulls a character record into the CAD, but the
address field in your custom record is blank (your DB doesn't have this info). DB merge allows you to manually edit the record pulled from your database and edit any blank field.Next time you look up this character, it will pull the same character info from your database and then merge this with the manually specified data saved in Sonoran CAD.
Sonoran CAD - DB Sync SQL Credentials
Field | Description |
Type | This is the type of SQL database your server is using (MySQL, PostgreSQL, etc.) |
Host/Address | This is the external IP address or domain address to your SQL database.
Note: This IP will never be localhost or 127.0.0.1. This must be the external IP or domain to reach your database. Learn more about creating an external SQL connection. |
Port | This is the port your database is accessible from. Typically, the default port for MySQL is 3306. |
Database | This is the database or schema name that contains your community’s character, license and vehicle registration tables. |
Username | This is the SQL account username. |
Password | This is the password for the SQL account. |
Once the required SQL connection fields have been specified, select the “Test Connection” button.
This will query your database for the version.
If you see an alert similar to the following, your connection is successful:
DB Sync - Connection Successful
If you see an error message, it's likely you have not properly setup your new SQL user's credentials or opened the database port for external use.
Sonoran CAD requires character mapping to be properly configured for the additional license and vehicle registration mapping.
The DB sync configuration is designed to show Sonoran CAD the specific tables and columns that data can be pulled from in your database.
The
table name field will contain the name of your database table containing character records.The
column name field will contain the name of the specific column in the character records table containing data for this row.The
Character Mapping Column contains a unique ID for the specific character. This unique identifier will also map records in your license and vehicle registration tables back to the character that owns them. Typically, this is a Steam ID or license ID.Character records can also pull data from multiple different tables, such as a properties table to add address information. Just be sure that those additional tables have a proper
identifier column to map back to the other characters table.Example:
In our database, the
characters table contains our character records. The identifier column contains the character's unique ID, the firstname column contains the characters first name, the lastname character contains the characters last name, etc.
SQL Table Example
DB Sync Character Mapping Example
Be sure that you have enabled character mapping via the toggle. Once your character mapping has been completed, hit the save button and then the test button. The test button will attempt to select a single random character with the mapping configuration specified.
If you see "Success!" move onto the next section.
Licenses and Vehicle registrations can also be automatically pulled via CAD search with Database Sync.
Similar to the character mapping, specify the table name containing your vehicle registrations or licenses. Unlike character mapping, data for these records can only be pulled from a single table.
The vehicle and license mapping will also need to have the
Character Mapping Column specified. Again, this is the name of the column in your license/vehicle table containing a unique ID that maps back to the character that owns it.Example:
In our database, the
owned_vehicles table contains our stored vehicles. The owner column contains the character's unique ID that owns the vehicle, and the plate column contains the vehicle's license plate.
SQL Vehicle Table Example
DB Sync - Vehicle Mapping Example
Be sure that you have enabled the license/vehicle mapping via the toggle. Once your mapping has been completed, hit the save button and then the test button. The test button will attempt to select a single random license or vehicle with the mapping configuration specified.
If you see "Success!" move onto the next section.
Many databases store data in a JSON formatted column. Sonoran CAD can also parse these columns for data.
In our database, the
accounts column stores JSON formatted data. For this example, we want to display the bank money in the custom character record.
SQL - JSON Column Example
We can see that the JSON "key" for the bank account amount is
bank.Nested JSON values are also supported.
Here, the
eye_color is a JSON object, with the item property of 0.
Database - Nested JSON Value
In the DB Sync config, we list the JSON key as
eye_color.item
Then, we can use friendly mapping to convert the eye color
0 value to an actual color, like "brown".In some cases, your license or vehicle registration tables may not directly contain a
Character Mapping ID column (a column with an ID that directly maps back to the character/civilian record), but may contain a unique ID that maps back to a specific character in another table.In this example, the
vehicle table contains all of the vehicle information, but does not contain a CharacterID column. Instead, the vehicle table contains a VehicleRegistrationID column.The
VehicleRegistrationId column then maps to the vehicleregistration table. The vehicleregistration table then contains a corresponding CharacterId column, which maps back to the character table.
Sonoran CAD - External Key DB Layout
In the CAD, the configuration is simple.
Toggle on the
External Key checkbox, as the vehicle table's VehicleId needs to be mapped to an external table to be turned into the proper characterId.Specify the external key's table (
vehicleregistration) and the external key's column CharacterId.
Sonoran CAD - External Key
Back in the mapping panel, we toggle the field as a
JSON Column and set the column name to accounts as this is the column in our character table that contains the JSON data.We can then set the JSON Key for this data as
bank.
DB Sync - JSON Column
Friendly mapping allows you to convert any raw database value to a more user friendly value.
Ex:
drive_license in your database is converted to Driver's License.MySQL
tinyint column types may be displayed as numerical values 0 and 1 but be read as True and False by Sonoran CAD.
Instead of mapping the "Database Vaue" as 0 or 1 you will need to map False and True.In our SQL table, we can see the character's job columns has text values that can be improved. The
taxi job value can be automatically converted to Taxi Driver in DB Sync records, and the cardealer jon can be automatically converted to Car Dealer.
SQL Table - Unfriendly Values
In our character table mapping, we can select
Modify on the job field's friendly mapping.
DB Sync - Modify Friendly Mapping
In the editor, we can now map the raw database value of
taxi to a friendly value of Taxi Driver and the raw db value of cardealer to Car Dealer.Be sure to hit save in the friendly mapping editor, and then save the configuration for your mapping section.
Friendly Mapping Editor
Navigate to our official friendly mapping Google Sheet and make a copy. Using a copy of our official sheet ensures your friendly mapping is formatted correctly.
You may ONLY use the Google sheet directly. Editing this via Excel or any other program is NOT supported.
Sonoran CAD - Copy Friendly Mapping CSV
2. Add your Friendly Mappings
Be sure to leave the top header line as it is. Below the header, add in your friendly mapping keys and values.
Sonoran CAD - Edit Friendly Mapping CSV
In Google Sheets, navigate to File > Download > Comma Separated Values (.csv) to download the file.
Sonoran CAD - Download Friendly Mapping CSV
In the friendly mapping editor select
Import > CSV > Select your downloaded Google spreadsheetThen, save the mapping and save the database sync config.
You can also build and format your friendly mapping from raw JSON and paste them directly into the UI.
The JSON structure is an object array. Be sure to strictly follow the format.
[
{
"dbValue": "0",
"friendlyValue": "Brown"
},
{
"dbValue": "1",
"friendlyValue": "Green"
},
{
"dbValue": "2",
"friendlyValue": "Blue"
}
]
In the friendly mapping editor select
Import > JSON > Paste your JSON formatted structure
Friendly Mapping - Import via JSON
Sonoran CAD's records are entirely customizable, this includes database sync records! You can easily enable database sync mapping for any custom field you add to a character, license, or vehicle registration record.
Navigate to Admin > Customization > Custom Records
Select your custom character, license, or vehicle registration record to open the editor.
Simply add a field if the desired field doesn't already exist and note the
Label name for the next step. For this example, we'll enable database sync for a new job field in our character's table.Be sure to save your custom record format after enabling this!
Custom Records - DB Sync Mapping Toggle
Back in our database sync editor, we can now see the new
Job field has been added. We can map this new field to our database as any other field.
Database Sync - Custom Field Mapping