rdbModel::MysqlConnection Class Reference

#include <MysqlConnection.h>

Inheritance diagram for rdbModel::MysqlConnection:

List of all members.

Public Types
	VCONTINUE = 0
	VDONE
	VBRANCHDONE
	VERROR
	VERRORABORT
enum	VisitorState {   VCONTINUE = 0, VDONE, VBRANCHDONE, VERROR,   VERRORABORT }
Public Member Functions
	MysqlConnection (std::ostream *out=0, std::ostream *errOut=0)
virtual	~MysqlConnection ()
virtual bool	open (const std::string &host, const std::string &userid, const std::string &password, const std::string &dbName)
virtual bool	open (const std::string &parms)
virtual bool	close ()
virtual bool	isConnected ()
	Return true iff open has been done with no matching close.
std::ostream *	getOut () const
std::ostream *	getErrOut () const
virtual MATCH	matchSchema (Rdb *rdb, bool matchDbName=true)
virtual bool	insertRow (const std::string &tableName, const StringVector &colNames, const StringVector &values, int *auto_value=0, const StringVector *nullCols=0)
virtual unsigned int	update (const std::string &tableName, const StringVector &colNames, const StringVector &values, const Assertion *where=0, const StringVector *nullCols=0)
virtual ResultHandle *	select (const std::string &tableName, const StringVector &getCols, const StringVector &orderCols, const Assertion *where=0, int rowLimit=0, int rowOffset=0)
virtual ResultHandle *	dbRequest (const std::string &request)
virtual bool	compileAssertion (const Assertion *a, std::string &sqlString) const
virtual void	disableModify (bool disable)
virtual Visitor::VisitorState	visitRdb (Rdb *)
	This method sets if the visitor is recursive or not.
virtual Visitor::VisitorState	visitTable (Table *)
virtual Visitor::VisitorState	visitColumn (Column *)
virtual Visitor::VisitorState	visitIndex (Index *)
virtual Visitor::VisitorState	visitAssertion (Assertion *)
virtual VisitorState	visitInsertNew (InsertNew *)
virtual VisitorState	visitSupersede (Supersede *)
virtual VisitorState	visitQuery (Query *)
virtual VisitorState	visitSet (Set *)
virtual VisitorState	visitInterRow (InterRow *)
Private Types
	VISITORundefined
	VISITORmatch
enum	VISITOR { VISITORundefined, VISITORmatch }
Private Member Functions
bool	checkDType (Datatype *dtype, const std::string &sqlType)
Static Private Member Functions
static void	compileInit ()
static bool	compileComparison (Assertion::Operator *op, std::string &sqlString)
static bool	compileOperator (Assertion::Operator *op, std::string &sqlString)
Private Attributes
MYSQL *	m_mysql
bool	m_connected
std::string	m_dbName
std::ostream *	m_out
std::ostream *	m_err
VISITOR	m_visitorType
bool	m_matchDbName
MATCH	m_matchReturn
	Keep track of status during matching process.
Rdb *	m_rdb
	If successful match, save pointer to schema for use with smart insert.
std::vector< Column * >	m_ourCols
MYSQL_RES *	m_tempRes
	For query results while visiting.
std::map< std::string, unsigned int >	m_colIx
	Index by colname; data is row number with "SHOW COLUMNS.." result set.
std::string	m_primColName
bool	m_writeDisabled
std::string	m_host
std::string	m_user
std::string	m_password
Static Private Attributes
static bool	m_compileInit = false

---

Detailed Description

Class to handle connection to a MySQL database

• initiate connection
• make queries, including making returned data from queries available
• issue SQL statements such as INSERT and UPDATE which have no returned data other than status
• close down connection. Someday it might also have a method to create a database

Initial design will just use host, password, userid passed in. Will be up to caller to insure that the userid has the right privilages for the operations caller intends to do.

Definition at line 32 of file MysqlConnection.h.

---

Member Enumeration Documentation

enum rdbModel::MysqlConnection::VISITOR [private]

Someday we may want more than one kind of visitor; for example, might want one to create dbs

Enumerator:

VISITORundefined
VISITORmatch

Definition at line 158 of file MysqlConnection.h.

                 {
      VISITORundefined,
      VISITORmatch
    };

enum rdbModel::Visitor::VisitorState [inherited]

Enumerator:

VCONTINUE
VDONE
VBRANCHDONE
VERROR
VERRORABORT

Definition at line 34 of file Visitor.h.

                       {
      VCONTINUE = 0,
      VDONE,         // visitor has had its fill of visiting
      VBRANCHDONE,   // don't descend further; go to next sibling, if any
      VERROR,        // error during visit; perhaps can continue, though
      VERRORABORT    // error during visit; can't handle more
    };

---

Constructor & Destructor Documentation

rdbModel::MysqlConnection::MysqlConnection	(	std::ostream *	out = 0,
		std::ostream *	errOut = 0
	)

Open a connection Allowed operations will depend on userid, etc., specified return true if successful

Definition at line 70 of file MysqlConnection.cxx.

References m_err, and m_out.

                                                       :
    m_mysql(0), m_connected(0), m_out(out), m_err(errOut),
    m_visitorType(VISITORundefined), m_rdb(0), m_tempRes(0),
    m_writeDisabled(false)                                   {
    if (m_out == 0) m_out = &std::cout;
    if (m_err == 0) m_err = &std::cerr;
  }

rdbModel::MysqlConnection::~MysqlConnection

(

)

[virtual]

Definition at line 91 of file MysqlConnection.cxx.

References close(), and m_mysql.

                                    {
    close();
    delete m_mysql;
    return;
  }

---

Member Function Documentation

