From the API examples, in Chrome console:
snippets = $('.syntaxhighlighter.js table')
bits = []; snippets.each((i, snippet) => bits.push(snippet.innerText));
console.log(bits.join('\n'));
import * as squel from 'squel'; | |
const log = console.log; | |
log( squel.VERSION ); /* version string */ | |
const s = squel.select(); | |
s.from("student"); | |
log( s.toString() ); /* SELECT * FROM student */ | |
log( squel.select().from("students").toString() ); /* SELECT * FROM students */ | |
log( '' + squel.select().from("students") ); /* SELECT * FROM students */ | |
log( | |
squel.select({ separator: "\n" }) | |
.from("students") | |
.field("name") | |
.field("MIN(test_score)") | |
.field("MAX(test_score)") | |
.field("GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ')") | |
.group("name") | |
.toString(), | |
); | |
/* | |
SELECT | |
name, | |
MIN(test_score), | |
MAX(test_score), | |
GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ') | |
FROM | |
students | |
GROUP BY | |
name | |
*/ | |
log( | |
squel.select() | |
.from("students") | |
.toString(), | |
); | |
/* SELECT * FROM students */ | |
log( | |
squel.select({ separator: "\n" }) | |
.from("students") | |
.toString(), | |
); | |
/* | |
SELECT | |
* | |
FROM students | |
*/ | |
log( | |
squel.select() | |
.from("students") | |
.from("lecturers", "l") | |
.from("admins") | |
.toString(), | |
); | |
/* SELECT * FROM students, lecturers `l`, admins */ | |
log( | |
squel.select() | |
.from( squel.select().from('students'), 's' ) | |
.field('s.id') | |
.toString(), | |
); | |
/* SELECT s.id FROM (SELECT * FROM students) `s` */ | |
log( | |
squel.select() | |
.from("students") | |
.field("id") | |
.field("students.name") | |
.toString(), | |
); | |
/* SELECT id, students.name FROM students */ | |
log( | |
squel.select() | |
.from("students") | |
.field("id", "Id") | |
.field("students.name", "Student Name") | |
.toString(), | |
); | |
/* SELECT id AS "Id", students.name AS "Student Name" FROM students */ | |
log( | |
squel.select() | |
.from("students", "s") | |
.field("s.id") | |
.field("s.test_score", "Test score") | |
.field("DATE_FORMAT(s.date_taken, '%M %Y')", "Taken on") | |
.toString(), | |
); | |
/* | |
SELECT | |
s.id, | |
s.test_score AS "Test score" | |
DATE_FORMAT(s.date_taken, '%M %Y') AS "Taken on" | |
FROM | |
students `s` | |
*/ | |
log( | |
squel.select() | |
.from("students", "s") | |
.field(squel.select().field("MAX(score)").from("scores"), 'score') | |
.toString(), | |
); | |
/* | |
SELECT | |
(SELECT MAX(score) FROM scores) AS "score" | |
FROM | |
students `s` | |
*/ | |
log( | |
squel.select() | |
.from("students") | |
.field("id") | |
.distinct() | |
.toString(), | |
); | |
/* SELECT DISTINCT id FROM students */ | |
log( | |
squel.select() | |
.from("students") | |
.join("teachers") | |
.toString(), | |
); | |
/* SELECT * FROM students INNER JOIN teachers */ | |
log( | |
squel.select() | |
.from("students") | |
.join("teachers", "t") | |
.outer_join("expelled") | |
.toString(), | |
); | |
/* SELECT * FROM students | |
INNER JOIN teachers `t` | |
OUTER JOIN expelled | |
*/ | |
log( | |
squel.select() | |
.from("students") | |
.join( | |
squel.select().field('score').from('results'), | |
't' | |
) | |
.outer_join("expelled") | |
.toString(), | |
); | |
/* SELECT * FROM students | |
INNER JOIN (SELECT score FROM results) `t` | |
OUTER JOIN expelled | |
*/ | |
log( | |
squel.select() | |
.field("students.id") | |
.from("students") | |
.left_join("teachers", null, "students.id = teachers.student_id") | |
.right_join("jailed", "j", "j.student_id = students.id") | |
.toString(), | |
); | |
/* SELECT students.id FROM students | |
LEFT JOIN teachers ON (students.id = teachers.student_id) | |
RIGHT JOIN jailed `j` ON (j.student_id = students.id) | |
*/ | |
log( | |
squel.select() | |
.from('marks', 'm') | |
.join( squel.select().from('students'), 's', 's.id = m.id' ) | |
.toString(), | |
); | |
/* SELECT * FROM marks `m` INNER JOIN (SELECT * FROM students) `s` ON (s.id = m.id) */ | |
log( | |
squel.select() | |
.field("id") | |
.from("students") | |
.where("name = 'Thomas'") | |
.toString(), | |
); | |
/* SELECT id FROM students WHERE (name = 'Thomas') */ | |
log( | |
squel.select() | |
.field("id") | |
.from("students") | |
.where("score = ?", squel.select().field('MAX(score)').from('scores')) | |
.toString(), | |
); | |
/* SELECT id FROM students WHERE (score = (SELECT MAX(score) FROM scores)) */ | |
log( | |
squel.select() | |
.field("id") | |
.from("students") | |
.where("name = 'Thomas'") | |
.where("age > 18") | |
.toString(), | |
); | |
/* SELECT id FROM students WHERE (name = 'Thomas') AND (age > 18) */ | |
log( | |
squel.select() | |
.field("id") | |
.from("students") | |
.where("name = 'Thomas' OR age > 18") | |
.where("id BETWEEN 200 and 300") | |
.toString(), | |
); | |
/* SELECT id FROM students WHERE (name = 'Thomas' OR age > 18) AND (id BETWEEN 200 and 300) */ | |
log( | |
squel.select() | |
.field("id") | |
.from("students") | |
.where( | |
squel.expr().and("name = 'Thomas'").or("age > 18"), | |
) | |
.toString(), | |
); | |
/* SELECT id FROM students WHERE (name = 'Thomas' OR age > 18) */ | |
log( | |
squel.select() | |
.field("id") | |
.from("students") | |
.order("id") | |
.order("name") | |
.toString(), | |
); | |
/* SELECT id FROM students ORDER BY id ASC, name ASC */ | |
log( | |
squel.select() | |
.field("id") | |
.from("students") | |
.order("id") | |
.order("name", false) /* using TRUE instead would be the same as omitting it. */ | |
.toString(), | |
); | |
/* SELECT id FROM students ORDER BY id ASC, name DESC */ | |
log( | |
squel.select() | |
.field("id") | |
.from("students") | |
.order("DIST(?, ?)", true, 1, 2) | |
.toString(), | |
); | |
/* SELECT id FROM students ORDER BY DIST(1, 2) ASC */ | |
log( | |
squel.select() | |
.field("id") | |
.from("students") | |
.group("id") | |
.toString(), | |
); | |
/* SELECT id FROM students GR0UP BY id */ | |
log( | |
squel.select() | |
.field("id") | |
.from("students") | |
.group("id") | |
.group("students.name") | |
.toString(), | |
); | |
/* SELECT id FROM students GR0UP BY id, students.name */ | |
log( | |
squel.select() | |
.field("id") | |
.from("students") | |
.group("id") | |
.having("a = ?", 2) | |
.toString(), | |
); | |
/* SELECT id FROM students GR0UP BY id HAVING (a = 2) */ | |
log( | |
squel.select() | |
.from("students") | |
.limit(10) | |
.toString(), | |
); | |
/* SELECT * FROM students LIMIT 10 */ | |
log( | |
squel.select() | |
.from("students") | |
.limit(10) | |
.limit(0) | |
.toString(), | |
); | |
/* SELECT * FROM students */ | |
log( | |
squel.select() | |
.from("students") | |
.offset(102) | |
.toString(), | |
); | |
/* SELECT * FROM students OFFSET 102 */ | |
log( | |
squel.select() | |
.from("students") | |
.offset(1) | |
.offset(0) | |
.toString(), | |
); | |
/* SELECT * FROM students */ | |
log( | |
squel.select() | |
.from("students") | |
.union( | |
squel.select() | |
.from('scores') | |
) | |
.union( | |
squel.select() | |
.from('batches'), | |
) | |
.toString(), | |
); | |
/* SELECT * FROM students UNION (SELECT * FROM scores) UNION (SELECT * FROM batches) */ | |
log( | |
squel.select() | |
.function('1') | |
.toString(), | |
); | |
/* SELECT 1 */ | |
log( | |
squel.select() | |
.function('MAX(?, ?)', 45, 87) | |
.toString(), | |
); | |
/* SELECT MAX(45, 87) */ | |
log( | |
squel.update() | |
.table("students") | |
.set("name", "Thomas") | |
.toString(), | |
); | |
/* UPDATE students SET name = "Thomas" */ | |
log( | |
squel.update() | |
.table("students") | |
.table("teachers", "t") | |
.set("t.name", "Fred") | |
.toString(), | |
); | |
/* UPDATE students, teachers `t` SET t.name = "Fred" */ | |
log( | |
squel.update() | |
.table("students") | |
.set("name", "Fred") | |
.set("age", 29) | |
.set("score", 1.2) | |
.set("graduate", false) | |
.set("level", squel.select().field('MAX(level)').from('levels')) | |
.toString(), | |
); | |
/* UPDATE students SET name = "Fred", age = 29, score = 1.2, graduate = FALSE, level = (SELECT MAX(level) FROM levels) */ | |
log( | |
squel.update() | |
.table("students") | |
.set("time", "GETDATE()", { | |
dontQuote: true, | |
}) | |
.set("city", "London") | |
.toString(), | |
); | |
/* UPDATE students SET time = GETDATE(), city = "London" */ | |
log( | |
squel.update() | |
.table("students") | |
.set("name", "Fred") | |
.set("age", 29) | |
.set("nickname", null) | |
.toString(), | |
); | |
/* UPDATE students SET name = "Fred", age = 29, nickname = NULL */ | |
log( | |
squel.update() | |
.table("students") | |
.set("age = age + 1") | |
.toString(), | |
); | |
/* UPDATE students SET age = age + 1 */ | |
log( | |
squel.update() | |
.table("students") | |
.setFields({ age: 23, name: 'Fred' }) | |
.toString(), | |
); | |
/* UPDATE students SET age = 23, name = 'Fred' */ | |
log( | |
squel.update() | |
.table("students") | |
.set("name", "Fred") | |
.set("age", 29) | |
.where("id > 5") | |
.where("id < 102") | |
.order("id", false) | |
.limit(5) | |
.toString(), | |
); | |
/* UPDATE students SET name = "Fred", age = 29 WHERE (id > 5) AND (id < 102) ORDER BY id DESC LIMIT 5 */ | |
log( | |
squel.delete() | |
.from("students") | |
.toString(), | |
); | |
/* DELETE FROM students */ | |
log( | |
squel.delete() | |
.from("students", "s") | |
.outer_join("marks", "m", "m.student_id = s.id") | |
.toString(), | |
); | |
/* DELETE FROM students `s` OUTER JOIN marks `m` ON (m.student_id = s.id) */ | |
log( | |
squel.delete() | |
.from("students") | |
.where("id > 5") | |
.where("id < 102") | |
.order("id", false) | |
.limit(5) | |
.toString(), | |
); | |
/* DELETE FROM students WHERE (id > 5) AND (id < 102) ORDER BY id DESC LIMIT 5 */ | |
log( | |
squel.insert() | |
.into("students") | |
.set("name", "Thomas") | |
.toString(), | |
); | |
/* INSERT INTO students (name) VALUES ("Thomas") */ | |
log( | |
squel.insert() | |
.into("students") | |
.set("name", "Thomas") | |
.set("age", 29) | |
.set("score", 90.2) | |
.set("graduate", true) | |
.set("nickname", null) | |
.set("level", squel.select().field('MAX(level)').from('levels')) | |
.toString(), | |
); | |
/* INSERT INTO students (name, age, score, graduate, nickname, level) | |
VALUES ('Thomas', 29, 90.2, TRUE, NULL, (SELECT MAX(level) FROM levels)) */ | |
log( | |
squel.insert() | |
.into("students") | |
.set("time", "GETDATE()", { | |
dontQuote: true, | |
}) | |
.set("city", "London") | |
.toString(), | |
); | |
/* INSERT INTO students (time, city) VALUES(GETDATE(), "London") */ | |
log( | |
squel.insert() | |
.into("students") | |
.setFields({ name: "Thomas", age: 29 }) | |
.toString(), | |
); | |
/* INSERT INTO students (name, age) VALUES ('Thomas', 29) */ | |
log( | |
squel.insert() | |
.into("students") | |
.setFieldsRows([ | |
{ name: "Thomas", age: 29 }, | |
{ name: "Jane", age: 31 }, | |
]) | |
.toString(), | |
); | |
/* INSERT INTO students (name, age) VALUES ('Thomas', 29), ('Jane', 31) */ | |
log( | |
squel.insert() | |
.into("students") | |
.fromQuery( | |
['username'], | |
squel.select().field('name').from('candidates'), | |
) | |
.toString(), | |
); | |
/* INSERT INTO students (username) (SELECT name FROM candidates) */ | |
log( | |
squel.select() | |
.from("students") | |
.where("a = ? AND b = ?", "test", true) | |
.order("CALC(?, ?)", true, 1.2, false) | |
.limit(10) | |
.offset(3) | |
.toParam(), | |
); | |
/* | |
{ | |
text: SELECT * FROM students WHERE (a = ? AND b = ?) ORDER BY CALC(?, ?) ASC LIMIT ? OFFSET ?, | |
values: [ 'test', true, 1.2, false, 10, 3 ] | |
} | |
*/ | |
log( | |
squel.select() | |
.from("students") | |
.where("a = ? AND b IN ?", "test", squel.select().field('score').from('results').where('c IN ?', [5, 6, 7])) | |
.toParam(), | |
); | |
/* | |
{ | |
text: SELECT * FROM students WHERE (a = ? AND b IN (SELECT score FROM results WHERE c IN (?, ?, ?)))), | |
values: [ 'test', 5, 6, 7 ] | |
} | |
*/ | |
log( | |
squel.select() | |
.from("students") | |
.where("a IN ?", squel.select().field('score').from('results').where('c = ?', 5)) | |
.union( | |
squel.select().field('age').from('states').where('person = ?', | |
squel.select().field('id').from('students').where('name = ?', 'John').limit(1), | |
) | |
) | |
.toParam(), | |
); | |
/* | |
{ | |
text: SELECT * FROM students WHERE (a IN (SELECT score FROM results WHERE (c = ?))) UNION (SELECT age FROM states WHERE (person = (SELECT id FROM students WHERE (name = ?) LIMIT 1))), | |
values: [ 5, 'John' ] | |
} | |
*/ | |
log( | |
squel.insert({ numberedParameters: true }) | |
.into("students") | |
.set("a", "test") | |
.set("b", 1) | |
.set("c", null) | |
.toParam(), | |
); | |
/* | |
{ | |
text: INSERT INTO students (a, b, c) VALUES ($1, $2, $3) | |
values: [ 'test', 1, null ] | |
} | |
*/ | |
log( | |
squel.insert() | |
.into("students") | |
.set("a", "test") | |
.set("b", 1) | |
.set("c", null) | |
.toParam({ numberedParameters: true, numberedParametersStartAt: 3 }), | |
); | |
/* | |
{ | |
text: INSERT INTO students (a, b, c) VALUES ($3, $4, $5) | |
values: [ 'test', 1, null ] | |
} | |
*/ | |
log( | |
squel.insert() | |
.into("students") | |
.setFieldsRows([ | |
{ name: "Thomas", age: 29 }, | |
{ name: "Jane", age: 31 }, | |
]) | |
.toParam(), | |
); | |
/* | |
{ | |
text: INSERT INTO students (name, age) VALUES (?, ?), (?, ?) | |
values: [ 'Thomas', 29, 'Jane', 31 ] | |
} | |
*/ | |
log( | |
squel.select() | |
.field("id") | |
.from("students") | |
.where("age IN ?", [18, 19, 20]) | |
.toParam(), | |
); | |
/* | |
{ | |
text: SELECT id FROM students WHERE (age IN (?, ?, ?)), | |
values: [ 18, 19, 20 ] | |
} | |
*/ | |
log( | |
squel.update() | |
.table('students') | |
.set('modified', 'NOW()') | |
.toString(), | |
); | |
/* UPDATE students SET modified = 'NOW()' */ | |
log( | |
squel.update() | |
.table('students') | |
.set('modified', squel.str('NOW()')) | |
.toString(), | |
); | |
/* UPDATE students SET modified = (NOW()) */ | |
log( | |
squel.update() | |
.table('students') | |
.set('modified', squel.rstr('NOW()')) | |
.toString(), | |
); | |
/* UPDATE students SET modified = NOW() */ | |
log( | |
squel.select() | |
.from("students") | |
.where("age IN ?", squel.str('RANGE(?, ?)', 1, 1.2)) | |
.toParam(), | |
); | |
/* | |
{ | |
text: SELECT * FROM students WHERE (age IN (RANGE(?, ?))), | |
values: [ 1, 1.2 ] | |
} | |
*/ | |
log( | |
squel.select({ autoQuoteTableNames: true, autoQuoteFieldNames: true }) | |
.from("students", "s") | |
.field("s.name", "Student name") | |
.toString(), | |
); | |
/* SELECT `s`.`name` AS "Student name" FROM `students` `s` */ | |
log( | |
squel.select({ autoQuoteTableNames: true, autoQuoteFieldNames: true }) | |
.from("students", "s") | |
.field("s.name", "Student name", { ignorePeriodsForFieldNameQuotes: true }) | |
.toString(), | |
); | |
/* SELECT `s.name` AS "Student name" FROM `students` `s` */ | |
log( | |
squel.select({ autoQuoteTableNames: true, autoQuoteFieldNames: true, nameQuoteCharacter: '|' }) | |
.from("students") | |
.field("name", "Student name") | |
.toString(), | |
); | |
/* SELECT |name| AS "Student name" FROM |students| */ | |
log( | |
squel.select({ tableAliasQuoteCharacter: '|', fieldAliasQuoteCharacter: '~' }) | |
.from("students", "s") | |
.field("name", "Student name") | |
.toString(), | |
); | |
/* SELECT name AS ~Student name~ FROM students |s| */ | |
log( | |
squel.select({ autoQuoteAliasNames: false }) | |
.from("students", "s") | |
.field("name", "Student_name") | |
.toString(), | |
); | |
/* SELECT name AS Student_name FROM students s */ | |
log( | |
squel.select() | |
.from("students") | |
.where("(id < 500 AND (id > 100 OR name <> 'Thomas') AND " | |
+ "(age BETWEEN 20 AND 25 OR (name <> RANDOMNAME(?)))) OR (nickname = 'Hardy')", 10) | |
.toString(), | |
); | |
/* SELECT * FROM students WHERE | |
((id < 500 AND (id > 100 OR name <> 'Thomas') AND (age BETWEEN 20 AND 25 OR (name <> RANDOMNAME(10)))) | |
OR (nickname = 'Hardy')) */ | |
log( | |
squel.expr() | |
.and("id < ?", 500) | |
.and( | |
squel.expr() | |
.or("id > ?", 100) | |
.or("name <> ?", 'Thomas') | |
) | |
.and( | |
squel.expr() | |
.or("age BETWEEN ? AND ?", 20, 25) | |
.or("name <> ?", squel.str('RANDOMNAME(?)', 10)), | |
) | |
.or("nickname = ?", 'Hardy') | |
.toString(), | |
); | |
/* id < 500 AND (id > 100 OR name <> 'Thomas') AND (age BETWEEN 20 AND 25 OR name <> (RANDOMNAME(10))) | |
OR nickname = 'Hardy' | |
*/ | |
log( | |
squel.select() | |
.field("s.id") | |
.from("students", "s") | |
.where( | |
squel.expr() | |
.and("s.name <> 'Fred'") | |
.and( | |
squel.expr() | |
.or("s.id = 5") | |
.or("s.id = 6") | |
) | |
) | |
.join("teachers", "t", | |
squel.expr() | |
.and("s.id = t.sid") | |
.and("t.name = 'Frances'"), | |
) | |
.toString(), | |
); | |
/* | |
SELECT s.id FROM students `s` | |
INNER JOIN teachers `t` ON (s.id = t.sid AND t.name = 'Frances') | |
WHERE (s.name <> 'Fred' AND (s.id = 5 OR s.id = 6)) | |
*/ | |
log( | |
squel.update({ | |
stringFormatter: function(str) { | |
return "u'" + str + "'"; | |
} | |
}) | |
.table("students") | |
.set("name", "Jack") | |
.toString(), | |
); | |
/* UPDATE students SET name = u'Jack' */ | |
let myDate = new Date(2012, 4, 22); | |
let myDate2 = new Date(2013, 5, 30); | |
log( | |
squel.update() | |
.table("students") | |
.set("start_date", myDate.getFullYear() + '-' + (myDate.getMonth() + 1) + '-' + myDate.getDate()) | |
.set("end_date", myDate2.getFullYear() + '-' + (myDate2.getMonth() + 1) + '-' + myDate2.getDate()) | |
.toString(), | |
); | |
/* UPDATE students SET start_date = '2012-5-22', end_date = '2013-6-30' */ | |
myDate = new Date(2012, 4, 22); | |
myDate2 = new Date(2013, 5, 30); | |
/* Tell Squel how to handle Date objects */ | |
squel.registerValueHandler(Date, function(date) { | |
return '"' + date.getFullYear() + '-' + (date.getMonth() + 1) + '-' + date.getDate() + '"'; | |
}); | |
log( | |
squel.update() | |
.table("students") | |
.set("start_date", myDate) | |
.set("end_date", myDate2) | |
.toString(), | |
); | |
/* UPDATE students SET start_date = ("2012-5-22"), end_date = ("2013-6-30") */ | |
/* OOP Inheritance mechanism (substitute your own favourite library for this!) */ | |
// ERRORING | |
// Function.prototype.inheritsFrom = function( parentClassOrObject ) { | |
// this.prototype = new parentClassOrObject; | |
// this.prototype.constructor = this; | |
// this.prototype.parent = parentClassOrObject.prototype; | |
// }; | |
/* Base type */ | |
const ClassA = function() { this.a = 1; }; | |
/* Sub-type */ | |
// ERRORING | |
// let ClassB = function() { this.a = 2; }; | |
// ClassB.inheritsFrom(ClassA); | |
/* Register base type */ | |
squel.registerValueHandler(ClassA, function(obj) { | |
return obj.a; | |
}); | |
// ERRORING | |
// log( | |
// squel.update() | |
// .table("students") | |
// .set("value", new ClassB()) | |
// .toString(), | |
// ); | |
/* UPDATE students SET value = 2 */ | |
squel.registerValueHandler('boolean', function(v) { | |
return v ? 'YES' : 'NO'; | |
}); | |
log( | |
squel.update() | |
.table("students") | |
.set("value", true) | |
.toString(), | |
); | |
/* UPDATE students SET value = (YES) */ | |
squel.registerValueHandler('boolean', function(v) { | |
return { | |
value: v ? 'YES' : 'NO', | |
rawNesting: true, | |
} | |
}); | |
log( | |
squel.update() | |
.table("students") | |
.set("value", true) | |
.toString(), | |
); | |
/* UPDATE students SET value = YES */ | |
/* Global handler */ | |
squel.registerValueHandler(Date, function(date) { | |
return date.getFullYear(); | |
}); | |
log( | |
squel.update() | |
.registerValueHandler(Date, function(date) { | |
return '"[' + date.getFullYear() + ']"'; | |
}) | |
.table('students') | |
.set('value', new Date(2013,5,1)) | |
.toString(), | |
); | |
/* UPDATE students SET value = ("[2013]") */ | |
myDate = new Date(2012, 4, 22); | |
myDate2 = new Date(2013, 5, 30); | |
/* Tell Squel how to handle Date objects */ | |
squel.registerValueHandler(Date, function(date) { | |
return date.getFullYear() + '-' + (date.getMonth() + 1) + '-' + date.getDate(); | |
}); | |
log( | |
squel.select() | |
.from("students") | |
.where("start_date >= ?", myDate) | |
.where("end_date <= ?", myDate2) | |
.toParam(), | |
); | |
/* | |
{ | |
text: SELECT * FROM students WHERE (start_date >= ?) AND (end_date <= ?), | |
values: [ '2012-5-22', '2013-6-30' ] | |
} | |
*/ | |
/* We create a convenience method to make it easy to instantiate our customized UPDATE builder */ | |
// ERRORING | |
// squel.myupdate = function(options) { | |
// return squel.update(options, [ | |
// new squel.cls.StringBlock(options, 'UPDATE'), | |
// new squel.cls.UpdateTableBlock(options), | |
// new squel.cls.SetFieldBlock(options), | |
// new squel.cls.WhereBlock(options), | |
// new squel.cls.OrderByBlock(options), | |
// new squel.cls.OffsetBlock(options), | |
// new squel.cls.LimitBlock(options), | |
// ]); | |
// }; | |
// | |
// log( | |
// squel.myupdate() | |
// .table('students') | |
// .set('status', 'active') | |
// .limit(10) | |
// .offset(2) | |
// .toString(), | |
// ); | |
/* UPDATE students SET status = 'active' OFFSET 2 LIMIT 10 */ | |
/* | |
NOTE: All methods prefixed with '_' are internal and not exposed via the | |
query builder. | |
*/ | |
// ERRORING | |
// class CreateTableBlock extends squel.cls.Block { | |
// /** The method exposed by the query builder */ | |
// table (name) { | |
// this._name = name; | |
// } | |
// | |
// /** The method which generates the output */ | |
// _toParamString (options) { | |
// return { | |
// text: this._name, | |
// values: [], /* values for paramterized queries */ | |
// }; | |
// } | |
// } | |
// | |
// class CreateFieldBlock extends squel.cls.Block { | |
// constructor (options) { | |
// super(options); | |
// this._fields = []; | |
// } | |
// | |
// /** The method exposed by the query builder */ | |
// field (name, type) { | |
// this._fields.push({ | |
// name: name, type, | |
// }); | |
// } | |
// | |
// /** The method which generates the output */ | |
// _toParamString (options) { | |
// const str = this._fields.map((f) => { | |
// return `${f.name} ${f.type.toUpperCase()}`; | |
// }).join(', '); | |
// | |
// return { | |
// text: `(${str})`, | |
// values: [], /* values for paramterized queries */ | |
// }; | |
// } | |
// } | |
// | |
// class CreateTableQuery extends squel.cls.QueryBuilder { | |
// constructor (options, blocks) { | |
// super(options, blocks || [ | |
// new squel.cls.StringBlock(options, 'CREATE TABLE'), | |
// new CreateTableBlock(options), | |
// new CreateFieldBlock(options), | |
// ]); | |
// } | |
// } | |
// | |
// /** Convenience method */ | |
// squel.create = function(options) { | |
// return new CreateTableQuery(options); | |
// }; | |
// | |
// /* Try it out! */ | |
// | |
// log( | |
// squel.create() | |
// .table("pet") | |
// .field("name", "varchar(20)") | |
// .field("owner", "varchar(20)") | |
// .field("species", "varchar(20)") | |
// .field("sex", "char(1)") | |
// .toString(), | |
// ); | |
/* | |
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1)) | |
*/ | |
const select1 = squel.select().from('students'); | |
const select2 = select1.clone().field('id').where('name = "david"'); | |
log( select1.toString() ); | |
/* SELECT * FROM students */ | |
log( select2.toString() ); | |
/* SELECT id FROM students WHERE (name = "david") */ | |
const ins = squel.insert().into('students').set('name', 'David'); | |
const insMathew = ins.clone().set('name', 'Mathew'); | |
const insMark = insMathew.clone().set('name', 'Mark'); | |
log( insMathew.toString() ); | |
/* INSERT INTO students (name) VALUES('Mathew') */ | |
log( insMark.toString() ); | |
/* INSERT INTO students (name) VALUES('Mark') */ | |
const squelPostgres = squel.useFlavour('postgres'); | |
log( | |
squelPostgres.insert() | |
.into('table') | |
.set('field', 5) | |
.returning('*') | |
.toParam(), | |
); | |
/* | |
{ | |
text: 'INSERT INTO table (field) VALUES ($1) RETURNING *' | |
values: [5] | |
} | |
*/ | |
log( | |
squelPostgres.insert() | |
.into('table') | |
.set('field', 5) | |
.with('someAlias', squelPostgres.insert().into('otherTable').set('otherField', 3)) | |
.toParam(), | |
); | |
/* | |
{ | |
text: 'WITH someAlias AS (INSERT INTO otherTable (otherField) VALUES ($1)) INSERT INTO table (field) VALUES ($2)' | |
values: [3, 5] | |
} | |
*/ | |
log( | |
squelPostgres.select() | |
.distinct('field1', 'field2') | |
.from('table') | |
.order('field1') | |
.order('field2') | |
.order('field3') | |
.toString(), | |
); | |
/* SELECT DISTINCT ON (field1, field2) * FROM table ORDER BY field1 ASC, field2 ASC, field3 ASC */ | |
const squelMysql = squel.useFlavour('mysql'); | |
log( | |
squelMysql.insert() | |
.into('table') | |
.setFields({ | |
field1: 'abc', | |
field2: 3, | |
}) | |
.onDupUpdate('field1', 'upd') | |
.toParam(), | |
); | |
/* | |
{ | |
text: 'INSERT INTO table (field1, field2) VALUES (?, ?) ON DUPLICATE KEY UPDATE field1 = ?' | |
values: ['abc', 3, 'upd'] | |
} | |
*/ | |
const squelMssql = squel.useFlavour('mssql'); | |
log( | |
squelMssql.insert() | |
.into('table') | |
.setFields({ | |
field1: new Date('2012-12-12T04:30:00Z'), | |
}) | |
.toString(), | |
); | |
/* | |
INSERT INTO table (field1) VALUES(('2012-12-12 4:30:0')) | |
*/ | |
log( | |
squelMssql.select() | |
.from('table') | |
.field('field') | |
.top(10) | |
.toString(), | |
); | |
/* | |
SELECT TOP (10) field FROM table | |
*/ | |
log( | |
squelMysql.select() | |
.from('table') | |
.field('field') | |
.limit(10) | |
.offset(5) | |
.toString(), | |
); | |
/* | |
SELECT field FROM table OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY | |
*/ | |
log( | |
squelMssql.update() | |
.table('table') | |
.output('id', 'blah') | |
.set('field', 1) | |
.toString(), | |
); | |
/* | |
UPDATE table SET field = 1 OUTPUT INSERTED.id AS blah | |
*/ | |
log( | |
squelMssql.insert() | |
.into('table') | |
.output('id') | |
.set('field', 1) | |
.toString(), | |
); | |
/* | |
INSERT INTO table (field) OUTPUT INSERTED.id VALUES (1) | |
*/ |