http://sourceforge.net/project/showfiles.php?group_id=174258
力技でmany-to-menyをActiveRecordに実装
テーブル定義がこんな感じ。
CREATE TABLE ARTIST (
ID INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
NAME VARCHAR(256) NOT NULL
);CREATE TABLE PAINTING (
ARTIST_ID INTEGER NOT NULL,
GALLERY_ID INTEGER NOT NULL
);CREATE TABLE GALLERY (
ID INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
NAME VARCHAR(256) NOT NULL
);ALTER TABLE PAINTING ADD CONSTRAINT PK_PAINTING
PRIMARY KEY (ARTIST_ID, GALLERY_ID);ALTER TABLE PAINTING ADD CONSTRAINT FK_ARTIST_PAINTING
FOREIGN KEY (ARTIST_ID) REFERENCES ARTIST (ID);ALTER TABLE PAINTING ADD CONSTRAINT FK_ARTIST_GALLERY
FOREIGN KEY (GALLERY_ID) REFERENCES GALLERY (ID);
コードがこんな感じ。
Artist.addTrigger(new AfterCreate() {
public void execute(Timing timing, Event event, ActiveRecord row, Database dbh) {
Artist artist = (Artist) row;
artist.setId((Integer) dbh.getOne("CALL IDENTITY()"));
}
});Gallery.addTrigger(new AfterCreate() {
public void execute(Timing timing, Event event, ActiveRecord row, Database dbh) {
Gallery gallery = (Gallery) row;
gallery.setId((Integer) dbh.getOne("CALL IDENTITY()"));
}
});Artist lassen = new Artist();
lassen.setName("Christian Riese Lassen");
lassen.save();Artist yamagata = new Artist();
yamagata.setName("Hiro Yamagata");
yamagata.save();Gallery red = new Gallery();
red.setName("Red Gallery");
red.save();Gallery blue = new Gallery();
blue.setName("Blue Gallery");
blue.save();Gallery green = new Gallery();
green.setName("Green Gallery");
green.save();out.println("\n===== initial state =====");
out.println(new TabularFormat(Artist.findAny()));
out.println(new TabularFormat(Gallery.findAny()));
out.println(new TabularFormat(Painting.findAny()));
out.println();lassen.associate(blue);
lassen.associate(green);yamagata.associate(red);
yamagata.associate(blue);
yamagata.associate(green);out.println("\n===== associated =====");
out.println(new TabularFormat(Painting.findAny()));out.println("- lassen <- Gallery");
out.println(new TabularFormat(lassen.pull(Gallery.class)));
out.println("- yamagata <- Gallery");
out.println(new TabularFormat(yamagata.pull(Gallery.class)));out.println("- red <- Artist");
out.println(new TabularFormat(red.pull(Artist.class)));
out.println("- blue <- Artist");
out.println(new TabularFormat(blue.pull(Artist.class)));
out.println("- green <- Artist");
out.println(new TabularFormat(green.pull(Artist.class)));
out.println();green.unassociate(yamagata);
lassen.unassociate(blue);out.println("\n===== unassociated green<->yamagata, lassen<->blue =====");
out.println(new TabularFormat(Painting.findAny()));out.println("- lassen <- Gallery");
out.println(new TabularFormat(lassen.pull(Gallery.class)));
out.println("- yamagata <- Gallery");
out.println(new TabularFormat(yamagata.pull(Gallery.class)));out.println("- red <- Artist");
out.println(new TabularFormat(red.pull(Artist.class)));
out.println("- blue <- Artist");
out.println(new TabularFormat(blue.pull(Artist.class)));
out.println("- green <- Artist");
out.println(new TabularFormat(green.pull(Artist.class)));
out.println();
実行結果がこんな感じ。
'INSERT INTO ARTIST (NAME) VALUES (?) [Christian Riese Lassen]'
'CALL IDENTITY()'
'INSERT INTO ARTIST (NAME) VALUES (?) [Hiro Yamagata]'
'CALL IDENTITY()'
'INSERT INTO GALLERY (NAME) VALUES (?) [Red Gallery]'
'CALL IDENTITY()'
'INSERT INTO GALLERY (NAME) VALUES (?) [Blue Gallery]'
'CALL IDENTITY()'
'INSERT INTO GALLERY (NAME) VALUES (?) [Green Gallery]'
'CALL IDENTITY()'===== initial state =====
'SELECT * FROM ARTIST'
+--+----------------------+
|id|name |
+--+----------------------+
|0 |Christian Riese Lassen|
|1 |Hiro Yamagata |
+--+----------------------+
'SELECT * FROM GALLERY'
+--+-------------+
|id|name |
+--+-------------+
|0 |Red Gallery |
|1 |Blue Gallery |
|2 |Green Gallery|
+--+-------------+
'SELECT * FROM PAINTING'
Empty set'INSERT INTO PAINTING (GALLERY_ID, ARTIST_ID) VALUES (?, ?) [1, 0]'
'INSERT INTO PAINTING (GALLERY_ID, ARTIST_ID) VALUES (?, ?) [2, 0]'
'INSERT INTO PAINTING (GALLERY_ID, ARTIST_ID) VALUES (?, ?) [0, 1]'
'INSERT INTO PAINTING (GALLERY_ID, ARTIST_ID) VALUES (?, ?) [1, 1]'
'INSERT INTO PAINTING (GALLERY_ID, ARTIST_ID) VALUES (?, ?) [2, 1]'===== associated =====
'SELECT * FROM PAINTING'
+---------+----------+
|artist_id|gallery_id|
+---------+----------+
|0 |1 |
|0 |2 |
|1 |0 |
|1 |1 |
|1 |2 |
+---------+----------+
- lassen <- Gallery
'SELECT * FROM GALLERY , PAINTING WHERE (PAINTING.GALLERY_ID = GALLERY.ID) AND (PAINTING.ARTIST_ID = ?) [0]'
+--+-------------+
|id|name |
+--+-------------+
|1 |Blue Gallery |
|2 |Green Gallery|
+--+-------------+
- yamagata <- Gallery
'SELECT * FROM GALLERY , PAINTING WHERE (PAINTING.GALLERY_ID = GALLERY.ID) AND (PAINTING.ARTIST_ID = ?) [1]'
+--+-------------+
|id|name |
+--+-------------+
|0 |Red Gallery |
|1 |Blue Gallery |
|2 |Green Gallery|
+--+-------------+
- red <- Artist
'SELECT * FROM ARTIST , PAINTING WHERE (PAINTING.ARTIST_ID = ARTIST.ID) AND (PAINTING.GALLERY_ID = ?) [0]'
+--+-------------+
|id|name |
+--+-------------+
|1 |Hiro Yamagata|
+--+-------------+
- blue <- Artist
'SELECT * FROM ARTIST , PAINTING WHERE (PAINTING.ARTIST_ID = ARTIST.ID) AND (PAINTING.GALLERY_ID = ?) [1]'
+--+----------------------+
|id|name |
+--+----------------------+
|0 |Christian Riese Lassen|
|1 |Hiro Yamagata |
+--+----------------------+
- green <- Artist
'SELECT * FROM ARTIST , PAINTING WHERE (PAINTING.ARTIST_ID = ARTIST.ID) AND (PAINTING.GALLERY_ID = ?) [2]'
+--+----------------------+
|id|name |
+--+----------------------+
|0 |Christian Riese Lassen|
|1 |Hiro Yamagata |
+--+----------------------+'DELETE FROM PAINTING WHERE (GALLERY_ID = ?) AND (ARTIST_ID = ?) [2, 1]'
'DELETE FROM PAINTING WHERE (GALLERY_ID = ?) AND (ARTIST_ID = ?) [1, 0]'===== unassociated green<->yamagata, lassen<->blue =====
'SELECT * FROM PAINTING'
+---------+----------+
|artist_id|gallery_id|
+---------+----------+
|0 |2 |
|1 |0 |
|1 |1 |
+---------+----------+
- lassen <- Gallery
'SELECT * FROM GALLERY , PAINTING WHERE (PAINTING.GALLERY_ID = GALLERY.ID) AND (PAINTING.ARTIST_ID = ?) [0]'
+--+-------------+
|id|name |
+--+-------------+
|2 |Green Gallery|
+--+-------------+
- yamagata <- Gallery
'SELECT * FROM GALLERY , PAINTING WHERE (PAINTING.GALLERY_ID = GALLERY.ID) AND (PAINTING.ARTIST_ID = ?) [1]'
+--+------------+
|id|name |
+--+------------+
|0 |Red Gallery |
|1 |Blue Gallery|
+--+------------+
- red <- Artist
'SELECT * FROM ARTIST , PAINTING WHERE (PAINTING.ARTIST_ID = ARTIST.ID) AND (PAINTING.GALLERY_ID = ?) [0]'
+--+-------------+
|id|name |
+--+-------------+
|1 |Hiro Yamagata|
+--+-------------+
- blue <- Artist
'SELECT * FROM ARTIST , PAINTING WHERE (PAINTING.ARTIST_ID = ARTIST.ID) AND (PAINTING.GALLERY_ID = ?) [1]'
+--+-------------+
|id|name |
+--+-------------+
|1 |Hiro Yamagata|
+--+-------------+
- green <- Artist
'SELECT * FROM ARTIST , PAINTING WHERE (PAINTING.ARTIST_ID = ARTIST.ID) AND (PAINTING.GALLERY_ID = ?) [2]'
+--+----------------------+
|id|name |
+--+----------------------+
|0 |Christian Riese Lassen|
+--+----------------------+
