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');`
    ];
    
    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);
}