SQLite3

The MotionModel SQLite3 Adapter

MotionModel's SQL adapter leverages the SQLite built-in in to iOS and the FMDB Objective-C wrapper to provide a comprehensive feature set similar to what you would get with Rails' ActiveRecord.

Where possible, the syntax tries to duplicate Rails wherever possible. Some liberties have been taken to keep the code simpler, at least for now. Pull requests to get the syntax more in-line with Rails are welcome.

For the most part, the adapter uses lazy-loading. It does not yet have any caching support. Be sure to watch the logs as you develop and add any application-level caching or memoization to keep performance in check.

Please note that while this adapter has been tested extensively in the developer's application, the specs need work. Contributions are encouraged.

Features

• Select field augment and override
• Compound WHERE conditions with various operators, IN, NOT IN, BETWEEN
• ORDER BY, GROUP BY, LIMIT
• INNER and OUTER joins
• "Through" relation handling
• Scopes with stacking
• Polymorphism
• Single-Table Inheritance
• Logging
• Automatic table and index creation
• Cascaded dependent handling (delete/destroy/nullify)

Wish list

• Migrations
• More specs
• Caching

Using the SQL Adapter

Consult the sample app as a guide to getting started.

Sample Gemfile

source "https://rubygems.org"
gem "rake"
gem "motion-support", ">= 0.2.2"
gem 'cocoapods', '~>0.16.4'
gem 'motion-cocoapods', '1.2.1'

Sample Rakefile

$:.unshift("/Library/RubyMotion/lib")
require 'motion/project'
require 'rubygems'
require 'bundler'
Bundler.require(:default)
require 'motion-cocoapods'

$: << File.expand_path('../../lib', __FILE__)
require 'motion_model'
require 'motion_model/sql'
require 'motion_model/fmdb'

Motion::Project::App.setup do |app|
  # Use `rake config' to see complete project settings.
  app.name = 'My SQL App'
end

Define a Persistent Class

Added include MotionModel::FMDBModelAdapter to have your model use a database table.

class Todo
  include MotionModel::Model
  include MotionModel::FMDBModelAdapter

  columns name: :string
  columns created_at: :datetime
  columns updated_at: :datetime
end

Scopes

A scope is used to encapsulate the criteria and options for a database access. The simplest example of a scope is one which would select all rows for the table:

scope = Post.scoped # => #<MotionModel::SQLScope>
scope.to_sql # => SELECT "posts".* FROM "posts";

You can create a scope using the #scoped method, or directly using methods such as #where, #joins, etc.

scope = Post.where(title: "Hello world") # => #<MotionModel::SQLScope>
scope.to_sql # => SELECT "posts".* FROM "posts" WHERE ("posts"."title" = "Hello world");

Scopes can be chained together. Each chained scope clones its source, leaving the receiver intact.

The adapter is not as clever as Rails yet, but it does offer several ways to deference the scope.

scope.count
scope.to_a
scope.first
scope.last
scope.each { |r| ... }
scope.map { |r| ... }

Relations

You probably won't work with Relation instances directly. When you define an association between models, relations are used to manage those associations. The #scope method turns a relation into an MotionModel::Scope instance which is used to fetch the associated records.

Examples

SELECT

By default, all columns from the appropriate table are loaded from the table. Restriction is as expected:

Post.select(:id, :title)

You can use the :add option to augment the list:

Post.select(add: "lower(title) lower_title")

You can specify a table name when necessary, i.e. when using a join.

Comment.joins(:post).select(:title, table_name: :posts, add: true)

WHERE Conditions

WHERE Conditions are ANDed together. Non-numeric types are quoted.

Comment.where(post_id: 1, title: "test").to_sql 
# => SELECT 'commments'.* FROM 'comments' WHERE ('comments'.'post_id' = 1) AND ('comments'.'title' = 'test');

You can also use ranges

User.where(id: [1, 2, 3]).to_sql 
# => SELECT 'users'.* FROM 'users' WHERE ('users'.'id' IN (1, 2, 3));

Operators

User.where({id: {not_eq: 3}}).to_sql 
# => SELECT 'users'.* FROM 'users' WHERE ('users'.'id' <> 3);
User.where({id: {:'<=' => 3}}).to_sql 
# => SELECT 'users'.* FROM 'users' WHERE ('users'.'id' <= 3);
User.where({id: {:'!=' => [4, 5]}}).to_sql 
# => SELECT 'users'.* FROM 'users' WHERE ('users'.'id' NOT IN (4, 5));
User.where({id: {:between => [10, 20]}}).to_sql 
# => SELECT 'users'.* FROM 'users' WHERE ('users'.'id' BETWEEN 10 AND 20);

JOIN

Default joins are INNER as you should expect:

EmailAccount.scoped.joins(:emails).to_sql 
# => SELECT 'email_accounts'.* FROM 'email_accounts' INNER JOIN 'emails' ON ('emails'.'email_account_id' = 'email_accounts'.'id');

Outer join:

Payee.scoped.joins(transactions: {outer: true}).to_sql 
# => SELECT 'payees'.* FROM 'payees' LEFT OUTER JOIN 'transactions' ON ('transactions'.'payee_id' = 'payees'.'id');

Conditions on a joined column:

EmailAccount.scoped.joins(:emails).where({{emails: :id} =>  1}).to_sql 
# => SELECT 'email_accounts'.* FROM 'email_accounts' INNER JOIN 'emails' ON ('emails'.'email_account_id' = 'email_accounts'.'id') WHERE ('emails'.'id' = 1);

Select columns from a JOIN:

EmailAccount.scoped.joins(:emails).select(add: :title, table_name: :emails).to_sql 
# => SELECT 'email_accounts'.*, 'emails'.'title' FROM 'email_accounts' INNER JOIN 'emails' ON ('emails'.'email_account_id' = 'email_accounts'.'id');

###ORDER

User.scoped.order(created_at: :desc).to_sql 
# => SELECT 'users'.* FROM 'users' ORDER BY 'users'.'created_at' DESC;

GROUP

Email.scoped.select("COUNT(email_account_id)").group(:email_account_id).to_sql 
# => SELECT COUNT(email_account_id) FROM 'emails' GROUP BY 'emails'.'email_account_id';

Single-Table Inheritance

Polymorphism

Logging

Caveats

The adapter doesn't offer as much customization as Rails. You're probably best off sticking with them when possible. For example, don't use a different primary key than :id.

Code Organization

It may appear that there are more class layers than strictly necessary. The goal here was to properly locate code that is related to SQL vs. SQLite3 vs. FMDB as appropriate.

Credits

The SQL Adapter for MotionModel was developed by Doug Puchalski, @aceofspades. It is a key component to an upcoming SimpliFi for iOS. SimpliFi is your Personal Financial Inbox.

Thanks to @sxross for providing much assistance and a great starting place and much of the ActiveModel-like functionality required to build this adapter. Special thanks also goes to the RubyMotion community at large, with specific help from @clayallsopp and @tkadauke.