数据库搜索 排序分页

{
    "metaID": "", 
    "searchID": "123484644541", 
    "timeSpanList": 
    {
        "timeSpan":
         {
            "startTime": "2021-04-06", 
            "endTime": "2021-04-13"
        }
    }, 
    "wildcard": "", 
    "searchResultPostion": 40, 
    "operationType": 0, 
    "maxResults": 40
}

由json解析出搜索条件：时间区间、日志类型、搜索位置和最大数量（分页）。

 sprintf(tmpBuf, "WHERE (id >= %llu AND id < %llu) ", (UINT64)startTime*1000, (UINT64)endTime*1000);
strcat(sqlCmd, tmpBuf);//按时间搜索
	
sprintf(tmpBuf, " AND operationtype = %d  ", fileType);
strcat(sqlCmd, tmpBuf);//按日志类型搜索

//排序  ASC | DESC（升序|降序）
> SELECT column-list  FROM table_name  [WHERE condition]  [ORDER BY
> column1, column2, .. columnN] [ASC | DESC];

 sprintf(tmpBuf, "%s", "ORDER BY startTime DESC");//按起始时间倒序搜索
 strcat(sqlCmd, tmpBuf);
 
 snprintf(tmpBuf, sizeof(tmpBuf), " LIMIT %d, %d", startPos, elementNum);
 strcat(sqlCmd, tmpBuf);//搜索条件：起始位置+数量

AND (PoliceNum LIKE '%%%s%%' OR deviceNum LIKE '%%%s%%' OR memo LIKE '%%%s%%')
;//模糊匹配

"ALTER TABLE userlistinfo ADD groupName TEXT DEFAULT '';"
;//表增加元素列  

alter table table_name  rename to new_table_name;
//重命名数据库表  

完整搜索语句：
 SELECT 'sdd',* FROM loginfo WHERE (id >= 1617667200000 AND id < 1618272000000)  
 ORDER BY startTime DESC LIMIT 40, 40

/** 
 *  @brief  搜索交通数据，输出到picElement数组中
 *  @param  diskBitmap[IN]:磁盘的bitmap，为1表示需要搜索对应的磁盘，为0不需要搜索;
            pCondition[IN]:条件子句; byOrder[IN]:按时间排序(1-降序, 2-升序, 0-不排序);
 *          startPos[IN]:符合条件数据的起始位置; picElement[OUT]:指向存放结果数据的数组的指针;
 *          elementNum[IN]:存放结果数据的数组能存放的元素数量
 *  @return -1:失败; 0:成功
 */
