sql_value to_value(const shared_ptr<sqlite3_stmt> &stmt, int column)
{
if (stmt == nullptr || column < 0 || column >= sqlite3_column_count(stmt.get())) {
return sql_value();
}
switch (sqlite3_column_type(stmt.get(), column)) {
case SQLITE_INTEGER:
return sql_number(sqlite3_column_int64(stmt.get(), column));
case SQLITE3_TEXT:
default: {
const unsigned char *textValue = sqlite3_column_text(stmt.get(), column);
if (textValue != NULL) {
return sql_string(reinterpret_cast<const char *>(textValue));
}
return sql_value();
}
case SQLITE_FLOAT:
return sql_number(sqlite3_column_double(stmt.get(), column));
case SQLITE_BLOB: {
const unsigned char *blob =
reinterpret_cast<const unsigned char *>(sqlite3_column_blob(stmt.get(), column));
return sql_blob(blob, blob + sqlite3_column_bytes(stmt.get(), column));
}
}
}
sql_value schema::default_value(const std::string &name) const {
for (auto &c : columns_) {
if (c.name == name) {
return c.default_value;
}
}
return sql_value();
}
bool QmvSqlTuple::reload( bool withforeign )
{
QString query;
const QString & cn = parent_class->relationName();
const QString & pk = parent_class->serialKey();
const QString & pkv = this->attributeValue(pk);
int rows;
if (pkv) // This tuple exists in persistent storage.
{
query = QString("SELECT %1 FROM %2 WHERE %3 = '%4'::%5")
.arg(parent_class->SQLSelectList())
.arg(cn)
.arg(pk)
.arg(pkv)
.arg(parent_class->attAttValue(pk, QString("mtat_type")));
rows = sql_exec(query);
if (rows != 1)
{
qDebug("QmvSqlTuple::reload():lastError = <%s>", lastError().latin1() );
return(FALSE);
}
// reload tuple from persistent storage
QDictIterator<QmvTupleAttribute> it_mt(*this);
it_mt.toFirst();
while (it_mt.current())
{
it_mt.current()->load( QString(sql_value(0, it_mt.currentKey().latin1())) );
++it_mt;
}
}
return(TRUE);
}
base_query &base_query::bind(size_t index, const string &value, int len)
{
bindings_[assert_binding_index(index)] = sql_value(len > 0 ? value.substr(0, len) : value);
return *this;
}
base_query &base_query::bind(size_t index, const sql_blob &value)
{
bindings_[assert_binding_index(index)] = sql_value(value);
return *this;
}
base_query &base_query::bind(size_t index, const void *data, size_t size, void (*pFree)(void *))
{
bindings_[assert_binding_index(index)] = sql_value(sql_blob(data, size, pFree));
return *this;
}
base_query &base_query::bind(size_t index, double value)
{
bindings_[assert_binding_index(index)] = sql_value(value);
return *this;
}
/*!
\fn QmvSqlTuple::save()
Update it it it already exists, else insert it.
\return TRUE for success, else FALSE
*/
bool QmvSqlTuple::save()
{
QString sql_setlist;
QString sql_att_list;
QString sql_val_list;
QString attnam, attval;
const QString x_holder( "__@@__@@__@@__");
QString attatt = "mtat_type";
// Iterate over tuple attributes and build a "SET var=val" list
// from changed attributes.
QDictIterator<QmvTupleAttribute> it_mt(*this);
while (it_mt.current())
{
if (it_mt.current()->isChanged() && it_mt.current()->inputMethod() != QmvAttribute::Virtual )
{
if (sql_setlist.length())
sql_setlist.append(",");
if (sql_att_list.length())
sql_att_list.append(",");
if (sql_val_list.length())
sql_val_list.append(",");
// attribute name and value
attnam = it_mt.currentKey();
attval = it_mt.current()->currentValue();
attval = QmvClass::cleanForSQL(attval);
attval.replace( QRegExp("%"), x_holder );
// timestamps do not handle '' - use null
QString newvalue;
if (attval.length() == 0 || attval.lower() == "null")
newvalue = "null";
else
newvalue = QString("'%1'").arg(attval);
// Build a set-list
sql_setlist += QString("%1=%2::%3")
.arg(attnam)
.arg(newvalue) // current data value - null adjusted
.arg(parent_class->attAttValue(attnam, QString("mtat_type")));
// build an attribute list
sql_att_list += QString("%1")
.arg(attnam);
// build a values list
sql_val_list += QString("'%1'::%2")
.arg(attval) // current data value
.arg(parent_class->attAttValue(attnam, QString("mtat_type")));
}
//it_mt.current()->print();
++it_mt;
}
// --------------------------------------------------------------------------------
// Build a query string, the form of which depends on the current state
// of the tuple. The tuple is then stored, and recovered to update the values
// of all attributes.
// --------------------------------------------------------------------------------
QString query;
QString new_serial_pkv; // new primary key where exists specific SQL insert function
const QString & cn = parent_class->relationName();
const QString & pk = parent_class->serialKey();
QString pkv = this->attributeValue(pk);
int rows;
int have_used_list = 0;
// ------------------------------------------------------------------
// If there is a currently a serial key, then the tuple must already
// exist in persistent storage, otherwise try to insert it.
// ------------------------------------------------------------------
if ( pkv.length() < 1 )
{
// initialise to defaults
// First try for specific insert function - inserts via views cannot
// be located via oid's. Hence the first method to try is to look
// for an "insert function".
query = QString( "SELECT %1_insert_row() as new_sequence" ).arg( cn );
rows = sql_exec(query);
if ( rows == 1 )
{
pkv = QString(sql_value(0, "new_sequence" ) );
} else { // else use generic insert method
if (sql_setlist.length()) // something specific to insert
{
query = QString("INSERT INTO %1(%2) VALUES(%3)")
.arg(cn)
.arg(sql_att_list)
.arg(sql_val_list);
query.replace( QRegExp(x_holder), "%" );
rows = sql_exec(query);
if ( rows != 1 )
return(FALSE);
have_used_list = 1;
} else {
query = QString( "INSERT INTO %1 default values" ).arg( cn );
rows = sql_exec(query);
if ( rows != 1 )
return(FALSE);
}
// recover pkv
query = QString("SELECT %1 FROM %2 where oid = %3")
.arg(pk)
.arg(cn)
.arg(sql_getlastoid());
rows = sql_exec(query);
if ( rows != 1 )
{
return(FALSE);
}
pkv = QString(sql_value(0, pk ) );
}
// Now recover the new record
query = QString("SELECT * FROM %1 where %2 = %3")
.arg(cn)
.arg(pk)
.arg(pkv);
if (sql_exec(query) == 1)
{ // successfully recovered, so apply to tuple
QDictIterator<QmvAttribute> it_attribute(*parent_class->attributeMetadata());
while (it_attribute.current())
{
QmvTupleAttribute * mta = tupleAttribute(it_attribute.currentKey());
const QString & ta = QString(sql_value(0, it_attribute.currentKey().latin1()));
mta->load(ta);
++it_attribute;
}
} else
return(FALSE);
}
// ------------------------------------------------------------
// By now should have persistent tuple.
// - update it if the values have not been used in the INSERT.
// ------------------------------------------------------------
if ( pkv.length() > 0 )
{
if (sql_setlist.length() && !have_used_list ) // something to change
{
query = QString("UPDATE %1 SET %2 WHERE %3 = '%4'::%5")
.arg(cn)
.arg(sql_setlist)
.arg(pk)
.arg(pkv)
.arg(parent_class->attAttValue(pk, QString("mtat_type")));
query.replace( QRegExp(x_holder), "%" );
rows = sql_exec(query);
qDebug("QmvSqlTuple::save():rows = <%d>", rows);
// updating views returns 0 even if underlying update returns 1
if ( rows < 0 )
{
return(FALSE);
}
}
// ------------------------------------------------------------------
// No serial key, so irrecoverble
// ------------------------------------------------------------------
} else
return(FALSE);
// ------------------------------------------------------------------
// Reload tuple
// ------------------------------------------------------------------
if ( reload() )
{
it_mt.toFirst();
while (it_mt.current())
{
it_mt.current()->setSaved();
++it_mt;
}
emit tupleChanged( this );
return(TRUE);
} else
return(FALSE);
}
