I have a specific need to hack active record migrations for MySQL so that :timestamp fields will map to MySQL TIMESTAMP columns (instead of DATETIME like they do now). I first tried monkey-patching active record with an initializer, then with a gem that uses a railtie. After consistent failure (and after attending my local ruby user group meeting last night) it was suggested to me that I just try directly editing my copy of the active record gem to see if the stuff I’m changing is even correct for what I’m trying to accomplish.
I took their advice and directly edited the lib/active_record/connection_adapters/mysql_adapter.rb file like so:
NATIVE_DATABASE_TYPES = {
:primary_key => “int(11) DEFAULT NULL auto_increment PRIMARY KEY”.freeze,
:string => { :name => “varchar”, :limit => 255 },
:text => { :name => “text” },
:integer => { :name => “int”, :limit => 4 },
:float => { :name => “float” },
:decimal => { :name => “decimal” },
:datetime => { :name => “datetime” },
:timestamp => { :name => “timestamp” }, # “datetime” ==> “timestamp”
:time => { :name => “time” },
:date => { :name => “date” },
:binary => { :name => “blob” },
:boolean => { :name => “tinyint”, :limit => 1 }
}
The complete log of everything I did is here: http://gist.github.com/853701
It still doesn’t work. Their advice was good as clearly I’m not even changing the right stuff to make this happen!
Anyone know what I need to change so my :timestamp fields in my migrations will create TIMESTAMP columns in MySQL? If I can figure out what to change then I can make a monkey-patch to do it for my project.
Note that on the rails side of things, both rails db schema
definitions datatypes :datetime and :timestamp will result in values
returned as class Time for use in your rails app:
$ cat ..../active_record/connection_adapters/abstract/
schema_definitions.rb
...
# Returns the Ruby class that corresponds to the abstract data
type.
...
when :datetime then Time
...
when :timestamp then Time
...
Thus rails sees mysql db datatypes DATETIME and TIMESTAMP as basically
the same type of thing in rails: they're both just Times.
You didn't say what/why you needed mysql TIMESTAMP instead of
DATETIME, but I'm guessing you want/need to use it in order to also
use some mysql-specific TIMESTAMP features on the mysql side, like
DEFAULT CURRENT_TIMESTAMP, or ON UPDATE CURRENT_TIMESTAMP, or ....
If this is the case, what I'd probably do is just make those mysql-
specific calls (via exec) from within your migrations as needed, to
ALTER the table column to TIMESTAMP (from DATETIME) and set any other
mysql-specific things you need defined on that TIMESTAMP column in
mysql.
I’ll definitely be going with the custom, MySQL “alter table” route. As for the reason for doing this, it is a bit of an explanation:
the true production database is a legacy one that exists and backs an existing Microsoft Access 2003 based application.
the original developers of the Access app insist that for Access to use MySQL tables (via its “linked tables” mechanism) there must be a TIMESTAMP field w/the default of CURRENT_TIMESTAMP and the automatic ON UPDATE stuff in order for it to even work.
we’re writing a rails app to access this database
I want to develop, on my machine, using migrations that create these tables as they exist so I can test/verify my code against a test database that exactly matches the real one and that it plays nice with the access app
this way, once the rails app is done and we nuke the MS Access app (yay!) we can then continue to develop the live app, creating new migrations to continue altering our schema and life will be happier then
So you see, my requirements have nothing to do with rails specifically, but with playing nicely w/Microsoft Access. One of the other things I had to do (this is done already) is make all booleans get stored as -1 instead of 1. The joys of working w/MS Access. This isn’t to mention, of course, that the naming scheme of tables, fields, primary and foreign keys is nowhere close the the rails conventions (though that is easily dealt with in my models).
