1 #include <creaImageIOSQLiteTreeHandler.h>
2 #include <creaImageIOSystem.h>
3 #include <creaImageIOGimmick.h>
4 #include <creaImageIOTree.h>
6 #include "CppSQLite3.h"
13 #include <boost/algorithm/string.hpp>
23 //=============================================================
24 SQLiteTreeHandler::SQLiteTreeHandler(const std::string& filename)
27 mDB = new CppSQLite3DB;
29 //GimmickMessage(1,"SQLite version : " <<std::string(mDB->SQLiteVersion())<< std::endl);
31 //=============================================================
33 //=============================================================
34 SQLiteTreeHandler::~SQLiteTreeHandler()
38 //=============================================================
41 //=====================================================================
44 //=====================================================================
45 bool SQLiteTreeHandler::Open(bool writable)
47 // std::cout << "***> SQLiteTreeHandler::Open('"<<GetFileName()<<"')"<<std::endl;
48 SetWritable(writable);
52 //=====================================================================
53 bool SQLiteTreeHandler::Create(bool writable)
55 // std::cout << "***> SQLiteTreeHandler::New('"<<GetFileName()<<"')"<<std::endl;
56 SetWritable(writable);
59 //=====================================================================
62 //=====================================================================
63 bool SQLiteTreeHandler::Close()
67 //=====================================================================
70 //=====================================================================
71 bool SQLiteTreeHandler::Destroy()
76 //=====================================================================
78 //=====================================================================
79 int SQLiteTreeHandler::LoadChildren(tree::Node* parent, int maxlevel)
81 if (parent==0) parent = GetTree().GetTree();
82 return DBLoadChildren(parent,maxlevel);
84 //=====================================================================
89 //=====================================================================
90 void SQLiteTreeHandler::UnLoad(tree::Node* n)
93 //=====================================================================
95 //=====================================================================
96 int SQLiteTreeHandler::AddBranch( const AttributeMapType& attr )
98 tree::Node* parent = DBGetParent(attr);
99 DBGraftToParent(parent,attr);
100 return (parent->GetLevel()+1);
103 //=====================================================================
106 //=====================================================================
107 bool SQLiteTreeHandler::Remove(tree::Node* node)
109 DBRecursiveRemoveNode(node);
111 // std::cout << "DELETE"<<std::endl;
113 tree::Node* parent=node->GetParent();
116 int nC = parent->RemoveChildrenFromList(node);
117 if(nC>0 && parent->GetLevel()>0)
119 std::stringstream out;
121 SetAttribute(parent,"NumberOfChildren",out.str());
130 if(remove&&parent->GetLevel()>0)
134 // std::cout << "DELETE OK"<<std::endl;
138 //=====================================================================
140 //=====================================================================
141 /// Sets an attribute of a Node
142 bool SQLiteTreeHandler::SetAttribute(tree::Node* n,
143 const std::string& key,
144 const std::string& value)
146 if (n==0) n=GetTree().GetTree();
147 return DBSetAttribute(n,key,value);
149 //=====================================================================
150 //=====================================================================
151 /// Sets an attribute
152 void SQLiteTreeHandler::SetAttribute(const std::string& levelDescriptor,
153 const std::string& key,
154 const std::string& value,
155 const std::string& searchParam,
156 const std::string& searchVal)
158 DBSetAttribute(levelDescriptor,key,value,searchParam, searchVal);
160 //=====================================================================
162 void SQLiteTreeHandler::DeleteTuple(std::string levelDescriptor,
163 std::string key, std::string value)
165 DBDelete(levelDescriptor,key,value);
167 //=====================================================================
181 //=====================================================================
182 // SQLite DB specific methods
183 //=====================================================================
188 //=====================================================================
189 char* format_sql(const std::string& s)
191 return sqlite3_mprintf("%q",s.c_str());
193 //=====================================================================
196 //=====================================================================
197 #define QUERYDB(QUER,RES) \
200 GimmickMessage(2,"SQL query: '"<<QUER<<"'"<<std::endl); \
201 RES = mDB->execQuery(QUER.c_str()); \
203 catch (CppSQLite3Exception& e) \
205 GimmickError("SQLite query '"<<QUER<<"' : " \
206 << e.errorCode() << ":" \
207 << e.errorMessage() ); \
210 //=====================================================================
212 //=====================================================================
213 #define UPDATEDB(UP) \
216 GimmickMessage(2,"SQL update: '"<<UP<<"'"<<std::endl); \
217 mDB->execDML(UP.c_str()); \
219 catch (CppSQLite3Exception& e) \
221 GimmickError("SQLite update '"<<UP<<"' Error : " \
222 << e.errorCode() << ":" \
223 << e.errorMessage() ); \
225 //=====================================================================
228 //=====================================================================
229 bool SQLiteTreeHandler::DBOpen()
231 GimmickMessage(1,"Opening SQLite database '"<<GetFileName()
232 <<"' ... "<<std::endl);
234 if (!boost::filesystem::exists(GetFileName()))
241 mDB->open(GetFileName().c_str());
243 catch (CppSQLite3Exception& e)
245 GimmickError("Opening '"<<GetFileName()<<"' : "
246 << e.errorCode() << ":"
247 << e.errorMessage());
250 // IMPORT TREE DESCRIPTION (AND TEST DB VALIDITY)
251 if (!DBImportTreeDescription())
256 GimmickDebugMessage(1,"Opening SQLite database '"<<GetFileName()
257 <<"' ... OK"<<std::endl);
260 //=====================================================================
262 //=====================================================================
263 bool SQLiteTreeHandler::DBCreate()
265 GimmickMessage(1,"Creating SQLite database '"<<GetFileName()
266 <<"' ... "<<std::endl);
268 if (boost::filesystem::exists(GetFileName()))
270 GimmickError(GetFileName()<<"' : "
271 << "file already exists");
278 mDB->open(GetFileName().c_str());
280 catch (CppSQLite3Exception& e)
282 GimmickError(e.errorCode() << ":"
283 << e.errorMessage() <<std::endl);
291 // Create LEVELS table
292 command = "create table LEVELS\n";
293 command += "( Name text )\n";
296 // Iterate the Levels
297 for (l=0; l<GetTree().GetNumberOfLevels(); l++)
299 command = "INSERT INTO LEVELS (Name) VALUES ('";
300 command += GetTree().GetLevelDescriptor(l).GetName();
304 // Create table of level (for level>0, i.e. not Root)
307 command = "CREATE TABLE ";
308 command += GetTree().GetLevelDescriptor(l).GetName();
309 command += "\n(\nID INTEGER PRIMARY KEY";
312 command += ",\nPARENT_ID int not null";
314 SQLAppendAttributesDefinition(l,command);
317 command += ",\nconstraint FK_PARENT foreign key (PARENT_ID) references ";
318 command += GetTree().GetLevelDescriptor(l-1).GetName();
319 command += "(ID) on delete restrict on update restrict";
325 // Add Attribute 'ID' to Description
326 GetTree().GetDescriptor().Add
327 (AttributeDescriptor( "ID",
328 "Database Identifier",
330 AttributeDescriptor::PRIVATE
335 // Add Attribute 'PARENT_ID' to Description
336 GetTree().GetDescriptor().Add
337 (AttributeDescriptor( "PARENT_ID",
338 "Database Parent Identifier",
340 AttributeDescriptor::PRIVATE
346 // Create table *_ATTRIBUTES
348 command = "CREATE TABLE ";
349 command += GetTree().GetLevelDescriptor(l).GetName();
350 command += "_Attributes\n(\n";
351 command += "Key text,\n";
352 command += "Name text,\n";
353 command += "DicomGroup int,\n";
354 command += "DicomElement int,\n";
355 command += "Flags int\n";
359 // Fill the table *_ATTRIBUTES
360 LevelDescriptor::AttributeDescriptorListType::const_iterator i;
361 for (i = GetTree().GetAttributeDescriptorList(l).begin();
362 i != GetTree().GetAttributeDescriptorList(l).end();
366 std::stringstream insert;
367 insert << "INSERT INTO "
368 << GetTree().GetLevelDescriptor(l).GetName()
369 << "_Attributes (Key,Name,DicomGroup,DicomElement,Flags) "
371 << i->GetKey() << "','"
372 << i->GetName() << "',"
373 << i->GetGroup() << ","
374 << i->GetElement() << ","
375 << i->GetFlags() << ");";
376 UPDATEDB(insert.str());
381 // Initialize the root attributes
382 GetTree().InitializeAttributeMap();
383 // Insert the root in the level 0 table
384 DBInsert(GetTree().GetTree());
387 GetTree().SetChildrenLoaded(true);
388 GimmickMessage(1,"Creating SQLite database '"<<GetFileName()
389 <<"' ... OK"<<std::endl);
392 //=====================================================================
394 //=====================================================================
395 void SQLiteTreeHandler::SQLAppendAttributesDefinition(int level,
398 LevelDescriptor::AttributeDescriptorListType::const_iterator i;
399 for (i = GetTree().GetAttributeDescriptorList(level).begin();
400 i != GetTree().GetAttributeDescriptorList(level).end();
403 // if (i->second.flags==1) continue;
409 //=====================================================================
412 //=====================================================================
413 bool SQLiteTreeHandler::DBImportTreeDescription()
415 GimmickMessage(1,"Importing tree description for database ..."
418 // Test table 'LEVELS' existence
419 if ( ! mDB->tableExists("LEVELS") )
421 GimmickMessage(1,"!! ERROR : Table 'LEVELS' does not exist"
426 tree::Descriptor& desc = GetTree().GetDescriptor();
427 // clears the existing one
431 std::string query = "SELECT * FROM LEVELS";
437 std::string name = q.getStringField(0);
438 GimmickMessage(2," * Importing level '"<<name<<"'"<<std::endl);
439 desc.Add(LevelDescriptor(name));
444 for (int level = 0; level < nblevel; ++level )
446 std::string table = GetTree().GetLevelDescriptor(level).GetName();
447 table += "_Attributes";
448 // Test table 'LEVELS' existence
449 if ( ! mDB->tableExists(table.c_str()) )
451 GimmickMessage(1,"!! ERROR : Table '"<<table<<"' does not exist"
456 std::string query = "SELECT * FROM ";
461 GimmickMessage(2," * Level '"
462 <<GetTree().GetLevelDescriptor(level).GetName()
465 // Test that ID and PARENT_ID mandatory attributes exist
466 bool ID_found = false;
467 bool PARENT_ID_found = false;
468 if (level==0) ID_found = true;
469 if (level<=1) PARENT_ID_found = true;
473 std::string key(q.getStringField(0));
474 std::string name(q.getStringField(1));
475 GimmickMessage(2," - Importing attribute '"<<key<<"' '"<<name
478 (AttributeDescriptor( key, // Key
480 q.getIntField(2), // Group
481 q.getIntField(3), // Element
482 q.getIntField(4) // Flags
488 if ( key == "PARENT_ID" )
490 PARENT_ID_found = true;
495 if ( ! (ID_found || PARENT_ID_found ) )
497 GimmickMessage(1,"!! ERROR : Table '"<<table
498 <<"' does not contain mandatory attribute ID or PARENT_ID"
506 // Create the attributes table for Root (i.e. Tree)
507 LevelDescriptor::AttributeDescriptorListType::const_iterator a;
508 for (a = GetTree().GetAttributeDescriptorList(0).begin();
509 a!= GetTree().GetAttributeDescriptorList(0).end();
513 GetTree().UnsafeSetAttribute( a->GetKey(), "" );
516 // Reading Root attributes
518 query = "SELECT * FROM ";
519 query += GetTree().GetLevelDescriptor(0).GetName();
522 for (int fld = 0; fld < q.numFields(); fld++)
524 GetTree().UnsafeSetAttribute(q.fieldName(fld),
525 q.getStringField(fld));
528 GimmickMessage(1,"Importing tree description from database ... OK"
532 //=====================================================================
534 //========================================================================
536 void SQLformat(std::string i_str, std::string &o_str)
538 // quote must be doubled
539 boost::algorithm::replace_all(i_str,"'","''");
540 // Found strange strings which contained NULL char INSIDE string
541 int i,size=i_str.size();
546 i_str = i_str.substr(0,i);
552 //========================================================================
554 //=====================================================================
555 void SQLiteTreeHandler::SQLAppendAttributesValues(tree::Node* n,
558 GimmickMessage(4,"SQLAppendAttributesValues"<<std::endl);
560 std::string values="";
562 tree::Node::AttributeMapType::iterator i;
563 for (i = n->GetAttributeMap().begin();
564 i != n->GetAttributeMap().end();
572 atts += "'" + i->first + "'";
573 SQLformat(i->second, out);
574 values += "'" + out + "'";
577 GimmickMessage(4,"'"<<i->first<<"' = '"<<i->second<<"'"<<std::endl);
579 atts[atts.size()-1]=' ';
580 values[values.size()-1]=' ';
582 str = "("+atts+") VALUES ("+values+")";
583 GimmickMessage(4,"Result = '"<<str<<"'"<<std::endl);
585 //=====================================================================
587 //=====================================================================
588 tree::Node* SQLiteTreeHandler::DBGetParent( const AttributeMapType& attr)
590 Node* parent = GetTree().GetTree();
595 // Load the children of the current parent
596 DBLoadChildren(parent);
597 // Iterate the children
598 tree::Node::ChildrenListType::const_iterator i;
599 for (i = parent->GetChildrenList().begin();
600 i!= parent->GetChildrenList().end();
603 if ( (*i)->Matches( attr ) )
614 //=====================================================================
616 //=====================================================================
617 int SQLiteTreeHandler::DBLoadChildren(tree::Node* node,
620 if (node->GetLevel()+1 >= node->GetTree()->GetNumberOfLevels() )
623 GimmickMessage(2,"Loading children of '"<<node->GetLabel()
627 // If children loaded we do not have to do it but we need to recurse
628 // in order to load the children's children if necessary, and so on...
629 if (node->GetChildrenLoaded())
631 // Iterate the children
633 tree::Node::ChildrenListType::iterator i;
634 for (i = node->GetChildrenList().begin();
635 i!= node->GetChildrenList().end();
638 nbloaded += DBLoadChildren(*i,numberoflevels-1);
640 node->SetChildrenLoaded(true);
646 /// If children not loaded : do it and recurse
649 int level = node->GetLevel();
650 std::string query = "SELECT * FROM ";
652 query += GetTree().GetLevelDescriptor(level+1).GetName();
655 query += " WHERE PARENT_ID='" + node->GetAttribute("ID")
658 GimmickDebugMessage(1, "query : '" <<query <<std::endl);
666 // std::cout << "DBLoadCh : creating node level "<<level+1<<std::endl;
669 Node* n = new Node(node);
670 for (int fld = 0; fld < q.numFields(); fld++)
672 n->UnsafeSetAttribute(q.fieldName(fld),q.getStringField(fld));
676 if ( numberoflevels != 1 )
679 nbloaded += DBLoadChildren(n, numberoflevels-1);
686 node->SetChildrenLoaded(true);
693 //=====================================================================
695 //======================================================================
696 void SQLiteTreeHandler::DBInsert(tree::Node* n)
698 GimmickMessage(2,"Inserting in DB '"<<n->GetLabel()
701 SQLAppendAttributesValues(n,val);
702 std::string insert("INSERT INTO ");
703 insert += GetTree().GetLevelDescriptor(n->GetLevel()).GetName();
704 insert += " " + val + ";";
708 // Store DB id of newly created node;
709 long lastrow = mDB->lastRowId();
710 std::stringstream ri;
711 ri << mDB->lastRowId();
712 n->SetAttribute("ID",ri.str());
714 //======================================================================
716 //======================================================================
717 /// Graft the branch defined by the attributes to the parent
718 void SQLiteTreeHandler::DBGraftToParent( tree::Node* parent,
719 const AttributeMapType& attr)
721 // std::cout <<"Grafting to parent '"<<parent->GetLabel()
724 for (int level = parent->GetLevel()+1;
725 level < GetTree().GetNumberOfLevels();
729 tree::Node* child = new tree::Node(parent,attr);
730 child->SetChildrenLoaded(true);
733 int nc = GetNumberOfChildren(parent)+1;
735 // std::cout<<"Number of children "<<parent->GetNumberOfChildren()<<std::endl;
736 std::stringstream out;
738 SetAttribute(parent,"NumberOfChildren",out.str());
741 // Set PARENT_ID if necessary
742 if ( parent->GetLevel()>0 )
743 child->SetAttribute("PARENT_ID",parent->GetAttribute("ID"));
752 //======================================================================
755 //=====================================================================
756 /// Sets an attribute of a Node
757 bool SQLiteTreeHandler::DBSetAttribute(tree::Node* n,
758 const std::string& key,
759 const std::string& value)
761 GimmickMessage(3,"Setting Attribute of '"<<n->GetLabel()<<
762 "' "<<key<<"='"<<value<<"'"<<std::endl);
764 n->SetAttribute(key,value);
765 std::string sql = "UPDATE ";
766 sql += GetTree().GetLevelDescriptor(n->GetLevel()).GetName();
771 sql += "' WHERE ID = '";
772 sql += n->GetAttribute("ID");
774 // sql += " LIMIT 1";
779 //=====================================================================
780 /// Sets an attribute of a Node
781 void SQLiteTreeHandler::DBSetAttribute(const std::string& levelDescriptor,
782 const std::string& key,
783 const std::string& value,
784 const std::string& searchParam,
785 const std::string& searchVal)
788 std::string sql = "UPDATE ";
789 sql += levelDescriptor;
799 std::cout<<sql<<std::endl;
802 //=====================================================================
803 void SQLiteTreeHandler::DBRecursiveRemoveNode(Node* node)
806 std::string query = "DELETE FROM ";
809 query += GetTree().GetLevelDescriptor(node->GetLevel()).GetName();
811 query += " WHERE ID='"+ node->GetAttribute("ID") + "';";
814 GimmickDebugMessage(2,
816 <<node->GetLabel()<<"' with ID '"
817 <<node->GetAttribute("ID")
818 <<"' in level "<< GetTree().GetLevelDescriptor(node->GetLevel()).GetName()
822 if(node->GetNumberOfChildren()!=0)
824 Node::ChildrenListType::iterator i;
825 for (i = node->GetChildrenList().begin();
826 i != node->GetChildrenList().end();
829 DBRecursiveRemoveNode((*i));
832 else if(node->GetLevel()<GetTree().GetNumberOfLevels()-1)
834 DBRecursiveRemoveNode(node->GetLevel()+1,node->GetAttribute("ID"));
838 //=====================================================================
839 void SQLiteTreeHandler::DBRecursiveRemoveNode(int level, std::string parentId)
841 std::stringstream out;
842 std::stringstream result;
843 out<<"SELECT ID FROM "<<GetTree().GetLevelDescriptor(level).GetName()<<" WHERE PARENT_ID='"<<parentId<<"'";
846 QUERYDB(out.str(),q);
850 for (int fld = 0; fld < q.numFields(); fld++)
852 result<<q.getStringField(fld)<<"#";
856 std::string res=result.str();
859 while(fin<res.size()-1)
861 fin=res.find('#',ini);
862 DBDelete(GetTree().GetLevelDescriptor(level).GetName(),"ID",res.substr(ini,fin-ini));
863 if(level<GetTree().GetNumberOfLevels()-1)
865 DBRecursiveRemoveNode(level+1,res.substr(ini,fin-ini));
873 //=====================================================================
874 void SQLiteTreeHandler::DBDelete(std::string levelDescriptor, std::string key, std::string value)
877 std::stringstream query;
878 query<<"DELETE FROM "<<levelDescriptor<<" WHERE "<<key<<"='"<<value<<"';";
880 UPDATEDB(query.str());
881 GimmickDebugMessage(2," Deleting: Query: "<<query.str()<<std::endl);
885 //=====================================================================
886 void SQLiteTreeHandler::GetAttribute(std::string levelDescriptor,
887 std::string searchParam,
888 std::string searchVal,
892 std::stringstream out;
893 std::stringstream results;
894 out<<"SELECT "<<key<<" FROM "<<levelDescriptor;
897 out<<" WHERE "<<searchParam<<"='"<<searchVal<<"'";
901 QUERYDB(out.str(),q);
906 for (int fld = 0; fld < q.numFields(); fld++)
908 results<<q.getStringField(fld);
916 result=results.str();
919 //=====================================================================
920 unsigned int SQLiteTreeHandler::GetNumberOfChildren(tree::Node* n)
924 int level = n->GetLevel();
926 if(level<GetTree().GetNumberOfLevels()&& level>0)
928 std::string query = "SELECT NumberOfChildren FROM ";
929 query += GetTree().GetLevelDescriptor(level).GetName();
932 query += " WHERE ID='" + n->GetAttribute("ID")
941 for (int fld = 0; fld < q.numFields(); fld++)
943 nb=q.getIntField(fld);
957 //=====================================================================
958 void SQLiteTreeHandler::GetTopLevelNodeId(const std::string& searchParam, const std::string& searchValue, std::string& parent_id)
960 int level=GetTree().GetNumberOfLevels()-1;
961 std::string sp=searchParam.c_str();
962 std::string sv=searchValue.c_str();
966 std::stringstream out;
967 std::stringstream results;
968 out<<"SELECT PARENT_ID FROM "<<GetTree().GetLevelDescriptor(level).GetName();
969 out<<" WHERE "<<sp<<"='"<<sv<<"'";
971 QUERYDB(out.str(),q);
976 for (int fld = 0; fld < q.numFields(); fld++)
978 results<<q.getStringField(fld);
990 //=====================================================================
991 void SQLiteTreeHandler::RemoveEntries(const std::string i_table,
992 const std::string i_attribute,
993 const std::string i_operand,
994 const std::string i_val)
996 std::stringstream query;
997 query<<"DELETE FROM "<<i_table<<" WHERE "<<i_attribute<<" "<<i_operand<<" '"<<i_val<<"'";
998 UPDATEDB(query.str());
1001 //=====================================================================
1002 void SQLiteTreeHandler::BeginTransaction()
1004 std::stringstream out;
1005 out<<"begin transaction;";
1006 UPDATEDB(out.str());
1009 //=====================================================================
1010 void SQLiteTreeHandler::EndTransaction()
1012 std::stringstream out;
1013 out<<"commit transaction;";
1014 UPDATEDB(out.str());
1017 } // namespace creaImageIO