| 159 | | specifined by $bindParams. $options is an optional hash-reference which can define other |
| 160 | | selection options, such as how to order the results and limit the results returned. |
| 161 | | It has 3 elements: pageLength and pageOffset, and orderBy. pageLength specifies |
| 162 | | how many rows to return, while pageOffset specifies where to start counting from. |
| 163 | | orderBy can be a string such as 'username, password' which will then order the results |
| 164 | | first by the username column, then by the password column. By default, rows are ordered |
| 165 | | by primary key. |
| | 159 | specifined by $bindParams. |
| | 160 | |
| | 161 | If specified, $options should be a hash-reference which can be used to control the specifics of |
| | 162 | the query that you wish to run. |
| | 163 | |
| | 164 | The keys of the options hash are as follows: |
| | 165 | |
| | 166 | pageLength |
| | 167 | pageOffset |
| | 168 | orderBy |
| | 169 | count |
| | 170 | distinct |
| | 171 | fields |
| | 172 | |
| | 173 | pageLength can be used to limit the number of results returned. Setting it to 5 will return the |
| | 174 | first 5 results. |
| | 175 | |
| | 176 | pageOffset can be used to skip the first N rows in the result set. Together with pageLength, |
| | 177 | this makes up the LIMIT clause. pageLength must be specified before pageOffset is obeyed. |
| | 178 | |
| | 179 | orderBy can be used control the order of the rows returned. By default, they are returned |
| | 180 | in Primary Key order, but you can specify a comma-seperated list of fields to sort by, |
| | 181 | such as: {orderBy => 'groupId, userName'} |
| | 182 | |
| | 183 | If 'count' is specified, then only the number of rows are returned, rather than the |
| | 184 | rows themselves. This is a boolean option. |
| | 185 | |
| | 186 | If 'distinct' is specified, then only unique rows are returned. Can be used with 'count' |
| | 187 | so that only unique rows are counted. |
| | 188 | |
| | 189 | If 'fields' is specified, then it should be an array reference, and can be used to specify |
| | 190 | which fields are to be returned. By default, all fields in the table are selected. The row |
| | 191 | will be marked read-only unless you specify the primary key fields, because the primary key |
| | 192 | must be known if it is to be updated. Can be used with 'distinct' to pull a unique subset |
| | 193 | of the table. |
| 199 | | $limitClause = sprintf(' LIMIT %d OFFSET %d', $options->{'pageLength'}, $options->{'pageOffset'}); |
| | 232 | # %d is safe to use without having to worry about SQL injection etc. |
| | 233 | if (defined ($dbh->{'mysql_serverinfo'})) # Because MySQL 3.x doesnt support the OFFSET clause |
| | 234 | { |
| | 235 | $limitClause = sprintf(' LIMIT %d, %d', $options->{'pageOffset'}, $options->{'pageLength'}); |
| | 236 | } |
| | 237 | else |
| | 238 | { |
| | 239 | $limitClause = sprintf(' LIMIT %d OFFSET %d', $options->{'pageLength'}, $options->{'pageOffset'}); |
| | 240 | } |
| | 241 | } |
| | 242 | my $distinctClause = ''; |
| | 243 | if ($options->{'distinct'}) |
| | 244 | { |
| | 245 | $distinctClause = 'DISTINCT'; |
| | 246 | } |
| | 247 | my $fieldsList = join(',', @{$options->{'fields'}}); |
| | 248 | if ($options->{'count'}) |
| | 249 | { |
| | 250 | $fieldsList = 'count(*)'; |
| 236 | | my %row = (_dbh => $dbh, |
| 237 | | _table => $table); |
| 238 | | $get_obj_sth->bind_columns(map { \$row{$_} } $table->primaryKeys(), $table->fields()); |
| | 287 | if ($options->{'count'}) |
| | 288 | { |
| | 289 | my $result = $get_obj_sth->fetchall_arrayref()->[0]->[0]; |
| | 290 | $get_obj_sth->finish; |
| | 291 | return $result; |
| | 292 | } |
| | 293 | |
| | 294 | # Mark each row as read-only unless we have all the primary key fields |
| | 295 | # needed to do an update accuratley. |
| | 296 | my $readonly = 0; |
| | 297 | foreach my $pKey ($table->primaryKeys) |
| | 298 | { |
| | 299 | my $found = 0; |
| | 300 | foreach my $f (@{$options->{'fields'}}) |
| | 301 | { |
| | 302 | $found = 1 if ($f eq $pKey); |
| | 303 | } |
| | 304 | $readonly = 1 unless ($found); |
| | 305 | } |
| | 306 | my %row = (_dbh => $dbh, |
| | 307 | _table => $table, |
| | 308 | _readonly => $readonly); |
| | 309 | $get_obj_sth->bind_columns(map { \$row{$_} } @{$options->{'fields'}}); |
| 319 | | my $dbh = shift || confess("Usage: $class->searchByString(\$dbh, \$table, \$string);"); |
| 320 | | my $table = shift || confess("Usage: $class->searchByString(\$dbh, \$table, \$string);"); |
| 321 | | my $string = shift || confess("Usage: $class->searchByString(\$dbh, \$table, \$string);"); |
| 322 | | my $options = shift; |
| 323 | | |
| 324 | | my $where = join(' OR ', map { "LOWER($_\::text) LIKE ?" } $table->fields()); |
| 325 | | my @params = map { lc("\%$string\%") } $table->fields(); |
| | 390 | my $dbh = shift || confess("Usage: $class->searchByString(\$dbh, \$table, \$string [, \$options ]);"); |
| | 391 | my $table = shift || confess("Usage: $class->searchByString(\$dbh, \$table, \$string [, \$options ]);"); |
| | 392 | my $string = shift || confess("Usage: $class->searchByString(\$dbh, \$table, \$string [, \$options ]);"); |
| | 393 | my $options = shift || {}; |
| | 394 | |
| | 395 | my @fields; |
| | 396 | foreach my $f ($table->fields()) # boolean types cannot be cast to text. |
| | 397 | { # so we skip them. |
| | 398 | unless ($table->field($f)->type eq 'boolean') |
| | 399 | { |
| | 400 | push @fields, $f; |
| | 401 | } |
| | 402 | } |
| | 403 | |
| | 404 | my $where = join(' OR ', map { "LOWER($_\::text) LIKE ?" } @fields); |
| | 405 | my @params = map { lc("\%$string\%") } @fields; |
| 421 | | # TODO: Use $dbh->last_insert_id() instead of this. |
| 422 | | my $seqName = sprintf("%s_%s_seq", $self->{'_table'}->name(), ($self->{'_table'}->primaryKeys())[0]); |
| 423 | | my $lastIdSql = "SELECT CURRVAL(?)"; |
| 424 | | my $lastIdSth = $self->{'_dbh'}->prepare_cached($lastIdSql, {pg_prepare_now => 1}, 3) |
| 425 | | or confess (sprintf("Could not prepare_cached(%s): Error Code %d (%s)", $lastIdSql, $self->{'_dbh'}->err, $self->{'_dbh'}->errstr)); |
| 426 | | |
| 427 | | $lastIdSth->execute($seqName); |
| 428 | | my $newId = $lastIdSth->fetchall_arrayref()->[0]->[0]; |
| | 501 | my $newId = $self->{'_dbh'}->last_insert_id(undef, undef, $self->{'_table'}->name(), undef); |
