Using the Local Database with Rhom

Rhom is a mini database object mapper for Rhodes. It provides a high level interface to make it very powerful and simple to use a local database. That database is SQLite on all platforms except BlackBerry where it is HSQLDB.

Rhom currently supports two model types: Property Bag (default) and Fixed Schema

• Property Bag
• Fixed Schema
• Data Migrations
• Rhom API
• Associations
• Freezed models
• Accessing Sync Info with RhomSource
• Resetting the Database
• Seeding the Database
• Importing and exporting the database
• Advanced Queries
• Find by numeric field
• Database Encryption
• Performance Tips

Property Bag

With a property bag model, all data is stored in a single table using the object-attribute-value pattern also referred to as the Entity-attribute-value model.

Property Bag Advantages

• Simple to use, it doesn’t require specifying attributes.
• Data migrations are not necessary.
• Attributes can be added or removed without modifying the database schema.

Property Bag Disadvantages

• For some applications, the database size may be significantly larger than fixed schema. This is because each attribute is indexed for fast lookup.
• Sync process may be slightly slower because inserts are performed at attribute level.

In a property bag model, Rhom groups objects by their source id and object id. The following example illustrates this idea:

Source ID: 1, Model Name: Account
+-----------+----------+--------------+----------------------+
| source_id | attrib   | object       | value                |
+-----------+----------+--------------+------- --------------+
|         1 | name     | 48f39f63741b | A.G. Parr PLC 37862  | 
|         1 | industry | 48f39f63741b | Entertainment        |
|         1 | name     | 48f39f230529 | Jones Group          |
|         1 | industry | 48f39f230529 | Sales                |
+-----------+----------+--------------+----------------------+

Here, Rhom will expose a class Account with two attributes: name and industry

account = Account.find('48f39f63741b')
account.name
  #=> "A.G. Parr PLC 37862"

account.industry
  #=> "Entertainment"

Using Property Bag Models

To use a property bag model, simply generate a new model with some attributes:

$ rhodes model product name,brand,price,quantity,sku

This will generate a file called product.rb which looks like:

class Product
  include Rhom::PropertyBag

  # Uncomment the following line to enable sync with Product.
  # enable :sync

  #add model specific code here
end

There are several features you can enable or disable in the model, below is a complete list:

class SomeModel
  include Rhom::PropertyBag

  # rhoconnect settings
  # Enable sync for this model.
  # Default is disabled.
  enable :sync 

  # Set the type of sync this model
  # will use (default :incremental).
  # Set to :bulk_only to disable incremental
  # sync and only use bulk sync.
  set :sync_type, :bulk_only 

  # Set the sync priority for this model.
  # 1000 is default, set to lower number
  # for a higher priority.
  set :sync_priority, 1     

  # Instruct Rhom to send all attributes
  # to RhoConnect when an object is updated.
  # Default is disabled, only changed attributes
  # are sent.
  enable :full_update 

  # RhoConnect provides a simple way to keep data out of redis. 
  # If you have sensitive data that you do not want saved in redis, 
  # add the pass_through option in settings/settings.yml for each source.
  # Add pass_through to client model definition
  enable :pass_through 

  # model settings

  # Define how data is partitioned for this model.
  # For synced models default is :user. 
  # For non-synced models default is :local
  # If you have an :app partition
  # for your RhoConnect source adapter and use bulk sync,
  # set this to :app also.
  set :partition, :app

  # Define blob attributes for the model.
  # :blob           Declare property as a blob type
  #
  # :overwrite      (optional) Overwrite client copy 
  #                 of blob with new copy from server.
  #                 This is useful when RhoConnect modifies
  #                 images sent from Rhodes, for example 
  #                 zooming or cropping.
  property :image_url, :blob, :overwrite 

  # You can define your own properties also
  property :mycustomproperty, 'hello'
end

Fixed Schema

With a fixed schema model, each model has a separate database table and each attribute exists as a column in the table. In this sense, fixed schema models are similar to traditional relational tables.

Fixed Schema Advantages

