Tuesday 21 August 2012

Example Step6:Test Synchronization

Finally, I recommend that you get a sense for how synchronization works between the Oracle Server and the device. Depending on the conflict resolution and other rules that you have set for your Publication and Publication items, it is pretty cool to see that synchronization does work! Since I had a rooted device, here is how I verified that two-way data synchronization was working as expected.

Step 1: Data Modified on Oracle Server
  • I logged in to the database as user AA using a SQL client
  • Issued a simple update to change one record in the ITEMS table
SQL> UPDATE items SET ITEM_DESCRIPTION = 'Sorry Grass is dark Green' WHERE ITEM_ID = 10;
SQL> COMMIT;

On my device, I fired up the Mobile Sync application and clicked 'Sync' and the verified that the data did get refreshed on my device.  Check out the screenshots provided if you are not sure how to do this.


Step 2: Data Modified on Device
  •  Use SQLite client on the device to modify a record using an UPDATE statement
  • Perform a manual sync and you should see the modification on the Server-side. I have provided a screenshot

    Download Screen Shots

Example Step5:Demo Application

Now that we have installed DMS and set up the mobile sync client along with couple of tables, we can now look at a simple Titanium application.

Note: The sample application that I use is adapted from a Titanium database example shared by Hammer! In his/her example, Hammer creates the two database tables CATEGORIES and ITEMS using the FireFox add-on SQLite Manager and includes it as a file in the Titanium project. Since the tables are already available on the device in our case, we will ignore this step. I also have made a couple of changes to the sources. Instead of using the method Titanium.Database.install, we will use the method Titanium.Database.open since the database is already available on External Storage (SD Card) and is not in the ApplicationDataDirectory in internal memory

Step 1: Create a New Titanium Project
  • Create a new Mobile project in Titanium. I named my project DB_Test and set the Application ID to net.adkoli.dbtest (this must relate to what you set for DATA_DIRECTORY in OSE.INI; please pay extra attention to this)
Step 2: Replace application files
  • Use instructions in Step 4 of Hammer's sample to replace app.js and few other files of the new Titanium project created in Step 1 above.
  • Click File->Refresh
Step 3: Edit app.js for our needs
  • Comment the line initializing the database. So, comment the line:
Titanium.Database.install('content.sqlite','contentDB');

This is because we will be using the Ti.Database.open method to open the database stored in external storage 

Step 4: Edit tab_categories.js
  • There are two database execute calls in this file. The first one is to fetch records from the CATEGORIES table and the second one is to fetch records from the ITEMS table.
  • In the section "populate category array from database", add a statement to open the database that we have installed on the device. Comment the line 
 var db = Titanium.Database.open('contentDB');

 in the existing tab_categories.js file provided by Hammer.

Replace it with the following:

if (Ti.Platform.name === 'android' && Ti.Filesystem.isExternalStoragePresent()) {
     var db = Titanium.Database.open('/sdcard/net.adkoli.dbtest/AA/TEST.db');
    }

Be sure to modify the path and database name to match the location of your database.

Make the same change in the function(showItemsInCategory) in this file.

Step 5: Build the Application and Install

  • Build the application and run in the Emulator to ensure that there are no errors
  • Then connect the Android device via the USB port and use the option "Install to Android Device"
  • Pay close attention to the log window. If all goes well you should see a confirmation that application was installed on device and that you can launch it
  • Launch DB_Test or whatever you named your app and you should see the sample application with data.
You can download a copy of my app.js and tab_categories.js files

Application Screen Shots

My Experience

  1. I have hardcoded the path to the database in the Ti.Database.open method because I could not get my code with system variables and calls to Ti.Filesystem.externalStorageDirectory working. Ideally, we should be using something like:
var db = Ti.Database.open(Ti.Filesystem.externalStorageDirectory + 'AA' + Ti.Filesystem.separator + 'TEST.db'). I kept getting an error "contains a path separator". If someone gets this going, please share your comments


Monday 20 August 2012

Example Step4: Download Application onto Client

Thus far, have deployed an application named TestApp containing the Publication TEST with Publication items CATEGORIES and ITEMS. Access has been given to the user AA using Mobile Manager to this application. We will now install this application on to the device. By doing so, a SQLite database is created on the device containing two tables CATEGORIES and ITEMS.

