1 #include <creaImageIOSQLiteTreeHandler.h>
2 #include <creaImageIOSystem.h>
3 #include <creaImageIOTree.h>
5 #include "CppSQLite3.h"
12 #include <boost/algorithm/string.hpp>
20 //=============================================================
21 SQLiteTreeHandler::SQLiteTreeHandler(const std::string& filename)
24 mDB = new CppSQLite3DB;
26 GimmickMessage(1,"SQLite version : "
27 <<std::string(mDB->SQLiteVersion())<< std::endl);
29 //=============================================================
31 //=============================================================
32 SQLiteTreeHandler::~SQLiteTreeHandler()
36 //=============================================================
39 //=====================================================================
42 //=====================================================================
43 bool SQLiteTreeHandler::Open(bool writable)
45 // std::cout << "***> SQLiteTreeHandler::Open('"<<GetFileName()<<"')"<<std::endl;
46 SetWritable(writable);
50 //=====================================================================
51 bool SQLiteTreeHandler::Create(bool writable)
53 // std::cout << "***> SQLiteTreeHandler::New('"<<GetFileName()<<"')"<<std::endl;
54 SetWritable(writable);
57 //=====================================================================
60 //=====================================================================
61 bool SQLiteTreeHandler::Close()
65 //=====================================================================
68 //=====================================================================
69 bool SQLiteTreeHandler::Destroy()
74 //=====================================================================
76 //=====================================================================
77 int SQLiteTreeHandler::LoadChildren(tree::Node* parent, int maxlevel)
79 if (parent==0) parent = GetTree().GetTree();
80 return DBLoadChildren(parent,maxlevel);
82 //=====================================================================
87 //=====================================================================
88 void SQLiteTreeHandler::UnLoad(tree::Node* n)
91 //=====================================================================
93 //=====================================================================
94 int SQLiteTreeHandler::AddBranch( const AttributeMapType& attr )
96 tree::Node* parent = DBGetParent(attr);
97 DBGraftToParent(parent,attr);
98 return (parent->GetLevel()+1);
101 //=====================================================================
104 //=====================================================================
105 bool SQLiteTreeHandler::Remove(tree::Node* node)
107 DBRecursiveRemoveNode(node);
109 // std::cout << "DELETE"<<std::endl;
111 tree::Node* parent=node->GetParent();
114 int nC = parent->RemoveChildrenFromList(node);
115 if(nC>0 && parent->GetLevel()>0)
117 std::stringstream out;
119 SetAttribute(parent,"NumberOfChildren",out.str());
128 if(remove&&parent->GetLevel()>0)
132 // std::cout << "DELETE OK"<<std::endl;
136 //=====================================================================
138 //=====================================================================
139 /// Sets an attribute of a Node
140 bool SQLiteTreeHandler::SetAttribute(tree::Node* n,
141 const std::string& key,
142 const std::string& value)
144 if (n==0) n=GetTree().GetTree();
145 return DBSetAttribute(n,key,value);
147 //=====================================================================
148 //=====================================================================
149 /// Sets an attribute
150 void SQLiteTreeHandler::SetAttribute(const std::string& levelDescriptor,
151 const std::string& key,
152 const std::string& value,
153 const std::string& searchParam,
154 const std::string& searchVal)
156 DBSetAttribute(levelDescriptor,key,value,searchParam, searchVal);
158 //=====================================================================
160 void SQLiteTreeHandler::DeleteTuple(std::string levelDescriptor,
161 std::string key, std::string value)
163 DBDelete(levelDescriptor,key,value);
165 //=====================================================================
179 //=====================================================================
180 // SQLite DB specific methods
181 //=====================================================================
186 //=====================================================================
187 char* format_sql(const std::string& s)
189 return sqlite3_mprintf("%q",s.c_str());
191 //=====================================================================
194 //=====================================================================
195 #define QUERYDB(QUER,RES) \
198 GimmickMessage(2,"SQL query: '"<<QUER<<"'"<<std::endl); \
199 RES = mDB->execQuery(QUER.c_str()); \
201 catch (CppSQLite3Exception& e) \
203 GimmickError("SQLite query '"<<QUER<<"' : " \
204 << e.errorCode() << ":" \
205 << e.errorMessage() ); \
208 //=====================================================================
210 //=====================================================================
211 #define UPDATEDB(UP) \
214 GimmickMessage(2,"SQL update: '"<<UP<<"'"<<std::endl); \
215 mDB->execDML(UP.c_str()); \
217 catch (CppSQLite3Exception& e) \
219 GimmickError("SQLite update '"<<UP<<"' Error : " \
220 << e.errorCode() << ":" \
221 << e.errorMessage() ); \
223 //=====================================================================
226 //=====================================================================
227 bool SQLiteTreeHandler::DBOpen()
229 GimmickMessage(1,"Opening SQLite database '"<<GetFileName()
230 <<"' ... "<<std::endl);
232 if (!boost::filesystem::exists(GetFileName()))
239 mDB->open(GetFileName().c_str());
241 catch (CppSQLite3Exception& e)
243 GimmickError("Opening '"<<GetFileName()<<"' : "
244 << e.errorCode() << ":"
245 << e.errorMessage());
248 // IMPORT TREE DESCRIPTION (AND TEST DB VALIDITY)
249 if (!DBImportTreeDescription())
254 GimmickDebugMessage(1,"Opening SQLite database '"<<GetFileName()
255 <<"' ... OK"<<std::endl);
258 //=====================================================================
260 //=====================================================================
261 bool SQLiteTreeHandler::DBCreate()
263 GimmickMessage(1,"Creating SQLite database '"<<GetFileName()
264 <<"' ... "<<std::endl);
266 if (boost::filesystem::exists(GetFileName()))
268 GimmickError(GetFileName()<<"' : "
269 << "file already exists");
276 mDB->open(GetFileName().c_str());
278 catch (CppSQLite3Exception& e)
280 GimmickError(e.errorCode() << ":"
281 << e.errorMessage() <<std::endl);
289 // Create LEVELS table
290 command = "create table LEVELS\n";
291 command += "( Name text )\n";
294 // Iterate the Levels
295 for (l=0; l<GetTree().GetNumberOfLevels(); l++)
297 command = "INSERT INTO LEVELS (Name) VALUES ('";
298 command += GetTree().GetLevelDescriptor(l).GetName();
302 // Create table of level (for level>0, i.e. not Root)
305 command = "CREATE TABLE ";
306 command += GetTree().GetLevelDescriptor(l).GetName();
307 command += "\n(\nID INTEGER PRIMARY KEY";
310 command += ",\nPARENT_ID int not null";
312 SQLAppendAttributesDefinition(l,command);
315 command += ",\nconstraint FK_PARENT foreign key (PARENT_ID) references ";
316 command += GetTree().GetLevelDescriptor(l-1).GetName();
317 command += "(ID) on delete restrict on update restrict";
323 // Add Attribute 'ID' to Description
324 GetTree().GetDescriptor().Add
325 (AttributeDescriptor( "ID",
326 "Database Identifier",
328 AttributeDescriptor::PRIVATE
333 // Add Attribute 'PARENT_ID' to Description
334 GetTree().GetDescriptor().Add
335 (AttributeDescriptor( "PARENT_ID",
336 "Database Parent Identifier",
338 AttributeDescriptor::PRIVATE
344 // Create table *_ATTRIBUTES
346 command = "CREATE TABLE ";
347 command += GetTree().GetLevelDescriptor(l).GetName();
348 command += "_Attributes\n(\n";
349 command += "Key text,\n";
350 command += "Name text,\n";
351 command += "DicomGroup int,\n";
352 command += "DicomElement int,\n";
353 command += "Flags int\n";
357 // Fill the table *_ATTRIBUTES
358 LevelDescriptor::AttributeDescriptorListType::const_iterator i;
359 for (i = GetTree().GetAttributeDescriptorList(l).begin();
360 i != GetTree().GetAttributeDescriptorList(l).end();
364 std::stringstream insert;
365 insert << "INSERT INTO "
366 << GetTree().GetLevelDescriptor(l).GetName()
367 << "_Attributes (Key,Name,DicomGroup,DicomElement,Flags) "
369 << i->GetKey() << "','"
370 << i->GetName() << "',"
371 << i->GetGroup() << ","
372 << i->GetElement() << ","
373 << i->GetFlags() << ");";
374 UPDATEDB(insert.str());
379 // Initialize the root attributes
380 GetTree().InitializeAttributeMap();
381 // Insert the root in the level 0 table
382 DBInsert(GetTree().GetTree());
385 GetTree().SetChildrenLoaded(true);
386 GimmickMessage(1,"Creating SQLite database '"<<GetFileName()
387 <<"' ... OK"<<std::endl);
390 //=====================================================================
392 //=====================================================================
393 void SQLiteTreeHandler::SQLAppendAttributesDefinition(int level,
396 LevelDescriptor::AttributeDescriptorListType::const_iterator i;
397 for (i = GetTree().GetAttributeDescriptorList(level).begin();
398 i != GetTree().GetAttributeDescriptorList(level).end();
401 // if (i->second.flags==1) continue;
407 //=====================================================================
410 //=====================================================================
411 bool SQLiteTreeHandler::DBImportTreeDescription()
413 GimmickMessage(1,"Importing tree description for database ..."
416 // Test table 'LEVELS' existence
417 if ( ! mDB->tableExists("LEVELS") )
419 GimmickMessage(1,"!! ERROR : Table 'LEVELS' does not exist"
424 tree::Descriptor& desc = GetTree().GetDescriptor();
425 // clears the existing one
429 std::string query = "SELECT * FROM LEVELS";
435 std::string name = q.getStringField(0);
436 GimmickMessage(2," * Importing level '"<<name<<"'"<<std::endl);
437 desc.Add(LevelDescriptor(name));
442 for (int level = 0; level < nblevel; ++level )
444 std::string table = GetTree().GetLevelDescriptor(level).GetName();
445 table += "_Attributes";
446 // Test table 'LEVELS' existence
447 if ( ! mDB->tableExists(table.c_str()) )
449 GimmickMessage(1,"!! ERROR : Table '"<<table<<"' does not exist"
454 std::string query = "SELECT * FROM ";
459 GimmickMessage(2," * Level '"
460 <<GetTree().GetLevelDescriptor(level).GetName()
463 // Test that ID and PARENT_ID mandatory attributes exist
464 bool ID_found = false;
465 bool PARENT_ID_found = false;
466 if (level==0) ID_found = true;
467 if (level<=1) PARENT_ID_found = true;
471 std::string key(q.getStringField(0));
472 std::string name(q.getStringField(1));
473 GimmickMessage(2," - Importing attribute '"<<key<<"' '"<<name
476 (AttributeDescriptor( key, // Key
478 q.getIntField(2), // Group
479 q.getIntField(3), // Element
480 q.getIntField(4) // Flags
486 if ( key == "PARENT_ID" )
488 PARENT_ID_found = true;
493 if ( ! (ID_found || PARENT_ID_found ) )
495 GimmickMessage(1,"!! ERROR : Table '"<<table
496 <<"' does not contain mandatory attribute ID or PARENT_ID"
504 // Create the attributes table for Root (i.e. Tree)
505 LevelDescriptor::AttributeDescriptorListType::const_iterator a;
506 for (a = GetTree().GetAttributeDescriptorList(0).begin();
507 a!= GetTree().GetAttributeDescriptorList(0).end();
511 GetTree().UnsafeSetAttribute( a->GetKey(), "" );
514 // Reading Root attributes
516 query = "SELECT * FROM ";
517 query += GetTree().GetLevelDescriptor(0).GetName();
520 for (int fld = 0; fld < q.numFields(); fld++)
522 GetTree().UnsafeSetAttribute(q.fieldName(fld),
523 q.getStringField(fld));
526 GimmickMessage(1,"Importing tree description from database ... OK"
530 //=====================================================================
532 //========================================================================
534 std::string& SQLformat(std::string& str)
536 // quote must be doubled
537 boost::algorithm::replace_all(str,"'","''");
538 // Found strange strings which contained NULL char INSIDE string
539 int i,size=str.size();
544 str = str.substr(0,i);
551 //========================================================================
553 //=====================================================================
554 void SQLiteTreeHandler::SQLAppendAttributesValues(tree::Node* n,
557 GimmickMessage(4,"SQLAppendAttributesValues"<<std::endl);
559 std::string values="";
560 tree::Node::AttributeMapType::iterator i;
561 for (i = n->GetAttributeMap().begin();
562 i != n->GetAttributeMap().end();
570 atts += "'" + i->first + "'";
571 values += "'" + SQLformat(i->second) + "'";
574 GimmickMessage(4,"'"<<i->first<<"' = '"<<i->second<<"'"<<std::endl);
576 atts[atts.size()-1]=' ';
577 values[values.size()-1]=' ';
579 str = "("+atts+") VALUES ("+values+")";
580 GimmickMessage(4,"Result = '"<<str<<"'"<<std::endl);
582 //=====================================================================
584 //=====================================================================
585 tree::Node* SQLiteTreeHandler::DBGetParent( const AttributeMapType& attr)
587 Node* parent = GetTree().GetTree();
592 // Load the children of the current parent
593 DBLoadChildren(parent);
594 // Iterate the children
595 tree::Node::ChildrenListType::const_iterator i;
596 for (i = parent->GetChildrenList().begin();
597 i!= parent->GetChildrenList().end();
600 if ( (*i)->Matches( attr ) )
611 //=====================================================================
613 //=====================================================================
614 int SQLiteTreeHandler::DBLoadChildren(tree::Node* node,
617 if (node->GetLevel()+1 >= node->GetTree()->GetNumberOfLevels() )
620 GimmickMessage(2,"Loading children of '"<<node->GetLabel()
624 // If children loaded we do not have to do it but we need to recurse
625 // in order to load the children's children if necessary, and so on...
626 if (node->GetChildrenLoaded())
628 // Iterate the children
630 tree::Node::ChildrenListType::iterator i;
631 for (i = node->GetChildrenList().begin();
632 i!= node->GetChildrenList().end();
635 nbloaded += DBLoadChildren(*i,numberoflevels-1);
637 node->SetChildrenLoaded(true);
643 /// If children not loaded : do it and recurse
646 int level = node->GetLevel();
647 std::string query = "SELECT * FROM ";
649 query += GetTree().GetLevelDescriptor(level+1).GetName();
652 query += " WHERE PARENT_ID='" + node->GetAttribute("ID")
662 // std::cout << "DBLoadCh : creating node level "<<level+1<<std::endl;
665 Node* n = new Node(node);
666 for (int fld = 0; fld < q.numFields(); fld++)
668 n->UnsafeSetAttribute(q.fieldName(fld),q.getStringField(fld));
672 if ( numberoflevels != 1 )
675 nbloaded += DBLoadChildren(n, numberoflevels-1);
682 node->SetChildrenLoaded(true);
689 //=====================================================================
691 //======================================================================
692 void SQLiteTreeHandler::DBInsert(tree::Node* n)
694 GimmickMessage(2,"Inserting in DB '"<<n->GetLabel()
697 SQLAppendAttributesValues(n,val);
698 std::string insert("INSERT INTO ");
699 insert += GetTree().GetLevelDescriptor(n->GetLevel()).GetName();
700 insert += " " + val + ";";
704 // Store DB id of newly created node;
705 long lastrow = mDB->lastRowId();
706 std::stringstream ri;
707 ri << mDB->lastRowId();
708 n->SetAttribute("ID",ri.str());
710 //======================================================================
712 //======================================================================
713 /// Graft the branch defined by the attributes to the parent
714 void SQLiteTreeHandler::DBGraftToParent( tree::Node* parent,
715 const AttributeMapType& attr)
717 // std::cout <<"Grafting to parent '"<<parent->GetLabel()
720 for (int level = parent->GetLevel()+1;
721 level < GetTree().GetNumberOfLevels();
725 tree::Node* child = new tree::Node(parent,attr);
726 child->SetChildrenLoaded(true);
729 int nc = GetNumberOfChildren(parent)+1;
731 // std::cout<<"Number of children "<<parent->GetNumberOfChildren()<<std::endl;
732 std::stringstream out;
734 SetAttribute(parent,"NumberOfChildren",out.str());
737 // Set PARENT_ID if necessary
738 if ( parent->GetLevel()>0 )
739 child->SetAttribute("PARENT_ID",parent->GetAttribute("ID"));
748 //======================================================================
751 //=====================================================================
752 /// Sets an attribute of a Node
753 bool SQLiteTreeHandler::DBSetAttribute(tree::Node* n,
754 const std::string& key,
755 const std::string& value)
757 GimmickMessage(3,"Setting Attribute of '"<<n->GetLabel()<<
758 "' "<<key<<"='"<<value<<"'"<<std::endl);
760 n->SetAttribute(key,value);
761 std::string sql = "UPDATE ";
762 sql += GetTree().GetLevelDescriptor(n->GetLevel()).GetName();
767 sql += "' WHERE ID = '";
768 sql += n->GetAttribute("ID");
770 // sql += " LIMIT 1";
775 //=====================================================================
776 /// Sets an attribute of a Node
777 void SQLiteTreeHandler::DBSetAttribute(const std::string& levelDescriptor,
778 const std::string& key,
779 const std::string& value,
780 const std::string& searchParam,
781 const std::string& searchVal)
784 std::string sql = "UPDATE ";
785 sql += levelDescriptor;
795 std::cout<<sql<<std::endl;
798 //=====================================================================
799 void SQLiteTreeHandler::DBRecursiveRemoveNode(Node* node)
802 std::string query = "DELETE FROM ";
804 query += GetTree().GetLevelDescriptor(node->GetLevel()).GetName();
805 query += " WHERE ID='"+ node->GetAttribute("ID") + "';";
808 GimmickDebugMessage(2,
810 <<node->GetLabel()<<"' with ID '"
811 <<node->GetAttribute("ID")
812 <<"' in level "<< GetTree().GetLevelDescriptor(node->GetLevel()).GetName()
816 if(node->GetNumberOfChildren()!=0)
818 Node::ChildrenListType::iterator i;
819 for (i = node->GetChildrenList().begin();
820 i != node->GetChildrenList().end();
823 DBRecursiveRemoveNode((*i));
826 else if(node->GetLevel()<GetTree().GetNumberOfLevels()-1)
828 DBRecursiveRemoveNode(node->GetLevel()+1,node->GetAttribute("ID"));
832 //=====================================================================
833 void SQLiteTreeHandler::DBRecursiveRemoveNode(int level, std::string parentId)
835 std::stringstream out;
836 std::stringstream result;
837 out<<"SELECT ID FROM "<<GetTree().GetLevelDescriptor(level).GetName()<<" WHERE PARENT_ID='"<<parentId<<"'";
840 QUERYDB(out.str(),q);
844 for (int fld = 0; fld < q.numFields(); fld++)
846 result<<q.getStringField(fld)<<"#";
850 std::string res=result.str();
853 while(fin<res.size()-1)
855 fin=res.find('#',ini);
856 DBDelete(GetTree().GetLevelDescriptor(level).GetName(),"ID",res.substr(ini,fin-ini));
857 if(level<GetTree().GetNumberOfLevels()-1)
859 DBRecursiveRemoveNode(level+1,res.substr(ini,fin-ini));
867 //=====================================================================
868 void SQLiteTreeHandler::DBDelete(std::string levelDescriptor, std::string key, std::string value)
871 std::stringstream query;
872 query<<"DELETE FROM "<<levelDescriptor<<" WHERE "<<key<<"='"<<value<<"';";
874 UPDATEDB(query.str());
875 GimmickDebugMessage(2," Deleting: Query: "<<query.str()<<std::endl);
879 //=====================================================================
880 void SQLiteTreeHandler::GetAttribute(std::string levelDescriptor,
881 std::string searchParam,
882 std::string searchVal,
886 std::stringstream out;
887 std::stringstream results;
888 out<<"SELECT "<<key<<" FROM "<<levelDescriptor;
891 out<<" WHERE "<<searchParam<<"='"<<searchVal<<"'";
895 QUERYDB(out.str(),q);
900 for (int fld = 0; fld < q.numFields(); fld++)
902 results<<q.getStringField(fld);
910 result=results.str();
913 //=====================================================================
914 unsigned int SQLiteTreeHandler::GetNumberOfChildren(tree::Node* n)
918 int level = n->GetLevel();
920 if(level<GetTree().GetNumberOfLevels()&& level>0)
922 std::string query = "SELECT NumberOfChildren FROM ";
923 query += GetTree().GetLevelDescriptor(level).GetName();
926 query += " WHERE ID='" + n->GetAttribute("ID")
935 for (int fld = 0; fld < q.numFields(); fld++)
937 nb=q.getIntField(fld);
951 //=====================================================================
952 void SQLiteTreeHandler::GetTopLevelNodeId(const std::string& searchParam, const std::string& searchValue, std::string& parent_id)
954 int level=GetTree().GetNumberOfLevels()-1;
955 std::string sp=searchParam.c_str();
956 std::string sv=searchValue.c_str();
960 std::stringstream out;
961 std::stringstream results;
962 out<<"SELECT PARENT_ID FROM "<<GetTree().GetLevelDescriptor(level).GetName();
963 out<<" WHERE "<<sp<<"='"<<sv<<"'";
965 QUERYDB(out.str(),q);
970 for (int fld = 0; fld < q.numFields(); fld++)
972 results<<q.getStringField(fld);
984 //=====================================================================
985 void SQLiteTreeHandler::RemoveEntries(const std::string i_table,
986 const std::string i_attribute,
987 const std::string i_operand,
988 const std::string i_val)
990 std::stringstream query;
991 query<<"DELETE FROM "<<i_table<<" WHERE "<<i_attribute<<" "<<i_operand<<" '"<<i_val<<"'";
992 UPDATEDB(query.str());
995 //=====================================================================
996 void SQLiteTreeHandler::BeginTransaction()
998 std::stringstream out;
999 out<<"begin transaction;";
1000 UPDATEDB(out.str());
1003 //=====================================================================
1004 void SQLiteTreeHandler::EndTransaction()
1006 std::stringstream out;
1007 out<<"commit transaction;";
1008 UPDATEDB(out.str());
1011 } // namespace creaImageIO