bool rdbModel::MysqlConnection::checkDType	(	Datatype *	dtype,
		const std::string &	sqlType
	)			[private]

Definition at line 729 of file MysqlConnection.cxx.

References compareEnumList(), extractSize(), rdbModel::Enum::getChoices(), rdbModel::Datatype::getEnum(), rdbModel::Datatype::getOutputSize(), rdbModel::Datatype::getType(), m_matchReturn, rdbModel::MATCHcompatible, rdbModel::MATCHfail, deljobs::string, rdbModel::Datatype::TYPEchar, rdbModel::Datatype::TYPEdatetime, rdbModel::Datatype::TYPEdouble, rdbModel::Datatype::TYPEenum, rdbModel::Datatype::TYPEint, rdbModel::Datatype::TYPEmediumint, rdbModel::Datatype::TYPEreal, rdbModel::Datatype::TYPEsmallint, rdbModel::Datatype::TYPEtimestamp, and rdbModel::Datatype::TYPEvarchar.

Referenced by visitColumn().

                                {
    std::string base;
    int sqlSize;
    if (dtype->getType() != Datatype::TYPEenum) {
      sqlSize = extractSize(sqlType);
    }

    // Cases  char, varchar, enum and datetime are handled entirely within
    // the switch statement, but most do the bulk of the work in
    // common, after the switch.
    switch (dtype->getType()) {
      case Datatype::TYPEenum: {
                                 base = "enum";
                                 if (sqlType.find(base) != 0) {
                                   m_matchReturn = MATCHfail;
                                   return false;
                                 }
                                 Enum* ourEnum = dtype->getEnum();
                                 // Finally compare local list of choices to those listed in sqlType
                                 // Local list is a vector; in sqlType they're quoted, comma separated
                                 return compareEnumList(ourEnum->getChoices(), sqlType);
                               }
      case Datatype::TYPEvarchar: {
                                    base = "varchar";
                                    if (sqlType.find(base) != 0) {
                                      m_matchReturn = MATCHfail;
                                      return false;
                                    }
                                    // size in db must be at least as large as size in Col.
                                    if (sqlSize < dtype->getOutputSize()) {
                                      m_matchReturn = MATCHfail;
                                      return false;
                                    }
                                    else if (sqlSize > dtype->getOutputSize()) {
                                      m_matchReturn = MATCHcompatible;
                                    }
                                    return true;
                                  }
      case Datatype::TYPEchar: {
                                 base = "char";
                                 if (sqlType.find(base) != 0) {
                                   m_matchReturn = MATCHfail;
                                   return false;
                                 }
                                 //  For char datatype unspecified size is equivalent to size=1
                                 if (!sqlSize) sqlSize = 1;
                                 // size in db must be at least as large as size in Col.
                                 if (sqlSize < dtype->getOutputSize()) {
                                   m_matchReturn = MATCHfail;
                                   return false;
                                 }
                                 else if (sqlSize > dtype->getOutputSize()) {
                                   m_matchReturn = MATCHcompatible;
                                 }
                                 return true;
                               }
      case Datatype::TYPEdatetime: {
                                     if (sqlType != "datetime") {
                                       m_matchReturn = MATCHfail;
                                       return false;
                                     }
                                     return true;
                                   }


      case Datatype::TYPEtimestamp: {
                                      base = "timestamp";
                                      break;
                                    }
      case Datatype::TYPEint: {
                                base = "int";
                                break;
                              }
      case Datatype::TYPEmediumint: {
                                      base = "mediumint";
                                      break;
                                    }
      case Datatype::TYPEsmallint: {
                                     base = "smallint";
                                     break;
                                   }
      case Datatype::TYPEreal: 
      case Datatype::TYPEdouble: {
                                   base = "double";
                                   break;
                                 }
      default: {  // Indicates bad xml file input.  Applications
                 //should have exited already
                 m_matchReturn = MATCHfail;
                 return false;
               }
    }     // end switch
    if (sqlType.find(base) != 0) {
      m_matchReturn = MATCHfail;
      return false;
    }
    // Now check size.  It's only for display, so mismatch is not failure
    if (sqlSize != dtype->getOutputSize()) {
      m_matchReturn = MATCHcompatible;
    }

    return true;
  }

bool rdbModel::MysqlConnection::close

(

)

[virtual]

Close the current open connection , if any. Return true if there was a connection to close and it was closed successfully

Implements rdbModel::Connection.

Definition at line 79 of file MysqlConnection.cxx.

References m_connected, m_mysql, and m_tempRes.

Referenced by main(), and ~MysqlConnection().

                              {
    if (m_tempRes) {
      mysql_free_result(m_tempRes);
      m_tempRes = 0;
    }
 std::cout<<"close connection ================================"<<std::endl;
    mysql_close(m_mysql);
    m_mysql = 0;
    m_connected = false;
    return true;
  }

bool rdbModel::MysqlConnection::compileAssertion	(	const Assertion *	a,
		std::string &	sqlString
	)			const [virtual]

compile method for assertions. Use it internally, but also make it publicly available so assertions belonging to a table can save the compiled version.

Implements rdbModel::Connection.

Definition at line 463 of file MysqlConnection.cxx.

References compileInit(), compileOperator(), rdbModel::RdbException::getMsg(), rdbModel::Assertion::getOperator(), m_compileInit, and m_out.

Referenced by select(), and update().

                                  {
    if (!m_compileInit) {
      compileInit();
      m_compileInit = true;
    }
    try {
      return compileOperator(a->getOperator(), sqlString);
    }
    catch (RdbException ex) {
      (*m_out) << std::endl << ex.getMsg() << std::endl;
      m_out->flush();
      return false;
    }
  }

