Changeset 32593 for main/trunk

Timestamp:

2018-11-09T20:00:17+13:00 (5 years 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.

File:

• main/trunk/greenstone2/perllib/gsmysql.pm (modified) (6 diffs)

main/trunk/greenstone2/perllib/gsmysql.pm

@@ -445 +445 @@
     
     my $tablename = $self->get_metadata_table_name();
-    if(!$self->table_exists($tablename)) {
-    $self->create_metadata_table() || return 0;
-    } else {
-    print STDERR "@@@ Meta table exists\n" if($self->{'verbosity'} > 2);
-    }
+    # if(!$self->table_exists($tablename)) {
+    #   $self->create_metadata_table() || return 0;
+    # } else {
+    #   print STDERR "@@@ Meta table exists\n" if($self->{'verbosity'} > 2);
+    # }
+    $self->create_metadata_table() || return 0; # will now only create it if it doesn't already exist
     return 1;
 }
@@ -459 +460 @@
     
     my $tablename = $self->get_fulltext_table_name();    
-    if(!$self->table_exists($tablename)) {
-    $self->create_fulltext_table() || return 0;
-    } else {
-    print STDERR "@@@ Fulltxt table exists\n" if($self->{'verbosity'} > 2);
-    }
+    # if(!$self->table_exists($tablename)) {
+    #   $self->create_fulltext_table() || return 0;
+    # } else {
+    #   print STDERR "@@@ Fulltxt table exists\n" if($self->{'verbosity'} > 2);
+    # }
+    $self->create_fulltext_table() || return 0; # will now only create it if it doesn't already exist
     return 1;
 }
@@ -478 +480 @@
 }
 
-
+## NOTE: these 2 create_table methods use mysql specific "CREATE TABLE IF NOT EXISTS" syntax
+## vs general SQL CREATE TABLE syntax which would produce an error message if the table
+## already existed
+## And unless do() fails, these two create methods will now always return true,
+## even if table existed and didn't need to be created.
 sub create_metadata_table {
     my $self= shift (@_);
@@ -484 +490 @@
     
     my $table_name = $self->get_metadata_table_name();
-    print STDERR "   Creating table $table_name\n" if($self->{'verbosity'} > 1);
+    print STDERR "   Will create table $table_name if it doesn't exist\n" if($self->{'verbosity'} > 2);
     
     # If using an auto incremented primary key:
-    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));";
+    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));";
     return $dbh->do($stmt);
 }
@@ -500 +506 @@
     
     my $table_name = $self->get_fulltext_table_name();
-    print STDERR "   Creating table $table_name\n" if($self->{'verbosity'} > 1);
+    print STDERR "   Will create table $table_name if it doesn't exist\n" if($self->{'verbosity'} > 2);
     
     # If using an auto incremented primary key:
-    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));";
+    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));";
     return $dbh->do($stmt);
 
 }
 
-
+## NOTE: this method uses mysql specific "DROP TABLE IF EXISTS" syntax vs general SQL DROP TABLE
+## syntax which would produce an error message if the table didn't exist
 sub delete_collection_tables {
     my $self= shift (@_);
@@ -514 +521 @@
 
     # drop table <tablename>
-    my $table = $self->get_metadata_table_name();
-    if($self->table_exists($table)) {
-    $dbh->do("drop table $table");# || warn("@@@ Couldn't delete $table");
-    }
+    # my $table = $self->get_metadata_table_name();
+    # if($self->table_exists($table)) {
+    #   $dbh->do("drop table $table");
+    # }
+    # $table = $self->get_fulltext_table_name();
+    # if($self->table_exists($table)) {
+    #   $dbh->do("drop table $table");
+    # }
+    my $table = $self->get_metadata_table_name();    
+    $dbh->do("drop table if exists $table");
+
     $table = $self->get_fulltext_table_name();
-    if($self->table_exists($table)) {
-    $dbh->do("drop table $table");# || warn("@@@ Couldn't delete $table");
-    }
-
-    # + TODO Q: commit here, so that future select statements work?
+    $dbh->do("drop table if exists $table");
+
+    # If prepared select statement handles already exist, would need to commit here
+    # so that future select statements using those prepared handles work.
     # See https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#Transactions
 }