Postgres query test

create.js

var pg = require('pg');
var faker = require('faker');
var Promise = require('bluebird');
var uuid = require('node-uuid');

// create a config to configure both pooling behavior
// and client options
// note: all config is optional and the environment variables
// will be read if the config is not present
var config = {
  user: 'postgres', //env var: PGUSER
  database: 'iris', //env var: PGDATABASE
  password: '', //env var: PGPASSWORD
  port: 5432, //env var: PGPORT
  max: 10, // max number of clients in the pool
  idleTimeoutMillis: 30000, // how long a client is allowed to remain idle before being closed
};


//this initializes a connection pool
//it will keep idle connections open for a 30 seconds
//and set a limit of maximum 10 idle clients
var pool = new pg.Pool(config);

// to run a query we can acquire a client from the pool,
// run a query on the client, and then return the client to the pool
pool.connect(function(err, client, done) {
  if(err) {
    return console.error('error fetching client from pool', err);
  }

  setupTestData(pool);

  // pool
  //   .query('CREATE TABLE IF NOT EXISTS docs (id nvarchar(512)) ')
  //   .then( ()=> pool.query('INSERT INTO docs (id) VALUES (\'test\') ') )

  // client.query('SELECT $1::int AS number', ['1'], function(err, result) {
  //   //call `done()` to release the client back to the pool
  //   done();

  //   if(err) {
  //     return console.error('error running query', err);
  //   }
  //   console.log(result.rows[0].number);
  //   //output: 1
  //});
});

pool.on('error', function (err, client) {
  // if an error is encountered by a client while it sits idle in the pool
  // the pool itself will emit an error event with both the error and
  // the client which emitted the original error
  // this is a rare occurrence but can happen if there is a network partition
  // between your application and the database, the database restarts, etc.
  // and so you might want to handle it and at least log it out
  console.error('idle client error', err.message, err.stack)
})

// TEST database

function setupTestData(pool){

  var case_1 = createTestTable( pool, { name:'TEST_CASE', major:1 } );
  var product_1 = createTestTable( pool, { name:'TEST_PRODUCT', major:1 } );
  var product_2 = createTestTable( pool, { name:'TEST_PRODUCT', major:2 } );
  var company_1 = createTestTable( pool, { name:'TEST_COMPANY', major:1 } );

  return Promise.all( [case_1, product_1, product_2,company_1] ).spread( (case1,prod1,prod2,comp1)=>{

    return insertTestData( pool, comp1 ).then( (companyIds)=>{      
      return Promise.all([
        insertTestDataWithRefs( pool, prod1, companyIds),
        insertTestDataWithRefs( pool, prod2, companyIds)
      ]).spread((prod1ids,prod2ids)=>{
        const refids = prod1ids.concat(prod2ids);
        return insertTestDataWithRefs(pool, case1, refids);
      }).then( ()=>[case1,prod1,prod2,comp1] );
  })
    
  }).then( (tables)=>{ dropTables(pool, tables)} )
  .catch((error)=>{
    console.log('ERROR', error, error.stack);
  });
}

function createTestTable( pool, info ){
  var table = { name: info.name + '_' + info.major, refsTable: {name: info.name + '_' + info.major + '_' + 'data_refs'} };

  var op = pool.query(`CREATE TABLE IF NOT EXISTS ${table.name}
              (
                id        varchar(128) NOT NULL,
                name      varchar(512),
                number    double precision,
                text      text,
                data_ref  varchar(128),
                data_refs  varchar(128)[],
                PRIMARY KEY (id)
                )`);

   return op.then(()=>{
    createRefsTable(pool, table.refsTable);
   }).then( ()=>table );
}


function createRefsTable( pool, refTableInfo ){
  return pool.query(`CREATE TABLE IF NOT EXISTS ${refTableInfo.name}
              (
                ownerId       varchar(128) NOT NULL,
                index         smallint,
                referencedId  varchar(128) NOT NULL,
                PRIMARY KEY (ownerId,index)
                )`);
}

