// MSSQLQueryBuilderSpec.cfc
function orWhere() {
    // If just a string is returned, we assume the bindings is an empty array ([])
    return {
        sql = "SELECT * FROM [users] WHERE [id] = ? OR [email] = ?",
        bindings = [ 1, "foo" ]
    };
}

// OracleSchemaBuilderSpec.cfc
function boolean() {
    // returns an array since schema builder can execute multiple statements.
    return [ "CREATE TABLE ""USERS"" (""ACTIVE"" NUMBER(1, 0) NOT NULL)" ];
}

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

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

// 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();

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 } ]
);

moduleSettings = {
    qb = {
        defaultGrammar = "MySQLGrammar"
    }
};

var grammar = new qb.models.Query.Grammars.MySQLGrammar();
var builder = new qb.models.Query.Builder( grammar );

moduleSettings = {
    qb = {
        returningArrays = false
    }
};

moduleSettings = {
    qb = {
        returnFormat = function( q ) {
            return application.wirebox.getInstance(
                name = "Collection",
                initArguments = { collection = q }
            );
        }
    }
};

var getResults = query.from('users')
    .count();
writeDump(getResults);

var getResults = query.from('users')
    .max('age');
writeDump(getResults);

var getResults = query.from('users')
    .min('age');
writeDump(getResults);

var getResults = query.from('users')
    .where('active', '=', 1)
    .max('age');
writeDump(getResults);

moduleSettings = {
    qb = {
        returnFormat = function( q ) {
            return application.wirebox.getInstance(
                name = "Collection",
                initArguments = { collection = q }
            );
        }
    }
};

//qb
var addRecords = query.from( "users" )
    .whereID( 10 )
    .update(  { "name" = "Roberto", "email" = "roberto@test.com", "age" = 55 });
writeDump(addRecords);

//sql
UPDATE `users` SET `age` = 55, `email` = `roberto@test.com`, `name` = `Roberto` WHERE `ID` = 10

query.from( "posts" )
    .whereID( 10 )
    .update( { "likes" = query.raw( "likes + 1" ) } );
    
// SQL:      UPDATE `posts` SET `likes` = likes + 1 WHERE `ID` = ?
// Bindings: [ 10 ]

var builder = builder.from( "posts" );
if ( rc.recent ) {
    builder.orderBy( "published_date", "desc" );
}
var results = builder.get();

var results = builder.from( "posts" )
    .when( rc.recent, function( q ) {
        q.orderBy( "published_date", "desc" );
    } )
    .get();

query.from("user")
		.whereId( 10 )
		.update( {
			manager_FK = { value = "", null=true },
		} )

query.from("user")
		.whereId( 10 )
		.update( {
			manager_FK = { value = null },
		} )

// qb
var addRecords = query.from( "users" )
    .returning( "id" )
    .insert( { "name" = "Robert", "email" = "robert@test.com", "age" = 55 } );
writeDump(addRecords);

// Postgres
INSERT INTO "users" ("age", "email", "name")
VALUES (55, "robert@test.com", "Robert")
RETURNING "id"

// MSSQL
INSERT INTO [users] ([age], [email], [name])
OUTPUT INSERTED.[id]
VALUES (55, "robert@test.com", "Robert")

schema.alter( "users", function( table ) {
    table.addColumn( table.boolean( "is_active" ) );
} );

ALTER TABLE `users` ADD `is_active` TINYINT(1) NOT NULL

schema.alter( "users", function( table ) {
    table.dropColumn( "username" );
} );

ALTER TABLE `users` DROP COLUMN `username`

schema.alter( "users", function( table ) {
    table.modifyColumn( "name", table.string( "username" ) );
} );

ALTER TABLE `users` CHANGE `name` `username` VARCHAR(255) NOT NULL

schema.alter( "users", function( table ) {
    table.renameColumn( "name", table.string( "username" ) );
} );

ALTER TABLE `users` CHANGE `name` `username` VARCHAR(255) NOT NULL

schema.alter( "users", function( table ) {
    table.addConstraint( table.unique( "username" ) );
} );

ALTER TABLE `users` ADD CONSTRAINT `unq_users_username` UNIQUE (`username`)

schema.alter( "users", function( table ) {
    table.dropConstraint( "unq_users_full_name" );
    table.dropConstraint( table.unique( "username" ) );
} );

ALTER TABLE `users` DROP INDEX `unq_users_full_name`
ALTER TABLE `users` DROP INDEX `unq_users_username`

