FORGEBOX Enterprise 🚀 - Take your ColdFusion (CFML) Development to Modern Times! Learn More...

qb

v8.0.3 Public

qb

Build Status

Introduction

qb is a fluent query builder for CFML. It is heavily inspired by Eloquent from Laravel.

Using qb, you can:

  • Quickly scaffold simple queries
  • Make complex, out-of-order queries possible
  • Abstract away differences between database engines

Requirements

  • Adobe ColdFusion 2016+
  • Lucee 5+

Installation

Installation is easy through CommandBox and ForgeBox. Simply type box install qb to get started.

Code Samples

Compare these two examples:

// Plain old CFML
q = queryExecute("SELECT * FROM users");

// qb
query = wirebox.getInstance('QueryBuilder@qb');
q = query.from('users').get();

The differences become even more stark when we introduce more complexity:

// Plain old CFML
q = queryExecute(
    "SELECT * FROM posts WHERE published_at IS NOT NULL AND author_id IN ?",
    [ { value = '5,10,27', cfsqltype = 'CF_SQL_NUMERIC', list = true } ]
);

// qb
query = wirebox.getInstance('QueryBuilder@qb');
q = query.from('posts')
         .whereNotNull('published_at')
         .whereIn('author_id', [5, 10, 27])
         .get();

With Quick you can easily handle setting order by statements before the columns you want or join statements after a where clause:

query = wirebox.getInstance('QueryBuilder@qb');
q = query.from('posts')
         .orderBy('published_at')
         .select('post_id', 'author_id', 'title', 'body')
         .whereLike('author', 'Ja%')
         .join('authors', 'authors.id', '=', 'posts.author_id')
         .get();

// Becomes

q = queryExecute(
    "SELECT post_id, author_id, title, body FROM posts INNER JOIN authors ON authors.id = posts.author_id WHERE author LIKE ? ORDER BY published_at",
    [ { value = 'Ja%', cfsqltype = 'CF_SQL_VARCHAR', list = false, null = false } ]
);

qb enables you to explore new ways of organizing your code by letting you pass around a query builder object that will compile down to the right SQL without you having to keep track of the order, whitespace, or other SQL gotchas!

Here's a gist with an example of the powerful models you can create with this! https://gist.github.com/elpete/80d641b98025f16059f6476561d88202

Full Docs

You can browse the full documentation at https://qb.ortusbooks.com

Dependencies (1)


Dev Dependencies (1)


v8.0.3

17 Aug 2020 — 16:40: 49 UTC

fix

  • QueryBuilder: Ignore select bindings for aggregate queries (8a3a181)
  • BaseGrammer: Allow spaces in table aliases. (b06d690)
  • SqlServerGrammar: Split FLOAT and DECIMAL column types (82da682)

v8.0.2

13 Aug 2020 — 19:46: 21 UTC

fix

  • QueryBuilder: Clear order by bindings when calling clearOrders (f1e941a)

v8.0.1

28 Jul 2020 — 05:33: 09 UTC

fix

  • QueryBuilder: Add bindings from orderBySub expressions (77213a6)

v8.0.0

22 Jul 2020 — 16:39: 51 UTC

BREAKING

  • QueryBuilder: Automatic where scoping with OR clauses in when callbacks (0d6292d)

feat

  • QueryBuilder: Add reselect methods (81d987d)
  • QueryBuilder: Add a reorder method (69d6c5d)

v7.10.0

22 Jul 2020 — 04:57: 44 UTC

feat

  • QueryBuilder: Expose nested where functions (71ca350)

v7.9.9

21 Jul 2020 — 21:08: 35 UTC

fix

  • OracleGrammar: Correcly wrap all subqueries and aliases (3e9210f)

v7.9.8

17 Jul 2020 — 21:07: 34 UTC

fix

  • MySQLGrammar: Allow nullable timestamps in MySQL (ceb96a1)

v7.9.7

10 Jul 2020 — 05:50: 14 UTC

fix

  • QueryBuilder: Return 0 on null aggregates (ee10a67)

v7.9.6

08 Jul 2020 — 05:28: 16 UTC

fix

  • QueryBuilder: Match type hints to documentation for join functions (a23a1b6)

v7.9.5

07 Jul 2020 — 19:16: 32 UTC

fix

  • QueryUtils: Handle numeric checks with Secure Profile enabled (a849525)

v7.9.4

29 Jun 2020 — 19:19: 27 UTC

fix

  • QueryBuilder: Allow raw statements in basic where clauses (18200ec)

v7.9.3

27 Jun 2020 — 19:24: 33 UTC

fix

  • QueryBuilder: Added options structure to count() method call in paginate (99201fb)

v7.9.2

19 Jun 2020 — 03:52: 35 UTC

fix

  • QueryBuilder: Allow for space-delimited sort directions (5530679)
  • QueryBuilder: Add helpful message when trying to use a closure with 'from' (a8e7bb4)
  • QueryBuilder: 'value' and 'values' now work with column formatters (da60695)
  • QueryBuilder: Correctly format RETURNING clauses (977edcf)

v7.9.1

18 Jun 2020 — 22:37: 57 UTC

fix

  • QueryUtils: Handle multi-word columns in queryRemoveColumns (69d3058)
  • QueryBuilder: Fix incorrect structAppend overwrites (ad770d2)

v7.9.0

11 Jun 2020 — 22:59: 22 UTC

chore

  • Format: Format with cfformat (77af3ee)
  • format: Always use lf for new lines (707a288)
  • CI: Testing coldbox@be makes no sense as it's all unit tests (8b335da)
  • CI: Add coldbox@be testing (e14af28)
  • BaseGrammar: Inline null services (4ccad99)
  • QueryBuilder: Fix tests on ACF 2016 due to @default metadata. (29b31d0)

feat

  • SchemaBuilder: Add support for MONEY and SMALLMONEY data types (24aadec)
  • BaseGrammar: Add executionTime to the data output, including interceptors (25d66d7)
  • Select: selectRaw now can take an array of expressions (d5b00af)
  • Orders: Add a clearOrders method (507dfdb)
  • Joins: Optionally prevent duplicate joins from being added (40212ff)
  • QueryBuilder: Enhance order by's with more direction options (c767ac8)

