// 添加表数据
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);
}
