Qt5.15 SQLite 模仿 Delphi.DataSet封装

DataModule 负责数据库连接  DataSet 负责表操作

DataModuel.h

#pragma once

#include <QObject>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlDriver>
#include <QSqlRecord>
#include <QSqlTableModel>
#include <QSqlError>
#include <QDebug>

class DataModule : public QObject
{
    Q_OBJECT
public:
    static DataModule* getInstance();
    static QString utf8ToQStr(const char8_t* utf8String);

    void connAOIDB(QString dbPath);
    QSqlDatabase m_pdbAOI;
    QSqlDatabase m_pdbSys;
    void onSaveAOI(QString dbFile = "");
    void onSaveSys(QString dbFile = "");

    void AOIExec(QString SQL);
    void SYSExec(QString SQL);

    QVariant GetAOIValueSQL(QString SQL);
    QVariant GetSYSValueSQL(QString SQL);
    bool TableModelRecordCopy(QSqlTableModel& source, QSqlTableModel& target, int sourceRow);
    bool TableModelCopy(QSqlTableModel& source, QSqlTableModel& target);
    void close();
    void closeAOI();
private:
    DataModule();
    void connSysDB();
    void updateSysDBVer();
    void initUserInfo();
    void initActiveInfo();

    //~DataModule() = default;
    void updateAOIDBVer();
    void initNgName();

    QSqlQuery qryAOI;
    QSqlQuery qrySYS;

    QString m_sDBAOIFile;
    QString m_sDBSysFile;
};

DataModule.cpp

#include "DataModule.h"

#include <QtWidgets/QApplication>
#include <QDir>

/*
* 此单元需要第三方加密库支持加密,
* https://github.com/devbean/QtCipherSqlitePlugin/wiki
*加密dll需要放入 程序目录\sqldrivers\  sqlitecipher.dll sqlitecipherd.dll
*sqldrivers    sqlitecipher.dll    sqlitecipherd.dll
*/

#define DBVER 20240907

DataModule* DataModule::getInstance()
{
    static DataModule m_gDM;
    return &m_gDM;
}

void DataModule::onSaveAOI(QString dbFile)
{
    QSqlQuery mmQuery(m_pdbAOI);
    if (dbFile == "")
        dbFile = m_sDBAOIFile;
    mmQuery.exec("ATTACH DATABASE '" + dbFile + "' AS aoi_db");
    mmQuery.exec("SELECT sqlcipher_export('aoi_db')");
    mmQuery.exec("DETACH DATABASE aoi_db");
}

void DataModule::onSaveSys(QString dbFile)
{
    QSqlQuery mmQuery(m_pdbSys);
    if (dbFile == "")
        dbFile = m_sDBSysFile;
    mmQuery.exec("ATTACH DATABASE '" + dbFile + "' AS sys_db");
    mmQuery.exec("SELECT sqlcipher_export('sys_db')");
    mmQuery.exec("DETACH DATABASE sys_db");
}

void DataModule::AOIExec(QString SQL)
{
    if (!qryAOI.exec(SQL))
        qDebug() << QString("exec %1 error! error info:%2").arg(SQL).arg(qryAOI.lastError().text());
}

void DataModule::SYSExec(QString SQL)
{
    if (!qrySYS.exec(SQL))
        qDebug() << QString("exec %1 error! error info:%2").arg(SQL).arg(qryAOI.lastError().text());
}

QVariant DataModule::GetAOIValueSQL(QString SQL)
{
    if (!qryAOI.exec(SQL))
    {
        qDebug() << QString("exec %1 error! error info:%2").arg(SQL).arg(qryAOI.lastError().text());
        return QVariant();
    }
    if (!qryAOI.next())
    {
        qDebug() << QString("get record faild!exec %1 error! error info:%2").arg(SQL).arg(qryAOI.lastError().text());
        return QVariant();
    }
    return qryAOI.value(0);
}

QVariant DataModule::GetSYSValueSQL(QString SQL)
{
    if (!qrySYS.exec(SQL))
    {
        qDebug() << QString("exec %1 error! error info:%2").arg(SQL).arg(qryAOI.lastError().text());
        return QVariant();
    }
    if (!qrySYS.next())
    {
        qDebug() << QString("get record faild!exec %1 error! error info:%2").arg(SQL).arg(qryAOI.lastError().text());
        return QVariant();
    }
    return qrySYS.value(0);
}

bool DataModule::TableModelRecordCopy(QSqlTableModel& source, QSqlTableModel& target, int sourceRow)
{
    if (sourceRow > source.rowCount() - 1)
    {
        qDebug() << QString("TableModelRecordCopy error;error info:source tablemodel record row:%1 not exists").arg(sourceRow);
        return false;
    }
    if (!target.insertRecord(target.rowCount(), source.record(sourceRow)))
    {
        qDebug() << QString("TableModelRecordCopy error;error info:%2").arg(target.lastError().text());
        return false;
    }
    return true;
}

bool DataModule::TableModelCopy(QSqlTableModel& source, QSqlTableModel& target)
{
    if (source.rowCount() == 0)
    {
        qDebug() << QString("TableModelCopy error;error info:source tablemodel is empty");
        return false;
    }
    for (int i = 0; i < source.rowCount(); ++i) {
        if (!TableModelRecordCopy(source, target, i))
        {
            qDebug() << QString("TableModelCopy error;error info:%2").arg(target.lastError().text());
            return false;
        }
    }
    return true;
}

void DataModule::close()
{
    if (m_pdbAOI.isOpen())
        m_pdbAOI.close();
    if (m_pdbSys.isOpen())
        m_pdbSys.close();
}

void DataModule::closeAOI()
{
    if (m_pdbAOI.isOpen())
        m_pdbAOI.close();
}

DataModule::DataModule()
{
    connSysDB();
    // 备份加密的数据库到内存数据库
    //QSqlQuery backupQuery(m_pdbMemory);
    //if (!backupQuery.exec("ATTACH DATABASE '"+ m_sDBFileName +"' AS disk_db")) {
    //    qDebug() << "Error attaching encrypted database:" << backupQuery.lastError().text();
    //}
    // 查询 SQLite 版本
    SYSExec("SELECT sqlite_version();");
    if (qrySYS.next()) {
        qDebug() << "SQLite version:" << qrySYS.value(0).toString();
    }
    else {
        qDebug() << "Failed to get SQLite version!" + qrySYS.lastError().text();
    }
    SYSExec("PRAGMA encoding;");
    while (qrySYS.next()) {
        qDebug() << "SQLite charset:" << qrySYS.value(0).toString();  // 应该输出 "UTF-8"
    }
}
void DataModule::connSysDB()
{
    QString temStr = QCoreApplication::applicationDirPath();
    QString exePath = QDir::toNativeSeparators(temStr);
    m_sDBSysFile = exePath + "\\system.db";
    m_pdbSys = QSqlDatabase::addDatabase("SQLITECIPHER", "sys_db");//QSQLITE
    /*
    QSQLITE_USE_CIPHER的可选值分别为：
        aes128cbc        AES 128 Bit CBC – No HMAC (wxSQLite3)
        aes256cbc        AES 256 Bit CBC – No HMAC (wxSQLite3)
        chacha20        ChaCha20 – Poly1305 – Poly1305 HMAC (sqleet)
        sqlcipher        AES 256 Bit CBC – SHA1 HMAC (SQLCipher)
    */
    m_pdbSys.setConnectOptions("QSQLITE_USE_CIPHER=sqlcipher");
    m_pdbSys.setConnectOptions("QSQLITE_ENABLE_SHARED_CACHE");
    //m_pdbSys.setConnectOptions("PRAGMA encoding = 'UTF-8';");
    m_pdbSys.setDatabaseName(m_sDBSysFile);
    m_pdbSys.setUserName("aoi");
    m_pdbSys.setPassword("12345");
    if (!m_pdbSys.open()) {
        qDebug() << "Failed to open database:system.db! error info:" + m_pdbAOI.lastError().text();
        return;
    }
    qrySYS = QSqlQuery(m_pdbSys);
    //AOIExec("PRAGMA encoding = 'UTF-8';");
    updateSysDBVer();
}

void DataModule::connAOIDB(QString dbPath)
{
    //此方法在新建\打开程序 时调用
    m_sDBAOIFile = dbPath + "\\AOI.db";
    m_pdbAOI = QSqlDatabase::addDatabase("SQLITECIPHER", "aoi_db");//QSQLITE
    m_pdbAOI.setConnectOptions("QSQLITE_USE_CIPHER=sqlcipher");
    m_pdbAOI.setConnectOptions("QSQLITE_ENABLE_SHARED_CACHE");
    m_pdbAOI.setDatabaseName(m_sDBAOIFile);
    m_pdbAOI.setUserName("aoi");
    m_pdbAOI.setPassword("12345");
    if (!m_pdbAOI.open()) {
        qDebug() << "Failed to open database:AOI.db! error info:"+ m_pdbAOI.lastError().text();
        return;
    }
    qryAOI = QSqlQuery(m_pdbAOI);
    updateAOIDBVer();
}

void DataModule::initUserInfo()
{
    //中文必须用QStringLiteral 或者 u8 处理,否则会乱码
    auto insertvalue = [](QSqlQuery qry, QString username, QString password) {
        qry.bindValue(":username", username);
        qry.bindValue(":password", password);
        if (!qry.exec())
            qDebug() << "insert into userinfo error! error info:" + qry.lastError().text();
        };
    qrySYS.prepare("INSERT INTO userinfo(username,password)values(:username,:password)");
    insertvalue(qrySYS, utf8ToQStr(u8"管理员"), "314159");
    insertvalue(qrySYS, utf8ToQStr(u8"编程员"), "314159");
    insertvalue(qrySYS, utf8ToQStr(u8"测试员"), "314159");
}

void DataModule::initActiveInfo()
{
    auto insertvalue = [](QSqlQuery qry, QString name, QString caption, int Programmer, int Operator) {
        qry.bindValue(":name", name);
        qry.bindValue(":caption", caption);
        qry.bindValue(":Programmer", Programmer);
        qry.bindValue(":Operator", Operator);
        if (!qry.exec())
            qDebug() << "insert into actioninfo error! error info:" + qry.lastError().text();
        };
    qrySYS.prepare("INSERT INTO actioninfo(name, caption,program,test)values(:name,:caption,:Programmer,:Operator)");
    insertvalue(qrySYS, "AlFile", utf8ToQStr(u8"文件"), 1, 1);
    insertvalue(qrySYS, "ActFileNew", utf8ToQStr(u8"新建程序"), 1, 0);
    insertvalue(qrySYS, "ActFileSave", utf8ToQStr(u8"保存程序"),1,0);
    insertvalue(qrySYS, "ActFileReload", utf8ToQStr(u8"再次装载"),1,0);
    insertvalue(qrySYS, "ActFileSaveAs", utf8ToQStr(u8"另存为"),1,0);
    insertvalue(qrySYS, "ActFileExport", utf8ToQStr(u8"导出程序"),1,0);
    insertvalue(qrySYS, "ActFileImport", utf8ToQStr(u8"导入程序"),1,0);
}

void DataModule::updateAOIDBVer()
{
    int dbFileVer;
    AOIExec("create table IF NOT EXISTS AOIParams(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT UNIQUE,value TEXT,explan TEXT)");
    AOIExec("select value from AOIParams where name='dbver'");
    if (qryAOI.next()) {
        dbFileVer = qryAOI.value(0).toInt();
    }
    else {
        dbFileVer = DBVER;
        AOIExec(QString("insert into AOIParams (name, value) values ('dbver', '%1')").arg(DBVER));
        AOIExec("create table IF NOT EXISTS MapParam(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,value TEXT,explan varchar)");
        initNgName();
    }
    //此处根据版本号做对应版本数据升级处理
    if (dbFileVer < DBVER)
    {
        QString dbBackupFile = QString("%1_%2").arg(m_sDBAOIFile).arg(dbFileVer);
        onSaveAOI(dbBackupFile);//升级前备份旧版本
        AOIExec(QString("update AOIParams set value='%1' where name='dbver'").arg(DBVER));
        /*升级例子
        if (dbFileVer < 2)
        {
            AOIExec("ALTER TABLE test ADD COLUMN age INTEGER DEFAULT 0");
            AOIExec("update test age=age+1 where age>20");
        }
        */
        onSaveAOI();
    }
}

