Skip to content

Instantly share code, notes, and snippets.

@mscottford
Created December 17, 2009 19:59
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save mscottford/258993 to your computer and use it in GitHub Desktop.
require 'oci8'
require 'ostruct'
require 'optparse'
class DatabaseUtils
def print_table_row(owner, table_name, column_data)
output = "#{owner}.#{table_name}: {"
index = 0
first = true
column_data.each do |key, value|
if value.nil?
value = "(null)"
end
output << ", " if !first
output << "{#{key}, #{value}}"
first = false
index += 1
end
output << "}"
puts output
end
end
class DbGrepOptions
def self.parse(args)
options = OpenStruct.new
options.pattern = nil
options.tns_name = nil
options.user_name = nil
options.password = nil
options.owner = nil
options.verbose = false
optionParser = OptionParser.new do |optionParser|
optionParser.banner = "Usage: dbgrep [options]"
optionParser.separator ""
optionParser.separator "Required Options:"
optionParser.on("-e pattern", "--expression pattern", "The regular expression") do |pattern|
if !options.pattern.nil?
puts "Error: only one pattern may be specified."
puts optionParser
exit
end
options.pattern = pattern
end
optionParser.on("-d tns_name", "--database tns_name", "The TNS_NAME for the Oracle database to connect to.") do |tns_name|
if !options.tns_name.nil?
puts "Error: only one tns name may be specified."
puts optionParser
exit
end
options.tns_name = tns_name
end
optionParser.on("-u user_name", "--username user_name", "The database user name") do |user_name|
if !options.user_name.nil?
puts "Error: only one user name may be specified."
puts optionsParser
exit
end
options.user_name = user_name
end
optionParser.on("-p password", "--password password", "The database password") do |password|
if !options.password.nil?
puts "Error: only one password may be specified."
puts optionParser
exit
end
options.password = password
end
optionParser.on("-o owner", "--owner owner", "The database owner") do |owner|
if !options.owner.nil?
puts "Error: only one owner may be specified."
puts optionParser
exit
end
options.owner = owner
end
optionParser.separator "Optional Options:"
optionParser.on("--verbose", "Displays detailed informtation") do
if options.verbose
puts "Error: verbose may on be specified once."
puts optionParser
exit
end
options.verbose = true
end
end
optionParser.parse!(args)
required(optionParser, 'pattern', options.pattern)
required(optionParser, 'user name', options.user_name)
required(optionParser, 'password', options.password)
required(optionParser, 'owner', options.owner)
required(optionParser, 'database tns name', options.tns_name)
options
end
private
def self.required(optionParser, name, value)
if value.nil?
puts "Error: #{name} must be specified"
puts optionParser
exit
end
end
end
class DbGrep
def initialize(user_name, password, tns_name, owner, verbose = false)
@database = OCI8.new(user_name, password, tns_name)
@owner = owner.upcase
@verbose = verbose
end
def grep(pattern)
expression = Regexp.new(pattern)
dbutils = DatabaseUtils.new
@database.exec("select distinct table_name from all_all_tables where owner = '#{@owner}'") do |table_name_row|
table = table_name_row[0]
puts "Checking table #{@owner}.#{table}" if @verbose
start_time = Time.now
cell_count = 0
cursor = @database.exec("select * from #{@owner}.#{table}")
values = cursor.fetch_hash
while !values.nil?
values.each do |key, value|
if value.to_s =~ expression
dbutils.print_table_row(@owner, table, values)
end
cell_count += 1
end
values = cursor.fetch_hash
end
end_time = Time.now
puts "Checked #{cell_count} cells in #{cursor.row_count} rows in #{@owner}.#{table} in #{end_time - start_time} seconds" if @verbose
end
end
end
options = DbGrepOptions.parse(ARGV)
dbgrep = DbGrep.new(
options.user_name,
options.password,
options.tns_name,
options.owner,
options.verbose)
dbgrep.grep(options.pattern)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment