/* * @Author: qianziyang qianzy@bmser.com * @Date: 2023-12-20 10:07:40 * @LastEditors: qianziyang qianzy@bmser.com * @LastEditTime: 2024-01-25 18:10:37 * @FilePath: /rk3568_sdk_1018/bmser_app/bmser_upgrade/src/tools/db3_common/db3_common.h * @Description: db3处理 */ #ifndef __DTC_DB3_COMMON_H__ #define __DTC_DB3_COMMON_H__ #include #include #include #include #include "bmser_dev.h" #define DTC_RECORD_DB_FILE_PATH "/app/database/bmser_dtc_record.db" #define BACKUP_DB_FILE_PATH "/app/database/bmser_dtc_record_backup.db" #define DTC_RECORD_TABLE_NAME "dtc_record" #define DTC_TABLE_IDXID_NAME "indexID" #define DTC_TABLE_BANKID_NAME "bankID" #define DTC_TABLE_RACKID_NAME "rackID" #define CURRENT_DTC_RECORD_TABLE_COLUMNS 29 // DTC记录表列数 #define DROP_DTC_DTC_RECORD_TABLE_SQL \ "DROP TABLE IF EXISTS dtc_record;" #define CREATE_DTC_RECORD_TABLE_SQL \ "CREATE TABLE IF NOT EXISTS dtc_record (" \ " id INTEGER NOT NULL," \ " bankID INTEGER NOT NULL," \ " rackID INTEGER NOT NULL," \ " recordType INTEGER NOT NULL," \ " DBIndexID INTEGER NOT NULL," \ " dtcIndexID INTEGER," \ " unixTime INTEGER," \ " sysTime DATETIME," \ " softwareVersion VARCHAR (32)," \ " DID INTEGER," \ " dtcName VARCHAR (64)," \ " eventId INTEGER," \ " eventName_zh_CN VARCHAR (64)," \ " eventName_en_US VARCHAR (64)," \ " alert1 INTEGER," \ " alarm1 INTEGER," \ " stop1 INTEGER," \ " alert2 INTEGER," \ " alarm2 INTEGER," \ " stop2 INTEGER," \ " resetCount INTEGER DEFAULT (0),"\ " uptime VARCHAR (64)," \ " extSnapType INTEGER," \ " clusterId INTEGER," \ " extRecordsNum INTEGER," \ " issueDetails_zh_CN VARCHAR," \ " issueDetails_en_US VARCHAR," \ " alarm_level INTEGER," \ " dtcName_en VARCHAR," \ " PRIMARY KEY (id,bankID,rackID,recordType,DBIndexID)" \ ");" #define REPLACE_DTC_RECORD_SQL \ "REPLACE INTO dtc_record \ (id, bankID, rackID, recordType, DBIndexID, dtcIndexID, unixTime, sysTime, softwareVersion, DID, dtcName, \ eventId, eventName_zh_CN, eventName_en_US, alert1, alarm1, stop1, alert2, alarm2, stop2, \ resetCount, uptime, extSnapType, clusterId, extRecordsNum, \ issueDetails_zh_CN, issueDetails_en_US, alarm_level, dtcName_en) \ VALUES ( \ ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, \ ?, ?, ?, ?, ?, ?, ?, ?, ?, \ ?, ?, ?, ?, ?, \ ?, ?, ?, ?);" #define SELECT_DTC_MAX_ID_SQL \ "SELECT MAX(id) FROM dtc_record;" #define SELECT_DTC_INDEX_ID_SQL \ "SELECT DBIndexID FROM dtc_record WHERE id = \ (SELECT MAX(id) FROM dtc_record WHERE bankID = ? AND rackID = ? AND recordType = ?);" #define GET_DTC_SAME_REC_SQL \ "SELECT count(*) FROM dtc_record WHERE bankID = ? AND rackID = ? AND recordType = ? \ AND DBIndexID = ? AND unixTime = ?;" #define GET_DTC_DB_ROW_SQL \ "SELECT count(*) from dtc_record;" #define GET_DTC_DB_ROW_BY_TIME_SQL \ "SELECT count(*) from dtc_record WHERE unixTime >= %ld AND unixTime <= %ld;" #define CREATE_INDEX_IDX_DTC_LEVEL_EVENT \ "CREATE INDEX IF NOT EXISTS idx_dtc_record_ ON dtc_record(bankID, rackID, alarm_level);" #define CREATE_INDEX_IDX_DTC_SETTIME \ "CREATE INDEX IF NOT EXISTS idx_dtc_record_settime ON dtc_record(unixTime);" #define CREATE_IDX_DTC_RECORD_OPTIMIZATION \ "CREATE INDEX IF NOT EXISTS idx_dtc_record_optimization ON dtc_record (bankID,rackID,alarm_level,dtcName,unixTime DESC,DBIndexID DESC);" #define STR_VAL_MAX_LEN 64 /* 非详细信息字符串最大长度 */ #define EXTMSG_MAX_LEN 512 /* 详细定位信息最大长度 */ #define DTC_ONCE_BATCH_MAX_SIZE 1000 /* 单次批量插入数据量最大值 */ #define DTC_TABLE_CLEAN_ALARM_ROWS 18000 /* 告警类DTC清理阈值条数 */ #define DTC_TABLE_CLEAN_EVENT_ROWS 2000 /* 事件类DTC清理阈值条数 */ typedef struct { uint32_t id; /* 记录条目ID, 主键 */ int bankID; /* 堆ID, 主键 */ int rackID; /* 簇ID, 主键 */ int recordType; /* 记录类型, 主键 */ int DBIndexID; /* 对应DB的索引号, 主键 */ uint32_t dtcIndexID; /* 下位机上报的dtc总索引号 */ uint32_t unixTime; /* unix时间戳 */ char sysTime[STR_VAL_MAX_LEN]; /* 系统时间 */ char softwareVersion[STR_VAL_MAX_LEN]; /* 软件版本 */ int DID; /* DID */ char dtcName[STR_VAL_MAX_LEN]; /* DTC名称 */ int eventId; /* 事件ID */ char eventName_zh_CN[STR_VAL_MAX_LEN]; /* 触发事件名称(中文) */ char eventName_en_US[STR_VAL_MAX_LEN]; /* 触发事件名称(英文) */ int alert1; /* 警报1 */ int alarm1; /* 告警1 */ int stop1; /* 停止1 */ int alert2; /* 警报2 */ int alarm2; /* 告警2 */ int stop2; /* 停止2 */ int resetCount; /* 重置次数 */ char uptime[STR_VAL_MAX_LEN]; /* 运行时间 */ int extSnapType; /* 详细定位信息类型 */ int clusterId; /* 触发事件时的簇ID */ int extRecordsNum; /* 详细定位信息数量 */ char issueDetails_zh_CN[EXTMSG_MAX_LEN]; /* 详细定位信息(中文) */ char issueDetails_en_US[EXTMSG_MAX_LEN]; /* 详细定位信息(英文) */ int alarm_level; /* 告警级别(仅evetID为4,5时) */ char dtcName_en[STR_VAL_MAX_LEN]; /* DTC名称(英文) */ } DTC_Export_Table_Stru; /* dtc数据表 */ typedef enum { DTC_DB_INIT_MODE_0 = 0, /* 不删除原有表 */ DTC_DB_INIT_MODE_1, /* 删除原有表, 重新创建 */ } DTC_Export_DB3_Init_Enum; // 记录类型 typedef enum { DTC_RECORD_TYPE_EVENT = 9, /* LINUX按索引查询事件类记录(DTC格式) */ DTC_RECORD_TYPE_ALARM = 11, /* LINUX按索引查询告警类记录(DTC格式) */ DTC_RECORD_TYPE_MAX } DTC_RECORD_TYPE_Enum; typedef struct { int rows; DTC_Export_Table_Stru* events; } dtc_call_back_data; /** * @brief 将值插入指定数据表中对应的列，支持批量插入 * @param[in] DTC_Export_Table_Stru* info 数据表值 * @return 0: 成功; !0: 错误 * @par 修改日志: * Date Author Description \n * 2023-12-27 QZY 创建初始版本 \n * 2024-05-29 QZY 重构, 使用bind方式 \n */ int DTC_Export_DB3_dataInsert(DTC_Export_Table_Stru* info); /** * @brief 获取指定设备+记录类型 条目id最大时的indexID * @param[in] bankid 堆ID * @param[in] rackid 簇ID * @param[in] record_type 记录类型(告警，事件) * @param[out] indexID 最大值 * @par 修改日志: * Date Author Description \n * 2023-12-27 QZY 创建初始版本 \n * 2024-05-29 QZY 使用bind 方式 \n */ int DTC_Export_DB3_getIndexID(uint16_t bankid, uint16_t rackid, int record_type, int *indexID); /** * @brief 获取指定设备+记录类型 最大条目id * @param[out] maxID 条目id最大值 * @par 修改日志: * Date Author Description \n * 2023-12-27 QZY 创建初始版本 \n * 2024-05-29 QZY 使用bind 方式 \n */ int DTC_Export_DB3_getMaxID(int *maxID); /** * @brief 查找设备信息，类型，索引，时间戳都相同的记录条目 * @param[in] bankid 堆ID * @param[in] rackid 簇ID * @param[in] record_type 记录类型(告警，事件) * @param[in] DBIndexID 索引DB * @param[in] unixTime 时间戳 * @param[out] count 条目数量 * @par 修改日志: * Date Author Description \n * 2024-10-12 QZY 创建初始版本 \n */ int DTC_Export_DB3_getSameRecord(uint16_t bankid, uint16_t rackid, int record_type, \ int DBIndexID, uint32_t unixTime, int *count); /** * @brief DTC db3初始化 * @param[in] mode 模式(0:不删除原有表; 1:删除原有表,重新创建) * @return 0: 成功 !0: 错误 * @par 修改日志: * Date Author Description \n * 2024-05-29 QZY 创建初始版本 \n */ int DTC_Export_DB3_init(); /** * @brief DTC db3反初始化 * @return void * @retval none * @par 修改日志: * Date Author Description \n * 2024-05-29 QZY 创建初始版本 \n */ void DTC_Export_DB3_deinit(); /** * @brief DTC db3数据库查询 * @param[in] char *sql 数据库查询语句 * @param[in] uint16_t sql_siz 数据查询语句size * @param[out] dtc_call_back_data *result 查询结果 * @return int32_t * @retval 0:正常 !0:错误 * @par 修改日志: * Date Author Description \n * 2024-07-30 ZHY 创建初始版本 \n */ int32_t DTC_Export_DB3_query(char *sql, uint16_t sql_size, dtc_call_back_data *result); /** * @brief 根据查询条件返回dtc总条数 * @param[out] uint16_t *counts 总条数 * @param[in] char *sql sql语句 * @return int32_t * @retval 0:正常 !0:错误 * @par 修改日志: * Date Author Description \n * 2024-08-07 ZHY 创建初始版本 \n */ int32_t DTC_Export_DB3_get_sql_counts(uint16_t *counts, char *sql); /** * @brief DB3清理 * @param[in] int record_type 记录类型(告警/事件) * @return int32_t * @retval 0:正常 !0:错误 * @par 修改日志: * Date Author Description \n * 2024-10-12 QZY 创建初始版本 \n * 2024-10-29 QZY 事件告警分开处理，保证剩余事件告警记录比例大致为1:9 * 2025-02-07 QZY 入参调整 */ int32_t DTC_Export_DB3_space_cleaning(int record_type); #endif