void DataModule::initNgName()
{
    if (Global::Common::GetAoiModel() == OFFLINE_SOFT_MODEL)
    {
        AOIExec("create table IF NOT EXISTS NgName(id INTEGER PRIMARY KEY AUTOINCREMENT, ngID INT UNIQUE, shortName TEXT, nameEn TEXT, name TEXT UNIQUE, ngColor INT, updateTime TEXT, applyFashion TEXT, speedKey TEXT, isBasic varchar(1), IntA INT, IntB INT, IntC INT, IntD INT, IntE INT, IntF INT, IntG INT, IntH INT, IntI INT, IntJ INT, FloatA REAL, FloatB REAL, StrA TEXT, StrB TEXT, Key1 TEXT, Key2 TEXT, Key3 TEXT, Key4 TEXT, Key5 TEXT, NgWarning INT, NgWarnQty INT, pypassMin REAL, pypassMax REAL, jdpassMin REAL, jdpassMax REAL, warnpassPre INT, tjpassCnt INT, bincode TEXT, bindesc TEXT)");
        qryAOI.prepare("INSERT INTO NgName(ngID, shortname, nameEn, name, ngColor, updateTime, applyFashion, speedKey, isBasic, IntA, IntB, IntC, IntD, IntE, IntF, IntG, IntH, IntI, IntJ, FloatA, FloatB, StrA, StrB, Key1, Key2, Key3, Key4, Key5, NgWarning, NgWarnQty, pypassMin, pypassMax, jdpassMin, jdpassMax, warnpassPre, tjpassCnt, bincode, bindesc) values(:ngID, :shortname, :nameEn, :name, :ngColor, :updateTime, :applyFashion, :speedKey, :isBasic, :IntA, :IntB, :IntC, :IntD, :IntE, :IntF, :IntG, :IntH, :IntI, :IntJ, :FloatA, :FloatB, :StrA, :StrB, :Key1, :Key2, :Key3, :Key4, :Key5, :NgWarning, :NgWarnQty, :pypassMin, :pypassMax, :jdpassMin, :jdpassMax, :warnpassPre, :tjpassCnt, :bincode, :bindesc)");
        auto insertvalue = [](QSqlQuery qry, int ngID, QString shortname, QString nameEn, QString name, int ngColor, QString applyFashion, QString speedKey, QString isBasic, int IntA, int IntB, int IntC, int IntD, int IntE, int IntF, int IntG, int IntH, int IntI, int IntJ, qreal FloatA, qreal FloatB, QString StrA, QString StrB, QString Key1, QString Key2, QString Key3, QString Key4, QString Key5, int NgWarning, int NgWarnQty, qreal pypassMin, qreal pypassMax, qreal jdpassMin, qreal jdpassMax, int warnpassPre, int tjpassCnt,  QString bincode, QString bindesc) {
            qry.bindValue(":ngID", ngID);
            qry.bindValue(":shortname", shortname);
            qry.bindValue(":nameEn", nameEn);
            qry.bindValue(":name", name);
            qry.bindValue(":ngColor", ngColor);
            qry.bindValue(":updateTime", QDateTime::currentDateTime());
            qry.bindValue(":applyFashion", applyFashion);
            qry.bindValue(":speedKey", speedKey);
            qry.bindValue(":isBasic", isBasic);
            qry.bindValue(":IntA", IntA);
            qry.bindValue(":IntB", IntB);
            qry.bindValue(":IntC", IntC);

            qry.bindValue(":IntD", IntD);
            qry.bindValue(":IntE", IntE);
            qry.bindValue(":IntF", IntF);
            qry.bindValue(":IntG", IntG);
            qry.bindValue(":IntH", IntH);
            qry.bindValue(":IntI", IntI);
            qry.bindValue(":IntJ", IntJ);
            qry.bindValue(":FloatA", FloatA);
            qry.bindValue(":FloatB", FloatB);
            qry.bindValue(":StrA", StrA);

            qry.bindValue(":StrB", StrB);
            qry.bindValue(":Key1", Key1);
            qry.bindValue(":Key2", Key2);
            qry.bindValue(":Key3", Key3);
            qry.bindValue(":Key4", Key4);
            qry.bindValue(":Key5", Key5);
            qry.bindValue(":NgWarning", NgWarning);
            qry.bindValue(":NgWarnQty", NgWarnQty);
            qry.bindValue(":pypassMin", pypassMin);
            qry.bindValue(":pypassMax", pypassMax);

            qry.bindValue(":jdpassMin", jdpassMin);
            qry.bindValue(":jdpassMax", jdpassMax);
            qry.bindValue(":warnpassPre", warnpassPre);
            qry.bindValue(":tjpassCnt", tjpassCnt);
            qry.bindValue(":bincode", bincode);
            qry.bindValue(":bindesc", bindesc);
            if (!qry.exec())
                qDebug() << "insert into ngname error! error info:" + qry.lastError().text();
            };

        insertvalue(qryAOI, 0, "OK", "OK", "OK", 32768, "YYYYY", "", "Y", 0, 0, 0    ,0, 0, 0, 0, 0,  0, 0, 0.0000, 0.0000, "", "", "E", "E", "E", "E", "E", 0, 0, 0, 0    ,0, 0, 0, 0, "", "");
        insertvalue(qryAOI, 1, "Short", "Short", utf8ToQStr(u8"短路"), 255, "YYNYY", "3", "Y", 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.0000, 0.0000, "", "", "Num", "Num", "NULL", "Num", "Num", 0, 0, 0, 0, 0, 0, 0, 0, "", "");
    }
}

QString DataModule::utf8ToQStr(const char8_t* utf8String)
{
    return QString::fromUtf8(reinterpret_cast<const char*>(utf8String));
}

void DataModule::updateSysDBVer()
{
    //注意 varchar 不支持中文,要使用 text
    int dbFileVer;
    SYSExec("create table IF NOT EXISTS SysParams(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT UNIQUE,value TEXT,explan TEXT)");
    SYSExec("select value from SysParams where name='dbver'");
    if (qrySYS.next()) {
        dbFileVer = qrySYS.value(0).toInt();
    }
    else{
        dbFileVer = DBVER;
        //DSSystem.h
        SYSExec(QString("insert into SysParams (name, value) values ('dbver','%1')").arg(DBVER));
        SYSExec("create table IF NOT EXISTS SysConfigure(id INTEGER PRIMARY KEY AUTOINCREMENT,ProductLine TEXT,softx0 REAL,softy0 REAL,softbx0 REAL,softby0 REAL, zaxisorgmmposwidth REAL,zaxisorgmmposwidthb REAL, nojtcolor INT, nobzcolor INT, ngcolor INT, okcolor INT, fqcolor INT, crosscolor INT, jtljcolor INT, batchcolor INT)");
        SYSExec("create table IF NOT EXISTS UserInfo(id INTEGER PRIMARY KEY AUTOINCREMENT,username TEXT UNIQUE,password TEXT,explan TEXT)");
        SYSExec("create table IF NOT EXISTS Machine(id INTEGER PRIMARY KEY AUTOINCREMENT,languageid INT,aoimodelid INT,bdtime TEXT, lightsourcetime TEXT,machineid INT,machineno TEXT)");
        SYSExec("create table IF NOT EXISTS CompType(id INTEGER PRIMARY KEY AUTOINCREMENT, typeID INT, name TEXT UNIQUE, parentid INT, prechar TEXT, sortid REAL)");
        SYSExec("create table IF NOT EXISTS ProgType(id INTEGER PRIMARY KEY AUTOINCREMENT, typeID INT, name TEXT UNIQUE, sortid INT, prechar TEXT)");
        SYSExec("create table IF NOT EXISTS ActionInfo(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE,parentid INT,caption TEXT,captionEng TEXT, program INT, test INT)");
        SYSExec("create table IF NOT EXISTS ProgList(id INTEGER PRIMARY KEY AUTOINCREMENT, listID INT, name TEXT UNIQUE,lastdate TEXT, sltimg BLOB, slttbl REAL,filepath TEXT,isbmp INT, barcode TEXT, memo TEXT)");
        initUserInfo();
        initActiveInfo();
    }
    //此处根据版本号做对应版本数据升级处理
    if (dbFileVer < DBVER)
    {
        QString dbBackupFile = QString("%1_%2").arg(m_sDBSysFile).arg(dbFileVer);
        onSaveSys(dbBackupFile);//升级前备份旧版本
        SYSExec(QString("update SysParams set value='%1' where name='dbver'").arg(DBVER));
        /*升级例子
        if (dbFileVer < 2)
        {
            AOIExec("ALTER TABLE test ADD COLUMN age INTEGER DEFAULT 0");
            AOIExec("update test age=age+1 where age>20");
        }
        */
        onSaveSys();
    }
}

DataSet.h

#pragma once
#include <qobject.h>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlDriver>
#include <QSqlRecord>
#include <QSqlTableModel>
#include <QDataWidgetMapper>
#include <QSqlError>
#include <QDebug>

class DataSet :
    public QObject
{
        Q_OBJECT
    public:
        DataSet(QSqlDatabase db);
        virtual ~DataSet();
        void Open();
        void Close();
        //void Save(bool saveAll = true);
        void Cancel(bool cancelAll = true);

        void Next();
        void Prior();
        void Last();
        void First();
        bool CopyRecordFrom(QSqlTableModel& source, int sourceRow);//复制其他数据集的一条记录
        bool CopyRecordTo(QSqlTableModel& target);//将当前数据集当前记录复制到其他数据集
        bool CopyDataSetFrom(QSqlTableModel& source, bool Empty = false);//复制其他数据集当前数据
        bool CopyTableFrom(QSqlTableModel& source, bool Empty = false);//复制其他数据集当前数据 及 保存在表中的数据
        bool CopyTableFrom(const QString tablename, bool Empty = false, const QString& wherestr = "");

        QString getIndex();
        void setIndex(const QString &lFields);
        int RecordCount();
        int RecNO();
        int FieldsCount();
        bool Eof();
        bool Bof();
        int fieldIndex(const QString& sFieldName);
        QVariant FieldByName(const QString& sFieldName);
        void SetFieldValue(const QString& sFieldName, const QVariant& value);
        QVariant GetValueByLocate(const QStringList& fieldNames, const QVariantList& fieldValues, const QString& sReturnField);
        void SetValueByLocate(const QStringList& fieldNames, const QVariantList& fieldValues, const QString& sField, const QVariant& value);
        bool Locate(const QStringList& fieldNames, const QVariantList& fieldValues);
        // 有待完善
        //bool LoadFromStream(QDataStream* Stream);
        //bool SaveToStream(QDataStream* Stream);
        //bool LoadFromQry(const QSqlQuery Qry);
        //bool LoadFromFile(const QString sFileName);
        //bool SaveToFileCds(const QString sFileName, QSqlQuery Qry);
        //bool SaveToFile(const QString sFileName);

        void setFilter(const QString sFilter);
        QString getFilter();
        void setTableName(const QString sTableName);
        QString getTableName();
        QString getOriginTable();
        void setTmpTable(bool bTmpTable = false);

        bool Edit();
        bool Post();
        void Append();
        void Delete(int row = -1);
        void Empty(const bool bSave = true);
        bool isOpen();

        int getMaxID(const QString keyField = "id");
        int getNewID(const QString keyField = "id");
        void resetID(const QString keyField);
        QSqlTableModel* Model();
        QSqlTableModel& getModel();
        QModelIndex fIndex;
        QDataWidgetMapper* Mapper();
        //procedure UpdateField(DataDefs: TClientDataSet);
        virtual bool checkPost();//在提交记录前对一些字段值检查,返回假则不提交
        virtual bool checkDelete();//在删除记录前对一些字段值检查,返回假则不删除
signals:
    void signOnNewRecord();//可绑定信号槽,在新增记录时设置一些字段初始值
    void signBeforePost();//可绑定信号槽,在提交记录前对一些字段值检查或计算或关联更新等
    void signBeforeDelete();//可绑定信号槽,在删除记录前对一些字段值检查或计算或关联更新等
    protected:
        QSqlQuery fDataset;
        QSqlTableModel fModel;
        QDataWidgetMapper fMapper;
        bool EnableGetData();
        bool EnableSetData();
    private:
        bool fOpen;
        QString fIndexFieldNames;
        QString fTableName;
        bool fTmpTable;
};

