From 8e40c8f68d80f05d5bcb8880b38caece4fdb3936 Mon Sep 17 00:00:00 2001 From: Ralph Amissah Date: Mon, 1 Oct 2012 16:31:23 -0400 Subject: v3: db, book_idx field & minor re-arrangement --- lib/sisu/v3/constants.rb | 38 ++++++++++++++-------------- lib/sisu/v3/db_create.rb | 45 ++++++++++++++++++++------------- lib/sisu/v3/db_import.rb | 60 ++++++++++++++++++++++++++------------------ lib/sisu/v3/db_load_tuple.rb | 8 +++--- lib/sisu/v3/db_remove.rb | 10 +++++--- 5 files changed, 91 insertions(+), 70 deletions(-) (limited to 'lib') diff --git a/lib/sisu/v3/constants.rb b/lib/sisu/v3/constants.rb index a786a8b4..b38e3313 100644 --- a/lib/sisu/v3/constants.rb +++ b/lib/sisu/v3/constants.rb @@ -203,25 +203,25 @@ Ep={ f_opf: 'content.opf', } Db={ - name_prefix: "SiSU#{SiSU_version_dir}e_", - name_prefix_db: "sisu_#{SiSU_version_dir}e_", - col_title: 800, - col_title_part: 400, - col_title_edition: 10, - col_name: 600, - col_creator_misc_short: 100, - col_language: 100, - col_language_char: 6, - col_date_text: 10, - col_classify_txt_long: 600, - col_classify_txt_short: 600, - col_classify_short: 200, - col_classify_identify: 256, - col_classify_library: 30, - col_classify_small: 16, - col_filename: 256, - col_digest: 64, - col_filesize: 10, + name_prefix: "SiSU#{SiSU_version_dir}f_", + name_prefix_db: "sisu_#{SiSU_version_dir}f_", + col_title: 800, + col_title_part: 400, + col_title_edition: 10, + col_name: 600, + col_creator_misc_short: 100, + col_language: 100, + col_language_char: 6, + col_date_text: 10, + col_classify_txt_long: 600, + col_classify_txt_short: 600, + col_classify_short: 200, + col_classify_identify: 256, + col_classify_library: 30, + col_classify_small: 16, + col_filename: 256, + col_digest: 64, + col_filesize: 10, col_info_note: 2500, } Gt={ diff --git a/lib/sisu/v3/db_create.rb b/lib/sisu/v3/db_create.rb index 9fe35193..e302a94f 100644 --- a/lib/sisu/v3/db_create.rb +++ b/lib/sisu/v3/db_create.rb @@ -99,7 +99,7 @@ module SiSU_DbCreate create tables metadata_and_text data import through ruby transfer } if @opt.cmd =~/[VM]/ - @conn.execute(%{ + create_metadata_and_text=%{ CREATE TABLE metadata_and_text ( tid BIGINT PRIMARY KEY, /* title */ @@ -201,8 +201,9 @@ module SiSU_DbCreate /* types CHAR(1) NULL, */ /* writing_focus_nationality VARCHAR(100) NULL, */ ); - }) - @comment.psql.metadata_and_text if @comment + } + @conn.execute(create_metadata_and_text) + @comment.psql.metadata_and_text if @comment end def doc_objects # create doc_objects base print %{ @@ -210,7 +211,7 @@ module SiSU_DbCreate create tables doc_objects data import through ruby transfer } if @opt.cmd =~/[VM]/ - @conn.execute(%{ + create_doc_objects=%{ CREATE TABLE doc_objects ( lid BIGINT PRIMARY KEY, metadata_tid BIGINT REFERENCES metadata_and_text, @@ -219,6 +220,7 @@ module SiSU_DbCreate ocns VARCHAR(6), clean TEXT NULL, body TEXT NULL, + book_idx TEXT NULL, seg VARCHAR(256) NULL, lev_an VARCHAR(1), lev SMALLINT NULL, @@ -242,8 +244,9 @@ module SiSU_DbCreate digest_all CHAR(#{@@dl}), types CHAR(1) NULL ); - }) - @comment.psql.doc_objects if @comment + } + @conn.execute(create_doc_objects) + @comment.psql.doc_objects if @comment end def endnotes print %{ @@ -251,7 +254,7 @@ module SiSU_DbCreate create tables endnotes data import through ruby transfer } if @opt.cmd =~/[VM]/ - @conn.execute(%{ + create_endnotes=%{ CREATE TABLE endnotes ( nid BIGINT PRIMARY KEY, document_lid BIGINT REFERENCES doc_objects, @@ -264,8 +267,9 @@ module SiSU_DbCreate digest_clean CHAR(#{@@dl}), metadata_tid BIGINT REFERENCES metadata_and_text ); - }) - @comment.psql.endnotes if @comment + } + @conn.execute(create_endnotes) + @comment.psql.endnotes if @comment end def endnotes_asterisk print %{ @@ -273,7 +277,7 @@ module SiSU_DbCreate create tables endnotes_asterisk data import through ruby transfer } if @opt.cmd =~/[VM]/ - @conn.execute(%{ + create_endnotes_asterisk=%{ CREATE TABLE endnotes_asterisk ( nid BIGINT PRIMARY KEY, document_lid BIGINT REFERENCES doc_objects, @@ -286,8 +290,9 @@ module SiSU_DbCreate digest_clean CHAR(#{@@dl}), metadata_tid BIGINT REFERENCES metadata_and_text ); - }) - @comment.psql.endnotes_asterisk if @comment + } + @conn.execute(create_endnotes_asterisk) + @comment.psql.endnotes_asterisk if @comment end def endnotes_plus print %{ @@ -295,7 +300,7 @@ module SiSU_DbCreate create tables endnotes_plus data import through ruby transfer } if @opt.cmd =~/[VM]/ - @conn.execute(%{ + create_endnotes_plus=%{ CREATE TABLE endnotes_plus ( nid BIGINT PRIMARY KEY, document_lid BIGINT REFERENCES doc_objects, @@ -308,8 +313,9 @@ module SiSU_DbCreate digest_clean CHAR(#{@@dl}), metadata_tid BIGINT REFERENCES metadata_and_text ); - }) - @comment.psql.endnotes_plus if @comment + } + @conn.execute(create_endnotes_plus) + @comment.psql.endnotes_plus if @comment end def urls # create doc_objects file links mapping print %{ @@ -318,7 +324,7 @@ module SiSU_DbCreate create tables urls data import through ruby transfer } if @opt.cmd =~/[VM]/ - @conn.execute(%{ + create_urls=%{ CREATE TABLE urls ( metadata_tid BIGINT REFERENCES metadata_and_text, plaintext varchar(512), @@ -338,8 +344,9 @@ module SiSU_DbCreate markup varchar(512), sisupod varchar(512) ); - }) - @comment.psql.urls if @comment + } + @conn.execute(create_urls) + @comment.psql.urls if @comment end self end @@ -483,6 +490,8 @@ module SiSU_DbCreate IS 'text object - substantive text: clean, stripped of markup';}, %{COMMENT ON COLUMN doc_objects.body IS 'text object - substantive text: light html markup';}, + %{COMMENT ON COLUMN doc_objects.book_idx + IS 'book index creation information for paragraph, if provided';}, %{COMMENT ON COLUMN doc_objects.lev1 IS 'document structure, level number 1';}, %{COMMENT ON COLUMN doc_objects.lev2 diff --git a/lib/sisu/v3/db_import.rb b/lib/sisu/v3/db_import.rb index 0cae5872..ae808cf8 100644 --- a/lib/sisu/v3/db_import.rb +++ b/lib/sisu/v3/db_import.rb @@ -278,11 +278,13 @@ module SiSU_DbImport @col[:lev],txt,@col[:ocn],@col[:lev_an],@col[:ocnd],@col[:ocns],@col[:t_of],@col[:t_is],@col[:node],@col[:parent],@col[:digest_clean],@col[:digest_all]=data.ln,data.obj,data.ocn,data.lv,data.odv,data.osp,data.of,data.is,data.node,data.parent,'','' @col[:lid]+=1 txt=endnotes(txt).extract_any - @col[:body]=SiSU_FormatShared::CSS_Format.new(@md,data).lev4_minus - @col[:body]=special_character_escape(@col[:body]) - @col[:plaintext]=@col[:body].dup - @col[:plaintext]=strip_markup(@col[:plaintext]) - @col[:plaintext]=clean_searchable_text(@col[:plaintext]) + body=SiSU_FormatShared::CSS_Format.new(@md,data).lev4_minus + @col[:body]=special_character_escape(body) + plaintext=@col[:body].dup + plaintext=strip_markup(plaintext) + @col[:plaintext]=clean_searchable_text(plaintext) + book_idx=data.idx ? data.idx : '' + @col[:book_idx]=clean_searchable_text(book_idx) if @en[0] then @en_a,@en_z=@en[0].first,@en[0].last end if @en_ast[0] then @en_a_asterisk,@en_z_asterisk=@en_ast[0].first,@en_ast[0].last @@ -312,11 +314,13 @@ module SiSU_DbImport @env=SiSU_Env::InfoEnv.new(@md.fns) @base_url="#{@env.url.root}/#{@md.fnb}/#{@hname}.html" txt=endnotes(txt).extract_any - @col[:body]=SiSU_FormatShared::CSS_Format.new(@md,data).lev4_plus - @col[:body]=special_character_escape(@col[:body]) - @col[:plaintext]=@col[:body].dup - @col[:plaintext]=strip_markup(@col[:plaintext]) - @col[:plaintext]=clean_searchable_text(@col[:plaintext]) + body=SiSU_FormatShared::CSS_Format.new(@md,data).lev4_plus + @col[:body]=special_character_escape(body) + plaintext=@col[:body].dup + plaintext=strip_markup(plaintext) + @col[:plaintext]=clean_searchable_text(plaintext) + book_idx=data.idx ? data.idx : '' + @col[:book_idx]=clean_searchable_text(book_idx) @en_a,@en_z=@en[0].first,@en[0].last if @en[0] @en_a_asterisk,@en_z_asterisk=@en_ast[0].first,@en_ast[0].last if @en_ast[0] @en_a_plus,@en_z_plus=@en_pls[0].first,@en_pls[0].last if @en_pls[0] @@ -342,11 +346,13 @@ module SiSU_DbImport @env=SiSU_Env::InfoEnv.new(@md.fns) @base_url="#{@env.url.root}/#{@md.fnb}/#{@hname}.html" txt=endnotes(txt).extract_any - @col[:body]=SiSU_FormatShared::CSS_Format.new(@md,data).lev4_plus - @col[:body]=special_character_escape(@col[:body]) - @col[:plaintext]=@col[:body].dup - @col[:plaintext]=strip_markup(@col[:plaintext]) - @col[:plaintext]=clean_searchable_text(@col[:plaintext]) + body=SiSU_FormatShared::CSS_Format.new(@md,data).lev4_plus + @col[:body]=special_character_escape(body) + plaintext=@col[:body].dup + plaintext=strip_markup(plaintext) + @col[:plaintext]=clean_searchable_text(plaintext) + book_idx=data.idx ? data.idx : '' + @col[:book_idx]=clean_searchable_text(book_idx) @en_a,@en_z=@en[0].first,@en[0].last if @en[0] @en_a_asterisk,@en_z_asterisk=@en_ast[0].first,@en_ast[0].last if @en_ast[0] @en_a_plus,@en_z_plus=@en_pls[0].first,@en_pls[0].last if @en_pls[0] @@ -370,11 +376,13 @@ module SiSU_DbImport @env=SiSU_Env::InfoEnv.new(@md.fns) @base_url="#{@env.url.root}/#{@md.fnb}/#{@hname}.html" txt=endnotes(txt).extract_any - @col[:body]=SiSU_FormatShared::CSS_Format.new(@md,data).lev4_plus - @col[:body]=special_character_escape(@col[:body]) - @col[:plaintext]=@col[:body].dup - @col[:plaintext]=strip_markup(@col[:plaintext]) - @col[:plaintext]=clean_searchable_text(@col[:plaintext]) + body=SiSU_FormatShared::CSS_Format.new(@md,data).lev4_plus + @col[:body]=special_character_escape(body) + plaintext=@col[:body].dup + plaintext=strip_markup(plaintext) + @col[:plaintext]=clean_searchable_text(plaintext) + book_idx=data.idx ? data.idx : '' + @col[:book_idx]=clean_searchable_text(book_idx) @en_a,@en_z=@en[0].first,@en[0].last if @en[0] @en_a_asterisk,@en_z_asterisk=@en_ast[0].first,@en_ast[0].last if @en_ast[0] @en_a_plus,@en_z_plus=@en_pls[0].first,@en_pls[0].last if @en_pls[0] @@ -410,7 +418,7 @@ module SiSU_DbImport @en_a,@en_z=@en[0].first,@en[0].last if @en[0] @en_a_asterisk,@en_z_asterisk=@en_ast[0].first,@en_ast[0].last if @en_ast[0] @en_a_plus,@en_z_plus=@en_pls[0].first,@en_pls[0].last if @en_pls[0] - @col[:body]=if data.is==:table + body=if data.is==:table SiSU_FormatShared::CSS_Format.new(@md,data).html_table elsif data.is==:code SiSU_FormatShared::CSS_Format.new(@md,data).code @@ -427,10 +435,12 @@ module SiSU_DbImport else SiSU_FormatShared::CSS_Format.new(@md,data).norm end - @col[:body]=special_character_escape(@col[:body]) - @col[:plaintext]=@col[:body].dup - @col[:plaintext]=strip_markup(@col[:plaintext]) - @col[:plaintext]=clean_searchable_text(@col[:plaintext]) + @col[:body]=special_character_escape(body) + plaintext=@col[:body].dup + plaintext=strip_markup(plaintext) + @col[:plaintext]=clean_searchable_text(plaintext) + book_idx=data.idx ? data.idx : '' + @col[:book_idx]=clean_searchable_text(book_idx) t=SiSU_DbTuple::LoadDocuments.new(@conn,@col,@opt,@file_maint) @tuple_array << t.tuple @en,@en_ast,@en_pls=[],[],[] diff --git a/lib/sisu/v3/db_load_tuple.rb b/lib/sisu/v3/db_load_tuple.rb index 39ea9022..f55ce66c 100644 --- a/lib/sisu/v3/db_load_tuple.rb +++ b/lib/sisu/v3/db_load_tuple.rb @@ -78,11 +78,11 @@ module SiSU_DbTuple end def tuple #% import line sql_entry=if @col[:en_a] - "INSERT INTO doc_objects (lid, metadata_tid, lev, lev_an, clean, body, ocn, ocnd, ocns, seg, lev1, lev2, lev3, lev4, lev5, lev6, en_a, en_z, t_of, t_is, node, parent, digest_clean, digest_all) " + - "VALUES (#{@col[:lid]}, #{@col[:tid]}, #{@col[:lev]}, '#{@col[:lev_an]}', '#{@col[:plaintext]}', '#{@col[:body]}', '#{@col[:ocn]}', '#{@col[:ocnd]}', '#{@col[:ocns]}', '#{@col[:seg]}', '#{@col[:lv1]}', '#{@col[:lv2]}', '#{@col[:lv3]}', '#{@col[:lv4]}', '#{@col[:lv5]}', '#{@col[:lv6]}', '#{@col[:en_a]}', '#{@col[:en_z]}', '#{@col[:t_of]}', '#{@col[:t_is]}', '#{@col[:node]}', '#{@col[:parent]}', '#{@col[:digest_clean]}', '#{@col[:digest_all]}');" + "INSERT INTO doc_objects (lid, metadata_tid, lev, lev_an, clean, body, book_idx, ocn, ocnd, ocns, seg, lev1, lev2, lev3, lev4, lev5, lev6, en_a, en_z, t_of, t_is, node, parent, digest_clean, digest_all) " + + "VALUES (#{@col[:lid]}, #{@col[:tid]}, #{@col[:lev]}, '#{@col[:lev_an]}', '#{@col[:plaintext]}', '#{@col[:body]}', '#{@col[:book_idx]}', '#{@col[:ocn]}', '#{@col[:ocnd]}', '#{@col[:ocns]}', '#{@col[:seg]}', '#{@col[:lv1]}', '#{@col[:lv2]}', '#{@col[:lv3]}', '#{@col[:lv4]}', '#{@col[:lv5]}', '#{@col[:lv6]}', '#{@col[:en_a]}', '#{@col[:en_z]}', '#{@col[:t_of]}', '#{@col[:t_is]}', '#{@col[:node]}', '#{@col[:parent]}', '#{@col[:digest_clean]}', '#{@col[:digest_all]}');" else - "INSERT INTO doc_objects (lid, metadata_tid, lev, lev_an, clean, body, ocn, ocnd, ocns, seg, lev1, lev2, lev3, lev4, lev5, lev6, t_of, t_is, node, parent, digest_clean, digest_all) " + - "VALUES (#{@col[:lid]}, #{@col[:tid]}, #{@col[:lev]}, '#{@col[:lev_an]}', '#{@col[:plaintext]}', '#{@col[:body]}', '#{@col[:ocn]}', '#{@col[:ocnd]}', '#{@col[:ocns]}', '#{@col[:seg]}', '#{@col[:lv1]}', '#{@col[:lv2]}', '#{@col[:lv3]}', '#{@col[:lv4]}', '#{@col[:lv5]}', '#{@col[:lv6]}', '#{@col[:t_of]}', '#{@col[:t_is]}', '#{@col[:node]}', '#{@col[:parent]}', '#{@col[:digest_clean]}', '#{@col[:digest_all]}');" + "INSERT INTO doc_objects (lid, metadata_tid, lev, lev_an, clean, body, book_idx, ocn, ocnd, ocns, seg, lev1, lev2, lev3, lev4, lev5, lev6, t_of, t_is, node, parent, digest_clean, digest_all) " + + "VALUES (#{@col[:lid]}, #{@col[:tid]}, #{@col[:lev]}, '#{@col[:lev_an]}', '#{@col[:plaintext]}', '#{@col[:body]}', '#{@col[:book_idx]}', '#{@col[:ocn]}', '#{@col[:ocnd]}', '#{@col[:ocns]}', '#{@col[:seg]}', '#{@col[:lv1]}', '#{@col[:lv2]}', '#{@col[:lv3]}', '#{@col[:lv4]}', '#{@col[:lv5]}', '#{@col[:lv6]}', '#{@col[:t_of]}', '#{@col[:t_is]}', '#{@col[:node]}', '#{@col[:parent]}', '#{@col[:digest_clean]}', '#{@col[:digest_all]}');" end if @opt.cmd =~/M/ if @opt.cmd =~/V/ diff --git a/lib/sisu/v3/db_remove.rb b/lib/sisu/v3/db_remove.rb index 6d481cb4..3371a9be 100644 --- a/lib/sisu/v3/db_remove.rb +++ b/lib/sisu/v3/db_remove.rb @@ -71,19 +71,21 @@ module SiSU_DbRemove : false end del_id=if driver_sqlite3 - @conn.get_first_value(%{ + remove_selected=%{ 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(remove_selected).to_i else - x=@conn.select_one(%{ + remove_selected=%{ 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(remove_selected) x ? (x.join.to_i) : nil end if del_id -- cgit v1.2.3