Preparing consolidated databases

Whether you are designing a new database or preparing an existing
one to be used as a MobiLink consolidated database, you must install
the MobiLink system tables in that database. SQL Anywhere
provides setup scripts for Sybase Adaptive Server Enterprise, Oracle,
Microsoft SQL Server, and IBM DB2.
A setup script is not required for SQL Anywhere databases.

MobiLink system tables store information for MobiLink users,
tables, scripts, and script versions in the consolidated database.
You will probably not directly access these tables, but you alter
them when you perform actions such as adding synchronization scripts.

ODBC connections and drivers

To carry out synchronization, the MobiLink synchronization
server needs an ODBC connection to the consolidated database. You
must have an ODBC driver for your server and you must create an
ODBC data source for the database on the machine on which your MobiLink
synchronization server is running. For a list of supported drivers,
see Recommended ODBC Drivers for MobiLink
.

Writing synchronization scripts

There are two types of events that occur during synchronization
and for which you need to write synchronization scripts:

• Connection events that
  perform global tasks required during every synchronization
• Table events that
  are associated with a specific table and perform tasks related to
  modifying data in that table

Connection events

At the connection level, the sequence of major events is as
follows:

When a synchronization request occurs, the begin_connection event
is fired. When all synchronization requests for the current script
version have been completed, the end_connection event
is fired. Typically you place initialization and cleanup code in
the scripts for these events, such as variable declaration and database
cleanup.

Apart from begin_connection and end_connection,
all of these events take the MobiLink user name stored in the ml_user table
in the consolidated database as a parameter. You can use parameters
in your scripts by placing question marks where the parameter value
should be substituted.

To make scripts in SQL Anywhere databases easier to read,
you might declare a variable in the begin_connection script,
then set it to the value of ml_username in
the begin_synchronization script.

For example, in begin_connection:

In begin_synchronization:

The begin_synchronization and end_synchronization events
are fired before and after changes are applied to the remote and
consolidated databases.

The begin_upload event marks the
beginning of the upload transaction. Applicable inserts and updates
to the consolidated database are performed for all remote tables,
then rows are deleted as applicable for all remote tables. After end_upload,
upload changes are committed.

If you do not want to delete rows from the consolidated database,
do not write scripts for the upload_delete event,
or use the STOP SYNCHRONIZATION DELETE statement
in your PowerScript code. For more information, see “Deleting rows from the remote
database only”.

The begin_download event marks
the beginning of the download transaction. Applicable deletes are
performed for all remote tables, and then rows are added as applicable
for all remote tables in the download_cursor.
After end_download, download changes
are committed. These events have the date of the last download as
a parameter.

Other connection-level events can also occur, such as handle_error, report_error,
and synchronization_statistics. For
a complete list of events and examples of their use, see the chapter
on synchronization events in the MobiLink Administration
Guide
.

Table events

Many of the connection events that occur between the begin_synchronization and end_synchronization events,
such as begin_download and end_upload,
also have table equivalents. These and other overall table events
might be used for tasks such as creating an intermediate table to
hold changes or printing information to a log file.

You can also script table events that apply to each row in
the table. For row-level events, the order of the columns
in your scripts must match the order in which they appear in the CREATE
TABLE statement in the remote database, and the column
names in the scripts must refer to the column names in the consolidated
database.

Generating default scripts

Although there are several row-level events, most tables need
scripts for three upload events (for INSERT, UPDATE,
and DELETE) and one download event. To speed
up the task of creating these four scripts for every table, you
can generate scripts for them automatically by running the “create
a synchronization model” task from the MobiLink plug-in
in Sybase Central.

For information on the MobiLink plug-in, see the online MobiLink
Getting Started
book.

The MobiLink plug-in allows you to add more functionality
to default scripts than default scripts generated in earlier versions
of MobiLink. However, if you are using ASA 8 or ASA 9 instead of SQL Anywhere 10 or 11, you can still generate
default synchronization scripts by starting the MobiLink synchronization
server with the -za switch and setting the SendColumnNames extended
option for dbmlsync.

