New feature: Common Table Expression enhancement

Following discussions in issue #161, @arikkfir and I have tackled several key gaps in the Common Table Expression (CTE) related APIs:

• Implemented support for the WITH RECURSIVE clause;
• Enabled the use of CTE tables in JOIN and WHERE clauses, excluding FROM;
• Incorporated WITH support in UPDATE and DELETE statements.

To introduce these features, we've made substantial API updates designed to be backward compatible, ensuring no disruption to existing implementations. However, all users of the CTE APIs should be cognizant of these enhancements.

• CTEQuery vs CTETable:
  • In prior versions, a quirk in the design automatically appended all table names defined in CTEBuilder to the FROM clause of a SELECT statement, which hindered the exclusive use of CTE tables in JOIN or WHERE clauses.
  • Based on the conversation in issue #163, we proposed two solutions and selected the most viable. To summarize, the new API CTEQuery constructs CTE queries without any unintended consequences; meanwhile, CTETable retains its original functionality, effectively turning the previous side effect into a deliberate feature.
• Introduction of CTEQueryBuilder:
  • CTEQueryBuilder is now the sole builder for crafting CTE query expressions, replacing the previous approach.
  • CTETableBuilder has been repurposed as a type alias for CTEQueryBuilder and is deprecated. It is advised that this alias may be phased out in upcoming releases.

What's New

• Recursive CTE support added by @arikkfir in #162
• Overhaul of the CTE API by @huandu in issue #163
• A new method SelectMore in SelectBuilder allows for the addition of further columns to a SELECT statement post the Select() method call.

New Contributors

• @arikkfir made their inaugural contribution in #162

Full Changelog: v1.28.0...v1.29.0