TrackOffical/utils/usqlite.js

/**
 * 对 SQLite 的 ORM 的封装处理
 * @time 2023-04-06
 * @version 2.0.0
 * @by wzx
 */

let config = {
	deBug: true,
	isConnect: false
}
// class Response {
// 	constructor(code, msg, data) {
// 		this.code = code;
// 		this.msg = msg;
// 		this.data = data;
// 	}
// 	toString() {
// 		return JSON.stringify(this);
// 	}
// }

class Utils {
	static modelSql(name, options) {
		let sql;
		let sqlArr = [];
		let primaryKeyArr = [];
		Utils.log('[modelSql] options:', options);
		
		for (const key in options) {
			if (Object.hasOwnProperty.call(options, key)) {
				const option = options[key];
				sqlArr.push(Utils.restrain(key, option));
				if (option.primaryKey == true) {
					primaryKeyArr.push(key.toString());
					Utils.log(`[modelSql] ${key} is primary key${primaryKeyArr.length}`);
				}
			}
		}
		
		Utils.log(primaryKeyArr.length);
		if (primaryKeyArr.length>1) {
			
			sql = `CREATE TABLE '${name}' (${sqlArr.join(', ').replaceAll(' PRIMARY KEY','')}, PRIMARY KEY (${primaryKeyArr.join()}))`;
		}
		else{
			sql = `CREATE TABLE '${name}' (${sqlArr.join(', ')})`;
		}
		Utils.log(`[modelSql] ${sql}`);
		return sql;
	}

	static restrain(key, options) {
		let restrainArray = [];
		restrainArray.push(`'${key}'`);

		// 如果是 String 拦截处理
		if (options.constructor != Object) {
			restrainArray.push(Utils.toType(options));
			return restrainArray.join(' ');
		}

		restrainArray.push(Utils.toType(options.type));
		
		// 主键
		if (options.primaryKey == true) {
			if(options.autoIncrement != true){
				restrainArray.push('PRIMARY KEY');
			}
		}
		
		// 自增
		if (Utils.isNumber(options.type)&&options.autoIncrement == true) {
			restrainArray.pop();
			restrainArray.push('INTEGER');
			restrainArray.push('PRIMARY KEY');
			restrainArray.push('AUTOINCREMENT');
		}
		
		// 非空
		if (options.notNull == true) {
			restrainArray.push('NOT NULL');
		}

		// 默认值
		if (options.default) {
			restrainArray.push(`DEFAULT ${options.default}`);
		}

		// 是否是不同的值
		if (options.unique == true) {
			restrainArray.push('UNIQUE');
		}

		// 检查
		if (options.check) {
			restrainArray.push(`CHECK(${THIS_VALUE.check})`);
		}

		return restrainArray.join(' ');
	}

	static toType(jsType) {
		let sqliteType = '';
		if (Utils.isNumber(jsType)) {
			sqliteType = 'numeric';
		} else if (Utils.isDate(jsType)) {
			sqliteType = 'timestamp';
		} else if (Utils.isBlob(jsType)) {
			sqliteType = 'BLOB';
		} else {
			sqliteType = 'varchar';
		}
		return sqliteType;
	}
	
	static log() {
		if (config.deBug) {
			console.log.apply(null, arguments);
		}
	}
	
	static warn() {
		if (config.deBug) {
			console.warn.apply(null, arguments);
		}
	}
	
	static error() {
		console.error.apply(null, arguments);
	}
	
	static isBlob(value){ return value === 'BLOB' }
	static isArray(value){ return Object.prototype.toString.call(value) === '[object Array]'}
	static isObject(value){ return Object.prototype.toString.call(value) === '[object Object]'}
	static isString(value){ return Object.prototype.toString.call(value) === '[object String]'}
	static isFunction(value){ return (value === Function || Object.prototype.toString.call(value) === '[object Function]')}
	static isNumber(value){ return (value === Number || Object.prototype.toString.call(value) === '[object Number]')}
	static isNaN(value){ return (Object.prototype.toString.call(value) === '[object Number]' && isNaN(value))}
	static isBoolean(value){ return Object.prototype.toString.call(value) === '[object Boolean]'}
	static isUndefined(value){ return Object.prototype.toString.call(value) === '[object Undefined]'}
	static isModel(value){ return Object.prototype.toString.call(value) === '[object Model]'}
	static isDate(value){ return (value === Date||Object.prototype.toString.call(value) === '[object Date]')}
}


