[generator][ugc] UGC section.

24 files changed, 276 insertions, 186 deletions

diff --git a/3party/jansson/myjansson.cpp b/3party/jansson/myjansson.cpp
index ed59a2c7a0..34fd6ac1f7 100644
--- a/3party/jansson/myjansson.cpp
+++ b/3party/jansson/myjansson.cpp
@@ -38,6 +38,11 @@ void FromJSON(json_t * root, bool & result)
   result = json_is_true(root);
 }
 
+bool CheckJsonArray(json_t const * data)
+{
+  return data != nullptr && json_is_array(data) && json_array_size(data) > 0;
+}
+
 namespace std
 {
 void FromJSON(json_t * root, string & result)
diff --git a/3party/jansson/myjansson.hpp b/3party/jansson/myjansson.hpp
index 5a6c438d34..610ef8e870 100644
--- a/3party/jansson/myjansson.hpp
+++ b/3party/jansson/myjansson.hpp
@@ -171,6 +171,8 @@ void FromJSONObjectOptionalField(json_t * root, std::string const & field, std::
     FromJSON(json_array_get(arr, i), result[i]);
 }
 
+bool CheckJsonArray(json_t const * data);
+
 struct JSONFreeDeleter
 {
   void operator()(char * buffer) const { free(buffer); }
diff --git a/defines.hpp b/defines.hpp
index a982e36870..5bac0f9a4f 100644
--- a/defines.hpp
+++ b/defines.hpp
@@ -42,6 +42,7 @@
 #define SEARCH_TOKENS_FILE_TAG "addrtags"
 #define TRAFFIC_KEYS_FILE_TAG "traffic"
 #define TRANSIT_FILE_TAG "transit"
+#define UGC_FILE_TAG "ugc"
 
 #define ROUTING_MATRIX_FILE_TAG "mercedes"
 #define ROUTING_EDGEDATA_FILE_TAG "daewoo"
diff --git a/generator/CMakeLists.txt b/generator/CMakeLists.txt
index 184b125201..815bb5540a 100644
--- a/generator/CMakeLists.txt
+++ b/generator/CMakeLists.txt
@@ -95,6 +95,8 @@ set(SRC
   transit_generator.hpp
   ugc_db.cpp
   ugc_db.hpp
+  ugc_section_builder.cpp
+  ugc_section_builder.hpp
   ugc_translator.cpp
   ugc_translator.hpp
   unpack_mwm.cpp
diff --git a/generator/gen_mwm_info.hpp b/generator/gen_mwm_info.hpp
index 09816200ba..db3941b61e 100644
--- a/generator/gen_mwm_info.hpp
+++ b/generator/gen_mwm_info.hpp
@@ -2,9 +2,11 @@
 
 #include "generator/osm_id.hpp"
 
+#include "coding/file_reader.hpp"
 #include "coding/read_write_utils.hpp"
 
 #include "base/assert.hpp"
+#include "base/logging.hpp"
 
 #include <algorithm>
 #include <utility>
@@ -51,10 +53,9 @@ public:
     BaseT::Flush(sink);
   }
 
-  /// Find a feature id for an OSM way id. Returns 0 if the feature was not found.
-  uint32_t GetRoadFeatureID(uint64_t wayId) const
+  /// Find a feature id for an OSM id. Returns 0 if the feature was not found.
+  uint32_t GetFeatureID(osm::Id const & id) const
   {
-    osm::Id id = osm::Id::Way(wayId);
     auto const it = std::lower_bound(m_data.begin(), m_data.end(), id, LessID());
     if (it != m_data.end() && it->first == id)
       return it->second;
@@ -67,5 +68,23 @@ public:
     for (auto const & v : m_data)
       fn(v);
   }
+
+  bool ReadFromFile(std::string const & filename)
+  {
+    try
+    {
+      FileReader reader(filename);
+      ReaderSource<FileReader> src(reader);
+      Read(src);
+    }
+    catch (FileReader::Exception const & e)
+    {
+      LOG(LERROR, ("Exception while reading osm id to feature id mapping file:", filename,
+                   ". Msg:", e.Msg()));
+      return false;
+    }
+
+    return true;
+  }
 };
 }  // namespace gen
diff --git a/generator/generator.pro b/generator/generator.pro
index 7aa16a251a..f5325f8c11 100644
--- a/generator/generator.pro
+++ b/generator/generator.pro
@@ -58,6 +58,7 @@ SOURCES += \
     unpack_mwm.cpp \
     utils.cpp \
     viator_dataset.cpp \
+    ugc_section_builder.cpp
 
 HEADERS += \
     altitude_generator.hpp \
@@ -115,3 +116,4 @@ HEADERS += \
     viator_dataset.hpp \
     ways_merger.hpp \
     world_map_generator.hpp \
