The mobile client database contains a subset of data stored in the Oracle database., which are synchronized with the Oracle database.
The following sections describe how you can programmatically configure the features for the back-end Oracle database.
Section 3.2, "Register a Remote Oracle Database for Application Data"
Section 3.3, "Create a Synonym for Remote Database Link Support For a Publication Item"
Section 3.7, "Set DBA or Operational Privileges for the Mobile Server"
If you want to delete a device, use the delete method from the Device class. To retrieve the Device object, use either the getDevice or getDeviceByName methods, as demonstrated below.
If the device id is available, the following can be directly used:
if (oracle.lite.resource.ResourceManager.getInstance() == NULL) oracle.lite.resource.ResourceManager.initialize(JDBC_URL, USER, PASSWORD); oracle.lite.resource.Device d = oracle.lite.resource.ResourceManager.getInstance().getDevice(deviceId); d.delete();
If the device id is not available, then you can provide the device name, which is shown on the Mobile Manager UI in the oracle.lite.resource.User.getDeviceByName(deviceName) method. Once retrieved, use the delete method of the Device object as demonstrated above.
By default, the repository metadata and the application schemas are present in the same database. However, it is possible to place the application schemas in a database other than the MAIN database where the repository exists. This can be an advantage from a performance or administrative viewpoint.
Thus, you can spread your application data across multiple databases.
Note:
We refer to the database where the application schema resides as remote because it is separate from the MAIN database that contains the repository. It does not mean that the database is geographically remote. It can be local or remote. For performance reasons, the mobile server must have connectivity to all databases involved in the synchronization—MAIN and remote.This section describes how to register a remote Oracle database containing application schemas, using the ConsolidatorManager APIs. However, it is recommended that you use the Oracle Database Mobile Server GUI tools for this task unless you have a specific need to use the API. For concepts and description of how to perform this with the Oracle Database Mobile Server GUI tools, see Section 5.8.1, "Register or Deregister an Oracle Database for Application Data" in the Oracle Database Mobile Server Administration and Deployment Guide.
To use an Oracle database other than the Oracle database used for the repository, perform the following:
Use the apprepwizard script to setup a remote application repository. See Section 3.2.1, "Set up a Remote Application Repository With the APPREPWIZARD Script" for details.
Register the Oracle database as described in Section 3.2.2, "Register or Deregister a Remote Oracle Database for Application Data".
When creating the publication and publication items, specify the name of the registered Oracle database that contains the application schemas. All data for a single application—that is, all publication items for the publication—must be contained in the same Oracle database.
Use the apprepwizard script to setup a remote application repository. This script creates and initializes an administrator schema with the same name as the adminstrator schema in the Main database. For example, if the administrator schema name in the Main database is mobileadmin, then the apprepwizard script will create a mobileadmin schema on the remote database.
The apprepwizard script is located in the ORACLE_HOME/Mobile/Server/admin. The usage of this script is as follows:
apprepwizard.bat <MAIN_Repository_Schema_Name> <MAIN_Repository_Schema_Password> <Application_Database_Administrator_User_Name> <Application_Database_Administrator_Password> <Application_Database_JDBC_URL> <Application_Database_Schema_Password> [<DB_name>]
Where each parameter is as follows:
MAIN_Repository_Schema_Name: Provide the repository schema name, which exists on the Main database. The default is MOBILEADMIN.
MAIN_Repository_Schema_Password: Provide the password for the repository administrator schema.
Application_Database_Administrator_User_Name: Any user with administrator privileges at the application database. such as SYSTEM.
Application_Database_Administrator_Password: Password of the administrator user for the application database.
Application_Database_JDBC_URL: JDBC URL of the application database.
Application_Database_Schema_Password: Password of the schema, which will be created at the application database. The user name is the same as the repository schema name.
DB_Name: Optionally, the user can provide a name to identify this database. This name is used in logging. By default, the log is sent to the console. If this name is provided as the last parameter, then the log is generated in the By default, the log is sent to the console. If the database name is provided as the last parameter, then the log is generated in the ORACLE_HOME/Mobile/Server/<DB_NAME>/apprepository.log file.
This script installs silently. Thus, If you execute this script without any arguments, nothing is performed.
Use the following ConsolidatorManager APIs to register, deregister, or alter the properties of the remote Oracle database:
void registerDatabase(String name, Consolidator.DBProps props) void deRegisterDatabase(String name) void alterDatabase(String name, Consolidator.DBProps props)
Where:
Name—An identifying name for the database where the application schema resides. Once defined, this name cannot be modified. This name must be unique across all registered database names.
DBProps—A class that contains the JDBC URL, password and description, as follows:
public static class DBProps {
public String jdbcUrl;
public String adminPassword;
public String description;
}
JDBC URL—The JDBC URL can be one of the following formats:
The URL for a single Oracle database has the following structure: jdbc:oracle:thin:@<host>:<port>:<SID>
The JDBC URL for an Oracle RAC database can have more than one address in it for multiple Oracle databases in the cluster and follows this URL structure:
jdbc:oracle:thin:@(DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=PRIMARY_NODE_HOSTNAME)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=SECONDARY_NODE_HOSTNAME)(PORT=1521)) ) (CONNECT_DATA=(SERVICE_NAME=DATABASE_SERVICENAME)))
Password—The administrator password is used to logon to the database. The administrator name is the same as what was defined for the main database.
When defining, the password must conform to the following restrictions:
not case sensitive
cannot contain white space characters
maximum length of 28 characters
must begin with an alphabet
can contain only alphanumeric characters
cannot be an Oracle database reserved word
Description—A user-defined description to help identify this database.
Refer to the ConsolidatorManager in the Oracle Database Mobile Server JavaDoc for more details.
The following code example registers a database as APP1. The registerDatabase API stores access information for the application repository and provides a name so that publications, publication items, and MGP Jobs can be created against this repository. It does not define the administrator schema.
Consolidator.DBProps props = new Consolidator.DBProps();
props.jdbcUrl = "jdbc:oracle:thin:@apphost:1521:app1";
props.description="App database 1"
props.adminPassword = "secret";
consMgr.registerDatabase("APP1", props);
The following code example deregisters the APP1 database.
consMgr.deRegisterDatabase("APP1");
You can retrieve the names of all of the registered databases with the getDatabaseInstances method, which is as follows:
Map getDatabaseInstances()
The Map returned by getDatabaseInstances method contains a keyset of the application database names and the entry for each key is a Consolidator.DBProps class where the adminPassword is always NULL for security purposes.
You must have already registered the remote database before defining publications, publication items, hints, and virtual primary keys that use the application data schemas and tables on the remote database. In the ConsolidatorManager API calls, the registered name of the remote database is required.
Note:
The publication and publication item names are unique irrespective of where the data resides.All publication items within a publication must be defined on tables within the same database.
The following example illustrates the creation of a publication and a publication item against a remote database registered as APP1. Refer to the ConsolidatorManager in the Oracle Database Mobile Server JavaDoc for more details.
ConsolidatorManager consMgr = new ConsolidatorManager();
consMgr.openConnection("mobileadmin", "mobileadmin",
"oracle:jdbc:thin:@host1:1521:master");
consMgr.createPublication( "PUB1","APP1",Consolidator.DFLT_CREATOR_ID,
"ddb.%s", NULL);
Consolidator.PubItemProps taskPIProps = new Consolidator.PubItemProps();
taskPIProps.db_inst = "APP1"; // Remote App database name as registered
taskPIProps.owner = "APPUSER1";
taskPIProps.store = "TASKS";
taskPIProps.refresh_mode = "F";
taskPIProps.select_stmt = "select id, emp_id, cust_id, stat_id, notes
from APPUSER1.TASKS";
taskPIProps.cbk_owner = "MOBILEADMIN";
taskPIProps.cbk_name = "TASKSPI_PKG";
consMgr.createPublicationItem( "PI_1_TASKS", taskPIPProps);
consMgr.addPublicationItem("PUB1", "PI_1_TASKS", NULL, NULL, "S", NULL, NULL);
consMgr.createSubscription( "PUB1", "USER1");
consMgr.instantiateSubscription("PUB1", "USER1");
consMgr.closeConnection();
Other API calls for managing data collection queues, hints, and virtual primary keys that require the remote database name are shown below. Refer to the ConsolidatorManager in the Oracle Database Mobile Server JavaDoc for more details.
Data Collection Queue
void createDataCollectionQueue(String name, String db_inst,
String owner, String store, String inq_cols, String pk_columns,
boolean purgeClientAfterSync, boolean isOutView)
Hint
void parentHint(String db_inst, String owner, String store, String owner_d, String store_d) void dependencyHint(String db_inst, String owner, String store, String owner_d, String store_d) void removeDependencyHint(String db_inst, String owner, String store, String owner_d, String store_d)
Virtual Primary Key
public void createVirtualPKColumn(String db_inst, String owner, String store, String column) public void dropVirtualPKColumns(String db_inst, String owner, String store)
The APIs used for creating a publication and publication item is the same except for the addition of the remote database name. Following is an example that provides the remote database name, APP1, in bold for creating a publication and publication item:
ConsolidatorManager consMgr = new ConsolidatorManager();
consMgr.openConnection("mobileadmin", "mobileadmin", "oracle:jdbc:thin:@host1:1521:master");
consMgr.createPublication( "PUB1","APP1",Consolidator.DFLT_CREATOR_ID,
"ddb.%s", NULL);
Consolidator.PubItemProps taskPIProps = new Consolidator.PubItemProps();
taskPIProps.db_inst = "APP1"; // Remote APP instance name as registered
taskPIProps.owner = "APPUSER1";
taskPIProps.store = "TASKS";
taskPIProps.refresh_mode = "F";
taskPIProps.select_stmt = "select id, emp_id, cust_id, stat_id, notes from APPUSER1.TASKSî;
taskPIProps.cbk_owner = "MOBILEADMIN";
taskPIProps.cbk_name = "TASKSPI_PKG";
consMgr.createPublicationItem( "PI_1_TASKS", taskPIPProps);
consMgr.addPublicationItem("PUB1", "PI_1_TASKS", NULL, NULL, "S", NULL, NULL);
consMgr.createSubscription( "PUB1", "USER1");
consMgr.instantiateSubscription("PUB1", "USER1");
consMgr.closeConnection();
The following sections describe how the synchronization callbacks, described in Section 2.7, "Customize What Occurs Before and After Synchronization Phases", must be handled for the remote database:
Section 3.2.4.1, "Customize Callbacks on the Remote Database"
Section 3.2.4.2, "Publication Item Level Callbacks for the MGP Apply/Compose Phases"
The Customize callbacks, as described in Section 2.7.1, "Customize What Occurs Before and After Every Phase of Each Synchronization", are created to perform defined tasks before or after any phase of synchronization.
Most of the callbacks pertain to MGP processing. Since an MGP Job executes against a database, these callbacks are invoked separately by each job against the corresponding database. Callbacks that are not related to the MGP are invoked against the MAIN database. Thus, the callback PL/SQL package must be created on the MAIN database as well as on the appropriate remote databases.
Define the MGP publication item level callbacks on the database against which the publication item is defined. Then, these can access the base tables on that database.
For full details on the MGP publication item level callbacks, see Section 2.7.2, "Customize What Occurs Before and After Compose/Apply Phases for a Single Publication Item".
When you customize the apply/compose phase for a queue-based publication item, as described in Section 2.12.1, "Customizing Apply/Compose Phase of Synchronization with a Queue-Based Publication Item", then these packages must be defined on the database where the queue-based publication item base tables exist. Thus, if the base tables exist on a remote database, then the packages must be defined on the remote database.
Publication items can be defined for database objects existing on remote databases outside of the mobile server repository. Local private synonyms of the remote objects can be created in the Oracle database. However, we recommend that you use the remote database functionality as described in Section 3.2, "Register a Remote Oracle Database for Application Data".
If you still decide to use database links for defining publication items on remote databases, then you can execute the following SQL script located in the <ORACLE_HOME>\Mobile\server\admin\consolidator_rmt.sql directory on the remote schema in order to create Consolidator Manager logging objects.
The synonyms should then be published using the createPublicationItem method of the ConsolidatorManager object. If the remote object is a view that needs to be published in updatable mode and/or fast-refresh mode, the remote parent table must also be published locally. Parent hints should be provided for the synonym of the remote view similar those used for local, updatable and/or fast refreshable views.
Two additional methods have been created, dependencyHint and removeDependencyHint, to deal with non-apparent dependencies introduced by publication of remote objects.
Remote links to the Oracle database must be established prior to attempting remote linking procedures, please refer to the Oracle SQL Reference for this information.
Note:
The performance of synchronization from remote databases is subject to network throughput and the performance of remote query processing. Because of this, remote data synchronization is best used for simple views or tables with limited amount of data.The following sections describe how to manage remote links:
The createPublicationItem method creates a new, stand-alone publication item as a remote database object. If the URL string is used, the remote connection is established and closed automatically. If the connection is NULL or cannot be established, an exception is thrown. The remote connection information is used to create logging objects on the linked database and to extract metadata.
Note:
See the Oracle Database Mobile Server JavaDoc for more information.
consolidatorManager.createPublicationItem(
"jdbc:oracle:oci8:@oracle.world",
"P_SAMPLE1",
"SAMPLE1",
"PAYROLL_SYN",
"F"
"SELECT * FROM sample1.PAYROLL_SYN"+"WHERE SALARY >:CAP", NULL, NULL);
Note:
Within the select statement, the parameter name for the data subset must be prefixed with a colon, for example:CAP.Use the dependencyHint method to create a hint for a non-apparent dependency.
Given remote view definition
create payroll_view as
select p.pid, e.name
from payroll p, emp e
where p.emp_id = e.emp_id;
Execute locally
create synonym v_payroll_syn for payroll_view@<remote_link_address>;
create synonym t_emp_syn for emp@<remote_link_address>;
Where <remote_link_address> is the link established on the Oracle database. Use dependencyHint to indicate that the local synonym v_payroll_syn depends on the local synonym t_emp_syn:
consolidatorManager.dependencyHint("SAMPLE1","V_PAYROLL_SYN","SAMPLE1","T_EMP_SYN");
Use the removeDependencyHint method to remove a hint for a non-apparent dependency.
Note:
See the Oracle Database Mobile Server JavaDoc for more information.For a view to be updatable, it must have a parent table. A parent table can be any one of the view base tables in which a primary key is included in the view column list and is unique in the view row set. If you want to make a view updatable, provide the mobile server with the appropriate hint and the view parent table before you create a publication item on the view.
To make publication items based on a updatable view, use the following two mechanisms:
Parent table hints
INSTEAD OF triggers or DML procedure callouts
Parent table hints define the parent table for a given view. Parent table hints are provided through the parentHint method of the Consolidator Manager object, as follows:
consolidatorManager.parentHint("SAMPLE3","ADDROLRL4P","SAMPLE3","ADDRESS");
See the Oracle Database Mobile Server JavaDoc for more information.
INSTEAD OF triggers are used to execute INSTEAD OF INSERT, INSTEAD OF UPDATE, or INSTEAD OF DELETE commands. INSTEAD OF triggers also map these DML commands into operations that are performed against the view base tables. INSTEAD OF triggers are a function of the Oracle database. See the Oracle database documentation for details on INSTEAD OF triggers.
If you need to manipulate the application tables to create a secondary index or a virtual primary key, you can use ConsolidatorManager methods to programmatically perform these tasks in your application, as described in the following sections:
The first time a client synchronizes, the mobile server automatically enables a mobile client to create the database objects on the client in the form of snapshots. By default, the primary key index of a table is automatically replicated from the server. You can create secondary indexes on a publication item through the Consolidator Manager APIs. See the Oracle Database Mobile Server Javadoc for specific API information. See Section 2.4.1.6, "Create Publication Item Indexes" for an example.
You can specify a virtual primary key for publication items where the base object does not have a primary key defined. This is useful if you want to create a fast refresh publication item on a table that does not have a primary key.
A virtual primary key must be unique and not NULL. A virtual primary key can consist of a single or multiple columns, where each column included in the virtual primary key must not NULL. If a NULL value is entered into any column of a virtual primary key, this results in an error. If the virtual primary key is on a single column, it must be unique; if the virtual primary key consists of a composite of multiple columns, then the composite must be unique.
If you want to create a virtual primary key for more than one column, then the API must be called separately for each column that you wish to assign to that virtual primary key.
Use the createVirtualPKColumn method to create a virtual primary key column.
consolidatorManager.createVirtualPKColumn("SAMPLE1", "DEPT", "DEPT_ID");
Use the dropVirtualPKColumns method to drop a virtual primary key.
consolidatorManager.dropVirtualPKColumns("SAMPLE1", "DEPT");
Note:
See the Oracle Database Mobile Server JavaDoc for more information.You can use schema evolution when adding or altering a column in the application tables for updatable publication items. You do not use schema evolution for read-only publication items.
If you do alter the schema, then the client receives a complete refresh on the modified publication item, but not for the entire publication.
Note:
You should stop all synchronization events and MGP activity during a schema evolution.The following types of schema modifications are supported:
Add new columns
Change the type of a column—You can only modify the type of a column in accordance to the Oracle Database limitations. In addition, you CANNOT modify a primary key or virtual primary key column
Increase the width of a column
Note:
You cannot modify the definition of any primary key or virtual primary key using this method. Instead, use the directions provided in Section 3.6.1, "Schema Evolution Involving a Primary Key".For facilitating schema evolution, perform the following:
If necessary, modify the table in the back-end Oracle database.
Modify the publication item directly on the production repository through MDW or the alterPublicationItem API. Modifying the SQL query of the publication item causes the schema evolution to occur.
A schema evolution only occurs if the SQL query is modified. If the SQL query does not change, then the evolution does not occur. If your modification only touched the table, then you must modify the SQL query by adding an additional space to force the schema evolution to occur.
Note:
If you decide to republish the application to a different repository, then update the publication definition in the packaging wizard.Once you alter the SQL query, then either use Mobile Manager to refresh the metadata cache or restart the mobile server. To refresh the metadata cache through the mobile server, select Data Synchronization->Administration->Reset Metadata Cache or execute the resetCache method of the ConsolidatorManager class.
Note:
Use of the high priority flag during synchronization will override any schema evolution, as a result, the new table definition will not come to the client.When you modify the table in the repository, the client snapshot is no longer. Thus—by default—a complete refresh occurs the next time you synchronize, because a new snapshot must be created on the client.
What if you want to perform a schema evolution that does include a modification to the primary key. Normally, you would drop the entire publication and recreate it. However, there is a way that you can modify the primary key constraint and recreate the publication item without dropping the entire publication.
The following steps describe how to remove the primary key constraint, add a new column and identify it as the primary or virtual primary key and then recreate the publication item. The steps below must be followed in the order listed:
Using MDW, remove the publication item from the publication and drop the publication item from the repository.
Modify the table in the back-end Oracle database, as described in the following steps:
Drop the Primary Key constraint. For example, if table1 has primary key constraint of pk_constraint, then drop this constraint, as follows:
alter table table1 drop constraint pk_constraint;
Add a new column to perform as the new primary key or virtual primary key, as follows:
alter table table1 add my_new_col number(5,0) not null;
Populate the new column with values that can be used (solely or as part of) the new the primary key or virtual primary key.
Alter the table to create a primary key or virtual primary key constraint on the new column. If you want to create the primary key constraint on the new column my_new_col for table1, use the ALTER TABLE SQL command. If you want to define a virtual primary key on my_new_col for table1, use MDW.
In MDW, create a new publication item for table1. This should be a duplicate of the previously dropped publication item, but with teh new column included. When creating the publication item, verify the my_new_col appears as teh primary key.
Add the publication item to the publication.
Reset the Metadata Cache using the Mobile Manager by selecting Data Synchronization -> Administration -> Reset Metadata Cache.
Verify in the Parent Table Primary Key and Base table Primary Key fields in the Publication Item detail screen in the Mobile Manager that the new primary key is in effect.
Synchronize on the existing client device to bring down the new publication.
After the synchronization is complete, then verify that the new column is present and that it is functioning as the primary key on the client device.
You can set either DBA or operational privileges for the mobile server with the following Consolidator Manager API:
void setMobilePrivileges( String dba_schema, String dba_pass, int type )
throws ConsolidatorException
where the input parameter are as follows:
dba_schema—The DBA schema name
dba_pass—The DBA password
type—Define the user by setting this parameter to either Consolidator.DBA or Consolidator.OPER
If you specify Consolidator.DBA, then the privileges needed are those necessary for granting DBA privileges that are required for publish/subscribe functions of the mobile server.
If you specify Consolidator.OPER type, then the privileges needed are those necessary for executing the mobile server without any schema modifications. The OPER is given DML and select access to publication item base objects, version, log, and error queue tables.
The mobile server privileges are modified using the C$MOBILE_PRIVILEGES PL/SQL package, which is created for you automatically after the first time you use the setMobilePrivileges procedure. After the package is created, the mobile server privileges can be administered from SQL or from this Java API.