An interesting experiment with JSON, Postgres and Omnis
Vik Shah
Vik at Keys2Solutions.com.au
Tue Jan 10 01:03:33 EST 2017
Hi Bas,
First, a Happy new year to you and everyone on the list. =)
This is a great article and its something I have just recently done albeit using Omnis Studio 8.x.
I must note that while Omnis is quite good and efficient in managing the JSONs there is one issue that still needs ironing out (reported as bug).
With Omnis it can received
1. Simple structured JSON a.k.a a row perfectly.
2. 2-tier JSON, a row in a row works too.
3. n-tier JSON breaks. a.k.a a list in a row. The list looses its definition and only retains the data that it receives, **so for now**, one must parse that incoming structure, extract the list and populate it into a proper list variable and then re-insert it into the row for further down-the-pipe processing to take place on the Omnis input data structure.
Just a heads up in case this trips someone going down this path. I am certain that the guys at Omnis will be fixing this soon too.
Regards,
Vik Shah
Director
Keys2Solutions
AU: +61 411 493 495
SE: +46 21-495 01 05
US: +1 (415) 413 4172
> On 10 Jan 2017, at 3:36 PM, Bastiaan Olij <bastiaan at basenlily.me> wrote:
>
> 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