tatsuyasusukida/!README-hana-forms-public.md

## !README-hana-forms-public.md

      
    Raw
  

              !README-hana-forms-public.md
            
          
    🌻 Node.js tutorial to make Google Forms within an hour | Hana Forms Public (a subsystem for users to enter their answers) [demo video available]


About this article

This article describes how to make a online form creator like Google Forms in Node.js.
The online form creator implemented in this article consists of the following two subsystems.

Hana Forms Public: a subsystem for users to enter their answers
Hana Forms Admin: a subsystem for users to manage forms

This article covers Hana Forms Public. See Hana Forms Admin for the other subsystem. The resources related to this artice are as follows:

Demo)
Demo video
Source code
日本語版 / Japanese version

Workflow

The workflow is as follows:

Preparing for the database
Preparing for coding
Coding
Operation check

Preparing for the database

Run the following command in the terminal to prepare for the database.
create database hana_forms charset utf8;
create user hana_forms@localhost identified by 'password';
grant all privileges on hana_forms.* to hana_forms@localhost;
Preparing for coding

Run the following command in the terminal to prepare for coding.
mkdir HanaForms
cd HanaForms
npm init -y
npm install --save dotenv ejs express morgan mysql2 sequelize
touch .env api-initialize.js api-validate.js api-submit.js fixture.js main.js model.js static-js-input.js validate.js view-finish.ejs view-home.ejs view-input.ejs
Coding

.env

Open .env in the editor and enter the following content.
Click to go to .env.example
main.js

Open main.js in the editor and enter the following content.
Click to go to main.js
view-home.ejs

Open view-home.ejs in the editor and enter the following content.
Click to go to view-home.ejs
view-input.ejs

Open view-input.ejs in the editor and enter the following content.
Click to go to view-input.ejs
view-finish.ejs

Open view-finish.ejs in the editor and enter the following content.
Click to go to view-finish.ejs
model.js

Open model.js in the editor and enter the following content.
Click to go to model.js
fixture.js

Open fixture.js in the editor and enter the following content.
Click to go to fixture.js
static-js-input.js

Open static-js-input.js in the editor and enter the following content.
Click to go to static-js-input.js
validate.js

Open validate.js in the editor and enter the following content.
Click to go to validate.js
api-initialize.js

Open api-initialize.js in the editor and enter the following content.
Click to go to api-initialize.js
api-validate.js

Open api-validate.js in the editor and enter the following content.
Click to go to api-validate.js
api-submit.js

Open api-submit.js in the editor and enter the following content.
Click to go to api-submit.js
Operation check

Run the following command in the terminal to create tables and insert records.
node -r dotenv/config fixture.js
Run the following command in the terminal to start the server.
node -r dotenv/config main.js
Access http://localhost:3000/form/1234abcd/ in the browser.
Fill out the form and click the submit button.
Check that the finish page is displayed.
Conclusion

We have implemented a subsystem like the Google Forms answer page that dynamically generates a form based on a database record. However, the implemented system has been simplified for the tutorial. In order to get closer to Google Forms, it is necessary to provide the following functions.

Display of images and videos
File upload
Sending reception email
Theme setting

In addition to adding features, you need to address the following:

Improving user experience
Improving security
Improving operability
Improving accessibility

One example of improving user experience is the real-time display of validation error messages. For that, it is necessary to perform various processing with JavaScript on the front end, and in that case, module bundler such as webpack may be useful.
One example of improving security is setting the Content Security Policy. You can use Helmet middleware to automatically add security HTTP headers to your Express router.
One example of improving operability is log collection. Winston is a well-known node collection library for Node.js.
One example of improving accessibility is setting the aria attribute. W3C's WAI-ARIA Overview page is very helpful for accessibility information, including the aria attribute.
Read the Hana Forms Admin article to learn how to create a subsystem for users to manage their forms. If you have any opinions or impressions, please feel free to comment. Thank you for reading!
Related articles


How to insert test data with Sequelize
How to make foreign keys non-null with Sequelize
How to validate user input in Node.js
How to collect logs using winston in Node.js


