stocksql.c

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

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

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

int stockSql::quickDeleteFsData(char *code)
{
  char querystr[4096];
    
  sprintf(querystr,"DELETE FROM %s_fs;",code);
  mysql_query(&stsql,querystr);
 
  return SUCCESS;
}

int stockSql::deleteFsData(char *code,int time)
{
  char querystr[4096];
    
//  if(!selectDayKlineTable(code))  return FAIL;
 if(!isExistFsTable(code)) return FAIL;
 sprintf(querystr,"DELETE FROM %s_fs WHERE time=%d;",code,time);
  if(!mysql_query(&stsql,querystr))
        return SUCCESS;
  else
        return FAIL;
}

int stockSql::getSqlFsData(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(!selectFsTable(code))  return FAIL;
  sprintf(querystr,"SELECT * FROM %s_fs;",curstockcode);
  if(mysql_query(&stsql,querystr)){
//    printf("get fs data: Error: %s\n",mysql_error(&stsql));  
      return FAIL;
  }
  result=mysql_store_result(&stsql);
  
  recsum= mysql_num_rows(result);
//  num_fields = mysql_num_fields(result);
  i=0;
  while ((row = mysql_fetch_row(result)) && i<MAXFSLINENUM)
  {
//    lengths = mysql_fetch_lengths(result); 
     fs[i].time=atoi(row[0]);
     fs[i].price=atoi(row[1]);
     fs[i].avgprice=atoi(row[2]);
     fs[i].colume=atoi(row[3]);
     i++;   
  }
   mysql_free_result(result);
   getfsflag = 1;
   frechfsflag=1;
   curfslinesum = recsum;
   fssum=recsum;
   strcpy(stockcode,code);
   mouselinex=stocksum;
   return SUCCESS;  
}


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

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

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

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

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

  if(!sqlflag) return FAIL;

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

int stockSql::quickCreateTraceTable(char *code)
{
  char querystr[4096];
  char opt[]="( 
                no       INT(8) NOT NULL,
                time     INT(8) NOT NULL,
                price    FLOAT(8,3) DEFAULT 0 NOT NULL,
                colume   FLOAT(10,3) DEFAULT 0 NOT NULL,
                mrj      FLOAT(8,3) DEFAULT 0 NOT NULL,
                mcj      FLOAT(8,3) DEFAULT 0 NOT NULL,
                zdf      FLOAT(7,3) DEFAULT 0 NOT NULL,
                bz       VARCHAR(1) DEFAULT '1' NOT NULL,  
                PRIMARY KEY (no),
                KEY (time));";
  sprintf(querystr,"CREATE TABLE %s_tr%s",code,opt);
  mysql_query(&stsql,querystr);
  return SUCCESS;
}

int stockSql::createTraceTable(char *code)
{
  char querystr[4096];
  char opt[]="( 
                no       INT(8) NOT NULL,
                time     INT(8) NOT NULL,
                price    FLOAT(8,3) DEFAULT 0 NOT NULL,
                colume   FLOAT(10,3) DEFAULT 0 NOT NULL,
                mrj      FLOAT(8,3) DEFAULT 0 NOT NULL,
                mcj      FLOAT(8,3) DEFAULT 0 NOT NULL,
                zdf      FLOAT(7,3) DEFAULT 0 NOT NULL,
                bz       VARCHAR(1) DEFAULT '1' NOT NULL,  
                PRIMARY KEY (no),
                KEY (time));";
  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKTRACEDATABASE)) return FAIL;

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

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

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

int stockSql::selectTraceDatabase()
{
  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKTRACEDATABASE)) return FAIL;
  return SUCCESS;
}

int stockSql::selectTraceTable(char *code)
{
  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKTRACEDATABASE)) return FAIL;
//  if(!isExistFsTable(code)) return FAIL;
  return SUCCESS;
}

int stockSql::insertTraceData(char *code,int num,STOCKTRACE *data)
{
  char querystr[4096];
  int i;
    
//  if(!selectDayKlineTable(code))  return FAIL;
  if(!isExistTraceTable(code)) return FAIL;
  for(i=0;i<num;i++){
  sprintf(querystr,"INSERT INTO %s_tr 
      VALUES(%d,%d,%8.3f,%10.3f,%8.3f,%8.3f,%7.3f,'%c');",code,
      data[i].no,data[i].time,data[i].price,data[i].colume,
      data[i].mrj,data[i].mcj,data[i].zdf,data[i].bz);
   mysql_query(&stsql,querystr);
  }
  return SUCCESS;
}

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

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

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

int stockSql::quickDeleteTraceData(char *code)
{
  char querystr[4096];
    
  sprintf(querystr,"DELETE FROM %s_tr;",code);
  mysql_query(&stsql,querystr);
  return SUCCESS;
}

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

