Based on excellent suggestions by Rob B.
[email protected] in
Storing constant data in a separate database - Rails - Ruby-Forum
…here’s a recipe for setting up access to an external readonly
database.
Motivation: My application has several very large tables that are
constant (from the point of my Rails app). In fact, there are external
scripts that will occasionally update the large tables, but such updates
are infrequent. I want my Rails app to access but not modify these
tables.
Overall approach: we create an external database (‘myapp_external’) and
a database user (‘readonly_user’) that is granted only SELET privileges
on the database. We then create a subclass of ActiveRecord
(‘ReadonlyDB’) that connects to the database through readonly_user, so
any attempts to modify the database will raise an error.
=== Step 1: extend config/database.yml with a new entry specifying a
database named ‘myapp_external’ and a username of “readonly_user”. For
mysql, it might look like this:
readonly_db:
adapter: mysql
encoding: utf8
reconnect: false
database: myapp_external # see note 1
pool: 5
username: readonly_user
password: readonly_pass # see note 2
socket: /tmp/mysql.sock
[note 1]: You could, in fact specify “myapp_development” or
“myapp_production”, in which case you’d have a readonly window onto your
development or production database. In our case, we want a separate
database altogether.
[note 2]: Naturally, you can pick any username and password that you
want for the readonly user.
=== Step 2: create the external database and readonly user.
% mysql -uroot -pxxxxx
mysql> create database myapp_external;
Query OK, 1 row affected (0.15 sec)
mysql> CREATE USER ‘readonly_user’@‘localhost’ IDENTIFIED BY
‘readonly_pass’;
Query OK, 0 rows affected (0.82 sec)
mysql> GRANT SELECT ON . TO ‘readonly_user’@‘localhost’ IDENTIFIED BY
‘readonly_pass’;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR ‘readonly_user’@‘localhost’;
±-------------------------------------------------------------------------+
| Grants for readonly_user@localhost |
±-------------------------------------------------------------------------+
| GRANT SELECT ON . TO ‘readonly_user’@‘localhost’ IDENTIFIED BY PASS… |
±-------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> quit
=== Step 3: populate the external database with some data Here, we’re
just copying a table from myapp_development into myapp_external.
% mysqldump -c -uroot -pxxxxx myapp_development employees >
/tmp/empsl.sql
% mysql -uroot -pxxxx myapp_external < /tmp/empls.sql
=== Step 4: Create ReadonlyDB < ActiveRecord::Base and a test subclass
— file: apps/models/readonly_db.rb:
class ReadonlyDB < ActiveRecord::Base
establish_connection :readonly_db
Mark ReadonlyDB as abstract so AR won’t try to access any underlying
tables. But subclasses will be concrete (abstract_class?() => true)
@abstract_class = true
This hook is called when ReadonlyDB is subclassed. We use it to set
the table name from the class name (just like ActiveRecord does)
def self.inherited(c)
super
c.set_table_name(c.name.tableize)
end
I’m not sure this is 100% required, but it allows AR to catch
attempts
to modify the db earlier than if it went all the way down to the db.
def readonly?
true
end
end
— file: apps/models/employee.rb:
class Employee < ReadonlyDB
end
=== Step 5: Test it out!
bash-3.2$ script/console
Loading development environment (Rails 2.3.8)
Employee.count
=> 2
Employee.first
=> #<Employee id: 1, name: “Roger Dodger”, created_at: “2010-06-17
00:05:52”…>
Employee.create(:name => “Pete Shelley”) # (…“I don’t exist”)
ActiveRecord::ReadOnlyRecord: ActiveRecord::ReadOnlyRecord
from
/Users/r/Development/Ruby/lib/ruby/gems/1.9.1/gems/activerecord-2.3.8/lib/active_record/base.rb:2914:in
create_or_update' ... Employee.first.delete ActiveRecord::StatementInvalid: Mysql::Error: DELETE command denied to user 'readonly_user'@'localhost' for table 'employees': DELETE FROMemployeesWHERE (idIN (1)) from /Users/r/Development/Ruby/lib/ruby/gems/1.9.1/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract_adapter.rb:221:inrescue in log’
…
ReadonlyDB # Show that AR knows that ReadonlyDB is abstract
=> ReadonlyDB(abstract)
Employee # Show that AR knows the structure of Employee
=> Employee(id: integer, name: string, created_at: datetime, …)
=== Summary & Comments:
I’ve only tried this with Rails 2.3.8 and mysql 5.1.36 – YMMV. But the
approach should be generally applicable: create a database user that has
readonly privileges, create a new entry in config/database.yml to
reference that user, create a subclass of AR that connects through that
entry. Voila: a readonly external database.
As hinted at in Step 1 Note 1 (above), your entry in config/database.yml
doesn’t have to reference an external database: it can reference one of
your existing databases (e.g. your development or production database).
Since it connects via a readonly user, anything subclassed under
ReadonlyDB won’t be allowed to modify the database. This may be useful
for some applications.
Send comments, questions, improvements. I’m still learning myself.
- ff