Differential D23859 Diff 73561 src/databaseinterface.cpp

Changeset View

Standalone View

View Options

src/databaseinterface.cpp

  • This file is larger than 256 KB, so syntax highlighting is disabled by default.
Show First 20 Lines • Show All 2815 Lines • ▼ Show 20 Line(s)2813 if (!result) {
2816 2816​
2817 Q_EMIT databaseError(); 2817​ Q_EMIT databaseError();
2818 } 2818​ }
2819 } 2819​ }
2820 2820​
2821 qCInfo(orgKdeElisaDatabase) << "finished update to v13 of database schema"; 2821​ qCInfo(orgKdeElisaDatabase) << "finished update to v13 of database schema";
2822} 2822​}
2823 2823​
2824void DatabaseInterface::upgradeDatabaseV15() ​
2825{ ​
2826 ​
2827} ​
2828 ​
2829void DatabaseInterface::upgradeDatabaseV16() ​
2830{ ​
2831 ​
2832} ​
2833 ​
2834void DatabaseInterface::upgradeDatabaseV14() 2824​void DatabaseInterface::upgradeDatabaseV14()
2835{ 2825​{
2836 qCInfo(orgKdeElisaDatabase) << "begin update to v14 of database schema"; 2826​ qCInfo(orgKdeElisaDatabase) << "begin update to v14 of database schema";
2837 2827​
2838 { 2828​ {
2839 QSqlQuery createSchemaQuery(d->mTracksDatabase); 2829​ QSqlQuery createSchemaQuery(d->mTracksDatabase);
2840 2830​
2841 const auto &result = createSchemaQuery.exec(QStringLiteral("CREATE TABLE `DatabaseVersionV14` (`Version` INTEGER PRIMARY KEY NOT NULL)")); 2831​ const auto &result = createSchemaQuery.exec(QStringLiteral("CREATE TABLE `DatabaseVersionV14` (`Version` INTEGER PRIMARY KEY NOT NULL)"));
Show All 22 Lines
2864 d->mTracksDatabase.transaction(); 2854​ d->mTracksDatabase.transaction();
2865 2855​
2866 { 2856​ {
2867 QSqlQuery createSchemaQuery(d->mTracksDatabase); 2857​ QSqlQuery createSchemaQuery(d->mTracksDatabase);
2868 2858​
2869 const auto &result = createSchemaQuery.exec(QStringLiteral("CREATE TABLE `Radios` (" 2859​ const auto &result = createSchemaQuery.exec(QStringLiteral("CREATE TABLE `Radios` ("
2870 "`ID` INTEGER PRIMARY KEY AUTOINCREMENT, " 2860​ "`ID` INTEGER PRIMARY KEY AUTOINCREMENT, "
2871 "`HttpAddress` VARCHAR(255) NOT NULL, " 2861​ "`HttpAddress` VARCHAR(255) NOT NULL, "
2872 "`Priority` INTEGER NOT NULL, " 2862​ "`Priority` INTEGER NOT NULL, "
mgallienUnsubmitted
Not Done

Even though this branch has not been released, can you bump the database version and do this modification via a database upgrade. That will be nicer for people using builds from trunk (think of early testers via the flatpak package built by KDE for example).

mgallien: Even though this branch has not been released, can you bump the database version and do this…
jguidonAuthorUnsubmitted
Not Done

Thanks for the feedback.

I wonder if there is a particular reason for creating some tables to check the database version? (DatabaseVersionV13, DatabaseVersionV14 ...). Maybe we could use on table with a version field? It could be more efficient/readable in DatabaseInterface::initDatabase ?

jguidon: Thanks for the feedback. I wonder if there is a particular reason for creating some tables to…
mgallienUnsubmitted
Not Done

The current solution was what I found enough to do the job.
Feel free to propose another solution via a review, I will be happy to let that evolve.

mgallien: The current solution was what I found enough to do the job. Feel free to propose another…
2873 "`Title` VARCHAR(85) NOT NULL, " 2863​ "`Title` VARCHAR(85) NOT NULL, "
2874 "`Rating` INTEGER NOT NULL DEFAULT 0, " 2864​ "`Rating` INTEGER NOT NULL DEFAULT 0, "
2875 "`Genre` VARCHAR(55), " 2865​ "`Genre` VARCHAR(55), "
2876 "`Comment` VARCHAR(255), " 2866​ "`Comment` VARCHAR(255), "
2877 "UNIQUE (" 2867​ "UNIQUE ("
2878 "`HttpAddress`" 2868​ "`HttpAddress`"
2879 "), " 2869​ "), "
2880 "UNIQUE (" 2870​ "UNIQUE ("
▲ Show 20 Lines • Show All 46 Lines • ▼ Show 20 Line(s)2914 if (!result) {
2927 2917​
2928 Q_EMIT databaseError(); 2918​ Q_EMIT databaseError();
2929 } 2919​ }
2930 } 2920​ }
2931 2921​
2932 qCInfo(orgKdeElisaDatabase) << "finished update to v14 of database schema"; 2922​ qCInfo(orgKdeElisaDatabase) << "finished update to v14 of database schema";
2933} 2923​}
2934 2924​
2925​void DatabaseInterface::upgradeDatabaseV15()
2926​{
2927​ qCInfo(orgKdeElisaDatabase) << "begin update to v15 of database schema";
2928​
2929​ {
2930​ QSqlQuery createSchemaQuery(d->mTracksDatabase);
2931​
2932​ const auto &result = createSchemaQuery.exec(QStringLiteral("CREATE TABLE `DatabaseVersionV15` (`Version` INTEGER PRIMARY KEY NOT NULL)"));
2933​
2934​ if (!result) {
2935​ qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::upgradeDatabaseV15" << createSchemaQuery.lastQuery();
2936​ qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::upgradeDatabaseV15" << createSchemaQuery.lastError();
2937​
2938​ Q_EMIT databaseError();
2939​ }
2940​ }
2941​
2942​ {
2943​ QSqlQuery disableForeignKeys(d->mTracksDatabase);
2944​
2945​ auto result = disableForeignKeys.exec(QStringLiteral(" PRAGMA foreign_keys=OFF"));
2946​
2947​ if (!result) {
2948​ qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::upgradeDatabaseV15" << disableForeignKeys.lastQuery();
2949​ qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::upgradeDatabaseV15" << disableForeignKeys.lastError();
2950​
2951​ Q_EMIT databaseError();
2952​ }
2953​ }
2954​
2955​ d->mTracksDatabase.transaction();
2956​
2957​ {
2958​ QSqlQuery createSchemaQuery(d->mTracksDatabase);
2959​
2960​ const auto &result = createSchemaQuery.exec(QStringLiteral("CREATE TABLE `RadiosNew` ("
2961​ "`ID` INTEGER PRIMARY KEY AUTOINCREMENT, "
2962​ "`HttpAddress` VARCHAR(255) NOT NULL, "
2963​ "`ImageAddress` VARCHAR(255) NOT NULL, "
2964​ "`Title` VARCHAR(85) NOT NULL, "
2965​ "`Rating` INTEGER NOT NULL DEFAULT 0, "
2966​ "`Genre` VARCHAR(55), "
2967​ "`Comment` VARCHAR(255), "
2968​ "UNIQUE ("
2969​ "`HttpAddress`"
2970​ "), "
2971​ "UNIQUE ("
2972​ "`Title`, `HttpAddress`"
2973​ ") "
2974​ "CONSTRAINT fk_tracks_genre FOREIGN KEY (`Genre`) REFERENCES `Genre`(`Name`))"
2975​ ));
2976​
2977​ if (!result) {
2978​ qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::upgradeDatabaseV15" << createSchemaQuery.lastQuery();
2979​ qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::upgradeDatabaseV15" << createSchemaQuery.lastError();
2980​
2981​ Q_EMIT databaseError();
2982​ }
2983​ }
2984​
2985​ {
2986​ QSqlQuery createSchemaQuery(d->mTracksDatabase);
2987​
2988​ const auto &result = createSchemaQuery.exec(QStringLiteral("INSERT INTO RadiosNew SELECT ID, HttpAddress, '', Title, Rating, Genre, Comment FROM Radios"));
2989​
2990​ if (!result) {
2991​ qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::upgradeDatabaseV15" << createSchemaQuery.lastQuery();
2992​ qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::upgradeDatabaseV15" << createSchemaQuery.lastError();
2993​
2994​ Q_EMIT databaseError();
2995​ }
2996​ }
2997​
2998​ {
2999​ QSqlQuery createSchemaQuery(d->mTracksDatabase);
3000​
3001​ const auto &result = createSchemaQuery.exec(QStringLiteral("DROP TABLE `Radios`"));
3002​
3003​ if (!result) {
3004​ qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::upgradeDatabaseV15" << createSchemaQuery.lastQuery();
3005​ qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::upgradeDatabaseV15" << createSchemaQuery.lastError();
3006​
3007​ Q_EMIT databaseError();
3008​ }
3009​ }
3010​
3011​ {
3012​ QSqlQuery createSchemaQuery(d->mTracksDatabase);
3013​
3014​ const auto &result = createSchemaQuery.exec(QStringLiteral("ALTER TABLE `RadiosNew` RENAME TO `Radios`"));
3015​
3016​ if (!result) {
3017​ qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::upgradeDatabaseV15" << createSchemaQuery.lastQuery();
3018​ qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::upgradeDatabaseV15" << createSchemaQuery.lastError();
3019​
3020​ Q_EMIT databaseError();
3021​ }
3022​ }
3023​
3024​ {
3025​ QSqlQuery createSchemaQuery(d->mTracksDatabase);
3026​ const auto &result = createSchemaQuery.exec(QStringLiteral("INSERT INTO `Radios` (`HttpAddress`, `ImageAddress`, `Title`) "
3027​ "SELECT 'https://chai5she.cdn.dvmr.fr/francemusique-lofi.mp3', 'https://static.radio.fr/images/broadcasts/07/f7/3366/c44.png', 'France Musique'"
3028​ ));
3029​ if (!result) {
3030​ qCInfo(orgKdeElisaDatabase) << "DatabaseInterface::initRequest" << createSchemaQuery.lastQuery();
3031​ qCInfo(orgKdeElisaDatabase) << "DatabaseInterface::initRequest" << createSchemaQuery.lastError();
3032​
3033​ Q_EMIT databaseError();
3034​ }
3035​ }
3036​
3037​ d->mTracksDatabase.commit();
3038​
3039​ {
3040​ QSqlQuery enableForeignKeys(d->mTracksDatabase);
3041​
3042​ auto result = enableForeignKeys.exec(QStringLiteral(" PRAGMA foreign_keys=ON"));
3043​
3044​ if (!result) {
3045​ qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::upgradeDatabaseV15" << enableForeignKeys.lastQuery();
3046​ qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::upgradeDatabaseV15" << enableForeignKeys.lastError();
3047​
3048​ Q_EMIT databaseError();
3049​ }
3050​ }
3051​
3052​ qCInfo(orgKdeElisaDatabase) << "finished update to v15 of database schema";
3053​}
3054​
3055​void DatabaseInterface::upgradeDatabaseV16()
3056​{
3057​
3058​}
3059​
2935void DatabaseInterface::checkDatabaseSchema() 3060​void DatabaseInterface::checkDatabaseSchema()
2936{ 3061​{
2937 checkAlbumsTableSchema(); 3062​ checkAlbumsTableSchema();
2938 if (d->mIsInBadState) 3063​ if (d->mIsInBadState)
2939 { 3064​ {
2940 resetDatabase(); 3065​ resetDatabase();
2941 return; 3066​ return;
2942 } 3067​ }
▲ Show 20 Lines • Show All 882 Lines • ▼ Show 20 Line(s)3830 {
3825 } 3950​ }
3826 } 3951​ }
3827 3952​
3828 { 3953​ {
3829 auto selectAllRadiosText = QStringLiteral("SELECT " 3954​ auto selectAllRadiosText = QStringLiteral("SELECT "
3830 "radios.`ID`, " 3955​ "radios.`ID`, "
3831 "radios.`Title`, " 3956​ "radios.`Title`, "
3832 "radios.`HttpAddress`, " 3957​ "radios.`HttpAddress`, "
3958​ "radios.`ImageAddress`, "
3833 "radios.`Rating`, " 3959​ "radios.`Rating`, "
3834 "trackGenre.`Name`, " 3960​ "trackGenre.`Name`, "
3835 "radios.`Comment` " 3961​ "radios.`Comment` "
3836 "FROM " 3962​ "FROM "
3837 "`Radios` radios " 3963​ "`Radios` radios "
3838 "LEFT JOIN `Genre` trackGenre ON trackGenre.`Name` = radios.`Genre` " 3964​ "LEFT JOIN `Genre` trackGenre ON trackGenre.`Name` = radios.`Genre` "
3839 ""); 3965​ "");
3840 3966​
▲ Show 20 Lines • Show All 915 Lines • ▼ Show 20 Line(s)4772 {
4756 } 4882​ }
4757 } 4883​ }
4758 4884​
4759 { 4885​ {
4760 auto selectRadioFromIdQueryText = QStringLiteral("SELECT " 4886​ auto selectRadioFromIdQueryText = QStringLiteral("SELECT "
4761 "radios.`ID`, " 4887​ "radios.`ID`, "
4762 "radios.`Title`, " 4888​ "radios.`Title`, "
4763 "radios.`HttpAddress`, " 4889​ "radios.`HttpAddress`, "
4890​ "radios.`ImageAddress`, "
4764 "radios.`Rating`, " 4891​ "radios.`Rating`, "
4765 "trackGenre.`Name`, " 4892​ "trackGenre.`Name`, "
4766 "radios.`Comment` " 4893​ "radios.`Comment` "
4767 "FROM " 4894​ "FROM "
4768 "`Radios` radios " 4895​ "`Radios` radios "
4769 "LEFT JOIN `Genre` trackGenre ON trackGenre.`Name` = radios.`Genre` " 4896​ "LEFT JOIN `Genre` trackGenre ON trackGenre.`Name` = radios.`Genre` "
4770 "WHERE " 4897​ "WHERE "
4771 "radios.`ID` = :radioId " 4898​ "radios.`ID` = :radioId "
▲ Show 20 Lines • Show All 654 Lines • ▼ Show 20 Line(s)
5426 5553​
5427 { 5554​ {
5428 auto insertRadioQueryText = QStringLiteral("INSERT INTO `Radios` " 5555​ auto insertRadioQueryText = QStringLiteral("INSERT INTO `Radios` "
5429 "(" 5556​ "("
5430 "`Title`, " 5557​ "`Title`, "
5431 "`httpAddress`, " 5558​ "`httpAddress`, "
5432 "`Comment`, " 5559​ "`Comment`, "
5433 "`Rating`, " 5560​ "`Rating`, "
5434 "`Priority`) " 5561​ "`ImageAddress`) "
5435 "VALUES " 5562​ "VALUES "
5436 "(" 5563​ "("
5437 ":title, " 5564​ ":title, "
5438 ":httpAddress, " 5565​ ":httpAddress, "
5439 ":comment, " 5566​ ":comment, "
5440 ":trackRating," 5567​ ":trackRating,"
5441 "1)"); 5568​ ":imageAddress)");
5442 5569​
5443 auto result = prepareQuery(d->mInsertRadioQuery, insertRadioQueryText); 5570​ auto result = prepareQuery(d->mInsertRadioQuery, insertRadioQueryText);
5444 5571​
5445 if (!result) { 5572​ if (!result) {
5446 qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::initRequest" << d->mInsertRadioQuery.lastQuery(); 5573​ qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::initRequest" << d->mInsertRadioQuery.lastQuery();
5447 qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::initRequest" << d->mInsertRadioQuery.lastError(); 5574​ qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::initRequest" << d->mInsertRadioQuery.lastError();
5448 5575​
5449 Q_EMIT databaseError(); 5576​ Q_EMIT databaseError();
Show All 15 Lines
5465 } 5592​ }
5466 5593​
5467 { 5594​ {
5468 auto updateRadioQueryText = QStringLiteral("UPDATE `Radios` " 5595​ auto updateRadioQueryText = QStringLiteral("UPDATE `Radios` "
5469 "SET " 5596​ "SET "
5470 "`HttpAddress` = :httpAddress, " 5597​ "`HttpAddress` = :httpAddress, "
5471 "`Title` = :title, " 5598​ "`Title` = :title, "
5472 "`Comment` = :comment, " 5599​ "`Comment` = :comment, "
5473 "`Rating` = :trackRating " 5600​ "`Rating` = :trackRating, "
5601​ "`ImageAddress` = :imageAddress "
5474 "WHERE " 5602​ "WHERE "
5475 "`ID` = :radioId"); 5603​ "`ID` = :radioId");
5476 5604​
5477 auto result = prepareQuery(d->mUpdateRadioQuery, updateRadioQueryText); 5605​ auto result = prepareQuery(d->mUpdateRadioQuery, updateRadioQueryText);
5478 5606​
5479 if (!result) { 5607​ if (!result) {
5480 qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::initRequest" << d->mUpdateRadioQuery.lastQuery(); 5608​ qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::initRequest" << d->mUpdateRadioQuery.lastQuery();
5481 qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::initRequest" << d->mUpdateRadioQuery.lastError(); 5609​ qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::initRequest" << d->mUpdateRadioQuery.lastError();
▲ Show 20 Lines • Show All 1242 Lines • ▼ Show 20 Line(s)
6724} 6852​}
6725 6853​
6726DataTypes::TrackDataType DatabaseInterface::buildRadioDataFromDatabaseRecord(const QSqlRecord &trackRecord) const 6854​DataTypes::TrackDataType DatabaseInterface::buildRadioDataFromDatabaseRecord(const QSqlRecord &trackRecord) const
6727{ 6855​{
6728 DataTypes::TrackDataType result; 6856​ DataTypes::TrackDataType result;
6729 6857​
6730 result[DataTypes::TrackDataType::key_type::DatabaseIdRole] = trackRecord.value(0); 6858​ result[DataTypes::TrackDataType::key_type::DatabaseIdRole] = trackRecord.value(0);
6731 result[DataTypes::TrackDataType::key_type::TitleRole] = trackRecord.value(1); 6859​ result[DataTypes::TrackDataType::key_type::TitleRole] = trackRecord.value(1);
6732 ​
6733 result[DataTypes::TrackDataType::key_type::AlbumRole] = i18n("Radios"); 6860​ result[DataTypes::TrackDataType::key_type::AlbumRole] = i18n("Radios");
6734 result[DataTypes::TrackDataType::key_type::ArtistRole] = trackRecord.value(1); 6861​ result[DataTypes::TrackDataType::key_type::ArtistRole] = trackRecord.value(1);
6735 ​
6736 result[DataTypes::TrackDataType::key_type::ResourceRole] = trackRecord.value(2); 6862​ result[DataTypes::TrackDataType::key_type::ResourceRole] = trackRecord.value(2);
6737 result[DataTypes::TrackDataType::key_type::RatingRole] = trackRecord.value(3); 6863​ result[DataTypes::TrackDataType::key_type::ImageUrlRole] = trackRecord.value(3);
6738 if (!trackRecord.value(4).isNull()) { 6864​ result[DataTypes::TrackDataType::key_type::RatingRole] = trackRecord.value(4);
6739 result[DataTypes::TrackDataType::key_type::GenreRole] = trackRecord.value(4); 6865​ if (!trackRecord.value(5).isNull()) {
6866​ result[DataTypes::TrackDataType::key_type::GenreRole] = trackRecord.value(5);
6740 } 6867​ }
6741 result[DataTypes::TrackDataType::key_type::CommentRole] = trackRecord.value(5); 6868​ result[DataTypes::TrackDataType::key_type::CommentRole] = trackRecord.value(6);
6742 result[DataTypes::TrackDataType::key_type::ElementTypeRole] = ElisaUtils::Radio; 6869​ result[DataTypes::TrackDataType::key_type::ElementTypeRole] = ElisaUtils::Radio;
6743 6870​
6744 return result; 6871​ return result;
6745} 6872​}
6746 6873​
6747void DatabaseInterface::internalRemoveTracksList(const QList<QUrl> &removedTracks) 6874​void DatabaseInterface::internalRemoveTracksList(const QList<QUrl> &removedTracks)
6748{ 6875​{
6749 QSet<qulonglong> modifiedAlbums; 6876​ QSet<qulonglong> modifiedAlbums;
▲ Show 20 Lines • Show All 389 Lines • ▼ Show 20 Line(s)7265 if (oneTrack.databaseId() == -1ull) {
7139 query = d->mInsertRadioQuery; 7266​ query = d->mInsertRadioQuery;
7140 } 7267​ }
7141 7268​
7142 query.bindValue(QStringLiteral(":httpAddress"), oneTrack.resourceURI()); 7269​ query.bindValue(QStringLiteral(":httpAddress"), oneTrack.resourceURI());
7143 query.bindValue(QStringLiteral(":radioId"), oneTrack.databaseId()); 7270​ query.bindValue(QStringLiteral(":radioId"), oneTrack.databaseId());
7144 query.bindValue(QStringLiteral(":title"), oneTrack.title()); 7271​ query.bindValue(QStringLiteral(":title"), oneTrack.title());
7145 query.bindValue(QStringLiteral(":comment"), oneTrack.comment()); 7272​ query.bindValue(QStringLiteral(":comment"), oneTrack.comment());
7146 query.bindValue(QStringLiteral(":trackRating"), oneTrack.rating()); 7273​ query.bindValue(QStringLiteral(":trackRating"), oneTrack.rating());
7274​ query.bindValue(QStringLiteral(":imageAddress"), oneTrack.albumCover());
7147 7275​
7148 auto result = execQuery(query); 7276​ auto result = execQuery(query);
7149 7277​
7150 if (!result || !query.isActive()) { 7278​ if (!result || !query.isActive()) {
7151 Q_EMIT databaseError(); 7279​ Q_EMIT databaseError();
7152 7280​
7153 qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::updateTrackInDatabase" << query.lastQuery(); 7281​ qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::updateTrackInDatabase" << query.lastQuery();
7154 qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::updateTrackInDatabase" << query.boundValues(); 7282​ qCDebug(orgKdeElisaDatabase) << "DatabaseInterface::updateTrackInDatabase" << query.boundValues();
▲ Show 20 Lines • Show All 935 Lines • Show Last 20 Lines