What's New?
Adobe has ended support for ACF 2016, and so must we.
CFML's
uuid does not match other languages; it's one character shorter. Because of this, the value from createUUID() cannot be used in some database column types like SQL Server's uniqueidentifier. This made for some confusion in SchemaBuilder since it wasn't clear if uuid meant CFML's definition or the wider world's definition.So, the types have been split, following Lucee's pattern, into
uuid (matching CFML's createUUID()) and guid (matching Java's UUID or createGUID() on Lucee).Popular grid frameworks like Quasar and Datatables use values of 0 or -1 to return all rows from a query. This is now supported in qb. Previously, it generated an invalid query (
SELECT * FROM users LIMIT 0 OFFSET 0).This behavior can be customized by providing a callback to the
shouldMaxRowsOverrideToAll setting or init argument. For instance, to revert to the previous behavior you would set the function as follows:moduleSettings = {
"qb": {
"shouldMaxRowsOverrideToAll": function( maxRows ) {
return false;
}
}
};
Introduced in 8.10.0, this feature uses separate SQL types for integers and decimals to increase performance in certain database grammars. This feature is now the default, but the previous behavior can be enabled by setting
autoDeriveNumericType to false.Note: the option to revert to the old behavior will be removed in the next major version.
Introduced in 8.1.0, this feature only returns a SQL type of
CF_SQL_TIMESTAMP if the param is a date object, not just a string that looks like a date. This helps avoid situations where some strings were incorrectly interpreted as dates. For many, the migration path is straightforward — calls to now() are already date objects as well as any function that operates on a date. If you need to parse a string as a date, the parseDateTime built-in function can accomplish that.Note: the option to revert to the old behavior may be removed in the next major version.
Thanks to Jason Steinhouer, qb now supports SQLite for both
QueryBuilder and SchemaBuilder. You can use it in your apps by specifying [email protected] as the default grammar.sqlCommenter is a specification by Google for adding contextual information as a comment at the end of a SQL statement. This can give insights into your application, especially when diagnosing slow queries. Examples of the information you can append to your queries are
route, handler, action, version, and others, as well as the ability to add your own, such as loggedInUser and more.There's a new shortcut method to return
qb.sum( qb.raw( expression ) ). You're welcome. 😉Some grammars, like SQL Server, do not treat simple indexes as constraints. For this reason, we've added a
dropIndex method alongside the existing dropConstraint.columnList will return either an array of column names for the configured table or the query that is generated by cfdbinfo for the configured table. Especially useful when working with dynamically generated grids. - Correctly compile
insertUsingstatements that use Common Table Expressions (CTEs). - Update
announceInterceptioncalls for ColdBox 7. (Thank you, Michael Born.) - Fixed
insertUsingnot placing Common Table Expressions (CTEs) in the correct order. - Don't add
DISTINCTwhen doing aCOUNT(*). - Support aggregates for unioned queries.
- There are now specific numeric SQL types for integers and decimals used during the
inferSQLTypecheck inQueryUtils. This is an opt-in feature, enabled by setting theautoDeriveNumericTypesetting. The previous approach was to useCF_SQL_NUMERICfor all numeric types which could cause performance issues in some grammars as they interpreted allCF_SQL_NUMERICas floating point numbers.
HOLDLOCKandREADPASTare mutually exclusive table locks in SQL Server but were mistakenly being applied together.
- Don't uppercase quoted aliases in Oracle.
- Fix for aliases in update statements.
- Don't sort columns for
insertUsing. - Add subquery bindings in insert and upsert statements.
- Maintain column order when using source in upsert.
- Fix for Oracle returning custom column types when renaming a column.
- Explicit arguments scoping.
arrayEachis slow compared to merging arrays.
- Fix wheres with joins in update statements.
- Add better null handling to
inferSqlType.
- Correctly format columns being updated.
- Add an upsert method.
upsertcan update or insert multiple records at once depending on if a column is matched.
- Correctly wrap CTE expressions with parenthesis when required in certain grammars.
SchemaBuildercan now be configured with default query options. (Default options will still be overridden by options passed to eachSchemaBuildermethod.)
- Correct return aggregate values for date values from
maxandminexecutors. - Correctly format a
COUNT(DISTINCT column)query. - Only use bulk insert syntax when needed in OracleGrammar due to interactions between the
resultparameter tocfquery, Lucee, and the Oracle JDBC driver.
- Swap
masterbranch tomainbranch.
- Remove unnecessary injection for QueryUtils.
- Account for raw expressions when generating mementos for comparison
- Migrate release process to GitHub Actions.
- Add a
simplePaginatepagination method for quicker performance when total records or total pages are not needed or too slow.
- Default to
htmlfor thedumpformat argument towriteDump.
- Correctly use the passed in
strictDateDetectionto theQueryUtils.cfc.
- A new, optional
strictDateDetectionsetting is available to check the underlying Java class of a date object instead of usingisDate.
- Ignore select bindings for aggregate queries.
- Allow spaces in table aliases.
- Split FLOAT and DECIMAL column types in SQL Server.
- Clear orderBy bindings when calling
clearOrders.
- Trim table definitions before searching for aliases. Makes qb more lenient with extra whitespace.
- Expose nested where functions to enable advanced query manipulation in downstream libraries like Quick.
- Fixes for OracleGrammar including table aliases and wrapped subqueries.
- Handle enhanced numeric checks with Secure Profile enabled.
- Allow raw statements in basic where clauses.
- Handle multi-word columns in
queryRemoveColumns.
- Remove elvis operator due to ACF compatibility issues
- Compatibility fix for ACF 2018 and
listLastparsing. - Ignore table qualifiers for insert and update.
- Fix a bug with preventDuplicateJoins when using the closure syntax with a join.
- Add executionTime to the data output from BaseGrammar, including being available in interceptors.
- Fix a case where a column was not wrapped correctly when a
whereused a subquery for the value.
- Avoid
duplicatefunction due to cbORM / Hibernate bugs when used in the same application.
- Split off a private
whereBasicmethod. This is used in Quick to provide extra sql type features. - Add a
clearOrdersmethod. Any already configured orders are cleared. Any orders added after this call will be added as normal.
Fixed an issue using column formatters with
update and insert.Using a new
preventDuplicateJoins setting in the module settings, qb can detect duplicate joins and ignore them. This is especially useful in a heavily filtered and dynamic query where you may or may not need the join at all or more than one column may need the same join. preventDuplicateJoins defaults to false, so it is opt-in. It may be turned on by default in a future breaking release of qb.You can now use two shortcut methods:
orderByAsc and orderByDesc. Additionally, orderBySub or using orderBy with a closure or builder instance will respect the direction argument.- Fix for null values breaking the new
checkIsActuallyNumericmethod inQueryUtils.
- Add a
parameterLimitpublic property toSqlServerGrammar. This property is used in Quick to split up eager loading to work around the 2100 param limit of SQL Server.
- Allow a parent query to be set. A parent query will receive any method calls that are not found on the Query Builder instance. This is especially useful for instances like Quick to allow Quick features like scopes to be available inside any closures.
- Lambdas (arrow functions) are now allowed wherever closures are allowed.
- Drop support for Lucee 4.5 and Adobe ColdFusion 11.
MSSQLGrammarrenamed toSqlServerGrammar- Remove variadic parameters support in builder functions like
select. - The
defaultGrammarmapping needs to be the full WireBox mapping, including the@qb, if needed.- For instance,
MSSQLGrammarwould become[email protected]. - This will allow for other grammars to be more easily contributed via third party modules.
- The argument names of
forPagechanged to match the newpaginatemethod. - Add
defaultValueand optional exception throwing tovalue. (This changed the argument order.) - All methods that could conceivably take a subquery as well as a value now accept a closure or another builder instance to use as a subquery. (This changed the argument names in some instances.)
- Completely revamped documentation! (You're looking at it right now.)
- Add new flag to
toSQL( showBindings = true )to replace question marks (?) withcfqueryparam-compatible structs for debugging. - Preserve column case and order when converting a query to an array using the default
"array"return format. - Add a new paginate method to generate a pagination struct alongside the results. This can be customized using a custom PaginationCollector.
- Allow default
queryExecuteoptionsto be configure at a Query Builder level. This also enables customQueryBuildersa la Hyper. - Allow closures to be used in left and right joins.
- Add
rawinalterTablesegments. - Add
dropAllObjectssupport forSqlServerGrammarandOracleGrammarto supportmigrate freshfrom cfmigrations. - Add a
renameTablealias forrename. - Remove default constraints when dropping columns with a default on
SqlServerGrammar. - Add more column types and column helpers to
SchemaBuilder, including:datetimeTzlineStringnullableTimestampspointpolygonsoftDeletessoftDeletesTztimeTztimestampstimestampTztimestampsTzwithCurrent
****
Last modified 2mo ago