@EXPORT_OK = qw(
dbh fields hfields qsearch qsearchs dbdef jsearch
str2time_sql str2time_sql_closing regexp_sql not_regexp_sql concat_sql
+ midnight_sql
);
$DEBUG = 0;
qq-( $column $op "" )-;
}
}
+ } elsif ( $op eq '!=' ) {
+ qq-( $column IS NULL OR $column != ? )-;
#if this needs to be re-enabled, it needs to use a custom op like
#"APPROX=" or something (better name?, not '=', to avoid affecting other
# searches
my $error = $self->check;
return $error if $error;
- #single-field unique keys are given a value if false
+ #single-field non-null unique keys are given a value if empty
#(like MySQL's AUTO_INCREMENT or Pg SERIAL)
foreach ( $self->dbdef_table->unique_singles) {
- $self->unique($_) unless $self->getfield($_);
+ next if $self->getfield($_);
+ next if $self->dbdef_table->column($_)->null eq 'NULL';
+ $self->unique($_);
}
#and also the primary key, if the database isn't going to
defined($scalar) ? $scalar : '';
}
+=item count [ WHERE ]
+
+Convenience method for the common case of "SELECT COUNT(*) FROM table",
+with optional WHERE. Must be called as method on a class with an
+associated table.
+
+=cut
+
+sub count {
+ my($self, $where) = (shift, shift);
+ my $table = $self->table or die 'count called on object of class '.ref($self);
+ my $sql = "SELECT COUNT(*) FROM $table";
+ $sql .= " WHERE $where" if $where;
+ $self->scalar_sql($sql);
+}
+
=back
=head1 SUBROUTINES
=item concat_sql [ DRIVER_NAME ] ITEMS_ARRAYREF
-Returns the items concatendated based on database type, using "CONCAT()" for
+Returns the items concatenated based on database type, using "CONCAT()" for
mysql and " || " for Pg and other databases.
You can pass an optional driver name such as "Pg", "mysql" or
}
+=item midnight_sql DATE
+
+Returns an SQL expression to convert DATE (a unix timestamp) to midnight
+on that day in the system timezone, using the default driver name.
+
+=cut
+
+sub midnight_sql {
+ my $driver = driver_name;
+ my $expr = shift;
+ if ( $driver =~ /^mysql/i ) {
+ "UNIX_TIMESTAMP(DATE(FROM_UNIXTIME($expr)))";
+ }
+ else {
+ "EXTRACT( EPOCH FROM DATE(TO_TIMESTAMP($expr)) )";
+ }
+}
+
=back
=head1 BUGS