/[cvs]/nfo/perl/libs/shortcuts/database.pm
ViewVC logotype

Diff of /nfo/perl/libs/shortcuts/database.pm

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 1.1 by joko, Wed Apr 9 07:47:52 2003 UTC revision 1.4 by joko, Tue May 13 09:10:50 2003 UTC
# Line 2  Line 2 
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
# Line 22  Line 30 
30    
31    
32    
33  package shortcuts::db;  package shortcuts::database;
34    
35  use strict;  use strict;
36  use warnings;  use warnings;
# Line 40  our @EXPORT_OK = qw( Line 48  our @EXPORT_OK = qw(
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;
# Line 48  use Data::Dumper; Line 57  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);
# Line 76  sub hash2sql { Line 108  sub hash2sql {
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        
# Line 96  sub hash2sql { Line 129  sub hash2sql {
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  }  }
Legend:
Removed from v.1.1  
changed lines
  Added in v.1.4
MailToCvsAdmin">MailToCvsAdmin
 ViewVC Help
Powered by ViewVC 1.1.26 RSS 2.0 feed