TrackOffical/utils/__sqlite.js

/*
 * @Descripttion: sqlite 方法集合
 * @version: 1.0
 * 已弃用
 */

/**
 * 数据库初始化
 * @dbName 		数据库名
 * @version    	数据库版本
 **/
async function initDB(dbName, version) {
	let sqlversion = 9; // 数据库版本，只要对不上就进行更新每次需要更新数据库都要重设此字段
	if (sqlversion == version) {
		console.log("无需更新");
		let recode = {
			"version": sqlversion
		};
		return recode;
	}
	
	//需要进行更新的数据语句依次从后加入 sqlite不能执行set 操作，只能add
	let sqldata = [{
			"sql": 'create table if not exists sqldo(id INTEGER PRIMARY KEY AUTOINCREMENT,num INT NOT NULL DEFAULT 0--执行数据语句号num\n); --数据库更新版本库\n',
			"num": '1'
		},
		{
			"sql": 'create table if not exists bills(id INTEGER PRIMARY KEY AUTOINCREMENT,remarks TEXT,time VARCHAR(80) NOT NULL,amount FLOAT NOT NULL,classifi VARCHAR(80) NOT NULL,btype TINYINT NOT NULL,uid INT NOT NULL); --账单表\n',
			"num": '2'
		},
		{
			"sql": 'create table if not exists plans(id INTEGER PRIMARY KEY AUTOINCREMENT,remarks TEXT, --备注\n time VARCHAR(80) NOT NULL, --完成时间\n title TEXT, --名称\n classifi VARCHAR(80) NOT NULL, --分类\n btype TINYINT NOT NULL, --大类别0临时1常规\n uid INT NOT NULL --用户id\n); --计划备忘录\n',
			"num": '3'
		},

		//{"sql":'insert into sqldo (num) values (4); --完成状态-1未完成1已经完成\n',"num":'4'},
		{
			"sql": 'ALTER TABLE plans ADD COLUMN amount TINYINT DEFAULT -1; --完成状态-1未完成1已经完成\n',
			"num": '4'
		}, //计划表更新字段
		{
			"sql": 'ALTER TABLE plans ADD COLUMN endtime VARCHAR(80) DEFAULT 0; --结束时间\n',
			"num": '5'
		},
		{
			"sql": 'ALTER TABLE plans ADD COLUMN donum TINYINT DEFAULT 0; --完成度0~100\n',
			"num": '6'
		},
		{
			"sql": 'ALTER TABLE plans ADD COLUMN deletetime VARCHAR(80) DEFAULT 0; --删除时间0就是未删除\n',
			"num": '7'
		},
		{
			"sql": 'ALTER TABLE bills ADD COLUMN deletetime VARCHAR(80) DEFAULT 0; --删除时间0就是未删除\n',
			"num": '8'
		},
		
	];
	//查询数据库版本表是否存在，如果不存在就全部更新
	let istable = await queryIsTableinit(databaseName, "sqldo");
	if (istable) { //先进行判断需要执行哪些语句
		//查询数据库更新的版本
		let setDatasql = 'select * from sqldo;';
		let sedatalist = await selectDataListsqlonly(databaseName, setDatasql);
		sedatalist.forEach((item, index) => { //从数据中循环读取，再进行比对，如果没有的就进行执行语句，并添加到数据库
			sqldata.forEach((items, indexs) => {
				if (item.num == items.num) {
					sqldata.splice(indexs, 1);
				}
			});
		});
		if (sqldata.length == 0) { //空数据没有执行语句
			console.log("判断完毕，无需更新");
			let recode = {
				"code": sqlsqlcode,
				"version": sqlversion
			};
			return recode;
		}
	}
	//开始执行sql语句
	for (var i = 0; i < sqldata.length; i++) {
		let sqlone = sqldata[i].sql;
		let obj = {
			"num": sqldata[i].num
		};
		let sqlcode = await queryDBsqloney(databaseName, sqlone);
		if (sqlcode == 100) { //成功执行添加操作
			let addcode = await addSaveData(databaseName, "sqldo", obj);
			if (addcode == 200) {
				sqlsqlcode = 200;
			}
		}
	}
	let recode = {
		"code": sqlsqlcode,
		"version": sqlversion
	};
	return recode;


	/*
	let sql=[
		'create table if not exists bills(id INTEGER PRIMARY KEY AUTOINCREMENT,remarks TEXT,time VARCHAR(80) NOT NULL,amount FLOAT NOT NULL,classifi VARCHAR(80) NOT NULL,btype TINYINT NOT NULL,uid INT NOT NULL); --账单表\n',//账单
		'create table if not exists plans(id INTEGER PRIMARY KEY AUTOINCREMENT,remarks TEXT, --备注\n time VARCHAR(80) NOT NULL, --完成时间\n title TEXT, --名称\n classifi VARCHAR(80) NOT NULL, --分类\n btype TINYINT NOT NULL, --大类别0临时1常规\n uid INT NOT NULL --用户id\n); --计划备忘录\n',
		'ALTER TABLE plans ADD COLUMN amount TINYINT DEFAULT -1; --完成状态-1未完成1已经完成\n',
		'ALTER TABLE plans ADD COLUMN endtime VARCHAR(80) DEFAULT 0; --结束时间\n',
		'ALTER TABLE plans ADD COLUMN donum TINYINT DEFAULT 0; --完成度0~100\n'
	];
	openDBinit(databaseName);
    plus.sqlite.executeSql({  
        name: databaseName,  
        sql: sql,
		 // sql:['DROP TABLE plans;','DROP TABLE plansss;','DROP TABLE planssss;'],//删除表
        success: function(e){  
            console.log('executeSql2222 success!');   
        },  
        fail: function(e){  
            console.log('executeSql failed: '+JSON.stringify(e));  
        }  
    }); 
	 */
}

