query_handler.cpp

Go to the documentation of this file.

/*****************************************************************************\
*                                                                             *
*  Name   : query_handler                                                     *
*  Author : Chris Koeritz                                                     *
*                                                                             *
*******************************************************************************
* Copyright (c) 2008-$now By Author.  This program is free software; you can  *
* redistribute it and/or modify it under the terms of the GNU General Public  *
* License as published by the Free Software Foundation; either version 2 of   *
* the License or (at your option) any later version.  This is online at:      *
*     http://www.fsf.org/copyleft/gpl.html                                    *
* Please send any updates to: fred@gruntose.com                               *
\*****************************************************************************/

#include "common.h"
#include "query_handler.h"
#include "login_info.h"

#include <basis/function.h>
#include <basis/log_base.h>
#include <basis/portable.h>
#include <basis/set.cpp>
#include <basis/string_array.h>
#include <data_struct/deep_array.cpp>
#include <opsystem/byte_filer.h>
#include <opsystem/directory.h>
#include <opsystem/filename.h>
#include <opsystem/ini_config.h>
#include <textual/list_parsing.h>

#include <malloc.h>

#define BASE_LOG(s) EMERGENCY_LOG(program_wide_logger(), s)
#define LOG(s) CLASS_EMERGENCY_LOG(program_wide_logger(), filename(portable::application_name()).basename().raw() + " " + s)


class deep_string_array : public deep_array<istring> {};



query_handler::query_handler()
: _global_current_db(NIL),
  _bind_strings(new deep_string_array),
  _bind_ints(new int_array)
{
  common_database_support::_error_was_seen = false;
}

query_handler::~query_handler()
{
  FUNCDEF("destructor");
  if (_global_current_db) {
    LOG(isprintf("closing database connection."));
    dbclose(_global_current_db);
    _global_current_db = NIL;
  }
  dbexit();
  WHACK(_bind_strings);
  WHACK(_bind_ints);
}

istring query_handler::get_bound_string(int index)
{ return _bind_strings->get(index); }

int query_handler::get_bound_int(int index)
{ return _bind_ints->get(index); }

bool query_handler::prepare_for_query(const istring &config_file,
    database_login_info &persistent_login_info)
{
  FUNCDEF("prepare_for_query");
  ini_configurator ini(config_file);

  database_login_info temp_info(persistent_login_info.db_section);
  bool worked = database_login_info::read_login_info(ini, temp_info);
  if (!worked) {
    LOG(istring("ERROR: failed to read db login info from ") + config_file);
    return false;
  }

  // if the login info is different, then close the old database and open
  // the new one with the parameters.
  bool retried_connection = false;
  if (!_global_current_db || !temp_info.identical(persistent_login_info)
      || common_database_support::_error_was_seen) {
    persistent_login_info = temp_info;
    retried_connection = true;

    if (common_database_support::_error_was_seen) {
      // if we saw an error previously, that's why we're taking the db down.
      LOG("handling previous db issue by re-opening database...");
      common_database_support::_error_was_seen = false;
    }

    persistent_login_info.update_freetds_configuration();
      // make sure the database knows about our parameter.

    bool worked = login_to_database(persistent_login_info);
    if (!worked) {
      LOG(istring("ERROR: failed to login to database ")
          + persistent_login_info.database
          + " using authentication info.");
      return false;
    }

    bool using_okay = use_database(persistent_login_info);
    if (!using_okay) {
      LOG(istring("ERROR: failed to USE database ")
          + persistent_login_info.database + " on server.");
      return false;
    }
  }

  if (!_global_current_db) {
    // we seem to have a serious problem here.  stop processing now.
    LOG("ERROR: failed to end up with a working database object");
    return false;
  } else {
    if (retried_connection) {
      LOG(istring("Now connected to database ")
          + persistent_login_info.database);
    }
  }

  return true;
}

bool query_handler::bind_columns()
{
  FUNCDEF("bind_columns");
  if (!_global_current_db) { LOG("ERROR: null database object"); return false; }

  bool to_return = true;  // hopeful at first.

  int db_cols = dbnumcols(_global_current_db);
  for (int i = 1; i <= db_cols; i++) {
    int type = dbcoltype(_global_current_db, i);
    switch (type) {
      case SYBINT1:
      case SYBINT2:
      case SYBINT4: {
        if (_bind_ints->length() < db_cols + 1) {
          _bind_ints->insert(_bind_ints->length(),
              db_cols + 2 - _bind_ints->length());
        }
        if (dbbind(_global_current_db, i, INTBIND, sizeof(int),
            (BYTE *) &_bind_ints->use(i)) != SUCCEED) {
          to_return = false;
          LOG(isprintf("ERROR: had problem with binding to int on column %d", i));
        }
        break;
      }
      case SYBCHAR:
      case SYBVARCHAR:
      case SYBTEXT: {
        if (_bind_strings->length() < db_cols + 1) {
          _bind_strings->insert(_bind_strings->length(),
              db_cols + 2 - _bind_strings->length());
        }
        if (_bind_strings->get(i).length() < MAX_DB_STRING + 1) {
          _bind_strings->put(i, istring('\0', MAX_DB_STRING + 1));
        }
        char *field = _bind_strings->use(i).access();
        if (SUCCEED != dbbind(_global_current_db, i, STRINGBIND, -1, (BYTE *)field)) {
//STRINGBIND
//VARYCHARBIND
          to_return = false;
          LOG(isprintf("ERROR: had problem with binding to string on column %d", i));
        }
        break;
      }
      default: {
        // we don't consider this case an error; we simply haven't implemented
        // the type handling yet.
        LOG(isprintf("unhandled type for column %d, type is %d", i, type));
        break;
      }
    }
  }
  return to_return;
}

bool query_handler::print_row(log_base &output)
{
  FUNCDEF("print_row");
  if (!_global_current_db) { LOG("ERROR: null database object"); return false; }
  bool to_return = true;
  istring to_show;
  for (int i = 1; i <= dbnumcols(_global_current_db); i++) {
    if (i != 1) to_show += " ";  // separator.
    int type = dbcoltype(_global_current_db, i);
    switch (type) {
      case SYBINT1:
      case SYBINT2:
      case SYBINT4: {
        to_show += isprintf("%d", _bind_ints->get(i));
        break;
      }
      case SYBCHAR:
      case SYBVARCHAR:
      case SYBTEXT: {
//STRINGBIND
//VARYCHARBIND
        istring found = (*_bind_strings)[i];
        found.shrink();  // get proper length.
        to_show += found;
        break;
      }
      default: {
        to_show += "UNHANDLED";
        break;
      }
    }
  }
  output.log(to_show);
  if (&output != &program_wide_logger()) {
    BASE_LOG(to_show);
  }
}

bool query_handler::show_this_row(int row_num, log_base &output)
{
  FUNCDEF("show_this_row");
  if (!_global_current_db) { LOG("ERROR: null database object"); return false; }
  bool to_return = true;
  istring to_show;
  for (int i = 1; i <= dbnumcols(_global_current_db); i++) {
    if (i != 1) to_show += "\t";  // separator.
    int type = dbcoltype(_global_current_db, i);
    switch (type) {
      case SYBINT1:
      case SYBINT2:
      case SYBINT4: {
        to_show += isprintf("I=%d", _bind_ints->get(i));
        break;
      }
      case SYBCHAR:
      case SYBVARCHAR:
      case SYBTEXT: {
//STRINGBIND
//VARYCHARBIND
        to_show += "S=";
        istring found = (*_bind_strings)[i];
        found.shrink();  // get proper length.
        to_show += found;
        break;
      }
      default: {
        to_show += "UNHANDLED";
        break;
      }
    }
  }
  output.log(isprintf("#%d: ", row_num) + to_show);
  if (&output != &program_wide_logger()) {
    LOG(isprintf("#%d: ", row_num) + to_show);
  }
}

void query_handler::show_column_structure()
{
  FUNCDEF("show_column_structure");
  if (!_global_current_db) { LOG("ERROR: null database object"); return; }
  for (int i = 1; i <= dbnumcols(_global_current_db); i++) {
    LOG(isprintf("   col %d: type=%d name=%s", i, dbcoltype(_global_current_db, i),
        dbcolname(_global_current_db, i)));
  }
}

void query_handler::show_results(bool verbose, log_base &output)
{
  FUNCDEF("show_results");
  if (!_global_current_db) { LOG("ERROR: null database object"); return; }
  int db_result = REG_ROW;
  // bail out when the result comes back other than success.
  int row_num = 1;
  BASE_LOG(istring('-', 42));
  bind_columns();
  while (db_result == REG_ROW) {
    // see whether there's a next row.
    db_result = dbnextrow(_global_current_db);
    if (db_result == REG_ROW) {
      if (verbose)
        show_this_row(row_num++, output);
      else
        print_row(output);
    } else if (db_result != NO_MORE_ROWS) {
      // report that an unexpected error occurred.      
      LOG(isprintf("ERROR: failure to get next row=%d", db_result));
    }
  }
  BASE_LOG(istring('-', 42));
}

