/*
# ---------------------------------------------------------------------
#
# Copyright (c) CREATIS (Centre de Recherche en Acquisition et Traitement de l'Image 
#                        pour la Santé)
# Authors : Eduardo Davila, Frederic Cervenansky, Claire Mouton
# Previous Authors : Laurent Guigues, Jean-Pierre Roux
# CreaTools website : www.creatis.insa-lyon.fr/site/fr/creatools_accueil
#
#  This software is governed by the CeCILL-B license under French law and 
#  abiding by the rules of distribution of free software. You can  use, 
#  modify and/ or redistribute the software under the terms of the CeCILL-B 
#  license as circulated by CEA, CNRS and INRIA at the following URL 
#  http://www.cecill.info/licences/Licence_CeCILL-B_V1-en.html 
#  or in the file LICENSE.txt.
#
#  As a counterpart to the access to the source code and  rights to copy,
#  modify and redistribute granted by the license, users are provided only
#  with a limited warranty  and the software's author,  the holder of the
#  economic rights,  and the successive licensors  have only  limited
#  liability. 
#
#  The fact that you are presently reading this means that you have had
#  knowledge of the CeCILL-B license and that you accept its terms.
# ------------------------------------------------------------------------
*/


#include <creaImageIOSQLiteTreeHandler.h>
#include <creaImageIOSystem.h>
#include <creaImageIOGimmick.h>

#include "CppSQLite3.h"

#include <sys/stat.h>

#include <deque>

#include <creaWx.h>
#include <boost/algorithm/string.hpp>
using namespace crea;



namespace creaImageIO
{
  using namespace tree;


  //=============================================================
  SQLiteTreeHandler::SQLiteTreeHandler(const std::string& filename)
    : mFileName(filename)
  {
    mDB = new CppSQLite3DB;
	mIsAdding=false;
    //GimmickMessage(1,"SQLite version : "		   <<std::string(mDB->SQLiteVersion())<< std::endl);
  }
  //=============================================================

  //=============================================================
  SQLiteTreeHandler::~SQLiteTreeHandler()
  {
    delete mDB;
  }
  //=============================================================
  

  //=====================================================================


  //=====================================================================
  bool SQLiteTreeHandler::Open(bool writable)
  {

	  //sqlite3_exec(mDB, "PRAGMA synchronous=OFF ", NULL, 0, &errMsg); 
    SetWritable(writable);
    return DBOpen();
  }

  //=====================================================================
  bool SQLiteTreeHandler::Create(bool writable)
  {
    //    std::cout << "***> SQLiteTreeHandler::New('"<<GetFileName()<<"')"<<std::endl;
    SetWritable(writable);
    return DBCreate();
  }
  //=====================================================================


  //=====================================================================
  bool SQLiteTreeHandler::Close()
  {
    return true;
  }
  //=====================================================================


  //=====================================================================
  bool SQLiteTreeHandler::Destroy()
  {
    return false;
  }
  
  //===================================================================== 

  //===================================================================== 
  int SQLiteTreeHandler::LoadChildren(tree::Node* parent, int maxlevel)
  {	 
	//mDB->execDML("PRAGMA synchronous=ON ");
    if (parent==0) parent = GetTree().GetTree();
    return DBLoadChildren(parent,maxlevel);
  }
  //===================================================================== 




  //===================================================================== 
  void SQLiteTreeHandler::UnLoad(tree::Node* n)
  {
  }
  //===================================================================== 

  //===================================================================== 
  int SQLiteTreeHandler::AddBranch( const AttributeMapType& attr )
  {
    tree::Node* parent = DBGetParent(attr);
    DBGraftToParent(parent,attr);
    return (parent->GetLevel()+1);
	  
  }
  //===================================================================== 
 

  //===================================================================== 
   bool SQLiteTreeHandler::Remove(tree::Node* node)
   {
   DBRecursiveRemoveNode(node);
 
    //    std::cout << "DELETE"<<std::endl;
   bool remove=false;
   tree::Node* parent=node->GetParent();
    if (parent)
      {
	int nC = parent->RemoveChildrenFromList(node);
	if(nC>0 && parent->GetLevel()>0)
	{	
		std::stringstream out;
		out <<nC;
		SetAttribute(parent,"NumberOfChildren",out.str());
	}
	else
	{
		remove=true;
	}

      }
    delete node;
	if(remove&&parent->GetLevel()>0)
	{
		Remove(parent);
	}
    //    std::cout << "DELETE OK"<<std::endl;
    return true;
   }
  
