PrimeBase Database Server 4.0
=============================

12 March 2002

Release Notes 4.0.38
====================

CHANGES, ADDITIONS AND BUG FIXES SINCE 4.0.34:

* Correct error messages are now generated when the server cannot start due
to an invalid server evironment.
* Windows: All applications now have a GUI console. The standard windows
console is no longer used.

Automation Client
-----------------

* The -d option which is used to specify a working directory now works
correctly.
* The command '#h' list a 20 line history for console input.
* The history buffer can also be accessed using the up and down arrow keys.

PrimeBase ODBC Driver
---------------------

* The ODBC driver now accepts stored procedure calls with missing () for
procedures requiring no paramaters.
* A bug when convertion ODBC time and datetime structures to primebase format
has been fixed.
* The ODBC driver now accepts "table qualifiers" (database names) in catalog
functions such as SQLTables(). The result sets from these functions now
contain the database name in the "Table_Qualifier" column. As a result of
this change "Crystal Reports" now works correctly with the PrimeBase ODBC
driver.
*A bug was fixed that resulted in Microsoft Access returning an error message
when attempting to create a table link.


20 December 2001

Release Notes 4.0.34
====================

CHANGES, ADDITIONS AND BUG FIXES SINCE 4.0.16:

* Bug fix: A bug that, under certain cercumstances, could corrupt an entry in
the SysUsers table has been fixed.
* Incompatibility: The implementation of the following functions have changed
in order to be more ANSI SQL conform: MIN/MAX/SUM. They now deliver NULLS
when the selection has no rows, or all the applied value are NULL.
* System parameter IOFlushing: Set this value to $false, to prevent the
server from flushing log and database files to disk. This improves the
performance at the cost of data security.
* System parameter LogThresholdLimit: Sets an upper limit for the log
threshold, in bytes. If LogThresholdLimit is set to $null then the log will
grow without limit if necessary (as long as a transaction is active). If set
to a value less than or equal to the LogThreshold then the original log
threshold limit is used which is 150% of LogThreshold. Otherwize the value is
a limit in bytes. Active transactions in the log or aborted when the log
exceeds the LogThresholdLimit (as before).
* Server Functions: NOW, DAY, LEFT, HOUR, YEAR, CAST, USER, WORDS, RIGHT,
MONTH, LOWER, UPPER, CONCAT, SUBSTR, LOCATE, MINUTE, SECOND, REPLACE;
TOUPPER, TOLOWER, SUBSTR, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP,
CURRENT_USER, SYSTEM_USER.
* (4029) Server stability has been increased in the case of an error on the
network. Addition checking has been added to prevent the server from crashing
when receiving bad query structures.
* Creating a domain based on the unicode data type now works correctly.
* Locking: The server uses a different locking strategy to avoid certain
potential deadlock situations when using SELECT FOR UPDATE and SELECTs within
a transaction (4029).
* A bug which caused the group privileges of database users not to be
recognized correctly after reorganizing the system tables has been fixed
(4029).
* An error in conjunction with the server functions, TOUPPER, TOLOWER,
INITCAP and CONCAT and the UNICODE data type has been fixed (4031).
* Sub-queries in the HAVING clause are now supported.
* Nested NOT EXISTS sub-queries are now supported.
* Sub-queries with HAVING clause are now supported.
* LIKE operator: Checking of the search pattern hase been added for LIKE
search with ESCAPE character.
* Bug fix: LIKE '%' was delivering no results in cases where a composite
index was used.
* Bug fixed for COUNT(*) on joined tables.
* Bug fix: NULL values caused wrong results in MINUS operation.
*  An improvment in the query planer avoids large sorts for join operations
where duplicates where removed from a sub-join. This has a time saving effect
for selection of large string columns.
*  Bug fixes for LIKE ESCAPE expressions. Some search pattern could cause a
crash.

PrimeBase Virtual Machine
-------------------------

* Modified $sendmail() so that the body of the email to be sent can be
greater than 64K (4019). 
* Fixed a bug in $sendmail() that could cause the function to fail with error
-12982 (4019).
* There is an undocumented built in command in the PrimeBase Virtual machine
called:
$pop3login(<user>, <passwd>, <address>) (4019)
The address is the IP address of the pop3 server, if not specified it will
default to the address of the SMTP server which is stored in the pbvm.env
environment file (4019).
$pop3login() should be called before calling $sendmail() for SMTP servers
that require the caller log in to the server before sending mail. It is not
necessary to login for each mail sent. How often the login must be done is
determined by the SMTP server service provider (4019).
* Fixed a bug in PBIGetError() that caused a crash if the function was called
with a NULL message buffer (4019).
* Fixed a bug that caused applications to hang in side of the virtual machine
when more than 1 plugin was loaded (4019).
* Calculateion warnings are returned by SQL statements. In this case $sqlcode
is set to a value > 0.
* Numeric literals that start with a dot are supported.
* $errorctl returns the current errorctl status.
* Mac OS X: A bug could cause PBVM to crash during concurrent access. This
error have been fixed.
* Fixed a bug in pbvm that resulted in the error "Item type cannot be input
by the API" when a non blob column was bound to a buffer > 1Meg. in size.
(Effects Tango.) (4024)
* TCP/IP: Connection timeout can now be set using the option \dc<timeout>.
The timeout must be a value in 1/100th of second (4025).
* PBISetCursorPosition
* 4030: Associative array value with UNICODE did not work correctly.
* Static initialization of non-basic type attributes values in no longer
allowed. For example:
class A {
  varchar[] my_values := new varchar[10];
}
'my_values' must now be initialized in the constuctor of A:
class A {
  varchar[] my_values;
  
