Class: Aqueduct::Wrappers::Postgresql

Inherits:
Object
  • Object
show all
Includes:
Aqueduct::Wrapper
Defined in:
lib/aqueduct/wrappers/postgresql.rb

Instance Method Summary collapse

Instance Method Details

#column_values(table, column) ⇒ Object 



133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
 
# File 'lib/aqueduct/wrappers/postgresql.rb', line 133

def column_values(table, column)
  error = ''
  result = []
  begin
    db_connection = PG.connect( host: @source.host, user: @source.username, password: @source.password, dbname: @source.database, port: @source.port )

    results = db_connection.exec("SELECT column_name FROM information_schema.columns WHERE table_name ='#{table}';")
    columns = results.collect{ |r| r["column_name"] }
    column_found = columns.include?(column)

    if column_found
      results = db_connection.exec("SELECT CAST(\"#{column}\" AS text) FROM \"#{table}\" GROUP BY \"#{column}\";")
      result = results.collect{|r| r[column.to_s]}
    end
  rescue PG::Error => e
    error = "Error: #{e.inspect}"
  ensure
    db_connection.finish if db_connection
  end
  { result: result, error: error }
end

#connect ⇒ Object 



12
13
14
 
# File 'lib/aqueduct/wrappers/postgresql.rb', line 12

def connect
  @db_connection = PG.connect( host: @source.host, user: @source.username, password: @source.password, dbname: @source.database, port: @source.port )
end

#connected? ⇒ Boolean

Returns:

  • (Boolean) 


31
32
33
34
35
36
37
38
39
40
41
42
43
44
 
# File 'lib/aqueduct/wrappers/postgresql.rb', line 31

def connected?
  result = false
  error = ''
  begin
    db_connection = PG.connect( host: @source.host, user: @source.username, password: @source.password, dbname: @source.database, port: @source.port )
    status = db_connection.status
  rescue PG::Error => e
    error = "#{e.errno}: #{e.error}"
  ensure
    result = true if status == 0
    db_connection.finish if db_connection
  end
  { result: result, error: error }
end

#count(query_concepts, conditions, tables, join_conditions, concept_to_count) ⇒ Object 



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

def count(query_concepts, conditions, tables, join_conditions, concept_to_count)
  result = 0
  error = ''
  sql_conditions = ''
  begin
    t = Time.now
    if tables.size > 0
      sql_conditions = "SELECT count(#{concept_to_count ? 'DISTINCT ' + concept_to_count : '*'}) as record_count FROM #{tables.join(', ')} WHERE #{join_conditions.join(' and ')}#{' and ' unless join_conditions.blank?}#{conditions}"
      Rails.logger.info sql_conditions
      db_connection = PG.connect( host: @source.host, user: @source.username, password: @source.password, dbname: @source.database, port: @source.port )
      if db_connection
        results = db_connection.exec(sql_conditions)
        result = results[0]["record_count"].to_i
      end
    else
      error = "Database [#{@source.name}] Error: No tables for concepts. Database not fully mapped."
    end
  rescue PG::Error => e
    error = "Database [#{@source.name}] Error: #{e}"
  ensure
    db_connection.finish if db_connection
  end
  { result: result, error: error, sql_conditions: sql_conditions }
end

#disconnect ⇒ Object 



16
17
18
19
 
# File 'lib/aqueduct/wrappers/postgresql.rb', line 16

def disconnect
  @db_connection.finish if @db_connection
  true
end

#get_all_values_for_column(table, column) ⇒ Object 



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
 
# File 'lib/aqueduct/wrappers/postgresql.rb', line 104

def get_all_values_for_column(table, column)
  values = []
  error = ''
  begin
    db_connection = PG.connect( host: @source.host, user: @source.username, password: @source.password, dbname: @source.database, port: @source.port )
    if db_connection
      results = db_connection.exec("SELECT column_name FROM information_schema.columns WHERE table_name ='#{table}';")
      columns = results.collect{ |r| r["column_name"] }
      column_found = columns.include?(column)

      if not column_found
        result += " <i>#{column}</i> does not exist in <i>#{@source.database}.#{table}</i>"
      else
        results = db_connection.exec("SELECT CAST(\"#{column}\" AS text) FROM \"#{table}\";")
        values = results.collect{|r| r[column.to_s]}
      end
    end
  rescue PG::Error => e
    error = "#{e.errno}: #{e.error}"
  ensure
    if db_connection
      db_connection.finish
    else
      error += " unable to connect to <i>#{@source.name}</i>"
    end
  end
  { values: values, error: error }
end

#get_table_metadata ⇒ Object 



46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
 
# File 'lib/aqueduct/wrappers/postgresql.rb', line 46

def get_table_metadata
  result = {}
  error = ''
  begin
    db_connection = PG.connect( host: @source.host, user: @source.username, password: @source.password, dbname: @source.database, port: @source.port )
    if db_connection
      tables = []
      results = db_connection.exec("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';")
      tables = results.collect{|r| r["table_name"]}

      tables.sort{|table_a, table_b| table_a.downcase <=> table_b.downcase}.each do |my_table|
        results = db_connection.exec("SELECT column_name, data_type FROM information_schema.columns WHERE table_name ='#{my_table}';")
        columns = results.collect{ |r| { column: r["column_name"], datatype: r['data_type'] } }
        result[my_table] = columns.sort{|a,b| a[:column].downcase <=> b[:column].downcase}
      end
    end
  rescue PG::Error => e
    error = "#{e.errno}: #{e.error}"
  ensure
    db_connection.finish if db_connection
  end
  { result: result, error: error }
end

#query(sql_statement) ⇒ Object 



21
22
23
24
25
26
27
28
29
 
# File 'lib/aqueduct/wrappers/postgresql.rb', line 21

def query(sql_statement)
  results = []
  total_count = 0
  if @db_connection
    results = @db_connection.exec(sql_statement).values
    total_count = results.size
  end
  [results, total_count]
end

#sql_codes ⇒ Object 



8
9
10
 
# File 'lib/aqueduct/wrappers/postgresql.rb', line 8

def sql_codes
  { text: 'text', numeric: 'numeric', open: '"', close: '"' }
end

#table_columns(table) ⇒ Object 



87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
 
# File 'lib/aqueduct/wrappers/postgresql.rb', line 87

def table_columns(table)
  columns = []
  error = ''
  begin
    db_connection = PG.connect( host: @source.host, user: @source.username, password: @source.password, dbname: @source.database, port: @source.port )
    if db_connection
      results = db_connection.exec("SELECT column_name, data_type FROM information_schema.columns WHERE table_name ='#{table}';")
      columns = results.collect{ |r| { column: r["column_name"], datatype: r['data_type'] } }
    end
  rescue PG::Error => e
    error = "Error retrieving column information. Please make sure that this database is configured correctly."
  ensure
    db_connection.finish if db_connection
  end
  { columns: columns, error: error }
end

#tables ⇒ Object 



70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
 
# File 'lib/aqueduct/wrappers/postgresql.rb', line 70

def tables
  tables = []
  error = ''
  begin
    db_connection = PG.connect( host: @source.host, user: @source.username, password: @source.password, dbname: @source.database, port: @source.port )
    if db_connection
      results = db_connection.exec("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';")
      tables = results.collect{|r| r["table_name"]}
    end
  rescue PG::Error => e
    error = "#{e.errno}: #{e.error}"
  ensure
    db_connection.finish if db_connection
  end
  { result: tables, error: error }
end