move to sqlite3, an issue with finalising transactions (commit, close...)

8 files changed, 99 insertions, 17 deletions

diff --git a/lib/sisu/v0/cgi_sqlite.rb b/lib/sisu/v0/cgi_sqlite.rb
index 87714f6c..35d0fbc6 100644
--- a/lib/sisu/v0/cgi_sqlite.rb
+++ b/lib/sisu/v0/cgi_sqlite.rb
@@ -211,7 +211,7 @@ module  SiSU_CGI_sqlite
     end
     def dbi_connect
       <<-'WOK_SQL'
-          @dbi="DBI:SQLite:#{db_sqlite}" #sqlite3 ?
+          @dbi="DBI:SQLite3:#{db_sqlite}" #sqlite3 ?
           @conn=DBI.connect(@dbi)
       WOK_SQL
     end
diff --git a/lib/sisu/v0/constants.rb b/lib/sisu/v0/constants.rb
new file mode 100644
index 00000000..883f1c00
--- /dev/null
+++ b/lib/sisu/v0/constants.rb
@@ -0,0 +1,66 @@
+=begin
+
+ * Name: SiSU
+
+ * Description: a framework for document structuring, publishing and search
+   #___#
+
+ * Author: Ralph Amissah
+
+ * Copyright: (C) 1997, 1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006,
+   2007,
+   2008 Ralph Amissah All Rights Reserved.
+
+ * License: GPL 3 or later:
+
+   SiSU, a framework for document structuring, publishing and search
+
+   Copyright (C) 1997, 1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006,
+   2007 Ralph Amissah
+
+   This program is free software: you can redistribute it and/or modify it
+   under the terms of the GNU General Public License as published by the Free
+   Software Foundation, either version 3 of the License, or (at your option)
+   any later version.
+
+   This program is distributed in the hope that it will be useful, but WITHOUT
+   ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
+   FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License for
+   more details.
+
+   You should have received a copy of the GNU General Public License along with
+   this program. If not, see <http://www.gnu.org/licenses/>.
+
+   If you have Internet connection, the latest version of the GPL should be
+   available at these locations:
+   <http://www.fsf.org/licensing/licenses/gpl.html>
+   <http://www.gnu.org/copyleft/gpl.html>
+
+   <http://www.jus.uio.no/sisu/gpl.fsf/toc.html>
+   <http://www.jus.uio.no/sisu/gpl.fsf/doc.html>
+   <http://www.jus.uio.no/sisu/gpl.fsf/plain.txt>
+
+ * SiSU uses:
+   * Standard SiSU markup syntax,
+   * Standard SiSU meta-markup syntax, and the
+   * Standard SiSU object citation numbering and system
+
+ * Hompages:
+   <http://www.jus.uio.no/sisu>
+   <http://www.sisudoc.org>
+
+ * Download:
+   <http://www.jus.uio.no/sisu/SiSU/download.html>
+
+ * Ralph Amissah
+   <ralph@amissah.com>
+   <ralph.amissah@gmail.com>
+
+ ** Description: system environment, resource control and configuration details
+
+=end
+
+SQL_TEST_FILE=File.new('sqlite3_insert.sql','w+')
+
+__END__
+
diff --git a/lib/sisu/v0/db_import.rb b/lib/sisu/v0/db_import.rb
index fde6b307..646ea176 100644
--- a/lib/sisu/v0/db_import.rb
+++ b/lib/sisu/v0/db_import.rb
@@ -63,6 +63,7 @@ module SiSU_DB_import
   require "#{SiSU_lib}/db_columns"
   require "#{SiSU_lib}/db_load_tuple"
   require "#{SiSU_lib}/shared_html_lite"
+  #require 'sqlite3'
   class Import < SiSU_DB_columns::Column_size
     include SiSU_Param
     include SiSU_Screen
@@ -86,11 +87,19 @@ module SiSU_DB_import
       @col[:ocn]=''
       @counter={}
       sql='SELECT MAX(lid) FROM documents'
-      @col[:lid]=@conn.execute( sql ) { |x| x.fetch_all.to_s.to_i }
-      @col[:lid] ||=0
+      begin
+        @col[:lid] ||=0
+        @col[:lid]=@conn.execute( sql ) { |x| x.fetch_all.to_s.to_i }
+      rescue
+      end
+      @col[:lid] =0 if @col[:lid].nil? or @col[:lid].to_s.empty?
       sql='SELECT MAX(nid) FROM endnotes'
