Skip to content

Commit 250f5ec

Browse files
author
Paul Winkler
committed
sqlite dialect and all sqlite tests. subqueries can now have their columns selected as tables
1 parent a882f30 commit 250f5ec

20 files changed

Lines changed: 173 additions & 22 deletions

‎lib/dialect/mysql.js‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10,6 +10,8 @@ var Mysql = function() {
1010

1111
util.inherits(Mysql, require(__dirname + '/postgres'));
1212

13+
Mysql.prototype._myClass = Mysql;
14+
1315
Mysql.prototype._quoteCharacter = '`';
1416

1517
Mysql.prototype._arrayAggFunctionName = 'GROUP_CONCAT';

‎lib/dialect/postgres.js‎

Lines changed: 13 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,8 @@ var Postgres = function() {
99
this.params = [];
1010
};
1111

12+
Postgres.prototype._myClass = Postgres;
13+
1214
Postgres.prototype._arrayAggFunctionName = 'array_agg';
1315

1416
Postgres.prototype.getQuery = function(queryNode) {
@@ -223,20 +225,18 @@ Postgres.prototype.visitQuery = function(queryNode) {
223225
};
224226

225227
Postgres.prototype.visitSubquery = function(queryNode) {
226-
var result = [];
227-
for(var i = 0; i < queryNode.nodes.length; i ++) {
228-
var res = this.visit(queryNode.nodes[i]);
229-
result = result.concat(res);
230-
}
231-
//implicit 'from'
232-
if(!this._visitedFrom) {
233-
var select = result.slice(0, this._selectOrDeleteEndIndex);
234-
var from = this.visitFrom(new From().add(queryNode.table.toNode()));
235-
var rest = result.slice(this._selectOrDeleteEndIndex);
236-
result = select.concat(from).concat(rest);
237-
}
228+
var subQuery = new this._myClass();
229+
subQuery.visitQuery(queryNode);
230+
this.params = this.params.concat(subQuery.params);
231+
232+
var result = subQuery.output;
233+
238234
result[0] = '('+result[0];
239-
result[result.length-1] = result[result.length-1] + ') ' + queryNode.alias;
235+
result[result.length-1] = result[result.length-1] + ')';
236+
if(queryNode.alias) {
237+
result[result.length-1] = result[result.length-1] + ' ' + queryNode.alias;
238+
}
239+
240240
return result;
241241
};
242242

‎lib/dialect/sqlite.js‎

Lines changed: 2 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,8 @@ var Sqlite = function() {
99

1010
util.inherits(Sqlite, require(__dirname + '/postgres'));
1111

12+
Sqlite.prototype._myClass = Sqlite;
13+
1214
Sqlite.prototype._arrayAggFunctionName = 'GROUP_CONCAT';
1315

1416
Sqlite.prototype.visitDefault = function(parameter) {
@@ -31,12 +33,4 @@ Sqlite.prototype.visitRenameColumn = function(renameColumn) {
3133
throw new Error('SQLite does not allow renaming columns.');
3234
};
3335

34-
Sqlite.prototype.visitIfExists = function() {
35-
throw new Error('SQLite does not support IF EXISTS.');
36-
};
37-
38-
Sqlite.prototype.visitIfNotExists = function() {
39-
throw new Error('SQLite does not support IF NOT EXISTS.');
40-
};
41-
4236
module.exports = Sqlite;

‎lib/node/index.js‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,7 @@
11
'use strict';
22

3+
var assert = require('assert');
4+
35
var Node = function(type) {
46
this.nodes = [];
57
};
@@ -9,6 +11,7 @@ Node.prototype.toNode = function() {
911
};
1012

1113
Node.prototype.add = function(node) {
14+
assert(node, 'Error while trying to add a non-existant node to a query');
1215
this.nodes.push(typeof node === 'string' ? new TextNode(node) : node.toNode());
1316
return this;
1417
};

‎lib/node/query.js‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -41,8 +41,24 @@ var Query = Node.define({
4141
//arg could be a column instead of a node
4242
select.add(args[i]);
4343
}
44+
// if this is a subquery then add reference to this column
45+
if(this.type === 'SUBQUERY') {
46+
for(var j = 0; j < select.nodes.length; j++) {
47+
var name = select.nodes[j].alias || select.nodes[j].name;
48+
var col = new Column(select.nodes[j]);
49+
col.name = name;
50+
col.table = this;
51+
if( this[name] === undefined ) {
52+
this[name] = col;
53+
}
54+
}
55+
}
4456
return this.add(select);
4557
},
58+
star: function() {
59+
assert( this.type === 'SUBQUERY', 'star() can only be used on a subQuery');
60+
return new Column({table: this, star: true});
61+
},
4662
from: function(tableNode) {
4763
var from = new From().add(tableNode);
4864
return this.add(from);

‎test/dialects/aggregate-tests.js‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7,41 +7,47 @@ var post = Harness.definePostTable();
77
Harness.test({
88
query : post.select(post.count()),
99
pg : 'SELECT COUNT("post".*) AS "post_count" FROM "post"',
10+
sqlite: 'SELECT COUNT("post".*) AS "post_count" FROM "post"',
1011
mysql : 'SELECT COUNT(`post`.*) AS `post_count` FROM `post`',
1112
params: []
1213
});
1314

1415
Harness.test({
1516
query : post.select(post.count('post_count')),
1617
pg : 'SELECT COUNT("post".*) AS "post_count" FROM "post"',
18+
sqlite: 'SELECT COUNT("post".*) AS "post_count" FROM "post"',
1719
msyql : 'SELECT COUNT(`post`.*) AS `post_count` FROM `post`',
1820
params: []
1921
});
2022

2123
Harness.test({
2224
query : post.select(post.count().as('post_amount')),
2325
pg : 'SELECT COUNT("post".*) AS "post_amount" FROM "post"',
26+
sqlite: 'SELECT COUNT("post".*) AS "post_amount" FROM "post"',
2427
mysql : 'SELECT COUNT(`post`.*) AS `post_amount` FROM `post`',
2528
params: []
2629
});
2730

2831
Harness.test({
2932
query : post.select(post.content.count()),
3033
pg : 'SELECT COUNT("post"."content") AS "content_count" FROM "post"',
34+
sqlite: 'SELECT COUNT("post"."content") AS "content_count" FROM "post"',
3135
mysql : 'SELECT COUNT(`post`.`content`) AS `content_count` FROM `post`',
3236
params: []
3337
});
3438

3539
Harness.test({
3640
query : post.select(post.content.count('content_count')),
3741
pg : 'SELECT COUNT("post"."content") AS "content_count" FROM "post"',
42+
sqlite: 'SELECT COUNT("post"."content") AS "content_count" FROM "post"',
3843
mysql : 'SELECT COUNT(`post`.`content`) AS `content_count` FROM `post`',
3944
params: []
4045
});
4146

4247
Harness.test({
4348
query : post.select(post.content.count().as('content_count')),
4449
pg : 'SELECT COUNT("post"."content") AS "content_count" FROM "post"',
50+
sqlite: 'SELECT COUNT("post"."content") AS "content_count" FROM "post"',
4551
mysql : 'SELECT COUNT(`post`.`content`) AS `content_count` FROM `post`',
4652
params: []
4753
});

‎test/dialects/alter-table-tests.js‎

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7,20 +7,32 @@ var Table = require(__dirname + '/../../lib/table');
77
Harness.test({
88
query : post.alter().dropColumn(post.content),
99
pg : 'ALTER TABLE "post" DROP COLUMN "content"',
10+
sqlite: {
11+
text : 'Sqlite cannot drop columns',
12+
throws: true
13+
},
1014
mysql : 'ALTER TABLE `post` DROP COLUMN `content`',
1115
params: []
1216
});
1317

1418
Harness.test({
1519
query : post.alter().dropColumn(post.content).dropColumn(post.userId),
1620
pg : 'ALTER TABLE "post" DROP COLUMN "content", DROP COLUMN "userId"',
21+
sqlite: {
22+
text : 'Sqlite cannot drop columns',
23+
throws: true
24+
},
1725
mysql : 'ALTER TABLE `post` DROP COLUMN `content`, DROP COLUMN `userId`',
1826
params: []
1927
});
2028

2129
Harness.test({
2230
query : post.alter().dropColumn('content').dropColumn('userId'),
2331
pg : 'ALTER TABLE "post" DROP COLUMN "content", DROP COLUMN "userId"',
32+
sqlite: {
33+
text : 'Sqlite cannot drop columns',
34+
throws: true
35+
},
2436
mysql : 'ALTER TABLE `post` DROP COLUMN `content`, DROP COLUMN `userId`',
2537
params: []
2638
});
@@ -39,20 +51,29 @@ var group = Table.define({
3951
Harness.test({
4052
query : group.alter().addColumn(group.id),
4153
pg : 'ALTER TABLE "group" ADD COLUMN "id" varchar(100)',
54+
sqlite: 'ALTER TABLE "group" ADD COLUMN "id" varchar(100)',
4255
mysql : 'ALTER TABLE `group` ADD COLUMN `id` varchar(100)',
4356
params: []
4457
});
4558

4659
Harness.test({
4760
query : group.alter().addColumn(group.id).addColumn(group.userId),
4861
pg : 'ALTER TABLE "group" ADD COLUMN "id" varchar(100), ADD COLUMN "userId" varchar(100)',
62+
sqlite: {
63+
text : 'Sqlite cannot add more than one column at a time',
64+
throws: true
65+
},
4966
mysql : 'ALTER TABLE `group` ADD COLUMN `id` varchar(100), ADD COLUMN `userId` varchar(100)',
5067
params: []
5168
});
5269

5370
Harness.test({
5471
query : group.alter().addColumn('id', 'varchar(100)').addColumn('userId', 'varchar(100)'),
5572
pg : 'ALTER TABLE "group" ADD COLUMN "id" varchar(100), ADD COLUMN "userId" varchar(100)',
73+
sqlite: {
74+
text : 'Sqlite cannot add more than one column at a time',
75+
throws: true
76+
},
5677
mysql : 'ALTER TABLE `group` ADD COLUMN `id` varchar(100), ADD COLUMN `userId` varchar(100)',
5778
params: []
5879
});
@@ -64,19 +85,31 @@ Harness.test({
6485
text: 'Mysql requires data type for renaming a column',
6586
throws: true
6687
},
88+
sqlite: {
89+
text : 'Sqlite cannot rename columns',
90+
throws: true
91+
},
6792
params: []
6893
});
6994

7095
Harness.test({
7196
query : group.alter().renameColumn(group.userId, 'newUserId'),
7297
pg : 'ALTER TABLE "group" RENAME COLUMN "userId" TO "newUserId"',
98+
sqlite: {
99+
text : 'Sqlite cannot rename columns',
100+
throws: true
101+
},
73102
mysql : 'ALTER TABLE `group` CHANGE COLUMN `userId` `newUserId` varchar(100)',
74103
params: []
75104
});
76105

77106
Harness.test({
78107
query : group.alter().renameColumn('userId', group.id),
79108
pg : 'ALTER TABLE "group" RENAME COLUMN "userId" TO "id"',
109+
sqlite: {
110+
text : 'Sqlite cannot rename columns',
111+
throws: true
112+
},
80113
mysql : 'ALTER TABLE `group` CHANGE COLUMN `userId` `id` varchar(100)',
81114
params: []
82115
});

‎test/dialects/create-table-tests.js‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -17,13 +17,15 @@ var group = Table.define({
1717
Harness.test({
1818
query : group.create(),
1919
pg : 'CREATE TABLE "group" ("id" varchar(100), "user_id" varchar(100))',
20+
sqlite: 'CREATE TABLE "group" ("id" varchar(100), "user_id" varchar(100))',
2021
mysql : 'CREATE TABLE `group` (`id` varchar(100), `user_id` varchar(100))',
2122
params: []
2223
});
2324

2425
Harness.test({
2526
query : group.create().ifNotExists(),
2627
pg : 'CREATE TABLE IF NOT EXISTS "group" ("id" varchar(100), "user_id" varchar(100))',
28+
sqlite: 'CREATE TABLE IF NOT EXISTS "group" ("id" varchar(100), "user_id" varchar(100))',
2729
mysql : 'CREATE TABLE IF NOT EXISTS `group` (`id` varchar(100), `user_id` varchar(100))',
2830
params: []
2931
});

‎test/dialects/delete-tests.js‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,7 @@ var post = Harness.definePostTable();
66
Harness.test({
77
query : post['delete']().where(post.content.equals('')),
88
pg : 'DELETE FROM "post" WHERE ("post"."content" = $1)',
9+
sqlite: 'DELETE FROM "post" WHERE ("post"."content" = $1)',
910
mysql : 'DELETE FROM `post` WHERE (`post`.`content` = ?)',
1011
params: ['']
1112
});

‎test/dialects/drop-table-tests.js‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6,13 +6,15 @@ var post = Harness.definePostTable();
66
Harness.test({
77
query : post.drop(),
88
pg : 'DROP TABLE "post"',
9+
sqlite: 'DROP TABLE "post"',
910
mysql : 'DROP TABLE `post`',
1011
params: []
1112
});
1213

1314
Harness.test({
1415
query : post.drop().ifExists(),
1516
pg : 'DROP TABLE IF EXISTS "post"',
17+
sqlite: 'DROP TABLE IF EXISTS "post"',
1618
mysql : 'DROP TABLE IF EXISTS `post`',
1719
params: []
1820
});

0 commit comments

Comments
 (0)