diff options
| -rw-r--r-- | CHANGELOG | 16 | ||||
| -rw-r--r-- | lib/sisu/v0/cgi_sqlite.rb | 2 | ||||
| -rw-r--r-- | lib/sisu/v0/db_drop.rb | 64 | ||||
| -rw-r--r-- | lib/sisu/v0/db_import.rb | 48 | ||||
| -rw-r--r-- | lib/sisu/v0/db_load_tuple.rb | 6 | ||||
| -rw-r--r-- | lib/sisu/v0/db_remove.rb | 55 | ||||
| -rw-r--r-- | lib/sisu/v0/db_select.rb | 12 | ||||
| -rw-r--r-- | lib/sisu/v0/dbi.rb | 7 | ||||
| -rw-r--r-- | lib/sisu/v0/sysenv.rb | 14 |
9 files changed, 130 insertions, 94 deletions
@@ -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=<<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 - @conn.execute(%{#{sql_entry}}) + driver_sqlite3 = true if @conn.inspect.match(/^(.{10})/)[1] == @db.sqlite.conn_sqlite3.inspect.match(/^(.{10})/)[1] + del_id=if driver_sqlite3 + @conn.get_first_value(%{ SELECT tid FROM metadata WHERE filename LIKE '#{@opt.fns}'; }).to_i + else + x=@conn.select_one(%{ SELECT tid FROM metadata WHERE filename LIKE '#{@opt.fns}'; }) + del=if x; x.join.to_i else nil + end + end + if del_id + sql_entry=[ + "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}';", + ] + if driver_sqlite3 + @conn.transaction + sql_entry.each do |s| + @conn.execute(s) + end + @conn.commit if driver_sqlite3 + else + s=sql_entry.join(' ') + @conn.execute(s) + end 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}") @@ -92,17 +105,3 @@ SQL 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 675a89e4..87a3a5af 100644 --- a/lib/sisu/v0/db_select.rb +++ b/lib/sisu/v0/db_select.rb @@ -65,18 +65,18 @@ module SiSU_DB_select #@conn.execute("BEGIN;") #@sdb=Create.new(@conn) @file=sql_maintenance_file + @db=SiSU_Env::Info_db.new @sdb=SiSU_DB_DBI::Create.new(@opt,@conn,@file,@sql_type) # db_dbi.rb @sdb_index=SiSU_DB_DBI::Index.new(@opt,@conn,@file,@sql_type) # db_dbi.rb - @sdb_no=SiSU_DB_DBI::Drop.new(@opt,@conn,@file,@sql_type) # db_dbi.rb + @sdb_no=SiSU_DB_DBI::Drop.new(@opt,@conn,@db,@sql_type) # db_dbi.rb @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" + puts "\n#{@env.path.sqlite}/#{@opt.fns}.sql" if @sql_type =~/sqlite/ and @opt.cmd =~/M/ @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+') @@ -185,11 +185,7 @@ module SiSU_DB_select end end begin - #if @sql_type =~/sqlite/ - # #p @conn.methods.sort - # @conn.commit - # ##@conn.disconnect - #end + #@conn.commit if @sql_type =~/sqlite/ rescue; @sdb.output_dir? end end diff --git a/lib/sisu/v0/dbi.rb b/lib/sisu/v0/dbi.rb index 9576fa74..14b3e059 100644 --- a/lib/sisu/v0/dbi.rb +++ b/lib/sisu/v0/dbi.rb @@ -105,8 +105,7 @@ module SiSU_DBI end def read_psql begin - db,user,dbi,pass=@db.psql.db,@db.psql.user,@db.psql.dbi,@db.psql.password - @conn=DBI.connect(dbi,user,pass) + @conn=@db.psql.conn_dbi rescue if @opt.mod.inspect=~/--(createall|create)/ puts %{manually create the database: "#{db}" if it does not yet exist} @@ -120,8 +119,8 @@ module SiSU_DBI def read_sqlite begin sql_type='sqlite' - dbi=@db.sqlite.dbi - @conn=DBI.connect(dbi) + @conn=@db.sqlite.conn_sqlite3 + #@conn=@db.sqlite.conn_dbi #issue with dbi sqlite api, when fixed revert to using, KEEP rescue ensure end diff --git a/lib/sisu/v0/sysenv.rb b/lib/sisu/v0/sysenv.rb index ded14951..8356e5ee 100644 --- a/lib/sisu/v0/sysenv.rb +++ b/lib/sisu/v0/sysenv.rb @@ -2759,7 +2759,10 @@ WOK else "DBI:Pg:database=#{psql.db};port=#{psql.port}" end end - self + def conn_dbi + DBI.connect(psql.dbi,psql.user,psql.db) + end + self end def mysql def db @@ -2780,6 +2783,15 @@ WOK def dbi "DBI:SQLite3:#{sqlite.db}" #sqlite3 ? end + def sqlite3 + sqlite.db #sqlite3 ? + end + def conn_dbi + DBI.connect(sqlite.dbi) + end + def conn_sqlite3 + SQLite3::Database.new(sqlite.sqlite3) + end self end end |