Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[BUG] Cannot update the references (UUIDs) of a relation between two entities on different backends #123

Open
benatspo opened this issue Feb 2, 2021 · 4 comments
Open

[BUG] Cannot update the references (UUIDs) of a relation between two entities on different backends #123

benatspo opened this issue Feb 2, 2021 · 4 comments
Labels
bug Something isn't working

Comments

@benatspo
Copy link

benatspo commented Feb 2, 2021 •
edited by DavyLandman
Loading

Describe the bug
First test
I tried to update some relations between two entities on different backends. One is stored in a table in a relational database, and the other is in a collection in a document database. The relation between both entities has for cardinality [0..*]. The update failed with the exception:

authAll_1                   | PreEvent [slots=[], queryTime=Tue Feb 02 16:34:27 GMT 2021, dbUser=nemo, authenticated=true, id=19eb3a35-c33f-4be8-b171-9c6210784187, query={"query":"update Customers_migrated x where x.@id == ??UUID set {Orders +: [??UUID2]}","parameterNames":["UUID","UUID2"],"parameterTypes":["uuid","uuid"],"boundRows":[["00e9fdcf-2103-313a-8066-242d4a6b8cf8","69741b09-2414-3e44-baa5-ba473da679e3"]]}, invertedQuery=null, invertedNeeded=false]
typhon-polystore-service_1  | Got result from AUTH!!! 19eb3a35-c33f-4be8-b171-9c6210784187
documentdatabase_1          | {"t":{"$date":"2021-02-02T16:34:28.355+00:00"},"s":"I",  "c":"NETWORK",  "id":22943,   "ctx":"listener","msg":"Connection accepted","attr":{"remote":"172.18.0.2:39298","connectionId":411,"connectionCount":1}}
documentdatabase_1          | {"t":{"$date":"2021-02-02T16:34:28.355+00:00"},"s":"I",  "c":"NETWORK",  "id":51800,   "ctx":"conn411","msg":"client metadata","attr":{"remote":"172.18.0.2:39298","client":"conn411","doc":{"driver":{"name":"mongo-java-driver|sync","version":"3.12.0"},"os":{"type":"Linux","name":"Linux","architecture":"amd64","version":"4.19.76-linuxkit"},"platform":"Java/AdoptOpenJDK/1.8.0_282-b08"}}}
documentdatabase_1          | {"t":{"$date":"2021-02-02T16:34:28.358+00:00"},"s":"I",  "c":"NETWORK",  "id":22943,   "ctx":"listener","msg":"Connection accepted","attr":{"remote":"172.18.0.2:39302","connectionId":412,"connectionCount":2}}
documentdatabase_1          | {"t":{"$date":"2021-02-02T16:34:28.359+00:00"},"s":"I",  "c":"NETWORK",  "id":51800,   "ctx":"conn412","msg":"client metadata","attr":{"remote":"172.18.0.2:39302","client":"conn412","doc":{"driver":{"name":"mongo-java-driver|sync","version":"3.12.0"},"os":{"type":"Linux","name":"Linux","architecture":"amd64","version":"4.19.76-linuxkit"},"platform":"Java/AdoptOpenJDK/1.8.0_282-b08"}}}
documentdatabase_1          | {"t":{"$date":"2021-02-02T16:34:28.398+00:00"},"s":"I",  "c":"ACCESS",   "id":20250,   "ctx":"conn412","msg":"Successful authentication","attr":{"mechanism":"SCRAM-SHA-256","principalName":"mongoUser","authenticationDatabase":"admin","client":"172.18.0.2:39302"}}
typhonql-server_1           | java.lang.IllegalArgumentException: Invalid BSON document for an update. The document may not be empty.
typhonql-server_1           | (internal error)
documentdatabase_1          | {"t":{"$date":"2021-02-02T16:34:28.400+00:00"},"s":"I",  "c":"NETWORK",  "id":22944,   "ctx":"conn412","msg":"Connection ended","attr":{"remote":"172.18.0.2:39302","connectionId":412,"connectionCount":1}}
typhonql-server_1           |   at $typhonql$(|main://$typhonql$|)
typhonql-server_1           |
typhonql-server_1           | java.lang.IllegalArgumentException: Invalid BSON document for an update. The document may not be empty.

Query

{"query":"update Customers_migrated x where x.@id == ??UUID set {Orders +: [??UUID2]}","parameterNames":["UUID","UUID2"],"parameterTypes":["uuid","uuid"],"boundRows":[["00e9fdcf-2103-313a-8066-242d4a6b8cf8","69741b09-2414-3e44-baa5-ba473da679e3"]]}

Second test
We ran another test, using a relation having for cardinality [1]. The query is a bit different (using ":" and not "+:" for the "set" parameter.

Query

{"query":"update Order_Details_migrated x where x.@id == ??UUID set {Products: ??UUID2}","parameterNames":["UUID","UUID2"],"parameterTypes":["uuid","uuid"],"boundRows":[["249a9962-d7ae-3dc2-93b4-c95b96788f32","69ce2884-4280-31ba-8645-6d0ddb3f36fc"]]}

The second test returned the following exception:

authAll_1                   | PreEvent [slots=[], queryTime=Tue Feb 02 16:42:24 GMT 2021, dbUser=nemo, authenticated=true, id=6be80b84-d789-4def-b12a-67f0659e1c85, query={"query":"update Order_Details_migrated x where x.@id == ??UUID set {Products: ??UUID2}","parameterNames":["UUID","UUID2"],"parameterTypes":["uuid","uuid"],"boundRows":[["249a9962-d7ae-3dc2-93b4-c95b96788f32","69ce2884-4280-31ba-8645-6d0ddb3f36fc"]]}, invertedQuery=null, invertedNeeded=false]
typhon-polystore-service_1  | Got result from AUTH!!! 6be80b84-d789-4def-b12a-67f0659e1c85
documentdatabase_1          | {"t":{"$date":"2021-02-02T16:42:24.877+00:00"},"s":"I",  "c":"NETWORK",  "id":22943,   "ctx":"listener","msg":"Connection accepted","attr":{"remote":"172.18.0.2:39800","connectionId":419,"connectionCount":1}}
documentdatabase_1          | {"t":{"$date":"2021-02-02T16:42:24.877+00:00"},"s":"I",  "c":"NETWORK",  "id":51800,   "ctx":"conn419","msg":"client metadata","attr":{"remote":"172.18.0.2:39800","client":"conn419","doc":{"driver":{"name":"mongo-java-driver|sync","version":"3.12.0"},"os":{"type":"Linux","name":"Linux","architecture":"amd64","version":"4.19.76-linuxkit"},"platform":"Java/AdoptOpenJDK/1.8.0_282-b08"}}}
documentdatabase_1          | {"t":{"$date":"2021-02-02T16:42:24.880+00:00"},"s":"I",  "c":"NETWORK",  "id":22943,   "ctx":"listener","msg":"Connection accepted","attr":{"remote":"172.18.0.2:39804","connectionId":420,"connectionCount":2}}
documentdatabase_1          | {"t":{"$date":"2021-02-02T16:42:24.880+00:00"},"s":"I",  "c":"NETWORK",  "id":51800,   "ctx":"conn420","msg":"client metadata","attr":{"remote":"172.18.0.2:39804","client":"conn420","doc":{"driver":{"name":"mongo-java-driver|sync","version":"3.12.0"},"os":{"type":"Linux","name":"Linux","architecture":"amd64","version":"4.19.76-linuxkit"},"platform":"Java/AdoptOpenJDK/1.8.0_282-b08"}}}
documentdatabase_1          | {"t":{"$date":"2021-02-02T16:42:24.914+00:00"},"s":"I",  "c":"ACCESS",   "id":20250,   "ctx":"conn420","msg":"Successful authentication","attr":{"mechanism":"SCRAM-SHA-256","principalName":"mongoUser","authenticationDatabase":"admin","client":"172.18.0.2:39804"}}
documentdatabase_1          | {"t":{"$date":"2021-02-02T16:42:24.917+00:00"},"s":"I",  "c":"NETWORK",  "id":22944,   "ctx":"conn420","msg":"Connection ended","attr":{"remote":"172.18.0.2:39804","connectionId":420,"connectionCount":1}}
typhonql-server_1           | )
typhonql-server_1           | java.lang.RuntimeException: java.sql.BatchUpdateException: Error reading results 1
typhonql-server_1           | (internal error)
typhonql-server_1           |   at $typhonql$(|main://$typhonql$|)
typhonql-server_1           |
documentdatabase_1          | {"t":{"$date":"2021-02-02T16:42:24.917+00:00"},"s":"I",  "c":"NETWORK",  "id":22944,   "ctx":"conn419","msg":"Connection ended","attr":{"remote":"172.18.0.2:39800","connectionId":419,"connectionCount":0}}
typhonql-server_1           | java.lang.RuntimeException: java.sql.BatchUpdateException: Error reading results 1
typhonql-server_1           |   at nl.cwi.swat.typhonql.backend.mariadb.MariaDBEngine.lambda$2(MariaDBEngine.java:136)
typhonql-server_1           |   at java.util.ArrayList.forEach(ArrayList.java:1259)
typhonql-server_1           |   at nl.cwi.swat.typhonql.backend.rascal.TyphonSessionState.flush(TyphonSessionState.java:73)

NB
If we remember correctly, such an update worked in the past when both entities were stored in a relational database.

Thank you for your help
@benatspo benatspo added the bug Something isn't working label Feb 2, 2021
@tvdstorm
Copy link
Contributor

tvdstorm commented Feb 4, 2021

Do you have a TyphonML model for this? Is the relation containment?

@benatspo
Copy link
Author

benatspo commented Feb 4, 2021 •
edited
Loading

For sure, i'll share the model with you.
I've already shared the logs with Davy.

No containment relation. A "simple" [0..*]

to resume the scenario:

  • I migrated an entity from relational to document database
  • I inserted the data in the migrated entity
  • I tried to update the references from the migrated entity to the referenced relations of that migrated entity (here, our entity Customers has two relations [0..*] : to Orders, and to CustomerDemographics)
    BUT Bug while updating the references (same problem when migrating from document to relational because the migrated entity has an attribute containing ONE uuid, and not an array, and same problem with [1] relations "typhonql-server_1 | java.lang.RuntimeException: java.sql.BatchUpdateException: Error reading results 1")

The problematic reduced query:

{"query":"update Customers_migrated x where x.@id == ??UUID set {Orders+: [??UUID2]}","parameterNames":["UUID", "UUID2"],"parameterTypes":["uuid", "uuid"],"boundRows":[["776cc3f6-60ed-3332-896b-d75600374c64","ef14dcba-20f9-3c57-98d6-1e7ae2c12e34"],["776cc3f6-60ed-3332-896b-d75600374c64","407a8809-5ebd-3cab-87d5-035f4dd64bd9"],["776cc3f6-60ed-3332-896b-d75600374c64","958b7f2f-108f-370a-9ff0-bc62f35982d1"],["776cc3f6-60ed-3332-896b-d75600374c64","42566922-4734-38b8-814d-9765b90b1e03"],["776cc3f6-60ed-3332-896b-d75600374c64","552a69d5-f8c0-38ba-8299-8f019e80342a"],["776cc3f6-60ed-3332-896b-d75600374c64","aab2a14c-d8b1-32f4-9917-183d3f83304f"],["776cc3f6-60ed-3332-896b-d75600374c64","b18f2194-4c01-30d8-a00d-85549e398e5f"],["776cc3f6-60ed-3332-896b-d75600374c64","300a86bc-7da3-3905-8e6e-24919b9a0a0d"],["776cc3f6-60ed-3332-896b-d75600374c64","9b020ba9-a2e3-3b8e-b50a-4200bcdbe511"],["776cc3f6-60ed-3332-896b-d75600374c64","bbf5b17b-0e76-37cb-8959-a77dc4da0937"],["37b6a4d2-b0e0-3739-bd2a-090164ce95f5","e0dcc46b-4a8b-387f-a5ab-9cf24aa960d3"],["37b6a4d2-b0e0-3739-bd2a-090164ce95f5","545f43cd-e7aa-3e97-a929-431852b79e54"],["37b6a4d2-b0e0-3739-bd2a-090164ce95f5","f3211ccd-3c6b-3f57-9d83-6b0de166301d"],["37b6a4d2-b0e0-3739-bd2a-090164ce95f5","c7235be6-fdff-3238-af75-9200633b08b5"],["37b6a4d2-b0e0-3739-bd2a-090164ce95f5","87683d9b-8451-3036-a726-2567c9806d25"],["37b6a4d2-b0e0-3739-bd2a-090164ce95f5","b4b8724e-a551-33e8-a069-c7f53677a561"],["37b6a4d2-b0e0-3739-bd2a-090164ce95f5","a444d696-2994-35be-9e59-859a2bec3666"],["37b6a4d2-b0e0-3739-bd2a-090164ce95f5","e932207b-f8d3-389f-8baa-92a225bcc049"],["37b6a4d2-b0e0-3739-bd2a-090164ce95f5","e1d2ed6f-9dbe-32a3-b11c-3bb4a4427db7"],["f787937e-a038-3b50-956b-4c08d2f93e4e","66bafb48-c2ee-31d1-8d37-e3eeedb21150"],["f787937e-a038-3b50-956b-4c08d2f93e4e","7c8284c1-93fb-3c35-8b74-5629d6588dbf"],["f787937e-a038-3b50-956b-4c08d2f93e4e","b495c519-3fbe-30a6-834d-4f52fd6eaf09"],["f787937e-a038-3b50-956b-4c08d2f93e4e","a5a60da5-fe32-3252-b0db-96cfb1f30904"],["b4159942-962a-3108-9973-cc934ed6de7c","1f2a59bc-67d1-3c3a-a23e-c3485a20d4c0"],["b4159942-962a-3108-9973-cc934ed6de7c","3e582add-5758-3ef9-a599-6cc331eeee55"],["b4159942-962a-3108-9973-cc934ed6de7c","0fa93ff8-7c33-3e2d-8a2a-7e945f9b50f4"],["b4159942-962a-3108-9973-cc934ed6de7c","9f63327d-6a13-31c1-8172-8f67c8685408"],["b4159942-962a-3108-9973-cc934ed6de7c","db16292e-5c5a-3080-935e-50ac1dce27d7"],["b4159942-962a-3108-9973-cc934ed6de7c","1f92b95c-f1c3-3337-a997-57a37e6f8a43"],["b4159942-962a-3108-9973-cc934ed6de7c","e0a3a6c7-b4c3-3715-b3e3-c97099580559"],["b4159942-962a-3108-9973-cc934ed6de7c","036687ef-63e1-324c-85f4-ed51c7518284"],["b4159942-962a-3108-9973-cc934ed6de7c","d9d09c2f-e4ed-34e0-b8b4-5cebac471238"],["b4159942-962a-3108-9973-cc934ed6de7c","85bc02f5-c68c-3129-937b-3914a3d702e6"],["cb9ae42f-91b3-3ee7-b931-febfe7075161","f1062f2d-8f77-3cf0-b2da-080c2c67d4e9"],["cb9ae42f-91b3-3ee7-b931-febfe7075161","c4b68b63-c799-382b-af7e-68025632c54f"],["cb9ae42f-91b3-3ee7-b931-febfe7075161","0f75a8a9-d291-38d2-afee-23d7008f9771"],["cb9ae42f-91b3-3ee7-b931-febfe7075161","c4dfaffe-ed4e-3e7a-b389-f23c634cda1b"],["cb9ae42f-91b3-3ee7-b931-febfe7075161","5b540382-172d-31f2-ae8d-141f320e50d5"],["cb9ae42f-91b3-3ee7-b931-febfe7075161","571fe63a-4890-3cf7-beba-6e44f1bbc3ed"],["cb9ae42f-91b3-3ee7-b931-febfe7075161","ae6c29b6-df68-36cd-a787-8cc03f4e385f"],["cb9ae42f-91b3-3ee7-b931-febfe7075161","9c60ae93-8b86-3d6d-965e-a5f6986d1ad2"],["cb9ae42f-91b3-3ee7-b931-febfe7075161","7251e10a-c122-3914-b1f2-0fe67b7b7461"],["cb9ae42f-91b3-3ee7-b931-febfe7075161","860c3dea-ba28-317b-a210-66ee8e34cb4f"],["cb9ae42f-91b3-3ee7-b931-febfe7075161","ebc0674b-5c70-3519-9d14-b71e918a94df"],["4da9786b-eafc-38a7-85bf-6d112d96dc2f","239ea6ca-f3d7-33fd-9897-dc4aa1a886bd"],["4da9786b-eafc-38a7-85bf-6d112d96dc2f","b00c9872-3f13-33f9-91fe-a4bc9ba51e8d"],["4da9786b-eafc-38a7-85bf-6d112d96dc2f","9f345e17-3e35-3eb8-af00-00b97033cbb2"],["4da9786b-eafc-38a7-85bf-6d112d96dc2f","4fa21171-3952-314f-9628-288beb3a6aea"],["4da9786b-eafc-38a7-85bf-6d112d96dc2f","9723b2f8-dbb8-39cc-afab-80b8cb2be155"],["4da9786b-eafc-38a7-85bf-6d112d96dc2f","8c9a82fe-d41e-3058-99f6-d5efef9ff682"],["4da9786b-eafc-38a7-85bf-6d112d96dc2f","fadf0327-a298-33f4-a5c0-1905eb59a6b3"],["4da9786b-eafc-38a7-85bf-6d112d96dc2f","08be2cb0-248c-3a1e-888f-41a013439eae"],["4da9786b-eafc-38a7-85bf-6d112d96dc2f","a0601b9d-a1ce-3fc2-a4f9-1b116aefd6f3"],["4da9786b-eafc-38a7-85bf-6d112d96dc2f","e0d956aa-f33a-3146-8bce-b2af0ca979aa"],["4da9786b-eafc-38a7-85bf-6d112d96dc2f","1759d64c-3da3-365f-b807-ee93e743fcdb"],["4da9786b-eafc-38a7-85bf-6d112d96dc2f","ea2e8cea-54a4-345d-bc09-77158c3b5087"],["4da9786b-eafc-38a7-85bf-6d112d96dc2f","95c63526-64a1-3c60-9d5e-0f1cfc0fa398"],["4da9786b-eafc-38a7-85bf-6d112d96dc2f","ced3b070-f01b-3522-a86e-72355a7d8e85"],["f3c4486d-1ee6-37c8-bd26-b2e211fd7985","b3c34791-6f8e-39ce-969b-c4cd60c387ce"],["f3c4486d-1ee6-37c8-bd26-b2e211fd7985","eceed406-9a00-3e86-b921-471ff0af2f1e"],["f3c4486d-1ee6-37c8-bd26-b2e211fd7985","b0fdbc66-b9fa-3c5f-a5ea-0fcb0a50c5e6"],["1d5f7825-9d67-314a-aa1d-0a51a72f1a64","5c487d6a-2434-304a-8090-6b75fa68a81b"],["1d5f7825-9d67-314a-aa1d-0a51a72f1a64","c199b984-34d0-3ce4-90d3-682396610401"],["9d34d202-e33a-3fce-9d13-798354562d31","e0dd6d23-f913-3690-ad98-90228bfa288c"],["9d34d202-e33a-3fce-9d13-798354562d31","e0bb4cb5-fc2e-3e8e-8164-28a2ad40b753"]]}

@benatspo
Copy link
Author

benatspo commented Feb 4, 2021 •
edited by tvdstorm
Loading

Model before migration:

entity "CustomerDemographics" {
   "CustomerTypeID" : string[10]
   "CustomerDesc" : text
   "Customers" -> "Customers"[0..*]
}

entity "Orders" {
   "OrderDate" : datetime
   "RequiredDate" : datetime
   "ShippedDate" : datetime
   "Freight" : float
   "ShipName" : string[40]
   "ShipAddress" : string[60]
   "ShipCity" : string[15]
   "ShipRegion" : string[15]
   "ShipPostalCode" : string[10]
   "ShipCountry" : string[15]
   "Customers" -> "Customers"[0..1]
   "Employees" -> "Employees"[0..1]
   "Shippers" -> "Shippers"[0..1]
   "Order_Details" -> "Order_Details"."Order_Details.Orders"[0..*]
}

entity "Products" {
   "ProductName" : string[40]
   "QuantityPerUnit" : string[20]
   "UnitPrice" : float
   "UnitsInStock" : int
   "UnitsOnOrder" : int
   "ReorderLevel" : int
   "Discontinued" : string[5]
   "Categories" -> "Categories"[0..1]
   "Suppliers" -> "Suppliers"[0..1]
   "Order_Details" -> "Order_Details"."Order_Details.Products"[0..*]
}

entity "Suppliers" {
   "CompanyName" : string[40]
   "ContactName" : string[30]
   "ContactTitle" : string[30]
   "Address" : string[60]
   "City" : string[15]
   "Region" : string[15]
   "PostalCode" : string[10]
   "Country" : string[15]
   "Phone" : string[24]
   "Fax" : string[24]
   "HomePage" : text
   "Products" -> "Products"."Products.Suppliers"[0..*]
}

entity "Customers" {
   "CustomerID" : string[5]
   "CompanyName" : string[40]
   "ContactName" : string[30]
   "ContactTitle" : string[30]
   "Address" : string[60]
   "City" : string[15]
   "Region" : string[15]
   "PostalCode" : string[10]
   "Country" : string[15]
   "Phone" : string[24]
   "Fax" : string[24]
   "Orders" -> "Orders"."Orders.Customers"[0..*]
   "CustomerDemographics" -> "CustomerDemographics"."CustomerDemographics.Customers"[0..*]
}

entity "Categories" {
   "CategoryName" : string[15]
   "Description" : text
   "Picture" : text
   "Products" -> "Products"."Products.Categories"[0..*]
}

entity "Shippers" {
   "CompanyName" : string[40]
   "Phone" : string[24]
   "Orders" -> "Orders"."Orders.Shippers"[0..*]
}

entity "Employees" {
   "LastName" : string[20]
   "FirstName" : string[10]
   "Title" : string[30]
   "TitleOfCourtesy" : string[25]
   "BirthDate" : datetime
   "HireDate" : datetime
   "Address" : string[60]
   "City" : string[15]
   "Region" : string[15]
   "PostalCode" : string[10]
   "Country" : string[15]
   "HomePhone" : string[24]
   "Extension" : string[4]
   "Photo" : text
   "Notes" : text
   "PhotoPath" : string[255]
   "Salary" : float
   "Employees" -> "Employees"[0..1]
   "Orders" -> "Orders"."Orders.Employees"[0..*]
   "Employees_1" -> "Employees"."Employees.Employees"[0..*]
   "Territories" -> "Territories"[0..*]
}

entity "Region" {
   "RegionID" : int
   "RegionDescription" : string[50]
   "Territories" -> "Territories"."Territories.Region"[0..*]
}

entity "Order_Details" {
   "UnitPrice" : float
   "Quantity" : int
   "Discount" : float
   "Products" -> "Products"[1]
   "Orders" -> "Orders"[1]
}

entity "Territories" {
   "TerritoryID" : string[20]
   "TerritoryDescription" : string[50]
   "Region" -> "Region"[1]
   "Employees" -> "Employees"."Employees.Territories"[0..*]
}

entity "User" {
	"id" : string[32]
	"name" : string[32]
	"paymentsDetails" : string
	"address" -> "UserAddress"[0..*]
}

entity UserAddress {
	streetName: string[32]
	streetNumber: bigint
	zipcode: string[32]
	city: string[32]
	country: string[32]
	user -> "User"."User.address"[1]
}

relationaldb RelationalDatabase {
   tables {
      table {
         "CustomerDemographics" : "CustomerDemographics"
         idSpec ("CustomerTypeID")
      }
      table {
         "Orders" : "Orders"
         index "index_0" {
            attributes ("OrderDate")
         }
      }
      table {
         "Products" : "Products"
         index "index_1" {
            attributes ("ProductName")
         }
      }
      table {
         "Suppliers" : "Suppliers"
         index "index_2" {
            attributes ("CompanyName")
         }
      }
      table {
         "Categories" : "Categories"
         index "index_4" {
            attributes ("CategoryName")
         }
      }
      table {
         "Shippers" : "Shippers"
      }
      table {
         "Employees" : "Employees"
         index "index_5" {
            attributes ("LastName")
         }
      }
      table {
         "Region" : "Region"
         idSpec ("RegionID")
      }
      table {
         "Order_Details" : "Order_Details"
      }
      table {
         "Territories" : "Territories"
         idSpec ("TerritoryID")
      }
	  table {
			"User" : "User"
	  }
	  table {
			"UserAddress" : "UserAddress"
	  }
	  table {
         	"Customers" : "Customers"
	  }
   }
}

documentdb DocumentDatabase{
}
changeOperators [
	migrate Customers to DocumentDatabase
]

Model after migration:

entity "CustomerDemographics" {
   "CustomerTypeID" : string[10]
   "CustomerDesc" : text
   "Customers" -> "Customers"[0..*]
}

entity "Orders" {
   "OrderDate" : datetime
   "RequiredDate" : datetime
   "ShippedDate" : datetime
   "Freight" : float
   "ShipName" : string[40]
   "ShipAddress" : string[60]
   "ShipCity" : string[15]
   "ShipRegion" : string[15]
   "ShipPostalCode" : string[10]
   "ShipCountry" : string[15]
   "Customers" -> "Customers"[0..1]
   "Employees" -> "Employees"[0..1]
   "Shippers" -> "Shippers"[0..1]
   "Order_Details" -> "Order_Details"."Order_Details.Orders"[0..*]
}

entity "Products" {
   "ProductName" : string[40]
   "QuantityPerUnit" : string[20]
   "UnitPrice" : float
   "UnitsInStock" : int
   "UnitsOnOrder" : int
   "ReorderLevel" : int
   "Discontinued" : string[5]
   "Categories" -> "Categories"[0..1]
   "Suppliers" -> "Suppliers"[0..1]
   "Order_Details" -> "Order_Details"."Order_Details.Products"[0..*]
}

entity "Suppliers" {
   "CompanyName" : string[40]
   "ContactName" : string[30]
   "ContactTitle" : string[30]
   "Address" : string[60]
   "City" : string[15]
   "Region" : string[15]
   "PostalCode" : string[10]
   "Country" : string[15]
   "Phone" : string[24]
   "Fax" : string[24]
   "HomePage" : text
   "Products" -> "Products"."Products.Suppliers"[0..*]
}

entity "Customers_migrated" {
   "CustomerID" : string[5]
   "CompanyName" : string[40]
   "ContactName" : string[30]
   "ContactTitle" : string[30]
   "Address" : string[60]
   "City" : string[15]
   "Region" : string[15]
   "PostalCode" : string[10]
   "Country" : string[15]
   "Phone" : string[24]
   "Fax" : string[24]
   "Orders" -> "Orders"."Orders.Customers"[0..*]
   "CustomerDemographics" -> "CustomerDemographics"."CustomerDemographics.Customers"[0..*]
}

entity "Categories" {
   "CategoryName" : string[15]
   "Description" : text
   "Picture" : text
   "Products" -> "Products"."Products.Categories"[0..*]
}

entity "Shippers" {
   "CompanyName" : string[40]
   "Phone" : string[24]
   "Orders" -> "Orders"."Orders.Shippers"[0..*]
}

entity "Employees" {
   "LastName" : string[20]
   "FirstName" : string[10]
   "Title" : string[30]
   "TitleOfCourtesy" : string[25]
   "BirthDate" : datetime
   "HireDate" : datetime
   "Address" : string[60]
   "City" : string[15]
   "Region" : string[15]
   "PostalCode" : string[10]
   "Country" : string[15]
   "HomePhone" : string[24]
   "Extension" : string[4]
   "Photo" : text
   "Notes" : text
   "PhotoPath" : string[255]
   "Salary" : float
   "Employees" -> "Employees"[0..1]
   "Orders" -> "Orders"."Orders.Employees"[0..*]
   "Employees_1" -> "Employees"."Employees.Employees"[0..*]
   "Territories" -> "Territories"[0..*]
}

entity "Region" {
   "RegionID" : int
   "RegionDescription" : string[50]
   "Territories" -> "Territories"."Territories.Region"[0..*]
}

entity "Order_Details" {
   "UnitPrice" : float
   "Quantity" : int
   "Discount" : float
   "Products" -> "Products"[1]
   "Orders" -> "Orders"[1]
}

entity "Territories" {
   "TerritoryID" : string[20]
   "TerritoryDescription" : string[50]
   "Region" -> "Region"[1]
   "Employees" -> "Employees"."Employees.Territories"[0..*]
}

entity "User" {
	"id" : string[32]
	"name" : string[32]
	"paymentsDetails" : string
	"address" -> "UserAddress"[0..*]
}

entity UserAddress {
	streetName: string[32]
	streetNumber: bigint
	zipcode: string[32]
	city: string[32]
	country: string[32]
	user -> "User"."User.address"[1]
}

relationaldb RelationalDatabase {
   tables {
      table {
         "CustomerDemographics" : "CustomerDemographics"
         idSpec ("CustomerTypeID")
      }
      table {
         "Orders" : "Orders"
         index "index_0" {
            attributes ("OrderDate")
         }
      }
      table {
         "Products" : "Products"
         index "index_1" {
            attributes ("ProductName")
         }
      }
      table {
         "Suppliers" : "Suppliers"
         index "index_2" {
            attributes ("CompanyName")
         }
      }
      table {
         "Categories" : "Categories"
         index "index_4" {
            attributes ("CategoryName")
         }
      }
      table {
         "Shippers" : "Shippers"
      }
      table {
         "Employees" : "Employees"
         index "index_5" {
            attributes ("LastName")
         }
      }
      table {
         "Region" : "Region"
         idSpec ("RegionID")
      }
      table {
         "Order_Details" : "Order_Details"
      }
      table {
         "Territories" : "Territories"
         idSpec ("TerritoryID")
      }
	  table {
			"User" : "User"
	  }
	  table {
			"UserAddress" : "UserAddress"
	  }
   }
}

documentdb DocumentDatabase{
      collections {
         "Customers_migrated" : "Customers_migrated"
      }
}

@tvdstorm
Copy link
Contributor

tvdstorm commented Feb 4, 2021

Hmm. The first query generates a (spurious) empty update:

Script: script([
    step(
      "DocumentDatabase",
      mongo(findAndUpdateOne("DocumentDatabase","Customers_migrated","{\"_id\": \"${UUID}\"}","{}")),
      ()),
    step(
      "DocumentDatabase",
      mongo(findAndUpdateOne("DocumentDatabase","Customers_migrated","{\"_id\": \"${UUID}\"}","{\"$addToSet\": {\"Orders\": {\"$each\": [\"${UUID2}\"]}}}")),
      ()),
    step(
      "RelationalDatabase",
      sql(executeStatement("RelationalDatabase","delete from `Customers_migrated.Orders-Orders.Customers` \nwhere (`Customers_migrated.Orders-Orders.Customers`.`Orders.Customers`) = (${UUID2});")),
      ()),
    step(
      "RelationalDatabase",
      sql(executeStatement("RelationalDatabase","insert into `Customers_migrated.Orders-Orders.Customers` (`Orders.Customers`, `Customers_migrated.Orders`) \nvalues (${UUID2}, ${UUID});")),
      ()),
    finish()
  ])

tvdstorm added a commit that referenced this issue Feb 4, 2021
@tvdstorm
Updating the original findAndUpdate instead of appending a new one (#123
4eec774 
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@tvdstorm @benatspo