Go to Qiita Advent Calendar 2024 Top
LoginSignup
6
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

SRAAdvent Calendar 2019

Day 24
@atmitani(at mitani)

PostgreSQLの統計情報を可視化（単体テスト編）

Posted at

#はじめに
折角「環境構築編」でテスト環境をインストールしていますので、テストコードを書いてみます。
なお、テスト対象コードは「バックエンド編」と「フロントエンド編」で作成したものを使います。

バックエンドの単体テスト

"test/routers"の下にroutersモジュールのテストコードを記述し、APIでアクセスが可能か確認します。
statistics.js用のテストコードを記述します。

###テストコード

test/routers/statistics.spec.js
const expect = require('expect')
const express = require('express')
const app = require('../index.js')
var req = require('supertest').agent(app)

describe('getDbSize', () => {
  it('GET getDbSize', (done) => {
    req.get('/statistics/getDbSize')
      .query({})
      .expect(200, function(err, res) {
        expect(res.body.value.length).toBeGreaterThan(0)
        done()
      })
  })
})

describe('getSqlCalls', () => {
  it('GET getSqlCalls ok', (done) => {
    req.get('/statistics/getSqlCalls')
      .expect(200, function(err, res) {
        expect(res.body.value.length).toBeGreaterThan(0)
        done()
      })
  })
})

describe('getSlowQuery', () => {
  it('GET getSlowQuery ok', (done) => {
    req.get('/statistics/getSlowQuery')
      .query({'threshold': 1000, 'limit': 2})
      .expect(200, function(err, res) {
        expect(res.body.value.length).toBeGreaterThan(0)
        done()
      })
  })
  it('GET getSlowQuery no param', (done) => {
    req.get('/statistics/getSlowQuery')
      .query({})
      .expect(400, function(err, res) {
        expect(res.body.message).toBe('bad request')
        done()
      })
  })
  it('GET getSlowQuery bad param', (done) => {
    req.get('/statistics/getSlowQuery')
      .query({'threshold': 'abc', 'limit': 20})
      .expect(400, function(err, res) {
        expect(res.body.message).toBe('bad request')
        done()
      })
  })
})

###テストランチャー
Express用のテストランチャーを記述します。

test/index.js
const express = require('express')
const consola = require('consola')
const { Nuxt, Builder } = require('nuxt')
const compression = require('compression')
const cors = require('cors')

// Import and Set Nuxt.js options
const config = require('../nuxt.config.js')
config.dev = process.env.NODE_ENV !== 'production'
// Init Nuxt.js
const nuxt = new Nuxt(config)

const app = express()
app.use(compression({
  threshold: 0,
  level: 9,
  memLevel: 9
}))
app.use(express.json())
app.use(express.urlencoded({ extended: false }))
app.use(cors())
const { host, port } = nuxt.options.backend
var http = require('http')
app.set('port', port)
var server = http.createServer(app)
server.listen(port, host)

// set router
const statisticsRouter = require('../server/routers/statistics')
app.use('/statistics', statisticsRouter)

module.exports = app

###テスト実施
テストを実行してみましょう。

> yarn run nyc

nyc_result.JPG
データベースアクセス失敗時の分岐以外は、無事テストが通ったようです。

##フロントエンドの単体テスト
次にフロントエンド側のstoreの単体テストを行ってみたいと思います。
###テストコード

test/store/statistics.spec.js
import Vuex from 'vuex'
import cloneDeep from 'lodash/cloneDeep'
import { mount, createLocalVue } from '@vue/test-utils'
import * as api from '~/store/api'
import * as statistics from '~/store/statistics'

const localVue = createLocalVue()
localVue.use(Vuex)

var action
var store = new Vuex.Store({
  state: () => ({}),
  actions: {},
  // store使用モジュールの宣言
  modules: {
    statistics: { 
      namespaced: true,
      state: cloneDeep(statistics.state),
      getters: cloneDeep(statistics.getters),
      actions: cloneDeep(statistics.actions),
      mutations: cloneDeep(statistics.mutations)
    }
  }
})

