Class: SiSU_CGI_SQLite::SearchSQLite

Inherits:
CGI_Common
  • Object
show all
Defined in:
lib/sisu/cgi_sqlite.rb

Instance Method Summary collapse

Constructor Details

#initialize(opt, webserv) ⇒ SearchSQLite

Returns a new instance of SearchSQLite.



60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
 
# File 'lib/sisu/cgi_sqlite.rb', line 60

def initialize(opt,webserv)
  @opt,@webserv=opt,webserv
  @cX=SiSU_Screen::Ansi.new(opt.act[:color_state][:set]).cX
  @env=SiSU_Env::InfoEnv.new('',opt)
  @image_src="#{@env.url.webserv_cgi(opt)}/_sisu/image_sys"
  @name_of={}
  @name_of[:output_dir_structure]=if opt.dir_structure_by.to_s =~/(?:language|filetype|filename)/
    opt.dir_structure_by.to_s
  else 'language'
  end
  @name_of[:lingual]=if opt.lingual.to_s =~/(?:mono|multi)/
    opt.lingual.to_s
  else 'multi'
  end
  @name_of[:host_url_cgi]=%q{http://#{ENV['HTTP_HOST']}#{ENV['PATH_INFO']}}
  @name_of[:host_url_docs]=%q{http://#{ENV['HTTP_HOST']}}
  @name_of[:cgi_script]=%q{#{ENV['SCRIPT_NAME']}}
  @image_src=%q{http://#{ENV['HTTP_HOST']}/_sisu/image_sys}
  @common=SiSU_CGI_SQL::CGI_Common.new(@webserv,opt,@image_src,@env)
  @cgi_file_name=@env.url.cgi_sample_search_form_name(opt)
  @name_of_sqlite_db_file='sisu_sqlite.db'
end

Instance Method Details

#dbi_connect ⇒ Object 



234
235
236
237
238
239
 
# File 'lib/sisu/cgi_sqlite.rb', line 234

def dbi_connect
  <<-'WOK_SQL'
      @conn=SQLite3::Database.new(db_sqlite)
      @conn.results_as_hash=true
  WOK_SQL
end

#header0 ⇒ Object 



156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
 
# File 'lib/sisu/cgi_sqlite.rb', line 156

def header0
  <<-WOK_SQL
#!/usr/bin/env ruby
=begin
#{about}
 * Description: generates naive cgi search form for search of sisu database (sqlite)
#{gpl}
=end
  begin
    require 'cgi'
    require 'fcgi'
    require 'sqlite3'
  rescue LoadError
    puts 'cgi, fcgi or sqlite3 NOT FOUND (LoadError)'
  end
  @stub_default='sisu_sqlite'
  @image_src="#{@image_src}"
  @hosturl_cgi="#{@name_of[:host_url_cgi]}"
  @hosturl_files="#{@name_of[:host_url_docs]}"
  @output_dir_structure_by='#{@name_of[:output_dir_structure]}'
  @lingual='#{@name_of[:lingual]}'
  @db_name_prefix='#{Db[:name_prefix]}'
  @base="#{@name_of[:host_url_cgi]}#{@name_of[:cgi_script]}"
  WOK_SQL
end

#search_query1 ⇒ Object 



207
208
209
210
211
212
213
214
 
# File 'lib/sisu/cgi_sqlite.rb', line 207

def search_query1
  <<-'WOK_SQL'
      @search_text=''
      @search_text=search[:text].flatten.join(' AND ')
      @search_text=@search_text.gsub(/(doc_objects\.clean\s+LIKE\s+\(\s*'%[^']+%'\s*\)\s+(?:(?:AND|OR)\s+doc_objects\.clean\s+LIKE\s+\(\s*'%[^']+%'\s*\))+)/,'(\1)')
    end
  WOK_SQL
end

#search_query2 ⇒ Object 



215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
 
# File 'lib/sisu/cgi_sqlite.rb', line 215

def search_query2
  <<-'WOK_SQL'
    def sql_select_body
      limit ||=@@limit
      offset ||=@@offset
      @sql_statement[:body]=%{SELECT metadata_and_text.title, metadata_and_text.creator_author, metadata_and_text.src_filename, metadata_and_text.language_document_char, 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.language_document_char, metadata_and_text.title, metadata_and_text.src_filename, doc_objects.ocn}
      @sql_statement[:range]=%{LIMIT #{limit} OFFSET #{offset} ;}
      select=@sql_statement[:body] + ' ' + @sql_statement[:range]
      select
    end
    def sql_select_body_format
      %{<font color="#666666" size="2">#{sql_select_body}</font>}
    end
    def contents
      @conn.execute(sql_select_body)
    end
  end
  WOK_SQL
end

#search_statement ⇒ Object 



181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
 
# File 'lib/sisu/cgi_sqlite.rb', line 181

def search_statement
  <<-'WOK_SQL'
  class DBI_SearchString
    def initialize(l,t,q,cse=false)
      @l,@t,@q=l,t,q
    end
    def string
      search={ search: [], flag: false }
      if @t =~/\S+/ or @q =~/\S+/
        if @t =~/\S+/    then unescaped_search=CGI.unescape(@t)
        elsif @q =~/\S+/ then unescaped_search=CGI.unescape(@q)
        end
        search_construct=[]
        unescaped_search=unescaped_search.gsub(/\s*(AND|OR)\s*/,"%' \) \\1 #{@l} LIKE \( '%").
          gsub(/(.+)/,"#{@l} LIKE \( '%\\1%' \)")
        search_construct << unescaped_search
        search_construct=search_construct.join(' ')
        search[:search]                    << search_construct
        search[:flag]=true
        search
      end
      search
    end
  end
  WOK_SQL
end

#sqlite ⇒ Object 



82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
 
# File 'lib/sisu/cgi_sqlite.rb', line 82

def sqlite
  serve=[]
  Dir.foreach(@env.path.webserv) do |x|
    if x !~/^\./ \
    and FileTest.directory?("#{@env.path.webserv}/#{x}")
      if FileTest.file?("#{@env.path.webserv}/#{x}/#{@name_of_sqlite_db_file}")
        serve << x unless x =~/^_\S+/
      end
    end
  end
  serve=serve.sort
  f1,f2,f3='','',''
  serve.each do |x|
    f1 << %{              <option value="#{Db[:name_prefix]}#{x}">#{x}</option>\n}
  end
  f2 <<  %{          selected_db=case cgi['db']\n}
  serve.each do |x|
    f2 << %{           when /#{Db[:name_prefix]}#{x}/ then '<option value="#{Db[:name_prefix]}#{x}">#{x}</option>'\n}
  end
  f2 << "          end\n"
  f3 << %{          db_name='#{@name_of_sqlite_db_file}'\n}
  f3 << %{          db_sqlite=case cgi['db']\n}
  serve.each do |x|
    f3 << %{          when /#{Db[:name_prefix]}#{x}/ then "#{@env.path.webserv}/#{x}/\#{db_name}"\n}
  end
  f3 << %{           else  "#{@env.path.webserv}/#{serve[0]}/\#{db_name}"\n          end\n}
  if FileTest.writable?('.')
    output=File.open(@cgi_file_name,'w')
    output << header0 << header1 << header_desc << header2 << f1 << buttons1 << buttons2 << search_request << search_statement << search_statement_common << search_query1 << @common.pages << search_query2 << @common.tail << @common.main1 << f2 << f3 << dbi_connect << @common.main2 << @common.dir_structure << @common.main3
    puts <<-WOK
        generated sample search form: #{@cX.green}#{@cgi_file_name}#{@cX.off}
        default database name:     #{@cX.green}#{Db[:name_prefix]}#{@env.path.base_markup_dir_stub}#{@cX.off} (#{@env.path.base_markup_dir_stub})
        cgi & db host on:          #{@cX.blue}#{@env.url.webserv_base_cgi(@opt)}#{@cX.off}
          to modify use:           #{@cX.brown}sisu --db-sqlite --webserv-search='#{@env.url.webserv_base_cgi(@opt)}'#{@cX.off}
        sisu output on:            #{@cX.blue}#{@env.url.webserv_files_from_db(@opt)}#{@cX.off}
          to modify use:           #{@cX.brown}sisu --db-sqlite --webserv-output='#{@env.url.webserv_files_from_db(@opt)}'#{@cX.off}
        cgi search form link name: #{@cX.green}#{@env.url.cgi_sample_search_form_name(@opt)}#{@cX.off}
          to modify use:           #{@cX.brown}sisu --db-sqlite --cgi-search-form-name='#{@env.url.cgi_sample_search_form_name(@opt)}'#{@cX.off}
        #{@cX.fuchsia}(settings priority: command line; sisurc.yml; else defaults)#{@cX.off}

        #{@cX.fuchsia}NOTE it is first necessary to create the database and tables and populate it#{@cX.off}

        sisu --sqlite --dropall                          # removes existing postgresql db & tables
        sisu --sqlite --createall -v                     # creates postgresql db & tables
        sisu --sqlite --update -v *.sst  *.ssm           # populate the db
        sisu --sample-search-form --sqlite               # creates the postgresql search form
                                                         # this should be done after creating the db
                                                         # to be searched
        sisu --webrick &                                 # starts ruby webrick web server

        # if necessary make the directory '/usr/lib/cgi-bin'
        # here we copy the postgresql search form to cgi-bin
        # (copy #{@cgi_file_name} to your cgi directory)
        # set file permissions to 755
    WOK
    a=case @webserv
    when /pwd/ then ''
    else <<-WOK

          sudo cp -vi #{Dir.pwd}/#{@cgi_file_name} /usr/lib/cgi-bin/.; \\
          sudo chmod -v 755  /usr/lib/cgi-bin/#{@cgi_file_name}
      WOK
    end
    b='(to create and populate sisu sqlite database see "man sisu" and in particular the -d flag)'
    SiSU_Screen::Ansi.new(@opt.act[:color_state][:set],a,b).warn
    a=<<-WOK

          #{@env.webserv_base_cgi(@opt)}/cgi-bin/#{@cgi_file_name}

    WOK
    SiSU_Screen::Ansi.new(@opt.act[:color_state][:set],a).print_blue
  else puts "failed in attempt to write #{@cgi_file_name} to present directory, is directory writable?"
  end
end