Create category feature is tested

2 files changed, 296 insertions, 17 deletions

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 0da7842..9796632 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
@@ -113,6 +113,7 @@ vector<poi_category_common_t> sqlRequest::getAvailableCategories(POIServiceTypes
             }
             else
             {
+                poiCategory.attributeList.clear();
                 for (sub_index = 0; sub_index <additionnal_query_result.size(); sub_index++)
                 {
                     additionnal_query_line = additionnal_query_result.at(sub_index);
@@ -213,16 +214,9 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::createCategory(POIServiceTypes::CAMCa
     size_t index;
     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
-
-    //Check if all the parent categories exist
-    for (index=0;index < category.details.parentsId.size();index++)
-    {
-        ret = checkIfCategoryIdExist(category.details.parentsId.at(index));
-        if (ret != OK)
-            return ret;
-    }
+    recordId_t recordId;
+    iconId_t iconId;
 
     //Check if the name doesn't exist into the database to avoid duplications
     ret = checkIfCategoryNameDoesntExist(category.details.name);
@@ -273,20 +267,136 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::createCategory(POIServiceTypes::CAMCa
                 ret = DATABASE_ACCESS_ERROR;
                 return ret;
             }
-
         }
         else
         {
             if (ret != OK)
+                return ret;
+        }
+
+        //Complete the table of attributes for the category (hasattribute)
+        ret = getFreeRecordId(m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_HAS_ATTRIBUTE,recordId);
+        if (ret != OK)
+            return ret;
+        //Create the entry into the table hasattribute
+        sqlQuery = m_SQL_REQUEST_INSERT_HAS_ATTRIBUTE;
+        strStream.str("");
+        strStream << recordId;
+        sqlQuery += strStream.str();
+        sqlQuery += ",";
+        strStream.str("");
+        strStream << unique_id;
+        sqlQuery += strStream.str();
+        sqlQuery += ",";
+        strStream.str("");
+        strStream << (category.attributes.at(index)).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;
         }
     }
 
-    //Complete the table of attributes for the category (hasattribute)
+    //Check if all the parent categories exist and complete the table of family categories (poicategorykinship)
+    for (index=0;index < category.details.parentsId.size();index++)
+    {
+        ret = checkIfCategoryExist(category.details.parentsId.at(index));
+        if (ret != OK)
+            return ret;
 
-    //Complete the table of family categories (poicategorykinship)
+        ret = getFreeRecordId(m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_POI_CATEGORY_KINSHIP,recordId);
+        if (ret != OK)
+            return ret;
+        //Create the entry into the table poicategorykinship
+        sqlQuery = m_SQL_REQUEST_INSERT_POI_CATEGORY_KINSHIP;
+        strStream.str("");
+        strStream << recordId;
+        sqlQuery += strStream.str();
+        sqlQuery += ",";
+        strStream.str("");
+        strStream << unique_id;
+        sqlQuery += strStream.str();
+        sqlQuery += ",";
+        strStream.str("");
+        strStream << category.details.parentsId.at(index);
+        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;
+        }
+    }
 
     //Complete the table of icons (iconset and isdisplayedas)
+    //Get a free id (iconset)
+    ret = getFreeIconId(iconId);
+    if (ret != OK)
+        return ret;
+
+    //Create the icon
+    sqlQuery = m_SQL_REQUEST_INSERT_ICON;
+    strStream.str("");
+    strStream << iconId;
+    sqlQuery += strStream.str();
+    sqlQuery += ",";
+    strStream.str("");
+    strStream << ICON_WIDTH;
+    sqlQuery += strStream.str();
+    sqlQuery += ",";
+    strStream.str("");
+    strStream << ICON_HEIGHT;
+    sqlQuery += strStream.str();
+    sqlQuery += ",'";
+    sqlQuery.append(ICON_URL);
+    strStream.str("");
+    strStream << iconId;
+    sqlQuery += strStream.str();
+    sqlQuery += "','";
+    sqlQuery.append(ICON_FORMAT);
+    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;
+    }
+
+    ret = getFreeRecordId(m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_IS_DISPLAYED_HAS,recordId);
+    if (ret != OK)
+        return ret;
+    //Create the entry into the table isdisplayedas
+    sqlQuery = m_SQL_REQUEST_INSERT_IS_DISPLAYED_HAS;
+    strStream.str("");
+    strStream << recordId;
+    sqlQuery += strStream.str();
+    sqlQuery += ",";
+    strStream.str("");
+    strStream << unique_id;
+    sqlQuery += strStream.str();
+    sqlQuery += ",";
+    strStream.str("");
+    strStream << iconId;
+    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;
@@ -329,7 +439,7 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::checkIfCategoryNameDoesntExist(std::s
     return ret;
 }
 
-sqlRequest::SQL_REQUEST_ERRORS sqlRequest::checkIfCategoryIdExist(POIServiceTypes::CategoryID unique_id)
+sqlRequest::SQL_REQUEST_ERRORS sqlRequest::checkIfCategoryExist(POIServiceTypes::CategoryID unique_id)
 {
     std::string sqlQuery; //SQL request on database
     vector<vector<string> > query_result;
@@ -374,6 +484,30 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::removeCategory(POIServiceTypes::Categ
 
 }
 
+sqlRequest::SQL_REQUEST_ERRORS sqlRequest::getFreePoiId(POIServiceTypes::POI_ID &unique_id)
+{
+    sqlRequest::SQL_REQUEST_ERRORS ret;
+    vector<vector<string> > query_result;
+    vector<string >  query_line;
+
+    // retrieve the next free category id
+    query_result = mp_database->queryNotUTF(m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_POI_ID);
+    if (query_result.empty())
+    {
+        onError(); //database is not well populated
+        //todo something with table ?
+        ret = DATABASE_ACCESS_ERROR;
+    }
+    else
+    { // Id
+        query_line = query_result.at(0);
+        fromString<categoryId_t>(unique_id,query_line[0], std::dec);
+        ret = OK;
+    }
+
+    return ret;
+}
+
 sqlRequest::SQL_REQUEST_ERRORS sqlRequest::getFreeCategoryId(POIServiceTypes::CategoryID& unique_id)
 {
     sqlRequest::SQL_REQUEST_ERRORS ret;
@@ -422,6 +556,54 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::getFreeAttributeId(POIServiceTypes::A
     return ret;
 }
 
+sqlRequest::SQL_REQUEST_ERRORS sqlRequest::getFreeIconId(iconId_t &unique_id)
+{
+    sqlRequest::SQL_REQUEST_ERRORS ret;
+    vector<vector<string> > query_result;
+    vector<string >  query_line;
+
+    // retrieve the next free category id
+    query_result = mp_database->queryNotUTF(m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_ICON_ID);
+    if (query_result.empty())
+    {
+        onError(); //database is not well populated
+        //todo something with table ?
+        ret = DATABASE_ACCESS_ERROR;
+    }
+    else
+    { // Id
+        query_line = query_result.at(0);
+        fromString<iconId_t>(unique_id,query_line[0], std::dec);
+        ret = OK;
+    }
+
+    return ret;
+}
+
+sqlRequest::SQL_REQUEST_ERRORS sqlRequest::getFreeRecordId(const char* request, recordId_t &unique_id)
+{
+    sqlRequest::SQL_REQUEST_ERRORS ret;
+    vector<vector<string> > query_result;
+    vector<string >  query_line;
+
+    // retrieve the next free category id
+    query_result = mp_database->queryNotUTF(request);
+    if (query_result.empty())
+    {
+        onError(); //database is not well populated
+        //todo something with table ?
+        ret = DATABASE_ACCESS_ERROR;
+    }
+    else
+    { // Id
+        query_line = query_result.at(0);
+        fromString<recordId_t>(unique_id,query_line[0], std::dec);
+        ret = OK;
+    }
+
+    return ret;
+}
+
 sqlRequest::SQL_REQUEST_ERRORS sqlRequest::checkIfAttributeExist(POIServiceTypes::AttributeID unique_id)
 {
     std::string sqlQuery; //SQL request on database
@@ -457,6 +639,75 @@ sqlRequest::SQL_REQUEST_ERRORS sqlRequest::checkIfAttributeExist(POIServiceTypes
     return ret;
 }
 
+sqlRequest::SQL_REQUEST_ERRORS sqlRequest::createPoi(POIServiceTypes::CategoryID categoryId, POIServiceTypes::PoiAddedDetails poi, POIServiceTypes::POI_ID &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
+    recordId_t recordId;
+    poiproviderId_t poiproviderId;
+
+    //Get a free id (poi)
+    ret = getFreePoiId(unique_id);
+    if (ret != OK)
+        return ret;
+
+    //Complete the table belongsto
+
+    // Get the id of the default POI_PROVIDER
+    sqlQuery = m_SQL_REQUEST_GET_POI_PROVIDER_ID;
+    sqlQuery += "'";
+    sqlQuery.append(POI_PROVIDER);
+    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;
+    }
+    else
+    { // Id
+        query_line = query_result.at(0);
+        fromString<poiproviderId_t>(poiproviderId,query_line[0], std::dec);
+    }
+
+    ret = getFreeRecordId(m_SQL_REQUEST_GET_AVAILABLE_NEXT_FREE_BELONGS_TO,recordId);
+    if (ret != OK)
+        return ret;
+    //Create the entry into the table belongsto
+    sqlQuery = m_SQL_REQUEST_INSERT_BELONGSTO;
+    strStream.str("");
+    strStream << recordId;
+    sqlQuery += strStream.str();
+    sqlQuery += ",";
+    strStream.str("");
+    strStream << unique_id;
+    sqlQuery += strStream.str();
+    sqlQuery += ",";
+    strStream.str("");
+    strStream << categoryId;
+    sqlQuery += strStream.str();
+    sqlQuery += ",";
+    strStream.str("");
+    strStream << poiproviderId;
+    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;
+}
+
 PoiManagerServerStub::PoiManagerServerStub() {
     m_version.versionMajor = 1;
     m_version.versionMicro = 0;
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 c5bb1f9..abeb85e 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
@@ -56,31 +56,53 @@ public:
         OK
     } SQL_REQUEST_ERRORS;
 
+    #define ICON_WIDTH 32
+    #define ICON_HEIGHT 37
+    #define ICON_URL "../resource/file"
+    #define ICON_FORMAT "png"
+    #define POI_PROVIDER "OpenStreetMap"
+
+    typedef uint32_t recordId_t;
+    typedef uint32_t iconId_t;
+    typedef uint32_t poiproviderId_t;
+
     sqlRequest();
     ~sqlRequest();
     SQL_REQUEST_ERRORS setDatabase(const char* poiDatabaseFileName);
     vector<poi_category_common_t> getAvailableCategories(POIServiceTypes::CategoryID& rootCategory);
     SQL_REQUEST_ERRORS createCategory(POIServiceTypes::CAMCategory category,POIServiceTypes::CategoryID& unique_id);
     SQL_REQUEST_ERRORS removeCategory(POIServiceTypes::CategoryID unique_id);
+    SQL_REQUEST_ERRORS createPoi(POIServiceTypes::CategoryID categoryId, POIServiceTypes::PoiAddedDetails poi, POIServiceTypes::POI_ID& unique_id);
 
 private:
-    const char* m_SQL_REQUEST_GET_AVAILABLE_CATEGORIES = "SELECT Id,name FROM poicategory WHERE Id IN (SELECT poicategory_Id FROM belongsto GROUP BY poicategory_Id);";
+    const char* m_SQL_REQUEST_GET_AVAILABLE_CATEGORIES = "SELECT Id,name FROM poicategory;";
+    const char* m_SQL_REQUEST_GET_CATEGORIES_USED_BY_POI = "SELECT Id,name FROM poicategory WHERE Id IN (SELECT poicategory_Id FROM belongsto GROUP BY poicategory_Id);";
     const char* m_SQL_REQUEST_GET_CATEGORY_ATTRIBUTES = "SELECT Id,name FROM poiattribute WHERE Id IN (SELECT poiattribute_Id FROM hasattribute WHERE poicategory_Id IS ";
     const char* m_SQL_REQUEST_GET_AVAILABLE_AREA = "SELECT leftlongitude,bottomlatitude,rightlongitude,toplatitude FROM availablearea;";
     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 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 isdisplayedas 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_INSERT_ATTRIBUTE = "INSERT INTO poiattribute VALUES (";
     const char* m_SQL_REQUEST_DELETE_ATTRIBUTE = "DELETE from poiattribute WHERE id = ";
+    const char* m_SQL_REQUEST_INSERT_HAS_ATTRIBUTE = "INSERT INTO hasattribute VALUES (";
+    const char* m_SQL_REQUEST_INSERT_POI_CATEGORY_KINSHIP = "INSERT INTO poicategorykinship VALUES (";
+    const char* m_SQL_REQUEST_INSERT_IS_DISPLAYED_HAS = "INSERT INTO isdisplayedas VALUES (";
+    const char* m_SQL_REQUEST_INSERT_ICON = "INSERT INTO iconset VALUES (";
     const char* m_SQL_REQUEST_CHECK_IF_ATTRIBUTE_ID_EXIST = "SELECT CASE WHEN EXISTS (SELECT * FROM poiattribute WHERE id = ";
-    const char* m_SQL_REQUEST_GET_POI_PROVIDER_ID = "(SELECT Id FROM poiprovider WHERE name='OpenStreetMap')";
+    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,poiprovider_Id,poicategory_Id,poi_Id) ";
 
@@ -98,14 +120,20 @@ private:
 
     SQL_REQUEST_ERRORS checkIfCategoryNameDoesntExist(std::string name);
 
-    SQL_REQUEST_ERRORS checkIfCategoryIdExist(POIServiceTypes::CategoryID unique_id);
+    SQL_REQUEST_ERRORS checkIfCategoryExist(POIServiceTypes::CategoryID unique_id);
 
     SQL_REQUEST_ERRORS checkIfAttributeExist(POIServiceTypes::AttributeID unique_id);
 
+    SQL_REQUEST_ERRORS getFreePoiId(POIServiceTypes::POI_ID &unique_id);
+
     SQL_REQUEST_ERRORS getFreeCategoryId(POIServiceTypes::CategoryID &unique_id);
 
     SQL_REQUEST_ERRORS getFreeAttributeId(POIServiceTypes::AttributeID &unique_id);
 
+    SQL_REQUEST_ERRORS getFreeRecordId(const char* request, recordId_t &unique_id);
+
+    SQL_REQUEST_ERRORS getFreeIconId(iconId_t &unique_id);
+
     void getAvailableArea();
 
     NavigationTypes::Coordinate2D m_leftBottomLocation,m_rightTopLocation;