mysqlmigrate.cpp

/* This file is part of the KDE project
   Copyright (C) 2004 Martin Ellis <m.a.ellis@ncl.ac.uk>
   Copyright (C) 2006 Jaroslaw Staniek <js@iidea.pl>
 
   This program is free software; you can redistribute it and/or
   modify it under the terms of the GNU Library General Public
   License as published by the Free Software Foundation; either
   version 2 of the License, or (at your option) any later version.
 
   This program is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
   Library General Public License for more details.
 
   You should have received a copy of the GNU Library General Public License
   along with this program; see the file COPYING.  If not, write to
   the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
 * Boston, MA 02110-1301, USA.
*/
 
#include "mysqlmigrate.h"
 
#include <tqstring.h>
#include <tqregexp.h>
#include <tqfile.h>
#include <tqvariant.h>
#include <tqvaluelist.h>
#include <kdebug.h>
 
#include <mysql_version.h>
#include <mysql.h>
 
#include "migration/keximigratedata.h"
#include <kexidb/cursor.h>
#include <kexidb/field.h>
#include <kexidb/utils.h>
#include <kexidb/drivers/mySQL/mysqlconnection_p.cpp>
#include <kexidb/drivermanager.h>
#include <kexiutils/identifier.h>
 
using namespace KexiMigration;
 
/* This is the implementation for the MySQL specific import routines. */
 
KEXIMIGRATE_DRIVER_INFO( MySQLMigrate, mysql )
 
/* ************************************************************************** */
/*MySQLMigrate::MySQLMigrate() :
    d(new MySqlConnectionInternal())
{
}*/
 

MySQLMigrate::MySQLMigrate(TQObject *parent, const char *name,
                           const TQStringList &args) :
    KexiMigrate(parent, name, args)
    ,d(new MySqlConnectionInternal(0))
    ,m_mysqlres(0)
{
    KexiDB::DriverManager manager;
    m_kexiDBDriver = manager.driver("mysql");
}
 
/* ************************************************************************** */
MySQLMigrate::~MySQLMigrate() {
    if (m_mysqlres)
        mysql_free_result(m_mysqlres);
    m_mysqlres = 0;
}
 
 
/* ************************************************************************** */
bool MySQLMigrate::drv_connect() {
    if(d->db_connect(*m_migrateData->source)) {
        return d->useDatabase(m_migrateData->sourceName);
    } else {
        return false;
    }
}
 

bool MySQLMigrate::drv_disconnect()
{
    return d->db_disconnect();
}
 
 
/* ************************************************************************** */
bool MySQLMigrate::drv_readTableSchema(
    const TQString& originalName, KexiDB::TableSchema& tableSchema)
{
//  m_table = new KexiDB::TableSchema(table);
 
//  //TODO IDEA: ask for user input for captions
//  tableSchema.setCaption(table + " table");
 
    //Perform a query on the table to get some data
    TQString query = TQString("SELECT * FROM `") + drv_escapeIdentifier(originalName) + "` LIMIT 0";
    if(d->executeSQL(query)) {
        MYSQL_RES *res = mysql_store_result(d->mysql);
        if (res != NULL) {
        
            unsigned int numFlds = mysql_num_fields(res);
            MYSQL_FIELD *fields = mysql_fetch_fields(res); 
            
            for(unsigned int i = 0; i < numFlds; i++) {
                TQString fldName(fields[i].name);
                TQString fldID( KexiUtils::string2Identifier(fldName) );
 
                KexiDB::Field *fld = 
                  new KexiDB::Field(fldID, type(originalName, &fields[i]));
                
                if(fld->type() == KexiDB::Field::Enum) {
                    TQStringList values = examineEnumField(originalName, &fields[i]);
                }
                
                fld->setCaption(fldName);
                getConstraints(fields[i].flags, fld);
                getOptions(fields[i].flags, fld);
                tableSchema.addField(fld);
            }
            mysql_free_result(res);
        } else {
            kdDebug() << "MySQLMigrate::drv_tableNames: null result" << endl;
        }
        return true;
    } else {
      return false;
    }
}
 

bool MySQLMigrate::drv_tableNames(TQStringList& tableNames)
{
    if(d->executeSQL("SHOW TABLES")) {
        MYSQL_RES *res = mysql_store_result(d->mysql);
        if (res != NULL) {
            MYSQL_ROW row;
            while ((row = mysql_fetch_row(res)) != NULL) {
              tableNames << TQString::fromUtf8(row[0]); //utf8.. ok?
            }
            mysql_free_result(res);
        } else {
            kdDebug() << "MySQLMigrate::drv_tableNames: null result" << endl;
        }
        return true;
    } else {
      return false;
    }
}

