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

Andy Hilton andyh at totallybrilliant.com
Thu Mar 11 16:43:59 UTC 2021


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
Web : www.totallybrilliant.com <http://www.totallybrilliant.com/> 
Helpdesk : http://totallybrilliant.kayako.com
Email : andyh at totallybrilliant.com

> On Mar 10, 2021, at 10:14 PM, Paul Mulroney <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!
> 
> Regards,
> Paul.
> 
> 
>> On 11 Mar 2021, at 10:55 am, Gordon Wilson <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>> 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 <mailto:pmulroney at logicaldevelopments.com.au>       Trading as Logical Developments
> www.logicaldevelopments.com.au <http://www.logicaldevelopments.com.au/>                   ACN 161 009 374 
> Ph: +61 8 9458 3889                                       86 Coolgardie Street
>                                                                         BENTLEY  WA  6102
> 
> 
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com <http://lists.omnis-dev.com/>
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com <mailto:omnisdev-en at lists.omnis-dev.com>


More information about the omnisdev-en mailing list