Importing virtual relationships while using SQLite or MySql


#1

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, CONSTRAINT db_virtual_relationship_ref_service_id_foreign FOREIGN KEY (ref_service_id) REFERENCES service (id) ON DELETE CASCADE) (SQL: insert into db_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.