kexi

pqxxmigrate.cpp

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