Class: Aqueduct::Wrappers::Mysql
- Inherits:
-
Object
- Object
- Aqueduct::Wrappers::Mysql
- Includes:
- Aqueduct::Wrapper
- Defined in:
- lib/aqueduct/wrappers/mysql.rb
Instance Method Summary collapse
- #column_values(table, column) ⇒ Object
- #connect ⇒ Object
- #connected? ⇒ Boolean
- #count(query_concepts, conditions, tables, join_conditions, concept_to_count) ⇒ Object
- #disconnect ⇒ Object
- #get_all_values_for_column(table, column) ⇒ Object
- #get_table_metadata ⇒ Object
- #query(sql_statement) ⇒ Object
- #sql_codes ⇒ Object
- #table_columns(table) ⇒ Object
- #tables ⇒ Object
Instance Method Details
#column_values(table, column) ⇒ Object
152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 |
# File 'lib/aqueduct/wrappers/mysql.rb', line 152 def column_values(table, column) error = '' result = [] begin db_connection = Mysql2::Client.new(host: @source.host, username: @source.username, password: @source.password, database: @source.database, port: @source.port) column_found = false db_connection.query("DESCRIBE #{table}").each do |field| column_found = true if field['Field'] == column end if column_found results = db_connection.query("SELECT `#{column}` as 'column', count(*) FROM #{table} GROUP BY `#{column}`;") results.each do |row| if row['column'].class != String and row['column'].respond_to?('round') and row['column'].round == row['column'] result << row['column'].round else result << row['column'] end end end rescue Mysql2::Error => e error = "Error: #{e.inspect}" ensure db_connection.close if db_connection end { result: result, error: error } end |
#connect ⇒ Object
12 13 14 |
# File 'lib/aqueduct/wrappers/mysql.rb', line 12 def connect @db_connection = Mysql2::Client.new(host: @source.host, username: @source.username, password: @source.password, database: @source.database, port: @source.port) end |
#connected? ⇒ Boolean
31 32 33 34 35 36 37 38 39 40 41 42 43 |
# File 'lib/aqueduct/wrappers/mysql.rb', line 31 def connected? result = false error = '' begin db_connection = Mysql2::Client.new(host: @source.host, username: @source.username, password: @source.password, database: @source.database, port: @source.port) rescue Mysql2::Error => e error = "#{e.errno}: #{e.error}" ensure result = true if db_connection db_connection.close if db_connection end { result: result, error: error } end |
#count(query_concepts, conditions, tables, join_conditions, concept_to_count) ⇒ Object
179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 |
# File 'lib/aqueduct/wrappers/mysql.rb', line 179 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 = Mysql2::Client.new(host: @source.host, username: @source.username, password: @source.password, database: @source.database, port: @source.port) if db_connection results = db_connection.query(sql_conditions) results.each do |row| result = row['record_count'] end end else error = "Database [#{@source.name}] Error: No tables for concepts. Database not fully mapped." end rescue Mysql2::Error => e error = "Database [#{@source.name}] Error: #{e}" ensure db_connection.close if db_connection end { result: result, error: error, sql_conditions: sql_conditions } end |
#disconnect ⇒ Object
16 17 18 19 |
# File 'lib/aqueduct/wrappers/mysql.rb', line 16 def disconnect @db_connection.close if @db_connection true end |
#get_all_values_for_column(table, column) ⇒ Object
114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 |
# File 'lib/aqueduct/wrappers/mysql.rb', line 114 def get_all_values_for_column(table, column) values = [] error = '' begin db_connection = Mysql2::Client.new(host: @source.host, username: @source.username, password: @source.password, database: @source.database, port: @source.port) if db_connection column_found = false db_connection.query("DESCRIBE #{table}").each do |field| column_found = true if field['Field'] == column end if not column_found error += " <i>#{column}</i> does not exist in <i>#{@source.database}.#{table}</i>" else results = db_connection.query("SELECT `#{column}` FROM #{table};") results.each do |row| row.values.each do |value| if value.class != String and value.respond_to?('round') and value.round == value values << value.round else values << value end end end end end rescue Mysql2::Error => e error = "#{e.errno}: #{e.error}" ensure if db_connection db_connection.close else error += " unable to connect to <i>#{@source.name}</i>" end end { values: values, error: error } end |
#get_table_metadata ⇒ Object
45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
# File 'lib/aqueduct/wrappers/mysql.rb', line 45 def result = {} error = '' begin db_connection = Mysql2::Client.new(host: @source.host, username: @source.username, password: @source.password, database: @source.database, port: @source.port) if db_connection tables = [] results = db_connection.query("SHOW TABLES;") results.each do |row| row.values.each do |table| tables << table end end tables.sort{|table_a, table_b| table_a.downcase <=> table_b.downcase}.each do |my_table| results = db_connection.query("SHOW COLUMNS FROM #{my_table}") columns = [] results.each do |row| columns << {column: row['Field'], datatype: row['Type']} end result[my_table] = columns.sort{|a,b| a[:column].downcase <=> b[:column].downcase} end end rescue Mysql2::Error => e error = "#{e.errno}: #{e.error}" ensure db_connection.close 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/mysql.rb', line 21 def query(sql_statement) results = [] total_count = 0 if @db_connection results = @db_connection.query(sql_statement, as: :array) total_count = results.each.size end [results, total_count] end |
#sql_codes ⇒ Object
8 9 10 |
# File 'lib/aqueduct/wrappers/mysql.rb', line 8 def sql_codes { text: 'CHAR(255)', numeric: nil, open: '`', close: '`' } # Using "5.4" + 0.0 to convert end |
#table_columns(table) ⇒ Object
97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 |
# File 'lib/aqueduct/wrappers/mysql.rb', line 97 def table_columns(table) columns = [] error = '' begin db_connection = Mysql2::Client.new(host: @source.host, username: @source.username, password: @source.password, database: @source.database, port: @source.port) if db_connection results = db_connection.query("SHOW COLUMNS FROM #{table}") results.each { |row| columns << {column: row['Field'], datatype: row['Type']} } end rescue Mysql2::Error => e error = "Error retrieving column information. Please make sure that this database is configured correctly." ensure db_connection.close if db_connection end { columns: columns, error: error } end |
#tables ⇒ Object
76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 |
# File 'lib/aqueduct/wrappers/mysql.rb', line 76 def tables tables = [] error = '' begin db_connection = Mysql2::Client.new(host: @source.host, username: @source.username, password: @source.password, database: @source.database, port: @source.port) if db_connection results = db_connection.query("SHOW TABLES;") results.each do |row| row.values.each do |table| tables << table end end end rescue Mysql2::Error => e error = "#{e.errno}: #{e.error}" ensure db_connection.close if db_connection end { result: tables, error: error } end |