void
SqlSubscriberMap::put(const DatabaseConnectionPtr& connection,
const SubscriberRecordKey& key,
const SubscriberRecord& record)
{
QString driver = connection->sqlConnection().driverName();
QSqlQuery query(connection->sqlConnection());
ostringstream queryString;
queryString << "UPDATE " << _table
<< " SET topicName = ?, link = '" << (record.link ? "true" : "false")
<< "', obj = ?, cost = '" << record.cost
<< "', theTopic = ? WHERE topic = ? AND id = ?;";
query.prepare(queryString.str().c_str());
query.bindValue(0, record.topicName.c_str());
query.bindValue(1, _communicator->proxyToString(record.obj).c_str());
query.bindValue(2, _communicator->proxyToString(record.theTopic).c_str());
query.bindValue(3, _communicator->identityToString(key.topic).c_str());
query.bindValue(4, _communicator->identityToString(key.id).c_str());
if(!query.exec())
{
throwDatabaseException(__FILE__, __LINE__, query.lastError());
}
if(query.numRowsAffected() == 0)
{
//
// We do a find since some databases (MySQL) return 0 for number of rows affected
// if row exists but data was not changed from previous values.
//
try
{
find(connection, key);
}
catch(const NotFoundException&)
{
QSqlQuery insertQuery(connection->sqlConnection());
queryString.str("");
queryString << "INSERT INTO " << _table << " VALUES(?, ?, ?, '" << (record.link ? "true" : "false")
<< "', ?, '" << record.cost << "', ?);";
insertQuery.prepare(queryString.str().c_str());
insertQuery.bindValue(0, _communicator->identityToString(key.topic).c_str());
insertQuery.bindValue(1, _communicator->identityToString(key.id).c_str());
insertQuery.bindValue(2, record.topicName.c_str());
insertQuery.bindValue(3, _communicator->proxyToString(record.obj).c_str());
insertQuery.bindValue(4, _communicator->proxyToString(record.theTopic).c_str());
if(!insertQuery.exec())
{
throwDatabaseException(__FILE__, __LINE__, insertQuery.lastError());
}
}
}
_qosMap.put(connection, key, record.theQoS);
}
void
SqlSubscriberMap::getMap(const DatabaseConnectionPtr& connection,
SubscriberMap& smap)
{
QSqlQuery query(connection->sqlConnection());
string queryString = "SELECT * FROM ";
queryString += _table;
queryString += ";";
if(!query.exec(queryString.c_str()))
{
throwDatabaseException(__FILE__, __LINE__, query.lastError());
}
while(query.next())
{
SubscriberRecordKey key;
key.topic = _communicator->stringToIdentity(query.value(0).toString().toUtf8().data());
key.id = _communicator->stringToIdentity(query.value(1).toString().toUtf8().data());
SubscriberRecord record;
record.topicName = query.value(2).toString().toUtf8().data();
record.id = key.id;
record.link = string(query.value(3).toString().toUtf8().data()) == "true" ? true : false;
record.obj = _communicator->stringToProxy(query.value(4).toString().toUtf8().data());
record.cost = query.value(5).toInt();
record.theTopic =
TopicPrx::uncheckedCast(_communicator->stringToProxy(query.value(6).toString().toUtf8().data()));
record.theQoS = _qosMap.find(connection, key);
smap[key] = record;
}
}
void
SqlSubscriberMap::QoSMap::put(const DatabaseConnectionPtr& connection,
const SubscriberRecordKey& key,
const QoS& qos)
{
erase(connection, key);
for(QoS::const_iterator p = qos.begin(); p != qos.end(); ++p)
{
QSqlQuery query(connection->sqlConnection());
string queryString = "INSERT INTO ";
queryString += _table;
queryString += " VALUES(?, ?, ?, ?)";
query.prepare(queryString.c_str());
query.bindValue(0, _communicator->identityToString(key.topic).c_str());
query.bindValue(1, _communicator->identityToString(key.id).c_str());
query.bindValue(2, p->first.c_str());
query.bindValue(3, p->second.c_str());
if(!query.exec())
{
throwDatabaseException(__FILE__, __LINE__, query.lastError());
}
}
}
QoS
SqlSubscriberMap::QoSMap::find(const DatabaseConnectionPtr& connection,
const SubscriberRecordKey& key)
{
QSqlQuery query(connection->sqlConnection());
string queryString = "SELECT * FROM ";
queryString += _table;
queryString += " WHERE topic = ? AND id = ?;";
query.prepare(queryString.c_str());
query.bindValue(0, _communicator->identityToString(key.topic).c_str());
query.bindValue(1, _communicator->identityToString(key.id).c_str());
if(!query.exec())
{
throwDatabaseException(__FILE__, __LINE__, query.lastError());
}
QoS qos;
while(query.next())
{
qos[query.value(2).toString().toUtf8().data()] = query.value(3).toString().toUtf8().data();
}
return qos;
}
DatabaseCache::DatabaseCache(const Ice::CommunicatorPtr& communicator,
const string& type,
const string& name,
const string& host,
int port,
const string& user,
const string& password,
bool requiresBlob)
{
Ice::PropertiesPtr properties = communicator->getProperties();
//
// File lock to prevent multiple process open the same db env.
//
if(type == "QSQLITE")
{
_fileLock = new IceUtilInternal::FileLock(name + ".lock");
}
_connection = QSqlDatabase::addDatabase(type.c_str(), IceUtil::generateUUID().c_str());
_connection.setDatabaseName(name.c_str());
_connection.setHostName(host.c_str());
if(port != 0)
{
_connection.setPort(port);
}
_connection.setUserName(user.c_str());
_connection.setPassword(password.c_str());
DatabaseConnectionPtr connection = DatabaseConnectionPtr::dynamicCast(getConnection());
QSqlDriver* driver = connection->sqlConnection().driver();
if(!driver->hasFeature(QSqlDriver::Transactions))
{
throw Ice::InitializationException(__FILE__, __LINE__, "SQL database driver requires transaction support");
}
if(!driver->hasFeature(QSqlDriver::Unicode))
{
throw Ice::InitializationException(__FILE__, __LINE__, "SQL database driver requires unicode support");
}
if(requiresBlob && connection->sqlConnection().driverName() != "QODBC" && !driver->hasFeature(QSqlDriver::BLOB))
{
throw Ice::InitializationException(__FILE__, __LINE__, "SQL database driver requires blob support");
}
}
void
SqlStringApplicationInfoDict::clear(const DatabaseConnectionPtr& connection)
{
QSqlQuery query(connection->sqlConnection());
string queryString = "DELETE FROM ";
queryString += _table;
queryString += ";";
if(!query.exec(queryString.c_str()))
{
throwDatabaseException(__FILE__, __LINE__, query.lastError());
}
}
SqlSubscriberMap::SqlSubscriberMap(const DatabaseConnectionPtr& connection,
const string& table,
const Ice::CommunicatorPtr& communicator) :
_table(table),
_communicator(communicator),
_qosMap(connection, table + "_QoS", communicator)
{
QStringList tables = connection->sqlConnection().tables(QSql::Tables);
if(!tables.contains(_table.c_str(), Qt::CaseInsensitive))
{
QSqlQuery query(connection->sqlConnection());
string queryString = "CREATE TABLE ";
queryString += _table;
queryString += " (topic VARCHAR(255), id VARCHAR(255), topicName ";
if(connection->sqlConnection().driverName() == "QODBC")
{
queryString += "N";
}
queryString += "TEXT, link TEXT, obj TEXT, cost INT, theTopic TEXT, PRIMARY KEY (topic, id));";
if(!query.exec(queryString.c_str()))
{
throwDatabaseException(__FILE__, __LINE__, query.lastError());
}
QSqlQuery idxQuery(connection->sqlConnection());
queryString = "CREATE INDEX IDX_";
queryString += _table;
queryString += "_TOPIC ON ";
queryString += _table;
queryString += " (topic);";
if(!idxQuery.exec(queryString.c_str()))
{
throwDatabaseException(__FILE__, __LINE__, idxQuery.lastError());
}
}
}
void
SqlSubscriberMap::clear(const DatabaseConnectionPtr& connection)
{
_qosMap.clear(connection);
QSqlQuery query(connection->sqlConnection());
string queryString = "DELETE FROM ";
queryString += _table;
queryString += ";";
if(!query.exec(queryString.c_str()))
{
throwDatabaseException(__FILE__, __LINE__, query.lastError());
}
}
SqlSubscriberMap::QoSMap::QoSMap(const DatabaseConnectionPtr& connection,
const string& table,
const Ice::CommunicatorPtr& communicator) :
_table(table),
_communicator(communicator)
{
QStringList tables = connection->sqlConnection().tables(QSql::Tables);
if(!tables.contains(_table.c_str(), Qt::CaseInsensitive))
{
QSqlQuery query(connection->sqlConnection());
string queryString = "CREATE TABLE ";
queryString += _table;
queryString += " (topic VARCHAR(255), id VARCHAR(255), name ";
if(connection->sqlConnection().driverName() == "QODBC")
{
queryString += "N";
}
queryString += "TEXT, value ";
if(connection->sqlConnection().driverName() == "QODBC")
{
queryString += "N";
}
queryString += "TEXT);";
if(!query.exec(queryString.c_str()))
{
throwDatabaseException(__FILE__, __LINE__, query.lastError());
}
QSqlQuery idx1Query(connection->sqlConnection());
queryString = "CREATE INDEX IDX_";
queryString += _table;
queryString += "_TOPIC_SUBSCRIBER ON ";
queryString += _table;
queryString += " (topic, id);";
if(!idx1Query.exec(queryString.c_str()))
{
throwDatabaseException(__FILE__, __LINE__, idx1Query.lastError());
}
QSqlQuery idx2Query(connection->sqlConnection());
queryString = "CREATE INDEX IDX_";
queryString += _table;
queryString += "_TOPIC ON ";
queryString += _table;
queryString += " (topic);";
if(!idx2Query.exec(queryString.c_str()))
{
throwDatabaseException(__FILE__, __LINE__, idx2Query.lastError());
}
}
}
IceGrid::ApplicationInfo
SqlStringApplicationInfoDict::find(const DatabaseConnectionPtr& connection,
const string& name)
{
QSqlQuery query(connection->sqlConnection());
string queryString = "SELECT * FROM ";
queryString += _table;
queryString += " WHERE name = ?;";
query.prepare(queryString.c_str());
query.bindValue(0, name.c_str());
if(!query.exec())
{
throwDatabaseException(__FILE__, __LINE__, query.lastError());
}
if(query.next())
{
IceGrid::ApplicationInfo info;
info.uuid = query.value(1).toString().toStdString();
info.createTime = query.value(2).toLongLong();
info.createUser = query.value(3).toString().toStdString();
info.updateTime = query.value(4).toLongLong();
info.updateUser = query.value(5).toString().toStdString();
info.revision = query.value(6).toInt();
QByteArray bytes = query.value(7).toByteArray();
IceInternal::InstancePtr instance = IceInternal::getInstance(_communicator);
IceInternal::BasicStream stream(instance.get());
stream.b.resize(bytes.size());
::memcpy(&stream.b[0], bytes.data(), bytes.size());
stream.i = stream.b.begin();
stream.startReadEncaps();
info.descriptor.__read(&stream);
stream.readPendingObjects();
stream.endReadEncaps();
return info;
}
else
{
throw NotFoundException(__FILE__, __LINE__);
}
}
void
SqlStringApplicationInfoDict::erase(const DatabaseConnectionPtr& connection,
const string& name)
{
QSqlQuery query(connection->sqlConnection());
string queryString = "DELETE FROM ";
queryString += _table;
queryString += " WHERE name = ?;";
query.prepare(queryString.c_str());
query.bindValue(0, name.c_str());
if(!query.exec())
{
throwDatabaseException(__FILE__, __LINE__, query.lastError());
}
}
void
SqlSubscriberMap::QoSMap::eraseTopic(const DatabaseConnectionPtr& connection,
const Ice::Identity& topic)
{
QSqlQuery query(connection->sqlConnection());
string queryString = "DELETE FROM ";
queryString += _table;
queryString += " WHERE topic = ?;";
query.prepare(queryString.c_str());
query.bindValue(0, _communicator->identityToString(topic).c_str());
if(!query.exec())
{
throwDatabaseException(__FILE__, __LINE__, query.lastError());
}
}
SqlStringApplicationInfoDict::SqlStringApplicationInfoDict(const DatabaseConnectionPtr& connection,
const string& table,
const Ice::CommunicatorPtr& communicator) :
_communicator(communicator),
_table(table)
{
QStringList tables = connection->sqlConnection().tables(QSql::Tables);
if(!tables.contains(_table.c_str(), Qt::CaseInsensitive))
{
QSqlQuery query(connection->sqlConnection());
string queryString = "CREATE TABLE ";
queryString += _table;
queryString += " (name ";
if(connection->sqlConnection().driverName() == "QODBC")
{
queryString += "N";
}
queryString += "VARCHAR(255) PRIMARY KEY, uuid VARCHAR(40), createTime BIGINT, createUser ";
if(connection->sqlConnection().driverName() == "QODBC")
{
queryString += "N";
}
queryString += "TEXT, updateTime BIGINT, updateUser ";
if(connection->sqlConnection().driverName() == "QODBC")
{
queryString += "N";
}
queryString += "TEXT, revision INTEGER, descriptor ";
if(connection->sqlConnection().driverName() == "QPSQL")
{
queryString += "BYTEA";
}
else if(connection->sqlConnection().driverName() == "QODBC")
{
queryString += "VARBINARY(MAX)";
}
else
{
queryString += "BLOB";
}
queryString += ");";
if(!query.exec(queryString.c_str()))
{
throwDatabaseException(__FILE__, __LINE__, query.lastError());
}
}
}
void
SqlSubscriberMap::erase(const DatabaseConnectionPtr& connection,
const SubscriberRecordKey& key)
{
_qosMap.erase(connection, key);
QSqlQuery query(connection->sqlConnection());
string queryString = "DELETE FROM ";
queryString += _table;
queryString += " WHERE topic = ? AND id = ?;";
query.prepare(queryString.c_str());
query.bindValue(0, _communicator->identityToString(key.topic).c_str());
query.bindValue(1, _communicator->identityToString(key.id).c_str());
if(!query.exec())
{
throwDatabaseException(__FILE__, __LINE__, query.lastError());
}
}
void
SqlStringApplicationInfoDict::getMap(const DatabaseConnectionPtr& connection,
StringApplicationInfoDict& applicationMap)
{
QSqlQuery query(connection->sqlConnection());
string queryString = "SELECT * FROM ";
queryString += _table;
queryString += ";";
if(!query.exec(queryString.c_str()))
{
throwDatabaseException(__FILE__, __LINE__, query.lastError());
}
while(query.next())
{
string name = query.value(0).toString().toStdString();
IceGrid::ApplicationInfo info;
info.uuid = query.value(1).toString().toStdString();
info.createTime = query.value(2).toLongLong();
info.createUser = query.value(3).toString().toStdString();
info.updateTime = query.value(4).toLongLong();
info.updateUser = query.value(5).toString().toStdString();
info.revision = query.value(6).toInt();
QByteArray bytes = query.value(7).toByteArray();
IceInternal::InstancePtr instance = IceInternal::getInstance(_communicator);
IceInternal::BasicStream stream(instance.get());
stream.b.resize(bytes.size());
::memcpy(&stream.b[0], bytes.data(), bytes.size());
stream.i = stream.b.begin();
stream.startReadEncaps();
info.descriptor.__read(&stream);
stream.readPendingObjects();
stream.endReadEncaps();
applicationMap[name] = info;
}
}
SubscriberRecord
SqlSubscriberMap::find(const DatabaseConnectionPtr& connection,
const SubscriberRecordKey& key)
{
QSqlQuery query(connection->sqlConnection());
string queryString = "SELECT * FROM ";
queryString += _table;
queryString += " WHERE topic = ? AND id = ?;";
query.prepare(queryString.c_str());
query.bindValue(0, _communicator->identityToString(key.topic).c_str());
query.bindValue(1, _communicator->identityToString(key.id).c_str());
if(!query.exec())
{
throwDatabaseException(__FILE__, __LINE__, query.lastError());
}
if(query.next())
{
SubscriberRecord record;
record.topicName = query.value(2).toString().toUtf8().data();
record.id = key.id;
record.link = string(query.value(3).toString().toUtf8().data()) == "true" ? true : false;
record.obj = _communicator->stringToProxy(query.value(4).toString().toUtf8().data());
record.cost = query.value(5).toInt();
record.theTopic =
TopicPrx::uncheckedCast(_communicator->stringToProxy(query.value(6).toString().toUtf8().data()));
record.theQoS = _qosMap.find(connection, key);
return record;
}
else
{
throw NotFoundException(__FILE__, __LINE__);
}
}
void
SqlStringApplicationInfoDict::put(const DatabaseConnectionPtr& connection,
const string& name,
const ApplicationInfo& info)
{
IceInternal::InstancePtr instance = IceInternal::getInstance(_communicator);
IceInternal::BasicStream stream(instance.get());
stream.startWriteEncaps();
info.descriptor.__write(&stream);
stream.writePendingObjects();
stream.endWriteEncaps();
QSqlQuery query(connection->sqlConnection());
ostringstream queryString;
queryString << "UPDATE " << _table << " SET uuid = ?, createTime = '" << info.createTime
<< "', createUser = ?, updateTime = '" << info.updateTime << "', updateUser = ?, revision = '"
<< info.revision << "', descriptor = ";
QString driver = connection->sqlConnection().driverName();
if(driver == "QPSQL" || driver == "QODBC")
{
vector<Ice::Byte> bytes(stream.b.size());
::memcpy(&bytes[0], stream.b.begin(), stream.b.size());
if(driver == "QPSQL")
{
queryString << "E'" << escapePsqlBinary(bytes) << "'";
}
else
{
queryString << "0x" << escapeOdbcBinary(bytes);
}
}
else
{
queryString << "?";
}
queryString << " WHERE name = ?;";
query.prepare(queryString.str().c_str());
int placeholder = 0;
query.bindValue(placeholder++, info.uuid.c_str());
query.bindValue(placeholder++, info.createUser.c_str());
query.bindValue(placeholder++, info.updateUser.c_str());
if(driver != "QPSQL" && driver != "QODBC")
{
QByteArray bytes;
bytes.resize(static_cast<int>(stream.b.size()));
::memcpy(bytes.data(), stream.b.begin(), stream.b.size());
QVariant descriptor(bytes);
query.bindValue(placeholder++, descriptor);
}
query.bindValue(placeholder, name.c_str());
if(!query.exec())
{
throwDatabaseException(__FILE__, __LINE__, query.lastError());
}
if(query.numRowsAffected() == 0)
{
//
// We do a find since some databases (MySQL) return 0 for number of rows affected
// if row exists but data was not changed from previous values.
//
try
{
find(connection, name);
}
catch(const NotFoundException&)
{
QSqlQuery insertQuery(connection->sqlConnection());
queryString.str("");
queryString << "INSERT INTO " << _table << " VALUES(?, ?, '" << info.createTime << "', ?, '"
<< info.updateTime << "', ?, '" << info.revision << "', ";
if(driver == "QPSQL" || driver == "QODBC")
{
vector<Ice::Byte> bytes(stream.b.size());
::memcpy(&bytes[0], stream.b.begin(), stream.b.size());
if(driver == "QPSQL")
{
queryString << "E'" << escapePsqlBinary(bytes) << "'";
}
else
{
queryString << "0x" << escapeOdbcBinary(bytes);
}
}
else
{
queryString << "?";
}
queryString << ");";
insertQuery.prepare(queryString.str().c_str());
insertQuery.bindValue(0, name.c_str());
insertQuery.bindValue(1, info.uuid.c_str());
insertQuery.bindValue(2, info.createUser.c_str());
insertQuery.bindValue(3, info.updateUser.c_str());
if(driver != "QPSQL" && driver != "QODBC")
{
QByteArray bytes;
bytes.resize(static_cast<int>(stream.b.size()));
::memcpy(bytes.data(), stream.b.begin(), stream.b.size());
QVariant descriptor(bytes);
insertQuery.bindValue(4, descriptor);
}
if(!insertQuery.exec())
{
throwDatabaseException(__FILE__, __LINE__, insertQuery.lastError());
}
}
}
}