From 239484572a9191100993bb1e85ffe8834689feb0 Mon Sep 17 00:00:00 2001 From: ivan Date: Mon, 4 Mar 2002 12:48:49 +0000 Subject: [PATCH] *** empty log message *** --- FS/FS/Record.pm | 54 ++++++++++++++++++++++++++---- FS/FS/nas.pm | 4 ++- Makefile | 4 +-- README.1.4.0pre12 | 4 +++ bin/create-history-tables | 78 +++++++++++++++++++++++++++++++++++++++++++ bin/fs-radius-add-check | 9 +++++ bin/fs-radius-add-reply | 11 +++++- bin/fs-setup | 59 +++++++++++++++++++++++++++++--- httemplate/docs/upgrade8.html | 2 ++ 9 files changed, 209 insertions(+), 16 deletions(-) create mode 100755 bin/create-history-tables diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm index 0aa010bbc..7dc19ccac 100644 --- a/FS/FS/Record.pm +++ b/FS/FS/Record.pm @@ -344,7 +344,7 @@ sub table { =item dbdef_table -Returns the FS::dbdef_table object for the table. +Returns the DBIx::DBSchema::Table object for the table. =cut @@ -473,20 +473,27 @@ sub insert { $self->unique($primary_key) if $primary_key && ! $self->getfield($primary_key); + #false laziness w/delete my @fields = grep defined($self->getfield($_)) && $self->getfield($_) ne "", $self->fields ; + my @values = map { _quote( $self->getfield($_), $self->table, $_) } @fields; + #eslaf my $statement = "INSERT INTO ". $self->table. " ( ". - join(', ',@fields ). + join( ', ', @fields ). ") VALUES (". - join(', ',map(_quote($self->getfield($_),$self->table,$_), @fields)). + join( ', ', @values ). ")" ; warn "[debug]$me $statement\n" if $DEBUG; my $sth = dbh->prepare($statement) or return dbh->errstr; + my $h_statement = $self->_h_statement('insert'); + warn "[debug]$me $h_statement\n" if $DEBUG; + my $h_sth = dbh->prepare($h_statement) or return dbh->errstr; + local $SIG{HUP} = 'IGNORE'; local $SIG{INT} = 'IGNORE'; local $SIG{QUIT} = 'IGNORE'; @@ -495,6 +502,7 @@ sub insert { local $SIG{PIPE} = 'IGNORE'; $sth->execute or return $sth->errstr; + $h_sth->execute or return $h_sth->errstr; dbh->commit or croak dbh->errstr if $FS::UID::AutoCommit; ''; @@ -521,7 +529,7 @@ otherwise returns false. sub delete { my $self = shift; - my($statement)="DELETE FROM ". $self->table. " WHERE ". join(' AND ', + my $statement = "DELETE FROM ". $self->table. " WHERE ". join(' AND ', map { $self->getfield($_) eq '' #? "( $_ IS NULL OR $_ = \"\" )" @@ -537,6 +545,10 @@ sub delete { warn "[debug]$me $statement\n" if $DEBUG; my $sth = dbh->prepare($statement) or return dbh->errstr; + my $h_statement = $self->_h_statement('delete'); + warn "[debug]$me $h_statement\n" if $DEBUG; + my $h_sth = dbh->prepare($h_statement) or return dbh->errstr; + local $SIG{HUP} = 'IGNORE'; local $SIG{INT} = 'IGNORE'; local $SIG{QUIT} = 'IGNORE'; @@ -546,6 +558,7 @@ sub delete { my $rc = $sth->execute or return $sth->errstr; #not portable #return "Record not found, statement:\n$statement" if $rc eq "0E0"; + $h_sth->execute or return $h_sth->errstr; dbh->commit or croak dbh->errstr if $FS::UID::AutoCommit; undef $self; #no need to keep object! @@ -611,6 +624,14 @@ sub replace { warn "[debug]$me $statement\n" if $DEBUG; my $sth = dbh->prepare($statement) or return dbh->errstr; + my $h_old_statement = $old->_h_statement('replace_old'); + warn "[debug]$me $h_old_statement\n" if $DEBUG; + my $h_old_sth = dbh->prepare($h_old_statement) or return dbh->errstr; + + my $h_new_statement = $new->_h_statement('replace_new'); + warn "[debug]$me $h_new_statement\n" if $DEBUG; + my $h_new_sth = dbh->prepare($h_new_statement) or return dbh->errstr; + local $SIG{HUP} = 'IGNORE'; local $SIG{INT} = 'IGNORE'; local $SIG{QUIT} = 'IGNORE'; @@ -620,6 +641,8 @@ sub replace { my $rc = $sth->execute or return $sth->errstr; #not portable #return "Record not found (or records identical)." if $rc eq "0E0"; + $h_old_sth->execute or return $h_old_sth->errstr; + $h_new_sth->execute or return $h_new_sth->errstr; dbh->commit or croak dbh->errstr if $FS::UID::AutoCommit; ''; @@ -647,6 +670,23 @@ sub check { confess "FS::Record::check not implemented; supply one in subclass!"; } +sub _h_statement { + my( $self, $action ) = @_; + + my @fields = + grep defined($self->getfield($_)) && $self->getfield($_) ne "", + $self->fields + ; + my @values = map { _quote( $self->getfield($_), $self->table, $_) } @fields; + + "INSERT INTO h_". $self->table. " ( ". + join(', ', qw(history_date history_user history_action), @fields ). + ") VALUES (". + join(', ', time, dbh->quote(getotaker()), dbh->quote($action), @values). + ")" + ; +} + =item unique COLUMN Replaces COLUMN in record with a unique number. Called by the B method @@ -1056,7 +1096,7 @@ sub reload_dbdef { =item dbdef -Returns the current database definition. See L. +Returns the current database definition. See L. =cut @@ -1066,7 +1106,7 @@ sub dbdef { $dbdef; } This is an internal function used to construct SQL statements. It returns VALUE DBI-quoted (see L) unless VALUE is a number and the column -type (see L) does not end in `char' or `binary'. +type (see L) does not end in `char' or `binary'. =cut @@ -1136,7 +1176,7 @@ The whole fields / hfields mess should be removed. The various WHERE clauses should be subroutined. -table string should be depriciated in favor of FS::dbdef_table. +table string should be depriciated in favor of DBIx::DBSchema::Table. No doubt we could benefit from a Tied hash. Documenting how exists / defined true maps to the database (and WHERE clauses) would also help. diff --git a/FS/FS/nas.pm b/FS/FS/nas.pm index cb0c1b901..58c6827ea 100644 --- a/FS/FS/nas.pm +++ b/FS/FS/nas.pm @@ -136,10 +136,12 @@ sub heartbeat { =head1 VERSION -$Id: nas.pm,v 1.5 2001-04-15 13:35:12 ivan Exp $ +$Id: nas.pm,v 1.6 2002-03-04 12:48:49 ivan Exp $ =head1 BUGS +heartbeat method uses SQL directly and doesn't update history tables. + =head1 SEE ALSO L, schema.html from the base documentation. diff --git a/Makefile b/Makefile index 7596c5b7c..8f577c579 100644 --- a/Makefile +++ b/Makefile @@ -21,8 +21,8 @@ QUEUED_RESTART = /etc/init.d/freeside restart #not changable yet FREESIDE_CONF = /usr/local/etc/freeside -VERSION="1.4.0pre11" -TAG="1_4_0_PRE11" +VERSION="1.4.0pre12" +TAG="1_4_0_PRE12" help: @echo "supported targets: aspdocs masondocs alldocs docs install-docs" diff --git a/README.1.4.0pre12 b/README.1.4.0pre12 index 3f30aa48c..aba8b7a37 100644 --- a/README.1.4.0pre12 +++ b/README.1.4.0pre12 @@ -15,5 +15,9 @@ UPDATE cust_bill_event SET status = 'done'; Run bin/dbdef-create +Run bin/create-history-tables + +Run bin/dbdef-create again + Restart Apache and freeside-queued diff --git a/bin/create-history-tables b/bin/create-history-tables new file mode 100755 index 000000000..fb4c866c6 --- /dev/null +++ b/bin/create-history-tables @@ -0,0 +1,78 @@ +#!/usr/bin/perl -Tw + +use strict; +use DBI; +use DBIx::DBSchema 0.20; +use DBIx::DBSchema::Table; +use DBIx::DBSchema::Column; +use DBIx::DBSchema::ColGroup::Unique; +use DBIx::DBSchema::ColGroup::Index; +use FS::UID qw(adminsuidsetup); +use FS::Record qw(dbdef); + +my $user = shift or die &usage; +my $dbh = adminsuidsetup $user; + +my $schema = dbdef(); + +#false laziness w/fs-setup +foreach my $table ( grep { ! /^h_/ } $schema->tables ) { + my $tableobj = $schema->table($table); + my $h_tableobj = DBIx::DBSchema::Table->new( { + name => "h_$table", + primary_key => 'historynum', + unique => DBIx::DBSchema::ColGroup::Unique->new( [] ), + 'index' => DBIx::DBSchema::ColGroup::Index->new( [ + @{$tableobj->unique->lol_ref}, + @{$tableobj->index->lol_ref} + ] ), + columns => [ + DBIx::DBSchema::Column->new( { + 'name' => 'historynum', + 'type' => 'serial', + 'null' => 'NOT NULL', + 'length' => '', + 'default' => '', + 'local' => '', + } ), + DBIx::DBSchema::Column->new( { + 'name' => 'history_date', + 'type' => 'int', + 'null' => 'NULL', + 'length' => '', + 'default' => '', + 'local' => '', + } ), + DBIx::DBSchema::Column->new( { + 'name' => 'history_user', + 'type' => 'varchar', + 'null' => 'NOT NULL', + 'length' => '80', + 'default' => '', + 'local' => '', + } ), + DBIx::DBSchema::Column->new( { + 'name' => 'history_action', + 'type' => 'varchar', + 'null' => 'NOT NULL', + 'length' => '80', + 'default' => '', + 'local' => '', + } ), + map { $tableobj->column($_) } $tableobj->columns + ], + } ); + foreach my $statement ( $h_tableobj->sql_create_table($dbh) ) { + $dbh->do( $statement ) + or die "CREATE error: ". $dbh->errstr. "\ndoing statement: $statement"; + } + +} + +$dbh->commit or die $dbh->errstr; +$dbh->disconnect or die $dbh->errstr; + +sub usage { + die "Usage:\n create-history-tables user\n"; +} + diff --git a/bin/fs-radius-add-check b/bin/fs-radius-add-check index 35f4d3262..4e4769e58 100755 --- a/bin/fs-radius-add-check +++ b/bin/fs-radius-add-check @@ -39,12 +39,21 @@ my($char_d) = 80; #default maxlength for text fields ### foreach my $attribute ( @attributes ) { + my $statement = "ALTER TABLE svc_acct ADD COLUMN rc_$attribute varchar($char_d) NULL"; my $sth = $dbh->prepare( $statement ) or warn "Error preparing $statement: ". $dbh->errstr; my $rc = $sth->execute or warn "Error executing $statement: ". $sth->errstr; + + $statement = + "ALTER TABLE h_svc_acct ADD COLUMN rc_$attribute varchar($char_d) NULL"; + $sth = $dbh->prepare( $statement ) + or warn "Error preparing $statement: ". $dbh->errstr; + $rc = $sth->execute + or warn "Error executing $statement: ". $sth->errstr; + } $dbh->commit or die $dbh->errstr; diff --git a/bin/fs-radius-add-reply b/bin/fs-radius-add-reply index 6b9a39e49..3de01374f 100755 --- a/bin/fs-radius-add-reply +++ b/bin/fs-radius-add-reply @@ -39,12 +39,21 @@ my($char_d) = 80; #default maxlength for text fields ### foreach my $attribute ( @attributes ) { + my $statement = "ALTER TABLE svc_acct ADD COLUMN radius_$attribute varchar($char_d) NULL"; my $sth = $dbh->prepare( $statement ) or warn "Error preparing $statement: ". $dbh->errstr; - $sth->execute + my $rc = $sth->execute or warn "Error executing $statement: ". $sth->errstr; + + $statement = + "ALTER TABLE h_svc_acct ADD COLUMN radius_$attribute varchar($char_d) NULL"; + $sth = $dbh->prepare( $statement ) + or warn "Error preparing $statement: ". $dbh->errstr; + $rc = $sth->execute + or warn "Error executing $statement: ". $sth->errstr; + } $dbh->commit or die $dbh->errstr; diff --git a/bin/fs-setup b/bin/fs-setup index 62c2cd490..7ee04a5d8 100755 --- a/bin/fs-setup +++ b/bin/fs-setup @@ -1,13 +1,13 @@ #!/usr/bin/perl -Tw # -# $Id: fs-setup,v 1.82 2002-02-27 22:39:14 ivan Exp $ +# $Id: fs-setup,v 1.83 2002-03-04 12:48:49 ivan Exp $ #to delay loading dbdef until we're ready BEGIN { $FS::Record::setup_hack = 1; } use strict; use DBI; -use DBIx::DBSchema 0.19; +use DBIx::DBSchema 0.20; use DBIx::DBSchema::Table; use DBIx::DBSchema::Column; use DBIx::DBSchema::ColGroup::Unique; @@ -160,6 +160,56 @@ foreach $attribute (@check_attributes) { # } #} +#create history tables (false laziness w/create-history-tables) +foreach my $table ( grep { ! /^h_/ } $dbdef->tables ) { + my $tableobj = $dbdef->table($table); + my $h_tableobj = DBIx::DBSchema::Table->new( { + name => "h_$table", + primary_key => 'historynum', + unique => DBIx::DBSchema::ColGroup::Unique->new( [] ), + 'index' => DBIx::DBSchema::ColGroup::Index->new( [ + @{$tableobj->unique->lol_ref}, + @{$tableobj->index->lol_ref} + ] ), + columns => [ + DBIx::DBSchema::Column->new( { + 'name' => 'historynum', + 'type' => 'serial', + 'null' => 'NOT NULL', + 'length' => '', + 'default' => '', + 'local' => '', + } ), + DBIx::DBSchema::Column->new( { + 'name' => 'history_date', + 'type' => 'int', + 'null' => 'NULL', + 'length' => '', + 'default' => '', + 'local' => '', + } ), + DBIx::DBSchema::Column->new( { + 'name' => 'history_user', + 'type' => 'varchar', + 'null' => 'NOT NULL', + 'length' => '80', + 'default' => '', + 'local' => '', + } ), + DBIx::DBSchema::Column->new( { + 'name' => 'history_action', + 'type' => 'varchar', + 'null' => 'NOT NULL', + 'length' => '80', + 'default' => '', + 'local' => '', + } ), + map { $tableobj->column($_) } $tableobj->columns + ], + } ); + $dbdef->addtable($h_tableobj); +} + #important $dbdef->save($dbdef_file); &FS::Record::reload_dbdef($dbdef_file); @@ -173,10 +223,9 @@ my($dbh)=adminsuidsetup $user; #create tables $|=1; -my @sql = $dbdef->sql($dbh); foreach my $statement ( $dbdef->sql($dbh) ) { $dbh->do( $statement ) - or die "CREATE error: ",$dbh->errstr, "\ndoing statement: $statement"; + or die "CREATE error: ". $dbh->errstr. "\ndoing statement: $statement"; } #not really sample data (and shouldn't default to US) @@ -256,7 +305,7 @@ foreach my $aref ( } - +$dbh->commit or die $dbh->errstr; $dbh->disconnect or die $dbh->errstr; print "Freeside database initialized sucessfully\n"; diff --git a/httemplate/docs/upgrade8.html b/httemplate/docs/upgrade8.html index a4fb1762f..874a9de7e 100644 --- a/httemplate/docs/upgrade8.html +++ b/httemplate/docs/upgrade8.html @@ -305,6 +305,8 @@ ALTER TABLE cust_refund DROP COLUMN crednum;
  • IMPORTANT: After applying the second set of database changes, run bin/dbdef-create again. +
  • IMPORTANT: run bin/create-history-tables +
  • IMPORTANT: After running bin/create-history-tables, run bin/dbdef-create again.
  • set the user_policy configuration value as appropriate for your site.
  • Create the `/usr/local/etc/freeside/cache.datasrc' directory (ownded by the freeside user). -- 2.11.0