  A() {
    my_values := new varchar[10]
  }
}
* OPEN DBMS: A user name set to '*@*' means get the user and password
information from the connection definition. In this case, the DBMS name must
correspond to a connection definition alias. For example:
OPEN my_dbms DBMS;
In this case, a connection definition with the alias 'my_dbms' must exist in
the connect.def file (4024).
* Socket object: The method writeline() will output the entire line as a
single TCP packet in order to be compatible with badly written FTP (and
TELNET) servers (4024).
* $insertrow() * A bug that cause the VM to crash when out of memory in this
function has been fixed (4024).
* 4030: $right() with UNICODE had a bug that could cause the VM to crash.
* 4030: $format or printf("%x" with values containing trailing space could
cause the VM to crash.
* A bug was fixed in VM optimizer which could crash when reporting an error
symbol not found (4024).
* An error exception now occurs when a procedure is called with too many
parameters (4025).
* $SelectDistinct: By default this value is $true. Set this value to $false
if the result of SELECT should not be distinct by default. NOTE:
$SelectDistinct = $false, is ANSI SQL conformant. The keyword ALL or DISTINCT
(e.g. SELECT ALL) can always be used in the SELECT statement to override the
default.
* $readfile(<file>, <mode>): This build in function reads an entire file and
returns a LONGBIN value. Note that the file may remain open as long the
LONGBIN value exists. By default, this function opens the file for READONLY,
and reads the data fork of the file only. The mode argument is optional. The
mode is a string which may contain one or more of the character sequences:
  r = read access
  w = write access
  c = create if the file does not exist
  /as = use "Apple Single" format for storing the data. When using the Apple
Single format, $readfile() stores data, resource and file attribute all in a
single LONGBIN value.
* $writefile(<file>, <data>): Write a value out as file. LONGCHAR and LONGBIN
values are allowed. If the data is in Apple Single format then the file
attributes will be set according to the stored information and resource data
will be written to the resource fork on Mac OS, and on all other platforms
the the resource fork will be written using the Apple Double format which
stores reource and file attributes in the .AppleDouble sub-directory. 
* $random(<seed>) generates a random integer value. The seed value is
optional (4024).
* $delete(<file>, <mode>): The optional <mode> parameter has been added to
$delete() to delete files in the Apple Double format. If mode is set to "ad",
then $delete() will also delete the resource information stored in the
.AppleDouble sub-directory.
* XMLObject: The VM XMLObject can be used to parse XML and HTML files. Three
options are now available and can be set using setOptions():
StripEmptyCDATA * (default true) Set to true if empty CDATA (text between
tags is only space).
ConsolidateCDATA * (default true) Consolidating CDATA removes explicit CDATA
tags.
DiscardComments * (default true) Set to true if you want the parser to
discard comments.
NOTE: All these options must be set to false if you want to preserve the
exact form of the original XML file after parsing. Setting the options to
false make s interpreting and undertanding the XML data easier, and is
therefore the default.
* Mac OS version: filemappings.xml * This file defines the mapping from
Windows file exension to Mac OS creator and type file attributes. The format
of the file is as follows:
<filemappings>
	<mappingentry>
		<name>Microsoft Word</name>
		<extension>.doc</extension>
		<mactype>W8BN</mactype>
		<maccreator>MSWD</maccreator>
	</mappingentry>
	...
</filemappings>
This file must be located in the SETUP directory.
* Long integer Literals: literal value that exceed 2147483647 or less than
-2147483648 are considered DECIMAL values.
* Semi-colon required: in PBT code a semi-colon (';') is required to
terminate the following statements: CREATE TABLE, UPDATE, DELETE, INSERT
(4025), SELECT (4031).
* Select mode: The SELECT mode (UPDATE, EXTRACT, READONLY, SCROLLING), may
now appear before and/or after the INTO <cursor> clause (4024).
* UPDATE/DELETE WHERE CURRENT OF: The table need no longer be specified. The
information is stored in the cursor (4026).
* Error with UNICODE and used in the LIKE operator have been fixed
(4022/4027).
* REQUIRED: Use WORDS() function to indicate a word search, or else LIKE will
do a sequence scan or filter. e.g. WORDS(c1) LIKE "a%" (4022).
* According to the ANSI SQL Standard overflow and underflow are now checked
when converting integer values. For example, and overflow error will result
is an INTEGER is converted to a SMALLINT, and the value is too large to be
represented by a SMALLINT. NOTE: This means tinyint only accepts values in
the range 0 to 255.
* JDBC Native APIs: Windows platform: A bug has been fixed which caused a
crash when sending and recieving large strings (> 16000 characters) through
the native API (4025).
* JDBC: SELECT FOR UPDATE now works correctly, locking the selected data so
that it can be updated later in the same transaction.
* Fixed a bug in the PBIConnect(): when a server name passed into 
PBIConnect() also appeared as an alias in the connect.defs file, a connection
was made using the connect.defs file settings and not those passed into the
PBIConnect() function (4030).

PrimeBase ODBC Driver
---------------------

* Fixed a bug that caused a crash when the ODBC driver tried to make a
runtime connection.
* Fixed a bug in SQLPrimaryKeys, it is no longer necessary to specify a table
name. When no table is specified primary keys for all tables including system
tables are returned.
* Fixed a bug in the Windows DSN setup dialog: the OK button failed to
activate  when all the required information was entered.

PrimeBase Automation Client
---------------------------

* PBAC no longer prints the error twice when an error occurs in the startup
code (4025).


11 September 2001

Release Notes 4.0.18 (pre-release)
==================================

* New Feature: It is now possible to disable disk flushing on the server.
This is done by setting the system variable 'IOFlushing' to $FALSE. The
default is $TRUE. Disabling disk flushing has the effect of significantly
speeding up inserts and updates at the risk of lost data and possibly
database corruption in the event that your computer crashes or a sudden loss
of power. Use this setting at your own risk!
* New built-in functions $readfile() and $writefile():
longbin $readfile(<path> [, <options> ])

$readfile() returns the contents of a specified file as a BLOB. <options> may
be any of the following:

r - Open for reading.
w - Open for writing.
c - Create if the file does not exist.

In addition to this, you may specify to use the "Apple Single" format, as
follows:

/as

For example: rw/as, open for reading and writing using the Apple Single
format. The Apple Single format is a file format which contains both the data
of the file, and file system information about the file (for example
modification date). On a Mac this format also includes the Mac resource fork.

$writefile(<path>, <value>)

$writefile() creates and writes BLOB data to a file.

* A file called "filemappings.xml" contains a mapping from extensions to Mac
file creator and type. The file must be located in the setup folder. This
mapping is used to set the Mac attribute correctly when creating files using
the functions $map, $create and $writefile. The format of the file is as
follows:

<filemappings>
	<mappingentry>
		<name>Microsoft Word</name>
		<extension>.doc</extension>
		<mactype>W8BN</mactype>
		<maccreator>MSWD</maccreator>
	</mappingentry>
	...
</filemappings>

* A bug in the XML parser that caused a stack overflow has been fixed.

PrimeBase ODBC Driver
---------------------

* Fixed a bug that caused an error to be reported whenever a value was
returned from an ODBC stored procedure.
* Modified the ODBC drivers behavior so that it works with the Microsoft SQL
data import tool.
* A bug that resulted in an extra character being added to data inserted via
MS Access has been fixed.


14 July 2001

Release Notes 4.0.16 (pre-release)
==================================

* All PrimeBase executables on Unix and Windows platforms now support a '-v'
command line option that will display the executable's version number.
 
NOTE: Static initialization of reference or array type instance variables is
no longer permitted! For example:

class Example {
	varchar[] values := new varchar[0];

	public void addValue(int i, varchar string)
	{
		values[i] := string;
	}
}

The initialization of the array must now be done in a contructor:

class Example {
	varchar[] values;

	public Example()
	{
		values := new varchar[0];
	}
	
	public void addValue(int i, varchar string)
	{
		values[i] := string;
	}
}

The PBE Objects framework has been updated to conform to this requirement.


Bugs fixed in this version:
---------------------------

* ppexec:
  A live transaction has to be stored in the communications handle.
* sort relation:
  Support the special case, that the relation to be sorted has no columns.
* Rule evaluation & MIN, MAX functions on UNICODE:
  Support the default collation sequence for columns of type unicode.
* Function MAX(x) & indices:
  When a SELECT MAX(x) was executed on an empty table and an index was used
an "Empty set error" was returned.
* function COUNT(*):
  Fixed uninitialized type variable.
* Bugs fixed for counting on views and grouped relations
* Union operator:
  - The UNION ALL is supported.
* Record locking:
  - Bug fixed that causes a deadlock when the commit was executed.
  - Bug fixed that causes a deadlock in special cases when a table was first
selected and later inserted.
* Optimizer:
  - Bug fixed that caused crash.
  - When an literal was converted a wrong function was called to free the
literal.
  - A duplicate join condition was causing a crash.
* Open table:
  - The function inspects and corrects an isam handle.
  - The corrected header is now written to disk (when this transaction is
commited).
* Carthesian join:
  - Was not returning all rows when an operand relation had aggregate an
function
like COUNT().
* LIKE Operator & Composite indices:
  - In some cases only one row was returned.
  - Bug fix: in an composite index the component is addessed now properly
with the item member.
* LIKE & ESCAPE:
  - Some the pattern containing ? * returned no result.
* Primary keys:
  - bug fixed for version 4013: when a composite index was created on the
columns of a primary key, and that columns had nulls allowed, this index was
in a bad state.
  - SELECT using the index was returning "inconstistent isam handle" error
* INSERT statement:
  - Bug fix: NULL default values where not properly set.
* SELECT statement:
  - Bugs fixed: FULL OUTER JOIN syntax and the *=* operator are supported
now.
  - Only when the OUTER keyword is found or the *= and =* operator is found,
an outer join is generated. Before an full outer join was generated WHERE the
JOIN keyword was used.
* "set_my_engine_activity() Failed, Bad engine id: 0" This warning message
has been removed. (It never was an error.)

PrimeBase Virtual Machine
-------------------------

* Fixed a bug that could result in an error being reported when inserting or
updating blob data while using treads.
* Fixed a bug that resulted in temporary blob files accumulating on the
client side and never being deleted.
* Fixed a bug that caused message queues to be purged unnecessarily.
* Reduced severity of message queue purged error to that of a warning.

PrimeBase ODBC Driver
---------------------

* Fixed a memory leak. Memory was lost with each ODBC statement handle
created. This could also result in temporary blob files accumulating on the
client side.
* Fixed a bug that resulted in extra byes being added to non null terminated
strings when inserted or updated via ODBC. This effected Microsoft Access in
particular.


23 May 2001

Release Notes 4.0 #4013
=======================

* A Primary Key is no longer required when creating a table. However, the
creation of a primary key is still recommended.

* The DESCRIBE OPEN DATABASES command now includes the actual name of the
database.

* A "carbonized" runtime version of the PrimeBase Database Server for the Mac
is available in this release.

Bug Fixes to the PrimeBase Database Server
------------------------------------------

* A bug that caused to server to crash when a large number of BLOBS (> 30000)
was deleted or updated at once has been fixed.

* consistence checks for fulltext index buffer internal checks have been
added to prevent crashes.

* bug fix in delete index function.

* bug fixed  with blobs data type
  blob where not accessable when a database was unmounted and then later
mounted.
   
* reorg table with fulltext indices has been tuned for size of the
reorganized tables and indices. 
    
* bug fixed with the max() function. In some cases when an index was
accessed, no result was returned.

* bug fixed with primary key
  The rule, that primary key columns may not be null, was not properly
enforced.

* bug fixed for rule evaluation. In some cases when a large number of tables
has been created the evaluation of rules could fail or cause a invalid memory
access.

* bug fixed for now() functions as an operand for a subtraction. 

* The $now() and now() functions returned a incorrect current time after
awaking a Mac from a sleep.

* Bugs with update, delete using a fulltext search in the where clause.
  Row locks where not set correctly.

* Bug with IS [NOT] NULL in the where clause using a fulltext index.
  Rows there not found.

* Bug in CREATE INDEX
  Unsupported datatypes where accepted by the statement. Columns of type
BINFILE are no longer allowed.
  Columns of type LONGCHAR are no longer allowed in composite indices.

* Server Function RTRIM failed to remove trailing spaces from a column of
type CHAR.

* Bug in index access for a join operation. When a many to many join was
accessing a CHAR column via an index, and the column size was < 32 wrong
results where returned.

* modified TCP communications so that if the 'select()' function returns an
error the connections will be searched to find the connection(s) causing the
error and they will be terminated. Previously all connections where
terminated if 'select()' returned an error.

PrimeBase Virtual Machine
-------------------------

* Threading related. Fixed several bugs that resulted in incorrect behavior
when using the PBVM with threads. System level mutexes are now used for
locking.

* Added a 2 minute timeout for connection attempts.

PrimeBase Automation Client
---------------------------

* Fixed bug to allow quoted parameter options in pbac.cfg.

* Fixed bug that caused crash when CTRL-C entered when adding/editing a
connection entry.

* Modified PBAC so that it will generate a unique name automatically when
started in the background.

PrimeBase ODBC Driver
---------------------

* Removed the automatic Primary key generation for tables created without a
primary key specified.

* Fixed a bug that cause a syntax error to be reported for create table
statements where columns were explicitly define to allow NULLs.

* Fixed a bug that caused an error when inserting blob data larger than 32 K.


12 March 2001

Release Notes 4.0 Final
=======================

A summary of changes to the database server is given below, including
information on NUMEROUS NEW FEATURES!

UNIX: The "read me" file has been updated. PLEASE READ IT!

The JDBC 2.0 Driver is now available for PrimeBase. The driver supports the
"meta data" functions, and includes full support for Unicode.


UPGRADING TO 4.0
================

We recommend that you use the installation as it is provided in this folder.
If you want to use the 4.0 server copy your database (the 'db' folder under
UNIX, the 'DATABASE' folder under Windows or the 'Databases' folder on the
Macintosh) over from the 3.5 server to the database folder in this directory
and MOUNT it.

After you have mounted your database you may do a REORG to get maximum
performance from your new server.

Mounting and reorganising the database can be done using the Admin Server
which can be accessed using a JavaScript capable browser.


DOWNGRADING TO 3.5
==================

Because of the index structure changes, once a database table has been
REORG'ed by the 4.0 server it can no longer be accessed by PrimeBase Database
Server 3.5.42 or earlier. Mount and reorganise the database using the
PrimeBase Database Server 3.5.43 (or later) in order to "downgrade" a 4.0
database.


PrimeBase Virtual Machine
-------------------------

* DAL compatiblity has been improved. The keywords 'error', 'this' and 'new'
can be used as variable names in DAL programs. The keywords are still
reserved in class definitions and other PBT code.

* The global system variable $dbmscharset can be set to specify the character
set of the server. This can be used in place of the \xc connection option
(see below) if the server does not specify it's own character set. For
example, a PrimeBase 3.5 Database Server.

* A bug which caused the error "file '' not found" when loading a NULL blob
value from the PrimeBase Open Server has been fixed.


PrimeBase Admin Server
----------------------

* The Admin Server now longer returns error pages when running in the
PrimeBase Application server environment.

* All new database system parameters can now be set using the Admin Server.
This includes the system character set.

NOTE: By default the system character set is ROMAN.TXT. This character set is
used because it corresponds to the system collating sequences.

If you install a 4.0 server in an environment in which databases are already
mounted, then the system character set is set to $null. In this case you
should set the character set according to the character set actually used in
the mounted databases. Note that if you use a character set other than
ROMAN.TXT, you should modify the sytem collating sequences in all databases
and reorganize the databases.

* When creating a database schema, the Admin Server now requires an index to
be created on primary and candidate key columns. This is because the database
server requires the index to be able to guarentee that values inserted into
key columns are unique.


Bug Fixes to PBDS
-----------------

* A bug that caused the server to crash when using the NOW() server function
has been fixed.

* A bug which caused composite indexes entries to be incorrectly build has
been fixed.

* The 'smm' executable (shared memory manager) which is started by the server
has been modified to ignore the SIGHUP signal generated when a telnet session
ends.

* The server and automation client (pbac) consoles now accept non us-ascii
charaters.

* The name of the 'pbds_start' script has been changed to 'start_data_server'
and 2 new scripts have been added 'stop_data_server' and
'data_server_console'.

* Bug fixed in server function TO_CHAR() When a wrong format string was
passed to the function,  there was a memory leaking.

* Bug fixed for NULL test on database column with a NOT NULL rule. Now this
test is only removed when it is found is a search condition and no outer join
can extend that column.

* Bug fixed with decimal addition and multiplication: Addition and
multiplication with money and decimal types are no longer commutative,
because the size of the result depends on the execution order.

* Bug fixed for LIKE searches on composite indices.

* Bug fixed in CREATE FULLTEXT INDEX a null pointer was accessed in the
function, that added index entries for existing rows.

* Bug fixed for SUM and AVG server functions the degree of duplication column
was not properly handled, when an outer join inserted null marks into that
column.

* Speed up for cache overflow situations when all cache pages are dirty Now
the server flush dirty pages from cache whenever more when 7/8 is dirty. This
should avoid the long delay that was caused by flushing the cache, when cache
is full and has to be recycled.

* Bug fixed for server string functions with varchar arguments. Blanks as
arguments where not handled consistently. "trailing_zero_to_space" is now
called for the string arguments.

* Bug fixed in SORT RELATION when duplicates removed and  a many to many join
is performed an offset into a sort buffer was calculated wrong. When
duplicates are removed a page can shrink in size. Now calculate Offset using
the actual page size.

* Speed up for copy file function increased COPY_BUFFER_SIZE and
RESTORE_BUFFER_SIZE from 32K to 64K has doubled the speed for the PPC
platform.

* REORG TABLE: In some cases the datafile size was much larger after
reorganizing. Now the average row size is used to calculate a good block
size. Large block size will save space in full text index files, but a small
block size saves space in the data file.



Summary of Changes and New Features to PrimeBase 4.0
====================================================

NEW SET OPERATORS
-----------------

Primebase 4.0 supports set the operators (UNION, UNION ALL, INTERSECT and
MINUS) which combine sets of rows returned by SELECT statements.

Syntax:

Query Specification

Use a query specification to select a set of data from the database.

       <query_spec> ::= <table_expr> |
                          <query_spec> <set_operator> <table_expr> 


       <table_expr> ::= <select_clause> 
                        <from_clause>
                        [ <where_clause> ]
                        [ <group_by_clause> ]
                        [ <having_clause> ]

       <set_operator> ::= [UNION [ ALL ] | INTERSECT | MINUS]

A <table_expr> consists of a FROM clause and an optional WHERE, GROUP BY and
HAVING clauses.

<set_operator> are used to merge and intersect multiple <table_expr>. The
<table_expr> must have the same cardinality (number of columns) and the
datatypes of the columns must be the same for a columns in the same position
of the <select_list>. use <datatype> conversions in the <select_item> to
build matching rowsets. All <set_operator> have the same precedence.

UNION is used to merge the rows of multible <table_expr> in one rowset. A
rowset that contains rows that are included in the first <table_expr> OR the
second <table_expr> INTERSECT is used to build the intersection of multible
<table_expr> in one rowset. A rowset that contains rows that are included in
the first <table_expr> AND the second <table_expr>.

MINUS is used to build a relational difference. A rowset that contains rows
form the first <table_expr> and that are not included in the second
<table_expr>.

When you apply a <all_set_function> like SUM(ALL column) or AVG(ALL column)
to the result set of an UNION, INTERSECT and MINUS <set_operator> the totals
are build from the first operand. In case of the UNION ALL, the totals are
build from both operands.

OUTER JOIN
-----------
  
New outer join syntax and table expressions in the <from_clause> of a SELECT
statement.

       <from_clause> ::= FROM <table_spec> { ',' <table_spec> }

       <table_spec>  ::= <table_ref> [ [ AS ] <table_alias> ] |
                         <view_ref>  [ AS ] <table_alias> |
                         <subquery> [ AS ] <table_alias> |
                         <outer_join> [ AS ] <table_alias> |

       <outer_join>  ::= <table_ref> 
                          [ LEFT | RIGHT ] OUTER JOIN 
                          <table_ref>
                            ON <join_condition> 
                            [ AND <search_condition> ]

       <join_condition> ::= <column_ref> = <column_ref>

       <table_ref>   ::= [ <database_alias> '!' ]
                           [ <creator_name> '.' ]
                           <table_name> |
                           ':' <object_name>

       <view_ref>    ::= [ '(' ] 
                           [ <database_alias> '!' ]
                           [ <creator_name> '.' ]
                           <view_name> |
                           ':' <object_name> 
                           [ ')' ]

The alias name applies to this query specification only. In no alias is
specified, the table name will be used. A <table_ref> (table reference) ,may
include the explicit specification of the database and the table creator name
(Also known as the table owner name).

<database_alias> defines the name of the database. When mulible databases
have been opend in the current program instance (session), the
<database_alias> specifies with database to use. If no database is specified
the currently in-use database is assumed. The "USE DATABASE
<database_alias>;" command can be used to set the database that should be
used when no <database_alias> is set in a <table_ref> or <view_ref>

<creator_name> defines the object owner. If the creator name is not
specified, then the server first checks for a table with the user's creator
name, and then for tables with creator names 'Common' and 'System'.

<object_name> is a variable of type OBJNAME containing a valid object
reference may be used in place of the explicit object reference. In addition,
each "name" type value may specified using an OBJNAME variable (proceeded by
a colon, ':').

When a <view_ref> (view reference) is enclosed in parenthesis, the view is
optimized in isolation. Without parenthesis, the view is optimized by pushing
<search_condition> that are applied to simple columns of the view into the
<where_clause> of the view. In cases where is optimization is not improving
the access speed, parenthesis will disable the optimization process.

In an LEFT OUTER equal join, NULL marks are inserted in place of columns from
the RIGHT table where no matching value from the LEFT table was found.

In an RIGHT OUTER equal join, NULL marks are inserted in place of columns
from the LEFT table where no matching value from the RIGHT table was found.
       
Examples:

  SELECT a.*, b.* 
  FROM customers a LEFT OUTER JOIN popups b
  ON a.country = b.code AND a.name LIKE "b%";

is the same as: 
     
  SELECT a.*, b.* 
  FROM customers a, popups b
  WHERE a.country *= b.code AND a.name LIKE "b%";

and:
     
  SELECT a.*, b.* 
  FROM customers a RIGHT OUTER JOIN popups b
  ON a.country = b.code AND a.name LIKE "b%";

is the same as: 
     
  SELECT a.*, b.* 
  FROM customers a, popups b
  WHERE a.country =* b.code AND a.name LIKE "b%";

In an LEFT OUTER JOIN, NULL marks inserted in place of columns from the RIGHT
table where no matching value from the LEFT table was found.

In an RIGHT OUTER JOIN, NULL marks are inserted in place of columns from the
LEFT table where no matching value from the RIGHT table was found.
     
When you place a '(' <view_ref> ')' in the <from_clause> the view is
optimized in isolation.  Any <search_condition> that use to columns of the
view will be applied after the view has been generated. 
     
When you place a <view_ref> without the '(' ')' in the <from_clause> then
view will be optimized to perform any <search_condition> as early as possible
to make use of indices.
     
NULL EXTENDED INDICATOR
-----------------------

The asterisk character '*' is used to indicate that the boolan value TRUE is
returned by the operation when one of the operand expressions is null.

When you have columns with NULL values and you apply a search condition to
that column, rows that have a NULL values in the searched column are removed
from the result. When you want to keep those rows in the result you can set
the '*' indicator in after the operator. This indicator is especially useful
for search condition on results of outer join operations. The outer join
operation can insert NULL values in columns even when NULL values are not
permitted in the base tables. Normal search conditions would remove those
rows from the result set.

The id column is required in the example, because the selection delivers no
rows that have nothing but NULL values.

Example:

      SELECT .id, .filename FROM fulltext WHERE .filename LIKE * '*.htm';
      SELECT .id, .filename FROM fulltext WHERE .filename *= 'index.htm';
      SELECT .id, .filename FROM fulltext WHERE .filename BETWEEN * 'E' AND
'G';


EXTENSION TO THE LIKE OPERATOR
------------------------------

The LIKE test for columns of type CHAR, VARCHAR, LONGCHAR and UNICODE, uses
the collation sequence keys to match the pattern.

The LIKE test can have an optional ESCAPE character. This option is used to
enable searching of the characters that a used of wildcards
('%','_','*','?'). When a character is preceded with the ESCAPE character, it
will be used literally.

For common search operations in larger tables, there should exist an index on
the search column and at least the first 2 or 3 characters should be provided
as a search pattern.

When the LIKE search is performed on the words of a full text indexed column
the matching is within the words boundaries

Example:
Find products where the name the name starts with 'A10*8':

  SELECT * FROM products WHERE .name LIKE 'A10\*8%' ESCAPE '\'; printall;

Joins and the SUM() function
----------------------------

When you apply a function like SUM(ALL column) or AVG(ALL column) to the
result set of a join of two tables A and B, the result may not be what you
expect because the join operation can build duplicates of the column values
that you are computing.

When you need totals from two or more tables, you have to build the totals
before the tables are joined.
     
Build separate views or tables expressions that summarizes the data and group
it by the foreign key columns.
     
When join the views or table expressions to combine the results in the select
list.

PRIMARY KEY
-----------

The definition of the primary Keys of a table is no longer required before
you can insert data in a table. In Primebase version before 4.0 you received
and "primary key is missing" error when you tried to insert rows in a table
where no primary key was created.
     
This forced import programs to define artificial keys in cases where
information about the key is not available.

The primary key definition is still required before you can create an index
for a table. Because primary and candidate key definitions determinate the
uniqueness of column combinations in you index definition. When the column
combination in a create index statement is unique, the server can detect and
refuse duplicates in that columns.
     
To preserve the integrity of you database schema, create primary and
candidate key and indices for them as soon as possible. 

UNICODE SUPPORT
---------------

Variables and database columns can now be declared as UNICODE. In a database
table, a maximum size must be specified. The maximum size of a UNICODE string
is 30000 characters.

Unicode character mapping and collation support:

* The system parameter System.CharSet in the Master database can now be set
to reflect the character set used by databases on the server.

Characters sets must be specified using the name of the Unicode conversion
file found at www.unicode.org. For example: ROMAN.TXT, 8859-1.TXT or
CP1252.TXT.

By default this value is set to $null which means the character set is
unspecified. Only if the character set is specified on both the client and
the server will character conversion be done on data inserted and retrieved
from the database.

To set the character set on the client, see $charset below.

The final release of PBDS will allow you to specify the character set on a
per database basis. In this way, databases moved from one platform to another
will still continue to correctly interpret the character data in the
database.

* The connection option \cp can be used to set the server "code page"
(character set), when connecting to Primebase version 3.5 or earlier.

The system parameter System.UnicodeSequence in the Master database can be set
to define the default Unicode collation sequence files and other attributes.

  SET VARIABLE Master!System.UnicodeSequence = "
       unidata=UnicodeData.txt;
       basekeys=basekeys.txt;
       compositekeys=compkeys.txt;
       compositionexclusions=CompositionExclusions.txt;
       language=LATIN";

The server in any comparison and sort uses this collation operations on
Unicode strings where no collation has been explicitly defined via a domain
with a sequence. 

When you create your own sequence definition, you can use it to define the
ordering of a domain.
     
In the sequence definition you only list attributes where you want to change
the default setting. When you use the domain in table definitions, the text
in the column will be sorted and compared by the collation definition.
     
Syntax:

  You will define a string of attributes.
  The notation has to follow this rules:
  1. No spaces.
  2. Case insensitive.
  3. New line or ';' is the attribute separator
  4. Filename up to 32 characters.
  5. The default value for attributes 
               variables, backwards, ignore and normalization is 'off'.
          
Attributes of an unicode collation sequence:
 
  unidata=filename
  basekeys=filename
  compositekeys=filename
  collationtable=filename
  compositionexclusions=filename
       files with character tables used by the collation
       
  variables= [off|blanked |on|non-ignore |shifted]
       handling of non alphabet characters
       
  backwards= [on | off]
      accent is compared backwards (french sorting)
       
  ignore= [off | case | case&accent]
       ignore case and/or accent of the characters
       
  normalization= [on | off]
       normalize different writing systems

Example:
     
  CREATE UNICODE SEQUENCE my_uni_seq =
"ignore=case&accent;variables=blanked";     
     
  CREATE DOMAIN d_uni20 unicode[20] ORDER AS SEQUENCE my_uni_seq;

  CREATE TABLE uni_test (name d_uni20 not null);

The client session has system variable $charset that defines a map file for
the conversion from unicode to 8 bit character and back. This file will map a
vendor specific 8 bit character set to unicode. print '$charset :', $charset;

The client session has system variable $charset that defines the meaning of
comparison operators with unicode operands in a PrimeBaseTalk (PBT) program. 
     
Example:
 
  print '$unicodemap :', $unicodemap;
  varchar savemap = $unicodemap;
  
  $unicodemap = "ignore=case&accent;";
  print unicode "a-B" == unicode "a-b";
  
  $unicodemap = "ignore=case;normalization=on;variables=blanked";
  print unicode "a-B" > unicode "a;b";
  
  $unicodemap = savemap;

TABLES
------

The maximum row size has been increased from 32K to 60K. The maximum logical
size of a table row is now 61400 bytes. As before, this does not include the
size of BLOB values in the row.

Larger SELECT and sort results In SELECT statements, the maximum size of rows
that can be sorted with an ORDER BY clause is now 61400 bytes.

DOMAINS of type LONGCHAR can have a COLLATION SEQUENCE definition. The
collation sequence will be used for indexing the words with case insensitive
or ignore accent matching and ordering. You can use the LIKE and LOCATE
operator to search in LONGCHAR columns.

Duplicate row elimination with collation sequences. When duplicate rows are
removed from a selection, the collation sequences of the columns of type
CHAR, VARCHAR, UNICODE, LONGCHAR are used when comparing rows.

INDICES
-------

The maximum count of indices that you can create has been raised to ca. 190.
The actual limit depends on the number of index descriptors at 446.  In
Primebase 3.5 the limit is 50 descriptors.
     
The maximum size of the columns in an composite index has been raised to 512
bytes.
     
New index structures store columns with null marks and columns with duplicate
values in less space.

Varchar columns data can now be projected from indices. When you select a set
of columns what are all found in an index, that index will be used to
retrieve the result set, avoiding any access to the data rows.
     
MIN and MAX operations are mapped to index accesses where ever possible.
     
Primebase 4.0 can operate databases in the format of Primebase 3.5.
     
But to enable the higher limits and to enjoy the new performance you have
reorganize your databases. 

General Index Definition Rules:

- The PRIMARY KEY must be created before an index can be created.
- You have to create an index for the PRIMARY KEY columns to enforce the
uniqueness of the primary key. The same rules applies to the CANDIDATE KEY
definitions. 
- When you change a PRIMARY KEY or CANDIDATE KEY definition you have to
reorganize the table to bring the indices up to date.
- For text columns with a maximum size less that 244 the whole text is stored
in the index item and when two column values are compared, the whole text is
compared from the first to the last character.
- When a text column is used as a component in a composite index, the total
of all field sizes should not exceed the limit of 560 bytes.
- When there exists a domain definition for the index column with a collation
sequence, the words are sorted and compared with the rules of the collation
sequence definition. When, for example, a case insensitive collation is used,
words that have the same base character and only have a difference in the
character case, are treated as the equal.
- When there exists no primary or candidate key for the columns of an index,
duplicate index entries are permitted.
- When the total of size of the index columns is less then 32 bytes,
duplicate entries are stored in the index nodes and the entries distinguished
by there logical data address field LDA.
- When the total of size of the index columns is >= 32 bytes, duplicate
entries are stored in separate lists of LDA's. In the case that a duplicate
item is repeated 1000s times, updating can slow down when this lists have to
be searched. When you have this case, you should add a column to that index
that is in combination with the first less duplicate or more unique.
- When you are creating full text indices for larger amounts of text, set the
server cache size to at least 15 MB to get the best performance. Give the
server application 30 MB of Heap space to avoid memory shortage.

CREATE FULLTEXT INDEX
---------------------

This release has an implementation of a full text index for any text column
(CHAR, VARCHAR, LONGCHAR, UNICODE).

An full text index is created with the FULLTEXT keyword in the CREATE INDEX
statement. An fulltext index is also created, when the column of the index is
of type LONGCHAR or when the maximum size of the column is larger then 244
characters. 

It is fully integrated into the relational database engine, that means:

- When a row is inserted, updated or deleted, the indices are automatically
synchronized. 
- The process manager controls concurrent updates with automatic locking and
queuing and deadlock detection.
- A transaction manager ensures the data integrity with a before and after
image journalizing system. 
- A cache manager can buffer many update operations to the same index pages,
before that pages have to be written to disk and so speedup the indexing
process to an attractive level. 
- The searching is exact. It can be made more fuzzy, by using case
insensitive and/or ignore accent collations sequence key tables, and by using
the LIKE and '== ANY' operations.
	
New search operations, that work with sets of words, can be used to search
for a subset: ' WORDS(.column) = ANY "string" ', or to search for the
complete set of search words: ' WORDS(.column) = ALL "string" '.

Full text index definition rules:
  
- Before you create any index, you have to define the uniqueness rules for
table with the CREATE PRIMARY KEY and CREATE CANDIDATE KEY commands.
- Full text columns can not be unique. No PRIMARY or CANDIDATE KEY should  be
defined for that column. The full text index can have only one component
column.
- The system parameter System.CharSet in the Master database has to be
assigned with the filename of the platform specific unicode character mapping
table. 

Example:

  OPEN DATABASE master;
  set variable master!system.CacheSize = 1024*1024*15;
  set variable master!system.CharSet = 'ROMAN.TXT';
   
  CLOSE DATABASE master;

  CREATE DOMAIN d_content LONGCHAR, ARITHMETIC APPLICABLE, ORDER AS COLLATING
SEQUENCE CaseInsSeq;
  CREATE DEFAULT def_d_content ON DOMAIN d_content AS '';
  CREATE TABLE fulltext (
    id      INTEGER     NOT NULL,
    name    VARCHAR(32) NOT NULL,
    content d_content   NULL);
  CREATE PRIMARY KEY fulltext_pk ON fulltext.id;
  CREATE INDEX ind_fulltext_pk ON fulltext (id);
  CREATE INDEX ind_fulltextcontent ON fulltext (content);

Full text index parameter files:
  
When an fulltext Index is created on a single character column, each
individual word of the text in that column will be indexed.
		
To speed up the index update operations and to keep the index file size
smaller, a list of stop words is used to avoid the indexing of very frequent
words. This list of stop words is used for all fulltext indices of the
server. You have to decide in advance, which words should be in the list.

The installation comes with a text file 'stopwords.txt' that is stored in the
':Setup:UNICODE:' folder. It come with very frequent word for English, German
and HTML language.
		
The maximum count of stop words used, is limited to 2000.

To build your own list of very frequent words, you first build an index on
large amount of sample text, and then execute a select statement to select
the most frequent words:
		
      SELECT words(A.content) content, count(*) count
      FROM fulltext A 
      GROUP BY A.content
      HAVING  count(*) > 100) AS A
      ORDER BY 2 desc;

