stocksql.c

用GTK+做用户界面 股票每日分时行情用数据库管理——MYSQL 软件应用方式客户端/服务器模式 基于TCP/IP的C/S通信模式 实现STOCK

#include "jszb.h"
#include "gmclient.h"
#include "stocksql.h"

stockSql::stockSql()
{
  mysql_init(&stsql);
  sqlflag = 0;
  getnametblflag=0;
  getklineflag=0;
  shnametblsum=0;
  sznametblsum=0;
  curstocksum=0;
  getfsflag=0;
  curfslinesum=0;
}

stockSql::~stockSql()
{
}

int stockSql::connectStockSql()
{
  if(!mysql_real_connect(&stsql,sqlhost,sqluser,sqlpassword,
      NULL,sqlport,sqlsocket,0)){
      printf("Failed to connect to database: Error: %s\n",
          mysql_error(&stsql));  
      sqlflag = FAIL;
      return FAIL;
  }
  else{
      sqlflag = SUCCESS;
      printf("connect to sql database SUCCESS!\n");
      return SUCCESS;
  }

}

int stockSql::connectStockSql(char *host,char *user,char *pass,char  *database,int port,char *socket,int flag)
{
  if(!mysql_real_connect(&stsql,host,user,pass,
      database,port,socket,flag)){
      printf("Failed to connect to database: Error: %s\n",
          mysql_error(&stsql));  
      sqlflag = FAIL;
      return FAIL;
  }
  else{
      sqlflag = SUCCESS;
      printf("connect to sql database SUCCESS!\n");
      return SUCCESS;
  }

}

void stockSql::closeStockSql()
{
  if(sqlflag)
     mysql_close(&stsql);
}

char *stockSql::stockSqlError()
{
  return mysql_error(&stsql);
}

int stockSql::createStockDatabase()
{
  char querystr[4096];

  if(!sqlflag) return FAIL;
  if(isExistStockDatabase()) return SUCCESS;
  sprintf(querystr,"CREATE DATABASE %s;",STOCKDATABASE);
  if(!mysql_query(&stsql,querystr))
      return SUCCESS;
  else
      return FAIL;
}

int stockSql::dropStockDatabase()
{
  char querystr[4096];

  if(!sqlflag) return FAIL;
 sprintf(querystr,"DROP DATABASE IF EXISTS %s;",STOCKDATABASE);
  if(!mysql_query(&stsql,querystr))
       return SUCCESS;
  else
       return FAIL;
}

int stockSql::isExistStockDatabase()
{
  MYSQL_RES *result;
  int num;

  if(!sqlflag) return FAIL;

  result=mysql_list_dbs(&stsql,STOCKDATABASE);
  num= mysql_num_rows(result);
  mysql_free_result(result);
  if(num){
//     printf("nametbl database exist success!\n");
     return SUCCESS;
  }
  else{
//      printf("Nametbl database not exist!\n");  
      return FAIL;
  }  

}

int stockSql::createNameTblTable(int bz)
{
  char querystr[4096];
  char opt[]="( 
                code VARCHAR(6) NOT NULL,
                name VARCHAR(8) NOT NULL,
                type VARCHAR(1) DEFAULT '0' NOT NULL,
                PRIMARY KEY (code));";
  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKDATABASE)) return FAIL;

  if(isExistNameTblTable(bz)) return SUCCESS;
  if(bz == SHBZ)
    sprintf(querystr,"CREATE TABLE %s%s",SHNAMETBL,opt);
  else
    sprintf(querystr,"CREATE TABLE %s%s",SZNAMETBL,opt);
  if(!mysql_query(&stsql,querystr)){
//    printf("create nametbl %d Table SUCCESS!\n",bz);  
      return SUCCESS;
  }
  else{
//    printf("create nametbl:Error:%s\n",mysql_error(&stsql));  
      return FAIL;
  }
   
}


int stockSql::dropNameTblTable(int bz)
{
  char querystr[4096];
  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKDATABASE)) return FAIL;

  if(bz == SHBZ)
    sprintf(querystr,"DROP TABLE IF EXISTS %s;",SHNAMETBL);
  else
    sprintf(querystr,"DROP TABLE IF EXISTS %s;",SZNAMETBL);
  if(!mysql_query(&stsql,querystr))
        return SUCCESS;
  else
        return FAIL;
  
}


int stockSql::insertNameTblData(int bz,int num,STOCKNAMETBL *data)
{
  char querystr[4096];
  int i;
    
//  if(!sqlflag) return FAIL;
//  if(mysql_select_db(&stsql,STOCKDATABASE)) return FAIL;
  if(!isExistNameTblTable(bz)) return FAIL;
  for(i=1;i<=num;i++){
  if(bz == SHBZ){
    sprintf(querystr,"INSERT INTO %s 
          VALUES('%s','%s','%c');",SHNAMETBL,
          data[i].code,data[i].name,data[i].type);
  }
  else{
    sprintf(querystr,"INSERT INTO %s 
          VALUES('%s','%s','%c');",SZNAMETBL,
          data[i].code,data[i].name,data[i].type);
  }
   mysql_query(&stsql,querystr);
  }
  return SUCCESS;

}

int stockSql::isExistNameTblTable(int bz)
{
  char tmp[256];
  MYSQL_RES *result;
  int num;

  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKDATABASE)) return FAIL;

  if(bz == SHBZ)
    strcpy(tmp,SHNAMETBL);
  else
    strcpy(tmp,SZNAMETBL);
  result=mysql_list_tables(&stsql,tmp);
  num= mysql_num_rows(result);
  mysql_free_result(result);
  if(num){
//     printf("Nametbl table exist success!\n");
     return SUCCESS;
  }
  else{
//      printf("Nametbl table not exist!\n");  
      return FAIL;
  }  

}

int stockSql::selectNameTbl()
{
  if(!sqlflag) return FAIL;
//  printf("sqlflag: %d\n",sqlflag);
  if(mysql_select_db(&stsql,STOCKDATABASE)) return FAIL;
  return SUCCESS;

}

int stockSql::deleteNameTblData(int bz)
{
  char querystr[4096];
  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKDATABASE)) return FAIL;

  if(bz == SHBZ)
    sprintf(querystr,"DELETE FROM %s;",SHNAMETBL);
  else
    sprintf(querystr,"DELETE FROM %s;",SZNAMETBL);
  if(!mysql_query(&stsql,querystr))
        return SUCCESS;
  else
        return FAIL;
  

}

int stockSql::getSqlShNameTbl()
{
  char querystr[4096];
  MYSQL_RES *result;
  MYSQL_ROW row;
  unsigned int i;
  int recsum;

//  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKDATABASE)) return FAIL;
  sprintf(querystr,"SELECT * FROM %s;",SHNAMETBL);
  if(mysql_query(&stsql,querystr)){
    printf("get shnametbl:Error: %s\n",mysql_error(&stsql));  
    return FAIL;
  }
  result=mysql_store_result(&stsql);
  
  if(shtbl) g_free(shtbl);
  recsum = mysql_num_rows(result);
//  printf("recsum :%d\n",recsum);
  shtbl=
   (STOCKNAMETBL *)g_malloc(sizeof(STOCKNAMETBL)*(recsum+10));
  i=1;
  while ((row = mysql_fetch_row(result)))
  {
     strcpy(shtbl[i].code,row[0]);
     strcpy(shtbl[i].name,row[1]);
     shtbl[i].type=row[2][0];
     i++;   
  }
  mysql_free_result(result);
  shnametblsum = recsum;
  shstocksum = recsum;
  return SUCCESS;  
}

int stockSql::getSqlSzNameTbl()
{
  char querystr[4096];
  MYSQL_RES *result;
  MYSQL_ROW row;
  unsigned int i;
  int recsum;

//  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKDATABASE)) return FAIL;
  sprintf(querystr,"SELECT * FROM %s;",SZNAMETBL);
  if(mysql_query(&stsql,querystr)){
     printf("get sznametbl:Error: %s\n",mysql_error(&stsql));  
     return FAIL;
  }
  result=mysql_store_result(&stsql);
  
  if(sztbl) g_free(sztbl);
  recsum = mysql_num_rows(result);
  sztbl=
    (STOCKNAMETBL *)g_malloc(sizeof(STOCKNAMETBL)*(recsum+10));
  i=1;
  while ((row = mysql_fetch_row(result)))
  {
     strcpy(sztbl[i].code,row[0]);
     strcpy(sztbl[i].name,row[1]);
     sztbl[i].type=row[2][0];
     i++;   
  }
  mysql_free_result(result);
  sznametblsum = recsum;
  szstocksum = recsum;
  return SUCCESS;  
}


int stockSql::getSqlNameTbl()
{
  if(getSqlShNameTbl() && getSqlSzNameTbl()){
      getnametblflag = SUCCESS;
      frechtblflag=1; 
      return SUCCESS;  
   }
   else{
      frechtblflag=0; 
      return FAIL;
   }  

}

int stockSql::createDayKlineDatabase()
{
  char querystr[4096];

  if(!sqlflag) return FAIL;
  if(isExistDayKlineDatabase()) return SUCCESS;
  sprintf(querystr,"CREATE DATABASE %s;",DAYKLINEDATABASE);
  if(!mysql_query(&stsql,querystr))
      return SUCCESS;
  else
      return FAIL;
}

int stockSql::dropDayKlineDatabase()
{
  char querystr[4096];

  if(!sqlflag) return FAIL;
  sprintf(querystr,"DROP DATABASE IF EXISTS %s;",DAYKLINEDATABASE);
  if(!mysql_query(&stsql,querystr))
     return SUCCESS;
  else
     return FAIL;
}

int stockSql::isExistDayKlineDatabase()
{
  MYSQL_RES *result;
  int num;

  if(!sqlflag) return FAIL;

  result=mysql_list_dbs(&stsql,DAYKLINEDATABASE);
  num= mysql_num_rows(result);
  mysql_free_result(result);
  if(num){
//     printf("kline database exist success!\n");
     return SUCCESS;
  }
  else{
//      printf("kline database not exist!\n");  
      return FAIL;
  }  

}

