From 0cdd579c809f3041df8842adb4d6f74d9cabd457 Mon Sep 17 00:00:00 2001 From: Ralph Amissah Date: Wed, 1 Jul 2009 13:45:51 -0400 Subject: db dbi, interfaces to sqlite and pgsql, fixes, pass individual sql statements --- lib/sisu/v0/db_import.rb | 129 +++++++++++++++++++++++++---------------------- 1 file changed, 70 insertions(+), 59 deletions(-) (limited to 'lib/sisu/v0/db_import.rb') diff --git a/lib/sisu/v0/db_import.rb b/lib/sisu/v0/db_import.rb index 5810f13f..da564639 100644 --- a/lib/sisu/v0/db_import.rb +++ b/lib/sisu/v0/db_import.rb @@ -1,4 +1,4 @@ -# coding: utf-8 +#j coding: utf-8 =begin * Name: SiSU @@ -22,7 +22,7 @@ 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 + 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 @@ -95,9 +95,9 @@ module SiSU_DB_import else @conn.execute( sql ) { |x| x.fetch_all.to_s.to_i } end rescue - puts "#{__FILE__} #{__LINE__}" if @opt.cmd =~/M/ + puts "#{__FILE__}:#{__LINE__}" if @opt.cmd =~/M/ end - @col[:lid] =0 if @col[:lid].nil? or @col[:lid].to_s.empty? + @col[:lid]=0 if @col[:lid].nil? or @col[:lid].to_s.empty? sql='SELECT MAX(nid) FROM endnotes' begin @id_n ||=0 @@ -107,7 +107,7 @@ module SiSU_DB_import @id_n=@conn.execute( sql ) { |x| x.fetch_all.to_s.to_i } end rescue - puts "#{__FILE__} #{__LINE__}" if @opt.cmd =~/M/ + puts "#{__FILE__}:#{__LINE__}" if @opt.cmd =~/M/ 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 @@ -121,59 +121,66 @@ module SiSU_DB_import tell.puts_blue unless @opt.cmd =~/q/ tell=SiSU_Screen::Ansi.new(@opt.cmd,'Marshal Load',@fnm) tell.print_grey if @opt.cmd =~/v/ - case @sql_type - when /sqlite/ #fix logic for sqlite ! - #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 - if @opt.cmd =~/M/ + file_exist=if @sql_type=~/sqlite/; nil + else + @conn.select_one(%{ SELECT metadata.tid FROM metadata WHERE metadata.filename ~ '#{@opt.fns}'; }) + end + if (@sql_type!~/sqlite/ and not file_exist) \ + or @sql_type=~/sqlite/ + t_d=[] # transaction_data + t_d << db_import_metadata + t_d << db_import_documents(@dal_array) + t_d << db_import_urls(@dal_array,@fnc) #import OID on/off + t_d=t_d.flatten + if @opt.cmd =~/[MV]/ + puts @conn.class if defined? @conn.class + puts @conn.driver_name if defined? @conn.driver_name + puts @conn.driver if defined? @conn.driver + end + begin + @conn.transaction do |conn| + t_d.each do |sql| + conn.execute(sql) + end + end + rescue + SiSU_Errors::Info_error.new($!,$@,@opt.cmd,@opt.fns).error + puts "#{__FILE__}:#{__LINE__}" if @opt.cmd =~/M/ + ensure + #@conn.execute("COMMIT") + end + if @sql_type=~/sqlite/ \ + and @opt.cmd =~/[MV]/ puts "\n" + @conn.inspect - puts "\nat #{__FILE__} #{__LINE__}" + puts "\nat #{__FILE__}:#{__LINE__}" end else - file_exist=@conn.select_one(%{ SELECT metadata.tid FROM metadata WHERE metadata.filename ~ '#{@opt.fns}'; }) - unless file_exist - @conn.execute('BEGIN') - db_import_metadata - db_import_documents(@dal_array) - db_import_urls(@dal_array,@fnm) #import OID on/off - @conn.execute('COMMIT') - if @opt.cmd =~/[MV]/ - puts "\n" + @conn.inspect - puts "\nat #{__FILE__} #{__LINE__}" - end - else + if file_exist @db=SiSU_Env::Info_db.new puts "\n#{@cX.grey}file #{@cX.off} #{@cX.blue}#{@opt.fns}#{@cX.off} #{@cX.grey}already exists in database#{@cX.off} #{@cX.blue}#{@db.psql.db}#{@cX.off} #{@cX.brown}update instead?#{@cX.off}" end end end - def special_character_escape(string) - string.gsub!(/'/,"''") #string.gsub!(/'/,"\047") #string.gsub!(/'/,"\\'") - string.gsub!(/#{Mx[:br_line]}|#{Mx[:br_nl]}/,"
\n") - string.gsub!(/#{Mx[:gr_o]}(?:code|alt|group|verse)(?:-end)?#{Mx[:gr_c]}/,'') - string.gsub!(/#{Mx[:mk_o]}:name#\S+?#{Mx[:mk_c]}/,'') - string.gsub!(/#{Mx[:lnk_o]}\s*(\S+?\.(?:png|jpg))(?:\s+\d+x\d+)?(.+?)#{Mx[:lnk_c]}\S+/,'[image: \1] \2') - string.gsub!(/#{Mx[:lnk_o]}\s*(.+?)\s*#{Mx[:lnk_c]}(?:https?|file|ftp):\/\/\S+?([.,!?]?(?:\s|$))/,'\1\2') + def special_character_escape(str) + str.gsub!(/'/,"''") #string.gsub!(/'/,"\047") #string.gsub!(/'/,"\\'") + str.gsub!(/#{Mx[:br_line]}|#{Mx[:br_nl]}/,"
\n") + str.gsub!(/#{Mx[:gr_o]}(?:code|alt|group|verse)(?:-end)?#{Mx[:gr_c]}/,'') + str.gsub!(/#{Mx[:mk_o]}:name#\S+?#{Mx[:mk_c]}/,'') + str.gsub!(/#{Mx[:lnk_o]}\s*(\S+?\.(?:png|jpg))(?:\s+\d+x\d+)?(.+?)#{Mx[:lnk_c]}\S+/,'[image: \1] \2') + str.gsub!(/#{Mx[:lnk_o]}\s*(.+?)\s*#{Mx[:lnk_c]}(?:https?|file|ftp):\/\/\S+?([.,!?]?(?:\s|$))/,'\1\2') end - def strip_markup(string) #define rules, make same as in dal clean - string.gsub!(/#{Mx[:fa_superscript_o]}(\d+)#{Mx[:fa_superscript_c]}/,'[\1]') - string.gsub!(/#{Mx[:pa_o]}:i[1-9]#{Mx[:pa_c]}/,'') - string.gsub!(/(?: \\;|#{Mx[:nbsp]})+/,' ') - string.gsub!(/#{Mx[:gr_o]}T[h]?#{Mx[:tc_p]}.+?#{Mx[:gr_c]}/u,"[TABLE]\n") #tables #CHECK should take whole table - string.gsub!(/#{Mx[:tc_o]}#{Mx[:tc_p]}#{Mx[:tc_p]}\d+(.+)#{Mx[:tc_c]}/u,'\1') #tables - string.gsub!(/#{Mx[:tc_p]}#{Mx[:tc_p]}\d+#{Mx[:tc_p]}/u,' ') #tables - string.gsub!(/#{Mx[:tc_p]}/u,' ') #tables tidy later - string.gsub!(/<.+?>/,'') - string.gsub!(/#{Mx[:lnk_o]}.+?\.(?:png|jpg|gif).+?#{Mx[:lnk_c]}(?:https?|file|ftp)\\\:\S+ /,' [image] ') # else image names found in search - string.gsub!(/\s\s+/,' ') - string.strip! + def strip_markup(str) #define rules, make same as in dal clean + str.gsub!(/#{Mx[:fa_superscript_o]}(\d+)#{Mx[:fa_superscript_c]}/,'[\1]') + str.gsub!(/#{Mx[:pa_o]}:i[1-9]#{Mx[:pa_c]}/,'') + str.gsub!(/(?: \\;|#{Mx[:nbsp]})+/,' ') + str.gsub!(/#{Mx[:gr_o]}T[h]?#{Mx[:tc_p]}.+?#{Mx[:gr_c]}/u,"[TABLE]\n") #tables #CHECK should take whole table + str.gsub!(/#{Mx[:tc_o]}#{Mx[:tc_p]}#{Mx[:tc_p]}\d+(.+)#{Mx[:tc_c]}/u,'\1') #tables + str.gsub!(/#{Mx[:tc_p]}#{Mx[:tc_p]}\d+#{Mx[:tc_p]}/u,' ') #tables + str.gsub!(/#{Mx[:tc_p]}/u,' ') #tables tidy later + str.gsub!(/<.+?>/,'') + str.gsub!(/#{Mx[:lnk_o]}.+?\.(?:png|jpg|gif).+?#{Mx[:lnk_c]}(?:https?|file|ftp)\\\:\S+ /,' [image] ') # else image names found in search + str.gsub!(/\s\s+/,' ') + str.strip! end #% import into database tables def pf_db_import_transaction_open @@ -386,7 +393,8 @@ module SiSU_DB_import #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,@opt,@file) - t.tuple + tuple=t.tuple + tuple end def db_import_documents(dal_array) #% import documents - populate main database table #% import into substantive database tables (tuple) @@ -395,6 +403,7 @@ module SiSU_DB_import @en,@en_ast,@en_pls=[],[],[] @col[:en_a]=nil @col[:en_z]=nil + @tuple_array=[] dal_array.each do |data| #data.gsub!(/<[biu]>(.+?)<\/[biu]>/,'\1') # remove bold, italics, underscore data.gsub!(/#{Mx[:fa_bold_o]}(.+?)#{Mx[:fa_bold_c]}/,'\1') @@ -436,7 +445,7 @@ module SiSU_DB_import 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,@file) - t.tuple + @tuple_array << t.tuple case @col[:lev] when /1/; @col[:lv1]+=1 when /2/; @col[:lv2]+=1 @@ -478,7 +487,7 @@ module SiSU_DB_import 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,@file) - t.tuple + @tuple_array << t.tuple @col[:lev]=@col[:plaintext]=@col[:body]='' elsif data[/^#{Mx[:lv_o]}5:\S*?#{Mx[:lv_c]}\s*(.+?)#{Mx[:id_o]}~(\d+);((?:\w|[0-6]:)\d+);(\w\d+)#{Mx[:id_c]}#{Mx[:id_o]}([0-9a-f]{#{@@dl}}):([0-9a-f]{#{@@dl}})#{Mx[:id_c]}/] # header lev5 seg level txt,@col[:ocn],@col[:ocnd],@col[:ocns],@col[:digest_clean],@col[:digest_all]=$1,$2,$3,$4,$5,$6 @@ -518,7 +527,7 @@ module SiSU_DB_import 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,@file) - t.tuple + @tuple_array << t.tuple @col[:lev]=@col[:plaintext]=@col[:body]='' elsif data[/^#{Mx[:lv_o]}6:\S*?#{Mx[:lv_c]}\s*(.+?)#{Mx[:id_o]}~(\d+);((?:\w|[0-6]:)\d+);(\w\d+)#{Mx[:id_c]}#{Mx[:id_o]}([0-9a-f]{#{@@dl}}):([0-9a-f]{#{@@dl}})#{Mx[:id_c]}/] # header lev6 seg level txt,@col[:ocn],@col[:ocnd],@col[:ocns],@col[:digest_clean],@col[:digest_all]=$1,$2,$3,$4,$5,$6 @@ -558,7 +567,7 @@ module SiSU_DB_import 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,@file) - t.tuple + @tuple_array << t.tuple @col[:lev]=@col[:plaintext]=@col[:body]='' else #% regular text @col[:lid]+=1 @@ -609,7 +618,7 @@ module SiSU_DB_import @col[:plaintext]=@col[:body].dup strip_markup(@col[:plaintext]) t=SiSU_DB_tuple::Load_documents.new(@conn,@col,@opt,@file) - t.tuple + @tuple_array << t.tuple @en,@en_ast,@en_pls=[],[],[] @col[:en_a]=@col[:en_z]=nil @col[:lev]=@col[:plaintext]=@col[:body]='' @@ -649,7 +658,7 @@ module SiSU_DB_import :hash => digest_clean } t=SiSU_DB_tuple::Load_endnotes.new(@conn,en,@opt,@file) - t.tuple + @tuple_array << t.tuple end end end @@ -691,7 +700,7 @@ module SiSU_DB_import :hash => digest_clean } t=SiSU_DB_tuple::Load_endnotes.new(@conn,en,@opt,@file) - t.tuple + @tuple_array << t.tuple end end end @@ -732,7 +741,7 @@ module SiSU_DB_import :hash => digest_clean } t=SiSU_DB_tuple::Load_endnotes.new(@conn,en,@opt,@file) - t.tuple + @tuple_array << t.tuple end end end @@ -743,6 +752,7 @@ module SiSU_DB_import rescue; SiSU_Errors::Info_error.new($!,$@,@opt.cmd,@opt.fns).error ensure end + @tuple_array end def endnotes(txt) @txt=txt @@ -865,10 +875,11 @@ module SiSU_DB_import f[:sisupod],u[:sisupod]='sisupod,', "'#{base}/#@fnb/#{@opt.fns}.tgz'," end t=SiSU_DB_tuple::Load_urls.new(@conn,f,u,@@id_t,@opt,@file) - t.tuple + tuple=t.tuple rescue; SiSU_Errors::Info_error.new($!,$@,@opt.cmd,@opt.fns).error ensure end + tuple end end end -- cgit v1.2.3