Rails - Jul 2006 - switching database dynamically based on incoming request

So I have this idea to reduce code replication in our rails setup.

Currently we have our rails app serving two distinct sites, soon to be
three. At the moment we check out a separate set of code for each site,
and configure it by adjusting the database setting and some config files
that pull in separate style sheets.

To remove this replication I''d like to have a single codebase, but
adjust the databases and config settings based on what site the user is
trying to view, e.g.

http://virtualhost1.us.com ==> use virtualhost1 db & stylesheets
http://virtualhost2.us.com ==> use virtualhost2 db & stylesheets

Currently the two virtual hosts map to two different directories that
both include the same version of our application, just with slightly
different configurations.

It seems fairly straightforward to make the config settings dynamic,
since we can use request.host to work out which host the user is
hitting, and switch style sheets etc. based on that. [note that ENV
can''t be used in this fashion since it appears to only pick up
enviroment variables set for the fcgid process in general]

In principle we could do the same for the database by adjusting
database.yml like so:

development:
adapter: mysql
database: <%= request.env[''DEV_DATABASE''] %>
host: localhost
username: <%= request.env[''DEV_DATABASE_USERNAME'']
password: <%= request.env[''DEV_DATABASE_PASSWORD'']

The idea being to have a virtual host config like:

<VirtualHost virtualhost1.us.com:80>
ServerName virtualhost1.us.com
SetEnv DEV_DATABASE virtualhost1
SetEnv DEV_DATABASE_USERNAME virtualhost1_username
SetEnv DEV_DATABASE_PASSWORD virtualhost1_password
DocumentRoot "/var/www/our_rails_app"
<Directory "/var/www/our_rails_app">
Options ExecCGI FollowSymLinks
AllowOverride all
Allow from all
Order allow,deny
AddHandler cgi-script .cgi
AddHandler fastcgi-script .fcgi
</Directory>
</VirtualHost>

However the request variable is not available for use in database.yml,
and while the ENV variable is, the following:

development:
adapter: mysql
database: <%= ENV[''database''] %>
host: localhost
username: <%= ENV[''database_username'']
password: <%= ENV[''database_password'']

won''t work because the ENV settings under fcgid don''t pick up
anything
set in the virtual host settings, or any other SetEnv directive.
Currently ENV under fcgid appears to only have variables set as part of
the fcgid config which are global for all processes, so this won''t
allow
us to dynamically switch the database based on user request.

request.env appears to pick up environment variables set up the Apache
SetEnv directive, and I''ve been trying to work out how this happens in
actionpack, but experiments like the following:

<% cgi = CGI.new %>
<%= debug cgi.send(:env_table)%>

appear to produce access to ENV, i.e. only the global variables, and not
the specific ones to the current virtual host request.

I would be very grateful for any input on how to resolve this problem,
or any information that might help me better understand the difference
between ENV and request.env in rails.

Many thanks in advance.

CHEERS> SAM

Hmm, so I think I found one way round this which is to include the
following in application.rb:

before_filter :dynamic_db

def dynamic_db
ActiveRecord::Base.configurations[RAILS_ENV][''database'']
request.env[RAILS_ENV+''_DATABASE'']
ActiveRecord::Base.establish_connection
end

where we have the following in our virtual host specification:

<VirtualHost virtualhost1:80>
ServerName virtualhost1
SetEnv RAILS_ENV development
SetEnv development_DATABASE dev_db
SetEnv production_DATABASE prod_db
SetEnv test_DATABASE test_db
DocumentRoot "/var/www/myrailsapp/"
<Directory "/var/www/myrailsapp/">
Options ExecCGI FollowSymLinks
AllowOverride all
Allow from all
Order allow,deny
AddHandler cgi-script .cgi
AddHandler fastcgi-script .fcgi
</Directory>
</VirtualHost>

I''m not sure if this setup with the reconnection in application.rb will
adversely affect performance, but superficially it appears to work fine.

CHEERS> SAM