schema.alter( "users", function( table ) {
    table.renameConstraint( "unq_users_first_name_last_name", "unq_users_full_name" );
} );

ALTER TABLE `users` RENAME INDEX `unq_users_first_name_last_name` TO `unq_users_full_name`

//qb
var getResults = query.from('users')
    .first();
writeDump(getResults);

//sql
select * from users limit(1)

public any function first( struct options = {} )

query.from( "users" ).values( "email" );
// [ "john@example.com", "jane@example.com", "jim@example.com", ... ]

public any function values( required string column, struct options = {} );

query.from( "users" ).value( "email" );
// "john@example.com"

public any function value( required string column, struct options = {} );

//qb
var getAllResults = query.from('users')
    .get( options={ datasource= 'MyOtherDatasourceName'} );
writeDump(getAllResults);

var query =  wirebox.getInstance('QueryBuilder@qb')
    .setGrammar( wirebox.getInstance('MSSQLGrammar@qb') );

moduleSettings = {
    qb = {
        defaultGrammar = "MySQLGrammar"
    }
};

var grammar = new qb.models.Grammars.MySQLGrammar();
var builder = new qb.models.Query.QueryBuilder( grammar );

this.mappings = {
    "/qb" = expandPath("./subsystems/qb")
};

qb = {
  diLocations = "/qb/models",
  diConfig = {
    loadListener = function( di1 ) {
      di1.declare( "BaseGrammar" ).instanceOf( "qb.models.Query.Grammars.Grammar" ).done()
         .declare( "MySQLGrammar" ).instanceOf( "qb.models.Query.Grammars.MySQLGrammar" ).done()
         .declare( "QueryUtils" ).instanceOf( "qb.models.Query.QueryUtils" ).done()
         .declare( "QueryBuilder" ).instanceOf( "qb.models.Query.QueryBuilder" )
         .withOverrides({
            grammar = di1.getBean( "MySQLGrammar" ),
            utils = di1.getBean( "QueryUtils" ),
            returnFormat = "array"
         })
         .asTransient();
    }
  }
}

// Create an instance of qb
builder = getBeanFactory( "qb" ).getBean( "QueryBuilder" );
// Query the database
posts = builder.from( "Posts" ).get();
posts = builder.from( "Posts" ).where( "IsDraft", "=", 0 ).get();

schema.create( "users", function( table ) {
    table.increments( "id" );
    table.string( "email" );
    table.string( "password" );
    table.timestamp( "created_date" );
    table.timestamp( "modified_date" );
    table.timestamp( "last_logged_in" ).nullable();
} );

CREATE TABLE `users` (
    `id` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(255) NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `created_date` TIMESTAMP NOT NULL,
    `modified_date` TIMESTAMP NOT NULL,
    `last_logged_in` TIMESTAMP,
    CONSTRAINT `pk_users_id` PRIMARY KEY (`id`)
)

//qb
var getResults = query.from('users as u').get();

//sql
SELECT * FROM `users` AS `u`

//qb
var getResults = query.from('users u').get();

//sql
SELECT * FROM `users` AS `u`

//qb
var getResults = query.fromRaw('[users] u (nolock)').get();

//sql
SELECT * FROM [users] u (nolock)

//qb
var getResults = query
    .fromRaw('dbo.generateDateTable(?, ?, ?) as dt', ['2017-01-01', '2017-12-31', 'm'])
    .get()
;

//sql
SELECT * FROM dbo.generateDateTable('2017-01-01', '2017-12-31', 'm') as dt

//qb
var getResults = query
    .select('firstName', 'lastName')
    .fromSub('u', function (q){
        q
            .select('lName as lastName', 'fName as firstName')
            .from('users')
            .where('age', '>=', 21)
        ;
    })
    .orderBy('lastName')
    .get()
;

//sql
SELECT `firstName`, `lastName` FROM (SELECT `lName` as `lastName`, `fName` as `firstName` FROM `users` WHERE `age` >= 21) AS `u` ORDER BY `lastName`

//qb
var derivedQA = query
    .select('lName as lastName', 'fName as firstName')
    .from('users')
    .where('age', '>=', 21)
;

var getResults = query
    .select('firstName', 'lastName')
    .fromSub('u', derivedQA)
    .orderBy('lastName')
    .get()
;

//sql
SELECT `firstName`, `lastName` FROM (SELECT `lName` as `lastName`, `fName` as `firstName` FROM `users` WHERE `age` >= 21) AS `u` ORDER BY `lastName`

