2 |
## $Id$ |
## $Id$ |
3 |
## --------------------------------------------------------------------------- |
## --------------------------------------------------------------------------- |
4 |
## $Log$ |
## $Log$ |
5 |
## Revision 1.1 2003/04/09 07:47:52 joko |
## Revision 1.4 2003/05/13 09:10:50 joko |
6 |
|
## disabled debugging |
7 |
|
## |
8 |
|
## Revision 1.3 2003/04/11 01:06:44 joko |
9 |
|
## enhanced hash2sql: |
10 |
|
## + revamped crud action tokenizer |
11 |
|
## + introduced SQL_DELETE |
12 |
|
## |
13 |
|
## Revision 1.2 2003/04/09 07:51:11 joko |
14 |
## renamed from db.pm |
## renamed from db.pm |
15 |
## |
## |
16 |
## Revision 1.1 2003/04/08 23:09:16 joko |
## Revision 1.1 2003/04/08 23:09:16 joko |
30 |
|
|
31 |
|
|
32 |
|
|
33 |
package shortcuts::db; |
package shortcuts::database; |
34 |
|
|
35 |
use strict; |
use strict; |
36 |
use warnings; |
use warnings; |
48 |
use constant SQL_INSERT => 10; |
use constant SQL_INSERT => 10; |
49 |
use constant SQL_UPDATE => 11; |
use constant SQL_UPDATE => 11; |
50 |
use constant SQL_SELECT => 12; |
use constant SQL_SELECT => 12; |
51 |
|
use constant SQL_DELETE => 13; |
52 |
|
|
53 |
|
|
54 |
use Data::Dumper; |
use Data::Dumper; |
57 |
# TODO: handle usage of "$crit" in an abstract way somehow |
# TODO: handle usage of "$crit" in an abstract way somehow |
58 |
sub hash2sql { |
sub hash2sql { |
59 |
|
|
60 |
|
# the arguments |
61 |
my $table = shift; |
my $table = shift; |
62 |
my $hash = shift; |
my $hash = shift; |
63 |
my $mode = shift; |
my $mode = shift; |
64 |
my $crit = shift; |
my $crit = shift; |
65 |
|
|
66 |
|
# our result |
67 |
my $sql; |
my $sql; |
68 |
$mode = SQL_SELECT if ($mode eq 'SQL_SELECT' || $mode eq 'SELECT'); |
|
69 |
$mode = SQL_INSERT if ($mode eq 'SQL_INSERT' || $mode eq 'INSERT'); |
# declare commands and associated tokens |
70 |
$mode = SQL_UPDATE if ($mode eq 'SQL_UPDATE' || $mode eq 'UPDATE'); |
my @command = qw( SELECT INSERT UPDATE DELETE ); |
71 |
|
my @token = ( SQL_SELECT, SQL_INSERT, SQL_UPDATE, SQL_DELETE ); |
72 |
|
|
73 |
|
# translate stringified mode to token |
74 |
|
my $c = 0; |
75 |
|
foreach (@command) { |
76 |
|
$mode = $token[$c] if ($mode eq 'SQL_' . $_ || $mode eq $_); |
77 |
|
$c++; |
78 |
|
} |
79 |
|
|
80 |
|
# pre-flight check: has mode been resolved into token? |
81 |
|
if ($mode !~ m/\d+/) { |
82 |
|
print __PACKAGE__ . "::hash2sql: no mode, no token, no way!", "\n"; |
83 |
|
return; |
84 |
|
} |
85 |
|
|
86 |
|
# dispatch mode |
87 |
if ($mode == SQL_SELECT) { |
if ($mode == SQL_SELECT) { |
88 |
$sql = "SELECT #fields# FROM $table"; |
$sql = "SELECT #fields# FROM $table"; |
89 |
} elsif ($mode == SQL_INSERT) { |
} elsif ($mode == SQL_INSERT) { |
90 |
$sql = "INSERT INTO $table (#fields#) VALUES (#values#)"; |
$sql = "INSERT INTO $table (#fields#) VALUES (#values#)"; |
91 |
} elsif ($mode == SQL_UPDATE) { |
} elsif ($mode == SQL_UPDATE) { |
92 |
$sql = "UPDATE $table SET #fields-values#"; |
$sql = "UPDATE $table SET #fields-values#"; |
93 |
|
} elsif ($mode == SQL_DELETE) { |
94 |
|
if (!$crit) { |
95 |
|
print __PACKAGE__ . "::hash2sql: Criteria required for mode 'SQL_DELETE' (\$crit must not be empty!).", "\n"; |
96 |
|
return; |
97 |
|
} |
98 |
|
$sql = "DELETE FROM $table"; |
99 |
} |
} |
100 |
|
|
101 |
# FIXME: this wouldn't be valid for 'INSERT' queries |
# apply filter? |
102 |
if ($crit) { |
if ($mode != SQL_INSERT && $crit) { |
103 |
$sql .= " WHERE $crit"; |
$sql .= " WHERE $crit" if $sql; |
104 |
} |
} |
105 |
|
|
106 |
my (@fields, @values); |
my (@fields, @values); |
108 |
push @fields, $key; |
push @fields, $key; |
109 |
push @values, $hash->{$key}; |
push @values, $hash->{$key}; |
110 |
} |
} |
111 |
|
|
112 |
# quote each element |
# quote each element |
113 |
map { if (defined $_) { $_ = "'$_'" } else { $_ = "null" } } @values; |
map { if (defined $_) { $_ = "'$_'" } else { $_ = "null" } } @values; |
114 |
|
|
129 |
$sql =~ s/#values#/$values/; |
$sql =~ s/#values#/$values/; |
130 |
$sql =~ s/#fields-values#/$fields_values/; |
$sql =~ s/#fields-values#/$fields_values/; |
131 |
|
|
132 |
print "shortcuts::db::hash2sql: \$sql=$sql", "\n"; |
#print __PACKAGE__ . "::hash2sql: \$sql=$sql", "\n"; |
133 |
|
|
134 |
return $sql; |
return $sql; |
135 |
} |
} |