node-database-connectors

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)

  var jsonQuery = {
    table: "tbl_PersonMaster",
    insert:{
      field:['PM_Code','PM_fname','PM_lname'],
      fValue:[['CorDig','Digi', 'Corp'],['SofMic','Micro', 'Soft']],
    }
  };

  Output :

    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;