Changeset 32593

Show
Ignore:
Timestamp:
09.11.2018 20:00:17 (5 weeks ago)
Author:
ak19
Message:

Now that gssql.pm has become gsmysql.pm, it now uses the presumably optimised MySQL specific statements CREATE TABLE IF NOT EXISTS and DROP TABLE IF EXISTS, as opposed to my less efficient of looping through tables in the currently loaded db to check if a table exists or not, before deciding on whether a table needs to be created/deleted.

Files:
1 modified

Legend:

Unmodified
Added
Removed
  • main/trunk/greenstone2/perllib/gsmysql.pm

    r32592 r32593  
    445445     
    446446    my $tablename = $self->get_metadata_table_name(); 
    447     if(!$self->table_exists($tablename)) { 
    448     $self->create_metadata_table() || return 0; 
    449     } else { 
    450     print STDERR "@@@ Meta table exists\n" if($self->{'verbosity'} > 2); 
    451     } 
     447    # if(!$self->table_exists($tablename)) { 
     448    #   $self->create_metadata_table() || return 0; 
     449    # } else { 
     450    #   print STDERR "@@@ Meta table exists\n" if($self->{'verbosity'} > 2); 
     451    # } 
     452    $self->create_metadata_table() || return 0; # will now only create it if it doesn't already exist 
    452453    return 1; 
    453454} 
     
    459460     
    460461    my $tablename = $self->get_fulltext_table_name();     
    461     if(!$self->table_exists($tablename)) { 
    462     $self->create_fulltext_table() || return 0; 
    463     } else { 
    464     print STDERR "@@@ Fulltxt table exists\n" if($self->{'verbosity'} > 2); 
    465     } 
     462    # if(!$self->table_exists($tablename)) { 
     463    #   $self->create_fulltext_table() || return 0; 
     464    # } else { 
     465    #   print STDERR "@@@ Fulltxt table exists\n" if($self->{'verbosity'} > 2); 
     466    # } 
     467    $self->create_fulltext_table() || return 0; # will now only create it if it doesn't already exist 
    466468    return 1; 
    467469} 
     
    478480} 
    479481 
    480  
     482## NOTE: these 2 create_table methods use mysql specific "CREATE TABLE IF NOT EXISTS" syntax 
     483## vs general SQL CREATE TABLE syntax which would produce an error message if the table 
     484## already existed 
     485## And unless do() fails, these two create methods will now always return true, 
     486## even if table existed and didn't need to be created. 
    481487sub create_metadata_table { 
    482488    my $self= shift (@_); 
     
    484490     
    485491    my $table_name = $self->get_metadata_table_name(); 
    486     print STDERR "   Creating table $table_name\n" if($self->{'verbosity'} > 1); 
     492    print STDERR "   Will create table $table_name if it doesn't exist\n" if($self->{'verbosity'} > 2); 
    487493     
    488494    # If using an auto incremented primary key: 
    489     my $stmt = "CREATE TABLE $table_name (id INT NOT NULL AUTO_INCREMENT, did VARCHAR(63) NOT NULL, sid VARCHAR(63) NOT NULL, metaname VARCHAR(127) NOT NULL, metavalue VARCHAR(1023) NOT NULL, PRIMARY KEY(id));"; 
     495    my $stmt = "CREATE TABLE IF NOT EXISTS $table_name (id INT NOT NULL AUTO_INCREMENT, did VARCHAR(63) NOT NULL, sid VARCHAR(63) NOT NULL, metaname VARCHAR(127) NOT NULL, metavalue VARCHAR(1023) NOT NULL, PRIMARY KEY(id));"; 
    490496    return $dbh->do($stmt); 
    491497} 
     
    500506     
    501507    my $table_name = $self->get_fulltext_table_name(); 
    502     print STDERR "   Creating table $table_name\n" if($self->{'verbosity'} > 1); 
     508    print STDERR "   Will create table $table_name if it doesn't exist\n" if($self->{'verbosity'} > 2); 
    503509     
    504510    # If using an auto incremented primary key: 
    505     my $stmt = "CREATE TABLE $table_name (id INT NOT NULL AUTO_INCREMENT, did VARCHAR(63) NOT NULL, sid VARCHAR(63) NOT NULL, fulltxt LONGTEXT, PRIMARY KEY(id));"; 
     511    my $stmt = "CREATE TABLE IF NOT EXISTS $table_name (id INT NOT NULL AUTO_INCREMENT, did VARCHAR(63) NOT NULL, sid VARCHAR(63) NOT NULL, fulltxt LONGTEXT, PRIMARY KEY(id));"; 
    506512    return $dbh->do($stmt); 
    507513 
    508514} 
    509515 
    510  
     516## NOTE: this method uses mysql specific "DROP TABLE IF EXISTS" syntax vs general SQL DROP TABLE 
     517## syntax which would produce an error message if the table didn't exist 
    511518sub delete_collection_tables { 
    512519    my $self= shift (@_); 
     
    514521 
    515522    # drop table <tablename> 
    516     my $table = $self->get_metadata_table_name(); 
    517     if($self->table_exists($table)) { 
    518     $dbh->do("drop table $table");# || warn("@@@ Couldn't delete $table"); 
    519     } 
     523    # my $table = $self->get_metadata_table_name(); 
     524    # if($self->table_exists($table)) { 
     525    #   $dbh->do("drop table $table"); 
     526    # } 
     527    # $table = $self->get_fulltext_table_name(); 
     528    # if($self->table_exists($table)) { 
     529    #   $dbh->do("drop table $table"); 
     530    # } 
     531    my $table = $self->get_metadata_table_name();     
     532    $dbh->do("drop table if exists $table"); 
     533 
    520534    $table = $self->get_fulltext_table_name(); 
    521     if($self->table_exists($table)) { 
    522     $dbh->do("drop table $table");# || warn("@@@ Couldn't delete $table"); 
    523     } 
    524  
    525     # + TODO Q: commit here, so that future select statements work? 
     535    $dbh->do("drop table if exists $table"); 
     536 
     537    # If prepared select statement handles already exist, would need to commit here 
     538    # so that future select statements using those prepared handles work. 
    526539    # See https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#Transactions 
    527540}