[BUG] Creating an opposite relation between entities defined in two different relational databases causes severe problems

Question

[BUG] Creating an opposite relation between entities defined in two different relational databases causes severe problems

Closed this issue 4 years ago · 3 comments

Hi,
This is my ML model:

entity Product{
    id : string[32]
    name : string[32]
    description : string[32]
    orders -> OrderProduct[0..*]
}

entity OrderProduct{
    id : string[32]
    product_date : string[32]
    totalAmount : int
    products -> Product.products[1]
}

relationaldb RelationalDatabase2 {
	tables {
		table {
	            OrderDB : OrderProduct
	            index orderIndex {
	                attributes ("OrderProduct.id")
	            }
	            idSpec ("OrderProduct.id")
	        }
        }
}
 
relationaldb RelationalDatabase{
    tables{
        table {
            ProductDB : Product
            index productIndex{
                attributes ('Product.name')
            }
            idSpec ('Product.name')
        }
    }
}

As you can see, two different relational databases are defined: RelationalDatabase storing the Product entity and RelationalDatabase2 storing OrderProduct entity. An opposite relation is defined between both entities.

I encountered a problem while inserting an 'OrderProduct' with a reference to the related Product.
This is my use case:

I inserted a new product: insert Product {}
I then tried to insert a new orderproduct by referencing the previously inserted product: insert OrderProduct { products: #9ab3ce44-3300-4aa4-a187-a0372f6a8399}
Unfortunately the second insert statement returned this error:

java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: (conn=22) Table 'RelationalDatabase.OrderProduct.products-Product.products' doesn't exist
        at nl.cwi.swat.typhonql.backend.mariadb.MariaDBEngine$2.performUpdate(MariaDBEngine.java:98)
        at nl.cwi.swat.typhonql.backend.UpdateExecutor.executeUpdateOperation(UpdateExecutor.java:37)
        at nl.cwi.swat.typhonql.backend.UpdateExecutor.executeUpdateOperation(UpdateExecutor.java:62)
        at nl.cwi.swat.typhonql.backend.UpdateExecutor.lambda$0(UpdateExecutor.java:30)
        at nl.cwi.swat.typhonql.backend.Runner.lambda$1(Runner.java:27)
        at nl.cwi.swat.typhonql.backend.Runner.executeUpdates(Runner.java:30)
        at nl.cwi.swat.typhonql.backend.rascal.TyphonSession.lambda$8(TyphonSession.java:255)
        at nl.cwi.swat.typhonql.backend.rascal.Operations$1.call(Operations.java:56)
        at org.rascalmpl.semantics.dynamic.Expression$CallOrTree.interpret(Expression.java:533)
        at org.rascalmpl.semantics.dynamic.Statement$Expression.interpret(Statement.java:365)
        at org.rascalmpl.semantics.dynamic.Statement$NonEmptyBlock.interpret(Statement.java:759)
        at org.rascalmpl.interpreter.utils.Cases.matchAndEval(Cases.java:423)
        at org.rascalmpl.interpreter.utils.Cases$DefaultBlock.matchAndEval(Cases.java:261)
        at org.rascalmpl.interpreter.utils.Cases$NodeCaseBlock.tryCases(Cases.java:394)
        at org.rascalmpl.interpreter.utils.Cases$NodeCaseBlock.matchAndEval(Cases.java:382)
        at org.rascalmpl.semantics.dynamic.Statement$Switch.interpret(Statement.java:900)
        at org.rascalmpl.semantics.dynamic.Statement$NonEmptyBlock.interpret(Statement.java:759)
        at org.rascalmpl.semantics.dynamic.Statement$For.interpret(Statement.java:468)
        at org.rascalmpl.interpreter.result.RascalFunction.runBody(RascalFunction.java:400)
        at org.rascalmpl.interpreter.result.RascalFunction.call(RascalFunction.java:333)
        at org.rascalmpl.semantics.dynamic.Expression$CallOrTree.interpret(Expression.java:533)
        at org.rascalmpl.semantics.dynamic.Statement$Expression.interpret(Statement.java:365)
        at org.rascalmpl.semantics.dynamic.Statement$Assignment.interpret(Statement.java:205)
        at org.rascalmpl.semantics.dynamic.Statement$NonEmptyBlock.interpret(Statement.java:759)
        at org.rascalmpl.semantics.dynamic.Statement$IfThenElse.interpret(Statement.java:679)
        at org.rascalmpl.semantics.dynamic.Statement$NonEmptyBlock.interpret(Statement.java:759)
        at org.rascalmpl.semantics.dynamic.Statement$IfThen.interpret(Statement.java:604)
        at org.rascalmpl.interpreter.result.RascalFunction.runBody(RascalFunction.java:400)
        at org.rascalmpl.interpreter.result.RascalFunction.call(RascalFunction.java:333)
        at org.rascalmpl.interpreter.result.OverloadedFunction.callWith(OverloadedFunction.java:416)
        at org.rascalmpl.interpreter.result.OverloadedFunction.call(OverloadedFunction.java:394)
        at org.rascalmpl.semantics.dynamic.Expression$CallOrTree.interpret(Expression.java:533)
        at org.rascalmpl.semantics.dynamic.Statement$Expression.interpret(Statement.java:365)
        at org.rascalmpl.semantics.dynamic.Statement$Return.interpret(Statement.java:783)
        at org.rascalmpl.interpreter.result.RascalFunction.runBody(RascalFunction.java:400)
        at org.rascalmpl.interpreter.result.RascalFunction.call(RascalFunction.java:333)
        at org.rascalmpl.interpreter.result.OverloadedFunction.callWith(OverloadedFunction.java:416)
        at org.rascalmpl.interpreter.result.OverloadedFunction.call(OverloadedFunction.java:394)
        at org.rascalmpl.interpreter.result.OverloadedFunction.call(OverloadedFunction.java:385)
        at org.rascalmpl.interpreter.Evaluator.call(Evaluator.java:710)
        at org.rascalmpl.interpreter.Evaluator.call(Evaluator.java:694)
        at org.rascalmpl.interpreter.Evaluator.call(Evaluator.java:683)
        at nl.cwi.swat.typhonql.client.XMIPolystoreConnection.lambda$4(XMIPolystoreConnection.java:215)
        at org.rascalmpl.util.ConcurrentSoftReferenceObjectPool.useAndReturn(ConcurrentSoftReferenceObjectPool.java:96)
        at nl.cwi.swat.typhonql.client.XMIPolystoreConnection.evaluateUpdate(XMIPolystoreConnection.java:210)
        at nl.cwi.swat.typhonql.client.XMIPolystoreConnection.executeUpdate(XMIPolystoreConnection.java:204)
        at engineering.swat.typhonql.server.QLRestServer.handleCommand(QLRestServer.java:208)
        at engineering.swat.typhonql.server.QLRestServer.handle(QLRestServer.java:244)
        at engineering.swat.typhonql.server.QLRestServer.access$300(QLRestServer.java:46)
        at engineering.swat.typhonql.server.QLRestServer$1.doPost(QLRestServer.java:276)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
        at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:755)
        at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:547)
        at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
        at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1297)
        at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
        at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:485)
        at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
        at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1212)
        at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
        at org.eclipse.jetty.server.handler.gzip.GzipHandler.handle(GzipHandler.java:767)
        at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
        at org.eclipse.jetty.server.Server.handle(Server.java:500)
        at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:383)
        at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:547)
        at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:375)
        at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:270)
        at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
        at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
        at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)
        at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:806)
        at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:938)
        at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLSyntaxErrorException: (conn=22) Table 'RelationalDatabase.OrderProduct.products-Product.products' doesn't exist
        at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:242)
        at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:171)
        at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:248)
        at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:230)
        at org.mariadb.jdbc.ClientSidePreparedStatement.execute(ClientSidePreparedStatement.java:157)
        at org.mariadb.jdbc.ClientSidePreparedStatement.executeUpdate(ClientSidePreparedStatement.java:192)
        at nl.cwi.swat.typhonql.backend.mariadb.MariaDBEngine$2.performUpdate(MariaDBEngine.java:96)
        ... 73 more
