目录

如何使用公共目录下的数据库文件

问题现象

用户在公共目录下载保存了一个数据库文件,在应用内应该如何使用该数据库文件?

背景知识

解决方案

需要使用公共目录下的数据库文件,主要有以下两种解决方案:

本文主要介绍如何直接使用公共目录下的数据库文件。

  1. 使用documentViewPicker.select()选择公共目录下的数据库文件。
    • 关系型数据库至少需要.db、.db-shm、.db-wal三个文件,因此此处需要引导用户选择对应的这三个文件。
    • 该接口获取的URI权限是临时只读权限,待退出应用后台后,获取的临时权限就会失效。
    selectFile() {
      try {
        let documentSelectOptions = new picker.DocumentSelectOptions();
        documentSelectOptions.maxSelectNumber = 3;
        documentSelectOptions.fileSuffixFilters = ['.db', '.db-shm', '.db-wal'];
        documentSelectOptions.selectMode = picker.DocumentSelectMode.MIXED;
        let documentPicker = new picker.DocumentViewPicker(this.context);
        documentPicker.select(documentSelectOptions).then((result: Array<string>) => {
          console.info(`DocumentViewPicker.select successfully, result uri: ${JSON.stringify(result)}`);
          if (!result || result.length < 1) {
            console.warn(`未选择文件`);
            this.promptAction.showToast({ message: '请选择对应db、db-shm、db-wal文件' });
            return;
          }
          if (result.length < 3) {
            console.warn(`请选择必须的db、db-shm、db-wal文件`);
            this.promptAction.showToast({ message: '请选择对应db、db-shm、db-wal文件' });
            return;
          }
          // 持久化授权:如果是 /storage/Users/currentUser/Download/应用包名 目录下的文件无需持久化授权即可使用
          this.persistPermission(result);
          // 获取其中的db文件名、rootDir、customDir
          this.resolveDbMessage(result);
          this.promptAction.showToast({ message: `选择成功!` });
        }).catch((err: BusinessError) => {
          console.error(`documentPicker select failed, code is ${err.code},message is ${err.message}`);
        });
      } catch (e) {
        let err = e as BusinessError;
        console.error(`selectFile failed, code is ${err.code},message is ${err.message}`);
      }
    }
  2. 使用fileShare.persistPermission()对上述选择的文件URI进行持久化授权,需在module.json5文件中配置ohos.permission.FILE_ACCESS_PERSIST权限。数据库只读场景至少需要有.db、.db-shm、.db-wal这三个文件的可读权限。
    • 持久化授权需要ohos.permission.FILE_ACCESS_PERSIST权限,该权限属于ACL受限权限,需要参考申请受限权限进行申请。
    • 持久化授权的数据存储在系统的数据库中,应用或者设备重启后需要使用fileShare.activatePermission()激活已持久化的授权才可以正常使用激活持久化授权。
    • 对于DOWNLOAD/应用包名对应的公共目录路径,应用无需使用持久化授权即可获得读取权限。
    {
      "name": "ohos.permission.FILE_ACCESS_PERSIST"
    },
    persistPermission(uris: Array<string>) {
      try {
        // 获取已授权uri,对未授权uri重新授权
        let policyUris = this.dataPreferences.getSync('policyUris', []) as Array<string>;
        if (policyUris && policyUris.length > 0) {
          uris.filter((uri) => {
            // 过滤已授权uri
            return !policyUris.includes(uri);
          });
        }
        let policies: Array<fileShare.PolicyInfo> = [];
        uris.forEach((uri: string) => {
          let policyInfo: fileShare.PolicyInfo = {
            uri: uri,
            operationMode: fileShare.OperationMode.READ_MODE,
          };
          policies.push(policyInfo);
        });
        // 持久化授权:需在module.json5文件中配置ohos.permission.FILE_ACCESS_PERSIST权限
        fileShare.persistPermission(policies).then(() => {
          console.info(`persistPermission successfully. policies: ${JSON.stringify(policies)}`);
          // 授权成功,保存已授权uri
          let policyUris = this.dataPreferences.getSync('policyUris', []) as Array<string>;
          if (policyUris) {
            policyUris.push(...uris);
          }
          this.dataPreferences.putSync('policyUris', policyUris);
          this.dataPreferences.flushSync();
        }).catch((err: BusinessError<Array<fileShare.PolicyErrorResult>>) => {
          console.error(`persistPermission failed with error message: ${err.message}, error code: ${err.code}`);
          if (err.code == 13900001 && err.data) {
            for (let i = 0; i < err.data.length; i++) {
              console.error(`error code : ${JSON.stringify(err.data[i].code)}`);
              console.error(`error uri : ${JSON.stringify(err.data[i].uri)}`);
              console.error(`error reason : ${JSON.stringify(err.data[i].message)}`);
            }
          }
        });
      } catch (error) {
        let err: BusinessError = error as BusinessError;
        console.error(`persistPermission failed with err, Error code: ${err.code}, message: ${err.message}`);
      }
    }
  3. 提取公共目录下对应的db文件名、rootDir、customDir,使用relationalStore.getRdbStore()以只读模式打开对应数据库,即可读取公共目录的数据库文件内容。
    initRdb() {
      try {
        /**
         * 直接使用公共目录下数据库文件至少需要.db、.db-shm、.db-wal,所以在实际开库使用前需要提前校验是否有这对应三个文件的可读权限
         */
        if (!this.checkUirPermission()) {
          console.warn(`校验未通过,请重新选择必要的db、db-shm、db-wal文件.`);
          return;
        }
        let dbConfig: relationalStore.StoreConfig = {
          name: this.dbName,
          securityLevel: relationalStore.SecurityLevel.S3,
          rootDir: this.rootDir,
          customDir: this.customDir
        };
        console.info(`dbName: ${this.dbName}, rootDir: ${this.rootDir}, customDir: ${this.customDir}`);
        // 1.获取公共目录下的数据库文件,使用只读模式打开
        relationalStore.getRdbStore(this.context, dbConfig).then((rdbStore: relationalStore.RdbStore) => {
          this.store = rdbStore;
          try {
            if (rdbStore != undefined) {
              // 监听sql错误信息
              rdbStore.on('sqliteErrorOccurred', exceptionMessage => {
                let sqliteCode = exceptionMessage.code;
                let sqliteMessage = exceptionMessage.message;
                let errSQL = exceptionMessage.sql;
                console.error(`error log is ${sqliteCode}, errMessage is ${sqliteMessage}, errSQL is ${errSQL}`);
              });
            }
          } catch (err) {
            let code = (err as BusinessError).code;
            let message = (err as BusinessError).message;
            console.error(`Register observer failed, code is ${code},message is ${message}`);
          }
          this.promptAction.showToast({ message: `数据库初始化成功.` });
          console.info('initRdb successfully.');
        }).catch((err: BusinessError) => {
          console.error(`initRdb failed, code is ${err.code},message is ${err.message}`);
        });
      } catch (err) {
        console.error(`initRdb failed, code is ${err.code},message is ${err.message}`);
      }
    }

完整示例参考如下,需在module.json5文件中配置ohos.permission.FILE_ACCESS_PERSIST权限(实际运行需提前将数据库对应的db文件、db-shm文件、db-wal文件预置在设备的公共目录下):

import { common } from '@kit.AbilityKit';
import { fileIo, fileShare, picker } from '@kit.CoreFileKit';
import { preferences, relationalStore } from '@kit.ArkData';
import { BusinessError } from '@kit.BasicServicesKit';

@Entry
@Component
struct Index {
  private rootDir: string = '';
  private customDir: string = '';
  private dbName: string = '';
  private tableName: string = 'testTable'; // 表名,根据实际修改
  private context: common.UIAbilityContext = this.getUIContext().getHostContext() as common.UIAbilityContext;
  private promptAction = this.getUIContext().getPromptAction();
  private store: relationalStore.RdbStore | undefined = undefined;
  private dataPreferences = preferences.getPreferencesSync(this.context, { name: 'test' });

  aboutToAppear(): void {
    try {
      // 重新激活持久化授权
      this.activatePermission();

      // 获取上次打开的公共目录
      this.rootDir = this.dataPreferences.getSync('rootDir', '') as string;
      this.customDir = this.dataPreferences.getSync('customDir', '') as string;
      this.dbName = this.dataPreferences.getSync('dbName', '') as string;
    } catch (error) {
      let err: BusinessError = error as BusinessError;
      console.error(`activatePermission failed with error message: ${err.message}, error code: ${err.code}`);
    }
  }