//qb
var getResults = query
        .select('title')
        .from('blogs')
        .whereIn('id', [1, 2, 3])
        .union(function (q){
            q
                .select('title')
                .from('blogs-archive')
                .whereIn('id', [1, 2, 3])
            ;
        })
    .get();
writeDump(getResults);

//sql
SELECT `title` FROM `blogs` WHERE `id` IN (1, 2, 3) UNION SELECT `title` FROM `blogs-archive` WHERE `id` IN (1, 2, 3)

var unionQB = query
    .select('title')
    .from('blogs-archive')
    .whereIn('id', [1, 2, 3])
;

//qb
var getResults = query
        .select('title')
        .from('blogs')
        .whereIn('id', [1, 2, 3])
        .union(unionQB)
    .get();
writeDump(getResults);

//sql
SELECT `title` FROM `blogs` WHERE `id` IN (1, 2, 3) UNION SELECT `title` FROM `blogs-archive` WHERE `id` IN (1, 2, 3)

//qb
var getResults = query
        .select('title')
        .from('blogs')
        .whereIn('id', [1, 2, 3])
        .unionAll(function (q){
            q
                .select('title')
                .from('blogs-archive')
                .whereIn('id', [1, 2, 3])
            ;
        })
    .get();
writeDump(getResults);

//sql
SELECT `title` FROM `blogs` WHERE `id` IN (1, 2, 3) UNION ALL SELECT `title` FROM `blogs-archive` WHERE `id` IN (1, 2, 3)

//qb
var getResults = query
        .select('title')
        .from('blogs')
        .whereIn('id', [1, 2, 3])
        .union(function (q){
            q
                .select('title')
                .from('blogs-archive')
                .whereIn('id', [1, 2, 3])
            ;
        })
        .orderBy("title", "desc")
    .get();
writeDump(getResults);

//sql
SELECT `title` FROM `blogs` WHERE `id` IN (1, 2, 3) UNION SELECT `title` FROM `blogs-archive` WHERE `id` IN (1, 2, 3) ORDER BY `title` DESC

//qb
var getResults = query
        .select('title')
        .from('blogs')
        .whereIn('id', [1, 2, 3])
        // order can be *before* union statements
        .orderBy("title", "desc")
        .unionAll(function (q){
            q
                .select('title')
                .from('blogs-archive-1990-decade')
                .whereIn('id', [1, 2, 3])
            ;
        })
        .unionAll(function (q){
            q
                .select('title')
                .from('blogs-archive-2000-decade')
                .whereIn('id', [1, 2, 3])
            ;
        })
    .get();
writeDump(getResults);

//sql
SELECT `title` FROM `blogs` WHERE `id` IN (1, 2, 3) UNION ALL SELECT `title` FROM `blogs-archive-1990-decade` WHERE `id` IN (1, 2, 3) UNION ALL SELECT `title` FROM `blogs-archive-2000-decade` WHERE `id` IN (1, 2, 3) ORDER BY `title` DESC

//qb
var getResults = query.from('blogs')
    .join('users', 'users.ID', '=', 'blogs.FK_usersID')
    .get();
writeDump(getResults);

//sql
SELECT users.name,blogs.title,blogs.ID as blogID FROM `blogs` INNER JOIN `users` ON `users`.`ID` = `blogs`.`FK_usersID`

//qb
var getResults = query.from('blogs')
    .joinRaw('[users] AS u (nolock)', 'u.ID', '=', 'blogs.FK_usersID')
    .get();
writeDump(getResults);

//sql
SELECT * FROM [blogs] INNER JOIN [users] AS u (nolock) ON [u].[ID] = [blogs].[FK_usersID]

//qb
var getResults = query.from( "blogs" )
    .join( "users", function( j ) {
        j.on( "users.ID", "=", "blogs.FK_usersID" )
            .on( "users.type", "=", "blogs.type" );
    } )
    .get();

//sql
SELECT users.name,blogs.title,blogs.ID as blogID FROM `blogs` INNER JOIN `users` ON `users`.`ID` = `blogs`.`FK_usersID` AND `users`.`type` = `blogs`.`type`

//qb (leftJoin)
var getResults = query.from('blogs')
    .leftJoin('users', 'users.ID', '=', 'blogs.FK_usersID')
    .get();
writeDump(getResults);

//qb (rightJoin)
var getResults = query.from('blogs')
    .rightJoin('users', 'users.ID', '=', 'blogs.FK_usersID')
    .get();