-      @id_n=@conn.execute( sql ) { |x| x.fetch_all.to_s.to_i }
-      @id_n ||=0
+      begin
+        @id_n ||=0
+        @id_n=@conn.execute( sql ) { |x| x.fetch_all.to_s.to_i }
+      rescue
+      end
+      @id_n =0 if @col[:lid].nil? or @col[:lid].to_s.empty?
       @col[:lv1]=@col[:lv2]=@col[:lv3]=@col[:lv4]=@col[:lv5]=@col[:lv6]=0
       @db=SiSU_Env::Info_db.new
       @@dl ||=SiSU_Env::Info_env.new.digest.length
@@ -107,7 +116,7 @@ module SiSU_DB_import
         import_db_metadata(@dal_array)
         import_documents(@dal_array)
         import_db_urls(@dal_array,@fnm)                                #import OID on/off
-        @conn.commit                                                   #sqlite watch
+        #@conn.commit                                                   #sqlite watch
       else
         file_exist=@conn.select_one(%{ SELECT metadata.tid FROM metadata WHERE metadata.filename ~ '#{@opt.fns}'; })
         unless file_exist
@@ -152,9 +161,13 @@ module SiSU_DB_import
         special_character_escape(@tp[:title])
         @tp[:title_f],@tp[:title_i]='title, ',"'#{@tp[:title]}', "
         sql='SELECT MAX(tid) FROM metadata'
