switch_core_db.c

Go to the documentation of this file.

/*
 * FreeSWITCH Modular Media Switching Software Library / Soft-Switch Application
 * Copyright (C) 2005-2014, Anthony Minessale II <anthm@freeswitch.org>
 *
 * Version: MPL 1.1
 *
 * The contents of this file are subject to the Mozilla Public License Version
 * 1.1 (the "License"); you may not use this file except in compliance with
 * the License. You may obtain a copy of the License at
 * http://www.mozilla.org/MPL/
 *
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
 * for the specific language governing rights and limitations under the
 * License.
 *
 * The Original Code is FreeSWITCH Modular Media Switching Software Library / Soft-Switch Application
 *
 * The Initial Developer of the Original Code is
 * Michael Jerris <mike@jerris.com>
 * Portions created by the Initial Developer are Copyright (C)
 * the Initial Developer. All Rights Reserved.
 *
 * Contributor(s):
 *
 * Michael Jerris <mike@jerris.com>
 *
 *
 * switch_core_db.c -- sqlite wrapper and extensions
 *
 */

#include <switch.h>
#include "private/switch_core_pvt.h"

#include <sqlite3.h>

static void db_pick_path(const char *dbname, char *buf, switch_size_t size)
{
        memset(buf, 0, size);
        if (switch_is_file_path(dbname)) {
                strncpy(buf, dbname, size-1);
        } else {
                switch_snprintf(buf, size, "%s%s%s.db", SWITCH_GLOBAL_dirs.db_dir, SWITCH_PATH_SEPARATOR, dbname);
        }
}

SWITCH_DECLARE(int) switch_core_db_open(const char *filename, switch_core_db_t **ppDb)
{
        return sqlite3_open(filename, ppDb);
}

SWITCH_DECLARE(int) switch_core_db_open_v2(const char *filename, switch_core_db_t **ppDb)
{
        return sqlite3_open_v2(filename, ppDb, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_URI, NULL);
}

SWITCH_DECLARE(int) switch_core_db_close(switch_core_db_t *db)
{
        return sqlite3_close(db);
}

SWITCH_DECLARE(const unsigned char *) switch_core_db_column_text(switch_core_db_stmt_t *stmt, int iCol)
{
        return sqlite3_column_text(stmt, iCol);
}

SWITCH_DECLARE(const char *) switch_core_db_column_name(switch_core_db_stmt_t *stmt, int N)
{
        return sqlite3_column_name(stmt, N);
}

SWITCH_DECLARE(int) switch_core_db_column_count(switch_core_db_stmt_t *pStmt)
{
        return sqlite3_column_count(pStmt);
}

SWITCH_DECLARE(const char *) switch_core_db_errmsg(switch_core_db_t *db)
{
        return sqlite3_errmsg(db);
}

SWITCH_DECLARE(int) switch_core_db_exec(switch_core_db_t *db, const char *sql, switch_core_db_callback_func_t callback, void *data, char **errmsg)
{
        int ret = 0;
        int sane = 300;
        char *err = NULL;

        while (--sane > 0) {
                ret = sqlite3_exec(db, sql, callback, data, &err);
                if (ret == SQLITE_BUSY || ret == SQLITE_LOCKED) {
                        switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_WARNING, "SQLite is %s, sane=%d [%s]\n", (ret == SQLITE_BUSY ? "BUSY" : "LOCKED"), sane, sql);
                        if (sane > 1) {
                                switch_core_db_free(err);
                                switch_yield(100000);
                                continue;
                        }
                } else {
                        break;
                }
        }

        if (errmsg) {
                *errmsg = err;
        } else if (err) {
                switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, "SQL ERR [%s]\n", err);
                switch_core_db_free(err);
                err = NULL;
        }

        return ret;
}

SWITCH_DECLARE(int) switch_core_db_finalize(switch_core_db_stmt_t *pStmt)
{
        return sqlite3_finalize(pStmt);
}

SWITCH_DECLARE(int) switch_core_db_prepare(switch_core_db_t *db, const char *zSql, int nBytes, switch_core_db_stmt_t **ppStmt, const char **pzTail)
{
        return sqlite3_prepare(db, zSql, nBytes, ppStmt, pzTail);
}

SWITCH_DECLARE(int) switch_core_db_step(switch_core_db_stmt_t *stmt)
{
        return sqlite3_step(stmt);
}

SWITCH_DECLARE(int) switch_core_db_reset(switch_core_db_stmt_t *pStmt)
{
        return sqlite3_reset(pStmt);
}

SWITCH_DECLARE(int) switch_core_db_bind_int(switch_core_db_stmt_t *pStmt, int i, int iValue)
{
        return sqlite3_bind_int(pStmt, i, iValue);
}

