Using restsql with several different databases

[ghost]

Hi,

I am investigating if restsql is a suitable solution for us, and I am wondering if it is possible to make restsql handle more databases at the same time.
We have a dynamically increasing number of projects that all have their own databases (on a few different servers) but with the same database schema. (It is because they all use the same webtool for presenting the data.) The data is sent to the database automatically by a test automation framework we use, and we would like a single application handling the requests from all of the projects. (~1500 testers)
So, I am wondering if it is possible to tell restsql dynamically which database server should be used.

Thanks,
Áron

[restsql]

Hello Áron. Thanks for your interest in the framework.

It currently does not have multi-server capability but I see that as an easy extension. A JDBC connection is opened (and closed) per request; there is no connection persistence or pooling. The connection URL is currently driven from the main conf file, restsql.properties. We could add an optional http query parameter on a request indicating the server/connection, e.g. _connection={value}. If the connection parameter is present, then the framework uses it instead of the statically configured connection. Request parameters are parsed into a request object. The sql resource and builder objects use the request object to execute it.

The _connection value could be a named connection or a server ip and port. I'm guessing you'd lean towards the latter since it's more dynamic. Can we count on the database name and type being consistent across servers? If it's not then we need to include more info and do some lookup for the appropriate driver.

RestSQL does not have documentation for framework extenders. It's something I'd like to add in the near term. But I think you'll find the organization straightforward. It has a test project with a solid suite of unit and component-level tests.

Regards,
Mark

[ghost]

Hello Mark,

Thanks for your quick answer!
Unfortunately, the only consistent thing is the db schema. So neither the
server url, nor the database name could be determined at the time of
development. But it would be appreciated if these could be set as HTTP
parameters as well.

BR,
Áron

[restsql]

Ok, the entire jdbc URL can be sent then as the parameter.

So there's our quick design. Are you interested in implementing it? :>)

[ghost]

Yeah, well, my time is not completely planned by just me, and we are
looking into alternatives currently. If we go with restSQL, I would love to
do this. But the decision is not just mine to make.
We definitely want something like restSQL; question is whether it can be
customized to satisfy all our requirements, which are still very high-level
ones, so might take 2-3 weeks to have a final decision.
//Áron

[ghost]

I am in the process of evaluation, so let me share my findings and questions with you in separate threads. :)
To this thread, I would like to add that if we decide to use restSQL, my team would be happy to implement this multi-server feature. :)

[alfredoport]

Mark, have you done any work on this direction? I wouldn't mind trying to implement this with some direction from you.

[restsql]

Hello alredoport. No I haven't worked on this.

Do you have the same use case as the original requester, aronhamvas? Dynamic selection of a database instance/server per request?

Sure, I'm happy to help.

I like my original suggestion of a _connection query parameter, but it's value shouldn't be the full URL. Too verbose, and not enough to actually start a connection. Instead the value would be a unique name for the connection.

Connection metadata is currently in the restsql.properties file. Here are the defaults:

# database.driverClassName=x.x.x
# database.url=jdbc:etc:etc
# database.user=userName
# database.password=password
database.driverClassName=com.mysql.jdbc.Driver
database.url=jdbc:mysql://localhost:3306?noDatetimeStringSync=true&zeroDateTimeBehavior=convertToNull
database.user=root
database.password=root

I think connections metadata can be promoted to a first class citizen. In the process, we can make this easier to change (enable dynamic reload like we have on sql resources), and more visible. Running with this, from restsql.properties we remove the db metadata and add:

databaseConnections.config=/etc/opt/business/restsql/databaseConnections.xml

databaseConnections.xml would have one or more connection definitions. To make this easier on users, the default connection is used for all sql resources. To use a non-default, for all requests we add a connection attribute to the database element of the sql resource, e.g.:

<metadata>
   <database default="sakila" connection="analytics"/>
   <table name="...
</metadata>

It's not the most intuitive. I had to look at the xsd to refresh my understanding of what default does. Currently the default attribute is the schema name used by the sql generator if it is omitted in the definition file. A more explicit attribute name would have been defaultSchema.

If there's the dynamic use case, then the connection could instead be provided on the request url.

The databaseConnections.xml could look like this:

<databaseConnections>
   <databaseConnection default="true" name="op1" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://op.dbhost:3306?noDatetimeStringSync=true&zeroDateTimeBehavior=convertToNull" user="op" password="***" />
   <databaseConnection default="false" name="op2" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://op.dbhost:4306?noDatetimeStringSync=true&zeroDateTimeBehavior=convertToNull" user="op" password="***" />
   <databaseConnection default="false" name="analytics" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://customer_stg.dbhost:3306?noDatetimeStringSync=true&zeroDateTimeBehavior=convertToNull" user="root" password="***" />
</databaseConnections

Let's say there's a resource product, and no connection is defined. If we want to make a request from the product catalog from op1, the default connection, say by some column named category:

http://mybusiness.com/restsql/res/product?category=chair

If we want from op2:

http://mybusiness.com/restsql/res/product?category=chair&_connection=op2

Thoughts?

Mark