For years, and I will keep advocating this for anyone who writes his/her software in Omnis and wants some measure of portability between backends, I have used table classes for each and every table in my database. I have fully featured base classes from which my table classes are subclassed and it really is a very strong way of interacting with database tables. They do anything from validating foreign keys, to joining in extra tables, to writing more complete CRUD SQL statements then Omnis does natively.
But object classes are just as important but for me they are containers of structure. To paraphrase a recent post I did on the Omnis list, I have an object class for an invoice and inside that object class I have a row variable defined from my invoice header table class and a list defined from my invoice detail table. The object class has a $load function that will load the header and the details using the $select and $fetch methods on the table class (my base class actually has a $build method that combines these actions) and a $save function that will start a transaction, then create/update my header and update my details.
My windows then simply uses that invoice object class to display the invoice and interact with the user.
So both have a place in my heart. The table class contains all the business intelligence logic related to one specific table, but the object as a container contains the business logic related to the structure as a unit. It makes sure the totals on my invoice match the sum of the details, it makes sure the correct tax is calculated over the details, it makes sure of all things that can not be done in relation to just one table, as that one table is only one part of the invoice.
That all said, I’ve started down a different path but this is a path where I’m using more technologies then just Omnis and we’re increasingly dealing with either larger organisations as clients or increasing our risks by putting our solution online. We live in a much less secure world then we once did and one large downside of an SQL centric solution as we implement is that we open ourselves up to a lot of risk when someone connects to our database outside of our Omnis application. In order to update my invoice with table classes I need to give my user the ability to log on to my database and give them CRUD access to both the invoice table and invoice detail table. If they do so through my application, my applications business logic ensures invoices are correctly updated. If a disgruntled employee at my client however figures out we’ve got a SQL based solution and they download any of the many free SQL tools available, they can do a lot more damage then they would be able to do logging in through our application (which you can’t prevent if you want happy employees to work too).
However, we still want to deploy a full desktop application written in Omnis but in this brave new world this Omnis desktop application no longer connects directly to the database but instead talks to the middle tier. It has no inkling of the existence of an invoice header table and an invoice detail table and whatever other table is referenced by these tables. Instead it calls a function on the API to retrieve an invoice, and calls a function on the API to save an invoice. Table classes have no use at this side of the solution anymore. I still use schema classes but purely as an easy way to define a list of columns, not as a representation of the table in the database.
Now even in the intermediate solution I talked about on the Omnis list table classes are taking a back seat. This does require choosing one database platform and sticking with it and closes the door to be cross SQL database. I’m actually closing off direct access to tables so that someone logging into our database doesn’t even see that we have an invoice header and invoice detail table or at best just has read only access. Instead we provide authenticated access to a stored function that gets an invoice, and a stored function that saves an invoice, and both mirror the API of the middle tier I eventually intent to replace this with. All the logic that currently resides in my table classes is therefor moving to the backend and is no longer part of the Omnis solution.
So yes, while I absolutely love table classes, and I absolutely endorse what you say here Andreas, and I would absolutely recommend anyone using Omnis to go down this path first and always go down this path if your solution does CRUD directly through SQL queries to the database, there are very valid arguments for taking a very different approach.
P.S. I don’t get your ST/TA/018, unless I am missing something, I have had that functionality for years. If you leave the schema class property of a table class empty and you simply execute a query through its statement object, you can do a fetch and the list will be defined according to your resultset. There is a bug in the Postgres dam that a text column will be defined with a length of 0 and while it is retrieved correctly, if you try and update the column you loose the data but that can be worked around with a bit of notational trickery. Anyway try adding the following method to a brand new table class and try it out:
$getStuff Calculate lvSQL as 'select * from atable' if $cinst.$statementobject.$execdirect(lvSQL) do $cinst.$fetch(kfetchAll) else do $cinst.$sqlerror(kSelect, $cinst.$statementobject.$errcode, $cinst.$statementobject.$errtext) end if
Obviously make sure you’ve assigned your session to your table class first as per usual.