TIBCO ActiveSpaces JDBC Driver               Last Updated: 2016-06-29

Overview
--------
The TIBCO ActiveSpaces JDBC Driver provides Java applications with the ability to interact with
ActiveSpaces through the use of SQL commands. The driver can be used to port existing applications,
which already use a JDBC driver to interact with a database, to TIBCO ActiveSpaces. The
TIBCO ActiveSpaces JDBC Driver can also facilitate the use of TIBCO ActiveSpaces by those users
who are familiar with the use of JDBC drivers by reducing their learning curve.

This is a partial implementation of a JDBC driver for TIBCO ActiveSpaces. The driver has
been developed to support the following SQL commands:

    - CREATE TABLE
    - INSERT
    - UPDATE
    - DELETE
    - SELECT

It is important to note that in some cases only partial support for a command has been implemented.
This is especially true for the SELECT command. See the sections for each command below
for a description of what has or has not been implemented.

The TIBCO ActiveSpaces JDBC Driver is designed to be a layer that sits on top of existing TIBCO
ActiveSpaces software and utilizes the Java API of TIBCO ActiveSpaces. This design requires the
ActiveSpaces software to be installed on each machine running processes which utilize the TIBCO
ActiveSpaces JDBC Driver.  Since TIBCO ActiveSpaces is also designed so that each machine running
a TIBCO ActiveSpaces application requires TIBCO ActiveSpaces software to be installed, there was
no need to design a JDBC driver which communicates to TIBCO ActiveSpaces using a network
protocol (JDBC Type 3 driver). Since TIBCO ActiveSpaces does not really have a specific database
protocol, it also did not make sense to implement a JDBC Type 4 driver.


SQL Syntax Support
------------------
The ActiveSpaces JDBC Driver has been developed to support common SQL syntax but the syntax has 
been simplified and customized for ActiveSpaces. It is *not* intended that the TIBCO ActiveSpaces 
JDBC Driver should be a JDBC Driver "adapter" for other databases. Therefore, in some cases the SQL
syntax recognized by other databases may not be recognized by the ActiveSpaces JDBC driver. Currently,
the ActiveSpaces JDBC Driver really only supports basic SQL command functionality.

The ActiveSpaces JDBC driver provides additional syntax support for cases where a normal SQL command 
does not provide adequate functionality for ActiveSpaces. For example, the syntax of the CREATE TABLE 
command has been extended to accept additional parameters which provide full support for defining an 
ActiveSpaces space.

The ActiveSpaces JDBC Driver does not support the following (this is a partial list):

    - Schemas
    - Modules
    - Unicode String & Identifiers

See the sections on the individual commands for information about the limitations of a particular
command.


JDBC Driver Implemenation Directories
-------------------------------------
The TIBCO ActiveSpaces JDBC Driver sources reside in several directories as described below:

    src/com/tibco/as/jdbc - Contains the classes which implement the Java JDBC interfaces.
    
    src/com/tibco/as/sql/grammar - Contains the ANTLR grammar and the files automatically 
        generated by ANTLR which are used to parse SQL commands passed to the JDBC driver.
    
    src/com/tibco/as/sql - Contains the classes which actually process the SQL commands by 
        making the appropriate calls into ActiveSpaces. The methods of the class ASSQLProcessor
        are invoked to cause the processing of each SQL command to occur.
    
    examples - Contain several examples which are intended to help learn about using the
        TIBCO ActiveSpaces JDBC Driver.


Getting Started
---------------
Several examples are provided with the TIBCO ActiveSpaces JDBC Driver which are intended to help
teach you how to use the driver in different scenarios as follows:

    - SimpleJDBC uses the TIBCO ActiveSpaces JDBC Driver to interact with ActiveSpaces using
      SQL commands.
      
    - MixedASJdbc combines the use of the TIBCO ActiveSpaces Java API with the use of the JDBC driver.
      The TIBCO ActiveSpaces Java API is first used to define and connect to a metaspace and space.
      Then the JDBC driver is used after that to manipulate the data in the space.
      
    - MixedJdbcAS also combines the use of the TIBCO ActiveSpaces Java API with the use of the
      JDBC driver. However, in this example the JDBC driver is used to first define and connect to
      a metaspace and space and then the TIBCO ActiveSpaces Java API is used to manipulate the
      data in the space.