+    ugc_section_builder.hpp
diff --git a/generator/generator_tests/ugc_test.cpp b/generator/generator_tests/ugc_test.cpp
index a1826fb5a1..44ca9055b9 100644
--- a/generator/generator_tests/ugc_test.cpp
+++ b/generator/generator_tests/ugc_test.cpp
@@ -7,23 +7,15 @@
 #include "ugc/types.hpp"
 
 std::string g_database(R"LLL(
-   PRAGMA foreign_keys=OFF;
-   BEGIN TRANSACTION;
-   CREATE TABLE agg (id bigint, data blob);
-   INSERT INTO "agg" VALUES(1,X'7B22637269746572696F6E5F6964223A20332C202276616C7565223A20322E307D');
-   INSERT INTO "agg" VALUES(2,X'7B22637269746572696F6E5F6964223A20372C202276616C7565223A20332E307D');
-   INSERT INTO "agg" VALUES(3,X'7B22637269746572696F6E5F6964223A20382C202276616C7565223A20322E307D');
-   INSERT INTO "agg" VALUES(4,X'7B22637269746572696F6E5F6964223A2031302C202276616C7565223A20322E363636363636363636363636363636357D');
-   INSERT INTO "agg" VALUES(5,X'7B22637269746572696F6E5F6964223A2031322C202276616C7565223A20352E307D');
-   INSERT INTO "agg" VALUES(6,X'7B22637269746572696F6E5F6964223A2031342C202276616C7565223A20342E307D');
-   INSERT INTO "agg" VALUES(1,X'7B22637269746572696F6E5F6964223A206E756C6C2C202276616C7565223A20322E393238353731343238353731343238347D');
-   INSERT INTO "agg" VALUES(2,X'7B22637269746572696F6E5F6964223A206E756C6C2C202276616C7565223A20332E3033393530363137323833393530367D');
-   INSERT INTO "agg" VALUES(3,X'7B22637269746572696F6E5F6964223A206E756C6C2C202276616C7565223A20322E393735333038363431393735333038357D');
-   INSERT INTO "agg" VALUES(4,X'7B22637269746572696F6E5F6964223A206E756C6C2C202276616C7565223A20332E3037363932333037363932333037377D');
-   COMMIT;
+                       PRAGMA foreign_keys=OFF;
+                       BEGIN TRANSACTION;
+                       CREATE TABLE ratings (key bigint, value blob);
+                       INSERT INTO "ratings" VALUES(9826352,'{"osm_id":9826352,"total_rating":10.34,"votes":721,"ratings":[{"id":2,"value":3.4},{"id":2,"value":6.0001}],"reviews":[{"id":7864532,"text":"The best service on the Earth","lang":"en","author":"Robert","rating":8.5,"date":1234567}]}');
+                       INSERT INTO "ratings" VALUES(9826353,'{"osm_id":9826353,"total_rating":0.34,"votes":1,"ratings":[{"id":2,"value":3.4},{"id":3,"value":6.0001},{"id":6,"value":0.0001}],"reviews":[{"id":78645323924,"text":"Заебись!","lang":"ru","author":"Вася","rating":10,"date":1234569}]}');
+                       CREATE INDEX key_index ON ratings (key);
+                       COMMIT;
 )LLL");
 
