A platform for community discussion. Free, open, simple.
# frozen_string_literal: true
module DiscourseWorkflows
module DataTables
class Facade
LOCK_TIMEOUT_MS = 5_000
STATEMENT_TIMEOUT_MS = 500
class StatementTimeout < StandardError
end
class Query
include ActiveModel::Validations
attr_reader :normalized_filter, :limit, :offset, :sort_by, :sort_direction
validate :check_filter_errors
def initialize(
data_table:,
filter: nil,
limit: nil,
offset: nil,
sort_by: nil,
sort_direction: nil,
optional_filter: false
)
normalized_filter =
DiscourseWorkflows::NormalizedFilter.new(
data_table:,
filter:,
optional: optional_filter,
)
@invalid_filter = normalized_filter if normalized_filter.invalid?
@normalized_filter = normalized_filter.value
@limit = limit
@offset = offset
@sort_by = sort_by
@sort_direction = sort_direction
end
def has_changes_to_save?
true
end
private
def check_filter_errors
@invalid_filter&.errors&.full_messages&.each { |message| errors.add(:base, message) }
end
end
class RowInput
include ActiveModel::Validations
attr_reader :columns
validate :check_normalization_error
def initialize(data_table:, data:, fill_missing: false)
@columns = DataTableRow.normalize_row_data(data_table, data, fill_missing: fill_missing)
rescue ArgumentError => e
@normalization_error = e.message
@columns = {}
end
def has_changes_to_save?
true
end
private
def check_normalization_error
errors.add(:base, @normalization_error) if @normalization_error.present?
end
end
MAX_TOTAL_SIZE_BYTES = 50.megabytes
class << self
def within_storage_limit?
Storage.total_size_bytes < MAX_TOTAL_SIZE_BYTES
end
def total_size_bytes
Storage.total_size_bytes
end
def batch_stats(data_table_ids)
return {} if data_table_ids.empty?
with_statement_timeout { Storage.batch_stats(data_table_ids) }
end
def size_bytes(data_table_id)
Storage.size_bytes(data_table_id)
end
def create_table!(data_table, columns: [])
Storage.create_table!(data_table, columns: columns)
end
def drop_table!(data_table_id)
Storage.drop_table!(data_table_id)
end
def with_statement_timeout
connection = ActiveRecord::Base.connection
previous_timeout =
connection.select_value("SHOW statement_timeout") if connection.transaction_open?
connection.transaction(requires_new: true) do
connection.execute("SET LOCAL statement_timeout = '#{STATEMENT_TIMEOUT_MS}ms'")
result = yield
if previous_timeout
connection.execute(
"SET LOCAL statement_timeout = #{connection.quote(previous_timeout)}",
)
end
result
end
rescue ActiveRecord::QueryCanceled, PG::QueryCanceled
raise StatementTimeout, "Data table query exceeded the maximum allowed execution time"
end
end
SORT_DIRECTIONS = { "asc" => "ASC", "desc" => "DESC" }.freeze
MAX_LIMIT = DiscourseWorkflows::Pagination::MAX_LIMIT
TOTAL_COUNT_COLUMN = "total-count"
attr_reader :data_table
delegate :with_statement_timeout, to: :class, private: true
def initialize(data_table)
@data_table = data_table
@table_name = Storage.table_name(data_table.id)
@table = Arel::Table.new(@table_name)
end
def build_query(**options)
Query.new(data_table: @data_table, **options)
end
def build_row_input(**options)
RowInput.new(data_table: @data_table, **options)
end
def query(query)
with_statement_timeout { query_rows(query) }
end
def find_row(row_id)
with_statement_timeout do
query = @table.project(Arel.star).where(@table[:id].eq(row_id))
connection.exec_query(query.to_sql).to_a.first&.then { |row| serialize_row(row) }
end
end
def count(query = nil)
with_statement_timeout { count_rows(normalized_filter: query&.normalized_filter) }
end
def insert(row_input)
validate_storage_limit!
with_statement_timeout { insert_row(row_input.columns) }
end
def update_row(row_id:, row_input:)
return nil if row_input.columns.empty?
validate_storage_limit!
with_statement_timeout do
um = build_update_manager(row_input.columns)
um.where(@table[:id].eq(row_id))
returning_rows(um.to_sql)[:rows].first
end
end
def update(query:, row_input:, returning: :count)
return 0 if row_input.columns.empty?
validate_storage_limit!
with_statement_timeout do
um = build_update_manager(row_input.columns)
um = apply_filters(um, query.normalized_filter)
execute(um.to_sql, returning:, limit: query.limit)
end
end
def delete(query:, returning: :count)
with_statement_timeout do
dm = Arel::DeleteManager.new
dm.from(@table)
dm = apply_filters(dm, query.normalized_filter)
execute(dm.to_sql, returning:, limit: query.limit)
end
end
def delete_rows(row_ids)
return 0 if row_ids.empty?
with_statement_timeout do
dm = Arel::DeleteManager.new
dm.from(@table)
dm.where(@table[:id].in(row_ids))
connection.exec_delete(dm.to_sql)
end
end
def add_column!(name, type)
with_lock_timeout do
connection.add_column(@table_name, name, Storage::SCHEMA_TYPES.fetch(type))
end
end
def rename_column!(old_name:, new_name:)
with_lock_timeout do
connection.rename_column(@table_name, old_name, new_name)
@data_table.update_columns(
column_order:
@data_table.column_order.map { |name| name == old_name ? new_name : name },
)
end
end
def move_column!(column_name, position)
names = @data_table.user_columns.map { |column| column["name"] } - [column_name]
names.insert([position, names.size].min, column_name)
@data_table.update_columns(column_order: names)
end
def drop_column!(column_name)
with_lock_timeout do
connection.remove_column(@table_name, column_name)
@data_table.update_columns(column_order: @data_table.column_order - [column_name])
end
end
private
def validate_storage_limit!
return if self.class.within_storage_limit?
raise InvalidInput, "Data table storage limit exceeded"
end
def query_rows(data_query)
query = @table.project(Arel.star, Arel.sql("COUNT(*) OVER() AS \"#{TOTAL_COUNT_COLUMN}\""))
query = apply_filters(query, data_query.normalized_filter)
query = apply_ordering(query, data_query.sort_by, data_query.sort_direction)
query = apply_pagination(query, data_query.limit, data_query.offset)
rows_with_count(query.to_sql)
end
def rows_with_count(sql)
results = connection.exec_query(sql).to_a
total_count = results.first&.[](TOTAL_COUNT_COLUMN) || 0
rows = results.map { |row| serialize_row(row.except(TOTAL_COUNT_COLUMN)) }
{ rows: rows, count: total_count }
end
def count_rows(normalized_filter: nil)
query = @table.project(Arel.star.count)
query = apply_filters(query, normalized_filter)
connection.exec_query(query.to_sql).rows.first.first.to_i
end
def insert_row(normalized_data)
im = Arel::InsertManager.new
im.into(@table)
return returning_rows("#{im.to_sql} DEFAULT VALUES")[:rows].first if normalized_data.empty?
im.insert(normalized_data.map { |name, value| [@table[name], value] })
returning_rows(im.to_sql)[:rows].first
end
def execute(sql, returning:, limit:)
return connection.exec_update(sql) if returning == :count
returning_rows(sql, limit:)
end
def returning_rows(sql, limit: nil)
select =
"WITH affected AS (#{sql} RETURNING *) SELECT *, COUNT(*) OVER() AS \"#{TOTAL_COUNT_COLUMN}\" FROM affected ORDER BY id"
capped = capped_limit(limit)
select = "#{select} LIMIT #{capped}" if capped
rows_with_count(select)
end
def build_update_manager(normalized_data)
um = Arel::UpdateManager.new
um.table(@table)
assignments =
normalized_data.map { |name, value| [@table[name], value] } +
[[@table[:updated_at], Arel.sql("CURRENT_TIMESTAMP")]]
um.set(assignments)
um
end
def apply_filters(query, normalized_filter)
return query if normalized_filter.blank? || normalized_filter["filters"].blank?
conditions = normalized_filter["filters"].map { |f| build_arel_condition(f) }
combiner = normalized_filter["type"] == "or" ? :or : :and
query.where(conditions.reduce(combiner))
end
def apply_ordering(query, sort_by, sort_direction)
return query.order(@table[:id].asc) if sort_by.blank?
if valid_sort_columns.exclude?(sort_by.to_s)
raise InvalidInput, "Invalid sort column: #{sort_by.inspect}"
end
direction = SORT_DIRECTIONS[sort_direction.to_s.downcase] || "ASC"
query.order(
Arel.sql("#{Storage.quoted_column(sort_by)} #{direction} NULLS LAST"),
@table[:id].asc,
)
end
def valid_sort_columns
@valid_sort_columns ||= @data_table.columns.map { |c| c["name"] }.to_set
end
def apply_pagination(query, limit, offset)
capped = capped_limit(limit)
query = query.take(capped) if capped
if offset.present?
parsed_offset = parse_integer(offset, "offset")
query = query.skip(parsed_offset) if parsed_offset > 0
end
query
end
def capped_limit(limit)
return if limit.blank?
parsed = [parse_integer(limit, "limit"), MAX_LIMIT].min
parsed if parsed > 0
end
def parse_integer(value, name)
Integer(value)
rescue ArgumentError, TypeError
raise InvalidInput, "#{name} must be an integer"
end
def build_arel_condition(filter)
col = @table[filter["columnName"]]
value = filter["value"]
return filter["condition"] == "eq" ? col.eq(nil) : col.not_eq(nil) if value.nil?
case filter["condition"]
when "eq"
col.eq(value)
when "neq"
col.not_eq(value).or(col.eq(nil))
when "gt"
col.gt(value)
when "gte"
col.gteq(value)
when "lt"
col.lt(value)
when "lte"
col.lteq(value)
when "like"
col.matches(escape_like_specials(value), "!", true)
when "ilike"
col.matches(escape_like_specials(value), "!", false)
when "not_ilike"
col.does_not_match(escape_like_specials(value), "!", false)
else
raise ArgumentError, "Unknown filter condition: #{filter["condition"].inspect}"
end
end
def escape_like_specials(value)
"%#{ActiveRecord::Base.sanitize_sql_like(value.to_s, "!")}%"
end
def connection
ActiveRecord::Base.connection
end
def serialize_row(row)
row.transform_values { |value| serialize_value(value) }
end
def serialize_value(value)
return nil if value.nil?
value.respond_to?(:iso8601) ? value.utc.iso8601 : value
end
def with_lock_timeout
connection.transaction do
connection.execute("SET LOCAL lock_timeout = '#{LOCK_TIMEOUT_MS}ms'")
yield
end
end
end
end
end