Sam Joseph wrote:
>So I have this idea to reduce code replication in our rails setup.
>
>Currently we have our rails app serving two distinct sites, soon to be
>three. At the moment we check out a separate set of code for each site,
>and configure it by adjusting the database setting and some config files
>that pull in separate style sheets.
>
>To remove this replication I''d like to have a single codebase, but
>adjust the databases and config settings based on what site the user is
>trying to view, e.g.
>
>http://virtualhost1.us.com ==> use virtualhost1 db & stylesheets
>http://virtualhost2.us.com ==> use virtualhost2 db & stylesheets
>
>Currently the two virtual hosts map to two different directories that
>both include the same version of our application, just with slightly
>different configurations.
>
>It seems fairly straightforward to make the config settings dynamic,
>since we can use request.host to work out which host the user is
>hitting, and switch style sheets etc. based on that. [note that ENV
>can''t be used in this fashion since it appears to only pick up
>enviroment variables set for the fcgid process in general]
>
>In principle we could do the same for the database by adjusting
>database.yml like so:
>
>development:
>adapter: mysql
>database: <%= request.env[''DEV_DATABASE''] %>
>host: localhost
>username: <%= request.env[''DEV_DATABASE_USERNAME'']
>password: <%= request.env[''DEV_DATABASE_PASSWORD'']
>
>The idea being to have a virtual host config like:
>
><VirtualHost virtualhost1.us.com:80>
>ServerName virtualhost1.us.com
>SetEnv DEV_DATABASE virtualhost1
>SetEnv DEV_DATABASE_USERNAME virtualhost1_username
>SetEnv DEV_DATABASE_PASSWORD virtualhost1_password
>DocumentRoot "/var/www/our_rails_app"
><Directory "/var/www/our_rails_app">
>Options ExecCGI FollowSymLinks
>AllowOverride all
>Allow from all
>Order allow,deny
>AddHandler cgi-script .cgi
>AddHandler fastcgi-script .fcgi
></Directory>
></VirtualHost>
>
>However the request variable is not available for use in database.yml,
>and while the ENV variable is, the following:
>
>development:
>adapter: mysql
>database: <%= ENV[''database''] %>
>host: localhost
>username: <%= ENV[''database_username'']
>password: <%= ENV[''database_password'']
>
>won''t work because the ENV settings under fcgid don''t pick
up anything
>set in the virtual host settings, or any other SetEnv directive.
>Currently ENV under fcgid appears to only have variables set as part of
>the fcgid config which are global for all processes, so this won''t
allow
>us to dynamically switch the database based on user request.
>
>request.env appears to pick up environment variables set up the Apache
>SetEnv directive, and I''ve been trying to work out how this happens
in
>actionpack, but experiments like the following:
>
><% cgi = CGI.new %>
><%= debug cgi.send(:env_table)%>
>
>appear to produce access to ENV, i.e. only the global variables, and not
>the specific ones to the current virtual host request.
>
>I would be very grateful for any input on how to resolve this problem,
>or any information that might help me better understand the difference
>between ENV and request.env in rails.
>
>Many thanks in advance.
>
>CHEERS> SAM
>
>
>
>_______________________________________________
>Rails mailing list
>Rails@lists.rubyonrails.org
>http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>  
>

Impressive.. I''m trying to do something similar but a little different,
maybe
you could help me out. i want to make 2 sets of identical tables: one for the
actual data and the other (fake) data that will be used to train people/demo the
rails app. so i want my code to do something like: user clicks on a button to
switch to training mode, which sets a session variable to flag that the app is
in training mode and i want it to switch to using the training database during
that time. I thought that this might not have been possible with rails and gave
up much hope, but after reading your post i have regained hope :)

i''m not exactly an expert when it comes to the nitty gritty details of
rails
configuration/environment.. definetly have much to learn.

Thanks in advance,

stuart

Hi Stuart,

Hmm, I think there are a lot of different ways to do what you describe.

Depending on your setup I would have thought that the easiest thing 
might be to use the same approach as the one I described previously, and 
have slighlty different urls for the training site and the live site, e.g.

http://train.stuartsite.com/  ==> hit''s training db
http://www.stuartsite.com/ ==> hit''s live db

and each page could have a link to "TRAIN" or "LIVE" that
would switch
from one site to the other, but to exactly the same page, e.g.

if you are on http://train.stuartsite.com/items/list, then hitting the 
"LIVE" button takes you to http://www.stuartsite.com/items/list and
v.v.
for "TRAIN"

I think this would have the added benefit that it would always be clear 
from the url which mode you were in.

Of course you may be wanting to avoid completely replicating your entire 
db, then you could go for something much simpler such as having two 
models like this:

class StuartItemTrain < StuartItem
  set_table_name ''stuart_item_train''
end

class StuartItem < ActiveRecord::Base
  set_table_name ''stuart_item_train''
  def stuarts_funky_method
    # funky stuff
  end
end

Then you could just switch from:

http://www.stuartsite.com/stuartitems/list

to

http://www.stuartsite.com/stuartitemtrains/list

to get the same effect for a single model.

HTH

CHEERS> SAM

Stuart Wade wrote:
>Impressive.. I''m trying to do something similar but a little
different, maybe
>you could help me out. i want to make 2 sets of identical tables: one for
the
>actual data and the other (fake) data that will be used to train people/demo
the
>rails app. so i want my code to do something like: user clicks on a button
to
>switch to training mode, which sets a session variable to flag that the app
is
>in training mode and i want it to switch to using the training database
during
>that time. I thought that this might not have been possible with rails and
gave
>up much hope, but after reading your post i have regained hope :)
>
>i''m not exactly an expert when it comes to the nitty gritty details
of rails
>configuration/environment.. definetly have much to learn.
>
>Thanks in advance,
>
>stuart
>
>
>_______________________________________________
>Rails mailing list
>Rails@lists.rubyonrails.org
>http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>  
>

Hi Sam,

Thanks for the excellent recommendations. I appreciate it very much! :)

Stuart