[system] / branches / rel-2-2-dev / webwork2 / lib / WeBWorK / DB / Schema / SQL.pm Repository:
ViewVC logotype

Diff of /branches/rel-2-2-dev/webwork2/lib/WeBWorK/DB/Schema/SQL.pm

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

Revision 903 Revision 904
… …
44 driver => $driver, 44 driver => $driver,
45 table => $table, 45 table => $table,
46 record => $record, 46 record => $record,
47 params => $params, 47 params => $params,
48 }; 48 };
49 $self->{table} = $params->{tableOverride} if $params->{tableOverride};
49 bless $self, $class; 50 bless $self, $class;
50 return $self; 51 return $self;
51} 52}
52 53
53################################################################################ 54################################################################################
54# table access functions 55# table access functions
55################################################################################ 56################################################################################
56 57
57sub list($) { 58sub list($@) {
58 my ($self, @keyparts) = @_; 59 my ($self, @keyparts) = @_;
59 60
60 my $table = $self->{table}; 61 my $table = $self->{table};
61 my @keynames = $self->{record}->KEYFIELDS(); 62 my @keynames = $self->sqlKeynames();
62 my $keynames = join(", ", @keynames); 63 my $keynames = join(", ", @keynames);
64
65 die "too many keyparts for table $table (need at most: @keynames)"
66 if @keyparts > @keynames;
67
63 my $stmt = "SELECT $keynames FROM $table"; 68 my $stmt = "SELECT $keynames FROM $table ";
64 $stmt .= " WHERE" if @keyparts; 69 $stmt .= $self->makeWhereClause(@keyparts);
65 while (@keyparts) { 70 warn "SQL-list: $stmt\n";
66 $stmt .= " " . shift @keynames . "=" . shift @keyparts;
67 $stmt .= " AND" if @keyparts;
68 }
69 71
70 $self->{driver}->connect("ro"); 72 $self->{driver}->connect("ro");
71 my $keys = $self->{driver}->handle()->selectall_arrayref($stmt); 73 my $result = $self->{driver}->handle()->selectall_arrayref($stmt);
72 $self->{driver}->disconnect(); 74 $self->{driver}->disconnect();
73 75 die "failed to SELECT: $DBI::errstr" unless defined $result;
74 unless (defined $keys) {
75 die "failed to SELECT: $DB::errstr";
76 }
77
78 return $keys; 76 return @$result;
79} 77}
80 78
81sub exists($$) { 79sub exists($@) {
82 my ($self, @keyparts) = @_; 80 my ($self, @keyparts) = @_;
83 81
84 my $table = $self->{table}; 82 my $table = $self->{table};
85 my @keynames = $self->{record}->KEYFIELDS(); 83 my @keynames = $self->sqlKeynames();
86 84
87 die "wrong number of keyparts for table $table (needs: @keynames)" 85 die "wrong number of keyparts for table $table (needs: @keynames)"
88 unless (@keyparts == @keynames); 86 unless @keyparts == @keynames;
89 87
90 my $stmt = "SELECT COUNT(*) FROM $table WHERE"; 88 my $stmt = "SELECT COUNT(*) FROM $table ";
91 while (@keyparts) { 89 $stmt .= $self->makeWhereClause(@keyparts);
92 $stmt .= " " . shift @keynames . "=" . shift @keyparts; 90 warn "SQL-exists: $stmt\n";
93 $stmt .= " AND" if @keyparts;
94 }
95 91
96 $self->{driver}->connect("ro"); 92 $self->{driver}->connect("ro");
97 my $exists = $self->{driver}->handle()->do($stmt); 93 my ($result) = $self->{driver}->handle()->selectrow_array($stmt);
98 $self->{driver}->disconnect(); 94 $self->{driver}->disconnect();
99 95 die "failed to SELECT: $DBI::errstr" unless defined $result;
100 unless (defined $exists) { 96 return $result > 0;
101 die "failed to SELECT: $DB::errstr";
102 }
103
104 return $exists;
105} 97}
106 98
107sub add($$) { 99sub add($$) {
108 my ($self, $Record) = @_; 100 my ($self, $Record) = @_;
109 101
110 my $table = $self->{table};
111 my @fieldnames = $self->{record}->FIELDS(); 102 my @realKeynames = $self->{record}->KEYFIELDS();
103 my @keyparts = map { $Record->$_() } @realKeynames;
104 die "(" . join(", ", @keyparts) . "): exists (use put)"
105 if $self->exists(@keyparts);
106
107 my $table = $self->{table};
108 my @fieldnames = $self->sqlFieldnames();
112 my $fieldnames = join(", ", @fieldnames); 109 my $fieldnames = join(", ", @fieldnames);
113 my @fieldvalues = map { $Record->$_() } @fieldnames;
114 my $marks = join(", ", map { "?" } @fieldnames); 110 my $marks = join(", ", map { "?" } @fieldnames);
111
112 my @realFieldnames = $self->{record}->FIELDS();
113 my @fieldvalues = map { $Record->$_() } @realFieldnames;
114
115 my $stmt = "INSERT INTO $table ($fieldnames) VALUES ($marks)"; 115 my $stmt = "INSERT INTO $table ($fieldnames) VALUES ($marks)";
116 warn "SQL-add: $stmt\n";
116 117
117 $self->{driver}->connect("rw"); 118 $self->{driver}->connect("rw");
118 my $sth = $self->{driver}->handle()->prepare($stmt); 119 my $sth = $self->{driver}->handle()->prepare($stmt);
119 my $result = $sth->execute(@fieldvalues); 120 my $result = $sth->execute(@fieldvalues);
120 $self->{driver}->disconnect(); 121 $self->{driver}->disconnect();
121 122
122 unless (defined $result) { 123 unless (defined $result) {
123 my @keynames = $self->{record}->KEYFIELDS(); 124 my @realKeynames = $self->{record}->KEYFIELDS();
124 my @keyvalues = map $Record->$_() } @keynames; 125 my @keyvalues = map { $Record->$_() } @realKeynames;
125 die "(@keyvalues): failed to INSERT: $DB::errstr"; 126 die "(" . join(", ", @keyvalues) . "): failed to INSERT: $DBI::errstr";
126 } 127 }
127 128
128 return 1; 129 return 1;
129} 130}
130 131
131sub get($$) { 132sub get($@) {
132 my ($self, @keyfields) = @_; 133 my ($self, @keyparts) = @_;
133 134
134 my $table = $self->{table}; 135 my $table = $self->{table};
135 my @keynames = $self->{record}->KEYFIELDS(); 136 my @keynames = $self->sqlKeynames();
136 137
137 die "wrong number of keyparts for table $table (needs: @keynames)" 138 die "wrong number of keyparts for table $table (needs: @keynames)"
138 unless (@keyparts == @keynames); 139 unless @keyparts == @keynames;
139 140
140 my $stmt = "SELECT * FROM $table WHERE"; 141 my $stmt = "SELECT * FROM $table ";
141 while (@keyparts) { 142 $stmt .= $self->makeWhereClause(@keyparts);
142 $stmt .= " " . shift @keynames . "=" . shift @keyparts; 143 warn "SQL-get: $stmt\n";
143 $stmt .= " AND" if @keyparts;
144 }
145 144
146 $self->{driver}->connect("ro"); 145 $self->{driver}->connect("ro");
147 my @record = $self->{driver}->handle()->selectrow_array($stmt); 146 my $result = $self->{driver}->handle()->selectrow_arrayref($stmt);
148 $self->{driver}->disconnect(); 147 $self->{driver}->disconnect();
148 # $result comes back undefined if there are no matches. hmm...
149 #die "failed to SELECT: $DBI::errstr" unless defined $result;
150 return undef unless defined $result;
149 151
150 unless (defined @record) { 152 my @record = @$result;
151 die "failed to SELECT: $DB::errstr";
152 }
153
154 my $Record = $self->{record}->new(); 153 my $Record = $self->{record}->new();
155 my @fieldnames = $self->{record}->FIELDS(); 154 my @realFieldnames = $self->{record}->FIELDS();
156 foreach (@fieldnames) { 155 foreach (@realFieldnames) {
157 $Record->$_(shift @record); 156 $Record->$_(shift @record);
158 } 157 }
159 158
160 return $Record; 159 return $Record;
161} 160}
162 161
163sub put($$) { 162sub put($$) {
164 my ($self, $Record) = @_; 163 my ($self, $Record) = @_;
165 164
166 my $table = $self->{table};
167 my @fieldnames = $self->{record}->FIELDS(); 165 my @realKeynames = $self->{record}->KEYFIELDS();
166 my @keyparts = map { $Record->$_() } @realKeynames;
167 die "(" . join(", ", @keyparts) . "): not found (use add)"
168 unless $self->exists(@keyparts);
169
170 my $table = $self->{table};
171 my @fieldnames = $self->sqlFieldnames();
168 my $fieldnames = join(", ", @fieldnames); 172 my $fieldnames = join(", ", @fieldnames);
169 my @fieldvalues = map { $Record->$_() } @fieldnames;
170 my $marks = join(", ", map { "?" } @fieldnames); 173 my $marks = join(", ", map { "?" } @fieldnames);
174
175 my @realFieldnames = $self->{record}->FIELDS();
176 my @fieldvalues = map { $Record->$_() } @realFieldnames;
177
171 my $stmt = "UPDATE $table SET"; 178 my $stmt = "UPDATE $table SET";
172 while (@fieldnames) { 179 while (@fieldnames) {
173 $stmt .= " " . shift @fieldnames . "=?"; 180 $stmt .= " " . (shift @fieldnames) . "=?";
174 $stmt .= "," if @fieldnames; 181 $stmt .= "," if @fieldnames;
175 } 182 }
183 warn "SQL-put: $stmt\n";
176 184
177 $self->{driver}->connect("rw"); 185 $self->{driver}->connect("rw");
178 my $sth = $self->{driver}->handle()->prepare($stmt); 186 my $sth = $self->{driver}->handle()->prepare($stmt);
179 my $result = $sth->execute(@fieldvalues); 187 my $result = $sth->execute(@fieldvalues);
180 $self->{driver}->disconnect(); 188 $self->{driver}->disconnect();
181 189
182 unless (defined $result) { 190 unless (defined $result) {
183 my @keynames = $self->{record}->KEYFIELDS(); 191 #my @realKeynames = $self->{record}->KEYFIELDS();
184 my @keyvalues = map $Record->$_() } @keynames; 192 #my @keyvalues = map { $Record->$_() } @realKeynames;
185 die "(@keyvalues): failed to UPDATE: $DB::errstr"; 193 die "(" . join(", ", @keyparts) . "): failed to UPDATE: $DBI::errstr";
186 } 194 }
187 195
188 return 1; 196 return 1;
189} 197}
190 198
191sub delete($$) { 199sub delete($@) {
200 my ($self, @keyparts) = @_;
201
202 die "(" . join(", ", @keyparts) . "): not found"
203 unless $self->exists(@keyparts);
204
205 my $table = $self->{table};
206 my @keynames = $self->sqlKeynames();
207
208 die "wrong number of keyparts for table $table (needs: @keynames)"
209 unless @keyparts == @keynames;
210
211 my $stmt = "DELETE FROM $table ";
212 $stmt .= $self->makeWhereClause(@keyparts);
213 warn "SQL-delete: $stmt\n";
214
215 $self->{driver}->connect("rw");
216 my $result = $self->{driver}->handle()->do($stmt);
217 $self->{driver}->disconnect();
218 die "failed to DELETE: $DBI::errstr" unless defined $result;
219
220 if ($result > 1) {
221 warn "danger! deleted more than one record!";
222 }
223
224 return $result;
225}
226
227################################################################################
228# utility functions
229################################################################################
230
231sub makeWhereClause($@) {
232 my ($self, @keyparts) = @_;
233
234 my @keynames = $self->sqlKeynames();
235 my $where;
236 my $first = 1;
237 while (@keyparts) {
238 unless (defined $keyparts[0]) {
239 shift @keynames;
240 shift @keyparts;
241 next;
242 }
243 $where .= " AND" unless $first;
244 $where .= " " . (shift @keynames);
245 $where .= "='" . (shift @keyparts) . "'";
246 $first = 0;
247 }
248
249 return $where ? "WHERE$where" : "";
250}
251
252sub sqlKeynames($) {
192 my ($self, @keyparts) = @_; 253 my ($self) = @_;
193
194 my $table = $self->{table};
195 my @keynames = $self->{record}->KEYFIELDS(); 254 my @keynames = $self->{record}->KEYFIELDS();
196 255 return map { $self->{params}->{fieldOverride}->{$_} || $_ }
197 die "wrong number of keyparts for table $table (needs: @keynames)" 256 @keynames;
198 unless (@keyparts == @keynames); 257}
199 258
200 my $stmt = "DELETE FROM $table WHERE"; 259sub sqlFieldnames($) {
201 while (@keyparts) { 260 my ($self) = @_;
202 $stmt .= " " . shift @keynames . "=" . shift @keyparts; 261 my @keynames = $self->{record}->FIELDS();
203 $stmt .= " AND" if @keyparts; 262 return map { $self->{params}->{fieldOverride}->{$_} || $_ }
204 } 263 @keynames;
205
206 $self->{driver}->connect("ro");
207 my $num = $self->{driver}->handle()->do($stmt);
208 $self->{driver}->disconnect();
209
210 unless (defined $num) {
211 die "failed to SELECT: $DB::errstr";
212 }
213
214 unless ($num > 1) {
215 warn "danger! deleted more than one record!";
216 }
217
218 return $num;
219} 264}
220 265
2211; 2661;
Legend:
Removed from v.903  
changed lines
  Added in v.904
aubreyja at gmail dot com
 ViewVC Help
Powered by ViewVC 1.0.9