Tutorial: Using an SQLite database with ActiveRecord

adawolfs edited this page Aug 27, 2014 · 74 revisions
Clone this wiki locally

Our goal

We want to access an SQLite database using ActiveRecord. We will be using the SQLDroid jar distributed by the sqldroid gem.

Prerequisites

  • You should have completed the Getting started with Ruboto.
  • Have an emulator running or a device with USB debugging connected.
  • Internet access to download gems.

This tutorial has been tested with the following setups:

ruboto RubotoCore ActiveRecord ARJDBC SQLDroid Device Android Android SDK Tester
1.0.0 3.2.16 1.2.9 1.0.0 Emulator 4.0.3 22.3 donv
0.16.0.dev 0.5.6 3.2.15 1.2.9 1.0.0.RC11 Emulator 4.1.2 23 donv
0.11.0.dev 0.5.3 3.2.13 1.2.9 1.0.0.RC11 Emulator 4.0.3 21.1 donv
0.10.2 0.5.3 3.1.11 1.2.2.1 1.0.0.RC7 Emulator 3.0.2 21.1 donv
0.7.1.rc.3 0.4.7 3.1.8 1.2.2 0.3.0 ASUS TF101 4.0.3 20.0.1 donv

Create your project

ruboto gen app -t 15 --package org.ruboto.example.sqlite_ar --with-jruby
cd sqlite_ar

Add the gems to your Gemfile

Create a new file Gemfile, and add the following:

source "http://rubygems.org"

gem 'activerecord', '<4.0.0'
gem 'activerecord-jdbc-adapter', '<1.3.0'
gem 'activerecord-jdbcsqlite3-adapter', '<1.3.0'
gem 'sqldroid'

Configure database

Create a new file src/android_logger.rb

class AndroidLogger
  def self.debug(*args)
    # Java::android.util.Log.d *args
    puts *args
  end
    
  def self.debug?
    true
  end

  def self.info(*args)
    # Java::android.util.Log.i *args
    puts *args
  end

  def self.warn(*args)
    # Java::android.util.Log.w *args
    puts *args
  end

  def self.error(*args)
    # Java::android.util.Log.e *args
    args.each do |m|
      if m.is_a? Exception
        puts m.message
        puts m.backtrace.join("\n")
      end
      puts m
    end
  end
end

Create a new file src/database.rb

require 'active_record'
require 'android_logger'

class Database
  def self.setup(context)
    ActiveRecord::Base.logger = AndroidLogger

    db_dir = "#{context.files_dir.path}/my_db.sqlite"
    connection_options = {
        :adapter => 'jdbcsqlite3',
        :driver => 'org.sqldroid.SQLDroidDriver',
        :url => "jdbc:sqldroid:#{db_dir}",
        :database => db_dir,
        :pool => 30,
        :timeout => 25000,
    }

    ActiveRecord::Base.configurations = {
        :production => connection_options,
    }

    ActiveRecord::Base.establish_connection(ActiveRecord::Base.configurations[:production])
  end

  def self.migrate(context)
    src_dir = 'file:' + context.package_manager.getApplicationInfo($package_name, 0).sourceDir + '!/'
    migration_path = File.expand_path("#{src_dir}/migrate")
    puts "Looking for migration scripts in #{migration_path}"
    migrator = ActiveRecord::Migrator.new(:up, migration_path)
    if migrator.pending_migrations.size > 0
      puts "Found #{migrator.pending_migrations.size} migrations."
      migrator.migrate
    end
  end
end

Add migrations

Create a new file in src/migrate/001_create_people.rb

class CreatePeople < ActiveRecord::Migration
  def self.up
    create_table :people do |t|
      t.string :name, :limit => 32, :null => false
      t.date :birthdate
      t.timestamps
    end
    add_index :people, :name, :unique => true
  end

  def self.down
    drop_table :people
  end
end

Add data and display table

Create a new file src/person.rb

class Person < ActiveRecord::Base
  validates_presence_of :name
  validates_uniqueness_of :name
end

Edit src/sqlite_ar_activity.rb to

require 'ruboto/widget'
require 'ruboto/util/stack'

ruboto_import_widgets :LinearLayout, :ListView

class SqliteArActivity
  def onCreate(bundle)
    super
    setTitle 'People'

    self.content_view = linear_layout :orientation => :vertical do
      @list_view = list_view :list => []
    end
  end

  def onResume
    super
    dialog = android.app.ProgressDialog.show(self, 'SQLite ActiveRecord Example', 'Loading...')
    Thread.with_large_stack 128 do
      require 'database'
      Database.setup(self)
      run_on_ui_thread{dialog.message = 'Generating DB schema...'}
      Database.migrate(self)
      run_on_ui_thread{dialog.message = 'Populating table...'}
      require 'person'
      Person.delete_all
      Person.create :name => 'Charles Oliver Nutter'
      Person.create :name => 'Jan Berkel'
      Person.create :name => 'Scott Moyer'
      Person.create :name => 'Daniel Jackoway'
      Person.create :name => 'Uwe Kubosch'
      Person.create :name => 'Roberto Gonzalez'
      people_names = Person.order(:name).all.map(&:name)
      run_on_ui_thread do
        @list_view.adapter.add_all people_names
        dialog.dismiss
      end
    end
  end
end

Update the scripts and restart the application:

rake update_scripts:restart

This will take a very long time, but you should see a progress dialog along the way.

When the app has started you should now see a list of people on your screen.