int stockSql::deleteTraceData(char *code,int time)
{
  char querystr[4096];
    
//  if(!selectDayKlineTable(code))  return FAIL;
 if(!isExistTraceTable(code)) return FAIL;
 sprintf(querystr,"DELETE FROM %s_tr WHERE time=%d;",code,time);
  if(!mysql_query(&stsql,querystr))
        return SUCCESS;
  else
        return FAIL;
}

int stockSql::getSqlTraceData(char *code)
{
  char querystr[4096];
  MYSQL_RES *result;
  MYSQL_ROW row;
//  unsigned int num_fields;
//  unsigned long *lengths;
  int i,add;
  int recsum;
    
  if(!selectTraceTable(code))  return FAIL;
  sprintf(querystr,"SELECT * FROM %s_tr;",code);
  if(mysql_query(&stsql,querystr)){
//    printf("get tr data: Error: %s\n",mysql_error(&stsql));  
      return FAIL;
  }
  result=mysql_store_result(&stsql);
  
  recsum= mysql_num_rows(result);
//  num_fields = mysql_num_fields(result);
  add=(recsum-MAXTRACENUM)>0?recsum-MAXTRACENUM:0;
  mysql_data_seek(result,add);
  i=0;
  while ((row = mysql_fetch_row(result)) && i<MAXTRACENUM)
  {
//    lengths = mysql_fetch_lengths(result); 
     
     trace[i].no=atoi(row[0]);
     trace[i].time=atoi(row[1]);
     trace[i].price=atof(row[2]);
     trace[i].colume=atof(row[3]);
     trace[i].mrj=atof(row[4]);
     trace[i].mcj=atof(row[5]);
     trace[i].zdf=atof(row[6]);
     trace[i].bz=row[7][0];
     i++;   
  }
   mysql_free_result(result);
   tracesum=(recsum-MAXTRACENUM)>0?MAXTRACENUM:recsum;
   return SUCCESS;
}


int stockSql::getSqlStockData(char *code)
{
   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);
   } 

   getSqlTraceData(code);
//      printf("get trace data fail.\n");
   getSqlDayKline(code);
//      printf("get kline data fail.\n");
   getSqlFsData(code);
//      printf("get fs data fail.\n");
   return SUCCESS;
}


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

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

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

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

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

  if(!sqlflag) return FAIL;

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

}


int stockSql::createInitTable()
{
  char querystr[4096];
  char opt[]="( 
                no        int(4) NOT NULL,
                initday   int(8) NOT NULL,
                nametblbz int(1) DEFAULT '0' NOT NULL,
                initflag  int(1) DEFAULT '0' NOT NULL);";
  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKDATABASE)) return FAIL;

  if(isExistInitTable()) return SUCCESS;
   sprintf(querystr,"CREATE TABLE %s%s",STOCKINITTBL,opt);
  if(!mysql_query(&stsql,querystr)){
//    printf("create nametbl %d Table SUCCESS!\n",bz);  
     sprintf(querystr,"INSERT INTO %s 
          VALUES(1,20000101,0,0);",STOCKINITTBL);
     mysql_query(&stsql,querystr);
     return SUCCESS;
  }
  else{
//    printf("create nametbl:Error:%s\n",mysql_error(&stsql));  
      return FAIL;
  }
}

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

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

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

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

  result=mysql_list_tables(&stsql,STOCKINITTBL);
  num= mysql_num_rows(result);
  mysql_free_result(result);
  if(num){
//     printf("init table exist success!\n");
     return SUCCESS;
  }
  else{
//      printf("init table not exist!\n");  
      return FAIL;
  }  
}

int stockSql::updateInitData(STOCKINIT data)
{
  char querystr[4096];
    
  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKDATABASE)) return FAIL;
  if(!isExistInitTable()) return FAIL;
  sprintf(querystr,"UPDATE %s set  initday=%8d,nametblbz=%1d,initflag=%1d WHERE no=1;",STOCKINITTBL,
          data.initday,data.nametblbz,data.initflag);
  if(!mysql_query(&stsql,querystr)){
//    printf("update init table success.\n");  
    return SUCCESS;
  }
  else{
//     printf("update init:Error:%s\n",mysql_error(&stsql));  
     return FAIL;
  }
}

int stockSql::getSqlInitData()
{
  char querystr[4096];
  MYSQL_RES *result;
  MYSQL_ROW row;

//  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKDATABASE)) return FAIL;
  sprintf(querystr,"SELECT * FROM %s where no=1;",STOCKINITTBL);
  if(mysql_query(&stsql,querystr)){
//    printf("get init data:Error: %s\n",mysql_error(&stsql));  
    return FAIL;
  }
  result=mysql_store_result(&stsql);
  
  row = mysql_fetch_row(result);
  stockinit.initday=atoi(row[1]);
  stockinit.nametblbz=atoi(row[2]);
  stockinit.initflag=atoi(row[3]);

  mysql_free_result(result);
  return SUCCESS;  
}