Forums/Other/SQL Yoga
Answered
Creating a cgi / web service
david
asked this on January 07, 2010 10:49
I'd like to offer bot local database access and server side access behind a number of restfull web services. Am I wrong that this is a reason not to use SQL Yoga? Has anyone used the SQL Yoga library stack on a server - OnRev or other rev-cgi hosting? How big would the penalty be using the library over direct scripting of the SQL in a cgi environment?
Would it not be a nice feature to be able to export a query in a way in which it could be easily adapted to server side programming without the use of the full SQL Yoga library?
Comments
SQL Yoga does not work with on-rev yet though it may work with a CGI. It depends on whether or not the debugContexts is available when using a CGI and I haven't tested that yet.
The reason is that SQL Yoga commands often set 'the result' and 'it'. 'the result' contains any error message and 'it' has data. I have to use debugContexts to trick the engine into letting me do this. A future version of the Rev engine just might support setting 'the result' and 'it' natively in which case SQL Yoga could then be used with on-rev and CGI.
As for exporting a query - when creating a SQL Query object you can get the "query" property after setting all of the properties. The result is the SQL Query that will be executed.
As for the penalty - I have done any benchmarking on the web yet but basically you would have the overhead of loading a text file that contained all of the schema information for your database. Creating the SQL Objects doesn't take very long but hand writing SQL will always be faster.
Thanks - so as I understand it, you can determine the actual SQL generated by an SQL Query object by accessing the "query" property. This would mean that designing and debugging the db, including queries, relationships and so forth in SQL Yoga, testing this all out in the client - all this work would be productive in terms of designing the web services as you could grab the SQL from the query objects and also replicate the db structure from the schema.
You'd have to do some Rev db coding to be able to call teh SQL query - but still there is a reasonable migration path there - right?
That is correct.
The released version of SQL Yoga does not create databases for you. It will only query them. An iterative means of creating databases is in the works.
You would have to have your database created to work with SQL Yoga so you would be able to use that existing db schema in your web version.
You would need to write your own code for opening/closing db connections and executing the SQL calls. You might need to write calls to iterate through the database cursor as well unless you are using revDataFromQuery (which doesn't require a cursor). There shouldn't be anything too complicated though.
OK - with a project I am working on I need to create a database abstraction layer - all though in this case I am not sure this is the right term. The abstraction needs to allow various languages to call a data model (in MVC terms). The workflow I envisage is to use RunRev / SQL Yoga to prototype the interaction with the data, and when this is stabilised move the model logic over to the abstraction layer, so that other languages can make use of the model without having to reimplement this logic in their own languages. I am thinking here of using a procedural language built into the database such as PL/pgSQL - http://www.postgresql.org/docs/8.4/static/plpgsql.html It seems that building the model in SQL Yoga would at least make it easier to write the SQL for PL/pgSQL - and that indeed quite a lot if not all of the PL/pgSQL could exported from SQL Yoga / Rev at some time in the future. Any comments on this as a workflow?
It sounds like you are creating an API for your data that allows other applications to access the model data.
Writing a Rev app that generated the PL/pgSQL sounds like it would be a neat approach.