bool rdbModel::MysqlConnection::compileComparison	(	Assertion::Operator *	op,
		std::string &	sqlString
	)			[static, private]

Result is appended to caller-supplied string Convention is to use " " around literal values Note no verification is done here; that operator is in fact a comparison or isNull operator. This is called internally only and that check will have been done before invoking this routine.

Definition at line 510 of file MysqlConnection.cxx.

References addArg(), rdbModel::FIELDTYPElit, rdbModel::Assertion::Operator::getCompareArgs(), rdbModel::Assertion::Operator::getCompareArgTypes(), rdbModel::Assertion::Operator::getOpType(), rdbModel::Assertion::Operator::getToBe(), rdbModel::opSymbols, and rdbModel::OPTYPEisNull.

Referenced by compileOperator().

                            {
    if (op->getToBe()) return false;  // can't compile

    OPTYPE opType = op->getOpType();
    if (opType == OPTYPEisNull) {
      sqlString +="(";
      sqlString += op->getCompareArgs()[0];
      sqlString += opSymbols[opType];
      sqlString += ")";
      return true;
    }
    sqlString += "(";

    bool literal0 = (op->getCompareArgTypes()[0] == FIELDTYPElit);
    bool literal1 = (op->getCompareArgTypes()[1] == FIELDTYPElit);

    addArg(literal0, op->getCompareArgs()[0], sqlString);
    sqlString += opSymbols[opType];
    addArg(literal1, op->getCompareArgs()[1], sqlString);
    sqlString += ")";

    return true;
  }

void rdbModel::MysqlConnection::compileInit

(

)

[static, private]

Definition at line 481 of file MysqlConnection.cxx.

References rdbModel::opSymbols, rdbModel::OPTYPEand, rdbModel::OPTYPEequal, rdbModel::OPTYPEexists, rdbModel::OPTYPEgreaterOrEqual, rdbModel::OPTYPEgreaterThan, rdbModel::OPTYPEisNull, rdbModel::OPTYPElessOrEqual, rdbModel::OPTYPElessThan, rdbModel::OPTYPEnot, rdbModel::OPTYPEnotEqual, and rdbModel::OPTYPEor.

Referenced by compileAssertion().

                                    {
    opSymbols[OPTYPEor] = " OR ";
    opSymbols[OPTYPEand] = " AND ";
    opSymbols[OPTYPEnot] = " NOT ";
    opSymbols[OPTYPEexists] = "EXISTS ";
    opSymbols[OPTYPEisNull] = " IS NULL";
    opSymbols[OPTYPEequal] = "=";
    opSymbols[OPTYPEnotEqual] = "<>";
    opSymbols[OPTYPElessThan] = "<";
    opSymbols[OPTYPEgreaterThan] = ">";
    opSymbols[OPTYPElessOrEqual] = "<=";
    opSymbols[OPTYPEgreaterOrEqual] = ">=";
    return;
  }

bool rdbModel::MysqlConnection::compileOperator	(	Assertion::Operator *	op,
		std::string &	sqlString
	)			[static, private]

Definition at line 535 of file MysqlConnection.cxx.

References compileComparison(), rdbModel::Assertion::Operator::isCompareOp(), rdbModel::opSymbols, rdbModel::OPTYPEexists, and deljobs::string.

Referenced by compileAssertion().

                            {

    //maqm     std::cout<<"in compileOperator() sqlString is00:"<<sqlString<<std::endl;
    if (op->isCompareOp() ) return compileComparison(op, sqlString);
    if (op->getToBe()) return false;  // can't compile in this case
    bool ret = true;

    const std::vector<Assertion::Operator*>& children = op->getChildren();
    unsigned nChild = children.size();

    // For single-child operators NOT,  exists, operator symbol
    // goes 1st, then operand
    if (nChild <= 1) { // operator goes first
      sqlString += opSymbols[op->getOpType()];

      // more special handling for EXISTS
      if (op->getOpType() == OPTYPEexists) {
        sqlString += "(SELECT * FROM " + op->getTableName();
        if (!nChild) {     // done
          sqlString += ")";
          return ret;
        }
        // else EXISTS child is object of a WHERE clause 
        sqlString += " WHERE(";
      }
      ret = compileOperator(children[0], sqlString);
      if (!ret) {
        std::string msg = 
          "rdbModel::MysqlConnection::compileOperator failed for operator "
          + opSymbols[op->getOpType()];
        throw RdbException(msg);
      }

      // Have an extra closing ")" for EXISTS with WHERE clause
      if (op->getOpType() == OPTYPEexists)       sqlString += ")";


      return ret;
    }

    // Otherwise put operator symbols between adjacent children.

    // First open parentheses
    sqlString += "(";

    std::string symbol = opSymbols[op->getOpType()];

    ret = compileOperator(children[0], sqlString);
    if (!ret) {
      std::string msg = 
        "rdbModel::MysqlConnection::compileOperator failed for operator "
        + symbol;
      throw RdbException(msg);
    }
    for (unsigned int iChild = 1; iChild < nChild; iChild++) {
      sqlString += symbol;

      ret = compileOperator(children[iChild], sqlString);
      if (!ret) {
        std::string msg = 
          "rdbModel::MysqlConnection::compileOperator failed for operator "
          + symbol;
        throw RdbException(msg);
      }
    }
    // Finally close paren.
    sqlString += ")";
    return ret;
  }

ResultHandle * rdbModel::MysqlConnection::dbRequest

(

const std::string &

request

)

[virtual]

Transmit raw request of any form to our other end. If it is a request that returns results, those results will be stored in a newly-allocated ResultHandle and dbRequest will return a pointer to it. Otherwise dbRequest will return a null pointer. Throw an exception if request fails for any reason.

