#!/usr/bin/env ruby#pgsearch.rb#To make querying a postgres database easierrequire 'postgres'class Pgsearch Space = 25 #spacing/formatting Phost = 'localhost' #machine to connect to Port = 5432 #port for database Pdb = 'testdb' #database to connect to Puser='' #postgres user Pw='' #postgres password def initialize #(host, port, options, tty, dbname, login, passwd) @conn=PGconn.connect(Phost,Port,'','',Pdb,Puser,Pw) @res="" @shownames=false end def list @res=@conn.exec("select datname from pg_database;") output end def desc(tab,shownames) @shownames=shownames @res=@conn.exec("select table_name,column_name,data_type from information_schema.columns where table_name like '%#{tab}%';") output end def show(doselect, shownames, where, argarray) @shownames=shownames a,b = argarray[0],argarray[1] c=argarray[2] if where if !doselect @res=@conn.exec("select column_name,table_name,data_type from information_schema.columns where table_name like '%#{b}%'and column_name like '%#{a}%';") else a='*' if a=='all' if where @res=@conn.exec("select #{a} from #{b} where #{c};") else #@res=@conn.exec("select #{a} from information_schema.columns where table_name #like '%#{b}%';") @res=@conn.exec("select #{a} from #{b};") end end output end def output (@res.fields.each {|field| print field; (Space-field.length).times{print " "}};puts "","") if @shownames #p res.fields if shownames @res.each do |row| row.each do |column| if column!=nil print column (Space-column.length).times{print " "} end end puts end puts "","#{@res.rows.length} rows" end def help helpstr=[] helpstr<< 'Usage:' << ' ./pgsearch.rb PATTERN' << ' ./pgsearch.rb [OPTIONS] PATTERN [TABLE] [CONDITION]' << ''<< 'By default, attempts to search all tables for PATTERN' << 'Wildcard given by \'\'; output can be easily piped to *nix commands' << 'Options:' << ' -d TABLE describes a table' << ' -l list available databases' << ' -n show column names' << ' -s PATTERN TABLE select PATTERN from TABLE (doesnt support partial names)' << ' -w \'CONDITION\' specify a \'where\' condition' << ' --help shows this help file' << 'Examples:' << ' ./pgsearch.rb -nd ord' << ' ./pgsearch.rb ship' << ' ./pgsearch.rb ship ord' << ' ./pgsearch.rb ship | grep int' << ' ./pgsearch.rb \'\' orders | grep int' << ' ./pgsearch.rb -sn id,patient orders -w \'id>40\'' helpstr.each {|e| puts e+="\n"} end def close @conn.close endend### Controller ####o=Pgsearch.newif ARGV.include? '--help' or ARGV.length==0 o.help exit 0 enddoselect=falseshownames=falsewhere=falsedescribe=falsedolist=falsepargs=[]ARGV.each {|arg| #switches doselect=true if !doselect && arg =~ /\-\w*s/ shownames=true if !shownames && arg =~ /\-\w*n/ where=true if !where && arg =~ /\-\w*w/ describe=true if !describe && arg =~ /\-\w*d/ dolist=true if !dolist && arg =~ /\-\w*l/ #arguments pargs<<arg if ! (arg =~ /\-\w*[dlnsw]/) } if dolist o.list o.close exit 0 elsif describe o.desc(pargs[0],shownames) o.close exit 0 endo.show(doselect,shownames,where,pargs)o.close