# React全球新闻发布管理系统后端

# 1.技术栈

koa
ts
prisma

# 2.数据库表设计

schema.prisma

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

model User{
  id Int @id @default(autoincrement())
  username String @unique
  password String
  default Boolean @default(false)
  roleState Boolean @default(true)
  region String @default("全球")
  Region Region? @relation(fields: [region],references: [value])
  roleId Int
  Role Role @relation(fields: [roleId],references: [id])
  news New[]
}

model Role{
  id Int @id @default(autoincrement())
  rolename String
  roleType Int
  users User[]
  rights Right[]
  news New[]
}

model Right{
  id Int @id @default(autoincrement())
  title String
  key String @unique
  pagepermission Int?
  routerpermission Int?
  grade Int @default(1)
  Role Role[]
  children Right[] @relation("children")
  rightId Int?
  right Right? @relation("children",fields: [rightId],references: [id])
}

model Category{
  id Int @id @default(autoincrement())
  title String @unique
  value String @unique
  news New[]
}

model Region{
  id Int @id @default(autoincrement())
  title String @unique
  value String @unique
  news New[]
  users User[]
}

model New{
  id Int @id @default(autoincrement())
  author String
  User User @relation(fields: [author],references: [username])
  categoryId Int
  Category Category @relation(fields: [categoryId],references: [id])
  region String @default("全球")
  Region Region? @relation(fields: [region],references: [value])
  roleId Int
  Role Role @relation(fields: [roleId],references: [id])
  auditState Int @default(0)
  publishState Int @default(0)
  createTime DateTime @default(now())
  updateTime DateTime?
  title String
  article String @db.LongText
  star Int @default(0)
  view Int @default(0)
  publishTime DateTime?
  coverImage String?
}

# 3.数据库相关操作

db文件夹下全是数据库相关操作，大部分都是CRUD，少部分涉及到，分组，聚合，排序等

category.ts

import { prisma } from "./right.js";

export const getCategories=async()=>{
    try {
        return await prisma.category.findMany()
    } catch (error) {
        console.log(error)
        return Promise.reject(error)
    }
}

export const deleteCategory=async(id:number)=>{
    try {
        return await prisma.category.delete({
            where:{
                id
            }
        })
    } catch (error) {
        console.log(error)
        return Promise.reject(error)
    }
}

export const updateCategory=async(id:number,value:string)=>{
    try {
        return await prisma.category.update({
            where:{
                id
            },
            data:{
                title:value,
                value
            }
        })
    } catch (error) {
        console.log(error)
        return Promise.reject(error)
    }
}

export const createCategory=async(value:string)=>{
    try {
        return await prisma.category.create({
            data:{
                title:value,
                value
            }
        })
    } catch (error) {
        console.log(error)
        return Promise.reject(error)
    }
}

export const categoriesList=async(author?:string)=>{
    try {
        const category=await prisma.category.findMany()
        const newsCount=await prisma.new.groupBy({
            by:['categoryId'],
            where:{
                publishState:2,
                author
            },
            _count:true,
            orderBy:{
                categoryId:'asc'
            }
        })
        return category.map((item)=>({
            ...item,
            _count:newsCount.find(i=>i.categoryId===item.id)?._count ?? 0
        }))
        
    } catch (error) {
        console.log(error)
        return Promise.reject(error)
    }
}

# 4.中间件

jwt.middleware.ts做登录权限校验的

import { secret } from "./../router/login.js";
import { Middleware } from "koa";
import Jwt from "jsonwebtoken";
const whiteList = ["/login", "/tourist-news", "/tourist-detail/:id",'/'];
//匹配路径，自动替换：id
const matchPatch = (whiteList: string[], path: string) => {
  let flag = false;
  for (let item of whiteList) {
    const regExp = new RegExp("\\" + item.replace(/\/:id$/g, "\\/[\\d]+"), "g");
    flag = regExp.test(path);
    if (flag) {
      return true;
    }
  }
  return flag;
};

export const JwtMiddleware: Middleware = async (ctx, next) => {
  if (matchPatch(whiteList, ctx.path)) {
    await next();
  } else {
    if (ctx.header.authorization) {
      const token = ctx.header.authorization;
      if (Jwt.verify(token, secret)) {
        await next();
      } else {
        ctx.status = 403;
        ctx.body = {
          meta: {
            msg: "no authorization",
          },
        };
      }
    } else {
      ctx.status = 403;
      ctx.body = {
        meta: {
          msg: "no authorization",
        },
      };
    }
  }
};

# 5.路由

router/news.ts

import { auditList, createNews, deleteNews, draftNews, draftPreviewNews, mostNewsStar, mostNewsView, publishList, touristNews, updateDraftNews, waitAuditList } from "./../db/news.js";
import Router = require("koa-router");
const router = new Router();
export default router;
/* 创建新闻 */
router.post("/news", async (ctx) => {
  try {
    const data = ctx.request.body;
    const file = ctx.request.files;
    const coverImage = (file?.coverImage as any)?.newFilename;
    const res = await createNews({
      ...data,
      categoryId: +data.categoryId,
      roleId: +data.roleId,
      auditState: +data.auditState,
      coverImage,
    });
    ctx.body = {
      data: res,
      meta: {
        msg: "ok",
        status: 200,
      },
    };
  } catch (error) {
    console.log(error);
    Promise.reject(error);
  }
});

/* 草稿箱 */
router.post('/draft',async (ctx) => {
  try {
    const data = ctx.request.body.author;
    const res = await draftNews(data)
    ctx.body = {
      data: res,
      meta: {
        msg: "ok",
        status: 200,
      },
    };
  } catch (error) {
    console.log(error);
    Promise.reject(error);
  }
})

