bool getFilteredData( string rows,
string table,
ocString filterTemplate,
string defaultSort = "",
string groupBy = "",
string countTable = "" ) // for cases where grouping limits the real count.
{
string sql = "select count(*) from ";
sql += countTable.length()?countTable:table;
sql += whereClause( filterTemplate );
webIO << "<!--" << sql << "-->";
opened = rs.open(sql);
if( opened )
{
recCount = atol(rs.getField(0).format().c_str());
maxPageFixup();
rs.close();
}
sql = "select ";
sql += rows;
sql += " from ";
sql += table;
sql += whereClause( filterTemplate );
if( groupBy.length() )
{
sql += " group by ";
sql += groupBy;
}
if( sort.length() && rows.find(sort) != string::npos)
{
sql += " order by ";
sql += sort;
sql += " ";
sql += direction;
}
else if ( defaultSort.length() )
{
sql += " order by ";
sql += defaultSort;
}
sql += limitClause();
webIO << "<!--" << sql << "-->";
opened = rs.open(sql);
if( opened )
{
dsetCount = rs.getRecordCount();
}
return opened;
}
void SimpleDBStorage::save(QString table)
{
if (!setting->isChanged())
return;
MSqlBindings bindings;
QString querystr = QString("SELECT * FROM " + table + " WHERE "
+ whereClause(bindings) + ";");
MSqlQuery query(MSqlQuery::InitCon());
query.prepare(querystr);
query.bindValues(bindings);
if (!query.exec())
{
MythContext::DBError("SimpleDBStorage::save() query", query);
return;
}
if (query.isActive() && query.next())
{
// Row already exists
// Don"t change this QString. See the CVS logs rev 1.91.
MSqlBindings bindings;
querystr = QString("UPDATE " + table + " SET " + setClause(bindings) +
" WHERE " + whereClause(bindings) + ";");
query.prepare(querystr);
query.bindValues(bindings);
if (!query.exec())
MythContext::DBError("SimpleDBStorage::save() update", query);
}
else
{
// Row does not exist yet
MSqlBindings bindings;
querystr = QString("INSERT INTO " + table + " SET "
+ setClause(bindings) + ";");
query.prepare(querystr);
query.bindValues(bindings);
if (!query.exec())
MythContext::DBError("SimpleDBStorage::save() insert", query);
}
}
void SimpleDBStorage::load(void)
{
MSqlQuery query(MSqlQuery::InitCon());
MSqlBindings bindings;
query.prepare(
"SELECT " + column + " "
"FROM " + table + " " +
"WHERE " + whereClause(bindings));
query.bindValues(bindings);
if (!query.exec() || !query.isActive())
{
MythContext::DBError("SimpleDBStorage::load()", query);
}
else if (query.next())
{
QString result = query.value(0).toString();
if (!result.isNull())
{
result = QString::fromUtf8(query.value(0).toString());
setting->setValue(result);
setting->setUnchanged();
}
}
}
OGRErr AOLayer::SetAttributeFilter( const char* pszQuery )
{
if( pszQuery == NULL )
{
CComBSTR whereClause(_T(""));
m_ipQF->put_WhereClause(whereClause);
}
else
{
CComBSTR whereClause(pszQuery);
m_ipQF->put_WhereClause(whereClause);
}
ResetReading();
return OGRERR_NONE;
}
bool IDBIndexBackendImpl::addingKeyAllowed(IDBKey* key)
{
if (!m_unique)
return true;
SQLiteStatement query(sqliteDatabase(), "SELECT id FROM IndexData " + whereClause(key));
bool ok = query.prepare() == SQLResultOk;
ASSERT_UNUSED(ok, ok); // FIXME: Better error handling?
bindWhereClause(query, m_id, key);
bool existingValue = query.step() == SQLResultRow;
return !existingValue;
}
void IDBObjectStoreBackendImpl::remove(PassRefPtr<IDBKey> key, PassRefPtr<IDBCallbacks> callbacks)
{
SQLiteStatement query(sqliteDatabase(), "DELETE FROM ObjectStoreData " + whereClause(key->type()));
bool ok = query.prepare() == SQLResultOk;
ASSERT_UNUSED(ok, ok); // FIXME: Better error handling?
bindWhereClause(query, m_id, key.get());
if (query.step() != SQLResultDone) {
callbacks->onError(IDBDatabaseError::create(IDBDatabaseException::NOT_FOUND_ERR, "Key does not exist in the object store."));
return;
}
callbacks->onSuccess();
}
bool DatabaseController::UpdateEntry(string table, vector<dbDataPair> data, dbDataPair WhereClause, string &output)
{
string querey = ("UPDATE " + table + " SET ");
string whereClause(" WHERE " + WhereClause.first + "= \""+ WhereClause.second +"\"");
for (size_t i = 0; i < data.size(); i++)
{
querey += (data[i].first + "= \""+ data[i].second +"\"");
if (i < data.size() - 1)
{
querey += ",";
}
}
return db->executeSQL(querey + whereClause, output);
}
// *****************************************************************************
// * *
// * 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;
}
void IDBObjectStoreBackendImpl::openCursor(PassRefPtr<IDBKeyRange> range, unsigned short direction, PassRefPtr<IDBCallbacks> callbacks)
{
// FIXME: Fully implement.
RefPtr<IDBKey> key = range->left();
SQLiteStatement query(sqliteDatabase(), "SELECT id, value FROM ObjectStoreData " + whereClause(key->type()));
bool ok = query.prepare() == SQLResultOk;
ASSERT_UNUSED(ok, ok); // FIXME: Better error handling?
bindWhereClause(query, m_id, key.get());
if (query.step() != SQLResultRow) {
callbacks->onSuccess();
return;
}
RefPtr<SerializedScriptValue> value = SerializedScriptValue::createFromWire(query.getColumnText(1));
RefPtr<IDBCursorBackendInterface> cursor = IDBCursorBackendImpl::create(this, range, static_cast<IDBCursor::Direction>(direction), key.release(), value.release());
callbacks->onSuccess(cursor.release());
}
void IDBObjectStoreBackendImpl::put(PassRefPtr<SerializedScriptValue> prpValue, PassRefPtr<IDBKey> prpKey, bool addOnly, PassRefPtr<IDBCallbacks> callbacks)
{
RefPtr<SerializedScriptValue> value = prpValue;
RefPtr<IDBKey> key = prpKey;
if (!m_keyPath.isNull()) {
if (key) {
callbacks->onError(IDBDatabaseError::create(IDBDatabaseException::UNKNOWN_ERR, "A key was supplied for an objectStore that has a keyPath."));
return;
}
Vector<RefPtr<SerializedScriptValue> > values;
values.append(value);
Vector<RefPtr<IDBKey> > idbKeys;
IDBKeyPathBackendImpl::createIDBKeysFromSerializedValuesAndKeyPath(values, m_keyPath, idbKeys);
if (idbKeys.isEmpty()) {
callbacks->onError(IDBDatabaseError::create(IDBDatabaseException::UNKNOWN_ERR, "An invalid keyPath was supplied for an objectStore."));
return;
}
key = idbKeys[0];
}
if (!key) {
callbacks->onError(IDBDatabaseError::create(IDBDatabaseException::DATA_ERR, "No key supplied."));
return;
}
SQLiteStatement getQuery(sqliteDatabase(), "SELECT id FROM ObjectStoreData " + whereClause(key->type()));
bool ok = getQuery.prepare() == SQLResultOk;
ASSERT_UNUSED(ok, ok); // FIXME: Better error handling?
bindWhereClause(getQuery, m_id, key.get());
bool existingValue = getQuery.step() == SQLResultRow;
if (addOnly && existingValue) {
callbacks->onError(IDBDatabaseError::create(IDBDatabaseException::CONSTRAINT_ERR, "Key already exists in the object store."));
return;
}
String sql = existingValue ? "UPDATE ObjectStoreData SET keyString = ?, keyDate = ?, keyNumber = ?, value = ? WHERE id = ?"
: "INSERT INTO ObjectStoreData (keyString, keyDate, keyNumber, value, objectStoreId) VALUES (?, ?, ?, ?, ?)";
SQLiteStatement putQuery(sqliteDatabase(), sql);
ok = putQuery.prepare() == SQLResultOk;
ASSERT_UNUSED(ok, ok); // FIXME: Better error handling?
switch (key->type()) {
case IDBKey::StringType:
putQuery.bindText(1, key->string());
putQuery.bindNull(2);
putQuery.bindNull(3);
break;
// FIXME: Implement date.
case IDBKey::NumberType:
putQuery.bindNull(1);
putQuery.bindNull(2);
putQuery.bindInt(3, key->number());
break;
case IDBKey::NullType:
putQuery.bindNull(1);
putQuery.bindNull(2);
putQuery.bindNull(3);
break;
default:
ASSERT_NOT_REACHED();
}
putQuery.bindText(4, value->toWireString());
if (existingValue)
putQuery.bindInt(5, getQuery.getColumnInt(0));
else
putQuery.bindInt64(5, m_id);
ok = putQuery.step() == SQLResultDone;
ASSERT_UNUSED(ok, ok); // FIXME: Better error handling?
callbacks->onSuccess(key.get());
}
void IDBObjectStoreBackendImpl::get(PassRefPtr<IDBKey> key, PassRefPtr<IDBCallbacks> callbacks)
{
SQLiteStatement query(sqliteDatabase(), "SELECT keyString, keyDate, keyNumber, value FROM ObjectStoreData " + whereClause(key->type()));
bool ok = query.prepare() == SQLResultOk;
ASSERT_UNUSED(ok, ok); // FIXME: Better error handling?
bindWhereClause(query, m_id, key.get());
if (query.step() != SQLResultRow) {
callbacks->onError(IDBDatabaseError::create(IDBDatabaseException::NOT_FOUND_ERR, "Key does not exist in the object store."));
return;
}
ASSERT((key->type() == IDBKey::StringType) != query.isColumnNull(0));
// FIXME: Implement date.
ASSERT((key->type() == IDBKey::NumberType) != query.isColumnNull(2));
callbacks->onSuccess(SerializedScriptValue::createFromWire(query.getColumnText(3)));
ASSERT(query.step() != SQLResultRow);
}
void SqliteTableModel::updateFilter(int column, const QString& value, bool applyQuery)
{
// Check for any special comparison operators at the beginning of the value string. If there are none default to LIKE.
QString op = "LIKE";
QString val, val2;
QString escape;
bool numeric = false, ok = false;
// range/BETWEEN operator
if (value.contains("~")) {
int sepIdx = value.indexOf('~');
val = value.mid(0, sepIdx);
val2 = value.mid(sepIdx+1);
val.toFloat(&ok);
if (ok) {
val2.toFloat(&ok);
ok = ok && (val.toFloat() < val2.toFloat());
}
}
if (ok) {
op = "BETWEEN";
numeric = true;
} else {
val.clear();
val2.clear();
if(value.left(2) == ">=" || value.left(2) == "<=" || value.left(2) == "<>")
{
// Check if we're filtering for '<> NULL'. In this case we need a special comparison operator.
if(value.left(2) == "<>" && value.mid(2) == "NULL")
{
// We are filtering for '<>NULL'. Override the comparison operator to search for NULL values in this column. Also treat search value (NULL) as number,
// in order to avoid putting quotes around it.
op = "IS NOT";
numeric = true;
val = "NULL";
} else if(value.left(2) == "<>" && value.mid(2) == "''") {
// We are filtering for "<>''", i.e. for everything which is not an empty string
op = "<>";
numeric = true;
val = "''";
} else {
value.mid(2).toFloat(&numeric);
op = value.left(2);
val = value.mid(2);
}
} else if(value.left(1) == ">" || value.left(1) == "<") {
value.mid(1).toFloat(&numeric);
op = value.left(1);
val = value.mid(1);
} else if(value.left(1) == "=") {
val = value.mid(1);
// Check if value to compare with is 'NULL'
if(val != "NULL")
{
// It's not, so just compare normally to the value, whatever it is.
op = "=";
} else {
// It is NULL. Override the comparison operator to search for NULL values in this column. Also treat search value (NULL) as number,
// in order to avoid putting quotes around it.
op = "IS";
numeric = true;
}
} else {
// Keep the default LIKE operator
// Set the escape character if one has been specified in the settings dialog
QString escape_character = Settings::getValue("databrowser", "filter_escape").toString();
if(escape_character == "'") escape_character = "''";
if(escape_character.length())
escape = QString("ESCAPE '%1'").arg(escape_character);
// Add % wildcards at the start and at the beginning of the filter query, but only if there weren't set any
// wildcards manually. The idea is to assume that a user who's just typing characters expects the wildcards to
// be added but a user who adds them herself knows what she's doing and doesn't want us to mess up her query.
if(!value.contains("%"))
{
val = value;
val.prepend('%');
val.append('%');
}
}
}
if(val.isEmpty())
val = value;
// If the value was set to an empty string remove any filter for this column. Otherwise insert a new filter rule or replace the old one if there is already one
if(val == "" || val == "%" || val == "%%")
m_mWhere.remove(column);
else {
// Quote and escape value, but only if it's not numeric and not the empty string sequence
if(!numeric && val != "''")
val = QString("'%1'").arg(val.replace("'", "''"));
QString whereClause(op + " " + QString(encode(val.toUtf8())));
if (!val2.isEmpty())
whereClause += " AND " + QString(encode(val2.toUtf8()));
whereClause += " " + escape;
m_mWhere.insert(column, whereClause);
}
// Build the new query
if (applyQuery)
buildQuery();
}
void IDBObjectStoreBackendImpl::deleteInternal(ScriptExecutionContext*, PassRefPtr<IDBObjectStoreBackendImpl> objectStore, PassRefPtr<IDBKey> key, PassRefPtr<IDBCallbacks> callbacks)
{
SQLiteStatement idQuery(objectStore->sqliteDatabase(), "SELECT id FROM ObjectStoreData " + whereClause(key.get()));
bool ok = idQuery.prepare() == SQLResultOk;
ASSERT_UNUSED(ok, ok); // FIXME: Better error handling?
bindWhereClause(idQuery, objectStore->id(), key.get());
if (idQuery.step() != SQLResultRow) {
callbacks->onError(IDBDatabaseError::create(IDBDatabaseException::NOT_FOUND_ERR, "Key does not exist in the object store."));
return;
}
int64_t id = idQuery.getColumnInt64(0);
SQLiteStatement osQuery(objectStore->sqliteDatabase(), "DELETE FROM ObjectStoreData WHERE id = ?");
ok = osQuery.prepare() == SQLResultOk;
ASSERT_UNUSED(ok, ok); // FIXME: Better error handling?
osQuery.bindInt64(1, id);
ok = osQuery.step() == SQLResultDone;
ASSERT_UNUSED(ok, ok);
SQLiteStatement indexQuery(objectStore->sqliteDatabase(), "DELETE FROM IndexData WHERE objectStoreDataId = ?");
ok = indexQuery.prepare() == SQLResultOk;
ASSERT_UNUSED(ok, ok); // FIXME: Better error handling?
indexQuery.bindInt64(1, id);
ok = indexQuery.step() == SQLResultDone;
ASSERT_UNUSED(ok, ok);
callbacks->onSuccess();
}
void IDBObjectStoreBackendImpl::putInternal(ScriptExecutionContext*, PassRefPtr<IDBObjectStoreBackendImpl> objectStore, PassRefPtr<SerializedScriptValue> prpValue, PassRefPtr<IDBKey> prpKey, bool addOnly, PassRefPtr<IDBCallbacks> callbacks, PassRefPtr<IDBTransactionBackendInterface> transaction)
{
RefPtr<SerializedScriptValue> value = prpValue;
RefPtr<IDBKey> key = prpKey;
// FIXME: Support auto-increment.
if (!objectStore->m_keyPath.isNull()) {
if (key) {
callbacks->onError(IDBDatabaseError::create(IDBDatabaseException::UNKNOWN_ERR, "A key was supplied for an objectStore that has a keyPath."));
return;
}
key = fetchKeyFromKeyPath(value.get(), objectStore->m_keyPath);
if (!key) {
callbacks->onError(IDBDatabaseError::create(IDBDatabaseException::UNKNOWN_ERR, "The key could not be fetched from the keyPath."));
return;
}
} else if (!key) {
callbacks->onError(IDBDatabaseError::create(IDBDatabaseException::DATA_ERR, "No key supplied."));
return;
}
if (key->type() == IDBKey::NullType) {
callbacks->onError(IDBDatabaseError::create(IDBDatabaseException::DATA_ERR, "NULL key is not allowed."));
return;
}
Vector<RefPtr<IDBKey> > indexKeys;
for (IndexMap::iterator it = objectStore->m_indexes.begin(); it != objectStore->m_indexes.end(); ++it) {
RefPtr<IDBKey> key = fetchKeyFromKeyPath(value.get(), it->second->keyPath());
if (!key) {
callbacks->onError(IDBDatabaseError::create(IDBDatabaseException::UNKNOWN_ERR, "The key could not be fetched from an index's keyPath."));
return;
}
if (key->type() == IDBKey::NullType) {
callbacks->onError(IDBDatabaseError::create(IDBDatabaseException::DATA_ERR, "One of the derived (from a keyPath) keys for an index is NULL."));
return;
}
if (!it->second->addingKeyAllowed(key.get())) {
callbacks->onError(IDBDatabaseError::create(IDBDatabaseException::UNKNOWN_ERR, "One of the derived (from a keyPath) keys for an index does not satisfy its uniqueness requirements."));
return;
}
indexKeys.append(key.release());
}
SQLiteStatement getQuery(objectStore->sqliteDatabase(), "SELECT id FROM ObjectStoreData " + whereClause(key.get()));
bool ok = getQuery.prepare() == SQLResultOk;
ASSERT_UNUSED(ok, ok); // FIXME: Better error handling?
bindWhereClause(getQuery, objectStore->id(), key.get());
bool isExistingValue = getQuery.step() == SQLResultRow;
if (addOnly && isExistingValue) {
callbacks->onError(IDBDatabaseError::create(IDBDatabaseException::CONSTRAINT_ERR, "Key already exists in the object store."));
return;
}
// Before this point, don't do any mutation. After this point, rollback the transaction in case of error.
int64_t dataRowId = isExistingValue ? getQuery.getColumnInt(0) : InvalidId;
if (!putObjectStoreData(objectStore->sqliteDatabase(), key.get(), value.get(), objectStore->id(), dataRowId)) {
// FIXME: The Indexed Database specification does not have an error code dedicated to I/O errors.
callbacks->onError(IDBDatabaseError::create(IDBDatabaseException::UNKNOWN_ERR, "Error writing data to stable storage."));
transaction->abort();
return;
}
if (!deleteIndexData(objectStore->sqliteDatabase(), dataRowId)) {
// FIXME: The Indexed Database specification does not have an error code dedicated to I/O errors.
callbacks->onError(IDBDatabaseError::create(IDBDatabaseException::UNKNOWN_ERR, "Error writing data to stable storage."));
transaction->abort();
return;
}
int i = 0;
for (IndexMap::iterator it = objectStore->m_indexes.begin(); it != objectStore->m_indexes.end(); ++it, ++i) {
if (!putIndexData(objectStore->sqliteDatabase(), indexKeys[i].get(), it->second->id(), dataRowId)) {
// FIXME: The Indexed Database specification does not have an error code dedicated to I/O errors.
callbacks->onError(IDBDatabaseError::create(IDBDatabaseException::UNKNOWN_ERR, "Error writing data to stable storage."));
transaction->abort();
return;
}
}
callbacks->onSuccess(key.get());
}
