source: tags/0.5/mysql_pool.cpp @ 9

#include <iostream>
#include "wutils.h"
#include "stopos.h"

#include "mysql_connection.h"
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>

#include "mysql_pool.h"
// construcor
mysql_pool::mysql_pool()
{
  std::string schema = "stopos";
  sql::Driver *driver;
  driver = get_driver_instance();
  //  connection, mysqlusername, mysqlpassword
  this->connection = driver->connect("tcp://127.0.0.1:3307",schema,"x");
  this->connection->setSchema(schema);
  this->stmt = this->connection->createStatement();
}

mysql_pool::~mysql_pool(void)
{
  delete this->stmt;
  delete this->connection;
}

int mysql_pool::create_db(void)
{
  int rc;
  rc = this->purge_db();
  if (rc != 0)
    return rc;

  try
  {
    int rc;

    rc = this->open_db();
    if (rc != 0)
      return rc;

    this->status.init();

    rc = put_status();

    if (rc != 0)
    {
      this->close_db();
      return this->STOREERROR;
    }

    rc = this->close_db();
    if (rc != 0)
      return rc;
  }
  catch (sql::SQLException &e)
  {
    std::cerr << "# ERR: SQLException in " << __FILE__;
    std::cerr << "(" << __FUNCTION__ << ") on line "
       << __LINE__ << std::endl;
    std::cerr << "# ERR: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ", SQLState: " << e.getSQLState() <<
   " )" << std::endl;
    return OPENERROR;
  }
  return 0;
}

int mysql_pool::purge_db(void)
{
  if (this->db_name == 0)
    return NOFILENAME;
  try
  {
    int rc;
    std::string cmd = "DROP TABLE IF EXISTS " + this->sdb_name;
    this->stmt->execute(cmd);

    cmd = "CREATE TABLE " + this->sdb_name +
          "(pkey CHAR(20) NOT NULL,"
          " PRIMARY KEY (pkey),"
          " INDEX(pkey),"
          " data VARCHAR(" +
            NumberToString(this->reclen) +
          +") NOT NULL)";
    this->stmt->execute(cmd);
  }
  catch (sql::SQLException &e)
  {
    std::cerr << "# ERR: SQLException in " << __FILE__;
    std::cerr << "(" << __FUNCTION__ << ") on line "
       << __LINE__ << std::endl;
    std::cerr << "# ERR: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ", SQLState: " << e.getSQLState() <<
   " )" << std::endl;
    return PURGEERROR;
  }
    return 0;
}

int mysql_pool::open_db(void)
{
  if (! this->db_name)
    return OPENERROR;

  if (this->db_open)
    return 0;

  try
  {
    sql::ResultSet *res;
    std::string cmd;
    cmd="select count(*) as count "
        "from information_schema.tables "
        "where table_schema='stopos' "
        "and table_name = '"+this->sdb_name+"'";
    res = this->stmt->executeQuery(cmd);
    res->beforeFirst();
    if (res->next())
      if (res->getInt("count") == 0)
        return OPENERROR;
    delete res;

    cmd = "lock tables " + this->sdb_name + " write";
    this->stmt->execute(cmd);
  }
  catch (sql::SQLException &e)
  {
    std::cerr << "# ERR: SQLException in " << __FILE__;
    std::cerr << "(" << __FUNCTION__ << ") on line "
       << __LINE__ << std::endl;
    std::cerr << "# ERR: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ", SQLState: " << e.getSQLState() <<
   " )" << std::endl;
    return OPENERROR;
  }

  this->db_open = 1;

  return 0;
}

int mysql_pool::close_db(void)
{
  if (!this->db_open)
    return 0;

  std::string cmd;
  try
  {
    cmd = "unlock tables";
    this->stmt->execute(cmd);
  }
  catch (sql::SQLException &e)
  {
    std::cerr << "# ERR: SQLException in " << __FILE__;
    std::cerr << "(" << __FUNCTION__ << ") on line "
       << __LINE__ << std::endl;
    std::cerr << "# ERR: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ", SQLState: " << e.getSQLState() <<
   " )" << std::endl;
    return OPENERROR;
  }

  this->db_open = 0;

  return 0;
}

