From 63c5a3cead1fb5cbd9b1bff653f269dce8d8052c Mon Sep 17 00:00:00 2001 From: Ralph Amissah Date: Tue, 20 Apr 2010 19:01:55 -0400 Subject: 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 --- lib/sisu/v2/db_drop.rb | 72 +++++++++++++++++++++++++++++--------------------- 1 file changed, 42 insertions(+), 30 deletions(-) (limited to 'lib/sisu/v2/db_drop.rb') diff --git a/lib/sisu/v2/db_drop.rb b/lib/sisu/v2/db_drop.rb index 7189da56..35d971af 100644 --- a/lib/sisu/v2/db_drop.rb +++ b/lib/sisu/v2/db_drop.rb @@ -133,37 +133,49 @@ module SiSU_DB_drop ensure end end + def conn_execute_array(sql_arr) + @conn.transaction do |conn| + sql_arr.each do |sql| + conn.execute(sql) + end + end + end def indexes #% drop all indexes - #@conn.do(%{ - # DROP INDEX object_nr ON doc_objects(ocn); - # DROP INDEX body ON doc_objects(body); - # DROP INDEX clean ON doc_objects(clean); - # DROP INDEX lev1 ON doc_objects(lev1); - # DROP INDEX lev2 ON doc_objects(lev2); - # DROP INDEX lev3 ON doc_objects(lev3); - # DROP INDEX lev4 ON doc_objects(lev4); - # DROP INDEX lev5 ON doc_objects(lev5); - # DROP INDEX lev6 ON doc_objects(lev6); - # DROP INDEX endnote_nr ON endnotes(nr); - # DROP INDEX endnote ON endnotes(body); - # DROP INDEX title ON metadata_and_text(title); - # DROP INDEX filename ON metadata_and_text(filename) - # /* - # DROP INDEX object_nr ON doc_objects(ocn) CASCADE; - # DROP INDEX body ON doc_objects(body) CASCADE; - # DROP INDEX clean ON doc_objects(clean) CASCADE; - # DROP INDEX lev1 ON doc_objects(lev1) CASCADE; - # DROP INDEX lev2 ON doc_objects(lev2) CASCADE; - # DROP INDEX lev3 ON doc_objects(lev3) CASCADE; - # DROP INDEX lev4 ON doc_objects(lev4) CASCADE; - # DROP INDEX lev5 ON doc_objects(lev5) CASCADE; - # DROP INDEX lev6 ON doc_objects(lev6) CASCADE; - # DROP INDEX endnote_nr ON endnotes(nr) CASCADE; - # DROP INDEX endnote ON endnotes(body) CASCADE; - # DROP INDEX title ON metadata_and_text(title) CASCADE; - # DROP INDEX filename ON metadata_and_text(filename) CASCADE - # */ - #}) + print "\n drop documents common indexes\n" unless @opt.cmd =~/q/ + sql_arr=[ + %{DROP INDEX idx_text_words;}, + %{DROP INDEX idx_title;}, + %{DROP INDEX idx_author;}, + %{DROP INDEX idx_filename;}, + %{DROP INDEX idx_topics;}, + %{DROP INDEX idx_ocn;}, + %{DROP INDEX idx_digest_clean;}, + %{DROP INDEX idx_digest_all;}, + %{DROP INDEX idx_lev1;}, + %{DROP INDEX idx_lev2;}, + %{DROP INDEX idx_lev3;}, + %{DROP INDEX idx_lev4;}, + %{DROP INDEX idx_lev5;}, + %{DROP INDEX idx_lev6;}, + %{DROP INDEX idx_endnote_words;}, + %{DROP INDEX idx_endnote_nr;}, + %{DROP INDEX idx_digest_en;}, + %{DROP INDEX idx_endnote_words_asterisk;}, + %{DROP INDEX idx_endnote_nr_asterisk;}, + %{DROP INDEX idx_endnote_asterisk;}, + %{DROP INDEX idx_digest_en_asterisk;}, + %{DROP INDEX idx_endnote_words_plus;}, + %{DROP INDEX idx_endnote_nr_plus;}, + %{DROP INDEX idx_endnote_plus;}, + %{DROP INDEX idx_digest_en_plus}, + ] + conn_execute_array(sql_arr) + print "\n drop documents TEXT indexes\n" unless @opt.cmd =~/q/ + sql_arr=[ + %{DROP INDEX idx_clean;}, + %{DROP INDEX idx_endnote}, + ] + conn_execute_array(sql_arr) end self end -- cgit v1.2.3 From d348ae931a17901eda839ef9501e13c9be51e913 Mon Sep 17 00:00:00 2001 From: Ralph Amissah Date: Tue, 20 Apr 2010 19:12:58 -0400 Subject: reverts unique words list column and indexes; fix for drop indexes; changelog & version update * reverts unique word list column on doc_objects and endnotes and associated indexes, may reintroduce at a later time * db_drop, drop TEXT indexes for sqlite only * update: changelog, version date --- lib/sisu/v2/db_drop.rb | 85 ++++++++++++++++++++++++++------------------------ 1 file changed, 44 insertions(+), 41 deletions(-) (limited to 'lib/sisu/v2/db_drop.rb') diff --git a/lib/sisu/v2/db_drop.rb b/lib/sisu/v2/db_drop.rb index 35d971af..edd08345 100644 --- a/lib/sisu/v2/db_drop.rb +++ b/lib/sisu/v2/db_drop.rb @@ -133,50 +133,53 @@ module SiSU_DB_drop ensure end end - def conn_execute_array(sql_arr) - @conn.transaction do |conn| - sql_arr.each do |sql| - conn.execute(sql) + def indexes + def conn_execute_array(sql_arr) + @conn.transaction do |conn| + sql_arr.each do |sql| + conn.execute(sql) + end end end + def base #% drop base indexes + print "\n drop documents common indexes\n" unless @opt.cmd =~/q/ + sql_arr=[ + %{DROP INDEX idx_title;}, + %{DROP INDEX idx_author;}, + %{DROP INDEX idx_filename;}, + %{DROP INDEX idx_topics;}, + %{DROP INDEX idx_ocn;}, + %{DROP INDEX idx_digest_clean;}, + %{DROP INDEX idx_digest_all;}, + %{DROP INDEX idx_lev1;}, + %{DROP INDEX idx_lev2;}, + %{DROP INDEX idx_lev3;}, + %{DROP INDEX idx_lev4;}, + %{DROP INDEX idx_lev5;}, + %{DROP INDEX idx_lev6;}, + %{DROP INDEX idx_endnote_nr;}, + %{DROP INDEX idx_digest_en;}, + %{DROP INDEX idx_endnote_nr_asterisk;}, + %{DROP INDEX idx_endnote_asterisk;}, + %{DROP INDEX idx_digest_en_asterisk;}, + %{DROP INDEX idx_endnote_nr_plus;}, + %{DROP INDEX idx_endnote_plus;}, + %{DROP INDEX idx_digest_en_plus}, + ] + conn_execute_array(sql_arr) + end + def text #% drop TEXT indexes, sqlite + print "\n drop documents TEXT indexes\n" unless @opt.cmd =~/q/ + sql_arr=[ + %{DROP INDEX idx_clean;}, + %{DROP INDEX idx_endnote}, + ] + conn_execute_array(sql_arr) + end + self end - def indexes #% drop all indexes - print "\n drop documents common indexes\n" unless @opt.cmd =~/q/ - sql_arr=[ - %{DROP INDEX idx_text_words;}, - %{DROP INDEX idx_title;}, - %{DROP INDEX idx_author;}, - %{DROP INDEX idx_filename;}, - %{DROP INDEX idx_topics;}, - %{DROP INDEX idx_ocn;}, - %{DROP INDEX idx_digest_clean;}, - %{DROP INDEX idx_digest_all;}, - %{DROP INDEX idx_lev1;}, - %{DROP INDEX idx_lev2;}, - %{DROP INDEX idx_lev3;}, - %{DROP INDEX idx_lev4;}, - %{DROP INDEX idx_lev5;}, - %{DROP INDEX idx_lev6;}, - %{DROP INDEX idx_endnote_words;}, - %{DROP INDEX idx_endnote_nr;}, - %{DROP INDEX idx_digest_en;}, - %{DROP INDEX idx_endnote_words_asterisk;}, - %{DROP INDEX idx_endnote_nr_asterisk;}, - %{DROP INDEX idx_endnote_asterisk;}, - %{DROP INDEX idx_digest_en_asterisk;}, - %{DROP INDEX idx_endnote_words_plus;}, - %{DROP INDEX idx_endnote_nr_plus;}, - %{DROP INDEX idx_endnote_plus;}, - %{DROP INDEX idx_digest_en_plus}, - ] - conn_execute_array(sql_arr) - print "\n drop documents TEXT indexes\n" unless @opt.cmd =~/q/ - sql_arr=[ - %{DROP INDEX idx_clean;}, - %{DROP INDEX idx_endnote}, - ] - conn_execute_array(sql_arr) - end + indexes.base + @opt.cmd=~/D/ || ((@opt.mod=~/psql/) ? '' : indexes.text) self end end -- cgit v1.2.3