path: root/org/db.org

-*- mode: org -*-
#+TITLE:       sisu db sql
#+DESCRIPTION: documents - structuring, various output representations & search
#+FILETAGS:    :sisu:db:sql:
#+AUTHOR:      Ralph Amissah
#+EMAIL:       [[mailto:ralph.amissah@gmail.com][ralph.amissah@gmail.com]]
#+COPYRIGHT:   Copyright (C) 2015 - 2021 Ralph Amissah
#+LANGUAGE:    en
#+STARTUP:     content hideblocks hidestars noindent entitiespretty
#+OPTIONS:     H:3 num:nil toc:t \n:nil @:t ::t |:t ^:nil _:nil -:t f:t *:t <:t
#+PROPERTY:    header-args  :exports code
#+PROPERTY:    header-args+ :noweb yes
#+PROPERTY:    header-args+ :eval no
#+PROPERTY:    header-args+ :results no
#+PROPERTY:    header-args+ :cache no
#+PROPERTY:    header-args+ :padline no
#+PROPERTY:    header-args+ :mkdirp yes

* dbi.rb

#+BEGIN_SRC ruby  :tangle "../lib/sisu/dbi.rb"
<<sisu_document_header>>
module  SiSU_DBI                                                                 #% database building
  require_relative 'se'                                 # se.rb
    include SiSU_Env; include SiSU_Screen
  require_relative 'dp'                                 # dp.rb
    include SiSU_Param
  require_relative 'db_dbi'                             # db_dbi.rb
    include SiSU_DbDBI
  require_relative 'html_lite_shared'                   # html_lite_shared.rb
    include SiSU_FormatShared
  class SQL
    def initialize(opt)
      @opt=opt
      @db=SiSU_Env::InfoDb.new
      if @opt.act[:psql][:set]==:on \
      or @opt.act[:sqlite][:set]==:on
        @sql_type=if @opt.act[:psql][:set]==:on
          maintenance_check(@opt,__FILE__,__LINE__) if @opt.act[:maintenance][:set]==:on
          :pg
        elsif @opt.act[:psql][:set]==:on
          maintenance_check(@opt,__FILE__,__LINE__) if @opt.act[:maintenance][:set]==:on
          :pg
        elsif @opt.act[:sqlite][:set]==:on
          maintenance_check(@opt,__FILE__,__LINE__) if @opt.act[:maintenance][:set]==:on
          :sqlite
        elsif @opt.act[:sqlite][:set]==:on
          maintenance_check(@opt,__FILE__,__LINE__) if @opt.act[:maintenance][:set]==:on
          :sqlite
        else
          maintenance_check(@opt,__FILE__,__LINE__) if @opt.act[:maintenance][:set]==:on
          :sqlite
        end
        if    @sql_type==:pg    then SiSU_Env::Load.new('pg',true).prog
        elsif @sql_type==:sqlite then SiSU_Env::Load.new('sqlite3',true).prog
        end
      end
    end
    def maintenance_check(opt,file,line)
      p opt.selections.str
      p "at #{file} #{line}"
    end
    def read_psql
      begin
        begin
          require 'pg'
        rescue LoadError
          SiSU_Utils::CodeMarker.new(__LINE__,__FILE__,:fuchsia).
            error('pg NOT FOUND (LoadError)')
        end
        @conn=@db.psql.conn_pg
      rescue
        if @opt.act[:psql_create][:set]==:on
          cX=SiSU_Screen::Ansi.new(@opt.act[:color_state][:set]).cX
          puts <<-WOK
manually create the database: "#{cX.green}#{@db.db}#{cX.off}" if it does not yet exist
  #{cX.yellow}createdb #{@db.db}#{cX.off}
          WOK
          #sudo su -p postgres;  createdb #{@db.db}; #[createuser?]
        end
      ensure
      end
    end
    def read_sqlite
      begin
        begin
          begin
            require 'sqlite3'
          rescue LoadError
            SiSU_Utils::CodeMarker.new(__LINE__,__FILE__,:fuchsia).
              error('sqlite3 NOT FOUND (LoadError)')
          end
          @conn=@db.sqlite.conn_sqlite3
        rescue LoadError
          errmsg='sqlite3 NOT FOUND (LoadError)'
          if @opt.act[:no_stop][:set]==:on
            SiSU_Utils::CodeMarker.new(__LINE__,__FILE__,:fuchsia).
              error(errmsg + ', ' + 'attempt to proceed without sqlite output (as requested)')
          else
            SiSU_Utils::CodeMarker.new(__LINE__,__FILE__,:fuchsia).
              error(errmsg + ', ' + 'STOPPING')
            exit
          end
        end
      end
    end
    def connect
      case @sql_type
      when :pg     then read_psql #read_pg
      when :sqlite then read_sqlite
      end
      SiSU_Screen::Ansi.new(
        @opt.act[:color_state][:set],
        "SQL DB #{@sql_type.upcase}",
        @opt.fno
      ).dbi_title unless @opt.act[:quiet][:set]==:on
      begin
        SiSU_DbDBI::Case.new(@opt,@conn,@sql_type).cases
      rescue
        SiSU_Errors::Rescued.new($!,$@,@cf,@opt.fns).location do
          __LINE__.to_s + ':' + __FILE__
        end
      ensure
      end
      begin
      rescue
        connect
      end
    end
  end
end
__END__
#+END_SRC

* dbi_discrete.rb

#+BEGIN_SRC ruby  :tangle "../lib/sisu/dbi_discrete.rb"
<<sisu_document_header>>
module  SiSU_DBI_Discrete                               #% database building
  require_relative 'se'                                 # se.rb
    include SiSU_Env; include SiSU_Screen
  require_relative 'dp'                                 # dp.rb
    include SiSU_Param
  require_relative 'db_dbi'                             # db_dbi.rb
    include SiSU_DbDBI
  require_relative 'html_lite_shared'                   # html_lite_shared.rb
    include SiSU_FormatShared
  begin
    require 'fileutils'
      include FileUtils::Verbose
  rescue LoadError
    SiSU_Utils::CodeMarker.new(__LINE__,__FILE__,:fuchsia).
      error('fileutils NOT FOUND (LoadError)')
  end
  class SQL
    def initialize(opt)
      SiSU_Env::Load.new('sqlite3',true).prog
      @opt=opt
      @particulars=SiSU_Particulars::CombinedSingleton.instance.get_all(opt)
      @md=@particulars.md
      if @opt.act[:sqlite][:set]==:on
        @sql_type=:sqlite
        if @opt.act[:maintenance][:set]==:on
          maintenance_check(@opt,__FILE__,__LINE__)
        end
      end
      @output_path=@md.file.output_path.sqlite_discrete.dir
      @filename=@md.file.base_filename.sqlite_discrete
      @file_maint=sql_maintenance_file
      @file="#{@output_path}/#{@filename}"
    end
    def build
      prepare
      create_and_populate
    end
    def maintenance_check(opt,file,line)
      #p opt.selections.str
      p "at #{file} #{line}"
    end
    def prepare
      if not FileTest.directory?(@output_path)
        FileUtils::mkdir_p(@output_path)
      elsif @file
        FileUtils::rm_rf(@file)
      end
    end
    def db_exist?(db,conn)
      msg=%{no connection with sqlite database established, createdb "#{db.sqlite.db}"?}
      if (not (FileTest.file?(db.sqlite.db)) \
      or FileTest.zero?(db.sqlite.db))
        puts msg
        exit
      end
      if conn.is_a?(NilClass)
        puts msg
        exit
      end
    end
    def create_and_populate
      db=SiSU_Env::DbOp.new(@md)
      conn=db.sqlite_discrete.conn_sqlite3
      sdb=SiSU_DbDBI::Create.new(@opt,conn,@file,:sqlite)
      sdb_index=SiSU_DbDBI::Index.new(@opt,conn,@file,:sqlite)
      sdb.output_dir?
      begin
        SiSU_Screen::Ansi.new(
          @opt.act[:color_state][:set],
          'SQLite (discrete)',
          "[#{@opt.f_pth[:lng_is]}] #{@opt.fno}"
        ).green_title_hi unless @opt.act[:quiet][:set]==:on
        sdb.create_db
        sdb.create_table.metadata_and_text
        sdb.create_table.doc_objects
        sdb.create_table.endnotes
        sdb.create_table.endnotes_asterisk
        sdb.create_table.endnotes_plus
        sdb.create_table.urls
        sdb_index.create_indexes
        db_exist?(db,conn)
        sdb_import=SiSU_DbDBI::Import.new(@opt,conn,@file_maint,:sqlite)
        sdb_import.marshal_load
        tell=SiSU_Screen::Ansi.new(
               @opt.act[:color_state][:set],
               "sqlite3 #{db.sqlite.db} database?"
             )
        tell.puts_grey if @opt.act[:verbose][:set]==:on
      rescue
        SiSU_Errors::Rescued.new($!,$@,'-d').location do
          __LINE__.to_s + ':' + __FILE__
        end
        sdb.output_dir?
      end
    end
    def read_sqlite
      begin
        begin
          require 'sqlite3'
          @conn=@db.sqlite.conn_sqlite3
        rescue LoadError
          SiSU_Utils::CodeMarker.new(__LINE__,__FILE__,:fuchsia).error('sqlite3 not available')
        ensure
          Dir.chdir(@opt.f_pth[:pth])
        end
      end
    end
    def connect
      begin
        SiSU_Screen::Ansi.new(
          @opt.act[:color_state][:set],
          "DBI (#{@sql_type}) #{@opt.selections.str}",
          @opt.fns
        ).dbi_title unless @opt.act[:quiet][:set]==:on
        @db.sqlite_discrete.conn_sqlite3
      rescue
        SiSU_Errors::Rescued.new($!,$@,@cf,@opt.fns).location do
          __LINE__.to_s + ':' + __FILE__
        end
      ensure
      end
    end
    def populate
    end
    def sql_maintenance_file
      file=if @opt.act[:maintenance][:set]==:on
        if @opt.fns and not @opt.fns.empty?
          @env=SiSU_Env::InfoEnv.new(@opt.fns) if @opt.fns
          if @sql_type ==:sqlite
            puts "\n#{@env.processing_path.sqlite}/#{@opt.fns}.sql"
          end
          @db=SiSU_Env::InfoDb.new
          @job="sqlite3 #{@db.sqlite.db} < #{@env.processing_path.sqlite}/#{@opt.fns}.sql"
          if @sql_type ==:sqlite
            File.new("#{@env.processing_path.sqlite}/#{@opt.fns}.sql",'w+')
          else
            File.new("#{@env.processing_path.postgresql}/#{@opt.fns}.sql",'w+')
          end
        elsif @opt.fns \
        and @opt.fns.inspect =~/create/
          nil #sort variations later
        else nil
        end
      else nil
      end
      file
    end
  end
end
__END__
#+END_SRC

* db_dbi.rb

#+BEGIN_SRC ruby  :tangle "../lib/sisu/db_dbi.rb"
<<sisu_document_header>>
module  SiSU_DbDBI
  require_relative 'db_columns'                                             # db_columns.rb
  require_relative 'db_tests'                                               # db_tests.rb
  require_relative 'db_create'                                              # db_create.rb
  require_relative 'db_select'                                              # db_select.rb
  require_relative 'db_indexes'                                             # db_indexes.rb
  require_relative 'db_drop'                                                # db_drop.rb
  require_relative 'db_remove'                                              # db_remove.rb
  require_relative 'db_load_tuple'                                          # db_load_tuple.rb
  require_relative 'db_import'                                              # db_import.rb
  class ColumnSize < SiSU_DbColumns::ColumnSize                             # db_columns.rb
  end
  class Test < SiSU_DbTests::Test                                           # db_tests.rb
  end
  class Create <SiSU_DbCreate::Create                                       # db_create.rb
  end
  class Case <SiSU_DbSelect::Case                                           # db_select.rb
  end
  class Index <SiSU_DbIndex::Index                                          # db_indexes.rb
  end
  class Drop <SiSU_DbDrop::Drop                                             # db_drop.rb
  end
  class Remove <SiSU_DbRemove::Remove                                       # db_remove.rb
  end
  class LoadDocuments <SiSU_DbTuple::LoadDocuments                          # db_load_tuple.rb
  end
  class LoadMetadata <SiSU_DbTuple::LoadMetadata                            # db_load_tuple.rb
  end
  class LoadUrls <SiSU_DbTuple::LoadUrls                                    # db_update urls
  end
  class Import <SiSU_DbImport::Import #<SiSU_DB::ColumnSize                 # db_import.rb
  end
end
__END__
#+END_SRC

* db_sqltxt.rb