## .env.example
PORT=3000
DB_URL=mysql://hana_forms_user:hana_forms_pass@localhost:3306/hana_forms_db
DB_IS_SSL=0
DB_IS_VITESS=0
ADMIN_URL=http://localhost:3001/
FIXTURE_OWNER=google-oauth2|000000000000000000000
FIXTURE_SKIP=0

## .gitignore
/node_modules/
/.env
/package-lock.json
# Do not ignore package-lock.json other than gist

## api-initialize.js
const {Op} = require('sequelize')
const model = require('./model')

exports.apiInitialize = async (req, res, next) => {
  try {
    const formItems = await model.formItem.findAll({
      where: {
        formId: {[Op.eq]: req.locals.form.id},
      },
      order: [['sort', 'asc']],
      include: [{model: model.formItemType, as: 'formItemType'}],
    })

    const {title} = req.locals.form
    const items = []

    for (const formItem of formItems) {
      const options = []
      const {code: type} = formItem.formItemType

      if (type === 'radio' || type === 'checkbox') {
        const formItemOptions = await model.formItemOption.findAll({
          where: {
            formItemId: {[Op.eq]: formItem.id},
          },
          order: [['sort', 'asc']],
        })

        for (const {label} of formItemOptions) {
          options.push({label, isSelected: false})
        }
      }

      const {label, isRequired} = formItem
      const input = ''
      const validation = {ok: null}

      items.push({
        type,
        label,
        isRequired,
        options,
        input,
        validation,
      })
    }

    const form = {title, items}
    res.send({form})
  } catch (err) {
    next(err)
  }
}

## api-submit.js
const crypto = require('crypto')
const {Op} = require('sequelize')
const model = require('./model')
const {validate} = require('./validate')

exports.apiSubmit = async (req, res, next) => {
  try {
    const {canValidate, validation} = await validate(req)

    if (!canValidate || !validation.ok) {
      res.status(400).end()
      return
    }

    await model.sequelize.transaction(async (transaction) => {
      const answer = await model.answer.create({
        code: crypto.randomBytes(4).toString('hex'),
        date: new Date(),
        formId: req.locals.form.id,
      }, {transaction})

      const formItems = await model.formItem.findAll({
        where: {
          formId: {[Op.eq]: req.locals.form.id},
        },
        sort: [['sort', 'asc']],
        include: [{model: model.formItemType, as: 'formItemType'}],
        transaction,
      })

      for (const formItem of formItems) {
        const i = formItems.indexOf(formItem)
        const answerItem = await model.answerItem.create({
          sort: formItem.sort,
          label: formItem.label,
          input: req.body.items[i].input,
          answerId: answer.id,
        }, {transaction})

        if (formItem.formItemType.code === 'checkbox') {
          const formItemOptions = await model.formItemOption.findAll({
            where: {
              formItemId: {[Op.eq]: formItem.id},
            },
            order: [['sort', 'asc']],
            transaction,
          })

          for (const formItemOption of formItemOptions) {
            const j = formItemOptions.indexOf(formItemOption)

            if (req.body.items[i].options[j].isSelected) {
              await model.answerItemOption.create({
                sort: formItemOption.sort,
                label: formItemOption.label,
                answerItemId: answerItem.id,
              }, {transaction})
            }
          }
        }
      }

      const ok = true
      const redirect = './finish/'

      res.status(201).send({ok, redirect})
    })
  } catch (err) {
    next(err)
  }
}

## api-validate.js
const {validate} = require('./validate')

exports.apiValidate = async (req, res, next) => {
  try {
    const {canValidate, validation} = await validate(req)

    if (!canValidate) {
      res.status(400).end()
    } else {
      res.send(validation)
    }
  } catch (err) {
    next(err)
  }
}

## db.sql
create database hana_forms_db charset utf8;
create user hana_forms_user@localhost identified by 'hana_forms_pass';
grant all privileges on hana_forms_db.* to hana_forms_user@localhost;


## find-form.js
const {Op} = require('sequelize')
const model = require('./model')

exports.findForm = async (req, res, next) => {
  try {
    const form = await model.form.findOne({
      where: {
        code: {[Op.eq]: req.params.formCode},
      },
    })

    if (!form) {
      res.status(404).end()
      return
    }

    req.locals = req.locals || {}
    req.locals.form = form

    next()
  } catch (err) {
    next(err)
  }
}

