Module: MDBTools

Extended by:
MDBTools
Included in:
Column, MDB, MDBTools, Record, Table
Defined in:
lib/active_mdb/mdb_tools.rb

Constant Summary collapse

DELIMITER = 
'::'
LINEBREAK = 
"\n"
SANITIZER =

dumb filter for SQL arguments

/^\w\.\_/
BACKENDS = 
%w{ access mysql oracle postgres sybase }

Instance Method Summary collapse

Instance Method Details

#arrays_to_hashes(headers, arrays) ⇒ Object 



180
181
182
183
184
185
186
187
188
189
190
 
# File 'lib/active_mdb/mdb_tools.rb', line 180

def arrays_to_hashes(headers, arrays)
  arrays.collect do |record|
    record_hash = Hash.new
    until record.empty? do
      headers.each do |header|
        record_hash[header] = record.shift
      end
    end
    record_hash
  end
end

#backends ⇒ Object 



199
200
201
 
# File 'lib/active_mdb/mdb_tools.rb', line 199

def backends
  BACKENDS
end

#check_file(mdb_file) ⇒ Object

test for existence and usability of file

Raises:

  • (ArgumentError) 


11
12
13
14
15
16
17
18
 
# File 'lib/active_mdb/mdb_tools.rb', line 11

def check_file(mdb_file)
  raise ArgumentError, "File not found: #{mdb_file}" unless File.exist?(mdb_file)
  @mdb_version = `mdb-ver #{mdb_file} 2>&1`.chomp
  if $? != 0
    raise ArgumentError, "mdbtools cannot access #{mdb_file}"
  end
  mdb_file
end

#check_table(mdb_file, table_name) ⇒ Object

raises an ArgumentError unless the mdb file contains a table with the specified name. returns the table name, otherwise.



31
32
33
34
35
36
 
# File 'lib/active_mdb/mdb_tools.rb', line 31

def check_table(mdb_file, table_name)
  unless mdb_tables(mdb_file).include?(table_name)
    raise ArgumentError, "mdbtools does not think a table named \"#{table_name}\" exists"
  end
  table_name
end

#compile_conditions(conditions_hash) ⇒ Object

takes a hash where keys are column names, values are search values and returns a string that you can use in a WHERE clause

ex. compile_conditions(:first_name => ‘Summer’, :last_name => ‘Roberts’) gives “first_name like ‘%Summer%’ AND last_name like ‘%Roberts%’

if you want to use an operator other than LIKE, give compile_conditions a block that accepts column_name and value and does something interesting

compile_conditions(:age => 18) {|name, value| “#{name} = #value”}

the condition phrases are all ANDed together before insertion into a WHERE clause



118
119
120
121
122
123
124
125
126
 
# File 'lib/active_mdb/mdb_tools.rb', line 118

def compile_conditions(conditions_hash)
  conditions = conditions_hash.sort_by{|k,v| k.to_s}.map do |column_name, value|
    if block_given?
      yield column_name, value
    else
      "#{column_name} like '%#{value}%'"        
    end
  end.join(' AND ')
end

#delimited_to_arrays(text) ⇒ Object 



175
176
177
178
 
# File 'lib/active_mdb/mdb_tools.rb', line 175

def delimited_to_arrays(text)
  text.gsub!(/\r\n/,' ')
  text.split(LINEBREAK).collect { |row| row.split(DELIMITER)}
end

#describe_table(mdb_file, table_name) ⇒ Object

wrapper for DESCRIBE TABLE using mdb-sql



160
161
162
163
 
# File 'lib/active_mdb/mdb_tools.rb', line 160

def describe_table(mdb_file, table_name)
  command = "describe table \"#{table_name}\""
  mdb_sql(mdb_file,command)
end

#faked_count(*args) ⇒ Object

really dumb way to get a count. Does a SELECT and call size on the results



129
130
131
 
# File 'lib/active_mdb/mdb_tools.rb', line 129

def faked_count(*args)
  sql_select_where(*args).size
end

#field_names_for(mdb_file, table) ⇒ Object

uses mdb-sql to retrieve an array of the table’s field names



100
101
102
103
 
# File 'lib/active_mdb/mdb_tools.rb', line 100

def field_names_for(mdb_file, table)
  fields = `echo -n 'select * from #{table} where 1 = 2' | mdb-sql -Fp -d '#{DELIMITER}' #{mdb_file}`.chomp.sub(/^\n+/, '')
  fields.split(DELIMITER)
end

#mdb_export(mdb_file, table_name, options = {}) ⇒ Object

convenience method, not really used with ActiveMDB.

Valid options are :format, :headers, and :sanitize, which correspond rather directly to the underlying mdb-export arguments. Defaults to :format => ‘sql’, :headers => false, :sanitize => true



137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
 
# File 'lib/active_mdb/mdb_tools.rb', line 137

def mdb_export(mdb_file, table_name, options = {})
  defaults = {  :format => 'sql',
                :headers => false,
                :sanitize => true  }
  options = defaults.merge options
  
  args = []
  if options[:delimiter]
    args << "-d #{options[:delimiter].dump}"
  elsif options[:format] == 'sql'
    args << "-I "
  elsif options[:format] == 'csv'
    args << "-d ',' "
  else
    raise ArgumentError, "Unknown format:  #{options[:format]}"
  end
  
  args << "-H " unless options[:headers] == true
  args << "-S" unless options[:sanitize] == false
  `mdb-export #{args} #{mdb_file} #{table_name.to_s.dump}`
end

#mdb_schema(mdb_file, table_name) ⇒ Object

wrapper for mdb-schema, returns SQL statements



166
167
168
 
# File 'lib/active_mdb/mdb_tools.rb', line 166

def mdb_schema(mdb_file, table_name)
  schema = `mdb-schema -T #{table_name.dump} #{mdb_file}`
end

#mdb_sql(mdb_file, sql) ⇒ Object

forks an IO.popen running mdb-sql and discarding STDERR to /dev/null. The sql argument should be a single statement, ‘cause I don’t know what will happen otherwise. mdb-sql uses “ngo” as the command terminator.



78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
 
# File 'lib/active_mdb/mdb_tools.rb', line 78

def mdb_sql(mdb_file,sql)
  # libMDB barks on stderr quite frequently, so discard stderr entirely
  command = "mdb-sql -Fp -d '#{DELIMITER}' #{mdb_file} 2> /dev/null \n"
  array = []
  IO.popen(command, 'r+') do |pipe|
    pipe << "#{sql}\ngo\n"
    pipe.close_write
    pipe.readline
    fields = pipe.readline.chomp.split(DELIMITER)
    pipe.each do |row|
      hash = {}
      row = row.chomp.split(DELIMITER)
      fields.each_index do |i|
        hash[fields[i]] = row[i]
      end
      array << hash
    end
  end
  array
end

#mdb_tables(mdb_file, options = {}) ⇒ Object

uses mdb-tables tool to return an array of table names. You can filter the tables by passing an array of strings as either the :exclude or :include key to the options hash. The strings will be ORed into a regex. Only one or the other of :exclude or :include, please.

ex. mdb_tables(‘thing.mdb’, :exclude => [‘_Lookup’])

ex. mdb_tables(‘thing.mdb’, :include => [‘tbl’])

Raises:

  • (ArgumentError) 


47
48
49
50
51
52
53
54
55
56
57
58
59
60
 
# File 'lib/active_mdb/mdb_tools.rb', line 47

def mdb_tables(mdb_file, options = {})
  included, excluded = options[:include], options[:exclude]
  return `mdb-tables -1 #{mdb_file}`.split(LINEBREAK) if not (included || excluded)
  raise ArgumentError if (options[:include] && options [:exclude])
  if options[:exclude]
    regex = Regexp.new options[:exclude].to_a.join('|') 
    tables = `mdb-tables -1 #{mdb_file}`.split(LINEBREAK).delete_if { |name| name =~ regex }
  end
  if options[:include]
    regex = Regexp.new options[:include].to_a.join('|')
    tables = `mdb-tables -1 #{mdb_file}`.split(LINEBREAK).select { |name| name =~ regex }
  end
  tables
end

#mdb_truth(value) ⇒ Object

mdb-tools recognizes 1 and 0 as the boolean values. Make it so.



210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
 
# File 'lib/active_mdb/mdb_tools.rb', line 210

def mdb_truth(value)
  case value
  when false
    0
  when true
    1
  when 0
    0
  when 1
    1
  when "0"
    0
  when "1"
    1
  end
end

#mdb_version(file) ⇒ Object 



25
26
27
 
# File 'lib/active_mdb/mdb_tools.rb', line 25

def mdb_version(file)
  `mdb-ver #{file} 2> /dev/null`.chomp
end

#methodize(table_name) ⇒ Object

helper to turn table names into standard format method names. Inside, it’s just ActionView::Inflector.underscore



195
196
197
 
# File 'lib/active_mdb/mdb_tools.rb', line 195

def methodize(table_name)
  Inflector.underscore table_name
end

#sanitize!(string) ⇒ Object

poor, weakly sanitizing gsub!.



204
205
206
 
# File 'lib/active_mdb/mdb_tools.rb', line 204

def sanitize!(string)
  string.gsub!(SANITIZER, '')
end

#sql_select_where(mdb_file, table_name, attributes = nil, conditions = nil) ⇒ Object

takes an array of field names and some conditions to append in a WHERE clause



64
65
66
67
68
69
70
71
72
73
 
# File 'lib/active_mdb/mdb_tools.rb', line 64

def sql_select_where(mdb_file, table_name, attributes = nil, conditions=nil)
  if attributes.respond_to?(:join)
    fields = attributes.join(' ') 
  else
    attributes ||= '*'
  end
  where = conditions ? "where #{conditions}" : ""
  sql = "select #{attributes} from #{table_name} #{where}"
  mdb_sql(mdb_file, sql)
end

#table_to_csv(mdb_file, table_name) ⇒ Object

convenience method for mdb_export to output CSV with headers.



171
172
173
 
# File 'lib/active_mdb/mdb_tools.rb', line 171

def table_to_csv(mdb_file, table_name)
  mdb_export(mdb_file, table_name, :format => 'csv', :headers => true)
end

#valid_file?(file) ⇒ Boolean

runs mdb_version. A blank version indicates an unusable file

Returns:

  • (Boolean) 


21
22
23
 
# File 'lib/active_mdb/mdb_tools.rb', line 21

def valid_file?(file)
  !mdb_version(file).blank?
end