Step 1: Install necessary applications
  • Ensure that your device can install non-marketplace applications. Go to Settings, Applications and ensure that "Unknown Sources" is selected.
  • Visit the URL <Mobile Server>:<port>/mobile/setup from the browser on your device. I used http://192.168.0.17:8080/mobile/setup
  • Choose SQLite Android and download it to your device. You should see a file named Setup.apk. Install this app. Provide the credentials for the user AA when prompted along with URL to the Mobile Server (use http:// in the URL, else it won't work)
  • You will automatically move into the installation of the next application named Oracle DMAgent
  • On one of the screens, you will provide information on the device for registration (I could not change the name of the device - not sure why)
  • At the end of this step, you should see three applications named Oracle Setup, Oracle DMAgent and Oracle Update installed on your device.
Update Screen Shots

Step 2:  Install the TestApp onto device
  • Launch Oracle Update and provide the credentials for user AA along with the URL to the Mobile Server
  • You should see a screen showing TestApp available as an update. Choose to install this by touching the name of the application
  • You will see confirmation that application has been installed
TestApp Screen Shots

Step 3: Validate 
  • Launch Mobile Sync application and click Sync
  • View the OSE.INI file. You should see some additional entries like QUEUES and LIMIT_CONNECTIONS
  • Use adb to view listing of the files on the SD Card
Validation Screen Shots

Example Step3: Publish Test Application

Now that the Mobile Sync application is installed on the device, we can test out the synchronization by pushing and pulling some data to/fro Oracle database and device.

Step 1: Create Sample Schema and Tables in database

We will create a new user (or 'schema' if you prefer) in the Oracle database.
  • Log in to Oracle with DBA user and use CREATE USER, GRANT etc to create a user. I created a user named AA (note that this is a database user and not a user on the DMS. I just happened to use the same name for both).
  • For the purposes of my example, I created two tables CATEGORIES and ITEMS in this schema. You can download the SQL script to create these
Step 2: Create Publication and Publication items
  • On the client development machine, launch Oracle Database Workbench
  • Create a New project named TEST
  • Create a New Publication named TEST
  • Create two New Publication Items named CATEGORIES and ITEMS using the wizard. I selected all columns of my tables CATEGORIES and ITEMS.
  • Select the publication TEST and add the two publication items CATEGORIES and ITEMS
  • Save the Project and Deploy (File-> Deploy). You can view this publication and items in the repository via Mobile Manager to double-check
  • Close MDW

    MDW Screen Shots
Step 3: Use Packaging Wizard to package an application
  • Launch Packaging Wizard, connect to repository
  • Create a new Application Definition called TestApp
  • Choose the platform SQLite Android;US from the list
  • Provide credentials to connect to repository (the database URL will be something like jdbc:oracle:thin:@192.168.0.17:1521:orcl)
  • Choose the publication TEST created in Step 2
  • Click Finish to publish the application
 Packaging Wizard Screen Shots

Step 4: Grant Access to Application
  • Log in to Mobile Manager
  • Click Applications Tab under Mobile Servers. You should see the application TestApp listed
  • Click on the Application and navigate to the Access tab
  • Choose the user AA and grant access to TestApp
Grant Screen Shots

Example Step2: Install Mobile Sync & complete first sync

Note: I have shared screen shots of these steps in a zip file at the bottom of this blog. File names of the screen shot are in parentheses in the discussion below

Step 1: Install Oracle Mobile Development Kit

Launch Oracle Installer and choose to install Mobile Development Kit. I believe Oracle does not support this installation on the same machine as the Mobile Server. I installed Oracle Mobile Development Kit on Windows XP in a VM using default options.

Step 2: Install Eclipse, Android SDK
Step 3: Build Mobile Sync application
  • Launch Eclipse
  • Import simple_sync_android application (File-> Import)
  • Choose the option Import Existing Projects into Workspace
  • Ensure that MOBILE_SYNC_ANDROID_LIB is configured properly (Project Properties -> Java Build Path). In my case, I set it to C:/OraHome_1/Mobile/Sdk/android/lib/osync_android.jar
  • Create an Android Virtual Device (Window->AVD Manager)
  • Set default Build Target to Android 2.3.3 (Project Properties, choose Android and check the default target)
  • Build and run the application in the Android Emulator (Run-> Run As)
  • You should see the Mobile Sync application in your Android Emulator. If you get a ClassNotFoundException, or Force Close, see below  
 Step 4: Install Mobile Sync application on device
  •  If you were able to build the Mobile Sync application successfully and it shows up in the Emulator, then attach your Android device to the PC with USB port. 
  • Ensure that USB driver is installed 
  • Install the application onto device Run As-> with the device connected. You should see the Mobile Sync application on the device (Sync1.png)

    Caution: Do not log in to Mobile Server as yet
  • Launch Mobile Sync application and configure the setting DATA_DIRECTORY. Click the Menu button to see options to Edit OSE.INI, View Error log etc.(Sync2.png)
  • Edit the OSE.INI file. You should see no entries in OSE.INI as yet (Sync3.png)

    Add an entry similar to: SQLITE.DATA_DIRECTORY=/sdcard/net.adkoli.dbtest
    and click Save.



    In the entry above, /sdcard is the directory for external storage (also called SD card). The rest of the entry "net.adkoli.dbtest" matches the name of my mobile application created in Titanium. More on this later. 
  • Now enter User credentials and Server URL. In my example, I had created a user AA (Sync4.png)
  • Click Sync. After a few seconds, you should see message "Sync finished successfully" (Sync5.png)
Note: Detailed instructions are available in a file named "Creating Sync Application for Android" in the \mobile\sdk\samples\sync\android\simple_sync_android folder from the installation completed in Step 1 above. 

Step 5: Sanity Check
  • Use adb to check if the directory structures expected are created. You should see a directory with username appended to it. So, in my example, you would see a directory such as /sdcard/net.adkoli.dbtest/AA.

My Experience

I had a difficult time building the simple_sync_android project out of the box. I kept getting an application Force Close and ClassNotFoundException. I had to do the following to workaround this:
  1. In Eclipse, go to Project Properties
  2. Choose Java Build Path
  3. Click Order and Export tab
  4. Move "MOBILE_SYNC_ANDROID_LIB" entry Up until it is just below the /simple/sync_android/src entry
  5. Now rebuild and this worked for me
Other errors I saw were "HTTP Transport error" while I was trying to Sync. This is because my device had lost network (Sync6.png)

I also was not able to get Mobile Sync to Save the password when I chose the option to do so (checkbox Save Password). The password is somehow saved incorrectly and I kept getting "USER_INVALID" error (Sync7.png). I suspect that the Sync client is encrypting the password provided and then using the mangled version of the password without decrypting again.

Screen Shots

I have made available screenshots to help you follow this blog. Please download zip file and extract it. The names of the files referenced are in parentheses.

Example Step1:Create User on DMS

The general idea is that one mobile client (or device) will be used by one individual user. There might be more complex environments, but in my sample deployment, I will create a user named AA on the Mobile Server and publish my test application to this user. The same application will then be installed and used on the mobile device. In this step, I will share the steps to creating this user.

Log In to Mobile Manager

Visit Mobile Manager via a browser. In my case, I pointed my browser to http://192.168.0.17:8080/mobile and logged in as Administrator.

Create a New User

I navigated to the User Manager section and used the "Create Like" feature to create a new user AA like an Administrator as illustrated below.







Sunday 19 August 2012

Illustrated Example - 6 Steps

I will share my experience of deploying a sample application. I will break the discussion into six separate blogs as follows:
  1. Create User on DMS 
  2. Install Mobile Sync application on client and complete first sync
  3. Publish a Test Application on DMS with Publication and Publication Items to user created in Step 1
  4. Download Application onto Client
  5. Install Demo Mobile Application on device
  6. Test synchronization and mobile application
Note: I have created a separate development environment for this discussion. What I describe here don't necessarily represent best practices or my advice for a production deployment!
Prerequisite: Oracle Database Mobile Server 11g is installed and running

Understanding the Mobile Client

There is a lot of documentation provided by Oracle, and I spent considerable time reading these. Despite the long hours that I spent reading the docs, it took me several days to understand how the mobile client hangs together in the whole architecture. For example, it took me ages to understand that the instructions provided in Chapter 4 of the Oracle Database Mobile Server Mobile Client Guide are vital to set up the mobile client. It took me over two weeks to understand exactly how the mobile application will "address" or "find" the database.  I am used to Connect Strings, JDBC connections etc.. in my legacy applications. But for the life of me I could not figure out how my mobile application will open a connection to the database on the device!

Let me take a stab at providing my own view of the mobile client. In upcoming blogs I will also provide information on how to do all of this :-)