• Smaller database size, indexes can be specified only on specific attributes.
• Sync process may perform faster because whole objects are inserted at a time.

Fixed Schema Disadvantages

• Schema changes must be handled with data migrations.
• Database performance may be slow unless you specify proper indexes.

Using Fixed Schema Models

Using a fixed schema model involves an additional step to using a property bag model.

First, generate the model using the rhodes command:

$ rhodes model product name,brand,price,quantity,sku

Next, change the include statement in product.rb to include Rhom::FixedSchema and add the attributes:

class Product
  include Rhom::FixedSchema

  # Uncomment the following line to enable sync with Product.
  # enable :sync

  property :name, :string
  property :brand, :string
  property :price, :string
  property :quantity, :string
  property :sku, :string

  property :int_prop, :integer
  property :float_prop, :float
  property :date_prop, :date #translate to integer type
  property :time_prop, :time #translate to integer type

end

That’s it! Now your model is a fixed schema model, the table will be generated automatically for you when the application launches.

Below is a full list of options available to fixed schema models:

class SomeModel
  include Rhom::FixedSchema

  # rhoconnect settings
  # Enable sync for this model.
  # Default is disabled.
  enable :sync 

  # Set the type of sync this model
  # will use (default :incremental).
  # Set to :bulk_only to disable incremental
  # sync and only use bulk sync.
  set :sync_type, :bulk_only 

  # Set the sync priority for this model.
  # 1000 is default, set to lower number
  # for a higher priority.
  set :sync_priority, 1     

  # Instruct Rhom to send all attributes
  # to RhoConnect when an object is updated.
  # Default is disabled, only changed attributes
  # are sent.
  enable :full_update 

  # RhoConnect provides a simple way to keep data out of redis. 
  # If you have sensitive data that you do not want saved in redis, 
  # add the pass_through option in settings/settings.yml for each source.
  # Add pass_through to client model definition
  enable :pass_through 

  # model settings

  # Define how data is partitioned for this model.
  # Default is :user.  If you have an :app partition
  # for your RhoConnect source adapter and use bulk sync,
  # set this to :app also.
  set :partition, :app

  # Set the current version of the fixed schema.
  # Your application may use it for data migrations.
  set :schema_version, '1.0'

  # Define fixed schema attributes.
  # :string and :blob types are supported.
  property :name, :string
  property :tag, :string
  property :phone, :string
  property :image_url, :blob

  # Define a named index on a set of attributes.
  # For example, this will create index for name and tag columns.
  index :by_name_tag, [:name, :tag] 

  # Define a unique named index on a set of attributes.
  # For example, this will create unique index for the phone column.
  unique_index :by_phone, [:phone] 

  # Define blob attributes for the model.
  # :blob           Declare property as a blob type
  #
  # :overwrite      (optional) Overwrite client copy 
  #                 of blob with new copy from server.
  #                 This is useful when RhoConnect modifies
  #                 images sent from Rhodes, for example 
  #                 zooming or cropping.
  property :image_url, :blob, :overwrite

  # You can define your own properties also
  property :mycustomproperty, 'hello'
end

Data Migrations

Fixed Schema

Rhom provides an application hook to migrate the data manually. You can also use this hook to run business logic related to updating the database. For example, your application may want to display a customized alert notifying the user that a migration is in progress and it may take a few moments.

To use this hook, first we need to track the :schema_version in our model:

class Product
  include Rhom::FixedSchema

  set :schema_version, '1.1'
end

Next, we will implement the following hook in our application.rb class:

on_migrate_source(old_version, new_src)

This is called on application start when :schema_version has changed.

class AppApplication < Rho::RhoApplication

  # old_version     String containing old version value (i.e. '1.0')
  # new_src         Hash with source information:
  #                 'schema_version', 'name', 'schema'
  #                 new_src['schema']['sql'] contains new schema sql
  def on_migrate_source(old_version, new_src)
    # ... do something like alert user ...

    db = Rho::RHO.get_src_db(new_src['name'])
    db.execute_sql("ALTER TABLE #{new_src['name']} ADD COLUMN mytest VARCHAR DEFAULT null")

    true # does not create table
  end
