I am trying to create database columns on the fly in mysql. Creating them has proven pretty easy. What I can’t figure out is how to check if the column already exists before I create it. So I don’t force a MySQL error.

Field.connection.execute("ALTER TABLE contents ADD #{@field.name.downcase!} VARCHAR(60);")

Content.find_by_ << "#{@field.name}(:first)"

show columns from contents; 
if not exists price 
   alter table contents add price varchar(60) 
end if;

def create
    @field = Field.new(params[:field])

    #Check if the supplied column already exists, if not create it.
    @find_column = Content.find_by_
    if @find_column.nil?
      Field.connection.execute("ALTER TABLE contents ADD #{@field.name.downcase!} VARCHAR(60);")
    end

    respond_to do |format|
      if @field.save
        flash[:notice] = 'Fields was successfully created.'
        format.html { redirect_to(@field) }
        format.xml  { render :xml => @field, :status => :created, :location => @field }
      else
        format.html { render :action => "new" }
        format.xml  { render :xml => @field.errors, :status => :unprocessable_entity }
      end
    end
  end

Seems like you’re doing some pretty funky stuff… I won’t ask why :)

Create a method that checks for the existence of the column first.

def column_exists?(table, column)
  column_check_sql = <<-SQL
    SELECT  column_name
    FROM    information_schema.columns
    WHERE   table_name = '#{table}'
    AND     table_schema = '#{ActiveRecord::Base.connection.current_database}'
    AND     column_name = '#{column}'
  SQL

  return ActiveRecord::Base.connection.execute(column_check_sql).num_rows == 1
end

Then, do your column creation code unless column_exists?

HTH… good luck.

Brian. Thanks for the help. I had seen the information_schema.columns in the MYSQL docs but wasn’t sure how to use it. I will try this out. Looks like it will work.

As for the funky stuff. I am working on a side project where some content will need to have web administrated custom fields for certain database objects. If that makes any sense, I am definitely open to a simpler approach.

Thanks again.

....execute(column_check_sql).num_rows == 0

This for some reason creates a column if that column is not already in that table. I am happy to have it working but I don’t completely understand the return value. does it return a 1 or 0 regardless of the result set or is num_rows just a limiter.

If it is a limiter it should work the way you wrote it.

unless column_exists?('contents', '#{@field.name}')
  Field.connection.execute("ALTER TABLE contents ADD #{@field.name} VARCHAR(60);")
end

Thanks for again for your time.

bohara,

Not totally sure what you mean by custom fields for certain objects, but if I can take a guess, perhaps linking to a simple key-value pair table would work instead? This would keep the schema the same instead of having to add columns.

For example, if you had a table like this:

TABLE: customizations

id      owner       key         value
---   -----       -----    --------
1      Product      "color"   "blue" 
2      Shipment   "track"   "1Z123456" 
3      Product      "size"   "large" 

then in your Product model:

has_many :customizations, :conditions => "owner = 'Product'" 

then you can do

p = Product.find(1)
p.customizations.create :key => "Rebate", :value => "20" 
p.customizations  # returns 3 rows now

Or am I way off base? Again just a shot in the dark, but maybe it helps spur some other thoughts?

Jeff

Jeff, That is a good thought. My previous description of what I am trying to do (or lack thereof) is that I am trying to create a cms that will allow users to create their own content model through a web interface that they can then use through standard ERB templates, and partials to create content driven sites more easily. It is actually a functionality I really love in expression engine, but I would like to use it in my rails applications, but I may be approaching this incorrectly for rails

sections - (Services, Products, About, Contact, etc.)                  
-------          
id                                    
name                 
meta_data          
....                     

fields
-------
id
section_id
field_type
searchable                   
etc....              

contents
---------
id
section_id
.....
custom created fields 

The contents table then ends up being built by the user through a web interface. The content entry form gets built from the fields available in the contents field for that section. When the form gets submitted it all gets slugged into the same table, as one row. I’m not sure what the performance tradeoffs would be have a very wide table versus assembling each entry from many rows. I think the MySQL column limit is 4096, not that you’d want to take it that far.

Love to hear what you think. I thought this model would keep the database calls to a minimum.

By the way you guys are doing are great job with the site, and the forums. It is appreciated.