some re-arrangement related to sqlite3 testing (including a rudimentary sql import file, dbi by-pass)

8 files changed, 139 insertions, 93 deletions

diff --git a/lib/sisu/v0/db_create.rb b/lib/sisu/v0/db_create.rb
index bf622bef..95803324 100644
--- a/lib/sisu/v0/db_create.rb
+++ b/lib/sisu/v0/db_create.rb
@@ -64,8 +64,8 @@ module SiSU_DB_create
   class Create < SiSU_DB_columns::Column_size
     require "#{SiSU_lib}/sysenv"
     @@dl=nil
-    def initialize(opt,conn='',sql_type='pg')
-      @opt,@conn,@sql_type=opt,conn,sql_type
+    def initialize(opt,conn,file,sql_type='pg')
+      @opt,@conn,@file,@sql_type=opt,conn,file,sql_type
       @cX=SiSU_Screen::Ansi.new(@opt.cmd).cX
       @comment=Comment.new(@sql_type)
       @@dl ||=SiSU_Env::Info_env.new.digest.length
@@ -86,7 +86,9 @@ module SiSU_DB_create
     end
     def output_dir?
       dir=SiSU_Env::Info_env.new('')
-      if @opt.cmd =~/d/; dir.path.webserv_stub_ensure
+      if @opt.cmd =~/d/
+        #p dir.path.webserv_stub_ensure.inspect
+        dir.path.webserv_stub_ensure
       end
     end
     def create_table
diff --git a/lib/sisu/v0/db_drop.rb b/lib/sisu/v0/db_drop.rb
index fc7b7b61..bd5e647f 100644
--- a/lib/sisu/v0/db_drop.rb
+++ b/lib/sisu/v0/db_drop.rb
@@ -61,8 +61,8 @@
 =end
 module SiSU_DB_drop
   class Drop
-    def initialize(opt,conn='',sql_type='')
-      @opt,@conn,@sql_type=opt,conn,sql_type
+    def initialize(opt,conn,file,sql_type='')
+      @opt,@conn,@file,@sql_type=opt,conn,file,sql_type
     end
     def drop
       def tables                                                              #% drop all tables
diff --git a/lib/sisu/v0/db_import.rb b/lib/sisu/v0/db_import.rb
index 646ea176..cb68ffd6 100644
--- a/lib/sisu/v0/db_import.rb
+++ b/lib/sisu/v0/db_import.rb
@@ -70,8 +70,8 @@ module SiSU_DB_import
     @@dl=nil
     @@hname=nil
     attr_accessor :tp
-    def initialize(opt,conn='',sql_type='pg')
-      @opt,@conn,@sql_type=opt,conn,sql_type
+    def initialize(opt,conn,file,sql_type='pg')
+      @opt,@conn,@file,@sql_type=opt,conn,file,sql_type
       @cX=SiSU_Screen::Ansi.new(@opt.cmd).cX
       @env=SiSU_Env::Info_env.new(@opt.fns)
       @dal="#{@env.path.dal}"
@@ -113,17 +113,22 @@ module SiSU_DB_import
       tell.print_grey if @opt.cmd =~/v/
       case @sql_type
       when /sqlite/                                                    #fix logic for sqlite !
-        import_db_metadata(@dal_array)
-        import_documents(@dal_array)
-        import_db_urls(@dal_array,@fnm)                                #import OID on/off
-        #@conn.commit                                                   #sqlite watch
+        #pf_db_import_transaction_open if @opt =~/M/
+        db_import_metadata
+        db_import_documents(@dal_array)
+        db_import_urls(@dal_array,@fnm)                                #import OID on/off
+        #pf_db_import_transaction_close if @opt =~/M/
+        #@conn.commit
+        #@conn.close
+        #@conn.disconnect
+#sqlite watch
       else
         file_exist=@conn.select_one(%{ SELECT metadata.tid FROM metadata WHERE metadata.filename ~ '#{@opt.fns}'; })
         unless file_exist
           @conn.execute('BEGIN')
-          import_db_metadata(@dal_array)
-          import_documents(@dal_array)
-          import_db_urls(@dal_array,@fnm)                              #import OID on/off
+          db_import_metadata
+          db_import_documents(@dal_array)
+          db_import_urls(@dal_array,@fnm)                              #import OID on/off
           @conn.execute('COMMIT')
         else
           @db=SiSU_Env::Info_db.new
