import relationalStore from '@ohos.data.relationalStore'; import { LogUtil, StrUtil } from '@pura/harmony-utils'; import { VideoItem } from '../../viewmodel/VideoItem'; import { WebDavAccount } from '../../viewmodel/WebDavAccount'; import { CommonConstants } from '../constants/CommonConstants'; import Logger from './Logger'; import RdbUtils from './RdbUtils'; import { AudioQuality, Utility } from './Utility'; import { WebDavUrlUtil } from './WebDavUrlUtil'; /** * 数据库字段常量接口定义 */ interface DBColumnsInterface { ID: string; NAME: string; FILE_PATH: string; TYPE: string; VIDEO_SIZE: string; C_TIME: string; PARENT_PATH: string; IS_FAV: string; PIXEL_MAP_PATH: string; IS_CUSTOM_COVER: string; ARTIST: string; ALBUM: string; FILE_NAME: string; SIZE: string; DURATION: string; MIME_TYPE: string; TRACK_COUNT: string; SAMPLE_RATE: string; LAST_PLAYED_STR: string; PLAY_COUNT: string; LYRIC_CONTENT: string; WEBDAV_ACCOUNT_ID: string; REMOTE_REL_PATH: string; } /** * 数据库字段常量，避免硬编码 */ const DB_COLUMNS: DBColumnsInterface = { ID: 'id', NAME: 'name', FILE_PATH: 'filePath', TYPE: 'mtype', VIDEO_SIZE: 'videoSize', C_TIME: 'cTime', PARENT_PATH: 'parentPath', IS_FAV: 'isFav', PIXEL_MAP_PATH: 'pixelMapPath', IS_CUSTOM_COVER: 'isCustomCover', ARTIST: 'artist', ALBUM: 'album', FILE_NAME: 'fileName', SIZE: 'size', DURATION: 'duration', MIME_TYPE: 'mimeType', TRACK_COUNT: 'trackCount', SAMPLE_RATE: 'sampleRate', LAST_PLAYED_STR: 'lastPlayedStr', PLAY_COUNT: 'playCount', LYRIC_CONTENT: 'lyricContent', WEBDAV_ACCOUNT_ID: 'webdav_account_id', REMOTE_REL_PATH: 'remote_rel_path' }; /** * 统一的 filePath 规范化方法： * - WebDAV 的 HTTP/HTTPS 地址转为相对路径，便于与数据库匹配 * - 其他路径保持不变 */ function normalizeFilePath(filePath: string): string { if (!filePath) { return filePath; } if (WebDavUrlUtil.isHttpUrl(filePath)) { const relative = WebDavUrlUtil.toStoragePath(filePath); return relative || filePath; } return filePath; } /** * 分页查询接口 - 支持条件查询、排序、分页 */ export interface PageQueryOptions { parentPath?: string; // 文件夹路径(首页模式) searchKeyword?: string; // 搜索关键词 sortType?: number; // 排序类型 pageIndex: number; // 页码(从0开始) pageSize: number; // 每页数量 type?: number; // 媒体类型过滤 isShowFileName?: boolean; // 是否显示文件名（影响排序字段选择） } export interface PageQueryResult { items: VideoItem[]; totalCount: number; hasMore: boolean; countMap?: Map; } export interface RandomSongQueryOptions { type?: number; excludeFilePaths?: string[]; parentPath?: string; // 文件夹路径(用于首页模式随机播放) playlistSongFilePaths?: string[]; // 歌单歌曲路径列表(用于歌单模式随机播放) } export default class MediaTable { private accountTable = new RdbUtils(RdbUtils.MEDIA_TABLE.tableName, RdbUtils.MEDIA_TABLE.sqlCreate, RdbUtils.MEDIA_TABLE.columns); constructor(context:Context,callback: Function = () => { }) { this.accountTable.getRdbStore(context,callback); } /** * 判断指定 filePath 是否已存在 */ private async existsByFilePath(filePath: string): Promise { const normalizedPath = normalizeFilePath(filePath); return new Promise((resolve, reject) => { try { const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); predicates.equalTo(DB_COLUMNS.FILE_PATH, normalizedPath); this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => { try { resolve(resultSet.rowCount > 0); } catch (e) { reject(e); } finally { resultSet.close(); } }); } catch (err) { reject(err); } }); } /** * WebDAV歌曲入库（若不存在则插入） */ public async upsertWebDavVideoItem(item: VideoItem): Promise { try { if (!item || !item.filePath) { return false; } const isPureWebDav = item.type === CommonConstants.TYPE_WEBDAV; if (isPureWebDav) { // 将完整URL转换为相对路径用于存储 const relativePath = WebDavUrlUtil.toStoragePath(item.filePath); if (relativePath !== item.filePath) { // 只有当转换成功时才设置remote_rel_path item.remote_rel_path = relativePath; // 更新filePath为相对路径 item.filePath = relativePath; Logger.info(RdbUtils.RDB_TAG, `WebDAV URL转换: 原始URL -> 存储路径: ${relativePath}`); } } const exists = await this.existsByFilePath(item.filePath); if (exists) { return true; // 已存在不再重复插入 } return await this.insertWebDavItem(item); } catch (e) { Logger.error(RdbUtils.RDB_TAG, 'upsertWebDavVideoItem 失败: ' + (e as Error).message); return false; } } /** * 插入WebDAV歌曲的最小字段集合 */ private async insertWebDavItem(item: VideoItem): Promise { return new Promise((resolve) => { try { const bucket: relationalStore.ValuesBucket = generateBucket(item); if (!bucket.id) { // 使用filePath作为主键/ID以避免为空 bucket.id = item.filePath; } if (!bucket.parentPath) { // 以路径去掉文件名部分作为 parentPath（已经是相对路径） const idx = item.filePath.lastIndexOf('/'); if (idx > 0) { bucket.parentPath = item.filePath.substring(0, idx); } } if (item.remote_rel_path) { bucket.remote_rel_path = item.remote_rel_path; } this.accountTable.insertData(bucket, (success: boolean) => { resolve(success); }); } catch (e) { Logger.error(RdbUtils.RDB_TAG, 'insertWebDavItem 出错: ' + (e as Error).message); resolve(false); } }); } /** * 保存或更新WebDAV歌曲的完整元数据 */ public async saveOrUpdateWebDavItem(item: VideoItem): Promise { try { if (!item || !item.filePath) { return false; } item.filePath = normalizeFilePath(item.filePath); if (!item.remote_rel_path) { item.remote_rel_path = item.filePath; } if (!item.parentPath) { const idx = item.filePath.lastIndexOf('/'); item.parentPath = idx > 0 ? item.filePath.substring(0, idx) : ''; } const exists = await this.existsByFilePath(item.filePath); if (exists) { const cached = await this.queryVideoByFilePath(item.filePath); if (cached && cached.isCustomCover === 1 && item.isCustomCover !== 1) { item.pixelMapPath = cached.pixelMapPath || item.pixelMapPath; item.isCustomCover = 1; } return await new Promise((resolve) => { const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); predicates.equalTo(DB_COLUMNS.FILE_PATH, item.filePath); const bucket = generateBucket(item); this.accountTable.updateData(predicates, bucket, (success: boolean) => resolve(success)); }); } return await this.insertWebDavItem(item); } catch (error) { Logger.error(RdbUtils.RDB_TAG, 'saveOrUpdateWebDavItem 失败: ' + (error as Error).message); return false; } } getRdbStore(context:Context,callback: Function = () => { }) { this.accountTable.getRdbStore(context,callback); } insert(item: VideoItem, callback: Function,cover_api?:string) { const valueBucket: relationalStore.ValuesBucket = generateBucket(item); this.accountTable.insertData(valueBucket, callback,cover_api); } deleteData(item: VideoItem, callback: Function) { let predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); predicates.equalTo('id', item.id); this.accountTable.deleteData(predicates, callback); } deleteDataFilePath(filePath: string, callback: Function) { const normalizedPath = normalizeFilePath(filePath); let predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); predicates.equalTo('filePath', normalizedPath); this.accountTable.deleteData(predicates, callback); } deleteDataForParentPath(parentPath:string, callback: Function) { let predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); predicates.equalTo('parentPath', parentPath); this.accountTable.deleteData(predicates, callback); } //更新音乐封面地址 public updatePixelMapPath(filePath: string, newPixelMapPath: string, callback: Function) { this.updateCoverInfo(filePath, newPixelMapPath, undefined, false, callback); } public updateCustomCoverPath(filePath: string, newPixelMapPath: string, callback: Function) { this.updateCoverInfo(filePath, newPixelMapPath, 1, true, callback); } private updateCoverInfo(filePath: string, newPixelMapPath: string, isCustomCover: number | undefined, allowReplaceCustomCover: boolean, callback: Function) { const normalizedPath = normalizeFilePath(filePath); const queryPredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); queryPredicates.equalTo('filePath', normalizedPath); this.accountTable.query(queryPredicates, (resultSet: relationalStore.ResultSet) => { if (resultSet.rowCount === 0) { callback(false, 'Error: Target file not found in database'); resultSet.close(); return; } let existingPixelMapPath = ''; let existingIsCustomCover = 0; if (resultSet.goToFirstRow()) { const pixelMapIndex = resultSet.getColumnIndex(DB_COLUMNS.PIXEL_MAP_PATH); if (pixelMapIndex >= 0) { existingPixelMapPath = resultSet.getString(pixelMapIndex) || ''; } const customCoverIndex = resultSet.getColumnIndex(DB_COLUMNS.IS_CUSTOM_COVER); if (customCoverIndex >= 0) { existingIsCustomCover = resultSet.getDouble(customCoverIndex) || 0; } } resultSet.close(); if (existingIsCustomCover === 1 && !allowReplaceCustomCover && existingPixelMapPath.length > 0 && existingPixelMapPath !== newPixelMapPath) { callback(true, 'Skip updating custom cover'); return; } const updatePredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); updatePredicates.equalTo('filePath', normalizedPath); const valueBucket: relationalStore.ValuesBucket = { pixelMapPath: newPixelMapPath }; if (isCustomCover !== undefined) { valueBucket.isCustomCover = isCustomCover; } this.accountTable.updateData(updatePredicates, valueBucket, (success: boolean) => { callback(success, success ? null : 'Database update operation failed'); }); }); } updateData(item: VideoItem, callback: Function) { const valueBucket: relationalStore.ValuesBucket = generateBucket(item); let predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); predicates.equalTo('id', item.id); this.accountTable.updateData(predicates, valueBucket, callback); } //编辑歌曲的信息更新数据库 public updateMediaInfo(filePath: string, title: string, artist: string, album: string, lyricContent:string,year:string,genre:string,track:string, ALBUMARTIST:string,COMPOSER:string,LYRICIST:string,COMMENT:string,disc:string, callback: Function): boolean { if (!callback || typeof callback !== 'function') { Logger.info(RdbUtils.RDB_TAG, 'updateMediaInfo() has no valid callback!'); return false; } if (!this.accountTable) { Logger.error(RdbUtils.RDB_TAG, 'RdbStore is not initialized.'); callback(false); return false; } const normalizedPath = normalizeFilePath(filePath); console.info('onecold filePath updateMediaInfo= '+normalizedPath) // Step 1: Create a predicate to find the record by filePath const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); predicates.equalTo('filePath', normalizedPath); // Step 2: Query the database to check if the record exists this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => { if (resultSet.rowCount === 0) { Logger.info(RdbUtils.RDB_TAG, `No record found with filePath ${normalizedPath}.`); console.info('onecold No record found with filePath '+normalizedPath) callback(false); resultSet.close(); return; } console.info('onecold filePath updateMediaInfo1= '+normalizedPath) // Step 3: Prepare the values to update const valuesToUpdate: relationalStore.ValuesBucket = {}; if (title !== '') { valuesToUpdate.name = title; } if (artist !== '') { valuesToUpdate.artist = artist; } if (album !== '') { valuesToUpdate.album = album; } if (lyricContent !== '') { valuesToUpdate.lyricContent = lyricContent; } if (year !== '') { valuesToUpdate.year = year; } if (genre !== '') { valuesToUpdate.genre = genre; } if (track !== '') { valuesToUpdate.track = track; } if (ALBUMARTIST !== '') { valuesToUpdate.ALBUMARTIST = ALBUMARTIST; } if (COMPOSER !== '') { valuesToUpdate.COMPOSER = COMPOSER; } if (LYRICIST !== '') { valuesToUpdate.LYRICIST = LYRICIST; } if (COMMENT !== '') { valuesToUpdate.COMMENT = COMMENT; } if (disc !== '') { valuesToUpdate.disc = disc; } resultSet.close(); // Step 4: Update the record if there are values to update if (Object.keys(valuesToUpdate).length > 0) { this.accountTable.updateData(predicates, valuesToUpdate, (success: boolean) => { callback(success, success ? null : 'Database update operation failed'); }); } else { Logger.info(RdbUtils.RDB_TAG, 'No fields to update.'); callback(false); } }); return true; // 表示方法已成功启动 } //更新重命名数据操作 public updateRename(newName: string, oldPath: string, newPath: string, callback: Function) { const normalizedOldPath = normalizeFilePath(oldPath); const normalizedNewPath = normalizeFilePath(newPath); // Step 1: 查询原始记录 const queryPredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); queryPredicates.equalTo('filePath', normalizedOldPath); this.accountTable.query(queryPredicates, (resultSet: relationalStore.ResultSet) => { if (resultSet.rowCount === 0) { callback(false, 'Error: File not found'); return; } resultSet.goToFirstRow(); // Step 3: 构建更新数据 const currentName = resultSet.getString(resultSet.getColumnIndex('name')); const currentFileName = resultSet.getString(resultSet.getColumnIndex('fileName')); let obj: relationalStore.ValuesBucket = {}; obj.id = normalizedNewPath obj.filePath = normalizedNewPath; if (currentName === currentFileName) { obj.name = newName; obj.fileName = newName; } else { obj.fileName = newName; } obj.mtype = resultSet.getDouble(resultSet.getColumnIndex('mtype')); obj.videoSize = resultSet.getDouble(resultSet.getColumnIndex('videoSize')); obj.cTime = resultSet.getString(resultSet.getColumnIndex('cTime')); obj.parentPath = resultSet.getString(resultSet.getColumnIndex('parentPath')); // obj.pixelMapToString = resultSet.getString(resultSet.getColumnIndex('pixelMapToString')); obj.artist = resultSet.getString(resultSet.getColumnIndex('artist')); obj.album = resultSet.getString(resultSet.getColumnIndex('album')); obj.isFav = resultSet.getDouble(resultSet.getColumnIndex('isFav')); obj.pixelMapPath = resultSet.getString(resultSet.getColumnIndex('pixelMapPath')); const customCoverIndex = resultSet.getColumnIndex(DB_COLUMNS.IS_CUSTOM_COVER); if (customCoverIndex >= 0) { obj.isCustomCover = resultSet.getDouble(customCoverIndex); } obj.duration = resultSet.getString(resultSet.getColumnIndex('duration')); obj.mimeType = resultSet.getString(resultSet.getColumnIndex('mimeType')); obj.trackCount = resultSet.getString(resultSet.getColumnIndex('trackCount')); obj.sampleRate = resultSet.getString(resultSet.getColumnIndex('sampleRate')); obj.lastPlayedStr = resultSet.getString(resultSet.getColumnIndex('lastPlayedStr')); obj.playCount = resultSet.getDouble(resultSet.getColumnIndex('playCount')); obj.lyricContent = resultSet.getString(resultSet.getColumnIndex('lyricContent')); obj.md5Str = resultSet.getString(resultSet.getColumnIndex('md5Str')); obj.extra_json = resultSet.getString(resultSet.getColumnIndex('extra_json')); obj.pyStr = resultSet.getString(resultSet.getColumnIndex('pyStr')); obj.bit_rate = resultSet.getString(resultSet.getColumnIndex('bit_rate')); obj.probe_score = resultSet.getDouble(resultSet.getColumnIndex('probe_score')); obj.year = resultSet.getString(resultSet.getColumnIndex('year')); obj.nb_streams = resultSet.getDouble(resultSet.getColumnIndex('nb_streams')); obj.nb_programs = resultSet.getDouble(resultSet.getColumnIndex('nb_programs')); obj.genre = resultSet.getString(resultSet.getColumnIndex('genre')); obj.track = resultSet.getString(resultSet.getColumnIndex('track')); obj.bits_per_raw_sample = resultSet.getString(resultSet.getColumnIndex('bits_per_raw_sample')); obj.channels = resultSet.getString(resultSet.getColumnIndex('channels')); obj.channel_layout = resultSet.getString(resultSet.getColumnIndex('channel_layout')); obj.start_time = resultSet.getString(resultSet.getColumnIndex('start_time')); obj.ALBUMARTIST = resultSet.getString(resultSet.getColumnIndex('ALBUMARTIST')); obj.COMPOSER = resultSet.getString(resultSet.getColumnIndex('COMPOSER')); obj.LYRICIST = resultSet.getString(resultSet.getColumnIndex('LYRICIST')); obj.COMMENT = resultSet.getString(resultSet.getColumnIndex('COMMENT')); obj.disc = resultSet.getString(resultSet.getColumnIndex('disc')); const valueBucket: relationalStore.ValuesBucket = obj // Step 4: 执行更新 const updatePredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); updatePredicates.equalTo('filePath', normalizedOldPath); this.accountTable.updateData(updatePredicates, valueBucket, (success: boolean) => { callback(success, success ? null : 'Update failed'); }); resultSet.close() }); } // 根据isFav查询数据 public queryByisFav(isFav: number, callback: (result: VideoItem[]) => void) { const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); predicates.equalTo('isFav', isFav); this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => { const result = this.parseResultSetToVideoItems(resultSet); callback(result); }); } // 根据filePath更新isFav的值 public updateIsFavByFilePath(filePath: string, isFav: number, callback: (success: boolean, error?: string) => void) { const normalizedPath = normalizeFilePath(filePath); const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); predicates.equalTo('filePath', normalizedPath); this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => { if (resultSet.rowCount === 0) { callback(false, 'Error: File not found'); resultSet.close(); return; } resultSet.close(); const valueBucket: relationalStore.ValuesBucket = { isFav: isFav }; this.accountTable.updateData(predicates, valueBucket, (success: boolean) => { callback(success, success ? '' : 'Update failed'); }); }); } // 查询全部，或者某个id(查询的字段，回调，是否查询全部) query(id: number, callback: Function, isAll: boolean = true) { let predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); if (!isAll) { predicates.equalTo('id', id); } this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => { let count: number = resultSet.rowCount; if (count === 0 || typeof count === 'string') { console.log(`${RdbUtils.RDB_TAG}` + 'Query no results!'); callback([]); } else { const result = this.parseResultSetToVideoItems(resultSet); callback(result); } }); } // 新增方法：根据parentPath查询数据 public queryByParentPath(path: string, callback: (result: VideoItem[]) => void) { try { const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); predicates.equalTo('parentPath', path); LogUtil.info('onecold parentPath = '+path) // 2. 执行查询并处理结果 this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => { // 3. 复用已有的解析逻辑 const result = this.parseResultSetToVideoItems(resultSet); callback(result); }); }catch (err) { Logger.error(` onecold testtag queryByParentPath: ${err.code} - ${err.message}`); } // 1. 构建查询条件 } // 查询所有艺术家及其歌曲（返回Map结构：artist -> VideoItem[]） public queryArtistsWithSongs(callback: (result: Map) => void) { // 1. 查询去重的艺术家列表（非空） const artistPredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); artistPredicates.isNotNull('artist').distinct(); this.accountTable.query(artistPredicates, (resultSet: relationalStore.ResultSet) => { const artists: string[] = this.parseDistinctColumn(resultSet, 'artist'); // 2. 遍历每个艺术家，查询其歌曲 const resultMap = new Map(); let processedCount = 0; if (artists.length === 0) { callback(resultMap); return; } artists.forEach(artist => { const songPredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); songPredicates.equalTo('artist', artist); this.accountTable.query(songPredicates, (songResultSet: relationalStore.ResultSet) => { const songs = this.parseResultSetToVideoItems(songResultSet); resultMap.set(artist, songs); processedCount++; // 3. 全部查询完成后回调 if (processedCount === artists.length) { callback(resultMap); } }); }); }); } // 查询所有专辑及其歌曲（返回Map结构：album -> VideoItem[]） public queryAlbumsWithSongs(callback: (result: Map) => void) { // 1. 查询去重的专辑列表（非空） const albumPredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); albumPredicates.isNotNull('album').distinct(); this.accountTable.query(albumPredicates, (resultSet: relationalStore.ResultSet) => { const albums: string[] = this.parseDistinctColumn(resultSet, 'album'); // 2. 遍历每个专辑，查询其歌曲 const resultMap = new Map(); let processedCount = 0; if (albums.length === 0) { callback(resultMap); return; } albums.forEach(album => { const songPredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); songPredicates.equalTo('album', album); this.accountTable.query(songPredicates, (songResultSet: relationalStore.ResultSet) => { const songs = this.parseResultSetToVideoItems(songResultSet); resultMap.set(album, songs); processedCount++; // 3. 全部查询完成后回调 if (processedCount === albums.length) { callback(resultMap); } }); }); }); } /** * 按艺术家查询歌曲列表（用于艺术家详情页） */ public async querySongsByArtist(artist: string, sortType?: number, isShowFileName?: boolean): Promise { try { const trimmedArtist = artist?.trim() || ''; if (!trimmedArtist) { return []; } // 使用SQL的TRIM函数匹配，解决数据库中artist字段可能有前后空格的问题 const tableName = RdbUtils.MEDIA_TABLE.tableName; let orderBy = `${DB_COLUMNS.NAME} COLLATE NOCASE ASC`; if (isShowFileName) { orderBy = `${DB_COLUMNS.FILE_NAME} COLLATE NOCASE ASC`; } const sql = `SELECT * FROM ${tableName} WHERE TRIM(${DB_COLUMNS.ARTIST}) = ? AND ${DB_COLUMNS.TYPE} = ? ORDER BY ${orderBy}`; const params: (string | number)[] = [trimmedArtist, CommonConstants.TYPE_LOCAL]; const resultSet = await this.accountTable.querySql(sql, params); try { const result = this.parseResultSetToVideoItems(resultSet, sortType); return result; } finally { resultSet.close(); } } catch (err) { const error = err as Error; Logger.error('heanup MediaTable', `querySongsByArtist error: ${error.message}`); return []; } } /** * 按专辑查询歌曲列表（用于专辑详情页） */ public async querySongsByAlbum(album: string, sortType?: number, isShowFileName?: boolean): Promise { try { const trimmedAlbum = album?.trim() || ''; if (!trimmedAlbum) { return []; } // 使用SQL的TRIM函数匹配，解决数据库中album字段可能有前后空格的问题 const tableName = RdbUtils.MEDIA_TABLE.tableName; let orderBy = `${DB_COLUMNS.NAME} COLLATE NOCASE ASC`; if (isShowFileName) { orderBy = `${DB_COLUMNS.FILE_NAME} COLLATE NOCASE ASC`; } const sql = `SELECT * FROM ${tableName} WHERE TRIM(${DB_COLUMNS.ALBUM}) = ? AND ${DB_COLUMNS.TYPE} = ? ORDER BY ${orderBy}`; const params: (string | number)[] = [trimmedAlbum, CommonConstants.TYPE_LOCAL]; const resultSet = await this.accountTable.querySql(sql, params); try { const result = this.parseResultSetToVideoItems(resultSet, sortType); return result; } finally { resultSet.close(); } } catch (err) { const error = err as Error; Logger.error('heanup MediaTable', `querySongsByAlbum error: ${error.message}`); return []; } } // 解析去重列数据（如artist/album） private parseDistinctColumn(resultSet: relationalStore.ResultSet, columnName: string): string[] { const uniqueValues = new Set(); // 使用Set特性自动去重 if (resultSet.rowCount > 0) { resultSet.goToFirstRow(); for (let i = 0; i < resultSet.rowCount; i++) { const value = resultSet.getString(resultSet.getColumnIndex(columnName))?.trim(); // 处理空格 if (value) { // 过滤空值 uniqueValues.add(value); } if (i < resultSet.rowCount - 1) { // 避免最后一行越界 resultSet.goToNextRow(); } } } resultSet.close(); return Array.from(uniqueValues); // Set转数组 } /** * 解析ResultSet为VideoItem数组（复用原有逻辑） * 当sortType=10时,会按照disc和track进行二次排序 */ private parseResultSetToVideoItems(resultSet: relationalStore.ResultSet, sortType?: number): VideoItem[] { const items: VideoItem[] = []; try { // 检查结果集是否有效 if (resultSet && resultSet.rowCount > 0) { while (resultSet.goToNextRow()) { const item = this.buildVideoItem(resultSet); items.push(item); } } } catch (err) { Logger.error(`解析结果集出错: ${err.message}`); } finally { // 确保结果集被关闭 if (resultSet) { resultSet.close(); } } // sortType=10: 按碟片编号(disc)和音轨号(track)升序排序 if (sortType === 10) { items.sort((a, b) => { // 解析disc字段,处理可能为空或包含非数字字符的情况 const discA = this.parseDiscNumber(a.disc); const discB = this.parseDiscNumber(b.disc); // 先按碟片编号排序 if (discA !== discB) { return discA - discB; } // 碟片编号相同,按音轨号排序 const trackA = this.parseTrackNumber(a.track); const trackB = this.parseTrackNumber(b.track); return trackA - trackB; }); Logger.info(RdbUtils.RDB_TAG, `parseResultSetToVideoItems: 已按碟片-音轨升序排序,歌曲数量=${items.length}`); } // sortType=11: 按碟片编号(disc)和音轨号(track)降序排序 if (sortType === 11) { items.sort((a, b) => { // 解析disc字段,处理可能为空或包含非数字字符的情况 const discA = this.parseDiscNumber(a.disc); const discB = this.parseDiscNumber(b.disc); // 先按碟片编号降序排序 if (discA !== discB) { return discB - discA; } // 碟片编号相同,按音轨号降序排序 const trackA = this.parseTrackNumber(a.track); const trackB = this.parseTrackNumber(b.track); return trackB - trackA; }); Logger.info(RdbUtils.RDB_TAG, `parseResultSetToVideoItems: 已按碟片-音轨降序排序,歌曲数量=${items.length}`); } return items; } /** * 解析碟片编号,返回数字进行比较 * 支持格式: "1", "1/5", "1.5", "CD1", "Vol1"等 */ private parseDiscNumber(disc: string | undefined | null): number { if (!disc) return 0; // 移除空格并转大写 const discStr = disc.toString().trim().toUpperCase(); // 尝试提取数字 const match = discStr.match(/(\d+)/); if (match) { return parseInt(match[1], 10); } return 0; } /** * 解析音轨号,返回数字进行比较 * 支持格式: "1", "1/10", "1.12", "Track 1"等 */ private parseTrackNumber(track: string | undefined | null): number { if (!track) return 0; // 移除空格并转大写 const trackStr = track.toString().trim().toUpperCase(); // 尝试提取数字 const match = trackStr.match(/(\d+)/); if (match) { return parseInt(match[1], 10); } return 0; } // 根据filePath更新lastPlayedStr的值同时playCount值加1 public updateLastPlayedStrByFilePath(filePath: string, lastPlayedStr: string, callback: (success: boolean, error?: string) => void) { const normalizedPath = normalizeFilePath(filePath); const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); predicates.equalTo('filePath', normalizedPath); this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => { if (resultSet.rowCount === 0) { callback(false, 'Error: File not found'); resultSet.close(); return; } // 获取当前的 playCount 值 let currentPlayCount = 0; if (resultSet.goToFirstRow()) { currentPlayCount = resultSet.getLong(resultSet.getColumnIndex('playCount')); } resultSet.close(); // 计算新的 playCount 值 const newPlayCount = currentPlayCount + 1; // 准备要更新的值 const valueBucket: relationalStore.ValuesBucket = { lastPlayedStr: lastPlayedStr, playCount: newPlayCount }; // 更新数据 this.accountTable.updateData(predicates, valueBucket, (success: boolean) => { callback(success, success ? '' : 'Update failed'); }); }); } // 根据最近播放时间查询指定数量的记录 public queryRecentPlayedRecords(count: number, callback: (result: VideoItem[]) => void) { try { // 1. 构建查询条件：按lastPlayedStr降序排列，限制返回条数，且lastPlayedStr不为空 const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); // 添加筛选条件，确保lastPlayedStr不为空 predicates.isNotNull('lastPlayedStr'); predicates.notEqualTo('lastPlayedStr', ''); // 排除空字符串 predicates.notEqualTo('mtype', CommonConstants.TYPE_LOCK); // 使用 orderByDesc 方法进行降序排序 predicates.orderByDesc('lastPlayedStr'); // 使用 limit 方法限制返回的记录数量 predicates.limitAs(count); // 2. 执行查询并处理结果 this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => { // 3. 复用已有的解析逻辑 const result = this.parseResultSetToVideoItems(resultSet); callback(result); }); } catch (err) { Logger.error(`queryRecentPlayedRecords error: ${err.code} - ${err.message}`); callback([]); } } // 清空播放历史记录 public clearPlayHistory(callback: (success: boolean, error?: string) => void) { // 1. 构建查询条件：筛选lastPlayedStr非空的记录 const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); predicates.isNotNull('lastPlayedStr'); // 2. 准备更新数据：将lastPlayedStr设为空字符串 const valueBucket: relationalStore.ValuesBucket = { lastPlayedStr: '' }; // 3. 执行批量更新操作 this.accountTable.updateData(predicates, valueBucket, (success: boolean) => { // 将 null 替换为 undefined callback(success, success ? 'Clear play history success' : 'Clear play history operation failed'); }); } private buildVideoItem(rs: relationalStore.ResultSet): VideoItem { // 添加空值保护 const safeGet = (col: string) => { const index = rs.getColumnIndex(col); return index >= 0 ? rs.getString(index) || '' : ''; }; const safeGetNumber = (col: string) => { const index = rs.getColumnIndex(col); return index >= 0 ? rs.getDouble(index) || 0 : 0; }; let item = new VideoItem( safeGet(DB_COLUMNS.NAME), safeGet(DB_COLUMNS.ID), safeGet(DB_COLUMNS.FILE_PATH), safeGetNumber(DB_COLUMNS.TYPE), safeGetNumber(DB_COLUMNS.VIDEO_SIZE), safeGet(DB_COLUMNS.C_TIME), safeGet(DB_COLUMNS.SIZE), safeGet(DB_COLUMNS.PIXEL_MAP_PATH), safeGet(DB_COLUMNS.ARTIST), safeGet(DB_COLUMNS.ALBUM), safeGet(DB_COLUMNS.FILE_NAME) ); // 设置额外属性，添加安全检查 item.isFav = safeGetNumber(DB_COLUMNS.IS_FAV); item.isCustomCover = safeGetNumber(DB_COLUMNS.IS_CUSTOM_COVER); item.duration = safeGet(DB_COLUMNS.DURATION); item.mimeType = safeGet(DB_COLUMNS.MIME_TYPE); item.trackCount = safeGet(DB_COLUMNS.TRACK_COUNT); item.sampleRate = safeGet(DB_COLUMNS.SAMPLE_RATE); item.lastPlayedStr = safeGet(DB_COLUMNS.LAST_PLAYED_STR); item.playCount = safeGetNumber(DB_COLUMNS.PLAY_COUNT); item.lyricContent = safeGet(DB_COLUMNS.LYRIC_CONTENT); item.md5Str = safeGet('md5Str'); item.extra_json = safeGet('extra_json'); item.pyStr = safeGet('pyStr'); item.bit_rate = safeGet('bit_rate'); item.probe_score = safeGetNumber('probe_score'); item.year = safeGet('year'); item.nb_streams = safeGetNumber('nb_streams'); item.nb_programs = safeGetNumber('nb_programs'); item.genre = safeGet('genre'); item.track = safeGet('track'); item.bits_per_raw_sample = safeGet('bits_per_raw_sample'); item.channels = safeGet('channels'); item.channel_layout = safeGet('channel_layout'); item.start_time = safeGet('start_time'); item.ALBUMARTIST = safeGet('ALBUMARTIST'); item.COMPOSER = safeGet('COMPOSER'); item.LYRICIST = safeGet('LYRICIST'); item.COMMENT = safeGet('COMMENT'); item.disc = safeGet('disc'); item.webdav_account_id = safeGet('webdav_account_id'); item.remote_rel_path = safeGet('remote_rel_path'); return item; } /** * 查询播放次数大于0的记录，并按播放次数降序排列 * @param limitCount 限制返回的记录数量，0表示返回全部 * @param callback 回调函数，返回VideoItem数组 */ public queryByPlayCountDesc(limitCount: number = 0, callback: (result: VideoItem[]) => void): void { try { // 1. 构建查询条件：playCount > 0，按playCount降序排列 const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); predicates.greaterThan('playCount', 0); // 播放次数大于0 predicates.orderByDesc('playCount'); // 按播放次数降序排列 // 只有当limitCount大于0时才设置限制 if (limitCount > 0) { predicates.limitAs(limitCount); } // 2. 执行查询 this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => { // 3. 解析结果集 const result = this.parseResultSetToVideoItems(resultSet); callback(result); }); } catch (err) { Logger.error(`queryByPlayCountDesc error: ${err.code} - ${err.message}`); callback([]); // 发生错误时返回空数组 } } public countSongsByQualityOptimized(callback: (result: Record) => void): void { const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); predicates.in('md5Str', [AudioQuality.LQ, AudioQuality.SQ, AudioQuality.HQ, AudioQuality.HIRES, AudioQuality.HR]); this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => { const result: Record = { [AudioQuality.LQ]: 0, [AudioQuality.SQ]: 0, [AudioQuality.HQ]: 0, [AudioQuality.HIRES]: 0 , // 只保留HR，HIRES的统计将合并到这里 [AudioQuality.HR]: 0 // 只保留HR，HIRES的统计将合并到这里 }; if (resultSet.rowCount > 0) { resultSet.goToFirstRow(); do { const quality = resultSet.getString(resultSet.getColumnIndex('md5Str')) as AudioQuality; if (quality === AudioQuality.LQ || quality === AudioQuality.SQ || quality === AudioQuality.HQ || quality === AudioQuality.HIRES || // HIRES的统计将被合并到HR quality === AudioQuality.HR) { // 如果质量是HIRES或HR，都统计到HR中 const targetKey = (quality === AudioQuality.HIRES || quality === AudioQuality.HR) ? AudioQuality.HR : quality; result[targetKey]++; } } while (resultSet.goToNextRow()); } resultSet.close(); callback(result); }); } /** * Get lyric content by file path (Promise version) * @param filePath The file path to query * @returns Promise that resolves with the lyric content (string) or null if not found */ public getLyricContentByFilePath(filePath: string): Promise { const normalizedPath = normalizeFilePath(filePath); return new Promise((resolve, reject) => { // Create query predicates const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); predicates.equalTo(DB_COLUMNS.FILE_PATH, normalizedPath); // Execute the query this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => { try { if (resultSet.rowCount === 0) { Logger.info(RdbUtils.RDB_TAG, `No record found for filePath: ${normalizedPath}`); resolve(null); return; } // Get the first row resultSet.goToFirstRow(); // Get the column index safely const columnIndex = resultSet.getColumnIndex(DB_COLUMNS.LYRIC_CONTENT); if (columnIndex < 0) { Logger.error(RdbUtils.RDB_TAG, `Column ${DB_COLUMNS.LYRIC_CONTENT} not found`); resolve(null); return; } // Get the lyric content const lyricContent = resultSet.getString(columnIndex); resolve(lyricContent || null); } catch (err) { Logger.error(RdbUtils.RDB_TAG, `Error getting lyric content: ${err.message}`); reject(err); } finally { // Ensure the result set is closed resultSet.close(); } }); }); } /** * Check if a record exists in the database by filePath * @param filePath The file path to check * @returns Promise that resolves to true if record exists, false otherwise */ public isRecordExists(filePath: string): Promise { const normalizedPath = normalizeFilePath(filePath); return new Promise((resolve, reject) => { try { // Create query predicates const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); predicates.equalTo(DB_COLUMNS.FILE_PATH, normalizedPath); // Execute the query this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => { try { // If rowCount > 0, record exists resolve(resultSet.rowCount > 0); } catch (err) { Logger.error(RdbUtils.RDB_TAG, `Error checking record existence: ${err.message}`); reject(err); } finally { // Ensure the result set is closed resultSet.close(); } }); } catch (err) { Logger.error(RdbUtils.RDB_TAG, `Error creating query: ${err.message}`); reject(err); } }); } /** * Query a VideoItem by file path * @param filePath The file path to query * @returns Promise that resolves with the VideoItem or null if not found */ public queryVideoByFilePath(filePath: string): Promise { const normalizedPath = normalizeFilePath(filePath); return new Promise((resolve, reject) => { try { // Create query predicates const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); predicates.equalTo(DB_COLUMNS.FILE_PATH, normalizedPath); // Execute the query this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => { try { if (resultSet.rowCount === 0) { Logger.info(RdbUtils.RDB_TAG, `No record found for filePath: ${normalizedPath}`); resolve(null); return; } // Get the first row if (resultSet.goToFirstRow()) { const videoItem = this.buildVideoItem(resultSet); resolve(videoItem); } else { resolve(null); } } catch (err) { Logger.error(RdbUtils.RDB_TAG, `Error querying video by filePath: ${err.message}`); reject(err); } finally { // Ensure the result set is closed resultSet.close(); } }); } catch (err) { Logger.error(RdbUtils.RDB_TAG, `Error creating query: ${err.message}`); reject(err); } }); } /** * Query all video items * @returns Promise that resolves with an array of VideoItem objects */ public queryAllVideos(): Promise { return new Promise((resolve, reject) => { try { Logger.info(RdbUtils.RDB_TAG, 'queryAllVideos: 开始查询所有音乐文件'); // Create query predicates for all records const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); // Only query music files (type = 0) predicates.equalTo(DB_COLUMNS.TYPE, 0); // Order by name predicates.orderByAsc(DB_COLUMNS.NAME); Logger.info(RdbUtils.RDB_TAG, 'queryAllVideos: 创建查询条件完成'); // Execute the query this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => { try { Logger.info(RdbUtils.RDB_TAG, 'queryAllVideos: 查询回调执行'); if (resultSet.rowCount === 0) { Logger.info(RdbUtils.RDB_TAG, 'queryAllVideos: 没有找到音乐文件'); resolve([]); return; } Logger.info(RdbUtils.RDB_TAG, `queryAllVideos: 找到 ${resultSet.rowCount} 条记录`); const items: VideoItem[] = []; // Go to first row if (resultSet.goToFirstRow()) { do { const item = this.buildVideoItem(resultSet); items.push(item); } while (resultSet.goToNextRow()); } Logger.info(RdbUtils.RDB_TAG, `queryAllVideos: 解析完成，返回 ${items.length} 个项目`); resolve(items); } catch (err) { Logger.error(RdbUtils.RDB_TAG, `queryAllVideos: 解析结果集出错: ${err.message}`); Logger.error(RdbUtils.RDB_TAG, `queryAllVideos: 错误堆栈: ${err.stack || '无堆栈信息'}`); reject(err); } // 注意：不在这里关闭 resultSet，因为 RdbUtils.query 会在回调函数执行后关闭它 }); } catch (err) { Logger.error(RdbUtils.RDB_TAG, `queryAllVideos: 创建查询出错: ${err.message}`); Logger.error(RdbUtils.RDB_TAG, `queryAllVideos: 错误堆栈: ${err.stack || '无堆栈信息'}`); reject(err); } }); } /** * 从数据库随机获取一首歌曲（可选条件：类型、排除路径、文件夹、歌单） */ public async queryRandomSong(options?: RandomSongQueryOptions): Promise { try { // 优先处理歌单模式 if (options?.playlistSongFilePaths && options.playlistSongFilePaths.length > 0) { Logger.info(RdbUtils.RDB_TAG, `queryRandomSong: 歌单模式,歌曲数量=${options.playlistSongFilePaths.length}`); // 过滤掉排除的路径 let availablePaths = options.playlistSongFilePaths; if (options.excludeFilePaths && options.excludeFilePaths.length > 0) { const excludeSet = new Set(options.excludeFilePaths); availablePaths = availablePaths.filter(path => !excludeSet.has(path)); } if (availablePaths.length === 0) { Logger.warn(RdbUtils.RDB_TAG, 'queryRandomSong: 歌单中没有可用歌曲'); return null; } // 随机选择一个路径 const randomPath = availablePaths[Math.floor(Math.random() * availablePaths.length)]; return await this.queryVideoByFilePath(randomPath); } // 文件夹模式或全局模式 const conditions: string[] = []; const params: Array = []; if (options?.type !== undefined) { conditions.push(`${DB_COLUMNS.TYPE} = ?`); params.push(options.type); } // 文件夹条件 if (options?.parentPath) { conditions.push(`${DB_COLUMNS.PARENT_PATH} = ?`); params.push(options.parentPath); } // 排除路径条件 if (options?.excludeFilePaths && options.excludeFilePaths.length > 0) { const normalizedPaths = options.excludeFilePaths.map(path => normalizeFilePath(path)); const placeholders = normalizedPaths.map(() => '?').join(', '); conditions.push(`${DB_COLUMNS.FILE_PATH} NOT IN (${placeholders})`); params.push(...normalizedPaths); } let sql = `SELECT * FROM ${RdbUtils.MEDIA_TABLE.tableName}`; if (conditions.length > 0) { sql += ` WHERE ${conditions.join(' AND ')}`; } sql += ' ORDER BY RANDOM() LIMIT 1'; Logger.info(RdbUtils.RDB_TAG, `queryRandomSong: SQL=${sql}, params=${JSON.stringify(params)}`); const resultSet = await this.accountTable.querySql(sql, params); try { if (resultSet.rowCount === 0) { return null; } resultSet.goToFirstRow(); return this.buildVideoItem(resultSet); } finally { resultSet.close(); } } catch (error) { Logger.error(RdbUtils.RDB_TAG, `queryRandomSong failed: ${(error as Error).message}`); return null; } } /** * 应用搜索条件到查询谓词 * 支持对 name、artist、album、fileName 多字段模糊搜索 */ private applySearchConditions(predicates: relationalStore.RdbPredicates, searchKeyword: string): void { if (searchKeyword && searchKeyword.trim().length > 0) { const keyword = `%${searchKeyword.trim()}%`; predicates.beginWrap(); predicates.like(DB_COLUMNS.NAME, keyword); predicates.or(); predicates.like(DB_COLUMNS.ARTIST, keyword); predicates.or(); predicates.like(DB_COLUMNS.ALBUM, keyword); predicates.or(); predicates.like(DB_COLUMNS.FILE_NAME, keyword); predicates.endWrap(); } } /** * 应用排序条件到查询谓词 * sortType: 0-艺术家升序 1-艺术家降序 2-专辑升序 3-专辑降序 4-名称升序 5-名称降序 6-时间升序 7-时间降序 * 10-碟片编号升序+音轨号升序(用于专辑和文件夹) * @param predicates 查询谓词 * @param sortType 排序类型 * @param isShowFileName 是否显示文件名（当sortType为4或5时，此参数决定按name还是fileName排序） */ private applySortConditions(predicates: relationalStore.RdbPredicates, sortType?: number, isShowFileName?: boolean): void { const type = sortType !== undefined ? sortType : 4; // 当按名称排序时，根据isShowFileName决定使用name还是fileName字段 let nameColumn = DB_COLUMNS.NAME; if ((type === 4 || type === 5) && isShowFileName === true) { nameColumn = DB_COLUMNS.FILE_NAME; } // sortType=10 或 11 需要特殊处理:按碟片编号+音轨号排序 // 由于RdbPredicates不支持多字段排序,我们需要标记这个谓词需要使用原生SQL if (type === 10) { Logger.info(RdbUtils.RDB_TAG, 'applySortConditions: 碟片-音轨升序排序,需要使用内存排序'); predicates.orderByAsc('disc'); // 先按碟片编号排序 // 音轨号排序需要在查询后进行内存排序 return; } if (type === 11) { Logger.info(RdbUtils.RDB_TAG, 'applySortConditions: 碟片-音轨降序排序,需要使用内存排序'); predicates.orderByDesc('disc'); // 先按碟片编号降序排序 // 音轨号排序需要在查询后进行内存排序 return; } switch (type) { case 0: predicates.orderByAsc(DB_COLUMNS.ARTIST); break; case 1: predicates.orderByDesc(DB_COLUMNS.ARTIST); break; case 2: predicates.orderByAsc(DB_COLUMNS.ALBUM); break; case 3: predicates.orderByDesc(DB_COLUMNS.ALBUM); break; case 4: predicates.orderByAsc(nameColumn); break; case 5: predicates.orderByDesc(nameColumn); break; case 6: predicates.orderByAsc(DB_COLUMNS.C_TIME); break; case 7: predicates.orderByDesc(DB_COLUMNS.C_TIME); break; default: predicates.orderByAsc(nameColumn); break; } } /** * 获取总记录数（用于计算分页） */ private async getTotalCount(predicates: relationalStore.RdbPredicates): Promise { return new Promise((resolve, reject) => { this.accountTable.query(predicates, (resultSet: relationalStore.ResultSet) => { try { const count = resultSet.rowCount; resolve(count); } catch (err) { const error = err as Error; Logger.error(RdbUtils.RDB_TAG, `getTotalCount error: ${error.message}`); reject(err); } finally { resultSet.close(); } }); }); } /** * 首页文件夹内容分页查询(modeType=0) * 注意：此方法只返回数据库中的文件记录，不包含文件夹 * 文件夹需要通过文件系统API单独获取 */ public async queryByParentPathPaged(options: PageQueryOptions): Promise { try { Logger.info('heanup MediaTable', `queryByParentPathPaged: 开始分页查询 parentPath=${options.parentPath}, page=${options.pageIndex}, size=${options.pageSize}`); // 1. 构建基础查询条件 const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); if (options.parentPath) { predicates.equalTo(DB_COLUMNS.PARENT_PATH, options.parentPath); } // 2. 应用搜索条件 if (options.searchKeyword) { this.applySearchConditions(predicates, options.searchKeyword); } // 3. 先获取总数 const totalCount = await this.getTotalCount(predicates); // 4. 构建分页查询条件 const pagePredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); if (options.parentPath) { pagePredicates.equalTo(DB_COLUMNS.PARENT_PATH, options.parentPath); } if (options.searchKeyword) { this.applySearchConditions(pagePredicates, options.searchKeyword); } // 5. 应用排序 this.applySortConditions(pagePredicates, options.sortType, options.isShowFileName); // 6. 应用分页 const offset = options.pageIndex * options.pageSize; pagePredicates.limitAs(options.pageSize); pagePredicates.offsetAs(offset); // 7. 执行查询 return new Promise((resolve, reject) => { this.accountTable.query(pagePredicates, (resultSet: relationalStore.ResultSet) => { try { const items = this.parseResultSetToVideoItems(resultSet, options.sortType); const hasMore = (offset + items.length) < totalCount; Logger.info('heanup MediaTable', `queryByParentPathPaged: 查询完成 items=${items.length}, total=${totalCount}, hasMore=${hasMore}`); resolve({ items, totalCount, hasMore }); } catch (err) { const error = err as Error; Logger.error('heanup MediaTable', `queryByParentPathPaged error: ${error.message}`); reject(err); } }); }); } catch (err) { const error = err as Error; Logger.error('heanup MediaTable', `queryByParentPathPaged failed: ${error.message}`); throw error; } } /** * 媒体库分页查询(modeType=1) */ public async queryMediaLibraryPaged(options: PageQueryOptions): Promise { try { Logger.info('heanup MediaTable', `queryMediaLibraryPaged: 开始分页查询 page=${options.pageIndex}, size=${options.pageSize}`); // 1. 构建基础查询条件 - 查询所有type=0的音乐文件 const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); predicates.equalTo(DB_COLUMNS.TYPE, 0); // 2. 应用搜索条件 if (options.searchKeyword) { this.applySearchConditions(predicates, options.searchKeyword); } // 3. 先获取总数 const totalCount = await this.getTotalCount(predicates); // 4. 构建分页查询条件 const pagePredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); pagePredicates.equalTo(DB_COLUMNS.TYPE, 0); if (options.searchKeyword) { this.applySearchConditions(pagePredicates, options.searchKeyword); } // 5. 应用排序 this.applySortConditions(pagePredicates, options.sortType, options.isShowFileName); // 6. 应用分页 const offset = options.pageIndex * options.pageSize; pagePredicates.limitAs(options.pageSize); pagePredicates.offsetAs(offset); // 7. 执行查询 return new Promise((resolve, reject) => { this.accountTable.query(pagePredicates, (resultSet: relationalStore.ResultSet) => { try { const items = this.parseResultSetToVideoItems(resultSet, options.sortType); const hasMore = (offset + items.length) < totalCount; Logger.info('heanup MediaTable', `queryMediaLibraryPaged: 查询完成 items=${items.length}, total=${totalCount}, hasMore=${hasMore}`); resolve({ items, totalCount, hasMore }); } catch (err) { const error = err as Error; Logger.error('heanup MediaTable', `queryMediaLibraryPaged error: ${error.message}`); reject(err); } }); }); } catch (err) { const error = err as Error; Logger.error('heanup MediaTable', `queryMediaLibraryPaged failed: ${error.message}`); throw error; } } /** * 艺术家维度分页查询(modeType=2) * 返回艺术家封面列表的代表性歌曲 */ public async queryArtistsPaged(options: PageQueryOptions): Promise { return await this.queryGroupedPaged(DB_COLUMNS.ARTIST, CommonConstants.TYPE_IS_ARTIST, options); } /** * 专辑维度分页查询(modeType=3) * 返回专辑封面列表的代表性歌曲 */ public async queryAlbumsPaged(options: PageQueryOptions): Promise { return await this.queryGroupedPaged(DB_COLUMNS.ALBUM, CommonConstants.TYPE_IS_ALBUM, options); } private async queryGroupedPaged(columnName: string, itemType: number, options: PageQueryOptions): Promise { try { const keyword = options.searchKeyword?.trim(); const tableName = RdbUtils.MEDIA_TABLE.tableName; const whereClauses = [ `${columnName} IS NOT NULL`, `${columnName} != ''`, `${DB_COLUMNS.TYPE} = ?` ]; const params: Array = [CommonConstants.TYPE_LOCAL]; if (keyword) { whereClauses.push(`${columnName} LIKE ?`); params.push(`%${keyword}%`); } const whereSql = whereClauses.join(' AND '); // 使用TRIM函数去除空格，合并相同艺术家/专辑名的数据 const totalSql = `SELECT COUNT(DISTINCT TRIM(${columnName})) AS totalCount FROM ${tableName} WHERE ${whereSql}`; const totalResultSet = await this.accountTable.querySql(totalSql, params); let totalCount = 0; if (totalResultSet.goToFirstRow()) { totalCount = totalResultSet.getLong(totalResultSet.getColumnIndex('totalCount')); } totalResultSet.close(); const sortType = options.sortType ?? 9; let orderBy = `name ASC`; if (sortType === 5) { orderBy = `name DESC`; } else if (sortType === 8) { orderBy = `songCount ASC, name ASC`; } else if (sortType === 9) { orderBy = `songCount DESC, name ASC`; } const offset = options.pageIndex * options.pageSize; const pageSql = `SELECT TRIM(${columnName}) AS name, COUNT(*) AS songCount, ` + `MAX(${DB_COLUMNS.PIXEL_MAP_PATH}) AS coverPath ` + `FROM ${tableName} WHERE ${whereSql} ` + `GROUP BY TRIM(${columnName}) ` + `HAVING COUNT(*) > 0 ` + `ORDER BY ${orderBy} ` + `LIMIT ? OFFSET ?`; const pageParams = [...params, options.pageSize, offset]; const resultSet = await this.accountTable.querySql(pageSql, pageParams); const items: VideoItem[] = []; const countMap = new Map(); if (resultSet.rowCount > 0) { resultSet.goToFirstRow(); for (let i = 0; i < resultSet.rowCount; i++) { const name = resultSet.getString(resultSet.getColumnIndex('name')) || ''; const songCount = resultSet.getLong(resultSet.getColumnIndex('songCount')); const coverPath = resultSet.getString(resultSet.getColumnIndex('coverPath')) || ''; countMap.set(name, songCount); const item = new VideoItem(name, name, name, itemType, 0, ''); item.pixelMapPath = coverPath; if (itemType === CommonConstants.TYPE_IS_ARTIST) { item.artist = name; } else { item.album = name; } items.push(item); if (i < resultSet.rowCount - 1) { resultSet.goToNextRow(); } } } resultSet.close(); const hasMore = (offset + items.length) < totalCount; Logger.info('heanup MediaTable', `queryGroupedPaged: 查询完成 items=${items.length}, total=${totalCount}, hasMore=${hasMore}`); return { items, totalCount, hasMore, countMap }; } catch (err) { const error = err as Error; Logger.error('heanup MediaTable', `queryGroupedPaged failed: ${error.message}`); throw error; } } private async getCountByColumn(columnName: string, value: string): Promise { const predicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); predicates.equalTo(columnName, value); predicates.equalTo(DB_COLUMNS.TYPE, CommonConstants.TYPE_LOCAL); return await this.getTotalCount(predicates); } /** * 更新歌曲的webdav_account_id * @param filePath 歌曲文件路径 * @param accountId 新的账号ID * @returns Promise 更新是否成功 */ public async updateWebDavAccountId(filePath: string, accountId: string): Promise { const normalizedPath = normalizeFilePath(filePath); return new Promise((resolve, reject) => { try { // 先查询记录是否存在 const queryPredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); queryPredicates.equalTo(DB_COLUMNS.FILE_PATH, normalizedPath); this.accountTable.query(queryPredicates, (resultSet: relationalStore.ResultSet) => { try { if (resultSet.rowCount === 0) { Logger.warn(RdbUtils.RDB_TAG, `updateWebDavAccountId: 未找到记录 ${normalizedPath}`); resolve(false); resultSet.close(); return; } resultSet.close(); // 更新webdav_account_id const updatePredicates = new relationalStore.RdbPredicates(RdbUtils.MEDIA_TABLE.tableName); updatePredicates.equalTo(DB_COLUMNS.FILE_PATH, normalizedPath); const valueBucket: relationalStore.ValuesBucket = { webdav_account_id: accountId }; this.accountTable.updateData(updatePredicates, valueBucket, (success: boolean) => { if (success) { Logger.info(RdbUtils.RDB_TAG, `updateWebDavAccountId: 成功更新 ${normalizedPath} -> ${accountId}`); } else { Logger.error(RdbUtils.RDB_TAG, `updateWebDavAccountId: 更新失败 ${normalizedPath}`); } resolve(success); }); } catch (err) { Logger.error(RdbUtils.RDB_TAG, `updateWebDavAccountId error: ${(err as Error).message}`); reject(err); } }); } catch (err) { Logger.error(RdbUtils.RDB_TAG, `updateWebDavAccountId error: ${(err as Error).message}`); reject(err); } }); } } function generateBucket(item: VideoItem): relationalStore.ValuesBucket { let obj: relationalStore.ValuesBucket = {}; obj.id = item.id obj.name = item.name; const normalizedPath = normalizeFilePath(item.filePath); obj.filePath = normalizedPath; obj.mtype = item.type; obj.videoSize = item.videoSize; obj.cTime = item.cTime; obj.parentPath = item.parentPath; obj.isFav = item.isFav; // if(item.pixelMapToString){ // obj.pixelMapToString = item.pixelMapToString; // } if(item.artist){ obj.artist = item.artist; } if(item.album){ obj.album = item.album; } if(item.fileName){ obj.fileName = item.fileName; } if(item.size){ obj.size = item.size; } if(item.pixelMapPath){ obj.pixelMapPath = item.pixelMapPath; } if(item.isCustomCover !== undefined && item.isCustomCover !== null){ obj.isCustomCover = item.isCustomCover; } if(item.duration){ obj.duration = item.duration; } if(item.mimeType){ obj.mimeType = item.mimeType; } if(item.trackCount){ obj.trackCount = item.trackCount; } if(item.sampleRate){ obj.sampleRate = item.sampleRate; } if(item.lastPlayedStr){ obj.lastPlayedStr = item.lastPlayedStr; } if(item.playCount){ obj.playCount = item.playCount; } if(item.lyricContent){ obj.lyricContent = item.lyricContent; } if(item.md5Str){ obj.md5Str = item.md5Str; } if(item.extra_json){ obj.extra_json = item.extra_json; } if(item.pyStr){ obj.pyStr = item.pyStr; } if(item.bit_rate){ obj.bit_rate = item.bit_rate; } if(item.probe_score){ obj.probe_score = item.probe_score; } if(item.year){ obj.year = item.year; } if(item.nb_streams){ obj.nb_streams = item.nb_streams; } if(item.nb_programs){ obj.nb_programs = item.nb_programs; } if(item.genre){ obj.genre = item.genre; } if(item.track){ obj.track = item.track; } if(item.bits_per_raw_sample){ obj.bits_per_raw_sample = item.bits_per_raw_sample; } if(item.channels){ obj.channels = item.channels; } if(item.channel_layout){ obj.channel_layout = item.channel_layout; } if(item.start_time){ obj.start_time = item.start_time; } if(item.ALBUMARTIST){ obj.ALBUMARTIST = item.ALBUMARTIST; } if(item.COMPOSER){ obj.COMPOSER = item.COMPOSER; } if(item.LYRICIST){ obj.LYRICIST = item.LYRICIST; } if(item.COMMENT){ obj.COMMENT = item.COMMENT; } if(item.disc){ obj.disc = item.disc; } if(item.webdav_account_id){ obj.webdav_account_id = item.webdav_account_id; } if(item.remote_rel_path){ obj.remote_rel_path = item.remote_rel_path; } return obj; }