Stored Procedures in Omnis Library

hi Alan:

we build something into our application many years ago to create a supplemental data dictionary that made these things generally easy. In effect:
we used file classes to determine fields in the system. On that we built things like
display as money
left or right justified
long and short names for display
visible field or invisible field
NOTE: we use postgres for a database, but it was truly convenient having the file classes for the dictionary
we have a window where we let people change these values and then we compress it all into the $userinfo property of the file class
as part of that dictionary, we have a window to paste in and name triggers for each file. They can be before or after triggers, for insert, update and delete
those are also compressed and stored in the $userinfo property of the file

on an update to the application (i.e. version is newer than old version), then we:
uncompress the $userinfo for all files
add in new fields
drop all triggers
reacreate all triggers from the data in the $userinfo property.

this data dictionary idea has proved really useful for adding new fields and seeing them appear in the schema — plus having the fields behave properly in headed lists, all without al oto of work to add a new field.

yes, the dictionary object took a fair amount of time to conceive and build — -but once we did, we rely on the concept daily to build tables of fields that control the application and update it.

its a lot of work for us to unravel it from our application, and at this moment, I can’t really take the time to do so. I wanted to explain the concept and why the $userinfo property was very helpful.

I may unravel it once we get to studio 8 and stick it in open source… but that will be a few months.

Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug@artsman.com
www.artsman.com
Phone (403) 650-1978

see you at the third annual users conference
tickets.proctors.org/TheatreManager/95/online?performance=29086 <tickets.proctors.org/TheatreManager/95/online?performance=29086>
> On Nov 3, 2017, at 9:33 AM, Alan Grinberg <omnis@alangrinberg.com> wrote:
>
> Hello all,
>
> Using Postgres and Studio 6.
>
> We have some complex stored procedures that we want to set up in a new PG database.
> No problem doing this with PG Admin (and using Razor, too).
>
> We want to put this code in our Omnis library so the user can trigger it when a new database is created.
>
> The SQL is a large block of static text. (50+ lines)
>
> What is the preferred way of storing this in the library?
>
> 3 ideas so far:
> 1. STA: (too cumbersome, SQL is already written)
> 2. Copy text to an object method as comments, then extract the leading semi-colons
> 3. Store the SQL in a record in the database by copy/paste (but requires our intervention)
> 4. Read from a text file (not secure)
>
> Any ideas out there?
>
> Thanks,
> Alan
>
> Sorry I missed the conference. Sounds great.
>
> —————————————————-
> Alan Grinberg
> AG Systems/ZOO-INK
> San Francisco, CA
>
> www.zoo-ink.com <applewebdata://E01F3E44-AB72-4A7B-A459-57C700E4EAEF>
> www.perfectfit.net
> alan@perfectfit.net
> —————————————————-
>
> _____________________________________________________________
> Manage your list subscriptions at lists.omnis-dev.com

_____________________________________________________________
Manage your list subscriptions at lists.omnis-dev.com