The following procedure describes how to generate ASA 8 or
9 synchronizations scripts from the PowerBuilder UI.

To generate ASA 8 or 9 synchronization scripts
automatically from PowerBuilder:

1. Expand the ODBC Utilities folder in the
   Database painter and double-click the MobiLink Synchronization
   Server item.

   The MobiLink Synchronize Server Options dialog box displays.

2. Select Adaptive Server Anywhere 8 or 9 from the
   MobiLink Version drop-down list.

   You enable the Automatic Script Generation check box.

3. Select the Automatic Script Generation check box
   in the MobiLink Synchronize Server Options dialog box and click
   OK to start the server.

   You can open this dialog box from the Utilities folder in
   the Database painter or the Database Profiles dialog box.

4. In your application, enter SendColumnNames=ON in
   the Extended text box on the Settings page of the w_appname_sync_options window.

   You must have at least one publication, user, and subscription
   defined in the remote database. If you have more than one publication
   or user, you must use the -n and/or -u switches
   to specify which subscription you want to work with.

   If there are existing scripts in the consolidated database,
   MobiLink does nothing. If there are no existing scripts, MobiLink
   generates them for all tables specified in the publication. The
   scripts control the upload and download of data to and from your
   client and consolidated databases.

   If the column names on the remote and consolidated database
   differ, the generated scripts must be modified to match the names
   on the consolidated database.

You can also generate ASA 8 or 9 synchronization scripts from
a command prompt. Start the server using the -za switch,
then run dbmlsync and set the SendColumnNames
extended option to on. For example:

Generated scripts

Table 13-3 shows
sample default scripts generated by the MobiLink plug-in in Sybase
Central. The scripts are generated for a table named emp with
the columns emp_id, emp_name,
and dept_id. The primary key is emp_id.
The generated download scripts use a timestamp based download.

Table 13-3: Sample default synchronization
scripts from MobiLink plug-in

Script name	Script
upload_insert	INSERT INTO “GROUP1″.”emp” ( “emp_id”, “emp_name”, “dept_id” )VALUES ( {ml r.”emp_id”}, {ml r.”emp_name”}, {ml r.”dept_id”} )
upload_update	UPDATE “GROUP1″.”emp” SET “emp_name” = {ml r.”emp_name”}, “dept_id” = {ml r.”dept_id”} WHERE “emp_id” = {ml r.”emp_id”}
upload_delete	DELETE FROM “GROUP1″.”emp” WHERE “emp_id” = {ml r.”emp_id”}
download_cursor	SELECT “GROUP1″.”emp”.”emp_id”, “GROUP1″.”emp”.”emp_name”, “GROUP1″.”emp”.”dept_id” FROM “GROUP1″.”emp” WHERE “GROUP1″.”emp”.”last_modified” >= {ml s.last_table_download}
download_delete_cursor	SELECT “emp_del”.”emp_id FROM “emp_del” WHERE “emp_del”.”last_modified” >= {ml s.last_table_download}

The scripts that you generate with the MobiLink plug-in constitute
a synchronization model. After you create a synchronization model,
you must use the “Deploy the synchronization model” task
of the plug-in to deploy the scripts to consolidated and remote
databases or to SQL files.

Table 13-4 shows
the scripts that are generated for the same table using the -za command
switch for the ASA 9 MobiLink synchronization server. The scripts generated
for downloading data perform “snapshot” synchronization.
A complete image of the table is downloaded to the remote database.
Typically you need to edit these scripts to limit the data transferred.

For more information, see “Limiting data downloads”.

Table 13-4: Sample default scripts generated
by dbmlsrv9 -za

