This is the third day of my participation in the August Text Challenge.More challenges in August

Before, we have written two interfaces of login and registration and issued the token. Next, we will publish the article today (add, delete, change and check).

See this article for details: The second article

The general process is like this

1. Create the article table

CREATE TABLE IF NOT EXISTS `moment`(
	id INT PRIMARY KEY AUTO_INCREMENT,
	content VARCHAR(1000) NOT NULL,
	user_id INT NOT NULL,
	createAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updateAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.FOREIGN KEY(user_id) REFERENCES user(id));Copy the code

2. To createmomentrouting

    moment.router.js
    
const Router = require('koa-router')

const momentRouter = new Router({prefix: '/moment'})

const {
    create
} = require('.. /controller/moment.controller')

// Update
momentRouter.post("/".)module.exports = momentRouter
Copy the code
    moment.controller.js

class MomentController {
    async create(ctx, next) {
        ctx.body = "Comment on success"}}module.exports = new MomentController()
Copy the code

Implement middleware that verifies loginverifyAuth, belong to the auth. Middleware. Js


const jwt = require("jsonwebtoken");    // Use the public key to decrypt the password
const { PUBLIC_KEY } = require(".. /app/config");    / / the public key

// Verify login
const verifyAuth = async (ctx, next) => {
  const authorization = ctx.headers.authorization;

  / / access token
  if(! authorization) {const error = new Error(errorTypes.UNAUTHORIZATION);
    return ctx.app.emit("error", error, ctx);
  }
  const token = authorization.replace("Bearer "."");

  / / authentication token
  try {
    const result = jwt.verify(token, PUBLIC_KEY, {
      algorithms: ["RS256"]}); ctx.user = result;await next();
  } catch (err) {
    const error = new Error(errorTypes.UNAUTHORIZATION);
    ctx.app.emit("error", error, ctx); }};module.exports = {
  verifyAuth
};

Copy the code

When the verification is successful, it enters our publication middlewarecreate

const momentService = require(".. /service/moment.service");

async create(ctx, next) {
    // Get data (user_id, content)
    const userId = ctx.user.id;
    const content = ctx.request.body.content;

    // Data is inserted into the database
    const result = await momentService.create(userId, content);
    ctx.body = result;
  }
Copy the code
const connection = require(".. /app/database");

class MomentService {
    // Insert the retrieved comment content into the data
    async create(userId, content) {
        const statement = `INSERT INTO moment (content, user_id) VALUES (? ,?) ; `;
    
        const [result] = await connection.execute(statement, [content, userId]);
    
        returnresult; }}module.exports= new MomentService()
Copy the code

test

Dynamic Query (single)

//moment.router.js

const {
    detail
} = require('.. /controller/moment.controller')

// Query dynamic (single)
momentRouter.get("/:momentId", detail)
Copy the code

//moment.controller.js

class MomentController {
    async detail(ctx, next) {
          const momentId = ctx.params.momentId      

          ctx.body = momentId / / 2}}module.exports = new MomentController();
Copy the code

After obtaining the id of the article to be queried, we enter the data to start the query (the returned data include article, user ID, publication time and update time).

//moment.service.js

const connection = require(".. /app/database");

class MomentService {
    async getMomentById(id) {
        // We can also use tyrcatch to catch any errors
        const statement = ` SELECT m.id id, m.content content, m.createAt createTime, m.updateAt updateTime, JSON_OBJECT('id', u.id, 'name', u.name) author from moment m LEFT JOIN user u ON m.user_id = u.id WHERE m.id = ? `

        const [result] = await connection.execute(statement, [id])

        return result
      }
}
module.exports= new MomentService()
Copy the code

Return the result to the user

//moment.controller.js

const momentService = require(".. /service/moment.service");

class MomentController {
    async detail(ctx, next) {
    / / to get id
    const momentId = ctx.params.momentId;

    // Query by id
    const result = awaitmomentService.getMomentById(momentId); ctx.body = result; }}module.exports = new MomentController();
Copy the code

Querying a dynamic (multiple) process is similar to querying a single one, with the only difference being database queries

// moment.router.js

const {
    list
} = require('.. /controller/moment.controller')

// Query dynamic (multiple)
momentRouter.get("/", list)
Copy the code

Gets the incoming Query and passes it to the database for query

const momentService = require(".. /service/moment.service");

class MomentController {
    async list(ctx, next) {
      // Get data (offset, size)
    const { offset, size } = ctx.query;

    const result = awaitmomentService.getMomentList(offset, size); ctx.body = result; }}module.exports = new MomentController();
Copy the code
const connection = require(".. /app/database");

class MomentService {
    async getMomentList(offset, size) {
            const statement = ` SELECT m.id id, m.content content, m.createAt createTime, m.updateAt updateTime, JSON_OBJECT('id', u.id, 'name', u.name) author FROM moment m LEFT JOIN user u ON m.user_id = u.id LIMIT ?, ?; `

            const [result] = await connection.execute(statement, [offset, size])

            return result
          }
}      
      module.exports= new MomentService()
Copy the code

Modify the dynamic

Graph TD must be logged in --> The dynamic to be modified belongs to the logged in person --> Modified successfully/modified failed

Middleware that encapsulates authentication permissionsverifyPermission

//auth.middleware.js

// Through closures
const verifyPermission = (tableName) = > {     // The table to query
  return async (ctx, next) => {
    / / get data
    const {commentId} = ctx.params   // Get the id of the article
    const { id } = ctx.user;         // User ID

    / / query
    const isPermission = await authService.checkResoure(
      tableName,
      commentId,
      id
    );
  
    If not, throw an exception
    if(! isPermission) {const error = new Error(errorTypes.UNPERMISSION);
      return ctx.app.emit("error", error, ctx);
    }
  
    await next();
  };
}

module.exports = {
  verifyPermission
};
Copy the code
// auth.service.js

const connection = require(".. /app/database");

// Query whether the user exists according to the table id and user ID
class AuthService {
  async checkResoure(tableName, resourceId, userId) {
    const statement = `SELECT * from ${tableName}WHERE id = ? AND user_id = ? `;

    const [result] = await connection.execute(statement, [resourceId, userId]);

    return result.length === 0 ? false : true; }}module.exports = new AuthService();
Copy the code

When it has, get the modified content, go to the database for modification and prompt success

// moment.router.js

const {
    update
} = require('.. /controller/moment.controller')

const {
    verifyAuth,
    verifyPermission
} = require('.. /middleware/auth.middleware.js')

// Change the dynamic
momentRouter.patch("/:momentId", verifyAuth, verifyPermission(moment), update)
Copy the code
// moment.controller

const momentService = require(".. /service/moment.service");

class MomentController {
    async update(ctx, next) {
        const { momentId } = ctx.params;
        const { content } = ctx.request.body;

        const result = awaitmomentService.update(content, momentId); ctx.body = result; }}module.exports = new MomentController();
Copy the code
// moment.service.js

const connection = require(".. /app/database");

class MomentService {
    async update(content, momentId) {
            try {
              const statement = `UPDATE moment SET content = ? WHERE id = ? `;

              const [result] = await connection.execute(statement, [content, momentId]);

              return result;
            } catch (error) {
              console.log(error); }}}module.exports= new MomentService()
Copy the code

Delete the dynamic

Graph TD must be logged in --> The dynamic to be deleted belongs to the logged in person --> Delete succeeded/delete failed
// moment.router.js

const {
    remove
} = require('.. /controller/moment.controller')

const {
    verifyAuth,
    verifyPermission
} = require('.. /middleware/auth.middleware.js')

// Delete the dynamic
momentRouter.delete("/:momentId", verifyAuth, verifyPermission(moment), remove)
Copy the code
//moment.controller.js

const momentService = require(".. /service/moment.service");

class MomentController {
    async remove(ctx, next) {
        const { momentId } = ctx.params;

        const result = awaitmomentService.remove(momentId); ctx.body = result; }}module.exports = new MomentController();
Copy the code
// moment.service.js

const connection = require(".. /app/database");

class MomentService {
    async remove(momentId) {
        try {
          const statement = `DELETE FROM moment WHERE id = ? `;
    
          const [result] = await connection.execute(statement, [momentId]);
    
          return result;
        } catch (error) {
          console.log(error); }}}module.exports= new MomentService()
Copy the code

Okay, dynamic add, delete, change check is done,The project addressWelcome to download and learn