How to store tariffplan data into database.

[Aqs Younas]

Hi, Newbie here.
I am trying to use database with cgrates for tariff plans.
Could someone guide me how I can configure database for tariff plan. I am using defaut tutorial example with freeswitch which I think use redis for this.
Any pointer is much appreciated.
Thanks

[Iwada Eja]

Hi Aqs, 

Welcome! 

Configurations are done in cgrates.json file. 

Can we see what you've got in yours? 

--
You received this message because you are subscribed to the Google Groups "CGRateS" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cgrates+unsubscribe@googlegroups.com.
To post to this group, send email to cgr...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/cgrates/2ada602e-78b0-4e03-a459-391d71827ce8%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Aqs Younas]

Thank you.

Below is my configuration file(cgrates.json).

{

// Real-time Charging System for Telecom & ISP environments
// Copyright (C) ITsysCOM GmbH
//
// This file contains the default configuration hardcoded into CGRateS.
// This is what you get when you load CGRateS with an empty configuration file.


"rals": {
    "enabled": true,                        // enable Rater service: <true|false>
    "cdrstats_conns": [
        {"address": "*internal"}
    ],
    "historys_conns": [
        {"address": "*internal"}
    ],
    "pubsubs_conns": [
        {"address": "*internal"}
    ],
    "users_conns": [
        {"address": "*internal"}
    ],
    "aliases_conns": [
        {"address": "*internal"}
    ],
},



"data_db": {                                // database used to store runtime data (eg: accounts, cdr stats)
     "db_type": "redis",                        // data_db type: <redis|mongo>
     "db_host": "127.0.0.1",                    // data_db host address
     "db_port": 6379,                         // data_db port to reach the database
     "db_name": "10",                         // data_db database name to connect to
     "db_user": "cgrates",                     // username to use when connecting to data_db
     "db_password": "",                         // password to use when connecting to data_db
     "load_history_size": 10,                // Number of records in the load history
},


"stor_db": {                                // database used to store offline tariff plans and CDRs
     "db_type": "mysql",                        // stor database type to use: <mongo|mysql|postgres>
     "db_host": "127.0.0.1",                    // the host to connect to
     "db_port": 3306,                        // the port to reach the stordb
     "db_name": "cgrates",                    // stor database name
     "db_user": "root",                    // username to use when connecting to stordb
     "db_password": "hammer",                        // password to use when connecting to stordb
     "max_open_conns": 100,                    // maximum database connections opened
     "max_idle_conns": 10,                    // maximum database connections idle
     "cdrs_indexes": [],                        // indexes on cdrs table to speed up queries, used only in case of mongo
},



"scheduler": {
    "enabled": true,                        // start Scheduler service: <true|false>
},


"cdrs": {
    "enabled": true,                        // start the CDR Server service:  <true|false>
    "cdrstats_conns": [
        {"address": "*internal"}
    ],
},


"cdrstats": {
    "enabled": true,                        // starts the cdrstats service: <true|false>
},


//"rls": {
//    "enabled": true,                        // starts ResourceLimiter service: <true|false>.
//    "cdrstats_conns": [
//        {"address": "*internal"}
//    ],
//},


"cdre": {
    "*default": {
        "cdr_format": "csv",                            // exported CDRs format <csv>
        "field_separator": ",",
        "data_usage_multiply_factor": 1,                // multiply data usage before export (eg: convert from KBytes to Bytes)
        "sms_usage_multiply_factor": 1,                    // multiply data usage before export (eg: convert from SMS unit to call duration in some billing systems)
        "generic_usage_multiply_factor": 1,                    // multiply data usage before export (eg: convert from GENERIC unit to call duration in some billing systems)
        "cost_multiply_factor": 1,                        // multiply cost before export, eg: add VAT
        "cost_rounding_decimals": -1,                    // rounding decimals for Cost values. -1 to disable rounding
        "cost_shift_digits": 0,                            // shift digits in the cost on export (eg: convert from EUR to cents)
        "mask_destination_id": "MASKED_DESTINATIONS",    // destination id containing called addresses to be masked on export
        "mask_length": 0,                                // length of the destination suffix to be masked
        "export_dir": "/tmp/cgr_fsevsock/cgrates/cdre",            // path where the exported CDRs will be placed
        "header_fields": [],                            // template of the exported header fields
        "content_fields": [                                // template of the exported content fields
            {"tag": "CgrId", "type": "*composed", "value": "CGRID"},
            {"tag":"RunId", "type": "*composed", "value": "RunID"},
            {"tag":"Tor", "type": "cdrfield", "value": "ToR"},
            {"tag":"AccId", "type": "*composed", "value": "OriginID"},
            {"tag":"ReqType", "type": "*composed", "value": "RequestType"},
            {"tag":"Direction", "type": "*composed", "value": "Direction"},
            {"tag":"Tenant", "type": "*composed", "value": "Tenant"},
            {"tag":"Category", "type": "*composed", "value": "Category"},
            {"tag":"Account", "type": "*composed", "value": "Account"},
            {"tag":"Subject", "type": "*composed", "value": "Subject"},
            {"tag":"Destination", "type": "*composed", "value": "Destination"},
            {"tag":"SetupTime", "type": "*datetime", "value": "SetupTime", "layout": "2006-01-02T15:04:05Z07:00"},
            {"tag":"AnswerTime", "type": "*datetime", "value": "AnswerTime", "layout": "2006-01-02T15:04:05Z07:00"},
            {"tag":"Usage", "type": "*datetime", "value": "Usage"},
            {"tag":"Cost", "type": "*composed", "value": "Cost"},
        ],
        "trailer_fields": [],                            // template of the exported trailer fields
    },
    "customer_tpl": {
        "cdr_format": "csv",                            // exported CDRs format <csv>
        "field_separator": ";",
        "data_usage_multiply_factor": 1,                // multiply data usage before export (eg: convert from KBytes to Bytes)
        "sms_usage_multiply_factor": 1,                    // multiply data usage before export (eg: convert from SMS unit to call duration in some billing systems)
        "generic_usage_multiply_factor": 1,                    // multiply data usage before export (eg: convert from GENERIC unit to call duration in some billing systems)
        "cost_multiply_factor": 1,                        // multiply cost before export, eg: add VAT
        "cost_rounding_decimals": -1,                    // rounding decimals for Cost values. -1 to disable rounding
        "cost_shift_digits": 0,                            // shift digits in the cost on export (eg: convert from EUR to cents)
        "mask_destination_id": "MASKED_DESTINATIONS",    // destination id containing called addresses to be masked on export
        "mask_length": 0,                                // length of the destination suffix to be masked
        "export_dir": "/tmp/cgr_fsevsock/cgrates/cdre",            // path where the exported CDRs will be placed
        "header_fields": [],                            // template of the exported header fields
        "content_fields": [                                // template of the exported content fields
            {"tag": "CgrId", "type": "*composed", "value": "CGRID"},
            {"tag":"AccId", "type": "*composed", "value": "OriginID"},
            {"tag":"ReqType", "type": "*composed", "value": "RequestType"},
            {"tag":"Tenant", "type": "*composed", "value": "Tenant"},
            {"tag":"Category", "type": "*composed", "value": "Category"},
            {"tag":"Subject", "type": "*composed", "value": "Account"},
            {"tag":"Destination", "type": "*composed", "value": "~Destination:s/^1(\\d+)/+$1/:s/^\\+(\\d+)/00$1/"},
            {"tag":"AnswerTime", "type": "*datetime", "value": "AnswerTIme", "layout": "2006-01-02T15:04:05Z07:00"},
            {"tag":"Usage", "type": "*composed", "value": "Usage"},
            {"tag":"Cost", "type": "*composed", "value": "Cost"},
        ],
        "trailer_fields": [],   
    },
},


"sm_freeswitch": {
    "enabled": true,                // starts SessionManager service: <true|false>
    "debit_interval": "5s",            // interval to perform debits on.
    //"rls_conns": [
    //    {"address": "*internal"}
    //],
    "channel_sync_interval": "10s",
    "event_socket_conns":[                    // instantiate connections to multiple FreeSWITCH servers
        {"address": "127.0.0.1:8021", "password": "ClueCon", "reconnects": 5}
    ],
},


"historys": {
    "enabled": true,                            // starts History service: <true|false>.
    "history_dir": "/tmp/cgr_fsevsock/cgrates/history",    // location on disk where to store history files.
},


"pubsubs": {
    "enabled": true,                // starts PubSub service: <true|false>.
},


"users": {
    "enabled": true,                // starts User service: <true|false>.
    "indexes": ["Uuid"],            // user profile field indexes
},

"aliases": {
    "enabled": true,
}


}

