const field_value Dataset::get_field_value(const char *f_name) {
if (ds_state != dsInactive)
{
if (ds_state == dsEdit || ds_state == dsInsert){
for (unsigned int i=0; i < edit_object->size(); i++)
if (str_compare((*edit_object)[i].props.name.c_str(), f_name)==0) {
return (*edit_object)[i].val;
}
throw DbErrors("Field not found: %s",f_name);
}
else
{
//Lets try to reuse a string ->index conversation
if (get_index_map_entry(f_name))
return get_field_value(static_cast<int>(fieldIndexMap_Entries[fieldIndexMapID].fieldIndex));
const char* name=strstr(f_name, ".");
if (name)
name++;
for (unsigned int i=0; i < fields_object->size(); i++)
if (str_compare((*fields_object)[i].props.name.c_str(), f_name) == 0 || (name && str_compare((*fields_object)[i].props.name.c_str(), name) == 0)) {
fieldIndexMap_Entries[fieldIndexMapID].fieldIndex = i;
return (*fields_object)[i].val;
}
}
throw DbErrors("Field not found: %s",f_name);
}
throw DbErrors("Dataset state is Inactive");
//field_value fv;
//return fv;
}
void SqliteDataset::make_query(StringList &_sql) {
std::string query;
if (db == NULL) throw DbErrors("No Database Connection");
try {
if (autocommit) db->start_transaction();
for (std::list<std::string>::iterator i =_sql.begin(); i!=_sql.end(); ++i) {
query = *i;
char* err=NULL;
Dataset::parse_sql(query);
if (db->setErr(sqlite3_exec(this->handle(),query.c_str(),NULL,NULL,&err),query.c_str())!=SQLITE_OK) {
throw DbErrors(db->getErrorMsg());
}
} // end of for
if (db->in_transaction() && autocommit) db->commit_transaction();
active = true;
ds_state = dsSelect;
if (autorefresh)
refresh();
} // end of try
catch(...) {
if (db->in_transaction()) db->rollback_transaction();
throw;
}
}
int SqliteDatabase::drop() {
if (active == false) throw DbErrors("Can't drop database: no active connection...");
disconnect();
if (!unlink(db.c_str())) {
throw DbErrors("Can't drop database: can't unlink the file %s,\nError: %s",db.c_str(),strerror(errno));
}
return DB_COMMAND_OK;
}
int SqliteDataset::exec(const string &sql) {
if (!handle()) throw DbErrors("No Database Connection");
int res;
exec_res.clear();
if((res = db->setErr(sqlite3_exec(handle(),sql.c_str(),&callback,&exec_res,&errmsg),sql.c_str())) == SQLITE_OK)
return res;
else
{
throw DbErrors(db->getErrorMsg());
}
}
int MysqlDatabase::drop() {
if (!active)
throw DbErrors("Can't drop database: no active connection...");
char sqlcmd[512];
int ret;
sprintf(sqlcmd,"DROP DATABASE `%s`", db.c_str());
if ( (ret=query_with_reconnect(sqlcmd)) != MYSQL_OK )
{
throw DbErrors("Can't drop database: '%s' (%d)", db.c_str(), ret);
}
disconnect();
return DB_COMMAND_OK;
}
int SqliteDataset::exec(const std::string &sql) {
if (!handle()) throw DbErrors("No Database Connection");
std::string qry = sql;
int res;
exec_res.clear();
// Strip size constraints from indexes (not supported in sqlite)
//
// Example:
// before: CREATE UNIQUE INDEX ixPath ON path ( strPath(255) )
// after: CREATE UNIQUE INDEX ixPath ON path ( strPath )
//
// NOTE: unexpected results occur if brackets are not matched
if ( qry.find("CREATE UNIQUE INDEX") != std::string::npos ||
(qry.find("CREATE INDEX") != std::string::npos))
{
size_t pos = 0;
size_t pos2 = 0;
if ( (pos = qry.find("(")) != std::string::npos )
{
pos++;
while ( (pos = qry.find("(", pos)) != std::string::npos )
{
if ( (pos2 = qry.find(")", pos)) != std::string::npos )
{
qry.replace(pos, pos2-pos+1, "");
pos = pos2;
}
}
}
}
// Strip ON table from DROP INDEX statements:
// before: DROP INDEX foo ON table
// after: DROP INDEX foo
size_t pos = qry.find("DROP INDEX ");
if ( pos != std::string::npos )
{
pos = qry.find(" ON ", pos+1);
if ( pos != std::string::npos )
qry = qry.substr(0, pos);
}
if((res = db->setErr(sqlite3_exec(handle(),qry.c_str(),&callback,&exec_res,&errmsg),qry.c_str())) == SQLITE_OK)
return res;
else
{
throw DbErrors(db->getErrorMsg());
}
}
bool Dataset::set_field_value(const char *f_name, const field_value &value) {
bool found = false;
if ((ds_state == dsInsert) || (ds_state == dsEdit)) {
for (unsigned int i=0; i < fields_object->size(); i++)
if (str_compare((*edit_object)[i].props.name.c_str(), f_name)==0) {
(*edit_object)[i].val = value;
found = true;
}
if (!found) throw DbErrors("Field not found: %s",f_name);
return true;
}
throw DbErrors("Not in Insert or Edit state");
// return false;
}
int SqliteDatabase::connect(bool create) {
if (host.empty() || db.empty())
return DB_CONNECTION_NONE;
//CLog::Log(LOGDEBUG, "Connecting to sqlite:%s:%s", host.c_str(), db.c_str());
std::string db_fullpath = URIUtils::AddFileToFolder(host, db);
try
{
disconnect();
int flags = SQLITE_OPEN_READWRITE;
if (create)
flags |= SQLITE_OPEN_CREATE;
if (sqlite3_open_v2(db_fullpath.c_str(), &conn, flags, NULL)==SQLITE_OK)
{
sqlite3_busy_handler(conn, busy_callback, NULL);
char* err=NULL;
if (setErr(sqlite3_exec(getHandle(),"PRAGMA empty_result_callbacks=ON",NULL,NULL,&err),"PRAGMA empty_result_callbacks=ON") != SQLITE_OK)
{
throw DbErrors(getErrorMsg());
}
active = true;
return DB_CONNECTION_OK;
}
return DB_CONNECTION_NONE;
}
catch(...)
{
}
return DB_CONNECTION_NONE;
}
const field_value Dataset::get_field_value(int index) {
if (ds_state != dsInactive) {
if (ds_state == dsEdit || ds_state == dsInsert) {
if (index <0 || index >field_count())
throw DbErrors("Field index not found: %d",index);
return (*edit_object)[index].val;
}
else if (index <0 || index >field_count())
throw DbErrors("Field index not found: %d",index);
return (*fields_object)[index].val;
}
throw DbErrors("Dataset state is Inactive");
//field_value fv;
//return fv;
}
int SqliteDatabase::copy(const char *backup_name) {
if (active == false)
throw DbErrors("Can't copy database: no active connection...");
CLog::Log(LOGDEBUG, "Copying from %s to %s at %s", db.c_str(), backup_name, host.c_str());
int rc;
std::string backup_db = backup_name;
sqlite3 *pFile; /* Database connection opened on zFilename */
sqlite3_backup *pBackup; /* Backup object used to copy data */
//
if (backup_name[0] == '/' || backup_name[0] == '\\')
backup_db = backup_db.substr(1);
// ensure the ".db" extension is appended to the end
if ( backup_db.find(".db") != (backup_db.length()-3) )
backup_db += ".db";
std::string backup_path = host + backup_db;
/* Open the database file identified by zFilename. Exit early if this fails
** for any reason. */
rc = sqlite3_open(backup_path.c_str(), &pFile);
if( rc==SQLITE_OK )
{
pBackup = sqlite3_backup_init(pFile, "main", getHandle(), "main");
if( pBackup )
{
(void)sqlite3_backup_step(pBackup, -1);
(void)sqlite3_backup_finish(pBackup);
}
rc = sqlite3_errcode(pFile);
}
(void)sqlite3_close(pFile);
if( rc != SQLITE_OK )
throw DbErrors("Can't copy database. (%d)", rc);
return rc;
}
void Dataset::edit() {
if (ds_state != dsSelect) {
throw DbErrors("Editing is possible only when query exists!");
}
for (unsigned int i=0; i<fields_object->size(); i++) {
(*edit_object)[i].val = (*fields_object)[i].val;
}
ds_state = dsEdit;
}
void MysqlDatabase::configure_connection() {
char sqlcmd[512];
int ret;
// MySQL 5.7.5+: See #8393
strcpy(sqlcmd, "SET SESSION sql_mode = (SELECT REPLACE(@@SESSION.sql_mode,'ONLY_FULL_GROUP_BY',''))");
if ((ret = mysql_real_query(conn, sqlcmd, strlen(sqlcmd))) != MYSQL_OK)
throw DbErrors("Can't disable sql_mode ONLY_FULL_GROUP_BY: '%s' (%d)", db.c_str(), ret);
// MySQL 5.7.6+: See #8393. Non-fatal if error, as not supported by MySQL 5.0.x
strcpy(sqlcmd, "SELECT @@SESSION.optimizer_switch");
if ((ret = mysql_real_query(conn, sqlcmd, strlen(sqlcmd))) == MYSQL_OK)
{
MYSQL_RES* res = mysql_store_result(conn);
MYSQL_ROW row;
if (res)
{
if ((row = mysql_fetch_row(res)) != NULL)
{
std::string column = row[0];
std::vector<std::string> split = StringUtils::Split(column, ',');
for (std::vector<std::string>::iterator itIn = split.begin(); itIn != split.end(); ++itIn)
{
if (StringUtils::Trim(*itIn) == "derived_merge=on")
{
strcpy(sqlcmd, "SET SESSION optimizer_switch = 'derived_merge=off'");
if ((ret = mysql_real_query(conn, sqlcmd, strlen(sqlcmd))) != MYSQL_OK)
throw DbErrors("Can't set optimizer_switch = '%s': '%s' (%d)", StringUtils::Trim(*itIn).c_str(), db.c_str(), ret);
break;
}
}
}
mysql_free_result(res);
}
}
else
CLog::Log(LOGWARNING, "Unable to query optimizer_switch: '%s' (%d)", db.c_str(), ret);
}
const field_value Dataset::get_field_value(const char *f_name) {
const char* name=strstr(f_name, ".");
if (name) name++;
if (ds_state != dsInactive) {
if (ds_state == dsEdit || ds_state == dsInsert){
for (unsigned int i=0; i < edit_object->size(); i++)
if (str_compare((*edit_object)[i].props.name.c_str(), f_name)==0) {
return (*edit_object)[i].val;
}
throw DbErrors("Field not found: %s",f_name);
}
else
for (unsigned int i=0; i < fields_object->size(); i++)
if (str_compare((*fields_object)[i].props.name.c_str(), f_name)==0 || (name && str_compare((*fields_object)[i].props.name.c_str(), name)==0)) {
return (*fields_object)[i].val;
}
throw DbErrors("Field not found: %s",f_name);
}
throw DbErrors("Dataset state is Inactive");
//field_value fv;
//return fv;
}
const field_value Dataset::get_field_value_by_index(int p_index)
{
if (ds_state != dsInactive)
{
if (ds_state == dsEdit || ds_state == dsInsert)
{
return (*edit_object)[p_index] . val;
/*for (unsigned int i=0; i < edit_object->size(); i++)
{
int t_index;
t_index = (*edit_object)[i].props.index;
if ((*edit_object)[i].props.index == p_index)
return (*edit_object)[i].val;
}
throw DbErrors("Field not found: %i", p_index);*/
}
else
{
return (*fields_object)[p_index] . val;
/* for (unsigned int i=0; i < fields_object->size(); i++)
{
int t_index;
t_index = (*edit_object)[i].props.index;
if ((*edit_object)[i].props.index == p_index)
return (*edit_object)[i] . val;
}*/
}
throw DbErrors("Field not found: %i", p_index);
}
throw DbErrors("Dataset state is Inactive");
}
int SqliteDatabase::drop_analytics(void) {
// SqliteDatabase::copy used a full database copy, so we have a new version
// with all the analytics stuff. We should clean database from everything but data
if (active == false)
throw DbErrors("Can't drop extras database: no active connection...");
char sqlcmd[4096];
result_set res;
CLog::Log(LOGDEBUG, "Cleaning indexes from database %s at %s", db.c_str(), host.c_str());
sprintf(sqlcmd, "SELECT name FROM sqlite_master WHERE type == 'index'");
if ((last_err = sqlite3_exec(conn, sqlcmd, &callback, &res, NULL)) != SQLITE_OK) return DB_UNEXPECTED_RESULT;
for (size_t i=0; i < res.records.size(); i++) {
sprintf(sqlcmd,"DROP INDEX '%s'", res.records[i]->at(0).get_asString().c_str());
if ((last_err = sqlite3_exec(conn, sqlcmd, NULL, NULL, NULL)) != SQLITE_OK) return DB_UNEXPECTED_RESULT;
}
res.clear();
CLog::Log(LOGDEBUG, "Cleaning views from database %s at %s", db.c_str(), host.c_str());
sprintf(sqlcmd, "SELECT name FROM sqlite_master WHERE type == 'view'");
if ((last_err = sqlite3_exec(conn, sqlcmd, &callback, &res, NULL)) != SQLITE_OK) return DB_UNEXPECTED_RESULT;
for (size_t i=0; i < res.records.size(); i++) {
sprintf(sqlcmd,"DROP VIEW '%s'", res.records[i]->at(0).get_asString().c_str());
if ((last_err = sqlite3_exec(conn, sqlcmd, NULL, NULL, NULL)) != SQLITE_OK) return DB_UNEXPECTED_RESULT;
}
res.clear();
CLog::Log(LOGDEBUG, "Cleaning triggers from database %s at %s", db.c_str(), host.c_str());
sprintf(sqlcmd, "SELECT name FROM sqlite_master WHERE type == 'trigger'");
if ((last_err = sqlite3_exec(conn, sqlcmd, &callback, &res, NULL)) != SQLITE_OK) return DB_UNEXPECTED_RESULT;
for (size_t i=0; i < res.records.size(); i++) {
sprintf(sqlcmd,"DROP TRIGGER '%s'", res.records[i]->at(0).get_asString().c_str());
if ((last_err = sqlite3_exec(conn, sqlcmd, NULL, NULL, NULL)) != SQLITE_OK) return DB_UNEXPECTED_RESULT;
}
// res would be cleared on destruct
return DB_COMMAND_OK;
}
int SqliteDatabase::connect() {
try {
disconnect();
if (sqlite3_open(db.c_str(),&conn/*,NULL*/)==SQLITE_OK) {
//cout << "Connected!\n";
sqlite3_busy_handler(conn, busy_callback,NULL);
char* err=NULL;
if (setErr(sqlite3_exec(getHandle(),"PRAGMA empty_result_callbacks=ON",NULL,NULL,&err),"PRAGMA empty_result_callbacks=ON") != SQLITE_OK) {
throw DbErrors(getErrorMsg());
}
active = true;
return DB_CONNECTION_OK;
}
CLog::Log(LOGERROR, "unable to open database:%s (%u)",
db.c_str(), GetLastError());
return DB_CONNECTION_NONE;
}
catch(...)
{
CLog::Log(LOGERROR, "unable to open database:%s (%u)",
db.c_str(), GetLastError());
}
return DB_CONNECTION_NONE;
}
int MysqlDatabase::drop_analytics(void) {
if ( !active || conn == NULL)
throw DbErrors("Can't clean database: no active connection...");
char sql[4096];
int ret;
// ensure we're connected to the db we are about to clean from stuff
if ( (ret=mysql_select_db(conn, db.c_str())) != MYSQL_OK )
throw DbErrors("Can't connect to database: '%s'",db.c_str());
// getting a list of indexes in the database
sprintf(sql, "SELECT DISTINCT table_name, index_name"
" FROM information_schema.statistics"
" WHERE index_name != 'PRIMARY' AND"
" table_schema = '%s'", db.c_str());
if ( (ret=query_with_reconnect(sql)) != MYSQL_OK )
throw DbErrors("Can't determine list of indexes to drop.");
// we will acquire lists here
MYSQL_RES* res = mysql_store_result(conn);
MYSQL_ROW row;
if (res)
{
while ( (row=mysql_fetch_row(res)) != NULL )
{
sprintf(sql, "ALTER TABLE `%s`.%s DROP INDEX %s", db.c_str(), row[0], row[1]);
if ( (ret=query_with_reconnect(sql)) != MYSQL_OK )
{
mysql_free_result(res);
throw DbErrors("Can't drop index '%s'\nError: %d", row[0], ret);
}
}
mysql_free_result(res);
}
// next topic is a views list
sprintf(sql, "SELECT table_name"
" FROM information_schema.views"
" WHERE table_schema = '%s'", db.c_str());
if ( (ret=query_with_reconnect(sql)) != MYSQL_OK )
throw DbErrors("Can't determine list of views to drop.");
res = mysql_store_result(conn);
if (res)
{
while ( (row=mysql_fetch_row(res)) != NULL )
{
/* we do not need IF EXISTS because these views are exist */
sprintf(sql, "DROP VIEW `%s`.%s", db.c_str(), row[0]);
if ( (ret=query_with_reconnect(sql)) != MYSQL_OK )
{
mysql_free_result(res);
throw DbErrors("Can't drop view '%s'\nError: %d", row[0], ret);
}
}
mysql_free_result(res);
}
// triggers
sprintf(sql, "SELECT trigger_name"
" FROM information_schema.triggers"
" WHERE event_object_schema = '%s'", db.c_str());
if ( (ret=query_with_reconnect(sql)) != MYSQL_OK )
throw DbErrors("Can't determine list of triggers to drop.");
res = mysql_store_result(conn);
if (res)
{
while ( (row=mysql_fetch_row(res)) != NULL )
{
sprintf(sql, "DROP TRIGGER `%s`.%s", db.c_str(), row[0]);
if ( (ret=query_with_reconnect(sql)) != MYSQL_OK )
{
mysql_free_result(res);
throw DbErrors("Can't create trigger '%s'\nError: %d", row[0], ret);
}
}
mysql_free_result(res);
}
return 1;
}
int MysqlDatabase::connect(bool create_new) {
if (host.empty() || db.empty())
return DB_CONNECTION_NONE;
//CLog::Log(LOGDEBUG, "Connecting to mysql:%s:%s", host.c_str(), db.c_str());
try
{
disconnect();
if (conn == NULL) {
conn = mysql_init(conn);
mysql_ssl_set(
conn,
key.empty() ? NULL : key.c_str(),
cert.empty() ? NULL : cert.c_str(),
ca.empty() ? NULL : ca.c_str(),
capath.empty() ? NULL : capath.c_str(),
ciphers.empty() ? NULL : ciphers.c_str());
}
if (!CWakeOnAccess::GetInstance().WakeUpHost(host, "MySQL : " + db))
return DB_CONNECTION_NONE;
// establish connection with just user credentials
if (mysql_real_connect(conn, host.c_str(),
login.c_str(),
passwd.c_str(),
NULL,
atoi(port.c_str()),
NULL,
compression ? CLIENT_COMPRESS : 0) != NULL)
{
// disable mysql autocommit since we handle it
//mysql_autocommit(conn, false);
// enforce utf8 charset usage
default_charset = mysql_character_set_name(conn);
if(mysql_set_character_set(conn, "utf8")) // returns 0 on success
{
CLog::Log(LOGERROR, "Unable to set utf8 charset: %s [%d](%s)",
db.c_str(), mysql_errno(conn), mysql_error(conn));
}
// check existence
if (exists())
{
// nothing to see here
}
else if (create_new)
{
char sqlcmd[512];
int ret;
sprintf(sqlcmd, "CREATE DATABASE `%s` CHARACTER SET utf8 COLLATE utf8_general_ci", db.c_str());
if ( (ret=query_with_reconnect(sqlcmd)) != MYSQL_OK )
{
throw DbErrors("Can't create new database: '%s' (%d)", db.c_str(), ret);
}
}
if (mysql_select_db(conn, db.c_str()) == 0)
{
active = true;
return DB_CONNECTION_OK;
}
}
// if we failed above, either credentials were incorrect or the database didn't exist
if (mysql_errno(conn) == ER_BAD_DB_ERROR && create_new)
{
if (create() == MYSQL_OK)
{
active = true;
return DB_CONNECTION_OK;
}
}
CLog::Log(LOGERROR, "Unable to open database: %s [%d](%s)",
db.c_str(), mysql_errno(conn), mysql_error(conn));
return DB_CONNECTION_NONE;
}
catch(...)
{
CLog::Log(LOGERROR, "Unable to open database: %s (%u)",
db.c_str(), GetLastError());
}
return DB_CONNECTION_NONE;
}
bool SqliteDataset::query(const std::string &query) {
if(!handle()) throw DbErrors("No Database Connection");
std::string qry = query;
int fs = qry.find("select");
int fS = qry.find("SELECT");
if (!( fs >= 0 || fS >=0))
throw DbErrors("MUST be select SQL!");
close();
sqlite3_stmt *stmt = NULL;
if (db->setErr(sqlite3_prepare_v2(handle(),query.c_str(),-1,&stmt, NULL),query.c_str()) != SQLITE_OK)
throw DbErrors(db->getErrorMsg());
// column headers
const unsigned int numColumns = sqlite3_column_count(stmt);
result.record_header.resize(numColumns);
for (unsigned int i = 0; i < numColumns; i++)
result.record_header[i].name = sqlite3_column_name(stmt, i);
// returned rows
while (sqlite3_step(stmt) == SQLITE_ROW)
{ // have a row of data
sql_record *res = new sql_record;
res->resize(numColumns);
for (unsigned int i = 0; i < numColumns; i++)
{
field_value &v = res->at(i);
switch (sqlite3_column_type(stmt, i))
{
case SQLITE_INTEGER:
v.set_asInt64(sqlite3_column_int64(stmt, i));
break;
case SQLITE_FLOAT:
v.set_asDouble(sqlite3_column_double(stmt, i));
break;
case SQLITE_TEXT:
v.set_asString((const char *)sqlite3_column_text(stmt, i));
break;
case SQLITE_BLOB:
v.set_asString((const char *)sqlite3_column_text(stmt, i));
break;
case SQLITE_NULL:
default:
v.set_asString("");
v.set_isNull();
break;
}
}
result.records.push_back(res);
}
if (db->setErr(sqlite3_finalize(stmt),query.c_str()) == SQLITE_OK)
{
active = true;
ds_state = dsSelect;
this->first();
return true;
}
else
{
throw DbErrors(db->getErrorMsg());
}
}
int64_t SqliteDataset::lastinsertid()
{
if(!handle()) throw DbErrors("No Database Connection");
return sqlite3_last_insert_rowid(handle());
}
int MysqlDatabase::copy(const char *backup_name) {
if ( !active || conn == NULL)
throw DbErrors("Can't copy database: no active connection...");
char sql[4096];
int ret;
// ensure we're connected to the db we are about to copy
if ( (ret=mysql_select_db(conn, db.c_str())) != MYSQL_OK )
throw DbErrors("Can't connect to source database: '%s'",db.c_str());
// grab a list of base tables only (no views)
sprintf(sql, "SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'");
if ( (ret=query_with_reconnect(sql)) != MYSQL_OK )
throw DbErrors("Can't determine base tables for copy.");
// get list of all tables from old DB
MYSQL_RES* res = mysql_store_result(conn);
if (res)
{
if (mysql_num_rows(res) == 0)
{
mysql_free_result(res);
throw DbErrors("The source database was unexpectedly empty.");
}
// create the new database
sprintf(sql, "CREATE DATABASE `%s` CHARACTER SET utf8 COLLATE utf8_general_ci", backup_name);
if ( (ret=query_with_reconnect(sql)) != MYSQL_OK )
{
mysql_free_result(res);
throw DbErrors("Can't create database for copy: '%s' (%d)", db.c_str(), ret);
}
MYSQL_ROW row;
// duplicate each table from old db to new db
while ( (row=mysql_fetch_row(res)) != NULL )
{
// copy the table definition
sprintf(sql, "CREATE TABLE `%s`.%s LIKE %s",
backup_name, row[0], row[0]);
if ( (ret=query_with_reconnect(sql)) != MYSQL_OK )
{
mysql_free_result(res);
throw DbErrors("Can't copy schema for table '%s'\nError: %d", row[0], ret);
}
// copy the table data
sprintf(sql, "INSERT INTO `%s`.%s SELECT * FROM %s",
backup_name, row[0], row[0]);
if ( (ret=query_with_reconnect(sql)) != MYSQL_OK )
{
mysql_free_result(res);
throw DbErrors("Can't copy data for table '%s'\nError: %d", row[0], ret);
}
}
mysql_free_result(res);
// we don't recreate views, indicies, or triggers on copy
// as we'll be dropping and recreating them anyway
}
return 1;
}
