use Date::Format 'time2str';
use FS::UID qw(adminsuidsetup dbh);
use FS::cdr;
-use DBI;
+use FS::DBI;
use Getopt::Std;
use vars qw( $DEBUG );
use FS::cdr::Import;
FS::cdr::Import->dbi_import(
- 'dbd' => 'Pg', #mysql, Sybase, etc.
- 'table' => 'TABLE_NAME',
- 'primary_key' => 'BILLING_ID',
- 'status_table' = > 'STATUS_TABLE_NAME', # if using a table rather than field in main table
- 'column_map' => { #freeside => remote_db
- 'freeside_column' => 'remote_db_column',
- 'freeside_column' => sub { my $row = shift; $row->{remote_db_column}; },
+ 'dbd' => 'Pg', #mysql, Sybase, etc.
+ 'database' => 'DATABASE_NAME',
+ 'table' => 'TABLE_NAME',,
+ 'status_table' => 'STATUS_TABLE_NAME', # if using a table rather than field in main table
+ 'primary_key' => 'BILLING_ID',
+ 'primary_key_info' => 'BIGINT', # defaults to bigint
+ 'status_column' => 'STATUS_COLUMN_NAME', # defaults to freesidestatus
+ 'status_column_info' => 'varchar(32)', # defaults to varchar(32)
+ 'column_map' => { #freeside => remote_db
+ 'freeside_column' => 'remote_db_column',
+ 'freeside_column' => sub { my $row = shift; $row->{remote_db_column}; },
},
- 'batch_name' => 'batch_name', # cdr_batch name -import-date gets appended.
+ 'batch_name' => 'batch_name', # cdr_batch name -import-date gets appended.
);
=head1 DESCRIPTION
my $dbd_type = $args{'dbd'} ? $args{'dbd'} : 'Pg';
my $status_column = $args{status_column} ? $args{status_column} : 'freesidestatus';
my $status_column_info = $args{status_column_info} ? $args{status_column} : 'VARCHAR(32)';
+ my $st_sql;
+ my $batch_name = $args{batch_name} ? $args{batch_name} : 'CDR_DB';
my $queries = get_queries({
'dbd' => $dbd_type,
+ 'host' => $opt{H},
'table' => $table,
'status_column' => $status_column,
'status_column_info' => $status_column_info,
'primary_key_info' => $pkey_info,
});
- my $dsn = 'dbi:'. $dbd_type;
- $dsn .= $queries->{connect_type} . "=$opt{H}";
+ my $dsn = 'dbi:'. $dbd_type . $queries->{connect_type};
$dsn .= ";database=$database" if $database;
- my $dbi = DBI->connect($dsn, $opt{U}, $opt{P})
- or die $DBI::errstr;
+ my $dbi = FS::DBI->connect($dsn, $opt{U}, $opt{P})
+ or die $FS::DBI::errstr;
adminsuidsetup $user;
$dbi->do( $queries->{create_statustable} )
or die $dbi->errstr;
}
+ $st_sql = "INSERT INTO $status_table ( $pkey, $status_column ) VALUES ( ?, 'done' )";
}
## check for column freeside status if not using status table and create it if not there.
else {
$dbi->do( $queries->{create_statuscolumn} )
or die $dbi->errstr;
}
+ $st_sql = "UPDATE $table SET $status_column = 'done' WHERE $pkey = ?";
}
#my @cols = values %{ $args{column_map} };
$sth->execute or die $sth->errstr. " executing $sql";
my $cdr_batch = new FS::cdr_batch({
- 'cdrbatch' => $args{batch_name} . '-import-'. time2str('%Y/%m/%d-%T',time),
+ 'cdrbatch' => $batch_name . '-import-'. time2str('%Y/%m/%d-%T',time),
});
my $error = $cdr_batch->insert;
die $error if $error;
$imported++;
- my $st_sql;
- if ( $status_table ) {
-
- $st_sql =
- 'INSERT INTO '. $status_table. " ( $pkey, $status_column ) ".
- " VALUES ( ?, 'done' )";
-
- } else {
-
- $st_sql = "UPDATE $table SET $status_column = 'done' WHERE $pkey = ?";
-
- }
-
my $updated = $dbi->do($st_sql, undef, $pkey_value );
#$updates += $updated;
die "failed to set status: ".$dbi->errstr."\n" unless $updated;
}
sub cli_usage {
- #"Usage: \n $0\n\t[ -H hostname ]\n\t-D database\n\t-U user\n\t-P password\n\tfreesideuser\n";
- #"Usage: \n $0\n\t-H hostname\n\t-D database\n\t-U user\n\t-P password\n\t[ -c cdrtypenum ]\n\tfreesideuser\n";
"Usage: \n $0\n\t-H hostname\n\t[ -D database ]\n\t-U user\n\t-P password\n\t[ -c cdrtypenum ]\n\t[ -L num_cdrs_limit ]\n\t[ -T table ]\n\t[ -S status table ]\n\tfreesideuser\n";
}
sub get_queries {
- #my ($dbd, $table, $column, $column_create_info, $status_table, $primary_key, $primary_key_info) = @_;
+ #my ($dbd, $host, $table, $column, $column_create_info, $status_table, $primary_key, $primary_key_info) = @_;
my $info = shift;
- #do we want to add more types? or add as we go?
+ #get host and port information.
+ my ($host, $port) = split /:/, $info->{host};
+ $host ||= 'localhost';
+ $port ||= '5000'; # check for pg default 5000 is sybase.
+
my %dbi_connect_types = (
- 'Sybase' => ':server',
- 'Pg' => ':host',
+ 'Sybase' => ':server='.$host.';port='.$port,
+ 'Pg' => ':host='.$info->{host},
);
- #Check for freeside status table Sybase has not been tested
+ #Check for freeside status table
my %dbi_check_statustable = (
- 'Sybase' => "SELECT systables.name FROM sysobjects
- JOIN systables ON sysobjects.id = systables.id
- WHERE sysobjects.name LIKE '$info->{table}' AND systables.name = $info->{status_table}",
+ 'Sybase' => "SELECT * FROM sysobjects WHERE name = '$info->{status_table}'",
'Pg' => "SELECT * FROM information_schema.columns WHERE table_schema = 'public' AND table_name = '$info->{status_table}' AND column_name = '$info->{status_column}'",
);
- #Check for freeside status table Sybase has not been tested
+ #Create freeside status table
my %dbi_create_statustable = (
'Sybase' => "CREATE TABLE $info->{status_table} ( $info->{primary_key} $info->{primary_key_info}, $info->{status_column} $info->{status_column_info} )",
'Pg' => "CREATE TABLE $info->{status_table} ( $info->{primary_key} $info->{primary_key_info}, $info->{status_column} $info->{status_column_info} )",
);
- #Check for freeside status column Sybase has not been tested
+ #Check for freeside status column
my %dbi_check_statuscolumn = (
'Sybase' => "SELECT syscolumns.name FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
- WHERE sysobjects.name LIKE '$info->{table}' AND syscolumns.name = $info->{status_column}",
+ WHERE sysobjects.name LIKE '$info->{table}' AND syscolumns.name = '$info->{status_column}'",
'Pg' => "SELECT * FROM information_schema.columns WHERE table_schema = 'public' AND table_name = '$info->{table}' AND column_name = '$info->{status_column}' ",
);
- #Check for freeside status column Sybase has not been tested
+ #Create freeside status column
my %dbi_create_statuscolumn = (
- 'Sybase' => "ALTER TABLE $info->{table} ADD COLUMN $info->{status_column} $info->{status_column_info}",
+ 'Sybase' => "ALTER TABLE $info->{table} ADD $info->{status_column} $info->{status_column_info} NULL",
'Pg' => "ALTER TABLE $info->{table} ADD COLUMN $info->{status_column} $info->{status_column_info}",
);
=head1 BUGS
-This has only been test with Pg -> postgresql databases
+currently works with Pg(Postgresql) and Sybase(Sybase AES)
Sparse documentation.
projects / freeside.git / blobdiff