fix

  • OracleGrammar: Remove elvis operator due to ACF compatibility issues (e4b27b8)
  • PostgresGrammar: Update enum tests for Postgres (c50b00b)
  • PostgresGrammar: Fix wrapping of enum types (2d65e08)
  • QueryBuilder: Compat fix for ACF 2018 and listLast parsing (d30c8cd)
  • SchemaBuilder: Include current_timestamp default for timestamps (9f9a6c9)
  • QueryBuilder: Ignore table qualifiers for insert and update (466d791)
  • JoinClause: Prevent duplicate joins when using closure syntax (8f5028a)
  • BaseGrammar: Fix a case where a column was not wrapped correctly (e4fcff4)
  • QueryBuilder: Avoid duplicate due to Hibernate bugs (ec429ba)
  • QueryBuilder: Upgrade cbpaginator to fix maxrows discrepency (085c8a6)
  • BaseGrammar: Fix using column formatters with updates and inserts (e4fb585)
  • QueryBuilder: Fix using with query param structs (07c9b72)
  • QueryBuilder: Ignore orders in aggregate queries (39e1338)
  • BaseGrammar: Improve column wrapping with trimming (d98a5cb)
  • QueryBuilder: Prefer the parent query over magic methods (f9fd8d1)

other

  • *: fix: Format with cfformat (dc2a9b6)
  • *: fix: Update gitignore to account for folder paths (382c16b)
  • *: chore: Adjust ignore files (e5702ed)

refactor

  • QueryBuilder: Split off a whereBasic method (36d87b3)

v7.8.0

17 May 2020 — 13:31: 14 UTC

feat

  • SchemaBuilder: Add support for MONEY and SMALLMONEY data types (24aadec)

v7.7.3

17 May 2020 — 07:33: 29 UTC

fix

  • PostgresGrammar: Update enum tests for Postgres (c50b00b)
  • PostgresGrammar: Fix wrapping of enum types (2d65e08)

v7.7.2

08 May 2020 — 21:26: 38 UTC

fix

  • QueryBuilder: Compat fix for ACF 2018 and listLast parsing (d30c8cd)
  • SchemaBuilder: Include current_timestamp default for timestamps (9f9a6c9)
  • QueryBuilder: Ignore table qualifiers for insert and update (466d791)

v7.7.1

04 May 2020 — 16:50: 13 UTC

fix

  • JoinClause: Prevent duplicate joins when using closure syntax (8f5028a)

v7.7.0

03 May 2020 — 16:43: 06 UTC

feat

  • BaseGrammar: Add executionTime to the data output, including interceptors (25d66d7)

v7.6.2

30 Apr 2020 — 21:19: 47 UTC

fix

  • BaseGrammar: Fix a case where a column was not wrapped correctly (e4fcff4)

v7.6.1

28 Apr 2020 — 21:46: 32 UTC

fix

  • QueryBuilder: Avoid duplicate due to Hibernate bugs (ec429ba)

v7.6.0

31 Mar 2020 — 02:50: 36 UTC

chore

  • format: Always use lf for new lines (707a288)

feat

  • Select: selectRaw now can take an array of expressions (d5b00af)
  • Orders: Add a clearOrders method (507dfdb)

refactor

  • QueryBuilder: Split off a whereBasic method (36d87b3)

v7.5.2

25 Mar 2020 — 15:10: 13 UTC

chore

  • CI: Testing coldbox@be makes no sense as it's all unit tests (8b335da)

fix

  • QueryBuilder: Upgrade cbpaginator to fix maxrows discrepency (085c8a6)

v7.5.1

25 Mar 2020 — 01:57: 21 UTC

chore

  • CI: Add coldbox@be testing (e14af28)

fix

  • BaseGrammar: Fix using column formatters with updates and inserts (e4fb585)

v7.5.0

12 Mar 2020 — 15:54: 22 UTC

feat

  • Joins: Optionally prevent duplicate joins from being added (40212ff)

v7.4.0

12 Mar 2020 — 15:33: 10 UTC

feat

  • QueryBuilder: Enhance order by's with more direction options (c767ac8)

v7.3.15

04 Mar 2020 — 15:00: 38 UTC

fix

  • QueryBuilder: Fix using with query param structs (07c9b72)

v7.3.14

28 Feb 2020 — 18:38: 25 UTC

fix

  • QueryBuilder: Ignore orders in aggregate queries (39e1338)

v7.3.13

28 Feb 2020 — 07:59: 08 UTC

other

  • *: fix: Format with cfformat (dc2a9b6)

v7.3.12

28 Feb 2020 — 05:37: 32 UTC

chore

  • BaseGrammar: Inline null services (4ccad99)
  • QueryBuilder: Fix tests on ACF 2016 due to @default metadata. (29b31d0)

fix

  • BaseGrammar: Improve column wrapping with trimming (d98a5cb)
  • QueryBuilder: Prefer the parent query over magic methods (f9fd8d1)

v7.3.11

13 Feb 2020 — 19:45: 42 UTC

other

  • *: fix: Update gitignore to account for folder paths (382c16b)

v7.3.10

13 Feb 2020 — 19:35: 23 UTC

other

  • *: chore: Adjust ignore files (e5702ed)

v7.3.9

13 Feb 2020 — 17:34: 56 UTC

other

  • *: chore: Use forgeboxStorage (191c732)

v7.3.8

07 Feb 2020 — 06:19: 42 UTC

fix

  • Pagination: Allow passing query options in to paginate (cdecfb3)

v7.3.7

30 Jan 2020 — 16:50: 04 UTC

fix

  • QueryBuilder: Fix for inserting null values directly (1de27a6)

v7.3.6

27 Jan 2020 — 22:04: 59 UTC

fix

  • formatting: Futher fixes with cfformat (b4d74b3)

v7.3.5

25 Jan 2020 — 08:09: 54 UTC

chore

  • formatting: Use cfformat for automatic formatting (119e434)

fix

  • QueryBuilder: Add a type to the onMissingMethod exception (90d1093)

v7.3.4

13 Jan 2020 — 18:21: 30 UTC

fix

  • MySQLGrammar: Use single quote for column comment (7304202)

v7.3.3

09 Jan 2020 — 20:28: 40 UTC

