From 71223cf6cd035b3e395f6d4c3e4c41b79ed29510 Mon Sep 17 00:00:00 2001 From: Ralph Amissah Date: Mon, 22 Dec 2008 01:08:06 -0500 Subject: sqlite3 fix, populate do using rb sqlite3 driver directly (rb dbi problematic at present) sqlite fix, change ruby driver used to populate sisu sqlite3 db to rb sqlite3 for the time being. Use rb sqlite3 driver directly rather (than more convenient when working) rb dbi interface to populate content. Used to bypass problems with rb dbi sqlite3 interface noted in sisu 0.66.2 (2008-04-25) and 0.66.3 (2008-05-11) that have have persisted (apparently worked at end 2007 (v.0.62.4) and start of 2008 (v.0.64.0) * sqlite dropall, fix * fix to auto-generated sqlite cgi script sample, only works if at least one existing populated sisu sqlite database is found during the generation process --- CHANGELOG | 16 ++++++++--- lib/sisu/v0/cgi_sqlite.rb | 2 +- lib/sisu/v0/db_drop.rb | 64 +++++++++++++++++++++++++++----------------- lib/sisu/v0/db_import.rb | 48 ++++++++++++++++++--------------- lib/sisu/v0/db_load_tuple.rb | 6 ++--- lib/sisu/v0/db_remove.rb | 55 +++++++++++++++++++------------------ lib/sisu/v0/db_select.rb | 12 +++------ lib/sisu/v0/dbi.rb | 7 +++-- lib/sisu/v0/sysenv.rb | 14 +++++++++- 9 files changed, 130 insertions(+), 94 deletions(-) diff --git a/CHANGELOG b/CHANGELOG index 7b1b22dd..3c799700 100644 --- a/CHANGELOG +++ b/CHANGELOG @@ -9,7 +9,7 @@ Reverse Chronological: %% STABLE MANIFEST -%% sisu_0.70.3.orig.tar.gz (2008-12-16:50/2) +%% sisu_0.70.3.orig.tar.gz (2008-12-24:51/3) http://www.jus.uio.no/sisu/pkg/src/sisu_0.70.3.orig.tar.gz sisu_0.70.3.orig.tar.gz sisu_0.70.3-1.dsc @@ -17,8 +17,18 @@ http://www.jus.uio.no/sisu/pkg/src/sisu_0.70.3.orig.tar.gz * html and css, cosmetic changes (and the closing of a tag) - * sqlite module remains broken, inconsistent ruby dbi api - made explicit - when run + * db/sql + * sqlite fix, change ruby driver used to populate sisu sqlite3 db to rb + sqlite3 for the time being. Use rb sqlite3 driver directly rather (than + more convenient when working) rb dbi interface to populate content. Used + to bypass problems with rb dbi sqlite3 interface noted in sisu 0.66.2 + (2008-04-25) and 0.66.3 (2008-05-11) that have have persisted (apparently + worked at end 2007 (v.0.62.4) and start of 2008 (v.0.64.0) + * sqlite dropall, fix + * fix to auto-generated sqlite cgi script sample, only works if at least + one existing populated sisu sqlite database is found during the + generation process + * postgresql, much shared code, however, operation should be unchanged %% sisu_0.70.2.orig.tar.gz (2008-12-16:50/2) http://www.jus.uio.no/sisu/pkg/src/sisu_0.70.2.orig.tar.gz diff --git a/lib/sisu/v0/cgi_sqlite.rb b/lib/sisu/v0/cgi_sqlite.rb index 08897f54..56bcecf6 100644 --- a/lib/sisu/v0/cgi_sqlite.rb +++ b/lib/sisu/v0/cgi_sqlite.rb @@ -94,7 +94,7 @@ module SiSU_CGI_sqlite serve.each do |x| f3 << %{ when /SiSU_#{x}/; "#{@env.path.webserv}/#{x}/sisu_sqlite.db"\n} end - f3 << " end\n" + f3 << "else #{@env.path.webserv}/#{serve[0]}/sisu_sqlite.db\n end\n" if FileTest.writable?('.') output=File.open('sisu_sqlite.cgi','w') output << header0 << header1 << header_desc << header2 << f1 << buttons1 << buttons2 << search_request << search_statement << search_statement_common << search_query1 << @common.pages << search_query2 << @common.tail << @common.main1 << f2 << f3 << dbi_connect << @common.main2 diff --git a/lib/sisu/v0/db_drop.rb b/lib/sisu/v0/db_drop.rb index f41325b2..93b27e6d 100644 --- a/lib/sisu/v0/db_drop.rb +++ b/lib/sisu/v0/db_drop.rb @@ -60,40 +60,56 @@ =end module SiSU_DB_drop class Drop - def initialize(opt,conn,file,sql_type='') - @opt,@conn,@file,@sql_type=opt,conn,file,sql_type + require "#{SiSU_lib}/response" + def initialize(opt,conn,db_info,sql_type='') + @opt,@conn,@db_info,@sql_type=opt,conn,db_info,sql_type + @ans=SiSU_Response::Response.new + case @sql_type + when /sqlite/ + cascade='' + else + cascade='CASCADE' + end + @drop_table=[ + "DROP TABLE metadata #{cascade};", + "DROP TABLE documents #{cascade};", + "DROP TABLE urls #{cascade};", + "DROP TABLE endnotes #{cascade};", + "DROP TABLE endnotes_asterisk #{cascade};", + "DROP TABLE endnotes_plus #{cascade};", + ] end def drop def tables #% drop all tables begin + msg_sqlite="as not all disk space is recovered after dropping the database << #{@db_info.sqlite.db} >>, you may be better off deleting the file, and recreating it as necessary" case @sql_type when /sqlite/ - cascade='' - commit=@conn.commit + @conn.transaction + @drop_table.each do |d| + @conn.execute(d) + end + @conn.commit + puts msg_sqlite + ans=@ans.response?('remove sql database?') + if ans and File.exist?(@db_info.sqlite.db) + File.unlink(@db_info.sqlite.db) + end else - cascade='CASCADE' - commit='' + @conn.do(@drop_table.join('')) end - @conn.do(%{ - DROP TABLE metadata #{cascade}; - DROP TABLE documents #{cascade}; - DROP TABLE urls #{cascade}; - DROP TABLE endnotes #{cascade}; - DROP TABLE endnotes_asterisk #{cascade}; - DROP TABLE endnotes_plus #{cascade}; - }) - commit rescue - @conn.do(%{ - DROP TABLE endnotes; - DROP TABLE endnotes_asterisk; - DROP TABLE endnotes_plus; - DROP TABLE urls #{cascade}; - DROP TABLE documents #{cascade}; - DROP TABLE metadata #{cascade}; - }) + case @sql_type + when /sqlite/ + #system("rm -vi #{@db_info.sqlite.db}") + ans=@ans.response?('remove sql database?') + if ans and File.exist?(@db_info.sqlite.db); File.unlink(@db_info.sqlite.db) + end + else + @conn.do(@drop_table.join('')) + end ensure - commit + #commit end end def indexes #% drop all indexes diff --git a/lib/sisu/v0/db_import.rb b/lib/sisu/v0/db_import.rb index bcdfc497..03f72065 100644 --- a/lib/sisu/v0/db_import.rb +++ b/lib/sisu/v0/db_import.rb @@ -8,7 +8,7 @@ * Author: Ralph Amissah * Copyright: (C) 1997, 1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, - 2007, 2008 Ralph Amissah All Rights Reserved. + 2007, 2008, 2009 Ralph Amissah All Rights Reserved. * License: GPL 3 or later: @@ -62,7 +62,7 @@ module SiSU_DB_import require "#{SiSU_lib}/db_columns" require "#{SiSU_lib}/db_load_tuple" require "#{SiSU_lib}/shared_html_lite" - #require 'sqlite3' + require 'sqlite3' class Import < SiSU_DB_columns::Column_size include SiSU_Param include SiSU_Screen @@ -85,18 +85,30 @@ module SiSU_DB_import @col=Hash.new('') @col[:ocn]='' @counter={} + @db=SiSU_Env::Info_db.new + @driver_sqlite3=false + @driver_sqlite3=true if @conn.inspect.match(/^(.{10})/)[1] == @db.sqlite.conn_sqlite3.inspect.match(/^(.{10})/)[1] sql='SELECT MAX(lid) FROM documents' begin @col[:lid] ||=0 - @col[:lid]=@conn.execute( sql ) { |x| x.fetch_all.to_s.to_i } + @col[:lid]=if @driver_sqlite3 + @conn.execute( sql ).join.to_i + else @conn.execute( sql ) { |x| x.fetch_all.to_s.to_i } + end rescue + puts "#{__FILE__} #{__LINE__}" if @opt.cmd =~/M/ end @col[:lid] =0 if @col[:lid].nil? or @col[:lid].to_s.empty? sql='SELECT MAX(nid) FROM endnotes' begin @id_n ||=0 - @id_n=@conn.execute( sql ) { |x| x.fetch_all.to_s.to_i } + @id_n=if @driver_sqlite3 + @conn.execute( sql ).join.to_i + else + @id_n=@conn.execute( sql ) { |x| x.fetch_all.to_s.to_i } + end rescue + 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 @@ -112,15 +124,8 @@ module SiSU_DB_import tell.print_grey if @opt.cmd =~/v/ case @sql_type when /sqlite/ #fix logic for sqlite ! -#sqlite watch -unless @opt.cmd =~/[MVv]/ - puts "\nSiSU's sqlite module for the time being broken: inconsistent ruby dbi api for sqlite3, (unable to begin and commit transaction)\n".upcase - p @conn.methods.sort - #p "here #{__FILE__} #{__LINE__}" - exit -else + #sqlite watch #pf_db_import_transaction_open if @opt =~/M/ - #@conn.begin db_import_metadata db_import_documents(@dal_array) db_import_urls(@dal_array,@fnm) #import OID on/off @@ -128,15 +133,10 @@ else #@conn.commit #@conn.close #@conn.disconnect - puts "\nSiSU's sqlite module for the time being broken: inconsistent ruby dbi api for sqlite3, (unable to begin and commit transaction)\n".upcase - p @conn.methods.sort - if @opt.cmd =~/M/ - puts "\n" + @conn.inspect - puts "\nat #{__FILE__} #{__LINE__}" - end - exit -end -#sqlite watch + if @opt.cmd =~/M/ + puts "\n" + @conn.inspect + puts "\nat #{__FILE__} #{__LINE__}" + end else file_exist=@conn.select_one(%{ SELECT metadata.tid FROM metadata WHERE metadata.filename ~ '#{@opt.fns}'; }) unless file_exist @@ -191,9 +191,13 @@ end sql='SELECT MAX(tid) FROM metadata' begin @@id_t ||=0 - id_t=@conn.execute( sql ) { |x| x.fetch_all.to_s.to_i } + id_t=if @driver_sqlite3 + @conn.execute( sql ).join.to_i # { |x| id_t=x.join.to_i } + else @conn.execute( sql ) { |x| x.fetch_all.to_s.to_i } + end @@id_t=id_t if id_t rescue + puts "#{__FILE__} #{__LINE__}" if @opt.cmd =~/M/ 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 diff --git a/lib/sisu/v0/db_load_tuple.rb b/lib/sisu/v0/db_load_tuple.rb index 512d5933..c5169b4e 100644 --- a/lib/sisu/v0/db_load_tuple.rb +++ b/lib/sisu/v0/db_load_tuple.rb @@ -121,7 +121,7 @@ module SiSU_DB_tuple 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}}) + else @conn.execute(sql_entry) end end end @@ -133,7 +133,7 @@ module SiSU_DB_tuple 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}}) + else @conn.execute(sql_entry) end end end @@ -145,7 +145,7 @@ module SiSU_DB_tuple 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}}) + else @conn.execute(sql_entry) end end end diff --git a/lib/sisu/v0/db_remove.rb b/lib/sisu/v0/db_remove.rb index fef4e797..a869bd24 100644 --- a/lib/sisu/v0/db_remove.rb +++ b/lib/sisu/v0/db_remove.rb @@ -67,22 +67,35 @@ module SiSU_DB_remove @db=SiSU_Env::Info_db.new end def remove - del=@conn.select_one(%{ SELECT tid FROM metadata WHERE filename LIKE '#{@opt.fns}'; }) - if del - del_id=del.join - #@conn.execute("BEGIN") - sql_entry=<