  activatePermission() {
    try {
      // 获取已授权uri,重新激活授权
      let policyUris = this.dataPreferences.getSync('policyUris', []) as Array<string>;
      if (policyUris && policyUris.length > 0) {
        let policies: Array<fileShare.PolicyInfo> = [];
        policyUris.forEach((uri: string) => {
          let policyInfo: fileShare.PolicyInfo = {
            uri: uri,
            operationMode: fileShare.OperationMode.READ_MODE,
          };
          policies.push(policyInfo);
        });
        // 重新激活持久化授权:需在module.json5文件中配置ohos.permission.FILE_ACCESS_PERSIST权限
        fileShare.activatePermission(policies).then(() => {
          console.info(`activatePermission successfully, policies: ${JSON.stringify(policies)}`);
        }).catch(async (err: BusinessError<Array<fileShare.PolicyErrorResult>>) => {
          console.error(`activatePermission failed with error message: ${err.message}, error code: ${err.code}`);
          if (err.code == 13900001 && err.data) {
            for (let i = 0; i < err.data.length; i++) {
              console.error(`error code : ${JSON.stringify(err.data[i].code)}`);
              console.error(`error uri : ${JSON.stringify(err.data[i].uri)}`);
              console.error(`error reason : ${JSON.stringify(err.data[i].message)}`);
              if (err.data[i].code == fileShare.PolicyErrorCode.PERMISSION_NOT_PERSISTED) {
                await fileShare.persistPermission(policies);
              }
            }
          }
        });
      }
    } catch (error) {
      let err: BusinessError = error as BusinessError;
      console.error(`activatePermission failed with error message: ${err.message}, error code: ${err.code}`);
    }
  }

  build() {
    Column({ space: 20 }) {
      Button('选择公共目录下的数据库文件')
        .onClick(() => {
          this.selectFile();
        });

      Button('初始化数据库实例')
        .onClick(() => {
          this.initRdb();
        });

      Button('查询数据')
        .onClick(() => {
          this.queryData();
        });
    }
    .justifyContent(FlexAlign.Center)
    .height('100%')
    .width('100%');
  }

  selectFile() {
    try {
      let documentSelectOptions = new picker.DocumentSelectOptions();
      documentSelectOptions.maxSelectNumber = 3;
      documentSelectOptions.fileSuffixFilters = ['.db', '.db-shm', '.db-wal'];
      documentSelectOptions.selectMode = picker.DocumentSelectMode.MIXED;
      let documentPicker = new picker.DocumentViewPicker(this.context);
      documentPicker.select(documentSelectOptions).then((result: Array<string>) => {
        console.info(`DocumentViewPicker.select successfully, result uri: ${JSON.stringify(result)}`);
        if (!result || result.length < 1) {
          console.warn(`未选择文件`);
          this.promptAction.showToast({ message: '请选择对应db、db-shm、db-wal文件' });
          return;
        }
        if (result.length < 3) {
          console.warn(`请选择必须的db、db-shm、db-wal文件`);
          this.promptAction.showToast({ message: '请选择对应db、db-shm、db-wal文件' });
          return;
        }
        // 持久化授权:如果是 /storage/Users/currentUser/Download/应用包名 目录下的文件无需持久化授权即可使用
        this.persistPermission(result);
        // 获取其中的db文件名、rootDir、customDir
        this.resolveDbMessage(result);
        this.promptAction.showToast({ message: `选择成功!` });
      }).catch((err: BusinessError) => {
        console.error(`documentPicker select failed, code is ${err.code},message is ${err.message}`);
      });
    } catch (e) {
      let err = e as BusinessError;
      console.error(`selectFile failed, code is ${err.code},message is ${err.message}`);
    }
  }

