mysqlmigrate.cpp
1/* This file is part of the KDE project
2 Copyright (C) 2004 Martin Ellis <m.a.ellis@ncl.ac.uk>
3 Copyright (C) 2006 Jaroslaw Staniek <js@iidea.pl>
4
5 This program is free software; you can redistribute it and/or
6 modify it under the terms of the GNU Library General Public
7 License as published by the Free Software Foundation; either
8 version 2 of the License, or (at your option) any later version.
9
10 This program is distributed in the hope that it will be useful,
11 but WITHOUT ANY WARRANTY; without even the implied warranty of
12 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
13 Library General Public License for more details.
14
15 You should have received a copy of the GNU Library General Public License
16 along with this program; see the file COPYING. If not, write to
17 the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
18 * Boston, MA 02110-1301, USA.
19*/
20
21#include "mysqlmigrate.h"
22
23#include <tqstring.h>
24#include <tqregexp.h>
25#include <tqfile.h>
26#include <tqvariant.h>
27#include <tqvaluelist.h>
28#include <kdebug.h>
29
30#include <mysql_version.h>
31#include <mysql.h>
32
33#include "migration/keximigratedata.h"
34#include <kexidb/cursor.h>
35#include <kexidb/field.h>
36#include <kexidb/utils.h>
37#include <kexidb/drivers/mySQL/mysqlconnection_p.cpp>
38#include <kexidb/drivermanager.h>
39#include <kexiutils/identifier.h>
40
41using namespace KexiMigration;
42
43/* This is the implementation for the MySQL specific import routines. */
44
45KEXIMIGRATE_DRIVER_INFO( MySQLMigrate, mysql )
46
47/* ************************************************************************** */
49/*MySQLMigrate::MySQLMigrate() :
50 d(new MySqlConnectionInternal())
51{
52}*/
53
54
55MySQLMigrate::MySQLMigrate(TQObject *parent, const char *name,
56 const TQStringList &args) :
57 KexiMigrate(parent, name, args)
58 ,d(new MySqlConnectionInternal(0))
59 ,m_mysqlres(0)
60{
61 KexiDB::DriverManager manager;
62 m_kexiDBDriver = manager.driver("mysql");
63}
64
65/* ************************************************************************** */
67MySQLMigrate::~MySQLMigrate() {
68 if (m_mysqlres)
69 mysql_free_result(m_mysqlres);
70 m_mysqlres = 0;
71}
72
73
74/* ************************************************************************** */
76bool MySQLMigrate::drv_connect() {
77 if(d->db_connect(*m_migrateData->source)) {
78 return d->useDatabase(m_migrateData->sourceName);
79 } else {
80 return false;
81 }
82}
83
84
86bool MySQLMigrate::drv_disconnect()
87{
88 return d->db_disconnect();
89}
90
91
92/* ************************************************************************** */
94bool MySQLMigrate::drv_readTableSchema(
95 const TQString& originalName, KexiDB::TableSchema& tableSchema)
96{
97// m_table = new KexiDB::TableSchema(table);
98
99// //TODO IDEA: ask for user input for captions
100// tableSchema.setCaption(table + " table");
101
102 //Perform a query on the table to get some data
103 TQString query = TQString("SELECT * FROM `") + drv_escapeIdentifier(originalName) + "` LIMIT 0";
104 if(d->executeSQL(query)) {
105 MYSQL_RES *res = mysql_store_result(d->mysql);
106 if (res != NULL) {
107
108 unsigned int numFlds = mysql_num_fields(res);
109 MYSQL_FIELD *fields = mysql_fetch_fields(res);
110
111 for(unsigned int i = 0; i < numFlds; i++) {
112 TQString fldName(fields[i].name);
113 TQString fldID( KexiUtils::string2Identifier(fldName) );
114
115 KexiDB::Field *fld =
116 new KexiDB::Field(fldID, type(originalName, &fields[i]));
117
118 if(fld->type() == KexiDB::Field::Enum) {
119 TQStringList values = examineEnumField(originalName, &fields[i]);
120 }
121
122 fld->setCaption(fldName);
123 getConstraints(fields[i].flags, fld);
124 getOptions(fields[i].flags, fld);
125 tableSchema.addField(fld);
126 }
127 mysql_free_result(res);
128 } else {
129 kdDebug() << "MySQLMigrate::drv_tableNames: null result" << endl;
130 }
131 return true;
132 } else {
133 return false;
134 }
135}
136
137
139bool MySQLMigrate::drv_tableNames(TQStringList& tableNames)
140{
141 if(d->executeSQL("SHOW TABLES")) {
142 MYSQL_RES *res = mysql_store_result(d->mysql);
143 if (res != NULL) {
144 MYSQL_ROW row;
145 while ((row = mysql_fetch_row(res)) != NULL) {
146 tableNames << TQString::fromUtf8(row[0]); //utf8.. ok?
147 }
148 mysql_free_result(res);
149 } else {
150 kdDebug() << "MySQLMigrate::drv_tableNames: null result" << endl;
151 }
152 return true;
153 } else {
154 return false;
155 }
156}
157
162tristate MySQLMigrate::drv_queryStringListFromSQL(
163 const TQString& sqlStatement, uint columnNumber, TQStringList& stringList, int numRecords)
164{
165 stringList.clear();
166 if (d->executeSQL(sqlStatement)) {
167 MYSQL_RES *res = mysql_use_result(d->mysql);
168 if (res != NULL) {
169 for (int i=0; numRecords == -1 || i < numRecords; i++) {
170 MYSQL_ROW row = mysql_fetch_row(res);
171 if (!row) {
172 tristate r;
173 if (mysql_errno(d->mysql))
174 r = false;
175 else
176 r = (numRecords == -1) ? true : cancelled;
177 mysql_free_result(res);
178 return r;
179 }
180 uint numFields = mysql_num_fields(res);
181 if (columnNumber > (numFields-1)) {
182 kdWarning() << "MySQLMigrate::drv_querySingleStringFromSQL("<<sqlStatement
183 << "): columnNumber too large ("
184 << columnNumber << "), expected 0.." << numFields << endl;
185 mysql_free_result(res);
186 return false;
187 }
188 unsigned long *lengths = mysql_fetch_lengths(res);
189 if (!lengths) {
190 mysql_free_result(res);
191 return false;
192 }
193 stringList.append( TQString::fromUtf8(row[columnNumber], lengths[columnNumber]) ); //ok? utf8?
194 }
195 mysql_free_result(res);
196 } else {
197 kdDebug() << "MySQLMigrate::drv_querySingleStringFromSQL(): null result" << endl;
198 }
199 return true;
200 } else {
201 return false;
202 }
203}
204
207tristate MySQLMigrate::drv_fetchRecordFromSQL(const TQString& sqlStatement,
208 KexiDB::RowData& data, bool &firstRecord)
209{
210 if (firstRecord || !m_mysqlres) {
211 if (m_mysqlres) {
212 mysql_free_result(m_mysqlres);
213 m_mysqlres = 0;
214 }
215 if (!d->executeSQL(sqlStatement) || !(m_mysqlres = mysql_use_result(d->mysql)))
216 return false;
217 firstRecord = false;
218 }
219
220 MYSQL_ROW row = mysql_fetch_row(m_mysqlres);
221 if (!row) {
222 tristate r = cancelled;
223 if (mysql_errno(d->mysql))
224 r = false;
225 mysql_free_result(m_mysqlres);
226 m_mysqlres = 0;
227 return r;
228 }
229 const int numFields = mysql_num_fields(m_mysqlres);
230 unsigned long *lengths = mysql_fetch_lengths(m_mysqlres);
231 if (!lengths) {
232 mysql_free_result(m_mysqlres);
233 m_mysqlres = 0;
234 return false;
235 }
236 data.resize(numFields);
237 for (int i=0; i < numFields; i++)
238 data[i] = TQString::fromUtf8(row[i], lengths[i] ); //ok? utf8?
239 return true;
240}
241
243bool MySQLMigrate::drv_copyTable(const TQString& srcTable, KexiDB::Connection *destConn,
244 KexiDB::TableSchema* dstTable)
245{
246 if(d->executeSQL("SELECT * FROM `" + drv_escapeIdentifier(srcTable)) + "`") {
247 MYSQL_RES *res = mysql_use_result(d->mysql);
248 if (res != NULL) {
249 MYSQL_ROW row;
250 const KexiDB::QueryColumnInfo::Vector fieldsExpanded( dstTable->query()->fieldsExpanded() );
251 while ((row = mysql_fetch_row(res)) != NULL) {
252 const int numFields = TQMIN((int)fieldsExpanded.count(), (int)mysql_num_fields(res));
253 TQValueList<TQVariant> vals;
254 unsigned long *lengths = mysql_fetch_lengths(res);
255 if (!lengths) {
256 mysql_free_result(res);
257 return false;
258 }
259 for(int i = 0; i < numFields; i++)
260 vals.append( KexiDB::cstringToVariant(row[i], fieldsExpanded.at(i)->field, (int)lengths[i]) );
261 if (!destConn->insertRecord(*dstTable, vals)) {
262 mysql_free_result(res);
263 return false;
264 }
265 updateProgress();
266 }
267 if (!row && mysql_errno(d->mysql)) {
268 mysql_free_result(res);
269 return false;
270 }
272 mysql_free_result(res);
273 } else {
274 kdDebug() << "MySQLMigrate::drv_copyTable: null result" << endl;
275 }
276 return true;
277 } else {
278 return false;
279 }
280}
281
282
283bool MySQLMigrate::drv_getTableSize(const TQString& table, TQ_ULLONG& size) {
284 if(d->executeSQL("SELECT COUNT(*) FROM `" + drv_escapeIdentifier(table)) + "`") {
285 MYSQL_RES *res = mysql_store_result(d->mysql);
286 if (res != NULL) {
287 MYSQL_ROW row;
288 while ((row = mysql_fetch_row(res)) != NULL) {
290 size = TQString(row[0]).toULongLong();
291 }
292 mysql_free_result(res);
293 } else {
294 kdDebug() << "MySQLMigrate::drv_getTableSize: null result" << endl;
295 }
296 return true;
297 } else {
298 return false;
299 }
300}
301
303KexiDB::Field::Type MySQLMigrate::type(const TQString& table,
304 const MYSQL_FIELD *fld)
305{
306 // Field type
307 KexiDB::Field::Type kexiType = KexiDB::Field::InvalidType;
308
309 switch(fld->type)
310 {
311 // These are in the same order as mysql_com.h.
312 // MySQL names given on the right
313 case FIELD_TYPE_DECIMAL: // DECIMAL or NUMERIC
314 break;
315 case FIELD_TYPE_TINY: // TINYINT (-2^7..2^7-1 or 2^8)
316 kexiType = KexiDB::Field::Byte;
317 break;
318 case FIELD_TYPE_SHORT: // SMALLINT (-2^15..2^15-1 or 2^16)
319 kexiType = KexiDB::Field::ShortInteger;
320 break;
321 case FIELD_TYPE_LONG: // INTEGER (-2^31..2^31-1 or 2^32)
322 kexiType = KexiDB::Field::Integer;
323 break;
324 case FIELD_TYPE_FLOAT: // FLOAT
325 kexiType = KexiDB::Field::Float;
326 break;
327 case FIELD_TYPE_DOUBLE: // DOUBLE or REAL (8 byte)
328 kexiType = KexiDB::Field::Double;
329 break;
330 case FIELD_TYPE_NULL: // WTF?
331 break;
332 case FIELD_TYPE_TIMESTAMP: // TIMESTAMP (promote?)
333 kexiType = KexiDB::Field::DateTime;
334 break;
335 case FIELD_TYPE_LONGLONG: // BIGINT (-2^63..2^63-1 or 2^64)
336 case FIELD_TYPE_INT24: // MEDIUMINT (-2^23..2^23-1 or 2^24) (promote)
337 kexiType = KexiDB::Field::BigInteger;
338 break;
339 case FIELD_TYPE_DATE: // DATE
340 kexiType = KexiDB::Field::Date;
341 break;
342 case FIELD_TYPE_TIME: // TIME
343 kexiType = KexiDB::Field::Time;
344 break;
345 case FIELD_TYPE_DATETIME: // DATETIME
346 kexiType = KexiDB::Field::DateTime;
347 break;
348 case FIELD_TYPE_YEAR: // YEAR (promote)
349 kexiType = KexiDB::Field::ShortInteger;
350 break;
351 case FIELD_TYPE_NEWDATE: // WTF?
352 case FIELD_TYPE_ENUM: // ENUM
353 // If MySQL did what it's documentation said it did, we would come here
354 // for enum fields ...
355 kexiType = KexiDB::Field::Enum;
356 break;
357 case FIELD_TYPE_SET: // SET
359 break;
360 case FIELD_TYPE_TINY_BLOB:
361 case FIELD_TYPE_MEDIUM_BLOB:
362 case FIELD_TYPE_LONG_BLOB:
363 case FIELD_TYPE_BLOB: // BLOB or TEXT
364 case FIELD_TYPE_VAR_STRING: // VARCHAR
365 case FIELD_TYPE_STRING: // CHAR
366
367 if (fld->flags & ENUM_FLAG) {
368 // ... instead we come here, using the ENUM_FLAG which is supposed to
369 // be deprecated! Duh.
370 kexiType = KexiDB::Field::Enum;
371 break;
372 }
373 kexiType = examineBlobField(table, fld);
374 break;
375 default:
376 kexiType = KexiDB::Field::InvalidType;
377 }
378
379 if (kexiType == KexiDB::Field::InvalidType) {
380 return userType(table);
381 }
382 return kexiType;
383}
384
385
387
394KexiDB::Field::Type MySQLMigrate::examineBlobField(const TQString& table,
395 const MYSQL_FIELD* fld) {
396 TQString mysqlType;
397 KexiDB::Field::Type kexiType;
398 TQString query = "SHOW COLUMNS FROM `" + drv_escapeIdentifier(table) +
399 "` LIKE '" + TQString::fromLatin1(fld->name) + "'";
400
401 if(d->executeSQL(query)) {
402 MYSQL_RES *res = mysql_store_result(d->mysql);
403
404 if (res != NULL) {
405 MYSQL_ROW row;
406 while ((row = mysql_fetch_row(res)) != NULL) {
407 mysqlType = TQString(row[1]);
408 }
409 mysql_free_result(res);
410 } else {
411 kdDebug() << "MySQLMigrate::examineBlobField: null result" << endl;
412 }
413 } else {
414 // Huh? MySQL wont tell us what kind of field it is! Lets guess.
415 return KexiDB::Field::LongText;
416 }
417
418 kdDebug() << "MySQLMigrate::examineBlobField: considering "
419 << mysqlType << endl;
420 if(mysqlType.contains("blob", false) != 0) {
421 // Doesn't matter how big it is, it's binary
422 kexiType = KexiDB::Field::BLOB;
423/* } else if(mysqlType.contains("text", false) != 0) {
424 // All the TEXT types are too big for Kexi text.
425 kexiType = KexiDB::Field::BLOB;*/
426 } else if(fld->length < 200) {
427 kexiType = KexiDB::Field::Text;
428 } else {
429 kexiType = KexiDB::Field::LongText;
430 }
431 return kexiType;
432}
433
434
436
443TQStringList MySQLMigrate::examineEnumField(const TQString& table,
444 const MYSQL_FIELD* fld) {
445 TQString vals;
446 TQString query = "SHOW COLUMNS FROM `" + drv_escapeIdentifier(table) +
447 "` LIKE '" + TQString::fromLatin1(fld->name) + "'";
448
449 if(d->executeSQL(query)) {
450 MYSQL_RES *res = mysql_store_result(d->mysql);
451
452 if (res != NULL) {
453 MYSQL_ROW row;
454 while ((row = mysql_fetch_row(res)) != NULL) {
455 vals = TQString(row[1]);
456 }
457 mysql_free_result(res);
458 } else {
459 kdDebug() << "MySQLMigrate::examineEnumField: null result" << endl;
460 }
461 } else {
462 // Huh? MySQL wont tell us what values it can take.
463 return TQStringList();
464 }
465
466 kdDebug() << "MySQLMigrate::examineEnumField: considering "
467 << vals << endl;
468
469 // Crash and burn if we get confused...
470 if(!vals.startsWith("enum(")) {
471 // Huh? We're supposed to be parsing an enum!
472 kdDebug() << "MySQLMigrate::examineEnumField:1 not an enum!" << endl;
473 return TQStringList();
474 }
475 if(!vals.endsWith(")")) {
476 kdDebug() << "MySQLMigrate::examineEnumField:2 not an enum!" << endl;
477 return TQStringList();
478 }
479
480 // It'd be nice to use TQString.section or TQStringList.split, but we need
481 // to be careful as enum values can have commas and quote marks in them
482 // e.g. CREATE TABLE t(f enum('option,''') gives one option: "option,'"
483 vals = vals.remove(0,5);
484 TQRegExp rx = TQRegExp("^'((?:[^,']|,|'')*)'");
485 TQStringList values = TQStringList();
486 int index = 0;
487
488 while ((index = rx.search(vals, index, TQRegExp::CaretAtOffset)) != -1) {
489 int len = rx.matchedLength();
490 if (len != -1) {
491 kdDebug() << "MySQLMigrate::examineEnumField:3 " << rx.cap(1) << endl;
492 values << rx.cap(1);
493 } else {
494 kdDebug() << "MySQLMigrate::examineEnumField:4 lost" << endl;
495 }
496
497 TQChar next = vals[index + len];
498 if (next != TQChar(',') && next != TQChar(')')) {
499 kdDebug() << "MySQLMigrate::examineEnumField:5 " << (char)next << endl;
500 }
501 index += len + 1;
502 }
503
504 return values;
505}
506
507
508void MySQLMigrate::getConstraints(int flags, KexiDB::Field* fld) {
509 fld->setPrimaryKey(flags & PRI_KEY_FLAG);
510 fld->setAutoIncrement(flags & AUTO_INCREMENT_FLAG);
511 fld->setNotNull(flags & NOT_NULL_FLAG);
512 fld->setUniqueKey(flags & UNIQUE_KEY_FLAG);
514}
515
516
517void MySQLMigrate::getOptions(int flags, KexiDB::Field* fld) {
518 fld->setUnsigned(flags & UNSIGNED_FLAG);
519}
520
521
522#include "mysqlmigrate.moc"
KexiDB::Connection
Provides database connection, allowing queries and data modification.
Definition connection.h:62
KexiDB::DriverManager
Database driver management, e.g. finding and loading drivers.
Definition drivermanager.h:43
KexiDB::DriverManager::driver
Driver * driver(const TQString &name)
Definition drivermanager.cpp:388
KexiDB::FieldList::addField
FieldList & addField(Field *field)
Definition fieldlist.cpp:106
KexiDB::Field
Meta-data for a field.
Definition field.h:73
KexiDB::Field::type
Type type() const
Definition field.cpp:134
KexiDB::Field::setCaption
void setCaption(const TQString &caption)
Definition field.h:493
KexiDB::Field::ShortInteger
@ ShortInteger
Definition field.h:86
KexiDB::Field::InvalidType
@ InvalidType
Definition field.h:84
KexiDB::Field::setPrimaryKey
void setPrimaryKey(bool p)
Definition field.cpp:524
KexiDB::Field::setUniqueKey
void setUniqueKey(bool u)
Definition field.cpp:541
KexiDB::Field::setAutoIncrement
void setAutoIncrement(bool a)
Definition field.cpp:515
KexiDB::Field::setNotNull
void setNotNull(bool n)
Definition field.cpp:558
KexiDB::Field::setUnsigned
void setUnsigned(bool u)
Definition field.cpp:389
KexiDB::QuerySchema::fieldsExpanded
QueryColumnInfo::Vector fieldsExpanded(FieldsExpandedOptions options=Default)
Definition queryschema.cpp:1121
KexiDB::TableSchema::query
QuerySchema * query()
Definition tableschema.cpp:364
KexiMigration::KexiMigrate
Imports non-native databases into Kexi projects.
Definition keximigrate.h:79
tristate
3-state logical type with three values: true, false and cancelled and convenient operators.
Definition tristate.h:100
KexiDB::cstringToVariant
TQVariant cstringToVariant(const char *data, KexiDB::Field *f, int length=-1)
Definition kexidb/utils.h:447
KexiDB::RowData
TQValueVector< TQVariant > RowData
structure for storing single record with type information
Definition connection.h:47
KexiMigration
Framework for importing databases into native KexiDB databases.
Definition importoptionsdlg.h:28
KexiUtils::string2Identifier
KEXIUTILS_EXPORT TQString string2Identifier(const TQString &s)
Definition identifier.cpp:54