Implements rdbModel::Connection.

Definition at line 409 of file MysqlConnection.cxx.

References genRecEmupikp::i, RawFileTools::itoa(), m_dbName, m_host, m_mysql, m_out, m_password, m_user, open(), and deljobs::string.

Referenced by main().

                                                                   {

    (*m_out) << std::endl << "# About to issue SQL request:" << std::endl;
    (*m_out) << request << std::endl;
    m_out->flush();
    
    int i=0;
    int mysqlRet = mysql_query(m_mysql, request.c_str());
    for (i=0;i<10;i++) {
    //  int mysqlRet = mysql_query(m_mysql, request.c_str());
      if (mysqlRet) {  
        //not connected
        std::string msg = 
          "rdbModel::MysqlConnection::dbRequest: mysql_query error, code ";
        std::string codeString;
        facilities::Util::itoa(mysqlRet, codeString);
        msg += codeString;
        (*m_out) << std::endl <<i<<"times not connected++++ "<< msg << std::endl;
        m_out->flush();
        fprintf(stderr, "mysql_query error %d: %s\n",mysql_errno(m_mysql),mysql_error(m_mysql));
        if (i>=9){
         throw RdbException(msg, mysqlRet);
          return 0;
        }
         mysql_close(m_mysql);
         m_mysql = 0;
         sleep(100);
         bool st = open(m_host,m_user,m_password,m_dbName);
         if(st==false) continue;
        mysqlRet = mysql_query(m_mysql, request.c_str());
      }else{
        break;
      }
    }
   

    MYSQL_RES *myres = mysql_store_result(m_mysql);
    if (!myres) {
      // Was it supposed to return data?
      if (mysql_field_count(m_mysql) == 0) { // no data expected
        return 0;
      }
      else {
        std::string msg =
          "rdbModel::MysqlConnection::dbRequest: expected data; none returned";
        (*m_out) << std::endl << msg << std::endl;
        m_out->flush();
        throw RdbException(msg);
        return 0;
      }
    }
    return new MysqlResults(myres);
  }

virtual void rdbModel::MysqlConnection::disableModify

(

bool

disable

)

[inline, virtual]

Turn select and update into no-ops: output SQL string for debugging but don't change db

Implements rdbModel::Connection.

Definition at line 137 of file MysqlConnection.h.

References m_writeDisabled.

Referenced by main().

{m_writeDisabled=disable;}

std::ostream* rdbModel::MysqlConnection::getErrOut

(

)

const [inline, virtual]

Implements rdbModel::Connection.

Definition at line 57 of file MysqlConnection.h.

References m_err.

{return m_err;}

std::ostream* rdbModel::MysqlConnection::getOut

(

)

const [inline, virtual]

Implements rdbModel::Connection.

Definition at line 56 of file MysqlConnection.h.

References m_out.

{return m_out;}

bool rdbModel::MysqlConnection::insertRow	(	const std::string &	tableName,
		const StringVector &	colNames,
		const StringVector &	values,
		int *	auto_value = 0,
		const StringVector *	nullCols = 0
	)			[virtual]

Typical derived class will form a syntactically correct INSERT statement from the input arguments and issue it to the dbms. Return true if row was inserted successfully If auto_value is non-zero and the table has an auto-increment column, its value will be returned. If nullCols is non-zero, insertRow will treat each string in the vector as a column name whose value should be set to NULL

Might also want to add a routine for INSERT ... SELECT

Implements rdbModel::Connection.

Definition at line 216 of file MysqlConnection.cxx.

References m_err, m_mysql, m_out, m_writeDisabled, and deljobs::string.

                                                                {
    std::string ins;
    if (auto_value) *auto_value = 0;

    // check that sizes of vectors match
    unsigned  nCol = colNames.size();    
    if (!nCol || (nCol != values.size()  ) ) {
      (*m_err) << " MysqlConnection::insertRow: vector lengths incompatible"
                << std::endl;
      m_err->flush();
      return false;
    }

    // caller should already have checked for validity and should
    // have supplied all necessary columns

    ins += "insert into " + tableName;
    ins += " set " + colNames[0] + "='" + values[0] + "' ";
    for (unsigned iCol = 1; iCol < nCol; iCol++) {
      ins += ", " + colNames[iCol] + "='" + values[iCol] + "' ";
    }
    if (nullCols) {
      if (nullCols->size() > 0) {
        unsigned nNull = nullCols->size();
        for (unsigned iNull = 0; iNull < nNull; iNull++) {
          ins += ", " + (*nullCols)[iNull] + "= NULL ";
        }
      }
    }

    (*m_out) << std::endl << "# INSERT string is:" << std::endl;
    (*m_out) << ins << std::endl;
    m_out->flush();

    if (m_writeDisabled) {
      (*m_out) << "write to Db previously disabled; INSERT not sent"
               << std::endl;
      m_out->flush();
      return true;
    }

    int mysqlRet = mysql_query(m_mysql, ins.c_str());

    if (mysqlRet) {
      (*m_out) << "MySQL error during INSERT, code " << mysqlRet << std::endl;
      m_out->flush();
      return false;
    }
    if (auto_value) {
      *auto_value = mysql_insert_id(m_mysql);
    }
    return true;
  }

virtual bool rdbModel::MysqlConnection::isConnected

(

)

[inline, virtual]

Return true iff open has been done with no matching close.

Implements rdbModel::Connection.

Definition at line 54 of file MysqlConnection.h.

References m_connected.

{return m_connected;}

MATCH rdbModel::MysqlConnection::matchSchema	(	Rdb *	rdb,
		bool	matchDbName = true
	)			[virtual]

