v5 v6: db, remove ruby-dbi, for: pg ruby-pg, sqlite ruby-sqlite3 (already uses)

* ruby-pg require for postgresql
* ruby-sqlite3 require for sqlite3 (done previously: already using ruby-sqlite3
  rather than ruby-dbi)
* ruby-dbi remove calls (in any common files & sisu pg and sisu sqlite3)
* separate pg and sqlite db actions

13 files changed, 169 insertions, 63 deletions

diff --git a/lib/sisu/v5/db_create.rb b/lib/sisu/v5/db_create.rb
index d558aacd..451a0a34 100644
--- a/lib/sisu/v5/db_create.rb
+++ b/lib/sisu/v5/db_create.rb
@@ -101,6 +101,19 @@ module SiSU_DbCreate
       end
     end
     def create_table
+      def conn_exec(sql)
+        if @sql_type==:pg
+          conn_exec_pg(sql)
+        elsif @sql_type==:sqlite
+          conn_exec_sqlite(sql)
+        end
+      end
+      def conn_exec_pg(sql)
+        @conn.exec_params(sql)
+      end
+      def conn_exec_sqlite(sql)
+          @conn.execute(sql)
+      end
       def metadata_and_text
         if (@opt.act[:verbose_plus][:set]==:on \
         or @opt.act[:maintenance][:set]==:on)
@@ -211,7 +224,7 @@ module SiSU_DbCreate
 /*          writing_focus_nationality VARCHAR(100) NULL, */
           );
         }
-        @conn.execute(create_metadata_and_text)
+        conn_exec(create_metadata_and_text)
         @comment.psql.metadata_and_text if @comment
       end
       def doc_objects                                                 # create doc_objects base
@@ -258,7 +271,7 @@ module SiSU_DbCreate
             types           CHAR(1) NULL
           );
         }
-        @conn.execute(create_doc_objects)
+        conn_exec(create_doc_objects)
         @comment.psql.doc_objects if @comment
       end
       def endnotes
@@ -284,7 +297,7 @@ module SiSU_DbCreate
             metadata_tid    BIGINT REFERENCES metadata_and_text
           );
         }
-        @conn.execute(create_endnotes)
+        conn_exec(create_endnotes)
         @comment.psql.endnotes if @comment
       end
       def endnotes_asterisk
@@ -310,7 +323,7 @@ module SiSU_DbCreate
             metadata_tid    BIGINT REFERENCES metadata_and_text
           );
         }
-        @conn.execute(create_endnotes_asterisk)
+        conn_exec(create_endnotes_asterisk)
         @comment.psql.endnotes_asterisk if @comment
       end
       def endnotes_plus
@@ -336,7 +349,7 @@ module SiSU_DbCreate
             metadata_tid    BIGINT REFERENCES metadata_and_text
           );
         }
-        @conn.execute(create_endnotes_plus)
+        conn_exec(create_endnotes_plus)
         @comment.psql.endnotes_plus if @comment
       end
       def urls                                                       # create doc_objects file links mapping
@@ -370,7 +383,7 @@ module SiSU_DbCreate
             sisupod         varchar(512)
           );
         }
-        @conn.execute(create_urls)
+        conn_exec(create_urls)
         @comment.psql.urls if @comment
       end
       self
@@ -386,7 +399,7 @@ module SiSU_DbCreate
       def conn_execute_array(sql_arr)
         @conn.transaction do |conn|
           sql_arr.each do |sql|
-            conn.execute(sql)
+            conn.exec_params(sql)
           end
         end
       end
diff --git a/lib/sisu/v5/db_drop.rb b/lib/sisu/v5/db_drop.rb
index 1a76eff9..75e98faa 100644
--- a/lib/sisu/v5/db_drop.rb
+++ b/lib/sisu/v5/db_drop.rb
@@ -118,14 +118,24 @@ module SiSU_DbDrop
             else
               @conn.transaction
               @drop_table.each do |d|
