Class: Blazer::Adapters::SqlAdapter
- Inherits:
-
BaseAdapter
- Object
- BaseAdapter
- Blazer::Adapters::SqlAdapter
- Defined in:
- lib/blazer/adapters/sql_adapter.rb
Direct Known Subclasses
Instance Attribute Summary collapse
-
#connection_model ⇒ Object
readonly
Returns the value of attribute connection_model.
Attributes inherited from BaseAdapter
Instance Method Summary collapse
- #cachable?(statement) ⇒ Boolean
- #cancel(run_id) ⇒ Object
-
#cohort_analysis_statement(statement, period:, days:) ⇒ Object
TODO treat date columns as already in time zone.
- #cost(statement) ⇒ Object
- #explain(statement) ⇒ Object
-
#initialize(data_source) ⇒ SqlAdapter
constructor
A new instance of SqlAdapter.
-
#parameter_binding ⇒ Object
Redshift adapter silently ignores binds.
- #preview_statement ⇒ Object
- #quoting ⇒ Object
- #reconnect ⇒ Object
- #run_statement(statement, comment, bind_params = []) ⇒ Object
- #schema ⇒ Object
- #supports_cohort_analysis? ⇒ Boolean
- #tables ⇒ Object
Constructor Details
#initialize(data_source) ⇒ SqlAdapter
Returns a new instance of SqlAdapter.
6 7 8 9 10 11 12 13 14 15 16 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 6 def initialize(data_source) super @connection_model = Class.new(Blazer::Connection) do def self.name "Blazer::Connection::Adapter#{object_id}" end establish_connection(data_source.settings["url"]) if data_source.settings["url"] end end |
Instance Attribute Details
#connection_model ⇒ Object (readonly)
Returns the value of attribute connection_model.
4 5 6 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 4 def connection_model @connection_model end |
Instance Method Details
#cachable?(statement) ⇒ Boolean
132 133 134 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 132 def cachable?(statement) !%w[CREATE ALTER UPDATE INSERT DELETE].include?(statement.split.first.to_s.upcase) end |
#cancel(run_id) ⇒ Object
121 122 123 124 125 126 127 128 129 130 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 121 def cancel(run_id) if postgresql? select_all("SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND query LIKE ?", ["%,run_id:#{run_id}%"]) elsif redshift? first_row = select_all("SELECT pid FROM stv_recents WHERE status = 'Running' AND query LIKE ?", ["%,run_id:#{run_id}%"]).first if first_row select_all("CANCEL #{first_row["pid"].to_i}") end end end |
#cohort_analysis_statement(statement, period:, days:) ⇒ Object
TODO treat date columns as already in time zone
141 142 143 144 145 146 147 148 149 150 151 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 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 141 def cohort_analysis_statement(statement, period:, days:) raise "Cohort analysis not supported" unless supports_cohort_analysis? cohort_column = statement =~ /\bcohort_time\b/ ? "cohort_time" : "conversion_time" tzname = Blazer.time_zone.tzinfo.name if mysql? time_sql = "CONVERT_TZ(cohorts.cohort_time, '+00:00', ?)" case period when "day" date_sql = "CAST(DATE_FORMAT(#{time_sql}, '%Y-%m-%d') AS DATE)" date_params = [tzname] when "week" date_sql = "CAST(DATE_FORMAT(#{time_sql} - INTERVAL ((5 + DAYOFWEEK(#{time_sql})) % 7) DAY, '%Y-%m-%d') AS DATE)" date_params = [tzname, tzname] else date_sql = "CAST(DATE_FORMAT(#{time_sql}, '%Y-%m-01') AS DATE)" date_params = [tzname] end bucket_sql = "CAST(CEIL(TIMESTAMPDIFF(SECOND, cohorts.cohort_time, query.conversion_time) / ?) AS SIGNED)" else date_sql = "date_trunc(?, cohorts.cohort_time::timestamptz AT TIME ZONE ?)::date" date_params = [period, tzname] bucket_sql = "CEIL(EXTRACT(EPOCH FROM query.conversion_time - cohorts.cohort_time) / ?)::int" end # WITH not an optimization fence in Postgres 12+ statement = <<~SQL WITH query AS ( {placeholder} ), cohorts AS ( SELECT user_id, MIN(#{cohort_column}) AS cohort_time FROM query WHERE user_id IS NOT NULL AND #{cohort_column} IS NOT NULL GROUP BY 1 ) SELECT #{date_sql} AS period, 0 AS bucket, COUNT(DISTINCT cohorts.user_id) FROM cohorts GROUP BY 1 UNION ALL SELECT #{date_sql} AS period, #{bucket_sql} AS bucket, COUNT(DISTINCT query.user_id) FROM cohorts INNER JOIN query ON query.user_id = cohorts.user_id WHERE query.conversion_time IS NOT NULL AND query.conversion_time >= cohorts.cohort_time #{cohort_column == "conversion_time" ? "AND query.conversion_time != cohorts.cohort_time" : ""} GROUP BY 1, 2 SQL params = [statement] + date_params + date_params + [days.to_i * 86400] connection_model.send(:sanitize_sql_array, params) end |
#cost(statement) ⇒ Object
95 96 97 98 99 100 101 102 103 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 95 def cost(statement) result = explain(statement) if sqlserver? result["TotalSubtreeCost"] else match = /cost=\d+\.\d+..(\d+\.\d+) /.match(result) match[1] if match end end |
#explain(statement) ⇒ Object
105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 105 def explain(statement) if postgresql? || redshift? select_all("EXPLAIN #{statement}").rows.first.first elsif sqlserver? begin execute("SET SHOWPLAN_ALL ON") result = select_all(statement).each.first ensure execute("SET SHOWPLAN_ALL OFF") end result end rescue nil end |
#parameter_binding ⇒ Object
Redshift adapter silently ignores binds
202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 202 def parameter_binding if postgresql? :numeric elsif sqlite? && prepared_statements? # Active Record silently ignores binds with SQLite when prepared statements are disabled :numeric elsif mysql? && prepared_statements? # Active Record silently ignores binds with MySQL when prepared statements are disabled :positional elsif sqlserver? proc do |statement, variables| variables.each_with_index do |(k, _), i| statement = statement.gsub("{#{k}}", "@#{i} ") end [statement, variables.values] end end end |
#preview_statement ⇒ Object
83 84 85 86 87 88 89 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 83 def preview_statement if sqlserver? "SELECT TOP (10) * FROM {table}" else "SELECT * FROM {table} LIMIT 10" end end |
#quoting ⇒ Object
197 198 199 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 197 def quoting ->(value) { connection_model.connection.quote(value) } end |
#reconnect ⇒ Object
91 92 93 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 91 def reconnect connection_model.establish_connection(settings["url"]) end |
#run_statement(statement, comment, bind_params = []) ⇒ Object
18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 18 def run_statement(statement, comment, bind_params = []) columns = [] rows = [] error = nil begin result = nil in_transaction do set_timeout(data_source.timeout) if data_source.timeout binds = bind_params.map { |v| ActiveRecord::Relation::QueryAttribute.new(nil, v, ActiveRecord::Type::Value.new) } result = connection_model.connection.select_all("#{statement} /*#{comment}*/", nil, binds) end columns = result.columns result.rows.each do |untyped_row| rows << (result.column_types.empty? ? untyped_row : columns.each_with_index.map { |c, i| untyped_row[i] && result.column_types[c] ? result.column_types[c].send(:cast_value, untyped_row[i]) : untyped_row[i] }) end # fix for non-ASCII column names and charts if adapter_name == "Trilogy" columns.map! { |k| k.dup.force_encoding(Encoding::UTF_8) } end rescue => e error = e..sub(/.+ERROR: /, "") error = Blazer::TIMEOUT_MESSAGE if Blazer::TIMEOUT_ERRORS.any? { |e| error.include?(e) } error = Blazer::VARIABLE_MESSAGE if error.include?("syntax error at or near \"$") || error.include?("Incorrect syntax near '@") || error.include?("your MySQL server version for the right syntax to use near '?") if error.include?("could not determine data type of parameter") error += " - try adding casting to variables and make sure none are inside a string literal" end reconnect if error.include?("PG::ConnectionBad") end [columns, rows, error] end |
#schema ⇒ Object
77 78 79 80 81 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 77 def schema sql = add_schemas("SELECT table_schema, table_name, column_name, data_type, ordinal_position FROM information_schema.columns") result = data_source.run_statement(sql) result.rows.group_by { |r| [r[0], r[1]] }.map { |k, vs| {schema: k[0], table: k[1], columns: vs.sort_by { |v| v[2] }.map { |v| {name: v[2], data_type: v[3]} }} }.sort_by { |t| [t[:schema] == default_schema ? "" : t[:schema], t[:table]] } end |
#supports_cohort_analysis? ⇒ Boolean
136 137 138 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 136 def supports_cohort_analysis? postgresql? || mysql? end |
#tables ⇒ Object
53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 53 def tables sql = add_schemas("SELECT table_schema, table_name FROM information_schema.tables") result = data_source.run_statement(sql, refresh_cache: true) if postgresql? || redshift? || snowflake? result.rows.sort_by { |r| [r[0] == default_schema ? "" : r[0], r[1]] }.map do |row| table = if row[0] == default_schema row[1] else "#{row[0]}.#{row[1]}" end table = table.downcase if snowflake? { table: table, value: connection_model.connection.quote_table_name(table) } end else result.rows.map(&:second).sort end end |