I am using an SQLite database, mostly because that is what the project started with, however I am willing to move it over to a MySQL database, which I probably will in the future for the better features.
I am trying to transfer virtual relationships using the json schema upload/modify tools.
When I create a new table with the upload, and I pass in a table schema with the relationship description afterwards, the table is created but the relationships are not brought in. If I then try to modify the json schema in the dreamfactory tools, and the press save, it says I can’t alter in sqlite, but if I do it using MYSQL it says:
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (
bitnami_dreamfactory.db_virtual_relationship, CONSTRAINTdb_virtual_relationship_ref_service_id_foreignFOREIGN KEY (ref_service_id) REFERENCESservice(id) ON DELETE CASCADE) (SQL: insert intodb_virtual_relationship(type,service_id,table,field,ref_service_id,ref_table,ref_field,junction_service_id,junction_table,junction_field,junction_ref_field,ref_on_update,ref_on_delete,last_modified_date,created_date) values (has_many, 8, loans, loan_id, 10, Collateral, loan_id, , , , , , , 2017-03-02 17:19:42, 2017-03-02 17:19:42))
It looks like it is trying to (in both sqlite and mysql) insert the relationships, but all I want it to do is have dreamfactory remember the virtual relationships, just like it does when i successfully create them manually. What am I supposed to do so I don’t have to go through an entire list of relationships and re-create them every time I want to move to a different location for local testing?
Here is an example json schema:
{
"alias": null,
"name": "loans",
"label": "Loans",
"description": null,
"native": [],
"plural": "Loans",
"is_view": false,
"primary_key": "loan_id",
"name_field": null,
"field": [
{
"alias": null,
"name": "loan_id",
"label": "Loan Id",
"description": null,
"native": [],
"type": "id",
"db_type": "int(11)",
"length": 11,
"precision": null,
"scale": null,
"default": null,
"required": false,
"allow_null": false,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": true,
"is_primary_key": true,
"is_unique": false,
"is_index": false,
"is_foreign_key": false,
"ref_table": null,
"ref_field": null,
"ref_on_update": null,
"ref_on_delete": null,
"picklist": null,
"validation": null,
"db_function": null,
"is_virtual": false,
"is_aggregate": false
},
{
"alias": null,
"name": "client_id",
"label": "Client Id",
"description": null,
"native": [],
"type": "integer",
"db_type": "int(11)",
"length": 11,
"precision": null,
"scale": null,
"default": null,
"required": true,
"allow_null": false,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_unique": false,
"is_index": false,
"is_foreign_key": false,
"ref_table": null,
"ref_field": null,
"ref_on_update": null,
"ref_on_delete": null,
"picklist": null,
"validation": null,
"db_function": null,
"is_virtual": false,
"is_aggregate": false
},
{
"alias": null,
"name": "loan_start_date",
"label": "Loan Start Date",
"description": null,
"native": [],
"type": "integer",
"db_type": "int(11)",
"length": 11,
"precision": null,
"scale": null,
"default": null,
"required": true,
"allow_null": false,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_unique": false,
"is_index": false,
"is_foreign_key": false,
"ref_table": null,
"ref_field": null,
"ref_on_update": null,
"ref_on_delete": null,
"picklist": null,
"validation": null,
"db_function": null,
"is_virtual": false,
"is_aggregate": false
},
{
"alias": null,
"name": "loan_points",
"label": "Loan Points",
"description": null,
"native": [],
"type": "integer",
"db_type": "int(11)",
"length": 11,
"precision": null,
"scale": null,
"default": null,
"required": true,
"allow_null": false,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_unique": false,
"is_index": false,
"is_foreign_key": false,
"ref_table": null,
"ref_field": null,
"ref_on_update": null,
"ref_on_delete": null,
"picklist": null,
"validation": null,
"db_function": null,
"is_virtual": false,
"is_aggregate": false
},
{
"alias": null,
"name": "loan_amount",
"label": "Loan Amount",
"description": null,
"native": [],
"type": "integer",
"db_type": "int(11)",
"length": 11,
"precision": null,
"scale": null,
"default": null,
"required": true,
"allow_null": false,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_unique": false,
"is_index": false,
"is_foreign_key": false,
"ref_table": null,
"ref_field": null,
"ref_on_update": null,
"ref_on_delete": null,
"picklist": null,
"validation": null,
"db_function": null,
"is_virtual": false,
"is_aggregate": false
},
{
"alias": null,
"name": "loan_contract_id",
"label": "Loan Contract Id",
"description": null,
"native": [],
"type": "integer",
"db_type": "int(11)",
"length": 11,
"precision": null,
"scale": null,
"default": null,
"required": true,
"allow_null": false,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_unique": false,
"is_index": false,
"is_foreign_key": false,
"ref_table": null,
"ref_field": null,
"ref_on_update": null,
"ref_on_delete": null,
"picklist": null,
"validation": null,
"db_function": null,
"is_virtual": false,
"is_aggregate": false
},
{
"alias": null,
"name": "late_payment_fee",
"label": "Late Payment Fee",
"description": null,
"native": [],
"type": "integer",
"db_type": "int(11)",
"length": 11,
"precision": null,
"scale": null,
"default": null,
"required": true,
"allow_null": false,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_unique": false,
"is_index": false,
"is_foreign_key": false,
"ref_table": null,
"ref_field": null,
"ref_on_update": null,
"ref_on_delete": null,
"picklist": null,
"validation": null,
"db_function": null,
"is_virtual": false,
"is_aggregate": false
},
{
"alias": null,
"name": "days_late_before_charged",
"label": "Days Late Before Charged",
"description": null,
"native": [],
"type": "integer",
"db_type": "int(11)",
"length": 11,
"precision": null,
"scale": null,
"default": null,
"required": true,
"allow_null": false,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_unique": false,
"is_index": false,
"is_foreign_key": false,
"ref_table": null,
"ref_field": null,
"ref_on_update": null,
"ref_on_delete": null,
"picklist": null,
"validation": null,
"db_function": null,
"is_virtual": false,
"is_aggregate": false
},
{
"alias": null,
"name": "loan_group",
"label": "Loan Group",
"description": null,
"native": [],
"type": "integer",
"db_type": "int(11)",
"length": 11,
"precision": null,
"scale": null,
"default": null,
"required": false,
"allow_null": true,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_unique": false,
"is_index": false,
"is_foreign_key": false,
"ref_table": null,
"ref_field": null,
"ref_on_update": null,
"ref_on_delete": null,
"picklist": null,
"validation": null,
"db_function": null,
"is_virtual": false,
"is_aggregate": false
},
{
"alias": null,
"name": "package_id",
"label": "Package Id",
"description": null,
"native": [],
"type": "integer",
"db_type": "int(11)",
"length": 11,
"precision": null,
"scale": null,
"default": null,
"required": true,
"allow_null": false,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_unique": false,
"is_index": false,
"is_foreign_key": false,
"ref_table": null,
"ref_field": null,
"ref_on_update": null,
"ref_on_delete": null,
"picklist": null,
"validation": null,
"db_function": null,
"is_virtual": false,
"is_aggregate": false
},
{
"alias": null,
"name": "next_payment_due_timestamp",
"label": "Next Payment Due Timestamp",
"description": null,
"native": [],
"type": "integer",
"db_type": "int(11)",
"length": 11,
"precision": null,
"scale": null,
"default": null,
"required": false,
"allow_null": true,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_unique": false,
"is_index": false,
"is_foreign_key": false,
"ref_table": null,
"ref_field": null,
"ref_on_update": null,
"ref_on_delete": null,
"picklist": null,
"validation": null,
"db_function": null,
"is_virtual": false,
"is_aggregate": false
},
{
"alias": null,
"name": "last_payment_id",
"label": "Last Payment Id",
"description": null,
"native": [],
"type": "integer",
"db_type": "int(11)",
"length": 11,
"precision": null,
"scale": null,
"default": null,
"required": false,
"allow_null": true,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_unique": false,
"is_index": false,
"is_foreign_key": false,
"ref_table": null,
"ref_field": null,
"ref_on_update": null,
"ref_on_delete": null,
"picklist": null,
"validation": null,
"db_function": null,
"is_virtual": false,
"is_aggregate": false
},
{
"alias": null,
"name": "origination_fee",
"label": "Origination Fee",
"description": null,
"native": [],
"type": "float",
"db_type": "float",
"length": null,
"precision": null,
"scale": null,
"default": null,
"required": false,
"allow_null": true,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_unique": false,
"is_index": false,
"is_foreign_key": false,
"ref_table": null,
"ref_field": null,
"ref_on_update": null,
"ref_on_delete": null,
"picklist": null,
"validation": null,
"db_function": null,
"is_virtual": false,
"is_aggregate": false
},
{
"alias": null,
"name": "late_fee_cache",
"label": "Late Fee Cache",
"description": null,
"native": [],
"type": "float",
"db_type": "float",
"length": null,
"precision": null,
"scale": null,
"default": null,
"required": false,
"allow_null": true,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_unique": false,
"is_index": false,
"is_foreign_key": false,
"ref_table": null,
"ref_field": null,
"ref_on_update": null,
"ref_on_delete": null,
"picklist": null,
"validation": null,
"db_function": null,
"is_virtual": false,
"is_aggregate": false
},
{
"alias": null,
"name": "interest_cache",
"label": "Interest Cache",
"description": null,
"native": [],
"type": "float",
"db_type": "float",
"length": null,
"precision": null,
"scale": null,
"default": null,
"required": true,
"allow_null": false,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_unique": false,
"is_index": false,
"is_foreign_key": false,
"ref_table": null,
"ref_field": null,
"ref_on_update": null,
"ref_on_delete": null,
"picklist": null,
"validation": null,
"db_function": null,
"is_virtual": false,
"is_aggregate": false
},
{
"alias": null,
"name": "payment_cache",
"label": "Payment Cache",
"description": null,
"native": [],
"type": "float",
"db_type": "float",
"length": null,
"precision": null,
"scale": null,
"default": null,
"required": false,
"allow_null": true,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_unique": false,
"is_index": false,
"is_foreign_key": false,
"ref_table": null,
"ref_field": null,
"ref_on_update": null,
"ref_on_delete": null,
"picklist": null,
"validation": null,
"db_function": null,
"is_virtual": false,
"is_aggregate": false
},
{
"alias": null,
"name": "principle_cache",
"label": "Principle Cache",
"description": null,
"native": [],
"type": "float",
"db_type": "float",
"length": null,
"precision": null,
"scale": null,
"default": null,
"required": false,
"allow_null": true,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_unique": false,
"is_index": false,
"is_foreign_key": false,
"ref_table": null,
"ref_field": null,
"ref_on_update": null,
"ref_on_delete": null,
"picklist": null,
"validation": null,
"db_function": null,
"is_virtual": false,
"is_aggregate": false
},
{
"alias": null,
"name": "balance_cache",
"label": "Balance Cache",
"description": null,
"native": [],
"type": "float",
"db_type": "float",
"length": null,
"precision": null,
"scale": null,
"default": null,
"required": false,
"allow_null": true,
"fixed_length": false,
"supports_multibyte": false,
"auto_increment": false,
"is_primary_key": false,
"is_unique": false,
"is_index": false,
"is_foreign_key": false,
"ref_table": null,
"ref_field": null,
"ref_on_update": null,
"ref_on_delete": null,
"picklist": null,
"validation": null,
"db_function": null,
"is_virtual": false,
"is_aggregate": false
},
{
"alias": null,
"name": "status_cache",
"label": "Status Cache",
"description": null,
"native": [],
"type": "text",
"db_type": "text",
"length": null,
"precision": null,
"scale": null,
"default": null,
"required": false,
"allow_null": true,
"fixed_length": false,
"supports_multibyte": true,
"auto_increment": false,
"is_primary_key": false,
"is_unique": false,
"is_index": false,
"is_foreign_key": false,
"ref_table": null,
"ref_field": null,
"ref_on_update": null,
"ref_on_delete": null,
"picklist": null,
"validation": null,
"db_function": null,
"is_virtual": false,
"is_aggregate": false
}
],
"related": [
{
"alias": null,
"name": "clients_by_client_id",
"label": "Clients By Client Id",
"description": null,
"native": [],
"type": "belongs_to",
"field": "client_id",
"is_virtual": true,
"ref_service_id": 10,
"ref_table": "clients",
"ref_field": "client_id",
"ref_on_update": null,
"ref_on_delete": null,
"junction_service_id": null,
"junction_table": null,
"junction_field": null,
"junction_ref_field": null,
"always_fetch": false,
"flatten": false,
"flatten_drop_prefix": false
}
],
"access": 31
}
Edit: Also, I have tried using the normal import and export tools in dreamfactory for the service and database, and it also does not bring through the virtual relationships.