-                @conn.execute(d)
-              end
+                begin
+                  @conn.exec_params(d)
+                rescue
+                  next
+                end
+                end
               @conn.commit
             end
-          else
+          when :pg
+            @conn.transaction
             @drop_table.each do |d|
-              @conn.execute(d)
+              begin
+                @conn.exec_params(d)
+              rescue
+                next
+              end
             end
+            @conn.commit
           end
         rescue
           case @sql_type
@@ -135,7 +145,11 @@ module SiSU_DbDrop
             end
           else
             @drop_table.each do |d|
-              @conn.execute(d)
+              begin
+                @conn.exec_params(d)
+              rescue
+                next
+              end
             end
           end
         ensure
@@ -145,7 +159,11 @@ module SiSU_DbDrop
         def conn_execute_array(sql_arr)
           @conn.transaction do |conn|
             sql_arr.each do |sql|
-              conn.execute(sql)
+              begin
+                conn.exec_params(sql)
+              rescue
+                next
+              end
             end
           end
         end
diff --git a/lib/sisu/v5/db_import.rb b/lib/sisu/v5/db_import.rb
index 3d0b7f8c..9e88e7ee 100644
--- a/lib/sisu/v5/db_import.rb
+++ b/lib/sisu/v5/db_import.rb
@@ -109,7 +109,7 @@ module SiSU_DbImport
       begin
         @id_n=@driver_sqlite3 \
         ? @conn.execute( sql ).join.to_i
-        : @id_n=@conn.execute( sql ) { |x| x.fetch_all.flatten[0] }
+        : @id_n=@conn.exec( sql ).getvalue(0,0).to_i
         @id_n ||=0
       rescue
         puts "#{__FILE__}:#{__LINE__}" if @opt.act[:maintenance][:set]==:on
@@ -138,7 +138,7 @@ module SiSU_DbImport
       ;} # note, for .ssm: @md.fns (is set during runtime & is) != @opt.fns @md.opt.fns
       file_exist=@sql_type==:sqlite \
       ? @conn.get_first_value(select_first_match)
-      : @conn.select_one(select_first_match)
+      : @conn.exec(select_first_match).field_values("tid")[0]
       if not file_exist
         t_d=[]                                                              # transaction_data
         t_d << db_import_metadata
@@ -166,11 +166,11 @@ module SiSU_DbImport
             #@conn.execute("COMMIT")
           else
             #'do' works for postgresql
-            @conn.do("BEGIN")
+            @conn.exec("BEGIN")
               t_d.each do |sql|
-                @conn.do(sql)
+                @conn.exec(sql)
               end
-            @conn.do("COMMIT")
+            @conn.exec("COMMIT")
           end
         rescue DBI::DatabaseError => e
           STDERR.puts "Error code: #{e.err}"
@@ -206,7 +206,7 @@ module SiSU_DbImport
       else
         if file_exist
           @db=SiSU_Env::InfoDb.new
-          puts "\n#{@cX.grey}file #{@cX.off} #{@cX.blue}#{@opt.fns}#{@cX.off} in language code #{cX.blue}#{@opt.lng}#{cX.off} #{@cX.grey}already exists in database#{@cX.off} #{@cX.blue}#{@db.psql.db}#{@cX.off} #{@cX.brown}update instead?#{@cX.off}"
+          puts "\nfile #{@opt.fns} in language code #{@opt.lng} already exists in database #{@db.psql.db} update instead?"
         end
       end
     end
diff --git a/lib/sisu/v5/db_indexes.rb b/lib/sisu/v5/db_indexes.rb
index 8a269a4c..7e1164ab 100644
--- a/lib/sisu/v5/db_indexes.rb
+++ b/lib/sisu/v5/db_indexes.rb
@@ -67,10 +67,24 @@ module SiSU_DbIndex
       @opt,@conn,@file,@sql_type=opt,conn,file,sql_type
     end
     def create_indexes                                                           # check added from pg not tested
+      def conn_execute_sql_pg(conn,sql)
+        conn.exec_params(sql)
+      end
+      def conn_execute_sql_sqlite(conn,sql)
+        conn.execute(sql)
+      end
+      def conn_execute_sql(conn,sql)
+        if @sql_type==:pg
+          conn_execute_sql_pg(conn,sql)
+        elsif @sql_type==:sqlite
+          conn_execute_sql_sqlite(conn,sql)
+        else
+        end
+      end
       def conn_execute_array(sql_arr)
         @conn.transaction do |conn|
           sql_arr.each do |sql|
-            conn.execute(sql)
+            conn_execute_sql(conn,sql)
           end
         end
       end
diff --git a/lib/sisu/v5/db_remove.rb b/lib/sisu/v5/db_remove.rb
index 9a7ea8ea..2dea65f2 100644
--- a/lib/sisu/v5/db_remove.rb
+++ b/lib/sisu/v5/db_remove.rb
@@ -90,8 +90,8 @@ module SiSU_DbRemove
           WHERE metadata_and_text.src_filename = '#{@md.fns}'
           AND metadata_and_text.language_document_char = '#{@opt.lng}'
         ;} # note, for .ssm: @md.fns (is set during runtime & is) != @opt.fns @md.opt.fns
-        x=@conn.select_one(remove_selected)
-        x ? (x.join.to_i) : nil
+        x=@conn.exec(remove_selected)
+        x.field_values("tid")[0]
       end
       if del_id
         sql_entry=[
@@ -105,12 +105,20 @@ module SiSU_DbRemove
         if driver_sqlite3
           @conn.transaction
           sql_entry.each do |s|
-            @conn.execute(s)
+            begin
+              @conn.execute(s)
+            rescue
+              next
+            end
           end
           @conn.commit if driver_sqlite3
         else
           sql_entry.each do |s|
-            @conn.execute(s)
+            begin
+              @conn.exec_params(s)
+            rescue
+              next
+            end
           end
         end
         if @opt.act[:maintenance][:set]==:on
diff --git a/lib/sisu/v5/dbi_discrete.rb b/lib/sisu/v5/dbi_discrete.rb
index b0468e8d..0b42c50e 100644
--- a/lib/sisu/v5/dbi_discrete.rb
+++ b/lib/sisu/v5/dbi_discrete.rb
@@ -77,7 +77,7 @@ module  SiSU_DBI_Discrete                               #% database building
   end
   class SQL
     def initialize(opt)
-      SiSU_Env::Load.new('dbi',true).prog
+      SiSU_Env::Load.new('sqlite3',true).prog
       @opt=opt
       @particulars=SiSU_Particulars::CombinedSingleton.instance.get_all(opt)
       @md=@particulars.md
diff --git a/lib/sisu/v6/db_create.rb b/lib/sisu/v6/db_create.rb
index 105f089c..01fc1935 100644
--- a/lib/sisu/v6/db_create.rb
+++ b/lib/sisu/v6/db_create.rb
@@ -101,6 +101,19 @@ module SiSU_DbCreate
       end
     end
     def create_table
+      def conn_exec(sql)
+        if @sql_type==:pg
+          conn_exec_pg(sql)
+        elsif @sql_type==:sqlite
+          conn_exec_sqlite(sql)
+        end
+      end
+      def conn_exec_pg(sql)
+        @conn.exec_params(sql)
+      end
+      def conn_exec_sqlite(sql)
+          @conn.execute(sql)
+      end
       def metadata_and_text
         if (@opt.act[:verbose_plus][:set]==:on \
         or @opt.act[:maintenance][:set]==:on)
@@ -211,7 +224,7 @@ module SiSU_DbCreate
 /*          writing_focus_nationality VARCHAR(100) NULL, */
           );
         }
