cgi sample search form (cgi_sql_common): take account of db structure changes; match limit options

* update to work with database structure changes

* add radio buttons to set increased match limit

3 files changed, 119 insertions, 81 deletions

diff --git a/lib/sisu/v2/cgi_pgsql.rb b/lib/sisu/v2/cgi_pgsql.rb
index db2d8267..493c39d4 100644
--- a/lib/sisu/v2/cgi_pgsql.rb
+++ b/lib/sisu/v2/cgi_pgsql.rb
@@ -68,7 +68,7 @@ module  SiSU_CGI_pgsql
       @image_src="#{@env.url.webserv_cgi}/_sisu/image_sys"
       @common=SiSU_CGI_sql::SiSU_CGI_common.new(@webserv,@opt.cmd,@image_src,@env)
       @db=SiSU_Env::Info_db.new
-      @cgi_file_name="sisu_#{SiSU_version_dir}_pgsql.cgi"
+      @cgi_file_name="#{Db[:name_prefix_db]}pgsql.cgi"
     end
     def pgsql
       serve=[]
@@ -177,7 +177,7 @@ module  SiSU_CGI_pgsql
           @search_text,@search_endnotes=[],[]
           search[:text].each{|x| @search_text << "#{x} AND " }
           @search_text=@search_text.join.gsub!(/AND\s+$/,'') #watch
-          @search_text.gsub!(/(documents\.clean~[*]?\(\s*'[^']+'\s*\)\s+(?:(?:AND|OR)\s+documents\.clean~[*]?\(\s*'[^']+'\s*\))+)/,'(\1)')
+          @search_text.gsub!(/(doc_objects\.clean~[*]?\(\s*'[^']+'\s*\)\s+(?:(?:AND|OR)\s+doc_objects\.clean~[*]?\(\s*'[^']+'\s*\))+)/,'(\1)')
           search[:endnotes].each{|x| @search_endnotes << "#{x} AND " }
           @search_endnotes=@search_endnotes.join.gsub!(/AND\s+$/,'') #watch
           @search_endnotes.gsub!(/(endnotes\.clean~\(\s*'[^']+'\s*\)\s+(?:(?:AND|OR)\s+endnotes\.clean~\(\s*'[^']+'\s*\))+)/,'(\1)')
@@ -189,7 +189,7 @@ module  SiSU_CGI_pgsql
         def sql_select_body
           limit ||=@@limit
           offset ||=@@offset