## fixture.js
const model = require('./model')

if (require.main === module) {
  main()
}

async function main () {
  try {
    await model.sequelize.sync({force: true})

    const formItemType = {
      text: await model.formItemType.create({
        code: 'text',
        sort: 1,
        title: 'Text input',
      }),

      textarea: await model.formItemType.create({
        code: 'textarea',
        sort: 2,
        title: 'Textarea',
      }),

      radio: await model.formItemType.create({
        code: 'radio',
        sort: 3,
        title: 'Radio button',
      }),

      checkbox: await model.formItemType.create({
        code: 'checkbox',
        sort: 4,
        title: 'Checkbox',
      }),
    }

    if (process.env.FIXTURE_SKIP === '1') {
      return
    }

    const form = await model.form.create({
      code: '1234abcd',
      title: 'TITLE',
      owner: process.env.FIXTURE_OWNER,
    })

    const formItems = [
      await model.formItem.create({
        code: '12ab',
        sort: 1,
        label: 'inputText',
        isRequired: true,
        formId: form.id,
        formItemTypeId: formItemType.text.id,
      }),

      await model.formItem.create({
        code: '34cd',
        sort: 2,
        label: 'inputTextarea',
        isRequired: true,
        formId: form.id,
        formItemTypeId: formItemType.textarea.id,
      }),

      await model.formItem.create({
        code: '56ef',
        sort: 3,
        label: 'selectRadio',
        isRequired: true,
        formId: form.id,
        formItemTypeId: formItemType.radio.id,
      }),

      await model.formItem.create({
        code: '7890',
        sort: 4,
        label: 'selectCheckbox',
        isRequired: true,
        formId: form.id,
        formItemTypeId: formItemType.checkbox.id,
      }),
    ]

    const formItemOptions = [
      await model.formItemOption.create({
        code: '1a',
        sort: 1,
        label: 'selectRadio option 1',
        formItemId: formItems[2].id,
      }),

      await model.formItemOption.create({
        code: '2b',
        sort: 2,
        label: 'selectRadio option 2',
        formItemId: formItems[2].id,
      }),

      await model.formItemOption.create({
        code: '3c',
        sort: 1,
        label: 'selectCheckbox option 1',
        formItemId: formItems[3].id,
      }),

      await model.formItemOption.create({
        code: '4d',
        sort: 2,
        label: 'selectCheckbox option 2',
        formItemId: formItems[3].id,
      }),
    ]
  } catch (err) {
    console.error(err)
  } finally {
    model.sequelize.close()
  }
}

## loc.js
const fs = require('fs')
const path = require('path')

const files = fs.readdirSync(__dirname)
  .filter(file => {
    return (/\.js$/.test(file) || /\.ejs$/.test(file))
      && file !== 'loc.js'
  })

const lines = files.map(file => {
  const buffer = fs.readFileSync(path.join(__dirname, file))
  const text = buffer.toString()
  const lines = text.split('\n')

  return lines.length
})

const sum = lines.reduce((memo, line) => memo + line, 0)

console.log({sum})

## main.js
const path = require('path')
const morgan = require('morgan')
const {Op} = require('sequelize')
const express = require('express')
const nocache = require('nocache')
const model = require('./model')
const {apiInitialize} = require('./api-initialize')
const {apiValidate} = require('./api-validate')
const {apiSubmit} = require('./api-submit')
const {findForm} = require('./find-form')

if (require.main === module) {
  main()
}

function main () {
  try {
    const router = express()

    router.set('views', __dirname)
    router.set('view engine', 'ejs')
    router.set('strict routing', true)

    router.use(morgan('dev'))

    router.get('/', render('view-home', {env: process.env}))
    router.get('/form/:formCode([0-9a-f]+)/', findForm)
    router.get('/form/:formCode([0-9a-f]+)/', render('view-input'))
    router.get('/form/:formCode([0-9a-f]+)/finish/', render('view-finish'))
    router.get('/js/input.js', sendFile('static-js-input.js'))

    router.use('/api/', express.json())
    router.use('/api', nocache())
    router.use('/api/form/:formCode([0-9a-f]+)/', findForm)
    router.get('/api/form/:formCode([0-9a-f]+)/initialize', apiInitialize)
    router.post('/api/form/:formCode([0-9a-f]+)/validate', apiValidate)
    router.post('/api/form/:formCode([0-9a-f]+)/submit', apiSubmit)

    router.use((err, _, res, __) => {
      res.status(err.status || 500).end()
      console.error(err)
    })

    router.listen(process.env.PORT, () => {
      console.info(`Listening on ${process.env.PORT}`)
    })
  } catch (err) {
    console.error(err)
  }
}