-        @conn.execute(create_metadata_and_text)
+        conn_exec(create_metadata_and_text)
         @comment.psql.metadata_and_text if @comment
       end
       def doc_objects                                                 # create doc_objects base
@@ -258,7 +271,7 @@ module SiSU_DbCreate
             types           CHAR(1) NULL
           );
         }
-        @conn.execute(create_doc_objects)
+        conn_exec(create_doc_objects)
         @comment.psql.doc_objects if @comment
       end
       def endnotes
@@ -284,7 +297,7 @@ module SiSU_DbCreate
             metadata_tid    BIGINT REFERENCES metadata_and_text
           );
         }
-        @conn.execute(create_endnotes)
+        conn_exec(create_endnotes)
         @comment.psql.endnotes if @comment
       end
       def endnotes_asterisk
@@ -310,7 +323,7 @@ module SiSU_DbCreate
             metadata_tid    BIGINT REFERENCES metadata_and_text
           );
         }
-        @conn.execute(create_endnotes_asterisk)
+        conn_exec(create_endnotes_asterisk)
         @comment.psql.endnotes_asterisk if @comment
       end
       def endnotes_plus
@@ -336,7 +349,7 @@ module SiSU_DbCreate
             metadata_tid    BIGINT REFERENCES metadata_and_text
           );
         }
-        @conn.execute(create_endnotes_plus)
+        conn_exec(create_endnotes_plus)
         @comment.psql.endnotes_plus if @comment
       end
       def urls                                                       # create doc_objects file links mapping
@@ -370,7 +383,7 @@ module SiSU_DbCreate
             sisupod         varchar(512)
           );
         }
-        @conn.execute(create_urls)
+        conn_exec(create_urls)
         @comment.psql.urls if @comment
       end
       self
@@ -386,7 +399,7 @@ module SiSU_DbCreate
       def conn_execute_array(sql_arr)
         @conn.transaction do |conn|
           sql_arr.each do |sql|
-            conn.execute(sql)
+            conn.exec_params(sql)
           end
         end
       end
diff --git a/lib/sisu/v6/db_drop.rb b/lib/sisu/v6/db_drop.rb
index 9ab73186..926da47f 100644
--- a/lib/sisu/v6/db_drop.rb
+++ b/lib/sisu/v6/db_drop.rb
@@ -118,14 +118,24 @@ module SiSU_DbDrop
             else
               @conn.transaction
               @drop_table.each do |d|
-                @conn.execute(d)
-              end
+                begin
+                  @conn.exec_params(d)
+                rescue
+                  next
+                end
+                end
               @conn.commit
             end
-          else
+          when :pg
+            @conn.transaction
             @drop_table.each do |d|
-              @conn.execute(d)
+              begin
+                @conn.exec_params(d)
+              rescue
+                next
+              end
             end
+            @conn.commit
           end
         rescue
           case @sql_type
@@ -135,7 +145,11 @@ module SiSU_DbDrop
             end
           else
             @drop_table.each do |d|
-              @conn.execute(d)
+              begin
+                @conn.exec_params(d)
+              rescue
+                next
+              end
             end
           end
         ensure
@@ -145,7 +159,11 @@ module SiSU_DbDrop
         def conn_execute_array(sql_arr)
           @conn.transaction do |conn|
             sql_arr.each do |sql|
-              conn.execute(sql)
+              begin
+                conn.exec_params(sql)
+              rescue
+                next
+              end
             end
           end
         end
diff --git a/lib/sisu/v6/db_import.rb b/lib/sisu/v6/db_import.rb
index dd9dfe84..35fb631a 100644
--- a/lib/sisu/v6/db_import.rb
+++ b/lib/sisu/v6/db_import.rb
@@ -109,7 +109,7 @@ module SiSU_DbImport
       begin
         @id_n=@driver_sqlite3 \
         ? @conn.execute( sql ).join.to_i