int searchLogInfoDataByDesc(UINT32 diskBitmap, /*磁盘的bitmap*/
						const char *pCondition,  /*条件子句*/
						 UINT32 byDataType, 
                        UINT32 startPos,       /*符合条件数据的起始位置*/      0/40/80
                         MatchLogElement_t *picElement,    /*数组  存数据内容*/
                         UINT32 elementNum,   /*数组  元素数量*/      40
                        char responseMsg[], 
                        UINT32 responseMsgLen)
{
    char sqlCmd[CMDBUFSIZE] = {0};
    char attachSqlCmd[32] = {0};
    char **dbResult;
    char *pMsg = NULL;
    char tmpBuf[512] = {0};
    char dbName[16] = {0};
    char utfbuf[256] = {0};
    int nRow, nColumn;//行  列
    bool bFirst;
    int i, result;
    int retval;
    UINT32 redLightTime1, redLightTime2, redLightTime3;
    UINT8 ctrl, drive, part;
    stDBLinkInfo *pLinkInfo;
    sqlite3 *rawDeviceDb = NULL;//打开db的句柄
    struct timeval stTime1, stTime2;
    struct timeval t1, t2, t3, t4,t5,t6;
	struct tm timebuf;
	//数据库加读锁
	retval = getSqliteHandle(0, EM_SQLITE_LOCK_LEVEL_0, EM_SQLITE_LOCK_MODE_READ); 
    memset(sqlCmd, 0, sizeof(sqlCmd));
    bFirst = true;
    gettimeofday(&t1, NULL);
    for(i=0; i<MAX_HARDDISK_NUM; i++)
    {
        pLinkInfo = &gSqliteLinkInfo[i];
        if((pLinkInfo->bAttached == true) && ((diskBitmap&(1<<i)) != 0))
        {       
            //搜索第一个盘
            if(bFirst)
            {
                retval = sqlite3_open(pLinkInfo->dbName, &rawDeviceDb);
                if(retval != 0)
                {                   
                    sqlite3_close(rawDeviceDb);
                    snprintf(responseMsg, responseMsgLen, "Cannot open dbName %s", pLinkInfo->devName);
					freeSqliteHandle(0, EM_SQLITE_LOCK_LEVEL_0, EM_SQLITE_LOCK_MODE_READ);
                    return -1;
                }
                //搜索日志表（加之前时间和日志类型的条件语句）
            	sprintf(sqlCmd, "SELECT '%s',* FROM loginfo %s ", pLinkInfo->attachDbName, \
                     pCondition);
                bFirst = false;
            }
            else//搜索其他盘
            {
                memset(attachSqlCmd, 0, sizeof(attachSqlCmd));
                sprintf(attachSqlCmd, "ATTACH '%s' AS %s", pLinkInfo->dbName, pLinkInfo->	   attachDbName);   
                gettimeofday(&stTime1, NULL);
                retval = sqlite3_exec(rawDeviceDb, attachSqlCmd, 0, 0, NULL);
                gettimeofday(&stTime2, NULL);  
                //合并多个盘 select语句的结果集。
                memset(tmpBuf, 0, sizeof(tmpBuf));
            	snprintf(tmpBuf, sizeof(tmpBuf), "UNION ALL SELECT '%s',* FROM %s.loginfo %s ",
                     pLinkInfo->attachDbName, pLinkInfo->attachDbName, pCondition);
                strcat(sqlCmd, tmpBuf);
            }
			#endif
        }
    }
      
    gettimeofday(&t2, NULL);
    if(rawDeviceDb == NULL)
    {
        snprintf(responseMsg, responseMsgLen, "No database attached!");
		freeSqliteHandle(0, EM_SQLITE_LOCK_LEVEL_0, EM_SQLITE_LOCK_MODE_READ);
        return -1;
    }

	//按起始时间倒序搜索
    memset(tmpBuf, 0, sizeof(tmpBuf));
    sprintf(tmpBuf, "%s", "ORDER BY startTime DESC");
    strcat(sqlCmd, tmpBuf);
    //搜索条件：起始位置+数量
    memset(tmpBuf, 0, sizeof(tmpBuf));
    snprintf(tmpBuf, sizeof(tmpBuf), " LIMIT %d, %d", startPos, elementNum);
    strcat(sqlCmd, tmpBuf);
    TRAFFIC_DB_ERR("\nsearchTrafficData sqlCmd: %s\n", sqlCmd);
	if(strlen(sqlCmd) >= 1024)
	{
		TRAFFIC_DB_ERR("sqlCmd too long, search may return error!\n");
		printf("sqlcmd:%s\n", sqlCmd);
	}
    // dbResult 前面第一行数据是字段名称，从 nColumn 索引开始才是真正的数据.
    // dbResult 的字段值是连续的，从第0索引到第 nColumn - 1索引都是字段名称，从第 nColumn 索引开始，
    // 后面都是字段值，它把一个二维的表（传统的行列表示法）用一个扁平的形式来表示
    result = sqlite3_get_table(rawDeviceDb, sqlCmd, &dbResult, &nRow, &nColumn, &pMsg);
    gettimeofday(&t3, NULL);

    if(result != SQLITE_OK)
    {
        TRAFFIC_DB_ERR("search fail, errMsg: %s\n", pMsg);      
        snprintf(responseMsg, responseMsgLen, "%s", pMsg);
        if(pMsg != NULL)
        {
            sqlite3_free(pMsg);
            pMsg = NULL;
        }
        sqlite3_free_table( dbResult );
        sqlite3_close(rawDeviceDb);
		freeSqliteHandle(0, EM_SQLITE_LOCK_LEVEL_0, EM_SQLITE_LOCK_MODE_READ);
        return -1;
    }
    else
    {
    	//取搜索结果的数据
        for( i = 0; i < nRow && i < elementNum; i++ )
        {
			snprintf(picElement[i].logDescriptor.deviceId,   	sizeof(picElement[i].logDescriptor.deviceId), "%s", dbResult[(i+1)*nColumn + enum_deviceId]);

			picElement[i].logDescriptor.cid = atoi(dbResult[(i+1)*nColumn + enum_cid]);	
        }
    }

    // 释放 char** 查询结果，使用 sqlite 提供的功能来释放
    sqlite3_free_table( dbResult );
    sqlite3_close(rawDeviceDb);
	freeSqliteHandle(0, EM_SQLITE_LOCK_LEVEL_0, EM_SQLITE_LOCK_MODE_READ);

    return nRow;
}