libs/shortcuts/database.pm

## ---------------------------------------------------------------------------
##  $Id: database.pm,v 1.3 2003/04/11 01:06:44 joko Exp $
## ---------------------------------------------------------------------------
##  $Log: database.pm,v $
##  Revision 1.3  2003/04/11 01:06:44  joko
##  enhanced hash2sql:
##  + revamped crud action tokenizer
##  + introduced SQL_DELETE
##
##  Revision 1.2  2003/04/09 07:51:11  joko
##  renamed from db.pm
##
##  Revision 1.1  2003/04/08 23:09:16  joko
##  initial commit: code from perl/libs/libdb.pm
##
## ---------------------------------------------------------------------------

=pod

=head1  Background

   Databases = Everything is sql
   Perl ~ Everything is a hash   ;-)


=cut


package shortcuts::database;

use strict;
use warnings;

require Exporter;
our @ISA = qw( Exporter );
our @EXPORT_OK = qw( 
  SQL_INSERT SQL_UPDATE SQL_SELECT
  hash2sql
  quotesql
  dsn2dbname
);


use constant SQL_INSERT => 10;
use constant SQL_UPDATE => 11;
use constant SQL_SELECT => 12;
use constant SQL_DELETE => 13;


use Data::Dumper;


# TODO: handle usage of "$crit" in an abstract way somehow
sub hash2sql {
  
  # the arguments
  my $table = shift;
  my $hash = shift;
  my $mode = shift;
  my $crit = shift;
  
  # our result
  my $sql;
  
  # declare commands and associated tokens
  my @command = qw( SELECT INSERT UPDATE DELETE );
  my @token = ( SQL_SELECT, SQL_INSERT, SQL_UPDATE, SQL_DELETE );
  
  # translate stringified mode to token
  my $c = 0;
  foreach (@command) {
    $mode = $token[$c] if ($mode eq 'SQL_' . $_ || $mode eq $_);
    $c++;
  }
  
  # pre-flight check: has mode been resolved into token?
  if ($mode !~ m/\d+/) {
    print __PACKAGE__ . "::hash2sql: no mode, no token, no way!", "\n";
    return;
  }

  # dispatch mode
  if ($mode == SQL_SELECT) {
    $sql = "SELECT #fields# FROM $table";
  } elsif ($mode == SQL_INSERT) {
    $sql = "INSERT INTO $table (#fields#) VALUES (#values#)";
  } elsif ($mode == SQL_UPDATE) {
    $sql = "UPDATE $table SET #fields-values#";
  } elsif ($mode == SQL_DELETE) {
    if (!$crit) {
      print __PACKAGE__ . "::hash2sql: Criteria required for mode 'SQL_DELETE' (\$crit must not be empty!).", "\n";
      return;
    }
    $sql = "DELETE FROM $table";
  }
  
  # apply filter?
  if ($mode != SQL_INSERT && $crit) {
    $sql .= " WHERE $crit" if $sql;
  }
  
  my (@fields, @values);
  foreach my $key (keys %{$hash}) {
    push @fields, $key;
    push @values, $hash->{$key};
  }

  # quote each element
  map { if (defined $_) { $_ = "'$_'" } else { $_ = "null" } } @values;
  
  my $fields = join(', ', @fields);
  my $values = join(', ', @values);
  my $fields_values = '';
  my $fc = 0;
  foreach (@fields) {
    $fields_values .= $_ . '=' . $values[$fc] . ', ';
    $fc++;
  }
  $fields_values = substr($fields_values, 0, -2);
  
  # FIXME: this should only be valid for 'SELECT' queries
  $fields ||= '*';
  
  $sql =~ s/#fields#/$fields/;
  $sql =~ s/#values#/$values/;
  $sql =~ s/#fields-values#/$fields_values/;
  
  #print __PACKAGE__ . "::hash2sql: \$sql=$sql", "\n";
  
  return $sql;
}


sub quotesql {
  my $string = shift;
  if ($string) {
    $string =~ s/'/\\'/g;
  }
  return $string;
}

sub dsn2dbname {
  my $dsn = shift;
  $dsn =~ m/database=(.+?);/;
  my $database_name = $1;
  return $database_name;
}