I did not see any error in syslog.

Any pointer is much appreciated.

--
You received this message because you are subscribed to a topic in the Google Groups "CGRateS" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/cgrates/41kGAeh-XS0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to cgrates+unsubscribe@googlegroups.com.

To post to this group, send email to cgr...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/cgrates/CAHaUJVa_duSwZhf6pBaY6Ntdc3N--NU3vaUa9rYFPU%2Bzcn9JcQ%40mail.gmail.com.

[Dan B]

Hi

please try to remove /var/run/cgrates folder and restart CGRateS. Sometimes it helps if you have started under different user. Otherwise start without init script (just by calling in console cgr-engine binary) and post here what you get in both console and syslog.

DanB

To view this discussion on the web visit https://groups.google.com/d/msgid/cgrates/CAGVOjVVDJ%2BA3Mdbq-%2BcGoc4dVVwBP_qjcO2gf45kOvF9You22g%40mail.gmail.com.

[Iwada Eja]

+1,

I remember having a similar issue and what Dan suggested was the fix i used then.

To view this discussion on the web visit https://groups.google.com/d/msgid/cgrates/CA%2BJAn%2BQ82PQe2s73p4waaZu1f88C_ayY0Ete5qZGxjcX8QAA3g%40mail.gmail.com.

[Aqs Younas]