  resolveDbMessage(result: string[]) {
    try {
      result.forEach((uri: string) => {
        if (uri.endsWith('.db')) {
          // 从公共目录文件路径获取数据库文件名及父目录路径等必要参数
          this.dbName = uri.split('/').pop() || '';
          let file = fileIo.openSync(uri, fileIo.OpenMode.READ_ONLY);
          let parent = file.getParent();
          console.info(`parent: ${parent}`);
          fileIo.closeSync(file);
          if (parent.includes('/')) {
            this.rootDir = parent.substring(0, parent.lastIndexOf('/'));
          }
          this.customDir = parent.split('/').pop() || '';

          // 存储公共目录数据库必要参数,以便下次直接使用
          this.dataPreferences.putSync('dbName', this.dbName);
          this.dataPreferences.putSync('rootDir', this.rootDir);
          this.dataPreferences.putSync('customDir', this.customDir);
          this.dataPreferences.flushSync();
          console.info(`dbName: ${this.dbName}, rootDir: ${this.rootDir}, customDir: ${this.customDir}`);
        }
      });
    } catch (e) {
      let err = e as BusinessError;
      console.error(`resolveDbMessage failed, code is ${err.code},message is ${err.message}`);
    }
  }

  persistPermission(uris: Array<string>) {
    try {
      // 获取已授权uri,对未授权uri重新授权
      let policyUris = this.dataPreferences.getSync('policyUris', []) as Array<string>;
      if (policyUris && policyUris.length > 0) {
        uris.filter((uri) => {
          // 过滤已授权uri
          return !policyUris.includes(uri);
        });
      }
      let policies: Array<fileShare.PolicyInfo> = [];
      uris.forEach((uri: string) => {
        let policyInfo: fileShare.PolicyInfo = {
          uri: uri,
          operationMode: fileShare.OperationMode.READ_MODE,
        };
        policies.push(policyInfo);
      });
      // 持久化授权:需在module.json5文件中配置ohos.permission.FILE_ACCESS_PERSIST权限
      fileShare.persistPermission(policies).then(() => {
        console.info(`persistPermission successfully. policies: ${JSON.stringify(policies)}`);
        // 授权成功,保存已授权uri
        let policyUris = this.dataPreferences.getSync('policyUris', []) as Array<string>;
        if (policyUris) {
          policyUris.push(...uris);
        }
        this.dataPreferences.putSync('policyUris', policyUris);
        this.dataPreferences.flushSync();
      }).catch((err: BusinessError<Array<fileShare.PolicyErrorResult>>) => {
        console.error(`persistPermission failed with error message: ${err.message}, error code: ${err.code}`);
        if (err.code == 13900001 && err.data) {
          for (let i = 0; i < err.data.length; i++) {
            console.error(`error code : ${JSON.stringify(err.data[i].code)}`);
            console.error(`error uri : ${JSON.stringify(err.data[i].uri)}`);
            console.error(`error reason : ${JSON.stringify(err.data[i].message)}`);
          }
        }
      });
    } catch (error) {
      let err: BusinessError = error as BusinessError;
      console.error(`persistPermission failed with err, Error code: ${err.code}, message: ${err.message}`);
    }
  }