SWITCH_DECLARE(int) switch_core_db_bind_int64(switch_core_db_stmt_t *pStmt, int i, int64_t iValue)
{
        return sqlite3_bind_int64(pStmt, i, iValue);
}

SWITCH_DECLARE(int) switch_core_db_bind_text(switch_core_db_stmt_t *pStmt, int i, const char *zData, int nData, switch_core_db_destructor_type_t xDel)
{
        return sqlite3_bind_text(pStmt, i, zData, nData, xDel);
}

SWITCH_DECLARE(int) switch_core_db_bind_double(switch_core_db_stmt_t *pStmt, int i, double dValue)
{
        return sqlite3_bind_double(pStmt, i, dValue);
}

SWITCH_DECLARE(int64_t) switch_core_db_last_insert_rowid(switch_core_db_t *db)
{
        return sqlite3_last_insert_rowid(db);
}

SWITCH_DECLARE(int) switch_core_db_get_table(switch_core_db_t *db, const char *sql, char ***resultp, int *nrow, int *ncolumn, char **errmsg)
{
        return sqlite3_get_table(db, sql, resultp, nrow, ncolumn, errmsg);
}

SWITCH_DECLARE(void) switch_core_db_free_table(char **result)
{
        sqlite3_free_table(result);
}

SWITCH_DECLARE(void) switch_core_db_free(char *z)
{
        sqlite3_free(z);
}

SWITCH_DECLARE(int) switch_core_db_changes(switch_core_db_t *db)
{
        return sqlite3_changes(db);
}

SWITCH_DECLARE(int) switch_core_db_load_extension(switch_core_db_t *db, const char *extension)
{
        int ret = 0;
        char *err = NULL;

#ifdef SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION
        sqlite3_db_config(db, SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION, 1, 0);
        ret = sqlite3_load_extension(db, extension, 0, &err);
#else
        sqlite3_enable_load_extension(db, 1);
        ret = sqlite3_load_extension(db, extension, 0, &err);
        sqlite3_enable_load_extension(db, 0);
#endif

        if (err) {
                switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, "LOAD EXTENSION ERR [%s]\n", err);
                switch_core_db_free(err);
                err = NULL;
        }
        return ret;
}

static int switch_core_db_connection_setup(switch_core_db_t *db, switch_bool_t in_memory) {
        int db_ret;

        if ((db_ret = switch_core_db_exec(db, "PRAGMA synchronous=OFF;", NULL, NULL, NULL)) != SQLITE_OK) {
                goto end;
        }
        if ((db_ret = switch_core_db_exec(db, "PRAGMA count_changes=OFF;", NULL, NULL, NULL)) != SQLITE_OK) {
                goto end;
        }
        if ((db_ret = switch_core_db_exec(db, "PRAGMA temp_store=MEMORY;", NULL, NULL, NULL)) != SQLITE_OK) {
                goto end;
        }
        if (!in_memory) {
                if ((db_ret = switch_core_db_exec(db, "PRAGMA cache_size=8000;", NULL, NULL, NULL)) != SQLITE_OK) {
                        goto end;
                }
        } else {
                if ((db_ret = switch_core_db_exec(db, "PRAGMA cache_size=-8192;", NULL, NULL, NULL)) != SQLITE_OK) {
                        goto end;
                }
                if ((db_ret = switch_core_db_exec(db, "PRAGMA journal_mode=OFF;", NULL, NULL, NULL)) != SQLITE_OK) {
                        goto end;
                }
        }

end:
        return db_ret;
}

SWITCH_DECLARE(switch_core_db_t *) switch_core_db_open_file(const char *filename)
{
        switch_core_db_t *db;
        char path[1024];
        int db_ret;

        db_pick_path(filename, path, sizeof(path));
        if ((db_ret = switch_core_db_open(path, &db)) != SQLITE_OK) {
                goto end;
        }
        if ((db_ret = switch_core_db_connection_setup(db, SWITCH_FALSE)) != SQLITE_OK) {
                goto end;
        }

end:
        if (db_ret != SQLITE_OK) {
                switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, "SQL ERR [%s]\n", switch_core_db_errmsg(db));
                switch_core_db_close(db);
                db = NULL;
        }
        return db;
}

SWITCH_DECLARE(switch_core_db_t *) switch_core_db_open_in_memory(const char *uri)
{
        switch_core_db_t *db;
        int db_ret;

        if ((db_ret = switch_core_db_open_v2(uri, &db)) != SQLITE_OK) {
                goto end;
        }
        if ((db_ret = switch_core_db_connection_setup(db, SWITCH_TRUE)) != SQLITE_OK) {
                goto end;
        }

end:
        if (db_ret != SQLITE_OK) {
                switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, "SQL ERR [%s]\n", switch_core_db_errmsg(db));
                switch_core_db_close(db);
                db = NULL;
        }
        return db;
}

