Parent Directory
| 
Revision Log
Revision 972 - (view) (download) (as text)
| 1 : | sh002i | 874 | ################################################################################ |
| 2 : | # WeBWorK mod_perl (c) 2000-2002 WeBWorK Project | ||
| 3 : | # $Id$ | ||
| 4 : | ################################################################################ | ||
| 5 : | |||
| 6 : | package WeBWorK::DB::Schema::SQL; | ||
| 7 : | |||
| 8 : | =head1 NAME | ||
| 9 : | |||
| 10 : | WeBWorK::DB::Schema::SQL - support SQL access to all tables. | ||
| 11 : | |||
| 12 : | =cut | ||
| 13 : | |||
| 14 : | use strict; | ||
| 15 : | use warnings; | ||
| 16 : | sh002i | 915 | use Carp qw(croak); |
| 17 : | sh002i | 874 | |
| 18 : | use constant TABLES => qw(password permission key user set set_user problem problem_user); | ||
| 19 : | use constant STYLE => "sql"; | ||
| 20 : | |||
| 21 : | ################################################################################ | ||
| 22 : | # static functions | ||
| 23 : | ################################################################################ | ||
| 24 : | |||
| 25 : | sub tables() { | ||
| 26 : | return TABLES; | ||
| 27 : | } | ||
| 28 : | |||
| 29 : | sub style() { | ||
| 30 : | return STYLE; | ||
| 31 : | } | ||
| 32 : | |||
| 33 : | ################################################################################ | ||
| 34 : | # constructor | ||
| 35 : | ################################################################################ | ||
| 36 : | |||
| 37 : | sub new($$$) { | ||
| 38 : | sh002i | 931 | my ($proto, $db, $driver, $table, $record, $params) = @_; |
| 39 : | sh002i | 874 | my $class = ref($proto) || $proto; |
| 40 : | die "$table: unsupported table" | ||
| 41 : | unless grep { $_ eq $table } $proto->tables(); | ||
| 42 : | die $driver->style(), ": style mismatch" | ||
| 43 : | unless $driver->style() eq $proto->style(); | ||
| 44 : | my $self = { | ||
| 45 : | sh002i | 931 | db => $db, |
| 46 : | sh002i | 874 | driver => $driver, |
| 47 : | table => $table, | ||
| 48 : | record => $record, | ||
| 49 : | params => $params, | ||
| 50 : | }; | ||
| 51 : | sh002i | 904 | $self->{table} = $params->{tableOverride} if $params->{tableOverride}; |
| 52 : | sh002i | 874 | bless $self, $class; |
| 53 : | return $self; | ||
| 54 : | } | ||
| 55 : | |||
| 56 : | ################################################################################ | ||
| 57 : | # table access functions | ||
| 58 : | ################################################################################ | ||
| 59 : | |||
| 60 : | sh002i | 904 | sub list($@) { |
| 61 : | sh002i | 874 | my ($self, @keyparts) = @_; |
| 62 : | |||
| 63 : | my $table = $self->{table}; | ||
| 64 : | sh002i | 904 | my @keynames = $self->sqlKeynames(); |
| 65 : | sh002i | 874 | my $keynames = join(", ", @keynames); |
| 66 : | |||
| 67 : | sh002i | 915 | croak "too many keyparts for table $table (need at most: @keynames)" |
| 68 : | sh002i | 904 | if @keyparts > @keynames; |
| 69 : | |||
| 70 : | my $stmt = "SELECT $keynames FROM $table "; | ||
| 71 : | $stmt .= $self->makeWhereClause(@keyparts); | ||
| 72 : | sh002i | 915 | $self->debug("SQL-list: $stmt\n"); |
| 73 : | sh002i | 904 | |
| 74 : | sh002i | 874 | $self->{driver}->connect("ro"); |
| 75 : | sh002i | 904 | my $result = $self->{driver}->handle()->selectall_arrayref($stmt); |
| 76 : | sh002i | 874 | $self->{driver}->disconnect(); |
| 77 : | sh002i | 915 | croak "failed to SELECT: $DBI::errstr" unless defined $result; |
| 78 : | sh002i | 904 | return @$result; |
| 79 : | sh002i | 874 | } |
| 80 : | |||
| 81 : | sh002i | 904 | sub exists($@) { |
| 82 : | sh002i | 874 | my ($self, @keyparts) = @_; |
| 83 : | |||
| 84 : | my $table = $self->{table}; | ||
| 85 : | sh002i | 904 | my @keynames = $self->sqlKeynames(); |
| 86 : | sh002i | 874 | |
| 87 : | sh002i | 915 | croak "wrong number of keyparts for table $table (needs: @keynames)" |
| 88 : | sh002i | 904 | unless @keyparts == @keynames; |
| 89 : | sh002i | 874 | |
| 90 : | sh002i | 904 | my $stmt = "SELECT COUNT(*) FROM $table "; |
| 91 : | $stmt .= $self->makeWhereClause(@keyparts); | ||
| 92 : | sh002i | 915 | $self->debug("SQL-exists: $stmt\n"); |
| 93 : | sh002i | 874 | |
| 94 : | $self->{driver}->connect("ro"); | ||
| 95 : | sh002i | 904 | my ($result) = $self->{driver}->handle()->selectrow_array($stmt); |
| 96 : | sh002i | 874 | $self->{driver}->disconnect(); |
| 97 : | sh002i | 915 | croak "failed to SELECT: $DBI::errstr" unless defined $result; |
| 98 : | sh002i | 904 | return $result > 0; |
| 99 : | sh002i | 874 | } |
| 100 : | |||
| 101 : | sub add($$) { | ||
| 102 : | my ($self, $Record) = @_; | ||
| 103 : | |||
| 104 : | sh002i | 904 | my @realKeynames = $self->{record}->KEYFIELDS(); |
| 105 : | my @keyparts = map { $Record->$_() } @realKeynames; | ||
| 106 : | sh002i | 915 | croak "(" . join(", ", @keyparts) . "): exists (use put)" |
| 107 : | sh002i | 904 | if $self->exists(@keyparts); |
| 108 : | |||
| 109 : | sh002i | 874 | my $table = $self->{table}; |
| 110 : | sh002i | 904 | my @fieldnames = $self->sqlFieldnames(); |
| 111 : | sh002i | 874 | my $fieldnames = join(", ", @fieldnames); |
| 112 : | my $marks = join(", ", map { "?" } @fieldnames); | ||
| 113 : | sh002i | 904 | |
| 114 : | my @realFieldnames = $self->{record}->FIELDS(); | ||
| 115 : | my @fieldvalues = map { $Record->$_() } @realFieldnames; | ||
| 116 : | |||
| 117 : | sh002i | 874 | my $stmt = "INSERT INTO $table ($fieldnames) VALUES ($marks)"; |
| 118 : | sh002i | 915 | $self->debug("SQL-add: $stmt\n"); |
| 119 : | sh002i | 923 | #no warnings; |
| 120 : | #$self->debug("SQL-add: fieldvalues=@fieldvalues\n"); | ||
| 121 : | #use warnings; | ||
| 122 : | sh002i | 874 | |
| 123 : | $self->{driver}->connect("rw"); | ||
| 124 : | my $sth = $self->{driver}->handle()->prepare($stmt); | ||
| 125 : | my $result = $sth->execute(@fieldvalues); | ||
| 126 : | $self->{driver}->disconnect(); | ||
| 127 : | |||
| 128 : | unless (defined $result) { | ||
| 129 : | sh002i | 904 | my @realKeynames = $self->{record}->KEYFIELDS(); |
| 130 : | my @keyvalues = map { $Record->$_() } @realKeynames; | ||
| 131 : | sh002i | 915 | croak "(" . join(", ", @keyvalues) . "): failed to INSERT: $DBI::errstr"; |
| 132 : | sh002i | 874 | } |
| 133 : | |||
| 134 : | return 1; | ||
| 135 : | } | ||
| 136 : | |||
| 137 : | sh002i | 904 | sub get($@) { |
| 138 : | my ($self, @keyparts) = @_; | ||
| 139 : | sh002i | 874 | |
| 140 : | my $table = $self->{table}; | ||
| 141 : | sh002i | 904 | my @keynames = $self->sqlKeynames(); |
| 142 : | sh002i | 874 | |
| 143 : | sh002i | 915 | croak "wrong number of keyparts for table $table (needs: @keynames)" |
| 144 : | sh002i | 904 | unless @keyparts == @keynames; |
| 145 : | sh002i | 874 | |
| 146 : | sh002i | 904 | my $stmt = "SELECT * FROM $table "; |
| 147 : | $stmt .= $self->makeWhereClause(@keyparts); | ||
| 148 : | sh002i | 915 | $self->debug("SQL-get: $stmt\n"); |
| 149 : | sh002i | 874 | |
| 150 : | $self->{driver}->connect("ro"); | ||
| 151 : | sh002i | 904 | my $result = $self->{driver}->handle()->selectrow_arrayref($stmt); |
| 152 : | sh002i | 874 | $self->{driver}->disconnect(); |
| 153 : | sh002i | 904 | # $result comes back undefined if there are no matches. hmm... |
| 154 : | sh002i | 915 | #croak "failed to SELECT: $DBI::errstr" unless defined $result; |
| 155 : | sh002i | 904 | return undef unless defined $result; |
| 156 : | sh002i | 874 | |
| 157 : | sh002i | 904 | my @record = @$result; |
| 158 : | sh002i | 874 | my $Record = $self->{record}->new(); |
| 159 : | sh002i | 904 | my @realFieldnames = $self->{record}->FIELDS(); |
| 160 : | foreach (@realFieldnames) { | ||
| 161 : | sh002i | 874 | $Record->$_(shift @record); |
| 162 : | } | ||
| 163 : | |||
| 164 : | return $Record; | ||
| 165 : | } | ||
| 166 : | |||
| 167 : | sub put($$) { | ||
| 168 : | my ($self, $Record) = @_; | ||
| 169 : | |||
| 170 : | sh002i | 904 | my @realKeynames = $self->{record}->KEYFIELDS(); |
| 171 : | my @keyparts = map { $Record->$_() } @realKeynames; | ||
| 172 : | sh002i | 915 | croak "(" . join(", ", @keyparts) . "): not found (use add)" |
| 173 : | sh002i | 904 | unless $self->exists(@keyparts); |
| 174 : | |||
| 175 : | sh002i | 874 | my $table = $self->{table}; |
| 176 : | sh002i | 904 | my @fieldnames = $self->sqlFieldnames(); |
| 177 : | sh002i | 874 | my $fieldnames = join(", ", @fieldnames); |
| 178 : | my $marks = join(", ", map { "?" } @fieldnames); | ||
| 179 : | sh002i | 904 | |
| 180 : | my @realFieldnames = $self->{record}->FIELDS(); | ||
| 181 : | my @fieldvalues = map { $Record->$_() } @realFieldnames; | ||
| 182 : | |||
| 183 : | sh002i | 874 | my $stmt = "UPDATE $table SET"; |
| 184 : | while (@fieldnames) { | ||
| 185 : | sh002i | 904 | $stmt .= " " . (shift @fieldnames) . "=?"; |
| 186 : | sh002i | 874 | $stmt .= "," if @fieldnames; |
| 187 : | } | ||
| 188 : | sh002i | 923 | $stmt .= " "; |
| 189 : | $stmt .= $self->makeWhereClause(map { $Record->$_() } @realKeynames); | ||
| 190 : | sh002i | 915 | $self->debug("SQL-put: $stmt\n"); |
| 191 : | sh002i | 874 | |
| 192 : | $self->{driver}->connect("rw"); | ||
| 193 : | my $sth = $self->{driver}->handle()->prepare($stmt); | ||
| 194 : | my $result = $sth->execute(@fieldvalues); | ||
| 195 : | $self->{driver}->disconnect(); | ||
| 196 : | |||
| 197 : | unless (defined $result) { | ||
| 198 : | sh002i | 904 | #my @realKeynames = $self->{record}->KEYFIELDS(); |
| 199 : | #my @keyvalues = map { $Record->$_() } @realKeynames; | ||
| 200 : | sh002i | 915 | croak "(" . join(", ", @keyparts) . "): failed to UPDATE: $DBI::errstr"; |
| 201 : | sh002i | 874 | } |
| 202 : | |||
| 203 : | return 1; | ||
| 204 : | } | ||
| 205 : | |||
| 206 : | sh002i | 904 | sub delete($@) { |
| 207 : | sh002i | 874 | my ($self, @keyparts) = @_; |
| 208 : | |||
| 209 : | sh002i | 972 | #croak "(" . join(", ", @keyparts) . "): not found" |
| 210 : | return 0 unless $self->exists(@keyparts); | ||
| 211 : | sh002i | 904 | |
| 212 : | sh002i | 874 | my $table = $self->{table}; |
| 213 : | sh002i | 904 | my @keynames = $self->sqlKeynames(); |
| 214 : | sh002i | 874 | |
| 215 : | sh002i | 915 | croak "wrong number of keyparts for table $table (needs: @keynames)" |
| 216 : | sh002i | 904 | unless @keyparts == @keynames; |
| 217 : | sh002i | 874 | |
| 218 : | sh002i | 904 | my $stmt = "DELETE FROM $table "; |
| 219 : | $stmt .= $self->makeWhereClause(@keyparts); | ||
| 220 : | sh002i | 915 | $self->debug("SQL-delete: $stmt\n"); |
| 221 : | sh002i | 874 | |
| 222 : | sh002i | 904 | $self->{driver}->connect("rw"); |
| 223 : | my $result = $self->{driver}->handle()->do($stmt); | ||
| 224 : | sh002i | 874 | $self->{driver}->disconnect(); |
| 225 : | sh002i | 915 | croak "failed to DELETE: $DBI::errstr" unless defined $result; |
| 226 : | sh002i | 874 | |
| 227 : | sh002i | 904 | if ($result > 1) { |
| 228 : | warn "danger! deleted more than one record!"; | ||
| 229 : | sh002i | 874 | } |
| 230 : | |||
| 231 : | sh002i | 904 | return $result; |
| 232 : | } | ||
| 233 : | |||
| 234 : | ################################################################################ | ||
| 235 : | # utility functions | ||
| 236 : | ################################################################################ | ||
| 237 : | |||
| 238 : | sub makeWhereClause($@) { | ||
| 239 : | my ($self, @keyparts) = @_; | ||
| 240 : | |||
| 241 : | my @keynames = $self->sqlKeynames(); | ||
| 242 : | my $where; | ||
| 243 : | my $first = 1; | ||
| 244 : | while (@keyparts) { | ||
| 245 : | unless (defined $keyparts[0]) { | ||
| 246 : | shift @keynames; | ||
| 247 : | shift @keyparts; | ||
| 248 : | next; | ||
| 249 : | } | ||
| 250 : | $where .= " AND" unless $first; | ||
| 251 : | $where .= " " . (shift @keynames); | ||
| 252 : | $where .= "='" . (shift @keyparts) . "'"; | ||
| 253 : | $first = 0; | ||
| 254 : | sh002i | 874 | } |
| 255 : | |||
| 256 : | sh002i | 904 | return $where ? "WHERE$where" : ""; |
| 257 : | sh002i | 874 | } |
| 258 : | |||
| 259 : | sh002i | 904 | sub sqlKeynames($) { |
| 260 : | my ($self) = @_; | ||
| 261 : | my @keynames = $self->{record}->KEYFIELDS(); | ||
| 262 : | return map { $self->{params}->{fieldOverride}->{$_} || $_ } | ||
| 263 : | @keynames; | ||
| 264 : | } | ||
| 265 : | |||
| 266 : | sub sqlFieldnames($) { | ||
| 267 : | my ($self) = @_; | ||
| 268 : | my @keynames = $self->{record}->FIELDS(); | ||
| 269 : | return map { $self->{params}->{fieldOverride}->{$_} || $_ } | ||
| 270 : | @keynames; | ||
| 271 : | } | ||
| 272 : | |||
| 273 : | sh002i | 915 | sub debug($@) { |
| 274 : | my ($self, @string) = @_; | ||
| 275 : | |||
| 276 : | sh002i | 919 | if ($self->{params}->{debug}) { |
| 277 : | sh002i | 915 | warn @string; |
| 278 : | sh002i | 919 | } |
| 279 : | sh002i | 915 | } |
| 280 : | |||
| 281 : | sh002i | 874 | 1; |
| aubreyja at gmail dot com | ViewVC Help |
| Powered by ViewVC 1.0.9 |