-
 UNIT_TEST(UGC_SmokeTest)
 {
   generator::UGCDB db(":memory:");
@@ -40,14 +32,14 @@ UNIT_TEST(UGC_SmokeTest)
 UNIT_TEST(UGC_TranslateRatingTest)
 {
   generator::UGCTranslator tr;
-  tr.CreateRatings(g_database);
-  osm::Id id = osm::Id(6);
+  tr.CreateDb(g_database);
+  osm::Id id = osm::Id(9826352);
 
   ugc::UGC ugc;
   bool rc = tr.TranslateUGC(id, ugc);
   TEST(rc, ("Can't translate rating for", id));
 
-  TEST_EQUAL(ugc.m_ratings.size(), 1, ());
-  TEST_EQUAL(ugc.m_ratings[0].m_key, "TranslationKey14", ());
-  TEST_EQUAL(ugc.m_ratings[0].m_value, 4.0, ());
+  TEST_EQUAL(ugc.m_ratings.size(), 2, ());
+  TEST_EQUAL(ugc.m_ratings[0].m_key, "2", ());
+  TEST_LESS(ugc.m_ratings[0].m_value - 3.4, 1e-6, ());
 }
diff --git a/generator/generator_tool/CMakeLists.txt b/generator/generator_tool/CMakeLists.txt
index 185160383c..e6f7daf2c3 100644
--- a/generator/generator_tool/CMakeLists.txt
+++ b/generator/generator_tool/CMakeLists.txt
@@ -36,6 +36,7 @@ omim_link_libraries(
   tess2
   gflags
   oauthcpp
+  sqlite3
   ${LIBZ}
 )
 
diff --git a/generator/generator_tool/generator_tool.cpp b/generator/generator_tool/generator_tool.cpp
index 70a3bbc81d..277cb80c73 100644
--- a/generator/generator_tool/generator_tool.cpp
+++ b/generator/generator_tool/generator_tool.cpp
@@ -18,6 +18,7 @@
 #include "generator/statistics.hpp"
 #include "generator/traffic_generator.hpp"
 #include "generator/transit_generator.hpp"
+#include "generator/ugc_section_builder.hpp"
 #include "generator/unpack_mwm.hpp"
 
 #include "indexer/classificator.hpp"
@@ -99,6 +100,9 @@ DEFINE_string(opentable_data, "", "Path to opentable data in .tsv format.");
 DEFINE_string(opentable_reference_path, "", "Path to mwm dataset for opentable addresses matching.");
 DEFINE_string(viator_data, "", "Path to viator data in .tsv format.");
 
+// UGC
+DEFINE_string(ugc_data, "", "Input UGC source database file name");
+
 // Printing stuff.
 DEFINE_bool(calc_statistics, false, "Calculate feature statistics for specified mwm bucket files.");
 DEFINE_bool(type_statistics, false, "Calculate statistics by type for specified mwm bucket files.");
@@ -118,6 +122,8 @@ DEFINE_bool(generate_addresses_file, false, "Generate .addr file (for '--output'
 DEFINE_bool(generate_traffic_keys, false,
             "Generate keys for the traffic map (road segment -> speed group).");
 
+using namespace generator;
+
 int main(int argc, char ** argv)
 {
   google::SetUsageMessage(
@@ -339,6 +345,14 @@ int main(int argc, char ** argv)
         LOG(LCRITICAL, ("Error generating cross mwm section."));
     }
 
+    if (!FLAGS_ugc_data.empty())
+    {
+      if (!BuildUgcMwmSection(FLAGS_ugc_data, datFile, osmToFeatureFilename))
+      {
+        LOG(LCRITICAL, ("Error generating UGC mwm section."));
+      }
+    }
+
     if (FLAGS_generate_traffic_keys)
     {
       if (!traffic::GenerateTrafficKeysFromDataFile(datFile))
diff --git a/generator/generator_tool/generator_tool.pro b/generator/generator_tool/generator_tool.pro
index b4ff04d984..3bd25e091c 100644
--- a/generator/generator_tool/generator_tool.pro
+++ b/generator/generator_tool/generator_tool.pro
@@ -2,8 +2,8 @@
 
 ROOT_DIR = ../..
 
-DEPENDENCIES = generator routing traffic routing_common search storage indexer editor mwm_diff platform geometry \
-               coding base freetype expat jansson protobuf osrm stats_client \
+DEPENDENCIES = generator routing traffic routing_common search storage indexer editor mwm_diff ugc \
+               platform geometry coding base freetype expat jansson protobuf osrm stats_client \
                minizip succinct pugixml tess2 gflags oauthcpp icu
 include($$ROOT_DIR/common.pri)
 
@@ -18,6 +18,7 @@ TEMPLATE = app
 
 # needed for Platform::WorkingDir() and unicode combining
 QT *= core
+LIBS *= -lsqlite3
 
 !iphone*:!android*:!tizen:!macx-* {
   QT *= network
diff --git a/generator/routing_generator.cpp b/generator/routing_generator.cpp
index fdcc8f8d48..7b3595c8ad 100644
--- a/generator/routing_generator.cpp
+++ b/generator/routing_generator.cpp
@@ -35,11 +35,16 @@ using platform::LocalCountryFile;
 
 namespace routing
 {
-
 using RawRouteResult = InternalRouteResult;
 
 static double const EQUAL_POINT_RADIUS_M = 2.0;
 
+/// Find a feature id for an OSM way id. Returns 0 if the feature was not found.
+uint32_t GetRoadFeatureID(gen::OsmID2FeatureID const & osm2ft, uint64_t wayId)
+{
+  return osm2ft.GetFeatureID(osm::Id::Way(wayId));
+}
+
 // For debug purposes only. So I do not use constanst or string representations.
 uint8_t GetWarningRank(FeatureType const & ft)
 {
@@ -63,12 +68,8 @@ bool LoadIndexes(std::string const & mwmFile, std::string const & osrmFile, osrm
     LOG(LCRITICAL, ("Can't load node data"));
     return false;
   }
-  {
-    FileReader reader(mwmFile + OSM2FEATURE_FILE_EXTENSION);
-    ReaderSource<FileReader> src(reader);
-    osm2ft.Read(src);
-  }
-  return true;
+
+  return osm2ft.ReadFromFile(mwmFile + OSM2FEATURE_FILE_EXTENSION);
 }
 
 bool CheckBBoxCrossingBorder(m2::RegionD const & border, osrm::NodeData const & data)
@@ -120,7 +121,7 @@ void FindCrossNodes(osrm::NodeDataVectorT const & nodeData, gen::OsmID2FeatureID
       auto const & startSeg = data.m_segments.front();
       auto const & endSeg = data.m_segments.back();
       // Check if we have geometry for our candidate.
-      if (osm2ft.GetRoadFeatureID(startSeg.wayId) || osm2ft.GetRoadFeatureID(endSeg.wayId))
+      if (GetRoadFeatureID(osm2ft, startSeg.wayId) || GetRoadFeatureID(osm2ft, endSeg.wayId))
       {
         // Check mwm borders crossing.
         for (m2::RegionD const & border: regionBorders)
@@ -177,7 +178,7 @@ void FindCrossNodes(osrm::NodeDataVectorT const & nodeData, gen::OsmID2FeatureID
           {
             FeatureType ft;
             Index::FeaturesLoaderGuard loader(index, mwmId);
-            if (loader.GetFeatureByIndex(osm2ft.GetRoadFeatureID(startSeg.wayId), ft))
+            if (loader.GetFeatureByIndex(GetRoadFeatureID(osm2ft, startSeg.wayId), ft))
             {
               LOG(LINFO,
                   ("Double border intersection", wgsIntersection, "rank:", GetWarningRank(ft)));
@@ -313,7 +314,7 @@ void BuildRoutingIndex(std::string const & baseDir, std::string const & countryN
       ++all;
 
       // now need to determine feature id and segments in it
-      uint32_t const fID = osm2ft.GetRoadFeatureID(seg.wayId);
+      uint32_t const fID = GetRoadFeatureID(osm2ft, seg.wayId);
       if (fID == 0)
       {
         LOG(LWARNING, ("No feature id for way:", seg.wayId));
diff --git a/generator/ugc_db.cpp b/generator/ugc_db.cpp
index 0ad91153c3..494999c315 100644
--- a/generator/ugc_db.cpp
+++ b/generator/ugc_db.cpp
@@ -21,6 +21,10 @@ namespace generator
 static int callback(void * results_ptr, int argc, char ** argv, char ** azColName)
 {
   Results & results = *reinterpret_cast<Results *>(results_ptr);
+
+  if (argc > 1)
+    results.values << "[";
+
   for (size_t i = 0; i < argc; i++)
   {
     if (results.empty)
@@ -30,6 +34,10 @@ static int callback(void * results_ptr, int argc, char ** argv, char ** azColNam
 
     results.values << (argv[i] ? argv[i] : "{}");
   }
+
+  if (argc > 1)
+    results.values << "]";
+
   return 0;
 }
 
@@ -56,10 +64,8 @@ bool UGCDB::Get(osm::Id const & id, std::vector<uint8_t> & blob)
     return false;
 
   Results results;
-  results.values << "[";
-
   std::ostringstream cmd;
-  cmd << "SELECT data FROM agg WHERE id=" << id.OsmId() << ";";
+  cmd << "SELECT value FROM ratings WHERE key=" << id.OsmId() << ";";
 
   char * zErrMsg = nullptr;
   auto rc = sqlite3_exec(m_db, cmd.str().c_str(), callback, &results, &zErrMsg);
@@ -69,7 +75,6 @@ bool UGCDB::Get(osm::Id const & id, std::vector<uint8_t> & blob)
     sqlite3_free(zErrMsg);
     return false;
   }
-  results.values << "]";
 
   return ValueToBlob(results.values.str(), blob);
 }
diff --git a/generator/ugc_section_builder.cpp b/generator/ugc_section_builder.cpp
new file mode 100644
index 0000000000..0118c3a114
--- /dev/null
+++ b/generator/ugc_section_builder.cpp
@@ -0,0 +1,63 @@
+#include "generator/ugc_section_builder.hpp"
+
+#include "generator/gen_mwm_info.hpp"
+#include "generator/ugc_translator.hpp"
+
+#include "ugc/binary/index_ugc.hpp"
+#include "ugc/binary/serdes.hpp"
+
+#include "indexer/feature_data.hpp"
+#include "indexer/feature_processor.hpp"
+#include "indexer/ftraits.hpp"
+
+#include <unordered_map>
+#include <utility>
+
+namespace generator
+{
+bool BuildUgcMwmSection(std::string const & srcDbFilename, std::string const & mwmFile,
+                        std::string const & osmToFeatureFilename)
+{
+  using ugc::binary::IndexUGC;
+
+  gen::OsmID2FeatureID osmIdsToFeatureIds;
+  if (!osmIdsToFeatureIds.ReadFromFile(osmToFeatureFilename))
+    return false;
+
+  std::unordered_map<uint32_t, osm::Id> featureToOsmId;
+  osmIdsToFeatureIds.ForEach([&featureToOsmId](gen::OsmID2FeatureID::ValueT const & p) {
+    featureToOsmId.emplace(p.second /* feature id */, p.first /* osm id */);
+  });
+
+  UGCTranslator translator(srcDbFilename);
+
+  std::vector<IndexUGC> content;
+
+  feature::ForEachFromDat(mwmFile, [&](FeatureType const & f, uint32_t featureId) {
+    auto const ugcMasks = ftraits::UGC::GetValue({f});
+
+    if (!ftraits::UGC::IsUGCAvailable(ugcMasks))
+      return;
+
+    auto const it = featureToOsmId.find(featureId);
+    CHECK(it != featureToOsmId.cend(),
+          ("FeatureID", featureId, "is not found in", osmToFeatureFilename));
+
+    ugc::UGC result;
+    if (!translator.TranslateUGC(it->second, result))
+      return;
+
+    content.emplace_back(featureId, result);
+  });
+
+  if (content.empty())
+    return true;
+
+  FilesContainerW cont(mwmFile, FileWriter::OP_WRITE_EXISTING);
+  FileWriter writer = cont.GetWriter(UGC_FILE_TAG);
+  ugc::binary::UGCSeriaizer serializer(std::move(content));
+  serializer.Serialize(writer);
+
+  return true;
+}
+}  // namespace generator
diff --git a/generator/ugc_section_builder.hpp b/generator/ugc_section_builder.hpp
new file mode 100644
index 0000000000..4ddd42a601
--- /dev/null
+++ b/generator/ugc_section_builder.hpp
@@ -0,0 +1,9 @@
+#pragma once
+
+#include <string>
+
+namespace generator
+{
+bool BuildUgcMwmSection(std::string const & srcDbFilename, std::string const & mwmFile,
+                        std::string const & osmToFeatureFilename);
+}  // namespace generator
diff --git a/generator/ugc_translator.cpp b/generator/ugc_translator.cpp
index 60c172521d..c69d493a11 100644
--- a/generator/ugc_translator.cpp
+++ b/generator/ugc_translator.cpp
@@ -1,72 +1,106 @@
-#include "ugc_translator.hpp"
+#include "generator/ugc_translator.hpp"
 
-#include "ugc_db.hpp"
+#include "generator/ugc_db.hpp"
+
+#include "coding/multilang_utf8_string.hpp"
+
+#include "base/string_utils.hpp"
 
 #include "3party/jansson/myjansson.hpp"
 
-namespace generator
+namespace
 {
-UGCTranslator::UGCTranslator() : m_dbRatings(":memory:"), m_dbReviews(":memory:") {}
-
-UGCTranslator::UGCTranslator(std::string const & path)
-  : m_dbRatings(path + ".ratings"), m_dbReviews(path + ".reviews")
+void FillRatings(json_t const * ratings, ugc::Ratings & result)
 {
-}
+  size_t size = json_array_size(ratings);
+  for (size_t i = 0; i < size; ++i)
+  {
+    json_t * el = json_array_get(ratings, i);
+    uint32_t id = 0;
+    double ratingValue = 0.;
 
-bool UGCTranslator::TranslateUGC(osm::Id const & id, ugc::UGC & ugc)
-{
-  bool ratingsOk = TranslateRatings(m_dbRatings, id, ugc.m_ratings);
-  bool reviewsOk = TranslateReview(m_dbReviews, id, ugc.m_reviews);
-  return ratingsOk && reviewsOk;
+    FromJSONObject(el, "id", id);
+    FromJSONObject(el, "value", ratingValue);
+
+    result.emplace_back(strings::to_string(id), static_cast<float>(ratingValue));
+  }
 }
 
-void UGCTranslator::CreateRatings(std::string const & data)
+void FillReviews(json_t const * reviews, ugc::Reviews & result)
 {
-  bool rc = m_dbRatings.Exec(data);
-  UNUSED_VALUE(rc);
+  size_t size = json_array_size(reviews);
+  for (size_t i = 0; i < size; ++i)
+  {
+    ugc::Review review;
+
+    json_t * el = json_array_get(reviews, i);
+
+    uint32_t daysSinceEpoch = 0;
+    double rating = 0.;
+    std::string lang;
+
+    FromJSONObject(el, "id", review.m_id);
+    FromJSONObject(el, "text", review.m_text.m_text);
+    FromJSONObject(el, "lang", lang);
+    FromJSONObject(el, "author", review.m_author);
+    FromJSONObject(el, "rating", rating);
+    FromJSONObject(el, "date", daysSinceEpoch);
+
+    review.m_text.m_lang = StringUtf8Multilang::GetLangIndex(lang);
+    review.m_rating = rating;
+    review.m_time = ugc::Clock::now() - std::chrono::hours(daysSinceEpoch * 24);
+
+    result.push_back(std::move(review));
+  }
 }
+}  // namespace
 
-void UGCTranslator::CreateReviews(std::string const & data)
+namespace generator
 {
-  bool rc = m_dbReviews.Exec(data);
-  UNUSED_VALUE(rc);
-}
+UGCTranslator::UGCTranslator() : m_db(":memory:") {}
 
-bool UGCTranslator::TranslateRatings(UGCDB & db, osm::Id const id, ugc::Ratings & ratings)
+UGCTranslator::UGCTranslator(std::string const & dbFilename) : m_db(dbFilename) {}
+
+bool UGCTranslator::TranslateUGC(osm::Id const & id, ugc::UGC & ugc)
 {
   std::vector<uint8_t> blob;
-  bool rc = db.Get(id, blob);
+  bool rc = m_db.Get(id, blob);
   if (!rc)
     return false;
   std::string result(blob.cbegin(), blob.cend());
 
-  my::Json jsonRoot(result);
-
-  size_t size = json_array_size(jsonRoot.get());
-  for (size_t i = 0; i < size; ++i)
+  try
   {
-    json_t * el = json_array_get(jsonRoot.get(), i);
-    double ratingValue = 0;
-    size_t translationKeyId = 0;
+    my::Json root(result);
+    double totalRating = 0.;
 
-    FromJSONObject(el, "value", ratingValue);
-    FromJSONObject(el, "criterion_id", translationKeyId);
+    FromJSONObject(root.get(), "total_rating", totalRating);
+    FromJSONObject(root.get(), "votes", ugc.m_votes);
 
-    std::ostringstream translationKey;
-    translationKey << "TranslationKey" << translationKeyId;
-    ratings.emplace_back(translationKey.str(), static_cast<float>(ratingValue));
+    ugc.m_totalRating = totalRating;
+
+    auto const ratings = json_object_get(root.get(), "ratings");
+    auto const reviews = json_object_get(root.get(), "reviews");
+
+    if (!CheckJsonArray(ratings) || !CheckJsonArray(reviews))
+      return false;
+
+    FillRatings(ratings, ugc.m_ratings);
+    FillReviews(reviews, ugc.m_reviews);
+  }
+  catch (my::Json::Exception const & e)
+  {
+    LOG(LERROR, (e.Msg()));
+    ugc = {};
+    return false;
   }
 
   return true;
 }
 
-bool UGCTranslator::TranslateReview(UGCDB & db, osm::Id const id, std::vector<ugc::Review> & review)
+void UGCTranslator::CreateDb(std::string const & data)
 {
-  return true;
+  bool rc = m_db.Exec(data);
+  UNUSED_VALUE(rc);
 }
-
-// bool UGCTranslator::TranslateAttribute(UGCDB & db, osm::Id const id, ugc::Attribute & attribute)
-//{
-//  return false;
-//}
 }  // namespace generator
diff --git a/generator/ugc_translator.hpp b/generator/ugc_translator.hpp
index bc22a44e19..14af49cfd4 100644
--- a/generator/ugc_translator.hpp
+++ b/generator/ugc_translator.hpp
@@ -11,19 +11,14 @@ class UGCTranslator
 {
 public:
   UGCTranslator();
-  UGCTranslator(std::string const & path);
+  UGCTranslator(std::string const & dbFilename);
 
   bool TranslateUGC(osm::Id const & id, ugc::UGC & ugc);
+
   // For testing only
-  void CreateRatings(std::string const & data);
-  void CreateReviews(std::string const & data);
-  
-private:
-  bool TranslateRatings(UGCDB & db, osm::Id const id, ugc::Ratings & ratings);
-  bool TranslateReview(UGCDB & db, osm::Id const id, std::vector<ugc::Review> & review);
-//  bool TranslateAttribute(UGCDB & db, osm::Id const id, ugc::Attribute & attribute);
+  void CreateDb(std::string const & data);
 
-  UGCDB m_dbRatings;
-  UGCDB m_dbReviews;
+private:
+  UGCDB m_db;
 };
 }  // namespace generator
diff --git a/indexer/feature_data.cpp b/indexer/feature_data.cpp
index 2be9cf0d62..f16775b001 100644
--- a/indexer/feature_data.cpp
+++ b/indexer/feature_data.cpp
@@ -31,7 +31,6 @@ string DebugPrint(TypesHolder const & holder)
     s.pop_back();
   return s;
 }
-}  // namespace feature
 
 TypesHolder::TypesHolder(FeatureBase const & f)
 : m_size(0), m_geoType(f.GetFeatureType())
@@ -57,10 +56,10 @@ bool TypesHolder::Equals(TypesHolder const & other) const
 
   return my == his;
 }
+}  // namespace feature
 
 namespace
 {
-
 class UselessTypesChecker
 {
   vector<uint32_t> m_types;
@@ -124,7 +123,6 @@ public:
     return false;
   }
 };
-
 }  // namespace
 
 namespace feature
@@ -163,7 +161,6 @@ uint8_t CalculateHeader(size_t const typesCount, uint8_t const headerGeomType,
 
   return header;
 }
-}  // namespace feature
 
 void TypesHolder::SortBySpec()
 {
@@ -182,6 +179,7 @@ vector<string> TypesHolder::ToObjectNames() const
     result.push_back(classif().GetReadableObjectName(type));
   return result;
 }
+}  // namespace feature
 
 ////////////////////////////////////////////////////////////////////////////////////
 // FeatureParamsBase implementation
diff --git a/indexer/ftraits.hpp b/indexer/ftraits.hpp
index 05ca4b596b..0b4cd4cb8e 100644
--- a/indexer/ftraits.hpp
+++ b/indexer/ftraits.hpp
@@ -85,21 +85,26 @@ class UGC : public TraitsBase<UGC, UGCTypeMask, UGCTYPE_NONE>
   }
 
 public:
+  static bool IsUGCAvailable(UGCTypeMask mask) { return mask != UGCTYPE_NONE; }
+  static bool IsRatingAvailable(UGCTypeMask mask) { return mask & UGCTYPE_RATING; }
+  static bool IsReviewsAvailable(UGCTypeMask mask) { return mask & UGCTYPE_REVIEWS; }
+  static bool IsDetailsAvailable(UGCTypeMask mask) { return mask & UGCTYPE_DETAILS; }
+
   static bool IsUGCAvailable(feature::TypesHolder const & types)
   {
-    return GetValue(types) != UGCTYPE_NONE;
+    return IsUGCAvailable(GetValue(types));
   }
   static bool IsRatingAvailable(feature::TypesHolder const & types)
   {
-    return GetValue(types) & UGCTYPE_RATING;
+    return IsRatingAvailable(GetValue(types));
   }
   static bool IsReviewsAvailable(feature::TypesHolder const & types)
   {
-    return GetValue(types) & UGCTYPE_REVIEWS;
+    return IsReviewsAvailable(GetValue(types));
   }
   static bool IsDetailsAvailable(feature::TypesHolder const & types)
   {
-    return GetValue(types) & UGCTYPE_DETAILS;
+    return IsDetailsAvailable(GetValue(types));
   }
 };
 
diff --git a/partners_api/locals_api.cpp b/partners_api/locals_api.cpp
index 46b1969b6f..fbde1d72c6 100644
--- a/partners_api/locals_api.cpp
+++ b/partners_api/locals_api.cpp
@@ -18,14 +18,7 @@ namespace
 {
 using namespace locals;
 
-bool CheckJsonArray(json_t const * data)
-{
-  if (data == nullptr)
-    return false;
-  return json_is_array(data) && json_array_size(data) > 0;
-}
-
-void ParseError(std::string const & src, int & errorCode, std::string & message)
+void ParseError(std::string const & src, ErrorCode & errorCode, std::string & message)
 {
   message.clear();
   errorCode = 0;
diff --git a/partners_api/viator_api.cpp b/partners_api/viator_api.cpp
index 3555d06236..09b7bed3c6 100644
--- a/partners_api/viator_api.cpp
+++ b/partners_api/viator_api.cpp
@@ -123,20 +123,6 @@ std::string MakeUrl(std::string const & apiMethod)
   return os.str();
 }
 
-bool CheckJsonArray(json_t const * data)
-{
-  if (data == nullptr)
-    return false;
-
-  if (!json_is_array(data))
-    return false;
-
-  if (json_array_size(data) <= 0)
-    return false;
-
-  return true;
-}
-
 bool CheckAnswer(my::Json const & root)
 {
   bool success;
diff --git a/tools/unix/generate_planet.sh b/tools/unix/generate_planet.sh
index debdb2e4c0..1873741b60 100755
--- a/tools/unix/generate_planet.sh
+++ b/tools/unix/generate_planet.sh
@@ -177,6 +177,7 @@ fi
 ROADS_SCRIPT="$PYTHON_SCRIPTS_PATH/road_runner.py"
 HIERARCHY_SCRIPT="$PYTHON_SCRIPTS_PATH/hierarchy_to_countries.py"
 LOCALADS_SCRIPT="$PYTHON_SCRIPTS_PATH/local_ads/mwm_to_csv_4localads.py"
+UGC_FILE="${UGC_FILE:-$INTDIR/ugc_db.sqlite3}"
 BOOKING_SCRIPT="$PYTHON_SCRIPTS_PATH/booking_hotels.py"
 BOOKING_FILE="${BOOKING_FILE:-$INTDIR/hotels.csv}"
 OPENTABLE_SCRIPT="$PYTHON_SCRIPTS_PATH/opentable_restaurants.py"
@@ -316,6 +317,14 @@ if [ ! -f "$VIATOR_FILE" -a -n "${VIATOR_KEY-}" ]; then
   ) &
 fi
 
+# Download UGC (user generated content) database.
+if [ -n "${UGC-}" ]; then
+  putmode "Step UGC: Dowloading UGC database"
+  (
+    curl "https://dummy.ru/" --output "$UGC_FILE" --silent || fail "Failed to download UGC database."
+  ) &
+fi
+
 if [ "$MODE" == "coast" ]; then
   putmode
 
@@ -441,6 +450,7 @@ if [ "$MODE" == "features" ]; then
   [ -f "$BOOKING_FILE" ] && PARAMS_SPLIT="$PARAMS_SPLIT --booking_data=$BOOKING_FILE"
   [ -f "$OPENTABLE_FILE" ] && PARAMS_SPLIT="$PARAMS_SPLIT --opentable_data=$OPENTABLE_FILE"
   [ -f "$VIATOR_FILE" ] && PARAMS_SPLIT="$PARAMS_SPLIT --viator_data=$VIATOR_FILE"
+  [ -f "$UGC_FILE" ] && PARAMS_SPLIT="$PARAMS_SPLIT --ugc_data=$UGC_FILE"
   "$GENERATOR_TOOL" --intermediate_data_path="$INTDIR/" --node_storage=$NODE_STORAGE --osm_file_type=o5m --osm_file_name="$PLANET" \
     --data_path="$TARGET" --user_resource_path="$DATA_PATH/" $PARAMS_SPLIT 2>> "$PLANET_LOG"
   MODE=mwm
diff --git a/ugc/api.cpp b/ugc/api.cpp
index b2e6af9aa5..10d191bda7 100644
--- a/ugc/api.cpp
+++ b/ugc/api.cpp
@@ -39,13 +39,13 @@ UGC Api::MakeTestUGC1(Time now)
 
   Reviews reviews;
   reviews.emplace_back(20 /* id */, Text("Damn good coffee", StringUtf8Multilang::kEnglishCode),
-                       Author(UID(987654321 /* hi */, 123456789 /* lo */), "Cole"),
+                       Author("Cole"),
                        5.0 /* rating */, FromDaysAgo(now, 10));
   reviews.emplace_back(
       67812 /* id */, Text("Clean place, reasonably priced", StringUtf8Multilang::kDefaultCode),
-      Author(UID(0 /* hi */, 315 /* lo */), "Cooper"), 5.0 /* rating */, FromDaysAgo(now, 1));
+      Author("Cooper"), 5.0 /* rating */, FromDaysAgo(now, 1));
 
-  return UGC(records, reviews, 4.5 /* rating */);
+  return UGC(records, reviews, 4.5 /* rating */, 4000000000 /* votes */);
 }
 
 // static
@@ -59,9 +59,9 @@ UGC Api::MakeTestUGC2(Time now)
   vector<Review> reviews;
   reviews.emplace_back(
       119 /* id */, Text("This pie's so good it is a crime", StringUtf8Multilang::kDefaultCode),
-      Author(UID(0 /* hi */, 315 /* lo */), "Cooper"), 5.0 /* rating */, FromDaysAgo(now, 1));
+      Author("Cooper"), 5.0 /* rating */, FromDaysAgo(now, 1));
 
-  return UGC(records, reviews, 5.0 /* rating */);
+  return UGC(records, reviews, 5.0 /* rating */, 0 /* votes */);
 }
 
 // static
diff --git a/ugc/types.hpp b/ugc/types.hpp
index 568b64ffeb..2e6f278756 100644
--- a/ugc/types.hpp
+++ b/ugc/types.hpp
@@ -100,33 +100,6 @@ struct RatingRecord
 
 using Ratings = std::vector<RatingRecord>;
 
-struct Rating
-{
-  Rating() = default;
-  Rating(std::vector<RatingRecord> const & ratings, float const aggValue)
-    : m_ratings(ratings), m_aggValue(aggValue)
-  {
-  }
-
-  DECLARE_VISITOR(visitor(m_ratings, "ratings"), visitor.VisitRating(m_aggValue, "aggValue"))
-
-  bool operator==(Rating const & rhs) const
-  {
-    return m_ratings == rhs.m_ratings && m_aggValue == rhs.m_aggValue;
-  }
-
-  friend std::string DebugPrint(Rating const & rating)
-  {
-    std::ostringstream os;
-    os << "Rating [ ratings:" << ::DebugPrint(rating.m_ratings)
-       << ", aggValue:" << rating.m_aggValue << " ]";
-    return os.str();
-  }
-
-  std::vector<RatingRecord> m_ratings;
-  float m_aggValue{};
-};
-
 struct UID
 {
   UID() = default;
@@ -148,24 +121,7 @@ struct UID
   uint64_t m_lo{};
 };
 
