PRODUCT:  R:BASE                   VERSION :  3.1 or Higher
     AREA   :  Programming In R:BASE    CATEGORY:  Other         
     Once applications have been distributed and are up and running at 
     customer sites, there comes a whole new set of problems. The client has 
     requested changes to the application. If the changes only involve 
     program code, you can send them new application files. But what about a 
     new report, for example. How do you update the database? It could be at 
     a site thousands of miles from your location. The client has data in 
     their database that must be maintained, as well as existing forms, 
     reports, rules etc. With some basic understanding of how R:BASE stores 
     forms, reports, and other system information - the updating process 
     becomes simple. 
     Updating Forms and Reports 
     R:BASE forms and reports are just data stored in the SYSFORM and SYSREP 
     tables respectively. To transfer this data, simply unload the data from 
     these tables into an ASCII file and load the data, thus creating the 
     new form or report, into the client's database. The following code 
     example shows the process of unloading a form (orders) and a report 
     (sales) from your development database into a file, update.unl, for 
     transfer to the client's database. 
     CONNECT concomp
     SET NULL -0-
     OUTPUT update.unl
     UNLOAD DATA FOR sysform WHERE sysfname = 'orders' 
     UNLOAD DATA FOR sysrep WHERE sysrname = 'sales'
     OUTPUT screen 
     When transferring reports from one database to another it is vital 
     that you set the NULL symbol to -0-. If NULL is set to a blank, the 
     form and report become corrupted upon loading into the new database. 
     Updating Existing Tables 
     Adding columns to existing tables is a fairly simple process. The 
     R:BASE command language includes a command, ALTER TABLE, for adding 
     a column to an existing table. Assume you had the following transactions 
                  Trans_Id        INTEGER
                  Cust_Id         INTEGER
                  Trans_Date      DATE
                  Model           TEXT 10
                  Quantity        INTEGER
     The client requests addition of a date shipped column to the table. 
     The following ALTER TABLE command adds the required column. 
     ALTER TABLE transactions ADD Ship_Date DATE 
     Creating New Tables and Views
     Another situation that commonly arises is when the client requests a 
     new module to be added to an existing system. An example is adding the 
     ability to track suppliers. Adding this option requires a supplier 
     table, forms and reports (see above for form/report techniques). The 
     following CREATE TABLE command is used to update the database. 
     CREATE TABLE suppliers +
             (Supp_Id INTEGER +
             Supp_Name TEXT 20 +
             Supp_Add TEXT 20 +
             Supp_City TEXT 15 +
             Supp_St TEXT 2 +
             Supp_Zip TEXT 5)
     Adding Views to the Database
     New views in the database can be incorporated in the same way as a new 
     table. The CREATE VIEW command is added to the update command file and 
     the new view is created in the client's database. The following CREATE 
     VIEW command illustrates creating a simple two table view.
     CREATE VIEW vTransSummary as +
             SELECT TransDate, SalesRep, Qty, Model, Price +
             FROM TransMaster T1, TransDetail T2 +
             Where T1.TransId = T2.TransId 
     Updating existing rules or creating new rules within a database is also 
     done through the command language. The following RULES command creates 
     a rule that requires entry of an employee last name. 
       RULES 'Employee last name is required' FOR employee +
     To update a rule use the DROP RULE command to remove the old rule then 
     the RULES command to add the new rule definition into the database. For 
     more information on defining rules consult the Command Dictionary 
     section Rules. 
     Upgrading Application Code
     Upgrading your application code is the easiest part. All you need to do 
     is to set up commands to copy the new application code into the 
     directory where the existing application code resides. 
     Putting It All Together
     Now that you see how to address changes in the different sections of a 
     database, combine all the separate commands together into one command 
     file for updating the client's system. It's a good procedure to make 
     sure the client has a backup of their database before running the update 
     command file.
     Typically, first thing the update command file does is ask the client 
     if they have backed up the database and provide them with a YES/NO 
     prompt for response. If they answer NO, display a message on the screen 
     asking them to backup the database, then re-start the update process. 
     The update does not execute unless the user responds YES. Rather than 
     copying the update files to the client's computer, reference the files 
     from the A: drive. This keeps the application directory clean and free 
     of temporary files.
     -- File name: inst_upd.rmd
     -- Function : To install update of the sales database and application 
     -- Called by:  inst.bat
     SET ERROR VAR evar
     SET VAR ehold INTEGER
     CONNECT Sales
     SET VAR ehold = .evar
     -- Check to see if the database was successfully connected
     IF ehold <> 0 THEN 
       WRITE '-------------------------  Error  -------------------------' +
        AT 10 25
       WRITE 'The update program was unable to open the SALES database.' + 
        AT 11 10
       WRITE 'Please verify this program is being run in the directory' +
        AT 12 10
       WRITE 'with the SALES database. ' AT 13 10
     WRITE 'The SALES database MUST be backed up before running this update' +
             AT 5 5
     DIALOG 'Has the database been backed up  ? ' vcont vkey No
     IF vcont = 'Yes' THEN 
     -- Remove old forms/reports and load new form/report definitions into 
     -- database
       DROP FORM orders
       DROP REPORT sales
     -- load the ASCII file containing new forms and reports data
       RUN a:\update.unl
     -- Add the ship date column to the transactions table 
       ALTER TABLE transactions ADD Ship_Date DATE
     -- Create the suppliers table
       CREATE TABLE suppliers +
        (Supp_Id INTEGER +
         Supp_Name TEXT 20 +
         Supp_Add TEXT 20 +
         Supp_City TEXT15 +
         Supp_St TEXT 2 +
         Supp_Zip TEXT 5)
     -- Create the view vTransSummary
       CREATE VIEW vTransSummary as +
        SELECT TransDate, SalesRep, Qty, Model, Price +
        FROM TransMaster T1, TransDetail T2 +
        WHERE T1.TransId = T2.TransId 
     -- Add the Rule for employee last name. 
       RULES 'Employee last name is required' +
        FOR employee +
        SUCCEEDS +
        WHERE EmpLName IS NOT NULL
     -- Update the application file from the a: drive
       COPY a:\SalesApp.apx
     Initiating The Update 
     Because user knowledge varies, it is best to assume the user performing 
     the upgrade has little to no knowledge of computers. Supply a DOS batch 
     file to start Runtime and process the update command file. This elimi-
     nates another area for failure. The only information the user has to 
     provide is the drive and directory where the application is installed. 
     This can be eliminated also if you have control over the clients environ-
     ment and know where the product was installed. The following DOS batch 
     file requires the user to pass the drive and directory where the product 
     was installed, then starts Runtime and executes the update command file. 
     @ECHO OFF
     REM     Batch file to upgrade the Sales database and application 
     REM     Parameters Expected: drive and directory where application is 
     REM     installed
     REM     Check to see if the user passed in the drive and directory. 
     IF "%1"==""   GOTO :USAGE
     IF "%2"==""   GOTO :USAGE
     REM Change to drive with product 
     REM Change to directory where product is installed
     CD \%2
     REM Start Runtime and perform update. 
     RTIME inst_upd.rmd
     REM Branch over usage
     GOTO :END
     @ECHO  Usage:   INSTALL C: XXXX
     @ECHO              C: - Drive where product is installed
     @ECHO              XXXX Directory where product is installed. 
     The above DOS batch file was written for MS-DOS 5.0, if you are using 
     another version of DOS or are looking for more information on how this 
     batch file works consult your DOS manual.