save with relations doesn't update id

[Davita]

Hi. I'm facing a strange issue. I have 2 classes, City and District. City has many to one relation with district. Here's the code

Code: Select all

class QX_SERVICE_DLL_EXPORT City
{
private:
    typedef QSharedPointer<District> DistrictPtr;

    long m_Id;
    QString m_Name;
    DistrictPtr m_District;

    friend void registerOrm(qx::QxClass<City> & t);

public:
    City() : m_Id(0) { ; }
    virtual ~City() { ; }

    inline long id() const { return m_Id; }
    inline void setId(long id) { m_Id = id; }

    inline const QString & name() const { return m_Name; }
    inline void setName(const QString & name) { m_Name = name; }

    inline const DistrictPtr & district() const { return m_District; }
    inline void setDistrict(const DistrictPtr & district) { m_District = district; }
};

class QX_SERVICE_DLL_EXPORT District
{
public:
    District() : m_Id(0) { ; }
    virtual ~District() { ; }

    inline long id() const { return m_Id; }
    inline void setId(long id) { m_Id = id; }

    inline const QString & name() { return m_Name; }
    inline void setName(const QString & name) { m_Name = name; }

private:
    long m_Id;
    QString m_Name;

    friend void registerOrm(qx::QxClass<District> & t);
};

        CityPtr city(new City());
        city->setName(CITY_NAME);

        DistrictPtr district(new District());
        district->setName(DISTRICT_NAME);

        city->setDistrict(district);

        /// Test insertion ///
        QSqlError err = qx::dao::insert_with_all_relation(city);
        QVERIFY(err.isValid() == false);
        QVERIFY(city->id() != 0);
        QVERIFY(district->id() != 0);


This code executes fine, but when I look at the database, in City table, DistrictId is 0. I should call qx::dao::save(city) after insert_with_all_relations in order to update that value (DistrictId). Doesn't qxorm automatically resolves IDs? Or we should assign them explicitly?

Thanks
Thanks.

[QxOrm admin]

Doesn't qxorm automatically resolves IDs? Or we should assign them explicitly?

It depends on your database : what is your database engine : SQLite, PostgreSQL, MySQL, Oracle, other ?
QxOrm library works with QtSql module of Qt framework : to verify if your database provides the "last insert id" feature, you can use the following function : http://doc-snapshot.qt-project.org/4.8/ ... hasFeature

Code: Select all

bool QSqlDriver::hasFeature ( QSqlDriver::LastInsertId );

Note : for PostgreSQL, there is a specific code to retrieve the ID using the RETURNING keyword of PostgreSQL.

I have 2 classes, City and District. City has many to one relation with district.

You are exactly in the same situation as qxBlog project with comment and blog classes : http://www.qxorm.com/qxorm_en/tutorial.html#tuto_7
You must add a collection of City into your District class.
Moreover, what is friend void registerOrm(qx::QxClass<City> & t); ? You must use the QX_REGISTER_FRIEND_CLASS macro and you don't need your registerOrm function !!!

Could you provide please your register_class<T> function to verify that all is Ok ?

[Davita]

Now I see the problem. I'm using Sqlite and it doesn't support returning last insert id. So I guess QxOrm relies on that functionality which Sqlite doesn't provide and that's why I need to call save/update after insert_with_all_relations.
And about collections, I didn't create a collection of City for District class, simply because I didn't need it. I want to have uni-directional associations where appropriate.
Could you provide more details about QX_REGISTER_FRIEND_CLASS macro? Actually what does it do and why we should use it instead of standard friend declaration? I have friend function registerOrm because as you can see, I have declared member variables as private. They can be accessed through getter methods, and because register_class is a template specialization function, I had problems declaring it as a friend, that's why I defined another friend function registerOrm where I'm doing mappings. register_class just calls registerOrm.

Code: Select all

QX_REGISTER_CPP_QX_SERVICE(District)

namespace qx {
template <> void register_class(QxClass<District> & t)
{
    registerOrm(t);
}}

void registerOrm(qx::QxClass<District> & t)
{
    t.id(&District::m_Id, "Id");
    t.data(&District::m_Name, "Name");
}

Thank you very much.

[QxOrm admin]

I'm using Sqlite and it doesn't support returning last insert id

