[Gmod-tripal-devel] Tripal API

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

[Gmod-tripal-devel] Tripal API

Kucheran, Lacey Sanderson
I'm contemplating some changes to the select api and was wanting feedback. 
1. Case insensitive search:
Want to search for Pintium but it's in our database as PINTUIM so there's no match.

The best approach seems to be SELECT * FROM stock WHERE lower(name) = lower('Pintium')
The other suggested approach was ~* which also matched PINTIUM SELECTION or ILIKE which appeared to work but I'm uncertain apout the support between different postgresql versions...

Proposed change to api:
Add $case_insensitive_columns = array() to function arguements. This way the default is no columns are case insensitive.
if (in_array($field, $case_insensitive_columns)) {
  $sql .= "lower($field) = lower('%s') AND ";
  $args[] = $value;
}  else {
  $sql .= "$field = '%s' AND ';
  $args[] = $value;
}

2. Searching using postgresql regular expression handling (ie: ~ method)
Want to search for Sm2010-P and get Sm2010-P1, Sm2010-P2 and sm2010-p6677

The approach SELECT * FROM stockprop WHERE value ~* 'Sm2010-P';

Proposed change to api:
Add $regex_columns = array() to function arguements. This way the default is exact match for all columns.
Then add the following 419-420:
$operator = '=';
if (in_array($field, $regex_columns)) {
  $operator = '~*';
}
$sql .= "$field $operator '%s' AND ';
$args[] = $value;

Note: If we choose to go with #1 above:
Then instead add combined from 419-420:
$operator = '=';
if (in_array($field, $regex_columns)) {
  $operator = '~*';
}
if (in_array($field, $case_insensitive_columns)) {
  $sql .= "lower($field) $operator lower('%s') AND ";
  $args[] = $value;
}  else {
  $sql .= "$field $operator '%s' AND ';
  $args[] = $value;
}

NOTE: ~* is itself a case-insensitive search and thus doesn't need the lower() but I though simply changing the operator would allow for easier changing of operator later (ie: functionality is already in place for LIKE

~Lacey Sanderson
------------------------------------------------------
Lacey-Anne Sanderson
Bioinformaticist
Pulse Crop Breeding and Genetics
Phone: (306) 966-2430
Room 3D10 Agriculture
Department of Plant Sciences
University of Saskatchewan


------------------------------------------------------------------------------
Free Software Download: Index, Search & Analyze Logs and other IT data in
Real-Time with Splunk. Collect, index and harness all the fast moving IT data
generated by your applications, servers and devices whether physical, virtual
or in the cloud. Deliver compliance at lower cost and gain new business
insights. http://p.sf.net/sfu/splunk-dev2dev 
_______________________________________________
Gmod-tripal-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal-devel