function sendFile(filename) {
  return (req, res) => {
    res.sendFile(path.join(__dirname, filename))
  }
}

function render(view, locals) {
  return (req, res) => {
    res.render(view, locals)
  }
}

## model.js
const {Sequelize, DataTypes} = require('sequelize')
const dialectOptions = process.env.DB_IS_SSL === '1'
  ? {
    ssl: {
      rejectUnauthorized: true,
    },
  } : {}

const sequelize = new Sequelize(process.env.DB_URL, {
  logQueryParameters: true,
  dialectOptions,
})

const model = {
  sequelize,

  form: sequelize.define('hanaForm', {
    code: {type: DataTypes.STRING, allowNull: false, unique: true},
    title: {type: DataTypes.STRING, allowNull: false},
    owner: {type: DataTypes.STRING, allowNull: false},
  }, {freezeTableName: true}),

  formItem: sequelize.define('hanaFormItem', {
    code: {type: DataTypes.STRING, allowNull: false, unique: true},
    sort: {type: DataTypes.INTEGER, allowNull: false},
    label: {type: DataTypes.TEXT, allowNull: false},
    isRequired: {type: DataTypes.BOOLEAN, allowNull: false},
  }, {freezeTableName: true}),

  formItemOption: sequelize.define('hanaFormItemOption', {
    code: {type: DataTypes.STRING, allowNull: false, unique: true},
    sort: {type: DataTypes.INTEGER, allowNull: false},
    label: {type: DataTypes.STRING, allowNull: false},
  }, {freezeTableName: true}),

  formItemType: sequelize.define('hanaFormItemType', {
    code: {type: DataTypes.STRING, allowNull: false, unique: true},
    sort: {type: DataTypes.INTEGER, allowNull: false},
    title: {type: DataTypes.STRING, allowNull: false},
  }, {freezeTableName: true}),

  answer: sequelize.define('hanaAnswer', {
    code: {type: DataTypes.STRING, allowNull: false, unique: true},
    date: {type: DataTypes.DATE, allowNull: false},
  }, {freezeTableName: true}),

  answerItem: sequelize.define('hanaAnswerItem', {
    sort: {type: DataTypes.INTEGER, allowNull: false},
    label: {type: DataTypes.TEXT, allowNull: false},
    input: {type: DataTypes.TEXT, allowNull: false},
  }, {freezeTableName: true}),

  answerItemOption: sequelize.define('hanaAnswerItemOption', {
    sort: {type: DataTypes.INTEGER, allowNull: false},
    label: {type: DataTypes.STRING, allowNull: false},
  }, {freezeTableName: true}),
}

const options = (as) => process.env.DB_IS_VITESS === '1'
  ? {
    as,
    foreignKey: {allowNull: false},
    constraints: false,
  } : {
    as,
    foreignKey: {allowNull: false},
    onDelete: 'cascade',
    onUpdate: 'cascade',
  }

model.formItem.belongsTo(model.form, options('form'))
model.formItem.belongsTo(model.formItemType, options('formItemType'))
model.formItemOption.belongsTo(model.formItem, options('formItem'))
model.answer.belongsTo(model.form, options('form'))
model.answerItem.belongsTo(model.answer, options('answer'))
model.answerItemOption.belongsTo(model.answerItem, options('answerItem'))

module.exports = model