// 创建表
function createTable(dbName, tableName, tableStructure) {
	// 注意：tableName不能用数字作为表格名的开头
	return new Promise((resolve, reject) => {
		plus.sqlite.executeSql({
			name: dbName,
			// sql: 'create table if not exists dataList("list" INTEGER PRIMARY KEY AUTOINCREMENT,"id" TEXT,"name" TEXT,"gender" TEXT,"avatar" TEXT)',
			sql: `create table if not exists ${tableName}(${tableStructure})`,
			success(e) {
				resolve(e);
			},
			fail(e) {
				console.log(e)
				reject(e);
			}
		})
	})
}

// 查询表是否存在
function isTableExist(dbName, tableName) {
	return new Promise((resolve, reject) => {
		plus.sqlite.selectSql({
			name: dbName,
			sql: `select count(*) as isTable FROM sqlite_master where type='table' and name='${tableName}'`,
			success(e) {
				resolve(e[0].isTable ? true : false);
			},
			fail(e) {
				console.log(e)
				reject(e);
			}
		})
	})
}

// 判断数据库是否打开
function isOpenDB(dbName) {
	let dbPath = `_doc/${dbName}_record.db`;
	//数据库打开了就返回true,否则返回false
	let isopen = plus.sqlite.isOpenDatabase({
		name: dbName,
		path: dbPath
	})
	return isopen
}

// 创建数据库/打开数据库
function openDB(dbName) {
	return new Promise((resolve, reject) => {
		plus.sqlite.openDatabase({
			name: dbName || 'testData',
			path: `_doc/${dbName}_record.db`,
			success: function() {
				resolve('openDatabase success!')
			},
			fail: function(e) {
				reject('openDatabase failed: ' + JSON.stringify(e))
			}
		});
	})
}

// 查询所有数据库表名
function getDbTables(dbName) {
	return new Promise((resolve, reject) => {
		plus.sqlite.selectSql({
			name: dbName,
			sql: "select * FROM sqlite_master where type='table'",
			success(e) {
				console.log(e)
				resolve(e);
			},
			fail(e) {
				console.log(e)
				reject(e);
			}
		})
	})
}

// 添加数据
function insertData(dbName, tableName, obj) {
	if (obj) {
		let keys = Object.keys(obj)
		let keyStr = keys.toString()
		let valStr = ''
		keys.forEach((item, index) => {
			if (keys.length - 1 == index) {
				valStr += ('"' + obj[item] + '"')
			} else {
				valStr += ('"' + obj[item] + '",')
			}
		})
		// console.log(valStr)
		let sqlStr = `insert into ${tableName}(${keyStr}) values(${valStr})`
		// console.log(sqlStr)
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({
				name: dbName,
				sql: sqlStr,
				success(e) {
					console.log(e);
					resolve(100);
				},
				fail(e) {
					console.log(e)
					reject(e);
				}
			})
		})
	} else {
		return new Promise((resolve, reject) => {
			reject("错误")
		})
	}
}

