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 //=====================================================================
51 //=====================================================================
52 bool SQLiteTreeHandler::Open(bool writable)
54 // std::cout << "***> SQLiteTreeHandler::Open('"<<GetFileName()<<"')"<<std::endl;
55 SetWritable(writable);
59 //=====================================================================
60 bool SQLiteTreeHandler::Create(bool writable)
62 // std::cout << "***> SQLiteTreeHandler::New('"<<GetFileName()<<"')"<<std::endl;
63 SetWritable(writable);
66 //=====================================================================
69 //=====================================================================
70 bool SQLiteTreeHandler::Close()
74 //=====================================================================
77 //=====================================================================
78 bool SQLiteTreeHandler::Destroy()
83 //=====================================================================
85 //=====================================================================
86 int SQLiteTreeHandler::LoadChildren(tree::Node* parent, int maxlevel)
88 if (parent==0) parent = GetTree().GetTree();
89 return DBLoadChildren(parent,maxlevel);
91 //=====================================================================
96 //=====================================================================
97 void SQLiteTreeHandler::UnLoad(tree::Node* n)
100 //=====================================================================
102 //=====================================================================
103 int SQLiteTreeHandler::AddBranch( const AttributeMapType& attr )
105 tree::Node* parent = DBGetParent(attr);
106 DBGraftToParent(parent,attr);
107 return (parent->GetLevel()+1);
110 //=====================================================================
113 //=====================================================================
114 bool SQLiteTreeHandler::Remove(tree::Node* node)
116 DBRecursiveRemoveNode(node);
118 // std::cout << "DELETE"<<std::endl;
120 tree::Node* parent=node->GetParent();
123 int nC = parent->RemoveChildrenFromList(node);
124 if(nC>0 && parent->GetLevel()>0)
126 std::stringstream out;
128 SetAttribute(parent,"NumberOfChildren",out.str());
137 if(remove&&parent->GetLevel()>0)
141 // std::cout << "DELETE OK"<<std::endl;
145 //=====================================================================
147 //=====================================================================
148 /// Sets an attribute of a Node
149 bool SQLiteTreeHandler::SetAttribute(tree::Node* n,
150 const std::string& key,
151 const std::string& value)
153 if (n==0) n=GetTree().GetTree();
154 return DBSetAttribute(n,key,value);
156 //=====================================================================
157 //=====================================================================
158 /// Sets an attribute
159 void SQLiteTreeHandler::SetAttribute(const std::string& levelDescriptor,
160 const std::string& key,
161 const std::string& value,
162 const std::string& searchParam,
163 const std::string& searchVal)
165 DBSetAttribute(levelDescriptor,key,value,searchParam, searchVal);
167 //=====================================================================
169 void SQLiteTreeHandler::DeleteTuple(std::string levelDescriptor,
170 std::string key, std::string value)
172 DBDelete(levelDescriptor,key,value);
174 //=====================================================================
188 //=====================================================================
189 // SQLite DB specific methods
190 //=====================================================================
195 //=====================================================================
196 char* format_sql(const std::string& s)
198 return sqlite3_mprintf("%q",s.c_str());
200 //=====================================================================
203 //=====================================================================
204 #define QUERYDB(QUER,RES) \
207 GimmickMessage(2,"SQL query: '"<<QUER<<"'"<<std::endl); \
208 RES = mDB->execQuery(QUER.c_str()); \
210 catch (CppSQLite3Exception& e) \
212 GimmickError("SQLite query '"<<QUER<<"' : " \
213 << e.errorCode() << ":" \
214 << e.errorMessage() ); \
217 //=====================================================================
219 //=====================================================================
220 #define UPDATEDB(UP) \
223 GimmickMessage(2,"SQL update: '"<<UP<<"'"<<std::endl); \
224 mDB->execDML(UP.c_str()); \
226 catch (CppSQLite3Exception& e) \
228 GimmickError("SQLite update '"<<UP<<"' Error : " \
229 << e.errorCode() << ":" \
230 << e.errorMessage() ); \
232 //=====================================================================
235 //=====================================================================
236 bool SQLiteTreeHandler::DBOpen()
238 GimmickMessage(1,"Opening SQLite database '"<<GetFileName()
239 <<"' ... "<<std::endl);
241 if (!boost::filesystem::exists(GetFileName()))
248 mDB->open(GetFileName().c_str());
250 catch (CppSQLite3Exception& e)
252 GimmickError("Opening '"<<GetFileName()<<"' : "
253 << e.errorCode() << ":"
254 << e.errorMessage());
257 // IMPORT TREE DESCRIPTION (AND TEST DB VALIDITY)
258 if (!DBImportTreeDescription())
263 GimmickDebugMessage(1,"Opening SQLite database '"<<GetFileName()
264 <<"' ... OK"<<std::endl);
267 //=====================================================================
269 //=====================================================================
270 bool SQLiteTreeHandler::DBCreate()
272 GimmickMessage(1,"Creating SQLite database '"<<GetFileName()
273 <<"' ... "<<std::endl);
275 if (boost::filesystem::exists(GetFileName()))
277 GimmickError(GetFileName()<<"' : "
278 << "file already exists");
285 mDB->open(GetFileName().c_str());
287 catch (CppSQLite3Exception& e)
289 GimmickError(e.errorCode() << ":"
290 << e.errorMessage() <<std::endl);
298 // Create LEVELS table
299 command = "create table LEVELS\n";
300 command += "( Name text )\n";
303 // Iterate the Levels
304 for (l=0; l<GetTree().GetNumberOfLevels(); l++)
306 command = "INSERT INTO LEVELS (Name) VALUES ('";
307 command += GetTree().GetLevelDescriptor(l).GetName();
311 // Create table of level (for level>0, i.e. not Root)
314 command = "CREATE TABLE ";
315 command += GetTree().GetLevelDescriptor(l).GetName();
316 command += "\n(\nID INTEGER PRIMARY KEY";
319 command += ",\nPARENT_ID int not null";
321 SQLAppendAttributesDefinition(l,command);
324 command += ",\nconstraint FK_PARENT foreign key (PARENT_ID) references ";
325 command += GetTree().GetLevelDescriptor(l-1).GetName();
326 command += "(ID) on delete restrict on update restrict";
332 // Add Attribute 'ID' to Description
333 GetTree().GetDescriptor().Add
334 (AttributeDescriptor( "ID",
335 "Database Identifier",
337 AttributeDescriptor::PRIVATE
342 // Add Attribute 'PARENT_ID' to Description
343 GetTree().GetDescriptor().Add
344 (AttributeDescriptor( "PARENT_ID",
345 "Database Parent Identifier",
347 AttributeDescriptor::PRIVATE
353 // Create table *_ATTRIBUTES
355 command = "CREATE TABLE ";
356 command += GetTree().GetLevelDescriptor(l).GetName();
357 command += "_Attributes\n(\n";
358 command += "Key text,\n";
359 command += "Name text,\n";
360 command += "DicomGroup int,\n";
361 command += "DicomElement int,\n";
362 command += "Flags int\n";
367 // Fill the table *_ATTRIBUTES
368 LevelDescriptor::AttributeDescriptorListType::const_iterator i;
369 for (i = GetTree().GetAttributeDescriptorList(l).begin();
370 i != GetTree().GetAttributeDescriptorList(l).end();
374 std::stringstream insert;
375 insert << "INSERT INTO "
376 << GetTree().GetLevelDescriptor(l).GetName()
377 << "_Attributes (Key,Name,DicomGroup,DicomElement,Flags) "
379 << i->GetKey() << "','"
380 << i->GetName() << "',"
381 << i->GetGroup() << ","
382 << i->GetElement() << ","
383 << i->GetFlags() << ");";
384 UPDATEDB(insert.str());
389 // Initialize the root attributes
390 GetTree().InitializeAttributeMap();
391 // Insert the root in the level 0 table
392 DBInsert(GetTree().GetTree());
395 GetTree().SetChildrenLoaded(true);
396 GimmickMessage(1,"Creating SQLite database '"<<GetFileName()
397 <<"' ... OK"<<std::endl);
400 //=====================================================================
402 //=====================================================================
403 void SQLiteTreeHandler::SQLAppendAttributesDefinition(int level,
406 LevelDescriptor::AttributeDescriptorListType::const_iterator i;
407 for (i = GetTree().GetAttributeDescriptorList(level).begin();
408 i != GetTree().GetAttributeDescriptorList(level).end();
411 // if (i->second.flags==1) continue;
417 //=====================================================================
420 //=====================================================================
421 bool SQLiteTreeHandler::DBImportTreeDescription()
423 GimmickMessage(1,"Importing tree description for database ..."
426 // Test table 'LEVELS' existence
427 if ( ! mDB->tableExists("LEVELS") )
429 GimmickMessage(1,"!! ERROR : Table 'LEVELS' does not exist"
434 tree::Descriptor& desc = GetTree().GetDescriptor();
435 // clears the existing one
439 std::string query = "SELECT * FROM LEVELS";
445 std::string name = q.getStringField(0);
446 GimmickMessage(2," * Importing level '"<<name<<"'"<<std::endl);
447 desc.Add(LevelDescriptor(name));
452 for (int level = 0; level < nblevel; ++level )
454 std::string table = GetTree().GetLevelDescriptor(level).GetName();
455 table += "_Attributes";
456 // Test table 'LEVELS' existence
457 if ( ! mDB->tableExists(table.c_str()) )
459 GimmickMessage(1,"!! ERROR : Table '"<<table<<"' does not exist"
464 std::string query = "SELECT * FROM ";
469 GimmickMessage(2," * Level '"
470 <<GetTree().GetLevelDescriptor(level).GetName()
473 // Test that ID and PARENT_ID mandatory attributes exist
474 bool ID_found = false;
475 bool PARENT_ID_found = false;
476 if (level==0) ID_found = true;
477 if (level<=1) PARENT_ID_found = true;
481 std::string key(q.getStringField(0));
482 std::string name(q.getStringField(1));
483 GimmickMessage(2," - Importing attribute '"<<key<<"' '"<<name
486 (AttributeDescriptor( key, // Key
488 q.getIntField(2), // Group
489 q.getIntField(3), // Element
490 q.getIntField(4) // Flags
496 if ( key == "PARENT_ID" )
498 PARENT_ID_found = true;
503 if ( ! (ID_found || PARENT_ID_found ) )
505 GimmickMessage(1,"!! ERROR : Table '"<<table
506 <<"' does not contain mandatory attribute ID or PARENT_ID"
514 // Create the attributes table for Root (i.e. Tree)
515 LevelDescriptor::AttributeDescriptorListType::const_iterator a;
516 for (a = GetTree().GetAttributeDescriptorList(0).begin();
517 a!= GetTree().GetAttributeDescriptorList(0).end();
521 GetTree().UnsafeSetAttribute( a->GetKey(), "" );
524 // Reading Root attributes
526 query = "SELECT * FROM ";
527 query += GetTree().GetLevelDescriptor(0).GetName();
530 for (int fld = 0; fld < q.numFields(); fld++)
532 GetTree().UnsafeSetAttribute(q.fieldName(fld),
533 q.getStringField(fld));
536 GimmickMessage(1,"Importing tree description from database ... OK"
540 //=====================================================================
542 //========================================================================
544 std::string& SQLformat(std::string& str)
546 // quote must be doubled
547 boost::algorithm::replace_all(str,"'","''");
548 // Found strange strings which contained NULL char INSIDE string
549 int i,size=str.size();
554 str = str.substr(0,i);
561 //========================================================================
563 //=====================================================================
564 void SQLiteTreeHandler::SQLAppendAttributesValues(tree::Node* n,
567 GimmickMessage(4,"SQLAppendAttributesValues"<<std::endl);
569 std::string values="";
570 tree::Node::AttributeMapType::iterator i;
571 for (i = n->GetAttributeMap().begin();
572 i != n->GetAttributeMap().end();
580 atts += "'" + i->first + "'";
581 values += "'" + SQLformat(i->second) + "'";
584 GimmickMessage(4,"'"<<i->first<<"' = '"<<i->second<<"'"<<std::endl);
586 atts[atts.size()-1]=' ';
587 values[values.size()-1]=' ';
589 str = "("+atts+") VALUES ("+values+")";
590 GimmickMessage(4,"Result = '"<<str<<"'"<<std::endl);
592 //=====================================================================
594 //=====================================================================
595 tree::Node* SQLiteTreeHandler::DBGetParent( const AttributeMapType& attr)
597 Node* parent = GetTree().GetTree();
602 // Load the children of the current parent
603 DBLoadChildren(parent);
604 // Iterate the children
605 tree::Node::ChildrenListType::const_iterator i;
606 for (i = parent->GetChildrenList().begin();
607 i!= parent->GetChildrenList().end();
610 if ( (*i)->Matches( attr ) )
621 //=====================================================================
623 //=====================================================================
624 int SQLiteTreeHandler::DBLoadChildren(tree::Node* node,
627 if (node->GetLevel()+1 >= node->GetTree()->GetNumberOfLevels() )
630 GimmickMessage(2,"Loading children of '"<<node->GetLabel()
634 // If children loaded we do not have to do it but we need to recurse
635 // in order to load the children's children if necessary, and so on...
636 if (node->GetChildrenLoaded())
638 // Iterate the children
640 tree::Node::ChildrenListType::iterator i;
641 for (i = node->GetChildrenList().begin();
642 i!= node->GetChildrenList().end();
645 nbloaded += DBLoadChildren(*i,numberoflevels-1);
647 node->SetChildrenLoaded(true);
653 /// If children not loaded : do it and recurse
656 int level = node->GetLevel();
657 std::string query = "SELECT * FROM ";
659 query += GetTree().GetLevelDescriptor(level+1).GetName();
662 query += " WHERE PARENT_ID='" + node->GetAttribute("ID")
672 // std::cout << "DBLoadCh : creating node level "<<level+1<<std::endl;
675 Node* n = new Node(node);
676 for (int fld = 0; fld < q.numFields(); fld++)
678 n->UnsafeSetAttribute(q.fieldName(fld),q.getStringField(fld));
682 if ( numberoflevels != 1 )
685 nbloaded += DBLoadChildren(n, numberoflevels-1);
692 node->SetChildrenLoaded(true);
699 //=====================================================================
701 //======================================================================
702 void SQLiteTreeHandler::DBInsert(tree::Node* n)
704 GimmickMessage(2,"Inserting in DB '"<<n->GetLabel()
707 SQLAppendAttributesValues(n,val);
708 std::string insert("INSERT INTO ");
709 insert += GetTree().GetLevelDescriptor(n->GetLevel()).GetName();
710 insert += " " + val + ";";
714 // Store DB id of newly created node;
715 long lastrow = mDB->lastRowId();
716 std::stringstream ri;
717 ri << mDB->lastRowId();
718 n->SetAttribute("ID",ri.str());
720 //======================================================================
722 //======================================================================
723 /// Graft the branch defined by the attributes to the parent
724 void SQLiteTreeHandler::DBGraftToParent( tree::Node* parent,
725 const AttributeMapType& attr)
727 // std::cout <<"Grafting to parent '"<<parent->GetLabel()
730 for (int level = parent->GetLevel()+1;
731 level < GetTree().GetNumberOfLevels();
735 tree::Node* child = new tree::Node(parent,attr);
736 child->SetChildrenLoaded(true);
739 int nc = GetNumberOfChildren(parent)+1;
741 // std::cout<<"Number of children "<<parent->GetNumberOfChildren()<<std::endl;
742 std::stringstream out;
744 SetAttribute(parent,"NumberOfChildren",out.str());
747 // Set PARENT_ID if necessary
748 if ( parent->GetLevel()>0 )
749 child->SetAttribute("PARENT_ID",parent->GetAttribute("ID"));
758 //======================================================================
761 //=====================================================================
762 /// Sets an attribute of a Node
763 bool SQLiteTreeHandler::DBSetAttribute(tree::Node* n,
764 const std::string& key,
765 const std::string& value)
767 GimmickMessage(3,"Setting Attribute of '"<<n->GetLabel()<<
768 "' "<<key<<"='"<<value<<"'"<<std::endl);
770 n->SetAttribute(key,value);
771 std::string sql = "UPDATE ";
772 sql += GetTree().GetLevelDescriptor(n->GetLevel()).GetName();
777 sql += "' WHERE ID = '";
778 sql += n->GetAttribute("ID");
780 // sql += " LIMIT 1";
785 //=====================================================================
786 /// Sets an attribute of a Node
787 void SQLiteTreeHandler::DBSetAttribute(const std::string& levelDescriptor,
788 const std::string& key,
789 const std::string& value,
790 const std::string& searchParam,
791 const std::string& searchVal)
794 std::string sql = "UPDATE ";
795 sql += levelDescriptor;
805 std::cout<<sql<<std::endl;
808 //=====================================================================
809 void SQLiteTreeHandler::DBRecursiveRemoveNode(Node* node)
812 std::string query = "DELETE FROM ";
814 query += GetTree().GetLevelDescriptor(node->GetLevel()).GetName();
815 query += " WHERE ID='"+ node->GetAttribute("ID") + "';";
818 GimmickDebugMessage(2,
820 <<node->GetLabel()<<"' with ID '"
821 <<node->GetAttribute("ID")
822 <<"' in level "<< GetTree().GetLevelDescriptor(node->GetLevel()).GetName()
826 if(node->GetNumberOfChildren()!=0)
828 Node::ChildrenListType::iterator i;
829 for (i = node->GetChildrenList().begin();
830 i != node->GetChildrenList().end();
833 DBRecursiveRemoveNode((*i));
836 else if(node->GetLevel()<GetTree().GetNumberOfLevels()-1)
838 DBRecursiveRemoveNode(node->GetLevel()+1,node->GetAttribute("ID"));
842 //=====================================================================
843 void SQLiteTreeHandler::DBRecursiveRemoveNode(int level, std::string parentId)
845 std::stringstream out;
846 std::stringstream result;
847 out<<"SELECT ID FROM "<<GetTree().GetLevelDescriptor(level).GetName()<<" WHERE PARENT_ID='"<<parentId<<"'";
850 QUERYDB(out.str(),q);
854 for (int fld = 0; fld < q.numFields(); fld++)
856 result<<q.getStringField(fld)<<"#";
860 std::string res=result.str();
863 while(fin<res.size()-1)
865 fin=res.find('#',ini);
866 DBDelete(GetTree().GetLevelDescriptor(level).GetName(),"ID",res.substr(ini,fin-ini));
867 if(level<GetTree().GetNumberOfLevels()-1)
869 DBRecursiveRemoveNode(level+1,res.substr(ini,fin-ini));
877 //=====================================================================
878 void SQLiteTreeHandler::DBDelete(std::string levelDescriptor, std::string key, std::string value)
881 std::stringstream query;
882 query<<"DELETE FROM "<<levelDescriptor<<" WHERE "<<key<<"='"<<value<<"';";
884 UPDATEDB(query.str());
885 GimmickDebugMessage(2," Deleting: Query: "<<query.str()<<std::endl);
889 //=====================================================================
890 void SQLiteTreeHandler::GetAttribute(std::string levelDescriptor,
891 std::string searchParam,
892 std::string searchVal,
896 std::stringstream out;
897 std::stringstream results;
898 out<<"SELECT "<<key<<" FROM "<<levelDescriptor;
901 out<<" WHERE "<<searchParam<<"='"<<searchVal<<"'";
905 QUERYDB(out.str(),q);
910 for (int fld = 0; fld < q.numFields(); fld++)
912 results<<q.getStringField(fld);
920 result=results.str();
923 //=====================================================================
924 unsigned int SQLiteTreeHandler::GetNumberOfChildren(tree::Node* n)
928 int level = n->GetLevel();
930 if(level<GetTree().GetNumberOfLevels()&& level>0)
932 std::string query = "SELECT NumberOfChildren FROM ";
933 query += GetTree().GetLevelDescriptor(level).GetName();
936 query += " WHERE ID='" + n->GetAttribute("ID")
945 for (int fld = 0; fld < q.numFields(); fld++)
947 nb=q.getIntField(fld);
961 //=====================================================================
962 void SQLiteTreeHandler::GetTopLevelNodeId(const std::string& searchParam, const std::string& searchValue, std::string& parent_id)
964 int level=GetTree().GetNumberOfLevels()-1;
965 std::string sp=searchParam.c_str();
966 std::string sv=searchValue.c_str();
970 std::stringstream out;
971 std::stringstream results;
972 out<<"SELECT PARENT_ID FROM "<<GetTree().GetLevelDescriptor(level).GetName();
973 out<<" WHERE "<<sp<<"='"<<sv<<"'";
975 QUERYDB(out.str(),q);
980 for (int fld = 0; fld < q.numFields(); fld++)
982 results<<q.getStringField(fld);
994 //=====================================================================
995 void SQLiteTreeHandler::RemoveEntries(const std::string i_table,
996 const std::string i_attribute,
997 const std::string i_operand,
998 const std::string i_val)
1000 std::stringstream query;
1001 query<<"DELETE FROM "<<i_table<<" WHERE "<<i_attribute<<" "<<i_operand<<" '"<<i_val<<"'";
1002 UPDATEDB(query.str());
1005 //=====================================================================
1006 void SQLiteTreeHandler::BeginTransaction()
1008 std::stringstream out;
1009 out<<"begin transaction;";
1010 UPDATEDB(out.str());
1013 //=====================================================================
1014 void SQLiteTreeHandler::EndTransaction()
1016 std::stringstream out;
1017 out<<"commit transaction;";
1018 UPDATEDB(out.str());
1021 } // namespace creaImageIO