Guys, i have been successful in starting cgrates with mysql after manually creating /tmp/cgr_fsevsock/cgrates/cdrc/in and /tmp/cgr_fsevsock/cgrates/cdrc/out directories. But still tarrifplan related data is being inserted into redis instead of mysql.

I try to popluate mysql tarrifplan database with below command.

root@debian:/usr/share/cgrates/tariffplans/tutorial# ls
AccountActions.csv  Actions.csv         Aliases.csv   DerivedChargers.csv   Destinations.csv  Rates.csv        RatingProfiles.csv  ResourceLimits.csv  Timings.csv
ActionPlans.csv     ActionTriggers.csv  CdrStats.csv  DestinationRates.csv  LcrRules.csv      RatingPlans.csv  README.md           SharedGroups.csv    Users.csv
root@debian:/usr/share/cgrates/tariffplans/tutorial# cgr-loader
root@debian:/usr/share/cgrates/tariffplans/tutorial#
 

But i see cdrs tables being populated with cdrs.

How i can make cgrates to read and insert tarrifplan data from mysql instead of redis.

Also what does it mean.
stor_db database used to store offline tariff plans?

What are offline tariff plans?

Maybe what is why it is not inserting tariff into database.

And another question can i insert accounts into some database also instead of redis?

My configuration file.

Thanks for any pointer.

To view this discussion on the web visit https://groups.google.com/d/msgid/cgrates/CAHaUJVZh%3Dcrx_t%3DC_1YNsE1M4DdEZxbi68f08k5OuPkcr%3DfUmg%40mail.gmail.com.

[DanB]

Hi Aqs,

Answers inline ...