Check to what degree local schema definition is compatible with remote db accessed via this connection. By default check db names match, but this may be disabled.

If match is successful, may also want to cache information about some things; for example, rows for which agent = "service"

Implements rdbModel::Connection.

Definition at line 191 of file MysqlConnection.cxx.

References rdbModel::Rdb::accept(), m_connected, m_matchDbName, m_matchReturn, m_visitorType, rdbModel::MATCHequivalent, rdbModel::MATCHfail, rdbModel::MATCHnoConnection, rdbModel::Visitor::VERROR, rdbModel::Visitor::VERRORABORT, and VISITORmatch.

Referenced by main().

                                                               {
    if (!m_connected) return MATCHnoConnection;

    m_matchDbName = matchDbName;

    // Check global characteristics; 
    // Could do this via Manager; seems a bit artificial, bypass for now
    m_visitorType = VISITORmatch;
    m_matchReturn = MATCHequivalent;
    unsigned int ret = rdb->accept(this);

    if ((ret == Visitor::VERROR) || (ret == Visitor::VERRORABORT)) {
      return MATCHfail;
    }
    else return m_matchReturn;
  }

bool rdbModel::MysqlConnection::open

(

const std::string &

parms

)

[virtual]

Parameter is normally path for an xml file descrbing the connection parameters

Implements rdbModel::Connection.

Definition at line 163 of file MysqlConnection.cxx.

References xmlBase::Dom::getAttribute(), m_err, m_out, open(), xmlBase::XmlParser::parse(), and deljobs::string.

                                                   {
    using XERCES_CPP_NAMESPACE_QUALIFIER DOMDocument;
    using XERCES_CPP_NAMESPACE_QUALIFIER DOMElement;
    xmlBase::XmlParser parser;
    DOMDocument* doc = parser.parse(parms.c_str(), "mysqlConnection");
    if (doc == 0) {
      (*m_err) << "parse of connection parameters failed" << std::endl;
      m_err->flush();
      return false;
    }
    DOMElement*  conn = doc->getDocumentElement();
    
    std::string host = xmlBase::Dom::getAttribute(conn, "host");
    std::string user = xmlBase::Dom::getAttribute(conn, "user");
    std::string password = xmlBase::Dom::getAttribute(conn, "password");
    std::string dbname = xmlBase::Dom::getAttribute(conn, "dbname");

    if (password.size() == 0 ) { // prompt for password?
      (*m_out) << "interactive login NYI " << std::endl;
      m_out->flush();
      return false;
    }

    return this->open(host, user, password, dbname);
  }

bool rdbModel::MysqlConnection::open	(	const std::string &	host,
		const std::string &	userid,
		const std::string &	password,
		const std::string &	dbName
	)			[virtual]

Implements rdbModel::Connection.

Definition at line 97 of file MysqlConnection.cxx.

References m_connected, m_dbName, m_err, m_host, m_mysql, m_out, m_password, m_user, RealDBUtil::npos, and deljobs::string.

Referenced by dbRequest(), main(), and open().

                                                      {
                             //     , unsigned int       port) {
    if (dbName.size() == 0) {
      (*m_err) << 
        "rdbModel::MysqlConnection::open : null db name not allowed!" <<
        std::endl;
      m_err->flush();
      return false;
    } 
   //huangb add
    m_host = host;
    m_user = user;
    m_password = password;

    m_mysql = new MYSQL;
    mysql_init(m_mysql);

    // 'host' argument is of the form hostname[:port]
    //  That is, port section is optional.  If not supplied, use
    // default port.
    std::string hostOnly;
    int port = 0;
    std::string::size_type colonLoc = host.find(":");
    if (colonLoc == std::string::npos) {
      hostOnly = host;
    }
    else {
      hostOnly = host.substr(0, colonLoc);
      std::string portString = host.substr(colonLoc+1);
      try {
        port = facilities::Util::stringToInt(portString);
      }
      catch (facilities::WrongType ex) {
        (*m_err) << "From MysqlConnection::connect.  Bad port: "
                 << ex.getMsg() << std::endl;
        m_err->flush();
        return false;
      }

    }
    //    mysql_init(m_mysql);
    std::cout<<"host is:"<<hostOnly.c_str()<<"::use is:: "<<user.c_str()<<":pass is::"<<password.c_str()<<":db is::"<<dbName.c_str()<<std::endl;
    MYSQL *connected = mysql_real_connect(m_mysql, hostOnly.c_str(),  
                                          user.c_str(),
                                          password.c_str(), dbName.c_str(),
                                          3306, NULL, 0);

    if (connected != 0) {  // Everything is fine.  Put out an info message
      (*m_out) << "Successfully connected to MySQL host " << 
        host << ", database " << dbName << std::endl;
      m_out->flush();
      m_connected = true;
      m_dbName = dbName;
    }
    else {
      (*m_err) <<  "Failed to connect to MySQL host " << host <<
        "with error:::: " << mysql_error(m_mysql) << std::endl;
      m_err->flush();
      m_connected = false;
    }
    return m_connected;
  }

ResultHandle * rdbModel::MysqlConnection::select	(	const std::string &	tableName,
		const StringVector &	getCols,
		const StringVector &	orderCols,
		const Assertion *	where = 0,
		int	rowLimit = 0,
		int	rowOffset = 0
	)			[virtual]

Support only for relatively simple SELECT, involving just one table.

Parameters:

	tableName
	getCols	vector of columns to be retrieved
	where	ptr to an Assertion object
	rowLimit	max number of rows to return
	rowOffset	offset for first row returned among those satisfying conditions; ignored if 0.

