1 #include <creaImageIOSQLiteTreeHandler.h>
3 #include "CppSQLite3.h"
7 //#include <creaImageIOSQLiteTreeHandlerStructure.h>
9 //#include <creaImageIOUtilities.h>
11 //#include <icons/database.xpm>
17 #include <wx/filename.h>
20 //#include <icons/close.xpm>
23 #include <creaMessageManager.h>
26 #include <boost/filesystem.hpp>
27 #include <boost/algorithm/string/replace.hpp>
34 //=============================================================
35 SQLiteTreeHandler::SQLiteTreeHandler(const std::string& filename)
39 mSQLiteTreeHandler = this;
40 NodeTypeDescription::FieldDescriptionMapType& M =
41 mNodeTypeDescription[Node::Database].GetFieldDescriptionMap();
43 boost::filesystem::path full_path(location);
44 mName = full_path.leaf();
45 Field::Description fname("Name",0,0,"Name",0);
47 UnsafeSetFieldValue(fname.key,mName);
48 Field::Description flocation("File name",0,0,"File name",0);
49 M[flocation.key] = flocation;
50 UnsafeSetFieldValue(flocation.key,location);
52 // Field::Description ftype("Type",0,0,"Type",0);
53 // M[ftype.key] = ftype;
54 // UnsafeSetFieldValue(ftype.key,"Invalid location");
56 mDB = new CppSQLite3DB;
57 // std::cout << "** SQLite Version: " << mDB->SQLiteVersion() << std::endl;
61 //=============================================================
63 //=============================================================
64 SQLiteTreeHandler::~SQLiteTreeHandler()
68 Already done in Node now that SQLiteTreeHandler inherits from it
69 ChildrenListType::iterator i;
70 for (i=GetChildrenList().begin(); i!=GetChildrenList().end(); i++)
76 //=============================================================
79 //=============================================================
80 void SQLiteTreeHandler::BuildDefaultTreeDescription()
83 for (int i = Node::Patient;
87 mNodeTypeDescription[i].BuildDefault(i);
91 // TODO : GetTree().GetDescription().LoadXML(FILE);
93 //=============================================================
95 //=============================================================
96 // void SQLiteTreeHandler::Print() const
99 std::cout << "-> '"<<GetName()<< "' - '"
100 << GetFileName()<<"'"<<std::endl;
101 ChildrenListType::const_iterator i;
102 for (i=GetChildrenList().begin(); i!=GetChildrenList().end(); i++)
108 //=============================================================
110 //=====================================================================
112 bool SQLiteTreeHandler::LocationIsValid()
118 //=====================================================================
120 //=====================================================================
121 char* format_sql(const std::string& s)
123 return sqlite3_mprintf("%q",s.c_str());
125 //=====================================================================
127 // sqlite3_exec(db, zSQL, 0, 0, 0);
128 // sqlite3_free(zSQL);
129 // char* CHAIN = format_sql(QUER); \
130 // sqlite3_free(CHAIN); \
132 //=====================================================================
133 #define QUERYDB(QUER,RES) \
136 RES = mDB->execQuery(QUER.c_str()); \
138 catch (CppSQLite3Exception& e) \
140 std::cout << "SQLite query '"<<QUER<<"' : "<< e.errorCode() << ":" \
141 << e.errorMessage()<<std::endl; \
142 creaError("SQLite query '"<<QUER<<"' : "<< e.errorCode() << ":" \
143 << e.errorMessage() ); \
146 //=====================================================================
148 //=====================================================================
149 #define UPDATEDB(UP) \
152 mDB->execDML(UP.c_str()); \
154 catch (CppSQLite3Exception& e) \
156 std::cout << "SQLite update '"<<UP<<"' Error : "<< e.errorCode() << ":" \
157 << e.errorMessage()<<std::endl; \
158 creaError("SQLite update '"<<UP<<"' Error : "<< e.errorCode() << ":" \
159 << e.errorMessage() ); \
161 //=====================================================================
163 //=====================================================================
164 bool SQLiteTreeHandler::Open(bool writable)
166 // std::cout << "***> SQLiteTreeHandler::Open('"<<GetFileName()<<"')"<<std::endl;
167 SetWritable(writable);
171 //=====================================================================
172 bool SQLiteTreeHandler::Create(bool writable)
174 // std::cout << "***> SQLiteTreeHandler::New('"<<GetFileName()<<"')"<<std::endl;
175 SetWritable(writable);
178 //=====================================================================
181 //=====================================================================
182 bool SQLiteTreeHandler::Close()
186 //=====================================================================
189 //=====================================================================
190 bool SQLiteTreeHandler::Destroy()
194 //=====================================================================
196 //=====================================================================
197 unsigned int SQLiteTreeHandler::GetNumberOfChildren(tree::Node* n)
201 //=====================================================================
203 //=====================================================================
204 int SQLiteTreeHandler::LoadChildren(tree::Node* parent, int maxlevel)
208 //=====================================================================
213 //=====================================================================
214 void SQLiteTreeHandler::UnLoad(tree::Node* n)
217 //=====================================================================
220 //=====================================================================
221 int SQLiteTreeHandler::AddBranch( const std::map<std::string,std::string>& attr )
225 //=====================================================================
228 //=====================================================================
229 bool SQLiteTreeHandler::Remove(tree::Node*)
233 //=====================================================================
241 //=====================================================================
242 bool SQLiteTreeHandler::DBOpen()
244 // std::cout << "### Opening SQLite database '"<<GetFileName()<<std::endl;
246 if (!boost::filesystem::exists(GetFileName()))
253 mDB->open(GetFileName().c_str());
255 catch (CppSQLite3Exception& e)
257 std::cerr << "Opening '"<<GetFileName()<<"' : "
258 << e.errorCode() << ":"
259 << e.errorMessage() << std::endl;
262 // TESTING STRUCTURE VALIDITY
263 if (!DBStructureIsValid())
265 std::cerr << "Opening '"<<GetFileName()<<"' : "
266 << " invalid database structure" << std::endl;
269 // IMPORTING NODE TYPE DESCRIPTIONS
270 DBImportTreeDescription();
273 //=====================================================================
275 //=====================================================================
276 bool SQLiteTreeHandler::DBCreate()
278 // std::cout << "### Creating SQLite database '"<<GetFileName()<<std::endl;
280 if (boost::filesystem::exists(GetFileName()))
282 creaMessage("Gimmick!",1,
283 "[Gimmick!] !! ERROR '"<<GetFileName()<<"' : "
284 << "file already exists"<<std::endl);
291 mDB->open(GetFileName().c_str());
293 catch (CppSQLite3Exception& e)
295 creaMessage("Gimmick!",1,
296 "[Gimmick!] !! ERROR '"
297 << e.errorCode() << ":"
298 << e.errorMessage() <<std::endl);
307 command = "create table ";
308 command += GetTree().GetLevelDescriptor(0).GetName();
309 command += "\n(\nID INTEGER PRIMARY KEY,\n";
310 AppendAttributesSQLDefinition(0,command);
314 // Iterate the other Levels
315 for (int l=1; l<GetTree().GetNumberOfLevels(); ++l)
317 command = "create table ";
318 command += GetTree().GetLevelDescriptor(l).GetName();
319 command += "\n(\nID INTEGER PRIMARY KEY,\nPARENT_ID int not null,\n";
320 AppendAttributesSQLDefinition(l,command);
322 command +="constraint FK_PARENT foreign key (PARENT_ID) references PATIENT(ID) on delete restrict on update restrict\n)";
326 // Create tables *_ATTRIBUTES
327 for (int l=1; l<GetTree().GetNumberOfLevels(); ++l)
329 command = "create table ";
330 command += GetTree().GetLevelDescriptor(l).GetName();
331 command += "_ATTRIBUTES\n(\n";
332 command += "Key text,\n";
333 command += "Group int,\n";
334 command += "Element int,\n";
335 command += "Name text,\n";
336 command += "Flags int\n";
340 // Fill the tables *_ATTRIBUTES
341 DBExportTreeDescription();
344 catch (std::exception)
349 if (DBStructureIsValid())
351 // std::cout << "*** DONE ***"<<std::endl;
353 GetTree().SetChildrenLoaded(true);
359 // std::cout << "*** AAAARRRRGGG ***"<<std::endl;
364 //=====================================================================
366 //=====================================================================
367 void SQLiteTreeHandler::AppendAttributesSQLDefinition(int level,
371 std::vector<std::string*> keys;
372 NodeTypeDescription::FieldDescriptionMapType::iterator i;
373 for (i = GetNodeTypeDescription(c).GetFieldDescriptionMap().begin();
374 i != GetNodeTypeDescription(c).GetFieldDescriptionMap().end();
377 if (i->second.flags==1) continue;
378 keys.push_back(&(i->second.key));
380 std::vector<std::string*>::iterator j;
381 for (j=keys.begin();j!=keys.end();)
390 //=====================================================================
392 //=====================================================================
393 void SQLiteTreeHandler::DBExportTreeDescription()
396 // std::cout<<"ExportNodeTypeDescriptionsToDB()"<<std::endl;
397 for (Node::Type type=Node::Patient;
401 NodeTypeDescription::FieldDescriptionMapType::iterator i;
402 for (i = GetNodeTypeDescription(type).GetFieldDescriptionMap().begin();
403 i!= GetNodeTypeDescription(type).GetFieldDescriptionMap().end();
407 std::stringstream insert;
408 insert << "INSERT INTO "
409 << std::string(SQLiteTreeHandlerStructure::Table(type))
410 << "_FIELDS (Key,DicomGroup,DicomElement,Name,Flags) "
412 << (*i).second.GetKey() << "',"
413 << (*i).second.GetGroup() << ","
414 << (*i).second.GetElement() << ",'"
415 << (*i).second.GetName() << "',"
416 << (*i).second.GetFlags() << ");";
418 // std::cout << "** SQL = '"<<insert.str()<<"'"<<std::endl;
420 UPDATEDB(insert.str());
425 //=====================================================================
427 //=====================================================================
428 void SQLiteTreeHandler::DBImportTreeDescription()
430 // std::cout<<"ImportNodeTypeDescriptionsFromDB()"<<std::endl;
432 for (Node::Type type=Node::Patient;
436 std::string query = "SELECT * FROM ";
437 query += SQLiteTreeHandlerStructure::Table(type);
440 // std::cout << "** SQL = '"<<query<<"'"<<std::endl;
448 Field::Description d(q.getStringField(0), // Key
449 q.getIntField(1), // Group
450 q.getIntField(2), // Element
451 q.getStringField(3), // Name
452 q.getIntField(4) // Flags
454 GetNodeTypeDescription(type).GetFieldDescriptionMap()[d.key] = d;
455 // std::cout << d << std::endl;
461 //=====================================================================
464 //=====================================================================
465 bool SQLiteTreeHandler::DBStructureIsValid()
467 bool success = false; //true;
469 // TO DO : TABLE WHICH STORES THE LEVELS
472 for (int i = SQLiteTreeHandlerStructure::TableBegin();
473 i != SQLiteTreeHandlerStructure::TableEnd();++i)
475 bool ok = mDB->tableExists(SQLiteTreeHandlerStructure::Table(i));
478 // std::cout << "** Table "<<SQLiteTreeHandlerStructure::Table(i)
479 // <<" exists"<<std::endl;
480 // TO DO : TEST MANDATORY FIELDS EXIST
484 // std::cout << "** Table "<<SQLiteTreeHandlerStructure::Table(i)
485 // <<" does *NOT* exist"<<std::endl;
492 //=====================================================================
495 //=====================================================================
496 int SQLiteTreeHandler::DBLoadChildren(Node* parent,
500 // std::cout << "SQLiteTreeHandler::DBLoadChildren("<<parent<<","<<maxlevel
501 // << ")"<<std::endl;
503 if (parent == GetTree()) { parent = 0; }
504 Node* xparent = parent;
505 if ( xparent==0 ) xparent = GetTree();
506 if ( xparent->ChildrenLoaded() )
508 // std::cout << "--> Children already loaded"<<std::endl;
511 if ( xparent->GetType() == Node::Image )
515 if ( xparent->GetType() >= maxlevel )
523 Node::Type type = xparent->GetType()+1;
527 std::string query = "SELECT * FROM ";
528 query += GetTree().GetDescriptor().GetLevelDescriptor(level).GetName();
529 //SQLiteTreeHandlerStructure::Table(type);
532 query += " WHERE PARENT_ID='" + parent->GetFieldValue("ID") + "'";
535 // std::cout << "** SQL = '"<<query<<"'"<<std::endl;
543 Node* n = new Node(type,
545 for (int fld = 0; fld < q.numFields(); fld++)
547 n->SetFieldValue(q.fieldName(fld),q.getStringField(fld));
552 ti.id = n->GetFieldValue("ID");
553 mTypeIdToNodeMap[ti] = n;
555 if ( type < maxlevel )
558 nbloaded += DBLoadChildren(n,maxlevel);
565 xparent->SetChildrenLoaded(true);
570 //=====================================================================
577 //=====================================================================
578 bool SQLiteTreeHandler::DBInsert(Node* alien_node,
579 UpdateSummary& summary)
581 // std::cout << "SQLiteTreeHandler::Insert('"<<alien_node->GetLabel()
582 // <<"')"<<std::endl;
584 // if (!ChildrenLoaded()) DBLoadChildren(this,Node::Database);
588 std::string parent_id;
589 parent = DBGetOrCreateParent(alien_node,parent_id,summary);
592 DBRecursiveGetOrCreateNode(alien_node,parent,parent_id,summary);
595 //=====================================================================
598 //=====================================================================
599 Node* SQLiteTreeHandler::DBGetOrCreateParent(Node* alien_node,
600 std::string& parent_id,
601 UpdateSummary& summary)
603 // std::cout << "DBGetOrCreateParent '" << alien_node->GetLabel()<<"'"
605 // Load the patients if not already done
606 DBLoadChildren(this,Node::Patient);
611 // The chain of ancestors
612 std::deque<Node*> chain;
613 Node* cur = alien_node->GetParent();
614 for (int type=Node::Patient;
615 type<alien_node->GetType();++type)
617 chain.push_front(cur);
618 cur = cur->GetParent();
621 // create the nodes if do not exist
622 std::deque<Node*>::iterator i;
623 for (i=chain.begin();i!=chain.end();++i)
625 // std::cout << " cur = '"<<(*i)->GetLabel()<<"'"<<std::endl;
626 // std::string cur_id = DBGetNodeId(*i,parent_id);
627 // if (cur_id.size()==0)
629 // Node does not exist : create it
631 parent = DBGetOrCreateNode(*i,
637 DBLoadChildren(parent,parent->GetType()+1);
643 //=====================================================================
647 //=====================================================================
648 void SQLiteTreeHandler::DBRecursiveGetOrCreateNode(Node* alien_node,
650 const std::string& parent_id,
651 UpdateSummary& summary)
653 // std::cout << "SQLiteTreeHandler::RecursiveGetOrCreateNode('"
654 // <<alien_node->GetLabel()
655 // <<"','"<<parent<<"','"<<parent_id<<"')"<<std::endl;
658 // std::cout << " -- Parent = '"<<parent->GetLabel()<<"'"<<std::endl;
661 Node* new_node = DBGetOrCreateNode(alien_node,
666 Node::ChildrenListType::iterator i;
667 for (i = alien_node->GetChildrenList().begin();
668 i != alien_node->GetChildrenList().end();
671 DBRecursiveGetOrCreateNode((*i),new_node,new_id,summary);
674 //=====================================================================
677 //=====================================================================
678 Node* SQLiteTreeHandler::DBGetOrCreateNode(Node* alien_node,
679 Node* internal_parent,
680 std::string parent_id,
681 std::string& node_id,
682 UpdateSummary& summary)
684 // std::cout << "DBGetOrCreateNode('"<<alien_node->GetLabel()<<"','"
685 // << internal_parent << "','"<< parent_id<<"')"<<std::endl;
686 if (internal_parent != 0)
688 // std::cout << " -- Parent = '"<<internal_parent->GetLabel()<<"'"<<std::endl;
690 // Node Exists ? return it
691 // First try among children of internal parent
692 Node* node = GetChildrenLike(internal_parent,alien_node);
695 node_id = node->UnsafeGetFieldValue("ID");
698 // Second : try in DB
700 // Does not exist : Create new one
701 node = new Node(alien_node->GetType(),this,internal_parent);
702 node->SetChildrenLoaded(true);
703 // Copy fields values from alien
704 Node::FieldValueMapType::iterator i,j;
705 for (i = node->GetFieldValueMap().begin();
706 i != node->GetFieldValueMap().end();
709 j = alien_node->GetFieldValueMap().find(i->first);
710 if (j != alien_node->GetFieldValueMap().end() )
712 i->second = j->second;
717 if (node->GetType()!=Node::Patient)
718 node->SetFieldValue("PARENT_ID",parent_id);
722 BuildSQLFieldsValues(node,val);
723 std::string insert("INSERT INTO ");
724 insert += std::string(SQLiteTreeHandlerStructure::Table(node->GetType()))
726 // std::cout << "** SQL = '"<<insert<<"'"<<std::endl;
728 // std::cout << "** SQL OK"<<std::endl;
730 // Store DB id of newly created node;
731 long lastrow = mDB->lastRowId();
732 std::stringstream ri;
733 ri << mDB->lastRowId();
735 // std::cout << "LastRowId='"<<mDB->lastRowId()<<"' vs '"<<created_id<<"'"<<std::endl;
737 node->SetFieldValue("ID",node_id);
738 // Insert in TypeId map
740 ti.type = node->GetType();
742 mTypeIdToNodeMap[ti] = node;
743 // std::cout << "== Insert TypeId ("<<ti.type<<","<<ti.id<<")"<<std::endl;
747 if (node->GetType()==Node::Patient) summary.added_patients++;
748 if (node->GetType()==Node::Study) summary.added_studies++;
749 if (node->GetType()==Node::Series) summary.added_series++;
750 if (node->GetType()==Node::Image) summary.added_images++;
754 //=====================================================================
756 //=====================================================================
757 Node* SQLiteTreeHandler::GetChildrenLike(Node* parent,
760 Node::ChildrenListType::iterator i;
761 for (i = parent->GetChildrenList().begin();
762 i != parent->GetChildrenList().end();
765 Node::Type type = alien_node->GetType();
768 j<SQLiteTreeHandlerStructure::NbQueryFields(type);
772 alien_node->GetFieldValue(SQLiteTreeHandlerStructure::
773 QueryField(type,j).key ) !=
774 (*i)->GetFieldValue(SQLiteTreeHandlerStructure::
775 QueryField(type,j).key ) )
788 //=====================================================================
790 //=====================================================================
791 std::string SQLiteTreeHandler::DBGetNodeId(Node* node,
792 const std::string& parent_id)
794 // std::cout << "SQLiteTreeHandler::DBGetNodeId('"<<node->GetLabel()
795 // <<"','"<<parent_id<<"')"
798 int type = node->GetType();
800 std::string table = SQLiteTreeHandlerStructure::Table(type);
801 std::string where = "WHERE ";
803 if (type!=Node::Patient)
805 where += "PARENT_ID='" + parent_id
806 //node->GetFieldValue("PARENT_ID")
810 for (int i=0;i<SQLiteTreeHandlerStructure::NbQueryFields(type);i++)
812 where += SQLiteTreeHandlerStructure::QueryField(type,i).key + "='"
813 + node->GetFieldValue(SQLiteTreeHandlerStructure::QueryField(type,i).key) + "' ";
814 if (i<SQLiteTreeHandlerStructure::NbQueryFields(type)-1)
818 std::string query = "SELECT ID FROM " + table + " " + where + ";";
819 // std::cout << "** SQL = '"<<query<<"'"<<std::endl;
826 // std::cout << " - Node exists " << std::endl;
827 std::string id = q.getStringField(0);
828 // std::cout << " id = '"<<id<<"'"<<std::endl;
835 //=====================================================================
839 //=====================================================================
840 Node* SQLiteTreeHandler::GetNodeFromTypeId(Node::Type type,
841 const std::string& id)
843 // std::cout << "GetNodeFromTypeId("<<type<<","<<id<<")"<<std::endl;
849 TypeIdToNodeMapType::iterator i = mTypeIdToNodeMap.find(ti);
850 if (i == mTypeIdToNodeMap.end())
853 std::cout << "Internal error : mTypeIdToNodeMap does not contain key"
855 creaError("Internal error : mTypeIdToNodeMap does not contain key");
859 // std::cout << " ** Node = "<<i->second<<std::endl;
862 //=====================================================================
864 //=====================================================================
865 bool SQLiteTreeHandler::Remove(Node* node)
867 DBRecursiveRemoveNode(node);
869 // std::cout << "DELETE"<<std::endl;
870 if (node->GetParent())
872 node->GetParent()->RemoveChildrenFromList(node);
875 // std::cout << "DELETE OK"<<std::endl;
878 //========================================================================
880 //=====================================================================
881 void SQLiteTreeHandler::DBRecursiveRemoveNode(Node* node)
883 // std::cout << "SQLiteTreeHandler::DBRecursiveRemoveNode('"
884 // <<node->GetLabel()<<"')"<<std::endl;
886 std::string query = "DELETE FROM ";
887 query += SQLiteTreeHandlerStructure::Table(node->GetType());
888 query += " WHERE ID='"+ node->GetFieldValue("ID") + "';";
892 Node::ChildrenListType::iterator i;
893 for (i = node->GetChildrenList().begin();
894 i != node->GetChildrenList().end();
897 DBRecursiveRemoveNode((*i));
900 //=====================================================================
902 //=====================================================================
903 int SQLiteTreeHandler::DBQueryNumberOfChildren(Node* node)
905 std::string query = "SELECT COUNT (ID) FROM ";
906 query += SQLiteTreeHandlerStructure::Table(node->GetType()+1);
907 if (node->GetType() != Node::Database)
909 query += " WHERE PARENT_ID='"+ node->GetFieldValue("ID")+"'";
913 // std::cout << "**SQL = "<< query << std::endl;
918 // std::cout << "**RES = "<< q.getIntField(0) <<std::endl;
920 return q.getIntField(0);
922 //=====================================================================
924 //========================================================================
925 std::string& format_sql2(std::string& str)
927 // quote must be doubled
928 // crea::Utils::Replace( str, "'", "''" );
929 boost::algorithm::replace_all(str,"'","''");
930 // Found strange strings which contained NULL char INSIDE string
931 int i,size=str.size();
936 str = str.substr(0,i);
943 //========================================================================
945 //=====================================================================
946 void SQLiteTreeHandler::BuildSQLFieldsValues(Node* n,
949 // std::cout << "BuildSQLFieldsValues('"<<n->GetLabel()<<"')"<<std::endl;
952 std::string values="";
953 Node::FieldValueMapType::iterator i;
954 for (i = n->GetFieldValueMap().begin();
955 i != n->GetFieldValueMap().end();
962 // std::cout << "("<<i->first<<","<<i->second<<")"<<std::endl;
963 atts += "'" + i->first + "'";
964 values += "'" + format_sql2(i->second) + "'";
968 atts[atts.size()-1]=' ';
969 values[values.size()-1]=' ';
971 str = "("+atts+") VALUES ("+values+")";
974 //=====================================================================
979 } // namespace creaImageIO