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

Paul Mulroney pmulroney at logicaldevelopments.com.au
Thu Mar 11 03:14:31 UTC 2021


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!

Regards,
Paul.


> On 11 Mar 2021, at 10:55 am, Gordon Wilson <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> 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 :)
> 
> Regards,
> Paul.
> 
> 
>> On 10 Mar 2021, at 10:29 pm, Andy Hilton <andyh at totallybrilliant.com> wrote:
>> 
>> Gordon
>> 
>> 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
>> Web : www.totallybrilliant.com <http://www.totallybrilliant.com/> 
>> Helpdesk : http://totallybrilliant.kayako.com
>> Email : andyh at totallybrilliant.com
>> 
>>> On Mar 10, 2021, at 4:55 AM, Gordon Wilson via omnisdev-en <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> wrote:  
>>> 
>>> 
>>> I pay NO 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. 
>>> 
>>> Das


The sole purpose of a child's middle name, is so he can tell when he's really in trouble.
-- 
Paul W. Mulroney                                            We Don't Do Simple Pty Ltd 
pmulroney at logicaldevelopments.com.au       Trading as Logical Developments
www.logicaldevelopments.com.au                   ACN 161 009 374 
Ph: +61 8 9458 3889                                       86 Coolgardie Street
                                                                         BENTLEY  WA  6102





More information about the omnisdev-en mailing list