SQL queries containing 'range' keyword.

[runelk]

When searching for a word in Glossa after specifying publishing dates (from/to) and/or author year of birth (from/to), Glossa responds with the following error:

Software error: DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'range '1990' OR text.pubdate range '2000') AND (text.pubdate >= '1990' AND text.' at line 1 at lib//Glossa_local.pm line 431.

It seems that 'range' is used inappropriately during query construction/execution.

Tested on the LBK corpus (?corpus=bokmal as url parameter).

[andrely]

Look at the end of the query.

If you look at the code in question it's fairly obvious that it doesn't work.

Is this a new error? Maybe something has been cut out by accident.

[runelk]

No, it's an old one, number ranges in general haven't worked (for LBK at least) for a long time. I'll probably see to it tomorrow, just thought I'd add it as an issue.

On 2013-01-31 17:53, andrely wrote:

If you look at the code in question it's fairly obvious that it doesn't work.

Is this a new error?

On Jan 31, 2013, at 4:49 PM, runelk wrote:

When searching for a word in Glossa after specifying publishing dates (from/to) and/or author year of birth (from/to), Glossa responds with the following error:

Software error: DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'range '1990' OR text.pubdate range '2000') AND (text.pubdate >= '1990' AND text.' at line 1 at lib//Glossa_local.pm line 431.

It seems that 'range' is used inappropriately during query construction/execution.

Tested on the LBK corpus (?corpus=bokmal as url parameter).

— Reply to this email directly or view it on GitHub.

-- Reply to this email directly or view it on GitHub [1].

Links:

[1] https://github.com/textlab/glossa-foni-cgibin/issues/6#issuecomment-12952254

[runelk]

Offending code (starting at line 306 in lib/Glossa_local.pm):

    # MODE: like / not like / check
    foreach my $val (@$vals) {
        $subcorpus_string .= "\t" . $val;

        if ($mode eq 'check'){ $mode = '=';  }

        my $val_restr = "$sql $mode '$val'";

        if($mode eq 'NOT LIKE'){  push @restr_neg, $val_restr; }
        else { push @restr_pos, $val_restr; }

        $from_string{$tablename}=1;
    }

---

Range values from the POST parameters are handled by the previous if statement (beginning at line 293: '# MODE: range'), but bleed into the foreach statement starting at line 306.

[runelk]

Seems to be a combination of 2 bugs. CGI params regarding metadata entering query_dev.cgi are prepended with the the corpus name in order to point to the right SQL table (e.g. meta_values_kategori::BOKMALtext.category). This does not happen for metadata specified as ranges (e.g. meta_values_kategori::text.pubdate).

[runelk]

This issue also affects the run corpus (by specifying a range in "publication date").

[runelk]

Main culprit seems to be in meta.js, line 134 and onwards:

function writeWidgetFromTo(widgetId, widgetName, defaultPosition, note) {

// temporary fix ... that doesn't work

 widgetContent['pubdate']=new Array();
 widgetContent['pubdate'].tablename="text";
 widgetContent['pubdate'].colname="pubdate";

 widgetContent['age']= new Array();
 widgetContent['age'].tablename="author";
 widgetContent['age'].colname="age";

 widgetContent['time']= new Array();
 widgetContent['time'].tablename="text";
 widgetContent['time'].colname="time";

.... }

[runelk]

Culprits everywhere. Seems to work now, but each individual corpus with range metadata fields need configuration for these fields, which has probably never been done due to these never working correctly in the first place. A short description for configuring a corpus for range metadata fields have been added at https://github.com/textlab/glossa-foni-cgibin/wiki/Fixing-range-metadata-fields.