CONCAT() is mysqlisch

[ads]

Hello,

this query is problematic:

Code: Select all

SELECT sum(visits) AS weekvisitors FROM serendipity_visitors_count WHERE CONCAT(year,month,day) >= '20100329' AND CONCAT(year,month,day) <= '20100404'

The CONCAT() function is a MySQL extension and will not work in all databases.

The correct solution would be the || operator, according to the SQL standard.
BUT: MySQL is an exception in this point, because the || operator is binary OR in MySQL and not string concatenation. See this post:
http://sql-info.de/mysql/gotchas.html#1_12

Big mess ...

Is there a way to work around this whole problem by modifying the query?

If that's not possible: Is it possible to create some functions during the installation process - but only if the database is not MySQL? Then i will provide a matching CONCAT() function.

[kleinerChemiker]

or simply use php to concat the strings

[ads]

That's not possible here, the query concats database columns, not values.

This raises the question, why there are three different columns for one single date ... maybe this can be reworked to solve the entire problem.

[kleinerChemiker]

ups, sorry. didn't read that carfully.

[garvinhicking]

Hi!

Hm, indeed that's problematic. The visitors plugin was something I never worked on, so it was better to have it for mysql than to not have it at all.

Forking the code to use "||" when the DB is postgresql seems to be the easiest way to fix it. What woud the cited query need to look like in postgresql? Then I can patch up the code (after easter...)

Thanks,
Garvin

[ads]

For all non-MySQL databases the code should look like:

Code: Select all

year || month || day

instead of:

Code: Select all

CONCAT(year, month, day)

For PostgreSQL it's even possible to add a functional index:

Code: Select all

CREATE INDEX serendipity_visitors_count_date ON serendipity_visitors_count ((year || month || day));

This should speedup the query, because the result of the operation can be found in the index - no need to scan the table for every request.