creaImageIOSQLiteTreeHandler.cpp

Go to the documentation of this file.

#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;
   }
  
  //===================================================================== 

  //===================================================================== 
  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);
  }
  //===================================================================== 
   //===================================================================== 
  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);
  }
  //===================================================================== 
  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=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
        {

    // 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());
  }
  //======================================================================

  //======================================================================
  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;
          }
  }
  //======================================================================


  //===================================================================== 
  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;
  }

  //===================================================================== 
  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;
                  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();
                  }
                  search = id;
                  param = "ID";
                  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