Returns:

If the SELECT succeeds, a pointer to an object which manages the returned data; else 0. Caller is responsible for deleting the ResultHandle object.

Implements rdbModel::Connection.

Definition at line 330 of file MysqlConnection.cxx.

References compileAssertion(), RawFileTools::itoa(), m_mysql, m_out, deljobs::string, and boss::where().

                                                         {
    std::string sqlString = "SELECT ";
    unsigned nGet = getCols.size();
    unsigned nOrder = orderCols.size();

     std::cout<<"tableName is:"<<tableName<<std::endl;

//     tableName="metadata_v2r1";
       
    sqlString += getCols[0];
    for (unsigned iGet = 1; iGet < nGet; iGet++) {
      sqlString += ",";
      sqlString += getCols[iGet];
    }
    sqlString +=  " FROM " + tableName +  " ";
     std::cout<<"sqlString is11:"<<sqlString<<std::endl;
   if (where != 0) {
      sqlString += " WHERE ";
      bool ret = compileAssertion(where, sqlString);
      if (!ret) return 0;
    }
   
    std::cout<<"sqlString is22:"<<sqlString<<std::endl;
  
    /*maqm  if (nOrder > 0 ) {
      sqlString += " ORDER BY " + orderCols[0]; 
      for (unsigned iOrder = 1; iOrder < nOrder; iOrder++) {
        sqlString += ",";
        sqlString += orderCols[iOrder];
      }
    }
   */ 
//     sqlString ="SELECT ser_no FROM metadata_v2r1  WHERE ((completion='OK') AND (instrument='LAT') AND (calib_type='MDC_t0') AND (flavor='vanilla'))";
    if (rowLimit > 0) {
      // SQL format is LIMIT offset,limit      or
      //               LIMIT limit             or
      //               LIMIT limit   OFFSET offset  [don't use this one]
      sqlString += " LIMIT ";
      std::string limitStr;
      if (rowOffset > 0) {
        facilities::Util::itoa(rowOffset, limitStr);
        sqlString += limitStr + ",";
      }
      limitStr.clear();
      facilities::Util::itoa(rowLimit, limitStr);
       std::cout<<"limitStr is:"<<limitStr<<std::endl;
      sqlString += limitStr;
    }

    (*m_out) << std::endl << " # About to issue SELECT:" << std::endl;
    (*m_out) << sqlString << std::endl;
    m_out->flush();
   //maqm add
//     sqlString="SELECT ser_no FROM metadata_v2r1";

     
    int mysqlRet = mysql_query(m_mysql, sqlString.c_str());
    if (mysqlRet) {
      std::string msg = 
        "rdbModel::MysqlConnection::select: mysql_query error, code ";
      std::string codeString;
      facilities::Util::itoa(mysqlRet, codeString);
      msg += codeString;
      (*m_out) << std::endl << msg << std::endl;
      m_out->flush();
      throw RdbException(msg, mysqlRet);
      return 0;
    }

    MYSQL_RES *myres = mysql_store_result(m_mysql);
    MysqlResults* results = new MysqlResults(myres);
    return results;
  }

unsigned int rdbModel::MysqlConnection::update	(	const std::string &	tableName,
		const StringVector &	colNames,
		const StringVector &	values,
		const Assertion *	where = 0,
		const StringVector *	nullCols = 0
	)			[virtual]

Generic UPDATE. Return value is number of rows changed.

Implements rdbModel::Connection.

Definition at line 275 of file MysqlConnection.cxx.

References compileAssertion(), m_err, m_mysql, m_out, m_writeDisabled, deljobs::string, and boss::where().

                                                                     {

    unsigned int nCol = colNames.size();
    if (nCol != values.size()) {
      (*m_err) << "rdbModel::mysqlConnection::update: ";
      (*m_err) << "Incompatible vector arguments " << std::endl;
      m_err->flush();
      return 0;
    }
    std::string sqlString = "UPDATE " + tableName + " SET ";
    sqlString += colNames[0] + " = '" + values[0] + "'";
    for (unsigned int iCol = 1; iCol < nCol; iCol++) {
      sqlString += "," + colNames[iCol] + " = '" + values[iCol] + "'";
    }
    if (nullCols) {
      unsigned nNull = nullCols->size();
      for (unsigned iNull = 0; iNull < nNull; iNull++) {
        sqlString += ", " + (*nullCols)[iNull] + "= NULL ";
      }
    }

    if (where) {
      sqlString += " WHERE ";
      bool ret = compileAssertion(where, sqlString);
      if (!ret) return 0;
    }
    (*m_out) << std::endl << "#  UPDATE to be issued:" << std::endl;
    (*m_out) << sqlString << std::endl;
    m_out->flush();
    if (m_writeDisabled) {
      (*m_out) << "write to Db previously disabled; UPDATE not sent"
               << std::endl;
      m_out->flush();
      return 0;
    }
    int mysqlRet = mysql_query(m_mysql, sqlString.c_str());

    if (mysqlRet) {
      (*m_out) << "rdbModel::MysqlConnection::update: ";
      (*m_out) << "MySQL error during UPDATE, code " << mysqlRet << std::endl;
      m_out->flush();
      return 0;
    }
    my_ulonglong nModLong = mysql_affected_rows(m_mysql);
    // Not much chance that we'll change more rows than will fit in just long
    unsigned nMod = nModLong;
    return nMod;


  }

Visitor::VisitorState rdbModel::MysqlConnection::visitAssertion

(

Assertion *

)

[virtual]

Implements rdbModel::Visitor.

Definition at line 841 of file MysqlConnection.cxx.

References rdbModel::Visitor::VCONTINUE.

                                                                {
    return Visitor::VCONTINUE;
  }