On Fri, 2017-05-05 at 19:27 +0500, Aqs Younas wrote:
> Guys, i have been successful in starting cgrates with mysql after
> manually creating /tmp/cgr_fsevsock/cgrates/cdrc/in and
> /tmp/cgr_fsevsock/cgrates/cdrc/out directories.

It should work automated by removing /tmp/cgr_fsevsock completely and
restart both FreeSWITCH and CGRateS.

> But still tarrifplan related data is being inserted into redis
> instead of mysql. 

For active TP we only support redis or mongo (due to performance
reasons). If you want to store your TP offline, you should do something
like: cgr-loader -to_stordb

> I try to popluate mysql tarrifplan database with below command. 
>
> root@debian:/usr/share/cgrates/tariffplans/tutorial# ls
> AccountActions.csv  Actions.csv         Aliases.csv  
> DerivedChargers.csv   Destinations.csv  Rates.csv       
> RatingProfiles.csv  ResourceLimits.csv  Timings.csv
> ActionPlans.csv     ActionTriggers.csv  CdrStats.csv 
> DestinationRates.csv  LcrRules.csv      RatingPlans.csv 
> README.md           SharedGroups.csv    Users.csv
> root@debian:/usr/share/cgrates/tariffplans/tutorial# cgr-loader
> root@debian:/usr/share/cgrates/tariffplans/tutorial#
>  
> But i see cdrs tables being populated with cdrs. 
>
> How i can make cgrates to read and insert tarrifplan data from mysql
> instead of redis. 

Only for offline purposes you can do it with command above but the TP
will not be used in price calculations. It will be there just for
archiving.

>
> Also what does it mean. 
> stor_db database used to store offline tariff plans?

Offline TP is stored for later, more or less the same as your .csv in
folder. They are there but CGRateS is not using them until you load
them to DataDB/online one.

> What are offline tariff plans?
> Maybe what is why it is not inserting tariff into database. 

Most likely yes :).

>
> And another question can i insert accounts into some database also
> instead of redis?

We only support redis or mongo as data dbs (online). Accounts are
stored in dataDB always.

DanB

> > > > > it, send an email to cgrates+u...@googlegroups.com.

> > > > > To post to this group, send email to cgr...@googlegroups.com
> > > > > .
> > > > > To view this discussion on the web visit https://groups.googl
> > > > > e.com/d/msgid/cgrates/2ada602e-78b0-4e03-a459-
> > > > > 391d71827ce8%40googlegroups.com.
> > > > > For more options, visit https://groups.google.com/d/optout.
> > > > >
> > > > > -- 
> > > > > You received this message because you are subscribed to a
> > > > > topic in the Google Groups "CGRateS" group.
> > > > > To unsubscribe from this topic, visit https://groups.google.c
> > > > > om/d/topic/cgrates/41kGAeh-XS0/unsubscribe.
> > > > > To unsubscribe from this group and all its topics, send an

> > > > > email to cgrates+u...@googlegroups.com.

> > > > > To post to this group, send email to cgr...@googlegroups.com
> > > > > .
> > > > > To view this discussion on the web visit https://groups.googl

> > > > > e.com/d/msgid/cgrates/CAHaUJVa_duSwZhf6pBaY6Ntdc3N
> > > > > --NU3vaUa9rYFPU%2Bzcn9JcQ%40mail.gmail.com.

> > > > > For more options, visit https://groups.google.com/d/optout.
> > > > >
> > > >
> > > > -- 
> > > > You received this message because you are subscribed to the
> > > > Google Groups "CGRateS" group.
> > > > To unsubscribe from this group and stop receiving emails from

> > > > it, send an email to cgrates+u...@googlegroups.com.

> > > > To post to this group, send email to cgr...@googlegroups.com.
> > > > To view this discussion on the web visit https://groups.google.

> > > > com/d/msgid/cgrates/CAGVOjVVDJ%2BA3Mdbq-
> > > > %2BcGoc4dVVwBP_qjcO2gf45kOvF9You22g%40mail.gmail.com.

