possible to add columns to a database table from a controller? (not from a migration)

I am trying to figure out how to add columns to a database table from within a controller. I have a client who wants to be able to add their own attributes to a model from within the running application, so I don't think using migrations is an option.

Basically, I have a Person model which starts off with some basic attributes (like first_name and last_name), and my client wants to be able to add new attributes on the fly through the application (like birthdate or hometown, or anything he can think of). My plan for implementing this is to use another model (PersonField) to keep track of each new attribute's name, label, data type, and default value, and then tie the creation, updating, or destruction of these PersonField objects to the addition, changing, or removing of columns to the 'people' table in the database.

To restate it in another way, I'm looking for the equivalent to 'add_column' in a migration, but I'd like to call it from a controller.

I have not been able to find a solution to this problem, but I'm relatively new to Rails (just a few months experience), so I may not be looking in the right places.

I'd appreciate any help or suggestions anyone can give me. Thanks for reading this!

Looking around some more, I found this thread http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/bfb700028cf5e696# , where the poster was trying to do something similar. Generally, he was advised that this was a bad idea and might be better addressed in other ways, however, these other possible ways weren't discussed. I'm not at all committed to implementing this feature (custom information fields added on the fly from the application) in the way I outlined above; if there's a better way to handle it, that would be fine.

Basically, what my client wants is to be able to add new information fields to records about people. For example, he would like to be able to add a field for 'Home Town' to a Person record. Then, whenever he creates or edits a Person, he wants there to be a place to enter 'Home Town' information. Obviously this would be easy to do with a migration (just add a 'home_town' column to the 'people' table in the database), but he wants to be able to do this from within the running application.

It seems most natural to me to alter the database table structure, but apparently this is a bad idea because of caching that Rails does for you to improve performance. It sounds as though you can turn off model caching, but that this typically causes a significant performance hit. Is there a way to turn it off for just one model? In my case, the Person model is the only one which would need to be dynamically altered.

Thanks for any help or suggestions!

-Mike Bindschadler

You could run the execute method and pass it the query that creates the column

I’d personally create a table to hold these attributes, and add rows to it, rather than creating/dropping columns dynamically. That is I’d create an attribute model and give it a foreign key to the row in the other table, the name, and the value (and possibly a column for each possible type + a type column.)

I am trying to figure out how to add columns to a database table from within a controller. I have a client who wants to be able to add their own attributes to a model from within the running application, so I don't think using migrations is an option.

Basically, I have a Person model which starts off with some basic attributes (like first_name and last_name), and my client wants to be able to add new attributes on the fly through the application (like birthdate or hometown, or anything he can think of). My plan for implementing this is to use another model (PersonField) to keep track of each new attribute's name, label, data type, and default value, and then tie the creation, updating, or destruction of these PersonField objects to the addition, changing, or removing of columns to the 'people' table in the database.

To restate it in another way, I'm looking for the equivalent to 'add_column' in a migration, but I'd like to call it from a controller.

You can always execute arbitrary sql commands by using Model.find_by_sql

A better way may be to store these customs fields in the database:

Person   has_many :person_fields

PersonField   belongs_to :custom_field   belongs_to :person

CustomField   has_many :person_fields

Thanks for the suggestions! I'll chew on these some more when it's not 2am, but I think the idea from Miles Georgi sounds very promising. The bit about a column for each data type really helped the idea to begin to crystalize for me. Christopher Kintner's solution may be similar, but I don't see yet how having the CustomField model would help.

I'll post back when I've settled on something and have it working or if I hit another wall. Thanks again!

-Mike

The reason there's no easy method for doing what you want to do is because it's a terrible idea! Letting the customer arbitrarily change the structure of the database is guaranteed to get you an unstable database.

You already have a second table in mind to store data about the arbitrary fields the customer wants to create. Have a third table that stores the data and includes pointers back to the attribute table and the persons table. You can set this sort of thing up easily with a has_many :through relationship. This is much more stable and requires no special programming. The customer can create or delete data fields on the fly, use them inconsistently or not at all, and the data remain easily accessible.

Thanks for all the help! I ended up doing a slight variation on the solutions suggested here. The client wanted every Person to have the same fields rather than adding single custom fields to an individual person, so I dropped the has_many :people_fields from the Person model (since all people have all PeopleFields). In outline it looks something like this:

class Person < ActiveRecord::Base has_many :custom_people_field_values

class CustomPeopleField < ActiveRecord::Base has_many :custom_people_field_values

class CustomPeopleFieldValue < ActiveRecord::Base belongs_to :person belongs_to :custom_people_field

In the custom_people_fields table there are columns for the field name, label, data type, default value, and order of appearance. In the custom_people_field_values table there are id columns for person and custom people field, as well as columns for data type and one column for each allowed data type (to hold the appropriate value in the appropriate format, as suggested by Miles Georgi above).

I wanted to be able to refer to custom fields just like regular fields, so if 'hometown' was a custom-created field, I wanted to be able to do things like this dave = Person.new dave.hometown = 'Seattle' current_town = dave.hometown

To set this up, I added a method_missing method to the Person model which looks something like this

def method_missing(m, *args) if <m corresponds to any field_name of a CustomPeopleField>    if <there is an equals sign on the end of the method>       # create a new or update a current CustomPeopleFieldValue which points to the current Person and the matching CustomPeopleField with the requested value    else # there's not an = on the end of the method call       if <there exists a CustomPeopleFieldValue for the current Person and the matching field>          # return the existing value       else          # return the default value (or nil if no default value) for this CustomPeopleField       end     end else # there was no matching field, just treat it like a regular missing method    super end

Hope this helps someone else in similar predicament! And thanks again to everyone who responded!

Very good idea. I was searching some solution exactly like this. Thanks for the detailed suggestion!