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 User Rev File contents
1 gpertea 23 #!/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 *