| 1 | package dinfo; |
1 | package dinfo; |
| 2 | |
2 | |
| 3 | use Carp; |
3 | use Carp; |
| 4 | |
|
|
| 5 | use PApp::SQL; |
4 | use PApp::SQL; |
|
|
5 | |
|
|
6 | =head1 dinfo - THIS IS A PRIVATE MODULE, DON't COPY |
|
|
7 | |
|
|
8 | =cut |
|
|
9 | |
|
|
10 | BEGIN { |
|
|
11 | $VERSION = 0.1; |
|
|
12 | use XSLoader; |
|
|
13 | XSLoader::load __PACKAGE__, $VERSION; |
|
|
14 | } |
| 6 | |
15 | |
| 7 | sub new { |
16 | sub new { |
| 8 | my ($class, $dsn, $user, $pass) = @_; |
17 | my ($class, $dsn, $user, $pass) = @_; |
| 9 | |
18 | |
| 10 | my $self = bless { |
19 | my $self = bless { |
| … | |
… | |
| 12 | }, $class; |
21 | }, $class; |
| 13 | |
22 | |
| 14 | $self; |
23 | $self; |
| 15 | } |
24 | } |
| 16 | |
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 | [ 8 => "hausnr"], |
|
|
52 | [ 9 => "plz"], |
|
|
53 | [10 => "ort"], |
|
|
54 | [11 => "branche"], |
|
|
55 | [12 => "typ"], |
|
|
56 | ); |
|
|
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 | $select .= "$column = ?"; |
|
|
72 | push @args, nummer2str $match, 10; |
|
|
73 | warn unpack "H*", $args[-1];#d# |
|
|
74 | } elsif ($type eq "prefix") { |
|
|
75 | $select .= "($column between ? and ?)"; |
|
|
76 | push @args, |
|
|
77 | (nummer2str $match, 0), |
|
|
78 | (nummer2str $match, 10); |
|
|
79 | warn unpack "H*", $args[-2];#d# |
|
|
80 | warn unpack "H*", $args[-1];#d# |
|
|
81 | } else { |
|
|
82 | croak "illegal search type '$type', must be one of exact, prefix"; |
|
|
83 | } |
|
|
84 | } else { |
|
|
85 | push @args, $match; |
|
|
86 | if ($type eq "exact") { |
|
|
87 | $select .= "($column.data = ?)"; |
|
|
88 | } elsif ($type eq "like") { |
|
|
89 | $select .= "($column.data like ?)"; |
|
|
90 | } elsif ($type eq "prefix") { |
|
|
91 | $select .= "($column.data like ?)"; |
|
|
92 | $args[-1] .= "%"; |
|
|
93 | } elsif ($type eq "regexp") { |
|
|
94 | $select .= "($column.data regexp ?)"; |
|
|
95 | } elsif ($type eq "match") { |
|
|
96 | $select .= "(match $column.data against (?))"; |
|
|
97 | } else { |
|
|
98 | croak "illegal search type '$type', must be one of exact, like, regexp or match"; |
|
|
99 | } |
|
|
100 | } |
|
|
101 | } |
|
|
102 | |
|
|
103 | my $st = sql_exec $self->{dbh}, $select, @args |
|
|
104 | or die "sql_exec returned no statement handle"; |
|
|
105 | |
|
|
106 | return bless { |
|
|
107 | dinfo => $self, |
|
|
108 | st => $st, |
|
|
109 | }, dinfo::result; |
|
|
110 | } |
|
|
111 | |
|
|
112 | =item my ($prefix, $local) = $dinfo->split_number($number) |
|
|
113 | |
|
|
114 | split a number into prefix and local part. |
|
|
115 | |
|
|
116 | =cut |
|
|
117 | |
|
|
118 | sub split_number { |
|
|
119 | my ($self, $number) = @_; |
|
|
120 | |
|
|
121 | for ($number) { |
|
|
122 | y/0-9//cd; |
|
|
123 | s/^/0/ unless /^0/; |
|
|
124 | |
|
|
125 | for (3..6) { |
|
|
126 | my $prefix = substr $number, 0, $_; |
|
|
127 | my $isprefix = |
|
|
128 | $cache{$prefix} |
|
|
129 | ||= sql_fetch $self->{dbh}, |
|
|
130 | "select 1 + count(*) from vorwahl where data = ?", |
|
|
131 | $prefix; |
|
|
132 | return ($prefix, substr $number, $_) |
|
|
133 | if $isprefix == 2; |
|
|
134 | } |
|
|
135 | |
|
|
136 | return (); |
|
|
137 | } |
|
|
138 | } |
|
|
139 | |
|
|
140 | =item my $hash = $dinfo->identify_number ($number) |
|
|
141 | |
|
|
142 | Try to find out as much as possible about the given number. |
|
|
143 | |
|
|
144 | =cut |
|
|
145 | |
|
|
146 | sub identify_number { |
|
|
147 | my ($self, $number) = @_; |
|
|
148 | my %r; |
|
|
149 | my ($prefix, $number) = $self->split_number ($number); |
|
|
150 | |
|
|
151 | if ($prefix) { |
|
|
152 | $r{vorwahl} = $prefix; |
|
|
153 | $r{match} = "exact"; |
|
|
154 | |
|
|
155 | while (1 < length $number) { |
|
|
156 | for ($number, "${number}0") { |
|
|
157 | my $result = $self->search (vorwahl => exact => $prefix, |
|
|
158 | nummer => exact => $_); |
|
|
159 | if ($result->rows > 1) { |
|
|
160 | return { %r, %{$result->fetch} }; |
|
|
161 | } elsif ($result->rows == 1) { |
|
|
162 | return { %r, %{$result->fetch} }; |
|
|
163 | } |
|
|
164 | |
|
|
165 | $r{match} = "approx"; |
|
|
166 | } |
|
|
167 | |
|
|
168 | substr $number, -1, 1, ""; |
|
|
169 | } |
|
|
170 | } |
|
|
171 | |
|
|
172 | #$r{ort} = join ", ", |
|
|
173 | # sql_fetchall $self->{dbh}, |
|
|
174 | # "select distinct ort.data |
|
|
175 | # from row |
|
|
176 | # inner join vorwahl on (vorwahl.id = row.vorwahl) |
|
|
177 | # inner join ort on (ort.id = row.ort) |
|
|
178 | # where vorwahl.data = ?", |
|
|
179 | # $r{vorwahl}; |
|
|
180 | |
|
|
181 | \%r; |
|
|
182 | } |
|
|
183 | |
|
|
184 | =head2 dinfo::result |
|
|
185 | |
|
|
186 | =item $count = $result->rows |
|
|
187 | |
|
|
188 | =cut |
|
|
189 | |
|
|
190 | sub dinfo::result::rows { |
|
|
191 | $_[0]{st}->rows; |
|
|
192 | } |
|
|
193 | |
|
|
194 | =item $hash = $result->fetch |
|
|
195 | |
|
|
196 | Fetch and return the next result row. |
|
|
197 | |
|
|
198 | =cut |
|
|
199 | |
|
|
200 | my %typ; |
|
|
201 | |
|
|
202 | sub dinfo::result::fetch { |
|
|
203 | my ($self) = @_; |
|
|
204 | |
|
|
205 | my $row = $self->{st}->fetchrow_arrayref; |
|
|
206 | |
|
|
207 | if ($row) { |
|
|
208 | my %r; |
|
|
209 | |
|
|
210 | $r{$_->[1]} = $row->[$_->[0]] for @cols; |
|
|
211 | |
|
|
212 | warn unpack "H*", $row->[0];#d# |
|
|
213 | $r{nummer} = str2nummer $row->[0]; |
|
|
214 | |
|
|
215 | \%r; |
|
|
216 | } else { |
|
|
217 | return (); |
|
|
218 | } |
|
|
219 | } |
|
|
220 | |
| 17 | 1; |
221 | 1; |
