Changeset 24190 for main/trunk/greenstone2/perllib/dbutil/sqlite.pm

Timestamp:

2011-06-22T15:17:23+12:00 (12 years ago)

Author:

max

Message:

Improve set_infodb_entry to now use optimisations such as transactions, this is very useful when we only update one metadata entry, it is now much faster.
Add a new set of optional optimisations when writing to the database in order to get even faster I/O by using PRAGMA journal_mode (MEMORY instead of DELETE) and synchronous (OFF instead of FULL).

File:

• main/trunk/greenstone2/perllib/dbutil/sqlite.pm (modified) (10 diffs)

main/trunk/greenstone2/perllib/dbutil/sqlite.pm

@@ -29 +29 @@
 use strict;
 
+# Please set $db_fast to 1 if you wish to enable faster I/O to the database by using
+# optimisations such as PRAGMA journal_mode (MEMORY instead of DELETE) and synchronous (OFF instead of FULL)
+# Please be aware that in this case it will be less secure and the database file
+# may become corrupted if the if the operating system crashes or the computer loses
+# power before that data has been written to the disk surface.
+# But the speed gain is about 50x
+my $db_fast = 0;
+
 
 # -----------------------------------------------------------------------------
@@ -48 +56 @@
   } 
 
-  if(!open($infodb_handle, "| \"$sqlite3_exe\" \"$infodb_file_path\""))
+  # running sqlite3 with the pragma journal_mode=memory, causes sqlite to print out the 
+  # word "memory". While this is not a problem usually, in our case, this ends up going out
+  # to the web page first, as part of the web page's headers, thus ruining the web page
+  # which causes an Internal Server Error (500). Therefore, we redirect sqlite's output to
+  # the nul device instead. 
+  my $nul_device="";
+  if(defined $db_fast && $db_fast == 1) {
+    if($ENV{'GSDLOS'} =~ m/windows/) {
+        $nul_device=">NUL";
+    } else {
+        $nul_device=">/dev/null"; # linux, mac
+    }
+  }  
+  
+  if(!open($infodb_handle, "| \"$sqlite3_exe\" \"$infodb_file_path\"$nul_device"))
   {
       print STDERR "Error: Failed to open pipe to \"$sqlite3_exe\" \"$infodb_file_path\"\n";
@@ -56 +78 @@
 
   binmode($infodb_handle,":utf8");
+  
+   # Add extra optimisations, less secure but with a massive gain in performance with large databases which are often uptaded
+   # They should be set before the transaction begins
+  if (defined $db_fast && $db_fast == 1) {
+    print $infodb_handle "PRAGMA synchronous=OFF;\n";
+    print $infodb_handle "PRAGMA journal_mode=MEMORY;\n";
+  }
+  
+  # This is very important for efficiency, otherwise each command will be actioned one at a time
+  print $infodb_handle "BEGIN TRANSACTION;\n";
+  
+
 
   if (!(defined $opt_append) || ($opt_append ne "append")) {
@@ -68 +102 @@
   print $infodb_handle "CREATE INDEX IF NOT EXISTS dmd ON document_metadata(docOID);\n";
 
-  # This is very important for efficiency, otherwise each command will be actioned one at a time
-  print $infodb_handle "BEGIN TRANSACTION;\n";
-
   return $infodb_handle;
 }
@@ -79 +110 @@
   my $infodb_handle = shift(@_);
 
+  # This is crucial for efficient queries on the database!
+  print $infodb_handle "CREATE INDEX IF NOT EXISTS dme ON document_metadata(element);\n";
+  
   # Close the transaction we began after opening the file
   print $infodb_handle "END TRANSACTION;\n";
-
-  # This is crucial for efficient queries on the database!
-  print $infodb_handle "CREATE INDEX IF NOT EXISTS dme ON document_metadata(element);\n";
 
   close($infodb_handle);
@@ -201 +232 @@
   my $infodb_key = shift(@_);
   my $infodb_map = shift(@_);
+  
 
   # Add the key -> value mapping into the "data" table
@@ -211 +243 @@
     }
   }
-
+  
   my $safe_infodb_key = &sqlite_safe($infodb_key);
   print $infodb_handle "INSERT OR REPLACE INTO data (key, value) VALUES ('" . $safe_infodb_key . "', '" . &sqlite_safe($infodb_entry_value) . "');\n";
@@ -219 +251 @@
   if ($infodb_key !~ /\./ && $infodb_entry_value =~ /\<doctype\>doc\n/)
   {
+    
     print $infodb_handle "DELETE FROM document_metadata WHERE docOID='" . $safe_infodb_key . "';\n";
-
+    
     foreach my $infodb_value_key (keys(%$infodb_map))
     {
@@ -242 +275 @@
       foreach my $infodb_value (@{$infodb_map->{$infodb_value_key}})
       {
-        print $infodb_handle "INSERT INTO document_metadata (docOID, element, value) VALUES ('" . $safe_infodb_key . "', '" . &sqlite_safe($infodb_value_key) . "', '" . &sqlite_safe($infodb_value) . "');\n";
+         print $infodb_handle "INSERT INTO document_metadata (docOID, element, value) VALUES ('" . $safe_infodb_key . "', '" . &sqlite_safe($infodb_value_key) . "', '" . &sqlite_safe($infodb_value) . "');\n";
       }
     }
   }
+
+     #### DEBUGGING
+    #my $new_file = "D:\\sql.txt";
+    #open(FOUT, ">>$new_file") or die "Unable to open $new_file for writing out sql statements...ERROR: $!\n";
+    #print FOUT "BEGIN;\n".$insertStatementsBuffer."\nEND;\n";
+    #close(FOUT);
+    #print STDERR $insertStatementsBuffer; 
+     #### END DEBUGGING
 }
 
@@ -261 +302 @@
 sub set_infodb_entry
 {
-    my $infodb_file_path = shift(@_);
-    my $infodb_key = shift(@_);
-    my $infodb_map = shift(@_);
-    
-  my $sqlite3_exe = &util::filename_cat($ENV{'GSDLHOME'},"bin",$ENV{'GSDLOS'}, "sqlite3" . &util::get_os_exe());
-  my $infodb_handle = undef;
-  my $cmd = "\"$sqlite3_exe\" \"$infodb_file_path\"";
-
-  if (!-e "$sqlite3_exe" || !open($infodb_handle, "|$cmd"))
-  {
-      print STDERR "Unable to execute: $cmd\n";
-      print STDERR "$!\n";
+  my $infodb_file_path = shift(@_);
+  my $infodb_key = shift(@_);
+  my $infodb_map = shift(@_);
+  my $infodb_handle = open_infodb_write_handle($infodb_file_path, "append");
+
+  if (!defined $infodb_handle) {
+      print STDERR "Error: Failed to open infodb write handle\n";
       return -1;
   }
   else {
-
-      binmode($infodb_handle, ":utf8");
       write_infodb_entry($infodb_handle,$infodb_key,$infodb_map);
-      close($infodb_handle);
-  }
-
+      close_infodb_write_handle($infodb_handle);
+  }
   # Not currently checking for errors on write to DB
   return 0;
-  
-  
-    }
-    
+
+}