1 | 'use strict';
|
2 | var assert = require('assert');
|
3 |
|
4 | var sql = require('../lib');
|
5 |
|
6 | var user = sql.define({
|
7 | name: 'user',
|
8 | columns: ['id', 'email']
|
9 | });
|
10 |
|
11 | suite('index', function() {
|
12 | test('unknown dialect throws exception', function() {
|
13 | assert.throws(function() {
|
14 | sql.setDialect('asdf');
|
15 | });
|
16 | });
|
17 |
|
18 | test('stores the default dialect\'s name if none has been passed', function() {
|
19 | assert.equal(sql.create().dialectName, 'postgres');
|
20 | });
|
21 |
|
22 | test('stores the sqlite dialect', function() {
|
23 | assert.equal(sql.create('sqlite').dialectName, 'sqlite');
|
24 | });
|
25 |
|
26 | test('stores the mysql dialect', function() {
|
27 | assert.equal(sql.create('mysql').dialectName, 'mysql');
|
28 | });
|
29 |
|
30 | test('stores the mssql dialect', function() {
|
31 | assert.equal(sql.create('mssql').dialectName, 'mssql');
|
32 | });
|
33 |
|
34 | test('stores the oracle dialect', function() {
|
35 | assert.equal(sql.create('oracle').dialectName, 'oracle');
|
36 | });
|
37 |
|
38 |
|
39 | test('can create a query using the default dialect', function() {
|
40 | var query = sql.select(user.id).from(user).where(user.email.equals('brian.m.carlson@gmail.com')).toQuery();
|
41 | assert.equal(query.text, 'SELECT "user"."id" FROM "user" WHERE ("user"."email" = $1)');
|
42 | assert.equal(query.values[0], 'brian.m.carlson@gmail.com');
|
43 | });
|
44 |
|
45 | test('setting dialect to postgres works', function() {
|
46 | sql.setDialect('postgres');
|
47 | var query = sql.select(user.id).from(user).where(user.email.equals('brian.m.carlson@gmail.com')).toQuery();
|
48 | assert.equal(query.text, 'SELECT "user"."id" FROM "user" WHERE ("user"."email" = $1)');
|
49 | assert.equal(query.values[0], 'brian.m.carlson@gmail.com');
|
50 | });
|
51 |
|
52 | test('sql.create creates an instance with a new dialect', function() {
|
53 | var mysql = sql.create('mysql');
|
54 | var query = mysql.select(user.id).from(user).where(user.email.equals('brian.m.carlson@gmail.com')).toQuery();
|
55 | assert.equal(query.text, 'SELECT `user`.`id` FROM `user` WHERE (`user`.`email` = ?)');
|
56 | assert.equal(query.values[0], 'brian.m.carlson@gmail.com');
|
57 | });
|
58 |
|
59 | test('sql.define for parallel dialects work independently', function() {
|
60 | var mssql = sql.create('mssql');
|
61 | var mysql = sql.create('mysql');
|
62 | var postgres = sql.create('postgres');
|
63 | var sqlite = sql.create('sqlite');
|
64 | var oracle = sql.create('oracle');
|
65 |
|
66 | var mssqlTable = mssql.define({name: 'table', columns: ['column']});
|
67 | var mysqlTable = mysql.define({name: 'table', columns: ['column']});
|
68 | var postgresTable = postgres.define({name: 'table', columns: ['column']});
|
69 | var sqliteTable = sqlite.define({name: 'table', columns: ['column']});
|
70 | var oracleTable = oracle.define({name: 'table', columns: ['column']});
|
71 |
|
72 | assert.equal(mysqlTable.sql, mysql);
|
73 | assert.equal(postgresTable.sql, postgres);
|
74 | assert.equal(sqliteTable.sql, sqlite);
|
75 | assert.equal(mssqlTable.sql, mssql);
|
76 | assert.equal(oracleTable.sql, oracle);
|
77 | });
|
78 |
|
79 | test('using Sql as a class', function() {
|
80 | var Sql = sql.Sql;
|
81 | var mssql = new Sql('mssql');
|
82 | var mysql = new Sql('mysql');
|
83 | var postgres = new Sql('postgres');
|
84 | var sqlite = new Sql('sqlite');
|
85 | var oracle = new Sql('oracle');
|
86 |
|
87 | assert.equal(mysql.dialect, require(__dirname + '/../lib/dialect/mysql'));
|
88 | assert.equal(postgres.dialect, require(__dirname + '/../lib/dialect/postgres'));
|
89 | assert.equal(sqlite.dialect, require(__dirname + '/../lib/dialect/sqlite'));
|
90 | assert.equal(mssql.dialect, require(__dirname + '/../lib/dialect/mssql'));
|
91 | assert.equal(oracle.dialect, require(__dirname + '/../lib/dialect/oracle'));
|
92 | });
|
93 |
|
94 | test('override dialect for toQuery using dialect name', function() {
|
95 | var Sql = sql.Sql;
|
96 | var mssql = new Sql('mssql');
|
97 | var mysql = new Sql('mysql');
|
98 | var postgres = new Sql('postgres');
|
99 | var sqlite = new Sql('sqlite');
|
100 | var oracle = new Sql('oracle');
|
101 |
|
102 | var sqliteQuery = mysql.select(user.id).from(user).where(user.email.equals('brian.m.carlson@gmail.com')).toQuery('sqlite');
|
103 | var postgresQuery = sqlite.select(user.id).from(user).where(user.email.equals('brian.m.carlson@gmail.com')).toQuery('postgres');
|
104 | var mysqlQuery = postgres.select(user.id).from(user).where(user.email.equals('brian.m.carlson@gmail.com')).toQuery('mysql');
|
105 | var mssqlQuery = mysql.select(user.id).from(user).where(user.email.equals('brian.m.carlson@gmail.com')).toQuery('mssql');
|
106 | var oracleQuery = oracle.select(user.id).from(user).where(user.email.equals('brian.m.carlson@gmail.com')).toQuery('oracle');
|
107 |
|
108 | var values = ['brian.m.carlson@gmail.com'];
|
109 | assert.equal(sqliteQuery.text, 'SELECT "user"."id" FROM "user" WHERE ("user"."email" = $1)');
|
110 | assert.deepEqual(sqliteQuery.values, values);
|
111 |
|
112 | assert.equal(postgresQuery.text, 'SELECT "user"."id" FROM "user" WHERE ("user"."email" = $1)');
|
113 | assert.deepEqual(postgresQuery.values, values);
|
114 |
|
115 | assert.equal(mysqlQuery.text, 'SELECT `user`.`id` FROM `user` WHERE (`user`.`email` = ?)');
|
116 | assert.deepEqual(mysqlQuery.values, values);
|
117 |
|
118 | assert.equal(mssqlQuery.text, 'SELECT [user].[id] FROM [user] WHERE ([user].[email] = @1)');
|
119 | assert.deepEqual(mssqlQuery.values, values);
|
120 |
|
121 | assert.equal(oracleQuery.text, 'SELECT "user"."id" FROM "user" WHERE ("user"."email" = :1)');
|
122 | assert.deepEqual(oracleQuery.values, values);
|
123 | });
|
124 |
|
125 | test('override dialect for toQuery using invalid dialect name', function() {
|
126 | var query = sql.select(user.id).from(user);
|
127 | assert.throws(function() {
|
128 | query.toQuery('invalid');
|
129 | });
|
130 | });
|
131 |
|
132 | test('using named queries with toNamedQuery', function() {
|
133 | var query = sql.select(user.id).from(user).where(user.email.equals('brian.m.carlson@gmail.com')).toNamedQuery('users');
|
134 | assert.equal(query.text, 'SELECT "user"."id" FROM "user" WHERE ("user"."email" = $1)');
|
135 | assert.equal(query.values[0], 'brian.m.carlson@gmail.com');
|
136 | assert.equal(query.name, 'users');
|
137 | });
|
138 |
|
139 | test('provide an empty query name for toNamedQuery', function() {
|
140 | var query = sql.select(user.id).from(user);
|
141 | assert.throws(function() {
|
142 | query.toNamedQuery('');
|
143 | });
|
144 | });
|
145 |
|
146 | test('provide an undefined query name for toNamedQuery', function() {
|
147 | var query = sql.select(user.id).from(user);
|
148 | assert.throws(function() {
|
149 | query.toNamedQuery();
|
150 | });
|
151 | });
|
152 |
|
153 | test('override dialect for toNamedQuery using dialect name', function() {
|
154 | var Sql = sql.Sql;
|
155 | var mysql = new Sql('mysql');
|
156 | var postgres = new Sql('postgres');
|
157 | var sqlite = new Sql('sqlite');
|
158 | var mssql = new Sql('mssql');
|
159 | var oracle = new Sql('oracle');
|
160 |
|
161 | var sqliteQuery = mysql.select(user.id).from(user).where(user.email.equals('brian.m.carlson@gmail.com')).toNamedQuery('user.select_brian','sqlite');
|
162 | var postgresQuery = sqlite.select(user.id).from(user).where(user.email.equals('brian.m.carlson@gmail.com')).toNamedQuery('user.select_brian','postgres');
|
163 | var mysqlQuery = postgres.select(user.id).from(user).where(user.email.equals('brian.m.carlson@gmail.com')).toNamedQuery('user.select_brian','mysql');
|
164 | var oracleQuery = mssql.select(user.id).from(user).where(user.email.equals('brian.m.carlson@gmail.com')).toNamedQuery('user.select_brian','oracle');
|
165 | var mssqlQuery = oracle.select(user.id).from(user).where(user.email.equals('brian.m.carlson@gmail.com')).toNamedQuery('user.select_brian','mssql');
|
166 |
|
167 |
|
168 | var values = ['brian.m.carlson@gmail.com'];
|
169 | assert.equal(sqliteQuery.text, 'SELECT "user"."id" FROM "user" WHERE ("user"."email" = $1)');
|
170 | assert.deepEqual(sqliteQuery.values, values);
|
171 | assert.equal('user.select_brian', sqliteQuery.name);
|
172 |
|
173 | assert.equal(postgresQuery.text, 'SELECT "user"."id" FROM "user" WHERE ("user"."email" = $1)');
|
174 | assert.deepEqual(postgresQuery.values, values);
|
175 | assert.equal('user.select_brian', postgresQuery.name);
|
176 |
|
177 | assert.equal(mysqlQuery.text, 'SELECT `user`.`id` FROM `user` WHERE (`user`.`email` = ?)');
|
178 | assert.deepEqual(mysqlQuery.values, values);
|
179 | assert.equal('user.select_brian', mysqlQuery.name);
|
180 |
|
181 | assert.equal(mssqlQuery.text, 'SELECT [user].[id] FROM [user] WHERE ([user].[email] = @1)');
|
182 | assert.deepEqual(mssqlQuery.values, values);
|
183 | assert.equal('user.select_brian', mssqlQuery.name);
|
184 |
|
185 | assert.equal(oracleQuery.text, 'SELECT "user"."id" FROM "user" WHERE ("user"."email" = :1)');
|
186 | assert.deepEqual(oracleQuery.values, values);
|
187 | assert.equal('user.select_brian', oracleQuery.name);
|
188 |
|
189 | });
|
190 |
|
191 | test('override dialect for toNamedQuery using invalid dialect name', function() {
|
192 | var query = sql.select(user.id).from(user);
|
193 | assert.throws(function() {
|
194 | query.toNamedQuery('name', 'invalid');
|
195 | });
|
196 | });
|
197 |
|
198 | test('mssql default parameter place holder is @index', function() {
|
199 | var Sql = sql.Sql;
|
200 | var mssql = new Sql('mssql');
|
201 | var query = mssql.select(user.id).from(user).where(user.email.equals('x@y.com')).toQuery();
|
202 | assert.equal(query.text, 'SELECT [user].[id] FROM [user] WHERE ([user].[email] = @1)');
|
203 | assert.equal(query.values[0], 'x@y.com');
|
204 | });
|
205 |
|
206 | test('mssql override default parameter placeholder with ?', function() {
|
207 | var Sql = sql.Sql;
|
208 | var mssql = new Sql('mssql',{questionMarkParameterPlaceholder:true});
|
209 | var query = mssql.select(user.id).from(user).where(user.email.equals('x@y.com')).toQuery();
|
210 | assert.equal(query.text, 'SELECT [user].[id] FROM [user] WHERE ([user].[email] = ?)');
|
211 | assert.equal(query.values[0], 'x@y.com');
|
212 | });
|
213 |
|
214 | });
|