When you change the stop word list and full text indices already exist, that
files become invalid, because words that are in the list should not be in the
index files.

When you change the collation sequence definition or the UNICODE key weight
lists the indices become invalid, because the order if the index items is not
the same as the changed sequence definition.
		
When an index file becomes invalid you have to execute the reorg table
command for the tables on which the indices are defined. (There exists a DAL
script 'reorgall.dal' that has a procedure to reorganize a complete
database.)
	
When a text column is indexed, that contains HTML text, the HTML entries of
the form "&xxx;" are converted to the character set of the server platform.
This method ensures that word boundaries can be properly detected and search
conditions can use the characters directly ignoring of the format of the
text.
 
Structure of the index:

Each word of the text column is stored as a {word, LDA} tuple in an sorted
index node. The LDA is a logical data address that references to the data
record. A Word is a character sequence that starts with an alphabet character
and that is followed by an alphabet or digit character.  When a Word starts
with a digit, it has to be continued by a punctuation or digit character.
Words that are only one character or that are longer than 244 character are
ignored.

When a word is repeated in the same document only one LDA is stored. The
index entries are stored in sorted and balanced tree nodes. The entries are
stored with variable size depending on the length of the word.

A single word in the index can have one or many references to different
documents.
		
When the first {word, LDA} tuple is stored in the index, then the index entry
directly points to the data record. When a row is deleted all references to
the document are deleted from the index. When the second tuple for an
existing word is added to the index, then the index entry points to an
compacted array of LDA. In this case the index entry represents a cluster of
LDA.
		
The cluster is addressed by the word and an index number that is calculated
from the LDA. Inside a cluster, all LDA's of a calculated range are stored in
an array. These arrays are compacted before they are stored on disk.

FULLTEXT SEARCH
---------------

Usage in the query language:

Example Table:
      
        CREATE DOMAIN d_content VARCHAR(60000), ARITHMETIC APPLICABLE, ORDER
AS COLLATING SEQUENCE CaseInsSeq;
        CREATE TABLE fulltext (
        	id        int        not NULL,
        	blockno		int         not NULL,
        	modified	datetime	not NULL,
        	filename	VARCHAR(32)	not NULL,
        	pathname	VARCHAR(512) NULL,
        	content		d_content NULL
        );
        CREATE PRIMARY KEY fulltext_pk ON fulltext.id;
        
        CREATE COUNTER VARIABLE INTEGER counter_fulltext;
        SET VARIABLE counter_fulltext = 0;
        CREATE DEFAULT def_fulltextid ON COLUMN fulltext.id AS SERIAL
counter_fulltext;
        CREATE INDEX ind_fulltext_pk ON fulltext (id);
        CREATE INDEX ind_fulltextfile ON fulltext (filename);
        CREATE INDEX ind_fulltextcontent ON fulltext (content);

PB 4.0 supplies a WORDS(column) function that can be used on text columns to
access the list of words in that column. The function WORDS(column) is used
to access the word index (lexicon) of the argument column.
		
