----------
X-Sun-Data-Type: text
X-Sun-Data-Description: text
X-Sun-Data-Name: text
X-Sun-Charset: iso-8859-1
X-Sun-Content-Lines: 19
Si sigurna da je problem v Perlu? Jaz delam z Oraclom iz PHPja in dela
ok... ko se ga navadis in spoznas njegove finte, seveda. Poslji
strukturo tabele in stavek, ki ga uporabljas za insert.
Matjaz /kesl
V prilogi pošiljam dva dokumenta. Zaradi poenostavitve povem,
da je v LJ.sql opisana baza, definirane so tabele, indexi ...
l1_ANIMAL pa je perlov program za loadanje podatkov.
Dodajam tudi odgovor za Simona Golicnika!
Odgovor je z veseljem.
Lep pozdrav,
Sandra.
----------
X-Sun-Data-Type: default-app
X-Sun-Data-Description: default
X-Sun-Data-Name: l1_ANIMAL
X-Sun-Charset: iso-8859-1
X-Sun-Content-Lines: 326
#!/usr/bin/perl -w
##############################################################################
# Loading basic identification data from table $src_table into table
# $dest_table. Here: load into entry and transfer.
##############################################################################
use strict;
use vars qw/ $dbh %id_hash $cull_cause $cull_dt $transfer_dt $unit $origin $action
$birth_dt $owner $dat %store_datum $ext_id @line $entry_dt $decision_dt
$concat $status $db_user $db_passwd $sec_id $sex $breed $sire $dam
$ext_id $db_id $last_action /;
my $unita = shift;
my $src_table = "irena.raw_animal_$unita";
my $dest_table = 'entry';
my $dest_table_2 = 'transfer';
my $dest_table_3 = 'animal';
use DBI;
$| = 1;
$db_user = 'lj';
$db_passwd = 'jklm';
my $today = GetToday();
use Env qw( PDBL_HOME );
#use Env qw( PDBL_LOCAL );
use lib "$PDBL_HOME" ;
use lib "$PDBL_HOME/lib";
use lib "$PDBL_HOME/baza";
#use lib "$PDBL_LOCAL/model";
require "pdblrc";
require "LJ.model";
$dbh = DBI->connect("dbi:Oracle:Z",$db_user,$db_passwd)
or die $DBI::errstr;
use pdbl_lib; # standard pdbl lib
use DataBase; # database routines
use Load; # some aux routines for loading
#use Rules;
#use FormatDateSS;
#ConnectDB() unless defined $dbh;
###### $id_hash{ $ext_id.$unit } = $db_id
Get_ID_Hash( \%id_hash );
#####################################################################
#### set up handles:
# $sth1 -- read RAW_TABLE
# $sth_ins_entry -- insert into ENTRY
#####################################################################
# get ID data from src_table:
my $sth1 = $dbh->prepare(qq{
SELECT id,sex,breed,sec_id,owner,origin,sire,dam,birth_dt,cull_dt,cull_cause
FROM $src_table
}) or die $dbh->errstr;
# id,sex,breed,sec_id,owner,origin,sire,dam,birth_dt,cull_dt,cull_cause
####### INSERT into ENTRY
my $sth_ins_entry = $dbh->prepare(qq{
INSERT INTO $dest_table
(ext_id,db_id,unit)
VALUES (? , seq_entry_db_id.nextval, ? )
}) or die $dbh->errstr;
#
# (ext_id,db_id,unit)
##################################################################
## end handles
##################################################################
### print "Reading data from $_ ...\n";
$sth1->execute();
my %store;
my $i = 0;
my $a = 0; # animals
my $dups = 0; # duplicate records
my $line_ref;
###print "Processing data ...\n";
while ( $line_ref = $sth1->fetch ) { # load line by line
my @line = @$line_ref;
### print "@line \n";
foreach ( @line ) {
$_ =~ s/^\s*//; # remove leading ...
$_ =~ s/\s*$//; # ... and trailing whitespaces
}
my ($ext_id,$sex,$breed,$sec_id,$owner,$origin,$sire,$dam,$birth_dt,$cull_dt,$cull_cause);
$ext_id=$sex=$breed=$sec_id=$owner=$origin=$sire=$dam=$birth_dt=$cull_dt=$cull_cause = '';
my ($ext_id,$sex,$breed,$sec_id,$owner,$origin,$sire,$dam,$birth_dt,$cull_dt,$cull_cause
) = @line;
unless ( exists $id_hash{$ext_id . $owner} ) {
if ( exists $store{$ext_id} and $store{$ext_id} != 0 ) { # duplicate records!!!
print "Duplicate Record --> sex: $sex, society: $origin, hb_nr: $ext_id\n";
$dups++;
} else {
$store{$ext_id} = $owner;
$a++;
}
# }
}
# show the progress:
print '.' unless ++$i%100;
print " --> $i\n" unless $i%1000;
}
print " --> $i total\n";
print "(new records: $a animals ($dups duplicates))\n";
##########################################################
############ now all EXT_IDs and UNITs have been created
##########################################################
$i = 0;
print "Processing data and inserting into table $dest_table ...\n";
foreach ( keys %store ) {
$sth_ins_entry->execute( $_, $store{$_} );
# $sth_ins_entry->execute( $_ );
print '*' unless ++$i%100;
print " --> $i\n" unless $i%1000;
}
print "\n$i records inserted into table $dest_table.\n";
$sth1->finish;
##########################################################
###### ENTRY has been populated
##########################################################
##########################################################
##########################################################
############ now all EXT_IDs and UNITs have been created
##########################################################
print "Processing data and inserting into table $dest_table_2...\n";
#TRANSFER:
%id_hash = ();
Get_ID_Hash( \%id_hash );
my $sth2 = $dbh->prepare(qq{
SELECT id,owner,birth_dt,status,location,entry_dt,exit_dt,cull_dt,cull_cause,sale_dt,sale_wt,sale_val,
buyer,sale_cat
FROM $src_table
}) or die $dbh->errstr;
my $sth_ins_trans = $dbh->prepare(qq{
INSERT INTO $dest_table_2
(ext_id, db_id, owner, status, action, entry_dt, decision_dt,transfer_dt, verific_dt,buyer)
VALUES (?, ?, ?, ?, ? , ?, Null, ?, Null, Null)
}) or die $dbh->errstr;
print "Reading data from $src_table again ...\n";
$sth2->execute();
%store = ();
$i = 0;
$a = 0;
$dups = 0;
while ( $line_ref = $sth2->fetch ) { # load line by line
my @line = @$line_ref;
foreach ( @line ) {
$_ =~ s/^\s*//; # remove leading ...
$_ =~ s/\s*$//; # ... and trailing whitespaces
}
my ( $ext_id,$owner,$birth_dt,$status,$location,$entry_dt,$exit_dt,$cull_dt,$cull_cause,$sale_dt,$sale_wt,$sale_val,
$buyer,$sale_cat
) = @line;
#if (exists($id_hash{$ext_id.$owner})){
# print "obstajam $id_hash{$ext_id.$owner}XXXXX $cull_dt\n";} else {
# print "NE obstajam \n"; }
#print "Reading data from $cull_dt, $ext_id again ...\n";
if ($entry_dt eq '' ) {
$entry_dt='27.12.51' ; }
if ($cull_dt eq '' ) {
$cull_dt='27.12.51' ; }
# print "Reading data from $cull_dt,$action, $ext_id again ...\n";
if ($cull_dt eq '27.12.51') {$action = 'INITIAL-HERD'};
if ($cull_dt ne '27.12.51') {
if ($cull_cause eq '98') {$action = 'SA'; $unit=$origin}
elsif ($cull_cause eq '55') {$action = 'SA'; $unit=$origin}
elsif ($cull_cause eq '40') {$action = 'UC'}
else {$action = 'EX'}; }
# if ($birth_dt eq '27.12.51'){
# if ($origin eq '9') {$action = 'IP'} # animal appears on pedigre of imported animals or semen
# else {$action = 'UC'}; }
#print "Reading data from $cull_dt,$action, $ext_id,$id_hash{$ext_id.$owner},$owner,$status,$action,$entry_dt,$cull_dt again ...\n";
#if (exists($id_hash{$ext_id.$owner})){
# print "obstajam $ext_id XXXX $id_hash{$ext_id.$owner}XXXXX $cull_dt\n";} else {
# print "NE obstajam \n"; }
if ($cull_cause eq '' or $cull_cause eq '55' or $cull_cause eq '98' ){
$status='A'} else {
$status='H'};
##### insert into TRANSFER for each entry in ENTRY
if (exists($id_hash{$ext_id.$owner})){
$sth_ins_trans->execute($ext_id ,$id_hash{$ext_id.$owner},$owner,$status,$action,"$entry_dt","$cull_dt");
print '*' unless ++$i%100;
print " --> $i\n" unless $i%1000; }
}
print "\n$i records inserted into table $dest_table_2.\n";
$sth2->finish;
##########################################################
###### TRANSFER has been populated
##########################################################
##########################################################
############ now all EXT_IDs and UNITs have been created
##########################################################
print "Processing data and inserting into table $dest_table_2...\n";
#TRANSFER:
%id_hash = ();
Get_ID_Hash( \%id_hash );
my $sth3 = $dbh->prepare(qq{
SELECT id,sex,breed,sec_id,owner,origin,sire,dam,birth_dt,cull_dt,cull_cause
FROM $src_table
}) or die $dbh->errstr;
my $sth_ins_animal = $dbh->prepare(qq{
INSERT INTO $dest_table_3
(ext_id, db_id, sex, breed_id, sec_id, birth_dt, sire,dam,origin,status,cull_cause,last_action,last_action_dt)
VALUES (?, ?, ?, ? , ? ,?, ? , ? ,? , Null, ?, ?, ? )
}) or die $dbh->errstr;
print "Reading data from $src_table again ...\n";
$sth3->execute();
%store = ();
$i = 0;
$a = 0;
$dups = 0;
$ext_id = undef if $ext_id eq '';
$db_id = undef if $db_id eq '';
$sec_id = undef if $sec_id eq '';
$sex = undef if $sex eq '';
$breed = undef if $breed eq '';
$owner= undef if $owner eq '';
$origin = undef if $origin eq '';
$sire= undef if $sire eq '';
$dam= undef if $dam eq '';
$birth_dt= undef if $birth_dt eq '';
$cull_dt = undef if $cull_dt eq '';
$cull_cause = undef if $cull_cause eq '';
$last_action = undef if $last_action eq '';
while ( $line_ref = $sth3->fetch ) { # load line by line
my @line = @$line_ref;
### print " XXX @line \n";
foreach ( @line ) {
$_ =~ s/^\s*//; # remove leading ...
$_ =~ s/\s*$//; # ... and trailing whitespaces
}
my ( $ext_id,$sex,$breed,$sec_id,$owner,$origin,$sire,$dam,$birth_dt,$cull_dt,$cull_cause
) = @line;
#if (exists($id_hash{$ext_id.$owner})){
# print "obstajam $id_hash{$ext_id.$owner}XXXXX $cull_dt\n";} else {
# print "NE obstajam \n"; }
my $last_action='$last_action';
if ($cull_dt eq '' ) {
$cull_dt="27.12.51" ; }
if ($birth_dt eq '' ) {
$birth_dt="27.12.51" ; }
if ($cull_dt ne '27.12.51') {
$last_action="CU" }
else {$last_action='';}
#if ($origin eq '') { #ne dela
# $origin='';}
#if ($cull_cause eq '') { #ne dela
# $cull_cause='';}
#my $last_action='$last_action';
#my $breed_id='$breed';
# print "XXXXXXXXX $origin XXXXXXXXXXX \n" ;
# my $concat = "$ext_id.$sex";
# if ($cull_cause eq '' or $cull_cause eq '55' or $cull_cause eq '98' ){
# $status='A'} else {
# $status='H'};
# print "To so vzroki izločitev $cull_cause,$action \n;"
# unless ( exists $id_hash{$ext_id . $owner} ) {
# if ( exists $store{$ext_id} and $store{$ext_id} != 0 ) { # duplicate records!!!
# print "Duplicate Record --> $ext_id\n";
# $dups++;
# } else {
# $store{$ext_id} = $owner;
# $a++;
# }
# }
##### insert into ANIMAL for each entry in ENTRY
if (exists($id_hash{$ext_id.$owner})){
$sth_ins_animal->execute($ext_id ,$id_hash{$ext_id.$owner},$sex,$breed,$sec_id,"$birth_dt",$sire,$dam,$origin,$cull_cause,$last_action,"$cull_dt");
print '*' unless ++$i%100;
print " --> $i\n" unless $i%1000; }
}
print "\n$i records inserted into table $dest_table_3.\n";
$sth3->finish;
DisconnectDB();
print "... done.\n";
----------
X-Sun-Data-Type: default
X-Sun-Data-Description: default
X-Sun-Data-Name: LJ.sql
X-Sun-Charset: us-ascii
X-Sun-Content-Lines: 157
DROP TABLE entry;
CREATE TABLE entry (
ext_id varchar2(13), --External Identification of the animal
db_id number, -- Internal database ID
unit number(5) -- reporting unit
);
DROP INDEX uidx_entry_1;
CREATE UNIQUE INDEX uidx_entry_1 ON entry ( ext_id, unit );
DROP SEQUENCE seq_entry_db_id;
CREATE SEQUENCE seq_entry_db_id;
DROP TABLE transfer;
CREATE TABLE transfer (
ext_id varchar2(13),
db_id number,
owner number(5),
status varchar2(2),
action varchar2(15),
entry_dt date,
decision_dt date,
transfer_dt date,
verific_dt date,
buyer varchar2(5),
coment varchar2(20) );
DROP INDEX uidx_transfer_1;
CREATE UNIQUE INDEX uidx_transfer_1 ON transfer ( ext_id, owner, entry_dt );
DROP TABLE animal;
CREATE TABLE animal (
ext_id varchar2(13),
db_id number,
sex varchar2(1),
breed_id varchar(4),
sec_id varchar2(9),
birth_dt date,
sire varchar2(13),
dam varchar2(13),
origin number(5),
status varchar2(2),
cull_cause number(2),
last_action varchar2(5),
last_action_dt date,
coment varchar2(20)
);
DROP INDEX uidx_animal_1;
CREATE UNIQUE INDEX uidx_animal_1 ON animal ( db_id );
DROP TABLE service;
CREATE TABLE service (
sowid varchar2(13),
db_id number,
boarid varchar2(13),
service_dt date,
parity number(2),
service number(2),
behav varchar2(2),
appl varchar2(2),
tech1 varchar2(4),
tech2 varchar2(4),
outcome varchar2(1),
DPI number
);
DROP INDEX uidx_service_1;
CREATE UNIQUE INDEX uidx_service_1 ON service ( sowid, service_dt );
DROP TABLE litter;
CREATE TABLE litter (
sowid varchar2(13),
db_id number,
parity number(2),
farrow_dt date,
liveborn number(2),
stillborn number(2),
mummies number(2),
tatoo_dt date,
notch_n number(3),
litter_wt number(2),
weaning_dt date,
weaned number(2),
weaning_wt number(3)
);
DROP TABLE weaning2;
CREATE TABLE weaning2 (
sowid varchar2(13),
parity number(2),
weaning2_dt date,
weaned number(2),
weaning_wt number
);
DROP TABLE location;
CREATE TABLE location (
db_id varchar2(13),
relocation_dt date,
barn varchar2(2),
box varchar2(2)
);
DROP TABLE slaughter;
CREATE TABLE slaughter (
sl_house number,
sl_dt date,
sl_hour float8,
supplier number(),
number number(),
traitM number(),
traitS number(),
wt_warm float8,
category varchar2(),
lean_meat float8, t
EUROP varchar2(),
treatment varchar2(),
INSPECT number,
code number,
id varchar2(),
comment varchar2()
);
DROP INDEX uidx_slaughter_1;
CREATE UNIQUE INDEX uidx_slaughter_1 ON slaughter ( sl_house, sl_dt, sl_hour, supplier, number, traitM, traitS );
DROP TABLE suppliers;
CREATE TABLE suppliers (
code number,
supplier1 number,
supplier2 number,
id varchar2(),
date date
);
DROP INDEX uidx_suppliers_1;
CREATE UNIQUE INDEX uidx_suppliers_1 ON suppliers ( id );
DROP TABLE address;
CREATE TABLE address (
id number,
name varchar2(50),
street varchar2(60),
city varchar2(60),
zip_code number,
status number,
region number,
phone varchar2(13),
fax varchar2(13),
email varchar2(40)
);
DROP INDEX uidx_address_1;
CREATE UNIQUE INDEX uidx_address_1 ON address ( id );
DROP TABLE breed;
CREATE TABLE breed (
breed_id number,
short_name varchar2(4),
long_name varchar2(20),
en_name varchar2(20)
);
DROP INDEX uidx_breed_1;
CREATE UNIQUE INDEX uidx_breed_1 ON breed ( breed_id );