end

Return false to run the custom sql specified by the new_src[‘schema’][‘sql’] string:

def on_migrate_source(old_version, new_src)
  # ... do something like alert user ...
  false # create table by source schema - useful only for non-synced models
end

Property Bag

No data migration required, since all attributes are dynamic. If you want to remove all local data when upgrading to new application version: change app_db_version in rhoconfig.txt.

This scenario will work for Property Bag and Fixed Schema models.

Rhom API

Below is the full list of links to the Rhom API methods available to Rhom models.

• client_id - Returns the current sync client id.
• clear_notification - Clear notification for the object.
• database_export - Creates a zip archive of a local database partition with all its blob objects, and returns a path to that zip archive.
• database_import - Imports the database and blob objects from a zip archive created with database_export.
• delete_all - Delete all Rhodes model objects for a source, filtering by conditions.
• destroy - Destroy a Rhodes model object.
• find - Find Rhodes model objects.
• find_all - Alias for modelname.find(:all, argument list).
• find_by_sql - Returns Rhodes model object(s) based on sql_query. This method works only for schema models.
• new - Create a new Rhodes model object.
• create - Create a new Rhodes model object and save it to the database.
• paginate - Call find with a limit on the number of records.
• sync - Start the sync process for a Rhodes model.
• can_modify - Returns true if the Rhodes model object is not currently being synced (if it is being synced, you should disable editing of the object).
• changed? - Returns true if a Rhodes model object has local database changes that need to be synchronized, false otherwise.
• have_local_changes? - Returns true if any of the Rhodes model objects have local database changes that need to be synchronized, false otherwise.
• save - Saves the current Rhodes model object to the database.
• set_notification - Set a notification to be called when the sync is complete for this model.
• update_attributes - Updates the current Rho model object attributes and saves it to the database.
• database_fullclient_reset_and_logout - Reset the Rhodes model database and logout.
• database_full_reset - Deletes all records from the property bag and model tables.
• database_full_reset_and_logout - Perform a full reset, then logout the RhoConnect client.
• database_local_reset - Reset only local (non-sync-enabled) models.
• metadata - Returns the metadata definition for a given model as a hash.
• metadata=(metadata_def) - Assigns the JSON metadata definition for a given model.

Rhom Delete Method Examples

Deletes all rhom objects for a source, optionally filtering by conditions:

# :conditions   Delete only objects matching these criteria. 
#               Supports find() conditions.
# :op           See advanced find syntax
Account.delete_all(:conditions => {'industry'=>'electronics'})

Delete a rhom object.

@account = Account.find(:all).first
@account.destroy

Rhom Find Method Examples

Find Rhom objects.

acct = Account.find "3560c0a0-ef58-2f40-68a5-48f39f63741b"

acct.name 
  #=> "A.G. Parr PLC 37862"

accts = Account.find(:all, :select => ['name','address'])

accts[0].name 
  #=> "A.G. Parr PLC 37862"

accts[0].telephone 
  #=> nil

Order Examples

The :order argument for find accepts several forms.

:order by one attribute.

        @accts = Account.find(
      :all, 
      :order => 'name',
      :orderdir => 'DESC'
    )

:order by one attribute with a block.

        @accts = Account.find(:all, :order => 'name') do |x,y|
      y <=> x    
    end

:order with a block.

        @accts = Account.find(:all) do |item1,item2|
      item2.name <=> item1.name
    end

:order by multiple attributes.

        @accts = Account.find(
      :all, 
      :order => ['name', 'industry'], 
      :orderdir => ['ASC', 'DESC']
    )

find_by_sql(sql_query) returns rhom object(s) based on sql_query. This method works only for schema models.

@accts = Account.find_by_sql("SELECT * FROM Account")

Rhom New and Create Method Examples

Create a new rhom object and assign given attributes.

@account = Account.new(
  {"name" => "ABC Inc.","address" => "555 5th St."}
)
@account.name 
  #=> "ABC Inc."