jest.mock('~/store/api', () => ({
  getApi: jest.fn(),
  postApi: jest.fn()
}))

describe('store/statistics.js', () => {
  beforeEach(() => {
  })

  /*
   * actionのテスト
   */
  describe('actions', () => {
    var params
    var testAction
    beforeEach(() => {
      params = {callback: null}
      testAction = (params = {}, data = {}) => {
        return store.dispatch.bind({api: api, process: process} )(action, data)
      }
    })

    // doGetDbSize Test
    describe('doGetDbSize', () => {
      // 正常系応答
      const okResult = {
        status: 200,
        data: {
          value: [
            {'host': 'localhost', 'db': 'db1', 'result': [
              { pg_database_size: '1576649583' }
            ]},
            {'host': 'localhost', 'db': 'db2', 'result': [
              { pg_database_size: '1106649967' }
            ]},
            {'host': 'localhost', 'db': 'db', 'result': [
              { pg_database_size: '1889903471' }
            ]},
          ]
        } 
      }
      const ngResult = {
        status: 400,
        data: {message: 'bad request'}
      }

      test('test doGetDbSize ok', async ()=> {
        // 正常系テスト
        api.getApi.mockImplementationOnce( () => okResult)
        action = 'statistics/doGetDbSize'
        await testAction(params, {})
        store.hotUpdate(store.state)
        const dbSize = store.getters['statistics/getDbSize']
        expect(dbSize).toEqual(okResult.data.value)
      })

      test('test doGetDbSize NG', async ()=> {
        // 異常系テスト
        api.getApi.mockImplementationOnce( () => ngResult)
        action = 'statistics/doGetDbSize'
        await testAction(params, {})
        store.hotUpdate(store.state)
        const dbSize = store.getters['statistics/getDbSize']
        expect(dbSize).toEqual([])
      })
    })

    // doGetSqlCall Test
    describe('doGetSqlCall', () => {
      // 正常系応答
      const okResult = {
        status: 200,
        data: {
          date: '1575870652420',
          value: [
            {'host': 'localhost', 'db': 'db1', 'result': [
              {'sql': 'select', 'call': 100},
              {'sql': 'insert', 'call': 90},
              {'sql': 'update', 'call': 80},
              {'sql': 'delete', 'call': 70}
            ]},
            {'host': 'localhost', 'db': 'db2', 'result': [
              {'sql': 'select', 'call': 120},
              {'sql': 'insert', 'call': 110},
              {'sql': 'update', 'call': 100},
              {'sql': 'delete', 'call': 90}
            ]},
            {'host': 'localhost', 'db': 'db3', 'result': [
              {'sql': 'select', 'call': 50},
              {'sql': 'insert', 'call': 40},
              {'sql': 'update', 'call': 30},
              {'sql': 'delete', 'call': 20}
            ]}
          ]
        }
      }
      const ngResult = {
        status: 400,
        data: {message: 'bad request'}
      }

      test('test doGetSqlCall ok', async ()=> {
        // 正常系テスト
        api.getApi.mockImplementationOnce( () => okResult)
        action = 'statistics/doGetSqlCall'
        await testAction(params, {})
        store.hotUpdate(store.state)
        const sqlCall = store.getters['statistics/getSqlCall']
        expect(sqlCall).toEqual(okResult.data)
      })

      test('test doGetSlowQuery NG', async ()=> {
        // 異常系テスト
        api.getApi.mockImplementationOnce( () => ngResult)
        action = 'statistics/doGetSqlCall'
        await testAction(params, {})
        store.hotUpdate(store.state)
        const sqlCall = store.getters['statistics/getSqlCall']
        expect(sqlCall).toBeNull()
      })
    })

    // doGetSlowQuery Test
    describe('doGetSlowQuery', () => {
      // 正常系応答
      const okResult = {
        status: 200,
        data: {
          value: [
            {'host': 'localhost', 'db': 'db1', 'result': [
              {
                query: 'copy pgbench_accounts from stdin',
                calls: '1',
                mean_time: 56801.7614,
                min_time: 56801.7614,
                max_time: 56801.7614
              },
              {
                query: 'copy pgbench_accounts from stdin',
                calls: '1',
                mean_time: 49246.7166,
                min_time: 49246.7166,
                max_time: 49246.7166
              }
            ]},
            {'host': 'localhost', 'db': 'db2', 'result': [
              {
                query: 'copy pgbench_accounts from stdin',
                calls: '1',
                mean_time: 56801.7614,
                min_time: 56801.7614,
                max_time: 56801.7614
              },
              {
                query: 'copy pgbench_accounts from stdin',
                calls: '1',
                mean_time: 49246.7166,
                min_time: 49246.7166,
                max_time: 49246.7166
              }
            ]},
            {'host': 'localhost', 'db': 'db3', 'result': [
              {
                query: 'copy pgbench_accounts from stdin',
                calls: '1',
                mean_time: 56801.7614,
                min_time: 56801.7614,
                max_time: 56801.7614
              },
              {
                query: 'copy pgbench_accounts from stdin',
                calls: '1',
                mean_time: 49246.7166,
                min_time: 49246.7166,
                max_time: 49246.7166
              }
            ]}
          ]
        }
      }
      const ngResult = {
        status: 400,
        data: {message: 'bad request'}
      }

      test('test doGetSlowQuery ok', async ()=> {
        // 正常系テスト
        api.getApi.mockImplementationOnce( () => okResult)
        action = 'statistics/doGetSlowQuery'
        await testAction(params, {threshold: 100, limit: 20})
        store.hotUpdate(store.state)
        const slowQuery = store.getters['statistics/getSlowQuery']
        expect(slowQuery).toEqual(okResult.data.value)
      })

      test('test doGetSlowQuery ok (no param)', async ()=> {
        // 正常系テスト
        api.getApi.mockImplementationOnce( () => okResult)
        action = 'statistics/doGetSlowQuery'
        await testAction(params, {})
        store.hotUpdate(store.state)
        const slowQuery = store.getters['statistics/getSlowQuery']
        expect(slowQuery).toEqual(okResult.data.value)
      })

      test('test doGetSlowQuery NG', async ()=> {
        // 異常系テスト
        api.getApi.mockImplementationOnce( () => ngResult)
        action = 'statistics/doGetSlowQuery'
        await testAction(params, {threshold: 100, limit: 20})
        store.hotUpdate(store.state)
        const slowQuery = store.getters['statistics/getSlowQuery']
        expect(slowQuery).toEqual([])
      })
    })
  })
})

####テスト実施
テストを実行してみましょう。

> yarn test

jest_result.JPG

store/statistics.jsのテストも無事通りました。

##まとめ
テストコードのデバッグに本体のコーディングよりも時間が掛かっていた時期もありましたが（遠い目）、テストパターンが溜まって来ると少しは効率良くテストできるようになると思います。

####参考
An Async Example(https://jestjs.io/docs/ja/tutorial-async)
facebook/jest(https://github.com/facebook/jest/tree/master/docs)
Facebook製のJavaScriptテストツール「Jest」の逆引き使用例(https://qiita.com/chimame/items/e97883fd46b67529d59f)
mochaとsupertestを使ってexpressとHeadless Chromeを使ったアプリのテストを書く(https://uyamazak.hatenablog.com/entry/2018/08/27/112600)
Mocha - the fun, simple, flexible JavaScript test framework(https://mochajs.org/)
SuperTest(https://www.npmjs.com/package/supertest)
Jestを使ってExpressのルーティングテストを書く(https://qiita.com/ckoshien/items/9afc60546ba1c9ce04f4)

6
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
6
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?