function dropTables(pool,tables){
  return;
  console.log('dropping', tables)
  return Promise.each( tables, (table)=>Promise.all([pool.query(`DROP TABLE ${table.name}`),pool.query(`DROP TABLE ${table.refsTable.name}`)] ) );
}


function insertTestData(pool,table){
  var number = 100;
  var ids = [];

  return Promise.each( new Array(number) , ()=>{
    const id = uuid.v1();
    ids.push(id);

    const q = {
      text: `INSERT INTO ${table.name}
              (id,name,number,text)
            VALUES ($1,$2,$3,$4)`,
      values:[id,faker.name.lastName(), faker.random.number(),faker.lorem.paragraph()]
    };

    // console.log(q)
    return pool.query(q);
  }).then( ()=>ids );
}


function insertTestDataWithRefs(pool,table, targetIds){

  console.log('insertTestDataWithRefs', targetIds);
  var number = 100;
  var ids = [];

  return Promise.each( new Array(number) , ()=>{

    const id = uuid.v1();
    ids.push(id);

    const randomRef = randomOf(targetIds);
    const randomRefs = someRandomOf(targetIds);

    const rowInsert = {
      text: `INSERT INTO ${table.name}
              (id,name,number,text, data_ref, data_refs)
            VALUES ($1,$2,$3,$4,$5,$6)`,
      values:[id,faker.name.lastName(), faker.random.number(),faker.lorem.paragraph(),randomRef,randomRefs]
    };

    const refsInserts = createRefsInserts( table.refsTable, id, randomRefs );

    const inserts = Promise.all([rowInsert,...refsInserts]);

    // console.log(q)
    return Promise.each( inserts, (i)=>pool.query(i) );
  }).then( ()=>ids);
}


function someRandomOf(targetIds, count=5){
  var refIds = new Array(count);
  for (var i = 0; i < refIds.length; i++) {
    refIds[i] = randomOf(targetIds);
  }
  return refIds;
}

function randomOf( list ){
  var i = Math.floor( Math.random()*list.length );
  //console.log(i)
  return list[ i ];
}

function createRefsInserts( refTableInfo, ownerId, referencedIds){

  var inserts = [];

  for (var i = 0; i < referencedIds.length; i++) {
    var refId = referencedIds[i];

    const rowInsert = {
      text: `INSERT INTO ${refTableInfo.name}
              (ownerId,index,referencedId)
            VALUES ($1,$2,$3)`,
      values:[ownerId, i, refId]
    };

    inserts.push(rowInsert);
  }
   
  return inserts;
}

query.sql

--SELECT * from public."Document";

--INSERT INTO "Document" VALUES ('case1',1,'{ "text":"test","number":1, "ref":{"id":"customer1"} } '); 
--INSERT INTO "Document" VALUES ('customer1', 1,'{ "name":"customer1","age":20 } ') ;

--CREATE INDEX customer_age ON "Document" ((data->'age'));

--set enable_seqscan=false;

/*

SELECT * FROM "Document"
	WHERE data->'age' IN ('10','20');

--CREATE INDEX ON "Document" USING GIN (data);
*/
/*select * from "Document" as doc,
 LATERAL jsonb_to_record(doc.data) AS data(age integer)
 WHERE doc.data ? 'age' AND data.age > 10;
 */
 --CREATE DATABASE
/*

SELECT * FROM "Case_2";


SELECT * FROM "Case_2" WHERE  ("data.customers")[1].id = 'customer1';


SELECT * FROM ( SELECT *, generate_subscripts("data.customers",1) as s FROM "Case_2") as main WHERE  ("data.customers")[s].id = 'customer2';

*/
WITH 	TEST_CASE_all as (
		SELECT * from TEST_CASE_1
	),
	data_ref AS (
		SELECT id, number, 'p1' FROM TEST_PRODUCT_1
		WHERE TEST_PRODUCT_1.number < 10000
		UNION ALL
		SELECT id, number, 'p2' FROM TEST_PRODUCT_2
		WHERE TEST_PRODUCT_2.number < 10000
	)
SELECT TEST_CASE_all.id, TEST_CASE_all.name, data_ref.number from TEST_CASE_all
	INNER JOIN data_ref ON (data_ref.id = TEST_CASE_all.data_ref)
	WHERE TEST_CASE_all.number > 1000
	ORDER BY TEST_CASE_all.name;

-- with company too

WITH 	TEST_CASE_all as (
		SELECT * from TEST_CASE_1
		WHERE TEST_CASE_1.number > 1000
	),
	data_ref AS (
		WITH TEST_COMPANY_all AS (
			SELECT id, TEST_COMPANY_1.number as coNumber FROM TEST_COMPANY_1
			WHERE TEST_COMPANY_1.number > 10000
		)
		SELECT TEST_PRODUCT_1.id, number, 'p1',TEST_COMPANY_all.coNumber FROM TEST_PRODUCT_1
		INNER JOIN TEST_COMPANY_all ON (TEST_PRODUCT_1.data_ref = TEST_COMPANY_all.id)
		WHERE TEST_PRODUCT_1.number < 10000 
		UNION ALL
		SELECT TEST_PRODUCT_2.id, number, 'p2', TEST_COMPANY_all.coNumber FROM TEST_PRODUCT_2
		INNER JOIN TEST_COMPANY_all ON (TEST_PRODUCT_2.data_ref = TEST_COMPANY_all.id)
		WHERE TEST_PRODUCT_2.number < 10000
	)
SELECT TEST_CASE_all.id, TEST_CASE_all.name, data_ref.number, coNumber from TEST_CASE_all
	INNER JOIN data_ref ON (TEST_CASE_all.data_ref = data_ref.id)
	ORDER BY TEST_CASE_all.name;


-- ALT WITHOUT WITH

	
SELECT TEST_CASE_1.id, TEST_CASE_1.name FROM TEST_CASE_1	
	WHERE TEST_CASE_1.data_ref IN
	(
		SELECT TEST_PRODUCT_1.id FROM TEST_PRODUCT_1
		WHERE TEST_PRODUCT_1.data_ref IN (SELECT id FROM TEST_COMPANY_1 WHERE TEST_COMPANY_1.number > 10000)
		AND TEST_PRODUCT_1.number < 10000 
		UNION ALL
		SELECT TEST_PRODUCT_2.id FROM TEST_PRODUCT_2
		WHERE TEST_PRODUCT_2.data_ref IN (SELECT id FROM TEST_COMPANY_1 WHERE TEST_COMPANY_1.number > 10000)
		AND TEST_PRODUCT_2.number < 10000 
	)
	AND TEST_CASE_1.number > 1000
	ORDER BY TEST_CASE_all.name;

-- WITH REFS (PLURAL)

WITH 	TEST_CASE_all as (
		SELECT * from TEST_CASE_1
	),
	data_refs AS (
		SELECT id, number, 'p1' FROM TEST_PRODUCT_1
		WHERE TEST_PRODUCT_1.number < 10000
		UNION ALL
		SELECT id, number, 'p2' FROM TEST_PRODUCT_2
		WHERE TEST_PRODUCT_2.number < 10000
	)
SELECT TEST_CASE_all.id, TEST_CASE_all.name, data_refs.number from TEST_CASE_all
	INNER JOIN data_refs ON (data_refs.id = ANY(TEST_CASE_all.data_refs))
	WHERE TEST_CASE_all.number > 1000
	ORDER BY TEST_CASE_all.name;

-- WITH REFS (PLURAL) AND COMPANY


