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 += ",\nconstraint FK_PARENT foreign key (PARENT_ID) references ";
174 command += "(ID) on delete restrict on update restrict";
177 UPDATETIMESTAMPDB(command);
184 //=====================================================================
185 void TimestampDatabaseHandler::CleanName(std::string& str) const
190 pos = str.find('\\');
193 str.replace(pos, 1, "/");
200 //=====================================================================
202 bool TimestampDatabaseHandler::AddDirectory(const std::string& parent,
203 const std::string& path,
204 const time_t lastModif,
205 const time_t lastRead)
208 std::string par=parent.c_str();
209 std::string pat=path.c_str();
213 std::string pathId=IsIndexed(pat);
214 //Case: It is a root parent
215 if(parent.compare("")==0)
217 if(pathId.compare("")==0)
219 AddFile(pat,lastModif,lastRead);
224 valid=CheckTimestamp(pathId, lastModif);
229 std::string parentId=IsIndexed(par);
230 //Case: Parent is not in database
231 if(parentId.compare("")==0)
233 AddFile(par,lastModif,lastRead);
234 parentId=IsIndexed(par);
237 //Case path is not in database
238 if(pathId.compare("")==0)
240 AddFile(parentId,pat,lastModif,lastRead);
243 //Parent and path are in the database
246 SetAttribute("PARENT_ID",parentId,"ID", pathId);
247 valid=CheckTimestamp(pathId, lastModif);
254 //=====================================================================
256 void TimestampDatabaseHandler::AddFile(const std::string& path, const time_t lastModif, const time_t lastRead)
258 std::stringstream out;
259 out<<"INSERT INTO FILES (PARENT_ID,PATH,LastModified,LastRead) VALUES(0,'"<<path<<"',";
260 out<<lastModif<<","<<lastRead<<");";
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)
272 std::stringstream out;
273 out<<"INSERT INTO FILES (PARENT_ID,PATH,LastModified,LastRead) VALUES("<<parentId<<",'"<<path<<"',";
274 out<<lastModif<<","<<lastRead<<");";
275 UPDATETIMESTAMPDB(out.str());
278 //=====================================================================
279 std::string TimestampDatabaseHandler::IsIndexed(const std::string& path)
281 std::stringstream out;
282 std::stringstream result;
283 out<<"SELECT ID FROM FILES WHERE PATH='"<<path<<"'";
286 QUERYTIMESTAMPDB(out.str(),q);
291 for (int fld = 0; fld < q.numFields(); fld++)
293 result<<q.getStringField(fld);
301 //=====================================================================
302 void TimestampDatabaseHandler::SetAttribute(const std::string& attName,
303 const std::string& attValue,
304 const std::string& searchParam,
305 const std::string& searchValue)
307 std::string av=attValue.c_str();
308 std::string sv=searchValue.c_str();
312 std::string sql = "UPDATE FILES SET ";
321 UPDATETIMESTAMPDB(sql);
324 //=====================================================================
325 void TimestampDatabaseHandler::RemoveNode(const std::string& searchAtt, const tree::Node* node)
327 int n=node->GetNumberOfChildren();
330 std::vector<tree::Node*> children=node->GetChildrenList();
331 std::vector<tree::Node*>::iterator it;
332 for(it=children.begin();it!=children.end();++it)
334 RemoveNode(searchAtt,(*it));
337 else if(node->GetLevel()==3)
339 RemoveFile(searchAtt,node->GetAttribute("FullFileName"));
343 DBRemove("TopLevelNodeId",node->GetAttribute("ID"));
348 //=====================================================================
349 void TimestampDatabaseHandler::RemoveFile(const std::string& searchAtt, const std::string& searchVal)
352 std::stringstream result;
353 std::string sel="SELECT PARENT_ID FROM FILES WHERE "+searchAtt+"='"+searchVal+"'";
356 QUERYTIMESTAMPDB(sel,q);
360 for (int fld = 0; fld < q.numFields(); fld++)
362 result<<q.getStringField(fld);
366 DBRemove(searchAtt,searchVal);
369 sel="SELECT ID FROM FILES WHERE PARENT_ID='"+result.str()+"'";
371 QUERYTIMESTAMPDB(sel,q2);
379 if(!result.str().compare("0"))
381 RemoveFile("ID",result.str());
385 DBRemove("ID",result.str());
390 //=====================================================================
391 void TimestampDatabaseHandler::DBRemove(const std::string& searchAtt, const std::string& searchVal)
394 std::string query = "DELETE FROM FILES WHERE "+searchAtt+"='"+ searchVal + "';";
395 UPDATETIMESTAMPDB(query);
398 //=====================================================================
399 bool TimestampDatabaseHandler::CheckTimestamp(const std::string pathId, const time_t lastModif)
401 std::string sel="SELECT LastModified FROM FILES WHERE ID='"+pathId+"';";
403 QUERYTIMESTAMPDB(sel,q);
408 for (int fld = 0; fld < q.numFields(); fld++)
410 timestamp=q.getFloatField(fld);
416 std::stringstream lm;
418 double modif=atof((lm.str()).c_str());
421 SetAttribute("LastModified",lm.str(),"ID",pathId);
427 }// namespace creaImageIO