Discuss - Sqlite3 development optimization

1. Use transactions to process multiple database change statements or loop traversal statements in one transaction.

     SqliteDB::startTransaction();

         do_CRUD;

         MayBe: SqliteDB::RollBack();

     SqliteDB::Commit();

 

2. Use batch to perform operations with set type parameters

   QVariantList    bindValue1;

  QVariantList     bindValue2;

 QString   strSql = " delete from tablename where  field1 = ? and field2 = ?"

  QSqlquery sqlQuery.addbindValue(bindValue1);

  QSqlquery sqlQuery.addbindValue(bindValue2);

     sqlQuery.queryBatch();

      getResult();

 

3. Generally, for the insert operation, you need to return the self increasing sequence number ID of the row after inserting the database, or a custom column. This ID will be used by subsequent processes to mark data lines.

There are two ways to get the insert self growth ID:

    -select last_insert_rowid() as newID from TableName

select max(ID) from table

It can also be processed together with transactions.

Note that:

SQLiteAPI function sqlite3'last'insert'rowid() can get the ROWID of the last record inserted. But sqlite3'last'insert'rowid() is based on the current process. That is to say, sqlite3'last'insert'rowid() gets the ROWID of the last record inserted by the current process. For records that are not inserted by the current process, sqlite3'last'insert'rowi() returns 0. The SQL corresponding to sqlite3'last'insert'rowi() is declared as last'insert'rowid(),

    

Operation example:

bool SampleRecordDAO::updatePrintStatus(const QSet<int> &sampleIdSet, bool isPrint)
{
    int sampleIdSize = sampleIdSet.size();
    if (sampleIdSize <= 0)
    {
        return true;
    }

    QSqlQuery query(SqliteDbHelper::Instance()->getDB());
    query.prepare("UPDATE SampleInfo SET ResultStatePrint = ? where ID = ? ");
    QVariantList stateList,idList;
    for (QSet<int>::const_iterator idItr = sampleIdSet.begin(); idItr != sampleIdSet.end(); ++idItr)
    {
        stateList << (int)isPrint;
        idList << *idItr;
    }
    query.addBindValue(stateList);
    query.addBindValue(idList);
    if (!query.execBatch())
    {
        revDebug << query.lastError().text();
        return false;
    }
    return true;
}

 

bool SampleRecordDAO::deleteSampleInfoAbout4Ids(QList<int> sampleIds)
{
    //1.Haven't Sample Id
    int sampleSize = sampleIds.size();
    if (sampleSize <= 0)
    {
        return true;
    }
    //2.Build Sample Id List
    QVariantList idList;
    for (int sampleIdx = 0; sampleIdx < sampleSize; ++sampleIdx)
    {
        idList << sampleIds.at(sampleIdx);
    }
    //3.Delete DB
    SqliteDbHelper::Instance()->startTransaction();
    QSqlQuery query(SqliteDbHelper::Instance()->getDB());
    do
    {
        query.prepare("DELETE FROM SampleInfo WHERE ID = ? ");
        query.addBindValue(idList);
        if (!query.execBatch())
        {
            break;
        }

        query.prepare("DELETE FROM SampleInfoOrg WHERE ID = ? ");
        query.addBindValue(idList);
        if (!query.execBatch())
        {
            break;
        }

        query.prepare("DELETE FROM MicroscopyInfoTable WHERE ID = ? ");
        query.addBindValue(idList);
        if (!query.execBatch())
        {
            break;
        }

        query.prepare("DELETE FROM SampleInforgraincountTable WHERE ID = ? ");
        query.addBindValue(idList);
        if (!query.execBatch())
        {
            break;
        }
        SqliteDbHelper::Instance()->commit();
        return true;
    }
    while(false);
    revDebug << query.lastError().text();
    SqliteDbHelper::Instance()->rollback();
    return false;
}

Tags: Database SQL

Posted on Sat, 04 Apr 2020 06:19:11 -0700 by pkedpker