6.数据库编码规范

myddd-electron使用SQLite3数据库，为简化相关开发，封装了SQLite3规范，以便快速入手

申明表

提供了一个ITable的接口，包含以下两个方法

import { Table } from './Table';

/**
 * 仓储接口协议
 */
export interface ITable {

    /**
     * 创建表的语句
     */
    createTable():Table;

    /**
     * 更新表的语句
     * @param from 从哪个版本 
     * @param to 到哪个版本
     */
    updateTable(from:number,to:number):string[];
}

需要建立的表，新建一个类，实现以上接口

参考. MessageTable.ts

export class MessageTable implements ITable{
    public createTable(): Table {
        const messageTable = new Table("message_") //表名
        .addPrimaryColumn('deliveryId') //消息ID，主键
        .addIndexColumn('sessionId') //会话ID，索引
        .addIndexColumn('deliveryTime',ColumnType.Integer) //消息时间，索引 
        .addColumn('messageType') //消息类型
        .addColumn('fromId') //消息发送ID
        .addColumn('fromDomain') //消息发送域
        .addColumn('fromType',ColumnType.Integer) //发送者类型
        .addColumn('toId') //接收ID
        .addColumn('toDomain') //接收域
        .addColumn('toType',ColumnType.Integer) //接收类型
        .addColumn('metadata') //元数据，my_name,my_avatar类似
        .addColumn('body'); //消息body中的数据
        return messageTable;
    }   
    
    public updateTable(from: number, to: number): string[] {
        return [];
    }
  
}

将表注册进数据库

export class BaseRepository {

    private static DB_NAME = 'workplus.data';

    private static DB_VERSION = 1;

    private static instance:BaseRepository;

    private repository:Repository;

    private tables:ITable[] = [];

    private constructor(){
        //添加会话表
        this.tables.push(new SessionTable());
        //添加消息表
        this.tables.push(new MessageTable());
        
        this.repository = new Repository(BaseRepository.DB_NAME,this.tables,BaseRepository.DB_VERSION);
    }
}

在BaseRepository中将表注册进去

仓储实现实现

@injectable()
export class BlogRepository {
  private repository: Repository;

  constructor(@inject(Repository) repository: Repository) {
    this.repository = repository;
  }

  async batchSaveBlogs(blogs: Blog[]): Promise<boolean> {
    const sqls: string[] = [];
    const params: any[] = [];
    blogs.forEach((blog) => {
      const tags: string[] = [];
      blog.tags.forEach((tag) => {
        tags.push(tag.name);
      });
      sqls.push(
        "replace into blog_ (slug,title,brief,created,date,tagData) values ($slug,$title,$brief,$created,$date,$tagData)"
      );
      params.push({
        $slug: blog.slug,
        $title: blog.title,
        $brief: blog.brief,
        $created: blog.created,
        $date: blog.date,
        $tagData: tags.join(","),
      });

      blog.tags.forEach((tag) => {
        sqls.push("replace into tag_ (name) values ($name)");
        params.push({
          $name: tag,
        });
      });
    });
    return this.repository.executeBatchUpdate(sqls, params);
  }

  async fetchMaxBlogDate(): Promise<number> {
    const sql = "select max(created) as _created from blog_";
    const response = await this.repository.executeSingleQuery<IBlog>(sql);
    if (response._created == null) return 0;
    return response._created;
  }

  async fetchLatestBlogs(): Promise<Blog[]> {
    const sql = "select * from blog_ order by created desc";
    const results: IBlog[] = await this.repository.executeQuery<IBlog>(sql);

    const blogs: Blog[] = [];
    results.forEach((blog) => {
      const tags: string[] = blog.tagData.split(",");
      blog.tags = tags;
      blogs.push(new Blog(blog));
    });
    return blogs;
  }
}

封装的接口及API说明

相关接口封装仍在完善中

export interface IRepository {

    /**
     * 执行一条查询，返回数组或空
     * @param sql 
     * @param params 
     */
    executeQuery<T>(sql:string,params:any):Promise<T[] | null >;

      /**
     * 执行一条查询，返回单个对像或空
     * @param sql 
     * @param params 
     */
    executeSingleQuery<T>(sql:string,params:any):Promise<T | null >;

    /**
     * 执行一条数据库更新操作，返回是否执行成功
     * @param sql 
     * @param params 
     */
    executeUpdate(sql:string,params:any):Promise<boolean>;

    /**
     * 执行一个事务内的批量数据库操作
     * @param sqls 批量执行的数组
     * @param params 批量参数组
     */
    executeBatchUpdate(sqls:string[],params:any[]):Promise<boolean>;
}