DataSet.cpp

#include "DataSet.h"
#include <QFile>
#include <QTextStream>
#include <QMessageBox>
#include <QSortFilterProxyModel>
//#include <QRegExp>
//#include <QTableView>
#include <QRegularExpression>
#include <QSqlIndex>
#include <QSqlField>
//#include <QThread>
#include <QUuid>

#include "DataModule.h"

class QSqlField;

DataSet::DataSet(QSqlDatabase db)
	:fDataset(db)
	,fModel(this, db)
{
	//子类中需要确认表名,是否多次实例化 fTableName = usersinfo;  fTmpTalbe = true;
	fOpen = false;//false表未打开
	fTmpTable = false;
	fIndexFieldNames = "";
	fModel.setEditStrategy(QSqlTableModel::OnManualSubmit); //手工提交 removeRow 不会自动提交;需要手工submitAll OnManualSubmit手工提交  OnFieldChange字段修改时提交
	fMapper.setModel(&fModel);
}

DataSet::~DataSet()
{
	fModel.clear();
	fOpen = false;
}

void DataSet::Open()
{
	if (fTableName == "") return;
	if (fModel.tableName() != "")
	{
		fOpen = fModel.select();
		fMapper.setModel(&fModel);
		//if (!fModel.hasIndex(0, 0))//放在Edit中处理更合理;  保证表打开后有一条空记录;判断是否为空,为空则新增一条
		//	Append();
		return;
	}
	QString sTmpTableName = fTableName;
	//是否多次实例化
	if (fTmpTable)
	{
		sTmpTableName = QString("%1_%2").arg(fTableName).arg(QUuid::createUuid().toString(QUuid::Id128));
		fDataset.exec(QString("CREATE TEMPORARY TABLE IF NOT EXISTS %1 LIkE %2;").arg(sTmpTableName).arg(fTableName));
	}
	fModel.setTable(sTmpTableName);
	fOpen = fModel.select();
	fMapper.setModel(&fModel);
	//if (!fModel.hasIndex(0, 0))//放在Edit中处理更合理;  保证表打开后有一条空记录;判断是否为空,为空则新增一条
	//	Append();
	fIndexFieldNames = "";
}

void DataSet::Close()
{
	fModel.clear();
	fOpen = false;
}

//void DataSet::Save(bool saveAll)
//{
//  fModel.database().transaction(); //开始事务操作
//	if (saveAll)
//		fModel.submitAll();
//	else
//		fModel.submit();
//  fModel.database().commit();
//}

void DataSet::Cancel(bool cancelAll)
{
	if (cancelAll)
		fModel.revertAll();
	else
		fModel.revert();
}

void DataSet::Next()
{
	fMapper.toNext();
}

void DataSet::Prior()
{
	fMapper.toPrevious();
}

void DataSet::Last()
{
	fMapper.toLast();
}

void DataSet::First()
{
	fMapper.toFirst();//fRow = 0;	fModel.selectRow(fRow);
}

bool DataSet::CopyRecordFrom(QSqlTableModel& source, int sourceRow)
{
	if (EnableSetData())
	{
		if (sourceRow > source.rowCount() - 1)
		{
			qDebug() << QString("CopyRecordFrom error;error info:source tablemodel record row:%1 not exists").arg(sourceRow);
			return false;
		}
		if (fModel.insertRecord(RecordCount(), source.record(sourceRow)))
			return true;
		else
		{
			qDebug() << QString("%1 CopyRecordFrom error;error info:%2").arg(fModel.tableName()).arg(fModel.lastError().text());
			return false;
		}
	}
	return false;
}

bool DataSet::CopyRecordTo(QSqlTableModel& target)
{
	if (EnableGetData())
	{
		if (target.insertRecord(target.rowCount(), fModel.record(RecNO())))
			return true;
		else
		{
			qDebug() << QString("%1 CopyRecordTo.insertRecord error;error info:%2").arg(fModel.tableName()).arg(target.lastError().text());
			return false;
		}
	}
	return false;
}

bool DataSet::CopyDataSetFrom(QSqlTableModel& source, bool Empty)
{
	if (source.rowCount() == 0)
	{
		qDebug() << QString("CopyDataSetFrom error;error info:source tablemodel is empty");
		return false;
	}
	if (Empty)
		this->Empty(false);
	// 将 source 的数据复制到 fModel 中
	for (int i = 0; i < source.rowCount(); ++i) {
		if (!fModel.insertRecord(RecordCount(), source.record(i)))
		{
			qDebug() << QString("%1 CopyDataSetFrom.insertRecord error;error info:%2").arg(fModel.tableName()).arg(fModel.lastError().text());
			return false;
		}
	}
	return true;
}

bool DataSet::CopyTableFrom(QSqlTableModel& source, bool Empty)
{
	if (Empty)
	{
		this->Empty(true);
	}
	if (source.isDirty())
		source.submitAll();
	return CopyTableFrom(source.tableName(), Empty);
}

bool DataSet::CopyTableFrom(const QString tablename, bool Empty, const QString& wherestr)
{
	if (Empty)
	{
		this->Empty(true);
	}
	QSqlRecord record = fModel.record();
	QString sField;
	for (int i = 0; i < record.count(); ++i) {
		if (!record.field(i).isAutoValue())//排除自增字段
		{
			if (i != 0)
				sField += ",";
			sField += record.value(i).toString();
		}
	}
	QString sInsert = QString("insert into %1 (%2) select (%3) from %4 %5").arg(fModel.tableName()).arg(sField).arg(sField).arg(tablename).arg(wherestr);
	if (fDataset.exec(sInsert))
	{
		fOpen = fModel.select();
		fMapper.setModel(&fModel);
		return fOpen;
	}
	else
	{
		qDebug() << QString("insert into %1 error;error info:%2").arg(fModel.tableName()).arg(fModel.lastError().text());
		return false;
	}
}

