Skip to content

Commit 39be3f3

Browse files
briancbrianc
committed
Merge pull request brianc#301 from danrzeppa/dan-mssqlinsertselect
INSERT INTO ... SELECT create with .add()
2 parents 600abc1 + 687eecf commit 39be3f3

2 files changed

Lines changed: 124 additions & 2 deletions

File tree

‎lib/dialect/postgres.js‎

Lines changed: 23 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -596,7 +596,7 @@ Postgres.prototype.visitOverlap = function(overlap) {
596596
};
597597

598598
Postgres.prototype.visitQuery = function(queryNode) {
599-
if (this._queryNode) return this.visitSubquery(queryNode);
599+
if (this._queryNode) return this.visitSubquery(queryNode,dontParenthesizeSubQuery(this._queryNode));
600600
this._queryNode = queryNode;
601601
// need to sort the top level query nodes on visitation priority
602602
// so select/insert/update/delete comes before from comes before where
@@ -678,7 +678,7 @@ Postgres.prototype.visitQueryHelper=function(actions,targets,filters){
678678
return this.output;
679679
};
680680

681-
Postgres.prototype.visitSubquery = function(queryNode) {
681+
Postgres.prototype.visitSubquery = function(queryNode,dontParenthesize) {
682682
// create another query builder of the current class to build the subquery
683683
var subQuery = new this._myClass(this.config);
684684

@@ -696,6 +696,9 @@ Postgres.prototype.visitSubquery = function(queryNode) {
696696
}
697697

698698
var alias = queryNode.alias;
699+
if (dontParenthesize) {
700+
return [subQuery.output.join(' ') + (alias ? ' ' + this.quote(alias) : '')];
701+
}
699702
return ['(' + subQuery.output.join(' ') + ')' + (alias ? ' ' + this.quote(alias) : '')];
700703
};
701704

@@ -1045,4 +1048,22 @@ Postgres.prototype.handleDistinct = function(actions,filters) {
10451048
selectInfo.node.isDistinct = true;
10461049
};
10471050

1051+
/**
1052+
* If the parent of the subquery is an INSERT we don't want to parenthesize.
1053+
* This happens when you create the query like so:
1054+
*
1055+
* var query=post.insert(post.id)
1056+
* var select=user.select(user.id)
1057+
* query.add(select)
1058+
*
1059+
* @param parentQuery
1060+
* @returns {boolean}
1061+
*/
1062+
function dontParenthesizeSubQuery(parentQuery){
1063+
if (!parentQuery) return false;
1064+
if (parentQuery.nodes.length == 0) return false;
1065+
if (parentQuery.nodes[0].type != 'INSERT') return false;
1066+
return true;
1067+
}
1068+
10481069
module.exports = Postgres;

‎test/dialects/insert-tests.js‎

Lines changed: 101 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -675,3 +675,104 @@ Harness.test({
675675
string: 'INSERT INTO "arraytest" ("id", "numbers") VALUES (1, (\'one\', \'two\', \'three\'))'
676676
}
677677
});
678+
679+
Harness.test({
680+
query: post.insert(post.userId).select(user.id).from(user),
681+
pg: {
682+
text : 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user"',
683+
string: 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user"'
684+
},
685+
sqlite: {
686+
text : 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user"',
687+
string: 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user"'
688+
},
689+
mysql: {
690+
text : 'INSERT INTO `post` (`userId`) SELECT `user`.`id` FROM `user`',
691+
string: 'INSERT INTO `post` (`userId`) SELECT `user`.`id` FROM `user`'
692+
},
693+
mssql: {
694+
text : 'INSERT INTO [post] ([userId]) SELECT [user].[id] FROM [user]',
695+
string: 'INSERT INTO [post] ([userId]) SELECT [user].[id] FROM [user]'
696+
},
697+
oracle: {
698+
text : 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user"',
699+
string: 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user"'
700+
},
701+
params: []
702+
});
703+
704+
Harness.test({
705+
query: post.insert(post.userId).add(user.select(user.id)),
706+
pg: {
707+
text : 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user"',
708+
string: 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user"'
709+
},
710+
sqlite: {
711+
text : 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user"',
712+
string: 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user"'
713+
},
714+
mysql: {
715+
text : 'INSERT INTO `post` (`userId`) SELECT `user`.`id` FROM `user`',
716+
string: 'INSERT INTO `post` (`userId`) SELECT `user`.`id` FROM `user`'
717+
},
718+
mssql: {
719+
text : 'INSERT INTO [post] ([userId]) SELECT [user].[id] FROM [user]',
720+
string: 'INSERT INTO [post] ([userId]) SELECT [user].[id] FROM [user]'
721+
},
722+
oracle: {
723+
text : 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user"',
724+
string: 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user"'
725+
},
726+
params: []
727+
});
728+
729+
Harness.test({
730+
query: post.insert(post.userId).add(user.select(user.id).from(user)),
731+
pg: {
732+
text : 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user"',
733+
string: 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user"'
734+
},
735+
sqlite: {
736+
text : 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user"',
737+
string: 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user"'
738+
},
739+
mysql: {
740+
text : 'INSERT INTO `post` (`userId`) SELECT `user`.`id` FROM `user`',
741+
string: 'INSERT INTO `post` (`userId`) SELECT `user`.`id` FROM `user`'
742+
},
743+
mssql: {
744+
text : 'INSERT INTO [post] ([userId]) SELECT [user].[id] FROM [user]',
745+
string: 'INSERT INTO [post] ([userId]) SELECT [user].[id] FROM [user]'
746+
},
747+
oracle: {
748+
text : 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user"',
749+
string: 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user"'
750+
},
751+
params: []
752+
});
753+
754+
Harness.test({
755+
query: post.insert(post.userId).add(user.select(user.id).order(user.id)),
756+
pg: {
757+
text : 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user" ORDER BY "user"."id"',
758+
string: 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user" ORDER BY "user"."id"'
759+
},
760+
sqlite: {
761+
text : 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user" ORDER BY "user"."id"',
762+
string: 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user" ORDER BY "user"."id"'
763+
},
764+
mysql: {
765+
text : 'INSERT INTO `post` (`userId`) SELECT `user`.`id` FROM `user` ORDER BY `user`.`id`',
766+
string: 'INSERT INTO `post` (`userId`) SELECT `user`.`id` FROM `user` ORDER BY `user`.`id`'
767+
},
768+
mssql: {
769+
text : 'INSERT INTO [post] ([userId]) SELECT [user].[id] FROM [user] ORDER BY [user].[id]',
770+
string: 'INSERT INTO [post] ([userId]) SELECT [user].[id] FROM [user] ORDER BY [user].[id]'
771+
},
772+
oracle: {
773+
text : 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user" ORDER BY "user"."id"',
774+
string: 'INSERT INTO "post" ("userId") SELECT "user"."id" FROM "user" ORDER BY "user"."id"'
775+
},
776+
params: []
777+
});
778+

0 commit comments

Comments
 (0)