creaImageIO_lib
creaImageIOTimestampDatabaseHandler.cpp
Go to the documentation of this file.
1 /*
2 # ---------------------------------------------------------------------
3 #
4 # Copyright (c) CREATIS (Centre de Recherche en Acquisition et Traitement de l'Image
5 # pour la Santé)
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
9 #
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.
16 #
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
21 # liability.
22 #
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 # ------------------------------------------------------------------------
26 */
27 
28 #include <creaImageIOTimestampDatabaseHandler.h>
29 #include <creaImageIOSystem.h>
30 
31 #include "CppSQLite3.h"
32 #include <sys/stat.h>
33 #include <deque>
34 #include <boost/filesystem.hpp>
35 #include <boost/algorithm/string/replace.hpp>
36 
37 namespace creaImageIO
38 {
39  using namespace tree;
40  //=============================================================
41  TimestampDatabaseHandler::TimestampDatabaseHandler(const std::string& filename)
42  : mFileName(filename)
43  {
44  mDB = new CppSQLite3DB;
45  GimmickMessage(1,"SQLite version : "
46  <<std::string(mDB->SQLiteVersion())<< std::endl);
47  }
48  //=============================================================
49 
50  //=============================================================
51  TimestampDatabaseHandler::~TimestampDatabaseHandler()
52  {
53  delete mDB;
54  }
55  //=============================================================
56  //=====================================================================
57  bool TimestampDatabaseHandler::Open()
58  {
59  return DBOpen();
60  }
61 
62  //=====================================================================
63  bool TimestampDatabaseHandler::Create()
64  {
65  return DBCreate();
66  }
67  //=====================================================================
68 
69 
70  //=====================================================================
71  bool TimestampDatabaseHandler::Close()
72  {
73  return true;
74  }
75  //=====================================================================
76 
77 
78  //=====================================================================
79  bool TimestampDatabaseHandler::Destroy()
80  {
81  return false;
82  }
83 
84 
85 
86 
87 
88 
89  //=====================================================================
90  // SQLite DB specific methods
91  //=====================================================================
92  //=====================================================================
93 #define QUERYTIMESTAMPDB(QUER,RES) \
94  try \
95  { \
96  GimmickMessage(2,"SQL query: '"<<QUER<<"'"<<std::endl); \
97  RES = mDB->execQuery(QUER.c_str()); \
98  } \
99  catch (CppSQLite3Exception& e) \
100  { \
101  GimmickError("SQLite query '"<<QUER<<"' : " \
102  << e.errorCode() << ":" \
103  << e.errorMessage() ); \
104  } \
105  //=====================================================================
106 #define UPDATETIMESTAMPDB(UP) \
107  try \
108  { \
109  GimmickMessage(2,"SQL update: '"<<UP<<"'"<<std::endl); \
110  mDB->execDML(UP.c_str()); \
111  } \
112  catch (CppSQLite3Exception& e) \
113  { \
114  GimmickError("SQLite update '"<<UP<<"' Error : " \
115  << e.errorCode() << ":" \
116  << e.errorMessage() ); \
117  }
118  //=====================================================================
119 
120 
121  //=====================================================================
122  bool TimestampDatabaseHandler::DBOpen()
123  {
124  GimmickMessage(1,"Opening SQLite database '"<<GetFileName()
125  <<"' ... "<<std::endl);
126  // OPENING FILE
127  if (!boost::filesystem::exists(GetFileName()))
128  {
129  return false;
130  }
131 
132  try
133  {
134  mDB->open(GetFileName().c_str());
135  }
136  catch (CppSQLite3Exception& e)
137  {
138  GimmickError("Opening '"<<GetFileName()<<"' : "
139  << e.errorCode() << ":"
140  << e.errorMessage());
141  return false;
142  }
143 
144  GimmickDebugMessage(1,"Opening SQLite database '"<<GetFileName()
145  <<"' ... OK"<<std::endl);
146  return true;
147  }
148  //=====================================================================
149 
150  //=====================================================================
151  bool TimestampDatabaseHandler::DBCreate()
152  {
153  GimmickMessage(1,"Creating SQLite database '"<<GetFileName()
154  <<"' ... "<<std::endl);
155 
156  if (boost::filesystem::exists(GetFileName()))
157  {
158  GimmickError(GetFileName()<<"' : "
159  << "file already exists");
160  return false;
161  }
162 
163  // OPENING
164  try
165  {
166  mDB->open(GetFileName().c_str());
167  }
168  catch (CppSQLite3Exception& e)
169  {
170  GimmickError(e.errorCode() << ":"
171  << e.errorMessage() <<std::endl);
172  return false;
173  }
174 
175 
176  // CREATING TABLES
177 
178  std::string command;
179 
180 
181  command = "CREATE TABLE ";
182  command += "FILES";
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 ";
191  command += "FILES";
192  command += "(ID) on delete restrict on update restrict";
193 
194  command += "\n)";
195  UPDATETIMESTAMPDB(command);
196 
197  return true;
198  }
199 
200 
201 
202  //=====================================================================
203  void TimestampDatabaseHandler::CleanPath(std::string& str) const
204  {
205  size_t pos;
206  do
207  {
208  pos = str.find('\\');
209  if ((int)pos!=-1)
210  {
211  str.replace(pos, 1, "/");
212  }
213  }
214  while ((int)pos!=-1);
215  }
216  //=====================================================================
217 
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)
223  {
224  bool valid=false;
225  std::string par=parent.c_str();
226  std::string pat=path.c_str();
227  CleanPath(par);
228  CleanPath(pat);
229 
230  std::string pathId=IsIndexed(pat,refdb);
231  //Case: It is a root parent
232  if(parent.compare("")==0)
233  {
234  if(pathId.compare("")==0)
235  {
236  AddFile(pat,lastModif,lastRead,refdb);
237  valid=true;
238  }
239  else
240  {
241  valid=CheckTimestamp(pathId, lastModif, refdb);
242  }
243  }
244  else
245  {
246  std::string parentId=IsIndexed(par,refdb);
247  //Case: Parent is not in database
248  if(parentId.compare("")==0)
249  {
250  AddFile(par,lastModif,lastRead,refdb);
251  parentId=IsIndexed(par,refdb);
252  }
253 
254  //Case path is not in database
255  if(pathId.compare("")==0)
256  {
257  AddFile(parentId,pat,lastModif,lastRead,refdb);
258  valid=true;
259  }
260  //Parent and path are in the database
261  else
262  {
263  SetAttribute("PARENT_ID",parentId,"ID", pathId);
264  valid=CheckTimestamp(pathId, lastModif, refdb);
265  }
266  }
267  return valid;
268 
269  }
270 
271  //=====================================================================
272 
273  void TimestampDatabaseHandler::AddFile(const std::string& path, const time_t lastModif, const time_t lastRead, const std::string& refdb)
274  {
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());
279 
280  }
281 
282  //=====================================================================
283 
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)
289  {
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());
294  }
295 
296  //=====================================================================
297  std::string TimestampDatabaseHandler::IsIndexed(const std::string& path, const std::string& refdb)
298  {
299  std::string pat=path.c_str();
300  CleanPath(pat);
301  std::stringstream out;
302  std::stringstream result;
303  out<<"SELECT ID FROM FILES WHERE PATH='"<<pat<<"' AND REFERENCEDDB='"<<refdb<<"';";
304 
305  CppSQLite3Query q;
306  QUERYTIMESTAMPDB(out.str(),q);
307 
308 
309  while (!q.eof())
310  {
311  for (int fld = 0; fld < q.numFields(); fld++)
312  {
313  result<<q.getStringField(fld);
314  }
315  q.nextRow();
316  }
317 
318  return result.str();
319  }
320 
321  //=====================================================================
322  void TimestampDatabaseHandler::SetAttribute(const std::string& attName,
323  const std::string& attValue,
324  const std::string& searchParam,
325  const std::string& searchValue)
326  {
327  std::string av=attValue.c_str();
328  std::string sv=searchValue.c_str();
329  CleanPath(av);
330  CleanPath(sv);
331 
332  std::string sql = "UPDATE FILES SET ";
333  sql += attName;
334  sql += " = '";
335  sql += av;
336  sql += "' WHERE ";
337  sql += searchParam;
338  sql += " = '";
339  sql += sv;
340  sql += "'";
341  UPDATETIMESTAMPDB(sql);
342  }
343 
344  //=====================================================================
345  void TimestampDatabaseHandler::RemoveNode(const std::string& searchAtt, const tree::Node* node, const std::string& refdb)
346  {
347  int n=node->GetNumberOfChildren();
348  if(n>0)
349  {
350  std::vector<tree::Node*> children=node->GetChildrenList();
351  std::vector<tree::Node*>::iterator it;
352  for(it=children.begin();it!=children.end();++it)
353  {
354  RemoveNode(searchAtt,(*it),refdb);
355  }
356  }
357  else if(node->GetLevel()==3)
358  {
359  RemoveFile(searchAtt,node->GetAttribute("FullFileName"),refdb);
360  }
361  else
362  {
363  DBRemove("TopLevelNodeId",node->GetAttribute("ID"),refdb);
364  }
365 
366 
367  }
368  //=====================================================================
369  void TimestampDatabaseHandler::RemoveFile(const std::string& searchAtt, const std::string& searchVal, const std::string& refdb )
370  {
371 
372  std::stringstream result;
373  std::string sel="SELECT PARENT_ID FROM FILES WHERE "+searchAtt+"='"+searchVal+"' AND REFERENCEDDB='"+refdb+"';";
374 
375  CppSQLite3Query q;
376  QUERYTIMESTAMPDB(sel,q);
377 
378  while (!q.eof())
379  {
380  for (int fld = 0; fld < q.numFields(); fld++)
381  {
382  result<<q.getStringField(fld);
383  }
384  q.nextRow();
385  }
386  DBRemove(searchAtt,searchVal,refdb);
387 
388  int nChildren=0;
389  sel="SELECT ID FROM FILES WHERE PARENT_ID='"+result.str()+"'";
390  CppSQLite3Query q2;
391  QUERYTIMESTAMPDB(sel,q2);
392  while (!q2.eof())
393  {
394  nChildren++;
395  q2.nextRow();
396  }
397  if(nChildren<1)
398  {
399  if(!result.str().compare("0"))
400  {
401  RemoveFile("ID",result.str(),refdb);
402  }
403  else
404  {
405  DBRemove("ID",result.str(),refdb);
406  }
407  }
408  }
409 
410  //=====================================================================
411  void TimestampDatabaseHandler::DBRemove(const std::string& searchAtt, const std::string& searchVal, const std::string& refdb)
412  {
413 
414  std::string query = "DELETE FROM FILES WHERE "+searchAtt+"='"+ searchVal + "' AND REFERENCEDDB='"+refdb+"';";
415  UPDATETIMESTAMPDB(query);
416  }
417 
418  //=====================================================================
419  bool TimestampDatabaseHandler::CheckTimestamp(const std::string pathId, const time_t lastModif, const std::string& refdb)
420  {
421  std::string sel="SELECT LastModified FROM FILES WHERE ID='"+pathId+"' AND REFERENCEDDB='"+refdb+"';";
422  CppSQLite3Query q;
423  QUERYTIMESTAMPDB(sel,q);
424  double timestamp;
425 
426  while (!q.eof())
427  {
428  for (int fld = 0; fld < q.numFields(); fld++)
429  {
430  timestamp=q.getFloatField(fld);
431  }
432  q.nextRow();
433  }
434 
435 
436  std::stringstream lm;
437  lm<<lastModif;
438  double modif=atof((lm.str()).c_str());
439  if(timestamp<modif)
440  {
441  SetAttribute("LastModified",lm.str(),"ID",pathId);
442  return true;
443  }
444  return false;
445  }
446 
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)
452  {
453  std::stringstream query;
454  query<<"DELETE FROM "<<i_table<<" WHERE "<<i_attribute<<" "<<i_operand<<" '"<<i_val<<"'";
455  UPDATETIMESTAMPDB(query.str());
456  }
457 
458 }// namespace creaImageIO
459