From 2d79257e0b2fe90e036fa6554cadb2d587128bc5 Mon Sep 17 00:00:00 2001 From: Ralph Amissah Date: Wed, 4 May 2011 23:27:02 -0400 Subject: v3: db, review, directory structure changed, database prefix renamed * language_document_char field made larger [consider renaming] * title, src_filename and language_document_char fields mandatory (title, title_main, src_filename, language_document_char) * review of unique key setting, now based on combination of filename and language (filename no longer sufficient with intro of language subdirectories) * [retest sqlite3] --- data/doc/sisu/CHANGELOG_v3 | 6 ++++++ lib/sisu/v3/constants.rb | 10 ++++------ lib/sisu/v3/db_columns.rb | 25 ++++++++++++------------- lib/sisu/v3/db_import.rb | 30 +++++++++++++++++++----------- lib/sisu/v3/db_remove.rb | 14 ++++++++++++-- 5 files changed, 53 insertions(+), 32 deletions(-) diff --git a/data/doc/sisu/CHANGELOG_v3 b/data/doc/sisu/CHANGELOG_v3 index dea1dd0b..ba783a33 100644 --- a/data/doc/sisu/CHANGELOG_v3 +++ b/data/doc/sisu/CHANGELOG_v3 @@ -32,6 +32,12 @@ http://www.jus.uio.no/sisu/pkg/src/sisu_3.0.8.orig.tar.gz * prog_text_translation, renamed from what was i18n, i18n name reused + * db, directory structure changed, database prefix renamed + * language_document_char field made larger + [consider renaming language_document_code or ..._id] + * title, src_filename and language_document_char fields mandatory + (title, title_main, src_filename, language_document_char) + * NOTE: cgi sample search form caught by Ruby/DBI libdbi-ruby1.9.1 reported reported as libdbd-pg-ruby1.9.1 bug #596103, where "select_all fails". The cgi sample search scripts for postgresql and sqlite3 are almost identical, diff --git a/lib/sisu/v3/constants.rb b/lib/sisu/v3/constants.rb index d94da654..57491a7c 100644 --- a/lib/sisu/v3/constants.rb +++ b/lib/sisu/v3/constants.rb @@ -147,20 +147,18 @@ Px[:lng_lst]=%w[am bg bn br ca cs cy da de el en eo es et eu fi fr ga gl he hi h Px[:lv1]= '*' Px[:lv2]= '=' Px[:lv3]= '=' -#Px[:lv2_3]= '=' Px[:lv4]= '-' Px[:lv5]= '.' Px[:lv6]= '.' -#Px[:lv5_6]= '.' -Db[:name_prefix]="SiSU#{SiSU_version_dir}c_" -Db[:name_prefix_db]="sisu_#{SiSU_version_dir}c_" +Db[:name_prefix]="SiSU#{SiSU_version_dir}d_" +Db[:name_prefix_db]="sisu_#{SiSU_version_dir}d_" Db[:col_title]=800 Db[:col_title_part]=400 Db[:col_title_edition]=10 Db[:col_name]=600 Db[:col_creator_misc_short]=100 Db[:col_language]=100 -Db[:col_language_char]=3 +Db[:col_language_char]=6 Db[:col_date_text]=10 Db[:col_classify_txt_long]=600 Db[:col_classify_txt_short]=600 @@ -312,7 +310,7 @@ see polyglossia for subset * es_GA (Spanish, as spoken in Gabon) * es_GT (Spanish, as spoken in Guatemala) * es_HN (Spanish, as spoken in Honduras) -* es_LA (Spanish, as spoken in Lao People's Democratic Republic) +* es_LA (Spanish, as spoken in Lao People''s Democratic Republic) * es_MX (Spanish, as spoken in Mexico) * es_NI (Spanish, as spoken in Nicaragua) * es_PA (Spanish, as spoken in Panama) diff --git a/lib/sisu/v3/db_columns.rb b/lib/sisu/v3/db_columns.rb index 8b8a0b3c..d8a6f3cf 100644 --- a/lib/sisu/v3/db_columns.rb +++ b/lib/sisu/v3/db_columns.rb @@ -64,6 +64,7 @@ module SiSU_DB_columns def initialize(md=nil) @md=md @db=SiSU_Env::Info_db.new #watch + @lang ||=SiSU_i18n::Languages.new if defined? md.opt.mod \ and md.opt.mod.inspect=~/import|update/ \ and FileTest.exist?(md.fns) @@ -98,7 +99,7 @@ module SiSU_DB_columns 'title' end def create_column - "#{name} VARCHAR(#{Db[:col_title]}) NULL," + "#{name} VARCHAR(#{Db[:col_title]}) NOT NULL," end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} @@ -120,7 +121,7 @@ module SiSU_DB_columns 'title_main' end def create_column - "#{name} VARCHAR(#{Db[:col_title_part]}) NULL," + "#{name} VARCHAR(#{Db[:col_title_part]}) NOT NULL," end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} @@ -237,9 +238,8 @@ module SiSU_DB_columns IS 'metadata document language [DC12]';} end def tuple - t=if defined? @md.title.language \ - and @md.title.language=~/\S+/ - txt=@md.title.language + t=if @lang.list[@md.opt.lng][:n] + txt=@lang.list[@md.opt.lng][:n] special_character_escape(txt) ["#{name}, ","'#{txt}', "] else ['',''] @@ -259,9 +259,9 @@ module SiSU_DB_columns IS 'metadata document language iso code';} end def tuple - t=if defined? @md.title.language_char \ - and @md.title.language_char=~/\S+/ - txt=@md.title.language_char + t=if defined? @md.opt.lng \ + and @md.opt.lng=~/\S+/ + txt=@md.opt.lng special_character_escape(txt) ["#{name}, ","'#{txt}', "] else ['',''] @@ -599,9 +599,8 @@ module SiSU_DB_columns IS 'metadata document language';} end def tuple - t=if defined? @md.language.document \ - and @md.language.document=~/\S+/ - txt=@md.language.document + t=if @lang.list[@md.opt.lng][:n] + txt=@lang.list[@md.opt.lng][:n] special_character_escape(txt) ["#{name}, ","'#{txt}', "] else ['',''] @@ -614,7 +613,7 @@ module SiSU_DB_columns 'language_document_char' end def create_column - "#{name} VARCHAR(#{Db[:col_language_char]}) NULL," + "#{name} VARCHAR(#{Db[:col_language_char]}) NOT NULL," end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} @@ -1833,7 +1832,7 @@ module SiSU_DB_columns 'src_filename' end def create_column - "#{name} VARCHAR(#{Db[:col_filename]}) NULL," + "#{name} VARCHAR(#{Db[:col_filename]}) NOT NULL," end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} diff --git a/lib/sisu/v3/db_import.rb b/lib/sisu/v3/db_import.rb index 45bb37f3..541399a9 100644 --- a/lib/sisu/v3/db_import.rb +++ b/lib/sisu/v3/db_import.rb @@ -96,17 +96,17 @@ module SiSU_DB_import @col[:lid] ||=0 @col[:lid]=@driver_sqlite3 \ ? @conn.execute( sql ).join.to_i \ - : @conn.execute( sql ) { |x| x.fetch_all.to_s.to_i } + : @conn.execute( sql ) { |x| x.fetch_all.flatten[0] } 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=@driver_sqlite3 \ ? @conn.execute( sql ).join.to_i \ - : @id_n=@conn.execute( sql ) { |x| x.fetch_all.to_s.to_i } + : @id_n=@conn.execute( sql ) { |x| x.fetch_all.flatten[0] } + @id_n ||=0 rescue puts "#{__FILE__}:#{__LINE__}" if @opt.cmd =~/M/ end @@ -121,7 +121,12 @@ module SiSU_DB_import SiSU_Screen::Ansi.new(@opt.cmd,"#{@db.psql.db}::#{@opt.fns}").puts_blue if @opt.cmd =~/vVM/ SiSU_Screen::Ansi.new(@opt.cmd,'Marshal Load',@fnc).puts_grey if @opt.cmd =~/v/ #% - select_first_match=%{ SELECT metadata_and_text.tid FROM metadata_and_text WHERE metadata_and_text.src_filename = '#{@opt.fns}' AND metadata_and_text.language_document_char = '#{@opt.lng}'; } + select_first_match=%{ + SELECT metadata_and_text.tid + FROM metadata_and_text + WHERE metadata_and_text.src_filename = '#{@opt.fns}' + AND metadata_and_text.language_document_char = '#{@opt.lng}' + ;} file_exist=@sql_type=~/sqlite/ \ ? @conn.get_first_value(select_first_match) \ : @conn.select_one(select_first_match) @@ -188,7 +193,7 @@ module SiSU_DB_import 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}" + 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}" end end end @@ -216,18 +221,18 @@ module SiSU_DB_import #@tp[:title]=@md.title.full #special_character_escape(@tp[:title]) #@tp[:title_f],@tp[:title_i]='title, ',"'#{@tp[:title]}', " - sql='SELECT MAX(tid) FROM metadata_and_text' + sql='SELECT MAX(tid) FROM metadata_and_text;' begin @@id_t ||=0 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 } + else + @conn.execute( sql ) { |x| x.fetch_all.flatten[0] } 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 @title: puts %{\n#{@cX.grey}Processing file number#{@cX.off}: #{@cX.green}#{@@id_t}#{@@cX.off}} if @opt.cmd =~/vVM/ end @@ -422,7 +427,8 @@ module SiSU_DB_import txt=%{\n\nLARGE TEXT BLOCK OMITTED\n\n} end if txt - en={ type: 'endnotes', + en={ + type: 'endnotes', id: @id_n, lid: @col[:lid], nr: nr, @@ -460,7 +466,8 @@ module SiSU_DB_import txt=%{\n\nLARGE TEXT BLOCK OMITTED\n\n} end if txt - en={ type: 'endnotes_asterisk', + en={ + type: 'endnotes_asterisk', id: @id_n, lid: @col[:lid], nr: nr, @@ -498,7 +505,8 @@ module SiSU_DB_import txt=%{\n\nLARGE TEXT BLOCK OMITTED\n\n} end if txt - en={ type: 'endnotes_plus', + en={ + type: 'endnotes_plus', id: @id_n, lid: @col[:lid], nr: nr, diff --git a/lib/sisu/v3/db_remove.rb b/lib/sisu/v3/db_remove.rb index 7644ad52..cfa19c9f 100644 --- a/lib/sisu/v3/db_remove.rb +++ b/lib/sisu/v3/db_remove.rb @@ -72,9 +72,19 @@ module SiSU_DB_remove : false end del_id=if driver_sqlite3 - @conn.get_first_value(%{ SELECT tid FROM metadata_and_text WHERE src_filename = '#{@opt.fns}' AND metadata_and_text.language_document_char = '#{@opt.lng}'; }).to_i + @conn.get_first_value(%{ + SELECT tid + FROM metadata_and_text + WHERE src_filename = '#{@opt.fns}' + AND metadata_and_text.language_document_char = '#{@opt.lng}' + ;}).to_i else - x=@conn.select_one(%{ SELECT metadata_and_text.tid FROM metadata_and_text WHERE metadata_and_text.src_filename = '#{@opt.fns}' AND metadata_and_text.language_document_char = '#{@opt.lng}'; }) + x=@conn.select_one(%{ + SELECT metadata_and_text.tid + FROM metadata_and_text + WHERE metadata_and_text.src_filename = '#{@opt.fns}' + AND metadata_and_text.language_document_char = '#{@opt.lng}' + ;}) x ? (x.join.to_i) : nil end if del_id -- cgit v1.2.3