--- /dev/null
+#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
+