It is recommended that you start with the example SimpleJDBC. The comments in the example will 
walk you through setting up your environment, building the driver and the example, and then
running the example. The comments throughout the code try to explain what is going on and things
you need to pay attention to. However, the comments do not attempt to rewrite the TIBCO ActiveSpaces
Developer's Guide. For information on the TIBCO ActiveSpaces objects and the Java API, you should read
the developer's guide and the JavaDoc for the API.

Both of the 'mixed' examples point out important things about how to use the TIBCO ActiveSpaces
JDBC Driver with the TIBCO ActiveSpaces Java API. Read the comments in the code!

Note: If you get a Java Verify Error when running the examples, you will need to specify the
following VM argument to get past the error:
    -XX:-UseSplitVerifier


JDBC URL
--------
The standard syntax for JDBC URLs is:
    jdbc:<subprotocol>:<subname>    
where:
    subprotocol is the name of the driver or the name of a database connectivity mechanism
    subname is a way to identify the data source 
    
The ActiveSpaces JDBC URL has been extended from the standard JDBC URL syntax to provide users the ability
to specify the various settings for connecting to an ActiveSpaces metaspace.  The ActiveSpaces JDBC URL format
is:

    jdbc:tibco:as[:<data-source-name>][;<propertyName>=<propertyValue>]*
    
The <data-source-name> is optional and specifies the metaspace name. The metaspace name can also be specified
as a property as in the following example URL:

    jdbc:tibco:as;metaspace=mymetaspace
    
If the metaspace name is not specified as a property or as <data-source-name>, the default metaspace name of 'ms'
is used.

This format for the JDBC URL for ActiveSpaces was chosen because it is similar to the formats used by the MySQL,
Microsoft SQL Server and the Oracle Thin Client JDBC URLs.

The following lists the possible properties which can be set for a metaspace. Of these properties, the metaspace
property and the discovery property must be identical for all applications trying to connect to the same metaspace. 
A new metaspace is created for any application which uses a different metaspace or discovery property setting than
other applications.

    Property               Description
    ---------              ------------
    metaspace              Metaspace name (default: 'ms')
    discovery              Metaspace discovery URL - how the metaspace discovers its members (default: tibpgm://239.8.8.8:7888).
                           To specify several well-known TCP addresses for discovery use tcp://ip1:port1;ip2:port2
    security_token         Path to a security token file. If specified, any discovery setting specified in the JDBC URL is ignored
                           as AS will use the discovery setting from the token file.
    member_name            Unique name to use for the member connecting to the metaspace (default: auto-generated)
    listen                 URL to use for listening for incoming connections from new metaspace members on. 
                           Format: tcp://[interface[:port]]  (default:  0.0.0.0 for the interface and the first port available
                           starting from port 5000 and above.)
    remote_listen          The URL to use to listen for remote client connections (optional).
    remote_discovery       Discovery URL for connecting to a metaspace as a remote client. However, it is preferred that the
                           discovery URL property be used to specify the remote discovery URL as in
                           �discovery=tcp://IP:port?remote=true�. (optional)
    transport_thread_count The number of threads a metaspace member can use for handling remote invocation calls.

If a security token file has been specified, the following additional properties can also be specified:

    Property                  Description
    ---------                 ------------
    authentication_domain     The user login domain
    authentication_username   The user login name
    authentication_password   The user login password.

Although ActiveSpaces only allows one metaspace connection by a single application, the JDBC driver will allow you to
use multiple Connection objects to the same metaspace within a single application.


ANTLR Grammar
-------------
An ANTLR grammar is used to parse the SQL query strings passed to the ActiveSpaces JDBC Driver. The grammar file is 
com/tibco/as/sql/grammar/ASSQL.g. Three files are automatically generated by ANTLR for this grammar. They are:

    ASSQLLexer.java
    ASSQLParser.java
    ASSQLTokens.java
    
Only the grammar file should ever be modified. These three files generated by ANTLR should never be directly modified.

The ASSQL.g grammar file is written to use Java. Since JDBC is Java based, there is no reason to expect that any other
language would need to be supported. Inside of the grammar, the appropriate Java class is created to handle each different
supported SQL command. It was felt that embedding the Java code inside of the grammar would be easier to understand for those
users who do not understand ANTLR.

Sometimes the driver's grammar allows an SQL command but portions of the command syntax are
ignored by the driver implementation. The idea in developing the grammar for this driver was to allow
common SQL syntax even though full support for the command may not have been implemented in the driver.
For example, the following command is successfully parsed by the driver even though support for
LIMIT and OFFSET has not been implemented:
    SELECT * FROM mytable LIMIT 100 OFFSET 0

Important!! SQL WHERE clauses are not fully supported. Currently the WHERE clause is used, in its
entirety, as an ActiveSpaces filter. However, the grammar still needs work to completely support
the successful parsing of all possible filter formats. Only simple filters are currently
supported such as:
    key=value
    name='Joe Smith'  (Note, the driver converts SQL single quotes to the double-quotes used by filters.)
    age>50
    name = 'John'' Doe'
    name = 'John'' Doe' and city = 'Bangalore'
    name = 'John'' Doe' or income = 50150.50
    name = 'John'' Doe' or (income > 50150.50 and age > 0)
    city is not null
    where city is null
    where city like 'View'


Building the ANTLR Grammar
--------------------------
If you find that you need to modify the grammar and build it yourself, you will
need to download the ANTLR v3 task for ant from:
    http://www.antlr3.org/share/list.html

Extract the contents of the zip file and copy the file ant-antlr3.jar into
your ANT lib directory.

Ensure you have both your AS_HOME and ANT_HOME environment variables set.
Change to the AS_HOME/examples/java/JDBCDriver directory and type:
    ant grammar -verbose

This will build the grammar in a temporary 'grammar' directory and if the build
is successful, it will then copy the resulting Lexer and Parser files into the
AS_HOME/examples/java/JDBCDriver/src/com/tibco/as/sql/grammar directory.


SQL Command Overview
--------------------
As mentioned above, the following SQL commands are supported:

    - CREATE TABLE
    - INSERT
    - UPDATE
    - DELETE
    - SELECT

The SQL keywords for each command are case insensitive. For example, the following three SQL commands are equivalent:

    SELECT * FROM mytable
    select * from mytable
    Select * From mytable

When strings are specified as values in an SQL command, strings should be enclosed in single quotes and not double quotes.
For example:

    INSERT INTO mytable (column1, column2) VALUES (�George�, �Washington�)
    
