From 7bfd567154b9fd468e5df53901c3d8c383cc25f8 Mon Sep 17 00:00:00 2001 From: Ralph Amissah Date: Sun, 18 May 2014 08:53:33 -0400 Subject: 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 --- lib/sisu/v5/db_create.rb | 27 ++++++++++++++++++++------- lib/sisu/v5/db_drop.rb | 30 ++++++++++++++++++++++++------ lib/sisu/v5/db_import.rb | 12 ++++++------ lib/sisu/v5/db_indexes.rb | 16 +++++++++++++++- lib/sisu/v5/db_remove.rb | 16 ++++++++++++---- lib/sisu/v5/dbi_discrete.rb | 2 +- lib/sisu/v6/db_create.rb | 27 ++++++++++++++++++++------- lib/sisu/v6/db_drop.rb | 30 ++++++++++++++++++++++++------ lib/sisu/v6/db_import.rb | 12 ++++++------ lib/sisu/v6/db_indexes.rb | 16 +++++++++++++++- lib/sisu/v6/db_remove.rb | 16 ++++++++++++---- lib/sisu/v6/dbi.rb | 7 +++++-- lib/sisu/v6/sysenv.rb | 21 +++++++++------------ 13 files changed, 169 insertions(+), 63 deletions(-) (limited to 'lib') 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 -- cgit v1.2.3