The Node series learning MySQL redo disk

This is the 12th day of my participation in Gwen Challenge

Having spent two days learning how to use MySQL in Node, you can check out my two articles: MySQL Node series learning MySQL(2), today home first day, get up early + travel tired + cooking, I’m tired, I can’t move ~ so today I’m simple to resume the study of these two days!

File analysis

conf/db.js

Store environment variables and configurations related to SQL linked databases

const env = process.env.NODE_ENV // Get environment parameters

let MYSQL_CONF

if (env == 'dev') {
  MYSQL_CONF = {
    host: 'localhost'.user: 'root'.password: '111'.port: '3306'.database: 'myblog'}}if (env == 'production') {
  MYSQL_CONF = {
    host: 'localhost'.user: 'root'.password: '111'.port: '3306'.database: 'myblog'}}module.exports = {
  MYSQL_CONF
}

Copy the code

controller

The files in the directory remotely correspond to the routing files in the router directory, and only care about the data. After receiving the data from the database, the data is returned without any processing

crotroller/blog.js

const {exec} = require('.. /db/mysql.js')

const getList = (author, keyword) = > {
  
  let sql = ` select * from blogs where 1=1 `
  if (author) {
    sql += `and author='${author}'`
  }
  if(keyword) {
    sql += `and title like '%${keyword}%' `
  }
  sql += `order by createtime desc; `
  // Return a Promise
  return exec(sql)
  
}
// Blog details
const getDetail = id= > {
  const sql = `select * from blogs where id='${id}'`
  return exec(sql).then(rows= > {
    return rows[0]})}// Create a new blog
const newBlog = (blogData = {}) = > {
  const title = blogData.title
  const content = blogData.content
  const author = blogData.author
  const createTime = Date.now()
  const sql = `
    insert into blogs (title,content,createtime,author) values ('${title}', '${content}',${createTime}, '${author}');
  `
  return exec(sql).then(insertData= > {
    // console.log('insertData',insertData);
    return {
      id: insertData.insertId
    }
  })
}
const updateBlog = (id,blogData= {}) = > {
  const title= blogData.title
  const content = blogData.content
  const sql = `
    update blogs set title='${title}', content='${content}' where id=${id}; 
  `
  return exec(sql).then(updateData= > {
    // console.log('updateData', updateData);
    if(updateData.affectedRows > 0) {
      return true
    }
    return false})}const delBlog = (id,author) = > {
  const sql = `
    delete from blogs where id='${id}' and author='${author}';
  `
  return exec(sql).then(delData= > {
    if(delData.affectedRows > 0) {
      return true
    }
    return false})}module.exports = {
  getList,
  getDetail,
  newBlog,
  updateBlog,
  delBlog
}
Copy the code

controller/user.js

const {exec} = require('.. /db/mysql.js')
const loginCheck = (username, password) = > {
  const sql = `
    select username, realname from users where username='${username}' and password='${password}';
  `
  return exec(sql).then(rows= > {
    return rows[0) | | {}})}module.exports = {
  loginCheck
}
Copy the code

db/mysql.js

Mysql’s wrapper function, which returns a Promise, returns the result returned by the database.

const mysql = require('mysql')
const { MYSQL_CONF } = require('.. /conf/db')
// Create a link object
const con = mysql.createConnection(MYSQL_CONF)

// Start the connection
con.connect()

// Agree to execute SQL functions

function exec(sql) {
  return new Promise((resolve, reject) = > {
    con.query(sql, (err, result) = > {
      if (err) {
        reject(err)
        return
      }
      resolve(result)
    })
  })
}
module.exports = {
  exec
}
Copy the code

model

model/resModel.js

A unified template can be returned successfully or failed

class BaseModel {
  constructor(data,message) {
    if(typeof data == 'string') {
      this.message = data
      data = null
      message = null
    }
    if(data) {
      this.data = data
    }
    if(message) {
      this.message = message
    }
  }
}

class SuccessModel extends BaseModel {
  constructor(data,message) {
    super(data, message)
    this.errno = 0}}class ErrorModel extends BaseModel {
  constructor(data,message) {
    super(data,message)
    this.errno = -1}}module.exports = {
  SuccessModel,
  ErrorModel
}
Copy the code

router

For routing interface processing, the relevant methods exposed by the same file under the controller are introduced to obtain data, and the data is processed and finally returned

router/user.js