chore

  • build: Skip cleanup of working directory before uploading APIDocs (1c2d0d3)

v7.3.2

09 Jan 2020 — 20:16: 18 UTC

chore

  • build: Commit apidocs to Ortus artifacts (636af8b)

v7.3.1

07 Jan 2020 — 17:58: 00 UTC

other

  • *: fix(QueryUtils): Account for null values when checking for numeric values (42f2eb4)

v7.3.0

03 Jan 2020 — 06:06: 16 UTC

feat

  • SqlServerGrammar: Add a parameterLimit public property (155cd3c)

v7.2.0

02 Jan 2020 — 06:11: 35 UTC

feat

  • QueryBuilder: Add a parentQuery option (f84de76)

v7.1.0

31 Dec 2019 — 01:40: 01 UTC

feat

  • QueryBuilder: Fully-qualified columns can be used in value and values (e4c16b8)
  • QueryBuilder: Add orderByRaw method (67a9222)

fix

  • QueryBuilder: Accept lambdas where closures are allowed. (f88809b)

v7.0.0

20 Dec 2019 — 05:53: 14 UTC

BREAKING

  • QueryUtils: Improve numeric sqltype detection (74649bd)
  • QueryBuilder: Add pagination collectors to qb (4b2d85f)
  • MSSQLGrammar: Rename MSSQLGrammar to SqlServerGrammar (ea94494)
  • QueryBuilder: Rename callback to query for subSelect (87b27f5)
  • QueryBuilder: Expand closure and builder argument options (e002d94)
  • QueryBuilder: Add defaultValue and optional exception to value (ec23bb7)
  • ModuleConfig: Use full setting for WireBox mapping (1e14099)
  • QueryBuilder: Remove variadic parameter support (8690fcf)
  • *: refactor: Drop support for ACF 11 and Lucee 4.5 (9dbeaf3)

chore

  • tests: Add code coverage with FusionReactor (6e6600f)
  • README: Remove unused all-contributors information (e84addd)

feat

  • SchemaBuilder: Add methods to manage views (1ef8f82)
  • QueryUtils: Preserve column case and order in conversion (00cd691)
  • QueryBuilder: Generate SQL strings with bindings (2c84afb)
  • QueryBuilder: Distinct can now be toggled off (7255fa3)
  • SchemaBuilder: Add more column types (c9c4678)
  • MSSQLGrammar: Remove default constraint when dropping columns (88bfe81)
  • SchemaBuilder: Add renameTable alias for rename (e2c796e)
  • OracleGrammar: Add dropAllObjects and migrate fresh support (7fe3429)
  • MSSQLGrammar: Add support for dropAllObjects and migrate fresh (719e264)
  • QueryBuilder: Add database chunking (2a20ba4)
  • QueryBuilder: Use addUpdate to progressively add columns to update (65ad791)
  • QueryBuilder: Add whereLike method (ec12a2a)
  • QueryBuilder: Allow default options to be configured (34db905)
  • QueryBuilder: Allow raw values in inserts (bae3435)

fix

  • QueryBuilder: Better whitespace splitting for select lists (6f771e3)
  • QueryUtils: Fix array normalization to handle non-string inputs (01613c4)
  • QueryBuilder: Trim select columns string before applying (d6cbf36)
  • QueryBuilder: Fix cbpaginator instantiation path (9a8f03a)
  • QueryBuilder: Fix typo in docblock (97c8785)
  • QueryBuilder: Fix docblock name (79b96c6)
  • QueryBuilder: Pass paginationCollector and defaultOptions to newQuery (bccbc40)
  • QueryBuilder: Explicitly set andWhere methods to use the 'and' combinator (adce834)
  • QueryBuilder: Allow any custom function for where (fb01927)
  • SchemaBuilder: Allow raw in alter statements (2202828)
  • QueryBuilder: Allow closures to be used with leftJoin and rightJoin (e7ddf2f)

other

  • *: refactor: Remove unneeded clearExcept argument (0b90157)

perf

  • QueryBuilder: Use count to determine exists instead of the full query (d51ecf4)

refactor

  • QueryBuilder: Handle all andWhere.* and orWhere.* methods dynamically (cc560af)
  • QueryBuilder: Remove unnecessary arguments from crossJoin methods (f920d1b)

v6.5.0

05 Sep 2019 — 18:45: 38 UTC

feat

  • QueryBuilder: Add a performant clone method (f1b367a)

fix

  • Utils: Preserve column casing when removing columns (433df5d)

v6.4.1

04 Sep 2019 — 21:14: 02 UTC

build

  • travis: Use openjdk for builds (061e9d0)

fix

  • namespaces: Fix for ACF 11 namespaces (784855c)
  • OracleGrammar: Fix for removing generated columns from insert and updates (f4ab485)

v6.4.0

12 Jul 2019 — 03:46: 03 UTC

feat

  • QueryBuilder: Allow raw values in updates (5f287b9)

v6.3.4

12 Jun 2019 — 04:52: 37 UTC

fix

  • QueryBuilder: Make operator and combinator checks case-insensitive (a90b944)

other

v6.3.3

09 May 2019 — 19:51: 30 UTC

chore

  • APIDocs: Don't nest API docs (dc6bde8)

other

v6.3.2

06 May 2019 — 21:09: 24 UTC

fix

  • PostgresGrammar: Only drop tables in the current schema (0866f9a)

other

v6.3.1

06 May 2019 — 19:50: 34 UTC

fix

  • PostgresGrammar: Use correct detection of tables in schemas (10408a1)

other

v6.3.0

03 May 2019 — 15:59: 35 UTC

feat

  • Subselect: Allow passing query objects to subselect (d2fb971)

other

v6.2.1

30 Apr 2019 — 18:55: 17 UTC

fix

  • QueryBuilder: Revery using array returntype where available (d4fea1d)

other

v6.2.0

30 Apr 2019 — 01:25: 18 UTC

feat

  • QueryBuilder: Use array returntype where available (2c45627)

other

v6.1.0

10 Apr 2019 — 17:56: 24 UTC

feat

  • QueryBuilder: Add a columnFormatter option (984da75)

other

v6.0.4

20 Dec 2018 — 01:57: 03 UTC

