/* Connects to MySQL server
Returns a connection descriptor */
MYSQL* getConnection()
{
MYSQL *conn;
if ((conn = mysql_init(NULL)) == NULL) {
handle_mysql_error(conn);
}
if (mysql_real_connect(conn, DBHOST, DBUSER, DBPASS, DB, DBPORT, NULL, 0) == NULL) {
handle_mysql_error(conn);
}
return conn;
}
int data_refresh() {
client_t *client;
char sql[1024], resp[1024], tmp[128];
MYSQL_RES *res, *res2;
MYSQL_ROW row, row2;
snprintf(sql, 1020, "SELECT id, user_id, data FROM %s WHERE direction='c' AND status=1", opt.db_task_table); //Some LIMIT required
if (opt.debug_log)
_log("[Notice] data_refresh: sql: %s", sql);
if (mysql_query(&g_dbc[0], sql)) {
return handle_mysql_error();
}
res = mysql_use_result(&g_dbc[0]);
while (res && mysql_num_fields(res) == 3 && (row = mysql_fetch_row(res)) != NULL) {
//sj = sj_add(atol(row[0]));
client = client_get_by_id(row[1], g_clients);
do {
if (client && *client->sbuf == NULL) {
strncpy(client->sbuf, row[2], sizeof (client->sbuf) - 1);
snprintf(sql, 1020, "UPDATE %s SET status=5 WHERE id='%s'", opt.db_task_table, row[0]);
if (opt.debug_log)
_log("[Notice] data_refresh: sql: %s", sql);
if (mysql_query(&g_dbc[1], sql)) {
handle_mysql_error();
goto end;
}
}
} while (client && (client = client_get_by_id(row[1], client->next)));
}
end:
if (res) {
mysql_free_result(res);
res = NULL;
}
return 1;
}
int date_insert(const char *buf, MYSQL *db_insert)
{
printf("data ready\n");
char mysql_buf[BUFLEN];
bzero(mysql_buf, BUFLEN);
sprintf(mysql_buf, "INSERT INTO `loginfo` values(NULL, '%s');", buf);
if (0 != mysql_real_query(db_insert, mysql_buf, strlen(mysql_buf)))
handle_mysql_error("query for adverinfo error: %s\n", db_insert);
write(STDOUT_FILENO, mysql_buf, sizeof(mysql_buf));
return 0;
}
void*
sqlExcute(void *paramsv)
{
MYSQL_RES *res;
MYSQL_ROW row;
sqlThreadParams *params = (sqlThreadParams *)paramsv;
int *retval;
retval = (int *)malloc(sizeof(int));
// Making the query, getting the retval
*retval = mysql_query(params->conn, params->query);
if(!(*retval)){
res = mysql_store_result(params->conn);
vPrintResult(&res, &row);
}else{
handle_mysql_error(params->conn);
}
return (void *)retval;
}
int client_initialize(client_t * client) {
char str_ip[16] = {0}, sql[1024], resp[1024], tmp[128], safe_str[4096];
//dynamically allocated variables
char *user_id, *session_hash, *version;
char *s, *dat[10] = {0}, ch;
short n = 0;
MYSQL_RES *res;
MYSQL_ROW row;
user_id = NULL;
session_hash = NULL;
version = NULL;
s = client->rbuf;
while (n < 6) {
dat[n++] = s;
if (!(s = strstr(s, ":")))
break;
*s = NULL;
s++;
}
if (n < 2)
goto end;
user_id = get_safe_string(dat[0], g_dbc[0]);
session_hash = get_safe_string(dat[1], g_dbc[0]);
//version = get_safe_string(dat[2], g_dbc[0]);
sprintf(str_ip, "%d.%d.%d.%d", (client->ip) & 0x000000ff,
(client->ip >> 8) & 0x000000ff,
(client->ip >> 16) & 0x000000ff,
(client->ip >> 24) & 0x000000ff);
if (opt.debug_log)
_log("[Notice] client_initialize: Client authentication details have just been got ( ip: %s, user_id: %s, session_hash: %s )",
str_ip, user_id, session_hash);
//////////////////////////////////////////////////////////////////////////////
strncpy(client->id, user_id, sizeof (client->id) - 1);
snprintf(sql, 1020, "SELECT id FROM %s WHERE %s = '%s' AND %s='%s' LIMIT 1", opt.db_user_table, opt.db_userid_field, user_id, opt.db_session_field, session_hash);
if (opt.debug_log)
_log("[Notice] client_initialize: sql: %s", sql);
if (mysql_query(&g_dbc[0], sql)) {
handle_mysql_error();
goto end;
}
res = mysql_store_result(&g_dbc[0]);
if (res && mysql_num_fields(res) == 1 && (row = mysql_fetch_row(res)) != NULL) {
if (res) {
mysql_free_result(res);
res = NULL;
}
} else {
_log("[Error] client_initialize: Authentication failed!");
goto end;
}
if (*opt.db_connected_field != 0) {
snprintf(sql, 1020, "UPDATE %s SET %s=1 WHERE %s = '%s'", opt.db_user_table, opt.db_connected_field, opt.db_userid_field, user_id);
if (opt.debug_log)
_log("[Notice] client_initialize: sql: %s", sql);
if (mysql_query(&g_dbc[0], sql)) {
handle_mysql_error();
goto end;
}
}
client->initialized = 1;
end:
if (user_id)
free(user_id);
if (session_hash)
free(session_hash);
if (version)
free(version);
return 1;
}
int client_operation(client_t *client, char operation) {
MYSQL_RES *res;
MYSQL_ROW row;
char sql[4096];
unsigned long time_current;
//
char *rbuf;
rbuf = NULL;
time_current = time(NULL);
if (!client->initialized)
return 0;
if (operation == 1) {
if (*opt.db_connected_field != 0) {
snprintf(sql, sizeof (sql) - 1, "UPDATE %s SET %s=0 WHERE $s = '%s'", opt.db_user_table, opt.db_connected_field, opt.db_userid_field, client->id);
if (mysql_query(&g_dbc[0], sql)) {
return handle_mysql_error();
}
}
} else if (operation == 2) {
rbuf = get_safe_string(client->rbuf, g_dbc[0]);
snprintf(sql, sizeof (sql) - 1, "SELECT id FROM %s WHERE direction='s' AND status=5 LIMIT 1", opt.db_task_table);
if (opt.debug_log)
_log("[Notice] client_operation: sql: %s", sql);
if (mysql_query(&g_dbc[0], sql)) {
return handle_mysql_error();
}
res = mysql_use_result(&g_dbc[0]);
if (res && mysql_num_fields(res) == 1 && (row = mysql_fetch_row(res)) != NULL) {
snprintf(
sql, sizeof (sql) - 1,
"UPDATE %s SET status=1, user_id='%s', direction='s', create_t=%u, data='%s' WHERE id='%s'",
opt.db_task_table, client->id, time(NULL), rbuf, row[0]);
if (opt.debug_log)
_log("[Notice] client_operation: sql: %s", sql);
if (mysql_query(&g_dbc[1], sql)) {
return handle_mysql_error();
}
mysql_free_result(res);
res = NULL;
} else {
snprintf(sql, sizeof (sql) - 1,
"INSERT INTO %s (id, status, user_id, direction, create_t, data) VALUES ('',1,'%s','s',%u,'%s')",
opt.db_task_table, client->id, time(NULL), rbuf);
if (opt.debug_log)
_log("[Notice] client_operation: sql: %s", sql);
if (mysql_query(&g_dbc[0], sql)) {
return handle_mysql_error();
}
}
}
if (rbuf)
free(rbuf);
return 1;
}
static void do_oper(User * u)
{
MYSQL_ROW row;
unsigned int nick_id, fields, rows, err;
MYSQL_RES *result;
char *cmd, *nick;
cmd = strtok(NULL, " ");
if (!cmd)
cmd = "";
if (stricmp(cmd, "ADD") == 0) {
if (!is_services_admin(u)) {
notice_lang(s_OperServ, u, PERMISSION_DENIED);
return;
}
nick = strtok(NULL, " ");
if (nick) {
if (!(nick_id = mysql_findnick(nick))) {
notice_lang(s_OperServ, u,
NICK_X_NOT_REGISTERED, nick);
return;
} else if (num_serv_opers() >= MAX_SERVOPERS) {
notice_lang(s_OperServ, u,
OPER_OPER_TOO_MANY, MAX_SERVOPERS);
return;
}
nick_id = mysql_getlink(nick_id);
result = mysql_simple_query(mysqlconn, &err,
"INSERT INTO oper (nick_id) VALUES (%u)",
nick_id);
switch (err) {
case 0:
mysql_free_result(result);
notice_lang(s_OperServ, u, OPER_OPER_ADDED,
nick);
break;
case ER_DUP_ENTRY:
notice_lang(s_OperServ, u,
OPER_OPER_EXISTS, nick);
break;
default:
handle_mysql_error(mysqlconn,
"insert_oper");
break; /* never reached */
}
} else {
syntax_error(s_OperServ, u, "OPER",
OPER_OPER_ADD_SYNTAX);
}
} else if (stricmp(cmd, "DEL") == 0) {
if (!is_services_admin(u)) {
notice_lang(s_OperServ, u, PERMISSION_DENIED);
return;
}
nick = strtok(NULL, " ");
if (nick) {
if (!(nick_id = mysql_findnick(nick))) {
notice_lang(s_OperServ, u,
NICK_X_NOT_REGISTERED, nick);
return;
}
nick_id = mysql_getlink(nick_id);
result = smysql_bulk_query(mysqlconn, &fields,
&rows, "DELETE FROM oper WHERE nick_id=%u",
nick_id);
if (rows) {
notice_lang(s_OperServ, u,
OPER_OPER_REMOVED, nick);
} else {
notice_lang(s_OperServ, u,
OPER_OPER_NOT_FOUND, nick);
}
mysql_free_result(result);
} else {
syntax_error(s_OperServ, u, "OPER",
OPER_OPER_DEL_SYNTAX);
}
} else if (stricmp(cmd, "LIST") == 0) {
notice_lang(s_OperServ, u, OPER_OPER_LIST_HEADER);
result = smysql_bulk_query(mysqlconn, &fields, &rows,
"SELECT nick.nick FROM nick, oper "
"WHERE nick.nick_id=oper.nick_id ORDER BY nick.nick");
while ((row = smysql_fetch_row(mysqlconn, result)))
notice(s_OperServ, u->nick, "%s", row[0]);
} else {
syntax_error(s_OperServ, u, "OPER", OPER_OPER_SYNTAX);
}
}
/*
* 子线程：用于定时给所有已连接设备发送广告信息
* 仅在第一个连接建立时启动，
* TODO: 包裹数据库函数，优化查询循环
*/
static void *pthr_adver_boardcast(void *arg)
{
pthread_detach(pthread_self());
int connfd;
char sendbuf[BUFFSIZE];
MYSQL *db_mysql;
MYSQL_RES *db_result;
MYSQL_ROW db_row;
MYSQL_FIELD *db_field;
unsigned int timeout = 3000; /* mysqlopt timeout val */
unsigned int num_fields; /* fields numbers of query result */
unsigned int i; /* int for loop */
const char *pQuery_init = "SET NAMES UTF8"; /* set mysql for utf8 */
const char *pQuery = "SELECT * FROM `adverinfo`"; /* select table of advertise info */
/* database init */
mysql_thread_init();
if ( NULL == (db_mysql = mysql_init(NULL))) {
printf("mysql init error\n");
return(NULL);
}
// mysql_options(db_mysql, MYSQL_OPT_CONNECT_TIMEOUT, &timeout);
if (!mysql_real_connect(db_mysql, DBHOST, DBUSER, DBPWD,
DBNAME, 0, NULL, 0))
handle_mysql_error("connect failed: %s\n", db_mysql);
/* set mysql names for UTF8 */
if (0 != mysql_real_query(db_mysql, pQuery_init, strlen(pQuery_init)))
handle_mysql_error("utf8 init failded: %s\n", db_mysql);
while(1) { /* get adverinfo for loop */
if (0 != mysql_real_query(db_mysql, pQuery, strlen(pQuery)))
handle_mysql_error("query for adverinfo error: %s\n", db_mysql);
if ( NULL == (db_result = mysql_store_result(db_mysql)))
handle_mysql_error("fetch result failed: %s\n", db_mysql);
while (NULL != (db_row = mysql_fetch_row(db_result)))
{
memset(sendbuf, 0, sizeof(sendbuf));
sprintf(sendbuf, "Adver: %s\n", db_row[1]);
for (i = 0; i <= maxi; i++)
{
connfd = client[i];
send(connfd, sendbuf, sizeof(sendbuf), 0);
}
sleep(2);
}
free(db_row);
mysql_free_result(db_result);
}
mysql_close(db_mysql);
mysql_thread_end();
mysql_library_end();
return(NULL);
}
void serve(int listenfd)
{
int i, connfd, sockfd;
int maxfd, nready;
ssize_t n;
pthread_t tid;
socklen_t alen, blen;
struct addrinfo cliaddr;
fd_set rset, allset;
char buf[MAXLINE];
char clihost[BUFLEN];
char cliserv[BUFLEN];
const char *query_init = "SET NAMES UTF8";
MYSQL *db_insert;
maxfd = listenfd;
mysql_library_init(0, NULL, NULL);
if ( NULL == (db_insert = mysql_init(NULL))) {
printf("mysql init error\n");
exit(1);
}
if (!mysql_real_connect(db_insert, DBHOST, DBUSER, DBPWD,
DBNAME, 0, NULL, 0))
handle_mysql_error("connect failed: %s\n", db_insert);
/* set mysql names for UTF8 */
if (0 != mysql_real_query(db_insert, query_init, strlen(query_init)))
handle_mysql_error("utf8 init failded: %s\n", db_insert);
for (i = 0; i < FD_SETSIZE; i++)
client[i] = -1;
FD_ZERO(&allset);
FD_SET(listenfd, &allset);
for ( ; ; )
{
rset = allset;
nready = select(maxfd+1, &rset, NULL, NULL, NULL);
if (FD_ISSET(listenfd, &rset)) { /* new connection */
alen = sizeof(cliaddr);
memset(&cliaddr, 0, alen);
if ((connfd = accept(listenfd, (SA *)&cliaddr, &alen)) < 0) {
syslog(LOG_ERR, "serv: accept error: %s", strerror(errno));
exit(1);
}
for (i = 0; i < FD_SETSIZE; i++)
if (client[i] < 0) {
client[i] = connfd; /* save descriptor */
break;
}
if (i == FD_SETSIZE)
handle_error("too many clients");
FD_SET(connfd, &allset); /* add new fd to set */
if (connfd > maxfd)
maxfd = connfd;
if (i > maxi)
maxi = i; /* max index in client[] */
if (flag_advpthread) {
pthread_create(&tid, NULL, pthr_adver_boardcast, NULL);
flag_advpthread = 0;
}
if (--nready <= 0) /* if there is only one fd return in select */
continue;
}
for (i = 0; i <= maxi; i++) { /* check all clients for data */
if ( (sockfd = client[i]) < 0)
continue;
if (FD_ISSET(sockfd, &rset)) {
if ((n = read(sockfd, buf, MAXLINE)) == 0) {
/* Connection closed by client */
close(sockfd);
FD_CLR(sockfd, &allset);
client[i] = -1;
} else
date_insert(buf, db_insert);
//write(STDOUT_FILENO, buf, n);
if (--nready <= 0)
break;
}
}
}
mysql_close(db_insert);
mysql_library_end();
}
