/*
      _______                          .__ .__   .__   .__                 
      \      \    ____   __ __ _______ |__||  |  |  |  |__|  ____    ____  
      /   |   \ _/ __ \ |  |  \\_  __ \|  ||  |  |  |  |  | /  _ \  /    \ 
     /    |    \\  ___/ |  |  / |  | \/|  ||  |__|  |__|  |(  <_> )|   |  \
     \____|__  / \___  >|____/  |__|   |__||____/|____/|__| \____/ |___|  /
    =========\/======\/=================================================\/==
  v0.01 04/JUL/2007 © Copyright 2007-2007 Scott D. Yelich SOME RIGHTS RESERVED
 .,-*~'`^`'~*-,._.,-*~'`^`'~*-,._.,-*~'`^`'~*-,._.-*~'`^`'~*-,._.,-*~'`^`'~*-,. 


  LICENSE:  Creative Commons Attribution 3.0 License.
  SEE:      http://creativecommons.org/licenses/by/3.0/


  Tue Aug 21 20:44:09 EDT 2007, v0.01 sdy
  Thu Aug 23 23:29:36 EDT 2007, v0.04 sdy


  This is the third version of simple class to facilitate working with
  sqlite3 from C++.  The first verion used the convenience function
  sqlite3_get_table which kept the entire result set in memory.  The second
  version used the legacy "exec" and a "two-step" double callback with the
  fisrt being a static member function receiving a passed in void* pointer
  that was static cast to Xql3 and then called a virtual callback.  Wow,
  fugly.  Buried in the docs, it states that use of "prepare" plus "step" is
  the new and preferred method of accessing the sqlite3 system, so although
  the legacy exec did just this, there were a few other legacy dependencies
  and if those were changed, it would probably break some code.  Therefore,
  this class will implement an "exec" that is similar to the legacy exec.
  This exec will call a virtual callback that if not overridden, simply
  returns (ie: returned results are ignored).

  This version of the class handles multi-part SQL statements.

*/

//  C++

//  for error in dtor ...
// #include <iostream>

//  PACKAGE

#include "Xql3.h"


Xql3::Xql3()
{
  //  must do a manual call to Xql3::open(std::string) ...
}

Xql3::Xql3(std::string const & dbname)
{
  open(dbname);
}


/*

  The destructor takes the necessary steps in order to close a database
  -- so that no data is lost or corrupted.  The var _stmt was moved to a
  class var so that upon an exception and call to dtor, the _stmt could
  be finalized and the db could be closed.  It's not necessary to call
  "close" in a catch -- although it won't hurt, if the exception is
  serious enough.

*/

Xql3::~Xql3()
{
  int rc = 0;
  try {
    Xql3::close();
  }
  catch (Xql3Exception & e) {
    //    std::cerr << "Xql3Exception in Xql3::~Xql3() ..." << std::endl;
  }
}

int
Xql3::open ( std::string const & name )
{
  _db_name = name.c_str();
  int rc = 0;
  if (rc = sqlite3_open(_db_name, &_db)) {
    throw Xql3Exception(1, "open");
  }
/*
//  I decided that using the busy_handler allowed for more control.  So
//  this is left commented out.
  if (rc = sqlite3_busy_timeout(_db, 25)) {  // 25ms?
    throw Xql3Exception(2, "set busy timeout");
  }
*/
  if (rc = sqlite3_busy_handler(_db, Xql3::busy, this)) {
    throw Xql3Exception(3, "set busy handler");
  }
  return rc;
}


/*

  close is called to make sure the database is closed so that no data is
  lost or corrupted.  The var _stmt was moved to a class var so that
  upon an exception and call to dtor, the _stmt could be finalized and
  the db could be closed.  It's not necessary to call "close" in a catch
  -- although it won't hurt, if the exception is serious enough.

*/

int
Xql3::close ( )
{
  int rc = 0;
  if (_db) {
    if (_stmt) {
      rc = sqlite3_finalize(_stmt);
    }
    if (rc = sqlite3_close(_db) ) {
      throw Xql3Exception(4, "close");
    }
    _db = 0;
  }
  return rc;
}