I suggest that the mobile client be viewed as two separate pieces. The first piece called "Mobile Sync" is required to create the directory structure of the Mobile Client on the Android device. The second piece, and I will call it the Oracle Update piece, really helps to pull applications and updates from the Oracle Mobile Server. 

Let me elaborate on some of the points that confused me for academic reasons.

How will the Oracle Mobile Server sync publications and publication items with a database on the device? Exactly, which database does it sync to? How do the tables associated with the Publication and items get onto the device?

Suggested Reading: Storage Options on Android

Basically, the Oracle Mobile Sync application is the first piece of the puzzle. You must build this application and install it on the Android Device. When this application is run the first time, it creates a directory structure in internal storage on the device under the /data/data directory structure. If you really want to go and take a peek at this, you can do so with adb. I even went to the extent of rooting an old phone that I had to get a full understanding of what was going on (rooting a phone allows you superuser access - so you can see everything and have complete control of your device. Just search on the web for 'how to root android phone' and you will get information). 

I found that the Mobile Sync application created a structure /data/data/tests.sync and then created some configuration files like ose.ini and default SQLite database default.db under this path in /data/data/tests.sync/app_oracle.sync

This led me to my next roadblock. If the Mobile Sync application puts the database in its Application directory in internal storage, how will my application be able to read this database? Considering that Android 'sandboxes' applications and their files, I could not understand how my application would connect to this database sitting in the Mobile Sync's application directory. 