SWITCH_DECLARE(void) switch_core_db_test_reactive(switch_core_db_t *db, char *test_sql, char *drop_sql, char *reactive_sql)
{
        char *errmsg;

        if (!switch_test_flag((&runtime), SCF_CLEAR_SQL)) {
                return;
        }

        if (!switch_test_flag((&runtime), SCF_AUTO_SCHEMAS)) {
                switch_core_db_exec(db, test_sql, NULL, NULL, NULL);
                return;
        }


        if (db) {
                if (test_sql) {
                        switch_core_db_exec(db, test_sql, NULL, NULL, &errmsg);

                        if (errmsg) {
                                switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_DEBUG, "SQL ERR [%s]\n[%s]\nAuto Generating Table!\n", errmsg, test_sql);
                                switch_core_db_free(errmsg);
                                errmsg = NULL;
                                if (drop_sql) {
                                        switch_core_db_exec(db, drop_sql, NULL, NULL, &errmsg);
                                }
                                if (errmsg) {
                                        switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_DEBUG, "SQL ERR [%s]\n[%s]\n", errmsg, reactive_sql);
                                        switch_core_db_free(errmsg);
                                        errmsg = NULL;
                                }
                                switch_core_db_exec(db, reactive_sql, NULL, NULL, &errmsg);
                                if (errmsg) {
                                        switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_DEBUG, "SQL ERR [%s]\n[%s]\n", errmsg, reactive_sql);
                                        switch_core_db_free(errmsg);
                                        errmsg = NULL;
                                }
                        }
                }
        }

}


SWITCH_DECLARE(switch_status_t) switch_core_db_persistant_execute_trans(switch_core_db_t *db, char *sql, uint32_t retries)
{
        char *errmsg;
        switch_status_t status = SWITCH_STATUS_FALSE;
        uint8_t forever = 0;
        unsigned begin_retries = 100;
        uint8_t again = 0;

        if (!retries) {
                forever = 1;
                retries = 1000;
        }

  again:

        while (begin_retries > 0) {
                again = 0;

                switch_core_db_exec(db, "BEGIN", NULL, NULL, &errmsg);

                if (errmsg) {
                        begin_retries--;
                        if (strstr(errmsg, "cannot start a transaction within a transaction")) {
                                again = 1;
                        } else {
                                switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_DEBUG, "SQL Retry [%s]\n", errmsg);
                        }
                        switch_core_db_free(errmsg);
                        errmsg = NULL;

                        if (again) {
                                switch_core_db_exec(db, "COMMIT", NULL, NULL, NULL);
                                goto again;
                        }

                        switch_yield(100000);

                        if (begin_retries == 0) {
                                goto done;
                        }
                } else {
                        break;
                }

        }

        while (retries > 0) {
                switch_core_db_exec(db, sql, NULL, NULL, &errmsg);
                if (errmsg) {
                        switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, "SQL ERR [%s]\n", errmsg);
                        switch_core_db_free(errmsg);
                        errmsg = NULL;
                        switch_yield(100000);
                        retries--;
                        if (retries == 0 && forever) {
                                retries = 1000;
                                continue;
                        }
                } else {
                        status = SWITCH_STATUS_SUCCESS;
                        break;
                }
        }

  done:

        switch_core_db_exec(db, "COMMIT", NULL, NULL, NULL);

        return status;
}

SWITCH_DECLARE(switch_status_t) switch_core_db_persistant_execute(switch_core_db_t *db, char *sql, uint32_t retries)
{
        char *errmsg;
        switch_status_t status = SWITCH_STATUS_FALSE;
        uint8_t forever = 0;

        if (!retries) {
                forever = 1;
                retries = 1000;
        }

        while (retries > 0) {
                switch_core_db_exec(db, sql, NULL, NULL, &errmsg);
                if (errmsg) {
                        //switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, "SQL ERR [%s]\n", errmsg);
                        switch_core_db_free(errmsg);
                        switch_yield(100000);
                        retries--;
                        if (retries == 0 && forever) {
                                retries = 1000;
                                continue;
                        }
                } else {
                        status = SWITCH_STATUS_SUCCESS;
                        break;
                }
        }

        return status;
}



/* For Emacs:
 * Local Variables:
 * mode:c
 * indent-tabs-mode:t
 * tab-width:4
 * c-basic-offset:4
 * End:
 * For VIM:
 * vim:set softtabstop=4 shiftwidth=4 tabstop=4 noet:
 */