Deer/commons/basic/src/main/ets/rdb/utils/RelationalStoreUtis.ts

import { relationalStore } from '@kit.ArkData';
import { Context } from '@ohos.abilityAccessCtrl';
import { BusinessError } from '@kit.BasicServicesKit';
import { rDbSql } from '../sqls/RDBSql';


export interface CloudLoadItem {
  uid: string,
  date: string,
  jsonDataPath: string,
  lastRequestTime: number
}


export class RelationalStoreUtils {
  private static cloudLoadStore: relationalStore.RdbStore | undefined = undefined;

  //初始化数据库
  static initReplayCloudStore(context: Context, success?: () => void, isDeBug: boolean = false) {
    let securityLevel: relationalStore.SecurityLevel = relationalStore.SecurityLevel.S3
    // 在 debug模式下，数据库安全级别为S1。 确保数据库可视化工具可以正常访问
    if (isDeBug) {
      securityLevel = relationalStore.SecurityLevel.S1
    }

    const REPLAY_CLOUD_STORE_CONFIG: relationalStore.StoreConfig = {
      name: 'YT_HM.db', // 数据库文件名
      securityLevel: securityLevel, // 数据库安全级别
      encrypt: false, // 可选参数，指定数据库是否加密，默认不加密
      customDir: 'customDir/subCustomDir', // 可选参数，数据库自定义路径。数据库将在如下的目录结构中被创建：context.databaseDir + '/rdb/' + customDir，其中context.databaseDir是应用沙箱对应的路径，'/rdb/'表示创建的是关系型数据库，customDir表示自定义的路径。当此参数不填时，默认在本应用沙箱目录下创建RdbStore实例。
      isReadOnly: false // 可选参数，指定数据库是否以只读方式打开。该参数默认为false，表示数据库可读可写。该参数为true时，只允许从数据库读取数据，不允许对数据库进行写操作，否则会返回错误码801。
    };

    // 假设当前数据库版本为3，表结构：EMPLOYEE (NAME, AGE, SALARY, CODES, IDENTITY)

    relationalStore.getRdbStore(context, REPLAY_CLOUD_STORE_CONFIG, (err, store) => {
      if (err) {
        console.error(`Failed to get RdbStore. Code:${err.code}, message:${err.message}`);
        return;
      }
      // 当数据库创建时，数据库默认版本为0
      if (store.version === 0) {

        try {
          Object.keys(rDbSql).forEach((key) => {
            store.executeSync(rDbSql[key])
          })
        } catch (error) {
          console.error('Failed to create table:', error);
        }
        // 设置数据库的版本，入参为大于0的整数
        store.version = 3;
      }
      RelationalStoreUtils.cloudLoadStore = store;
      success?.()
    })
  }


  static getStore() {
    return RelationalStoreUtils.cloudLoadStore;
  }


  /**
   * 插入数据 --id不用传，数据库自增
   * @param table 对呀的哪张表
   * @param param
   * @param success
   */
  static insert<T>(table: new () => T, param: ESObject, success?: (id: number) => void) {
    // 添加参数校验
    if (!table || !table.name) {
      console.error('Invalid table constructor provided');
      return;
    }

    const insertParam: relationalStore.ValuesBucket = {};
    if (param) {
      Object.keys(param).forEach((key) => {
        insertParam[key] = param[key];
      })
    }

    if (RelationalStoreUtils.cloudLoadStore !== undefined) {
      (RelationalStoreUtils.cloudLoadStore as relationalStore.RdbStore).insert(table.name, insertParam,
        (err: BusinessError, rowId: number) => {
          if (err) {
            console.error(`Failed to insert data. Code:${err.code}, message:${err.message}`);
            return;
          }
          success?.(rowId);
          console.info(`Succeeded in inserting data. rowId:${rowId}`);
        })
    }
  }


  /**
   * 插入数据 --id不用传，数据库自增
   * @param table 对呀的哪张表
   * @param param
   * @param success  返回插入后的id
   */
  static insertSync<T>(table: new () => T, param: ESObject) {
    let id = -1
    // 添加参数校验
    if (!table || !table.name) {
      console.error('Invalid table constructor provided');
      return;
    }

    const insertParam: relationalStore.ValuesBucket = {};
    if (param) {
      Object.keys(param).forEach((key) => {
        insertParam[key] = param[key];
      })
    }

    if (RelationalStoreUtils.cloudLoadStore !== undefined) {
    id =   (RelationalStoreUtils.cloudLoadStore as relationalStore.RdbStore).insertSync(table.name, insertParam)
    }
    return id
  }


  /**
   * 根据id更新数据
   * @param tableName
   * @param param
   * @param success
   */
  static updateItemById<T>(table: new () => T, param: ESObject, success?: () => void) {
    const updateParam: relationalStore.ValuesBucket = {};
    Object.keys(param).forEach((key) => {
      updateParam[key] = param[key];
    })

    let predicates = new relationalStore.RdbPredicates(table.name);
    predicates = predicates.equalTo("id", updateParam.id)

    if (RelationalStoreUtils.cloudLoadStore !== undefined) {
      (RelationalStoreUtils.cloudLoadStore as relationalStore.RdbStore).update(updateParam, predicates,
        (err: BusinessError, rows: number) => {
          if (err) {
            console.error(`Updated failed, code is ${err.code},message is ${err.message}`);
            return;
          }
          success?.();
          console.info(`Updated row count: ${rows}`);
        })
    }
  }


