O$5 (& O$10.2) Updating tables and column definitions from library updates via notation?

Mike Matthews - Omnis omnis at lineal.co.uk
Fri Mar 12 07:30:17 UTC 2021

We do the same here.

Mike Matthews

Lineal Software Solutions
Commercial House, The Strand<x-apple-data-detectors://1/1> Barnstaple, Devon, EX31 1EU<x-apple-data-detectors://1/1>

omnis at lineal.co.uk<mailto:mike.matthews at lineal.co.uk>



On 11 Mar 2021, at 22:40, Phil (OmnisList) via omnisdev-en <omnisdev-en at lists.omnis-dev.com<mailto:omnisdev-en at lists.omnis-dev.com>> wrote:

We just store the version number within the database, and compare it with the library version number, if the library is newer then check the formats, otherwise, don't bother.

Phil Potter
Based in Chester in the UK.

On 11/03/2021 19:34, Gordon Wilson via omnisdev-en wrote:
 Hi Andy
Good point. I guess the best way would be to do a speedy check when starting up such as Paul has suggested to ensure some kind of integrity. If there is a "difference" detected, start asking questions about recent updates or checking. Only do modifications in single user mode and perhaps block access until the integrity check is satisfied for multiple users... Perhaps that should always be the case - just thinking !!
: )

Kind regards, Gordon.

    On Friday, 12 March 2021, 5:51:37 am NZDT, Andy Hilton <andyh at totallybrilliant.com<mailto:andyh at totallybrilliant.com>> wrote:
   Not saying anything against the concept here (as its really quite smart !) but the time taken could be a factor - I have many hundreds of tables in my app - and I wouldn’t;t want every user to check every time they start up ! In essence that’s where the number system provides a really fast check and move on with no further ado - just saying !!

Andy Hilton
Totally Brilliant Software Inc
Phone (US) : (863) 409 4870
Phone (UK) : 0207 193 8582

On Mar 10, 2021, at 10:14 PM, Paul Mulroney <pmulroney at logicaldevelopments.com.au<mailto:pmulroney at logicaldevelopments.com.au>> wrote:

Hi Gordon,

Here's some code for you to try.  This is Studio 8.1 but should work in any version:

Do $root.$libs.[vsLibName].$files.[fcClassName].$objs.$makelist($ref.$name,$ref.$objtype,$ref.$objsubtype,$ref.$objsublen) Returns vlFieldList    ;; Include subtype - otherwise we can't tell the difference between short int and long int.
Do vlFieldList.$redefine(vsName,vsType,vsSubType,vsLength)
Calculate #F as vsFieldList.$assign('')+totc(vlFieldList,vsFieldList.$assign(con(vsFieldList,vsName,kTab,vsType,kTab,vsSubType,kTab,vsLength,kCr)))    ;; Omnis 1-liner to create a text field by concatenating fields, tab delimited and CR terminated, from a list
Calculate vlOmnis.checksum as binchecksum(vsFieldList)    ;; Calculate a unique checksum for this combination of fields.  Doesn't matter what it is, just so long as it's consistent
If we look at the definition: name, type, subtype, length, and concat these values together in a consistent way, we can then generate a checksum from the resulting string.  If anything significant changes in the definition, then the checksum changes for the file class.

Good luck!


On 11 Mar 2021, at 10:55 am, Gordon Wilson <g_r_wilson at yahoo.com<mailto:g_r_wilson at yahoo.com> <mailto:g_r_wilson at yahoo.com>> wrote:

Hi $all ; )

A big thanks to all who have replied : ))

Lots of interesting suggestions and recommendations supplied... both on and off list.

Now, I'm drinking from the fire hose. Solutions some complex and some a little less so.

Paul, how are the checksums calculated? That sounds like a really quick and simple check that might speed things along at startup when there are no changes which would be most of the time...?

Kind regards, Gordon.

