Creating SQL Metadata
SQL metadata is used to implement ISAM-to-SQL replication via the SQL Agent which is configured in the environment using the TableDefinitions section of MasterConfig.json.
View the current SQL metadata
- Use the Configuration Server UI to review the current mappings, which are for the same two sample ISAM files named DEPARTMENT.ISM and EMPLOYEE.ISM. The SQL metadata is much more verbose, so it is not all shown here, but it starts like this:
"TableDefinitions": [
{
"file_name": "DEPARTMENT.ISM",
"name": "DEPARTMENT",
"file_type": "ISAM",
"file_spec": "SIP_DAT:DEPARTMENT.IS1",
"record_size": 100,
"sub_tables": [
{
"name": "DEPARTMENT",
"enabled": true,
"record_size": 100,
"columns": [
{
"name": "DeptId",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 15,
"signed": null,
"custom_handler": null,
"dependent_columns": null
},
"size": 15,
"precision": 0,
"position": 0,
"nullable": false,
"index": 0
},
The SQL Metadata contains all of the data required to map the DBL data to a data format known as Parquet files which are used when producing data snapshots for use with the SQL Agent.
You must create new SQL metadata for the nine ISAM files used in the workshop application environment, and then replace the metadata for the two sample files that is currently in MasterConfig.json.
Once again you can use metadatatool to produce the metadata, but this time you will use its sql mode which produces the necessary metadata based on the information in a Synergy repository, which has already been created and provided.
Generate new ISAM metadata
cd ~ && export DAT=~/data && metadatatool sql --files CUSTOMER EMPLOYEE INVENTORY ORDER ORDERLINE SHIPTO USER VENDOR WAREHOUSE
You should see output like this:
Processing file: CUSTOMER
Processing file: EMPLOYEE
Processing file: INVENTORY
Processing file: ORDER
Processing file: ORDERLINE
Processing file: SHIPTO
Processing file: USER
Processing file: VENDOR
Processing file: WAREHOUSE
Created /home/admin/.table_definitions.json
You should also have a new hidden file named .table_definitions.json in your current directory:
If you want to see what was created, it's here:
Click to see the new SQL Metadata
It is not necessary to understand every detail of the metadata, but if you are interested, this is the SQL metadata that was just created:
[
{
"name": "CUSTOMER",
"file_type": "ISAM",
"file_spec": "DAT:customer.is1",
"file_name": "customer.ism",
"record_size": 287,
"sub_tables": [
{
"name": "CUSTOMER",
"enabled": true,
"record_size": 287,
"columns": [
{
"name": "CustomerId",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 8,
"signed": true
},
"size": 8,
"precision": 0,
"position": 0,
"nullable": true,
"index": 0
},
{
"name": "CustomerName",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 40
},
"size": 40,
"precision": 0,
"position": 8,
"nullable": true,
"index": 1
},
{
"name": "AddressLine1",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 30
},
"size": 30,
"precision": 0,
"position": 48,
"nullable": true,
"index": 2
},
{
"name": "AddressLine2",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 30
},
"size": 30,
"precision": 0,
"position": 78,
"nullable": true,
"index": 3
},
{
"name": "City",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 25
},
"size": 25,
"precision": 0,
"position": 108,
"nullable": true,
"index": 4
},
{
"name": "State",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 2
},
"size": 2,
"precision": 0,
"position": 133,
"nullable": true,
"index": 5
},
{
"name": "PostalCode",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 10
},
"size": 10,
"precision": 0,
"position": 135,
"nullable": true,
"index": 6
},
{
"name": "Country",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 2
},
"size": 2,
"precision": 0,
"position": 145,
"nullable": true,
"index": 7
},
{
"name": "PhoneNumber",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 15
},
"size": 15,
"precision": 0,
"position": 147,
"nullable": true,
"index": 8
},
{
"name": "EmailAddress",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 50
},
"size": 50,
"precision": 0,
"position": 162,
"nullable": true,
"index": 9
},
{
"name": "ContactName",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 30
},
"size": 30,
"precision": 0,
"position": 212,
"nullable": true,
"index": 10
},
{
"name": "CustomerType",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 1,
"signed": true
},
"size": 1,
"precision": 0,
"position": 242,
"nullable": true,
"index": 11
},
{
"name": "CreditLimit",
"data_type": {
"Decimal128": [
9,
2
]
},
"native_type": {
"type": "Decimal",
"size": 9,
"signed": true
},
"size": 9,
"precision": 2,
"position": 243,
"nullable": true,
"index": 12
},
{
"name": "PaymentTerms",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 2,
"signed": true
},
"size": 2,
"precision": 0,
"position": 252,
"nullable": true,
"index": 13
},
{
"name": "TaxId",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 15
},
"size": 15,
"precision": 0,
"position": 254,
"nullable": true,
"index": 14
},
{
"name": "SalesRepId",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 6,
"signed": true
},
"size": 6,
"precision": 0,
"position": 269,
"nullable": true,
"index": 15
},
{
"name": "CurrencyCode",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 3
},
"size": 3,
"precision": 0,
"position": 275,
"nullable": true,
"index": 16
},
{
"name": "CustomerStatus",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 1,
"signed": true
},
"size": 1,
"precision": 0,
"position": 278,
"nullable": true,
"index": 17
},
{
"name": "DateAdded",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 8,
"signed": true
},
"size": 8,
"precision": 0,
"position": 279,
"nullable": true,
"index": 18
}
]
}
]
},
{
"name": "EMPLOYEE",
"file_type": "ISAM",
"file_spec": "DAT:employee.is1",
"file_name": "employee.ism",
"record_size": 280,
"sub_tables": [
{
"name": "EMPLOYEE",
"enabled": true,
"record_size": 280,
"columns": [
{
"name": "EmployeeId",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 6,
"signed": true
},
"size": 6,
"precision": 0,
"position": 0,
"nullable": true,
"index": 0
},
{
"name": "FirstName",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 20
},
"size": 20,
"precision": 0,
"position": 6,
"nullable": true,
"index": 1
},
{
"name": "LastName",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 30
},
"size": 30,
"precision": 0,
"position": 26,
"nullable": true,
"index": 2
},
{
"name": "AddressLine1",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 30
},
"size": 30,
"precision": 0,
"position": 56,
"nullable": true,
"index": 3
},
{
"name": "AddressLine2",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 30
},
"size": 30,
"precision": 0,
"position": 86,
"nullable": true,
"index": 4
},
{
"name": "City",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 25
},
"size": 25,
"precision": 0,
"position": 116,
"nullable": true,
"index": 5
},
{
"name": "State",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 2
},
"size": 2,
"precision": 0,
"position": 141,
"nullable": true,
"index": 6
},
{
"name": "PostalCode",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 10
},
"size": 10,
"precision": 0,
"position": 143,
"nullable": true,
"index": 7
},
{
"name": "Country",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 2
},
"size": 2,
"precision": 0,
"position": 153,
"nullable": true,
"index": 8
},
{
"name": "PhoneNumber",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 15
},
"size": 15,
"precision": 0,
"position": 155,
"nullable": true,
"index": 9
},
{
"name": "EmailAddress",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 50
},
"size": 50,
"precision": 0,
"position": 170,
"nullable": true,
"index": 10
},
{
"name": "WarehouseId",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 4,
"signed": true
},
"size": 4,
"precision": 0,
"position": 220,
"nullable": true,
"index": 11
},
{
"name": "JobTitle",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 30
},
"size": 30,
"precision": 0,
"position": 224,
"nullable": true,
"index": 12
},
{
"name": "DepartmentCode",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 2,
"signed": true
},
"size": 2,
"precision": 0,
"position": 254,
"nullable": true,
"index": 13
},
{
"name": "HireDate",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 8,
"signed": true
},
"size": 8,
"precision": 0,
"position": 256,
"nullable": true,
"index": 14
},
{
"name": "SupervisorId",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 6,
"signed": true
},
"size": 6,
"precision": 0,
"position": 264,
"nullable": true,
"index": 15
},
{
"name": "PayRate",
"data_type": {
"Decimal128": [
9,
2
]
},
"native_type": {
"type": "Decimal",
"size": 9,
"signed": true
},
"size": 9,
"precision": 2,
"position": 270,
"nullable": true,
"index": 16
},
{
"name": "EmployeeStatus",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 1,
"signed": true
},
"size": 1,
"precision": 0,
"position": 279,
"nullable": true,
"index": 17
}
]
}
]
},
{
"name": "INVENTORY",
"file_type": "ISAM",
"file_spec": "DAT:inventory.is1",
"file_name": "inventory.ism",
"record_size": 125,
"sub_tables": [
{
"name": "INVENTORY",
"enabled": true,
"record_size": 125,
"columns": [
{
"name": "Sku",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 8,
"signed": true
},
"size": 8,
"precision": 0,
"position": 0,
"nullable": true,
"index": 0
},
{
"name": "ItemDescription",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 40
},
"size": 40,
"precision": 0,
"position": 8,
"nullable": true,
"index": 1
},
{
"name": "ItemCategory",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 2,
"signed": true
},
"size": 2,
"precision": 0,
"position": 48,
"nullable": true,
"index": 2
},
{
"name": "UnitOfMeasure",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 4
},
"size": 4,
"precision": 0,
"position": 50,
"nullable": true,
"index": 3
},
{
"name": "UnitPrice",
"data_type": {
"Decimal128": [
9,
2
]
},
"native_type": {
"type": "Decimal",
"size": 9,
"signed": true
},
"size": 9,
"precision": 2,
"position": 54,
"nullable": true,
"index": 4
},
{
"name": "UnitCost",
"data_type": {
"Decimal128": [
9,
2
]
},
"native_type": {
"type": "Decimal",
"size": 9,
"signed": true
},
"size": 9,
"precision": 2,
"position": 63,
"nullable": true,
"index": 5
},
{
"name": "QuantityOnHand",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 9,
"signed": true
},
"size": 9,
"precision": 0,
"position": 72,
"nullable": true,
"index": 6
},
{
"name": "ReorderPoint",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 9,
"signed": true
},
"size": 9,
"precision": 0,
"position": 81,
"nullable": true,
"index": 7
},
{
"name": "ReorderQuantity",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 9,
"signed": true
},
"size": 9,
"precision": 0,
"position": 90,
"nullable": true,
"index": 8
},
{
"name": "PrimaryVendorId",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 6,
"signed": true
},
"size": 6,
"precision": 0,
"position": 99,
"nullable": true,
"index": 9
},
{
"name": "PrimaryWarehouseId",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 4,
"signed": true
},
"size": 4,
"precision": 0,
"position": 105,
"nullable": true,
"index": 10
},
{
"name": "ItemWeight",
"data_type": {
"Decimal128": [
7,
2
]
},
"native_type": {
"type": "Decimal",
"size": 7,
"signed": true
},
"size": 7,
"precision": 2,
"position": 109,
"nullable": true,
"index": 11
},
{
"name": "InventoryStatus",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 1,
"signed": true
},
"size": 1,
"precision": 0,
"position": 116,
"nullable": true,
"index": 12
},
{
"name": "DateAdded",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 8,
"signed": true
},
"size": 8,
"precision": 0,
"position": 117,
"nullable": true,
"index": 13
}
]
}
]
},
{
"name": "ORDER",
"file_type": "ISAM",
"file_spec": "DAT:order.is1",
"file_name": "order.ism",
"record_size": 83,
"sub_tables": [
{
"name": "ORDER",
"enabled": true,
"record_size": 83,
"columns": [
{
"name": "OrderId",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 8,
"signed": true
},
"size": 8,
"precision": 0,
"position": 0,
"nullable": true,
"index": 0
},
{
"name": "CustomerId",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 8,
"signed": true
},
"size": 8,
"precision": 0,
"position": 8,
"nullable": true,
"index": 1
},
{
"name": "LocationId",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 4,
"signed": true
},
"size": 4,
"precision": 0,
"position": 16,
"nullable": true,
"index": 2
},
{
"name": "WarehouseId",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 4,
"signed": true
},
"size": 4,
"precision": 0,
"position": 20,
"nullable": true,
"index": 3
},
{
"name": "SalesRepId",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 6,
"signed": true
},
"size": 6,
"precision": 0,
"position": 24,
"nullable": true,
"index": 4
},
{
"name": "OrderDate",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 8,
"signed": true
},
"size": 8,
"precision": 0,
"position": 30,
"nullable": true,
"index": 5
},
{
"name": "RequestedShipDate",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 8,
"signed": true
},
"size": 8,
"precision": 0,
"position": 38,
"nullable": true,
"index": 6
},
{
"name": "PoNumber",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 20
},
"size": 20,
"precision": 0,
"position": 46,
"nullable": true,
"index": 7
},
{
"name": "OrderStatus",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 1,
"signed": true
},
"size": 1,
"precision": 0,
"position": 66,
"nullable": true,
"index": 8
},
{
"name": "PaymentTerms",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 2,
"signed": true
},
"size": 2,
"precision": 0,
"position": 67,
"nullable": true,
"index": 9
},
{
"name": "CurrencyCode",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 3
},
"size": 3,
"precision": 0,
"position": 69,
"nullable": true,
"index": 10
},
{
"name": "OrderTotal",
"data_type": {
"Decimal128": [
11,
2
]
},
"native_type": {
"type": "Decimal",
"size": 11,
"signed": true
},
"size": 11,
"precision": 2,
"position": 72,
"nullable": true,
"index": 11
}
]
}
]
},
{
"name": "ORDERLINE",
"file_type": "ISAM",
"file_spec": "DAT:orderline.is1",
"file_name": "orderline.ism",
"record_size": 63,
"sub_tables": [
{
"name": "ORDERLINE",
"enabled": true,
"record_size": 63,
"columns": [
{
"name": "OrderId",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 8,
"signed": true
},
"size": 8,
"precision": 0,
"position": 0,
"nullable": true,
"index": 0
},
{
"name": "LineId",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 4,
"signed": true
},
"size": 4,
"precision": 0,
"position": 8,
"nullable": true,
"index": 1
},
{
"name": "Sku",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 8,
"signed": true
},
"size": 8,
"precision": 0,
"position": 12,
"nullable": true,
"index": 2
},
{
"name": "WarehouseId",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 4,
"signed": true
},
"size": 4,
"precision": 0,
"position": 20,
"nullable": true,
"index": 3
},
{
"name": "QuantityOrdered",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 9,
"signed": true
},
"size": 9,
"precision": 0,
"position": 24,
"nullable": true,
"index": 4
},
{
"name": "QuantityShipped",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 9,
"signed": true
},
"size": 9,
"precision": 0,
"position": 33,
"nullable": true,
"index": 5
},
{
"name": "UnitPrice",
"data_type": {
"Decimal128": [
9,
2
]
},
"native_type": {
"type": "Decimal",
"size": 9,
"signed": true
},
"size": 9,
"precision": 2,
"position": 42,
"nullable": true,
"index": 6
},
{
"name": "ExtendedPrice",
"data_type": {
"Decimal128": [
11,
2
]
},
"native_type": {
"type": "Decimal",
"size": 11,
"signed": true
},
"size": 11,
"precision": 2,
"position": 51,
"nullable": true,
"index": 7
},
{
"name": "LineStatus",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 1,
"signed": true
},
"size": 1,
"precision": 0,
"position": 62,
"nullable": true,
"index": 8
}
]
}
]
},
{
"name": "SHIPTO",
"file_type": "ISAM",
"file_spec": "DAT:shipto.is1",
"file_name": "shipto.ism",
"record_size": 198,
"sub_tables": [
{
"name": "SHIPTO",
"enabled": true,
"record_size": 198,
"columns": [
{
"name": "CustomerId",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 8,
"signed": true
},
"size": 8,
"precision": 0,
"position": 0,
"nullable": true,
"index": 0
},
{
"name": "LocationId",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 4,
"signed": true
},
"size": 4,
"precision": 0,
"position": 8,
"nullable": true,
"index": 1
},
{
"name": "LocationName",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 40
},
"size": 40,
"precision": 0,
"position": 12,
"nullable": true,
"index": 2
},
{
"name": "AddressLine1",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 30
},
"size": 30,
"precision": 0,
"position": 52,
"nullable": true,
"index": 3
},
{
"name": "AddressLine2",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 30
},
"size": 30,
"precision": 0,
"position": 82,
"nullable": true,
"index": 4
},
{
"name": "City",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 25
},
"size": 25,
"precision": 0,
"position": 112,
"nullable": true,
"index": 5
},
{
"name": "State",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 2
},
"size": 2,
"precision": 0,
"position": 137,
"nullable": true,
"index": 6
},
{
"name": "PostalCode",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 10
},
"size": 10,
"precision": 0,
"position": 139,
"nullable": true,
"index": 7
},
{
"name": "Country",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 2
},
"size": 2,
"precision": 0,
"position": 149,
"nullable": true,
"index": 8
},
{
"name": "ContactName",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 30
},
"size": 30,
"precision": 0,
"position": 151,
"nullable": true,
"index": 9
},
{
"name": "PhoneNumber",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 15
},
"size": 15,
"precision": 0,
"position": 181,
"nullable": true,
"index": 10
},
{
"name": "DefaultLocationFlag",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 1,
"signed": true
},
"size": 1,
"precision": 0,
"position": 196,
"nullable": true,
"index": 11
},
{
"name": "ShiptoStatus",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 1,
"signed": true
},
"size": 1,
"precision": 0,
"position": 197,
"nullable": true,
"index": 12
}
]
}
]
},
{
"name": "SYSPARAMS",
"file_type": "RELATIVE",
"file_spec": "DAT:sysparams.ddf",
"file_name": "sysparams.ddf",
"record_size": 38,
"sub_tables": [
{
"name": "SYSPARAMS",
"enabled": true,
"record_size": 38,
"columns": [
{
"name": "ParamName",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 30
},
"size": 30,
"precision": 0,
"position": 0,
"nullable": true,
"index": 0
},
{
"name": "ParamValue",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 8,
"signed": true
},
"size": 8,
"precision": 0,
"position": 30,
"nullable": true,
"index": 1
}
]
}
]
},
{
"name": "USER",
"file_type": "ISAM",
"file_spec": "DAT:user.is1",
"file_name": "user.ism",
"record_size": 162,
"sub_tables": [
{
"name": "USER",
"enabled": true,
"record_size": 162,
"columns": [
{
"name": "Username",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 40
},
"size": 40,
"precision": 0,
"position": 0,
"nullable": true,
"index": 0
},
{
"name": "FirstName",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 20
},
"size": 20,
"precision": 0,
"position": 40,
"nullable": true,
"index": 1
},
{
"name": "LastName",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 20
},
"size": 20,
"precision": 0,
"position": 60,
"nullable": true,
"index": 2
},
{
"name": "Email",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 50
},
"size": 50,
"precision": 0,
"position": 80,
"nullable": true,
"index": 3
},
{
"name": "Password",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 30
},
"size": 30,
"precision": 0,
"position": 130,
"nullable": true,
"index": 4
},
{
"name": "Enabled",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 1,
"signed": true
},
"size": 1,
"precision": 0,
"position": 160,
"nullable": true,
"index": 5
},
{
"name": "IsAdmin",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 1,
"signed": true
},
"size": 1,
"precision": 0,
"position": 161,
"nullable": true,
"index": 6
}
]
}
]
},
{
"name": "VENDOR",
"file_type": "ISAM",
"file_spec": "DAT:vendor.is1",
"file_name": "vendor.ism",
"record_size": 270,
"sub_tables": [
{
"name": "VENDOR",
"enabled": true,
"record_size": 270,
"columns": [
{
"name": "VendorId",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 6,
"signed": true
},
"size": 6,
"precision": 0,
"position": 0,
"nullable": true,
"index": 0
},
{
"name": "VendorName",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 40
},
"size": 40,
"precision": 0,
"position": 6,
"nullable": true,
"index": 1
},
{
"name": "AddressLine1",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 30
},
"size": 30,
"precision": 0,
"position": 46,
"nullable": true,
"index": 2
},
{
"name": "AddressLine2",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 30
},
"size": 30,
"precision": 0,
"position": 76,
"nullable": true,
"index": 3
},
{
"name": "City",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 25
},
"size": 25,
"precision": 0,
"position": 106,
"nullable": true,
"index": 4
},
{
"name": "State",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 2
},
"size": 2,
"precision": 0,
"position": 131,
"nullable": true,
"index": 5
},
{
"name": "PostalCode",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 10
},
"size": 10,
"precision": 0,
"position": 133,
"nullable": true,
"index": 6
},
{
"name": "Country",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 2
},
"size": 2,
"precision": 0,
"position": 143,
"nullable": true,
"index": 7
},
{
"name": "PhoneNumber",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 15
},
"size": 15,
"precision": 0,
"position": 145,
"nullable": true,
"index": 8
},
{
"name": "EmailAddress",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 50
},
"size": 50,
"precision": 0,
"position": 160,
"nullable": true,
"index": 9
},
{
"name": "ContactName",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 30
},
"size": 30,
"precision": 0,
"position": 210,
"nullable": true,
"index": 10
},
{
"name": "VendorType",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 1,
"signed": true
},
"size": 1,
"precision": 0,
"position": 240,
"nullable": true,
"index": 11
},
{
"name": "PaymentTerms",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 2,
"signed": true
},
"size": 2,
"precision": 0,
"position": 241,
"nullable": true,
"index": 12
},
{
"name": "TaxId",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 15
},
"size": 15,
"precision": 0,
"position": 243,
"nullable": true,
"index": 13
},
{
"name": "CurrencyCode",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 3
},
"size": 3,
"precision": 0,
"position": 258,
"nullable": true,
"index": 14
},
{
"name": "VendorStatus",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 1,
"signed": true
},
"size": 1,
"precision": 0,
"position": 261,
"nullable": true,
"index": 15
},
{
"name": "DateAdded",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 8,
"signed": true
},
"size": 8,
"precision": 0,
"position": 262,
"nullable": true,
"index": 16
}
]
}
]
},
{
"name": "WAREHOUSE",
"file_type": "ISAM",
"file_spec": "DAT:warehouse.is1",
"file_name": "warehouse.ism",
"record_size": 158,
"sub_tables": [
{
"name": "WAREHOUSE",
"enabled": true,
"record_size": 158,
"columns": [
{
"name": "WarehouseId",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 4,
"signed": true
},
"size": 4,
"precision": 0,
"position": 0,
"nullable": true,
"index": 0
},
{
"name": "WarehouseName",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 30
},
"size": 30,
"precision": 0,
"position": 4,
"nullable": true,
"index": 1
},
{
"name": "AddressLine1",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 30
},
"size": 30,
"precision": 0,
"position": 34,
"nullable": true,
"index": 2
},
{
"name": "AddressLine2",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 30
},
"size": 30,
"precision": 0,
"position": 64,
"nullable": true,
"index": 3
},
{
"name": "City",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 25
},
"size": 25,
"precision": 0,
"position": 94,
"nullable": true,
"index": 4
},
{
"name": "State",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 2
},
"size": 2,
"precision": 0,
"position": 119,
"nullable": true,
"index": 5
},
{
"name": "PostalCode",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 10
},
"size": 10,
"precision": 0,
"position": 121,
"nullable": true,
"index": 6
},
{
"name": "Country",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 2
},
"size": 2,
"precision": 0,
"position": 131,
"nullable": true,
"index": 7
},
{
"name": "PhoneNumber",
"data_type": "Utf8",
"native_type": {
"type": "Alpha",
"size": 15
},
"size": 15,
"precision": 0,
"position": 133,
"nullable": true,
"index": 8
},
{
"name": "WarehouseType",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 1,
"signed": true
},
"size": 1,
"precision": 0,
"position": 148,
"nullable": true,
"index": 9
},
{
"name": "DateOpened",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 8,
"signed": true
},
"size": 8,
"precision": 0,
"position": 149,
"nullable": true,
"index": 10
},
{
"name": "WarehouseStatus",
"data_type": "Int32",
"native_type": {
"type": "Decimal",
"size": 1,
"signed": true
},
"size": 1,
"precision": 0,
"position": 157,
"nullable": true,
"index": 11
}
]
}
]
}
]
The new file .table_definitions.json contains an array of metadata objects that we need to use to replace the metadata objects in the MasterConfig.json file's TableDefinitions array. Once again, this can be achieved with a single jq command:
Execute this command to replace the SSQL metadata in MasterConfig.json
CONFDIR=/etc/synergex/sip/config-server/default && sudo cp $CONFDIR/MasterConfig.json{,.bak} && sudo jq --slurpfile files ~/.table_definitions.json '.TableDefinitions = $files[0]' $CONFDIR/MasterConfig.json > /tmp/MasterConfig.json && sudo cp /tmp/MasterConfig.json $CONFDIR/MasterConfig.json && unset CONFDIR
Before proceeding, it is a good idea to verify that the updated SQL metadata is in place.
Verify SQL metadata in Configuration Server
- Use the Configuration Server UI to view the updated SQL metadata.
You should see that the SQL metadata has been updated to reflect the nine ISAM files.