-        : @id_n=@conn.execute( sql ) { |x| x.fetch_all.flatten[0] }
+        : @id_n=@conn.exec( sql ).getvalue(0,0).to_i
         @id_n ||=0
       rescue
         puts "#{__FILE__}:#{__LINE__}" if @opt.act[:maintenance][:set]==:on
@@ -138,7 +138,7 @@ module SiSU_DbImport
       ;} # note, for .ssm: @md.fns (is set during runtime & is) != @opt.fns @md.opt.fns
       file_exist=@sql_type==:sqlite \
       ? @conn.get_first_value(select_first_match)
-      : @conn.select_one(select_first_match)
+      : @conn.exec(select_first_match).field_values("tid")[0]
       if not file_exist
         t_d=[]                                                              # transaction_data
         t_d << db_import_metadata
@@ -166,11 +166,11 @@ module SiSU_DbImport
             #@conn.execute("COMMIT")
           else
             #'do' works for postgresql
-            @conn.do("BEGIN")
+            @conn.exec("BEGIN")
               t_d.each do |sql|
-                @conn.do(sql)
+                @conn.exec(sql)
               end
-            @conn.do("COMMIT")
+            @conn.exec("COMMIT")
           end
         rescue DBI::DatabaseError => e
           STDERR.puts "Error code: #{e.err}"
@@ -206,7 +206,7 @@ module SiSU_DbImport
       else
         if file_exist
           @db=SiSU_Env::InfoDb.new
-          puts "\n#{@cX.grey}file #{@cX.off} #{@cX.blue}#{@opt.fns}#{@cX.off} in language code #{cX.blue}#{@opt.lng}#{cX.off} #{@cX.grey}already exists in database#{@cX.off} #{@cX.blue}#{@db.psql.db}#{@cX.off} #{@cX.brown}update instead?#{@cX.off}"
+          puts "\nfile #{@opt.fns} in language code #{@opt.lng} already exists in database #{@db.psql.db} update instead?"
         end
       end
     end
diff --git a/lib/sisu/v6/db_indexes.rb b/lib/sisu/v6/db_indexes.rb
index 6550fe51..7df20889 100644
--- a/lib/sisu/v6/db_indexes.rb
+++ b/lib/sisu/v6/db_indexes.rb
@@ -67,10 +67,24 @@ module SiSU_DbIndex
       @opt,@conn,@file,@sql_type=opt,conn,file,sql_type
     end
     def create_indexes                                                           # check added from pg not tested
+      def conn_execute_sql_pg(conn,sql)
+        conn.exec_params(sql)
+      end
+      def conn_execute_sql_sqlite(conn,sql)
+        conn.execute(sql)
+      end
+      def conn_execute_sql(conn,sql)
+        if @sql_type==:pg
+          conn_execute_sql_pg(conn,sql)
+        elsif @sql_type==:sqlite
+          conn_execute_sql_sqlite(conn,sql)
+        else
+        end
+      end
       def conn_execute_array(sql_arr)
         @conn.transaction do |conn|
           sql_arr.each do |sql|
-            conn.execute(sql)
+            conn_execute_sql(conn,sql)
           end
         end
       end
diff --git a/lib/sisu/v6/db_remove.rb b/lib/sisu/v6/db_remove.rb
index 5a392649..b1628030 100644
--- a/lib/sisu/v6/db_remove.rb
+++ b/lib/sisu/v6/db_remove.rb
@@ -90,8 +90,8 @@ module SiSU_DbRemove
           WHERE metadata_and_text.src_filename = '#{@md.fns}'
           AND metadata_and_text.language_document_char = '#{@opt.lng}'
         ;} # note, for .ssm: @md.fns (is set during runtime & is) != @opt.fns @md.opt.fns
