cvsroot/dinfo/dinfo.pm

package dinfo;

use Carp;
use PApp::SQL;

=head1 dinfo - THIS IS A PRIVATE MODULE, DON't COPY

=cut

BEGIN  {
   $VERSION = 0.1;
   use XSLoader;
   XSLoader::load __PACKAGE__, $VERSION;
}

sub new {
   my ($class, $dsn, $user, $pass) = @_;

   my $self = bless {
      dbh => (PApp::SQL::connect_cached "dinfo::", $dsn, $user, $pass),
   }, $class;

   $self;
}

=item $result = $dinfo->search (column => type => value, ...)

Initiate a search on the given columns. C<type> can be one of C<exact>,
C<prefix>, C<like>, C<regexp> or C<match>, which specifies the sql-search
mode used. For C<nummer>-columns, only C<exact> and C<prefix> are
supported.

Returns a C<dinfo::result> object.

 my $r = $dinfo->search(name => like => "Marc%");

 my $r = $dinfo->search(plz => exact => 76139, branche => match => "psychologe");

Not all types are efficient on all columns.. check your indices! :)

=cut

my @cols = (
  [ 1 => "name"],
  [ 2 => "vorname"],
  [ 3 => "zusatz1"],
  [ 4 => "zusatz2"],
  [ 5 => "zusatz3"],
  [ 6 => "vorwahl"],
  [ 7 => "strasse"],
  [ 8 => "hausnr"],
  [ 9 => "plz"],
  [10 => "ort"],
  [11 => "branche"],
  [12 => "typ"],
);

sub search {
   my ($self, @pred) = @_;

   my @args;
   my $select = "select\n  nummer, " . (join ", ", map "$_->[1].data", @cols) . "\n"
              . "from row\n". (join "", map "  inner join $_->[1] on (row.$_->[1] = $_->[1].id)\n", @cols)
              . "where  (1=1)\n";

   while (@pred) {
      my ($column, $type, $match) = splice @pred, 0, 3, ();
      $select .= "  and ";
      if ($column eq "nummer") {
         if ($type eq "exact") {
            $select .= "$column = ?";
            push @args, nummer2str $match, 10;
         } elsif ($type eq "prefix") {
            $select .= "($column between ? and ?)";
            push @args, 
               (nummer2str $match, 0),
               (nummer2str $match, 10);
         } else {
            croak "illegal search type '$type', must be one of exact, prefix";
         }
      } else {
         push @args, $match;
         if ($type eq "exact") {
            $select .= "($column.data = ?)";
         } elsif ($type eq "like") {
            $select .= "($column.data like ?)";
         } elsif ($type eq "prefix") {
            $select .= "($column.data like ?)";
            $args[-1] .= "%";
         } elsif ($type eq "regexp") {
            $select .= "($column.data regexp ?)";
         } elsif ($type eq "match") {
            $select .= "(match $column.data against (?))";
         } else {
            croak "illegal search type '$type', must be one of exact, like, regexp or match";
         }
      }
   }

   my $st = sql_exec $self->{dbh}, $select, @args
      or die "sql_exec returned no statement handle";

   return bless {
      dinfo => $self,
      st    => $st,
   }, dinfo::result;
}

=item my ($prefix, $local) = $dinfo->split_number($number)

split a number into prefix and local part.

=cut

sub split_number {
   my ($self, $number) = @_;

   for ($number) {
      y/0-9//cd;
      s/^/0/ unless /^0/;

      for (3..6) {
         my $prefix = substr $number, 0, $_;
         my $isprefix =
            $cache{$prefix}
               ||= sql_fetch $self->{dbh}, 
                             "select 1 + count(*) from vorwahl where data = ?",
                             $prefix;
         return ($prefix, substr $number, $_)
            if $isprefix == 2;
      }

      return ();
   }
}

=item my $hash = $dinfo->identify_number ($number)

Try to find out as much as possible about the given number.

=cut