1;
__END__
1	joko	1.1	## ---------------------------------------------------------------------------
2	joko	1.4	## $Id: database.pm,v 1.3 2003/04/11 01:06:44 joko Exp $
3	joko	1.1	## ---------------------------------------------------------------------------
4	joko	1.3	## $Log: database.pm,v $
5	joko	1.4	## Revision 1.3 2003/04/11 01:06:44 joko
6			## enhanced hash2sql:
7			## + revamped crud action tokenizer
8			## + introduced SQL_DELETE
9			##
10	joko	1.3	## Revision 1.2 2003/04/09 07:51:11 joko
11			## renamed from db.pm
12			##
13	joko	1.1	## Revision 1.1 2003/04/08 23:09:16 joko
14			## initial commit: code from perl/libs/libdb.pm
15			##
16			## ---------------------------------------------------------------------------
17
18			=pod
19
20			=head1 Background
21
22			Databases = Everything is sql
23			Perl ~ Everything is a hash ;-)
24
25
26			=cut
27
28
29
30	joko	1.3	package shortcuts::database;
31	joko	1.1
32			use strict;
33			use warnings;
34
35			require Exporter;
36			our @ISA = qw( Exporter );
37			our @EXPORT_OK = qw(
38			SQL_INSERT SQL_UPDATE SQL_SELECT
39			hash2sql
40			quotesql
41			dsn2dbname
42			);
43
44
45			use constant SQL_INSERT => 10;
46			use constant SQL_UPDATE => 11;
47			use constant SQL_SELECT => 12;
48	joko	1.3	use constant SQL_DELETE => 13;
49	joko	1.1
50
51			use Data::Dumper;
52
53
54			# TODO: handle usage of "$crit" in an abstract way somehow
55			sub hash2sql {
56
57	joko	1.3	# the arguments
58	joko	1.1	my $table = shift;
59			my $hash = shift;
60			my $mode = shift;
61			my $crit = shift;
62
63	joko	1.3	# our result
64	joko	1.1	my $sql;
65	joko	1.3
66			# declare commands and associated tokens
67			my @command = qw( SELECT INSERT UPDATE DELETE );
68			my @token = ( SQL_SELECT, SQL_INSERT, SQL_UPDATE, SQL_DELETE );
69
70			# translate stringified mode to token
71			my $c = 0;
72			foreach (@command) {
73			$mode = $token[$c] if ($mode eq 'SQL_' . $_ \|\| $mode eq $_);
74			$c++;
75			}
76
77			# pre-flight check: has mode been resolved into token?
78			if ($mode !~ m/\d+/) {
79			print __PACKAGE__ . "::hash2sql: no mode, no token, no way!", "\n";
80			return;
81			}
82	joko	1.1
83	joko	1.3	# dispatch mode
84	joko	1.1	if ($mode == SQL_SELECT) {
85			$sql = "SELECT #fields# FROM $table";
86			} elsif ($mode == SQL_INSERT) {
87			$sql = "INSERT INTO $table (#fields#) VALUES (#values#)";
88			} elsif ($mode == SQL_UPDATE) {
89			$sql = "UPDATE $table SET #fields-values#";
90	joko	1.3	} elsif ($mode == SQL_DELETE) {
91			if (!$crit) {
92			print __PACKAGE__ . "::hash2sql: Criteria required for mode 'SQL_DELETE' (\$crit must not be empty!).", "\n";
93			return;
94			}
95			$sql = "DELETE FROM $table";
96	joko	1.1	}
97
98	joko	1.3	# apply filter?
99			if ($mode != SQL_INSERT && $crit) {
100			$sql .= " WHERE $crit" if $sql;
101	joko	1.1	}
102
103			my (@fields, @values);
104			foreach my $key (keys %{$hash}) {
105			push @fields, $key;
106			push @values, $hash->{$key};
107			}
108	joko	1.2
109	joko	1.1	# quote each element
110			map { if (defined $_) { $_ = "'$_'" } else { $_ = "null" } } @values;
111
112			my $fields = join(', ', @fields);
113			my $values = join(', ', @values);
114			my $fields_values = '';
115			my $fc = 0;
116			foreach (@fields) {
117			$fields_values .= $_ . '=' . $values[$fc] . ', ';
118			$fc++;
119			}
120			$fields_values = substr($fields_values, 0, -2);
121
122			# FIXME: this should only be valid for 'SELECT' queries
123			$fields \|\|= '*';
124
125			$sql =~ s/#fields#/$fields/;
126			$sql =~ s/#values#/$values/;
127			$sql =~ s/#fields-values#/$fields_values/;
128
129	joko	1.4	#print __PACKAGE__ . "::hash2sql: \$sql=$sql", "\n";
130	joko	1.1
131			return $sql;
132			}
133
134
135			sub quotesql {
136			my $string = shift;
137			if ($string) {
138			$string =~ s/'/\\'/g;
139			}
140			return $string;
141			}
142
143			sub dsn2dbname {
144			my $dsn = shift;
145			$dsn =~ m/database=(.+?);/;
146			my $database_name = $1;
147			return $database_name;
148			}
149
150			1;
151			__END__
MailToCvsAdmin">MailToCvsAdmin	ViewVC Help
Powered by ViewVC 1.1.26