(RADIATOR) Seeking some database advice

Hugh Irvine hugh at open.com.au
Tue Dec 14 15:57:38 CST 2004


Hello Ray -

Many thanks - would you be agreeable to us including a copy of your 
script in the goodies directory (with credit to you of course)?

regards

Hugh


On 15 Dec 2004, at 04:01, Ray Van Dolson wrote:

> Brian, here is the script I am using to rotate the ACCOUNTING table.  
> It
> runs monthly from cron.
>
> #!/usr/bin/perl
> #
> # Create new accounting table for the current month.
> #
>
> use strict;
>
> use DBI;
> use POSIX qw(strftime);
>
> my $DATABASE = 'radiator';
> my $DBUSER   = 'radius';
> my $DBPASS   = 'dbpass';
> my $TMPL_TBL = 'ACCOUNTING_TMPL';
> my $TS       = strftime('%Y%m', localtime(time()));
>
> my $dbh = DBI->connect("DBI:mysql:$DATABASE", $DBUSER, $DBPASS,
>             { RaiseError => 1, AutoCommit => 1});
>
> # Let's make sure the table doesn't exist already.
> my $sth = $dbh->prepare("SHOW TABLE STATUS LIKE 'ACCOUNTING$TS'");
> $sth->execute;
> my @results;
>
> if (not @results = $sth->fetchrow_array()) {
>   $sth->finish;
>   $sth = $dbh->prepare("SHOW CREATE TABLE $TMPL_TBL");
>   $sth->execute;
>   my @tbl_row = $sth->fetchrow_array();
>   my $create_sql = $tbl_row[1];
>   $sth->finish;
>
>   # Replace table name with correct one.
>   $create_sql =~ s/ACCOUNTING_TMPL/ACCOUNTING$TS/;
>
>   $sth = $dbh->prepare($create_sql);
>   $sth->execute;
>   $sth->finish;
>   print "Table ACCOUNTING$TS created.\n";
> } else {
>   $sth->finish;
>   print "Table ACCOUNTING$TS already exists.\n";
> }
>
> $dbh->disconnect;
>
> Basically, this creates an ACCOUNTINGYYYYMM table every month from a
> template table you have set up -- ACCOUNTING_TMPL.
>
> You could easily modify it to rotate an ACCOUNTING table to another 
> name
> though I would guess.
>
> Ray
>
> On Thu, Dec 09, 2004 at 07:18:54PM +1100, Hugh Irvine wrote:
>> Hello Brian -
>>
>> I would be inclined to have a cron job or similar archive the data
>> older than a month or two.
>>
>> Other Radiator users may have other ideas.
>>
>> regards
>>
>> Hugh
>>
>> On 9 Dec 2004, at 09:27, Brian Morris wrote:
>>
>>> Hi All,
>>>  
>>> I am seeking some advice regarding the setup of the database tables
>>> for Radiator.
>>>  
>>> Specifically the accounting table as it is becoming quite large 
>>> indeed.
>>>  
>>> We currently have a single accounting table containing millions of
>>> records which is getting pretty large.  Would it be more efficient to
>>> split this table into smaller tables (say one for each month).  The
>>> only reading done on this table is to display a customers detailed
>>> usage history for a particular month so it would make sense to me to
>>> split it into monthly chunks but I'm not sure if there would be any
>>> side-effects in doing so.
>>>  
>>> Any advice would be appreciated.
>>>  
>>> Regards,  Brian.
>>>  
>>>  
>
>

NB:

Have you read the reference manual ("doc/ref.html")?
Have you searched the mailing list archive 
(www.open.com.au/archives/radiator)?
Have you had a quick look on Google (www.google.com)?
Have you included a copy of your configuration file (no secrets),
together with a trace 4 debug showing what is happening?

-- 
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. Available on *NIX, *BSD, Windows, MacOS X.
-
Nets: internetwork inventory and management - graphical, extensible,
flexible with hardware, software, platform and database independence.
-
CATool: Private Certificate Authority for Unix and Unix-like systems.

--
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.


More information about the radiator mailing list