=====================================================================
Converting to R:BASE 4.5
=====================================================================
PRODUCT: R:BASE VERSION: 4.5
=====================================================================
AREA: Conversion CATALOG: GENERAL INFORMATION
=====================================================================
The current database format (3.x and 4.0) has been used since R:BASE
System V, 6 years. With R:BASE 3.1 new data types and features were
added that affected the structure of a database but the underlying
format of the database did not change. Instead, a new system table,
SYSINFO, was added to keep track of these new features and data types.
The underlying database format was not changed to ensure compatibility
with previous versions of R:BASE mainly R:BASE for DOS, 2.11.
The time has come, enough new features have been added, where if the
same database format is kept, performance will be severely degraded.
By using a new database format changes can be incorporated to data
types and indexes with maximum performance.
Converting from 3.x or 4.0
Converting your database
The conversion process is straightforward and easy. The conversion
utility, NEWDB, creates a new database and the three database files
have new names -- dbname.RB1, dbname.RB2, dbname.RB3. Now you can have
an eight character database name. Because new database files are made,
make sure you have sufficient disk space before starting the
conversion process. The conversion utility makes an estimate of the
amount of disk space needed, and it needs that much space on a single
drive. In general, you will need more available disk space than the
current database uses. Once the database has been converted you can
split the .RB1, .RB2 and .RB3 files across different drives, but the
conversion program needs to be able to put all three in a single
location.
The data is file2 is stored slightly differently in 4.5. Now, each
table will take up at least 8K (8192 bytes) of disk space regardless
of the number of rows. Even if a table has just one row, it will
require 8K of disk space. Each table's rows are now stored in 8K
blocks rather than just individual rows. This means that there are
always 8K of contiguous rows of data for faster retrieval. Because
space for rows of data has been pre-allocated, you may not see the
size of file2 change as you add rows in 4.5. The size of file2 will
change only when R:BASE needs to add a new 8K block for a table.
As with the 2.11 to 3.x, 4.0 conversion utility, DBCONV, NEWDB
converts all of your system information (forms, reports, rules, views,
computed columns, passwords) to new system tables and formats for 4.5.
In 4.5 you will always have all 15 system tables in your database. You
cannot drop a system table. Your data tables are converted to the new
storage format. NEWDB has three options for converting indexes:
convert all indexes, convert all non-text indexes, convert with no
indexes. It is prudent to see if any of your indexes ccan be replaced
with primary/foreign key or not null constraints. Also, rules can be
removed because of the comprehensive power of constraints.
Constraints are new to 4.5, read about them in "Using Constraints in
4.5" in this issue of the Exchange.
* Convert all indexes
When no option is specified (NEWDB dbname) all indexes are converted.
NEWDB creates indexes for all indexed columns. All indexes are built
storing the full data value, text indexes are built as full text
indexes. The indexes are named with numbers, starting with 15.
Everything will work as it did in 3.x or 4.0. To change indexes on
TEXT columns to full text or partial text indexes, to define multi-
column indexes, or to replace indexes with primary, foreign key or
other constraints, drop the index in 4.5 and recreate it. Use the LIST
INDEXES command at the R> prompt to see a list of converted indexes.
* Convert all non-text indexes
When the NOTEXTKEYS option is specified (NEWDB dbname NOTEXTKEYS) all
non-text indexes are converted. NEWDB creates indexes for all indexed
non-text columns, no indexes are created for text columns. The indexes
are named with numbers, starting with 15. Create the desired index
type on TEXT columns using the Info Create/modify menu or the CREATE
INDEX command. Drop indexes to replace them with primary, foreign key
or other constraints or to build multi-column indexes. If using this
option, be sure to list all text column indexes before converting. If
you do not reindex a text column you can affect performance.
* Convert with no indexes
When the NOKEYS option is specified (NEWDB dbname NOKEYS) no indexes
of any kind are converted. NEWDB converts the structure and data only
and builds indexes for system tables. No indexes are built on user
tables. This option can be used if disk space is a concern. After
conversion, database file3 (dbname.RB3) can be placed on a different
drive and indexes built. Define indexes and primary, foreign key and
other constraints in 4.5 after the database is converted using the
Info Create/modify menu or the CREATE INDEX command. When using this
option, be sure to list all indexes in the database before converting.
If you do not reindex a column you can affect performance.
* Converting your application
Most 3.x, 4.0 applications will run in 4.5 with no conversion and no
changes necessary. You will want to review your applications, however,
to see where you can take advantage of new features, such as nested
text functions. Applications will automatically run faster, but
reducing code by using new programming features and applying some of
the new optimization features can provide even more speed.
If your application does not run correctly in 4.5, it may be a problem
with the new WHILE loop optimization feature. SET WHILEOPT OFF at the
R> prompt or TRACE the application and try it again. If it runs then
you know there is a problem with an optimized command and the problem
is most likely with a variable used in that command. See the section
below on WHILE loop optimization for more information.
* Obsolete commands
A number of old commands were no longer documented in R:BASE 3.x and
4.0. While the commands would still execute in 3.x and 4.0, they were
replaced by other commands which were more efficient and SQL
compliant. HELP OBSOLETE showed a list of those commands. We
recommended that you no longer use those commands when writing new
programs, and replace them whenever possible in converted applications.
The old commands were candidates to be removed completely from future
versions of R:BASE instead of just being removed from the
documentation. With the changes to 4.5 database structure, some of
these obsolete commands will no longer execute.
The truly obsolete commands are EXPAND, REDEFINE and DEFINE, including
the DEFINE subcommands OWNER, COLUMNS, TABLES and PASSWORDS. Executing
EXPAND, DEFINE or REDEFINE from the R> prompt will generate "-ERROR-
Obsolete command.". From a command file, the commands are ignored, no
error is generated. If you use DEFINE mode or the REDEFINE or EXPAND
commands in an application you will need to replace those commands
with CREATE TABLE, ALTER TABLE or GRANT.
Obsolete commands that should be replaced in your application code
even though they may still execute in 4.5 are: APPEND, BUILD KEY,
DELETE KEY, CHANGE, REMOVE, VIEW, SET POINTER and SORTED BY. Support
for these commands will be completely removed from future versions of
R:BASE.
* While loop optimization
WHILE loops were always considered one of the faster ways to process
code because R:BASE read the WHILE loop code into memory and parsed it
before beginning execution. R:BASE did not need to do line-by-line
reading and parsing of the code for each iteration of the WHILE loop.
As it is read into memory the WHILE loop code was parsed in tokens
(4-byte segments). But R:BASE still needed to figure out what was what
-- parse the expressions, find variable names and values etc. It was
faster than reading and parsing line-by-line, but not as fast as it
could be.
In 4.5, WHILE loops have been made much faster. Some of the commands
in WHILE loops are optimized as well as parsed when the WHILE loop is
read into memory. Optimization means that information about the
command is stored in a binary format that can be easily processed.
Optimized commadands and variable names, type and location are stored
as numbers and expressions are stored in binary; less storage space
and faster identification for execution.
For example, when a BREAK is encountered, R:BASE stores a number
identifying this as a BREAK command and whether it breaks out of a
SWITCH or out of a WHILE. Then when the WHILE loop executes, R:BASE
knows immediately how to process the BREAK command, it doesn't need to
determine that the command is BREAK on each iteration of the loop, it
does it once and stores that information. For CASE, R:BASE stores a
number identifying the command as CASE and stores the values (labels)
into a memory location., Each iteration through the WHILE loop, R:BASE
compares the SWITCH variable to a memory location - ot to a value.
R:BASE doesn't need to read and evaluate all the separate CASE
statements again, it goes to the memory location where it has stored
the CASE labels, retrieves the CASE number and knows which section of
code to execute next.
The commands ENDWH, CONTINUE, BREAK, ELSE, ENDIF, ENDSW, DEFAULT,
RETURN and CASE are always optimized. These are essentially static
commands to start with and it is easy for R:BASE to write the
necessary information to a specific memory address.
The SET VAR, WHILE, IF, SWITCH and FETCH commands are not always
optimized. Sometimes you can modify your code to have these commands
optimized, other times you won't want to make the change. The
discussion that follows is talking about these commands only as they
are used inside a WHILE loop. Outside of a WHILE loop these commands
are not optimized
SET VAR commands are optimized only when the command is SET VAR
vname = something. In addition, the variable, vname, must exist when
the WHILE loop is first read. Something can be a value, another
variable name or an expression, but its parts must exist when the
WHILE loop is first read. SET VAR commands that include the datatype
of the variable, or set multiple variables with one command are not
optimized. For example,
SET VAR vdate = .#date -- is optimized
SET VAR vdate DATE = .#date -- is not optimized
SET VAR vlname = LastName, vfname = FirstName IN employee
WHERE empid=.vempid -- is not optimized
no optimized equivalent
SET VAR vtotal = (.vtotal + .vamount) -- is optimized only when both
vtotal and vamount are initialized outside the
WHILE loop.
Datatype variables at the beginning of a command file or before the
WHILE loop is executed. Don't datatype variables inside the WHILE
loop, they won't be optimized.
WHILE and IF commands are only optimized where they use a single
condition comparing values. If expressions are used the command is not
optimized. The comparison operator must be =, <>, >, >=, <, or <=. The
operators IS NULL, IS NOT NULL, LIKE, NOT LIKE, BETWEEN, IN and NOT IN
are not optimized. For example,
IF vtype IN ('A','B') THEN -- is not optimized
IF vtype = 'A' AND vtype = 'B' THEN -- is not optimized
IF vtype = 'A' THEN
IF vtype = 'B" THEN -- is optimized
IF (MOD(.vlength,2)) = 0 THEN -- is not optimized
SET VAR vodd = (MOD(.vlength,2))
IF vodd = 0 THEN -- is optimized
WHILE #PI > 0.0 THEN -- is optimized
WHILE #PI IS NOT NULL THEN -- is not optimized
The SWITCH command is optimized if the expression is legal when the
WHILE loop is read. Legal expression means that the variables used in
the expression need to be defined when the WHILE loop is first read.
FETCH is optimized if the cursor is already defined when the WHILE
loop is first read and all the variables and indicator variables that
are "fetched into" exist. Normally, the first FETCH is outside of the
WHILE loop, it initializes the variables for the WHILE loop
optimization. If you have nested cursors, you will need to define your
inner FETCH variables before the initial WHILE loop is executed. The
inner WHILE loop is optimized at the time the first WHILE loop is
read.
The variables used in any optimized command can change values as the
WHILE loop iterations are executed, but they cannot change data types
and cannot be cleared (with the CLEAR VAR command). The value can be
reset to NULL (SET VAR vname = NULL) but not cleared. The WHILE will
fail and exit to the R> with an error such as "-ERROR- Current SET
command is invalid in its optimized form." If your 3.x or 4.0
applications unexpectedly exit, it may be a problem with optimized
commands in a WHILE loop. Try TRACEing the application, TRACE does not
use the WHILE loop optimization or SET WHILEOPT OFF. That will turn
off WHILE loop optimization. If you determine that the problem is with
an optimized command, review the rules above, you may be clearing,
retyping or not data typing a variable.
Converting from R:BASE for DOS (2.11)
There are many changes between R:BASE for DOS and 4.5. Databases and
applications can be converted and will generally run as is. But you
will want to modify forms, reports and views to take advantage of new
features that were not available in 2.11. There are also many
programming enhancements that can be added to your applications to
reduce the lines of code and increase usability. One of the bigger
changes is in naming conventions. In 4.5, names (column, table, view
and variable) must start with a letter and can contain only the
characters #, _, $ and %. If you have names containing other
characters, such as '-' or '/', or names that being with numbers or
special characters, you will have manual conversion that needs to be
done to change those names throughout the database and applications.
Converting your database
First convert your database to the R:BASE 3.x, 4.0 format using
DBCONV, then convert the database to 4.5 format using NEWDB. This
two-step procedure is necessary to convert your computed columns,
rules, forms, reports and views from R:BASE for DOS format to the 4.5
format. If you don't follow the two-step process the database may not
be usable in 4.5. To convert computed columns, forms and reports
correctly you must first run DBCONV and then run NEWDB. You cannot
just run NEWDB on the database.
Converting your application
Convert Application Express applications by reading them into the
4.5 Application Express and then saving changes. That will not convert
any custom code included in the application. Run the 3.x, 4.0 APCONV
application conversion utility to convert custom application code.
Complete instructions on running APCONV are included in the Startup
Guide.
After converting your application, you should be able to just run it
in 4.5. There is no application conversion necessary from 3.x, 4.0 to
4.5 although a few applications may run differently -- see the section
above about converting applications from 3.x and 4.0.