[Spce-user] Subscriber Alias Entries in Database

Fri Sep 5 06:44:58 EDT 2014

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
> 