The Deep Table

#pragma warning(disable: 4786)

#include "Eva.h"

enum
{
  col_CON = 0,
  col_SRC_TAB,
  col_SRC_KEY,
  col_TGT_TAB,
  col_TGT_KEY,
};

vector<string> selectList;
vector<string> fromList;
vector<string> whereList;



/**
   Find the connection 'conn' of the table 'realTable' in the connections list 'eConnections'
   if found return true and the 'targetTable' and the list of source and target keys ('srcKeys'
   and 'tgtKeys)


*/
bool findConnection (
      Eva & eConnections,
      const string & conn,
      const string & realTable,

      string & targetTable,
      vector<string> & srcKeys,
      vector<string> & tgtKeys
      )
{
   targetTable = "";
   srcKeys.clear ();
   tgtKeys.clear ();

   int indx = 0;
   for (indx = 0; indx < eConnections.rows (); indx ++)
      if (eConnections[indx][col_CON] == conn && eConnections[indx][col_SRC_TAB] == realTable)
         break;

   if (indx >= eConnections.rows ()) return false;
   targetTable = eConnections[indx][3];

   for (int rr = indx; rr < eConnections.rows (); rr ++)
   {
      if (eConnections[rr][col_CON] != conn ||
          eConnections[rr][col_SRC_TAB] != realTable ||
          eConnections[rr][col_TGT_TAB] != targetTable) break;

      srcKeys.push_back (eConnections[rr][col_SRC_KEY]);
      tgtKeys.push_back (eConnections[rr][col_TGT_KEY]);
   }

   return true;
}

void trim (string & str)
{
   while (str.size() > 0 && str[0] == ' ') str = str.substr(1);
}


/**
   Consumes a connection from a deep column string and returns the connection consumed

   Example:
      string deepCol = "one two field";
      string conn = consumeConnection (deepCol);
      // now conn is "one" and deepCol is "two field"
*/
string consumeConnection (string & deepRest)
{
   int pos = deepRest.find (" ");
   if (pos == -1) return "";

   string conn = deepRest.substr(0,pos);
   deepRest = deepRest.substr(pos);
   trim (deepRest);

   return conn;
}

/**
   Adds a string to a list avoiding duplicates
*/
void addToList (vector<string> & list, const string & str, bool unique = true)
{
   if (unique)
   {
      for (int ii = 0; ii < list.size(); ii ++)
         if (list[ii] == str) return;
   }
   list.push_back(str);
}


/**
   Algorithm that generates the deep SQL (generates the three lists)
*/
void deepSQL (Eva & eConnections, const string & baseTable, Eva & deepColumns)
{
   fromList.push_back (baseTable);

   for (int ii = 0; ii < deepColumns.rows (); ii ++)
   {
      string currTableAlias = baseTable;
      string currRealTable  = baseTable;
      string deepCol = deepColumns[ii][0];
      trim (deepCol);
      do
      {
         string conn = consumeConnection(deepCol);
         if (conn.length() == 0) break;

         string targetTab = "";
         vector<string> srcKey;
         vector<string> tgtKey;
         if (findConnection (eConnections, conn, currRealTable, targetTab, srcKey, tgtKey))
         {
            string aliasPlus = currTableAlias + "_" + conn;
            addToList(fromList, targetTab + " AS " +  aliasPlus);
            for (int jj = 0; jj < srcKey.size (); jj ++)
               addToList(whereList,
                         currTableAlias + "." + srcKey[jj] + " == " +
                         aliasPlus + "." + tgtKey[jj] );
            currTableAlias = aliasPlus;
            currRealTable = targetTab;
         }
         else
         {
            printf ("ERROR: Connection [%s] of table [%s] not found!\n", conn.c_str(), currRealTable.c_str());
            break;
         }
      } while (true);

      addToList(selectList, currTableAlias + "." + deepCol + " AS " +  currTableAlias + "_" + deepCol);
   }
}

void printSelect ()
{
   printf ("SELECT\n");
   int ii = 0;
   for (ii = 0; ii < selectList.size(); ii ++)
   {
      printf ((ii > 0) ? "   , ": "   ");
      printf ("%s\n", selectList[ii].c_str());
   }

   printf ("FROM\n");
   for (ii = 0; ii < fromList.size(); ii ++)
   {
      printf ((ii > 0) ? "   , ": "   ");
      printf ("%s\n", fromList[ii].c_str());
   }

   printf ("WHERE\n");
   for (ii = 0; ii < whereList.size(); ii ++)
   {
      printf ((ii > 0) ? "   AND ": "   ");
      printf ("%s\n", whereList[ii].c_str());
   }
   printf (";\n");
}

int main (int nn, char **aa)
{
   Eva conn;
   Eva deep;

   conn.addLine(EvaLine("customer  , tSales     , customerId , tCompanies   , id"));
   conn.addLine(EvaLine("product   , tSales     , productId  , tProducts    , prodID"));
   conn.addLine(EvaLine("provider  , tProducts  , providerID , tCompanies   , id"));
   conn.addLine(EvaLine("country   , tCompanies , countryId  , tISOCountries, isoA2"));

   deep.addLine(EvaLine("id"));
   deep.addLine(EvaLine("date"));
   deep.addLine(EvaLine("customer name"));
   deep.addLine(EvaLine("customer country name"));
   deep.addLine(EvaLine("product name"));
   deep.addLine(EvaLine("quantity"));
   deep.addLine(EvaLine("product provider name"));
   deep.addLine(EvaLine("product provider country name"));

   deepSQL (conn, "tSales", deep);
   printSelect ();

   printf ("done\n");

   return 0;
}