int stockSql::createDayKlineTable(char *code)
{
  char querystr[4096];
  char opt[]="( 
                jyrq INT(8) NOT NULL,
                kpj  INT(8) DEFAULT '0' NOT NULL,
                spj  INT(8) DEFAULT '0' NOT NULL,
                zgj  INT(8) DEFAULT '0' NOT NULL,
                zdj  INT(8) DEFAULT '0' NOT NULL,
                cjl  INT(12) DEFAULT '0' NOT NULL,
                cjje INT(12) DEFAULT '0' NOT NULL,
                PRIMARY KEY (jyrq));";
  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,DAYKLINEDATABASE)) return FAIL;

//  if(isExistDayKlineTable(code)) return SUCCESS;
  sprintf(querystr,"CREATE TABLE %s_day%s",code,opt);
  if(!mysql_query(&stsql,querystr)){
//      printf("create Table %s SUCCESS!\n",code);  
        return SUCCESS;
  }
  else{
//      printf("create Table: Error: %s\n",mysql_error(&stsql));  
        return FAIL;
  }
   
}


int stockSql::dropDayKlineTable(char *code)
{
  char querystr[4096];
  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,DAYKLINEDATABASE)) return FAIL;

  sprintf(querystr,"DROP TABLE IF EXISTS %s_day;",code);
  if(!mysql_query(&stsql,querystr))
        return SUCCESS;
  else
        return FAIL;
  
}

int stockSql::isExistDayKlineTable(char *code)
{
  char tmp[256];
  MYSQL_RES *result;
  int num;

  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,DAYKLINEDATABASE)) return FAIL;

  sprintf(tmp,"%s_day",code);
  result=mysql_list_tables(&stsql,tmp);
  num= mysql_num_rows(result);
  mysql_free_result(result);
  if(num){
//     printf("table exist success!\n");
     return SUCCESS;
  }
  else{
//      printf("Table not exist!\n");  
      return FAIL;
  }  

}

int stockSql::selectDayKlineTable(char *code)
{
  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,DAYKLINEDATABASE)) return FAIL;
//  if(!isExistDayKlineTable(code)) return FAIL;
  strcpy(curstockcode,code);
  return SUCCESS;
}

int stockSql::insertDayKlineData(char *code,int num,STOCK *data)
{
  char querystr[4096];
  int i;
    
//  if(!selectDayKlineTable(code))  return FAIL;
  if(!isExistDayKlineTable(code)) return FAIL;
  for(i=1;i<=num;i++){
  sprintf(querystr,"INSERT INTO %s_day 
          VALUES(%d,%d,%d,%d,%d,%ld,%ld);",code,
          data[i].jydate,data[i].kpj,data[i].spj,data[i].zgj,
          data[i].zdj,data[i].cjsl,data[i].cjje);
   mysql_query(&stsql,querystr);
  }
  return SUCCESS;

}

int stockSql::deleteDayKlineData(char *code)
{
  char querystr[4096];
    
//  if(!selectDayKlineTable(code))  return FAIL;
  if(!isExistDayKlineTable(code)) return FAIL;
  sprintf(querystr,"DELETE FROM %s_day;",code);
  if(!mysql_query(&stsql,querystr))
        return SUCCESS;
  else
        return FAIL;
}

int stockSql::deleteDayKlineData(char *code,int date)
{
  char querystr[4096];
    
//  if(!selectDayKlineTable(code))  return FAIL;
 if(!isExistDayKlineTable(code)) return FAIL;
 sprintf(querystr,"DELETE FROM %s_day WHERE jydate=%d;",code,date);
  if(!mysql_query(&stsql,querystr))
        return SUCCESS;
  else
        return FAIL;
}

int stockSql::getSqlDayKline(char *code)
{
  char querystr[4096];
  MYSQL_RES *result;
  MYSQL_ROW row;
//  unsigned int num_fields;
//  unsigned long *lengths;
  unsigned int i;
  int recsum;
    
/*   if(!frechtblflag){
    printf("frech tbl fail.\n");
    return FAIL;
   }

   if(strlen(code) != SHCODELEN && strlen(code) != SZCODELEN)
      return FAIL;
   if(strlen(code) == SHCODELEN){
     if(!codeToName(SHBZ,code)) return FAIL; 
     getSqlGpmxData(SHBZ,code);
   } 
   if(strlen(code) == SZCODELEN){
     if(!codeToName(SZBZ,code)) return FAIL; 
     getSqlGpmxData(SZBZ,code);
   } 
*/

  if(!selectDayKlineTable(code))  return FAIL;
  sprintf(querystr,"SELECT * FROM %s_day;",curstockcode);
  if(mysql_query(&stsql,querystr)){
//    printf("get kline data: Error: %s\n",mysql_error(&stsql));  
      return FAIL;
  }
  result=mysql_store_result(&stsql);
  
   if(frechflag){
       g_free(stock);
   }
  recsum= mysql_num_rows(result);