[Spce-user] Subscriber Alias Entries in Database

Fri Sep 5 06:46:46 EDT 2014

And if the intention is to modify those tables yourself, I can just warn
you to not do it and use the REST interface instead. Otherwise, I can
guarantee that horrible inconsistencies will occur. We try to cover that
with rigid testing, but even our code sometimes breaks in that regards.

Andreas

On 09/05/2014 12:44 PM, Andreas Granig wrote:
> Hi Kevin,
> 
> I'm still not sure what exactly the question is, but let me try :)
> 
> Both provisioning.voip_dbaliases and billing.voip_numbers are filled in
> parallel. The former shows a representation of what is currently
> assigned to subscribers (written via triggers to kamailio.dbalias), and
> the second is a general list of numbers ever used on the system. If it
> gets unassigned, the subscriber_id/reseller_id are reset. It's a
> preparation for a full number management in the system, to come in a
> future release.
> 
> Andreas
> 
> On 08/29/2014 09:52 PM, Kevin Masse wrote:
>> Greetings group:
>>
>> I was hoping that someone might have had some thought on the two
>> questions below or perhaps it got buried.
>>
>> \\ locate the 
>> ************* 2 questions below ****************
>> \\ below
>>
>> Thanks
>> Kevin
>>
>>
>>
>> -----Original Message-----
>> From: Kevin Masse 
>> Sent: Thursday, August 14, 2014 7:33 AM
>> To: Kevin Masse; 'Andreas Granig'; 'spce-user at lists.sipwise.com'
>> Subject: RE: [Spce-user] Subscriber Alias Entries in Database
>>
>> Hello Andraes, have you had a chance to review the two questions stated
>> below?
>>
>> Our dev team is seeking to close out this issue we are having.
>>
>> Thank you
>> Kevin
>>
>>
>>
>>
>> -----Original Message-----
>> From: Kevin Masse
>> Sent: Wednesday, August 06, 2014 7:55 AM
>> To: Kevin Masse; 'Andreas Granig'; 'spce-user at lists.sipwise.com'
>> Subject: RE: [Spce-user] Subscriber Alias Entries in Database
>>
>> Hello, I was wondering if there was a response to this that I missed.
>> There are two specific questions we are trying to answer.
>>
>>
>> Thank you
>> Kevin
>>
>>
>>
>> -----Original Message-----
>> From: Kevin Masse
>> Sent: Thursday, July 24, 2014 10:02 AM
>> To: Kevin Masse; Andreas Granig; spce-user at lists.sipwise.com
>> Subject: RE: [Spce-user] Subscriber Alias Entries in Database
>>
>> Andraes one additional scenario has come up based on our last thread:
>> I wanted to post to the group the question from our dev team:
>>
>> ****  Begin Comment ****
>> It is going to be a long post because I can't explain in a short
>> message. I'll provide some example to explain what I mean. 
>>
>> We have SIP named QBATRUNK.  If you open administrator page
>> (https://DOMAIN:1443/subscriber/detail?subscriber_id=395) you can see
>> there following info:
>>
>> Contract ID (405) - This is main ID which we store in (customer site -
>> not part of Sipwise it is our Database) . All operations like
>> lock/unlock SIP, add/remove DID using the ID in it request to API.
>>
>> Subscriber ID (395) - I can call it internal info, it is not used in any
>> request to Sipwise API but to make direct request to Sipwise DB I have
>> to use it.
>>
>> I routed 3384066 DID to the SIP to work with the example. 
>> According to previous instructions we use two databases to store
>> aliases. Now I'll show you request to retrieve the DID from Sipwise DB.
>>
>> First I use billing DB. Let's retrieve Subscriber ID (we do not know it
>> when call DB directly). I use voip_subscribers table which stores the
>> info.
>>
>> mysql> select * from voip_subscribers where contract_id = 405;
>> +-----+-------------+--------------------------------------+----------+-
>> ----------+--------+-------------------+-------------+
>> | id | contract_id | uuid | username | domain_id | status | 
>> | primary_number_id | external_id |
>> +-----+-------------+--------------------------------------+----------+-
>> ----------+--------+-------------------+-------------+
>> | 395 | 405 | 14a58c84-c83e-4673-9d10-361a1a6b990e | QBATRUNK | 1 | 
>> | active | NULL | NULL |
>> +-----+-------------+--------------------------------------+----------+-
>> ----------+--------+-------------------+-------------+
>>
>> OK you can see that I receive id 395. We need it.
>>
>>  
>>
>> Let's get DIDs info from voip_numbers, DB - 
>>
>> mysql> select * from voip_numbers where subscriber_id = 395;
>> +------+----+-----+---------+-------------+---------------+--------+----
>> ----+---------------------+
>> | id | cc | ac | sn | reseller_id | subscriber_id | status | ported | 
>> | list_timestamp |
>> +------+----+-----+---------+-------------+---------------+--------+----
>> ----+---------------------+
>> | 2119 | 1 | 910 | 3384066 | NULL | 395 | active | 1 | 2014-07-22 
>> | 18:32:16 |
>> +------+----+-----+---------+-------------+---------------+--------+----
>> ----+---------------------+
>>
>> Everything is OK we got it as expected. Lets' get DID info from second
>> DB (provisioning) I'll do it using DID number
>>
>> mysql> select * from voip_dbaliases where username like '%3384066%';
>> +------+-------------+-----------+---------------+
>> | id | username | domain_id | subscriber_id |
>> +------+-------------+-----------+---------------+
>> | 2821 | 19103384066 | 3 | 398 |
>>
>> As you can see subscriber_id in the table does not corresponds to the
>> subscriber_id which we retrieve from billing DB. That is the question
>> for me.
>>
>> I found that the subscriber_id is stored in voip_dbaliases table but the
>> ID does not corresponds to the subscriber_id from billing table.
>>
>>
>>
>>
>> ************* 2 questions below ****************
>>
>>
>>
>>
>>
>> Question 1
>> I do not understand how subscriber_id in billing DB (table voip_numbers)
>> related with subscriber_id in provisioning DB (table voip_dbaliases ).
>>
>> Question 2
>> Another question. I found that DIDs info is stored in voip_dbaliases
>> table (provisioning) but I did not told to creae the record by VoIP
>> specialist.
>>
>>
>> mysql> select * from voip_dbaliases where username like '%3384066%';
>> +------+-------------+-----------+---------------+
>> | id | username | domain_id | subscriber_id |
>> +------+-------------+-----------+---------------+
>> | 2821 | 19103384066 | 3 | 398 |
>> +------+-------------+-----------+---------------+
>>
>> Hopefully I explained my questions clear. If not please let me know
>>
>> Thanks,
>> *** End Comment from DEV ***
>>
>>
>>
>> In conclusion Andreas do you think there is a proper answer for the
>> above Question 1 and Question 2?
>> Thank you
>> Kevin 
>>
>>
>>
>>
>>
>> -----Original Message-----
>> From: spce-user-bounces at lists.sipwise.com
>> [mailto:spce-user-bounces at lists.sipwise.com] On Behalf Of Kevin Masse
>> Sent: Wednesday, July 16, 2014 8:56 AM
>> To: Andreas Granig; spce-user at lists.sipwise.com
>> Subject: Re: [Spce-user] Subscriber Alias Entries in Database
>>
>> Thank you Andreas, I appreciate the detailed response.  Works as
>> described.
>> Kevin
>>
>>
>>
>> -----Original Message-----
>> From: spce-user-bounces at lists.sipwise.com
>> [mailto:spce-user-bounces at lists.sipwise.com] On Behalf Of Andreas Granig
>> Sent: Wednesday, July 16, 2014 6:24 AM
>> To: spce-user at lists.sipwise.com
>> Subject: Re: [Spce-user] Subscriber Alias Entries in Database
>>
>> Hi,
>>
>> On 07/15/2014 03:05 PM, Kevin Masse wrote:
>>
>>> **  Current operation:   We use SOAP to add alias numbers to a
>> subscriber 
>>>
>>> Requested operation:  We would like to write a new alias number for a 
>>> subscriber directly to the database.
>>
>> As long as you make sure to write it to both billing.voip_numbers and
>> provisioning.voip_dbaliases, it should be fine.
>>
>> Andreas
>>
>> _______________________________________________
>> Spce-user mailing list
>> Spce-user at lists.sipwise.com
>> http://lists.sipwise.com/listinfo/spce-user
>>
>> _______________________________________________
>> Spce-user mailing list
>> Spce-user at lists.sipwise.com
>> http://lists.sipwise.com/listinfo/spce-user
>>