Active Record Find Methods

Active Record find methods for selecting range from http://charlesmaxwood.com/notes-from-reading-activerecordbase/

Student.find(:all, :conditions => { :grade => 9..12 })
return a range
Student.find(:all, :conditions => { :grade => [9,11,12] })
will return an "in()"

Managing Timestamps in MySQL with a Trigger

MySQL doesn’t support having two columns with time stamping on both initialization and/or on updating at the same time. It would be nice to be able to do *this* where the created_at column gets the current_timestamp on initialization and the updated_at gets changed on updating the row.

# like so doesn't work...
create table entries(
  body blob,
  created_at datetime default current_timestamp,
  updated_at timestamp default current_timestamp on update current_timestamp
);

Seems like a feature a lot of folks would like. There are two work-arounds. The first is baking it into your application code with something like

create table entries(
  body blob,
  created_at datetime default null,
  updated_at timestamp default current_timestamp on update current_timestamp
);
insert into entries (body, created_at) values ('hello world', now());

The second way is to create a trigger and call the trigger on your insert action on a row.

create table entries (
  body  blob,
  created_at datetime default null,
  updated_at timestamp default null on update current_timestamp
);
create trigger init_created_at before insert on entries for each row set new.created_at = now();

Now whenever a new row is created the trigger will be executed and set the time to the current timestamp. You can forget about the created_at column in your code because it’s not meant to be changed.

Quick Syntax to Pipe an SQL Query Directly to a file

Here is a quick way to put the contents of a database table into a simple text file. This could be handy if for example, you just want to grab some emails and pop the results into a simple csv file. Your sql statement can be as creative as sql allows. All you are doing here is piping the query to mysql and then saving it to a file.

echo 'select concat(firstname, ', ', lastname, ', ', email) from email_subscribers' \
| mysql -uroot -p emails_database > emails.csv