## package.json
{
  "name": "hanaforms",
  "version": "1.0.0",
  "description": "",
  "main": "app.js",
  "scripts": {
    "start": "node main.js",
    "dev": "nodemon -r dotenv/config main.js",
    "drop:db": "mysql -u root -e 'drop database hana_forms_db'",
    "drop:user": "mysql -u root -e 'drop user hana_forms_user@localhost'",
    "drop": "npm run drop:db && npm run drop:user",
    "db": "mysql -u root < db.sql",
    "fixture": "node -r dotenv/config fixture.js",
    "deploy": "gcloud run deploy hana-forms --source . --region asia-northeast1 --platform managed --allow-unauthenticated"
  },
  "keywords": [],
  "author": "",
  "license": "MIT",
  "dependencies": {
    "dotenv": "^16.0.1",
    "ejs": "^3.1.8",
    "express": "^4.18.1",
    "morgan": "^1.10.0",
    "mysql2": "^2.3.3",
    "nocache": "^3.0.4",
    "sequelize": "^6.19.0"
  },
  "engines": {
    "node": "^16"
  }
}

## partial-checkbox.ejs
<fieldset class="mb-3">
  <legend class="fs-6">
    {{item.label}}
    {{item.isRequired ? '(Required)' : ''}}
  </legend>
  <template v-for="(option, j) of item.options">
    <div class="form-check">
      <input type="checkbox" v-bind:name="'item' + i" v-bind:id="'item' + i + 'option' + j" class="form-check-input" v-bind:class="{'is-invalid': item.validation.ok === false}" v-bind:value="option.label" v-model="option.isSelected">
      <label v-bind:for="'item' + i + 'option' + j" class="form-check-label">{{option.label}}</label>
      <template v-if="j === item.options.length - 1">
        <p class="invalid-feedback mb-0">
          Please select the "{{item.label}}"
        </p>
      </template>
    </div>
  </template>
</fieldset>

## partial-input.ejs
<div class="form-group mb-3">
  <label v-bind:for="'item' + i" class="form-label">
    {{item.label}}
    {{item.isRequired ? '(Required)' : ''}}
  </label>
  <input type="text" v-bind:name="'item' + i" v-bind:id="'item' + i" class="form-control" v-bind:class="{'is-invalid': item.validation.ok === false}" v-model="item.input">
  <p class="invalid-feedback mb-0">Please enter the "{{item.label}}"</p>
</div>

## partial-radio.ejs
<fieldset class="mb-3">
  <legend class="fs-6">
    {{item.label}}
    {{item.isRequired ? '(Required)' : ''}}
  </legend>
  <template v-for="(option, j) of item.options">
    <div class="form-check">
      <input type="radio" v-bind:name="'item' + i" v-bind:id="'item' + i + 'option' + j" class="form-check-input" v-bind:class="{'is-invalid': item.validation.ok === false}" v-bind:value="option.label" v-model="item.input">
      <label v-bind:for="'item' + i + 'option' + j" class="form-check-label">{{option.label}}</label>
      <template v-if="j === item.options.length - 1">
        <p class="invalid-feedback mb-0">
          Please select the "{{item.label}}"
        </p>
      </template>
    </div>
  </template>
</fieldset>

## partial-textarea.ejs
<div class="form-group mb-3">
  <label v-bind:for="'item' + i" class="form-label">
    {{item.label}}
    {{item.isRequired ? '(Required)' : ''}}
  </label>
  <textarea rows="3" v-bind:name="'item' + i" v-bind:id="'item' + i" class="form-control" v-bind:class="{'is-invalid': item.validation.ok === false}" v-model="item.input"></textarea>
  <p class="invalid-feedback mb-0">Please enter the "{{item.label}}"</p>
</div>

## static-js-input.js
const {createApp} = Vue

createApp({
  data () {
    return {
      api: '/api' + window.location.pathname,
      form: null,
    }
  },

  async created() {
    try {
      const url = this.api + 'initialize'
      const response = await fetch(url)
      const {form} = await response.json()

      this.form = form
    } catch (err) {
      console.error(err)
    }
  },

  methods: {
    async onClickButtonSubmit () {
      const url = this.api + 'validate'
      const options = {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json; charset=UTF-8',
        },
        body: JSON.stringify({items: this.form.items})
      }

      const response = await fetch(url, options)
      const {ok, items} = await response.json()

      for (const item of items) {
        const i = items.indexOf(item)
        this.form.items[i].validation = item.validation
      }

      if (ok) {
        const url = this.api + 'submit'
        const response = await fetch(url, options)
        const {ok, redirect} = await response.json()

        if (ok) {
          window.location.assign(redirect)
          return
        }
      }
    },
  },
}).mount('#app')

