setupNewDatabase.js

var mysql = require('promise-mysql')
var mysqlConnectionOptions = require('../common/settings.js').mysqlConnectionOptions

var optionsBeforeDBCreation = JSON.parse(JSON.stringify(mysqlConnectionOptions)) // full copy
delete optionsBeforeDBCreation['database'] // delete database field

console.log('opt\n', optionsBeforeDBCreation)

var pool = mysql.createPool(optionsBeforeDBCreation)

function createDatabase () {
  pool.query('create database etheroscope')
    .then(function (results) {
      console.log('Database creation success! results:\n ')
      pool = mysql.createPool(mysqlConnectionOptions)
    })
    .catch(function (err) {
      console.log('Database creation error:\n', err)
      pool = mysql.createPool(mysqlConnectionOptions)
    })
}

var sqlContracts = 'create table if not exists contracts(\n' +
  '    contractHash VARCHAR(40)  not null,\n' +
  '    name         VARCHAR(128),\n' +
  '    abi          NVARCHAR(11844),\n' +
  '    primary key (contractHash)\n' +
  ');\n'
var sqlContractLookupHistory = 'create table if not exists contractLookupHistory(\n' +
  '    contractHash VARCHAR(40)  not null,\n' +
  '    date         datetime,\n' +
  '    primary key (contractHash, date)\n' +
  ');\n'
var sqlBlocks = 'create table if not exists blocks(\n' +
  '    blockNumber BIGINT   not null,\n' +
  '    timeStamp   BIGINT   not null,\n' +
  '    primary key (blockNumber)\n' +
  ');\n'
var sqlVariables = 'create table if not exists variables(\n' +
  '    contractHash VARCHAR(40) not null,\n' +
  '    variableName VARCHAR(50) not null,\n' +
  '    cachedFrom   BIGINT,\n' +
  '    cachedUpTo   BIGINT,\n' +
  '    unitID       BIGINT,\n' +
  '    primary key (contractHash, variableName)\n' +
  ');\n'
var sqlVariableUnits = 'create table if not exists variableUnits(\n' +
  '    id          BIGINT not null,\n' +
  '    variable    VARCHAR(50) not null,\n' +
  '    unit        VARCHAR(50) not null,\n' +
  '    description NVARCHAR(11844),\n' +
  '    primary key (id)\n' +
  ');\n'
var sqlDataPoints = 'create table if not exists dataPoints(\n' +
  '    contractHash VARCHAR(40) not null,\n' +
  '    variableName VARCHAR(50) not null,\n' +
  '    blockNumber  BIGINT      not null,\n' +
  '    value        VARCHAR(78) not null,\n' +
  '    primary key (contractHash, variableName, blockNumber),\n' +
  '    foreign key (contractHash) references contracts(contractHash),\n' +
  '    foreign key (blockNumber) references  blocks(blockNumber),\n' +
  '    foreign key (contractHash, variableName) references  variables(contractHash, variableName)\n' +
  ');\n'

function createTables () {
  pool.query(sqlContractLookupHistory).then(function (results) {
    console.log('create table (' + sqlContractLookupHistory + '): ', results)
  }).then(() => {
    pool.query(sqlContracts).then(function (results) {
      console.log('create(' + sqlContracts + '): ', results)
    })
  }).then(() => {
    pool.query(sqlBlocks).then(function (results) {
      console.log('create(' + sqlBlocks + '): ', results)
    })
  }).then(() => {
    pool.query(sqlVariables).then(function (results) {
      console.log('create(' + sqlVariables + '): ', results)
    })
  }).then(() => {
    pool.query(sqlVariableUnits).then(function (results) {
      console.log('create(' + sqlVariableUnits + '): ', results)
    })
  }).then(() => {
    pool.query(sqlDataPoints).then(function (results) {
      console.log('create(' + sqlDataPoints + '): ', results)
    })
  })

}


setTimeout(createDatabase, 500)
setTimeout(createTables, 3500)
setTimeout(createTables, 5500) // datapoints table doesnt create first time