Logo Search packages:      
Sourcecode: netams version File versions  Download package

st_sql_mysql.c

/*************************************************************************
***   Authentication, authorization, accounting + firewalling package
***   Copyright 1998-2002 Anton Vinokurov <anton@netams.com>
***   Copyright 2002-2008 NeTAMS Development Team
***   This code is GPL v3
***   For latest version and more info, visit this project web page
***   located at http://www.netams.com
***
*************************************************************************/
/* $Id: st_sql_mysql.c,v 1.143 2009-08-01 09:23:55 anton Exp $ */

#ifdef USE_MYSQL

#include "netams.h"
#include "st_any.h"

#if defined (LINUX) || defined (NETBSD)
#include <mysql.h>
#else
#include <mysql/mysql.h>
#endif

//////////////////////////////////////////////////////////////////////////
void *my_stOpenSql(struct sql_config *cfg,st_conn_type type);
void *my_stCheckSql(void *fd);
unsigned my_stSaveSql(void *fd, char *filename, st_conn_type type);
void my_stCloseSql(void *fd);
u_char  my_stObtainDbData (void *fd, char *query,
      void (*FillData)(void*, void* ,char* (*getRowData)(void*, void* , u_char)));
//////////////////////////////////////////////////////////////////////////
struct sql_data my_sql_data = {
      3306,
      &my_stOpenSql,
      &my_stCheckSql,
      &my_stSaveSql,
      &my_stCloseSql,
      &my_stObtainDbData
};
//////////////////////////////////////////////////////////////////////////
void my_stShowErrors(void *fd, char *type);
//////////////////////////////////////////////////////////////////////////
char* my_getRowData(void *res, void *row, u_char num) {
      return (*(MYSQL_ROW*)row)[num];
}
//////////////////////////////////////////////////////////////////////////
void *my_stOpenSql(struct sql_config *cfg, st_conn_type type){
      MYSQL* fd=NULL;

      MYSQL_RES *res;
      char *my_host, *my_user, *my_pass, *my_dbname, *my_socket;
      u_short my_port;
      char buffer[255]; 

      if(!(fd=mysql_init(fd))) { 
            aLog(D_ERR, "mysql init failed      \n");
            return NULL;
      }

      my_host=cfg->hostname;
      my_user=cfg->username;
      my_pass=cfg->password;
      my_dbname=cfg->dbname;
      my_port=cfg->port;
      my_socket=cfg->socket;

      if (!mysql_real_connect(fd, my_host, my_user, my_pass, my_dbname, my_port, my_socket, CLIENT_LOCAL_FILES)) {
            if (!mysql_real_connect(fd, my_host, my_user, my_pass, NULL, my_port, my_socket, CLIENT_LOCAL_FILES)) {
                  aLog(D_ERR, "mysql connect failed: %s, continuing closed\n", mysql_error(fd));
                  return NULL;
            }

            res=mysql_list_dbs(fd, my_dbname); 

            if (!res) {
                  aLog(D_ERR, "mysql list dbs failed: %s\n", mysql_error(fd)); 
                  mysql_close(fd);
                  return NULL;
            }

            if (!mysql_num_rows(res)) { //hmmm, there are no netams database there... creating it...
                  snprintf(buffer, 254, "CREATE DATABASE %s", my_dbname);
                  if (mysql_query(fd, buffer)) aLog(D_ERR, "mysql create db failed: %s\n", mysql_error(fd));
                  else aLog(D_INFO, "mysql database [%s] created\n", my_dbname);
            }
            mysql_free_result(res);

            if (mysql_select_db(fd, my_dbname)) aLog(D_ERR, "mysql select netams failed: %s\n", mysql_error(fd));
      }
      
      const char *table=st_table_name[type];
      res=mysql_list_tables(fd, table);
      if (!res) {
            aLog(D_ERR, "mysql list tables failed: %s\n", mysql_error(fd));
            mysql_close(fd);
            return NULL;
      }

      if (!mysql_num_rows(res)) {
            const char *query=NULL;
            switch(type) {
                  case ST_CONN_RAW:
                        // there is no raw table ... creating it ...
                        query="CREATE TABLE raw (unit_oid INT UNSIGNED NOT NULL, policy_oid INT UNSIGNED NOT NULL, t_from INT UNSIGNED NOT NULL, t_to INT UNSIGNED NOT NULL, bytes_in BIGINT UNSIGNED, bytes_out BIGINT UNSIGNED, KEY (unit_oid), KEY (policy_oid), KEY(t_from), KEY(t_to))";
                        break;
                  case ST_CONN_SUMMARY:   
                              // there is no summary table ... creating it ...
                        query="CREATE TABLE summary (prefix ENUM('M','W','D','H') NOT NULL, unit_oid INT UNSIGNED NOT NULL, policy_oid INT UNSIGNED NOT NULL, t_from INT UNSIGNED NOT NULL, bytes_in BIGINT UNSIGNED, bytes_out BIGINT UNSIGNED, KEY (unit_oid), KEY (policy_oid), KEY (t_from), PRIMARY KEY(prefix, unit_oid, policy_oid, t_from))";
                        break;
                  case ST_CONN_MONITOR:
                        // there is no monitor table ... creating it ...
                        query="CREATE TABLE monitor (time INT UNSIGNED NOT NULL, flowt INT UNSIGNED, unit_oid INT UNSIGNED, proto TINYINT UNSIGNED, src INT UNSIGNED NOT NULL, srcport SMALLINT UNSIGNED, dst INT UNSIGNED NOT NULL, dstport SMALLINT UNSIGNED, if_in SMALLINT UNSIGNED, if_out SMALLINT UNSIGNED, as_src SMALLINT UNSIGNED, as_dst SMALLINT UNSIGNED, dpkts BIGINT UNSIGNED, len BIGINT UNSIGNED, layer7 VARCHAR(80), KEY(time), KEY(unit_oid))";
                        break;
                  case ST_CONN_LOGIN:
                        // there is no login table ... creating it ...
                        query="CREATE TABLE login (unit_oid INT UNSIGNED PRIMARY KEY NOT NULL, password VARCHAR(32), inact INT UNSIGNED, abs INT UNSIGNED, last_changed INT UNSIGNED, last_opened_time INT UNSIGNED, ip INT UNSIGNED NULL, mac VARCHAR(18), flags INT UNSIGNED NULL)";
                        break;
                  case ST_CONN_QUOTA:
                        // there is no quota table ... creating it ...
                        query="CREATE TABLE quota (unit_oid INT UNSIGNED NOT NULL,policy_oid INT UNSIGNED NOT NULL,syspolicy_oid INT UNSIGNED,soft_treshold INT UNSIGNED,flags INT UNSIGNED,last_blocked_time INT UNSIGNED,notify_soft_oid INT UNSIGNED,notify_hard_oid INT UNSIGNED,notify_return_oid INT UNSIGNED,h_in BIGINT UNSIGNED NULL,h_out BIGINT UNSIGNED NULL,h_sum BIGINT UNSIGNED NULL,d_in BIGINT UNSIGNED NULL,d_out BIGINT UNSIGNED NULL,d_sum BIGINT UNSIGNED NULL,w_in BIGINT UNSIGNED NULL,w_out BIGINT UNSIGNED NULL,w_sum BIGINT UNSIGNED NULL,m_in BIGINT UNSIGNED NULL,m_out BIGINT UNSIGNED NULL,m_sum BIGINT UNSIGNED NULL, block_policy INT UNSIGNED, block_policy_flags INT UNSIGNED, KEY(unit_oid), KEY(policy_oid), PRIMARY KEY(unit_oid, policy_oid))";
                        break;
                  case ST_CONN_EVENTS:
                        // there is no events table ... creating it ...
                        query="CREATE TABLE events (time INT UNSIGNED, type VARCHAR(8), unit_oid INT UNSIGNED, user_oid INT UNSIGNED, account_oid INT UNSIGNED, param VARCHAR(254))";
                        break;
                  case ST_CONN_OIDS:
                        //there is no oids table ... creating it ...
                        query="CREATE TABLE oids (oid INT UNSIGNED PRIMARY KEY NOT NULL, name VARCHAR(32))";
                        break;
                  case ST_CONN_BILLING:
                        //there is no billing table ... creating it ...
                        query="CREATE TABLE billing (oid INT UNSIGNED PRIMARY KEY NOT NULL, name VARCHAR(32), description VARCHAR(254), balance DOUBLE, units TEXT, plan INT(10) UNSIGNED, plan_ch INT UNSIGNED, nextplan INT(10) UNSIGNED, nextplan_ch INT UNSIGNED, blocked INT UNSIGNED,     created INT UNSIGNED,   changed INT UNSIGNED,         last_fee_ch INT UNSIGNED,     email VARCHAR(32), passwd VARCHAR(32), status INT UNSIGNED NULL, credit_limit DOUBLE)";
                        break;
                  case ST_CONN_BDATA:
                        //there is no billing table ... creating it ...
                        query="CREATE TABLE bdata (prefix ENUM('M','W','D','H') NOT NULL, account_oid INT UNSIGNED NOT NULL, subplan_oid INT UNSIGNED NOT NULL, t_from INT UNSIGNED NOT NULL, bytes_in BIGINT NULL, bytes_out BIGINT NULL, pay_in DOUBLE NULL, pay_out DOUBLE NULL, KEY(account_oid), KEY(subplan_oid), KEY(t_from), PRIMARY KEY(prefix, account_oid, subplan_oid, t_from))";
                        break;
                  default:
                        break;
            }
            if (mysql_query(fd, query))
                  aLog(D_ERR, "mysql create '%s' failed: %s\n", table, mysql_error(fd));
            else
                  aLog(D_INFO, "mysql table '%s' created\n", table);
      }
      mysql_free_result(res); 
/*
      if (!cfg->db_path) {
            if (mysql_query(fd, "show variables like 'datadir'"))
                  aLog(D_ERR, "mysql show datadir failed: %s\n", mysql_error(fd));
            else {
                  MYSQL_ROW row;
                  res = mysql_use_result(fd);
                  row = mysql_fetch_row(res);   
                  cfg->db_path = set_string(row[1]);
                  mysql_free_result(res); 
                  aLog(D_INFO, "mysql datadir is at '%s'\n", cfg->db_path);
            }
      }
*/    
      return (void*)fd;
}

