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>
24 #include <creaMessageManager.h>
27 #include <boost/filesystem.hpp>
28 #include <boost/algorithm/string/replace.hpp>
35 //=============================================================
36 SQLiteTreeHandler::SQLiteTreeHandler(const std::string& filename)
39 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::BuildDefaultTreeDescription()
57 for (int i = Node::Patient;
61 mNodeTypeDescription[i].BuildDefault(i);
65 // TODO : GetTree().GetDescription().LoadXML(FILE);
67 //=============================================================
69 //=============================================================
70 // void SQLiteTreeHandler::Print() const
73 std::cout << "-> '"<<GetName()<< "' - '"
74 << GetFileName()<<"'"<<std::endl;
75 ChildrenListType::const_iterator i;
76 for (i=GetChildrenList().begin(); i!=GetChildrenList().end(); i++)
82 //=============================================================
84 //=====================================================================
86 bool SQLiteTreeHandler::LocationIsValid()
92 //=====================================================================
94 //=====================================================================
95 char* format_sql(const std::string& s)
97 return sqlite3_mprintf("%q",s.c_str());
99 //=====================================================================
101 // sqlite3_exec(db, zSQL, 0, 0, 0);
102 // sqlite3_free(zSQL);
103 // char* CHAIN = format_sql(QUER); \
104 // sqlite3_free(CHAIN); \
106 //=====================================================================
107 #define QUERYDB(QUER,RES) \
110 GimmickMessage(2,"SQL: '"<<QUER<<"'"<<std::endl); \
111 RES = mDB->execQuery(QUER.c_str()); \
113 catch (CppSQLite3Exception& e) \
115 std::cout << "SQLite query '"<<QUER<<"' : " \
116 << e.errorCode() << ":" \
117 << e.errorMessage()<<std::endl; \
118 creaError("SQLite query '"<<QUER<<"' : " \
119 << e.errorCode() << ":" \
120 << e.errorMessage() ); \
123 //=====================================================================
125 //=====================================================================
126 #define UPDATEDB(UP) \
129 GimmickMessage(2,"SQL: '"<<UP<<"'"<<std::endl); \
130 mDB->execDML(UP.c_str()); \
132 catch (CppSQLite3Exception& e) \
134 std::cout << "SQLite update '"<<UP<<"' Error : " \
135 << e.errorCode() << ":" \
136 << e.errorMessage()<<std::endl; \
137 creaError("SQLite update '"<<UP<<"' Error : " \
138 << e.errorCode() << ":" \
139 << e.errorMessage() ); \
141 //=====================================================================
143 //=====================================================================
144 bool SQLiteTreeHandler::Open(bool writable)
146 // std::cout << "***> SQLiteTreeHandler::Open('"<<GetFileName()<<"')"<<std::endl;
147 SetWritable(writable);
151 //=====================================================================
152 bool SQLiteTreeHandler::Create(bool writable)
154 // std::cout << "***> SQLiteTreeHandler::New('"<<GetFileName()<<"')"<<std::endl;
155 SetWritable(writable);
158 //=====================================================================
161 //=====================================================================
162 bool SQLiteTreeHandler::Close()
166 //=====================================================================
169 //=====================================================================
170 bool SQLiteTreeHandler::Destroy()
174 //=====================================================================
176 //=====================================================================
177 unsigned int SQLiteTreeHandler::GetNumberOfChildren(tree::Node* n)
181 //=====================================================================
183 //=====================================================================
184 int SQLiteTreeHandler::LoadChildren(tree::Node* parent, int maxlevel)
188 //=====================================================================
193 //=====================================================================
194 void SQLiteTreeHandler::UnLoad(tree::Node* n)
197 //=====================================================================
200 //=====================================================================
201 int SQLiteTreeHandler::AddBranch( const std::map<std::string,std::string>& attr )
205 //=====================================================================
208 //=====================================================================
209 bool SQLiteTreeHandler::Remove(tree::Node*)
213 //=====================================================================
221 //=====================================================================
222 bool SQLiteTreeHandler::DBOpen()
224 GimmickMessage(2,"Opening SQLite database '"<<GetFileName()<<std::endl);
226 if (!boost::filesystem::exists(GetFileName()))
233 mDB->open(GetFileName().c_str());
235 catch (CppSQLite3Exception& e)
237 std::cerr << "Opening '"<<GetFileName()<<"' : "
238 << e.errorCode() << ":"
239 << e.errorMessage() << std::endl;
242 // TESTING STRUCTURE VALIDITY
243 if (!DBStructureIsValid())
245 std::cerr << "Opening '"<<GetFileName()<<"' : "
246 << " invalid database structure" << std::endl;
249 // IMPORTING NODE TYPE DESCRIPTIONS
250 DBImportTreeDescription();
253 //=====================================================================
255 //=====================================================================
256 bool SQLiteTreeHandler::DBCreate()
258 // std::cout << "### Creating SQLite database '"<<GetFileName()<<std::endl;
260 if (boost::filesystem::exists(GetFileName()))
262 creaMessage("Gimmick!",1,
263 "[Gimmick!] !! ERROR '"<<GetFileName()<<"' : "
264 << "file already exists"<<std::endl);
271 mDB->open(GetFileName().c_str());
273 catch (CppSQLite3Exception& e)
275 creaMessage("Gimmick!",1,
276 "[Gimmick!] !! ERROR '"
277 << e.errorCode() << ":"
278 << e.errorMessage() <<std::endl);
286 // Create LEVELS table
287 command = "create table LEVELS\n";
288 command += "( Name text )\n";
292 command = "INSERT INTO Levels (Name) VALUES ('";
293 command += GetTree().GetLevelDescriptor(0).GetName();
297 command = "CREATE TABLE ";
298 command += GetTree().GetLevelDescriptor(0).GetName();
299 command += "\n(\nID INTEGER PRIMARY KEY";
300 AppendAttributesSQLDefinition(0,command);
304 // Iterate the other Levels
305 for (int l=1; l<GetTree().GetNumberOfLevels(); ++l)
307 command = "INSERT INTO LEVELS (Name) VALUES ('";
308 command += GetTree().GetLevelDescriptor(l).GetName();
312 command = "CREATE TABLE ";
313 command += GetTree().GetLevelDescriptor(l).GetName();
314 command += "\n(\nID INTEGER PRIMARY KEY,\nPARENT_ID int not null";
315 AppendAttributesSQLDefinition(l,command);
317 command +="constraint FK_PARENT foreign key (PARENT_ID) references PATIENT(ID) on delete restrict on update restrict\n)";
321 // Create tables *_ATTRIBUTES
322 // and fill the tables *_ATTRIBUTES
323 DBExportTreeDescription();
326 catch (std::exception)
331 if (DBStructureIsValid())
333 // std::cout << "*** DONE ***"<<std::endl;
335 GetTree().SetChildrenLoaded(true);
341 // std::cout << "*** AAAARRRRGGG ***"<<std::endl;
346 //=====================================================================
348 //=====================================================================
349 void SQLiteTreeHandler::AppendAttributesSQLDefinition(int level,
352 LevelDescriptor::AttributeDescriptorListType::const_iterator i;
353 for (i = GetTree().GetAttributeDescriptorList(level).begin();
354 i != GetTree().GetAttributeDescriptorList(level).end();
357 // if (i->second.flags==1) continue;
363 //=====================================================================
365 //=====================================================================
366 void SQLiteTreeHandler::DBExportTreeDescription()
368 for (int level=0; level<GetTree().GetNumberOfLevels(); ++level)
371 command = "CREATE TABLE ";
372 command += GetTree().GetLevelDescriptor(level).GetName();
373 command += "_Attributes\n(\n";
374 command += "Key text,\n";
375 command += "Name text,\n";
376 command += "DicomGroup int,\n";
377 command += "DicomElement int,\n";
378 command += "Flags int\n";
382 LevelDescriptor::AttributeDescriptorListType::const_iterator i;
383 for (i = GetTree().GetAttributeDescriptorList(level).begin();
384 i != GetTree().GetAttributeDescriptorList(level).end();
388 std::stringstream insert;
389 insert << "INSERT INTO "
390 << GetTree().GetLevelDescriptor(level).GetName()
391 << "_Attributes (Key,Name,DicomGroup,DicomElement,Flags) "
393 << i->GetKey() << "','"
394 << i->GetName() << "',"
395 << i->GetGroup() << ","
396 << i->GetElement() << ","
397 << i->GetFlags() << ");";
399 UPDATEDB(insert.str());
404 //=====================================================================
406 //=====================================================================
407 void SQLiteTreeHandler::DBImportTreeDescription()
409 GimmickMessage(3,"ImportTreeDescription"<<std::endl);
411 // tree::Description& desc = GetTree().GetDescription();
414 for (Node::Type type=Node::Patient;
418 std::string query = "SELECT * FROM ";
419 query += SQLiteTreeHandlerStructure::Table(type);
422 // std::cout << "** SQL = '"<<query<<"'"<<std::endl;
430 Field::Description d(q.getStringField(0), // Key
431 q.getIntField(1), // Group
432 q.getIntField(2), // Element
433 q.getStringField(3), // Name
434 q.getIntField(4) // Flags
436 GetNodeTypeDescription(type).GetFieldDescriptionMap()[d.key] = d;
437 // std::cout << d << std::endl;
443 //=====================================================================
446 //=====================================================================
447 bool SQLiteTreeHandler::DBStructureIsValid()
451 // TO DO : TABLE WHICH STORES THE LEVELS
454 for (int i = SQLiteTreeHandlerStructure::TableBegin();
455 i != SQLiteTreeHandlerStructure::TableEnd();++i)
457 bool ok = mDB->tableExists(SQLiteTreeHandlerStructure::Table(i));
460 // std::cout << "** Table "<<SQLiteTreeHandlerStructure::Table(i)
461 // <<" exists"<<std::endl;
462 // TO DO : TEST MANDATORY FIELDS EXIST
466 // std::cout << "** Table "<<SQLiteTreeHandlerStructure::Table(i)
467 // <<" does *NOT* exist"<<std::endl;
474 //=====================================================================
477 //=====================================================================
478 int SQLiteTreeHandler::DBLoadChildren(Node* parent,
482 // std::cout << "SQLiteTreeHandler::DBLoadChildren("<<parent<<","<<maxlevel
483 // << ")"<<std::endl;
485 if (parent == GetTree()) { parent = 0; }
486 Node* xparent = parent;
487 if ( xparent==0 ) xparent = GetTree();
488 if ( xparent->ChildrenLoaded() )
490 // std::cout << "--> Children already loaded"<<std::endl;
493 if ( xparent->GetType() == Node::Image )
497 if ( xparent->GetType() >= maxlevel )
505 Node::Type type = xparent->GetType()+1;
509 std::string query = "SELECT * FROM ";
510 query += GetTree().GetDescriptor().GetLevelDescriptor(level).GetName();
511 //SQLiteTreeHandlerStructure::Table(type);
514 query += " WHERE PARENT_ID='" + parent->GetFieldValue("ID") + "'";
517 // std::cout << "** SQL = '"<<query<<"'"<<std::endl;
525 Node* n = new Node(type,
527 for (int fld = 0; fld < q.numFields(); fld++)
529 n->SetFieldValue(q.fieldName(fld),q.getStringField(fld));
534 ti.id = n->GetFieldValue("ID");
535 mTypeIdToNodeMap[ti] = n;
537 if ( type < maxlevel )
540 nbloaded += DBLoadChildren(n,maxlevel);
547 xparent->SetChildrenLoaded(true);
552 //=====================================================================
559 //=====================================================================
560 bool SQLiteTreeHandler::DBInsert(Node* alien_node,
561 UpdateSummary& summary)
563 // std::cout << "SQLiteTreeHandler::Insert('"<<alien_node->GetLabel()
564 // <<"')"<<std::endl;
566 // if (!ChildrenLoaded()) DBLoadChildren(this,Node::Database);
570 std::string parent_id;
571 parent = DBGetOrCreateParent(alien_node,parent_id,summary);
574 DBRecursiveGetOrCreateNode(alien_node,parent,parent_id,summary);
577 //=====================================================================
580 //=====================================================================
581 Node* SQLiteTreeHandler::DBGetOrCreateParent(Node* alien_node,
582 std::string& parent_id,
583 UpdateSummary& summary)
585 // std::cout << "DBGetOrCreateParent '" << alien_node->GetLabel()<<"'"
587 // Load the patients if not already done
588 DBLoadChildren(this,Node::Patient);
593 // The chain of ancestors
594 std::deque<Node*> chain;
595 Node* cur = alien_node->GetParent();
596 for (int type=Node::Patient;
597 type<alien_node->GetType();++type)
599 chain.push_front(cur);
600 cur = cur->GetParent();
603 // create the nodes if do not exist
604 std::deque<Node*>::iterator i;
605 for (i=chain.begin();i!=chain.end();++i)
607 // std::cout << " cur = '"<<(*i)->GetLabel()<<"'"<<std::endl;
608 // std::string cur_id = DBGetNodeId(*i,parent_id);
609 // if (cur_id.size()==0)
611 // Node does not exist : create it
613 parent = DBGetOrCreateNode(*i,
619 DBLoadChildren(parent,parent->GetType()+1);
625 //=====================================================================
629 //=====================================================================
630 void SQLiteTreeHandler::DBRecursiveGetOrCreateNode(Node* alien_node,
632 const std::string& parent_id,
633 UpdateSummary& summary)
635 // std::cout << "SQLiteTreeHandler::RecursiveGetOrCreateNode('"
636 // <<alien_node->GetLabel()
637 // <<"','"<<parent<<"','"<<parent_id<<"')"<<std::endl;
640 // std::cout << " -- Parent = '"<<parent->GetLabel()<<"'"<<std::endl;
643 Node* new_node = DBGetOrCreateNode(alien_node,
648 Node::ChildrenListType::iterator i;
649 for (i = alien_node->GetChildrenList().begin();
650 i != alien_node->GetChildrenList().end();
653 DBRecursiveGetOrCreateNode((*i),new_node,new_id,summary);
656 //=====================================================================
659 //=====================================================================
660 Node* SQLiteTreeHandler::DBGetOrCreateNode(Node* alien_node,
661 Node* internal_parent,
662 std::string parent_id,
663 std::string& node_id,
664 UpdateSummary& summary)
666 // std::cout << "DBGetOrCreateNode('"<<alien_node->GetLabel()<<"','"
667 // << internal_parent << "','"<< parent_id<<"')"<<std::endl;
668 if (internal_parent != 0)
670 // std::cout << " -- Parent = '"<<internal_parent->GetLabel()<<"'"<<std::endl;
672 // Node Exists ? return it
673 // First try among children of internal parent
674 Node* node = GetChildrenLike(internal_parent,alien_node);
677 node_id = node->UnsafeGetFieldValue("ID");
680 // Second : try in DB
682 // Does not exist : Create new one
683 node = new Node(alien_node->GetType(),this,internal_parent);
684 node->SetChildrenLoaded(true);
685 // Copy fields values from alien
686 Node::FieldValueMapType::iterator i,j;
687 for (i = node->GetFieldValueMap().begin();
688 i != node->GetFieldValueMap().end();
691 j = alien_node->GetFieldValueMap().find(i->first);
692 if (j != alien_node->GetFieldValueMap().end() )
694 i->second = j->second;
699 if (node->GetType()!=Node::Patient)
700 node->SetFieldValue("PARENT_ID",parent_id);
704 BuildSQLFieldsValues(node,val);
705 std::string insert("INSERT INTO ");
706 insert += std::string(SQLiteTreeHandlerStructure::Table(node->GetType()))
708 // std::cout << "** SQL = '"<<insert<<"'"<<std::endl;
710 // std::cout << "** SQL OK"<<std::endl;
712 // Store DB id of newly created node;
713 long lastrow = mDB->lastRowId();
714 std::stringstream ri;
715 ri << mDB->lastRowId();
717 // std::cout << "LastRowId='"<<mDB->lastRowId()<<"' vs '"<<created_id<<"'"<<std::endl;
719 node->SetFieldValue("ID",node_id);
720 // Insert in TypeId map
722 ti.type = node->GetType();
724 mTypeIdToNodeMap[ti] = node;
725 // std::cout << "== Insert TypeId ("<<ti.type<<","<<ti.id<<")"<<std::endl;
729 if (node->GetType()==Node::Patient) summary.added_patients++;
730 if (node->GetType()==Node::Study) summary.added_studies++;
731 if (node->GetType()==Node::Series) summary.added_series++;
732 if (node->GetType()==Node::Image) summary.added_images++;
736 //=====================================================================
738 //=====================================================================
739 Node* SQLiteTreeHandler::GetChildrenLike(Node* parent,
742 Node::ChildrenListType::iterator i;
743 for (i = parent->GetChildrenList().begin();
744 i != parent->GetChildrenList().end();
747 Node::Type type = alien_node->GetType();
750 j<SQLiteTreeHandlerStructure::NbQueryFields(type);
754 alien_node->GetFieldValue(SQLiteTreeHandlerStructure::
755 QueryField(type,j).key ) !=
756 (*i)->GetFieldValue(SQLiteTreeHandlerStructure::
757 QueryField(type,j).key ) )
770 //=====================================================================
772 //=====================================================================
773 std::string SQLiteTreeHandler::DBGetNodeId(Node* node,
774 const std::string& parent_id)
776 // std::cout << "SQLiteTreeHandler::DBGetNodeId('"<<node->GetLabel()
777 // <<"','"<<parent_id<<"')"
780 int type = node->GetType();
782 std::string table = SQLiteTreeHandlerStructure::Table(type);
783 std::string where = "WHERE ";
785 if (type!=Node::Patient)
787 where += "PARENT_ID='" + parent_id
788 //node->GetFieldValue("PARENT_ID")
792 for (int i=0;i<SQLiteTreeHandlerStructure::NbQueryFields(type);i++)
794 where += SQLiteTreeHandlerStructure::QueryField(type,i).key + "='"
795 + node->GetFieldValue(SQLiteTreeHandlerStructure::QueryField(type,i).key) + "' ";
796 if (i<SQLiteTreeHandlerStructure::NbQueryFields(type)-1)
800 std::string query = "SELECT ID FROM " + table + " " + where + ";";
801 // std::cout << "** SQL = '"<<query<<"'"<<std::endl;
808 // std::cout << " - Node exists " << std::endl;
809 std::string id = q.getStringField(0);
810 // std::cout << " id = '"<<id<<"'"<<std::endl;
817 //=====================================================================
821 //=====================================================================
822 Node* SQLiteTreeHandler::GetNodeFromTypeId(Node::Type type,
823 const std::string& id)
825 // std::cout << "GetNodeFromTypeId("<<type<<","<<id<<")"<<std::endl;
831 TypeIdToNodeMapType::iterator i = mTypeIdToNodeMap.find(ti);
832 if (i == mTypeIdToNodeMap.end())
835 std::cout << "Internal error : mTypeIdToNodeMap does not contain key"
837 creaError("Internal error : mTypeIdToNodeMap does not contain key");
841 // std::cout << " ** Node = "<<i->second<<std::endl;
844 //=====================================================================
846 //=====================================================================
847 bool SQLiteTreeHandler::Remove(Node* node)
849 DBRecursiveRemoveNode(node);
851 // std::cout << "DELETE"<<std::endl;
852 if (node->GetParent())
854 node->GetParent()->RemoveChildrenFromList(node);
857 // std::cout << "DELETE OK"<<std::endl;
860 //========================================================================
862 //=====================================================================
863 void SQLiteTreeHandler::DBRecursiveRemoveNode(Node* node)
865 // std::cout << "SQLiteTreeHandler::DBRecursiveRemoveNode('"
866 // <<node->GetLabel()<<"')"<<std::endl;
868 std::string query = "DELETE FROM ";
869 query += SQLiteTreeHandlerStructure::Table(node->GetType());
870 query += " WHERE ID='"+ node->GetFieldValue("ID") + "';";
874 Node::ChildrenListType::iterator i;
875 for (i = node->GetChildrenList().begin();
876 i != node->GetChildrenList().end();
879 DBRecursiveRemoveNode((*i));
882 //=====================================================================
884 //=====================================================================
885 int SQLiteTreeHandler::DBQueryNumberOfChildren(Node* node)
887 std::string query = "SELECT COUNT (ID) FROM ";
888 query += SQLiteTreeHandlerStructure::Table(node->GetType()+1);
889 if (node->GetType() != Node::Database)
891 query += " WHERE PARENT_ID='"+ node->GetFieldValue("ID")+"'";
895 // std::cout << "**SQL = "<< query << std::endl;
900 // std::cout << "**RES = "<< q.getIntField(0) <<std::endl;
902 return q.getIntField(0);
904 //=====================================================================
906 //========================================================================
907 std::string& format_sql2(std::string& str)
909 // quote must be doubled
910 // crea::Utils::Replace( str, "'", "''" );
911 boost::algorithm::replace_all(str,"'","''");
912 // Found strange strings which contained NULL char INSIDE string
913 int i,size=str.size();
918 str = str.substr(0,i);
925 //========================================================================
927 //=====================================================================
928 void SQLiteTreeHandler::BuildSQLFieldsValues(Node* n,
931 // std::cout << "BuildSQLFieldsValues('"<<n->GetLabel()<<"')"<<std::endl;
934 std::string values="";
935 Node::FieldValueMapType::iterator i;
936 for (i = n->GetFieldValueMap().begin();
937 i != n->GetFieldValueMap().end();
944 // std::cout << "("<<i->first<<","<<i->second<<")"<<std::endl;
945 atts += "'" + i->first + "'";
946 values += "'" + format_sql2(i->second) + "'";
950 atts[atts.size()-1]=' ';
951 values[values.size()-1]=' ';
953 str = "("+atts+") VALUES ("+values+")";
956 //=====================================================================
961 } // namespace creaImageIO