Hi, I wonder how much of the basecamp database model or it''s philosophy has been made public. It seem like if we are using the framework extracted from the Basecamp app it would be good to know a little about the database model structure. In particular I wonder how multiple projects are handled for one account. I would like to have multiple storefronts for an ecommerce application and think this might be similar to multiple projects in basecamp. Thanks, Peter Michaux store.rb - http://trac.vaillant.ca/store.rb
> I wonder how much of the basecamp database model or it''s philosophy has > been made public. It seem like if we are using the framework extracted > from the Basecamp app it would be good to know a little about the > database model structure. In particular I wonder how multiple projects > are handled for one account. I would like to have multiple storefronts > for an ecommerce application and think this might be similar to > multiple projects in basecamp.A few clues: * http://wiki.rubyonrails.com/rails/pages/HowToUseSubdomainsAsAccountKeys * http://dev.rubyonrails.org/svn/rails/plugins/account_location/ This is naturally not verbatim, but for this discussion it''ll serve: class Account < ActiveRecord::Base has_many :projects end class Project < ActiveRecord::Base belongs_to :account has_many :milestones end class Milestone < ActiveRecord::Base belongs_to :project end So you can say Account.find(:first).projects.first.milestones to get all the milestones of the first project that belongs to the first account. -- David Heinemeier Hansson http://www.loudthinking.com -- Broadcasting Brain http://www.basecamphq.com -- Online project management http://www.backpackit.com -- Personal information manager http://www.rubyonrails.com -- Web-application framework
Hi David, Thanks for the reply. What you are saying makes me think that ALL the accounts for ALL the projects for the entire basecamp website are stored in the same database. Wow! I thought there would be one database for every subdomain/account. So it is true? Just one humongous database? Peter store.rb - http://trac.vaillant.ca/store.rb On 30-Oct-05, at 2:39 AM, David Heinemeier Hansson wrote:>> I wonder how much of the basecamp database model or it''s philosophy >> has >> been made public. It seem like if we are using the framework extracted >> from the Basecamp app it would be good to know a little about the >> database model structure. In particular I wonder how multiple projects >> are handled for one account. I would like to have multiple storefronts >> for an ecommerce application and think this might be similar to >> multiple projects in basecamp. > > A few clues: > > * > http://wiki.rubyonrails.com/rails/pages/ > HowToUseSubdomainsAsAccountKeys > * http://dev.rubyonrails.org/svn/rails/plugins/account_location/ > > This is naturally not verbatim, but for this discussion it''ll serve: > > class Account < ActiveRecord::Base > has_many :projects > end > > class Project < ActiveRecord::Base > belongs_to :account > has_many :milestones > end > > class Milestone < ActiveRecord::Base > belongs_to :project > end > > So you can say Account.find(:first).projects.first.milestones to get > all the milestones of the first project that belongs to the first > account. > -- > David Heinemeier Hansson > http://www.loudthinking.com -- Broadcasting Brain > http://www.basecamphq.com -- Online project management > http://www.backpackit.com -- Personal information manager > http://www.rubyonrails.com -- Web-application framework > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Oct 30, 2005, at 8:06 AM, Peter Michaux wrote:> Thanks for the reply. What you are saying makes me think that ALL > the accounts for ALL the projects for the entire basecamp website > are stored in the same database. Wow! I thought there would be one > database for every subdomain/account. So it is true? Just one > humongous database?I find it very strange that you find this strange. Can you imagine Visa making a separate database for every customer? Regards, jeremy -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (Darwin) iD8DBQFDZP/WAQHALep9HFYRAmLUAJ41vOZi+KRuWpXI/gioBY+f0cGL/QCeNDwO 0a6k1L5ragcXyvL6kQL3bhU=IdEW -----END PGP SIGNATURE-----
In article <4578F8A8-364F-46C3-B45E-FC20D7A87572-w7CzD/W5Ocjk1uMJSBkQmQ@public.gmane.org>, jeremy- w7CzD/W5Ocjk1uMJSBkQmQ-XMD5yJDbdMReXY1tMh2IBg@public.gmane.org says...> On Oct 30, 2005, at 8:06 AM, Peter Michaux wrote: > > Thanks for the reply. What you are saying makes me think that ALL > > the accounts for ALL the projects for the entire basecamp website > > are stored in the same database. Wow! I thought there would be one > > database for every subdomain/account. So it is true? Just one > > humongous database? > > I find it very strange that you find this strange. > > Can you imagine Visa making a separate database for every customer?Well-put. This is actually the wonder of one-to-many relations; you can assign each customer an ID, and by using Account.find instead of Project.find, everybody appears to have their own virtual project database. You MUST understand that before you can hope to harness databases. This is how mail works at AOL, by far the largest mail system - there''s one row for every message sent, and with the power of SQL relations, it appears in the mailbox of every sender and recipient. For the most part, that "mailbox" is not a real construct, but simply a SELECT query by screen name. Of course, this one logical database is partitioned and replicated under the covers, but the database engine takes care of that. (They''re using hardware that actually implements SQL transactions down at the drive controller level.) This handles 4,000 messages a second, no problem. In fact, it''s significantly better-performing than the old system, which used a one- record-per-mailbox file that directly dumped the in-memory structure to disk. Visa''s at least an order of magnitude larger, but I''d assume a similar layout - a single logical database for all transactions, indexed by both card number and merchant ID. -- Jay Levitt | Wellesley, MA | I feel calm. I feel ready. I can only Faster: jay at jay dot fm | conclude that''s because I don''t have a http://www.jay.fm | full grasp of the situation. - Mark Adler
>> are stored in the same database. Wow! I thought there would be one >> database for every subdomain/account. So it is true? Just one >> humongous database? > > I find it very strange that you find this strange. > > Can you imagine Visa making a separate database for every customer?No kidding. One database per customer would be nuts. That''s why god created user authentication and ACLs. I''d guess that a one-db-per- customer approach would be incredibly rare and a nightmare to maintain; what situations would require it?
Interestingly the only place I''ve seen this sort of incredibly bad design utilized publicly in the wild is on http://wordpress.com, which is powered by Wordpress-mu (http://mu.wordpress.org/). One database per blog. The developers actually defend the design with some sort of half-baked scalability argument. /me runs for Typo. On Oct 30, 2005, at 1:37 PM, Andrew Otwell wrote:> > >>> are stored in the same database. Wow! I thought there would be one >>> database for every subdomain/account. So it is true? Just one >>> humongous database? >>> >> >> I find it very strange that you find this strange. >> >> Can you imagine Visa making a separate database for every customer? >> > > No kidding. One database per customer would be nuts. That''s why god > created user authentication and ACLs. I''d guess that a one-db-per- > customer approach would be incredibly rare and a nightmare to > maintain; what situations would require it? > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
On 10/30/05, Andrew Otwell <andrew-uQjPo4GTFqgS+FvcfC7Uqw@public.gmane.org> wrote:> > >> are stored in the same database. Wow! I thought there would be one > >> database for every subdomain/account. So it is true? Just one > >> humongous database? > > > > I find it very strange that you find this strange. > > > > Can you imagine Visa making a separate database for every customer? > > No kidding. One database per customer would be nuts. That''s why god > created user authentication and ACLs. I''d guess that a one-db-per- > customer approach would be incredibly rare and a nightmare to > maintain; what situations would require it?The easiest answer to that would be for individually hosted setups. Basecamp is run as a service, so it makes sense that it''s just one large database. Most store owners don''t like to be locked into a host. Granted, they''ll be limited due to the newness of Rails. I agree with the store owners, I''d rather have a shopping cart that''s portable ala osCommerce and Miva Merchant instead of a service such as Yahoo Stores. The biggest benefits you gain are the ability to work on a local version of your store, and also the ability to customize it beyond the original design''s limitations. That said, the original question was about multiple stores on the same server. My experience* has been that the demand for that type of setup isn''t large enough to warrant it being a core feature. For those who need it, you could just install multiple stores on the same server. * I''ve been doing e-commerce for 8 years now, and I''ve worked on well over 100 stores -- none of which ran as a ''mall'', although I''ve run into one or two users who are running malls on the the related mailing lists.> _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Bill Guindon (aka aGorilla)
Andrew Otwell wrote:> No kidding. One database per customer would be nuts. That''s why god > created user authentication and ACLs. I''d guess that a one-db-per- > customer approach would be incredibly rare and a nightmare to > maintain; what situations would require it?Well, for one, when the customer''s data is stored on the *customer''s* system and the customer is unwilling to share it. :) -- M. Edward (Ed) Borasky http://linuxcapacityplanning.com
Heya :)> -----Original Message----- > From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of > Jeremy Kemper > Sent: Sunday, October 30, 2005 12:16 PM > To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > Subject: Re: [Rails] Basecamp database model > > I find it very strange that you find this strange. > > Can you imagine Visa making a separate database for every customer?No, I can''t - but the situation is not always the same. For instance I >can< see some hosted applications where data security was crucial hosting clients as independent databases especially if the ratio of data to customers is very large. So visa? No. But a full on hosted CMS application? sure. There are advantages: 1) No concievable code error (after set-up) will accidentally allow for the exposure of another clients data 2) Customizations for individual clients is never going to impact other clients 3) Individual client backup / restore, potentially under client control, is trivial 4) Balancing clients across clusters becomes trivial 5) Phased roll-outs of upgrades and new versions / schema changes become spossible without a problem For some applications one database for many clients makes sense, for others one db per client is a good choice. As with so many things, this simply "depends". Soulhuntre ---------- http://www.girl2.com - my girls http://www.the-estate.com - my legacy http://wiki.thegreybook.com - my project http://weblog.soulhuntre.com - my thoughts
On 10/30/05 3:39 AM, "David Heinemeier Hansson" <david.heinemeier-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:>> I wonder how much of the basecamp database model or it''s philosophy has >> been made public. It seem like if we are using the framework extracted >> from the Basecamp app it would be good to know a little about the >> database model structure. In particular I wonder how multiple projects >> are handled for one account. I would like to have multiple storefronts >> for an ecommerce application and think this might be similar to >> multiple projects in basecamp. > > A few clues: > > * http://wiki.rubyonrails.com/rails/pages/HowToUseSubdomainsAsAccountKeys > * http://dev.rubyonrails.org/svn/rails/plugins/account_location/ > > This is naturally not verbatim, but for this discussion it''ll serve: > > class Account < ActiveRecord::Base > has_many :projects > end > > class Project < ActiveRecord::Base > belongs_to :account > has_many :milestones > end > > class Milestone < ActiveRecord::Base > belongs_to :project > end > > So you can say Account.find(:first).projects.first.milestones to get > all the milestones of the first project that belongs to the first > account. > -- > David Heinemeier Hansson > http://www.loudthinking.com -- Broadcasting Brain > http://www.basecamphq.com -- Online project management > http://www.backpackit.com -- Personal information manager > http://www.rubyonrails.com -- Web-application framework > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/railsHow do you elegantly prevent someone from accessing records that belong to other accounts? Sergio B
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Oct 31, 2005, at 9:06 AM, Sergio Bayona wrote:> On 10/30/05 3:39 AM, "David Heinemeier Hansson" > <david.heinemeier-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > wrote: >> So you can say Account.find(:first).projects.first.milestones to get >> all the milestones of the first project that belongs to the first >> account. > > How do you elegantly prevent someone from accessing records that > belong to > other accounts?See the code snippet quoted above. By following associations from the owner you will only ever see the owner''s records. jeremy -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (Darwin) iD8DBQFDZmBOAQHALep9HFYRAku9AJ9I4O4n4EVz9ZNsUiC0Pl4mbCSBaACdGOXL qxbAJ7jmzvr21yz3/59CwIo=yIOK -----END PGP SIGNATURE-----
On 31-Oct-05, at 11:06 AM, Sergio Bayona wrote:> > How do you elegantly prevent someone from accessing records that > belong to > other accounts? >I''ve created the following code which I''ve stuck into a plugin. It overrides the ActiveRecord conditions to always filter based on the account (or site in my case). I can just add restrict_to_site to each model as appropriate. The filter can then be overriden (in admin code, for example) with override_site_filter, such as: Section.find(:all, :conditions=>{"override_site_filter"=>true}). This code also sets the appropriate site value to new records, so that happens automatically as well. This works, but the problem with this code are several: * It is based on an earlier version of activerecord -- I see the add_conditions! has changed in the latest source. It still works, but should be updated. * It requires a global $site variable to be set (I do it in a pre filter in the application controller). * the override condition is ugly -- better syntax or way of doing it? * It could be generalized -- account/site, for example, or custom restriction conditions.... something like: restrict_to :site, :condition=>"Custom restrict condition here if necessary, default would look for a foreign key" If someone were to tackle these issues and make a generalized plugin, that would be super cool.... Regards, Wayne class ActiveRecord::Base private # Adds a sanitized version of +conditions+ to the +sql+ string. Note that it''s the passed +sql+ string is changed. def self.add_conditions!(sql, conditions) override_site_filter = conditions.delete("override_site_filter") unless conditions.nil? where = conditions.blank? ? "" : "WHERE #{sanitize_sql(conditions)} " where << (where.blank? ? "WHERE " : " AND ") + " (#{restrict_condition}) " unless override_site_filter or restrict_condition.blank? where << (where.blank? ? "WHERE " : " AND ") + type_condition unless descends_from_active_record? sql << where end end ActiveRecord::Base.class_eval do def self.restrict_to_site include SiteFilter::ModelMethods end end module SiteFilter module ModelMethods def self.included(base) base.class_eval do before_validation_on_create :set_site end base.extend(ClassMethods) end def set_site if $site.nil? then raise "Site Not Found" end self.site_id = $site.id end module ClassMethods def restrict_condition if $site.nil? then raise "Site Not Found" end $site.id.nil? ? "" : "site_id = ''#{$site.id}''" end end end end
On 10/31/05, Wayne Larsen <wayne-NiStyETRz7leoWH0uzbU5w@public.gmane.org> wrote:> > On 31-Oct-05, at 11:06 AM, Sergio Bayona wrote: > > > > How do you elegantly prevent someone from accessing records that > > belong to > > other accounts? > > > I''ve created the following code which I''ve stuck into a plugin. It > overrides the ActiveRecord conditions to always filter based on the > account (or site in my case). I can just add restrict_to_site to each > model as appropriate. The filter can then be overriden (in admin code, > for example) with override_site_filter, such as: Section.find(:all, > :conditions=>{"override_site_filter"=>true}). This code also sets the > appropriate site value to new records, so that happens automatically as > well. > > This works, but the problem with this code are several: > * It is based on an earlier version of activerecord -- I see the > add_conditions! has changed in the latest source. It still works, but > should be updated. > * It requires a global $site variable to be set (I do it in a pre > filter in the application controller). > * the override condition is ugly -- better syntax or way of doing it? > * It could be generalized -- account/site, for example, or custom > restriction conditions.... something like: > restrict_to :site, :condition=>"Custom restrict condition here if > necessary, default would look for a foreign key" > > If someone were to tackle these issues and make a generalized plugin, > that would be super cool....Post.constrain(:conditions => ''blog_id = 5'') do @posts = Post.find :all end -- rick http://techno-weenie.net
On 31-Oct-05, at 12:32 PM, Rick Olson wrote:> On 10/31/05, Wayne Larsen <wayne-NiStyETRz7leoWH0uzbU5w@public.gmane.org> wrote: >> >> On 31-Oct-05, at 11:06 AM, Sergio Bayona wrote: >>> >>> How do you elegantly prevent someone from accessing records that >>> belong to >>> other accounts? >>> >> I''ve created the following code which I''ve stuck into a plugin. It >> overrides the ActiveRecord conditions to always filter based on the >> account (or site in my case). >> >> If someone were to tackle these issues and make a generalized plugin, >> that would be super cool.... > > Post.constrain(:conditions => ''blog_id = 5'') do > @posts = Post.find :all > end >Sure, but you need to remember to do that every time it is used. And if the constrained case is the common case, and chanting the mantra of DRY, I would rather be able to do: @posts = Post.find :all, and have the constraint built in. Wayne
Jeremy Kemper wrote:> On Oct 31, 2005, at 9:06 AM, Sergio Bayona wrote: > >>> On 10/30/05 3:39 AM, "David Heinemeier Hansson" >>> <david.heinemeier-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> >>> wrote: >>> >>>> So you can say Account.find(:first).projects.first.milestones to get >>>> all the milestones of the first project that belongs to the first >>>> account. >>> >>> >>> How do you elegantly prevent someone from accessing records that >>> belong to >>> other accounts? > > > See the code snippet quoted above. By following associations from the > owner you will only ever see the owner''s records.Sure, but at some point you''ll be processing an object id sent as a CGI param, albeit one you got by following associations. You do need to validate that somehow, especially with the usual tempting Rails-style urls: /supersekritinfo/showitalltome/1 (I''m not trying to suggest these urls are a bad idea, mind.) It''d be ideal to state for a controller that firstly, some sort of authenticated user context should exist, and further, that all find calls should only return that user''s accessible objects, per the relevant ACLs. This is what all these authorisation plugins (ModelSecurity, ActiveRBAC, others) are doing, right? Chris.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Oct 31, 2005, at 12:22 PM, Chris Andrews wrote:> Sure, but at some point you''ll be processing an object id sent as a > CGI > param, albeit one you got by following associations. You do need to > validate that somehow, especially with the usual tempting Rails- > style urls:Yes; validate it by walking the associations. Introducing heavyweight access-control frameworks doesn''t seem appropriate if this is the only need to satisfy. For /foobars/show/1 do user.foobars.find(params[:id]) instead of Foobar.find(params[:id]) This is easier on my brain than new APIs and configuration. jeremy -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (Darwin) iD8DBQFDZoBEAQHALep9HFYRAqs5AJwJFlX6oG16FyKiFzhyKS8+qJKS3wCg2f16 iXL56T68iwh5Fjq24M8HrbM=TnXq -----END PGP SIGNATURE-----
Tobias and Jeremy you''ve open a whole new world of possibilities I didn''t know existed and I thank you for that. Sergio On 10/31/05 3:49 PM, "Tobias Luetke" <tobias.luetke-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:>> Yes; validate it by walking the associations. Introducing >> heavyweight access-control frameworks doesn''t seem appropriate if >> this is the only need to satisfy. >> >> For /foobars/show/1 do >> user.foobars.find(params[:id]) >> instead of >> Foobar.find(params[:id]) > > > In Shopify there are over 30 tables which store a shop_id. The Shop > object is figured out at the beginning of each request by looking at > the incoming domain in a before filter and all the db queries are > scoped over it. > > Product.find(:all) becomes shop.products.find(:all) > Product.new becomes shop.products.build > > Since rails 0.13.1 we support calling class methods over associations. > This is actually a direct extraction out of shopify and makes such > databases much easier to handle. > > Little example: > > class Product < AR:B > def self.search(q) > find(:all, :conditions => "title LIKE ''%#{q}%''") > end > end > > Product.search("snowboard") will search in the entire database as > normal but shop.products.search("snowboards") will only search the > products with the appropriate shop_id. This same code allows you to > call dynamic finders on associations too like this : > shop.products.find_by_type(''snowboard''). > > I think because of those features ActiveRecord is in fact the best > tool to create such scoped databases with minimal work. > > Hope this helps. > > -- > Tobi > http://jadedpixel.com - modern e-commerce software > http://typo.leetsoft.com - Open source weblog engine > http://blog.leetsoft.com - Technical weblog > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails
> Yes; validate it by walking the associations. Introducing > heavyweight access-control frameworks doesn''t seem appropriate if > this is the only need to satisfy. > > For /foobars/show/1 do > user.foobars.find(params[:id]) > instead of > Foobar.find(params[:id])In Shopify there are over 30 tables which store a shop_id. The Shop object is figured out at the beginning of each request by looking at the incoming domain in a before filter and all the db queries are scoped over it. Product.find(:all) becomes shop.products.find(:all) Product.new becomes shop.products.build Since rails 0.13.1 we support calling class methods over associations. This is actually a direct extraction out of shopify and makes such databases much easier to handle. Little example: class Product < AR:B def self.search(q) find(:all, :conditions => "title LIKE ''%#{q}%''") end end Product.search("snowboard") will search in the entire database as normal but shop.products.search("snowboards") will only search the products with the appropriate shop_id. This same code allows you to call dynamic finders on associations too like this : shop.products.find_by_type(''snowboard''). I think because of those features ActiveRecord is in fact the best tool to create such scoped databases with minimal work. Hope this helps. -- Tobi http://jadedpixel.com - modern e-commerce software http://typo.leetsoft.com - Open source weblog engine http://blog.leetsoft.com - Technical weblog
On 31-okt-2005, at 23:49, Tobias Luetke wrote:>> Yes; validate it by walking the associations. Introducing >> heavyweight access-control frameworks doesn''t seem appropriate if >> this is the only need to satisfy. >> >> For /foobars/show/1 do >> user.foobars.find(params[:id]) >> instead of >> Foobar.find(params[:id]) >> > > > In Shopify there are over 30 tables which store a shop_id. The Shop > object is figured out at the beginning of each request by looking at > the incoming domain in a before filter and all the db queries are > scoped over it.So that''s why we have collection.find... I didn''t realize -- Julian "Julik" Tarkhanov