class Aqueduct::Wrappers::Postgresql
Public Instance Methods
column_values(table, column)
click to toggle source
# 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()
click to toggle source
# 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?()
click to toggle source
# 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.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)
click to toggle source
# 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()
click to toggle source
# 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)
click to toggle source
# 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 error += " <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.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()
click to toggle source
# 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.error}" ensure db_connection.finish if db_connection end { result: result, error: error } end
query(sql_statement)
click to toggle source
# 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()
click to toggle source
# File lib/aqueduct/wrappers/postgresql.rb, line 8 def sql_codes { text: 'text', numeric: 'numeric', open: '"', close: '"' } end
table_columns(table)
click to toggle source
# 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()
click to toggle source
# 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.error}" ensure db_connection.finish if db_connection end { result: tables, error: error } end