An app needs a "Setup the Database" routine correct ?

[Clifford Ilkay]

We have a hybrid vertical market application consisting of and Omnis Studio
4.3.2.1 fat-client library and AngularJS, Node.js web application going
against Microsoft SQL Server. The application has more than 700 of
everything - database tables, windows, reports. I've written about this
application before so search for "AngularJS" and "Clifford" in the archives
if you're curious why we have a hybrid application. I'll just cover what we
do in terms of the database setup and ongoing changes, something that is
commonly known as "schema migration".

All of our customers run their own SQL Server instances. The application
consists of an Omnis library, an installer for the XCOMP that embeds the
open source Chromium browser within an Omnis window (CEF XCOMP), a Debian
Linux virtual machine appliance that runs the AngularJS/Node.js web
application and a Django application to configure the virtual machine
appliance on first boot, and a text file containing the scripts that will
create the SQL Server database and everything within it, tables, indexes,
stored procedures, etc.

We essentially have two completely different applications, Omnis and the
web application, which are completely different technology stacks running
on different machines that are doing CRUD (Create, Read, Update, Delete)
operations on the same SQL Server database. There must be only one source
of truth about what that SQL Server database looks like whether it's the
SQL script that we're distributing, Omnis, or the web application. For
historical reasons, it's Omnis. We have a schema migration utility
implemented in Omnis that checks for the version stored in the db, let's
call it X, and compares it to a version embedded within a format, let's
call it Y, and if the version in Omnis is greater than the version in the
db, prompts the user to run the schema migration utility and if the user
clicks "OK", it grinds away and generates what we call a "differences
script". That script can then be run against another SQL Server at version
X and migrate the database schema and, if necessary, the data, to version Y.

The web application queries SQL Server and generates the Javascript classes
that it needs corresponding to the SQL Server schema. Omnis generates the
script to manage the SQL Server schema. The script is run and applies the
changes to SQL Server. The web application generates class definitions from
SQL Server. In effect, it's the same as if we were generating the
Javascript classes directly from Omnis. We're not. We have an intermediary.
Omnis knows nothing of the existence of the web application other than
sending messages to and receiving messages from the web application through
an interface in the CEF XCOMP.

Once we have converted every window and report in the Omnis fat-client
application to AngularJS/Node.js, we will have eliminated our dependency on
Omnis and hence, the custom-built, very complex, and still missing features
schema migration utility that we built using Omnis. That will be replaced
with the built-in schema migration utility in the Node.js framework we are
using.

Microsoft now ships SQL Server on Linux. I can see using the same
technology we're using to distribute and manage our virtual machine
appliances to distribute and manage the databases. For those customers who
want a "plug it in and it works with some simple configuration data you
enter in a web form", it's perfect because it will not require the kind of
technical sophistication the current situation requires. We can script the
creation, schema migration, backups, all of it and have it all driven by a
web form.



Regards,

Clifford Ilkay

+1 647-778-8696

On Wed, Jan 24, 2018 at 10:15 PM, Alex Clay <aclay at mac.com> wrote:

> On Jan 24, 2018, at 20:44, Doug Easterbrook <doug at artsman.com> wrote:
>
> > with postgres, you just run your app.. constantly ..  adding the field
> when you want —the act of adding a field does not affect anybody else.
>
> And PostgreSQL allows you to modify the schema in a transaction so if
> something goes wrong, just roll back!
>
> For more jargon, that's running DDL (data definition language) statements
> under MVCC (multi-version concurrency control).
>
> Alex
>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
>