cuulee / node-database-connectors Goto Github PK

var connectionIdentifier = require('node-database-connectors');  
var objConnection = connectionIdentifier.identify(sampleConfig);
var query = objConnection.prepareQuery(jsonQuery);

var sampleConfig = {
  type: "database",
  engine: 'MyISAM',
  databaseType: 'mysql',
  database: 'database',
  host: "hostname",
  port: "port",
  user: "user",
  password: "password",
  cacheResponse: false
};

  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'
      }]
    }
  };

SELECT ``.`pk_tableID` as `pk`,``.`refNumber`
FROM `tbl_SampleMaster` as TM
WHERE (``.`pk_id` = '1')
ORDER BY `refNumber` ASC;

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'
    }]
  }
};

  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');

var jsonQuery = {
  table: "tbl_SampleMaster",
  insert: [{
    field: 'SM_code',
    fValue: 'D0001'
  }, {
    field: 'SM_fname',
    fValue: 'Digi'
  }, {
    field: 'SM_lname',
    fValue: 'Corp'
  }],
};

  INSERT INTO tbl_PersonMaster(`SM_code`,`SM_fname`,`SM_lname`)
  VALUES(`D001`,`Digi`,`Corp`);

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`))

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'
    }]
  }
};

  UPDATE tbl_PersonMaster SET ``.`SM_code`=`D001`,``.`PM_fname`=`Ashraf`,``.`PM_lname`=`Ansari`
  WHERE (``.`pk_id` = '1');

var jsonQuery = {
  table: "tbl_PersonMaster",
  alias: "PM",
  delete: [],
  filter: {
    AND: [{
      field: 'pk_id',
      operator: 'EQ',
      value: '1'
    }]
  }
};

  DELETE FROM tbl_PersonMaster WHERE(``.`pk_id` = '1');

  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',
    ]
  };

SELECT ``.`pk_tableID` as `pk`,count(``.`refNumber`)
FROM `tbl_SampleMaster` as TM
WHERE (``.`pk_id` = '1')
GROUP BY `refNumber`
ORDER BY `refNumber` ASC;

  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',
    ]
  };

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;