Címtár feltöltése PostgreSQL adatbázisba

Térképészeti adatokat tartalmazó szöveges állomány beemelése PostgreSQL adatbázisba

#!/usr/bin/env perl
use DBI;
 
sub telep{
 # T E L E P Ü L É S E K
 my $name=shift;
 my $ksh=shift;
 my $sql="select tpid from telepulesek where name='$name'";
 my $sth=$dbh->prepare($sql);
 my $rv=$sth->execute;
 if ($@){
   die "Hiba település levállogatásánál" if ($sth->err);
 }
 my $rc=$sth->rows;
 print "[$sql]=$rc\n";
 my @row_ary=$sth->fetchrow_array;
 if (!defined $row_ary[0]){
  $sth=None;
  $sql="insert into telepulesek (name,ksh) values ('$name', $ksh)";
  my $rv=$dbh->do($sql) or die "Hiba $table levállogatásánál $dbh->errstr";
  print "[$sql]\n";
  my $sth=$dbh->prepare("select currval('seq_telepulesek')");
  $rv=$sth->execute;
  @row_ary=$sth->fetchrow_array;
 }else{
  print "telepulesek[$row_ary[0]] = $name\n";
 }
 $sth=None;
 return $row_ary[0];
}
 
sub dict{
 # D I C T
 my $table=shift;
 my $idfield=shift;
 my $name=shift;
 my $sql="select $idfield from $table where name='$name'";
 my $sth=$dbh->prepare($sql);
 my $rv=$sth->execute;
 if ($@){
   die "Hiba $table levállogatásánál" if ($sth->err);
 }
 my $rc=$sth->rows;
 print "[$sql]=$rc\n";
 my @row_ary=$sth->fetchrow_array;
 if (!defined $row_ary[0]){
  $sth=None;
  $sql="insert into $table (name) values ('$name')";
  my $rv=$dbh->do($sql) or die "Hiba $table levállogatásánál $dbh->errstr";
  print "[$sql]";
  my $sth=$dbh->prepare("select currval('seq_$table');");
  $rv=$sth->execute;
  @row_ary=$sth->fetchrow_array;
 }else{
  print "$table\[$row_ary[0]] = $name\n";
 }
 return $row_ary[0];
}
 
$dbs="dbi:Pg:dbname=cdata;host=localhost";
$dbh=DBI->connect($dbs,"","");
 
while (<STDIN>){
 @fds=$_=~/^(?:\")([0-9]*)(?:\"),(?:\")(.*?)(?:\"),(?:\")(.*?)(?:\"),(?:\")(.*?)(?:\"),(?:\")(.*?)(?:\"),(.*?),(.*?),(.*?),(.*?)$/;
 #print "$fds[0] , $fds[1], $fds[2], $fds[3], $fds[4], $fds[5], $fds[6], $fds[7], $fds[8]\n";
 $tpid=&telep($fds[1],$fds[0]);
 $ktid=&dict('kteruletek','ktid',$fds[2]);
 $ktipid=&dict('ktertip','ktipid',$fds[3]);
 $sql = "insert into cimtar (tpid,ktid,ktipid,hazszam,eov_x,eov_y) values ($tpid,$ktid,$ktipid,'$fds[4]',$fds[5],$fds[6])";
 my $rv=$dbh->do($sql) or die "Hiba $table levállogatásánál $dbh->errstr";
 print "[$sql]\n"; 
}
 
$dbh->disconnect();