# Storing constant data in a separate database

**URL:** <https://www.ruby-forum.com/t/storing-constant-data-in-a-separate-database/191536>\
**Category:** Rails\
**Created:** [July 14, 2010, 2:08am UTC](https://www.ruby-forum.com/t/storing-constant-data-in-a-separate-database/191536 "2010-07-14T02:08:01Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [July 14, 2010, 2:08am UTC](https://www.ruby-forum.com/t/storing-constant-data-in-a-separate-database/191536/1 "2010-07-14T02:08:01Z")

</div>

Following a suggestion by Rob B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid),  
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.

---

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [July 14, 2010, 3:05am UTC](https://www.ruby-forum.com/t/storing-constant-data-in-a-separate-database/191536/2 "2010-07-14T03:05:30Z")

</div>

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](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-  
> removed\_email\_address@domain.invalid.  
> To unsubscribe from this group, send email to removed\_email\_address@domain.invalid  
> .  
> For more options, visit this group at [http://groups.google.com/group/rubyonrails-talk?hl=en](http://groups.google.com/group/rubyonrails-talk?hl=en)  
> .

Rob B.  
removed\_email\_address@domain.invalid [http://AgileConsultingLLC.com/](http://AgileConsultingLLC.com/)  
removed\_email\_address@domain.invalid [http://GaslightSoftware.com/](http://GaslightSoftware.com/)

---

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [July 14, 2010, 9:16pm UTC](https://www.ruby-forum.com/t/storing-constant-data-in-a-separate-database/191536/3 "2010-07-14T21:16:39Z")

</div>

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](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