poi-manager-server tested (with common api interface)

8 files changed, 351 insertions, 48 deletions

diff --git a/.gitignore b/.gitignore
index 7b420d5..1d7fec6 100644
--- a/.gitignore
+++ b/.gitignore
@@ -4,6 +4,7 @@
 *-release/
 *-Release/
 bin/
+lib/
 positioning/
 build/
 
diff --git a/src/poi-service/poi-manager-server/poi-manager-server-stub.cpp b/src/poi-service/poi-manager-server/poi-manager-server-stub.cpp
index 4136866..3af886b 100644
--- a/src/poi-service/poi-manager-server/poi-manager-server-stub.cpp
+++ b/src/poi-service/poi-manager-server/poi-manager-server-stub.cpp
@@ -249,7 +249,7 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::createCategory(POIServiceTypes::CAMCa
     //Check the attributes and complete the table if necessary (poiattribute)
     for (index=0;index < category.attributes.size();index++)
     {
-        ret = checkIfAttributeExist((category.attributes.at(index)).id);
+        ret = checkIfAttributeExist((category.attributes.at(index)).id,(category.attributes.at(index)).name);
         if (ret == ATTRIBUTE_ID_NOT_EXIST)
         {
             //Create the attribute
@@ -268,7 +268,21 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::createCategory(POIServiceTypes::CAMCa
                 ret = DATABASE_ACCESS_ERROR;
                 return ret;
             }
-            //todo extend the fields of poi with this new attribute
+
+/*            //Extend the fields of poi with this new attribute
+              // to be added later: DROP management (not available in sqlite, so need a buffer table)
+              //                    how to populate poi with a more dynamically way ...
+            sqlQuery = m_SQL_REQUEST_EXTEND_TABLE_POI;
+            sqlQuery.append((category.attributes.at(index)).name);
+            sqlQuery += "' text;";
+            query_result = mp_database->queryNotUTF(sqlQuery.c_str());
+            if (!query_result.empty())
+            {
+                onError(); //database is not well populated
+                //todo something with table ?
+                ret = DATABASE_ACCESS_ERROR;
+                return ret;
+            } */
         }
         else
         {
@@ -400,7 +414,6 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::createCategory(POIServiceTypes::CAMCa
         return ret;
     }
 
-
     return ret;
 }
 
@@ -476,12 +489,50 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::checkIfCategoryExist(POIServiceTypes:
     return ret;
 }
 
+sqlRequest::SQL_REQUEST_ERRORS sqlRequest::checkIfPoiExist(POIServiceTypes::POI_ID unique_id)
+{
+    std::string sqlQuery; //SQL request on database
+    vector<vector<string> > query_result;
+    vector<string >  query_line;
+    std::ostringstream  strStream; //temporary stream used for transformation into string
+    sqlRequest::SQL_REQUEST_ERRORS ret;
+    bool retSqlRequest;
+
+    sqlQuery = m_SQL_REQUEST_CHECK_IF_POI_ID_EXIST;
+    strStream.str("");
+    strStream << unique_id;
+    sqlQuery += strStream.str();
+    sqlQuery += ")";
+    sqlQuery += m_SQL_RETURN_BOOL_VALUE;
+    query_result = mp_database->queryNotUTF(sqlQuery.c_str());
+    if (query_result.empty())
+    {
+        onError(); //database is not well populated
+        //todo something with table ?
+        ret = DATABASE_ACCESS_ERROR;
+    }
+    else
+    {
+        // read the result of the query
+        query_line = query_result.at(0);
+        fromString<bool>(retSqlRequest,query_line[0], std::dec);
+        if (retSqlRequest)
+            ret = OK;
+        else
+            ret = POI_ID_NOT_EXIST;
+    }
+    return ret;
+}
+
 sqlRequest::SQL_REQUEST_ERRORS sqlRequest::removeCategory(POIServiceTypes::CategoryID unique_id)
 {
     sqlRequest::SQL_REQUEST_ERRORS ret;
     std::string sqlQuery; //SQL request on database
     vector<vector<string> > query_result;
+    vector<string>  query_line;
     std::ostringstream  strStream; //temporary stream used for transformation into string
+    POIServiceTypes::POI_ID poi_id;
+    size_t index;
 
     //Check if the id exists into the database
     ret = checkIfCategoryExist(unique_id);
@@ -504,7 +555,7 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::removeCategory(POIServiceTypes::Categ
     }
 
     // Clean up poicategorykinship
-    sqlQuery = m_SQL_REQUEST_DELETE_POI_CATEGORY_KINSHIP;
+    sqlQuery = m_SQL_REQUEST_CLEAN_UP_CATEGORY_POI_CATEGORY_KINSHIP;
     strStream.str("");
     strStream << unique_id;
     sqlQuery += strStream.str();
@@ -519,7 +570,7 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::removeCategory(POIServiceTypes::Categ
     }
 
     // Clean up iconset
-    sqlQuery = m_SQL_REQUEST_DELETE_ICON;
+    sqlQuery = m_SQL_REQUEST_CLEAN_UP_CATEGORY_ICON;
     strStream.str("");
     strStream << unique_id;
     sqlQuery += strStream.str();
@@ -534,7 +585,22 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::removeCategory(POIServiceTypes::Categ
     }
 
     // Clean up hasattribute
-    sqlQuery = m_SQL_REQUEST_DELETE_HAS_ATTRIBUTE;
+    sqlQuery = m_SQL_REQUEST_CLEAN_UP_CATEGORY_HAS_ATTRIBUTE;
+    strStream.str("");
+    strStream << unique_id;
+    sqlQuery += strStream.str();
+    sqlQuery += ";";
+    query_result = mp_database->queryNotUTF(sqlQuery.c_str());
+    if (!query_result.empty())
+    {
+        onError(); //database is not well populated
+        //todo something with table ?
+        ret = DATABASE_ACCESS_ERROR;
+        return ret;
+    }
+
+    // Clean up orphan attributes (the ones that have no category)
+    sqlQuery = m_SQL_REQUEST_CLEAN_UP_ORPHAN_ATTRIBUTES;
     strStream.str("");
     strStream << unique_id;
     sqlQuery += strStream.str();
@@ -549,7 +615,7 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::removeCategory(POIServiceTypes::Categ
     }
 
     // Clean up isdisplayedas
-    sqlQuery = m_SQL_REQUEST_DELETE_IS_DISPLAYED_HAS;
+    sqlQuery = m_SQL_REQUEST_CLEAN_UP_CATEGORY_IS_DISPLAYED_HAS;
     strStream.str("");
     strStream << unique_id;
     sqlQuery += strStream.str();
@@ -563,7 +629,26 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::removeCategory(POIServiceTypes::Categ
         return ret;
     }
 
-    // Clean up orphan poi
+    // Clean up orphan pois
+    // Select orphans
+    sqlQuery = m_SQL_REQUEST_SELECT_ORPHAN_POIS;
+    strStream.str("");
+    strStream << unique_id;
+    sqlQuery += strStream.str();
+    sqlQuery += ";";
+    query_result = mp_database->queryNotUTF(sqlQuery.c_str());
+    // and remove it if necessary
+    if (query_result.size() != 0)
+    {
+        for (index = 0; index < query_result.size(); index++)
+        {
+            query_line = query_result.at(index);
+            fromString<POIServiceTypes::POI_ID>(poi_id,query_line.at(0), std::dec);
+            ret = removePoi(poi_id);
+            if (ret != OK)
+                return ret;
+        }
+    }
 
     return ret;
 }
@@ -688,7 +773,7 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::getFreeRecordId(const char* request,
     return ret;
 }
 
-sqlRequest::SQL_REQUEST_ERRORS sqlRequest::checkIfAttributeExist(POIServiceTypes::AttributeID unique_id)
+sqlRequest::SQL_REQUEST_ERRORS sqlRequest::checkIfAttributeExist(POIServiceTypes::AttributeID unique_id, std::string name)
 {
     std::string sqlQuery; //SQL request on database
     vector<vector<string> > query_result;
@@ -697,6 +782,7 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::checkIfAttributeExist(POIServiceTypes
     sqlRequest::SQL_REQUEST_ERRORS ret;
     bool retSqlRequest;
 
+    // check if id exists
     sqlQuery = m_SQL_REQUEST_CHECK_IF_ATTRIBUTE_ID_EXIST;
     strStream.str("");
     strStream << unique_id;
@@ -718,8 +804,32 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::checkIfAttributeExist(POIServiceTypes
         if (retSqlRequest)
             ret = OK;
         else
-            ret = ATTRIBUTE_ID_NOT_EXIST;
+        {
+            // check if name exists (that'd be an error, name must be unique)
+            sqlQuery = m_SQL_REQUEST_CHECK_IF_ATTRIBUTE_NAME_EXIST;
+            sqlQuery += name;
+            sqlQuery += "')";
+            sqlQuery += m_SQL_RETURN_BOOL_VALUE;
+            query_result = mp_database->queryNotUTF(sqlQuery.c_str());
+            if (query_result.empty())
+            {
+                onError(); //database is not well populated
+                //todo something with table ?
+                ret = DATABASE_ACCESS_ERROR;
+            }
+            else
+            {
+                // read the result of the query
+                query_line = query_result.at(0);
+                fromString<bool>(retSqlRequest,query_line[0], std::dec);
+                if (retSqlRequest)
+                    ret = ATTRIBUTE_NAME_ALREADY_EXIST;
+                else
+                    ret = ATTRIBUTE_ID_NOT_EXIST;
+            }
+        }
     }
+
     return ret;
 }
 
@@ -802,6 +912,7 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::createPoi(POIServiceTypes::CategoryID
     }
 
     //Create the poi with the associated data
+    //CREDIT_CARD and later not implemented yet, limited to 18 column
     //Example INSERT INTO poi VALUES (1,'mySweetHome',48.7798390,2.2172600,120,0,0,'Genivi','','',5,'','','',0,'Velizy','','');
     sqlQuery = m_SQL_REQUEST_INSERT_POI;
     strStream.str("");
@@ -857,8 +968,7 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::createPoi(POIServiceTypes::CategoryID
     sqlQuery.append(poiRecorded.brand); // brand
     sqlQuery += "','";
     sqlQuery.append(poiRecorded.operateur); // operateur
-    sqlQuery += "')";
-    sqlQuery += m_SQL_RETURN_BOOL_VALUE;
+    sqlQuery += "');";
     query_result = mp_database->queryNotUTF(sqlQuery.c_str());
     if (!query_result.empty())
     {
@@ -921,17 +1031,98 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::createPoi(POIServiceTypes::CategoryID
         return ret;
     }
 
+    // Complete the table isshownas
+    ret = getFreeRecordId(m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_IS_SHOWN_HAS,recordId);
+    if (ret != OK)
+        return ret;
+    //Create the entry into the table isshownas
+    sqlQuery = m_SQL_REQUEST_INSERT_IS_SHOWN_HAS;
+    strStream.str("");
+    strStream << recordId;
+    sqlQuery += strStream.str();
+    sqlQuery += ",";
+    strStream.str("");
+    strStream << unique_id;
+    sqlQuery += strStream.str();
+    sqlQuery += ",";
+    strStream.str("");
+    strStream << MEDIASET;
+    sqlQuery += strStream.str();
+    sqlQuery += ");";
+    query_result = mp_database->queryNotUTF(sqlQuery.c_str());
+    if (!query_result.empty())
+    {
+        onError(); //database is not well populated
+        //todo something with table ?
+        ret = DATABASE_ACCESS_ERROR;
+        return ret;
+    }
+
     return ret;
 }
 
 sqlRequest::SQL_REQUEST_ERRORS sqlRequest::removePoi(POIServiceTypes::POI_ID unique_id)
 {
     sqlRequest::SQL_REQUEST_ERRORS ret;
+    std::string sqlQuery; //SQL request on database
+    vector<vector<string> > query_result;
+    std::ostringstream  strStream; //temporary stream used for transformation into string
+
+    //Check if the id exists into the database
+    ret = checkIfPoiExist(unique_id);
+    if (ret != OK)
+        return ret;
+
+    // Remove poi
+    sqlQuery = m_SQL_REQUEST_DELETE_POI;
+    strStream.str("");
+    strStream << unique_id;
+    sqlQuery += strStream.str();
+    sqlQuery += ";";
+    query_result = mp_database->queryNotUTF(sqlQuery.c_str());
+    if (!query_result.empty())
+    {
+        onError(); //database is not well populated
+        //todo something with table ?
+        ret = DATABASE_ACCESS_ERROR;
+        return ret;
+    }
+
+    // Clean up belongsto
+    sqlQuery = m_SQL_REQUEST_CLEAN_UP_POI_BELONGSTO;
+    strStream.str("");
+    strStream << unique_id;
+    sqlQuery += strStream.str();
+    sqlQuery += ";";
+    query_result = mp_database->queryNotUTF(sqlQuery.c_str());
+    if (!query_result.empty())
+    {
+        onError(); //database is not well populated
+        //todo something with table ?
+        ret = DATABASE_ACCESS_ERROR;
+        return ret;
+    }
+
+
+    // Clean up isshownas
+    sqlQuery = m_SQL_REQUEST_CLEAN_UP_POI_IS_SHOWN_HAS;
+    strStream.str("");
+    strStream << unique_id;
+    sqlQuery += strStream.str();
+    sqlQuery += ";";
+    query_result = mp_database->queryNotUTF(sqlQuery.c_str());
+    if (!query_result.empty())
+    {
+        onError(); //database is not well populated
+        //todo something with table ?
+        ret = DATABASE_ACCESS_ERROR;
+        return ret;
+    }
 
     return ret;
 }
 
-sqlRequest::SQL_REQUEST_ERRORS sqlRequest::searchPoi(string &categoryName, string &search_string, NavigationTypes::Coordinate3D &left_bottom_location, NavigationTypes::Coordinate3D &right_top_location, std::vector<POIServiceTypes::POI_ID> poi_id_list)
+sqlRequest::SQL_REQUEST_ERRORS sqlRequest::searchPoi(string &categoryName, string &search_string, NavigationTypes::Coordinate3D &left_bottom_location, NavigationTypes::Coordinate3D &right_top_location, std::vector<POIServiceTypes::POI_ID> &poi_id_list)
 {
     sqlRequest::SQL_REQUEST_ERRORS ret;
     std::string sqlQuery; //SQL request on database
@@ -940,6 +1131,7 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::searchPoi(string &categoryName, strin
     std::ostringstream  strStream; //temporary stream used for transformation into string
     size_t index;
     NavigationTypes::Coordinate3D recordedBottom, recordedTop;
+    POIServiceTypes::POI_ID poi_id;
 
     // example of query     SELECT Id FROM poi
     //                      WHERE (Id IN (SELECT poi_Id FROM belongsto,poicategory
@@ -1002,7 +1194,8 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::searchPoi(string &categoryName, strin
         for (index = 0; index < query_result.size(); index++)
         {
             query_line = query_result.at(index);
-            fromString<POIServiceTypes::POI_ID>(poi_id_list[index],query_line.at(0), std::dec);
+            fromString<POIServiceTypes::POI_ID>(poi_id,query_line.at(0), std::dec);
+            poi_id_list.push_back(poi_id);
         }
         ret = OK;
     }
@@ -1130,12 +1323,44 @@ void PoiManagerServerStub::removeCategories(const std::shared_ptr<CommonAPI::Cli
 
 void PoiManagerServerStub::addPOIs(const std::shared_ptr<CommonAPI::ClientId> clientId, POIServiceTypes::CategoryID unique_id, std::vector<POIServiceTypes::PoiAddedDetails> poiList)
 {
+    size_t index;
+    std::vector<POIServiceTypes::POI_ID> addedPoiList;
+    POIServiceTypes::POI_ID poiId;
+
+    for(index=0;index<poiList.size();index++)
+    {
+        if (mp_sqlRequest->createPoi(unique_id,poiList.at(index),poiId) != sqlRequest::OK)
+            break;
+        addedPoiList.push_back(poiId);
+    }
+    if (index<poiList.size())
+    { //it failed
+//to do something
+    }
+    else
+    {
+        firePOIAddedEvent(addedPoiList);
+    }
 
 }
 
 void PoiManagerServerStub::removePOIs(const std::shared_ptr<CommonAPI::ClientId> clientId, std::vector<POIServiceTypes::POI_ID> ids)
 {
+    size_t index;
 
+    for(index=0;index<ids.size();index++)
+    {
+        if (mp_sqlRequest->removePoi(ids.at(index)) != sqlRequest::OK)
+            break;
+    }
+    if (index<ids.size())
+    { //it failed
+//to do something
+    }
+    else
+    {
+        firePOIRemovedEvent(ids);
+    }
 }
 
 bool PoiManagerServerStub::initDatabase(const char* poiDatabaseFileName)
@@ -1143,7 +1368,14 @@ bool PoiManagerServerStub::initDatabase(const char* poiDatabaseFileName)
     mp_sqlRequest->setDatabase(poiDatabaseFileName);
     refreshCategoryList(); //read the database and buffer the category list locally
 
-    // test: create a new category and add a poi under this category
+//    return test();
+
+    return true; //maybe add some check of the file here
+}
+
+bool PoiManagerServerStub::test()
+{
+    // test: create a new category, with a new attribute and add a poi under this category
     POIServiceTypes::CAMCategory category;
     POIServiceTypes::CategoryAttribute category_attribute;
     POIServiceTypes::CategoryID category_id;
@@ -1153,12 +1385,12 @@ bool PoiManagerServerStub::initDatabase(const char* poiDatabaseFileName)
     NavigationTypes::Coordinate3D left_bottom_location,right_top_location;
     std::vector<POIServiceTypes::POI_ID> poi_id_list;
     std::string str;
-    category.details.name = "recreation";
+    category.details.name = "recreation";               //new category
     category_attribute.id = ATTRIBUTE_PHONE;
-    category_attribute.name = "phone";
+    category_attribute.name = "phone";                  //existing attribute
     category.attributes.push_back(category_attribute);
-    category_attribute.id = ATTRIBUTE_CREDIT_CARD;
-    category_attribute.name = "credit card";
+    category_attribute.id = ATTRIBUTE_CREDIT_CARD;      //new attribute id
+    category_attribute.name = "credit card";            //new attribute
     category.attributes.push_back(category_attribute);
     category.details.parentsId.push_back(0);
     poi.name = POI_NAME;
@@ -1177,6 +1409,8 @@ bool PoiManagerServerStub::initDatabase(const char* poiDatabaseFileName)
     left_bottom_location.longitude = 2.22;
     right_top_location.latitude = 48.78;
     right_top_location.longitude = 2.20;
+
+    // Create category, create poi, search, remove poi, remove category
     if (mp_sqlRequest->createCategory(category,category_id) != sqlRequest::OK)
         return false;
 
@@ -1189,15 +1423,33 @@ bool PoiManagerServerStub::initDatabase(const char* poiDatabaseFileName)
     if (mp_sqlRequest->searchPoi(category.details.name,str,left_bottom_location,right_top_location,poi_id_list) != sqlRequest::OK)
         return false;
 
-//    if (mp_sqlRequest->removePoi(poi_id) != sqlRequest::OK)
-//        return false;
+    if (mp_sqlRequest->removePoi(poi_id) != sqlRequest::OK)
+        return false;
 
     if (mp_sqlRequest->removeCategory(category_id) != sqlRequest::OK)
         return false;
 
     refreshCategoryList(); //read the database and buffer the category list locally
 
-    return true; //maybe add some check of the file here
+    // Create category, create poi,  remove category (auto remove orphan poi), search
+    if (mp_sqlRequest->createCategory(category,category_id) != sqlRequest::OK)
+        return false;
+
+    refreshCategoryList(); //read the database and buffer the category list locally
+
+    if (mp_sqlRequest->createPoi(category_id,poi,poi_id) != sqlRequest::OK)
+        return false;
+
+    if (mp_sqlRequest->removeCategory(category_id) != sqlRequest::OK)
+        return false;
+
+    refreshCategoryList(); //read the database and buffer the category list locally
+
+    str = SEARCH_STRING;
+    if (mp_sqlRequest->searchPoi(category.details.name,str,left_bottom_location,right_top_location,poi_id_list) != sqlRequest::POI_ID_NOT_EXIST)
+        return false;
+
+    return true;
 }
 
 // refresh the buffer that contains the categories related data, called each time a category is added or removed
diff --git a/src/poi-service/poi-manager-server/poi-manager-server-stub.h b/src/poi-service/poi-manager-server/poi-manager-server-stub.h
index 462cc86..e0c268c 100644
--- a/src/poi-service/poi-manager-server/poi-manager-server-stub.h
+++ b/src/poi-service/poi-manager-server/poi-manager-server-stub.h
@@ -51,6 +51,7 @@ public:
         CATEGORY_NAME_ALREADY_EXIST,
         CATEGORY_ID_NOT_EXIST,
         ATTRIBUTE_ID_NOT_EXIST,
+        ATTRIBUTE_NAME_ALREADY_EXIST,
         PARENT_CATEGORY_NOT_EXIST,
         POI_ID_NOT_EXIST,
         DATABASE_ACCESS_ERROR,
@@ -64,6 +65,7 @@ public:
     #define POI_PROVIDER "OpenStreetMap"
     #define POI_NAME "mySweetHome"
     #define SEARCH_STRING "Sweet"
+    #define MEDIASET 1
 
     typedef uint32_t recordId_t;
     typedef uint32_t iconId_t;
@@ -92,7 +94,7 @@ public:
     SQL_REQUEST_ERRORS removeCategory(POIServiceTypes::CategoryID unique_id);
     SQL_REQUEST_ERRORS createPoi(POIServiceTypes::CategoryID categoryId, POIServiceTypes::PoiAddedDetails poi, POIServiceTypes::POI_ID& unique_id);
     SQL_REQUEST_ERRORS removePoi(POIServiceTypes::POI_ID unique_id);
-    SQL_REQUEST_ERRORS searchPoi(string &categoryName, string &search_string, NavigationTypes::Coordinate3D &left_bottom_location, NavigationTypes::Coordinate3D &right_top_location, std::vector<POIServiceTypes::POI_ID> poi_id_list);
+    SQL_REQUEST_ERRORS searchPoi(string &categoryName, string &search_string, NavigationTypes::Coordinate3D &left_bottom_location, NavigationTypes::Coordinate3D &right_top_location, std::vector<POIServiceTypes::POI_ID> &poi_id_list);
 
 private:
     const char* m_SQL_REQUEST_GET_AVAILABLE_CATEGORIES = "SELECT Id,name FROM poicategory;";
@@ -102,34 +104,56 @@ private:
     const char* m_SQL_REQUEST_GET_PARENT_CATEGORIES = "SELECT parentId FROM poicategorykinship WHERE childId IS ";
     const char* m_SQL_REQUEST_GET_CHILD_CATEGORIES = "SELECT childId FROM poicategorykinship WHERE parentId IS ";
     const char* m_SQL_REQUEST_GET_CATEGORY_ICONS = "SELECT url,format FROM iconset WHERE Id IS (SELECT iconset_Id FROM isdisplayedas WHERE poicategory_Id IS  ";
+
     const char* m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_CATEGORY_ID = "SELECT a.id+1 FROM poicategory a WHERE NOT EXISTS (SELECT * FROM poicategory b WHERE a.id+1 = b.id) ORDER BY a.id";
-    const char* m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_ATTRIBUTE_ID = "SELECT a.id+1 FROM poiattribute a WHERE NOT EXISTS (SELECT * FROM poiattribute b WHERE a.id+1 = b.id) ORDER BY a.id";
-    const char* m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_POI_ID = "SELECT a.id+1 FROM poi a WHERE NOT EXISTS (SELECT * FROM poi b WHERE a.id+1 = b.id) ORDER BY a.id";
-    const char* m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_ICON_ID = "SELECT a.id+1 FROM iconset a WHERE NOT EXISTS (SELECT * FROM iconset b WHERE a.id+1 = b.id) ORDER BY a.id";
-    const char* m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_HAS_ATTRIBUTE = "SELECT a.id+1 FROM hasattribute a WHERE NOT EXISTS (SELECT * FROM hasattribute b WHERE a.id+1 = b.id) ORDER BY a.id";
-    const char* m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_POI_CATEGORY_KINSHIP = "SELECT a.id+1 FROM poicategorykinship a WHERE NOT EXISTS (SELECT * FROM poicategorykinship b WHERE a.id+1 = b.id) ORDER BY a.id";
-    const char* m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_IS_DISPLAYED_HAS = "SELECT a.id+1 FROM isdisplayedas a WHERE NOT EXISTS (SELECT * FROM isdisplayedas b WHERE a.id+1 = b.id) ORDER BY a.id";
-    const char* m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_BELONGS_TO = "SELECT a.id+1 FROM belongsto a WHERE NOT EXISTS (SELECT * FROM belongsto b WHERE a.id+1 = b.id) ORDER BY a.id";
-    const char* m_SQL_REQUEST_INSERT_POI = "INSERT INTO poi VALUES (";
     const char* m_SQL_REQUEST_INSERT_CATEGORY = "INSERT INTO poicategory VALUES (";
     const char* m_SQL_REQUEST_DELETE_CATEGORY = "DELETE from poicategory WHERE id = ";
     const char* m_SQL_REQUEST_CHECK_IF_CATEGORY_ID_EXIST = "SELECT CASE WHEN EXISTS (SELECT * FROM poicategory WHERE id = ";
     const char* m_SQL_REQUEST_CHECK_IF_CATEGORY_NAME_EXIST = "SELECT CASE WHEN EXISTS (SELECT * FROM poicategory WHERE name = ";
+
+    const char* m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_POI_ID = "SELECT a.id+1 FROM poi a WHERE NOT EXISTS (SELECT * FROM poi b WHERE a.id+1 = b.id) ORDER BY a.id";
+    const char* m_SQL_REQUEST_INSERT_POI = "INSERT INTO poi VALUES (";
+    const char* m_SQL_REQUEST_DELETE_POI = "DELETE from poi WHERE Id = ";
+    const char* m_SQL_REQUEST_CHECK_IF_POI_ID_EXIST = "SELECT CASE WHEN EXISTS (SELECT * FROM poi WHERE id = ";
+    const char* m_SQL_REQUEST_SEARCH_POI = "SELECT Id FROM poi WHERE (Id IN (SELECT poi_Id FROM belongsto,poicategory WHERE (belongsto.poicategory_Id = poicategory.Id) AND (poicategory.name = '";
+    const char* m_SQL_REQUEST_SELECT_ORPHAN_POIS = "SELECT poi_Id FROM belongsto WHERE (belongsto.poicategory_Id NOT IN (SELECT Id FROM poicategory));";
+    const char* m_SQL_REQUEST_EXTEND_TABLE_POI = "ALTER TABLE poi ADD COLUMN '";
+
+    const char* m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_ATTRIBUTE_ID = "SELECT a.id+1 FROM poiattribute a WHERE NOT EXISTS (SELECT * FROM poiattribute b WHERE a.id+1 = b.id) ORDER BY a.id";
     const char* m_SQL_REQUEST_INSERT_ATTRIBUTE = "INSERT INTO poiattribute VALUES (";
     const char* m_SQL_REQUEST_DELETE_ATTRIBUTE = "DELETE from poiattribute WHERE id = ";
+    const char* m_SQL_REQUEST_CHECK_IF_ATTRIBUTE_ID_EXIST = "SELECT CASE WHEN EXISTS (SELECT * FROM poiattribute WHERE id = ";
+    const char* m_SQL_REQUEST_CHECK_IF_ATTRIBUTE_NAME_EXIST = "SELECT CASE WHEN EXISTS (SELECT * FROM poiattribute WHERE name = '";
+    const char* m_SQL_REQUEST_CLEAN_UP_ORPHAN_ATTRIBUTES = "DELETE FROM poiattribute WHERE  Id NOT IN (SELECT Id FROM poiattribute WHERE (Id IN (SELECT poiattribute_Id FROM hasattribute)));";
+
+    const char* m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_ICON_ID = "SELECT a.id+1 FROM iconset a WHERE NOT EXISTS (SELECT * FROM iconset b WHERE a.id+1 = b.id) ORDER BY a.id";
+    const char* m_SQL_REQUEST_INSERT_ICON = "INSERT INTO iconset VALUES (";
+    const char* m_SQL_REQUEST_CLEAN_UP_CATEGORY_ICON = "DELETE from iconset WHERE Id = ";
+
+    const char* m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_HAS_ATTRIBUTE = "SELECT a.id+1 FROM hasattribute a WHERE NOT EXISTS (SELECT * FROM hasattribute b WHERE a.id+1 = b.id) ORDER BY a.id";
     const char* m_SQL_REQUEST_INSERT_HAS_ATTRIBUTE = "INSERT INTO hasattribute VALUES (";
-    const char* m_SQL_REQUEST_DELETE_HAS_ATTRIBUTE = "DELETE from hasattribute WHERE poicategory_Id = ";
+    const char* m_SQL_REQUEST_CLEAN_UP_CATEGORY_HAS_ATTRIBUTE = "DELETE from hasattribute WHERE poicategory_Id = ";
+
+    const char* m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_POI_CATEGORY_KINSHIP = "SELECT a.id+1 FROM poicategorykinship a WHERE NOT EXISTS (SELECT * FROM poicategorykinship b WHERE a.id+1 = b.id) ORDER BY a.id";
     const char* m_SQL_REQUEST_INSERT_POI_CATEGORY_KINSHIP = "INSERT INTO poicategorykinship VALUES (";
-    const char* m_SQL_REQUEST_DELETE_POI_CATEGORY_KINSHIP = "DELETE from poicategorykinship WHERE childId = ";
+    const char* m_SQL_REQUEST_CLEAN_UP_CATEGORY_POI_CATEGORY_KINSHIP = "DELETE from poicategorykinship WHERE childId = ";
+
+    const char* m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_IS_DISPLAYED_HAS = "SELECT a.id+1 FROM isdisplayedas a WHERE NOT EXISTS (SELECT * FROM isdisplayedas b WHERE a.id+1 = b.id) ORDER BY a.id";
     const char* m_SQL_REQUEST_INSERT_IS_DISPLAYED_HAS = "INSERT INTO isdisplayedas VALUES (";
-    const char* m_SQL_REQUEST_DELETE_IS_DISPLAYED_HAS = "DELETE from isdisplayedas WHERE poicategory_Id = ";
-    const char* m_SQL_REQUEST_INSERT_ICON = "INSERT INTO iconset VALUES (";
-    const char* m_SQL_REQUEST_DELETE_ICON = "DELETE from iconset WHERE Id = ";
-    const char* m_SQL_REQUEST_CHECK_IF_ATTRIBUTE_ID_EXIST = "SELECT CASE WHEN EXISTS (SELECT * FROM poiattribute WHERE id = ";
+    const char* m_SQL_REQUEST_CLEAN_UP_CATEGORY_IS_DISPLAYED_HAS = "DELETE from isdisplayedas WHERE poicategory_Id = ";
+
+    const char* m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_IS_SHOWN_HAS = "SELECT a.id+1 FROM isshownas a WHERE NOT EXISTS (SELECT * FROM isshownas b WHERE a.id+1 = b.id) ORDER BY a.id";
+    const char* m_SQL_REQUEST_INSERT_IS_SHOWN_HAS = "INSERT INTO isshownas VALUES (";
+    const char* m_SQL_REQUEST_CLEAN_UP_POI_IS_SHOWN_HAS = "DELETE from isshownas WHERE poi_Id = ";
+
+    const char* m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_BELONGS_TO = "SELECT a.id+1 FROM belongsto a WHERE NOT EXISTS (SELECT * FROM belongsto b WHERE a.id+1 = b.id) ORDER BY a.id";
+    const char* m_SQL_REQUEST_INSERT_BELONGSTO = "INSERT INTO belongsto (Id,poi_Id,poicategory_Id,poiprovider_Id) VALUES (";
+    const char* m_SQL_REQUEST_CLEAN_UP_POI_BELONGSTO = "DELETE from belongsto WHERE poi_Id = ";
+    const char* m_SQL_REQUEST_CLEAN_UP_CATEGORY_BELONGSTO = "DELETE from belongsto WHERE poicategory_Id = ";
+
     const char* m_SQL_REQUEST_GET_POI_PROVIDER_ID = "SELECT Id FROM poiprovider WHERE name=";
+
     const char* m_SQL_RETURN_BOOL_VALUE = " THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END;";
-    const char* m_SQL_REQUEST_INSERT_BELONGSTO = "INSERT INTO belongsto (Id,poi_Id,poicategory_Id,poiprovider_Id) VALUES (";
-    const char* m_SQL_REQUEST_SEARCH_POI = "SELECT Id FROM poi WHERE (Id IN (SELECT poi_Id FROM belongsto,poicategory WHERE (belongsto.poicategory_Id = poicategory.Id) AND (poicategory.name = '";
 
     Database *mp_database; // database access
 
@@ -147,7 +171,9 @@ private:
 
     SQL_REQUEST_ERRORS checkIfCategoryExist(POIServiceTypes::CategoryID unique_id);
 
-    SQL_REQUEST_ERRORS checkIfAttributeExist(POIServiceTypes::AttributeID unique_id);
+    SQL_REQUEST_ERRORS checkIfAttributeExist(POIServiceTypes::AttributeID unique_id, string name);
+
+    SQL_REQUEST_ERRORS checkIfPoiExist(POIServiceTypes::POI_ID unique_id);
 
     SQL_REQUEST_ERRORS getFreePoiId(POIServiceTypes::POI_ID &unique_id);
 
@@ -207,6 +233,7 @@ private:
       return !(iss >> f >> t).fail();
     }
 
+    bool test();
 };
 
 #endif /* POIMANAGERSERVERSTUBIMPL_H_ */
diff --git a/src/poi-service/resource/poi-database-managed.db b/src/poi-service/resource/poi-database-managed.db
index 52c495e..f7ad60b 100644
--- a/src/poi-service/resource/poi-database-managed.db
+++ b/src/poi-service/resource/poi-database-managed.db
diff --git a/test/poi-service/.gitignore b/test/poi-service/.gitignore
index dbdac10..5af3146 100644
--- a/test/poi-service/.gitignore
+++ b/test/poi-service/.gitignore
@@ -12,3 +12,5 @@ Makefile
 
 
 
+
+
diff --git a/test/poi-service/CMakeLists.txt b/test/poi-service/CMakeLists.txt
index 05acffe..6579dfe 100644
--- a/test/poi-service/CMakeLists.txt
+++ b/test/poi-service/CMakeLists.txt
@@ -35,9 +35,11 @@ message(STATUS "WITH_DEBUG = ${WITH_DEBUG}")
 set(API_DIR "${CMAKE_CURRENT_SOURCE_DIR}/../../api")
 set(DBUS_GENERATED_INCLUDE_DIR "${CMAKE_CURRENT_BINARY_DIR}/dbus-include")
 set(DBUS_GENERATED_INCLUDE_DIR_POSITIONING "${CMAKE_CURRENT_BINARY_DIR}/api") # this one is for positioning
+set(COMMON_API_GENERATED_INCLUDE_DIR "${CMAKE_CURRENT_SOURCE_DIR}/../../api/franca/navigation/src-gen")
 set(POI_SRC_DIR "${CMAKE_CURRENT_SOURCE_DIR}/../../src")
 set(COMMON_DIR "${CMAKE_CURRENT_SOURCE_DIR}/poi-common")
 set(COMMON_DIR_POI_SERVER "${POI_SRC_DIR}/poi-service/poi-common")
+set(DBUS_LIB_PATH "/usr/local/lib")
 set(CMAKE_LIBRARY_OUTPUT_DIRECTORY ${CMAKE_CURRENT_SOURCE_DIR}/lib)
 set(CMAKE_RUNTIME_OUTPUT_DIRECTORY ${CMAKE_CURRENT_SOURCE_DIR}/bin)
 
@@ -56,3 +58,7 @@ add_subdirectory(poi-supplier)
 add_subdirectory(poi-client)
 
 add_subdirectory(poi-contentaccess-module)
+
+if (WITH_FRANCA_INTERFACE)
+   add_subdirectory(poi-manager-client)
+endif()
diff --git a/test/poi-service/poi-manager-client/CMakeLists.txt b/test/poi-service/poi-manager-client/CMakeLists.txt
index 3e1803c..b7fc9b5 100644
--- a/test/poi-service/poi-manager-client/CMakeLists.txt
+++ b/test/poi-service/poi-manager-client/CMakeLists.txt
@@ -41,19 +41,16 @@ pkg_check_modules(GLIBMM glibmm-2.4)
 
 # Source Files
 set(PRJ_SRC_PATH .)
-set(PRJ_COMMON_SRC_PATH ../../../src/poi-service/poi-common)
-set(PRJ_SRC_GEN_PATH ${COMMON_API_PATH_GENERATED_FILES}/org/genivi)
-FILE(GLOB PRJ_LOCAL_SRCS ${PRJ_SRC_PATH}/*.cpp)
-FILE(GLOB PRJ_COMMON_SRCS ${PRJ_COMMON_SRC_PATH}/*.cpp)
+set(PRJ_SRC_GEN_PATH ${COMMON_API_GENERATED_INCLUDE_DIR}/org/genivi)
+FILE(GLOB PRJ_LOCAL_SRCS ${CMAKE_CURRENT_SOURCE_DIR}/*.cpp)
 FILE(GLOB PRJ_STUB_GEN_SRCS ${PRJ_SRC_GEN_PATH}/${ROOT_API}/${PARENT_API}/*Stub*.cpp)
 FILE(GLOB PRJ_STUB_GEN_TYPES ${PRJ_SRC_GEN_PATH}/${ROOT_API}/${PARENT_API}/*Types*.cpp ${PRJ_SRC_GEN_PATH}/${ROOT_API}/*Types*.cpp)
 FILE(GLOB PRJ_STUB_IMPL_SRCS ${PRJ_SRC_PATH}/*stub*.cpp)
-set(PRJ_SRCS ${PRJ_LOCAL_SRCS} ${PRJ_COMMON_SRCS} ${PRJ_STUB_GEN_SRCS} ${PRJ_STUB_GEN_TYPES} ${PRJ_STUB_IMPL_SRCS})
+set(PRJ_SRCS ${PRJ_LOCAL_SRCS} ${PRJ_STUB_GEN_SRCS} ${PRJ_STUB_GEN_TYPES} ${PRJ_STUB_IMPL_SRCS})
 
 include_directories(
     ${PRJ_SRC_PATH}
-    ${PRJ_COMMON_SRC_PATH}
-    ${COMMON_API_PATH_GENERATED_FILES}
+    ${COMMON_API_GENERATED_INCLUDE_DIR}
     ${DBUS_INCLUDE_DIRS}
     ${COMMONAPI_INCLUDEDIR}/${COMMON_API_VERSION}
     ${COMMONAPI_DBUS_INCLUDEDIR}
diff --git a/test/poi-service/poi-manager-client/main.cpp b/test/poi-service/poi-manager-client/main.cpp
index 12d07d7..19ffb69 100644
--- a/test/poi-service/poi-manager-client/main.cpp
+++ b/test/poi-service/poi-manager-client/main.cpp
@@ -27,11 +27,29 @@
 #include <iostream>
 #include <thread>
 #include <CommonAPI/CommonAPI.h> //Defined in the Common API Runtime library
+#include "org/genivi/navigation/poiservice/POIContentManagerProxy.h"
 
+using namespace org::genivi::navigation::poiservice;
 
 int main()
 {
-    std::cout << "Hello World!" << std::endl;
+    // Common API data init
+    std::shared_ptr<CommonAPI::Runtime> runtime = CommonAPI::Runtime::load();
+    std::shared_ptr<CommonAPI::Factory> factory = runtime->createFactory();
+
+    const std::string& serviceAddress = "local:org.genivi.poiservice.POIContentManager:org.genivi.poiservice.POIContentManager";
+    std::shared_ptr<POIContentManagerProxyDefault> myProxy = factory->buildProxy<POIContentManagerProxy>(serviceAddress);
+
+
+    while (!myProxy->isAvailable()) {
+        usleep(10);
+    }
+
+
+    while (true) {
+        std::this_thread::sleep_for(std::chrono::seconds(5));
+    }
+
     return 0;
 }