UNPKG

9.4 kBJavaScriptView Raw
1'use strict';
2var assert = require('assert');
3
4var sql = require('../lib');
5
6var user = sql.define({
7 name: 'user',
8 columns: ['id', 'email']
9});
10
11suite('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});