bool TSqlObject::remove()
{
syncToSqlRecord();
QString del = TActionContext::currentDatabase().driver()->sqlStatement(QSqlDriver::DeleteStatement, tableName(), *static_cast<QSqlRecord *>(this), false);
if (del.isEmpty()) {
sqlError = QSqlError(QLatin1String("Unable to delete row"),
QString(), QSqlError::StatementError);
return false;
}
del.append(" WHERE ");
int revIndex = metaObject()->indexOfProperty(REVISION_PROPERTY_NAME);
if (revIndex >= 0) {
bool ok;
int revsion = property(REVISION_PROPERTY_NAME).toInt(&ok);
if (!ok || revsion <= 0) {
sqlError = QSqlError(QLatin1String("Unable to convert the 'revision' property to an int"),
QString(), QSqlError::UnknownError);
tError("Unable to convert the 'revsion' property to an int, %s", qPrintable(objectName()));
return false;
}
del.append(TSqlQuery::escapeIdentifier(REVISION_PROPERTY_NAME));
del.append("=").append(TSqlQuery::formatValue(revsion));
del.append(" AND ");
}
const char *pkName = metaObject()->property(metaObject()->propertyOffset() + primaryKeyIndex()).name();
if (primaryKeyIndex() < 0 || !pkName) {
QString msg = QString("Not found the primary key for table ") + tableName();
sqlError = QSqlError(msg, QString(), QSqlError::StatementError);
tError("%s", qPrintable(msg));
return false;
}
del.append(TSqlQuery::escapeIdentifier(pkName));
del.append("=").append(TSqlQuery::formatValue(property(pkName)));
tSystemDebug("SQL statement: %s", qPrintable(del));
QSqlQuery query(TActionContext::currentDatabase());
bool res = query.exec(del);
sqlError = query.lastError();
if (!res) {
tSystemError("SQL delete error: %s", qPrintable(sqlError.text()));
return false;
}
// Optimistic lock check
if (query.numRowsAffected() != 1) {
if (revIndex >= 0) {
QString msg = QString("Row was updated or deleted from table ") + tableName() + QLatin1String(" by another transaction");
sqlError = QSqlError(msg, QString(), QSqlError::UnknownError);
throw SqlException(msg, __FILE__, __LINE__);
}
tWarn("Row was deleted by another transaction, %s", qPrintable(tableName()));
}
clear();
return true;
}
void MySQLConnection::connect()
{
bool reconnecting = false;
if (_myConn != nullptr) //reconnection attempt
{
if (!mysql_ping(_myConn)) //ping ok
return;
else
reconnecting = true;
}
//remove any state from previous session
this->clear();
Poco::Logger& logger = _dbEngine->getLogger();
for(;;)
{
const char* unix_socket = nullptr;
if (_unix_socket.length() > 0)
unix_socket = _unix_socket.c_str();
_myConn = mysql_real_connect(_myHandle, _host.c_str(), _user.c_str(), _password.c_str(), _database.c_str(), _port, unix_socket, CLIENT_REMEMBER_OPTIONS);
if (!_myConn)
{
const char* actionToDo = "connect";
if (reconnecting)
actionToDo = "reconnect";
unsigned int errNo = mysql_errno(_myHandle);
if (IsConnectionErrFatal(errNo))
throw SqlException(errNo,mysql_error(_myHandle),actionToDo);
static const long sleepTime = 1000;
logger.warning(Poco::format("Could not %s to MySQL database at %s: %s, retrying in %d seconds",
string(actionToDo),_host,string(mysql_error(_myHandle)),static_cast<int>(sleepTime/1000)));
Poco::Thread::sleep(sleepTime);
continue;
}
break;
}
string actionDone = (reconnecting)?string("Reconnected"):string("Connected");
poco_information(logger,Poco::format( actionDone + " to MySQL database %s:%d/%s client ver: %s server ver: %s",
_host, _port,_database,string(mysql_get_client_info()),string(mysql_get_server_info(_myConn)) ));
//Autocommit should be ON because without it, MySQL would require everything to be wrapped into a transaction
if (!mysql_autocommit(_myConn, 1))
poco_trace(logger,"Set autocommit to true");
else
poco_error(logger,"Failed to set autocommit to true");
//set connection properties to UTF8 to properly handle locales for different server configs
//core sends data in UTF8, so MySQL must expect UTF8 too
if (!mysql_set_character_set(_myConn,"utf8"))
poco_trace(logger,Poco::format("Character set changed to %s",string(mysql_character_set_name(_myConn))));
else
poco_error(logger,Poco::format("Failed to change charset, remains at %s",string(mysql_character_set_name(_myConn))));
}
/*!
* \brief SqlQuery::exec Execute a existing query with capability to throw SqlException
* if necessary.
* \return true if successfully executed, false otherwise.
*/
bool SqlQuery::exec() throw(SqlException)
{
bool b = m_sqlQuery.exec();
QSqlError sqlError = m_sqlQuery.lastError();
switch(sqlError.type ())
{
case QSqlError::NoError:
return b;
case QSqlError::ConnectionError:
case QSqlError::StatementError:
if(sqlError.databaseText() == "MySQL server has gone away"){
throw SqlConnectionException(sqlError);
}else{
throw SqlStatementException(sqlError);
}
case QSqlError::TransactionError:
throw SqlTransactionException(sqlError);
case QSqlError::UnknownError:
throw SqlUnknownException(sqlError);
default:
throw SqlException(sqlError);
}
}
void SqlQuery::exec()
{
SqlQueryManager::instance()->checkDbIsAlive(m_db);
#ifdef SQLATE_ENABLE_NETWORK_WATCHER
KDThreadRunner<SqlQueryWatcherHelper> watcher;
SqlQueryWatcherHelper* helper = watcher.startThread();
#endif
bool result = QSqlQuery::exec();
#ifdef SQLATE_ENABLE_NETWORK_WATCHER
QMetaObject::invokeMethod( helper, "quit", Qt::QueuedConnection );
watcher.wait();
#endif
if (!result && ( !m_db.isOpen() || !m_db.isValid() ) ) {
SqlQueryManager::instance()->checkDbIsAlive(m_db); //double check is needed, as Qt might not set m_db.isOpen() to false after connection loss if no queries were run meantime.
result = QSqlQuery::exec();
}
if ( !result ) {
qWarning() << Q_FUNC_INFO << "Exec failed: " << this << QSqlQuery::lastError() << " query was: " << QSqlQuery::lastQuery()
<< ", executed query: " << QSqlQuery::executedQuery() << " bound values: "<< QSqlQuery::boundValues().values();
// qWarning() << "Database status: " << m_db.isOpen() << m_db.isValid() << m_db.isOpenError();
throw SqlException( QSqlQuery::lastError() );
}
}
SqlConnection::SqlConnection()
{
if(sqlite3_open_v2("users.db", &connection, SQLITE_OPEN_READWRITE, NULL) != SQLITE_OK)
{
throw SqlException("Unable to open database");
}
}
void MySQLConnection::_MySQLStmtPrepare(const SqlPreparedStatement& who, MYSQL_STMT* stmt, const char* sqlText, size_t textLen)
{
int returnVal = mysql_stmt_prepare(stmt, sqlText, textLen);
if (returnVal)
{
auto errInfo = StmtErrorInfo(who.lastError());
throw SqlException(who.lastError(),who.lastErrorDescr(),"MySQLStmtPrepare",errInfo.first,!errInfo.second,who.getSqlString());
}
}
std::shared_ptr<QSqlQuery> dataBase::query(const QString& q, const QString& dbName)
{
std::shared_ptr<QSqlQuery> db_query(new QSqlQuery("", QSqlDatabase::database(dbName)));
if(db_query->exec(q))
{
return db_query;
}
throw SqlException(db_query->lastError(), q);
}
void MySQLConnection::_MySQLStmtExecute(const SqlPreparedStatement& who, MYSQL_STMT* stmt)
{
int returnVal = mysql_stmt_execute(stmt);
if (returnVal)
{
auto errInfo = StmtErrorInfo(who.lastError());
throw SqlException(who.lastError(),who.lastErrorDescr(),"MySQLStmtExecute",errInfo.first,!errInfo.second,who.getSqlString(true));
}
}
void MySQLConnection::_MySQLQuery(const char* sql)
{
int returnVal = mysql_query(_myConn,sql);
if (returnVal)
{
returnVal = mysql_errno(_myConn);
bool connLost = IsConnectionLost(returnVal);
throw SqlException(returnVal,mysql_error(_myConn),"MySQLQuery",connLost,connLost,sql);
}
}
FOR_EACH_UINT32(i, mResultCount)
{
MYSQL_BIND& bind = resultBinds[i];
ulong alreadyRetrieved = bind.buffer_length;
if (bind.length != nullptr&&alreadyRetrieved < *bind.length)
{
if (bind.buffer_type == MYSQL_TYPE_BLOB ||
bind.buffer_type == MYSQL_TYPE_TINY_BLOB ||
bind.buffer_type == MYSQL_TYPE_MEDIUM_BLOB ||
bind.buffer_type == MYSQL_TYPE_LONG_BLOB)
{
MemoryData& data = *(MemoryData*)bind.extension;
data.Realloc(*bind.length);
bind.buffer = data.MutableData() + alreadyRetrieved;
bind.buffer_length = (ulong)data.Size() - alreadyRetrieved;
const int status = mysql_stmt_fetch_column(mSTMT, &bind, i, alreadyRetrieved);
if (0 != status)
{
throw SqlException(mSTMT, mysql_stmt_error(mSTMT));
}
}
else if (bind.buffer_type == MYSQL_TYPE_VAR_STRING ||
bind.buffer_type == MYSQL_TYPE_STRING ||
bind.buffer_type == MYSQL_TYPE_VARCHAR)
{
HeapString& str = *(HeapString*)bind.extension;
str.ReserveLength(*bind.length);
bind.buffer = str.MutableBuffer() + alreadyRetrieved;
bind.buffer_length = (ulong)str.Size() - alreadyRetrieved;
const int status = mysql_stmt_fetch_column(mSTMT, &bind, i, alreadyRetrieved);
if (0 != status)
{
throw SqlException(mSTMT, mysql_stmt_error(mSTMT));
}
str.ForceSetLength(*bind.length);
}
}
}
bool PostgreSQLConnection::_Query(const char* sql)
{
if (!_pgConn)
return false;
int returnVal = PQsendQuery(_pgConn,sql);
if (!returnVal)
{
bool connLost = _ConnectionLost();
throw SqlException(returnVal,lastErrorDescr(),"Query",connLost,connLost,sql);
}
return true;
}
bool SqlPreparedStatement::Prepare()
{
auto sql = Sql();
mSTMT = mysql_stmt_init(Sql());
if (mSTMT == nullptr)
{
throw SqlException(sql, "Error in SqlPreparedStatement::mysql_stmt_init");
}
if (mysql_stmt_prepare(mSTMT, mStatement.c_str(), (ulong)mStatement.Length()) != 0)
{
SqlException e(mSTMT, "Error in SqlPreparedStatement::mysql_stmt_prepare");
mysql_stmt_free_result(mSTMT);
mysql_stmt_close(mSTMT);
throw e;
}
mParamterCount = mysql_stmt_param_count(mSTMT);
mResultCount = mysql_stmt_field_count(mSTMT);
if (mResultCount!=0)
{
if ((mResultMetadata = mysql_stmt_result_metadata(mSTMT)) == NULL)
{
throw SqlException(mSTMT, "Error in SqlPreparedStatement::mysql_stmt_result_metadata");
}
MYSQL_FIELD* field = nullptr;
while ((field = mysql_fetch_field(mResultMetadata)) != nullptr)
{
mResultFields.Add(field);
}
}
return true;
}
bool PostgreSQLConnection::_PostgreStoreResult( const char* sql, ResultInfo* outResInfo )
{
PGresult* outResult = PQgetResult(_pgConn);
if (!outResult)
return false;
ExecStatusType resStatus = PQresultStatus(outResult);
int outRowCount = 0;
int outFieldCount = 0;
if (resStatus == PGRES_TUPLES_OK) //has resultset
{
outRowCount = PQntuples(outResult);
outFieldCount = PQnfields(outResult);
}
else if (resStatus == PGRES_COMMAND_OK) //rows affected
{
const char* numTuples = PQcmdTuples(outResult);
if (strlen(numTuples) > 0)
outRowCount = atoi(numTuples);
//don't need it anymore
PQclear(outResult);
outResult = nullptr;
}
else //errored
{
PQclear(outResult);
outResult = nullptr;
bool connLost = _ConnectionLost();
throw SqlException(resStatus,lastErrorDescr(outResult),"PostgreStoreResult",connLost,connLost,sql);
}
if (outResInfo != nullptr)
{
outResInfo->pgRes = outResult;
outResInfo->numFields = outFieldCount;
outResInfo->numRows = outRowCount;
}
else if (outResult != nullptr)
{
PQclear(outResult);
outResult = nullptr;
}
return true;
}
MYSQL_RES* MySQLConnection::_MySQLStoreResult(const char* sql, UInt64& outRowCount, size_t& outFieldCount)
{
MYSQL_RES* outResult = mysql_store_result(_myConn);
if (outResult)
{
outRowCount = mysql_num_rows(outResult);
outFieldCount = mysql_num_fields(outResult);
}
else
{
int resultRetVal = mysql_errno(_myConn);
if (resultRetVal)
throw SqlException(resultRetVal,mysql_error(_myConn),"MySQLStoreResult",IsConnectionLost(resultRetVal),true,sql);
else
{
outRowCount = mysql_affected_rows(_myConn);
outFieldCount = mysql_field_count(_myConn);
}
}
return outResult;
}
bool SqlConnection::insertUser(const std::string &name, const std::string &password, const std::string &salt)
{
sqlite3_stmt * stat;
prepareStatement(&stat, PREPARE_INSERT_STATEMENT);
sqlite3_bind_text(stat, 1, name.c_str(), name.length()+1, NULL);
sqlite3_bind_text(stat, 2, password.c_str(), password.length()+1, NULL);
sqlite3_bind_text(stat, 3, salt.c_str(), salt.length()+1, NULL);
QMutexLocker lock(&mutex);
if(sqlite3_step(stat) != SQLITE_DONE)
{
sqlite3_finalize(stat);
throw SqlException("The statement was not executed");
}
sqlite3_finalize(stat);
//sqlite3_reset(include);
return true;
}
SqlConnection::ContainedData SqlConnection::getUserByName(const std::string &username)
{
sqlite3_stmt * stat;
prepareStatement(&stat, PREPARE_SELECT_USER_STATEMENT);
sqlite3_bind_text(stat, 1, username.c_str(), username.length() +1, NULL);
QMutexLocker l(&mutex);
if(sqlite3_step(stat) !=SQLITE_ROW)
{
sqlite3_finalize(stat);
throw SqlException("The user is not in the db");
}
ContainedData t;
t.id = sqlite3_column_int(stat, 0);
char * t1, *t2, *t3;
t1 = (char*)sqlite3_column_text(stat,1);
t2 = (char*)sqlite3_column_text(stat,2);
t3 = (char*)sqlite3_column_text(stat,3);
QString name(t1), pass(t2), salt(t3);
t.name = name;
t.password = pass;
t.salt = salt;
//free(t1);
//free(t2);
//free(t3);
sqlite3_finalize(stat);
return t;
}
/*!
Deletes the record with this primary key from the database.
*/
bool TSqlObject::remove()
{
if (isNew()) {
sqlError = QSqlError(QLatin1String("No record to remove"),
QString(), QSqlError::UnknownError);
tWarn("Unable to remove the '%s' object. Create it before!", metaObject()->className());
return false;
}
QSqlDatabase &database = Tf::currentSqlDatabase(databaseId());
QString del = database.driver()->sqlStatement(QSqlDriver::DeleteStatement, tableName(), *static_cast<QSqlRecord *>(this), false);
if (del.isEmpty()) {
sqlError = QSqlError(QLatin1String("Unable to delete row"),
QString(), QSqlError::StatementError);
return false;
}
del.append(" WHERE ");
int revIndex = -1;
for (int i = metaObject()->propertyOffset(); i < metaObject()->propertyCount(); ++i) {
const char *propName = metaObject()->property(i).name();
QByteArray prop = QByteArray(propName).toLower();
if (prop == LockRevision) {
bool ok;
int revision = property(propName).toInt(&ok);
if (!ok || revision <= 0) {
sqlError = QSqlError(QLatin1String("Unable to convert the 'revision' property to an int"),
QString(), QSqlError::UnknownError);
tError("Unable to convert the 'revision' property to an int, %s", qPrintable(objectName()));
return false;
}
del.append(QLatin1String(propName));
del.append("=").append(TSqlQuery::formatValue(revision, database));
del.append(" AND ");
revIndex = i;
break;
}
}
const char *pkName = metaObject()->property(metaObject()->propertyOffset() + primaryKeyIndex()).name();
if (primaryKeyIndex() < 0 || !pkName) {
QString msg = QString("Primary key not found for table ") + tableName() + QLatin1String(". Create a primary key!");
sqlError = QSqlError(msg, QString(), QSqlError::StatementError);
tError("%s", qPrintable(msg));
return false;
}
del.append(QLatin1String(pkName));
del.append("=").append(TSqlQuery::formatValue(value(pkName), database));
TSqlQuery query(database);
bool ret = query.exec(del);
sqlError = query.lastError();
if (ret) {
// Optimistic lock check
if (query.numRowsAffected() != 1) {
if (revIndex >= 0) {
QString msg = QString("Row was updated or deleted from table ") + tableName() + QLatin1String(" by another transaction");
sqlError = QSqlError(msg, QString(), QSqlError::UnknownError);
throw SqlException(msg, __FILE__, __LINE__);
}
tWarn("Row was deleted by another transaction, %s", qPrintable(tableName()));
}
clear();
}
return ret;
}
bool TSqlObject::update()
{
if (isNew()) {
sqlError = QSqlError(QLatin1String("No record to update"),
QString(), QSqlError::UnknownError);
tWarn("Unable to update the '%s' object. Create it before!", metaObject()->className());
return false;
}
QString where(" WHERE ");
int revIndex = metaObject()->indexOfProperty(REVISION_PROPERTY_NAME);
if (revIndex >= 0) {
bool ok;
int oldRevision = property(REVISION_PROPERTY_NAME).toInt(&ok);
if (!ok || oldRevision <= 0) {
sqlError = QSqlError(QLatin1String("Unable to convert the 'revision' property to an int"),
QString(), QSqlError::UnknownError);
tError("Unable to convert the 'revision' property to an int, %s", qPrintable(objectName()));
return false;
}
setProperty(REVISION_PROPERTY_NAME, oldRevision + 1);
where.append(TSqlQuery::escapeIdentifier(REVISION_PROPERTY_NAME));
where.append("=").append(TSqlQuery::formatValue(oldRevision));
where.append(" AND ");
}
// Updates the value of 'updated_at' property
for (int i = metaObject()->propertyOffset(); i < metaObject()->propertyCount(); ++i) {
const char *propName = metaObject()->property(i).name();
if (QLatin1String("updated_at") == propName) {
setProperty(propName, QDateTime::currentDateTime());
break;
}
}
syncToSqlRecord();
QString upd = TActionContext::currentDatabase().driver()->sqlStatement(QSqlDriver::UpdateStatement, tableName(), *static_cast<QSqlRecord *>(this), false);
if (upd.isEmpty()) {
sqlError = QSqlError(QLatin1String("No Fields to update"),
QString(), QSqlError::StatementError);
return false;
}
const char *pkName = metaObject()->property(metaObject()->propertyOffset() + primaryKeyIndex()).name();
if (primaryKeyIndex() < 0 || !pkName) {
QString msg = QString("Not found the primary key for table ") + tableName();
sqlError = QSqlError(msg, QString(), QSqlError::StatementError);
tError("%s", qPrintable(msg));
return false;
}
where.append(TSqlQuery::escapeIdentifier(pkName));
where.append("=").append(TSqlQuery::formatValue(property(pkName)));
upd.append(where);
tSystemDebug("SQL statement: %s", qPrintable(upd));
QSqlQuery query(TActionContext::currentDatabase());
bool res = query.exec(upd);
sqlError = query.lastError();
if (!res) {
tSystemError("SQL update error: %s", qPrintable(sqlError.text()));
return false;
}
// Optimistic lock check
if (revIndex >= 0 && query.numRowsAffected() != 1) {
QString msg = QString("Row was updated or deleted from table ") + tableName() + QLatin1String(" by another transaction");
sqlError = QSqlError(msg, QString(), QSqlError::UnknownError);
throw SqlException(msg, __FILE__, __LINE__);
}
return true;
}
/*!
Updates the corresponding record with the properties of the object.
*/
bool TSqlObject::update()
{
if (isNew()) {
sqlError = QSqlError(QLatin1String("No record to update"),
QString(), QSqlError::UnknownError);
tWarn("Unable to update the '%s' object. Create it before!", metaObject()->className());
return false;
}
QSqlDatabase &database = Tf::currentSqlDatabase(databaseId());
QString where(" WHERE ");
// Updates the value of 'updated_at' or 'modified_at' property
bool updflag = false;
int revIndex = -1;
for (int i = metaObject()->propertyOffset(); i < metaObject()->propertyCount(); ++i) {
const char *propName = metaObject()->property(i).name();
QByteArray prop = QByteArray(propName).toLower();
if (!updflag && (prop == UpdatedAt || prop == ModifiedAt)) {
setProperty(propName, QDateTime::currentDateTime());
updflag = true;
} else if (revIndex < 0 && prop == LockRevision) {
bool ok;
int oldRevision = property(propName).toInt(&ok);
if (!ok || oldRevision <= 0) {
sqlError = QSqlError(QLatin1String("Unable to convert the 'revision' property to an int"),
QString(), QSqlError::UnknownError);
tError("Unable to convert the 'revision' property to an int, %s", qPrintable(objectName()));
return false;
}
setProperty(propName, oldRevision + 1);
revIndex = i;
where.append(QLatin1String(propName));
where.append("=").append(TSqlQuery::formatValue(oldRevision, database));
where.append(" AND ");
} else {
// continue
}
}
QString upd; // UPDATE Statement
upd.reserve(255);
upd.append(QLatin1String("UPDATE ")).append(tableName()).append(QLatin1String(" SET "));
int pkidx = metaObject()->propertyOffset() + primaryKeyIndex();
const char *pkName = metaObject()->property(pkidx).name();
if (primaryKeyIndex() < 0 || !pkName) {
QString msg = QString("Primary key not found for table ") + tableName() + QLatin1String(". Create a primary key!");
sqlError = QSqlError(msg, QString(), QSqlError::StatementError);
tError("%s", qPrintable(msg));
return false;
}
QVariant origpkval = value(pkName);
where.append(QLatin1String(pkName));
where.append("=").append(TSqlQuery::formatValue(origpkval, database));
// Restore the value of primary key
QObject::setProperty(pkName, origpkval);
for (int i = metaObject()->propertyOffset(); i < metaObject()->propertyCount(); ++i) {
const char *propName = metaObject()->property(i).name();
QVariant newval = QObject::property(propName);
QVariant recval = QSqlRecord::value(QLatin1String(propName));
if (i != pkidx && recval.isValid() && recval != newval) {
upd.append(QLatin1String(propName));
upd.append(QLatin1Char('='));
upd.append(TSqlQuery::formatValue(newval, database));
upd.append(QLatin1String(", "));
}
}
if (!upd.endsWith(QLatin1String(", "))) {
tSystemDebug("SQL UPDATE: Same values as that of the record. No need to update.");
return true;
}
upd.chop(2);
syncToSqlRecord();
upd.append(where);
TSqlQuery query(database);
bool ret = query.exec(upd);
sqlError = query.lastError();
if (ret) {
// Optimistic lock check
if (revIndex >= 0 && query.numRowsAffected() != 1) {
QString msg = QString("Row was updated or deleted from table ") + tableName() + QLatin1String(" by another transaction");
sqlError = QSqlError(msg, QString(), QSqlError::UnknownError);
throw SqlException(msg, __FILE__, __LINE__);
}
}
return ret;
}
