QTApp_800/uni_modules/uni-sqlContext/utssdk/app-android/index.uts

import Cursor from 'android.database.Cursor';
import SQLiteDatabase from 'android.database.sqlite.SQLiteDatabase';
import SQLiteOpenHelper from 'android.database.sqlite.SQLiteOpenHelper';
import Environment from 'android.os.Environment';

import { createSQLiteContextOptions, executeSqlOptions, selectSqlOptions, executeSqlOptionsResult, selectSqlOptionsResult, CreateSQLiteContext, transactionOptions, transactionResult, ICreateSQLiteContextError } from '../interface.uts';
//import { createSQLiteContextOptions, executeSqlOptions, selectSqlOptions, transactionOptions } from '../interface.uts';
import { createSQLiteContextFailImpl } from '../unierror.uts';

class SQLiteContext extends SQLiteOpenHelper {
	private dbName : string | null;
	private forceRecreateTables : boolean;

	constructor(name : string, forceRecreate : boolean = false) {
		// 使用应用内部存储路径而不是静态资源路径
		const context = UTSAndroid.getAppContext()!!;
		// 版本号 - 必须在super调用前定义

		const dbPath = context.getDatabasePath(name).getAbsolutePath();
		console.log("Creating database at: " + dbPath);

		// 只有在明确需要强制重新创建表时，才删除现有数据库
		if (forceRecreate) {
			console.log("Force recreating tables requested, deleting existing database if exists");
			context.deleteDatabase(name);
		}

		//1版本号
		super(UTSAndroid.getAppContext()!!, name, null, 1);
		this.dbName = name;
		this.forceRecreateTables = forceRecreate;

		// 立即获取可写数据库以触发初始化
		console.log("Initializing database connection");
		this.getWritableDatabase();
	}