Visitor::VisitorState rdbModel::MysqlConnection::visitColumn

(

Column *

)

[virtual]

Implements rdbModel::Visitor.

Definition at line 685 of file MysqlConnection.cxx.

References checkDType(), rdbModel::Column::getDatatype(), rdbModel::Column::getName(), rdbModel::Column::isAutoIncrement(), m_colIx, m_matchReturn, m_primColName, m_tempRes, rdbModel::MATCHfail, rdbModel::Column::nullAllowed(), deljobs::string, rdbModel::Visitor::VCONTINUE, and rdbModel::Visitor::VERRORABORT.

                                                              {
    std::string myName = col->getName();
    if (m_colIx.find(myName) == m_colIx.end()) {
      m_matchReturn = MATCHfail;
      return Visitor::VERRORABORT;
    }
    unsigned int ix = m_colIx[myName];
    mysql_data_seek(m_tempRes, ix);
    MYSQL_ROW colDescrip = mysql_fetch_row(m_tempRes);

    // Type
    std::string sqlDtype = std::string(colDescrip[1]);
    Datatype* dtype = col->getDatatype();
    if (!checkDType(dtype, sqlDtype)) {
      m_matchReturn = MATCHfail;
      (*m_out) << "Failed dtype match of col " << myName << std::endl;
      return Visitor::VERRORABORT;
    }

    // Null
    bool nullable = (std::string(colDescrip[2]) == std::string("YES"));
    if (nullable != col->nullAllowed()) {
      m_matchReturn = MATCHfail;
      (*m_out) << "Failed null/not null match of col " << myName << std::endl;
      return Visitor::VERRORABORT;
    }
    // Key (PRI for primary, MUL if first in a multiple-field key
    // Save primary key info, if any
    if (std::string(colDescrip[3]) == std::string("PRI")) {
      m_primColName = myName;
    }

    // Field 4 is default
    // Extra (may say auto_increment)
    bool autoInc = 
      (std::string(colDescrip[5]) == std::string("auto_increment"));
    if (autoInc != col->isAutoIncrement()) {
      m_matchReturn = MATCHfail;
      (*m_out) << "Failed isAutoIncrement match of col " << myName << std::endl;
      return Visitor::VERRORABORT;
    }
    return Visitor::VCONTINUE;
  }

Visitor::VisitorState rdbModel::MysqlConnection::visitIndex

(

Index *

)

[virtual]

Implements rdbModel::Visitor.

Definition at line 836 of file MysqlConnection.cxx.

References rdbModel::Visitor::VCONTINUE.

                                                         {
    return Visitor::VCONTINUE;
    // might put something real here later
  }

Visitor::VisitorState rdbModel::MysqlConnection::visitInsertNew

(

InsertNew *

)

[virtual]

Implements rdbModel::Visitor.

Definition at line 845 of file MysqlConnection.cxx.

References rdbModel::Visitor::VCONTINUE.

                                                                {
    return Visitor::VCONTINUE;
  }

Visitor::VisitorState rdbModel::MysqlConnection::visitInterRow

(

InterRow *

)

[virtual]

Implements rdbModel::Visitor.

Definition at line 861 of file MysqlConnection.cxx.

References rdbModel::Visitor::VCONTINUE.

                                                              {
    return Visitor::VCONTINUE;
  }

Visitor::VisitorState rdbModel::MysqlConnection::visitQuery

(

Query *

)

[virtual]

Implements rdbModel::Visitor.

Definition at line 853 of file MysqlConnection.cxx.

References rdbModel::Visitor::VCONTINUE.

                                                        {
    return Visitor::VCONTINUE;
  }

Visitor::VisitorState rdbModel::MysqlConnection::visitRdb

(

Rdb *

)

[virtual]

This method sets if the visitor is recursive or not.

Implements rdbModel::Visitor.

Definition at line 609 of file MysqlConnection.cxx.

References rdbModel::Rdb::getDbName(), rdbModel::Rdb::getNTable(), m_dbName, m_matchDbName, m_matchReturn, m_mysql, rdbModel::MATCHcompatible, rdbModel::MATCHfail, rdbModel::Rdb::setConnection(), rdbModel::Visitor::VCONTINUE, rdbModel::Visitor::VDONE, and rdbModel::Visitor::VERRORABORT.

                                                        {

    if (m_matchDbName) {
      if (m_dbName != rdb->getDbName()) {
        m_matchReturn = MATCHfail;
        return Visitor::VDONE;
      }
    }

    unsigned int nLocal = rdb->getNTable();

    // Null pointer for 2nd argument means "list all tables"

    MYSQL_RES* res = mysql_list_tables(m_mysql, 0);
    if (!res) {
      m_matchReturn = MATCHfail;
      return Visitor::VERRORABORT;
    }
    unsigned int nRemote = mysql_num_rows(res);
    mysql_free_result(res);

    if (nRemote < nLocal) {
      m_matchReturn = MATCHfail;
      return Visitor::VDONE;
    }
    else if (nRemote > nLocal) m_matchReturn = MATCHcompatible;

    // Tell Rdb about this 
    rdb->setConnection(this);

    return Visitor::VCONTINUE;
  }

Visitor::VisitorState rdbModel::MysqlConnection::visitSet

(

Set *

)

[virtual]

Implements rdbModel::Visitor.

Definition at line 857 of file MysqlConnection.cxx.

References rdbModel::Visitor::VCONTINUE.

                                                    {
    return Visitor::VCONTINUE;
  }

Visitor::VisitorState rdbModel::MysqlConnection::visitSupersede

(

Supersede *

)

[virtual]