router.delete('/draft/:id',async (ctx) => {
  try {
    const id = ctx.params.id;
    const res = await deleteNews(+id)
    ctx.body = {
      data: res,
      meta: {
        msg: "ok",
        status: 200,
      },
    };
  } catch (error) {
    console.log(error);
    Promise.reject(error);
  }
})

router.get('/draft/:id',async (ctx) => {
  try {
    const id = ctx.params.id;
    const res = await draftPreviewNews(+id)
    ctx.body = {
      data: res,
      meta: {
        msg: "ok",
        status: 200,
      },
    };
  } catch (error) {
    console.log(error);
    Promise.reject(error);
  }
})


router.post("/draft/:id", async (ctx) => {
  try {
    const id = ctx.params.id;
    const data = ctx.request.body;
    const file = ctx.request.files;
    const coverImage = (file?.coverImage as any)?.newFilename;
    const res = await updateDraftNews(+id,{
      ...data,
      categoryId: +data.categoryId,
      auditState: +data.auditState,
      coverImage,
    });
    ctx.body = {
      data: res,
      meta: {
        msg: "ok",
        status: 200,
      },
    };
  } catch (error) {
    console.log(error);
    Promise.reject(error);
  }
});

router.post("/push_audit/:id", async (ctx) => {
  try {
    const id = ctx.params.id;
    const res = await updateDraftNews(+id,ctx.request.body);
    ctx.body = {
      data: res,
      meta: {
        msg: "ok",
        status: 200,
      },
    };
  } catch (error) {
    console.log(error);
    Promise.reject(error);
  }
});


router.get('/audit',async(ctx)=>{
  try {
    const author= ctx.query.author as string;
    const res = await auditList(author)
    ctx.body = {
      data: res,
      meta: {
        msg: "ok",
        status: 200,
      },
    };
  }catch (error) {
      console.log(error);
      Promise.reject(error);
    }
  }
)

router.get('/audit-list',async(ctx)=>{
  try {
    const {roleId,region}=ctx.query
    let res =await waitAuditList()
    if(roleId && region){
      res=await waitAuditList(+roleId,region as string)
    }
    ctx.body = {
      data: res,
      meta: {
        msg: "ok",
        status: 200,
      },
    };
  }catch (error) {
      console.log(error);
      Promise.reject(error);
    }
  }
)

router.get('/publish-list',async(ctx)=>{
  try {
    const {author,publishState}=ctx.query
    let res
    if(author && publishState){
      res=await publishList(author as string,+publishState)
    }
    ctx.body = {
      data: res,
      meta: {
        msg: "ok",
        status: 200,
      },
    };
  }catch (error) {
      console.log(error);
      Promise.reject(error);
    }
  }
)


router.get('/most-view',async(ctx)=>{
  try {
    const res=await mostNewsView()
    ctx.body = {
      data: res,
      meta: {
        msg: "ok",
        status: 200,
      },
    };
  }catch (error) {
      console.log(error);
      Promise.reject(error);
    }
  }
)

router.get('/most-star',async(ctx)=>{
  try {
    const res=await mostNewsStar()
    ctx.body = {
      data: res,
      meta: {
        msg: "ok",
        status: 200,
      },
    };
  }catch (error) {
      console.log(error);
      Promise.reject(error);
    }
  }
)

router.get('/tourist-news',async(ctx)=>{
  try {
    const res=await touristNews()
    ctx.body = {
      data: res,
      meta: {
        msg: "ok",
        status: 200,
      },
    };
  }catch (error) {
      console.log(error);
      Promise.reject(error);
    }
  }
)

router.get('/tourist-detail/:id',async(ctx)=>{
  try {
    const id=ctx.params.id
    const res=await draftPreviewNews(+id)
    ctx.body = {
      data: res,
      meta: {
        msg: "ok",
        status: 200,
      },
    };
  }catch (error) {
      console.log(error);
      Promise.reject(error);
    }
  }
)

router.post('/tourist-detail/:id',async(ctx)=>{
  try {
    const id = ctx.params.id;
    const res = await updateDraftNews(+id,ctx.request.body);
    ctx.body = {
      data: res,
      meta: {
        msg: "ok",
        status: 200,
      },
    };
  }catch (error) {
      console.log(error);
      Promise.reject(error);
    }
  }
)

# 6.app.ts

import { errorMiddleware } from './middleware/error.middleware.js';
import { routerList } from './router/index.js';
import koaBody from "koa-body";
import Koa from 'koa'
import KoaStatic from 'koa-static'
import {resolve} from 'path'
import { JwtMiddleware } from './middleware/jwt.middleware.js';
import koaCors from 'koa-cors';

const app=new Koa()

app.use(koaCors())
app.use(koaBody({
    multipart:true,
    formidable:{
        uploadDir:resolve(__dirname,'../public/image'),
        keepExtensions:true,
    }
}))
app.use(KoaStatic(resolve(__dirname,'../public')))
app.use(JwtMiddleware)
app.use(errorMiddleware)
routerList.forEach(router=>{
    app.use(router.routes())
})

app.listen(5143,()=>{
    console.log('server is running at http://localhost:5143')
})

# 总结

做完这个全栈项目，对前后端理解更深了，对数据库相关的操作也更熟练了，仅使用prisma方面。

红尘散仙

全球新闻发布管理系统后端

全球新闻发布管理系统后端

# React全球新闻发布管理系统后端

# 1.技术栈

# 2.数据库表设计

# 3.数据库相关操作

# 4.中间件

# 5.路由

# 6.app.ts

# 总结