QString DataSet::getIndex()
{
	return fIndexFieldNames;
}

void DataSet::setIndex(const QString& lFields)
{
	/*fIndexFieldNames = "name;age" 正序     fIndexFieldNames = "name;age:d"  姓名正序,年龄倒序*/
	if (lFields == fIndexFieldNames) return;
	fModel.setSort(-1, Qt::AscendingOrder); // 清除排序
	fIndexFieldNames = lFields;
	QStringList list = lFields.split(";");
	QString fieldname;
	int iIndex;
	Qt::SortOrder order;
	for (int i = 0; i < list.size(); ++i) {
		fieldname = list.at(i);
		order = Qt::SortOrder::AscendingOrder;
		if (fieldname.indexOf(":d", Qt::CaseInsensitive) != -1)
		{
			fieldname = fieldname.replace(":d", "");
			order = Qt::SortOrder::DescendingOrder;
		}
		iIndex = fModel.fieldIndex(fieldname);
		if (iIndex != -1)
			fModel.setSort(iIndex, order);
	}
	//fModel.sort();
	fOpen = fModel.select();
	fMapper.setModel(&fModel);
}

int DataSet::RecordCount()
{
	return fModel.rowCount();
}

int DataSet::RecNO()
{
	//-1打开后为空; Append() 里 insertRow() 后返回-1(新增状态);  > -1 有记录, 记录下标从0开始
	int row = fMapper.currentIndex();//Append() 里 insertRow() 后返回-1(新增状态)
	if ((row == -1) && fOpen)
		row = fModel.rowCount() - 1;
	return row;
}

int DataSet::FieldsCount()
{
	if (fModel.record().isEmpty()) return 0;
	return fModel.record().count();
}

bool DataSet::Eof()
{
	return fMapper.currentIndex() == RecordCount() - 1;
}

bool DataSet::Bof()
{
	return fMapper.currentIndex() == 0;
}

int DataSet::fieldIndex(const QString& sFieldName)
{
	return fModel.fieldIndex(sFieldName);
}