## validate.js
const {Op} = require('sequelize')
const model = require('./model')

exports.validate = async (req) => {
  const formItems = await model.formItem.findAll({
    where: {
      formId: {[Op.eq]: req.locals.form.id},
    },
    order: [['sort', 'asc']],
  })

  if (formItems.length !== req.body.items.length) {
    return {canValidate: false, validation: null}
  }

  const items = []

  for (const item of req.body.items) {
    const validation = {ok: null}

    if (item.isRequired) {
      if (item.type === 'checkbox') {
        validation.ok = item.options.some((option) => {
          return option.isSelected
        })
      } else {
        validation.ok = !/^\s*$/.test(item.input)
      }
    } else {
      validation.ok = true
    }

    items.push({validation})
  }

  const ok = items.every((item) => {
    return item.validation.ok === true
  })

  const validation = {ok, items}

  return {canValidate: true, validation}
}

## view-finish.ejs
<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Finish | Hana Forms</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-0evHe/X+R7YkIZDRvuzKMRqM+OrBnVFBL6DOitfPri4tjfHxaWutUpFmBp4vmVor" crossorigin="anonymous">
  </head>
  <body>
    <main>
      <div class="container">
        <h1 class="mt-3">Finish</h1>
        <p class="mt-3 mb-3">The answer has been saved.</p>
        <a href="../../../">Back to top page</a>
      </div>
    </main>
  </body>
</html>

## view-home.ejs
<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Hana Forms</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-0evHe/X+R7YkIZDRvuzKMRqM+OrBnVFBL6DOitfPri4tjfHxaWutUpFmBp4vmVor" crossorigin="anonymous">
  </head>
  <body>
    <main>
      <div class="container">
        <h1 class="mt-3">Hana Forms</h1>
        <p class="mt-3 mb-3">
          You can make a from at
          <a href="<%- env.ADMIN_URL %>">Hana Forms Admin</a>
        </p>
      </div>
    </main>
  </body>
</html>

## view-input.ejs
<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Input | Hana Forms</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-0evHe/X+R7YkIZDRvuzKMRqM+OrBnVFBL6DOitfPri4tjfHxaWutUpFmBp4vmVor" crossorigin="anonymous">
  </head>
  <body>
    <main>
      <div id="app">
        <div class="container" v-if="form">
          <h1 class="mt-3">{{form.title}}</h1>
          <form role="form">
            <template v-for="(item, i) of form.items">
              <template v-if="item.type === 'text'">
                <%- include('partial-input') %>
              </template>

              <template v-if="item.type === 'textarea'">
                <%- include('partial-textarea') %>
              </template>

              <template v-if="item.type === 'radio'">
                <%- include('partial-radio') %>
              </template>

              <template v-if="item.type === 'checkbox'">
                <%- include('partial-textarea') %>
              </template>
            </template>

            <button type="submit" class="btn btn-primary" v-on:click.prevent="onClickButtonSubmit">Submit</button>
          </form>
        </div>
      </div>
    </main>
    <script src="https://unpkg.com/vue@3"></script>
    <script src="/js/input.js"></script>
  </body>