tristate MySQLMigrate::drv_queryStringListFromSQL(
    const TQString& sqlStatement, uint columnNumber, TQStringList& stringList, int numRecords)
{
    stringList.clear();
    if (d->executeSQL(sqlStatement)) {
        MYSQL_RES *res = mysql_use_result(d->mysql);
        if (res != NULL) {
            for (int i=0; numRecords == -1 || i < numRecords; i++) {
                MYSQL_ROW row = mysql_fetch_row(res);
                if (!row) {
                    tristate r;
                    if (mysql_errno(d->mysql))
                        r = false;
                    else
                        r = (numRecords == -1) ? true : cancelled;
                    mysql_free_result(res);
                    return r;
                }
                uint numFields = mysql_num_fields(res);
                if (columnNumber > (numFields-1)) {
                    kdWarning() << "MySQLMigrate::drv_querySingleStringFromSQL("<<sqlStatement
                        << "): columnNumber too large (" 
                        << columnNumber << "), expected 0.." << numFields << endl;
                    mysql_free_result(res);
                    return false;
                }
                unsigned long *lengths = mysql_fetch_lengths(res);
                if (!lengths) {
                    mysql_free_result(res);
                    return false;
                }
                stringList.append( TQString::fromUtf8(row[columnNumber], lengths[columnNumber]) ); //ok? utf8?
            }
            mysql_free_result(res);
        } else {
            kdDebug() << "MySQLMigrate::drv_querySingleStringFromSQL(): null result" << endl;
        }
        return true;
    } else {
        return false;
    }
}

tristate MySQLMigrate::drv_fetchRecordFromSQL(const TQString& sqlStatement, 
    KexiDB::RowData& data, bool &firstRecord)
{
    if (firstRecord || !m_mysqlres) {
        if (m_mysqlres) {
            mysql_free_result(m_mysqlres);
            m_mysqlres = 0;
        }
        if (!d->executeSQL(sqlStatement) || !(m_mysqlres = mysql_use_result(d->mysql)))
            return false;
        firstRecord = false;
    }
 
    MYSQL_ROW row = mysql_fetch_row(m_mysqlres);
    if (!row) {
        tristate r = cancelled;
        if (mysql_errno(d->mysql))
            r = false;
        mysql_free_result(m_mysqlres);
        m_mysqlres = 0;
        return r;
    }
    const int numFields = mysql_num_fields(m_mysqlres);
    unsigned long *lengths = mysql_fetch_lengths(m_mysqlres);
    if (!lengths) {
        mysql_free_result(m_mysqlres);
        m_mysqlres = 0;
        return false;
    }
    data.resize(numFields);
    for (int i=0; i < numFields; i++)
        data[i] = TQString::fromUtf8(row[i], lengths[i] ); //ok? utf8?
    return true;
}

bool MySQLMigrate::drv_copyTable(const TQString& srcTable, KexiDB::Connection *destConn, 
    KexiDB::TableSchema* dstTable)
{
    if(d->executeSQL("SELECT * FROM `" + drv_escapeIdentifier(srcTable)) + "`") {
        MYSQL_RES *res = mysql_use_result(d->mysql);
        if (res != NULL) {
            MYSQL_ROW row;
            const KexiDB::QueryColumnInfo::Vector fieldsExpanded( dstTable->query()->fieldsExpanded() );
            while ((row = mysql_fetch_row(res)) != NULL) {
                const int numFields = TQMIN((int)fieldsExpanded.count(), (int)mysql_num_fields(res));
                TQValueList<TQVariant> vals;
                unsigned long *lengths = mysql_fetch_lengths(res);
                if (!lengths) {
                    mysql_free_result(res);
                    return false;
                }
                for(int i = 0; i < numFields; i++)
                    vals.append( KexiDB::cstringToVariant(row[i], fieldsExpanded.at(i)->field, (int)lengths[i]) );
                if (!destConn->insertRecord(*dstTable, vals)) {
                    mysql_free_result(res);
                    return false;
                }
                updateProgress();
            }
            if (!row && mysql_errno(d->mysql)) {
                mysql_free_result(res);
                return false;
            }
            mysql_free_result(res);
        } else {
            kdDebug() << "MySQLMigrate::drv_copyTable: null result" << endl;
        }
        return true;
    } else {
        return false;
    }
}
 
 
bool MySQLMigrate::drv_getTableSize(const TQString& table, TQ_ULLONG& size) {
    if(d->executeSQL("SELECT COUNT(*) FROM `" + drv_escapeIdentifier(table)) + "`") {
        MYSQL_RES *res = mysql_store_result(d->mysql);
        if (res != NULL) {
            MYSQL_ROW row;
            while ((row = mysql_fetch_row(res)) != NULL) {
                size = TQString(row[0]).toULongLong();
            }
            mysql_free_result(res);
        } else {
            kdDebug() << "MySQLMigrate::drv_getTableSize: null result" << endl;
        }
        return true;
    } else {
      return false;
    }
}