bool query_handler::login_to_database(const database_login_info &cred)
{
  FUNCDEF("login_to_database");
  LOG(isprintf("about to logon as \"%s\"", cred.user_name.s()));

  // close any current database we might be looking at.
  if (_global_current_db) {
//      LOG(isprintf("closing previous database connection."));
    dbclose(_global_current_db);
    _global_current_db = NIL;
    dbexit();
  }

  dbinit();
  dberrhandle(common_database_support::syb_err_handler);
  dbmsghandle(common_database_support::syb_msg_handler);

  LOGINREC *database_login = dblogin();
  DBSETLPWD(database_login, cred.password.s());
  DBSETLUSER(database_login, cred.user_name.s());
  DBSETLAPP(database_login, portable::application_name().s());

//make note of port if we're using that instead.
  LOG(isprintf("about to connect to \"%s\" on instance %s",
      cred.db_host.s(), cred.db_instance.s()));

//make note of port if we're using that instead.
  _global_current_db = dbopen(database_login, cred.db_section.s());
  if (!_global_current_db) {
    LOG(isprintf("ERROR: unable to connect to \"%s\" on instance %s",
        cred.db_host.s(), cred.db_instance.s()));
    dbloginfree(database_login);  
    return NIL;
  }
  dbloginfree(database_login);  
  return !!_global_current_db;
}

bool query_handler::use_database(const database_login_info &cred)
{
  FUNCDEF("use_database");
  if (!_global_current_db) { LOG("ERROR: null database object"); return false; }
  LOG(isprintf("using database \"%s\"", cred.database.s()));
  if (cred.database.length()) {
    DBINT erc = dbuse(_global_current_db, cred.database.s());
    if (erc != SUCCEED) {
      LOG(isprintf("ERROR: unable to use database %s", cred.database.s()));
      return false;
    }
  }
  return true;
}

bool query_handler::process_query(const istring &query, log_base &output)
{
  FUNCDEF("process_query");
  if (!_global_current_db) { LOG("ERROR: null database object"); return false; }
  LOG(istring("process_query on: ") + query);
  dbfreebuf(_global_current_db);
  dbcmd(_global_current_db, query.s());
  dbsqlexec(_global_current_db);

  if (dbresults(_global_current_db) != SUCCEED) {
    LOG(isprintf("ERROR: was expecting a result set and got none."));
    return false;
  }
  show_results(false, output);
  dbcanquery(_global_current_db);
  return true;
}


/*
...grist for the mill of getting metadata queries done.

//this needs to become the "show me the tables" function.

  istring sql_query;  // the actual query string we'll use.
  istring query_limiter;  // additions on the end of the query.


// make this into a retrieve table names method.  probably the table name
// below is definitive for MS SQL.
  LOG("\n\n");

  table_name = "INFORMATION_SCHEMA.TABLES";
LOG(istring("selecting data from table ") + table_name);

  query_limiter = " where TABLE_NAME not like 'sys%' "
      "and TABLE_NAME not like 'tsvw_%' "
      "and TABLE_NAME not like 'MS%' "
      "and TABLE_NAME not like 'conflict_%' "
      "and TABLE_NAME not like 'ctsv_%' ";

//different query below; need to capture which columns in a query builder.
  sql_query = "select TABLE_NAME from ";
  sql_query += table_name;
  sql_query += query_limiter;
  dbfreebuf(current_db);
  dbcmd(_global_current_db, sql_query.s());
  dbsqlexec(_global_current_db);

  if (dbresults(_global_current_db) != SUCCEED) {
    LOG(isprintf("ERROR: failure--was expecting a result set."));
    return 1;
  }

  show_column_structure(_global_current_db);

  show_results(current_db);


//this is a grab column structure method.  make the table name a parameter.

  LOG("\n\n");

  table_name = "LL_SavedExpressions";
LOG(istring("selecting data from table ") + table_name);
  query_limiter = "";
  sql_query = "select top 1 * from ";
//special query with "top 1" for first item.
  sql_query += table_name;
  sql_query += query_limiter;
  dbfreebuf(current_db);
  dbcmd(_global_current_db, sql_query.s());
  dbsqlexec(_global_current_db);

  if (dbresults(_global_current_db) != SUCCEED) {
    LOG(isprintf("ERROR: failure--was expecting a result set."));
    return 1;
  }

  show_column_structure(_global_current_db);

  show_results(_global_current_db);

*/

---