fix

  • QueryBuilder: Correctly keep where bindings for updateOrInsert (fa9fab6)

other

v6.0.3

11 Dec 2018 — 22:33: 30 UTC

other

perf

  • BaseGrammar: Remove the need for duplicate or structCopy calls (89ea9fc)

v6.0.2

30 Nov 2018 — 13:41: 18 UTC

fix

  • BaseGrammar: Fix for when the query object is null (efb3917)

other

v6.0.1

29 Nov 2018 — 22:51: 44 UTC

other

perf

  • SchemaBuilder: Replace duplicate() with structCopy() (d0237c8)

v6.0.0

28 Nov 2018 — 05:10: 44 UTC

BREAKING

  • SchemaBuilder: Use uniqueidentifier for MSSQL uuid() (1b2d456)

feat

  • QueryBuilder: Add returning functionality for compatible grammars (7b12b02)

fix

  • SchemaBuilder: Default values respect column types (ae2fc4b)
  • SchemaBuilder: Wrap enum values in single quotes (89b58c4)
  • QueryBuilder: Add missing andWhere methods (7273ce4)

other

perf

  • SchemaBuilder: Removed case of isInstanceOf because it is slow (2d65d03)

v5.8.1

17 Sep 2018 — 21:14: 15 UTC

fix

  • SchemaBuilder: Fix incorrect column name for hasTable and hasColumn (292bc2a)

other

v5.8.0

17 Sep 2018 — 19:23: 06 UTC

feat

  • SchemaBuilder: Add unicode text functions (1a5207e)
  • Logging: Add debug logging for query sql and bindings. (2928feb)

fix

  • SchemaBuilder: Update UUID length to 36 characters (2569f82)
  • MSSQLGrammar: Replace NTEXT with NVARCHAR(MAX) (936b01d)

other

perf

  • QueryBuilder: Remove isInstanceOf for performance benefits (33fe75c)

refactor

  • InterceptorService: Use a null interceptor service in the constructor (5f3a3ec)

v5.7.0

18 Aug 2018 — 05:10: 01 UTC

chore

  • README: Remove emoji until ForgeBox can handle it again (70f2d45)
  • Changelog: Fix Changelog to rerun build (2b6aaa3)

feat

  • QueryBuilder: Add support for Common Table Expressions (3e10da6)
  • QueryBuilder: Derived and Sub Tables (b3f0461)
  • QueryBuilder: Unions (59028a8)

fix

  • QueryBuilder: Fix JoinClause return value (5d113c7)

other

v5.7.0

18 Aug 2018 — 04:28: 41 UTC

feat

  • QueryBuilder: Add support for Common Table Expressions

Add CTE support for the with CTE AS (...) syntax (3e10da6)

  • QueryBuilder: Derived and Sub Tables
  • Fixed JoinClause.newQuery() to expect QueryBuilder object as return value
  • Added support for derived tables
  • Added derived table support
  • Added fromRaw() method, which allows you to raw SQL "from" statements
  • Added fromSub() to support derived tables
  • Added joinRaw(), leftJoinRaw(), rightJoinRaw() and crossJoinRaw() for defining the raw SQL
  • Added the joinSub(), leftJoinSub(), rightJoinSub() and crossJoinSub() for joining to a derived table
  • Added mergeBindings() which is used for merging bindings from another QueryBuilder instance (b3f0461)
  • QueryBuilder: Unions
  • Fixed JoinClause.newQuery() to expect QueryBuilder object as return value
  • Added support for UNION/UNION ALL
  • Union statement is not created until after ORDER BY validation (59028a8)

fix

  • QueryBuilder: Fix JoinClause return value

