[Asterisk-code-review] astdb: Improve prefix searches in astdb (asterisk[15])
Sean Bright
asteriskteam at digium.com
Thu Dec 7 15:06:23 CST 2017
Sean Bright has uploaded this change for review. ( https://gerrit.asterisk.org/7476
Change subject: astdb: Improve prefix searches in astdb
......................................................................
astdb: Improve prefix searches in astdb
Using the LIKE operator requires a full table scan of 'astdb', whereas a
comparison operation is able to use the primary key index.
This patch adds a new function to the AstDB API for quick prefix matches
and updates res_sorcery_astdb to utilize it. This showed substantial
performance improvement in my test environment.
Related to ASTERISK~26806, but does not completely resolve it.
Change-Id: I7d37f9ba2aea139dabf2ca72d31fbe34bd9b2fa1
---
M include/asterisk/astdb.h
M main/db.c
M res/res_sorcery_astdb.c
3 files changed, 69 insertions(+), 4 deletions(-)
git pull ssh://gerrit.asterisk.org:29418/asterisk refs/changes/76/7476/1
diff --git a/include/asterisk/astdb.h b/include/asterisk/astdb.h
index 8a870ae..383864b 100644
--- a/include/asterisk/astdb.h
+++ b/include/asterisk/astdb.h
@@ -83,6 +83,16 @@
*/
struct ast_db_entry *ast_db_gettree(const char *family, const char *keytree);
+/*!
+ * \brief Get a list of values with the given key prefix
+ *
+ * \param family The family to search under
+ * \param key_prefix The key prefix to search under
+ *
+ * \retval NULL An error occurred
+ */
+struct ast_db_entry *ast_db_gettree_by_prefix(const char *family, const char *key_prefix);
+
/*! \brief Free structure created by ast_db_gettree() */
void ast_db_freetree(struct ast_db_entry *entry);
diff --git a/main/db.c b/main/db.c
index fa125d7..dd00b18 100644
--- a/main/db.c
+++ b/main/db.c
@@ -124,6 +124,7 @@
DEFINE_SQL_STATEMENT(deltree_all_stmt, "DELETE FROM astdb")
DEFINE_SQL_STATEMENT(gettree_stmt, "SELECT key, value FROM astdb WHERE key || '/' LIKE ? || '/' || '%' ORDER BY key")
DEFINE_SQL_STATEMENT(gettree_all_stmt, "SELECT key, value FROM astdb ORDER BY key")
+DEFINE_SQL_STATEMENT(gettree_prefix_stmt, "SELECT key, value FROM astdb WHERE key > ?1 AND key <= ?1 || X'ffff'")
DEFINE_SQL_STATEMENT(showkey_stmt, "SELECT key, value FROM astdb WHERE key LIKE '%' || '/' || ? ORDER BY key")
DEFINE_SQL_STATEMENT(create_astdb_stmt, "CREATE TABLE IF NOT EXISTS astdb(key VARCHAR(256), value VARCHAR(256), PRIMARY KEY(key))")
@@ -167,6 +168,7 @@
clean_stmt(&deltree_all_stmt, deltree_all_stmt_sql);
clean_stmt(&gettree_stmt, gettree_stmt_sql);
clean_stmt(&gettree_all_stmt, gettree_all_stmt_sql);
+ clean_stmt(&gettree_prefix_stmt, gettree_prefix_stmt_sql);
clean_stmt(&showkey_stmt, showkey_stmt_sql);
clean_stmt(&put_stmt, put_stmt_sql);
clean_stmt(&create_astdb_stmt, create_astdb_stmt_sql);
@@ -182,6 +184,7 @@
|| init_stmt(&deltree_all_stmt, deltree_all_stmt_sql, sizeof(deltree_all_stmt_sql))
|| init_stmt(&gettree_stmt, gettree_stmt_sql, sizeof(gettree_stmt_sql))
|| init_stmt(&gettree_all_stmt, gettree_all_stmt_sql, sizeof(gettree_all_stmt_sql))
+ || init_stmt(&gettree_prefix_stmt, gettree_prefix_stmt_sql, sizeof(gettree_prefix_stmt_sql))
|| init_stmt(&showkey_stmt, showkey_stmt_sql, sizeof(showkey_stmt_sql))
|| init_stmt(&put_stmt, put_stmt_sql, sizeof(put_stmt_sql));
}
@@ -494,7 +497,7 @@
ast_mutex_lock(&dblock);
if (!ast_strlen_zero(prefix) && (sqlite3_bind_text(stmt, 1, prefix, -1, SQLITE_STATIC) != SQLITE_OK)) {
- ast_log(LOG_WARNING, "Could bind %s to stmt: %s\n", prefix, sqlite3_errmsg(astdb));
+ ast_log(LOG_WARNING, "Could not bind %s to stmt: %s\n", prefix, sqlite3_errmsg(astdb));
sqlite3_reset(stmt);
ast_mutex_unlock(&dblock);
return NULL;
@@ -528,6 +531,58 @@
return ret;
}
+struct ast_db_entry *ast_db_gettree_by_prefix(const char *family, const char *key_prefix)
+{
+ char prefix[MAX_DB_FIELD];
+ int res;
+ struct ast_db_entry *cur, *last = NULL, *ret = NULL;
+
+ res = snprintf(prefix, sizeof(prefix), "/%s/%s", family, key_prefix);
+ if (res >= sizeof(prefix)) {
+ ast_log(LOG_WARNING, "Requested key prefix is too long: %s\n", key_prefix);
+ return NULL;
+ }
+
+ ast_mutex_lock(&dblock);
+ if (sqlite3_bind_text(gettree_prefix_stmt, 1, prefix, res, SQLITE_STATIC) != SQLITE_OK) {
+ ast_log(LOG_WARNING, "Could not bind %s to stmt: %s\n", prefix, sqlite3_errmsg(astdb));
+ sqlite3_reset(gettree_prefix_stmt);
+ ast_mutex_unlock(&dblock);
+ return NULL;
+ }
+
+ while (sqlite3_step(gettree_prefix_stmt) == SQLITE_ROW) {
+ const char *key, *value;
+ size_t key_len, value_len;
+
+ key = (const char *) sqlite3_column_text(gettree_prefix_stmt, 0);
+ value = (const char *) sqlite3_column_text(gettree_prefix_stmt, 1);
+
+ if (!key || !value) {
+ break;
+ }
+
+ key_len = strlen(key);
+ value_len = strlen(value);
+
+ cur = ast_malloc(sizeof(*cur) + key_len + value_len + 2);
+ cur->next = NULL;
+ cur->key = cur->data + value_len + 1;
+ memcpy(cur->data, value, value_len + 1);
+ memcpy(cur->key, key, key_len + 1);
+ if (last) {
+ last->next = cur;
+ } else {
+ ret = cur;
+ }
+ last = cur;
+ }
+ sqlite3_reset(gettree_prefix_stmt);
+ ast_mutex_unlock(&dblock);
+
+ return ret;
+}
+
void ast_db_freetree(struct ast_db_entry *dbe)
{
struct ast_db_entry *last;
diff --git a/res/res_sorcery_astdb.c b/res/res_sorcery_astdb.c
index 8b93b57..87823be 100644
--- a/res/res_sorcery_astdb.c
+++ b/res/res_sorcery_astdb.c
@@ -334,14 +334,14 @@
const char *family_prefix = data;
size_t family_len = strlen(family_prefix) + strlen(type) + 1; /* +1 for slash delimiter */
char family[family_len + 1];
- char tree[prefix_len + sizeof("%")];
+ char tree[prefix_len + 1];
RAII_VAR(struct ast_db_entry *, entries, NULL, ast_db_freetree);
struct ast_db_entry *entry;
- snprintf(tree, sizeof(tree), "%.*s%%", (int) prefix_len, prefix);
+ snprintf(tree, sizeof(tree), "%.*s", (int) prefix_len, prefix);
snprintf(family, sizeof(family), "%s/%s", family_prefix, type);
- if (!(entries = ast_db_gettree(family, tree))) {
+ if (!(entries = ast_db_gettree_by_prefix(family, tree))) {
return;
}
--
To view, visit https://gerrit.asterisk.org/7476
To unsubscribe, visit https://gerrit.asterisk.org/settings
Gerrit-Project: asterisk
Gerrit-Branch: 15
Gerrit-MessageType: newchange
Gerrit-Change-Id: I7d37f9ba2aea139dabf2ca72d31fbe34bd9b2fa1
Gerrit-Change-Number: 7476
Gerrit-PatchSet: 1
Gerrit-Owner: Sean Bright <sean.bright at gmail.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.digium.com/pipermail/asterisk-code-review/attachments/20171207/af34adeb/attachment.html>
More information about the asterisk-code-review
mailing list