An interesting experiment with JSON, Postgres and Omnis

Andrea Zen a.zen at athesiavr.it
Wed Jan 11 06:42:33 EST 2017


Nice share, thanks!

Just a question: if I understood well, at the moment you have a client-server application entirely written in Omnis and you plan to develop a middle tier that will handle all the business logic with another language.
Why don't you use Omnis also to develop the middle tier? If so you could use the current business logic you have, calling it from remote tasks. From outside you could directly call the $construct of the remote tasks with http calls.
You'll need only to develop the JSON encode/decode part, to format the input as your current business logic expects it, and to format the output in a common understandable way for all possible callers (client/app/web).

Andrea Zen


> -----Original Message-----
> From: omnisdev-en [mailto:omnisdev-en-bounces at lists.omnis-dev.com] On
> Behalf Of Bastiaan Olij
> Sent: Tuesday, January 10, 2017 6:36 AM
> To: OmnisList <omnisdev-en at lists.omnis-dev.com>
> Subject: An interesting experiment with JSON, Postgres and Omnis
> 
> Hey All,
> 
> First of all Happy Newyear!
> 
> I just wanted to share with everyone a nice little experiment we've been
> doing here at Instinct Systems. While this definitely isn't for everyone we've
> been trying a different approach to accessing our data. While still early days
> we've been pretty impressed by the results so far.
> 
> There are two issues we've been trying to find a nice solution for and so far
> this seems to do really well.
> 
> The first issue is one of moving to a 3-tier approach and how to get from an
> existing client-server architecture to a 3-tier architecture so other
> environments can work through the same middle tier and thus move as
> much business logic out of our client. The problem being that we have a 25+
> year old legacy product that we can't suddenly switch over and we didn't
> want to do some sort of hybrid.
> 
> The second issue is one of structure. Take an invoice for example, it has a
> header record, it has a number of detail records, it may have a payment plan
> associated with it, there may be other info that is part of what makes up the
> invoice. These bits and bobs are all tables in our database. On the Omnis side
> each table has a schema and table class, and we have an object that gathers
> all the data together and voila, one invoice. This means that our client is still in
> control. Once it becomes time to save the invoice it needs to perform the
> right update query to update the header, it needs to do the right inserts,
> updates, and deletes on the detail table, etc. It means that any client, so
> both my desktop application, my webbased portal and my mobile app all
> need to duplicate the same complex logic.
> And seeing this interface is available to anyone who knows how to logon to
> our database, they can muck about and bypass all the safeties by using a run
> of the mill generic SQL client. If you need to be able to delete a line on an
> invoice, you can also do a "DELETE FINVOICE_DETAIL"
> trashing the whole table.
> 
> What we really want is for our software to be completely agnostic of the
> database structure. We want to retrieve the invoice as a single object, work
> with the invoice, and submit that object back to the middle tier so the middle
> tier can validate if it's received a proper invoice, check if the user requesting
> the changes is actually allowed to make them, and then perform the correct
> changes.
> 
> Now this is nothing new. To anyone who's implemented 3-tier architecture
> this is preaching to the choir. If I didn't have a legacy product and I would
> start from scratch life would be easy. I would setup my postgres database
> and create my tables. I would create a middle tier in something like Python or
> Ruby or Node or any of a legion of solutions out there that allows me to build
> in the logic to expose my invoice as an invoice and handle all the business
> logic internally, and I most likely would do so though a RESTFUL interface.
> And indeed, that is exactly what we plan to do for our middle tier and how
> we're building it.
> 
> Omnis is off course able to talk to our RESTFUL API and indeed in due time
> that is what we'll be doing but as I mentioned, in the short term that isn't an
> option. However looking under the hood each action is supported by the
> data formatted as a JSON object. Omnis requests an invoice through the API,
> gets a JSON object, parses it, and voila we have our invoice. Saving the
> invoice is the same in reverse, we take our invoice, convert it to JSON, send it
> off to the API, it validates and saves it....
> 
> Now here is the kicker. Postgres supports an incredibly powerful JSON
> parser. On the Omnis side I can do exactly what I described above but
> instead of sending/receiving the JSON from a RESTFUL API, I'm getting it
> directly from Postgres through function calls.
> 
> So on my Postgres server I have a function called getInvoice(invoiceID) and a
> function called saveInvoice(JSON). In Omnis I call those functions directly. In
> my webinterface I retrieve the invoice through my RESTFUL API which ends
> up calling the exact same functions on Postgres. Suddenly I have a path of
> allowing me to keep using the a direct connection to my database to access
> the same logic as my API does, I can reuse the same logic, and when I'm
> ready I can replace the function calls with API calls without having to rewrite
> half my application as the JSON is the same, where it goes/comes from just
> changes. With limited effort I can even implement this in such a way that this
> switch is easy to make at some later time.
> 
> Also an added benefit is that I can start closing off direct access to tables in
> my database and solely allow modifications to be performed through these
> functions which take into account the rights of the user that is logged on.
> Now this is a bit of two edged sword, this is great for protecting direct access
> to the database as long as we allow it but it makes life for my middle tier
> harder as it often connects to the database with a generic logon. In a fully 3-
> tier solution nobody would be able to directly connect to the database, the
> only connections accepted are those from the middle-tier. But that is the
> longer term place we'll go to where we'll dumb down the database and move
> far more logic into the middle tier.
> 
> Finally, and this surprised me to a certain extent, it seems to be faster.
> Especially if you design the client side around Omnis' JSON parser so you
> don't have to rework the data too much (though I'm doing some extensive
> ripping apart of the data and its still faster) it seems to take out a lot of the
> overhead in the DAM.
> Another part of this is that you have one call instead of many. Saving a
> 20 line invoice means anywhere between 21 and 42 queries for our solution,
> each one being sent to the server, waiting for a response, sending the next,
> etc. Especially doing so to a hosted server over Australias dodgy internet, the
> lag time alone will add seconds to the save. Replace that with a single call that
> sends some JSON data and suddenly you skip all that waiting time.
> 
> Anyway, I may write up some more about this in due time as we develop this
> further but I thought it is a really interesting approach to share.
> 
> Cheers,
> 
> Bas
> 
> __________________________________________________________
> ___
> Manage your list subscriptions at http://lists.omnis-dev.com




More information about the omnisdev-en mailing list