creaImageIO_lib
creaImageIOSQLiteTreeHandler.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 
29 #include <creaImageIOSQLiteTreeHandler.h>
30 #include <creaImageIOSystem.h>
31 #include <creaImageIOGimmick.h>
32 
33 #include "CppSQLite3.h"
34 
35 #include <sys/stat.h>
36 
37 #include <deque>
38 
39 #include <creaWx.h>
40 #include <boost/algorithm/string.hpp>
41 using namespace crea;
42 
43 
44 
45 namespace creaImageIO
46 {
47  using namespace tree;
48 
49 
50  //=============================================================
51  SQLiteTreeHandler::SQLiteTreeHandler(const std::string& filename)
52  : mFileName(filename)
53  {
54  mDB = new CppSQLite3DB;
55  mIsAdding=false;
56  //GimmickMessage(1,"SQLite version : " <<std::string(mDB->SQLiteVersion())<< std::endl);
57  }
58  //=============================================================
59 
60  //=============================================================
61  SQLiteTreeHandler::~SQLiteTreeHandler()
62  {
63  delete mDB;
64  }
65  //=============================================================
66 
67 
68  //=====================================================================
69 
70 
71  //=====================================================================
72  bool SQLiteTreeHandler::Open(bool writable)
73  {
74 
75  //sqlite3_exec(mDB, "PRAGMA synchronous=OFF ", NULL, 0, &errMsg);
76  SetWritable(writable);
77  return DBOpen();
78  }
79 
80  //=====================================================================
81  bool SQLiteTreeHandler::Create(bool writable)
82  {
83  // std::cout << "***> SQLiteTreeHandler::New('"<<GetFileName()<<"')"<<std::endl;
84  SetWritable(writable);
85  return DBCreate();
86  }
87  //=====================================================================
88 
89 
90  //=====================================================================
91  bool SQLiteTreeHandler::Close()
92  {
93  return true;
94  }
95  //=====================================================================
96 
97 
98  //=====================================================================
99  bool SQLiteTreeHandler::Destroy()
100  {
101  return false;
102  }
103 
104  //=====================================================================
105 
106  //=====================================================================
107  int SQLiteTreeHandler::LoadChildren(tree::Node* parent, int maxlevel)
108  {
109  //mDB->execDML("PRAGMA synchronous=ON ");
110  if (parent==0) parent = GetTree().GetTree();
111  return DBLoadChildren(parent,maxlevel);
112  }
113  //=====================================================================
114 
115 
116 
117 
118  //=====================================================================
119  void SQLiteTreeHandler::UnLoad(tree::Node* n)
120  {
121  }
122  //=====================================================================
123 
124  //=====================================================================
125  int SQLiteTreeHandler::AddBranch( const AttributeMapType& attr )
126  {
127  tree::Node* parent = DBGetParent(attr);
128  DBGraftToParent(parent,attr);
129  return (parent->GetLevel()+1);
130 
131  }
132  //=====================================================================
133 
134 
135  //=====================================================================
136  bool SQLiteTreeHandler::Remove(tree::Node* node)
137  {
138  DBRecursiveRemoveNode(node);
139 
140  // std::cout << "DELETE"<<std::endl;
141  bool remove=false;
142  tree::Node* parent=node->GetParent();
143  if (parent)
144  {
145  int nC = parent->RemoveChildrenFromList(node);
146  if(nC>0 && parent->GetLevel()>0)
147  {
148  std::stringstream out;
149  out <<nC;
150  SetAttribute(parent,"NumberOfChildren",out.str());
151  }
152  else
153  {
154  remove=true;
155  }
156 
157  }
158  delete node;
159  if(remove&&parent->GetLevel()>0)
160  {
161  Remove(parent);
162  }
163  // std::cout << "DELETE OK"<<std::endl;
164  return true;
165  }
166 
167  //=====================================================================
168 
169  //=====================================================================
171  bool SQLiteTreeHandler::SetAttribute(tree::Node* n,
172  const std::string& key,
173  const std::string& value)
174  {
175  if (n==0) n=GetTree().GetTree();
176  return DBSetAttribute(n,key,value);
177  }
178  //=====================================================================
179  //=====================================================================
181  void SQLiteTreeHandler::SetAttribute(const std::string& levelDescriptor,
182  const std::string& key,
183  const std::string& value,
184  const std::string& searchParam,
185  const std::string& searchVal)
186  {
187  DBSetAttribute(levelDescriptor,key,value,searchParam, searchVal);
188  }
189  //=====================================================================
191  void SQLiteTreeHandler::DeleteTuple(std::string levelDescriptor,
192  std::string key, std::string value)
193  {
194  DBDelete(levelDescriptor,key,value);
195  }
196  //=====================================================================
197 
198 
199 
200 
201 
202 
203 
204 
205 
206 
207 
208 
209 
210  //=====================================================================
211  // SQLite DB specific methods
212  //=====================================================================
213 
214 
215 
216 
217  //=====================================================================
218  char* format_sql(const std::string& s)
219  {
220  return sqlite3_mprintf("%q",s.c_str());
221  }
222  //=====================================================================
223 
224 
225  //=====================================================================
226 #define QUERYDB(QUER,RES) \
227  try \
228  { \
229  GimmickMessage(2,"SQL query: '"<<QUER<<"'"<<std::endl); \
230  RES = mDB->execQuery(QUER.c_str()); \
231  } \
232  catch (CppSQLite3Exception& e) \
233  { \
234  GimmickError("SQLite query '"<<QUER<<"' : " \
235  << e.errorCode() << ":" \
236  << e.errorMessage() ); \
237  } \
238 
239  //=====================================================================
240 
241  //=====================================================================
242 #define UPDATEDB(UP) \
243  try \
244  { \
245  GimmickMessage(2,"SQL update: '"<<UP<<"'"<<std::endl); \
246  mDB->execDML(UP.c_str()); \
247  } \
248  catch (CppSQLite3Exception& e) \
249  { \
250  GimmickError("SQLite update '"<<UP<<"' Error : " \
251  << e.errorCode() << ":" \
252  << e.errorMessage() ); \
253  }
254  //=====================================================================
255 
256 
257  //=====================================================================
258  bool SQLiteTreeHandler::DBOpen()
259  {
260  GimmickMessage(1,"Opening SQLite database '"<<GetFileName()
261  <<"' ... "<<std::endl);
262  // OPENING FILE
263  if (!boost::filesystem::exists(GetFileName()))
264  {
265  return false;
266  }
267 
268  try
269  {
270  mDB->open(GetFileName().c_str());
271  mDB->execDML("pragma synchronous=off;");
272  }
273  catch (CppSQLite3Exception& e)
274  {
275  GimmickError("Opening '"<<GetFileName()<<"' : "
276  << e.errorCode() << ":"
277  << e.errorMessage());
278  return false;
279  }
280  // IMPORT TREE DESCRIPTION (AND TEST DB VALIDITY)
281  if (!DBImportTreeDescription())
282  {
283  return false;
284  }
285 
286  GimmickDebugMessage(1,"Opening SQLite database '"<<GetFileName()
287  <<"' ... OK"<<std::endl);
288  return true;
289  }
290  //=====================================================================
291 
292  //=====================================================================
293  bool SQLiteTreeHandler::DBCreate()
294  {
295  GimmickMessage(1,"Creating SQLite database '"<<GetFileName()
296  <<"' ... "<<std::endl);
297 
298  if (boost::filesystem::exists(GetFileName()))
299  {
300  GimmickError(GetFileName()<<"' : "
301  << "file already exists");
302  return false;
303  }
304 
305  // OPENING
306  try
307  {
308  mDB->open(GetFileName().c_str());
309  }
310  catch (CppSQLite3Exception& e)
311  {
312  GimmickError(e.errorCode() << ":"
313  << e.errorMessage() <<std::endl);
314  return false;
315  }
316  mDB->execDML("pragma synchronous=off;");
317 
318  // CREATING TABLES
319 
320  std::string command;
321  // Create LEVELS table
322  command = "create table LEVELS\n";
323  command += "( Name text )\n";
324  UPDATEDB(command);
325  int l;
326  // Iterate the Levels
327  for (l=0; l<GetTree().GetNumberOfLevels(); l++)
328  {
329  command = "INSERT INTO LEVELS (Name) VALUES ('";
330  command += GetTree().GetLevelDescriptor(l).GetName();
331  command += "')";
332  UPDATEDB(command);
333 
334  // Create table of level (for level>0, i.e. not Root)
335  if (l>=0)
336  {
337  command = "CREATE TABLE ";
338  command += GetTree().GetLevelDescriptor(l).GetName();
339  command += "\n(\nID INTEGER PRIMARY KEY";
340  if (l>1)
341  {
342  command += ",\nPARENT_ID int not null";
343  }
344  SQLAppendAttributesDefinition(l,command);
345  if (l>1)
346  {
347  command += ",\nconstraint FK_PARENT foreign key (PARENT_ID) references ";
348  command += GetTree().GetLevelDescriptor(l-1).GetName();
349  command += "(ID) on delete restrict on update restrict";
350  }
351  command += "\n)";
352  UPDATEDB(command);
353 
354  GetTree().CopyAttributeDescriptorList(l);
355 
356  // Add Attribute 'ID' to Description
357  GetTree().GetDescriptor().Add
358  (AttributeDescriptor( "ID",
359  "Database Identifier",
360  0,0,
361  AttributeDescriptor::PRIVATE
362  ),l);
363 
364  if (l>1)
365  {
366  // Add Attribute 'PARENT_ID' to Description
367  GetTree().GetDescriptor().Add
368  (AttributeDescriptor( "PARENT_ID",
369  "Database Parent Identifier",
370  0,0,
371  AttributeDescriptor::PRIVATE
372  ),l);
373  }
374 
375  }
376 
377  // Create table *_ATTRIBUTES
378 
379  command = "CREATE TABLE ";
380  command += GetTree().GetLevelDescriptor(l).GetName();
381  command += "_Attributes\n(\n";
382  command += "Key text,\n";
383  command += "Name text,\n";
384  command += "DicomGroup int,\n";
385  command += "DicomElement int,\n";
386  command += "Flags int\n";
387  command += "\n)";
388  UPDATEDB(command);
389 
390  // Fill the table *_ATTRIBUTES
391  LevelDescriptor::AttributeDescriptorListType::const_iterator i;
392  for (i = GetTree().GetAttributeDescriptorList(l).begin();
393  i != GetTree().GetAttributeDescriptorList(l).end();
394  ++i)
395  {
396 
397  std::stringstream insert;
398  insert << "INSERT INTO "
399  << GetTree().GetLevelDescriptor(l).GetName()
400  << "_Attributes (Key,Name,DicomGroup,DicomElement,Flags) "
401  << "VALUES ('"
402  << i->GetKey() << "','"
403  << i->GetName() << "',"
404  << i->GetGroup() << ","
405  << i->GetElement() << ","
406  << i->GetFlags() << ");";
407  UPDATEDB(insert.str());
408  }
409 
410  } // For l=0...
411 
412  // Initialize the root attributes
413  GetTree().InitializeAttributeMap();
414  // Insert the root in the level 0 table
415  DBInsert(GetTree().GetTree());
416 
417 
418  GetTree().SetChildrenLoaded(true);
419  GimmickMessage(1,"Creating SQLite database '"<<GetFileName()
420  <<"' ... OK"<<std::endl);
421  return true;
422  }
423  //=====================================================================
424 
425  //=====================================================================
426  void SQLiteTreeHandler::SQLAppendAttributesDefinition(int level,
427  std::string& s)
428  {
429  LevelDescriptor::AttributeDescriptorListType::const_iterator i;
430  for (i = GetTree().GetAttributeDescriptorList(level).begin();
431  i != GetTree().GetAttributeDescriptorList(level).end();
432  ++i)
433  {
434  // if (i->second.flags==1) continue;
435  s += ",\n";
436  s += i->GetKey();
437  s += " text";
438  }
439  }
440  //=====================================================================
441 
442 
443  //=====================================================================
444  bool SQLiteTreeHandler::DBImportTreeDescription()
445  {
446  GimmickMessage(1,"Importing tree description for database ..."
447  <<std::endl);
448 
449  // Test table 'LEVELS' existence
450  if ( ! mDB->tableExists("LEVELS") )
451  {
452  GimmickMessage(1,"!! ERROR : Table 'LEVELS' does not exist"
453  <<std::endl);
454  return false;
455  }
456 
457  tree::Descriptor& desc = GetTree().GetDescriptor();
458  // clears the existing one
459  desc.Clear();
460 
461  int nblevel = 0;
462  std::string query = "SELECT * FROM LEVELS";
463  CppSQLite3Query q;
464  QUERYDB(query,q);
465 
466  while (!q.eof())
467  {
468  std::string name = q.getStringField(0);
469  GimmickMessage(2," * Importing level '"<<name<<"'"<<std::endl);
470  desc.Add(LevelDescriptor(name));
471  nblevel++;
472  q.nextRow();
473  }
474 
475  for (int level = 0; level < nblevel; ++level )
476  {
477  std::string table = GetTree().GetLevelDescriptor(level).GetName();
478  table += "_Attributes";
479  // Test table 'LEVELS' existence
480  if ( ! mDB->tableExists(table.c_str()) )
481  {
482  GimmickMessage(1,"!! ERROR : Table '"<<table<<"' does not exist"
483  <<std::endl);
484  return false;
485  }
486 
487  std::string query = "SELECT * FROM ";
488  query += table;
489  CppSQLite3Query q;
490  QUERYDB(query,q);
491 
492  GimmickMessage(2," * Level '"
493  <<GetTree().GetLevelDescriptor(level).GetName()
494  <<"'"<<std::endl);
495 
496  // Test that ID and PARENT_ID mandatory attributes exist
497  bool ID_found = false;
498  bool PARENT_ID_found = false;
499  if (level==0) ID_found = true;
500  if (level<=1) PARENT_ID_found = true;
501 
502  while (!q.eof())
503  {
504  std::string key(q.getStringField(0));
505  std::string name(q.getStringField(1));
506  GimmickMessage(2," - Importing attribute '"<<key<<"' '"<<name
507  <<"'"<<std::endl);
508  desc.Add
509  (AttributeDescriptor( key, // Key
510  name, // Name
511  q.getIntField(2), // Group
512  q.getIntField(3), // Element
513  q.getIntField(4) // Flags
514  ),level);
515  if ( key == "ID" )
516  {
517  ID_found = true;
518  }
519  if ( key == "PARENT_ID" )
520  {
521  PARENT_ID_found = true;
522  }
523  q.nextRow();
524  }
525 
526  if ( ! (ID_found || PARENT_ID_found ) )
527  {
528  GimmickMessage(1,"!! ERROR : Table '"<<table
529  <<"' does not contain mandatory attribute ID or PARENT_ID"
530  <<std::endl);
531  return false;
532 
533  }
534  GetTree().CopyAttributeDescriptorList(level);
535  }
536 
537 
538  // Create the attributes table for Root (i.e. Tree)
539  LevelDescriptor::AttributeDescriptorListType::const_iterator a;
540  for (a = GetTree().GetAttributeDescriptorList(0).begin();
541  a!= GetTree().GetAttributeDescriptorList(0).end();
542  ++a)
543  {
544 
545  GetTree().UnsafeSetAttribute( a->GetKey(), "" );
546  }
547 
548  // Reading Root attributes
549  // Query DB
550  query = "SELECT * FROM ";
551  query += GetTree().GetLevelDescriptor(0).GetName();
552  QUERYDB(query,q);
553 
554  for (int fld = 0; fld < q.numFields(); fld++)
555  {
556  GetTree().UnsafeSetAttribute(q.fieldName(fld),
557  q.getStringField(fld));
558  }
559 
560  GimmickMessage(1,"Importing tree description from database ... OK"
561  <<std::endl);
562  return true;
563  }
564  //=====================================================================
565 
566  //========================================================================
568  void SQLformat(std::string i_str, std::string &o_str)
569  {
570  // quote must be doubled
571  boost::algorithm::replace_all(i_str,"'","''");
572  // Found strange strings which contained NULL char INSIDE string
573  int i,size=(int)i_str.size();
574  for (i=0;i<size;++i)
575  {
576  if (i_str[i]==0)
577  {
578  i_str = i_str.substr(0,i);
579  break;
580  }
581  }
582  o_str = i_str;
583  }
584  //========================================================================
585 
586  //=====================================================================
587  void SQLiteTreeHandler::SQLAppendAttributesValues(tree::Node* n,
588  std::string& str)
589  {
590  GimmickMessage(4,"SQLAppendAttributesValues"<<std::endl);
591  std::string atts="";
592  std::string values="";
593  std::string out ="";
594  tree::Node::AttributeMapType::iterator i;
595  for (i = n->GetAttributeMap().begin();
596  i != n->GetAttributeMap().end();
597  i++)
598  {
599  if (i->first=="ID")
600  {
601  continue;
602  }
603 
604  atts += "'" + i->first + "'";
605  SQLformat(i->second, out);
606  values += "'" + out + "'";
607  atts += ",";
608  values += ",";
609  GimmickMessage(4,"'"<<i->first<<"' = '"<<i->second<<"'"<<std::endl);
610  }
611  atts[atts.size()-1]=' ';
612  values[values.size()-1]=' ';
613 
614  str = "("+atts+") VALUES ("+values+")";
615  GimmickMessage(4,"Result = '"<<str<<"'"<<std::endl);
616  }
617  //=====================================================================
618 
619  //=====================================================================
620  tree::Node* SQLiteTreeHandler::DBGetParent( const AttributeMapType& attr)
621  {
622  Node* parent = GetTree().GetTree();
623  bool go_down;
624  do
625  {
626  go_down = false;
627  // Load the children of the current parent
628  DBLoadChildren(parent);
629  // Iterate the children
630  tree::Node::ChildrenListType::const_iterator i;
631  for (i = parent->GetChildrenList().begin();
632  i!= parent->GetChildrenList().end();
633  ++i)
634  {
635  if ( (*i)->Matches( attr ) )
636  {
637  go_down = true;
638  parent = *i;
639  break;
640  }
641  }
642  }
643  while (go_down);
644  return parent;
645  }
646  //=====================================================================
647 
648  //=====================================================================
649  int SQLiteTreeHandler::DBLoadChildren(tree::Node* node,
650  int numberoflevels)
651  {
652  if (node->GetLevel()+1 >= node->GetTree()->GetNumberOfLevels() )
653  return 0;
654 
655  GimmickMessage(2,"Loading children of '"<<node->GetLabel()
656  <<"'"<<std::endl);
657 
658  int nbloaded = 0;
659  // If children loaded we do not have to do it but we need to recurse
660  // in order to load the children's children if necessary, and so on...
661  if (node->GetChildrenLoaded())
662  {
663  // Iterate the children
664 
665  tree::Node::ChildrenListType::iterator i;
666  for (i = node->GetChildrenList().begin();
667  i!= node->GetChildrenList().end();
668  ++i)
669  {
670  nbloaded += DBLoadChildren(*i,numberoflevels-1);
671  }
672  node->SetChildrenLoaded(true);
673  return nbloaded;
674 
675  }
676  else
677  {
679 
680  // Query DB
681  int level = node->GetLevel();
682  std::string query = "SELECT * FROM ";
683 
684  query += GetTree().GetLevelDescriptor(level+1).GetName();
685  if (level>0)
686  {
687  query += " WHERE PARENT_ID='" + node->GetAttribute("ID")
688  + "'";
689  }
690 GimmickDebugMessage(1, "query : '" <<query <<std::endl);
691  CppSQLite3Query q;
692  QUERYDB(query,q);
693 
694  //int p=0;
695  while (!q.eof())
696  {
697 
698  // std::cout << "DBLoadCh : creating node level "<<level+1<<std::endl;
699 
700  nbloaded++;
701  Node* n = new Node(node);
702  for (int fld = 0; fld < q.numFields(); fld++)
703  {
704  n->UnsafeSetAttribute(q.fieldName(fld),q.getStringField(fld));
705  }
706 
707  // recurse
708  if ( numberoflevels != 1 )
709  {
710  // msw[2].Pause();
711  nbloaded += DBLoadChildren(n, numberoflevels-1);
712  // msw[2].Resume();
713  }
714  // next entry in db
715  q.nextRow();
716  }
717 
718  node->SetChildrenLoaded(true);
719 
720 
721  // msw[2].Pause();
722  return nbloaded;
723  }
724  }
725  //=====================================================================
726 
727  //======================================================================
728  void SQLiteTreeHandler::DBInsert(tree::Node* n)
729  {
730  GimmickMessage(2,"Inserting in DB '"<<n->GetLabel()
731  <<"'"<<std::endl);
732  std::string val;
733  SQLAppendAttributesValues(n,val);
734  std::string insert("INSERT INTO ");
735  insert += GetTree().GetLevelDescriptor(n->GetLevel()).GetName();
736  insert += " " + val + ";";
737 
738  UPDATEDB(insert);
739 
740  // Store DB id of newly created node;
741  //long lastrow = mDB->lastRowId();
742  std::stringstream ri;
743  ri << mDB->lastRowId();
744  n->SetAttribute("ID",ri.str());
745  }
746  //======================================================================
747 
748  //======================================================================
750  void SQLiteTreeHandler::DBGraftToParent( tree::Node* parent,
751  const AttributeMapType& attr)
752  {
753  // std::cout <<"Grafting to parent '"<<parent->GetLabel()
754  // <<"'"<<std::endl;
755 
756  for (int level = parent->GetLevel()+1;
757  level < GetTree().GetNumberOfLevels();
758  level++)
759  {
760  // Create Node
761  tree::Node* child = new tree::Node(parent,attr);
762  child->SetChildrenLoaded(true);
763  if (level>1)
764  {
765  int nc = GetNumberOfChildren(parent)+1;
766 
767  // std::cout<<"Number of children "<<parent->GetNumberOfChildren()<<std::endl;
768  std::stringstream out;
769  out << nc;
770  SetAttribute(parent,"NumberOfChildren",out.str());
771  }
772 
773  // Set PARENT_ID if necessary
774  if ( parent->GetLevel()>0 )
775  child->SetAttribute("PARENT_ID",parent->GetAttribute("ID"));
776 
777  // Insert in DB
778  DBInsert(child);
779 
780  // Down one level
781  parent = child;
782  }
783  }
784  //======================================================================
785 
786 
787  //=====================================================================
789  bool SQLiteTreeHandler::DBSetAttribute(tree::Node* n,
790  const std::string& key,
791  const std::string& value)
792  {
793  GimmickMessage(3,"Setting Attribute of '"<<n->GetLabel()<<
794  "' "<<key<<"='"<<value<<"'"<<std::endl);
795 
796  n->SetAttribute(key,value);
797  std::string sql = "UPDATE ";
798  sql += GetTree().GetLevelDescriptor(n->GetLevel()).GetName();
799  sql += " SET ";
800  sql += key;
801  sql += " = '";
802  sql += convert(value);
803  sql += "' WHERE ID = '";
804  sql += n->GetAttribute("ID");
805  sql +="'";
806  // sql += " LIMIT 1";
807  UPDATEDB(sql);
808  return true;
809  }
810 
811  //=====================================================================
813  void SQLiteTreeHandler::DBSetAttribute(const std::string& levelDescriptor,
814  const std::string& key,
815  const std::string& value,
816  const std::string& searchParam,
817  const std::string& searchVal)
818  {
819 
820  std::string sql = "UPDATE ";
821  sql += levelDescriptor;
822  sql += " SET ";
823  sql += key;
824  sql += " = '";
825  sql += value;
826  sql += "' WHERE ";
827  sql += searchParam;
828  sql += " = '";
829  sql += searchVal;
830  sql += "'";
831  std::cout<<sql<<std::endl;
832  UPDATEDB(sql);
833  }
834  //=====================================================================
835  void SQLiteTreeHandler::DBRecursiveRemoveNode(Node* node)
836  {
837 
838  std::string query = "DELETE FROM ";
839  query += GetTree().GetLevelDescriptor(node->GetLevel()).GetName();
840  query += " WHERE ID='"+ node->GetAttribute("ID") + "';";
841  UPDATEDB(query);
842 
843  if(node->GetNumberOfChildren()!=0)
844  {
845  Node::ChildrenListType::iterator i;
846  for (i = node->GetChildrenList().begin();
847  i != node->GetChildrenList().end();
848  i++)
849  {
850  DBRecursiveRemoveNode((*i));
851  }
852  }
853  else if(node->GetLevel()<GetTree().GetNumberOfLevels()-1)
854  {
855  DBRecursiveRemoveNode(node->GetLevel()+1,node->GetAttribute("ID"));
856  }
857  }
858 
859  //=====================================================================
860  void SQLiteTreeHandler::DBRecursiveRemoveNode(int level, std::string parentId)
861  {
862  std::stringstream out;
863  std::stringstream result;
864  out<<"SELECT ID FROM "<<GetTree().GetLevelDescriptor(level).GetName()<<" WHERE PARENT_ID='"<<parentId<<"'";
865 
866  CppSQLite3Query q;
867  QUERYDB(out.str(),q);
868 
869  while (!q.eof())
870  {
871  for (int fld = 0; fld < q.numFields(); fld++)
872  {
873  result<<q.getStringField(fld)<<"#";
874  }
875  q.nextRow();
876  }
877  std::string res=result.str();
878  size_t ini=0;
879  size_t fin=0;
880  while(fin<res.size()-1)
881  {
882  fin=res.find('#',ini);
883  DBDelete(GetTree().GetLevelDescriptor(level).GetName(),"ID",res.substr(ini,fin-ini));
884  if(level<GetTree().GetNumberOfLevels()-1)
885  {
886  DBRecursiveRemoveNode(level+1,res.substr(ini,fin-ini));
887  }
888  ini=fin+1;
889  }
890 
891 
892  }
893 
894  //=====================================================================
895  void SQLiteTreeHandler::DBDelete(std::string levelDescriptor, std::string key, std::string value)
896  {
897 
898  std::stringstream query;
899  query<<"DELETE FROM "<<levelDescriptor<<" WHERE "<<key<<"='"<<value<<"';";
900 
901  UPDATEDB(query.str());
902  GimmickDebugMessage(2," Deleting: Query: "<<query.str()<<std::endl);
903  }
904 
905 
906  //=====================================================================
907  void SQLiteTreeHandler::GetAttribute(std::string levelDescriptor,
908  std::string searchParam,
909  std::string searchVal,
910  std::string key,
911  std::string& result)
912  {
913  std::stringstream out;
914  std::stringstream results;
915  out<<"SELECT "<<key<<" FROM "<<levelDescriptor;
916  if(searchParam!="")
917  {
918  out<<" WHERE "<<searchParam<<"='"<<searchVal<<"'";
919  }
920 
921  CppSQLite3Query q;
922  QUERYDB(out.str(),q);
923 
924 
925  while (!q.eof())
926  {
927  for (int fld = 0; fld < q.numFields(); fld++)
928  {
929  results<<q.getStringField(fld);
930  if(searchParam=="")
931  {
932  results<<"#";
933  }
934  }
935  q.nextRow();
936  }
937  result=results.str();
938 
939  }
940  //=====================================================================
941  unsigned int SQLiteTreeHandler::GetNumberOfChildren(tree::Node* n)
942  {
943  // Query DB
944  int nb=0;
945  int level = n->GetLevel();
946 
947  if(level<GetTree().GetNumberOfLevels()&& level>0)
948  {
949  std::string query = "SELECT NumberOfChildren FROM ";
950  query += GetTree().GetLevelDescriptor(level).GetName();
951  if (level>0)
952  {
953  query += " WHERE ID='" + n->GetAttribute("ID")
954  + "'";
955  }
956  CppSQLite3Query q;
957  QUERYDB(query,q);
958 
959 
960  while (!q.eof())
961  {
962  for (int fld = 0; fld < q.numFields(); fld++)
963  {
964  nb=q.getIntField(fld);
965  }
966  q.nextRow();
967  }
968  }
969  /*
970  if(nb==0)
971  {
972  nb=1;
973  }
974  */
975  return nb;
976  }
977 
978 //=====================================================================
979 // get all attributes from database for a given file
980  void SQLiteTreeHandler::getAllAttributes(std::string i_filename, std::map<std::string, std::string> &i_results)
981  {
982  int level=GetTree().GetNumberOfLevels()-1;
983 
984  std::string search = i_filename;
985  std::string param = "FullFileName";
986  std::string name;
987 
988  std::string id;
989  std::set<std::string> pid;
990  std::vector<AttributeDescriptor> attr;
991  std::vector<AttributeDescriptor>::iterator it_attr;
992  std::vector<std::string> values;
993  std::vector<std::string>::iterator it_val;
994 
995 
996 
997 
998  while(level>1)
999  {
1000  attr = GetTree().GetAttributeDescriptorList(level,1);
1001 
1002  name = GetTree().GetLevelDescriptor(level).GetName();
1003  std::vector<std::string> values;
1004  GetUpLevelNodeId(level, param,search,id);
1005  GetAttributes(name, param,search,attr, values);
1006  for(it_attr = attr.begin(), it_val = values.begin(); it_attr != attr.end(); it_attr++, it_val++)
1007  {
1008  i_results[(*it_attr).GetKey()] = (*it_val).c_str();
1009  }
1010  search = id;
1011  param = "ID";
1012  level --;
1013  }
1014  }
1015 
1016 
1017 //=====================================================================
1018 // get selected attributes from database for a given file
1019  void SQLiteTreeHandler::GetAttributes(std::string name, std::string i_id, std::string i_value, tree::LevelDescriptor::AttributeDescriptorListType i_attr, std::vector<std::string> &i_results)
1020  {
1021  //SELECT t1.ArtistName,CDs.Title FROM Artists t1, CDs WHERE t1.ArtistID=CDs.ArtistID
1022  std::stringstream out;
1023  std::stringstream results;
1024  out<<"SELECT ";
1025  tree::LevelDescriptor::AttributeDescriptorListType::iterator it = i_attr.begin();
1026  std::string query ="";
1027  for(; it != i_attr.end(); it++)
1028  {
1029  query += (*it).GetKey();
1030  query +=" ,";
1031  }
1032  query = query.substr(0, query.size()-1);
1033  out << query;
1034  out << "FROM "<<name;
1035  out<<" WHERE "<<i_id <<"='"<<i_value<<"'";
1036  CppSQLite3Query q;
1037  QUERYDB(out.str(),q);
1038  while (!q.eof())
1039  {
1040  for (int fld = 0; fld < q.numFields(); fld++)
1041  {
1042  i_results.push_back(q.getStringField(fld));
1043  }
1044  q.nextRow();
1045  }
1046  }
1047 
1048  void SQLiteTreeHandler::GetUpLevelNodeId(int level, const std::string& searchParam, const std::string& searchValue, std::string& parent_id)
1049  {
1050  std::string sp=searchParam.c_str();
1051  std::string sv=searchValue.c_str();
1052  std::stringstream out;
1053  std::stringstream results;
1054  out<<"SELECT PARENT_ID FROM "<<GetTree().GetLevelDescriptor(level).GetName();
1055  out<<" WHERE "<<sp<<"='"<<sv<<"'";
1056  CppSQLite3Query q;
1057  QUERYDB(out.str(),q);
1058  while (!q.eof())
1059  {
1060  for (int fld = 0; fld < q.numFields(); fld++)
1061  {
1062  results<<q.getStringField(fld);
1063  }
1064  q.nextRow();
1065  }
1066  parent_id = results.str();
1067 
1068  }
1069 
1070 
1071  //=====================================================================
1072  void SQLiteTreeHandler::GetTopLevelNodeId(const std::string& searchParam, const std::string& searchValue, std::string& parent_id)
1073  {
1074  int level=GetTree().GetNumberOfLevels()-1;
1075  std::string sp=searchParam.c_str();
1076  std::string sv=searchValue.c_str();
1077 
1078  while(level>1)
1079  {
1080  GetUpLevelNodeId(level, sp, sv, parent_id);
1081  level--;
1082  sp = "ID";
1083  sv = parent_id;
1084  }
1086 // std::stringstream out;
1087 // std::stringstream results;
1088 // out<<"SELECT PARENT_ID FROM "<<GetTree().GetLevelDescriptor(level).GetName();
1089 // out<<" WHERE "<<sp<<"='"<<sv<<"'";
1090 // CppSQLite3Query q;
1091 // QUERYDB(out.str(),q);
1092 //
1093 //
1094 // while (!q.eof())
1095 // {
1096 // for (int fld = 0; fld < q.numFields(); fld++)
1097 // {
1098 // results<<q.getStringField(fld);
1099 // }
1100 // q.nextRow();
1101 // }*/
1102 // level=level-1;
1103 // sp="ID";
1104 // sv=results.str();
1105 // }
1106 // parent_id=sv;
1107 
1108  }
1109 
1110  //=====================================================================
1111  void SQLiteTreeHandler::RemoveEntries(const std::string i_table,
1112  const std::string i_attribute,
1113  const std::string i_operand,
1114  const std::string i_val)
1115  {
1116  std::stringstream query;
1117  query<<"DELETE FROM "<<i_table<<" WHERE "<<i_attribute<<" "<<i_operand<<" '"<<i_val<<"'";
1118  UPDATEDB(query.str());
1119  }
1120 
1121  //=====================================================================
1122  void SQLiteTreeHandler::BeginTransaction()
1123  {
1124  std::stringstream out;
1125  out<<"begin transaction;";
1126  UPDATEDB(out.str());
1127  }
1128 
1129  //=====================================================================
1130  void SQLiteTreeHandler::EndTransaction()
1131  {
1132  std::stringstream out;
1133  out<<"commit transaction;";
1134  UPDATEDB(out.str());
1135  }
1136  const std::string SQLiteTreeHandler::convert(const std::string &i_word)
1137  {
1138  std::string temp = i_word;
1139  boost::algorithm::replace_all(temp,"'","''");
1140  return temp.c_str();
1141  }
1142 
1143 } // namespace creaImageIO