WITH 	TEST_CASE_all as (
		SELECT * from TEST_CASE_1
		WHERE TEST_CASE_1.number > 1000
	),
	data_refs AS (
		WITH TEST_COMPANY_all AS (
			SELECT id, TEST_COMPANY_1.number as coNumber FROM TEST_COMPANY_1
			WHERE TEST_COMPANY_1.number > 10000
		)
		SELECT TEST_PRODUCT_1.id, number, 'p1',TEST_COMPANY_all.coNumber FROM TEST_PRODUCT_1
		INNER JOIN TEST_COMPANY_all ON (TEST_COMPANY_all.id = TEST_PRODUCT_1.data_ref)
		WHERE TEST_PRODUCT_1.number < 10000 
		UNION ALL
		SELECT TEST_PRODUCT_2.id, number, 'p2', TEST_COMPANY_all.coNumber FROM TEST_PRODUCT_2
		INNER JOIN TEST_COMPANY_all ON (TEST_COMPANY_all.id = TEST_PRODUCT_2.data_ref)
		WHERE TEST_PRODUCT_2.number < 10000
	)
SELECT TEST_CASE_all.id, TEST_CASE_all.name, data_refs.number, coNumber from TEST_CASE_all
	INNER JOIN data_refs ON (data_refs.id = ANY(TEST_CASE_all.data_refs))
	ORDER BY TEST_CASE_all.name;


--- WITH REFS (PLURAL) USING NORMALIZED REFS_TABLE


WITH 	TEST_CASE_all as (
		SELECT * from TEST_CASE_1
	),
	data_refs as (
		SELECT ownerId, referencedId FROM TEST_CASE_1_data_refs
	),
	PRODUCTS_all AS (
		SELECT id, number, 'p1' FROM TEST_PRODUCT_1
		WHERE TEST_PRODUCT_1.number < 10000
		UNION ALL
		SELECT id, number, 'p2' FROM TEST_PRODUCT_2
		WHERE TEST_PRODUCT_2.number < 10000
	)
SELECT TEST_CASE_all.id, TEST_CASE_all.name, PRODUCTS_all.number from TEST_CASE_all
	INNER JOIN data_refs ON (data_refs.ownerId = TEST_CASE_all.id)
	INNER JOIN PRODUCTS_all ON (PRODUCTS_all.id = data_refs.referencedId)
	WHERE TEST_CASE_all.number > 1000
	ORDER BY TEST_CASE_all.name;


--- WITH REFS (PLURAL) AND COMPANY USING NORMALIZED REFS_TABLE


WITH 	TEST_CASE_all as (
		SELECT * from TEST_CASE_1
	),
	data_refs as (
		SELECT ownerId, referencedId FROM TEST_CASE_1_data_refs
	),
	PRODUCTS_all AS (
		WITH TEST_COMPANY_all AS (
			SELECT id, TEST_COMPANY_1.number as coNumber FROM TEST_COMPANY_1
			WHERE TEST_COMPANY_1.number > 10000
		)
		SELECT TEST_PRODUCT_1.id, number, 'p1',TEST_COMPANY_all.coNumber FROM TEST_PRODUCT_1
		INNER JOIN TEST_COMPANY_all ON (TEST_PRODUCT_1.data_ref = TEST_COMPANY_all.id)
		WHERE TEST_PRODUCT_1.number < 10000 
		UNION ALL
		SELECT TEST_PRODUCT_2.id, number, 'p2', TEST_COMPANY_all.coNumber FROM TEST_PRODUCT_2
		INNER JOIN TEST_COMPANY_all ON (TEST_PRODUCT_2.data_ref = TEST_COMPANY_all.id)
		WHERE TEST_PRODUCT_2.number < 10000
	)
SELECT TEST_CASE_all.id, TEST_CASE_all.name, PRODUCTS_all.number from TEST_CASE_all
	INNER JOIN data_refs ON (data_refs.ownerId = TEST_CASE_all.id)
	INNER JOIN PRODUCTS_all ON (PRODUCTS_all.id = data_refs.referencedId)
	WHERE TEST_CASE_all.number > 1000
	ORDER BY TEST_CASE_all.name;