From 71223cf6cd035b3e395f6d4c3e4c41b79ed29510 Mon Sep 17 00:00:00 2001
From: Ralph Amissah <ralph@amissah.com>
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=<<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
--
cgit v1.2.3