As part of a learning exercise, although I would like this to become a real application in the long run, I am trying to set up an accounting application, for this application I need the users to be able to create and use different Companies (Sets of books). For maintenance reasons I would rather keep the data for each company seperate in some way i.e. seperate tables with prefixes or a seperate database. Having read up a bit on using multiple database connections I think I would rather stick with one database and prefix the table names. There are some ''application wide'' tables for which I don''t want the prefix one of which is the table that holds the different companies that have been added. I already have code which creates the set of prefixed tables from a set of template tables by using execute(sql) I hope to improve this later by using schema/migration but for now executing the raw sql statements is working. My best guess after trying a few things is to use a before_filter in the application.rb and my simple tests with one table prove that this could work. (I am using a session variable to define which ''company'' is the one currently used). I suppose I could set up a hash/array to define which models the table names need to be set for but I would prefer this to a bit more dynamic i.e. not relying on me to remember to put the model/table in my array/hash in application.rb. I would rather have this prefixing in the model but I can''t figure out how to get at the session variable for set_table_name in the model(s). Any suggestions are welcome, I know I could just use tables with a company column and filter all my finds appropiately but this would lead to huge, more difficult to maintain, transaction tables in the long run. --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
JermWorm wrote:> As part of a learning exercise, although I would like this to become a > real application in the long run, I am trying to set up an accounting > application, for this application I need the users to be able to create > and use different Companies (Sets of books). > > For maintenance reasons I would rather keep the data for each company > seperate in some way i.e. seperate tables with prefixes or a seperate > database. Having read up a bit on using multiple database connections I > think I would rather stick with one database and prefix the table > names. > > There are some ''application wide'' tables for which I don''t want the > prefix one of which is the table that holds the different companies > that have been added. I already have code which creates the set of > prefixed tables from a set of template tables by using execute(sql) I > hope to improve this later by using schema/migration but for now > executing the raw sql statements is working. > > My best guess after trying a few things is to use a before_filter in > the application.rb and my simple tests with one table prove that this > could work. (I am using a session variable to define which ''company'' is > the one currently used). I suppose I could set up a hash/array to > define which models the table names need to be set for but I would > prefer this to a bit more dynamic i.e. not relying on me to remember to > put the model/table in my array/hash in application.rb. > > I would rather have this prefixing in the model but I can''t figure out > how to get at the session variable for set_table_name in the model(s). > > Any suggestions are welcome, I know I could just use tables with a > company column and filter all my finds appropiately but this would lead > to huge, more difficult to maintain, transaction tables in the long run.I think you should reconsider the "company column" alternative. A well indexed database shouldn''t be that hard to query/mantain. --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
Seeing as how the only answer so far isn''t the one I wanted, I had
another look at using multiple databases and came up with a solution to
use a different database for each company.
Similar to the ''External'' example in Rails Recipes It involves
setting
up a ''connection'' class for the other db connections and
subclassing my
my models from there. When I choose the company to work with the
connection is (re)established.
#app/models/comp_conn.rb
class Comp_conn < ActiveRecord::Base
# attr_accessible :dbname
@@db=''''
self.abstract_class = true
def Comp_conn.open(dbname)
@@db=dbname
establish_connection(:adapter => "mysql",
:host => "localhost",
:username => "ruby",
:password => "itsahugesecretwooo",
:database => @@db
)
end
end
#app/models/test.rb
class Test < Comp_conn
end
A snippet from a controller could look like this:
def open_company
id = params[:id]
if @company = Accset.find(id)
@session[:curraccset] = @company.code
@session[:company_id] = @company.id
Comp_conn::open(@session[:curraccset])
redirect_to(:controller => ''company'', :action =>
''index'')
else
flash[:notice]="Invalid Company"
redirect_to(:action => ''choose_company'')
end
end
I was watching mysql processes whilst testing this and it appears to be
closing and opening connections correctly as I choose different
datasets.
I don''t know if those who aren''t complete newbies like me can
spot any
problems in using this method, one that immediately comes to mind but
doesn''t seem to affect my simple testing is caching of data. It might
be worth mentioning that my ''main'' database contains
''templates'' of the
tables that would go into a new company once created.
Yes I''ll have to write something to keep the table structures of my
companies in line with the development set but since I''ve done this
sort of thing in my foxpro application on which I''m basing my self
learning I don''t foresee any huge difficulty. Something like check the
version of the tables when opening and offer to upgrade the tables
and/or refusing to open an unmatched version. Or an admin tool that can
be run when required.
--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---