search

typhon-project / typhonql

Typhon Query Language
Eclipse Public License 2.0
4 stars 1 forks source link

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

Open benatspo opened 3 years ago

benatspo commented 3 years ago

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

tvdstorm commented 3 years ago

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

benatspo commented 3 years ago

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:

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 commented 3 years ago

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 commented 3 years ago

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()
  ])