Download & Extend
Email Registration » Issues

Rewrite name unique check so it is cross-database compatible

Posted by neilnz on April 1, 2009 at 9:33pm
Project:Email Registration
Version:6.x-1.x-dev
Component:Code
Category:feature request
Priority:normal
Assigned:Unassigned
Status:reviewed & tested by the community

Issue Summary

Hi,

I wanted to use this module on Postgres, but your SQL query to fetch the next sequence number is MySQL-specific.

Patch attached for Postgres support.

Thanks

AttachmentSize
email_registration.module.patch1.36 KB
Login or register to post comments

Comments

#1

Posted by greggles on June 9, 2009 at 12:42am
Title:PostgreSQL support (patch)» PostgreSQL support for email_registration (patch)
Status:active» needs review

I don't have a test bed for this, but here's a better status ;)

#2

Posted by greggles on June 10, 2009 at 12:15pm
Title:PostgreSQL support for email_registration (patch)» Rewrite name unique check so it is cross-database compatible
Status:needs review» needs work

This is not a real solution to the problem because it uses a switch. We should use standard sql.

As far as I can tell, this code is trying to find 1) if the name is unique, 2) if not, what is the highest "index" for the name. While it may be slightly slower, we should be able to do more of this in php and make it cross-database compatible.

#3

Posted by dspring0021 on September 23, 2010 at 10:06pm

Is there a solution to this. I'm running Drupal 6.19 and Email Registration 6.x-1.3 on PostgreSQL. I'm getting the errors below when registering a username of an email address that already exists in the db--as in the scenario outlined in this issue. The patch above failed. So, then, I tried to manually make the changes, but the code seems to be always using the mysql version of the query. I'm kind of stuck. Any advice on resolving this for pgsql would be much appreciated.

warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "REGEXP" LINE 1: ...UBSTRING_INDEX(name,'_',-1) FROM users WHERE name REGEXP '^d... ^ in /home/dspring/public_html/cert2/includes/database.pgsql.inc on line 139.
user warning: query: SELECT SUBSTRING_INDEX(name,'_',-1) FROM users WHERE name REGEXP '^dspring_[0-9]+$' ORDER BY CAST(SUBSTRING_INDEX(name,'_',-1) AS UNSIGNED) DESC LIMIT 1 in /home/dspring/public_html/cert2/sites/all/modules/email_registration/email_registration.module on line 34.
warning: pg_query() [function.pg-query]: Query failed: ERROR: duplicate key value violates unique constraint "users_name_key" in /home/dspring/public_html/cert2/includes/database.pgsql.inc on line 139.
user warning: query: UPDATE users SET name = 'dspring_1' WHERE uid = '307' in /home/dspring/public_html/cert2/sites/all/modules/email_registration/email_registration.module on line 45.

#4

Posted by dspring0021 on September 24, 2010 at 1:17pm

***UPDATE***

I lied in my post above. The patch does work for me (user error before). It will suffice as a workaround for now. Thank you!

#5

Posted by dwightaspinwall on October 27, 2010 at 6:48pm

Subscribing. An ANSI sql solution would be nice. BTW, thanks for a great module - we use it on hci.org - 200,000 member site.

#6

Posted by dwightaspinwall on October 28, 2010 at 2:34pm
Status:needs work» needs review

Here's a patch for review. Should be cross-db compatible, eliminating the need for db-specific solutions.

AttachmentSize
email_registration_ansi_sql.patch 2.9 KB

#7

Posted by dwightaspinwall on October 28, 2010 at 2:34pm
Version:6.x-1.1» 6.x-1.x-dev

#8

Posted by thePanz on November 26, 2010 at 10:13am
Status:needs review» reviewed & tested by the community

Tested, working fine!

#9

Posted by YK85 on November 27, 2010 at 4:53pm

fyi - #657472: Add setting to allow users to login with email address or username depends on this patch

#10

Posted by thePanz on November 27, 2010 at 4:59pm

@yaz085: to clarify your comment: the #657472-23: Add setting to allow users to login with email address or username patch includes this patch (it's not a dependency) ;) Maybe someone could work to split them (and then commit both) :)