> > > > For more options, visit https://groups.google.com/d/optout.
> > > >
> > >
> > > -- 
> > > You received this message because you are subscribed to the
> > > Google Groups "CGRateS" group.
> > > To unsubscribe from this group and stop receiving emails from it,

> > > send an email to cgrates+u...@googlegroups.com.

> > > To post to this group, send email to cgr...@googlegroups.com.
> > > To view this discussion on the web visit https://groups.google.co

> > > m/d/msgid/cgrates/CA%2BJAn%2BQ82PQe2s73p4waaZu1f88C_ayY0Ete5qZGxj
> > > cX8QAA3g%40mail.gmail.com.

[Aqs Younas]

Thank you very much for such nice help and software. :) 

I will come back if i have any other question.
 

> > > > > it, send an email to cgrates+unsubscribe@googlegroups.com.

> > > > > To post to this group, send email to cgr...@googlegroups.com
> > > > > .
> > > > > To view this discussion on the web visit https://groups.googl
> > > > > e.com/d/msgid/cgrates/2ada602e-78b0-4e03-a459-
> > > > > 391d71827ce8%40googlegroups.com.
> > > > > For more options, visit https://groups.google.com/d/optout.
> > > > >
> > > > > -- 
> > > > > You received this message because you are subscribed to a
> > > > > topic in the Google Groups "CGRateS" group.
> > > > > To unsubscribe from this topic, visit https://groups.google.c
> > > > > om/d/topic/cgrates/41kGAeh-XS0/unsubscribe.
> > > > > To unsubscribe from this group and all its topics, send an

> > > > > email to cgrates+unsubscribe@googlegroups.com.

> > > > > To post to this group, send email to cgr...@googlegroups.com
> > > > > .
> > > > > To view this discussion on the web visit https://groups.googl

> > > > > e.com/d/msgid/cgrates/CAHaUJVa_duSwZhf6pBaY6Ntdc3N

> > > > > --NU3vaUa9rYFPU%2Bzcn9JcQ%40mail.gmail.com.
> > > > > For more options, visit https://groups.google.com/d/optout.
> > > > >
> > > >
> > > > -- 
> > > > You received this message because you are subscribed to the
> > > > Google Groups "CGRateS" group.
> > > > To unsubscribe from this group and stop receiving emails from

> > > > it, send an email to cgrates+unsubscribe@googlegroups.com.

> > > > To post to this group, send email to cgr...@googlegroups.com.
> > > > To view this discussion on the web visit https://groups.google.
> > > > com/d/msgid/cgrates/CAGVOjVVDJ%2BA3Mdbq-
> > > > %2BcGoc4dVVwBP_qjcO2gf45kOvF9You22g%40mail.gmail.com.
> > > > For more options, visit https://groups.google.com/d/optout.
> > > >
> > >
> > > -- 
> > > You received this message because you are subscribed to the
> > > Google Groups "CGRateS" group.
> > > To unsubscribe from this group and stop receiving emails from it,

> > > send an email to cgrates+unsubscribe@googlegroups.com.

> > > To post to this group, send email to cgr...@googlegroups.com.
> > > To view this discussion on the web visit https://groups.google.co

> > > m/d/msgid/cgrates/CA%2BJAn%2BQ82PQe2s73p4waaZu1f88C_ayY0Ete5qZGxj
> > > cX8QAA3g%40mail.gmail.com.

> > >
> > > For more options, visit https://groups.google.com/d/optout.
> > >
> >
> >

--
You received this message because you are subscribed to a topic in the Google Groups "CGRateS" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/cgrates/41kGAeh-XS0/unsubscribe.

To unsubscribe from this group and all its topics, send an email to cgrates+unsubscribe@googlegroups.com.

To post to this group, send email to cgr...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/cgrates/1493995331.3140.4.camel%40cgrates.org.