aboutsummaryrefslogtreecommitdiffhomepage
path: root/lib/sisu/v0/db_create.rb
diff options
context:
space:
mode:
authorRalph Amissah <ralph.amissah@gmail.com>2007-06-02 11:25:19 +0100
committerRalph Amissah <ralph.amissah@gmail.com>2007-06-02 11:25:19 +0100
commitd0f1974a7b93db754f70d013738e6ad7d16b4d24 (patch)
tree252594de9ed0f78fd398609b9a0f298eb5b56ccd /lib/sisu/v0/db_create.rb
parentsisu-0.52.7 + md5s (diff)
0.53.0, pre-build, see changelog, library naming changed for scm, placed under v0 (instead of 0.53)
Diffstat (limited to 'lib/sisu/v0/db_create.rb')
-rw-r--r--lib/sisu/v0/db_create.rb530
1 files changed, 530 insertions, 0 deletions
diff --git a/lib/sisu/v0/db_create.rb b/lib/sisu/v0/db_create.rb
new file mode 100644
index 00000000..67ef7bbd
--- /dev/null
+++ b/lib/sisu/v0/db_create.rb
@@ -0,0 +1,530 @@
+=begin
+ * Name: SiSU information Structuring Universe - Structured information, Serialized Units
+ * Author: Ralph Amissah
+ * http://www.jus.uio.no/sisu
+ * http://www.jus.uio.no/sisu/SiSU/download.html
+
+ * Description: modules shared by the different db types, dbi, postgresql, sqlite
+
+ * Copyright (C) 1997, 1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007 Ralph Amissah
+
+ * License: GPL 2 or later
+
+ Summary of GPL 2
+
+ This program is free software; you can redistribute it and/or modify it
+ under the terms of the GNU General Public License as published by the Free
+ Software Foundation; either version 2 of the License, or (at your option)
+ any later version.
+
+ This program is distributed in the hope that it will be useful, but WITHOUT
+ ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
+ FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for
+ more details.
+
+ You should have received a copy of the GNU General Public License along
+ with this program; if not, write to the Free Software Foundation, Inc.,
+ 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA
+
+ If you have Internet connection, the latest version of the GPL should be
+ available at these locations:
+ http://www.fsf.org/licenses/gpl.html
+ http://www.gnu.org/copyleft/gpl.html
+ http://www.jus.uio.no/sisu/gpl2.fsf
+
+ SiSU was first released to the public on January 4th 2005
+
+ SiSU uses:
+
+ * Standard SiSU markup syntax,
+ * Standard SiSU meta-markup syntax, and the
+ * Standard SiSU object citation numbering and system
+
+ © Ralph Amissah 1997, current 2007.
+ All Rights Reserved.
+
+ * Ralph Amissah: ralph@amissah.com
+ ralph.amissah@gmail.com
+=end
+module SiSU_DB_create
+ require "#{SiSU_lib}/db_columns"
+ class Create < SiSU_DB_columns::Column_size
+ require "#{SiSU_lib}/sysenv"
+ @@dl=nil
+ 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
+ @@dl ||=SiSU_Env::Info_env.new.digest.length
+ end
+ def available
+ DBI.available_drivers.each do |driver|
+ puts "Driver: #{driver}"
+ DBI.data_sources(driver).each do |dsn|
+ puts "\tDatasource: #{dsn}"
+ end
+ end
+ end
+ def create_db
+ @env=SiSU_Env::Info_env.new(@opt.fns)
+ tell=SiSU_Screen::Ansi.new(@opt.cmd,'invert','Create PG db:',%{"SiSU_#{@env.path.stub_pwd}"})
+ 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'] =%{
+ COMMENT ON Table metadata
+ IS 'contains SiSU documents metadata with metadata';
+ COMMENT ON COLUMN metadata.tid
+ IS 'unique';
+ COMMENT ON COLUMN metadata.filename
+ IS 'document filename';
+ COMMENT ON COLUMN metadata.title
+ IS 'metadata title (dublin core element 1)';
+ COMMENT ON COLUMN metadata.subtitle
+ IS 'document subtitle';
+ COMMENT ON COLUMN metadata.creator
+ IS 'metadata creator (dublin core element 2)';
+ COMMENT ON COLUMN metadata.illustrator
+ IS 'metadata illustrator';
+ COMMENT ON COLUMN metadata.translator
+ IS 'metadata translator';
+ COMMENT ON COLUMN metadata.subject
+ IS 'metadata subject (dublin core element 3)';
+ COMMENT ON COLUMN metadata.date
+ IS 'metadata date (dublin core element 7)';
+ COMMENT ON COLUMN metadata.date_created
+ IS 'metadata date created (dublin core)';
+ COMMENT ON COLUMN metadata.date_issued
+ IS 'metadata date of issue (dublin core)';
+ COMMENT ON COLUMN metadata.date_available
+ IS 'metadata date available (dublin core)';
+ COMMENT ON COLUMN metadata.date_valid
+ IS 'metadata date valid (dublin core)';
+ COMMENT ON COLUMN metadata.date_modified
+ IS 'metadata date modified (dublin core)';
+ COMMENT ON COLUMN metadata.type
+ IS 'metadata type (dublin core element 8)';
+ COMMENT ON COLUMN metadata.description
+ IS 'metadata description (dublin core element 4)';
+ COMMENT ON COLUMN metadata.publisher
+ IS 'metadata publisher (dublin core element 5)';
+ COMMENT ON COLUMN metadata.contributor
+ IS 'metadata contributor (dublin core element 6)';
+ COMMENT ON COLUMN metadata.prepared_by
+ IS 'metadata markup prepared by';
+ COMMENT ON COLUMN metadata.digitized_by
+ IS 'metadata digitized by';
+ COMMENT ON COLUMN metadata.format
+ IS 'metadata format (dublin core element 9)';
+ COMMENT ON COLUMN metadata.identifier
+ IS 'metadata identifier (dublin core element 10)';
+ COMMENT ON COLUMN metadata.source
+ IS 'metadata source (dublin core element 11)';
+ COMMENT ON COLUMN metadata.language
+ IS 'metadata language (dublin core element 12)';
+ COMMENT ON COLUMN metadata.language_original
+ IS 'metadata original language';
+ COMMENT ON COLUMN metadata.relation
+ IS 'metadata (dublin core element 13)';
+ COMMENT ON COLUMN metadata.coverage
+ IS 'metadata coverage (dublin core element 14)';
+ COMMENT ON COLUMN metadata.rights
+ IS 'metadata rights / copyright / license (dublin core element 15)';
+ COMMENT ON COLUMN metadata.owner
+ IS 'metadata owner';
+ COMMENT ON COLUMN metadata.keywords
+ IS 'metadata keywords';
+ COMMENT ON COLUMN metadata.comment
+ IS 'metadata comment';
+ COMMENT ON COLUMN metadata.abstract
+ IS 'metadata abstract';
+ COMMENT ON COLUMN metadata.loc
+ IS 'metadata library of congress';
+ COMMENT ON COLUMN metadata.dewey
+ IS 'metadata dewey';
+ COMMENT ON COLUMN metadata.isbn
+ IS 'metadata isbn';
+ COMMENT ON COLUMN metadata.pg
+ IS 'metadata project gutenberg number';
+ COMMENT ON COLUMN metadata.prefix_a
+ IS 'metadata prefix';
+ COMMENT ON COLUMN metadata.prefix_b
+ IS 'metadata prefix';
+ COMMENT ON COLUMN metadata.skin
+ IS 'metadata sisu skin';
+ COMMENT ON COLUMN metadata.markup
+ IS 'metadata markup source';
+ COMMENT ON COLUMN metadata.links
+ IS 'metadata links';
+ COMMENT ON COLUMN metadata.information
+ IS 'metadata information';
+ COMMENT ON COLUMN metadata.contact
+ IS 'metadata contact';
+ COMMENT ON COLUMN metadata.suffix
+ IS 'metadata sisu suffix (output related)';
+ COMMENT ON COLUMN metadata.filename
+ IS 'metadata source filename';
+ COMMENT ON COLUMN metadata.types
+ IS 'document types scroll 1, seg 2, both 3';
+ COMMENT ON COLUMN metadata.subj
+ IS 'subject areas - no way to populate at present as not mapped';
+/*
+ CREATE FUNCTION fileremoval() RETURNS opaque AS '
+ BEGIN
+ DELETE FROM metadata WHERE tid=#@removetid;
+ DELETE FROM documents WHERE documents.metadata_tid=#@removetid;
+ DELETE FROM endnotes WHERE endnotes.metadata_tid=#@removetid;
+ DELETE FROM endnotes_asterisk WHERE endnotes_asterisk.metadata_tid=#@removetid;
+ DELETE FROM endnotes_plus WHERE endnotes_plus.metadata_tid=#@removetid;
+ DELETE FROM urls WHERE urls.metadata_tid=#@removetid;
+ END;
+ ' LANGUAGE 'plpgsql';
+ CREATE TRIGGER removefile AFTER INSERT
+ PROCEDURE fileremoval();
+*/
+ }
+ @comment['documents'] =%{
+ COMMENT ON Table documents
+ IS 'contains searchable text of SiSU documents';
+ COMMENT ON COLUMN documents.lid
+ IS 'unique';
+ COMMENT ON COLUMN documents.metadata_tid
+ IS 'tie to title in metadata';
+ COMMENT ON COLUMN documents.lev
+ IS 'doc level 1-6 \d\~';
+ COMMENT ON COLUMN documents.seg
+ IS 'segment name from level 4';
+ COMMENT ON COLUMN documents.ocn
+ IS 'object citation number';
+ COMMENT ON COLUMN documents.en_a
+ IS 'first endnote number in text object (eg. NULL or 34) (used with en_z to create range)';
+ COMMENT ON COLUMN documents.en_z
+ IS 'last endnote number within text object (eg. NULL, 34 or say 47) (used with en_a to create range)';
+ COMMENT ON COLUMN documents.en_a_asterisk
+ IS 'first endnote number in text object (eg. NULL or 34) (used with en_z_asterisk to create range)';
+ COMMENT ON COLUMN documents.en_z_asterisk
+ IS 'last endnote number within text object (eg. NULL, 34 or say 47) (used with en_a_asterisk to create range)';
+ COMMENT ON COLUMN documents.en_a_plus
+ IS 'first endnote number in text object (eg. NULL or 34) (used with en_z_plus to create range)';
+ COMMENT ON COLUMN documents.en_z_plus
+ IS 'last endnote number within text object (eg. NULL, 34 or say 47) (used with en_a_plus to create range)';
+ COMMENT ON COLUMN documents.types
+ IS 'document types seg scroll';
+ COMMENT ON COLUMN documents.clean
+ IS 'text object - substantive text: clean, stripped of markup';
+ COMMENT ON COLUMN documents.body
+ IS 'text object - substantive text: light html markup';
+ COMMENT ON COLUMN documents.lev1
+ IS 'document structure, level 1';
+ COMMENT ON COLUMN documents.lev2
+ IS 'document structure, level 2';
+ COMMENT ON COLUMN documents.lev3
+ IS 'document structure, level 3';
+ COMMENT ON COLUMN documents.lev4
+ IS 'document structure, level 4';
+ COMMENT ON COLUMN documents.lev5
+ IS 'document structure, level 5';
+ COMMENT ON COLUMN documents.lev6
+ IS 'document structure, level 6';
+ }
+ @comment['endnotes'] =%{
+ COMMENT ON Table endnotes
+ IS 'contains searchable text of SiSU documents endnotes';
+ COMMENT ON COLUMN endnotes.nid
+ IS 'unique';
+ COMMENT ON COLUMN endnotes.document_lid
+ IS 'ties to text block from which referenced';
+ COMMENT ON COLUMN endnotes.nr
+ IS 'endnote number <!e_(\d+)!>';
+ COMMENT ON COLUMN endnotes.clean
+ IS 'endnote substantive content, stripped of markup';
+ COMMENT ON COLUMN endnotes.body
+ IS 'endnote substantive content';
+ COMMENT ON COLUMN endnotes.ocn
+ IS 'object citation no# <\~(\d+)> from which endnote is referenced';
+ COMMENT ON COLUMN documents.metadata_tid
+ IS 'tie to title in metadata - unique for each document';
+ }
+ @comment['endnotes_asterisk'] =%{
+ COMMENT ON Table endnotes_asterisk
+ IS 'contains searchable text of SiSU documents endnotes asterisk';
+ COMMENT ON COLUMN endnotes_asterisk.nid
+ IS 'unique';
+ COMMENT ON COLUMN endnotes_asterisk.document_lid
+ IS 'ties to text block from which referenced';
+ COMMENT ON COLUMN endnotes_asterisk.nr
+ IS 'endnote number <!e_(\d+)!>';
+ COMMENT ON COLUMN endnotes_asterisk.clean
+ IS 'endnote substantive content, stripped of markup';
+ COMMENT ON COLUMN endnotes_asterisk.body
+ IS 'endnote substantive content';
+ COMMENT ON COLUMN endnotes_asterisk.ocn
+ IS 'object citation no# <\~(\d+)> from which endnote is referenced';
+ COMMENT ON COLUMN documents.metadata_tid
+ IS 'tie to title in metadata - unique for each document';
+ }
+ @comment['endnotes_plus'] =%{
+ COMMENT ON Table endnotes_plus
+ IS 'contains searchable text of SiSU documents endnotes';
+ COMMENT ON COLUMN endnotes_plus.nid
+ IS 'unique';
+ COMMENT ON COLUMN endnotes_plus.document_lid
+ IS 'ties to text block from which referenced';
+ COMMENT ON COLUMN endnotes_plus.nr
+ IS 'endnote number <!e_(\d+)!>';
+ COMMENT ON COLUMN endnotes_plus.clean
+ IS 'endnote substantive content, stripped of markup';
+ COMMENT ON COLUMN endnotes_plus.body
+ IS 'endnote substantive content';
+ COMMENT ON COLUMN endnotes_plus.ocn
+ IS 'object citation no# <\~(\d+)> from which endnote is referenced';
+ COMMENT ON COLUMN documents.metadata_tid
+ IS 'tie to title in metadata - unique for each document';
+ }
+ @comment['urls'] =%{
+ COMMENT ON Table urls
+ IS 'contains base url links to different SiSU output';
+ COMMENT ON COLUMN documents.metadata_tid
+ IS 'tie to title in metadata - unique for each document, the mapping of rows is one to one';
+ COMMENT ON COLUMN urls.plaintext
+ IS 'plaintext utf-8';
+ COMMENT ON COLUMN urls.html_toc
+ IS 'table of contents for segmented html document';
+ COMMENT ON COLUMN urls.html_doc
+ IS 'html document (scroll)';
+ COMMENT ON COLUMN urls.xhtml
+ IS 'xhtml document (scroll)';
+ COMMENT ON COLUMN urls.xml_sax
+ IS 'xml sax oriented document (scroll)';
+ COMMENT ON COLUMN urls.xml_dom
+ IS 'xml dom oriented document (scroll)';
+ COMMENT ON COLUMN urls.odf
+ IS 'opendocument format text';
+ COMMENT ON COLUMN urls.pdf_p
+ IS 'pdf portrait';
+ COMMENT ON COLUMN urls.pdf_l
+ IS 'pdf landscape';
+ COMMENT ON COLUMN urls.concordance
+ IS 'rudimentary document index linked to html';
+ COMMENT ON COLUMN urls.latex_p
+ IS 'latex portrait';
+ COMMENT ON COLUMN urls.latex_l
+ IS 'latex_landscape';
+ COMMENT ON COLUMN urls.markup
+ IS 'markup';
+ COMMENT ON COLUMN urls.sisupod
+ IS 'SiSU document format .tgz (all SiSU information on document)';
+ }
+ end
+ @comment
+ end
+ def output_dir?
+ dir=SiSU_Env::Info_env.new('')
+ if @opt.cmd =~/d/; dir.path.webserv_stub_ensure
+ 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']}
+ })
+ end
+ end
+end
+__END__