(RADIATOR) Seeking some database advice (Modified by Hugh Irvine)

[Mike McCauley]

Hello Ray,

Thanks for your contribution.
We have added it to the goodies for the next relase and it is also in the 
latest patch set.

Thanks again.

Cheers.


> 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.
> >>  
> >>  

-- 
Mike McCauley                               mikem at open.com.au
Open System Consultants Pty. Ltd            Unix, Perl, Motif, C++, WWW
9 Bulbul Place Currumbin Waters QLD 4223 Australia   http://www.open.com.au
Phone +61 7 5598-7474                       Fax   +61 7 5598-7070

Radiator: the most portable, flexible and configurable RADIUS server 
anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald, 
Platypus, Freeside, TACACS+, PAM, external, Active Directory, EAP, TLS, 
TTLS, PEAP etc on Unix, Windows, MacOS etc.

--
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.