//////////////////////////////////////////////////////////////////////////
void *my_stCheckSql(void *fd) {
        //check for reconnect
      if(mysql_ping((MYSQL*)fd)==0)
            return (void*)fd;
      else
            my_stCloseSql(fd);
      
      return NULL;
}
//////////////////////////////////////////////////////////////////////////
void my_stCloseSql(void *fd){
      mysql_close((MYSQL*)fd);
}
//////////////////////////////////////////////////////////////////////////
void my_stShowErrors(void *fd, const char *type) {
      char query[255];
      
      MYSQL *m=(MYSQL*)fd;

      snprintf(query, 254, "SHOW %s", type);
      aDebug(DEBUG_STORAGE, "%s\n", query);

      if (mysql_query(m, query)) { 
            aLog(D_WARN, "mysql show command failed: %s\n", mysql_error(m)); 
            return; 
      }
      
      MYSQL_RES *res;
        MYSQL_ROW row;

      res=mysql_store_result(m);
      if (res) {
            char *Level;
            unsigned Code;
            char *Message;
            
            while((row=mysql_fetch_row(res))){
                  Level=row[0];
                  sscanf(row[1], "%u", &Code);
                  Message=row[2];
                  aLog(D_WARN, "Got the following %s from MySQL: level = %s, code = %u, message = %s.\n", type, Level, Code, Message);
            }
                mysql_free_result(res);
      } else 
            aLog(D_WARN, "MySQL show command problem\n");
}
//////////////////////////////////////////////////////////////////////////
unsigned my_stSaveSql(void *fd, char *filename, st_conn_type type){

      char query[255];
      unsigned result=0;
      MYSQL *m=(MYSQL*)fd;
      
      const char *replace="";
      switch(type) {
            case ST_CONN_SUMMARY:
            case ST_CONN_OIDS:
            case ST_CONN_BILLING:
            case ST_CONN_BDATA:
            case ST_CONN_LOGIN:
            case ST_CONN_QUOTA:
                  replace="REPLACE";
                  break;
            default:
                  break;
      }
      snprintf(query, 254, "LOAD DATA CONCURRENT LOCAL INFILE '%s' %s INTO TABLE %s FIELDS TERMINATED BY ','", filename, replace, st_table_name[type]); 
      if(mysql_query(m, query)) {
             aLog(D_WARN, "SQL Load data: %s\n", mysql_error(m));
             my_stShowErrors(m, "WARNINGS");
             my_stShowErrors(m, "ERRORS");
      } else {
            result=mysql_affected_rows(m);
            aDebug(DEBUG_STORAGE, "SQL Load data: %s\n", mysql_info(m));
      }
      return result;
}
//////////////////////////////////////////////////////////////////////////
u_char my_stObtainDbData(void *fd, char *query, 
      void (*FillData)(void*, void* ,char* (*)(void*, void* , u_char)) ){
      
      MYSQL *m=(MYSQL*)fd;
      
      if (mysql_query(m, query)) {
            aLog(D_ERR, "mysql select failed: %s\n", mysql_error(m));
            return 0;
      }

      MYSQL_RES *res;
      MYSQL_ROW row;

      res=mysql_store_result(m);
      if (res) {
            while((row=mysql_fetch_row(res))){
                  FillData(res, &row, &my_getRowData);
            }
            mysql_free_result(res);
      } else 
            return 0;
      
      return 1;
}
//////////////////////////////////////////////////////////////////////////
#endif

Generated by  Doxygen 1.6.0   Back to index