1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
|
use lib 't';
BEGIN {
# to handle systems with no installed Test module
# we include the t dir (where a copy of Test.pm is located)
# as a fallback
eval { require Test; };
use Test;
use DBStagTest;
plan tests => 4;
}
use DBIx::DBStag;
use DBI;
use Data::Stag;
use FileHandle;
use strict;
drop qw( person2address person address );
my $ddl = <<EOM
CREATE TABLE address (
address_id serial NOT NULL PRIMARY KEY,
addressline varchar(255),
city VARCHAR(255)
);
CREATE TABLE person (
person_id serial NOT NULL PRIMARY KEY,
fname varchar(255) NOT NULL,
lname varchar(255) NOT NULL,
job varchar(255),
UNIQUE (fname, lname)
);
CREATE TABLE person2address (
address_id integer NOT NULL REFERENCES address(address_id),
person_id integer NOT NULL REFERENCES person(person_id)
);
EOM
;
my $data = <<EOM
(set
(dbstag_metadata
(link
(table "person2address")
(to "person")
(from "address")))
(person
(fname "sherlock")
(lname "holmes")
(job "detective"))
(person
(fname "immanuel")
(lname "kant")
(job "philosopher"))
(person
(fname "charles")
(lname "darwin")
(job "naturalist"))
(person
(fname "winston")
(lname "churchill")
(job "prime minister"))
(person
(fname "clement")
(lname "attlee")
(job "prime minister"))
(person
(fname "buck")
(lname "rogers")
(job "space pilot"))
(address
(addressline "1 A street")
(city "london"))
(address
(addressline "221B Baker Street")
(city "london")
(person
(fname "sherlock")
(lname "holmes"))
(person
(fname "dr")
(lname "watson")))
(address
(addressline "10 Downing Street")
(city "london")
(person
(fname "winston")
(lname "churchill"))
(person
(fname "clement")
(lname "attlee")))
)
EOM
;
my $dbh = connect_to_cleandb();
# this time we are storing mapping data in dbstag_metadata in data
$dbh->do($ddl);
my $set = Data::Stag->from('sxprstr', $data);
my @nodes = $set->kids;
$dbh->is_caching_on('person',1);
foreach (@nodes) {
$dbh->storenode($_);
}
my $aset =
$dbh->selectall_stag("SELECT address.*, person.* FROM address NATURAL JOIN person2address NATURAL JOIN person WHERE addressline = '10 Downing Street'");
print $aset->xml;
my @addresses = $aset->get_address;
ok(@addresses==1);
my $address = shift @addresses;
my @persons = $address->get_person;
ok(@persons==2);
ok(grep {$_->get_lname eq "attlee"} @persons);
ok(grep {$_->get_lname eq "churchill"} @persons);
$dbh->disconnect;
|
