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

Gordon Wilson g_r_wilson at yahoo.com
Thu Mar 11 19:34:37 UTC 2021


 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> 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
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>
_____________________________________________________________
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