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 NOT NULL DEFAULT 1,
        avatar TEXT,
        createtime TEXT,
        createuser INTEGER NOT NULL DEFAULT 1,
        updatetime TEXT,
        updateuser INTEGER NOT NULL DEFAULT 1,
        role TEXT,
        UNIQUE (id ASC),
        UNIQUE (username ASC),
        UNIQUE (userno ASC)
      )`
      // 可以添加更多表结构...
    ];
    
    // 执行所有建表语句
    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';
  
  console.log("Initializing SQLiteContext for database: " + name);
  // 注意：根据接口定义，只接受一个参数，默认不强制重新创建表
  return new SQLiteContext(name, false);
}