source: tags/0.9/mysql_pool.cpp @ 9

/*
   Copyright 2013 Willem Vermin, SURFsara

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
 */
#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 "stopos_pool.h"
#include "mysql_pool.h"
// construcor
mysql_pool::mysql_pool()
{
  this->whoami = "mysql";
  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(const std::string &dbname)
{
  int rc;
  rc = this->purge_db();
  if (rc != 0)
    return rc;

  try
  {
    int rc;

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

    return 0;
  }
  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;
  }
}

int mysql_pool::purge_db(void)
{
  if (this->db_name == 0)
    return NOFILENAME;
  try
  {
    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;
}

int mysql_pool::get_record(std::string &r, const std::string &k, const std::string &l)
{
  try
  {
    std::string cmd;
    sql::ResultSet *res;
    cmd = "select data from " + sdb_name +
        " where pkey = '" + k + "'";
    res = this->stmt->executeQuery(cmd);
    res->beforeFirst();
    if (res->next() == 0)
      return NOTFOUND;
    r = res->getString("data"); 
    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(const std::string &r, 
                           const std::string &k,
                           const std::string &l)
{
  try
  {
    std::string cmd;

    if (r.size() >= reclen)
      return RECORDTOOLONG;

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

    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 std::string &k, const std::string &l)
{
  try
  {
    std::string cmd;

    cmd = "delete from " + sdb_name +
          " where pkey ='" + k + "'";

    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;
}