Proposal to add quoteDateTime to JDatabaseDriver class

[Will Daniels]

Hi,

[I mentioned this a couple of weeks back on the general list and was asked to
raise it here for platform]

Would anybody oppose the addition of a quoteDateTime method to the database
driver class?

The issue is that not all RDBMS use string literals for date/time values. The
way things are done currently using getDateFormat (and JDate::toSql accordingly)
does not quote the result as a string literal, which means everything that calls
it will blindly quote the result afterwards. So this makes it impossible to use
any other escape notation. For example, ODBC likes timestamp values supplied
using the "brace escape" syntax:

{ ts 'YYYY-MM-DD hh:mm:ss' }
{ d 'YYYY-MM-DD' }
{ t 'hh:mm:ss' }

The SQL standard specifies a type prefix TIMESTAMP, DATE or TIME, e.g.

TIMESTAMP 'YYYY-MM-DD hh:mm:ss'

Microsoft Access (!?) would want:

#YYYY-MM-DD hh:mm:ss#

There may be other/better examples, but I think ODBC is the main one and is
where I came up against this issue.

Currently, if I were to override getDateTime to:

return '{ \\t\\s \'d.m.Y H:i:s\' }';

...the correct syntax is produced by PHP in DateTime::format, but everywhere in
Joomla! this just gets escaped and quoted as a string literal, and so it ends up
in queries as something like:

AND (foo OR a.publish_up <= '{ts \'24-03-2012 05:43:47\'}')

...instead of the required...

AND (foo OR a.publish_up <= {ts '24-03-2012 05:43:47'})

My only solution to this at present has been to resort to rewriting queries
using regex in the driver class, which is obviously far from ideal.

While I understand that all _mainstream_ RDBMS are quite happy to take a string
literal for timestamp values, it makes enough sense (to me) to add a
quoteDateTime in line with quoteName and quote, since this is more convenient
for consumers anyway to have the literal string-quoted ready for them wherever
it does need to be quoted.

My proposal is to add this new method alongside getDateFormat and getNullDate to
avoid breakage and to patch the Platform and CMS at least to use the new method
(passing PHP null into quoteDateTime instead of using getNullDate). However, I'm
unsure about the possibility of deprecating the existing methods because there
may yet be some use for an unquoted format in DDL statements, since those tend
to use a string literal format for specifying DATETIME column DEFAULT values,
because DDL is processed differently by the ODBC drivers than SQL.

Any thoughts? Objections? If I prepare a commit for this, would it likely be
accepted? And if so, what do I put in the @since tag, "TBD" or just the next
version serial?

Thanks,
-Will

[Andrew Eddie]

I think a "quoteDate" method and a "qd" alias would be fine.

Regards,
Andrew Eddie
http://learn.theartofjoomla.com - training videos for Joomla developers

[Rob Schley]

I was not aware of this problem but it seems like a reasonable solution. If you're going to do a pull request, it would sure be nice if you fixed up the existing quoted dates to use the new method. As far as the version number, I would suggest you put 12.2 as that is the more likely version for it to be merged into.

Cheers,

Rob

[84.le0n]

Let me know which pull request will be, to help you about PostgreSQL date quote.

Eng. Gabriele Pongelli.