Extra-table creation for many-to-many relation

[Flaviu]

Hi,

I started few days ago to use for the first time QxOrm. I'm totally new to this but I'm very excited about QxOrm.
I made a small application using many-to-many relation. My issue now is the creation of extra-table for many-to-many relation. It seems this table isn't created automatically. I don't known what is wrong and now I'm stuck. On debug I saw it is failing on

Code: Select all

qx::QxSqlRelation_ManyToMany<qx::QxCollection<long,boost::shared_ptr<Alpha> >,Beta>::onAfterSave(qx::QxSqlRelationParams & params)  Line 146

trying to delete records from a non-existing table. I'm using QxOrm 1.1.4 and Qt 4.7.1 on Win32 - VS 2010. My code is the following:
main.h

Code: Select all

#pragma once

class Alpha
{
public:
   long id;
   int size;

   virtual ~Alpha() {}
};

typedef boost::shared_ptr<Alpha> AlphaPtr;
typedef std::vector<AlphaPtr> AlphaVector;
typedef qx::QxCollection<long, AlphaPtr> AlphaList;

class Beta
{
public:
   long id;
   QString name;
   AlphaList items;

   virtual ~Beta() {}
};

typedef boost::shared_ptr<Beta> BetaPtr;

QX_REGISTER_HPP_MY_TEST_EXE(Alpha, qx::trait::no_base_class_defined, 1)
QX_REGISTER_HPP_MY_TEST_EXE(Beta, qx::trait::no_base_class_defined, 1)
 

main.cpp

Code: Select all

#include "stdafx.h"
#include "main.h"
#include "QxMemLeak.h"

QX_REGISTER_CPP_MY_TEST_EXE(Alpha)
QX_REGISTER_CPP_MY_TEST_EXE(Beta)

namespace qx
{
   template <> void register_class( QxClass<Alpha>& t)
   {
      t.id(& Alpha::id, "id");
      t.data(& Alpha::size, "size", 1);
   }

   template <> void register_class( QxClass<Beta>& t)
   {
      t.id(& Beta::id, "id");
      t.data(& Beta::name, "name", 1);
      t.relationManyToMany( &Beta::items, "Beta_Alpha_Rel", "Beta_Alpha", "Beta_id", "Alpha_id", 1);
   }
}

void main(int argc, char * argv[])
{
   qx::QxSqlDatabase* const db = qx::QxSqlDatabase::getSingleton();
   db->setDriverName("QSQLITE");
   db->setDatabaseName("mytest.db");
   db->setHostName("localhost");
   db->setUserName("root");
   db->setPassword("");

   AlphaPtr a1( new Alpha()); a1->size = 100;
   AlphaPtr a2( new Alpha()); a2->size = 200;

   AlphaVector alphas;
   alphas.push_back( a1);
   alphas.push_back( a2);

   QSqlError daoError = qx::dao::create_table<Alpha>();
   daoError = qx::dao::insert( alphas);

   BetaPtr b1( new Beta());
   b1->name = "test name";
   b1->items.insert( a1->id, a1);
   b1->items.insert( a2->id, a2);

   daoError = qx::dao::create_table<Beta>();
   daoError = qx::dao::save_with_all_relation( b1);
   qAssert( daoError.type() == QSqlError::NoError); // <-- STOPS HERE, daoError = StatementError

   BetaPtr b2( new Beta());
   b2->id = 1;
   daoError = qx::dao::fetch_by_id_with_all_relation( b2);
}


The output is

Code: Select all

[QxOrm] qx::QxSqlDatabase : create new database connection in thread '4200' with key '{ea667e20-5330-4c76-a75a-bd43340d1
ce1}'
[QxOrm] sql query (26 ms) : CREATE TABLE Alpha (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, size INTEGER)
[QxOrm] sql query (3 ms) : INSERT INTO Alpha (size) VALUES (:size)
[QxOrm] sql query (2 ms) : CREATE TABLE Beta (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT)
[QxOrm] sql query (0 ms) : SELECT Beta.id AS Beta_id_0 FROM Beta WHERE Beta.id = :id
[QxOrm] sql query (0 ms) : INSERT INTO Beta (name) VALUES (:name)
[QxOrm] sql query (1 ms) : SELECT Alpha.id AS Alpha_id_0 FROM Alpha WHERE Alpha.id = :id
[QxOrm] sql query (0 ms) : UPDATE Alpha SET id = :id, size = :size WHERE id = :id_bis
[QxOrm] sql query (0 ms) : SELECT Alpha.id AS Alpha_id_0 FROM Alpha WHERE Alpha.id = :id
[QxOrm] sql query (1 ms) : UPDATE Alpha SET id = :id, size = :size WHERE id = :id_bis
[QxOrm] sql query (extra-table) : DELETE FROM Beta_Alpha WHERE Beta_Alpha.Beta_id = :id
ASSERT: "daoError.type() == QSqlError::NoError" in file main.cpp, line 50


Thank you

[QxOrm admin]

Hi,

I don't recommend to use "qx::dao::create_table<>".
You are right, there is a problem with relation many-to-many and create table function because extra-table is not created with current version of QxOrm.

You have to design your database with a tool provided by your SGBD (pgAdmin with PostgresSql, Navicat or other with MySql, etc...).
You could also add some indexes in your tables, and other things to improve your database management.

[Flaviu]

OK. I understand you recommend to use a DB that has the tables and indexes defined, at least for the many-to-many relations.

[QxOrm admin]

Yes, because create table is not very standard SQL with all databases on the market.
qxBlog tutorial doesn't use "qx::dao::create_table<>" function, the database has been designed with SQLite Manager plugin for Firefox.

I will correct "many-to-many and create table" bug in the next version...
It will work at least with SQLite database, with others I don't know.

Just a thing with your code : you have a many-to-many relation but your class Alpha has no list of Beta.

[Flaviu]

Welcome news. Thanks.

About the class Alpha that has no list of Beta, I think it should not be a problem. In my case Alpha class should not be aware about Beta and, in this case, Beta still has a many-to-many relation with A.