The simple answer. You can control the location of the database on the Mobile device by configuring a file named OSE.INI on the device. So all one has to do is point a parameter DATA_DIRECTORY to a common location that can be 'seen' by both the application and the sync client - most likely on external storage (SD card) to get over space limitations in internal storage.

Again, Oracle documentation is unclear on how the OSE.INI gets edited. Since I had a rooted phone, I used adb pull and adb push to replace the old OSE.INI file with my version. Turns out that the OSE.INI file can be edited from the Mobile Sync client itself! So, here is what one needs to do to install Mobile Sync and then 'point' it to a common storage area.

Step 0: Set up Oracle Database Mobile Server and a new user
Step 1: Import the simple_sync_android application into Eclipse
Step 2: Build Mobile Sync Application and install it on device
Step 3: Edit OSE.INI file and point DATA_DIRECTORY to a directory on external storage
Step 4: Connect to DMS using the user credentials created in Step 0 and sync to create the basic directory structures used by the Mobile Client.

Now that the database is created and available in External Storage, you can address it from your mobile application.

I have described how the Mobile Sync piece works thus far. Let us now look at the second piece - the Oracle Update.
Oracle Update application allows the device to pull publications, publication items and applications onto the device. For Android, you can point your browser on the device to <mobile server>/mobile/setup and install the appropriate software. You will first install an application named Oracle Setup. You will be automatically made to install two other applications named Oracle DMAgent and Oracle Update. 

When you launch Oracle Update and connect to the DMS, you can see any new application update (including publication and publication items) made available to you. So, every time you update, new applications, upgrades, publications and publication items will get on to your device.

Finally, another point that was not quite clear to me was how to install or package the mobile application itself to install on device. You really have two choices. The first method is to simple push your application (APK file) on to the device using Eclipse or Titanium (in my case). The second method is to bundle the application and publish through the DMS. 

Now for most readers who have come this far, it might still not be clear as to how all this hangs together. I recommend that you review my next 2-3 posts where I will walk you through a sample deployment and then return to this blog to get a complete understanding.



Friday 17 August 2012

Installation of Oracle Database Mobile Server 11g

Prerequisites: Install Oracle Database 11g

Note: Screenshots from my installation are available for your reference at the bottom of this note.

Step 1: Download Application Server.
I chose Oracle GlassFish Server 3.1.2.

Step 2: Set up environment for GlassFish
Since I already had Oracle Database installed, I set the environment for a different Oracle Home.

export ORACLE_HOME=/home/oracle/glassfish/glassfish3
export JAVA15_HOME=$JAVA_HOMe

(please read README before you start!)

Step 3: Installed GlassFish
Execute the install script provided. In my example,
$glassfish-3.1.2-unix.sh

