00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
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
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>
00037
00038 using namespace KexiDB;
00039 using namespace KexiMigration;
00040
00041
00042
00043
00044
00045
00046
00047
00048 KEXIMIGRATE_DRIVER_INFO( PqxxMigrate, pqxx )
00049
00050
00051
00052
00053
00054
00055
00056
00057
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
00071 PqxxMigrate::~PqxxMigrate()
00072 {
00073 clearResultInfo();
00074 }
00075
00076
00077
00078
00079
00080 bool PqxxMigrate::drv_readTableSchema(
00081 const QString& originalName, KexiDB::TableSchema& tableSchema)
00082 {
00083
00084
00085
00086
00087
00088
00089 if (query("select * from \"" + originalName + "\" limit 1"))
00090 {
00091
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));
00105 tableSchema.addField(f);
00106
00107
00108
00109
00110
00111
00112
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
00127 bool PqxxMigrate::drv_tableNames(QStringList& tableNames)
00128 {
00129
00130
00131
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
00138 tableNames << QString::fromLatin1 (c[0].c_str());
00139 }
00140 return true;
00141 }
00142 else
00143 {
00144 return false;
00145 }
00146 }
00147
00148
00149
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
00187 return userType(fname);
00188 }
00189
00190
00191
00192 bool PqxxMigrate::drv_connect()
00193 {
00194 kdDebug() << "drv_connect: " << m_migrateData->sourceName << endl;
00195
00196 QString conninfo;
00197 QString socket;
00198
00199
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
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
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
00260 bool PqxxMigrate::query(const QString& statement)
00261 {
00262 kdDebug() << "query: " << statement.latin1() << endl;
00263
00264 Q_ASSERT (m_conn);
00265
00266
00267 clearResultInfo ();
00268
00269 try
00270 {
00271
00272 m_trans = new pqxx::nontransaction(*m_conn);
00273
00274 m_res = new pqxx::result(m_trans->exec(statement.latin1()));
00275
00276 m_trans->commit();
00277
00278 return true;
00279 }
00280 catch (const std::exception &e)
00281 {
00282
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
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
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
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
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
00368
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
00389 tmpres->at(0).at(0).to(keyf);
00390 if (keyf-1 == col)
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
00451
00452
00453
00454
00455
00456 if (i < numRecords)
00457 return cancelled;
00458
00459 return true;
00460
00461
00462
00463
00464
00465
00466
00467
00468
00469
00470
00471
00472
00473
00474
00475
00476
00477
00478
00479
00480
00481
00482
00483
00484
00485
00486
00487
00488
00489
00490
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
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
00553
00554
00555 return true;
00556 }
00557
00558
00559
00560
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
00581 tmpres->at(0).at(0).to(keyf);
00582 if (keyf-1 == col)
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
00617 bool PqxxMigrate::autoInc(pqxx::oid , int ) const
00618 {
00619 return false;
00620 }
00621
00622
00623
00624 bool PqxxMigrate::notNull(pqxx::oid , int ) const
00625 {
00626 return false;
00627 }
00628
00629
00630
00631 bool PqxxMigrate::notEmpty(pqxx::oid , int ) const
00632 {
00633 return false;
00634 }
00635
00636
00637
00638
00639
00640
00641
00642
00643
00644
00645
00646
00647
00648
00649
00650
00651
00652
00653
00654
00655
00656
00657
00658
00659
00660 #include "pqxxmigrate.moc"