From 96049bf591123333b8a17780544a7951146ef03b Mon Sep 17 00:00:00 2001 From: Ralph Amissah Date: Fri, 26 Oct 2007 22:41:42 +0100 Subject: db rearranging, create, drop, import (and a renaming: db_dbi from shared_db) --- lib/sisu/v0/db_create.rb | 459 +++++++++++++++++++++++++---------------------- 1 file changed, 246 insertions(+), 213 deletions(-) (limited to 'lib/sisu/v0/db_create.rb') diff --git a/lib/sisu/v0/db_create.rb b/lib/sisu/v0/db_create.rb index 633c92fa..61505cb3 100644 --- a/lib/sisu/v0/db_create.rb +++ b/lib/sisu/v0/db_create.rb @@ -63,7 +63,7 @@ module SiSU_DB_create def initialize(opt,conn='',sql_type='pg') @opt,@conn,@sql_type=opt,conn,sql_type @cX=SiSU_Screen::Ansi.new(@opt.cmd).cX - @comment=comment + @comment=Comment.new(@sql_type) @@dl ||=SiSU_Env::Info_env.new.digest.length end def available @@ -80,11 +80,222 @@ module SiSU_DB_create tell.colorize unless @opt.cmd =~/q/ SiSU_Env::System_call.new.create_pg_db(@env.path.stub_pwd) #watch use of path.stub_pwd instead of stub end - def comment - @comment=Hash.new('') - case @sql_type - when /pg/ - @comment['metadata'] =%{ + def output_dir? + dir=SiSU_Env::Info_env.new('') + if @opt.cmd =~/d/; dir.path.webserv_stub_ensure + end + end + def create_table + def metadata + print %{ + currently using sisu dbi module + to be populated from documents files + create tables metadata + data import through ruby transfer + } unless @opt.cmd =~/q/ + @conn.execute(%{ + CREATE TABLE metadata ( + tid INT4 PRIMARY KEY, + title VARCHAR(#{lt_title}) NULL, + subtitle VARCHAR(#{lt_subtitle}) NULL, + creator VARCHAR(#{lt_creator}) NULL, + illustrator VARCHAR(#{lt_illustrator}) NULL, + translator VARCHAR(#{lt_translator}) NULL, + subject VARCHAR(#{lt_subject}) NULL, + date VARCHAR(#{lt_date}) NULL, + date_created VARCHAR(#{lt_date_created}) NULL, + date_issued VARCHAR(#{lt_date_issued}) NULL, + date_available VARCHAR(#{lt_date_available}) NULL, + date_valid VARCHAR(#{lt_date_valid}) NULL, + date_modified VARCHAR(#{lt_date_modified}) NULL, +/* date DATE, */ +/* date_created DATE, */ +/* date_issued DATE, */ +/* date_available DATE, */ +/* date_valid DATE, */ +/* date_modified DATE, */ + type VARCHAR(#{lt_type}) NULL, + description VARCHAR(#{lt_description}) NULL, + publisher VARCHAR(#{lt_publisher}) NULL, + contributor VARCHAR(#{lt_contributor}) NULL, + prepared_by VARCHAR(#{lt_prepared_by}) NULL, + digitized_by VARCHAR(#{lt_digitized_by}) NULL, + format VARCHAR(#{lt_format}) NULL, + identifier VARCHAR(#{lt_identifier}) NULL, + source VARCHAR(#{lt_source}) NULL, + language VARCHAR(#{lt_language}) NULL, + language_original VARCHAR(#{lt_language_original}) NULL, + relation VARCHAR(#{lt_relation}) NULL, + coverage VARCHAR(#{lt_coverage}) NULL, + rights VARCHAR(#{lt_rights}) NULL, + copyright VARCHAR(#{lt_copyright}) NULL, + owner VARCHAR(#{lt_owner}) NULL, + keywords VARCHAR(#{lt_keywords}) NULL, + comment VARCHAR(#{lt_comment}) NULL, + loc VARCHAR(#{lt_loc}) NULL, + dewey VARCHAR(#{lt_dewey}) NULL, + isbn VARCHAR(#{lt_isbn}) NULL, + pg VARCHAR(#{lt_pg}) NULL, + abstract VARCHAR(#{lt_abstract}) NULL, + prefix_a TEXT NULL, + prefix_b TEXT NULL, + skin VARCHAR(#{lt_skin}) NULL, + markup VARCHAR(#{lt_markup}) NULL, + links VARCHAR(#{lt_links}) NULL, + information VARCHAR(#{lt_information}) NULL, + contact VARCHAR(#{lt_contact}) NULL, + suffix VARCHAR(#{lt_suffix}) NULL, + filename VARCHAR(#{lt_filename}) NULL UNIQUE, + types CHAR(#{lt_types}) NULL, + subj VARCHAR(#{lt_subj}) NULL + ); + #{@comment.metadata} + }) + end + def documents # create documents base + print %{ + to be populated from documents files + create tables documents document_trade document_env + data import through ruby transfer + } unless @opt.cmd =~/q/ + @conn.execute(%{ + CREATE TABLE documents ( + lid INT4 PRIMARY KEY, + metadata_tid INT4 REFERENCES metadata, + ocn SMALLINT, + ocnd VARCHAR(6), + ocns VARCHAR(6), + clean TEXT NULL, + body TEXT NULL, + seg VARCHAR(#{document_seg}) NULL, + lev SMALLINT NULL, + lev1 SMALLINT, + lev2 SMALLINT, + lev3 SMALLINT, + lev4 SMALLINT, + lev5 SMALLINT, + lev6 SMALLINT, + en_a SMALLINT NULL, + en_z SMALLINT NULL, + en_a_asterisk SMALLINT NULL, + en_z_asterisk SMALLINT NULL, + en_a_plus SMALLINT NULL, + en_z_plus SMALLINT NULL, + digest_clean CHAR(#{@@dl}), + digest_all CHAR(#{@@dl}), + types CHAR(1) NULL + ); + #{@comment.documents} + }) + end + def endnotes + print %{ + to be populated from documents files + create tables endnotes + data import through ruby transfer + } unless @opt.cmd =~/q/ + @conn.execute(%{ + CREATE TABLE endnotes ( + nid INT4 PRIMARY KEY, + document_lid INT4 REFERENCES documents, + nr SMALLINT, + clean TEXT NULL, + body TEXT NULL, + ocn SMALLINT, + ocnd VARCHAR(6), + ocns VARCHAR(6), + digest_clean CHAR(#{@@dl}), + metadata_tid INT4 REFERENCES metadata + ); + #{@comment.endnotes} + }) + end + def endnotes_asterisk + print %{ + to be populated from documents files + create tables endnotes_asterisk + data import through ruby transfer + } unless @opt.cmd =~/q/ + @conn.execute(%{ + CREATE TABLE endnotes_asterisk ( + nid INT4 PRIMARY KEY, + document_lid INT4 REFERENCES documents, + nr SMALLINT, + clean TEXT NULL, + body TEXT NULL, + ocn SMALLINT, + ocnd VARCHAR(6), + ocns VARCHAR(6), + digest_clean CHAR(#{@@dl}), + metadata_tid INT4 REFERENCES metadata + ); + #{@comment.endnotes_asterisk} + }) + end + def endnotes_plus + print %{ + to be populated from documents files + create tables endnotes_plus + data import through ruby transfer + } unless @opt.cmd =~/q/ + @conn.execute(%{ + CREATE TABLE endnotes_plus ( + nid INT4 PRIMARY KEY, + document_lid INT4 REFERENCES documents, + nr SMALLINT, + clean TEXT NULL, + body TEXT NULL, + ocn SMALLINT, + ocnd VARCHAR(6), + ocns VARCHAR(6), + digest_clean CHAR(#{@@dl}), + metadata_tid INT4 REFERENCES metadata + ); + #{@comment.endnotes_plus} + }) + end + def urls # create documents file links mapping + print %{ + currently using sisu dbi module + to be populated from documents files + create tables urls + data import through ruby transfer + } unless @opt.cmd =~/q/ + @conn.execute(%{ + CREATE TABLE urls ( + metadata_tid INT4 REFERENCES metadata, + plaintext varchar(512), + html_toc varchar(512), + html_doc varchar(512), + xhtml varchar(512), + xml_sax varchar(512), + xml_dom varchar(512), + odf varchar(512), + pdf_p varchar(512), + pdf_l varchar(512), + concordance varchar(512), + latex_p varchar(512), + latex_l varchar(512), + digest varchar(512), + manifest varchar(512), + markup varchar(512), + sisupod varchar(512) + ); + #{@comment.urls} + }) + end + self + end + end + class Comment + def initialize(sql_type='pg') + if sql_type =~ /pg/; psql + else none + end + end + def psql + def metadata + %{ COMMENT ON Table metadata IS 'contains SiSU documents metadata with metadata'; COMMENT ON COLUMN metadata.tid @@ -196,7 +407,9 @@ module SiSU_DB_create PROCEDURE fileremoval(); */ } - @comment['documents'] =%{ + end + def documents + %{ COMMENT ON Table documents IS 'contains searchable text of SiSU documents'; COMMENT ON COLUMN documents.lid @@ -240,7 +453,9 @@ module SiSU_DB_create COMMENT ON COLUMN documents.lev6 IS 'document structure, level 6'; } - @comment['endnotes'] =%{ + end + def endnotes + %{ COMMENT ON Table endnotes IS 'contains searchable text of SiSU documents endnotes'; COMMENT ON COLUMN endnotes.nid @@ -258,7 +473,9 @@ module SiSU_DB_create COMMENT ON COLUMN documents.metadata_tid IS 'tie to title in metadata - unique for each document'; } - @comment['endnotes_asterisk'] =%{ + end + def endnotes_asterisk + %{ COMMENT ON Table endnotes_asterisk IS 'contains searchable text of SiSU documents endnotes asterisk'; COMMENT ON COLUMN endnotes_asterisk.nid @@ -276,7 +493,9 @@ module SiSU_DB_create COMMENT ON COLUMN documents.metadata_tid IS 'tie to title in metadata - unique for each document'; } - @comment['endnotes_plus'] =%{ + end + def endnotes_plus + %{ COMMENT ON Table endnotes_plus IS 'contains searchable text of SiSU documents endnotes'; COMMENT ON COLUMN endnotes_plus.nid @@ -294,7 +513,9 @@ module SiSU_DB_create COMMENT ON COLUMN documents.metadata_tid IS 'tie to title in metadata - unique for each document'; } - @comment['urls'] =%{ + end + def urls + %{ COMMENT ON Table urls IS 'contains base url links to different SiSU output'; COMMENT ON COLUMN documents.metadata_tid @@ -329,210 +550,22 @@ module SiSU_DB_create IS 'SiSU document format .tgz (all SiSU information on document)'; } end - @comment + self end - def output_dir? - dir=SiSU_Env::Info_env.new('') - if @opt.cmd =~/d/; dir.path.webserv_stub_ensure + def none + def metadata end - end - def create_table_metadata - print %{ - currently using sisu dbi module - to be populated from documents files - create tables metadata - data import through ruby transfer - } unless @opt.cmd =~/q/ - @conn.execute(%{ - CREATE TABLE metadata ( - tid INT4 PRIMARY KEY, - title VARCHAR(#{lt_title}) NULL, - subtitle VARCHAR(#{lt_subtitle}) NULL, - creator VARCHAR(#{lt_creator}) NULL, - illustrator VARCHAR(#{lt_illustrator}) NULL, - translator VARCHAR(#{lt_translator}) NULL, - subject VARCHAR(#{lt_subject}) NULL, - date VARCHAR(#{lt_date}) NULL, - date_created VARCHAR(#{lt_date_created}) NULL, - date_issued VARCHAR(#{lt_date_issued}) NULL, - date_available VARCHAR(#{lt_date_available}) NULL, - date_valid VARCHAR(#{lt_date_valid}) NULL, - date_modified VARCHAR(#{lt_date_modified}) NULL, -/* date DATE, */ -/* date_created DATE, */ -/* date_issued DATE, */ -/* date_available DATE, */ -/* date_valid DATE, */ -/* date_modified DATE, */ - type VARCHAR(#{lt_type}) NULL, - description VARCHAR(#{lt_description}) NULL, - publisher VARCHAR(#{lt_publisher}) NULL, - contributor VARCHAR(#{lt_contributor}) NULL, - prepared_by VARCHAR(#{lt_prepared_by}) NULL, - digitized_by VARCHAR(#{lt_digitized_by}) NULL, - format VARCHAR(#{lt_format}) NULL, - identifier VARCHAR(#{lt_identifier}) NULL, - source VARCHAR(#{lt_source}) NULL, - language VARCHAR(#{lt_language}) NULL, - language_original VARCHAR(#{lt_language_original}) NULL, - relation VARCHAR(#{lt_relation}) NULL, - coverage VARCHAR(#{lt_coverage}) NULL, - rights VARCHAR(#{lt_rights}) NULL, - copyright VARCHAR(#{lt_copyright}) NULL, - owner VARCHAR(#{lt_owner}) NULL, - keywords VARCHAR(#{lt_keywords}) NULL, - comment VARCHAR(#{lt_comment}) NULL, - loc VARCHAR(#{lt_loc}) NULL, - dewey VARCHAR(#{lt_dewey}) NULL, - isbn VARCHAR(#{lt_isbn}) NULL, - pg VARCHAR(#{lt_pg}) NULL, - abstract VARCHAR(#{lt_abstract}) NULL, - prefix_a TEXT NULL, - prefix_b TEXT NULL, - skin VARCHAR(#{lt_skin}) NULL, - markup VARCHAR(#{lt_markup}) NULL, - links VARCHAR(#{lt_links}) NULL, - information VARCHAR(#{lt_information}) NULL, - contact VARCHAR(#{lt_contact}) NULL, - suffix VARCHAR(#{lt_suffix}) NULL, - filename VARCHAR(#{lt_filename}) NULL UNIQUE, - types CHAR(#{lt_types}) NULL, - subj VARCHAR(#{lt_subj}) NULL - ); - #{@comment['metadata']} - }) - end - def create_table # create documents base - print %{ - to be populated from documents files - create tables documents document_trade document_env - data import through ruby transfer - } unless @opt.cmd =~/q/ - @conn.execute(%{ - CREATE TABLE documents ( - lid INT4 PRIMARY KEY, - metadata_tid INT4 REFERENCES metadata, - ocn SMALLINT, - ocnd VARCHAR(6), - ocns VARCHAR(6), - clean TEXT NULL, - body TEXT NULL, - seg VARCHAR(#{document_seg}) NULL, - lev SMALLINT NULL, - lev1 SMALLINT, - lev2 SMALLINT, - lev3 SMALLINT, - lev4 SMALLINT, - lev5 SMALLINT, - lev6 SMALLINT, - en_a SMALLINT NULL, - en_z SMALLINT NULL, - en_a_asterisk SMALLINT NULL, - en_z_asterisk SMALLINT NULL, - en_a_plus SMALLINT NULL, - en_z_plus SMALLINT NULL, - digest_clean CHAR(#{@@dl}), - digest_all CHAR(#{@@dl}), - types CHAR(1) NULL - ); - #{@comment['documents']} - }) - end - def create_table_endnotes - print %{ - to be populated from documents files - create tables endnotes - data import through ruby transfer - } unless @opt.cmd =~/q/ - @conn.execute(%{ - CREATE TABLE endnotes ( - nid INT4 PRIMARY KEY, - document_lid INT4 REFERENCES documents, - nr SMALLINT, - clean TEXT NULL, - body TEXT NULL, - ocn SMALLINT, - ocnd VARCHAR(6), - ocns VARCHAR(6), - digest_clean CHAR(#{@@dl}), - metadata_tid INT4 REFERENCES metadata - ); - #{@comment['endnotes']} - }) - end - def create_table_endnotes_asterisk - print %{ - to be populated from documents files - create tables endnotes_asterisk - data import through ruby transfer - } unless @opt.cmd =~/q/ - @conn.execute(%{ - CREATE TABLE endnotes_asterisk ( - nid INT4 PRIMARY KEY, - document_lid INT4 REFERENCES documents, - nr SMALLINT, - clean TEXT NULL, - body TEXT NULL, - ocn SMALLINT, - ocnd VARCHAR(6), - ocns VARCHAR(6), - digest_clean CHAR(#{@@dl}), - metadata_tid INT4 REFERENCES metadata - ); - #{@comment['endnotes_asterisk']} - }) - end - def create_table_endnotes_plus - print %{ - to be populated from documents files - create tables endnotes_plus - data import through ruby transfer - } unless @opt.cmd =~/q/ - @conn.execute(%{ - CREATE TABLE endnotes_plus ( - nid INT4 PRIMARY KEY, - document_lid INT4 REFERENCES documents, - nr SMALLINT, - clean TEXT NULL, - body TEXT NULL, - ocn SMALLINT, - ocnd VARCHAR(6), - ocns VARCHAR(6), - digest_clean CHAR(#{@@dl}), - metadata_tid INT4 REFERENCES metadata - ); - #{@comment['endnotes_plus']} - }) - end - def create_table_urls # create documents file links mapping - print %{ - currently using sisu dbi module - to be populated from documents files - create tables urls - data import through ruby transfer - } unless @opt.cmd =~/q/ - @conn.execute(%{ - CREATE TABLE urls ( - metadata_tid INT4 REFERENCES metadata, - plaintext varchar(512), - html_toc varchar(512), - html_doc varchar(512), - xhtml varchar(512), - xml_sax varchar(512), - xml_dom varchar(512), - odf varchar(512), - pdf_p varchar(512), - pdf_l varchar(512), - concordance varchar(512), - latex_p varchar(512), - latex_l varchar(512), - digest varchar(512), - manifest varchar(512), - markup varchar(512), - sisupod varchar(512) - ); - #{@comment['urls']} - }) + def documents + end + def endnotes + end + def endnotes_asterisk + end + def endnotes_plus + end + def urls + end + self end end end -- cgit v1.2.3