Forum: Building VoltDB Applications

Post: CRUD Scaffolding & Stored Procedure Calls over ODBC

CRUD Scaffolding & Stored Procedure Calls over ODBC
sylverboss
Jul 6, 2010
Hello,

I came across VoltDB which seems very promising. However I'm a bit skeptical with the fact that we have to write the logic within the DB itself using Java classes when most people these days (I think) are using all sort of frameworks with built-in CRUD generators & dynamic scaffolding (Django is my favorite).

I think (imho) VoltDB could have a great success if it had some sort of stored procedures generator with basic CRUD methods. This is simply because it saves tremendous time and when you are used to work with a framework that has scaffolding you don't want to go back to the time when you were writing every single sql queries, that's for sure.

The JSON interface is a good move, but for applications I'm working on (for telcos) where you need to give customers the ability to sort/download call records, json/http combo is a bit limited. Why not use what's widely available for seamless integration .. my 2 cents: stored procedure calls over odbc.

What do you think ?
SB
re: CRUD + VoltDB
tcallaghan
Jul 6, 2010
SB,
You can currently create simple (1 SQL statement) stored procedures for your CRUD logic in your VoltDB application's project file (project.xml).
The syntax for a Java Stored Procedure (named MyProcedure) in VoltDB is as follows:
<?xml version="1.0"?>
<project>
    <database name='database'>
        <schemas>
            <schema path='ddl.sql' />
        </schemas>
        <procedures>
            <procedure class='com.procedures.MyProcedure'></procedure>
    ...



You can create the 4 CRUD procedures for table1 directly within the project file as follows (no Java code required):

<?xml version="1.0"?>
<project>
    <database name='database'>
        <schemas>
            <schema path='ddl.sql' />
        </schemas>
        <procedures>
             <procedure class='table1_insert'  partitioninfo='table1.column1:0'><sql>insert into table1  (column1, column2) values (?,?);</sql></procedure>
             <procedure class='table1_update'  partitioninfo='table1.column1:1'><sql>update table1 set column2  = ? where column1 = ?;</sql></procedure>
             <procedure class='table1_delete'  partitioninfo='table1.column1:0'><sql>delete from table1 where  column1 = ?;</sql></procedure>
            <procedure  class='table1_select'  partitioninfo='table1.column1:0'><sql>select column1, column2  from table1 where column1 = ?;</sql></procedure>
    ...



You can also define multi-partition stored procedures within the project file by excluding the "partitioninfo" attribute. At some point we may add support to create these CRUD procedures automatically.

We do not plan on adding ODBC support to VoltDB. You will need to call these procedures using either the client libraries or the JSON interface.

-Tim
re: CRUD + VoltDB
sylverboss
Jul 6, 2010
SB,
You can currently create simple (1 SQL statement) stored procedures for your CRUD logic in your VoltDB application's project file (project.xml)...
-Tim


Hi Tim,

Thanks. Should be alright to start with. I was wondering how you would go about retrieving few hundred thousand records (or more) with json over http ? that's the limitation I was thinking of.. I'm still not sure how I could integrate Volt with an existing PHP application.

SB
re: PHP/JSON + VoltDB
tcallaghan
Jul 7, 2010
Hi Tim,

Thanks. Should be alright to start with. I was wondering how you would go about retrieving few hundred thousand records (or more) with json over http ? that's the limitation I was thinking of.. I'm still not sure how I could integrate Volt with an existing PHP application.

SB


SB,

The V1.1 implementation of the HTTP/JSON interface is intended to be a preview. It is fully functional (you can call user defined stored procedures and system procedures and it returns the correct result sets) but it is not yet performant. We will be addressing the performance of the interface in future releases of VoltDB.

In regards to your other question, "how you would go about retrieving few hundred thousand records (or more) with json over http", I'd need you to further describe your application needs. Are you looking to return a few hundred thousand records in a single stored procedure call? Please provide more information.
-Tim
re: PHP/JSON + VoltDB
sylverboss
Jul 7, 2010
SB,

The V1.1 implementation of the HTTP/JSON interface is intended to be a preview. It is fully functional (you can call user defined stored procedures and system procedures and it returns the correct result sets) but it is not yet performant. We will be addressing the performance of the interface in future releases of VoltDB.,,

-Tim


Tim,

Yes ideally 1 SP call.

Basically the application I would like to use for a test-drive consist of 2 parts :
- ETL script written in perl that push call records into the db (postgres)
- front end interface written in PHP

When logged in, users can sort call records by using a combination of several filters: date/time, country and so on .. they first get a summary, then they can download all the records . Right now records are split in several files with a maximum of 65k lines per file (excel compatible). They get a zip that could contain on average 5 to 10 files .. 10 x 65000 , you get the idea..

SB
re: PHP/JSON + VoltDB
tcallaghan
Jul 8, 2010
Tim,

Yes ideally 1 SP call.

Basically the application I would like to use for a test-drive consist of 2 parts :
- ETL script written in perl that push call records into the db (postgres)
- front end interface written in PHP..

SB


There are limitations on how much data can be returned in a single stored procedure call, you'd need to look at how much data per row you are returning to see if 65k rows is possible. Also, you'd need to look at your data (schema) and the queries you are doing in your stored procedures to see if your workload can be partitioned.

If you'd like to discuss your use case in more detail please send me an email at tcallaghan@voltdb.com

-Tim