kexi
pqxxmigrate.cpp00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
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
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
00039
00040
00041
00042
00043
00044 KEXIMIGRATE_DRIVER_INFO( PqxxMigrate, pqxx )
00045
00046
00047
00048
00049
00050
00051
00052
00053
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
00065 PqxxMigrate::~PqxxMigrate()
00066 {
00067 }
00068
00069
00070
00071
00072
00073 bool PqxxMigrate::drv_readTableSchema(
00074 const QString& originalName, KexiDB::TableSchema& tableSchema)
00075 {
00076
00077
00078
00079
00080
00081
00082 if (query("select * from \"" + originalName + "\" limit 1"))
00083 {
00084
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));
00098 tableSchema.addField(f);
00099
00100
00101
00102
00103
00104
00105
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
00120 bool PqxxMigrate::drv_tableNames(QStringList& tableNames)
00121 {
00122
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
00128 tableNames << QString::fromLatin1 (c[0].c_str());
00129 }
00130 return true;
00131 }
00132 else
00133 {
00134 return false;
00135 }
00136 }
00137
00138
00139
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
00167
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
00175 }
00176
00177
00178 return userType(fname);
00179 }
00180
00181
00182
00183 bool PqxxMigrate::drv_connect()
00184 {
00185 kdDebug() << "drv_connect: " << m_migrateData->sourceName << endl;
00186
00187 QString conninfo;
00188 QString socket;
00189
00190
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
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
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
00251 bool PqxxMigrate::query (const QString& statement)
00252 {
00253 kdDebug() << "query: " << statement.latin1() << endl;
00254
00255 Q_ASSERT (m_conn);
00256
00257
00258 clearResultInfo ();
00259
00260 try
00261 {
00262
00263 m_trans = new pqxx::nontransaction(*m_conn);
00264
00265 m_res = new pqxx::result(m_trans->exec(statement.latin1()));
00266
00267 m_trans->commit();
00268
00269 return true;
00270 }
00271 catch (const std::exception &e)
00272 {
00273
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
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
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
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
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
00359
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
00380 tmpres->at(0).at(0).to(keyf);
00381 if (keyf-1 == col)
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
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
00440
00441
00442 return true;
00443 }
00444
00445
00446
00447
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
00468 tmpres->at(0).at(0).to(keyf);
00469 if (keyf-1 == col)
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
00504 bool PqxxMigrate::autoInc(pqxx::oid , int ) const
00505 {
00506 return false;
00507 }
00508
00509
00510
00511 bool PqxxMigrate::notNull(pqxx::oid , int ) const
00512 {
00513 return false;
00514 }
00515
00516
00517
00518 bool PqxxMigrate::notEmpty(pqxx::oid , int ) const
00519 {
00520 return false;
00521 }
00522
00523
00524
00525
00526
00527
00528
00529
00530
00531
00532
00533
00534
00535
00536
00537
00538
00539
00540
00541
00542
00543
00544
00545
00546
00547 #include "pqxxmigrate.moc"
|