3 # Copyright (C) 2007-2009 Martin A. Hansen.
5 # This program is free software; you can redistribute it and/or
6 # modify it under the terms of the GNU General Public License
7 # as published by the Free Software Foundation; either version 2
8 # of the License, or (at your option) any later version.
10 # This program is distributed in the hope that it will be useful,
11 # but WITHOUT ANY WARRANTY; without even the implied warranty of
12 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 # GNU General Public License for more details.
15 # You should have received a copy of the GNU General Public License
16 # along with this program; if not, write to the Free Software
17 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
19 # http://www.gnu.org/copyleft/gpl.html
22 # >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> DESCRIPTION <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
24 # Write Biopiece records to a KISS MySQL database.
26 # >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
34 use Maasha::Biopieces;
38 # >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
41 my ( $options, $user, $password, $dbh, $in, $out, $tmp_dir, $tmp_file, $fh_out, $record, $entry );
43 $user = Maasha::Biopieces::biopiecesrc( "MYSQL_USER" );
44 $password = Maasha::Biopieces::biopiecesrc( "MYSQL_PASSWORD" );
46 $options = Maasha::Biopieces::parse_options(
48 { long => 'user', short => 'u', type => 'string', mandatory => 'no', default => $user, allowed => undef, disallowed => undef },
49 { long => 'password', short => 'p', type => 'string', mandatory => 'no', default => $password, allowed => undef, disallowed => undef },
50 { long => 'database', short => 'd', type => 'string', mandatory => 'yes', default => undef, allowed => undef, disallowed => undef },
51 { long => 'table', short => 't', type => 'string', mandatory => 'yes', default => undef, allowed => undef, disallowed => undef },
52 { long => 'table_replace', short => 'T', type => 'flag', mandatory => 'no' , default => undef, allowed => undef, disallowed => undef },
53 { long => 'table_append', short => 'A', type => 'flag', mandatory => 'no' , default => undef, allowed => undef, disallowed => undef },
54 { long => 'no_stream', short => 'x', type => 'flag', mandatory => 'no', default => undef, allowed => undef, disallowed => undef },
58 $in = Maasha::Biopieces::read_stream( $options->{ "stream_in" } );
59 $out = Maasha::Biopieces::write_stream( $options->{ "stream_out" } );
61 $tmp_dir = Maasha::Biopieces::get_tmpdir();
62 $tmp_file = "$tmp_dir/upload_to_KISS.kiss";
64 $dbh = Maasha::SQL::connect( $options->{ 'database' }, $options->{ 'user' }, $options->{ 'password' } );
66 if ( Maasha::SQL::table_exists( $dbh, $options->{ 'table' } ) )
68 if ( not $options->{ 'table_replace' } and not $options->{ 'table_append' } ) {
69 Maasha::Common::error( qq(Table "$options->{ 'table' }" exists. Use --table_replace or --table_append) )
72 if ( $options->{ 'table_replace' } ) {
73 Maasha::SQL::delete_table( $dbh, $options->{ 'table' } ) if Maasha::SQL::table_exists( $dbh, $options->{ 'table' } );
76 if ( $options->{ 'sql' } ) {
77 Maasha::SQL::request( $dbh, $options->{ 'sql' } );
78 } elsif ( not $options->{ 'table_append' } ) {
79 create_table( $dbh, $options->{ 'table' }, $options->{ 'verbose' } );
84 create_table( $dbh, $options->{ 'table' }, $options->{ 'verbose' } );
87 $fh_out = Maasha::Filesys::file_write_open( $tmp_file );
89 while ( $record = Maasha::Biopieces::get_record( $in ) )
91 if ( $entry = Maasha::KISS::IO::biopiece2kiss( $record ) )
93 map { $entry->{ $_ } = '\N' if $entry->{ $_ } eq '.' } keys %{ $entry };
95 Maasha::KISS::IO::kiss_entry_put( $entry, $fh_out );
98 Maasha::Biopieces::put_record( $record, $out ) if not $options->{ "no_stream" };
103 bulk_load_file( $dbh, $tmp_file, $options->{ 'table' }, $options->{ 'verbose' } );
105 Maasha::SQL::disconnect( $dbh );
109 Maasha::Biopieces::close_stream( $in );
110 Maasha::Biopieces::close_stream( $out );
113 # >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> SUBROUTINES <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
118 # Martin A. Hansen, October 2009
120 # Bulk load a tab separated file to MySQL while
121 # converting . to NULL.
123 my ( $dbh, # Database handle
124 $file, # File to load
126 $verbose, # Verbose flag
133 #LOAD DATA INFILE....(col1, @dummy) SET col2 = IF(@dummy = '.', NULL, @dummy)
135 $sql = qq(LOAD DATA LOCAL INFILE "$file" INTO TABLE $table);
137 print STDERR "$sql\n" if $verbose;
139 Maasha::SQL::request( $dbh, $sql );
145 # Martin A. Hansen, July 2009
147 # Create a new MySQL table.
149 my ( $dbh, # Database handle
151 $verbose, # Verbose switch
156 my ( @fields, $field_str, $sql );
159 "S_ID VARCHAR(256), INDEX S_ID_index (S_ID)",
160 "S_BEG INT, INDEX S_BEG_index (S_BEG)",
161 "S_LEN INT, INDEX S_LEN_index (S_LEN)",
162 "Q_ID VARCHAR(256), INDEX Q_ID_index (Q_ID)",
163 "SCORE FLOAT, INDEX SCORE_index (SCORE)",
164 "STRAND CHAR(1), INDEX STRAND_index (STRAND)",
165 "HITS INT, INDEX HITS_index (HITS)",
166 "ALIGN VARCHAR(256)",
167 "BLOCK_COUNT TINYINT, INDEX BLOCK_COUNT_index (BLOCK_COUNT)",
168 "BLOCK_BEGS VARCHAR(1024)",
169 "BLOCK_LENS VARCHAR(1024)",
172 $field_str = join( ", ", @fields );
174 $sql = "CREATE TABLE $table ($field_str)";
176 print STDERR "$sql\n" if $verbose;
178 Maasha::SQL::request( $dbh, $sql );
182 # >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
187 Maasha::Biopieces::status_set();
193 Maasha::SQL::disconnect( $dbh ) if $dbh;
194 Maasha::Biopieces::status_log();
198 # >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<