Щось корисне про IT

postgres=# CREATE USER ast_user with password 'xxxxxxx';
CREATE ROLE
postgres=# CREATE DATABASE ast_db with owner ast_user;
CREATE DATABASE
postgres=#exit
 
psql ast_db ast_user

ast_db=> CREATE TABLE cdr (
calldate TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
clid CHARACTER VARYING(80) DEFAULT '' NOT NULL,
src CHARACTER VARYING(80) DEFAULT '' NOT NULL,
dst CHARACTER VARYING(80) DEFAULT '' NOT NULL,
dcontext CHARACTER VARYING(80) DEFAULT '' NOT NULL,
channel CHARACTER VARYING(80) DEFAULT '' NOT NULL,
dstchannel CHARACTER VARYING(80) DEFAULT '' NOT NULL,
lastapp CHARACTER VARYING(80) DEFAULT '' NOT NULL,
lastdata CHARACTER VARYING(80) DEFAULT '' NOT NULL,
duration BIGINT DEFAULT 0::BIGINT NOT NULL,
billsec BIGINT DEFAULT 0::BIGINT NOT NULL,
disposition CHARACTER VARYING(45) DEFAULT '' NOT NULL,
amaflags BIGINT DEFAULT 0::BIGINT NOT NULL,
accountcode CHARACTER VARYING(20) DEFAULT '' NOT NULL,
uniqueid CHARACTER VARYING(32) DEFAULT '' NOT NULL,
userfield CHARACTER VARYING(255) DEFAULT '' NOT NULL
);
CREATE TABLE

perl -e shell -MCPAN
install DBI::DBD
install DBD::Pg

cat import.pl
 
#!/usr/bin/perl -w
use strict;
use DBI; 
 
# this script import asterisk cdr log Master.csv into a postgresql table
 
my $cdr_log_file = $ARGV[0]; 
my $pg_host = $ARGV[1]; 
my $pg_db = $ARGV[2]; 
my $pg_table = $ARGV[3]; 
my $pg_user = $ARGV[4]; 
my $pg_pwd = $ARGV[5]; 
 
 
&dbformat if ($ARGV[0] eq "database_format"); 
&usage if (!$ARGV[5]); 
 
 
# Connect to database 
print "Connecting to database...\n\n"; 
 
my $dsn="DBI:Pg:dbname=$pg_db;host=$pg_host;port=5432";
my $dbh=DBI->connect($dsn,$pg_user,$pg_pwd);
 
if ($dbh) {
	print "Successfully connected to $dsn";
	open cdr_log, "<$cdr_log_file" or die "Cannot open cdr_log_file\n";
	while (<cdr_log>) { 
		$_ =~ s/\n//iog; 
		$_ =~ s/\"//iog; 
		my (@fields) = split(/\,/, $_); 
		my $insert_str = "insert into $pg_table (src, dst, dcontext, clid, channel, dstchannel, lastapp, lastdata, calldate, duration, billsec, disposition, amaflags) values (\'".$fields[1]."\', \'".$fields[2]."\', \'".$fields[3]."\', \'".$fields[4]."\', \'".$fields[5]."\', \'".$fields[6]."\', \'".$fields[7]."\', \'".$fields[8]."\', \'".$fields[9]."\', \'".$fields[12]."\', \'".$fields[13]."\', \'".$fields[14]."\', \'3\');\n";
		print $insert_str."\n";
		my $sth = $dbh->prepare($insert_str); 
		$sth->execute(); 
		$sth->finish(); 
	} 
	$dbh->disconnect(); 
	close (cdr_log); 
}
else{
	die("Problem connecting to : $dsn\n");
}
 
print "\n\nEnd.\n"; 
exit; 
 
 
sub usage() { 
       print_header(); 
       print "\nUsage: perl import.pl <cdr_log_file> <pg_hostname> <database> <table> <username> <password>"; 
       print "\n\nTo see the expected database format run perl import.pl database_format\n\nEnd.\n"; 
       die; 
}; 
 
sub dbformat() { 
       print_header(); 
       print "\nimport.pl expects a table containing the following fields:\n
    calldate timestamp with time zone DEFAULT now() NOT NULL,
    clid character varying(80) DEFAULT ''::character varying NOT NULL,
    src character varying(80) DEFAULT ''::character varying NOT NULL,
    dst character varying(80) DEFAULT ''::character varying NOT NULL,
    dcontext character varying(80) DEFAULT ''::character varying NOT NULL,
    channel character varying(80) DEFAULT ''::character varying NOT NULL,
    dstchannel character varying(80) DEFAULT ''::character varying NOT NULL,
    lastapp character varying(80) DEFAULT ''::character varying NOT NULL,
    lastdata character varying(80) DEFAULT ''::character varying NOT NULL,
    duration bigint DEFAULT (0)::bigint NOT NULL,
    billsec bigint DEFAULT (0)::bigint NOT NULL,
    disposition character varying(45) DEFAULT ''::character varying NOT NULL,
    amaflags bigint DEFAULT (0)::bigint NOT NULL,
    accountcode character varying(20) DEFAULT ''::character varying NOT NULL,
    userfield character varying(255) DEFAULT ''::character varying NOT NUL
"; 
       die; 
}; 
 
sub print_header() { 
       print "\nimport.pl - Load Asterisk CDR datas to PGSQL database\n"; 
       print "written by Stéphane HENRY stephane.henry (=at=) heberge.net - 2007-03-20\n"; 
}

import.pl <cdr_log_file> <pg_hostname> <database> <table> <username> <password>

./import.pl Master.csv 127.0.0.1 asterdb cdr asterisk asterpass