ViewVC Help
View File | Revision Log | Show Annotations | View Changeset | Root Listing
root/gclib/scripts/pullsql
Revision: 23
Committed: Tue Jul 26 21:44:38 2011 UTC (8 years, 2 months ago) by gpertea
Original Path: ann_bin/pullsql
File size: 2881 byte(s)
Log Message:
adding misc scripts

Line File contents
1 #!/usr/bin/perl
2 use strict;
3 use Getopt::Std;
4 use strict;
5 # {
6 # my $sd=$ENV{'SCRIPT_FILENAME'};
7 # push(@INC,$sd ? substr($sd, 0, rindex($sd, '/')) : '.');
8 # }
9
10 use FindBin;
11 use lib $FindBin::Bin;
12 use dbSession;
13
14 my $usage = q/
15 Extracts data from a database based on a sql query.
16 Usage:
17 pullsql [<sql_file>] -b <db> [-c <csep>] [-r <rsep>] [-FC]
18 [-a <authfile>] [-o <output_file>]
19 <sql_file> will be the query command to use; otherwise
20 the standard input is used.
21 -b <target> will select a target database, based on the global
22 authentication file. May also be <db>[@<server>][:<user>]
23 -c <csep> column separator character; default is tab (\t)
24 -r <rsep> row separator character; default is new line (\n)
25 -F indicates that the results will be FASTA formatted
26 (result columns are expected as: seq_name, sequence)
27 -C only considered for -F option, this will extract only
28 a given range from the sequence, end5 and end3
29 coordinates being expected as the third and fourth
30 result columns (seq_name, sequence, end5, end3)
31 -o send the output to <outputfile> instead of stdout
32 If no authentication information is found, the read-only 'access' account
33 will be used, using probably SYBTIGR as a server.
34 /;
35
36 my $qfile;
37 $qfile=shift if (substr($ARGV[0],0,1) ne '-');
38
39 getopts('hb:c:r:a:o:FC') || die $usage;
40
41 die $usage if ($Getopt::Std::opt_h);
42
43 my $tdb=$Getopt::Std::opt_b;
44 my $errmsg="Error: ";
45 &ErrExit($usage.$errmsg."Target database not specified!") unless $tdb;
46 my @auth = &db_perm($tdb, $Getopt::Std::opt_a);
47
48 &ErrExit($usage.$errmsg."SQL command file not found.")
49 unless (!$qfile || -e $qfile);
50 my $outfile=$Getopt::Std::opt_o;
51 if ($outfile) {
52 open(OUTFILE, '>'.$outfile) || die "Cannot create file '$outfile'!\n";
53 select(OUTFILE);
54 }
55 my $query="";
56 if ($qfile)
57 { local $/=undef;
58 open(INFILE, $qfile);
59 $query=<INFILE>; #slurp
60 close(INFILE);
61 }
62 else
63 { local $/="\n";
64 print STDERR ">Enter SQL command(s) for server $auth[0], in db $auth[3], user $auth[1]\n".
65 " (press Enter followed by Ctrl+D to send the query) :\n";
66 $query.=$_ while (<STDIN>);
67 }
68 print STDERR ("\nSending query to $auth[0]..\n");
69 my $csep=$Getopt::Std::opt_c;
70 $csep="\t" unless $csep;
71 my $rsep=$Getopt::Std::opt_r;
72 $rsep="\n" unless $rsep;
73 my $dbh=&db_login(@auth);
74 my $total;
75 if ($Getopt::Std::opt_F) {
76 if ($Getopt::Std::opt_C)
77 { $total=&sql2fasta_CLR($dbh, $query);}
78 else
79 { $total=&sql2fasta($dbh, $query);}
80 }
81 else {
82 $total=&print_sql($dbh, $query, $csep, $rsep);
83 }
84 &db_logout($dbh);
85 if ($outfile) {
86 select(STDOUT);
87 close(OUTFILE);
88 }
89 print STDERR "\nA total of $total row(s) were extracted.\n";

Properties

Name Value
svn:executable *