writeDump(getResults);

//sql (leftJoin)
SELECT * FROM `blogs` LEFT JOIN `users` ON `users`.`ID` = `blogs`.`FK_usersID`

//sql (rightJoin)
SELECT * FROM `blogs` RIGHT JOIN `users` ON `users`.`ID` = `blogs`.`FK_usersID`

var getResults = query.from('users')
    .crossJoin('departments', 'departments.ID', '=', 'users.FK_departmentID')
    .get();
writeDump(getResults);

//qb
var getResults = query
    .from('blogs')
    .joinSub('u', function (q){
        q
            .from('users')
            .where('disabled', 0)
        ;
    }, 'u.ID', '=', 'blogs.FK_usersID')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `blogs` INNER JOIN (SELECT * FROM `users` WHERE `disabled` = 0) AS `u` ON `u`.`ID` = `blogs`.`FK_usersID`

// qb
var getResults = query
    .with('UserCTE', function (q){
        q
            .select('fName as firstName', 'lName as lastName')
            .from('users')
            .where('disabled', 0)
        ;
    })
    .from('UserCTE')
    .get();
writeDump(getResults);

// sql
WITH `UserCTE` AS (
    SELECT
        `fName` as `firstName`,
        `lName` as `lastName`
    FROM `users`
    WHERE `disabled` = 0
) SELECT * FROM `UserCTE`

// qb
var cte = query
    .select('fName as firstName', 'lName as lastName')
    .from('users')
    .where('disabled', 0)
;

var getResults = query
    .with('UserCTE', cte)
    .from('UserCTE')
    .get();
writeDump(getResults);

// sql
WITH `UserCTE` AS (
    SELECT
        `fName` as `firstName`,
        `lName` as `lastName`
    FROM `users`
    WHERE `disabled` = 0
)
SELECT * FROM `UserCTE`

// qb
var getResults = query
    .with('UserCTE', function (q){
        q
            .select('id', 'fName as firstName', 'lName as lastName')
            .from('users')
            .where('disabled', 0)
        ;
    })
    .with('BlogCTE', function (q){
        q
            .from('blogs')
            .where('disabled', 0)
        ;
    })
    .from('BlogCTE as b')
    .join('UserCTE as u', 'b.Creator', 'u.id')
    .get();
writeDump(getResults);

// sql
WITH `UserCTE` AS (
    SELECT
        `id`,
        `fName` as `firstName`,
        `lName` as `lastName`
    FROM `users`
    WHERE `disabled` = 0
),
`BlogCTE` AS (
    SELECT *
    FROM `blogs`
    WHERE `disabled` = 0
)
SELECT *
FROM `BlogCTE` AS `b`
INNER JOIN `UserCTE` AS `u`
ON `b`.`Creator` = `u`.`id`

