Update of /cvsroot/monetdb/sql/src/server
In directory 23jxhf1.ch3.sourceforge.com:/tmp/cvs-serv18234/server
Modified Files:
bin_optimizer.mx rel_bin.mx rel_dump.mx rel_optimizer.mx
rel_select.mx sql_parser.mx sql_rel2bin.mx sql_scan.mx
sql_statement.mx
Log Message:
Add the ILIKE operator to SQL for Stefan de Konink
U sql_rel2bin.mx
Index: sql_rel2bin.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/sql_rel2bin.mx,v
retrieving revision 1.135
retrieving revision 1.136
diff -u -d -r1.135 -r1.136
--- sql_rel2bin.mx 25 Jul 2009 22:08:38 -0000 1.135
+++ sql_rel2bin.mx 1 Aug 2009 14:47:46 -0000 1.136
@@ -595,9 +595,20 @@
}
/* finally collect all saved like, notlike, and
notequal selects */
- for (ct = cmp_like; ct >= cmp_notequal; ct--) {
- list_merge(newsels, sels1[ct], (fdup)
&stmt_dup);
- list_destroy(sels1[ct]);
+ {
+ comp_type *ctp;
+ comp_type cts[] = {
+ cmp_ilike,
+ cmp_notilike,
+ cmp_like,
+ cmp_notlike,
+ cmp_notequal,
+ (comp_type)0
+ };
+ for (ctp = cts; *ctp != (comp_type)0; ctp++) {
+ list_merge(newsels, sels1[*ctp], (fdup)
&stmt_dup);
+ list_destroy(sels1[*ctp]);
+ }
}
}
list_destroy(colsels);
@@ -916,11 +927,13 @@
comp_type cmp = (comp_type)select->flag;
stmt *op2 = stmt_dup( select->op2.stval);
- if (cmp == cmp_like || cmp == cmp_notlike) {
- stmt *op3 = stmt_dup( select->op3.stval);
+ if (cmp == cmp_like || cmp == cmp_notlike ||
+ cmp == cmp_ilike || cmp == cmp_notilike)
+ {
+ stmt *op3 = stmt_dup(select->op3.stval);
stmt_destroy(select);
- return stmt_likeselect( s, op2, op3, cmp);
+ return stmt_likeselect(s, op2, op3, cmp);
} else {
stmt_destroy(select);
return stmt_select( s, op2, cmp);
U rel_bin.mx
Index: rel_bin.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_bin.mx,v
retrieving revision 1.85
retrieving revision 1.86
diff -u -d -r1.85 -r1.86
--- rel_bin.mx 29 Jul 2009 09:04:24 -0000 1.85
+++ rel_bin.mx 1 Aug 2009 14:47:38 -0000 1.86
@@ -520,8 +520,10 @@
return NULL;
}
- /* the escape charachter of like is in the right expression */
- if (e->flag == cmp_notlike || e->flag == cmp_like) {
+ /* the escape character of like is in the right expression */
+ if (e->flag == cmp_notlike || e->flag == cmp_like ||
+ e->flag == cmp_notilike || e->flag == cmp_ilike)
+ {
if (!e->f)
r2 = stmt_atom_string(_strdup(""));
if (!l || !r || !r2) {
@@ -533,8 +535,11 @@
}
if (l->nrcols == 0) {
stmt *lstmt;
+ char *likef = (e->flag == cmp_notilike ||
e->flag == cmp_ilike ?
+ "ilike" : "like");
sql_subtype *s = sql_bind_localtype("str");
- sql_subfunc *like =
sql_bind_func3(sql->session->schema, "like", s, s, s);
+ sql_subfunc *like =
sql_bind_func3(sql->session->schema,
+ likef, s, s, s);
list *ops = create_stmt_list();
assert(s && like);
@@ -545,7 +550,8 @@
lstmt = stmt_Nop(stmt_list(ops), like);
if (e->flag == cmp_notlike) {
sql_subtype *bt =
sql_bind_localtype("bit");
- sql_subfunc *not =
sql_bind_func(sql->session->schema, "not", bt, NULL);
+ sql_subfunc *not =
sql_bind_func(sql->session->schema,
+ "not", bt, NULL);
lstmt = stmt_unop(lstmt, not);
}
return lstmt;
@@ -570,9 +576,14 @@
if (r2) {
if (l->nrcols == 0 && r->nrcols == 0 &&
r2->nrcols == 0) {
sql_subtype *bt =
sql_bind_localtype("bit");
- sql_subfunc *lf =
sql_bind_func(sql->session->schema, compare_func(range2lcompare(e->flag)),
tail_type(l), tail_type(r));
- sql_subfunc *rf =
sql_bind_func(sql->session->schema, compare_func(range2rcompare(e->flag)),
tail_type(l), tail_type(r));
- sql_subfunc *a =
sql_bind_func(sql->session->schema, "and", bt, bt);
+ sql_subfunc *lf =
sql_bind_func(sql->session->schema,
+
compare_func(range2lcompare(e->flag)),
+ tail_type(l),
tail_type(r));
+ sql_subfunc *rf =
sql_bind_func(sql->session->schema,
+
compare_func(range2rcompare(e->flag)),
+ tail_type(l),
tail_type(r));
+ sql_subfunc *a =
sql_bind_func(sql->session->schema,
+ "and", bt, bt);
assert(lf && rf && a);
l = stmt_dup(l);
s = stmt_binop(
@@ -589,7 +600,9 @@
} else {
/* value compare or select */
if (l->nrcols == 0 && r->nrcols == 0) {
- sql_subfunc *f =
sql_bind_func(sql->session->schema, compare_func((comp_type)e->flag),
tail_type(l), tail_type(r));
+ sql_subfunc *f =
sql_bind_func(sql->session->schema,
+
compare_func((comp_type)e->flag),
+ tail_type(l),
tail_type(r));
assert(f);
s = stmt_binop(l, r, f);
} else
U rel_optimizer.mx
Index: rel_optimizer.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_optimizer.mx,v
retrieving revision 1.66
retrieving revision 1.67
diff -u -d -r1.66 -r1.67
--- rel_optimizer.mx 26 Jul 2009 21:38:25 -0000 1.66
+++ rel_optimizer.mx 1 Aug 2009 14:47:41 -0000 1.67
@@ -280,6 +280,8 @@
return 6;
case cmp_like:
case cmp_notlike:
+ case cmp_ilike:
+ case cmp_notilike:
*cnt += 2;
return 2;
case cmp_or: /* prefer union over like */
U sql_statement.mx
Index: sql_statement.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/sql_statement.mx,v
retrieving revision 1.186
retrieving revision 1.187
diff -u -d -r1.186 -r1.187
--- sql_statement.mx 25 Jul 2009 22:08:38 -0000 1.186
+++ sql_statement.mx 1 Aug 2009 14:47:46 -0000 1.187
@@ -136,7 +136,9 @@
cmp_like = 7,
cmp_all = 8,
cmp_or = 9,
- cmp_project = 10
+ cmp_project = 10,
+ cmp_notilike = 11,
+ cmp_ilike = 12
} comp_type;
/* flag to indicate anti join/select */
@@ -1548,7 +1550,8 @@
{
stmt *s = stmt_create(st_uselect);
- assert(cmptype != cmp_like && cmptype != cmp_notlike);
+ assert(cmptype != cmp_like && cmptype != cmp_notlike &&
+ cmptype != cmp_ilike && cmptype != cmp_notilike);
s->op1.stval = op1;
s->op2.stval = op2;
s->flag = cmptype;
@@ -2788,7 +2791,8 @@
case st_ordered: dump2(s, "ordered", nr); s->nr = -s->op1.stval->nr;
break;
case st_select:
case st_uselect:
- if (s->flag == cmp_like || s->flag == cmp_notlike)
+ if (s->flag == cmp_like || s->flag == cmp_notlike ||
+ s->flag == cmp_ilike || s->flag ==
cmp_notilike)
dump3(s, "likeselect", nr);
else
dump2(s, "select", nr);
U rel_dump.mx
Index: rel_dump.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_dump.mx,v
retrieving revision 1.38
retrieving revision 1.39
diff -u -d -r1.38 -r1.39
--- rel_dump.mx 8 Jun 2009 04:43:10 -0000 1.38
+++ rel_dump.mx 1 Aug 2009 14:47:41 -0000 1.39
@@ -72,6 +72,8 @@
case cmp_notequal: r = "!="; break;
case cmp_notlike: r = "notlike"; break;
case cmp_like: r = "like"; break;
+ case cmp_notilike: r = "notilike"; break;
+ case cmp_ilike: r = "ilike"; break;
case cmp_all: r = "all"; break;
case cmp_or: r = "or"; break;
default:
U bin_optimizer.mx
Index: bin_optimizer.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/bin_optimizer.mx,v
retrieving revision 1.18
retrieving revision 1.19
diff -u -d -r1.18 -r1.19
--- bin_optimizer.mx 25 Jul 2009 22:08:38 -0000 1.18
+++ bin_optimizer.mx 1 Aug 2009 14:47:37 -0000 1.19
@@ -82,16 +82,24 @@
} else if (PSEL(s1) && s1->flag == cmp_notequal) {
/* do notequal select last */
swap = 1;
- } else if (PSEL(s2) && s2->flag == cmp_notlike) {
+ } else if (PSEL(s2) &&
+ (s2->flag == cmp_notlike || s2->flag ==
cmp_notilike))
+ {
/* do notequal select last */
swap = 0;
- } else if (PSEL(s1) && s1->flag == cmp_notlike) {
+ } else if (PSEL(s1) &&
+ (s1->flag == cmp_notlike || s1->flag ==
cmp_notilike))
+ {
/* do notequal select last */
swap = 1;
- } else if (PSEL(s2) && s2->flag == cmp_like) {
+ } else if (PSEL(s2) &&
+ (s2->flag == cmp_like || s2->flag ==
cmp_ilike))
+ {
/* do like select last */
swap = 0;
- } else if (PSEL(s1) && s1->flag == cmp_like) {
+ } else if (PSEL(s1) &&
+ (s1->flag == cmp_like || s1->flag ==
cmp_ilike))
+ {
/* do like select last */
swap = 1;
} else if (PSEL(s1)) {
@@ -181,10 +189,13 @@
return stmt_uselect2( s, stmt_dup(select->op2.stval),
stmt_dup(select->op3.stval), (comp_type)select->flag);
if (select->type == st_select) {
- if (select->flag == cmp_like || select->flag == cmp_notlike)
- return stmt_likeselect( s, stmt_dup(select->op2.stval),
stmt_dup(select->op3.stval), (comp_type)select->flag);
+ if (select->flag == cmp_like || select->flag == cmp_notlike ||
+ select->flag == cmp_ilike || select->flag ==
cmp_notilike)
+ return stmt_likeselect(s, stmt_dup(select->op2.stval),
+ stmt_dup(select->op3.stval),
(comp_type)select->flag);
else
- return stmt_select( s, stmt_dup(select->op2.stval),
(comp_type)select->flag);
+ return stmt_select(s, stmt_dup(select->op2.stval),
+ (comp_type)select->flag);
}
if (select->type == st_uselect)
U rel_select.mx
Index: rel_select.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_select.mx,v
retrieving revision 1.149
retrieving revision 1.150
diff -u -d -r1.149 -r1.150
--- rel_select.mx 31 Jul 2009 09:34:16 -0000 1.149
+++ rel_select.mx 1 Aug 2009 14:47:43 -0000 1.150
@@ -1825,8 +1825,14 @@
type = cmp_gte;
} else if (compare_op[0] == 'l') {
type = cmp_like;
+ } else if (compare_op[0] == 'i') {
+ type = cmp_ilike;
} else if (compare_op[0] == 'n') {
- type = cmp_notlike;
+ if (strcmp(compare_op, "not_like")) {
+ type = cmp_notlike;
+ } else {
+ type = cmp_notilike;
+ }
}
return type;
}
@@ -1843,7 +1849,9 @@
exp_destroy(rs2);
return NULL;
}
- if (!rs2 && type != cmp_like && type != cmp_notlike) {
+ if (!rs2 && type != cmp_like && type != cmp_notlike &&
+ type != cmp_ilike && type != cmp_notilike)
+ {
if (ls->card < rs->card) {
sql_exp *swap = ls;
@@ -1856,9 +1864,9 @@
type = (int)swap_compare((comp_type)type);
}
- e = exp_compare( ls, rs, type );
+ e = exp_compare(ls, rs, type);
} else {
- e = exp_compare2( ls, rs, rs2, type );
+ e = exp_compare2(ls, rs, rs2, type);
}
if (anti)
set_anti(e);
@@ -1888,7 +1896,8 @@
}
static sql_rel *
-rel_compare_exp(mvc *sql, sql_rel *rel, sql_exp *ls, sql_exp *rs, char
*compare_op, sql_exp *esc, int reduce )
+rel_compare_exp(mvc *sql, sql_rel *rel, sql_exp *ls, sql_exp *rs,
+ char *compare_op, sql_exp *esc, int reduce)
{
comp_type type = cmp_equal;
@@ -1898,10 +1907,6 @@
if (!rel || !reduce) {
sql_exp *e;
- if (compare_op[0] == 'l')
- compare_op = "like";
- if (compare_op[0] == 'n')
- compare_op = "not_like";
if (rel_convert_types(sql, &ls, &rs, 1, type_equal) < 0) {
exp_destroy(ls);
exp_destroy(rs);
@@ -1929,7 +1934,8 @@
}
static sql_rel *
-rel_compare(mvc *sql, sql_rel *rel, symbol *lo, symbol *ro, char *compare_op,
int f, exp_kind k )
+rel_compare(mvc *sql, sql_rel *rel, symbol *lo, symbol *ro,
+ char *compare_op, int f, exp_kind k)
{
sql_exp *rs = 0, *ls;
exp_kind ek = {type_value, card_column, FALSE};
@@ -2240,6 +2246,7 @@
{
symbol *lo = sc->data.lval->h->data.sym;
symbol *ro = sc->data.lval->h->next->data.sym;
+ int insensitive = sc->data.lval->h->next->next->data.i_val;
sql_subtype *st = sql_bind_localtype("str");
sql_exp *le = rel_value_exp(sql, rel, lo, f, ek);
sql_exp *re, *ee = NULL;
@@ -2249,7 +2256,8 @@
if (!exp_subtype(le)) {
exp_destroy(le);
- return sql_error(sql, 02, "No parameter allowed on left
hand of LIKE statement");
+ return sql_error(sql, 02, "SELECT: parameter not
allowed on "
+ "left hand side of LIKE operator");
}
lo = ro->data.lval->h->data.sym;
@@ -2262,10 +2270,10 @@
if (rel_set_type_param(sql, st, re) == -1) {
exp_destroy(le);
exp_destroy(re);
- return sql_error(sql, 02, "wrong type used with
LIKE statement, should be string");
+ return sql_error(sql, 02, "LIKE: wrong type,
should be string");
}
} else if ((re = rel_check_type(sql, st, re, type_equal)) ==
NULL) {
- return sql_error(sql, 02, "Wrong type used with LIKE
statement, should be string");
+ return sql_error(sql, 02, "LIKE: wrong type, should be
string");
}
/* Do we need to escape ? */
if (dlist_length(ro->data.lval) == 2) {
@@ -2273,13 +2281,15 @@
ee = exp_atom(atom_string(st, _strdup(escape), 1));
}
if (sc->token == SQL_LIKE) {
+ char *like = insensitive ? "ilike" : "like";
if (ee)
- return rel_nop_(sql, le, re, ee, NULL, NULL,
"like", 0);
- return rel_binop_(sql, le, re, NULL, "like", 0);
+ return rel_nop_(sql, le, re, ee, NULL, NULL,
like, 0);
+ return rel_binop_(sql, le, re, NULL, like, 0);
} else {
+ char *notlike = insensitive ? "not_ilike" : "not_like";
if (ee)
- return rel_nop_(sql, le, re, ee, NULL, NULL,
"not_like", 0);
- return rel_binop_(sql, le, re, NULL, "not_like", 0);
+ return rel_nop_(sql, le, re, ee, NULL, NULL,
notlike, 0);
+ return rel_binop_(sql, le, re, NULL, notlike, 0);
}
}
case SQL_BETWEEN:
@@ -2681,6 +2691,7 @@
{
symbol *lo = sc->data.lval->h->data.sym;
symbol *ro = sc->data.lval->h->next->data.sym;
+ int insensitive = sc->data.lval->h->next->next->data.i_val;
sql_subtype *st = sql_bind_localtype("str");
sql_exp *le = rel_value_exp(sql, &rel, lo, f, ek);
sql_exp *re, *ee = NULL;
@@ -2690,7 +2701,8 @@
if (!exp_subtype(le)) {
exp_destroy(le);
- return sql_error(sql, 02, "SELECT: parameter not
allowed on left hand of LIKE operator");
+ return sql_error(sql, 02, "SELECT: parameter not
allowed on "
+ "left hand side of LIKE operator");
}
/* Do we need to escape ? */
@@ -2717,8 +2729,10 @@
return sql_error(sql, 02, "LIKE: wrong type, should be
string");
}
if (sc->token == SQL_LIKE)
- return rel_compare_exp(sql, rel, le, re, "l", ee, TRUE);
- return rel_compare_exp(sql, rel, le, re, "n", ee, TRUE);
+ return rel_compare_exp(sql, rel, le, re,
+ (insensitive ? "ilike" : "like"), ee,
TRUE);
+ return rel_compare_exp(sql, rel, le, re,
+ (insensitive ? "not_ilike" : "not_like"), ee,
TRUE);
}
case SQL_BETWEEN:
case SQL_NOT_BETWEEN:
U sql_scan.mx
Index: sql_scan.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/sql_scan.mx,v
retrieving revision 1.150
retrieving revision 1.151
diff -u -d -r1.150 -r1.151
--- sql_scan.mx 25 Jul 2009 22:08:38 -0000 1.150
+++ sql_scan.mx 1 Aug 2009 14:47:46 -0000 1.151
@@ -174,6 +174,7 @@
keywords_insert("GLOBAL", GLOBAL);
keywords_insert("GROUP", GROUP);
keywords_insert("HAVING", HAVING);
+ keywords_insert("ILIKE", ILIKE);
keywords_insert("IN", sqlIN);
keywords_insert("INNER", INNER);
keywords_insert("INTO", INTO);
U sql_parser.mx
Index: sql_parser.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/sql_parser.mx,v
retrieving revision 1.311
retrieving revision 1.312
diff -u -d -r1.311 -r1.312
--- sql_parser.mx 25 Jul 2009 22:08:38 -0000 1.311
+++ sql_parser.mx 1 Aug 2009 14:47:45 -0000 1.312
@@ -641,7 +641,7 @@
%token UNCOMMITTED COMMITTED sqlREPEATABLE SERIALIZABLE DIAGNOSTICS sqlSIZE
%token <sval> ASYMMETRIC SYMMETRIC ORDER BY
-%token <sval> LIKE BETWEEN
+%token <sval> LIKE ILIKE BETWEEN
%token <operation> sqlIN EXISTS ESCAPE HAVING GROUP sqlNULL
%token <operation> FROM FOR MATCH
@@ -1769,7 +1769,7 @@
;
like_table:
- LIKE qname { $$ = _symbol_create_list(SQL_LIKE, $2 ); }
+ LIKE qname { $$ = _symbol_create_list( SQL_LIKE, $2 ); }
;
view_def:
@@ -3165,12 +3165,26 @@
{ dlist *l = L();
append_symbol(l, $1);
append_symbol(l, $4);
- $$ = _symbol_create_list(SQL_NOT_LIKE, l ); }
+ append_int(l, FALSE); /* case insensitive */
+ $$ = _symbol_create_list( SQL_NOT_LIKE, l ); }
+ | pred_exp NOT ILIKE like_exp
+ { dlist *l = L();
+ append_symbol(l, $1);
+ append_symbol(l, $4);
+ append_int(l, TRUE); /* case insensitive */
+ $$ = _symbol_create_list( SQL_NOT_LIKE, l ); }
| pred_exp LIKE like_exp
{ dlist *l = L();
append_symbol(l, $1);
append_symbol(l, $3);
- $$ = _symbol_create_list(SQL_LIKE, l ); }
+ append_int(l, FALSE); /* case insensitive */
+ $$ = _symbol_create_list( SQL_LIKE, l ); }
+ | pred_exp ILIKE like_exp
+ { dlist *l = L();
+ append_symbol(l, $1);
+ append_symbol(l, $3);
+ append_int(l, TRUE); /* case insensitive */
+ $$ = _symbol_create_list( SQL_LIKE, l ); }
;
like_exp:
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
Monetdb-sql-checkins mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-sql-checkins