-          @sql_statement[:body]=%{SELECT metadata.title, metadata.subtitle, metadata.creator, metadata.filename, metadata.suffix, documents.body, documents.seg, documents.ocn, metadata.tid FROM documents, metadata WHERE (#{@search_text}) AND documents.metadata_tid = metadata.tid ORDER BY metadata.title, metadata.filename, documents.ocn}
+          @sql_statement[:body]=%{SELECT metadata_and_text.title, metadata_and_text.creator_author, metadata_and_text.filename, metadata_and_text.notes_suffix, doc_objects.body, doc_objects.seg, doc_objects.ocn, metadata_and_text.tid FROM doc_objects, metadata_and_text WHERE (#{@search_text}) AND doc_objects.metadata_tid = metadata_and_text.tid ORDER BY metadata_and_text.title, metadata_and_text.filename, doc_objects.ocn}
           @sql_statement[:range]=%{LIMIT #{limit} OFFSET #{offset} ;}
           select=@sql_statement[:body] + ' ' + @sql_statement[:range]
           select
@@ -197,7 +197,7 @@ module  SiSU_CGI_pgsql
         def sql_select_endnotes
           limit ||=@@limit
           offset ||=@@offset
-          @sql_statement[:endnotes]=%{SELECT metadata.title, metadata.subtitle, metadata.creator, metadata.filename, endnotes.body, endnotes.nr, endnotes.ocn, endnotes.metadata_tid FROM metadata, endnotes WHERE (#{@search_endnotes}) AND metadata.tid = endnotes.metadata_tid ORDER BY metadata.title, metadata.filename, endnotes.nr}
+          @sql_statement[:endnotes]=%{SELECT metadata_and_text.title, metadata_and_text.creator_author, metadata_and_text.filename, endnotes.body, endnotes.nr, endnotes.ocn, endnotes.metadata_tid FROM metadata_and_text, endnotes WHERE (#{@search_endnotes}) AND metadata_and_text.tid = endnotes.metadata_tid ORDER BY metadata_and_text.title, metadata_and_text.filename, endnotes.nr}
           @sql_statement[:range]=%{LIMIT #{limit} OFFSET #{offset} ;}
           select=@sql_statement[:endnotes] + ' ' + @sql_statement[:range]
           select
@@ -219,7 +219,7 @@ module  SiSU_CGI_pgsql
     end
     def buttons1_pgsql
       <<-'WOK_SQL'
-            <input type="checkbox" name="casesense" #{@checked_case}> case sensitive
+        <input type="checkbox" name="casesense" #{@checked_case}> case sensitive
       WOK_SQL
     end
     def dbi_connect
diff --git a/lib/sisu/v2/cgi_sql_common.rb b/lib/sisu/v2/cgi_sql_common.rb
index 0c3977c4..e536551f 100644
--- a/lib/sisu/v2/cgi_sql_common.rb
+++ b/lib/sisu/v2/cgi_sql_common.rb
@@ -125,19 +125,19 @@ module SiSU_CGI_sql
     def header1
       <<-'WOK_SQL'
 #Common TOP
-      @@limit,@@offset=1000,0
+      @@offset=0
       @base="#{@hosturl_db}/cgi-bin/#{@version}.cgi"
       @@canned_search_url=@base
       @color_heading='#DDFFAA'
       @color_match='#ffff48'
       class Form
-        def initialize(base,search_field,selected_db,checked_index,checked_text,checked_tip,checked_stats,checked_searched,checked_url,checked_case,checked_echo,checked_sql,checked_all,checked_none,checked_selected,checked_default,search_note,the_can='')
+        def initialize(base,search_field,selected_db,result_type,checked_sql_limit,checked_tip,checked_stats,checked_searched,checked_url,checked_case,checked_echo,checked_sql,checked_all,checked_none,checked_selected,checked_default,search_note,the_can='')
           search_note='' if checked_searched !~/\S/
           the_can='' if checked_url !~/\S/
           search_field='' if checked_echo !~/\S/
-          @base,@search_field,@selected_db,@checked_index,@checked_text,@checked_tip,@checked_stats,@checked_searched,@checked_url,@checked_case,@checked_echo,@checked_sql,@checked_all,@checked_none,@checked_selected,@checked_default,@search_note,@the_can=base,search_field,selected_db,checked_index,checked_text,checked_tip,checked_stats,checked_searched,checked_url,checked_case,checked_echo,checked_sql,checked_all,checked_none,checked_selected,checked_default,search_note,the_can
+          @base,@search_field,@selected_db,@result_type,@checked_sql_limit,@checked_tip,@checked_stats,@checked_searched,@checked_url,@checked_case,@checked_echo,@checked_sql,@checked_all,@checked_none,@checked_selected,@checked_default,@search_note,@the_can=base,search_field,selected_db,result_type,checked_sql_limit,checked_tip,checked_stats,checked_searched,checked_url,checked_case,checked_echo,checked_sql,checked_all,checked_none,checked_selected,checked_default,search_note,the_can
           @tip=if checked_tip =~/\S/
-            '<font size="2" color="#666666">text:__; keywords:__; title:__; author:__; subject:__; description:__; publisher:__; contributor:__; date:__; type:__; format:__; identifier:__; source:__; language:__; relation:__; coverage:__; rights:__; comment:__; abstract:__; filename:__;</font><br />'
+            '<font size="2" color="#666666">text:__; fulltxt:__; keywords:__; title:__; author:__; topic_register:__; subject:__; description:__; publisher:__; contributor:__; date:__; type:__; format:__; identifier:__; source:__; language:__; relation:__; coverage:__; rights:__; comment:__; abstract:__; filename:__;</font><br />'
           else ''
           end
         end
@@ -195,13 +195,19 @@ module SiSU_CGI_sql
       <<-'WOK_SQL'
         </select>
         <input type="submit" value="SiSU search" />
-        <input type="radio" name="view" value="index" #{@checked_index}> index
-        <input type="radio" name="view" value="text" #{@checked_text}> text / grep
+        <input type="radio" name="view" value="index" #{@result_type[:index]}> index
+        <input type="radio" name="view" value="text" #{@result_type[:text]}> text / grep
       WOK_SQL
     end
     def buttons2
       <<-'WOK_SQL'
         <br />
+          match limit:
+          <input type="radio" name="sql_match_limit" value="1000" #{@checked_sql_limit[:l1k]}> 1,000
+          <input type="radio" name="sql_match_limit" value="2000" #{@checked_sql_limit[:l2k]}> 2,000
+          <input type="radio" name="sql_match_limit" value="5000" #{@checked_sql_limit[:l5k]}> 5,000
+          <input type="radio" name="sql_match_limit" value="10000" #{@checked_sql_limit[:l10k]}> 10,000
+        <br />
           <input type="checkbox" name="echo" #{@checked_echo}> echo query
           <input type="checkbox" name="stats" #{@checked_stats}> result stats
           <input type="checkbox" name="url" #{@checked_url}> search url
@@ -226,13 +232,14 @@ module SiSU_CGI_sql
     def search_request
       <<-'WOK_SQL'
       class Search_request                                                       #% search_for
-        attr_accessor :text1,:keywords,:title,:author,:subject,:description,:publisher,:contributor,:date,:type,:format,:identifier,:source,:language,:relation,:coverage,:rights,:comment,:abstract,:owner,:date_created,:date_issued,:date_modified,:date_available,:date_valid,:filename
+        attr_accessor :text1,:fulltext,:keywords,:title,:author,:topic_register,:subject,:description,:publisher,:contributor,:date,:type,:format,:identifier,:source,:language,:relation,:coverage,:rights,:comment,:abstract,:owner,:date_created,:date_issued,:date_modified,:date_available,:date_valid,:filename
         def initialize(search_field='',q='')
           @search_field,@q=search_field,q
-          @text1=@keywords=@title=@author=@subject=@description=@publisher=@contributor=@date=@type=@format=@identifier=@source=@language=@relation=@coverage=@rights=@comment=@abstract=@owner=@date_created=@date_issued=@date_modified=@date_available=@date_valid=@filename=''
+          @text1=@fulltext=@keywords=@title=@author=@topic_register=@subject=@description=@publisher=@contributor=@date=@type=@format=@identifier=@source=@language=@relation=@coverage=@rights=@comment=@abstract=@owner=@date_created=@date_issued=@date_modified=@date_available=@date_valid=@filename=''
           if @search_field=~/\S/
             @text1=text_to_match('text:')
-            @keywords=text_to_match('key(?:words?)?:')
+            @fulltext=text_to_match('fulltxt:')
+            @topic_register=text_to_match('topic_register:')
             @title=text_to_match('title:')                  # DublinCore 1  - title
             @author=text_to_match('(?:author|creator)s?:')  # DublinCore 2  - creator/author
             @subject=text_to_match('subj(?:ect)?:')         # DublinCore 3  - subject
@@ -248,6 +255,7 @@ module SiSU_CGI_sql
             @relation=text_to_match('relation:')            # DublinCore 13 - relation
             @coverage=text_to_match('coverage:')            # DublinCore 14 - coverage
             @rights=text_to_match('rights:')                # DublinCore 15 - rights
+            @keywords=text_to_match('key(?:words?)?:')
             @comment=text_to_match('comment:')
             @abstract=text_to_match('abs(?:tract)?:')
             @owner=text_to_match('owner:')
@@ -257,12 +265,14 @@ module SiSU_CGI_sql
             @date_available=text_to_match('date_available:')
             @date_valid=text_to_match('date_valid:')
             @filename=text_to_match('filename:')
-            @text1=text_to_match unless @keywords or @author or @title or @text1 or @comment or @abstract or @rights or @subject or @publisher or @date or @filename
+            @text1=text_to_match unless @keywords or @author or @title or @text1 or @fulltext or @comment or @abstract or @rights or @subject or @publisher or @date or @filename or @topic_register
           else
             @text1=q['s1'] if q['s1']=~/\S/
+            @fulltext=q['ft'] if q['ft']=~/\S/
             @keywords=q['key'] if q['key']=~/\S/
             @title=q['ti'] if q['ti']=~/\S/
             @author=q['au'] if q['au']=~/\S/
+            @topic_register=q['tr'] if q['tr']=~/\S/
             @subject=q['sj'] if q['sj']=~/\S/
             @description=q['dsc'] if q['dsc']=~/\S/
             @publisher=q['pb'] if q['pb']=~/\S/
@@ -327,99 +337,109 @@ module SiSU_CGI_sql
           cse=if c =~/\S/; true
           else false
           end
-          st=Dbi_search_string.new('documents.clean',search_for.text1,q['s1'],cse).string
+          st=Dbi_search_string.new('doc_objects.clean',search_for.text1,q['s1'],cse).string
           se=Dbi_search_string.new('endnotes.clean',search_for.text1,q['s1'],cse).string
           @text_search_flag=st[:flag]
           if st[:flag]
             search[:text]                    << st[:search]
             search[:endnotes]                << se[:search]
           end
-          st=Dbi_search_string.new('metadata.keywords',search_for.keywords,q['key'],cse).string
+          st=Dbi_search_string.new('metadata_and_text.fulltext',search_for.fulltext,q['ft'],cse).string
+          if st[:flag]
+            search[:text]                    << st[:search]
+            search[:endnotes]                << st[:search]
+          end
+          st=Dbi_search_string.new('metadata_and_text.classify_keywords',search_for.keywords,q['key'],cse).string
+          if st[:flag]
+            search[:text]                    << st[:search]
+            search[:endnotes]                << st[:search]
+          end
+          st=Dbi_search_string.new('metadata_and_text.title',search_for.title,q['ti'],cse).string
           if st[:flag]
             search[:text]                    << st[:search]
             search[:endnotes]                << st[:search]
           end
-          st=Dbi_search_string.new('metadata.title',search_for.title,q['ti'],cse).string
+          st=Dbi_search_string.new('metadata_and_text.creator_author',search_for.author,q['au'],cse).string
           if st[:flag]
             search[:text]                    << st[:search]
             search[:endnotes]                << st[:search]
           end
-          st=Dbi_search_string.new('metadata.creator',search_for.author,q['au'],cse).string
+          st=Dbi_search_string.new('metadata_and_text.classify_topic_register',search_for.topic_register,q['tr'],cse).string
           if st[:flag]
             search[:text]                    << st[:search]
             search[:endnotes]                << st[:search]
           end
-          st=Dbi_search_string.new('metadata.subject',search_for.subject,q['sj'],cse).string
+          st=Dbi_search_string.new('metadata_and_text.classify_subject',search_for.subject,q['sj'],cse).string
           if st[:flag]
             search[:text]                    << st[:search]
             search[:endnotes]                << st[:search]
           end
-          st=Dbi_search_string.new('metadata.description',search_for.description,q['dsc'],cse).string
+          st=Dbi_search_string.new('metadata_and_text.notes_description',search_for.description,q['dsc'],cse).string
           if st[:flag]
             search[:text]                    << st[:search]
             search[:endnotes]                << st[:search]
           end
-          st=Dbi_search_string.new('metadata.publisher',search_for.publisher,q['pb'],cse).string
+          st=Dbi_search_string.new('metadata_and_text.publisher',search_for.publisher,q['pb'],cse).string
           if st[:flag]
             search[:text]                    << st[:search]
             search[:endnotes]                << st[:search]
           end
-          st=Dbi_search_string.new('metadata.contributor',search_for.contributor,q['cntr'],cse).string
+          st=Dbi_search_string.new('metadata_and_text.creator_contributor',search_for.contributor,q['cntr'],cse).string
           if st[:flag]
             search[:text]                    << st[:search]
             search[:endnotes]                << st[:search]
           end
-          st=Dbi_search_string.new('metadata.date',search_for.date,q['dt'],cse).string
+          st=Dbi_search_string.new('metadata_and_text.date_published',search_for.date,q['dt'],cse).string
           if st[:flag]
             search[:text]                    << st[:search]
             search[:endnotes]                << st[:search]
           end
-          st=Dbi_search_string.new('metadata.type',search_for.type,q['ty'],cse).string
+          st=Dbi_search_string.new('metadata_and_text.classify_type',search_for.type,q['ty'],cse).string
           if st[:flag]
             search[:text]                    << st[:search]
             search[:endnotes]                << st[:search]
           end
-          st=Dbi_search_string.new('metadata.identifier',search_for.identifier,q['id'],cse).string
+          st=Dbi_search_string.new('metadata_and_text.classify_identifier',search_for.identifier,q['id'],cse).string
           if st[:flag]
             search[:text]                    << st[:search]
             search[:endnotes]                << st[:search]
           end
-          st=Dbi_search_string.new('metadata.source',search_for.source,q['src'],cse).string
+          st=Dbi_search_string.new('metadata_and_text.original_source',search_for.source,q['src'],cse).string
           if st[:flag]
             search[:text]                    << st[:search]
             search[:endnotes]                << st[:search]
           end
-          st=Dbi_search_string.new('metadata.language',search_for.language,q['lang'],cse).string
+          st=Dbi_search_string.new('metadata_and_text.title_language',search_for.language,q['lang'],cse).string
           if st[:flag]
             search[:text]                    << st[:search]
             search[:endnotes]                << st[:search]
           end
-          st=Dbi_search_string.new('metadata.relation',search_for.relation,q['rel'],cse).string
+          st=Dbi_search_string.new('metadata_and_text.classify_relation',search_for.relation,q['rel'],cse).string
           if st[:flag]
             search[:text]                    << st[:search]
             search[:endnotes]                << st[:search]
           end
-          st=Dbi_search_string.new('metadata.coverage',search_for.coverage,q['cov'],cse).string
+          st=Dbi_search_string.new('metadata_and_text.classify_coverage',search_for.coverage,q['cov'],cse).string
           if st[:flag]
             search[:text]                    << st[:search]
             search[:endnotes]                << st[:search]
           end
-          st=Dbi_search_string.new('metadata.rights',search_for.rights,q['cr'],cse).string
+          st=Dbi_search_string.new('metadata_and_text.rights_all',search_for.rights,q['cr'],cse).string
           if st[:flag]
             search[:text]                    << st[:search]
             search[:endnotes]                << st[:search]
           end
-          st=Dbi_search_string.new('metadata.comment',search_for.comment,q['co'],cse).string
+          st=Dbi_search_string.new('metadata_and_text.notes_comment',search_for.comment,q['co'],cse).string
           if st[:flag]
             search[:text]                    << st[:search]
             search[:endnotes]                << st[:search]
           end
-          st=Dbi_search_string.new('metadata.abstract',search_for.abstract,q['ab'],cse).string
+          st=Dbi_search_string.new('metadata_and_text.notes_abstract',search_for.abstract,q['ab'],cse).string
           if st[:flag]
             search[:text]                    << st[:search]
             search[:endnotes]                << st[:search]
           end
-          st=Dbi_search_string.new('metadata.filename',search_for.filename,q['fns'],cse).string
+          st=Dbi_search_string.new('metadata_and_text.filename',search_for.filename,q['fns'],cse).string
           if st[:flag]
             search[:text]                    << st[:search]
             search[:endnotes]                << st[:search]
@@ -433,31 +453,31 @@ module SiSU_CGI_sql
         def sql_offset
           @@offset
         end
-        def sql_limit
+        def sql_match_limit
           @@limit
         end
         def sql_canned_search
-          @offset_next=sql_offset.to_i + sql_limit.to_i
-          @offset_previous=sql_offset.to_i - sql_limit.to_i
+          @offset_next=sql_offset.to_i + sql_match_limit.to_i
+          @offset_previous=sql_offset.to_i - sql_match_limit.to_i
           def current
-            @@canned_search_url.to_s + '&ltd=' + sql_limit.to_s + '&off=' + sql_offset.to_s
+            @@canned_search_url.to_s + '&ltd=' + sql_match_limit.to_s + '&off=' + sql_offset.to_s
           end
           def next
-            @@canned_search_url.to_s + '&ltd=' + sql_limit.to_s + '&off=' + @offset_next.to_s
+            @@canned_search_url.to_s + '&ltd=' + sql_match_limit.to_s + '&off=' + @offset_next.to_s
           end
           def previous
             @offset_previous >= 0 \
-            ? (@@canned_search_url.to_s + '&ltd=' + sql_limit.to_s + '&off=' + @offset_previous.to_s) \
+            ? (@@canned_search_url.to_s + '&ltd=' + sql_match_limit.to_s + '&off=' + @offset_previous.to_s) \
             : ''
           end
           def start
-            @@canned_search_url.to_s + '&ltd=' + sql_limit.to_s + '&off=' + 0.to_s
+            @@canned_search_url.to_s + '&ltd=' + sql_match_limit.to_s + '&off=' + 0.to_s
           end
           self
         end
         def pre_next(beyond_limit,img)
           can=sql_canned_search
-          page=(sql_offset.to_i + sql_limit.to_i)/sql_limit.to_i
+          page=(sql_offset.to_i + sql_match_limit.to_i)/sql_match_limit.to_i
           if beyond_limit
             if page.to_s =~ /^1$/
               %{<hr /><br /><center>
@@ -630,8 +650,23 @@ module SiSU_CGI_sql
             "#{@db_name_prefix}#{@stub}"
           end
           checked_url,checked_stats,checked_searched,checked_tip,checked_case,checked_echo,checked_sql,checked_all,checked_none,checked_selected,checked_default,selected_db='','','','','','','','',''
-          if cgi['view']=~/text/; checked_index,checked_text='','checked'
-          else                    checked_index,checked_text='checked',''
+          if cgi['view']=~/text/
+            result_type={:index=>'',:text=>'checked'}
+          else
+            result_type={:index=>'checked',:text=>''}
+          end
+          @@limit=if cgi['sql_match_limit'].to_s=~/10000/
+            checked_sql_limit={:l1k=>'',:l2k=>'',:l5k=>'',:l10k=>'checked'}
+            '10000'
+          elsif cgi['sql_match_limit'].to_s=~/5000/
+            checked_sql_limit={:l1k=>'',:l2k=>'',:l5k=>'checked',:l10k=>''}
+            '5000'
+          elsif cgi['sql_match_limit'].to_s=~/2000/
+            checked_sql_limit={:l1k=>'',:l2k=>'checked',:l5k=>'',:l10k=>''}
+            '2000'
+          else
+            checked_sql_limit={:l1k=>'checked',:l2k=>'',:l5k=>'',:l10k=>''}
+            '1000'
           end
           checked_echo='checked' if cgi['echo'] =~/\S/
           checked_stats='checked' if cgi['stats'] =~/\S/
@@ -666,11 +701,13 @@ module SiSU_CGI_sql
           @search_for=Search_request.new(search_field,q) #.analyze               #% search_for
                                                                                  #% searches
           #Canned_search.new(@base,@search_for.text1,cgi)
-          if @search_for.text1=~/\S+/ or @search_for.author=~/\S+/ #and search_field =~/\S/
+          if @search_for.text1=~/\S+/ or @search_for.fulltext=~/\S+/ or @search_for.author=~/\S+/ or @search_for.topic_register=~/\S+/  #and search_field =~/\S/
             s1='s1=' + CGI.escape(@search_for.text1) if @search_for.text1=~/\S/
+            ft='&ft=' + CGI.escape(@search_for.fulltext) if @search_for.fulltext=~/\S/
             key='key=' + CGI.escape(@search_for.keywords) if @search_for.keywords=~/\S/
             ti='&ti=' + CGI.escape(@search_for.title) if @search_for.title=~/\S/
             au='&au=' + CGI.escape(@search_for.author) if @search_for.author=~/\S/
+            tr='&tr=' + CGI.escape(@search_for.topic_register) if @search_for.topic_register=~/\S/
             sj='&sj=' + CGI.escape(@search_for.subject) if @search_for.subject=~/\S/
             dsc='&dsc=' + CGI.escape(@search_for.description) if @search_for.description=~/\S/
             pb='&pb=' + CGI.escape(@search_for.publisher) if @search_for.publisher=~/\S/
@@ -692,15 +729,16 @@ module SiSU_CGI_sql
             dtv='&dtv=' + CGI.escape(@search_for.date_valid) if @search_for.date_valid=~/\S/
             fns='&fns=' + CGI.escape(@search_for.filename) if @search_for.filename=~/\S/
             @@canned_search_url=if checked_all =~/checked/
-              "#{@base}?#{s1}#{key}#{ti}#{au}#{sj}#{dsc}#{pb}#{cntr}#{dt}#{ty}#{id}#{src}#{lang}#{rel}#{cov}#{cr}#{co}#{ab}#{dtc}#{dti}#{dtm}#{dta}#{dtv}#{fns}&db=#{cgi['db']}&view=#{cgi['view']}&a=1"
-            else "#{@base}?#{s1}#{key}#{ti}#{au}#{sj}#{dsc}#{pb}#{cntr}#{dt}#{ty}#{id}#{src}#{lang}#{rel}#{cov}#{cr}#{co}#{ab}#{dtc}#{dti}#{dtm}#{dta}#{dtv}#{fns}&db=#{cgi['db']}&view=#{cgi['view']}"
+              "#{@base}?#{s1}#{ft}#{key}#{ti}#{au}#{tr}#{sj}#{dsc}#{pb}#{cntr}#{dt}#{ty}#{id}#{src}#{lang}#{rel}#{cov}#{cr}#{co}#{ab}#{dtc}#{dti}#{dtm}#{dta}#{dtv}#{fns}&db=#{cgi['db']}&view=#{cgi['view']}&a=1"
+            else "#{@base}?#{s1}#{ft}#{key}#{ti}#{au}#{tr}#{sj}#{dsc}#{pb}#{cntr}#{dt}#{ty}#{id}#{src}#{lang}#{rel}#{cov}#{cr}#{co}#{ab}#{dtc}#{dti}#{dtm}#{dta}#{dtv}#{fns}&db=#{cgi['db']}&view=#{cgi['view']}"
             end
-            @canned_base_url="#{@base}?#{s1}&db=#{cgi['db']}"
+            mod=ft=~/\S+/ ? (ft.gsub(/ft/,'s1')) : s1
+            @canned_base_url="#{@base}?#{mod}&db=#{cgi['db']}"
             if checked_case=~/\S/
-              @search[:text][1]=%{documents.clean~'#{@search_for.text1}'} #s1
+              @search[:text][1]=%{doc_objects.clean~'#{@search_for.text1}'} #s1
               @search[:endnotes][1]=%{endnotes.clean~'#{@search_for.text1}'} #s1
             else
-              @search[:text][1]=%{documents.clean~*'#{@search_for.text1}'} #s1
+              @search[:text][1]=%{doc_objects.clean~*'#{@search_for.text1}'} #s1
               @search[:endnotes][1]=%{endnotes.clean~*'#{@search_for.text1}'} #s1
             end
             canned_note='search url:'
@@ -711,12 +749,14 @@ module SiSU_CGI_sql
           if search_field =~/\S+/
             analyze_format=search_field.gsub(/\s*\n/,'; ')
           elsif checked_all =~/checked/ or checked_url =~/checked/
-            canned_search=@@canned_search_url.scan(/(?:s1|au|ti|fns)=[^&]+/)
+            canned_search=@@canned_search_url.scan(/(?:s1|ft|au|ti|fns|tr)=[^&]+/)
             af=canned_search.join('; ')
             af.gsub!(/s1=/,'text: ')
+            af.gsub!(/ft=/,'fulltxt: ')
             af.gsub!(/au=/,'author: ')
             af.gsub!(/ti=/,'title: ')
             af.gsub!(/fns=/,'filename: ')
+            af.gsub!(/tr=/,'topic_register: ')
             af.gsub!(/%2B/,' ')
             analyze_format=af
             st=af.split(/\s*;\s*/)
@@ -725,37 +765,39 @@ module SiSU_CGI_sql
           green=%{<font size="2" color="#004000">}
           canned_search_url_txt=CGI.escapeHTML(@@canned_search_url)
           the_can=%{<font size="2" color="#666666">#{canned_note} <a href="#{@@canned_search_url}">#{canned_search_url_txt}</a></font><br />}
-          p_text=p_keywords=p_title=p_author=p_subject=p_description=p_publisher=p_contributor=p_date=p_type=p_format=p_identifier=p_source=p_language=p_relation=p_coverage=p_rights=p_comment=p_abstract=p_subject=p_filename=''
+          p_text=p_fulltext=p_keywords=p_title=p_author=p_topic_register=p_subject=p_description=p_publisher=p_contributor=p_date=p_type=p_format=p_identifier=p_source=p_language=p_relation=p_coverage=p_rights=p_comment=p_abstract=p_filename=''
+          p_filename=%{filename: #{green}#{@search_for.filename}</font><br />} if @search_for.filename =~/\S+/
           p_text=%{text: #{green}#{@search_for.text1}</font><br />} if @search_for.text1 =~/\S+/
-          p_keywords=%{keywords: #{green}#{@search_for.keywords}</font><br />} if @search_for.keywords =~/\S+/
+          p_fulltext=%{fulltxt: #{green}#{@search_for.fulltext}</font><br />} if @search_for.fulltext =~/\S+/
           p_title=%{title: #{green}#{@search_for.title}</font><br />} if @search_for.title =~/\S+/
           p_author=%{author: #{green}#{@search_for.author}</font><br />} if @search_for.author =~/\S+/
-          p_subject=%{subject: #{green}#{@search_for.subject}</font><br />} if @search_for.subject =~/\S+/
-          p_description=%{description: #{green}#{@search_for.description}</font><br />} if @search_for.description =~/\S+/
-          p_publisher=%{publisher: #{green}#{@search_for.publisher}</font><br />} if @search_for.publisher =~/\S+/
           p_contributor=%{contributor: #{green}#{@search_for.contributor}</font><br />} if @search_for.contributor =~/\S+/
           p_date=%{date: #{green}#{@search_for.date}</font><br />} if @search_for.date =~/\S+/
+          p_rights=%{rights: #{green}#{@search_for.rights}</font><br />} if @search_for.rights =~/\S+/
+          p_topic_register=%{topic_register: #{green}#{@search_for.topic_register}</font><br />} if @search_for.topic_register =~/\S+/
+          p_subject=%{subject: #{green}#{@search_for.subject}</font><br />} if @search_for.subject =~/\S+/
+          p_keywords=%{keywords: #{green}#{@search_for.keywords}</font><br />} if @search_for.keywords =~/\S+/
+          p_identifier=%{identifier: #{green}#{@search_for.identifier}</font><br />} if @search_for.identifier =~/\S+/
           p_type=%{type: #{green}#{@search_for.type}</font><br />} if @search_for.type =~/\S+/
           p_format=%{format: #{green}#{@search_for.format}</font><br />} if @search_for.format =~/\S+/
-          p_identifier=%{identifier: #{green}#{@search_for.identifier}</font><br />} if @search_for.identifier =~/\S+/
-          p_source=%{source: #{green}#{@search_for.source}</font><br />} if @search_for.source =~/\S+/
-          p_language=%{language: #{green}#{@search_for.language}</font><br />} if @search_for.language =~/\S+/
           p_relation=%{relation: #{green}#{@search_for.relation}</font><br />} if @search_for.relation =~/\S+/
           p_coverage=%{coverage: #{green}#{@search_for.coverage}</font><br />} if @search_for.coverage =~/\S+/
-          p_rights=%{rights: #{green}#{@search_for.rights}</font><br />} if @search_for.rights =~/\S+/
-          p_comment=%{comment: #{green}#{@search_for.comment}</font><br />} if @search_for.comment =~/\S+/
+          p_description=%{description: #{green}#{@search_for.description}</font><br />} if @search_for.description =~/\S+/
           p_abstract=%{abstract: #{green}#{@search_for.abstract}</font><br />} if @search_for.abstract =~/\S+/
-          p_filename=%{filename: #{green}#{@search_for.filename}</font><br />} if @search_for.filename =~/\S+/
+          p_comment=%{comment: #{green}#{@search_for.comment}</font><br />} if @search_for.comment =~/\S+/
+          p_publisher=%{publisher: #{green}#{@search_for.publisher}</font><br />} if @search_for.publisher =~/\S+/
+          p_source=%{source: #{green}#{@search_for.source}</font><br />} if @search_for.source =~/\S+/
+          p_language=%{language: #{green}#{@search_for.language}</font><br />} if @search_for.language =~/\S+/
           search_note=<<-WOK
       <font size="2" color="#666666">
       <b>database:</b> #{green}#{@db}</font>; <b>selected view:</b> #{green}#{cgi['view']}</font>
       <b>search string:</b> "#{green}#{analyze_format}</font>"<br />
-      #{p_text} #{p_keywords} #{p_title} #{p_author} #{p_subject} #{p_description} #{p_publisher} #{p_contributor} #{p_date} #{p_type} #{p_format} #{p_identifier} #{p_source} #{p_language} #{p_relation} #{p_coverage} #{p_rights} #{p_comment} #{p_abstract} #{p_filename}
+      #{p_text} #{p_fulltext} #{p_keywords} #{p_title} #{p_author} #{p_topic_register} #{p_subject} #{p_description} #{p_publisher} #{p_contributor} #{p_date} #{p_type} #{p_format} #{p_identifier} #{p_source} #{p_language} #{p_relation} #{p_coverage} #{p_rights} #{p_comment} #{p_abstract} #{p_filename}
       </font>
       WOK
         #eg = %{canned search e.g.:<br /> <a href="#{url}">#{url}</a><br />find: #{analyze}<br />database: #{database}}
         #dbi_canning
-        @header=Form.new(@base,search_field,selected_db,checked_index,checked_text,checked_tip,checked_stats,checked_searched,checked_url,checked_case,checked_echo,checked_sql,checked_all,checked_none,checked_selected,checked_default,search_note,the_can).submission_form #% form
+        @header=Form.new(@base,search_field,selected_db,result_type,checked_sql_limit,checked_tip,checked_stats,checked_searched,checked_url,checked_case,checked_echo,checked_sql,checked_all,checked_none,checked_selected,checked_default,search_note,the_can).submission_form #% form
         unless q['s1'] =~/\S/ or q['au'] =~/\S/ or @search[:text][1] =~/\S/
           print "Content-type: text/html\n\n"
           puts (@header+@tail)
@@ -765,10 +807,10 @@ module SiSU_CGI_sql
           else 'Unavailable'
           end
           if checked_case=~/\S/
-            @search[:text]<<%{documents.clean~'#{CGI.unescape(s1)}'}
+            @search[:text]<<%{doc_objects.clean~'#{CGI.unescape(s1)}'}
             @search[:endnotes]<<%{endnotes.clean~'#{CGI.unescape(s1)}'}
           else
-            @search[:text]<<%{documents.clean~*'#{CGI.unescape(s1)}'}
+            @search[:text]<<%{doc_objects.clean~*'#{CGI.unescape(s1)}'}
             @search[:endnotes]<<%{endnotes.clean~*'#{CGI.unescape(s1)}'}
           end
           #dbi_request
@@ -802,13 +844,11 @@ module SiSU_CGI_sql
             end
           #metadata_found_body
             if c['tid'].to_i != oldtid.to_i
-              ti=if c['subtitle'] =~/\S+/; "#{c['title']} - #{c['subtitle']}"
-              else c['title']
-              end
+              ti=c['title']
               can_txt_srch=if cgi['view']=~/index/; %{<a href="#{@canned_base_url}&fns=#{c['filename']}&view=text"><img border="0" width="24" height="16" src="#{@image_src}/b_search.png" alt="search"></a>&nbsp;}
               else %{<a href="#{@canned_base_url}&fns=#{c['filename']}&view=index"><img border="0" width="24" height="16" src="#{@image_src}/b_search.png" alt="search"></a>&nbsp;}
               end
-              title=%{<span style="background-color: #{@color_heading}"><a href="#{@hosturl_files}/#{@stub}/#{location}/toc#{lang}.html"><img border="0" width="15" height="18" src="#{@image_src}/b_toc.png" alt="toc html">&nbsp;#{ti}</a></span> by #{c['creator']} #{can_txt_srch}<a href="#{@hosturl_files}/#{@stub}/#{location}/toc#{lang}.html"><img border="0" width="15" height="18" src="#{@image_src}/b_toc.png" alt="toc html"></a>&nbsp;<a href="#{@hosturl_files}/#{@stub}/epub/#{location}#{lang}.epub"><img border="0" width="15" height="15" src="#{@image_src}/b_epub.png" alt="epub"></a>&nbsp;<a href="#{@hosturl_files}/#{@stub}/#{location}/portrait#{lang}.pdf"><img border="0" width="15" height="18" src="#{@image_src}/b_pdf.png" alt="pdf portrait"></a>&nbsp;<a href="#{@hosturl_files}/#{@stub}/#{location}/landscape#{lang}.pdf"><img border="0" width="18" height="15" src="#{@image_src}/b_pdf.png" alt="pdf landscape"></a></a>&nbsp;<a href="#{@hosturl_files}/#{@stub}/#{location}/opendocument#{lang}.odt"><img border="0" width="15" height="15" src="#{@image_src}/b_odf.png" alt="odf"></a>&nbsp;<a href="#{@hosturl_files}/#{@stub}/#{location}/sisu_manifest#{lang}.html"><img border="0" width="15" height="15" src="#{@image_src}/b_info.png" alt="manifest"></a><br />}  if file_suffix=~/s/ #hmm watch file_suffix
+              title=%{<span style="background-color: #{@color_heading}"><a href="#{@hosturl_files}/#{@stub}/#{location}/toc#{lang}.html"><img border="0" width="15" height="18" src="#{@image_src}/b_toc.png" alt="toc html">&nbsp;#{ti}</a></span> by #{c['creator_author']} #{can_txt_srch}<a href="#{@hosturl_files}/#{@stub}/#{location}/toc#{lang}.html"><img border="0" width="15" height="18" src="#{@image_src}/b_toc.png" alt="toc html"></a>&nbsp;<a href="#{@hosturl_files}/#{@stub}/epub/#{location}#{lang}.epub"><img border="0" width="15" height="15" src="#{@image_src}/b_epub.png" alt="epub"></a>&nbsp;<a href="#{@hosturl_files}/#{@stub}/#{location}/portrait#{lang}.pdf"><img border="0" width="15" height="18" src="#{@image_src}/b_pdf.png" alt="pdf portrait"></a>&nbsp;<a href="#{@hosturl_files}/#{@stub}/#{location}/landscape#{lang}.pdf"><img border="0" width="18" height="15" src="#{@image_src}/b_pdf.png" alt="pdf landscape"></a></a>&nbsp;<a href="#{@hosturl_files}/#{@stub}/#{location}/opendocument#{lang}.odt"><img border="0" width="15" height="15" src="#{@image_src}/b_odf.png" alt="odf"></a>&nbsp;<a href="#{@hosturl_files}/#{@stub}/#{location}/sisu_manifest#{lang}.html"><img border="0" width="15" height="15" src="#{@image_src}/b_info.png" alt="manifest"></a><br />}  if file_suffix=~/s/ #hmm watch file_suffix
               if @text_search_flag; title='<br /><hr>'+title
               else                  title='<br />'+title
               end
@@ -875,7 +915,7 @@ module SiSU_CGI_sql
             end
             @counters_txt=if @counter_txt_doc > 0
               if checked_stats =~/\S/
-                @@lt_t=if @counter_txt_ocn==dbi_statement.sql_limit.to_i
+                @@lt_t=if @counter_txt_ocn==dbi_statement.sql_match_limit.to_i
                   over_limit='over the limit set of'
                   over_this_number='more than'
                   true
@@ -904,13 +944,11 @@ module SiSU_CGI_sql
             #metadata_found_endnotes
             if @text_search_flag
               if e['metadata_tid'].to_i != oldtid.to_i
-                ti=if e['subtitle'] =~/\S+/; "#{e['title']} - #{e['subtitle']}"
-                else e['title']
-                end
+                ti=e['title']
                 can_txt_srch=if cgi['view']=~/index/; %{<a href="#{@canned_base_url}&fns=#{e['filename']}&view=text"><img border="0" width="24" height="16" src="#{@image_src}/b_search.png" alt="search"></a>&nbsp;}
                 else %{<a href="#{@canned_base_url}&fns=#{e['filename']}&view=index"><img border="0" width="24" height="16" src="#{@image_src}/b_search.png" alt="search"></a>&nbsp;}
                 end
-                title=%{<br /><hr><span style="background-color: #{@color_heading}"><a href="#{@hosturl_files}/#{@stub}/#{location}/toc#{lang}.html"><img border="0" width="15" height="18" src="#{@image_src}/b_toc.png" alt="toc html">&nbsp;#{ti}</a></span> by #{e['creator']} #{can_txt_srch}<a href="#{@hosturl_files}/#{@stub}/#{location}/toc#{lang}.html"><img border="0" width="15" height="18" src="#{@image_src}/b_toc.png" alt="toc html"></a>&nbsp;<a href="#{@hosturl_files}/#{@stub}/epub/#{location}#{lang}.epub"><img border="0" width="15" height="15" src="#{@image_src}/b_epub.png" alt="epub"></a>&nbsp;<a href="#{@hosturl_files}/#{@stub}/#{location}/portrait.pdf"><img border="0" width="15" height="18" src="#{@image_src}/b_pdf.png" alt="pdf portrait"></a>&nbsp;<a href="#{@hosturl_files}/#{@stub}/#{location}/landscape.pdf"><img border="0" width="18" height="15" src="#{@image_src}/b_pdf.png" alt="pdf landscape"></a>&nbsp;<a href="#{@hosturl_files}/#{@stub}/#{location}/opendocument#{lang}.odt"><img border="0" width="15" height="15" src="#{@image_src}/b_odf.png" alt="odf"></a>&nbsp;<a href="#{@hosturl_files}/#{@stub}/#{location}/sisu_manifest#{lang}.html"><img border="0" width="15" height="15" src="#{@image_src}/b_info.png" alt="manifest"></a><br />}  if file_suffix=~/s/
+                title=%{<br /><hr><span style="background-color: #{@color_heading}"><a href="#{@hosturl_files}/#{@stub}/#{location}/toc#{lang}.html"><img border="0" width="15" height="18" src="#{@image_src}/b_toc.png" alt="toc html">&nbsp;#{ti}</a></span> by #{e['creator_author']} #{can_txt_srch}<a href="#{@hosturl_files}/#{@stub}/#{location}/toc#{lang}.html"><img border="0" width="15" height="18" src="#{@image_src}/b_toc.png" alt="toc html"></a>&nbsp;<a href="#{@hosturl_files}/#{@stub}/epub/#{location}#{lang}.epub"><img border="0" width="15" height="15" src="#{@image_src}/b_epub.png" alt="epub"></a>&nbsp;<a href="#{@hosturl_files}/#{@stub}/#{location}/portrait.pdf"><img border="0" width="15" height="18" src="#{@image_src}/b_pdf.png" alt="pdf portrait"></a>&nbsp;<a href="#{@hosturl_files}/#{@stub}/#{location}/landscape.pdf"><img border="0" width="18" height="15" src="#{@image_src}/b_pdf.png" alt="pdf landscape"></a>&nbsp;<a href="#{@hosturl_files}/#{@stub}/#{location}/opendocument#{lang}.odt"><img border="0" width="15" height="15" src="#{@image_src}/b_odf.png" alt="odf"></a>&nbsp;<a href="#{@hosturl_files}/#{@stub}/#{location}/sisu_manifest#{lang}.html"><img border="0" width="15" height="15" src="#{@image_src}/b_info.png" alt="manifest"></a><br />}  if file_suffix=~/s/
                 @counter_endn_doc+=1
                 oldtid=e['metadata_tid'].to_i
               else title = ''
@@ -929,7 +967,7 @@ module SiSU_CGI_sql
               end
               @counters_endn=if @counter_endn_doc > 0
                 if checked_stats =~/\S/
-                  @@lt_e=if @counter_endn_ocn==dbi_statement.sql_limit.to_i
+                  @@lt_e=if @counter_endn_ocn==dbi_statement.sql_match_limit.to_i
                     over_limit='over the limit set of'
                     over_this_number='more than'
                     true
@@ -946,14 +984,14 @@ module SiSU_CGI_sql
               end
             end
             offset=dbi_statement.sql_offset.to_s
-            limit=dbi_statement.sql_limit.to_s
+            limit=dbi_statement.sql_match_limit.to_s
             @@lt_t ||=false; @@lt_e ||=false
             canned=if (@@lt_t or @@lt_e)
               dbi_statement.pre_next(true,@image_src).to_s
             else
               dbi_statement.pre_next(false,@image_src).to_s
             end
-            limit=dbi_statement.sql_limit.to_s
+            limit=dbi_statement.sql_match_limit.to_s
             cgi.out{@header + @counters_txt + @counters_endn + @body_main.join + @endnotes.join + canned + @tail} #% print cgi_output_header+counters+body+endnotes
           end
         rescue Exception => e
diff --git a/lib/sisu/v2/cgi_sqlite.rb b/lib/sisu/v2/cgi_sqlite.rb
index 7af99b9d..947cc80a 100644
--- a/lib/sisu/v2/cgi_sqlite.rb
+++ b/lib/sisu/v2/cgi_sqlite.rb
@@ -167,7 +167,7 @@ module  SiSU_CGI_sqlite
           @search_text,@search_endnotes=[],[]
           search[:text].each{|x| @search_text << "#{x} AND " }
           @search_text=@search_text.join.gsub!(/AND\s+$/,'')
-          @search_text.gsub!(/(documents\.clean\s+LIKE\s+\(\s*'%[^']+%'\s*\)\s+(?:(?:AND|OR)\s+documents\.clean\s+LIKE\s+\(\s*'%[^']+%'\s*\))+)/,'(\1)')
+          @search_text.gsub!(/(doc_objects\.clean\s+LIKE\s+\(\s*'%[^']+%'\s*\)\s+(?:(?:AND|OR)\s+doc_objects\.clean\s+LIKE\s+\(\s*'%[^']+%'\s*\))+)/,'(\1)')
           search[:endnotes].each{|x| @search_endnotes << "#{x} AND " }
           @search_endnotes=@search_endnotes.join.gsub!(/AND\s+$/,'')
           @search_text.gsub!(/(endnotes\.clean\s+LIKE\s+\(\s*'%[^']+%'\s*\)\s+(?:(?:AND|OR)\s+endnotes\.clean\s+LIKE\s+\(\s*'%[^']+%'\s*\))+)/,'(\1)')
@@ -179,7 +179,7 @@ module  SiSU_CGI_sqlite
         def sql_select_body
           limit ||=@@limit
           offset ||=@@offset
-          @sql_statement[:body]=%{SELECT metadata.title, metadata.subtitle, metadata.creator, metadata.filename, metadata.suffix, documents.body, documents.seg, documents.ocn, metadata.tid FROM documents, metadata WHERE #{@search_text} AND documents.metadata_tid = metadata.tid ORDER BY metadata.title, metadata.filename, documents.ocn}
+          @sql_statement[:body]=%{SELECT metadata_and_text.title, metadata_and_text.creator_author, metadata_and_text.filename, metadata_and_text.notes_suffix, doc_objects.body, doc_objects.seg, doc_objects.ocn, metadata_and_text.tid FROM doc_objects, metadata_and_text WHERE #{@search_text} AND doc_objects.metadata_tid = metadata_and_text.tid ORDER BY metadata_and_text.title, metadata_and_text.filename, doc_objects.ocn}
           @sql_statement[:range]=%{LIMIT #{limit} OFFSET #{offset} ;}
           select=@sql_statement[:body] + ' ' + @sql_statement[:range]
           select
@@ -187,7 +187,7 @@ module  SiSU_CGI_sqlite
         def sql_select_endnotes
           limit ||=@@limit
           offset ||=@@offset
-          @sql_statement[:endnotes]= %{SELECT metadata.title, metadata.subtitle, metadata.creator, metadata.filename, endnotes.body, endnotes.nr, endnotes.ocn, endnotes.metadata_tid FROM metadata, endnotes WHERE #{@search_endnotes} AND metadata.tid = endnotes.metadata_tid ORDER BY metadata.title, metadata.filename, endnotes.nr}
+          @sql_statement[:endnotes]=%{SELECT metadata_and_text.title, metadata_and_text.creator_author, metadata_and_text.filename, endnotes.body, endnotes.nr, endnotes.ocn, endnotes.metadata_tid FROM metadata_and_text, endnotes WHERE #{@search_endnotes} AND metadata_and_text.tid = endnotes.metadata_tid ORDER BY metadata_and_text.title, metadata_and_text.filename, endnotes.nr}
           @sql_statement[:range]=%{LIMIT #{limit} OFFSET #{offset} ;}
           select=@sql_statement[:endnotes] + ' ' + @sql_statement[:range]
           select