Hi; I've been playing around with various report writers on the cdr_mysql table and I'm getting rather irritated. I started a thread a couple weeks ago on "Billing Fun" and got a lot of good responses on how to get an up-to-date copy of the NDA/NXX database, which I managed to retrieve and bring into MySQL in a script from the various pieces of code people posted... very nice. After going into this preopinionated from a "php perspective" of MySQL as a really great tool, by now I'm experiencing the sinking feeling of being let down- both on the MySQL side and on the various report writer limitations I've come across. My goal is to be able to generate a professional looking VOIP Phone bill, with City & State lookup, Country lookup for International, as well as a rating table by customer - so I can come up with various different plans for my clients individually. I want a billing run to either calculate this - with appropriate lookups, or I want this to be done on the database side, populating a secondary cdr table of some sort- so the report writer run just pulls stuff out of one table and does nothing more complex than subtotals - simple and easy. The reasons I'm frustrated are as follows: 1) MySQl cannot do stored procedures or triggers, or even views. For this reason I can't just add a cdr_billing table and pull in City & State from the NDA/NXX table, et al, on the fly as a call is hung up. (or various other real-time sorts of ideas that come up along the way) MySQL is not a serious database. Anyone who thinks otherwise doesn't have a clue yet. 2) The best Open Source report writers I've come across are so deeply in love with Java and low level languages that they don't give the end user trying to perform calculations anything but the most cryptic tools to work with. If I wanted to learn Java I'd just write the whole thing in pure Java - funk dat. One can't even directly perform SQL queries without having an interpreter of some sort in-between. 3) I'm trying to re-invent the wheel, but, perhaps in a pretty nice way that might be either marketable or usefull to others as Open Source, though I can't justify spending a lot of time on it unless it brings something in for me. So since my wife is a senior Oracle (Financials) person, and I've got 9i running on a couple of machines (experimenting with Compiere CRM/ERP) I'm really tempted to put her to work. Unless somebody already has a really slick solution to this I'd be very curious (and appreciative) to hear people's thoughts. BTW: I was involved in bizarre telephony billing systems back in the early 90's with International Callback. I'm not a heads down programmer, but a math major/cs minor, so the fascination for me is in solving the problem of creating a sort of "rating engine," but not writing every line of the code. I'm located in Argentina, so I have great resources of human capital to tap into at rates cheaper than India. Does anybody see a need/opportunity for something like this? Thank you very much in advance for your ideas or comments. Thomas Hutton
cdr_odbc can talk to oracle and many more. bkw> -----Original Message----- > From: asterisk-users-bounces@lists.digium.com [mailto:asterisk-users- > bounces@lists.digium.com] On Behalf Of Thomas Hutton > Sent: Friday, October 01, 2004 3:23 PM > To: AsteriskUserMaillist > Subject: [Asterisk-Users] CDR_Oracle anybody? > > Hi; > > I've been playing around with various report writers on the cdr_mysql > table and I'm getting rather irritated. I started a thread a couple > weeks ago on "Billing Fun" and got a lot of good responses on how to get > an up-to-date copy of the NDA/NXX database, which I managed to retrieve > and bring into MySQL in a script from the various pieces of code people > posted... very nice. > After going into this preopinionated from a "php perspective" of MySQL > as a really great tool, by now I'm experiencing the sinking feeling of > being let down- both on the MySQL side and on the various report writer > limitations I've come across. > > My goal is to be able to generate a professional looking VOIP Phone > bill, with City & State lookup, Country lookup for International, as > well as a rating table by customer - so I can come up with various > different plans for my clients individually. I want a billing run to > either calculate this - with appropriate lookups, or I want this to be > done on the database side, populating a secondary cdr table of some > sort- so the report writer run just pulls stuff out of one table and > does nothing more complex than subtotals - simple and easy. > > The reasons I'm frustrated are as follows: > 1) MySQl cannot do stored procedures or triggers, or even views. For > this reason I can't just add a cdr_billing table and pull in City & > State from the NDA/NXX table, et al, on the fly as a call is hung up. > (or various other real-time sorts of ideas that come up along the way) > MySQL is not a serious database. Anyone who thinks otherwise doesn't > have a clue yet. > 2) The best Open Source report writers I've come across are so deeply in > love with Java and low level languages that they don't give the end user > trying to perform calculations anything but the most cryptic tools to > work with. If I wanted to learn Java I'd just write the whole thing in > pure Java - funk dat. One can't even directly perform SQL queries > without having an interpreter of some sort in-between. > 3) I'm trying to re-invent the wheel, but, perhaps in a pretty nice way > that might be either marketable or usefull to others as Open Source, > though I can't justify spending a lot of time on it unless it brings > something in for me. > > So since my wife is a senior Oracle (Financials) person, and I've got 9i > running on a couple of machines (experimenting with Compiere CRM/ERP) > I'm really tempted to put her to work. > > Unless somebody already has a really slick solution to this I'd be very > curious (and appreciative) to hear people's thoughts. BTW: I was > involved in bizarre telephony billing systems back in the early 90's > with International Callback. I'm not a heads down programmer, but a > math major/cs minor, so the fascination for me is in solving the problem > of creating a sort of "rating engine," but not writing every line of the > code. I'm located in Argentina, so I have great resources of human > capital to tap into at rates cheaper than India. Does anybody see a > need/opportunity for something like this? > > Thank you very much in advance for your ideas or comments. > > Thomas Hutton > > _______________________________________________ > Asterisk-Users mailing list > Asterisk-Users@lists.digium.com > http://lists.digium.com/mailman/listinfo/asterisk-users > To UNSUBSCRIBE or update options visit: > http://lists.digium.com/mailman/listinfo/asterisk-users
You probably just need to load the CSV CDR records into Oracle... It's probably not that difficult to do that in real-time, even. Rob
I'm in no ways a database guru, but take a look at PostgreSQL. It does most of what MySQL won't, and is still opensource. -Tim -----Original Message----- From: Thomas Hutton [mailto:pres@nicheware.com] Sent: Friday, October 01, 2004 3:23 PM To: AsteriskUserMaillist Subject: [Asterisk-Users] CDR_Oracle anybody? Hi; I've been playing around with various report writers on the cdr_mysql table and I'm getting rather irritated. I started a thread a couple weeks ago on "Billing Fun" and got a lot of good responses on how to get an up-to-date copy of the NDA/NXX database, which I managed to retrieve and bring into MySQL in a script from the various pieces of code people posted... very nice. After going into this preopinionated from a "php perspective" of MySQL as a really great tool, by now I'm experiencing the sinking feeling of being let down- both on the MySQL side and on the various report writer limitations I've come across. My goal is to be able to generate a professional looking VOIP Phone bill, with City & State lookup, Country lookup for International, as well as a rating table by customer - so I can come up with various different plans for my clients individually. I want a billing run to either calculate this - with appropriate lookups, or I want this to be done on the database side, populating a secondary cdr table of some sort- so the report writer run just pulls stuff out of one table and does nothing more complex than subtotals - simple and easy. The reasons I'm frustrated are as follows: 1) MySQl cannot do stored procedures or triggers, or even views. For this reason I can't just add a cdr_billing table and pull in City & State from the NDA/NXX table, et al, on the fly as a call is hung up. (or various other real-time sorts of ideas that come up along the way) MySQL is not a serious database. Anyone who thinks otherwise doesn't have a clue yet. 2) The best Open Source report writers I've come across are so deeply in love with Java and low level languages that they don't give the end user trying to perform calculations anything but the most cryptic tools to work with. If I wanted to learn Java I'd just write the whole thing in pure Java - funk dat. One can't even directly perform SQL queries without having an interpreter of some sort in-between. 3) I'm trying to re-invent the wheel, but, perhaps in a pretty nice way that might be either marketable or usefull to others as Open Source, though I can't justify spending a lot of time on it unless it brings something in for me. So since my wife is a senior Oracle (Financials) person, and I've got 9i running on a couple of machines (experimenting with Compiere CRM/ERP) I'm really tempted to put her to work. Unless somebody already has a really slick solution to this I'd be very curious (and appreciative) to hear people's thoughts. BTW: I was involved in bizarre telephony billing systems back in the early 90's with International Callback. I'm not a heads down programmer, but a math major/cs minor, so the fascination for me is in solving the problem of creating a sort of "rating engine," but not writing every line of the code. I'm located in Argentina, so I have great resources of human capital to tap into at rates cheaper than India. Does anybody see a need/opportunity for something like this? Thank you very much in advance for your ideas or comments. Thomas Hutton _______________________________________________ Asterisk-Users mailing list Asterisk-Users@lists.digium.com http://lists.digium.com/mailman/listinfo/asterisk-users To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
> The reasons I'm frustrated are as follows: > 1) MySQl cannot do stored procedures or triggers, or even views.Take a look at most recent MySQL 5.0. It does stored procedures. Never done them myself cause its faster for me to program a "procedure" in php. Transaction support has been in MySQL for some time now.> MySQL is not a serious database. Anyone who thinks otherwise doesn't havea clue yet. I don't want this to become a MySQL flame war but I've used Postgres and Oracle 8i and the speed and ease of MySQL makes it a far better database. I've used MySQL in situations with Gigs of data and millions of rows. It is deffinatly a serious database. Sure people say "MySQL doesn't have feature XXX" which was probably true a year ago, but not anymore. Just my $0.02 Matthew