const { loginCheck } = require('.. /controller/user')
const { SuccessModel, ErrorModel } = require('.. /model/resModel')
const handleUserRouter = (req, res) = > {
  const method = req.method

  / / login
  if (method == 'POST' && req.path == '/api/user/login') {
    const { username, password } = req.body
    const result = loginCheck(username,password)
    return result.then(data= > {
      if(data.username) {
        return new SuccessModel()
      }
      return new ErrorModel('Login failed ~')}}}module.exports = handleUserRouter
Copy the code

router/blog.js

const { getList, getDetail, newBlog, updateBlog, delBlog } = require('.. /controller/blog')
const { SuccessModel, ErrorModel } = require('.. /model/resModel')
const handleBlogRouter = (req, res) = > {
  const method = req.method
  const id = req.query.id
  // Get the blog list
  if (method == 'GET' && req.path == '/api/blog/list') {
    const author = req.query.author || ' '
    const keyword = req.query.keyword || ' '
    // const listData = getList(author, keyword)
    // return new SuccessModel(listData)
    const result = getList(author,keyword)
    return result.then(listData= > {
      return new SuccessModel(listData)
    })
  }
  // Get blog details
  if (method == 'GET' && req.path == '/api/blog/detail') {
    // const data = getDetail(id)
    // return new SuccessModel(data)
    const result = getDetail(id)
    return result.then(data= > {
      return new SuccessModel(data)
    })
  }
  // Create a new blog
  if (method == 'POST' && req.path == '/api/blog/new') {
    // const data = newBlog(req.body)
    // return new SuccessModel(data)
    req.body.author = 'tmier' // Wait until the development login is complete before changing to real data
    const result = newBlog(req.body)
    return result.then(data= > {
      return new SuccessModel(data)
    })
  }

  // Update a blog post
  if (method == 'POST' && req.path == '/api/blog/update') {
    const result = updateBlog(id, req.body)
    return result.then(val= > {
      if(val) {
        return new SuccessModel()
      }
      return new ErrorModel('Failed to update blog ~')})}// Delete a blog post
  if (method == 'POST' && req.path == '/api/blog/del') {
    let author = 'tmier'
    const result = delBlog(id,author)
    return result.then(val= > {
      if(val) {
        return new SuccessModel()
      }
      return new ErrorModel('Failed to delete blog ~')}}}module.exports = handleBlogRouter
Copy the code

app.js

The received REQ and returned RES are set and processed uniformly, and the route is not matched

const querystring = require('querystring')
const handleBlogRouter = require('./src/router/blog.js')
const handleUserRouter = require('./src/router/user.js')

// Used to process postData
const getPostData = req= > {
  return new Promise((resolve, reject) = > {
    if(req.method ! = ='POST') {
      resolve({})
      return
    }
    // Non-JSON data type, ignore and return {}
    if (req.headers['content-type'! = ='application/json']) {
      resolve({})
      return
    }
    / / correct
    let postData = ' '
    req.on('data'.chunk= > {
      postData += chunk.toString()
    })
    req.on('end'.() = > {
      if(! postData) { resolve({})return
      }
      // Successful return
      resolve(JSON.parse(postData))
    })
  })
}

const serverHandle = (req, res) = > {
  res.setHeader('Content-Type'.'application/json')

  / / get the path
  const url = req.url
  req.path = url.split('? ') [0]

  / / query
  req.query = querystring.parse(url.split('? ') [1])

  / / postData processing
  getPostData(req).then(postData= > {
    req.body = postData
    // Handle old blog routes

    // const blogData = handleBlogRouter(req, res)
    // if (blogData) {
    // res.end(JSON.stringify(blogData))
    // return
    // }

    // Handle blog routing
    const blogResult = handleBlogRouter(req, res)
    if (blogResult) {
      blogResult.then(blogData= > {
        res.end(JSON.stringify(blogData))
      })
      return
    }

    // Process the user route
    // const userData = handleUserRouter(req, res)
    // if (userData) {
    // res.end(JSON.stringify(userData))
    // return
    // }
    const userResult = handleUserRouter(req,res)
    if(userResult) {
      userResult.then(userData= > {
        res.end(JSON.stringify(userData))
      })
      return 
    }

    // No route is matched, 404 is returned
    res.writeHead(404, { 'Content-Type': 'text/plain' })
    res.write('404 Not Found\n')
    res.end()
  })
}
module.exports = serverHandle
Copy the code