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);
152 if(nC>0 && parent->GetLevel()>0)
154 std::stringstream out;
156 SetAttribute(parent,"NumberOfChildren",out.str());
165 if(remove&&parent->GetLevel()>0)
169 // std::cout << "DELETE OK"<<std::endl;
173 //=====================================================================
175 //=====================================================================
176 /// Sets an attribute of a Node
177 bool SQLiteTreeHandler::SetAttribute(tree::Node* n,
178 const std::string& key,
179 const std::string& value)
181 if (n==0) n=GetTree().GetTree();
182 return DBSetAttribute(n,key,value);
184 //=====================================================================
185 //=====================================================================
186 /// Sets an attribute
187 void SQLiteTreeHandler::SetAttribute(const std::string& levelDescriptor,
188 const std::string& key,
189 const std::string& value,
190 const std::string& searchParam,
191 const std::string& searchVal)
193 DBSetAttribute(levelDescriptor,key,value,searchParam, searchVal);
195 //=====================================================================
197 void SQLiteTreeHandler::DeleteTuple(std::string levelDescriptor,
198 std::string key, std::string value)
200 DBDelete(levelDescriptor,key,value);
202 //=====================================================================
216 //=====================================================================
217 // SQLite DB specific methods
218 //=====================================================================
223 //=====================================================================
224 char* format_sql(const std::string& s)
226 return sqlite3_mprintf("%q",s.c_str());
228 //=====================================================================
230 // sqlite3_exec(db, zSQL, 0, 0, 0);
231 // sqlite3_free(zSQL);
232 // char* CHAIN = format_sql(QUER); \
233 // sqlite3_free(CHAIN); \
235 //=====================================================================
236 #define QUERYDB(QUER,RES) \
239 GimmickMessage(2,"SQL query: '"<<QUER<<"'"<<std::endl); \
240 RES = mDB->execQuery(QUER.c_str()); \
242 catch (CppSQLite3Exception& e) \
244 GimmickError("SQLite query '"<<QUER<<"' : " \
245 << e.errorCode() << ":" \
246 << e.errorMessage() ); \
249 //=====================================================================
251 //=====================================================================
252 #define UPDATEDB(UP) \
255 GimmickMessage(2,"SQL update: '"<<UP<<"'"<<std::endl); \
256 mDB->execDML(UP.c_str()); \
258 catch (CppSQLite3Exception& e) \
260 GimmickError("SQLite update '"<<UP<<"' Error : " \
261 << e.errorCode() << ":" \
262 << e.errorMessage() ); \
264 //=====================================================================
267 //=====================================================================
268 bool SQLiteTreeHandler::DBOpen()
270 GimmickMessage(1,"Opening SQLite database '"<<GetFileName()
271 <<"' ... "<<std::endl);
273 if (!boost::filesystem::exists(GetFileName()))
280 mDB->open(GetFileName().c_str());
282 catch (CppSQLite3Exception& e)
284 GimmickError("Opening '"<<GetFileName()<<"' : "
285 << e.errorCode() << ":"
286 << e.errorMessage());
289 // IMPORT TREE DESCRIPTION (AND TEST DB VALIDITY)
290 if (!DBImportTreeDescription())
295 GimmickDebugMessage(1,"Opening SQLite database '"<<GetFileName()
296 <<"' ... OK"<<std::endl);
299 //=====================================================================
301 //=====================================================================
302 bool SQLiteTreeHandler::DBCreate()
304 GimmickMessage(1,"Creating SQLite database '"<<GetFileName()
305 <<"' ... "<<std::endl);
307 if (boost::filesystem::exists(GetFileName()))
309 GimmickError(GetFileName()<<"' : "
310 << "file already exists");
317 mDB->open(GetFileName().c_str());
319 catch (CppSQLite3Exception& e)
321 GimmickError(e.errorCode() << ":"
322 << e.errorMessage() <<std::endl);
330 // Create LEVELS table
331 command = "create table LEVELS\n";
332 command += "( Name text )\n";
335 // Iterate the Levels
336 for (l=0; l<GetTree().GetNumberOfLevels(); l++)
338 command = "INSERT INTO LEVELS (Name) VALUES ('";
339 command += GetTree().GetLevelDescriptor(l).GetName();
343 // Create table of level (for level>0, i.e. not Root)
346 command = "CREATE TABLE ";
347 command += GetTree().GetLevelDescriptor(l).GetName();
348 command += "\n(\nID INTEGER PRIMARY KEY";
351 command += ",\nPARENT_ID int not null";
353 SQLAppendAttributesDefinition(l,command);
356 command += ",\nconstraint FK_PARENT foreign key (PARENT_ID) references ";
357 command += GetTree().GetLevelDescriptor(l-1).GetName();
358 command += "(ID) on delete restrict on update restrict";
364 // Add Attribute 'ID' to Description
365 GetTree().GetDescriptor().Add
366 (AttributeDescriptor( "ID",
367 "Database Identifier",
369 AttributeDescriptor::PRIVATE
374 // Add Attribute 'PARENT_ID' to Description
375 GetTree().GetDescriptor().Add
376 (AttributeDescriptor( "PARENT_ID",
377 "Database Parent Identifier",
379 AttributeDescriptor::PRIVATE
385 // Create table *_ATTRIBUTES
387 command = "CREATE TABLE ";
388 command += GetTree().GetLevelDescriptor(l).GetName();
389 command += "_Attributes\n(\n";
390 command += "Key text,\n";
391 command += "Name text,\n";
392 command += "DicomGroup int,\n";
393 command += "DicomElement int,\n";
394 command += "Flags int\n";
399 // Fill the table *_ATTRIBUTES
400 LevelDescriptor::AttributeDescriptorListType::const_iterator i;
401 for (i = GetTree().GetAttributeDescriptorList(l).begin();
402 i != GetTree().GetAttributeDescriptorList(l).end();
406 std::stringstream insert;
407 insert << "INSERT INTO "
408 << GetTree().GetLevelDescriptor(l).GetName()
409 << "_Attributes (Key,Name,DicomGroup,DicomElement,Flags) "
411 << i->GetKey() << "','"
412 << i->GetName() << "',"
413 << i->GetGroup() << ","
414 << i->GetElement() << ","
415 << i->GetFlags() << ");";
416 UPDATEDB(insert.str());
421 // Initialize the root attributes
422 GetTree().InitializeAttributeMap();
423 // Insert the root in the level 0 table
424 DBInsert(GetTree().GetTree());
427 GetTree().SetChildrenLoaded(true);
428 GimmickMessage(1,"Creating SQLite database '"<<GetFileName()
429 <<"' ... OK"<<std::endl);
432 //=====================================================================
434 //=====================================================================
435 void SQLiteTreeHandler::SQLAppendAttributesDefinition(int level,
438 LevelDescriptor::AttributeDescriptorListType::const_iterator i;
439 for (i = GetTree().GetAttributeDescriptorList(level).begin();
440 i != GetTree().GetAttributeDescriptorList(level).end();
443 // if (i->second.flags==1) continue;
449 //=====================================================================
452 //=====================================================================
453 bool SQLiteTreeHandler::DBImportTreeDescription()
455 GimmickMessage(1,"Importing tree description from database ..."
458 // Test table 'LEVELS' existence
459 if ( ! mDB->tableExists("LEVELS") )
461 GimmickMessage(1,"!! ERROR : Table 'LEVELS' does not exist"
466 tree::Descriptor& desc = GetTree().GetDescriptor();
467 // clears the existing one
471 std::string query = "SELECT * FROM LEVELS";
477 std::string name = q.getStringField(0);
478 GimmickMessage(2," * Importing level '"<<name<<"'"<<std::endl);
479 desc.Add(LevelDescriptor(name));
484 for (int level = 0; level < nblevel; ++level )
486 std::string table = GetTree().GetLevelDescriptor(level).GetName();
487 table += "_Attributes";
488 // Test table 'LEVELS' existence
489 if ( ! mDB->tableExists(table.c_str()) )
491 GimmickMessage(1,"!! ERROR : Table '"<<table<<"' does not exist"
496 std::string query = "SELECT * FROM ";
501 GimmickMessage(2," * Level '"
502 <<GetTree().GetLevelDescriptor(level).GetName()
505 // Test that ID and PARENT_ID mandatory attributes exist
506 bool ID_found = false;
507 bool PARENT_ID_found = false;
508 if (level==0) ID_found = true;
509 if (level<=1) PARENT_ID_found = true;
513 std::string key(q.getStringField(0));
514 std::string name(q.getStringField(1));
515 GimmickMessage(2," - Importing attribute '"<<key<<"' '"<<name
518 (AttributeDescriptor( key, // Key
520 q.getIntField(2), // Group
521 q.getIntField(3), // Element
522 q.getIntField(4) // Flags
528 if ( key == "PARENT_ID" )
530 PARENT_ID_found = true;
535 if ( ! (ID_found || PARENT_ID_found ) )
537 GimmickMessage(1,"!! ERROR : Table '"<<table
538 <<"' does not contain mandatory attribute ID or PARENT_ID"
546 // Create the attributes table for Root (i.e. Tree)
547 LevelDescriptor::AttributeDescriptorListType::const_iterator a;
548 for (a = GetTree().GetAttributeDescriptorList(0).begin();
549 a!= GetTree().GetAttributeDescriptorList(0).end();
554 AttributeMapType::const_iterator i = attr.find(a->GetKey());
555 if ( i != attr.end() )
560 GetTree().UnsafeSetAttribute( a->GetKey(), "" );
563 // Reading Root attributes
565 query = "SELECT * FROM ";
566 query += GetTree().GetLevelDescriptor(0).GetName();
569 for (int fld = 0; fld < q.numFields(); fld++)
571 GetTree().UnsafeSetAttribute(q.fieldName(fld),
572 q.getStringField(fld));
575 GimmickMessage(1,"Importing tree description from database ... OK"
579 //=====================================================================
597 //========================================================================
599 std::string& SQLformat(std::string& str)
601 // quote must be doubled
602 boost::algorithm::replace_all(str,"'","''");
603 // Found strange strings which contained NULL char INSIDE string
604 int i,size=str.size();
609 str = str.substr(0,i);
616 //========================================================================
618 //=====================================================================
619 void SQLiteTreeHandler::SQLAppendAttributesValues(tree::Node* n,
622 GimmickMessage(4,"SQLAppendAttributesValues"<<std::endl);
624 std::string values="";
625 tree::Node::AttributeMapType::iterator i;
626 for (i = n->GetAttributeMap().begin();
627 i != n->GetAttributeMap().end();
634 // std::cout << "("<<i->first<<","<<i->second<<")"<<std::endl;
635 atts += "'" + i->first + "'";
636 values += "'" + SQLformat(i->second) + "'";
639 GimmickMessage(4,"'"<<i->first<<"' = '"<<i->second<<"'"<<std::endl);
641 atts[atts.size()-1]=' ';
642 values[values.size()-1]=' ';
644 str = "("+atts+") VALUES ("+values+")";
645 GimmickMessage(4,"Result = '"<<str<<"'"<<std::endl);
647 //=====================================================================
649 //=====================================================================
650 tree::Node* SQLiteTreeHandler::DBGetParent( const AttributeMapType& attr)
652 Node* parent = GetTree().GetTree();
657 // Load the children of the current parent
658 DBLoadChildren(parent);
659 // Iterate the children
660 tree::Node::ChildrenListType::const_iterator i;
661 for (i = parent->GetChildrenList().begin();
662 i!= parent->GetChildrenList().end();
665 if ( (*i)->Matches( attr ) )
676 //=====================================================================
679 //=====================================================================
680 int SQLiteTreeHandler::DBLoadChildren(tree::Node* node,
683 if (node->GetLevel()+1 >= node->GetTree()->GetNumberOfLevels() )
686 GimmickMessage(2,"Loading children of '"<<node->GetLabel()
690 // If children loaded we do not have to do it but we need to recurse
691 // in order to load the children's children if necessary, and so on...
692 if (node->GetChildrenLoaded())
694 // Iterate the children
696 tree::Node::ChildrenListType::iterator i;
697 for (i = node->GetChildrenList().begin();
698 i!= node->GetChildrenList().end();
701 nbloaded += DBLoadChildren(*i,numberoflevels-1);
703 node->SetChildrenLoaded(true);
709 /// If children not loaded : do it and recurse
712 int level = node->GetLevel();
713 std::string query = "SELECT * FROM ";
715 query += GetTree().GetLevelDescriptor(level+1).GetName();
718 query += " WHERE PARENT_ID='" + node->UnsafeGetAttribute("ID")
728 // std::cout << "DBLoadCh : creating node level "<<level+1<<std::endl;
731 Node* n = new Node(node);
732 for (int fld = 0; fld < q.numFields(); fld++)
734 n->UnsafeSetAttribute(q.fieldName(fld),q.getStringField(fld));
740 ti.id = n->GetFieldValue("ID");
741 mTypeIdToNodeMap[ti] = n;
744 if ( numberoflevels != 1 )
747 nbloaded += DBLoadChildren(n, numberoflevels-1);
754 node->SetChildrenLoaded(true);
761 //=====================================================================
763 //======================================================================
764 void SQLiteTreeHandler::DBInsert(tree::Node* n)
766 GimmickMessage(2,"Inserting in DB '"<<n->GetLabel()
769 SQLAppendAttributesValues(n,val);
770 std::string insert("INSERT INTO ");
771 insert += GetTree().GetLevelDescriptor(n->GetLevel()).GetName();
772 insert += " " + val + ";";
776 // Store DB id of newly created node;
777 long lastrow = mDB->lastRowId();
778 std::stringstream ri;
779 ri << mDB->lastRowId();
780 n->SetAttribute("ID",ri.str());
782 //======================================================================
784 //======================================================================
785 /// Graft the branch defined by the attributes to the parent
786 void SQLiteTreeHandler::DBGraftToParent( tree::Node* parent,
787 const AttributeMapType& attr)
789 // std::cout <<"Grafting to parent '"<<parent->GetLabel()
792 for (int level = parent->GetLevel()+1;
793 level < GetTree().GetNumberOfLevels();
797 tree::Node* child = new tree::Node(parent,attr);
798 child->SetChildrenLoaded(true);
801 int nc = GetNumberOfChildren(parent)+1;
803 // std::cout<<"Number of children "<<parent->GetNumberOfChildren()<<std::endl;
804 std::stringstream out;
806 SetAttribute(parent,"NumberOfChildren",out.str());
809 // Set PARENT_ID if necessary
810 if ( parent->GetLevel()>0 )
811 child->SetAttribute("PARENT_ID",parent->GetAttribute("ID"));
817 SQLAppendAttributesValues(child,val);
818 std::string insert("INSERT INTO ");
819 insert += GetTree().GetLevelDescriptor(child->GetLevel()).GetName();
820 insert += " " + val + ";";
823 // Store DB id of newly created node;
824 long lastrow = mDB->lastRowId();
825 std::stringstream ri;
826 ri << mDB->lastRowId();
827 child->SetAttribute("ID",ri.str());
833 // Insert in TypeId map
835 ti.type = node->GetType();
837 mTypeIdToNodeMap[ti] = node;
838 // std::cout << "== Insert TypeId ("<<ti.type<<","<<ti.id<<")"<<std::endl;
842 if (node->GetType()==Node::Patient) summary.added_patients++;
843 if (node->GetType()==Node::Study) summary.added_studies++;
844 if (node->GetType()==Node::Series) summary.added_series++;
845 if (node->GetType()==Node::Image) summary.added_images++;
849 //======================================================================
852 //=====================================================================
853 /// Sets an attribute of a Node
854 bool SQLiteTreeHandler::DBSetAttribute(tree::Node* n,
855 const std::string& key,
856 const std::string& value)
858 GimmickMessage(3,"Setting Attribute of '"<<n->GetLabel()<<
859 "' "<<key<<"='"<<value<<"'"<<std::endl);
861 n->SetAttribute(key,value);
862 std::string sql = "UPDATE ";
863 sql += GetTree().GetLevelDescriptor(n->GetLevel()).GetName();
868 sql += "' WHERE ID = '";
869 sql += n->GetAttribute("ID");
871 // sql += " LIMIT 1";
875 //=====================================================================
876 /// Sets an attribute of a Node
877 void SQLiteTreeHandler::DBSetAttribute(const std::string& levelDescriptor,
878 const std::string& key,
879 const std::string& value,
880 const std::string& searchParam,
881 const std::string& searchVal)
884 std::string sql = "UPDATE ";
885 sql += levelDescriptor;
895 std::cout<<sql<<std::endl;
898 //=====================================================================
899 void SQLiteTreeHandler::DBRecursiveRemoveNode(Node* node)
902 std::string query = "DELETE FROM ";
904 query += GetTree().GetLevelDescriptor(node->GetLevel()).GetName();
905 query += " WHERE ID='"+ node->GetAttribute("ID") + "';";
908 GimmickDebugMessage(2,
910 <<node->GetLabel()<<"' with ID '"
911 <<node->GetAttribute("ID")
912 <<"' in level "<< GetTree().GetLevelDescriptor(node->GetLevel()).GetName()
916 if(node->GetNumberOfChildren()!=0)
918 Node::ChildrenListType::iterator i;
919 for (i = node->GetChildrenList().begin();
920 i != node->GetChildrenList().end();
923 DBRecursiveRemoveNode((*i));
926 else if(node->GetLevel()<GetTree().GetNumberOfLevels()-1)
928 DBRecursiveRemoveNode(node->GetLevel()+1,node->GetAttribute("ID"));
932 //=====================================================================
933 void SQLiteTreeHandler::DBRecursiveRemoveNode(int level, std::string parentId)
935 std::stringstream out;
936 std::stringstream result;
937 out<<"SELECT ID FROM "<<GetTree().GetLevelDescriptor(level).GetName()<<" WHERE PARENT_ID='"<<parentId<<"'";
940 QUERYDB(out.str(),q);
944 for (int fld = 0; fld < q.numFields(); fld++)
946 result<<q.getStringField(fld)<<"#";
950 std::string res=result.str();
953 while(fin<res.size()-1)
955 fin=res.find('#',ini);
956 DBDelete(GetTree().GetLevelDescriptor(level).GetName(),"ID",res.substr(ini,fin-ini));
957 if(level<GetTree().GetNumberOfLevels()-1)
959 DBRecursiveRemoveNode(level+1,res.substr(ini,fin-ini));
967 //=====================================================================
968 void SQLiteTreeHandler::DBDelete(std::string levelDescriptor, std::string key, std::string value)
971 std::stringstream query;
972 query<<"DELETE FROM "<<levelDescriptor<<" WHERE "<<key<<"='"<<value<<"';";
974 UPDATEDB(query.str());
975 GimmickDebugMessage(2," Deleting: Query: "<<query.str()<<std::endl);
979 //=====================================================================
980 void SQLiteTreeHandler::GetAttribute(std::string levelDescriptor,
981 std::string searchParam,
982 std::string searchVal,
986 std::stringstream out;
987 std::stringstream results;
988 out<<"SELECT "<<key<<" FROM "<<levelDescriptor;
991 out<<" WHERE "<<searchParam<<"='"<<searchVal<<"'";
995 QUERYDB(out.str(),q);
1000 for (int fld = 0; fld < q.numFields(); fld++)
1002 results<<q.getStringField(fld);
1010 result=results.str();
1013 //=====================================================================
1014 unsigned int SQLiteTreeHandler::GetNumberOfChildren(tree::Node* n)
1018 int level = n->GetLevel();
1020 if(level<GetTree().GetNumberOfLevels()&& level>0)
1022 std::string query = "SELECT NumberOfChildren FROM ";
1023 query += GetTree().GetLevelDescriptor(level).GetName();
1026 query += " WHERE ID='" + n->UnsafeGetAttribute("ID")
1035 for (int fld = 0; fld < q.numFields(); fld++)
1037 nb=q.getIntField(fld);
1051 //=====================================================================
1052 void SQLiteTreeHandler::GetTopLevelNodeId(const std::string& searchParam, const std::string& searchValue, std::string& parent_id)
1054 int level=GetTree().GetNumberOfLevels()-1;
1055 std::string sp=searchParam.c_str();
1056 std::string sv=searchValue.c_str();
1060 std::stringstream out;
1061 std::stringstream results;
1062 out<<"SELECT PARENT_ID FROM "<<GetTree().GetLevelDescriptor(level).GetName();
1063 out<<" WHERE "<<sp<<"='"<<sv<<"'";
1065 QUERYDB(out.str(),q);
1070 for (int fld = 0; fld < q.numFields(); fld++)
1072 results<<q.getStringField(fld);
1084 //=====================================================================
1085 void SQLiteTreeHandler::RemoveEntries(const std::string i_table,
1086 const std::string i_attribute,
1087 const std::string i_operand,
1088 const std::string i_val)
1090 std::stringstream query;
1091 query<<"DELETE FROM "<<i_table<<" WHERE "<<i_attribute<<" "<<i_operand<<" '"<<i_val<<"'";
1092 UPDATEDB(query.str());
1095 //=====================================================================
1096 void SQLiteTreeHandler::BeginTransaction()
1098 std::stringstream out;
1099 out<<"begin transaction;";
1100 UPDATEDB(out.str());
1103 //=====================================================================
1104 void SQLiteTreeHandler::EndTransaction()
1106 std::stringstream out;
1107 out<<"commit transaction;";
1108 UPDATEDB(out.str());
1112 //=====================================================================
1113 bool SQLiteTreeHandler::DBInsert(Node* alien_node,
1114 UpdateSummary& summary)
1116 // std::cout << "SQLiteTreeHandler::Insert('"<<alien_node->GetLabel()
1117 // <<"')"<<std::endl;
1119 // if (!ChildrenLoaded()) DBLoadChildren(this,Node::Database);
1123 std::string parent_id;
1124 parent = DBGetOrCreateParent(alien_node,parent_id,summary);
1127 DBRecursiveGetOrCreateNode(alien_node,parent,parent_id,summary);
1130 //=====================================================================
1133 //=====================================================================
1134 Node* SQLiteTreeHandler::DBGetOrCreateParent(Node* alien_node,
1135 std::string& parent_id,
1136 UpdateSummary& summary)
1138 // std::cout << "DBGetOrCreateParent '" << alien_node->GetLabel()<<"'"
1140 // Load the patients if not already done
1141 DBLoadChildren(this,Node::Patient);
1144 Node* parent = this;
1146 // The chain of ancestors
1147 std::deque<Node*> chain;
1148 Node* cur = alien_node->GetParent();
1149 for (int type=Node::Patient;
1150 type<alien_node->GetType();++type)
1152 chain.push_front(cur);
1153 cur = cur->GetParent();
1156 // create the nodes if do not exist
1157 std::deque<Node*>::iterator i;
1158 for (i=chain.begin();i!=chain.end();++i)
1160 // std::cout << " cur = '"<<(*i)->GetLabel()<<"'"<<std::endl;
1161 // std::string cur_id = DBGetNodeId(*i,parent_id);
1162 // if (cur_id.size()==0)
1164 // Node does not exist : create it
1166 parent = DBGetOrCreateNode(*i,
1172 DBLoadChildren(parent,parent->GetType()+1);
1178 //=====================================================================
1182 //=====================================================================
1183 void SQLiteTreeHandler::DBRecursiveGetOrCreateNode(Node* alien_node,
1185 const std::string& parent_id,
1186 UpdateSummary& summary)
1188 // std::cout << "SQLiteTreeHandler::RecursiveGetOrCreateNode('"
1189 // <<alien_node->GetLabel()
1190 // <<"','"<<parent<<"','"<<parent_id<<"')"<<std::endl;
1193 // std::cout << " -- Parent = '"<<parent->GetLabel()<<"'"<<std::endl;
1196 Node* new_node = DBGetOrCreateNode(alien_node,
1201 Node::ChildrenListType::iterator i;
1202 for (i = alien_node->GetChildrenList().begin();
1203 i != alien_node->GetChildrenList().end();
1206 DBRecursiveGetOrCreateNode((*i),new_node,new_id,summary);
1209 //=====================================================================
1212 //=====================================================================
1213 Node* SQLiteTreeHandler::DBGetOrCreateNode(Node* alien_node,
1214 Node* internal_parent,
1215 std::string parent_id,
1216 std::string& node_id,
1217 UpdateSummary& summary)
1219 // std::cout << "DBGetOrCreateNode('"<<alien_node->GetLabel()<<"','"
1220 // << internal_parent << "','"<< parent_id<<"')"<<std::endl;
1221 if (internal_parent != 0)
1223 // std::cout << " -- Parent = '"<<internal_parent->GetLabel()<<"'"<<std::endl;
1225 // Node Exists ? return it
1226 // First try among children of internal parent
1227 Node* node = GetChildrenLike(internal_parent,alien_node);
1230 node_id = node->UnsafeGetFieldValue("ID");
1233 // Second : try in DB
1235 // Does not exist : Create new one
1236 node = new Node(alien_node->GetType(),this,internal_parent);
1237 node->SetChildrenLoaded(true);
1238 // Copy fields values from alien
1239 Node::FieldValueMapType::iterator i,j;
1240 for (i = node->GetFieldValueMap().begin();
1241 i != node->GetFieldValueMap().end();
1244 j = alien_node->GetFieldValueMap().find(i->first);
1245 if (j != alien_node->GetFieldValueMap().end() )
1247 i->second = j->second;
1252 if (node->GetType()!=Node::Patient)
1253 node->SetFieldValue("PARENT_ID",parent_id);
1257 BuildSQLFieldsValues(node,val);
1258 std::string insert("INSERT INTO ");
1259 insert += std::string(SQLiteTreeHandlerStructure::Table(node->GetType()))
1261 // std::cout << "** SQL = '"<<insert<<"'"<<std::endl;
1263 // std::cout << "** SQL OK"<<std::endl;
1265 // Store DB id of newly created node;
1266 long lastrow = mDB->lastRowId();
1267 std::stringstream ri;
1268 ri << mDB->lastRowId();
1270 // std::cout << "LastRowId='"<<mDB->lastRowId()<<"' vs '"<<created_id<<"'"<<std::endl;
1272 node->SetFieldValue("ID",node_id);
1273 // Insert in TypeId map
1275 ti.type = node->GetType();
1277 mTypeIdToNodeMap[ti] = node;
1278 // std::cout << "== Insert TypeId ("<<ti.type<<","<<ti.id<<")"<<std::endl;
1282 if (node->GetType()==Node::Patient) summary.added_patients++;
1283 if (node->GetType()==Node::Study) summary.added_studies++;
1284 if (node->GetType()==Node::Series) summary.added_series++;
1285 if (node->GetType()==Node::Image) summary.added_images++;
1289 //=====================================================================
1291 //=====================================================================
1292 Node* SQLiteTreeHandler::GetChildrenLike(Node* parent,
1295 Node::ChildrenListType::iterator i;
1296 for (i = parent->GetChildrenList().begin();
1297 i != parent->GetChildrenList().end();
1300 Node::Type type = alien_node->GetType();
1303 j<SQLiteTreeHandlerStructure::NbQueryFields(type);
1307 alien_node->GetFieldValue(SQLiteTreeHandlerStructure::
1308 QueryField(type,j).key ) !=
1309 (*i)->GetFieldValue(SQLiteTreeHandlerStructure::
1310 QueryField(type,j).key ) )
1323 //=====================================================================
1325 //=====================================================================
1326 std::string SQLiteTreeHandler::DBGetNodeId(Node* node,
1327 const std::string& parent_id)
1329 // std::cout << "SQLiteTreeHandler::DBGetNodeId('"<<node->GetLabel()
1330 // <<"','"<<parent_id<<"')"
1333 int type = node->GetType();
1335 std::string table = SQLiteTreeHandlerStructure::Table(type);
1336 std::string where = "WHERE ";
1338 if (type!=Node::Patient)
1340 where += "PARENT_ID='" + parent_id
1341 //node->GetFieldValue("PARENT_ID")
1345 for (int i=0;i<SQLiteTreeHandlerStructure::NbQueryFields(type);i++)
1347 where += SQLiteTreeHandlerStructure::QueryField(type,i).key + "='"
1348 + node->GetFieldValue(SQLiteTreeHandlerStructure::QueryField(type,i).key) + "' ";
1349 if (i<SQLiteTreeHandlerStructure::NbQueryFields(type)-1)
1353 std::string query = "SELECT ID FROM " + table + " " + where + ";";
1354 // std::cout << "** SQL = '"<<query<<"'"<<std::endl;
1361 // std::cout << " - Node exists " << std::endl;
1362 std::string id = q.getStringField(0);
1363 // std::cout << " id = '"<<id<<"'"<<std::endl;
1370 //=====================================================================
1374 //=====================================================================
1375 Node* SQLiteTreeHandler::GetNodeFromTypeId(Node::Type type,
1376 const std::string& id)
1378 // std::cout << "GetNodeFromTypeId("<<type<<","<<id<<")"<<std::endl;
1384 TypeIdToNodeMapType::iterator i = mTypeIdToNodeMap.find(ti);
1385 if (i == mTypeIdToNodeMap.end())
1388 std::cout << "Internal error : mTypeIdToNodeMap does not contain key"
1390 creaError("Internal error : mTypeIdToNodeMap does not contain key");
1394 // std::cout << " ** Node = "<<i->second<<std::endl;
1398 //=====================================================================
1400 //=====================================================================
1401 bool SQLiteTreeHandler::Remove(Node* node)
1404 //DBRecursiveRemoveNode(node);
1406 // std::cout << "DELETE"<<std::endl;
1407 if (node->GetParent())
1409 node->GetParent()->RemoveChildrenFromList(node);
1412 // std::cout << "DELETE OK"<<std::endl;
1417 //========================================================================
1419 //=====================================================================
1420 void SQLiteTreeHandler::DBRecursiveRemoveNode(Node* node)
1422 // std::cout << "SQLiteTreeHandler::DBRecursiveRemoveNode('"
1423 // <<node->GetLabel()<<"')"<<std::endl;
1425 std::string query = "DELETE FROM ";
1426 query += SQLiteTreeHandlerStructure::Table(node->GetType());
1427 query += " WHERE ID='"+ node->GetFieldValue("ID") + "';";
1431 Node::ChildrenListType::iterator i;
1432 for (i = node->GetChildrenList().begin();
1433 i != node->GetChildrenList().end();
1436 DBRecursiveRemoveNode((*i));
1440 //=====================================================================
1442 //=====================================================================
1443 int SQLiteTreeHandler::DBQueryNumberOfChildren(Node* node)
1445 std::string query = "SELECT COUNT (ID) FROM ";
1446 query += SQLiteTreeHandlerStructure::Table(node->GetType()+1);
1447 if (node->GetType() != Node::Database)
1449 query += " WHERE PARENT_ID='"+ node->GetFieldValue("ID")+"'";
1453 // std::cout << "**SQL = "<< query << std::endl;
1458 // std::cout << "**RES = "<< q.getIntField(0) <<std::endl;
1460 return q.getIntField(0);
1462 //=====================================================================
1470 } // namespace creaImageIO