void BaseSqlTableModel::select() {
if (!m_bInitialized) {
return;
}
// We should be able to detect when a select() would be a no-op. The DAO's
// do not currently broadcast signals for when common things happen. In the
// future, we can turn this check on and avoid a lot of needless
// select()'s. rryan 9/2011
// if (!m_bDirty) {
// if (sDebug) {
// qDebug() << this << "Skipping non-dirty select()";
// }
// return;
// }
if (sDebug) {
qDebug() << this << "select()";
}
QTime time;
time.start();
QString columns = m_tableColumnsJoined;
QString orderBy = orderByClause();
QString queryString = QString("SELECT %1 FROM %2 %3")
.arg(columns, m_tableName, orderBy);
if (sDebug) {
qDebug() << this << "select() executing:" << queryString;
}
QSqlQuery query(m_database);
// This causes a memory savings since QSqlCachedResult (what QtSQLite uses)
// won't allocate a giant in-memory table that we won't use at all.
query.setForwardOnly(true);
query.prepare(queryString);
if (!query.exec()) {
LOG_FAILED_QUERY(query);
return;
}
// Remove all the rows from the table. We wait to do this until after the
// table query has succeeded. See Bug #1090888.
// TODO(rryan) we could edit the table in place instead of clearing it?
if (m_rowInfo.size() > 0) {
beginRemoveRows(QModelIndex(), 0, m_rowInfo.size()-1);
m_rowInfo.clear();
m_trackIdToRows.clear();
endRemoveRows();
}
QSqlRecord record = query.record();
int idColumn = record.indexOf(m_idColumn);
QLinkedList<int> tableColumnIndices;
foreach (QString column, m_tableColumns) {
Q_ASSERT(record.indexOf(column) == m_tableColumnIndex[column]);
tableColumnIndices.push_back(record.indexOf(column));
}
/*!
Returns the SQL \c SELECT statement used internally to populate
the model. The statement includes the filter and the \c{ORDER BY}
clause.
\sa filter(), orderByClause()
*/
QString QSqlTableModel::selectStatement() const
{
Q_D(const QSqlTableModel);
QString query;
if (d->tableName.isEmpty()) {
d->error = QSqlError(QLatin1String("No table name given"), QString(),
QSqlError::StatementError);
return query;
}
if (d->rec.isEmpty()) {
d->error = QSqlError(QLatin1String("Unable to find table ") + d->tableName, QString(),
QSqlError::StatementError);
return query;
}
query = d->db.driver()->sqlStatement(QSqlDriver::SelectStatement,
d->tableName,
d->rec,
false);
if (query.isEmpty()) {
d->error = QSqlError(QLatin1String("Unable to select fields from table ") + d->tableName,
QString(), QSqlError::StatementError);
return query;
}
if (!d->filter.isEmpty())
query.append(QLatin1String(" WHERE ")).append(d->filter);
QString orderBy(orderByClause());
if (!orderBy.isEmpty())
query.append(QLatin1Char(' ')).append(orderBy);
return query;
}
/*!
Returns the SQL \c SELECT statement used internally to populate
the model. The statement includes the filter and the \c{ORDER BY}
clause.
\sa filter(), orderByClause()
*/
QString QSqlTableModel::selectStatement() const
{
Q_D(const QSqlTableModel);
if (d->tableName.isEmpty()) {
d->error = QSqlError(QLatin1String("No table name given"), QString(),
QSqlError::StatementError);
return QString();
}
if (d->rec.isEmpty()) {
d->error = QSqlError(QLatin1String("Unable to find table ") + d->tableName, QString(),
QSqlError::StatementError);
return QString();
}
const QString stmt = d->db.driver()->sqlStatement(QSqlDriver::SelectStatement,
d->tableName,
d->rec,
false);
if (stmt.isEmpty()) {
d->error = QSqlError(QLatin1String("Unable to select fields from table ") + d->tableName,
QString(), QSqlError::StatementError);
return stmt;
}
return Sql::concat(Sql::concat(stmt, Sql::where(d->filter)), orderByClause());
}
QString PartsQueryBuilder::buildQuery() const
{
QStringList clauses;
//Keeps the criterium used for the WHERE clause
QStringList criteriumClauses;
clauses.append(_baseSelectClause);
Criterion* criterion;
foreach (criterion, _criterions) {
const QString & whereClause = criterion->clause();
if(!whereClause.isEmpty()){
criteriumClauses.append(whereClause);
const QString & joinClause = criterion->joinClause();
if(!joinClause.isEmpty())
clauses.append(joinClause);
}
}
//Join the criterium as an where clause
if(criteriumClauses.size()>0){
clauses.append(QLatin1String("WHERE"));
clauses.append(criteriumClauses.join(" AND "));
}
const QString & orderBy = orderByClause();
if (!orderBy.isEmpty())
clauses.append(orderBy);
const QString statement = clauses.join(QLatin1String(" "));
qDebug()<<"Select statement is "<<statement;
return statement;
}
QString SqlRelationalTableModel::selectStatement() const
{
QString query;
if (tableName().isEmpty())
return query;
QString tList;
QString jList;
QString fList;
QString where;
QSqlRecord rec = record();
QStringList tables;
// Count how many times each field name occurs in the record
QHash<QString, int> fieldNames;
QStringList fieldList;
for (int i = 0; i < rec.count(); ++i) {
SqlRelation relation = this->relation(i);
QString name;
if (relation.isValid())
{
// Count the display column name, not the original foreign key
name = relation.displayColumn();
if (database().driver()->isIdentifierEscaped(name, QSqlDriver::FieldName))
name = database().driver()->stripDelimiters(name, QSqlDriver::FieldName);
QSqlRecord rec = database().record(relation.tableName());
for (int i = 0; i < rec.count(); ++i) {
if (name.compare(rec.fieldName(i), Qt::CaseInsensitive) == 0) {
name = rec.fieldName(i);
break;
}
}
}
else
name = rec.fieldName(i);
fieldNames.insert(name, fieldNames.value(name, 0) + 1);
fieldList.append(name);
}
for (int i = 0; i < rec.count(); ++i) {
SqlRelation relation = this->relation(i);
if (relation.isValid()) {
QString relTableAlias = QString::fromLatin1("relTblAl_%1").arg(i);
if (!fList.isEmpty())
fList.append(QLatin1String(", "));
fList.append(relationField(relTableAlias,relation.displayColumn()));
// If there are duplicate field names they must be aliased
if (fieldNames.value(fieldList[i]) > 1) {
QString relTableName = relation.tableName().section(QChar::fromLatin1('.'), -1, -1);
if (database().driver()->isIdentifierEscaped(relTableName, QSqlDriver::TableName))
relTableName = database().driver()->stripDelimiters(relTableName, QSqlDriver::TableName);
QString displayColumn = relation.displayColumn();
if (database().driver()->isIdentifierEscaped(displayColumn, QSqlDriver::FieldName))
displayColumn = database().driver()->stripDelimiters(displayColumn, QSqlDriver::FieldName);
fList.append(QString::fromLatin1(" AS %1_%2_%3").arg(relTableName).arg(displayColumn).arg(fieldNames.value(fieldList[i])));
fieldNames.insert(fieldList[i], fieldNames.value(fieldList[i])-1);
}
// this needs fixing!! the below if is borken.
//tables.append(relation.tableName().append(QLatin1Char(' ')).append(relTableAlias));
//if(!where.isEmpty())
//where.append(QLatin1String(" AND "));
switch(relation.getJoinKind()){
case SqlRelation::RIGHT:
jList.append(QLatin1String(" RIGHT JOIN "));
break;
case SqlRelation::LEFT:
jList.append(QLatin1String(" LEFT JOIN "));
break;
case SqlRelation::OUTER:
jList.append(QLatin1String(" FULL OUTER JOIN "));
break;
case SqlRelation::INNER:
jList.append(QLatin1String(" INNER JOIN "));
break;
default:
jList.append(QLatin1String(" JOIN "));
break;
}
jList.append(relation.tableName().append(QLatin1Char(' ')).append(relTableAlias));
jList.append(QLatin1String(" ON "));
jList.append(relationField(tableName(), database().driver()->escapeIdentifier(rec.fieldName(i), QSqlDriver::FieldName)));
jList.append(QLatin1String(" = "));
jList.append(relationField(relTableAlias, relation.indexColumn()));
} else {
if (!fList.isEmpty())
fList.append(QLatin1String(", "));
fList.append(relationField(tableName(), database().driver()->escapeIdentifier(rec.fieldName(i), QSqlDriver::FieldName)));
}
}
for(int i = 0; i < rec.count(); ++i)
{
SqlRelation relation = this->relation(i);
if(relation.isValid()){
fList.append(QLatin1String(", "));
fList.append(relationField(QString::fromLatin1("relTblAl_%1").arg(i),relation.indexColumn()));
fList.append(QString::fromLatin1(" AS %1_%2").arg(relation.tableName().section(QChar::fromLatin1('.'), -1, -1)).arg(relation.indexColumn()));
}
}
if(!additionalField.isEmpty())
fList.append(additionalField+" ");
if (!tables.isEmpty())
tList.append(tables.join(QLatin1String(", ")));
if (fList.isEmpty())
return query;
if(!tList.isEmpty())
tList.prepend(QLatin1String(", "));
tList.prepend(tableName());
query.append(QLatin1String("SELECT "));
query.append(fList).append(QLatin1String(" FROM ")).append(tList);
query.append(jList);
query = qAppendWhereClause(query, where, filter());
query.append(groupByClause);
QString orderBy = orderByClause();
if (!orderBy.isEmpty())
query.append(QLatin1Char(' ')).append(orderBy);
return query;
}
/*!
\reimp
*/
QString QSqlRelationalTableModel::selectStatement() const
{
Q_D(const QSqlRelationalTableModel);
QString query;
if (tableName().isEmpty())
return query;
if (d->relations.isEmpty())
return QSqlTableModel::selectStatement();
QString tList;
QString fList;
QString where;
QSqlRecord rec = d->baseRec;
QStringList tables;
const QRelation nullRelation;
// Count how many times each field name occurs in the record
QHash<QString, int> fieldNames;
QStringList fieldList;
for (int i = 0; i < rec.count(); ++i) {
QSqlRelation relation = d->relations.value(i, nullRelation).rel;
QString name;
if (relation.isValid())
{
// Count the display column name, not the original foreign key
name = relation.displayColumn();
if (d->db.driver()->isIdentifierEscaped(name, QSqlDriver::FieldName))
name = d->db.driver()->stripDelimiters(name, QSqlDriver::FieldName);
QSqlRecord rec = database().record(relation.tableName());
for (int i = 0; i < rec.count(); ++i) {
if (name.compare(rec.fieldName(i), Qt::CaseInsensitive) == 0) {
name = rec.fieldName(i);
break;
}
}
}
else
name = rec.fieldName(i);
fieldNames.insert(name, fieldNames.value(name, 0) + 1);
fieldList.append(name);
}
for (int i = 0; i < rec.count(); ++i) {
QSqlRelation relation = d->relations.value(i, nullRelation).rel;
if (relation.isValid()) {
QString relTableAlias = QString::fromLatin1("relTblAl_%1").arg(i);
if (!fList.isEmpty())
fList.append(QLatin1String(", "));
fList.append(d->relationField(relTableAlias,relation.displayColumn()));
// If there are duplicate field names they must be aliased
if (fieldNames.value(fieldList[i]) > 1) {
QString relTableName = relation.tableName().section(QChar::fromLatin1('.'), -1, -1);
if (d->db.driver()->isIdentifierEscaped(relTableName, QSqlDriver::TableName))
relTableName = d->db.driver()->stripDelimiters(relTableName, QSqlDriver::TableName);
QString displayColumn = relation.displayColumn();
if (d->db.driver()->isIdentifierEscaped(displayColumn, QSqlDriver::FieldName))
displayColumn = d->db.driver()->stripDelimiters(displayColumn, QSqlDriver::FieldName);
fList.append(QString::fromLatin1(" AS %1_%2_%3").arg(relTableName).arg(displayColumn).arg(fieldNames.value(fieldList[i])));
fieldNames.insert(fieldList[i], fieldNames.value(fieldList[i])-1);
}
// this needs fixing!! the below if is borken.
tables.append(relation.tableName().append(QLatin1Char(' ')).append(relTableAlias));
if(!where.isEmpty())
where.append(QLatin1String(" AND "));
where.append(d->relationField(tableName(), d->db.driver()->escapeIdentifier(rec.fieldName(i), QSqlDriver::FieldName)));
where.append(QLatin1String(" = "));
where.append(d->relationField(relTableAlias, relation.indexColumn()));
} else {
if (!fList.isEmpty())
fList.append(QLatin1String(", "));
fList.append(d->relationField(tableName(), d->db.driver()->escapeIdentifier(rec.fieldName(i), QSqlDriver::FieldName)));
}
}
if (!tables.isEmpty())
tList.append(tables.join(QLatin1String(", ")));
if (fList.isEmpty())
return query;
if(!tList.isEmpty())
tList.prepend(QLatin1String(", "));
tList.prepend(tableName());
query.append(QLatin1String("SELECT "));
query.append(fList).append(QLatin1String(" FROM ")).append(tList);
qAppendWhereClause(query, where, filter());
QString orderBy = orderByClause();
if (!orderBy.isEmpty())
query.append(QLatin1Char(' ')).append(orderBy);
return query;
}
/*!
\reimp
*/
QString QSqlRelationalTableModel::selectStatement() const
{
Q_D(const QSqlRelationalTableModel);
if (tableName().isEmpty())
return QString();
if (d->relations.isEmpty())
return QSqlTableModel::selectStatement();
// Count how many times each field name occurs in the record
QHash<QString, int> fieldNames;
QStringList fieldList;
for (int i = 0; i < d->baseRec.count(); ++i) {
QSqlRelation relation = d->relations.value(i).rel;
QString name;
if (relation.isValid()) {
// Count the display column name, not the original foreign key
name = relation.displayColumn();
if (d->db.driver()->isIdentifierEscaped(name, QSqlDriver::FieldName))
name = d->db.driver()->stripDelimiters(name, QSqlDriver::FieldName);
const QSqlRecord rec = database().record(relation.tableName());
for (int i = 0; i < rec.count(); ++i) {
if (name.compare(rec.fieldName(i), Qt::CaseInsensitive) == 0) {
name = rec.fieldName(i);
break;
}
}
}
else {
name = d->baseRec.fieldName(i);
}
fieldNames[name] = fieldNames.value(name, 0) + 1;
fieldList.append(name);
}
QString fList;
QString conditions;
QString from = Sql::from(tableName());
for (int i = 0; i < d->baseRec.count(); ++i) {
QSqlRelation relation = d->relations.value(i).rel;
const QString tableField = d->fullyQualifiedFieldName(tableName(), d->db.driver()->escapeIdentifier(d->baseRec.fieldName(i), QSqlDriver::FieldName));
if (relation.isValid()) {
const QString relTableAlias = Sql::relTablePrefix(i);
QString displayTableField = d->fullyQualifiedFieldName(relTableAlias, relation.displayColumn());
// Duplicate field names must be aliased
if (fieldNames.value(fieldList[i]) > 1) {
QString relTableName = relation.tableName().section(QChar::fromLatin1('.'), -1, -1);
if (d->db.driver()->isIdentifierEscaped(relTableName, QSqlDriver::TableName))
relTableName = d->db.driver()->stripDelimiters(relTableName, QSqlDriver::TableName);
QString displayColumn = relation.displayColumn();
if (d->db.driver()->isIdentifierEscaped(displayColumn, QSqlDriver::FieldName))
displayColumn = d->db.driver()->stripDelimiters(displayColumn, QSqlDriver::FieldName);
const QString alias = QString::fromLatin1("%1_%2_%3").arg(relTableName).arg(displayColumn).arg(fieldNames.value(fieldList[i]));
displayTableField = Sql::as(displayTableField, alias);
--fieldNames[fieldList[i]];
}
fList = Sql::comma(fList, displayTableField);
// Join related table
const QString tblexpr = Sql::concat(relation.tableName(), relTableAlias);
const QString relTableField = d->fullyQualifiedFieldName(relTableAlias, relation.indexColumn());
const QString cond = Sql::eq(tableField, relTableField);
if (d->joinMode == QSqlRelationalTableModel::InnerJoin) {
// FIXME: InnerJoin code is known to be broken.
// Use LeftJoin mode if you want correct behavior.
from = Sql::comma(from, tblexpr);
conditions = Sql::et(conditions, cond);
} else {
from = Sql::concat(from, Sql::leftJoin(tblexpr));
from = Sql::concat(from, Sql::on(cond));
}
} else {
fList = Sql::comma(fList, tableField);
}
}
if (fList.isEmpty())
return QString();
const QString stmt = Sql::concat(Sql::select(fList), from);
const QString where = Sql::where(Sql::et(Sql::paren(conditions), Sql::paren(filter())));
return Sql::concat(Sql::concat(stmt, where), orderByClause());
}
// *****************************************************************************
// * *
// * Function: transferObjectPrivs *
// * *
// * Transfers object privs from current owner to new owner. *
// * *
// *****************************************************************************
// * *
// * Parameters: *
// * *
// * <systemCatalogName> const char * In *
// * is the location of the system catalog. *
// * *
// * <catalogName> const char * In *
// * is the catalog of the schema whose objects are getting a new owner. *
// * *
// * <schemaName> const char * In *
// * is the schema whose objects are getting a new owner. *
// * *
// * <newOwnerID> const int32_t In *
// * is the ID of the new owner for the objects. *
// * *
// * <newOwnerName const char * In *
// * is the database username or role name of the new owner for the objects.*
// * *
// *****************************************************************************
// * *
// * Returns: bool *
// * *
// * true: Privileges for object(s) transferred to new owner. *
// * false: Privileges for object(s) NOT transferred to new owner. *
// * *
// *****************************************************************************
static bool transferObjectPrivs(
const char * systemCatalogName,
const char * catalogName,
const char * schemaName,
const int32_t newOwnerID,
const char * newOwnerName)
{
PrivStatus privStatus = STATUS_GOOD;
// Initiate the privilege manager interface class
NAString privMgrMDLoc;
CONCAT_CATSCH(privMgrMDLoc,systemCatalogName,SEABASE_PRIVMGR_SCHEMA);
PrivMgrCommands privInterface(std::string(privMgrMDLoc.data()),CmpCommon::diags());
std::vector<UIDAndOwner> objectRows;
std::string whereClause(" WHERE catalogName = ");
whereClause += catalogName;
whereClause += " AND schema_name = ";
whereClause += schemaName;
std::string orderByClause(" ORDER BY OBJECT_OWNER");
std::string metadataLocation(systemCatalogName);
metadataLocation += ".";
metadataLocation += SEABASE_MD_SCHEMA;
PrivMgrObjects objects(metadataLocation,CmpCommon::diags());
privStatus = objects.fetchUIDandOwner(whereClause,orderByClause,objectRows);
if (privStatus != STATUS_GOOD || objectRows.size() == 0)
return false;
int32_t lastOwner = objectRows[0].ownerID;
std::vector<int64_t> objectUIDs;
for (size_t i = 0; i < objectRows.size(); i++)
{
if (objectRows[i].ownerID != lastOwner)
{
privStatus = privInterface.givePrivForObjects(lastOwner,
newOwnerID,
newOwnerName,
objectUIDs);
objectUIDs.clear();
}
objectUIDs.push_back(objectRows[i].UID);
lastOwner = objectRows[i].ownerID;
}
privStatus = privInterface.givePrivForObjects(lastOwner,
newOwnerID,
newOwnerName,
objectUIDs);
return true;
}
/*!
\reimp
*/
QString QSqlRelationalTableModel::selectStatement() const
{
Q_D(const QSqlRelationalTableModel);
QString query;
if (tableName().isEmpty())
return query;
if (d->relations.isEmpty())
return QSqlTableModel::selectStatement();
QString tList;
QString fList;
QString where;
QSqlRecord rec = d->baseRec;
QStringList tables;
const QRelation nullRelation;
// Count how many times each field name occurs in the record
QHash<QString, int> fieldNames;
for (int i = 0; i < rec.count(); ++i) {
QSqlRelation relation = d->relations.value(i, nullRelation).rel;
QString name;
if (relation.isValid())
// Count the display column name, not the original foreign key
name = relation.displayColumn();
else
name = rec.fieldName(i);
fieldNames.insert(name, fieldNames.value(name, 0) + 1);
}
for (int i = 0; i < rec.count(); ++i) {
QSqlRelation relation = d->relations.value(i, nullRelation).rel;
if (relation.isValid()) {
QString relTableAlias = QString::fromLatin1("relTblAl_%1").arg(i);
fList.append(d->escapedRelationField(relTableAlias, relation.displayColumn()));
// If there are duplicate field names they must be aliased
if (fieldNames.value(relation.displayColumn()) > 1)
fList.append(QString::fromLatin1(" AS %1_%2").arg(relation.tableName()).arg(relation.displayColumn()));
fList.append(QLatin1Char(','));
if (!tables.contains(relation.tableName()))
tables.append(d->db.driver()->escapeIdentifier(relation.tableName(),
QSqlDriver::TableName).append(QLatin1String(" ")).append(
d->db.driver()->escapeIdentifier(relTableAlias, QSqlDriver::TableName)));
where.append(d->escapedRelationField(tableName(), rec.fieldName(i)));
where.append(QLatin1Char('='));
where.append(d->escapedRelationField(relTableAlias, relation.indexColumn()));
where.append(QLatin1String(" AND "));
} else {
fList.append(d->escapedRelationField(tableName(), rec.fieldName(i)));
fList.append(QLatin1Char(','));
}
}
if (!tables.isEmpty())
tList.append(tables.join(QLatin1String(","))).append(QLatin1String(","));
if (fList.isEmpty())
return query;
tList.prepend(QLatin1Char(',')).prepend(d->db.driver()->escapeIdentifier(tableName(),
QSqlDriver::TableName));
// truncate tailing comma
tList.chop(1);
fList.chop(1);
query.append(QLatin1String("SELECT "));
query.append(fList).append(QLatin1String(" FROM ")).append(tList);
if (!where.isEmpty())
where.chop(5);
qAppendWhereClause(query, where, filter());
QString orderBy = orderByClause();
if (!orderBy.isEmpty())
query.append(QLatin1Char(' ')).append(orderBy);
return query;
}
void ContentTypesModel::paginator(void) {
QString f = _f.isEmpty() ? "1=1" : _f;
setFilter(QString("%1 %2 LIMIT %3, %4 --").arg(f).arg(orderByClause()).arg(
_begin).arg(_limit));
select();
}