sub identify_number {
   my ($self, $number) = @_;
   my %r;
   my ($prefix, $number) = $self->split_number ($number);

   if ($prefix) {
      $r{vorwahl} = $prefix;
      $r{match} = "exact";

      while (1 < length $number) {
         for ($number, "${number}0") {
            my $result = $self->search (vorwahl => exact => $prefix,
                                        nummer => exact => $_);
            if ($result->rows > 1) {
               return { %r, %{$result->fetch} };
            } elsif ($result->rows == 1) {
               return { %r, %{$result->fetch} };
            }

            $r{match} = "approx";
         }

         substr $number, -1, 1, "";
      }
   }

   #$r{ort} = join ", ",
   #            sql_fetchall $self->{dbh},
   #                         "select distinct ort.data
   #                          from row
   #                             inner join vorwahl on (vorwahl.id = row.vorwahl)
   #                             inner join ort     on (ort.id     = row.ort)
   #                          where vorwahl.data = ?",
   #                         $r{vorwahl};
   
   \%r;
}

=head2 dinfo::result

=item $count = $result->rows

=cut

sub dinfo::result::rows {
   $_[0]{st}->rows;
}

=item $hash = $result->fetch

Fetch and return the next result row.

=cut

my %typ;

sub dinfo::result::fetch {
   my ($self) = @_;

   my $row = $self->{st}->fetchrow_arrayref;

   if ($row) {
      my %r;

      $r{$_->[1]} = $row->[$_->[0]] for @cols;

      $r{nummer} = str2nummer $row->[0];

      \%r;
   } else {
      return ();
   }
}