@@ -153,7 +158,11 @@ module SiSU_DB_import
       string.strip!
     end
                                                                                  #% import into database tables
-    def import_db_metadata(dbi_unit)                                             #% import documents - populate database
+    def pf_db_import_transaction_open
+    end
+    def pf_db_import_transaction_close
+    end
+    def db_import_metadata                                                       #% import documents - populate database
       print %{ #{@cX.grey}import documents dbi_unit #{@cX.off} } unless @opt.cmd =~/q/
       @tp={}
       @md=SiSU_Param::Parameters.new(@opt).get
@@ -354,17 +363,17 @@ module SiSU_DB_import
       #end
       #if @md.ruby_version;                      special_character_escape(@md.ruby_version)
       SiSU_DB_DBI::Test.new(self,@opt).verify                          #% import title names, filenames (tuple)
-      t=SiSU_DB_tuple::Load_metadata.new(@conn,@tp,@@id_t)
+      t=SiSU_DB_tuple::Load_metadata.new(@conn,@tp,@@id_t,@opt,@file)
       t.tuple
     end
-    def import_documents(dbi_unit)                                     #% import documents - populate main database table
+    def db_import_documents(dal_array)                                     #% import documents - populate main database table
                                                                        #% import into substantive database tables (tuple)
       begin
         @col[:tid]=@@id_t
         @en,@en_ast,@en_pls=[],[],[]
         @col[:en_a]=nil
         @col[:en_z]=nil
-        dbi_unit.each do |data|
+        dal_array.each do |data|
           #data.gsub!(/<[biu]>(.+?)<\/[biu]>/,'\1')                    # remove bold, italics, underscore
           data.gsub!(/<b>(.+?)<\/b>/,'\1')                             # remove bold, italics, underscore
           data.gsub!(/<i>(.+?)<\/i>/,'\1')                             # remove bold, italics, underscore
@@ -396,7 +405,7 @@ module SiSU_DB_import
               end
               if @en_pls[0]; @en_a_plus,@en_z_plus=@en_pls[0].first,@en_pls[0].last
               end
-              t=SiSU_DB_tuple::Load_documents.new(@conn,@col,@opt)
+              t=SiSU_DB_tuple::Load_documents.new(@conn,@col,@opt,@file)
               t.tuple
               case @col[:lev]
               when /1/; @col[:lv1]+=1
@@ -437,7 +446,7 @@ module SiSU_DB_import
               end
               if @en_pls[0]; @en_a_plus,@en_z_plus=@en_pls[0].first,@en_pls[0].last
               end
-              t=SiSU_DB_tuple::Load_documents.new(@conn,@col,@opt)
+              t=SiSU_DB_tuple::Load_documents.new(@conn,@col,@opt,@file)
               t.tuple
               @col[:lev]=@col[:plaintext]=@col[:body]=''
             elsif data[/^5~(?:~\S+)?(.+?)<~(\d+);((?:\w|[0-6]:)\d+);(\w\d+)><([0-9a-f]{#{@@dl}}):([0-9a-f]{#{@@dl}})>/]                             # header lev5 seg level
@@ -476,7 +485,7 @@ module SiSU_DB_import
               end
               if @en_pls[0]; @en_a_plus,@en_z_plus=@en_pls[0].first,@en_pls[0].last
               end
-              t=SiSU_DB_tuple::Load_documents.new(@conn,@col,@opt)
+              t=SiSU_DB_tuple::Load_documents.new(@conn,@col,@opt,@file)
               t.tuple
               @col[:lev]=@col[:plaintext]=@col[:body]=''
             elsif data[/^6~(?:~\S+)?(.+?)<~(\d+);((?:\w|[0-6]:)\d+);(\w\d+)><([0-9a-f]{#{@@dl}}):([0-9a-f]{#{@@dl}})>/] # header lev6 seg level
@@ -515,7 +524,7 @@ module SiSU_DB_import
               end
               if @en_pls[0]; @en_a_plus,@en_z_plus=@en_pls[0].first,@en_pls[0].last
               end
-              t=SiSU_DB_tuple::Load_documents.new(@conn,@col,@opt)
+              t=SiSU_DB_tuple::Load_documents.new(@conn,@col,@opt,@file)
               t.tuple
               @col[:lev]=@col[:plaintext]=@col[:body]=''
             else                                                               #% regular text
@@ -565,7 +574,7 @@ module SiSU_DB_import
               special_character_escape(@col[:body])
               @col[:plaintext]=@col[:body].dup
               strip_markup(@col[:plaintext])
-              t=SiSU_DB_tuple::Load_documents.new(@conn,@col,@opt)
+              t=SiSU_DB_tuple::Load_documents.new(@conn,@col,@opt,@file)
               t.tuple
               @en,@en_ast,@en_pls=[],[],[]
               @col[:en_a]=@col[:en_z]=nil
@@ -604,7 +613,7 @@ module SiSU_DB_import
                          :id_t => @@id_t,
                          :hash => digest_clean
                     }
-                    t=SiSU_DB_tuple::Load_endnotes.new(@conn,en)
+                    t=SiSU_DB_tuple::Load_endnotes.new(@conn,en,@opt,@file)
                     t.tuple
                   end
                 end
@@ -645,7 +654,7 @@ module SiSU_DB_import
                          :id_t => @@id_t,
                          :hash => digest_clean
                     }
-                    t=SiSU_DB_tuple::Load_endnotes.new(@conn,en)
+                    t=SiSU_DB_tuple::Load_endnotes.new(@conn,en,@opt,@file)
                     t.tuple
                   end
                 end
@@ -685,7 +694,7 @@ module SiSU_DB_import
                          :id_t => @@id_t,
                          :hash => digest_clean
                     }
-                    t=SiSU_DB_tuple::Load_endnotes.new(@conn,en)
+                    t=SiSU_DB_tuple::Load_endnotes.new(@conn,en,@opt,@file)
                     t.tuple
                   end
                 end
@@ -743,7 +752,7 @@ module SiSU_DB_import
       end
       self
     end
-    def import_db_urls(dbi_unit,meta)                                           #% import documents OID - populate database
+    def db_import_urls(dbi_unit,meta)                                           #% import documents OID - populate database
       begin
         @fnm=meta
         @env=SiSU_Env::Info_env.new(@opt.fns)
@@ -818,7 +827,7 @@ module SiSU_DB_import
         or (@opt.cmd=~/e/ and FileTest.file?("#{out}/#@fnb/#{@opt.fns}.tgz")==true)
           f[:sisupod],u[:sisupod]='sisupod,', "'#{base}/#@fnb/#{@opt.fns}.tgz',"
         end
-      t=SiSU_DB_tuple::Load_urls.new(@conn,f,u,@@id_t)
+      t=SiSU_DB_tuple::Load_urls.new(@conn,f,u,@@id_t,@opt,@file)
       t.tuple
       rescue; SiSU_Errors::Info_error.new($!,$@,@opt.cmd,@opt.fns).error
       ensure
diff --git a/lib/sisu/v0/db_indexes.rb b/lib/sisu/v0/db_indexes.rb
index ff7a8156..faf2b720 100644
--- a/lib/sisu/v0/db_indexes.rb
+++ b/lib/sisu/v0/db_indexes.rb
@@ -61,8 +61,8 @@
 =end
 module SiSU_DB_index
   class Index                                           # create documents Indexes def initialize(opt,conn='',sql_type='')
-    def initialize(opt,conn='',sql_type='')
-      @opt,@conn,@sql_type=opt,conn,sql_type
+    def initialize(opt,conn,file,sql_type='')
+      @opt,@conn,@file,@sql_type=opt,conn,file,sql_type
     end
     def create_indexes                                                           # check added from pg not tested
       def base
diff --git a/lib/sisu/v0/db_load_tuple.rb b/lib/sisu/v0/db_load_tuple.rb
index 5e80623b..cb0278fb 100644
--- a/lib/sisu/v0/db_load_tuple.rb
+++ b/lib/sisu/v0/db_load_tuple.rb
@@ -63,8 +63,8 @@ module SiSU_DB_tuple
   class Load_documents
     require "#{SiSU_lib}/param"
     include SiSU_Param
-    def initialize(conn,col,opt)
-      @conn,@col,@opt=conn,col,opt
+    def initialize(conn,col,opt,file)
+      @conn,@col,@opt,@file=conn,col,opt,file
       @col[:lev]=@col[:lev].to_i
       unless @col[:lev]=~/^[1-6]/ \
       or @col[:lev]==1 \
@@ -80,16 +80,14 @@ module SiSU_DB_tuple
     end
     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]}');
-        })
+        sql_entry=if @col[:en_a]
+          "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]}');
-        })
+          "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]}');"
+        end
+        if @opt.cmd =~/M/
+          @file.puts sql_entry
+        else @conn.execute(sql_entry)
         end
         if @opt.cmd =~/v/
           if @col[:lev].inspect =~/[1235678]/
