Desktop apps and Web - what makes sense?
doug at artsman.com
Thu Aug 12 18:28:52 UTC 2021
I agree with you alex. I like postgres as the glue… and while I say — psotgres and pgsql. its more a do what I say and not what we’ve done.
I make a new stored proc about once a week, so its a slow migration (sadly).
and we’ve opted to avoid pgsql in a number of cases and put it into our python servers. — thats still gives us 3 tier .. with a fantastic ORM capability in SQLAlchemy ….
so it becomes a debate of ‘business logic behind database — which we use extensively for triggers and an number of things’ to ‘business logic in front database’ and thats primarily because of our situation. we still have sites with self hosted databases.. and putting logic into the db make it visible to people and less protected. so its a pragmatic thing to protect intellectual property at the current time.
all of which is to say, you are right.. and thats the end goal. not there yet
Arts Management Systems Ltd.
mailto:doug at artsman.com
Phone (403) 650-1978
> On August 12, 2021, at 11:02 AM, Alex Clay via omnisdev-en <omnisdev-en at lists.omnis-dev.com> wrote:
>> On Aug 12, 2021, at 12:31, Doug Easterbrook <doug at artsman.com> wrote:
>>> One thing that I am not sure about is the use of stored procedures. I see the advantage of having multiple parties (including legacy Omnis desktop app) using them, however, the challenge is to maintain them in such a way that nothing gets broken. The debugging of stored procedures where you put biz logic might be another challenge, not a trivial thing.
>> true it is harder. there is a command in pgsql called ‘raise’
>> refer to: https://www.postgresql.org/docs/current/plpgsql-errors-and-messages.html
>> basically it lets you put out debugging info into the postgres log…. if you need to. This is no different than a headless server where you have to write to a text file or log to see whats going on.
>> solution: make small, testable routines if you want to have some stuff in pgsql. to be clear, we DO NOT have all, nor do we have a significant portion of our business logic in postgres.
>> we have some. and the places we chose to do it at the beginning is where we really needed performance for database I/O where many things happen on a single database call. We do not use it for anything where we are simply reading or updating a large set of records. thats all in omnis.
> We actively push logic to the database. Any kind of processing, data analysis, and certainly enforcing data integrity works extremely well as a server-side function. We interface with external web services and the file system through PostgreSQL. Our SMTP sending system is all server-side in PostgreSQL. Functions are a mix of plpgsql, perl, and pure SQL. Even our backup and database management systems are all server-side.
> Server-side functions have zero latency, robust features, and are easy to learn, IMHO. We had a requirement to build an endpoint in our REST API to dump a decent-sized table. In native Rails this was 50+ seconds. We rebuilt this to generate the JSON using PostgreSQL's native functions in a view and the time dropped to under 10 seconds, and a good chunk of that time was interfacing through Rails and the data transfer. You might argue that a view isn't server-side programming. I would argue it's absolutely programming—just declarative instead of imperative.
> Function development is one area that all of our developers can share, whether they are desktop, web, or mobile. That is hugely intentional since all those platforms converge at the PostgreSQL layer, and new devs grok server-side programming easily.
> Could some of this move to a middle tier? Sure, but we don't have a consistent middle tier since Omnis makes direct connections and we allow clients to self-host (ain't no way I'm going to try a turn-key installer for a Ruby + Rails + Nginx stack on Windows).
> Current function count is just shy of 5,000 in an application database. About 33% of these are testing functions. We use pgTAP (https://pgtap.org <https://pgtap.org/>) to add automated testing in PostgreSQL and it's a fantastic tool for building a test harness, refactoring, and scaling code.
> One downside is that we're all in on PostgreSQL. That's an issue in theory only...I have ZERO reason to even think about leaving PostgreSQL. If you want to talk about software that can be glue, PostgreSQL should be high on that list. :)
> Debugging can be a little challenging, but there are some good techniques to help. I don't have any experience with it but it appears pgAdmin can offer a more traditional debugger: https://stackoverflow.com/questions/20190406/how-to-debug-postgresql-stored-procedures
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
More information about the omnisdev-en