-struct Author
-{
-  Author() = default;
-  Author(UID const & uid, std::string const & name) : m_uid(uid), m_name(name) {}
-
-  DECLARE_VISITOR(visitor(m_uid, "uid"), visitor(m_name, "name"));
-
-  bool operator==(Author const & rhs) const { return m_uid == rhs.m_uid && m_name == rhs.m_name; }
-  friend std::string DebugPrint(Author const & author)
-  {
-    std::ostringstream os;
-    os << "Author [ " << DebugPrint(author.m_uid) << " " << author.m_name << " ]";
-    return os.str();
-  }
-
-  UID m_uid{};
-  std::string m_name;
-};
+using Author = std::string;
 
 struct Text
 {
@@ -190,7 +146,7 @@ struct Text
 
 struct Review
 {
-  using ReviewId = uint32_t;
+  using ReviewId = uint64_t;
 
   Review() = default;
   Review(ReviewId id, Text const & text, Author const & author, float const rating,
@@ -214,7 +170,7 @@ struct Review
     os << "Review [ ";
     os << "id:" << review.m_id << ", ";
     os << "text:" << DebugPrint(review.m_text) << ", ";
-    os << "author:" << DebugPrint(review.m_author) << ", ";
+    os << "author:" << review.m_author << ", ";
     os << "rating:" << review.m_rating << ", ";
     os << "days since epoch:" << ToDaysSinceEpoch(review.m_time) << " ]";
     return os.str();
@@ -258,13 +214,13 @@ struct Attribute
 struct UGC
 {
   UGC() = default;
-  UGC(Ratings const & records, Reviews const & reviews, float const rating)
-    : m_ratings(records), m_reviews(reviews), m_aggRating(rating)
+  UGC(Ratings const & records, Reviews const & reviews, float const totalRating, uint32_t votes)
+    : m_ratings(records), m_reviews(reviews), m_totalRating(totalRating), m_votes(votes)
   {
   }
 
   DECLARE_VISITOR(visitor(m_ratings, "ratings"), visitor(m_reviews, "reviews"),
-                  visitor.VisitRating(m_aggRating, "aggRating"))
+                  visitor.VisitRating(m_totalRating, "totalRating"))
 
   bool operator==(UGC const & rhs) const
   {
@@ -282,7 +238,8 @@ struct UGC
 
   Ratings m_ratings;
   Reviews m_reviews;
-  float m_aggRating{};
+  float m_totalRating{};
+  uint32_t m_votes{};
 };
 
 struct UGCUpdate
diff --git a/ugc/ugc_tests/serdes_tests.cpp b/ugc/ugc_tests/serdes_tests.cpp
index 0bda71fbcc..fc4af6e95e 100644
--- a/ugc/ugc_tests/serdes_tests.cpp
+++ b/ugc/ugc_tests/serdes_tests.cpp
@@ -22,14 +22,9 @@ using FromBin = DeserializerV0<ReaderSource<MemReader>>;
 using ToJson = SerializerJson<MemWriter<Buffer>>;
 using FromJson = DeserializerJsonV0<ReaderSource<MemReader>>;
 
-
-Rating GetTestRating()
+Ratings GetTestRating()
 {
-  vector<RatingRecord> records;
-  records.emplace_back("music" /* key */, 5.0 /* value */);
-  records.emplace_back("service" /* key */, 4.0 /* value */);
-
-  return Rating(records, 4.5 /* aggValue */);
+  return {{"music" /* key */, 5.0 /* value */}, {"service" /* key */, 4.0 /* value */}};
 }
 
 MemWriter<Buffer> MakeSink(Buffer & buffer) { return MemWriter<Buffer>(buffer); }
@@ -65,7 +60,7 @@ UNIT_TEST(SerDes_Rating)
   auto const expectedRating = GetTestRating();
   TEST_EQUAL(expectedRating, expectedRating, ());
 
-  MakeTest<Rating, ToBin, FromBin>(expectedRating);
+  MakeTest<Ratings, ToBin, FromBin>(expectedRating);
 }
 
 UNIT_TEST(SerDes_Json_Rating)
@@ -73,7 +68,7 @@ UNIT_TEST(SerDes_Json_Rating)
   auto const expectedRating = GetTestRating();
   TEST_EQUAL(expectedRating, expectedRating, ());
 
-  MakeTest<Rating, ToJson, FromJson>(expectedRating);
+  MakeTest<Ratings, ToJson, FromJson>(expectedRating);
 }
 
 UNIT_TEST(SerDes_Json_Reviews)
@@ -114,7 +109,7 @@ UNIT_TEST(SerDes_UGC)
     Serialize(sink, expectedUGC);
   }
 
-  UGC actualUGC({} /* rating */, {} /* reviews */, {} /* attributes */);
+  UGC actualUGC({} /* rating */, {} /* reviews */, {} /* totalRating */, {} /* votes */);
   {
     auto source = MakeSource(buffer);
     Deserialize(source, actualUGC);