• MySQL 数据库存储，Redis 缓存
• OAuth 鉴权
• Dataloader 数据查询优化
• GraphQL 底层接口数据引擎

表结构

数据库采用 MySQL，核心两张表，分别是 工单 和 回复。

CREATE TABLE IF NOT EXISTS `xibang`.`d_ticket` (
  `tid` varchar(40) NOT NULL DEFAULT '' COMMENT '工单id',
  `uid` int(11) unsigned NOT NULL COMMENT '提交用户id',
  `status` enum('open','closed') NOT NULL DEFAULT 'open' COMMENT '开闭状态',
  `reply` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '回复状态',
  `type` varchar(32) NOT NULL DEFAULT 'bug' COMMENT '类型',
  `notify` enum('mobile','email','both','none') NOT NULL DEFAULT 'email' COMMENT '通知方式',
  `title` varchar(255) NOT NULL DEFAULT '' COMMENT '标题',
  `body` blob NOT NULL COMMENT '描述',
  `createdAt` int(10) unsigned NOT NULL COMMENT '创建时间',
  `updatedAt` int(10) unsigned NOT NULL COMMENT '操作时间',
  PRIMARY KEY (`tid`),
  KEY `uid` (`uid`),
  KEY `createdAt` (`createdAt`),
  KEY `status` (`status`),
  KEY `type` (`type`),
  KEY `reply` (`reply`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

工单状态分两种：

• 打开
• 关闭

回复状态也分两种：

• 0： 未回复
• 1： 回复

CREATE TABLE IF NOT EXISTS `xibang`.`d_ticketreply` (
  `tid` varchar(40) NOT NULL DEFAULT '' COMMENT '工单id',
  `uid` int(11) unsigned NOT NULL COMMENT '回复人用户id',
  `body` blob NOT NULL COMMENT '回复内容',
  `createdAt` int(10) unsigned NOT NULL COMMENT '回复时间',
  `updatedAt` int(10) unsigned NOT NULL COMMENT '最后修改时间',
  KEY `tid` (`tid`),
  KEY `createdAt` (`createdAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

GraphQL Schema

打印脚本：

const { printSchema } = require("graphql");
const schema = require("../src/graphql");

console.log(printSchema(schema));

完整的 GraphQL 结构：

"""
Root mutation object
"""
type Mutation {
  createTicket(input: TicketCreateInput!): Ticket
  updateTicket(input: TicketUpdateInput!): Ticket
  createReply(input: ReplyCreateInput!): TicketReply
  updateReply(input: ReplyUpdateInput!): TicketReply
}

"""
An object with an ID
"""
interface Node {
  """
  The id of the object.
  """
  id: ID!
}

type Owner implements Node {
  """
  The ID of an object
  """
  id: ID!
  uid: Int!
  oid: Int!
  username: String!
  mobile: String!
  email: String!
  createdAt: Int!
  avatar: String!
  verified: Boolean!
  isAdmin: Boolean!
}

"""
Information about pagination in a connection.
"""
type PageInfo {
  """
  When paginating forwards, are there more items?
  """
  hasNextPage: Boolean!

  """
  When paginating backwards, are there more items?
  """
  hasPreviousPage: Boolean!

  """
  When paginating backwards, the cursor to continue.
  """
  startCursor: String

  """
  When paginating forwards, the cursor to continue.
  """
  endCursor: String
}

"""
Root query object
"""
type Query {
  viewer: User
  ticket(
    """
    Ticket ID
    """
    tid: String!
  ): Ticket
  tickets(
    """
    Ticket Owner User ID
    """
    uid: Int

    """
    Ticket Open Status
    """
    status: TicketStatus

    """
    Ticket Type
    """
    type: TicketNotify

    """
    Ticket Reply Status
    """
    reply: Boolean
    after: String
    first: Int
    before: String
    last: Int
  ): TicketsConnection
}

"""
A connection to a list of items.
"""
type RepliesConnection {
  """
  Information to aid in pagination.
  """
  pageInfo: PageInfo!

  """
  A list of edges.
  """
  edges: [RepliesEdge]

  """
  A count of the total number of objects in this connection, ignoring pagination.
  """
  totalCount: Int

  """
  A list of all of the objects returned in the connection.
  """
  replies: [TicketReply]
}

"""
An edge in a connection.
"""
type RepliesEdge {
  """
  The item at the end of the edge
  """
  node: TicketReply

  """
  A cursor for use in pagination
  """
  cursor: String!
}

"""
Input reply payload
"""
input ReplyCreateInput {
  """
  Ticket ID
  """
  tid: String!

  """
  Reply Content
  """
  body: String!
}

"""
Input reply payload
"""
input ReplyUpdateInput {
  """
  Ticket ID
  """
  tid: String!

  """
  Reply Content
  """
  body: String!

  """
  Reply createdAt
  """
  createdAt: Int!
}

type Ticket implements Node {
  """
  The ID of an object
  """
  id: ID!
  tid: String!
  uid: Int!
  status: TicketStatus!
  reply: Boolean!
  type: String!
  notify: TicketNotify!
  title: String!
  body: String!
  createdAt: Int!
  updatedAt: Int!
  replies(
    after: String
    first: Int
    before: String
    last: Int
  ): RepliesConnection
  owner: Owner
}

"""
Input ticket payload
"""
input TicketCreateInput {
  """
  Ticket Type
  """
  type: String!

  """
  Ticket Notification Type
  """
  notify: TicketNotify!

  """
  Ticket Title
  """
  title: String!

  """
  Ticket Content
  """
  body: String!
}

enum TicketNotify {
  mobile
  email
  both
  none
}

type TicketReply implements Node {
  """
  The ID of an object
  """
  id: ID!
  tid: String!
  uid: Int!
  body: String!
  createdAt: Int!
  updatedAt: Int!
  owner: Owner
}

"""
A connection to a list of items.
"""
type TicketsConnection {
  """
  Information to aid in pagination.
  """
  pageInfo: PageInfo!

  """
  A list of edges.
  """
  edges: [TicketsEdge]

  """
  A count of the total number of objects in this connection, ignoring pagination.
  """
  totalCount: Int

  """
  A list of all of the objects returned in the connection.
  """
  tickets: [Ticket]
}

"""
An edge in a connection.
"""
type TicketsEdge {
  """
  The item at the end of the edge
  """
  node: Ticket

  """
  A cursor for use in pagination
  """
  cursor: String!
}

enum TicketStatus {
  open
  closed
}

"""
Input ticket payload
"""
input TicketUpdateInput {
  """
  TicketID
  """
  tid: String!

  """
  Ticket Open Status
  """
  status: TicketStatus

  """
  Ticket Type
  """
  type: String

  """
  Ticket Notify Status
  """
  notify: TicketNotify

  """
  Ticket Title
  """
  title: String

  """
  Ticket Body
  """
  body: String
}

type User implements Node {
  """
  The ID of an object
  """
  id: ID!
  uid: Int!
  oid: Int!
  username: String!
  mobile: String!
  email: String!
  createdAt: Int!
  avatar: String!
  verified: Boolean!
  isAdmin: Boolean!
  tickets(
    """
    Ticket Owner User ID
    """
    uid: Int

    """
    Ticket Open Status
    """
    status: TicketStatus

    """
    Ticket Type
    """
    type: TicketNotify

    """
    Ticket Reply Status
    """
    reply: Boolean
    after: String
    first: Int
    before: String
    last: Int
  ): TicketsConnection
}

权限设置

Query 部分：

• Viewer
  • 用户查询自己的信息
  • 查询自己的工单
• Ticket
  • 管理员查询所有工单
  • 用户查询自己的工单
  • 查询工单回复
• Tickets
  • 用户无权限，仅限管理员查询所有工单

Mutation 部分：

• 创建工单
• 更新工单：用户操作自己的，管理员操作（关闭、重新打开）所有
• 添加回复
• 更新回复

{
  Query: {
    viewer: {
      // 用户（管理员）查询自己的
      tickets: {
        // 用户查询自己的工单
      }
    },
    ticket: {
      // 用户查询自己的，管理员查询所有
      replies: {

      }
    },
    tickets: {
      // 用户无权限，管理员查询所有
      // 用户查询自己的工单从 viewer 下进行
    }
  },
  Mutation: {
    addTicket: '用户',
    updateTicket: '用户操作自己的，管理员操作（关闭、重新打开）所有',
    addReply: '用户',
    updateReply: '用户（管理员）操作自己的'
  }
}

代码实现

在 Root 中进行鉴权。

Query 部分

const {
  GraphQLObjectType, GraphQLNonNull, GraphQLString
} = require('graphql');
const { type: UserType } = require('./types/user');
const { type: TicketType, args: TicketArgs } = require('./types/ticket');
const connection = require('./interfaces/connection');
const { getObject } = require('./loaders');

module.exports = new GraphQLObjectType({
  name: 'Query',
  description: 'Root query object',
  fields: {
    viewer: {
      type: UserType,
      resolve: (_, args, ctx) => {
        const { uid } = ctx.session;
        return getObject({ type: 'user', id: uid });
      }
    },
    ticket: {
      type: TicketType,
      args: {
        tid: {
          description: 'Ticket ID',
          type: new GraphQLNonNull(GraphQLString)
        }
      },
      resolve: (_, args, ctx) => getObject({ id: args.tid, type: 'ticket' }).then((data) => {
        const { uid } = ctx.session;
        // TODO: Admin Auth Check
        // data.uid !== uid && user is not admin
        if (data.uid !== uid) {
          return null;
        }
        return data;
      })
    },
    tickets: connection('Tickets', TicketType, TicketArgs)
  }
});

权限的校验在此处进行。可以通过用户 uid 判断是否为自己的工单，也可以在此处去做管理员的校验。

Mutation 部分

const { GraphQLObjectType } = require('graphql');
const { type: TicketType, input: TicketInputArgs, inputOperation: TicketUpdateInputArgs } = require('./types/ticket');
const { type: ReplyType, input: ReplyInputArgs, inputUpdate: ReplyUpdateInputArgs } = require('./types/reply');
const { TicketCreate, TicketUpdate } = require('./mutations/ticket');
const { ReplyCreate, ReplyUpdate } = require('./mutations/reply');

module.exports = new GraphQLObjectType({
  name: 'Mutation',
  description: 'Root mutation object',
  fields: {
    createTicket: {
      type: TicketType,
      args: TicketInputArgs,
      resolve: (_, { input }, ctx) => {
        const { uid } = ctx.session;
        return TicketCreate(uid, input);
      }
    },
    updateTicket: {
      type: TicketType,
      args: TicketUpdateInputArgs,
      resolve: (_, { input }, ctx) => {
        const { uid } = ctx.session;
        const { tid, ...args } = input;
        return TicketUpdate(tid, args, uid);
      }
    },
    createReply: {
      type: ReplyType,
      args: ReplyInputArgs,
      resolve: (_, { input }, ctx) => {
        const { uid } = ctx.session;
        return ReplyCreate(uid, input);
      }
    },
    updateReply: {
      type: ReplyType,
      args: ReplyUpdateInputArgs,
      resolve: (_, { input }, ctx) => {
        const { uid } = ctx.session;
        return ReplyUpdate(uid, input);
      }
    }
  }
});

Mutation 中不需要进行用户的 UID 校验了，因为有 Session 的校验在前面了。

DataLoader 引入查询

DataLoader 中文文档翻译： https://dataloader.js.cool/

const DataLoader = require("dataloader");
const { query, format } = require("../db");
const { CountLoader } = require("./connection");

const TICKETTABLE = "xibang.d_ticket";

/**
 * TicketLoader
 * ref: UserLoader
 */
exports.TicketLoader = new DataLoader((tids) => {
  const sql = format("SELECT * FROM ?? WHERE tid in (?)", [TICKETTABLE, tids]);
  return query(sql).then((rows) =>
    tids.map(
      (tid) =>
        rows.find((row) => row.tid === tid) ||
        new Error(`Row not found: ${tid}`)
    )
  );
});

/**
 * TicketsLoader
 * Each arg:
 * {  time: {before, after}, // Int, Int
 *    where, // obj: {1:1, type:'xxx'}
 *    order, // 'DESC' / 'ASC'
 *    limit // Int
 * }
 */
exports.TicketsLoader = new DataLoader((args) => {
  const result = args.map(
    ({ time: { before, after }, where, order, limit }) => {
      let time = [];
      if (before) {
        time.push(format("createdAt > ?", [before]));
      }
      if (after) {
        time.push(format("createdAt < ?", [after]));
      }
      if (time.length > 0) {
        time = ` AND ${time.join(" AND ")}`;
      } else {
        time = "";
      }
      let sql;
      if (where) {
        sql = format(
          `SELECT * from ?? WHERE ?${time} ORDER BY createdAt ${order} LIMIT ?`,
          [TICKETTABLE, where, limit]
        );
      } else {
        sql = format(
          `SELECT * from ?? WHERE 1=1${time} ORDER BY createdAt ${order} LIMIT ?`,
          [TICKETTABLE, limit]
        );
      }
      return query(sql);
    }
  );
  return Promise.all(result);
});

/**
 * TicketsCountLoader
 * @param {obj} where where args
 * @return {DataLoader} CountLoader
 */
exports.TicketsCounter = (where) => CountLoader.load([TICKETTABLE, where]);

Facebook 的 Dataloader 框架可以帮助代码中减少查询次数，提升查询的效率。

GraphQL Edge 分页实现

使用 Cursor 分页，由于 MySQL 不支持 Cursor 游标，所以通过代码来实现。

const { parseArgs, fromConnectionCursor, toConnectionCursor } = require('../lib');
const { TicketsLoader } = require('./ticket');
const { RepliesLoader } = require('./reply');

/**
 * Switch DataLoader by Type
 * @param {string} type Ticket or TicketReply
 * @returns {function} DataLoader
 */
const TypeLoader = (type) => {
  if (type === 'Ticket') {
    return TicketsLoader;
  }
  return RepliesLoader;
};

/**
 * Filter Limit Args
 * @param {string} arg first or last
 * @param {int} v value
 * @returns {int} limit or undefined
 */
const filterLimitArg = (arg, v) => {
  if (typeof v === 'number') {
    if (v < 0) {
      throw new Error(`Argument "${arg}" must be a non-negative integer`);
    } else if (v > 1000) {
      return 1000;
    }
    return v;
  }
  return undefined;
};

/**
 * Connection Edges Loader
 * @param {string} type Type Name
 * @param {obj} args Args like: {first: 10, after: "xxx"}
 * @param {int} totalCount totalCount
 * @param {obj} obj parent node object
 * @returns {Promise} {edges, pageInfo: {startCursor, endCursor, hasNextPage, hasPreviousPage}}
 */
exports.NodesLoader = (type, args, totalCount, obj = {}) => {
  // 分页查询 limit 字段
  let { first, last } = args;
  first = filterLimitArg('first', first);
  last = filterLimitArg('last', last);
  const [limit, order] = last === undefined ? [first, 'DESC'] : [last, 'ASC'];

  // 删除查询参数中的 first, last, before, after 无关条件
  // 保留剩余的，如 { type: 'issue' }
  const { after, before } = args;
  let where = parseArgs(args);
  if (type === 'Ticket') {
    if (obj.uid) {
      where.uid = obj.uid;
    }
  } else {
    where = {
      tid: obj.tid
    };
  }

  // 从 before, after 中获取 createdAt 和 index
  const [beforeTime, beforeIndex = totalCount] = fromConnectionCursor(before);
  const [afterTime, afterIndex = -1] = fromConnectionCursor(after);

  const loader = TypeLoader(type);
  return loader.load({
    time: {
      before: beforeTime,
      after: afterTime
    },
    where,
    order,
    limit
  }).then((nodes) => {
    const edges = nodes.map((v, i) => ({
      cursor: toConnectionCursor(v.createdAt, order === 'DESC' ? (afterIndex + i + 1) : (totalCount - beforeIndex - i - 1)),
      node: v
    }));
    const firstEdge = edges[0];
    const lastEdge = edges[edges.length - 1];

    return {
      edges,
      totalCount,
      pageInfo: {
        startCursor: firstEdge ? firstEdge.cursor : null,
        endCursor: lastEdge ? lastEdge.cursor : null,
        hasPreviousPage:
          typeof last === 'number' ? (totalCount - beforeIndex - limit) > 0 : false,
        hasNextPage:
          typeof first === 'number' ? (afterIndex + limit) < totalCount : false
      }
    };
  });
};

需要注意一下：cursor 是 base64 编码的。

OAuth 鉴权

const { getAccessToken } = require('./model');

const e403 = (ctx) => {
  // 失败
  ctx.status = 403;
  ctx.body = {
    data: {},
    errors: [{
      message: 'You need signin first.',
      type: 'FORBIDDEN'
    }]
  };
};

module.exports = () => (ctx, next) => {
  const { access_token: accessTokenQuery = '' } = ctx.query;
  const { authorization = '' } = ctx.header;
  const accessToken = authorization.startsWith('Bearer ') ? authorization.replace('Bearer ', '') : accessTokenQuery;

  if (accessToken === '') {
    return e403(ctx);
  }
  // 检查 Token 合法性
  return getAccessToken(accessToken)
    .then((data) => {
      if (!data) {
        return e403(ctx);
      }
      ctx.session = data.user;
      return next();
    });
};

这部分比较简单，可以通过 Query 或者 Header 传递鉴权信息。

---

该项目完整实现代码下载： https://download.csdn.net/download/jslygwx/88188235