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"],
);

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),
               (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";
         }
      }
   }

   warn $select, @args;
   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) {
      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;

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

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

   #my @orte = sql_fetchall $self->{dbh},

   \%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;

      my $nummer = unpack "H*", $row->[0];

      my $len = hex substr $nummer, -2, 1;
      my $typ = hex substr $nummer, -1, 1;

      $r{nummer} = substr $nummer, 0, 12 - $len;
      $r{typ}    = $typ{$typ}
                   ||= sql_fetch $self->{dinfo}{dbh},
                                 "select data from typ where id = ?",
                                 $typ;

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

1;
Revision:	1.7
Committed:	Fri Aug 29 14:23:17 2003 UTC (23 years, 1 month ago) by root
Branch:	MAIN
Changes since 1.6:	+7 -7 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			);
56
57			sub search {
58			my ($self, @pred) = @_;
59
60			my @args;
61			my $select = "select\n nummer, " . (join ", ", map "$_->[1].data", @cols) . "\n"
62			. "from row\n". (join "", map " inner join $_->[1] on (row.$_->[1] = $_->[1].id)\n", @cols)
63			. "where (1=1)\n";
64
65			while (@pred) {
66			my ($column, $type, $match) = splice @pred, 0, 3, ();
67			$select .= " and ";
68			if ($column eq "nummer") {
69			if ($type eq "exact") {
70	root	1.7	$select .= "$column = ?";
71			push @args, nummer2str $match, 10;
72	root	1.3	} elsif ($type eq "prefix") {
73	root	1.7	$select .= "($column between ? and ?)";
74	root	1.3	push @args,
75	root	1.7	(nummer2str $match),
76			(nummer2str $match, 10);
77	root	1.3	} else {
78			croak "illegal search type '$type', must be one of exact, prefix";
79			}
80			} else {
81			push @args, $match;
82			if ($type eq "exact") {
83			$select .= "($column.data = ?)";
84			} elsif ($type eq "like") {
85			$select .= "($column.data like ?)";
86			} elsif ($type eq "prefix") {
87			$select .= "($column.data like ?)";
88			$args[-1] .= "%";
89			} elsif ($type eq "regexp") {
90			$select .= "($column.data regexp ?)";
91			} elsif ($type eq "match") {
92			$select .= "(match $column.data against (?)";
93			} else {
94			croak "illegal search type '$type', must be one of exact, like, regexp or match";
95			}
96			}
97			}
98
99	root	1.7	warn $select, @args;
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			s/^/0/ unless /^0/;
120
121			for (3..6) {
122			my $prefix = substr $number, 0, $_;
123			my $isprefix =
124			$cache{$prefix}
125			\|\|= sql_fetch $self->{dbh},
126			"select 1 + count(*) from vorwahl where data = ?",
127			$prefix;
128			return ($prefix, substr $number, $_)
129			if $isprefix == 2;
130			}
131	root	1.3
132			return ();
133			}
134	root	1.5	}
135
136			=item my $hash = $dinfo->identify_number ($number)
137
138			Try to find out as much as possible about the given number.
139
140			=cut
141
142			sub identify_number {
143			my ($self, $number) = @_;
144			my %r;
145			my ($prefix, $number) = $self->split_number ($number);
146
147			if ($prefix) {
148			$r{vorwahl} = $prefix;
149
150			while (1 < length $number) {
151			for ($number, "${number}0") {
152			my $result = $self->search (vorwahl => exact => $prefix,
153			nummer => exact => $_);
154			if ($result->rows > 1) {
155			return $result->fetch;
156			} elsif ($result->rows == 1) {
157			return $result->fetch;
158			}
159			}
160
161			substr $number, -1, 1, "";
162			}
163			}
164
165			#my @orte = sql_fetchall $self->{dbh},
166
167			\%r;
168	root	1.3	}
169
170			=head2 dinfo::result
171
172			=item $count = $result->rows
173
174			=cut
175
176			sub dinfo::result::rows {
177			$_[0]{st}->rows;
178			}
179
180			=item $hash = $result->fetch
181
182			Fetch and return the next result row.
183
184			=cut
185
186			my %typ;
187
188			sub dinfo::result::fetch {
189			my ($self) = @_;
190
191			my $row = $self->{st}->fetchrow_arrayref;
192
193			if ($row) {
194			my %r;
195
196			$r{$_->[1]} = $row->[$_->[0]] for @cols;
197
198			my $nummer = unpack "H*", $row->[0];
199
200			my $len = hex substr $nummer, -2, 1;
201			my $typ = hex substr $nummer, -1, 1;
202
203			$r{nummer} = substr $nummer, 0, 12 - $len;
204			$r{typ} = $typ{$typ}
205			\|\|= sql_fetch $self->{dinfo}{dbh},
206			"select data from typ where id = ?",
207			$typ;
208
209			\%r;
210			} else {
211			return ();
212			}
213	root	1.1	}
214
215	root	1.2	1;