void mysql_pool::dump_db(void)
{

}

int mysql_pool::get_record(datarecord &r, const stopos_key &k)
{
  try
  {
    std::string cmd;
    sql::ResultSet *res;
    cmd = "select data from " + sdb_name +
        " where pkey = '" + k.str() + "'";
    res = this->stmt->executeQuery(cmd);
    res->beforeFirst();
    if (res->next() == 0)
      return NOTFOUND;
    std::string rec = res->getString("data");
    r = datarecord(rec,dbsep);
    delete res;
  }
  catch (sql::SQLException &e)
  {
    std::cerr << "# ERR: SQLException in " << __FILE__;
    std::cerr << "(" << __FUNCTION__ << ") on line "
       << __LINE__ << std::endl;
    std::cerr << "# ERR: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ", SQLState: " << e.getSQLState() <<
   " )" << std::endl;
    return NOTFOUND;
  }

  return 0;
}

int mysql_pool::put_record(datarecord &r, const stopos_key &k)
{
  try
  {
    std::string cmd;

    cmd = "replace " + sdb_name +
      " values('" + k.str() + "','" +
      r.str(dbsep) + "')";

    this->stmt->execute(cmd);
  }
  catch (sql::SQLException &e)
  {
    std::cerr << "# ERR: SQLException in " << __FILE__;
    std::cerr << "(" << __FUNCTION__ << ") on line "
       << __LINE__ << std::endl;
    std::cerr << "# ERR: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ", SQLState: " << e.getSQLState() <<
   " )" << std::endl;
    return STOREERROR;
  }

  return 0;
}

int mysql_pool::remove_record(const stopos_key &k)
{
  try
  {
    std::string cmd;

    cmd = "delete from " + sdb_name +
          " where pkey ='" + k.str() + "'";

    this->stmt->execute(cmd);
  }
  catch (sql::SQLException &e)
  {
    std::cerr << "# ERR: SQLException in " << __FILE__;
    std::cerr << "(" << __FUNCTION__ << ") on line "
       << __LINE__ << std::endl;
    std::cerr << "# ERR: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ", SQLState: " << e.getSQLState() <<
   " )" << std::endl;
    return STOREERROR;
  }

  return 0;
}

int mysql_pool::get_status(void)
{
  try
  {
    std::string cmd;
    sql::ResultSet *res;

    cmd = "select data from " + sdb_name +
        " where pkey = '" + this->statuskey.str() + "'";

    res = this->stmt->executeQuery(cmd);
    res->beforeFirst();
    if (res->next() == 0)
    {
      return NOTFOUND;
    }

    std::string rec = res->getString("data");
    this->status = statusrecord(rec,dbsep);
    delete res;
  }
  catch (sql::SQLException &e)
  {
    std::cerr << "# ERR: SQLException in " << __FILE__;
    std::cerr << "(" << __FUNCTION__ << ") on line "
       << __LINE__ << std::endl;
    std::cerr << "# ERR: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ", SQLState: " << e.getSQLState() <<
   " )" << std::endl;
    return FETCHERROR;
  }

  return 0;
}

int mysql_pool::put_status(void)
{
  try
  {
    std::string cmd;

    cmd = "replace " + sdb_name +
      " values('" + statuskey.str() + "','" +
      this->status.str(dbsep) + "')";

    this->stmt->execute(cmd);
  }
  catch (sql::SQLException &e)
  {
    std::cerr << "# ERR: SQLException in " << __FILE__;
    std::cerr << "(" << __FUNCTION__ << ") on line "
       << __LINE__ << std::endl;
    std::cerr << "# ERR: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ", SQLState: " << e.getSQLState() <<
   " )" << std::endl;
    return STOREERROR;
  }
  return 0;
}