  /**
   * 根据id更新数据
   * @param tableName
   * @param param
   * @param success
   */
  static updateItemByIdSync<T>(table: new () => T, param: ESObject) {
    const updateParam: relationalStore.ValuesBucket = {};
    Object.keys(param).forEach((key) => {
      updateParam[key] = param[key];
    })

    let predicates = new relationalStore.RdbPredicates(table.name);
    predicates = predicates.equalTo("id", updateParam.id)

    if (RelationalStoreUtils.cloudLoadStore !== undefined) {
      (RelationalStoreUtils.cloudLoadStore as relationalStore.RdbStore).updateSync(updateParam, predicates)
    }
  }


  /**
   * 查询列表
   * @param tableName
   * @param queryParam
   * @returns
   */
  static getListSync<T = ESObject>(table: new () => T, queryParam?: ESObject): T[] {
    const list: T[] = []
    let predicates = new relationalStore.RdbPredicates(table.name);
    let keys: string[] = []
    if (queryParam) {
      keys = Object.keys(queryParam);
    }
    if (RelationalStoreUtils.cloudLoadStore != undefined) {
      // 正确拼接查询条件
      // if (keys.length > 0) {
      //   predicates = predicates.equalTo(keys[0], queryParam[keys[0]]);
      //   for (let i = 1; i < keys.length; i++) {
      //     predicates = predicates.and().equalTo(keys[i], queryParam[keys[i]]);
      //   }
      // }
      //朝毅高级写法
      if (keys.length > 0) {
        predicates = keys.slice(1).reduce(
          (pred, key) => pred.and().equalTo(key, queryParam[key]),
          predicates.equalTo(keys[0], queryParam[keys[0]])
        );
      }
      const resultSet = (RelationalStoreUtils.cloudLoadStore as relationalStore.RdbStore).querySync(predicates)

      // 获取列名信息
      const columnNames = resultSet.columnNames

      while (resultSet.goToNextRow()) {
        const item: ESObject = {}
        columnNames.forEach((columnName) => {
          // 根据列类型获取值
          const columnIndex = resultSet.getColumnIndex(columnName);
          // 可以根据需要处理不同数据类型
          item[columnName] = resultSet.getValue(columnIndex);
        })
        list.push(item as T);
      }

      resultSet.close();
    }
    return list;
  }


  /**
   * 查询单个数据
   * @param tableName
   * @param queryParam
   * @returns
   */
  static getItemByIdSync<T = ESObject>(table: new () => T, queryParam: ESObject) {
    const keys = Object.keys(queryParam);
    const item: ESObject = {}
    let predicates = new relationalStore.RdbPredicates(table.name);

    if (RelationalStoreUtils.cloudLoadStore != undefined) {
      //高级写法
      if (keys.length > 0) {
        predicates = keys.slice(1).reduce(
          (pred, key) => pred.and().equalTo(key, queryParam[key]),
          predicates.equalTo(keys[0], queryParam[keys[0]])
        );
      }
      const resultSet = (RelationalStoreUtils.cloudLoadStore as relationalStore.RdbStore).querySync(predicates)
      // 获取列名信息
      const columnNames = resultSet.columnNames
      // resultSet是一个数据集合的游标，默认指向第-1个记录，有效的数据从0开始。
      while (resultSet.goToNextRow()) {
        columnNames.forEach((columnName) => {
          // 根据列类型获取值
          const columnIndex = resultSet.getColumnIndex(columnName);
          // 可以根据需要处理不同数据类型
          item[columnName] = resultSet.getValue(columnIndex);
        })
        break
      }
      // 释放数据集的内存，若不释放可能会引起fd泄露与内存泄露
      resultSet.close();
    }
    return item as T;
  }


  /**
   * 根据id删除数据
   * @param tableName
   * @param queryParam
   * @param success
   */
  static deleteItemById<T>(table: new () => T, id: number, success?: () => void) {

    let predicates = new relationalStore.RdbPredicates(table.name);
    predicates = predicates.equalTo("id", id)

    if (RelationalStoreUtils.cloudLoadStore !== undefined) {
      (RelationalStoreUtils.cloudLoadStore as relationalStore.RdbStore).delete(predicates,
        (err: BusinessError, flag: number) => {
          if (err) {
            console.error(`Updated failed, code is ${err.code},message is ${err.message}`);
            return;
          }
          if (flag != 0) {
            success?.()
          }
        })
    }
  }

  /**
   * 根据id删除数据
   * @param tableName
   * @param queryParam
   * @param success
   */
  static deleteItemByIdSync<T>(table: new () => T, id: number) {

    let predicates = new relationalStore.RdbPredicates(table.name);
    predicates = predicates.equalTo("id", id)

    if (RelationalStoreUtils.cloudLoadStore !== undefined) {
      const flag = (RelationalStoreUtils.cloudLoadStore as relationalStore.RdbStore).deleteSync(predicates)
      if (flag != 0) {
        return true
      }
    }
    return false
  }
}