refactoring of assemble_pairs

[biopieces.git] / bp_bin / write_mysql

diff --git a/bp_bin/write_mysql b/bp_bin/write_mysql
index 950a17549b7f9da414fa31eb637d598d853df922..2d0a2142f83ea0cc15f7efa38fe94f4d6f7a4280 100755 (executable)
--- a/bp_bin/write_mysql
+++ b/bp_bin/write_mysql
@@ -39,8 +39,8 @@ use Maasha::Calc;
 
 my ( $options, $user, $password, $dbh, $in, $out, $tmp_dir, $tmp_file, $fh_out, $record, $key, $type_hash, $i, @keys, $line );
 
-$user     = $ENV{ "USER" };
-$password = $ENV{ "USER" };
+$user     = Maasha::Biopieces::biopiecesrc( "MYSQL_USER" );
+$password = Maasha::Biopieces::biopiecesrc( "MYSQL_PASSWORD" );
 
 $options = Maasha::Biopieces::parse_options(
     [
@@ -50,17 +50,18 @@ $options = Maasha::Biopieces::parse_options(
         { long => 'table',            short => 't', type => 'string', mandatory => 'yes', default => undef,     allowed => undef, disallowed => undef },
         { long => 'index',            short => 'i', type => 'list',   mandatory => 'no' , default => undef,     allowed => undef, disallowed => undef },
         { long => 'database_replace', short => 'D', type => 'flag',   mandatory => 'no' , default => undef,     allowed => undef, disallowed => undef },
-        { long => 'table_replace',    short => 'T', type => 'flat',   mandatory => 'no' , default => undef,     allowed => undef, disallowed => undef },
-        { long => 'table_append',     short => 'A', type => 'flat',   mandatory => 'no' , default => undef,     allowed => undef, disallowed => undef },
+        { long => 'table_replace',    short => 'T', type => 'flag',   mandatory => 'no' , default => undef,     allowed => undef, disallowed => undef },
+        { long => 'table_append',     short => 'A', type => 'flag',   mandatory => 'no' , default => undef,     allowed => undef, disallowed => undef },
         { long => 'guess_type',       short => 'g', type => 'uint',   mandatory => 'no',  default => 1000,      allowed => undef, disallowed => 0     },
-        { long => 'no_stream',        short => 'x', type => 'flag',   mandatory => 'no',  default => $password, allowed => undef, disallowed => undef },
+        { long => 'sql',              short => 's', type => 'string', mandatory => 'no',  default => undef,     allowed => undef, disallowed => undef },
+        { long => 'no_stream',        short => 'x', type => 'flag',   mandatory => 'no',  default => undef,     allowed => undef, disallowed => undef },
     ]   
 );
 
 $in  = Maasha::Biopieces::read_stream( $options->{ "stream_in" } );
 $out = Maasha::Biopieces::write_stream( $options->{ "stream_out" } );
 
-$options->{ 'guess_type' } = 0 if $options->{ 'table_append' }; # no point in analyzing data types if appending.
+$options->{ 'guess_type' } = 0 if $options->{ 'table_append' } or $options->{ 'sql' }; # no point in analyzing data types.
 
 $tmp_dir  = Maasha::Biopieces::get_tmpdir();
 $tmp_file = "$tmp_dir/mysql.tab";
@@ -92,28 +93,43 @@ close $fh_out;
 
 if ( Maasha::SQL::database_exists( $options->{ 'database' }, $options->{ 'user' }, $options->{ 'password' } ) )
 {
-    if ( $options->{ 'database_replace' } ) {
+    if ( $options->{ 'database_replace' } )
+    {
         Maasha::SQL::delete_database( $options->{ 'database' }, $options->{ 'user' }, $options->{ 'password' } );
-    } else {
-        Maasha::Common::error( qq(Database "$options->{ 'database' }" already exists - use --database_replace to overwrite.) );
+        Maasha::SQL::create_database( $options->{ 'database' }, $options->{ 'user' }, $options->{ 'password' } );
     }
 }
-
-Maasha::SQL::create_database( $options->{ 'database' }, $options->{ 'user' }, $options->{ 'password' } );
+else
+{
+    Maasha::SQL::create_database( $options->{ 'database' }, $options->{ 'user' }, $options->{ 'password' } );
+}
 
 $dbh = Maasha::SQL::connect( $options->{ 'database' }, $options->{ 'user' }, $options->{ 'password' } );
 
-if ( $options->{ 'table_replace' } ) {
-    Maasha::SQL::delete_table( $dbh, $options->{ 'table' } ) if Maasha::SQL::table_exists( $dbh, $options->{ 'table' } );
-}
+if ( Maasha::SQL::table_exists( $dbh, $options->{ 'table' } ) )
+{
+    if ( not $options->{ 'table_replace' } and not $options->{ 'table_append' } ) {
+        Maasha::Common::error( qq(Table "$options->{ 'table' }" exists. Use --table_replace or --table_append) )
+    }
 
-if ( not $options->{ 'table_append' } ) {
+    if ( $options->{ 'table_replace' } ) {
+        Maasha::SQL::delete_table( $dbh, $options->{ 'table' } ) if Maasha::SQL::table_exists( $dbh, $options->{ 'table' } );
+    }
+
+    if ( $options->{ 'sql' } ) {
+        Maasha::SQL::request( $dbh, $options->{ 'sql' } );
+    } elsif ( not $options->{ 'table_append' } ) {
+        create_table( $dbh, $options->{ 'table' }, $type_hash, $options->{ 'index' }, $options->{ 'verbose' } );
+    }
+}
+else
+{
     create_table( $dbh, $options->{ 'table' }, $type_hash, $options->{ 'index' }, $options->{ 'verbose' } );
 }
 
 Maasha::SQL::bulk_load_file( $dbh, $tmp_file, $options->{ 'table' }, "\t" ) if Maasha::SQL::table_exists( $dbh, $options->{ 'table' } );
 
-$dbh = Maasha::SQL::disconnect( $dbh );
+Maasha::SQL::disconnect( $dbh );
 
 unlink $tmp_file;
 
@@ -202,6 +218,8 @@ sub create_table
     {
         $field = "";
 
+        $diff = $type_hash->{ $key }->{ "MAX" } - $type_hash->{ $key }->{ "MIN" };
+
         if ( $type_hash->{ $key }->{ "TYPE" } eq "INTEGER" )
         {
             $field = "$key ";
@@ -210,8 +228,6 @@ sub create_table
        #         $field .= "UNSIGNED ";
             }
 
-            $diff = $type_hash->{ $key }->{ "MAX" } - $type_hash->{ $key }->{ "MIN" };
-
             if ( $diff <= 255 ) {
                 $field .= "TINYINT";
             } elsif ( $diff <= 65535 ) {
@@ -231,7 +247,9 @@ sub create_table
         }
         else
         {
-            if ( $type_hash->{ $key }->{ "MIN" } == $type_hash->{ $key }->{ "MAX" } ) {
+            if ( $type_hash->{ $key }->{ "MAX" } > 1024 ) {
+                $field = "$key BLOB";
+            } if ( $diff == 0 ) {
                 $field = "$key CHAR($type_hash->{ $key }->{ 'MAX' })";
             } else {
                 $field = "$key VARCHAR($type_hash->{ $key }->{ 'MAX' })";
@@ -264,6 +282,7 @@ BEGIN
 
 END
 {
+    Maasha::SQL::disconnect( $dbh ) if $dbh;
     Maasha::Biopieces::status_log();
 }