2 # ---------------------------------------------------------------------
4 # Copyright (c) CREATIS (Centre de Recherche en Acquisition et Traitement de l'Image
6 # Authors : Eduardo Davila, Frederic Cervenansky, Claire Mouton
7 # Previous Authors : Laurent Guigues, Jean-Pierre Roux
8 # CreaTools website : www.creatis.insa-lyon.fr/site/fr/creatools_accueil
10 # This software is governed by the CeCILL-B license under French law and
11 # abiding by the rules of distribution of free software. You can use,
12 # modify and/ or redistribute the software under the terms of the CeCILL-B
13 # license as circulated by CEA, CNRS and INRIA at the following URL
14 # http://www.cecill.info/licences/Licence_CeCILL-B_V1-en.html
15 # or in the file LICENSE.txt.
17 # As a counterpart to the access to the source code and rights to copy,
18 # modify and redistribute granted by the license, users are provided only
19 # with a limited warranty and the software's author, the holder of the
20 # economic rights, and the successive licensors have only limited
23 # The fact that you are presently reading this means that you have had
24 # knowledge of the CeCILL-B license and that you accept its terms.
25 # ------------------------------------------------------------------------
28 #include <creaImageIOTimestampDatabaseHandler.h>
29 #include <creaImageIOSystem.h>
31 #include "CppSQLite3.h"
34 #include <boost/filesystem.hpp>
35 #include <boost/algorithm/string/replace.hpp>
40 //=============================================================
41 TimestampDatabaseHandler::TimestampDatabaseHandler(const std::string& filename)
44 mDB = new CppSQLite3DB;
45 GimmickMessage(1,"SQLite version : "
46 <<std::string(mDB->SQLiteVersion())<< std::endl);
48 //=============================================================
50 //=============================================================
51 TimestampDatabaseHandler::~TimestampDatabaseHandler()
55 //=============================================================
56 //=====================================================================
57 bool TimestampDatabaseHandler::Open()
62 //=====================================================================
63 bool TimestampDatabaseHandler::Create()
67 //=====================================================================
70 //=====================================================================
71 bool TimestampDatabaseHandler::Close()
75 //=====================================================================
78 //=====================================================================
79 bool TimestampDatabaseHandler::Destroy()
89 //=====================================================================
90 // SQLite DB specific methods
91 //=====================================================================
92 //=====================================================================
93 #define QUERYTIMESTAMPDB(QUER,RES) \
96 GimmickMessage(2,"SQL query: '"<<QUER<<"'"<<std::endl); \
97 RES = mDB->execQuery(QUER.c_str()); \
99 catch (CppSQLite3Exception& e) \
101 GimmickError("SQLite query '"<<QUER<<"' : " \
102 << e.errorCode() << ":" \
103 << e.errorMessage() ); \
105 //=====================================================================
106 #define UPDATETIMESTAMPDB(UP) \
109 GimmickMessage(2,"SQL update: '"<<UP<<"'"<<std::endl); \
110 mDB->execDML(UP.c_str()); \
112 catch (CppSQLite3Exception& e) \
114 GimmickError("SQLite update '"<<UP<<"' Error : " \
115 << e.errorCode() << ":" \
116 << e.errorMessage() ); \
118 //=====================================================================
121 //=====================================================================
122 bool TimestampDatabaseHandler::DBOpen()
124 GimmickMessage(1,"Opening SQLite database '"<<GetFileName()
125 <<"' ... "<<std::endl);
127 if (!boost::filesystem::exists(GetFileName()))
134 mDB->open(GetFileName().c_str());
136 catch (CppSQLite3Exception& e)
138 GimmickError("Opening '"<<GetFileName()<<"' : "
139 << e.errorCode() << ":"
140 << e.errorMessage());
144 GimmickDebugMessage(1,"Opening SQLite database '"<<GetFileName()
145 <<"' ... OK"<<std::endl);
148 //=====================================================================
150 //=====================================================================
151 bool TimestampDatabaseHandler::DBCreate()
153 GimmickMessage(1,"Creating SQLite database '"<<GetFileName()
154 <<"' ... "<<std::endl);
156 if (boost::filesystem::exists(GetFileName()))
158 GimmickError(GetFileName()<<"' : "
159 << "file already exists");
166 mDB->open(GetFileName().c_str());
168 catch (CppSQLite3Exception& e)
170 GimmickError(e.errorCode() << ":"
171 << e.errorMessage() <<std::endl);
181 command = "CREATE TABLE ";
183 command += "\n(\nID INTEGER PRIMARY KEY";
184 command += ",\nPARENT_ID int not null";
185 command += ",\nPATH text";
186 command += ",\nLastModified datetext";
187 command += ",\nLastRead datetext";
188 command += ",\nTopLevelNodeId text";
189 command += ",\nReferencedDB text";
190 command += ",\nconstraint FK_PARENT foreign key (PARENT_ID) references ";
192 command += "(ID) on delete restrict on update restrict";
195 UPDATETIMESTAMPDB(command);
202 //=====================================================================
203 void TimestampDatabaseHandler::CleanPath(std::string& str) const
208 pos = str.find('\\');
211 str.replace(pos, 1, "/");
214 while ((int)pos!=-1);
216 //=====================================================================
218 bool TimestampDatabaseHandler::AddDirectory(const std::string& parent,
219 const std::string& path,
220 const time_t lastModif,
221 const time_t lastRead,
222 const std::string& refdb)
225 std::string par=parent.c_str();
226 std::string pat=path.c_str();
230 std::string pathId=IsIndexed(pat,refdb);
231 //Case: It is a root parent
232 if(parent.compare("")==0)
234 if(pathId.compare("")==0)
236 AddFile(pat,lastModif,lastRead,refdb);
241 valid=CheckTimestamp(pathId, lastModif, refdb);
246 std::string parentId=IsIndexed(par,refdb);
247 //Case: Parent is not in database
248 if(parentId.compare("")==0)
250 AddFile(par,lastModif,lastRead,refdb);
251 parentId=IsIndexed(par,refdb);
254 //Case path is not in database
255 if(pathId.compare("")==0)
257 AddFile(parentId,pat,lastModif,lastRead,refdb);
260 //Parent and path are in the database
263 SetAttribute("PARENT_ID",parentId,"ID", pathId);
264 valid=CheckTimestamp(pathId, lastModif, refdb);
271 //=====================================================================
273 void TimestampDatabaseHandler::AddFile(const std::string& path, const time_t lastModif, const time_t lastRead, const std::string& refdb)
275 std::stringstream out;
276 out<<"INSERT INTO FILES (PARENT_ID,PATH,LastModified,LastRead,ReferencedDB) VALUES(0,'"<<path<<"',";
277 out<<lastModif<<","<<lastRead<<",'"<<refdb<<"');";
278 UPDATETIMESTAMPDB(out.str());
282 //=====================================================================
284 void TimestampDatabaseHandler::AddFile(const std::string& parentId,
285 const std::string& path,
286 const time_t lastModif,
287 const time_t lastRead,
288 const std::string& refdb)
290 std::stringstream out;
291 out<<"INSERT INTO FILES (PARENT_ID,PATH,LastModified,LastRead,ReferencedDB) VALUES("<<parentId<<",'"<<path<<"',";
292 out<<lastModif<<","<<lastRead<<",'"<<refdb<<"');";
293 UPDATETIMESTAMPDB(out.str());
296 //=====================================================================
297 std::string TimestampDatabaseHandler::IsIndexed(const std::string& path, const std::string& refdb)
299 std::string pat=path.c_str();
301 std::stringstream out;
302 std::stringstream result;
303 out<<"SELECT ID FROM FILES WHERE PATH='"<<pat<<"' AND REFERENCEDDB='"<<refdb<<"';";
306 QUERYTIMESTAMPDB(out.str(),q);
311 for (int fld = 0; fld < q.numFields(); fld++)
313 result<<q.getStringField(fld);
321 //=====================================================================
322 void TimestampDatabaseHandler::SetAttribute(const std::string& attName,
323 const std::string& attValue,
324 const std::string& searchParam,
325 const std::string& searchValue)
327 std::string av=attValue.c_str();
328 std::string sv=searchValue.c_str();
332 std::string sql = "UPDATE FILES SET ";
341 UPDATETIMESTAMPDB(sql);
344 //=====================================================================
345 void TimestampDatabaseHandler::RemoveNode(const std::string& searchAtt, const tree::Node* node, const std::string& refdb)
347 int n=node->GetNumberOfChildren();
350 std::vector<tree::Node*> children=node->GetChildrenList();
351 std::vector<tree::Node*>::iterator it;
352 for(it=children.begin();it!=children.end();++it)
354 RemoveNode(searchAtt,(*it),refdb);
357 else if(node->GetLevel()==3)
359 RemoveFile(searchAtt,node->GetAttribute("FullFileName"),refdb);
363 DBRemove("TopLevelNodeId",node->GetAttribute("ID"),refdb);
368 //=====================================================================
369 void TimestampDatabaseHandler::RemoveFile(const std::string& searchAtt, const std::string& searchVal, const std::string& refdb )
372 std::stringstream result;
373 std::string sel="SELECT PARENT_ID FROM FILES WHERE "+searchAtt+"='"+searchVal+"' AND REFERENCEDDB='"+refdb+"';";
376 QUERYTIMESTAMPDB(sel,q);
380 for (int fld = 0; fld < q.numFields(); fld++)
382 result<<q.getStringField(fld);
386 DBRemove(searchAtt,searchVal,refdb);
389 sel="SELECT ID FROM FILES WHERE PARENT_ID='"+result.str()+"'";
391 QUERYTIMESTAMPDB(sel,q2);
399 if(!result.str().compare("0"))
401 RemoveFile("ID",result.str(),refdb);
405 DBRemove("ID",result.str(),refdb);
410 //=====================================================================
411 void TimestampDatabaseHandler::DBRemove(const std::string& searchAtt, const std::string& searchVal, const std::string& refdb)
414 std::string query = "DELETE FROM FILES WHERE "+searchAtt+"='"+ searchVal + "' AND REFERENCEDDB='"+refdb+"';";
415 UPDATETIMESTAMPDB(query);
418 //=====================================================================
419 bool TimestampDatabaseHandler::CheckTimestamp(const std::string pathId, const time_t lastModif, const std::string& refdb)
421 std::string sel="SELECT LastModified FROM FILES WHERE ID='"+pathId+"' AND REFERENCEDDB='"+refdb+"';";
423 QUERYTIMESTAMPDB(sel,q);
428 for (int fld = 0; fld < q.numFields(); fld++)
430 timestamp=q.getFloatField(fld);
436 std::stringstream lm;
438 double modif=atof((lm.str()).c_str());
441 SetAttribute("LastModified",lm.str(),"ID",pathId);
447 //=====================================================================
448 void TimestampDatabaseHandler::RemoveEntries(const std::string i_table,
449 const std::string i_attribute,
450 const std::string i_operand,
451 const std::string i_val)
453 std::stringstream query;
454 query<<"DELETE FROM "<<i_table<<" WHERE "<<i_attribute<<" "<<i_operand<<" '"<<i_val<<"'";
455 UPDATETIMESTAMPDB(query.str());
458 }// namespace creaImageIO