TrackOffical/utils/SqliteHelper.js

import { usqlite } from '@/utils/usqlite.js'

var dbVersionModel, mapInfoModel
export default {
	/**
	 * 打开数据库
	 * @param {string} dbName 数据库的名字
	 * @param {int} version 数据库的版本
	 * @return {object} 该函数会返回一个数据库实例
	 */
	async openDB(dbName = 'Asistent', version = 1.00) {
		try {
			console.log(`[openDB] name: ${dbName} version: ${version}`);
			await usqlite.connect({
				name: dbName, // 数据库名称
				path:`_doc/${dbName}_record.db`, // 路径
			});
			console.log(`数据库打开成功 name: ${dbName} version: ${version}`);
			
			if (dbName == 'Asistent') {
				dbVersionModel = await usqlite.model('dbVersion',{
					version:{
						type: Number,
						default: 0
					}
				});
				
				mapInfoModel = await usqlite.model('mapInfo',{
					shopId:{
						type: Number,
						primaryKey: true
					},
					mapName:{
						type: String,
					},
					zipImageMd5:{
						type: String,
					},
					zipImageUrl:{
						type: String,
					},
					zipFileName:{
						type: String,
					},
					zipFileKey:{
						type: String,
					},
					// zipFilePath:{
					// 	type: String,
					// },
					// zipFileData:{
					// 	type: 'BLOB',
					// }
				});
			
				let resDbVersion = await dbVersionModel.find()
				// console.log("dbVersion:", resDbVersion);
				
				let localVersion = (resDbVersion.length > 0) ? resDbVersion[0].version : ''
				console.log("localVersion:", localVersion);
				if (localVersion == '') {
					await dbVersionModel.insert({ version: version })
				}
				else if (localVersion < version) {
					console.warn(`本地数据库版本低，需要升级！ current:${localVersion} New:${version}`);
					if (localVersion == 1) {
						await mapInfoModel.alter([
							{ name: 'zipFileName', option: String },
							{ name: 'zipFileKey', option: String }
							// { name: 'zipFilePath', option: String }
							// { name: 'zipFileData', option: 'BLOB' }
						])
					}
					await dbVersionModel.update({ version: version })
				}
			}
			return usqlite;
		} catch (e) {
			console.log('[openDB] err', e)
			throw e
		}
	},
	
	async insertMapData(tableName, arrayBuffer) {
		try {
			if (tableName == 'mapInfo') {
				let res = await mapInfoModel.insertMapData(arrayBuffer) 
				console.log('[insertMapData] 成功');
				return res
			}
		} catch (e) {
			console.error("[insertMapData] 失败", e);
			throw e
		}
	},
	
	/**
	 * 新增数据
	 * @param {string} tableName 表名
	 * @param {string} data 数据
	 */
	async insertData(tableName, data) {
		try {
			if (tableName == 'mapInfo') {
				let res = await mapInfoModel.insert(data)
				console.log('[insertData] 数据写入成功');
				return res
			}
		} catch (e) {
			console.error("[insertData] 数据写入失败", e);
			throw e
		}
	},
	
	/**
	 * 更新数据
	 * @param {object} db 数据库实例
	 * @param {string} tableName 表名
	 * @param {object} data 数据
	 */
	async updateData(tableName, data, options='') {
		try {
			if (tableName == 'mapInfo') {
				let res = await mapInfoModel.update(data, options)
				console.log('[updateData] 数据更新成功');
				return res
			}
		} catch (e) {
			console.error("[updateData] 数据更新失败", e);
			throw e
		}
	},
	
	/**
	 * 通过主键删除数据
	 * @param {object} db 数据库实例
	 * @param {string} tableName 表名
	 * @param {object} key 主键值
	 */
	async deleteDataByKey(tableName, key) {
		try {
			if (tableName == 'mapInfo') {
				let options = 'shopId = ' + key
				let res = await mapInfoModel.delete(options)
				console.log('[deleteDataByKey] 数据删除成功');
				return res
			}
		} catch (e) {
			console.error("[deleteDataByKey] 数据删除失败", e);
			throw e
		}			
	},
	
	/**
	 * 通过主键读取数据
	 * @param {object} db 数据库实例
	 * @param {string} tableName 表名
	 * @param {string} key 主键值
	 */
	async getDataByKey(tableName, key) {
		try {
			if (tableName == 'mapInfo') {
				let options = 'shopId = ' + key
				let res = await mapInfoModel.find(options)
				console.log('[getDataByKey] 数据读取成功', res);
				return res[0]
			}
		} catch (e) {
			console.error("[getDataByKey] 数据读取失败", e);
			throw e
		}
	},
	
	/**
	 * 关闭数据库
	 * @param {object} db 数据库实例
	 */
	async closeDB() {
		try {
			await usqlite.close();
			console.log("关闭数据库成功");
		} catch (e) {
			console.error("[closeDB] 关闭数据库失败", e);
			throw e
		}			
	}
}