/*
 ** 执行查询的SQL语句
 */
function selectSql(dbName, sql) {
	return new Promise((resolve, reject) => {
		plus.sqlite.selectSql({
			name: dbName,
			sql: sql,
			success(e) {
				console.log(e)
				resolve(e);
			},
			fail(e) {
				console.log(e)
				reject(e);
			}
		})
	})
}

/*
 ** 执行增删改等操作的SQL语句
 */
function executeSql(dbName, sql) {
	return new Promise((resolve, reject) => {
		plus.sqlite.executeSql({
			name: dbName,
			sql: sql,
			success(e) {
				console.log(e)
				resolve(e);
			},
			fail(e) {
				console.log(e)
				reject(e);
			}
		})
	})
}

/*
 ** 执行事务
 * @param {string} dbName operation
 * @param {string} operation 需要执行的事务操作 可选值：begin（开始事务）、commit（提交）、rollback（回滚）
 */
function transaction(dbName, operation) {
	return new Promise((resolve, reject) => {
		plus.sqlite.transaction({
			name: dbName,
			operation: operation,
			success(e) {
				console.log(e)
				resolve(e);
			},
			fail(e) {
				console.log(e)
				reject(e);
			}
		})
	})
}

// 获取数据库分页数据
/**
 * 
 * @param {*} dbName 
 * @param {*} tableName 
 * @param {*} num 页码
 * @param {*} size 页面大小返回条数
 * @param {*} byName 排序主键字段
 * @param {*} byType 排序类型 desc倒序 / asc正序
 */
async function queryDataPageList(dbName, tableName, num, size, byName, byType) {
	let count = 0
	let sql = ''
	let numindex = 0
	await queryCount(dbName, tableName).then((resNum) => {
		count = Math.ceil(resNum[0].num / size)
	})
	if (((num - 1) * size) == 0) {
		numindex = 0
	} else {
		numindex = ((num - 1) * size) + 1
	}
	sql = `select * from ${tableName}`
	if (byName && byType) {
		// desc asc
		sql += ` order by ${byName} ${byType}`
	}
	sql += ` limit ${numindex},${size}`
	if (count < num - 1) {
		return new Promise((resolve, reject) => {
			reject("无数据")
		});
	} else {
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: dbName,
				// sql: "select * from userInfo limit 3 offset 3",
				sql: sql,
				success(e) {
					resolve(e);
				},
				fail(e) {
					reject(e);
				}
			})
		})
	}
}

// 查询表数据总条数
function queryCount(dbName, tableName) {
	return new Promise((resolve, reject) => {
		plus.sqlite.selectSql({
			name: dbName,
			sql: "select count(*) as num from " + tableName,
			success(e) {
				resolve(e);
			},
			fail(e) {
				reject(e);
			}
		})
	})
}

// 修改（更新）数据
// 示例：UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;
// UPDATE 表名 SET 要修改字段 = '修改内容' WHERE 筛选条件 = 6;
/**
 * 
 * @param {*} dbName 数据库名
 * @param {*} tableName 表名
 * @param {*} setData 设置值 （修改字段 + 修改内容）
 * @param {*} setName 筛选条件
 * @param {*} setVal 筛选值
 * @returns 
 */
function updateSqlData(dbName, tableName, setData, setName, setVal) {
	if (JSON.stringify(setData) !== '{}') {
		let dataKeys = Object.keys(setData)
		let setStr = ''
		dataKeys.forEach((item, index) => {
			// console.log(item, setData[item])
			setStr += (
				`${item} = ${JSON.stringify(setData[item])}${dataKeys.length - 1 !== index ? "," : ""}`)
		})
		console.log(setStr)
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({
				name: dbName,
				sql: `update ${tableName} set ${setStr} where ${setName} = "${setVal}"`,
				success(e) {
					//resolve(e);
					resolve(100);
				},
				fail(e) {
					console.log(e)
					reject(e);
				}
			})
		})
	} else {
		return new Promise((resolve, reject) => {
			reject("错误")
		});
	}
}

// 关闭数据库
function closeDB(dbName) {
	return new Promise((resolve, reject) => {
		plus.sqlite.closeDatabase({
			name: dbName,
			success: function(e) {
				resolve('closeDatabase success!');
			},
			fail: function(e) {
				reject('closeDatabase failed: ' + JSON.stringify(e));
			}
		});
	})
}