ViewVC Help
View File | Revision Log | Show Annotations | View Changeset | Root Listing
root/gclib/scripts/mybcpin
Revision: 24
Committed: Tue Jul 26 21:46:39 2011 UTC (8 years, 2 months ago) by gpertea
File size: 6469 byte(s)
Log Message:
Line User Rev File contents
1 gpertea 23 #!/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     mybcpin -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 data
25     -I : disable the indices before, and
26     enable them after loading data
27     -c,-r : provide custom column\/row delimiters (defaults are tab and
28     newline, respectively)
29     /;
30     my $bcpcmd=`which mysqlimport`;chomp($bcpcmd);
31     die "Error: mysqlimport not found in path!\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="--fields-terminated-by='$coldelim'" if $coldelim;
42     my $rowdelimflag="--lines-terminated-by='$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     print STDERR "Connecting to server $server (user=$user, db=$db)\n";
51     my $dbh=&db_login($server, $user, $pass, $db, @authdta);
52    
53     #die "Error: dbo permissions needed for current user ($user)!\n"
54     # unless syb_dboalias($dbh);
55    
56     #fetch the identity information for that annoying -E flag of bcp in
57     # my %hasident;
58     # print STDERR "..find $db tables with identity fields..\n";
59     # my $sth=&sql_exec($dbh, q/
60     # select so.name from sysobjects so,
61     # syscolumns sc where sc.status & 128 = 128
62     # and sc.id=so.id
63     # /);
64     # while (my $r=&sth_fetch($sth)) {
65     # $hasident{$$r[0]}=1;
66     # }
67    
68     print STDERR "..start processing bcp files..\n";
69     #$server=uc($1) if ($server=~m/^(\w+)\./); #NEOSYBASE case before full client upgrade
70    
71     my $text_size = 9900000;
72     foreach my $file (@files) {
73     my ($tab)=($file=~/^(\w+)/);
74     my $bcplog="${tab}_bcp.log";
75     if (-s $bcplog) {
76     print STDERR ">> ..skipping file $file (because log $bcplog was found)\n\n";
77     next;
78     }
79     my $start = time;
80     my $start_str = localtime($start);
81     warn ">>Processing file $tab ...\n";
82     my $unzcmd;
83     #try to use the
84     if ($file=~/\.g?zi?p?$/i) {
85     $unzcmd='gzip -cd '.$file;
86     }
87     elsif ($file=~/\.bzi?p?2?$/i) {
88     $unzcmd='bzip2 -cd '.$file;
89     }
90     my $bcpinput=$file;
91     my ($postcmd);
92     if ($unzcmd) {
93     $bcpinput=$tab.'.unz.bcp';
94     #system("rm -f $bcpinput");
95     #system("mknod $bcpinput p") && die "Error: mknod $bcpinput p failed!\n";
96     print STDERR " ..uncompressing $file to $bcpinput..\n";
97     open(TOBCP, '>'.$bcpinput) || die "Error creating file '$bcpinput'!\n";
98     open(UNZ, $unzcmd.'|') || die "Error: failed to open '$unzcmd' pipe\n";
99     #local $/="(**)\n"; #one row at a time, not a line!
100     local $/=$rowdelim;
101     while (<UNZ>) {
102     #row processing here:
103     #print TOBCP &processRow($_);
104     #my @t=split(/\!~~\!/s);
105     my @t=split(/$coldelim/s); #if there are metachars in there, bad luck..
106     chomp($t[-1]);
107     foreach my $v (@t) {
108     if ($v=~m/^([\-\d\.]+)e\-(\d+)$/i) {
109     my ($mant, $exp)=($1,$2);
110     $v=sprintf('%.4e',$v);
111     $v='0.0' if $exp>300;
112     }
113     # -- the transform below can be commented out
114     # if you really have values when more than 2 decimals matter!
115     elsif ($v=~m/^\d{2,3}\.\d{3,}$/) { #percent value here
116     $v=sprintf('%4.2f',$v); #round to two decimals
117     #nobody cares about the other decimals
118     }
119     }
120     print TOBCP join($coldelim,@t).$rowdelim;
121     }
122     #system("$unzcmd > $bcpinput") && die "Error uncompressing $file!\n";
123     close(UNZ);
124     close(TOBCP);
125     my $duration = time - $start;
126     print STDERR " Finished unpacking - it took $duration seconds.\n"
127     }
128     # else { - we should filter uncompressed files too!
129     #
130     # }
131    
132    
133     # my $identflag = exists($hasident{$tab}) ? ' -E ' : '';
134    
135    
136     if ($truncate) {
137     &sql_do($dbh, "truncate table $tab");
138     }
139     #my @tabidx;
140     if ($dropidx) {
141     #@tabidx=&syb_getIndexes($dbh, $tab, 1);
142     &sql_do($dbh, "alter table `$tab` disable keys");
143     }
144    
145     # my $bcpjob="$bcpcmd $db..$tab in $bcpinput -m1 -c ".
146     # " $coldelimflag $rowdelimflag ".
147     # "-U$user -P $pass -S $server $identflag -b 10000 -T $text_size ".
148     # "-e $bcplog";
149    
150     my $inopts="-h $server -u $user -L --password=$pass $coldelimflag $rowdelimflag";
151     my $bcpjob="$bcpcmd $inopts $db $bcpinput";
152     print STDERR "..started loading $bcpinput..\n";
153     my $joboutput=`( $bcpjob ) 2>&1`;
154     my $status=$?;
155     #die "ERROR status $status at bcp from $db..$tab\n" if $status;
156     my $alloutput=$joboutput;
157     if (-s $bcplog) {
158     $alloutput.="\n ------error log content ($bcplog): \n";
159     $alloutput.=`cat $bcplog`;
160     }
161     local *TOLOG;
162     open(TOLOG, '>>'.$bcplog);
163     print TOLOG "\n$joboutput\n";
164     close(TOLOG);
165     if ($dropidx) {
166     # &syb_putIndexes($dbh, @tabidx);
167     &sql_do($dbh, "alter table `$tab` enable keys");
168     }
169     $bcpjob=~s/--password=\S+/--password=<yourpass>/;
170     die "Error detected at bcp-in $file file: \n$alloutput\n$bcpjob\n"
171     if $status || ($joboutput=~/error|fail|fatal|truncat|permission/i);
172     system("rm -f $bcpinput") if $unzcmd;
173     my $tot = time - $start;
174     if ($dropidx) {
175     #@tabidx=&syb_getIndexes($dbh, $tab, 1);
176     &sql_do($dbh, "alter table `$tab` enable keys");
177     }
178    
179     print STDERR "<< $tab done in $tot seconds (exit code $status)\n\n";
180    
181     }
182     &db_logout($dbh);
183     #-----------------------

Properties

Name Value
svn:executable *