@@ -112,36 +110,40 @@ module SiSU_DB_tuple
     end
   end
   class Load_metadata
-    def initialize(conn,tp,id)
-      @conn,@tp,@id=conn,tp,id
+    def initialize(conn,tp,id,opt,file)
+      @conn,@tp,@id,@opt,@file=conn,tp,id,opt,file
     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});
-      })
+      sql_entry="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/
+        puts "maintenance mode on: creating sql transaction file (for last transaction set (document) only):\n\t#{@file.inspect}"
+        @file.puts sql_entry
+      else @conn.execute(%{#{sql_entry}})
+      end
     end
   end
   class Load_urls
-    def initialize(conn,f,u,id)
-      @conn,@f,@u,@id=conn,f,u,id
+    def initialize(conn,f,u,id,opt,file)
+      @conn,@f,@u,@id,@opt,@file=conn,f,u,id,opt,file
     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});
-      })
+      sql_entry="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/
+        @file.puts sql_entry
+      else @conn.execute(%{#{sql_entry}})
+      end
     end
   end
   class Load_endnotes
-    def initialize(conn,en)
-      @conn,@en=conn,en
+    def initialize(conn,en,opt,file)
+      @conn,@en,@opt,@file=conn,en,opt,file
     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]}');
-        })
+      sql_entry="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/
+        @file.puts sql_entry
+      else @conn.execute(%{#{sql_entry}})
+      end
     end
   end
 end
diff --git a/lib/sisu/v0/db_remove.rb b/lib/sisu/v0/db_remove.rb
index 63d69b64..bb88af58 100644
--- a/lib/sisu/v0/db_remove.rb
+++ b/lib/sisu/v0/db_remove.rb
@@ -61,8 +61,8 @@
 =end
 module SiSU_DB_remove
   class Remove
-    def initialize(opt,conn='' )
-      @opt,@conn=opt,conn
+    def initialize(opt,conn,file)
+      @opt,@conn,@file=opt,conn,file
       @md=SiSU_Param::Parameters.new(@opt).get
       @fnb=@md.fnb
       @db=SiSU_Env::Info_db.new
@@ -72,23 +72,18 @@ module SiSU_DB_remove
       if del
         del_id=del.join
         #@conn.execute("BEGIN")
-        @conn.execute(%{
-          DELETE FROM endnotes WHERE metadata_tid = '#{del_id}';
-          DELETE FROM endnotes_asterisk WHERE metadata_tid = '#{del_id}';
-          DELETE FROM endnotes_plus WHERE metadata_tid = '#{del_id}';
-          DELETE FROM documents WHERE metadata_tid = '#{del_id}';
-          DELETE FROM urls WHERE metadata_tid = '#{del_id}';
-          DELETE FROM metadata WHERE tid = '#{del_id}';
-          /*
-          DELETE FROM documents WHERE documents.metadata_tid = '#{del_id}';
-          DELETE FROM endnotes WHERE endnotes.metadata_tid = '#{del_id}';
-          DELETE FROM endnotes_asterisk WHERE endnotes_asterisk.metadata_tid = '#{del_id}';
-          DELETE FROM endnotes_plus WHERE endnotes_plus.metadata_tid = '#{del_id}';
-          DELETE FROM urls WHERE urls.metadata_tid = '#{del_id}';
-          DELETE FROM metadata WHERE metadata.tid = '#{del_id}';
-          */
-        })
-        #@conn.execute("COMMIT")
+        sql_entry=<<SQL
+DELETE FROM endnotes WHERE metadata_tid = '#{del_id}';
+DELETE FROM endnotes_asterisk WHERE metadata_tid = '#{del_id}';
+DELETE FROM endnotes_plus WHERE metadata_tid = '#{del_id}';
+DELETE FROM documents WHERE metadata_tid = '#{del_id}';
+DELETE FROM urls WHERE metadata_tid = '#{del_id}';
+DELETE FROM metadata WHERE tid = '#{del_id}';
+SQL
+        if @opt.cmd =~/M/
+          @file.puts sql_entry if @opt.cmd =~/M/
+        else @conn.execute(%{#{sql_entry}})
+        end
       else
         tell=SiSU_Screen::Ansi.new(@opt.cmd,"no such file in database #{@db.psql.db}::#{@opt.fns}")
         tell.puts_grey #if @opt.cmd.include? ?v
@@ -97,3 +92,17 @@ module SiSU_DB_remove
   end
 end
 __END__
+DELETE FROM endnotes WHERE metadata_tid = '#{del_id}';
+DELETE FROM endnotes_asterisk WHERE metadata_tid = '#{del_id}';
+DELETE FROM endnotes_plus WHERE metadata_tid = '#{del_id}';
+DELETE FROM documents WHERE metadata_tid = '#{del_id}';
+DELETE FROM urls WHERE metadata_tid = '#{del_id}';
+DELETE FROM metadata WHERE tid = '#{del_id}';
+/*
+DELETE FROM documents WHERE documents.metadata_tid = '#{del_id}';
+DELETE FROM endnotes WHERE endnotes.metadata_tid = '#{del_id}';
+DELETE FROM endnotes_asterisk WHERE endnotes_asterisk.metadata_tid = '#{del_id}';
+DELETE FROM endnotes_plus WHERE endnotes_plus.metadata_tid = '#{del_id}';
+DELETE FROM urls WHERE urls.metadata_tid = '#{del_id}';
+DELETE FROM metadata WHERE metadata.tid = '#{del_id}';
+*/
diff --git a/lib/sisu/v0/db_select.rb b/lib/sisu/v0/db_select.rb
index 0b456262..002f0039 100644
--- a/lib/sisu/v0/db_select.rb
+++ b/lib/sisu/v0/db_select.rb
@@ -63,14 +63,31 @@ module SiSU_DB_select
   class Case
     def initialize(opt,conn='',sql_type='pg')
       @opt,@conn,@sql_type=opt,conn,sql_type
+#@conn.execute("BEGIN;")
       #@sdb=Create.new(@conn)
-      @sdb=SiSU_DB_DBI::Create.new(@opt,@conn,@sql_type)
-      @index=SiSU_DB_DBI::Index.new(@opt,@conn,@sql_type)
-      @sdb_no=SiSU_DB_DBI::Drop.new(@opt,@conn,@sql_type)
-      @sdb_import=SiSU_DB_DBI::Import.new(@opt,@conn,@sql_type) if @opt.mod.inspect =~/update|import/
-      @remove=SiSU_DB_DBI::Remove.new(@opt,@conn) if @opt.mod.inspect =~/update|remove/
+      @file=sql_maintenance_file
+      @sdb=SiSU_DB_DBI::Create.new(@opt,@conn,@file,@sql_type)
+      @sdb_index=SiSU_DB_DBI::Index.new(@opt,@conn,@file,@sql_type)
+      @sdb_no=SiSU_DB_DBI::Drop.new(@opt,@conn,@file,@sql_type)
+      @sdb_import=SiSU_DB_DBI::Import.new(@opt,@conn,@file,@sql_type) if @opt.mod.inspect =~/update|import/
+      @sdb_remove_doc=SiSU_DB_DBI::Remove.new(@opt,@conn,@file) if @opt.mod.inspect =~/update|remove/
       @db=SiSU_Env::Info_db.new
     end
+    def sql_maintenance_file
+      file=if @opt.inspect =~/M/
+        x=if @opt.fns and not @opt.fns.empty?
+          @env=SiSU_Env::Info_env.new(@opt.fns) if @opt.fns
+          puts "\n#{@env.path.sqlite}/#{@opt.fns}.sql"
+          @db=SiSU_Env::Info_db.new
+          @job="sqlite3 #{@db.sqlite.db} < #{@env.path.sqlite}/#{@opt.fns}.sql"
+          File.new("#{@env.path.sqlite}/#{@opt.fns}.sql",'w+')
+        elsif @opt.fns and @opt.fns.inspect =~/create/; nil #sort variations later
+        else nil
+        end
+      else nil
+      end
+      file
+    end
     def cases
       @opt.mod.each do |mod|
         case mod
@@ -84,13 +101,14 @@ module SiSU_DB_select
           @sdb.output_dir?
           #@sdb.create_db
           begin
+          #@sdb.create_db
           @sdb.create_table.metadata
           @sdb.create_table.documents
           @sdb.create_table.endnotes
           @sdb.create_table.endnotes_asterisk
           @sdb.create_table.endnotes_plus
           @sdb.create_table.urls
-          @index.create_indexes
+          @sdb_index.create_indexes
           rescue;  SiSU_Errors::Info_error.new($!,$@,'-D').error; @sdb.output_dir?
           end
         when /^--createtable(s)?$/
@@ -102,7 +120,7 @@ module SiSU_DB_select
           @sdb.create_table.endnotes_asterisk
           @sdb.create_table.endnotes_plus
           @sdb.create_table.urls
-          @index.create_indexes
+          @sdb_index.create_indexes
           rescue; @sdb.output_dir?
           end
         when /^--recreate$/
@@ -115,7 +133,7 @@ module SiSU_DB_select
           @sdb.create_table.endnotes_asterisk
           @sdb.create_table.endnotes_plus
           @sdb.create_table.urls
-          @index.create_indexes
+          @sdb_index.create_indexes
           rescue; @sdb.output_dir?
           end
         when /^--cr(eate)?lex$/
@@ -139,14 +157,14 @@ module SiSU_DB_select
           end
           tell.puts_grey if @opt.cmd =~/v/
         when /^--remove$/
-          @remove.remove
+          @sdb_remove_doc.remove
         when /^--update$/
-          @remove.remove
+          @sdb_remove_doc.remove
           @sdb_import.marshal_load
           tell=SiSU_Screen::Ansi.new(@opt.cmd,"pgaccess or psql #{@db.psql.db} database?")
           tell.puts_grey if @opt.cmd =~/v/
         when /^--index$/
-          @index.create_indexes
+          @sdb_index.create_indexes
         when /^droptable(s)?$/
           @sdb_no.drop.tables
         when /^--dropindex(es)?$/
@@ -160,6 +178,12 @@ module SiSU_DB_select
           help.summary
           help.commands
         end
+        if @opt.cmd =~/M/
+          #@conn.commit
+          #@conn.disconnect
+          puts @job
+          #system(@job)
+        end
       end
       begin
       @conn.commit if @sql_type =~/sqlite/
diff --git a/lib/sisu/v0/db_tests.rb b/lib/sisu/v0/db_tests.rb
index 73e0400f..72282b59 100644
--- a/lib/sisu/v0/db_tests.rb
+++ b/lib/sisu/v0/db_tests.rb
@@ -64,9 +64,9 @@ module SiSU_DB_tests
     def initialize(info,opt)
       @ck,@opt=info,opt
       unless @opt.cmd =~/q/
-        puts @ck.tp[:fns]
-        puts @ck.tp[:title]
-        puts @ck.tp[:creator] if @ck.tp[:creator]
+        puts @ck.tp[:fns]  if @ck.tp[:fns] and not @ck.tp[:fns].empty?
+        puts @ck.tp[:title] if @ck.tp[:title] and not @ck.tp[:title].empty?
+        puts @ck.tp[:creator] if @ck.tp[:creator] and not @ck.tp[:creator].empty?
       end
     end
     def verify
@@ -109,7 +109,7 @@ module SiSU_DB_tests
       puts @ck.tp[:dewey].length.to_s          + ' checklength ' + @ck.tp[:dewey]          if @ck.tp[:dewey]          and @ck.tp[:dewey].length          >@ck.lt_dewey
       puts @ck.tp[:isbn].length.to_s           + ' checklength ' + @ck.tp[:isbn]           if @ck.tp[:isbn]           and @ck.tp[:isbn].length           >@ck.lt_isbn
       puts @ck.tp[:pg].length.to_s             + ' checklength ' + @ck.tp[:pg]             if @ck.tp[:pg]             and @ck.tp[:pg].length             >@ck.lt_pg
-      puts @ck.tp[:date]                                                                   if @ck.tp[:date] !~/\d\d-\d\d-\d\d/
+      puts @ck.tp[:date]                                                                   if  @ck.tp[:date] and not @ck.tp[:date].empty? and @ck.tp[:date] !~/\d\d-\d\d-\d\d/
       end
     end
   end