AUTO_INCREMENT MySQL

There is a bug in MySQL that reset AUTO_INCREMENT if an index is added and the table is empty. I found a ticket regarding this issue in Ruby on Rails' bug tracker but it won't be fix because it is a MySQL bug.

Anyway, I reset AUTO_INCREMENT to my desire value after adding indexes to the table and that seems to work. Just remember to not add anymore indexes to the table after you set the AUTO_INCREMENT value or else it will be reset again.

Here is how to set the value manually.

execute "ALTER TABLE table1 AUTO_INCREMENT = 100"


added method to help create and drop foreign key

We have a naming convention at my work place for foreign key so I made two methods to help in the creation and dropping of foreign key. I made them because I got tired of typing out the query everytime.

We name our foreign key like this: fk_table_column_reference_table_column. So a foreign key from a "users" table to the "employers" table would look like this. fk_users_employer_id.

This is how you call the method to create a foreign key.


create_foreign_key(:users, :employer_id, :employers, :id)
drop_foreign_key(:users, :employer_id)



def create_foreign_key(table, column, foreign_table, foreign_column)
execute "ALTER TABLE #{table.to_s} ADD CONSTRAINT fk_#{table.to_s}_#{column.to_s} FOREIGN KEY (#{column.to_s}) REFERENCES #{foreign_table}(#{foreign_column})"
end

def drop_foreign_key(table, column)
execute "ALTER TABLE #{table.to_s} DROP FOREIGN KEY fk_#{table.to_s}_#{column.to_s}"
end


How to use migration

Yavor Ivanov over at rubycorner.net started a mini series on Rails migration. So far there is one article and I already learned a few things that I didn't know.

How to use Rails Migrations - Part I

I created a table with :created_at column as :null => false but I didn't define the default value so I was having problem with specifying the datetime in my test fixture.

I solved that problem by putting this in my test fixture:

created_at: <%= DateTime.now.strftime('%y-%m-%d %H:%M:%S') %>

What I really should have done is created the column like this.

t.column :created_at, :datetime, :default => Time.now

Drop Derby and goes back to MySql

After my rant yesterday about Derby not supporting column rename, I checked ActiveRecord-JDBC document for Derby and column_rename is not one of the supported feature.

Here is the list of databases and the level of support taken from the document.

• MySQL - Complete support
• PostgreSQL - Complete support
• Oracle - Complete support
• Microsoft SQL Server - Complete support except for change_column_default
• DB2 - Complete, except for the migrations:
  • change_column
  • change_column_default
  • remove_column
  • rename_column
  • add_index
  • remove_index
  • rename_table
• FireBird - Complete, except for change_column_default and rename_column
• Derby - Complete, except for:
  • change_column
  • change_column_default
  • remove_column
  • rename_column
• HSQLDB - Complete

Since MySql is completely supported, I switch to using MySql as the database back end for development.

I did notice one little problem with Netbeans today. The "Generate" menu item disappeared from the menu for some reason. I closed and reopened Netbeans but that didn't do anything. Finally, I closed the project, reopen it and the Generate menu item came back. It only happened once. I don't remember what I did to cause the problem though.

Derby does not support renaming column

-- rename_column(:users, :username, :user_name)
rake aborted!
rename_column is not implemented

Opps, I hit another snag when trying to rename username to user_name. Apparently, Derby does not allow renaming of column. I wiped out the database and started over but that is not a practical way to do thing going forward. I think my experiment with Derby is coming in an end here. I wonder why Sun is endorsing a database that is missing such fundamental feature. I don't remember using a database that does not support renaming of column. I guess there is a first for everything.

Now, I regretted that I read, "If You Thought Rails Development Was Fast Before...". I wouldn't have tried Derby without reading that article. It should be renamed to "If You Thought Rails Development Was Fast Before... Here Is How To Slow Yourself Down". I know it is a cheap shot but that article really make me think that I would be more productive using Derby as my development database.