bool DataSet::Locate(const QStringList& fieldNames, const QVariantList& fieldValues)
{
	/* 调用例子
	QStringList fieldNames = { "name", "age" };
	QVariantList fieldValues = { QStringLiteral("测试员"), 22 };
	if (DataSet.locate(fieldNames, fieldValues)) {// 找到了记录，可以进一步操作
		ui->tableView->setCurrentIndex(dsUser->fIndex);
	}
	*/
	if (fieldNames.size() != fieldValues.size())
	{
		qDebug() << " fieldnames count != fieldvalues count";
		return false;
	}
	/*  第一方案 查询记录返回主键 id 值,再通过id定位fModel记录 	*/
	QString sKeyField = "id";
	if (fModel.primaryKey().count() == 1)
		sKeyField = fModel.primaryKey().fieldName(0);
	int idIndex = fModel.fieldIndex(sKeyField);
	if (idIndex != -1)
	{
		QString queryStr = "SELECT "+ sKeyField +" FROM " + fModel.tableName() + " WHERE ";
		for (int i = 0; i < fieldNames.size(); ++i) {
			if (i == 0)
				queryStr += fieldNames[i] + " = :value" + QString::number(i);
			else
				queryStr += " and " + fieldNames[i] + " = :value" + QString::number(i);
		}
		fDataset.prepare(queryStr);
		for (int i = 0; i < fieldNames.size(); ++i) {
			fDataset.bindValue(":value" + QString::number(i), fieldValues[i]);
		}

		if (!fDataset.exec() || !fDataset.next()) {
			qDebug() << QString("%1 Record not found;error info:%2").arg(fModel.tableName()).arg(fModel.lastError().text());
			return false;
		}
		QString sKeyValue = fDataset.value(0).toString();
		QSortFilterProxyModel* proxyModel = new QSortFilterProxyModel();
		proxyModel->setSourceModel(&fModel);
		proxyModel->setFilterFixedString(sKeyValue);
		proxyModel->setFilterKeyColumn(idIndex); // 设置过滤的列
		proxyModel->invalidate();
		if (proxyModel->rowCount() > 0) {
			fIndex = proxyModel->index(0, idIndex); // 获取第一条记录的索引
			fIndex = proxyModel->mapToSource(fIndex);// 获取原始Model的索引
			fModel.selectRow(fIndex.row());
			fMapper.setCurrentIndex(fIndex.row());
			return true;
		}
		else {
			qDebug() << "No record found with KeyValue:" << sKeyValue;
			//return false;  继续使用 第二方案 使用循环
		}
	}

	/*
	//Qt5.15 QSortFilterProxyModel 定位name = John, age = 22的记录
	// 创建代理模型
	QSortFilterProxyModel* proxyModel = new QSortFilterProxyModel();
	proxyModel->setSourceModel(&fModel);

	// 设置过滤器
	// 假设name在第一列，age在第二列
	QRegExp regex("John"); // 匹配以"John"
    proxyModel->setFilterRegExp(regex);
    proxyModel->setFilterKeyColumn(0); // 设置过滤的列

	regex = QRegExp("22"); // 正则表达式匹配数字22
    proxyModel->setFilterRegExp(regex);
    proxyModel->setFilterKeyColumn(1); // 设置过滤的列

    // 定位记录
    QModelIndexList matches = proxyModel->match(proxyModel->index(0, 0), Qt::DisplayRole, "John", 1);
    if (!matches.isEmpty()) {
		return true;
        QModelIndex index = matches.at(0);
        qDebug() << "Found John at row:" << index.row();
		fModel.selectRow(index.row());
        // 可以进一步操作，例如选中视图中的行
    } else {
        qDebug() << "No records found for John";
		return false;
	}
	*/


	/*  第二方案 使用循环比较耗时	*/
	for (int row = 0; row < fModel.rowCount(); ++row) {
		bool match = true;
		for (int i = 0; i < fieldNames.size(); ++i) {
			int column = fModel.record().indexOf(fieldNames[i]);
			if (column == -1) {
				qWarning() << "Field not found:" << fieldNames[i];
				match = false;
				break;
			}

			QVariant value = fModel.index(row, column).data();
			if (value != fieldValues[i]) {
				match = false;
				break;
			}
		}

		if (match) {
			// 找到匹配的行
			qDebug() << "Found row at:" << row;
			// 可以在这里操作找到的行，比如选中或高亮显示
			fIndex = fModel.index(row, 0);
			fModel.selectRow(row);
			fMapper.setCurrentIndex(row);
			return true;
		}
	}

	// 没有找到匹配的行
	qDebug() << "No matching row found.";
	return false;

}
/*
bool DataSet::LoadFromStream(QDataStream* Stream)
{
	if (!Stream) {
		return false; // 流指针为空，加载失败
	}

	// 读取记录数
	int recordCount = 0;
	*Stream >> recordCount;

	// 确保记录数有效
	if (recordCount < 0) {
		return false;
	}
	// 清空当前数据集（如果需要）
	fDataset.clear();
	QVariant variant;
	*Stream >> variant;
	QVariantList variantList = variant.value<QVariantList>();

	QSqlRecord record;
	for (int i = 0; i < variantList.size(); ++i) {
		QVariant value = variantList.at(i);
		record.setValue(i, value);
	}
	QString sInsert = QString("insert into %1 (").arg(fTableName);
	QString sValues = " values(";
	// 读取数据并恢复到数据集中
	for (int r = 0; r < recordCount; ++r) {
		for (int i = 0; i < record.count(); ++i) {
			QVariant value;
			*Stream >> value;
			if (i != 0)
			{
				sInsert += ",";
				sValues += ",";
			}
			sInsert += record.value(i).toString();
			sValues += value.toString();
		}
		sInsert += ")";
		sValues += ")";
		if (fDataset.exec(sInsert + sValues))
		{ }
		else
		{
			qDebug() << QString("%1 insert error;error info:%2").arg(fModel.tableName()).arg(fModel.lastError().text());
			return false;
		}
	}
	return true;
}

bool DataSet::SaveToStream(QDataStream* Stream)
{
	if (!Stream) {
		return false; // 流指针为空，保存失败
	}
	QSqlRecord record = fDataset.record();
	fDataset.first();
	int recordCount = 0;
	while (fDataset.next()) {
		for (int i = 0; i < record.count(); ++i) {
			*Stream << fDataset.value(i);
		}
		++recordCount;
	}

	QStringList sList;
	for (int i = 0; i < record.count(); ++i) {
		sList.append(record.fieldName(i));
	}

	QVariant variant = QVariant::fromValue(sList);
	*Stream << variant;
	*Stream << recordCount;
	return true;
}

bool DataSet::LoadFromQry(const QSqlQuery Qry)
{
	return false;
}

bool DataSet::LoadFromFile(const QString sFileName)
{
	QFile file(sFileName);
	if (!file.open(QIODevice::ReadOnly))
	{
		qDebug() << "Failed to open file for reading:" << file.errorString();
		return false;
	}
	QDataStream in(&file);
	fDataset.clear();
	QVariant variant;
	in >> variant;
	QVariantList variantList = variant.value<QVariantList>();

	QSqlRecord record;
	for (int i = 0; i < variantList.size(); ++i) {
		QVariant value = variantList.at(i);
		record.setValue(i, value);
	}
	QString sInsert = QString("insert into %1 (").arg(fModel.tableName());
	QString sValues = " values(";
	while (!in.atEnd()) {
		for (int i = 0; i < record.count(); ++i) {
			QVariant value;
			in >> value;
			if (i != 0)
			{
				sInsert += ",";
				sValues += ",";
			}
			sInsert += record.value(i).toString();
			sValues += value.toString();
		}
		sInsert += ")";
		sValues += ")";
		if (fDataset.exec(sInsert + sValues))
		{
		}
		else
		{
			qDebug() << QString("%1 insert error;error info:%2").arg(fModel.tableName()).arg(fModel.lastError().text());
			file.close();
			return false;
		}
	}

	file.close();
	return true;
}

bool DataSet::SaveToFileCds(const QString sFileName, QSqlQuery Qry)
{
	return false;
}

bool DataSet::SaveToFile(const QString sFileName)
{
	QFile file(sFileName);
	if (!file.open(QIODevice::WriteOnly | QIODevice::Text)) {
		//QMessageBox::critical(this, "Error", "Failed to open file: " + file.errorString());
		return false;
	}

	QTextStream out(&file);

	int recordCount = 0;
	QSqlRecord record = fDataset.record();
	fDataset.first();
	do
	{
		for (int i = 0; i < record.count(); ++i) {
			out << fDataset.value(i).toString();
		}
		++recordCount;
	} while (fDataset.next());
	out << recordCount;

	file.close();
	return true;
}
*/
void DataSet::setFilter(const QString sFilter)
{
	/*  调用例子
		setFilter("id>1");
		setFilter("username="+DataModule::utf8ToQStr("'编程员'"));
		setFilter("username like "+QStringLiteral("'编程员%'"));
		setFilter(u8"username='编程员'");
	*/
	if (fModel.filter() != sFilter)
	{
		fModel.setFilter(sFilter);
		fModel.select();
	}
}

QString DataSet::getFilter()
{
	return fModel.filter();
}

void DataSet::setTableName(const QString sTableName)
{
	if (sTableName == "") return;
	if (fTableName != sTableName)
	{
		fTableName = sTableName;
		Open();
	}
}

QString DataSet::getTableName()
{
	return fModel.tableName();//fTableName;
}

QString DataSet::getOriginTable()
{
	return fTableName;
}

void DataSet::setTmpTable(bool bTmpTable)
{
	fTmpTable = bTmpTable;
}

QVariant DataSet::FieldByName(const QString& sFieldName)
{
	int fieldIndex = fModel.fieldIndex(sFieldName);
	int row = RecNO();
	if (fModel.hasIndex(row, fieldIndex))
	{
		QModelIndex index = fModel.index(row, fieldIndex);
		return fModel.data(index);
	}
	return QVariant();
}

