node-database-connectors v1.7.16
NODE-DATABASE-CONNECTORS
Author: Axiom
Created on: 3rd Dec 2015
Function
prepareQuery :
var connectionIdentifier = require('node-database-connectors'); var objConnection = connectionIdentifier.identify(sampleConfig); var query = objConnection.prepareQuery(jsonQuery);
sampleConfig : Configuration for database connection. (As given below)
var sampleConfig = { type: "database", engine: 'MyISAM', databaseType: 'mysql', database: 'database', host: "hostname", port: "port", user: "user", password: "password", cacheResponse: false };
jsonQuery : JSON structure of Select, Insert, Update, Delete for Generating query
Sample 1 (Select Query)
var jsonQuery = { table: "tbl_SampleMaster", alias: "SM", select: [{ field: 'pk_tableID', alias: 'pk' }, { field: 'refNumber' }], sortby: [{ field: 'refNumber' }], filter: { AND: [{ field: 'pk_id', operator: 'EQ', value: '1' }] } };
Output :
SELECT ``.`pk_tableID` as `pk`,``.`refNumber` FROM `tbl_SampleMaster` as TM WHERE (``.`pk_id` = '1') ORDER BY `refNumber` ASC;
Sample 2 (Select Query)
var jsonQuery = { join: { table: 'tbl_tableMaster', alias: 'A', joinwith: [{ table: 'tbl_OtherMaster', alias: 'B', joincondition: { table: 'A', field: 'TM_pk_id', operator: 'eq', value: { table: 'B', field: 'OT_fk_id' } } }] }, select: [{ table: 'A', field: 'pk_tableID', alias: 'pk' }, { table: 'B', field: 'refNumber' }], filter: { AND: [{ field: 'pk_id', operator: 'EQ', value: '1' }] } };
Output :
SELECT `A`.`pk_tableID` as `pk`,`B`.`refNumber` FROM `tbl_tableMaster` as A INNER JOIN `tbl_OtherMaster` as B ON `A`.`TM_pk_id` = `B`.`OT_fk_id` WHERE (``.`pk_id` = '1');
Sample 3 (Insert Query)
var jsonQuery = { table: "tbl_SampleMaster", insert: [{ field: 'SM_code', fValue: 'D0001' }, { field: 'SM_fname', fValue: 'Digi' }, { field: 'SM_lname', fValue: 'Corp' }], };
Output :
INSERT INTO tbl_PersonMaster(`SM_code`,`SM_fname`,`SM_lname`) VALUES(`D001`,`Digi`,`Corp`);
- Sample 3-1 (Insert Query)
Output :var jsonQuery = { table: "tbl_PersonMaster", insert:{ field:['PM_Code','PM_fname','PM_lname'], fValue:[['CorDig','Digi', 'Corp'],['SofMic','Micro', 'Soft']], } };
INSERT INTO tbl_PersonMaster(`PM_Code`,`PM_fname`,`PM_lname`) VALUES((`CorDig`,`Digi`,`Corp`),(`SofMic`,`Micro`,`Soft`))
Sample 4 (Update Query)
var jsonQuery = { table: "tbl_SampleMaster", update: [{ field: 'SM_code', fValue: 'D001' }, { field: 'SM_fname', fValue: 'Digi' }, { field: 'SM_lname', fValue: 'Corp' }], filter: { AND: [{ field: 'pk_id', operator: 'EQ', value: '1' }] } };
Output :
UPDATE tbl_PersonMaster SET ``.`SM_code`=`D001`,``.`PM_fname`=`Ashraf`,``.`PM_lname`=`Ansari` WHERE (``.`pk_id` = '1');
Sample 5 (Delete Query)
var jsonQuery = { table: "tbl_PersonMaster", alias: "PM", delete: [], filter: { AND: [{ field: 'pk_id', operator: 'EQ', value: '1' }] } };
Output :
DELETE FROM tbl_PersonMaster WHERE(``.`pk_id` = '1');
- jsonQuery : JSON structure of Select with aggregation
Sample 6 (Select Query)
var jsonQuery = { table: "tbl_SampleMaster", alias: "SM", select: [{ field: 'pk_tableID', alias: 'pk' }, { field: 'refNumber', aggregation:"count" }], sortby: [{ field: 'refNumber' }], filter: { AND: [{ field: 'pk_id', operator: 'EQ', value: '1' }] }, groupby:[ table: "SM", field: 'refNumber', ] };
Output :
SELECT ``.`pk_tableID` as `pk`,count(``.`refNumber`) FROM `tbl_SampleMaster` as TM WHERE (``.`pk_id` = '1') GROUP BY `refNumber` ORDER BY `refNumber` ASC;
- jsonQuery : JSON structure of Select with nested aggregation
Sample 7 (Select Query)
var jsonQuery = { table: "tbl_SampleMaster", alias: "SM", select: [{ field: 'pk_tableID', alias: 'pk' }, { field: 'refNumber', aggregation:"count" }, { field: 'applicationCount', aggregation:["count","distinct"] }], sortby: [{ field: 'refNumber' }], filter: { AND: [{ field: 'pk_id', operator: 'EQ', value: '1' }] }, groupby:[ table: "SM", field: 'refNumber', ] };
Output :
SELECT ``.`pk_tableID` as `pk`,count(``.`refNumber`),count(distinct(``.`applicationCount`)) FROM `tbl_SampleMaster` as TM WHERE (``.`pk_id` = '1') GROUP BY `refNumber` ORDER BY `refNumber` ASC;
4 months ago
11 months ago
9 months ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
2 years ago
2 years ago
2 years ago
2 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
5 years ago
5 years ago
5 years ago
6 years ago
6 years ago
6 years ago
6 years ago
6 years ago
6 years ago
6 years ago
6 years ago
7 years ago
7 years ago
7 years ago
7 years ago
7 years ago
7 years ago
8 years ago
8 years ago
8 years ago
8 years ago
8 years ago
8 years ago
8 years ago
8 years ago
8 years ago
8 years ago
8 years ago
8 years ago
8 years ago
8 years ago
9 years ago
9 years ago
9 years ago
9 years ago
9 years ago
9 years ago
10 years ago
10 years ago
10 years ago
10 years ago
10 years ago
10 years ago
10 years ago
10 years ago
10 years ago
10 years ago