Cloudsync Commands Reference
Page 4 of 8

CREATE PUBLICATION

Creates a publication based on a source database. This statement can only be executed at the source database. A publication defines which objects in the source database will be synchronized with a certain set of targets. It also can define database properties.

Syntax

CREATE PUBLICATION PublicationName
    [ ( ParameterSpec [ , ParameterSpec ]* ) ]
    [ ADD TABLE TableName
        [ ( SimpleColumnName [, SimpleColumnName ] * ) ]
    [ WHERE restriction ] ]*
    [ ADD INDEX IndexName ]*
    [ ADD VIEW ViewName ]*
    [ ADD STATEMENT StatementName ]*
    [ ADD METHOD ALIAS MethodAliasName ]*
    [ ADD WORK ALIAS WorkAliasname ]*
    [ ADD CLASS ALIAS ClassAliasName ]*
    [ ADD FOREIGN KEY ForeignKeyName ]*
    [ ADD AGGREGATE AggregateName ]*
    [ ADD TRIGGER TriggerName ]*
    [ ADD JAR FILE JarFileName ]*
    [ ADD TARGET DATABASE PROPERTY
        PropertyName='propertyValue' ]*

ParameterSpec

ParameterName DataType [ DEFAULT NULL ]

NOTE: The ADD clauses can be in any order.

SimpleColumnName is the column's name, unqualified by a table or correlation name, as within a CREATE TABLE statement. SimpleColumnName must identify a column in the table. ParameterName is an SQL-92-style identifier. (See the Cloudscape Reference Manual for more information on SQL-J identifiers.)

Object names (PublicationName, TableName, IndexName, ViewName, StatementName, ForeignKeyName, JarFileName, and so on) are SQL-J identifiers that may be qualified with a schema name. While objects in the SYS schema cannot appear in a publication, views can reference SYS schema objects. If the schema is not specified, the schema that holds the publication is assumed.

Syntax of DataType, TableName, SimpleColumnName, IndexName, ViewName, StatementName, ForeignKeyName, TriggerName, AggregateName, ClassAliasName, and JarFileName are described in the Cloudscape Reference Manual.

NULL is the only currently supported DEFAULT value for parameters.

If no column list is provided for a published table, all of the table's columns are published, including those added in later ALTER TABLE ADD COLUMN commands.

The WHERE Clause

Each ADD TABLE declaration can include an optional WHERE clause. The WHERE clause may refer to the publication parameters and published columns of that table only. Parameter references are prefixed with a `?'.

You must explicitly publish all work units and method aliases (as well as other objects) used by targets made from the publication. You must also make available to the target any Java classes used in the WHERE clause.

The WHERE clause of a CREATE PUBLICATION statement becomes a check constraint on a target created from the publication. Therefore, a target transaction that would violate the publication's WHERE clause will fail at the target.

A publication's WHERE clause should evaluate to the same boolean value each time it is run on an unchanged row. To ensure this, a WHERE clause cannot contain:

  • subqueries
  • aggregates
  • any of the following operators:
    • CURRENT_DATE
    • CURRENT_TIME
    • CURRENT_TIMESTAMP
    • RUNTIMESTATISTICS
    • USER
    • CURRENT_USER
    • SESSION_USER

A WHERE clause can contain any other SQL-J operators, including:

  • EXTRACT
  • CHAR_LENGTH
  • CHAR_LENGTH (or CHARACTER_LENGTH)
  • OCTET_LENGTH
  • BIT_LENGTH
  • IN values lists
  • LIKE
  • BETWEEN
  • boolean operators (AND, OR, NOT)
  • comparison operators (e.g., "<", "=")
  • arithmetic operators (i.e., "+", "-")
  • NEW
  • INSTANCEOF

Be careful when including object methods or fields in a CREATE PUBLICATION statement's WHERE clause. Make sure that the publication will always create the same target file if the published data is unchanged. To ensure this, be careful when using SQL-J statements in the method, since a query may return different results at different times.

Notes

A published table must have a primary key. The primary key must be contained in the table's published columns. This makes it possible to keep source and target data synchronized, since row identity comes from the primary key.

Tables must be explicitly published using the ADD TABLE clause. Once a table (or any other dictionary item) is explicitly published, you cannot drop it from the source until you drop all publications in which it occurs.

When you publish a table, you also automatically publish:

  • the table's primary key
  • the table's unique and check constraints, except those that refer to unpublished columns
  • the indexes that support the table's primary key or unique constraints
  • the column defaults
  • the table's conglomerate-level properties

Published statements keep their NO COMPILE status.

Indexes other than those supporting the table's primary key or unique constraints must be published explicitly. To do this, use the ADD INDEX clause. The table and all columns in the index must also be published.

Triggers must be published explicitly. Even if a trigger is published, changes made by the target trigger are not sent to the source at refresh time, unless these changes were made in a different connection (which is strongly discouraged). Cloudsync relies on the source trigger to ensure that the correct data is copied to the target at the end of the refresh operation. For more information about trigger behavior during refresh, see the section Triggers and the Refresh Operation.

Foreign keys must also be explicitly published.

To publish a view, you must also publish all tables, columns, method aliases, and other views that it depends on. There is no restriction on the operators used in a view. Views can reference built-in class aliases, since all databases, including targets, contain them.

When you publish a method alias, work unit, user-aggregate, or class alias, only the alias names are copied to the target; the classes themselves are not copied. Also, when you publish a table or view that uses a Java class, the class itself is not automatically copied. To synchronize such classes, put them into the database in jar files, and publish the jar files. See Distributing Application Code Within the Database for instructions.

Because DDL is not allowed on a target database, a publication which contains a stored prepared DDL statement will fail at run-time. All dictionary objects (e.g., tables, views) referenced by stored prepared DML statements in the publication must be published.

Changes to published static properties do not take effect until the target reboots.

JarFileName is a qualified name, that is, an optional schema name, followed by a dot, followed by an object name (a SQL-92 identifier). PropertyName is a dot-separated name (a Java identifier). PropertyValue is a string.

Please refer to Tuning Cloudscape for more information about properties.

If ADD TARGET DATABASE PROPERTY publishes a database class path, CREATE PUBLICATION fails unless the class path has two-part names and mentions only published jar files.

Example

CREATE PUBLICATION toySales (salesRepID INT)
ADD TABLE toys
ADD TABLE orders
    WHERE salesRepID = ?salesRepID
ADD TABLE leads
    WHERE sentToRepID = ?salesRepID
ADD METHOD ALIAS getKey
ADD WORK ALIAS placeOrder
ADD WORK ALIAS sendLead
ADD JAR FILE APP.targetWORK
ADD TARGET DATABASE PROPERTY
    cloudscape.database.classpath='APP.TargetWork'