Class: SiSU_CGI_PgSQL::SearchPgSQL

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

Instance Method Summary collapse

Constructor Details

#initialize(opt, webserv) ⇒ SearchPgSQL

Returns a new instance of SearchPgSQL.



62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
 
# File 'lib/sisu/cgi_pgsql.rb', line 62

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)
  @sys=SiSU_Env::SystemCall.new
  @db=SiSU_Env::InfoDb.new
  get_init=SiSU_Env::GetInit.new
  @rc=get_init.sisu_yaml.rc
  @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[:db]=if defined? @rc['search'] \
  and defined? @rc['search']['sisu'] \
  and defined? @rc['search']['sisu']['action'] \
  and @rc['search']['sisu']['action'] =~/https?:\/\/\S+?\.cgi/ \
  and defined? @rc['search']['sisu']['db'] \
  and @rc['search']['sisu']['db'] =~/\S+/
    @rc['search']['sisu']['db']
  else
    @env.path.base_markup_dir_stub #'sisu' #breaks if not present
  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']}}
  @name_of[:user]=@db.psql.user(opt)
  @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)
end

Instance Method Details

#buttons1_pgsql ⇒ Object 



258
259
260
261
262
 
# File 'lib/sisu/cgi_pgsql.rb', line 258

def buttons1_pgsql
  <<-'WOK_SQL'
    <input type="checkbox" name="casesense" #{@checked_case}> case sensitive
  WOK_SQL
end

#dbi_connect ⇒ Object 



263
264
265
266
267
 
# File 'lib/sisu/cgi_pgsql.rb', line 263

def dbi_connect
  <<-'WOK_SQL'
      @conn=PG::Connection.open(dbname: @db, port: @port, user: @user)
  WOK_SQL
end

#header0 ⇒ Object 



173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
 
# File 'lib/sisu/cgi_pgsql.rb', line 173

def header0
  <<-WOK_SQL
#!/usr/bin/env ruby
=begin
#{about}
 * Description: generates naive cgi search form for search of sisu database (pgsql)
#{gpl}
=end
  begin
    require 'cgi'
    require 'fcgi'
    require 'pg'
  rescue LoadError
    puts 'cgi, fcgi or pg NOT FOUND (LoadError)'
  end
  @stub_default='#{@name_of[:db]}'
  @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]}'
  @port='#{@db.psql.port}'
  @db_name_prefix='#{Db[:name_prefix]}'
  @user='#{@name_of[:user]}'  # check user name for access to pg database: e.g. www-data or '#{@env.user}'
  @base="#{@name_of[:host_url_cgi]}#{@name_of[:cgi_script]}"
  WOK_SQL
end

#pgsql ⇒ Object 



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
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
 
# File 'lib/sisu/cgi_pgsql.rb', line 97

def pgsql
  serve=[]
  if @sys.psql
    available_db_table=`psql --list`                            # system call requires psql
    available_db=available_db_table.scan(/(#{Db[:name_prefix]}\S+)/) if not available_db_table.nil?
    if available_db \
    and available_db.is_a?(Array)
      available_db.flatten.each do |x|
        serve << x.gsub(/#{Db[:name_prefix]}(\S+)/,'\1')
      end
    else STDERR.puts "WARNING: no postgresql database available, (have you created one?)"
    end
    serve=serve.sort
    f1,f2='',''
    serve.each do |x|
      f1 << %{              <option value="#{Db[:name_prefix]}#{x}">#{x}</option>\n} unless x =~/apache|sisu\/image/ #check
    end
  end
  f2 << %q{          selected_db=%{<option value="#{@db_name_prefix}#{@stub}">#{@stub}</option>}} + "\n"
  if FileTest.writable?('.')
    output=File.open(@cgi_file_name,'w')
    output << header0 << header1 << header_desc << header2 << f1 << buttons1 << buttons1_pgsql << buttons2 << search_request << search_statement << search_statement_common << search_query1 << @common.pages << search_query2 << @common.tail << @common.main1 << f2 << 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]}#{@name_of[:db]}#{@cX.off} (#{@name_of[:db]})
        db user:                   #{@cX.green}#{@name_of[:user]}#{@cX.off}
          to modify use:           #{@cX.brown}sisu --db-pg --db-user='#{@name_of[:user]}'#{@cX.off}

        #{@cX.fuchsia}BASED ON ALREADY EXISTING databases#{@cX.off} (default database name: #{@db.psql.db})
        NOTE it is first necessary to createdb,
        use sisu to create the tables & populate the postgresql db

        the database to be used for this directory (#{@db.psql.db})
        will have to be created manually if it does not exist:
        using postgresql tools directly (the following may work):
          (i) if you are not yet a postgresql user,
            #{@cX.brown}sudo su postgres
              createuser -d -a #{@env.user}
            exit#{@cX.off}
          (ii) create the database:
            #{@cX.brown}createdb #{@db.psql.db}#{@cX.off}
        [for a list of existing databases try 'psql --list']"

        you can use sisu to create the database tables and populate the database with documents

        sisu --pg --dropall                              # removes existing postgresql db & tables
        sisu --pg --createall -v                         # creates postgresql db & tables
        sisu --pg --update -v *.sst  *.ssm               # populate the db
        sisu --sample-search-form --db-pg                # 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
    SiSU_Screen::Ansi.new(@opt.act[:color_state][:set],a).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
    a="\n\t(to create and populate postgresql database see 'man sisu' and in particular the --pg option)\n\t[the database to be used for this directory (#{@db.psql.db}) will have to be created manually if it does not exist,\n\tusing postgresql tools directly: 'createdb #{@db.psql.db}' for a list of existing databases try 'psql --list']"
    SiSU_Screen::Ansi.new(@opt.act[:color_state][:set],a).txt_grey
  else puts 'failed in attempt to write #{@cgi_file_name} to present directory,  is directory writable?'
  end
end

#search_query1 ⇒ Object 



231
232
233
234
235
236
237
238
 
# File 'lib/sisu/cgi_pgsql.rb', line 231

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

#search_query2 ⇒ Object 



239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
 
# File 'lib/sisu/cgi_pgsql.rb', line 239

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.exec(sql_select_body)
    end
  end
  WOK_SQL
end

#search_statement ⇒ Object 



200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
 
# File 'lib/sisu/cgi_pgsql.rb', line 200

def search_statement
  <<-'WOK_SQL'
  class DBI_SearchString
    def initialize(l,t,q,cse=false)
      @l,@t,@q,@c=l,t,q,cse
    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=if @c
          unescaped_search.gsub(/\s*(AND|OR)\s*/,"' \) \\1 #{@l}~\( '").
            gsub(/(.+)/,"#{@l}~\( '\\1' \)")
        else
          unescaped_search.gsub(/\s*(AND|OR)\s*/,"' \) \\1 #{@l}~*\( '").
            gsub(/(.+)/,"#{@l}~*\( '\\1' \)")
        end
        search_construct << unescaped_search
        search_construct=search_construct.join(' ')
        search[:search]                    << search_construct
        search[:flag]=true
        search
      end
      search
    end
  end
  WOK_SQL
end