	override onCreate(db : SQLiteDatabase) : void {
		console.log("SQLiteContext onCreate() called - creating database tables");

		// 创建表结构的DDL语句
		// 这里可以根据实际需要添加更多的CREATE TABLE语句
		const createTableSQLs = [
			// app_user表结构
			`CREATE TABLE IF NOT EXISTS app_user (
          id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
          username TEXT NOT NULL,
          name TEXT,
          userno INTEGER,
          password TEXT NOT NULL,
          token TEXT,
          status INTEGER DEFAULT 1,
          avatar TEXT,
          createtime TEXT,
          createuser INTEGER,
          updatetime TEXT,
          updateuser INTEGER,
          role TEXT DEFAULT 'user',
          UNIQUE (username ASC),
          UNIQUE (userno ASC)
        )`,

			// 日志表
			`CREATE TABLE "app_log" ( 
       "id" INTEGER NOT NULL UNIQUE, 
       "module" text, 
       "dataid" INTEGER, 
       "content" text, 
       "status" text, 
       "params" text, 
       "createtime" text, 
       "createuser" integer, 
       "updatetime" text, 
       "updateuser" integer, 
       PRIMARY KEY("id" AUTOINCREMENT) 
      )`,

			// 声像记录信息表
			`CREATE TABLE "app_media_info" ( 
        "pdid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
        "workorder" TEXT, 
        "invname" TEXT, 
        "productno" TEXT, 
        "cardno" TEXT, 
        "model" TEXT, 
        "graphid" TEXT, 
        "ver" TEXT, 
        "phase" TEXT, 
        "processno" TEXT, 
        "progress" TEXT, 
        "status" INTEGER, 
        "createtime" TEXT, 
        "createuser" text, 
        "updatetime" TEXT, 
        "updateuser" text, 
        "uploadFlag" integer DEFAULT 0, 
        "uploadtime" text, 
        UNIQUE ("pdid" ASC) 
      )`,

			// 声像记录记录表
			`CREATE TABLE "app_media_record" ( 
        "sxid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
        "senum" TEXT, 
        "photoitem" TEXT, 
        "productno" TEXT, 
        "part" TEXT, 
        "partno" TEXT, 
        "pk" TEXT, 
        "exampleid" text, 
        "descb" TEXT, 
        "num" INTEGER, 
        "status" INTEGER NOT NULL, 
        "date" TEXT, 
        "urlspl" TEXT, 
        "imgname" TEXT, 
        "urlpdt" TEXT, 
        "createtime" TEXT, 
        "createuser" text, 
        "updatetime" TEXT, 
        "updateuser" text, 
        "pid" INTEGER, 
        "uploadFlag" integer NOT NULL DEFAULT 0, 
        UNIQUE ("sxid" ASC) 
      )`,

			// 声像记录任务表
			`CREATE TABLE "app_media_task" ( 
       "id" INTEGER NOT NULL UNIQUE, 
       "pid" INTEGER NOT NULL, 
       "name" TEXT, 
       "num" INTEGER, 
       "step" INTEGER, 
       "status" INTEGER NOT NULL DEFAULT 1, 
       "createtime" TEXT, 
       "createuser" INTEGER, 
       "updatetime" TEXT, 
       "updateuser" INTEGER DEFAULT 1, 
       PRIMARY KEY("id" AUTOINCREMENT) 
      )`,

			// 操作日志表
			`CREATE TABLE "app_operate_log" ( 
        "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
        "module" text, 
        "title" text, 
        "content" text, 
        "msg" text, 
        "createtime" text, 
        "createuser" text, 
        UNIQUE ("id" ASC) 
      )`,

			// 产品表
			`CREATE TABLE "app_product" ( 
       "pdid" integer NOT NULL UNIQUE, 
       "productno" text, 
       "cardno" text, 
       "model" text, 
       "workorder" text, 
       "invname" text, 
       "graphid" text, 
       "processno" text, 
       "ver" text, 
       "phase" text, 
       "progress" TEXT, 
       "status" text NOT NULL DEFAULT 1, 
       "createtime" text, 
       "createuser" integer NOT NULL DEFAULT 1, 
       "updatetime" text, 
       "updateuser" integer NOT NULL DEFAULT 1, 
       PRIMARY KEY("pdid" AUTOINCREMENT) 
      )`,

			// 产品点位表
			`CREATE TABLE "app_product_points" ( 
       "sxid" INTEGER NOT NULL UNIQUE, 
       "pid" INTEGER NOT NULL, 
       "senum" INTEGER, 
       "part" text, 
       "photoitem" text, 
       "desb" text, 
       "partno" text, 
       "num" INTEGER, 
       "status" INTEGER NOT NULL DEFAULT 1, 
       "urlspl" text, 
       "imgname" text, 
       "urlpdt" text, 
       "createtime" TEXT, 
       "createuser" integer, 
       "updatetime" TEXT, 
       "updateuser" integer, 
       PRIMARY KEY("sxid" AUTOINCREMENT) 
      )`,

			// 产品任务表
			`CREATE TABLE "app_product_task" ( 
       "id" INTEGER NOT NULL UNIQUE, 
       "pid" INTEGER NOT NULL, 
       "name" TEXT, 
       "num" INTEGER, 
       "step" INTEGER, 
       "status" INTEGER NOT NULL DEFAULT 1, 
       "createtime" TEXT, 
       "createuser" INTEGER, 
       "updatetime" TEXT, 
       "updateuser" INTEGER DEFAULT 1, 
       PRIMARY KEY("id" AUTOINCREMENT) 
      )`,

			// 检验信息表
			`CREATE TABLE "app_task_info" ( 
        "pdid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
        "gxpk" text, 
        "pk_serial" text, 
        "cardno" text, 
        "productcode" text, 
        "model" text, 
        "workorder" text, 
        "invname" text, 
        "graphid" text, 
        "processno" text, 
        "gxno" text, 
        "ver" text, 
        "lastupdatetime" text, 
        "updateuser" text, 
        "uploadFlag" integer DEFAULT 0, 
        "uploadtime" text 
      )`,

			// 检验任务关键工序表
			`CREATE TABLE "app_task_keyprocess" ( 
        "sxid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
        "pdid" INTEGER, 
        "pk" text, 
        "testapparatus" text, 
        "tableid" text, 
        "testrequirelower" text, 
        "testrequireupper" text, 
        "parametername" text, 
        "parameterorder" text, 
        "measureunit" text, 
        "parameterinstruction" text, 
        "parameterid" text, 
        "fk_creator" text, 
        "fk_prodcode" text, 
        "prodno" text, 
        "processstep" text, 
        "fk_processtask" text, 
        "measuredvaluemin" text, 
        "measuredvaluemax" text, 
        "result" TEXT, 
        "memo" TEXT, 
        "fks_operator" text, 
        "cs" text, 
        "ts" text, 
        "operator" TEXT 
      )`,

			// 检验任务拍照点
			`CREATE TABLE "app_task_photo" ( 
        "sxid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
        "pdid" INTEGER, 
        "pk" text, 
        "photographpoint" text, 
        "photographdescription" text, 
        "imgname" TEXT, 
        "photourl" text, 
        "exampleurl" text, 
        "photoname" text, 
        "fk_qcRecord" text, 
        "fk_prodcode" text, 
        "prodno" text, 
        "fk_creator" text, 
        "fks_operator" text, 
        "operator" text, 
        "processStep" text, 
        "fk_processTask" text, 
        "cs" text, 
        "ts" text, 
        "uploadFlag" integer NOT NULL DEFAULT 0 
      )`,

			// 检验任务记录
			`CREATE TABLE "app_task_record" ( 
        "sxid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
        "pdid" INTEGER, 
       "pk" text, 
       "fk_invcode" text, 
       "no" text, 
       "invcode" text, 
       "invname" text, 
       "processStep" text, 
       "fk_processTask" text, 
       "checkTarget" text, 
       "checknum" INTEGER, 
       "oknum" INTEGER, 
       "ngnum" INTEGER, 
       "status" text, 
       "result" text, 
        "checkTime" text, 
       "fk_creator" text, 
       "cs" text, 
        "ts" text 
      )`,

			// 检验任务任务记录项
			`CREATE TABLE "app_task_record_item" ( 
        "sxid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
        "psxid" INTEGER, 
        "pk" text, 
        "fk_qcRecord" text, 
        "fk_prodcode" text, 
        "prodno" text, 
        "name" text, 
        "no" text, 
        "nature" text, 
        "unit" text, 
        "maxNum" text, 
        "minNum" text, 
        "status" text, 
        "memo" text, 
        "measuredvalue" text, 
        "measuredvaluemax" TEXT, 
        "result" text, 
        "cs" text, 
        "ts" text, 
        "recorder" TEXT 
      )`

		];

		// 执行所有建表语句
		for (const sql of createTableSQLs) {
			try {
				db.execSQL(sql);
				console.log("Created table with SQL: " + sql.substring(0, 50) + "...");
			} catch (e) {
				console.error("Error creating table: " + e);
			}
		}

		// 插入初始数据
		console.log("Inserting initial data into app_user table");
		const insertDataSQLs = [
			`INSERT INTO "app_user" ("id", "username", "name", "userno", "password", "token", "status", "avatar", "createtime", "createuser", "updatetime", "updateuser", "role") 
       VALUES (1, 'admin', '管理员', 1, 'admin123', 'token1', 1, NULL, NULL, 1, NULL, 1, 'admin');`,

			`INSERT INTO "app_user" ("id", "username", "name", "userno", "password", "token", "status", "avatar", "createtime", "createuser", "updatetime", "updateuser", "role") 
       VALUES (2, 'xuyunliang', '许运良', 2, '123456', 'token1', 1, NULL, NULL, 1, NULL, 1, 'user');`,

			`INSERT INTO "app_user" ("id", "username", "name", "userno", "password", "token", "status", "avatar", "createtime", "createuser", "updatetime", "updateuser", "role")
	    VALUES (3, 'chenpeng', '陈朋', 3, '123456', 'token1', 1, NULL, NULL, 1, NULL, 1, 'user');`,

			`INSERT INTO "app_user" ("id", "username", "name", "userno", "password", "token", "status", "avatar", "createtime", "createuser", "updatetime", "updateuser", "role")
		 VALUES (4, 'lubing', '鲁兵', 4, '123456', 'token1', 1, NULL, NULL, 1, NULL, 1, 'user');`,

			`INSERT INTO "app_user" ("id", "username", "name", "userno", "password", "token", "status", "avatar", "createtime", "createuser", "updatetime", "updateuser", "role")
		  VALUES (5, 'tianzhenyou', '田振友', 5, '123456', 'token1', 1, NULL, NULL, 1, NULL, 1, 'user');` ,

			`INSERT INTO "app_user" ("id", "username", "name", "userno", "password", "token", "status", "avatar", "createtime", "createuser", "updatetime", "updateuser", "role")
		   VALUES (6, 'luyongjun', '陆永俊', 6, '123456', 'token1', 1, NULL, NULL, 1, NULL, 1, 'user');`
		];

		for (const sql of insertDataSQLs) {
			try {
				// 使用INSERT OR IGNORE避免重复插入
				const safeSql = sql.replace("INSERT INTO", "INSERT OR IGNORE INTO");
				db.execSQL(safeSql);
				console.log("Inserted initial data successfully");
			} catch (e) {
				console.error("Error inserting initial data: " + e);
			}
		}
	}

