db name, tables, columns, indexes changes, review (need another version bump 2.2.0)

* db (sql) table structure, further review and changes (hence breakage & version bump)
  * new pgsql db name prefix "sisu_v2b_"
  * new table column words in doc_objects & endnotes, VARCHAR 3000 to contain
    list of unique sorted words in object
  * increase use of VARCHAR
  * constants takes on related additions

* param, extensive db column size checks for metadata

1 files changed, 29 insertions, 23 deletions

diff --git a/lib/sisu/v2/db_indexes.rb b/lib/sisu/v2/db_indexes.rb
index 3cbcc20c..fb5ddd76 100644
--- a/lib/sisu/v2/db_indexes.rb
+++ b/lib/sisu/v2/db_indexes.rb
@@ -73,38 +73,44 @@ module SiSU_DB_index
       def base
         print "\n          create documents common indexes\n" unless @opt.cmd =~/q/
         sql_arr=[
-          %{CREATE INDEX object_nr ON doc_objects(ocn);},
-          %{CREATE INDEX digest_clean ON doc_objects(digest_clean);},
-          %{CREATE INDEX digest_all ON doc_objects(digest_all);},
-          %{CREATE INDEX lev1 ON doc_objects(lev1);},
-          %{CREATE INDEX lev2 ON doc_objects(lev2);},
-          %{CREATE INDEX lev3 ON doc_objects(lev3);},
-          %{CREATE INDEX lev4 ON doc_objects(lev4);},
-          %{CREATE INDEX lev5 ON doc_objects(lev5);},
-          %{CREATE INDEX lev6 ON doc_objects(lev6);},
-          %{CREATE INDEX endnote_nr ON endnotes(nr);},
-          %{CREATE INDEX digest_en ON endnotes(digest_clean);},
-          %{CREATE INDEX endnote_nr_asterisk ON endnotes_asterisk(nr);},
-          %{CREATE INDEX endnote_asterisk ON endnotes_asterisk(clean);},
-          %{CREATE INDEX digest_en_asterisk ON endnotes_asterisk(digest_clean);},
-          %{CREATE INDEX endnote_nr_plus ON endnotes_plus(nr);},
-          %{CREATE INDEX endnote_plus ON endnotes_plus(clean);},
-          %{CREATE INDEX digest_en_plus ON endnotes_plus(digest_clean);},
-          %{CREATE INDEX title ON metadata_and_text(title);},
-          %{CREATE INDEX filename ON metadata_and_text(filename)},
+          %{CREATE INDEX idx_text_words ON doc_objects(words);},
+          %{CREATE INDEX idx_ocn ON doc_objects(ocn);},
+          %{CREATE INDEX idx_digest_clean ON doc_objects(digest_clean);},
+          %{CREATE INDEX idx_digest_all ON doc_objects(digest_all);},
+          %{CREATE INDEX idx_lev1 ON doc_objects(lev1);},
+          %{CREATE INDEX idx_lev2 ON doc_objects(lev2);},
+          %{CREATE INDEX idx_lev3 ON doc_objects(lev3);},
+          %{CREATE INDEX idx_lev4 ON doc_objects(lev4);},
+          %{CREATE INDEX idx_lev5 ON doc_objects(lev5);},
+          %{CREATE INDEX idx_lev6 ON doc_objects(lev6);},
+          %{CREATE INDEX idx_endnote_words ON endnotes(words);},
+          %{CREATE INDEX idx_endnote_nr ON endnotes(nr);},
+          %{CREATE INDEX idx_digest_en ON endnotes(digest_clean);},
+          %{CREATE INDEX idx_endnote_words_asterisk ON endnotes_asterisk(words);},
+          %{CREATE INDEX idx_endnote_nr_asterisk ON endnotes_asterisk(nr);},
+          %{CREATE INDEX idx_endnote_asterisk ON endnotes_asterisk(clean);},
+          %{CREATE INDEX idx_digest_en_asterisk ON endnotes_asterisk(digest_clean);},
+          %{CREATE INDEX idx_endnote_words_plus ON endnotes_plus(words);},
+          %{CREATE INDEX idx_endnote_nr_plus ON endnotes_plus(nr);},
+          %{CREATE INDEX idx_endnote_plus ON endnotes_plus(clean);},
+          %{CREATE INDEX idx_digest_en_plus ON endnotes_plus(digest_clean);},
+          %{CREATE INDEX idx_title ON metadata_and_text(title);},
+          %{CREATE INDEX idx_author ON metadata_and_text(creator_author);},
+          %{CREATE INDEX idx_filename ON metadata_and_text(src_filename);},
+          %{CREATE INDEX idx_topics ON metadata_and_text(classify_topic_register)},
         ]
         conn_execute_array(sql_arr)
       end
       def text
-        print "\n          create documents text indexes\n" unless @opt.cmd =~/q/
+        print "\n          create documents TEXT indexes\n" unless @opt.cmd =~/q/
         sql_arr=[
-          %{CREATE INDEX clean ON doc_objects(clean);},
-          %{CREATE INDEX endnote ON endnotes(clean);}
+          %{CREATE INDEX idx_clean ON doc_objects(clean);},
+          %{CREATE INDEX idx_endnote ON endnotes(clean);}
         ]
         conn_execute_array(sql_arr)
       end
       base
-      @opt.cmd=~/D/ || ((@opt.mod=~/psql/) ? '' : text)
+      text #@opt.cmd=~/D/ || ((@opt.mod=~/psql/) ? '' : text)
     end
   end
 end