The relational operators '== ANY' and '== ALL' followed by a literal string
or a program variable are used to select the rows the contain ALL or ANY the
words of the string. Characters like that separate the words are ignored in
the search operations. You can use the LOCATE() function to test for
substring matching.
		
Example:
List full rows where the content column contains ANY one of the words of "mac
macintosh powerpc powerbook apple"

  SELECT * FROM fulltext WHERE WORDS(.content) == ANY "mac macintosh powerpc
powerbook apple";
		
Example:
List full rows where the content column contains ALL of the words of "black
designer jeans" 

  SELECT * FROM fulltext WHERE WORDS(.content) == ALL "black designer jeans";
		
For simplicity the expression ' WORDS(.description) == ALL "text" ', can be
written as ' .description = "text" ' , but only when the column is of type
CHAR or VARCHAR and an fulltext index has been created on that column.
		
The WORDS(column) function can be used in the SELECT column list, to access
the words that apply to a search condition. When you group the selected
column you can also select the count(*) of rows where each word occurs.
		
Example:
List the words and the count of rows where the content column contains words
that starts with "Blue". ordered by count of matches, with the most frequent
first.
		
  SELECT WORDS(A.content), count(*) 
			from fulltext A 
			where WORDS(.content) like "Blue%"
			group by A.content 
			order by 2 desc; 
		
Example:
Get the count of words that start with a "B"

  SELECT count(distinct .content) FROM fulltext WHERE WORDS(.content) LIKE
'B%'; printall; 

Example:
List the id, and the first 40 characters from the content column where words
that start with "Zoo" are found.

  SELECT .id, VARCHAR[40] .content FROM fulltext WHERE WORDS(.content) LIKE
'Zoo%'; printall;

The query planer can only build an index access plan for 'AND' concatenated
conditions in the WHERE clause. When you have a OR condition, you have
convert the statement to one that uses a UNION operator to combined the
results of two selections.

Example:
List filenames where the content has any one of the words ('powerpc', 'imac')
or the words start with 'mac' and the word 'donald' is not contain in the
content column.
		
  SELECT .filename from fulltext where WORDS(.content) IN ('powerpc', 'imac')

  UNION 
  SELECT .filename from fulltext where WORDS(.content) LIKE 'mac%'
  MINUS
  SELECT .filename from fulltext where WORDS(.content) = 'donald, burger'
        
Example:
You can build a query that matches a longer sequence of search text. First
the text searched with the full text index using the = ALL operator. Then the
locate function is used on the matching set of the index search of filter the
rows that contains the text in the given word order and includes the same
text as a sub string. The statement extracts a text probe from the location
of the match, to be displayed in the result list.
		
  integer contextlen = 80;
  VARCHAR keyword = 'digital cameras for beginners';
  SELECT .id, .blockno, .pathname, .filename, 
  	locate(.content, :keyword) offset, 
  	substr(.content, locate(.content, :keyword) - :contextlen, :contextlen *
2) probe
  FROM fulltext 
  WHERE WORDS(.content) == :keyword
  AND locate(.content, :keyword) > 0
  INTO match_cur FOR EXTRACT;

This query has a common sub expression, three calls with locate(.content,
:keyword). It can be simplified by calculating the expression in a sub query,
and then use the offset column in the outer query.
		
  SELECT B.id id, 
  	B.blockno blockno, 
  	B.pathname pathname, 
  	B.filename filename, 
  	B.offset offset, 
  	substr(B.content, B.offset - :contextlen, :contextlen * 2) probe
  FROM (
  	SELECT A.id, A.blockno, A.pathname, A.filename, locate(A.content,
:keyword) offset, A.content
  	FROM fulltext A
  	WHERE WORDS(.content) == :keyword
  ) B
  WHERE B.offset > 0
  INTO match_cur FOR EXTRACT;

LOG FLUSH DELAY:
----------------

In the Master database exists a new system variable named LogFlushDelay. This
variable defines a time delay in Seconds/100. For that time the flushing of
the transaction log buffer is delayed after the completion of a transaction.
This improves the performance of INSERT, UPDATE and DELETE statements that
are executed as individual transaction.
     
The price you have to pay for this, is the fact that you may loose some data
that was processed in that split second when the system should ever fail. The
server can always recover from the crash and will roll back transactions that
where not completed at the time of the crash or that where not flushed to the
log file. 
     
         SET VARIABLE Master!System.LogFlushDelay = 10;

BACKWARD COMPATIBLE
-------------------

Databases that where created or reorganized with PB4.0 can be opened and
reorganized with version #3540 of PB3.5. You have to remove full text
indices, tables with unicode columns, or views that use the new mathematical
and string functions from the databases that you need to mount in a PB3.5
environment.

VIEWS
-----

- Larger Views

The size of the view binary buffer has been increased from 16K to 32K.
Existing databases will be updated automatically

- Bug fixed for VIEWS with subqueries

The column constant expressions where not properly copied to the
view binary buffer.

- Bug fixed for cathesian joins and NOT IN (subquery)

When a cathesian joins or NOT IN (subquery) was nested in the FROM
clause or within a view a Column link Error was returned.

- Bug fixed for VIEWS containing floating point literals

The SWAP_REAL_8(x) functions unconditionally swapped the literals when the
dictionary buffer was reallocated.



14 February 2001

Release Notes Pre-final
=======================

* The PrimeBase environment has been changed so that files that use to be
created in the /tmp directory are now placed in the PRIMEBASEHOME directory.
The PRIMEBASEHOME directory is by default /usr/local/primebase. You can
change this by setting the Unix environment variable PRIMEBASEHOME.

IMPORTANT: Because of the above mentioned change you must create the
PRIMEBASEHOME directory and give read, write and execute privileges to all
users on the machine who will be running PrimeBase software and using the
PRIMEBASEHOME directory.

* PrimeBase packages now come with installers to assist in setting up the
PRIMEBASEHOME directory and optionally installing the software into a
centralized location inside of the PRIMEBASEHOME directory.

* The document 'PrimeBase System Overview' has been provided with this
release and is recommended as the starting point for learning about
PrimeBase.