/*

  Ok, the idea here is that this callback is called for each line in the
  result of the SQL statement.  The header is the names of the columns
  for the result and data holds the string representation of the value
  of the result for that column.  This means that an int will be coerced
  into a string representation of the int -- and blobs have not been
  tested with this interface.  A return value other than a 0 would cause
  the legacy exec to not continue with any more steps -- this version of
  Xql3 does not check the return value from the callback for any reason.

*/

int
Xql3::callback(std::vector<std::string> & headers,
               std::vector<std::string> & data )
{
  int header_count = headers.size();
  int i;
  if (    _headers.size() == 0
       && header_count > 0 ) {
    for(i=0; i<header_count; i++){
      _headers.push_back(headers[i]);
    }
  }
  int data_count = data.size();
  if ( data_count > 0 ) {
    for(i=0; i<data_count; i++){
      _data.push_back(data[i]);
    }
  }
  return 0;
}


/*

  This is a simple routine to allow the sqlite3 system to retry the
  latest "step" ... basically this routine should decide if it should
  sleep and return a code that tells sqlite3 to try again, or to return
  a code that says to stop trying.  I picked a value of 200 for the
  number of retries and 5000 for microseconds to sleep.  These values
  aren't settable other than by changing the source code -- the values
  are totally arbitrary but seem to give a reasonable balance between
  speed of retry and the effect of the speed of retry -- both load as
  well as actually obtaining a lock.  For most simple SQL, the sqlite3
  lock period is remarkably short.  If something gets to 200 tries (in
  less than a second or two), the calling code can decide what to do --
  such as sleep, fail or other.

*/

int
Xql3::busy(void*,int cnt)
{
  if ( cnt < 200 ) {
    usleep (5000); // 5 ms + 10 ms [re]start-up built in?
    return 1;
  } else {
    return 0;   // returning 0 means stop trying...
  }
}


/*
    It'spossible that a subsequent succeeding call to the sqlite3 API 
    would reset both the errorcode as well as the errormsg, but this
    is good enough for most cases.
*/

std::string
Xql3::dberrmsg()
{
  return sqlite3_errmsg(_db);
}

int
Xql3::dberrcode()
{
  return sqlite3_errcode(_db);
}


/*

This is the heart of the Xql3 interface to sqlite3.  This method is
modelled after the exec in legacy.c.  However, this exec is not
intended to be functionally equivalent.  This routine accepts a
std::strng containing one or more SQL statements.  Each statement is
prepared using sqlite3_prepare_v2 (vs v1 of the old exec).  The
statement is then "stepped" where the header and data for each result
row are passed to a callback.  The default callback places the header
and data into the class vector of streaings _header and _data,
respectively.

*/

int
Xql3::exec(std::string const & sql_text)
{

  int rc = SQLITE_OK;

  if ( sql_text.size() == 0 ) {
    return rc;
  }

  std::vector<std::string> headers;
  std::vector<std::string> data;

  int i;
  int ncols;
  _stmt = 0;

  const char * zSql;
  const char * zLeftover = sql_text.c_str();

  //  clear out the old results ...
  _headers.clear();
  _data.clear();

  while (1) { //  loop for all sql statements ...

    _stmt = 0;
    zSql = zLeftover;
    if (!zSql[0]) {
      break;
    }
    if ( SQLITE_OK != (rc = sqlite3_prepare(_db, zSql, -1, &_stmt, &zLeftover))) {
      throw Xql3Exception(5, "prepare");
    }
    if (!_stmt) {  // parsed blank or comment?
      continue;
    }

    ncols = sqlite3_column_count(_stmt);

    while (1) {  // loop for all results of an individual statement ...

      rc = sqlite3_step(_stmt);

      if (SQLITE_ROW == rc) {
        if (0 == headers.size()) {
          for(i=0; i<ncols; i++){
            headers.push_back((char *)sqlite3_column_name(_stmt, i));
          }
        }
        data.clear();
        for(i=0; i<ncols; i++){
          data.push_back((char *)sqlite3_column_text(_stmt, i));
        }

        callback(headers, data); //  header/data just for this *row* ...

      } else {
        if ( SQLITE_DONE != rc ) {
          throw Xql3Exception(5, "step");
        }
        rc = sqlite3_finalize(_stmt);
        _stmt = 0;
        zSql = zLeftover;  // advance to next statement ...
        while (isspace((unsigned char)zSql[0])) zSql++;
        break;
      }

    }

  }

  if (_stmt) sqlite3_finalize(_stmt);

  return rc;
}