Details
-
Type:
Sub-task
-
Status: Done/Fixed
-
Priority:
Trivial
-
Resolution: Fixed/Completed
-
Affects Version/s: 4.2.0
-
Fix Version/s: 4.2.0
-
Component/s: None
-
Labels:None
Description
We would need a table to add different sms providers and their credentials.
Assumption: there could be multiple numbers / accounts / api-types from same provider
Adding a new option group for sms provider name (to store provider names eg : Clickatell )
Table 1: civicrm_sms_provider with following columns:
name (provider's internal name - FK to value of this new option group) - varchar(64)
title (provider's name visible to user) - varchar(64)
username - varchar(255)
password - varchar(255)
api_type (http / xml / smtp .. FK to value of this new option group)
api_url (example - http://api.clickatell.com/http/sendmsg, http://api.clickatell.com/xml/xml, sms@messaging.clickatell.com)
api_params (example:
HTTP:
user=testuser
password=<Clickatell Account Password>
api_id=3364258
to=<Mobile Number>
text=<SMS Message>
XML:
<api_id>3364259</api_id>
<user>testuser</user>
<password>PASSWORD</password>
<to>Mobile Number</to>
<text>SMS Message</text>
SMTP:
user:testuser
password:<Clickatell Account Password>
api_id:3364266
text:<SMS Message>
to:<Mobile Number 1>
to:<Mobile Number 2> ..
)
notify (number to notify) - varchar(32)
is_default - tinyint(4)
is_active - tinyint(4)
Text in api_params should be allowed to be replaced also via hooks.
For now we would just update the activity for status returned from callback. So we won't add any additional table. Hooks should allow using additional tables and storing / keeping track of additional values.
Add a new column 'sms_provider_id' to existing civicrm_mailing table which would be FK to civicrm_sms_provider.id.
Add a new column 'phone_id' in civicrm_mailing_recipients and civicrm_mailing_event_queue table. This will help cron to be sms compatible.
Make civicrm_mailing_recipients.email_id and civicrm_mailing_event_queue.email_id accept NULL values, since there could be situations where contacts only have phone and no email.
Attachments
Activity
A couple thoughts...
1. Clickatell also requires an API key in addition to the username and password... so in Table 1 should there either be an additional "freeform" column or give up on splitting it up and have a credentials column that "blobs" it up and let each provider parse out the needed info. But that said...
2. With regards to Tim's point... It might help to "slice" the tables differently?
civicrm_sms_provider
id,name, title, api_type
1,"Clickatell","Clickatell HTTP","http"
2,"Clickatell","Clickatell SMTP","smtp"
3,"RapidSMS","RapidSMS","xml"
civicrm_sms_provider_instance
id,sms_provider(fk to above),name,phonenumber,username,password,other_credentials,connection_string
1,1,"Clickatell DC","202-123-4567","myusername","s3cr3t","api:1234567","http://api.clickatell.com"
2,1,"Clickatell Pittsburgh,"412-987-6543,"mypghuser","als0s3cr3t","api:987654","http://api.clickatell.com"
3,2,"Clickatell Pgh (SMTP)","412-987-6543,"mypghuser","als0s3cr3t","api:987654","smtp:sms@sms.clickatell.com"
Note that this moves all the connection information into one table leaving only general information the first table. This should allow multiple instances of a single provider.
At that point... it might just be easier to remove Table 1 entirely and move the list of provider names into an option look up.
The implementor should have flexibility in making different combinations of driver classes, API credentials, and phone numbers. A few example scenarios:
a. One driver used with one user account – which has multiple phone numbers.
b. One driver used with multiple user accounts – each with one phone number.
c. Multiple drivers used with different user accounts and different phone numbers.
I might not be reading correctly, but in this schema, I don't see how one would create records in which one driver implementation could be used with multiple user accounts.