mysql> describe names;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| name_id | int(7) | NO | PRI | NULL | auto_increment |
| full_name | varchar(50) | NO | | NULL | |
| last_name | text | YES | | NULL | |
| first_name | text | YES | | NULL | |
| comments | varchar(100) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
####
mysql> describe account;
+----------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+----------------+
| account_id | int(7) | NO | PRI | NULL | auto_increment |
| account_number | varchar(10) | NO | | NULL | |
| boxfolder_id | int(7) | NO | | NULL | |
| name_id | int(7) | YES | | NULL | |
| url | varchar(1028) | YES | | NULL | |
| comments | varchar(100) | YES | | NULL | |
+----------------+---------------+------+-----+---------+----------------+
####
mysql> describe boxfolder;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| boxfolder_id | int(7) | NO | PRI | NULL | auto_increment |
| volume | varchar(20) | NO | | NULL | |
| box | varchar(10) | NO | | NULL | |
| folder | varchar(20) | YES | | NULL | |
| range | text | YES | | NULL | |
| comments | varchar(100) | YES | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
####
mysql> SELECT n.name_id,n.full_name,a.account_id,b.boxfolder_id,b.volume,b.folder,b.range,substring(a.url,118,locate('&',a.url,118)-118) AS value2 FROM names as n LEFT JOIN account AS a ON n.name_id = a.name_id LEFT JOIN boxfolder AS b on a.boxfolder_id = b.boxfolder_id WHERE n.full_name REGEXP '[[:<:]]byrne[[:>:]]' LIMIT 10;
+---------+----------------------------+------------+--------------+--------+--------+-----------+---------+
| name_id | full_name | account_id | boxfolder_id | volume | folder | range | value2 |
+---------+----------------------------+------------+--------------+--------+--------+-----------+---------+
| 7220 | Byrne, E. & McCausland A. | 2642 | 322 | 31 | 8 | 5123-5148 | =b22f08 |
| 7221 | Byrne, G. | 2643 | 295 | 29 | 11 | 4293-4314 | =b20f11 |
| 7222 | Byrne, John | 2644 | 106 | 16 | 1 | 6003-6128 | =b08f01 |
| 7223 | Byrne, P. | 2645 | 425 | 37 | fol03 | | =fol03 |
| 7224 | Byrne, P., Estate of | 2646 | 425 | 37 | fol03 | | =fol03 |
| 7225 | Byrne, Patrick | 2672 | 210 | 23 | 1 | 1404-1433 | =b15f01 |
| 7225 | Byrne, Patrick | 2673 | 210 | 23 | 1 | 1404-1433 | =b15f01 |
| 7225 | Byrne, Patrick | 2674 | 214 | 23 | 5 | 1502-1538 | =b15f05 |
| 7225 | Byrne, Patrick | 2675 | 215 | 23 | 6 | 1539-1596 | =b15f06 |
| 7225 | Byrne, Patrick | 2676 | 215 | 23 | 6 | 1539-1596 | =b15f06 |
+---------+----------------------------+------------+--------------+--------+--------+-----------+---------+
####
mysql> SELECT me.name_id, me.full_name, me.last_name, me.first_name, me.comments, account.account_id, account.account_number, account.boxfolder_id, account.name_id, substring(account.url,118,locate('&',account.url,118)-118) AS value2, account.comments, boxfolder.boxfolder_id, boxfolder.volume, boxfolder.box, boxfolder.folder, boxfolder.range, boxfolder.comments FROM (SELECT me.name_id, me.full_name, me.last_name, me.first_name, me.comments FROM names me WHERE ( full_name REGEXP '[[:<:]]Byrne[[:>:]]') ORDER BY full_name ASC LIMIT 5) me LEFT JOIN account account ON account.name_id = me.name_id LEFT JOIN boxfolder boxfolder ON boxfolder.boxfolder_id = account.account_id WHERE ( full_name REGEXP '[[:<:]]Byrne[[:>:]]' ) ORDER BY full_name ASC;
+---------+----------------------------+-----------+------------+----------+------------+----------------+--------------+---------+---------+----------+--------------+--------+------+--------+-------+----------+
| name_id | full_name | last_name | first_name | comments | account_id | account_number | boxfolder_id | name_id | value2 | comments | boxfolder_id | volume | box | folder | range | comments |
+---------+----------------------------+-----------+------------+----------+------------+----------------+--------------+---------+---------+----------+--------------+--------+------+--------+-------+----------+
| 7220 | Byrne, E. & McCausland A. | NULL | NULL | NULL | 2642 | 5139 | 322 | 7220 | =b22f08 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 7221 | Byrne, G. | NULL | NULL | NULL | 2643 | 4299 | 295 | 7221 | =b20f11 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 7222 | Byrne, John | NULL | NULL | NULL | 2644 | 6052 | 106 | 7222 | =b08f01 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 7223 | Byrne, P. | NULL | NULL | NULL | 2645 | 396 | 425 | 7223 | =fol03 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 7224 | Byrne, P., Estate of | NULL | NULL | NULL | 2646 | 396 | 425 | 7224 | =fol03 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+---------+----------------------------+-----------+------------+----------+------------+----------------+--------------+---------+---------+----------+--------------+--------+------+--------+-------+----------+
5 rows in set (0.20 sec)
####
package Carey::Schema::Result::Name;
__PACKAGE__->table("names");
__PACKAGE__->add_columns(
"name_id",
{ data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
"full_name",
{ data_type => "varchar", is_nullable => 0, size => 50 },
"last_name",
{ data_type => "text", is_nullable => 1 },
"first_name",
{ data_type => "text", is_nullable => 1 },
"comments",
{ data_type => "varchar", is_nullable => 1, size => 100 },
);
__PACKAGE__->set_primary_key("name_id");
__PACKAGE__->has_many('account','Carey::Schema::Result::Account','name_id');
####
package Carey::Schema::Result::Account;
__PACKAGE__->table("account");
__PACKAGE__->add_columns(
"account_id",
{ data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
"account_number",
{ data_type => "varchar", is_nullable => 0, size => 10 },
"boxfolder_id",
{ data_type => "integer", is_nullable => 0 },
"name_id",
{ data_type => "integer", is_nullable => 1 },
"url",
{ data_type => "varchar", is_nullable => 1, size => 1028 },
"comments",
{ data_type => "varchar", is_nullable => 1, size => 100 },
);
__PACKAGE__->set_primary_key("account_id");
__PACKAGE__->belongs_to('name', 'Carey::Schema::Result::Name','account_id');
__PACKAGE__->has_one('boxfolder', 'Carey::Schema::Result::Boxfolder','boxfolder_id' );
####
package Carey::Schema::Result::Boxfolder;
__PACKAGE__->table("boxfolder");
__PACKAGE__->add_columns(
"boxfolder_id",
{ data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
"volume",
{ data_type => "varchar", is_nullable => 0, size => 20 },
"box",
{ data_type => "varchar", is_nullable => 0, size => 10 },
"folder",
{ data_type => "varchar", is_nullable => 1, size => 20 },
"range",
{ data_type => "text", is_nullable => 1 },
"comments",
{ data_type => "varchar", is_nullable => 1, size => 100 },
);
__PACKAGE__->set_primary_key("boxfolder_id");
__PACKAGE__->belongs_to('account','Carey::Schema::Result::Account','boxfolder_id');
####
#!/usr/bin/perl -w
use DBIx::Class;
use lib '../lib';
use Carey::Schema;
use Data::Dumper;
die "Usage: $0 name\n\n" if $#ARGV < 0;
my $name = shift @ARGV;
my $schema = Carey::Schema->connect('DBI:mysql:database=careymss;host=localhost;port=3306', 'kiddb'\
,'', { PrintError => 1, RaiseError => 1});
#my $rs = $schema->resultset('Name')->search({ full_name => { like => $name }, },
# { rows => 5,
# order_by => { -asc => 'full_name' },
# prefetch => { 'account' => 'boxfolder'},
# });
my $rs = $schema->resultset('Name')->search({ full_name =>
{ regexp => '[[:<:]]'.$name.'[[:>:]]' }
},
{
rows => 5,
order_by => { -asc => 'full_name' },
#join => ['account', { 'account' => 'boxfolder' } ],
prefetch => [ 'account', { 'account' => 'boxfolder' } \
],
});
$schema->storage->debug(1);
#while (my $r = $rs->next) {
my @r = $rs->all;
foreach my $r (@r) {
print "Full Name: ", $r->full_name, " (", $r->name_id, ")\n";
foreach my $a ($r->account()) {
print " account_id=", $a->account_id();
print " boxfolder_id=",$a->boxfolder_id()," ";
my $t = $a->url();
$t =~ m/value2=(\w+)\&/;
print " URL: $1";
$vol = $a->boxfolder->volume;
$folder = $a->boxfolder->volume;
$range = $a->boxfolder->range;
print " V=$vol " if defined $vol;
print " F=$folder " if defined $folder;
print " R=$range " if defined $range;
print "\n";
}
}