#+BEGIN_SRC ruby  :tangle "../lib/sisu/db_sqltxt.rb"
<<sisu_document_header>>
module SiSU_DbText
  class Prepare
    def special_character_escape(str)
      str=str.to_s.gsub(/'/m,"''"). #string.gsub!(/'/,"\047") #string.gsub!(/'/,"\\'")
        gsub(/(\\)/m,'\1\1'). #ok but with warnings, double backslash on sqlite #str.gsub!(/[\\]/m,'\\x5C') #ok but with warnings, but not for sqlite #str.gsub!(/(\\)/m,'\1') #ok for sqlite not for pgsql
        gsub(/#{Mx[:br_line]}|#{Mx[:br_nl]}/m,"<br>\n").
        gsub(/#{Mx[:tag_o]}\S+?#{Mx[:tag_c]}/m,''). #check
        gsub(/#{Mx[:lnk_o]}\s*(\S+?\.(?:png|jpg))(?:\s+\d+x\d+)?(.+?)#{Mx[:lnk_c]}\S+/m,'[image: \1] \2').
        gsub(/#{Mx[:lnk_o]}\s*(.+?)\s*#{Mx[:lnk_c]}(?:file|ftp):\/\/\S+?([.,!?]?(?:\s|$))/m,'\1\2').
        gsub(/#{Mx[:lnk_o]}\s*(.+?)\s*#{Mx[:lnk_c]}#{Mx[:url_o]}\S+?#{Mx[:url_c]}/m,'\1')
    end
    def clean_searchable_text_from_document_objects(arr)
      en=[]
      arr=(arr.is_a?(String)) ? [ arr ] : arr
      txt_arr=arr.each.map do |s|
        s=s.gsub(/#{Mx[:fa_o]}[a-z]{1,4}#{Mx[:fa_o_c]}/m,'').
            gsub(/#{Mx[:fa_c_o]}[a-z]{1,4}#{Mx[:fa_c]}/m,'').
            gsub(/<br>/m,' ')
        en << s.scan(/#{Mx[:en_a_o]}\s*(.+?)\s*#{Mx[:en_a_c]}/m)
        s=s.gsub(/#{Mx[:en_a_o]}.+?#{Mx[:en_a_c]}/m,'').
          gsub(/#{Mx[:en_b_o]}.+?#{Mx[:en_b_c]}/m,'').
          gsub(/ \s+/m,' ')
        #p s if s =~/[^ \nA-Za-z0-9'"`?!#@$%^&*=+,.;:\[\]()<>{}‹›|\\\/~_-]/
        s
      end
      txt_arr=txt_arr << en
      txt=txt_arr.flatten.join("\n")
      special_character_escape(txt)
    end
    def clean_document_objects_body(arr)
      en=[]
      arr=(arr.is_a?(String)) ? [ arr ] : arr
      txt_arr=arr.each.map do |s|
        en << s.scan(/#{Mx[:en_a_o]}\s*(.+?)\s*#{Mx[:en_a_c]}/m)
        s=s.
          gsub(/#{Mx[:en_a_o]}\s*(\d+).+?#{Mx[:en_a_c]}/m,
            '<sup>\1</sup>').
          gsub(/#{Mx[:en_b_o]}.+?#{Mx[:en_b_c]}/m,'').
          gsub(/ \s+/m,' ')
        s
      end
      en_arr=en.flatten.each.map do |e|
        e.sub(/^(\d+)\s*/,'<sup>\1</sup> ')
      end
      txt_arr=txt_arr << en_arr
      txt=txt_arr.flatten.join("\n<br>")
      special_character_escape(txt)
    end
    def clean_searchable_text_from_document_source(arr)
      txt_arr,en=[],[]
      arr=(arr.is_a?(String)) ? arr.split(/\n+/m) : arr
      arr.each do |s|
        s=s.gsub(/([*\/_-])\{(.+?)\}\1/m,'\2').
          gsub(/^(?:block|group|poem|code)\{/m,'').
          gsub(/^\}(?:block|group|poem|code)/m,'').
          gsub(/\A(?:@\S+:\s+.+)\Z/m,'')
        if s =~/^:A~/
          if defined? @md.creator \
          and defined? @md.creator.author \
          and not @md.creator.author.empty?
            s=s.gsub(/@author/,@md.creator.author)
          else
            SiSU_Screen::Ansi.new(
              'v',
              'WARNING Document Author information missing; provide @creator: :author:',
              @md.fnb
            ).warn unless @md.opt.act[:quiet][:set]==:on
          end
          if defined? @md.title \
          and defined? @md.title.full \
          and not @md.title.full.empty?
            s=s.gsub(/@title/,@md.title.full)
          else
            SiSU_Screen::Ansi.new(
              'v',
              'WARNING Document Title missing; provide @title:',
              @md.fnb
            ).warn unless @md.opt.act[:quiet][:set]==:on
          end
        end
        s=s.gsub(/^(?:_[1-9]\*?|_\*)\s+/m,'').
          gsub(/^(?:[1-9]\~(\S+)?)\s+/m,'').
          gsub(/^(?::?[A-C]\~(\S+)?)\s+/m,'').
          gsub(/^%{1,3} .+/m,''). #removed even if contained in code block
          gsub(/<br>/m,' ')
        #en << s.scan(/~\{\s*(.+?)\s*\}~/m)
        s=s.gsub(/~\{.+?\}~/m,'').
          gsub(/ \s+/m,' ')
        ##special_character_escape(s)
        #p s if s =~/[^ \nA-Za-z0-9'"`?!#@$%^&*=+,.;:\[\]()<>{}‹›|\\\/~_-]/
        s
      end
      txt_arr << arr << en
      txt=txt_arr.flatten.join("\n")
      txt=special_character_escape(txt)
      txt
    end
    def strip_markup(str) #define rules, make same as in dal clean
      str=str.gsub(/#{Mx[:fa_superscript_o]}(\d+)#{Mx[:fa_superscript_c]}/,'[\1]').
        gsub(/(?:&nbsp\\;|#{Mx[:nbsp]})+/,' ').
        gsub(/#{Mx[:tc_o]}#{Mx[:tc_p]}#{Mx[:tc_p]}\d+(.+)#{Mx[:tc_c]}/u,'\1').         #tables
        gsub(/#{Mx[:tc_p]}#{Mx[:tc_p]}\d+#{Mx[:tc_p]}/u,' ').                          #tables
        gsub(/#{Mx[:tc_p]}/u,' ').                                                     #tables tidy later
        gsub(/<.+?>/,'').
        gsub(/#{Mx[:lnk_o]}.+?\.(?:png|jpg|gif).+?#{Mx[:lnk_c]}(?:file|ftp)\/\/:\S+ /,' [image] '). # else image names found in search
        gsub(/#{Mx[:lnk_o]}.+?\.(?:png|jpg|gif).+?#{Mx[:lnk_c]}#{Mx[:url_o]}\S+?#{Mx[:url_c]}/,' [image]'). # else image names found in search
        gsub(/\s\s+/,' ').
        strip
    end
    def unique_words(str)
      a=str.scan(/[a-zA-Z0-9\\\/_-]{2,}/) #a=str.scan(/\S+{2,}/)
      str=a.uniq.sort.join(' ')
      str
    end
  end
end
__END__
#+END_SRC

* create drop import remove
** db_create.rb

#+BEGIN_SRC ruby  :tangle "../lib/sisu/db_create.rb"
<<sisu_document_header>>
module SiSU_DbCreate
  require_relative 'db_columns'                         # db_columns.rb
  class Create < SiSU_DbColumns::Columns
    require_relative 'se'                               # se.rb
    @@dl=nil
    def initialize(opt,conn,file,sql_type=:pg)
      @opt,@conn,@file,@sql_type=opt,conn,file,sql_type
      @cX=SiSU_Screen::Ansi.new(@opt.act[:color_state][:set]).cX
      @comment=(@sql_type==:pg) \
      ? (SiSU_DbCreate::Comment.new(@conn,@sql_type))
      : nil
      @@dl ||=SiSU_Env::InfoEnv.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::InfoEnv.new(@opt.fns)
      tell=(@sql_type==:sqlite) \
      ? SiSU_Screen::Ansi.new(
          @opt.act[:color_state][:set],
          'Create SQLite db tables in:',
          %{"#{@file}"}
        )
      : SiSU_Screen::Ansi.new(
          @opt.act[:color_state][:set],
          'Create pgSQL db tables in:',
          %{"#{Db[:name_prefix]}#{@env.path.base_markup_dir_stub}"}
        )
      if (@opt.act[:verbose][:set]==:on \
      || @opt.act[:verbose_plus][:set]==:on \
      || @opt.act[:maintenance][:set]==:on)
        tell.dark_grey_title_hi
      end
      SiSU_Env::SystemCall.new.create_pg_db(@env.path.base_markup_dir_stub) if @sql_type==:pg #watch use of path.base_markup_dir_stub instead of stub
    end
    def output_dir?
      dir=SiSU_Env::InfoEnv.new('')
      if @opt.act[:sqlite][:set]==:on
        dir.path.webserv_stub_ensure
      end
    end
    def create_table
      def conn_exec(sql)
        if @sql_type==:pg
          conn_exec_pg(sql)
        elsif @sql_type==:sqlite
          conn_exec_sqlite(sql)
        end
      end
      def conn_exec_pg(sql)
        begin
          @conn.exec_params(sql)
        rescue
          if @conn.is_a?(NilClass)
            errmsg="No pg connection (check pg dependencies)"
            if @opt.act[:no_stop][:set]==:on
              SiSU_Utils::CodeMarker.new(__LINE__,__FILE__,:fuchsia).
                error("#{errmsg}, proceeding without pg output (as requested)")
            else
              SiSU_Utils::CodeMarker.new(__LINE__,__FILE__,:fuchsia).
                error("#{errmsg}, STOPPING")
              exit
            end
          end
        end
      end
      def conn_exec_sqlite(sql)
        begin
          @conn.execute(sql)
        rescue
          if @conn.is_a?(NilClass)
            errmsg="No sqlite3 connection (check sqlite3 dependencies)"
            if @opt.act[:no_stop][:set]==:on
              SiSU_Utils::CodeMarker.new(__LINE__,__FILE__,:fuchsia).
                error("#{errmsg}, proceeding without sqlite output (as requested)")
            else
              SiSU_Utils::CodeMarker.new(__LINE__,__FILE__,:fuchsia).
                error("#{errmsg}, STOPPING")
              exit
            end
          end
        end
      end
      def metadata_and_text
        if (@opt.act[:verbose_plus][:set]==:on \
        or @opt.act[:maintenance][:set]==:on)
          print %{
          currently using sisu_dbi module
          to be populated from document files
          create tables metadata_and_text
          data import through ruby transfer
          }
        end
        create_metadata_and_text=%{
          CREATE TABLE metadata_and_text (
            tid                  BIGINT PRIMARY KEY,
            /* title */
            #{column.title.create_column}
            #{column.title_main.create_column}
            #{column.title_sub.create_column}
            #{column.title_short.create_column}
            #{column.title_edition.create_column}
            #{column.title_note.create_column}
            #{column.title_language.create_column}
            #{column.title_language_char.create_column}
            /* creator */
            #{column.creator_author.create_column}
            #{column.creator_author_honorific.create_column}
            #{column.creator_author_nationality.create_column}
            #{column.creator_editor.create_column}
            #{column.creator_contributor.create_column}
            #{column.creator_illustrator.create_column}
            #{column.creator_photographer.create_column}
            #{column.creator_translator.create_column}
            #{column.creator_prepared_by.create_column}
            #{column.creator_digitized_by.create_column}
            #{column.creator_audio.create_column}
            #{column.creator_video.create_column}
            /* language */
            #{column.language_document.create_column}
            #{column.language_document_char.create_column}
            #{column.language_original.create_column}
            #{column.language_original_char.create_column}
            /* date */
            #{column.date_added_to_site.create_column}
            #{column.date_available.create_column}
            #{column.date_created.create_column}
            #{column.date_issued.create_column}
            #{column.date_modified.create_column}
            #{column.date_published.create_column}
            #{column.date_valid.create_column}
            #{column.date_translated.create_column}
            #{column.date_original_publication.create_column}
            #{column.date_generated.create_column}
            /* publisher */
            #{column.publisher.create_column}
            /* original */
            #{column.original_publisher.create_column}
            #{column.original_language.create_column}
            #{column.original_language_char.create_column}
            #{column.original_source.create_column}
            #{column.original_institution.create_column}
            #{column.original_nationality.create_column}
            /* rights */
            #{column.rights_all.create_column}
            #{column.rights_copyright_text.create_column}
            #{column.rights_copyright_translation.create_column}
            #{column.rights_copyright_illustrations.create_column}
            #{column.rights_copyright_photographs.create_column}
            #{column.rights_copyright_preparation.create_column}
            #{column.rights_copyright_digitization.create_column}
            #{column.rights_copyright_audio.create_column}
            #{column.rights_copyright_video.create_column}
            #{column.rights_license.create_column}
            /* classify */
            #{column.classify_topic_register.create_column}
            #{column.classify_subject.create_column}
            #{column.classify_loc.create_column}
            #{column.classify_dewey.create_column}
            #{column.classify_keywords.create_column}
            /* identifier */
            #{column.identifier_oclc.create_column}
            #{column.identifier_isbn.create_column}
            /* notes */
            #{column.notes_abstract.create_column}
            #{column.notes_description.create_column}
            #{column.notes_comment.create_column}
            #{column.notes_history.create_column}
            #{column.notes_coverage.create_column}
            #{column.notes_relation.create_column}
            /* column.notes_source.create_column */
            #{column.notes_type.create_column}
            #{column.notes_format.create_column}
            #{column.notes_prefix.create_column}
            #{column.notes_prefix_a.create_column}
            #{column.notes_prefix_b.create_column}
            #{column.notes_suffix.create_column}
            /* src */
            #{column.src_filename.create_column}
            #{column.src_fingerprint.create_column}
            #{column.src_filesize.create_column}
            #{column.src_word_count.create_column}
            #{column.src_txt.create_column}
            /* misc */
            #{column.fulltext.create_column}
            #{column.links.create_column.gsub(/,$/,'')}
/*          subj                 VARCHAR(64) NULL, */
/*          contact              VARCHAR(100) NULL, */
/*          information          VARCHAR(100) NULL, */
/*          types                CHAR(1) NULL, */
/*          writing_focus_nationality VARCHAR(100) NULL, */
          );
        }
        conn_exec(create_metadata_and_text)
        @comment.psql.metadata_and_text if @comment
      end
      def doc_objects                                                 # create doc_objects base
        if (@opt.act[:verbose_plus][:set]==:on \
        or @opt.act[:maintenance][:set]==:on)
          print %{
          to be populated from documents files
          create tables doc_objects
          data import through ruby transfer
          }
        end
        create_doc_objects=%{
          CREATE TABLE doc_objects (
            lid             BIGINT PRIMARY KEY,
            metadata_tid    BIGINT REFERENCES metadata_and_text,
            ocn             SMALLINT,
            ocnd            VARCHAR(6),
            ocns            VARCHAR(6),
            clean           TEXT NULL,
            body            TEXT NULL,
            book_idx        TEXT NULL,
            seg             VARCHAR(256) NULL,
            lev_an          VARCHAR(1),
            lev             SMALLINT NULL,
            lev0            SMALLINT,
            lev1            SMALLINT,
            lev2            SMALLINT,
            lev3            SMALLINT,
            lev4            SMALLINT,
            lev5            SMALLINT,
            lev6            SMALLINT,
            lev7            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,
            t_of            VARCHAR(16),
            t_is            VARCHAR(16),
            node            VARCHAR(16) NULL,
            parent          VARCHAR(16) NULL,
            digest_clean    CHAR(#{@@dl}),
            digest_all      CHAR(#{@@dl}),
            types           CHAR(1) NULL
          );
        }
        conn_exec(create_doc_objects)
        @comment.psql.doc_objects if @comment
      end
      def endnotes
        if (@opt.act[:verbose_plus][:set]==:on \
        or @opt.act[:maintenance][:set]==:on)
          print %{
          to be populated from document files
          create tables endnotes
          data import through ruby transfer
          }
        end
        create_endnotes=%{
          CREATE TABLE endnotes (
            nid             BIGINT PRIMARY KEY,
            document_lid    BIGINT REFERENCES doc_objects,
            nr              SMALLINT,
            clean           TEXT NULL,
            body            TEXT NULL,
            ocn             SMALLINT,
            ocnd            VARCHAR(6),
            ocns            VARCHAR(6),
            digest_clean    CHAR(#{@@dl}),
            metadata_tid    BIGINT REFERENCES metadata_and_text
          );
        }
        conn_exec(create_endnotes)
        @comment.psql.endnotes if @comment
      end
      def endnotes_asterisk
        if (@opt.act[:verbose_plus][:set]==:on \
        or @opt.act[:maintenance][:set]==:on)
          print %{
          to be populated from document files
          create tables endnotes_asterisk
          data import through ruby transfer
          }
        end
        create_endnotes_asterisk=%{
          CREATE TABLE endnotes_asterisk (
            nid             BIGINT PRIMARY KEY,
            document_lid    BIGINT REFERENCES doc_objects,
            nr              SMALLINT,
            clean           TEXT NULL,
            body            TEXT NULL,
            ocn             SMALLINT,
            ocnd            VARCHAR(6),
            ocns            VARCHAR(6),
            digest_clean    CHAR(#{@@dl}),
            metadata_tid    BIGINT REFERENCES metadata_and_text
          );
        }
        conn_exec(create_endnotes_asterisk)
        @comment.psql.endnotes_asterisk if @comment
      end
      def endnotes_plus
        if (@opt.act[:verbose_plus][:set]==:on \
        or @opt.act[:maintenance][:set]==:on)
          print %{
          to be populated from document files
          create tables endnotes_plus
          data import through ruby transfer
          }
        end
        create_endnotes_plus=%{
          CREATE TABLE endnotes_plus (
            nid             BIGINT PRIMARY KEY,
            document_lid    BIGINT REFERENCES doc_objects,
            nr              SMALLINT,
            clean           TEXT NULL,
            body            TEXT NULL,
            ocn             SMALLINT,
            ocnd            VARCHAR(6),
            ocns            VARCHAR(6),
            digest_clean    CHAR(#{@@dl}),
            metadata_tid    BIGINT REFERENCES metadata_and_text
          );
        }
        conn_exec(create_endnotes_plus)
        @comment.psql.endnotes_plus if @comment
      end
      def urls                                                       # create doc_objects file links mapping
        if (@opt.act[:verbose_plus][:set]==:on \
        or @opt.act[:maintenance][:set]==:on)
          print %{
          currently using sisu_dbi module
          to be populated from doc_objects files
          create tables urls
          data import through ruby transfer
          }
        end
        create_urls=%{
          CREATE TABLE urls (
            metadata_tid    BIGINT REFERENCES metadata_and_text,
            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)
          );
        }
        conn_exec(create_urls)
        @comment.psql.urls if @comment
      end
      self
    end
  end
  class Comment < SiSU_DbColumns::Columns
    def initialize(conn,sql_type=:pg)
      @conn=conn
      if sql_type == :pg then psql
      end
    end
    def psql
      def conn_execute_array(sql_arr)
        @conn.transaction do |conn|
          sql_arr.each do |sql|
            conn.exec_params(sql)
          end
        end
      end
      def metadata_and_text
        sql_arr=[
          %{COMMENT ON Table metadata_and_text
            IS 'contains SiSU metadata and fulltext for search (including source .sst if shared)';},
          %{COMMENT ON COLUMN metadata_and_text.tid
            IS 'unique';},
          %{#{column.title.column_comment}},
          %{#{column.title_main.column_comment}},
          %{#{column.title_sub.column_comment}},
          %{#{column.title_short.column_comment}},
          %{#{column.title_edition.column_comment}},
          %{#{column.title_note.column_comment}},
          %{#{column.title_language.column_comment}},
          %{#{column.title_language_char.column_comment}},
          %{#{column.creator_author.column_comment}},
          %{#{column.creator_author_honorific.column_comment}},
          %{#{column.creator_author_nationality.column_comment}},
          %{#{column.creator_editor.column_comment}},
          %{#{column.creator_contributor.column_comment}},
          %{#{column.creator_illustrator.column_comment}},
          %{#{column.creator_photographer.column_comment}},
          %{#{column.creator_translator.column_comment}},
          %{#{column.creator_prepared_by.column_comment}},
          %{#{column.creator_digitized_by.column_comment}},
          %{#{column.creator_audio.column_comment}},
          %{#{column.creator_video.column_comment}},
          %{#{column.language_document.column_comment}},
          %{#{column.language_document_char.column_comment}},
          %{#{column.language_original.column_comment}},
          %{#{column.language_original_char.column_comment}},
          %{#{column.date_added_to_site.column_comment}},
          %{#{column.date_available.column_comment}},
          %{#{column.date_created.column_comment}},
          %{#{column.date_issued.column_comment}},
          %{#{column.date_modified.column_comment}},
          %{#{column.date_published.column_comment}},
          %{#{column.date_valid.column_comment}},
          %{#{column.date_translated.column_comment}},
          %{#{column.date_original_publication.column_comment}},
          %{#{column.date_generated.column_comment}},
          %{#{column.publisher.column_comment}},
          %{#{column.original_publisher.column_comment}},
          %{#{column.original_language.column_comment}},
          %{#{column.original_language_char.column_comment}},
          %{#{column.original_source.column_comment}},
          %{#{column.original_institution.column_comment}},
          %{#{column.original_nationality.column_comment}},
          %{#{column.rights_all.column_comment}},
          %{#{column.rights_copyright_text.column_comment}},
          %{#{column.rights_copyright_translation.column_comment}},
          %{#{column.rights_copyright_illustrations.column_comment}},
          %{#{column.rights_copyright_photographs.column_comment}},
          %{#{column.rights_copyright_preparation.column_comment}},
          %{#{column.rights_copyright_digitization.column_comment}},
          %{#{column.rights_copyright_audio.column_comment}},
          %{#{column.rights_copyright_video.column_comment}},
          %{#{column.rights_license.column_comment}},
          %{#{column.classify_topic_register.column_comment}},
          %{#{column.classify_subject.column_comment}},
          %{#{column.classify_loc.column_comment}},
          %{#{column.classify_dewey.column_comment}},
          %{#{column.classify_keywords.column_comment}},
          %{#{column.identifier_oclc.column_comment}},
          %{#{column.identifier_isbn.column_comment}},
          %{#{column.notes_abstract.column_comment}},
          %{#{column.notes_comment.column_comment}},
          %{#{column.notes_description.column_comment}},
          %{#{column.notes_history.column_comment}},
          %{#{column.notes_coverage.column_comment}},
          %{#{column.notes_relation.column_comment}},
          %{#{column.notes_type.column_comment}},
          %{#{column.notes_format.column_comment}},
          %{#{column.notes_prefix.column_comment}},
          %{#{column.notes_prefix_a.column_comment}},
          %{#{column.notes_prefix_b.column_comment}},
          %{#{column.notes_suffix.column_comment}},
          %{#{column.src_filename.column_comment}},
          %{#{column.src_fingerprint.column_comment}},
          %{#{column.src_filesize.column_comment}},
          %{#{column.src_word_count.column_comment}},
          %{#{column.src_txt.column_comment}},
          %{#{column.fulltext.column_comment}},
          %{#{column.links.column_comment}},
        ]
        conn_execute_array(sql_arr)
      end
      def doc_objects
        sql_arr=[
          %{COMMENT ON Table doc_objects
            IS 'contains searchable text of SiSU document objects';},
          %{COMMENT ON COLUMN doc_objects.lid
            IS 'unique';},
          %{COMMENT ON COLUMN doc_objects.metadata_tid
            IS 'tie to title in metadata_and_text';},
          %{COMMENT ON COLUMN doc_objects.lev_an
            IS 'doc level A-D 1-4';},
          %{COMMENT ON COLUMN doc_objects.lev
            IS 'doc level 0-7 \d\~';},
          %{COMMENT ON COLUMN doc_objects.seg
            IS 'segment name from level number 4 (lv 1)';},
          %{COMMENT ON COLUMN doc_objects.ocn
            IS 'object citation number';},
          %{COMMENT ON COLUMN doc_objects.en_a
            IS 'first endnote number in text object (eg. NULL or 34) (used with en_z to create range)';},
          %{COMMENT ON COLUMN doc_objects.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 doc_objects.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 doc_objects.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 doc_objects.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 doc_objects.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 doc_objects.types
            IS 'document types seg scroll';},
          %{COMMENT ON COLUMN doc_objects.clean
            IS 'text object - substantive text: clean, stripped of markup';},
          %{COMMENT ON COLUMN doc_objects.body
            IS 'text object - substantive text: light html markup';},
          %{COMMENT ON COLUMN doc_objects.book_idx
            IS 'book index creation information for paragraph, if provided';},
          %{COMMENT ON COLUMN doc_objects.lev0
            IS 'document structure, level number 0';},
          %{COMMENT ON COLUMN doc_objects.lev1
            IS 'document structure, level number 1';},
          %{COMMENT ON COLUMN doc_objects.lev2
            IS 'document structure, level number 2';},
          %{COMMENT ON COLUMN doc_objects.lev3
            IS 'document structure, level number 3';},
          %{COMMENT ON COLUMN doc_objects.lev4
            IS 'document structure, level number 4';},
          %{COMMENT ON COLUMN doc_objects.lev5
            IS 'document structure, level number 5';},
          %{COMMENT ON COLUMN doc_objects.lev6
            IS 'document structure, level number 6';},
          %{COMMENT ON COLUMN doc_objects.lev7
            IS 'document structure, level number 7';},
          %{COMMENT ON COLUMN doc_objects.t_of
            IS 'document structure, type of object (object is of)';},
          %{COMMENT ON COLUMN doc_objects.t_is
            IS 'document structure, object is';},
          %{COMMENT ON COLUMN doc_objects.node
            IS 'document structure, object node if heading';},
          %{COMMENT ON COLUMN doc_objects.parent
            IS 'document structure, object parent (is a heading)';}
        ]
        conn_execute_array(sql_arr)
      end
      def endnotes
        sql_arr=[
          %{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 doc_objects.metadata_tid
            IS 'tie to title in metadata_and_text - unique for each document';}
        ]
        conn_execute_array(sql_arr)
      end
      def endnotes_asterisk
        sql_arr=[
          %{COMMENT ON Table endnotes_asterisk
            IS 'contains searchable text of SiSU documents endnotes marked with 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 doc_objects.metadata_tid
            IS 'tie to title in metadata_and_text - unique for each document';}
        ]
        conn_execute_array(sql_arr)
      end
      def endnotes_plus
        sql_arr=[
          %{COMMENT ON Table endnotes_plus
            IS 'contains searchable text of SiSU documents endnotes marked with plus';},
          %{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 doc_objects.metadata_tid
            IS 'tie to title in metadata_and_text - unique for each document';},
        ]
        conn_execute_array(sql_arr)
      end
      def urls
        sql_arr=[
          %{COMMENT ON Table urls
            IS 'contains base url links to different SiSU output';},
          %{COMMENT ON COLUMN doc_objects.metadata_tid
            IS 'tie to title in metadata_and_text - 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)';},
        ]
        conn_execute_array(sql_arr)
      end
      self
    end
  end
end
__END__
#+END_SRC

** db_drop.rb

#+BEGIN_SRC ruby  :tangle "../lib/sisu/db_drop.rb"
<<sisu_document_header>>
module SiSU_DbDrop
  require_relative 'utils_response'                   # utils_response.rb
  class Drop
    include SiSU_Response
    def initialize(opt,conn,db_info,sql_type)
      @opt,@conn,@db_info,@sql_type=opt,conn,db_info,sql_type
      case @sql_type
      when :sqlite
        cascade=''
      else
        cascade='CASCADE'
      end
      @drop_table=[
        "DROP TABLE metadata_and_text #{cascade};",
        "DROP TABLE doc_objects #{cascade};",
        "DROP TABLE urls #{cascade};",
        "DROP TABLE endnotes #{cascade};",
        "DROP TABLE endnotes_asterisk #{cascade};",
        "DROP TABLE endnotes_plus #{cascade};",
      ]
    end
    def drop
      def tables                                                              #% drop all tables
        begin
          msg_sqlite="as not all disk space is recovered after dropping the database << #{@db_info.sqlite.db} >>, you may be better off deleting the file, and recreating it as necessary"
          case @sql_type
          when :sqlite
            puts msg_sqlite
            ans=response?('remove sql database?')
            if ans \
            and File.exist?(@db_info.sqlite.db)
              @conn.close
              File.unlink(@db_info.sqlite.db)
              db=SiSU_Env::InfoDb.new
              conn=db.sqlite.conn_sqlite3
              sdb=SiSU_DbDBI::Create.new(@opt,conn,@db_info,@sql_type)
              sdb_index=SiSU_DbDBI::Index.new(@opt,conn,@db_info,@sql_type)
              sdb.output_dir?
              begin
                sdb.create_db
                sdb.create_table.metadata_and_text
                sdb.create_table.doc_objects
                sdb.create_table.endnotes
                sdb.create_table.endnotes_asterisk
                sdb.create_table.endnotes_plus
                sdb.create_table.urls
                sdb_index.create_indexes
              rescue
                SiSU_Errors::Rescued.new($!,$@,'-D').location do
                  __LINE__.to_s + ':' + __FILE__
                end
                sdb.output_dir?
              end
              exit
            else
              @conn.transaction
              @drop_table.each do |d|
                begin
                  @conn.exec_params(d)
                rescue
                  next
                end
                end
              @conn.commit
            end
          when :pg
            @conn.transaction
            @drop_table.each do |d|
              begin
                @conn.exec_params(d)
              rescue
                next
              end
            end
            @conn.commit
          end
        rescue
          case @sql_type
          when :sqlite
            ans=response?('remove sql database?')
            if ans and File.exist?(@db_info.sqlite.db); File.unlink(@db_info.sqlite.db)
            end
          else
            @drop_table.each do |d|
              begin
                @conn.exec_params(d)
              rescue
                next
              end
            end
          end
        ensure
        end
      end
      def indexes
        def conn_execute_array(sql_arr)
          @conn.transaction do |conn|
            sql_arr.each do |sql|
              begin
                conn.exec_params(sql)
              rescue
                next
              end
            end
          end
        end
        def base                                                             #% drop base indexes
          print "\n          drop documents common indexes\n" unless @opt.act[:quiet][:set]==:on
          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_lev0;},
            %{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.act[:quiet][:set]==:on
          sql_arr=[
            %{DROP INDEX idx_clean;},
            %{DROP INDEX idx_endnote},
          ]
          conn_execute_array(sql_arr)
        end
        self
      end
      indexes.base
      @opt.act[:psql][:set]==:on ? '' : indexes.text
      self
    end
  end
end
__END__
#+END_SRC

** db_import.rb

#+BEGIN_SRC ruby  :tangle "../lib/sisu/db_import.rb"
<<sisu_document_header>>
module SiSU_DbImport
  require_relative 'db_columns'                         # db_columns.rb
  require_relative 'db_load_tuple'                      # db_load_tuple.rb
  require_relative 'db_sqltxt'                          # db_sqltxt.rb
  require_relative 'html_lite_shared'                   # html_lite_shared.rb
  class Import < SiSU_DbText::Prepare
    include SiSU_Param
    include SiSU_Screen
    include SiSU_DbAction
    @@dl=nil
    @@hname=nil
    attr_accessor :tp
    def initialize(opt,conn,file_maint,sql_type=:pg)
      @opt,@conn,@file_maint,@sql_type=opt,conn,file_maint,sql_type
      @cX=SiSU_Screen::Ansi.new(@opt.act[:color_state][:set]).cX
      @env=SiSU_Env::InfoEnv.new(@opt.fns)
      @dal="#{@env.processing_path.ao}"
      @fnb=if @opt.fns.empty? \
      or @opt.selections.str.empty?
        ''
      else
        @md=SiSU_Param::Parameters.new(@opt).get
        @md.fnb
      end
      @fnc="#{@dal}/#{@opt.fns}.content.rbm"
      @@seg,@@seg_full='',''                                  #create? consider placing field just before clean text as opposed to seg which contains seg(.html) name info seg_full would contain seg info for levels 5 & 6 where available eg seg_full may be 7.3 (level 5) and 7.3.1 (level 6) where seg  is 7
      @col=Hash.new('')
      @col[:ocn]=''
      @counter={}
      @db=SiSU_Env::InfoDb.new
      if @sql_type==:sqlite
        @driver_sqlite3=
        (@conn.inspect.match(/^(.{10})/)[1] \
        == @db.sqlite.conn_sqlite3.inspect.match(/^(.{10})/)[1]) \
        ? true
        : false
      end
      sql='SELECT MAX(lid) FROM doc_objects'
      begin
        @col[:lid] ||=0
        @col[:lid]=@driver_sqlite3 \
        ? @conn.execute( sql ).join.to_i
        : @conn.exec( sql ).getvalue(0,0).to_i
      rescue
        if @opt.act[:maintenance][:set]==:on
          puts "#{__FILE__}:#{__LINE__}"
        end
      end
      @col[:lid]=0 if @col[:lid].nil? or @col[:lid].to_s.empty?
      sql='SELECT MAX(nid) FROM endnotes'
      begin
        @id_n=@driver_sqlite3 \
        ? @conn.execute( sql ).join.to_i
        : @conn.exec( sql ).getvalue(0,0).to_i
        @id_n ||=0
      rescue
        if @opt.act[:maintenance][:set]==:on
          puts "#{__FILE__}:#{__LINE__}"
        end
      end
      @id_n =0 if @col[:lid].nil? or @col[:lid].to_s.empty?
      @col[:lv0]=@col[:lv1]=@col[:lv2]=@col[:lv3]=@col[:lv4]=@col[:lv5]=@col[:lv6]=@col[:lv7]=0
      @db=SiSU_Env::InfoDb.new
      @pdf_fn=SiSU_Env::FileOp.new(@md).base_filename
      @@dl ||=SiSU_Env::InfoEnv.new.digest.length
    end
    def marshal_load
      require_relative 'ao'                               # ao.rb
      @ao_array=SiSU_AO::Source.new(@opt).get             # ao file drawn here
      if (@opt.act[:verbose][:set]==:on \
      || @opt.act[:verbose_plus][:set]==:on \
      || @opt.act[:maintenance][:set]==:on)
        SiSU_Screen::Ansi.new(
          @opt.act[:color_state][:set],
          "#{@db.psql.db}::#{@opt.fns}"
        ).puts_blue
      end
      SiSU_Screen::Ansi.new(
        @opt.act[:color_state][:set],
        'Marshal Load',
        @fnc
      ).puts_grey if @opt.act[:verbose][:set]==:on
      select_first_match=%{
        SELECT metadata_and_text.tid
        FROM metadata_and_text
        WHERE metadata_and_text.src_filename = '#{@md.fns}'
        AND metadata_and_text.language_document_char = '#{@opt.lng}'
      ;} # note, for .ssm: @md.fns (is set during runtime & is) != @opt.fns @md.opt.fns
      file_exist=if @sql_type==:sqlite
        begin
          @conn.get_first_value(select_first_match)
        rescue SQLite3::Exception => e
          # not tested
          puts "Exception occurred"
          puts e
          SiSU_Utils::CodeMarker.new(__LINE__,__FILE__,:yellow).mark(
            "\n" \
            + 'Attempting to initialize db' + "\n" \
            + 'Creating db tables'
          )
          sdb={
            create: SiSU_DbDBI::Create.new(@opt,@conn,@file_maint,@sql_type),
            index: SiSU_DbDBI::Index.new(@opt,@conn,@file_maint,@sql_type),
          }
          db_action(sdb).create
        end
      else
        begin
          @conn.exec(select_first_match).field_values("tid")[0]
        rescue PG::Error => e
          err=[
            e.result.error_field( PG::Result::PG_DIAG_SEVERITY ),
            e.result.error_field( PG::Result::PG_DIAG_SQLSTATE ),
            e.result.error_field( PG::Result::PG_DIAG_MESSAGE_PRIMARY ),
            e.result.error_field( PG::Result::PG_DIAG_MESSAGE_DETAIL ),
            e.result.error_field( PG::Result::PG_DIAG_MESSAGE_HINT ),
            e.result.error_field( PG::Result::PG_DIAG_STATEMENT_POSITION ),
            e.result.error_field( PG::Result::PG_DIAG_INTERNAL_POSITION ),
            e.result.error_field( PG::Result::PG_DIAG_INTERNAL_QUERY ),
            e.result.error_field( PG::Result::PG_DIAG_CONTEXT ),
            e.result.error_field( PG::Result::PG_DIAG_SOURCE_FILE ),
            e.result.error_field( PG::Result::PG_DIAG_SOURCE_LINE ),
            e.result.error_field( PG::Result::PG_DIAG_SOURCE_FUNCTION ),
          ]
          p err
          if err[2] =~/relation "\S+?" does not exist/ \
          or err.inspect =~/relation "\S+?" does not exist/
            SiSU_Utils::CodeMarker.new(__LINE__,__FILE__,:yellow).mark(
              "\n" \
              + err[2] + "\n" \
              + 'Attempting to initialize db' + "\n" \
              + 'Creating db tables'
            )
            sdb={
              create: SiSU_DbDBI::Create.new(@opt,@conn,@file_maint,@sql_type),
              index: SiSU_DbDBI::Index.new(@opt,@conn,@file_maint,@sql_type),
            }
            db_action(sdb).create
            retry
          end
        end
      end
      if not file_exist
        t_d=[]                                                              # transaction_data
        t_d << db_import_metadata
        t_d << db_import_documents(@ao_array)
        t_d << db_import_urls(@ao_array,@fnc)                              #import OID on/off
        t_d=t_d.flatten
        if (@opt.act[:verbose_plus][:set]==:on \
        || @opt.act[:maintenance][:set]==:on)
          puts @conn.class if defined? @conn.class
          puts @conn.driver_name if defined? @conn.driver_name
          puts @conn.driver if defined? @conn.driver
        end
        begin                                                               #% sql
          if @sql_type==:sqlite
            @conn.transaction do |conn|
              t_d.each do |sql|
                conn.execute(sql)
              end
            end
            #also 'execute' works for sqlite
            #@conn.execute("BEGIN")
            #  t_d.each do |sql|
            #    @conn.execute(sql)
            #  end
            #@conn.execute("COMMIT")
          else
            #'do' works for postgresql
            @conn.exec("BEGIN")
              t_d.each do |sql|
                @conn.exec(sql)
              end
            @conn.exec("COMMIT")
          end
        rescue
          SiSU_Errors::Rescued.new($!,$@,@opt.selections.str,@opt.fns).location do
            __LINE__.to_s + ':' + __FILE__
          end
          sqlfn="#{@env.processing_path.sql}/#{@md.fnb}.sql"
          sql=File.new(sqlfn,'w')
          t_d.each {|i| sql.puts i}
          p sqlfn
          if @opt.act[:maintenance][:set]==:on
            puts sql
            p @conn.methods.sort
            puts "#{__FILE__}:#{__LINE__}"
          end
        ensure
        end
      else
        if file_exist
          @db=SiSU_Env::InfoDb.new
          puts "\nfile #{@opt.fns} in language code #{@opt.lng} already exists in database #{@db.psql.db}, use --update instead?"
        end
      end
    end
    def pf_db_import_transaction_open
    end
    def pf_db_import_transaction_close
    end
    def book_idx_hash_to_str(book_idx)
      book_idx=book_idx ? book_idx : ''
      book_idx_str,book_subidx_part='',''
      if not book_idx.empty?
        book_idx_str=''
        book_idx.each_pair do |k0,v0|
          book_idx_str << %{#{k0}+#{v0[:plus]}}
          book_subidx_part=''
          if v0[:sub].length > 0
            v0[:sub].each do |subterms|
               subterms.each_pair do |k1,v1|
                 book_subidx_part << %{\n  #{k1}+#{v1[:plus]} | }
               end
            end
            book_idx_str=book_idx_str + ':' + book_subidx_part
          end
        end
      end
      book_idx_str
    end
    def db_import_metadata                                                       #% import documents - populate database
      if (@opt.act[:verbose][:set]==:on \
      || @opt.act[:verbose_plus][:set]==:on \
      || @opt.act[:maintenance][:set]==:on)
        print %{ #{@cX.grey}import documents dbi_unit #{@cX.off} }
      end
      @tp={}
      @md=SiSU_Param::Parameters.new(@opt).get
#% sisutxt & fulltxt
      if FileTest.exist?(@md.fns)
        txt_arr=IO.readlines(@md.fns,'')
        src=txt_arr.join("\n")
        src=special_character_escape(src)
        @tp[:sisutxt_f],@tp[:sisutxt_i]='sisutxt, ',"'#{src}', "
        txt=clean_searchable_text_from_document_source(txt_arr)
        #txt=special_character_escape(txt)
        @tp[:fulltxt_f],@tp[:fulltxt_i]='fulltxt, ',"'#{txt}', "
      end
#% title
      if defined? @md.title.full \
      and @md.title.full=~/\S+/                                              # DublinCore 1 - title
        #@tp[:title]=@md.title.full
        #special_character_escape(@tp[:title])
        #@tp[:title_f],@tp[:title_i]='title, ',"'#{@tp[:title]}', "
        sql='SELECT MAX(tid) FROM metadata_and_text;'
        begin
          @@id_t ||=0
          id_t=@driver_sqlite3 \
          ? @conn.execute( sql ).join.to_i # { |x| id_t=x.join.to_i }
          : @conn.exec( sql ).getvalue(0,0).to_i
          @@id_t=id_t if id_t
        rescue
          if @opt.act[:maintenance][:set]==:on
            puts "#{__FILE__} #{__LINE__}"
          end
        end
        @@id_t+=1 #bug related, needs to be performed once at start of file, but consider moving, as, placed here it means program will fail if document header lacks @title:
        if (@opt.act[:verbose][:set]==:on \
        || @opt.act[:verbose_plus][:set]==:on \
        || @opt.act[:maintenance][:set]==:on)
          puts %{\n#{@cX.grey}Processing file number#{@cX.off}: #{@cX.green}#{@@id_t}#{@cX.off}}
        end
      end
      ################ CLEAR ##############
      SiSU_DbDBI::Test.new(self,@opt).verify                          #% import title names, filenames (tuple)
      t=SiSU_DbTuple::LoadMetadata.new(@conn,@@id_t,@md,@file_maint)
      tuple=t.tuple
      tuple
    end
    def db_import_documents(ao_array)                                     #% import documents - populate main database table, import into substantive database tables (tuple)
      begin
        @col[:tid]=@@id_t
        @en,@en_ast,@en_pls,@tuple_array=[],[],[],[]
        @col[:en_a],@col[:en_z]=nil,nil
        ao_array.each do |data|
          data.obj=data.obj.gsub(/#{Mx[:fa_bold_o]}(.+?)#{Mx[:fa_bold_c]}/,'\1').
            gsub(/#{Mx[:fa_italics_o]}(.+?)#{Mx[:fa_italics_c]}/,'\1').
            gsub(/#{Mx[:fa_underscore_o]}(.+?)#{Mx[:fa_underscore_c]}/,'\1').
            gsub(/#{Mx[:fa_superscript_o]}(.+?)#{Mx[:fa_superscript_c]}/,'\1').
            gsub(/#{Mx[:fa_subscript_o]}(.+?)#{Mx[:fa_subscript_c]}/,'\1').
            gsub(/#{Mx[:fa_insert_o]}(.+?)#{Mx[:fa_insert_c]}/,'\1').
            gsub(/#{Mx[:fa_cite_o]}(.+?)#{Mx[:fa_cite_c]}/,'\1').
            gsub(/#{Mx[:fa_strike_o]}(.+?)#{Mx[:fa_strike_c]}/,'\1').
            gsub(/#{Mx[:fa_monospace_o]}(.+?)#{Mx[:fa_monospace_c]}/,'\1').
            gsub(/#{Mx[:gl_o]}(●)#{Mx[:gl_c]}\s*/,'\1 ').
            gsub(/#{Mx[:tag_o]}\S+?#{Mx[:tag_c]}/,'') #check
          @col[:seg]=@@seg
          if data.of ==:para \
          || data.of ==:heading \
          || data.of ==:heading_insert \
          || data.of ==:block \
          || data.of ==:group      # regular text what of code-blocks grouped text etc.
            notedata=data.obj.dup
                                                                               #% :headings
            if data.is==:heading \
            && (data.ln.inspect=~/[0-3]/)
              (
                @col[:lev],
                txt,@col[:ocn],
                @col[:lev_an],
                @col[:ocnd],@col[:ocns],
                @col[:t_of],@col[:t_is],
                @col[:node],@col[:parent],
                @col[:digest_clean],@col[:digest_all]=
                data.ln,
                data.obj,data.ocn,
                data.lv,
                data.odv,data.osp,
                data.of,data.is,
                data.node,data.parent,
                '',''
              )
              @col[:lid]+=1
              txt=endnotes(txt).extract_any
              body=SiSU_FormatShared::CSS_Format.new(@md,data).lev4_minus
              @col[:body]=clean_document_objects_body(body)
              plaintext=@col[:body].dup
              plaintext=strip_markup(plaintext)
              @col[:plaintext]=clean_searchable_text_from_document_objects(plaintext)
              book_idx=book_idx_hash_to_str(data.idx)
              @col[:book_idx]=clean_searchable_text_from_document_objects(book_idx)
              if @en[0] then @en_a,@en_z=@en[0].first,@en[0].last
              end
              if @en_ast[0] then @en_a_asterisk,@en_z_asterisk=@en_ast[0].first,@en_ast[0].last
              end
              if @en_pls[0] then @en_a_plus,@en_z_plus=@en_pls[0].first,@en_pls[0].last
              end
              t=SiSU_DbTuple::LoadDocuments.new(@conn,@col,@opt,@file_maint)
              @tuple_array << t.tuple
              case @col[:lev]
              when /0/ then @col[:lv0]+=1
              when /1/ then @col[:lv1]+=1
              when /2/ then @col[:lv2]+=1
              when /3/ then @col[:lv3]+=1
              when /4/ then @col[:lv4]+=1
              end
              @col[:lev]=@col[:plaintext]=@col[:body]=''
            elsif data.is==:heading \
            && data.ln==4
              (
                @@seg,txt,
                @col[:ocn],@col[:lev_an],
                @col[:ocnd],@col[:ocns],
                @col[:t_of],@col[:t_is],
                @col[:node],@col[:parent],
                @col[:digest_clean],@col[:digest_all]=
                data.name,data.obj,
                data.ocn,data.lv,
                data.odv,data.osp,
                data.of,data.is,
                data.node,data.parent,
                '',''
              )
              @col[:seg]=@@seg
              @col[:lv4]+=1
              @col[:lid]+=1
              @col[:lev]=4
              @hname=if @col[:seg] \
              and not @col[:seg].to_s.empty?
                @@hname=@col[:seg].to_s
              else @@hname
              end
              @env=SiSU_Env::InfoEnv.new(@md.fns)
              @base_url="#{@env.url.root}/#{@md.fnb}/#{@hname}.html"
              txt=endnotes(txt).extract_any
              body=SiSU_FormatShared::CSS_Format.new(@md,data).lev4_plus
              @col[:body]=clean_document_objects_body(body)
              plaintext=@col[:body].dup
              plaintext=strip_markup(plaintext)
              @col[:plaintext]=clean_searchable_text_from_document_objects(plaintext)
              book_idx=book_idx_hash_to_str(data.idx)
              @col[:book_idx]=clean_searchable_text_from_document_objects(book_idx)
              @en_a,@en_z=@en[0].first,@en[0].last if @en[0]
              @en_a_asterisk,@en_z_asterisk=@en_ast[0].first,@en_ast[0].last if @en_ast[0]
              @en_a_plus,@en_z_plus=@en_pls[0].first,@en_pls[0].last if @en_pls[0]
              t=SiSU_DbTuple::LoadDocuments.new(@conn,@col,@opt,@file_maint)
              @tuple_array << t.tuple
              @col[:lev]=@col[:plaintext]=@col[:body]=''
            elsif data.is==:heading \
            && data.ln==5
              (
                txt,
                @col[:ocn],@col[:lev_an],
                @col[:ocnd],@col[:ocns],
                @col[:t_of],@col[:t_is],
                @col[:node],@col[:parent],
                @col[:digest_clean],@col[:digest_all]=
                data.obj,
                data.ocn,data.lv,
                data.odv,data.osp,
                data.of,data.is,
                data.node,data.parent,
                '',''
              )
              @@seg_full=data.name if data.is==:heading \
              && data.ln==5 \
              && data.name #check data.name
              @@seg ||='' #nil # watch
              @col[:seg]=@@seg
              @col[:lv5]+=1
              @col[:lid]+=1
              @col[:lev]=5
              @hname=if @col[:seg] \
              and not @col[:seg].to_s.empty?
                @@hname=@col[:seg].to_s
              else @@hname
              end
              @env=SiSU_Env::InfoEnv.new(@md.fns)
              @base_url="#{@env.url.root}/#{@md.fnb}/#{@hname}.html"
              txt=endnotes(txt).extract_any
              body=SiSU_FormatShared::CSS_Format.new(@md,data).lev4_plus
              @col[:body]=clean_document_objects_body(body)
              plaintext=@col[:body].dup
              plaintext=strip_markup(plaintext)
              @col[:plaintext]=clean_searchable_text_from_document_objects(plaintext)
              book_idx=book_idx_hash_to_str(data.idx)
              @col[:book_idx]=clean_searchable_text_from_document_objects(book_idx)
              @en_a,@en_z=@en[0].first,@en[0].last if @en[0]
              @en_a_asterisk,@en_z_asterisk=@en_ast[0].first,@en_ast[0].last if @en_ast[0]
              @en_a_plus,@en_z_plus=@en_pls[0].first,@en_pls[0].last if @en_pls[0]
              t=SiSU_DbTuple::LoadDocuments.new(@conn,@col,@opt,@file_maint)
              @tuple_array << t.tuple
              @col[:lev]=@col[:plaintext]=@col[:body]=''
            elsif data.is==:heading \
            && data.ln==6
              txt,       @col[:ocn],@col[:lev_an],@col[:ocnd],@col[:ocns],@col[:t_of],@col[:t_is],@col[:node],@col[:parent],@col[:digest_clean],@col[:digest_all]=
                data.obj,data.ocn,  data.lv,      data.odv,   data.osp,   data.of,    data.is,    data.node,  data.parent,  '',                 ''
              @@seg_full=data.name if data.is==:heading && data.ln==6 && data.name #check data.name
              @@seg ||='' #nil # watch
              @col[:seg]=@@seg
              @col[:lv6]+=1
              @col[:lid]+=1
              @col[:lev]=6
              @hname=if @col[:seg] \
              and not @col[:seg].to_s.empty?
                @@hname=@col[:seg].to_s
              else @@hname
              end
              @env=SiSU_Env::InfoEnv.new(@md.fns)
              @base_url="#{@env.url.root}/#{@md.fnb}/#{@hname}.html"
              txt=endnotes(txt).extract_any
              body=SiSU_FormatShared::CSS_Format.new(@md,data).lev4_plus
              @col[:body]=clean_document_objects_body(body)
              plaintext=@col[:body].dup
              plaintext=strip_markup(plaintext)
              @col[:plaintext]=clean_searchable_text_from_document_objects(plaintext)
              book_idx=book_idx_hash_to_str(data.idx)
              @col[:book_idx]=clean_searchable_text_from_document_objects(book_idx)
              @en_a,@en_z=@en[0].first,@en[0].last if @en[0]
              @en_a_asterisk,@en_z_asterisk=@en_ast[0].first,@en_ast[0].last if @en_ast[0]
              @en_a_plus,@en_z_plus=@en_pls[0].first,@en_pls[0].last if @en_pls[0]
              t=SiSU_DbTuple::LoadDocuments.new(@conn,@col,@opt,@file_maint)
              @tuple_array << t.tuple
              @col[:lev]=@col[:plaintext]=@col[:body]=''
            elsif data.is==:heading \
            && data.ln==7
              txt,       @col[:ocn],@col[:lev_an],@col[:ocnd],@col[:ocns],@col[:t_of],@col[:t_is],@col[:node],@col[:parent],@col[:digest_clean],@col[:digest_all]=
                data.obj,data.ocn,  data.lv,      data.odv,   data.osp,   data.of,    data.is,    data.node,  data.parent,  '',                 ''
              @@seg_full=data.name if data.is==:heading && data.ln==7 && data.name #check data.name
              @@seg ||='' #nil # watch
              @col[:seg]=@@seg
              @col[:lv7]+=1
              @col[:lid]+=1
              @col[:lev]=7
              @hname=if @col[:seg] \
              and not @col[:seg].to_s.empty?
                @@hname=@col[:seg].to_s
              else @@hname
              end
              @env=SiSU_Env::InfoEnv.new(@md.fns)
              @base_url="#{@env.url.root}/#{@md.fnb}/#{@hname}.html"
              txt=endnotes(txt).extract_any
              body=SiSU_FormatShared::CSS_Format.new(@md,data).lev4_plus
              @col[:body]=clean_document_objects_body(body)
              plaintext=@col[:body].dup
              plaintext=strip_markup(plaintext)
              @col[:plaintext]=clean_searchable_text_from_document_objects(plaintext)
              book_idx=book_idx_hash_to_str(data.idx)
              @col[:book_idx]=clean_searchable_text_from_document_objects(book_idx)
              @en_a,@en_z=@en[0].first,@en[0].last if @en[0]
              @en_a_asterisk,@en_z_asterisk=@en_ast[0].first,@en_ast[0].last if @en_ast[0]
              @en_a_plus,@en_z_plus=@en_pls[0].first,@en_pls[0].last if @en_pls[0]
              t=SiSU_DbTuple::LoadDocuments.new(@conn,@col,@opt,@file_maint)
              @tuple_array << t.tuple
              @col[:lev]=@col[:plaintext]=@col[:body]=''
                                                                               #% :structure :layout :comment
            elsif data.of==:structure \
            || data.of==:layout \
            || data.of==:comment
              #added watch
                                                                               #% :
            else                                                               #% regular text
              @col[:lid]+=1
              (
                txt=''
                txt,@col[:ocn],
                @col[:ocnd],@col[:ocns],
                @col[:t_of],@col[:t_is],
                @col[:node],@col[:parent],
                @col[:digest_clean],@col[:digest_all],
                @col[:lev]=
                data.obj,data.ocn,
                data.odv,data.osp,
                data.of,data.is,
                '',data.parent,
                '','',
                9
              )
              @hname=if @col[:seg] \
              and not @col[:seg].to_s.empty?
                @@hname=@col[:seg].to_s
              else @@hname
              end
              @env=SiSU_Env::InfoEnv.new(@md.fns)
              @base_url="#{@env.url.root}/#{@md.fnb}/#{@hname}.html"
              txt=endnotes(txt).extract_any
              if @sql_type==:pg \
              and txt.size > (SiSU_DbColumns::ColumnSize.new.document_clean - 1)             # examine pg build & remove limitation
                puts "\n\nTOO LARGE (TXT - see error log)\n\n"
                open("#{Dir.pwd}/pg_documents_error_log",'a') do |error|
                  error.puts("\n#{@opt.fns}\nTEXT BODY\n#{@col[:body].size} object #{@col[:ocn]} -> #{@col[:body].slice(0..500)}")
                end
                txt=%{\n\nLARGE TEXT BLOCK OMITTED\n\n}
              end
              @en_a,@en_z=@en[0].first,@en[0].last if @en[0]
              @en_a_asterisk,@en_z_asterisk=@en_ast[0].first,@en_ast[0].last if @en_ast[0]
              @en_a_plus,@en_z_plus=@en_pls[0].first,@en_pls[0].last if @en_pls[0]
              body=if data.is==:table
                SiSU_FormatShared::CSS_Format.new(@md,data).html_table
              elsif data.is==:code
                SiSU_FormatShared::CSS_Format.new(@md,data).code
              elsif defined? data.indent \
              and defined? data.hang \
              and data.indent =~/[1-9]/ \
              and data.indent == data.hang
                SiSU_FormatShared::CSS_Format.new(@md,data).indent(data.indent)
              elsif defined? data.indent \
              and defined? data.hang \
              and data.hang =~/[0-9]/ \
              and data.indent != data.hang
                SiSU_FormatShared::CSS_Format.new(@md,data).hang_indent(data.hang,data.indent)
              else
                SiSU_FormatShared::CSS_Format.new(@md,data).norm
              end
              @col[:body]=clean_document_objects_body(body)
              plaintext=@col[:body].dup
              plaintext=strip_markup(plaintext)
              @col[:plaintext]=clean_searchable_text_from_document_objects(plaintext)
              book_idx=book_idx_hash_to_str(data.idx)
              @col[:book_idx]=clean_searchable_text_from_document_objects(book_idx)
              t=SiSU_DbTuple::LoadDocuments.new(@conn,@col,@opt,@file_maint)
              @tuple_array << t.tuple
              @en,@en_ast,@en_pls=[],[],[]
              @col[:en_a]=@col[:en_z]=nil
              @col[:lev]=@col[:plaintext]=@col[:body]=@col[:words]=''
            end
            if notedata =~/#{Mx[:en_a_o]}.+?#{Mx[:en_a_c]}/                                         #% import into database endnotes tables
              endnote_array=notedata.scan(/#{Mx[:en_a_o]}.+?#{Mx[:en_a_c]}/)
              endnote_array.each do |inf|
                if inf[/#{Mx[:en_a_o]}\d+.+?#{Mx[:en_a_c]}/]
                  if inf[/#{Mx[:en_a_o]}(\d+)(.+?)#{Mx[:en_a_c]}/]
                    nr,txt,digest_clean=$1,$2.strip,0
                  end
                  @id_n ||=0
                  @id_n+=1
                  txt=special_character_escape(txt)
                  body=SiSU_FormatShared::CSS_Format.new(@md,data).endnote(nr,txt)
                  txt=strip_markup(txt)
                  if txt.size > (SiSU_DbColumns::ColumnSize.new.endnote_clean - 1)
                    puts "\n\nTOO LARGE (ENDNOTE - see error log)\n\n"
                    open("#{Dir.pwd}/pg_documents_error_log",'a') do |error|
                      error.puts("\n#{@opt.fns}\nENDNOTE\n#{txt.size} object #{@col[:ocn]},#{@col[:ocnd]},#{@col[:ocns]} -> #{txt.slice(0..500)}")
                    end
                    txt=%{\n\nLARGE TEXT BLOCK OMITTED\n\n}
                  end
                  if txt
                    en={
                      type: 'endnotes',
                      id:      @id_n,
                      lid:     @col[:lid],
                      nr:      nr,
                      txt:     txt,
                      body:    body,
                      ocn:     @col[:ocn],
                      ocnd:    @col[:ocnd],
                      ocns:    @col[:ocns],
                      id_t:    @@id_t,
                      hash:    digest_clean
                    }
                    t=SiSU_DbTuple::LoadEndnotes.new(@conn,en,@opt,@file_maint)
                    @tuple_array << t.tuple
                  end
                end
              end
              word_mode=notedata.scan(/\S+/)
            end
            if notedata =~/#{Mx[:en_b_o]}\*.+?#{Mx[:en_b_c]}/                                      #% import into database endnotes tables
              endnote_array=notedata.scan(/#{Mx[:en_b_o]}\*.+?#{Mx[:en_b_c]}/)
              endnote_array.each do |inf|
                if inf[/#{Mx[:en_b_o]}\*\d+.+?#{Mx[:en_b_c]}/]                    # dal new endnotes 2003w31/1
                  if inf[/#{Mx[:en_b_o]}[*](\d+)(.+?)#{Mx[:en_b_c]}/]           # dal new endnotes 2003w31/1
                    nr,txt,digest_clean=$1,$2.strip,0
                  end
                  @id_n+=1
                  txt=special_character_escape(txt)
                  body=SiSU_FormatShared::CSS_Format.new(@md,data).endnote(nr,txt)
                  txt=strip_markup(txt)
                  if txt.size > (SiSU_DbColumns::ColumnSize.new.endnote_clean - 1)
                    puts "\n\nTOO LARGE (ENDNOTE - see error log)\n\n"
                    open("#{Dir.pwd}/pg_documents_error_log",'a') do |error|
                      error.puts("\n#{@opt.fns}\nENDNOTE\n#{txt.size} object #{@col[:ocn]},#{@col[:ocnd]},#{@col[:ocns]} -> #{txt.slice(0..500)}")
                    end
                    txt=%{\n\nLARGE TEXT BLOCK OMITTED\n\n}
                  end
                  if txt
                    en={
                      type: 'endnotes_asterisk',
                      id:      @id_n,
                      lid:     @col[:lid],
                      nr:      nr,
                      txt:     txt,
                      body:    body,
                      ocn:     @col[:ocn],
                      ocnd:    @col[:ocnd],
                      ocns:    @col[:ocns],
                      id_t:    @@id_t,
                      hash:    digest_clean
                    }
                    t=SiSU_DbTuple::LoadEndnotes.new(@conn,en,@opt,@file_maint)
                    @tuple_array << t.tuple
                  end
                end
              end
              word_mode=notedata.scan(/\S+/)
            end
            if notedata =~/#{Mx[:en_b_o]}\+.+?#{Mx[:en_b_c]}/                                           #% import into database endnotes tables
              endnote_array=notedata.scan(/#{Mx[:en_b_o]}\+.+?#{Mx[:en_b_c]}/)
              endnote_array.each do |inf|
                if inf[/#{Mx[:en_b_o]}\+\d+.+?#{Mx[:en_b_c]}/]                        # dal new endnotes 2003w31/1
                  if inf[/#{Mx[:en_b_o]}[+](\d+)(.+?)#{Mx[:en_b_c]}/]               # dal new endnotes 2003w31/1
                    nr,txt,digest_clean=$1,$2.strip,0
                  end
                  @id_n+=1
                  txt=special_character_escape(txt)
                  body=SiSU_FormatShared::CSS_Format.new(@md,data).endnote(nr,txt)
                  txt=strip_markup(txt)
                  if txt.size > (SiSU_DbColumns::ColumnSize.new.endnote_clean - 1)
                    puts "\n\nTOO LARGE (ENDNOTE - see error log)\n\n"
                    open("#{Dir.pwd}/pg_documents_error_log",'a') do |error|
                      error.puts("\n#{@opt.fns}\nENDNOTE\n#{txt.size} object #{@col[:ocn]},#{@col[:ocnd]},#{@col[:ocns]} -> #{txt.slice(0..500)}")
                    end
                    txt=%{\n\nLARGE TEXT BLOCK OMITTED\n\n}
                  end
                  if txt
                    en={
                      type: 'endnotes_plus',
                      id:      @id_n,
                      lid:     @col[:lid],
                      nr:      nr,
                      txt:     txt,
                      body:    body,
                      ocn:     @col[:ocn],
                      ocnd:    @col[:ocnd],
                      ocns:    @col[:ocns],
                      id_t:    @@id_t,
                      hash:    digest_clean
                    }
                    t=SiSU_DbTuple::LoadEndnotes.new(@conn,en,@opt,@file_maint)
                    @tuple_array << t.tuple
                  end
                end
              end
              word_mode=notedata.scan(/\S+/)
            end
          end
        end
      rescue
        SiSU_Errors::Rescued.new($!,$@,@opt.selections.str,@opt.fns).location do
          __LINE__.to_s + ':' + __FILE__
        end
      ensure
      end
      @tuple_array
    end
    def endnotes(txt)
      @txt=txt
      def extract_any
        if @txt =~/(?:#{Mx[:en_a_o]}|#{Mx[:en_b_o]})[*+]?(\d+)\s+.+?(?:#{Mx[:en_a_c]}|#{Mx[:en_b_c]})/
          endnotes(@txt).range
          @en << endnotes(@txt).standard if @txt =~/#{Mx[:en_a_o]}.+?#{Mx[:en_a_c]}/
          @en_ast << endnotes(@txt).asterisk if @txt =~/#{Mx[:en_b_o]}\*.+?#{Mx[:en_b_c]}/
          @en_pls << endnotes(@txt).plus if @txt =~/#{Mx[:en_b_o]}\+.+?#{Mx[:en_b_c]}/
          @txt=endnotes(@txt).clean_text
        end
        @txt
      end
      def standard
        (@txt =~/#{Mx[:en_a_o]}.+?#{Mx[:en_a_c]}/) \
        ? @txt.scan(/#{Mx[:en_a_o]}(\d+).+?#{Mx[:en_a_c]}/)
        : nil
      end
      def asterisk
        (@txt =~/#{Mx[:en_b_o]}\*.+?#{Mx[:en_b_c]}/) \
        ? @txt.scan(/#{Mx[:en_b_o]}[*](\d+).+?#{Mx[:en_b_c]}/)
        : nil
      end
      def plus
        (@txt =~/#{Mx[:en_b_o]}\+.+?#{Mx[:en_b_c]}/) \
        ? @txt.scan(/#{Mx[:en_b_o]}[+](\d+).+?#{Mx[:en_b_c]}/)
        : nil
      end
      def clean_text(base_url=nil)
        @txt=if base_url
          @txt.gsub(/#{Mx[:en_a_o]}(\d+).+?#{Mx[:en_a_c]}/,%{<sup><a href="#{base_url}#_\\1" name="-\\1">\\1</a></sup>}).
            gsub(/#{Mx[:en_b_o]}([*]\d+).+?#{Mx[:en_b_c]}/,%{<sup><a href="#{base_url}#_\\1" name="-\\1">\\1</a></sup>}).
            gsub(/#{Mx[:en_b_o]}([+]\d+).+?#{Mx[:en_b_c]}/,%{<sup><a href="#{base_url}#_\\1" name="-\\1">\\1</a></sup>})
        else
          @txt.gsub(/#{Mx[:en_a_o]}(\d+).+?#{Mx[:en_a_c]}/,'<sup>\1</sup>').
            gsub(/#{Mx[:en_b_o]}([*]\d+).+?#{Mx[:en_b_c]}/,'<sup>\1</sup>').
            gsub(/#{Mx[:en_b_o]}([+]\d+).+?#{Mx[:en_b_c]}/,'<sup>\1</sup>')
        end
        @txt
      end
      def range
        @col[:en_a]=@col[:en_z]=nil
        if @txt =~/#{Mx[:en_a_o]}.+?#{Mx[:en_a_c]}|#{Mx[:en_b_o]}([*]\d+).+?#{Mx[:en_b_c]}|#{Mx[:en_b_o]}([+]\d+).+?#{Mx[:en_b_c]}/
          word_array=@txt.scan(/\S+/)
          word_array.each do |w|
            if w[/(?:#{Mx[:en_a_o]}|#{Mx[:en_b_o]})[*+]?(\d+)\s+.+?(?:#{Mx[:en_a_c]}|#{Mx[:en_b_c]})(?:#{Mx[:en_a_c]}|#{Mx[:en_b_c]})/]                                                # not tested since change 2003w31
              @col[:en_a]=$1 unless @col[:en_a]
              @col[:en_z]=@col[:en_a].dup unless @col[:en_a]
              @col[:en_z]=$1 if @col[:en_a]
            end
          end
        end
        @col
      end
      self
    end
    def db_import_urls(dbi_unit,content)                                           #% import documents OID - populate database
      begin
        @fnc=content
        @env=SiSU_Env::InfoEnv.new(@opt.fns)
        f,u={},{}
        if @fnb.empty? \
        or @fnb.nil?
          p 'file output path error' #remove
        end
        if FileTest.file?("#{@md.file.output_path.txt.dir}/#{@md.file.base_filename.txt}")==true
          f[:txt],u[:txt]='plaintext,', "'#{@md.file.output_path.txt.url}/#{@md.file.base_filename.txt}',"
        end
        if FileTest.file?("#{@md.file.output_path.html_seg.dir}/#{@md.file.base_filename.html_segtoc}")==true
          f[:html_toc],u[:html_toc]='html_toc,', "'#{@md.file.output_path.html_seg.url}/#{@md.file.base_filename.html_segtoc}',"
        end
        if FileTest.file?("#{@md.file.output_path.html_scroll.dir}/#{@md.file.base_filename.html_scroll}")==true
          f[:html_doc],u[:html_doc]='html_doc,', "'#{@md.file.output_path.html_scroll.url}/#{@md.file.base_filename.html_scroll}',"
        end
        if FileTest.file?("#{@md.file.output_path.xhtml.dir}/#{@md.file.base_filename.xhtml}")==true
          f[:xhtml],u[:xhtml]='xhtml,', "'#{@md.file.output_path.xhtml.url}/#{@md.file.base_filename.xhtml}',"
        end
        if FileTest.file?("#{@md.file.output_path.xml_sax.dir}/#{@md.file.base_filename.xml_sax}")==true
          f[:xml_sax],u[:xml_sax]='xml_sax,', "'#{@md.file.output_path.xml_sax.url}/#{@md.file.base_filename.xml_sax}',"
        end
        if FileTest.file?("#{@md.file.output_path.xml_dom.dir}/#{@md.file.base_filename.xml_dom}")==true
          f[:xml_dom],u[:xml_dom]='xml_dom,', "'#{@md.file.output_path.xml_dom.url}/#{@md.file.base_filename.xml_dom}',"
        end
        if FileTest.file?("#{@md.file.output_path.epub.dir}/#{@md.file.base_filename.epub}")==true
          f[:epub],u[:epub]='epub,', "'#{@md.file.output_path.epub.url}/#{@md.file.base_filename.epub}',"
        end
        if FileTest.file?("#{@md.file.output_path.odt.dir}/#{@md.file.base_filename.odt}")==true
          f[:odf],u[:odf]='odf,', "'#{@md.file.output_path.odt.url}/#{@md.file.base_filename.odt}',"
        end
        if FileTest.file?("#{@md.file.output_path.pdf.dir}/#{@pdf_fn.pdf_p_a4}")==true #\
        #or FileTest.file?("#{@md.file.output_path.pdf.dir}/#{@pdf_fn.pdf_p_letter}")==true
          f[:pdf_p],u[:pdf_p]='pdf_p,', "'#{@md.file.output_path.pdf.url}/#{@pdf_fn.pdf_p_a4}',"
        end
        if FileTest.file?("#{@md.file.output_path.pdf.dir}/#{@pdf_fn.pdf_l_a4}")==true #\
        #or FileTest.file?("#{@md.file.output_path.pdf.dir}/#{@pdf_fn.pdf_l_letter}")==true
          f[:pdf_l],u[:pdf_l]='pdf_l,', "'#{@md.file.output_path.pdf.url}/#{@pdf_fn.pdf_l_a4}',"
        end
        if FileTest.file?("#{@md.file.output_path.html_concordance.dir}/#{@md.file.base_filename.html_concordance}")==true
          f[:concordance],u[:concordance]='concordance,', "'#{@md.file.output_path.html_concordance.url}/#{@md.file.base_filename.html_concordance}',"
        end
        #if FileTest.file?("#{@md.file.output_path.x.dir}/#{@md.file.base_filename.x}")==true
        #  f[:latex_p],u[:latex_p]='latex_p,', "'#{@md.file.output_path.x.url}/#{@md.file.base_filename.x}',"
        #end
        ##if FileTest.file?("#{out}/#{@fnb}/#{@opt.fns}.tex")==true
        ##  f[:latex_p],u[:latex_p]='latex_p,', "'#{base}/#{@fnb}/#{@opt.fns}.tex',"
        ##end
        #if FileTest.file?("#{@md.file.output_path.x.dir}/#{@md.file.base_filename.x}")==true
        #  f[:latex_l],u[:latex_l]='latex_l,', "'#{@md.file.output_path.x.url}/#{@md.file.base_filename.x}',"
        #end
        ##if FileTest.file?("#{out}/#{@fnb}/#{@opt.fns}.landscape.tex")==true
        ##  f[:latex_l],u[:latex_l]='latex_l,', "'#{base}/#{@fnb}/#{@opt}.fns}.landscape.tex',"
        ##end
        if FileTest.file?("#{@md.file.output_path.digest.dir}/#{@md.file.base_filename.digest}")==true
          f[:digest],u[:digest]='digest,', "'#{@md.file.output_path.digest.url}/#{@md.file.base_filename.digest}',"
        end
        if FileTest.file?("#{@md.file.output_path.manifest.dir}/#{@md.file.base_filename.manifest}")==true #revisit, was to be text, this is html
          f[:manifest],u[:manifest]='manifest,', "'#{@md.file.output_path.manifest.url}/#{@md.file.base_filename.manifest}',"
        end
        if FileTest.file?("#{@md.file.output_path.src.dir}/#{@md.file.base_filename.src}")==true
          f[:markup],u[:markup]='markup,', "'#{@md.file.output_path.src.url}/#{@md.file.base_filename.src}',"
        end
        if FileTest.file?("#{@md.file.output_path.sisupod.dir}/#{@md.file.base_filename.sisupod}")==true
          f[:sisupod],u[:sisupod]='sisupod,', "'#{@md.file.output_path.sisupod.url}/#{@md.file.base_filename.sisupod}',"
        end
        t=SiSU_DbTuple::LoadUrls.new(@conn,f,u,@@id_t,@opt,@file_maint)
        tuple=t.tuple
      rescue
        SiSU_Errors::Rescued.new($!,$@,@opt.selections.str,@opt.fns).location do
          __LINE__.to_s + ':' + __FILE__
        end
      ensure
      end
      tuple
    end
  end
end
__END__
#+END_SRC

** db_remove.rb

#+BEGIN_SRC ruby  :tangle "../lib/sisu/db_remove.rb"
<<sisu_document_header>>
module SiSU_DbRemove
  class Remove
    include SiSU_DbAction
    def initialize(opt,conn,file,sql_type)
      @opt,@conn,@file,@sql_type=opt,conn,file,sql_type
      @md=SiSU_Param::Parameters.new(@opt).get
      @fnb=@md.fnb
      @db=SiSU_Env::InfoDb.new
    end
    def remove
      driver_sqlite3=if @sql_type==:sqlite
        (@conn.inspect.match(/^(.{10})/)[1]==@db.sqlite.conn_sqlite3.inspect.match(/^(.{10})/)[1]) \
        ? true
        : false
      end
      del_id=if driver_sqlite3
        begin
          remove_selected=%{
            SELECT tid
            FROM metadata_and_text
            WHERE src_filename = '#{@md.fns}'
            AND metadata_and_text.language_document_char = '#{@opt.lng}'
          ;} # note, for .ssm: @md.fns (is set during runtime & is) != @opt.fns @md.opt.fns
          @conn.get_first_value(remove_selected).to_i
        rescue SQLite3::Exception => e
          #not tested
          puts "Exception occurred"
          SiSU_Utils::CodeMarker.new(__LINE__,__FILE__,:fuchsia).mark(e.inspect)
          SiSU_Utils::CodeMarker.new(__LINE__,__FILE__,:yellow).mark(
            "\n" \
            + 'Attempting to initialize db' + "\n" \
            + 'Creating db tables'
          )
          sdb={
            create: SiSU_DbDBI::Create.new(@opt,@conn,@file_maint,@sql_type),
            index: SiSU_DbDBI::Index.new(@opt,@conn,@file_maint,@sql_type),
          }
          db_action(sdb).create
        end
      else
        begin
          remove_selected=%{
            SELECT metadata_and_text.tid
            FROM metadata_and_text
            WHERE metadata_and_text.src_filename = '#{@md.fns}'
            AND metadata_and_text.language_document_char = '#{@opt.lng}'
          ;} # note, for .ssm: @md.fns (is set during runtime & is) != @opt.fns @md.opt.fns
          x=@conn.exec(remove_selected)
          x.field_values("tid")[0]
        rescue PG::Error => e
          err=[
            e.result.error_field( PG::Result::PG_DIAG_SEVERITY ),
            e.result.error_field( PG::Result::PG_DIAG_SQLSTATE ),
            e.result.error_field( PG::Result::PG_DIAG_MESSAGE_PRIMARY ),
            e.result.error_field( PG::Result::PG_DIAG_MESSAGE_DETAIL ),
            e.result.error_field( PG::Result::PG_DIAG_MESSAGE_HINT ),
            e.result.error_field( PG::Result::PG_DIAG_STATEMENT_POSITION ),
            e.result.error_field( PG::Result::PG_DIAG_INTERNAL_POSITION ),
            e.result.error_field( PG::Result::PG_DIAG_INTERNAL_QUERY ),
            e.result.error_field( PG::Result::PG_DIAG_CONTEXT ),
            e.result.error_field( PG::Result::PG_DIAG_SOURCE_FILE ),
            e.result.error_field( PG::Result::PG_DIAG_SOURCE_LINE ),
            e.result.error_field( PG::Result::PG_DIAG_SOURCE_FUNCTION ),
          ]
          SiSU_Utils::CodeMarker.new(__LINE__,__FILE__,:fuchsia).mark("\n" + err.inspect)
          if err[2] =~/relation "\S+?" does not exist/ \
          or err.inspect =~/relation "\S+?" does not exist/
            SiSU_Utils::CodeMarker.new(__LINE__,__FILE__,:yellow).mark(
              "\n" \
              + err[2] + "\n" \
              + 'Attempting to initialize db' + "\n" \
              + 'Creating db tables'
            )
            sdb={
              create: SiSU_DbDBI::Create.new(@opt,@conn,@file_maint,@sql_type),
              index: SiSU_DbDBI::Index.new(@opt,@conn,@file_maint,@sql_type),
            }
            db_action(sdb).create
          end
        end
      end
      if del_id
        sql_entry=[
          "DELETE FROM endnotes WHERE metadata_tid = '#{del_id}';",
          "DELETE FROM endnotes_asterisk WHERE metadata_tid = '#{del_id}';",
          "DELETE FROM endnotes_plus WHERE metadata_tid = '#{del_id}';",
          "DELETE FROM doc_objects WHERE metadata_tid = '#{del_id}';",
          "DELETE FROM urls WHERE metadata_tid = '#{del_id}';",
          "DELETE FROM metadata_and_text WHERE metadata_and_text.tid = '#{del_id}';",
        ]
        if driver_sqlite3
          @conn.transaction
          sql_entry.each do |s|
            begin
              @conn.execute(s)
            rescue
              next
            end
          end
          @conn.commit if driver_sqlite3
        else
          sql_entry.each do |s|
            begin
              @conn.exec_params(s)
            rescue
              next
            end
          end
        end
        if @opt.act[:maintenance][:set]==:on
          @file.puts sql_entry
        end
      else
        if (@opt.act[:verbose][:set]==:on \
        || @opt.act[:verbose_plus][:set]==:on \
        || @opt.act[:maintenance][:set]==:on)
          SiSU_Screen::Ansi.new(
            @opt.selections.str,
            "no such file in database #{@db.psql.db}::#{@opt.fns}"
          ).puts_grey
        end
      end
    end
  end
end
__END__
#+END_SRC

* db_load_tuple.rb

#+BEGIN_SRC ruby  :tangle "../lib/sisu/db_load_tuple.rb"
<<sisu_document_header>>
module SiSU_DbTuple
  require_relative 'db_columns'                         # db_columns.rb
  class LoadDocuments
    require_relative 'dp'                               # dp.rb
      include SiSU_Param
    def initialize(conn,col,opt,file_maint)
      @conn,@col,@opt,@file_maint=conn,col,opt,file_maint
      @col[:lev]=@col[:lev].to_i
      unless @col[:lev].inspect=~/^[0-7]/ \
      or @col[:lev]==0..7
        @col[:lev]=9
      end
      @col[:ocn]=0 unless @col[:ocn].inspect=~/\d+/
      @cX=SiSU_Screen::Ansi.new(@opt.act[:color_state][:set]).cX
    end
    def tuple                                                                    #% import line
      sql_entry=if @col[:en_a]
        "INSERT INTO doc_objects (lid, metadata_tid, lev, lev_an, clean, body, book_idx, ocn, ocnd, ocns, seg, lev0, lev1, lev2, lev3, lev4, lev5, lev6, lev7, en_a, en_z, t_of, t_is, node, parent, digest_clean, digest_all) " +
        "VALUES (#{@col[:lid]}, #{@col[:tid]}, #{@col[:lev]}, '#{@col[:lev_an]}', '#{@col[:plaintext]}', '#{@col[:body]}', '#{@col[:book_idx]}', '#{@col[:ocn]}', '#{@col[:ocnd]}', '#{@col[:ocns]}', '#{@col[:seg]}', '#{@col[:lv0]}', '#{@col[:lv1]}', '#{@col[:lv2]}', '#{@col[:lv3]}', '#{@col[:lv4]}', '#{@col[:lv5]}', '#{@col[:lv6]}', '#{@col[:lv7]}', '#{@col[:en_a]}', '#{@col[:en_z]}', '#{@col[:t_of]}', '#{@col[:t_is]}', '#{@col[:node]}', '#{@col[:parent]}', '#{@col[:digest_clean]}', '#{@col[:digest_all]}');"
      else
        "INSERT INTO doc_objects (lid, metadata_tid, lev, lev_an, clean, body, book_idx, ocn, ocnd, ocns, seg, lev0, lev1, lev2, lev3, lev4, lev5, lev6, lev7, t_of, t_is, node, parent, digest_clean, digest_all) " +
        "VALUES (#{@col[:lid]}, #{@col[:tid]}, #{@col[:lev]}, '#{@col[:lev_an]}', '#{@col[:plaintext]}', '#{@col[:body]}', '#{@col[:book_idx]}', '#{@col[:ocn]}', '#{@col[:ocnd]}', '#{@col[:ocns]}', '#{@col[:seg]}', '#{@col[:lv0]}', '#{@col[:lv1]}', '#{@col[:lv2]}', '#{@col[:lv3]}', '#{@col[:lv4]}', '#{@col[:lv5]}', '#{@col[:lv6]}', '#{@col[:lv7]}', '#{@col[:t_of]}', '#{@col[:t_is]}', '#{@col[:node]}', '#{@col[:parent]}', '#{@col[:digest_clean]}', '#{@col[:digest_all]}');"
      end
      if @opt.act[:maintenance][:set]==:on
          puts @file_maint.inspect
          puts sql_entry
          @file_maint.puts sql_entry
      elsif @opt.act[:verbose_plus][:set]==:on
          puts sql_entry
      end
      if @opt.act[:verbose][:set]==:on
        if @col[:lev].inspect =~/[0-35-7]/
          lev=case @col[:lev].inspect
          when /0/ then ':A'
          when /1/ then ':B'
          when /2/ then ':C'
          when /3/ then ':D'
          when /5/ then ' 2'
          when /6/ then ' 3'
          when /7/ then ' 4'
          end
          puts %{#{lev}>\t#{@col[:lv0]}\t#{@col[:lv1]}\t#{@col[:lv2]}\t#{@col[:lv3]}\t#{@col[:lv4]}\t#{@col[:lv5]}\t#{@col[:lv6]}\t#{@col[:lv7]}\t#{@col[:ocn]}\t#{@col[:node]}\t#{@col[:ocns]}}
        elsif @col[:lev].inspect =~/[4]/
          puts %{ #{@cX.green}1>#{@cX.off}\t#{@col[:lv0]}\t#{@col[:lv1]}\t#{@col[:lv2]}\t#{@col[:lv3]}\t#{@col[:lv4]}\t#{@col[:lv5]}\t#{@col[:lv6]}\t#{@col[:lv7]}\t#{@col[:ocn]}\t#{@col[:node]}\t#{@col[:ocns]}\t#{@col[:seg]}}
        end
      end
      sql_entry
    end
  end
  class LoadMetadata #< SiSU_DbColumns::Columns
    def initialize(conn,id,md,file_maint)
      @conn,@id,@md,@file_maint=conn,id,md,file_maint
      @tp=SiSU_DbColumns::Columns.new(md)
    end
    def tuple
      sql_entry="INSERT INTO metadata_and_text (
#{@tp.column.title.tuple[0]}
#{@tp.column.title_main.tuple[0]}
#{@tp.column.title_sub.tuple[0]}
#{@tp.column.title_short.tuple[0]}
#{@tp.column.title_edition.tuple[0]}
#{@tp.column.title_note.tuple[0]}
#{@tp.column.title_language.tuple[0]}
#{@tp.column.title_language_char.tuple[0]}
#{@tp.column.creator_author.tuple[0]}
#{@tp.column.creator_author_honorific.tuple[0]}
#{@tp.column.creator_author_nationality.tuple[0]}
#{@tp.column.creator_editor.tuple[0]}
#{@tp.column.creator_contributor.tuple[0]}
#{@tp.column.creator_illustrator.tuple[0]}
#{@tp.column.creator_photographer.tuple[0]}
#{@tp.column.creator_translator.tuple[0]}
#{@tp.column.creator_prepared_by.tuple[0]}
#{@tp.column.creator_digitized_by.tuple[0]}
#{@tp.column.creator_audio.tuple[0]}
#{@tp.column.creator_video.tuple[0]}
#{@tp.column.language_document.tuple[0]}
#{@tp.column.language_document_char.tuple[0]}
#{@tp.column.language_original.tuple[0]}
#{@tp.column.language_original_char.tuple[0]}
#{@tp.column.date_added_to_site.tuple[0]}
#{@tp.column.date_available.tuple[0]}
#{@tp.column.date_created.tuple[0]}
#{@tp.column.date_issued.tuple[0]}
#{@tp.column.date_modified.tuple[0]}
#{@tp.column.date_published.tuple[0]}
#{@tp.column.date_valid.tuple[0]}
#{@tp.column.date_translated.tuple[0]}
#{@tp.column.date_original_publication.tuple[0]}
#{@tp.column.date_generated.tuple[0]}
#{@tp.column.publisher.tuple[0]}
#{@tp.column.original_publisher.tuple[0]}
#{@tp.column.original_language.tuple[0]}
#{@tp.column.original_language_char.tuple[0]}
#{@tp.column.original_source.tuple[0]}
#{@tp.column.original_institution.tuple[0]}
#{@tp.column.original_nationality.tuple[0]}
#{@tp.column.rights_all.tuple[0]}
#{@tp.column.rights_copyright_text.tuple[0]}
#{@tp.column.rights_copyright_translation.tuple[0]}
#{@tp.column.rights_copyright_illustrations.tuple[0]}
#{@tp.column.rights_copyright_photographs.tuple[0]}
#{@tp.column.rights_copyright_preparation.tuple[0]}
#{@tp.column.rights_copyright_digitization.tuple[0]}
#{@tp.column.rights_copyright_audio.tuple[0]}
#{@tp.column.rights_copyright_video.tuple[0]}
#{@tp.column.rights_license.tuple[0]}
#{@tp.column.classify_topic_register.tuple[0]}
#{@tp.column.classify_subject.tuple[0]}
#{@tp.column.classify_loc.tuple[0]}
#{@tp.column.classify_dewey.tuple[0]}
#{@tp.column.classify_keywords.tuple[0]}
#{@tp.column.identifier_oclc.tuple[0]}
#{@tp.column.identifier_isbn.tuple[0]}
#{@tp.column.notes_abstract.tuple[0]}
#{@tp.column.notes_description.tuple[0]}
#{@tp.column.notes_comment.tuple[0]}
#{@tp.column.notes_history.tuple[0]}
#{@tp.column.notes_format.tuple[0]}
#{@tp.column.notes_relation.tuple[0]}
#{@tp.column.notes_coverage.tuple[0]}
#{@tp.column.notes_type.tuple[0]}
#{@tp.column.notes_prefix.tuple[0]}
#{@tp.column.notes_prefix_a.tuple[0]}
#{@tp.column.notes_prefix_b.tuple[0]}
#{@tp.column.notes_suffix.tuple[0]}
#{@tp.column.src_filename.tuple[0]}
#{@tp.column.src_fingerprint.tuple[0]}
#{@tp.column.src_filesize.tuple[0]}
#{@tp.column.src_word_count.tuple[0]}
#{@tp.column.src_txt.tuple[0]}
#{@tp.column.fulltext.tuple[0]}
tid)
" +
       "VALUES (
#{@tp.column.title.tuple[1]}
#{@tp.column.title_main.tuple[1]}
#{@tp.column.title_sub.tuple[1]}
#{@tp.column.title_short.tuple[1]}
#{@tp.column.title_edition.tuple[1]}
#{@tp.column.title_note.tuple[1]}
#{@tp.column.title_language.tuple[1]}
#{@tp.column.title_language_char.tuple[1]}
#{@tp.column.creator_author.tuple[1]}
#{@tp.column.creator_author_honorific.tuple[1]}
#{@tp.column.creator_author_nationality.tuple[1]}
#{@tp.column.creator_editor.tuple[1]}
#{@tp.column.creator_contributor.tuple[1]}
#{@tp.column.creator_illustrator.tuple[1]}
#{@tp.column.creator_photographer.tuple[1]}
#{@tp.column.creator_translator.tuple[1]}
#{@tp.column.creator_prepared_by.tuple[1]}
#{@tp.column.creator_digitized_by.tuple[1]}
#{@tp.column.creator_audio.tuple[1]}
#{@tp.column.creator_video.tuple[1]}
#{@tp.column.language_document.tuple[1]}
#{@tp.column.language_document_char.tuple[1]}
#{@tp.column.language_original.tuple[1]}
#{@tp.column.language_original_char.tuple[1]}
#{@tp.column.date_added_to_site.tuple[1]}
#{@tp.column.date_available.tuple[1]}
#{@tp.column.date_created.tuple[1]}
#{@tp.column.date_issued.tuple[1]}
#{@tp.column.date_modified.tuple[1]}
#{@tp.column.date_published.tuple[1]}
#{@tp.column.date_valid.tuple[1]}
#{@tp.column.date_translated.tuple[1]}
#{@tp.column.date_original_publication.tuple[1]}
#{@tp.column.date_generated.tuple[1]}
#{@tp.column.publisher.tuple[1]}
#{@tp.column.original_publisher.tuple[1]}
#{@tp.column.original_language.tuple[1]}
#{@tp.column.original_language_char.tuple[1]}
#{@tp.column.original_source.tuple[1]}
#{@tp.column.original_institution.tuple[1]}
#{@tp.column.original_nationality.tuple[1]}
#{@tp.column.rights_all.tuple[1]}
#{@tp.column.rights_copyright_text.tuple[1]}
#{@tp.column.rights_copyright_translation.tuple[1]}
#{@tp.column.rights_copyright_illustrations.tuple[1]}
#{@tp.column.rights_copyright_photographs.tuple[1]}
#{@tp.column.rights_copyright_preparation.tuple[1]}
#{@tp.column.rights_copyright_digitization.tuple[1]}
#{@tp.column.rights_copyright_audio.tuple[1]}
#{@tp.column.rights_copyright_video.tuple[1]}
#{@tp.column.rights_license.tuple[1]}
#{@tp.column.classify_topic_register.tuple[1]}
#{@tp.column.classify_subject.tuple[1]}
#{@tp.column.classify_loc.tuple[1]}
#{@tp.column.classify_dewey.tuple[1]}
#{@tp.column.classify_keywords.tuple[1]}
#{@tp.column.identifier_oclc.tuple[1]}
#{@tp.column.identifier_isbn.tuple[1]}
#{@tp.column.notes_abstract.tuple[1]}
#{@tp.column.notes_comment.tuple[1]}
#{@tp.column.notes_description.tuple[1]}
#{@tp.column.notes_history.tuple[1]}
#{@tp.column.notes_format.tuple[1]}
#{@tp.column.notes_relation.tuple[1]}
#{@tp.column.notes_coverage.tuple[1]}
#{@tp.column.notes_type.tuple[1]}
#{@tp.column.notes_prefix.tuple[1]}
#{@tp.column.notes_prefix_a.tuple[1]}
#{@tp.column.notes_prefix_b.tuple[1]}
#{@tp.column.notes_suffix.tuple[1]}
#{@tp.column.src_filename.tuple[1]}
#{@tp.column.src_fingerprint.tuple[1]}
#{@tp.column.src_filesize.tuple[1]}
#{@tp.column.src_word_count.tuple[1]}
#{@tp.column.src_txt.tuple[1]}
#{@tp.column.fulltext.tuple[1]}
#{@id}
);"
      if @md.opt.act[:maintenance][:set]==:on
        puts "maintenance mode on: creating sql transaction file (for last transaction set (document) only):\n\t#{@file_maint.inspect}"
        @file_maint.puts sql_entry
      end
      sql_entry
    end
  end
  class LoadUrls
    def initialize(conn,f,u,id,opt,file_maint)
      @conn,@f,@u,@id,@opt,@file_maint=conn,f,u,id,opt,file_maint
    end
    def tuple
      sql_entry="INSERT INTO urls (#{@f[:txt]} #{@f[:html_toc]} #{@f[:html_doc]} #{@f[:xhtml]} #{@f[:xml_sax]} #{@f[:xml_dom]} #{@f[:odf]} #{@f[:pdf_p]} #{@f[:pdf_l]} #{@f[:concordance]} #{@f[:latex_p]} #{@f[:latex_l]} #{@f[:manifest]} #{@f[:digest]} #{@f[:markup]} #{@f[:sisupod]} metadata_tid) " +
      "VALUES (#{@u[:txt]} #{@u[:html_toc]} #{@u[:html_doc]} #{@u[:xhtml]} #{@u[:xml_sax]} #{@u[:xml_dom]} #{@u[:odf]} #{@u[:pdf_p]} #{@u[:pdf_l]} #{@u[:concordance]} #{@u[:latex_p]} #{@u[:latex_l]} #{@u[:manifest]} #{@u[:digest]} #{@u[:markup]} #{@u[:sisupod]} #{@id});"
      if @opt.act[:maintenance][:set]==:on
        @file_maint.puts sql_entry
      end
      sql_entry
    end
  end
  class LoadEndnotes
    def initialize(conn,en,opt,file_maint)
      @conn,@en,@opt,@file_maint=conn,en,opt,file_maint
    end
    def tuple
      sql_entry="INSERT INTO #{@en[:type]} (nid, document_lid, nr, clean, body, ocn, ocnd, ocns, metadata_tid, digest_clean) " +
      "VALUES ('#{@en[:id]}', '#{@en[:lid]}', '#{@en[:nr]}', '#{@en[:txt]}', '#{@en[:body]}', '#{@en[:ocn]}', '#{@en[:ocnd]}', '#{@en[:ocns]}', '#{@en[:id_t]}', '#{@en[:hash]}');"
      if @opt.act[:maintenance][:set]==:on
        @file_maint.puts sql_entry
      end
      sql_entry
    end
  end
end
__END__
#+END_SRC

* db_select.rb

#+BEGIN_SRC ruby  :tangle "../lib/sisu/db_select.rb"
<<sisu_document_header>>
module SiSU_DbAction
  def db_action(sdb)
    @sdb=sdb
    def createdb
      @sdb[:create].output_dir?
      begin
        @sdb[:create].create_db
      rescue
        @sdb[:create].output_dir?
      end
    end
    def drop
      @sdb[:drop].drop.tables
    end
    def create
      @sdb[:create].output_dir?
      begin
        @sdb[:create].create_table.metadata_and_text
        @sdb[:create].create_table.doc_objects
        @sdb[:create].create_table.endnotes
        @sdb[:create].create_table.endnotes_asterisk
        @sdb[:create].create_table.endnotes_plus
        @sdb[:create].create_table.urls
        @sdb[:index].create_indexes
      rescue
        SiSU_Errors::Rescued.new($!,$@,'--sqlite').location
        @sdb[:create].output_dir? do
          __LINE__.to_s + ':' + __FILE__
        end
      end
    end
    def import
      db_exist?
      @sdb[:import].marshal_load
      tell=case @sql_type
      when :sqlite
        SiSU_Screen::Ansi.new(
          @opt.act[:color_state][:set],
          "sqlite3 #{@db.sqlite.db} database?"
        )
      when :pg
        SiSU_Screen::Ansi.new(
          @opt.act[:color_state][:set],
          "pgaccess or psql #{@db.psql.db} database?"
        )
      else '???'
      end
      tell.puts_grey if @opt.act[:verbose][:set]==:on
    end
    def remove
      db_exist?
      @sdb[:remove_doc].remove
    end
    def update
      remove
      import
    end
    self
  end
end
module SiSU_DbSelect
  class Case
    include SiSU_DbAction
    def initialize(opt,conn='',sql_type=:pg)
      @opt,@conn,@sql_type=opt,conn,sql_type
      @db=SiSU_Env::InfoDb.new
      @file_maint=sql_maintenance_file
      @sdb={
        create: SiSU_DbDBI::Create.new(@opt,@conn,@file_maint,@sql_type),
        index: SiSU_DbDBI::Index.new(@opt,@conn,@file_maint,@sql_type),
        drop: SiSU_DbDBI::Drop.new(@opt,@conn,@db,@sql_type),
      }
      if (@opt.act[:psql_import][:set]==:on \
      || @opt.act[:psql_update][:set]==:on) \
      or (@opt.act[:sqlite_import][:set]==:on \
      || @opt.act[:sqlite_update][:set]==:on)
        @sdb[:import]=SiSU_DbDBI::Import.new(@opt,@conn,@file_maint,@sql_type)
        @sdb[:remove_doc]=SiSU_DbDBI::Remove.new(@opt,@conn,@file_maint,@sql_type)
      elsif (@opt.act[:psql_remove][:set]==:on \
      or @opt.act[:sqlite_remove][:set]==:on)
        @sdb[:remove_doc]=SiSU_DbDBI::Remove.new(@opt,@conn,@file_maint,@sql_type)
      end
    end
    def db_exist?
      if @sql_type==:sqlite \
      and (not (FileTest.file?(@db.sqlite.db)) \
      or FileTest.zero?(@db.sqlite.db))
        puts %{no connection with sqlite database established, you may need to run:\n} \
        + %{    sisu --sqlite --createall\n} \
        + %{  before attempting to populate the database}
        SiSU_Utils::CodeMarker.new(__LINE__,__FILE__,:yellow).mark(
          "\n" \
          + 'Attempting to initialize db' + "\n" \
          + 'Creating db tables'
        )
        db_action(@sdb).create
      end
      if @conn.is_a?(NilClass)
        if @sql_type==:sqlite
          puts %{no connection with sqlite database established, you may need to run:\n} \
          + %{    sisu --sqlite --createall\n} \
          + %{  before attempting to populate the database}
          SiSU_Utils::CodeMarker.new(__LINE__,__FILE__,:yellow).mark(
            "\n" \
            + 'Attempting to initialize db' + "\n" \
            + 'Creating db tables'
          )
          db_action(@sdb).create
          @db.sqlite.db
        else
          puts %{no connection with pg database established, you may need to run:\n} \
          + %{    createdb "#{@db.psql.db}"\n} \
          + %{  after that don't forget to run:\n} \
          + %{    sisu --pg --createall\n} \
          + %{  before attempting to populate the database}
          @db.psql.db
        end
        exit
      end
    end
    def sql_maintenance_file
      file=if @opt.act[:maintenance][:set]==:on
        if @opt.fns and not @opt.fns.empty?
          @env=SiSU_Env::InfoEnv.new(@opt.fns) if @opt.fns
          if @sql_type ==:sqlite
            puts "\n#{@env.processing_path.sqlite}/#{@opt.fns}.sql"
          end
          @db=SiSU_Env::InfoDb.new
          @job="sqlite3 #{@db.sqlite.db} < #{@env.processing_path.sqlite}/#{@opt.fns}.sql"
          if @sql_type ==:sqlite
            File.new("#{@env.processing_path.sqlite}/#{@opt.fns}.sql",'w+')
          else
            File.new("#{@env.processing_path.postgresql}/#{@opt.fns}.sql",'w+')
          end
        elsif @opt.fns \
        and (@opt.act[:sqlite_create][:set] ==:on \
        || @opt.act[:psql_create][:set] ==:on)
          nil #sort variations later
        else nil
        end
      else nil
      end
      file
    end
    def cases
      if @opt.act[:psql_drop][:set] ==:on \
      or @opt.act[:sqlite_drop][:set] ==:on
        db_action(@sdb).drop
      end
      if @opt.act[:psql_createdb][:set] ==:on \
      or @opt.act[:sqlite_createdb][:set] ==:on
        db_action(@sdb).createdb
      end
      if @opt.act[:psql_create][:set] ==:on \
      or @opt.act[:sqlite_create][:set] ==:on
        db_action(@sdb).create
      end
      if @opt.act[:psql_update][:set] ==:on \
      or @opt.act[:sqlite_update][:set] ==:on
        db_action(@sdb).update
      else
        if @opt.act[:psql_remove][:set] ==:on \
        or @opt.act[:sqlite_remove][:set] ==:on
          db_action(@sdb).remove
        end
        if @opt.act[:psql_import][:set] ==:on \
        or @opt.act[:sqlite_import][:set] ==:on
          db_action(@sdb).import
        end
      end
    end
  end
end
__END__
#+END_SRC

* structure
** db_columns.rb

#+BEGIN_SRC ruby  :tangle "../lib/sisu/db_columns.rb"
<<sisu_document_header>>
module SiSU_DbColumns
  require_relative 'se'                                # se.rb
  require_relative 'db_sqltxt'                         # db_sqltxt.rb
  class Columns < SiSU_DbText::Prepare
    def initialize(md=nil)
      @md=md
      @db=SiSU_Env::InfoDb.new #watch
      @lang ||=SiSU_i18n::Languages.new
      if defined? md.opt.act \
      and ((md.opt.act[:psql_import][:set]==:on \
      || md.opt.act[:psql_update][:set]==:on) \
      or (md.opt.act[:sqlite_import][:set]==:on \
      || md.opt.act[:sqlite_update][:set]==:on)) \
      and FileTest.exist?(md.fns)
        txt_arr=IO.readlines(md.fns,'')
        src=txt_arr.join("\n")
        if @db.share_source?
          @sisutxt=special_character_escape(src)
        else @sisutxt=''
        end
        @fulltext=clean_searchable_text_from_document_objects(txt_arr)
       else @sisutxt,@fulltext='',''
      end
    end
#% structures
    #def column_define
    #  def varchar(name,size)
    #    "#{name}                VARCHAR(#{size}) NULL,"
    #  end
    #end
=begin
#% title
@title:
 :subtitle:
 :short:
 :edition:
 :language:
 :note:
=end
    def column
      def title                          # DublinCore 1 - title
        def name
          'title'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_title]}) NOT NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
            IS 'metadata full document title [DC1]';}
        end
        def tuple
          if defined? @md.title.full \
          and @md.title.full=~/\S+/
            txt=@md.title.full
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def title_main
        def name
          'title_main'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_title_part]}) NOT NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
            IS 'metadata main document title';}
        end
        def tuple
          if defined? @md.title.main \
          and @md.title.main=~/\S+/
            txt=@md.title.main
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def title_sub
        def name
          'title_sub'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_title_part]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
            IS 'metadata document subtitle';}
        end
        def tuple
          if defined? @md.title.sub \
          and @md.title.sub=~/\S+/
            txt=@md.title.sub
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def title_short
        def name
          'title_short'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_title_part]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
            IS 'metadata document short title if any';}
        end
        def tuple
          if defined? @md.title.short \
          and @md.title.short=~/\S+/
            txt=@md.title.short
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def title_edition
        def name
          'title_edition'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_title_edition]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
            IS 'metadata document edition (version)';}
        end
        def tuple
          if defined? @md.title.edition \
          and @md.title.edition=~/\S+/
            txt=@md.title.edition
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def title_note
        def name
          'title_note'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_info_note]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
            IS 'metadata document notes associated with title';}
        end
        def tuple
          if defined? @md.title.note \
          and @md.title.note=~/\S+/
            txt=@md.title.note
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def title_language
        def name
          'title_language'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_language]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
            IS 'metadata document language [DC12]';}
        end
        def tuple
          if @lang.list[@md.opt.lng][:n]
            txt=@lang.list[@md.opt.lng][:n]
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def title_language_char            # consider
        def name
          'title_language_char'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_language_char]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
            IS 'metadata document language iso code';}
        end
        def tuple
          if defined? @md.opt.lng \
          and @md.opt.lng=~/\S+/
            txt=@md.opt.lng
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
=begin
#% creator
@creator:
 :author:
 :editor:
 :contributor:
 :illustrator:
 :photographer:
 :translator:
 :prepared_by:
 :digitized_by:
 :audio:
 :video:
=end
      def creator_author                 # DublinCore 2 - creator/author (author)
        def name
          'creator_author'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_name]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document author (creator) [DC2]';}
        end
        def tuple
          if defined? @md.creator.author_detail \
          and @md.creator.author_detail.is_a?(Array) \
          and @md.creator.author_detail.length > 0
            txt=''
            @md.creator.author_detail.each do |h|
              txt=txt + %{#{h[:the]}, #{h[:others]}; }
            end
            txt=txt.gsub(/[;, ]+\s*$/,'')
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def creator_author_honorific       # consider
        def name
          'creator_author_hon'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_creator_misc_short]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document author honorific (title e.g, Ms. Dr. Prof.)';}
        end
        def tuple
          if defined? @md.creator.author_hon \
          and @md.creator.author_hon=~/\S+/
            txt=@md.creator.author_hon
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def creator_author_nationality     # consider
        def name
          'creator_author_nationality'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_creator_misc_short]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata nationality of document author (creator)';}
        end
        def tuple
          if defined? @md.creator.author_nationality_detail \
          and @md.creator.author_nationality=~/\S+/
            txt=@md.creator.author_nationality_detail
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def creator_editor
        def name
          'creator_editor'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_name]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document editor name(s)';}
        end
        def tuple
          if defined? @md.creator.editor_detail \
          and @md.creator.editor_detail.is_a?(Array) \
          and @md.creator.editor_detail.length > 0
            txt=''
            @md.creator.editor_detail.each do |h|
              txt=txt + %{#{h[:the]}, #{h[:others]}; }
            end
            txt=txt.gsub(/[;, ]+\s*$/,'')
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def creator_contributor            # DublinCore 6 - contributor
        def name
          'creator_contributor'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_name]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document contributor name(s) [DC6]';}
        end
        def tuple
          if defined? @md.creator.contributor_detail \
          and @md.creator.contributor_detail.is_a?(Array) \
          and @md.creator.contributor_detail.length > 0
            txt=''
            @md.creator.contributor_detail.each do |h|
              txt=txt + %{#{h[:the]}, #{h[:others]}; }
            end
            txt=txt.gsub(/[;, ]+\s*$/,'')
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def creator_illustrator
        def name
          'creator_illustrator'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_name]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document illustrator name(s)';}
        end
        def tuple
          if defined? @md.creator.illustrator_detail \
          and @md.creator.illustrator_detail.is_a?(Array) \
          and @md.creator.illustrator_detail.length > 0
            txt=''
            @md.creator.illustrator_detail.each do |h|
              txt=txt + %{#{h[:the]}, #{h[:others]}; }
            end
            txt=txt.gsub(/[;, ]+\s*$/,'')
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def creator_photographer
        def name
          'creator_photographer'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_name]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document photographer name(s)';}
        end
        def tuple
          if defined? @md.creator.photographer_detail \
          and @md.creator.photographer_detail.is_a?(Array) \
          and @md.creator.photographer_detail.length > 0
            txt=''
            @md.creator.photographer_detail.each do |h|
              txt=txt + %{#{h[:the]}, #{h[:others]}; }
            end
            txt=txt.gsub(/[;, ]+\s*$/,'')
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def creator_translator
        def name
          'creator_translator'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_name]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document translator name(s)';}
        end
        def tuple
          if defined? @md.creator.translator_detail \
          and @md.creator.translator_detail.is_a?(Array) \
          and @md.creator.translator_detail.length > 0
            txt=''
            @md.creator.translator_detail.each do |h|
              txt=txt + %{#{h[:the]}, #{h[:others]}; }
            end
            txt=txt.gsub(/[;, ]+\s*$/,'')
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def creator_prepared_by
        def name
          'creator_prepared_by'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_name]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document prepared by name(s)';}
        end
        def tuple
          if defined? @md.creator.prepared_by_detail \
          and @md.creator.prepared_by_detail.is_a?(Array) \
          and @md.creator.prepared_by_detail.length > 0
            txt=''
            @md.creator.prepared_by_detail.each do |h|
              txt=txt + %{#{h[:the]}, #{h[:others]}; }
            end
            txt=txt.gsub(/[;, ]+\s*$/,'')
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def creator_digitized_by
        def name
          'creator_digitized_by'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_name]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document digitized by name(s)';}
        end
        def tuple
          if defined? @md.creator.digitized_by_detail \
          and @md.creator.digitized_by_detail.is_a?(Array) \
          and @md.creator.digitized_by_detail.length > 0
            txt=''
            @md.creator.digitized_by_detail.each do |h|
              txt=txt + %{#{h[:the]}, #{h[:others]}; }
            end
            txt=txt.gsub(/[;, ]+\s*$/,'')
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def creator_audio
        def name
          'creator_audio'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_name]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document audio by name(s)';}
        end
        def tuple
          if defined? @md.creator.audio_detail \
          and @md.creator.audio_detail.is_a?(Array) \
          and @md.creator.audio_detail.length > 0
            txt=''
            @md.creator.audio_detail.each do |h|
              txt=txt + %{#{h[:the]}, #{h[:others]}; }
            end
            txt=txt.gsub(/[;, ]+\s*$/,'')
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def creator_video
        def name
          'creator_video'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_name]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document video by name(s)';}
        end
        def tuple
          if defined? @md.creator.video_detail \
          and @md.creator.video_detail.is_a?(Array) \
          and @md.creator.video_detail.length > 0
            txt=''
            @md.creator.video_detail.each do |h|
              txt=txt + %{#{h[:the]}, #{h[:others]}; }
            end
            txt=txt.gsub(/[;, ]+\s*$/,'')
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
=begin
#% language
#taken from other fields
@title:
 :language:
@original:
 :language:
#not available -->
#@language:
# :document:
# :original:
=end
      def language_document
        def name
          'language_document'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_language]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document language';}
        end
        def tuple
          if @lang.list[@md.opt.lng][:n]
            txt=@lang.list[@md.opt.lng][:n]
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def language_document_char
        def name
          'language_document_char'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_language_char]}) NOT NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document language';}
        end
        def tuple
          #modify check, is now required, SiSUv3d_
          if defined? @md.opt.lng \
          and @md.opt.lng=~/\S+/
            txt=@md.opt.lng
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def language_original
        def name
          'language_original'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_language]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata original document/text language';}
        end
        def tuple
          if defined? @md.language.original \
          and @md.language.original=~/\S+/
            txt=@md.language.original
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def language_original_char
        def name
          'language_original_char'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_language_char]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document language';}
        end
        def tuple
          if defined? @md.language.original_char \
          and @md.language.original_char=~/\S+/
            txt=@md.language.original_char
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
=begin
#% date
@date:
 :added_to_site:
 :available:
 :created:
 :issued:
 :modified:
 :published:
 :valid:
 :translated:
 :original_publication:
=end
      def date_added_to_site
        def name
          'date_added_to_site'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_date_text]}) NULL,"
          #"#{name}                DATE,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata date added to site';}
        end
        def tuple
          if defined? @md.date.added_to_site \
          and @md.date.added_to_site=~/\S+/
            txt=@md.date.added_to_site
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def date_available
        def name
          'date_available'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_date_text]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata date added to site [DC]';}
        end
        def tuple
          if defined? @md.date.available \
          and @md.date.available=~/\S+/
            txt=@md.date.available
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def date_created
        def name
          'date_created'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_date_text]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata date created [DC]';}
        end
        def tuple
          if defined? @md.date.created \
          and @md.date.created=~/\S+/
            txt=@md.date.created
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def date_issued
        def name
          'date_issued'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_date_text]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata date issued [DC]';}
        end
        def tuple
          if defined? @md.date.issued \
          and @md.date.issued=~/\S+/
            txt=@md.date.issued
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def date_modified
        def name
          'date_modified'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_date_text]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata date modified [DC]';}
        end
        def tuple
          if defined? @md.date.modified \
          and @md.date.modified=~/\S+/
            txt=@md.date.modified
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def date_published
        def name
          'date_published'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_date_text]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata date published [DC7]';}
        end
        def tuple
          if defined? @md.date.published \
          and @md.date.published=~/\S+/
            txt=@md.date.published
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def date_valid
        def name
          'date_valid'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_date_text]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata date valid [DC]';}
        end
        def tuple
          if defined? @md.date.valid \
          and @md.date.valid=~/\S+/
            txt=@md.date.valid
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def date_translated
        def name
          'date_translated'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_date_text]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata date translated';}
        end
        def tuple
          if defined? @md.date.translated \
          and @md.date.translated=~/\S+/
            txt=@md.date.translated
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def date_original_publication
        def name
          'date_original_publication'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_date_text]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata date of original publication';}
        end
        def tuple
          if defined? @md.date.original_publication \
          and @md.date.original_publication=~/\S+/
            txt=@md.date.original_publication
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def date_generated
        def name
          'date_generated'
        end
        def create_column              #choose other representation of time
          "#{name}                VARCHAR(30) NULL,"
          #"#{name}                VARCHAR(10) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata date of sisu generation of document, automatically populated';}
        end
        def tuple                      #choose other representation of time
          if defined? @md.generated \
          and @md.generated.to_s=~/\S+/
            txt=@md.generated.to_s
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
=begin
#% publisher
@publisher:
=end
      def publisher
        def name
          'publisher'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_name]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document publisher [DC5]';}
        end
        def tuple
          if defined? @md.publisher \
          and @md.publisher=~/\S+/
            txt=@md.publisher
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
##% current
#    def current_publisher
#      def name
#        'current_publisher'
#      end
#      def size
#        10
#      end
#      def create_column
#        "#{name}                VARCHAR(#{current_publisher.size}) NULL,"
#      end
#      def tuple
#        t=if defined? @md.current.publisher \
#        and @md.current.publisher=~/\S+/
#          txt=@md.current.publisher
#          txt=special_character_escape(txt)
#          "'#{txt}', "
#        end
#      end
#      self
#    end
=begin
#% original
@original:
 :publisher:
 #:date:                                #repeated under date
 :language:
 :institution:
 :nationality:
 :source:
=end
      def original_publisher
        def name
          'original_publisher'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_name]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document original publisher [DC5]';}
        end
        def tuple
          if defined? @md.original.publisher \
          and @md.original.publisher=~/\S+/
            txt=@md.original.publisher
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def original_language
        def name
          'original_language'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_language]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document original language';}
        end
        def tuple
          if defined? @md.original.language \
          and @md.original.language=~/\S+/
            txt=@md.original.language
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def original_language_char         # consider
        def name
          'original_language_char'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_language_char]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document original language iso character';}
        end
        def tuple
          if defined? @md.original.language_char \
          and @md.original.language_char=~/\S+/
            txt=@md.original.language_char
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def original_source
        def name
          'original_source'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_name]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document original source [DC11]';}
        end
        def tuple
          if defined? @md.original.source \
          and @md.original.source=~/\S+/
            txt=@md.original.source
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def original_institution
        def name
          'original_institution'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_name]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document original institution';}
        end
        def tuple
          if defined? @md.original.institution \
          and @md.original.institution=~/\S+/
            txt=@md.original.institution
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def original_nationality
        def name
          'original_nationality'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_language]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document original nationality';}
        end
        def tuple
          if defined? @md.original.nationality \
          and @md.original.nationality=~/\S+/
            txt=@md.original.nationality
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
=begin
#% rights
@rights:
 #:copyright:                          #mapped to :text: used where no other copyrights and included in :all:
 :text:
 :translation:
 :illustrations:
 :photographs:
 :preparation:
 :digitization:
 :audio:
 :video:
 :license:
 :all:
=end
      def rights_all
        def name
          'rights'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_info_note]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata rights associated with document (composite) [DC15]';}
        end
        def tuple
          if defined? @md.rights.all \
          and @md.rights.all=~/\S+/
            txt=@md.rights.all
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def rights_copyright_text
        def name
          'rights_copyright_text'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_info_note]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata copyright associated for document text';}
        end
        def tuple
          if defined? @md.rights.copyright_text \
          and @md.rights.copyright_text=~/\S+/
            txt=@md.rights.copyright_text
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def rights_copyright_translation
        def name
          'rights_copyright_translation'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_info_note]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata copyright associated for document text translation (if any)';}
        end
        def tuple
          if defined? @md.rights.copyright_translation \
          and @md.rights.copyright_translation=~/\S+/
            txt=@md.rights.copyright_translation
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def rights_copyright_illustrations
        def name
          'rights_copyright_illustrations'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_info_note]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata copyright associated for document text illustrations (if any)';}
        end
        def tuple
          if defined? @md.rights.copyright_illustrations \
          and @md.rights.copyright_illustrations=~/\S+/
            txt=@md.rights.copyright_illustrations
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def rights_copyright_photographs
        def name
          'rights_copyright_photographs'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_info_note]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata copyright associated for document text photographs (if any)';}
        end
        def tuple
          if defined? @md.rights.copyright_photographs \
          and @md.rights.copyright_photographs=~/\S+/
            txt=@md.rights.copyright_photographs
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def rights_copyright_preparation
        def name
          'rights_copyright_preparation'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_info_note]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata copyright associated for document text preparation (if any)';}
        end
        def tuple
          if defined? @md.rights.copyright_preparation \
          and @md.rights.copyright_preparation=~/\S+/
            txt=@md.rights.copyright_preparation
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def rights_copyright_digitization
        def name
          'rights_copyright_digitization'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_info_note]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata copyright associated for document text digitization (if any)';}
        end
        def tuple
          if defined? @md.rights.copyright_digitization \
          and @md.rights.copyright_digitization=~/\S+/
            txt=@md.rights.copyright_digitization
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def rights_copyright_audio
        def name
          'rights_copyright_audio'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_info_note]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata copyright associated for document text audio (if any)';}
        end
        def tuple
          if defined? @md.rights.copyright_audio \
          and @md.rights.copyright_audio=~/\S+/
            txt=@md.rights.copyright_audio
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def rights_copyright_video
        def name
          'rights_copyright_video'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_info_note]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata copyright associated for document text video (if any)';}
        end
        def tuple
          if defined? @md.rights.copyright_video \
          and @md.rights.copyright_video=~/\S+/
            txt=@md.rights.copyright_video
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def rights_license
        def name
          'rights_license'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_info_note]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata license granted for use of document if any)';}
        end
        def tuple
          if defined? @md.rights.license \
          and @md.rights.license=~/\S+/
            txt=@md.rights.license
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
=begin
#% identifier
@identifier:
 :oclc:
 :isbn:
=end
      def identifier_oclc
        def name
          'identifier_oclc'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_library]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata identifier document Online Computer Library Center number';}
        end
        def tuple
          if defined? @md.identifier.oclc \
          and @md.identifier.oclc=~/\S+/
            txt=@md.identifier.oclc
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def identifier_isbn
        def name
          'identifier_isbn'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_small]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata identifier document isbn (if any)';}
        end
        def tuple
          if defined? @md.identifier.isbn \
          and @md.identifier.isbn=~/\S+/
            txt=@md.identifier.isbn
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
=begin
#% classify
@classify:
 :topic_register:
 :subject:
 :keywords:
 :type:
 :loc:
 :dewey:
=end
      def classify_topic_register
        def name
          'classify_topic_register'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_info_note]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata classify document topic register (semi-structured document subject information)';}
        end
        def tuple
          if defined? @md.classify.topic_register \
          and @md.classify.topic_register=~/\S+/
            txt=@md.classify.topic_register
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def classify_subject
        def name
          'classify_subject'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_txt_long]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata classify document subject matter [DC3]';}
        end
        def tuple
          if defined? @md.classify.subject \
          and @md.classify.subject=~/\S+/
            txt=@md.classify.subject
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def classify_loc
        def name
          'classify_loc'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_library]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata classify document Library of Congress';}
        end
        def tuple
          if defined? @md.classify.loc \
          and @md.classify.loc=~/\S+/
            txt=@md.classify.loc
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def classify_dewey
        def name
          'classify_dewey'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_library]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata classify document Dewey';}
        end
        def tuple
          if defined? @md.classify.dewey \
          and @md.classify.dewey=~/\S+/
            txt=@md.classify.dewey
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def classify_keywords
        def name
          'classify_keywords'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_txt_long]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata classify document keywords';}
        end
        def tuple
          if defined? @md.classify.keywords \
          and @md.classify.keywords=~/\S+/
            txt=@md.classify.keywords
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
=begin
#% notes
@notes:
 :abstract:
 :description:
 :comment:
 :coverage:
 :relation:
 :format:
 :history:
 :prefix:
 :prefix_a:
 :prefix_b:
 :suffix:
=end
      def notes_abstract
        def name
          'notes_abstract'
        end
        def create_column
          "#{name}                     TEXT NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document notes abstract';}
        end
        def tuple
          if defined? @md.notes.abstract \
          and @md.notes.abstract=~/\S+/
            txt=@md.notes.abstract
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def notes_description
        def name
          'notes_description'
        end
        def create_column
          "#{name}                    TEXT NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document notes description [DC4]';}
        end
        def tuple
          if defined? @md.notes.description \
          and @md.notes.description=~/\S+/
            txt=@md.notes.description
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def notes_comment
        def name
          'notes_comment'
        end
        def create_column
          "#{name}                       TEXT NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document notes comment';}
        end
        def tuple
          if defined? @md.notes.comment \
          and @md.notes.comment=~/\S+/
            txt=@md.notes.comment
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def notes_coverage
        def name
          'notes_coverage'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_txt_short]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata classify document coverage [DC14]';}
        end
        def tuple
          if defined? @md.classify.coverage \
          and @md.classify.coverage=~/\S+/
            txt=@md.classify.coverage
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def notes_relation
        def name
          'notes_relation'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_txt_short]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata classify document relation [DC13]';}
        end
        def tuple
          if defined? @md.classify.relation \
          and @md.classify.relation=~/\S+/
            txt=@md.classify.relation
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def notes_history   #check, consider removal
        def name
          'notes_history'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_txt_long]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document notes history';}
        end
        def tuple
          if defined? @md.notes.history \
          and @md.notes.history=~/\S+/
            txt=@md.notes.history
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def notes_type #check
        def name
          'notes_type'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_txt_long]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata notes document type [DC8]';}
        end
        def tuple
          if defined? @md.notes.type \
          and @md.notes.type=~/\S+/
            txt=@md.notes.type
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def notes_format
        def name
          'notes_format'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_txt_long]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata classify document format [DC9]';}
        end
        def tuple
          if defined? @md.classify.format \
          and @md.classify.format=~/\S+/
            txt=@md.classify.format
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def notes_prefix
        def name
          'notes_prefix'
        end
        def create_column
          "#{name}                TEXT NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document notes prefix';}
        end
        def tuple
          if defined? @md.notes.prefix \
          and @md.notes.prefix=~/\S+/
            txt=@md.notes.prefix
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def notes_prefix_a
        def name
          'notes_prefix_a'
        end
        def create_column
          "#{name}                TEXT NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document notes prefix_a';}
        end
        def tuple
          if defined? @md.notes.prefix_a \
          and @md.notes.prefix_a=~/\S+/
            txt=@md.notes.prefix_a
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def notes_prefix_b
        def name
          'notes_prefix_b'
        end
        def create_column
          "#{name}                TEXT NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document notes prefix_b';}
        end
        def tuple
          if defined? @md.notes.prefix_b \
          and @md.notes.prefix_b=~/\S+/
            txt=@md.notes.prefix_b
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def notes_suffix
        def name
          'notes_suffix'
        end
        def create_column                # keep text
          "#{name}                TEXT NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document notes suffix';}
        end
        def tuple
          if defined? @md.notes.suffix \
          and @md.notes.suffix=~/\S+/
            txt=@md.notes.suffix
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
=begin
#% src
=end
      def src_filename
        def name
          'src_filename'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_filename]}) NOT NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'sisu markup source text filename';}
        end
        def tuple
          if defined? @md.fns \
          and @md.fns=~/\S+/
            txt=@md.fns
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def src_fingerprint
        def name
          'src_fingerprint' #hash/digest, sha512, sha256 or md5
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_digest]}) NULL,"
          #"#{name}                TEXT NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
            IS 'sisu markup source text fingerprint, hash digest sha512, sha256 or md5';}
        end
        def tuple
          if defined? @md.dgst \
          and @md.dgst.is_a?(Array) \
          and @md.dgst[1]=~/\S+/
            txt=@md.dgst[1]
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def src_filesize
        def name
          'src_filesize'
        end
        def create_column
          "#{name}                VARCHAR(#{Db[:col_filesize]}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
            IS 'sisu markup source text file size';}
        end
        def tuple
         if defined? @md.filesize \
         and @md.filesize=~/\S+/
           txt=@md.filesize
           txt=special_character_escape(txt)
           ["#{name}, ","'#{txt}', "]
         else ['','']
         end
        end
        self
      end
      def src_word_count
        def name
          'src_word_count'
        end
        def create_column
          "#{name}                TEXT NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
            IS 'sisu markup source text word count';}
        end
        def tuple
          if defined? @md.wc_words \
          and @md.wc_words.to_s=~/\S+/
            txt=@md.wc_words
            txt=special_character_escape(txt)
            ["#{name}, ","'#{txt}', "]
          else ['','']
          end
        end
        self
      end
      def src_txt                      # consider naming sisusrc
        def name
          'src_text'
        end
        def create_column
          "#{name}                TEXT NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'sisu markup source text (if shared)';}
        end
        def tuple
          if ((@md.opt.act[:psql_import][:set]==:on \
          || @md.opt.act[:psql_update][:set]==:on) \
          or (@md.opt.act[:sqlite_import][:set]==:on \
          || @md.opt.act[:sqlite_update][:set]==:on)) \
          and FileTest.exist?(@md.fns)
            ["#{name}, ","'#{@sisutxt}', "]
          else ['','']
          end
        end
        self
      end
=begin
#% misc
@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
          if ((@md.opt.act[:psql_import][:set]==:on \
          || @md.opt.act[:psql_update][:set]==:on) \
          or (@md.opt.act[:sqlite_import][:set]==:on \
          || @md.opt.act[:sqlite_update][:set]==:on)) \
          and  FileTest.exist?(@md.fns)
            ["#{name}, ","'#{@fulltext}', "]
          else ['','']
          end
        end
        self
      end
      def links
        def name
          'links'
        end
        def create_column
          "#{name}                TEXT NULL,"
          #"#{name}                 VARCHAR(#{links.size}) NULL,"
        end
        def column_comment
          %{COMMENT ON COLUMN metadata_and_text.#{name}
           IS 'metadata document links';}
        end
        #def tuple
        #  #BUG HERE - links is an array of paired values :say :url
        #  if defined? @md.links \
        #  and @md.links=~/\S+/
        #    txt=@md.links
        #    txt=special_character_escape(txt)
        #    ["#{name}, ","'#{txt}', "]
        #  else ['','']
        #  end
        #end
        self
      end
      self
    end
  end
  class ColumnSize
    def document_clean # restriction not necessary
      60000
    end
    def document_body
      16000
    end
    def document_seg
      120
    end
    def document_seg_full
      120
    end
    def endnote_clean # restriction not necessary
      60000
    end
    def endnote_body
      16000
    end
  end
end
__END__
#+END_SRC

** db_indexes.rb

#+BEGIN_SRC ruby  :tangle "../lib/sisu/db_indexes.rb"
<<sisu_document_header>>
module SiSU_DbIndex
  class Index                                           # create documents Indexes def initialize(opt,conn='',sql_type)
    def initialize(opt,conn,file,sql_type)
      @opt,@conn,@file,@sql_type=opt,conn,file,sql_type
    end
    def create_indexes                                                           # check added from pg not tested
      def conn_execute_sql_pg(conn,sql)
        conn.exec_params(sql)
      end
      def conn_execute_sql_sqlite(conn,sql)
        conn.execute(sql)
      end
      def conn_execute_sql(conn,sql)
        if @sql_type==:pg
          conn_execute_sql_pg(conn,sql)
        elsif @sql_type==:sqlite
          conn_execute_sql_sqlite(conn,sql)
        else
        end
      end
      def conn_execute_array(sql_arr)
        begin
          @conn.transaction do |conn|
            sql_arr.each do |sql|
              conn_execute_sql(conn,sql)
            end
          end
        rescue
          if @conn.is_a?(NilClass)
            errmsg="No sqlite3 connection (check sqlite3 dependencies)"
            if @opt.act[:no_stop][:set]==:on
              SiSU_Utils::CodeMarker.new(__LINE__,__FILE__,:fuchsia).
                error("#{errmsg}, proceeding without sqlite output (as requested)")
            else
              SiSU_Utils::CodeMarker.new(__LINE__,__FILE__,:fuchsia).
                error("#{errmsg}, STOPPING")
              exit
            end
          end
        end
      end
      def base
        if (@opt.act[:verbose_plus][:set]==:on \
        || @opt.act[:maintenance][:set]==:on)
          print "\n          create documents common indexes\n"
        end
        sql_arr=[
          %{CREATE INDEX idx_ocn ON doc_objects(ocn);},
          %{CREATE INDEX idx_digest_clean ON doc_objects(digest_clean);},
          %{CREATE INDEX idx_digest_all ON doc_objects(digest_all);},
          %{CREATE INDEX idx_lev0 ON doc_objects(lev0);},
          %{CREATE INDEX idx_lev1 ON doc_objects(lev1);},
          %{CREATE INDEX idx_lev2 ON doc_objects(lev2);},
          %{CREATE INDEX idx_lev3 ON doc_objects(lev3);},
          %{CREATE INDEX idx_lev4 ON doc_objects(lev4);},
          %{CREATE INDEX idx_lev5 ON doc_objects(lev5);},
          %{CREATE INDEX idx_lev6 ON doc_objects(lev6);},
          %{CREATE INDEX idx_endnote_nr ON endnotes(nr);},
          %{CREATE INDEX idx_digest_en ON endnotes(digest_clean);},
          %{CREATE INDEX idx_endnote_nr_asterisk ON endnotes_asterisk(nr);},
          %{CREATE INDEX idx_endnote_asterisk ON endnotes_asterisk(clean);},
          %{CREATE INDEX idx_digest_en_asterisk ON endnotes_asterisk(digest_clean);},
          %{CREATE INDEX idx_endnote_nr_plus ON endnotes_plus(nr);},
          %{CREATE INDEX idx_endnote_plus ON endnotes_plus(clean);},
          %{CREATE INDEX idx_digest_en_plus ON endnotes_plus(digest_clean);},
          %{CREATE INDEX idx_title ON metadata_and_text(title);},
          %{CREATE INDEX idx_author ON metadata_and_text(creator_author);},
          %{CREATE INDEX idx_filename ON metadata_and_text(src_filename);},
          %{CREATE INDEX idx_language ON metadata_and_text(language_document_char);},
          %{CREATE INDEX idx_topics ON metadata_and_text(classify_topic_register)},
        ]
        conn_execute_array(sql_arr)
      end
      def text
        if (@opt.act[:verbose_plus][:set]==:on \
        || @opt.act[:maintenance][:set]==:on)
          print "\n          create documents TEXT indexes\n"
        end
        sql_arr=[
          %{CREATE INDEX idx_clean ON doc_objects(clean);},
          %{CREATE INDEX idx_endnote ON endnotes(clean);}
        ]
        conn_execute_array(sql_arr)
      end
      base
      @opt.act[:psql][:set]==:on ? '' : text
    end
  end
end
__END__
#+END_SRC

* db_tests.rb

#+BEGIN_SRC ruby  :tangle "../lib/sisu/db_tests.rb"
<<sisu_document_header>>
module SiSU_DbTests
  class Test
    def initialize(info,opt)
      @ck,@opt=info,opt
      unless @opt.act[:quiet][:set]==:on
        puts @ck.tp[:fns]  if @ck.tp[:fns] and not @ck.tp[:fns].empty?
        puts @ck.tp[:title] if @ck.tp[:title] and not @ck.tp[:title].empty?
        puts @ck.tp[:creator] if @ck.tp[:creator] and not @ck.tp[:creator].empty?
      end
    end
    def verify
      unless @opt.act[:quiet][:set]==:on
        puts @ck.tp[:fns].length.to_s                + ' checklength ' + @ck.tp[:fns]                if @ck.tp[:fns]                and @ck.tp[:fns].length                >@ck.lt_filename
        puts @ck.tp[:title].length.to_s              + ' checklength ' + @ck.tp[:title]              if @ck.tp[:title]              and @ck.tp[:title].length              >@ck.lt_title
        puts @ck.tp[:subtitle].length.to_s           + ' checklength ' + @ck.tp[:subtitle]           if @ck.tp[:subtitle]           and @ck.tp[:subtitle].length           >@ck.lt_subtitle
        puts @ck.tp[:creator].length.to_s            + ' checklength ' + @ck.tp[:creator]            if @ck.tp[:creator]            and @ck.tp[:creator].length            >@ck.lt_creator
        puts @ck.tp[:author_title].length.to_s       + ' checklength ' + @ck.tp[:author_title]       if @ck.tp[:author_title]       and @ck.tp[:author_title].length       >@ck.lt_author_title
        puts @ck.tp[:illustrator].length.to_s        + ' checklength ' + @ck.tp[:illustrator]        if @ck.tp[:illustrator]        and @ck.tp[:illustrator].length        >@ck.lt_illustrator
        puts @ck.tp[:translator].length.to_s         + ' checklength ' + @ck.tp[:translator]         if @ck.tp[:translator]         and @ck.tp[:translator].length         >@ck.lt_translator
        puts @ck.tp[:prepared_by].length.to_s        + ' checklength ' + @ck.tp[:prepared_by]        if @ck.tp[:prepared_by]        and @ck.tp[:prepared_by].length        >@ck.lt_prepared_by
        puts @ck.tp[:digitized_by].length.to_s       + ' checklength ' + @ck.tp[:digitized_by]       if @ck.tp[:digitized_by]       and @ck.tp[:digitized_by].length       >@ck.lt_digitized_by
        puts @ck.tp[:subject].length.to_s            + ' checklength ' + @ck.tp[:subject]            if @ck.tp[:subject]            and @ck.tp[:subject].length            >@ck.lt_subject
        puts @ck.tp[:description].length.to_s        + ' checklength ' + @ck.tp[:description]        if @ck.tp[:description]        and @ck.tp[:description].length        >@ck.lt_description
        puts @ck.tp[:publisher].length.to_s          + ' checklength ' + @ck.tp[:publisher]          if @ck.tp[:publisher]          and @ck.tp[:publisher].length          >@ck.lt_publisher
        puts @ck.tp[:contributor].length.to_s        + ' checklength ' + @ck.tp[:contributor]        if @ck.tp[:contributor]        and @ck.tp[:contributor].length        >@ck.lt_contributor
        puts @ck.tp[:date].length.to_s               + ' checklength ' + @ck.tp[:date]               if @ck.tp[:date]               and @ck.tp[:date].length               >@ck.lt_date
        puts @ck.tp[:date_created].length.to_s       + ' checklength ' + @ck.tp[:date_created]       if @ck.tp[:date_created]       and @ck.tp[:date_created].length       >@ck.lt_date
        puts @ck.tp[:date_issued].length.to_s        + ' checklength ' + @ck.tp[:date_issued]        if @ck.tp[:date_issued]        and @ck.tp[:date_issued].length        >@ck.lt_date
        puts @ck.tp[:date_valid].length.to_s         + ' checklength ' + @ck.tp[:date_valid]         if @ck.tp[:date_valid]         and @ck.tp[:date_valid].length         >@ck.lt_date
        puts @ck.tp[:date_available].length.to_s     + ' checklength ' + @ck.tp[:date_available]     if @ck.tp[:date_available]     and @ck.tp[:date_available].length     >@ck.lt_date
        puts @ck.tp[:date_modified].length.to_s      + ' checklength ' + @ck.tp[:date_modified]      if @ck.tp[:date_modified]      and @ck.tp[:date_modified].length      >@ck.lt_date
        puts @ck.tp[:date_translated].length.to_s    + ' checklength ' + @ck.tp[:date_translated]    if @ck.tp[:date_translated]    and @ck.tp[:date_translated].length    >@ck.lt_date
        puts @ck.tp[:date_added_to_site].length.to_s + ' checklength ' + @ck.tp[:date_added_to_site] if @ck.tp[:date_added_to_site] and @ck.tp[:date_added_to_site].length >@ck.lt_date
        puts @ck.tp[:type].length.to_s               + ' checklength ' + @ck.tp[:type]               if @ck.tp[:type]               and @ck.tp[:type].length               >@ck.lt_type
        puts @ck.tp[:format].length.to_s             + ' checklength ' + @ck.tp[:format]             if @ck.tp[:format]             and @ck.tp[:format].length             >@ck.lt_format
        puts @ck.tp[:identifier].length.to_s         + ' checklength ' + @ck.tp[:identifier]         if @ck.tp[:identifier]         and @ck.tp[:identifier].length         >@ck.lt_identifier
        puts @ck.tp[:source].length.to_s             + ' checklength ' + @ck.tp[:source]             if @ck.tp[:source]             and @ck.tp[:source].length             >@ck.lt_source
        puts @ck.tp[:language].length.to_s           + ' checklength ' + @ck.tp[:language]           if @ck.tp[:language]           and @ck.tp[:language].length           >@ck.lt_language
        puts @ck.tp[:language_original].length.to_s  + ' checklength ' + @ck.tp[:language_original]  if @ck.tp[:language_original]  and @ck.tp[:language_original].length  >@ck.lt_language_original
        puts @ck.tp[:relation].length.to_s           + ' checklength ' + @ck.tp[:relation]           if @ck.tp[:relation]           and @ck.tp[:relation].length           >@ck.lt_relation
        puts @ck.tp[:coverage].length.to_s           + ' checklength ' + @ck.tp[:coverage]           if @ck.tp[:coverage]           and @ck.tp[:coverage].length           >@ck.lt_coverage
        puts @ck.tp[:rights].length.to_s             + ' checklength ' + @ck.tp[:rights]             if @ck.tp[:rights]             and @ck.tp[:rights].length             >@ck.lt_rights
        puts @ck.tp[:copyright].length.to_s          + ' checklength ' + @ck.tp[:copyright]          if @ck.tp[:copyright]          and @ck.tp[:copyright].length          >@ck.lt_copyright
        puts @ck.tp[:owner].length.to_s              + ' checklength ' + @ck.tp[:owner]              if @ck.tp[:owner]              and @ck.tp[:owner].length              >@ck.lt_owner
        puts @ck.tp[:keywords].length.to_s           + ' checklength ' + @ck.tp[:keywords]           if @ck.tp[:keywords]           and @ck.tp[:keywords].length           >@ck.lt_keywords
        puts @ck.tp[:abstract].length.to_s           + ' checklength ' + @ck.tp[:abstract]           if @ck.tp[:abstract]           and @ck.tp[:abstract].length           >@ck.lt_abstract
        puts @ck.tp[:comment].length.to_s            + ' checklength ' + @ck.tp[:comment]            if @ck.tp[:comment]            and @ck.tp[:comment].length            >@ck.lt_comment
        puts @ck.tp[:loc].length.to_s                + ' checklength ' + @ck.tp[:loc]                if @ck.tp[:loc]                and @ck.tp[:loc].length                >@ck.lt_loc
        puts @ck.tp[:dewey].length.to_s              + ' checklength ' + @ck.tp[:dewey]              if @ck.tp[:dewey]              and @ck.tp[:dewey].length              >@ck.lt_dewey
        puts @ck.tp[:isbn].length.to_s               + ' checklength ' + @ck.tp[:isbn]               if @ck.tp[:isbn]               and @ck.tp[:isbn].length               >@ck.lt_isbn
        puts @ck.tp[:pg].length.to_s                 + ' checklength ' + @ck.tp[:pg]                 if @ck.tp[:pg]                 and @ck.tp[:pg].length                 >@ck.lt_pg
        puts @ck.tp[:topic_register].length.to_s     + ' checklength ' + @ck.tp[:topic_register]     if @ck.tp[:topic_register]     and @ck.tp[:topci_register].length     >@ck.lt_topic_register
        puts @ck.tp[:date]                                                                           if @ck.tp[:date] and not @ck.tp[:date].empty? and @ck.tp[:date] !~/\d\d-\d\d-\d\d/
      end
    end
  end
end
__END__
#+END_SRC

* document header

#+NAME: sisu_document_header
#+BEGIN_SRC text
#encoding: utf-8
=begin
- Name: SiSU

  - Description: documents, structuring, processing, publishing, search
    db sql

  - Author: Ralph Amissah
    <ralph.amissah@gmail.com>

  - Copyright: (C) 1997, 1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006,
    2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2019,
    2020, 2021, Ralph Amissah,
    All Rights Reserved.

  - License: GPL 3 or later:

    SiSU, a framework for document structuring, publishing and search

    Copyright (C) Ralph Amissah

    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 3 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, see <http://www.gnu.org/licenses/>.

    If you have Internet connection, the latest version of the GPL should be
    available at these locations:
    <http://www.fsf.org/licensing/licenses/gpl.html>
    <http://www.gnu.org/licenses/gpl.html>

    <http://www.sisudoc.org/sisu/en/manifest/gpl.fsf.html>

  - SiSU uses:
    - Standard SiSU markup syntax,
    - Standard SiSU meta-markup syntax, and the
    - Standard SiSU object citation numbering and system

  - Homepages:
    <http://www.sisudoc.org>

  - Git
    <https://git.sisudoc.org/projects/>
    <https://git.sisudoc.org/projects/?p=software/sisu.git;a=summary>
    <https://git.sisudoc.org/projects/?p=markup/sisu-markup-samples.git;a=summary>
=end
#+END_SRC