Forum: Building VoltDB Clients

Post: MyJSQLView

MyJSQLView
danap
May 31, 2010
The open source MyJSQLView SQL client relies on a JDBC standard API for executing
queries in a database. I see from the faqs that this is not available, but queries
should be executed via stored procedures. The faq also indicates that ad hoc queries
are not beneficial because they "cannot be used to modify the schema or perform other
arbitrary actions through interactive SQL statements." I understand the need in a
distributed database to control schema modifications, but "arbitrary actions" isn't
that a bit vague. Would not a delete of all records in a table be classified as
"arbitrary".? Now isn't your real reason for not wishing ad-hoc queries is so that
queries may be controlled on the database through the stored procedures which could
then be optimized?
If this is the case I assume that there must be, I have not reviewed the documentation
yet, a generic set of stored procedures that I could use to perform most of the
functions that the MyJSQLView client executes, like sort, search, delete, insert, &
update records. Or is a generic client like MyJSQLView, which really does generate
ad-hoc queries, not appropriate for the VoltDB.
Dana M. Proctor
MyJSQLView Project Manager
Interesting Question
jhugg
May 31, 2010
Any SQL that VoltDB supports in stored procedures, VoltDB also supports in our ad-hoc SQL. We don't recommend running ad-hoc sql in your applications because our SQL parser, planner and executor is optimized for repeat business at some computational expense for each statement. It can take anywhere from half a second to several seconds to prepare a statement in VoltDB 1.0.01, limiting the usefulness of ad-hoc execution. Additionally, all ad-hoc statements run in auto-commit mode and as multi-partition transactions. Our ad-hoq SQL basically exists for debugging, testing, management and emergencies.
Also, as you point out, our ad-hoc SQL is not supported in a JDBC driver. We'd like to add one in the future, but it is not currently scheduled due to all of the other work we are doing.
The good news is that search, sort, delete, insert, etc., should all work over ad-hoc. What doesn't work is SQL to support getting the schema of tables, listing tables or altering tables. You could probably get the schema from a "select * from table limit 1" statement. I think the list of tables might be available though the @Statistics procedure as well.
So something like MyJSQLView might work, but it would either have to support the existing interface for VoltDB, or it would involve writing a JDBC driver for our ad-hoc queries. If you are interested in this work, please let us know and we'll try to provide some support.
Re:Interesting Question
danap
Jun 1, 2010
Any SQL that VoltDB supports in stored procedures, VoltDB also supports in our ad-hoc SQL. We don't recommend running ad-hoc sql in your applications because our SQL parser, planner and executor is optimized for repeat business at some computational expense for each statement. It can take anywhere from half a second to several seconds to prepare a statement in VoltDB 1.0.01, limiting the usefulness of ad-hoc execution. Additionally, all ad-hoc statements run in auto-commit mode and as multi-partition transactions. Our ad-hoq SQL basically exists for debugging, testing, management and emergencies.
Also, as you point out, our ad-hoc SQL is not supported in a JDBC driver. We'd like to add one in the future, but it is not currently scheduled due to all of the other work we are doing.
The good news is that search, sort, delete, insert, etc., should all work over ad-hoc. What doesn't work is SQL to support getting the schema of tables, listing tables or altering tables. You could probably get the schema from a "select * from table limit 1" statement. I think the list of tables might be available though the @Statistics procedure as well.
So something like MyJSQLView might work, but it would either have to support the existing interface for VoltDB, or it would involve writing a JDBC driver for our ad-hoc queries. If you are interested in this work, please let us know and we'll try to provide some support.


The good news aspects of the response indicates that it is worth taking a
closer look at the existing interface, and your ad-hoc queries. It looks like
the basic functionality of MyJSQLView search, sort, delete, etc., could be
supported for the VoltDB. MyJSQLView already uses SELECT * FROM 'table' LIMIT 1
as the default method to determine schema and if the @Statistics list the
tables then that will get the application up and is half the battle to getting
connected to a database. If the ad-hoc queries method is used I do not see
an all encompassing JDBC driver, but essential functions only that MyJSQLView
needs. I will review the documentation and see what can be done. Thank you
for the response.