  checkUirPermission() {
    try {
      if (!this.dbName) {
        this.promptAction.showToast({ message: '请选择必须的db、db-shm、db-wal文件' });
        console.warn(`请选择必须的db、db-shm、db-wal文件`);
        return false;
      }
      // 获取已授权uri,校验当前数据库必要文件是否已授权
      let policyUris = this.dataPreferences.getSync('policyUris', []) as Array<string>;
      if (!policyUris || policyUris.length < 3) {
        this.promptAction.showToast({ message: '请选择必须的db、db-shm、db-wal文件' });
        console.warn(`请选择必须的db、db-shm、db-wal文件`);
        return false;
      }
      let baseDir = `${this.rootDir}/${this.customDir}`;
      let dbPath = `${baseDir}/${this.dbName}`;
      let name = this.dbName.split('.')[0];
      if (!policyUris.some(uri => uri.endsWith(`${dbPath}`)) ||
        !policyUris.some(uri => uri.endsWith(`${baseDir}/${name}.db-shm`)) ||
        !policyUris.some(uri => uri.endsWith(`${baseDir}/${name}.db-wal`))) {
        this.promptAction.showToast({ message: '请选择必须的db、db-shm、db-wal文件' });
        console.warn(`请选择必须的db、db-shm、db-wal文件`);
        return false;
      }
      console.info(`checkUirPermission successful.`);
      return true;
    } catch (error) {
      let err: BusinessError = error as BusinessError;
      console.error(`checkUirPermission failed with err, Error code: ${err.code}, message: ${err.message}`);
      return false;
    }
  }

  initRdb() {
    try {
      /**
       * 直接使用公共目录下数据库文件至少需要.db、.db-shm、.db-wal,所以在实际开库使用前需要提前校验是否有这对应三个文件的可读权限
       */
      if (!this.checkUirPermission()) {
        console.warn(`校验未通过,请重新选择必要的db、db-shm、db-wal文件.`);
        return;
      }
      let dbConfig: relationalStore.StoreConfig = {
        name: this.dbName,
        securityLevel: relationalStore.SecurityLevel.S3,
        rootDir: this.rootDir,
        customDir: this.customDir
      };
      console.info(`dbName: ${this.dbName}, rootDir: ${this.rootDir}, customDir: ${this.customDir}`);
      // 1.获取公共目录下的数据库文件,使用只读模式打开
      relationalStore.getRdbStore(this.context, dbConfig).then((rdbStore: relationalStore.RdbStore) => {
        this.store = rdbStore;
        try {
          if (rdbStore != undefined) {
            // 监听sql错误信息
            rdbStore.on('sqliteErrorOccurred', exceptionMessage => {
              let sqliteCode = exceptionMessage.code;
              let sqliteMessage = exceptionMessage.message;
              let errSQL = exceptionMessage.sql;
              console.error(`error log is ${sqliteCode}, errMessage is ${sqliteMessage}, errSQL is ${errSQL}`);
            });
          }
        } catch (err) {
          let code = (err as BusinessError).code;
          let message = (err as BusinessError).message;
          console.error(`Register observer failed, code is ${code},message is ${message}`);
        }
        this.promptAction.showToast({ message: `数据库初始化成功.` });
        console.info('initRdb successfully.');
      }).catch((err: BusinessError) => {
        console.error(`initRdb failed, code is ${err.code},message is ${err.message}`);
      });
    } catch (err) {
      console.error(`initRdb failed, code is ${err.code},message is ${err.message}`);
    }
  }

  queryData() {
    if (!this.store) {
      console.error('RdbStore is undefined.');
      return;
    }
    try {
      // 查询数据
      let resultSet = this.store.querySqlSync(`SELECT * FROM ${this.tableName} limit 10`);
      console.info(`ResultSet column names: ${resultSet.columnNames}, row count: ${resultSet.rowCount}`);
      if (resultSet.rowCount > 0) {
        resultSet.getRows(resultSet.rowCount).then((rows: Array<relationalStore.ValuesBucket>) => {
          console.info(`querySqlSync successful rows = ${JSON.stringify(rows)}`);
          this.promptAction.showToast({ message: `查询数据库成功,查询结果:${JSON.stringify(rows)}` });
        }).catch((err: BusinessError) => {
          console.error(`querySqlSync failed, code is ${err.code},message is ${err.message}`);
        }).finally(() => {
          resultSet.close();
        });
      } else {
        resultSet.close();
      }
    } catch (error) {
      let err: BusinessError = error as BusinessError;
      console.error(`queryData failed, code is ${err.code},message is ${err.message}`);
    }
  }
}