</html>
	PORT=3000
	DB_URL=mysql://hana_forms_user:hana_forms_pass@localhost:3306/hana_forms_db
	DB_IS_SSL=0
	DB_IS_VITESS=0
	ADMIN_URL=http://localhost:3001/
	FIXTURE_OWNER=google-oauth2\|000000000000000000000
	FIXTURE_SKIP=0
	/node_modules/
	/.env
	/package-lock.json
	# Do not ignore package-lock.json other than gist
	const {Op} = require('sequelize')
	const model = require('./model')

	exports.apiInitialize = async (req, res, next) => {
	try {
	const formItems = await model.formItem.findAll({
	where: {
	formId: {[Op.eq]: req.locals.form.id},
	},
	order: [['sort', 'asc']],
	include: [{model: model.formItemType, as: 'formItemType'}],
	})

	const {title} = req.locals.form
	const items = []

	for (const formItem of formItems) {
	const options = []
	const {code: type} = formItem.formItemType

	if (type === 'radio' \|\| type === 'checkbox') {
	const formItemOptions = await model.formItemOption.findAll({
	where: {
	formItemId: {[Op.eq]: formItem.id},
	},
	order: [['sort', 'asc']],
	})

	for (const {label} of formItemOptions) {
	options.push({label, isSelected: false})
	}
	}

	const {label, isRequired} = formItem
	const input = ''
	const validation = {ok: null}

	items.push({
	type,
	label,
	isRequired,
	options,
	input,
	validation,
	})
	}

	const form = {title, items}
	res.send({form})
	} catch (err) {
	next(err)
	}
	}
	const crypto = require('crypto')
	const {Op} = require('sequelize')
	const model = require('./model')
	const {validate} = require('./validate')

	exports.apiSubmit = async (req, res, next) => {
	try {
	const {canValidate, validation} = await validate(req)

	if (!canValidate \|\| !validation.ok) {
	res.status(400).end()
	return
	}

	await model.sequelize.transaction(async (transaction) => {
	const answer = await model.answer.create({
	code: crypto.randomBytes(4).toString('hex'),
	date: new Date(),
	formId: req.locals.form.id,
	}, {transaction})

	const formItems = await model.formItem.findAll({
	where: {
	formId: {[Op.eq]: req.locals.form.id},
	},
	sort: [['sort', 'asc']],
	include: [{model: model.formItemType, as: 'formItemType'}],
	transaction,
	})

	for (const formItem of formItems) {
	const i = formItems.indexOf(formItem)
	const answerItem = await model.answerItem.create({
	sort: formItem.sort,
	label: formItem.label,
	input: req.body.items[i].input,
	answerId: answer.id,
	}, {transaction})

	if (formItem.formItemType.code === 'checkbox') {
	const formItemOptions = await model.formItemOption.findAll({
	where: {
	formItemId: {[Op.eq]: formItem.id},
	},
	order: [['sort', 'asc']],
	transaction,
	})

	for (const formItemOption of formItemOptions) {
	const j = formItemOptions.indexOf(formItemOption)

	if (req.body.items[i].options[j].isSelected) {
	await model.answerItemOption.create({
	sort: formItemOption.sort,
	label: formItemOption.label,
	answerItemId: answerItem.id,
	}, {transaction})
	}
	}
	}
	}

	const ok = true
	const redirect = './finish/'

	res.status(201).send({ok, redirect})
	})
	} catch (err) {
	next(err)
	}
	}
	const {validate} = require('./validate')

	exports.apiValidate = async (req, res, next) => {
	try {
	const {canValidate, validation} = await validate(req)

	if (!canValidate) {
	res.status(400).end()
	} else {
	res.send(validation)
	}
	} catch (err) {
	next(err)
	}
	}
	create database hana_forms_db charset utf8;
	create user hana_forms_user@localhost identified by 'hana_forms_pass';
	grant all privileges on hana_forms_db.* to hana_forms_user@localhost;
	const {Op} = require('sequelize')
	const model = require('./model')

	exports.findForm = async (req, res, next) => {
	try {
	const form = await model.form.findOne({
	where: {
	code: {[Op.eq]: req.params.formCode},
	},
	})

	if (!form) {
	res.status(404).end()
	return
	}

	req.locals = req.locals \|\| {}
	req.locals.form = form

	next()
	} catch (err) {
	next(err)
	}
	}
	const model = require('./model')

	if (require.main === module) {
	main()
	}

	async function main () {
	try {
	await model.sequelize.sync({force: true})

	const formItemType = {
	text: await model.formItemType.create({
	code: 'text',
	sort: 1,
	title: 'Text input',
	}),

	textarea: await model.formItemType.create({
	code: 'textarea',
	sort: 2,
	title: 'Textarea',
	}),

	radio: await model.formItemType.create({
	code: 'radio',
	sort: 3,
	title: 'Radio button',
	}),

	checkbox: await model.formItemType.create({
	code: 'checkbox',
	sort: 4,
	title: 'Checkbox',
	}),
	}

	if (process.env.FIXTURE_SKIP === '1') {
	return
	}

	const form = await model.form.create({
	code: '1234abcd',
	title: 'TITLE',
	owner: process.env.FIXTURE_OWNER,
	})

	const formItems = [
	await model.formItem.create({
	code: '12ab',
	sort: 1,
	label: 'inputText',
	isRequired: true,
	formId: form.id,
	formItemTypeId: formItemType.text.id,
	}),

	await model.formItem.create({
	code: '34cd',
	sort: 2,
	label: 'inputTextarea',
	isRequired: true,
	formId: form.id,
	formItemTypeId: formItemType.textarea.id,
	}),

	await model.formItem.create({
	code: '56ef',
	sort: 3,
	label: 'selectRadio',
	isRequired: true,
	formId: form.id,
	formItemTypeId: formItemType.radio.id,
	}),

	await model.formItem.create({
	code: '7890',
	sort: 4,
	label: 'selectCheckbox',
	isRequired: true,
	formId: form.id,
	formItemTypeId: formItemType.checkbox.id,
	}),
	]

	const formItemOptions = [
	await model.formItemOption.create({
	code: '1a',
	sort: 1,
	label: 'selectRadio option 1',
	formItemId: formItems[2].id,
	}),

	await model.formItemOption.create({
	code: '2b',
	sort: 2,
	label: 'selectRadio option 2',
	formItemId: formItems[2].id,
	}),

	await model.formItemOption.create({
	code: '3c',
	sort: 1,
	label: 'selectCheckbox option 1',
	formItemId: formItems[3].id,
	}),

	await model.formItemOption.create({
	code: '4d',
	sort: 2,
	label: 'selectCheckbox option 2',
	formItemId: formItems[3].id,
	}),
	]
	} catch (err) {
	console.error(err)
	} finally {
	model.sequelize.close()
	}
	}
	const fs = require('fs')
	const path = require('path')

	const files = fs.readdirSync(__dirname)
	.filter(file => {
	return (/\.js$/.test(file) \|\| /\.ejs$/.test(file))
	&& file !== 'loc.js'
	})

	const lines = files.map(file => {
	const buffer = fs.readFileSync(path.join(__dirname, file))
	const text = buffer.toString()
	const lines = text.split('\n')

	return lines.length
	})

	const sum = lines.reduce((memo, line) => memo + line, 0)

	console.log({sum})
	const path = require('path')
	const morgan = require('morgan')
	const {Op} = require('sequelize')
	const express = require('express')
	const nocache = require('nocache')
	const model = require('./model')
	const {apiInitialize} = require('./api-initialize')
	const {apiValidate} = require('./api-validate')
	const {apiSubmit} = require('./api-submit')
	const {findForm} = require('./find-form')

	if (require.main === module) {
	main()
	}

	function main () {
	try {
	const router = express()

	router.set('views', __dirname)
	router.set('view engine', 'ejs')
	router.set('strict routing', true)

	router.use(morgan('dev'))

	router.get('/', render('view-home', {env: process.env}))
	router.get('/form/:formCode([0-9a-f]+)/', findForm)
	router.get('/form/:formCode([0-9a-f]+)/', render('view-input'))
	router.get('/form/:formCode([0-9a-f]+)/finish/', render('view-finish'))
	router.get('/js/input.js', sendFile('static-js-input.js'))

	router.use('/api/', express.json())
	router.use('/api', nocache())
	router.use('/api/form/:formCode([0-9a-f]+)/', findForm)
	router.get('/api/form/:formCode([0-9a-f]+)/initialize', apiInitialize)
	router.post('/api/form/:formCode([0-9a-f]+)/validate', apiValidate)
	router.post('/api/form/:formCode([0-9a-f]+)/submit', apiSubmit)

	router.use((err, _, res, __) => {
	res.status(err.status \|\| 500).end()
	console.error(err)
	})

	router.listen(process.env.PORT, () => {
	console.info(`Listening on ${process.env.PORT}`)
	})
	} catch (err) {
	console.error(err)
	}
	}

	function sendFile(filename) {
	return (req, res) => {
	res.sendFile(path.join(__dirname, filename))
	}
	}

	function render(view, locals) {
	return (req, res) => {
	res.render(view, locals)
	}
	}