Fixed JoinClause.newQuery() to expect QueryBuilder object as return value (#48) (5d113c7)

v5.8.0

17 Aug 2018 — 21:33: 47 UTC

chore

  • ci: Fix flakey gpg keys (51d8c27)
  • ci: Test on adobe@2018 (d928b4b)
  • README: Update references to elpete to coldbox-modules (bc7c99c)
  • build: Enable commandbox-semantic-release (0fe689f)
  • box.json: Update references to coldbox-modules repo (7eb1a31)
  • build: Update Travis CI release process (e743833)

docs

  • box.json: Remove extra period in description

Remove period as it is not needed for a single sentance (87347c7)

errors

  • schema: Better error message when passing in a TableIndex to create column (f91a3f7)

feat

  • QueryBuilder: Add support for Common Table Expressions

Add CTE support for the with CTE AS (...) syntax (3e10da6)

  • QueryBuilder: Derived and Sub Tables
  • Fixed JoinClause.newQuery() to expect QueryBuilder object as return value
  • Added support for derived tables
  • Added derived table support
  • Added fromRaw() method, which allows you to raw SQL "from" statements
  • Added fromSub() to support derived tables
  • Added joinRaw(), leftJoinRaw(), rightJoinRaw() and crossJoinRaw() for defining the raw SQL
  • Added the joinSub(), leftJoinSub(), rightJoinSub() and crossJoinSub() for joining to a derived table
  • Added mergeBindings() which is used for merging bindings from another QueryBuilder instance (b3f0461)
  • QueryBuilder: Unions
  • Fixed JoinClause.newQuery() to expect QueryBuilder object as return value
  • Added support for UNION/UNION ALL
  • Union statement is not created until after ORDER BY validation (59028a8)
  • SchemaBuilder: Allow an optional schema to hasTable and hasColumn

Since some users have access to multiple schemas on the same database, allow an optional schema parameter passed to hasTable and hasColumn (9bfcd45)

  • QueryBuilder: Add andWhere method for more readable chains.

andWhere behaves exactly like where. It is provided for a more readable method chain if desired. (309f4d8)

  • AutoDiscover: Allow for runtime discovery

Add AutoDiscover component to allow for database discovery at runtime as opposed to just at module registration. (700948a)

  • ModuleConfig: Auto discover grammar by default.

By default, we will auto discover the grammar for the user. This only happens once for ColdBox modules, so the database hit should be minimal. If the user specifies a grammar in their settings, we will use that and not even try to detect the grammar. (b2347ae)

  • Grammar: Added official support for MSSQL, Oracle, and Postgres. (#34)

Full QueryBuilder and SchemaBuilder support for all four database grammars (MSSQL, MySQL, Oracle, and Postgres). Revamped test suite to have consistent grammar test coverage. (733dae3)

  • SchemaBuilder: Add dropAllObjects action. (#31)

compileDropAllObjects needs to be implemented in every Grammar. By default, it throws an exception. Only a MySQLGrammar implementation currently exists. (c3e23b5)

fix

  • QueryBuilder: Fix JoinClause return value

Fixed JoinClause.newQuery() to expect QueryBuilder object as return value (#48) (5d113c7)

  • Column: Explicitly name default constraint for MSSQL
  • update MSSQL for DEFAULT constraint (288bd66)
  • PostgresGrammar: Fix typo in getAllTableNames (91caf6a)
  • SchemaBuilder: Fix dropping foreign keys in MySQL (8895447)
  • ModuleConfig: Fix logic for determining CommandBox vs ColdBox environment (5c66466)
  • ModuleConfig: Add PostgresGrammar alias to WireBox (eca03f0)
  • QueryBuilder: Preserve returnFormat when creating a new builder (4538947)
  • MySQLGrammar: Default to CURRENT_TIMESTAMP for timestamp columns (#32)

(680750a)

other

  • *: added last() (5b0fe28)
  • *: Update references from Builder to QueryBuilder (632e697)
  • *: Updated API Docs (8325db5)
  • *: 5.0.2 (c8cab5d)
  • *: 5.0.1 (75def91)
  • *: 5.0.0 (d944eb7)
  • *: Add @tonyjunkes as a contributor (1adbbba)
  • *: Updated API Docs (e0ebc41)
  • *: 5.0.0 (dbcaf8a)
  • *: Updated API Docs (dbc7eb5)
  • *: 4.1.0 (b014875)
  • *: renameConstraint can take TableIndex instances as well as strings to rename a constraint (4e9476d)
  • *: Greatly simplify drop column (3fd4c39)
  • *: Add rename index (296cc43)
  • *: Rename removeConstraint to dropConstraint (4cfbaff)
  • *: Allowing adding multiple constraints in the same alter call (1d60df4)
  • *: Organize code (543eaa9)
  • *: Add doc blocks for table constraint methods (10fe437)
  • *: Alphabetize the table constraint methods (06e5d1c)
  • *: Add docblocks to TableIndex (60c7538)
  • *: Change default onUpdate and onDelete actions to NO ACTION. (3a8d7a5)
  • *: Add docblocks to Column (ad71086)
  • *: Remove hasPrecision file and do it manually for a cleaner Column class. (e5fc961)
  • *: Streamline uuid type to be just CHAR(35) (683cd36)
  • *: Refactor all the integers to have the same signature (28d5b81)
  • *: Add docblocks to SchemaBuilder (64cecd5)
  • *: Rename build to execute (cc09aaa)
  • *: Add missing semicolon (d1d0066)
  • *: CommandBox / ColdBox cross-compatibility updates (7d06660)
  • *: Fix typo in the sql method call (c2295d6)
  • *: Finish up foreign key dsl (4cf869a)
  • *: Fix foreign key dynamic names (7b0f9f0)
  • *: Add primary key dsl (25ba4e7)
  • *: Fix spacing in basic indexes and enum lists (4c2cf71)
  • *: Make index names more globally unique. (b7b6636)
  • *: Fix primary index names to include table names if no override provided (d63a4ae)
  • *: Add basic table index support (f247e15)
  • *: Remove constraints by name or index object (dffee36)
  • *: Add unique constraing for columns and tables.

Includes refactor for TableIndex to always deal with multiple columns. (61306e0)

  • *: Add hasTable and hasColumn support for MySQL and Oracle (2c4e5d0)
  • *: Add test for multiple table changes at once. (cf65e11)
  • *: Enable adding columns to an existing table (a54eb86)
  • *: Add modifyColumn syntax (1d64624)
  • *: Add raw method for SQL escape hatch (d77a729)
  • *: Rename columns

MySQL has an unfortunate syntax that requires the definition to be repeated. We may be able to discover this from the table, but right now we're punting and asking the user to redeclare the column definition.

Fun fact, in MySQL, renameColumn will let you modifyColumn at the same time. (0bb926e)

  • *: Add rename tables functionality (daa13fa)
  • *: Add drop multiple columns

Also refactor SchemaCommands to a component that can take arbitrary parameters. (f7a7fce)

  • *: Organize code a bit (d6170a3)
  • *: Drop a column from an existing table

(f8940bc)

  • *: Add dropIfExists support

(8b175c7)

  • *: Add drop table command

(b77781a)

  • *: Refactor order of arguments in create

Since build should be overridden less often than options, make it last. (3ee5dca)

  • *: Rename Grammar to BaseGrammar

Fits better with our current documentation and ModuleConfig.cfc settings (1bd2dcb)

  • *: Add indexes for morphs and nullableMorphs (5de9ee3)
  • *: Convert schema builder to allow for multiple sql statements. (c9c6405)
  • *: Add work in progress nullable implementation.

Still needs index creation. (735a03a)

  • *: Add column modifiers — comment, default, nullable, unsigned (25fbade)

  • *: Add uuid type (f35e1f1)

  • *: Add big, medium, small, and tiny integer and increments variants. (2bb379d)

  • *: Add medium and long text types (35b7d83)

  • *: Add json type (alias to TEXT) (6403d3f)

  • *: Add float type (86cc974)

  • *: Add enum type. (e2f17ab)

  • *: Add decimal type (aa13c72)

  • *: Add bit type (48d0044)

  • *: Have boolean be it's own type so different grammars can interpolate it differently. (c909f9f)

  • *: Add date, datetime, time, and timestamp types. (857bdcf)

  • *: Add char and string types (5732161)

  • *: Add integer, unsignedInteger, increments, and text types (fb76853)

  • *: Add more column types for schema builder

  • bigIncrements

  • bigInteger

  • boolean

  • tinyInteger

  • unsignedBigInteger (3f80002)

  • *: Initial Schema Buidler implementation

Move Grammars from being nested inside Query to it's own top-level folder. Rename Builder to QueryBuilder. Create SchemaBuilder, Column, and TableIndex and three basic tests. (8a299f6)

  • *: Rename Grammar to BaseGrammar to fit the rest of the documentation. (365e32a)
  • *: Update README.md (872355e)
  • *: Add codesponsor.io banner (a54966a)
  • *: Updated API Docs (a992557)
  • *: 4.0.1 (b479473)
  • *: Update with new docs link (f1c04c6)
  • *: Fixed a bug where calling toSQL would modify the builder object.

Affected debugging and things like updateOrInsert where the update call is preceded by an exists call. (c00ecef)

  • *: Fix for insert bindings including other binding types as well (c84ec6c)
  • *: Add @BluewaterSolutions as a contributor (92dd7ad)
  • *: Fix exists method to work across engines

Use the withReturnFormat( "array" ) to get around inconsistencies with queries across CFML engines. (17afdfa)

  • *: Normalize line endings and trim whitespace at the end of lines (bf4ecc7)
  • *: Allow lists to be passed in to whereIn (d0cc901)
  • *: Updated API Docs (bf83436)
  • *: 4.0.0 (ca7049f)
  • *: Fix bug when checking for a "*" column and it was actually an Expression.

Closes #17 (2edaf30)

  • *: Add subSelect method

Closes #18 (79343a0)

  • *: Add returnObject parameter to assist in returning the generated keys from insert statements (dc5242f)
  • *: Add preQBExecute and postQBExecute interception points.

Perfect for logging all queries that are executed!

interceptData includes: sql, bindings, and options. (0c964e5)

  • *: BREAKING CHANGE: Have first return a struct instead of an array.

Closes #20. (4b46fce)

  • *: Add profiling test tooling (25286fd)
  • *: Updated API Docs (aa9db53)
  • *: 3.0.0 (993b0c3)
  • *: Remove list detection since it isn't used in the builder and is causing issues (73f16d2)
  • *: add MIT License (6409438)
  • *: Stylistic fix on the badges (a04e631)
  • *: Add @timmaybrown as a contributor (584f785)
  • *: Add @murphydan as a contributor (2bf3566)
  • *: Add @aliaspooryorik as a contributor (caf9065)
  • *: Add @elpete as a contributor (bb7b8bb)
  • *: Merge branch 'development' (e3a27f6)
  • *: Updated API Docs (b9e04f5)
  • *: 2.1.0 (8dbddd9)
  • *: A couple minor stylistic changes. (80f14e2)
  • *: Update Builder.cfc

Remove a couple blank lines. (b77a87b)

  • *: issue #8 - additional tests for rawExpressions in the array, removed lists as a valid value for array value and refactored validDirections array to be an instance variable aptly named to match the other naming conventions. (8704ff4)
  • *: First stab at implementing the various requirements for issue #8 to accept an array or list as the column argument's value. The array can accept a variety of value formats that can be intermingled if desired. All scenarios will inherit eithe the default direction or the supplied value for the direction argument. (e0b9b63)
  • *: Cache CommandBox for Travis builds (5eb4561)
  • *: Add new API Docs (2c1f19b)
  • *: 2.0.4 (3414ea7)
  • *: Return result from Oracle grammar when record count is 0 (b8a13cd)
  • *: Updated API Docs (c50d061)
  • *: 2.0.3 (8634e5c)
  • *: Updated API Docs (a6df8a3)
  • *: 2.0.2 (f0886b6)
  • *: Add new API Docs package scripts (c8555ec)
  • *: Updated API Docs (95c2d93)
  • *: Nest the apidocs in a different docs site for future better looking docs (9a8e1fa)
  • *: 2.0.1 (6bd958d)
  • *: Add more files to the box ignore (e233853)
  • *: Add docs to the ignore for box install (29017aa)
  • *: Move to the docs folder since that is what GitHub pages looks for. (7c94266)
  • *: 2.0.0 (e1710bf)
  • *: Add API Docs

Commit them for now until commandbox-docbox is fixed and we can do it in Travis. (fa2edae)

  • *: Finish API docs for QB!!!!! (f145d9a)
  • *: 1.6.2 (c6508bb)
  • *: Add a check to only try to remove the QB_RN column when records actually exist in the query. (4d10905)
  • *: A fun refactor using closures of aggregates. Added docblocks to the new with methods. (9b946c4)
  • *: Add docblocks for bindings (ef1970d)
  • *: Add docblocks for insert, update, and delete (bbd7c6a)
  • *: Add new tap method for inspecting a query in process without interrupting chaining. (f9b7432)
  • *: Use util check instead of raw isInstanceOf (6388bfd)
  • *: Better name forPage arguments (0037cdd)
  • *: Add docblocks for where clauses and groups/havings/orders/limits (c396c98)
  • *: Add docblocks for joins (f580f0a)
  • *: Add more to the API docs (0d5dd74)
  • *: Fix WireBox mapping for newly required returnFormat (61d8a14)
  • *: Add missing semicolons (0493b4a)
  • *: Deprecate returningArrays in favor of returnFormat

returnFormat can take a closure or “array” or “query”. Aggregate methods correctly ignore returnFormat

Fixes #6, #7 (f52e25a)

  • *: Add selectRaw helper method. Alias table for `from. (20da7ea)
  • *: Set up bdd with ColdBox Elixir (dc5cf18)
  • *: Add testbox runner and npm package script for tests (7a3ae71)
  • *: 1.6.1 (068d8a0)
  • *: Minor formatting changes

4 spaces for indentation and spaces inside braces with arguments ({}) (73f0856)

  • *: get tests to pass on ACF11 (aadb1f8)
  • *: Use queryExecute instead of Query() for query of query (e2c8cb2)
  • *: 1.6.0 (6db8522)
  • *: Parse column and table aliases without AS in them (9d04a89)
  • *: 1.5.0 (6937d35)
  • *: Add first MSSQL-specific grammar (89b9c84)
  • *: 1.4.0 (b11ea7f)
  • *: Fix failing test setup from adding return format (301d013)
  • *: Provide custom oracle mass insert compilation (d567830)
  • *: Fix return results failing on insert, update, and deletes (61ca2b1)
  • *: Allow passing options in to insert, update, and delete queries (c45fdcd)
  • *: 1.3.0 (8258998)
  • *: Allow a closure to influence return results. (7a633bb)
  • *: 1.2.4 (9ddea67)
  • *: Fix bug with oracle limits, offsets, and Query of Queries (58189fc)
  • *: 1.2.3 (b54ff1b)
  • *: Fix limit and offset for Oracle and remove generated QB_RN column (44849ff)
  • *: 1.2.2 (d36c7ba)
  • *: Update README formatting (a0a3177)
  • *: Use toBeWithCase for SQL statement checks. Add a test about uppercasing Oracle wrapped values. (e14da32)
  • *: Apply the table prefix to the table alias as well. (e5c6c4b)
  • *: 1.2.1 (44752d1)
  • *: Quick fixes for Oracle grammar. Still needs tests (74e14f7)
  • *: Merge branch 'development'
  • development: 1.2.0 Add OracleGrammar WireBox mapping in ModuleConfig Add section on specifying defaultGrammar Update readme with correct Travis badges Add Oracle grammar support with limit and offset Move MySQL Grammar tests to their own file (e4146cb)
  • *: 1.2.0 (a077807)
  • *: Add OracleGrammar WireBox mapping in ModuleConfig (a4499a9)
  • *: Add section on specifying defaultGrammar (cdc0776)
  • *: Update readme with correct Travis badges (d9fce51)
  • *: Add Oracle grammar support with limit and offset (9bea030)
  • *: Move MySQL Grammar tests to their own file (d4a6856)
  • *: 1.2.0 (a33d937)
  • *: Add OracleGrammar WireBox mapping in ModuleConfig (5c202f0)
  • *: Add section on specifying defaultGrammar (c6dcbd5)
  • *: Update readme with correct Travis badges (84a937d)
  • *: Add Oracle grammar support with limit and offset (c9bab7b)
  • *: Move MySQL Grammar tests to their own file (746d190)
  • *: 1.1.2 (511a567)
  • *: Fix two functions to return any to allow for query or array return results (b70b968)
  • *: 1.1.1 (00ac2b0)
  • *: Add MySQLGrammar binding (bbd2717)
  • *: 1.1.0 (fb82230)
  • *: Add initial MySQL Grammar support (77b636c)
  • *: Adding mappings for WireBox. (8771321)
  • *: Remove Oracle Grammar to be implemented at a later time. (8fcb297)
  • *: Add fix for negative values in forPage (10c77ce)
  • *: Add forPage helper to help with pagination. (658af5b)
  • *: Add missing semicolon for ACF (da77797)
  • *: Add havings clause (f1e3f67)
  • *: Use accessor instead of direct variables access. (6f54077)
  • *: Default to returning arrays of structs over queries. (1dd330b)
  • *: Refactor runQuery to run. (789a6c1)
  • *: Allow passing a single column or an array of columns to get to execute the query with those columns once. (338f82c)
  • *: Add value and exist helper query methods. (34f7ddc)
  • *: Implement count, max, min, and sum aggregate methods. (3217de2)
  • *: Default selecting “*” if nothing is passed in to select() (c542425)
  • *: Implement retrieval shortcuts — first, find, get (4440e23)
  • *: Verify raw statements work in select fields (9907bab)
  • *: Minor formatting adjustments (971489e)
  • *: Minor formatting changes (1f41186)
  • *: Remove unused interface (ee85fc7)
  • *: Remove inject helpers. We'll manage that in the ModuleConfig.cfc (71b5e5c)
  • *: Update references to qb and correct version (cb1ffdd)
  • *: Remove ACF 10 support because I want to use member functions. (0b634bd)
  • *: Add import statements for CF11. (ef23bf1)
  • *: Fixes for Adobe engines. (64c19c7)
  • *: Update Travis script (3249f98)
  • *: Add updateOrInsert helper (c30ad18)
  • *: Add exists (c678cf2)
  • *: Add limit and offset (e606102)
  • *: Update readme from Quick to qb (e585a88)
  • *: Rename Quick to qb.

Quick will be the ORM implementation that will use qb underneath the hood. (29b34af)

  • *: Remove the need to return a query in a when callback. (657d47c)
  • *: Insert, Updates, and Deletes! Oh my! (e19dae3)
  • *: Remove unneeded dependency (0452b44)
  • *: Clean up tests and all tests passing! (6e75e61)
  • *: Implement group bys (73f961d)
  • *: Implement when callbacks (ed65e09)
  • *: Refactor to addBindings (7ca0d5e)
  • *: Implement joins (f41bca0)
  • *: Finish implementing where in. All wheres are done! (4d734b3)
  • *: Implement between statements (26f937b)
  • *: Implement null checks (9f86158)
  • *: Refactor to generated getters and setters. (04f80f7)
  • *: Implement where exists (d4174e3)
  • *: Implement table prefixes (edf8f66)
  • *: Finish basic wheres (160f68d)
  • *: Implement select methods (baa72a2)
  • *: Reformat according to new style guidelines (fadb882)
  • *: Add sublime project file (9e1cd9c)
  • *: Round out failing tests. Time to start implementing (bb54d35)
  • *: Add more failing query/grammar tests (0d874c5)
  • *: Add a bunch of failing tests for builder+grammar interaction (532ffdb)
  • *: Update to the latest Travis CI multi-engine file (52c647c)
  • *: Add comments about ACF10 making life sad. (bc482d1)
  • *: Clarify that PLATFORM is really an ENGINE (cc328fb)
  • *: Add Travis build badge to README. (0856b69)
  • *: Remove unneeded files now that the testing script is inline. (1ce41ba)
  • *: Move script in to travis.yml file. (18c1f2e)
  • *: Add a sleep call to make sure the server has time to spin up. (96c4cf5)
  • *: Specify required CFML versions. (cce8483)
  • *: Major refactoring to support ACF10 (3abeb67)
  • *: Specify that Lucee 5 is a snapshot version. (cfd0d23)
  • *: Switch to the latest version of CommandBox for multi-server options. (cefa79d)
  • *: Add test result properties file to gitignore (a19ea45)
  • *: Add a gitkeep file to the tests results path so tests can run on Travis. (5687ae0)
  • *: Try to add travis support for multiple cf engines. (2833375)
  • *: Add README (369e3c7)
  • *: Move the list and array inferSqlType tests to the right block. (917f132)
  • *: Infer the sql type of lists and arrays based on if all the members share the same sql type; otherwise, default to CF_SQL_VARCHAR. (7ae2548)
  • *: Added orWhere{Column} dynamic method matching. (b221bcd)
  • *: Add whereIn and whereNotIn helper methods. (118db23)
  • *: Return the Builder to continue chaining on dynamic where methods. (202127a)
  • *: Add list functionality to the QueryUtils extractBinding (80dc5b0)
  • *: Wrap the parameters in an "IN" or "NOT IN" clause. (c69cae3)
  • *: Simplify the operator list (4ae3cf1)
  • *: Upper case operators in SQL strings. (d7ba404)
  • *: Unify exception types for invalid operators and combinators (c4b9d68)
  • *: Don't open the browser automatically on server start. (Use gulp watch instead for BrowserSync.) :-) (623517f)
  • *: Refactor to Wirebox injection. (54bc5e0)
  • *: Infer the cfsqltype on bindings. (3f53bd5)
  • *: Refactor to new QueryUtils file for shared functionality. (426e72d)
  • *: Refactor bindings to use structs instead of values in preparation for cfsqltypes. (90fc19c)
  • *: Also allow the shortcut where syntax for the on method. (c67bd66)
  • *: Allow the shortcut where statement in joins. (28fc828)
  • *: Add join query bindings. (ad911fa)
  • *: Fix the SQL compilation order. (ccbc273)
  • *: Allow default settings with user overrides in the ModuleConfig. (f2d2441)
  • *: Fixes for new Quick module mapping. (1caa4bc)
  • *: Add box scripts to workflow (23409ce)
  • *: 0.1.1 (ad26504)
  • *: Fix for mappings to work correctly in modules. (7502394)
  • *: Allow the join closure to be passed in as the second positional argument. (9efbe5b)
  • *: Work on Join clauses (fb25c48)
  • *: Implement joins (c1f3228)
  • *: Enable distinct flag. Clean up duplication in tests. Move src/ to models/ (7a83500)
  • *: Set up BrowserSync with ColdBox Elixir (8109613)
  • *: Always upper case the combinator. (b4b8e2b)
  • *: Validate combinators (e3bd0fe)
  • *: Compile where statements (e403216)
  • *: Simple query execution (a2b1090)
  • *: Allow specifying the combinator (AND or OR) . (eb3d6e0)
  • *: Add where values to the SQL bindings array. (8dc4a47)
  • *: Use ColdBox Elixir (9925fe6)
  • *: Run tests through CommandBox (a2c350f)
  • *: Just dump everything we'd been working on. (9ff2c6c)
  • *: Initial commit (00d24a6)

perf

  • QueryBuilder: Replace normalizeToArray with simpler Array check

normalizeToArray handles the case where variadic arguments are passed in. This comes at a cost, about 50 ms.

Speed is everything when testing against a database. (d54bcce)

  • BaseGrammar: Avoid isInstanceOf in wrapColumn

isInstanceOf takes about 30-40 ms per column. For just one table with 6 columns, this is close to a quarter of a second. This adds up.

Instead, just checking if the variable is an object that has a getSQL key (which we assume is a method), we save all of that time. (15042ce)

v5.7.0

17 Aug 2018 — 20:51: 49 UTC

feat

  • QueryBuilder: Add support for Common Table Expressions (3e10da6)
  • QueryBuilder: Derived and Sub Tables (b3f0461)
  • QueryBuilder: Unions (59028a8)

fix

  • QueryBuilder: Fix JoinClause return value (5d113c7)

v5.5.0

07 Jun 2018 — 03:00: 22 UTC

feat

  • QueryBuilder: Add andWhere method for more readable chains. (309f4d8)

other

v5.4.1

27 Apr 2018 — 22:48: 54 UTC

fix

  • PostgresGrammar: Fix typo in getAllTableNames (91caf6a)
  • SchemaBuilder: Fix dropping foreign keys in MySQL (8895447)

other

v5.4.0

16 Apr 2018 — 21:36: 02 UTC

feat

  • AutoDiscover: Allow for runtime discovery (700948a)

other

v5.3.1

28 Mar 2018 — 22:12: 45 UTC

fix

  • ModuleConfig: Fix logic for determining CommandBox vs ColdBox environment (5c66466)

other

v5.3.0

26 Mar 2018 — 16:15: 55 UTC

chore

  • README: Update references to elpete to coldbox-modules (bc7c99c)

feat

  • ModuleConfig: Auto discover grammar by default. (b2347ae)

fix

  • ModuleConfig: Add PostgresGrammar alias to WireBox (eca03f0)

other

v5.2.1

14 Mar 2018 — 03:18: 19 UTC

fix

  • QueryBuilder: Preserve returnFormat when creating a new builder (4538947)

other

v5.2.0

12 Mar 2018 — 21:33: 37 UTC

feat

  • Grammar: Added official support for MSSQL, Oracle, and Postgres. (#34) (733dae3)

other

v5.1.2

20 Feb 2018 — 06:59: 54 UTC

other

perf

  • QueryBuilder: Replace normalizeToArray with simpler Array check (d54bcce)
  • BaseGrammar: Avoid isInstanceOf in wrapColumn (15042ce)

v5.1.1

19 Feb 2018 — 18:03: 58 UTC

fix

  • MySQLGrammar: Default to CURRENT_TIMESTAMP for timestamp columns (#32) (680750a)

other

v5.1.0

16 Feb 2018 — 22:23: 59 UTC

feat

  • SchemaBuilder: Add dropAllObjects action. (#31) (c3e23b5)

other

v5.0.3

16 Feb 2018 — 21:10: 33 UTC

chore

  • build: Enable commandbox-semantic-release (0fe689f)

errors

  • schema: Better error message when passing in a TableIndex to create column (f91a3f7)

other

  • *: Updated API Docs (dfd9510)
  • *: Update references from Builder to QueryBuilder (632e697)
  • *: Updated API Docs (8325db5)

 

$ box install qb

No collaborators yet.
     
  • Dec 07 2016 01:03 PM
  • Aug 17 2020 04:40 PM
  • 5,782
  • 0
  • 122,638