* The PrimeBase Admin Server user interface has been updated.

* The system parameter System.CharSet in the Master database can now be set
to reflect the character set used by databases on the server.

Characters sets must be specified using the name of the UNICODE conversion
file found at www.UNICODE.org. For example: ROMAN.TXT, 8859-1.TXT or
CP1252.TXT.

By default this value is set to $NULL which means the character set is
unspecified. Only if the character set is specified on both the client and
the server will character conversion be done on data inserted and retrieved
from the database.

To set the character set on the client, see $charset below.

The final release of PBDS will allow you to specify the character set on a
per database basis. In this way, databases moved from one platform to another
will still continue to correctly interpret the character data in the
database.

* The connection option \cp can be used to set the server "code page"
(character set), when connecting to PrimeBase version 3.5 or earlier.

* A bug when writing NULL blobs to the Open Server has been fixed.

* The syntax GROUP BY .<column name> now works correctly.

* All new string and math functions are implemented and documented. All
string functions are implemented and tested for VARCHAR arguments. SUBSTR,
LOCATE and LOCATE_WORDS are implemented for CHAR, VARCHAR, LONGCHAR and
UNICODE.

* CREATE FULLTEXT INDEX
  The FULLTEXT attribute:
  - is stored in table sysindices as type 'Txt'
  - is automatically set for indices on larger character columns
  - is passed to the CREATE INDEX function to create fulltext indices for any
character column.

* Bug fix for WORDS(x) in a SELECT column list.
  The statement listed the text content of the column x instead of the words.

* Bug fix for WORDS(x) in WHERE clause in combination with other conditions
caused a
not implemented error.

* Sort relation can now handle LONGCHAR and LONGBIN types!
This means duplicates rows will be removed, even when columns of LONGCHAR and
LONGBIN types are selected.

* Bug fixed for restriction on calculated columns.
A bad execution plan was generated when a restriction was applied on
calculated columns.

* Bug fixed in SELECT ... FOR UPDATE.
Deadlock could occure with this statement because the index file was only
read locked. Now an exclusive lock is requested for the index and this lock
is held until the transaction ends.

Character Functions
-------------------

Some of the string functions return result types that are as large as their
arguments. For VARCHAR results, the maximum length is 61400 characters.
Return values that exceed the limit are truncated without warning.
When these functions are used in a select column list, beware that the total
row size is also limited to 61400 bytes.
To avoid the overflow in the row size and to be not surprized about the
result type, you should explicitly type cast the result.

  Example: SELECT id, VARCHAR[20000] CONCAT(text_a, text_b) FROM table_a

In the next releases of PrimeBase functions that currently can only return
VARCHAR results will return the data type of the first argument.

Function names are case insensitive. Function arguments are automatically
converted to the required data type.

* SMALLINT ASCII(char c)
  returns <c> as the binary equivalent INTEGER number.

* VARCHAR CHR(INTEGER n)
  Returns the lower 8 bits of <n> as a binary equivalent character of the
host character set.

* VARCHAR CONCAT(VARCHAR a, VARCHAR b)
* UNICODE CONCAT(UNICODE a, UNICODE b)
  Concatenates two strings <a> and <b>. When an argument is NULL, it is
replaced with an empty string. It is a replacement for the '+' operator, when
a domain rule prevents the '+' operator or NULL values should be ignored.

* VARCHAR INITCAP(VARCHAR string)
* UNICODE INITCAP(UNICODE string)
  Returns <string> with the first character of each word in upper case and
all other alphabet characters in lower case. Words are delimited by
characters that are not alphanumeric.

* VARCHAR TOLOWER(VARCHAR string)
* UNICODE TOLOWER(UNICODE string)
  Returns <string> with all alphabet characters in lower case.

* VARCHAR TOUPPER(VARCHAR string)
* UNICODE TOUPPER(UNICODE string)
  Returns <string> with all alphabet characters in upper case.

* VARCHAR WORDS(VARCHAR database_column)
* UNICODE WORDS(UNICODE database_column)
  Returns the words of a column. The function is used to access the full text
index word list. Use it to build full text search conditions and for listing
words from the words index directory in a SELECT column list. This can
currently only be used for columns that have a full text index defined for
them. When the database_column is of type UNICODE, the return type is
UNICODE[243], otherwise VARCHAR[243].

You can use all relational operators (==, !=, <=, <, >, >=, [NOT] BETWEEN, IS
[NOT] NULL, [NOT] LIKE) to search the full text index word list.

  Example:
  List words that are greater than 'A' and less than 'C' and that end with
'er'

    SELECT distinct WORDS(.content) AS Words FROM fulltext
    WHERE WORDS(.content) > 'A'
    AND WORDS(.content) LIKE '%er'
    AND WORDS(.content) < 'C';

* INTEGER LENGTH(GENERIC string)
  Return the length of <string> in character units. UNICODE characters
require 2 bytes per character. For other types the size is returned in byte
units.

* VARCHAR LEFT(VARCHAR string, VARCHAR pattern [, INTEGER count])
  Returns the string to the left of the given pattern.

  <string> - the string to be searched.
  <pattern> - a pattern to be found in <string>.
  <count> - an optional argument. If included, a positive <count> value
causes the search of <string> to continue until <count> occurrences have been
found, starting from the left side of the string. A negative <count> value
causes the search to begin at the end and proceed to the start of <string>.

* VARCHAR RIGHT(VARCHAR string, VARCHAR pattern [, INTEGER count])
  Returns the string to the right of the given pattern.

  <string> - the string to be searched.
  <pattern> - a pattern to be found in <string>.
  <count> - an optional argument. If included, a positive <count> value
causes the search of <string> to continue until <count> occurrences have been
found, starting from the left side of the string. A negative <count> value
causes the search to begin at the end and proceed to the start of <string>.

* VARCHAR SUBSTR(VARCHAR string, INTEGER position [, INTEGER len])
  Returns a substring extracted from a given string value.

  <string> - a VARCHAR, CHAR or LONGCHAR value.
  <position> - an INTEGER specifying the starting position within <string>.
The first character of <string> has a position of 1. If <position> is
negative, then it specifies a position relative to the end of <string>. The
last character of <string> is designated by the value -1.
  <length> - this argument is optional, and specifies the number of
characters to extract. If length is negative, then position specifies the
last character of the extracted string, and extraction proceeds to the left
instead of to the right. If omitted, the function extracts the entire
remainder of <string>.

* INTEGER LOCATE(string, VARCHAR pattern [, INTEGER count])
  Returns the offset of the pattern in the string. If the pattern could not
be found, LOCATE() returns 0.

  <string> - a VARCHAR, CHAR or LONGCHAR argument to be searched. When the
string argument is a column that has a collation sequence, the pattern will
be searched with keys of the collation, to do case insensitive and/or ignores
accent matching.
  <pattern> - a VARCHAR pattern to be found in <string>.
  <count> - an optional argument. If included, a positive <count> value
causes the search of <string> to continue until <count> occurrences have been
found, starting from the right side of the string. A negative <count> value
causes the search to begin at the end of <string>, and proceed from right to
left.

  Example:
    SELECT .pathname + .filename, LOCATE(.content, 'good')
    FROM fulltext WHERE WORDS(.content) == 'good'; PRINTALL;

* INTEGER LOCATEWORDS(VARCHAR string, VARCHAR pattern [, INTEGER max_dist])
  Returns the offset of the pattern in the string. If the pattern could not
be found, LOCATEWORDS() returns 0.

  Due in the next release!

  <string> - a VARCHAR, CHAR or LONGCHAR argument to be searched. When the
string argument is a column that has a collation sequence, the pattern will
be searched with keys of the collation, to do case insensitive and/or ignores
accent matching.
  <pattern> - a VARCHAR pattern to be found in <string>.
  <max_dist> - an optional argument and specifies the maximum distance
between words in count of characters.

  When the words of <pattern> are located in <string>, the same order of
words has to be found. Non-alphanumeric characters define the word boundaries
and are ignored in the comparison. Accent characters like '' will match HTML
entities of the form '&Uuml;'.

* VARCHAR REPLACE(VARCHAR string, VARCHAR search [, VARCHAR replacement])
  Returns <string> with every occurrence of <search> replaced by
<replacement>. The default value for <replacement> is an empty string. When
it is omitted or NULL, all occurrences of <search> are removed. If <search>
is NULL, <string> is returned.

