Provide a wrapper for cross-db and cross-field-type concatenation (PosgreSQL)

I'm not sure exactly what change you're asking for - could you provide a patch http://drupal.org/patch ?

This seems fine to me. Which database do you use?

mysql> select CONCAT('node/', CAST(nid AS CHAR)) from node limit 100;
+------------------------------------+
| CONCAT('node/', CAST(nid AS CHAR)) |
+------------------------------------+
| node/25                            | 
| node/26                            | 
| node/27                            | 
| node/28                            | 
| node/29                            | 
| node/30                            | 
+------------------------------------+

I assume you use PostgreSQL and I'm hesitant to fix this since this has basically no reviews and the original code was reviewed pretty heavily in #212327: cast the concatenated join value to be able to use the url_alias index.

Marked #328178: Postgresql 7.4 Bulk Update as a duplicate of this issue.

find attached a fix. Have fixed the error in the patch from #373841: Use update action for all existing aliases, not just the current existing alias

Tagging all the bulk alias issues for #713238: RFC: Pathauto Bulk module.

I can verify this is an issue for PostgreSQL db's -- we fixed our install with the attached patch, but the patch in #5 looks like it may be more db agnostic.

I'm RTBC'ing #5 on the bases that this problem is a postgres problem and that the changes fix postgres and don't break mysql.

Does this patch need to apply to the pathauto_taxonomy.inc and pathauto_user.inc? They seem to have the same things going on:

  $query = "SELECT tid, vid, name, src, dst FROM {term_data} LEFT JOIN {url_alias} ON CONCAT('taxonomy/term/', CAST(tid AS CHAR)) = src WHERE src IS NULL AND vid <> %d ". $vid_where;
  $query = "SELECT tid, vid, name, src, dst FROM {term_data} LEFT JOIN {url_alias} ON CONCAT('forum/', CAST(tid AS CHAR)) = src WHERE vid = %d AND src IS NULL";
  $query = "SELECT uid, name, src, dst FROM {users} LEFT JOIN {url_alias} ON CONCAT('user/', CAST(uid AS CHAR)) = src WHERE uid > 0 AND src IS NULL";
  $query = "SELECT uid, name, src, dst FROM {users} LEFT JOIN {url_alias} ON CONCAT('blog/', CAST(uid AS CHAR)) = src WHERE uid > 0 AND src IS NULL";
  $query = "SELECT uid, name, src, dst FROM {users} LEFT JOIN {url_alias} ON CONCAT(CONCAT('user/', CAST(uid AS CHAR)), '/track') = src WHERE uid > 0 AND src IS NULL";

If so, we'll need an updated patch.

yes.

#11 has worked fine for me on Postgres 8.3. I have only used it for creating url aliases for nodes. Thanks for the patch.

Casting to char(99) should work in both databases. Patch against HEAD attached.

Rebuilt the last patch against HEAD. Also changed CHAR(99) to CHAR(20) although I don't know if it will give us any significant speedup.

tuffnatty,

In PostgreSQL, the fastest is TEXT, not CHAR(<whatnot>);. It is specified in the docs, check out the first Tip on that page:

http://www.postgresql.org/docs/8.0/interactive/datatype-character.html

It would be good to always use TEXT and not bother with CHAR() at all. It has generated many problems in Core (aggregator URL too long and watchdog strings too long for two that I recall.)

Thank you.
Alexis

How about a function that will return the proper concatenation depending on the database type being used? This seems like a much better and future-proof method.

I've tested #17 with success using MySQL 5.1.37 and PostgreSQL 8.4.4. Still needs testing with lower versions of PostgreSQL and also with SQLite in D7.

Tested and it works great with sqlite with Drupal 7. Still could use someone testing on PostgreSQL below 8.3. Please report back your results/success.

Since I've tested this on all possible database types for D7, I'm committing #18 to this branch. The following patch still needs testing as per #19.
http://drupal.org/cvs?commit=377544

That doesn't look correct. On MS SQL, '+' is both the concatenation operator and the addition operator, and doesn't do automatic casting. You need an explicit cast here if you want to be able to use non-text columns in the concat.

That's one of the rational behind *not* using || in Drupal 7, see #809698: Refrain from using the || operator for concatenation.

Let's get back to the old code.

Our own PostgreSQL's CONCAT() function has been fixed, and now properly accept any non array type as arguments. This abstraction is thus completely unecessary, and actually breaks Drupal on SQL Server (because using || *requires* a cast, while our CONCAT() function doesn't).

Agreed, especially since we removed support for feed aliases, so now we never need to concat more than two strings.

So we can remove this in D7, but we still have to do something about this in the D6 versions, as this is affecting critical functionality.

Committed #23 to 7.x-1.x: http://drupalcode.org/project/pathauto.git/commit/45784f0

Here's the patch for 6.x-2.x.

In #26 we removed the support for MSSQL for 6.x since I don't think I can support it. I tested it with both MySQL and PostgreSQL.

Committed to 6.x-2.x: http://drupalcode.org/project/pathauto.git/commit/4aff481

Committed to 6.x-1.x as well as fixed a small bug in the committed code with the forum aliases in 6.x-2.x
http://drupalcode.org/project/pathauto.git/commit/7af542f
http://drupalcode.org/project/pathauto.git/commit/084dbd4