uniIM/api/DBactApi.ts

import type Message from '@/mode/Message'
import type Chat from '@/mode/Chat'

import Auth from "@/api/Auth";

const dbPath = '_doc/locateDB.db'
const name = 'imdata'

class DBactApi {
	static openDb = () :Boolean => {//打开数据库
	    return new Promise((resolve, reject) => {
			plus.sqlite.openDatabase({
				name:name,
				path:dbPath,
				success: res=> {
					console.log('打开数据库',res)
					resolve(true)
				},
				fail: function (err) {
					console.log('打开数据库 false',err)
					reject(false)
				},
			})
		})
	};
	static DBisOpen = () :Boolean => {//判断数据库是否打开
		if(plus.sqlite.isOpenDatabase({name:name,path:dbPath})){
			console.log('判断数据库是否打开')
			return true;
		}
		else{
			console.log('判断数据库是否打开 false')
			return false;
		}
	};
	
	static closeDB = () :Boolean => {//关闭数据库
		return new Promise((resolve, reject) => {
			plus.sqlite.closeDatabase({
				name:name,
				success: res=> {
					resolve(true)
				},
				fail: function (err) {
					reject(false)
				},
			})
		})
	};
	static deleatDB = () :Boolean => {//删除数据库
		return new Promise((resolve, reject) => {
			uni.removeSavedFile({
				filePath:dbPath,
				success() {
					console.log('数据库删除成功');
					resolve(true)
				},
				fail() {
					console.log('数据库删除失败');
					reject(false)
				}
			})
		})
	};
	// "id": "1813376960010895360",
	// "chatId": "1808714465480523777",
	// "type": "1",
	// "messageType": "0",
	// "content": "1111",
	// "mine": false,
	// "fromId": "48",
	// "timestamp": 1721177732838,
	// "extend": {
	//     "atAll": false,
	//     "atUserIds": []
	// },
	// "video": false,
	// "duration": 0,
	// "result": false,
	// "offline": false
	//创建消息表
	static createMSGtable(){
		
		plus.sqlite.executeSql({
			name:name,
			sql:'create table if not exists msglist_'+Auth.getUserName()+'("id" CHAR PRIMARY KEY,"chatId" CHAR,"type" CHAR,"messageType" CHAR,"content" TEXT,"mine" int,"fromId" CHAR,"timestamp" BIGINT,"extend" TEXT,"video" int,"duration" int,"result" int,"offline" int)',
			success: res=> {
				console.log('创建消息表',res)
				return res;
			},
			fail: function (err) {
				console.log(err)
				return false;
			},
		})
	};
	
