Based on excellent suggestions by Rob Biedenharn
<Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org> in
http://www.ruby-forum.com/topic/213083
...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 FROM
`employees` WHERE (`id` IN (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:in
`rescue 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
--
Posted via http://www.ruby-forum.com/.
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.