// qb
var getResults = query
    .withRecursive('Hierarchy', function (q){
        q
            // get the parent rows only
            .select('Id', 'ParentId', 'Name', q.raw('0 AS [Generation]'))
            .from('Sample')
            .whereNull('ParentId')
            // use recursion to join the child rows to their parents
            .unionAll(function (q){
                q
                    .select('child.Id', 'child.ParentId', 'child.Name', q.raw('[parent].[Generation] + 1'))
                    .from("Sample as child)
                    .join("Hierarchy as parent", "child.ParentId", "parent.Id")
                ;
            })
        ;
    }, ['Id', 'ParentId', 'Name', 'Generation'])
    .from('Hierarchy')
    .get();
writeDump(getResults);

// sql
;WITH [Hierarchy] ([Id], [ParentId], [Name], [Generation]) AS (SELECT [Id], [ParentId], [Name], 0 AS [Generation] FROM [Sample] WHERE [ParentId] IS NULL UNION ALL SELECT [child].[Id], [child].[ParentId], [child].[Name], [parent].[Generation] + 1 FROM [Sample] AS [child] INNER JOIN [Hierarchy] AS [parent] ON [child].[ParentId] = [parent].[Id]) SELECT * FROM [Hierarchy]

schema.create( "users", function( table ) {
    table.unsignedInteger( "country_id" ).references( "id" ).onTable( "countries" );
} );

CREATE TABLE `users` (
    `country_id` INTEGER UNSIGNED NOT NULL,
    CONSTRAINT `fk_users_country_id` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)

schema.create( "users", function( table ) {
    table.unsignedInteger( "country_id" ).references( "id" ).onTable( "countries" );
} );

CREATE TABLE `users` (
    `country_id` INTEGER UNSIGNED NOT NULL,
    CONSTRAINT `fk_users_country_id` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)

schema.create( "users", function( table ) {
    table.unsignedInteger( "country_id" )
        .references( "id" )
        .onTable( "countries" )
        .onUpdate( "CASCADE" );
} );

CREATE TABLE `users` (
    `country_id` INTEGER UNSIGNED NOT NULL,
    CONSTRAINT `fk_users_country_id` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON UPDATE CASCADE ON DELETE NO ACTION
)

schema.create( "users", function( table ) {
    table.unsignedInteger( "country_id" )
        .references( "id" )
        .onTable( "countries" )
        .onDelete( "SET NULL" );
} );

CREATE TABLE `users` (
    `country_id` INTEGER UNSIGNED NOT NULL,
    CONSTRAINT `fk_users_country_id` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON UPDATE NO ACTION ON DELETE SET NULL
)

schema.create( "users", function( table ) {
    table.string( "first_name" );
    table.string( "last_name" );
    table.index( [ "first_name", "last_name" ], "idx_users_full_name" );
} );

CREATE TABLE `users` (
    `first_name` VARCHAR(255) NOT NULL,
    `last_name` VARCHAR(255) NOT NULL,
    INDEX `idx_users_full_name` (`first_name`, `last_name`)
)

schema.create( "users", function( table ) {
    table.unsignedInteger( "country_id" );
    table.foreignKey( "country_id" ).references( "id" ).onTable( "countries" );
} );

CREATE TABLE `users` (
    `country_id` INTEGER UNSIGNED NOT NULL,
    CONSTRAINT `fk_users_country_id` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)

schema.create( "posts_users", function( table ) {
    table.unsignedInteger( "post_id" ).references( "id" ).onTable( "posts" );
    table.unsignedInteger( "user_id" ).references( "id" ).onTable( "users" );
    table.primaryKey( [ "post_id", "user_id" ], "pk_posts_users" );
} );

CREATE TABLE `posts_users` (
    `post_id` VARCHAR(255) NOT NULL,
    `user_id` VARCHAR(255) NOT NULL,
    INDEX `idx_users_full_name` (`first_name`, `last_name`),
    CONSTRAINT `fk_posts_users_post_id` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `fk_posts_users_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT ""pk_users_first_name_last_name"" PRIMARY KEY (""first_name"", ""last_name"")
)

schema.create( "users", function( table ) {
    table.increments( "id" );
    table.string( "username ");
    table.unique( "username" );
} );

CREATE TABLE `users` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    UNIQUE (`username`)
)

schema.create( "users", function( table ) {
    table.integer( "age" ).comment( "Do not lie about your age" );
} );

CREATE TABLE `users` (
    `age` INTEGER NOT NULL COMMENT `Do not lie about your age`
)

schema.create( "users", function( table ) {
    table.boolean( "is_active" ).default( 1 );
    table.timestamp( "created_date" ).default( "NOW()" );
    table.string( "country" ).default( "'USA'" );
} );

CREATE TABLE `users` (
    `is_active` TINYINT(1) DEFAULT 1,
    `created_date` TIMESTAMP DEFAULT NOW(),
    `country` VARCHAR(255) DEFAULT 'USA'
)

schema.create( "users", function( table ) {
    table.timestamp( "last_logged_in" ).nullable()
} );

CREATE TABLE `users` (
    `last_logged_in` TIMESTAMP
)

schema.create( "users", function( table ) {
    table.uuid( "id" ).primaryKey();
} );

CREATE TABLE `users` (
    `id` CHAR(35) NOT NULL,
    CONSTAINT `pk_users_id` PRIMARY KEY (`id`)
)

schema.create( "users", function( table ) {
    table.unsignedInteger( "country_id" ).references( "id" ).onTable( "countries" ).onDelete( "cascade" );
} );

CREATE TABLE `users` (
    `country_id` INTEGER UNSIGNED NOT NULL,
    CONSTRAINT `fk_users_country_id` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON UPDATE NO ACTION ON DELETE CASCADE
)

schema.create( "users", function( table ) {
    table.integer( age" ).unsigned();
} );

CREATE TABLE `users` (
    `age` INTEGER UNSIGNED NOT NULL
)

schema.create( "email", function( table ) {
    table.string( email" ).unique();
} );

CREATE TABLE `users` (
    `email` VARCHAR(255) NOT NULL UNIQUE
)

//qb
var getResults = query.from('users')
    .where('age','>=', { value = 18, cfsqltype = "CF_SQL_INTEGER" })
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `age` >= 18

//qb
var getResults = query.from('users')
    .where('age','>=','18')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `age` >= 18

//qb
var getResults = query.from('users')
    .where('age','18')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `age` = 18

//qb
var getResults = query.from('users')
    .where('age','>=','18')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `age` >= 18

//qb
var getResults = query.from('users')
    .where('age','<>','18')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `age` <> 18


//qb
var getResults = query.from('users')
    .where('name','like','A%')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `name` LIKE 'A%'

//qb
var getResults = query.from('users')
    .where('name','like','A%')
    .orWhere('age','>','30')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `name` LIKE 'A%' OR `age` > 30

//qb
var getResults = query.from('users')
    .whereBetween('age','18','21')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `age` BETWEEN 18 AND 21

//qb
var getResults = query.from('users')
    .whereNotBetween('age','18','21')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `age` NOT BETWEEN 18 AND 21

var getResults = query.from('users')
    .whereIn('age',[ 17, 18, 19, 20, 21 ])
    .get();
writeDump(getResults);

var getResults = query.from('users')
       .whereIn('age', function ( subQuery ) {
        return subQuery.select( "age" )
        .from( "all_ages" )
        .whereBetween("age","17","21");
    })
    .get();
writeDump(getResults);

var getResults = query.from('users')
    .whereNotIn('age',[ 17, 18, 19, 20, 21 ])
    .get();
writeDump(getResults);

//qb
var getResults = query.from('users')
    .whereNull('modifiedDate')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `modifiedDate` IS NULL

//qb
var getResults = query.from('users')
    .whereNotNull('modifiedDate')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `modifiedDate` IS NOT NULL

//qb
var getResults = query.from('users')
    .whereExists( function( q ) {
        q.select( q.raw( 1 ) ).from( "departments" )
            .where( "departments.ID", "=", q.raw( '"users.FK_departmentID"' ) );
    })
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE EXISTS (SELECT 1 FROM `departments` WHERE `departments`.`ID` = "users.FK_departmentID")

//qb
var getResults = query.from('users')
    .whereColumn('modifiedDate','createdDate')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `modifiedDate` = `createdDate`

//qb
var getResults = query.from('users')
    .whereColumn('modifiedDate','<>','createdDate')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `modifiedDate` <> `createdDate`

//qb
var getResults = query.from('users')
    .where( function( q ) {
        q.where( "a", 1 ).orWhere( "b", 2 );
    } )
    .where( "c", 3 );
writeDump(getResults);

// sql
SELECT * FROM `users` WHERE (a = ? OR b = ?) AND c = ?

schema.drop( "user_logins" );

DROP TABLE `user_logins`

schema.dropIfExists( "user_logins" );

DROP TABLE IF EXISTS `user_logins`

// manually
var schema = new qb.models.schema.SchemaBuilder(
    new qb.models.grammars.MySQLGrammar()
);

// WireBox
var schema = wirebox.getInstance( "SchemaBuilder@qb" );

schema.create( "users", function( table ) {
    table.increments( "id" );
    table.string( "email" );
    table.string( "password" );
    table.timestamp( "created_date" ).nullable();
    table.timestamp( "modified_date" ).nullable();
} );

CREATE TABLE `users` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(255) NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `created_date` TIMESTAMP,
    `modified_date` TIMESTAMP,
    CONSTRAINT `pk_users_id` PRIMARY KEY (`id`)
)

schema.alter( "users", function( table ) {
    table.addConstraint( table.unique( "username" ) );
    table.dropColumn( "last_logged_in" );
} );

ALTER TABLE `users` ADD CONSTRAINT `unq_users_username` UNIQUE (`username`);
ALTER TABLE `users` DROP COLUMN `last_logged_in`;

schema.drop( "user_logins" );

DROP TABLE `user_logins`

schema.rename( "posts", "blog_posts" );

RENAME TABLE `posts` TO `blog_posts`

schema.hasTable( "users" );

SELECT 1
FROM `information_schema`.`tables`
WHERE `table_name` = 'users'

schema.hasColumn( "users", "last_logged_in" );

SELECT 1
FROM `information_schema`.`columns`
WHERE `table_name` = 'users'
    AND `column_name` = 'last_logged_in'