Following a suggestion by Rob B. [email protected],
I’m setting up a separate database to hold “constant” data, ie, tables
that are not changed by my application. (Though they are occasionally
changed by an external app.)
My questions are first, followed by the recipe I used to set things up:
-
What’s the approved way to create and modify tables in the separate
db? I’m pretty sure rake:migrate is out of the question since there’s
only one schema.rb file, but I’d like to be wrong!
-
Does every subclass of Readonly need to call set_table_name (see
below)? (How un-DRY!)
-
If I genuinely wanted the readonly table to be read-only, is there a
way to specify that (in Rails, not in the db, since I still need my ETL
app to update Readonly occasionally)?
Thanks in advance.
– ff
The recipe:
== I created subclasses to ActiveRecord:
file: app/model/readonly.rb
class Readonly < ActiveRecord::Base
establish_connection :readonly
end
file: app/model/employee.rb
class Employee < Readonly
set_table_name self.to_s.tableize
end
=== I extended config/database.yml:
A database to hold constant data
readonly:
adapter: mysql
encoding: utf8
reconnect: false
database: myapp_readonly
pool: 5
username: <%= ENV[‘MYSQL_USERID’] || “root” %>
password: <%= ENV[‘MYSQL_PASSWORD’] || “” %>
socket: <%= ENV[‘MYSQL_SOCKET’] || “/tmp/mysql.sock” %>
=== Created the database “by hand” in mysql:
mysql> create database myapp_readonly;
create database myapp_readonly;
Query OK, 1 row affected (0.53 sec)
=== Populated it with a small Employee table (cribbed from another
database):
% mysqldump -uxxx -pxxx myapp_development employees > /tmp/employees.sql
% mysql -uxxx -pxxx myapp_readonly < /tmp/employees.sql
=== Verified myapp_readonly.employees:
bash-3.2$ script/dbconsole
mysql> use myapp_readonly;
mysql> show tables;
±-------------------------+
| Tables_in_myapp_readonly |
±-------------------------+
| employees |
±-------------------------+
1 row in set (0.17 sec)
mysql> select * from employees;
±—±---------------±--------------------±--------------------+
| id | name | created_at | updated_at |
±—±---------------±--------------------±--------------------+
| 1 | Roger Dodger | 2010-06-17 00:05:52 | 2010-06-17 00:05:52 |
| 2 | Monresh Trebon | 2010-06-17 00:05:52 | 2010-06-17 00:05:52 |
±—±---------------±--------------------±--------------------+
2 rows in set (0.22 sec)
== Verified that I could read Employee records:
% script/console
Employee.first
=> #<Employee id: 1, name: “Roger Dodger”, created_at: …>
Cool beans!! It works.
On Jul 13, 2010, at 8:08 PM, Fearless F. wrote:
only one schema.rb file, but I’d like to be wrong!
Don’t know about that part (my use for this a couple years ago was
offloading some processing to a separate environment, but needed one
specific table to look at the live production data – same schema ;-).
- Does every subclass of Readonly need to call set_table_name (see
below)? (How un-DRY!)
class Readonly < ActiveRecord::Base
@abstract_class = true
establish_connection “test”
def self.inherited?(klass)
super
set_table_name klass.name.tableize
end
end
class TimeDimension < Readonly
end
irb> TimeDimension.table_name
=> “time_dimensions”
irb> TimeDimension.base_class
=> TimeDimension
This works with Rails1.2 and ought to work up to 2.3.x, but the use of
@abstract_class doesn’t seem documented well enough to rely on (look
at the ‘show source’ for the ActiveRecord::Base#abstract_class? and
then try to find out where to “officially” declare your own abstract
class).
- If I genuinely wanted the readonly table to be read-only, is there a
way to specify that (in Rails, not in the db, since I still need my
ETL
app to update Readonly occasionally)?
You might be able to trick the subclasses by redefining the mutating
methods, but I’d hate to have to find them all (save, create,
update_attribute, update_attributes, toggle, etc., not to mention all
the AssociationProxy forms).
I thought perhaps there was an option to request a read-only
connection, but even in the low-level MySQL driver doesn’t appear to
have such a thing. Could you connect with a user that is only GRANTed
read-only access to the tables (or the schema, really)? ‘SELECT’ (and
perhaps ‘LOCK TABLE’) seems to be a minimum.[1] If you try it, please
let the list know how it goes.
-Rob
[1] http://dev.mysql.com/doc/refman/5.1/en/grant.html
class Readonly < ActiveRecord::Base
A database to hold constant data
=== Created the database “by hand” in mysql:
% mysql -uxxx -pxxx myapp_readonly < /tmp/employees.sql
±-------------------------+
== Verified that I could read Employee records:
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-
[email protected].
To unsubscribe from this group, send email to [email protected]
.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en
.
Rob B.
[email protected] http://AgileConsultingLLC.com/
[email protected] http://GaslightSoftware.com/
Rob B. wrote:
I thought perhaps there was an option to request a read-only
connection, but even in the low-level MySQL driver doesn’t appear to
have such a thing. Could you connect with a user that is only GRANTed
read-only access to the tables (or the schema, really)? ‘SELECT’ (and
perhaps ‘LOCK TABLE’) seems to be a minimum.[1] If you try it, please
let the list know how it goes.
-Rob
Rob:
Based on your most excellent suggestions, here you go:
http://www.ruby-forum.com/topic/213137
(short form: create a database user with readonly privileges, create an
AR subclass that uses a connection through that user. works like a
champ.)