Author: spadkins
Date: Fri May 12 12:57:40 2006
New Revision: 6291
Modified:
p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm
p5ee/trunk/App-Repository/t/DBI-getset.t
p5ee/trunk/App-Repository/t/DBI-select.t
Log:
added not_in, not_contains, not_matches, and value-side inferred ops
Modified: p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm
==============================================================================
--- p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm (original)
+++ p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm Fri May 12 12:57:40 2006
@@ -486,8 +486,11 @@
my ($self, $table) = @_;
$self->_load_table_metadata($table) if (!defined
$self->{table}{$table}{loaded});
my $table_def = $self->{table}{$table};
- my $columns = $table_def->{default_columns} || $table_def->{columns};
- if ($columns eq "configured") {
+ my $columns = $table_def->{default_columns} || "";
+ if (ref($columns) eq "ARRAY") {
+ # do nothing
+ }
+ elsif ($columns eq "configured") {
$columns = $table_def->{columns};
}
elsif (!$columns || $columns eq "physical") {
@@ -596,21 +599,37 @@
&App::sub_entry if ($App::trace);
my ($self, $table, $params, $options) = @_;
my ($where, $column, $param, $value, $colnum, $repop, $sqlop, $column_def,
$quoted);
- my ($tabledef, $tabcols, %sqlop, $alias, $dbexpr);
+ my ($tabledef, $tabcols, $alias, $dbexpr);
$tabledef = $self->{table}{$table};
$alias = $tabledef->{alias};
$tabcols = $tabledef->{column};
- %sqlop = (
- 'contains' => 'like',
- 'matches' => 'like',
- 'eq' => '=',
- 'ne' => '!=',
- 'le' => '<=',
- 'lt' => '<',
- 'ge' => '>=',
- 'gt' => '>',
- 'in' => 'in',
+ my %sqlop = (
+ "contains" => "like",
+ "matches" => "like",
+ "not_contains" => "not like",
+ "not_matches" => "not like",
+ "eq" => "=",
+ "ne" => "!=",
+ "le" => "<=",
+ "lt" => "<",
+ "ge" => ">=",
+ "gt" => ">",
+ "in" => "in",
+ "not_in" => "not in",
+ );
+ my %repop = (
+ "=~" => "contains",
+ "~" => "contains",
+ "!~" => "not_contains",
+ "==" => "eq",
+ "=" => "eq",
+ "!" => "ne",
+ "!=" => "ne",
+ "<=" => "le",
+ "<" => "lt",
+ ">=" => "ge",
+ ">" => "gt",
);
$where = "";
@@ -636,6 +655,16 @@
$sqlop = $sqlop{$repop};
}
}
+ $value = $params->{$param};
+ if (!$repop && $value && $value =~
s/^(=~|~|!~|==|=|!=|!|<=|<|>=|>)//) {
+ $repop = $repop{$1};
+ $sqlop = $sqlop{$repop};
+ $inferred_op = 0 if ($1 eq "==");
+ }
+ if (!$repop && $value && $value =~ /[\*\?]/) {
+ $repop = "matches";
+ $sqlop = $sqlop{$repop};
+ }
if ($repop eq "verbatim") {
push(@where, "$params->{$param}");
@@ -660,7 +689,7 @@
next if (!defined $column_def); # skip if the column is unknown
- if (! defined $params->{$param}) {
+ if (! defined $value) {
# $value = "?"; # TODO: make this work with the
"contains/matches" operators
if (!$sqlop || $sqlop eq "=") {
push(@where, "$column is null");
@@ -670,8 +699,6 @@
}
}
else {
- $value = $params->{$param};
-
next if ($inferred_op && $value eq "ALL");
if (ref($value) eq "ARRAY") {
@@ -698,11 +725,11 @@
$include_null = 0;
- if ($repop eq "contains") {
+ if ($repop eq "contains" || $repop eq "not_contains") {
$value =~ s/'/\\'/g;
$value = "'%$value%'";
}
- elsif ($repop eq "matches") {
+ elsif ($repop eq "matches" || $repop eq "not_matches") {
$value =~ s/_/\\_/g;
$value =~ s/'/\\'/g;
$value =~ s/\*/%/g;
@@ -741,6 +768,38 @@
}
}
}
+ elsif ($sqlop eq "not in" || ($inferred_op && $sqlop eq "!="))
{
+ if (! defined $value || $value eq "NULL") {
+ $sqlop = "is not";
+ $value = "null";
+ }
+ else {
+ if ($value =~ s/NULL,//g || $value =~ s/,NULL//) {
+ $include_null = 1;
+ }
+ if ($quoted) {
+ $value =~ s/'/\\'/g;
+ if ($value =~ /,/ && !
$tabledef->{param}{$param}{no_auto_in_param}) {
+ $value =~ s/,/','/g;
+ $value = "('$value')";
+ $sqlop = "not in";
+ }
+ else {
+ $value = "'$value'";
+ $sqlop = "!=";
+ }
+ }
+ else {
+ if ($value =~ /,/ && !
$tabledef->{param}{$param}{no_auto_in_param}) {
+ $value = "($value)";
+ $sqlop = "not in";
+ }
+ else {
+ $sqlop = "!=";
+ }
+ }
+ }
+ }
elsif ($quoted) {
$value =~ s/'/\\'/g;
$value = "'$value'";
@@ -751,7 +810,12 @@
$column =~ s/$alias.//g;
}
if ($include_null) {
- push(@where, "($column $sqlop $value or $column is null)");
+ if ($sqlop eq "not in" || $sqlop eq "!=") {
+ push(@where, "($column $sqlop $value and $column is
not null)");
+ }
+ else {
+ push(@where, "($column $sqlop $value or $column is
null)");
+ }
}
else {
push(@where, "$column $sqlop $value");
@@ -1108,18 +1172,34 @@
#print $self->{context}->dump(), "\n";
my %sqlop = (
- 'contains' => 'like',
- 'matches' => 'like',
- 'eq' => '=',
- 'ne' => '!=',
- 'le' => '<=',
- 'lt' => '<',
- 'ge' => '>=',
- 'gt' => '>',
- 'in' => 'in',
+ "contains" => "like",
+ "matches" => "like",
+ "not_contains" => "not like",
+ "not_matches" => "not like",
+ "eq" => "=",
+ "ne" => "!=",
+ "le" => "<=",
+ "lt" => "<",
+ "ge" => ">=",
+ "gt" => ">",
+ "in" => "in",
+ "not_in" => "not in",
+ );
+ my %repop = (
+ "=~" => "contains",
+ "~" => "contains",
+ "!~" => "not_contains",
+ "==" => "eq",
+ "=" => "eq",
+ "!" => "ne",
+ "!=" => "ne",
+ "<=" => "le",
+ "<" => "lt",
+ ">=" => "ge",
+ ">" => "gt",
);
- my ($where_condition, @join_conditions, @criteria_conditions, $param,
$repop, $sqlop, $paramvalue);
+ my ($where_condition, @join_conditions, @criteria_conditions, $param,
$repop, $sqlop, $value);
my ($include_null, $inferred_op);
for ($idx = 0; $idx <= $#$param_order; $idx++) {
@@ -1133,11 +1213,11 @@
# # o TODO: enable non-integer primary key fields (this assumes
integer, no quotes)
# $column = $table_def->{primary_key}; # assumes one column
primary key
# $dbexpr = $table_def->{column}{$column}{dbexpr};
- # if ($paramvalue =~ /,/) {
- # $where_condition = "$dbexpr in ($paramvalue)"; # assumes one
column, non-quoted primary key
+ # if ($value =~ /,/) {
+ # $where_condition = "$dbexpr in ($value)"; # assumes one
column, non-quoted primary key
# }
# else {
- # $where_condition = "$dbexpr = $paramvalue"; # assumes one
column, non-quoted primary key
+ # $where_condition = "$dbexpr = $value"; # assumes one
column, non-quoted primary key
# }
# push(@criteria_conditions, $where_condition);
# next;
@@ -1146,6 +1226,7 @@
$sqlop = "=";
$repop = "";
$inferred_op = 1;
+ $value = $params->{$param};
# check if $param contains an embedded operation, i.e. "name.eq",
"name.contains"
if ($param =~ /^(.*)\.([^.]+)$/) {
$repop = $2;
@@ -1155,6 +1236,15 @@
$sqlop = $sqlop{$repop};
}
}
+ if (!$repop && $value && $value =~ s/^(=~|~|!~|==|=|!=|!|<=|<|>=|>)//)
{
+ $repop = $repop{$1};
+ $sqlop = $sqlop{$repop};
+ $inferred_op = 0 if ($1 eq "==");
+ }
+ if (!$repop && $value && $value =~ /[\*\?]/) {
+ $repop = "matches";
+ $sqlop = $sqlop{$repop};
+ }
if ($repop eq "verbatim") {
push(@criteria_conditions, $params->{$param});
@@ -1181,77 +1271,75 @@
$include_null = 0;
- if (! defined $params->{$param}) {
- # $paramvalue = "?"; # TODO: make this work with the
"contains/matches" operators
+ if (! defined $value) {
+ # $value = "?"; # TODO: make this work with the
"contains/matches" operators
$sqlop = (!$sqlop || $sqlop eq "=") ? "is" : "is not";
- $paramvalue = "null";
+ $value = "null";
}
else {
- $paramvalue = $params->{$param};
-
next if (defined $table_def->{param}{$param}{all_value} &&
- $paramvalue eq $table_def->{param}{$param}{all_value});
+ $value eq $table_def->{param}{$param}{all_value});
- next if ($inferred_op && $paramvalue eq "ALL");
+ next if ($inferred_op && $value eq "ALL");
- if (ref($paramvalue) eq "ARRAY") {
- $paramvalue = join(",", @$paramvalue);
+ if (ref($value) eq "ARRAY") {
+ $value = join(",", @$value);
}
- if ($paramvalue =~ s/[EMAIL PROTECTED](.*)\]$/$1/) { # new @[]
expressions replace !expr!
+ if ($value =~ s/[EMAIL PROTECTED](.*)\]$/$1/) { # new @[]
expressions replace !expr!
$quoted = 0;
}
- elsif ($paramvalue =~ s/[EMAIL PROTECTED](.*)\}$/$1/) { # new @{}
don't work.. perl interpolates... deprecate.
+ elsif ($value =~ s/[EMAIL PROTECTED](.*)\}$/$1/) { # new @{}
don't work.. perl interpolates... deprecate.
$quoted = 0;
}
- elsif ($paramvalue =~ s/^!expr!//) { # deprecated (ugh!)
+ elsif ($value =~ s/^!expr!//) { # deprecated (ugh!)
$quoted = 0;
}
- elsif ($paramvalue =~ /,/ && !
$table_def->{param}{$param}{no_auto_in_param}) {
- $quoted = (defined $column_def->{quoted}) ?
($column_def->{quoted}) : ($paramvalue !~ /^-?[0-9.,]+$/);
+ elsif ($value =~ /,/ && !
$table_def->{param}{$param}{no_auto_in_param}) {
+ $quoted = (defined $column_def->{quoted}) ?
($column_def->{quoted}) : ($value !~ /^-?[0-9.,]+$/);
}
else {
- $quoted = (defined $column_def->{quoted}) ?
($column_def->{quoted}) : ($paramvalue !~ /^-?[0-9.]+$/);
+ $quoted = (defined $column_def->{quoted}) ?
($column_def->{quoted}) : ($value !~ /^-?[0-9.]+$/);
}
- next if ($inferred_op && !$quoted && $paramvalue eq "");
+ next if ($inferred_op && !$quoted && $value eq "");
- if ($repop eq "contains") {
- $paramvalue =~ s/'/\\'/g;
- $paramvalue = "'%$paramvalue%'";
+ if ($repop eq "contains" || $repop eq "not_contains") {
+ $value =~ s/'/\\'/g;
+ $value = "'%$value%'";
}
- elsif ($repop eq "matches") {
- $paramvalue =~ s/_/\\_/g;
- $paramvalue =~ s/'/\\'/g;
- $paramvalue =~ s/\*/%/g;
- $paramvalue =~ s/\?/_/g;
- $paramvalue = "'$paramvalue'";
+ elsif ($repop eq "matches" || $repop eq "not_matches") {
+ $value =~ s/_/\\_/g;
+ $value =~ s/'/\\'/g;
+ $value =~ s/\*/%/g;
+ $value =~ s/\?/_/g;
+ $value = "'$value'";
}
elsif ($sqlop eq "in" || ($inferred_op && $sqlop eq "=")) {
- if (! defined $paramvalue || $paramvalue eq "NULL") {
+ if (! defined $value || $value eq "NULL") {
$sqlop = "is";
- $paramvalue = "null";
+ $value = "null";
}
else {
- if ($paramvalue =~ s/NULL,//g || $paramvalue =~ s/,NULL//)
{
+ if ($value =~ s/NULL,//g || $value =~ s/,NULL//) {
$include_null = 1;
}
if ($quoted) {
- $paramvalue =~ s/'/\\'/g;
- if ($paramvalue =~ /,/ && !
$table_def->{param}{$param}{no_auto_in_param}) {
- $paramvalue =~ s/,/','/g;
- $paramvalue = "('$paramvalue')";
+ $value =~ s/'/\\'/g;
+ if ($value =~ /,/ && !
$table_def->{param}{$param}{no_auto_in_param}) {
+ $value =~ s/,/','/g;
+ $value = "('$value')";
$sqlop = "in";
}
else {
- $paramvalue = "'$paramvalue'";
+ $value = "'$value'";
$sqlop = "=";
}
}
else {
- if ($paramvalue =~ /,/ && !
$table_def->{param}{$param}{no_auto_in_param}) {
- $paramvalue = "($paramvalue)";
+ if ($value =~ /,/ && !
$table_def->{param}{$param}{no_auto_in_param}) {
+ $value = "($value)";
$sqlop = "in";
}
else {
@@ -1260,9 +1348,42 @@
}
}
}
+ elsif ($sqlop eq "not in" || ($inferred_op && $sqlop eq "!=")) {
+
+ if (! defined $value || $value eq "NULL") {
+ $sqlop = "is not";
+ $value = "null";
+ }
+ else {
+ if ($value =~ s/NULL,//g || $value =~ s/,NULL//) {
+ $include_null = 1;
+ }
+ if ($quoted) {
+ $value =~ s/'/\\'/g;
+ if ($value =~ /,/ && !
$table_def->{param}{$param}{no_auto_in_param}) {
+ $value =~ s/,/','/g;
+ $value = "('$value')";
+ $sqlop = "not in";
+ }
+ else {
+ $value = "'$value'";
+ $sqlop = "!=";
+ }
+ }
+ else {
+ if ($value =~ /,/ && !
$table_def->{param}{$param}{no_auto_in_param}) {
+ $value = "($value)";
+ $sqlop = "not in";
+ }
+ else {
+ $sqlop = "!=";
+ }
+ }
+ }
+ }
elsif ($quoted) {
- $paramvalue =~ s/'/\\'/g;
- $paramvalue = "'$paramvalue'";
+ $value =~ s/'/\\'/g;
+ $value = "'$value'";
}
}
@@ -1270,10 +1391,15 @@
if (defined $dbexpr && $dbexpr ne "") {
$self->_require_tables($dbexpr, \%reqd_tables, $tablealiashref, 2);
if ($include_null) {
- push(@criteria_conditions, "($dbexpr $sqlop $paramvalue or
$dbexpr is null)");
+ if ($sqlop eq "not in" || $sqlop eq "!=") {
+ push(@criteria_conditions, "($dbexpr $sqlop $value and
$dbexpr is not null)");
+ }
+ else {
+ push(@criteria_conditions, "($dbexpr $sqlop $value or
$dbexpr is null)");
+ }
}
else {
- push(@criteria_conditions, "$dbexpr $sqlop $paramvalue");
+ push(@criteria_conditions, "$dbexpr $sqlop $value");
}
}
}
Modified: p5ee/trunk/App-Repository/t/DBI-getset.t
==============================================================================
--- p5ee/trunk/App-Repository/t/DBI-getset.t (original)
+++ p5ee/trunk/App-Repository/t/DBI-getset.t Fri May 12 12:57:40 2006
@@ -80,7 +80,7 @@
$dbh->do("insert into test_person (person_id,age,first_name,gender,state)
values (3, 6,'maryalice','F','GA')");
$dbh->do("insert into test_person (person_id,age,first_name,gender,state)
values (4, 3,'paul', 'M','GA')");
$dbh->do("insert into test_person (person_id,age,first_name,gender,state)
values (5, 1,'christine','F','GA')");
- $dbh->do("insert into test_person (person_id,age,first_name,gender,state)
values (6,45,'tim', 'M','GA')");
+ $dbh->do("insert into test_person (person_id,age,first_name,gender,state)
values (6,45,'tim', 'M','FL')");
$dbh->do("insert into test_person (person_id,age,first_name,gender,state)
values (7,39,'keith', 'M','GA')");
}
@@ -97,7 +97,7 @@
[ 3, 6, "maryalice", "F", "GA", ],
[ 4, 3, "paul", "M", "GA", ],
[ 5, 1, "christine", "F", "GA", ],
- [ 6, 45, "tim", "M", "GA", ],
+ [ 6, 45, "tim", "M", "FL", ],
[ 7, 39, "keith", "M", "GA", ],
];
@@ -151,7 +151,7 @@
is($hash->{state}, "GA", "get_hash(1) state");
ok($rep->set("test_person", {first_name => "steve"}, {person_id => 1, age =>
41}), "set(table,\$params,\%hash)");
-ok($rep->set("test_person", {person_id => 8, age => 37, first_name => "nick",
gender => "M", state => "NY"},
+ok($rep->set("test_person", {person_id => 8, age => 37, first_name => "nick",
gender => "M", state => undef},
undef, undef, {create=>1}),
"set(table,\$params,\%hash) : insert");
is($rep->set("test_person", {gender => "F", age => 41}), 0,
@@ -174,7 +174,7 @@
is($hash->{age}, 37, "get_hashes()->[n] age");
is($hash->{first_name}, "nick", "get_hashes()->[n] first_name");
is($hash->{gender}, "M", "get_hashes()->[n] gender");
-is($hash->{state}, "NY", "get_hashes()->[n] state");
+is($hash->{state}, undef, "get_hashes()->[n] state");
eval {
$nrows = $rep->set("test_person", undef, "gender", "M");
@@ -182,6 +182,53 @@
};
ok($@, "set() with undef params");
+####################################################################
+# Exercise the special implied where conditions
+####################################################################
+#my $rows2 = $rep->get_rows("test_person", {},
["person_id","age","first_name","gender","state"]);
+#foreach my $row (@$rows2) {
+# print "ROW: [", join("|", map { defined $_ ? $_ : "undef" } @$row), "]\n";
+#}
+
+$hashes = $rep->get_hashes("test_person", {first_name => "!steve,joe,nick"});
+is($#$hashes+1, 6, "get_hashes(!steve,joe,nick)");
+$hashes = $rep->get_hashes("test_person", {first_name => "steve,joe,nick"});
+is($#$hashes+1, 2, "get_hashes(steve,joe,nick)");
+$hashes = $rep->get_hashes("test_person", {first_name => "=steve,joe,nick"});
+is($#$hashes+1, 2, "get_hashes(=steve,joe,nick)");
+$hashes = $rep->get_hashes("test_person", {first_name => "==steve,joe,nick"});
+is($#$hashes+1, 0, "get_hashes(==steve,joe,nick)");
+$hashes = $rep->get_hashes("test_person", {state => "GA"});
+is($#$hashes+1, 5, "get_hashes(GA)");
+$hashes = $rep->get_hashes("test_person", {state => "GA,NULL"});
+is($#$hashes+1, 6, "get_hashes(GA,NULL)");
+$hashes = $rep->get_hashes("test_person", {state => "!GA,NULL"});
+is($#$hashes+1, 2, "get_hashes(!GA,NULL)");
+$hashes = $rep->get_hashes("test_person", {state => "GA,CA"});
+is($#$hashes+1, 6, "get_hashes(GA,CA)");
+$hashes = $rep->get_hashes("test_person", {state => "!GA,CA"});
+is($#$hashes+1, 1, "get_hashes(!GA,CA)");
+$hashes = $rep->get_hashes("test_person", {"state.not_in" => ["GA","CA"]});
+is($#$hashes+1, 1, "get_hashes not_in [GA,CA]");
+$hashes = $rep->get_hashes("test_person", {"state.not_in" => "GA,CA"});
+is($#$hashes+1, 1, "get_hashes not_in (GA,CA)");
+$hashes = $rep->get_hashes("test_person", {"state.in" => "!GA,CA"});
+is($#$hashes+1, 1, "get_hashes in (!GA,CA)");
+$hashes = $rep->get_hashes("test_person", {"state.eq" => "!GA,CA"});
+is($#$hashes+1, 0, "get_hashes eq (!GA,CA)");
+$hashes = $rep->get_hashes("test_person", {"state.contains" => "A"});
+is($#$hashes+1, 6, "get_hashes contains (A)");
+$hashes = $rep->get_hashes("test_person", {"state.not_contains" => "A"});
+is($#$hashes+1, 1, "get_hashes not_contains (A)");
+
+$hashes = $rep->get_hashes("test_person", {"state.matches" => "?A"});
+is($#$hashes+1, 6, "get_hashes matches (?A)");
+$hashes = $rep->get_hashes("test_person", {"state" => "?A"});
+is($#$hashes+1, 6, "get_hashes (?A)");
+$hashes = $rep->get_hashes("test_person", {"state.not_matches" => "?A"});
+is($#$hashes+1, 1, "get_hashes not_matches (?A)");
+
+#print $rep->{sql};
exit(0);
#####################################################################
# $rep->set_rows($table, undef, [EMAIL PROTECTED], $rows, \%options);
Modified: p5ee/trunk/App-Repository/t/DBI-select.t
==============================================================================
--- p5ee/trunk/App-Repository/t/DBI-select.t (original)
+++ p5ee/trunk/App-Repository/t/DBI-select.t Fri May 12 12:57:40 2006
@@ -287,6 +287,14 @@
},["first_name"]);
is($sql, $expect_sql, "_mk_select_sql(): param.eq => in");
&check_select($sql,0);
+$sql = $rep->_mk_select_sql("test_person",{
+ "_order" => [ "first_name", "age", "birth_dt", ],
+ "first_name" => "==stephen,paul",
+ "age" => "=37,39",
+ "birth_dt" => "==1962-01-01,1963-12-31",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_sql(): param.eq => in (inferred)");
+&check_select($sql,0);
$expect_sql = <<EOF;
select
@@ -423,23 +431,91 @@
},["first_name"]);
is($sql, $expect_sql, "_mk_select_sql(): param.contains");
&check_select($sql,0);
+$sql = $rep->_mk_select_sql("test_person",{
+ "_order" => [ "first_name", "age", "birth_dt", ],
+ "first_name" => "=~s",
+ "age" => "=~3",
+ "birth_dt" => "~1962",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_sql(): param.contains (inferred)");
+&check_select($sql,0);
$expect_sql = <<EOF;
select
first_name
from test_person
-where first_name like '%s%'
+where first_name not like '%s%'
+ and age not like '%3%'
+ and birth_dt not like '%1962%'
+EOF
+$sql = $rep->_mk_select_sql("test_person",{
+ "_order" => [ "first_name.not_contains", "age.not_contains",
"birth_dt.not_contains", ],
+ "first_name.not_contains" => "s",
+ "age.not_contains" => "3",
+ "birth_dt.not_contains" => "1962",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_sql(): param.contains");
+&check_select($sql,0);
+$sql = $rep->_mk_select_sql("test_person",{
+ "_order" => [ "first_name", "age", "birth_dt", ],
+ "first_name" => "!~s",
+ "age" => "!~3",
+ "birth_dt" => "!~1962",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_sql(): param.not_contains (inferred)");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ first_name
+from test_person
+where first_name like '%s%e_'
and age like '%3'
- and birth_dt like '1962%'
+ and birth_dt like '1962\\_%'
EOF
$sql = $rep->_mk_select_sql("test_person",{
"_order" => [ "first_name.matches", "age.matches", "birth_dt.matches",
],
- "first_name.matches" => "*s*",
+ "first_name.matches" => "*s*e?",
"age.matches" => "*3",
- "birth_dt.matches" => "1962*",
+ "birth_dt.matches" => "1962_*",
},["first_name"]);
is($sql, $expect_sql, "_mk_select_sql(): param.matches");
&check_select($sql,0);
+$sql = $rep->_mk_select_sql("test_person",{
+ "_order" => [ "first_name", "age", "birth_dt", ],
+ "first_name" => "*s*e?",
+ "age" => "*3",
+ "birth_dt" => "1962_*",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_sql(): param.matches (inferred)");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ first_name
+from test_person
+where first_name not like '%s%'
+ and age not like '%3'
+ and birth_dt not like '1962%'
+EOF
+$sql = $rep->_mk_select_sql("test_person",{
+ "_order" => [ "first_name.not_matches", "age.not_matches",
"birth_dt.not_matches", ],
+ "first_name.not_matches" => "*s*",
+ "age.not_matches" => "*3",
+ "birth_dt.not_matches" => "1962*",
+ },["first_name"]);
+is($sql, $expect_sql, "_mk_select_sql(): param.not_matches");
+&check_select($sql,0);
+
+# this doesn't work yet, but that's ok
+#$sql = $rep->_mk_select_sql("test_person",{
+# "_order" => [ "first_name", "age", "birth_dt", ],
+# "first_name" => "!*s*",
+# "age" => "!*3",
+# "birth_dt" => "!1962*",
+# },["first_name"]);
+#is($sql, $expect_sql, "_mk_select_sql(): param.not_matches (inferred)");
+#&check_select($sql,0);
$expect_sql = <<EOF;
select
@@ -491,6 +567,76 @@
is($sql, $expect_sql, "_mk_select_sql(): verbatim");
&check_select($sql,0);
+###########################################################################
+# NULL CONDITIONS (AND "IN")
+###########################################################################
+
+$expect_sql = <<EOF;
+select
+ gender
+from test_person
+where age is null
+EOF
+$sql = $rep->_mk_select_sql("test_person", { age => "NULL", }, ["gender"]);
+is($sql, $expect_sql, "_mk_select_sql(): is null (by 'NULL')");
+&check_select($sql,0);
+$sql = $rep->_mk_select_sql("test_person", { age => undef, }, ["gender"]);
+is($sql, $expect_sql, "_mk_select_sql(): is null (by undef)");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ gender
+from test_person
+where age is not null
+EOF
+$sql = $rep->_mk_select_sql("test_person", { age => "!NULL", }, ["gender"]);
+is($sql, $expect_sql, "_mk_select_sql(): is not null (by '!NULL')");
+&check_select($sql,0);
+$sql = $rep->_mk_select_sql("test_person", { "age.ne" => undef, }, ["gender"]);
+is($sql, $expect_sql, "_mk_select_sql(): is not null (by .ne undef)");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ gender
+from test_person
+where first_name is not null
+EOF
+$sql = $rep->_mk_select_sql("test_person", { first_name => "!NULL", },
["gender"]);
+is($sql, $expect_sql, "_mk_select_sql(): is not null (by '!NULL')");
+&check_select($sql,0);
+$sql = $rep->_mk_select_sql("test_person", { "first_name.ne" => undef, },
["gender"]);
+is($sql, $expect_sql, "_mk_select_sql(): is not null (by .ne undef)");
+&check_select($sql,0);
+
+$expect_sql = <<EOF;
+select
+ gender
+from test_person
+where (first_name not in ('stephen','keith') and first_name is not null)
+EOF
+$sql = $rep->_mk_select_sql("test_person", { first_name =>
"!stephen,keith,NULL", }, ["gender"]);
+is($sql, $expect_sql, "_mk_select_sql(): not in and not null (by
'!stephen,keith,NULL')");
+&check_select($sql,0);
+$sql = $rep->_mk_select_sql("test_person", { "first_name.not_in" =>
"stephen,keith,NULL", }, ["gender"]);
+is($sql, $expect_sql, "_mk_select_sql(): is not null (by .not_in
'stephen,keith,NULL')");
+&check_select($sql,0);
+exit(0);
+
+$expect_sql = <<EOF;
+select
+ gender
+from test_person
+where first_name is not null
+EOF
+$sql = $rep->_mk_select_sql("test_person", { age => "!NULL", }, ["gender"]);
+is($sql, $expect_sql, "_mk_select_sql(): is not null (by '!NULL')");
+&check_select($sql,0);
+$sql = $rep->_mk_select_sql("test_person", { "age.ne" => undef, }, ["gender"]);
+is($sql, $expect_sql, "_mk_select_sql(): is not null (by .ne undef)");
+&check_select($sql,0);
+
$expect_sql = <<EOF;
select
first_name,
@@ -590,7 +736,7 @@
$sql = $rep->_mk_select_sql("test_person",
{"age" => "ALL"},
["first_name","last_name"]);
-is($sql, $expect_sql, "_mk_select_sql(): explicit ALL");
+is($sql, $expect_sql, "_mk_select_sql(): explicit ALL adds nothing to the
where clause");
&check_select($sql,0);
$expect_sql = <<EOF;
@@ -606,6 +752,24 @@
&check_select($sql,0);
###########################################################################
+# NEW REPOPS CONDITIONS
+###########################################################################
+
+$expect_sql = <<EOF;
+select
+ gender
+from test_person
+where age is null
+EOF
+$sql = $rep->_mk_select_sql("test_person", { age => "NULL", }, ["gender"]);
+is($sql, $expect_sql, "_mk_select_sql(): is null (by 'NULL')");
+&check_select($sql,0);
+$sql = $rep->_mk_select_sql("test_person", { age => undef, }, ["gender"]);
+is($sql, $expect_sql, "_mk_select_sql(): is null (by undef)");
+&check_select($sql,0);
+exit(0); # XXX REMOVE EXIT HERE XXX
+
+###########################################################################
# LITERAL EXPRESSIONS
###########################################################################
