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
126 127 128 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 126 def cachable?(statement) !%w[CREATE ALTER UPDATE INSERT DELETE].include?(statement.split.first.to_s.upcase) end |
#cancel(run_id) ⇒ Object
115 116 117 118 119 120 121 122 123 124 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 115 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
135 136 137 138 139 140 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 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 135 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
89 90 91 92 93 94 95 96 97 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 89 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
99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 99 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
196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 196 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
77 78 79 80 81 82 83 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 77 def preview_statement if sqlserver? "SELECT TOP (10) * FROM {table}" else "SELECT * FROM {table} LIMIT 10" end end |
#quoting ⇒ Object
191 192 193 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 191 def quoting ->(value) { connection_model.connection.quote(value) } end |
#reconnect ⇒ Object
85 86 87 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 85 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 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 18 def run_statement(statement, comment, bind_params = []) columns = [] rows = [] error = nil begin 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) 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 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
71 72 73 74 75 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 71 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
130 131 132 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 130 def supports_cohort_analysis? postgresql? || mysql? end |
#tables ⇒ Object
47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
# File 'lib/blazer/adapters/sql_adapter.rb', line 47 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 |