CppDB/test/test_mysql_reconnect.cpp

//////////////////////////////////////////////////////////////////////
// This tests MySQL backend for opt_reconnect operation
// Written by Jonathan A. Foster <jon@jfpossibilities.com>
// Started March 31st, 2021
// Copyright JF Possibilities, Inc. All rights reserved.
//
// This will make several queries, KILL its own connection and then
// remake the same queries in the hopes of reusing cached, prepared
// statements. Flip opt_reconnect to try it with and without.
//
// Test passed: 2021-03-31 v0.3.1-j3
//////////////////////////////////////////////////////////////////////
#include <iostream>
#include <string>
#include <stdexcept>
#include <cppdb/frontend.h>
using namespace std;
using namespace cppdb;



//////////////////////////////////////////////////////////////////////
// GLOBALS
//////////////////////////////////////////////////////////////////////

session db;
string  pid;



//////////////////////////////////////////////////////////////////////
// Simple R/O queries
//
// We're hoping these statements get cached...
//////////////////////////////////////////////////////////////////////

// Get our connection ID, stash it in "pid" and return a reference
string &qPid() {
  result q = db << "select connection_id()";
  while(q.next()) q >> pid;
  cout << pid << endl;
  return pid;
}



// dump process list
void qProcesses() {
  string id, user, host, dbn, command/*, time, state*/;
  result q = db << "show processlist";
  while(q.next()) {
    q >> id >> user >> host >> dbn >> command;
    cout << id << '\t' << user << '\t' << host << '\t' << dbn << '\t'
         << command << endl;
  }
}



//////////////////////////////////////////////////////////////////////
// A parameratized write query
//////////////////////////////////////////////////////////////////////

struct cUser {
  int    id;
  string name;
  string phone;
  bool   keep_private;
};

ostream &operator<<(ostream &o, const cUser &u) {
  o << u.id << ": " << u.name << '\t' << u.phone;
  if(u.keep_private) o << " **PRIVATE**";
  return o;
}



struct cUserTable {
  cUserTable &create() {
    db <<
      "CREATE TABLE user ("
        "id int not null primary key auto_increment,"
        "name char(64) not null,"
        "phone char(24) not null,"
        "keep_private tinyint(1) unsigned not null default 0"
      ")" << exec;
    return *this;
  }

  cUserTable &drop() {
    db <<
      "DROP TABLE IF EXISTS user" << exec;
    return *this;
  }

  cUserTable &operator<<(cUser &u) {
    statement q =
      db << "INSERT INTO user (name, phone, keep_private) VALUES (?,?,?)"
         << u.name << u.phone << u.keep_private
         << exec;
    u.id = q.last_insert_id();
    return *this;
  }

  cUserTable &operator>>(cUser &u) {
    result q = db << "SELECT id, name, phone, keep_private FROM user WHERE id=?" << u.id;
    if(q.next())
      q >> u.id >> u.name >> u.phone >> u.keep_private;
    else
      throw runtime_error("User "+to_string(u.id)+" not found");
    return *this;
  }

};



//////////////////////////////////////////////////////////////////////
// MAIN() - put it all together
//////////////////////////////////////////////////////////////////////
cUser jon    {0, "jon",     "555-555-5550", false},
      carol  {0, "carol",   "555-555-5551",  true},
      goliath{0, "goliath", "555-555-5552", false},
      kevin  {0, "kevin",   "555-555-5554", false},
      kelly  {0, "kelly",   "555-555-5555",  true},
      ivy    {0, "ivy",     "555-555-5556", false};

int main(int argct, char **args) {
  result q;
  cUserTable tbl;

  /// SETUP ///

  try {
    db.open(
      "mysql:"
      "user=testing;"
      "password=testing;"
      "database=test_cppdb;"
      "opt_reconnect=1" // isn't needed with my auto-auto-reconnect patch
    );
    qPid();
    qProcesses();
    tbl.drop();
    tbl.create();
    tbl << jon << carol << goliath;
    tbl >> jon >> carol >> goliath;
    cout << jon << '\n' << carol << '\n' << goliath << endl;

    /// BREAK ///

    try {
      db << "kill ?" << pid << exec;
    } catch(const cppdb_error &e) {
      cout << "  Expected error: " << e.what() << "\n";
    }

    /// TEST ///

    tbl << kevin << kelly << ivy;
    tbl >> kevin >> kelly >> ivy;
    cout << kevin << '\n' << kelly << '\n' << ivy << endl;
    qProcesses();
    qPid();

    /// FAIL ///

  } catch(const std::exception &e) {
    cerr << "EXCEPTION: " << e.what() << endl;
    return 2;
  }

    /// PASS ///

  cout << "\n\n*** PASS ***\n";
  return 0;
}