/*!
Inserts new record into the database, based on the current properties
of the object.
*/
bool TSqlObject::create()
{
// Sets the values of 'created_at', 'updated_at' or 'modified_at' properties
for (int i = metaObject()->propertyOffset(); i < metaObject()->propertyCount(); ++i) {
const char *propName = metaObject()->property(i).name();
QByteArray prop = QByteArray(propName).toLower();
if (prop == CreatedAt || prop == UpdatedAt || prop == ModifiedAt) {
setProperty(propName, QDateTime::currentDateTime());
} else if (prop == LockRevision) {
// Sets the default value of 'revision' property
setProperty(propName, 1); // 1 : default value
} else {
// do nothing
}
}
syncToSqlRecord();
QString autoValName;
QSqlRecord record = *this;
if (autoValueIndex() >= 0) {
autoValName = field(autoValueIndex()).name();
record.remove(autoValueIndex()); // not insert the value of auto-value field
}
QSqlDatabase &database = Tf::currentSqlDatabase(databaseId());
QString ins = database.driver()->sqlStatement(QSqlDriver::InsertStatement, tableName(), record, false);
if (Q_UNLIKELY(ins.isEmpty())) {
sqlError = QSqlError(QLatin1String("No fields to insert"),
QString(), QSqlError::StatementError);
tWarn("SQL statement error, no fields to insert");
return false;
}
TSqlQuery query(database);
bool ret = query.exec(ins);
sqlError = query.lastError();
if (Q_LIKELY(ret)) {
// Gets the last inserted value of auto-value field
if (autoValueIndex() >= 0) {
QVariant lastid = query.lastInsertId();
if (!lastid.isValid() && database.driverName().toUpper() == QLatin1String("QPSQL")) {
// For PostgreSQL without OIDS
ret = query.exec("SELECT LASTVAL()");
sqlError = query.lastError();
if (Q_LIKELY(ret)) {
lastid = query.getNextValue();
}
}
if (lastid.isValid()) {
QObject::setProperty(autoValName.toLatin1().constData(), lastid);
QSqlRecord::setValue(autoValueIndex(), lastid);
}
}
}
return ret;
}
bool TSqlObject::create()
{
// Sets the default value of 'revision' property
int index = metaObject()->indexOfProperty(REVISION_PROPERTY_NAME);
if (index >= 0) {
setProperty(REVISION_PROPERTY_NAME, 1); // 1 : default value
}
// Sets the values of 'created_at' and 'updated_at' properties
for (int i = metaObject()->propertyOffset(); i < metaObject()->propertyCount(); ++i) {
const char *propName = metaObject()->property(i).name();
if (QLatin1String("created_at") == propName
|| QLatin1String("updated_at") == propName) {
setProperty(propName, QDateTime::currentDateTime());
}
}
syncToSqlRecord();
QString ins = TActionContext::currentDatabase().driver()->sqlStatement(QSqlDriver::InsertStatement, tableName(), *static_cast<QSqlRecord *>(this), false);
if (ins.isEmpty()) {
sqlError = QSqlError(QLatin1String("No fields to insert"),
QString(), QSqlError::StatementError);
tWarn("SQL statement error, no fields to insert");
return false;
}
tSystemDebug("SQL statement: %s", qPrintable(ins));
QSqlQuery query(TActionContext::currentDatabase());
bool res = query.exec(ins);
sqlError = query.lastError();
if (!res) {
tSystemError("SQL insert error: %s", qPrintable(sqlError.text()));
}
return res;
}
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;
}
/*!
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;
}
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;
}
