Revert previous commit, was incorrect
[amarok.git] / src / collectiondb.cpp
blob3279705278591e9d21a717e53e706066d7c8764d
1 // (c) 2004 Mark Kretschmann <markey@web.de>
2 // (c) 2004 Christian Muehlhaeuser <chris@chris.de>
3 // (c) 2004 Sami Nieminen <sami.nieminen@iki.fi>
4 // (c) 2005 Ian Monroe <ian@monroe.nu>
5 // (c) 2005 Jeff Mitchell <kde-dev@emailgoeshere.com>
6 // (c) 2005 Isaiah Damron <xepo@trifault.net>
7 // (c) 2005-2006 Alexandre Pereira de Oliveira <aleprj@gmail.com>
8 // (c) 2006 Jonas Hurrelmann <j@outpo.st>
9 // (c) 2006 Shane King <kde@dontletsstart.com>
10 // (c) 2006 Peter C. Ndikuwera <pndiku@gmail.com>
11 // (c) 2006 Stanislav Nikolov <valsinats@gmail.com>
12 // See COPYING file for licensing information.
14 //krazy:excludeall=doublequote_chars
16 #define DEBUG_PREFIX "CollectionDB"
18 #include "collectiondb.h"
20 #include "app.h"
21 #include "amarok.h"
22 #include "amarokconfig.h"
23 #include "config-amarok.h"
24 #include "debug.h"
25 #include "CoverFetcher.h"
26 #include "enginecontroller.h"
27 #include "expression.h"
28 #include "mediabrowser.h"
29 #include "metabundle.h" //updateTags()
30 #include "mountpointmanager.h" //buildQuery()
32 #include "podcastbundle.h" //addPodcast
33 #include "qstringx.h"
34 #include "querybuilder.h"
35 //Added by qt3to4:
36 #include <QTimerEvent>
37 #include <QByteArray>
38 #include <QLabel>
39 #include <Q3ValueList>
40 #include <QPixmap>
42 #include "scriptmanager.h"
43 #include "scrobbler.h"
44 #include "ContextStatusBar.h"
45 #include "threadmanager.h"
47 //queries moved to sqlcollection, CollectionDB is still being used by legacy stuff
48 #include "collection/CollectionManager.h"
49 #include "SqlStorage.h"
51 #include <QBitmap>
52 #include <QBuffer>
53 #include <QCheckBox>
54 #include <QEvent>
55 #include <QEventLoop>
56 #include <QFile>
57 #include <QMap>
58 #include <QMutex>
59 #include <QRegExp> //setHTMLLyrics()
60 #include <QTimer>
61 #include <QPainter> //createDragPixmap()
62 #include <QPalette>
63 #include <QIODevice>
64 #include <QToolTip>
66 #include <kcharsets.h> //setHTMLLyrics()
67 #include <kcombobox.h>
68 #include <kconfig.h>
69 #include <kdialog.h> //checkDatabase()
70 #include <kglobal.h>
71 #include <kinputdialog.h> //setupCoverFetcher()
72 #include <klineedit.h> //setupCoverFetcher()
73 #include <klocale.h>
74 #include <kcodecs.h>
75 #include <kmessagebox.h>
76 #include <kstandarddirs.h>
77 #include <kio/job.h>
78 #include <kio/netaccess.h>
79 #include <krandom.h>
81 #include <cmath> //DbConnection::sqlite_power()
82 #include <ctime> //query()
83 #include <unistd.h> //usleep()
85 #include <audioproperties.h>
87 #include "sqlite/sqlite3.h"
89 #ifdef USE_MYSQL
90 #include <mysql/mysql.h>
91 #include <mysql/mysql_version.h>
92 #endif
94 #ifdef USE_POSTGRESQL
95 #include <libpq-fe.h>
96 #endif
98 #undef HAVE_INOTIFY // NOTE Disabled for now, due to stability issues
100 #ifdef HAVE_INOTIFY
101 #include <linux/inotify.h>
102 #include "inotify/inotify-syscalls.h"
103 #endif
106 //bump DATABASE_VERSION whenever changes to the table structure are made.
107 // This erases tags, album, artist, composer, genre, year, images, embed, directory and related_artists tables.
108 const int CollectionDB::DATABASE_VERSION = 35;
109 // Persistent Tables hold data that is somehow valuable to the user, and can't be erased when rescaning.
110 // When bumping this, write code to convert the data!
111 const int CollectionDB::DATABASE_PERSISTENT_TABLES_VERSION = 19;
112 // Bumping this erases stats table. If you ever need to, write code to convert the data!
113 const int CollectionDB::DATABASE_STATS_VERSION = 12;
114 // When bumping this, you should provide code to convert the data.
115 const int CollectionDB::DATABASE_PODCAST_TABLES_VERSION = 2;
116 const int CollectionDB::DATABASE_AFT_VERSION = 2;
117 // persistent table. you should provide code to convert the data when bumping this
118 const int CollectionDB::DATABASE_DEVICES_VERSION = 1;
119 const int CollectionDB::MONITOR_INTERVAL = 60;
121 using Amarok::QStringx;
123 #define DEBUG 0
125 //////////////////////////////////////////////////////////////////////////////////////////
126 // CLASS INotify
127 //////////////////////////////////////////////////////////////////////////////////////////
129 INotify* INotify::s_instance = 0;
131 INotify::INotify( CollectionDB *parent, int fd )
132 : DependentJob( parent, "INotify" )
133 , m_parent( parent )
134 , m_fd( fd )
136 s_instance = this;
140 INotify::~INotify()
144 bool
145 INotify::watchDir( const QString directory )
147 #ifdef HAVE_INOTIFY
148 int wd = inotify_add_watch( m_fd, directory.toLocal8Bit(), IN_CLOSE_WRITE | IN_DELETE | IN_MOVE |
149 IN_MODIFY | IN_ATTRIB );
150 if ( wd < 0 )
151 debug() << "Could not add INotify watch for: " << directory;
153 return ( wd >= 0 );
154 #else
155 Q_UNUSED(directory);
156 #endif
158 return false;
162 bool
163 INotify::doJob()
165 #ifdef HAVE_INOTIFY
166 DEBUG_BLOCK
168 IdList list = MountPointManager::instance()->getMountedDeviceIds();
169 QString deviceIds;
170 oldForeachType( IdList, list )
172 if ( !deviceIds.isEmpty() ) deviceIds += ',';
173 deviceIds += QString::number(*it);
175 const QStringList values = m_parent->query( QString( "SELECT dir, deviceid FROM directories WHERE deviceid IN (%1);" )
176 .arg( deviceIds ) );
177 oldForeach( values )
179 QString rpath = *it;
180 int deviceid = (*(++it)).toInt();
181 QString abspath = MountPointManager::instance()->getAbsolutePath( deviceid, rpath );
182 watchDir( abspath );
185 /* size of the event structure, not counting name */
186 const int EVENT_SIZE = ( sizeof( struct inotify_event ) );
187 /* reasonable guess as to size of 1024 events */
188 const int BUF_LEN = 1024 * ( EVENT_SIZE + 16 );
190 while ( 1 )
192 char buf[BUF_LEN];
193 int len, i = 0;
194 len = read( m_fd, buf, BUF_LEN );
195 if ( len < 0 )
197 debug() << "Read from INotify failed";
198 return false;
200 else
202 if ( !len )
204 /* BUF_LEN too small? */
206 else
208 while ( i < len )
210 struct inotify_event *event;
211 event = (struct inotify_event *) &buf[i];
213 i += EVENT_SIZE + event->len;
216 QTimer::singleShot( 0, m_parent, SLOT( scanMonitor() ) );
220 #endif
222 // this shouldn't happen
223 return false;
227 //////////////////////////////////////////////////////////////////////////////////////////
228 // CLASS CollectionDB
229 //////////////////////////////////////////////////////////////////////////////////////////
231 QMutex* CollectionDB::connectionMutex = new QMutex();
232 QMutex* CollectionDB::itemCoverMapMutex = new QMutex();
233 //we don't have to worry about this map leaking memory since ThreadManager limits the total
234 //number of QThreads ever created
235 QMap<QThread *, DbConnection *> *CollectionDB::threadConnections = new QMap<QThread *, DbConnection *>();
236 QMap<Q3ListViewItem*, CoverFetcher*> *CollectionDB::itemCoverMap = new QMap<Q3ListViewItem*, CoverFetcher*>();
238 CollectionDB* CollectionDB::instance()
240 static CollectionDB db;
241 return &db;
245 CollectionDB::CollectionDB()
246 : EngineObserver( EngineController::instance() )
247 , m_autoScoring( true )
248 , m_noCover( KStandardDirs::locate( "data", "amarok/images/nocover.png" ) )
249 , m_scanInProgress( false )
250 , m_rescanRequired( false )
251 , m_aftEnabledPersistentTables()
252 , m_moveFileJobCancelled( false )
254 DEBUG_BLOCK
256 #ifdef USE_MYSQL
257 if ( AmarokConfig::databaseEngine().toInt() == DbConnection::mysql )
258 m_dbConnType = DbConnection::mysql;
259 else
260 #endif
261 #ifdef USE_POSTGRESQL
262 if ( AmarokConfig::databaseEngine().toInt() == DbConnection::postgresql )
263 m_dbConnType = DbConnection::postgresql;
264 else
265 #endif
266 m_dbConnType = DbConnection::sqlite;
268 //perform all necessary operations to allow MountPointManager to access the devices table here
269 //there is a recursive dependency between CollectionDB and MountPointManager and this is the workaround
270 //checkDatabase has to be able to access MountPointManager
272 //<OPEN DATABASE>
273 // initialize();
274 //</OPEN DATABASE>
276 // Remove cached "nocover" images, so that a new version actually gets shown
277 // The asterisk is for also deleting the shadow-caches.
278 QStringList filters;
279 filters << "*nocover.png*";
280 const QStringList entryList = cacheCoverDir().entryList( filters, QDir::Files );
281 foreach( QString entry, entryList )
282 cacheCoverDir().remove( entry );
284 connect( this, SIGNAL(fileMoved(const QString&, const QString&, const QString&)),
285 this, SLOT(aftMigratePermanentTablesUrl(const QString&, const QString&, const QString&)) );
286 connect( this, SIGNAL(uniqueIdChanged(const QString&, const QString&, const QString&)),
287 this, SLOT(aftMigratePermanentTablesUniqueId(const QString&, const QString&, const QString&)) );
289 connect( kapp, SIGNAL( aboutToQuit() ), this, SLOT( disableAutoScoring() ) );
291 connect( this, SIGNAL( coverRemoved( const QString&, const QString& ) ),
292 SIGNAL( coverChanged( const QString&, const QString& ) ) );
293 connect( Scrobbler::instance(), SIGNAL( similarArtistsFetched( const QString&, const QStringList& ) ),
294 this, SLOT( similarArtistsFetched( const QString&, const QStringList& ) ) );
296 // If we're supposed to monitor dirs for changes, make sure we run it once
297 // on startup, since inotify can't inform us about old events
298 // QTimer::singleShot( 0, this, SLOT( scanMonitor() ) )
299 initDirOperations();
300 m_aftEnabledPersistentTables << "lyrics" << "statistics" << "tags_labels";
304 CollectionDB::~CollectionDB()
306 DEBUG_BLOCK
308 #ifdef HAVE_INOTIFY
309 if ( INotify::instance()->fd() >= 0 )
310 close( INotify::instance()->fd() );
311 #endif
313 destroy();
317 QString
318 CollectionDB::exactCondition( const QString &right )
320 if ( DbConnection::mysql == instance()->getDbConnectionType() )
321 return QString( "= BINARY '" + instance()->escapeString( right ) + '\'' );
322 else
323 return QString( "= '" + instance()->escapeString( right ) + '\'' );
327 QString
328 CollectionDB::likeCondition( const QString &right, bool anyBegin, bool anyEnd )
330 QString escaped = right;
331 escaped.replace( '/', "//" ).replace( '%', "/%" ).replace( '_', "/_" );
332 escaped = instance()->escapeString( escaped );
334 QString ret;
335 if ( DbConnection::postgresql == instance()->getDbConnectionType() )
336 ret = " ILIKE "; //case-insensitive according to locale
337 else
338 ret = " LIKE ";
340 ret += '\'';
341 if ( anyBegin )
342 ret += '%';
343 ret += escaped;
344 if ( anyEnd )
345 ret += '%';
346 ret += '\'';
348 //Use / as the escape character
349 ret += " ESCAPE '/' ";
351 return ret;
354 //////////////////////////////////////////////////////////////////////////////////////////
355 // PUBLIC
356 //////////////////////////////////////////////////////////////////////////////////////////
358 void
359 CollectionDB::initDirOperations()
361 //this code was originally part of the ctor. It has to call MountPointManager to
362 //generate absolute paths from deviceids and relative paths. MountPointManager's ctor
363 //absolutely has to access the database, which resulted in a recursive ctor call. To
364 //solve this problem, the directory access code was moved into its own method, which can
365 //only be called when the CollectionDB object already exists.
367 //FIXME max: make sure we check additional directories if we connect a new device
368 #ifdef HAVE_INOTIFY
369 // Try to initialize inotify, if not available use the old timer approach.
370 int inotify_fd = inotify_init();
371 if ( inotify_fd < 0 )
372 #endif
374 // debug() << "INotify not available, using QTimer!";
375 startTimer( MONITOR_INTERVAL * 1000 );
377 #ifdef HAVE_INOTIFY
378 else
380 debug() << "INotify enabled!";
381 ThreadManager::instance()->onlyOneJob( new INotify( this, inotify_fd ) );
383 #endif
389 * Executes a SQL query on the already opened database
390 * @param statement SQL program to execute. Only one SQL statement is allowed.
391 * @return The queried data, or QStringList() on error.
393 QStringList
394 CollectionDB::query( const QString& statement, bool suppressDebug )
396 Q_UNUSED( suppressDebug )
397 return CollectionManager::instance()->sqlStorage()->query( statement );
402 * Executes a SQL insert on the already opened database
403 * @param statement SQL statement to execute. Only one SQL statement is allowed.
404 * @return The rowid of the inserted item.
407 CollectionDB::insert( const QString& statement, const QString& table )
409 return CollectionManager::instance()->sqlStorage()->insert( statement, table );
412 QString
413 CollectionDB::deviceidSelection( const bool showAll )
415 if ( !showAll )
417 IdList list = MountPointManager::instance()->getMountedDeviceIds();
418 QString deviceIds = "";
419 oldForeachType( IdList, list )
421 if ( it != list.begin() ) deviceIds += ',';
422 deviceIds += QString::number(*it);
424 return " AND tags.deviceid IN (" + deviceIds + ')';
426 else return "";
429 QStringList
430 CollectionDB::URLsFromQuery( const QStringList &result ) const
432 QStringList values;
433 oldForeach( result )
435 const int id = (*it).toInt();
436 values << MountPointManager::instance()->getAbsolutePath( id, *(++it) );
438 return values;
441 KUrl::List
442 CollectionDB::URLsFromSqlDrag( const QStringList &values ) const
444 KUrl::List urls;
445 for( QStringList::const_iterator it = values.begin();
446 it != values.end();
447 it++ )
449 const QString &rel = *it;
450 it++;
451 int id = (*it).toInt();
452 urls += KUrl( MountPointManager::instance()->getAbsolutePath( id, rel ) );
453 for( int i = 0;
454 i < QueryBuilder::dragFieldCount-1 && it != values.end();
455 i++ )
456 it++;
459 return urls;
462 bool
463 CollectionDB::isEmpty( )
465 QStringList values;
467 values = query( "SELECT COUNT( url ) FROM tags LIMIT 1 OFFSET 0;" );
469 return values.isEmpty() ? true : values.first() == "0";
473 bool
474 CollectionDB::isValid( )
476 QStringList values1;
477 QStringList values2;
478 QStringList values3;
479 QStringList values4;
480 QStringList values5;
482 values1 = query( "SELECT COUNT( url ) FROM tags LIMIT 1 OFFSET 0;" );
483 values2 = query( "SELECT COUNT( url ) FROM statistics LIMIT 1 OFFSET 0;" );
484 values3 = query( "SELECT COUNT( url ) FROM podcastchannels LIMIT 1 OFFSET 0;" );
485 values4 = query( "SELECT COUNT( url ) FROM podcastepisodes LIMIT 1 OFFSET 0;" );
486 values5 = query( "SELECT COUNT( id ) FROM devices LIMIT 1 OFFSET 0;" );
488 //It's valid as long as we've got _some_ tables that have something in.
489 return !( values1.isEmpty() && values2.isEmpty() && values3.isEmpty() && values4.isEmpty() && values5.isEmpty() );
493 QString
494 CollectionDB::adminValue( QString noption ) {
495 QStringList values;
496 values = query (
497 QString( "SELECT value FROM admin WHERE noption = '%1';").arg(noption)
499 return values.isEmpty() ? "" : values.first();
503 void
504 CollectionDB::setAdminValue( QString noption, QString value ) {
506 QStringList values = query( QString( "SELECT value FROM admin WHERE noption = '%1';").arg( noption ));
507 if(values.count() > 0)
509 query( QString( "UPDATE admin SET value = '%1' WHERE noption = '%2';" ).arg( value, noption ) );
511 else
513 insert( QString( "INSERT INTO admin (value, noption) values ( '%1', '%2' );" ).arg( value, noption ),
514 NULL );
520 void
521 CollectionDB::createTables( const bool temporary )
523 DEBUG_BLOCK
525 //create tag table
526 query( QString( "CREATE %1 TABLE tags%2 ("
527 "url " + exactTextColumnType() + ","
528 "dir " + exactTextColumnType() + ","
529 "createdate INTEGER,"
530 "modifydate INTEGER,"
531 "album INTEGER,"
532 "artist INTEGER,"
533 "composer INTEGER,"
534 "genre INTEGER,"
535 "title " + textColumnType() + ","
536 "year INTEGER,"
537 "comment " + longTextColumnType() + ","
538 "track NUMERIC(4),"
539 "discnumber INTEGER,"
540 "bitrate INTEGER,"
541 "length INTEGER,"
542 "samplerate INTEGER,"
543 "filesize INTEGER,"
544 "filetype INTEGER,"
545 "sampler BOOL,"
546 "bpm FLOAT,"
547 "deviceid INTEGER);" )
548 .arg( temporary ? "TEMPORARY" : "" )
549 .arg( temporary ? "_temp" : "" ) );
551 QString albumAutoIncrement = "";
552 QString artistAutoIncrement = "";
553 QString composerAutoIncrement = "";
554 QString genreAutoIncrement = "";
555 QString yearAutoIncrement = "";
556 if ( getDbConnectionType() == DbConnection::postgresql )
558 if(!temporary)
560 query( QString( "CREATE SEQUENCE album_seq;" ) );
561 query( QString( "CREATE SEQUENCE artist_seq;" ) );
562 query( QString( "CREATE SEQUENCE composer_seq;" ) );
563 query( QString( "CREATE SEQUENCE genre_seq;" ) );
564 query( QString( "CREATE SEQUENCE year_seq;" ) );
567 albumAutoIncrement = QString("DEFAULT nextval('album_seq')");
568 artistAutoIncrement = QString("DEFAULT nextval('artist_seq')");
569 composerAutoIncrement = QString("DEFAULT nextval('composer_seq')");
570 genreAutoIncrement = QString("DEFAULT nextval('genre_seq')");
571 yearAutoIncrement = QString("DEFAULT nextval('year_seq')");
573 else if ( getDbConnectionType() == DbConnection::mysql )
575 albumAutoIncrement = "AUTO_INCREMENT";
576 artistAutoIncrement = "AUTO_INCREMENT";
577 composerAutoIncrement = "AUTO_INCREMENT";
578 genreAutoIncrement = "AUTO_INCREMENT";
579 yearAutoIncrement = "AUTO_INCREMENT";
582 //create album table
583 query( QString( "CREATE %1 TABLE album%2 ("
584 "id INTEGER PRIMARY KEY %3,"
585 "name " + textColumnType() + ");" )
586 .arg( temporary ? "TEMPORARY" : "" )
587 .arg( temporary ? "_temp" : "" )
588 .arg( albumAutoIncrement ) );
590 //create artist table
591 query( QString( "CREATE %1 TABLE artist%2 ("
592 "id INTEGER PRIMARY KEY %3,"
593 "name " + textColumnType() + ");" )
594 .arg( temporary ? "TEMPORARY" : "" )
595 .arg( temporary ? "_temp" : "" )
596 .arg( artistAutoIncrement ) );
598 //create composer table
599 query( QString( "CREATE %1 TABLE composer%2 ("
600 "id INTEGER PRIMARY KEY %3,"
601 "name " + textColumnType() + ");" )
602 .arg( temporary ? "TEMPORARY" : "" )
603 .arg( temporary ? "_temp" : "" )
604 .arg( composerAutoIncrement ) );
606 //create genre table
607 query( QString( "CREATE %1 TABLE genre%2 ("
608 "id INTEGER PRIMARY KEY %3,"
609 "name " + textColumnType() +");" )
610 .arg( temporary ? "TEMPORARY" : "" )
611 .arg( temporary ? "_temp" : "" )
612 .arg( genreAutoIncrement ) );
614 //create year table
615 query( QString( "CREATE %1 TABLE year%2 ("
616 "id INTEGER PRIMARY KEY %3,"
617 "name " + textColumnType() + ");" )
618 .arg( temporary ? "TEMPORARY" : "" )
619 .arg( temporary ? "_temp" : "" )
620 .arg( yearAutoIncrement ) );
622 //create images table
623 query( QString( "CREATE %1 TABLE images%2 ("
624 "path " + exactTextColumnType() + ","
625 "deviceid INTEGER,"
626 "artist " + textColumnType() + ","
627 "album " + textColumnType() + ");" )
628 .arg( temporary ? "TEMPORARY" : "" )
629 .arg( temporary ? "_temp" : "" ) );
631 //create embed table
632 query( QString( "CREATE %1 TABLE embed%2 ("
633 "url " + exactTextColumnType() + ","
634 "deviceid INTEGER,"
635 "hash " + exactTextColumnType() + ","
636 "description " + textColumnType() + ");" )
637 .arg( temporary ? "TEMPORARY" : "" )
638 .arg( temporary ? "_temp" : "" ) );
640 // create directory statistics table
641 query( QString( "CREATE %1 TABLE directories%2 ("
642 "dir " + exactTextColumnType() + ","
643 "deviceid INTEGER,"
644 "changedate INTEGER);" )
645 .arg( temporary ? "TEMPORARY" : "" )
646 .arg( temporary ? "_temp" : "" ) );
648 //create uniqueid table
649 query( QString( "CREATE %1 TABLE uniqueid%2 ("
650 "url " + exactTextColumnType() + ","
651 "deviceid INTEGER,"
652 "uniqueid " + exactTextColumnType(32) + " UNIQUE,"
653 "dir " + exactTextColumnType() + ");" )
654 .arg( temporary ? "TEMPORARY" : "" )
655 .arg( temporary ? "_temp" : "" ) );
657 //create indexes
658 query( QString( "CREATE INDEX album_idx%1 ON album%2( name );" )
659 .arg( temporary ? "_temp" : "" ).arg( temporary ? "_temp" : "" ) );
660 query( QString( "CREATE INDEX artist_idx%1 ON artist%2( name );" )
661 .arg( temporary ? "_temp" : "" ).arg( temporary ? "_temp" : "" ) );
662 query( QString( "CREATE INDEX composer_idx%1 ON composer%2( name );" )
663 .arg( temporary ? "_temp" : "" ).arg( temporary ? "_temp" : "" ) );
664 query( QString( "CREATE INDEX genre_idx%1 ON genre%2( name );" )
665 .arg( temporary ? "_temp" : "" ).arg( temporary ? "_temp" : "" ) );
666 query( QString( "CREATE INDEX year_idx%1 ON year%2( name );" )
667 .arg( temporary ? "_temp" : "" ).arg( temporary ? "_temp" : "" ) );
669 if ( !temporary )
671 //create admin table -- holds the db version, put here other stuff if necessary
672 query( QString( "CREATE TABLE admin ("
673 "noption " + textColumnType() + ", "
674 "value " + textColumnType() + ");" ) );
676 // create related artists cache
677 query( QString( "CREATE TABLE related_artists ("
678 "artist " + textColumnType() + ","
679 "suggestion " + textColumnType() + ","
680 "changedate INTEGER );" ) );
682 createIndices();
684 else
686 query( "CREATE UNIQUE INDEX url_tagtemp ON tags_temp( url, deviceid );" );
687 query( "CREATE UNIQUE INDEX embed_urltemp ON embed_temp( url, deviceid );" );
688 query( "CREATE UNIQUE INDEX dir_temp_dir ON directories_temp( dir, deviceid );" );
692 void
693 CollectionDB::createIndices()
695 //This creates the indices for tables created in createTables. It should not refer to
696 //tables which are not created in that function.
697 debug() << "Creating indices, ignore errors about already existing indices";
699 query( "CREATE UNIQUE INDEX url_tag ON tags( url, deviceid );", true );
700 query( "CREATE INDEX album_tag ON tags( album );" );
701 query( "CREATE INDEX artist_tag ON tags( artist );" );
702 query( "CREATE INDEX composer_tag ON tags( composer );" );
703 query( "CREATE INDEX genre_tag ON tags( genre );" );
704 query( "CREATE INDEX year_tag ON tags( year );" );
705 query( "CREATE INDEX sampler_tag ON tags( sampler );" );
707 query( "CREATE INDEX images_album ON images( album );" );
708 query( "CREATE INDEX images_artist ON images( artist );" );
710 query( "CREATE INDEX images_url ON images( path, deviceid );" );
712 query( "CREATE UNIQUE INDEX embed_url ON embed( url, deviceid );" );
713 query( "CREATE INDEX embed_hash ON embed( hash );" );
715 query( "CREATE UNIQUE INDEX directories_dir ON directories( dir, deviceid );" );
716 query( "CREATE INDEX uniqueid_uniqueid ON uniqueid( uniqueid );");
717 query( "CREATE INDEX uniqueid_url ON uniqueid( url, deviceid );");
719 query( "CREATE INDEX album_idx ON album( name );" );
720 query( "CREATE INDEX artist_idx ON artist( name );" );
721 query( "CREATE INDEX composer_idx ON composer( name );" );
722 query( "CREATE INDEX genre_idx ON genre( name );" );
723 query( "CREATE INDEX year_idx ON year( name );" );
725 query( "CREATE INDEX tags_artist_index ON tags( artist );" );
726 query( "CREATE INDEX tags_album_index ON tags( album );" );
727 query( "CREATE INDEX tags_deviceid_index ON tags( deviceid );" );
728 query( "CREATE INDEX tags_url_index ON tags( url(20) );" );
730 query( "CREATE INDEX embed_deviceid_index ON embed( deviceid );" );
731 query( "CREATE INDEX embed_url_index ON embed( url(20) );" );
733 query( "CREATE INDEX related_artists_artist ON related_artists( artist );" );
735 debug() << "Finished creating indices, stop ignoring errors";
738 void
739 CollectionDB::createPermanentIndices()
741 //this method creates all indices which are not referred to in createTables
742 //this method is called on each startup of amarok
743 //until we figure out a way to handle this better it produces SQL errors if the indices
744 //already exist, but these can be ignored
745 debug() << "Creating permanent indices, ignore errors about already existing indices";
747 query( "CREATE UNIQUE INDEX lyrics_url ON lyrics( url, deviceid );" );
748 query( "CREATE INDEX lyrics_uniqueid ON lyrics( uniqueid );" );
749 query( "CREATE INDEX playlist_playlists ON playlists( playlist );" );
750 query( "CREATE INDEX url_playlists ON playlists( url );" );
751 query( "CREATE UNIQUE INDEX labels_name ON labels( name, type );" );
752 query( "CREATE INDEX tags_labels_uniqueid ON tags_labels( uniqueid );" ); //m:n relationship, DO NOT MAKE UNIQUE!
753 query( "CREATE INDEX tags_labels_url ON tags_labels( url, deviceid );" ); //m:n relationship, DO NOT MAKE UNIQUE!
754 query( "CREATE INDEX tags_labels_labelid ON tags_labels( labelid );" ); //m:n relationship, DO NOT MAKE UNIQUE!
756 query( "CREATE UNIQUE INDEX url_stats ON statistics( deviceid, url );" );
757 query( "CREATE INDEX percentage_stats ON statistics( percentage );" );
758 query( "CREATE INDEX rating_stats ON statistics( rating );" );
759 query( "CREATE INDEX playcounter_stats ON statistics( playcounter );" );
760 query( "CREATE INDEX uniqueid_stats ON statistics( uniqueid );" );
762 query( "CREATE INDEX url_podchannel ON podcastchannels( url );" );
763 query( "CREATE INDEX url_podepisode ON podcastepisodes( url );" );
764 query( "CREATE INDEX localurl_podepisode ON podcastepisodes( localurl );" );
765 query( "CREATE INDEX url_podfolder ON podcastfolders( id );" );
767 debug() << "Finished creating permanent indices, stop ignoring errors";
771 void
772 CollectionDB::dropTables( const bool temporary )
774 query( QString( "DROP TABLE tags%1;" ).arg( temporary ? "_temp" : "" ) );
775 query( QString( "DROP TABLE album%1;" ).arg( temporary ? "_temp" : "" ) );
776 query( QString( "DROP TABLE artist%1;" ).arg( temporary ? "_temp" : "" ) );
777 query( QString( "DROP TABLE composer%1;" ).arg( temporary ? "_temp" : "" ) );
778 query( QString( "DROP TABLE genre%1;" ).arg( temporary ? "_temp" : "" ) );
779 query( QString( "DROP TABLE year%1;" ).arg( temporary ? "_temp" : "" ) );
780 query( QString( "DROP TABLE images%1;" ).arg( temporary ? "_temp" : "" ) );
781 query( QString( "DROP TABLE embed%1;" ).arg( temporary ? "_temp" : "" ) );
782 query( QString( "DROP TABLE directories%1;" ).arg( temporary ? "_temp" : "" ) );
783 query( QString( "DROP TABLE uniqueid%1;" ).arg( temporary ? "_temp" : "" ) );
784 if ( !temporary )
786 query( QString( "DROP TABLE related_artists;" ) );
787 debug() << "Dropping media table";
790 if ( getDbConnectionType() == DbConnection::postgresql )
792 if (temporary == false) {
793 query( QString( "DROP SEQUENCE album_seq;" ) );
794 query( QString( "DROP SEQUENCE artist_seq;" ) );
795 query( QString( "DROP SEQUENCE composer_seq;" ) );
796 query( QString( "DROP SEQUENCE genre_seq;" ) );
797 query( QString( "DROP SEQUENCE year_seq;" ) );
803 void
804 CollectionDB::clearTables( const bool temporary )
806 QString clearCommand = "DELETE FROM";
807 if ( getDbConnectionType() == DbConnection::mysql || getDbConnectionType() == DbConnection::postgresql)
809 // TRUNCATE TABLE is faster than DELETE FROM TABLE, so use it when supported.
810 clearCommand = "TRUNCATE TABLE";
813 query( QString( "%1 tags%2;" ).arg( clearCommand ).arg( temporary ? "_temp" : "" ) );
814 query( QString( "%1 album%2;" ).arg( clearCommand ).arg( temporary ? "_temp" : "" ) );
815 query( QString( "%1 artist%2;" ).arg( clearCommand ).arg( temporary ? "_temp" : "" ) );
816 query( QString( "%1 composer%2;" ).arg( clearCommand ).arg( temporary ? "_temp" : "" ) );
817 query( QString( "%1 genre%2;" ).arg( clearCommand ).arg( temporary ? "_temp" : "" ) );
818 query( QString( "%1 year%2;" ).arg( clearCommand ).arg( temporary ? "_temp" : "" ) );
819 query( QString( "%1 images%2;" ).arg( clearCommand ).arg( temporary ? "_temp" : "" ) );
820 query( QString( "%1 embed%2;" ).arg( clearCommand ).arg( temporary ? "_temp" : "" ) );
821 query( QString( "%1 directories%2;" ).arg( clearCommand ).arg( temporary ? "_temp" : "" ) );
822 query( QString( "%1 uniqueid%2;" ).arg( clearCommand ).arg( temporary ? "_temp" : "" ) );
823 if ( !temporary )
825 query( QString( "%1 related_artists;" ).arg( clearCommand ) );
826 //debug() << "Clearing media table";
827 //query( QString( "%1 media;" ).arg( clearCommand ) );
832 void
833 CollectionDB::copyTempTables( )
835 DEBUG_BLOCK
837 insert( "INSERT INTO tags SELECT * FROM tags_temp;", NULL );
839 //mysql 5 supports subqueries with IN, mysql 4 doesn't. this way will work for all SQL servers
840 QStringList albumIdList = query( "SELECT album.id FROM album;" );
841 //in an empty database, albumIdList is empty. This would result in a SQL query like NOT IN ( ) without
842 //the -1 below which is invalid SQL. The auto generated values start at 1 so this is fine
843 QString albumIds = "-1";
844 oldForeach( albumIdList )
846 albumIds += ',';
847 albumIds += *it;
849 insert( QString ( "INSERT INTO album SELECT * FROM album_temp WHERE album_temp.id NOT IN ( %1 );" ).arg( albumIds ), NULL );
851 QStringList artistIdList = query( "SELECT artist.id FROM artist;" );
852 QString artistIds = "-1";
853 oldForeach( artistIdList )
855 artistIds += ',';
856 artistIds += *it;
858 insert( QString ( "INSERT INTO artist SELECT * FROM artist_temp WHERE artist_temp.id NOT IN ( %1 );" ).arg( artistIds ), NULL );
860 QStringList composerIdList = query( "SELECT composer.id FROM composer;" );
861 QString composerIds = "-1";
862 oldForeach( composerIdList )
864 composerIds += ',';
865 composerIds += *it;
867 insert( QString ( "INSERT INTO composer SELECT * FROM composer_temp WHERE composer_temp.id NOT IN ( %1 );" ).arg( composerIds ), NULL );
869 QStringList genreIdList = query( "SELECT genre.id FROM genre;" );
870 QString genreIds = "-1";
871 oldForeach( genreIdList )
873 genreIds += ',';
874 genreIds += *it;
876 insert( QString ( "INSERT INTO genre SELECT * FROM genre_temp WHERE genre_temp.id NOT IN ( %1 );" ).arg( genreIds ), NULL );
878 QStringList yearIdList = query( "SELECT year.id FROM year;" );
879 QString yearIds = "-1";
880 oldForeach( yearIdList )
882 yearIds += ',';
883 yearIds += *it;
885 insert( QString ( "INSERT INTO year SELECT * FROM year_temp WHERE year_temp.id NOT IN ( %1 );" ).arg( yearIds ), NULL );
887 insert( "INSERT INTO images SELECT * FROM images_temp;", NULL );
888 insert( "INSERT INTO embed SELECT * FROM embed_temp;", NULL );
889 insert( "INSERT INTO directories SELECT * FROM directories_temp;", NULL );
890 insert( "INSERT INTO uniqueid SELECT * FROM uniqueid_temp;", NULL );
893 void
894 CollectionDB::prepareTempTables()
896 DEBUG_BLOCK
897 insert( "INSERT INTO album_temp SELECT * from album;", 0 );
898 insert( "INSERT INTO artist_temp SELECT * from artist;", 0 );
899 insert( "INSERT INTO composer_temp SELECT * from composer;", 0 );
900 insert( "INSERT INTO genre_temp SELECT * from genre;", 0 );
901 insert( "INSERT INTO year_temp SELECT * from year;", 0 );
904 void
905 CollectionDB::createDevicesTable()
907 debug() << "Creating DEVICES table";
908 QString deviceAutoIncrement = "";
909 if ( getDbConnectionType() == DbConnection::postgresql )
911 query( QString( "CREATE SEQUENCE devices_seq;" ) );
912 deviceAutoIncrement = QString("DEFAULT nextval('devices_seq')");
914 else if ( getDbConnectionType() == DbConnection::mysql )
916 deviceAutoIncrement = "AUTO_INCREMENT";
918 query( QString( "CREATE TABLE devices ("
919 "id INTEGER PRIMARY KEY %1,"
920 "type " + textColumnType() + ","
921 "label " + textColumnType() + ","
922 "lastmountpoint " + textColumnType() + ","
923 "uuid " + textColumnType() + ","
924 "servername " + textColumnType() + ","
925 "sharename " + textColumnType() + ");" )
926 .arg( deviceAutoIncrement ) );
927 query( "CREATE INDEX devices_type ON devices( type );" );
928 query( "CREATE INDEX devices_uuid ON devices( uuid );" );
929 query( "CREATE INDEX devices_rshare ON devices( servername, sharename );" );
932 void
933 CollectionDB::createStatsTable()
935 // create music statistics database
936 query( QString( "CREATE TABLE statistics ("
937 "url " + exactTextColumnType() + ","
938 "deviceid INTEGER,"
939 "createdate INTEGER,"
940 "accessdate INTEGER,"
941 "percentage FLOAT,"
942 "rating INTEGER DEFAULT 0,"
943 "playcounter INTEGER,"
944 "uniqueid " + exactTextColumnType(32) + " UNIQUE,"
945 "deleted BOOL DEFAULT " + boolF() + ","
946 "PRIMARY KEY(url, deviceid) );" ) );
950 //Old version, used in upgrade code. This should never be changed.
951 void
952 CollectionDB::createStatsTableV8()
954 // create music statistics database - old form, for upgrade code.
955 query( QString( "CREATE TABLE statistics ("
956 "url " + textColumnType() + " UNIQUE,"
957 "createdate INTEGER,"
958 "accessdate INTEGER,"
959 "percentage FLOAT,"
960 "rating INTEGER DEFAULT 0,"
961 "playcounter INTEGER,"
962 "uniqueid " + textColumnType(8) + " UNIQUE,"
963 "deleted BOOL DEFAULT " + boolF() + ");" ) );
965 query( "CREATE INDEX url_stats ON statistics( url );" );
966 query( "CREATE INDEX percentage_stats ON statistics( percentage );" );
967 query( "CREATE INDEX rating_stats ON statistics( rating );" );
968 query( "CREATE INDEX playcounter_stats ON statistics( playcounter );" );
969 query( "CREATE INDEX uniqueid_stats ON statistics( uniqueid );" );
972 //Old version, used in upgrade code
973 void
974 CollectionDB::createStatsTableV10( bool temp )
976 // create music statistics database
977 query( QString( "CREATE %1 TABLE statistics%2 ("
978 "url " + exactTextColumnType() + ","
979 "deviceid INTEGER,"
980 "createdate INTEGER,"
981 "accessdate INTEGER,"
982 "percentage FLOAT,"
983 "rating INTEGER DEFAULT 0,"
984 "playcounter INTEGER,"
985 "uniqueid " + exactTextColumnType(32) + " UNIQUE,"
986 "deleted BOOL DEFAULT " + boolF() + ","
987 "PRIMARY KEY(url, deviceid) );"
988 ).arg( temp ? "TEMPORARY" : "" )
989 .arg( temp ? "_fix_ten" : "" ) );
991 if ( !temp )
993 query( "CREATE UNIQUE INDEX url_stats ON statistics( deviceid, url );" );
994 query( "CREATE INDEX percentage_stats ON statistics( percentage );" );
995 query( "CREATE INDEX rating_stats ON statistics( rating );" );
996 query( "CREATE INDEX playcounter_stats ON statistics( playcounter );" );
997 query( "CREATE INDEX uniqueid_stats ON statistics( uniqueid );" );
1002 void
1003 CollectionDB::dropStatsTable()
1005 query( "DROP TABLE statistics;" );
1008 void
1009 CollectionDB::dropStatsTableV1()
1011 query( "DROP TABLE statistics;" );
1014 void
1015 CollectionDB::createPersistentTables()
1017 // create amazon table
1018 query( "CREATE TABLE amazon ( "
1019 "asin " + textColumnType(20) + ", "
1020 "locale " + textColumnType(2) + ", "
1021 "filename " + exactTextColumnType(33) + ", "
1022 "refetchdate INTEGER );" );
1024 // create lyrics table
1025 query( QString( "CREATE TABLE lyrics ("
1026 "url " + exactTextColumnType() + ", "
1027 "deviceid INTEGER,"
1028 "lyrics " + longTextColumnType() + ", "
1029 "uniqueid " + exactTextColumnType(32) + ");" ) );
1031 query( QString( "CREATE TABLE playlists ("
1032 "playlist " + textColumnType() + ", "
1033 "url " + exactTextColumnType() + ", "
1034 "tracknum INTEGER );" ) );
1036 QString labelsAutoIncrement = "";
1037 if ( getDbConnectionType() == DbConnection::postgresql )
1039 query( QString( "CREATE SEQUENCE labels_seq;" ) );
1041 labelsAutoIncrement = QString("DEFAULT nextval('labels_seq')");
1043 else if ( getDbConnectionType() == DbConnection::mysql )
1045 labelsAutoIncrement = "AUTO_INCREMENT";
1048 //create labels tables
1049 query( QString( "CREATE TABLE labels ("
1050 "id INTEGER PRIMARY KEY " + labelsAutoIncrement + ", "
1051 "name " + textColumnType() + ", "
1052 "type INTEGER);" ) );
1054 query( QString( "CREATE TABLE tags_labels ("
1055 "deviceid INTEGER,"
1056 "url " + exactTextColumnType() + ", "
1057 "uniqueid " + exactTextColumnType(32) + ", " //m:n relationship, DO NOT MAKE UNIQUE!
1058 "labelid INTEGER REFERENCES labels( id ) ON DELETE CASCADE );" ) );
1061 void
1062 CollectionDB::createPersistentTablesV12()
1064 // create amazon table
1065 query( "CREATE TABLE amazon ( "
1066 "asin " + textColumnType(20) + ", "
1067 "locale " + textColumnType(2) + ", "
1068 "filename " + textColumnType(33) + ", "
1069 "refetchdate INTEGER );" );
1071 // create lyrics table
1072 query( QString( "CREATE TABLE lyrics ("
1073 "url " + textColumnType() + ", "
1074 "lyrics " + longTextColumnType() + ");" ) );
1076 // create labels table
1077 query( QString( "CREATE TABLE label ("
1078 "url " + textColumnType() + ","
1079 "label " + textColumnType() + ");" ) );
1081 query( QString( "CREATE TABLE playlists ("
1082 "playlist " + textColumnType() + ", "
1083 "url " + textColumnType() + ", "
1084 "tracknum INTEGER );" ) );
1086 query( "CREATE INDEX url_label ON label( url );" );
1087 query( "CREATE INDEX label_label ON label( label );" );
1088 query( "CREATE INDEX playlist_playlists ON playlists( playlist );" );
1089 query( "CREATE INDEX url_playlists ON playlists( url );" );
1092 void
1093 CollectionDB::createPersistentTablesV14( bool temp )
1095 const QString a( temp ? "TEMPORARY" : "" );
1096 const QString b( temp ? "_fix" : "" );
1098 // create amazon table
1099 query( QString( "CREATE %1 TABLE amazon%2 ( "
1100 "asin " + textColumnType(20) + ", "
1101 "locale " + textColumnType(2) + ", "
1102 "filename " + exactTextColumnType(33) + ", "
1103 "refetchdate INTEGER );" ).arg( a,b ) );
1105 // create lyrics table
1106 query( QString( "CREATE %1 TABLE lyrics%2 ("
1107 "url " + exactTextColumnType() + ", "
1108 "deviceid INTEGER,"
1109 "lyrics " + longTextColumnType() + ");" ).arg( a,b ) );
1111 query( QString( "CREATE %1 TABLE playlists%2 ("
1112 "playlist " + textColumnType() + ", "
1113 "url " + exactTextColumnType() + ", "
1114 "tracknum INTEGER );" ).arg( a,b ) );
1116 if ( !temp )
1118 query( "CREATE UNIQUE INDEX lyrics_url ON lyrics( url, deviceid );" );
1119 query( "CREATE INDEX playlist_playlists ON playlists( playlist );" );
1120 query( "CREATE INDEX url_playlists ON playlists( url );" );
1124 void
1125 CollectionDB::createPodcastTables()
1127 QString podcastAutoIncrement = "";
1128 QString podcastFolderAutoInc = "";
1129 if ( getDbConnectionType() == DbConnection::postgresql )
1131 query( QString( "CREATE SEQUENCE podcastepisode_seq;" ) );
1133 query( QString( "CREATE SEQUENCE podcastfolder_seq;" ) );
1135 podcastAutoIncrement = QString("DEFAULT nextval('podcastepisode_seq')");
1136 podcastFolderAutoInc = QString("DEFAULT nextval('podcastfolder_seq')");
1138 else if ( getDbConnectionType() == DbConnection::mysql )
1140 podcastAutoIncrement = "AUTO_INCREMENT";
1141 podcastFolderAutoInc = "AUTO_INCREMENT";
1144 // create podcast channels table
1145 query( QString( "CREATE TABLE podcastchannels ("
1146 "url " + exactTextColumnType() + " UNIQUE,"
1147 "title " + textColumnType() + ","
1148 "weblink " + exactTextColumnType() + ","
1149 "image " + exactTextColumnType() + ","
1150 "comment " + longTextColumnType() + ","
1151 "copyright " + textColumnType() + ","
1152 "parent INTEGER,"
1153 "directory " + textColumnType() + ","
1154 "autoscan BOOL, fetchtype INTEGER, "
1155 "autotransfer BOOL, haspurge BOOL, purgecount INTEGER );" ) );
1157 // create podcast episodes table
1158 query( QString( "CREATE TABLE podcastepisodes ("
1159 "id INTEGER PRIMARY KEY %1, "
1160 "url " + exactTextColumnType() + " UNIQUE,"
1161 "localurl " + exactTextColumnType() + ","
1162 "parent " + exactTextColumnType() + ","
1163 "guid " + exactTextColumnType() + ","
1164 "title " + textColumnType() + ","
1165 "subtitle " + textColumnType() + ","
1166 "composer " + textColumnType() + ","
1167 "comment " + longTextColumnType() + ","
1168 "filetype " + textColumnType() + ","
1169 "createdate " + textColumnType() + ","
1170 "length INTEGER,"
1171 "size INTEGER,"
1172 "isNew BOOL );" )
1173 .arg( podcastAutoIncrement ) );
1175 // create podcast folders table
1176 query( QString( "CREATE TABLE podcastfolders ("
1177 "id INTEGER PRIMARY KEY %1, "
1178 "name " + textColumnType() + ","
1179 "parent INTEGER, isOpen BOOL );" )
1180 .arg( podcastFolderAutoInc ) );
1182 query( "CREATE INDEX url_podchannel ON podcastchannels( url );" );
1183 query( "CREATE INDEX url_podepisode ON podcastepisodes( url );" );
1184 query( "CREATE INDEX localurl_podepisode ON podcastepisodes( localurl );" );
1185 query( "CREATE INDEX url_podfolder ON podcastfolders( id );" );
1188 void
1189 CollectionDB::createPodcastTablesV2( bool temp )
1191 const QString a( temp ? "TEMPORARY" : "" );
1192 const QString b( temp ? "_fix" : "" );
1194 QString podcastAutoIncrement = "";
1195 QString podcastFolderAutoInc = "";
1196 if ( getDbConnectionType() == DbConnection::postgresql )
1198 query( QString( "CREATE SEQUENCE podcastepisode_seq;" ) );
1200 query( QString( "CREATE SEQUENCE podcastfolder_seq;" ) );
1202 podcastAutoIncrement = QString("DEFAULT nextval('podcastepisode_seq')");
1203 podcastFolderAutoInc = QString("DEFAULT nextval('podcastfolder_seq')");
1205 else if ( getDbConnectionType() == DbConnection::mysql )
1207 podcastAutoIncrement = "AUTO_INCREMENT";
1208 podcastFolderAutoInc = "AUTO_INCREMENT";
1211 // create podcast channels table
1212 query( QString( "CREATE %1 TABLE podcastchannels%2 ("
1213 "url " + exactTextColumnType() + " UNIQUE,"
1214 "title " + textColumnType() + ","
1215 "weblink " + exactTextColumnType() + ","
1216 "image " + exactTextColumnType() + ","
1217 "comment " + longTextColumnType() + ","
1218 "copyright " + textColumnType() + ","
1219 "parent INTEGER,"
1220 "directory " + textColumnType() + ","
1221 "autoscan BOOL, fetchtype INTEGER, "
1222 "autotransfer BOOL, haspurge BOOL, purgecount INTEGER );" ).arg( a,b ) );
1224 // create podcast episodes table
1225 query( QString( "CREATE %2 TABLE podcastepisodes%3 ("
1226 "id INTEGER PRIMARY KEY %1, "
1227 "url " + exactTextColumnType() + " UNIQUE,"
1228 "localurl " + exactTextColumnType() + ","
1229 "parent " + exactTextColumnType() + ","
1230 "guid " + exactTextColumnType() + ","
1231 "title " + textColumnType() + ","
1232 "subtitle " + textColumnType() + ","
1233 "composer " + textColumnType() + ","
1234 "comment " + longTextColumnType() + ","
1235 "filetype " + textColumnType() + ","
1236 "createdate " + textColumnType() + ","
1237 "length INTEGER,"
1238 "size INTEGER,"
1239 "isNew BOOL );" )
1240 .arg( podcastAutoIncrement, a, b ) );
1242 // create podcast folders table
1243 query( QString( "CREATE %2 TABLE podcastfolders%3 ("
1244 "id INTEGER PRIMARY KEY %1, "
1245 "name " + textColumnType() + ","
1246 "parent INTEGER, isOpen BOOL );" )
1247 .arg( podcastFolderAutoInc, a, b ) );
1249 if ( !temp )
1251 query( "CREATE INDEX url_podchannel ON podcastchannels( url );" );
1252 query( "CREATE INDEX url_podepisode ON podcastepisodes( url );" );
1253 query( "CREATE INDEX localurl_podepisode ON podcastepisodes( localurl );" );
1254 query( "CREATE INDEX url_podfolder ON podcastfolders( id );" );
1259 void
1260 CollectionDB::dropPersistentTables()
1262 query( "DROP TABLE amazon;" );
1263 query( "DROP TABLE lyrics;" );
1264 query( "DROP TABLE playlists;" );
1265 query( "DROP TABLE tags_labels;" );
1266 query( "DROP TABLE labels;" );
1269 void
1270 CollectionDB::dropPersistentTablesV14()
1272 query( "DROP TABLE amazon;" );
1273 query( "DROP TABLE lyrics;" );
1274 query( "DROP TABLE label;" );
1275 query( "DROP TABLE playlists;" );
1278 void
1279 CollectionDB::dropPodcastTables()
1281 query( "DROP TABLE podcastchannels;" );
1282 query( "DROP TABLE podcastepisodes;" );
1283 query( "DROP TABLE podcastfolders;" );
1286 void
1287 CollectionDB::dropPodcastTablesV2()
1289 query( "DROP TABLE podcastchannels;" );
1290 query( "DROP TABLE podcastepisodes;" );
1291 query( "DROP TABLE podcastfolders;" );
1294 void
1295 CollectionDB::dropDevicesTable()
1297 query( "DROP TABLE devices;" );
1300 uint
1301 CollectionDB::artistID( QString value, bool autocreate, const bool temporary, bool exact /* = true */ )
1303 // lookup cache
1304 if ( m_validArtistCache && m_cacheArtist[(int)temporary] == value )
1305 return m_cacheArtistID[(int)temporary];
1307 uint id;
1308 if ( exact )
1309 id = IDFromExactValue( "artist", value, autocreate, temporary ).toUInt();
1310 else
1311 id = IDFromValue( "artist", value, autocreate, temporary );
1313 // cache values
1314 m_cacheArtist[(int)temporary] = value;
1315 m_cacheArtistID[(int)temporary] = id;
1316 m_validArtistCache = 1;
1318 return id;
1322 QString
1323 CollectionDB::artistValue( uint id )
1325 // lookup cache
1326 if ( m_cacheArtistID[0] == id )
1327 return m_cacheArtist[0];
1329 QString value = valueFromID( "artist", id );
1331 // cache values
1332 m_cacheArtist[0] = value;
1333 m_cacheArtistID[0] = id;
1335 return value;
1339 uint
1340 CollectionDB::composerID( QString value, bool autocreate, const bool temporary, bool exact /* = true */ )
1342 // lookup cache
1343 if ( m_validComposerCache && m_cacheComposer[(int)temporary] == value )
1344 return m_cacheComposerID[(int)temporary];
1346 uint id;
1347 if ( exact )
1348 id = IDFromExactValue( "composer", value, autocreate, temporary ).toUInt();
1349 else
1350 id = IDFromValue( "composer", value, autocreate, temporary );
1352 // cache values
1353 m_cacheComposer[(int)temporary] = value;
1354 m_cacheComposerID[(int)temporary] = id;
1355 m_validComposerCache = 1;
1357 return id;
1361 QString
1362 CollectionDB::composerValue( uint id )
1364 // lookup cache
1365 if ( m_cacheComposerID[0] == id )
1366 return m_cacheComposer[0];
1368 QString value = valueFromID( "composer", id );
1370 // cache values
1371 m_cacheComposer[0] = value;
1372 m_cacheComposerID[0] = id;
1374 return value;
1378 uint
1379 CollectionDB::albumID( QString value, bool autocreate, const bool temporary, bool exact /* = true */ )
1381 // lookup cache
1382 if ( m_validAlbumCache && m_cacheAlbum[(int)temporary] == value )
1383 return m_cacheAlbumID[(int)temporary];
1385 uint id;
1386 if ( exact )
1387 id = IDFromExactValue( "album", value, autocreate, temporary ).toUInt();
1388 else
1389 id = IDFromValue( "album", value, autocreate, temporary );
1391 // cache values
1392 m_cacheAlbum[(int)temporary] = value;
1393 m_cacheAlbumID[(int)temporary] = id;
1394 m_validAlbumCache = 1;
1396 return id;
1399 QString
1400 CollectionDB::albumValue( uint id )
1402 // lookup cache
1403 if ( m_cacheAlbumID[0] == id )
1404 return m_cacheAlbum[0];
1406 QString value = valueFromID( "album", id );
1408 // cache values
1409 m_cacheAlbum[0] = value;
1410 m_cacheAlbumID[0] = id;
1412 return value;
1415 uint
1416 CollectionDB::genreID( QString value, bool autocreate, const bool temporary, bool exact /* = true */ )
1418 return exact ?
1419 IDFromExactValue( "genre", value, autocreate, temporary ).toUInt() :
1420 IDFromValue( "genre", value, autocreate, temporary );
1423 QString
1424 CollectionDB::genreValue( uint id )
1426 return valueFromID( "genre", id );
1430 uint
1431 CollectionDB::yearID( QString value, bool autocreate, const bool temporary, bool exact /* = true */ )
1433 return exact ?
1434 IDFromExactValue( "year", value, autocreate, temporary ).toUInt() :
1435 IDFromValue( "year", value, autocreate, temporary );
1439 QString
1440 CollectionDB::yearValue( uint id )
1442 return valueFromID( "year", id );
1446 uint
1447 CollectionDB::IDFromValue( QString name, QString value, bool autocreate, const bool temporary )
1449 if ( temporary )
1450 name.append( "_temp" );
1451 // what the hell is the reason for this?
1452 // else
1453 // conn = NULL;
1455 QStringList values =
1456 query( QString(
1457 "SELECT id, name FROM %1 WHERE name %2;" )
1458 .arg( name )
1459 .arg( CollectionDB::likeCondition( value ) ) );
1461 //check if item exists. if not, should we autocreate it?
1462 uint id;
1463 if ( values.isEmpty() && autocreate )
1465 id = insert( QString( "INSERT INTO %1 ( name ) VALUES ( '%2' );" )
1466 .arg( name )
1467 .arg( CollectionDB::instance()->escapeString( value ) ), name );
1469 return id;
1472 return values.isEmpty() ? 0 : values.first().toUInt();
1476 QString
1477 CollectionDB::valueFromID( QString table, uint id )
1479 QStringList values =
1480 query( QString(
1481 "SELECT name FROM %1 WHERE id=%2;" )
1482 .arg( table )
1483 .arg( id ) );
1486 return values.isEmpty() ? 0 : values.first();
1490 QString
1491 CollectionDB::albumSongCount( const QString &artist_id, const QString &album_id )
1493 QStringList values =
1494 query( QString(
1495 "SELECT COUNT( url ) FROM tags WHERE album = %1 AND artist = %2;" )
1496 .arg( album_id )
1497 .arg( artist_id ) );
1498 return values.first();
1501 bool
1502 CollectionDB::albumIsCompilation( const QString &album_id )
1504 QStringList values =
1505 query( QString(
1506 "SELECT sampler FROM tags WHERE sampler=%1 AND album=%2" )
1507 .arg( CollectionDB::instance()->boolT() )
1508 .arg( album_id ) );
1510 return (values.count() != 0);
1513 QStringList
1514 CollectionDB::albumTracks( const QString &artist_id, const QString &album_id )
1516 QueryBuilder qb;
1517 qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valURL );
1518 qb.addMatch( QueryBuilder::tabAlbum, QueryBuilder::valID, album_id );
1519 const bool isCompilation = albumIsCompilation( album_id );
1520 if( !isCompilation )
1521 qb.addMatch( QueryBuilder::tabArtist, QueryBuilder::valID, artist_id );
1522 qb.sortBy( QueryBuilder::tabSong, QueryBuilder::valDiscNumber );
1523 qb.sortBy( QueryBuilder::tabSong, QueryBuilder::valTrack );
1524 QStringList ret = qb.run();
1526 uint returnValues = qb.countReturnValues();
1527 if ( returnValues > 1 )
1529 QStringList ret2;
1530 for ( QStringList::size_type i = 0; i < ret.size(); i += returnValues )
1531 ret2 << ret[ i ];
1532 return ret2;
1534 else
1535 return ret;
1538 QStringList
1539 CollectionDB::albumDiscTracks( const QString &artist_id, const QString &album_id, const QString &discNumber)
1541 QStringList rs;
1542 rs = query( QString( "SELECT tags.deviceid, tags.url FROM tags, year WHERE tags.album = %1 AND "
1543 "tags.artist = %2 AND year.id = tags.year AND tags.discnumber = %3 "
1544 + deviceidSelection() + " ORDER BY tags.track;" )
1545 .arg( album_id )
1546 .arg( artist_id )
1547 .arg( discNumber ) );
1548 QStringList result;
1549 oldForeach( rs )
1551 const int id = (*it).toInt();
1552 result << MountPointManager::instance()->getAbsolutePath( id, *(++it) );
1554 return result;
1557 QStringList
1558 CollectionDB::artistTracks( const QString &artist_id )
1560 QStringList rs = query( QString( "SELECT tags.deviceid, tags.url FROM tags, album "
1561 "WHERE tags.artist = '%1' AND album.id = tags.album " + deviceidSelection() +
1562 "ORDER BY album.name, tags.discnumber, tags.track;" )
1563 .arg( artist_id ) );
1564 QStringList result = QStringList();
1565 oldForeach( rs )
1567 const int id = (*it).toInt();
1568 result << MountPointManager::instance()->getAbsolutePath( id, *(++it) );
1570 return result;
1574 void
1575 CollectionDB::addImageToAlbum( const QString& image, Q3ValueList< QPair<QString, QString> > info, const bool temporary )
1577 int deviceid = MountPointManager::instance()->getIdForUrl( image );
1578 QString rpath = MountPointManager::instance()->getRelativePath( deviceid, image );
1579 for ( Q3ValueList< QPair<QString, QString> >::ConstIterator it = info.begin(); it != info.end(); ++it )
1581 if ( (*it).first.isEmpty() || (*it).second.isEmpty() )
1582 continue;
1584 QString sql = QString( "INSERT INTO images%1 ( path, deviceid, artist, album ) VALUES ( '%3', %2" )
1585 .arg( temporary ? "_temp" : "" )
1586 .arg( deviceid )
1587 .arg( escapeString( rpath ) );
1588 sql += QString( ", '%1'" ).arg( escapeString( (*it).first ) );
1589 sql += QString( ", '%1' );" ).arg( escapeString( (*it).second ) );
1591 // debug() << "Added image for album: " << (*it).first << " - " << (*it).second << ": " << image;
1592 insert( sql, NULL );
1596 void
1597 CollectionDB::addEmbeddedImage( const QString& path, const QString& hash, const QString& description )
1599 // debug() << "Added embedded image hash " << hash << " for file " << path;
1600 //TODO: figure out what this embedded table does and then add the necessary code
1601 //what are embedded images anyway?
1602 int deviceid = MountPointManager::instance()->getIdForUrl( path );
1603 QString rpath = MountPointManager::instance()->getRelativePath(deviceid, path );
1604 insert( QString( "INSERT INTO embed_temp ( url, deviceid, hash, description ) VALUES ( '%2', %1, '%3', '%4' );" )
1605 .arg( deviceid )
1606 .arg( escapeString( rpath ), escapeString( hash ), escapeString( description ) ), NULL );
1609 void
1610 CollectionDB::removeOrphanedEmbeddedImages()
1612 //TODO refactor
1613 // do it the hard way, since a delete subquery wont work on MySQL
1614 QStringList orphaned = query( "SELECT embed.deviceid, embed.url FROM embed LEFT JOIN tags ON embed.url = tags.url AND embed.deviceid = tags.deviceid WHERE tags.url IS NULL;" );
1615 oldForeach( orphaned ) {
1616 QString deviceid = *it;
1617 QString rpath = *(++it);
1618 query( QString( "DELETE FROM embed WHERE embed.deviceid = %1 AND embed.url = '%2';" )
1619 .arg( deviceid, escapeString( rpath ) ) );
1623 QPixmap
1624 CollectionDB::createDragPixmapFromSQL( const QString &sql, QString textOverRide )
1626 // it is too slow to check if the url is actually in the colleciton.
1627 //TODO mountpointmanager: figure out what has to be done here
1628 QStringList values = instance()->query( sql );
1629 KUrl::List list;
1630 oldForeach( values )
1632 KUrl u = KUrl( *it );
1633 if( u.isValid() )
1634 list += u;
1636 return createDragPixmap( list, textOverRide );
1639 QPixmap
1640 CollectionDB::createDragPixmap( const KUrl::List &urls, QString textOverRide )
1642 // settings
1643 const int maxCovers = 4; // maximum number of cover images to show
1644 const int coverSpacing = 20; // spacing between stacked covers
1645 const int fontSpacing = 5; // spacing between covers and info text
1646 const int coverW = AmarokConfig::coverPreviewSize() > 100 ? 100 : AmarokConfig::coverPreviewSize();
1647 const int coverH = coverW;
1648 const int margin = 2; //px margin
1650 int covers = 0;
1651 int songs = 0;
1652 int pixmapW = 0;
1653 int pixmapH = 0;
1654 int remoteUrls = 0;
1655 int playlists = 0;
1657 QMap<QString, int> albumMap;
1658 QPixmap coverPm[maxCovers];
1660 QString song, album;
1663 // iterate urls, get covers and count artist/albums
1664 bool correctAlbumCount = true;
1665 KUrl::List::ConstIterator it = urls.begin();
1666 for ( ; it != urls.end(); ++it )
1668 //PORT 2.0
1669 if( /*PlaylistFile::isPlaylistFile( *it )
1670 ||*/ (*it).protocol() == "playlist" || (*it).protocol() == "smartplaylist"
1671 || (*it).protocol() == "dynamic" )
1673 playlists++;
1675 else if( (*it).isLocalFile() )
1677 songs++;
1679 if( covers >= maxCovers )
1681 correctAlbumCount = false;
1682 continue;
1685 MetaBundle mb( *it );
1687 song = mb.title();
1688 album = mb.album();
1690 QString artist = mb.artist();
1691 if( mb.compilation() == MetaBundle::CompilationYes )
1692 artist = QString( "Various_AMAROK_Artists" ); // magic key for the albumMap!
1694 if( !albumMap.contains( artist + album ) )
1696 albumMap[ artist + album ] = 1;
1697 QString coverName = CollectionDB::instance()->albumImage( mb.artist(), album, false, coverW );
1699 if ( !coverName.endsWith( "@nocover.png" ) )
1700 coverPm[covers++].load( coverName );
1703 else
1705 MetaBundle mb( *it );
1706 if( !albumMap.contains( mb.artist() + mb.album() ) )
1708 albumMap[ mb.artist() + mb.album() ] = 1;
1709 QString coverName = CollectionDB::instance()->podcastImage( mb, false, coverW );
1711 if ( covers < maxCovers && !coverName.endsWith( "@nocover.png" ) )
1712 coverPm[covers++].load( coverName );
1714 remoteUrls++;
1718 // make better text...
1719 int albums = albumMap.count();
1720 QString text;
1722 if( !textOverRide.isEmpty() )
1724 text = textOverRide;
1726 else if( ( songs && remoteUrls ) ||
1727 ( songs && playlists ) ||
1728 ( playlists && remoteUrls ) )
1730 text = i18np( "One item", "%1 items", songs + remoteUrls + playlists );
1732 else if( songs > 0 )
1734 if( correctAlbumCount ) {
1735 text = i18nc( "X songs from X albums", "%2 from %1" );
1736 text = text.arg( albums == 1 && !album.isEmpty() ? album : i18np( "one album", "%1 albums",albums ) );
1738 else
1739 text = "%1";
1740 text = text.arg( songs == 1 && !song.isEmpty() ? song : i18np( "One song", "%1 songs", songs ) );
1742 else if( playlists > 0 )
1743 text = i18np( "One playlist", "%1 playlists", playlists );
1744 else if ( remoteUrls > 0 )
1745 text = i18np( "One remote file", "%1 remote files", remoteUrls );
1746 else
1747 text = i18n( "Unknown item" );
1749 QFont font;
1750 QFontMetrics fm( font );
1751 int fontH = fm.height() + margin;
1752 int minWidth = fm.width( text ) + margin*2; //margin either side
1754 if ( covers > 0 )
1756 // insert "..." cover as first image if appropriate
1757 if ( covers < albums )
1759 if ( covers < maxCovers ) covers++;
1760 for ( int i = maxCovers-1; i > 0; i-- )
1761 coverPm[i] = coverPm[i-1];
1763 QImage im( KStandardDirs::locate( "data","amarok/images/more_albums.png" ) );
1764 coverPm[0] = QPixmap::fromImage( im.scaled( coverW, coverH, Qt::KeepAspectRatio, Qt::SmoothTransformation ) );
1767 pixmapH = coverPm[0].height();
1768 pixmapW = coverPm[0].width();
1770 // caluclate pixmap height
1771 int dW, dH;
1772 for ( int i = 1; i < covers; i++ )
1774 dW = coverPm[i].width() - coverPm[i-1].width() + coverSpacing;
1775 dH = coverPm[i].height() - coverPm[i-1].height() + coverSpacing;
1776 if ( dW > 0 ) pixmapW += dW;
1777 if ( dH > 0 ) pixmapH += dH;
1779 pixmapH += fontSpacing + fontH;
1781 if ( pixmapW < minWidth )
1782 pixmapW = minWidth;
1784 else
1786 pixmapW = minWidth;
1787 pixmapH = fontH;
1790 QPixmap pmdrag( pixmapW, pixmapH );
1791 QPixmap pmtext( pixmapW, fontH );
1793 QPalette palette = QToolTip::palette();
1795 QPainter p;
1796 p.begin( &pmtext );
1797 p.fillRect( 0, 0, pixmapW, fontH, QBrush( Qt::black ) ); // border
1798 p.fillRect( 1, 1, pixmapW-margin, fontH-margin, palette.brush( QPalette::Normal, QColorGroup::Background ) );
1799 p.setBrush( palette.color( QPalette::Normal, QColorGroup::Text ) );
1800 p.setFont( font );
1801 p.drawText( margin, fm.ascent() + 1, text );
1802 p.end();
1804 QBitmap pmtextMask(pixmapW, fontH);
1805 pmtextMask.fill( Qt::color1 );
1807 // when we have found no covers, just display the text message
1808 if( !covers )
1810 pmtext.setMask(pmtextMask);
1811 return pmtext;
1814 // compose image
1815 p.begin( &pmdrag );
1816 for ( int i = 0; i < covers; i++ )
1817 bitBlt( &pmdrag, i * coverSpacing, i * coverSpacing, &coverPm[i], 0 );
1819 bitBlt( &pmdrag, 0, pixmapH - fontH, &pmtext, 0 );
1820 p.end();
1822 QBitmap pmdragMask( pmdrag.size() );
1823 pmdragMask.clear();
1824 for ( int i = 0; i < covers; i++ )
1826 QBitmap coverMask( coverPm[i].width(), coverPm[i].height() );
1827 coverMask.fill( Qt::color1 );
1828 bitBlt( &pmdragMask, i * coverSpacing, i * coverSpacing, &coverMask, 0 );
1830 bitBlt( &pmdragMask, 0, pixmapH - fontH, &pmtextMask, 0 );
1831 pmdrag.setMask( pmdragMask );
1833 return pmdrag;
1836 QImage
1837 CollectionDB::fetchImage( const KUrl& url, QString &/*tmpFile*/ )
1839 if ( url.protocol() != "file" )
1841 QString tmpFile;
1842 KIO::NetAccess::download( url, tmpFile, 0 ); //TODO set 0 to the window, though it probably doesn't really matter
1843 return QImage( tmpFile );
1845 else
1847 return QImage( url.path() );
1851 bool
1852 CollectionDB::setAlbumImage( const QString& artist, const QString& album, const KUrl& url )
1854 QString tmpFile;
1855 bool success = setAlbumImage( artist, album, fetchImage(url, tmpFile) );
1856 KIO::NetAccess::removeTempFile( tmpFile ); //only removes file if it was created with NetAccess
1857 return success;
1861 bool
1862 CollectionDB::setAlbumImage( const QString& artist, const QString& album, QImage img, const QString& amazonUrl, const QString& asin )
1864 //show a wait cursor for the duration
1865 Amarok::OverrideCursor keep;
1867 const bool isCompilation = albumIsCompilation( QString::number( albumID( album, false, false, true ) ) );
1868 const QString artist_ = isCompilation ? "" : artist;
1870 // remove existing album covers
1871 removeAlbumImage( artist_, album );
1873 QByteArray key = md5sum( artist_, album );
1874 newAmazonReloadDate(asin, AmarokConfig::amazonLocale(), key);
1875 // Save Amazon product page URL as embedded string, for later retreival
1876 if ( !amazonUrl.isEmpty() )
1877 img.setText( "amazon-url", 0, amazonUrl );
1879 const bool b = img.save( largeCoverDir().filePath( key ), "PNG");
1880 emit coverChanged( artist_, album );
1881 return b;
1885 QString
1886 CollectionDB::podcastImage( const MetaBundle &bundle, const bool withShadow, uint width )
1888 PodcastEpisodeBundle peb;
1889 PodcastChannelBundle pcb;
1891 KUrl url = bundle.url().url();
1893 if( getPodcastEpisodeBundle( url, &peb ) )
1895 url = peb.parent().url();
1898 if( getPodcastChannelBundle( url, &pcb ) )
1900 if( pcb.imageURL().isValid() )
1901 return podcastImage( pcb.imageURL().url(), withShadow, width );
1904 return notAvailCover( withShadow, width );
1908 QString
1909 CollectionDB::podcastImage( const QString &remoteURL, const bool withShadow, uint width )
1911 // we aren't going to need a 1x1 size image. this is just a quick hack to be able to show full size images.
1912 // width of 0 == full size
1913 if( width == 1 )
1914 width = AmarokConfig::coverPreviewSize();
1916 QString s = findAmazonImage( "Podcast", remoteURL, width );
1918 if( s.isEmpty() )
1920 s = notAvailCover( withShadow, width );
1922 const KUrl url = KUrl( remoteURL );
1923 if( url.isValid() ) //KIO crashes with invalid URLs
1925 KIO::Job *job = KIO::storedGet( url, KIO::NoReload, KIO::HideProgressInfo );
1926 m_podcastImageJobs[job] = remoteURL;
1927 connect( job, SIGNAL( result( KIO::Job* ) ), SLOT( podcastImageResult( KIO::Job* ) ) );
1931 if ( withShadow )
1932 s = makeShadowedImage( s );
1934 return s;
1937 void
1938 CollectionDB::podcastImageResult( KIO::Job *gjob )
1940 QString url = m_podcastImageJobs[gjob];
1941 m_podcastImageJobs.remove( gjob );
1943 KIO::StoredTransferJob *job = dynamic_cast<KIO::StoredTransferJob *>( gjob );
1944 if( !job )
1946 debug() << "connected to wrong job type";
1947 return;
1950 if( job->error() )
1952 debug() << "job finished with error";
1953 return;
1956 if( job->isErrorPage() )
1958 debug() << "error page";
1959 return;
1962 QImage image = QImage::fromData( job->data() );
1963 if( !image.isNull() )
1965 if( url.isEmpty() )
1966 url = job->url().url();
1968 QByteArray key = md5sum( "Podcast", url );
1969 if( image.save( largeCoverDir().filePath( key ), "PNG") )
1970 emit imageFetched( url );
1975 QString
1976 CollectionDB::albumImage( const QString &artist, const QString &album, bool withShadow, uint width, bool* embedded )
1978 QString s;
1979 // we aren't going to need a 1x1 size image. this is just a quick hack to be able to show full size images.
1980 // width of 0 == full size
1981 if( width == 1 )
1982 width = AmarokConfig::coverPreviewSize();
1983 if( embedded )
1984 *embedded = false;
1986 s = findAmazonImage( artist, album, width );
1988 if( s.isEmpty() )
1989 s = findAmazonImage( "", album, width ); // handle compilations
1991 if( s.isEmpty() )
1992 s = findDirectoryImage( artist, album, width );
1994 if( s.isEmpty() )
1996 s = findEmbeddedImage( artist, album, width );
1997 if( embedded && !s.isEmpty() )
1998 *embedded = true;
2001 if( s.isEmpty() )
2002 s = notAvailCover( withShadow, width );
2004 if ( withShadow )
2005 s = makeShadowedImage( s );
2007 return s;
2011 QString
2012 CollectionDB::albumImage( const uint artist_id, const uint album_id, bool withShadow, uint width, bool* embedded )
2014 return albumImage( artistValue( artist_id ), albumValue( album_id ), withShadow, width, embedded );
2018 QString
2019 CollectionDB::albumImage( const MetaBundle &trackInformation, bool withShadow, uint width, bool* embedded )
2021 QString s;
2022 if( width == 1 )
2023 width = AmarokConfig::coverPreviewSize();
2025 QString album = trackInformation.album();
2026 QString artist = trackInformation.artist();
2028 // this art is per track, so should check for it first
2029 s = findMetaBundleImage( trackInformation, width );
2030 if( embedded )
2031 *embedded = !s.isEmpty();
2033 if( s.isEmpty() )
2034 s = findAmazonImage( artist, album, width );
2035 if( s.isEmpty() )
2036 s = findAmazonImage( "", album, width ); // handle compilations
2037 if( s.isEmpty() )
2038 s = findDirectoryImage( artist, album, width );
2039 if( s.isEmpty() )
2040 s = notAvailCover( withShadow, width );
2041 if ( withShadow )
2042 s = makeShadowedImage( s );
2043 return s;
2047 QString
2048 CollectionDB::makeShadowedImage( const QString& albumImage, bool cache )
2050 const QImage original( albumImage );
2052 if( original.hasAlphaChannel() )
2053 return albumImage;
2055 const QFileInfo fileInfo( albumImage );
2056 const uint shadowSize = static_cast<uint>( original.width() / 100.0 * 6.0 );
2057 const QString cacheFile = fileInfo.fileName() + "@shadow";
2058 QImage shadow;
2060 if ( !cache && cacheCoverDir().exists( cacheFile ) )
2061 return cacheCoverDir().filePath( cacheFile );
2063 const QString folder = Amarok::saveLocation( "covershadow-cache/" );
2064 const QString file = QString( "shadow_albumcover%1x%2.png" ).arg( original.width() + shadowSize ).arg( original.height() + shadowSize );
2065 if ( QFile::exists( folder + file ) )
2066 shadow.load( folder + file );
2067 else {
2068 shadow.load( KStandardDirs::locate( "data", "amarok/images/shadow_albumcover.png" ) );
2069 shadow = shadow.scaled( original.width() + shadowSize, original.height() + shadowSize, Qt::IgnoreAspectRatio, Qt::SmoothTransformation );
2070 shadow.save( folder + file, "PNG" );
2073 QImage target( shadow );
2074 bitBlt( &target, 0, 0, &original );
2076 if ( cache ) {
2077 target.save( cacheCoverDir().filePath( cacheFile ), "PNG" );
2078 return cacheCoverDir().filePath( cacheFile );
2081 target.save( albumImage, "PNG" );
2082 return albumImage;
2086 // Amazon Image
2087 QString
2088 CollectionDB::findAmazonImage( const QString &artist, const QString &album, uint width )
2090 QByteArray widthKey = makeWidthKey( width );
2092 if ( artist.isEmpty() && album.isEmpty() )
2093 return QString();
2095 QByteArray key = md5sum( artist, album );
2097 // check cache for existing cover
2098 if ( cacheCoverDir().exists( widthKey + key ) )
2099 return cacheCoverDir().filePath( widthKey + key );
2101 // we need to create a scaled version of this cover
2102 QDir imageDir = largeCoverDir();
2103 if ( imageDir.exists( key ) )
2105 if ( width > 1 )
2107 QImage img( imageDir.filePath( key ) );
2108 img.scaled( width, width, Qt::KeepAspectRatio, Qt::SmoothTransformation ).save( cacheCoverDir().filePath( widthKey + key ), "PNG" );
2110 return cacheCoverDir().filePath( widthKey + key );
2112 else
2113 return imageDir.filePath( key );
2116 return QString();
2120 QString
2121 CollectionDB::findDirectoryImage( const QString& artist, const QString& album, uint width )
2123 if ( width == 1 )
2124 width = AmarokConfig::coverPreviewSize();
2125 QByteArray widthKey = makeWidthKey( width );
2126 if ( album.isEmpty() )
2127 return QString();
2129 IdList list = MountPointManager::instance()->getMountedDeviceIds();
2130 QString deviceIds;
2131 oldForeachType( IdList, list )
2133 if ( !deviceIds.isEmpty() ) deviceIds = deviceIds + ",";
2134 deviceIds += QString::number(*it);
2137 QStringList rs;
2138 if ( artist == i18n( "Various Artists" ) || artist.isEmpty() )
2140 rs = query( QString(
2141 "SELECT images.deviceid,images.path FROM images, artist, tags "
2142 "WHERE images.artist = artist.name "
2143 "AND artist.id = tags.artist "
2144 "AND tags.sampler = %1 "
2145 "AND images.album %2 "
2146 "AND images.deviceid IN (%3) " )
2147 .arg( boolT() )
2148 .arg( CollectionDB::likeCondition( album ) )
2149 .arg( deviceIds ) );
2151 else
2153 rs = query( QString(
2154 "SELECT images.deviceid,images.path FROM images WHERE artist %1 AND album %2 AND deviceid IN (%3) ORDER BY path;" )
2155 .arg( CollectionDB::likeCondition( artist ) )
2156 .arg( CollectionDB::likeCondition( album ) )
2157 .arg( deviceIds ) );
2159 QStringList values = URLsFromQuery( rs );
2160 if ( !values.isEmpty() )
2162 QString image( values.first() );
2163 uint matches = 0;
2164 uint maxmatches = 0;
2165 QRegExp iTunesArt( "^AlbumArt_.*Large" );
2166 for ( int i = 0; i < values.count(); i++ )
2168 matches = values[i].count( "front", Qt::CaseInsensitive ) + values[i].count( "cover", Qt::CaseInsensitive ) + values[i].count( "folder", Qt::CaseInsensitive ) + values[i].count( iTunesArt );
2169 if ( matches > maxmatches )
2171 image = values[i];
2172 maxmatches = matches;
2176 QByteArray key = md5sum( artist, album, image );
2178 if ( width > 1 )
2180 QString path = cacheCoverDir().filePath( widthKey + key );
2181 if ( !QFile::exists( path ) )
2183 QImage img( image );
2184 img.scaled( width, width, Qt::KeepAspectRatio, Qt::SmoothTransformation ).save( path, "PNG" );
2186 return path;
2188 else //large image
2189 return image;
2191 return QString();
2195 QString
2196 CollectionDB::findEmbeddedImage( const QString& artist, const QString& album, uint width )
2198 // In the case of multiple embedded images, we arbitrarily choose one from the newest file
2199 // could potentially select multiple images within a file based on description, although a
2200 // lot of tagging software doesn't fill in that field, so we just get whatever the DB
2201 // happens to return for us
2202 QStringList rs;
2203 if ( artist == i18n("Various Artists") || artist.isEmpty() ) {
2204 // VAs need special handling to not match on artist name but instead check for sampler flag
2205 rs = query( QString(
2206 "SELECT embed.hash, embed.deviceid, embed.url FROM "
2207 "tags INNER JOIN embed ON tags.url = embed.url "
2208 "INNER JOIN album ON tags.album = album.id "
2209 "WHERE "
2210 "album.name = '%1' "
2211 "AND tags.sampler = %2 "
2212 "ORDER BY modifydate DESC LIMIT 1;" )
2213 .arg( escapeString( album ) )
2214 .arg( boolT() ) );
2215 } else {
2216 rs = query( QString(
2217 "SELECT embed.hash, embed.deviceid, embed.url FROM "
2218 "tags INNER JOIN embed ON tags.url = embed.url "
2219 "INNER JOIN artist ON tags.artist = artist.id "
2220 "INNER JOIN album ON tags.album = album.id "
2221 "WHERE "
2222 "artist.name = '%1' "
2223 "AND album.name = '%2' "
2224 "ORDER BY modifydate DESC LIMIT 1;" )
2225 .arg( escapeString( artist ) )
2226 .arg( escapeString( album ) ) );
2229 QStringList values = QStringList();
2230 if ( rs.count() == 3 ) {
2231 values += rs.first();
2232 values += MountPointManager::instance()->getAbsolutePath( rs[1].toInt(), rs[2] );
2235 if ( values.count() == 2 ) {
2236 QByteArray hash = values.first().toUtf8();
2237 QString result = loadHashFile( hash, width );
2238 if ( result.isEmpty() ) {
2239 // need to get original from file first
2240 MetaBundle mb( KUrl( values.last() ) );
2241 if ( extractEmbeddedImage( mb, hash ) ) {
2242 // try again, as should be possible now
2243 result = loadHashFile( hash, width );
2246 return result;
2248 return QString();
2252 QString
2253 CollectionDB::findMetaBundleImage( const MetaBundle& trackInformation, uint width )
2255 int deviceid = MountPointManager::instance()->getIdForUrl( trackInformation.url() );
2256 QString rpath = MountPointManager::instance()->getRelativePath( deviceid, trackInformation.url().path() );
2257 QStringList values =
2258 query( QString(
2259 "SELECT embed.hash FROM tags LEFT JOIN embed ON tags.url = embed.url "
2260 " AND tags.deviceid = embed.deviceid WHERE tags.url = '%2' AND tags.deviceid = %1 ORDER BY hash DESC LIMIT 1;" )
2261 .arg( deviceid ).arg( escapeString( rpath ) ) );
2263 if ( values.empty() || !values.first().isEmpty() ) {
2264 QByteArray hash;
2265 QString result;
2266 if( !values.empty() ) { // file in collection, so we know the hash
2267 hash = values.first().toUtf8();
2268 result = loadHashFile( hash, width );
2270 if ( result.isEmpty() ) {
2271 // need to get original from file first
2272 if ( extractEmbeddedImage( trackInformation, hash ) ) {
2273 // try again, as should be possible now
2274 result = loadHashFile( hash, width );
2277 return result;
2279 return QString();
2283 QByteArray
2284 CollectionDB::makeWidthKey( uint width )
2286 return QString::number( width ).toLocal8Bit() + '@';
2290 bool
2291 CollectionDB::removeAlbumImage( const QString &artist, const QString &album )
2293 DEBUG_BLOCK
2295 QByteArray widthKey = "*@";
2296 QByteArray key = md5sum( artist, album );
2297 query( "DELETE FROM amazon WHERE filename='" + key + '\'' );
2299 // remove scaled versions of images (and add the asterisk for the shadow-caches)
2300 QStringList filter;
2301 filter << widthKey + key + '*';
2302 QStringList scaledList = cacheCoverDir().entryList( filter );
2303 if ( scaledList.count() > 0 )
2304 for ( int i = 0; i < scaledList.count(); i++ )
2305 QFile::remove( cacheCoverDir().filePath( scaledList[ i ] ) );
2307 bool deleted = false;
2308 // remove large, original image
2309 if ( largeCoverDir().exists( key ) && QFile::remove( largeCoverDir().filePath( key ) ) )
2310 deleted = true;
2312 QString hardImage = findDirectoryImage( artist, album );
2313 debug() << "hardImage: " << hardImage;
2315 if( !hardImage.isEmpty() )
2317 int id = MountPointManager::instance()->getIdForUrl( hardImage );
2318 QString rpath = MountPointManager::instance()->getRelativePath( id, hardImage );
2319 query( "DELETE FROM images WHERE path='" + escapeString( hardImage ) + "' AND deviceid = " + QString::number( id ) + ';' );
2320 deleted = true;
2323 if ( deleted )
2325 emit coverRemoved( artist, album );
2326 return true;
2329 return false;
2333 bool
2334 CollectionDB::removeAlbumImage( const uint artist_id, const uint album_id )
2336 return removeAlbumImage( artistValue( artist_id ), albumValue( album_id ) );
2340 QString
2341 CollectionDB::notAvailCover( const bool withShadow, int width )
2343 if ( width <= 1 )
2344 width = AmarokConfig::coverPreviewSize();
2345 QString widthKey = QString::number( width ) + '@';
2346 QString s;
2348 if( cacheCoverDir().exists( widthKey + "nocover.png" ) )
2349 s = cacheCoverDir().filePath( widthKey + "nocover.png" );
2350 else
2352 m_noCover.scaled( width, width, Qt::KeepAspectRatio, Qt::SmoothTransformation ).save( cacheCoverDir().filePath( widthKey + "nocover.png" ), "PNG" );
2353 s = cacheCoverDir().filePath( widthKey + "nocover.png" );
2356 if ( withShadow )
2357 s = makeShadowedImage( s );
2359 return s;
2363 QStringList
2364 CollectionDB::artistList( bool withUnknowns, bool withCompilations )
2366 QueryBuilder qb;
2367 qb.addReturnValue( QueryBuilder::tabArtist, QueryBuilder::valName );
2369 if ( !withUnknowns )
2370 qb.excludeMatch( QueryBuilder::tabArtist, i18n( "Unknown" ) );
2371 if ( !withCompilations )
2372 qb.setOptions( QueryBuilder::optNoCompilations );
2374 qb.groupBy( QueryBuilder::tabArtist, QueryBuilder::valName );
2375 qb.setOptions( QueryBuilder::optShowAll );
2376 qb.sortBy( QueryBuilder::tabArtist, QueryBuilder::valName );
2377 return qb.run();
2381 QStringList
2382 CollectionDB::composerList( bool withUnknowns, bool withCompilations )
2384 DEBUG_BLOCK
2385 QueryBuilder qb;
2386 qb.addReturnValue( QueryBuilder::tabComposer, QueryBuilder::valName );
2388 if ( !withUnknowns )
2389 qb.excludeMatch( QueryBuilder::tabComposer, i18n( "Unknown" ) );
2390 if ( !withCompilations )
2391 qb.setOptions( QueryBuilder::optNoCompilations );
2393 qb.groupBy( QueryBuilder::tabComposer, QueryBuilder::valName );
2394 qb.setOptions( QueryBuilder::optShowAll );
2395 qb.sortBy( QueryBuilder::tabComposer, QueryBuilder::valName );
2396 return qb.run();
2400 QStringList
2401 CollectionDB::albumList( bool withUnknowns, bool withCompilations )
2403 QueryBuilder qb;
2404 qb.addReturnValue( QueryBuilder::tabAlbum, QueryBuilder::valName );
2406 if ( !withUnknowns )
2407 qb.excludeMatch( QueryBuilder::tabAlbum, i18n( "Unknown" ) );
2408 if ( !withCompilations )
2409 qb.setOptions( QueryBuilder::optNoCompilations );
2411 qb.groupBy( QueryBuilder::tabAlbum, QueryBuilder::valName );
2412 qb.setOptions( QueryBuilder::optShowAll );
2413 qb.sortBy( QueryBuilder::tabAlbum, QueryBuilder::valName );
2414 return qb.run();
2418 QStringList
2419 CollectionDB::genreList( bool withUnknowns, bool withCompilations )
2421 QueryBuilder qb;
2422 qb.addReturnValue( QueryBuilder::tabGenre, QueryBuilder::valName );
2424 //Only report genres that currently have at least one song
2425 qb.addFilter( QueryBuilder::tabSong, "" );
2427 if ( !withUnknowns )
2428 qb.excludeMatch( QueryBuilder::tabGenre, i18n( "Unknown" ) );
2429 if ( !withCompilations )
2430 qb.setOptions( QueryBuilder::optNoCompilations );
2432 qb.groupBy( QueryBuilder::tabGenre, QueryBuilder::valName );
2433 qb.setOptions( QueryBuilder::optShowAll );
2434 qb.sortBy( QueryBuilder::tabGenre, QueryBuilder::valName );
2435 return qb.run();
2439 QStringList
2440 CollectionDB::yearList( bool withUnknowns, bool withCompilations )
2442 QueryBuilder qb;
2443 qb.addReturnValue( QueryBuilder::tabYear, QueryBuilder::valName );
2445 if ( !withUnknowns )
2446 qb.excludeMatch( QueryBuilder::tabYear, i18n( "Unknown" ) );
2447 if ( !withCompilations )
2448 qb.setOptions( QueryBuilder::optNoCompilations );
2450 qb.groupBy( QueryBuilder::tabYear, QueryBuilder::valName );
2451 qb.setOptions( QueryBuilder::optShowAll );
2452 qb.sortBy( QueryBuilder::tabYear, QueryBuilder::valName );
2453 return qb.run();
2456 QStringList
2457 CollectionDB::labelList()
2459 QueryBuilder qb;
2460 qb.addReturnValue( QueryBuilder::tabLabels, QueryBuilder::valName );
2461 qb.groupBy( QueryBuilder::tabLabels, QueryBuilder::valName );
2462 qb.setOptions( QueryBuilder::optShowAll );
2463 qb.sortBy( QueryBuilder::tabLabels, QueryBuilder::valName );
2464 return qb.run();
2467 QStringList
2468 CollectionDB::albumListOfArtist( const QString &artist, bool withUnknown, bool withCompilations )
2470 if (getDbConnectionType() == DbConnection::postgresql)
2472 return query( "SELECT DISTINCT album.name, lower( album.name ) AS __discard FROM tags, album, artist WHERE "
2473 "tags.album = album.id AND tags.artist = artist.id "
2474 "AND lower(artist.name) = lower('" + escapeString( artist ) + "') " +
2475 ( withUnknown ? QString() : "AND album.name <> '' " ) +
2476 ( withCompilations ? QString() : "AND tags.sampler = " + boolF() ) + deviceidSelection() +
2477 " ORDER BY lower( album.name );" );
2479 else
2481 return query( "SELECT DISTINCT album.name FROM tags, album, artist WHERE "
2482 "tags.album = album.id AND tags.artist = artist.id "
2483 "AND lower(artist.name) = lower('" + escapeString( artist ) + "') " +
2484 ( withUnknown ? QString() : "AND album.name <> '' " ) +
2485 ( withCompilations ? QString() : "AND tags.sampler = " + boolF() ) + deviceidSelection() +
2486 " ORDER BY lower( album.name );" );
2491 QStringList
2492 CollectionDB::artistAlbumList( bool withUnknown, bool withCompilations )
2494 if (getDbConnectionType() == DbConnection::postgresql)
2496 return query( "SELECT DISTINCT artist.name, album.name, lower( album.name ) AS __discard FROM tags, album, artist WHERE "
2497 "tags.album = album.id AND tags.artist = artist.id " +
2498 ( withUnknown ? QString() : "AND album.name <> '' AND artist.name <> '' " ) +
2499 ( withCompilations ? QString() : "AND tags.sampler = " + boolF() ) + deviceidSelection() +
2500 " ORDER BY lower( album.name );" );
2502 else
2504 return query( "SELECT DISTINCT artist.name, album.name FROM tags, album, artist WHERE "
2505 "tags.album = album.id AND tags.artist = artist.id " +
2506 ( withUnknown ? QString() : "AND album.name <> '' AND artist.name <> '' " ) +
2507 ( withCompilations ? QString() : "AND tags.sampler = " + boolF() ) + deviceidSelection() +
2508 " ORDER BY lower( album.name );" );
2512 bool
2513 CollectionDB::addPodcastChannel( const PodcastChannelBundle &pcb, const bool &replace )
2515 QString command;
2516 if( replace ) {
2517 command = "REPLACE INTO podcastchannels "
2518 "( url, title, weblink, image, comment, copyright, parent, directory"
2519 ", autoscan, fetchtype, autotransfer, haspurge, purgecount ) "
2520 "VALUES (";
2521 } else {
2522 command = "INSERT INTO podcastchannels "
2523 "( url, title, weblink, image, comment, copyright, parent, directory"
2524 ", autoscan, fetchtype, autotransfer, haspurge, purgecount ) "
2525 "VALUES (";
2528 QString title = pcb.title();
2529 KUrl link = pcb.link();
2530 KUrl image = pcb.imageURL();
2531 QString description = pcb.description();
2532 QString copyright = pcb.copyright();
2534 if( title.isEmpty() )
2535 title = pcb.url().prettyUrl();
2537 command += '\'' + escapeString( pcb.url().url() ) + "',";
2538 command += ( title.isEmpty() ? "NULL" : '\'' + escapeString( title ) + '\'' ) + ',';
2539 command += ( link.isEmpty() ? "NULL" : '\'' + escapeString( link.url() ) + '\'' ) + ',';
2540 command += ( image.isEmpty() ? "NULL" : '\'' + escapeString( image.url() ) + '\'' ) + ',';
2541 command += ( description.isEmpty() ? "NULL" : '\'' + escapeString( description ) + '\'' ) + ',';
2542 command += ( copyright.isEmpty() ? "NULL" : '\'' + escapeString( copyright ) + '\'' ) + ',';
2543 command += QString::number( pcb.parentId() ) + ",'";
2544 command += escapeString( pcb.saveLocation() ) + "',";
2545 command += pcb.autoscan() ? boolT() + ',' : boolF() + ',';
2546 command += QString::number( pcb.fetchType() ) + ',';
2547 command += pcb.autotransfer() ? boolT() + ',' : boolF() + ',';
2548 command += pcb.hasPurge() ? boolT() + ',' : boolF() + ',';
2549 command += QString::number( pcb.purgeCount() ) + ");";
2551 //FIXME: currently there's no way to check if an INSERT query failed or not - always return true atm.
2552 // Now it might be possible as insert returns the rowid.
2553 insert( command, NULL );
2554 return true;
2558 CollectionDB::addPodcastEpisode( const PodcastEpisodeBundle &episode, const int idToUpdate )
2560 QString command;
2562 if( idToUpdate ) {
2563 command = "REPLACE INTO podcastepisodes "
2564 "( id, url, localurl, parent, title, subtitle, composer, comment, filetype, createdate, guid, length, size, isNew ) "
2565 "VALUES (";
2566 } else {
2567 command = "INSERT INTO podcastepisodes "
2568 "( url, localurl, parent, title, subtitle, composer, comment, filetype, createdate, guid, length, size, isNew ) "
2569 "VALUES (";
2572 QString localurl = episode.localUrl().url();
2573 QString title = episode.title();
2574 QString subtitle = episode.subtitle();
2575 QString author = episode.author();
2576 QString description = episode.description();
2577 QString type = episode.type();
2578 QString date = episode.date();
2579 QString guid = episode.guid();
2580 int duration = episode.duration();
2581 uint size = episode.size();
2583 if( title.isEmpty() )
2584 title = episode.url().prettyUrl();
2586 if( idToUpdate )
2587 command += QString::number( idToUpdate ) + ',';
2589 command += '\'' + escapeString( episode.url().url() ) + "',";
2590 command += ( localurl.isEmpty() ? "NULL" : '\'' + escapeString( localurl ) + '\'' ) + ',';
2591 command += '\'' + escapeString( episode.parent().url()) + "',";
2592 command += ( title.isEmpty() ? "NULL" : '\'' + escapeString( title ) + '\'' ) + ',';
2593 command += ( subtitle.isEmpty() ? "NULL" : '\'' + escapeString( subtitle ) + '\'' ) + ',';
2594 command += ( author.isEmpty() ? "NULL" : '\'' + escapeString( author ) + '\'' ) + ',';
2595 command += ( description.isEmpty() ? "NULL" : '\'' + escapeString( description ) + '\'' ) + ',';
2596 command += ( type.isEmpty() ? "NULL" : '\'' + escapeString( type ) + '\'' ) + ',';
2597 command += ( date.isEmpty() ? "NULL" : '\'' + escapeString( date ) + '\'' ) + ',';
2598 command += ( guid.isEmpty() ? "NULL" : '\'' + escapeString( guid ) + '\'' ) + ',';
2599 command += QString::number( duration ) + ',';
2600 command += QString::number( size ) + ',';
2601 command += episode.isNew() ? boolT() + " );" : boolF() + " );";
2603 insert( command, NULL );
2605 if( idToUpdate ) return idToUpdate;
2606 //This is a bit of a hack. We have just inserted an item, so it is going to be the one with the
2607 //highest id. Change this if threaded insertions are used in the future.
2608 QStringList values = query( QString("SELECT id FROM podcastepisodes WHERE url='%1' ORDER BY id DESC;")
2609 .arg( escapeString( episode.url().url() ) ) );
2610 if( values.isEmpty() ) return -1;
2612 return values[0].toInt();
2615 Q3ValueList<PodcastChannelBundle>
2616 CollectionDB::getPodcastChannels()
2618 QString command = "SELECT url, title, weblink, image, comment, copyright, parent, directory "
2619 ", autoscan, fetchtype, autotransfer, haspurge, purgecount FROM podcastchannels;";
2621 QStringList values = query( command );
2622 Q3ValueList<PodcastChannelBundle> bundles;
2624 oldForeach( values )
2626 PodcastChannelBundle pcb;
2627 pcb.setUrl ( KUrl(*it) );
2628 pcb.setTitle ( *++it );
2629 pcb.setLink ( KUrl(*++it) );
2630 pcb.setImageURL ( KUrl(*++it) );
2631 pcb.setDescription ( *++it );
2632 pcb.setCopyright ( *++it );
2633 pcb.setParentId ( (*++it).toInt() );
2634 pcb.setSaveLocation( *++it );
2635 pcb.setAutoScan ( *++it == boolT() ? true : false );
2636 pcb.setFetchType ( (*++it).toInt() );
2637 pcb.setAutoTransfer( *++it == boolT() ? true : false );
2638 pcb.setPurge ( *++it == boolT() ? true : false );
2639 pcb.setPurgeCount ( (*++it).toInt() );
2641 bundles.append( pcb );
2644 return bundles;
2647 Q3ValueList<PodcastEpisodeBundle>
2648 CollectionDB::getPodcastEpisodes( const KUrl &parent, bool onlyNew, int limit )
2650 QString command = QString( "SELECT id, url, localurl, parent, guid, title, subtitle, composer, comment, filetype, createdate, length, size, isNew FROM podcastepisodes WHERE ( parent='%1'" ).arg( parent.url() );
2651 if( onlyNew )
2652 command += QString( " AND isNew='%1'" ).arg( boolT() );
2653 command += " ) ORDER BY id";
2654 if( limit != -1 )
2655 command += QString( " DESC LIMIT %1 OFFSET 0" ).arg( limit );
2656 command += ';';
2658 QStringList values = query( command );
2659 Q3ValueList<PodcastEpisodeBundle> bundles;
2661 oldForeach( values )
2663 PodcastEpisodeBundle peb;
2664 peb.setDBId ( (*it).toInt() );
2665 peb.setUrl ( KUrl(*++it) );
2666 if( *++it != "NULL" )
2667 peb.setLocalURL ( KUrl(*it) );
2668 peb.setParent ( KUrl(*++it) );
2669 peb.setGuid ( *++it );
2670 peb.setTitle ( *++it );
2671 if( *++it != NULL )
2672 peb.setSubtitle( *it );
2673 peb.setAuthor ( *++it );
2674 peb.setDescription ( *++it );
2675 peb.setType ( *++it );
2676 peb.setDate ( *++it );
2677 peb.setDuration ( (*++it).toInt() );
2678 if( *++it == NULL )
2679 peb.setSize ( 0 );
2680 else
2681 peb.setSize ( (*it).toInt() );
2682 peb.setNew ( (*++it) == boolT() ? true : false );
2684 bundles.append( peb );
2687 return bundles;
2690 PodcastEpisodeBundle
2691 CollectionDB::getPodcastEpisodeById( int id )
2693 QString command = QString( "SELECT url, localurl, parent, guid, title, subtitle, composer, comment, filetype, createdate, length, size, isNew FROM podcastepisodes WHERE id=%1;").arg( id );
2695 QStringList values = query( command );
2696 PodcastEpisodeBundle peb;
2697 oldForeach( values )
2699 peb.setDBId ( id );
2700 peb.setUrl ( KUrl(*it) );
2701 if( *++it != "NULL" )
2702 peb.setLocalURL( KUrl(*it) );
2703 peb.setParent ( KUrl(*++it) );
2704 peb.setGuid ( *++it );
2705 peb.setTitle ( *++it );
2706 peb.setSubtitle ( *++it );
2707 peb.setAuthor ( *++it );
2708 peb.setDescription ( *++it );
2709 peb.setType ( *++it );
2710 peb.setDate ( *++it );
2711 peb.setDuration ( (*++it).toInt() );
2712 if( *++it == NULL )
2713 peb.setSize ( 0 );
2714 else
2715 peb.setSize ( (*it).toInt() );
2716 peb.setNew ( (*++it) == boolT() ? true : false );
2719 return peb;
2722 bool
2723 CollectionDB::getPodcastEpisodeBundle( const KUrl &url, PodcastEpisodeBundle *peb )
2725 int id = 0;
2726 if( url.isLocalFile() )
2728 QStringList values =
2729 query( QString( "SELECT id FROM podcastepisodes WHERE localurl = '%1';" )
2730 .arg( escapeString( url.url() ) ) );
2731 if( !values.isEmpty() )
2732 id = values[0].toInt();
2734 else
2736 QStringList values =
2737 query( QString( "SELECT id FROM podcastepisodes WHERE url = '%1';" )
2738 .arg( escapeString( url.url() ) ) );
2739 if( !values.isEmpty() )
2740 id = values[0].toInt();
2743 if( id )
2745 *peb = getPodcastEpisodeById( id );
2746 return true;
2749 return false;
2752 bool
2753 CollectionDB::getPodcastChannelBundle( const KUrl &url, PodcastChannelBundle *pcb )
2755 QStringList values = query( QString(
2756 "SELECT url, title, weblink, image, comment, copyright, parent, directory "
2757 ", autoscan, fetchtype, autotransfer, haspurge, purgecount FROM podcastchannels WHERE url = '%1';"
2758 ).arg( escapeString( url.url() ) ) );
2760 oldForeach( values )
2762 pcb->setUrl ( KUrl(*it) );
2763 pcb->setTitle ( *++it );
2764 pcb->setLink ( KUrl(*++it) );
2765 if( *++it != "NULL" )
2766 pcb->setImageURL( KUrl(*it) );
2767 pcb->setDescription ( *++it );
2768 pcb->setCopyright ( *++it );
2769 pcb->setParentId ( (*++it).toInt() );
2770 pcb->setSaveLocation( *++it );
2771 pcb->setAutoScan ( *++it == boolT() ? true : false );
2772 pcb->setFetchType ( (*++it).toInt() );
2773 pcb->setAutoTransfer( *++it == boolT() ? true : false );
2774 pcb->setPurge ( *++it == boolT() ? true : false );
2775 pcb->setPurgeCount ( (*++it).toInt() );
2778 return !values.isEmpty();
2781 // return newly created folder id
2783 CollectionDB::addPodcastFolder( const QString &name, const int parent_id, const bool isOpen )
2785 QString command = QString( "INSERT INTO podcastfolders ( name, parent, isOpen ) VALUES ('" );
2786 command += escapeString( name ) + "',";
2787 command += QString::number( parent_id ) + ',';
2788 command += isOpen ? boolT() + ");" : boolF() + ");";
2790 insert( command, NULL );
2792 command = QString( "SELECT id FROM podcastfolders WHERE name = '%1' AND parent = '%2';" )
2793 .arg( name, QString::number(parent_id) );
2794 QStringList values = query( command );
2796 return values[0].toInt();
2799 void
2800 CollectionDB::updatePodcastChannel( const PodcastChannelBundle &b )
2802 if( getDbConnectionType() == DbConnection::postgresql )
2804 query( QStringx( "UPDATE podcastchannels SET title='%1', weblink='%2', comment='%3', "
2805 "copyright='%4', parent=%5, directory='%6', autoscan=%7, fetchtype=%8, "
2806 "autotransfer=%9, haspurge=%10, purgecount=%11 WHERE url='%12';" )
2807 .args ( QStringList()
2808 << escapeString( b.title() )
2809 << escapeString( b.link().url() )
2810 << escapeString( b.description() )
2811 << escapeString( b.copyright() )
2812 << QString::number( b.parentId() )
2813 << escapeString( b.saveLocation() )
2814 << ( b.autoscan() ? boolT() : boolF() )
2815 << QString::number( b.fetchType() )
2816 << (b.hasPurge() ? boolT() : boolF() )
2817 << (b.autotransfer() ? boolT() : boolF() )
2818 << QString::number( b.purgeCount() )
2819 << escapeString( b.url().url() )
2823 else {
2824 addPodcastChannel( b, true ); //replace the already existing row
2828 void
2829 CollectionDB::updatePodcastEpisode( const int id, const PodcastEpisodeBundle &b )
2831 if( getDbConnectionType() == DbConnection::postgresql )
2833 query( QStringx( "UPDATE podcastepisodes SET url='%1', localurl='%2', parent='%3', title='%4', subtitle='%5', composer='%6', comment='%7', "
2834 "filetype='%8', createdate='%9', guid='%10', length=%11, size=%12, isNew=%13 WHERE id=%14;" )
2835 .args( QStringList()
2836 << escapeString( b.url().url() )
2837 << ( b.localUrl().isValid() ? escapeString( b.localUrl().url() ) : "NULL" )
2838 << escapeString( b.parent().url() )
2839 << escapeString( b.title() )
2840 << escapeString( b.subtitle() )
2841 << escapeString( b.author() )
2842 << escapeString( b.description() )
2843 << escapeString( b.type() )
2844 << escapeString( b.date() )
2845 << escapeString( b.guid() )
2846 << QString::number( b.duration() )
2847 << escapeString( QString::number( b.size() ) )
2848 << ( b.isNew() ? boolT() : boolF() )
2849 << QString::number( id )
2853 else {
2854 addPodcastEpisode( b, id );
2858 void
2859 CollectionDB::updatePodcastFolder( const int folder_id, const QString &name, const int parent_id, const bool isOpen )
2861 if( getDbConnectionType() == DbConnection::postgresql ) {
2862 query( QStringx( "UPDATE podcastfolders SET name='%1', parent=%2, isOpen=%3 WHERE id=%4;" )
2863 .args( QStringList()
2864 << escapeString(name)
2865 << QString::number(parent_id)
2866 << ( isOpen ? boolT() : boolF() )
2867 << QString::number(folder_id)
2871 else {
2872 query( QStringx( "REPLACE INTO podcastfolders ( id, name, parent, isOpen ) "
2873 "VALUES ( %1, '%2', %3, %4 );" )
2874 .args( QStringList()
2875 << QString::number(folder_id)
2876 << escapeString(name)
2877 << QString::number(parent_id)
2878 << ( isOpen ? boolT() : boolF() )
2884 void
2885 CollectionDB::removePodcastChannel( const KUrl &url )
2887 //remove channel
2888 query( QString( "DELETE FROM podcastchannels WHERE url = '%1';" )
2889 .arg( escapeString( url.url() ) ) );
2890 //remove all children
2891 query( QString( "DELETE FROM podcastepisodes WHERE parent = '%1';" )
2892 .arg( escapeString( url.url() ) ) );
2896 /// Try not to delete by url, since some podcast feeds have all the same url
2897 void
2898 CollectionDB::removePodcastEpisode( const int id )
2900 if( id < 0 ) return;
2901 query( QString( "DELETE FROM podcastepisodes WHERE id = '%1';" )
2902 .arg( QString::number(id) ) );
2905 void
2906 CollectionDB::removePodcastFolder( const int id )
2908 if( id < 0 ) return;
2909 query( QString("DELETE FROM podcastfolders WHERE id=%1;")
2910 .arg( QString::number(id) ) );
2913 bool
2914 CollectionDB::addSong( MetaBundle* bundle, const bool incremental )
2916 if ( !QFileInfo( bundle->url().path() ).isReadable() ) return false;
2918 QString command = "INSERT INTO tags_temp "
2919 "( url, dir, deviceid, createdate, modifydate, album, artist, composer, genre, year, title, "
2920 "comment, track, discnumber, bpm, sampler, length, bitrate, "
2921 "samplerate, filesize, filetype ) "
2922 "VALUES ('";
2924 QString artist = bundle->artist();
2925 QString title = bundle->title();
2926 if ( title.isEmpty() )
2928 title = bundle->url().fileName();
2929 if ( bundle->url().fileName().indexOf( '-' ) > 0 )
2931 if ( artist.isEmpty() )
2933 artist = bundle->url().fileName().section( '-', 0, 0 ).trimmed();
2934 bundle->setArtist( artist );
2936 title = bundle->url().fileName().section( '-', 1 ).trimmed();
2937 title = title.left( title.lastIndexOf( '.' ) ).trimmed();
2938 if ( title.isEmpty() ) title = bundle->url().fileName();
2940 bundle->setTitle( title );
2943 int deviceId = MountPointManager::instance()->getIdForUrl( bundle->url() );
2944 KUrl relativePath;
2945 MountPointManager::instance()->getRelativePath( deviceId, bundle->url(), relativePath );
2946 //debug() << "File has deviceId " << deviceId << ", relative path " << relativePath.path() << ", absolute path " << bundle->url().path();
2948 command += escapeString( relativePath.path() ) + "','";
2949 command += escapeString( relativePath.directory() ) + "',";
2950 command += QString::number( deviceId ) + ',';
2951 command += QString::number( QFileInfo( bundle->url().path() ).created().toTime_t() ) + ',';
2952 command += QString::number( QFileInfo( bundle->url().path() ).lastModified().toTime_t() ) + ',';
2954 command += escapeString( QString::number( albumID( bundle->album(), true, !incremental, true ) ) ) + ',';
2955 command += escapeString( QString::number( artistID( bundle->artist(), true, !incremental, true ) ) ) + ',';
2956 command += escapeString( QString::number( composerID( bundle->composer(), true, !incremental, true ) ) ) + ',';
2957 command += escapeString( QString::number( genreID( bundle->genre(), true, !incremental, true ) ) ) + ",'";
2958 command += escapeString( QString::number( yearID( QString::number( bundle->year() ), true, !incremental, true ) ) ) + "','";
2960 command += escapeString( bundle->title() ) + "','";
2961 command += escapeString( bundle->comment() ) + "', ";
2962 command += escapeString( QString::number( bundle->track() ) ) + " , ";
2963 command += escapeString( QString::number( bundle->discNumber() ) ) + " , ";
2964 command += escapeString( QString::number( bundle->bpm() ) ) + " , ";
2965 switch( bundle->compilation() ) {
2966 case MetaBundle::CompilationNo:
2967 command += boolF();
2968 break;
2970 case MetaBundle::CompilationYes:
2971 command += boolT();
2972 break;
2974 case MetaBundle::CompilationUnknown:
2975 default:
2976 command += "NULL";
2978 command += ',';
2980 // NOTE any of these may be -1 or -2, this is what we want
2981 // see MetaBundle::Undetermined
2982 command += QString::number( bundle->length() ) + ',';
2983 command += QString::number( bundle->bitrate() ) + ',';
2984 command += QString::number( bundle->sampleRate() ) + ',';
2985 command += QString::number( bundle->filesize() ) + ',';
2986 command += QString::number( bundle->fileType() ) + ')';
2988 //FIXME: currently there's no way to check if an INSERT query failed or not - always return true atm.
2989 // Now it might be possible as insert returns the rowid.
2990 insert( command, NULL );
2992 doAFTStuff( bundle, true );
2994 return true;
2997 void
2998 CollectionDB::doAFTStuff( MetaBundle* bundle, const bool tempTables )
3000 if( bundle->uniqueId().isEmpty() || bundle->url().path().isEmpty() )
3001 return;
3003 MountPointManager *mpm = MountPointManager::instance();
3004 //const to make sure one isn't later modified without the other being changed
3005 const int deviceIdInt = mpm->getIdForUrl( bundle->url().path() );
3006 const QString currdeviceid = QString::number( deviceIdInt );
3007 QString currid = escapeString( bundle->uniqueId() );
3008 QString currurl = escapeString( mpm->getRelativePath( deviceIdInt, bundle->url().path() ) );
3009 QString currdir = escapeString( mpm->getRelativePath( deviceIdInt, bundle->url().directory() ) );
3010 //debug() << "Checking currid = " << currid << ", currdir = " << currdir << ", currurl = " << currurl;
3011 //debug() << "tempTables = " << (tempTables?"true":"false");
3014 QStringList urls = query( QString(
3015 "SELECT url, uniqueid "
3016 "FROM uniqueid%1 "
3017 "WHERE deviceid = %2 AND url = '%3';" )
3018 .arg( tempTables ? "_temp" : ""
3019 , currdeviceid
3020 , currurl ) );
3022 QStringList uniqueids = query( QString(
3023 "SELECT url, uniqueid, deviceid "
3024 "FROM uniqueid%1 "
3025 "WHERE uniqueid = '%2';" )
3026 .arg( tempTables ? "_temp" : ""
3027 , currid ) );
3029 QStringList nonTempIDs = query( QString(
3030 "SELECT url, uniqueid, deviceid "
3031 "FROM uniqueid "
3032 "WHERE uniqueid = '%1';" )
3033 .arg( currid ) );
3035 QStringList nonTempURLs = query( QString(
3036 "SELECT url, uniqueid "
3037 "FROM uniqueid "
3038 "WHERE deviceid = %1 AND url = '%2';" )
3039 .arg( currdeviceid
3040 , currurl ) );
3042 bool tempTablesAndInPermanent = false;
3043 bool permanentFullMatch = false;
3045 //if we're not using temp tables here, i.e. tempTables is false,
3046 //then the results from both sets of queries above should be equal,
3047 //so behavior should be the same
3048 if( tempTables && ( nonTempURLs.count() > 0 || nonTempIDs.count() > 0 ) )
3049 tempTablesAndInPermanent = true;
3050 if( tempTablesAndInPermanent && nonTempURLs.count() > 0 && nonTempIDs.count() > 0 )
3051 permanentFullMatch = true;
3053 //debug() << "tempTablesAndInPermanent = " << (tempTablesAndInPermanent?"true":"false");
3054 //debug() << "permanentFullMatch = " << (permanentFullMatch?"true":"false");
3056 //debug() << "Entering checks";
3057 //first case: not in permanent table or temporary table
3058 if( !tempTablesAndInPermanent && urls.empty() && uniqueids.empty() )
3060 //debug() << "first case";
3061 QString insertline = QStringx( "INSERT INTO uniqueid%1 (deviceid, url, uniqueid, dir) "
3062 "VALUES ( %2,'%3', '%4', '%5');" )
3063 .args( QStringList()
3064 << ( tempTables ? "_temp" : "" )
3065 << currdeviceid
3066 << currurl
3067 << currid
3068 << currdir );
3069 insert( insertline, NULL );
3070 //debug() << "aftCheckPermanentTables #1";
3071 aftCheckPermanentTables( currdeviceid, currid, currurl );
3072 return;
3075 //next case: not in permanent table, but a match on one or the other in the temporary table
3076 //OR, we are using permanent tables (and not considering temp ones)
3077 if( !tempTablesAndInPermanent )
3079 if( urls.empty() ) //uniqueid already found in temporary table but not url; check the old URL
3081 //stat the original URL
3082 QString absPath = mpm->getAbsolutePath( uniqueids[2].toInt(), uniqueids[0] );
3083 //debug() << "At doAFTStuff, stat-ing file " << absPath;
3084 bool statSuccessful = false;
3085 bool pathsSame = absPath == bundle->url().path();
3086 if( !pathsSame )
3087 statSuccessful = QFile::exists( absPath );
3088 if( statSuccessful ) //if true, new one is a copy
3089 warning() << "Already-scanned file at " << absPath << " has same UID as new file at " << bundle->url().path();
3090 else //it's a move, not a copy, or a copy and then both files were moved...can't detect that
3092 //debug() << "stat was NOT successful, updating tables with: ";
3093 //debug() << QString( "UPDATE uniqueid%1 SET url='%2', dir='%3' WHERE uniqueid='%4';" ).arg( ( tempTables ? "_temp" : "" ), currurl, currdir, currid );
3094 query( QStringx( "UPDATE uniqueid%1 SET deviceid = %2, url='%3', dir='%4' WHERE uniqueid='%5';" )
3095 .args( QStringList()
3096 << ( tempTables ? "_temp" : "" )
3097 << currdeviceid
3098 << currurl
3099 << currdir
3100 << currid ) );
3101 if( !pathsSame )
3102 emit fileMoved( absPath, bundle->url().path(), bundle->uniqueId() );
3105 //okay then, url already found in temporary table but different uniqueid
3106 //a file exists in the same place as before, but new uniqueid...assume
3107 //that this is desired user behavior
3108 //NOTE: this should never happen during an incremental scan with temporary tables...!
3109 else if( uniqueids.empty() )
3111 //debug() << "file exists in same place as before, new uniqueid";
3112 query( QString( "UPDATE uniqueid%1 SET uniqueid='%2' WHERE deviceid = %3 AND url='%4';" )
3113 .arg( tempTables ? "_temp" : ""
3114 , currid
3115 , currdeviceid
3116 , currurl ) );
3117 emit uniqueIdChanged( bundle->url().path(), urls[1], bundle->uniqueId() );
3119 //else uniqueid and url match; nothing happened, so safely exit
3120 return;
3122 //okay...being here means, we are using temporary tables, AND it exists in the permanent table
3123 else
3125 //first case...full match exists in permanent table, should then be no match in temp table
3126 //(since code below deleted from permanent table after changes)
3127 //in this case, just insert into temp table
3128 if( permanentFullMatch )
3130 QString insertline = QString( "INSERT INTO uniqueid_temp (deviceid, url, uniqueid, dir) "
3131 "VALUES ( %1, '%2'" )
3132 .arg( currdeviceid
3133 , currurl );
3134 insertline += QString( ", '%1', '%2');" ).arg( currid ).arg( currdir );
3135 //debug() << "running command: " << insertline;
3136 insert( insertline, NULL );
3137 //debug() << "aftCheckPermanentTables #2";
3138 aftCheckPermanentTables( currdeviceid, currid, currurl );
3139 return;
3142 //second case...full match exists in permanent table, but path is different
3143 if( nonTempURLs.empty() )
3145 //stat the original URL
3146 QString absPath = mpm->getAbsolutePath( nonTempIDs[2].toInt(), nonTempIDs[0] );
3147 //debug() << "At doAFTStuff part 2, stat-ing file " << absPath;
3148 bool statSuccessful = false;
3149 bool pathsSame = absPath == bundle->url().path();
3150 if( !pathsSame )
3151 statSuccessful = QFile::exists( absPath );
3152 if( statSuccessful ) //if true, new one is a copy
3153 warning() << "Already-scanned file at " << absPath << " has same UID as new file at " << currurl;
3154 else //it's a move, not a copy, or a copy and then both files were moved...can't detect that
3156 //debug() << "stat part 2 was NOT successful, updating tables with: ";
3157 query( QString( "DELETE FROM uniqueid WHERE uniqueid='%1';" )
3158 .arg( currid ) );
3159 query( QString( "INSERT INTO uniqueid_temp (deviceid, url, uniqueid, dir) "
3160 "VALUES ( %1, '%2', '%3', '%4')" )
3161 .arg( currdeviceid
3162 , currurl
3163 , currid
3164 , currdir ) );
3165 if( !pathsSame )
3166 emit fileMoved( absPath, bundle->url().path(), bundle->uniqueId() );
3169 else if( nonTempIDs.empty() )
3171 //debug() << "file exists in same place as before, part 2, new uniqueid";
3172 query( QString( "DELETE FROM uniqueid WHERE deviceid = %1 AND url='%2';" )
3173 .arg( currdeviceid )
3174 .arg( currurl ) );
3175 query( QString( "INSERT INTO uniqueid_temp (deviceid, url, uniqueid, dir) VALUES ( %1, '%2', '%3', '%4')" )
3176 .arg( currdeviceid
3177 , currurl
3178 , currid
3179 , currdir ) );
3180 emit uniqueIdChanged( bundle->url().path(), nonTempURLs[1], bundle->uniqueId() );
3182 //else do nothing...really this case should never happen
3183 return;
3187 void
3188 CollectionDB::emitFileDeleted( const QString &absPath, const QString &uniqueid )
3190 if( uniqueid.isEmpty() )
3191 emit fileDeleted( absPath );
3192 else
3193 emit fileDeleted( absPath, uniqueid );
3196 void
3197 CollectionDB::emitFileAdded( const QString &absPath, const QString &uniqueid )
3199 if( uniqueid.isEmpty() )
3200 emit fileAdded( absPath );
3201 else
3202 emit fileAdded( absPath, uniqueid );
3205 QString
3206 CollectionDB::urlFromUniqueId( const QString &id )
3208 //bool scanning = ( ScanController::instance() && ScanController::instance()->tablesCreated() );
3209 bool scanning = false;
3210 QStringList urls = query( QString(
3211 "SELECT deviceid, url "
3212 "FROM uniqueid%1 "
3213 "WHERE uniqueid = '%2';" )
3214 .arg( scanning ? "_temp" : QString() )
3215 .arg( id ), true );
3217 if( urls.empty() && scanning )
3218 urls = query( QString(
3219 "SELECT deviceid, url "
3220 "FROM uniqueid "
3221 "WHERE uniqueid = '%1';" )
3222 .arg( id ) );
3224 if( urls.empty() )
3225 return QString();
3227 return MountPointManager::instance()->getAbsolutePath( urls[0].toInt(), urls[1] );
3230 QString
3231 CollectionDB::uniqueIdFromUrl( const KUrl &url )
3233 MountPointManager *mpm = MountPointManager::instance();
3234 int currdeviceid = mpm->getIdForUrl( url.path() );
3235 QString currurl = escapeString( mpm->getRelativePath( currdeviceid, url.path() ) );
3237 //bool scanning = ( ScanController::instance() && ScanController::instance()->tablesCreated() );
3238 bool scanning = false;
3239 QStringList uid = query( QString(
3240 "SELECT uniqueid "
3241 "FROM uniqueid%1 "
3242 "WHERE deviceid = %2 AND url = '%3';" )
3243 .arg( scanning ? "_temp" : QString() )
3244 .arg( currdeviceid )
3245 .arg( currurl ), true );
3247 if( uid.empty() && scanning )
3248 uid = query( QString(
3249 "SELECT uniqueid "
3250 "FROM uniqueid "
3251 "WHERE deviceid = %1 AND url = '%2';" )
3252 .arg( currdeviceid )
3253 .arg( currurl ) );
3255 if( uid.empty() )
3256 return QString();
3258 return uid[0];
3261 QString
3262 CollectionDB::getURL( const MetaBundle &bundle )
3264 uint artID = artistID( bundle.artist(), false );
3265 if( !artID )
3266 return QString();
3268 uint albID = albumID( bundle.album(), false );
3269 if( !albID )
3270 return QString();
3272 QString q = QString( "SELECT tags.deviceid, tags.url "
3273 "FROM tags "
3274 "WHERE tags.album = '%1' AND tags.artist = '%2' AND tags.track = '%3' AND tags.title = '%4'" +
3275 deviceidSelection() + ';' )
3276 .arg( albID )
3277 .arg( artID )
3278 .arg( bundle.track() )
3279 .arg( escapeString( bundle.title() ) );
3281 QStringList urls = URLsFromQuery( query( q ) );
3283 if( urls.empty() )
3284 return QString();
3286 if( urls.size() == 1 )
3288 return urls.first();
3291 QString url = urls.first();
3292 int maxPlayed = -1;
3293 for( QStringList::iterator it = urls.begin();
3294 it != urls.end();
3295 it++ )
3297 int pc = getPlayCount( *it );
3298 if( pc > maxPlayed )
3300 maxPlayed = pc;
3301 url = *it;
3305 return url;
3308 // Helper function to convert the "tags.sampler" column to a MetaBundle::Collection value
3310 // We use the first char of boolT / boolF as not all DBs store true/false as
3311 // numerics (and it's only a single-char column)
3312 static int
3313 samplerToCompilation( const QString &it )
3315 if( it == CollectionDB::instance()->boolT().mid( 0, 1 ) )
3317 return MetaBundle::CompilationYes;
3319 else if( it == CollectionDB::instance()->boolF().mid( 0, 1 ) )
3321 return MetaBundle::CompilationNo;
3323 return MetaBundle::CompilationUnknown;
3326 MetaBundle
3327 CollectionDB::bundleFromQuery( QStringList::const_iterator *iter )
3329 QStringList::const_iterator &it = *iter;
3330 MetaBundle b;
3331 //QueryBuilder automatically inserts the deviceid as return value if asked for the path
3332 QString rpath = *it;
3333 int deviceid = (*++it).toInt();
3334 b.setPath ( MountPointManager::instance()->getAbsolutePath( deviceid, rpath ) );
3335 b.setAlbum ( *++it );
3336 b.setArtist ( *++it );
3337 b.setComposer ( *++it );
3338 b.setGenre ( *++it );
3339 b.setTitle ( *++it );
3340 b.setYear ( (*++it).toInt() );
3341 b.setComment ( *++it );
3342 b.setTrack ( (*++it).toInt() );
3343 b.setBitrate ( (*++it).toInt() );
3344 b.setDiscNumber( (*++it).toInt() );
3345 b.setLength ( (*++it).toInt() );
3346 b.setSampleRate( (*++it).toInt() );
3347 b.setFilesize ( (*++it).toInt() );
3349 b.setCompilation( samplerToCompilation( *it ) );
3350 ++it;
3351 b.setFileType( (*++it).toInt() );
3352 b.setBpm ( (*++it).toFloat() );
3354 b.setScore ( (*++it).toFloat() );
3355 b.setRating ( (*++it).toInt() );
3356 b.setPlayCount ( (*++it).toInt() );
3357 b.setLastPlay ( (*++it).toInt() );
3359 if( false && b.length() <= 0 ) {
3360 // we try to read the tags, despite the slow-down
3361 debug() << "Audioproperties not known for: " << b.url().fileName();
3362 b.readTags( TagLib::AudioProperties::Fast);
3365 return b;
3368 static void
3369 fillInBundle( QStringList values, MetaBundle &bundle )
3371 //TODO use this whenever possible
3373 // crash prevention
3374 while( values.count() < 16 )
3375 values += "IF YOU CAN SEE THIS THERE IS A BUG!";
3377 QStringList::ConstIterator it = values.begin();
3379 bundle.setAlbum ( *it ); ++it;
3380 bundle.setArtist ( *it ); ++it;
3381 bundle.setComposer ( *it ); ++it;
3382 bundle.setGenre ( *it ); ++it;
3383 bundle.setTitle ( *it ); ++it;
3384 bundle.setYear ( (*it).toInt() ); ++it;
3385 bundle.setComment ( *it ); ++it;
3386 bundle.setDiscNumber( (*it).toInt() ); ++it;
3387 bundle.setTrack ( (*it).toInt() ); ++it;
3388 bundle.setBitrate ( (*it).toInt() ); ++it;
3389 bundle.setLength ( (*it).toInt() ); ++it;
3390 bundle.setSampleRate( (*it).toInt() ); ++it;
3391 bundle.setFilesize ( (*it).toInt() ); ++it;
3392 bundle.setFileType ( (*it).toInt() ); ++it;
3393 bundle.setBpm ( (*it).toFloat() ); ++it;
3395 bundle.setCompilation( samplerToCompilation( *it ) );
3396 ++it;
3398 bundle.setUniqueId(*it);
3401 bool
3402 CollectionDB::bundleForUrl( MetaBundle* bundle )
3404 int deviceid = MountPointManager::instance()->getIdForUrl( bundle->url() );
3405 KUrl rpath;
3406 MountPointManager::instance()->getRelativePath( deviceid, bundle->url(), rpath );
3407 QStringList values = query( QString(
3408 "SELECT album.name, artist.name, composer.name, genre.name, tags.title, "
3409 "year.name, tags.comment, tags.discnumber, "
3410 "tags.track, tags.bitrate, tags.length, tags.samplerate, "
3411 "tags.filesize, tags.filetype, tags.bpm, tags.sampler, uniqueid.uniqueid "
3412 "FROM tags LEFT OUTER JOIN uniqueid ON tags.url = uniqueid.url AND tags.deviceid = uniqueid.deviceid,"
3413 "album, artist, composer, genre, year "
3414 "WHERE album.id = tags.album AND artist.id = tags.artist AND composer.id = tags.composer AND "
3415 "genre.id = tags.genre AND year.id = tags.year AND tags.url = '%2' AND tags.deviceid = %1;" )
3416 .arg( deviceid )
3417 .arg( escapeString( rpath.path( ) ) ) );
3419 bool valid = false;
3421 if ( !values.empty() )
3423 fillInBundle( values, *bundle );
3424 valid = true;
3426 // else if( MediaBrowser::instance() && MediaBrowser::instance()->getBundle( bundle->url(), bundle ) )
3427 // {
3428 // valid = true;
3429 // }
3430 else
3432 // check if it's a podcast
3433 PodcastEpisodeBundle peb;
3434 if( getPodcastEpisodeBundle( bundle->url(), &peb ) )
3436 if( bundle->url().protocol() == "file" && QFile::exists( bundle->url().path() ) )
3438 MetaBundle mb( bundle->url(), true /* avoid infinite recursion */ );
3439 *bundle = mb;
3441 bundle->copyFrom( peb );
3442 valid = true;
3446 return valid;
3450 Q3ValueList<MetaBundle>
3451 CollectionDB::bundlesByUrls( const KUrl::List& urls )
3453 BundleList bundles;
3454 QStringList paths;
3455 QueryBuilder qb;
3456 int count = 0;
3458 for( KUrl::List::ConstIterator it = urls.begin(), end = urls.end(); it != end; ++it, ++count )
3460 // non file stuff won't exist in the db, but we still need to
3461 // re-insert it into the list we return, just with no tags assigned
3462 paths += (*it).protocol() == "file" ? (*it).path() : (*it).url();
3464 if( paths.count() == 50 || count == urls.size() - 1 )
3466 qb.clear();
3468 qb.addReturnValue( QueryBuilder::tabAlbum, QueryBuilder::valName );
3469 qb.addReturnValue( QueryBuilder::tabArtist, QueryBuilder::valName );
3470 qb.addReturnValue( QueryBuilder::tabComposer, QueryBuilder::valName );
3471 qb.addReturnValue( QueryBuilder::tabGenre, QueryBuilder::valName );
3472 qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valTitle );
3473 qb.addReturnValue( QueryBuilder::tabYear, QueryBuilder::valName );
3474 qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valComment );
3475 qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valTrack );
3476 qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valBitrate );
3477 qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valDiscNumber );
3478 qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valLength );
3479 qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valSamplerate );
3480 qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valFilesize );
3481 qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valFileType );
3482 qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valBPM );
3483 qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valURL );
3484 qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valIsCompilation );
3486 qb.addUrlFilters( paths );
3487 qb.setOptions( QueryBuilder::optRemoveDuplicates );
3489 const QStringList values = qb.run();
3491 BundleList buns50;
3492 MetaBundle b;
3493 oldForeach( values )
3495 b.setAlbum ( *it );
3496 b.setArtist ( *++it );
3497 b.setComposer ( *++it );
3498 b.setGenre ( *++it );
3499 b.setTitle ( *++it );
3500 b.setYear ( (*++it).toInt() );
3501 b.setComment ( *++it );
3502 b.setTrack ( (*++it).toInt() );
3503 b.setBitrate ( (*++it).toInt() );
3504 b.setDiscNumber( (*++it).toInt() );
3505 b.setLength ( (*++it).toInt() );
3506 b.setSampleRate( (*++it).toInt() );
3507 b.setFilesize ( (*++it).toInt() );
3508 b.setFileType ( (*++it).toInt() );
3509 b.setBpm ( (*++it).toFloat() );
3510 b.setPath ( *++it );
3512 b.setCompilation( samplerToCompilation( *it ) );
3513 ++it;
3515 b.checkExists();
3517 buns50.append( b );
3520 // we get no guarantee about the order that the database
3521 // will return our values, and sqlite indeed doesn't return
3522 // them in the desired order :( (MySQL does though)
3523 oldForeach( paths )
3525 for( BundleList::Iterator jt = buns50.begin(), end = buns50.end(); jt != end; ++jt )
3527 if ( ( *jt ).url().path() == ( *it ))
3529 bundles += *jt;
3530 buns50.remove( jt );
3531 goto success;
3535 // if we get here, we didn't find an entry
3537 KUrl url = KUrl( *it );
3539 if( true /* !MediaBrowser::instance()->getBundle( url, &b ) */ )
3541 if( url.isLocalFile() )
3543 b = MetaBundle( url );
3545 else
3547 b = MetaBundle();
3548 b.setUrl( url );
3549 // FIXME: more context for i18n after string freeze
3550 b.setTitle( QString( "%1 %2 %3%4" )
3551 .arg( url.fileName(),
3552 i18n( "from" ),
3553 url.hasHost() ? url.host() : QString(),
3554 url.directory() ) );
3557 // check if it's a podcast
3558 PodcastEpisodeBundle peb;
3559 if( getPodcastEpisodeBundle( url, &peb ) )
3561 b.copyFrom( peb );
3563 else if( b.url().protocol() == "audiocd" || b.url().protocol() == "cdda" )
3565 // try to see if the engine has some info about the
3566 // item (the intended behaviour should be that if the
3567 // item is an AudioCD track, the engine can return
3568 // CDDB data for it)
3569 Engine::SimpleMetaBundle smb;
3570 if ( EngineController::engine()->metaDataForUrl( b.url(), smb ) )
3572 b.setTitle( smb.title );
3573 b.setArtist( smb.artist );
3574 b.setAlbum( smb.album );
3575 b.setComment( smb.comment );
3576 b.setGenre( smb.genre );
3577 b.setBitrate( smb.bitrate.toInt() );
3578 b.setSampleRate( smb.samplerate.toInt() );
3579 b.setLength( smb.length.toInt() );
3580 b.setYear( smb.year.toInt() );
3581 b.setTrack( smb.tracknr.toInt() );
3586 bundles += b;
3588 success: ;
3591 paths.clear();
3595 return bundles;
3599 void
3600 CollectionDB::addAudioproperties( const MetaBundle& bundle )
3602 int deviceid = MountPointManager::instance()->getIdForUrl( bundle.url() );
3603 KUrl rpath;
3604 MountPointManager::instance()->getRelativePath( deviceid, bundle.url(), rpath );
3605 query( QString( "UPDATE tags SET bitrate='%1', length='%2', samplerate='%3' WHERE url='%5' AND deviceid = %4;" )
3606 .arg( bundle.bitrate() )
3607 .arg( bundle.length() )
3608 .arg( bundle.sampleRate() )
3609 .arg( deviceid )
3610 .arg( escapeString( rpath.path() ) ) );
3614 void
3615 CollectionDB::addSongPercentage( const QString &url, float percentage,
3616 const QString &reason, const QDateTime *playtime )
3618 //the URL must always be inserted last! an escaped URL can contain Strings like %1->bug
3619 int deviceid = MountPointManager::instance()->getIdForUrl( url );
3620 QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
3621 //statistics table might not have those values, but we need them later, so keep them
3622 int statDevId = deviceid;
3623 QString statRPath = rpath;
3624 QStringList values =
3625 query( QString(
3626 "SELECT playcounter, createdate, percentage, rating FROM statistics "
3627 "WHERE url = '%2' AND deviceid = %1;" )
3628 .arg( statDevId ).arg( escapeString( statRPath ) ) );
3630 //handle corner case: deviceid!=-1 but there is a statistics row for that song with deviceid -1
3631 if ( values.isEmpty() )
3633 QString rpath2 = '.' + url;
3634 values = query( QString(
3635 "SELECT playcounter, createdate, percentage, rating FROM statistics "
3636 "WHERE url = '%1' AND deviceid = -1;" )
3637 .arg( escapeString( rpath2 ) ) );
3638 if ( !values.isEmpty() )
3640 statRPath = rpath2;
3641 statDevId = -1;
3645 uint atime = playtime ? playtime->toTime_t() : QDateTime::currentDateTime().toTime_t();
3647 // check boundaries
3648 if ( percentage > 100.f ) percentage = 100.f;
3649 if ( percentage < 1.f ) percentage = 1.f;
3651 if ( !values.isEmpty() )
3654 // increment playcounter and update accesstime
3655 query( QString( "UPDATE statistics SET playcounter=%1, accessdate=%2 WHERE url='%4' AND deviceid= %3;" )
3656 .arg( values[0] + " + 1" )
3657 .arg( atime )
3658 .arg( statDevId )
3659 .arg( escapeString( statRPath ) ) );
3661 else
3663 insert( QString( "INSERT INTO statistics ( url, deviceid, createdate, accessdate, percentage, playcounter, rating, uniqueid, deleted ) "
3664 "VALUES ( '%6', %5, %1, %2, 0, 1, 0, %3, %4 );" )
3665 .arg( atime )
3666 .arg( atime )
3667 .arg( ( getUniqueId( url ).isNull() ? "NULL" : '\'' + escapeString( getUniqueId( url ) ) + '\'' ) )
3668 .arg( boolF() )
3669 .arg( statDevId )
3670 .arg( escapeString( statRPath ) ), 0 );
3673 double prevscore = 50;
3674 int playcount = 0;
3675 if( !values.isEmpty() )
3677 playcount = values[ 0 ].toInt();
3678 // This stops setting the Rating (which creates a row) from affecting the
3679 // prevscore of an unplayed track. See bug 127475
3680 if ( playcount )
3681 prevscore = values[ 2 ].toDouble();
3683 const QStringList v = query( QString( "SELECT length FROM tags WHERE url = '%2' AND deviceid = %1;" )
3684 .arg( deviceid ).arg( escapeString( rpath ) ) );
3685 const int length = v.isEmpty() ? 0 : v.first().toInt();
3687 ScriptManager::instance()->requestNewScore( url, prevscore, playcount, length, percentage, reason );
3691 float
3692 CollectionDB::getSongPercentage( const QString &url )
3694 QueryBuilder qb;
3695 qb.addReturnValue( QueryBuilder::tabStats, QueryBuilder::valScore );
3696 qb.addMatch( QueryBuilder::tabStats, QueryBuilder::valURL, url );
3698 QStringList values = qb.run();
3700 if( !values.isEmpty() )
3701 return values.first().toFloat();
3703 return 0;
3707 CollectionDB::getSongRating( const QString &url )
3709 QueryBuilder qb;
3710 qb.addReturnValue( QueryBuilder::tabStats, QueryBuilder::valRating );
3711 qb.addMatch( QueryBuilder::tabStats, QueryBuilder::valURL, url );
3713 QStringList values = qb.run();
3715 if( values.count() )
3716 return qBound( 0, values.first().toInt(), 10 );
3718 return 0;
3721 void
3722 CollectionDB::setSongPercentage( const QString &url , float percentage)
3724 int deviceid = MountPointManager::instance()->getIdForUrl( url );
3725 QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
3726 QStringList values =
3727 query( QString(
3728 "SELECT playcounter, createdate, accessdate, rating FROM statistics WHERE url = '%2' AND deviceid = %1;" )
3729 .arg( deviceid ).arg( escapeString( rpath ) ) );
3731 //handle corner case: deviceid!=-1 but there is a statistics row for that song with deviceid -1
3732 if ( values.isEmpty() )
3734 QString rpath2 = '.' + url;
3735 values = query( QString(
3736 "SELECT playcounter, createdate, accessdate, rating FROM statistics "
3737 "WHERE url = '%1' AND deviceid = -1;" )
3738 .arg( escapeString( rpath2 ) ) );
3739 if ( !values.isEmpty() )
3741 rpath = rpath2;
3742 deviceid = -1;
3746 // check boundaries
3747 if ( percentage > 100.f ) percentage = 100.f;
3748 if ( percentage < 0.f ) percentage = 0.f;
3750 if ( !values.isEmpty() )
3752 query( QString( "UPDATE statistics SET percentage=%1 WHERE url='%3' AND deviceid = %2;" )
3753 .arg( percentage )
3754 .arg( deviceid ).arg( escapeString( rpath ) ) );
3756 else
3758 insert( QString( "INSERT INTO statistics ( url, deviceid, createdate, accessdate, percentage, playcounter, rating, uniqueid, deleted ) "
3759 "VALUES ( '%7', %6, %2, %3, %1, 0, 0, %3, %4 );" )
3760 .arg( percentage )
3761 .arg( QDateTime::currentDateTime().toTime_t() )
3762 .arg( 0 )
3763 .arg( ( getUniqueId( url ).isNull() ? "NULL" : '\'' + escapeString( getUniqueId( url ) ) + '\'' ) )
3764 .arg( boolF() )
3765 .arg( deviceid )
3766 .arg( escapeString( rpath ) ),0 );
3769 emit scoreChanged( url, percentage );
3772 void
3773 CollectionDB::setSongRating( const QString &url, int rating, bool toggleHalf )
3775 int deviceid = MountPointManager::instance()->getIdForUrl( url );
3776 QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
3777 QStringList values =
3778 query( QString(
3779 "SELECT playcounter, createdate, accessdate, percentage, rating FROM statistics WHERE url = '%2' AND deviceid = %1;" )
3780 .arg( deviceid )
3781 .arg( escapeString( rpath ) ) );
3783 //handle corner case: deviceid!=-1 but there is a statistics row for that song with deviceid -1
3784 if ( values.isEmpty() )
3786 QString rpath2 = '.' + url;
3787 values = query( QString(
3788 "SELECT playcounter, createdate, accessdate, percentage, rating FROM statistics "
3789 "WHERE url = '%1' AND deviceid = -1;" )
3790 .arg( escapeString( rpath2 ) ) );
3791 if ( !values.isEmpty() )
3793 rpath = rpath2;
3794 deviceid = -1;
3798 bool ok = true;
3799 if ( !values.isEmpty() )
3801 int prev = values[4].toInt( &ok );
3802 if( ok && toggleHalf && ( prev == rating || ( prev == 1 && rating == 2 ) ) )
3804 if( prev == 1 && rating == 2 )
3805 rating = 0;
3806 else if( rating % 2 ) //.5
3807 rating++;
3808 else
3809 rating--;
3813 // check boundaries
3814 if ( rating > 10 ) rating = 10;
3815 if ( rating < 0 ) rating = 0;
3817 if ( !values.isEmpty() )
3819 query( QString( "UPDATE statistics SET rating=%1 WHERE url='%3' AND deviceid = %2;" )
3820 .arg( rating )
3821 .arg( deviceid )
3822 .arg( escapeString( rpath ) ) );
3824 else
3826 insert( QString( "INSERT INTO statistics ( url, deviceid, createdate, accessdate, percentage, rating, playcounter, uniqueid, deleted ) "
3827 "VALUES ( '%7', %6, %2, %3, 0, %1, 0, %4, %5 );" )
3828 .arg( rating )
3829 .arg( QDateTime::currentDateTime().toTime_t() )
3830 .arg( 0 )
3831 .arg( ( getUniqueId( url ).isNull() ? "NULL" : '\'' + escapeString( getUniqueId( url ) ) + '\'' ) )
3832 .arg( boolF() )
3833 .arg( deviceid )
3834 .arg( escapeString( rpath ) ), NULL );
3837 emit ratingChanged( url, rating );
3841 CollectionDB::getPlayCount( const QString &url )
3843 //queryBuilder is good
3844 QueryBuilder qb;
3845 qb.addReturnValue( QueryBuilder::tabStats, QueryBuilder::valPlayCounter );
3846 qb.addMatch( QueryBuilder::tabStats, QueryBuilder::valURL, url );
3847 QStringList values = qb.run();
3848 if( values.count() )
3849 return values.first().toInt();
3850 return 0;
3853 QDateTime
3854 CollectionDB::getFirstPlay( const QString &url )
3856 QueryBuilder qb;
3857 qb.addReturnValue( QueryBuilder::tabStats, QueryBuilder::valCreateDate );
3858 qb.addMatch( QueryBuilder::tabStats, QueryBuilder::valURL, url );
3859 QStringList values = qb.run();
3860 QDateTime dt;
3861 if( values.count() )
3862 dt.setTime_t( values.first().toUInt() );
3863 return dt;
3866 QDateTime
3867 CollectionDB::getLastPlay( const QString &url )
3869 QueryBuilder qb;
3870 qb.addReturnValue( QueryBuilder::tabStats, QueryBuilder::valAccessDate );
3871 qb.addMatch( QueryBuilder::tabStats, QueryBuilder::valURL, url );
3872 QStringList values = qb.run();
3873 QDateTime dt;
3874 if( values.count() )
3875 dt.setTime_t( values.first().toUInt() );
3876 else
3877 dt.setTime_t( 0 );
3878 return dt;
3881 * @short: exchange url references in the database for a particular file
3882 * @note: deletes all items for newURL, changes oldURL->newURL, deletes oldURL.
3883 * FIXME: should we check if lyrics etc exist in the newURL and keep them if necessary?
3885 void
3886 CollectionDB::migrateFile( const QString &oldURL, const QString &newURL )
3888 int oldMediaid = MountPointManager::instance()->getIdForUrl( oldURL );
3889 QString oldRpath = MountPointManager::instance()->getRelativePath( oldMediaid, oldURL );
3891 int newMediaid = MountPointManager::instance()->getIdForUrl( newURL );
3892 QString newRpath = MountPointManager::instance()->getRelativePath( newMediaid, newURL );
3894 // Ensure destination is clear.
3895 query( QString( "DELETE FROM tags WHERE url = '%2' AND deviceid = %1;" )
3896 .arg( newMediaid ).arg( escapeString( newRpath ) ) );
3898 query( QString( "DELETE FROM statistics WHERE url = '%2' AND deviceid = %1;" )
3899 .arg( newMediaid ).arg( escapeString( newRpath ) ) );
3901 query( QString( "DELETE FROM tags_labels WHERE url = '%2' and deviceid = %1;" )
3902 .arg( newMediaid).arg( escapeString( newRpath ) ) );
3904 if ( !getLyrics( oldURL ).isEmpty() )
3905 query( QString( "DELETE FROM lyrics WHERE url = '%2' AND deviceid = %1;" )
3906 .arg( newMediaid ).arg( escapeString( newRpath ) ) );
3907 // Migrate
3908 //code looks ugly but prevents problems when the URL contains HTTP escaped characters
3909 query( QString( "UPDATE tags SET url = '%3', deviceid = %1" )
3910 .arg( newMediaid ).arg( escapeString( newRpath ) )
3911 + QString( " WHERE deviceid=%1 AND url = '%2';" )
3912 .arg( oldMediaid ).arg( escapeString( oldRpath ) ) );
3914 query( QString( "UPDATE statistics SET url = '%2', deviceid = %1" )
3915 .arg( newMediaid ).arg( escapeString( newRpath ) )
3916 + QString( " WHERE deviceid=%1 AND url = '%2';" )
3917 .arg( oldMediaid ).arg( escapeString( oldRpath ) ) );
3919 query( QString( "UPDATE lyrics SET url = '%2', deviceid = %1" )
3920 .arg( newMediaid ).arg( escapeString( newRpath ) )
3921 + QString( " WHERE deviceid=%1 AND url = '%2';" )
3922 .arg( oldMediaid ).arg( escapeString( oldRpath ) ) );
3924 query( QString( "UPDATE tags_labels SET url = '%2', deviceid = %1 WHERE deviceid = %3 AND url = '%4';" )
3925 .arg( QString::number( newMediaid ), escapeString( newRpath ), QString::number( oldMediaid ), escapeString( oldRpath ) ) );
3927 query( QString( "UPDATE uniqueid SET url = '%1', deviceid = %2 WHERE url = '%3' AND deviceid = %4;" )
3928 .arg( escapeString( newRpath ), QString::number( newMediaid ),
3929 escapeString( oldRpath ), QString::number( oldMediaid ) ) );
3931 query( QString( "UPDATE playlists SET url = '%1' WHERE url = '%2';" )
3932 .arg( escapeString( newURL ),
3933 escapeString( oldURL ) ) );
3936 void
3937 CollectionDB::fileOperationResult( KIO::Job *job ) // slot
3939 if(job->error())
3941 m_fileOperationFailed = true;
3942 debug() << "file operation failed: " << job->errorText();
3944 else
3946 m_fileOperationFailed = false;
3949 m_waitForFileOperation = false;
3952 void CollectionDB::cancelMovingFileJob()
3954 m_moveFileJobCancelled = true;
3957 bool
3958 CollectionDB::organizeFile( const KUrl &src, /*const OrganizeCollectionDialog &dialog,*/ bool copy )
3960 Q_UNUSED( src ); Q_UNUSED( copy );
3961 return false;
3963 if( !MetaBundle::isKioUrl( src ) )
3964 return false;
3966 bool overwrite = dialog.overwriteCheck->isChecked();
3967 bool localFile = src.isLocalFile();
3968 KUrl tmpSrc = src;
3969 if( !localFile )
3971 QString tmp;
3972 QString extension = src.url().section( '.', -1 );
3973 extension = extension.section("?", 0, 0); // remove trailling stuff lead by ?, if any
3975 int count = 0;
3978 tmp = QString( dialog.folderCombo->currentText() + "/amarok-tmp-%1." + extension ).arg( count );
3979 count++;
3980 } while( QFile::exists( tmp ) );
3981 tmpSrc = KUrl( tmp );
3983 KIO::FileCopyJob *job = 0;
3984 if( copy )
3986 job = KIO::file_copy( src, tmpSrc, -1, KIO::HideProgressInfo );
3988 else
3990 job = KIO::file_move( src, tmpSrc, -1, KIO::HideProgressInfo );
3992 connect( job, SIGNAL(result( KIO::Job * )), SLOT(fileOperationResult( KIO::Job * )) );
3993 m_waitForFileOperation = true;
3994 while( m_waitForFileOperation )
3996 if( m_moveFileJobCancelled )
3998 disconnect( job, SIGNAL(result( KIO::Job * )), this, SLOT(fileOperationResult( KIO::Job * )) );
4000 QString partFile = QString( "%1.part" ).arg( (job->destUrl()).path() );
4001 job->kill();
4002 QFile file( partFile );
4003 if( file.exists() ) file.remove();
4005 m_waitForFileOperation = false;
4006 m_fileOperationFailed = true;
4007 continue;
4010 usleep( 10000 );
4011 kapp->processEvents( QEventLoop::AllEvents );
4014 if( m_fileOperationFailed )
4016 debug() << "failed to transfer " << src.url() << " to " << tmpSrc;
4018 m_moveFileJobCancelled = false;
4019 return false;
4023 //Building destination here.
4024 MetaBundle mb( tmpSrc );
4025 QString dest = dialog.buildDestination( dialog.buildFormatString(), mb );
4027 debug() << "Destination: " << dest;
4029 if( !m_moveFileJobCancelled && tmpSrc.path() != dest ) //suppress error warning that file couldn't be moved
4031 if( !CollectionDB::instance()->moveFile( tmpSrc.url(), dest, overwrite, copy && localFile ) )
4033 if( !localFile )
4034 QFile::remove( tmpSrc.path() );
4036 m_moveFileJobCancelled = false;
4037 return false;
4041 //Use cover image for folder icon
4042 if( !m_moveFileJobCancelled && dialog.coverCheck->isChecked() && !mb.artist().isEmpty() && !mb.album().isEmpty() )
4044 KUrl dstURL = KUrl( dest );
4045 dstURL.cleanPath();
4047 QString path = dstURL.directory();
4048 QString cover = CollectionDB::instance()->albumImage( mb.artist(), mb.album(), false, 1 );
4050 if( !QFile::exists(path + "/.directory") && !cover.endsWith( "nocover.png" ) )
4052 //QPixmap thumb; //Not amazon nice.
4053 //if ( thumb.load( cover ) ){
4054 //thumb.save(path + "/.front.png", "PNG", -1 ); //hide files
4056 KConfig config(path + "/.directory");
4057 config.setGroup("Desktop Entry");
4059 if( !config.hasKey("Icon") )
4061 config.writeEntry( "Icon", cover );
4062 config.sync();
4064 //} //Not amazon nice.
4068 if( localFile && isDirInCollection( src.directory() ) && QDir().rmdir( src.directory() ) )
4070 debug() << "removed: " << src.directory();
4073 m_moveFileJobCancelled = false;
4075 return true;
4079 bool
4080 CollectionDB::moveFile( const QString &src, const QString &dest, bool overwrite, bool copy )
4082 DEBUG_BLOCK
4083 if(src == dest){
4084 debug() << "Source and destination URLs are the same, aborting.";
4085 return false;
4088 // Escape URL.
4089 KUrl srcURL = KUrl( src );
4090 KUrl dstURL = KUrl( dest );
4092 // Clean it.
4093 srcURL.cleanPath();
4094 dstURL.cleanPath();
4096 // Make sure it is valid.
4097 if(!srcURL.isValid() || !dstURL.isValid())
4098 debug() << "Invalid URL ";
4100 // Get just the directory.
4101 KUrl dir = dstURL;
4102 dir.setFileName(QString());
4104 // Create the directory.
4105 if(!KStandardDirs::exists(dir.path()))
4106 if(!KStandardDirs::makeDir(dir.path())) {
4107 debug() << "Unable to create directory " << dir.path();
4110 m_fileOperationFailed = false;
4111 KIO::JobFlags jobFlags = KIO::HideProgressInfo;
4112 if (overwrite)
4113 jobFlags |= KIO::Overwrite;
4114 KIO::FileCopyJob *job = 0;
4115 if( copy )
4117 job = KIO::file_copy( srcURL, dstURL, -1, jobFlags );
4119 else
4121 job = KIO::file_move( srcURL, dstURL, -1, jobFlags );
4123 connect( job, SIGNAL(result( KIO::Job * )), SLOT(fileOperationResult( KIO::Job * )) );
4124 m_waitForFileOperation = true;
4125 while( m_waitForFileOperation )
4127 if( m_moveFileJobCancelled )
4129 disconnect( job, SIGNAL(result( KIO::Job * )), this, SLOT(fileOperationResult( KIO::Job * )) );
4131 QString partFile = QString( "%1.part" ).arg( (job->destUrl()).path() );
4132 job->kill();
4133 QFile file( partFile );
4134 if( file.exists() ) file.remove();
4136 m_waitForFileOperation = false;
4137 m_fileOperationFailed = true;
4138 continue;
4141 usleep( 10000 );
4142 kapp->processEvents( QEventLoop::AllEvents );
4145 if( !m_fileOperationFailed )
4147 if( copy )
4149 MetaBundle bundle( dstURL );
4150 if( bundle.isValidMedia() )
4152 addSong( &bundle, true );
4153 return true;
4156 else
4158 emit fileMoved( src, dest );
4159 migrateFile( srcURL.path(), dstURL.path() );
4161 if( isFileInCollection( srcURL.path() ) )
4163 return true;
4165 else
4167 MetaBundle bundle( dstURL );
4168 if( bundle.isValidMedia() )
4170 addSong( &bundle, true );
4171 return true;
4177 return false;
4181 void
4182 CollectionDB::updateDirStats( QString path, const long datetime, const bool temporary )
4184 if ( path.endsWith( "/" ) )
4185 path = path.left( path.length() - 1 );
4187 int deviceid = MountPointManager::instance()->getIdForUrl( path );
4188 QString rpath = MountPointManager::instance()->getRelativePath( deviceid, path );
4190 if (getDbConnectionType() == DbConnection::postgresql)
4192 // REPLACE INTO is not valid SQL for postgres, so we need to check whether we
4193 // should UPDATE() or INSERT()
4194 QStringList values = query( QString("SELECT * FROM directories%1 WHERE dir='%3' AND deviceid=%2;")
4195 .arg( temporary ? "_temp" : "")
4196 .arg( deviceid )
4197 .arg( escapeString( rpath ) ) );
4199 if(values.count() > 0 )
4201 query( QString( "UPDATE directories%1 SET changedate=%2 WHERE dir='%4'AND deviceid=%3;")
4202 .arg( temporary ? "_temp" : "" )
4203 .arg( datetime )
4204 .arg( deviceid )
4205 .arg( escapeString( rpath ) ) );
4207 else
4210 query( QString( "INSERT INTO directories%1 (dir, deviceid,changedate) VALUES ('%4', %3, '%2');")
4211 .arg( temporary ? "_temp" : "")
4212 .arg( datetime )
4213 .arg( deviceid )
4214 .arg( escapeString( rpath ) ) );
4217 else
4219 query( QString( "REPLACE INTO directories%1 ( dir, deviceid, changedate ) VALUES ( '%4', %3, %2 );" )
4220 .arg( temporary ? "_temp" : "" )
4221 .arg( datetime )
4222 .arg( deviceid )
4223 .arg( escapeString( rpath ) ) );
4226 INotify::instance()->watchDir( path );
4230 void
4231 CollectionDB::removeSongsInDir( QString path, QMap<QString,QString> *tagsRemoved )
4233 if ( path.endsWith( "/" ) )
4234 path = path.left( path.length() - 1 );
4235 int deviceid = MountPointManager::instance()->getIdForUrl( path );
4236 QString rpath = MountPointManager::instance()->getRelativePath( deviceid, path );
4238 // Pass back the list of tags we actually delete if requested.
4239 if( tagsRemoved )
4241 QStringList result
4242 = query( QString( "SELECT tags.deviceid, tags.url, uniqueid.uniqueid FROM tags "
4243 "LEFT JOIN uniqueid ON uniqueid.url = tags.url "
4244 "AND uniqueid.deviceid = tags.deviceid "
4245 "WHERE tags.dir = '%2' AND tags.deviceid = %1" )
4246 .arg( deviceid )
4247 .arg( escapeString( rpath ) ) );
4248 QStringList::ConstIterator it = result.begin(), end = result.end();
4249 while( it != end )
4251 int deviceid2 = (*(it++)).toInt();
4252 QString rpath2 = *(it++);
4253 QString uniqueid = *(it++);
4254 (*tagsRemoved)[uniqueid] = MountPointManager::instance()->getAbsolutePath(
4255 deviceid2, rpath2 );
4259 query( QString( "DELETE FROM tags WHERE dir = '%2' AND deviceid = %1;" )
4260 .arg( deviceid )
4261 .arg( escapeString( rpath ) ) );
4263 query( QString( "DELETE FROM uniqueid WHERE dir = '%2' AND deviceid = %1;" )
4264 .arg( deviceid )
4265 .arg( escapeString( rpath ) ) );
4269 bool
4270 CollectionDB::isDirInCollection( QString path )
4272 if ( path.endsWith( "/" ) )
4273 path = path.left( path.length() - 1 );
4274 int deviceid = MountPointManager::instance()->getIdForUrl( path );
4275 QString rpath = MountPointManager::instance()->getRelativePath( deviceid, path );
4277 QStringList values =
4278 query( QString( "SELECT changedate FROM directories WHERE dir = '%2' AND deviceid = %1;" )
4279 .arg( deviceid )
4280 .arg( escapeString( rpath ) ) );
4282 return !values.isEmpty();
4286 bool
4287 CollectionDB::isFileInCollection( const QString &url )
4289 int deviceid = MountPointManager::instance()->getIdForUrl( url );
4290 QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
4292 QString sql = QString( "SELECT url FROM tags WHERE url = '%2' AND deviceid = %1" )
4293 .arg( deviceid )
4294 .arg( escapeString( rpath ) );
4295 if ( deviceid == -1 )
4297 sql += ';';
4299 else
4301 QString rpath2 = '.' + url;
4302 sql += QString( " OR url = '%1' AND deviceid = -1;" )
4303 .arg( escapeString( rpath2 ) );
4305 QStringList values = query( sql );
4307 return !values.isEmpty();
4311 void
4312 CollectionDB::removeSongs( const KUrl::List& urls )
4314 for( KUrl::List::ConstIterator it = urls.begin(), end = urls.end(); it != end; ++it )
4316 int deviceid = MountPointManager::instance()->getIdForUrl( *it );
4317 QString rpath = MountPointManager::instance()->getRelativePath( deviceid, (*it).path() );
4319 query( QString( "DELETE FROM tags WHERE url = '%2' AND deviceid = %1;" )
4320 .arg( deviceid )
4321 .arg( escapeString( rpath ) ) );
4322 query( QString( "DELETE FROM uniqueid WHERE url = '%2' AND deviceid = %1;" )
4323 .arg( deviceid )
4324 .arg( escapeString( rpath ) ) );
4325 query( QString( "UPDATE statistics SET deleted = %1 WHERE url = '%3' AND deviceid = %2;" )
4326 .arg( boolT() )
4327 .arg( deviceid )
4328 .arg( escapeString( rpath ) ) );
4333 QStringList
4334 CollectionDB::similarArtists( const QString &artist, uint count )
4336 QStringList values;
4338 values = query( QString( "SELECT suggestion FROM related_artists WHERE artist = '%1' ORDER BY %2 LIMIT %3 OFFSET 0;" )
4339 .arg( escapeString( artist ), randomFunc(), QString::number( count ) ) );
4341 if ( values.isEmpty() )
4342 Scrobbler::instance()->similarArtists( artist );
4344 return values;
4348 void
4349 CollectionDB::sanitizeCompilations()
4351 query( QString( "UPDATE tags_temp SET sampler = %1 WHERE sampler IS NULL;").arg( boolF() ) );
4354 void
4355 CollectionDB::checkCompilations( const QString &path, const bool temporary )
4357 QStringList albums;
4358 QStringList artists;
4359 QStringList dirs;
4361 int deviceid = MountPointManager::instance()->getIdForUrl( path );
4362 QString rpath = MountPointManager::instance()->getRelativePath( deviceid, path );
4364 albums = query( QString( "SELECT DISTINCT album.name FROM tags_temp, album%1 AS album WHERE tags_temp.dir = '%3' AND tags_temp.deviceid = %2 AND album.id = tags_temp.album AND tags_temp.sampler IS NULL;" )
4365 .arg( temporary ? "_temp" : "" )
4366 .arg( deviceid )
4367 .arg( escapeString( rpath ) ) );
4369 for ( int i = 0; i < albums.count(); i++ )
4371 if ( albums[ i ].isEmpty() ) continue;
4373 const uint album_id = albumID( albums[ i ], false, temporary, true );
4374 artists = query( QString( "SELECT DISTINCT artist.name FROM tags_temp, artist%1 AS artist WHERE tags_temp.album = '%2' AND tags_temp.artist = artist.id;" )
4375 .arg( temporary ? "_temp" : "" )
4376 .arg( album_id ) );
4377 dirs = query( QString( "SELECT DISTINCT dir FROM tags_temp WHERE album = '%1';" )
4378 .arg( album_id ) );
4380 if ( artists.count() > dirs.count() )
4382 debug() << "Detected compilation: " << albums[ i ] << " - " << artists.count() << ':' << dirs.count();
4384 query( QString( "UPDATE tags_temp SET sampler = %1 WHERE album = '%2' AND sampler IS NULL;" )
4385 .arg(artists.count() > dirs.count() ? boolT() : boolF()).arg( album_id ) );
4389 void
4390 CollectionDB::setCompilation( const KUrl::List &urls, bool enabled, bool updateView )
4392 Q_UNUSED( updateView );
4393 for ( KUrl::List::const_iterator it = urls.begin(); it != urls.end(); ++it )
4395 QString url( ( *it ).path() );
4397 int deviceid = MountPointManager::instance()->getIdForUrl( url );
4398 QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
4400 query( QString( "UPDATE tags SET sampler = %1 WHERE tags.url = '%2' AND tags.deviceid = %3;" )
4401 .arg( ( enabled ? boolT() : boolF() ), escapeString( rpath ), QString::number( deviceid ) ) );
4404 // Update the Collection-Browser view,
4405 // using QTimer to make sure we don't manipulate the GUI from a thread
4407 //<PORT> 2.0
4408 // if ( updateView )
4409 // QTimer::singleShot( 0, CollectionView::instance(), SLOT( renderView() ) );
4410 //</PORT>
4414 void
4415 CollectionDB::removeDirFromCollection( QString path )
4417 //if ( path.endsWith( "/" ) )
4418 // path = path.left( path.length() - 1 );
4419 int deviceid = MountPointManager::instance()->getIdForUrl( path );
4420 QString rpath = MountPointManager::instance()->getRelativePath( deviceid, path );
4422 query( QString( "DELETE FROM directories WHERE dir = '%2' AND deviceid = %1;" )
4423 .arg( deviceid )
4424 .arg( escapeString( rpath ) ) );
4428 QString
4429 CollectionDB::IDFromExactValue( QString table, QString value, bool autocreate, bool temporary /* = false */ )
4431 if ( temporary )
4433 table.append( "_temp" );
4436 QString querystr( QString( "SELECT id FROM %1 WHERE name " ).arg( table ) );
4437 querystr += exactCondition( value ) + ';';
4438 QStringList result = query( querystr );
4439 if ( result.isEmpty() )
4441 if ( autocreate )
4442 return QString::number( insert( QString( "INSERT INTO %1 ( name ) VALUES ( '%2' );" )
4443 .arg( table, escapeString( value ) ),
4444 table ) );
4445 else
4446 return 0;
4448 else
4450 if ( result.count() > 1 )
4451 debug() << "More than one entry in the " << table << " database for '" << value << '\'';
4452 return result.first();
4456 void
4457 CollectionDB::deleteRedundantName( const QString &table, const QString &id )
4459 QString querystr( QString( "SELECT %1 FROM tags WHERE tags.%1 = %2 LIMIT 1;" ).arg( table, id ) );
4460 QStringList result = query( querystr );
4461 if ( result.isEmpty() )
4462 query( QString( "DELETE FROM %1 WHERE id = %2;" ).arg( table,id ) );
4465 void
4466 CollectionDB::deleteAllRedundant( const QString &table )
4468 //This works with MySQL4. I thought it might not do, due to the comment in copyTempTables
4469 query( QString( "DELETE FROM %1 WHERE id NOT IN ( SELECT %2 FROM tags )" ).arg( table, table ) );
4473 void
4474 CollectionDB::updateTags( const QString &url, const MetaBundle &bundle, const bool updateView )
4476 Q_UNUSED( updateView );
4477 DEBUG_BLOCK
4478 QueryBuilder qb;
4479 qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valTitle );
4480 qb.addReturnValue( QueryBuilder::tabArtist, QueryBuilder::valName );
4481 qb.addReturnValue( QueryBuilder::tabComposer, QueryBuilder::valName );
4482 qb.addReturnValue( QueryBuilder::tabAlbum, QueryBuilder::valName );
4483 qb.addReturnValue( QueryBuilder::tabGenre, QueryBuilder::valName );
4484 qb.addReturnValue( QueryBuilder::tabYear, QueryBuilder::valName );
4485 qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valTrack );
4486 qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valComment );
4487 qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valDiscNumber );
4488 qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valFilesize );
4489 qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valFileType );
4490 // [10] is above. [11] is below.
4491 qb.addReturnValue( QueryBuilder::tabSong, QueryBuilder::valBPM );
4492 qb.addReturnValue( QueryBuilder::tabArtist, QueryBuilder::valID );
4493 qb.addReturnValue( QueryBuilder::tabComposer, QueryBuilder::valID );
4494 qb.addReturnValue( QueryBuilder::tabAlbum, QueryBuilder::valID );
4495 qb.addReturnValue( QueryBuilder::tabGenre, QueryBuilder::valID );
4496 qb.addReturnValue( QueryBuilder::tabYear, QueryBuilder::valID );
4498 qb.addUrlFilters ( QStringList( url ) );
4499 qb.setOptions( QueryBuilder::optRemoveDuplicates );
4500 QStringList values = qb.run();
4502 if ( values.count() > 17 )
4504 error() << "Query returned more than 1 song. Aborting updating metadata";
4505 return;
4508 if ( !values.isEmpty() )
4510 bool art=false, comp=false, alb=false, gen=false, year=false;
4512 QString command = "UPDATE tags SET ";
4513 if ( values[ 0 ] != bundle.title() )
4514 command += "title = '" + escapeString( bundle.title() ) + "', ";
4515 if ( values[ 1 ] != bundle.artist() )
4517 art = true;
4518 command += "artist = " + IDFromExactValue( "artist", bundle.artist() ) + ", ";
4520 if ( values[ 2 ] != bundle.composer() )
4522 comp = true;
4523 command += "composer = " + IDFromExactValue( "composer", bundle.composer() ) + ", ";
4525 if ( values[ 3 ] != bundle.album() )
4527 alb = true;
4528 command += "album = " + IDFromExactValue( "album", bundle.album() ) + ", ";
4530 if ( values[ 4 ] != bundle.genre() )
4532 gen = true;
4533 command += "genre = " + IDFromExactValue( "genre", bundle.genre() ) + ", ";
4535 if ( values[ 5 ] != QString::number( bundle.year() ) )
4537 year = false;
4538 command += "year = " + IDFromExactValue( "year", QString::number( bundle.year() ) ) + ", ";
4540 if ( values[ 6 ] != QString::number( bundle.track() ) )
4541 command += "track = " + QString::number( bundle.track() ) + ", ";
4542 if ( values[ 7 ] != bundle.comment() )
4543 command += "comment = '" + escapeString( bundle.comment() ) + "', ";
4544 if ( values[ 8 ] != QString::number( bundle.discNumber() ) )
4545 command += "discnumber = '" + QString::number( bundle.discNumber() ) + "', ";
4546 if ( values[ 9 ] != QString::number( bundle.filesize() ) )
4547 command += "filesize = '" + QString::number( bundle.filesize() ) + "', ";
4548 if ( values[ 10 ] != QString::number( bundle.fileType() ) )
4549 command += "filetype = '" + QString::number( bundle.fileType() ) + "', ";
4550 if ( values[ 11 ] != QString::number( bundle.bpm() ) )
4551 command += "bpm = '" + QString::number( bundle.bpm() ) + "', ";
4553 if ( "UPDATE tags SET " == command )
4555 debug() << "No tags selected to be changed";
4557 else
4559 int deviceid = MountPointManager::instance()->getIdForUrl( url );
4560 QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
4561 //We have to remove the trailing comma from command
4562 query( command.left( command.length() - 2 ) + " WHERE url = '" + escapeString( rpath ) +
4563 "' AND deviceid = " + QString::number( deviceid ) + ';' );
4566 //Check to see if we use the entry anymore. If not, delete it
4567 if ( art )
4568 deleteRedundantName( "artist", values[ 12 ] );
4569 if ( comp )
4570 deleteRedundantName( "composer", values[ 13 ] );
4571 if ( alb )
4572 deleteRedundantName( "album", values[ 14 ] );
4573 if ( gen )
4574 deleteRedundantName( "genre", values[ 15 ] );
4575 if ( year )
4576 deleteRedundantName( "year", values[ 16 ] );
4578 // Update the Collection-Browser view,
4579 // using QTimer to make sure we don't manipulate the GUI from a thread
4580 //<PORT> 2.0
4581 //if ( updateView )
4582 // QTimer::singleShot( 0, CollectionView::instance(), SLOT( databaseChanged() ) );
4583 //</PORT>
4585 if( art || alb )
4586 emit tagsChanged( values[12], values[14] );
4589 //this method is totally useless now, so i'm not going to fix this
4590 /*if ( EngineController::instance()->bundle().url() == bundle.url() )
4592 debug() << "Current song edited, updating widgets: " << bundle.title();
4593 EngineController::instance()->currentTrackMetaDataChanged( bundle );
4596 emit tagsChanged( bundle );
4600 void
4601 CollectionDB::updateURL( const QString &url, const bool updateView )
4603 // don't use the KUrl ctor as it checks the db first
4604 MetaBundle bundle;
4605 bundle.setPath( url );
4606 bundle.readTags( TagLib::AudioProperties::Fast );
4608 updateTags( url, bundle, updateView);
4609 doAFTStuff( &bundle, false );
4612 QString
4613 CollectionDB::getUniqueId( const QString &url )
4615 int deviceid = MountPointManager::instance()->getIdForUrl( url );
4616 QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
4617 QStringList values = query( QString( "SELECT uniqueid FROM uniqueid WHERE deviceid = %1 AND url = '%2';" )
4618 .arg( deviceid )
4619 .arg( escapeString( rpath ) ));
4620 if( !values.empty() )
4621 return values[0];
4622 else
4623 return QString();
4626 void
4627 CollectionDB::setLyrics( const QString &url, const QString &lyrics, const QString &uniqueid )
4629 int deviceid = MountPointManager::instance()->getIdForUrl( url );
4630 QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
4632 QStringList values = query(QString("SELECT lyrics FROM lyrics WHERE url = '%2' AND deviceid = %1;")
4633 .arg( deviceid ).arg( escapeString( rpath ) ) );
4634 if(values.count() > 0)
4636 if ( !lyrics.isEmpty() )
4637 query( QString( "UPDATE lyrics SET lyrics = '%1' WHERE url = '%3' AND deviceid = %2;" )
4638 .arg( escapeString( lyrics), QString::number(deviceid), escapeString( rpath ) ) );
4639 else
4640 query( QString( "DELETE FROM lyrics WHERE url = '%2' AND deviceid = %1;" )
4641 .arg( deviceid).arg( escapeString( rpath ) ) );
4643 else
4645 insert( QString( "INSERT INTO lyrics (deviceid, url, lyrics, uniqueid) values ( %1, '%2', '%3', '%4' );" )
4646 .arg( QString::number(deviceid), escapeString( rpath ), escapeString( lyrics ), escapeString( uniqueid ) ), NULL);
4651 QString
4652 CollectionDB::getLyrics( const QString &url )
4654 int deviceid = MountPointManager::instance()->getIdForUrl( url );
4655 QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
4656 QStringList values = query( QString( "SELECT lyrics FROM lyrics WHERE url = '%2' AND deviceid = %1;" )
4657 .arg( deviceid ).arg( escapeString( rpath ) ) );
4658 if ( !values.isEmpty() )
4659 return values[0];
4660 else
4661 return QString();
4664 void CollectionDB::removeInvalidAmazonInfo( const QString& md5sum )
4666 query( QString( "DELETE FROM amazon WHERE filename='%1'" ).arg( md5sum ) );
4669 void CollectionDB::newAmazonReloadDate( const QString& asin, const QString& locale, const QString& md5sum)
4671 QStringList values = query(QString("SELECT filename FROM amazon WHERE filename = '%1'")
4672 .arg(md5sum));
4673 if(values.count() > 0)
4675 query( QString("UPDATE amazon SET asin = '%1', locale = '%2', refetchdate = '%3' WHERE filename = '%4'")
4676 .arg(asin)
4677 .arg(locale)
4678 .arg(QDateTime::currentDateTime().addDays(80).toTime_t())
4679 .arg(md5sum));
4681 else
4683 insert( QString( "INSERT INTO amazon ( asin, locale, filename, refetchdate ) VALUES ( '%1', '%2', '%3', '%4');" )
4684 .arg(asin)
4685 .arg(locale)
4686 .arg(md5sum)
4687 .arg(QDateTime::currentDateTime().addDays(80).toTime_t()), NULL );
4691 QStringList CollectionDB::staleImages()
4693 return query(QString("SELECT asin, locale, filename FROM amazon WHERE refetchdate < %1 ;")
4694 .arg(QDateTime::currentDateTime().toTime_t() ));
4697 void
4698 CollectionDB::applySettings()
4700 bool recreateConnections = false;
4701 if ( AmarokConfig::databaseEngine().toInt() != getDbConnectionType() )
4703 if ( AmarokConfig::databaseEngine().toInt() == DbConnection::mysql )
4704 m_dbConnType = DbConnection::mysql;
4705 else if ( AmarokConfig::databaseEngine().toInt() == DbConnection::postgresql )
4706 m_dbConnType = DbConnection::postgresql;
4707 else m_dbConnType = DbConnection::sqlite;
4708 recreateConnections = true;
4710 else if ( AmarokConfig::databaseEngine().toInt() == DbConnection::mysql )
4712 // Using MySQL, so check if MySQL settings were changed
4713 const MySqlConfig *config =
4714 static_cast<const MySqlConfig*> ( m_dbConfig );
4715 if ( AmarokConfig::mySqlHost() != config->host() )
4717 recreateConnections = true;
4719 else if ( AmarokConfig::mySqlPort() != config->port() )
4721 recreateConnections = true;
4723 else if ( AmarokConfig::mySqlDbName() != config->database() )
4725 recreateConnections = true;
4727 else if ( AmarokConfig::mySqlUser() != config->username() )
4729 recreateConnections = true;
4731 else if ( AmarokConfig::mySqlPassword() != config->password() )
4733 recreateConnections = true;
4736 else if ( AmarokConfig::databaseEngine().toInt() == DbConnection::postgresql )
4738 const PostgresqlConfig *config =
4739 static_cast<const PostgresqlConfig*> ( m_dbConfig );
4740 if ( AmarokConfig::postgresqlHost() != config->host() )
4742 recreateConnections = true;
4744 else if ( AmarokConfig::postgresqlPort() != config->port() )
4746 recreateConnections = true;
4748 else if ( AmarokConfig::postgresqlDbName() != config->database() )
4750 recreateConnections = true;
4752 else if ( AmarokConfig::postgresqlUser() != config->username() )
4754 recreateConnections = true;
4756 else if ( AmarokConfig::postgresqlPassword() != config->password() )
4758 recreateConnections = true;
4762 if ( recreateConnections )
4764 debug()
4765 << "Database engine settings changed: "
4766 << "recreating DbConnections";
4767 // If Database engine was changed, recreate DbConnections.
4768 destroy();
4769 initialize();
4770 //PORT 2.0 CollectionView::instance()->renderView();
4771 // PlaylistBrowser::instance()->loadPodcastsFromDatabase();
4773 emit databaseEngineChanged();
4777 DbConnection * CollectionDB::getMyConnection()
4779 //after some thought, to be thread-safe, must lock at the beginning of this function,
4780 //not only if a new connection is made
4781 connectionMutex->lock();
4783 DbConnection *dbConn;
4784 QThread *currThread = QThread::currentThread();
4786 if( threadConnections->contains(currThread) )
4788 QMap<QThread *, DbConnection *>::Iterator it = threadConnections->find(currThread);
4789 dbConn = it.value();
4790 connectionMutex->unlock();
4791 return dbConn;
4794 #ifdef USE_MYSQL
4795 if( m_dbConnType == DbConnection::mysql )
4797 dbConn = new MySqlConnection( static_cast<MySqlConfig*>( m_dbConfig ) );
4799 else
4800 #endif
4801 #ifdef USE_POSTGRESQL
4802 if( m_dbConnType == DbConnection::postgresql )
4804 dbConn = new PostgresqlConnection( static_cast<PostgresqlConfig*>( m_dbConfig ) );
4806 else
4807 #endif
4809 dbConn = new SqliteConnection( static_cast<SqliteConfig*>( m_dbConfig ) );
4812 threadConnections->insert(currThread, dbConn);
4814 connectionMutex->unlock();
4815 return dbConn;
4819 void
4820 CollectionDB::releasePreviousConnection(QThread *currThread)
4822 //if something already exists, delete the object, and erase knowledge of it from the QMap.
4823 connectionMutex->lock();
4824 DbConnection *dbConn;
4825 if (threadConnections->contains(currThread))
4827 QMap<QThread *, DbConnection *>::Iterator it = threadConnections->find(currThread);
4828 dbConn = it.value();
4829 delete dbConn;
4830 threadConnections->remove(currThread);
4832 connectionMutex->unlock();
4835 bool
4836 CollectionDB::isConnected()
4838 return getMyConnection()->isConnected();
4841 //////////////////////////////////////////////////////////////////////////////////////////
4842 // PROTECTED
4843 //////////////////////////////////////////////////////////////////////////////////////////
4845 QByteArray
4846 CollectionDB::md5sum( const QString& artist, const QString& album, const QString& file )
4848 KMD5 context( artist.toLower().toLocal8Bit() + album.toLower().toLocal8Bit() + file.toLocal8Bit() );
4849 // debug() << "MD5 SUM for " << artist << ", " << album << ": " << context.hexDigest();
4850 return context.hexDigest();
4853 void
4854 CollectionDB::timerEvent( QTimerEvent* )
4856 scanMonitor();
4860 //////////////////////////////////////////////////////////////////////////////////////////
4861 // PUBLIC SLOTS
4862 //////////////////////////////////////////////////////////////////////////////////////////
4864 // void
4865 // CollectionDB::fetchCover( QWidget* parent, const QString& artist, const QString& album, bool noedit, Q3ListViewItem* item ) //SLOT
4866 // {
4867 // debug() << "Fetching cover for " << artist << " - " << album;
4869 // const bool isCompilation = albumIsCompilation( QString::number( albumID( album, false, false, true ) ) );
4870 // CoverFetcher* fetcher;
4871 // if( isCompilation )
4872 // // avoid putting various artists in front of album title. this causes problems for locales other than US.
4873 // fetcher = new CoverFetcher( parent, "", album );
4874 // else
4875 // fetcher = new CoverFetcher( parent, artist, album );
4876 // if( item )
4877 // {
4878 // itemCoverMapMutex->lock();
4879 // itemCoverMap->insert( item, fetcher );
4880 // itemCoverMapMutex->unlock();
4881 // }
4882 // connect( fetcher, SIGNAL(result( CoverFetcher* )), SLOT(coverFetcherResult( CoverFetcher* )) );
4883 // fetcher->setUserCanEditQuery( !noedit );
4884 // fetcher->startFetch();
4885 // }
4887 void
4888 CollectionDB::scanMonitor() //SLOT
4890 if ( AmarokConfig::monitorChanges() )
4891 scanModifiedDirs();
4895 void
4896 CollectionDB::startScan() //SLOT
4898 QStringList folders = MountPointManager::instance()->collectionFolders();
4900 if ( folders.isEmpty() )
4902 //dropTables( false );
4903 //createTables( false );
4904 clearTables( false );
4905 emit scanDone( true );
4907 //Port 2.0
4908 else /*if( PlaylistBrowser::instance() )*/
4910 emit scanStarted();
4911 CollectionManager::instance()->startFullScan();
4916 void
4917 CollectionDB::stopScan() //SLOT
4919 ThreadManager::instance()->abortAllJobsNamed( "CollectionScanner" );
4923 //////////////////////////////////////////////////////////////////////////////////////////
4924 // PRIVATE SLOTS
4925 //////////////////////////////////////////////////////////////////////////////////////////
4927 void
4928 CollectionDB::dirDirty( const QString& path )
4930 debug() << "Dirty: " << path;
4931 QStringList dir;
4932 dir.append( path );
4934 CollectionManager::instance()->startFullScan();
4938 // void
4939 // CollectionDB::coverFetcherResult( CoverFetcher *fetcher )
4940 // {
4941 // if( fetcher->wasError() ) {
4942 // error() << fetcher->errors();
4943 // emit coverFetcherError( fetcher->errors().front() );
4944 // }
4946 // else {
4947 // setAlbumImage( fetcher->artist(), fetcher->album(), fetcher->image(), fetcher->amazonURL(), fetcher->asin() );
4948 // emit coverFetched( fetcher->artist(), fetcher->album() );
4949 // }
4951 // //check the validity of the CollectionItem as it may have been deleted e.g. by a
4952 // //collection scan while fetching the cover
4953 // /* PORT 2.0 itemCoverMapMutex->lock();
4954 // QMap<Q3ListViewItem*, CoverFetcher*>::Iterator it;
4955 // for( it = itemCoverMap->begin(); it != itemCoverMap->end(); ++it )
4956 // {
4957 // if( it.data() == fetcher )
4958 // {
4959 // if( it.key()->isOpen() )
4960 // static_cast<CollectionItem*>(it.key())->setPixmap( 0, QPixmap() );
4961 // itemCoverMap->erase( it );
4962 // }
4963 // } */
4964 // itemCoverMapMutex->unlock();
4965 // }
4968 * This query is fairly slow with sqlite, and often happens just
4969 * after the OSD is shown. Threading it restores responsivity.
4971 class SimilarArtistsInsertionJob : public ThreadManager::DependentJob
4973 virtual bool doJob()
4975 CollectionDB::instance()->query( QString( "DELETE FROM related_artists WHERE artist = '%1';" ).arg( escapedArtist ) );
4977 const QString sql = "INSERT INTO related_artists ( artist, suggestion, changedate ) VALUES ( '%1', '%2', 0 );";
4978 oldForeach( suggestions )
4979 CollectionDB::instance()->insert( sql
4980 .arg( escapedArtist,
4981 CollectionDB::instance()->escapeString( *it ) ), NULL);
4983 return true;
4986 virtual void completeJob() { emit CollectionDB::instance()->similarArtistsFetched( artist ); }
4988 const QString artist;
4989 const QString escapedArtist;
4990 const QStringList suggestions;
4992 public:
4993 SimilarArtistsInsertionJob( CollectionDB *parent, const QString &s, const QStringList &list )
4994 : ThreadManager::DependentJob( parent, "SimilarArtistsInsertionJob" )
4995 , artist( s )
4996 , escapedArtist( parent->escapeString( s ) )
4997 , suggestions( list )
5001 void
5002 CollectionDB::similarArtistsFetched( const QString& artist, const QStringList& suggestions )
5004 debug() << "Received similar artists\n";
5006 ThreadManager::instance()->queueJob( new SimilarArtistsInsertionJob( this, artist, suggestions ) );
5009 void
5010 CollectionDB::aftCheckPermanentTables( const QString &currdeviceid, const QString &currid, const QString &currurl )
5012 //DEBUG_BLOCK
5013 //debug() << "deviceid = " << currdeviceid << endl << "url = " << currurl << endl << "uid = " << currid;
5015 QStringList check1, check2;
5017 oldForeach( m_aftEnabledPersistentTables )
5019 //debug() << "Checking " << (*it);;
5020 check1 = query( QString(
5021 "SELECT url, deviceid "
5022 "FROM %1 "
5023 "WHERE uniqueid = '%2';" )
5024 .arg( escapeString( *it ) )
5025 .arg( currid ) );
5027 check2 = query( QString(
5028 "SELECT url, uniqueid "
5029 "FROM %1 "
5030 "WHERE deviceid = %2 AND url = '%3';" )
5031 .arg( escapeString( *it ) )
5032 .arg( currdeviceid
5033 , currurl ) );
5035 if( !check1.empty() )
5037 //debug() << "uniqueid found, updating url";
5038 query( QString( "UPDATE %1 SET deviceid = %2, url = '%4' WHERE uniqueid = '%3';" )
5039 .arg( escapeString( *it ) )
5040 .arg( currdeviceid
5041 , currid
5042 , currurl ) );
5044 else if( !check2.empty() )
5046 //debug() << "url found, updating uniqueid";
5047 query( QString( "UPDATE %1 SET uniqueid = '%2' WHERE deviceid = %3 AND url = '%4';" )
5048 .arg( escapeString( *it ) )
5049 .arg( currid
5050 , currdeviceid
5051 , currurl ) );
5056 void
5057 CollectionDB::aftMigratePermanentTablesUrl( const QString& /*oldUrl*/, const QString& newUrl, const QString& uniqueid )
5059 //DEBUG_BLOCK
5060 int deviceid = MountPointManager::instance()->getIdForUrl( newUrl );
5061 QString rpath = MountPointManager::instance()->getRelativePath( deviceid, newUrl );
5062 //NOTE: if ever do anything with "deleted" in the statistics table, set deleted to false in query
5063 //below; will need special case.
5064 //debug() << "deviceid = " << deviceid << endl << "newurl = " << newUrl << endl << "uid = " << uniqueid;
5065 oldForeach( m_aftEnabledPersistentTables )
5067 query( QString( "DELETE FROM %1 WHERE deviceid = %2 AND url = '%3';" )
5068 .arg( escapeString( *it ) )
5069 .arg( deviceid )
5070 .arg( escapeString( rpath ) ) );
5071 query( QString( "UPDATE %1 SET deviceid = %2, url = '%4' WHERE uniqueid = '%3';" )
5072 .arg( escapeString( *it ) )
5073 .arg( deviceid )
5074 .arg( escapeString( uniqueid ) )
5075 .arg( escapeString( rpath ) ) );
5079 void
5080 CollectionDB::aftMigratePermanentTablesUniqueId( const QString& /*url*/, const QString& oldid, const QString& newid )
5082 //DEBUG_BLOCK
5083 //debug() << "oldid = " << oldid << endl << "newid = " << newid;
5084 //NOTE: if ever do anything with "deleted" in the statistics table, set deleted to false in query
5085 //below; will need special case.
5086 oldForeach( m_aftEnabledPersistentTables )
5088 query( QString( "DELETE FROM %1 WHERE uniqueid = '%2';" )
5089 .arg( escapeString( *it ) )
5090 .arg( escapeString( newid ) ) );
5091 query( QString( "UPDATE %1 SET uniqueid = '%1' WHERE uniqueid = '%2';" )
5092 .arg( escapeString( *it ) )
5093 .arg( escapeString( newid ) )
5094 .arg( escapeString( oldid ) ) );
5099 //////////////////////////////////////////////////////////////////////////////////////////
5100 // PRIVATE
5101 //////////////////////////////////////////////////////////////////////////////////////////
5103 void
5104 CollectionDB::initialize()
5106 DEBUG_BLOCK
5108 /// Create DBConfig instance:
5110 #ifdef USE_MYSQL
5111 if ( m_dbConnType == DbConnection::mysql )
5113 QString appVersion = Amarok::config( "General Options" ).readEntry( "Version" );
5114 QString passwd = AmarokConfig::mySqlPassword2(); // stored as string type
5116 if( passwd.isEmpty() )
5118 if( appVersion.startsWith( "1.3" ) )
5120 /// This is because of the encrypted -> plaintext conversion
5121 passwd = AmarokConfig::mySqlPassword(); // stored as password type
5122 AmarokConfig::setMySqlPassword2( passwd );
5124 else if( appVersion.startsWith( "1.4" ) )
5126 passwd = Amarok::config( "MySql" ).readEntry( "MySqlPassword" ); //read the field as plaintext
5127 AmarokConfig::setMySqlPassword2( passwd ); // store it in plaintext field
5131 m_dbConfig = new MySqlConfig(
5132 AmarokConfig::mySqlHost(),
5133 AmarokConfig::mySqlPort(),
5134 AmarokConfig::mySqlDbName(),
5135 AmarokConfig::mySqlUser(),
5136 passwd );
5138 else
5139 #endif
5140 #ifdef USE_POSTGRESQL
5141 if ( m_dbConnType == DbConnection::postgresql )
5143 QString appVersion = Amarok::config( "General Options" ).readEntry( "Version" );
5144 QString passwd = AmarokConfig::postgresqlPassword2();
5146 if( passwd.isEmpty() )
5148 if( appVersion.startsWith( "1.3" ) )
5150 /// This is because of the encrypted -> plaintext conversion
5151 passwd = AmarokConfig::postgresqlPassword(); // stored as password type
5152 AmarokConfig::setPostgresqlPassword2( passwd );
5154 else if( appVersion.startsWith( "1.4" ) &&
5155 ( appVersion.contains( "beta", false ) ||
5156 appVersion.contains( "svn", false ) ) )
5158 passwd = Amarok::config( "Postgresql" ).readEntry( "PostgresqlPassword" );
5159 AmarokConfig::setPostgresqlPassword2( passwd );
5163 m_dbConfig = new PostgresqlConfig(
5164 AmarokConfig::postgresqlHost(),
5165 AmarokConfig::postgresqlPort(),
5166 AmarokConfig::postgresqlDbName(),
5167 AmarokConfig::postgresqlUser(),
5168 passwd );
5170 else
5171 #endif
5173 m_dbConfig = new SqliteConfig(
5174 Amarok::config( "Sqlite" ).readEntry( "location",
5175 Amarok::saveLocation() + "collection.db" ) );
5178 DbConnection *dbConn = getMyConnection();
5180 if ( !dbConn->isConnected() || !dbConn->isInitialized() )
5182 error() << "Failed to connect to or initialise database!";
5183 Amarok::MessageQueue::instance()->addMessage( dbConn->lastError() );
5185 else
5187 if ( !isValid() )
5189 //No tables seem to exist (as doing a count(url) didn't even return any number, even 0).
5190 warning() << "Tables seem to not exist.";
5191 warning() << "Attempting to create tables (this should be safe; ignore any errors)...";
5192 createTables(false);
5193 createPersistentTables();
5194 createPodcastTables();
5195 createStatsTable();
5196 warning() << "Tables should now definitely exist. (Stop ignoring errors)";
5198 //Since we have created the tables, we need to make sure the version numbers are
5199 //set to the correct values. If this is not done now, the database update code may
5200 //run, which could corrupt things.
5201 Amarok::config( "Collection Browser" ).writeEntry( "Database Version", DATABASE_VERSION );
5202 Amarok::config( "Collection Browser" ).writeEntry( "Database Stats Version", DATABASE_STATS_VERSION );
5203 Amarok::config( "Collection Browser" ).writeEntry( "Database Persistent Tables Version", DATABASE_PERSISTENT_TABLES_VERSION );
5204 Amarok::config( "Collection Browser" ).writeEntry( "Database Podcast Tables Version", DATABASE_PODCAST_TABLES_VERSION ); Amarok::config( "Collection Browser" ).writeEntry( "Database AFT Version", DATABASE_AFT_VERSION );
5206 setAdminValue( "Database Version", QString::number( DATABASE_VERSION ) );
5207 setAdminValue( "Database Stats Version", QString::number( DATABASE_STATS_VERSION ) );
5208 setAdminValue( "Database Persistent Tables Version", QString::number( DATABASE_PERSISTENT_TABLES_VERSION ) );
5209 setAdminValue( "Database Podcast Tables Version", QString::number( DATABASE_PODCAST_TABLES_VERSION ) );
5210 setAdminValue( "Database AFT Version", QString::number( DATABASE_AFT_VERSION ) );
5214 // Due to a bug in our sqllite handling code, we have to recreate the indices.
5215 // We should rmeove this before 1.4.5
5216 if ( m_dbConnType == DbConnection::sqlite ) {
5217 QStringList indices = query( "SELECT name FROM sqlite_master WHERE type='index' ORDER BY name;" );
5218 if (!indices.contains("url_tag")) {
5219 createIndices();
5224 //updates for the Devices table go here
5225 //put all other update code into checkDatabase()
5226 //make sure that there is no call to MountPointManager in CollectionDB's ctor
5227 //or in methods called from the ctor.
5228 if ( adminValue( "Database Devices Version" ).isEmpty()
5229 && Amarok::config( "CollectionBrowser" ).readEntry( "Database Devices Version", 0 ) == 0 )
5231 createDevicesTable();
5233 else if ( adminValue( "Database Devices Version" ).toInt() != DATABASE_DEVICES_VERSION
5234 || Amarok::config( "Collection Browser" ).readEntry( "Database Devices Version", 0 ) != DATABASE_DEVICES_VERSION )
5236 int prev = adminValue( "Database Devices Version" ).toInt();
5238 if ( prev > DATABASE_DEVICES_VERSION || prev < 0 )
5240 error() << "Database devices version too new for this version of Amarok";
5241 exit( 1 );
5242 //dropDevicesTable();
5244 else
5246 debug() << "Updating DEVICES table";
5247 //add future Devices update code here
5250 Amarok::config( "Collection Browser" ).writeEntry( "Database Devices Version", DATABASE_DEVICES_VERSION );
5251 setAdminValue( "Database Devices Version", QString::number( DATABASE_DEVICES_VERSION ) );
5253 //make sure that all indices exist
5254 createIndices();
5255 createPermanentIndices();
5260 void
5261 CollectionDB::checkDatabase()
5263 DEBUG_BLOCK
5264 if ( isValid() )
5266 //Inform the user that he should attach as many devices with music as possible
5267 //Hopefully this won't be necessary soon.
5269 //Currently broken, so disabled - seems to cause crashes as events are sent to
5270 //the Playlist - maybe it's not fully initialised?
5272 QString text = i18n( "Amarok has to update your database to be able to use the new Dynamic Collection(insert link) feature. Amarok now has to determine on which physical devices your collection is stored. Please attach all removable devices which contain part of your collection and continue. Cancelling will exit Amarok." );
5273 int result = KMessageBox::warningContinueCancel( 0, text, "Database migration" );
5274 if ( result != KMessageBox::Continue )
5276 error() << "Dynamic Collection migration was aborted by user...exiting";
5277 exit( 1 );
5281 bool needsUpdate = ( adminValue( "Database Stats Version" ).toInt() != DATABASE_STATS_VERSION
5282 || Amarok::config( "Collection Browser" ).readEntry( "Database Stats Version", 0 ) != DATABASE_STATS_VERSION
5283 || Amarok::config( "Collection Browser" ).readEntry( "Database Version", 0 ) != DATABASE_VERSION
5284 || adminValue( "Database Version" ).toInt() != DATABASE_VERSION
5285 || Amarok::config( "Collection Browser" ).readEntry( "Database Persistent Tables Version", 0 ) != DATABASE_PERSISTENT_TABLES_VERSION
5286 || adminValue( "Database Persistent Tables Version" ).toInt() != DATABASE_PERSISTENT_TABLES_VERSION
5287 || Amarok::config( "Collection Browser" ).readEntry( "Database Podcast Tables Version", 0 ) != DATABASE_PODCAST_TABLES_VERSION
5288 || adminValue( "Database Podcast Tables Version" ).toInt() != DATABASE_PODCAST_TABLES_VERSION
5289 || Amarok::config( "Collection Browser" ).readEntry( "Database AFT Version", 0 ) != DATABASE_AFT_VERSION
5290 || adminValue( "Database AFT Version" ).toInt() != DATABASE_AFT_VERSION );
5292 if ( needsUpdate )
5295 KDialog *dialog = new KDialog( 0 );
5296 dialog->setCaption( i18n( "Updating database" ) );
5297 dialog->setModal( false );
5298 dialog->showButtonSeparator( false );
5301 /* TODO: remove the standard window controls from the dialog window, the user should not be able
5302 to close, minimize, maximize the dialog
5303 add additional text, e.g. Amarok is currently updating your database. This may take a while.
5304 Please wait.
5306 Consider using a ProgressBarDialog
5308 QLabel *label = new QLabel( i18n( "Updating database" ), dialog );
5309 dialog->setMainWidget( label );
5310 label->show();
5311 QTimer::singleShot( 0, dialog, SLOT( show() ) );
5312 //process events in the main event loop so that the dialog actually gets shown
5313 kapp->processEvents();
5314 debug() << "Beginning database update";
5316 updateStatsTables();
5318 updatePersistentTables();
5320 updatePodcastTables();
5322 //remove database file if version is incompatible
5323 if ( Amarok::config( "Collection Browser" ).readEntry( "Database Version", 0 ) != DATABASE_VERSION
5324 || adminValue( "Database Version" ).toInt() != DATABASE_VERSION )
5326 debug() << "Rebuilding database!";
5327 dropTables(false);
5328 createTables(false);
5330 delete dialog;
5332 emit databaseUpdateDone();
5335 // TODO Should write to config in dtor, but it crashes...
5336 Amarok::config( "Collection Browser" ).writeEntry( "Database Version", DATABASE_VERSION );
5337 Amarok::config( "Collection Browser" ).writeEntry( "Database Stats Version", DATABASE_STATS_VERSION );
5338 Amarok::config( "Collection Browser" ).writeEntry( "Database Persistent Tables Version", DATABASE_PERSISTENT_TABLES_VERSION );
5339 Amarok::config( "Collection Browser" ).writeEntry( "Database Podcast Tables Version", DATABASE_PODCAST_TABLES_VERSION );
5340 Amarok::config( "Collection Browser" ).writeEntry( "Database AFT Version", DATABASE_AFT_VERSION );
5342 setAdminValue( "Database Version", QString::number( DATABASE_VERSION ) );
5343 setAdminValue( "Database Stats Version", QString::number( DATABASE_STATS_VERSION ) );
5344 setAdminValue( "Database Persistent Tables Version", QString::number( DATABASE_PERSISTENT_TABLES_VERSION ) );
5345 setAdminValue( "Database Podcast Tables Version", QString::number( DATABASE_PODCAST_TABLES_VERSION ) );
5346 setAdminValue( "Database AFT Version", QString::number( DATABASE_AFT_VERSION ) );
5348 initDirOperations();
5351 void
5352 CollectionDB::updateStatsTables()
5354 if ( adminValue( "Database Stats Version" ).toInt() != DATABASE_STATS_VERSION
5355 || Amarok::config( "Collection Browser" ).readEntry( "Database Stats Version", 0 ) != DATABASE_STATS_VERSION )
5357 debug() << "Different database stats version detected! Stats table will be updated or rebuilt.";
5359 #if 0 // causes mysterious crashes
5360 if( getType() == DbConnection::sqlite && QFile::exists( Amarok::saveLocation()+"collection.db" ) )
5362 debug() << "Creating a backup of the database in "
5363 << Amarok::saveLocation()+"collection-backup.db" << '.';
5365 bool copied = KIO::NetAccess::file_copy( Amarok::saveLocation()+"collection.db",
5366 Amarok::saveLocation()+"collection-backup.db",
5367 -1 /*perms*/, true /*overwrite*/, false /*resume*/ );
5369 if( !copied )
5371 debug() << "Backup failed! Perhaps the volume is not writable.";
5372 debug() << "Error was: " << KIO::NetAccess::lastErrorString();
5375 #endif
5377 int prev = adminValue( "Database Stats Version" ).toInt();
5379 /* If config returns 3 or lower, it came from an Amarok version that was not aware of
5380 admin table, so we can't trust this table at all */
5381 if( !prev || ( Amarok::config( "Collection Browser" ).readEntry( "Database Stats Version", 0 )
5382 && Amarok::config( "Collection Browser" ).readEntry( "Database Stats Version", 0 ) <= 3 ) )
5383 prev = Amarok::config( "Collection Browser" ).readEntry( "Database Stats Version", 0 );
5385 //pre somewhere in the 1.3-1.4 timeframe, the version wasn't stored in the DB, so try to guess it
5386 const QString q = "SELECT COUNT( %1 ) FROM statistics;";
5387 if( !prev && query( q.arg( "url" ) ).first().toInt()
5388 && query( q.arg( "createdate" ) ).first().toInt()
5389 && query( q.arg( "accessdate" ) ).first().toInt()
5390 && query( q.arg( "percentage" ) ).first().toInt()
5391 && query( q.arg( "playcounter" ) ).first().toInt() )
5393 prev = 3;
5396 if ( prev < 3 ) //it is from before 1.2, or our poor user is otherwise fucked
5398 debug() << "Rebuilding stats-database!";
5399 dropStatsTableV1();
5400 createStatsTable();
5402 else //Incrementally update the stats table to reach the present version
5404 if( prev < 4 ) //every version from 1.2 forward had a stats version of 3
5406 debug() << "Updating stats-database!";
5407 query( "ALTER TABLE statistics ADD rating INTEGER DEFAULT 0;" );
5408 query( "CREATE INDEX rating_stats ON statistics( rating );" );
5409 query( "UPDATE statistics SET rating=0 WHERE " + boolT() + ';' );
5411 if( prev < 5 )
5413 debug() << "Updating stats-database!";
5414 query( "UPDATE statistics SET rating = rating * 2;" );
5416 if( prev < 8 ) //Versions 6, 7 and 8 all were all attempts to add columns for ATF. his code should do it all.
5418 query( QString( "CREATE TABLE statistics_fix ("
5419 "url " + textColumnType() + " UNIQUE,"
5420 "createdate INTEGER,"
5421 "accessdate INTEGER,"
5422 "percentage FLOAT,"
5423 "rating INTEGER DEFAULT 0,"
5424 "playcounter INTEGER);" ) );
5426 insert( "INSERT INTO statistics_fix (url, createdate, accessdate, percentage, playcounter, rating)"
5427 "SELECT url, createdate, accessdate, percentage, playcounter, rating FROM statistics;"
5428 , NULL );
5430 dropStatsTableV1();
5431 createStatsTableV8();
5433 insert( "INSERT INTO statistics (url, createdate, accessdate, percentage, playcounter, rating)"
5434 "SELECT url, createdate, accessdate, percentage, playcounter, rating FROM statistics_fix;"
5435 , NULL );
5436 query( "DROP TABLE statistics_fix" );
5438 if( prev < 9 )
5440 //Update for Dynamic Collection:
5442 //This is not technically for the stats table, but it is part of the same
5443 //update, so put it here anyway.
5444 MountPointManager::instance()->setCollectionFolders( Amarok::config( "Collection" ).readEntry( "Collection Folders", QStringList() ) );
5446 query( "ALTER TABLE statistics ADD deviceid INTEGER;" );
5448 //FIXME: (max) i know this is bad but its fast
5449 QStringList oldURLs = query( "SELECT url FROM statistics;" );
5450 //it might be necessary to use batch updates to improve speed
5451 debug() << "Updating " << oldURLs.count() << " rows in statistics";
5452 oldForeach( oldURLs )
5454 bool exists = QFile::exists( *it );
5455 int deviceid = exists ? MountPointManager::instance()->getIdForUrl( *it ) : -2;
5456 QString rpath = exists ? MountPointManager::instance()->getRelativePath( deviceid, *it ) : *it;
5457 QString update = QString( "UPDATE statistics SET deviceid = %1, url = '%2' WHERE " )
5458 .arg( deviceid )
5459 .arg( escapeString( rpath ) );
5460 update += QString( "url = '%1';" ).arg( escapeString( *it ) );
5461 query ( update );
5464 if ( prev < 12 )
5466 //re-using old method cause just a slight change to one column...
5467 //if people are upgrading from earlier than 11, just get the new column
5468 //earlier :-)
5469 createStatsTableV10( true );
5470 query( "INSERT INTO statistics_fix_ten SELECT url,deviceid,createdate,"
5471 "accessdate,percentage,rating,playcounter,uniqueid,deleted FROM "
5472 "statistics;" );
5473 dropStatsTableV1();
5474 createStatsTableV10( false );
5475 query( "INSERT INTO statistics SELECT * FROM statistics_fix_ten;" );
5476 query( "UPDATE statistics SET uniqueid=NULL;" );
5478 else if( prev > DATABASE_STATS_VERSION )
5480 error() << "Database statistics version too new for this version of Amarok. Quitting...";
5481 exit( 1 );
5487 void
5488 CollectionDB::updatePersistentTables()
5490 QString PersistentVersion = adminValue( "Database Persistent Tables Version" );
5491 if ( PersistentVersion.isEmpty() )
5493 /* persistent tables didn't have a version on 1.3X and older, but let's be nice and try to
5494 copy/keep the good information instead of just deleting the tables */
5495 debug() << "Detected old schema for tables with important data. Amarok will convert the tables, ignore any \"table already exists\" errors.";
5496 createPersistentTables();
5497 /* Copy lyrics */
5498 debug() << "Trying to get lyrics from old db schema.";
5500 QStringList lyrics = query( "SELECT url, lyrics FROM tags where tags.lyrics IS NOT NULL;" );
5502 for( int i = 0; i < lyrics.count(); i += 2 )
5503 setLyrics( lyrics[i], lyrics[i+1] );
5505 debug() << "Building podcast tables";
5506 createPodcastTables();
5508 else if ( PersistentVersion == "1" || PersistentVersion == "2" )
5510 createPersistentTables(); /* From 1 to 2 nothing changed. There was just a bug on the code, and
5511 on some cases the table wouldn't be created.
5512 From 2 to 3, lyrics were made plain text, instead of HTML */
5513 debug() << "Converting Lyrics to Plain Text.";
5515 QStringList lyrics = query( "SELECT url, lyrics FROM lyrics;" );
5517 for( int i=0; i < lyrics.count(); i += 2 )
5518 setLyrics( lyrics[i], lyrics[i+1] );
5520 debug() << "Building podcast tables";
5521 createPodcastTables();
5523 else if ( PersistentVersion.toInt() < 4 )
5525 debug() << "Building podcast tables";
5526 createPodcastTables();
5528 else
5530 if ( PersistentVersion.toInt() < 5 )
5532 debug() << "Updating podcast tables";
5533 query( "ALTER TABLE podcastchannels ADD image " + textColumnType() + ';' );
5534 query( "ALTER TABLE podcastepisodes ADD localurl " + textColumnType() + ';' );
5535 query( "ALTER TABLE podcastepisodes ADD subtitle " + textColumnType() + ';' );
5536 query( "ALTER TABLE podcastepisodes ADD size INTEGER;" );
5537 query( "ALTER TABLE podcastepisodes DROP comment;" );
5538 query( "ALTER TABLE podcastepisodes ADD comment " + longTextColumnType() + ';' );
5539 query( "CREATE INDEX localurl_podepisode ON podcastepisodes( localurl );" );
5541 if ( PersistentVersion.toInt() < 6 )
5543 debug() << "Updating podcast tables";
5544 query( "ALTER TABLE podcastchannels ADD image " + textColumnType() + ';' );
5545 query( "ALTER TABLE podcastepisodes ADD subtitle " + textColumnType() + ';' );
5546 query( "ALTER TABLE podcastepisodes ADD size INTEGER;" );
5547 query( "ALTER TABLE podcastepisodes DROP comment;" );
5548 query( "ALTER TABLE podcastepisodes ADD comment " + longTextColumnType() + ';' );
5550 if ( PersistentVersion.toInt() < 11 )
5552 debug() << "This is used to handle problems from uniqueid changeover and should not do anything";
5554 if ( PersistentVersion.toInt() < 12 )
5556 debug() << "Adding playlists table...";
5557 createPersistentTablesV12();
5559 if ( PersistentVersion.toInt() < 13 )
5561 //Update for Dynamic Collection:
5562 query( "ALTER TABLE lyrics ADD deviceid INTEGER;" );
5564 //FIXME: (max) i know this is bad but its fast
5565 QStringList oldURLs = query( "SELECT url FROM lyrics;" );
5566 //it might be necessary to use batch updates to improve speed
5567 debug() << "Updating " << oldURLs.count() << " rows in lyrics";
5568 oldForeach( oldURLs )
5570 int deviceid = MountPointManager::instance()->getIdForUrl( *it );
5571 QString rpath = MountPointManager::instance()->getRelativePath( deviceid, *it );
5572 QString update = QString( "UPDATE lyrics SET deviceid = %1, url = '%2' WHERE " )
5573 .arg( deviceid )
5574 .arg( escapeString( rpath ) );
5575 update += QString( "url = '%1';" ).arg( escapeString( *it ) );
5576 query ( update );
5579 if ( PersistentVersion.toInt() < 15 )
5581 createPersistentTablesV14( true );
5582 query( "INSERT INTO amazon_fix SELECT asin,locale,filename,refetchdate FROM amazon;" );
5583 query( "INSERT INTO lyrics_fix SELECT url,deviceid,lyrics FROM lyrics;" );
5584 query( "INSERT INTO playlists_fix SELECT playlist,url,tracknum FROM playlists;" );
5585 dropPersistentTablesV14();
5586 createPersistentTablesV14( false );
5587 query( "INSERT INTO amazon SELECT * FROM amazon_fix;" );
5588 query( "INSERT INTO lyrics SELECT * FROM lyrics_fix;" );
5589 query( "INSERT INTO playlists SELECT * FROM playlists_fix;" );
5591 if ( PersistentVersion.toInt() < 17 )
5593 //drop old labels and label tables, they were never used anyway and just confuse things
5594 query( "DROP TABLE label;" );
5595 query( "DROP TABLE labels;" );
5596 query( "DROP TABLE tags_labels;" );
5597 //update for label support
5598 QString labelsAutoIncrement = "";
5599 if ( getDbConnectionType() == DbConnection::postgresql )
5601 query( QString( "CREATE SEQUENCE labels_seq;" ) );
5603 labelsAutoIncrement = QString("DEFAULT nextval('labels_seq')");
5605 else if ( getDbConnectionType() == DbConnection::mysql )
5607 labelsAutoIncrement = "AUTO_INCREMENT";
5610 query( QString( "CREATE TABLE labels ("
5611 "id INTEGER PRIMARY KEY " + labelsAutoIncrement + ", "
5612 "name " + textColumnType() + ", "
5613 "type INTEGER);" ) );
5615 query( QString( "CREATE TABLE tags_labels ("
5616 "deviceid INTEGER,"
5617 "url " + exactTextColumnType() + ", "
5618 "uniqueid " + exactTextColumnType(32) + ", " //m:n relationship, DO NOT MAKE UNIQUE!
5619 "labelid INTEGER REFERENCES labels( id ) ON DELETE CASCADE );" ) );
5621 query( "CREATE UNIQUE INDEX labels_name ON labels( name, type );" );
5622 query( "CREATE INDEX tags_labels_uniqueid ON tags_labels( uniqueid );" ); //m:n relationship, DO NOT MAKE UNIQUE!
5623 query( "CREATE INDEX tags_labels_url ON tags_labels( url, deviceid );" ); //m:n relationship, DO NOT MAKE UNIQUE!
5625 if ( PersistentVersion.toInt() < 18 )
5627 query( "ALTER TABLE lyrics ADD uniqueid " + exactTextColumnType(32) + ';' );
5628 query( "CREATE INDEX lyrics_uniqueid ON lyrics( uniqueid );" );
5630 if ( PersistentVersion.toInt() < 19 )
5632 query( "CREATE INDEX tags_labels_labelid ON tags_labels( labelid );" ); //m:n relationship, DO NOT MAKE UNIQUE!
5634 //Up to date. Keep this number \/ in sync!
5635 if ( PersistentVersion.toInt() > 19 || PersistentVersion.toInt() < 0 )
5637 //Something is horribly wrong
5638 if ( adminValue( "Database Persistent Tables Version" ).toInt() != DATABASE_PERSISTENT_TABLES_VERSION )
5640 error() << "There is a bug in Amarok: instead of destroying your valuable"
5641 << " database tables, I'm quitting";
5642 exit( 1 );
5644 debug() << "Rebuilding persistent tables database!";
5645 dropPersistentTables();
5646 createPersistentTables();
5652 void
5653 CollectionDB::updatePodcastTables()
5655 QString PodcastVersion = adminValue( "Database Podcast Tables Version" );
5656 if ( PodcastVersion.toInt() < 2 )
5658 createPodcastTablesV2( true );
5659 query( "INSERT INTO podcastchannels_fix SELECT url,title,weblink,image,comment,"
5660 "copyright,parent,directory,autoscan,fetchtype,autotransfer,haspurge,"
5661 "purgecount FROM podcastchannels;" );
5662 query( "INSERT INTO podcastepisodes_fix SELECT id,url,localurl,parent,guid,title,"
5663 "subtitle,composer,comment,filetype,createdate,length,size,isNew FROM "
5664 "podcastepisodes;" );
5665 query( "INSERT INTO podcastfolders_fix SELECT id,name,parent,isOpen FROM podcastfolders;" );
5666 dropPodcastTablesV2();
5667 createPodcastTablesV2( false );
5668 query( "INSERT INTO podcastchannels SELECT * FROM podcastchannels_fix;" );
5669 query( "INSERT INTO podcastepisodes SELECT * FROM podcastepisodes_fix;" );
5670 query( "INSERT INTO podcastfolders SELECT * FROM podcastfolders_fix;" );
5673 //Keep this number in sync \/
5674 if ( PodcastVersion.toInt() > 2 )
5676 error() << "Something is very wrong with the Podcast Tables. Aborting";
5677 exit( 1 );
5678 dropPodcastTables();
5679 createPodcastTables();
5683 void
5684 CollectionDB::vacuum()
5686 if ( DbConnection::sqlite == getDbConnectionType() ||
5687 DbConnection::postgresql == getDbConnectionType() )
5689 //Clean up DB and free unused space.
5690 debug() << "Running VACUUM";
5691 query( "VACUUM;" );
5695 void
5696 CollectionDB::destroy()
5698 //do we need or want to delete the actual connection objects as well as clearing them from the QMap?
5699 //or does QMap's clear function delete them?
5700 //this situation is not at all likely to happen often, so leaving them might be okay to prevent a
5701 //thread from having its connection torn out from under it...not likely, but possible
5702 //and leaving it should not end up eating much memory at all
5704 connectionMutex->lock();
5706 threadConnections->clear();
5707 delete m_dbConfig;
5709 connectionMutex->unlock();
5712 void
5713 CollectionDB::scanModifiedDirs()
5715 /* if ( !m_scanInProgress
5716 //PORT 2.0 && ( !CollectionView::instance() || !CollectionView::instance()->isOrganizingFiles() )
5717 // && ( !MediaBrowser::instance() || !MediaBrowser::instance()->isTranscoding() )
5720 //we check if a job is pending because we don't want to abort incremental collection readings
5721 //Port 2.0
5722 if ( !ThreadManager::instance()->isJobPending( "CollectionScanner" ) && PlaylistBrowser::instance() )
5724 m_scanInProgress = true;
5725 m_rescanRequired = false;
5726 emit scanStarted();
5728 ThreadManager::instance()->onlyOneJob( new ScanController( this, true ) );
5731 else
5732 m_rescanRequired = true;*/
5736 void
5737 CollectionDB::customEvent( QEvent *e )
5739 /*if ( e->type() == (int)ScanController::JobFinishedEventType )
5741 ScanController* s = static_cast<ScanController*>( e );
5742 m_scanInProgress = false;
5744 if ( s->isIncremental() )
5746 debug() << "JobFinishedEventType from Incremental ScanController received.\n";
5747 emit scanDone( s->hasChanged() );
5749 // check if something changed while we were scanning. in this case we should
5750 // rescan again, now.
5751 if ( m_rescanRequired )
5752 QTimer::singleShot( 0, CollectionDB::instance(), SLOT( scanMonitor() ) );
5754 else
5756 debug() << "JobFinishedEventType from ScanController received.\n";
5757 emit scanDone( s->wasSuccessful() );
5763 QString
5764 CollectionDB::loadHashFile( const QByteArray& hash, uint width )
5766 //debug() << "loadHashFile: " << hash << " - " << width;
5768 QString full = tagCoverDir().filePath( hash );
5770 if ( width == 0 ) {
5771 if ( QFileInfo( full ).isReadable() ) {
5772 //debug() << "loadHashFile: fullsize: " << full;
5773 return full;
5775 } else {
5776 if ( width == 1 ) width = AmarokConfig::coverPreviewSize();
5777 QByteArray widthKey = makeWidthKey( width );
5779 QString path = cacheCoverDir().filePath( widthKey + hash );
5780 if ( QFileInfo( path ).isReadable() ) {
5781 //debug() << "loadHashFile: scaled: " << path;
5782 return path;
5783 } else if ( QFileInfo( full ).isReadable() ) {
5784 //debug() << "loadHashFile: scaling: " << full;
5785 QImage image( full );
5786 if ( image.scaled( width, width, Qt::KeepAspectRatio, Qt::SmoothTransformation ).save( path, "PNG" ) ) {
5787 //debug() << "loadHashFile: scaled: " << path;
5788 return path;
5792 return QString();
5796 bool
5797 CollectionDB::extractEmbeddedImage( const MetaBundle &trackInformation, QByteArray& hash )
5799 //debug() << "extractEmbeddedImage: " << hash << " - " << trackInformation.url().path();
5801 MetaBundle::EmbeddedImageList images;
5802 trackInformation.embeddedImages( images );
5803 oldForeachType ( MetaBundle::EmbeddedImageList, images ) {
5804 if ( hash.isEmpty() || (*it).hash() == hash ) {
5805 if ( (*it).save( tagCoverDir() ) ) {
5806 //debug() << "extractEmbeddedImage: saved to " << tagCoverDir().path();
5807 hash = (*it).hash();
5808 return true;
5812 return false;
5815 QStringList
5816 CollectionDB::getLabels( const QString &url, const uint type )
5818 int deviceid = MountPointManager::instance()->getIdForUrl( url );
5819 QString rpath = MountPointManager::instance()->getRelativePath( deviceid, url );
5820 return query( QString( "SELECT labels.name FROM labels "
5821 "LEFT JOIN tags_labels ON labels.id = tags_labels.labelid "
5822 "WHERE labels.type = %1 AND tags_labels.deviceid = %2 AND tags_labels.url = '%3';" )
5823 .arg( type ).arg( deviceid ).arg( escapeString( rpath ) ) );
5826 void
5827 CollectionDB::cleanLabels()
5829 DEBUG_BLOCK
5830 QStringList labelIds = query( "select labels.id "
5831 "from labels left join tags_labels on labels.id = tags_labels.labelid "
5832 "where tags_labels.labelid is NULL;" );
5833 if ( !labelIds.isEmpty() )
5835 QString ids;
5836 oldForeach( labelIds )
5838 if ( !ids.isEmpty() )
5839 ids += ',';
5840 ids += *it;
5842 query( QString( "DELETE FROM labels "
5843 "WHERE labels.id IN ( %1 );" )
5844 .arg( ids ) );
5848 void
5849 CollectionDB::setLabels( const QString &url, const QStringList &labels, const QString &uid, const uint type )
5851 DEBUG_BLOCK
5852 int deviceid = MountPointManager::instance()->getIdForUrl( url );
5853 QString rpath = escapeString( MountPointManager::instance()->getRelativePath( deviceid, url ) );
5854 QStringList labelIds = query( QString( "SELECT id FROM labels WHERE type = %1;" ).arg( type ) );
5855 QString ids;
5856 if ( !labelIds.isEmpty() )
5858 oldForeach( labelIds )
5860 if ( !ids.isEmpty() )
5861 ids += ',';
5862 ids += *it;
5864 //TODO: max: add uniqueid handling
5865 query( QString( "DELETE FROM tags_labels "
5866 "WHERE tags_labels.labelid IN (%1) AND tags_labels.deviceid = %2 AND tags_labels.url = '%3';" )
5867 .arg( ids, QString::number(deviceid), rpath ) );
5870 oldForeach( labels )
5872 int id = query( QString( "SELECT id FROM labels WHERE type = %1 AND name = '%2';" )
5873 .arg( type ).arg( escapeString( *it ) ) ).first().toInt();
5874 if ( !id )
5876 id = insert( QString( "INSERT INTO labels( name, type ) VALUES ( '%2', %1 );" )
5877 .arg( type ).arg( escapeString( *it ) ), "labels" );
5879 insert( QString( "INSERT INTO tags_labels( labelid, deviceid, url, uniqueid ) VALUES ( %1, %2, '%3', '%4' );" )
5880 .arg( QString::number(id), QString::number(deviceid), rpath, escapeString( uid ) ), 0 );
5883 emit labelsChanged( url );
5886 void
5887 CollectionDB::removeLabels( const QString &url, const QStringList &labels, const uint type )
5889 DEBUG_BLOCK
5890 int deviceid = MountPointManager::instance()->getIdForUrl( url );
5891 QString rpath = escapeString( MountPointManager::instance()->getRelativePath( deviceid, url ) );
5892 QString sql = QString( "DELETE FROM tags_labels "
5893 "FROM tags_labels AS t LEFT JOIN labels AS l ON t.labelid = l.id "
5894 "WHERE l.type = %1 AND t.deviceid = %2 AND t.url = '%3' AND ( 0" )
5895 .arg( type ).arg( deviceid ).arg( rpath );
5896 oldForeach( labels )
5898 sql += QString( " OR l.name = '%1'" ).arg( escapeString( *it ) );
5900 sql += ");";
5901 query( sql );
5903 emit labelsChanged( url );
5906 bool
5907 CollectionDB::addLabel( const QString &url, const QString &label, const QString &uid, const uint type )
5909 DEBUG_BLOCK
5910 int deviceid = MountPointManager::instance()->getIdForUrl( url );
5911 QString rpath = escapeString( MountPointManager::instance()->getRelativePath( deviceid, url ) );
5913 int id = query( QString( "SELECT id FROM labels WHERE type = %1 AND name = '%2';" )
5914 .arg( type ).arg( escapeString( label ) ) ).first().toInt();
5915 bool labelAlreadyExists = id > 0;
5916 if ( !id )
5918 id = insert( QString( "INSERT INTO labels( name, type ) VALUES ( '%2', %1 );" )
5919 .arg( type ).arg( escapeString( label ) ), "labels" );
5921 if ( labelAlreadyExists )
5923 //we can return if the link between the tags row and the labels row already exists
5924 int count = query( QString( "SELECT COUNT(*) FROM tags_labels WHERE labelid = %1 AND deviceid = %2 AND url = '%3';" )
5925 .arg( id ).arg( deviceid ).arg( rpath ) ).first().toInt();
5926 if ( count )
5927 return false;
5929 insert( QString( "INSERT INTO tags_labels( labelid, deviceid, url, uniqueid ) VALUES ( %1, %2, '%3', '%4' );" )
5930 .arg( QString::number(id), QString::number(deviceid), rpath, escapeString( uid ) ), "tags_labels" );
5932 emit labelsChanged( url );
5933 return true;
5936 QStringList
5937 CollectionDB::favoriteLabels( int type, int count )
5939 return query( QString( "SELECT labels.name, count( tags_labels.labelid ) "
5940 "FROM labels LEFT JOIN tags_labels ON labels.id = tags_labels.labelid "
5941 "WHERE labels.type = %1 GROUP BY labels.name "
5942 "ORDER BY count(tags_labels.labelid) DESC LIMIT %2;" )
5943 .arg( QString::number( type ), QString::number( count ) ) );
5946 QDir
5947 CollectionDB::largeCoverDir() //static
5949 return QDir( Amarok::saveLocation( "albumcovers/large/" ) );
5953 QDir
5954 CollectionDB::tagCoverDir() //static
5956 return QDir( Amarok::saveLocation( "albumcovers/tagcover/" ) );
5960 QDir
5961 CollectionDB::cacheCoverDir() //static
5963 return QDir( Amarok::saveLocation( "albumcovers/cache/" ) );
5967 //////////////////////////////////////////////////////////////////////////////////////////
5968 // CLASS DbConnection
5969 //////////////////////////////////////////////////////////////////////////////////////////
5971 DbConnection::DbConnection()
5972 : m_initialized( false )
5976 //////////////////////////////////////////////////////////////////////////////////////////
5977 // CLASS SqliteConnection
5978 //////////////////////////////////////////////////////////////////////////////////////////
5980 SqliteConnection::SqliteConnection( const SqliteConfig* config )
5981 : DbConnection()
5984 DEBUG_BLOCK
5986 const QByteArray path = QFile::encodeName( config->dbFile() );
5988 // Open database file and check for correctness
5989 QFile file( path );
5990 if ( file.open( QIODevice::ReadOnly ) )
5992 QByteArray format;
5993 format = file.readLine( 50 );
5994 if ( !format.startsWith( "SQLite format 3" ) )
5996 warning() << "Database versions incompatible. Removing and rebuilding database.\n";
5998 else if ( sqlite3_open( path, &m_db ) != SQLITE_OK )
6000 warning() << "Database file corrupt. Removing and rebuilding database.\n";
6001 sqlite3_close( m_db );
6003 else
6004 m_initialized = true;
6007 if ( !m_initialized )
6009 // Remove old db file; create new
6010 QFile::remove( path );
6011 if ( sqlite3_open( path, &m_db ) == SQLITE_OK )
6013 m_initialized = true;
6016 if ( m_initialized )
6018 if( sqlite3_create_function(m_db, "rand", 0, SQLITE_UTF8, NULL, sqlite_rand, NULL, NULL) != SQLITE_OK )
6019 m_initialized = false;
6020 if( sqlite3_create_function(m_db, "power", 2, SQLITE_UTF8, NULL, sqlite_power, NULL, NULL) != SQLITE_OK )
6021 m_initialized = false;
6022 if ( sqlite3_create_function(m_db, "like", 2, SQLITE_UTF8, NULL, sqlite_like_new, NULL, NULL) != SQLITE_OK )
6023 m_initialized = false;
6024 if ( sqlite3_create_function(m_db, "like", 3, SQLITE_UTF8, NULL, sqlite_like_new, NULL, NULL) != SQLITE_OK )
6025 m_initialized = false;
6028 //optimization for speeding up SQLite
6029 query( "PRAGMA default_synchronous = OFF;" );
6033 SqliteConnection::~SqliteConnection()
6035 if ( m_db ) sqlite3_close( m_db );
6039 QStringList SqliteConnection::query( const QString& statement, bool /*suppressDebug*/ )
6042 QStringList values;
6043 int error;
6044 int rc = 0;
6045 const char* tail;
6046 sqlite3_stmt* stmt;
6047 int busyCnt = 0;
6048 int retryCnt = 0;
6050 do {
6051 //compile SQL program to virtual machine, reattempting if busy
6052 do {
6053 if ( busyCnt )
6055 ::usleep( 100000 ); // Sleep 100 msec
6056 debug() << "sqlite3_prepare: BUSY counter: " << busyCnt;
6058 error = sqlite3_prepare( m_db, statement.toUtf8(), -1, &stmt, &tail );
6060 while ( SQLITE_BUSY==error && busyCnt++ < 120 );
6062 if ( error != SQLITE_OK )
6064 if ( SQLITE_BUSY==error )
6065 Debug::error() << "Gave up waiting for lock to clear";
6066 Debug::error() << " sqlite3_compile error:";
6067 Debug::error() << sqlite3_errmsg( m_db );
6068 Debug::error() << "on query: " << statement;
6069 values = QStringList();
6070 break;
6072 else
6074 busyCnt = 0;
6075 int number = sqlite3_column_count( stmt );
6076 //execute virtual machine by iterating over rows
6077 while ( true )
6079 error = sqlite3_step( stmt );
6081 if ( error == SQLITE_BUSY )
6083 if ( busyCnt++ > 120 ) {
6084 Debug::error() << "Busy-counter has reached maximum. Aborting this sql statement!\n";
6085 break;
6087 ::usleep( 100000 ); // Sleep 100 msec
6088 debug() << "sqlite3_step: BUSY counter: " << busyCnt;
6089 continue;
6091 if ( error == SQLITE_MISUSE )
6092 debug() << "sqlite3_step: MISUSE";
6093 if ( error == SQLITE_DONE || error == SQLITE_ERROR )
6094 break;
6096 //iterate over columns
6097 for ( int i = 0; i < number; i++ )
6099 values << QString::fromUtf8( reinterpret_cast<const char*>( sqlite3_column_text( stmt, i ) ) );
6102 //deallocate vm resources
6103 rc = sqlite3_finalize( stmt );
6105 if ( error != SQLITE_DONE && rc != SQLITE_SCHEMA )
6107 Debug::error() << "sqlite_step error.\n";
6108 Debug::error() << sqlite3_errmsg( m_db );
6109 Debug::error() << "on query: " << statement;
6110 values = QStringList();
6112 if ( rc == SQLITE_SCHEMA )
6114 retryCnt++;
6115 debug() << "SQLITE_SCHEMA error occurred on query: " << statement;
6116 if ( retryCnt < 10 )
6117 debug() << "Retrying now.";
6118 else
6120 Debug::error() << "Retry-Count has reached maximum. Aborting this SQL statement!";
6121 Debug::error() << "SQL statement: " << statement;
6122 values = QStringList();
6127 while ( rc == SQLITE_SCHEMA && retryCnt < 10 );
6129 return values;
6133 int SqliteConnection::insert( const QString& statement, const QString& /* table */ )
6135 int error;
6136 int rc = 0;
6137 const char* tail;
6138 sqlite3_stmt* stmt;
6139 int busyCnt = 0;
6140 int retryCnt = 0;
6142 do {
6143 //compile SQL program to virtual machine, reattempting if busy
6144 do {
6145 if ( busyCnt )
6147 ::usleep( 100000 ); // Sleep 100 msec
6148 debug() << "sqlite3_prepare: BUSY counter: " << busyCnt;
6150 error = sqlite3_prepare( m_db, statement.toUtf8(), -1, &stmt, &tail );
6152 while ( SQLITE_BUSY==error && busyCnt++ < 120 );
6154 if ( error != SQLITE_OK )
6156 if ( SQLITE_BUSY==error )
6157 Debug::error() << "Gave up waiting for lock to clear";
6158 Debug::error() << " sqlite3_compile error:";
6159 Debug::error() << sqlite3_errmsg( m_db );
6160 Debug::error() << "on insert: " << statement;
6161 break;
6163 else
6165 busyCnt = 0;
6166 //execute virtual machine by iterating over rows
6167 while ( true )
6169 error = sqlite3_step( stmt );
6171 if ( error == SQLITE_BUSY )
6173 if ( busyCnt++ > 120 ) {
6174 Debug::error() << "Busy-counter has reached maximum. Aborting this sql statement!\n";
6175 break;
6177 ::usleep( 100000 ); // Sleep 100 msec
6178 debug() << "sqlite3_step: BUSY counter: " << busyCnt;
6180 if ( error == SQLITE_MISUSE )
6181 debug() << "sqlite3_step: MISUSE";
6182 if ( error == SQLITE_DONE || error == SQLITE_ERROR )
6183 break;
6185 //deallocate vm resources
6186 rc = sqlite3_finalize( stmt );
6188 if ( error != SQLITE_DONE && rc != SQLITE_SCHEMA)
6190 Debug::error() << "sqlite_step error.\n";
6191 Debug::error() << sqlite3_errmsg( m_db );
6192 Debug::error() << "on insert: " << statement;
6194 if ( rc == SQLITE_SCHEMA )
6196 retryCnt++;
6197 debug() << "SQLITE_SCHEMA error occurred on insert: " << statement;
6198 if ( retryCnt < 10 )
6199 debug() << "Retrying now.";
6200 else
6202 Debug::error() << "Retry-Count has reached maximum. Aborting this SQL insert!";
6203 Debug::error() << "SQL statement: " << statement;
6208 while ( SQLITE_SCHEMA == rc && retryCnt < 10 );
6209 return sqlite3_last_insert_rowid( m_db );
6213 // this implements a RAND() function compatible with the MySQL RAND() (0-param-form without seed)
6214 void SqliteConnection::sqlite_rand(sqlite3_context *context, int /*argc*/, sqlite3_value ** /*argv*/)
6216 sqlite3_result_double( context, static_cast<double>(KRandom::random()) / (RAND_MAX+1.0) );
6219 // this implements a POWER() function compatible with the MySQL POWER()
6220 void SqliteConnection::sqlite_power(sqlite3_context *context, int argc, sqlite3_value **argv)
6222 Q_ASSERT( argc==2 );
6223 if( sqlite3_value_type(argv[0])==SQLITE_NULL || sqlite3_value_type(argv[1])==SQLITE_NULL ) {
6224 sqlite3_result_null(context);
6225 return;
6227 double a = sqlite3_value_double(argv[0]);
6228 double b = sqlite3_value_double(argv[1]);
6229 sqlite3_result_double( context, pow(a,b) );
6232 // this implements a LIKE() function that overrides the default string comparison function
6233 // Reason: default function is case-sensitive for utf8 strings (BUG: 116458, ...)
6234 void SqliteConnection::sqlite_like_new( sqlite3_context *context, int argc, sqlite3_value **argv )
6237 const unsigned char *zA = sqlite3_value_text( argv[0] );
6238 const unsigned char *zB = sqlite3_value_text( argv[1] );
6240 QString pattern = QString::fromUtf8( (const char*)zA );
6241 QString text = QString::fromUtf8( (const char*)zB );
6243 int begin = pattern.startsWith( "%" ), end = pattern.endsWith( "%" );
6244 if (begin)
6245 pattern = pattern.right( pattern.length() - 1 );
6246 if (end)
6247 pattern = pattern.left( pattern.length() - 1 );
6249 if( argc == 3 ) // The function is given an escape character. In likeCondition() it defaults to '/'
6250 pattern.replace( "/%", "%" ).replace( "/_", "_" ).replace( "//", "/" );
6252 int result = 0;
6253 if ( begin && end ) result = ( text.indexOf( pattern, 0, Qt::CaseInsensitive ) != -1);
6254 else if ( begin ) result = text.endsWith( pattern, Qt::CaseInsensitive );
6255 else if ( end ) result = text.startsWith( pattern, Qt::CaseInsensitive );
6256 else result = ( text.toLower() == pattern.toLower() );
6258 sqlite3_result_int( context, result );
6261 //////////////////////////////////////////////////////////////////////////////////////////
6262 // CLASS MySqlConnection
6263 //////////////////////////////////////////////////////////////////////////////////////////
6265 #ifdef USE_MYSQL
6266 MySqlConnection::MySqlConnection( const MySqlConfig* config )
6267 : DbConnection()
6268 , m_connected( false )
6270 DEBUG_BLOCK
6272 debug() ;
6273 m_db = mysql_init(NULL);
6274 if (m_db)
6276 // if ( config->username().isEmpty() )
6277 // pApp->slotConfigAmarok("MySql");
6279 if ( mysql_real_connect( m_db, config->host().toLatin1(),
6280 config->username().toLatin1(),
6281 config->password().toLatin1(),
6282 config->database().toLatin1(),
6283 config->port(),
6284 NULL, CLIENT_COMPRESS ) )
6286 m_initialized = true;
6288 #if MYSQL_VERSION_ID >= 40113
6289 // now set the right charset for the connection
6290 QStringList my_qslist = query( "SHOW VARIABLES LIKE 'character_set_database'" );
6291 if( !my_qslist.isEmpty() && !mysql_set_character_set( m_db, const_cast<char *>( my_qslist[1].toLatin1() ) ) )
6292 //charset was updated
6293 debug() << "Connection Charset is now: " << my_qslist[1].toLatin1();
6294 else
6295 error() << "Failed to set database charset\n";
6296 #endif
6298 m_db->reconnect = 1; //setting reconnect flag for newer mysqld
6299 m_connected = true;
6301 else
6304 if ( mysql_real_connect(
6305 m_db,
6306 config->host().toLatin1(),
6307 config->username().toLatin1(),
6308 config->password().toLatin1(),
6309 NULL,
6310 config->port(),
6311 NULL, CLIENT_COMPRESS))
6313 if ( mysql_query(m_db,
6314 QString( "CREATE DATABASE " + config->database() ).toLatin1() ) )
6315 { m_connected = true; m_initialized = true; }
6316 else
6317 { setMysqlError(); }
6319 else
6320 setMysqlError();
6324 else
6325 error() << "Failed to allocate/initialize MySql struct\n";
6329 MySqlConnection::~MySqlConnection()
6331 if ( m_db ) mysql_close( m_db );
6335 QStringList MySqlConnection::query( const QString& statement, bool suppressDebug )
6337 QStringList values;
6339 if ( !mysql_query( m_db, statement.toUtf8() ) )
6341 MYSQL_RES* result;
6342 if ( ( result = mysql_use_result( m_db ) ) )
6344 int number = mysql_field_count( m_db );
6345 MYSQL_ROW row;
6346 while ( ( row = mysql_fetch_row( result ) ) )
6348 for ( int i = 0; i < number; i++ )
6350 values << QString::fromUtf8( (const char*)row[i] );
6354 else
6356 if ( mysql_field_count( m_db ) != 0 )
6358 if ( !suppressDebug )
6359 debug() << "MYSQL QUERY FAILED: " << mysql_error( m_db ) << "\n" << "FAILED QUERY: " << statement << "\n";
6360 values = QStringList();
6363 mysql_free_result( result );
6365 else
6367 if ( !suppressDebug )
6368 debug() << "MYSQL QUERY FAILED: " << mysql_error( m_db ) << "\n" << "FAILED QUERY: " << statement << "\n";
6369 values = QStringList();
6372 return values;
6376 int MySqlConnection::insert( const QString& statement, const QString& /* table */ )
6378 mysql_query( m_db, statement.toUtf8() );
6379 if ( mysql_errno( m_db ) )
6381 debug() << "MYSQL INSERT FAILED: " << mysql_error( m_db ) << "\n" << "FAILED INSERT: " << statement;
6383 return mysql_insert_id( m_db );
6387 void
6388 MySqlConnection::setMysqlError()
6390 m_error = i18n("MySQL reported the following error:<br>") + mysql_error(m_db)
6391 + i18n("<p>You can configure MySQL in the Collection section under Settings->Configure Amarok</p>");
6393 #endif
6396 //////////////////////////////////////////////////////////////////////////////////////////
6397 // CLASS PostgresqlConnection
6398 //////////////////////////////////////////////////////////////////////////////////////////
6400 #ifdef USE_POSTGRESQL
6401 PostgresqlConnection::PostgresqlConnection( const PostgresqlConfig* config )
6402 : DbConnection()
6403 , m_connected( false )
6405 QString conninfo;
6406 debug() ;
6408 // if ( config->username().isEmpty() )
6409 // pApp->slotConfigAmarok("Postgresql");
6411 conninfo = "host='" + config->host() +
6412 "' port=" + QString::number( config->port() ) +
6413 " dbname='" + config->database() +
6414 "' user='" + config->username() +
6415 "' password='" + config->password() + '\'';
6417 m_db = PQconnectdb( conninfo.toUtf8() );
6419 if (!m_db)
6421 debug() << "POSTGRESQL CONNECT FAILED: " << PQerrorMessage( m_db ) << "\n";
6422 error() << "Failed to allocate/initialize Postgresql struct\n";
6423 setPostgresqlError();
6424 return;
6427 if (PQstatus(m_db) != CONNECTION_OK)
6429 debug() << "POSTGRESQL CONNECT FAILED: " << PQerrorMessage( m_db ) << "\n";
6430 error() << "Failed to allocate/initialize Postgresql struct\n";
6431 setPostgresqlError();
6432 PQfinish(m_db);
6433 m_db = NULL;
6434 return;
6437 m_initialized = true;
6438 m_connected = true;
6442 PostgresqlConnection::~PostgresqlConnection()
6444 if ( m_db ) PQfinish( m_db );
6448 QStringList PostgresqlConnection::query( const QString& statement, bool suppressDebug )
6450 QStringList values;
6451 PGresult* result;
6452 ExecStatusType status;
6454 result = PQexec(m_db, statement.toUtf8());
6455 if (result == NULL)
6457 if ( !suppressDebug )
6458 debug() << "POSTGRESQL QUERY FAILED: " << PQerrorMessage( m_db ) << "\n" << "FAILED QUERY: " << statement << "\n";
6459 return values;
6462 status = PQresultStatus(result);
6463 if ((status != PGRES_COMMAND_OK) && (status != PGRES_TUPLES_OK))
6465 if ( !suppressDebug )
6466 debug() << "POSTGRESQL QUERY FAILED: " << PQerrorMessage( m_db ) << "\n" << "FAILED QUERY: " << statement << "\n";
6467 PQclear(result);
6468 return values;
6471 int cols = PQnfields( result );
6472 int rows = PQntuples( result );
6473 QMap<int, bool> discardCols;
6474 for(int col=0; col< cols; col++) {
6475 if (QString(PQfname(result, col)) == QString("__discard") || QString(PQfname(result, col)) == QString("__random"))
6477 discardCols[col] = true;
6481 for(int row=0; row< rows; row++)
6483 for(int col=0; col< cols; col++)
6485 if (discardCols[col]) continue;
6487 values << QString::fromUtf8(PQgetvalue(result, row, col));
6491 PQclear(result);
6493 return values;
6497 int PostgresqlConnection::insert( const QString& statement, const QString& table )
6499 PGresult* result;
6500 ExecStatusType status;
6501 QString curvalSql;
6502 int id;
6504 result = PQexec(m_db, statement.toUtf8());
6505 if (result == NULL)
6507 debug() << "POSTGRESQL INSERT FAILED: " << PQerrorMessage( m_db ) << "\n" << "FAILED SQL: " << statement << "\n";
6508 return 0;
6511 status = PQresultStatus(result);
6512 if (status != PGRES_COMMAND_OK)
6514 debug() << "POSTGRESQL INSERT FAILED: " << PQerrorMessage( m_db ) << "\n" << "FAILED SQL: " << statement << "\n";
6515 PQclear(result);
6516 return 0;
6518 PQclear(result);
6520 if (table == NULL) return 0;
6522 QString _table = table;
6523 if (table.find("_temp") > 0) _table = table.left(table.find("_temp"));
6525 curvalSql = QString("SELECT currval('%1_seq');").arg(_table);
6526 result = PQexec(m_db, curvalSql.toUtf8());
6527 if (result == NULL)
6529 debug() << "POSTGRESQL INSERT FAILED: " << PQerrorMessage( m_db ) << "\n" << "FAILED SQL: " << curvalSql << "\n";
6530 return 0;
6533 status = PQresultStatus(result);
6534 if (status != PGRES_TUPLES_OK)
6536 debug() << "POSTGRESQL INSERT FAILED: " << PQerrorMessage( m_db ) << "\n" << "FAILED SQL: " << curvalSql << "\n";
6537 PQclear(result);
6538 return 0;
6541 if ((PQnfields( result ) != 1) || (PQntuples( result ) != 1))
6543 debug() << "POSTGRESQL INSERT FAILED: " << PQerrorMessage( m_db ) << "\n" << "FAILED SQL: " << curvalSql << "\n";
6544 PQclear(result);
6545 return 0;
6548 id = QString::fromUtf8(PQgetvalue(result, 0, 0)).toInt();
6549 PQclear(result);
6551 return id;
6555 void PostgresqlConnection::setPostgresqlError()
6557 m_error = i18n("Postgresql reported the following error:<br>") + PQerrorMessage(m_db)
6558 + i18n("<p>You can configure Postgresql in the Collection section under Settings->Configure Amarok</p>");
6560 #endif
6564 //////////////////////////////////////////////////////////////////////////////////////////
6565 // CLASS SqliteConfig
6566 //////////////////////////////////////////////////////////////////////////////////////////
6568 SqliteConfig::SqliteConfig( const QString& dbfile )
6569 : m_dbfile( dbfile )
6573 //////////////////////////////////////////////////////////////////////////////////////////
6574 // CLASS MySqlConfig
6575 //////////////////////////////////////////////////////////////////////////////////////////
6577 MySqlConfig::MySqlConfig(
6578 const QString& host,
6579 const int port,
6580 const QString& database,
6581 const QString& username,
6582 const QString& password )
6583 : m_host( host ),
6584 m_port( port ),
6585 m_database( database ),
6586 m_username( username ),
6587 m_password( password )
6591 //////////////////////////////////////////////////////////////////////////////////////////
6592 // CLASS PostgresqlConfig
6593 //////////////////////////////////////////////////////////////////////////////////////////
6595 PostgresqlConfig::PostgresqlConfig(
6596 const QString& host,
6597 const int port,
6598 const QString& database,
6599 const QString& username,
6600 const QString& password )
6601 : m_host( host ),
6602 m_port( port ),
6603 m_database( database ),
6604 m_username( username ),
6605 m_password( password )
6608 #include "collectiondb.moc"