kexi

kexicsvimportdialog.cpp

00001 /* This file is part of the KDE project
00002    Copyright (C) 2005-2006 Jaroslaw Staniek <js@iidea.pl>
00003 
00004    This work is based on kspread/dialogs/kspread_dlg_csv.cc
00005    and will be merged back with KOffice libraries.
00006 
00007    Copyright (C) 2002-2003 Norbert Andres <nandres@web.de>
00008    Copyright (C) 2002-2003 Ariya Hidayat <ariya@kde.org>
00009    Copyright (C) 2002 Laurent Montel <montel@kde.org>
00010    Copyright (C) 1999 David Faure <faure@kde.org>
00011 
00012    This library is free software; you can redistribute it and/or
00013    modify it under the terms of the GNU Library General Public
00014    License as published by the Free Software Foundation; either
00015    version 2 of the License, or (at your option) any later version.
00016 
00017    This library is distributed in the hope that it will be useful,
00018    but WITHOUT ANY WARRANTY; without even the implied warranty of
00019    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
00020    Library General Public License for more details.
00021 
00022    You should have received a copy of the GNU Library General Public License
00023    along with this library; see the file COPYING.LIB.  If not, write to
00024    the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
00025  * Boston, MA 02110-1301, USA.
00026 */
00027 
00028 #include <qbuttongroup.h>
00029 #include <qcheckbox.h>
00030 #include <qclipboard.h>
00031 #include <qlabel.h>
00032 #include <qlineedit.h>
00033 #include <qmime.h>
00034 #include <qpushbutton.h>
00035 #include <qradiobutton.h>
00036 #include <qtable.h>
00037 #include <qlayout.h>
00038 #include <qfiledialog.h>
00039 #include <qpainter.h>
00040 #include <qtextcodec.h>
00041 #include <qtimer.h>
00042 #include <qfontmetrics.h>
00043 #include <qtooltip.h>
00044 
00045 #include <kapplication.h>
00046 #include <kdebug.h>
00047 #include <kdialogbase.h>
00048 #include <kfiledialog.h>
00049 #include <klocale.h>
00050 #include <kmessagebox.h>
00051 #include <kglobalsettings.h>
00052 #include <kiconloader.h>
00053 #include <kcharsets.h>
00054 #include <knuminput.h>
00055 #include <kprogress.h>
00056 #include <kactivelabel.h>
00057 
00058 #include <kexiutils/identifier.h>
00059 #include <kexiutils/utils.h>
00060 #include <core/kexi.h>
00061 #include <core/kexiproject.h>
00062 #include <core/kexipart.h>
00063 #include <core/kexipartinfo.h>
00064 #include <core/keximainwindow.h>
00065 #include <core/kexiguimsghandler.h>
00066 #include <kexidb/connection.h>
00067 #include <kexidb/tableschema.h>
00068 #include <kexidb/transaction.h>
00069 #include <widget/kexicharencodingcombobox.h>
00070 
00071 #include "kexicsvimportdialog.h"
00072 #include "kexicsvwidgets.h"
00073 
00074 #ifdef Q_WS_WIN
00075 #include <krecentdirs.h>
00076 #include <windows.h>
00077 #endif
00078 
00079 #if 0
00080 #include <kspread_cell.h>
00081 #include <kspread_doc.h>
00082 #include <kspread_sheet.h>
00083 #include <kspread_undo.h>
00084 #include <kspread_view.h>
00085 #endif
00086 
00087 #define _IMPORT_ICON "table" /*todo: change to "file_import" or so*/
00088 #define _TEXT_TYPE 0
00089 #define _NUMBER_TYPE 1
00090 #define _DATE_TYPE 2
00091 #define _TIME_TYPE 3
00092 #define _DATETIME_TYPE 4
00093 #define _PK_FLAG 5
00094 
00095 //extra:
00096 #define _NO_TYPE_YET -1 //allows to accept a number of empty cells, before something non-empty
00097 #define _FP_NUMBER_TYPE 255 //_NUMBER_TYPE variant
00098 #define MAX_ROWS_TO_PREVIEW 100 //max 100 rows is reasonable
00099 #define MAX_BYTES_TO_PREVIEW 10240 //max 10KB is reasonable
00100 #define MAX_CHARS_TO_SCAN_WHILE_DETECTING_DELIMITER 4096
00101 
00102 class KexiCSVImportDialogTable : public QTable
00103 {
00104 public:
00105     KexiCSVImportDialogTable( QWidget * parent = 0, const char * name = 0 )
00106     : QTable(parent, name) {
00107         f = font();
00108         f.setBold(true);
00109     }
00110     virtual void paintCell( QPainter * p, int row, int col, const QRect & cr, bool selected, const QColorGroup & cg ) {
00111         if (row==0)
00112             p->setFont(f);
00113         else
00114             p->setFont(font());
00115         QTable::paintCell(p, row, col, cr, selected, cg);
00116     }
00117     virtual void setColumnWidth( int col, int w ) {
00118         //make columns a bit wider
00119         QTable::setColumnWidth( col, w + 16 );
00120     }
00121     QFont f;
00122 };
00123 
00125 void installRecursiveEventFilter(QObject *filter, QObject *object)
00126 {
00127     object->installEventFilter(filter);
00128 
00129     if (!object->children())
00130         return;
00131 
00132     QObjectList list = *object->children();
00133     for(QObject *obj = list.first(); obj; obj = list.next())
00134         installRecursiveEventFilter(filter, obj);
00135 }
00136 
00137 KexiCSVImportDialog::KexiCSVImportDialog( Mode mode, KexiMainWindow* mainWin, 
00138     QWidget * parent, const char * name
00139 )
00140  : KDialogBase( 
00141     KDialogBase::Plain, 
00142     i18n( "Import CSV Data File" )
00144     ,
00145     (mode==File ? User1 : (ButtonCode)0) |Ok|Cancel, 
00146     Ok,
00147     parent, 
00148     name ? name : "KexiCSVImportDialog", 
00149     true, 
00150     false,
00151     KGuiItem( i18n("&Options"))
00152   ),
00153     m_mainWin(mainWin),
00154     m_cancelled( false ),
00155     m_adjustRows( true ),
00156     m_startline( 0 ),
00157     m_textquote( QString(KEXICSV_DEFAULT_FILE_TEXT_QUOTE)[0] ),
00158     m_mode(mode),
00159     m_prevSelectedCol(-1),
00160     m_columnsAdjusted(false),
00161     m_1stRowForFieldNamesDetected(false),
00162     m_firstFillTableCall(true),
00163     m_blockUserEvents(false),
00164     m_primaryKeyColumn(-1),
00165     m_dialogCancelled(false),
00166     m_conn(0),
00167     m_destinationTableSchema(0),
00168     m_allRowsLoadedInPreview(false),
00169     m_stoppedAt_MAX_BYTES_TO_PREVIEW(false)
00170 {
00171     setWFlags(getWFlags() | Qt::WStyle_Maximize | Qt::WStyle_SysMenu);
00172     hide();
00173     setButtonOK(KGuiItem( i18n("&Import..."), _IMPORT_ICON));
00174 
00175     m_typeNames.resize(5);
00176     m_typeNames[0] = i18n("text");
00177     m_typeNames[1] = i18n("number");
00178     m_typeNames[2] = i18n("date");
00179     m_typeNames[3] = i18n("time");
00180     m_typeNames[4] = i18n("date/time");
00181 
00182     kapp->config()->setGroup("ImportExport");
00183     m_maximumRowsForPreview = kapp->config()->readNumEntry("MaximumRowsForPreviewInImportDialog", MAX_ROWS_TO_PREVIEW);
00184     m_maximumBytesForPreview = kapp->config()->readNumEntry("MaximumBytesForPreviewInImportDialog", MAX_BYTES_TO_PREVIEW);
00185 
00186     m_pkIcon = SmallIcon("key");
00187 
00188     m_uniquenessTest.setAutoDelete(true);
00189 
00190     setIcon(DesktopIcon(_IMPORT_ICON));
00191     setSizeGripEnabled( TRUE );
00192 
00193 //  m_encoding = QString::fromLatin1(KGlobal::locale()->encoding());
00194 //  m_stripWhiteSpaceInTextValuesChecked = true;
00195     m_file = 0;
00196     m_inputStream = 0;
00197     
00198     QVBoxLayout *lyr = new QVBoxLayout(plainPage(), 0, KDialogBase::spacingHint(), "lyr");
00199 
00200     m_infoLbl = new KexiCSVInfoLabel(
00201         m_mode==File ? i18n("Preview of data from file:")
00202         : i18n("Preview of data from clipboard:"),
00203         plainPage()
00204     );
00205     lyr->addWidget( m_infoLbl );
00206 
00207     QWidget* page = new QFrame( plainPage(), "page" );
00208     QGridLayout *glyr= new QGridLayout( page, 4, 5, 0, KDialogBase::spacingHint(), "glyr");
00209     lyr->addWidget( page );
00210 
00211     // Delimiter: comma, semicolon, tab, space, other
00212     m_delimiterWidget = new KexiCSVDelimiterWidget(true /*lineEditOnBottom*/, page);
00213     m_detectDelimiter = true;
00214     glyr->addMultiCellWidget( m_delimiterWidget, 1, 2, 0, 0 );
00215 
00216     QLabel *delimiterLabel = new QLabel(m_delimiterWidget, i18n("Delimiter:"), page);
00217     delimiterLabel->setAlignment(Qt::AlignAuto | Qt::AlignBottom);
00218     glyr->addMultiCellWidget( delimiterLabel, 0, 0, 0, 0 );
00219 
00220     // Format: number, text, currency,
00221     m_formatComboText = i18n( "Format for column %1:" );
00222     m_formatCombo = new KComboBox(page, "m_formatCombo");
00223     m_formatCombo->insertItem(i18n("Text"));
00224     m_formatCombo->insertItem(i18n("Number"));
00225     m_formatCombo->insertItem(i18n("Date"));
00226     m_formatCombo->insertItem(i18n("Time"));
00227     m_formatCombo->insertItem(i18n("Date/Time"));
00228     glyr->addMultiCellWidget( m_formatCombo, 1, 1, 1, 1 );
00229 
00230     m_formatLabel = new QLabel(m_formatCombo, "", page);
00231     m_formatLabel->setAlignment(Qt::AlignAuto | Qt::AlignBottom);
00232     glyr->addWidget( m_formatLabel, 0, 1 );
00233 
00234     m_primaryKeyField = new QCheckBox( i18n( "Primary key" ), page, "m_primaryKeyField" );
00235     glyr->addWidget( m_primaryKeyField, 2, 1 );
00236     connect(m_primaryKeyField, SIGNAL(toggled(bool)), this, SLOT(slotPrimaryKeyFieldToggled(bool)));
00237 
00238     m_comboQuote = new KexiCSVTextQuoteComboBox( page );
00239     glyr->addWidget( m_comboQuote, 1, 2 );
00240 
00241     TextLabel2 = new QLabel( m_comboQuote, i18n( "Text quote:" ), page, "TextLabel2" );
00242     TextLabel2->setSizePolicy( QSizePolicy::Fixed, QSizePolicy::Preferred );
00243     TextLabel2->setAlignment(Qt::AlignAuto | Qt::AlignBottom);
00244     glyr->addWidget( TextLabel2, 0, 2 );
00245 
00246     m_startAtLineSpinBox = new KIntSpinBox( page, "m_startAtLineSpinBox" );
00247     m_startAtLineSpinBox->setMinValue(1);
00248     m_startAtLineSpinBox->setSizePolicy( QSizePolicy::Fixed, QSizePolicy::Fixed );
00249     m_startAtLineSpinBox->setMinimumWidth(QFontMetrics(m_startAtLineSpinBox->font()).width("8888888"));
00250     glyr->addWidget( m_startAtLineSpinBox, 1, 3 );
00251 
00252     m_startAtLineLabel = new QLabel( m_startAtLineSpinBox, "", 
00253         page, "TextLabel3" );
00254     m_startAtLineLabel->setSizePolicy( QSizePolicy::Fixed, QSizePolicy::Preferred );
00255     m_startAtLineLabel->setAlignment(Qt::AlignAuto | Qt::AlignBottom);
00256     glyr->addWidget( m_startAtLineLabel, 0, 3 );
00257 
00258     QSpacerItem* spacer_2 = new QSpacerItem( 0, 0, QSizePolicy::Minimum, QSizePolicy::Preferred );
00259     glyr->addItem( spacer_2, 0, 4 );
00260 
00261     m_ignoreDuplicates = new QCheckBox( page, "m_ignoreDuplicates" );
00262     m_ignoreDuplicates->setText( i18n( "Ignore duplicated delimiters" ) );
00263     glyr->addMultiCellWidget( m_ignoreDuplicates, 2, 2, 2, 4 );
00264 
00265     m_1stRowForFieldNames = new QCheckBox( page, "m_1stRowForFieldNames" );
00266     m_1stRowForFieldNames->setText( i18n( "First row contains column names" ) );
00267     glyr->addMultiCellWidget( m_1stRowForFieldNames, 3, 3, 2, 4 );
00268 
00269     m_table = new KexiCSVImportDialogTable( plainPage(), "m_table" );
00270     lyr->addWidget( m_table );
00271 
00272     m_table->setSizePolicy( QSizePolicy(QSizePolicy::MinimumExpanding, QSizePolicy::MinimumExpanding, 1, 1) );
00273     m_table->setNumRows( 0 );
00274     m_table->setNumCols( 0 );
00275 
00277 /*
00278 if ( m_mode == Clipboard )
00279   {
00280     setCaption( i18n( "Inserting From Clipboard" ) );
00281     QMimeSource * mime = QApplication::clipboard()->data();
00282     if ( !mime )
00283     {
00284       KMessageBox::information( this, i18n("There is no data in the clipboard.") );
00285       m_cancelled = true;
00286       return;
00287     }
00288 
00289     if ( !mime->provides( "text/plain" ) )
00290     {
00291       KMessageBox::information( this, i18n("There is no usable data in the clipboard.") );
00292       m_cancelled = true;
00293       return;
00294     }
00295     m_fileArray = QByteArray(mime->encodedData( "text/plain" ) );
00296   }
00297   else if ( mode == File )
00298   {*/
00299     m_dateRegExp = QRegExp("(\\d{1,4})([/\\-\\.])(\\d{1,2})([/\\-\\.])(\\d{1,4})");
00300     m_timeRegExp1 = QRegExp("(\\d{1,2}):(\\d{1,2}):(\\d{1,2})");
00301     m_timeRegExp2 = QRegExp("(\\d{1,2}):(\\d{1,2})");
00302     m_fpNumberRegExp = QRegExp("[\\-]{0,1}\\d*[,\\.]\\d+");
00303     QString caption( i18n("Open CSV Data File") );
00304 
00305     if (m_mode == File) {
00306         QStringList mimetypes( csvMimeTypes() );
00307 #ifdef Q_WS_WIN
00309         QString recentDir = KGlobalSettings::documentPath();
00310         m_fname = QFileDialog::getOpenFileName( 
00311             KFileDialog::getStartURL(":CSVImportExport", recentDir).path(),
00312             KexiUtils::fileDialogFilterStrings(mimetypes, false),
00313             page, "KexiCSVImportDialog", caption);
00314         if ( !m_fname.isEmpty() ) {
00315             //save last visited path
00316             KURL url;
00317             url.setPath( m_fname );
00318             if (url.isLocalFile())
00319                 KRecentDirs::add(":CSVImportExport", url.directory());
00320         }
00321 #else
00322         m_fname = KFileDialog::getOpenFileName(":CSVImportExport", mimetypes.join(" "), 
00323             this, caption);
00324 #endif
00325         //cancel action !
00326         if ( m_fname.isEmpty() )
00327         {
00328             actionButton( Ok )->setEnabled( false );
00329             m_cancelled = true;
00330             if (parentWidget())
00331                 parentWidget()->raise();
00332             return;
00333         }
00334     }
00335     else if (m_mode == Clipboard) {
00336         QCString subtype("plain");
00337         m_clipboardData = QApplication::clipboard()->text(subtype, QClipboard::Clipboard);
00338 /* debug
00339         for (int i=0;QApplication::clipboard()->data(QClipboard::Clipboard)->format(i);i++)
00340             kdDebug() << i << ": " 
00341                 << QApplication::clipboard()->data(QClipboard::Clipboard)->format(i) << endl;
00342 */
00343     }
00344     else {
00345         return;
00346     }
00347 
00348     m_loadingProgressDlg = 0;
00349     m_importingProgressDlg = 0;
00350     if (m_mode == File) {
00351         m_loadingProgressDlg = new KProgressDialog(
00352             this, "m_loadingProgressDlg", i18n("Loading CSV Data"), i18n("Loading CSV Data from \"%1\"...")
00353             .arg(QDir::convertSeparators(m_fname)), true);
00354         m_loadingProgressDlg->progressBar()->setTotalSteps( m_maximumRowsForPreview+1 );
00355         m_loadingProgressDlg->show();
00356     }
00357 
00358     if (m_mode==Clipboard) {
00359         m_infoLbl->setIcon("editpaste");
00360     }
00361     //updateRowCountInfo();
00362 
00363     m_table->setSelectionMode(QTable::NoSelection);
00364 
00365     connect(m_formatCombo, SIGNAL(activated(int)),
00366       this, SLOT(formatChanged(int)));
00367     connect(m_delimiterWidget, SIGNAL(delimiterChanged(const QString&)),
00368       this, SLOT(delimiterChanged(const QString&)));
00369     connect(m_startAtLineSpinBox, SIGNAL(valueChanged ( int )),
00370       this, SLOT(startlineSelected(int)));
00371     connect(m_comboQuote, SIGNAL(activated(int)),
00372       this, SLOT(textquoteSelected(int)));
00373     connect(m_table, SIGNAL(currentChanged(int, int)),
00374       this, SLOT(currentCellChanged(int, int)));
00375     connect(m_table, SIGNAL(valueChanged(int,int)),
00376       this, SLOT(cellValueChanged(int,int)));
00377     connect(m_ignoreDuplicates, SIGNAL(stateChanged(int)),
00378       this, SLOT(ignoreDuplicatesChanged(int)));
00379     connect(m_1stRowForFieldNames, SIGNAL(stateChanged(int)),
00380       this, SLOT(slot1stRowForFieldNamesChanged(int)));
00381 
00382     connect(this, SIGNAL(user1Clicked()), this, SLOT(optionsButtonClicked()));
00383 
00384     installRecursiveEventFilter(this, this);
00385 
00386     initLater();
00387 }
00388 
00389 KexiCSVImportDialog::~KexiCSVImportDialog()
00390 {
00391     delete m_file;
00392 }
00393 
00394 void KexiCSVImportDialog::initLater()
00395 {
00396     if (!openData())
00397         return;
00398 
00399 //  delimiterChanged(detectedDelimiter); // this will cause fillTable()
00400     m_columnsAdjusted = false;
00401     fillTable();
00402     delete m_loadingProgressDlg;
00403     m_loadingProgressDlg = 0;
00404     if (m_dialogCancelled) {
00405 //      m_loadingProgressDlg->hide();
00406     //  m_loadingProgressDlg->close();
00407         QTimer::singleShot(0, this, SLOT(reject()));
00408         return;
00409     }
00410 
00411     currentCellChanged(0, 0);
00412 
00413 //  updateGeometry();
00414     adjustSize();
00415     KDialog::centerOnScreen( this ); 
00416 
00417     if (m_loadingProgressDlg)
00418         m_loadingProgressDlg->hide();
00419     show();
00420     m_table->setFocus();
00421 }
00422 
00423 bool KexiCSVImportDialog::openData()
00424 {
00425     if (m_mode!=File) //data already loaded, no encoding stuff needed
00426         return true;
00427 
00428     delete m_inputStream;
00429     m_inputStream = 0;
00430     if (m_file) {
00431         m_file->close();
00432         delete m_file;
00433     }
00434     m_file = new QFile(m_fname);
00435     if (!m_file->open(IO_ReadOnly))
00436     {
00437         m_file->close();
00438         delete m_file;
00439         m_file = 0;
00440         KMessageBox::sorry( this, i18n("Cannot open input file <nobr>\"%1\"</nobr>.")
00441             .arg(QDir::convertSeparators(m_fname)) );
00442         actionButton( Ok )->setEnabled( false );
00443         m_cancelled = true;
00444         if (parentWidget())
00445             parentWidget()->raise();
00446         return false;
00447     }
00448     return true;
00449 }
00450 
00451 bool KexiCSVImportDialog::cancelled() const
00452 {
00453     return m_cancelled;
00454 }
00455 
00456 void KexiCSVImportDialog::fillTable()
00457 {
00458     KexiUtils::WaitCursor wc(true);
00459     repaint();
00460     m_blockUserEvents = true;
00461     QPushButton *pb = actionButton(KDialogBase::Cancel);
00462     if (pb)
00463         pb->setEnabled(true); //allow to cancel
00464     KexiUtils::WaitCursor wait;
00465 
00466     if (m_table->numRows()>0) //to accept editor
00467         m_table->setCurrentCell(0,0);
00468 
00469     int row, column, maxColumn;
00470     QString field = QString::null;
00471 
00472     for (row = 0; row < m_table->numRows(); ++row)
00473         for (column = 0; column < m_table->numCols(); ++column)
00474             m_table->clearCell(row, column);
00475 
00476     m_detectedTypes.clear();
00477     m_detectedTypes.resize(1024, _NO_TYPE_YET);//_TEXT_TYPE);
00478     m_uniquenessTest.clear();
00479     m_uniquenessTest.resize(1024);
00480     m_1stRowForFieldNamesDetected = true;
00481 
00482     if (true != loadRows(field, row, column, maxColumn, true))
00483         return;
00484 
00485     m_1stRowForFieldNamesDetected = false;
00486 
00487     // file with only one line without '\n'
00488     if (field.length() > 0)
00489     {
00490         setText(row - m_startline, column, field, true);
00491         ++row;
00492         field = QString::null;
00493     }
00494 
00495     adjustRows( row - m_startline - (m_1stRowForFieldNames->isChecked()?1:0) );
00496 
00497     maxColumn = QMAX( maxColumn, column );
00498     m_table->setNumCols(maxColumn);
00499 
00500     for (column = 0; column < m_table->numCols(); ++column)
00501     {
00502 //      QString header = m_table->horizontalHeader()->label(column);
00503 //      if (header != i18n("Text") && header != i18n("Number") &&
00504 //          header != i18n("Date") && header != i18n("Currency"))
00505 //      const int detectedType = m_detectedTypes[column+1];
00506 //      m_table->horizontalHeader()->setLabel(column, m_typeNames[ detectedType ]); //i18n("Text"));
00507         updateColumnText(column);
00508         if (!m_columnsAdjusted)
00509             m_table->adjustColumn(column);
00510     }
00511     m_columnsAdjusted = true;
00512 
00513     if (m_primaryKeyColumn>=0 && m_primaryKeyColumn<m_table->numCols()) {
00514         if (_NUMBER_TYPE != m_detectedTypes[ m_primaryKeyColumn ]) {
00515             m_primaryKeyColumn = -1;
00516         }
00517     }
00518 
00519     m_prevSelectedCol = -1;
00520     m_table->setCurrentCell(0,0);
00521     currentCellChanged(0, 0);
00522     if (m_primaryKeyColumn != -1)
00523         m_table->setPixmap(0, m_primaryKeyColumn, m_pkIcon);
00524 
00525     const int count = QMAX(0, m_table->numRows()-1+m_startline);
00526     m_allRowsLoadedInPreview = count < m_maximumRowsForPreview && !m_stoppedAt_MAX_BYTES_TO_PREVIEW;
00527     if (m_allRowsLoadedInPreview) {
00528         m_startAtLineSpinBox->setMaxValue(count);
00529         m_startAtLineSpinBox->setValue(m_startline+1);
00530     }
00531     m_startAtLineLabel->setText(i18n( "Start at line%1:").arg(
00532             m_allRowsLoadedInPreview ? QString(" (1-%1)").arg(count)
00533             : QString::null //we do not know what's real count
00534     ));
00535     updateRowCountInfo();
00536 
00537     m_blockUserEvents = false;
00538     repaint();
00539     m_table->verticalScrollBar()->repaint();//avoid missing repaint
00540     m_table->horizontalScrollBar()->repaint();//avoid missing repaint
00541 }
00542 
00543 QString KexiCSVImportDialog::detectDelimiterByLookingAtFirstBytesOfFile(QTextStream& inputStream)
00544 {
00545     m_file->at(0);
00546 
00547     // try to detect delimiter
00548     // \t has priority, then ; then ,
00549     const QIODevice::Offset origOffset = inputStream.device()->at();
00550     QChar c, prevChar=0;
00551     int detectedDelimiter = 0;
00552     bool insideQuote = false;
00553 
00554     //characters by priority
00555     const int CH_TAB_AFTER_QUOTE = 500;
00556     const int CH_SEMICOLON_AFTER_QUOTE = 499;
00557     const int CH_COMMA_AFTER_QUOTE = 498;
00558     const int CH_TAB = 200; // \t
00559     const int CH_SEMICOLON = 199; // ;
00560     const int CH_COMMA = 198; // ,
00561 
00562     QValueList<int> tabsPerLine, semicolonsPerLine, commasPerLine;
00563     int tabs = 0, semicolons = 0, commas = 0;
00564     int line = 0;
00565     for (uint i=0; !inputStream.atEnd() && i < MAX_CHARS_TO_SCAN_WHILE_DETECTING_DELIMITER; i++) {
00566         (*m_inputStream) >> c; // read one char
00567         if (prevChar=='"') {
00568             if (c!='"') //real quote (not double "")
00569                 insideQuote = !insideQuote;
00570         }
00571         if (insideQuote) {
00572             prevChar = c;
00573             continue;
00574         }
00575         if (c==' ')
00576             continue;
00577         if (c=='\n') {//end of line
00578             //remember # of tabs/semicolons/commas in this line
00579             tabsPerLine += tabs;
00580             tabs = 0;
00581             semicolonsPerLine += semicolons;
00582             semicolons = 0;
00583             commasPerLine += commas;
00584             commas = 0;
00585             line++;
00586         }
00587         else if (c=='\t') {
00588             tabs++;
00589             detectedDelimiter = QMAX( prevChar=='"' ? CH_TAB_AFTER_QUOTE : CH_TAB, detectedDelimiter );
00590         }
00591         else if (c==';') {
00592             semicolons++;
00593             detectedDelimiter = QMAX( prevChar=='"' ? CH_SEMICOLON_AFTER_QUOTE : CH_SEMICOLON, detectedDelimiter );
00594         }
00595         else if (c==',') {
00596             commas++;
00597             detectedDelimiter = QMAX( prevChar=='"' ? CH_COMMA_AFTER_QUOTE : CH_COMMA, detectedDelimiter );
00598         }
00599         prevChar = c;
00600     }
00601 
00602     inputStream.device()->at(origOffset); //restore orig. offset
00603 
00604     //now, try to find a delimiter character that exists the same number of times in all the checked lines
00605     //this detection method has priority over others
00606     QValueList<int>::ConstIterator it;
00607     if (tabsPerLine.count()>1) {
00608         tabs = tabsPerLine.isEmpty() ? 0 : tabsPerLine.first();
00609         for (it=tabsPerLine.constBegin(); it!=tabsPerLine.constEnd(); ++it) {
00610             if (tabs != *it)
00611                 break;
00612         }
00613         if (tabs>0 && it==tabsPerLine.constEnd())
00614             return "\t";
00615     }
00616     if (semicolonsPerLine.count()>1) {
00617         semicolons = semicolonsPerLine.isEmpty() ? 0 : semicolonsPerLine.first();
00618         for (it=semicolonsPerLine.constBegin(); it!=semicolonsPerLine.constEnd(); ++it) {
00619             if (semicolons != *it)
00620                 break;
00621         }
00622         if (semicolons > 0 && it==semicolonsPerLine.constEnd())
00623             return ";";
00624     }
00625     if (commasPerLine.count()>1) {
00626         commas = commasPerLine.first();
00627         for (it=commasPerLine.constBegin(); it!=commasPerLine.constEnd(); ++it) {
00628             if (commas != *it)
00629                 break;
00630         }
00631         if (commas > 0 && it==commasPerLine.constEnd())
00632             return ",";
00633     }
00634     //now return the winning character by looking at CH_* symbol
00635     if (detectedDelimiter == CH_TAB_AFTER_QUOTE || detectedDelimiter == CH_TAB)
00636         return "\t";
00637     if (detectedDelimiter == CH_SEMICOLON_AFTER_QUOTE || detectedDelimiter == CH_SEMICOLON)
00638         return ";";
00639     if (detectedDelimiter == CH_COMMA_AFTER_QUOTE || detectedDelimiter == CH_COMMA)
00640         return ",";
00641 
00642     return KEXICSV_DEFAULT_FILE_DELIMITER; //<-- default
00643 }
00644 
00645 tristate KexiCSVImportDialog::loadRows(QString &field, int &row, int &column, int &maxColumn, 
00646     bool inGUI)
00647 {
00648     enum { S_START, S_QUOTED_FIELD, S_MAYBE_END_OF_QUOTED_FIELD, S_END_OF_QUOTED_FIELD,
00649          S_MAYBE_NORMAL_FIELD, S_NORMAL_FIELD } state = S_START;
00650     field = QString::null;
00651     const bool ignoreDups = m_ignoreDuplicates->isChecked();
00652     bool lastCharDelimiter = false;
00653     bool nextRow = false;
00654     row = column = 1;
00655     maxColumn = 0;
00656     QChar x;
00657     const bool hadInputStream = m_inputStream!=0;
00658     delete m_inputStream;
00659     if ( m_mode == Clipboard ) {
00660         m_inputStream = new QTextStream(m_clipboardData, IO_ReadOnly);
00661         if (!hadInputStream)
00662             m_delimiterWidget->setDelimiter(KEXICSV_DEFAULT_CLIPBOARD_DELIMITER);
00663     }
00664     else {
00665         m_file->at(0); //always seek at 0 because loadRows() is called many times
00666         m_inputStream = new QTextStream(m_file);
00667         if (m_options.defaultEncodingExplicitySet) {
00668             QTextCodec *codec = KGlobal::charsets()->codecForName(m_options.encoding);
00669             if (codec)
00670                 m_inputStream->setCodec(codec); //QTextCodec::codecForName("CP1250"));
00671         }
00672         if (m_detectDelimiter) {
00673             const QString delimiter( detectDelimiterByLookingAtFirstBytesOfFile(*m_inputStream) );
00674             if (m_delimiterWidget->delimiter() != delimiter)
00675                 m_delimiterWidget->setDelimiter( delimiter );
00676         }
00677     }
00678     const QChar delimiter(m_delimiterWidget->delimiter()[0]);
00679     m_stoppedAt_MAX_BYTES_TO_PREVIEW = false;
00680     int progressStep = 0;
00681     if (m_importingProgressDlg)
00682         progressStep = QMAX( 1, m_importingProgressDlg->progressBar()->totalSteps()/200 );
00683     int offset = 0;
00684     for (;!m_inputStream->atEnd(); offset++)
00685     {
00686 //disabled: this breaks wide spreadsheets
00687 //  if (column >= m_maximumRowsForPreview)
00688 //      return true;
00689 
00690         if (m_importingProgressDlg && ((offset % progressStep) < 5)) {
00691             //update progr. bar dlg on final exporting
00692             m_importingProgressDlg->progressBar()->setValue(offset);
00693             qApp->processEvents();
00694             if (m_importingProgressDlg->wasCancelled()) {
00695                 delete m_importingProgressDlg;
00696                 m_importingProgressDlg = 0;
00697                 return ::cancelled;
00698             }
00699         }
00700 
00701         (*m_inputStream) >> x; // read one char
00702 
00703         if (x == '\r') {
00704             continue; // eat '\r', to handle RFC-compliant files
00705         }
00706         if (offset==0 && x.unicode()==0xfeff) {
00707             // Ignore BOM, the "Byte Order Mark" 
00708             // (http://en.wikipedia.org/wiki/Byte_Order_Mark, // http://www.unicode.org/charts/PDF/UFFF0.pdf)
00709             // Probably fixed in Qt4.
00710             continue;
00711         }
00712 
00713         switch (state)
00714         {
00715         case S_START :
00716             if (x == m_textquote)
00717             {
00718                 state = S_QUOTED_FIELD;
00719             }
00720             else if (x == delimiter)
00721             {
00722                 setText(row - m_startline, column, field, inGUI);
00723                 field = QString::null;
00724                 if ((ignoreDups == false) || (lastCharDelimiter == false))
00725                     ++column;
00726                 lastCharDelimiter = true;
00727             }
00728             else if (x == '\n')
00729             {
00730                 if (!inGUI) {
00731                     //fill remaining empty fields (database wants them explicitly)
00732                     for (int additionalColumn = column; additionalColumn <= maxColumn; additionalColumn++) {
00733                         setText(row - m_startline, additionalColumn, QString::null, inGUI);
00734                     }
00735                 }
00736                 nextRow = true;
00737                 maxColumn = QMAX( maxColumn, column );
00738                 column = 1;
00739             }
00740             else
00741             {
00742                 field += x;
00743                 state = S_MAYBE_NORMAL_FIELD;
00744             }
00745             break;
00746         case S_QUOTED_FIELD :
00747             if (x == m_textquote)
00748             {
00749                 state = S_MAYBE_END_OF_QUOTED_FIELD;
00750             }
00751 /*allow \n inside quoted fields
00752             else if (x == '\n')
00753             {
00754                 setText(row - m_startline, column, field, inGUI);
00755                 field = "";
00756                 if (x == '\n')
00757                 {
00758                     nextRow = true;
00759                     maxColumn = QMAX( maxColumn, column );
00760                     column = 1;
00761                 }
00762                 else
00763                 {
00764                     if ((ignoreDups == false) || (lastCharDelimiter == false))
00765                         ++column;
00766                     lastCharDelimiter = true;
00767                 }
00768                 state = S_START;
00769             }*/
00770             else
00771             {
00772                 field += x;
00773             }
00774             break;
00775         case S_MAYBE_END_OF_QUOTED_FIELD :
00776             if (x == m_textquote)
00777             {
00778                 field += x; //no, this was just escaped quote character
00779                 state = S_QUOTED_FIELD;
00780             }
00781             else if (x == delimiter || x == '\n')
00782             {
00783                 setText(row - m_startline, column, field, inGUI);
00784                 field = QString::null;
00785                 if (x == '\n')
00786                 {
00787                     nextRow = true;
00788                     maxColumn = QMAX( maxColumn, column );
00789                     column = 1;
00790                 }
00791                 else
00792                 {
00793                     if ((ignoreDups == false) || (lastCharDelimiter == false))
00794                         ++column;
00795                     lastCharDelimiter = true;
00796                 }
00797                 state = S_START;
00798             }
00799             else
00800             {
00801                 state = S_END_OF_QUOTED_FIELD;
00802             }
00803             break;
00804         case S_END_OF_QUOTED_FIELD :
00805             if (x == delimiter || x == '\n')
00806             {
00807                 setText(row - m_startline, column, field, inGUI);
00808                 field = QString::null;
00809                 if (x == '\n')
00810                 {
00811                     nextRow = true;
00812                     maxColumn = QMAX( maxColumn, column );
00813                     column = 1;
00814                 }
00815                 else
00816                 {
00817                     if ((ignoreDups == false) || (lastCharDelimiter == false))
00818                         ++column;
00819                     lastCharDelimiter = true;
00820                 }
00821                 state = S_START;
00822             }
00823             else
00824             {
00825                 state = S_END_OF_QUOTED_FIELD;
00826             }
00827             break;
00828         case S_MAYBE_NORMAL_FIELD :
00829             if (x == m_textquote)
00830             {
00831                 field = QString::null;
00832                 state = S_QUOTED_FIELD;
00833                 break;
00834             }
00835         case S_NORMAL_FIELD :
00836             if (x == delimiter || x == '\n')
00837             {
00838                 setText(row - m_startline, column, field, inGUI);
00839                 field = QString::null;
00840                 if (x == '\n')
00841                 {
00842                     nextRow = true;
00843                     maxColumn = QMAX( maxColumn, column );
00844                     column = 1;
00845                 }
00846                 else
00847                 {
00848                     if ((ignoreDups == false) || (lastCharDelimiter == false))
00849                         ++column;
00850                     lastCharDelimiter = true;
00851                 }
00852                 state = S_START;
00853             }
00854             else
00855             {
00856                 field += x;
00857             }
00858         }
00859         if (x != delimiter)
00860             lastCharDelimiter = false;
00861 
00862         if (nextRow) {
00863             if (!inGUI && row==1 && m_1stRowForFieldNames->isChecked()) {
00864                 // do not save to the database 1st row if it contains column names
00865                 m_importingStatement->clearArguments();
00866             }
00867             else if (!saveRow(inGUI))
00868                 return false;
00869     
00870             ++row;
00871         }
00872 
00873         if (m_firstFillTableCall && row==2 
00874             && !m_1stRowForFieldNames->isChecked() && m_1stRowForFieldNamesDetected) 
00875         {
00876             //'1st row for field name' flag detected: reload table
00877             m_1stRowForFieldNamesDetected = false;
00878             m_table->setNumRows( 0 );
00879             m_firstFillTableCall = false; //this trick is allowed only once, on startup
00880             m_1stRowForFieldNames->setChecked(true); //this will reload table
00881             //slot1stRowForFieldNamesChanged(1);
00882             m_blockUserEvents = false;
00883             repaint();
00884             return false;
00885         }
00886 
00887         if (!m_importingProgressDlg && row % 20 == 0) {
00888             qApp->processEvents();
00889             //only for GUI mode:
00890             if (!m_firstFillTableCall && m_loadingProgressDlg && m_loadingProgressDlg->wasCancelled()) {
00891                 delete m_loadingProgressDlg;
00892                 m_loadingProgressDlg = 0;
00893                 m_dialogCancelled = true;
00894                 reject();
00895                 return false;
00896             }
00897         }
00898 
00899         if (!m_firstFillTableCall && m_loadingProgressDlg) {
00900             m_loadingProgressDlg->progressBar()->setValue(QMIN(m_maximumRowsForPreview, row));
00901         }
00902 
00903         if ( inGUI && row > (m_maximumRowsForPreview + (m_1stRowForFieldNamesDetected?1:0)) ) {
00904             kexipluginsdbg << "KexiCSVImportDialog::fillTable() loading stopped at row #" 
00905                 << m_maximumRowsForPreview << endl;
00906             break;
00907         }
00908         if (nextRow) {
00909             nextRow = false;
00910             //additional speedup: stop processing now if too many bytes were loaded for preview
00911             kexipluginsdbg << offset << endl;
00912             if (inGUI && offset >= m_maximumBytesForPreview && row >= 2) {
00913                 m_stoppedAt_MAX_BYTES_TO_PREVIEW = true;
00914                 return true;
00915             }
00916         }
00917     }
00918     return true;
00919 }
00920 
00921 void KexiCSVImportDialog::updateColumnText(int col)
00922 {
00923     QString colName;
00924     if (col<(int)m_columnNames.count() && (m_1stRowForFieldNames->isChecked() || m_changedColumnNames[col]))
00925         colName = m_columnNames[ col ];
00926     if (colName.isEmpty()) {
00927         colName = i18n("Column %1").arg(col+1); //will be changed to a valid identifier on import
00928         m_changedColumnNames[ col ] = false;
00929     }
00930     int detectedType = m_detectedTypes[col];
00931     if (detectedType==_FP_NUMBER_TYPE)
00932         detectedType=_NUMBER_TYPE; //we're simplifying that for now
00933     else if (detectedType==_NO_TYPE_YET) {
00934         m_detectedTypes[col]=_TEXT_TYPE; //entirely empty column
00935         detectedType=_TEXT_TYPE;
00936     }
00937     m_table->horizontalHeader()->setLabel(col, 
00938         i18n("Column %1").arg(col+1) + "  \n(" + m_typeNames[ detectedType ] + ")  ");
00939     m_table->setText(0, col, colName);
00940     m_table->horizontalHeader()->adjustHeaderSize();
00941 
00942     //check uniqueness
00943     QValueList<int> *list = m_uniquenessTest[col];
00944     if (m_primaryKeyColumn==-1 && list && !list->isEmpty()) {
00945         qHeapSort(*list);
00946         QValueList<int>::ConstIterator it=list->constBegin();
00947         int prevValue = *it;
00948         ++it;
00949         for(; it!=list->constEnd() && prevValue!=(*it); ++it)
00950             prevValue=(*it);
00951         if (it!=list->constEnd()) {
00952             //duplicates:
00953             list->clear();
00954         }
00955         else {
00956             //a candidate for PK (autodetected)!
00957             if (-1==m_primaryKeyColumn) {
00958                 m_primaryKeyColumn=col;
00959             }
00960         }
00961     }
00962     if (list) //not needed now: conserve memory
00963         list->clear();
00964 }
00965 
00966 void KexiCSVImportDialog::detectTypeAndUniqueness(int row, int col, const QString& text)
00967 {
00968     int intValue;
00969     const int type = m_detectedTypes[col];
00970     if (row==1 || type!=_TEXT_TYPE) {
00971         bool found = false;
00972         if (text.isEmpty() && type==_NO_TYPE_YET)
00973             found = true; //real type should be found later
00974         //detect type because it's 1st row or all prev. rows were not text
00975         //-FP number? (trying before "number" type is a must)
00976         if (!found && (row==1 || type==_NUMBER_TYPE || type==_FP_NUMBER_TYPE || type==_NO_TYPE_YET)) {
00977             bool ok = text.isEmpty() || m_fpNumberRegExp.exactMatch(text);
00978             //if (!ok)
00979             //  text.toDouble(&ok);
00980             if (ok && (row==1 || type==_NUMBER_TYPE || type==_FP_NUMBER_TYPE || type==_NO_TYPE_YET)) {
00981                 m_detectedTypes[col]=_FP_NUMBER_TYPE;
00982                 found = true; //yes
00983             }
00984         }
00985         //-number?
00986         if (!found && (row==1 || type==_NUMBER_TYPE || type==_NO_TYPE_YET)) {
00987             bool ok = text.isEmpty();//empty values allowed
00988             if (!ok)
00989                 intValue = text.toInt(&ok);
00990             if (ok && (row==1 || type==_NO_TYPE_YET)) {
00991                 m_detectedTypes[col]=_NUMBER_TYPE;
00992                 found = true; //yes
00993             }
00994         }
00995         //-date?
00996         if (!found && (row==1 || type==_DATE_TYPE || type==_NO_TYPE_YET)) {
00997             if ((row==1 || type==_NO_TYPE_YET)
00998                 && (text.isEmpty() || m_dateRegExp.exactMatch(text)))
00999             {
01000                 m_detectedTypes[col]=_DATE_TYPE;
01001                 found = true; //yes
01002             }
01003         }
01004         //-time?
01005         if (!found && (row==1 || type==_TIME_TYPE || type==_NO_TYPE_YET)) {
01006             if ((row==1 || type==_NO_TYPE_YET)
01007                 && (text.isEmpty() || m_timeRegExp1.exactMatch(text) || m_timeRegExp2.exactMatch(text)))
01008             {
01009                 m_detectedTypes[col]=_TIME_TYPE;
01010                 found = true; //yes
01011             }
01012         }
01013         //-date/time?
01014         if (!found && (row==1 || type==_TIME_TYPE || type==_NO_TYPE_YET)) {
01015             if (row==1 || type==_NO_TYPE_YET) {
01016                 bool detected = text.isEmpty();
01017                 if (!detected) {
01018                     const QStringList dateTimeList( QStringList::split(" ", text) );
01019                     bool ok = dateTimeList.count()>=2;
01022                     if (ok) {
01023                         //try all combinations
01024                         QString datePart( dateTimeList[0].stripWhiteSpace() );
01025                         QString timePart( dateTimeList[1].stripWhiteSpace() );
01026                         ok = m_dateRegExp.exactMatch(datePart)
01027                             && (m_timeRegExp1.exactMatch(timePart) || m_timeRegExp2.exactMatch(timePart));
01028                     }
01029                     detected = ok;
01030                 }
01031                 if (detected) {
01032                     m_detectedTypes[col]=_DATETIME_TYPE;
01033                     found = true; //yes
01034                 }
01035             }
01036         }
01037         if (!found && type==_NO_TYPE_YET && !text.isEmpty()) {
01038             //eventually, a non-emptytext after a while
01039             m_detectedTypes[col]=_TEXT_TYPE;
01040             found = true; //yes
01041         }
01042         //default: text type (already set)
01043     }
01044     //check uniqueness for this value
01045     QValueList<int> *list = m_uniquenessTest[col];
01046     if (row==1 && (!list || !list->isEmpty()) && !text.isEmpty() && _NUMBER_TYPE == m_detectedTypes[col]) {
01047         if (!list) {
01048             list = new QValueList<int>();
01049             m_uniquenessTest.insert(col, list);
01050         }
01051         list->append( intValue );
01052     }
01053     else {
01054         //the value is empty or uniqueness test failed in the past
01055         if (list && !list->isEmpty())
01056             list->clear(); //indicate that uniqueness test failed
01057     }
01058 }
01059 
01060 bool KexiCSVImportDialog::parseDate(const QString& text, QDate& date)
01061 {
01062     if (!m_dateRegExp.exactMatch(text))
01063         return false;
01064     //dddd - dd - dddd
01065     //1    2 3  4 5    <- pos
01066     const int d1 = m_dateRegExp.cap(1).toInt(), d3 = m_dateRegExp.cap(3).toInt(), d5 = m_dateRegExp.cap(5).toInt();
01067     if (m_dateRegExp.cap(2)=="/") //probably separator for american format mm/dd/yyyy
01068         date = QDate(d5, d1, d3);
01069     else {
01070         if (d5 > 31) //d5 == year
01071             date = QDate(d5, d3, d1);
01072         else //d1 == year
01073             date = QDate(d1, d3, d5);
01074     }
01075     return date.isValid();
01076 }
01077 
01078 bool KexiCSVImportDialog::parseTime(const QString& text, QTime& time)
01079 {
01080     time = QTime::fromString(text, Qt::ISODate); //same as m_timeRegExp1
01081     if (time.isValid())
01082         return true;
01083     if (m_timeRegExp2.exactMatch(text)) { //hh:mm:ss
01084         time = QTime(m_timeRegExp2.cap(1).toInt(), m_timeRegExp2.cap(3).toInt(), m_timeRegExp2.cap(5).toInt());
01085         return true;
01086     }
01087     return false;
01088 }
01089 
01090 void KexiCSVImportDialog::setText(int row, int col, const QString& text, bool inGUI)
01091 {
01092     if (!inGUI) {
01093         //save text directly to database buffer
01094         if (col==1) { //1st col
01095             m_importingStatement->clearArguments();
01096             if (m_implicitPrimaryKeyAdded)
01097                 *m_importingStatement << QVariant(); //id will be autogenerated here
01098         }
01099         const int detectedType = m_detectedTypes[col-1];
01100         if (detectedType==_NUMBER_TYPE) {
01101             *m_importingStatement << ( text.isEmpty() ? QVariant() : text.toInt() );
01103         }
01104         else if (detectedType==_FP_NUMBER_TYPE) {
01105             //replace ',' with '.'
01106             QCString t(text.latin1());
01107             const int textLen = t.length();
01108             for (int i=0; i<textLen; i++) {
01109                 if (t.at(i)==',') {
01110                     t.at(i) = '.';
01111                     break;
01112                 }
01113             }
01114             *m_importingStatement << ( t.isEmpty() ? QVariant() : t.toDouble() );
01115         }
01116         else if (detectedType==_DATE_TYPE) {
01117             QDate date;
01118             if (parseDate(text, date))
01119                 *m_importingStatement << date;
01120         }
01121         else if (detectedType==_TIME_TYPE) {
01122             QTime time;
01123             if (parseTime(text, time))
01124                 *m_importingStatement << time;
01125         }
01126         else if (detectedType==_DATETIME_TYPE) {
01127             QStringList dateTimeList( QStringList::split(" ", text) );
01128             if (dateTimeList.count()<2)
01129                 dateTimeList = QStringList::split("T", text); //also support ISODateTime's "T" separator
01131             if (dateTimeList.count()>=2) {
01132                 QString datePart( dateTimeList[0].stripWhiteSpace() );
01133                 QDate date;
01134                 if (parseDate(datePart, date)) {
01135                     QString timePart( dateTimeList[1].stripWhiteSpace() );
01136                     QTime time;
01137                     if (parseTime(timePart, time))
01138                         *m_importingStatement << QDateTime(date, time);
01139                 }
01140             }
01141         }
01142         else //_TEXT_TYPE and the rest
01143             *m_importingStatement << (m_options.stripWhiteSpaceInTextValuesChecked ? text.stripWhiteSpace() : text);
01144         return;
01145     }
01146     //save text to GUI (table view)
01147     if (m_table->numCols() < col) {
01148         m_table->setNumCols(col);
01149         if ((int)m_columnNames.size() < m_table->numCols()) {
01150             m_columnNames.resize(m_table->numCols()+10);
01151             m_changedColumnNames.resize(m_table->numCols()+10);
01152         }
01153     }
01154 
01155     if (m_1stRowForFieldNames->isChecked()) {
01156         if ((row+m_startline)==1) {//this is for column name
01157             if ((col-1) < (int)m_changedColumnNames.size() && false==m_changedColumnNames[col-1]) {
01158                 //this column has no custom name entered by a user
01159                 //-get the name from the data cell
01160                 QString colName(text.simplifyWhiteSpace());
01161                 if (!colName.isEmpty()) {
01162                     if (colName.left(1)>="0" && colName.left(1)<="9")
01163                         colName.prepend(i18n("Column")+" ");
01164                     m_columnNames[ col-1 ] = colName;
01165                 }
01166             }
01167             return;
01168         }
01169     }
01170     else {
01171         if ((row+m_startline)==1) {//this row is for column name
01172             if (m_1stRowForFieldNamesDetected && !m_1stRowForFieldNames->isChecked()) {
01173                 QString f( text.simplifyWhiteSpace() );
01174                 if (f.isEmpty() || !f[0].isLetter())
01175                     m_1stRowForFieldNamesDetected = false; //this couldn't be a column name
01176             }
01177         }
01178         row++; //1st row was for column names
01179     }
01180 
01181     if (row < 2) // skipped by the user
01182         return;
01183 
01184     if (m_table->numRows() < row) {
01185 //      if (m_maximumRowsForPreview >= row+100)
01186         m_table->setNumRows(row+100); /* We add more rows at a time to limit recalculations */
01187         //else
01188 //          m_table->setNumRows(m_maximumRowsForPreview);
01189         m_table->verticalHeader()->setLabel(0, i18n("Column name")+"   ");
01190         m_adjustRows=true;
01191     }
01192 
01193     m_table->setText(row - 1, col - 1, (m_options.stripWhiteSpaceInTextValuesChecked ? text.stripWhiteSpace() : text));
01194     m_table->verticalHeader()->setLabel(row-1, QString::number(row-1));
01195 
01196     detectTypeAndUniqueness(row-1, col-1, text);
01197 }
01198 
01199 bool KexiCSVImportDialog::saveRow(bool inGUI)
01200 {
01201     if (inGUI) {
01202         //nothing to do
01203         return true;
01204     }
01205     //save db buffer
01206     bool res = m_importingStatement->execute();
01207 //todo: move
01208     m_importingStatement->clearArguments();
01209     return res;
01210 //  return m_conn->insertRecord(*m_destinationTableSchema, m_dbRowBuffer);
01211 }
01212 
01213 void KexiCSVImportDialog::adjustRows(int iRows)
01214 {
01215     if (m_adjustRows)
01216     {
01217         m_table->setNumRows( iRows );
01218         m_adjustRows=false;
01219         for (int i = 0; i<iRows; i++)
01220             m_table->adjustRow(i);
01221     }
01222 }
01223 
01224 void KexiCSVImportDialog::formatChanged(int id)
01225 {
01226     if (id==_PK_FLAG) {
01227         if (m_primaryKeyColumn>=0 && m_primaryKeyColumn<m_table->numCols()) {
01228             m_table->setPixmap(0, m_primaryKeyColumn, QPixmap());
01229         }
01230         if (m_primaryKeyField->isChecked()) {
01231             m_primaryKeyColumn = m_table->currentColumn();
01232             m_table->setPixmap(0, m_primaryKeyColumn, m_pkIcon);
01233         }
01234         else
01235             m_primaryKeyColumn = -1;
01236         return;
01237     }
01238     else {
01239         m_detectedTypes[m_table->currentColumn()]=id;
01240         m_primaryKeyField->setEnabled( _NUMBER_TYPE == id );
01241         m_primaryKeyField->setChecked( m_primaryKeyColumn == m_table->currentColumn() && m_primaryKeyField->isEnabled() );
01242     }
01243     updateColumnText(m_table->currentColumn());
01244 }
01245 
01246 void KexiCSVImportDialog::delimiterChanged(const QString& delimiter)
01247 {
01248     Q_UNUSED(delimiter);
01249     m_columnsAdjusted = false;
01250     m_detectDelimiter = false; //selected by hand: do not detect in the future
01251     //delayed, otherwise combobox won't be repainted
01252     fillTableLater();
01253 }
01254 
01255 void KexiCSVImportDialog::textquoteSelected(int)
01256 {
01257     const QString tq(m_comboQuote->textQuote());
01258     if (tq.isEmpty())
01259         m_textquote = 0;
01260     else
01261         m_textquote = tq[0];
01262 
01263     kexipluginsdbg << "KexiCSVImportDialog::textquoteSelected(): " << m_textquote << endl;
01264 
01265     //delayed, otherwise combobox won't be repainted
01266     fillTableLater();
01267 }
01268 
01269 void KexiCSVImportDialog::fillTableLater()
01270 {
01271     m_table->setNumRows( 0 );
01272     QTimer::singleShot(10, this, SLOT(fillTable()));
01273 }
01274 
01275 void KexiCSVImportDialog::startlineSelected(int startline)
01276 {
01277 //  const int startline = line.toInt() - 1;
01278     if (m_startline == (startline-1))
01279         return;
01280     m_startline = startline-1;
01281     m_adjustRows=true;
01282     fillTable();
01283     m_table->setFocus();
01284 }
01285 
01286 void KexiCSVImportDialog::currentCellChanged(int, int col)
01287 {
01288     if (m_prevSelectedCol==col)
01289         return;
01290     m_prevSelectedCol = col;
01291     int type = m_detectedTypes[col];
01292     if (type==_FP_NUMBER_TYPE)
01293         type=_NUMBER_TYPE; //we're simplifying that for now
01294 
01295     m_formatCombo->setCurrentItem( type );
01296     m_formatLabel->setText( m_formatComboText.arg(col+1) );
01297     m_primaryKeyField->setEnabled( _NUMBER_TYPE == m_detectedTypes[col]);
01298     m_primaryKeyField->blockSignals(true); //block to disable executing slotPrimaryKeyFieldToggled()
01299      m_primaryKeyField->setChecked( m_primaryKeyColumn == col );
01300     m_primaryKeyField->blockSignals(false);
01301 }
01302 
01303 void KexiCSVImportDialog::cellValueChanged(int row,int col)
01304 {
01305     if (row==0) {//column name has changed
01306         m_columnNames[ col ] = m_table->text(row, col);
01307         m_changedColumnNames.setBit( col );
01308     }
01309 }
01310 
01311 void KexiCSVImportDialog::accept()
01312 {
01314 
01315     KexiGUIMessageHandler msg; 
01316 
01317     const uint numRows( m_table->numRows() );
01318     if (numRows == 0)
01319         return; //impossible
01320 
01321     if (numRows == 1) {
01322         if (KMessageBox::No == KMessageBox::questionYesNo(this, 
01323             i18n("Data set contains no rows. Do you want to import empty table?")))
01324             return;
01325     }
01326 
01327     KexiProject* project = m_mainWin->project();
01328     if (!project) {
01329         msg.showErrorMessage(i18n("No project available."));
01330         return;
01331     }
01332     m_conn = project->dbConnection(); //cache this pointer
01333     if (!m_conn) {
01334         msg.showErrorMessage(i18n("No database connection available."));
01335         return;
01336     }
01337     KexiPart::Part *part = Kexi::partManager().partForMimeType("kexi/table");
01338     if (!part) {
01339         msg.showErrorMessage(&Kexi::partManager());
01340         return;
01341     }
01342 
01343     //get suggested name based on the file name
01344     QString suggestedName;
01345     if (m_mode==File) {
01346         suggestedName = KURL::fromPathOrURL(m_fname).fileName();
01347         //remove extension
01348         if (!suggestedName.isEmpty()) {
01349             const int idx = suggestedName.findRev(".");
01350             if (idx!=-1)
01351                 suggestedName = suggestedName.mid(0, idx ).simplifyWhiteSpace();
01352         }
01353     }
01354 
01355     //-new part item
01356     KexiPart::Item* partItemForSavedTable = project->createPartItem(part->info(), suggestedName);
01357     if (!partItemForSavedTable) {
01358     //      msg.showErrorMessage(project);
01359         return;
01360     }
01361 
01362 #define _ERR \
01363     { project->deleteUnstoredItem(partItemForSavedTable); \
01364       m_conn = 0; \
01365       delete m_destinationTableSchema; \
01366       m_destinationTableSchema = 0; \
01367     return; }
01368 
01369     //-ask for table name/title
01370     // (THIS IS FROM KexiMainWindowImpl::saveObject())
01371     bool allowOverwriting = true;
01372     tristate res = m_mainWin->getNewObjectInfo( partItemForSavedTable, part, allowOverwriting );
01373     if (~res || !res) {
01375         _ERR;
01376     }
01377     //(allowOverwriting is now set to true, if user accepts overwriting, 
01378     // and overwriting will be needed)
01379 
01380 //  KexiDB::SchemaData sdata(part->info()->projectPartID());
01381 //  sdata.setName( partItem->name() );
01382 
01383     //-create table schema (and thus schema object)
01384     //-assign information (THIS IS FROM KexiDialogBase::storeNewData())
01385     m_destinationTableSchema = new KexiDB::TableSchema(partItemForSavedTable->name());
01386     m_destinationTableSchema->setCaption( partItemForSavedTable->caption() );
01387     m_destinationTableSchema->setDescription( partItemForSavedTable->description() );
01388     const uint numCols( m_table->numCols() );
01389 
01390     m_implicitPrimaryKeyAdded = false;
01391     //add PK if user wanted it
01392     int msgboxResult;
01393     if (m_primaryKeyColumn==-1
01394         && KMessageBox::No != (msgboxResult = KMessageBox::questionYesNoCancel(this, 
01395             i18n("No Primary Key (autonumber) has been defined.\n"
01396             "Should it be automatically defined on import (recommended)?\n\n"
01397             "Note: An imported table without a Primary Key may not be editable (depending on database type)."),
01398             QString::null, KGuiItem(i18n("Add Database Primary Key to a Table", "Add Primary Key"), "key"),
01399             KGuiItem(i18n("Do Not Add Database Primary Key to a Table", "Do Not Add")))))
01400     {
01401         if (msgboxResult == KMessageBox::Cancel)
01402             _ERR; //cancel accepting
01403 
01404         //add implicit PK field
01406         m_implicitPrimaryKeyAdded = true;
01407 
01408         QString fieldName("id");
01409         QString fieldCaption("Id");
01410 
01411         QStringList colnames;
01412         for (uint col = 0; col < numCols; col++)
01413             colnames.append( m_table->text(0, col).lower().simplifyWhiteSpace() );
01414 
01415         if (colnames.find(fieldName)!=colnames.end()) {
01416             int num = 1;
01417             while (colnames.find(fieldName+QString::number(num))!=colnames.end())
01418                 num++;
01419             fieldName += QString::number(num);
01420             fieldCaption += QString::number(num);
01421         }
01422         KexiDB::Field *field = new KexiDB::Field(
01423             fieldName,
01424             KexiDB::Field::Integer,
01425             KexiDB::Field::NoConstraints,
01426             KexiDB::Field::NoOptions,
01427             0,0, //uint length=0, uint precision=0,
01428             QVariant(), //QVariant defaultValue=QVariant(),
01429             fieldCaption
01430         ); //no description and width for now
01431         field->setPrimaryKey(true);
01432         field->setAutoIncrement(true);
01433         m_destinationTableSchema->addField( field );
01434     }
01435 
01436     for (uint col = 0; col < numCols; col++) {
01437         QString fieldCaption( m_table->text(0, col).simplifyWhiteSpace() );
01438         QString fieldName( KexiUtils::string2Identifier( fieldCaption ) );
01439         if (m_destinationTableSchema->field(fieldName)) {
01440             QString fixedFieldName;
01441             uint i = 2; //"apple 2, apple 3, etc. if there're many "apple" names
01442             do {
01443                 fixedFieldName = fieldName + "_" + QString::number(i);
01444                 if (!m_destinationTableSchema->field(fixedFieldName))
01445                     break;
01446                 i++;
01447             } while (true);
01448             fieldName = fixedFieldName;
01449             fieldCaption += (" " + QString::number(i));
01450         }
01451         const int detectedType = m_detectedTypes[col];
01452         KexiDB::Field::Type fieldType;
01453         if (detectedType==_DATE_TYPE)
01454             fieldType = KexiDB::Field::Date;
01455         if (detectedType==_TIME_TYPE)
01456             fieldType = KexiDB::Field::Time;
01457         if (detectedType==_DATETIME_TYPE)
01458             fieldType = KexiDB::Field::DateTime;
01459         else if (detectedType==_NUMBER_TYPE)
01460             fieldType = KexiDB::Field::Integer;
01461         else if (detectedType==_FP_NUMBER_TYPE)
01462             fieldType = KexiDB::Field::Double;
01464         else //_TEXT_TYPE and the rest
01465             fieldType = KexiDB::Field::Text;
01467 
01468         KexiDB::Field *field = new KexiDB::Field(
01469             fieldName,
01470             fieldType,
01471             KexiDB::Field::NoConstraints,
01472             KexiDB::Field::NoOptions,
01473             0,0, //uint length=0, uint precision=0,
01474             QVariant(), //QVariant defaultValue=QVariant(),
01475             fieldCaption
01476         ); //no description and width for now
01477 
01478         if ((int)col == m_primaryKeyColumn) {
01479             field->setPrimaryKey(true);
01480             field->setAutoIncrement(true);
01481         }
01482         m_destinationTableSchema->addField( field );
01483     }
01484 
01485     KexiDB::Transaction transaction = m_conn->beginTransaction();
01486     if (transaction.isNull()) {
01487         msg.showErrorMessage(m_conn);
01488         _ERR;
01489     }
01490     KexiDB::TransactionGuard tg(transaction);
01491 
01492     //-create physical table
01493     if (!m_conn->createTable(m_destinationTableSchema, allowOverwriting)) {
01494             msg.showErrorMessage(m_conn);
01495         _ERR;
01496     }
01497 
01498 #define _DROP_DEST_TABLE_AND_RETURN \
01499     { \
01500     if (m_importingProgressDlg) \
01501         m_importingProgressDlg->hide(); \
01502     project->deleteUnstoredItem(partItemForSavedTable); \
01503     m_conn->dropTable(m_destinationTableSchema); /*alsoRemoveSchema*/ \
01504     m_destinationTableSchema = 0; \
01505     m_conn = 0; \
01506     return; \
01507     }
01508 
01509     m_importingStatement = m_conn->prepareStatement(
01510         KexiDB::PreparedStatement::InsertStatement, *m_destinationTableSchema);
01511     if (!m_importingStatement) {
01512         msg.showErrorMessage(m_conn);
01513         _DROP_DEST_TABLE_AND_RETURN;
01514     }
01515 
01516     if (m_file) {
01517         if (!m_importingProgressDlg) {
01518             m_importingProgressDlg = new KProgressDialog( this, "m_importingProgressDlg", 
01519                 i18n("Importing CSV Data"), QString::null, true );
01520         }
01521         m_importingProgressDlg->setLabel(
01522             i18n("Importing CSV Data from <nobr>\"%1\"</nobr> into \"%2\" table...")
01523             .arg(QDir::convertSeparators(m_fname)).arg(m_destinationTableSchema->name()) );
01524         m_importingProgressDlg->progressBar()->setTotalSteps( QFileInfo(*m_file).size() );
01525         m_importingProgressDlg->show();
01526     }
01527 
01528     int row, column, maxColumn;
01529     QString field = QString::null;
01530 
01531     // main job
01532     res = loadRows(field, row, column, maxColumn, false  );
01533 
01534     delete m_importingProgressDlg;
01535   m_importingProgressDlg = 0;
01536     if (true != res) {
01537         //importing cancelled or failed
01538         if (!res) //do not display err msg when res == cancelled
01539             msg.showErrorMessage(m_conn);
01540         _DROP_DEST_TABLE_AND_RETURN;
01541     }
01542 
01543     // file with only one line without '\n'
01544     if (field.length() > 0)
01545     {
01546         setText(row - m_startline, column, field, false );
01547         //fill remaining empty fields (database wants them explicitly)
01548         for (int additionalColumn = column; additionalColumn <= maxColumn; additionalColumn++) {
01549             setText(row - m_startline, additionalColumn, QString::null, false );
01550         }
01551         if (!saveRow(false )) {
01552             msg.showErrorMessage(m_conn);
01553             _DROP_DEST_TABLE_AND_RETURN;
01554         }
01555         ++row;
01556         field = QString::null;
01557     }
01558 
01559     if (!tg.commit()) {
01560         msg.showErrorMessage(m_conn);
01561         _DROP_DEST_TABLE_AND_RETURN;
01562     }
01563 
01564     //-now we can store the item
01565     partItemForSavedTable->setIdentifier( m_destinationTableSchema->id() );
01566     project->addStoredItem( part->info(), partItemForSavedTable );
01567 
01568     QDialog::accept();
01569     KMessageBox::information(this, i18n("Data has been successfully imported to table \"%1\".")
01570         .arg(m_destinationTableSchema->name()));
01571     parentWidget()->raise();
01572     m_conn = 0;
01573 }
01574 
01575 int KexiCSVImportDialog::getHeader(int col)
01576 {
01577     QString header = m_table->horizontalHeader()->label(col);
01578 
01579     if (header == i18n("Text type for column", "Text"))
01580         return TEXT;
01581     else if (header == i18n("Numeric type for column", "Number"))
01582         return NUMBER;
01583     else if (header == i18n("Currency type for column", "Currency"))
01584         return CURRENCY;
01585     else
01586         return DATE;
01587 }
01588 
01589 QString KexiCSVImportDialog::getText(int row, int col)
01590 {
01591     return m_table->text(row, col);
01592 }
01593 
01594 void KexiCSVImportDialog::ignoreDuplicatesChanged(int)
01595 {
01596     fillTable();
01597 }
01598 
01599 void KexiCSVImportDialog::slot1stRowForFieldNamesChanged(int)
01600 {
01601     m_adjustRows=true;
01602     if (m_1stRowForFieldNames->isChecked() && m_startline>0 && m_startline>=(m_startAtLineSpinBox->maxValue()-1))
01603         m_startline--;
01604     fillTable();
01605 }
01606 
01607 void KexiCSVImportDialog::optionsButtonClicked()
01608 {
01609     KexiCSVImportOptionsDialog dlg(m_options, this);
01610     if (QDialog::Accepted != dlg.exec())
01611         return;
01612 
01613     KexiCSVImportOptions newOptions( dlg.options() );
01614     if (m_options != newOptions) {
01615         m_options = newOptions;
01616         if (!openData())
01617             return;
01618         fillTable();
01619     }
01620 }
01621 
01622 bool KexiCSVImportDialog::eventFilter ( QObject * watched, QEvent * e )
01623 {
01624     QEvent::Type t = e->type();
01625     // temporary disable keyboard and mouse events for time-consuming tasks
01626     if (m_blockUserEvents && (t==QEvent::KeyPress || t==QEvent::KeyRelease 
01627         || t==QEvent::MouseButtonPress || t==QEvent::MouseButtonDblClick
01628         || t==QEvent::Paint ))
01629         return true;
01630 
01631     if (watched == m_startAtLineSpinBox && t==QEvent::KeyPress) {
01632         QKeyEvent *ke = static_cast<QKeyEvent*>(e);
01633         if (ke->key()==Qt::Key_Enter || ke->key()==Qt::Key_Return) {
01634             m_table->setFocus();
01635             return true;
01636         }
01637     }
01638     return QDialog::eventFilter( watched, e );
01639 }
01640 
01641 void KexiCSVImportDialog::slotPrimaryKeyFieldToggled(bool on)
01642 {
01643     Q_UNUSED(on);
01644     formatChanged(_PK_FLAG);
01645 }
01646 
01647 void KexiCSVImportDialog::updateRowCountInfo()
01648 {
01649     m_infoLbl->setFileName( m_fname );
01650     if (m_allRowsLoadedInPreview) {
01651         m_infoLbl->setCommentText( 
01652             i18n("row count", "(rows: %1)").arg( m_table->numRows()-1+m_startline ) );
01653         QToolTip::remove( m_infoLbl );
01654     }
01655     else {
01656         m_infoLbl->setCommentText( 
01657             i18n("row count", "(rows: more than %1)").arg( m_table->numRows()-1+m_startline ) );
01658         QToolTip::add( m_infoLbl->commentLabel(), i18n("Not all rows are visible on this preview") );
01659     }
01660 }
01661 
01662 #include "kexicsvimportdialog.moc"
KDE Home | KDE Accessibility Home | Description of Access Keys