I took the default install options and installed the GlassFish server on Port 4848

Step 4: Download and install DMS

I downloaded DMS for Linux. There are separate 32-bit and 64-bit installations. So make sure you choose the right one.

I used the same environment as GlassFish. Fired up the Oracle Installer (runInstaller) and installed the Oracle Database Mobile Server. I chose the option to not create repository and to not install the demo applications.

Step 5: Create Mobile Repository

Run $ORACLE_HOME/mobile/server/repwizard to bring up Repository Wizard. Follow instructions to create repository. You will need the GlassFish Port Number, Admin credentials and credentials for the Oracle SYSTEM user.

I chose the default installation schema MOBILEADMIN and created a new domain for the DMS.

Step 6: Start Domain

My main GlassFish domain is called domain1. There is no need to start the mobile server domain separately

$ORACLE_HOME/bin/asadmin start-domain domain1

Step 7: Log in to Mobile Manager

Verify that GlassFish Server is running. Visit <app server>:<port>. My URL is http://192.168.0.17:8080

From a browser, visit <app server>:<port>/mobile/console. For me, this URL looks like http://192.168.0.17:8080/mobile/console

Use the credentials for the Administrator to log in. If you cannot log in or get any error, you will need to figure out what went wrong by looking at installation logs.

How to Uninstall?


If you want to uninstall DMS and start over, here are the steps:

Log in to Oracle using SYSDBA and execute:
SQL> DROP TABLESPACE SYNCSERVER INCLUDING CONTENTS;

SQL> DROP USER MOBILEADMIN CASCADE

From OS prompt, delete the directory structure $ORACLE_HOME/mobile

$rm -rf mobile

Delete the Mobile Server domain from $ORACLE_HOME/domains (delete the mobile server domain and not the GlassFish main domain)

$rm -rf mobileserver

My Experience


All of the above went very smooth once I had Sun JDK installed and JAVA15_HOME and ORACLE_HOME set properly.

Since my DMS is running on a Linux Desktop, I did observe one strange behavior. When my Desktop went down abnormally due to a power failure, I could no longer log in to Mobile Manager using the Administrator credentials. I kept getting an error Invalid Username/Password or something to that effect. I could not figure out why. My database was running fine and I could log in to MOBILEADMIN from SQL client and even reach my GlassFish console. So there is some issue here that I have not figure out as yet. I have reported it to the Oracle team.

I finally reinstalled DMS and recreated the repository to get around this problem. Hopefully, we will get a solution for this issue

Screen Shots


I have made available four sets of screeshots for reference.

1) Oracle GlassFish Server Installation

2) Oracle Database Mobile Server Installation

3) Oracle DMS Repository Creation

4) Managing Domain and Mobile Manager 




Acknowledgements

I want to acknowledge the time and patience of Eric Jensen, Michal Brey and others from Oracle DMS team. They filled the gaps in the Oracle documentation and also responded readily to my queries.

The Titanium Tutorial - Tableview from a Database by "Hammer" at http://blogs.usask.ca/the_bolt/archive/2010/12/titanium_tutorial_database.html
It helped me understand how Titanium access SQLite database. I also have modified this application just a little bit to get it working in my sample application.

 Now to some business.

Pieces of the Puzzle

A quick explanation of the pieces of the puzzle is provided here.

Server-Side:

Server basically has an Oracle 11g database and Oracle Database Mobile Server running on GlassFish (App server). As stated in the Oracle DMS documentation, "Oracle Database Mobile Server provides a complete mobile infrastructure designed to run enterprise database applications for clients using either the Berkeley DB or SQLite databases." The features allow us to (there are many other features!):

- Create and Manage Users
- Manage Security; who has access to applications, data etc.
- Manage Devices; handle lost devices etc.
- View "subscriptions to data" etc. (referred Publication and Publication items in Oracle doc)

On Desktop:

I have installed Oracle Mobile Development Kit. It gives us Mobile Database Workbench (MDW). One can create and manage publications items using MDW. Packaging Wizard allows us to publish applications to the Mobile Server.

Note that a mobile repository is created under the schema MOBILEADMIN (default) during installation.

Eclipse is an IDE that we will use to build the Mobile Sync application bundled with the Mobile Development Kit.

Finally, I have chosen to use Titanium Studio  to build my application since I have limited knowledge and experience of Java.