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  
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 "mysqlmigrate.h"
00021 
00022 #include <qstring.h>
00023 #include <qregexp.h>
00024 #include <qfile.h>
00025 #include <qvariant.h>
00026 #include <qvaluelist.h>
00027 #include <kdebug.h>
00028 
00029 #include <mysql_version.h>
00030 #include <mysql.h>
00031 
00032 #include "migration/keximigratedata.h"
00033 #include <kexidb/cursor.h>
00034 #include <kexidb/field.h>
00035 #include <kexidb/drivers/mySQL/mysqlconnection_p.cpp>
00036 #include <kexiutils/identifier.h>
00037 
00038 using namespace KexiMigration;
00039 
00040 /* This is the implementation for the MySQL specific import routines. */
00041 
00042 KEXIMIGRATE_DRIVER_INFO( MySQLMigrate, mysql )
00043 
00044 /* ************************************************************************** */
00046 /*MySQLMigrate::MySQLMigrate() :
00047     d(new MySqlConnectionInternal())
00048 {
00049 }*/
00050 
00051 
00052 MySQLMigrate::MySQLMigrate(QObject *parent, const char *name,
00053                            const QStringList &args) :
00054     KexiMigrate(parent, name, args),
00055     d(new MySqlConnectionInternal(0)) {
00056 }
00057 
00058 /* ************************************************************************** */
00060 MySQLMigrate::~MySQLMigrate() {
00061 }
00062 
00063 
00064 /* ************************************************************************** */
00066 bool MySQLMigrate::drv_connect() {
00067     if(d->db_connect(*m_migrateData->source)) {
00068         return d->useDatabase(m_migrateData->sourceName);
00069     } else {
00070         return false;
00071     }
00072 }
00073 
00074 
00076 bool MySQLMigrate::drv_disconnect()
00077 {
00078     return d->db_disconnect();
00079 }
00080 
00081 
00082 /* ************************************************************************** */
00084 bool MySQLMigrate::drv_readTableSchema(
00085     const QString& originalName, KexiDB::TableSchema& tableSchema)
00086 {
00087 //  m_table = new KexiDB::TableSchema(table);
00088 
00089 //  //TODO IDEA: ask for user input for captions
00090 //  tableSchema.setCaption(table + " table");
00091 
00092     //Perform a query on the table to get some data
00093     QString query = QString("SELECT * FROM `") + d->escapeIdentifier(originalName) + "` LIMIT 0";
00094     if(d->executeSQL(query)) {
00095         MYSQL_RES *res = mysql_store_result(d->mysql);
00096         if (res != NULL) {
00097         
00098             unsigned int numFlds = mysql_num_fields(res);
00099             MYSQL_FIELD *fields = mysql_fetch_fields(res); 
00100             
00101             for(unsigned int i = 0; i < numFlds; i++) {
00102                 QString fldName(fields[i].name);
00103                 QString fldID( KexiUtils::string2Identifier(fldName) );
00104 
00105                 KexiDB::Field *fld = 
00106                   new KexiDB::Field(fldID, type(originalName, &fields[i]));
00107                 
00108                 if(fld->type() == KexiDB::Field::Enum) {
00109                     QStringList values = examineEnumField(originalName, &fields[i]);
00110                 }
00111                 
00112                 fld->setCaption(fldName);
00113                 getConstraints(fields[i].flags, fld);
00114                 getOptions(fields[i].flags, fld);
00115                 tableSchema.addField(fld);
00116             }
00117             mysql_free_result(res);
00118         } else {
00119             kdDebug() << "MySQLMigrate::drv_tableNames: null result" << endl;
00120         }
00121         return true;
00122     } else {
00123       return false;
00124     }
00125 }
00126 
00127 
00129 bool MySQLMigrate::drv_tableNames(QStringList& tableNames)
00130 {
00131     if(d->executeSQL("SHOW TABLES")) {
00132         MYSQL_RES *res = mysql_store_result(d->mysql);
00133         if (res != NULL) {
00134             MYSQL_ROW row;
00135             while ((row = mysql_fetch_row(res)) != NULL) {
00136               tableNames << QString(row[0]);
00137             }
00138             mysql_free_result(res);
00139         } else {
00140             kdDebug() << "MySQLMigrate::drv_tableNames: null result" << endl;
00141         }
00142         return true;
00143     } else {
00144       return false;
00145     }
00146 }
00147 
00148 
00150 bool MySQLMigrate::drv_copyTable(const QString& srcTable, KexiDB::Connection *destConn, 
00151     KexiDB::TableSchema* dstTable)
00152 {
00153     if(d->executeSQL("SELECT * FROM " + d->escapeIdentifier(srcTable))) {
00154         MYSQL_RES *res = mysql_use_result(d->mysql);
00155         if (res != NULL) {
00156             MYSQL_ROW row;
00157             while ((row = mysql_fetch_row(res)) != NULL) {
00158                 int numFields = mysql_num_fields(res);
00159                 QValueList<QVariant> vals = QValueList<QVariant>();
00160                 for(int i = 0; i < numFields; i++) {
00161                     QVariant var = QVariant(row[i]);
00162                     vals << var;
00163                 }
00164                 destConn->insertRecord(*dstTable, vals);
00165                 updateProgress();
00166             }
00168             mysql_free_result(res);
00169         } else {
00170             kdDebug() << "MySQLMigrate::drv_copyTable: null result" << endl;
00171         }
00172         return true;
00173     } else {
00174         return false;
00175     }
00176 }
00177 
00178 
00179 bool MySQLMigrate::drv_getTableSize(const QString& table, Q_ULLONG& size) {
00180     if(d->executeSQL("SELECT COUNT(*) FROM " + d->escapeIdentifier(table))) {
00181         MYSQL_RES *res = mysql_store_result(d->mysql);
00182         if (res != NULL) {
00183             MYSQL_ROW row;
00184             while ((row = mysql_fetch_row(res)) != NULL) {
00186                 size = QString(row[0]).toULongLong();
00187             }
00188             mysql_free_result(res);
00189         } else {
00190             kdDebug() << "MySQLMigrate::drv_getTableSize: null result" << endl;
00191         }
00192         return true;
00193     } else {
00194       return false;
00195     }
00196 }
00197 
00199 KexiDB::Field::Type MySQLMigrate::type(const QString& table,
00200                                        const MYSQL_FIELD *fld)
00201 {
00202   // Field type
00203     KexiDB::Field::Type kexiType = KexiDB::Field::InvalidType;
00204 
00205     switch(fld->type)
00206     {
00207   // These are in the same order as mysql_com.h.
00208     // MySQL names given on the right
00209         case FIELD_TYPE_DECIMAL:    // DECIMAL or NUMERIC
00210             break;
00211         case FIELD_TYPE_TINY:       // TINYINT (-2^7..2^7-1 or 2^8)
00212             kexiType = KexiDB::Field::Byte;
00213             break;
00214         case FIELD_TYPE_SHORT:      // SMALLINT (-2^15..2^15-1 or 2^16)
00215             kexiType = KexiDB::Field::ShortInteger;
00216             break;
00217         case FIELD_TYPE_LONG:       // INTEGER (-2^31..2^31-1 or 2^32)
00218             kexiType = KexiDB::Field::Integer;
00219             break;
00220         case FIELD_TYPE_FLOAT:      // FLOAT
00221             kexiType = KexiDB::Field::Float;
00222             break;
00223         case FIELD_TYPE_DOUBLE:     // DOUBLE or REAL (8 byte)
00224             kexiType = KexiDB::Field::Double;
00225             break;
00226         case FIELD_TYPE_NULL:       // WTF?
00227             break;
00228         case FIELD_TYPE_TIMESTAMP:  // TIMESTAMP (promote?)
00229             kexiType = KexiDB::Field::DateTime;
00230             break;
00231         case FIELD_TYPE_LONGLONG:   // BIGINT (-2^63..2^63-1 or 2^64)
00232         case FIELD_TYPE_INT24:      // MEDIUMINT (-2^23..2^23-1 or 2^24) (promote)
00233             kexiType = KexiDB::Field::BigInteger;
00234             break;
00235         case FIELD_TYPE_DATE:       // DATE
00236             kexiType = KexiDB::Field::Date;
00237             break;
00238         case FIELD_TYPE_TIME:       // TIME
00239             kexiType = KexiDB::Field::Time;
00240             break;
00241         case FIELD_TYPE_DATETIME:   // DATETIME
00242             kexiType = KexiDB::Field::DateTime;
00243             break;
00244         case FIELD_TYPE_YEAR:       // YEAR (promote)
00245             kexiType = KexiDB::Field::ShortInteger;
00246             break;
00247         case FIELD_TYPE_NEWDATE:    // WTF?
00248         case FIELD_TYPE_ENUM:       // ENUM
00249             // If MySQL did what it's documentation said it did, we would come here
00250             // for enum fields ...
00251             kexiType = KexiDB::Field::Enum;
00252             break;
00253         case FIELD_TYPE_SET:        // SET
00255             break;
00256         case FIELD_TYPE_TINY_BLOB:
00257         case FIELD_TYPE_MEDIUM_BLOB:
00258         case FIELD_TYPE_LONG_BLOB:
00259         case FIELD_TYPE_BLOB:       // BLOB or TEXT
00260         case FIELD_TYPE_VAR_STRING: // VARCHAR
00261         case FIELD_TYPE_STRING:     // CHAR
00262             
00263             if (fld->flags & ENUM_FLAG) {
00264                 // ... instead we come here, using the ENUM_FLAG which is supposed to
00265                 // be deprecated! Duh.
00266                 kexiType = KexiDB::Field::Enum;
00267                 break;
00268             }
00269             kexiType = examineBlobField(table, fld);
00270             break;  
00271         default:
00272           kexiType = KexiDB::Field::InvalidType;
00273     }
00274 
00275     if (kexiType == KexiDB::Field::InvalidType) {
00276         return userType(table);
00277     }
00278     return kexiType;
00279 }
00280 
00281 
00283 
00290 KexiDB::Field::Type MySQLMigrate::examineBlobField(const QString& table,
00291     const MYSQL_FIELD* fld) {
00292     QString mysqlType;
00293     KexiDB::Field::Type kexiType;
00294     QString query = "SHOW COLUMNS FROM `" + d->escapeIdentifier(table) + 
00295                     "` LIKE '" + QString::fromLatin1(fld->name) + "'";
00296 
00297     if(d->executeSQL(query)) {
00298         MYSQL_RES *res = mysql_store_result(d->mysql);
00299 
00300         if (res != NULL) {
00301             MYSQL_ROW row;
00302             while ((row = mysql_fetch_row(res)) != NULL) {
00303                 mysqlType = QString(row[1]);
00304             }
00305             mysql_free_result(res);
00306         } else {
00307             kdDebug() << "MySQLMigrate::examineBlobField: null result" << endl;
00308         }
00309     } else {
00310         // Huh? MySQL wont tell us what kind of field it is! Lets guess.
00311       return KexiDB::Field::LongText;
00312     }
00313 
00314     kdDebug() << "MySQLMigrate::examineBlobField: considering "
00315               << mysqlType << endl;
00316     if(mysqlType.contains("blob", false) != 0) {
00317         // Doesn't matter how big it is, it's binary
00318         kexiType = KexiDB::Field::BLOB;
00319     } else if(mysqlType.contains("text", false) != 0) {
00320         // All the TEXT types are too big for Kexi text.
00321         kexiType = KexiDB::Field::BLOB;
00322     } else if(fld->length < 200) {
00323         kexiType = KexiDB::Field::Text;
00324     } else {
00325         kexiType = KexiDB::Field::LongText;
00326     }
00327     return kexiType;
00328 }
00329 
00330 
00332 
00339 QStringList MySQLMigrate::examineEnumField(const QString& table,
00340         const MYSQL_FIELD* fld) {
00341     QString vals;
00342     QString query = "SHOW COLUMNS FROM `" + d->escapeIdentifier(table) + 
00343             "` LIKE '" + QString::fromLatin1(fld->name) + "'";
00344 
00345     if(d->executeSQL(query)) {
00346         MYSQL_RES *res = mysql_store_result(d->mysql);
00347 
00348         if (res != NULL) {
00349             MYSQL_ROW row;
00350             while ((row = mysql_fetch_row(res)) != NULL) {
00351                 vals = QString(row[1]);
00352             }
00353             mysql_free_result(res);
00354         } else {
00355             kdDebug() << "MySQLMigrate::examineEnumField: null result" << endl;
00356         }
00357     } else {
00358         // Huh? MySQL wont tell us what values it can take.
00359         return QStringList();
00360     }
00361 
00362     kdDebug() << "MySQLMigrate::examineEnumField: considering " 
00363                         << vals << endl;
00364     
00365     // Crash and burn if we get confused...
00366     if(!vals.startsWith("enum(")) {
00367         // Huh? We're supposed to be parsing an enum!
00368         kdDebug() << "MySQLMigrate::examineEnumField:1 not an enum!" << endl;
00369         return QStringList();
00370     }
00371     if(!vals.endsWith(")")) {
00372         kdDebug() << "MySQLMigrate::examineEnumField:2 not an enum!" << endl;
00373         return QStringList();
00374     }
00375     
00376     // It'd be nice to use QString.section or QStringList.split, but we need
00377     // to be careful as enum values can have commas and quote marks in them
00378     // e.g. CREATE TABLE t(f enum('option,''') gives one option: "option,'"
00379     vals = vals.remove(0,5);
00380     QRegExp rx = QRegExp("^'((?:[^,']|,|'')*)'");
00381     QStringList values = QStringList();
00382     int index = 0;
00383 
00384     while ((index = rx.search(vals, index, QRegExp::CaretAtOffset)) != -1) {
00385         int len = rx.matchedLength();
00386         if (len != -1) {
00387             kdDebug() << "MySQLMigrate::examineEnumField:3 " << rx.cap(1) << endl;
00388             values << rx.cap(1);
00389         } else {
00390             kdDebug() << "MySQLMigrate::examineEnumField:4 lost" << endl;
00391         }
00392         
00393         QChar next = vals[index + len];
00394         if (next != QChar(',') && next != QChar(')')) {
00395             kdDebug() << "MySQLMigrate::examineEnumField:5 " << (char)next << endl;
00396         }
00397         index += len + 1;
00398     }
00399 
00400     return values;
00401 }
00402 
00403 
00404 void MySQLMigrate::getConstraints(int flags, KexiDB::Field* fld) {
00405     fld->setPrimaryKey(flags & PRI_KEY_FLAG);
00406     fld->setAutoIncrement(flags & AUTO_INCREMENT_FLAG);
00407     fld->setNotNull(flags & NOT_NULL_FLAG);
00408     fld->setUniqueKey(flags & UNIQUE_KEY_FLAG);
00410 }
00411 
00412 
00413 void MySQLMigrate::getOptions(int flags, KexiDB::Field* fld) {
00414     fld->setUnsigned(flags & UNSIGNED_FLAG);
00415 }
00416 
00417 
00418 #include "mysqlmigrate.moc"
KDE Home | KDE Accessibility Home | Description of Access Keys