import_export/SQL/ImportSqlDatabaseTest.cpp

0001 /*
0002     File                 : ImportSqlDatabaseTest.h
0003     Project              : LabPlot
0004     Description          : Tests for the import from SQL databases
0005     --------------------------------------------------------------------
0006     SPDX-FileCopyrightText: 2023 Alexander Semke <alexander.semke@web.de>
0007     SPDX-License-Identifier: GPL-2.0-or-later
0008 */
0009
0010 #include "ImportSqlDatabaseTest.h"
0011 #include "backend/spreadsheet/Spreadsheet.h"
0012 #include "kdefrontend/datasources/ImportSQLDatabaseWidget.h"
0013
0014 #include <KConfig>
0015 #include <KConfigGroup>
0016
0017 void ImportSqlDatabaseTest::initTestCase() {
0018     // prepare the database connection
0019     QString m_configPath(QStandardPaths::standardLocations(QStandardPaths::AppDataLocation).constFirst() + QStringLiteral("sql_connections"));
0020     KConfig config(m_configPath, KConfig::SimpleConfig);
0021     KConfigGroup group = config.group(QStringLiteral("chinook"));
0022     group.writeEntry("Driver", QStringLiteral("QSQLITE"));
0023     group.writeEntry("DatabaseName", QFINDTESTDATA(QLatin1String("data/chinook.db")));
0024
0025     // needed in order to have the signals triggered by SignallingUndoCommand, see LabPlot.cpp
0026     // TODO: redesign/remove this
0027     qRegisterMetaType<const AbstractAspect*>("const AbstractAspect*");
0028     qRegisterMetaType<const AbstractColumn*>("const AbstractColumn*");
0029 }
0030
0031 // ##############################################################################
0032 // ########################  import from a table ################################
0033 // ##############################################################################
0034
0035 /*!
0036  * read the full table import in the replace mode
0037  */
0038 void ImportSqlDatabaseTest::testFullTableReplace() {
0039     // prepare the target spreadsheet
0040     Spreadsheet spreadsheet(QStringLiteral("test"), false);
0041
0042     // import the first table "artists"
0043     ImportSQLDatabaseWidget w;
0044     w.loadSettings();
0045     w.setCustomQuery(false);
0046     w.read(&spreadsheet, AbstractFileFilter::ImportMode::Replace);
0047
0048     // check the spreadsheet size and columns names and modes
0049     QCOMPARE(spreadsheet.rowCount(), 347);
0050     QCOMPARE(spreadsheet.columnCount(), 3);
0051
0052     QCOMPARE(spreadsheet.column(0)->name(), QLatin1String("AlbumId"));
0053     QCOMPARE(spreadsheet.column(1)->name(), QLatin1String("Title"));
0054     QCOMPARE(spreadsheet.column(2)->name(), QLatin1String("ArtistId"));
0055
0056     QCOMPARE(spreadsheet.column(0)->columnMode(), AbstractColumn::ColumnMode::Integer);
0057     QCOMPARE(spreadsheet.column(1)->columnMode(), AbstractColumn::ColumnMode::Text);
0058     QCOMPARE(spreadsheet.column(2)->columnMode(), AbstractColumn::ColumnMode::Integer);
0059 }
0060
0061 /*!
0062  * read the full table import in the append mode
0063  */
0064 void ImportSqlDatabaseTest::testFullTableAppend() {
0065     // prepare the target spreadsheet
0066     Spreadsheet spreadsheet(QStringLiteral("test"), false);
0067     spreadsheet.setColumnCount(1);
0068     spreadsheet.column(0)->setName(QStringLiteral("test"));
0069     spreadsheet.column(0)->setColumnMode(AbstractColumn::ColumnMode::Double);
0070
0071     // import the first table "artists"
0072     ImportSQLDatabaseWidget w;
0073     w.loadSettings();
0074     w.setCustomQuery(false);
0075     w.read(&spreadsheet, AbstractFileFilter::ImportMode::Append);
0076
0077     // check the spreadsheet size and columns names and modes
0078     QCOMPARE(spreadsheet.rowCount(), 347);
0079     QCOMPARE(spreadsheet.columnCount(), 4);
0080
0081     QCOMPARE(spreadsheet.column(0)->name(), QLatin1String("test"));
0082     QCOMPARE(spreadsheet.column(1)->name(), QLatin1String("AlbumId"));
0083     QCOMPARE(spreadsheet.column(2)->name(), QLatin1String("Title"));
0084     QCOMPARE(spreadsheet.column(3)->name(), QLatin1String("ArtistId"));
0085
0086     QCOMPARE(spreadsheet.column(0)->columnMode(), AbstractColumn::ColumnMode::Double);
0087     QCOMPARE(spreadsheet.column(1)->columnMode(), AbstractColumn::ColumnMode::Integer);
0088     QCOMPARE(spreadsheet.column(2)->columnMode(), AbstractColumn::ColumnMode::Text);
0089     QCOMPARE(spreadsheet.column(3)->columnMode(), AbstractColumn::ColumnMode::Integer);
0090 }
0091
0092 /*!
0093  * read the full table import in the prepand mode
0094  */
0095 void ImportSqlDatabaseTest::testFullTablePrepend() {
0096     // prepare the target spreadsheet
0097     Spreadsheet spreadsheet(QStringLiteral("test"), false);
0098     spreadsheet.setColumnCount(1);
0099     spreadsheet.column(0)->setName(QStringLiteral("test"));
0100     spreadsheet.column(0)->setColumnMode(AbstractColumn::ColumnMode::Double);
0101
0102     // import the first table "artists"
0103     ImportSQLDatabaseWidget w;
0104     w.loadSettings();
0105     w.setCustomQuery(false);
0106     w.read(&spreadsheet, AbstractFileFilter::ImportMode::Prepend);
0107
0108     // check the spreadsheet size and columns names and modes
0109     QCOMPARE(spreadsheet.rowCount(), 347);
0110     QCOMPARE(spreadsheet.columnCount(), 4);
0111
0112     QCOMPARE(spreadsheet.column(0)->name(), QLatin1String("AlbumId"));
0113     QCOMPARE(spreadsheet.column(1)->name(), QLatin1String("Title"));
0114     QCOMPARE(spreadsheet.column(2)->name(), QLatin1String("ArtistId"));
0115     QCOMPARE(spreadsheet.column(3)->name(), QLatin1String("test"));
0116
0117     QCOMPARE(spreadsheet.column(0)->columnMode(), AbstractColumn::ColumnMode::Integer);
0118     QCOMPARE(spreadsheet.column(1)->columnMode(), AbstractColumn::ColumnMode::Text);
0119     QCOMPARE(spreadsheet.column(2)->columnMode(), AbstractColumn::ColumnMode::Integer);
0120     QCOMPARE(spreadsheet.column(3)->columnMode(), AbstractColumn::ColumnMode::Double);
0121 }
0122
0123 void ImportSqlDatabaseTest::testFullTableCustomRowRange() {
0124     // prepare the target spreadsheet
0125     Spreadsheet spreadsheet(QStringLiteral("test"), false);
0126
0127     // import the records from 10 to 20 from the first table "artists"
0128     ImportSQLDatabaseWidget w;
0129     w.loadSettings();
0130     w.setCustomQuery(false);
0131     w.setStartRow(10);
0132     w.setEndRow(20);
0133     w.read(&spreadsheet, AbstractFileFilter::ImportMode::Replace);
0134
0135     // check the spreadsheet size and columns names and modes
0136     QCOMPARE(spreadsheet.rowCount(), 11);
0137     QCOMPARE(spreadsheet.columnCount(), 3);
0138
0139     QCOMPARE(spreadsheet.column(0)->name(), QLatin1String("AlbumId"));
0140     QCOMPARE(spreadsheet.column(1)->name(), QLatin1String("Title"));
0141     QCOMPARE(spreadsheet.column(2)->name(), QLatin1String("ArtistId"));
0142
0143     QCOMPARE(spreadsheet.column(0)->columnMode(), AbstractColumn::ColumnMode::Integer);
0144     QCOMPARE(spreadsheet.column(1)->columnMode(), AbstractColumn::ColumnMode::Text);
0145     QCOMPARE(spreadsheet.column(2)->columnMode(), AbstractColumn::ColumnMode::Integer);
0146
0147     // first row in the spreadsheet
0148     QCOMPARE(spreadsheet.column(0)->integerAt(0), 10);
0149     QCOMPARE(spreadsheet.column(1)->textAt(0), QLatin1String("Audioslave"));
0150     QCOMPARE(spreadsheet.column(2)->integerAt(0), 8);
0151
0152     // last row in the spreadsheet
0153     QCOMPARE(spreadsheet.column(0)->integerAt(10), 20);
0154     QCOMPARE(spreadsheet.column(1)->textAt(10), QLatin1String("The Best Of Buddy Guy - The Millenium Collection"));
0155     QCOMPARE(spreadsheet.column(2)->integerAt(10), 15);
0156 }
0157
0158 /*!
0159  * import the first two columns only
0160  */
0161 void ImportSqlDatabaseTest::testFullTableCustomColumnRange01() {
0162     // prepare the target spreadsheet
0163     Spreadsheet spreadsheet(QStringLiteral("test"), false);
0164
0165     // import the records from 10 to 20 from the first table "artists"
0166     ImportSQLDatabaseWidget w;
0167     w.loadSettings();
0168     w.setCustomQuery(false);
0169     w.setStartColumn(1);
0170     w.setEndColumn(2);
0171     w.read(&spreadsheet, AbstractFileFilter::ImportMode::Replace);
0172
0173     // check the spreadsheet size and columns names and modes
0174     QCOMPARE(spreadsheet.rowCount(), 347);
0175     QCOMPARE(spreadsheet.columnCount(), 2);
0176
0177     QCOMPARE(spreadsheet.column(0)->name(), QLatin1String("AlbumId"));
0178     QCOMPARE(spreadsheet.column(1)->name(), QLatin1String("Title"));
0179
0180     QCOMPARE(spreadsheet.column(0)->columnMode(), AbstractColumn::ColumnMode::Integer);
0181     QCOMPARE(spreadsheet.column(1)->columnMode(), AbstractColumn::ColumnMode::Text);
0182
0183     // first row in the spreadsheet
0184     QCOMPARE(spreadsheet.column(0)->integerAt(0), 1);
0185     QCOMPARE(spreadsheet.column(1)->textAt(0), QLatin1String("For Those About To Rock We Salute You"));
0186
0187     // last row in the spreadsheet
0188     QCOMPARE(spreadsheet.column(0)->integerAt(346), 347);
0189     QCOMPARE(spreadsheet.column(1)->textAt(346), QLatin1String("Koyaanisqatsi (Soundtrack from the Motion Picture)"));
0190 }
0191
0192 /*!
0193  * import the last two columns only
0194  */
0195 void ImportSqlDatabaseTest::testFullTableCustomColumnRange02() {
0196     // prepare the target spreadsheet
0197     Spreadsheet spreadsheet(QStringLiteral("test"), false);
0198
0199     // import the records from 10 to 20 from the first table "artists"
0200     ImportSQLDatabaseWidget w;
0201     w.loadSettings();
0202     w.setCustomQuery(false);
0203     w.setStartColumn(2);
0204     w.setEndColumn(3);
0205     w.read(&spreadsheet, AbstractFileFilter::ImportMode::Replace);
0206
0207     // check the spreadsheet size and columns names and modes
0208     QCOMPARE(spreadsheet.rowCount(), 347);
0209     QCOMPARE(spreadsheet.columnCount(), 2);
0210
0211     QCOMPARE(spreadsheet.column(0)->name(), QLatin1String("Title"));
0212     QCOMPARE(spreadsheet.column(1)->name(), QLatin1String("ArtistId"));
0213
0214     QCOMPARE(spreadsheet.column(0)->columnMode(), AbstractColumn::ColumnMode::Text);
0215     QCOMPARE(spreadsheet.column(1)->columnMode(), AbstractColumn::ColumnMode::Integer);
0216
0217     // first row in the spreadsheet
0218     QCOMPARE(spreadsheet.column(0)->textAt(0), QLatin1String("For Those About To Rock We Salute You"));
0219     QCOMPARE(spreadsheet.column(1)->integerAt(0), 1);
0220
0221     // last row in the spreadsheet
0222     QCOMPARE(spreadsheet.column(0)->textAt(346), QLatin1String("Koyaanisqatsi (Soundtrack from the Motion Picture)"));
0223     QCOMPARE(spreadsheet.column(1)->integerAt(346), 275);
0224 }
0225
0226 /*!
0227  * import the second column only
0228  */
0229 void ImportSqlDatabaseTest::testFullTableCustomColumnRange03() {
0230     // prepare the target spreadsheet
0231     Spreadsheet spreadsheet(QStringLiteral("test"), false);
0232
0233     // import the records from 10 to 20 from the first table "artists"
0234     ImportSQLDatabaseWidget w;
0235     w.loadSettings();
0236     w.setCustomQuery(false);
0237     w.setStartColumn(2);
0238     w.setEndColumn(2);
0239     w.read(&spreadsheet, AbstractFileFilter::ImportMode::Replace);
0240
0241     // check the spreadsheet size and columns names and modes
0242     QCOMPARE(spreadsheet.rowCount(), 347);
0243     QCOMPARE(spreadsheet.columnCount(), 1);
0244
0245     QCOMPARE(spreadsheet.column(0)->name(), QLatin1String("Title"));
0246     QCOMPARE(spreadsheet.column(0)->columnMode(), AbstractColumn::ColumnMode::Text);
0247
0248     // first row in the spreadsheet
0249     QCOMPARE(spreadsheet.column(0)->textAt(0), QLatin1String("For Those About To Rock We Salute You"));
0250
0251     // last row in the spreadsheet
0252     QCOMPARE(spreadsheet.column(0)->textAt(346), QLatin1String("Koyaanisqatsi (Soundtrack from the Motion Picture)"));
0253 }
0254
0255 /*!
0256  * import the second column only, records from 10 to 20
0257  */
0258 void ImportSqlDatabaseTest::testFullTableCustomRowColumnRange() {
0259     // prepare the target spreadsheet
0260     Spreadsheet spreadsheet(QStringLiteral("test"), false);
0261
0262     // import the records from 10 to 20 from the first table "artists"
0263     ImportSQLDatabaseWidget w;
0264     w.loadSettings();
0265     w.setCustomQuery(false);
0266     w.setStartRow(10);
0267     w.setEndRow(20);
0268     w.setStartColumn(2);
0269     w.setEndColumn(2);
0270     w.read(&spreadsheet, AbstractFileFilter::ImportMode::Replace);
0271
0272     // check the spreadsheet size and columns names and modes
0273     QCOMPARE(spreadsheet.rowCount(), 11);
0274     QCOMPARE(spreadsheet.columnCount(), 1);
0275
0276     QCOMPARE(spreadsheet.column(0)->name(), QLatin1String("Title"));
0277     QCOMPARE(spreadsheet.column(0)->columnMode(), AbstractColumn::ColumnMode::Text);
0278
0279     // first row in the spreadsheet
0280     QCOMPARE(spreadsheet.column(0)->textAt(0), QLatin1String("Audioslave"));
0281
0282     // last row in the spreadsheet
0283     QCOMPARE(spreadsheet.column(0)->textAt(10), QLatin1String("The Best Of Buddy Guy - The Millenium Collection"));
0284 }
0285
0286 // ##############################################################################
0287 // ##################  import the result of a custom query ######################
0288 // ##############################################################################
0289 void ImportSqlDatabaseTest::testQuery() {
0290     // prepare the target spreadsheet
0291     Spreadsheet spreadsheet(QStringLiteral("test"), false);
0292
0293     // import the resultset of a custom query
0294     ImportSQLDatabaseWidget w;
0295     w.loadSettings();
0296     w.setCustomQuery(true);
0297     w.setQuery(QLatin1String("select title from albums where title like '%best%';"));
0298     w.refreshPreview();
0299     w.read(&spreadsheet, AbstractFileFilter::ImportMode::Replace);
0300
0301     // check the spreadsheet size and columns names and modes
0302     QCOMPARE(spreadsheet.rowCount(), 15);
0303     QCOMPARE(spreadsheet.columnCount(), 1);
0304
0305     QCOMPARE(spreadsheet.column(0)->name(), QLatin1String("Title"));
0306     QCOMPARE(spreadsheet.column(0)->columnMode(), AbstractColumn::ColumnMode::Text);
0307
0308     // first row in the spreadsheet
0309     QCOMPARE(spreadsheet.column(0)->textAt(0), QLatin1String("The Best Of Billy Cobham"));
0310
0311     // last row in the spreadsheet
0312     QCOMPARE(spreadsheet.column(0)->textAt(14), QLatin1String("The Best of Beethoven"));
0313 }
0314
0315 QTEST_MAIN(ImportSqlDatabaseTest)