/**
 * Model 对象内部public方法全部 return this;
 */
class Model {
	/**
	 * @constructor
	 * @param {String} name 数据库表名
	 * @param {} options 数据表列对象
	 * @returns 
	 */
	constructor() {
	// constructor(name, options) {
		// this.init(name, options)
		
		// let self = this;
		// self.name = name;
		// self.options = options;

		// if (config.isConnect) {
		// 	self.repair();
		// } else {
		// 	if(!config.name||!config.path){
		// 		console.error('"config.name" or "config.path" is empty');
		// 	}
		// 	usqlite.connect(config);
		// }
	}
	
	/**
	 * @param {String} name 数据库表名
	 * @param {} options 数据表列对象
	 * @returns 
	 */
	async init(name, options) {
		let self = this;
		self.name = name;
		self.options = options;
	
		if (config.isConnect) {
			await self.repair();
		} else {
			if(!config.name||!config.path){
				console.error('"config.name" or "config.path" is empty');
			}
			await usqlite.connect(config);
		}
	}

	/**
	 * @description 查询表数据
	 * @param {String|Array} options 
	 * - String  WHERE 内容
	 * - Array 需要查询的列
	 * @returns 
	 */
	find(options='') {
		let sql = '';
		// let self = this;
		// self.repair();
		
		if(!(Utils.isString(options)||Utils.isArray(options)||Utils.isFunction(options))) {
			Utils.error('The first parameter of Model.find should be "Array", "String" or "Function" (when there is only one parameter).')
		}
		if (options == '') {
			sql = `SELECT * FROM '${this.name}'`; // 查找全部
		} else if (Utils.isArray(options)) {
			sql = `SELECT ${options.join()} FROM '${this.name}'`; // 查找制定列
		} else if (Utils.isString(options)) {
			sql = `SELECT * FROM '${this.name}' WHERE ${options}`; // 制定条件查询
		}

		Utils.log(`[find]: ${sql}`);

		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: config.name,
				sql: sql,
				success(res) {
					console.log('[find] res:', res)
					resolve(res)
				},
				fail(err) {
					console.error('[find] err:', err)
					reject(err)
				}
			});
		});
	}

	/**
	 * @description 分页查询
	 * @param {Object} options :   { where:查询条件, number: 当前页数 , count : 每页数量 }
	 * @return
	 */
	limit(options) {
		let sql = '';
		// let self = this;
		// self.repair();
		if(!Utils.isObject(options)){
			Utils.error('The first parameter of Model.limit should be "Object".')
		}
		
		if (!options.where) {
			// 不存在 where
			sql =
				`SELECT * FROM '${this.name}' LIMIT ${options.count} OFFSET ${(options.number - 1) * options.count}`
		} else {
			// 存在 where
			sql =
				`SELECT * FROM '${this.name}' WHERE ${options.where} LIMIT ${options.count} OFFSET ${(options.number - 1) * options.count}`;
		};

		Utils.log(`[limit]: ${sql}`);

		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: config.name,
				sql: sql,
				success(res) {
					console.log('[limit] res:', res)
					resolve(res)
				},
				fail(err) {
					console.error('[limit] err:', err)
					reject(err)
				}
			});
		});
	}

	/**
	 * @description 插入数据
	 * @param {Object|Array} data: 需要插入的单个或者多个数据
	 */
	async insert(data) {
		// let self = this;
		// self.repair();

		if(!(Utils.isObject(data)||Util.isArray(data))){
			Utils.error('The first parameter of Model.insert should be "Object" or "Array".')
		}
		
		if (config.isConnect) {
			if (Utils.isArray(data)) {
				for (var i = 0; i < data.length; i++) {
					await this.insert(data[i]);
				}
				return
			} else if (Utils.isObject(data)) {
				let keys = [];
				let values = [];
				// let index = arguments[3]??null;
				for (var key in data) {
					keys.push(key);
					values.push(`'${data[key]}'`);
				}

				let sql = `INSERT INTO '${this.name}' (${keys.join()}) VALUES (${values.join()})`;

				Utils.log(`[insert]: ${sql}`);
				
				return new Promise((resolve, reject) => {
					plus.sqlite.executeSql({
						name: config.name,
						sql: sql,
						success(res) {
							// console.log('[insert] res:', res)
							resolve(res)
						},
						fail(err) {
							console.error('[insert] err:', err)
							reject(err)
						}
					})
				});					
			}
		}
	}

	/**
	 * @description 更新数据
	 * @param {Object} data： 修改后的数据 
	 * @param {String} options：可选参数 更新条件
	 */
	update(data, options='') {
		// let self = this;
		// self.repair();
		let sql = '';
		let items = [];
		
		if(!(Utils.isObject(data))){
			Utils.error('The first parameter of Model.update should be "Objrct".')
		}
		if(!(Utils.isObject(options)||Utils.isString(options))){
			Utils.error('The second parameter of Model.update should be "Object" or "String".')
		}
		
		for (var key in data) {
			items.push(`${key}='${data[key]}'`);
		};
		
		if (options == '') {
			sql = `UPDATE '${this.name}' SET ${items.join()}`;
		} else {
			sql = `UPDATE ${this.name} SET ${items.join()} WHERE ${options}`;
		};
		Utils.log(`[update]: ${sql}`);
		
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({
				name: config.name,
				sql: sql,
				success(res) {
					// console.log('[update] res:', res)
					resolve(res)
				},
				fail(err) {
					console.error('[update] err:', err)
					reject(err)
				}
			});
		});
	}

	/**
	 * @description 删除数据
	 * @param {String} options ：可选参数 删除条件
	 */
	delete(options='') {
		// let self = this;
		// self.repair();
		var sql = '';		
		
		if(!(Utils.isString(options)||Utils.isFunction(options))){
			Utils.error('The first parameter of Model.delete should be "Object" or "Function".')
		}

		if (options == '') {
			sql = `DELETE FROM '${this.name}'`;
		} else {
			sql = `DELETE FROM '${this.name}' WHERE ${options}`;
		};
		Utils.log(`[delete]: ${sql}`);
		
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({
				name: config.name,
				sql: sql,
				success(res) {
					console.log('[delete] res:', res)
					resolve(res)
				},
				fail(err) {
					console.error('[delete] err:', err)
					reject(err)
				}
			});
		});
	}

	/**
	 * @description  重命名或者新增列
	 * @param {Object|Array|String} options 参数 数组为新增多列 对象为新增单列{aa} 字符串重命名
	 * @return: 
	 */
	async alter(options) {
		let self = this;
		// self.repair();
		let sql = '';
		
		if(!(Utils.isObject(options)||Utils.isArray(options)||Utils.isString(options))){
			Utils.error('The first parameter of Model.alter should be "Object", "Array" or "String".')
		}
		
		if (Utils.isArray(options)) { // 新增多列
			for (let i = 0; i < options.length; i++) {
				await this.alter(options[i]);
			}
			return
		} else if (Utils.isObject(options)) { // 新增单列
			let column = Utils.restrain(options.name, options.option);
			sql = `ALTER TABLE '${this.name}' ADD COLUMN ${column}`
		} else if (options.constructor == String) { // 重命名
			sql = `ALTER TABLE '${this.name}' RENAME TO '${options}'`
		}
		Utils.log(`[alter]: ${sql}`);
		
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: config.name,
				sql: sql,
				success(res) {
					if (options.constructor == String) { // 重命名
						self.name = options;
					}
					// console.log('[alter] res:', res)
					resolve(res)
				},
				fail(err) {
					console.error('[alter] err:', err)
					reject(err)
				}
			});
		});
	}
	
	/**
	 * @description 
	 * @param {Model} model 右 Model
	 * @param {Object} options 
	 * @returns 
	 */
	join(model, options) {
		// let self = this;
		// self.repair();
		if (!model) {
			Utils.error('"model" cannot be empty.');
		}
		if (!Utils.isObject(options)) {
			Utils.error('The type of "options" is wrong, it should be "Object".');
		}
		if (!options.type || !options.predicate) {
			Utils.error('Missing required parameters');
		}

		let leftName = this.name;
		let rightName = model.name;
		let leftValue = options.predicate.left;
		let rightValue = options.predicate.right;
		let cols = ['*'];

		const SQL_MAP = {
			cross: `SELECT ${cols.join()} FROM ${leftName} CROSS JOIN ${rightName};`,
			inner: [`SELECT ${cols.join()} FROM ${leftName} NATURAL JOIN ${rightName}`,
			`SELECT ${cols.join()} FROM ${leftName}  INNER JOIN ${rightName} ON ${leftName}.${leftValue} = ${rightName}.${rightValue}`
			],
			outer: `SELECT ${cols.join()} FROM ${leftName}  OUTER JOIN ${rightName} ON ${leftName}.${leftValue} = ${rightName}.${rightValue}`
		}

		let sql = '';
		if (options.type == inner && !options.predicate) {
			sql = SQL_MAP[options.type][0];
		} else if (options.type == inner && !options.predicate) {
			sql = SQL_MAP[options.type][1];
		} else {
			sql = SQL_MAP[options.type];
		}

		Utils.log(`[join]: ${sql}`);
		
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: config.name,
				sql: sql,
				success(res) {
					console.log('[join] res:', res)
					resolve(res)
				},
				fail(err) {
					console.error('[join] err:', err)
					reject(err)
				}
			});
		});
	}

	/**
	 * @description 执行sql语句
	 * @param {String} sql : sql语句
	 */
	sql(sql) {
		if (!Utils.isString(sql)) {
			Utils.error('"The type of "sql" is wrong, it should be "String".');
		}
		
		// let self = this;
		// self.repair();

		Utils.log(`[sql]: ${sql}`);
		
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: config.name,
				sql: sql,
				success(res) {
					console.log('[sql] res:', res)
					resolve(res)
				},
				fail(err) {
					console.error('[sql] err:', err)
					reject(err)
				}
			});
		});
	}

	/**
	 * @description 判断表是否存在 
	 */
	insertMapData(arrayBuffer) {
		console.log('arrayBuffer', arrayBuffer)
		let zipFileData = this.byteArrayToHexString(arrayBuffer)
		console.log('zipFileData', zipFileData)
		
		let sql = `INSERT INTO 'mapInfo' (shopId,mapName,zipImageUrl,zipImageMd5,zipFileName,zipFileData) 
		VALUES ('1','路线图','http://www.beswell.com/download/齐源大厦_已修改.zip','MTExMQ==','齐源大厦_已修改.tif', x'${zipFileData}')`;
		
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({
				name: config.name,
				sql: sql,
				success(res) {
					// console.log('[insertMapData] res:', res)
					resolve(res)
				},
				fail(err) {
					console.error('[insertMapData] err:', err)
					reject(err)
				}
			});
		});
	}
	
	byteArrayToHexString(arrayBuffer) { // converts byte arrays to string
		let i, j, inn;
		let hex = ["0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", "D", "E", "F"];
		let out = "";
	
		for (j = 0; j < arrayBuffer.length; ++j) {
			inn = arrayBuffer[j] & 0xff;
			i = (inn >>> 4) & 0x0f;
			out += hex[i];
			i = inn & 0x0f;
			out += hex[i];
		}
		return out;
	}
	
	/**
	 * @description 判断表是否存在 
	 */
	isExist() {
		let sql = `SELECT count(*) AS isExist FROM sqlite_master WHERE type='table' AND name='${this.name}'`;
		// Utils.log(`[isExist] ${config.name}`);
		// Utils.log(`[isExist] ${sql}`);
		
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: config.name,
				sql: sql,
				success(res) {
					// console.log('[isExist] res:', res)
					if (res[0].isExist == 1)
						console.log(`[isExist] ${config.name} 已存在`);
					else
						console.log(`[isExist] ${config.name} 不存在`);
					resolve(res)
				},
				fail(err) {
					console.error('[isExist] err:', err)
					reject(err)
				}
			});
		});
	}
	
	/**
	 * @description 删除数据表 **不推荐**
	 */
	drop() {
		var sql = `DROP TABLE '${this.name}'`;
		// let self = this;
		// self.repair();

		Utils.log(`[drop]: ${sql}`);
		
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: config.name,
				sql: sql,
				success(res) {
					console.log('[drop] res:', res)
					resolve(res)
				},
				fail(err) {
					console.error('[drop] err:', err)
					reject(err)
				}
			});
		});
	}

	/**
	 * @description 创建数据表 **不推荐**
	 */
	create() {
		// let self = this;
		let sql = Utils.modelSql(this.name, this.options);
		Utils.log(`[create]: ${sql}`);
		
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: config.name,
				sql: sql,
				success(res) {
					// console.log('[create] res:', res)
					resolve(res)
				},
				fail(err) {
					console.error('[create] err:', err)
					reject(err)
				}
			});
		});
	}

	toString() {
		return `[${this.name} Model]`;
	}

	async repair() {
		let self = this;
		
		try {
			let res = await self.isExist()
			if (!res[0].isExist) {
				await self.create();
			}
		} catch(e) {
			console.error(e);
		}
		
	}

}