* VARCHAR TRANSLATE(VARCHAR string, VARCHAR from [, VARCHAR to])
  Returns <string> with all occurrences of each character in <from> replaced
by character in <to> that is in the same position as in <from>. When <from>
is longer than <to>, then char of <from> that have not corresponding
character in <to> are removed.

* VARCHAR XMLPATH(VARCHAR text, VARCHAR search_path, VARCHAR escape)
  Returns a concatenation of sections of <text> where the nesting of <XX>
</XX> tags matches the <search_path> argument.

  <text> contains html or xml text that can be parsed by the function.
  <search_path> is a list of tag names separated by '/'. The matching is
performed using the wildcard syntax of the LIKE operator.
  <escape> is used to enable searching of the characters that a used of
wildcards ('%','_','*','?'). When a character is preceded with the <escape>
character, it will be used literally.

  This function may be changed in future releases to cover a subset of the
XQL language. See http://www.w3.org/TandS/QL/QL98/pp/xql.html

* VARCHAR TO_CHAR(GENERIC num , VARCHAR format)
  Returns a formated string that is converted from <num> with the specified
<format>. The content of <format> depends on the data type of <num>. The
format string has the notation of Format Control Variables:

	Type:		Notation:	Example:
	DATETIME	$tsfmt		"DDD, DD. MMM HH:MM"
	DATE		$datefmt	"MM/DD/YYYY"
	TIME		$timefmt	"HH:MM:SS:hu"
	DECIMAL		$decfmt		"[9999].9"
	MONEY		$moneyfmt	"$[9999].9"
	SMALLINT	$format		"%3.3d km/h"
	INTEGER		$format		"%05d"
	FLOAT		$format		"%10.2f"

* VARCHAR LTRIM(VARCHAR string [, VARCHAR set])
  Returns <string> with all characters of <set> removed from the left.
  Starting with first character the function skips all characters that are in
<set> until a character is not found in <set>.

  <set> is optional and defaults to a single blank.

* VARCHAR RTRIM(VARCHAR string [, VARCHAR set])
  Returns <string> with all characters of <set> removed from the right.
  Starting with last character the function skips all characters from the
end, that are in <set> until a character is not found in <set>.

  <set> is optional and defaults to a single blank.

* VARCHAR LPAD(VARCHAR string, int length, VARCHAR padding)
  Returns a left padded <string> with a specified <length>.
  When <string> is shorter than <length> characters of <padding> are
repeatedly appended until the string is <length> characters long.
  When <string> is longer than <length>, <string> is truncated.

* VARCHAR RPAD(VARCHAR string, int length, VARCHAR padding)
  Returns a right padded <string> with a specified <length>.
  When <string> is shorter than <length> characters of <padding> are
repeatedly appended until the string is <length> characters long.
  When <string> is longer than <length>, <string> is truncated.

* UNICODE NORMALIZE(UNICODE string, INTEGER form)
  Returns a UNICODE <string> normalized to the requested <form>.

    Form:   Description:
    0       (F)  Canonical Decomposition
    1       (KD) Compatibility Decomposition
    2       (C)  Canonical Decomposition, followed by Canonical Composition
    3       (KC) Compatibility Decomposition, followed by Canonical
Composition

  For more details see http://www.UNICODE.org/UNICODE/reports/tr15/

Generic functions:
------------------

When arguments have different types, sizes or scales, the return type will
have the larger types, sizes and scales.

* GENERIC IF(cond, GENERIC a, GENERIC b)
  When <cond> evaluates to TRUE, <a> is returned, else <b> is returned.

* GENERIC GREATEST(GENERIC a, GENERIC b)
  The function compares the arguments and returns the greatest value.

* GENERIC LEAST(GENERIC a, GENERIC b)
  The function compares the arguments and returns the least value.

* GENERIC NVL(GENERIC a, GENERIC b)
  When <a> is not NULL <a> is returned, else <b> is returned.

* INTEGER USER()
  Returns the user id for the current connection to the database server.

  Due in the next release!

* DATETIME NOW()
  Returns the current date and time of the host clock where the database
engine is executed.
  When a database column has a NOW default value, the current time is
assigned from the host clock at insert time. When you update that column to
set the current time, the NOW() function should be used to be sure that the
same clock is used.

Floating point mathematical functions:
--------------------------------------

* numeric ABS (numeric x)
  Returns the absolute value of the numeric argument x. The returned data
type is the same as the argument type.

* FLOAT CEIL(FLOAT x)
  Returns the smallest INTEGER not less than x.

* FLOAT COS(FLOAT x)
  Returns the cosine of argument x. (An angle expressed in radians.)

* FLOAT COSH(FLOAT x)
  Returns the hyperbolic cosine of argument x as FLOAT.

* FLOAT EXP(FLOAT x)
  Returns e to the x'th power. e is the base of natural logarithms.

* FLOAT FLOOR(FLOAT x)
  Returns the largest INTEGER value not larger than x as FLOAT.

* FLOAT LN(FLOAT x)
  Returns the natural logarithm of argument x.

* FLOAT LOG10(FLOAT x)
  Returns the base-10 logarithm of argument x.

* FLOAT POWER(FLOAT x, FLOAT y)
  Returns x raised to the power of y.

* DECIMAL ROUND(DECIMAL x [, INTEGER m])
  For positive <m> it returns <x> rounded to <m> places right of the DECIMAL
point;
  For negative <m> it returns <x> round off digits left of the DECIMAL point.
  <m> is optional and a default of 0 is used.

* GENERIC SIGN(GENERIC x)
  Returns 1 when x is greater than 0.
  Returns -1 when x is less than 0.
  Returns 0 when x is equal 0.

  The data type of the result is the same as the argument type.

* FLOAT SIN(FLOAT x)
  Returns the sine of argument x.

* FLOAT SINH(FLOAT x)
  Returns the hyperbolic sine of argument x.

* FLOAT SQRT(FLOAT x)
  Returns the square root of x.

* FLOAT TAN(FLOAT x)
  Returns the tangent of x in radians.

* FLOAT TANH(FLOAT x)
  Returns the hyperbolic tangent of x.

* FLOAT TRUNC(FLOAT x, INTEGER m)
  For positive <m> it returns <x> truncated to <m> places right of the
DECIMAL point;
  For negative <m> it returns <x> truncate <m> digits left of the DECIMAL
point.
  <m> is optional and a default of 0 is used.

* FLOAT ACOS(FLOAT x)
  Returns the principal value of the arc cosine of argument x.
  x must be in a range -1 to +1.

* FLOAT ASIN(FLOAT x)
  Returns the principal value of the arc sine of argument x.

* FLOAT ATAN(FLOAT x)
  Returns the principal value of the arc tangent of argument x.
  When x is between -1 and +1 it returns the arc sin of x in the range -pi/2
to +pi/2

* FLOAT ATAN2(FLOAT x, FLOAT y)
  Returns the principal value of the arc tangent of argument x/y.
  It uses the signs of both arguments to determine the quadrant of the return
value.

* Parts of DATE and TIME values:

  DATE(a)
      Returns the date part of a DATETIME argument
  TIME(a)
      Returns the time part of a DATETIME argument
  YEAR(a)
      Returns the year part of a DATETIME or date argument
  MONTH(a)
      Returns the month part of a DATETIME or date argument
  DAY(a)
      Returns the day part of a DATETIME or date argument
  HOUR(t)
      Returns the hour part of a DATETIME or time argument
  MINUTE(t)
      Returns the minute part of a DATETIME or time argument
  SECOND(t)
      Returns the second part of a DATETIME or time argument


JDBC
----

* PrimeBase JDBC now implements the "meta data" functions.

PrimeBase Virtual Machine
-------------------------

* $charset can be used to specify the local character set to be used by PBVM.

As mentioned above, character sets must be specified using the name of the
UNICODE conversion file found at www.unicode.org.

By default this variable is set to the platform specific character set. The
defaults are currently:

  - Macintosh: ROMAN.TXT
  - UNIX:      8859-1.TXT
  - Windows:   CP1252.TXT

Conversion to and from the local character set for data inserted and
retrieved from the database is only done if the character set for the
database server has been specified (see System.CharSet above).

* The sub-directory 'exec' in the Setup folder is now searched for executable
files in addition to the standard locations and the 'scripts' directory. The
'exec' directory is intended for system related code.

* 'initialize.sys' and 'startup.sys' replace 'initialize.dal' and
'startup.dal' if they exists. These new files are intended to contain system
"boot code" for the sessions created by the virtual machine.



