lib/veritas/sql/generator/relation/unary.rb in veritas-sql-generator-0.0.3 vs lib/veritas/sql/generator/relation/unary.rb in veritas-sql-generator-0.0.4

- old
+ new

@@ -6,23 +6,34 @@ class Relation # Generates an SQL statement for a unary relation class Unary < Relation extend Aliasable - include Direction, Literal, Logic + include Direction, + Literal, + Function::Aggregate, + Function::Connective, + Function::Predicate, + Function::Proposition, + Function::String, + Function::Numeric inheritable_alias(:visit_veritas_relation_operation_reverse => :visit_veritas_relation_operation_order) - DISTINCT = 'DISTINCT '.freeze - COLLAPSIBLE = { - Algebra::Projection => Set[ Algebra::Projection, Algebra::Restriction, ].freeze, - Algebra::Restriction => Set[ Algebra::Projection, Veritas::Relation::Operation::Order, Veritas::Relation::Operation::Reverse, ].freeze, - Veritas::Relation::Operation::Order => Set[ Algebra::Projection, Algebra::Restriction, Veritas::Relation::Operation::Order, Veritas::Relation::Operation::Reverse, Algebra::Rename ].freeze, - Veritas::Relation::Operation::Reverse => Set[ Algebra::Projection, Algebra::Restriction, Veritas::Relation::Operation::Order, Veritas::Relation::Operation::Reverse, Algebra::Rename ].freeze, - Veritas::Relation::Operation::Offset => Set[ Algebra::Projection, Algebra::Restriction, Veritas::Relation::Operation::Order, Veritas::Relation::Operation::Reverse, Algebra::Rename ].freeze, - Veritas::Relation::Operation::Limit => Set[ Algebra::Projection, Algebra::Restriction, Veritas::Relation::Operation::Order, Veritas::Relation::Operation::Reverse, Veritas::Relation::Operation::Offset, Algebra::Rename ].freeze, - Algebra::Rename => Set[ Algebra::Projection, Algebra::Restriction, Veritas::Relation::Operation::Order, Veritas::Relation::Operation::Reverse, Veritas::Relation::Operation::Offset, Veritas::Relation::Operation::Limit ].freeze, + DISTINCT = 'DISTINCT '.freeze + NO_ROWS = ' HAVING FALSE'.freeze + ANY_ROWS = ' HAVING COUNT (*) > 0' + COLLAPSIBLE = { + Algebra::Summarization => Set[ ].freeze, + Algebra::Projection => Set[ Algebra::Projection, Algebra::Restriction, ].freeze, + Algebra::Extension => Set[ Algebra::Projection, Algebra::Restriction, Veritas::Relation::Operation::Order, Veritas::Relation::Operation::Reverse, Veritas::Relation::Operation::Offset, Veritas::Relation::Operation::Limit ].freeze, + Algebra::Rename => Set[ Algebra::Projection, Algebra::Restriction, Veritas::Relation::Operation::Order, Veritas::Relation::Operation::Reverse, Veritas::Relation::Operation::Offset, Veritas::Relation::Operation::Limit ].freeze, + Algebra::Restriction => Set[ Algebra::Projection, Veritas::Relation::Operation::Order, Veritas::Relation::Operation::Reverse, ].freeze, + Veritas::Relation::Operation::Order => Set[ Algebra::Projection, Algebra::Extension, Algebra::Rename, Algebra::Restriction, Algebra::Summarization, Veritas::Relation::Operation::Order, Veritas::Relation::Operation::Reverse, ].freeze, + Veritas::Relation::Operation::Reverse => Set[ Algebra::Projection, Algebra::Extension, Algebra::Rename, Algebra::Restriction, Algebra::Summarization, Veritas::Relation::Operation::Order, Veritas::Relation::Operation::Reverse, ].freeze, + Veritas::Relation::Operation::Offset => Set[ Algebra::Projection, Algebra::Extension, Algebra::Rename, Algebra::Restriction, Algebra::Summarization, Veritas::Relation::Operation::Order, Veritas::Relation::Operation::Reverse, ].freeze, + Veritas::Relation::Operation::Limit => Set[ Algebra::Projection, Algebra::Extension, Algebra::Rename, Algebra::Restriction, Algebra::Summarization, Veritas::Relation::Operation::Order, Veritas::Relation::Operation::Reverse, Veritas::Relation::Operation::Offset, ].freeze, }.freeze # Initialize a Unary relation SQL generator # # @return [undefined] @@ -33,18 +44,19 @@ @scope = ::Set.new end # Visit a Base Relation # - # @param [BaseRelation] base_relation + # @param [Relation::Base] base_relation # # @return [self] # # @api private - def visit_veritas_base_relation(base_relation) + def visit_veritas_relation_base(base_relation) @name = base_relation.name @from = visit_identifier(@name) + @header = base_relation.header @columns = columns_for(base_relation) self end # Visit a Projection @@ -55,24 +67,42 @@ # # @api private def visit_veritas_algebra_projection(projection) @from = subquery_for(projection) @distinct = DISTINCT + @header = projection.header @columns = columns_for(projection) scope_query(projection) self end + # Visit an Extension + # + # @param [Algebra::Extension] extension + # + # @return [self] + # + # @api private + def visit_veritas_algebra_extension(extension) + @from = subquery_for(extension) + @header = extension.header + @columns ||= columns_for(extension.operand) + add_extensions(extension.extensions) + scope_query(extension) + self + end + # Visit a Rename # # @param [Algebra::Rename] rename # # @return [self] # # @api private def visit_veritas_algebra_rename(rename) @from = subquery_for(rename) + @header = rename.header @columns = columns_for(rename.operand, rename.aliases.to_hash) scope_query(rename) self end @@ -83,26 +113,47 @@ # @return [self] # # @api private def visit_veritas_algebra_restriction(restriction) @from = subquery_for(restriction) - @where = dispatch(restriction.predicate) + @where = " WHERE #{dispatch(restriction.predicate)}" + @header = restriction.header @columns ||= columns_for(restriction) scope_query(restriction) self end + # Visit a Summarization + # + # @param [Algebra::Summarization] summarization + # + # @return [self] + # + # @api private + def visit_veritas_algebra_summarization(summarization) + summarize_per = summarization.summarize_per + @from = subquery_for(summarization) + @header = summarization.header + @columns = columns_for(summarize_per) + summarize_per(summarize_per) + group_by_columns + add_extensions(summarization.summarizers) + scope_query(summarization) + self + end + # Visit an Order # # @param [Relation::Operation::Order] order # # @return [self] # # @api private def visit_veritas_relation_operation_order(order) @from = subquery_for(order) - @order = order_for(order.directions) + @order = " ORDER BY #{order_for(order.directions)}" + @header = order.header @columns ||= columns_for(order) scope_query(order) self end @@ -113,11 +164,12 @@ # @return [self] # # @api private def visit_veritas_relation_operation_limit(limit) @from = subquery_for(limit) - @limit = limit.limit + @limit = " LIMIT #{limit.limit}" + @header = limit.header @columns ||= columns_for(limit) scope_query(limit) self end @@ -128,112 +180,121 @@ # @return [self] # # @api private def visit_veritas_relation_operation_offset(offset) @from = subquery_for(offset) - @offset = offset.offset + @offset = " OFFSET #{offset.offset}" + @header = offset.header @columns ||= columns_for(offset) scope_query(offset) self end - # Return the SQL for the unary relation + private + + # Generate the SQL using the supplied columns # - # @example - # sql = unary_relation.to_s + # @param [String] columns # # @return [#to_s] # - # @api public - def to_s - generate_sql(@columns) + # @api private + def generate_sql(columns) + [ "SELECT #{columns} FROM #{@from}", @where, @group, @having, @order, @limit, @offset ].join end - # Return the SQL suitable for an subquery + # Return the columns to use in a query # # @return [#to_s] # # @api private - def to_subquery - generate_sql(all_columns? ? ALL_COLUMNS : @columns) + def query_columns + explicit_columns end - private - - # Generate the SQL using the supplied columns + # Return the columns to use in a subquery # - # @param [String] columns - # # @return [#to_s] # # @api private - def generate_sql(columns) - return EMPTY_STRING unless visited? - sql = "SELECT #{@distinct}#{columns} FROM #{@from}" - sql << " WHERE #{@where}" if @where - sql << " ORDER BY #{@order}" if @order - sql << " LIMIT #{@limit}" if @limit - sql << " OFFSET #{@offset}" if @offset - sql + def subquery_columns + explicit_columns_in_subquery? ? explicit_columns : super end - # Return a list of columns in a header + # Test if the subquery should use "*" and not specify columns explicitly # - # @param [Veritas::Relation] relation + # @return [Boolean] # - # @param [#[]] aliases - # optional aliases for the columns + # @api private + def explicit_columns_in_subquery? + @scope.include?(Algebra::Projection) || + @scope.include?(Algebra::Rename) || + @scope.include?(Algebra::Summarization) + end + + # Return a list of columns for ordering # + # @param [DirectionSet] directions + # # @return [#to_s] # # @api private - def columns_for(relation, aliases = {}) - relation.header.map { |attribute| column_for(attribute, aliases) }.join(SEPARATOR) + def order_for(directions) + directions.map { |direction| dispatch(direction) }.join(SEPARATOR) end - # Return the column for an attribute + # Summarize the operand over the provided relation # - # @param [Attribute] attribute + # @param [Relation] relation # - # @param [#[]] aliases - # aliases for the columns + # @return [undefined] # - # @return [#to_s] - # # @api private - def column_for(attribute, aliases) - column = dispatch(attribute) - if aliases.key?(attribute) - alias_for(column, aliases[attribute]) + def summarize_per(relation) + return if relation.eql?(TABLE_DEE) + + if relation.eql?(TABLE_DUM) then summarize_per_table_dum + elsif (generator = Binary.visit(relation)).name.eql?(name) then summarize_per_subset else - column + summarize_per_relation(generator) end end - # Return the column alias for an attribute + # Summarize the operand using table dee # - # @param [#to_s] column + # @return [undefined] # - # @param [Attribute, nil] alias_attribute - # attribute to use for the alias + # @api private + def summarize_per_table_dum + @having = NO_ROWS + end + + # Summarize the operand using a subset # - # @return [#to_s] + # @return [undefined] # # @api private - def alias_for(column, alias_attribute) - "#{column} AS #{visit_identifier(alias_attribute.name)}" + def summarize_per_subset + @having = ANY_ROWS end - # Return a list of columns for ordering + # Summarize the operand using another relation # - # @param [DirectionSet] directions + # @return [undefined] # - # @return [#to_s] + # @api private + def summarize_per_relation(generator) + @from = "#{generator.to_subquery} AS #{visit_identifier(generator.name)} NATURAL LEFT JOIN #{@from}" + end + + # Group by the columns # + # @return [undefined] + # # @api private - def order_for(directions) - directions.map { |direction| dispatch(direction) }.join(SEPARATOR) + def group_by_columns + @group = " GROUP BY #{column_list_for(@columns)}" if @columns.any? end # Return an expression that can be used for the FROM # # @param [Relation] relation @@ -260,19 +321,10 @@ # @api private def scope_query(operand) @scope << operand.class end - # Test if the query should use "*" and not specify columns explicitly - # - # @return [Boolean] - # - # @api private - def all_columns? - !@scope.include?(Algebra::Projection) && !@scope.include?(Algebra::Rename) - end - # Test if the relation should be collapsed # # @param [Relation] relation # # @return [#to_s] @@ -288,11 +340,11 @@ # # @return [#to_s] # # @api private def aliased_subquery(subquery) - self.class.subquery(subquery) + "#{subquery.to_subquery} AS #{visit_identifier(subquery.name)}" ensure reset_query_state end # Visit a Binary Relation @@ -314,10 +366,11 @@ # @return [undefined] # # @api private def reset_query_state @scope.clear - @distinct = @columns = @where = @order = @limit = @offset = nil + @extensions.clear + @distinct = @columns = @where = @order = @limit = @offset = @group = @having = nil end end # class Unary end # class Relation end # module Generator