// 单例模式
export class usqlite {
	/**
	 * 构造函数
	 * @param {Object} options 数据库配置信息 *
	 * {name: 'demo', path: '_doc/demo.db'}
	 * - name 数据库名称*
	 * - path 数据库路径
	 */
	constructor(options) {
		console.warn('No instantiation');
	}
	
	/**
	 * @description 链接数据库
	 * @param {Object} options 数据库配置信息 *
	 * {name: 'demo', path: '_doc/demo.db'}
	 * - name 数据库名称*
	 * - path 数据库路径
	 */
	static connect(options) {
		config.name = options.name; // 数据库名称*
		config.path = options.path; // 数据库名称*

		if (config.isConnect) {
			console.warn('[connect] 数据库已连接，无需重连')
			return
		}
		
		return new Promise((resolve, reject) => {
			plus.sqlite.openDatabase({
				name: config.name, //数据库名称
				path: config.path, //数据库地址
				success(res) {
					config.isConnect = true;
					// console.log('[connect] res:', res)
					resolve(res)
				},
				fail(err) {
					if (e.code == -1402) {
						config.isConnect = true;
						console.warn('[connect] warn:', err)
					} else {
						config.isConnect = false;
						console.error('[connect] err:', err)
					}
					reject(err)
				}
			});
		});
	}
	
	/**
	 * @description 断开数据库
	 * @param {*} callback 
	 */
	static close() {
		return new Promise((resolve, reject) => {
			plus.sqlite.closeDatabase({
				name: config.name, //数据库名称
				path: config.path, //数据库地址
				success(res) {
					config.isConnect = false;
					// console.log('[close] res:', res)
					resolve(res)
				},
				fail(err) {					
					console.error('[close] err:', err)
					reject(err)
				}
			});
		});
	}
	
	/**
	 * @description 创建 Model 对象
	 * @example
	 * usqlite.model('demo',
	 * {
	 * 		id: {
	 * 			type: Number
	 * 		},
	 * 		content: String
	 * })
	 * @param {String} name 数据表名称 *
	 * @param {String} options 参数配置 *
	 * @returns 返回 Model 对象
	 */
	static async model(name, options) {
		Utils.log(config);
		// return new Model(name, options);
		let model = new Model();
		await model.init(name, options)
		return model
	}
}