Storing constant data in a separate database

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.)

  • ff

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs