Automatic Updating Database Schema

Andrew Stolarz stolarz at gmail.com
Thu May 8 17:29:22 UTC 2025 

Hello Everyone,

Thanks for all your ideas and suggestions - looking into them.

Doug, we do something similar to what you mentioned "second option" in our
main app that is using MySQL backend.

The app in question right now is a very lightweight app using SQLite. In
Django, syncing the data/schema is done with one simple command. Looks like
I need to build that out if I want  something similar in Omnis...hmmmm...


Andrew


On Thu, May 8, 2025 at 3:14 AM Phil (OmnisList) <phil at pgpotter.co.uk> wrote:

> Hi,
>
> So, our take on this...
>
> We never considered rolling back a database change...
>
> A database for us holds a version number, and only upgrades can be made
> to it, so old libraries can attach, but cannot change the data
> structure, so may fall over if they are requiring a missing field....
>
> But our system checks the version, and the library would be upgraded
> immediately if it was not in line with the data, so this is not an issue.
>
> Our code can drop columns, but we don't tend to do that.
>
> We only use schemas as the definition of the table structure, and only
> those with a 'Create' in the userinfo property are expected to be in the
> sql database.
>
> We start by getting a table list on the database, and compare with the
> schemas we have to be created, and create any missing ones.
>
>    Do loStatObj.$tables(kStatementServerTable)
>    Do loStatObj.$fetch(llDBTableList,kFetchAll)
>
> After that, we loop through each schema, and then each column within
> that schema, comparing with what is on the server to the schema, and
> changing as required...
>
> so
> Do loStatObj.$columns(pcTableName)
> Do loStatObj.$fetch(llDBTableCols,kFetchAll)
>
> compared to
> Do
> $schemas.[pcSchemaName].$objs.$makelist($ref.$name,$ref.$coltype,$ref.$colsubtype,$ref.$colsublen)
>
> Returns llSchemaCols
>
> We don't tend to change data types, just maybe the length of character
> fields, so our ALTER COLUMN code is quite limited.
>
> Our code runs automatically when the database version is behind this
> library version, so no manual upgrade is required remotely.
>
>
> This works for us, but some of the other solutions sound more involved,
> and can roll back data changes... never had need for that.
>
> regards
> Phil Potter
> Based in Chester in the UK.
>
> On 08/05/2025 01:07, Doug Easterbrook via omnisdev-en wrote:
> > hi Rudolf
> >
> >
> > I agree with you.    Your "If I were to do this again'" option is
> actually what we implemented a number of years back, after trying the other
> two and finding some the odd issue of not being able to rollback quickly ..
> so we expanded upon the second option  I mentioned to handle rollbacks.
> >
> >
> > It has come in handy once in a while to roll back….so its a definite
> benefit — you write the rollback code at the same time you write the
> upgrade code so that you know how to get out of the mistake, should one be
> made, all while the upgrade code is familiar in your head.
> >
> > It is probably a best practice if you have a lot of separate databases
> to upgrade and you need to be able to roll back to previous version quickly.
> >
> >
> >
> >
> > Doug Easterbrook
> > doug at artsman.com
> > Phone (403) 650-1978
> >
> >> On May 7, 2025, at 12:12 PM, Rudolf Bargholz<rudolf at bargholz.ch> wrote:
> >>
> >> Hi Andrew,
> >>
> >> Here another option to those that Doug mentions below.
> >>
> >> I wrote code to sync schemas in our app with DB2 many years ago. It
> adds and migrates columns or tables, adds, modifies, or removes sequences,
> functions, stored procedures, indexes, combined indexes, does runstats.
> Saved us a lot of time for the past over 20 years. All we need to do is
> add/modify a schema and then sync the schemas with the database. Was a lot
> of code, but makes database migrations very easy for us.
> >>
> >> If I were to do this again, I would probably choose a different option.
> >>
> >> Create a table in the database that contains the last datetime of a
> migration: char string yyyymmddhhnnss
> >> Create a class that contains methods. Each method is of the form
> yyyymmddhhnnss
> >> Each method has a "Switch" statement with " Case 'up' " and " Case
> 'down' "
> >> The "up" executes the explicit SQL for the actual migration. This
> allows you to not just add a new column, for example, but also initialize
> the values in that new column, in a probably more complex manner than would
> be possible in an automated workflow.
> >> The "down" reverts the change, optionally reverting previously executed
> additional SQL.
> >>
> >> After each migration, you update your database migration table by
> inserting the value of the just executed "up" datetime value. Reverting the
> migration will just delete all the migrations one by one in the table after
> the DOWN code has been executed.
> >>
> >> You can create an admin window that allows you to either migrate UP or
> migrate DOWN again.
> >>
> >> The format of the datetime allows you to order the methods ascending,
> so that you can migrate each method one by one in ascending order.
> >>
> >> When you open your app, you can find the max value of the methods, and
> compare this to the value in your database, to check if there are any
> migrations to execute.
> >>
> >> Regards
> >>
> >> Rudolf Bargholz
> >>
> >> -----Ursprüngliche Nachricht-----
> >> Von: omnisdev-en<omnisdev-en-bounces at lists.omnis-dev.com> Im Auftrag
> von Doug Easterbrook via omnisdev-en
> >> Gesendet: Mittwoch, 7. Mai 2025 18:31
> >> An: OmnisDev List - English<omnisdev-en at lists.omnis-dev.com>
> >> Cc: Doug Easterbrook<doug at artsman.com>
> >> Betreff: Re: Automatic Updating Database Schema
> >>
> >> hi Andrew.
> >>
> >> you didn’t say what database you were using, so I’ll speak from the
> perspective of postgres.
> >>
> >> we’ve done it a number of ways, each with their own advantage.
> >>
> >>
> >> Option 1: sync the schema’s
> >>
> >> We always have a file class that matches the schema for each table in
> the database.  There are a number of reasons for that, such as
> >> - code completion in the IDE for variables
> >> - tooltip help for field names
> >> - definitions of variable type and length  (field.$type, and
> field.$subtype) as I recall
> >> - usage as temp global variables when merging with web pages and owrite
> and more
> >>
> >> ok, with that in mind, you can:
> >> 1) read your entire database schema in postgres to get the tables and
> fields — all in one sql statement
> >> 2) do a make list of all variables in your file classes, the type and
> subtype
> >> 3) sort them
> >> 4) run a loop on the file list and see if it is in the database list
> (hint, we use TMOBJS bindary search to make this fast.  omnis $search will
> slow it down considerably)
> >>
> >> if you find a missing field you can
> >> 1) create the SQL to alter table add field default ’some value’.     we
> default char to blank, dates to null, numbers to zero, boolean to false etc.
> >> 2) look at the omnis 'indexed' property for the file.variable — and
> then use that to create an index of you want.
> >>
> >> This generally works, but does not have a couple of disadvantages.
> It is the basis for a data dictionary in your application, and you can add
> stuff to your $info parameter on the field that can beef this up.  for
> example, if you want a different default value , you might put it in the
> $info in some json.
> >>
> >>
> >> NOTE: one of the topics to be covered at EUromnis 2025 will be a data
> dictionary — by Dan Ridinger.   I’m sure he’ll have a whole lot more during
> his presentation.
> >>
> >>
> >> ———————
> >> second option that we use more often is:
> >>
> >> we have a field in the database that indicates the current schema
> version.    lets say it is '5.6000'
> >> and in the program, we might say that the program version is '5.6001'
> >>
> >> the table is read on start up to get the version of the database.
> >>
> >> then we have startup code that compares the database to the program
> version.  should they be different, we run through a routine that does all
> the upgrade stuff required, since you may have:
> >> — new fields to add
> >> — old fields to delete
> >> — data migrations to happen (eg update all data in a new field or an
> empty field to be some specific value that could be fixed, or could be
> calculated)
> >> — missing indexes to add
> >> — new database functions to add
> >> — old functions to drop
> >>
> >> or what have you.
> >>
> >> in other words, this approach is a bit more scripted.   Upside, more
> flexibility in what you can do PLUS ability to roll back a version and
> rerun the updates..  Downside, you have to add a method call for each of
> the things you want to do.
> >>
> >> example:
> >>
> >> if databaseVersion < '5.6000'
> >>    do method Update56000
> >> end if
> >> if databaseVersion < '5.6001'
> >>    do method Update56001
> >> end if
> >>
> >>
> >> what might be in one of those updates:
> >>
> >> do method AddTable('MyNewTable')  ## add all fields for the table
> >> do method Add
> Field('MyTable','NewFieldName','DefaultValueForTable','HowToBackfillData')
> >> do method AddIndex('myTable','MyFieldName')
> >> do method AddFunction('StringContainingFunction’)
> >> do method DropFunction(’NameOfFunction’)
> >> do method MigrateDataForTable('xxyy')
> >> do method VaccumTable(’myTable’)  ## sometimes important for postgres
> >>
> >> etc.
> >>
> >>
> >> it gives a nice replayable set of SQL that lets you update things, plus
> it documents what you wanted to do.. and if you make a mistake in some
> update, you can then fix the routine and re-do it in a future update.
> >>
> >> since you are only giving constants for table and field names, you can
> get the values form the file class for ’type' and 'subtype'
> >>
> >>
> >>
> >> Final thought, if you are using omnis database workers, I might suggest
> a futher refinement (which we do)
> >>
> >> 1) figure out the SQL for each of the calls.  eg it might be 'alter
> table mytable add column mycolumn integer default 0'
> >> 2) add that SQL to a table called SQLJobs (name is suggested)
> >> 3) then have something later on work through the SQLJOBS as a
> background worker so that it can be done separately from the program
> >>
> >>
> >> what might be in SQLJObs
> >> job_seq
> >> job_status (not done, done, error)
> >> job_version
> >> job_description
> >> job_sql
> >> job_preconditions  (i.e. job can’t run until job_seq is 'done')
> >>
> >> meaning you can’t index a field or backfill it until the job is run
> that creates the field.
> >>
> >>
> >>
> >> We’ve run this approach for about 20 years….   works well for us.
> helps allow things to run 24x7 so people can use the app while we update
> the schema in the background.
> >>
> >>
> >>
> >>
> >>
> >> Doug Easterbrook
> >> doug at artsman.com
> >> Phone (403) 650-1978
> >>
> >>> On May 7, 2025, at 8:55 AM, Andrew Stolarz<stolarz at gmail.com> wrote:
> >>>
> >>> Hello Everyone,
> >>>
> >>> I'm wondering how others are doing this.
> >>>
> >>> When you add new columns to an omnis Schema, how are you adding the
> columns
> >>> to your SQL database?
> >>>
> >>> Is there any sleek way you guys have come up with that will look at the
> >>> Omnis schema and add the new columns into the DB automatically?
> >>>
> >>> Any advice or direction would be appreciated!
> >>>
> >>>
> >>> Andrew
> >>> _____________________________________________________________
> >>> Manage your list subscriptions athttps://lists.omnis-dev.com
> >>> Start a new message ->mailto:omnisdev-en at lists.omnis-dev.com
> >> _____________________________________________________________
> >> Manage your list subscriptions athttps://lists.omnis-dev.com
> >> Start a new message ->mailto:omnisdev-en at lists.omnis-dev.com
> >> _____________________________________________________________
> >> Manage your list subscriptions athttps://lists.omnis-dev.com
> >> Start a new message ->mailto:omnisdev-en at lists.omnis-dev.com
> > _____________________________________________________________
> > Manage your list subscriptions athttps://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 mailing list