COMPARE DATABASE

Purpose

Compares two databases and optionally creates an upgrade Lianja/VFP script

Syntax

COMPARE DATABASE <databasename1> WITH <databasename2> [TABLE <tablename> ] [TO FILE <filename.prg> [CREATE <databasename3>]]

See Also

ADATABASES(), ADD TABLE, ALTER INDEX, ALTER TABLE, CHECK DATABASE, CHECK TABLE, CLOSE DATABASES, CLOSE TABLES, COMPARE TABLE, COMPILE DATABASE, COPY DATABASE, CREATE DATABASE, CREATE INDEX, CREATE TABLE, CREATE VIEW, Database Events, DATABASE(), DBUSED(), DISPLAY DATABASE, DISPLAY INDEXES, DISPLAY SCHEMAS, DISPLAY TABLES, DROP DATABASE, DROP INDEX, DROP TABLE, GETENV(), INDEX, Lianja Package Files, Lianja Package Manager, LIST DATABASE, LIST INDEXES, LIST SCHEMAS, LIST TABLES, OPEN DATABASE, PACK, PACK DATABASE, REBUILD DATABASE, REINDEX DATABASE, SET AUTOCATALOG, SET EXCLUSIVE, USE

Description

The COMPARE DATABASE command compares two databases, <databasename1> and <databasename2>, and optionally generates an upgrade script, <filename.prg>, that can be packaged up and uploaded to a live system. A script named upgrade.prg (compiled to upgrade.dbo) is automatically detected and installed by the Lianja Cloud Server without requiring any machine downtime when included in a deployed Lianja Package File.

By default, all tables in <databasename1> are compared, along with their indexes and constraints/rules and the database metadata, stored procedures and events. If the optional TABLE <tablename> clause is included, only the specified table is compared (and its indexes and constraints/rules and the database metadata, stored procedures and events).

Individual tables can also be compared using the COMPARE TABLE command.

Example

// The 'production' database has been deployed to the server.
// On the development system the 'production' database
// has been copied to 'production_live'.
// Development continues and changes have been made to
// the development 'production' database.
// The following can be used to produce upgrade scripts
// to update the 'production' database on the server.
compare database production with production_live  to file upgrade.prg
compare database production with production_live table customers to file upgrade.prg
 
// After the compare, package and deploy the upgrade script to the server
// where it will be run on the server version of the 'production' database
 
// On the development system, copy the 'production' database
// to 'production_live': the latest production server database
// table structures, indexes etc., overwriting the previous backup
copy database production to production_live if exists
 
// Development can then continue using the 'production'
// database and it can subsequently be compared to the
// 'production_live' database
compare database production with production_live  to file upgrade.prg

Lianja v6.3 Enhancements

From Lianja v6.3, schema changes can be deployed directly using the COMPARE DATABASE command as in the example below.

When an App is accessed with Preview live in browser, the dbc_upgrade script will be autodetected and executed (then removed) to upgrade the live database without any other deployment being required.

A new App delegate Before preview has been added to provide Continuous Integration. This delegate is called with the name of the App's database as a parameter. Adding code to this delegate, as in the example below, will cause the comparison and upgrade to be carried out each time Preview live in browser is run.

Example

// Delete the old development copy of the live database
drop database production_live
 
// Make a new development copy of the live database
copy database "cloudserver:/tenants\public\data\production" to production_live
 
// Compare the latest development and live databases
compare database production with production_live
 
// Deploy the upgrade script to update the live database
compare database production with production_live to "cloudserver:/tenants\public\data\production\dbc_upgrade.prg"

Lianja v9.4 Enhancements

From v9.4.8, the optional CREATE <databasename3> clause adds commands to the start of the upgrade script to drop (if exists), create and open the specified database so that the changes in the script are applied to that database.

Example

// Create an empty database and compare the southwind database
// with the empty database
create database dummy
close data
compare database southwind with dummy to file southwind2.prg create south2

The resulting upgrade script can then be run to create the south2 database with the same tables, indexes, database metadata, stored procedures and events as southwind, without the data.

//////////////////////////////////////////////////////////////////
// compare database southwind with dummy
//////////////////////////////////////////////////////////////////
local m_exclusive = set('exclusive')
set exclusive on
drop database south2 if exists
create database south2
open database south2
...