Results 1 to 7 of 7

Thread: Lianja 5 continuous integration of database schema updates

  1. #1
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,786

    Lianja 5 continuous integration of database schema updates

    Continuous Integration (CI) is the process of automating the build and testing of code every time a team member commits changes to version control.

    CI encourages developers to share their code and unit tests by merging their changes into a shared version control repository after every small task completion. Committing code triggers an automated build system to grab the latest code from the shared repository and to build, test, and validate the full master branch (also known as the trunk or main).

    Thats all well and good for code intensive development but what happens with data centric development?

    Many enterprises planning a move to cloud-based systems fail to realize the importance of data centric development and the cost savings associated with it. Let me explain data centric development and how changes can be deployed to live cloud-based systems with little to no downtime.

    Building cloud-based business applications is speeded up by using a purpose built database that does most of the heavy lifting for you by embracing a “Design by thinking” methodology.

    Data centric development is the process of building applications that are driven by the business rules and metadata that are described in the data model i.e the database.

    The use of table-driven logic, i.e. behavior that is dictated by the contents of a database, allows applications to be simpler, more flexible and easier to maintain.

    Applications developed using the traditional technique of visually building a UI and embedding business rules in the UI are too inflexible and more difficult to maintain.

    Unfortunately most industry standard SQL databases do not provide the necessary functionality to enable data centric development.

    Lianja has been specifically engineered to provide this functionality for both its native and third party databases.

    Lianja 5 continuous integration.


    I have been working on adding database comparison tools into the database engine and I am pleased to say that this will be available in Lianja 5.

    One of the challenges in Web and Mobile Apps is to provide a seamless upgrade experience while the web/mobile apps are being used by end users. It is clearly not practical to ask users to "log off" as they can be logged in from all over the world and be operating in different timezones.

    Lianja 5 includes the ability to apply database and app updates to live systems without affecting the logged in users. Database schema updates can be deployed as a Lianja package.

    Let's see how this is accomplished.

    You can compare two databases and generate an upgrade script which will update an existing database with delta changes.

    This is typically accomplished by taking a snapshot of a database when it is deployed and using this as the baseline database to compare with.


    You can take a database snapshot like this:

    Code:
    copy database southwind to southwind_v42
    If development of “the next version” causes changes to the database and any business rules you can have an upgrade script generated for you that when executed will put the live database and the development database in sync with each other without losing any of the live data.

    To generate the upgrade.prg script you use the new "compare database" command.


    Code:
    compare database db1 with db2 [table name] [to file upgrade.prg]
    


    When you compare two databases a script is generated that when run will upgrade the live database with all of the latest:

    Database triggers
    Database metadata
    Database stored procedures

    Tables
    Table structure/schema
    Table/column business rules
    Table/column triggers
    Table/column metadata
    Table indexes

    Virtual tables
    Virtual table definitions
    Virtual table/column business rules
    Virtual table/column triggers
    Virtual table/column metadata

    If you specify to file upgrade.prg then the script will be saved to the specified file. You can include this file in a package to be deployed to a live Lianja Cloud Server and the database will be automagically upgraded without down time.


    It is important that you test this upgrade script to make sure it works as expected before deploying it.

    Here is an example:

    Name:  Screen Shot 2019-01-09 at 12.55.13 PM.jpg
Views: 307
Size:  83.0 KB

    Name:  Screen Shot 2019-01-09 at 12.55.37 PM.jpg
Views: 309
Size:  62.8 KB

    I am currently writing white paper "
    Essential guide to data centric development" which provides more details than is presented here.







    Last edited by barrymavin; 2019-01-09 at 00:05.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  2. #2
    Lianja MVP
    Join Date
    Feb 2012
    Location
    Berea, KY, USA
    Posts
    1,963
    Hi Barry,

    Fantastic and incredible.

    Presumably, upgrade.prg if placed in the database directory in the dev environment would perform the same function, based on existing functionality, correct?

    thanks,

    Hank

  3. #3
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    5,786
    Hi Hank,

    When a lpk package is autoinstalled if upgrade.prg is contained within it then it is executed no matter where it is. You can therefore place it wherever you want and just include it in the lpk.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  4. #4
    Senior Member
    Join Date
    Feb 2012
    Location
    Rome - Italy
    Posts
    1,882
    Yes, this is very wonderful.

  5. #5
    Senior Member
    Join Date
    Feb 2012
    Location
    Rome - Italy
    Posts
    1,882
    In the past, to update a DB I have always used a different approach.

    having many customers, I do not know that "version" of DB have ..

    so I do the opposite:
    I copy the new DB structure into a new DB.
    from the client, I copy the new DB structure
    then, with a script:
    I copy the client's DB into a "_old"
    I replace the customer's DB with the new empty DB
    for each table of the new DB, I make an append from the table "_old".

    but with this new function, I can review my procedure.

  6. #6
    Lianja MVP
    Join Date
    Feb 2012
    Location
    Berea, KY, USA
    Posts
    1,963
    HI Barry,

    This line in the example:

    Code:
    compare database db1 with db2 [table name] [to file upgrade.prg]
    has an optional argument [table name} that is not show in the COMPARE DATABASE doc: https://www.lianja.com/doc/index.php/COMPARE_DATABASE

    I was wondering a) the purpose/usage and b) whether the purpose/usage is in fact present.

    thanks,

    Hank

  7. #7
    Lianja Team yvonne.milne's Avatar
    Join Date
    Feb 2012
    Location
    Berkshire, UK
    Posts
    1,421
    Hi Hank,

    I have updated the doc with the missing clause and also added in the missing compare table command.

    Specifying the table clause on the compare database command compares that specific table and database elements (metadata, stored procedures etc.) whereas the compare table command compares just the specified table.

    Regards,

    Yvonne

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Journey into the Cloud
Join us