1 #include <creaImageIOSQLiteTreeHandler.h>
2 #include <creaImageIOSystem.h>
4 #include "CppSQLite3.h"
8 //#include <creaImageIOSQLiteTreeHandlerStructure.h>
10 //#include <creaImageIOUtilities.h>
12 //#include <icons/database.xpm>
18 #include <wx/filename.h>
21 //#include <icons/close.xpm>
26 #include <boost/filesystem.hpp>
27 #include <boost/algorithm/string/replace.hpp>
34 //=============================================================
35 SQLiteTreeHandler::SQLiteTreeHandler(const std::string& filename)
38 mDB = new CppSQLite3DB;
40 GimmickMessage(1,"SQLite version : "
41 <<std::string(mDB->SQLiteVersion())<< std::endl);
43 //=============================================================
45 //=============================================================
46 SQLiteTreeHandler::~SQLiteTreeHandler()
50 //=============================================================
53 //=============================================================
54 // void SQLiteTreeHandler::Print() const
57 std::cout << "-> '"<<GetName()<< "' - '"
58 << GetFileName()<<"'"<<std::endl;
59 ChildrenListType::const_iterator i;
60 for (i=GetChildrenList().begin(); i!=GetChildrenList().end(); i++)
66 //=============================================================
68 //=====================================================================
70 bool SQLiteTreeHandler::LocationIsValid()
76 //=====================================================================
79 //=====================================================================
80 bool SQLiteTreeHandler::Open(bool writable)
82 // std::cout << "***> SQLiteTreeHandler::Open('"<<GetFileName()<<"')"<<std::endl;
83 SetWritable(writable);
87 //=====================================================================
88 bool SQLiteTreeHandler::Create(bool writable)
90 // std::cout << "***> SQLiteTreeHandler::New('"<<GetFileName()<<"')"<<std::endl;
91 SetWritable(writable);
94 //=====================================================================
97 //=====================================================================
98 bool SQLiteTreeHandler::Close()
102 //=====================================================================
105 //=====================================================================
106 bool SQLiteTreeHandler::Destroy()
111 //=====================================================================
113 //=====================================================================
114 int SQLiteTreeHandler::LoadChildren(tree::Node* parent, int maxlevel)
116 if (parent==0) parent = GetTree().GetTree();
117 return DBLoadChildren(parent,maxlevel);
119 //=====================================================================
124 //=====================================================================
125 void SQLiteTreeHandler::UnLoad(tree::Node* n)
128 //=====================================================================
130 //=====================================================================
131 int SQLiteTreeHandler::AddBranch( const AttributeMapType& attr )
133 tree::Node* parent = DBGetParent(attr);
134 DBGraftToParent(parent,attr);
135 return (parent->GetLevel()+1);
138 //=====================================================================
141 //=====================================================================
142 bool SQLiteTreeHandler::Remove(tree::Node* node)
144 DBRecursiveRemoveNode(node);
146 // std::cout << "DELETE"<<std::endl;
148 tree::Node* parent=node->GetParent();
151 int nC = parent->RemoveChildrenFromList(node);
154 std::stringstream out;
156 SetAttribute(parent,"NumberOfChildren",out.str());
165 if(remove&&parent->GetLevel()>0)
169 // std::cout << "DELETE OK"<<std::endl;
173 //========================================================================
177 //=====================================================================
179 //=====================================================================
180 /// Sets an attribute of a Node
181 bool SQLiteTreeHandler::SetAttribute(tree::Node* n,
182 const std::string& key,
183 const std::string& value)
185 if (n==0) n=GetTree().GetTree();
186 return DBSetAttribute(n,key,value);
188 //=====================================================================
189 //=====================================================================
190 /// Sets an attribute
191 void SQLiteTreeHandler::SetAttribute(const std::string& levelDescriptor,
192 const std::string& key,
193 const std::string& value,
194 const std::string& searchParam,
195 const std::string& searchVal)
197 DBSetAttribute(levelDescriptor,key,value,searchParam, searchVal);
199 //=====================================================================
201 void SQLiteTreeHandler::DeleteTuple(std::string levelDescriptor,
202 std::string key, std::string value)
204 DBDelete(levelDescriptor,key,value);
206 //=====================================================================
220 //=====================================================================
221 // SQLite DB specific methods
222 //=====================================================================
227 //=====================================================================
228 char* format_sql(const std::string& s)
230 return sqlite3_mprintf("%q",s.c_str());
232 //=====================================================================
234 // sqlite3_exec(db, zSQL, 0, 0, 0);
235 // sqlite3_free(zSQL);
236 // char* CHAIN = format_sql(QUER); \
237 // sqlite3_free(CHAIN); \
239 //=====================================================================
240 #define QUERYDB(QUER,RES) \
243 GimmickMessage(2,"SQL query: '"<<QUER<<"'"<<std::endl); \
244 RES = mDB->execQuery(QUER.c_str()); \
246 catch (CppSQLite3Exception& e) \
248 GimmickError("SQLite query '"<<QUER<<"' : " \
249 << e.errorCode() << ":" \
250 << e.errorMessage() ); \
253 //=====================================================================
255 //=====================================================================
256 #define UPDATEDB(UP) \
259 GimmickMessage(2,"SQL update: '"<<UP<<"'"<<std::endl); \
260 mDB->execDML(UP.c_str()); \
262 catch (CppSQLite3Exception& e) \
264 GimmickError("SQLite update '"<<UP<<"' Error : " \
265 << e.errorCode() << ":" \
266 << e.errorMessage() ); \
268 //=====================================================================
271 //=====================================================================
272 bool SQLiteTreeHandler::DBOpen()
274 GimmickMessage(1,"Opening SQLite database '"<<GetFileName()
275 <<"' ... "<<std::endl);
277 if (!boost::filesystem::exists(GetFileName()))
284 mDB->open(GetFileName().c_str());
286 catch (CppSQLite3Exception& e)
288 GimmickError("Opening '"<<GetFileName()<<"' : "
289 << e.errorCode() << ":"
290 << e.errorMessage());
293 // IMPORT TREE DESCRIPTION (AND TEST DB VALIDITY)
294 if (!DBImportTreeDescription())
299 GimmickDebugMessage(1,"Opening SQLite database '"<<GetFileName()
300 <<"' ... OK"<<std::endl);
303 //=====================================================================
305 //=====================================================================
306 bool SQLiteTreeHandler::DBCreate()
308 GimmickMessage(1,"Creating SQLite database '"<<GetFileName()
309 <<"' ... "<<std::endl);
311 if (boost::filesystem::exists(GetFileName()))
313 GimmickError(GetFileName()<<"' : "
314 << "file already exists");
321 mDB->open(GetFileName().c_str());
323 catch (CppSQLite3Exception& e)
325 GimmickError(e.errorCode() << ":"
326 << e.errorMessage() <<std::endl);
334 // Create LEVELS table
335 command = "create table LEVELS\n";
336 command += "( Name text )\n";
339 // Iterate the Levels
340 for (int l=0; l<GetTree().GetNumberOfLevels(); ++l)
342 command = "INSERT INTO LEVELS (Name) VALUES ('";
343 command += GetTree().GetLevelDescriptor(l).GetName();
347 // Create table of level (for level>0, i.e. not Root)
350 command = "CREATE TABLE ";
351 command += GetTree().GetLevelDescriptor(l).GetName();
352 command += "\n(\nID INTEGER PRIMARY KEY";
355 command += ",\nPARENT_ID int not null";
357 SQLAppendAttributesDefinition(l,command);
360 command += ",\nconstraint FK_PARENT foreign key (PARENT_ID) references ";
361 command += GetTree().GetLevelDescriptor(l-1).GetName();
362 command += "(ID) on delete restrict on update restrict";
368 // Add Attribute 'ID' to Description
369 GetTree().GetDescriptor().Add
370 (AttributeDescriptor( "ID",
371 "Database Identifier",
373 AttributeDescriptor::PRIVATE
378 // Add Attribute 'PARENT_ID' to Description
379 GetTree().GetDescriptor().Add
380 (AttributeDescriptor( "PARENT_ID",
381 "Database Parent Identifier",
383 AttributeDescriptor::PRIVATE
389 // Create table *_ATTRIBUTES
391 command = "CREATE TABLE ";
392 command += GetTree().GetLevelDescriptor(l).GetName();
393 command += "_Attributes\n(\n";
394 command += "Key text,\n";
395 command += "Name text,\n";
396 command += "DicomGroup int,\n";
397 command += "DicomElement int,\n";
398 command += "Flags int\n";
403 // Fill the table *_ATTRIBUTES
404 LevelDescriptor::AttributeDescriptorListType::const_iterator i;
405 for (i = GetTree().GetAttributeDescriptorList(l).begin();
406 i != GetTree().GetAttributeDescriptorList(l).end();
410 std::stringstream insert;
411 insert << "INSERT INTO "
412 << GetTree().GetLevelDescriptor(l).GetName()
413 << "_Attributes (Key,Name,DicomGroup,DicomElement,Flags) "
415 << i->GetKey() << "','"
416 << i->GetName() << "',"
417 << i->GetGroup() << ","
418 << i->GetElement() << ","
419 << i->GetFlags() << ");";
420 UPDATEDB(insert.str());
425 // Initialize the root attributes
426 GetTree().InitializeAttributeMap();
427 // Insert the root in the level 0 table
428 DBInsert(GetTree().GetTree());
431 GetTree().SetChildrenLoaded(true);
432 GimmickMessage(1,"Creating SQLite database '"<<GetFileName()
433 <<"' ... OK"<<std::endl);
436 //=====================================================================
438 //=====================================================================
439 void SQLiteTreeHandler::SQLAppendAttributesDefinition(int level,
442 LevelDescriptor::AttributeDescriptorListType::const_iterator i;
443 for (i = GetTree().GetAttributeDescriptorList(level).begin();
444 i != GetTree().GetAttributeDescriptorList(level).end();
447 // if (i->second.flags==1) continue;
453 //=====================================================================
456 //=====================================================================
457 bool SQLiteTreeHandler::DBImportTreeDescription()
459 GimmickMessage(1,"Importing tree description from database ..."
462 // Test table 'LEVELS' existence
463 if ( ! mDB->tableExists("LEVELS") )
465 GimmickMessage(1,"!! ERROR : Table 'LEVELS' does not exist"
470 tree::Descriptor& desc = GetTree().GetDescriptor();
471 // clears the existing one
475 std::string query = "SELECT * FROM LEVELS";
481 std::string name = q.getStringField(0);
482 GimmickMessage(2," * Importing level '"<<name<<"'"<<std::endl);
483 desc.Add(LevelDescriptor(name));
488 for (int level = 0; level < nblevel; ++level )
490 std::string table = GetTree().GetLevelDescriptor(level).GetName();
491 table += "_Attributes";
492 // Test table 'LEVELS' existence
493 if ( ! mDB->tableExists(table.c_str()) )
495 GimmickMessage(1,"!! ERROR : Table '"<<table<<"' does not exist"
500 std::string query = "SELECT * FROM ";
505 GimmickMessage(2," * Level '"
506 <<GetTree().GetLevelDescriptor(level).GetName()
509 // Test that ID and PARENT_ID mandatory attributes exist
510 bool ID_found = false;
511 bool PARENT_ID_found = false;
512 if (level==0) ID_found = true;
513 if (level<=1) PARENT_ID_found = true;
517 std::string key(q.getStringField(0));
518 std::string name(q.getStringField(1));
519 GimmickMessage(2," - Importing attribute '"<<key<<"' '"<<name
522 (AttributeDescriptor( key, // Key
524 q.getIntField(2), // Group
525 q.getIntField(3), // Element
526 q.getIntField(4) // Flags
532 if ( key == "PARENT_ID" )
534 PARENT_ID_found = true;
539 if ( ! (ID_found || PARENT_ID_found ) )
541 GimmickMessage(1,"!! ERROR : Table '"<<table
542 <<"' does not contain mandatory attribute ID or PARENT_ID"
550 // Create the attributes table for Root (i.e. Tree)
551 LevelDescriptor::AttributeDescriptorListType::const_iterator a;
552 for (a = GetTree().GetAttributeDescriptorList(0).begin();
553 a!= GetTree().GetAttributeDescriptorList(0).end();
558 AttributeMapType::const_iterator i = attr.find(a->GetKey());
559 if ( i != attr.end() )
564 GetTree().UnsafeSetAttribute( a->GetKey(), "" );
567 // Reading Root attributes
569 query = "SELECT * FROM ";
570 query += GetTree().GetLevelDescriptor(0).GetName();
573 for (int fld = 0; fld < q.numFields(); fld++)
575 GetTree().UnsafeSetAttribute(q.fieldName(fld),
576 q.getStringField(fld));
579 GimmickMessage(1,"Importing tree description from database ... OK"
583 //=====================================================================
601 //========================================================================
603 std::string& SQLformat(std::string& str)
605 // quote must be doubled
606 boost::algorithm::replace_all(str,"'","''");
607 // Found strange strings which contained NULL char INSIDE string
608 int i,size=str.size();
613 str = str.substr(0,i);
620 //========================================================================
622 //=====================================================================
623 void SQLiteTreeHandler::SQLAppendAttributesValues(tree::Node* n,
626 GimmickMessage(4,"SQLAppendAttributesValues"<<std::endl);
628 std::string values="";
629 tree::Node::AttributeMapType::iterator i;
630 for (i = n->GetAttributeMap().begin();
631 i != n->GetAttributeMap().end();
638 // std::cout << "("<<i->first<<","<<i->second<<")"<<std::endl;
639 atts += "'" + i->first + "'";
640 values += "'" + SQLformat(i->second) + "'";
643 GimmickMessage(4,"'"<<i->first<<"' = '"<<i->second<<"'"<<std::endl);
645 atts[atts.size()-1]=' ';
646 values[values.size()-1]=' ';
648 str = "("+atts+") VALUES ("+values+")";
649 GimmickMessage(4,"Result = '"<<str<<"'"<<std::endl);
651 //=====================================================================
653 //=====================================================================
654 tree::Node* SQLiteTreeHandler::DBGetParent( const AttributeMapType& attr)
656 Node* parent = GetTree().GetTree();
661 // Load the children of the current parent
662 DBLoadChildren(parent);
663 // Iterate the children
664 tree::Node::ChildrenListType::const_iterator i;
665 for (i = parent->GetChildrenList().begin();
666 i!= parent->GetChildrenList().end();
669 if ( (*i)->Matches( attr ) )
680 //=====================================================================
683 //=====================================================================
684 int SQLiteTreeHandler::DBLoadChildren(tree::Node* node,
687 if (node->GetLevel()+1 >= node->GetTree()->GetNumberOfLevels() )
690 GimmickMessage(2,"Loading children of '"<<node->GetLabel()
694 // If children loaded we do not have to do it but we need to recurse
695 // in order to load the children's children if necessary, and so on...
696 if (node->GetChildrenLoaded())
698 // Iterate the children
700 tree::Node::ChildrenListType::iterator i;
701 for (i = node->GetChildrenList().begin();
702 i!= node->GetChildrenList().end();
705 nbloaded += DBLoadChildren(*i,numberoflevels-1);
707 node->SetChildrenLoaded(true);
713 /// If children not loaded : do it and recurse
716 int level = node->GetLevel();
717 std::string query = "SELECT * FROM ";
719 query += GetTree().GetLevelDescriptor(level+1).GetName();
722 query += " WHERE PARENT_ID='" + node->UnsafeGetAttribute("ID")
732 // std::cout << "DBLoadCh : creating node level "<<level+1<<std::endl;
735 Node* n = new Node(node);
736 for (int fld = 0; fld < q.numFields(); fld++)
738 n->UnsafeSetAttribute(q.fieldName(fld),q.getStringField(fld));
744 ti.id = n->GetFieldValue("ID");
745 mTypeIdToNodeMap[ti] = n;
748 if ( numberoflevels != 1 )
751 nbloaded += DBLoadChildren(n, numberoflevels-1);
758 node->SetChildrenLoaded(true);
765 //=====================================================================
767 //======================================================================
768 void SQLiteTreeHandler::DBInsert(tree::Node* n)
770 GimmickMessage(2,"Inserting in DB '"<<n->GetLabel()
773 SQLAppendAttributesValues(n,val);
774 std::string insert("INSERT INTO ");
775 insert += GetTree().GetLevelDescriptor(n->GetLevel()).GetName();
776 insert += " " + val + ";";
780 // Store DB id of newly created node;
781 long lastrow = mDB->lastRowId();
782 std::stringstream ri;
783 ri << mDB->lastRowId();
784 n->SetAttribute("ID",ri.str());
786 //======================================================================
788 //======================================================================
789 /// Graft the branch defined by the attributes to the parent
790 void SQLiteTreeHandler::DBGraftToParent( tree::Node* parent,
791 const AttributeMapType& attr)
793 // std::cout <<"Grafting to parent '"<<parent->GetLabel()
796 for (int level = parent->GetLevel()+1;
797 level < GetTree().GetNumberOfLevels();
801 tree::Node* child = new tree::Node(parent,attr);
802 child->SetChildrenLoaded(true);
805 int nc = GetNumberOfChildren(parent)+1;
807 // std::cout<<"Number of children "<<parent->GetNumberOfChildren()<<std::endl;
808 std::stringstream out;
810 SetAttribute(parent,"NumberOfChildren",out.str());
813 // Set PARENT_ID if necessary
814 if ( parent->GetLevel()>0 )
815 child->SetAttribute("PARENT_ID",parent->GetAttribute("ID"));
821 SQLAppendAttributesValues(child,val);
822 std::string insert("INSERT INTO ");
823 insert += GetTree().GetLevelDescriptor(child->GetLevel()).GetName();
824 insert += " " + val + ";";
827 // Store DB id of newly created node;
828 long lastrow = mDB->lastRowId();
829 std::stringstream ri;
830 ri << mDB->lastRowId();
831 child->SetAttribute("ID",ri.str());
837 // Insert in TypeId map
839 ti.type = node->GetType();
841 mTypeIdToNodeMap[ti] = node;
842 // std::cout << "== Insert TypeId ("<<ti.type<<","<<ti.id<<")"<<std::endl;
846 if (node->GetType()==Node::Patient) summary.added_patients++;
847 if (node->GetType()==Node::Study) summary.added_studies++;
848 if (node->GetType()==Node::Series) summary.added_series++;
849 if (node->GetType()==Node::Image) summary.added_images++;
853 //======================================================================
856 //=====================================================================
857 /// Sets an attribute of a Node
858 bool SQLiteTreeHandler::DBSetAttribute(tree::Node* n,
859 const std::string& key,
860 const std::string& value)
862 GimmickMessage(3,"Setting Attribute of '"<<n->GetLabel()<<
863 "' "<<key<<"='"<<value<<"'"<<std::endl);
865 n->SetAttribute(key,value);
866 std::string sql = "UPDATE ";
867 sql += GetTree().GetLevelDescriptor(n->GetLevel()).GetName();
872 sql += "' WHERE ID = '";
873 sql += n->GetAttribute("ID");
875 // sql += " LIMIT 1";
879 //=====================================================================
880 /// Sets an attribute of a Node
881 void SQLiteTreeHandler::DBSetAttribute(const std::string& levelDescriptor,
882 const std::string& key,
883 const std::string& value,
884 const std::string& searchParam,
885 const std::string& searchVal)
888 std::string sql = "UPDATE ";
889 sql += levelDescriptor;
901 //=====================================================================
902 void SQLiteTreeHandler::DBRecursiveRemoveNode(Node* node)
905 std::string query = "DELETE FROM ";
907 query += GetTree().GetLevelDescriptor(node->GetLevel()).GetName();
908 query += " WHERE ID='"+ node->GetAttribute("ID") + "';";
911 GimmickDebugMessage(2,
913 <<node->GetLabel()<<"' with ID '"
914 <<node->GetAttribute("ID")
915 <<"' in level "<< GetTree().GetLevelDescriptor(node->GetLevel()).GetName()
919 if(node->GetNumberOfChildren()!=0)
921 Node::ChildrenListType::iterator i;
922 for (i = node->GetChildrenList().begin();
923 i != node->GetChildrenList().end();
926 DBRecursiveRemoveNode((*i));
929 else if(node->GetLevel()<GetTree().GetNumberOfLevels()-1)
931 DBRecursiveRemoveNode(node->GetLevel()+1,node->GetAttribute("ID"));
935 //=====================================================================
936 void SQLiteTreeHandler::DBRecursiveRemoveNode(int level, std::string parentId)
938 std::stringstream out;
939 std::stringstream result;
940 out<<"SELECT ID FROM "<<GetTree().GetLevelDescriptor(level).GetName()<<" WHERE PARENT_ID='"<<parentId<<"'";
943 QUERYDB(out.str(),q);
947 for (int fld = 0; fld < q.numFields(); fld++)
949 result<<q.getStringField(fld)<<"#";
953 std::string res=result.str();
956 while(fin<res.size()-1)
958 fin=res.find('#',ini);
959 DBDelete(GetTree().GetLevelDescriptor(level).GetName(),"ID",res.substr(ini,fin-ini));
960 if(level<GetTree().GetNumberOfLevels()-1)
962 DBRecursiveRemoveNode(level+1,res.substr(ini,fin-ini));
970 //=====================================================================
971 void SQLiteTreeHandler::DBDelete(std::string levelDescriptor, std::string key, std::string value)
974 std::stringstream query;
975 query<<"DELETE FROM "<<levelDescriptor<<" WHERE "<<key<<"='"<<value<<"';";
977 UPDATEDB(query.str());
978 GimmickDebugMessage(2," Deleting: Query: "<<query.str()<<std::endl);
982 //=====================================================================
983 void SQLiteTreeHandler::GetAttribute(std::string levelDescriptor,
984 std::string searchParam,
985 std::string searchVal,
989 std::stringstream out;
990 std::stringstream results;
991 out<<"SELECT "<<key<<" FROM "<<levelDescriptor;
994 out<<" WHERE "<<searchParam<<"='"<<searchVal<<"'";
998 QUERYDB(out.str(),q);
1003 for (int fld = 0; fld < q.numFields(); fld++)
1005 results<<q.getStringField(fld);
1013 result=results.str();
1016 //=====================================================================
1017 unsigned int SQLiteTreeHandler::GetNumberOfChildren(tree::Node* n)
1021 int level = n->GetLevel();
1023 if(level<GetTree().GetNumberOfLevels()&& level>0)
1025 std::string query = "SELECT NumberOfChildren FROM ";
1026 query += GetTree().GetLevelDescriptor(level).GetName();
1029 query += " WHERE ID='" + n->UnsafeGetAttribute("ID")
1038 for (int fld = 0; fld < q.numFields(); fld++)
1040 nb=q.getIntField(fld);
1054 //=====================================================================
1055 void SQLiteTreeHandler::GetTopLevelNodeId(const std::string& searchParam, const std::string& searchValue, std::string& parent_id)
1057 int level=GetTree().GetNumberOfLevels()-1;
1058 std::string sp=searchParam.c_str();
1059 std::string sv=searchValue.c_str();
1063 std::stringstream out;
1064 std::stringstream results;
1065 out<<"SELECT PARENT_ID FROM "<<GetTree().GetLevelDescriptor(level).GetName();
1066 out<<" WHERE "<<sp<<"='"<<sv<<"'";
1068 QUERYDB(out.str(),q);
1073 for (int fld = 0; fld < q.numFields(); fld++)
1075 results<<q.getStringField(fld);
1088 //=====================================================================
1089 bool SQLiteTreeHandler::DBInsert(Node* alien_node,
1090 UpdateSummary& summary)
1092 // std::cout << "SQLiteTreeHandler::Insert('"<<alien_node->GetLabel()
1093 // <<"')"<<std::endl;
1095 // if (!ChildrenLoaded()) DBLoadChildren(this,Node::Database);
1099 std::string parent_id;
1100 parent = DBGetOrCreateParent(alien_node,parent_id,summary);
1103 DBRecursiveGetOrCreateNode(alien_node,parent,parent_id,summary);
1106 //=====================================================================
1109 //=====================================================================
1110 Node* SQLiteTreeHandler::DBGetOrCreateParent(Node* alien_node,
1111 std::string& parent_id,
1112 UpdateSummary& summary)
1114 // std::cout << "DBGetOrCreateParent '" << alien_node->GetLabel()<<"'"
1116 // Load the patients if not already done
1117 DBLoadChildren(this,Node::Patient);
1120 Node* parent = this;
1122 // The chain of ancestors
1123 std::deque<Node*> chain;
1124 Node* cur = alien_node->GetParent();
1125 for (int type=Node::Patient;
1126 type<alien_node->GetType();++type)
1128 chain.push_front(cur);
1129 cur = cur->GetParent();
1132 // create the nodes if do not exist
1133 std::deque<Node*>::iterator i;
1134 for (i=chain.begin();i!=chain.end();++i)
1136 // std::cout << " cur = '"<<(*i)->GetLabel()<<"'"<<std::endl;
1137 // std::string cur_id = DBGetNodeId(*i,parent_id);
1138 // if (cur_id.size()==0)
1140 // Node does not exist : create it
1142 parent = DBGetOrCreateNode(*i,
1148 DBLoadChildren(parent,parent->GetType()+1);
1154 //=====================================================================
1158 //=====================================================================
1159 void SQLiteTreeHandler::DBRecursiveGetOrCreateNode(Node* alien_node,
1161 const std::string& parent_id,
1162 UpdateSummary& summary)
1164 // std::cout << "SQLiteTreeHandler::RecursiveGetOrCreateNode('"
1165 // <<alien_node->GetLabel()
1166 // <<"','"<<parent<<"','"<<parent_id<<"')"<<std::endl;
1169 // std::cout << " -- Parent = '"<<parent->GetLabel()<<"'"<<std::endl;
1172 Node* new_node = DBGetOrCreateNode(alien_node,
1177 Node::ChildrenListType::iterator i;
1178 for (i = alien_node->GetChildrenList().begin();
1179 i != alien_node->GetChildrenList().end();
1182 DBRecursiveGetOrCreateNode((*i),new_node,new_id,summary);
1185 //=====================================================================
1188 //=====================================================================
1189 Node* SQLiteTreeHandler::DBGetOrCreateNode(Node* alien_node,
1190 Node* internal_parent,
1191 std::string parent_id,
1192 std::string& node_id,
1193 UpdateSummary& summary)
1195 // std::cout << "DBGetOrCreateNode('"<<alien_node->GetLabel()<<"','"
1196 // << internal_parent << "','"<< parent_id<<"')"<<std::endl;
1197 if (internal_parent != 0)
1199 // std::cout << " -- Parent = '"<<internal_parent->GetLabel()<<"'"<<std::endl;
1201 // Node Exists ? return it
1202 // First try among children of internal parent
1203 Node* node = GetChildrenLike(internal_parent,alien_node);
1206 node_id = node->UnsafeGetFieldValue("ID");
1209 // Second : try in DB
1211 // Does not exist : Create new one
1212 node = new Node(alien_node->GetType(),this,internal_parent);
1213 node->SetChildrenLoaded(true);
1214 // Copy fields values from alien
1215 Node::FieldValueMapType::iterator i,j;
1216 for (i = node->GetFieldValueMap().begin();
1217 i != node->GetFieldValueMap().end();
1220 j = alien_node->GetFieldValueMap().find(i->first);
1221 if (j != alien_node->GetFieldValueMap().end() )
1223 i->second = j->second;
1228 if (node->GetType()!=Node::Patient)
1229 node->SetFieldValue("PARENT_ID",parent_id);
1233 BuildSQLFieldsValues(node,val);
1234 std::string insert("INSERT INTO ");
1235 insert += std::string(SQLiteTreeHandlerStructure::Table(node->GetType()))
1237 // std::cout << "** SQL = '"<<insert<<"'"<<std::endl;
1239 // std::cout << "** SQL OK"<<std::endl;
1241 // Store DB id of newly created node;
1242 long lastrow = mDB->lastRowId();
1243 std::stringstream ri;
1244 ri << mDB->lastRowId();
1246 // std::cout << "LastRowId='"<<mDB->lastRowId()<<"' vs '"<<created_id<<"'"<<std::endl;
1248 node->SetFieldValue("ID",node_id);
1249 // Insert in TypeId map
1251 ti.type = node->GetType();
1253 mTypeIdToNodeMap[ti] = node;
1254 // std::cout << "== Insert TypeId ("<<ti.type<<","<<ti.id<<")"<<std::endl;
1258 if (node->GetType()==Node::Patient) summary.added_patients++;
1259 if (node->GetType()==Node::Study) summary.added_studies++;
1260 if (node->GetType()==Node::Series) summary.added_series++;
1261 if (node->GetType()==Node::Image) summary.added_images++;
1265 //=====================================================================
1267 //=====================================================================
1268 Node* SQLiteTreeHandler::GetChildrenLike(Node* parent,
1271 Node::ChildrenListType::iterator i;
1272 for (i = parent->GetChildrenList().begin();
1273 i != parent->GetChildrenList().end();
1276 Node::Type type = alien_node->GetType();
1279 j<SQLiteTreeHandlerStructure::NbQueryFields(type);
1283 alien_node->GetFieldValue(SQLiteTreeHandlerStructure::
1284 QueryField(type,j).key ) !=
1285 (*i)->GetFieldValue(SQLiteTreeHandlerStructure::
1286 QueryField(type,j).key ) )
1299 //=====================================================================
1301 //=====================================================================
1302 std::string SQLiteTreeHandler::DBGetNodeId(Node* node,
1303 const std::string& parent_id)
1305 // std::cout << "SQLiteTreeHandler::DBGetNodeId('"<<node->GetLabel()
1306 // <<"','"<<parent_id<<"')"
1309 int type = node->GetType();
1311 std::string table = SQLiteTreeHandlerStructure::Table(type);
1312 std::string where = "WHERE ";
1314 if (type!=Node::Patient)
1316 where += "PARENT_ID='" + parent_id
1317 //node->GetFieldValue("PARENT_ID")
1321 for (int i=0;i<SQLiteTreeHandlerStructure::NbQueryFields(type);i++)
1323 where += SQLiteTreeHandlerStructure::QueryField(type,i).key + "='"
1324 + node->GetFieldValue(SQLiteTreeHandlerStructure::QueryField(type,i).key) + "' ";
1325 if (i<SQLiteTreeHandlerStructure::NbQueryFields(type)-1)
1329 std::string query = "SELECT ID FROM " + table + " " + where + ";";
1330 // std::cout << "** SQL = '"<<query<<"'"<<std::endl;
1337 // std::cout << " - Node exists " << std::endl;
1338 std::string id = q.getStringField(0);
1339 // std::cout << " id = '"<<id<<"'"<<std::endl;
1346 //=====================================================================
1350 //=====================================================================
1351 Node* SQLiteTreeHandler::GetNodeFromTypeId(Node::Type type,
1352 const std::string& id)
1354 // std::cout << "GetNodeFromTypeId("<<type<<","<<id<<")"<<std::endl;
1360 TypeIdToNodeMapType::iterator i = mTypeIdToNodeMap.find(ti);
1361 if (i == mTypeIdToNodeMap.end())
1364 std::cout << "Internal error : mTypeIdToNodeMap does not contain key"
1366 creaError("Internal error : mTypeIdToNodeMap does not contain key");
1370 // std::cout << " ** Node = "<<i->second<<std::endl;
1374 //=====================================================================
1376 //=====================================================================
1377 bool SQLiteTreeHandler::Remove(Node* node)
1380 //DBRecursiveRemoveNode(node);
1382 // std::cout << "DELETE"<<std::endl;
1383 if (node->GetParent())
1385 node->GetParent()->RemoveChildrenFromList(node);
1388 // std::cout << "DELETE OK"<<std::endl;
1393 //========================================================================
1395 //=====================================================================
1396 void SQLiteTreeHandler::DBRecursiveRemoveNode(Node* node)
1398 // std::cout << "SQLiteTreeHandler::DBRecursiveRemoveNode('"
1399 // <<node->GetLabel()<<"')"<<std::endl;
1401 std::string query = "DELETE FROM ";
1402 query += SQLiteTreeHandlerStructure::Table(node->GetType());
1403 query += " WHERE ID='"+ node->GetFieldValue("ID") + "';";
1407 Node::ChildrenListType::iterator i;
1408 for (i = node->GetChildrenList().begin();
1409 i != node->GetChildrenList().end();
1412 DBRecursiveRemoveNode((*i));
1416 //=====================================================================
1418 //=====================================================================
1419 int SQLiteTreeHandler::DBQueryNumberOfChildren(Node* node)
1421 std::string query = "SELECT COUNT (ID) FROM ";
1422 query += SQLiteTreeHandlerStructure::Table(node->GetType()+1);
1423 if (node->GetType() != Node::Database)
1425 query += " WHERE PARENT_ID='"+ node->GetFieldValue("ID")+"'";
1429 // std::cout << "**SQL = "<< query << std::endl;
1434 // std::cout << "**RES = "<< q.getIntField(0) <<std::endl;
1436 return q.getIntField(0);
1438 //=====================================================================
1446 } // namespace creaImageIO