00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021 #include "mysqlmigrate.h"
00022
00023 #include <qstring.h>
00024 #include <qregexp.h>
00025 #include <qfile.h>
00026 #include <qvariant.h>
00027 #include <qvaluelist.h>
00028 #include <kdebug.h>
00029
00030 #include <mysql_version.h>
00031 #include <mysql.h>
00032
00033 #include "migration/keximigratedata.h"
00034 #include <kexidb/cursor.h>
00035 #include <kexidb/field.h>
00036 #include <kexidb/utils.h>
00037 #include <kexidb/drivers/mySQL/mysqlconnection_p.cpp>
00038 #include <kexidb/drivermanager.h>
00039 #include <kexiutils/identifier.h>
00040
00041 using namespace KexiMigration;
00042
00043
00044
00045 KEXIMIGRATE_DRIVER_INFO( MySQLMigrate, mysql )
00046
00047
00049
00050
00051
00052
00053
00054
00055 MySQLMigrate::MySQLMigrate(QObject *parent, const char *name,
00056 const QStringList &args) :
00057 KexiMigrate(parent, name, args)
00058 ,d(new MySqlConnectionInternal(0))
00059 ,m_mysqlres(0)
00060 {
00061 KexiDB::DriverManager manager;
00062 m_kexiDBDriver = manager.driver("mysql");
00063 }
00064
00065
00067 MySQLMigrate::~MySQLMigrate() {
00068 if (m_mysqlres)
00069 mysql_free_result(m_mysqlres);
00070 m_mysqlres = 0;
00071 }
00072
00073
00074
00076 bool MySQLMigrate::drv_connect() {
00077 if(d->db_connect(*m_migrateData->source)) {
00078 return d->useDatabase(m_migrateData->sourceName);
00079 } else {
00080 return false;
00081 }
00082 }
00083
00084
00086 bool MySQLMigrate::drv_disconnect()
00087 {
00088 return d->db_disconnect();
00089 }
00090
00091
00092
00094 bool MySQLMigrate::drv_readTableSchema(
00095 const QString& originalName, KexiDB::TableSchema& tableSchema)
00096 {
00097
00098
00099
00100
00101
00102
00103 QString query = QString("SELECT * FROM `") + drv_escapeIdentifier(originalName) + "` LIMIT 0";
00104 if(d->executeSQL(query)) {
00105 MYSQL_RES *res = mysql_store_result(d->mysql);
00106 if (res != NULL) {
00107
00108 unsigned int numFlds = mysql_num_fields(res);
00109 MYSQL_FIELD *fields = mysql_fetch_fields(res);
00110
00111 for(unsigned int i = 0; i < numFlds; i++) {
00112 QString fldName(fields[i].name);
00113 QString fldID( KexiUtils::string2Identifier(fldName) );
00114
00115 KexiDB::Field *fld =
00116 new KexiDB::Field(fldID, type(originalName, &fields[i]));
00117
00118 if(fld->type() == KexiDB::Field::Enum) {
00119 QStringList values = examineEnumField(originalName, &fields[i]);
00120 }
00121
00122 fld->setCaption(fldName);
00123 getConstraints(fields[i].flags, fld);
00124 getOptions(fields[i].flags, fld);
00125 tableSchema.addField(fld);
00126 }
00127 mysql_free_result(res);
00128 } else {
00129 kdDebug() << "MySQLMigrate::drv_tableNames: null result" << endl;
00130 }
00131 return true;
00132 } else {
00133 return false;
00134 }
00135 }
00136
00137
00139 bool MySQLMigrate::drv_tableNames(QStringList& tableNames)
00140 {
00141 if(d->executeSQL("SHOW TABLES")) {
00142 MYSQL_RES *res = mysql_store_result(d->mysql);
00143 if (res != NULL) {
00144 MYSQL_ROW row;
00145 while ((row = mysql_fetch_row(res)) != NULL) {
00146 tableNames << QString::fromUtf8(row[0]);
00147 }
00148 mysql_free_result(res);
00149 } else {
00150 kdDebug() << "MySQLMigrate::drv_tableNames: null result" << endl;
00151 }
00152 return true;
00153 } else {
00154 return false;
00155 }
00156 }
00157
00162 tristate MySQLMigrate::drv_queryStringListFromSQL(
00163 const QString& sqlStatement, uint columnNumber, QStringList& stringList, int numRecords)
00164 {
00165 stringList.clear();
00166 if (d->executeSQL(sqlStatement)) {
00167 MYSQL_RES *res = mysql_use_result(d->mysql);
00168 if (res != NULL) {
00169 for (int i=0; numRecords == -1 || i < numRecords; i++) {
00170 MYSQL_ROW row = mysql_fetch_row(res);
00171 if (!row) {
00172 tristate r;
00173 if (mysql_errno(d->mysql))
00174 r = false;
00175 else
00176 r = (numRecords == -1) ? true : cancelled;
00177 mysql_free_result(res);
00178 return r;
00179 }
00180 uint numFields = mysql_num_fields(res);
00181 if (columnNumber > (numFields-1)) {
00182 kdWarning() << "MySQLMigrate::drv_querySingleStringFromSQL("<<sqlStatement
00183 << "): columnNumber too large ("
00184 << columnNumber << "), expected 0.." << numFields << endl;
00185 mysql_free_result(res);
00186 return false;
00187 }
00188 unsigned long *lengths = mysql_fetch_lengths(res);
00189 if (!lengths) {
00190 mysql_free_result(res);
00191 return false;
00192 }
00193 stringList.append( QString::fromUtf8(row[columnNumber], lengths[columnNumber]) );
00194 }
00195 mysql_free_result(res);
00196 } else {
00197 kdDebug() << "MySQLMigrate::drv_querySingleStringFromSQL(): null result" << endl;
00198 }
00199 return true;
00200 } else {
00201 return false;
00202 }
00203 }
00204
00207 tristate MySQLMigrate::drv_fetchRecordFromSQL(const QString& sqlStatement,
00208 KexiDB::RowData& data, bool &firstRecord)
00209 {
00210 if (firstRecord || !m_mysqlres) {
00211 if (m_mysqlres) {
00212 mysql_free_result(m_mysqlres);
00213 m_mysqlres = 0;
00214 }
00215 if (!d->executeSQL(sqlStatement) || !(m_mysqlres = mysql_use_result(d->mysql)))
00216 return false;
00217 firstRecord = false;
00218 }
00219
00220 MYSQL_ROW row = mysql_fetch_row(m_mysqlres);
00221 if (!row) {
00222 tristate r = cancelled;
00223 if (mysql_errno(d->mysql))
00224 r = false;
00225 mysql_free_result(m_mysqlres);
00226 m_mysqlres = 0;
00227 return r;
00228 }
00229 const int numFields = mysql_num_fields(m_mysqlres);
00230 unsigned long *lengths = mysql_fetch_lengths(m_mysqlres);
00231 if (!lengths) {
00232 mysql_free_result(m_mysqlres);
00233 m_mysqlres = 0;
00234 return false;
00235 }
00236 data.resize(numFields);
00237 for (int i=0; i < numFields; i++)
00238 data[i] = QString::fromUtf8(row[i], lengths[i] );
00239 return true;
00240 }
00241
00243 bool MySQLMigrate::drv_copyTable(const QString& srcTable, KexiDB::Connection *destConn,
00244 KexiDB::TableSchema* dstTable)
00245 {
00246 if(d->executeSQL("SELECT * FROM `" + drv_escapeIdentifier(srcTable)) + "`") {
00247 MYSQL_RES *res = mysql_use_result(d->mysql);
00248 if (res != NULL) {
00249 MYSQL_ROW row;
00250 const KexiDB::QueryColumnInfo::Vector fieldsExpanded( dstTable->query()->fieldsExpanded() );
00251 while ((row = mysql_fetch_row(res)) != NULL) {
00252 const int numFields = QMIN((int)fieldsExpanded.count(), (int)mysql_num_fields(res));
00253 QValueList<QVariant> vals;
00254 unsigned long *lengths = mysql_fetch_lengths(res);
00255 if (!lengths) {
00256 mysql_free_result(res);
00257 return false;
00258 }
00259 for(int i = 0; i < numFields; i++)
00260 vals.append( KexiDB::cstringToVariant(row[i], fieldsExpanded.at(i)->field, (int)lengths[i]) );
00261 if (!destConn->insertRecord(*dstTable, vals)) {
00262 mysql_free_result(res);
00263 return false;
00264 }
00265 updateProgress();
00266 }
00267 if (!row && mysql_errno(d->mysql)) {
00268 mysql_free_result(res);
00269 return false;
00270 }
00272 mysql_free_result(res);
00273 } else {
00274 kdDebug() << "MySQLMigrate::drv_copyTable: null result" << endl;
00275 }
00276 return true;
00277 } else {
00278 return false;
00279 }
00280 }
00281
00282
00283 bool MySQLMigrate::drv_getTableSize(const QString& table, Q_ULLONG& size) {
00284 if(d->executeSQL("SELECT COUNT(*) FROM `" + drv_escapeIdentifier(table)) + "`") {
00285 MYSQL_RES *res = mysql_store_result(d->mysql);
00286 if (res != NULL) {
00287 MYSQL_ROW row;
00288 while ((row = mysql_fetch_row(res)) != NULL) {
00290 size = QString(row[0]).toULongLong();
00291 }
00292 mysql_free_result(res);
00293 } else {
00294 kdDebug() << "MySQLMigrate::drv_getTableSize: null result" << endl;
00295 }
00296 return true;
00297 } else {
00298 return false;
00299 }
00300 }
00301
00303 KexiDB::Field::Type MySQLMigrate::type(const QString& table,
00304 const MYSQL_FIELD *fld)
00305 {
00306
00307 KexiDB::Field::Type kexiType = KexiDB::Field::InvalidType;
00308
00309 switch(fld->type)
00310 {
00311
00312
00313 case FIELD_TYPE_DECIMAL:
00314 break;
00315 case FIELD_TYPE_TINY:
00316 kexiType = KexiDB::Field::Byte;
00317 break;
00318 case FIELD_TYPE_SHORT:
00319 kexiType = KexiDB::Field::ShortInteger;
00320 break;
00321 case FIELD_TYPE_LONG:
00322 kexiType = KexiDB::Field::Integer;
00323 break;
00324 case FIELD_TYPE_FLOAT:
00325 kexiType = KexiDB::Field::Float;
00326 break;
00327 case FIELD_TYPE_DOUBLE:
00328 kexiType = KexiDB::Field::Double;
00329 break;
00330 case FIELD_TYPE_NULL:
00331 break;
00332 case FIELD_TYPE_TIMESTAMP:
00333 kexiType = KexiDB::Field::DateTime;
00334 break;
00335 case FIELD_TYPE_LONGLONG:
00336 case FIELD_TYPE_INT24:
00337 kexiType = KexiDB::Field::BigInteger;
00338 break;
00339 case FIELD_TYPE_DATE:
00340 kexiType = KexiDB::Field::Date;
00341 break;
00342 case FIELD_TYPE_TIME:
00343 kexiType = KexiDB::Field::Time;
00344 break;
00345 case FIELD_TYPE_DATETIME:
00346 kexiType = KexiDB::Field::DateTime;
00347 break;
00348 case FIELD_TYPE_YEAR:
00349 kexiType = KexiDB::Field::ShortInteger;
00350 break;
00351 case FIELD_TYPE_NEWDATE:
00352 case FIELD_TYPE_ENUM:
00353
00354
00355 kexiType = KexiDB::Field::Enum;
00356 break;
00357 case FIELD_TYPE_SET:
00359 break;
00360 case FIELD_TYPE_TINY_BLOB:
00361 case FIELD_TYPE_MEDIUM_BLOB:
00362 case FIELD_TYPE_LONG_BLOB:
00363 case FIELD_TYPE_BLOB:
00364 case FIELD_TYPE_VAR_STRING:
00365 case FIELD_TYPE_STRING:
00366
00367 if (fld->flags & ENUM_FLAG) {
00368
00369
00370 kexiType = KexiDB::Field::Enum;
00371 break;
00372 }
00373 kexiType = examineBlobField(table, fld);
00374 break;
00375 default:
00376 kexiType = KexiDB::Field::InvalidType;
00377 }
00378
00379 if (kexiType == KexiDB::Field::InvalidType) {
00380 return userType(table);
00381 }
00382 return kexiType;
00383 }
00384
00385
00387
00394 KexiDB::Field::Type MySQLMigrate::examineBlobField(const QString& table,
00395 const MYSQL_FIELD* fld) {
00396 QString mysqlType;
00397 KexiDB::Field::Type kexiType;
00398 QString query = "SHOW COLUMNS FROM `" + drv_escapeIdentifier(table) +
00399 "` LIKE '" + QString::fromLatin1(fld->name) + "'";
00400
00401 if(d->executeSQL(query)) {
00402 MYSQL_RES *res = mysql_store_result(d->mysql);
00403
00404 if (res != NULL) {
00405 MYSQL_ROW row;
00406 while ((row = mysql_fetch_row(res)) != NULL) {
00407 mysqlType = QString(row[1]);
00408 }
00409 mysql_free_result(res);
00410 } else {
00411 kdDebug() << "MySQLMigrate::examineBlobField: null result" << endl;
00412 }
00413 } else {
00414
00415 return KexiDB::Field::LongText;
00416 }
00417
00418 kdDebug() << "MySQLMigrate::examineBlobField: considering "
00419 << mysqlType << endl;
00420 if(mysqlType.contains("blob", false) != 0) {
00421
00422 kexiType = KexiDB::Field::BLOB;
00423 } else if(mysqlType.contains("text", false) != 0) {
00424
00425 kexiType = KexiDB::Field::BLOB;
00426 } else if(fld->length < 200) {
00427 kexiType = KexiDB::Field::Text;
00428 } else {
00429 kexiType = KexiDB::Field::LongText;
00430 }
00431 return kexiType;
00432 }
00433
00434
00436
00443 QStringList MySQLMigrate::examineEnumField(const QString& table,
00444 const MYSQL_FIELD* fld) {
00445 QString vals;
00446 QString query = "SHOW COLUMNS FROM `" + drv_escapeIdentifier(table) +
00447 "` LIKE '" + QString::fromLatin1(fld->name) + "'";
00448
00449 if(d->executeSQL(query)) {
00450 MYSQL_RES *res = mysql_store_result(d->mysql);
00451
00452 if (res != NULL) {
00453 MYSQL_ROW row;
00454 while ((row = mysql_fetch_row(res)) != NULL) {
00455 vals = QString(row[1]);
00456 }
00457 mysql_free_result(res);
00458 } else {
00459 kdDebug() << "MySQLMigrate::examineEnumField: null result" << endl;
00460 }
00461 } else {
00462
00463 return QStringList();
00464 }
00465
00466 kdDebug() << "MySQLMigrate::examineEnumField: considering "
00467 << vals << endl;
00468
00469
00470 if(!vals.startsWith("enum(")) {
00471
00472 kdDebug() << "MySQLMigrate::examineEnumField:1 not an enum!" << endl;
00473 return QStringList();
00474 }
00475 if(!vals.endsWith(")")) {
00476 kdDebug() << "MySQLMigrate::examineEnumField:2 not an enum!" << endl;
00477 return QStringList();
00478 }
00479
00480
00481
00482
00483 vals = vals.remove(0,5);
00484 QRegExp rx = QRegExp("^'((?:[^,']|,|'')*)'");
00485 QStringList values = QStringList();
00486 int index = 0;
00487
00488 while ((index = rx.search(vals, index, QRegExp::CaretAtOffset)) != -1) {
00489 int len = rx.matchedLength();
00490 if (len != -1) {
00491 kdDebug() << "MySQLMigrate::examineEnumField:3 " << rx.cap(1) << endl;
00492 values << rx.cap(1);
00493 } else {
00494 kdDebug() << "MySQLMigrate::examineEnumField:4 lost" << endl;
00495 }
00496
00497 QChar next = vals[index + len];
00498 if (next != QChar(',') && next != QChar(')')) {
00499 kdDebug() << "MySQLMigrate::examineEnumField:5 " << (char)next << endl;
00500 }
00501 index += len + 1;
00502 }
00503
00504 return values;
00505 }
00506
00507
00508 void MySQLMigrate::getConstraints(int flags, KexiDB::Field* fld) {
00509 fld->setPrimaryKey(flags & PRI_KEY_FLAG);
00510 fld->setAutoIncrement(flags & AUTO_INCREMENT_FLAG);
00511 fld->setNotNull(flags & NOT_NULL_FLAG);
00512 fld->setUniqueKey(flags & UNIQUE_KEY_FLAG);
00514 }
00515
00516
00517 void MySQLMigrate::getOptions(int flags, KexiDB::Field* fld) {
00518 fld->setUnsigned(flags & UNSIGNED_FLAG);
00519 }
00520
00521
00522 #include "mysqlmigrate.moc"