Create a new rhom object and save to the database.

@account = Account.create(
  {"name" => "some new record", "industry" => "electronics"}
)

Rhom Paginate Method Example

Paginate calls find with a limit on the # of records. This emulates rails' classic pagination syntax. Default page size is 10.

Account.paginate(:page => 0) 
  #=> returns first 10 records
Account.paginate(:page => 1, :per_page => 20) 
  #=> returns records 21-40
Account.paginate(
  :page => 5, 
  :conditions => {'industry' => 'Technology'}, 
  :order => 'name'
) #=> you can have :conditions and :order as well

Rhom Sync and set_notification Method Example

Start the sync process for a model. In this example, the value for @params[“sku”] – the value of the sku – must be URL encoded.

Product.sync( url_for(:action => :sync_callback), "", false, "query=#{@params["sku"]}" )

Set a notification to be called when the sync is complete for this model. This is useful for example if you want to refresh the current list page or display an alert when new data is synchronized. See the sync notification docs for more information.

Account.set_notification( url_for(:action => :sync_notify) )

Rhom update_attributes Method Example

Update the current rhom object’s attributes and saves it to the database.

@account = Account.find(
  :all, 
  :conditions => {'name' => 'ABC Inc.'}
)
@account.update_attributes(
  {"name" => "ABC Inc.", "industry" => "Technology"}
)
@account.industry   
  #=> "Technology"

Saves the current rhom object to the database.

@account = Account.new(
  {"name" => "some new record", "industry" => "electronics"}
)
@account.save

Rhom can_modify Method Example

Before displaying an edit page for an object, your application can check if the object is currently being accessed by the sync process. If it is, you should disable editing of the object. can_modify could return true, for example, on a new local record that was created and sent to the RhoConnect application, but no response has been received yet.

def edit
  @product = Product.find(@params['id'])
  if @product && !@product.can_modify
    render :action => :show_edit_error
  else    
    render :action => :edit
  end
end

Rhom changed? Method Example

Determine if a rhom model has local database changes that need to be synchronized.

def should_sync_product_object
  if Product.changed?
    #... do stuff ...
  end
end

Rhom metadata Method Examples

Returns the metadata for a given model.

Product.metadata
#=> {'foo' => 'bar'}

Assigns the metadata for a given model.

Product.metadata = { 'foo' => 'bar' }.to_json

Associations

Rhom does not support Ruby on Rails associations. However, Rhom has a sync association called belongs_to which you can use to trigger updates on sync-enabled models.

Sync Associations

For sync-enabled models, Rhom has a belongs_to sync association as a means to automatically trigger sync updates for dependent objects. This is useful where you have relationships between backend service objects.

For example, you can have a list of customers who have purchased a product, and thus you can have them belong to that product:

class Customer
  include Rhom::PropertyBag

  # Declare container model and attribute.
  belongs_to :product_id, 'Product'
end

In your product_controller.rb, assign the belongs_to attribute when a product is created:

def create
  @product = Product.new(@params['product'])
  @product.save

  cust = Customer.find(:first) # find the customer
  cust.product_id = @product.object
  cust.save
  redirect :action => :index
end

You can also define polymorphic sync associations, or sync associations across multiple classes.

Using array notation:

belongs_to :parent_id, ['Product', 'Cases']

Or multiple declarations:

belongs_to :parent_id, 'Product'
belongs_to :parent_id, 'Cases'

If you are planning to use bulk sync feature for your associated models, then you should take into consideration corresponding support on RhoConnect server side. See RhoConnect Bulk Sync associations.

Freezed models

If you want to limit model attributes by specific list - you can ‘freeze’ model:

class Customer
    include Rhom::PropertyBag

    enable :sync

    set :freezed, true

    property :address, :string
    property :city, :string
    property :email, :string
end

For such models if you try to add not listed property - you will get ArgumentError exception:

obj = Customer.new( :wrong_address => 'test') #will raise ArgumentError exception
obj = Customer.create( :wrong_address => 'test') #will raise ArgumentError exception