No, SQLite supports last insert id feature !
Do you execute qxBlog project ? There is an assert to verify that ID are auto-updated after an insert query (see the lines 89 to 91 of the main file) :

Code: Select all

qAssert(category_1->m_id != 0);
qAssert(category_2->m_id != 0);
qAssert(category_3->m_id != 0);

Could you provide more details about QX_REGISTER_FRIEND_CLASS macro?

This macro must be used if you want to declare your C++ properties protected or private (this is just a friend class declaration) => using this macro, you don't need your registerOrm function !
For example, you can open the file CPerson.h into './test/qxDllSample/dll1/include/' directory of QxOrm package.

[Davita]

This macro must be used if you want to declare your C++ properties protected or private (this is just a friend class declaration) => using this macro, you don't need your registerOrm function !
For example, you can open the file CPerson.h into './test/qxDllSample/dll1/include/' directory of QxOrm package.

I didn't know that, many thanks for the info

No, SQLite supports last insert id feature !
Do you execute qxBlog project ? There is an assert to verify that ID are auto-updated after an insert query (see the lines 89 to 91 of the main file) :

Ouch, I just rechecked and sqlite supports last insert id. Don't remember why I remembered otherwise.
So if last insert id is not a problem, what causes my problem?

Code: Select all

    void test_relations()
    {
        static const QString CITY_NAME = "quteisi";
        static const QString DISTRICT_NAME = "ozurgeti";

        /// Create objects ///
        CityPtr city(new City());
        city->setName(CITY_NAME);

        DistrictPtr district(new District());
        district->setName(DISTRICT_NAME);

        city->setDistrict(district);

        /// Test insertion ///
        QSqlError err = qx::dao::insert_with_all_relation(city);
        QVERIFY(err.isValid() == false);
        QVERIFY(city->id() != 0);
        QVERIFY(district->id() != 0);

        //err = qx::dao::save(city);
        QVERIFY(err.isValid() == false);

        /// Fetch testing ///
        long city_id = city->id();
        city = CityPtr(new City());
        city->setId(city_id);
        city->setDistrict(DistrictPtr(new District()));

        err = qx::dao::fetch_by_id_with_all_relation(city);
        QVERIFY(err.isValid() == false);
        QVERIFY(city->district()->id() == district->id()); // Breaks here, because DistrictId is not saved in City table.
        QVERIFY(city->district()->name() == DISTRICT_NAME);
    }


The only reason I think of, is that QxOrm persists first City and then District, and by the time it tries to save City object, it's related District id is 0. Shouldn't it try to save it's relations first? Could this be a bug?

Thanks

[QxOrm admin]

The only reason I think of, is that QxOrm persists first City and then District, and by the time it tries to save City object, it's related District id is 0. Shouldn't it try to save it's relations first? Could this be a bug?

You are right : I just tested the same situation with qxBlog project trying to insert with all relations a comment :
1- First, the comment is saved into database.
2- Then, the blog is saved.
So blog_id is not updated into comment table : you can consider it as a bug !

To workaround easily this problem, you can use QxOrm triggers.
For more details about triggers, goto here : http://www.qxorm.com/qxorm_en/faq.html#faq_130
With triggers, you can write something like this :

Code: Select all

void City::onAfterInsert(qx::dao::detail::IxDao_Helper * dao)
{
   if (! m_District) { return; }
   QSqlError daoError = qx::dao::save(m_District, dao->database());
   if (daoError.isValid()) { dao->updateError(daoError); return; }
   daoError = qx::dao::update((* this), dao->database(), QStringList() << "DistrictId");
   if (daoError.isValid()) { dao->updateError(daoError); }
}

void City::onAfterUpdate(qx::dao::detail::IxDao_Helper * dao)
{
   if (! m_District) { return; }
   QSqlError daoError = qx::dao::save(m_District, dao->database());
   if (daoError.isValid()) { dao->updateError(daoError); return; }
   daoError = qx::dao::update((* this), dao->database(), QStringList() << "DistrictId");
   if (daoError.isValid()) { dao->updateError(daoError); }
}

This way, you don't have to use qx::dao::insert_with_all_relation function : qx::dao::insert is enough

[Davita]

Thank you very much