-        x=@conn.select_one(remove_selected)
-        x ? (x.join.to_i) : nil
+        x=@conn.exec(remove_selected)
+        x.field_values("tid")[0]
       end
       if del_id
         sql_entry=[
@@ -105,12 +105,20 @@ module SiSU_DbRemove
         if driver_sqlite3
           @conn.transaction
           sql_entry.each do |s|
-            @conn.execute(s)
+            begin
+              @conn.execute(s)
+            rescue
+              next
+            end
           end
           @conn.commit if driver_sqlite3
         else
           sql_entry.each do |s|
-            @conn.execute(s)
+            begin
+              @conn.exec_params(s)
+            rescue
+              next
+            end
           end
         end
         if @opt.act[:maintenance][:set]==:on
diff --git a/lib/sisu/v6/dbi.rb b/lib/sisu/v6/dbi.rb
index 5bd98b8a..7432f2f3 100644
--- a/lib/sisu/v6/dbi.rb
+++ b/lib/sisu/v6/dbi.rb
@@ -71,7 +71,6 @@ module  SiSU_DBI
     include SiSU_FormatShared
   class SQL
     def initialize(opt)
-      SiSU_Env::Load.new('dbi',true).prog
       @opt=opt
       @db=SiSU_Env::InfoDb.new
       if @opt.cmd =~/[Dd]/ \
@@ -96,6 +95,9 @@ module  SiSU_DBI
           maintenance_check(@opt,__FILE__,__LINE__) if @opt.act[:maintenance][:set]==:on
           :sqlite
         end
+        if    @sql_type==:pg    then SiSU_Env::Load.new('pg',true).prog
+        elsif @sql_type==:sqlite then SiSU_Env::Load.new('sqlite3',true).prog
+        end
       end
     end
     def maintenance_check(opt,file,line)
@@ -105,7 +107,8 @@ module  SiSU_DBI
     end
     def read_psql
       begin
-        @conn=@db.psql.conn_dbi
+        require 'pg'
+        @conn=@db.psql.conn_pg
       rescue
         if @opt.mod.inspect=~/--(?:createall|create)/
           cX=SiSU_Screen::Ansi.new(@opt.act[:color_state][:set]).cX
diff --git a/lib/sisu/v6/sysenv.rb b/lib/sisu/v6/sysenv.rb
index 8c819d9b..a8879e7a 100644
--- a/lib/sisu/v6/sysenv.rb
+++ b/lib/sisu/v6/sysenv.rb
@@ -249,7 +249,9 @@ module SiSU_Env
       $:.each do |reqpath|
         if FileTest.exist?("#{reqpath}/#{@prog}.rb")
           load_prog=true
+          #SiSU_Utils::CodeMarker.new(__LINE__,__FILE__,:fuchsia).ok("#{reqpath}/#{@prog}.rb loaded")
           break
+        #else SiSU_Utils::CodeMarker.new(__LINE__,__FILE__,:fuchsia).warn("#{reqpath}/#{@prog}.rb not found")
         end
       end
       if load_prog \
@@ -4428,6 +4430,9 @@ WOK
         : ''
       end
       def dbi
+        PG::Connection.open(:dbname =>  psql.db)
+      end
+      def dbi_
         (psql.host =~/(?:\S{1,3}\.){3}\S{1,3}|\S+?\.\S+/) \
         ? "DBI:Pg:database=#{psql.db};host=#{psql.host};port=#{psql.port}"
         : "DBI:Pg:database=#{psql.db};port=#{psql.port}"
@@ -4435,19 +4440,11 @@ WOK
       def conn_dbi
         DBI.connect(psql.dbi,psql.user,psql.db)
       end
-     self
-    end
-    def mysql
-      def db
-        #"#{Db[:name_prefix]}#{@pwd_stub}"
-      end
-      def port
-        '**'
+      def conn_pg
+        require 'pg'
+        PG::Connection.new(dbname: psql.db, port: psql.port)
       end
-      def dbi
-        "dbi:Mysql:database=#{mysql.db};port=#{mysql.port}"
-      end
-      self
+     self
     end
     def sqlite
       def db