creaImageIOTimestampDatabaseHandler.cpp

Go to the documentation of this file.

#include <creaImageIOTimestampDatabaseHandler.h>
#include <creaImageIOSystem.h>

#include "CppSQLite3.h"

#include <sys/stat.h>

#include <deque>

#include <boost/filesystem.hpp>
#include <boost/algorithm/string/replace.hpp>

namespace creaImageIO
{
        using namespace tree;
  //=============================================================
  TimestampDatabaseHandler::TimestampDatabaseHandler(const std::string& filename)
    : mFileName(filename)
  {
    mDB = new CppSQLite3DB;
    GimmickMessage(1,"SQLite version : "
                   <<std::string(mDB->SQLiteVersion())<< std::endl);
  }
  //=============================================================

  //=============================================================
  TimestampDatabaseHandler::~TimestampDatabaseHandler()
  {
    delete mDB;
  }
  //=============================================================
  //=====================================================================
  bool TimestampDatabaseHandler::Open()
  {
    return DBOpen();
  }

  //=====================================================================
  bool TimestampDatabaseHandler::Create()
  {
    return DBCreate();
  }
  //=====================================================================


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


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






  //=====================================================================
  // SQLite DB specific methods
  //=====================================================================
  //=====================================================================
#define QUERYTIMESTAMPDB(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 UPDATETIMESTAMPDB(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 TimestampDatabaseHandler::DBOpen()
  {
    GimmickMessage(1,"Opening SQLite database '"<<GetFileName()
                   <<"' ... "<<std::endl);
    // OPENING FILE
    if (!boost::filesystem::exists(GetFileName())) 
      {
        return false;
      }

    try
      {
        mDB->open(GetFileName().c_str());
      }
    catch (CppSQLite3Exception& e)
      {
        GimmickError("Opening '"<<GetFileName()<<"' : "
                     << e.errorCode() << ":" 
                     << e.errorMessage());
        return false;
      }

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

  //=====================================================================
  bool TimestampDatabaseHandler::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;
      }
    
     
    // CREATING TABLES
    
    std::string command;
  
    
            command = "CREATE TABLE ";
            command += "FILES";
            command += "\n(\nID INTEGER PRIMARY KEY";
                command += ",\nPARENT_ID int not null"; 
            command += ",\nPATH text";
                command += ",\nLastModified datetext";
                command += ",\nLastRead datetext";
                command += ",\nTopLevelNodeId text";
                command += ",\nReferencedDB text";
                command += ",\nconstraint FK_PARENT foreign key (PARENT_ID) references ";
                command += "FILES";
                command += "(ID) on delete restrict on update restrict";
              
            command += "\n)";
            UPDATETIMESTAMPDB(command);
            
    return true;
  }



  //=====================================================================
  void TimestampDatabaseHandler::CleanPath(std::string& str) const
  {
         size_t pos;
         do
     {
         pos = str.find('\\');
         if ((int)pos!=-1)  
                 {
                         str.replace(pos, 1, "/");
                 }
     }
     while ((int)pos!=-1);
  }
  //=====================================================================

  bool TimestampDatabaseHandler::AddDirectory(const std::string& parent,
                                                                                   const std::string& path, 
                                                                                   const time_t lastModif, 
                                                                                   const time_t lastRead,
                                                                                   const std::string& refdb)
  {
         bool valid=false;
         std::string par=parent.c_str();
         std::string pat=path.c_str();
         CleanPath(par);
         CleanPath(pat);

         std::string pathId=IsIndexed(pat,refdb);
         //Case: It is a root parent
         if(parent.compare("")==0)
         {
                 if(pathId.compare("")==0)
                 {
                        AddFile(pat,lastModif,lastRead,refdb);
                        valid=true;
                 }
                 else
                 {
                         valid=CheckTimestamp(pathId, lastModif, refdb);
                 }
         }
         else 
         {
                 std::string parentId=IsIndexed(par,refdb);
                 //Case: Parent is not in database
                 if(parentId.compare("")==0)
                {
                        AddFile(par,lastModif,lastRead,refdb);
                        parentId=IsIndexed(par,refdb);
                }

                //Case path is not in database
                if(pathId.compare("")==0)
                {
                    AddFile(parentId,pat,lastModif,lastRead,refdb);
                        valid=true;
                }
                //Parent and path are in the database
                else
                {
                        SetAttribute("PARENT_ID",parentId,"ID", pathId);
                        valid=CheckTimestamp(pathId, lastModif, refdb);
                }
         }
         return valid;
        
  }

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

  void TimestampDatabaseHandler::AddFile(const std::string& path, const time_t lastModif, const time_t lastRead,  const std::string& refdb)
  {
        std::stringstream out;
        out<<"INSERT INTO FILES (PARENT_ID,PATH,LastModified,LastRead,ReferencedDB) VALUES(0,'"<<path<<"',";
        out<<lastModif<<","<<lastRead<<",'"<<refdb<<"');";
    UPDATETIMESTAMPDB(out.str());
        
  }

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

  void TimestampDatabaseHandler::AddFile(const std::string& parentId, 
                                                                                 const std::string& path, 
                                                                                 const time_t lastModif, 
                                                                                 const time_t lastRead,  
                                                                                 const std::string& refdb)
  {
        std::stringstream out;
        out<<"INSERT INTO FILES (PARENT_ID,PATH,LastModified,LastRead,ReferencedDB) VALUES("<<parentId<<",'"<<path<<"',";
        out<<lastModif<<","<<lastRead<<",'"<<refdb<<"');";
    UPDATETIMESTAMPDB(out.str());
  }

  //=====================================================================
  std::string TimestampDatabaseHandler::IsIndexed(const std::string& path, const std::string& refdb)
  {
        std::string pat=path.c_str();
        CleanPath(pat);
        std::stringstream out;
        std::stringstream result;
        out<<"SELECT ID FROM FILES WHERE PATH='"<<pat<<"' AND REFERENCEDDB='"<<refdb<<"';";
                
        CppSQLite3Query q;
        QUERYTIMESTAMPDB(out.str(),q);
        
        
        while (!q.eof())
          {
            for (int fld = 0; fld < q.numFields(); fld++)
              {
                          result<<q.getStringField(fld);
              }
            q.nextRow();
          }

          return result.str();
  }

  //=====================================================================
  void TimestampDatabaseHandler::SetAttribute(const std::string& attName, 
                                                                                        const std::string& attValue,
                                                                                        const std::string& searchParam,
                                                                                        const std::string& searchValue)
  {
        std::string av=attValue.c_str();
        std::string sv=searchValue.c_str();
        CleanPath(av);
        CleanPath(sv);

        std::string sql = "UPDATE FILES SET ";
    sql += attName;
    sql += " = '";
    sql += av;
    sql += "' WHERE ";
        sql += searchParam;
        sql += " = '";
    sql += sv;
        sql += "'";
    UPDATETIMESTAMPDB(sql);
  }
 
  //=====================================================================
  void TimestampDatabaseHandler::RemoveNode(const std::string& searchAtt, const tree::Node* node, const std::string& refdb)
  {
          int n=node->GetNumberOfChildren();
          if(n>0)
          {
                  std::vector<tree::Node*> children=node->GetChildrenList();
                  std::vector<tree::Node*>::iterator it;
                  for(it=children.begin();it!=children.end();++it)
                  {
                          RemoveNode(searchAtt,(*it),refdb);
                  }
          }
          else if(node->GetLevel()==3)
          {
                  RemoveFile(searchAtt,node->GetAttribute("FullFileName"),refdb);
          }
          else
          {
                  DBRemove("TopLevelNodeId",node->GetAttribute("ID"),refdb);
          }


  }
  //=====================================================================
  void TimestampDatabaseHandler::RemoveFile(const std::string& searchAtt, const std::string& searchVal, const std::string& refdb )
  {
          
          std::stringstream result;
          std::string sel="SELECT PARENT_ID FROM FILES WHERE "+searchAtt+"='"+searchVal+"' AND REFERENCEDDB='"+refdb+"';";
                
          CppSQLite3Query q;
          QUERYTIMESTAMPDB(sel,q);
        
          while (!q.eof())
          {
            for (int fld = 0; fld < q.numFields(); fld++)
              {
                          result<<q.getStringField(fld);
              }
            q.nextRow();
          }
          DBRemove(searchAtt,searchVal,refdb);
          
                  int nChildren=0;
                  sel="SELECT ID FROM FILES WHERE PARENT_ID='"+result.str()+"'";
                  CppSQLite3Query q2;
                  QUERYTIMESTAMPDB(sel,q2);
                  while (!q2.eof())
                        {
                                nChildren++;
                                q2.nextRow();
                        }
                        if(nChildren<1)
                        {
                                if(!result.str().compare("0"))
                                {
                                RemoveFile("ID",result.str(),refdb);
                                }
                                else
                                {
                                DBRemove("ID",result.str(),refdb);
                                }
                        }
  }

  //=====================================================================
  void TimestampDatabaseHandler::DBRemove(const std::string& searchAtt, const std::string& searchVal, const std::string& refdb)
  {
       
    std::string query = "DELETE FROM FILES WHERE "+searchAtt+"='"+ searchVal + "' AND REFERENCEDDB='"+refdb+"';";
    UPDATETIMESTAMPDB(query);
  }

   //=====================================================================
  bool TimestampDatabaseHandler::CheckTimestamp(const std::string pathId, const time_t lastModif, const std::string& refdb)
  {
        std::string sel="SELECT LastModified FROM FILES WHERE ID='"+pathId+"' AND REFERENCEDDB='"+refdb+"';";
        CppSQLite3Query q;
        QUERYTIMESTAMPDB(sel,q);
        double timestamp;
        
        while (!q.eof())
          {
            for (int fld = 0; fld < q.numFields(); fld++)
              {
                          timestamp=q.getFloatField(fld);
              }
            q.nextRow();
          }

          
          std::stringstream lm;
          lm<<lastModif;
          double modif=atof((lm.str()).c_str());
          if(timestamp<modif)
          {
                  SetAttribute("LastModified",lm.str(),"ID",pathId);
                  return true;
          }
          return false;  
  }

  //=====================================================================
  void TimestampDatabaseHandler::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<<"'";
        UPDATETIMESTAMPDB(query.str());
        }

}// namespace creaImageIO