-        id_t=@conn.execute( sql ) { |x| x.fetch_all.to_s.to_i }
-        @@id_t=id_t if id_t
-        @@id_t ||=0
+        begin
+          @@id_t ||=0
+          id_t=@conn.execute( sql ) { |x| x.fetch_all.to_s.to_i }
+          @@id_t=id_t if id_t
+        rescue
+        end
+        @@id_t =0 if @col[:lid].nil? or @col[:lid].to_s.empty?
         @@id_t+=1 #bug related, needs to be performed once at start of file, but consider moving, as, placed here it means program will fail if document header lacks 0~title
         puts %{\n#{@cX.grey}Processing file number#{@cX.off}: #{@cX.green}#{@@id_t}#{@@cX.off}} unless @opt.cmd =~/q/
       end
diff --git a/lib/sisu/v0/db_indexes.rb b/lib/sisu/v0/db_indexes.rb
index ab33b450..ff7a8156 100644
--- a/lib/sisu/v0/db_indexes.rb
+++ b/lib/sisu/v0/db_indexes.rb
@@ -66,9 +66,7 @@ module SiSU_DB_index
     end
     def create_indexes                                                           # check added from pg not tested
       def base
-        print '
-          create documents common indexes
-        ' unless @opt.cmd =~/q/
+        print "\n          create documents common indexes\n" unless @opt.cmd =~/q/
         @conn.execute('
           CREATE INDEX object_nr ON documents(ocn);
           CREATE INDEX digest_clean ON documents(digest_clean);
@@ -92,9 +90,7 @@ module SiSU_DB_index
         ') unless @opt.cmd =~/q/
       end
       def text
-        print '
-          create documents text indexes
-        ' unless @opt.cmd =~/q/
+        print "\n          create documents text indexes\n" unless @opt.cmd =~/q/
         @conn.execute('
           CREATE INDEX clean ON documents(clean);
           CREATE INDEX endnote ON endnotes(clean);
diff --git a/lib/sisu/v0/db_load_tuple.rb b/lib/sisu/v0/db_load_tuple.rb
index 9098f0ae..5e80623b 100644
--- a/lib/sisu/v0/db_load_tuple.rb
+++ b/lib/sisu/v0/db_load_tuple.rb
@@ -81,10 +81,12 @@ module SiSU_DB_tuple
     def tuple                                                                    #% import line
       begin
         if @col[:en_a]
+          #SQL_TEST_FILE.puts "INSERT INTO documents (lid, metadata_tid, lev, clean, body, ocn, ocnd, ocns, seg, lev1, lev2, lev3, lev4, lev5, lev6, en_a, en_z, digest_clean, digest_all) VALUES (#{@col[:lid]}, #{@col[:tid]}, #{@col[:lev]}, '#{@col[:plaintext]}', '#{@col[:body]}', '#{@col[:ocn]}', '#{@col[:ocnd]}', '#{@col[:ocns]}', '#{@col[:seg]}', '#{@col[:lv1]}', '#{@col[:lv2]}', '#{@col[:lv3]}', '#{@col[:lv4]}', '#{@col[:lv5]}', '#{@col[:lv6]}', '#{@col[:en_a]}', '#{@col[:en_z]}', '#{@col[:digest_clean]}', '#{@col[:digest_all]}');" #if @opt.cmd =~/M/
           @conn.execute(%{
           INSERT INTO documents (lid, metadata_tid, lev, clean, body, ocn, ocnd, ocns, seg, lev1, lev2, lev3, lev4, lev5, lev6, en_a, en_z, digest_clean, digest_all) VALUES (#{@col[:lid]}, #{@col[:tid]}, #{@col[:lev]}, '#{@col[:plaintext]}', '#{@col[:body]}', '#{@col[:ocn]}', '#{@col[:ocnd]}', '#{@col[:ocns]}', '#{@col[:seg]}', '#{@col[:lv1]}', '#{@col[:lv2]}', '#{@col[:lv3]}', '#{@col[:lv4]}', '#{@col[:lv5]}', '#{@col[:lv6]}', '#{@col[:en_a]}', '#{@col[:en_z]}', '#{@col[:digest_clean]}', '#{@col[:digest_all]}');
         })
         else
+          #SQL_TEST_FILE.puts "INSERT INTO documents (lid, metadata_tid, lev, clean, body, ocn, ocnd, ocns, seg, lev1, lev2, lev3, lev4, lev5, lev6, digest_clean, digest_all) VALUES (#{@col[:lid]}, #{@col[:tid]}, #{@col[:lev]}, '#{@col[:plaintext]}', '#{@col[:body]}', '#{@col[:ocn]}', '#{@col[:ocnd]}', '#{@col[:ocns]}', '#{@col[:seg]}', '#{@col[:lv1]}', '#{@col[:lv2]}', '#{@col[:lv3]}', '#{@col[:lv4]}', '#{@col[:lv5]}', '#{@col[:lv6]}', '#{@col[:digest_clean]}', '#{@col[:digest_all]}');" #if @opt.cmd =~/M/
           @conn.execute(%{
           INSERT INTO documents (lid, metadata_tid, lev, clean, body, ocn, ocnd, ocns, seg, lev1, lev2, lev3, lev4, lev5, lev6, digest_clean, digest_all) VALUES (#{@col[:lid]}, #{@col[:tid]}, #{@col[:lev]}, '#{@col[:plaintext]}', '#{@col[:body]}', '#{@col[:ocn]}', '#{@col[:ocnd]}', '#{@col[:ocns]}', '#{@col[:seg]}', '#{@col[:lv1]}', '#{@col[:lv2]}', '#{@col[:lv3]}', '#{@col[:lv4]}', '#{@col[:lv5]}', '#{@col[:lv6]}', '#{@col[:digest_clean]}', '#{@col[:digest_all]}');
         })
@@ -114,6 +116,7 @@ module SiSU_DB_tuple
       @conn,@tp,@id=conn,tp,id
     end
     def tuple
+      #SQL_TEST_FILE.puts "INSERT INTO metadata (#{@tp[:fns_f]} #{@tp[:suffix_f]} #{@tp[:title_f]} #{@tp[:subtitle_f]} #{@tp[:creator_f]} #{@tp[:illustrator_f]} #{@tp[:translator_f]} #{@tp[:subject_f]} #{@tp[:description_f]} #{@tp[:publisher_f]} #{@tp[:contributor_f]} #{@tp[:prepared_by_f]} #{@tp[:digitized_by_f]} #{@tp[:date_f]} #{@tp[:date_created_f]} #{@tp[:date_issued_f]} #{@tp[:date_valid_f]} #{@tp[:date_available_f]} #{@tp[:date_modified_f]} #{@tp[:type_f]} #{@tp[:format_f]} #{@tp[:identifier_f]} #{@tp[:source_f]} #{@tp[:language_f]} #{@tp[:language_original_f]} #{@tp[:relation_f]} #{@tp[:coverage_f]} #{@tp[:rights_f]} #{@tp[:copyright_f]} #{@tp[:owner_f]} #{@tp[:keywords_f]} #{@tp[:abstract_f]} #{@tp[:comment_f]} #{@tp[:loc_f]} #{@tp[:dewey_f]} #{@tp[:isbn_f]} #{@tp[:pg_f]} #{@tp[:prefix_a_f]} #{@tp[:prefix_b_f]} tid) VALUES (#{@tp[:fns_i]} #{@tp[:suffix_i]} #{@tp[:title_i]} #{@tp[:subtitle_i]} #{@tp[:creator_i]} #{@tp[:illustrator_i]} #{@tp[:translator_i]} #{@tp[:subject_i]} #{@tp[:description_i]} #{@tp[:publisher_i]} #{@tp[:contributor_i]} #{@tp[:prepared_by_i]} #{@tp[:digitized_by_i]} #{@tp[:date_i]} #{@tp[:date_created_i]} #{@tp[:date_issued_i]} #{@tp[:date_valid_i]} #{@tp[:date_available_i]} #{@tp[:date_modified_i]} #{@tp[:type_i]} #{@tp[:format_i]} #{@tp[:identifier_i]} #{@tp[:source_i]} #{@tp[:language_i]} #{@tp[:language_original_i]} #{@tp[:relation_i]} #{@tp[:coverage_i]} #{@tp[:rights_i]} #{@tp[:copyright_i]} #{@tp[:owner_i]} #{@tp[:keywords_i]} #{@tp[:abstract_i]} #{@tp[:comment_i]} #{@tp[:loc_i]} #{@tp[:dewey_i]} #{@tp[:isbn_i]} #{@tp[:pg_i]} #{@tp[:prefix_a_i]} #{@tp[:prefix_b_i]} #{@id});" #if @opt.cmd =~/M/
       @conn.execute(%{
         INSERT INTO metadata (#{@tp[:fns_f]} #{@tp[:suffix_f]} #{@tp[:title_f]} #{@tp[:subtitle_f]} #{@tp[:creator_f]} #{@tp[:illustrator_f]} #{@tp[:translator_f]} #{@tp[:subject_f]} #{@tp[:description_f]} #{@tp[:publisher_f]} #{@tp[:contributor_f]} #{@tp[:prepared_by_f]} #{@tp[:digitized_by_f]} #{@tp[:date_f]} #{@tp[:date_created_f]} #{@tp[:date_issued_f]} #{@tp[:date_valid_f]} #{@tp[:date_available_f]} #{@tp[:date_modified_f]} #{@tp[:type_f]} #{@tp[:format_f]} #{@tp[:identifier_f]} #{@tp[:source_f]} #{@tp[:language_f]} #{@tp[:language_original_f]} #{@tp[:relation_f]} #{@tp[:coverage_f]} #{@tp[:rights_f]} #{@tp[:copyright_f]} #{@tp[:owner_f]} #{@tp[:keywords_f]} #{@tp[:abstract_f]} #{@tp[:comment_f]} #{@tp[:loc_f]} #{@tp[:dewey_f]} #{@tp[:isbn_f]} #{@tp[:pg_f]} #{@tp[:prefix_a_f]} #{@tp[:prefix_b_f]} tid) VALUES (#{@tp[:fns_i]} #{@tp[:suffix_i]} #{@tp[:title_i]} #{@tp[:subtitle_i]} #{@tp[:creator_i]} #{@tp[:illustrator_i]} #{@tp[:translator_i]} #{@tp[:subject_i]} #{@tp[:description_i]} #{@tp[:publisher_i]} #{@tp[:contributor_i]} #{@tp[:prepared_by_i]} #{@tp[:digitized_by_i]} #{@tp[:date_i]} #{@tp[:date_created_i]} #{@tp[:date_issued_i]} #{@tp[:date_valid_i]} #{@tp[:date_available_i]} #{@tp[:date_modified_i]} #{@tp[:type_i]} #{@tp[:format_i]} #{@tp[:identifier_i]} #{@tp[:source_i]} #{@tp[:language_i]} #{@tp[:language_original_i]} #{@tp[:relation_i]} #{@tp[:coverage_i]} #{@tp[:rights_i]} #{@tp[:copyright_i]} #{@tp[:owner_i]} #{@tp[:keywords_i]} #{@tp[:abstract_i]} #{@tp[:comment_i]} #{@tp[:loc_i]} #{@tp[:dewey_i]} #{@tp[:isbn_i]} #{@tp[:pg_i]} #{@tp[:prefix_a_i]} #{@tp[:prefix_b_i]} #{@id});
       })
@@ -124,6 +127,7 @@ module SiSU_DB_tuple
       @conn,@f,@u,@id=conn,f,u,id
     end
     def tuple
+      #SQL_TEST_FILE.puts "INSERT INTO urls (#{@f[:txt]} #{@f[:html_toc]} #{@f[:html_doc]} #{@f[:xhtml]} #{@f[:xml_sax]} #{@f[:xml_dom]} #{@f[:odf]} #{@f[:pdf_p]} #{@f[:pdf_l]} #{@f[:concordance]} #{@f[:latex_p]} #{@f[:latex_l]} #{@f[:manifest]} #{@f[:digest]} #{@f[:markup]} #{@f[:sisupod]} metadata_tid) VALUES (#{@u[:txt]} #{@u[:html_toc]} #{@u[:html_doc]} #{@u[:xhtml]} #{@u[:xml_sax]} #{@u[:xml_dom]} #{@u[:odf]} #{@u[:pdf_p]} #{@u[:pdf_l]} #{@u[:concordance]} #{@u[:latex_p]} #{@u[:latex_l]} #{@u[:manifest]} #{@u[:digest]} #{@u[:markup]} #{@u[:sisupod]} #{@id});" #if @opt.cmd =~/M/
       @conn.execute(%{
         INSERT INTO urls (#{@f[:txt]} #{@f[:html_toc]} #{@f[:html_doc]} #{@f[:xhtml]} #{@f[:xml_sax]} #{@f[:xml_dom]} #{@f[:odf]} #{@f[:pdf_p]} #{@f[:pdf_l]} #{@f[:concordance]} #{@f[:latex_p]} #{@f[:latex_l]} #{@f[:manifest]} #{@f[:digest]} #{@f[:markup]} #{@f[:sisupod]} metadata_tid) VALUES (#{@u[:txt]} #{@u[:html_toc]} #{@u[:html_doc]} #{@u[:xhtml]} #{@u[:xml_sax]} #{@u[:xml_dom]} #{@u[:odf]} #{@u[:pdf_p]} #{@u[:pdf_l]} #{@u[:concordance]} #{@u[:latex_p]} #{@u[:latex_l]} #{@u[:manifest]} #{@u[:digest]} #{@u[:markup]} #{@u[:sisupod]} #{@id});
       })
@@ -134,6 +138,7 @@ module SiSU_DB_tuple
       @conn,@en=conn,en
     end
     def tuple
+      #SQL_TEST_FILE.puts "INSERT INTO #{@en[:type]} (nid, document_lid, nr, clean, body, ocn, ocnd, ocns, metadata_tid, digest_clean) VALUES ('#{@en[:id]}', '#{@en[:lid]}', '#{@en[:nr]}', '#{@en[:txt]}', '#{@en[:body]}', '#{@en[:ocn]}', '#{@en[:ocnd]}', '#{@en[:ocns]}', '#{@en[:id_t]}', '#{@en[:hash]}');" #if @opt.cmd =~/M/
       @conn.execute(%{
         INSERT INTO #{@en[:type]} (nid, document_lid, nr, clean, body, ocn, ocnd, ocns, metadata_tid, digest_clean) VALUES ('#{@en[:id]}', '#{@en[:lid]}', '#{@en[:nr]}', '#{@en[:txt]}', '#{@en[:body]}', '#{@en[:ocn]}', '#{@en[:ocnd]}', '#{@en[:ocns]}', '#{@en[:id_t]}', '#{@en[:hash]}');
         })
diff --git a/lib/sisu/v0/dbi.rb b/lib/sisu/v0/dbi.rb
index fbe3014b..1746c71c 100644
--- a/lib/sisu/v0/dbi.rb
+++ b/lib/sisu/v0/dbi.rb
@@ -129,7 +129,8 @@ module  SiSU_DBI
       ensure
       end
       begin
-      @conn.disconnect if @sql_type =~/sqlite/
+      #@conn.disconnect if @sql_type =~/sqlite/ and @opt.mod.inspect !~/^--(?:re)?create/
+        @conn.disconnect if @sql_type =~/sqlite/
       rescue
         connect
       end
diff --git a/lib/sisu/v0/hub.rb b/lib/sisu/v0/hub.rb
index 1e051c24..5a193b0a 100644
--- a/lib/sisu/v0/hub.rb
+++ b/lib/sisu/v0/hub.rb
@@ -551,6 +551,7 @@ p "here #{__FILE__} #{__LINE__}" if @opt =~/M/
       require 'jcode'
       $KCODE='UTF8'
     end
+    require "#{SiSU_lib}/constants"
     require "#{SiSU_lib}/options"
     require "#{SiSU_lib}/sysenv"
     include SiSU_Env
diff --git a/lib/sisu/v0/sysenv.rb b/lib/sisu/v0/sysenv.rb
index 14435485..2e24954c 100644
--- a/lib/sisu/v0/sysenv.rb
+++ b/lib/sisu/v0/sysenv.rb
@@ -2685,7 +2685,7 @@ WOK
         "#{@env.path.webserv}/#@pwd_stub/sisu_sqlite.db"
       end
       def dbi
-        "DBI:SQLite:#{sqlite.db}" #sqlite3 ?
+        "DBI:SQLite3:#{sqlite.db}" #sqlite3 ?
       end
       self
     end