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 9.0.0 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:
|
1 |
begin_connection<br /> begin_synchronization<br /> begin_upload<br /> end_upload<br /> prepare_for_download<br /> begin_download<br /> end_download<br /> end_synchronization<br />end_connection |
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:
|
1 |
CREATE VARIABLE @sync_user VARCHAR(128); |
In begin_synchronization:
|
1 |
SET @sync_user = ? |
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 Synchronization
User’s 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 starting the MobiLink synchronization
server with the -za switch and setting the SendColumnNames extended
option for dbmlsync.
Read-only remote databases If the remote database is read-only—that is, you
never want to upload any data—you should not implement
the upload scripts. You can use the -ze switch to
generate sample scripts, and use the download samples as templates
for your download scripts.
To generate synchronization scripts automatically
in PowerBuilder:
-
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. -
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 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:
|
1 |
dbmlsrv10 -c "dsn=masterdb" -za |
|
1 |
dbmlsync -c "dsn=remotedb" -e SendColumnNames=ON |
Generated scripts
Table 13-3 shows
the scripts that are generated for a table named emp with
the columns emp_id, emp_name,
and dept_id. The primary key is emp_id.
| 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 |
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”.
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:
-
Start Sybase Central, select Tools>Connect
from the menu bar, select MobiLink Synchronization from the New
Connection dialog box, and click OK. -
On the Identification page in the Connect dialog
box, select ODBC DataSource Name, browse to select the DSN of the
consolidated database, and click OK.
When you expand the node for a consolidated database in the
MobiLink Synchronization plug-in, you see five folders: Tables,
Connection Scripts, Synchronized Tables, Users, and Versions. 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:
-
Select the Versions folder and double-click
Add Version. -
In the Add a New Script Version dialog box, provide
a name for the version and optionally a description, and click Finish.Sybase Central creates the new version and gives it a unique
integer identifier.
Adding 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 synchronized table to a consolidated
database:
-
Select the Tables folder and double-click
DBA. -
Right-click the table you want to add to the list
of synchronized tables and select Add to Synchronized Tables from
its pop-up menu.
To add a script to a synchronized table:
-
Select the Synchronized Tables folder,
select the table for which you want to add a script, and double-click
Add Table Script. -
From the first drop-down list, select
the version for which you want to add a script. -
From the second drop-down list, select
the event for which you want to add a script.Events that already have a script do not appear in the drop-down
list. -
From the third drop-down list, select
the language in which you want to write a script. -
Make sure the Edit the Script of the New Event
Immediately check box is selected and click Finish. -
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:1SELECT order_id<br /> FROM Order<br />WHERE status = 'Shipped'<br /> AND last_modified >= ?For more information about using the download_delete_cursor event,
see the section on handling deletes in the MobiLink Synchronization
User’s Guide
.
To add a connection-level script:
-
Select the Connection Scripts folder and
double-click Add Connection Script. -
Follow steps 2 to 6 in the previous procedure.
Modifying scripts
To modify an existing script, navigate to the script in Sybase
Central as described in the preceding procedures, then double-click
the Edit icon to the left of the version name.
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, select the Users folder,
double-click Add User, and complete the Add User wizard.
You also have to add at least one user name to each remote
database, as described in “Creating MobiLink users”.