Cassandra storage

CREATE KEYSPACE IF NOT EXISTS opencloud_thirdparty_reg WITH REPLICATION={'class' : 'SimpleStrategy' ,'replication_factor' : 3 };

CREATE KEYSPACE IF NOT EXISTS opencloud_thirdparty_reg WITH REPLICATION={'class' : 'SimpleStrategy' ,'replication_factor' : 1 };

USE opencloud_thirdparty_reg;

/*
Old tables for storing registration data.
Rows use the callid column as a part of the primary key.
However, the callid may not be unique for all devices, across registrations/re-registrations
whilst a device is active.
Issue is resolved by adding a new column (deviceid) in two new tables (see below)
*/

CREATE TABLE IF NOT EXISTS REGISTRATIONDATA(
    callid text,                    //The callid used for registration
    defaultpublicid text,           //The default IMS Public ID
    privateid text,                 //The IMS Private ID
    associateduris list<text>,      //The Associated URIs
    contacts set<text>,             //Registered Contacts
    visitednetworkid text,          //P-Visited-Network-ID header from registration
    creationtime timestamp,         //The registration creation datetime
    expires int,                    //The time this registration expires in seconds
    expirytime timestamp,           //The registration expiration datetime
    tempgruu text,                  //Temporary GRUU defined in the Contacts
    gruu text,                      //The GRUU in the Contacts
    cmsisdn text,                   //The Correlation MSISDN
    stnsr text,                     //The Session Transfer Number for Single Radio
    isuesrvcccapable boolean,       //Whether the ue has capability UE-SRVCC-CAPABILITY-SUPPORTED
    paccessnetworkinfo list<text>,  //P-Access-Network-Info header from registration
    currentatusti text,             //Current ATU-STI, from initial registration
    atcfmgmturi text,               //ATCF Management URI, from initial registration
    atcfpathuri text,               //ATCF path URI, from initial registration
    extensions map<text,text>,      //Extensions map
    primary key(callid)
)
WITH gc_grace_seconds = 172800; // 2 days

CREATE TABLE IF NOT EXISTS ASSOCURIS(
    impu text,                      //A referenced IMS Public ID
    callid text,                    //The callid used for registration
    isdefault boolean,              //Flag indicating if this IMPU is the default public ID
    defaultpublicid text,           //The default IMS Public ID
    privateid text,                 //The IMS Private ID
    regstate text,                  //Registration state: active, terminated or unknown
    creationtime timestamp,         //The registration creation datetime
    expires int,                    //The time this registration expires in seconds
    expirytime timestamp,           //The registration expiration datetime
    cmsisdn text,                   //The Correlation MSISDN
    associateduris list<text>,      //The Associated URIs
    contacts set<text>,             //Registered Contacts
    isuesrvcccapable boolean,       //Whether the ue has capability UE-SRVCC-CAPABILITY-SUPPORTED
    visitednetworkid text,          //P-Visited-Network-ID header from registration
    paccessnetworkinfo list<text>,  //P-Access-Network-Info headers from registration
    currentatusti text,             //Current ATU-STI, from initial registration
    atcfmgmturi text,               //ATCF Management URI, from initial registration
    atcfpathuri text,               //ATCF path URI, from initial registration
    extensions map<text,text>,      //Extensions map used to store PATH information
    primary key(impu,callid)
)
WITH gc_grace_seconds = 172800; // 2 days

/*
New Tables for storing a registration record for a registered device.
Rows uses a new column, deviceid, as a part of the primary key.

A row in the REGISTRATIONDATA_V2 table is created on initial registration, and updated on re-registration.
Corresponding row is removed on de-registration.

There should be one REGISTRATIONDATA_V2 row per registered device, whilst it is active.

Rows in ASSOCURIS_V2 are created on initial registration, and updated on re-registration.
Corresponding rows are removed on de-registration.

There should be one or more ASSOCURIS_V2 rows per registered device, whilst it is active.
*/

CREATE TABLE IF NOT EXISTS REGISTRATIONDATA_V2(
    deviceid text,                  //Uniquely identifies a device (based on privateid, IMEI ..)
    callid text,                    //The callid used for registration
    defaultpublicid text,           //The default IMS Public ID
    privateid text,                 //The IMS Private ID
    associateduris list<text>,      //The Associated URIs
    contacts set<text>,             //Registered Contacts
    visitednetworkid text,          //P-Visited-Network-ID header from registration
    creationtime timestamp,         //The registration creation datetime
    expires int,                    //The time this registration expires in seconds
    expirytime timestamp,           //The registration expiration datetime
    tempgruu text,                  //Temporary GRUU defined in the Contacts
    gruu text,                      //The GRUU in the Contacts
    cmsisdn text,                   //The Correlation MSISDN
    stnsr text,                     //The Session Transfer Number for Single Radio
    isuesrvcccapable boolean,       //Whether the ue has capability UE-SRVCC-CAPABILITY-SUPPORTED
    paccessnetworkinfo list<text>,  //P-Access-Network-Info header from registration
    currentatusti text,             //Current ATU-STI, from initial registration
    atcfmgmturi text,               //ATCF Management URI, from initial registration
    atcfpathuri text,               //ATCF path URI, from initial registration
    extensions map<text,text>,      //Extensions map
    primary key(deviceid)
)
WITH gc_grace_seconds = 172800; // 2 days

CREATE TABLE IF NOT EXISTS ASSOCURIS_V2(
    impu text,                      //A referenced IMS Public ID
    deviceid text,                  //Uniquely identifies a device (based on privateid, IMEI ..)
    callid text,                    //The callid used for registration
    isdefault boolean,              //Flag indicating if this IMPU is the default public ID
    defaultpublicid text,           //The default IMS Public ID
    privateid text,                 //The IMS Private ID
    regstate text,                  //Registration state: active, terminated or unknown
    creationtime timestamp,         //The registration creation datetime
    expires int,                    //The time this registration expires in seconds
    expirytime timestamp,           //The registration expiration datetime
    cmsisdn text,                   //The Correlation MSISDN
    associateduris list<text>,      //The Associated URIs
    contacts set<text>,             //Registered Contacts
    isuesrvcccapable boolean,       //Whether the ue has capability UE-SRVCC-CAPABILITY-SUPPORTED
    visitednetworkid text,          //P-Visited-Network-ID header from registration
    paccessnetworkinfo list<text>,  //P-Access-Network-Info headers from registration
    currentatusti text,             //Current ATU-STI, from initial registration
    atcfmgmturi text,               //ATCF Management URI, from initial registration
    atcfpathuri text,               //ATCF path URI, from initial registration
    extensions map<text,text>,      //Extensions map used to store PATH information
    primary key(impu, deviceid)
)
WITH gc_grace_seconds = 172800; // 2 days