Migrating MySQL database to Postgresql

Forum for posting problems using QxOrm library

Migrating MySQL database to Postgresql

Postby Vipul Shukla » Wed Feb 17, 2021 6:13 am

I havea qt application which was using qxorm library to communicate with MySql database.
Now we want to migrate the database to Postgresql.
As suggested we made changes below:

Code: Select all
    qx::QxSqlDatabase::getSingleton()->setDriverName("QPSQL");
    qx::QxSqlDatabase::getSingleton()->setDatabaseName(database);
    qx::QxSqlDatabase::getSingleton()->setHostName("localhost");
    qx::QxSqlDatabase::getSingleton()->setUserName("postgres");
    qx::QxSqlDatabase::getSingleton()->setPassword("postgres");


With the above changes i was able to connect to database but the queries are failing because postgresql folds the table name and column name to lowercase if they are not surrounded by quotes. I was expecting it to work straight away without any changes to queries.

Is there any way to resolve this issue which I am facing.
Vipul Shukla
 
Posts: 4
Joined: Wed Feb 17, 2021 5:22 am

Re: Migrating MySQL database to Postgresql

Postby qxorm » Wed Feb 17, 2021 8:13 am

Hello,

You can add this setting :
Code: Select all
qx::QxSqlDatabase::getSingleton()->setSqlDelimiterForTableName(QStringList() << "'");


And you have the same settings for columns if necessary : setSqlDelimiterForColumnName()
qxorm
Site Admin
 
Posts: 476
Joined: Mon Apr 12, 2010 7:45 am

Re: Migrating MySQL database to Postgresql

Postby Vipul Shukla » Wed Feb 17, 2021 12:02 pm

Hello,

I set the delimiter for table name and column name and the names are getting enclosed by double quotes.
But the issue now is that the column name under where clause is not getting delimited by double quotes.
Below is the query for your reference :

SELECT "TableName"."ColumnName" WHERE ColumnName = "test";

We can see that the columname is not getting delimited after WHERE clause the query is failing.

Thanks in advance for the help.

Regards,
Vipul
Vipul Shukla
 
Posts: 4
Joined: Wed Feb 17, 2021 5:22 am

Re: Migrating MySQL database to Postgresql

Postby qxorm » Wed Feb 17, 2021 1:08 pm

Hello,

I just tested the qx::dao::fetch_by_id() function and it seems ok for me :
Code: Select all
SELECT 'blog'.'blog_id' AS blog_blog_id_0, 'blog'.'blog_text' AS blog_blog_text_0, 'blog'.'date_creation' AS blog_date_creation_0, 'blog'.'author_id' AS blog_author_id_0
  FROM 'blog'
  WHERE 'blog'.'blog_id' = :blog_id


So I think the issue is with your WHERE conditions.
How do you build them ?
qxorm
Site Admin
 
Posts: 476
Joined: Mon Apr 12, 2010 7:45 am

Re: Migrating MySQL database to Postgresql

Postby Vipul Shukla » Wed Feb 17, 2021 2:42 pm

Hello,

I use the below statements to create the query:
Code: Select all
       
            query.where(EventsDAO::column_EventTime()).isGreaterThanOrEqualTo(start);
           
            query.and_(EventsDAO::column_EventTime()).isLessThanOrEqualTo(end);
       
           query.and_(EventsDAO::relation_FK_UserId()).isEqualTo(userId);

           daoError = qx::dao::fetch_by_query_with_relation( relation, query,events);

           
   
Vipul Shukla
 
Posts: 4
Joined: Wed Feb 17, 2021 5:22 am

Re: Migrating MySQL database to Postgresql

Postby qxorm » Wed Feb 17, 2021 3:24 pm

Yes, qx::QxSqlQuery class doesn't check the qx::QxSqlDatabase::setSqlDelimiterForColumnName() setting.

You could try these patches (add qx::IxDataMember::getSqlColumnName() when necessary) :

1- file .\src\QxDao\QxSqlElement\QxSqlCompare.cpp (line 55) :
Code: Select all
QString sReturn, sColumn(qx::IxDataMember::getSqlColumnName(m_lstColumns.at(0))), sKey(m_lstKeys.at(0));


2- file .\src\QxDao\QxSqlElement\QxSqlEmbedQuery.cpp (line 72) :
Code: Select all
QString column = qx::IxDataMember::getSqlColumnName(m_lstColumns.at(0));


3- file .\src\QxDao\QxSqlElement\QxSqlIn.cpp (line 55) :
Code: Select all
QString sReturn, sColumn(qx::IxDataMember::getSqlColumnName(m_lstColumns.at(0))), sKey(m_lstKeys.at(0));


4- file .\src\QxDao\QxSqlElement\QxSqlIsBetween.cpp (line 55) :
Code: Select all
QString sReturn, sColumn(qx::IxDataMember::getSqlColumnName(m_lstColumns.at(0))), sKey(m_lstKeys.at(0));


5- file .\src\QxDao\QxSqlElement\QxSqlIsNull.cpp (line 53) :
Code: Select all
QString sReturn, sColumn(qx::IxDataMember::getSqlColumnName(m_lstColumns.at(0)));


6- file .\src\QxDao\QxSqlElement\QxSqlSort.cpp (line 66) :
Code: Select all
QString sColumn = qx::IxDataMember::getSqlColumnName(m_lstColumns.at(i));


You will maybe have to add this include too in these *.cpp files :
Code: Select all
#include <QxDataMember/IxDataMember.h>



But how do you migrate your database from MySql to Postgres ?
Postgres by default has all its table names and column names in lower case.
Why did you create them exactly like MySql ?
Maybe you should consider to use the native way in postgres, so create your tables and columns without the " special character, and everything will be in lower case.
And you will be compatible MySql and Postgres without having to change anything (all SQL queries will be translated in lower case automatically by postgres engine).
qxorm
Site Admin
 
Posts: 476
Joined: Mon Apr 12, 2010 7:45 am

Re: Migrating MySQL database to Postgresql

Postby Vipul Shukla » Fri Feb 19, 2021 7:52 am

Hello,
The answer to your queries are:

1. But how do you migrate your database from MySql to Postgres ?
We create and sql dump using mysql dump utility and then convert it to postgres using a script.
Then we load that dump into postgres database.


2. Why did you create them exactly like MySql ?
The table is used by multiple application that is why we need to keep the table name and column name as same.
Plus we want to keep the old data in the database.


I tried the patch provided by you still i am facing the same issue and getting the error below:
Database error number '0' : QPSQL: Unable to prepare statement
QPSQL: Unable to create query
ERROR: missing FROM-clause entry for table "AccessLevel_1"
LINE 1: ...r" LEFT OUTER JOIN "AccessLevel" AccessLevel_1 ON "AccessLev...


Apart from that i can also see that the column name after FROM clause is not getting quoted.
Below is the query created by QxOrm:
SELECT "Usr"."User_Id" AS Usr_User_Id_0, "Usr"."Username" AS Usr_Username_0, "Usr"."Password" AS Usr_Password_0, "Usr"."PasswordExpiryTime" AS Usr_PasswordExpiryTime_0, "Usr"."Description" AS Usr_Description_0, "Usr"."FirstName" AS Usr_FirstName_0, "Usr"."LastName" AS Usr_LastName_0, "Usr"."PhoneNumber" AS Usr_PhoneNumber_0, "Usr"."EmailAddress" AS Usr_EmailAddress_0, "Usr"."CompanyName" AS Usr_CompanyName_0, "Usr"."CompanyAddress" AS Usr_CompanyAddress_0, "Usr"."CreatedTime" AS Usr_CreatedTime_0, "Usr"."ModifiedTime" AS Usr_ModifiedTime_0, "Usr"."IsDeleted" AS Usr_IsDeleted_0, "Usr"."UserFailedLogonAttempts" AS Usr_UserFailedLogonAttempts_0, "Usr"."UserLastLogonTime" AS Usr_UserLastLogonTime_0, "Usr"."LockStatus" AS Usr_LockStatus_0, "Usr"."AutoLockTime" AS Usr_AutoLockTime_0, "Usr"."Language" AS Usr_Language_0, "Usr"."CreatedBy" AS Usr_CreatedBy_0, "Usr"."AccessLevelId" AS Usr_AccessLevelId_0, "AccessLevel_1"."AccessLevelId" AS AccessLevel_1_AccessLevelId_0, "AccessLevel_1"."LevelNumber" AS AccessLevel_1_LevelNumber_0, "AccessLevel_1"."Description" AS AccessLevel_1_Description_0 FROM "Usr" LEFT OUTER JOIN "AccessLevel" AccessLevel_1 ON "AccessLevel_1"."AccessLevelId" = "Usr"."AccessLevelId" WHERE IsDeleted = 0 AND UPPER(Usr.Username) = :name


I tried going through the code. I am not sure if it is the correct flow but i checked the QxSqlQuery.cpp file in that i can see that the column name is not getting quoted:
Code: Select all
QxSqlQuery & QxSqlQuery::where(const QString & column)
{
   return addSqlExpression(column, qx::dao::detail::QxSqlExpression::_where);
}

QxSqlQuery & QxSqlQuery::addSqlExpression(const QString & column, qx::dao::detail::QxSqlExpression::type type)
{
   qx::dao::detail::QxSqlExpression_ptr p;
   p = std::make_shared<qx::dao::detail::QxSqlExpression>(m_iSqlElementIndex++, type);
   m_lstSqlElement.append(p);

   m_pSqlElementTemp = std::make_shared<qx::dao::detail::QxSqlElementTemp>();
   m_pSqlElementTemp->setColumn(column);
   return (* this);
}


From the above line of code we can see that "QxSqlQuery::addSqlExpression" adds column using the class "qx::dao::detail::QxSqlElementTemp" which doesn't check for delimiters for column.

Not sure if I am checking the correct code.

I will also attach the changes I made as patch file.
Vipul Shukla
 
Posts: 4
Joined: Wed Feb 17, 2021 5:22 am

Re: Migrating MySQL database to Postgresql

Postby qxorm » Fri Feb 19, 2021 8:31 am

From the above line of code we can see that "QxSqlQuery::addSqlExpression" adds column using the class "qx::dao::detail::QxSqlElementTemp" which doesn't check for delimiters for column.

This is done later by the patch I provided you in my previous comment.

About the IsDeleted : I think this is the soft delete behavior, so maybe we should patch the file ./src/QxDao/QxSoftDelete.cpp.

ERROR: missing FROM-clause entry for table "AccessLevel_1"
LINE 1: ...r" LEFT OUTER JOIN "AccessLevel" AccessLevel_1 ON "AccessLev...

I don't understand this error on the SQL alias : have you tried to copy/past this query on your SGDB tool to see what it is needed to modify to fix it ?

The table is used by multiple application that is why we need to keep the table name and column name as same.

Ok but imagine your have :
- in MySql, a table named : Usr
- in Postgres, a table named : usr
- and the SQL query : SELECT * FROM Usr
==> this SQL query should work exactly the same in MySQL and Postgres because Postgres convert automatically everything in lower case.

This is why you could try to recreate your Postgres schema without adding the "" in the CREATE TABLE statements ==> everything will be in lower case but your SQL queries should work in MySQL and Postgres exactly the same.
qxorm
Site Admin
 
Posts: 476
Joined: Mon Apr 12, 2010 7:45 am


Return to QxOrm - Help

Who is online

Users browsing this forum: No registered users and 1 guest

cron