Following a suggestion by Rob Biedenharn <Rob@AgileConsultingLLC.com>,
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!
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
TimeDimension.table_name
=> "time_dimensions"
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
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:
> 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
> use myapp_readonly;
> show tables;
+--------------------------+
> Tables_in_myapp_readonly |
+--------------------------+
> employees |
+--------------------------+
1 row in set (0.17 sec)
> 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.
--
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@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Rob Biedenharn
Rob@AgileConsultingLLC.com http://AgileConsultingLLC.com/
rab@GaslightSoftware.com http://GaslightSoftware.com/