void DataSet::SetFieldValue(const QString& sFieldName, const QVariant& value)
{
	int fieldIndex = fModel.fieldIndex(sFieldName);
	int row = RecNO();
	if (fModel.hasIndex(row, fieldIndex))
	{
		QModelIndex index = fModel.index(row, fieldIndex);
		fModel.setData(index, value);
	}
}

QVariant DataSet::GetValueByLocate(const QStringList& fieldNames, const QVariantList& fieldValues, const QString& sReturnField)
{
	if (fieldNames.size() != fieldValues.size())
	{
		qDebug() << " fieldnames count != fieldvalues count";
		return QVariant();
	}
	if (Locate(fieldNames, fieldValues))
	{
		return FieldByName(sReturnField);
	}
	else
	{
		return QVariant();
	}
}

void DataSet::SetValueByLocate(const QStringList& fieldNames, const QVariantList& fieldValues, const QString& sField, const QVariant& value)
{
	if (fieldNames.size() != fieldValues.size())
	{
		qDebug() << " fieldnames count != fieldvalues count";
		return;
	}
	if (!Locate(fieldNames, fieldValues))
	{
		Append();
		for (int i = 0; i < fieldNames.size(); i++)
		{
			SetFieldValue(fieldNames.at(i), fieldValues.at(i));
		}
	}
	SetFieldValue(sField, value);
	Post();
}

bool DataSet::Edit()
{
	if (!fOpen)
	{
		qDebug() << "Error edit record:数据集还未打开";
		return false;
	}

	fModel.setEditStrategy(QSqlTableModel::OnManualSubmit);// OnManualSubmit手工提交  OnFieldChange字段修改时提交

	if (RecordCount() == 0)
		Append();
	return true;
}

bool DataSet::Post()
{
	if (!fModel.isDirty())
	{
		qDebug() << QString("%1 no data modify to post").arg(fModel.tableName());
		return false;
	}
	if (!checkPost()) return false;
	emit signBeforePost();//可绑定信号槽,在提交记录前对一些字段值检查或计算等
	//return fModel.submit();
	if (fModel.submitAll()) {
		qDebug() << QString("%1 record submitAll successfully.").arg(fModel.tableName());
		return true;
	}
	else {
		//if (fModel.lastError().type() == QSqlError::ConstraintError) {
		//	qDebug() << "Unique constraint failed for compName. Value already exists.";  唯一键重复
		qDebug() << QString("%1 post error;error info:%2").arg(fModel.tableName()).arg(fModel.lastError().text());
		return false;
	}
}

void DataSet::Append()
{
	if (!fOpen)
	{
		Open();
	}
	int row = RecordCount();
	if (fModel.insertRow(row)) {
		fMapper.setCurrentIndex(row);
		emit signOnNewRecord();//可绑定信号槽,在新增记录时设置一些字段初始值
	}
	else
	{
		qDebug() << QString("%1 insertRow(%2) error;error info:%3").arg(fModel.tableName()).arg(RecordCount()).arg(fModel.lastError().text());
	}
}

void DataSet::Delete(int row)
{
	if (row == -1)
		row = fMapper.currentIndex();
	if (fModel.hasIndex(row, 0))
	{
		if (!checkDelete()) return;
		emit signBeforeDelete();
		fModel.removeRow(row);
		Post();
	}
}

void DataSet::Empty(const bool bSave)
{
	//fModel.clear();//会清空表结构
	fModel.removeRows(0, RecordCount());
	if (bSave)
	{
		Post();
		fModel.select();
	}
}

bool DataSet::isOpen()
{
	return fOpen;
}

int DataSet::getMaxID(const QString keyField)
{
	QString sKeyField = keyField;
	if ((keyField == "") || (keyField == "id"))
	{
		if (fModel.primaryKey().count() == 1)
			sKeyField = fModel.primaryKey().fieldName(0);
	}
	int idIndex = fModel.fieldIndex(sKeyField);
	if (idIndex != -1)
	{
		QString queryStr = QString("SELECT max(%1) FROM %2").arg(sKeyField).arg(fModel.tableName());
		if (fDataset.exec(queryStr))
		{
			if (fDataset.next()) {
				if (fDataset.value(0).isNull())
					return -1;
				else
					return fDataset.value(0).toInt(); // 获取最大ID
			}
			else {
				qDebug() << QString("%1 getmaxid error;error info:%2").arg(fModel.tableName()).arg(fModel.lastError().text());
				return -1; // 返回-1表示出错
			}
		}
		else
		{
			qDebug() << QString("%1 getmaxid error;error info:%2").arg(queryStr).arg(fModel.lastError().text());
			return -1; // 返回-1表示出错

		}
	}
	return -1;
}

int DataSet::getNewID(const QString keyField)
{
	//默认NewID下标从0开始
	if (keyField != "" && fieldIndex(keyField) == -1)
	{
		qDebug() << QString("table:%1 not exists field:%2").arg(fModel.tableName()).arg(keyField);
		return -1;
	}
	int maxID = getMaxID(keyField);
	//元件画框下标从1开始;这里处理一下 getNewID=getMaxID+1; getNewID即可返回1
	if (maxID == -1 && keyField == "yjID")
		maxID = 0;
	return maxID+1;
}

void DataSet::resetID(const QString keyField)
{
	if (fieldIndex(keyField) == -1)
	{
		qDebug() << QString("table:%1 not exists field:%2").arg(fModel.tableName()).arg(keyField);
		return;
	}
	First();
	for (int i = 0; i < RecordCount(); i++)
	{
		SetFieldValue(keyField, i);
		Next();
	}
}

QSqlTableModel* DataSet::Model()
{
	return &fModel;
}

QSqlTableModel& DataSet::getModel()
{
	return fModel;
}

QDataWidgetMapper* DataSet::Mapper()
{
	return &fMapper;
}

bool DataSet::checkPost()
{
	return true;
}

bool DataSet::checkDelete()
{
	return true;
}

bool DataSet::EnableGetData()
{
	return (RecNO() > -1);
}

bool DataSet::EnableSetData()
{
	return fOpen && (RecNO()>-1);//空数据集赋值前需要 Append(); 增加一条空记录
}