From adf6979cf0f222b6d8ad419f798fb82dbe4b99bb Mon Sep 17 00:00:00 2001 From: martinahansen Date: Tue, 28 Jul 2009 17:31:55 +0000 Subject: [PATCH] finished write_mysql git-svn-id: http://biopieces.googlecode.com/svn/trunk@607 74ccb610-7750-0410-82ae-013aeee3265d --- bp_bin/write_mysql | 51 ++++++++++++++++++++++++++--------------- code_perl/Maasha/SQL.pm | 1 + 2 files changed, 34 insertions(+), 18 deletions(-) diff --git a/bp_bin/write_mysql b/bp_bin/write_mysql index 950a175..1a783bf 100755 --- a/bp_bin/write_mysql +++ b/bp_bin/write_mysql @@ -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,39 @@ 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 ( $options->{ 'table_replace' } ) { + Maasha::SQL::delete_table( $dbh, $options->{ 'table' } ) if Maasha::SQL::table_exists( $dbh, $options->{ 'table' } ); + } -if ( not $options->{ 'table_append' } ) { - create_table( $dbh, $options->{ 'table' }, $type_hash, $options->{ 'index' }, $options->{ 'verbose' } ); + 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' } ); + } } 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 +214,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 +224,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 +243,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 +278,7 @@ BEGIN END { + Maasha::SQL::disconnect( $dbh ) if $dbh; Maasha::Biopieces::status_log(); } diff --git a/code_perl/Maasha/SQL.pm b/code_perl/Maasha/SQL.pm index 3f019f1..be60999 100644 --- a/code_perl/Maasha/SQL.pm +++ b/code_perl/Maasha/SQL.pm @@ -67,6 +67,7 @@ sub delete_database $password, ) = @_; + die qq(ERROR: Protected database: "$database!\n" ) if $database =~/^(mysql|information_schema)$/i; system( "mysqladmin drop $database --force --user=$user --password=$password > /dev/null 2>&1" ) == 0 or die qq(ERROR: Could not drop database "$database"!\n); -- 2.39.5