QxOrm + Postgres

[ramirezcris]

Good Afternoon:

Receive a welcome greeting from me.

I'm experimenting with QT (QT Creator) and database (Postgres 8.3) with the QxOrm ORM, but I have some doubts.

I'm studying the documentation QxOrm on the page:

http://www.qxorm.com/qxorm_en/home.html

I made some tests using the quick sample:

http://www.qxorm.com/qxorm_en/quick_sample.html

However the example shown for a database (SQLite version 3) and I'm doing tests for Postgres (QPSQL) which compiled and work to connect to the postgres database, making insert, delete, update, etc, all this works.

The connection with the postgres driver I did:

Code: Select all

// Postgres
qx: QxSqlDatabase:: getSingleton () -> setDriverName ("QPSQL");


The trouble arises when I try to insert a table in the database using the QxOrm with the following instructions:

Code: Select all

// Create Table 'drug' within the database
QSqlError daoError = qx:: dao: <drug> create_table ();


This statement produces the following SQL in debug:

CREATE TABLE drug (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT, desc TEXT)

This sql statement is a trouble for postgres tables because apparently "type sequence" in postgres not understand the instruction "AUTOINCREMENT". The postgres throws the following error:

ERROR: syntax error at or near "AUTOINCREMENT"
LINE 1: ... REAT drug TABLE (id INTEGER NOT NULL PRIMARY KEY AUTOINCREM ...
^
********** ********** Error
ERROR: syntax error at or near "AUTOINCREMENT"
SQL state: 42601
Character: 52

I'm not sure I should tell the ORM I want to generate scripts for Postgres database instead of SQLite. I thought that this was done automatically as you enter the driver QPSQL.

I wonder if anyone has had this problem and give me a light.

Thank you very much.

Buenas Tardes:

Reciban un grato saludo de mi parte.

Estoy haciendo pruebas con QT (QT Creator) y base de datos (Postgres 8.3) junto con el ORM QxOrm, pero me han surgido algunas dudas.

Estoy estudiando la documentación de QxOrm en la pagina:

http://www.qxorm.com/qxorm_en/home.html

He realizado algunas pruebas a partir del ejemplo “rápido” de la dirección:

http://www.qxorm.com/qxorm_en/quick_sample.html

Sin embargo el ejemplo se muestra para una base de datos en (SQLite version 3) y yo estoy realizando las pruebas para Postgres (QPSQL) el cual ya compile y funciona para conectarse a la base de datos postgres, hacer inserciones, eliminaciones, modificaciones, etc, todo esto me funciona.

La conexión con el driver postgres la hice así:

Code: Select all

// Postgres     
qx::QxSqlDatabase::getSingleton()->setDriverName("QPSQL");


El problema me surge al intentar insertar una tabla en la base de datos empleando el QxOrm con las instrucciones siguientes:

// Crea Tabla 'drug' dentro de la base de datos
QSqlError daoError = qx::dao::create_table<drug>();

Esta instrucción genera el SQL siguiente según el debug:

Code: Select all

CREATE TABLE drug (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT, desc TEXT)


Este sentencia sql es un problema para postgres porque al pareces las tablas “tipo secuencia” en postgres no comprenden la instrucción “AUTOINCREMENT” se manejan de manera diferente. El postgres arroja el siguiente error:

ERROR: syntax error at or near "AUTOINCREMENT"
LINEA 1: ...REATE TABLE drug (id INTEGER NOT NULL PRIMARY KEY AUTOINCREM...
^
********** Error **********
ERROR: syntax error at or near "AUTOINCREMENT"
SQL state: 42601
Character: 52

No estoy segura si me esta faltando indicarle al ORM que deseo general los scripts de bases de datos para una Base de Datos Postgres y no una base de datos SQLite. Pense que esto se hacia automáticamente al indicar el driver QPSQL.

Quisiera saber si alguien ha tenido un problema similar al mío que me pueda aclarar esta situación o mostrar alguna luz.

Muchas Gracias.

[QxOrm admin]

Hi,

If you read some threads in this forum, you will see that some developers works with QxOrm and Postgres database : so yes, QxOrm library works fine with Postgres.

All SQL queries (SELECT, INSERT, DELETE, UPDATE) builded automatically by QxOrm library will be ok for Postgres database.
The table creation is another thing : "qx::dao::create_table<>" has been tested only with SQLite database, so I don't recommend to use it.
You have to design and manage your database with a tool provided by your SGBD (pgAdmin with PostgresSql, Navicat or other with MySql, etc...).
So, with a tool provided by your SGBD, you can add some indexes in your tables and make others optimizations...

Thanks for using QxOrm library

[ramirezcris]

Thanks for answering

I can clarify anything else? The QxORM is like NHibernate?

With Hibernate you create a data schema "XML" and from these, almost automatically generates the table creation scripts Database and classes.

I wanted to know if the QxOrm works similarly? generates scripts to create tables and classes through the definition of tables with XML data schema?


Gracias por contestar

Me puede aclarar otra cosa? El QxORM es igual que Nhibernate?

Con Hibernate se crea un esquema de datos “XML” y a partir de estos, casi automaticamente genera los scripts de creacion de tablas de Base de Datos y las clases.

Yo queria saber, si el QxOrm trabaja de manera similar? genera scripts de crear tablas y clases a traves con la definición de las tablas con un esquema de datos XML?

[QxOrm admin]

I can clarify anything else? The QxORM is like NHibernate?

Yes, a little bit : you can compare NHibernate for .Net, and QxOrm for C++ (to map C++ classes to tables into database).
But NHibernate is more mature than QxOrm and provide more features. You can also find a lot of tutorials and forums on the web for NHibernate.

With Hibernate you create a data schema "XML" and from these, almost automatically generates the table creation scripts Database and classes.

With Hibernate, you have an XML schema per class.
With QxOrm, you have the mapping function per class : void qx::register_class<T>().

Today, there is no script to generate automatically table creation into database.
You can easily access, modify and delete your datas into database, but you can't design and manage your database with QxOrm. And I don't think that this is the role of an ORM.
There is a lot of optimizations (for example, add indexes to database) if you use a tool provided by your SGBD (and not provided by an ORM).

You can use qx::create_table<T> only with a SQLite database if you want to create a prototype or a quick sample.
But for a production software, I don't recommend to use it.

[Guest]

Ok, Thanks

I'm looking an ORM like Hibernate (c#) but QT (c++) that has this functionality as the Hibernate to build scripts for create databases with XML data schema.

Maybe as you say

And I don't think that this is the role of an ORM.

But hibernate allows this.

PD: I want to do a migration to QT, I'm currently using NHibernate and for me it is essential to use postgres.

Also, thank you very much for your recommendations.

Ok, Gracias

Estoy buscando un ORM parecido a Hibernate (c#) pero para QT (c++) que tenga esta funcionalidad tal como el Hibernate de crear los scripts de creacion de bases de datos con esquema de datos XML.

Quizas como tu dices

No creo que este es el papel de un ORM.

Pero hibernate permite esto.

PD: Quiero hacer una migración a QT, actualmente estoy usando Hibernate y para mi es indispensable el uso del postgres.

De igual manera, muchas gracias por sus recomendaciones.

[QxOrm admin]

In the next release, I will add the following function : qx::dao::dump_sql_schema().
This function will trace the script to create all tables into database.
So you will have the possibility to generate a script and modify it to be ok with your database (for example, change the word "AUTOINCREMENT" by "AUTO INCREMENT").

[ramirezcris]

OK, Thank you very much for this ORM

[edwin]

Hello,

QxORM is a great ORM Library!

But with Postgresql is a diferent history.

Consider the following example:

file main.cpp

#include "../include/precompiled.h"

#include <QtCore/qcoreapplication.h>
#include <QtGui/QApplication>

#include "../include/blog.h"
#include "../include/author.h"
#include "../include/comment.h"
#include "../include/category.h"

#include <QxMemLeak.h>

#include <QMessageBox>

int main(int argc, char * argv[])
{
// Qt application
//QCoreApplication app(argc, argv);

QApplication app(argc, argv);

// Parameters to connect to database

qx::QxSqlDatabase::getSingleton()->setDriverName("QPSQL");
qx::QxSqlDatabase::getSingleton()->setDatabaseName("qxorm_blog");
qx::QxSqlDatabase::getSingleton()->setHostName("localhost");
qx::QxSqlDatabase::getSingleton()->setUserName("postgres");
qx::QxSqlDatabase::getSingleton()->setPassword("admin");


author_ptr author_1; author_1.reset(new author());
author_ptr author_2; author_2.reset(new author());
author_ptr author_3; author_3.reset(new author());

author_1->m_id = "author_id_1"; author_1->m_name = "author_1";
author_1->m_sex = author::male; author_1->m_birthdate = QDate::currentDate();
author_2->m_id = "author_id_2"; author_2->m_name = "author_2";
author_2->m_sex = author::female; author_2->m_birthdate = QDate::currentDate();
author_3->m_id = "author_id_3"; author_3->m_name = "author_3";
author_3->m_sex = author::female; author_3->m_birthdate = QDate::currentDate();

list_author authorX;
authorX.insert(author_1->m_id, author_1);
authorX.insert(author_2->m_id, author_2);
authorX.insert(author_3->m_id, author_3);

QSqlError daoError ;
// Insert list of 3 author into database
daoError = qx::dao::insert(authorX);
qAssert(qx::dao::count<author>() == 3);


// Create a blog with the class name (factory)
boost::any blog_any = qx::create("blog");
blog_ptr blog_1;
try { blog_1 = boost::any_cast<blog_ptr>(blog_any); }
catch (...) { blog_1.reset(new blog()); }

blog_1->m_text = "blog_text_1777";
blog_1->m_dt_creation = QDateTime::currentDateTime();
blog_1->m_author = author_1;

qDebug()<<" blog nextid before save: "<< blog_1->m_id;
// Insert 'blog_1' into database with 'save()' method
daoError = qx::dao::save(blog_1);

qDebug()<<" blog nextid after save: "<< blog_1->m_id;

// Dump all registered classes into QxOrm context (introspection engine)
qx::QxClassX::dumpAllClasses();

return 0;
}

Database tables

CREATE TABLE author
(
author_id text NOT NULL,
"name" text,
birthdate date,
sex integer,
CONSTRAINT author_pkey PRIMARY KEY (author_id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE author OWNER TO postgres;

CREATE TABLE blog
(
blog_id bigserial NOT NULL,
blog_text text,
date_creation date,
author_id text NOT NULL,
CONSTRAINT blog_pkey PRIMARY KEY (blog_id),
CONSTRAINT fkblog900886 FOREIGN KEY (author_id)
REFERENCES author (author_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE blog OWNER TO postgres;

-- Index: blog_blog_id

-- DROP INDEX blog_blog_id;

CREATE UNIQUE INDEX blog_blog_id
ON blog
USING btree
(blog_id);

OUTPUT


Starting /opt/QtProjects/QxOrm/test/_bin/qxBlog...
[QxOrm] qx::QxSqlDatabase : create new database connection in thread '-1217140976' with key '{be3c569b-cb99-47b9-a2f0-ac5da5ff2846}'
[QxOrm] sql query (172 ms) : INSERT INTO author (author_id, name, birthdate, sex) VALUES (:author_id, :name, :birthdate, :sex)
blog nextid before save: 0
[QxOrm] sql query (6 ms) : INSERT INTO blog (blog_text, date_creation, author_id) VALUES (:blog_text, :date_creation, :author_id)
blog nextid after save: 0
[QxOrm] start dump all registered classes (4)
-- class 'author' (name 'author', description '', version '0', base class '')
* list of registered properties (5)
author_id (id)
name
birthdate
sex
list_blog (relation one-to-many)
* list of registered functions (1)
age

-- class 'blog' (name 'blog', description '', version '0', base class '')
* list of registered properties (6)
blog_id (id)
blog_text
date_creation
author_id (relation many-to-one)
list_comment (relation one-to-many)
list_category (relation many-to-many)
* list of registered functions (0)

-- class 'comment' (name 'comment', description '', version '0', base class '')
* list of registered properties (4)
comment_id (id)
comment_text
date_creation
blog_id (relation many-to-one)
* list of registered functions (0)

-- class 'category' (name 'category', description '', version '0', base class '')
* list of registered properties (4)
category_id (id)
name
description
list_blog (relation many-to-many)
* list of registered functions (0)

[QxOrm] end dump all registered classes
/opt/QtProjects/QxOrm/test/_bin/qxBlog exited with code 0


QXORM doesn't syncronize the entity id with table id (WITH POSTGRESQL !!! ) or maybe I'm missing something ??

Thanks in advance

[edwin]

The blog and author entity classes are the same from Blog example

[QxOrm admin]

Hi,

The next time, please create a new thread in the forum with a "good" title : for example "PostgreSQL and last insert ID".

QxOrm library works with QtSql module and uses QSqlQuery::lastInsertId() method to retrieve the last insert id into database : http://doc.qt.nokia.com/latest/qsqlquer ... stInsertId
If you see the documentation provided by Qt, it is written for a PostgreSQL database :
Note: For this function to work in PSQL, the table table must contain OIDs, which may not have been created by default. Check the default_with_oids configuration variable to be sure.

And in your example, you create your tables with "WITH (OIDS=FALSE);".
This is why QSqlQuery::lastInsertId() method returns an invalid QVariant.

But the OID is not the real ID of your object with PostgreSQL database.
If you want to retrieve the real ID of your inserted object, there is at least 3 approaches : http://stackoverflow.com/questions/2944 ... nserted-id

1- SELECT CURRVAL(<sequence name>); . Typically used in conjunction with pg_get_serial_sequence
2- SELECT LASTVAL(); Similar to previous, sequence name not needed. Typically also right, if called just after your insert. (And no, there are no concurrency problems, sessions are isolated).
3- Return the id in the RETURNING expression of INSERT Alternative approach, does not require an extra query, but you must modify your insert query.

So to workaround and retrieve the real inserted ID with QxOrm library, you can use the trigger mechanism. Here are 2 links from the FAQ to help you to implement it :
1- http://www.qxorm.com/qxorm_en/faq.html#faq_130
2- http://www.qxorm.com/qxorm_en/faq.html#faq_120

For example, you can create a base class for all persistents objects with an AUTO INCREMENT field (or sequence with PostgreSQL).
And you can write something like this (a kind of trigger base class) :

Code: Select all

#ifndef _I_AUTO_INCREMENT_H_
#define _I_AUTO_INCREMENT_H_

class QX_MY_DLL_EXPORT IAutoIncrement
{

   QX_REGISTER_FRIEND_CLASS(IAutoIncrement)

protected:

   long m_id;

public:

   IAutoIncrement() : m_id(0) { ; }
   virtual ~IAutoIncrement() { ; }

   long getId() const { return m_id; }
   void setId(long l) { m_id = l; }

   void onBeforeInsert(qx::dao::detail::IxDao_Helper * dao);
   void onAfterInsert(qx::dao::detail::IxDao_Helper * dao);

};

QX_REGISTER_HPP_MY_DLL(IAutoIncrement, qx::trait::no_base_class_defined, 0)

namespace qx {
namespace dao {
namespace detail {

template <>
struct QxDao_Trigger<IAutoIncrement>
{

   static inline void onBeforeInsert(IAutoIncrement * t, qx::dao::detail::IxDao_Helper * dao)
   { if (t) { t->onBeforeInsert(dao); } }
   static inline void onAfterInsert(IAutoIncrement * t, qx::dao::detail::IxDao_Helper * dao)
   { if (t) { t->onAfterInsert(dao); }

   static inline void onBeforeUpdate(IAutoIncrement * t, qx::dao::detail::IxDao_Helper * dao) { Q_UNUSED(t); Q_UNUSED(dao); }
   static inline void onBeforeDelete(IAutoIncrement * t, qx::dao::detail::IxDao_Helper * dao) { Q_UNUSED(t); Q_UNUSED(dao); }
   static inline void onAfterUpdate(IAutoIncrement * t, qx::dao::detail::IxDao_Helper * dao) { Q_UNUSED(t); Q_UNUSED(dao); }
   static inline void onAfterDelete(IAutoIncrement * t, qx::dao::detail::IxDao_Helper * dao) { Q_UNUSED(t); Q_UNUSED(dao); }

};

} // namespace detail
} // namespace dao
} // namespace qx

#endif // _I_AUTO_INCREMENT_H_

Code: Select all

#include "../include/precompiled.h"
#include "../include/IAutoIncrement.h"
#include <QxMemLeak.h>

QX_REGISTER_CPP_MY_DLL(IAutoIncrement)

namespace qx {
template <> void register_class(QxClass<IAutoIncrement> & t)
{
   t.id(& IAutoIncrement::m_id, "id");
}}

void IAutoIncrement::onBeforeInsert(qx::dao::detail::IxDao_Helper * dao)
{
  if (! dao) { qAssert(false); return; }
  QString sql = dao->sql();
  sql += " RETURNING id";
  dao->builder().setSqlQuery(sql);
  dao->query().prepare(sql);
}

void IAutoIncrement::onAfterInsert(qx::dao::detail::IxDao_Helper * dao)
{
  if (! dao) { qAssert(false); return; }
  if (! dao->nextRecord()) { qAssert(false); return; }
  m_id = dao->query().value(0).toLongLong();
}

Now, you just have to inherit all your persitents classes from IAutoIncrement class => your ID must be updated correctly after insertion into database.
Note : IAutoIncrement class can inherit from QObject if you want all your persistents classes inherited from QObject class.

Note : you have also the possibility to manage by yourself the ID of your object (don't create an AUTO INCREMENT field and set the ID in the C++ code before insert an object into database).