All About Table Methods

Doug Easterbrook doug at artsman.com
Tue Oct 4 15:55:49 UTC 2022


lots of discussion on table classes…. so I thought I’d touch on their great usefulness, objects, placement of code and simplicity of writing, and then end up with some final thoughts around sql…. which started this in the first place.





the starting place, take SQL completely out of the equation.


My simplistic viewpoint is that table classes are purpose built objects that
- bind a list of data
- with functions
- have smartness (like stored procs)
- that make coding easy, inheritable, re-suable, and reliable

and that precise set of requirements is something that I use every day to help me develop code faster.





Defining data

the data can be defined with a query or schema class that maps to things in a database (yeah, I use those a lot)





Functions:

the functions let me operate on that data.   I like to $verify the columns (I think thats a good idea - valid data), or $computeTopTenClients or $update, or $delete the data in the table.

and the ease of coding is:

do list.$definefromsqlclass()
do list.$verify
do list.$computeTopTenClients
do list.$update
do list.$insert
do list.$doIHaveChildrenThatPreventDelete


and you can refer to the table class on windows directly in field $dataname (this is perfect, since changing it while the list is in smart mode — makes DB update bueno simplistico

etc

it is a very clean syntax that I can use anywhere in my interface to keep the functions that look at the data or do something with it in one place.  


you can do the same thing with objects in omnis .. it is just a little more effort - but it can be done.       Others have pointed out that the table class is just a special case of an object class that deals with data.

I’m so glad I have them.





what about other languages?

these similar constructs exist in other languages.    In  python, you make objects that talk to data.    A great tool is SQLAlchemy — its not exactly like a table class, but quite similar in that you need to define your object relational model and it handles a lot of the magic of I/O to the database.


or if you want, you can put all your logic into stored procedures.   its a little harder and a bunch of traffic to the database. SQL views are similar to queries.    Stored procs can be like verifiers.  or functions that return data for you.


are table classes magic?  no.   do people outside omnis know what you are talking about ..   if you relate to them in their analgous objects - yes,   they do understand.     



Table classes and Library size?

(thank you Scotte for reminding me that blue code = good code :) )

Do table classes make your library grow in size with redundancy.   to the contrary, they simplify it.   one table reflects the data is is based on.  an inherited table class caries almost no overhead. 

- most of my query classes are a couple hundred bytes.    
- most of my table classes are a couple thounsand bytes - which is almost entirely reflected in the custom code that I need for that one table.  eg, the  record verifier for patrons is different than addresses .. its checking that different fields are filled or not filled and provide an error message.

at least it is in one spot and I can call it anywhere in my application.

do list.$definefromSqlClass(address)
do list.$verify

of course the table class has lots of code to make sure that postalcode meets a pattern, or address line 1 is filled, or city is in some sort of place, or to look up latitude and longitude in google to save it with the address so we can display the data on a GIS system.



we try hard to never put that code into windows.  every time we have, we end up duplicating code — and when I find it, it gets refactored back into a table class to make my life much simpler.



again .. read objects and table classes as similar.   there are benefits of each.  I just happen to find that using a table class with any database make incredibly convenient to display data on an omnis window or in an omnis list.



and then the feature of smart lists.    if you are sql junkie, thats so close to providing you SQL triggers for insert, update and delete within your code.    You get a ‘before and after’ image of the row (aka NEW and OLD record in pgsql)





to build, or not to build SQL, that is the question


the built in table class $insert, $update, $delete and $dowork process on smart lists do a lot of what many people need.     it is very well thought out, although the first time you use them, its not so obvious that the folks and Omnis did a really good job on this model.   But they did.

and you can extend them any way you want to do really cool stuff.    That sounds like any other languages or add on libraries..   base functionality does a lot for you.   You want more, you dig deeper.   SqlAlchemy & python is like that.


After 20 odd years with studio and table classes PLUS the new editor (love it) PLUS the ‘begin statement’ …. I have migrated to adding a lot of begin statement into our applications for specialty needs.


mostly, I use regular omnis stuff.

but if I want to fill a list with data using a common table expression, I do so.  (Using begin statement).   however, it is still in my table class.   I do that because I can copy the SQL and prototype it in PGadmin (substitute your own database platforms SQL tool for pgamdin)

once I have the prorotype sql working to my satisfaction, it goes into omnis, wrapy a begin statement around it and its instantly recognizable.


I may use that to fetch data into a smart list .. then I use standard omnis  $update to change the data.




so the debate shouldn’t be about about omnis limiting you on SQL.   au contraire, it lets you use all the bells and whistles.      but you only need to go hardcore SQL … when you need something extremely specific. 


regardless.  the sql code, however you write it, is often best placed in the table class so that it can be shared, inherited, and what have you.





conclusions

begin statement and table classes are not mutually exclusive.   they are powerful brothers in arms and consolidate code.

table classes (objects if you prefer writing a bit more code), are perfect for the whole process of building windows that interact with lists and data.

table classes can inherit a lot of their stuff from a superclass (yeah for blue code) and should not vastly increase the complexity or size of code.   in fact, they simplify it.





just my viewpoint.


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

> On Oct 3, 2022, at 11:42 PM, ADJob <mats at adjob.se> wrote:
> 
> Hi,
> 
>> I am also confused why we have two types of sql commands in Omnis:
>> 1. Begin...End statement
>> 2. Do iDataList.$select()... Do iDataList.$fetch()
>> When is each one recommended for use?
> 
> Great post Das! 
> 
> But I will also add another point of view.
> Omnis Developers is only a fraction of a fraction of all other SQL developers. And SQL is a complete other language than Omnis.
> 
> When using "Begin...End statement" you are talking the same standard language as millions of other developers.
> Add an Omnis SQL table question on Stackoverflow close to none knows what you are talking about. 
> 
> I have asked a few standard SQL questions on Stackoverflow and got very good help..
> Omnis support may answer some simple SQL questions, but I doubt they will or can answer some more advanced SQL-questions.
> 
> So I advocates for using Begin..End statement instead of Table classes for this reason.
> Talk the same language as the rest of the world. Do not talk Omnis if you want SQL answers.
> 
> The price you have to pay with the magic within table classes is that nobody outside Omnis may know what you are talking about…
> Another price you have to pay is that your library size will grow a lot with redundant extra table classes.
> 
> Disclaimer! Just my personal opinion.
> 
> /Mats
> 
> 
> 
> 
> 
> 
> 
> 
> 
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 



More information about the omnisdev-en mailing list