Rubyのコレクション系のメソッドがそのまま意味的に等価なSQLを生成し、データベースにアクセスするAmbitionって面白いですね。テーブル結合はどうなるのだろうと思いビートルズのディスコグラフィーを作って試してみました。
まずはアルバムのデータ作成
create table albums(
id integer not null auto_increment,
name varchar(50),
year smallint,
primary key(id)
);
insert into albums(name, year)values('Please Please Me', 1963);
insert into albums(name, year)values('With the Beatles', 1963);
insert into albums(name, year)values('A Hard Day\'s Night', 1964);
insert into albums(name, year)values('Beatles for Sale', 1964);
insert into albums(name, year)values('Help!', 1965);
insert into albums(name, year)values('Rubber Soul', 1965);
insert into albums(name, year)values('Revolver', 1966);
insert into albums(name, year)values('Sgt. Pepper\'s Lonly Hears Club Band', 1967);
insert into albums(name, year)values('The Beatles', 1968);
insert into albums(name, year)values('Yellow Submarine', 1969);
insert into albums(name, year)values('Abbey Road', 1969);
insert into albums(name, year)values('Let it Be', 1970);
次は曲のデータ、「ビートルズがやってきた」までです。ぁ〜なつかしいな！！
create table songs(
id integer not null auto_increment,
name varchar(50),
album_id integer not null,
primary key(id),
foreign key(album_id) references albums(id)
);
insert into songs(name, album_id)values('I Saw Her Standing There', 1);
insert into songs(name, album_id)values('Anna (Go to Him)', 1);
insert into songs(name, album_id)values('Misery', 1);
insert into songs(name, album_id)values('Boys', 1);
insert into songs(name, album_id)values('Ask Me Why', 1);
insert into songs(name, album_id)values('Please Please Me', 1);
insert into songs(name, album_id)values('Love Me Do', 1);
insert into songs(name, album_id)values('P.S. I Love Yoe', 1);
insert into songs(name, album_id)values('Baby It\'s You', 1);
insert into songs(name, album_id)values('Do You Want to Know a Secret', 1);
insert into songs(name, album_id)values('A Taste of Honey', 1);
insert into songs(name, album_id)values('There\'s a Place', 1);
insert into songs(name, album_id)values('Twist and Shout', 1);
insert into songs(name, album_id)values('It Won\'t Be Long', 2);
insert into songs(name, album_id)values('All I\'ve Got To Do', 2);
insert into songs(name, album_id)values('All My Loving', 2);
insert into songs(name, album_id)values('Don\'t Bother Me', 2);
insert into songs(name, album_id)values('Little Child', 2);
insert into songs(name, album_id)values('Till There Was You', 2);
insert into songs(name, album_id)values('Please Mister Postman', 2);
insert into songs(name, album_id)values('Roll Over Beethoven', 2);
insert into songs(name, album_id)values('Hold Me Tight', 2);
insert into songs(name, album_id)values('You Really Got A Hold On Me', 2);
insert into songs(name, album_id)values('I Wanna Be Your Man', 2);
insert into songs(name, album_id)values('Devil I Her Heart', 2);
insert into songs(name, album_id)values('Not A Second Time', 2);
insert into songs(name, album_id)values('Money', 2);
insert into songs(name, album_id)values('A Hard Day\'s Night', 3);
insert into songs(name, album_id)values('I Should Have Known Better', 3);
insert into songs(name, album_id)values('If I Fell', 3);
insert into songs(name, album_id)values('I\'m Happy Just to Dance With You', 3);
insert into songs(name, album_id)values('And I Love Her', 3);
insert into songs(name, album_id)values('Tell Me Why', 3);
insert into songs(name, album_id)values('Can\'t Buy Me Love', 3);
insert into songs(name, album_id)values('Any Time At All', 3);
insert into songs(name, album_id)values('I\'ll Cry Instead', 3);
insert into songs(name, album_id)values('Things We Said Today', 3);
insert into songs(name, album_id)values('When I Get Home', 3);
insert into songs(name, album_id)values('You Can\'t Do That', 3);
insert into songs(name, album_id)values('I\'ll Be Back', 3);
スクリプトを作成し実行してみます。
require 'rubygems'
require 'active_record'
require 'ambition'
ActiveRecord::Base.establish_connection(
:adapter => 'mysql',
:database => 'sampledb',
:host => 'localhost',
:username => 'myadmin',
:password => 'xxxxxx'
)
# テーブルが複数形なのに対してクラスは単数形
class Album < ActiveRecord::Base
end
class Song < ActiveRecord::Base
belongs_to :album
def to_s
return sprintf("%-30s [%s]", name, album.name)
end
end
puts Song.select{|s| s.album.name =~ 'Please%'}
#SELECT * FROM songs JOIN album WHERE albums.name LIKE 'Please%'
# SQL変ですね!!
Song.select{|s| s.album.name =~ 'Please%'}.each{|s| print s, s.album.year, "\n"}
#I Saw Her Standing There [Please Please Me]1963
#Anna (Go to Him) [Please Please Me]1963
#Misery [Please Please Me]1963
#Boys [Please Please Me]1963
#Ask Me Why [Please Please Me]1963
#Please Please Me [Please Please Me]1963
#Love Me Do [Please Please Me]1963
...
to_sで出力されたSQLは変ですが、 DBへはきちんとアクセスしているみたいです???
