db rearranging, create, drop, import (and a renaming: db_dbi from shared_db)

1 files changed, 246 insertions, 213 deletions

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