KexiDB::Field::Type MySQLMigrate::type(const TQString& table,
                                       const MYSQL_FIELD *fld)
{
  // Field type
    KexiDB::Field::Type kexiType = KexiDB::Field::InvalidType;
 
    switch(fld->type)
    {
  // These are in the same order as mysql_com.h.
    // MySQL names given on the right
        case FIELD_TYPE_DECIMAL:    // DECIMAL or NUMERIC
            break;
        case FIELD_TYPE_TINY:       // TINYINT (-2^7..2^7-1 or 2^8)
            kexiType = KexiDB::Field::Byte;
            break;
        case FIELD_TYPE_SHORT:      // SMALLINT (-2^15..2^15-1 or 2^16)
            kexiType = KexiDB::Field::ShortInteger;
            break;
        case FIELD_TYPE_LONG:       // INTEGER (-2^31..2^31-1 or 2^32)
            kexiType = KexiDB::Field::Integer;
            break;
        case FIELD_TYPE_FLOAT:      // FLOAT
            kexiType = KexiDB::Field::Float;
            break;
        case FIELD_TYPE_DOUBLE:     // DOUBLE or REAL (8 byte)
            kexiType = KexiDB::Field::Double;
            break;
        case FIELD_TYPE_NULL:       // WTF?
            break;
        case FIELD_TYPE_TIMESTAMP:  // TIMESTAMP (promote?)
            kexiType = KexiDB::Field::DateTime;
            break;
        case FIELD_TYPE_LONGLONG:   // BIGINT (-2^63..2^63-1 or 2^64)
        case FIELD_TYPE_INT24:      // MEDIUMINT (-2^23..2^23-1 or 2^24) (promote)
            kexiType = KexiDB::Field::BigInteger;
            break;
        case FIELD_TYPE_DATE:       // DATE
            kexiType = KexiDB::Field::Date;
            break;
        case FIELD_TYPE_TIME:       // TIME
            kexiType = KexiDB::Field::Time;
            break;
        case FIELD_TYPE_DATETIME:   // DATETIME
            kexiType = KexiDB::Field::DateTime;
            break;
        case FIELD_TYPE_YEAR:       // YEAR (promote)
            kexiType = KexiDB::Field::ShortInteger;
            break;
        case FIELD_TYPE_NEWDATE:    // WTF?
        case FIELD_TYPE_ENUM:       // ENUM
            // If MySQL did what it's documentation said it did, we would come here
            // for enum fields ...
            kexiType = KexiDB::Field::Enum;
            break;
        case FIELD_TYPE_SET:        // SET
            break;
        case FIELD_TYPE_TINY_BLOB:
        case FIELD_TYPE_MEDIUM_BLOB:
        case FIELD_TYPE_LONG_BLOB:
        case FIELD_TYPE_BLOB:       // BLOB or TEXT
        case FIELD_TYPE_VAR_STRING: // VARCHAR
        case FIELD_TYPE_STRING:     // CHAR
            
            if (fld->flags & ENUM_FLAG) {
                // ... instead we come here, using the ENUM_FLAG which is supposed to
                // be deprecated! Duh.
                kexiType = KexiDB::Field::Enum;
                break;
            }
            kexiType = examineBlobField(table, fld);
            break;  
        default:
          kexiType = KexiDB::Field::InvalidType;
    }
 
    if (kexiType == KexiDB::Field::InvalidType) {
        return userType(table);
    }
    return kexiType;
}
 


