1 #include <creaImageIOTimestampDatabaseHandler.h>
2 #include <creaImageIOSystem.h>
4 #include "CppSQLite3.h"
12 #include <wx/filename.h>
17 #include <boost/filesystem.hpp>
18 #include <boost/algorithm/string/replace.hpp>
23 //=============================================================
24 TimestampDatabaseHandler::TimestampDatabaseHandler(const std::string& filename)
27 mDB = new CppSQLite3DB;
28 GimmickMessage(1,"SQLite version : "
29 <<std::string(mDB->SQLiteVersion())<< std::endl);
31 //=============================================================
33 //=============================================================
34 TimestampDatabaseHandler::~TimestampDatabaseHandler()
38 //=============================================================
39 //=====================================================================
40 bool TimestampDatabaseHandler::Open()
45 //=====================================================================
46 bool TimestampDatabaseHandler::Create()
50 //=====================================================================
53 //=====================================================================
54 bool TimestampDatabaseHandler::Close()
58 //=====================================================================
61 //=====================================================================
62 bool TimestampDatabaseHandler::Destroy()
72 //=====================================================================
73 // SQLite DB specific methods
74 //=====================================================================
75 //=====================================================================
76 #define QUERYTIMESTAMPDB(QUER,RES) \
79 GimmickMessage(2,"SQL query: '"<<QUER<<"'"<<std::endl); \
80 RES = mDB->execQuery(QUER.c_str()); \
82 catch (CppSQLite3Exception& e) \
84 GimmickError("SQLite query '"<<QUER<<"' : " \
85 << e.errorCode() << ":" \
86 << e.errorMessage() ); \
88 //=====================================================================
89 #define UPDATETIMESTAMPDB(UP) \
92 GimmickMessage(2,"SQL update: '"<<UP<<"'"<<std::endl); \
93 mDB->execDML(UP.c_str()); \
95 catch (CppSQLite3Exception& e) \
97 GimmickError("SQLite update '"<<UP<<"' Error : " \
98 << e.errorCode() << ":" \
99 << e.errorMessage() ); \
101 //=====================================================================
104 //=====================================================================
105 bool TimestampDatabaseHandler::DBOpen()
107 GimmickMessage(1,"Opening SQLite database '"<<GetFileName()
108 <<"' ... "<<std::endl);
110 if (!boost::filesystem::exists(GetFileName()))
117 mDB->open(GetFileName().c_str());
119 catch (CppSQLite3Exception& e)
121 GimmickError("Opening '"<<GetFileName()<<"' : "
122 << e.errorCode() << ":"
123 << e.errorMessage());
127 GimmickDebugMessage(1,"Opening SQLite database '"<<GetFileName()
128 <<"' ... OK"<<std::endl);
131 //=====================================================================
133 //=====================================================================
134 bool TimestampDatabaseHandler::DBCreate()
136 GimmickMessage(1,"Creating SQLite database '"<<GetFileName()
137 <<"' ... "<<std::endl);
139 if (boost::filesystem::exists(GetFileName()))
141 GimmickError(GetFileName()<<"' : "
142 << "file already exists");
149 mDB->open(GetFileName().c_str());
151 catch (CppSQLite3Exception& e)
153 GimmickError(e.errorCode() << ":"
154 << e.errorMessage() <<std::endl);
164 command = "CREATE TABLE ";
166 command += "\n(\nID INTEGER PRIMARY KEY";
167 command += ",\nPARENT_ID int not null";
168 command += ",\nPATH text";
169 command += ",\nLastModified datetext";
170 command += ",\nLastRead datetext";
171 command += ",\nTopLevelNodeId text";
172 command += ",\nReferencedDB text";
173 command += ",\nconstraint FK_PARENT foreign key (PARENT_ID) references ";
175 command += "(ID) on delete restrict on update restrict";
178 UPDATETIMESTAMPDB(command);
185 //=====================================================================
186 void TimestampDatabaseHandler::CleanPath(std::string& str) const
191 pos = str.find('\\');
194 str.replace(pos, 1, "/");
197 while ((int)pos!=-1);
199 //=====================================================================
201 bool TimestampDatabaseHandler::AddDirectory(const std::string& parent,
202 const std::string& path,
203 const time_t lastModif,
204 const time_t lastRead,
205 const std::string& refdb)
208 std::string par=parent.c_str();
209 std::string pat=path.c_str();
213 std::string pathId=IsIndexed(pat,refdb);
214 //Case: It is a root parent
215 if(parent.compare("")==0)
217 if(pathId.compare("")==0)
219 AddFile(pat,lastModif,lastRead,refdb);
224 valid=CheckTimestamp(pathId, lastModif, refdb);
229 std::string parentId=IsIndexed(par,refdb);
230 //Case: Parent is not in database
231 if(parentId.compare("")==0)
233 AddFile(par,lastModif,lastRead,refdb);
234 parentId=IsIndexed(par,refdb);
237 //Case path is not in database
238 if(pathId.compare("")==0)
240 AddFile(parentId,pat,lastModif,lastRead,refdb);
243 //Parent and path are in the database
246 SetAttribute("PARENT_ID",parentId,"ID", pathId);
247 valid=CheckTimestamp(pathId, lastModif, refdb);
254 //=====================================================================
256 void TimestampDatabaseHandler::AddFile(const std::string& path, const time_t lastModif, const time_t lastRead, const std::string& refdb)
258 std::stringstream out;
259 out<<"INSERT INTO FILES (PARENT_ID,PATH,LastModified,LastRead,ReferencedDB) VALUES(0,'"<<path<<"',";
260 out<<lastModif<<","<<lastRead<<",'"<<refdb<<"');";
261 UPDATETIMESTAMPDB(out.str());
265 //=====================================================================
267 void TimestampDatabaseHandler::AddFile(const std::string& parentId,
268 const std::string& path,
269 const time_t lastModif,
270 const time_t lastRead,
271 const std::string& refdb)
273 std::stringstream out;
274 out<<"INSERT INTO FILES (PARENT_ID,PATH,LastModified,LastRead,ReferencedDB) VALUES("<<parentId<<",'"<<path<<"',";
275 out<<lastModif<<","<<lastRead<<",'"<<refdb<<"');";
276 UPDATETIMESTAMPDB(out.str());
279 //=====================================================================
280 std::string TimestampDatabaseHandler::IsIndexed(const std::string& path, const std::string& refdb)
282 std::string pat=path.c_str();
284 std::stringstream out;
285 std::stringstream result;
286 out<<"SELECT ID FROM FILES WHERE PATH='"<<pat<<"' AND REFERENCEDDB='"<<refdb<<"';";
289 QUERYTIMESTAMPDB(out.str(),q);
294 for (int fld = 0; fld < q.numFields(); fld++)
296 result<<q.getStringField(fld);
304 //=====================================================================
305 void TimestampDatabaseHandler::SetAttribute(const std::string& attName,
306 const std::string& attValue,
307 const std::string& searchParam,
308 const std::string& searchValue)
310 std::string av=attValue.c_str();
311 std::string sv=searchValue.c_str();
315 std::string sql = "UPDATE FILES SET ";
324 UPDATETIMESTAMPDB(sql);
327 //=====================================================================
328 void TimestampDatabaseHandler::RemoveNode(const std::string& searchAtt, const tree::Node* node, const std::string& refdb)
330 int n=node->GetNumberOfChildren();
333 std::vector<tree::Node*> children=node->GetChildrenList();
334 std::vector<tree::Node*>::iterator it;
335 for(it=children.begin();it!=children.end();++it)
337 RemoveNode(searchAtt,(*it),refdb);
340 else if(node->GetLevel()==3)
342 RemoveFile(searchAtt,node->GetAttribute("FullFileName"),refdb);
346 DBRemove("TopLevelNodeId",node->GetAttribute("ID"),refdb);
351 //=====================================================================
352 void TimestampDatabaseHandler::RemoveFile(const std::string& searchAtt, const std::string& searchVal, const std::string& refdb )
355 std::stringstream result;
356 std::string sel="SELECT PARENT_ID FROM FILES WHERE "+searchAtt+"='"+searchVal+"' AND REFERENCEDDB='"+refdb+"';";
359 QUERYTIMESTAMPDB(sel,q);
363 for (int fld = 0; fld < q.numFields(); fld++)
365 result<<q.getStringField(fld);
369 DBRemove(searchAtt,searchVal,refdb);
372 sel="SELECT ID FROM FILES WHERE PARENT_ID='"+result.str()+"'";
374 QUERYTIMESTAMPDB(sel,q2);
382 if(!result.str().compare("0"))
384 RemoveFile("ID",result.str(),refdb);
388 DBRemove("ID",result.str(),refdb);
393 //=====================================================================
394 void TimestampDatabaseHandler::DBRemove(const std::string& searchAtt, const std::string& searchVal, const std::string& refdb)
397 std::string query = "DELETE FROM FILES WHERE "+searchAtt+"='"+ searchVal + "' AND REFERENCEDDB='"+refdb+"';";
398 UPDATETIMESTAMPDB(query);
401 //=====================================================================
402 bool TimestampDatabaseHandler::CheckTimestamp(const std::string pathId, const time_t lastModif, const std::string& refdb)
404 std::string sel="SELECT LastModified FROM FILES WHERE ID='"+pathId+"' AND REFERENCEDDB='"+refdb+"';";
406 QUERYTIMESTAMPDB(sel,q);
411 for (int fld = 0; fld < q.numFields(); fld++)
413 timestamp=q.getFloatField(fld);
419 std::stringstream lm;
421 double modif=atof((lm.str()).c_str());
424 SetAttribute("LastModified",lm.str(),"ID",pathId);
430 //=====================================================================
431 void TimestampDatabaseHandler::RemoveEntries(const std::string i_table,
432 const std::string i_attribute,
433 const std::string i_operand,
434 const std::string i_val)
436 std::stringstream query;
437 query<<"DELETE FROM "<<i_table<<" WHERE "<<i_attribute<<" "<<i_operand<<" '"<<i_val<<"'";
438 UPDATETIMESTAMPDB(query.str());
441 }// namespace creaImageIO