obj = Customer.new
obj.wrong_address = 'test' #will raise ArgumentError exception

obj = Customer.new
obj.update_attributes(:wrong_address => 'test') #will raise ArgumentError exception

Accessing Sync Info with RhomSource

Rhom exposes sync information as a RhomSource object. You can use this information for alerts, status pages, etc.

To access a RhomSource, load it by name:

@source = RhomSource.find('source_name')

Then you can get statistics and information about the source, such as the name.

@source.name
  #=> "Product"

Here are the available statistics, from the rhomsource API.

• find - Returns a RhomSource object.
• source-id - Returns the id number of a source.
• name - Returns the name of the source.
• last_updated - returns the last time the source was successfully synchronized (in Time.at format).
• last_inserted_size - returns the number of records inserted on last sync.
• last_deleted_size - returns the number of records deleted on the last sync.
• last_sync_duration - returns the duration in milliseconds of the last sync for this source.
• last_sync_success - returns 1 if last sync was successful, 0 if it failed.
• distinct_objects - returns the number of records for this source.

This example shows the formatted time for the Product model.

RhomSource.find(
  Product.get_source_name
).last_updated.strftime("%m/%d/%Y, %I:%M%p")
  #=> "01/19/2011, 06:40PM"

Resetting the Database

Rhodes provides the following functions for recovering the database from a bad or corrupt state, or if the RhoConnect server returns errors.

Rhom::Rhom.database_full_reset(reset_client_info=false, reset_local_models=true)

Deletes all records from the property bag and model tables.

# reset_client_info     If set to true, client_info 
#                       table will be cleaned.
#
# reset_local_models    If set to true, local(non-synced models) 
#                       will be cleaned.
Rhom::Rhom.database_full_reset(false,true)

Rhom::Rhom.database_full_reset_and_logout

Perform a full reset and then logout the RhoConnect client.

Rhom::Rhom.database_full_reset_and_logout

Rhom::Rhom.database_fullclient_reset_and_logout

Equivalent to Rhom::Rhom.database_full_reset(true) followed by SyncEngine.logout.

Rhom::Rhom.database_fullclient_reset_and_logout

if @params['error_message'].downcase == 'unknown client'
  puts "Received unknown client, resetting!"
  Rhom::Rhom.database_fullclient_reset_and_logout
end

Rhom::Rhom.database_local_reset

Reset only local(non-sync-enabled) models.

Rhom::Rhom.database_local_reset

Rhom::Rhom.database_full_reset_ex( :models => [model_name1, model_name2], :reset_client_info=>false, :reset_local_models => true)

Deletes all records from the property bag and model tables, if models are set then reset only selected models

# models                Array of models names to reset
# reset_client_info     If set to true, client_info 
#                       table will be cleaned.
#
# reset_local_models    If set to true, local(non-synced models) 
#                       will be cleaned.
Rhom::Rhom.database_full_reset_ex(:models => ['Product', 'Customer'])

Seeding the Database

If your application requires seeding some initial data, you can use RhoUtils.load_offline_data.

For example, in the rhodes/spec/framework_spec, we use load_offline_data to seed the device database for each test:

Rho::RhoUtils.load_offline_data(
  ['client_info','object_values'], 'spec'
)

In this example, there is a ‘spec/fixtures’ directory which contains a client_info.txt and object_values.txt pipe-delimited files. These files are structured as follows:

client_info.txt:

client_id|last_sync_success
67320d31-e42e-4156-af91-5d9bd7175b08|

object_values.txt:

source_name|attrib|object|value
Case|status|4900dc4c072c|New|
Case|assigned_user_id|4900dc4c072c|48fce5e9fb16|
Case|work_log|4900dc4c072c||
Case|priority|4900dc4c072c|High|
...

Importing and exporting the database

You can export local database partition with all its blob objects to zip archive. Also you can import previously exported partition.

Rhom.database_export returns a local path to created archive with database and blob objects.

