Selecting a database from the application itself?

Hi everybody,

I just started thinking about a project and am seeking some help
regarding its design.
The idea would be to create labels (i.e. address labels).I could let
users type the text manually, but I dont think they would appreciate
that when they'll want to print 5000 addresses.
I can also probably let them import csv files, that should not be a
problem, but playing with those files is not extremely user friendly
either. Besides, I dont want them to get bored after 5 minutes and
going back to their good old excel after 5 minutes.

The most "exciting" feature i thought of is also the one that scares
me the most when it comes to coding:
I'd love to let users actually connect to any database they want,
retrieve a list of tables and fields and let them select anything they
want on the labels.
I can already see the tables with their fields that they could drag
and drop on a label template... Well, I'm dreaming, and I'd love to
know if this is going to stay just a dream or if this is actually
possible using Ruby and Rails...

Summary : Is there any way I can let users select (from the
application) a database they want to use? Or is the database.yml file
the only way of connecting to a database?

Any help would be greatly appreciated,

Thank you!

Francois

Yes, it is possible but not easy in RoR.

If you can stick to a single database and follow RoR conventions then
life is so much easier.

I arranged things so that I have one server per database, I put the
server name in database.yml:

# This defines the common login info
login: &login
  adapter: mysql
  username: root
  password:
  host: localhost

db1:
  <<: *login
  database: project_db1
  app_server: host.domain.com:4101

db2:
  <<: *login
  database: project_db2
  app_server: host.domain.com:4102

and (re-)read that file with:

APP_CONFIG = YAML::load(File.open("#{RAILS_ROOT}/config/
database.yml"))

I can then use APP_CONFIG[] in a view:

<%= link_to h(project.name), "http://#{APP_CONFIG[project.name]
['app_server']}/my_action" %>

It's a bit hacky but I hope that it gives you some ideas.

Allan

Hi Francois,

Actually, it's not too hard IMHO. You certainly don't need a
different URL / app instance for each database. In fact, you can pick
a database and run the pending migrations from a before filter in just
a few lines of code. Here's a demo:

g@bang:~/tmpapp$ cat db/migrate/001_create_initial_tables.rb
class CreateInitialTables < ActiveRecord::Migration
  def self.up
    create_table :things do |t|
      t.column :name, :string
    end
  end

  def self.down
    drop_table :things
  end
end
g@bang:~/tmpapp$ cat app/models/thing.rb
class Thing < ActiveRecord::Base
end
g@bang:~/tmpapp$ cat app/controllers/things_controller.rb
require 'ostruct'

class ThingsController < ApplicationController
  before_filter :connect_to_database
  def connect_to_database
    config = session[:database] ||= ActiveRecord::Base.configurations[RAILS_ENV]
    ActiveRecord::Base.establish_connection(config)
    ActiveRecord::Migrator.migrate("db/migrate")
    @database = OpenStruct.new(config)
  end

  def set_database
    session[:database] = params[:database]
    redirect_to :action => 'list'
  end

  def list
    @things = Thing.find(:all)
    @thing = Thing.new
  end

  def create
    Thing.create(params[:thing])
    redirect_to :action => 'list'
  end
end
g@bang:~/tmpapp$ cat app/views/things/list.rhtml
<h2>Database</h2>
<% form_for :database, @database,
            :url => {:action => 'set_database'} do |form| %>
  <table>
    <tr><td>Adapter:</td><td><%= form.text_field 'adapter' %></td></tr>
    <tr><td>Database:</td><td><%= form.text_field 'database' %></td></tr>
    <tr><td>User:</td><td><%= form.text_field 'username' %></td></tr>
    <tr><td>Pass:</td><td><%= form.text_field 'password' %></td></tr>
  </table>
  <%= submit_tag 'Switch Database' %>
<% end %>
<hr />

<h2>Things</h2>
<table>
  <tr><th>ID</td><td>Name</td></tr>
  <% @things.each do |thing| %>
    <tr><td><%= thing.id %></td><td><%= thing.name %></td></tr>
  <% end %>
</table>

<h2>Create Thing</h2>
<% form_for :thing, @thing,
            :url => {:action => 'create'} do |form| %>
  Name: <%= form.text_field 'name' %>
  <%= submit_tag 'Create' %>
<% end %>

Hope that helps!

Regards,
George.

Thank you very much, I will try that :slight_smile: