Re: set_sql weird cache issues?

[prev] [thread] [next] [Date index for 2005/07/01]

> What is the primary key of MyApp::M::DBI::list?  What columns did you
> define for it?  How about showing us the code for that class?
>=20
> - Perrin

The parent class is as follows:

package MyApp::M::DBI;
use strict;
use base 'Class::DBI';
__PACKAGE__->columns(All   =3D> qw/pid cid product customer notes addr1/);
__PACKAGE__->set_db('esp', 'dbi:mysql:esp', 'user', 'pass');
__PACKAGE__->set_sql("big_join",=20
                     qq{select c.id 'cid', p.id 'pid',
=09=09     c.name 'customer', c.addr1,=20
=09=09     p.name 'product', p.notes 'notes'=20
=09=09     from=20
=09=09     purchase_order_line po, product p,=20
=09=09     customer c where po.product =3D p.id=20
=09=09     and po.customer =3D c.id order by customer}
);

1;

its accessed by a script:

#!/usr/bin/perl
use lib './lib'; =20
use MyApp::M::DBI;
use Data::Dumper;

my @things =3D MyApp::M::DBI->search_big_join();
foreach my $t (@things) {
  print ">>".$t->cid." ";
  print ">>".$t->pid." ";
  print ">>".$t->customer." ";
  print ">>".$t->addr1." ";
  print ">>".$t->product." ";
  print ">>".$t->notes."\n";
}

the sql in the tables is formed by:

CREATE TABLE customer (
  id int(11) NOT NULL auto_increment,
  name varchar(30) default NULL,
  addr1 varchar(100) default NULL,
  addr2 varchar(100) default NULL,
  notes text,
  PRIMARY KEY  (id)
) TYPE=3DMyISAM;

INSERT INTO customer VALUES (1,'Adams','Adams close','Adamshire',NULL);
INSERT INTO customer VALUES (2,'Barthomelew','Brown street','Berkshire',NUL=
L);
INSERT INTO customer VALUES (3,'Cathcart','Crewe','Cheshire',NULL);
INSERT INTO customer VALUES (4,'Dougal','Devon ave','Devon',NULL);
INSERT INTO customer VALUES (5,'Forsyth','Free Street','Foosbury',NULL);

CREATE TABLE product (
  id int(11) NOT NULL auto_increment,
  name varchar(60) default NULL,
  notes text,
  PRIMARY KEY  (id)
) TYPE=3DMyISAM;

INSERT INTO product VALUES (1,'Crane','Construction');
INSERT INTO product VALUES (2,'Digger','Road Surfacing');
INSERT INTO product VALUES (3,'Drill','Earth Samples');

CREATE TABLE purchase_order_line (
  id int(11) NOT NULL auto_increment,
  customer int(11) default NULL,
  product int(11) default NULL,
  style int(11) default NULL,
  url varchar(120) default NULL,
  score int(2) default NULL,
  price varchar(12) default NULL,
  notes text,
  order_status int(11) default NULL,
  name varchar(60) default NULL,
  purchase_order_sheet int(11) default NULL,
  PRIMARY KEY  (id)
) TYPE=3DMyISAM;

INSERT INTO purchase_order_line VALUES
(1,1,1,0,'mynet.net',10,'7900','yaba daba doo',2,'offer',3);
INSERT INTO purchase_order_line VALUES
(2,1,2,0,'mynet.net',10,'7900','yaba daba doo',2,'offer',3);
INSERT INTO purchase_order_line VALUES
(3,1,1,0,'mynet.net',10,'7900','yaba daba doo',2,'offer',3);
INSERT INTO purchase_order_line VALUES
(4,1,2,0,'mynet.net',10,'7900','yaba daba doo',2,'offer',3);
INSERT INTO purchase_order_line VALUES
(5,1,2,0,'mynet.net',10,'7900','yaba daba doo',2,'offer',3);
INSERT INTO purchase_order_line VALUES
(6,3,2,0,'mynet.net',10,'7900','yaba daba doo',2,'offer',3);
INSERT INTO purchase_order_line VALUES
(7,3,3,0,'mynet.net',10,'7900','yaba daba doo',2,'offer',3);
INSERT INTO purchase_order_line VALUES
(8,3,3,0,'jmynet.net',10,'7900','yaba daba doo',2,'offer',3);
INSERT INTO purchase_order_line VALUES
(9,3,3,0,'jmynet.net',10,'7900','yaba daba doo',2,'offer',3);

the script outputs this:

>>1 >>1 >>Adams >>Adams close >>Crane >>Construction
>>1 >>1 >>Adams >>Adams close >>Crane >>Construction
>>1 >>2 >>Adams >>Adams close >>Digger >>Road Surfacing
>>1 >>2 >>Adams >>Adams close >>Digger >>Road Surfacing
>>1 >>2 >>Adams >>Adams close >>Digger >>Road Surfacing
>>1 >>2 >>Adams >>Adams close >>Digger >>Road Surfacing
>>3 >>3 >>Cathcart >>Crewe >>Drill >>Earth Samples
>>3 >>3 >>Cathcart >>Crewe >>Drill >>Earth Samples
>>3 >>3 >>Cathcart >>Crewe >>Drill >>Earth Samples

whereas a mysql select of ths same query returns this:

+-----+-----+----------+-------------+---------+----------------+
| cid | pid | customer | addr1       | product | notes          |
+-----+-----+----------+-------------+---------+----------------+
|   1 |   1 | Adams    | Adams close | Crane   | Construction   |
|   1 |   1 | Adams    | Adams close | Crane   | Construction   |
|   1 |   2 | Adams    | Adams close | Digger  | Road Surfacing |
|   1 |   2 | Adams    | Adams close | Digger  | Road Surfacing |
|   1 |   2 | Adams    | Adams close | Digger  | Road Surfacing |
|   3 |   2 | Cathcart | Crewe       | Digger  | Road Surfacing |
|   3 |   3 | Cathcart | Crewe       | Drill   | Earth Samples  |
|   3 |   3 | Cathcart | Crewe       | Drill   | Earth Samples  |
|   3 |   3 | Cathcart | Crewe       | Drill   | Earth Samples  |
+-----+-----+----------+-------------+---------+----------------+

which at first look is okay but on closer inspection shows us Cathcart
should be on 4 rows, but the script using Class::DBI returns only 3 -
which is why I thought I had some weird caching issues but would like
to be proven wrong.

I dont understand where I'm going wrong here but I know that I will be
using a lot of complex joins but would like to use Class::DBI to do
them with - I really like the abstraction it offers and where select /
search of single tables is required, it's brilliant.

Any pointers would be much appreciated

many thanks in advance,

Jon.