Forum OpenACS Development: Drop parties_email_un unique constraint

I'd like to propose in a TIP the dropping of the email unique constraint.

Why ?

It was a bad idea in the first place. E-mails are not unique, regardless how much we would like this to be. Take "mailto:info@bmw.org";. A couple of people would respond to this email. And would use this email when communicating with others (irrespectable of their actual email).

Where did it come from?

Originally we were supposed to log into the system using our e-mail. For this the e-mail needed to be unique. But it doesn't need to be unique anymore. We can login with a user name that just has to be unique on our site. Furthermore, this will allow us to have a real CRM system, which takes into account that e-mails can be used multiple times (we can't at the moment because storing the email in an Attribute would work, but then all the benefits of having an e-mail associated with a party are gone...).

What is the drawback ?

You would have to drop the option to register via e-mail. As the username is set to the email address anyway by default, this should be a no issue on upgrades and definitely on new sites. But maybe I'm wrong.

Does cognovis need it?

Not really. We just drop the parties_email_un constraint in all out custom packages, but I refrain from dropping it in contacts where it should be as I'm not entirely sure if this would require a TIP as well (sneaking in core changes through the backdoor).

And the last answer is the reason why I did not go for a full fledged TIP. I would like to get some feedback first before proposing it as a change for 5.3.

What does the community think about changing this fundamental part of the core?

Collapse
Posted by Jon Griffin on
A long, I mean long, while back I proposed the same thing. It was never dealt with, I even had patches.

You are correct, the dependence on email is terrible and should be killed.

parties may not even have an email.

Collapse
Posted by Matthew Coupe on
Sorry to revive such an old thread but how would I go about making email addresses un-unique in an OpenACS 5.3.2 implementation?

Just drop: "parties_email_un" UNIQUE, btree (email)?

I'm having lots of trouble registering bulk accounts where a generic email address is supplied.

Thanks for any help!

Collapse
Posted by Matthew Coupe on
ok, I'm going to take the plunge with this. I've grepped it and found where I need to drop it from. Let's hope it doesn't muck everything up for me!
Collapse
Posted by Dave Bauer on
You'll need to switch to using usernames for login.

I am curious what else it might affect. Be sure to let us know.

Collapse
Posted by Matthew Coupe on
Yup, done that.

Also requires a change in /acs-authentication/tcl/authentication-procs.tcl

to stop this:
set element_messages(email) [_ acs-subsite.Have_user_mail]
from bombing the user creation process.

I did a grep for all uses of acs_user::get and there are quite a few to say the least, I think potential problems will occur if any code uses acs_user::get to grab a user from their email address.

Collapse
Posted by Matthew Coupe on
OK.

I think making this change has caused a problem with either notifications and/or bulk mail.

I'm having trouble with bulk mails which are re-sending repeatedly, has anyone else had this problem before?

The notification error I'm getting is where I believe it is attempting to lookup the user by email address for some reason. The error information is below. Does anyone know which part of the code is the root of this evil?

Cheers any help.

[30/Jun/2009:00:00:02][20375.2573204400][-sched:27-] Error: Transaction aborted:
Database operation "0or1row" failed (exception NSINT, "Query returned more than
one row.")

SQL:

select user_id, first_names || ' ' || last_name as user_name
from cc_users
where email = 'mailto:myemail@email.com';

Database operation "0or1row" failed (exception NSINT, "Query returned more than
one row.")

SQL:

select user_id, first_names || ' ' || last_name as user_name
from cc_users
where email = 'mailto:myemail@email.com';

while executing
"ns_pg_bind 0or1row nsdb0 {

select user_id, first_names || ' ' || last_name as user_name
from cc_users
where email = :email

..."
("uplevel" body line 1)
invoked from within
"uplevel $ulevel [list ns_pg_bind $type $db $sql]"
("postgresql" arm line 2)
invoked from within
"switch $driverkey {
oracle {
return [uplevel $ulevel [list ns_ora $type $db $sql] $args]
}
..."
invoked from within
"db_exec 0or1row $db $full_statement_name $sql"
invoked from within
"set selection [db_exec 0or1row $db $full_statement_name $sql]"
("uplevel" body line 2)
invoked from within
"uplevel 1 $code_block "
invoked from within
"db_with_handle -dbn $dbn db {
set selection [db_exec 0or1row $db $full_statement_name $sql]
}"
(procedure "db_0or1row" line 50)
invoked from within
"db_0or1row get_user_name_and_id """
(procedure "get_address_array" line 25)
invoked from within
"get_address_array -addresses [string map {\n "" \r ""} $to_addr]"
(procedure "acs_mail_lite::send" line 10)
invoked from within
"acs_mail_lite::send -to_addr $email -from_addr $from_email -subject $subject
-body $content -extraheaders $extra_headers"
(procedure "notification::email::send" line 46)
invoked from within
"notification::email::send $from_user_id $to_user_id $reply_object_id $notificat
ion_type_id $subject $content_text $content_html"
(procedure "AcsSc.notificationdeliverymethod.send.notification_email" line 1
)
invoked from within
"AcsSc.notificationdeliverymethod.send.notification_email {} 945684 {} 692 {[The
NCC Education Campus - Batched Notification]} {Contents:/nFile Storage..."
("uplevel" body line 1)
invoked from within
"uplevel $func_and_args"
(procedure "apply" line 3)
invoked from within
"apply $proc_name $arguments"
(procedure "acs_sc_call" line 6)
invoked from within
"acs_sc_call NotificationDeliveryMethod Send $args $impl_key"
(procedure "notification::delivery::send" line 16)
invoked from within
"notification::delivery::send -to_user_id $prev_user_id -notification_type_id
$prev_type_id -subject "[_ notifications.lt_system_name_-_Batched]" -..."
("uplevel" body line 5)
invoked from within
"uplevel 1 $transaction_code "
(procedure "db_transaction" line 39)
invoked from within
"db_transaction {
ns_log Debug "NOTIF-BATCHED: sending content"
# System name is used in the su..."
(procedure "notification::sweep::sweep_notifications" line 45)
invoked from within
"notification::sweep::sweep_notifications -interval_id 633 -batched_p 1"
("eval" body line 1)
invoked from within
"eval [concat [list $proc] $args]"
(procedure "ad_run_scheduled_proc" line 46)
invoked from within
"ad_run_scheduled_proc {t f {0 0} notification::sweep::sweep_notifications {-int
erval_id 633 -batched_p 1} 1246016336 0 f}"

Collapse
Posted by Dave Bauer on
It looks like this is not called in the latest acs-mail-lite, but the proc remains.

You might want to look into that and see if you can drop the requirement to lookup the user_id from the destination email.