// 添加表数据 static bool tbl_insert(acl::db_handle& db, int n) { acl::query query; query.create_sql("insert into group_tbl(group_name, uvip_tbl," " update_date) values(:group, :test, :date)") .set_format("group", "group:%d", n) .set_parameter("test", "test") .set_date("date", time(NULL), "%Y-%m-%d"); if (db.exec_update(query) == false) return (false); const acl::db_rows* result = db.get_result(); if (result) { const std::vector<acl::db_row*>& rows = result->get_rows(); for (size_t i = 0; i < rows.size(); i++) { const acl::db_row* row = rows[i]; for (size_t j = 0; j < row->length(); j++) printf("%s, ", (*row)[j]); printf("\r\n"); } } db.free_result(); return (true); }
// 删除表数据 static bool tbl_delete(acl::db_handle& db, int n) { const char* sql_fmt = "delete from group_tbl where group_name='中国人-%d'"; acl::string sql; sql.format(sql_fmt, n); if (db.sql_update(sql.c_str()) == false) { printf("delete sql error\r\n"); return (false); } for (size_t i = 0; i < db.length(); i++) { const acl::db_row* row = db[i]; for (size_t j = 0; j < row->length(); j++) printf("%s, ", (*row)[j]); printf("\r\n"); } // 释放查询结果 db.free_result(); return (true); }
// 查询表数据 static int tbl_select(acl::db_handle& db) { const char* sql = "select value, category, type from black_white_list"; if (db.sql_select(sql) == false) { printf("select sql error\r\n"); return (-1); } printf("\r\n---------------------------------------------------\r\n"); const acl::db_rows* result = db.get_result(); if (result) { const std::vector<acl::db_row*>& rows = result->get_rows(); for (size_t i = 0; i < rows.size(); i++) { const acl::db_row* row = rows[i]; for (size_t j = 0; j < row->length(); j++) printf("%s, ", (*row)[j]); printf("\r\n"); } } int ret = (int) db.length(); // 释放查询结果 db.free_result(); return (ret); }
void ping_store::insert_one(acl::db_handle& db, const host_status* status) { acl::string sql; sql.format("insert into host_status_tbl(ip, sent, received, lost," " loss, minimum, maximum, average) values('%s', %d, %d," " %d, %0.2f, %0.2f, %0.2f, %0.2f)", status->get_ip(), status->get_sent(), status->get_received(), status->get_lost(), status->get_loss(), status->get_minimum(), status->get_maximum(), status->get_average()); if (db.sql_update(sql.c_str()) == false) logger_error("sql(%s) error", sql.c_str()); else logger("insert ip %s to host_status_tbl ok", status->get_ip()); const std::vector<PING_PKT*>& pkt_list = status->get_pkt_list(); std::vector<PING_PKT*>::const_iterator cit = pkt_list.begin(); for (; cit != pkt_list.end(); ++cit) { sql.format("insert into ping_status_tbl(ip, rtt, ttl, bytes," "seq, status) values('%s', %0.2f, %d, %d, %d, %d)", status->get_ip(), (*cit)->rtt_, (*cit)->ttl_, (*cit)->bytes_, (*cit)->seq_, (*cit)->status_); if (db.sql_update(sql.c_str()) == false) logger_error("sql(%s) error", sql.c_str()); } logger("ok, insert ip %s, pkt count: %d", status->get_ip(), (int) pkt_list.size()); }
// 查询表数据 static int tbl_select(acl::db_handle& db, int n) { const char* sql_fmt = "select * from group_tbl where" " group_name='中国人-%d' and uvip_tbl='test'"; acl::string sql; sql.format(sql_fmt, n); if (db.sql_select(sql.c_str()) == false) { printf("select sql error\r\n"); return (-1); } // 列出查询结果方法一 const acl::db_rows* result = db.get_result(); if (result) { const std::vector<acl::db_row*>& rows = result->get_rows(); for (size_t i = 0; i < rows.size(); i++) { if (n >= 5) continue; const acl::db_row* row = rows[i]; for (size_t j = 0; j < row->length(); j++) printf("%s, ", (*row)[j]); printf("\r\n"); } } // 列出查询结果方法二 for (size_t i = 0; i < db.length(); i++) { if (n >= 5) continue; const acl::db_row* row = db[i]; // 取出该行记录中某个字段的值 const char* ptr = (*row)["group_name"]; if (ptr == NULL) { printf(("error, no group name\r\n")); continue; } printf("group_name=%s: ", ptr); for (size_t j = 0; j < row->length(); j++) printf("%s, ", (*row)[j]); printf("\r\n"); } int ret = (int) db.length(); // 释放查询结果 db.free_result(); return (ret); }
static bool tbl_create(acl::db_handle& db) { if (db.tbl_exists("group_tbl")) return (true); if (db.sql_update(CREATE_TBL) == false) { printf("sql error\r\n"); return (false); } else { printf("create table ok\r\n"); return (true); } }
void pop3_store::insert_tbl(acl::db_handle& db) { acl::string sql; sql.format("insert into pop3_tbl(user, pop3_ip, nslookup_elapsed, " "connect_elapsed, banner_elapsed, " "auth_elapsed, uidl_elapsed, " "list_elapsed, recv_elapsed, " "quit_elapsed, total_elapsed, " "total_list, total_uidl, total_size, " "recved_count, recved_size, recved_speed)" "values('%s', '%s', %0.2f, %0.2f, %0.2f, %0.2f, " "%0.2f, %0.2f, %0.2f, %0.2f, %0.2f, %d, %d, %d, " "%d, %d, %d)", user_, pop3_ip_, meter_->pop3_nslookup_elapsed, meter_->pop3_connect_elapsed, meter_->pop3_banner_elapsed, meter_->pop3_auth_elapsed, meter_->pop3_uidl_elapsed, meter_->pop3_list_elapsed, meter_->pop3_recv_elapsed, meter_->pop3_quit_elapsed, meter_->pop3_total_elapsed, (int) meter_->total_list, (int) meter_->total_uidl, (int) meter_->total_size, (int) meter_->recved_count, (int) meter_->recved_size, (int) meter_->recved_speed); if (db.sql_update(sql.c_str()) == false) logger_error("sql(%s) error", sql.c_str()); else logger("insert into pop3_tbl OK!"); }
bool pop3_store::create_tbl(acl::db_handle& db) { if (db.tbl_exists("pop3_tbl")) { logger("pop3_tbl table exist"); return (true); } else if (db.sql_update(CREATE_TBL) == false) { logger_error("sql(%s) error", CREATE_TBL); return (false); } else { logger("create table pop3_tbl ok"); return (true); } }
bool ping_store::create_tbl(acl::db_handle& db) { if (db.tbl_exists("host_status_tbl")) logger("host_status_tbl exist"); else if (db.sql_update(CREATE_HOST_STATUS_TBL) == false) { logger_error("sql(%s) error", CREATE_HOST_STATUS_TBL); return (false); } else logger("create table host_status_tbl ok"); if (db.tbl_exists("ping_status_tbl")) logger("ping_status_tbl exist"); else if (db.sql_update(CREATE_PING_STATUS_TBL) == false) { logger_error("sql(%s) error", CREATE_PING_STATUS_TBL); return (false); } else logger("create table ping_status_tbl ok"); return (true); }
// 删除表数据 static bool tbl_delete(acl::db_handle& db, int n) { acl::query query; query.create_sql("delete from group_tbl where group_name=:group") .set_format("group", "group-%d", n); if (db.exec_update(query) == false) { printf("delete sql error\r\n"); return (false); } for (size_t i = 0; i < db.length(); i++) { const acl::db_row* row = db[i]; for (size_t j = 0; j < row->length(); j++) printf("%s, ", (*row)[j]); printf("\r\n"); } // 释放查询结果 db.free_result(); return (true); }
void dns_store::insert_one(acl::db_handle& db, const domain_info* info) { const std::vector<IP_INFO*>& ip_list = info->get_ip_list(); std::vector<IP_INFO*>::const_iterator cit = ip_list.begin(); acl::string sql; for (; cit != ip_list.end(); ++cit) { sql.format("insert into dns_tbl(domain, ip, ttl, spent)" " values('%s', '%s', '%d', '%.2f')", info->get_domain(), (*cit)->ip, (*cit)->ttl, info->get_spent()); if (db.sql_update(sql.c_str()) == false) logger_error("sql(%s) error", sql.c_str()); else logger("add ok, domain: %s, ip: %s, ttl: %d, spent: %0.2f", info->get_domain(), (*cit)->ip, (*cit)->ttl, info->get_spent()); } }
// 查询表数据 static int tbl_select(acl::db_handle& db, int n) { acl::query query; query.create_sql("select * from group_tbl where group_name=:group" " and uvip_tbl=:test") .set_format("group", "group:%d", n) .set_format("test", "test"); if (db.exec_select(query) == false) { printf("select sql error\r\n"); return (-1); } printf("\r\n---------------------------------------------------\r\n"); // 列出查询结果方法一 const acl::db_rows* result = db.get_result(); if (result) { const std::vector<acl::db_row*>& rows = result->get_rows(); for (size_t i = 0; i < rows.size(); i++) { if (n > 100) continue; const acl::db_row* row = rows[i]; for (size_t j = 0; j < row->length(); j++) printf("%s, ", (*row)[j]); printf("\r\n"); } } // 列出查询结果方法二 for (size_t i = 0; i < db.length(); i++) { if (n > 100) continue; const acl::db_row* row = db[i]; // 取出该行记录中某个字段的值 const char* ptr = (*row)["group_name"]; if (ptr == NULL) { printf("error, no group name\r\n"); continue; } printf("group_name=%s: ", ptr); for (size_t j = 0; j < row->length(); j++) printf("%s, ", (*row)[j]); printf("\r\n"); } // 列出查询结果方法三 const std::vector<acl::db_row*>* rows = db.get_rows(); if (rows) { std::vector<acl::db_row*>::const_iterator cit = rows->begin(); for (; cit != rows->end(); cit++) { if (n > 100) continue; const acl::db_row* row = *cit; for (size_t j = 0; j < row->length(); j++) printf("%s, ", (*row)[j]); printf("\r\n"); } } int ret = (int) db.length(); // 释放查询结果 db.free_result(); return (ret); }