数据库附加原理
可参考菜鸟的教程:https://www.runoob/sqlite/sqlite-attach-database.html
实例功能
软件对某些数据库数据,需要进行自动备份功能,比如数据量达到15000条后,需要拷贝10000条数据到备份数据库后,再删除原数据库中被拷贝的数据。
其中涉及到备份数据库、原数据库两者之间的数据操作,所以需要用到数据库附加功能。
数据库附加实现
如下代码, 'db/Slide.db’为被附加的数据库相对路径,'pSlide’为附加后的数据库名称,可在运行附加sql语句的数据库中访问操作。
queryRun(query,"ATTACH DATABASE 'db/Slide.db' as 'pSlide'");
附加之后还需要进行分离,执行如下代码:
queryRun(query,"DETACH DATABASE 'pSlide'");
后面的使用代码如下:
queryRun(query,QString("create table Slide as select * from pSlide.Slide where SlideState = 2 order by RunTime ASC limit %1").arg(10000));
pSlide.Slide为附加数据库中的表格。
总体代码
void DataThread::taskAutoBackup()
{
QTime time;
time.start();
qDebug()<<Q_FUNC_INFO<<"Start to backup(auto)... , mAutoBackupLimits : "<<mAutoBackupLimits<<" mAutoBackups : "<<mAutoBackupNum;
//创建备份文件夹: ./backups
QDir buckupDir(QApplication::applicationDirPath());
if(!buckupDir.cd("backups"))
{
if(!buckupDir.mkdir("backups"))
{
emit backupResult(false);
return ;
}
buckupDir.cd("backups");
}
qDebug()<<Q_FUNC_INFO<<"Enter the backup dir:"<< buckupDir.absolutePath();
//创建、打开备份数据库(注意初始化配置,如ID自增)
QString timeStr = QDateTime::currentDateTime().toString("yyyyMMdd_hhmmss");
QString dbName = "backup-" + timeStr + ".db"; //数据库名字
QString dbAllName = buckupDir.path() + "/" + dbName; //数据库全名(包含路径)
QSqlDatabase backupDB = QSqlDatabase::addDatabase("QSQLITE");
backupDB.setDatabaseName(dbAllName);
if(!backupDB.open())
{
emit backupResult(false);
return ;
}
qDebug()<<Q_FUNC_INFO<<"Open DB:"<< dbAllName;
//=== 附加数据库
QSqlQuery query(backupDB);
queryRun(query,"ATTACH DATABASE 'db/Slide.db' as 'pSlide'");
queryRun(query,"ATTACH DATABASE 'db/PatientCase.db' as 'pPatientCase'"); //PatientCase.db数据库
queryRun(query,"ATTACH DATABASE 'db/DyeExpRecord.db' as 'pDyeExpRecord'"); //PatientCase.db数据库
if(backupDB.transaction())
{
//复制Slide数据,并从附加数据库中 “条件” 拷贝内容
queryRun(query,QString("create table Slide as select * from pSlide.Slide where SlideState = 2 order by RunTime ASC limit %1").arg(mAutoBackupNum));
//“条件”删除附加数据库中拷贝的数据
queryRun(query,QString("delete from pSlide.Slide "
"where ID in(select Slide.ID from pSlide.Slide where SlideState = 2 order by RunTime ASC limit %1)").arg(mAutoBackupNum));
//复制 PatientCase , 选择方式:病例ID
queryRun(query,QString("CREATE TABLE PatientCase AS SELECT * FROM pPatientCase.PatientCase "
"WHERE CaseID in(select Slide.CaseID from Slide)"));
//复制DyExpRecord 实验信息 , 选择方式:实验编号+玻片ID
queryRun(query,QString("CREATE TABLE DyeExpRecord AS SELECT * FROM pDyeExpRecord.DyeExpRecord "
"WHERE ExpNo in(select Slide.ExpNo from Slide) and SlideID in(select Slide.SlideID from Slide)"));
if(!backupDB.commit())
{
backupDB.rollback();
emit backupResult(false);
}
else
emit backupResult(true);
}
else
emit backupResult(false);
//分离附加数据库
queryRun(query,"DETACH DATABASE 'pSlide'");
queryRun(query,"DETACH DATABASE 'pPatientCase'");
queryRun(query,"DETACH DATABASE 'pDyeExpRecord'");
backupDB.close();
qDebug()<<Q_FUNC_INFO<<"Auto backup end! Usage Time:"<<time.elapsed()/1000.0<<"s"; //计时精度为ms级别
}
更多推荐
sqlite附加数据库操作实例
发布评论