On Thursday, 11 March 2021, 2:01:27 pm NZDT, Paul Mulroney <pmulroney at logicaldevelopments.com.au<mailto:pmulroney at logicaldevelopments.com.au> <mailto:pmulroney at logicaldevelopments.com.au>> wrote:

Hi $all,

We calculate a checksum based on the field definitions in the schema/file class, and a last modified date, and then store this in a table in the database.  We use this to determine if the schema/file classes have changed, then we check the definition.  Saves having to remember a "change number", which always gets me into trouble :)


On 10 Mar 2021, at 10:29 pm, Andy Hilton <andyh at totallybrilliant.com<mailto:andyh at totallybrilliant.com>> wrote:


Within the StudioWorks framework we have a whole section devoted to this !

Essentially when you make changes you give that change a ’number’ (and we keep a table in the users database of which mods they have applied) so when told there are changes (which we keep by ‘last changed date’) it is then a simple matter to go through the code, picking out the code for the mods not yet done on that database, apply them and add those mods to our table

The mods come in two parts :

1 is for changes to the schema and table (which can be changing a field, adding or removing a field etc etc) i.e. structure changes, and
2 is for ’statement’ changes to make to a table - i.e. we may want to change a specific field to one given value or something like that i.e. data changes….

In any event it has served beautifully well for many years - and allows me to take even a very old users database, and just start it with latest software, and boom, the database is all automatically updated

I am simply outlining the bare bones here (there is a lot of it) in case it may appeal to you !

Andy Hilton
Totally Brilliant Software Inc
Phone (US) : (863) 409 4870
Phone (UK) : 0207 193 8582

On Mar 10, 2021, at 4:55 AM, Gordon Wilson via omnisdev-en <omnisdev-en at lists.omnis-dev.com<mailto:omnisdev-en at lists.omnis-dev.com>> wrote:

Does any one have a short script or detailed description of how to maintain changes to SQL (e.g. MySQL) tables and column definitions from within Omnis for library updates?
I am using Das's approach using the $file definitions to run SQL and have $files, $schema, $table objects within Omnis. I want to update columns from $files and using a checker on startup in case there are changes in the $files to update $schema, $tables and SQL server tables.
I guess something like...
Set reference to mrFile  to $clib.$files.$first()While mrFile
  Calculate %%name as mrFile.$name
  Do mrFile mrObj.$objs.$makelist($ref.$name,$ref.$type,$ref.$subtype,$ref.$sublen) Returns mFileList    Set reference mrObj to $clib.$schemas.[con('s',mid(%%name,2,100)]    If not(mrObj)        Do $clib.$schemas.$add(con('s',mid(%%name,2,100)) Returns mrObj
      Do $cinst.$makeColumns(mFileList,myObj) Returns #F    Else        Do $cinst.$updateColumns(mFileList, mrObj) Returns #F    End if    Set reference mrObj to $clib.$tables.[con('s',mid(%%name,2,100)]
  If not(mrObj)        Do $clib.$ tables.$add(con('s',mid(%%name,2,100)) Returns mrObj
      Do $cinst.$makeColumns(mFileList, mrObj) Returns #F    Else        Do $cinst.$updateColumns(mFileList, mrObj) Returns #F    End if    ;  something similar here for server tables...?
  Set reference to mrFile  to $clib.$files.$next(mrFile)
End while

Comments and suggestions most welcome : )
I guess there will be coded errors and typos but hopefully you get the idea of what I am after...?
Also, if the line spacing goes wrong, I'll resend this...!@#$

Kind regards, Gordon.

  On Wednesday, 10 March 2021, 2:32:01 pm NZDT, Das's <goravanis at gmail.com<mailto:goravanis at gmail.com>> wrote:

I pay NO IP.com<http://IP.com> <http://ip.com/> a small yearly fee and get email support, no ads, and it’s worked out great. I was a bit confused by their setup help, about a year ago.. but I got through it.


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 mailing list