When you need to use a table name or column name that matches an SQL keyword, you should enclose the name in escaped
double-quotes to allow the ANTLR parser to differentiate between the keyword and the name. For example:

    CREATE TABLE myspace (\"key\" INTEGER NOT NULL, value VARCHAR, \"time\" DATETIME, PRIMARY KEY (\"key\"))


CREATE TABLE Basic Syntax
-------------------------
CREATE TABLE is used to define a TIBCO ActiveSpaces space, which is the equivalent of a database table.
Since an ActiveSpaces space is defined using specific space properties other than the normal field definitions
of a database table, the CREATE TABLE syntax has been extended to allow users to specify space properties.

Basic Syntax:

    CREATE  TABLE  <table_name>   (  <table_element>   [,  <table_element>  ]...  ) 
    
Where:

  <table_element> =
      <column_name>  <column_type>  [ NOT NULL]  [PRIMARY KEY]
     
Or:

  <table_element> = 
      [CONSTRAINT Identifier]  PRIMARY KEY  [ <key_type>  ]   ( <column_name>  [,  <column_name> ]...  )
Or:

  <table_element> = 
      INDEX  <table_index_name>   [ <key_type> ]  ( <column_name>  [, <column_name> ]...  ) 

Example:

    CREATE TABLE mytable (name VARCHAR(255) NOT NULL, age INTEGER, city VARCHAR(255), zipcode INTEGER, income REAL, PRIMARY KEY (name), INDEX age ( age)) 
    
Notes:

CONSTRAINT Identifier is ignored, if specified, but is allowed by the grammar in order to be compatible with other 
common SQL syntax variations.

<key_type> is an ActiveSpaces extension to allow the setting of the type of index to generate.  For key fields, 
ActiveSpaces by default will automatically generate an index of type HASH which speeds up queries where the filter 
is an exact match (�=� operator). Regular indexes are created with a default index type of TREE. TREE indexes speed
up queries where the filter is a range match (�>�, �<�, �>=�, �<=� operators). The <key_type> extension allows you
to control which type of index is created to suit your particular needs. 

Columns in a table are the equivalent of ActiveSpaces fields in a space. ActiveSpaces does not support all of the
SQL column types for the fields in a space.  The following table shows how the SQL column types are mapped to what
ActiveSpaces supports for field types:

    SQL Column Type        ActiveSpaces Field Data Type
    ---------------        ----------------------------
    BIT                    Boolean
    CHAR                   FieldDef.FieldType.CHAR
    CHAR(1)                FieldDef.FieldType.CHAR
    VARCHAR                FieldDef.FieldType.STRING
    CHAR(n) where n>1      FieldDef.FieldType.STRING
    VARCHAR(n)             FieldDef.FieldType.STRING
    LONGVARCHAR            FieldDef.FieldType.STRING
    LONGVARCHAR(n)         FieldDef.FieldType.STRING
    SMALLINT               FieldDef.FieldType.SHORT
    INTEGER                FieldDef.FieldType.INTEGER
    BIGINT                 FieldDef.FieldType.LONG
    REAL                   FieldDef.FieldType.FLOAT
    DOUBLE                 FieldDef.FieldType.DOUBLE
    BLOB                   FieldDef.FieldType.BLOB
    DATE                   FieldDef.FieldType.DATETIME
    TIME                   FieldDef.FieldType.DATETIME
    TIMESTAMP              FieldDef.FieldType.DATETIME

Note: Currently support for BLOB has not been implemented.

When the type of a column is retrieved from the ResultSetMetaData object, for those SQL columns which map to the same
ActiveSpaces field data type, the SQL column type listed first in the table is the type that will be returned. For example,
calling ResultSetMetaData.getColumnType for a column which was defined as SQL column type TIME, will result in the SQL
column type of DATE being returned. And if a column was defined as SQL column type CHAR(255), the SQL column type returned
from ResultSetMetaData.getColumnType will be VARCHAR.

The implementation of an ActiveSpaces field type may be different from the SQL column type. For an explanation of each of
the ActiveSpaces field types, see the ActiveSpaces documentation. 


CREATE TABLE Extended Syntax
----------------------------
An extended form of the CREATE TABLE syntax is available which additionally allows setting the properties of a space.
If not specified, the normal ActiveSpaces default for these properties will be used.

Extended Syntax:

    CREATE  TABLE  <table_name>   ( <table_element>  [, <table_element>]...  )  [ <space_property>  =  <value> [,  <space_property>  =  <value> ]...
    
Where <space_property> can be any of the following (case insensitive):

    capacity
    cache_policy
    distribution_policy
    eviction_policy
    file_sync_interval
    forget_old_value
    host_aware_replication
    lock_scope
    lock_TTL
    lock_wait
    min_seeders
    persistence_policy
    persistence_type
    phase_count
    phase_interval
    query_limit
    query_timeout
    read_timeout
    replication_count
    replication_policy
    routed
    space_wait
    ttl
    virtual_node_count
    write_timeout

See the ActiveSpaces documentation for the valid values for these properties.

Example:

    CREATE TABLE mytable (name VARCHAR(255) NOT NULL, age INTEGER, city VARCHAR(255), zipcode INTEGER, income REAL, PRIMARY KEY (name, age), INDEX income ( income)) DISTRIBUTION_POLICY distributed, TTL -1, REPLICATION_COUNT 1


INSERT Command
--------------
The INSERT command is used to add data into a space. If an entry already exists in the space, an error will be returned.

Syntax:

    INSERT  INTO  <table_name>  ( <column_name>  [,  <column_name> ]... )  VALUES  ( <column_value>  [, <column_value> ]...  )
    
Example:

    INSERT INTO mytable (name, city) VALUES (�John Doe�, �Dallas�)


UPDATE Command
--------------
The UPDATE command is used to modify the values of data which already resides in the space.

Syntax:

    UPDATE  <table_name>  SET  ( <column_name> = <column_value>  [, <column_name> = <column_value> ]... )  WHERE  [ <where_condition> ]
Example:

    UPDATE mytable SET age=80 WHERE name = �George Washington�
    
Notes:
If a <where_condition> is not specified, all entries in the table will be updated.
Currently the <where_condition> is not parsed but is used in its entirety as a filter for browsing the space entries.
Therefore, the <where_condition> cannot handle things like embedded SELECT queries and must be specified in such a way
that ActiveSpaces will be able to properly use it as a filter. See the ActiveSpaces documentation for information on
how to format filter strings. The one exception for specifying a where clause is that the value for strings should be
specified with single qutoes to follow SQL syntax rules as opposed to double quotes which is the normal ActiveSpaces
filter syntax rule.


DELETE Command
--------------
The DELETE command is used to remove entries from a space.

Syntax:

    DELETE  FROM  <table_name>  WHERE  [ <where_condition>  ]
    
Example:

    DELETE FROM mytable WHERE name = 'Joe Smith'
    
Notes:
If a <where_condition> is not specified, all entries in the table will be deleted.
Currently the <where_condition> is not parsed but is used in its entirety as a filter for browsing the space entries.
Therefore, the <where_condition> cannot handle things like embedded SELECT queries and must be specified in such a way
that ActiveSpaces will be able to properly use it as a filter. See the ActiveSpaces documentation for information on how
to format filter strings. The one exception for specifying a where clause is that the value for strings should be specified
with single qutoes to follow SQL syntax rules as opposed to double quotes which is the normal ActiveSpaces filter syntax rule.


SELECT Command
--------------
The SELECT Command is used for retrieving data from a space.

Syntax:

    SELECT  [ <select_quantifier> ]  <select_list>  FROM  <table_list>  [ <where_condition> ]
    
Where:

  <select_quantifier> is currently ignored but allowed values are ALL or DISTINCT
  
  <select_list>  can be any of:
      -    * (Asterisk, meaning all columns)
      -    <table_name>.*  (meaning all columns of the specified table, the table must also be in the FROM table list)
      -    <column_name>  [ AS  <column_alias>  ] [,  <column_name>  [ AS  <column_alias>  ] ]...   (use when only a single table is in the FROM table list)
      -    <table_name>.<column_name>  [ AS  <column_alias>  ]  [,  <table_name>.<column_name>  [ AS  <column_alias>  ]  ]...  (use when multiple tables are specified in the FROM table list)
      
  <table_list> has the format:
      <table_name>  [,  <table_name>  ]...
      
Examples:

    SELECT * FROM mytable
    SELECT * FROM mytable WHERE field1 = �success�
    SELECT firstname, lastname FROM mytable
    SELECT firstname, lastname FROM table1, table2 (table2 is ignored, use qualified column names when there are multiple tables)
    SELECT table1.name, table2.address FROM table1, table2  (null values will be filled in for missing values if one table is larger than the other)
    SELECT table1.name AS table1name, table2.address AS table2address FROM table1, table2  (results of select statement are retrieved from the ResultSet object using the specified column alias names)
    
Notes:
  When multiple FROM tables are listed, the dot notation for specifying the columns to retrieve is required even if a column is
  only present in one of the tables.
  
  If a <where_condition> is not specified, all entries in the table will be selected.
  
  Currently the <where_condition> is not parsed but is used in its entirety as a filter for browsing the space entries. Therefore,
  the <where_condition> cannot handle things like embedded SELECT queries and must be specified in such a way that ActiveSpaces
  will be able to properly use it as a filter. See the ActiveSpaces documentation for information on how to format filter strings.
  The following are a couple of exceptions to keep in mind:
    The value for strings should be specified with single quotes to follow SQL syntax rules as opposed to double quotes which
    is the normal ActiveSpaces filter syntax rule. The JDBC driver grammar takes care of coverting the single quotes to double
    quotes for you.
    
    The value for DateTime fields should be specified without any single or double quotes. The JDBC driver grammar takes care
    of converting the DateTime value to have single quotes as is required by ActiveSpaces filters. The format for DateTime
    field values are:
        YYYY-MM-DD
        HH:MM:SS[.MS] [GMT]
        YYYY-MM-DDTHH:MM:SS[.MS] [GMT]


Limiting The Number of SELECT Results
-------------------------------------
By default, the AS JDBC driver will use an ActiveSpaces SNAPSHOT browser for retrieving data from a space/table when a SELECT
command is issued. The SNAPSHOT browser will use a default prefetch size of 5000 with no limit on the number of rows returned.

To limit the number of rows returned for a SELECT command, Statement.setMaxRows() should be invoked prior to issuing a SELECT command.

When max rows is set to 0 (default), no limit is applied to the number of rows returned for a SELECT command.

If max rows is greater than 0, the number of rows returned for a SELECT command will be limited to what has been specified for max rows.

To change the prefetch size used for the SELECT command or to switch from using a SNAPSHOT browser to a CURRENT browser, Statement.setFetchSize() should be invoked prior to issuing a SELECT command.

When the fetch size is set to 0 (zero), the JDBC driver will use its default prefetch size of 5000.

When the fetch size is set to > 0, the SNAPSHOT browser used by the SELECT command will used the specified fetch size as its prefetch setting.

When the fetch size is set to -1, a CURRENT browser will be used by the SELECT command instead of a SNAPSHOT browser. A CURRENT browser does not prefetch data and retrieves a row of data each time next() is called on the ResultSet. A CURRENT browser also ignores any query limit setting, so the number of rows returned for the SELECT command will not be limited.