Caused by: java.sql.SQLException: Table 'RelationalDatabase.OrderProduct.products-Product.products' doesn't exist
        at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1594)
        at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1453)
        at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1415)
        at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:289)
        at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:221)
        ... 76 more

By observing the physical database structures within the two mariadb databases, I noticed two 'join' tables were created (one by database) in order to store the relations between orders and orderproducts. It seems that the QL server get mixed up with these join tables.

Answer 1 · 2020-06-02T12:45:36.000Z

and this is my TDL file:

import schema.xmi
import RelationalDatabase.tdl
import RelationalDatabase2.tdl
import DocumentDatabase.tdl
import dbTypes.tdl
containertype Docker
clustertype DockerCompose
platformtype localhost
platform platformName : localhost {
	cluster clusterName : DockerCompose {
		application Polystore {
			container relationaldatabase1 : Docker {
				deploys RelationalDatabase
				ports {
					target = 3306 ;
				}
			}
			container relationaldatabase2 : Docker {
				deploys RelationalDatabase2
				ports {
					target = 3306 ;
				}
			}
			container documentdatabase : Docker {
				deploys DocumentDatabase
				ports {
					target = 27017 ;
				}
			}
		}
	}
}

Answer 2 · 2020-06-03T14:16:04.000Z

Hi, i don't understand the typhonML model. TyphonQL complains about products-products, but the model has orders. And also, I don't see use of opposite syntax. Can you explain?

Answer 3 · 2020-06-04T08:47:31.000Z

Really sorry, after verification, the problem was the ML schema which does not represent what I wanted... I corrected the schema, replayed the scenario and everything was fine.

I close this issue.