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_columns.rb | 158 ++++++++++++++++++++++++++++++---------------- 1 file changed, 103 insertions(+), 55 deletions(-) (limited to 'lib/sisu/v2/db_columns.rb') diff --git a/lib/sisu/v2/db_columns.rb b/lib/sisu/v2/db_columns.rb index ee66c59e..0c2eb367 100644 --- a/lib/sisu/v2/db_columns.rb +++ b/lib/sisu/v2/db_columns.rb @@ -208,7 +208,7 @@ module SiSU_DB_columns 'title_note' end def create_column - "#{name} TEXT NULL," + "#{name} VARCHAR(#{Db[:col_info_note]}) NULL," end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} @@ -1119,7 +1119,7 @@ module SiSU_DB_columns 'rights' end def create_column - "#{name} TEXT NULL," + "#{name} VARCHAR(#{Db[:col_info_note]}) NULL," end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} @@ -1141,7 +1141,7 @@ module SiSU_DB_columns 'rights_copyright_text' end def create_column - "#{name} TEXT NULL," + "#{name} VARCHAR(#{Db[:col_info_note]}) NULL," end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} @@ -1163,7 +1163,7 @@ module SiSU_DB_columns 'rights_copyright_translation' end def create_column - "#{name} TEXT NULL," + "#{name} VARCHAR(#{Db[:col_info_note]}) NULL," end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} @@ -1185,7 +1185,7 @@ module SiSU_DB_columns 'rights_copyright_illustrations' end def create_column - "#{name} TEXT NULL," + "#{name} VARCHAR(#{Db[:col_info_note]}) NULL," end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} @@ -1207,7 +1207,7 @@ module SiSU_DB_columns 'rights_copyright_photographs' end def create_column - "#{name} TEXT NULL," + "#{name} VARCHAR(#{Db[:col_info_note]}) NULL," end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} @@ -1229,7 +1229,7 @@ module SiSU_DB_columns 'rights_copyright_preparation' end def create_column - "#{name} TEXT NULL," + "#{name} VARCHAR(#{Db[:col_info_note]}) NULL," end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} @@ -1251,7 +1251,7 @@ module SiSU_DB_columns 'rights_copyright_digitization' end def create_column - "#{name} TEXT NULL," + "#{name} VARCHAR(#{Db[:col_info_note]}) NULL," end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} @@ -1273,7 +1273,7 @@ module SiSU_DB_columns 'rights_copyright_audio' end def create_column - "#{name} TEXT NULL," + "#{name} VARCHAR(#{Db[:col_info_note]}) NULL," end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} @@ -1295,7 +1295,7 @@ module SiSU_DB_columns 'rights_copyright_video' end def create_column - "#{name} TEXT NULL," + "#{name} VARCHAR(#{Db[:col_info_note]}) NULL," end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} @@ -1317,7 +1317,7 @@ module SiSU_DB_columns 'rights_license' end def create_column - "#{name} TEXT NULL," + "#{name} VARCHAR(#{Db[:col_info_note]}) NULL," end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} @@ -1355,8 +1355,7 @@ module SiSU_DB_columns 'classify_topic_register' end def create_column - "#{name} VARCHAR(#{Db[:col_classify_txt_long]}) NULL," - #"#{name} TEXT NULL," + "#{name} VARCHAR(#{Db[:col_info_note]}) NULL," end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} @@ -1804,21 +1803,18 @@ module SiSU_DB_columns self end =begin -#% misc -@make: - :skin: -@links: +#% src =end - def filename + def src_filename def name - 'filename' + 'src_filename' end def create_column "#{name} VARCHAR(#{Db[:col_filename]}) NULL," end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} - IS 'metadata document filename';} + IS 'sisu markup source text filename';} end def tuple t=if defined? @md.fns \ @@ -1831,56 +1827,61 @@ module SiSU_DB_columns end self end - def sisutxt # consider naming sisusrc + def src_fingerprint def name - 'sisutxt' + 'src_fingerprint' #hash/digest, sha256 or md5 end def create_column - "#{name} TEXT NULL," + "#{name} VARCHAR(#{Db[:col_digest]}) NULL," + #"#{name} TEXT NULL," end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} - IS 'sisu markup text (if shared)';} + IS 'sisu markup source text fingerprint, hash digest sha256 (or md5)';} end def tuple - t=if @md.mod.inspect=~/import|update/ \ - and FileTest.exist?(@md.fns) - ["#{name}, ","'#{@sisutxt}', "] + t=if defined? @md.dgst \ + and @md.dgst.class==Array \ + and @md.dgst[1]=~/\S+/ + txt=@md.dgst[1] + ["#{name}, ","'#{txt}', "] else ['',''] end end self end - def fulltext + def src_filesize def name - 'fulltext' + 'src_filesize' end def create_column - "#{name} TEXT NULL," + "#{name} VARCHAR(#{Db[:col_filesize]}) NULL," end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} - IS 'document full text clean, searchable';} + IS 'sisu markup source text file size';} end def tuple - t=if @md.mod.inspect=~/import|update/ \ - and FileTest.exist?(@md.fns) - ["#{name}, ","'#{@fulltext}', "] - else ['',''] - end + t=if defined? @md.filesize \ + and @md.filesize=~/\S+/ + txt=@md.filesize + special_character_escape(txt) + ["#{name}, ","'#{txt}', "] + else ['',''] + end end self end - def word_count + def src_word_count def name - 'word_count' + 'src_word_count' end def create_column "#{name} TEXT NULL," end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} - IS 'document word count';} + IS 'sisu markup source text word count';} end def tuple t=if defined? @md.wc_words \ @@ -1893,23 +1894,47 @@ module SiSU_DB_columns end self end - def digest + def src_txt # consider naming sisusrc def name - 'dgst' + 'src_text' end def create_column "#{name} TEXT NULL," end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} - IS 'document hash digest sha256 (or md5)';} + IS 'sisu markup source text (if shared)';} end def tuple - t=if defined? @md.dgst \ - and @md.dgst=~/\S+/ - txt=@md.dgst - special_character_escape(txt) - ["#{name}, ","'#{txt}', "] + t=if @md.mod.inspect=~/import|update/ \ + and FileTest.exist?(@md.fns) + ["#{name}, ","'#{@sisutxt}', "] + else ['',''] + end + end + self + end +=begin +#% misc +@make: + :skin: +@links: +=end + def fulltext + def name + 'fulltext' + end + def create_column + "#{name} TEXT NULL," + end + def column_comment + %{COMMENT ON COLUMN metadata_and_text.#{name} + IS 'document full text clean, searchable';} + end + def tuple + t=if @md.mod.inspect=~/import|update/ \ + and FileTest.exist?(@md.fns) + ["#{name}, ","'#{@fulltext}', "] else ['',''] end end @@ -1924,12 +1949,35 @@ module SiSU_DB_columns end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} - IS 'metadata document skin name';} + IS 'source text skin name';} + end + def tuple + t=if defined? @md.skin_name \ + and @md.skin_name=~/\S+/ + txt=@md.skin_name + special_character_escape(txt) + ["#{name}, ","'#{txt}', "] + else ['',''] + end + end + self + end + def skin_fingerprint #check + def name + 'skin_fingerprint' + end + def create_column + "#{name} VARCHAR(#{Db[:col_digest]}) NULL," + end + def column_comment + %{COMMENT ON COLUMN metadata_and_text.#{name} + IS 'source text skin fingerprint';} end def tuple - t=if defined? @md.notes.skin_name \ - and @md.notes.skin_name=~/\S+/ - txt=@md.notes.skin_name + t=if defined? @md.dgst_skin \ + and @md.dgst_skin.class==Array \ + and @md.dgst_skin[1]=~/\S+/ + txt=@md.dgst_skin[1] special_character_escape(txt) ["#{name}, ","'#{txt}', "] else ['',''] @@ -1946,7 +1994,7 @@ module SiSU_DB_columns end def column_comment %{COMMENT ON COLUMN metadata_and_text.#{name} - IS 'metadata document skin';} + IS 'source text skin';} end def tuple t=if defined? @md.skin \ @@ -1972,9 +2020,9 @@ module SiSU_DB_columns IS 'metadata document links';} end def tuple - t=if defined? @md.notes.links \ - and @md.notes.links=~/\S+/ - txt=@md.notes.links + t=if defined? @md.links \ + and @md.links=~/\S+/ + txt=@md.links special_character_escape(txt) ["#{name}, ","'#{txt}', "] else ['',''] -- cgit v1.2.3