v3: db, book_idx field & minor re-arrangement

6 files changed, 95 insertions, 70 deletions

diff --git a/data/doc/sisu/CHANGELOG_v3 b/data/doc/sisu/CHANGELOG_v3
index a48f5fe9..5168fdc7 100644
--- a/data/doc/sisu/CHANGELOG_v3
+++ b/data/doc/sisu/CHANGELOG_v3
@@ -82,6 +82,10 @@ http://www.jus.uio.no/sisu/pkg/src/sisu_3.3.3.orig.tar.xz
 * v3: dal_syntax, new syntax: =\\= page new & -\\- page break
   alias for <:pn> and <:pb> respectively
 
+* v3: db
+  * introduce book_idx
+  * minor re-arranging
+
 * v3: vim syntax sisu.vim, =\\= page new & -\\- page break
 
 * v3: vim ftplugin sisu.vim
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