Chapters1 What is Arel?
Arel is a SQL. AST manager for Ruby. It
- Simplifies the generation of complex SQL queries
- Adapts to various RDBMSes
Arel might be a short for ActiveRelation
2 Generate SQL string with Arel
The method Arel::Nodes::Node#to_sql could generate SQL string.
The gem active_record has required Arel gem, and the bind between
Arel and active_record is very close, because Arel needs a engine to work,
which is very confused to me.
require 'active_record'
ActiveRecord::Base.establish_connection(
adapter: 'mysql2',
database: 'test',
host: 'localhost',
username: 'root',
password: '1024'
)
user = Arel::Table.new('users')
arel = user.
project('id', 'user_name'). # select
where(user[:nick_name].eq('dsg')). # where
order('created_at DESC'). # order
skip(10). # offset
take(5); # limit
sql = arel.to_sql
#=>
# SELECT id, user_name FROM `users`
# WHERE `user`.`nick_name` = 'dsg'
# ORDER BY created_at DESC
# LIMIT 5
# OFFSET 10
When we get the sql, we can use ActiveRecord::Base.find_by_sql(sql) to get
the record.
An ActiveRecord::Base.connection is needed here, but the generating-sql progress is no business of the connecion.
Another example usage of Arel in Rails.
#
# SELECT `roles`.` FROM `roles`
# WHERE(
# `roles`.`id` < 10
# AND `roles`.`id` > 0
# OR `roles`.`id` = 1024
# )
t = Role.arel_table
Role.where(
t[:id].
lt(10).
and(t[:id].gt 0).
or(t[:id].eq 1024)
)
3 The Arel-SQL mapping
To know the Arel-SQL mapping, we should first know two concepts:
- Abstract Syntax Tree
- SQL Design Pattern
3.1 Abstract Syntax Tree
Abstract Syntax Tree is a tree representation of the abstract syntactic structure of source code written in a programming language.
An example of AST can be as below:
while b!=0 do
if a > b
a = a - b
else
b = b - a
end
end
return a
3.2 SQL Design Pattern
Take the SELECT part as an example:
The design comes from SQL As Understood By SQLite
- select_statement
- select_core
The SELECT part can be seen as below:
|-- SelectCore | |-- Projections(id, user_name, ...) | |-- Where | |-- Group |-- Order |-- Limit |-- Limit |-- Offset
3.3 Arel Design Pattern
Come back to the sql
user = Arel::Table.new('users')
arel = user.
project('id', 'user_name'). # select
where(user[:nick_name].eq('dsg')). # where
order('created_at DESC'). # order
skip(10). # offset
take(5); # limit
We can get the sql
SELECT id, user_name FROM `users`
WHERE `user`.`nick_name` = 'dsg'
ORDER BY created_at DESC
LIMIT 5
OFFSET 10
Arel gives a method to draw an AST image.
File.write('arel.dot', arel.to_dot)
system %x(dot arel.dot -T png -o arel.png)
Then we get the map of Arel
From the AST, we know
* The concept of select_statement and select_core comes from
SQL Design Pattern
* The left, right branch concept comes from
Abstract Syntax Tree
4 Arel Source Code Inspection
Everything goes to the method: to_sql
An tiny example of sql transferring
id = Arel::Nodes::SqlLiteral.new('id')
count = id.count
count.to_sql
We could use pry's show-method count.to_sql to find the method
# From: lib/arel/nodes/node.rb @ line 34: # Owner: Arel::Nodes::Node # Visibility: public # Number of lines: 3 def to_sql engine = Table.engine engine.connection.visitor.accept self end
To find the method accept, we should know the ancestor chain of
Arel::Table.engine.connection.visitor
visitor = Arel::Table.engine.connection.visitor visitor.class.ancestors #=> # # [ # [ 0] ActiveRecord::ConnectionAdapters::AbstractMysqlAdapter::BindSubstitution < Arel::Visitors::MySQL, # [ 1] Arel::Visitors::BindVisitor, # [ 2] Arel::Visitors::MySQL < Arel::Visitors::ToSql, # [ 3] Arel::Visitors::ToSql < Arel::Visitors::Visitor, # [ 4] Arel::Visitors::Visitor < Object, # [ 5] Object < BasicObject, # [ 6] JSON::Ext::Generator::GeneratorMethods::Object, # [ 7] ActiveSupport::Dependencies::Loadable, # [ 8] PP::ObjectMixin, # [ 9] Kernel, # [10] BasicObject # ]
# Arel::Visitors::Visitor def accept object visit object end
The object here is id.count. Inspect the class of id.count
ruby
id.count.class #=> Arel::Nodes::Count
Then we seek the visit method, which is the core of the Arel gem.
The accept, visit concepts come from the
Visitor Pattern,
while it's not the same with the traditional Visitor Pattern. Check this
reference
if you are intersted.
def visit object
send dispatch[object.class], object
rescue NoMethodError => e
raise e if respond_to?(dispatch[object.class], true)
superklass = object.class.ancestors.find { |klass|
respond_to?(dispatch[klass], true)
}
raise(TypeError, "Cannot visit #{object.class}") unless superklass
dispatch[object.class] = dispatch[superklass]
retry
end
Very intersting, just send dispatch[object.class], that is
send dispatch[Arel::Nodes::Count], object #=> visit_Arel_Nodes_Count(id.count)
Finally, find the visit_Arel_Nodes_Count method
# Arel::Visitors::ToSql
def visit_Arel_Nodes_Count o
"COUNT(#{o.distinct ? 'DISTINCT ' : ''}#{o.expressions.map { |x|
visit x
}.join(', ')})#{o.alias ? " AS #{visit o.alias}" : ''}"
end
Go through more complexed sql
user = Arel::Table.new('users')
arel = user.
project('id', 'user_name').
where(user[:nick_name].eq('dsg')).
order('created_at DESC').
skip(10).
take(5);
arel.to_sql
5 Is there a simple way to create SQL strings?
The AST, SQL Lang Pattern, Visitor Pattern may be a little complexed? Maybe there is a simple way to create SQL strings as below:
class NewArel
attr_accessor :where, :select, :order, :skip, :limit
def where(string)
@wheres ||= []
@wheres << string
self
end
def select(*args)
@selects ||= []
@selects = @selects.concat(args).compact.uniq
self
end
def order(string)
@orders ||= []
@orders << string
self
end
# ... omited
def to_sql
[
"SELECT #{@selects.join(', ')}",
"WHERE #{@where.join('AND ')}"
].join(' ')
end
end
arel = NewArel.new.
where('id < 10').
where('id > 5').
select(:id, :user_name)
arel.to_sql
6 What's more about Arel
- TreeManage
- SelectManager
- UpdateManager
- InsertManager
- DeleteManager
- visitors
- mysql
- sqlite
- mssql
- ....