1;
Revision:	1.10
Committed:	Wed Sep 3 23:47:59 2003 UTC (23 years, 1 month ago) by root
Branch:	MAIN
Changes since 1.9:	+0 -4 lines
Log Message:	* empty log message *
#	User	Rev	Content
1	root	1.1	package dinfo;
2
3			use Carp;
4	root	1.3	use PApp::SQL;
5	root	1.1
6	root	1.5	=head1 dinfo - THIS IS A PRIVATE MODULE, DON't COPY
7
8			=cut
9
10			BEGIN {
11			$VERSION = 0.1;
12	root	1.6	use XSLoader;
13	root	1.5	XSLoader::load __PACKAGE__, $VERSION;
14			}
15	root	1.1
16			sub new {
17			my ($class, $dsn, $user, $pass) = @_;
18
19			my $self = bless {
20			dbh => (PApp::SQL::connect_cached "dinfo::", $dsn, $user, $pass),
21			}, $class;
22
23			$self;
24	root	1.3	}
25
26			=item $result = $dinfo->search (column => type => value, ...)
27
28			Initiate a search on the given columns. C<type> can be one of C<exact>,
29			C<prefix>, C<like>, C<regexp> or C<match>, which specifies the sql-search
30			mode used. For C<nummer>-columns, only C<exact> and C<prefix> are
31			supported.
32
33			Returns a C<dinfo::result> object.
34
35			my $r = $dinfo->search(name => like => "Marc%");
36
37			my $r = $dinfo->search(plz => exact => 76139, branche => match => "psychologe");
38
39			Not all types are efficient on all columns.. check your indices! :)
40
41			=cut
42
43			my @cols = (
44			[ 1 => "name"],
45			[ 2 => "vorname"],
46			[ 3 => "zusatz1"],
47			[ 4 => "zusatz2"],
48			[ 5 => "zusatz3"],
49			[ 6 => "vorwahl"],
50			[ 7 => "strasse"],
51	root	1.7	[ 8 => "hausnr"],
52	root	1.3	[ 9 => "plz"],
53			[10 => "ort"],
54			[11 => "branche"],
55	root	1.8	[12 => "typ"],
56	root	1.3	);
57
58			sub search {
59			my ($self, @pred) = @_;
60
61			my @args;
62			my $select = "select\n nummer, " . (join ", ", map "$_->[1].data", @cols) . "\n"
63			. "from row\n". (join "", map " inner join $_->[1] on (row.$_->[1] = $_->[1].id)\n", @cols)
64			. "where (1=1)\n";
65
66			while (@pred) {
67			my ($column, $type, $match) = splice @pred, 0, 3, ();
68			$select .= " and ";
69			if ($column eq "nummer") {
70			if ($type eq "exact") {
71	root	1.7	$select .= "$column = ?";
72			push @args, nummer2str $match, 10;
73	root	1.3	} elsif ($type eq "prefix") {
74	root	1.7	$select .= "($column between ? and ?)";
75	root	1.3	push @args,
76	root	1.9	(nummer2str $match, 0),
77	root	1.7	(nummer2str $match, 10);
78	root	1.3	} else {
79			croak "illegal search type '$type', must be one of exact, prefix";
80			}
81			} else {
82			push @args, $match;
83			if ($type eq "exact") {
84			$select .= "($column.data = ?)";
85			} elsif ($type eq "like") {
86			$select .= "($column.data like ?)";
87			} elsif ($type eq "prefix") {
88			$select .= "($column.data like ?)";
89			$args[-1] .= "%";
90			} elsif ($type eq "regexp") {
91			$select .= "($column.data regexp ?)";
92			} elsif ($type eq "match") {
93	root	1.9	$select .= "(match $column.data against (?))";
94	root	1.3	} else {
95			croak "illegal search type '$type', must be one of exact, like, regexp or match";
96			}
97			}
98			}
99
100	root	1.5	my $st = sql_exec $self->{dbh}, $select, @args
101			or die "sql_exec returned no statement handle";
102
103			return bless {
104			dinfo => $self,
105			st => $st,
106			}, dinfo::result;
107			}
108
109			=item my ($prefix, $local) = $dinfo->split_number($number)
110
111			split a number into prefix and local part.
112
113			=cut
114
115			sub split_number {
116			my ($self, $number) = @_;
117
118			for ($number) {
119	root	1.8	y/0-9//cd;
120	root	1.5	s/^/0/ unless /^0/;
121
122			for (3..6) {
123			my $prefix = substr $number, 0, $_;
124			my $isprefix =
125			$cache{$prefix}
126			\|\|= sql_fetch $self->{dbh},
127			"select 1 + count(*) from vorwahl where data = ?",
128			$prefix;
129			return ($prefix, substr $number, $_)
130			if $isprefix == 2;
131			}
132	root	1.3
133			return ();
134			}
135	root	1.5	}
136
137			=item my $hash = $dinfo->identify_number ($number)
138
139			Try to find out as much as possible about the given number.
140
141			=cut
142
143			sub identify_number {
144			my ($self, $number) = @_;
145			my %r;
146			my ($prefix, $number) = $self->split_number ($number);
147
148			if ($prefix) {
149			$r{vorwahl} = $prefix;
150	root	1.9	$r{match} = "exact";
151	root	1.5
152			while (1 < length $number) {
153			for ($number, "${number}0") {
154			my $result = $self->search (vorwahl => exact => $prefix,
155			nummer => exact => $_);
156			if ($result->rows > 1) {
157	root	1.9	return { %r, %{$result->fetch} };
158	root	1.5	} elsif ($result->rows == 1) {
159	root	1.9	return { %r, %{$result->fetch} };
160	root	1.5	}
161	root	1.9
162			$r{match} = "approx";
163	root	1.5	}
164
165			substr $number, -1, 1, "";
166			}
167			}
168
169	root	1.9	#$r{ort} = join ", ",
170			# sql_fetchall $self->{dbh},
171			# "select distinct ort.data
172			# from row
173			# inner join vorwahl on (vorwahl.id = row.vorwahl)
174			# inner join ort on (ort.id = row.ort)
175			# where vorwahl.data = ?",
176			# $r{vorwahl};
177
178	root	1.5	\%r;
179	root	1.3	}
180
181			=head2 dinfo::result
182
183			=item $count = $result->rows
184
185			=cut
186
187			sub dinfo::result::rows {
188			$_[0]{st}->rows;
189			}
190
191			=item $hash = $result->fetch
192
193			Fetch and return the next result row.
194
195			=cut
196
197			my %typ;
198
199			sub dinfo::result::fetch {
200			my ($self) = @_;
201
202			my $row = $self->{st}->fetchrow_arrayref;
203
204			if ($row) {
205			my %r;
206
207			$r{$_->[1]} = $row->[$_->[0]] for @cols;
208
209	root	1.8	$r{nummer} = str2nummer $row->[0];
210	root	1.3
211			\%r;
212			} else {
213			return ();
214			}
215	root	1.1	}
216
217	root	1.2	1;