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

sqlFormatter

v1.1.3+31 Public

SQLFormatter

A CFML wrapper for the Vertical Blank sql-formatter java library.

Release

Installation

Using CommandBox:

box install sqlFormatter

Usage

Basic formatting is done via the format() method:

var prettySQL = getInstance( "[email protected]" )
                    .format( "SELECT COUNT(*) FROM users" );

For database-specific syntax, you can choose a dialect using .of():

var prettySQL = getInstance( "[email protected]" )
                    .of( "postgres" )
                    .format( "SELECT COUNT(*) FROM users" );

Advanced Formatting Configuration

You can also specify advanced configuration options via the ConfgBuilder:

var formatConfig = getInstance( "[email protected]" )
                        .setIndent("    ") // Defaults to two spaces
                        .setUppercase(true) // Defaults to false (not safe to use when SQL dialect has case-sensitive identifiers)
                        .setLinesBetweenQueries(2) // Defaults to 1
                        .setMaxColumnLength(80) // Defaults to 50
                        .build();

You can then pass the FormatConfig object as the second parameter in the Formatter.format() method call:

var prettySQL = getInstance( "[email protected]" )
                    .format( "SELECT * FROM pages ORDER BY 'name'", formatConfig );

Parameter Replacement

SQLFormatter supports parameter replacement using either an array of parameters or a struct of key/value params passed to the .withParams() method.

For positional parameters, pass an array:

var prettySQL = getInstance( "[email protected]" )
                    .of( "mysql" )
                    .withParams( [ "a", "b", "c" ] )
                    .format( "SELECT * FROM pages WHERE slug IN [?, ?, ?]" );

or for named parameters, use a key/value struct:

var prettySQL = getInstance( "[email protected]" )
                    .of( "postgres" )
                    .withParams( { "name" : "Michael", "age" : "18" } )
                    .format( "SELECT * FROM user WHERE name= :michael and age= :age" );

Warning: Only certain dialects support parameters, and each dialect uses different syntax. Use the postgresql dialect for the :name syntax, or tsql for @foo syntax.

Warning: There is an issue in the underlying library with named placeholders. See issue #57 on the SQLFormatter repository for more details.

Dialect

Supported dialects:

  • sql - Standard SQL
  • mariadb - MariaDB
  • mysql - MySQL
  • postgresql - PostgreSQL
  • db2 - IBM DB2
  • plsql - Oracle PL/SQL
  • n1ql - Couchbase N1QL
  • redshift - Amazon Redshift
  • spark - Spark
  • tsql - SQL Server Transact-SQL

Thanks

Special thanks to the SQLFormatter library. I could'na dunnit without ya. 😉

Contributing

All contributions welcome! Fixing typos or adding tests are especially easy ways to help out.

To get started hacking on SQLFormatter:

  1. Clone the module - git clone [email protected]:michaelborn/sqlFormatter.git
  2. Install dependencies - box install
  3. Start up a Lucee server - box server start [email protected]
  4. Write code
  5. Run tests - box testbox run
  6. Push up a pull request

Updating the SQLFormatter Dependency

To bump the embedded SQLFormatter library, run the following from the module directory root - making sure to replace 2.0.3 with the latest version number:

rm lib/sql-formatter-*.jar && cd lib && curl -LO https://search.maven.org/remotecontent?filepath=com/github/vertical-blank/sql-formatter/2.0.3/sql-formatter-2.0.3.jar

This should place a sql-formatter-<VERSION>.jar jar file in the lib/ directory. All that's needed after that is a git add lib && git commit && git push to update the repo.


Copyright Since 2005 ColdBox Framework by Luis Majano and Ortus Solutions, Corp www.coldbox.org | www.luismajano.com | www.ortussolutions.com


HONOR GOES TO GOD ABOVE ALL

Because of His grace, this project exists. If you don't like this, then don't read it, its not for you.

"Therefore being justified by faith, we have peace with God through our Lord Jesus Christ: By whom also we have access by faith into this grace wherein we stand, and rejoice in hope of the glory of God. And not only so, but we glory in tribulations also: knowing that tribulation worketh patience; And patience, experience; and experience, hope: And hope maketh not ashamed; because the love of God is shed abroad in our hearts by the Holy Ghost which is given unto us. ." Romans 5:5

THE DAILY BREAD

"I am the way, and the truth, and the life; no one comes to the Father, but by me (JESUS)" Jn 14:1-12

Changelog

All notable changes to this project will be documented in this file.

The format is based on Keep a Changelog, and this project adheres to Semantic Versioning.


[v1.1.3] - 2022-MAY-04

Changed

  • Switched BACK to S3 storage, since forgeboxStorage is still broken

[v1.1.2] - 2022-MAY-04

Changed

  • Switched back to forgeboxStorage since Forgebox resolved the package storage issue

[v1.1.1] - 2022-MAY-02

Changed

  • Switched to S3 storage to avoid a Forgebox storage issue

[v1.1.0] - 2022-APR-27

Changed

  • Embeds the sql-formatter jar to avoid downloading from Maven on install

[v1.0.3] - 2022-APR-21

Added

  • 📦 NEW: Add formatter.withParams() for parameter replacement

[v1.0.2] - 2022-APR-21

Added

  • 📦 NEW: Add ConfigBuilder for custom formatting configuration

[v1.0.1] - 2022-APR-18

Added

  • 👌 IMPROVE: Add BSD-3 license
  • 📖 DOC: Clean up README and API docs

[v1.0.0] - 2022-APR-18

  • 📦 Initial version!

 

$ box install sqlformatter

No collaborators yet.
     
  • {{ getFullDate("May 04 2022 12:05 PM GMT") }}
  • {{ getFullDate("May 04 2022 12:05 PM GMT") }}
  • 395
  • 94
  • 1,003