kexi

mysqlmigrate.cpp

00001 /* This file is part of the KDE project
00002    Copyright (C) 2004 Martin Ellis <m.a.ellis@ncl.ac.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 "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 /* This is the implementation for the MySQL specific import routines. */
00044 
00045 KEXIMIGRATE_DRIVER_INFO( MySQLMigrate, mysql )
00046 
00047 /* ************************************************************************** */
00049 /*MySQLMigrate::MySQLMigrate() :
00050     d(new MySqlConnectionInternal())
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 //  m_table = new KexiDB::TableSchema(table);
00098 
00099 //  //TODO IDEA: ask for user input for captions
00100 //  tableSchema.setCaption(table + " table");
00101 
00102     //Perform a query on the table to get some data
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]); //utf8.. ok?
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]) ); //ok? utf8?
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] ); //ok? utf8?
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   // Field type
00307     KexiDB::Field::Type kexiType = KexiDB::Field::InvalidType;
00308 
00309     switch(fld->type)
00310     {
00311   // These are in the same order as mysql_com.h.
00312     // MySQL names given on the right
00313         case FIELD_TYPE_DECIMAL:    // DECIMAL or NUMERIC
00314             break;
00315         case FIELD_TYPE_TINY:       // TINYINT (-2^7..2^7-1 or 2^8)
00316             kexiType = KexiDB::Field::Byte;
00317             break;
00318         case FIELD_TYPE_SHORT:      // SMALLINT (-2^15..2^15-1 or 2^16)
00319             kexiType = KexiDB::Field::ShortInteger;
00320             break;
00321         case FIELD_TYPE_LONG:       // INTEGER (-2^31..2^31-1 or 2^32)
00322             kexiType = KexiDB::Field::Integer;
00323             break;
00324         case FIELD_TYPE_FLOAT:      // FLOAT
00325             kexiType = KexiDB::Field::Float;
00326             break;
00327         case FIELD_TYPE_DOUBLE:     // DOUBLE or REAL (8 byte)
00328             kexiType = KexiDB::Field::Double;
00329             break;
00330         case FIELD_TYPE_NULL:       // WTF?
00331             break;
00332         case FIELD_TYPE_TIMESTAMP:  // TIMESTAMP (promote?)
00333             kexiType = KexiDB::Field::DateTime;
00334             break;
00335         case FIELD_TYPE_LONGLONG:   // BIGINT (-2^63..2^63-1 or 2^64)
00336         case FIELD_TYPE_INT24:      // MEDIUMINT (-2^23..2^23-1 or 2^24) (promote)
00337             kexiType = KexiDB::Field::BigInteger;
00338             break;
00339         case FIELD_TYPE_DATE:       // DATE
00340             kexiType = KexiDB::Field::Date;
00341             break;
00342         case FIELD_TYPE_TIME:       // TIME
00343             kexiType = KexiDB::Field::Time;
00344             break;
00345         case FIELD_TYPE_DATETIME:   // DATETIME
00346             kexiType = KexiDB::Field::DateTime;
00347             break;
00348         case FIELD_TYPE_YEAR:       // YEAR (promote)
00349             kexiType = KexiDB::Field::ShortInteger;
00350             break;
00351         case FIELD_TYPE_NEWDATE:    // WTF?
00352         case FIELD_TYPE_ENUM:       // ENUM
00353             // If MySQL did what it's documentation said it did, we would come here
00354             // for enum fields ...
00355             kexiType = KexiDB::Field::Enum;
00356             break;
00357         case FIELD_TYPE_SET:        // 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:       // BLOB or TEXT
00364         case FIELD_TYPE_VAR_STRING: // VARCHAR
00365         case FIELD_TYPE_STRING:     // CHAR
00366             
00367             if (fld->flags & ENUM_FLAG) {
00368                 // ... instead we come here, using the ENUM_FLAG which is supposed to
00369                 // be deprecated! Duh.
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         // Huh? MySQL wont tell us what kind of field it is! Lets guess.
00415       return KexiDB::Field::LongText;
00416     }
00417 
00418     kdDebug() << "MySQLMigrate::examineBlobField: considering "
00419               << mysqlType << endl;
00420     if(mysqlType.contains("blob", false) != 0) {
00421         // Doesn't matter how big it is, it's binary
00422         kexiType = KexiDB::Field::BLOB;
00423     } else if(mysqlType.contains("text", false) != 0) {
00424         // All the TEXT types are too big for Kexi text.
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         // Huh? MySQL wont tell us what values it can take.
00463         return QStringList();
00464     }
00465 
00466     kdDebug() << "MySQLMigrate::examineEnumField: considering " 
00467                         << vals << endl;
00468     
00469     // Crash and burn if we get confused...
00470     if(!vals.startsWith("enum(")) {
00471         // Huh? We're supposed to be parsing an enum!
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     // It'd be nice to use QString.section or QStringList.split, but we need
00481     // to be careful as enum values can have commas and quote marks in them
00482     // e.g. CREATE TABLE t(f enum('option,''') gives one option: "option,'"
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"
KDE Home | KDE Accessibility Home | Description of Access Keys