  //===================================================================== 

  //===================================================================== 
  /// Sets an attribute of a Node
  bool SQLiteTreeHandler::SetAttribute(tree::Node* n, 
				       const std::string& key,
				       const std::string& value)
  {
    if (n==0) n=GetTree().GetTree();
    return DBSetAttribute(n,key,value);
  }
  //===================================================================== 
   //===================================================================== 
  /// Sets an attribute
  void SQLiteTreeHandler::SetAttribute(const std::string& levelDescriptor, 
			      const std::string& key,
			      const std::string& value,
				  const std::string& searchParam, 
				  const std::string& searchVal)
  {
	DBSetAttribute(levelDescriptor,key,value,searchParam, searchVal);
  }
  //===================================================================== 
  /// Deletes a tuple
  void SQLiteTreeHandler::DeleteTuple(std::string levelDescriptor, 
								std::string key, std::string value)
  {
    DBDelete(levelDescriptor,key,value);
  }
  //===================================================================== 













  //=====================================================================
  // SQLite DB specific methods
  //=====================================================================




  //=====================================================================
  char* format_sql(const std::string& s)
  { 
    return sqlite3_mprintf("%q",s.c_str());
  }
  //=====================================================================

  
  //=====================================================================
#define QUERYDB(QUER,RES)						\
    try									\
      {									\
	GimmickMessage(2,"SQL query: '"<<QUER<<"'"<<std::endl);		\
	RES = mDB->execQuery(QUER.c_str());				\
      }									\
    catch (CppSQLite3Exception& e)					\
      {									\
	GimmickError("SQLite query '"<<QUER<<"' : "			\
		     << e.errorCode() << ":"				\
		     << e.errorMessage() );				\
      }									\
    
  //=====================================================================
  
  //=====================================================================
#define UPDATEDB(UP)							\
  try									\
    {									\
      GimmickMessage(2,"SQL update: '"<<UP<<"'"<<std::endl);		\
      mDB->execDML(UP.c_str());						\
    }									\
  catch (CppSQLite3Exception& e)					\
    {									\
      GimmickError("SQLite update '"<<UP<<"' Error : "			\
		   << e.errorCode() << ":"				\
		   << e.errorMessage() );				\
    }									
  //=====================================================================


  //=====================================================================
  bool SQLiteTreeHandler::DBOpen()
  {
    GimmickMessage(1,"Opening SQLite database '"<<GetFileName()
		   <<"' ... "<<std::endl);
    // OPENING FILE
    if (!boost::filesystem::exists(GetFileName())) 
      {
	return false;
      }

    try
      {
	mDB->open(GetFileName().c_str());
	mDB->execDML("pragma synchronous=off;");
      }
    catch (CppSQLite3Exception& e)
      {
	GimmickError("Opening '"<<GetFileName()<<"' : "
		     << e.errorCode() << ":" 
		     << e.errorMessage());
	return false;
      }
    // IMPORT TREE DESCRIPTION (AND TEST DB VALIDITY)
    if (!DBImportTreeDescription())
      {
	return false;
      }

    GimmickDebugMessage(1,"Opening SQLite database '"<<GetFileName()
		   <<"' ... OK"<<std::endl);
    return true;
  }
  //=====================================================================

  //=====================================================================
  bool SQLiteTreeHandler::DBCreate()
  {
    GimmickMessage(1,"Creating SQLite database '"<<GetFileName()
		   <<"' ... "<<std::endl);

    if (boost::filesystem::exists(GetFileName())) 
      {
	GimmickError(GetFileName()<<"' : "
		     << "file already exists");
	return false;
      }
    
    // OPENING
    try
      {
	mDB->open(GetFileName().c_str());
      }
    catch (CppSQLite3Exception& e)
      {
	GimmickError(e.errorCode() << ":" 
		     << e.errorMessage() <<std::endl);
	return false;
      }
    mDB->execDML("pragma synchronous=off;");
     
    // CREATING TABLES
    
    std::string command;
    // Create LEVELS table
    command = "create table LEVELS\n";
    command += "( Name text )\n";
    UPDATEDB(command);
    int l;
    // Iterate the Levels
    for (l=0; l<GetTree().GetNumberOfLevels(); l++)
      {
	command = "INSERT INTO LEVELS (Name) VALUES ('";
	command += GetTree().GetLevelDescriptor(l).GetName();
	command += "')";
	UPDATEDB(command);
	
	// Create table of level (for level>0, i.e. not Root)
	if (l>=0)
	  {
	    command = "CREATE TABLE ";
	    command += GetTree().GetLevelDescriptor(l).GetName();
	    command += "\n(\nID INTEGER PRIMARY KEY";
	    if (l>1) 
	      {
		command += ",\nPARENT_ID int not null";	
	      }
	    SQLAppendAttributesDefinition(l,command);
	    if (l>1) 
	      {
		command += ",\nconstraint FK_PARENT foreign key (PARENT_ID) references ";
		command += GetTree().GetLevelDescriptor(l-1).GetName();
		command += "(ID) on delete restrict on update restrict";
	      }
	    command += "\n)";
	    UPDATEDB(command);
	    
		GetTree().CopyAttributeDescriptorList(l);
	    
	    // Add Attribute 'ID' to Description
	    GetTree().GetDescriptor().Add
	      (AttributeDescriptor( "ID",
				    "Database Identifier",
				    0,0,
				    AttributeDescriptor::PRIVATE
				    ),l);
	    
	    if (l>1) 
	      {
		// Add Attribute 'PARENT_ID' to Description
		GetTree().GetDescriptor().Add
		  (AttributeDescriptor( "PARENT_ID",
					"Database Parent Identifier",
					0,0,
					AttributeDescriptor::PRIVATE
					),l);
	      }
	    
	  }
	
	// Create table *_ATTRIBUTES
	
	command = "CREATE TABLE ";
	command += GetTree().GetLevelDescriptor(l).GetName();
	command += "_Attributes\n(\n";
	command += "Key text,\n";
	command += "Name text,\n";	    
	command += "DicomGroup int,\n";
	command += "DicomElement int,\n";	    
	command += "Flags int\n";	    
	command += "\n)";
	UPDATEDB(command);

	// Fill the table *_ATTRIBUTES
	LevelDescriptor::AttributeDescriptorListType::const_iterator i;
	for (i  = GetTree().GetAttributeDescriptorList(l).begin();
	     i != GetTree().GetAttributeDescriptorList(l).end();
	     ++i)
	  {
	    
	    std::stringstream insert;
	    insert << "INSERT INTO "
		   << GetTree().GetLevelDescriptor(l).GetName()
		   << "_Attributes (Key,Name,DicomGroup,DicomElement,Flags) "
		   << "VALUES ('"
		   << i->GetKey() << "','"
		   << i->GetName() << "',"
		   << i->GetGroup() << ","
		   << i->GetElement() << ","
		   << i->GetFlags() << ");";
	    UPDATEDB(insert.str());
	  }

      } // For l=0...

    // Initialize the root attributes
    GetTree().InitializeAttributeMap();
    // Insert the root in the level 0 table 
    DBInsert(GetTree().GetTree());
    
    
    GetTree().SetChildrenLoaded(true);
    GimmickMessage(1,"Creating SQLite database '"<<GetFileName()
		   <<"' ... OK"<<std::endl);
    return true;
  }
  //=====================================================================
  
  //=====================================================================
  void SQLiteTreeHandler::SQLAppendAttributesDefinition(int level,
							std::string& s)
  {
    LevelDescriptor::AttributeDescriptorListType::const_iterator i;
    for (i  = GetTree().GetAttributeDescriptorList(level).begin();
	 i != GetTree().GetAttributeDescriptorList(level).end();
	 ++i)
      {
	//	if (i->second.flags==1) continue;
	s += ",\n";
	s += i->GetKey();
	s += " text";
      }
  }
  //=====================================================================
  
  
  //=====================================================================
  bool SQLiteTreeHandler::DBImportTreeDescription()
  {
    GimmickMessage(1,"Importing tree description for database ..."
		   <<std::endl);

    // Test table 'LEVELS' existence
    if ( ! mDB->tableExists("LEVELS") )
      {
	GimmickMessage(1,"!! ERROR : Table 'LEVELS' does not exist"
		       <<std::endl);
	return false;
      }

    tree::Descriptor& desc = GetTree().GetDescriptor();
    // clears the existing one
    desc.Clear();
     
    int nblevel = 0;
    std::string query = "SELECT * FROM LEVELS";
    CppSQLite3Query q;
    QUERYDB(query,q);

    while (!q.eof())
      {
	std::string name = q.getStringField(0);
	GimmickMessage(2," * Importing level '"<<name<<"'"<<std::endl);
	desc.Add(LevelDescriptor(name));
	nblevel++;
	q.nextRow();
      }   
    
    for (int level = 0; level < nblevel; ++level )
      {
	std::string table = GetTree().GetLevelDescriptor(level).GetName();
	table += "_Attributes";
	// Test table 'LEVELS' existence
	if ( ! mDB->tableExists(table.c_str()) )
	  {
	    GimmickMessage(1,"!! ERROR : Table '"<<table<<"' does not exist"
			   <<std::endl);
	    return false;
	  }
	
	std::string query = "SELECT * FROM ";
	query += table;
	CppSQLite3Query q;
	QUERYDB(query,q);
	
	GimmickMessage(2," * Level '"
		       <<GetTree().GetLevelDescriptor(level).GetName()
		       <<"'"<<std::endl);

	// Test that ID and PARENT_ID mandatory attributes exist
	bool ID_found = false;
	bool PARENT_ID_found = false;
	if (level==0) ID_found = true;
	if (level<=1) PARENT_ID_found = true;

	while (!q.eof())
	  {
	    std::string key(q.getStringField(0));
	    std::string name(q.getStringField(1));
	    GimmickMessage(2,"  - Importing attribute '"<<key<<"' '"<<name
			   <<"'"<<std::endl);
	    desc.Add
	      (AttributeDescriptor( key, // Key
				    name, // Name
				    q.getIntField(2), // Group
				    q.getIntField(3), // Element 
				    q.getIntField(4) // Flags
				    ),level);
	    if ( key == "ID" ) 
	      {
		ID_found = true;
	      }
	    if ( key == "PARENT_ID" ) 
	      {
		PARENT_ID_found = true;
	      }
	    q.nextRow();
	  }
	
	if ( ! (ID_found || PARENT_ID_found ) )
	  {
	    GimmickMessage(1,"!! ERROR : Table '"<<table
			   <<"' does not contain mandatory attribute ID or PARENT_ID"
			   <<std::endl);
	    return false;
 
	  }
		 GetTree().CopyAttributeDescriptorList(level);
      }

	
    // Create the attributes table for Root (i.e. Tree)
    LevelDescriptor::AttributeDescriptorListType::const_iterator a;
    for (a = GetTree().GetAttributeDescriptorList(0).begin();
	 a!= GetTree().GetAttributeDescriptorList(0).end();
	 ++a)
      {

	GetTree().UnsafeSetAttribute( a->GetKey(), "" );
      }

    // Reading Root attributes
    // Query DB
    query = "SELECT * FROM ";
    query += GetTree().GetLevelDescriptor(0).GetName();
    QUERYDB(query,q);

    for (int fld = 0; fld < q.numFields(); fld++)
      {
	GetTree().UnsafeSetAttribute(q.fieldName(fld),
				     q.getStringField(fld));	    
      }

    GimmickMessage(1,"Importing tree description from database ... OK"
		   <<std::endl);
    return true;
  }
  //=====================================================================

  //========================================================================
  /// 
  void SQLformat(std::string i_str, std::string &o_str)
  {
		// quote must be doubled
		boost::algorithm::replace_all(i_str,"'","''");
		// Found strange strings which contained NULL char INSIDE string 
		int i,size=(int)i_str.size();
		for (i=0;i<size;++i) 
		{
			if (i_str[i]==0) 
			{
				i_str = i_str.substr(0,i);
				break;
			  }
		}
		o_str = i_str;
  }
  //========================================================================
  
  //=====================================================================
  void SQLiteTreeHandler::SQLAppendAttributesValues(tree::Node* n, 
						    std::string& str)
  {
    GimmickMessage(4,"SQLAppendAttributesValues"<<std::endl);
    std::string atts="";
    std::string values="";
	std::string out ="";
    tree::Node::AttributeMapType::iterator i;
    for (i =  n->GetAttributeMap().begin();
	 i != n->GetAttributeMap().end();
	 i++)
      {
	if (i->first=="ID") 
	  {
	    continue;
	  }

	atts += "'" + i->first + "'";
	SQLformat(i->second, out);
	values += "'" + out + "'"; 
	atts += ",";
	values += ",";
	GimmickMessage(4,"'"<<i->first<<"' = '"<<i->second<<"'"<<std::endl);
      }
    atts[atts.size()-1]=' ';
    values[values.size()-1]=' ';

    str = "("+atts+") VALUES ("+values+")";
    GimmickMessage(4,"Result = '"<<str<<"'"<<std::endl);
  }
  //=====================================================================

  //=====================================================================
  tree::Node* SQLiteTreeHandler::DBGetParent( const AttributeMapType& attr) 
  {
    Node* parent = GetTree().GetTree();
    bool go_down;
    do 
      {
	go_down = false;
	// Load the children of the current parent
	DBLoadChildren(parent);
	// Iterate the children 
	tree::Node::ChildrenListType::const_iterator i;
	for (i = parent->GetChildrenList().begin();
	     i!= parent->GetChildrenList().end();
	     ++i)
	  {
	    if ( (*i)->Matches( attr ) ) 
	      {
		go_down = true;
		parent = *i;
		break;
	      }
	  }	
      }
    while (go_down);
    return parent;
  }
  //=====================================================================

  //=====================================================================
  int SQLiteTreeHandler::DBLoadChildren(tree::Node* node, 
					int numberoflevels)
  {
    if (node->GetLevel()+1 >= node->GetTree()->GetNumberOfLevels() ) 
      return 0;

    GimmickMessage(2,"Loading children of '"<<node->GetLabel()
		   <<"'"<<std::endl);

    int nbloaded = 0;
    // If children loaded we do not have to do it but we need to recurse
    // in order to load the children's children if necessary, and so on...
    if (node->GetChildrenLoaded()) 
      {
	// Iterate the children 

	tree::Node::ChildrenListType::iterator i;
	for (i = node->GetChildrenList().begin();
	     i!= node->GetChildrenList().end();
	     ++i)
	  {
	    nbloaded += DBLoadChildren(*i,numberoflevels-1);
	  }
	node->SetChildrenLoaded(true);
	return nbloaded;

      }
	else
	{
    /// If children not loaded : do it and recurse

    // Query DB
    int level = node->GetLevel();
    std::string query = "SELECT * FROM ";
	
    query += GetTree().GetLevelDescriptor(level+1).GetName();
    if (level>0)
      {
	query += " WHERE PARENT_ID='" + node->GetAttribute("ID") 
	  + "'";
      }
GimmickDebugMessage(1,					"query : '"			    <<query			    		    <<std::endl);
    CppSQLite3Query q;
    QUERYDB(query,q);

    //int p=0;
    while (!q.eof())
      {

	//	std::cout << "DBLoadCh : creating node level "<<level+1<<std::endl;

	nbloaded++;
	Node* n = new Node(node);
	for (int fld = 0; fld < q.numFields(); fld++)
	  {
	    n->UnsafeSetAttribute(q.fieldName(fld),q.getStringField(fld));	    
	  }

	// recurse 
	if ( numberoflevels != 1 ) 
	  {
	    //  msw[2].Pause();
	    nbloaded += DBLoadChildren(n, numberoflevels-1);
	    //	    msw[2].Resume();
	  }
	// next entry in db
	q.nextRow();
      }

    node->SetChildrenLoaded(true);
	
    
    //    msw[2].Pause();
    return nbloaded;
	}
  }
  //=====================================================================

  //======================================================================
  void SQLiteTreeHandler::DBInsert(tree::Node* n)
  {
    GimmickMessage(2,"Inserting in DB '"<<n->GetLabel()
		   <<"'"<<std::endl);
    std::string val;
    SQLAppendAttributesValues(n,val);
    std::string insert("INSERT INTO ");
    insert += GetTree().GetLevelDescriptor(n->GetLevel()).GetName();
    insert += " " + val + ";";

    UPDATEDB(insert);
	
    // Store DB id of newly created node;
    //long lastrow = mDB->lastRowId();
    std::stringstream ri;
    ri << mDB->lastRowId();
    n->SetAttribute("ID",ri.str());
  }
  //======================================================================

  //======================================================================
  /// Graft the branch defined by the attributes to the parent
  void SQLiteTreeHandler::DBGraftToParent( tree::Node* parent, 
					    const AttributeMapType& attr)
  {
    //    std::cout <<"Grafting to parent '"<<parent->GetLabel()
    //		   <<"'"<<std::endl;

    for (int level = parent->GetLevel()+1;
	 level < GetTree().GetNumberOfLevels();
	 level++)
      {
	// Create Node
	tree::Node* child = new tree::Node(parent,attr);
	child->SetChildrenLoaded(true);
	if (level>1)
	  {
	    int nc = GetNumberOfChildren(parent)+1;
	    
	    //	std::cout<<"Number of children "<<parent->GetNumberOfChildren()<<std::endl;
	    std::stringstream out;
	    out << nc;
	    SetAttribute(parent,"NumberOfChildren",out.str());
	  }

	// Set PARENT_ID if necessary 
	if ( parent->GetLevel()>0 )
	  child->SetAttribute("PARENT_ID",parent->GetAttribute("ID"));
	
	// Insert in DB
	DBInsert(child);
	
	// Down one level
	parent = child;
	  }
  }
  //======================================================================


  //===================================================================== 
  /// Sets an attribute of a Node
  bool SQLiteTreeHandler::DBSetAttribute(tree::Node* n, 
					 const std::string& key,
					 const std::string& value)
  {
    GimmickMessage(3,"Setting Attribute of '"<<n->GetLabel()<<
		   "' "<<key<<"='"<<value<<"'"<<std::endl);

    n->SetAttribute(key,value);
    std::string sql = "UPDATE ";
    sql += GetTree().GetLevelDescriptor(n->GetLevel()).GetName();
    sql += " SET ";
    sql += key;
    sql += " = '";
    sql += convert(value);
    sql += "' WHERE ID = '";
    sql += n->GetAttribute("ID");
	sql +="'";
    //    sql += " LIMIT 1";
    UPDATEDB(sql);
	return true;
  }

  //===================================================================== 
  /// Sets an attribute of a Node
  void SQLiteTreeHandler::DBSetAttribute(const std::string& levelDescriptor, 
			      const std::string& key,
			      const std::string& value,
				  const std::string& searchParam, 
				  const std::string& searchVal)
  {

    std::string sql = "UPDATE ";
    sql += levelDescriptor;
    sql += " SET ";
    sql += key;
    sql += " = '";
    sql += value;
    sql += "' WHERE ";
	sql += searchParam;
	sql += " = '";
    sql += searchVal;
	sql += "'";
	std::cout<<sql<<std::endl;
    UPDATEDB(sql);
  }
   //=====================================================================
  void SQLiteTreeHandler::DBRecursiveRemoveNode(Node* node)
  {
       
    std::string query = "DELETE FROM ";
 	query += GetTree().GetLevelDescriptor(node->GetLevel()).GetName();
    query += " WHERE ID='"+ node->GetAttribute("ID") + "';";
	UPDATEDB(query);
	
	if(node->GetNumberOfChildren()!=0)
	{
		Node::ChildrenListType::iterator i;
		for (i  = node->GetChildrenList().begin();
		i != node->GetChildrenList().end();
		i++)
		{
		DBRecursiveRemoveNode((*i));
		}
	}
	else if(node->GetLevel()<GetTree().GetNumberOfLevels()-1)
	{
		DBRecursiveRemoveNode(node->GetLevel()+1,node->GetAttribute("ID"));
    }
  }

  //=====================================================================
  void SQLiteTreeHandler::DBRecursiveRemoveNode(int level, std::string parentId)
  {
    std::stringstream out;
	std::stringstream result;
	out<<"SELECT ID FROM "<<GetTree().GetLevelDescriptor(level).GetName()<<" WHERE PARENT_ID='"<<parentId<<"'";
		
	CppSQLite3Query q;
	QUERYDB(out.str(),q);
	
	while (!q.eof())
	  {
	    for (int fld = 0; fld < q.numFields(); fld++)
	      {
			  result<<q.getStringField(fld)<<"#";
	      }
	    q.nextRow();
	  }
	  std::string res=result.str();
	  size_t ini=0;
	  size_t fin=0;
	  while(fin<res.size()-1)
	  {
	   fin=res.find('#',ini);
	   DBDelete(GetTree().GetLevelDescriptor(level).GetName(),"ID",res.substr(ini,fin-ini));
	  if(level<GetTree().GetNumberOfLevels()-1)
	  {
		DBRecursiveRemoveNode(level+1,res.substr(ini,fin-ini));
	  } 
	   ini=fin+1;
	  }
	  
    
  }

  //=====================================================================
  void SQLiteTreeHandler::DBDelete(std::string levelDescriptor, std::string key, std::string value)
  {
       
    std::stringstream query;
	query<<"DELETE FROM "<<levelDescriptor<<" WHERE "<<key<<"='"<<value<<"';";
 
    UPDATEDB(query.str());
	GimmickDebugMessage(2," Deleting: Query: "<<query.str()<<std::endl);
  }


  //===================================================================== 
  void SQLiteTreeHandler::GetAttribute(std::string levelDescriptor,
									   std::string searchParam, 
									   std::string searchVal, 
									   std::string key, 
									   std::string& result) 
  { 
	std::stringstream out;
	std::stringstream results;
	out<<"SELECT "<<key<<" FROM "<<levelDescriptor;
	if(searchParam!="")
	{
		out<<" WHERE "<<searchParam<<"='"<<searchVal<<"'";
	}
	
	CppSQLite3Query q;
	QUERYDB(out.str(),q);
	
	
	while (!q.eof())
	  {
	    for (int fld = 0; fld < q.numFields(); fld++)
	      {
			  results<<q.getStringField(fld);
			  if(searchParam=="")
			  {
				  results<<"#";
			  }
	      }
	    q.nextRow();
	  }
	result=results.str();
      
  }
  //===================================================================== 
  unsigned int SQLiteTreeHandler::GetNumberOfChildren(tree::Node* n) 
  { 
    // Query DB
    int nb=0;
    int level = n->GetLevel();

    if(level<GetTree().GetNumberOfLevels()&& level>0)
      {
	std::string query = "SELECT NumberOfChildren FROM ";
	query += GetTree().GetLevelDescriptor(level).GetName();
	if (level>0)
	  {
	    query += " WHERE ID='" + n->GetAttribute("ID") 
	      + "'";
	  }
	CppSQLite3Query q;
	QUERYDB(query,q);
	
	
	while (!q.eof())
	  {
	    for (int fld = 0; fld < q.numFields(); fld++)
	      {
		nb=q.getIntField(fld);	
	      }
	    q.nextRow();
	  }
      }
    /*
    if(nb==0)
      { 
	nb=1;
      }
    */
    return nb; 
  }

//===================================================================== 
// get all attributes from database for a given file
  void SQLiteTreeHandler::getAllAttributes(std::string i_filename, std::map<std::string, std::string> &i_results)
  {
	  int level=GetTree().GetNumberOfLevels()-1;

	  std::string search = i_filename;
	  std::string param = "FullFileName";
	  std::string name;

	  std::string id;
	  std::set<std::string> pid;
	  std::vector<AttributeDescriptor> attr;
	  std::vector<AttributeDescriptor>::iterator it_attr;
	  std::vector<std::string> values;
	  std::vector<std::string>::iterator it_val;
	 
	  while(level>=1)
	  {
		attr = GetTree().GetAttributeDescriptorList(level,1);

		name = GetTree().GetLevelDescriptor(level).GetName();
		std::vector<std::string> values;

//EED 2018-04-13		  GetUpLevelNodeId(level, param,search,id);  
		if (level>1)
		{
			GetUpLevelNodeId(level, param,search,id);
		}
		GetAttributes(name, param,search,attr, values);
		for(it_attr = attr.begin(), it_val = values.begin(); it_attr != attr.end(); it_attr++, it_val++)
		{
			i_results[(*it_attr).GetKey()] = (*it_val).c_str();
		} // for
		search = id;
		param = "ID";
		level --;
	  } // while level
  }


//===================================================================== 
// get selected attributes from database for a given file
 	  void SQLiteTreeHandler::GetAttributes(std::string name, std::string i_id, std::string i_value, tree::LevelDescriptor::AttributeDescriptorListType i_attr, std::vector<std::string> &i_results)
	 {
		 //SELECT t1.ArtistName,CDs.Title FROM Artists t1, CDs WHERE t1.ArtistID=CDs.ArtistID    
		  std::stringstream out;
		  std::stringstream results;
		  out<<"SELECT ";
		  tree::LevelDescriptor::AttributeDescriptorListType::iterator it = i_attr.begin();
		  std::string query ="";
		  for(; it != i_attr.end(); it++)
		  {
			  query += (*it).GetKey();
			  query +=" ,";
		  }
		  query = query.substr(0, query.size()-1);
		  out << query;
		  out << "FROM "<<name;
		  out<<" WHERE "<<i_id <<"='"<<i_value<<"'";	
		  CppSQLite3Query q;
		  QUERYDB(out.str(),q);
		  while (!q.eof())
		  {
			for (int fld = 0; fld < q.numFields(); fld++)
			{
				i_results.push_back(q.getStringField(fld));
			}
			q.nextRow();
		  }
	}

   void SQLiteTreeHandler::GetUpLevelNodeId(int level, const std::string& searchParam, const std::string& searchValue, std::string& parent_id) 
   {
	  std::string sp=searchParam.c_str();
	  std::string sv=searchValue.c_str();
  	  std::stringstream out;
	  std::stringstream results;
	  out<<"SELECT PARENT_ID FROM "<<GetTree().GetLevelDescriptor(level).GetName();
	  out<<" WHERE "<<sp<<"='"<<sv<<"'";	
	  CppSQLite3Query q;
	  QUERYDB(out.str(),q);
	  while (!q.eof())
	  {
		for (int fld = 0; fld < q.numFields(); fld++)
		{
			results<<q.getStringField(fld);
		}
		q.nextRow();
	  }
	  parent_id = results.str();
  }


  //===================================================================== 
  void SQLiteTreeHandler::GetTopLevelNodeId(const std::string& searchParam, const std::string& searchValue, std::string& parent_id) 
  {
	  int level=GetTree().GetNumberOfLevels()-1;
	  std::string sp=searchParam.c_str();
	  std::string sv=searchValue.c_str();

	  while(level>1)
	  {
		GetUpLevelNodeId(level, sp, sv, parent_id);
		level--;
		sp = "ID";
		sv = parent_id;
	  }
///*
//		std::stringstream out;
//		std::stringstream results;
//		out<<"SELECT PARENT_ID FROM "<<GetTree().GetLevelDescriptor(level).GetName();
//		out<<" WHERE "<<sp<<"='"<<sv<<"'";	
//		CppSQLite3Query q;
//		QUERYDB(out.str(),q);
//		
//		
//		while (!q.eof())
//		{
//			for (int fld = 0; fld < q.numFields(); fld++)
//			{
//				results<<q.getStringField(fld);
//			}
//			q.nextRow();
//		}*/
//		level=level-1;
//		sp="ID";
//		sv=results.str();
//	  }
//	  parent_id=sv;

  }

  //=====================================================================
  void SQLiteTreeHandler::RemoveEntries(const std::string i_table, 
		const std::string i_attribute, 
		const std::string i_operand, 
		const std::string i_val)
    {
        std::stringstream query;
		query<<"DELETE  FROM "<<i_table<<" WHERE "<<i_attribute<<" "<<i_operand<<" '"<<i_val<<"'";
        UPDATEDB(query.str());
	}

	//=====================================================================
  void SQLiteTreeHandler::BeginTransaction()
    {
		std::stringstream out;
		out<<"begin transaction;";
        UPDATEDB(out.str());
	}

	//=====================================================================
  void SQLiteTreeHandler::EndTransaction()
    {
       std::stringstream out;
		out<<"commit transaction;";
        UPDATEDB(out.str());
	}
  const std::string SQLiteTreeHandler::convert(const std::string &i_word)
  {
	  std::string temp = i_word;
	  boost::algorithm::replace_all(temp,"'","''");
	  return temp.c_str();
  }

} // namespace creaImageIO