dbBackup.service.ts 3.4 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283
  1. import { Config, Provide } from '@midwayjs/core';
  2. import { InjectDataSource, InjectEntityModel } from '@midwayjs/typeorm';
  3. import { DataSource, Repository, getCustomRepository, getManager } from 'typeorm';
  4. import { get, head, upperFirst } from 'lodash';
  5. import * as fs from 'fs';
  6. import * as path from 'path';
  7. @Provide()
  8. export class DbBackupService {
  9. @InjectDataSource('default')
  10. defaultDataSource: DataSource;
  11. @Config('PathConfig.path')
  12. filePath;
  13. dbBackupDir = 'dbBackUp';
  14. async getTables() {
  15. console.log('to dump');
  16. const entitys = this.defaultDataSource.entityMetadatas;
  17. const dbBackUpPath = path.resolve(this.filePath, this.dbBackupDir);
  18. const res = fs.existsSync(dbBackUpPath);
  19. if (!res) {
  20. fs.mkdirSync(dbBackUpPath);
  21. }
  22. for (const e of entitys) {
  23. const tableNames = e.givenTableName;
  24. const dataSql = `SELECT * from "public"."${tableNames}" ORDER BY "id" ;`;
  25. const dataRes = await this.defaultDataSource.query(dataSql);
  26. const fileFullPath = path.resolve(dbBackUpPath, `${tableNames}.json`);
  27. fs.writeFileSync(fileFullPath, JSON.stringify(dataRes), 'utf-8');
  28. }
  29. console.log('dump finish');
  30. }
  31. /**
  32. * 根据表名,找entity
  33. * 有entity,通过数据源创建createQueryBuilder.对数据进行批量添加
  34. * @param {string} dirPath 文件夹路径,没有就默认使用备份路径
  35. */
  36. async restore(dirPath?: string) {
  37. console.log('to restore');
  38. if (!dirPath) dirPath = path.resolve(this.filePath, this.dbBackupDir);
  39. const dbBackUpPathDir = dirPath;
  40. let dir = fs.readdirSync(dbBackUpPathDir);
  41. dir = dir.map(i => {
  42. const arr = i.split('.');
  43. return head(arr);
  44. });
  45. for (const tableName of dir) {
  46. // 清空表, 重置id;
  47. console.log(tableName);
  48. const filePath = path.resolve(dbBackUpPathDir, `${tableName}.json`);
  49. let datas = fs.readFileSync(filePath, 'utf-8');
  50. if (datas) datas = JSON.parse(datas);
  51. console.log(`${tableName} start`);
  52. await this.defaultDataSource.query(`TRUNCATE TABLE "public"."${tableName}" RESTART IDENTITY RESTRICT;`);
  53. await this.defaultDataSource.query(`SELECT setval('"${tableName}_id_seq"', (SELECT max(id) FROM "${tableName}"));`);
  54. const entities: any = this.defaultDataSource.options.entities;
  55. const entity = entities.find(f => get(f, 'name') === `${upperFirst(tableName)}`);
  56. for (const i of datas) {
  57. try {
  58. // 需要处理id的问题
  59. const data_id = get(i, 'id', 1);
  60. // 获取当前表id的序列
  61. const lastIdResult = await this.defaultDataSource.query(`SELECT max(id) FROM "${tableName}"`);
  62. let last_id = get(head(lastIdResult), 'max', 0);
  63. // 判断same_id: 表中最后一个id和当前数据id是否一致
  64. let same_id = last_id === data_id;
  65. if (!same_id && data_id > 1) {
  66. // 不一致:只有一种情况----当前数据的id比表中的数据id要大.那就直接执行id补上
  67. await this.defaultDataSource.query(`SELECT setval('"${tableName}_id_seq"', ${data_id - 1});`);
  68. }
  69. const builder = await this.defaultDataSource.manager.createQueryBuilder().insert().into(entity);
  70. await builder.values(i).execute();
  71. } catch (error) {
  72. console.log(`${tableName} - ${get(i, 'id')} - 发生错误`);
  73. console.log(error);
  74. }
  75. }
  76. }
  77. console.log('restore finish');
  78. }
  79. }