Ferreteria/v2/usage/SQO

About

Structured query objects (SQOs) allow classes to define the best way of implementing various parts of more complicated queries without having to define the entire query. Each SQO class represents part of an SQL statement and allows modification of its parameters and contents (if any).

The Render() and Trace() methods work for any element at any level, so you can inspect what you are getting and how it is being constructed. Render() produces usable SQL, while Trace() shows the object structure.

Tasks

Assuming $q is a QUERY object:

• to add a JOIN ELEMENT $qje to an existing JOIN: $q->Select()->Source()->AddElement($qje)

Structure

• QUERY
  • SELECT
    • SOURCE (can be TABLE, JOIN, or SUBQUERY)
      • TABLE
      • JOIN
        • JOIN ELEMENT (2 or more)
      • SUBQUERY
        • QUERY (lather, rinse, repeat...)
    • FIELDS
      • array of strings
  • TERMS

Classes

• fcSQL_base_element: this should probably be rewritten as an interface, because that's pretty much what it is: ensures that every object in a SQO hierarchy will have certain methods.
• fcSQL_Query: renders executable SQL. Includes a SELECT SQO and an optional TERMS SQO.
• fcSQL_Select: has a SOURCE and FIELDS (which defaults to '*')
• fcSQL_Fields: list of fields (each array element may specify an alias)
• fcSQL_Source: abstract base class for SOURCE types
  • fcSQL_SourceSingle: abstract base class for single-item (non-JOIN) sources
    • fcSQL_TableSource: a table source
    • fcSQL_SubQuerySource: source consisting of an executable SELECT wrapped in parentheses
  • fcSQL_JoinSource: source that joins two or more other sources together
    • AddElement(fcSQL_JoinElement $oJoin)
    • AddElements(array $ar)
• fcSQL_Terms: collects filtering, sorting, and grouping term objects (WHERE, ORDER BY, GROUP BY)
• fcSQL_Term: abstract base class for TERM classes
  • fcSQLt_Filt: filtering (WHERE) term
  • fcSQLt_Sort: sorting (ORDER BY) term
  • fcSQLt_Group: grouping (GROUP BY) term
• fcSQL_JoinElement: component for a JOIN