ViewVC Help
View File | Revision Log | Show Annotations | View Changeset | Root Listing
root/gclib/scripts/bcpin
Revision: 24
Committed: Tue Jul 26 21:46:39 2011 UTC (8 years, 1 month ago) by gpertea
File size: 5948 byte(s)
Log Message:
Line File contents
1 #!/usr/bin/perl
2 use strict;
3 use FindBin;
4 use Getopt::Std;
5 use lib $FindBin::Bin;
6 use dbSession;
7
8 my $usage=q/Usage:
9 bcpin -b <targetdb> [-T] [-I] [-c '<column_delim>'] [-r 'row_delim'] <files>..
10
11 Loads multiple bcp files into tables of database <targetdb>.
12 The first token of each name of the input <files> should correspond to
13 the exact target table name in the database <targetdb>.
14 (e.g. a filename called 'names.bcp.bz2' will be loaded in table 'names')
15
16 For file names with the extension .z, .gz, gzip, .bz2, .bzip2 or .bz, such
17 input files are decompressed first.
18
19 <targetdb> can be given in the format: <db>[@<server>][:<user>]
20 (complete login information is taken from your ~\/.db_pass
21 authentication file, use dbpass utility to maintain it)
22
23 Options (for each input file\/table):
24 -T : truncate the table before loading
25 -I : drop the indices before loading, then
26 recreate them after data have finished loading
27 -c,-r : provide custom column\/row delimiters (defaults are tab and
28 newline, respectively)
29 /;
30 my $bcpcmd=`which bcp`;chomp($bcpcmd);
31 die "Error: bcp command not found in path (using: $bcpcmd)!\n" unless -e $bcpcmd;
32 die "$usage\n" unless @ARGV;
33
34 print STDERR "Command line: $0".join(' ',@ARGV)."\n";
35
36
37 getopts('TIb:c:r:')
38 || die "$usage\nError getting options!\n";
39 my $targetdb = $Getopt::Std::opt_b || die "$usage Target database not specified!\n";
40 my ($coldelim, $rowdelim)=($Getopt::Std::opt_c, $Getopt::Std::opt_r);
41 my $coldelimflag="-t '$coldelim'" if $coldelim;
42 my $rowdelimflag="-r '$coldelim'" if $rowdelim;
43 my ($truncate, $dropidx)=($Getopt::Std::opt_T, $Getopt::Std::opt_I);
44
45 $coldelim="\t" unless $coldelim;
46 $rowdelim="\n" unless $rowdelim;
47 my @files=@ARGV;
48 my @authdta;
49 my ($server, $user, $pass, $db, @authdta)=&db_perm($targetdb, undef, 'ASK');
50 my $dbh=&db_login($server, $user, $pass, $db, @authdta);
51
52 #die "Error: dbo permissions needed for current user ($user)!\n"
53 # unless syb_dboalias($dbh);
54
55 #fetch the identity information for that annoying -E flag of bcp in
56 my %hasident;
57 print STDERR "..find $db tables with identity fields..\n";
58 my $sth=&sql_exec($dbh, q/
59 select so.name from sysobjects so,
60 syscolumns sc where sc.status & 128 = 128
61 and sc.id=so.id
62 /);
63 while (my $r=&sth_fetch($sth)) {
64 $hasident{$$r[0]}=1;
65 }
66
67 print STDERR "..start processing bcp files..\n";
68 $server=uc($1) if ($server=~m/^(\w+)\./); #NEOSYBASE case before full client upgrade
69
70 my $text_size = 9900000;
71 foreach my $file (@files) {
72 my ($tab)=($file=~/^(\w+)/);
73 my $bcplog="${tab}_bcp.log";
74 if (-s $bcplog) {
75 print STDERR " ..skipping file $file (because log $bcplog was found)\n";
76 next;
77 }
78 my $start = time;
79 my $start_str = localtime($start);
80 warn ">>Processing file $tab ...\n";
81 my $unzcmd;
82 #try to use the
83 if ($file=~/\.g?zi?p?$/i) {
84 $unzcmd='gzip -cd '.$file;
85 }
86 elsif ($file=~/\.bzi?p?2?$/i) {
87 $unzcmd='bzip2 -cd '.$file;
88 }
89 my $bcpinput=$file;
90 my ($postcmd);
91 if ($unzcmd) {
92 $bcpinput=$tab.'.unz.bcp';
93 #system("rm -f $bcpinput");
94 #system("mknod $bcpinput p") && die "Error: mknod $bcpinput p failed!\n";
95 print STDERR " ..uncompressing $file to $bcpinput..\n";
96 open(TOBCP, '>'.$bcpinput) || die "Error creating file '$bcpinput'!\n";
97 open(UNZ, $unzcmd.'|') || die "Error: failed to open '$unzcmd' pipe\n";
98 #local $/="(**)\n"; #one row at a time, not a line!
99 local $/=$rowdelim;
100 while (<UNZ>) {
101 #row processing here:
102 #print TOBCP &processRow($_);
103 #my @t=split(/\!~~\!/s);
104 my @t=split(/$coldelim/s); #if there are metachars in there, bad luck..
105 chomp($t[-1]);
106 foreach my $v (@t) {
107 if ($v=~m/^([\-\d\.]+)e\-(\d+)$/i) {
108 my ($mant, $exp)=($1,$2);
109 $v=sprintf('%.4e',$v);
110 $v='0.0' if $exp>300;
111 }
112 # -- the transform below can be commented out
113 # if you really have values when more than 2 decimals matter!
114 elsif ($v=~m/^\d{2,3}\.\d{3,}$/) { #percent value here
115 $v=sprintf('%4.2f',$v); #round to two decimals
116 #nobody cares about the other decimals
117 }
118 }
119 print TOBCP join($coldelim,@t).$rowdelim;
120 }
121 #system("$unzcmd > $bcpinput") && die "Error uncompressing $file!\n";
122 close(UNZ);
123 close(TOBCP);
124 my $duration = time - $start;
125 print STDERR " Finished unpacking - it took $duration seconds.\n"
126 }
127 # else { - we should filter uncompressed files too!
128 #
129 # }
130
131 if ($truncate) {
132 &sql_do($dbh, "truncate table $tab");
133 }
134 my @tabidx;
135 if ($dropidx) {
136 @tabidx=&syb_getIndexes($dbh, $tab, 1);
137 }
138
139 my $identflag = exists($hasident{$tab}) ? ' -E ' : '';
140
141
142
143 my $bcpjob="$bcpcmd $db..$tab in $bcpinput -m1 -c ".
144 " $coldelimflag $rowdelimflag ".
145 "-U$user -P $pass -S $server $identflag -b 10000 -T $text_size ".
146 "-e $bcplog";
147 print STDERR "..started loading $bcpinput..\n";
148 my $joboutput=`( $bcpjob ) 2>&1`;
149 my $status=$?;
150 #die "ERROR status $status at bcp from $db..$tab\n" if $status;
151 my $alloutput=$joboutput;
152 if (-s $bcplog) {
153 $alloutput.="\n ---------bcp log content ($bcplog): \n";
154 $alloutput.=`cat $bcplog`;
155 }
156 local *TOLOG;
157 open(TOLOG, '>>'.$bcplog);
158 print TOLOG "\n$joboutput\n";
159 close(TOLOG);
160 if ($dropidx) {
161 &syb_putIndexes($dbh, @tabidx);
162 }
163 $bcpjob=~s/\-P\s*\S+/\-P <yourpass>/;
164 die "Error detected at bcp-in $file file: \n$alloutput\n$bcpjob\n"
165 if $status || ($joboutput=~/error|fail|fatal|truncat|permission/i);
166 system("rm -f $bcpinput") if $unzcmd;
167 my $tot = time - $start;
168 print STDERR "<< $tab done in $tot seconds (exit code $status)\n\n";
169 }
170 &db_logout($dbh);
171 #-----------------------

Properties

Name Value
svn:executable *