Omnis Postgres No nulls puzzle
doug at artsman.com
Wed Jun 15 15:17:00 UTC 2022
happy to. its a long note, so please read on as I’ll try to explain everything, including some theory.
I’ve incluced my entire. $insert method that we use to replace the standard omnis $insert in the table class. For Clarity, this is a complete replacement of $insert in our base table class that we use in all our tables.
It helps with two key things:
we can do an insert using any query class. A query class can contain fields from one or more tables that are joined. Normally this is a huge no-no in SQL. You cannot insert data into a view (which is what a query class really is). However, in omnis, each table class is defined on a base table. The insert only considers fields that are in the base table and ignores the others that are joined to it.
example. main table is F_CLIENT. additional joins might be to a separate address table F_ADDRESS
so the sql to join them pairs up addresses to clients using a primary key on F_CLIENT and a foreign key on F_ADDRESS
C_SEQ = AD_C_SEQ <— primary key to foreign key join
if you were to insert based on that, it would not work. you can’t insert to two tables at one time, so the $insert, in our case, only considers the main table F_CLIENT and ignores any values in F_ADDRESS. its a contrived example, but it works. and the same thing also goes for the $prepareUpdate command in TMbjs.
it helps you only insert the fields that have something in it, dramatically reducing the amount of data being transmitted in the SQL
suppose the main file is F_CLIENT. Suppose that there are 100 data fields in that file, of which 5 or 6 might be binary or images such as the client’s picture, the picture of his/her home, a map to get to their home, a picture of their dog. Again, it is a contrived example, but we do have some tables in our database with at least 3 large binary fields (data for the setup of a theatre that might contain stage orientation, lighting grids, emergency access routing, seating orientation, and more).
omnis, by default, creates an insert statement that has all the fields and the update statement that has all the fields for update as well as in the WHERE CLAUSE. eg. omnis makes
insert into F_CLIENT (C_Field1, C_Field2, …. C_Field100) values
(@[$cinst.C_Field1], @[$cinst.C_Field2], …. @[$cinst.C_Field100])
update F_CLIENT set
C_Field1=@[$cinst.C_Field1],C_Field2=@[$cinst.C_Field2], …. C_Field100=@[$cinst.C_Field100])
C_Field1=C_Field1_oldValue and C_Field2=C_Field2_old_value and … C_Field100=C_Field100_oldValue
the net effect is that the SQL could transmit many many thousands of bytes of data for large tables with large binary fields. its overly chatty in my mind when trying to update data across a long distance to a cloud database.
KEY ASSUMPTIONS to use $prepareInsert and $prepareUpdate
In our tables, we have a naming convention.
The name of the table can be anything you want with no constraints. eg F_CLIENT, CLIENT, Client, TheClient
The names of the fields in each table has these constraints
— the fields must have a PREFIX
— the prefix MUST be the same for that table.
— the prefix cannot be used for a variable in another table (think foreign keys)
— the spearator for the prefix must be a common character.
eg for the F_CLIENT table, we start all out fields with C_. (C_LAST_NAME, C_FIRST_NAME, etc)
for the F_ADDRESS table, all the fields start with AD_. (AD_ADDRESS_LINE_1, AD_COUNTRY, etc)
the separator we have used is ‘_’. but it could be any other legitimate character that can be used in a variable name.
in a table class, the built in $dowork that omnis supplies calls $insert with no parameters.
it expects that all the bind variables will the $cinst and the anything in the current row being processed is all $cinst
that makes it easy to use our replacement $insert. we just refer to $cinst for variables and such and it works.
if you look into all the code, you’ll see the $prepareinsert statement that looks like this. I’ll explain the statement and they you can see how I came up with the values that go into it.
what is tStringFields? it is a TASK VARIABLE that instantiates TMOBJS, so that allows us to use $prepareinsert
here’s the variables. Bear in mind that everything is referring to $cinst.
InsertString is the OUTPUT of $prepareinsert. it is the insert string equivalent (sort of) to $cinst.$insertnames(). which is the omnis version of insert
otherThanStandardField is a boolean. KTRUE means build the insert statement anyway if it contains any field. kFalse means don’t build it if the only fields that have changed are the ’standard fields’. more on that in a bit.’
$cinst.$colcount is the way to get the count of columns in the query or schema class. this parameter must always be this.
"DATE_UPDATED`DATE_ENTERED`EDITNUM`CHANGED_BY_E_SEQ” these are the ’STANDARD FIELDS’ in each table. These are fields that if only they have changed, then do not make a sql statement. they will be prefixed by tempPrefix. see in a moment.
eg if the prefix for the table is C_ then the standard fields in are C_DATE_UPDATED, C_DATE_ENTERED, C_EDITNUM, C_CHANGED_BY_E_SEQ
in our database we have 5 or 6 fields that are in all tables. For example we track date updated on each row . another table like F_ADDRESS will have a field called AD_DATE_UPDATED, AD_DATE_ENTERED, etc. if the field doesn’t exist or the field does not appear in the query class, it is ignored.
basically we are saying, if these are the only fields that changed, it is not worthy of an insert or update. there must be at least one other field that changed.
tempPrefix is the prefix for the table. example C_
Back to the F_CLIENT example with 100 fields
omnis $cinst.$insertnames will look like this with 100 values
(C_Field1, C_Field2, …. C_Field100) values (@[$cinst.C_Field1], @[$cinst.C_Field2], …. @[$cinst.C_Field100])
our $prepareInesert will do the following.
assume C_Field1 and C_Field50 are the only field that was changed.
assume that there is a C_DATE_UPDATED in the query but none of the other ’standard field names’
then $prepareinsert will create a string that looks like
(C_Field1, C_Field50, C_DATE_UPDATED) values (@[$cinst.C_Field1], @[$cinst.C_Field50], @[$cinst.C_DATE_UPDATED])
Note. it only has 3 fields. not 100. it is far smaller
in both omnis standard $insertnames and ours, all you need to do to finish the statement is preappend ‘insert into’
Calculate Statement as con('insert into ',TableName,' ',InsertString)
HOLD ON, WHAT DOES this do $setsqlzeronullvalues($cinst.$colcount)
you’ll notice a call to $setSqlerNullValues near the top of the $insert code that looks like this
what is it doing?
it is going through all the fields that are in the query class associated with the table and:
- if the variable is number (any kind like integer, number, seq), and it is NULL, then change it to zero
- if the variable is a date, or time stamp, or time, and it is ‘’ (empty), then it sets it to NULL
- if the variable is binary and it is of length zero, then it sets it to NULL
these were the defaults we wanted for our variables in the postgres database. so if the field is included in the query, and it is integer and null, then it becomes zero (under the assumption that null for numbers is not so good, or was unintentional).
WHAT ABOUT DEFAULTS FOR FIELDS NOT IN THE QUERY CLASS
if a field is not in the query class. eg, suppose C_BIRTHDAY is not in the query class, then it will not be in insert statement
the fallback is to create a default for C_BIRTHDAY in the postgres default value. all of our fields have defaults in postgres. examples from part of our table.
c_seq integer NOT NULL DEFAULT nextval('f_client_c_seq_key'::regclass),
c_first_name character varying(60) COLLATE pg_catalog."default" DEFAULT ''::character varying,
c_last_name character varying(30) COLLATE pg_catalog."default" DEFAULT ''::character varying,
c_company character varying(100) COLLATE pg_catalog."default" DEFAULT ''::character varying,
c_date_entered timestamp with time zone DEFAULT now(),
c_date_updated timestamp with time zone DEFAULT now(),
c_sex character varying(40) COLLATE pg_catalog."default" DEFAULT ''::character varying,
c_special_needs_note character varying(200) COLLATE pg_catalog."default" DEFAULT ''::character varying,
c_name_combined character varying(500) COLLATE pg_catalog."default" DEFAULT ''::character varying,
c_height numeric(17,2) DEFAULT 0,
c_weight numeric(17,2) DEFAULT 0,
Purpose of the above. it doesn’t matter if omnis inserts the record, or if you do it in pgadmin, or using a different language like python or C++ …. if you don’t specify a field in the statement, you get the default. makes for more data consistency.
HOW DOES IT ALL GO TOGETHER
in omnis you make a schema class for your table F_CLIENT
you make a table class for F_CLIENT .. called tF_CLIENT
it has a few methods like
$getUniqueKey. (returns the unique key of the table, with is generic for us like Prefix and SEQ. or. C_SEQ
$getPrefix. (returns C_)
$getUniqueKeyValue. returns $cinst.[$cinst.$getUniqueKey]. or the current value of C_SEQ in that row. eg. $cinst.C_SEQ
$getMainSqlTable (lets you return the name of the table)
it may be based on a schema or a query class. it doesn’t matter. advantage of a query class is that it doesn’t have to define the whole table
so you can make a table called tF_CLIENTshort with a couple of variables in a query class that inherits from tF_CLIENT
you create a list based on the table class as in
set the list into smart mode
insert/update to your hearts content
do iList.$dowork() to commit the changes
take list out of smart list mode
meaning the code you currently use for updating a table will work if you use smartlists and dowork.
the only trick is that the $insert is changes to do things to make SMARTER SQL
we have added ‘returning’ clauses for our inserts to get the C_SEQ from an insert without having to pre-assign it. C_SEQ is a postgres SEQUENCE field. makes an insert about 20 percent faster than reading the next key and using it in the insert.
we’ve also added some local cache for some records. since we provide databases on our cloud for customers, we have optimized how we read or use some of the data. so we cache little changed data on the workstation and invalidate it periodically on a timer if we need to. This helps our performance on some things tremendously by avoiding reads in other parts of our applications, since the data is already local.
if you like what I’ve said above, and you think you want to go there, I’ll happily give you the $update and $delete code we use. and even talk about the caching approach we took.
but do read the code.. if it works for you and you have questions on it.. happy to go into detail
# If the PrimaryKey hasn't been assigned, set it now.
# Set the Standard Column values.
# Check the Data if the optional parameter was set to kTrue to check the data before $insert.
# All going well, we can proceed with the insert using "Do default".
# -> pCheckDataYN ## If the calling method says kFalse, skip calling $CheckData
# Update the record with any final settings before inserting
# Set the Standard Column values while we are doing insert
Do $cinst.$preUpdateSettings ## Will never fail. Old row is empty
Do $cinst.$preUpdateCustomTypes ## V1007345 DM - JSONB Support
# Set the BLOB data type columns to NULL
Calculate TableName as $cinst.$servertablenames
If pos(',',TableName)>0 ## A comma means more than one table is in the FROM portion of the insert. This means we are inserting from a query.
# Insertion from query, must do special behaviour. Ignore all columns that are not from the main table
Calculate TableName as $cinst.$getMainSQLTable ## V81000 DM - Allow inserting from queries
Calculate tempPrefix as con(iFieldNamePrefix,pick(iFieldNamePrefix='CD','','_')) ## V1006112 workaround for bug in $prepareinsert where two tables start with similar prefixes for credit cards
##### HERE IS $PREPAREINSERT
Calculate InsertString as $cinst.$insertnames()
# Ensure that we have an insert string before trying to send it to the server. If not, then we know it will fail.
Calculate returnCols as ''
# add the returning clause for fields that have server side defaults or that the table class indicates as being returned
Calculate Statement as con('insert into ',TableName,' ',InsertString)
Calculate Statement as con(Statement,' returning ',returnCols)
# All going well, we can proceed with the insert by calling the default method.
Do $cinst.$sendSQLdirect(Statement) Returns StatusOK
Calculate StatusOK as kFalse
# Retrieve the last key set
# Do any updates that are required to the fields after the database is updated.
# Also if we updated any child records, then update the parent as well.
Do $cinst.$setVariablesWhenRecordInserted ## only called on insert
Do $cinst.$postUpdateSettings ## called on insert and update
# V100677 set the cache after insertint the record - if same schema, will add to cache record
If iSetCacheAfterUpdate¬(tUpgradeInProgress) ## see if we poke the cache record (never when update in progress)
Do tDataCache.$UpdateCacheRecord($cinst.$getUniqueKeyField,$cinst.$getUniqueKey,$cinst) Returns #F
If flag false
Breakpoint what happened
Quit method kTrue
# Insert was not OK? Need to add code to elegantly figure out why and report to the user.
Do $cinst.$msgOk($cinst.$ref,TMObjs.$makeparamrow('Sound',kTrue,'Message',con('Developer Message:',kCr,kCr,'Insert of record has failed. We need to complete a generic method for giving the user more information about why the insert failed.')))
Calculate #S1 as iStatementRef.$sqltext
Calculate #S2 as iStatementRef.$errortext()
Calculate #S3 as iStatementRef.$nativeerrortext()
Quit method kFalse
Calculate pCheckDataYN as pCheckDataYN
Arts Management Systems Ltd.
mailto:doug at artsman.com
Phone (403) 650-1978
> On Jun 14, 2022, at 6:47 PM, Mike Rowan <michael.rowan3 at gmail.com> wrote:
> Dear Doug
> I take it you are referring to $prepareinsert. I wonder if you have a
> standard paragraph somewhere that describes its use? Of course, I see it
> requires several parameters but I'm afraid I'm very bad at interpreting how
> to use them.
> Perhaps just one line of actual code?
> On Tue, Jun 14, 2022 at 1:25 AM Doug Easterbrook via omnisdev-en <
> omnisdev-en at lists.omnis-dev.com> wrote:
>> hi Mike
>> a few things to add
>> - as dan says, if you put the field in your insert statement, it will NOT
>> default to the value, even if the field is null
>> - if you have a null value and you include the field in the insert
>> statement, then change the insert in your table class to be something like
>> (assume field2 is null)
>> insert into table1 (field1,field2) values (@[field1], DEFAULT)
>> in other words, if you want to enforce defaults for null or zero values,
>> then explicitly substitute DEFAULT for the bind variables usint a $replace,
>> perhaps in a loop)
>> Note, since you can change the bind variable in the statement you can also
>> include functions like now() for dates and set up sequences to use DEFAULT
>> as well.
>> or, you can remove the fields that are zero / null from the insert
>> statement and let them take the DEFAULT
>> DEFAULT is only for insert, it doesn’t apply to update statements (as
>> far as I know - since we remove fields on UPDATE statement as well)
>> just my thoughts.
>> finally, we use TMOBJS to set text and number fields to zero and empty as
>> paart of our table class. there is a function to do that which is useful.
>> if you use TMOBJS and have over ridden your $insert and $update in the
>> rtable class… which is specifically designed to
>> - set dates to null (of not specified),
>> - numbers to zero (if null)
>> - text to empty (if null)
>> and fo it fast.
>> Doug Easterbrook
>> Arts Management Systems Ltd.
>> mailto:doug at artsman.com
>> Phone (403) 650-1978
>>> On Jun 12, 2022, at 3:43 PM, Mike Rowan <michael.rowan3 at gmail.com>
>>> Morning all,
>>> An example of a column definition in one of my tables in PG 14 .
>>> prename text COLLATE pg_catalog."default" NOT NULL DEFAULT ''::text,
>>> (thats two single quotes after DEFAULT)
>>> I am no expert with PostgreSQL but this says to me :
>>> "When this table is inserted or updated, check that this column is NOT
>>> and, if it is, set it to EMPTY."
>>> Such inserts or updates are rejected due to null value in that column
>>> Can someone on this friendly Omnis list please tell me how I am wrong?
>>> Thanks as always.
>>> Manage your list subscriptions at https://lists.omnis-dev.com
>>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
>> Manage your list subscriptions at https://lists.omnis-dev.com
>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
> 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