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;
}
Exemple #5
0
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());
            }
        }
    }
}