1 |
## --------------------------------------------------------------------------- |
2 |
## $Id: db.pm,v 1.1 2003/04/08 23:09:16 joko Exp $ |
3 |
## --------------------------------------------------------------------------- |
4 |
## $Log: db.pm,v $ |
5 |
## Revision 1.1 2003/04/08 23:09:16 joko |
6 |
## initial commit: code from perl/libs/libdb.pm |
7 |
## |
8 |
## --------------------------------------------------------------------------- |
9 |
|
10 |
=pod |
11 |
|
12 |
=head1 Background |
13 |
|
14 |
Databases = Everything is sql |
15 |
Perl ~ Everything is a hash ;-) |
16 |
|
17 |
|
18 |
=cut |
19 |
|
20 |
|
21 |
|
22 |
package shortcuts::db; |
23 |
|
24 |
use strict; |
25 |
use warnings; |
26 |
|
27 |
require Exporter; |
28 |
our @ISA = qw( Exporter ); |
29 |
our @EXPORT_OK = qw( |
30 |
SQL_INSERT SQL_UPDATE SQL_SELECT |
31 |
hash2sql |
32 |
quotesql |
33 |
dsn2dbname |
34 |
); |
35 |
|
36 |
|
37 |
use constant SQL_INSERT => 10; |
38 |
use constant SQL_UPDATE => 11; |
39 |
use constant SQL_SELECT => 12; |
40 |
|
41 |
|
42 |
use Data::Dumper; |
43 |
|
44 |
|
45 |
# TODO: handle usage of "$crit" in an abstract way somehow |
46 |
sub hash2sql { |
47 |
|
48 |
my $table = shift; |
49 |
my $hash = shift; |
50 |
my $mode = shift; |
51 |
my $crit = shift; |
52 |
|
53 |
my $sql; |
54 |
$mode = SQL_SELECT if ($mode eq 'SQL_SELECT' || $mode eq 'SELECT'); |
55 |
$mode = SQL_INSERT if ($mode eq 'SQL_INSERT' || $mode eq 'INSERT'); |
56 |
$mode = SQL_UPDATE if ($mode eq 'SQL_UPDATE' || $mode eq 'UPDATE'); |
57 |
|
58 |
if ($mode == SQL_SELECT) { |
59 |
$sql = "SELECT #fields# FROM $table"; |
60 |
} elsif ($mode == SQL_INSERT) { |
61 |
$sql = "INSERT INTO $table (#fields#) VALUES (#values#)"; |
62 |
} elsif ($mode == SQL_UPDATE) { |
63 |
$sql = "UPDATE $table SET #fields-values#"; |
64 |
} |
65 |
|
66 |
# FIXME: this wouldn't be valid for 'INSERT' queries |
67 |
if ($crit) { |
68 |
$sql .= " WHERE $crit"; |
69 |
} |
70 |
|
71 |
my (@fields, @values); |
72 |
foreach my $key (keys %{$hash}) { |
73 |
push @fields, $key; |
74 |
push @values, $hash->{$key}; |
75 |
} |
76 |
|
77 |
# quote each element |
78 |
map { if (defined $_) { $_ = "'$_'" } else { $_ = "null" } } @values; |
79 |
|
80 |
my $fields = join(', ', @fields); |
81 |
my $values = join(', ', @values); |
82 |
my $fields_values = ''; |
83 |
my $fc = 0; |
84 |
foreach (@fields) { |
85 |
$fields_values .= $_ . '=' . $values[$fc] . ', '; |
86 |
$fc++; |
87 |
} |
88 |
$fields_values = substr($fields_values, 0, -2); |
89 |
|
90 |
# FIXME: this should only be valid for 'SELECT' queries |
91 |
$fields ||= '*'; |
92 |
|
93 |
$sql =~ s/#fields#/$fields/; |
94 |
$sql =~ s/#values#/$values/; |
95 |
$sql =~ s/#fields-values#/$fields_values/; |
96 |
|
97 |
print "shortcuts::db::hash2sql: \$sql=$sql", "\n"; |
98 |
|
99 |
return $sql; |
100 |
} |
101 |
|
102 |
|
103 |
sub quotesql { |
104 |
my $string = shift; |
105 |
if ($string) { |
106 |
$string =~ s/'/\\'/g; |
107 |
} |
108 |
return $string; |
109 |
} |
110 |
|
111 |
sub dsn2dbname { |
112 |
my $dsn = shift; |
113 |
$dsn =~ m/database=(.+?);/; |
114 |
my $database_name = $1; |
115 |
return $database_name; |
116 |
} |
117 |
|
118 |
1; |
119 |
__END__ |