Implements rdbModel::Visitor.

Definition at line 849 of file MysqlConnection.cxx.

References rdbModel::Visitor::VCONTINUE.

                                                                {
    return Visitor::VCONTINUE;
  }

Visitor::VisitorState rdbModel::MysqlConnection::visitTable

(

Table *

)

[virtual]

Implements rdbModel::Visitor.

Definition at line 642 of file MysqlConnection.cxx.

References rdbModel::Table::getName(), m_colIx, m_matchReturn, m_mysql, m_out, m_primColName, m_tempRes, rdbModel::MATCHfail, deljobs::string, rdbModel::Visitor::VCONTINUE, and rdbModel::Visitor::VERRORABORT.

                                                              {
    const std::string& tName = table->getName();

    // Result set will have all fields for the table
    if (m_tempRes) {
      mysql_free_result(m_tempRes);
      m_tempRes = 0;
    }
    m_primColName.clear();

    std::string query = "SHOW COLUMNS FROM " + tName;

    (*m_out) << std::endl << "# About to issue SHOW COLUMNS request :" 
      << std::endl;
    (*m_out) << query << std::endl;
    m_out->flush();

    int ret = mysql_query(m_mysql, query.c_str());
    if (ret) {
      m_matchReturn = MATCHfail;
      return Visitor::VERRORABORT;
    }

    m_tempRes = mysql_store_result(m_mysql);
    if (!m_tempRes) {
      m_matchReturn = MATCHfail;
      return Visitor::VERRORABORT;
    }
    // Result set is a table with fields "Field"(the name) "Type" "Null"(yes
    // or no) "Key" "Default", "Extra"  
    // Make it easier for accept(Column* ) to find relevant information
    unsigned int nRow = mysql_num_rows(m_tempRes);
    m_colIx.clear();
    for (unsigned iRow = 0; iRow < nRow; iRow++) {
      MYSQL_ROW colDescrip = mysql_fetch_row(m_tempRes);
      std::string name = std::string(colDescrip[0]);
      std::cout<<"name is:"<<name<<std::endl;
      m_colIx[name] = iRow;
    }
    return Visitor::VCONTINUE;

  }

---

Member Data Documentation

std::map<std::string, unsigned int> rdbModel::MysqlConnection::m_colIx [private]

Index by colname; data is row number with "SHOW COLUMNS.." result set.

Definition at line 190 of file MysqlConnection.h.

Referenced by visitColumn(), and visitTable().

bool rdbModel::MysqlConnection::m_compileInit = false [static, private]

Definition at line 194 of file MysqlConnection.h.

Referenced by compileAssertion().

bool rdbModel::MysqlConnection::m_connected [private]

Definition at line 164 of file MysqlConnection.h.

Referenced by close(), isConnected(), matchSchema(), and open().

std::string rdbModel::MysqlConnection::m_dbName [private]

Definition at line 166 of file MysqlConnection.h.

Referenced by dbRequest(), open(), and visitRdb().

std::ostream* rdbModel::MysqlConnection::m_err [private]

Definition at line 168 of file MysqlConnection.h.

Referenced by getErrOut(), insertRow(), MysqlConnection(), open(), and update().

std::string rdbModel::MysqlConnection::m_host [private]

Definition at line 206 of file MysqlConnection.h.

Referenced by dbRequest(), and open().

bool rdbModel::MysqlConnection::m_matchDbName [private]

Definition at line 174 of file MysqlConnection.h.

Referenced by matchSchema(), and visitRdb().

MATCH rdbModel::MysqlConnection::m_matchReturn [private]

Keep track of status during matching process.

Definition at line 177 of file MysqlConnection.h.

Referenced by checkDType(), matchSchema(), visitColumn(), visitRdb(), and visitTable().

MYSQL* rdbModel::MysqlConnection::m_mysql [private]

Definition at line 163 of file MysqlConnection.h.

Referenced by close(), dbRequest(), insertRow(), open(), select(), update(), visitRdb(), visitTable(), and ~MysqlConnection().

std::vector<Column* > rdbModel::MysqlConnection::m_ourCols [private]

Also save list of columns we ("service") are responsible for Could organize this by table, or just use Column::getTableName()

Definition at line 184 of file MysqlConnection.h.

std::ostream* rdbModel::MysqlConnection::m_out [private]

Definition at line 167 of file MysqlConnection.h.

Referenced by compileAssertion(), dbRequest(), getOut(), insertRow(), MysqlConnection(), open(), select(), update(), and visitTable().

std::string rdbModel::MysqlConnection::m_password [private]

Definition at line 208 of file MysqlConnection.h.

Referenced by dbRequest(), and open().

std::string rdbModel::MysqlConnection::m_primColName [private]

Definition at line 192 of file MysqlConnection.h.

Referenced by visitColumn(), and visitTable().

Rdb* rdbModel::MysqlConnection::m_rdb [private]

If successful match, save pointer to schema for use with smart insert.

Definition at line 180 of file MysqlConnection.h.

MYSQL_RES* rdbModel::MysqlConnection::m_tempRes [private]

For query results while visiting.

Definition at line 187 of file MysqlConnection.h.

Referenced by close(), visitColumn(), and visitTable().

std::string rdbModel::MysqlConnection::m_user [private]

Definition at line 207 of file MysqlConnection.h.

Referenced by dbRequest(), and open().

VISITOR rdbModel::MysqlConnection::m_visitorType [private]

Definition at line 173 of file MysqlConnection.h.

Referenced by matchSchema().

bool rdbModel::MysqlConnection::m_writeDisabled [private]

Definition at line 195 of file MysqlConnection.h.

Referenced by disableModify(), insertRow(), and update().

---