Rhom.database_import imports database and blob objects from zip archive, created with database_export call. If imported archive is inconsistent, or other failure will occur during import process, original database will be restored.

Advanced Queries

find(*args) (advanced conditions)

Rhom also supports advanced find :conditions. Using advanced :conditions, rhom can optimize the query for the property bag table.

Let’s say we have the following SQL fragment condition:

Product.find(
 :all, 
 :conditions => [ 
   "LOWER(description) like ? or LOWER(title) like ?", 
   query, 
   query
 ], 
 :select => ['title','description'] 
)

Using advanced :conditions, this becomes:

Product.find( 
  :all, 
  :conditions => { 
    { 
      :func => 'LOWER', 
      :name => 'description', 
      :op => 'LIKE'
    } => query, 
    {
      :func => 'LOWER', 
      :name => 'title', 
      :op => 'LIKE'
    } => query
  }, 
  :op => 'OR', 
  :select => ['title','description']
)

You can also use the ‘IN’ operator:

Product.find(
  :all, 
  :conditions => { 
    {
      :name => "image_uri", 
      :op => "IN" 
    } => "'15704','15386'" 
  } 
)

# or use array notation
Product.find(
  :all, 
  :conditions => { 
    {
      :name => "image_uri", 
      :op => "IN" 
    } => ["15704","15386"]
  } 
)

You can also group :conditions:

cond1 = {
  :conditions => { 
    {
      :func => 'UPPER', 
      :name => 'name', 
      :op => 'LIKE' 
    } => query, 
    { 
      :func => 'UPPER', 
      :name => 'industry', 
      :op => 'LIKE'
    } => query
  }, 
  :op => 'OR'
}

cond2 = {
  :conditions => { 
    {
      :name => 'description', 
      :op => 'LIKE'
    } => 'Hello%'
  }   
}

@accts = Account.find( 
  :all, 
  :conditions => [cond1, cond2], 
  :op => 'AND', 
  :select => ['name','industry','description']
)

Find by numeric field

To use number comparison conditions in find use CAST : :::ruby @accts = Account.find(:all, :conditions => { {:func=> ‘CAST’, :name=>‘rating as INTEGER’, :op=>‘<’} => 3 } ) #or using sql query: size = 3 @accts = Account.find(:all, :conditions => [“CAST(rating as INTEGER)< ?”, “#{size}”], :select => [‘rating’] )

Database Encryption

If your application requires that the local database is encrypted on the filesystem, you can enable it by setting a flag in build.yml:

encrypt_database: 1

Platform Notes

• iOS: Uses AES 128 encryption algorithm from iOS SDK.
• Android: Uses AES 128 ecryption algorithm from Android SDK.
• Windows Mobile: Uses RC4 algorithm from Windows Mobile SDK.

Blackberry Notes

• Any Blackberry versions: Uses AES 128 ecryption algorithm from Blackberry JDK with HSQL database
• Blackberry JDE >= 5.0 with SQLITE: Uses built-in encryption library for SQLite database.

• Any Blackberry Version: The user can turn on memory encryption (device memory and sdcard), This policy can also can be enforced by the Blackberry enterprise server:

bb:   
  use_sqlite: 0

Performance Tips

• Before test application for performance set warning log level in rhoconfig.txt(MUST set for Blackberry testing):

  :::txt MinSeverity = 3

• All database modification operations can be slow, especially on big databases. So optimize object modification - prepare data and call create/update_attributes once

• Do not use sql conditions in Model.find, use Advanced Queries.
• Use Model.create to insert object to database
• Use update_attributes to add or modify object attributes

• To insert/update multiple object/models use database transaction

    db = ::Rho::RHO.get_src_db('Model')
    db.start_transaction
    begin
      items.each do |item|
        # create hash of attribute/value pairs
        data = {
          :field1 => item['value1'], 
          :field2 => item['value2'] 
        } 
        # Creates a new Model object and saves it
        new_item = Model.create(data)
      end
     db.commit
    rescue
     db.rollback
    end
  

require_source 'Model'