Remove archivedcharts endpoint, optimize indices (#14296)

Remove undocumented archivedcharts endpoint. Use context endpoint instead
Remove unused functions to lookup chart and dimension UUIDs
Drop/Add new index for dimension and chart tables

4 files changed, 5 insertions, 337 deletions

diff --git a/database/sqlite/sqlite_functions.c b/database/sqlite/sqlite_functions.c
index 749dd99dbf..fc32853182 100644
--- a/database/sqlite/sqlite_functions.c
+++ b/database/sqlite/sqlite_functions.c
@@ -22,9 +22,8 @@ const char *database_config[] = {
     "multiplier int, divisor int , algorithm int, options text);",
 
     "CREATE TABLE IF NOT EXISTS metadata_migration(filename text, file_size, date_created int);",
-    "CREATE INDEX IF NOT EXISTS ind_d1 on dimension (chart_id, id, name);",
-    "CREATE INDEX IF NOT EXISTS ind_c1 on chart (host_id, id, type, name);",
-    "CREATE INDEX IF NOT EXISTS ind_c2 on chart (host_id, context);",
+    "CREATE INDEX IF NOT EXISTS ind_d2 on dimension (chart_id);",
+    "CREATE INDEX IF NOT EXISTS ind_c3 on chart (host_id);",
     "CREATE TABLE IF NOT EXISTS chart_label(chart_id blob, source_type int, label_key text, "
     "label_value text, date_created int, PRIMARY KEY (chart_id, label_key));",
     "CREATE TABLE IF NOT EXISTS node_instance (host_id blob PRIMARY KEY, claim_id, node_id, date_created);",
@@ -55,6 +54,9 @@ const char *database_cleanup[] = {
     "DELETE FROM host_info WHERE host_id NOT IN (SELECT host_id FROM host);",
     "DELETE FROM host_label WHERE host_id NOT IN (SELECT host_id FROM host);",
     "DROP TRIGGER IF EXISTS tr_dim_del;",
+    "DROP INDEX IF EXISTS ind_d1;",
+    "DROP INDEX IF EXISTS ind_c1;",
+    "DROP INDEX IF EXISTS ind_c2;",
     NULL
 };
 
@@ -504,211 +506,6 @@ skip:
     return result;
 }
 
-
-
-//
-// Support for archived charts (TO BE REMOVED)
-//
-#define SELECT_DIMENSION "select d.id, d.name from dimension d where d.chart_id = @chart_uuid;"
-
-static void sql_rrdim2json(sqlite3_stmt *res_dim, uuid_t *chart_uuid, BUFFER *wb, size_t *dimensions_count)
-{
-    int rc;
-
-    rc = sqlite3_bind_blob(res_dim, 1, chart_uuid, sizeof(*chart_uuid), SQLITE_STATIC);
-    if (rc != SQLITE_OK)
-        return;
-
-    int dimensions = 0;
-    buffer_sprintf(wb, "\t\t\t\"dimensions\": {\n");
-
-    while (sqlite3_step_monitored(res_dim) == SQLITE_ROW) {
-        if (dimensions)
-            buffer_strcat(wb, ",\n\t\t\t\t\"");
-        else
-            buffer_strcat(wb, "\t\t\t\t\"");
-        buffer_strcat_jsonescape(wb, (const char *) sqlite3_column_text(res_dim, 0));
-        buffer_strcat(wb, "\": { \"name\": \"");
-        buffer_strcat_jsonescape(wb, (const char *) sqlite3_column_text(res_dim, 1));
-        buffer_strcat(wb, "\" }");
-        dimensions++;
-    }
-    *dimensions_count += dimensions;
-    buffer_sprintf(wb, "\n\t\t\t}");
-}
-
-#define SELECT_CHART "select chart_id, id, name, type, family, context, title, priority, plugin, " \
-    "module, unit, chart_type, update_every from chart " \
-    "where host_id = @host_uuid and chart_id not in (select chart_id from chart_active) order by chart_id asc;"
-
-void sql_rrdset2json(RRDHOST *host, BUFFER *wb)
-{
-    //    time_t first_entry_t = 0; //= rrdset_first_entry_t(st);
-    //   time_t last_entry_t = 0; //rrdset_last_entry_t(st);
-    static char *custom_dashboard_info_js_filename = NULL;
-    int rc;
-
-    sqlite3_stmt *res_chart = NULL;
-    sqlite3_stmt *res_dim = NULL;
-    time_t now = now_realtime_sec();
-
-    rc = sqlite3_prepare_v2(db_meta, SELECT_CHART, -1, &res_chart, 0);
-    if (unlikely(rc != SQLITE_OK)) {
-        error_report("Failed to prepare statement to fetch host archived charts");
-        return;
-    }
-
-    rc = sqlite3_bind_blob(res_chart, 1, &host->host_uuid, sizeof(host->host_uuid), SQLITE_STATIC);
-    if (unlikely(rc != SQLITE_OK)) {
-        error_report("Failed to bind host parameter to fetch archived charts");
-        goto failed;
-    }
-
-    rc = sqlite3_prepare_v2(db_meta, SELECT_DIMENSION, -1, &res_dim, 0);
-    if (unlikely(rc != SQLITE_OK)) {
-        error_report("Failed to prepare statement to fetch chart archived dimensions");
-        goto failed;
-    };
-
-    if(unlikely(!custom_dashboard_info_js_filename))
-        custom_dashboard_info_js_filename = config_get(CONFIG_SECTION_WEB, "custom dashboard_info.js", "");
-
-    buffer_sprintf(wb, "{\n"
-                       "\t\"hostname\": \"%s\""
-                       ",\n\t\"version\": \"%s\""
-                       ",\n\t\"release_channel\": \"%s\""
-                       ",\n\t\"os\": \"%s\""
-                       ",\n\t\"timezone\": \"%s\""
-                       ",\n\t\"update_every\": %d"
-                       ",\n\t\"history\": %ld"
-                       ",\n\t\"memory_mode\": \"%s\""
-                       ",\n\t\"custom_info\": \"%s\""
-                       ",\n\t\"charts\": {"
-        , rrdhost_hostname(host)
-        , rrdhost_program_version(host)
-        , get_release_channel()
-        , rrdhost_os(host)
-        , rrdhost_timezone(host)
-        , host->rrd_update_every
-        , host->rrd_history_entries
-        , rrd_memory_mode_name(host->rrd_memory_mode)
-        , custom_dashboard_info_js_filename
-    );
-
-    size_t c = 0;
-    size_t dimensions = 0;
-
-    while (sqlite3_step_monitored(res_chart) == SQLITE_ROW) {
-        char id[512];
-        sprintf(id, "%s.%s", sqlite3_column_text(res_chart, 3), sqlite3_column_text(res_chart, 1));
-        RRDSET *st = rrdset_find(host, id);
-        if (st && !rrdset_flag_check(st, RRDSET_FLAG_ARCHIVED))
-            continue;
-
-        if (c)
-            buffer_strcat(wb, ",\n\t\t\"");
-        else
-            buffer_strcat(wb, "\n\t\t\"");
-        c++;
-
-        buffer_strcat(wb, id);
-        buffer_strcat(wb, "\": ");
-
-        buffer_sprintf(
-            wb,
-            "\t\t{\n"
-            "\t\t\t\"id\": \"%s\",\n"
-            "\t\t\t\"name\": \"%s\",\n"
-            "\t\t\t\"type\": \"%s\",\n"
-            "\t\t\t\"family\": \"%s\",\n"
-            "\t\t\t\"context\": \"%s\",\n"
-            "\t\t\t\"title\": \"%s (%s)\",\n"
-            "\t\t\t\"priority\": %ld,\n"
-            "\t\t\t\"plugin\": \"%s\",\n"
-            "\t\t\t\"module\": \"%s\",\n"
-            "\t\t\t\"enabled\": %s,\n"
-            "\t\t\t\"units\": \"%s\",\n"
-            "\t\t\t\"data_url\": \"/api/v1/data?chart=%s\",\n"
-            "\t\t\t\"chart_type\": \"%s\",\n",
-            id //sqlite3_column_text(res_chart, 1)
-            ,
-            id // sqlite3_column_text(res_chart, 2)
-            ,
-            sqlite3_column_text(res_chart, 3), sqlite3_column_text(res_chart, 4), sqlite3_column_text(res_chart, 5),
-            sqlite3_column_text(res_chart, 6), id //sqlite3_column_text(res_chart, 2)
-            ,
-            (long ) sqlite3_column_int(res_chart, 7),
-            (const char *) sqlite3_column_text(res_chart, 8) ? (const char *) sqlite3_column_text(res_chart, 8) : (char *) "",
-            (const char *) sqlite3_column_text(res_chart, 9) ? (const char *) sqlite3_column_text(res_chart, 9) : (char *) "", (char *) "false",
-            (const char *) sqlite3_column_text(res_chart, 10), id //sqlite3_column_text(res_chart, 2)
-            ,
-            rrdset_type_name(sqlite3_column_int(res_chart, 11)));
-
-        sql_rrdim2json(res_dim, (uuid_t *) sqlite3_column_blob(res_chart, 0), wb, &dimensions);
-
-        rc = sqlite3_reset(res_dim);
-        if (unlikely(rc != SQLITE_OK))
-            error_report("Failed to reset the prepared statement when reading archived chart dimensions");
-        buffer_strcat(wb, "\n\t\t}");
-    }
-
-    buffer_sprintf(wb
-        , "\n\t}"
-          ",\n\t\"charts_count\": %zu"
-          ",\n\t\"dimensions_count\": %zu"
-          ",\n\t\"alarms_count\": %zu"
-          ",\n\t\"rrd_memory_bytes\": %zu"
-          ",\n\t\"hosts_count\": %zu"
-          ",\n\t\"hosts\": ["
-        , c
-        , dimensions
-        , (size_t) 0
-        , (size_t) 0
-        , rrdhost_hosts_available()
-    );
-
-    if(unlikely(rrdhost_hosts_available() > 1)) {
-        rrd_rdlock();
-
-        size_t found = 0;
-        RRDHOST *h;
-        rrdhost_foreach_read(h) {
-            if(!rrdhost_should_be_removed(h, host, now) && !rrdhost_flag_check(h, RRDHOST_FLAG_ARCHIVED)) {
-                buffer_sprintf(wb
-                    , "%s\n\t\t{"
-                      "\n\t\t\t\"hostname\": \"%s\""
-                      "\n\t\t}"
-                    , (found > 0) ? "," : ""
-                    , rrdhost_hostname(h)
-                );
-
-                found++;
-            }
-        }
-
-        rrd_unlock();
-    }
-    else {
-        buffer_sprintf(wb
-            , "\n\t\t{"
-              "\n\t\t\t\"hostname\": \"%s\""
-              "\n\t\t}"
-            , rrdhost_hostname(host)
-        );
-    }
-
-    buffer_sprintf(wb, "\n\t]\n}\n");
-
-    rc = sqlite3_finalize(res_dim);
-    if (unlikely(rc != SQLITE_OK))
-        error_report("Failed to finalize the prepared statement when reading archived chart dimensions");
-
-failed:
-    rc = sqlite3_finalize(res_chart);
-    if (unlikely(rc != SQLITE_OK))
-        error_report("Failed to finalize the prepared statement when reading archived charts");
-}
-
 void db_execute(const char *cmd)
 {
     int rc;
@@ -1268,112 +1065,3 @@ int sql_metadata_cache_stats(int op)
     netdata_thread_enable_cancelability();
     return count;
 }
-
-#define SQL_FIND_CHART_UUID                                                                                            \
-    "SELECT chart_id FROM chart WHERE host_id = @host AND type=@type AND id=@id AND (name IS NULL OR name=@name) AND chart_id IS NOT NULL;"
-
-#define SQL_FIND_DIMENSION_UUID \
-    "SELECT dim_id FROM dimension WHERE chart_id=@chart AND id=@id AND name=@name AND LENGTH(dim_id)=16;"
-
-
-//Do a database lookup to find the UUID of a chart
-//If found store it in store_uuid and return 0
-int sql_find_chart_uuid(RRDHOST *host, RRDSET *st, uuid_t *store_uuid)
-{
-    static __thread sqlite3_stmt *res = NULL;
-    int rc;
-
-    const char *name = string2str(st->parts.name);
-
-    if (unlikely(!db_meta) && default_rrd_memory_mode != RRD_MEMORY_MODE_DBENGINE)
-        return 1;
-
-    if (unlikely(!res)) {
-        rc = prepare_statement(db_meta, SQL_FIND_CHART_UUID, &res);
-        if (rc != SQLITE_OK) {
-            error_report("Failed to prepare statement to lookup chart UUID in the database");
-            return 1;
-        }
-    }
-
-    rc = sqlite3_bind_blob(res, 1, &host->host_uuid, sizeof(host->host_uuid), SQLITE_STATIC);
-    if (unlikely(rc != SQLITE_OK))
-        goto bind_fail;
-
-    rc = sqlite3_bind_text(res, 2,  string2str(st->parts.type), -1, SQLITE_STATIC);
-    if (unlikely(rc != SQLITE_OK))
-        goto bind_fail;
-
-    rc = sqlite3_bind_text(res, 3, string2str(st->parts.id), -1, SQLITE_STATIC);
-    if (unlikely(rc != SQLITE_OK))
-        goto bind_fail;
-
-    rc = sqlite3_bind_text(res, 4, name && *name ? name : string2str(st->parts.id), -1, SQLITE_STATIC);
-    if (unlikely(rc != SQLITE_OK))
-        goto bind_fail;
-
-    int status = 1;
-    rc = sqlite3_step_monitored(res);
-    if (likely(rc == SQLITE_ROW)) {
-        uuid_copy(*store_uuid, sqlite3_column_blob(res, 0));
-        status = 0;
-    }
-
-    rc = sqlite3_reset(res);
-    if (unlikely(rc != SQLITE_OK))
-        error_report("Failed to reset statement when searching for a chart UUID, rc = %d", rc);
-
-    return status;
-
-bind_fail:
-    error_report("Failed to bind input parameter to perform chart UUID database lookup, rc = %d", rc);
-    rc = sqlite3_reset(res);
-    if (unlikely(rc != SQLITE_OK))
-        error_report("Failed to reset statement when searching for a chart UUID, rc = %d", rc);
-    return 1;
-}
-
-int sql_find_dimension_uuid(RRDSET *st, RRDDIM *rd, uuid_t *store_uuid)
-{
-    static __thread sqlite3_stmt *res = NULL;
-    int rc;
-    int status = 1;
-
-    if (unlikely(!db_meta) && default_rrd_memory_mode != RRD_MEMORY_MODE_DBENGINE)
-        return 1;
-
-    if (unlikely(!res)) {
-        rc = prepare_statement(db_meta, SQL_FIND_DIMENSION_UUID, &res);
-        if (rc != SQLITE_OK) {
-            error_report("Failed to bind prepare statement to lookup dimension UUID in the database");
-            return 1;
-        }
-    }
-
-    rc = sqlite3_bind_blob(res, 1, st->chart_uuid, sizeof(*st->chart_uuid), SQLITE_STATIC);
-    if (unlikely(rc != SQLITE_OK))
-        goto bind_fail;
-
-    rc = sqlite3_bind_text(res, 2, rrddim_id(rd), -1, SQLITE_STATIC);
-    if (unlikely(rc != SQLITE_OK))
-        goto bind_fail;
-
-    rc = sqlite3_bind_text(res, 3, rrddim_name(rd), -1, SQLITE_STATIC);
-    if (unlikely(rc != SQLITE_OK))
-        goto bind_fail;
-
-    rc = sqlite3_step_monitored(res);
-    if (likely(rc == SQLITE_ROW)) {
-        uuid_copy(*store_uuid, *((uuid_t *) sqlite3_column_blob(res, 0)));
-        status = 0;
-    }
-
-    rc = sqlite3_reset(res);
-    if (unlikely(rc != SQLITE_OK))
-        error_report("Failed to reset statement find dimension uuid, rc = %d", rc);
-    return status;
-
-bind_fail:
-    error_report("Failed to bind input parameter to perform dimension UUID database lookup, rc = %d", rc);
-    return 1;
-}
diff --git a/database/sqlite/sqlite_functions.h b/database/sqlite/sqlite_functions.h
index 5731d5c9e1..26bdef1e2c 100644
--- a/database/sqlite/sqlite_functions.h
+++ b/database/sqlite/sqlite_functions.h
@@ -65,16 +65,11 @@ int get_host_id(uuid_t *node_id, uuid_t *host_id);
 struct node_instance_list *get_node_list(void);
 void sql_load_node_id(RRDHOST *host);
 char *get_hostname_by_node_id(char *node_id);
-int sql_find_chart_uuid(RRDHOST *host, RRDSET *st, uuid_t *store_uuid);
-int sql_find_dimension_uuid(RRDSET *st, RRDDIM *rd, uuid_t *store_uuid);
 
 // Help build archived hosts in memory when agent starts
 void sql_build_host_system_info(uuid_t *host_id, struct rrdhost_system_info *system_info);
 DICTIONARY *sql_load_host_labels(uuid_t *host_id);
 
-// For queries: To be removed when context queries are implemented
-void sql_rrdset2json(RRDHOST *host, BUFFER *wb);
-
 // TODO: move to metadata
 int update_node_id(uuid_t *host_id, uuid_t *node_id);
 
diff --git a/web/api/web_api_v1.c b/web/api/web_api_v1.c
index 35c3c43786..a4d4836221 100644
--- a/web/api/web_api_v1.c
+++ b/web/api/web_api_v1.c
@@ -561,18 +561,6 @@ inline int web_client_api_request_v1_charts(RRDHOST *host, struct web_client *w,
     return HTTP_RESP_OK;
 }
 
-inline int web_client_api_request_v1_archivedcharts(RRDHOST *host __maybe_unused, struct web_client *w, char *url) {
-    (void)url;
-
-    buffer_flush(w->response.data);
-    w->response.data->contenttype = CT_APPLICATION_JSON;
-#ifdef ENABLE_DBENGINE
-    if (host->rrd_memory_mode == RRD_MEMORY_MODE_DBENGINE)
-        sql_rrdset2json(host, w->response.data);
-#endif
-    return HTTP_RESP_OK;
-}
-
 inline int web_client_api_request_v1_chart(RRDHOST *host, struct web_client *w, char *url) {
     return web_client_api_request_single_chart(host, w, url, rrd_stats_api_v1_chart);
 }
@@ -1620,7 +1608,6 @@ static struct api_command {
         { "charts",          0, WEB_CLIENT_ACL_DASHBOARD | WEB_CLIENT_ACL_ACLK, web_client_api_request_v1_charts                     },
         { "context",         0, WEB_CLIENT_ACL_DASHBOARD | WEB_CLIENT_ACL_ACLK, web_client_api_request_v1_context                    },
         { "contexts",        0, WEB_CLIENT_ACL_DASHBOARD | WEB_CLIENT_ACL_ACLK, web_client_api_request_v1_contexts                   },
-        { "archivedcharts",  0, WEB_CLIENT_ACL_DASHBOARD | WEB_CLIENT_ACL_ACLK, web_client_api_request_v1_archivedcharts             },
 
         // registry checks the ACL by itself, so we allow everything
         { "registry",        0, WEB_CLIENT_ACL_NOCHECK,                         web_client_api_request_v1_registry                   },
diff --git a/web/api/web_api_v1.h b/web/api/web_api_v1.h
index e6682c99ce..9dd6a1c231 100644
--- a/web/api/web_api_v1.h
+++ b/web/api/web_api_v1.h
@@ -9,7 +9,6 @@
 #include "web/api/health/health_cmdapi.h"
 #include "web/api/queries/weights.h"
 
-#define MAX_CHART_LABELS_FILTER (32)
 RRDR_OPTIONS web_client_api_request_v1_data_options(char *o);
 void web_client_api_request_v1_data_options_to_buffer(BUFFER *wb, RRDR_OPTIONS options);
 void web_client_api_request_v1_data_options_to_string(char *buf, size_t size, RRDR_OPTIONS options);
@@ -24,7 +23,6 @@ int web_client_api_request_single_chart(RRDHOST *host, struct web_client *w, cha
 int web_client_api_request_v1_alarm_variables(RRDHOST *host, struct web_client *w, char *url);
 int web_client_api_request_v1_alarm_count(RRDHOST *host, struct web_client *w, char *url);
 int web_client_api_request_v1_charts(RRDHOST *host, struct web_client *w, char *url);
-int web_client_api_request_v1_archivedcharts(RRDHOST *host, struct web_client *w, char *url);
 int web_client_api_request_v1_chart(RRDHOST *host, struct web_client *w, char *url);
 int web_client_api_request_v1_data(RRDHOST *host, struct web_client *w, char *url);
 int web_client_api_request_v1_registry(RRDHOST *host, struct web_client *w, char *url);