	static delmsglistTable(username:string){//删除消息表
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({
				name:name,
				sql:'DROP TABLE msglist_'+username,
				success: res=> {
					console.log('DROP TABLE',res);
					resolve(true)
				},
				fail: function (err) {
					reject(false)
				},
			})
		})
	};
	static insertmsg(msg: Message){//插入消息
		var values="('"+msg.id+"','"+msg.chatId+"','"+msg.type+"','"+msg.messageType+"','"+msg.content+"',"+msg.mine+",'"+msg.fromId+"',"+msg.timestamp+",'"+JSON.stringify(msg.extend)+"')";
		
		plus.sqlite.executeSql({
			name:name,
			sql:"insert into msglist_"+Auth.getUserName()+"('id','chatId','type','messageType','content','mine','fromId','timestamp','extend') values " +values,
			success: res=> {
				console.log('insertmsg11',res)
				return res;
			},
			fail: function (err) {
				console.log('insertmsg22',err)
				return false;
			},
		})
	};
	static selectmsg(chatId:string){//查询消息
		return new Promise((resolve, reject) => {
			var sql='select * from(select * from msglist_'+Auth.getUserName()+' where chatId = '+chatId+' ORDER BY "id" DESC LIMIT 10) ORDER BY "id"';
			//console.log('sql',sql);
			plus.sqlite.selectSql({
				name: name,
				sql:sql,// 'select * from userfl',
				success: function(res){
					console.log('selectSql success:',res);
					resolve(res)
				},
				fail: function(e){
					console.log('UserMsg: ',e);
					reject(false)
				}
			});
		})
	};
	
	static deletemsgs(idlist:Array){//删除多个消息 DELETE FROM users WHERE id IN (100, 101);
		var values='("'+JSON.stringify(idlist)+'")';
		var sql='DELETE FROM msglist_'+Auth.getUserName()+' WHERE id IN'+values;
		// console.log('sql',sql);
		// console.log(name);
		plus.sqlite.executeSql({
			name: name,
			sql:sql,// 'select * from userfl',
			success: function(data){
				//console.log('executeSql success: ',data);
				return data;
			},
			fail: function(e){
				console.log('UserMsg: ',e);
				return false;
			}
		});
	};
	static deletemsg(msg: Message){//删除单个消息 DELETE FROM users WHERE id IN (100, 101);
		var values='("'+msg.id+'")';
		var sql='DELETE FROM msglist_'+Auth.getUserName()+' WHERE id IN'+values;
		// console.log('sql',sql);
		// console.log(name);
		plus.sqlite.executeSql({
			name: name,
			sql:sql,// 'select * from userfl',
			success: function(data){
				//console.log('executeSql success: ',data);
				return data;
			},
			fail: function(e){
				//console.log('UserMsg: ',e);
				return false;
			}
		});
	}
	
	//-- 假设我们要将students表中id为1的学生的名字改为"John Doe"
	//UPDATE students SET name = 'John Doe' WHERE id = 1;
	static updataemsg(sql:string){
		plus.sqlite.executeSql({
			name: name,
			sql:sql,// 'select * from userfl',
			success: function(data){
				//console.log('executeSql success: ',data);
				return data;
			},
			fail: function(e){
				console.log('UserMsg: ',e);
				return false;
			}
		});
	}
	
	//-----------------------------------------------------------------------------------
	static createchattable(){
		plus.sqlite.executeSql({
			name:name,
			sql:'create table if not exists chatlist_'+Auth.getUserName()+'("id" CHAR PRIMARY KEY,"name" CHAR,"avatar" CHAR,"type" CHAR,"lastMessage" TEXT,"lastTime" BIGINT,"unreadCount" int,"loaded" int,"loading" int)',
			success: res=> {
				console.log('创建消息表',res)
				return res;
			},
			fail: function (err) {
				console.log(err)
				return false;
			},
		})
	};
	
	static delchatlistTable(username:string){//删除消息表
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({
				name:name,
				sql:'DROP TABLE chatlist_'+username,
				success: res=> {
					//console.log('DROP TABLE',res);
					resolve(true)
				},
				fail: function (err) {
					reject(false)
				},
			})
		})
	};
	static insertchat(chat: Chat){//插入消息
		var values="('"+chat.id+"','"+chat.name+"','"+chat.avatar+"','"+chat.type+"','"+chat.lastMessage+"',"+chat.lastTime+","+chat.unreadCount+","+chat.loaded+","+chat.loading+")";
		plus.sqlite.executeSql({
			name:name,
			sql:"INSERT OR REPLACE INTO chatlist_"+Auth.getUserName()+"('id','name','avatar','type','lastMessage','lastTime','unreadCount','loaded','loading') values " +values,
			success: res=> {
				//console.log('insertmsg1',res)
				return res;
			},
			fail: function (err) {
				//console.log('insertmsg2',err)
				return false;
			},
		})
	};
	static selectchats(){//查询消息
		return new Promise((resolve, reject) => {
			var sql='select * from chatlist_'+Auth.getUserName()+';';
			console.log('sql',sql);
			plus.sqlite.selectSql({
				name: name,
				sql:sql,// 'select * from userfl',
				success: function(res){
					//console.log('selectchats success:',res);
					resolve(res)
				},
				fail: function(e){
					console.log('UserMsg: ',e);
					reject(false)
				}
			});
		})
	};
	//重整消息本地数据库----------------------------------------
	//创建消息表
	static createLocalMSGtable(){
		
		plus.sqlite.executeSql({
			name:name,
			sql:'create table if not exists lcmsglist_'+Auth.getUserName()+'("id" CHAR PRIMARY KEY,"chatId" CHAR,"type" CHAR,"messageType" CHAR,"timestamp" BIGINT,"Message" TEXT)',
			success: res=> {
				console.log('创建消息表',res)
				return res;
			},
			fail: function (err) {
				console.log(err)
				return false;
			},
		})
	};
	
	static delLocalmsglistTable(username:string){//删除消息表
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({
				name:name,
				sql:'DROP TABLE lcmsglist_'+username,
				success: res=> {
					console.log('DROP TABLE',res);
					resolve(true)
				},
				fail: function (err) {
					reject(false)
				},
			})
		})
	};
	static insertLocalmsg(msg: Message){//插入消息
		var values="('"+msg.id+"','"+msg.chatId+"','"+msg.type+"','"+msg.messageType+"',"+msg.timestamp+",'"+JSON.stringify(msg)+"')";
		//console.log(msg);
		plus.sqlite.executeSql({
			name:name,
			sql:"insert into lcmsglist_"+Auth.getUserName()+"('id','chatId','type','messageType','timestamp','Message') values " +values,
			success: res=> {
				//console.log('insertmsg1',res)
				return res;
			},
			fail: function (err) {
				// console.log('insertmsg2',err)
				return false;
			},
		})
	};
	static selectLocalmsg(chatId:string){//查询消息
		return new Promise((resolve, reject) => {
			var sql='select * from(select * from lcmsglist_'+Auth.getUserName()+' where chatId = '+chatId+' ORDER BY "timestamp" DESC LIMIT 10) ORDER BY "timestamp"';
			//console.log('sql',sql);
			plus.sqlite.selectSql({
				name: name,
				sql:sql,// 'select * from userfl',
				success: function(res){
					//console.log('selectSql success:',res);
					resolve(res)
				},
				fail: function(e){
					//console.log('UserMsg: ',e);
					reject(false)
				}
			});
		})
	};
	static selectLocalmsgType(chatId:string,messageType:string,page:number){//查询消息
		return new Promise((resolve, reject) => {
			var sql='';
			if(messageType&&messageType!=''){
				sql='select * from(select * from lcmsglist_'+Auth.getUserName()+' where chatId = '+chatId+' AND messageType='+messageType+' ORDER BY "timestamp" DESC LIMIT '+(page-1)*20+',20) ORDER BY "timestamp" DESC';
			}
			else{
				sql='select * from(select * from lcmsglist_'+Auth.getUserName()+' where chatId = '+chatId+' ORDER BY "timestamp" DESC LIMIT '+(page-1)*20+',20) ORDER BY "timestamp" DESC';
			}
			console.log('sql',sql);
			plus.sqlite.selectSql({
				name: name,
				sql:sql,// 'select * from userfl',
				success: function(res){
					//console.log('selectSql success:',res);
					resolve(res)
				},
				fail: function(e){
					//console.log('UserMsg: ',e);
					reject(false)
				}
			});
		})
	};
	
	static deleteLocalmsgs(idlist:Array){//删除多个消息 DELETE FROM users WHERE id IN (100, 101);
		var values='("'+JSON.stringify(idlist)+'")';
		var sql='DELETE FROM lcmsglist_'+Auth.getUserName()+' WHERE id IN'+values;
		// console.log('sql',sql);
		// console.log(name);
		plus.sqlite.executeSql({
			name: name,
			sql:sql,// 'select * from userfl',
			success: function(data){
				//console.log('executeSql success: ',data);
				return data;
			},
			fail: function(e){
				console.log('UserMsg: ',e);
				return false;
			}
		});
	};
	static deleteLocalmsg(msg: Message){//删除单个消息 DELETE FROM users WHERE id IN (100, 101);
		var values='("'+msg.id+'")';
		var sql='DELETE FROM lcmsglist_'+Auth.getUserName()+' WHERE id IN'+values;
		console.log('sql',sql);
		// console.log(name);
		plus.sqlite.executeSql({
			name: name,
			sql:sql,// 'select * from userfl',
			success: function(data){
				console.log('executeSql success: ',data);
				return data;
			},
			fail: function(e){
				console.log('UserMsg: ',e);
				return false;
			}
		});
	}
	static deleteMyLocalmsg(msg: Message){//删除单个消息 DELETE FROM users WHERE id IN (100, 101);
		var sql='DELETE FROM lcmsglist_'+Auth.getUserName()+' WHERE id=='+JSON.stringify(msg.localtime);
		console.log('sql',sql);
		// console.log(name);
		plus.sqlite.executeSql({
			name: name,
			sql:sql,// 'select * from userfl',
			success: function(data){
				//console.log('deleteMyLocalmsg success: ',data);
				return data;
			},
			fail: function(e){
				console.log('deleteMyLocalmsg: ',e);
				return false;
			}
		});
	}
	
}
export default DBactApi;