Huge News!Announcing our $40M Series B led by Abstract Ventures.Learn More →
Socket
Sign inDemoInstall
Socket
Sign inDemoInstall

sqlquerybuilder

Package Overview
Dependencies
Maintainers
1
Versions
77
Alerts
File Explorer

Advanced tools

License
Socket logo

Install Socket

Detect and block malicious and high-risk dependencies

Install

sqlquerybuilder - npm Package Compare versions

Comparing version 0.0.32 to 0.0.35

test/unit/subQueriesTest.js

4

lib/ordering.js

@@ -13,3 +13,3 @@ module.exports = function ordering(self) {

if (orderByInput && orderByInput.indexOf('.') !== -1) {
if (orderByInput && orderByInput.indexOf('.') !== -1 && orderByInput.indexOf('(') !== 0) {
var parts = orderByInput.split('.');

@@ -29,3 +29,3 @@

}
} else if(!skipAlias) {
} else if(!skipAlias && orderByInput.indexOf('(') !== 0) {
orderByInput = self._sqlObject.fromAlias + "." + orderByInput;

@@ -32,0 +32,0 @@ }

2

lib/selects.js

@@ -115,3 +115,3 @@ module.exports = function selects(self) {

if (table != self._sqlObject.from) {
self.join(table);
self.leftJoin(table);
}

@@ -118,0 +118,0 @@ }

6

lib/where.js

@@ -89,3 +89,3 @@ module.exports = function where(self) {

whereString += containsAlias;
} else if (prop.indexOf(".") !== -1) {
} else if (prop.indexOf(".") !== -1 ) {
whereString += prop;

@@ -158,3 +158,3 @@ } else {

var greatAlias = self._selectProps[self._selectAliases.indexOf(filter.substr(0, dateComparer))];
whereString += (greatAlias ? greatAlias : (self._sqlObject.fromAlias + "." + filter.substr(0, dateComparer))) +
whereString += (greatAlias ? greatAlias : (filter.substr(0, dateComparer).charAt(0)==="(" ? filter.substr(0, dateComparer) :self._sqlObject.fromAlias + "." + filter.substr(0, dateComparer))) +
" >= '" +

@@ -169,3 +169,3 @@ filter.substr(dateComparer + 2, filter.length - dateComparer).replace(/-/g, "/") +

var lessAlias = self._selectProps[self._selectAliases.indexOf(filter.substr(0, dateComparer))];
whereString += (lessAlias ? lessAlias : (self._sqlObject.fromAlias + "." + filter.substr(0, dateComparer))) +
whereString += (lessAlias ? lessAlias : ( filter.substr(0, dateComparer).charAt(0)==="(" ? filter.substr(0, dateComparer) :self._sqlObject.fromAlias + "." + filter.substr(0, dateComparer))) +
" < '" +

@@ -172,0 +172,0 @@ filter.substr(dateComparer + 1, filter.length - dateComparer - 1).replace(/-/g, "/") +

2

package.json
{
"name": "sqlquerybuilder",
"version": "0.0.32",
"version": "0.0.35",
"description": "Highly opinionated Sql Server Query Writer, mostly for internal use.",

@@ -5,0 +5,0 @@ "main": "./lib/index",

16

test/unit/nestedSelectTest.js

@@ -61,3 +61,3 @@ /**

var test = sqlBuilder().from(valueObject.table).selectJoin(valueObject.queryObj).build();
test.should.equal("SELECT AccountingMonths.Id AS 'Id', MonthDisplayName AS 'Month', AccountingYears.YearValue AS 'Year', AccountingYears.YearTypeDisplayName AS 'YearType' FROM AccountingMonths JOIN AccountingYears ON AccountingMonths.AccountingYear_id = AccountingYears.Id ");
test.should.equal("SELECT AccountingMonths.Id AS 'Id', MonthDisplayName AS 'Month', AccountingYears.YearValue AS 'Year', AccountingYears.YearTypeDisplayName AS 'YearType' FROM AccountingMonths LEFT JOIN AccountingYears ON AccountingMonths.AccountingYear_id = AccountingYears.Id ");
done();

@@ -81,3 +81,3 @@ });

query.should.equal("SELECT RemitTos.VendorName AS 'RemitTo.VendorName', COUNT(RemitTos.VendorName) AS 'Count' FROM AccountsPayableInvoices JOIN RemitTos ON AccountsPayableInvoices.RemitTo_id = RemitTos.Id ");
query.should.equal("SELECT RemitTos.VendorName AS 'RemitTo.VendorName', COUNT(RemitTos.VendorName) AS 'Count' FROM AccountsPayableInvoices LEFT JOIN RemitTos ON AccountsPayableInvoices.RemitTo_id = RemitTos.Id ");
done();

@@ -94,3 +94,3 @@ });

query.should.equal("SELECT RemitTos.VendorName AS 'RemitTo.VendorName', COUNT(RemitTos.VendorName) AS 'Count' FROM AccountsPayableInvoices JOIN RemitTos ON AccountsPayableInvoices.RemitTo_id = RemitTos.Id GROUP BY RemitTos.VendorName ");
query.should.equal("SELECT RemitTos.VendorName AS 'RemitTo.VendorName', COUNT(RemitTos.VendorName) AS 'Count' FROM AccountsPayableInvoices LEFT JOIN RemitTos ON AccountsPayableInvoices.RemitTo_id = RemitTos.Id GROUP BY RemitTos.VendorName ");
done();

@@ -107,3 +107,3 @@ });

query.should.equal("SELECT RemitTos.VendorName AS 'RemitTo.VendorName', StatusDisplayName AS 'Status.DisplayName', COUNT(RemitTos.VendorName) AS 'Count' FROM AccountsPayableInvoices JOIN RemitTos ON AccountsPayableInvoices.RemitTo_id = RemitTos.Id ");
query.should.equal("SELECT RemitTos.VendorName AS 'RemitTo.VendorName', StatusDisplayName AS 'Status.DisplayName', COUNT(RemitTos.VendorName) AS 'Count' FROM AccountsPayableInvoices LEFT JOIN RemitTos ON AccountsPayableInvoices.RemitTo_id = RemitTos.Id ");
done();

@@ -120,3 +120,3 @@ });

query.should.equal("SELECT RemitTos.VendorName AS 'RemitTo.VendorName', StatusDisplayName AS 'Status.DisplayName', COUNT(RemitTos.VendorName) AS 'Count' FROM AccountsPayableInvoices JOIN RemitTos ON AccountsPayableInvoices.RemitTo_id = RemitTos.Id ");
query.should.equal("SELECT RemitTos.VendorName AS 'RemitTo.VendorName', StatusDisplayName AS 'Status.DisplayName', COUNT(RemitTos.VendorName) AS 'Count' FROM AccountsPayableInvoices LEFT JOIN RemitTos ON AccountsPayableInvoices.RemitTo_id = RemitTos.Id ");
done();

@@ -137,3 +137,3 @@ });

var query = sqlBuilder().from('RemitTos').selectJoin(qObj).where(wherobj).build();
query.should.equal("SELECT Id AS 'Id', Number AS 'Number', LongName AS 'LongName', VendorType.IsTaxAuthority AS 'VendorType.IsTaxAuthority' FROM RemitTos JOIN VendorType ON RemitTos.VendorType_id = VendorType.Id WHERE RemitTos.IsActive = 1 AND VendorType.IsTaxAuthority = 1 ");
query.should.equal("SELECT Id AS 'Id', Number AS 'Number', LongName AS 'LongName', VendorType.IsTaxAuthority AS 'VendorType.IsTaxAuthority' FROM RemitTos LEFT JOIN VendorType ON RemitTos.VendorType_id = VendorType.Id WHERE RemitTos.IsActive = 1 AND VendorType.IsTaxAuthority = 1 ");
done();

@@ -159,3 +159,3 @@ });

var query = sqlBuilder().from('FixedAssets').selectJoin(qObj).groupBy('OperationalCategory.Name').processListRequest(reqquery).build();
query.should.equal("WITH SelectedItems AS (SELECT EquipmentTypes.Name AS 'OperationalCategory.Name', COUNT(EquipmentTypes.Name) AS 'Count', ROW_NUMBER() OVER (ORDER BY EquipmentTypes.Name ASC) AS Position FROM FixedAssets JOIN EquipmentTypes ON FixedAssets.OperationalCategory_id = EquipmentTypes.Id GROUP BY EquipmentTypes.Name) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ");
query.should.equal("WITH SelectedItems AS (SELECT EquipmentTypes.Name AS 'OperationalCategory.Name', COUNT(EquipmentTypes.Name) AS 'Count', ROW_NUMBER() OVER (ORDER BY EquipmentTypes.Name ASC) AS Position FROM FixedAssets LEFT JOIN EquipmentTypes ON FixedAssets.OperationalCategory_id = EquipmentTypes.Id GROUP BY EquipmentTypes.Name) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ");
done();

@@ -182,3 +182,3 @@ });

var query = sqlBuilder().from('FixedAssets').selectJoin(qObj).join('GLCategory').groupBy('OperationalCategory.Name').processListRequest(reqquery).build();
query.should.equal("WITH SelectedItems AS (SELECT EquipmentTypes.Name AS 'OperationalCategory.Name', COUNT(EquipmentTypes.Name) AS 'Count', ROW_NUMBER() OVER (ORDER BY EquipmentTypes.Name ASC) AS Position FROM FixedAssets JOIN EquipmentTypes ON FixedAssets.OperationalCategory_id = EquipmentTypes.Id JOIN AccountingProcessTypes GLCategory ON FixedAssets.GLCategory_id = GLCategory.Id WHERE GLCategory.Name = 'Machinery and Equipment' GROUP BY EquipmentTypes.Name) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ");
query.should.equal("WITH SelectedItems AS (SELECT EquipmentTypes.Name AS 'OperationalCategory.Name', COUNT(EquipmentTypes.Name) AS 'Count', ROW_NUMBER() OVER (ORDER BY EquipmentTypes.Name ASC) AS Position FROM FixedAssets LEFT JOIN EquipmentTypes ON FixedAssets.OperationalCategory_id = EquipmentTypes.Id JOIN AccountingProcessTypes GLCategory ON FixedAssets.GLCategory_id = GLCategory.Id WHERE GLCategory.Name = 'Machinery and Equipment' GROUP BY EquipmentTypes.Name) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ");
done();

@@ -185,0 +185,0 @@ });

6

test/unit/processListRequestTest.js

@@ -6,3 +6,7 @@ /**

var sqlBuilder = require('../../lib/index').queryBuilder;
String.prototype.replaceAll=function(find,replace){
var me = this.toString();
me =me.replace(new RegExp(find, 'g'), replace);
return me;
};
describe("when calling processListRequest, valid sql should be produced when", function(){

@@ -9,0 +13,0 @@

78

test/unit/selectTest.js

@@ -81,3 +81,3 @@ /**

.build().should.equal("SELECT main.Id AS 'Id', secondary.Id AS 'secondary.Id', tertiary.Property AS 'Prop' FROM main " +
"JOIN secondary ON main.secondary_id = secondary.Id JOIN tertiary ON main.tertiary_id = tertiary.Id ");
"LEFT JOIN secondary ON main.secondary_id = secondary.Id JOIN tertiary ON main.tertiary_id = tertiary.Id ");
done();

@@ -91,79 +91,17 @@ });

.build().should.equal("SELECT tertiary.Property AS 'Prop', main.Id AS 'Id', secondary.Id AS 'secondary.Id' FROM main " +
"JOIN tertiary ON main.tertiary_id = tertiary.Id JOIN secondary ON main.secondary_id = secondary.Id ");
"JOIN tertiary ON main.tertiary_id = tertiary.Id LEFT JOIN secondary ON main.secondary_id = secondary.Id ");
done();
});
});
describe("when calling SELECT with SUBQUERIES, valid sql should be produced when", function() {
// STRING
it('called with ALIAS and SUBQUERY in STRING', function (done) {
//STRING MIXING VARIABLES
it('called with uncommon string', function (done) {
(sqlBuilder()
.select("p.Name")
.from("Products p")
.where("p.Id = (SELECT o.Product_id FROM OrderItems o WHERE o.Product_id = p.Id)")
.select("Id AS 'Key', (FirstName + ', ' + LastName) AS 'Value'")
.from("Users")
.build())
.should.equal("SELECT p.Name FROM Products p WHERE p.Id = (SELECT o.Product_id FROM OrderItems o WHERE o.Product_id = p.Id) ");
.should.equal("SELECT Id AS 'Key', (FirstName + ', ' + LastName) AS 'Value' FROM Users ");
done();
});
});
// sqlBuilder()
it('called with ALIAS as ARGUMENT and SUBQUERY from a separate sqlBuilder', function (done) {
(sqlBuilder()
.select("p.Name")
.from("Products", "p")
.where("p.Id = ("+ sqlBuilder().select("o.Product_id").from("OrderItems","o").where("o.Product_id = p.Id").build() +")")
.build())
.should.equal("SELECT p.Name FROM Products p WHERE p.Id = (SELECT o.Product_id FROM OrderItems o WHERE o.Product_id = p.Id ) ");
done();
});
// OBJECT STRING
it('called with ALIAS as ARGUMENT and selection as OBJECT', function (done) {
(sqlBuilder()
.select({"p.Name": "ProductName"})
.from("Products", "p")
.where("p.Id = (SELECT o.Product_id FROM OrderItems o WHERE o.Product_id = p.Id)")
.build())
.should.equal("SELECT p.Name AS 'ProductName' FROM Products p WHERE p.Id = (SELECT o.Product_id FROM OrderItems o WHERE o.Product_id = p.Id) ");
done();
});
// OBJECT sqlBuilder
it('called with WHERE IN using criteria as OBJECT with SUBQUERY', function (done) {
(sqlBuilder()
.select({"p.Name": "ProductName"})
.from("Products", "p")
.where({Id : sqlBuilder().select("o.Product_id").from("OrderItems","o").where("o.Product_id = p.Id").build()},"IN")
.build())
.should.equal("SELECT p.Name AS 'ProductName' FROM Products p WHERE p.Id IN (SELECT o.Product_id FROM OrderItems o WHERE o.Product_id = p.Id ) ");
done();
});
// AS
it('called with JOIN to SUBQUERY from a separate sqlBuilder', function (done) {
(sqlBuilder()
.select({"p.Name": "ProductName"})
.from("Items", "p")
.joinAs("(" + sqlBuilder().select("Id").from("Tickets").build() + ")", "t", "p.Ticket_Id", "t.Id")
.where("p.Id = (SELECT o.Product_id FROM OrderItems o WHERE o.Product_id = p.Id )")
.build())
.should.equal("SELECT p.Name AS 'ProductName' FROM Items p JOIN (SELECT Id FROM Tickets ) t ON p.Ticket_Id = t.Id WHERE p.Id = (SELECT o.Product_id FROM OrderItems o WHERE o.Product_id = p.Id ) ");
done();
});
// sqlBuilder w/ ALIAS in STRING
it('called with JOIN to SUBQUERY from a separate sqlBuilder and joinOn and To as a string', function (done) {
(sqlBuilder()
.select({"p.Name": "ProductName"})
.from("Products", "p")
.join("(" + sqlBuilder().select("Id").from("Tickets").build() + ") t ON p.Ticket_Id = t.Id")
.where("p.Id = (SELECT o.Product_id FROM OrderItems o WHERE o.Product_id = p.Id)")
.build())
.should.equal("SELECT p.Name AS 'ProductName' FROM Products p JOIN (SELECT Id FROM Tickets ) t ON p.Ticket_Id = t.Id WHERE p.Id = " +
"(SELECT o.Product_id FROM OrderItems o WHERE o.Product_id = p.Id) ");
done();
});
});

7

test/unit/whereTest.js

@@ -138,3 +138,8 @@ /**

var subQuery = sqlBuilder().select("pub_id").from("Titles").where("type = 'biography'").build();
(sqlBuilder().select("name").from("publishers").where({pub_id : subQuery},"IN").build())
(sqlBuilder()
.select("name")
.from("publishers")
.where({pub_id : subQuery},"IN")
.build())
.should.equal("SELECT name FROM publishers WHERE publishers.pub_id IN (SELECT pub_id FROM Titles WHERE type = 'biography' ) ");

@@ -141,0 +146,0 @@ done();

SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap
  • Changelog

About

Packages

npm

Go

Maven

PyPI

Rubygems

Stay in touch

Get open source security insights delivered straight into your inbox.

  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc