kexi

pqxxmigrate.cpp

00001 /* This file is part of the KDE project
00002    Copyright (C) 2004 Adam Pigg <adam@piggz.co.uk>
00003    Copyright (C) 2006 Jaroslaw Staniek <js@iidea.pl>
00004  
00005    This program is free software; you can redistribute it and/or
00006    modify it under the terms of the GNU Library General Public
00007    License as published by the Free Software Foundation; either
00008    version 2 of the License, or (at your option) any later version.
00009  
00010    This program is distributed in the hope that it will be useful,
00011    but WITHOUT ANY WARRANTY; without even the implied warranty of
00012    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
00013    Library General Public License for more details.
00014  
00015    You should have received a copy of the GNU Library General Public License
00016    along with this program; see the file COPYING.  If not, write to
00017    the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
00018  * Boston, MA 02110-1301, USA.
00019 */
00020 
00021 #include "pqxxmigrate.h"
00022 #include "pg_type.h"
00023 
00024 #include <qstring.h>
00025 #include <kdebug.h>
00026 #include <qstringlist.h>
00027 
00028 //I maybe should not use stl?
00029 #include <string>
00030 #include <vector>
00031 
00032 #include <kexidb/cursor.h>
00033 #include <kexidb/utils.h>
00034 #include <kexidb/drivermanager.h>
00035 #include <kexiutils/identifier.h>
00036 #include <kexidb/drivers/pqxx/pqxxcursor.h> //for pgsqlCStrToVariant()
00037 
00038 using namespace KexiDB;
00039 using namespace KexiMigration;
00040 
00041 /*
00042 This is the implementation for the pqxx specific import routines
00043 Thi is currently pre alpha and in no way is it meant
00044 to compile, let alone work.  This is meant as an example of
00045 what the system might be and is a work in progress
00046 */
00047 
00048 KEXIMIGRATE_DRIVER_INFO( PqxxMigrate, pqxx )
00049 
00050 //==================================================================================
00051 //Constructor
00052 /*PqxxMigrate::PqxxMigrate()
00053  : KexiMigrate(parent, name, args)
00054 {
00055     m_res=0;
00056     m_trans=0;
00057     m_conn=0;
00058 }*/
00059 
00060 PqxxMigrate::PqxxMigrate(QObject *parent, const char *name, const QStringList &args)
00061  : KexiMigrate(parent, name, args)
00062 {
00063     m_res=0;
00064     m_trans=0;
00065     m_conn=0;
00066     KexiDB::DriverManager manager;
00067     m_kexiDBDriver = manager.driver("pqxx");
00068 }
00069 //==================================================================================
00070 //Destructor
00071 PqxxMigrate::~PqxxMigrate()
00072 {
00073     clearResultInfo();
00074 }
00075 
00076 //==================================================================================
00077 //This is probably going to be quite complex...need to get the types for all columns
00078 //any any other attributes required by kexi
00079 //helped by reading the 'tables' test program
00080 bool PqxxMigrate::drv_readTableSchema(
00081     const QString& originalName, KexiDB::TableSchema& tableSchema)
00082 {
00083 //    m_table = new KexiDB::TableSchema(table);
00084 
00085     //TODO IDEA: ask for user input for captions
00086 //moved    m_table->setCaption(table + " table");
00087 
00088     //Perform a query on the table to get some data
00089     if (query("select * from \"" + originalName + "\" limit 1"))
00090     {
00091         //Loop round the fields
00092         for (uint i = 0; i < (uint)m_res->columns(); i++)
00093         {
00094             QString fldName(m_res->column_name(i));
00095             KexiDB::Field::Type fldType = type(m_res->column_type(i), fldName);
00096             QString fldID( KexiUtils::string2Identifier(fldName) );
00097             const pqxx::oid toid = tableOid(originalName);
00098             if (toid==0)
00099                 return false;
00100             KexiDB::Field *f = new KexiDB::Field(fldID, fldType);
00101             f->setCaption(fldName);
00102             f->setPrimaryKey(primaryKey(toid, i));
00103             f->setUniqueKey(uniqueKey(toid, i));
00104             f->setAutoIncrement(autoInc(toid, i));//This should be safe for all field types
00105             tableSchema.addField(f);
00106 
00107             // Do this for var/char types
00108             //m_f->setLength(m_res->at(0)[i].size());
00109 
00110            // Do this for numeric type
00111            /*m_f->setScale(0);
00112            m_f->setPrecision(0);*/
00113 
00114            kdDebug() << "Added field [" << f->name() << "] type [" << f->typeName() 
00115             << "]" << endl;
00116         }
00117         return true;
00118     }
00119     else
00120     {
00121         return false;
00122     }
00123 }
00124 
00125 //==================================================================================
00126 //get a list of tables and put into the supplied string list
00127 bool PqxxMigrate::drv_tableNames(QStringList& tableNames)
00128 {
00129     /*
00130     //pg_ = standard postgresql tables, pga_ = tables added by pgaccess, sql_ = probably information schemas, kexi__ = existing kexi tables
00131     if (query("SELECT relname FROM pg_class WHERE ((relkind = 'r') AND ((relname !~ '^pg_') AND (relname !~ '^pga_') AND (relname !~ '^sql_') AND (relname !~ '^kexi__')))"))
00132     */
00133     if (query("SELECT relname FROM pg_class WHERE ((relkind = 'r') AND ((relname !~ '^pg_') AND (relname !~ '^pga_') AND (relname !~ '^sql_')))"))
00134     {
00135         for (pqxx::result::const_iterator c = m_res->begin(); c != m_res->end(); ++c)
00136         {
00137             // Copy the result into the return list
00138             tableNames << QString::fromLatin1 (c[0].c_str());
00139         }
00140         return true;
00141     }
00142     else
00143     {
00144         return false;
00145     }
00146 }
00147 
00148 //==================================================================================
00149 //Convert a postgresql type to a kexi type
00150 KexiDB::Field::Type PqxxMigrate::type(int t, const QString& fname)
00151 {
00152     switch(t)
00153     {
00154     case UNKNOWNOID:
00155         return KexiDB::Field::InvalidType;
00156     case BOOLOID:
00157         return KexiDB::Field::Boolean;
00158     case INT2OID:
00159         return KexiDB::Field::ShortInteger;
00160     case INT4OID:
00161         return KexiDB::Field::Integer;
00162     case INT8OID:
00163         return KexiDB::Field::BigInteger;
00164     case FLOAT4OID:
00165         return KexiDB::Field::Float;
00166     case FLOAT8OID:
00167         return KexiDB::Field::Double;
00168     case NUMERICOID:
00169         return KexiDB::Field::Double;
00170     case DATEOID:
00171         return KexiDB::Field::Date;
00172     case TIMEOID:
00173         return KexiDB::Field::Time;
00174     case TIMESTAMPOID:
00175         return KexiDB::Field::DateTime;
00176     case BYTEAOID:
00177         return KexiDB::Field::BLOB;
00178     case BPCHAROID:
00179         return KexiDB::Field::Text;
00180     case VARCHAROID:
00181         return KexiDB::Field::Text;
00182     case TEXTOID:
00183         return KexiDB::Field::LongText;
00184     }
00185 
00186     //Ask the user what to do with this field
00187     return userType(fname);
00188 }
00189 
00190 //==================================================================================
00191 //Connect to the db backend
00192 bool PqxxMigrate::drv_connect()
00193 {
00194     kdDebug() << "drv_connect: " << m_migrateData->sourceName << endl;
00195 
00196     QString conninfo;
00197     QString socket;
00198 
00199     //Setup local/remote connection
00200     if (m_migrateData->source->hostName.isEmpty())
00201     {
00202         if (m_migrateData->source->fileName().isEmpty())
00203         {
00204             socket="/tmp/.s.PGSQL.5432";
00205         }
00206         else
00207         {
00208             socket=m_migrateData->source->fileName();
00209         }
00210     }
00211     else
00212     {
00213         conninfo = "host='" + m_migrateData->source->hostName + "'";
00214     }
00215 
00216     //Build up the connection string
00217     if (m_migrateData->source->port == 0)
00218         m_migrateData->source->port = 5432;
00219 
00220     conninfo += QString::fromLatin1(" port='%1'").arg(m_migrateData->source->port);
00221 
00222     conninfo += QString::fromLatin1(" dbname='%1'").arg(m_migrateData->sourceName);
00223 
00224     if (!m_migrateData->source->userName.isNull())
00225         conninfo += QString::fromLatin1(" user='%1'").arg(m_migrateData->source->userName);
00226 
00227     if (!m_migrateData->source->password.isNull())
00228         conninfo += QString::fromLatin1(" password='%1'").arg(m_migrateData->source->password);
00229 
00230     try
00231     {
00232         m_conn = new pqxx::connection( conninfo.latin1() );
00233         return true;
00234     }
00235     catch(const std::exception &e)
00236     {
00237         kdDebug() << "PqxxMigrate::drv_connect:exception - " << e.what() << endl;
00238     }
00239     catch(...)
00240     {
00241         kdDebug() << "PqxxMigrate::drv_connect:exception(...)??" << endl;
00242     }
00243     return false;
00244 }
00245 
00246 //==================================================================================
00247 //Connect to the db backend
00248 bool PqxxMigrate::drv_disconnect()
00249 {
00250     if (m_conn)
00251     {
00252         m_conn->disconnect();
00253         delete m_conn;
00254         m_conn = 0;
00255     }
00256     return true;
00257 }
00258 //==================================================================================
00259 //Perform a query on the database and store result in m_res
00260 bool PqxxMigrate::query(const QString& statement)
00261 {
00262     kdDebug() << "query: " << statement.latin1() << endl;
00263 
00264     Q_ASSERT (m_conn);
00265 
00266     // Clear the last result information...
00267     clearResultInfo ();
00268 
00269     try
00270     {
00271         //Create a transaction
00272         m_trans = new pqxx::nontransaction(*m_conn);
00273         //Create a result opject through the transaction
00274         m_res = new pqxx::result(m_trans->exec(statement.latin1()));
00275         //Commit the transaction
00276         m_trans->commit();
00277         //If all went well then return true, errors picked up by the catch block
00278         return true;
00279     }
00280     catch (const std::exception &e)
00281     {
00282         //If an error ocurred then put the error description into _dbError
00283         kdDebug() << "pqxxImport::query:exception - " << e.what() << endl;
00284         return false;
00285     }
00286     catch(...)
00287     {
00288         kdDebug() << "PqxxMigrate::query:exception(...)??" << endl;
00289     }
00290     return true;
00291 }
00292 
00293 //=========================================================================
00294 //Clears the current result
00295 void PqxxMigrate::clearResultInfo()
00296 {
00297     delete m_res;
00298     m_res = 0;
00299 
00300     delete m_trans;
00301     m_trans = 0;
00302 }
00303 
00304 //=========================================================================
00305 //Return the OID for a table
00306 pqxx::oid PqxxMigrate::tableOid(const QString& table)
00307 {
00308     QString statement;
00309     static QString otable;
00310     static pqxx::oid toid;
00311 
00312     pqxx::nontransaction* tran = 0;
00313     pqxx::result* tmpres = 0;
00314 
00315     //Some simple result caching
00316     if (table == otable)
00317     {
00318         kdDebug() << "Returning table OID from cache..." << endl;
00319         return toid;
00320     }
00321     else
00322     {
00323         otable = table;
00324     }
00325 
00326     try
00327     {
00328         statement = "SELECT relfilenode FROM pg_class WHERE (relname = '";
00329         statement += table;
00330         statement += "')";
00331 
00332         tran = new pqxx::nontransaction(*m_conn, "find_t_oid");
00333         tmpres = new pqxx::result(tran->exec(statement.latin1()));
00334 
00335         tran->commit();
00336         if (tmpres->size() > 0)
00337         {
00338             //We have a key field for this table, lets check if its this column
00339             tmpres->at(0).at(0).to(toid);
00340         }
00341         else
00342         {
00343             toid = 0;
00344         }
00345     }
00346     catch(const std::exception &e)
00347     {
00348         kdDebug() << "pqxxSqlDB::tableOid:exception - " << e.what() << endl;
00349         kdDebug() << "pqxxSqlDB::tableOid:failed statement - " << statement << endl;
00350         toid = 0;
00351     }
00352     catch(...)
00353     {
00354         kdDebug() << "PqxxMigrate::tableOid:exception(...)??" << endl;
00355     }
00356     delete tmpres;
00357     tmpres = 0;
00358     
00359     delete tran;
00360     tran = 0;
00361 
00362     kdDebug() << "OID for table [" << table << "] is [" << toid << "]" << endl;
00363     return toid;
00364 }
00365 
00366 //=========================================================================
00367 //Return whether or not the curent field is a primary key
00368 //TODO: Add result caching for speed
00369 bool PqxxMigrate::primaryKey(pqxx::oid table_uid, int col) const
00370 {
00371     QString statement;
00372     bool pkey;
00373     int keyf;
00374 
00375     pqxx::nontransaction* tran = 0;
00376     pqxx::result* tmpres = 0;
00377 
00378     try
00379     {
00380         statement = QString("SELECT indkey FROM pg_index WHERE ((indisprimary = true) AND (indrelid = %1))").arg(table_uid);
00381 
00382         tran = new pqxx::nontransaction(*m_conn, "find_pkey");
00383         tmpres = new pqxx::result(tran->exec(statement.latin1()));
00384 
00385         tran->commit();
00386         if (tmpres->size() > 0)
00387         {
00388             //We have a key field for this table, lets check if its this column
00389             tmpres->at(0).at(0).to(keyf);
00390             if (keyf-1 == col) //-1 because pg counts from 1 and we count from 0
00391             {
00392                 pkey = true;
00393                 kdDebug() << "Field is pkey" << endl;
00394             }
00395             else
00396             {
00397                 pkey = false;
00398                 kdDebug() << "Field is NOT pkey" << endl;
00399             }
00400         }
00401         else
00402         {
00403             pkey = false;
00404             kdDebug() << "Field is NOT pkey" << endl;
00405         }
00406     }
00407     catch(const std::exception &e)
00408     {
00409         kdDebug() << "pqxxSqlDB::primaryKey:exception - " << e.what() << endl;
00410         kdDebug() << "pqxxSqlDB::primaryKey:failed statement - " << statement << endl;
00411         pkey = false;
00412     }
00413     delete tmpres;
00414     tmpres = 0;
00415     
00416     delete tran;
00417     tran = 0;
00418 
00419     return pkey;
00420 }
00421 
00422 //=========================================================================
00427 tristate PqxxMigrate::drv_queryStringListFromSQL(
00428     const QString& sqlStatement, uint columnNumber, QStringList& stringList, int numRecords)
00429 {
00430     std::string result;
00431     int i = 0;
00432     if (query(sqlStatement))
00433     {
00434         for (pqxx::result::const_iterator it = m_res->begin(); 
00435           it != m_res->end() && (numRecords == -1 || i < numRecords); ++it, i++)
00436         {
00437             if (it.size() > 0 && it.size() > columnNumber) {
00438                 it.at(columnNumber).to(result);
00439                 stringList.append( QString::fromUtf8(result.c_str()) );
00440             }
00441             else {
00442                 clearResultInfo();
00443                 return cancelled;
00444             }
00445         }
00446     }
00447     else
00448         return false;
00449     clearResultInfo();
00450 /*    delete tmpres;
00451     tmpres = 0;
00452     
00453     delete tran;
00454     tran = 0;*/
00455 
00456     if (i < numRecords)
00457         return cancelled;
00458 
00459     return true;
00460     /*
00461     if (d->executeSQL(sqlStatement)) {
00462         MYSQL_RES *res = mysql_use_result(d->mysql);
00463         if (res != NULL) {
00464             MYSQL_ROW row = mysql_fetch_row(res);
00465             if (!row) {
00466                 tristate r = mysql_errno(d->mysql) ? false : cancelled;
00467                 mysql_free_result(res);
00468                 return r;
00469             }
00470             uint numFields = mysql_num_fields(res);
00471             if (columnNumber > (numFields-1)) {
00472                 kdWarning() << "PqxxMigrate::drv_querySingleStringFromSQL("<<sqlStatement
00473                     << "): columnNumber too large (" 
00474                     << columnNumber << "), expected 0.." << numFields << endl;
00475                 mysql_free_result(res);
00476                 return false;
00477             }
00478             unsigned long *lengths = mysql_fetch_lengths(res);
00479             if (!lengths) {
00480                 mysql_free_result(res);
00481                 return false;
00482             }
00483             string = QString::fromLatin1(row[columnNumber], lengths[columnNumber]);
00484             mysql_free_result(res);
00485         } else {
00486             kdDebug() << "PqxxMigrate::drv_querySingleStringFromSQL(): null result" << endl;
00487         }
00488         return true;
00489     } else {
00490         return false;
00491     }*/
00492 }
00493 
00494 tristate PqxxMigrate::drv_fetchRecordFromSQL(const QString& sqlStatement, 
00495     KexiDB::RowData& data, bool &firstRecord)
00496 {
00497     if (firstRecord || !m_res) {
00498         if (m_res)
00499             clearResultInfo();
00500         if (!query(sqlStatement))
00501             return false;
00502         m_fetchRecordFromSQL_iter = m_res->begin();
00503         firstRecord = false;
00504     }
00505     else
00506         ++m_fetchRecordFromSQL_iter;
00507 
00508     if (m_fetchRecordFromSQL_iter == m_res->end()) {
00509         clearResultInfo();
00510         return cancelled;
00511     }
00512 
00513     std::string result;
00514     const int numFields = m_fetchRecordFromSQL_iter.size();
00515     data.resize(numFields);
00516     for (int i=0; i < numFields; i++)
00517         data[i] = KexiDB::pgsqlCStrToVariant(m_fetchRecordFromSQL_iter.at(i));
00518     return true;
00519 }
00520 
00521 //=========================================================================
00523 bool PqxxMigrate::drv_copyTable(const QString& srcTable, KexiDB::Connection *destConn,
00524     KexiDB::TableSchema* dstTable)
00525 {
00526     std::vector<std::string> R;
00527 
00528     pqxx::work T(*m_conn, "PqxxMigrate::drv_copyTable");
00529 
00530     pqxx::tablereader stream(T, (srcTable.latin1()));
00531 
00532     //Loop round each row, reading into a vector of strings
00533     const KexiDB::QueryColumnInfo::Vector fieldsExpanded( dstTable->query()->fieldsExpanded() );
00534     for (int n=0; (stream >> R); ++n)
00535     {
00536         QValueList<QVariant> vals;
00537         std::vector<std::string>::const_iterator i, end( R.end() );
00538         int index = 0;
00539         for ( i = R.begin(); i != end; ++i, index++) {
00540              if (fieldsExpanded.at(index)->field->type()==KexiDB::Field::BLOB || fieldsExpanded.at(index)->field->type()==KexiDB::Field::LongText)
00541                   vals.append( KexiDB::pgsqlByteaToByteArray((*i).c_str(), (*i).size()) );
00542              else
00543                   vals.append( KexiDB::cstringToVariant((*i).c_str(), 
00544                     fieldsExpanded.at(index)->field, (*i).size()) );
00545         }
00546         if (!destConn->insertRecord(*dstTable, vals))
00547              return false;
00548         updateProgress();
00549         R.clear();
00550     }
00551 
00552     //This does not work in <libpqxx 2.2
00553     //stream.complete();
00554 
00555     return true;
00556 }
00557 
00558 //=========================================================================
00559 //Return whether or not the curent field is a primary key
00560 //TODO: Add result caching for speed
00561 bool PqxxMigrate::uniqueKey(pqxx::oid table_uid, int col) const
00562 {
00563     QString statement;
00564     bool ukey;
00565     int keyf;
00566 
00567     pqxx::nontransaction* tran = 0;
00568     pqxx::result* tmpres = 0;
00569 
00570     try
00571     {
00572         statement = QString("SELECT indkey FROM pg_index WHERE ((indisunique = true) AND (indrelid = %1))").arg(table_uid);
00573 
00574         tran = new pqxx::nontransaction(*m_conn, "find_ukey");
00575         tmpres = new pqxx::result(tran->exec(statement.latin1()));
00576 
00577         tran->commit();
00578         if (tmpres->size() > 0)
00579         {
00580             //We have a key field for this table, lets check if its this column
00581             tmpres->at(0).at(0).to(keyf);
00582             if (keyf-1 == col) //-1 because pg counts from 1 and we count from 0
00583             {
00584                 ukey = true;
00585                 kdDebug() << "Field is unique" << endl;
00586             }
00587             else
00588             {
00589                 ukey = false;
00590                 kdDebug() << "Field is NOT unique" << endl;
00591             }
00592         }
00593         else
00594         {
00595             ukey = false;
00596             kdDebug() << "Field is NOT unique" << endl;
00597         }
00598     }
00599     catch(const std::exception &e)
00600     {
00601         kdDebug() << "uniqueKey:exception - " << e.what() << endl;
00602         kdDebug() << "uniqueKey:failed statement - " << statement << endl;
00603         ukey = false;
00604     }
00605     
00606     delete tmpres;
00607     tmpres = 0;
00608     
00609     delete tran;
00610     tran = 0;
00611     
00612     return ukey;
00613 }
00614 
00615 //==================================================================================
00616 //TODO::Implement
00617 bool PqxxMigrate::autoInc(pqxx::oid /*table_uid*/, int /*col*/) const
00618 {
00619     return false;
00620 }
00621 
00622 //==================================================================================
00623 //TODO::Implement
00624 bool PqxxMigrate::notNull(pqxx::oid /*table_uid*/, int /*col*/) const
00625 {
00626     return false;
00627 }
00628 
00629 //==================================================================================
00630 //TODO::Implement
00631 bool PqxxMigrate::notEmpty(pqxx::oid /*table_uid*/, int /*col*/) const
00632 {
00633     return false;
00634 }
00635 
00636 //==================================================================================
00637 //Return a list of database names
00638 /*bool PqxxMigrate::drv_getDatabasesList( QStringList &list )
00639 {
00640     KexiDBDrvDbg << "pqxxSqlConnection::drv_getDatabaseList" << endl;
00641  
00642     if (executeSQL("SELECT datname FROM pg_database WHERE datallowconn = TRUE"))
00643     {
00644         std::string N;
00645         for (pqxx::result::const_iterator c = m_res->begin(); c != m_res->end(); ++c)
00646         {
00647             // Read value of column 0 into a string N
00648             c[0].to(N);
00649             // Copy the result into the return list
00650             list << QString::fromLatin1 (N.c_str());
00651         KexiDBDrvDbg << N.c_str() << endl;
00652         }
00653         return true;
00654     }
00655  
00656     return false;
00657 }*/
00658 
00659 
00660 #include "pqxxmigrate.moc"
KDE Home | KDE Accessibility Home | Description of Access Keys