1 |
package c_source; |
2 |
|
3 |
# Project: Clipping |
4 |
# Author: Nelson Ferraz <nferraz@phperl.com> |
5 |
# Date: 2003-08-14 |
6 |
|
7 |
# Methods: |
8 |
# |
9 |
# new |
10 |
# select |
11 |
# insert |
12 |
# delete |
13 |
# update |
14 |
# |
15 |
# Syntax: |
16 |
# |
17 |
# my $c_source = new c_source; |
18 |
# |
19 |
# $c_source->select(param1 => value1, param2, value2, ... ); |
20 |
# $c_source->insert(param1 => value1, param2, value2, ... ); |
21 |
# $c_source->delete(param1 => value1, param2, value2, ... ); |
22 |
# $c_source->update(param1 => value1, param2, value2, ... ); |
23 |
|
24 |
use strict; |
25 |
use vars qw($conn); # Connection |
26 |
|
27 |
### |
28 |
|
29 |
sub new { |
30 |
my $proto = shift; |
31 |
my $conn_ref = shift; |
32 |
|
33 |
die 'Missing database connection reference ($conn_ref)' if !ref $conn_ref; |
34 |
|
35 |
my $self = {}; |
36 |
bless($self, (ref($proto) || $proto)); |
37 |
|
38 |
# Database connection |
39 |
$conn = ${$conn_ref}; |
40 |
|
41 |
return $self; |
42 |
} |
43 |
|
44 |
### |
45 |
|
46 |
sub select { |
47 |
my($self, $attr) = @_; |
48 |
|
49 |
my $query = "SELECT |
50 |
c_source_id, |
51 |
c_source_description, |
52 |
c_source_notes |
53 |
FROM c_source\n"; |
54 |
|
55 |
# restrict results |
56 |
my @where = (); |
57 |
|
58 |
push @where, "c_source_id = " . quote($attr->{c_source_id}) |
59 |
if $attr->{"c_source_id"}; |
60 |
|
61 |
push @where, "c_source_description LIKE " . quote ("\%$attr->{c_source_description}\%") |
62 |
if $attr->{"c_source_description"}; |
63 |
|
64 |
push @where, "c_source_notes LIKE " . quote ("\%$attr->{c_source_notes}\%") |
65 |
if $attr->{"c_source_notes"}; |
66 |
|
67 |
push @where, "c_source_date_created = " . quote($attr->{c_source_date_created}) |
68 |
if $attr->{"c_source_date_created"}; |
69 |
|
70 |
push @where, "c_source_date_updated = " . quote($attr->{c_source_date_updated}) |
71 |
if $attr->{"c_source_date_updated"}; |
72 |
|
73 |
push @where, "c_source_date_deleted = " . quote($attr->{c_source_date_deleted}) |
74 |
if $attr->{"c_source_date_deleted"}; |
75 |
|
76 |
|
77 |
$query .= "WHERE " . join ("\nAND ", @where) . "\n" if ($#where > -1); |
78 |
|
79 |
$query .= "ORDER BY c_source_description"; |
80 |
|
81 |
# Execute query |
82 |
my $result = $conn->exec($query); |
83 |
|
84 |
# Error checking |
85 |
if ($conn->errorMessage) { |
86 |
my $errorMessage = $conn->errorMessage; |
87 |
die "Error executing query '$query':\n$errorMessage"; |
88 |
} |
89 |
|
90 |
my @result = (); |
91 |
while (my ($c_source_id,$c_source_description,$c_source_notes) = $result->fetchrow) { |
92 |
# FIXME: fetch using a hash? |
93 |
push @result, { |
94 |
c_source_id => $c_source_id, |
95 |
c_source_description => $c_source_description, |
96 |
c_source_notes => $c_source_notes |
97 |
}; |
98 |
} |
99 |
|
100 |
return @result; |
101 |
} |
102 |
|
103 |
### |
104 |
|
105 |
sub view { |
106 |
my($self, $attr) = @_; |
107 |
|
108 |
my $query = "SELECT |
109 |
c_source_id, |
110 |
c_source_description, |
111 |
c_source_notes, |
112 |
c_source_date_created, |
113 |
c_source_date_updated, |
114 |
c_source_date_deleted |
115 |
FROM view_c_source\n"; |
116 |
|
117 |
# restrict results |
118 |
my @where = (); |
119 |
|
120 |
push @where, "c_source_id = " . quote($attr->{c_source_id}) |
121 |
if $attr->{"c_source_id"}; |
122 |
|
123 |
push @where, "c_source_description LIKE " . quote ("\%$attr->{c_source_description}\%") |
124 |
if $attr->{"c_source_description"}; |
125 |
|
126 |
push @where, "c_source_notes LIKE " . quote ("\%$attr->{c_source_notes}\%") |
127 |
if $attr->{"c_source_notes"}; |
128 |
|
129 |
push @where, "c_source_date_created = " . quote($attr->{c_source_date_created}) |
130 |
if $attr->{"c_source_date_created"}; |
131 |
|
132 |
push @where, "c_source_date_updated = " . quote($attr->{c_source_date_updated}) |
133 |
if $attr->{"c_source_date_updated"}; |
134 |
|
135 |
push @where, "c_source_date_deleted = " . quote($attr->{c_source_date_deleted}) |
136 |
if $attr->{"c_source_date_deleted"}; |
137 |
|
138 |
|
139 |
|
140 |
$query .= "WHERE " . join ("\nAND ", @where) . "\n" |
141 |
if ($#where > -1); |
142 |
|
143 |
$attr->{"order_by"} ||= "c_source_description"; |
144 |
$query .= "ORDER BY " . $attr->{"order_by"} . "\n" |
145 |
if defined $attr->{"order_by"}; |
146 |
|
147 |
$query .= "GROUP BY " . $attr->{"group_by"} . "\n" |
148 |
if defined $attr->{"group_by"}; |
149 |
|
150 |
$attr->{"limit"} ||= 20; |
151 |
$attr->{"offset"} ||= '0'; |
152 |
|
153 |
$query .= "LIMIT $attr->{'limit'} OFFSET $attr->{'offset'}\n"; |
154 |
|
155 |
# Execute query |
156 |
my $result = $conn->exec($query); |
157 |
|
158 |
# Error checking |
159 |
if ($conn->errorMessage) { |
160 |
my $errorMessage = $conn->errorMessage; |
161 |
die "Error executing query '$query':\n$errorMessage"; |
162 |
} |
163 |
|
164 |
my @result = (); |
165 |
while (my ($c_source_id,$c_source_description,$c_source_notes,$c_source_date_created,$c_source_date_updated,$c_source_date_deleted) = $result->fetchrow) { |
166 |
push @result, { |
167 |
c_source_id => $c_source_id, |
168 |
c_source_description => $c_source_description, |
169 |
c_source_notes => $c_source_notes, |
170 |
c_source_date_created => $c_source_date_created, |
171 |
c_source_date_updated => $c_source_date_updated, |
172 |
c_source_date_deleted => $c_source_date_deleted |
173 |
}; |
174 |
} |
175 |
|
176 |
return @result; |
177 |
} |
178 |
|
179 |
### |
180 |
|
181 |
sub insert { |
182 |
my($self, $attr) = @_; |
183 |
|
184 |
my $query = "SELECT sp_ins_c_source (" . |
185 |
quote($attr->{'c_source_description'}) . ',' . |
186 |
quote($attr->{'c_source_notes'}) . |
187 |
")"; |
188 |
|
189 |
# Execute query |
190 |
my $result = $conn->exec($query); |
191 |
|
192 |
# Error checking |
193 |
if ($conn->errorMessage) { |
194 |
my $errorMessage = $conn->errorMessage; |
195 |
die "Error executing query '$query':\n$errorMessage"; |
196 |
} |
197 |
} |
198 |
|
199 |
### |
200 |
|
201 |
sub delete { |
202 |
my($self, $attr) = @_; |
203 |
|
204 |
my $c_source_id = $attr->{c_source_id}; |
205 |
my @c_source_id = split (/\0/,$c_source_id); |
206 |
|
207 |
foreach my $id (@c_source_id) { |
208 |
my $q_id = quote ($id); |
209 |
my $query = "SELECT sp_del_c_source ($q_id)"; |
210 |
|
211 |
# Execute query |
212 |
my $result = $conn->exec($query); |
213 |
|
214 |
# Error checking |
215 |
if ($conn->errorMessage) { |
216 |
my $errorMessage = $conn->errorMessage; |
217 |
die "Error executing query '$query':\n$errorMessage"; |
218 |
} |
219 |
} |
220 |
} |
221 |
|
222 |
### |
223 |
|
224 |
sub update { |
225 |
my($self, $attr) = @_; |
226 |
|
227 |
my $query = "SELECT sp_upd_c_source (" . |
228 |
quote($attr->{'c_source_id'}) . ',' . |
229 |
quote($attr->{'c_source_description'}) . ',' . |
230 |
quote($attr->{'c_source_notes'}) . |
231 |
")"; |
232 |
|
233 |
# Execute query |
234 |
my $result = $conn->exec($query); |
235 |
|
236 |
# Error checking |
237 |
if ($conn->errorMessage) { |
238 |
my $errorMessage = $conn->errorMessage; |
239 |
die "Error executing query '$query':\n$errorMessage"; |
240 |
} |
241 |
} |
242 |
|
243 |
### |
244 |
|
245 |
|
246 |
|
247 |
sub quote { |
248 |
my $str = shift; |
249 |
if ($str eq '') { |
250 |
return "NULL"; |
251 |
} else { |
252 |
$str =~ s/'/''/g; # ISO SQL2 |
253 |
return "'$str'"; |
254 |
} |
255 |
} |
256 |
|
257 |
1; |
258 |
|
259 |
__END__ |
260 |
|
261 |
Clipping - Copyright 2003 PhPerl.com |
262 |
|
263 |
This program is free software; you can redistribute it and/or |
264 |
modify it under the terms of the GNU General Public License |
265 |
as published by the Free Software Foundation; either version 2 |
266 |
of the License, or (at your option) any later version. |
267 |
|
268 |
This program is distributed in the hope that it will be useful, |
269 |
but WITHOUT ANY WARRANTY; without even the implied warranty of |
270 |
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
271 |
|
272 |
See the GNU General Public License for more details: |
273 |
|
274 |
http://www.gnu.org/copyleft/gpl.html |