	override onUpgrade(db : SQLiteDatabase, oldVersion : Int, newVersion : Int) : void {
		console.log("Upgrading database from version " + oldVersion + " to " + newVersion);
		// 数据库版本升级逻辑
		if (oldVersion < 2) {
			// 版本2的升级操作
		}
		// 可以添加更多版本升级逻辑
	}

	public executeSql(options : executeSqlOptions) {
		const database : SQLiteDatabase = this.getWritableDatabase();
		const SqlArray = options.sql.split(';');
		let result : executeSqlOptionsResult = {
			data: [] as boolean[],
			errMsg: 'ok'
		}

		try {
			for (let i = 0; i < SqlArray.length; i++) {
				if (SqlArray[i].length > 0) {
					const sql = SqlArray[i].replace(/^\s+/, '');
					try {
						database.execSQL(sql);
						result.data.push(true);
					} catch (e : Error) {
						result.data.push(false);
					}
				}
			}
			options.success?.(result);
		} catch (e) {
			const err = new createSQLiteContextFailImpl(1000002);
			result.errMsg = err.errMsg;
			options.fail?.(result);
		}
		options.complete?.(result);
		return result;
	}

	public selectSql(options : selectSqlOptions) {
		const database : SQLiteDatabase = this.getReadableDatabase();
		const SqlArray = options.sql.split(';');
		let result : selectSqlOptionsResult = {
			data: [] as UTSJSONObject[],
			errMsg: 'ok',
		}

		try {
			for (let i = 0; i < SqlArray.length; i++) {
				if (SqlArray[i].length > 0) {
					const sql = SqlArray[i].replace(/^\s+/, '');
					try {
						const cursor : Cursor = database.rawQuery(sql, null);
						//获取查询结果的字符串并push到result.data中
						if (cursor.moveToFirst()) {
							do {
								const row = cursor.getColumnCount();
								const utsJsonObject = {} as UTSJSONObject;
								for (let j : Int = 0; j < row; j++) {
									//rowArray.push(cursor.getString(j)!=null? cursor.getString(j):'');
									//let obj = {cursor.getColumnName(j):cursor.getString(j)!=null? cursor.getString(j):''}
									//rowArray.push(obj)
									utsJsonObject.set(cursor.getColumnName(j).toString(), cursor.getString(j) != null ? cursor.getString(j) : '')
								}
								result.data.push(utsJsonObject);
							} while (cursor.moveToNext());
						}
						cursor.close();
					} catch (e : Error) {
						console.log(e)
						result.data.push({});
					}
				}
			}
			options.success?.(result);
		} catch (e) {
			const err = new createSQLiteContextFailImpl(1000003);
			result.errMsg = err.errMsg;
			options.fail?.(result);
		}
		options.complete?.(result);
		return result;
	}
	/*
	public close(){
		const database: SQLiteDatabase = this.getReadableDatabase();
		database.close();
	} */

	public transaction(options : transactionOptions) {
		const database : SQLiteDatabase = this.getReadableDatabase();
		const transaction = options.operation;
		let result : transactionResult = {
			errMsg: 'transaction:ok',
		}
		try {
			if (transaction == 'begin') {
				//开启事务
				database.execSQL('BEGIN TRANSACTION');
			} else if (transaction == 'commit') {
				//提交事务
				database.execSQL('COMMIT');
			} else if (transaction == 'rollback') {
				//回滚事务
				database.execSQL('ROLLBACK');
			}
			options.success?.(result);
		} catch (e) {
			let errCode = 1000008;
			if (transaction == 'begin') {
				errCode = 1000004;
			} else if (transaction == 'commit') {
				errCode = 1000005;
			} else if (transaction == 'rollback') {
				errCode = 1000006;
			}
			const err = new createSQLiteContextFailImpl(errCode);
			const errInfo = { errMsg: err.errMsg } as transactionResult;
			options.fail?.(errInfo);
		}
		let ret = {
			errMsg: result.errMsg as string
		} as UTSJSONObject
		options.complete?.(result);
		return ret;
	}
}

export const createSQLiteContext = function (dbName : string) : SQLiteContext {
	// 确保数据库名称包含.db后缀
	const name = dbName.endsWith('.db') ? dbName : dbName + '.db';

	// 注意：根据接口定义，只接受一个参数，默认不强制重新创建表
	return new SQLiteContext(name, false);
}