Script name	Script
upload_insert	INSERT INTO emp (emp_id, emp_name, dept_id) VALUES (?,?,?)
upload_update	UPDATE emp SET emp_name = ?, dept_id = ? WHERE emp_id=?
upload_delete	DELETE FROM emp WHERE emp_id=?
download_cursor	SELECT emp_id, emp_name, dept_id FROM emp

Before modifying any scripts, you should test the synchronization
process to make sure that the generated scripts behave as expected.
Performing a test after each modification will help you narrow down
errors.

Working with scripts and users in Sybase Central

You can view and modify existing scripts and write new ones
in the MobiLink Synchronization plug-in in Sybase Central. These
procedures describe how to connect to the plug-in and write scripts,
and how to add a user to the consolidated database.

To connect to a consolidated database in Sybase
Central:

1. Start Sybase Central and select Connections>Connect
   with MobiLink 11 from the menu bar.

2. On the Identification page in the Connect to Consolidated
   Database dialog box, select or browse to a data source name or file,
   and click OK.

When you expand the node for a consolidated database in the
MobiLink Synchronization plug-in, you see folders with the following
labels: Tables, Connection Scripts, Synchronized Tables, Users,
Versions, and Notifications. All the procedures in this section
begin by opening one of these folders.

Script versions

Scripts are organized into groups called script versions.
By specifying a particular version, MobiLink clients can select
which set of synchronization scripts is used to process the upload
stream and prepare the download stream. If you want to define different
versions for scripts, you must add a script version to the consolidated
database before you add scripts for it.

If you create two different versions, make sure that you have
scripts for all required events in both versions.

To add a script version:

1. Open the Versions folder, then select File>New>Version
   from the Sybase Central menu bar.

2. In the Create Script Version wizard, provide a
   name for the version and optionally a description, then click Finish.

   Sybase Central creates the new version and gives it a unique
   integer identifier.

Adding synchronized tables and scripts

Scripts added for connection events are executed for every
synchronization. Scripts added for table events are executed when
a specific table has been modified. You must specify that a table
is synchronized before you can add scripts for it.

To add a table for synchronization:

1. Open the Synchronized Tables folder and
   select File>New>Synchronized Table.

2. Specify a remote table name you want to synchronize
   or select a table in the consolidated database that has the same
   name as a table in the remote database.

3. Click Finish.

To add a script to a synchronized table:

1. Double-click a table name in the Synchronized
   Tables folder, then select File>New>Table Script.

2. In the Create Table Script wizard, select the
   version for which you want to add a script, select the event you
   want to cause the script to execute, and click Next.

3. Choose to create a new script definition and the
   language (SQL, Java, or .NET) in which you want to write
   the definition, or select an existing script version that you want
   to share for the new script.

4. Click Finish.

5. Type your script in the editor that displays,
   then save and close the file.

   For example, if you want to remove rows that have been shipped
   from the Order table in a remote database,
   you can place the following SELECT statement
   in the download_delete_cursor event,
   where order_id is the primary key
   column. The first parameter to this event is the last_download timestamp.
   It is used here to supply the value for a last_modified column:

   PowerBuilder

   SELECT order_id<br /> FROM Order<br />WHERE status = 'Shipped'<br /> AND last_modified &gt;= ?

   1	SELECT order_id<br />  FROM Order<br />WHERE status = 'Shipped'<br />  AND last_modified &gt;= ?

   For more information about using the download_delete_cursor event,
   see the section on “Writing download_delete_cursor
   scripts” in the online MobiLink – Server Administration
   book.

To add a connection-level script:

1. Open the Connection Scripts folder and
   select File>New>Connection Script from the menu
   bar.

2. Follow steps 2 to 5 in the previous procedure.

Adding users

You can add users directly to the ml_user table
in the consolidated database, then provide the user names and optional
passwords to your users. To add a user, open the Users folder, select
File>New>User, and complete the Create User wizard.

You also have to add at least one user name to each remote
database, as described in “Creating MobiLink users”.

---