526396 - Replace hard coded usage of database datetime, substring and MOD functions with DB Module specific function calls

Status: RESOLVED WONTFIX

(Bugzilla :: Database, enhancement, P5)

Description

[Michael Thomas (Mockodin)]

The following calls do not exists in MSSQL:
'LOCALTIMESTAMP()', 'NOW()', 'CURRENT_DATE()'

Oracle similarly does not recognize 'CURRENT_DATE()'
but does recognize 'CURRENT_DATE'

For Oracle and MSSQL to correct these issues we are currently processing sql calls through adjust_statement() calls for each and every sql call checking for their existence and correction where detected. While it may not be possible to eliminate the usage of adjust_statement() at this time, less reliance should be worked toward,

Instead we should be calling these as function calls.

examples:

$dbh->do("INSERT INTO blah (created, userid) values (?, ?)", undef,
            $dbh->localtimestamp, $usr->id);

$dbh->do("INSERT INTO blah (created, userid) values (?, ?)", undef,
            $dbh->now, $usr->id);

this will provide the opportunity to support math operations that might otherwise not be supported by a given database.

Example:
'SELECT NOW() + INTERVAL 30 DAY'
-- This will not work under MSSQL.

Something like the above whould change to
$dbh->INTERVAL($column||undef, 30, 'DAY');

if undef use NOW() else the column specified.

This is not a requirement but would certainly simplify support for varying databases platforms that might not yet be supported, including of course the current mssql project.

Comment 1

[Michael Thomas (Mockodin)]

Attachment: v1

v1 is at present untested and was a general search and replace
I have also included SUBSTRING in the list of functions. Oracle used SUBSTR.

Note I have made the function names capitalized: Somewhat to mirror usage in the databases and otherwise to make them obvious in code. There is no real reason that they must be however.

Comment 2

[Michael Thomas (Mockodin)]

Attachment: v2

A few corrections, several places Bugzilla->dbh needed to be called directly instead of $dbh. Few other minor corrections.

Thus far working against mssql, but not extensively tested.

Comment 3

[Michael Thomas (Mockodin)]

Hmm possibility... probably... these should be constants in the db rather than functions. Except for LOCALTIMESTAMP where precision can be supplied.

Comment 4

[Michael Thomas (Mockodin)]

Removing [MS-SQL] tag as this effects Oracle as well. Well all db platforms but is for MSSQL and Oracle.

Comment 5

[Michael Thomas (Mockodin)]

Found another one in collectstats.pl

MOD syntax chagnes to N % M in MSSQL

Will post another patch later for collectstats.pl and db modules.

Comment 6

[Max Kanat-Alexander]

Comment on attachment 410299 [details] [diff] [review]
v2

No, NOW() is not going to become a sql_* function. LOCALTIMESTAMP is an ANSI-standard item. We can switch from NOW() to LOCALTIMESTAMP if it's a concern.

Comment 7

[Max Kanat-Alexander]

In any case, you've made this bug change like three or four different things, and it needs to focus on just one of those things in order to be reviewed and accepted.

Comment 8

[Michael Thomas (Mockodin)]

(In reply to comment #6)
> (From update of attachment 410299 [details] [diff] [review])
> No, NOW() is not going to become a sql_* function. LOCALTIMESTAMP is an
> ANSI-standard item. We can switch from NOW() to LOCALTIMESTAMP if it's a
> concern.

Neither are useful for MSSQL. Standardizing on one function would however be best if forced to use the adjust sql function, which I can't completely escape from anyhow as sql_limit() suffers from the same issues. I do not understand the issue with a function call however. It's better than the adjust sql function using regex replaces that oracle is using now and that mssql will be forced to do as well.

I'm addressing them all at once as it is the same fundamental issue for each.

Comment 9

[Frédéric Buclin]

I agree with mkanat that standard items do not need to be converted into SQL methods. And we already have fixes in place for e.g. Oracle.

Oracle and MSSQL should follow standards instead of forcing us to write custom code to support them.