v1.33.0: Support `LATERAL` keyword

What's Changed

• Support LATERAL keyword #181 #183 (Thanks @therve for your ideas)
  • Add a new method LateralAs in SelectBuilder to build LATERAL JOIN expression. Read sample code for LateralAs on GoDoc for details.

Full Changelog: v1.32.0...v1.33.0

v1.32.0: Enhancements and Bug Fixes in SQL Statement Handling and Error Identification

What's Changed

• NEW: Flavor getters by @rodionovv in #177
  • There is a new method Flavor() in Builder to get current flavor set in a builder.
• NEW: Automatically reference names of CTETables in DELETE and UPDATE statements. #179
  • Based on the discussion in issue #176, the capability previously used with CTETable to automatically include the CTE table name in the FROM clause of SELECT statements has now been extended to UPDATE and DELETE statements.
• FIX: Avoid stack overflow when Cond is misused. #180
  • Based on the discussion in issue #178, users may call this method to create Cond for building various conditions, which is a misuse, but we cannot completely prevent this error.
  • To facilitate problem identification for users, in the event of misuse of NewCond(), the generated SQL will include the pattern /* INVALID ARG $n */, where n is the sequence number of the problematic variable, allowing users to quickly pinpoint the issue.

Full Changelog: v1.31.0...v1.32.0

New feature: Ignore empty content to prevent the output of incorrect SQL

What's Changed

• Cond and WhereClause will actively ignore empty content to prevent the output of syntactically incorrect SQL #175 by @rodionovv
  • In previous version, Cond and WhereClause can produce syntax errors when any of required parameters field, op or expr is empty. This release fixes this issue by actively ignore these invalid values.
  • Here are samples affected by this change.
    • Select("*").From("t").Where("").String()
      • Now: SELECT * FROM t
      • Previous version: SELECT * FROM t WHERE
    • sb := Select("*").From("t"); sb.Where(sb.Equal("", 0))
      • Now: SELECT * FROM t
      • Previous version: SELECT * FROM t WHERE = ?

New Contributors

• @rodionovv made their first contribution in #175

Full Changelog: v1.30.0...v1.31.0

New feature: Add "IS [NOT] DISTINCT FROM" comparison operators to `Cond`

What's New

• Add "IS [NOT] DISTINCT FROM" comparison operators to Cond #169
• Automatically build compatible operators for a flavor.
  • If a database system does not support an operator,Cond can automatically build compatible syntax as a substitute.
  • For example, calling IsDistinctFrom("field", value) automatically determines which expression to output based on the current database flavor. If the flavor is PostgreSQL, it builds "field IS DISTINCT FROM $1". For MySQL, which does not support this operator, it builds the functionally equivalent expression "NOT field <=> ?".

Full Changelog: v1.29.1...v1.30.0

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

New feature: Support Common Table Expression and ILIKE

What's Changed

• Implement CTEBuilder and CTETableBuilder to support Common Table Expression (CTE) by @huandu in #157
  • Samples can be found in the document for With and CTEBuilder.
• Add ILIKE helper in SelectBuilder, UpdateBuilder and DeleteBuilder by @abanoub-fathy in #159

New Contributors

• @abanoub-fathy made their first contribution in #159

Full Changelog: v1.27.3...v1.28.0

New feature: New builder `WhereClause` dedicated for WHERE clause

Due to the importance of the WHERE statement in SQL, we often need to continuously append conditions and even share some common WHERE conditions among different builders. Therefore, we abstract the WHERE statement into a WhereClause struct, which can be used to create reusable WHERE conditions.

I hope the WhereClause can help more developers to build more and more valuable SQL/DB related packages on top of this package. It's extremely welcome!

What's Changed

• #147 [NEW] WhereClause: A new dedicated builder for WHERE. Thanks for raising this idea to me in #145, @LeDuyViet.
• #146 Optimize paging syntax for Informix flavor SQL. Thanks, @zhangyongding.

Full Changelog: v1.26.0...v1.27.0

New feature: Support Informix SQL

What's Changed

• #140 Feature: New flavor Informix. Thanks for your contribution, @zhangyongding.

Full Changelog: v1.25.0...v1.26.0

New feature: Return number of required clause in builders

Per discussion in #134, add methods to return number of required clause in builders, so that we can know whether a builder can build a valid SQL. Thanks @0x9ef for raising this issue.

Full Changelog: v1.24.0...v1.25.0

New feature: Support `INSERT INTO ... SELECT` statement

What's Changed

• #133 Add sub-select builder for InsertBuilder to support INSERT INTO ... SELECT statement. Thanks, @cemremengu.

Full Changelog: v1.23.0...v1.24.0