KexiDB::Field::Type MySQLMigrate::examineBlobField(const TQString& table,
    const MYSQL_FIELD* fld) {
    TQString mysqlType;
    KexiDB::Field::Type kexiType;
    TQString query = "SHOW COLUMNS FROM `" + drv_escapeIdentifier(table) + 
                    "` LIKE '" + TQString::fromLatin1(fld->name) + "'";
 
    if(d->executeSQL(query)) {
        MYSQL_RES *res = mysql_store_result(d->mysql);
 
        if (res != NULL) {
            MYSQL_ROW row;
            while ((row = mysql_fetch_row(res)) != NULL) {
                mysqlType = TQString(row[1]);
            }
            mysql_free_result(res);
        } else {
            kdDebug() << "MySQLMigrate::examineBlobField: null result" << endl;
        }
    } else {
        // Huh? MySQL wont tell us what kind of field it is! Lets guess.
      return KexiDB::Field::LongText;
    }
 
    kdDebug() << "MySQLMigrate::examineBlobField: considering "
              << mysqlType << endl;
    if(mysqlType.contains("blob", false) != 0) {
        // Doesn't matter how big it is, it's binary
        kexiType = KexiDB::Field::BLOB;
/*  } else if(mysqlType.contains("text", false) != 0) {
        // All the TEXT types are too big for Kexi text.
        kexiType = KexiDB::Field::BLOB;*/
    } else if(fld->length < 200) {
        kexiType = KexiDB::Field::Text;
    } else {
        kexiType = KexiDB::Field::LongText;
    }
    return kexiType;
}
 


TQStringList MySQLMigrate::examineEnumField(const TQString& table,
        const MYSQL_FIELD* fld) {
    TQString vals;
    TQString query = "SHOW COLUMNS FROM `" + drv_escapeIdentifier(table) + 
            "` LIKE '" + TQString::fromLatin1(fld->name) + "'";
 
    if(d->executeSQL(query)) {
        MYSQL_RES *res = mysql_store_result(d->mysql);
 
        if (res != NULL) {
            MYSQL_ROW row;
            while ((row = mysql_fetch_row(res)) != NULL) {
                vals = TQString(row[1]);
            }
            mysql_free_result(res);
        } else {
            kdDebug() << "MySQLMigrate::examineEnumField: null result" << endl;
        }
    } else {
        // Huh? MySQL wont tell us what values it can take.
        return TQStringList();
    }
 
    kdDebug() << "MySQLMigrate::examineEnumField: considering " 
                        << vals << endl;
    
    // Crash and burn if we get confused...
    if(!vals.startsWith("enum(")) {
        // Huh? We're supposed to be parsing an enum!
        kdDebug() << "MySQLMigrate::examineEnumField:1 not an enum!" << endl;
        return TQStringList();
    }
    if(!vals.endsWith(")")) {
        kdDebug() << "MySQLMigrate::examineEnumField:2 not an enum!" << endl;
        return TQStringList();
    }
    
    // It'd be nice to use TQString.section or TQStringList.split, but we need
    // to be careful as enum values can have commas and quote marks in them
    // e.g. CREATE TABLE t(f enum('option,''') gives one option: "option,'"
    vals = vals.remove(0,5);
    TQRegExp rx = TQRegExp("^'((?:[^,']|,|'')*)'");
    TQStringList values = TQStringList();
    int index = 0;
 
    while ((index = rx.search(vals, index, TQRegExp::CaretAtOffset)) != -1) {
        int len = rx.matchedLength();
        if (len != -1) {
            kdDebug() << "MySQLMigrate::examineEnumField:3 " << rx.cap(1) << endl;
            values << rx.cap(1);
        } else {
            kdDebug() << "MySQLMigrate::examineEnumField:4 lost" << endl;
        }
        
        TQChar next = vals[index + len];
        if (next != TQChar(',') && next != TQChar(')')) {
            kdDebug() << "MySQLMigrate::examineEnumField:5 " << (char)next << endl;
        }
        index += len + 1;
    }
 
    return values;
}
 
 
void MySQLMigrate::getConstraints(int flags, KexiDB::Field* fld) {
    fld->setPrimaryKey(flags & PRI_KEY_FLAG);
    fld->setAutoIncrement(flags & AUTO_INCREMENT_FLAG);
    fld->setNotNull(flags & NOT_NULL_FLAG);
    fld->setUniqueKey(flags & UNIQUE_KEY_FLAG);
}
 
 
void MySQLMigrate::getOptions(int flags, KexiDB::Field* fld) {
    fld->setUnsigned(flags & UNSIGNED_FLAG);
}
 
 
#include "mysqlmigrate.moc"