I''m running into the need (on at least one project now) to implement
end-user-customizable "metadata" or properties on model objects. The
standard example would be a Person class that had first_name,
last_name, etc. but would need to be extended real-time (through the
web admin interface) with properties such as phone_number : varchar
(30). I''ve done some basic Googling and I can''t find any
references
to how to approach this. So I''m going to try to implement something
myself. Hopefully, this can be released to the community if it works.
Here''s the basic concept as I see it:
CREATE TABLE property(
id int not null primary key auto_increment,
ar_class varchar(255), -- AR class this property extends
property_name varchar(255),
property_type text -- MySQL or Ruby type
);
CREATE TABLE property_value(
id int not null primary key auto_increment,
foreign_id int not null, -- PK of the referenced object
property_id int not null, -- Property
has_many :property_values, PropertyValue belongs_to :property
property_value text not null -- YAML-serialized
(serialize :property_value)
);
so that for our example, we would have:
INSERT INTO property(ar_class,property_name,property_type) VALUES
(''Person'',''Phone
Number'',''varchar(255)'');
INSERT INTO property_value(foreign_id,property_id,property_value)
VALUES(<person_id>,<property_id as inserted above>,<YAML
serialization of ''123-456-7890''>);
Some areas for discussion:
- Is there an easier way to do this?
- Should I use one set of tables (as above) or do away with the
ar_class and have a person_property and person_property_value table?
- Would this best be implemented as an acts_as_, a plugin, an
ActiveRecord::Base extension, or what? Extending AR is a learning
experience for me.
Thanks!
--
Brad Ediger
866-EDIGERS
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails
> Some areas for discussion: > - Is there an easier way to do this? > - Should I use one set of tables (as above) or do away with the > ar_class and have a person_property and person_property_value table? > - Would this best be implemented as an acts_as_, a plugin, an > ActiveRecord::Base extension, or what? Extending AR is a learning > experience for me.I''d recommend creating a new table per user to keep the benefits of the database''s typing system. I''ve used a large system based on the structure you''ve outlined and it gets really hard to search really quickly. Using PostgreSQL''s triggers I''ve implemented a system to manage this for you, but I haven''t integrated it in to ActiveRecord yet. See http://www.sitharus.com/articles/2005/10/29/dynamic-user-defined- tables-in-postgresql -- Phillip Hutchings phillip.hutchings-QrR4M9swfipWk0Htik3J/w@public.gmane.org _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Thanks for the article. That looks interesting. I hadn''t really thought about modifying the database in realtime. Do you ever run into issues with data going out of date with your model? I don''t really need one table per user (my concept is a set of administrators modifying per-application fields for easy customization), but the concept should be the same. Looks good. -- Brad Ediger 866-EDIGERS On Nov 2, 2005, at 4:38 PM, Phillip Hutchings wrote:>> Some areas for discussion: >> - Is there an easier way to do this? >> - Should I use one set of tables (as above) or do away with the >> ar_class and have a person_property and person_property_value table? >> - Would this best be implemented as an acts_as_, a plugin, an >> ActiveRecord::Base extension, or what? Extending AR is a learning >> experience for me. >> > > I''d recommend creating a new table per user to keep the benefits of > the database''s typing system. I''ve used a large system based on the > structure you''ve outlined and it gets really hard to search really > quickly. > > Using PostgreSQL''s triggers I''ve implemented a system to manage > this for you, but I haven''t integrated it in to ActiveRecord yet. > See http://www.sitharus.com/articles/2005/10/29/dynamic-user- > defined-tables-in-postgresql > > -- > Phillip Hutchings > phillip.hutchings-QrR4M9swfipWk0Htik3J/w@public.gmane.org > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
On 3 Nov 2005, at 12:10, Brad Ediger wrote:> Thanks for the article. That looks interesting. > > I hadn''t really thought about modifying the database in realtime. > Do you ever run into issues with data going out of date with your > model? > > I don''t really need one table per user (my concept is a set of > administrators modifying per-application fields for easy > customization), but the concept should be the same.I haven''t addressed the ActiveRecord issues yet, I believe there will be some with the column caching, I haven''t had that much time to investigate it. Having an acts_as for it does seem like a good idea. I get some more done in the next few days, I''ll post it when I''ve got some progress done. -- Phillip Hutchings phillip.hutchings-QrR4M9swfipWk0Htik3J/w@public.gmane.org _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Brad,
I''m using something that I believe is very similar to what you are
looking
for. It might be alittle bit more of what you need because it has a concept
of groups of preferences so I can have groups like contact information,
accounting, etc. Hope it helps.
Here''s the schema:
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL auto_increment,
`region_id` int(10) unsigned NOT NULL default ''0'',
`username` varchar(128) NOT NULL default '''',
`password` varchar(128) NOT NULL default '''',
`first_name` varchar(64) NOT NULL default '''',
`last_name` varchar(64) NOT NULL default '''',
`email` varchar(128) NOT NULL default '''',
`name` varchar(255) default NULL,
`status` varchar(32) default NULL,
`created_on` datetime default NULL,
`updated_on` datetime default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `users_username` (`username`),
KEY `users_name` (`name`),
KEY `users_FKIndex1` (`region_id`)
);
CREATE TABLE `preferences` (
`id` int(10) unsigned NOT NULL auto_increment,
`user_id` int(10) unsigned NOT NULL default ''0'',
`group` varchar(255) default NULL,
`name` varchar(255) default NULL,
`value` varchar(255) default NULL,
`created_on` datetime default NULL,
`updated_on` datetime default NULL,
PRIMARY KEY (`id`),
KEY `preferences_FK_user` (`user_id`)
);
Here''s the code I have added to the user.rb model to manage preferences
for
each user:
# Relationships
has_many :preferences, :dependent => true, :order => "`group`,
name"
# Has preference?
def has_pref?(group, name)
logger.info <http://logger.info> "User : get_pref :: group:#{group}
name:#{name}"
get_pref(group, name) ? true : false
end
alias_method :has_preference?, :has_pref?
# Get preference
def get_pref(group, name, value = true)
logger.info <http://logger.info> "User : get_pref :: group:#{group}
name:#{name}"
p = Preference.find(:first, :conditions => ["`group` = ? AND name = ?
and
user_id = ?", group, name, id])
return p.value if p and value
return p if p and not value
return false unless p
end
alias_method :get_preference, :get_pref
# Get all preferences in a hash by the group name
def get_prefs(group, values = true)
logger.info <http://logger.info> "User : get_preferences ::
group:#{group}"
p = Preference.find(:all, :conditions => ["`group` = ? and user_id =
?",
group, id])
if p and values
arr = {}
p.each {|pr| arr[pr.name <http://pr.name>] = pr.value}
return arr
end
return p if p and not values
return false unless p
end
alias_method :get_preferences, :get_prefs
# Add preference
def add_pref(group, name, value="")
logger.info <http://logger.info> "User : add_pref :: group:#{group}
name:#{name}"
self.rem_pref(group, name)
begin
preferences.create(:group => group, :name => name, :value => value) ||
false
rescue
logger.error "User : add_pref :: could not create preference with
group:#{group} name:#{name}"
end
end
alias_method :set_pref, :add_pref
alias_method :add_preference, :add_pref
alias_method :set_preference, :add_pref
# Remove preference
def rem_pref(group, name)
logger.info <http://logger.info> "User : rem_pref :: group:#{group}
name:#{name}"
preferences.each do |p|
begin
preferences.delete(p) if p.group == group and p.name <http://p.name> ==
name
rescue
logger.error "User : rem_pref :: could not delete preference #{p}"
end
end
end
alias_method :del_pref, :rem_pref
alias_method :remove_pref, :rem_pref
alias_method :delete_pref, :rem_pref
alias_method :remove_preference, :rem_pref
alias_method :delete_preference, :rem_pref
# Delete preferences group
def delete_pref_group(group)
logger.info "User : delete_pref_group :: group:#{group}"
query = "DELETE FROM preferences WHERE user_id = #{self.id
<http://self.id>}
and `group` = ''#{group}''"
begin
ActiveRecord::Base.connection.execute query
rescue
logger.error "User : delete_pref_group :: could not delete preference group
#{group} because of an error"
end
end
alias_method :delete_preference_group, :delete_pref_group
Using these preferences I have added a convenience method to handle contact
information. Here''s the code that implements that taken again from the
user.rb model.
# Contact Information
# Returns a hash with contact information from the user''s preferences
def contact_information
default = { ''title'' => '''',
''direct'' => '''',
''office'' => '''', ''cell''
=> '''',
''fax'' => '''', }
current = self.get_prefs ''contact''
return default.merge(current)
end
# Sets the user''s contact information from a hash
def contact_information=(hash)
hash.each do |k, v|
self.add_pref ''contact'', k.to_s.strip, v.to_s.strip
end
end
What this code does is allows me to handle all available contact information
for my user in a hash both for retrieving and writing:
<%= @user.contact_information[''address''] %> # retrieving
@user.contact_information = params[''contact''] # writing
Again, hope this helps you somewhat.
--
Adrian Esteban Madrid
aemadrid-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails