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>
16 //#include <icons/close.xpm>
21 #include <boost/filesystem.hpp>
22 #include <boost/algorithm/string/replace.hpp>
29 //=============================================================
30 SQLiteTreeHandler::SQLiteTreeHandler(const std::string& filename)
33 mDB = new CppSQLite3DB;
35 GimmickMessage(1,"SQLite version : "
36 <<std::string(mDB->SQLiteVersion())<< std::endl);
38 //=============================================================
40 //=============================================================
41 SQLiteTreeHandler::~SQLiteTreeHandler()
45 //=============================================================
48 //=============================================================
49 // void SQLiteTreeHandler::Print() const
52 std::cout << "-> '"<<GetName()<< "' - '"
53 << GetFileName()<<"'"<<std::endl;
54 ChildrenListType::const_iterator i;
55 for (i=GetChildrenList().begin(); i!=GetChildrenList().end(); i++)
61 //=============================================================
63 //=====================================================================
65 bool SQLiteTreeHandler::LocationIsValid()
71 //=====================================================================
74 //=====================================================================
75 bool SQLiteTreeHandler::Open(bool writable)
77 // std::cout << "***> SQLiteTreeHandler::Open('"<<GetFileName()<<"')"<<std::endl;
78 SetWritable(writable);
82 //=====================================================================
83 bool SQLiteTreeHandler::Create(bool writable)
85 // std::cout << "***> SQLiteTreeHandler::New('"<<GetFileName()<<"')"<<std::endl;
86 SetWritable(writable);
89 //=====================================================================
92 //=====================================================================
93 bool SQLiteTreeHandler::Close()
97 //=====================================================================
100 //=====================================================================
101 bool SQLiteTreeHandler::Destroy()
106 //=====================================================================
108 //=====================================================================
109 int SQLiteTreeHandler::LoadChildren(tree::Node* parent, int maxlevel)
111 if (parent==0) parent = GetTree().GetTree();
112 return DBLoadChildren(parent,maxlevel);
114 //=====================================================================
119 //=====================================================================
120 void SQLiteTreeHandler::UnLoad(tree::Node* n)
123 //=====================================================================
125 //=====================================================================
126 int SQLiteTreeHandler::AddBranch( const AttributeMapType& attr )
128 tree::Node* parent = DBGetParent(attr);
129 DBGraftToParent(parent,attr);
130 return (parent->GetLevel()+1);
133 //=====================================================================
136 //=====================================================================
137 bool SQLiteTreeHandler::Remove(tree::Node* node)
139 DBRecursiveRemoveNode(node);
141 // std::cout << "DELETE"<<std::endl;
143 tree::Node* parent=node->GetParent();
146 int nC = parent->RemoveChildrenFromList(node);
147 if(nC>0 && parent->GetLevel()>0)
149 std::stringstream out;
151 SetAttribute(parent,"NumberOfChildren",out.str());
160 if(remove&&parent->GetLevel()>0)
164 // std::cout << "DELETE OK"<<std::endl;
168 //=====================================================================
170 //=====================================================================
171 /// Sets an attribute of a Node
172 bool SQLiteTreeHandler::SetAttribute(tree::Node* n,
173 const std::string& key,
174 const std::string& value)
176 if (n==0) n=GetTree().GetTree();
177 return DBSetAttribute(n,key,value);
179 //=====================================================================
180 //=====================================================================
181 /// Sets an attribute
182 void SQLiteTreeHandler::SetAttribute(const std::string& levelDescriptor,
183 const std::string& key,
184 const std::string& value,
185 const std::string& searchParam,
186 const std::string& searchVal)
188 DBSetAttribute(levelDescriptor,key,value,searchParam, searchVal);
190 //=====================================================================
192 void SQLiteTreeHandler::DeleteTuple(std::string levelDescriptor,
193 std::string key, std::string value)
195 DBDelete(levelDescriptor,key,value);
197 //=====================================================================
211 //=====================================================================
212 // SQLite DB specific methods
213 //=====================================================================
218 //=====================================================================
219 char* format_sql(const std::string& s)
221 return sqlite3_mprintf("%q",s.c_str());
223 //=====================================================================
225 // sqlite3_exec(db, zSQL, 0, 0, 0);
226 // sqlite3_free(zSQL);
227 // char* CHAIN = format_sql(QUER); \
228 // sqlite3_free(CHAIN); \
230 //=====================================================================
231 #define QUERYDB(QUER,RES) \
234 GimmickMessage(2,"SQL query: '"<<QUER<<"'"<<std::endl); \
235 RES = mDB->execQuery(QUER.c_str()); \
237 catch (CppSQLite3Exception& e) \
239 GimmickError("SQLite query '"<<QUER<<"' : " \
240 << e.errorCode() << ":" \
241 << e.errorMessage() ); \
244 //=====================================================================
246 //=====================================================================
247 #define UPDATEDB(UP) \
250 GimmickMessage(2,"SQL update: '"<<UP<<"'"<<std::endl); \
251 mDB->execDML(UP.c_str()); \
253 catch (CppSQLite3Exception& e) \
255 GimmickError("SQLite update '"<<UP<<"' Error : " \
256 << e.errorCode() << ":" \
257 << e.errorMessage() ); \
259 //=====================================================================
262 //=====================================================================
263 bool SQLiteTreeHandler::DBOpen()
265 GimmickMessage(1,"Opening SQLite database '"<<GetFileName()
266 <<"' ... "<<std::endl);
268 if (!boost::filesystem::exists(GetFileName()))
275 mDB->open(GetFileName().c_str());
277 catch (CppSQLite3Exception& e)
279 GimmickError("Opening '"<<GetFileName()<<"' : "
280 << e.errorCode() << ":"
281 << e.errorMessage());
284 // IMPORT TREE DESCRIPTION (AND TEST DB VALIDITY)
285 if (!DBImportTreeDescription())
290 GimmickDebugMessage(1,"Opening SQLite database '"<<GetFileName()
291 <<"' ... OK"<<std::endl);
294 //=====================================================================
296 //=====================================================================
297 bool SQLiteTreeHandler::DBCreate()
299 GimmickMessage(1,"Creating SQLite database '"<<GetFileName()
300 <<"' ... "<<std::endl);
302 if (boost::filesystem::exists(GetFileName()))
304 GimmickError(GetFileName()<<"' : "
305 << "file already exists");
312 mDB->open(GetFileName().c_str());
314 catch (CppSQLite3Exception& e)
316 GimmickError(e.errorCode() << ":"
317 << e.errorMessage() <<std::endl);
325 // Create LEVELS table
326 command = "create table LEVELS\n";
327 command += "( Name text )\n";
330 // Iterate the Levels
331 for (l=0; l<GetTree().GetNumberOfLevels(); l++)
333 command = "INSERT INTO LEVELS (Name) VALUES ('";
334 command += GetTree().GetLevelDescriptor(l).GetName();
338 // Create table of level (for level>0, i.e. not Root)
341 command = "CREATE TABLE ";
342 command += GetTree().GetLevelDescriptor(l).GetName();
343 command += "\n(\nID INTEGER PRIMARY KEY";
346 command += ",\nPARENT_ID int not null";
348 SQLAppendAttributesDefinition(l,command);
351 command += ",\nconstraint FK_PARENT foreign key (PARENT_ID) references ";
352 command += GetTree().GetLevelDescriptor(l-1).GetName();
353 command += "(ID) on delete restrict on update restrict";
359 // Add Attribute 'ID' to Description
360 GetTree().GetDescriptor().Add
361 (AttributeDescriptor( "ID",
362 "Database Identifier",
364 AttributeDescriptor::PRIVATE
369 // Add Attribute 'PARENT_ID' to Description
370 GetTree().GetDescriptor().Add
371 (AttributeDescriptor( "PARENT_ID",
372 "Database Parent Identifier",
374 AttributeDescriptor::PRIVATE
380 // Create table *_ATTRIBUTES
382 command = "CREATE TABLE ";
383 command += GetTree().GetLevelDescriptor(l).GetName();
384 command += "_Attributes\n(\n";
385 command += "Key text,\n";
386 command += "Name text,\n";
387 command += "DicomGroup int,\n";
388 command += "DicomElement int,\n";
389 command += "Flags int\n";
394 // Fill the table *_ATTRIBUTES
395 LevelDescriptor::AttributeDescriptorListType::const_iterator i;
396 for (i = GetTree().GetAttributeDescriptorList(l).begin();
397 i != GetTree().GetAttributeDescriptorList(l).end();
401 std::stringstream insert;
402 insert << "INSERT INTO "
403 << GetTree().GetLevelDescriptor(l).GetName()
404 << "_Attributes (Key,Name,DicomGroup,DicomElement,Flags) "
406 << i->GetKey() << "','"
407 << i->GetName() << "',"
408 << i->GetGroup() << ","
409 << i->GetElement() << ","
410 << i->GetFlags() << ");";
411 UPDATEDB(insert.str());
416 // Initialize the root attributes
417 GetTree().InitializeAttributeMap();
418 // Insert the root in the level 0 table
419 DBInsert(GetTree().GetTree());
422 GetTree().SetChildrenLoaded(true);
423 GimmickMessage(1,"Creating SQLite database '"<<GetFileName()
424 <<"' ... OK"<<std::endl);
427 //=====================================================================
429 //=====================================================================
430 void SQLiteTreeHandler::SQLAppendAttributesDefinition(int level,
433 LevelDescriptor::AttributeDescriptorListType::const_iterator i;
434 for (i = GetTree().GetAttributeDescriptorList(level).begin();
435 i != GetTree().GetAttributeDescriptorList(level).end();
438 // if (i->second.flags==1) continue;
444 //=====================================================================
447 //=====================================================================
448 bool SQLiteTreeHandler::DBImportTreeDescription()
450 GimmickMessage(1,"Importing tree description from database ..."
453 // Test table 'LEVELS' existence
454 if ( ! mDB->tableExists("LEVELS") )
456 GimmickMessage(1,"!! ERROR : Table 'LEVELS' does not exist"
461 tree::Descriptor& desc = GetTree().GetDescriptor();
462 // clears the existing one
466 std::string query = "SELECT * FROM LEVELS";
472 std::string name = q.getStringField(0);
473 GimmickMessage(2," * Importing level '"<<name<<"'"<<std::endl);
474 desc.Add(LevelDescriptor(name));
479 for (int level = 0; level < nblevel; ++level )
481 std::string table = GetTree().GetLevelDescriptor(level).GetName();
482 table += "_Attributes";
483 // Test table 'LEVELS' existence
484 if ( ! mDB->tableExists(table.c_str()) )
486 GimmickMessage(1,"!! ERROR : Table '"<<table<<"' does not exist"
491 std::string query = "SELECT * FROM ";
496 GimmickMessage(2," * Level '"
497 <<GetTree().GetLevelDescriptor(level).GetName()
500 // Test that ID and PARENT_ID mandatory attributes exist
501 bool ID_found = false;
502 bool PARENT_ID_found = false;
503 if (level==0) ID_found = true;
504 if (level<=1) PARENT_ID_found = true;
508 std::string key(q.getStringField(0));
509 std::string name(q.getStringField(1));
510 GimmickMessage(2," - Importing attribute '"<<key<<"' '"<<name
513 (AttributeDescriptor( key, // Key
515 q.getIntField(2), // Group
516 q.getIntField(3), // Element
517 q.getIntField(4) // Flags
523 if ( key == "PARENT_ID" )
525 PARENT_ID_found = true;
530 if ( ! (ID_found || PARENT_ID_found ) )
532 GimmickMessage(1,"!! ERROR : Table '"<<table
533 <<"' does not contain mandatory attribute ID or PARENT_ID"
541 // Create the attributes table for Root (i.e. Tree)
542 LevelDescriptor::AttributeDescriptorListType::const_iterator a;
543 for (a = GetTree().GetAttributeDescriptorList(0).begin();
544 a!= GetTree().GetAttributeDescriptorList(0).end();
549 AttributeMapType::const_iterator i = attr.find(a->GetKey());
550 if ( i != attr.end() )
555 GetTree().UnsafeSetAttribute( a->GetKey(), "" );
558 // Reading Root attributes
560 query = "SELECT * FROM ";
561 query += GetTree().GetLevelDescriptor(0).GetName();
564 for (int fld = 0; fld < q.numFields(); fld++)
566 GetTree().UnsafeSetAttribute(q.fieldName(fld),
567 q.getStringField(fld));
570 GimmickMessage(1,"Importing tree description from database ... OK"
574 //=====================================================================
592 //========================================================================
594 std::string& SQLformat(std::string& str)
596 // quote must be doubled
597 boost::algorithm::replace_all(str,"'","''");
598 // Found strange strings which contained NULL char INSIDE string
599 int i,size=str.size();
604 str = str.substr(0,i);
611 //========================================================================
613 //=====================================================================
614 void SQLiteTreeHandler::SQLAppendAttributesValues(tree::Node* n,
617 GimmickMessage(4,"SQLAppendAttributesValues"<<std::endl);
619 std::string values="";
620 tree::Node::AttributeMapType::iterator i;
621 for (i = n->GetAttributeMap().begin();
622 i != n->GetAttributeMap().end();
629 // std::cout << "("<<i->first<<","<<i->second<<")"<<std::endl;
630 atts += "'" + i->first + "'";
631 values += "'" + SQLformat(i->second) + "'";
634 GimmickMessage(4,"'"<<i->first<<"' = '"<<i->second<<"'"<<std::endl);
636 atts[atts.size()-1]=' ';
637 values[values.size()-1]=' ';
639 str = "("+atts+") VALUES ("+values+")";
640 GimmickMessage(4,"Result = '"<<str<<"'"<<std::endl);
642 //=====================================================================
644 //=====================================================================
645 tree::Node* SQLiteTreeHandler::DBGetParent( const AttributeMapType& attr)
647 Node* parent = GetTree().GetTree();
652 // Load the children of the current parent
653 DBLoadChildren(parent);
654 // Iterate the children
655 tree::Node::ChildrenListType::const_iterator i;
656 for (i = parent->GetChildrenList().begin();
657 i!= parent->GetChildrenList().end();
660 if ( (*i)->Matches( attr ) )
671 //=====================================================================
674 //=====================================================================
675 int SQLiteTreeHandler::DBLoadChildren(tree::Node* node,
678 if (node->GetLevel()+1 >= node->GetTree()->GetNumberOfLevels() )
681 GimmickMessage(2,"Loading children of '"<<node->GetLabel()
685 // If children loaded we do not have to do it but we need to recurse
686 // in order to load the children's children if necessary, and so on...
687 if (node->GetChildrenLoaded())
689 // Iterate the children
691 tree::Node::ChildrenListType::iterator i;
692 for (i = node->GetChildrenList().begin();
693 i!= node->GetChildrenList().end();
696 nbloaded += DBLoadChildren(*i,numberoflevels-1);
698 node->SetChildrenLoaded(true);
704 /// If children not loaded : do it and recurse
707 int level = node->GetLevel();
708 std::string query = "SELECT * FROM ";
710 query += GetTree().GetLevelDescriptor(level+1).GetName();
713 query += " WHERE PARENT_ID='" + node->UnsafeGetAttribute("ID")
723 // std::cout << "DBLoadCh : creating node level "<<level+1<<std::endl;
726 Node* n = new Node(node);
727 for (int fld = 0; fld < q.numFields(); fld++)
729 n->UnsafeSetAttribute(q.fieldName(fld),q.getStringField(fld));
735 ti.id = n->GetFieldValue("ID");
736 mTypeIdToNodeMap[ti] = n;
739 if ( numberoflevels != 1 )
742 nbloaded += DBLoadChildren(n, numberoflevels-1);
749 node->SetChildrenLoaded(true);
756 //=====================================================================
758 //======================================================================
759 void SQLiteTreeHandler::DBInsert(tree::Node* n)
761 GimmickMessage(2,"Inserting in DB '"<<n->GetLabel()
764 SQLAppendAttributesValues(n,val);
765 std::string insert("INSERT INTO ");
766 insert += GetTree().GetLevelDescriptor(n->GetLevel()).GetName();
767 insert += " " + val + ";";
771 // Store DB id of newly created node;
772 long lastrow = mDB->lastRowId();
773 std::stringstream ri;
774 ri << mDB->lastRowId();
775 n->SetAttribute("ID",ri.str());
777 //======================================================================
779 //======================================================================
780 /// Graft the branch defined by the attributes to the parent
781 void SQLiteTreeHandler::DBGraftToParent( tree::Node* parent,
782 const AttributeMapType& attr)
784 // std::cout <<"Grafting to parent '"<<parent->GetLabel()
787 for (int level = parent->GetLevel()+1;
788 level < GetTree().GetNumberOfLevels();
792 tree::Node* child = new tree::Node(parent,attr);
793 child->SetChildrenLoaded(true);
796 int nc = GetNumberOfChildren(parent)+1;
798 // std::cout<<"Number of children "<<parent->GetNumberOfChildren()<<std::endl;
799 std::stringstream out;
801 SetAttribute(parent,"NumberOfChildren",out.str());
804 // Set PARENT_ID if necessary
805 if ( parent->GetLevel()>0 )
806 child->SetAttribute("PARENT_ID",parent->GetAttribute("ID"));
812 SQLAppendAttributesValues(child,val);
813 std::string insert("INSERT INTO ");
814 insert += GetTree().GetLevelDescriptor(child->GetLevel()).GetName();
815 insert += " " + val + ";";
818 // Store DB id of newly created node;
819 long lastrow = mDB->lastRowId();
820 std::stringstream ri;
821 ri << mDB->lastRowId();
822 child->SetAttribute("ID",ri.str());
828 // Insert in TypeId map
830 ti.type = node->GetType();
832 mTypeIdToNodeMap[ti] = node;
833 // std::cout << "== Insert TypeId ("<<ti.type<<","<<ti.id<<")"<<std::endl;
837 if (node->GetType()==Node::Patient) summary.added_patients++;
838 if (node->GetType()==Node::Study) summary.added_studies++;
839 if (node->GetType()==Node::Series) summary.added_series++;
840 if (node->GetType()==Node::Image) summary.added_images++;
844 //======================================================================
847 //=====================================================================
848 /// Sets an attribute of a Node
849 bool SQLiteTreeHandler::DBSetAttribute(tree::Node* n,
850 const std::string& key,
851 const std::string& value)
853 GimmickMessage(3,"Setting Attribute of '"<<n->GetLabel()<<
854 "' "<<key<<"='"<<value<<"'"<<std::endl);
856 n->SetAttribute(key,value);
857 std::string sql = "UPDATE ";
858 sql += GetTree().GetLevelDescriptor(n->GetLevel()).GetName();
863 sql += "' WHERE ID = '";
864 sql += n->GetAttribute("ID");
866 // sql += " LIMIT 1";
871 //=====================================================================
872 /// Sets an attribute of a Node
873 void SQLiteTreeHandler::DBSetAttribute(const std::string& levelDescriptor,
874 const std::string& key,
875 const std::string& value,
876 const std::string& searchParam,
877 const std::string& searchVal)
880 std::string sql = "UPDATE ";
881 sql += levelDescriptor;
891 std::cout<<sql<<std::endl;
894 //=====================================================================
895 void SQLiteTreeHandler::DBRecursiveRemoveNode(Node* node)
898 std::string query = "DELETE FROM ";
900 query += GetTree().GetLevelDescriptor(node->GetLevel()).GetName();
901 query += " WHERE ID='"+ node->GetAttribute("ID") + "';";
904 GimmickDebugMessage(2,
906 <<node->GetLabel()<<"' with ID '"
907 <<node->GetAttribute("ID")
908 <<"' in level "<< GetTree().GetLevelDescriptor(node->GetLevel()).GetName()
912 if(node->GetNumberOfChildren()!=0)
914 Node::ChildrenListType::iterator i;
915 for (i = node->GetChildrenList().begin();
916 i != node->GetChildrenList().end();
919 DBRecursiveRemoveNode((*i));
922 else if(node->GetLevel()<GetTree().GetNumberOfLevels()-1)
924 DBRecursiveRemoveNode(node->GetLevel()+1,node->GetAttribute("ID"));
928 //=====================================================================
929 void SQLiteTreeHandler::DBRecursiveRemoveNode(int level, std::string parentId)
931 std::stringstream out;
932 std::stringstream result;
933 out<<"SELECT ID FROM "<<GetTree().GetLevelDescriptor(level).GetName()<<" WHERE PARENT_ID='"<<parentId<<"'";
936 QUERYDB(out.str(),q);
940 for (int fld = 0; fld < q.numFields(); fld++)
942 result<<q.getStringField(fld)<<"#";
946 std::string res=result.str();
949 while(fin<res.size()-1)
951 fin=res.find('#',ini);
952 DBDelete(GetTree().GetLevelDescriptor(level).GetName(),"ID",res.substr(ini,fin-ini));
953 if(level<GetTree().GetNumberOfLevels()-1)
955 DBRecursiveRemoveNode(level+1,res.substr(ini,fin-ini));
963 //=====================================================================
964 void SQLiteTreeHandler::DBDelete(std::string levelDescriptor, std::string key, std::string value)
967 std::stringstream query;
968 query<<"DELETE FROM "<<levelDescriptor<<" WHERE "<<key<<"='"<<value<<"';";
970 UPDATEDB(query.str());
971 GimmickDebugMessage(2," Deleting: Query: "<<query.str()<<std::endl);
975 //=====================================================================
976 void SQLiteTreeHandler::GetAttribute(std::string levelDescriptor,
977 std::string searchParam,
978 std::string searchVal,
982 std::stringstream out;
983 std::stringstream results;
984 out<<"SELECT "<<key<<" FROM "<<levelDescriptor;
987 out<<" WHERE "<<searchParam<<"='"<<searchVal<<"'";
991 QUERYDB(out.str(),q);
996 for (int fld = 0; fld < q.numFields(); fld++)
998 results<<q.getStringField(fld);
1006 result=results.str();
1009 //=====================================================================
1010 unsigned int SQLiteTreeHandler::GetNumberOfChildren(tree::Node* n)
1014 int level = n->GetLevel();
1016 if(level<GetTree().GetNumberOfLevels()&& level>0)
1018 std::string query = "SELECT NumberOfChildren FROM ";
1019 query += GetTree().GetLevelDescriptor(level).GetName();
1022 query += " WHERE ID='" + n->UnsafeGetAttribute("ID")
1031 for (int fld = 0; fld < q.numFields(); fld++)
1033 nb=q.getIntField(fld);
1047 //=====================================================================
1048 void SQLiteTreeHandler::GetTopLevelNodeId(const std::string& searchParam, const std::string& searchValue, std::string& parent_id)
1050 int level=GetTree().GetNumberOfLevels()-1;
1051 std::string sp=searchParam.c_str();
1052 std::string sv=searchValue.c_str();
1056 std::stringstream out;
1057 std::stringstream results;
1058 out<<"SELECT PARENT_ID FROM "<<GetTree().GetLevelDescriptor(level).GetName();
1059 out<<" WHERE "<<sp<<"='"<<sv<<"'";
1061 QUERYDB(out.str(),q);
1066 for (int fld = 0; fld < q.numFields(); fld++)
1068 results<<q.getStringField(fld);
1080 //=====================================================================
1081 void SQLiteTreeHandler::RemoveEntries(const std::string i_table,
1082 const std::string i_attribute,
1083 const std::string i_operand,
1084 const std::string i_val)
1086 std::stringstream query;
1087 query<<"DELETE FROM "<<i_table<<" WHERE "<<i_attribute<<" "<<i_operand<<" '"<<i_val<<"'";
1088 UPDATEDB(query.str());
1091 //=====================================================================
1092 void SQLiteTreeHandler::BeginTransaction()
1094 std::stringstream out;
1095 out<<"begin transaction;";
1096 UPDATEDB(out.str());
1099 //=====================================================================
1100 void SQLiteTreeHandler::EndTransaction()
1102 std::stringstream out;
1103 out<<"commit transaction;";
1104 UPDATEDB(out.str());
1108 //=====================================================================
1109 bool SQLiteTreeHandler::DBInsert(Node* alien_node,
1110 UpdateSummary& summary)
1112 // std::cout << "SQLiteTreeHandler::Insert('"<<alien_node->GetLabel()
1113 // <<"')"<<std::endl;
1115 // if (!ChildrenLoaded()) DBLoadChildren(this,Node::Database);
1119 std::string parent_id;
1120 parent = DBGetOrCreateParent(alien_node,parent_id,summary);
1123 DBRecursiveGetOrCreateNode(alien_node,parent,parent_id,summary);
1126 //=====================================================================
1129 //=====================================================================
1130 Node* SQLiteTreeHandler::DBGetOrCreateParent(Node* alien_node,
1131 std::string& parent_id,
1132 UpdateSummary& summary)
1134 // std::cout << "DBGetOrCreateParent '" << alien_node->GetLabel()<<"'"
1136 // Load the patients if not already done
1137 DBLoadChildren(this,Node::Patient);
1140 Node* parent = this;
1142 // The chain of ancestors
1143 std::deque<Node*> chain;
1144 Node* cur = alien_node->GetParent();
1145 for (int type=Node::Patient;
1146 type<alien_node->GetType();++type)
1148 chain.push_front(cur);
1149 cur = cur->GetParent();
1152 // create the nodes if do not exist
1153 std::deque<Node*>::iterator i;
1154 for (i=chain.begin();i!=chain.end();++i)
1156 // std::cout << " cur = '"<<(*i)->GetLabel()<<"'"<<std::endl;
1157 // std::string cur_id = DBGetNodeId(*i,parent_id);
1158 // if (cur_id.size()==0)
1160 // Node does not exist : create it
1162 parent = DBGetOrCreateNode(*i,
1168 DBLoadChildren(parent,parent->GetType()+1);
1174 //=====================================================================
1178 //=====================================================================
1179 void SQLiteTreeHandler::DBRecursiveGetOrCreateNode(Node* alien_node,
1181 const std::string& parent_id,
1182 UpdateSummary& summary)
1184 // std::cout << "SQLiteTreeHandler::RecursiveGetOrCreateNode('"
1185 // <<alien_node->GetLabel()
1186 // <<"','"<<parent<<"','"<<parent_id<<"')"<<std::endl;
1189 // std::cout << " -- Parent = '"<<parent->GetLabel()<<"'"<<std::endl;
1192 Node* new_node = DBGetOrCreateNode(alien_node,
1197 Node::ChildrenListType::iterator i;
1198 for (i = alien_node->GetChildrenList().begin();
1199 i != alien_node->GetChildrenList().end();
1202 DBRecursiveGetOrCreateNode((*i),new_node,new_id,summary);
1205 //=====================================================================
1208 //=====================================================================
1209 Node* SQLiteTreeHandler::DBGetOrCreateNode(Node* alien_node,
1210 Node* internal_parent,
1211 std::string parent_id,
1212 std::string& node_id,
1213 UpdateSummary& summary)
1215 // std::cout << "DBGetOrCreateNode('"<<alien_node->GetLabel()<<"','"
1216 // << internal_parent << "','"<< parent_id<<"')"<<std::endl;
1217 if (internal_parent != 0)
1219 // std::cout << " -- Parent = '"<<internal_parent->GetLabel()<<"'"<<std::endl;
1221 // Node Exists ? return it
1222 // First try among children of internal parent
1223 Node* node = GetChildrenLike(internal_parent,alien_node);
1226 node_id = node->UnsafeGetFieldValue("ID");
1229 // Second : try in DB
1231 // Does not exist : Create new one
1232 node = new Node(alien_node->GetType(),this,internal_parent);
1233 node->SetChildrenLoaded(true);
1234 // Copy fields values from alien
1235 Node::FieldValueMapType::iterator i,j;
1236 for (i = node->GetFieldValueMap().begin();
1237 i != node->GetFieldValueMap().end();
1240 j = alien_node->GetFieldValueMap().find(i->first);
1241 if (j != alien_node->GetFieldValueMap().end() )
1243 i->second = j->second;
1248 if (node->GetType()!=Node::Patient)
1249 node->SetFieldValue("PARENT_ID",parent_id);
1253 BuildSQLFieldsValues(node,val);
1254 std::string insert("INSERT INTO ");
1255 insert += std::string(SQLiteTreeHandlerStructure::Table(node->GetType()))
1257 // std::cout << "** SQL = '"<<insert<<"'"<<std::endl;
1259 // std::cout << "** SQL OK"<<std::endl;
1261 // Store DB id of newly created node;
1262 long lastrow = mDB->lastRowId();
1263 std::stringstream ri;
1264 ri << mDB->lastRowId();
1266 // std::cout << "LastRowId='"<<mDB->lastRowId()<<"' vs '"<<created_id<<"'"<<std::endl;
1268 node->SetFieldValue("ID",node_id);
1269 // Insert in TypeId map
1271 ti.type = node->GetType();
1273 mTypeIdToNodeMap[ti] = node;
1274 // std::cout << "== Insert TypeId ("<<ti.type<<","<<ti.id<<")"<<std::endl;
1278 if (node->GetType()==Node::Patient) summary.added_patients++;
1279 if (node->GetType()==Node::Study) summary.added_studies++;
1280 if (node->GetType()==Node::Series) summary.added_series++;
1281 if (node->GetType()==Node::Image) summary.added_images++;
1285 //=====================================================================
1287 //=====================================================================
1288 Node* SQLiteTreeHandler::GetChildrenLike(Node* parent,
1291 Node::ChildrenListType::iterator i;
1292 for (i = parent->GetChildrenList().begin();
1293 i != parent->GetChildrenList().end();
1296 Node::Type type = alien_node->GetType();
1299 j<SQLiteTreeHandlerStructure::NbQueryFields(type);
1303 alien_node->GetFieldValue(SQLiteTreeHandlerStructure::
1304 QueryField(type,j).key ) !=
1305 (*i)->GetFieldValue(SQLiteTreeHandlerStructure::
1306 QueryField(type,j).key ) )
1319 //=====================================================================
1321 //=====================================================================
1322 std::string SQLiteTreeHandler::DBGetNodeId(Node* node,
1323 const std::string& parent_id)
1325 // std::cout << "SQLiteTreeHandler::DBGetNodeId('"<<node->GetLabel()
1326 // <<"','"<<parent_id<<"')"
1329 int type = node->GetType();
1331 std::string table = SQLiteTreeHandlerStructure::Table(type);
1332 std::string where = "WHERE ";
1334 if (type!=Node::Patient)
1336 where += "PARENT_ID='" + parent_id
1337 //node->GetFieldValue("PARENT_ID")
1341 for (int i=0;i<SQLiteTreeHandlerStructure::NbQueryFields(type);i++)
1343 where += SQLiteTreeHandlerStructure::QueryField(type,i).key + "='"
1344 + node->GetFieldValue(SQLiteTreeHandlerStructure::QueryField(type,i).key) + "' ";
1345 if (i<SQLiteTreeHandlerStructure::NbQueryFields(type)-1)
1349 std::string query = "SELECT ID FROM " + table + " " + where + ";";
1350 // std::cout << "** SQL = '"<<query<<"'"<<std::endl;
1357 // std::cout << " - Node exists " << std::endl;
1358 std::string id = q.getStringField(0);
1359 // std::cout << " id = '"<<id<<"'"<<std::endl;
1366 //=====================================================================
1370 //=====================================================================
1371 Node* SQLiteTreeHandler::GetNodeFromTypeId(Node::Type type,
1372 const std::string& id)
1374 // std::cout << "GetNodeFromTypeId("<<type<<","<<id<<")"<<std::endl;
1380 TypeIdToNodeMapType::iterator i = mTypeIdToNodeMap.find(ti);
1381 if (i == mTypeIdToNodeMap.end())
1384 std::cout << "Internal error : mTypeIdToNodeMap does not contain key"
1386 creaError("Internal error : mTypeIdToNodeMap does not contain key");
1390 // std::cout << " ** Node = "<<i->second<<std::endl;
1394 //=====================================================================
1396 //=====================================================================
1397 bool SQLiteTreeHandler::Remove(Node* node)
1400 //DBRecursiveRemoveNode(node);
1402 // std::cout << "DELETE"<<std::endl;
1403 if (node->GetParent())
1405 node->GetParent()->RemoveChildrenFromList(node);
1408 // std::cout << "DELETE OK"<<std::endl;
1413 //========================================================================
1415 //=====================================================================
1416 void SQLiteTreeHandler::DBRecursiveRemoveNode(Node* node)
1418 // std::cout << "SQLiteTreeHandler::DBRecursiveRemoveNode('"
1419 // <<node->GetLabel()<<"')"<<std::endl;
1421 std::string query = "DELETE FROM ";
1422 query += SQLiteTreeHandlerStructure::Table(node->GetType());
1423 query += " WHERE ID='"+ node->GetFieldValue("ID") + "';";
1427 Node::ChildrenListType::iterator i;
1428 for (i = node->GetChildrenList().begin();
1429 i != node->GetChildrenList().end();
1432 DBRecursiveRemoveNode((*i));
1436 //=====================================================================
1438 //=====================================================================
1439 int SQLiteTreeHandler::DBQueryNumberOfChildren(Node* node)
1441 std::string query = "SELECT COUNT (ID) FROM ";
1442 query += SQLiteTreeHandlerStructure::Table(node->GetType()+1);
1443 if (node->GetType() != Node::Database)
1445 query += " WHERE PARENT_ID='"+ node->GetFieldValue("ID")+"'";
1449 // std::cout << "**SQL = "<< query << std::endl;
1454 // std::cout << "**RES = "<< q.getIntField(0) <<std::endl;
1456 return q.getIntField(0);
1458 //=====================================================================
1466 } // namespace creaImageIO