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"
22 #include "amarokconfig.h"
23 #include "config-amarok.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 "playlistloader.h"
33 #include "playlistbrowser.h"
34 #include "podcastbundle.h" //addPodcast
36 #include "querybuilder.h"
38 #include <QTimerEvent>
41 #include <Q3ValueList>
43 #include "scancontroller.h"
44 #include "scriptmanager.h"
45 #include "scrobbler.h"
46 #include "statusbar.h"
47 #include "threadmanager.h"
49 //queries moved to sqlcollection, CollectionDB is still being used by legacy stuff
50 #include "collection/collectionmanager.h"
51 #include "SqlStorage.h"
60 #include <QRegExp> //setHTMLLyrics()
62 #include <QPainter> //createDragPixmap()
66 #include <kcharsets.h> //setHTMLLyrics()
67 #include <kcombobox.h>
69 #include <kdialog.h> //checkDatabase()
71 #include <kinputdialog.h> //setupCoverFetcher()
72 #include <klineedit.h> //setupCoverFetcher()
75 #include <kmessagebox.h>
76 #include <kstandarddirs.h>
78 #include <kio/netaccess.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"
90 #include <mysql/mysql.h>
91 #include <mysql/mysql_version.h>
98 #undef HAVE_INOTIFY // NOTE Disabled for now, due to stability issues
101 #include <linux/inotify.h>
102 #include "inotify/inotify-syscalls.h"
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
;
125 //////////////////////////////////////////////////////////////////////////////////////////
127 //////////////////////////////////////////////////////////////////////////////////////////
129 INotify
* INotify::s_instance
= 0;
131 INotify::INotify( CollectionDB
*parent
, int fd
)
132 : DependentJob( parent
, "INotify" )
145 INotify::watchDir( const QString directory
)
148 int wd
= inotify_add_watch( m_fd
, directory
.toLocal8Bit(), IN_CLOSE_WRITE
| IN_DELETE
| IN_MOVE
|
149 IN_MODIFY
| IN_ATTRIB
);
151 debug() << "Could not add INotify watch for: " << directory
;
168 IdList list
= MountPointManager::instance()->getMountedDeviceIds();
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);" )
180 int deviceid
= (*(++it
)).toInt();
181 QString abspath
= MountPointManager::instance()->getAbsolutePath( deviceid
, rpath
);
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 );
194 len
= read( m_fd
, buf
, BUF_LEN
);
197 debug() << "Read from INotify failed";
204 /* BUF_LEN too small? */
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() ) );
222 // this shouldn't happen
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
;
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 )
257 if ( AmarokConfig::databaseEngine().toInt() == DbConnection::mysql
)
258 m_dbConnType
= DbConnection::mysql
;
261 #ifdef USE_POSTGRESQL
262 if ( AmarokConfig::databaseEngine().toInt() == DbConnection::postgresql
)
263 m_dbConnType
= DbConnection::postgresql
;
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
276 // Remove cached "nocover" images, so that a new version actually gets shown
277 // The asterisk is for also deleting the shadow-caches.
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() ) )
300 m_aftEnabledPersistentTables
<< "lyrics" << "statistics" << "tags_labels";
304 CollectionDB::~CollectionDB()
309 if ( INotify::instance()->fd() >= 0 )
310 close( INotify::instance()->fd() );
318 CollectionDB::exactCondition( const QString
&right
)
320 if ( DbConnection::mysql
== instance()->getDbConnectionType() )
321 return QString( "= BINARY '" + instance()->escapeString( right
) + '\'' );
323 return QString( "= '" + instance()->escapeString( right
) + '\'' );
328 CollectionDB::likeCondition( const QString
&right
, bool anyBegin
, bool anyEnd
)
330 QString escaped
= right
;
331 escaped
.replace( '/', "//" ).replace( '%', "/%" ).replace( '_', "/_" );
332 escaped
= instance()->escapeString( escaped
);
335 if ( DbConnection::postgresql
== instance()->getDbConnectionType() )
336 ret
= " ILIKE "; //case-insensitive according to locale
348 //Use / as the escape character
349 ret
+= " ESCAPE '/' ";
354 //////////////////////////////////////////////////////////////////////////////////////////
356 //////////////////////////////////////////////////////////////////////////////////////////
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
369 // Try to initialize inotify, if not available use the old timer approach.
370 int inotify_fd
= inotify_init();
371 if ( inotify_fd
< 0 )
374 // debug() << "INotify not available, using QTimer!";
375 startTimer( MONITOR_INTERVAL
* 1000 );
380 debug() << "INotify enabled!";
381 ThreadManager::instance()->onlyOneJob( new INotify( this, inotify_fd
) );
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.
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
);
413 CollectionDB::deviceidSelection( const bool 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
+ ')';
430 CollectionDB::URLsFromQuery( const QStringList
&result
) const
435 const int id
= (*it
).toInt();
436 values
<< MountPointManager::instance()->getAbsolutePath( id
, *(++it
) );
442 CollectionDB::URLsFromSqlDrag( const QStringList
&values
) const
445 for( QStringList::const_iterator it
= values
.begin();
449 const QString
&rel
= *it
;
451 int id
= (*it
).toInt();
452 urls
+= KUrl( MountPointManager::instance()->getAbsolutePath( id
, rel
) );
454 i
< QueryBuilder::dragFieldCount
-1 && it
!= values
.end();
463 CollectionDB::isEmpty( )
467 values
= query( "SELECT COUNT( url ) FROM tags LIMIT 1 OFFSET 0;" );
469 return values
.isEmpty() ? true : values
.first() == "0";
474 CollectionDB::isValid( )
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() );
494 CollectionDB::adminValue( QString noption
) {
497 QString( "SELECT value FROM admin WHERE noption = '%1';").arg(noption
)
499 return values
.isEmpty() ? "" : values
.first();
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
) );
513 insert( QString( "INSERT INTO admin (value, noption) values ( '%1', '%2' );" ).arg( value
, noption
),
521 CollectionDB::createTables( const bool temporary
)
526 query( QString( "CREATE %1 TABLE tags%2 ("
527 "url " + exactTextColumnType() + ","
528 "dir " + exactTextColumnType() + ","
529 "createdate INTEGER,"
530 "modifydate INTEGER,"
535 "title " + textColumnType() + ","
537 "comment " + longTextColumnType() + ","
539 "discnumber INTEGER,"
542 "samplerate INTEGER,"
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
)
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";
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
) );
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
) );
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() + ","
626 "artist " + textColumnType() + ","
627 "album " + textColumnType() + ");" )
628 .arg( temporary
? "TEMPORARY" : "" )
629 .arg( temporary
? "_temp" : "" ) );
632 query( QString( "CREATE %1 TABLE embed%2 ("
633 "url " + exactTextColumnType() + ","
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() + ","
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() + ","
652 "uniqueid " + exactTextColumnType(32) + " UNIQUE,"
653 "dir " + exactTextColumnType() + ");" )
654 .arg( temporary
? "TEMPORARY" : "" )
655 .arg( temporary
? "_temp" : "" ) );
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" : "" ) );
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 );" ) );
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 );" );
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";
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";
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" : "" ) );
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;" ) );
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" : "" ) );
825 query( QString( "%1 related_artists;" ).arg( clearCommand
) );
826 //debug() << "Clearing media table";
827 //query( QString( "%1 media;" ).arg( clearCommand ) );
833 CollectionDB::copyTempTables( )
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
)
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
)
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
)
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
)
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
)
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
);
894 CollectionDB::prepareTempTables()
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 );
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 );" );
933 CollectionDB::createStatsTable()
935 // create music statistics database
936 query( QString( "CREATE TABLE statistics ("
937 "url " + exactTextColumnType() + ","
939 "createdate INTEGER,"
940 "accessdate INTEGER,"
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.
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,"
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
974 CollectionDB::createStatsTableV10( bool temp
)
976 // create music statistics database
977 query( QString( "CREATE %1 TABLE statistics%2 ("
978 "url " + exactTextColumnType() + ","
980 "createdate INTEGER,"
981 "accessdate INTEGER,"
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" : "" ) );
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 );" );
1003 CollectionDB::dropStatsTable()
1005 query( "DROP TABLE statistics;" );
1009 CollectionDB::dropStatsTableV1()
1011 query( "DROP TABLE statistics;" );
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() + ", "
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 ("
1056 "url " + exactTextColumnType() + ", "
1057 "uniqueid " + exactTextColumnType(32) + ", " //m:n relationship, DO NOT MAKE UNIQUE!
1058 "labelid INTEGER REFERENCES labels( id ) ON DELETE CASCADE );" ) );
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 );" );
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() + ", "
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
) );
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 );" );
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() + ","
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() + ","
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 );" );
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() + ","
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() + ","
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
) );
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 );" );
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;" );
1270 CollectionDB::dropPersistentTablesV14()
1272 query( "DROP TABLE amazon;" );
1273 query( "DROP TABLE lyrics;" );
1274 query( "DROP TABLE label;" );
1275 query( "DROP TABLE playlists;" );
1279 CollectionDB::dropPodcastTables()
1281 query( "DROP TABLE podcastchannels;" );
1282 query( "DROP TABLE podcastepisodes;" );
1283 query( "DROP TABLE podcastfolders;" );
1287 CollectionDB::dropPodcastTablesV2()
1289 query( "DROP TABLE podcastchannels;" );
1290 query( "DROP TABLE podcastepisodes;" );
1291 query( "DROP TABLE podcastfolders;" );
1295 CollectionDB::dropDevicesTable()
1297 query( "DROP TABLE devices;" );
1301 CollectionDB::artistID( QString value
, bool autocreate
, const bool temporary
, bool exact
/* = true */ )
1304 if ( m_validArtistCache
&& m_cacheArtist
[(int)temporary
] == value
)
1305 return m_cacheArtistID
[(int)temporary
];
1309 id
= IDFromExactValue( "artist", value
, autocreate
, temporary
).toUInt();
1311 id
= IDFromValue( "artist", value
, autocreate
, temporary
);
1314 m_cacheArtist
[(int)temporary
] = value
;
1315 m_cacheArtistID
[(int)temporary
] = id
;
1316 m_validArtistCache
= 1;
1323 CollectionDB::artistValue( uint id
)
1326 if ( m_cacheArtistID
[0] == id
)
1327 return m_cacheArtist
[0];
1329 QString value
= valueFromID( "artist", id
);
1332 m_cacheArtist
[0] = value
;
1333 m_cacheArtistID
[0] = id
;
1340 CollectionDB::composerID( QString value
, bool autocreate
, const bool temporary
, bool exact
/* = true */ )
1343 if ( m_validComposerCache
&& m_cacheComposer
[(int)temporary
] == value
)
1344 return m_cacheComposerID
[(int)temporary
];
1348 id
= IDFromExactValue( "composer", value
, autocreate
, temporary
).toUInt();
1350 id
= IDFromValue( "composer", value
, autocreate
, temporary
);
1353 m_cacheComposer
[(int)temporary
] = value
;
1354 m_cacheComposerID
[(int)temporary
] = id
;
1355 m_validComposerCache
= 1;
1362 CollectionDB::composerValue( uint id
)
1365 if ( m_cacheComposerID
[0] == id
)
1366 return m_cacheComposer
[0];
1368 QString value
= valueFromID( "composer", id
);
1371 m_cacheComposer
[0] = value
;
1372 m_cacheComposerID
[0] = id
;
1379 CollectionDB::albumID( QString value
, bool autocreate
, const bool temporary
, bool exact
/* = true */ )
1382 if ( m_validAlbumCache
&& m_cacheAlbum
[(int)temporary
] == value
)
1383 return m_cacheAlbumID
[(int)temporary
];
1387 id
= IDFromExactValue( "album", value
, autocreate
, temporary
).toUInt();
1389 id
= IDFromValue( "album", value
, autocreate
, temporary
);
1392 m_cacheAlbum
[(int)temporary
] = value
;
1393 m_cacheAlbumID
[(int)temporary
] = id
;
1394 m_validAlbumCache
= 1;
1400 CollectionDB::albumValue( uint id
)
1403 if ( m_cacheAlbumID
[0] == id
)
1404 return m_cacheAlbum
[0];
1406 QString value
= valueFromID( "album", id
);
1409 m_cacheAlbum
[0] = value
;
1410 m_cacheAlbumID
[0] = id
;
1416 CollectionDB::genreID( QString value
, bool autocreate
, const bool temporary
, bool exact
/* = true */ )
1419 IDFromExactValue( "genre", value
, autocreate
, temporary
).toUInt() :
1420 IDFromValue( "genre", value
, autocreate
, temporary
);
1424 CollectionDB::genreValue( uint id
)
1426 return valueFromID( "genre", id
);
1431 CollectionDB::yearID( QString value
, bool autocreate
, const bool temporary
, bool exact
/* = true */ )
1434 IDFromExactValue( "year", value
, autocreate
, temporary
).toUInt() :
1435 IDFromValue( "year", value
, autocreate
, temporary
);
1440 CollectionDB::yearValue( uint id
)
1442 return valueFromID( "year", id
);
1447 CollectionDB::IDFromValue( QString name
, QString value
, bool autocreate
, const bool temporary
)
1450 name
.append( "_temp" );
1451 // what the hell is the reason for this?
1455 QStringList values
=
1457 "SELECT id, name FROM %1 WHERE name %2;" )
1459 .arg( CollectionDB::likeCondition( value
) ) );
1461 //check if item exists. if not, should we autocreate it?
1463 if ( values
.isEmpty() && autocreate
)
1465 id
= insert( QString( "INSERT INTO %1 ( name ) VALUES ( '%2' );" )
1467 .arg( CollectionDB::instance()->escapeString( value
) ), name
);
1472 return values
.isEmpty() ? 0 : values
.first().toUInt();
1477 CollectionDB::valueFromID( QString table
, uint id
)
1479 QStringList values
=
1481 "SELECT name FROM %1 WHERE id=%2;" )
1486 return values
.isEmpty() ? 0 : values
.first();
1491 CollectionDB::albumSongCount( const QString
&artist_id
, const QString
&album_id
)
1493 QStringList values
=
1495 "SELECT COUNT( url ) FROM tags WHERE album = %1 AND artist = %2;" )
1497 .arg( artist_id
) );
1498 return values
.first();
1502 CollectionDB::albumIsCompilation( const QString
&album_id
)
1504 QStringList values
=
1506 "SELECT sampler FROM tags WHERE sampler=%1 AND album=%2" )
1507 .arg( CollectionDB::instance()->boolT() )
1510 return (values
.count() != 0);
1514 CollectionDB::albumTracks( const QString
&artist_id
, const QString
&album_id
)
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 )
1530 for ( QStringList::size_type i
= 0; i
< ret
.size(); i
+= returnValues
)
1539 CollectionDB::albumDiscTracks( const QString
&artist_id
, const QString
&album_id
, const QString
&discNumber
)
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;" )
1547 .arg( discNumber
) );
1551 const int id
= (*it
).toInt();
1552 result
<< MountPointManager::instance()->getAbsolutePath( id
, *(++it
) );
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();
1567 const int id
= (*it
).toInt();
1568 result
<< MountPointManager::instance()->getAbsolutePath( id
, *(++it
) );
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() )
1584 QString sql
= QString( "INSERT INTO images%1 ( path, deviceid, artist, album ) VALUES ( '%3', %2" )
1585 .arg( temporary
? "_temp" : "" )
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
);
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' );" )
1606 .arg( escapeString( rpath
), escapeString( hash
), escapeString( description
) ), NULL
);
1610 CollectionDB::removeOrphanedEmbeddedImages()
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
) ) );
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
);
1630 oldForeach( values
)
1632 KUrl u
= KUrl( *it
);
1636 return createDragPixmap( list
, textOverRide
);
1640 CollectionDB::createDragPixmap( const KUrl::List
&urls
, QString textOverRide
)
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
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 if( PlaylistFile::isPlaylistFile( *it
)
1669 || (*it
).protocol() == "playlist" || (*it
).protocol() == "smartplaylist"
1670 || (*it
).protocol() == "dynamic" )
1674 else if( (*it
).isLocalFile() )
1678 if( covers
>= maxCovers
)
1680 correctAlbumCount
= false;
1684 MetaBundle
mb( *it
);
1689 QString artist
= mb
.artist();
1690 if( mb
.compilation() == MetaBundle::CompilationYes
)
1691 artist
= QString( "Various_AMAROK_Artists" ); // magic key for the albumMap!
1693 if( !albumMap
.contains( artist
+ album
) )
1695 albumMap
[ artist
+ album
] = 1;
1696 QString coverName
= CollectionDB::instance()->albumImage( mb
.artist(), album
, false, coverW
);
1698 if ( !coverName
.endsWith( "@nocover.png" ) )
1699 coverPm
[covers
++].load( coverName
);
1704 MetaBundle
mb( *it
);
1705 if( !albumMap
.contains( mb
.artist() + mb
.album() ) )
1707 albumMap
[ mb
.artist() + mb
.album() ] = 1;
1708 QString coverName
= CollectionDB::instance()->podcastImage( mb
, false, coverW
);
1710 if ( covers
< maxCovers
&& !coverName
.endsWith( "@nocover.png" ) )
1711 coverPm
[covers
++].load( coverName
);
1717 // make better text...
1718 int albums
= albumMap
.count();
1721 if( !textOverRide
.isEmpty() )
1723 text
= textOverRide
;
1725 else if( ( songs
&& remoteUrls
) ||
1726 ( songs
&& playlists
) ||
1727 ( playlists
&& remoteUrls
) )
1729 text
= i18np( "One item", "%1 items", songs
+ remoteUrls
+ playlists
);
1731 else if( songs
> 0 )
1733 if( correctAlbumCount
) {
1734 text
= i18nc( "X songs from X albums", "%2 from %1" );
1735 text
= text
.arg( albums
== 1 && !album
.isEmpty() ? album
: i18np( "one album", "%1 albums",albums
) );
1739 text
= text
.arg( songs
== 1 && !song
.isEmpty() ? song
: i18np( "One song", "%1 songs", songs
) );
1741 else if( playlists
> 0 )
1742 text
= i18np( "One playlist", "%1 playlists", playlists
);
1743 else if ( remoteUrls
> 0 )
1744 text
= i18np( "One remote file", "%1 remote files", remoteUrls
);
1746 text
= i18n( "Unknown item" );
1749 QFontMetrics
fm( font
);
1750 int fontH
= fm
.height() + margin
;
1751 int minWidth
= fm
.width( text
) + margin
*2; //margin either side
1755 // insert "..." cover as first image if appropriate
1756 if ( covers
< albums
)
1758 if ( covers
< maxCovers
) covers
++;
1759 for ( int i
= maxCovers
-1; i
> 0; i
-- )
1760 coverPm
[i
] = coverPm
[i
-1];
1762 QImage
im( KStandardDirs::locate( "data","amarok/images/more_albums.png" ) );
1763 coverPm
[0] = QPixmap::fromImage( im
.scaled( coverW
, coverH
, Qt::KeepAspectRatio
, Qt::SmoothTransformation
) );
1766 pixmapH
= coverPm
[0].height();
1767 pixmapW
= coverPm
[0].width();
1769 // caluclate pixmap height
1771 for ( int i
= 1; i
< covers
; i
++ )
1773 dW
= coverPm
[i
].width() - coverPm
[i
-1].width() + coverSpacing
;
1774 dH
= coverPm
[i
].height() - coverPm
[i
-1].height() + coverSpacing
;
1775 if ( dW
> 0 ) pixmapW
+= dW
;
1776 if ( dH
> 0 ) pixmapH
+= dH
;
1778 pixmapH
+= fontSpacing
+ fontH
;
1780 if ( pixmapW
< minWidth
)
1789 QPixmap
pmdrag( pixmapW
, pixmapH
);
1790 QPixmap
pmtext( pixmapW
, fontH
);
1792 QPalette palette
= QToolTip::palette();
1796 p
.fillRect( 0, 0, pixmapW
, fontH
, QBrush( Qt::black
) ); // border
1797 p
.fillRect( 1, 1, pixmapW
-margin
, fontH
-margin
, palette
.brush( QPalette::Normal
, QColorGroup::Background
) );
1798 p
.setBrush( palette
.color( QPalette::Normal
, QColorGroup::Text
) );
1800 p
.drawText( margin
, fm
.ascent() + 1, text
);
1803 QBitmap
pmtextMask(pixmapW
, fontH
);
1804 pmtextMask
.fill( Qt::color1
);
1806 // when we have found no covers, just display the text message
1809 pmtext
.setMask(pmtextMask
);
1815 for ( int i
= 0; i
< covers
; i
++ )
1816 bitBlt( &pmdrag
, i
* coverSpacing
, i
* coverSpacing
, &coverPm
[i
], 0 );
1818 bitBlt( &pmdrag
, 0, pixmapH
- fontH
, &pmtext
, 0 );
1821 QBitmap
pmdragMask( pmdrag
.size() );
1823 for ( int i
= 0; i
< covers
; i
++ )
1825 QBitmap
coverMask( coverPm
[i
].width(), coverPm
[i
].height() );
1826 coverMask
.fill( Qt::color1
);
1827 bitBlt( &pmdragMask
, i
* coverSpacing
, i
* coverSpacing
, &coverMask
, 0 );
1829 bitBlt( &pmdragMask
, 0, pixmapH
- fontH
, &pmtextMask
, 0 );
1830 pmdrag
.setMask( pmdragMask
);
1836 CollectionDB::fetchImage( const KUrl
& url
, QString
&/*tmpFile*/ )
1838 if ( url
.protocol() != "file" )
1841 KIO::NetAccess::download( url
, tmpFile
, 0 ); //TODO set 0 to the window, though it probably doesn't really matter
1842 return QImage( tmpFile
);
1846 return QImage( url
.path() );
1851 CollectionDB::setAlbumImage( const QString
& artist
, const QString
& album
, const KUrl
& url
)
1854 bool success
= setAlbumImage( artist
, album
, fetchImage(url
, tmpFile
) );
1855 KIO::NetAccess::removeTempFile( tmpFile
); //only removes file if it was created with NetAccess
1861 CollectionDB::setAlbumImage( const QString
& artist
, const QString
& album
, QImage img
, const QString
& amazonUrl
, const QString
& asin
)
1863 //show a wait cursor for the duration
1864 Amarok::OverrideCursor keep
;
1866 const bool isCompilation
= albumIsCompilation( QString::number( albumID( album
, false, false, true ) ) );
1867 const QString artist_
= isCompilation
? "" : artist
;
1869 // remove existing album covers
1870 removeAlbumImage( artist_
, album
);
1872 QByteArray key
= md5sum( artist_
, album
);
1873 newAmazonReloadDate(asin
, AmarokConfig::amazonLocale(), key
);
1874 // Save Amazon product page URL as embedded string, for later retreival
1875 if ( !amazonUrl
.isEmpty() )
1876 img
.setText( "amazon-url", 0, amazonUrl
);
1878 const bool b
= img
.save( largeCoverDir().filePath( key
), "PNG");
1879 emit
coverChanged( artist_
, album
);
1885 CollectionDB::podcastImage( const MetaBundle
&bundle
, const bool withShadow
, uint width
)
1887 PodcastEpisodeBundle peb
;
1888 PodcastChannelBundle pcb
;
1890 KUrl url
= bundle
.url().url();
1892 if( getPodcastEpisodeBundle( url
, &peb
) )
1894 url
= peb
.parent().url();
1897 if( getPodcastChannelBundle( url
, &pcb
) )
1899 if( pcb
.imageURL().isValid() )
1900 return podcastImage( pcb
.imageURL().url(), withShadow
, width
);
1903 return notAvailCover( withShadow
, width
);
1908 CollectionDB::podcastImage( const QString
&remoteURL
, const bool withShadow
, uint width
)
1910 // we aren't going to need a 1x1 size image. this is just a quick hack to be able to show full size images.
1911 // width of 0 == full size
1913 width
= AmarokConfig::coverPreviewSize();
1915 QString s
= findAmazonImage( "Podcast", remoteURL
, width
);
1919 s
= notAvailCover( withShadow
, width
);
1921 const KUrl url
= KUrl( remoteURL
);
1922 if( url
.isValid() ) //KIO crashes with invalid URLs
1924 KIO::Job
*job
= KIO::storedGet( url
, false, false );
1925 m_podcastImageJobs
[job
] = remoteURL
;
1926 connect( job
, SIGNAL( result( KIO::Job
* ) ), SLOT( podcastImageResult( KIO::Job
* ) ) );
1931 s
= makeShadowedImage( s
);
1937 CollectionDB::podcastImageResult( KIO::Job
*gjob
)
1939 QString url
= m_podcastImageJobs
[gjob
];
1940 m_podcastImageJobs
.remove( gjob
);
1942 KIO::StoredTransferJob
*job
= dynamic_cast<KIO::StoredTransferJob
*>( gjob
);
1945 debug() << "connected to wrong job type";
1951 debug() << "job finished with error";
1955 if( job
->isErrorPage() )
1957 debug() << "error page";
1961 QImage image
= QImage::fromData( job
->data() );
1962 if( !image
.isNull() )
1965 url
= job
->url().url();
1967 QByteArray key
= md5sum( "Podcast", url
);
1968 if( image
.save( largeCoverDir().filePath( key
), "PNG") )
1969 emit
imageFetched( url
);
1975 CollectionDB::albumImage( const QString
&artist
, const QString
&album
, bool withShadow
, uint width
, bool* embedded
)
1978 // we aren't going to need a 1x1 size image. this is just a quick hack to be able to show full size images.
1979 // width of 0 == full size
1981 width
= AmarokConfig::coverPreviewSize();
1985 s
= findAmazonImage( artist
, album
, width
);
1988 s
= findAmazonImage( "", album
, width
); // handle compilations
1991 s
= findDirectoryImage( artist
, album
, width
);
1995 s
= findEmbeddedImage( artist
, album
, width
);
1996 if( embedded
&& !s
.isEmpty() )
2001 s
= notAvailCover( withShadow
, width
);
2004 s
= makeShadowedImage( s
);
2011 CollectionDB::albumImage( const uint artist_id
, const uint album_id
, bool withShadow
, uint width
, bool* embedded
)
2013 return albumImage( artistValue( artist_id
), albumValue( album_id
), withShadow
, width
, embedded
);
2018 CollectionDB::albumImage( const MetaBundle
&trackInformation
, bool withShadow
, uint width
, bool* embedded
)
2022 width
= AmarokConfig::coverPreviewSize();
2024 QString album
= trackInformation
.album();
2025 QString artist
= trackInformation
.artist();
2027 // this art is per track, so should check for it first
2028 s
= findMetaBundleImage( trackInformation
, width
);
2030 *embedded
= !s
.isEmpty();
2033 s
= findAmazonImage( artist
, album
, width
);
2035 s
= findAmazonImage( "", album
, width
); // handle compilations
2037 s
= findDirectoryImage( artist
, album
, width
);
2039 s
= notAvailCover( withShadow
, width
);
2041 s
= makeShadowedImage( s
);
2047 CollectionDB::makeShadowedImage( const QString
& albumImage
, bool cache
)
2049 const QImage
original( albumImage
);
2051 if( original
.hasAlphaChannel() )
2054 const QFileInfo
fileInfo( albumImage
);
2055 const uint shadowSize
= static_cast<uint
>( original
.width() / 100.0 * 6.0 );
2056 const QString cacheFile
= fileInfo
.fileName() + "@shadow";
2059 if ( !cache
&& cacheCoverDir().exists( cacheFile
) )
2060 return cacheCoverDir().filePath( cacheFile
);
2062 const QString folder
= Amarok::saveLocation( "covershadow-cache/" );
2063 const QString file
= QString( "shadow_albumcover%1x%2.png" ).arg( original
.width() + shadowSize
).arg( original
.height() + shadowSize
);
2064 if ( QFile::exists( folder
+ file
) )
2065 shadow
.load( folder
+ file
);
2067 shadow
.load( KStandardDirs::locate( "data", "amarok/images/shadow_albumcover.png" ) );
2068 shadow
= shadow
.scaled( original
.width() + shadowSize
, original
.height() + shadowSize
, Qt::IgnoreAspectRatio
, Qt::SmoothTransformation
);
2069 shadow
.save( folder
+ file
, "PNG" );
2072 QImage
target( shadow
);
2073 bitBlt( &target
, 0, 0, &original
);
2076 target
.save( cacheCoverDir().filePath( cacheFile
), "PNG" );
2077 return cacheCoverDir().filePath( cacheFile
);
2080 target
.save( albumImage
, "PNG" );
2087 CollectionDB::findAmazonImage( const QString
&artist
, const QString
&album
, uint width
)
2089 QByteArray widthKey
= makeWidthKey( width
);
2091 if ( artist
.isEmpty() && album
.isEmpty() )
2094 QByteArray key
= md5sum( artist
, album
);
2096 // check cache for existing cover
2097 if ( cacheCoverDir().exists( widthKey
+ key
) )
2098 return cacheCoverDir().filePath( widthKey
+ key
);
2100 // we need to create a scaled version of this cover
2101 QDir imageDir
= largeCoverDir();
2102 if ( imageDir
.exists( key
) )
2106 QImage
img( imageDir
.filePath( key
) );
2107 img
.scaled( width
, width
, Qt::KeepAspectRatio
, Qt::SmoothTransformation
).save( cacheCoverDir().filePath( widthKey
+ key
), "PNG" );
2109 return cacheCoverDir().filePath( widthKey
+ key
);
2112 return imageDir
.filePath( key
);
2120 CollectionDB::findDirectoryImage( const QString
& artist
, const QString
& album
, uint width
)
2123 width
= AmarokConfig::coverPreviewSize();
2124 QByteArray widthKey
= makeWidthKey( width
);
2125 if ( album
.isEmpty() )
2128 IdList list
= MountPointManager::instance()->getMountedDeviceIds();
2130 oldForeachType( IdList
, list
)
2132 if ( !deviceIds
.isEmpty() ) deviceIds
= deviceIds
+ ",";
2133 deviceIds
+= QString::number(*it
);
2137 if ( artist
== i18n( "Various Artists" ) || artist
.isEmpty() )
2139 rs
= query( QString(
2140 "SELECT images.deviceid,images.path FROM images, artist, tags "
2141 "WHERE images.artist = artist.name "
2142 "AND artist.id = tags.artist "
2143 "AND tags.sampler = %1 "
2144 "AND images.album %2 "
2145 "AND images.deviceid IN (%3) " )
2147 .arg( CollectionDB::likeCondition( album
) )
2148 .arg( deviceIds
) );
2152 rs
= query( QString(
2153 "SELECT images.deviceid,images.path FROM images WHERE artist %1 AND album %2 AND deviceid IN (%3) ORDER BY path;" )
2154 .arg( CollectionDB::likeCondition( artist
) )
2155 .arg( CollectionDB::likeCondition( album
) )
2156 .arg( deviceIds
) );
2158 QStringList values
= URLsFromQuery( rs
);
2159 if ( !values
.isEmpty() )
2161 QString
image( values
.first() );
2163 uint maxmatches
= 0;
2164 QRegExp
iTunesArt( "^AlbumArt_.*Large" );
2165 for ( int i
= 0; i
< values
.count(); i
++ )
2167 matches
= values
[i
].count( "front", Qt::CaseInsensitive
) + values
[i
].count( "cover", Qt::CaseInsensitive
) + values
[i
].count( "folder", Qt::CaseInsensitive
) + values
[i
].count( iTunesArt
);
2168 if ( matches
> maxmatches
)
2171 maxmatches
= matches
;
2175 QByteArray key
= md5sum( artist
, album
, image
);
2179 QString path
= cacheCoverDir().filePath( widthKey
+ key
);
2180 if ( !QFile::exists( path
) )
2182 QImage
img( image
);
2183 img
.scaled( width
, width
, Qt::KeepAspectRatio
, Qt::SmoothTransformation
).save( path
, "PNG" );
2195 CollectionDB::findEmbeddedImage( const QString
& artist
, const QString
& album
, uint width
)
2197 // In the case of multiple embedded images, we arbitrarily choose one from the newest file
2198 // could potentially select multiple images within a file based on description, although a
2199 // lot of tagging software doesn't fill in that field, so we just get whatever the DB
2200 // happens to return for us
2202 if ( artist
== i18n("Various Artists") || artist
.isEmpty() ) {
2203 // VAs need special handling to not match on artist name but instead check for sampler flag
2204 rs
= query( QString(
2205 "SELECT embed.hash, embed.deviceid, embed.url FROM "
2206 "tags INNER JOIN embed ON tags.url = embed.url "
2207 "INNER JOIN album ON tags.album = album.id "
2209 "album.name = '%1' "
2210 "AND tags.sampler = %2 "
2211 "ORDER BY modifydate DESC LIMIT 1;" )
2212 .arg( escapeString( album
) )
2215 rs
= query( QString(
2216 "SELECT embed.hash, embed.deviceid, embed.url FROM "
2217 "tags INNER JOIN embed ON tags.url = embed.url "
2218 "INNER JOIN artist ON tags.artist = artist.id "
2219 "INNER JOIN album ON tags.album = album.id "
2221 "artist.name = '%1' "
2222 "AND album.name = '%2' "
2223 "ORDER BY modifydate DESC LIMIT 1;" )
2224 .arg( escapeString( artist
) )
2225 .arg( escapeString( album
) ) );
2228 QStringList values
= QStringList();
2229 if ( rs
.count() == 3 ) {
2230 values
+= rs
.first();
2231 values
+= MountPointManager::instance()->getAbsolutePath( rs
[1].toInt(), rs
[2] );
2234 if ( values
.count() == 2 ) {
2235 QByteArray hash
= values
.first().toUtf8();
2236 QString result
= loadHashFile( hash
, width
);
2237 if ( result
.isEmpty() ) {
2238 // need to get original from file first
2239 MetaBundle
mb( KUrl( values
.last() ) );
2240 if ( extractEmbeddedImage( mb
, hash
) ) {
2241 // try again, as should be possible now
2242 result
= loadHashFile( hash
, width
);
2252 CollectionDB::findMetaBundleImage( const MetaBundle
& trackInformation
, uint width
)
2254 int deviceid
= MountPointManager::instance()->getIdForUrl( trackInformation
.url() );
2255 QString rpath
= MountPointManager::instance()->getRelativePath( deviceid
, trackInformation
.url().path() );
2256 QStringList values
=
2258 "SELECT embed.hash FROM tags LEFT JOIN embed ON tags.url = embed.url "
2259 " AND tags.deviceid = embed.deviceid WHERE tags.url = '%2' AND tags.deviceid = %1 ORDER BY hash DESC LIMIT 1;" )
2260 .arg( deviceid
).arg( escapeString( rpath
) ) );
2262 if ( values
.empty() || !values
.first().isEmpty() ) {
2265 if( !values
.empty() ) { // file in collection, so we know the hash
2266 hash
= values
.first().toUtf8();
2267 result
= loadHashFile( hash
, width
);
2269 if ( result
.isEmpty() ) {
2270 // need to get original from file first
2271 if ( extractEmbeddedImage( trackInformation
, hash
) ) {
2272 // try again, as should be possible now
2273 result
= loadHashFile( hash
, width
);
2283 CollectionDB::makeWidthKey( uint width
)
2285 return QString::number( width
).toLocal8Bit() + '@';
2290 CollectionDB::removeAlbumImage( const QString
&artist
, const QString
&album
)
2294 QByteArray widthKey
= "*@";
2295 QByteArray key
= md5sum( artist
, album
);
2296 query( "DELETE FROM amazon WHERE filename='" + key
+ '\'' );
2298 // remove scaled versions of images (and add the asterisk for the shadow-caches)
2300 filter
<< widthKey
+ key
+ '*';
2301 QStringList scaledList
= cacheCoverDir().entryList( filter
);
2302 if ( scaledList
.count() > 0 )
2303 for ( int i
= 0; i
< scaledList
.count(); i
++ )
2304 QFile::remove( cacheCoverDir().filePath( scaledList
[ i
] ) );
2306 bool deleted
= false;
2307 // remove large, original image
2308 if ( largeCoverDir().exists( key
) && QFile::remove( largeCoverDir().filePath( key
) ) )
2311 QString hardImage
= findDirectoryImage( artist
, album
);
2312 debug() << "hardImage: " << hardImage
;
2314 if( !hardImage
.isEmpty() )
2316 int id
= MountPointManager::instance()->getIdForUrl( hardImage
);
2317 QString rpath
= MountPointManager::instance()->getRelativePath( id
, hardImage
);
2318 query( "DELETE FROM images WHERE path='" + escapeString( hardImage
) + "' AND deviceid = " + QString::number( id
) + ';' );
2324 emit
coverRemoved( artist
, album
);
2333 CollectionDB::removeAlbumImage( const uint artist_id
, const uint album_id
)
2335 return removeAlbumImage( artistValue( artist_id
), albumValue( album_id
) );
2340 CollectionDB::notAvailCover( const bool withShadow
, int width
)
2343 width
= AmarokConfig::coverPreviewSize();
2344 QString widthKey
= QString::number( width
) + '@';
2347 if( cacheCoverDir().exists( widthKey
+ "nocover.png" ) )
2348 s
= cacheCoverDir().filePath( widthKey
+ "nocover.png" );
2351 m_noCover
.scaled( width
, width
, Qt::KeepAspectRatio
, Qt::SmoothTransformation
).save( cacheCoverDir().filePath( widthKey
+ "nocover.png" ), "PNG" );
2352 s
= cacheCoverDir().filePath( widthKey
+ "nocover.png" );
2356 s
= makeShadowedImage( s
);
2363 CollectionDB::artistList( bool withUnknowns
, bool withCompilations
)
2366 qb
.addReturnValue( QueryBuilder::tabArtist
, QueryBuilder::valName
);
2368 if ( !withUnknowns
)
2369 qb
.excludeMatch( QueryBuilder::tabArtist
, i18n( "Unknown" ) );
2370 if ( !withCompilations
)
2371 qb
.setOptions( QueryBuilder::optNoCompilations
);
2373 qb
.groupBy( QueryBuilder::tabArtist
, QueryBuilder::valName
);
2374 qb
.setOptions( QueryBuilder::optShowAll
);
2375 qb
.sortBy( QueryBuilder::tabArtist
, QueryBuilder::valName
);
2381 CollectionDB::composerList( bool withUnknowns
, bool withCompilations
)
2385 qb
.addReturnValue( QueryBuilder::tabComposer
, QueryBuilder::valName
);
2387 if ( !withUnknowns
)
2388 qb
.excludeMatch( QueryBuilder::tabComposer
, i18n( "Unknown" ) );
2389 if ( !withCompilations
)
2390 qb
.setOptions( QueryBuilder::optNoCompilations
);
2392 qb
.groupBy( QueryBuilder::tabComposer
, QueryBuilder::valName
);
2393 qb
.setOptions( QueryBuilder::optShowAll
);
2394 qb
.sortBy( QueryBuilder::tabComposer
, QueryBuilder::valName
);
2400 CollectionDB::albumList( bool withUnknowns
, bool withCompilations
)
2403 qb
.addReturnValue( QueryBuilder::tabAlbum
, QueryBuilder::valName
);
2405 if ( !withUnknowns
)
2406 qb
.excludeMatch( QueryBuilder::tabAlbum
, i18n( "Unknown" ) );
2407 if ( !withCompilations
)
2408 qb
.setOptions( QueryBuilder::optNoCompilations
);
2410 qb
.groupBy( QueryBuilder::tabAlbum
, QueryBuilder::valName
);
2411 qb
.setOptions( QueryBuilder::optShowAll
);
2412 qb
.sortBy( QueryBuilder::tabAlbum
, QueryBuilder::valName
);
2418 CollectionDB::genreList( bool withUnknowns
, bool withCompilations
)
2421 qb
.addReturnValue( QueryBuilder::tabGenre
, QueryBuilder::valName
);
2423 //Only report genres that currently have at least one song
2424 qb
.addFilter( QueryBuilder::tabSong
, "" );
2426 if ( !withUnknowns
)
2427 qb
.excludeMatch( QueryBuilder::tabGenre
, i18n( "Unknown" ) );
2428 if ( !withCompilations
)
2429 qb
.setOptions( QueryBuilder::optNoCompilations
);
2431 qb
.groupBy( QueryBuilder::tabGenre
, QueryBuilder::valName
);
2432 qb
.setOptions( QueryBuilder::optShowAll
);
2433 qb
.sortBy( QueryBuilder::tabGenre
, QueryBuilder::valName
);
2439 CollectionDB::yearList( bool withUnknowns
, bool withCompilations
)
2442 qb
.addReturnValue( QueryBuilder::tabYear
, QueryBuilder::valName
);
2444 if ( !withUnknowns
)
2445 qb
.excludeMatch( QueryBuilder::tabYear
, i18n( "Unknown" ) );
2446 if ( !withCompilations
)
2447 qb
.setOptions( QueryBuilder::optNoCompilations
);
2449 qb
.groupBy( QueryBuilder::tabYear
, QueryBuilder::valName
);
2450 qb
.setOptions( QueryBuilder::optShowAll
);
2451 qb
.sortBy( QueryBuilder::tabYear
, QueryBuilder::valName
);
2456 CollectionDB::labelList()
2459 qb
.addReturnValue( QueryBuilder::tabLabels
, QueryBuilder::valName
);
2460 qb
.groupBy( QueryBuilder::tabLabels
, QueryBuilder::valName
);
2461 qb
.setOptions( QueryBuilder::optShowAll
);
2462 qb
.sortBy( QueryBuilder::tabLabels
, QueryBuilder::valName
);
2467 CollectionDB::albumListOfArtist( const QString
&artist
, bool withUnknown
, bool withCompilations
)
2469 if (getDbConnectionType() == DbConnection::postgresql
)
2471 return query( "SELECT DISTINCT album.name, lower( album.name ) AS __discard FROM tags, album, artist WHERE "
2472 "tags.album = album.id AND tags.artist = artist.id "
2473 "AND lower(artist.name) = lower('" + escapeString( artist
) + "') " +
2474 ( withUnknown
? QString() : "AND album.name <> '' " ) +
2475 ( withCompilations
? QString() : "AND tags.sampler = " + boolF() ) + deviceidSelection() +
2476 " ORDER BY lower( album.name );" );
2480 return query( "SELECT DISTINCT album.name FROM tags, album, artist WHERE "
2481 "tags.album = album.id AND tags.artist = artist.id "
2482 "AND lower(artist.name) = lower('" + escapeString( artist
) + "') " +
2483 ( withUnknown
? QString() : "AND album.name <> '' " ) +
2484 ( withCompilations
? QString() : "AND tags.sampler = " + boolF() ) + deviceidSelection() +
2485 " ORDER BY lower( album.name );" );
2491 CollectionDB::artistAlbumList( bool withUnknown
, bool withCompilations
)
2493 if (getDbConnectionType() == DbConnection::postgresql
)
2495 return query( "SELECT DISTINCT artist.name, album.name, lower( album.name ) AS __discard FROM tags, album, artist WHERE "
2496 "tags.album = album.id AND tags.artist = artist.id " +
2497 ( withUnknown
? QString() : "AND album.name <> '' AND artist.name <> '' " ) +
2498 ( withCompilations
? QString() : "AND tags.sampler = " + boolF() ) + deviceidSelection() +
2499 " ORDER BY lower( album.name );" );
2503 return query( "SELECT DISTINCT artist.name, album.name FROM tags, album, artist WHERE "
2504 "tags.album = album.id AND tags.artist = artist.id " +
2505 ( withUnknown
? QString() : "AND album.name <> '' AND artist.name <> '' " ) +
2506 ( withCompilations
? QString() : "AND tags.sampler = " + boolF() ) + deviceidSelection() +
2507 " ORDER BY lower( album.name );" );
2512 CollectionDB::addPodcastChannel( const PodcastChannelBundle
&pcb
, const bool &replace
)
2516 command
= "REPLACE INTO podcastchannels "
2517 "( url, title, weblink, image, comment, copyright, parent, directory"
2518 ", autoscan, fetchtype, autotransfer, haspurge, purgecount ) "
2521 command
= "INSERT INTO podcastchannels "
2522 "( url, title, weblink, image, comment, copyright, parent, directory"
2523 ", autoscan, fetchtype, autotransfer, haspurge, purgecount ) "
2527 QString title
= pcb
.title();
2528 KUrl link
= pcb
.link();
2529 KUrl image
= pcb
.imageURL();
2530 QString description
= pcb
.description();
2531 QString copyright
= pcb
.copyright();
2533 if( title
.isEmpty() )
2534 title
= pcb
.url().prettyUrl();
2536 command
+= '\'' + escapeString( pcb
.url().url() ) + "',";
2537 command
+= ( title
.isEmpty() ? "NULL" : '\'' + escapeString( title
) + '\'' ) + ',';
2538 command
+= ( link
.isEmpty() ? "NULL" : '\'' + escapeString( link
.url() ) + '\'' ) + ',';
2539 command
+= ( image
.isEmpty() ? "NULL" : '\'' + escapeString( image
.url() ) + '\'' ) + ',';
2540 command
+= ( description
.isEmpty() ? "NULL" : '\'' + escapeString( description
) + '\'' ) + ',';
2541 command
+= ( copyright
.isEmpty() ? "NULL" : '\'' + escapeString( copyright
) + '\'' ) + ',';
2542 command
+= QString::number( pcb
.parentId() ) + ",'";
2543 command
+= escapeString( pcb
.saveLocation() ) + "',";
2544 command
+= pcb
.autoscan() ? boolT() + ',' : boolF() + ',';
2545 command
+= QString::number( pcb
.fetchType() ) + ',';
2546 command
+= pcb
.autotransfer() ? boolT() + ',' : boolF() + ',';
2547 command
+= pcb
.hasPurge() ? boolT() + ',' : boolF() + ',';
2548 command
+= QString::number( pcb
.purgeCount() ) + ");";
2550 //FIXME: currently there's no way to check if an INSERT query failed or not - always return true atm.
2551 // Now it might be possible as insert returns the rowid.
2552 insert( command
, NULL
);
2557 CollectionDB::addPodcastEpisode( const PodcastEpisodeBundle
&episode
, const int idToUpdate
)
2562 command
= "REPLACE INTO podcastepisodes "
2563 "( id, url, localurl, parent, title, subtitle, composer, comment, filetype, createdate, guid, length, size, isNew ) "
2566 command
= "INSERT INTO podcastepisodes "
2567 "( url, localurl, parent, title, subtitle, composer, comment, filetype, createdate, guid, length, size, isNew ) "
2571 QString localurl
= episode
.localUrl().url();
2572 QString title
= episode
.title();
2573 QString subtitle
= episode
.subtitle();
2574 QString author
= episode
.author();
2575 QString description
= episode
.description();
2576 QString type
= episode
.type();
2577 QString date
= episode
.date();
2578 QString guid
= episode
.guid();
2579 int duration
= episode
.duration();
2580 uint size
= episode
.size();
2582 if( title
.isEmpty() )
2583 title
= episode
.url().prettyUrl();
2586 command
+= QString::number( idToUpdate
) + ',';
2588 command
+= '\'' + escapeString( episode
.url().url() ) + "',";
2589 command
+= ( localurl
.isEmpty() ? "NULL" : '\'' + escapeString( localurl
) + '\'' ) + ',';
2590 command
+= '\'' + escapeString( episode
.parent().url()) + "',";
2591 command
+= ( title
.isEmpty() ? "NULL" : '\'' + escapeString( title
) + '\'' ) + ',';
2592 command
+= ( subtitle
.isEmpty() ? "NULL" : '\'' + escapeString( subtitle
) + '\'' ) + ',';
2593 command
+= ( author
.isEmpty() ? "NULL" : '\'' + escapeString( author
) + '\'' ) + ',';
2594 command
+= ( description
.isEmpty() ? "NULL" : '\'' + escapeString( description
) + '\'' ) + ',';
2595 command
+= ( type
.isEmpty() ? "NULL" : '\'' + escapeString( type
) + '\'' ) + ',';
2596 command
+= ( date
.isEmpty() ? "NULL" : '\'' + escapeString( date
) + '\'' ) + ',';
2597 command
+= ( guid
.isEmpty() ? "NULL" : '\'' + escapeString( guid
) + '\'' ) + ',';
2598 command
+= QString::number( duration
) + ',';
2599 command
+= QString::number( size
) + ',';
2600 command
+= episode
.isNew() ? boolT() + " );" : boolF() + " );";
2602 insert( command
, NULL
);
2604 if( idToUpdate
) return idToUpdate
;
2605 //This is a bit of a hack. We have just inserted an item, so it is going to be the one with the
2606 //highest id. Change this if threaded insertions are used in the future.
2607 QStringList values
= query( QString("SELECT id FROM podcastepisodes WHERE url='%1' ORDER BY id DESC;")
2608 .arg( escapeString( episode
.url().url() ) ) );
2609 if( values
.isEmpty() ) return -1;
2611 return values
[0].toInt();
2614 Q3ValueList
<PodcastChannelBundle
>
2615 CollectionDB::getPodcastChannels()
2617 QString command
= "SELECT url, title, weblink, image, comment, copyright, parent, directory "
2618 ", autoscan, fetchtype, autotransfer, haspurge, purgecount FROM podcastchannels;";
2620 QStringList values
= query( command
);
2621 Q3ValueList
<PodcastChannelBundle
> bundles
;
2623 oldForeach( values
)
2625 PodcastChannelBundle pcb
;
2626 pcb
.setUrl ( KUrl(*it
) );
2627 pcb
.setTitle ( *++it
);
2628 pcb
.setLink ( KUrl(*++it
) );
2629 pcb
.setImageURL ( KUrl(*++it
) );
2630 pcb
.setDescription ( *++it
);
2631 pcb
.setCopyright ( *++it
);
2632 pcb
.setParentId ( (*++it
).toInt() );
2633 pcb
.setSaveLocation( *++it
);
2634 pcb
.setAutoScan ( *++it
== boolT() ? true : false );
2635 pcb
.setFetchType ( (*++it
).toInt() );
2636 pcb
.setAutoTransfer( *++it
== boolT() ? true : false );
2637 pcb
.setPurge ( *++it
== boolT() ? true : false );
2638 pcb
.setPurgeCount ( (*++it
).toInt() );
2640 bundles
.append( pcb
);
2646 Q3ValueList
<PodcastEpisodeBundle
>
2647 CollectionDB::getPodcastEpisodes( const KUrl
&parent
, bool onlyNew
, int limit
)
2649 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 command
+= QString( " AND isNew='%1'" ).arg( boolT() );
2652 command
+= " ) ORDER BY id";
2654 command
+= QString( " DESC LIMIT %1 OFFSET 0" ).arg( limit
);
2657 QStringList values
= query( command
);
2658 Q3ValueList
<PodcastEpisodeBundle
> bundles
;
2660 oldForeach( values
)
2662 PodcastEpisodeBundle peb
;
2663 peb
.setDBId ( (*it
).toInt() );
2664 peb
.setUrl ( KUrl(*++it
) );
2665 if( *++it
!= "NULL" )
2666 peb
.setLocalURL ( KUrl(*it
) );
2667 peb
.setParent ( KUrl(*++it
) );
2668 peb
.setGuid ( *++it
);
2669 peb
.setTitle ( *++it
);
2671 peb
.setSubtitle( *it
);
2672 peb
.setAuthor ( *++it
);
2673 peb
.setDescription ( *++it
);
2674 peb
.setType ( *++it
);
2675 peb
.setDate ( *++it
);
2676 peb
.setDuration ( (*++it
).toInt() );
2680 peb
.setSize ( (*it
).toInt() );
2681 peb
.setNew ( (*++it
) == boolT() ? true : false );
2683 bundles
.append( peb
);
2689 PodcastEpisodeBundle
2690 CollectionDB::getPodcastEpisodeById( int id
)
2692 QString command
= QString( "SELECT url, localurl, parent, guid, title, subtitle, composer, comment, filetype, createdate, length, size, isNew FROM podcastepisodes WHERE id=%1;").arg( id
);
2694 QStringList values
= query( command
);
2695 PodcastEpisodeBundle peb
;
2696 oldForeach( values
)
2699 peb
.setUrl ( KUrl(*it
) );
2700 if( *++it
!= "NULL" )
2701 peb
.setLocalURL( KUrl(*it
) );
2702 peb
.setParent ( KUrl(*++it
) );
2703 peb
.setGuid ( *++it
);
2704 peb
.setTitle ( *++it
);
2705 peb
.setSubtitle ( *++it
);
2706 peb
.setAuthor ( *++it
);
2707 peb
.setDescription ( *++it
);
2708 peb
.setType ( *++it
);
2709 peb
.setDate ( *++it
);
2710 peb
.setDuration ( (*++it
).toInt() );
2714 peb
.setSize ( (*it
).toInt() );
2715 peb
.setNew ( (*++it
) == boolT() ? true : false );
2722 CollectionDB::getPodcastEpisodeBundle( const KUrl
&url
, PodcastEpisodeBundle
*peb
)
2725 if( url
.isLocalFile() )
2727 QStringList values
=
2728 query( QString( "SELECT id FROM podcastepisodes WHERE localurl = '%1';" )
2729 .arg( escapeString( url
.url() ) ) );
2730 if( !values
.isEmpty() )
2731 id
= values
[0].toInt();
2735 QStringList values
=
2736 query( QString( "SELECT id FROM podcastepisodes WHERE url = '%1';" )
2737 .arg( escapeString( url
.url() ) ) );
2738 if( !values
.isEmpty() )
2739 id
= values
[0].toInt();
2744 *peb
= getPodcastEpisodeById( id
);
2752 CollectionDB::getPodcastChannelBundle( const KUrl
&url
, PodcastChannelBundle
*pcb
)
2754 QStringList values
= query( QString(
2755 "SELECT url, title, weblink, image, comment, copyright, parent, directory "
2756 ", autoscan, fetchtype, autotransfer, haspurge, purgecount FROM podcastchannels WHERE url = '%1';"
2757 ).arg( escapeString( url
.url() ) ) );
2759 oldForeach( values
)
2761 pcb
->setUrl ( KUrl(*it
) );
2762 pcb
->setTitle ( *++it
);
2763 pcb
->setLink ( KUrl(*++it
) );
2764 if( *++it
!= "NULL" )
2765 pcb
->setImageURL( KUrl(*it
) );
2766 pcb
->setDescription ( *++it
);
2767 pcb
->setCopyright ( *++it
);
2768 pcb
->setParentId ( (*++it
).toInt() );
2769 pcb
->setSaveLocation( *++it
);
2770 pcb
->setAutoScan ( *++it
== boolT() ? true : false );
2771 pcb
->setFetchType ( (*++it
).toInt() );
2772 pcb
->setAutoTransfer( *++it
== boolT() ? true : false );
2773 pcb
->setPurge ( *++it
== boolT() ? true : false );
2774 pcb
->setPurgeCount ( (*++it
).toInt() );
2777 return !values
.isEmpty();
2780 // return newly created folder id
2782 CollectionDB::addPodcastFolder( const QString
&name
, const int parent_id
, const bool isOpen
)
2784 QString command
= QString( "INSERT INTO podcastfolders ( name, parent, isOpen ) VALUES ('" );
2785 command
+= escapeString( name
) + "',";
2786 command
+= QString::number( parent_id
) + ',';
2787 command
+= isOpen
? boolT() + ");" : boolF() + ");";
2789 insert( command
, NULL
);
2791 command
= QString( "SELECT id FROM podcastfolders WHERE name = '%1' AND parent = '%2';" )
2792 .arg( name
, QString::number(parent_id
) );
2793 QStringList values
= query( command
);
2795 return values
[0].toInt();
2799 CollectionDB::updatePodcastChannel( const PodcastChannelBundle
&b
)
2801 if( getDbConnectionType() == DbConnection::postgresql
)
2803 query( QStringx( "UPDATE podcastchannels SET title='%1', weblink='%2', comment='%3', "
2804 "copyright='%4', parent=%5, directory='%6', autoscan=%7, fetchtype=%8, "
2805 "autotransfer=%9, haspurge=%10, purgecount=%11 WHERE url='%12';" )
2806 .args ( QStringList()
2807 << escapeString( b
.title() )
2808 << escapeString( b
.link().url() )
2809 << escapeString( b
.description() )
2810 << escapeString( b
.copyright() )
2811 << QString::number( b
.parentId() )
2812 << escapeString( b
.saveLocation() )
2813 << ( b
.autoscan() ? boolT() : boolF() )
2814 << QString::number( b
.fetchType() )
2815 << (b
.hasPurge() ? boolT() : boolF() )
2816 << (b
.autotransfer() ? boolT() : boolF() )
2817 << QString::number( b
.purgeCount() )
2818 << escapeString( b
.url().url() )
2823 addPodcastChannel( b
, true ); //replace the already existing row
2828 CollectionDB::updatePodcastEpisode( const int id
, const PodcastEpisodeBundle
&b
)
2830 if( getDbConnectionType() == DbConnection::postgresql
)
2832 query( QStringx( "UPDATE podcastepisodes SET url='%1', localurl='%2', parent='%3', title='%4', subtitle='%5', composer='%6', comment='%7', "
2833 "filetype='%8', createdate='%9', guid='%10', length=%11, size=%12, isNew=%13 WHERE id=%14;" )
2834 .args( QStringList()
2835 << escapeString( b
.url().url() )
2836 << ( b
.localUrl().isValid() ? escapeString( b
.localUrl().url() ) : "NULL" )
2837 << escapeString( b
.parent().url() )
2838 << escapeString( b
.title() )
2839 << escapeString( b
.subtitle() )
2840 << escapeString( b
.author() )
2841 << escapeString( b
.description() )
2842 << escapeString( b
.type() )
2843 << escapeString( b
.date() )
2844 << escapeString( b
.guid() )
2845 << QString::number( b
.duration() )
2846 << escapeString( QString::number( b
.size() ) )
2847 << ( b
.isNew() ? boolT() : boolF() )
2848 << QString::number( id
)
2853 addPodcastEpisode( b
, id
);
2858 CollectionDB::updatePodcastFolder( const int folder_id
, const QString
&name
, const int parent_id
, const bool isOpen
)
2860 if( getDbConnectionType() == DbConnection::postgresql
) {
2861 query( QStringx( "UPDATE podcastfolders SET name='%1', parent=%2, isOpen=%3 WHERE id=%4;" )
2862 .args( QStringList()
2863 << escapeString(name
)
2864 << QString::number(parent_id
)
2865 << ( isOpen
? boolT() : boolF() )
2866 << QString::number(folder_id
)
2871 query( QStringx( "REPLACE INTO podcastfolders ( id, name, parent, isOpen ) "
2872 "VALUES ( %1, '%2', %3, %4 );" )
2873 .args( QStringList()
2874 << QString::number(folder_id
)
2875 << escapeString(name
)
2876 << QString::number(parent_id
)
2877 << ( isOpen
? boolT() : boolF() )
2884 CollectionDB::removePodcastChannel( const KUrl
&url
)
2887 query( QString( "DELETE FROM podcastchannels WHERE url = '%1';" )
2888 .arg( escapeString( url
.url() ) ) );
2889 //remove all children
2890 query( QString( "DELETE FROM podcastepisodes WHERE parent = '%1';" )
2891 .arg( escapeString( url
.url() ) ) );
2895 /// Try not to delete by url, since some podcast feeds have all the same url
2897 CollectionDB::removePodcastEpisode( const int id
)
2899 if( id
< 0 ) return;
2900 query( QString( "DELETE FROM podcastepisodes WHERE id = '%1';" )
2901 .arg( QString::number(id
) ) );
2905 CollectionDB::removePodcastFolder( const int id
)
2907 if( id
< 0 ) return;
2908 query( QString("DELETE FROM podcastfolders WHERE id=%1;")
2909 .arg( QString::number(id
) ) );
2913 CollectionDB::addSong( MetaBundle
* bundle
, const bool incremental
)
2915 if ( !QFileInfo( bundle
->url().path() ).isReadable() ) return false;
2917 QString command
= "INSERT INTO tags_temp "
2918 "( url, dir, deviceid, createdate, modifydate, album, artist, composer, genre, year, title, "
2919 "comment, track, discnumber, bpm, sampler, length, bitrate, "
2920 "samplerate, filesize, filetype ) "
2923 QString artist
= bundle
->artist();
2924 QString title
= bundle
->title();
2925 if ( title
.isEmpty() )
2927 title
= bundle
->url().fileName();
2928 if ( bundle
->url().fileName().indexOf( '-' ) > 0 )
2930 if ( artist
.isEmpty() )
2932 artist
= bundle
->url().fileName().section( '-', 0, 0 ).trimmed();
2933 bundle
->setArtist( artist
);
2935 title
= bundle
->url().fileName().section( '-', 1 ).trimmed();
2936 title
= title
.left( title
.lastIndexOf( '.' ) ).trimmed();
2937 if ( title
.isEmpty() ) title
= bundle
->url().fileName();
2939 bundle
->setTitle( title
);
2942 int deviceId
= MountPointManager::instance()->getIdForUrl( bundle
->url() );
2944 MountPointManager::instance()->getRelativePath( deviceId
, bundle
->url(), relativePath
);
2945 //debug() << "File has deviceId " << deviceId << ", relative path " << relativePath.path() << ", absolute path " << bundle->url().path();
2947 command
+= escapeString( relativePath
.path() ) + "','";
2948 command
+= escapeString( relativePath
.directory() ) + "',";
2949 command
+= QString::number( deviceId
) + ',';
2950 command
+= QString::number( QFileInfo( bundle
->url().path() ).created().toTime_t() ) + ',';
2951 command
+= QString::number( QFileInfo( bundle
->url().path() ).lastModified().toTime_t() ) + ',';
2953 command
+= escapeString( QString::number( albumID( bundle
->album(), true, !incremental
, true ) ) ) + ',';
2954 command
+= escapeString( QString::number( artistID( bundle
->artist(), true, !incremental
, true ) ) ) + ',';
2955 command
+= escapeString( QString::number( composerID( bundle
->composer(), true, !incremental
, true ) ) ) + ',';
2956 command
+= escapeString( QString::number( genreID( bundle
->genre(), true, !incremental
, true ) ) ) + ",'";
2957 command
+= escapeString( QString::number( yearID( QString::number( bundle
->year() ), true, !incremental
, true ) ) ) + "','";
2959 command
+= escapeString( bundle
->title() ) + "','";
2960 command
+= escapeString( bundle
->comment() ) + "', ";
2961 command
+= escapeString( QString::number( bundle
->track() ) ) + " , ";
2962 command
+= escapeString( QString::number( bundle
->discNumber() ) ) + " , ";
2963 command
+= escapeString( QString::number( bundle
->bpm() ) ) + " , ";
2964 switch( bundle
->compilation() ) {
2965 case MetaBundle::CompilationNo
:
2969 case MetaBundle::CompilationYes
:
2973 case MetaBundle::CompilationUnknown
:
2979 // NOTE any of these may be -1 or -2, this is what we want
2980 // see MetaBundle::Undetermined
2981 command
+= QString::number( bundle
->length() ) + ',';
2982 command
+= QString::number( bundle
->bitrate() ) + ',';
2983 command
+= QString::number( bundle
->sampleRate() ) + ',';
2984 command
+= QString::number( bundle
->filesize() ) + ',';
2985 command
+= QString::number( bundle
->fileType() ) + ')';
2987 //FIXME: currently there's no way to check if an INSERT query failed or not - always return true atm.
2988 // Now it might be possible as insert returns the rowid.
2989 insert( command
, NULL
);
2991 doAFTStuff( bundle
, true );
2997 CollectionDB::doAFTStuff( MetaBundle
* bundle
, const bool tempTables
)
2999 if( bundle
->uniqueId().isEmpty() || bundle
->url().path().isEmpty() )
3002 MountPointManager
*mpm
= MountPointManager::instance();
3003 //const to make sure one isn't later modified without the other being changed
3004 const int deviceIdInt
= mpm
->getIdForUrl( bundle
->url().path() );
3005 const QString currdeviceid
= QString::number( deviceIdInt
);
3006 QString currid
= escapeString( bundle
->uniqueId() );
3007 QString currurl
= escapeString( mpm
->getRelativePath( deviceIdInt
, bundle
->url().path() ) );
3008 QString currdir
= escapeString( mpm
->getRelativePath( deviceIdInt
, bundle
->url().directory() ) );
3009 //debug() << "Checking currid = " << currid << ", currdir = " << currdir << ", currurl = " << currurl;
3010 //debug() << "tempTables = " << (tempTables?"true":"false");
3013 QStringList urls
= query( QString(
3014 "SELECT url, uniqueid "
3016 "WHERE deviceid = %2 AND url = '%3';" )
3017 .arg( tempTables
? "_temp" : ""
3021 QStringList uniqueids
= query( QString(
3022 "SELECT url, uniqueid, deviceid "
3024 "WHERE uniqueid = '%2';" )
3025 .arg( tempTables
? "_temp" : ""
3028 QStringList nonTempIDs
= query( QString(
3029 "SELECT url, uniqueid, deviceid "
3031 "WHERE uniqueid = '%1';" )
3034 QStringList nonTempURLs
= query( QString(
3035 "SELECT url, uniqueid "
3037 "WHERE deviceid = %1 AND url = '%2';" )
3041 bool tempTablesAndInPermanent
= false;
3042 bool permanentFullMatch
= false;
3044 //if we're not using temp tables here, i.e. tempTables is false,
3045 //then the results from both sets of queries above should be equal,
3046 //so behavior should be the same
3047 if( tempTables
&& ( nonTempURLs
.count() > 0 || nonTempIDs
.count() > 0 ) )
3048 tempTablesAndInPermanent
= true;
3049 if( tempTablesAndInPermanent
&& nonTempURLs
.count() > 0 && nonTempIDs
.count() > 0 )
3050 permanentFullMatch
= true;
3052 //debug() << "tempTablesAndInPermanent = " << (tempTablesAndInPermanent?"true":"false");
3053 //debug() << "permanentFullMatch = " << (permanentFullMatch?"true":"false");
3055 //debug() << "Entering checks";
3056 //first case: not in permanent table or temporary table
3057 if( !tempTablesAndInPermanent
&& urls
.empty() && uniqueids
.empty() )
3059 //debug() << "first case";
3060 QString insertline
= QStringx( "INSERT INTO uniqueid%1 (deviceid, url, uniqueid, dir) "
3061 "VALUES ( %2,'%3', '%4', '%5');" )
3062 .args( QStringList()
3063 << ( tempTables
? "_temp" : "" )
3068 insert( insertline
, NULL
);
3069 //debug() << "aftCheckPermanentTables #1";
3070 aftCheckPermanentTables( currdeviceid
, currid
, currurl
);
3074 //next case: not in permanent table, but a match on one or the other in the temporary table
3075 //OR, we are using permanent tables (and not considering temp ones)
3076 if( !tempTablesAndInPermanent
)
3078 if( urls
.empty() ) //uniqueid already found in temporary table but not url; check the old URL
3080 //stat the original URL
3081 QString absPath
= mpm
->getAbsolutePath( uniqueids
[2].toInt(), uniqueids
[0] );
3082 //debug() << "At doAFTStuff, stat-ing file " << absPath;
3083 bool statSuccessful
= false;
3084 bool pathsSame
= absPath
== bundle
->url().path();
3086 statSuccessful
= QFile::exists( absPath
);
3087 if( statSuccessful
) //if true, new one is a copy
3088 warning() << "Already-scanned file at " << absPath
<< " has same UID as new file at " << bundle
->url().path() << endl
;
3089 else //it's a move, not a copy, or a copy and then both files were moved...can't detect that
3091 //debug() << "stat was NOT successful, updating tables with: ";
3092 //debug() << QString( "UPDATE uniqueid%1 SET url='%2', dir='%3' WHERE uniqueid='%4';" ).arg( ( tempTables ? "_temp" : "" ), currurl, currdir, currid );
3093 query( QStringx( "UPDATE uniqueid%1 SET deviceid = %2, url='%3', dir='%4' WHERE uniqueid='%5';" )
3094 .args( QStringList()
3095 << ( tempTables
? "_temp" : "" )
3101 emit
fileMoved( absPath
, bundle
->url().path(), bundle
->uniqueId() );
3104 //okay then, url already found in temporary table but different uniqueid
3105 //a file exists in the same place as before, but new uniqueid...assume
3106 //that this is desired user behavior
3107 //NOTE: this should never happen during an incremental scan with temporary tables...!
3108 else if( uniqueids
.empty() )
3110 //debug() << "file exists in same place as before, new uniqueid";
3111 query( QString( "UPDATE uniqueid%1 SET uniqueid='%2' WHERE deviceid = %3 AND url='%4';" )
3112 .arg( tempTables
? "_temp" : ""
3116 emit
uniqueIdChanged( bundle
->url().path(), urls
[1], bundle
->uniqueId() );
3118 //else uniqueid and url match; nothing happened, so safely exit
3121 //okay...being here means, we are using temporary tables, AND it exists in the permanent table
3124 //first case...full match exists in permanent table, should then be no match in temp table
3125 //(since code below deleted from permanent table after changes)
3126 //in this case, just insert into temp table
3127 if( permanentFullMatch
)
3129 QString insertline
= QString( "INSERT INTO uniqueid_temp (deviceid, url, uniqueid, dir) "
3130 "VALUES ( %1, '%2'" )
3133 insertline
+= QString( ", '%1', '%2');" ).arg( currid
).arg( currdir
);
3134 //debug() << "running command: " << insertline;
3135 insert( insertline
, NULL
);
3136 //debug() << "aftCheckPermanentTables #2";
3137 aftCheckPermanentTables( currdeviceid
, currid
, currurl
);
3141 //second case...full match exists in permanent table, but path is different
3142 if( nonTempURLs
.empty() )
3144 //stat the original URL
3145 QString absPath
= mpm
->getAbsolutePath( nonTempIDs
[2].toInt(), nonTempIDs
[0] );
3146 //debug() << "At doAFTStuff part 2, stat-ing file " << absPath;
3147 bool statSuccessful
= false;
3148 bool pathsSame
= absPath
== bundle
->url().path();
3150 statSuccessful
= QFile::exists( absPath
);
3151 if( statSuccessful
) //if true, new one is a copy
3152 warning() << "Already-scanned file at " << absPath
<< " has same UID as new file at " << currurl
<< endl
;
3153 else //it's a move, not a copy, or a copy and then both files were moved...can't detect that
3155 //debug() << "stat part 2 was NOT successful, updating tables with: ";
3156 query( QString( "DELETE FROM uniqueid WHERE uniqueid='%1';" )
3158 query( QString( "INSERT INTO uniqueid_temp (deviceid, url, uniqueid, dir) "
3159 "VALUES ( %1, '%2', '%3', '%4')" )
3165 emit
fileMoved( absPath
, bundle
->url().path(), bundle
->uniqueId() );
3168 else if( nonTempIDs
.empty() )
3170 //debug() << "file exists in same place as before, part 2, new uniqueid";
3171 query( QString( "DELETE FROM uniqueid WHERE deviceid = %1 AND url='%2';" )
3172 .arg( currdeviceid
)
3174 query( QString( "INSERT INTO uniqueid_temp (deviceid, url, uniqueid, dir) VALUES ( %1, '%2', '%3', '%4')" )
3179 emit
uniqueIdChanged( bundle
->url().path(), nonTempURLs
[1], bundle
->uniqueId() );
3181 //else do nothing...really this case should never happen
3187 CollectionDB::emitFileDeleted( const QString
&absPath
, const QString
&uniqueid
)
3189 if( uniqueid
.isEmpty() )
3190 emit
fileDeleted( absPath
);
3192 emit
fileDeleted( absPath
, uniqueid
);
3196 CollectionDB::emitFileAdded( const QString
&absPath
, const QString
&uniqueid
)
3198 if( uniqueid
.isEmpty() )
3199 emit
fileAdded( absPath
);
3201 emit
fileAdded( absPath
, uniqueid
);
3205 CollectionDB::urlFromUniqueId( const QString
&id
)
3207 bool scanning
= ( ScanController::instance() && ScanController::instance()->tablesCreated() );
3208 QStringList urls
= query( QString(
3209 "SELECT deviceid, url "
3211 "WHERE uniqueid = '%2';" )
3212 .arg( scanning
? "_temp" : QString() )
3215 if( urls
.empty() && scanning
)
3216 urls
= query( QString(
3217 "SELECT deviceid, url "
3219 "WHERE uniqueid = '%1';" )
3225 return MountPointManager::instance()->getAbsolutePath( urls
[0].toInt(), urls
[1] );
3229 CollectionDB::uniqueIdFromUrl( const KUrl
&url
)
3231 MountPointManager
*mpm
= MountPointManager::instance();
3232 int currdeviceid
= mpm
->getIdForUrl( url
.path() );
3233 QString currurl
= escapeString( mpm
->getRelativePath( currdeviceid
, url
.path() ) );
3235 bool scanning
= ( ScanController::instance() && ScanController::instance()->tablesCreated() );
3236 QStringList uid
= query( QString(
3239 "WHERE deviceid = %2 AND url = '%3';" )
3240 .arg( scanning
? "_temp" : QString() )
3241 .arg( currdeviceid
)
3242 .arg( currurl
), true );
3244 if( uid
.empty() && scanning
)
3245 uid
= query( QString(
3248 "WHERE deviceid = %1 AND url = '%2';" )
3249 .arg( currdeviceid
)
3259 CollectionDB::getURL( const MetaBundle
&bundle
)
3261 uint artID
= artistID( bundle
.artist(), false );
3265 uint albID
= albumID( bundle
.album(), false );
3269 QString q
= QString( "SELECT tags.deviceid, tags.url "
3271 "WHERE tags.album = '%1' AND tags.artist = '%2' AND tags.track = '%3' AND tags.title = '%4'" +
3272 deviceidSelection() + ';' )
3275 .arg( bundle
.track() )
3276 .arg( escapeString( bundle
.title() ) );
3278 QStringList urls
= URLsFromQuery( query( q
) );
3283 if( urls
.size() == 1 )
3285 return urls
.first();
3288 QString url
= urls
.first();
3290 for( QStringList::iterator it
= urls
.begin();
3294 int pc
= getPlayCount( *it
);
3295 if( pc
> maxPlayed
)
3305 // Helper function to convert the "tags.sampler" column to a MetaBundle::Collection value
3307 // We use the first char of boolT / boolF as not all DBs store true/false as
3308 // numerics (and it's only a single-char column)
3310 samplerToCompilation( const QString
&it
)
3312 if( it
== CollectionDB::instance()->boolT().mid( 0, 1 ) )
3314 return MetaBundle::CompilationYes
;
3316 else if( it
== CollectionDB::instance()->boolF().mid( 0, 1 ) )
3318 return MetaBundle::CompilationNo
;
3320 return MetaBundle::CompilationUnknown
;
3324 CollectionDB::bundleFromQuery( QStringList::const_iterator
*iter
)
3326 QStringList::const_iterator
&it
= *iter
;
3328 //QueryBuilder automatically inserts the deviceid as return value if asked for the path
3329 QString rpath
= *it
;
3330 int deviceid
= (*++it
).toInt();
3331 b
.setPath ( MountPointManager::instance()->getAbsolutePath( deviceid
, rpath
) );
3332 b
.setAlbum ( *++it
);
3333 b
.setArtist ( *++it
);
3334 b
.setComposer ( *++it
);
3335 b
.setGenre ( *++it
);
3336 b
.setTitle ( *++it
);
3337 b
.setYear ( (*++it
).toInt() );
3338 b
.setComment ( *++it
);
3339 b
.setTrack ( (*++it
).toInt() );
3340 b
.setBitrate ( (*++it
).toInt() );
3341 b
.setDiscNumber( (*++it
).toInt() );
3342 b
.setLength ( (*++it
).toInt() );
3343 b
.setSampleRate( (*++it
).toInt() );
3344 b
.setFilesize ( (*++it
).toInt() );
3346 b
.setCompilation( samplerToCompilation( *it
) );
3348 b
.setFileType( (*++it
).toInt() );
3349 b
.setBpm ( (*++it
).toFloat() );
3351 b
.setScore ( (*++it
).toFloat() );
3352 b
.setRating ( (*++it
).toInt() );
3353 b
.setPlayCount ( (*++it
).toInt() );
3354 b
.setLastPlay ( (*++it
).toInt() );
3356 if( false && b
.length() <= 0 ) {
3357 // we try to read the tags, despite the slow-down
3358 debug() << "Audioproperties not known for: " << b
.url().fileName();
3359 b
.readTags( TagLib::AudioProperties::Fast
);
3366 fillInBundle( QStringList values
, MetaBundle
&bundle
)
3368 //TODO use this whenever possible
3371 while( values
.count() < 16 )
3372 values
+= "IF YOU CAN SEE THIS THERE IS A BUG!";
3374 QStringList::ConstIterator it
= values
.begin();
3376 bundle
.setAlbum ( *it
); ++it
;
3377 bundle
.setArtist ( *it
); ++it
;
3378 bundle
.setComposer ( *it
); ++it
;
3379 bundle
.setGenre ( *it
); ++it
;
3380 bundle
.setTitle ( *it
); ++it
;
3381 bundle
.setYear ( (*it
).toInt() ); ++it
;
3382 bundle
.setComment ( *it
); ++it
;
3383 bundle
.setDiscNumber( (*it
).toInt() ); ++it
;
3384 bundle
.setTrack ( (*it
).toInt() ); ++it
;
3385 bundle
.setBitrate ( (*it
).toInt() ); ++it
;
3386 bundle
.setLength ( (*it
).toInt() ); ++it
;
3387 bundle
.setSampleRate( (*it
).toInt() ); ++it
;
3388 bundle
.setFilesize ( (*it
).toInt() ); ++it
;
3389 bundle
.setFileType ( (*it
).toInt() ); ++it
;
3390 bundle
.setBpm ( (*it
).toFloat() ); ++it
;
3392 bundle
.setCompilation( samplerToCompilation( *it
) );
3395 bundle
.setUniqueId(*it
);
3399 CollectionDB::bundleForUrl( MetaBundle
* bundle
)
3401 int deviceid
= MountPointManager::instance()->getIdForUrl( bundle
->url() );
3403 MountPointManager::instance()->getRelativePath( deviceid
, bundle
->url(), rpath
);
3404 QStringList values
= query( QString(
3405 "SELECT album.name, artist.name, composer.name, genre.name, tags.title, "
3406 "year.name, tags.comment, tags.discnumber, "
3407 "tags.track, tags.bitrate, tags.length, tags.samplerate, "
3408 "tags.filesize, tags.filetype, tags.bpm, tags.sampler, uniqueid.uniqueid "
3409 "FROM tags LEFT OUTER JOIN uniqueid ON tags.url = uniqueid.url AND tags.deviceid = uniqueid.deviceid,"
3410 "album, artist, composer, genre, year "
3411 "WHERE album.id = tags.album AND artist.id = tags.artist AND composer.id = tags.composer AND "
3412 "genre.id = tags.genre AND year.id = tags.year AND tags.url = '%2' AND tags.deviceid = %1;" )
3414 .arg( escapeString( rpath
.path( ) ) ) );
3418 if ( !values
.empty() )
3420 fillInBundle( values
, *bundle
);
3423 else if( MediaBrowser::instance() && MediaBrowser::instance()->getBundle( bundle
->url(), bundle
) )
3429 // check if it's a podcast
3430 PodcastEpisodeBundle peb
;
3431 if( getPodcastEpisodeBundle( bundle
->url(), &peb
) )
3433 if( bundle
->url().protocol() == "file" && QFile::exists( bundle
->url().path() ) )
3435 MetaBundle
mb( bundle
->url(), true /* avoid infinite recursion */ );
3438 bundle
->copyFrom( peb
);
3447 Q3ValueList
<MetaBundle
>
3448 CollectionDB::bundlesByUrls( const KUrl::List
& urls
)
3455 for( KUrl::List::ConstIterator it
= urls
.begin(), end
= urls
.end(); it
!= end
; ++it
, ++count
)
3457 // non file stuff won't exist in the db, but we still need to
3458 // re-insert it into the list we return, just with no tags assigned
3459 paths
+= (*it
).protocol() == "file" ? (*it
).path() : (*it
).url();
3461 if( paths
.count() == 50 || count
== urls
.size() - 1 )
3465 qb
.addReturnValue( QueryBuilder::tabAlbum
, QueryBuilder::valName
);
3466 qb
.addReturnValue( QueryBuilder::tabArtist
, QueryBuilder::valName
);
3467 qb
.addReturnValue( QueryBuilder::tabComposer
, QueryBuilder::valName
);
3468 qb
.addReturnValue( QueryBuilder::tabGenre
, QueryBuilder::valName
);
3469 qb
.addReturnValue( QueryBuilder::tabSong
, QueryBuilder::valTitle
);
3470 qb
.addReturnValue( QueryBuilder::tabYear
, QueryBuilder::valName
);
3471 qb
.addReturnValue( QueryBuilder::tabSong
, QueryBuilder::valComment
);
3472 qb
.addReturnValue( QueryBuilder::tabSong
, QueryBuilder::valTrack
);
3473 qb
.addReturnValue( QueryBuilder::tabSong
, QueryBuilder::valBitrate
);
3474 qb
.addReturnValue( QueryBuilder::tabSong
, QueryBuilder::valDiscNumber
);
3475 qb
.addReturnValue( QueryBuilder::tabSong
, QueryBuilder::valLength
);
3476 qb
.addReturnValue( QueryBuilder::tabSong
, QueryBuilder::valSamplerate
);
3477 qb
.addReturnValue( QueryBuilder::tabSong
, QueryBuilder::valFilesize
);
3478 qb
.addReturnValue( QueryBuilder::tabSong
, QueryBuilder::valFileType
);
3479 qb
.addReturnValue( QueryBuilder::tabSong
, QueryBuilder::valBPM
);
3480 qb
.addReturnValue( QueryBuilder::tabSong
, QueryBuilder::valURL
);
3481 qb
.addReturnValue( QueryBuilder::tabSong
, QueryBuilder::valIsCompilation
);
3483 qb
.addUrlFilters( paths
);
3484 qb
.setOptions( QueryBuilder::optRemoveDuplicates
);
3486 const QStringList values
= qb
.run();
3490 oldForeach( values
)
3493 b
.setArtist ( *++it
);
3494 b
.setComposer ( *++it
);
3495 b
.setGenre ( *++it
);
3496 b
.setTitle ( *++it
);
3497 b
.setYear ( (*++it
).toInt() );
3498 b
.setComment ( *++it
);
3499 b
.setTrack ( (*++it
).toInt() );
3500 b
.setBitrate ( (*++it
).toInt() );
3501 b
.setDiscNumber( (*++it
).toInt() );
3502 b
.setLength ( (*++it
).toInt() );
3503 b
.setSampleRate( (*++it
).toInt() );
3504 b
.setFilesize ( (*++it
).toInt() );
3505 b
.setFileType ( (*++it
).toInt() );
3506 b
.setBpm ( (*++it
).toFloat() );
3507 b
.setPath ( *++it
);
3509 b
.setCompilation( samplerToCompilation( *it
) );
3517 // we get no guarantee about the order that the database
3518 // will return our values, and sqlite indeed doesn't return
3519 // them in the desired order :( (MySQL does though)
3522 for( BundleList::Iterator jt
= buns50
.begin(), end
= buns50
.end(); jt
!= end
; ++jt
)
3524 if ( ( *jt
).url().path() == ( *it
))
3527 buns50
.remove( jt
);
3532 // if we get here, we didn't find an entry
3534 KUrl url
= KUrl( *it
);
3536 if( true /* !MediaBrowser::instance()->getBundle( url, &b ) */ )
3538 if( url
.isLocalFile() )
3540 b
= MetaBundle( url
);
3546 // FIXME: more context for i18n after string freeze
3547 b
.setTitle( QString( "%1 %2 %3%4" )
3548 .arg( url
.fileName(),
3550 url
.hasHost() ? url
.host() : QString(),
3551 url
.directory() ) );
3554 // check if it's a podcast
3555 PodcastEpisodeBundle peb
;
3556 if( getPodcastEpisodeBundle( url
, &peb
) )
3560 else if( b
.url().protocol() == "audiocd" || b
.url().protocol() == "cdda" )
3562 // try to see if the engine has some info about the
3563 // item (the intended behaviour should be that if the
3564 // item is an AudioCD track, the engine can return
3565 // CDDB data for it)
3566 Engine::SimpleMetaBundle smb
;
3567 if ( EngineController::engine()->metaDataForUrl( b
.url(), smb
) )
3569 b
.setTitle( smb
.title
);
3570 b
.setArtist( smb
.artist
);
3571 b
.setAlbum( smb
.album
);
3572 b
.setComment( smb
.comment
);
3573 b
.setGenre( smb
.genre
);
3574 b
.setBitrate( smb
.bitrate
.toInt() );
3575 b
.setSampleRate( smb
.samplerate
.toInt() );
3576 b
.setLength( smb
.length
.toInt() );
3577 b
.setYear( smb
.year
.toInt() );
3578 b
.setTrack( smb
.tracknr
.toInt() );
3597 CollectionDB::addAudioproperties( const MetaBundle
& bundle
)
3599 int deviceid
= MountPointManager::instance()->getIdForUrl( bundle
.url() );
3601 MountPointManager::instance()->getRelativePath( deviceid
, bundle
.url(), rpath
);
3602 query( QString( "UPDATE tags SET bitrate='%1', length='%2', samplerate='%3' WHERE url='%5' AND deviceid = %4;" )
3603 .arg( bundle
.bitrate() )
3604 .arg( bundle
.length() )
3605 .arg( bundle
.sampleRate() )
3607 .arg( escapeString( rpath
.path() ) ) );
3612 CollectionDB::addSongPercentage( const QString
&url
, float percentage
,
3613 const QString
&reason
, const QDateTime
*playtime
)
3615 //the URL must always be inserted last! an escaped URL can contain Strings like %1->bug
3616 int deviceid
= MountPointManager::instance()->getIdForUrl( url
);
3617 QString rpath
= MountPointManager::instance()->getRelativePath( deviceid
, url
);
3618 //statistics table might not have those values, but we need them later, so keep them
3619 int statDevId
= deviceid
;
3620 QString statRPath
= rpath
;
3621 QStringList values
=
3623 "SELECT playcounter, createdate, percentage, rating FROM statistics "
3624 "WHERE url = '%2' AND deviceid = %1;" )
3625 .arg( statDevId
).arg( escapeString( statRPath
) ) );
3627 //handle corner case: deviceid!=-1 but there is a statistics row for that song with deviceid -1
3628 if ( values
.isEmpty() )
3630 QString rpath2
= '.' + url
;
3631 values
= query( QString(
3632 "SELECT playcounter, createdate, percentage, rating FROM statistics "
3633 "WHERE url = '%1' AND deviceid = -1;" )
3634 .arg( escapeString( rpath2
) ) );
3635 if ( !values
.isEmpty() )
3642 uint atime
= playtime
? playtime
->toTime_t() : QDateTime::currentDateTime().toTime_t();
3645 if ( percentage
> 100.f
) percentage
= 100.f
;
3646 if ( percentage
< 1.f
) percentage
= 1.f
;
3648 if ( !values
.isEmpty() )
3651 // increment playcounter and update accesstime
3652 query( QString( "UPDATE statistics SET playcounter=%1, accessdate=%2 WHERE url='%4' AND deviceid= %3;" )
3653 .arg( values
[0] + " + 1" )
3656 .arg( escapeString( statRPath
) ) );
3660 insert( QString( "INSERT INTO statistics ( url, deviceid, createdate, accessdate, percentage, playcounter, rating, uniqueid, deleted ) "
3661 "VALUES ( '%6', %5, %1, %2, 0, 1, 0, %3, %4 );" )
3664 .arg( ( getUniqueId( url
).isNull() ? "NULL" : '\'' + escapeString( getUniqueId( url
) ) + '\'' ) )
3667 .arg( escapeString( statRPath
) ), 0 );
3670 double prevscore
= 50;
3672 if( !values
.isEmpty() )
3674 playcount
= values
[ 0 ].toInt();
3675 // This stops setting the Rating (which creates a row) from affecting the
3676 // prevscore of an unplayed track. See bug 127475
3678 prevscore
= values
[ 2 ].toDouble();
3680 const QStringList v
= query( QString( "SELECT length FROM tags WHERE url = '%2' AND deviceid = %1;" )
3681 .arg( deviceid
).arg( escapeString( rpath
) ) );
3682 const int length
= v
.isEmpty() ? 0 : v
.first().toInt();
3684 ScriptManager::instance()->requestNewScore( url
, prevscore
, playcount
, length
, percentage
, reason
);
3689 CollectionDB::getSongPercentage( const QString
&url
)
3692 qb
.addReturnValue( QueryBuilder::tabStats
, QueryBuilder::valScore
);
3693 qb
.addMatch( QueryBuilder::tabStats
, QueryBuilder::valURL
, url
);
3695 QStringList values
= qb
.run();
3697 if( !values
.isEmpty() )
3698 return values
.first().toFloat();
3704 CollectionDB::getSongRating( const QString
&url
)
3707 qb
.addReturnValue( QueryBuilder::tabStats
, QueryBuilder::valRating
);
3708 qb
.addMatch( QueryBuilder::tabStats
, QueryBuilder::valURL
, url
);
3710 QStringList values
= qb
.run();
3712 if( values
.count() )
3713 return qBound( 0, values
.first().toInt(), 10 );
3719 CollectionDB::setSongPercentage( const QString
&url
, float percentage
)
3721 int deviceid
= MountPointManager::instance()->getIdForUrl( url
);
3722 QString rpath
= MountPointManager::instance()->getRelativePath( deviceid
, url
);
3723 QStringList values
=
3725 "SELECT playcounter, createdate, accessdate, rating FROM statistics WHERE url = '%2' AND deviceid = %1;" )
3726 .arg( deviceid
).arg( escapeString( rpath
) ) );
3728 //handle corner case: deviceid!=-1 but there is a statistics row for that song with deviceid -1
3729 if ( values
.isEmpty() )
3731 QString rpath2
= '.' + url
;
3732 values
= query( QString(
3733 "SELECT playcounter, createdate, accessdate, rating FROM statistics "
3734 "WHERE url = '%1' AND deviceid = -1;" )
3735 .arg( escapeString( rpath2
) ) );
3736 if ( !values
.isEmpty() )
3744 if ( percentage
> 100.f
) percentage
= 100.f
;
3745 if ( percentage
< 0.f
) percentage
= 0.f
;
3747 if ( !values
.isEmpty() )
3749 query( QString( "UPDATE statistics SET percentage=%1 WHERE url='%3' AND deviceid = %2;" )
3751 .arg( deviceid
).arg( escapeString( rpath
) ) );
3755 insert( QString( "INSERT INTO statistics ( url, deviceid, createdate, accessdate, percentage, playcounter, rating, uniqueid, deleted ) "
3756 "VALUES ( '%7', %6, %2, %3, %1, 0, 0, %3, %4 );" )
3758 .arg( QDateTime::currentDateTime().toTime_t() )
3760 .arg( ( getUniqueId( url
).isNull() ? "NULL" : '\'' + escapeString( getUniqueId( url
) ) + '\'' ) )
3763 .arg( escapeString( rpath
) ),0 );
3766 emit
scoreChanged( url
, percentage
);
3770 CollectionDB::setSongRating( const QString
&url
, int rating
, bool toggleHalf
)
3772 int deviceid
= MountPointManager::instance()->getIdForUrl( url
);
3773 QString rpath
= MountPointManager::instance()->getRelativePath( deviceid
, url
);
3774 QStringList values
=
3776 "SELECT playcounter, createdate, accessdate, percentage, rating FROM statistics WHERE url = '%2' AND deviceid = %1;" )
3778 .arg( escapeString( rpath
) ) );
3780 //handle corner case: deviceid!=-1 but there is a statistics row for that song with deviceid -1
3781 if ( values
.isEmpty() )
3783 QString rpath2
= '.' + url
;
3784 values
= query( QString(
3785 "SELECT playcounter, createdate, accessdate, percentage, rating FROM statistics "
3786 "WHERE url = '%1' AND deviceid = -1;" )
3787 .arg( escapeString( rpath2
) ) );
3788 if ( !values
.isEmpty() )
3796 if ( !values
.isEmpty() )
3798 int prev
= values
[4].toInt( &ok
);
3799 if( ok
&& toggleHalf
&& ( prev
== rating
|| ( prev
== 1 && rating
== 2 ) ) )
3801 if( prev
== 1 && rating
== 2 )
3803 else if( rating
% 2 ) //.5
3811 if ( rating
> 10 ) rating
= 10;
3812 if ( rating
< 0 ) rating
= 0;
3814 if ( !values
.isEmpty() )
3816 query( QString( "UPDATE statistics SET rating=%1 WHERE url='%3' AND deviceid = %2;" )
3819 .arg( escapeString( rpath
) ) );
3823 insert( QString( "INSERT INTO statistics ( url, deviceid, createdate, accessdate, percentage, rating, playcounter, uniqueid, deleted ) "
3824 "VALUES ( '%7', %6, %2, %3, 0, %1, 0, %4, %5 );" )
3826 .arg( QDateTime::currentDateTime().toTime_t() )
3828 .arg( ( getUniqueId( url
).isNull() ? "NULL" : '\'' + escapeString( getUniqueId( url
) ) + '\'' ) )
3831 .arg( escapeString( rpath
) ), NULL
);
3834 emit
ratingChanged( url
, rating
);
3838 CollectionDB::getPlayCount( const QString
&url
)
3840 //queryBuilder is good
3842 qb
.addReturnValue( QueryBuilder::tabStats
, QueryBuilder::valPlayCounter
);
3843 qb
.addMatch( QueryBuilder::tabStats
, QueryBuilder::valURL
, url
);
3844 QStringList values
= qb
.run();
3845 if( values
.count() )
3846 return values
.first().toInt();
3851 CollectionDB::getFirstPlay( const QString
&url
)
3854 qb
.addReturnValue( QueryBuilder::tabStats
, QueryBuilder::valCreateDate
);
3855 qb
.addMatch( QueryBuilder::tabStats
, QueryBuilder::valURL
, url
);
3856 QStringList values
= qb
.run();
3858 if( values
.count() )
3859 dt
.setTime_t( values
.first().toUInt() );
3864 CollectionDB::getLastPlay( const QString
&url
)
3867 qb
.addReturnValue( QueryBuilder::tabStats
, QueryBuilder::valAccessDate
);
3868 qb
.addMatch( QueryBuilder::tabStats
, QueryBuilder::valURL
, url
);
3869 QStringList values
= qb
.run();
3871 if( values
.count() )
3872 dt
.setTime_t( values
.first().toUInt() );
3878 * @short: exchange url references in the database for a particular file
3879 * @note: deletes all items for newURL, changes oldURL->newURL, deletes oldURL.
3880 * FIXME: should we check if lyrics etc exist in the newURL and keep them if necessary?
3883 CollectionDB::migrateFile( const QString
&oldURL
, const QString
&newURL
)
3885 int oldMediaid
= MountPointManager::instance()->getIdForUrl( oldURL
);
3886 QString oldRpath
= MountPointManager::instance()->getRelativePath( oldMediaid
, oldURL
);
3888 int newMediaid
= MountPointManager::instance()->getIdForUrl( newURL
);
3889 QString newRpath
= MountPointManager::instance()->getRelativePath( newMediaid
, newURL
);
3891 // Ensure destination is clear.
3892 query( QString( "DELETE FROM tags WHERE url = '%2' AND deviceid = %1;" )
3893 .arg( newMediaid
).arg( escapeString( newRpath
) ) );
3895 query( QString( "DELETE FROM statistics WHERE url = '%2' AND deviceid = %1;" )
3896 .arg( newMediaid
).arg( escapeString( newRpath
) ) );
3898 query( QString( "DELETE FROM tags_labels WHERE url = '%2' and deviceid = %1;" )
3899 .arg( newMediaid
).arg( escapeString( newRpath
) ) );
3901 if ( !getLyrics( oldURL
).isEmpty() )
3902 query( QString( "DELETE FROM lyrics WHERE url = '%2' AND deviceid = %1;" )
3903 .arg( newMediaid
).arg( escapeString( newRpath
) ) );
3905 //code looks ugly but prevents problems when the URL contains HTTP escaped characters
3906 query( QString( "UPDATE tags SET url = '%3', deviceid = %1" )
3907 .arg( newMediaid
).arg( escapeString( newRpath
) )
3908 + QString( " WHERE deviceid=%1 AND url = '%2';" )
3909 .arg( oldMediaid
).arg( escapeString( oldRpath
) ) );
3911 query( QString( "UPDATE statistics SET url = '%2', deviceid = %1" )
3912 .arg( newMediaid
).arg( escapeString( newRpath
) )
3913 + QString( " WHERE deviceid=%1 AND url = '%2';" )
3914 .arg( oldMediaid
).arg( escapeString( oldRpath
) ) );
3916 query( QString( "UPDATE lyrics SET url = '%2', deviceid = %1" )
3917 .arg( newMediaid
).arg( escapeString( newRpath
) )
3918 + QString( " WHERE deviceid=%1 AND url = '%2';" )
3919 .arg( oldMediaid
).arg( escapeString( oldRpath
) ) );
3921 query( QString( "UPDATE tags_labels SET url = '%2', deviceid = %1 WHERE deviceid = %3 AND url = '%4';" )
3922 .arg( QString::number( newMediaid
), escapeString( newRpath
), QString::number( oldMediaid
), escapeString( oldRpath
) ) );
3924 query( QString( "UPDATE uniqueid SET url = '%1', deviceid = %2 WHERE url = '%3' AND deviceid = %4;" )
3925 .arg( escapeString( newRpath
), QString::number( newMediaid
),
3926 escapeString( oldRpath
), QString::number( oldMediaid
) ) );
3928 query( QString( "UPDATE playlists SET url = '%1' WHERE url = '%2';" )
3929 .arg( escapeString( newURL
),
3930 escapeString( oldURL
) ) );
3934 CollectionDB::fileOperationResult( KIO::Job
*job
) // slot
3938 m_fileOperationFailed
= true;
3939 debug() << "file operation failed: " << job
->errorText();
3943 m_fileOperationFailed
= false;
3946 m_waitForFileOperation
= false;
3949 void CollectionDB::cancelMovingFileJob()
3951 m_moveFileJobCancelled
= true;
3955 CollectionDB::organizeFile( const KUrl
&src
, /*const OrganizeCollectionDialog &dialog,*/ bool copy
)
3957 Q_UNUSED( src
); Q_UNUSED( copy
);
3960 if( !MetaBundle::isKioUrl( src ) )
3963 bool overwrite = dialog.overwriteCheck->isChecked();
3964 bool localFile = src.isLocalFile();
3969 QString extension = src.url().section( '.', -1 );
3970 extension = extension.section("?", 0, 0); // remove trailling stuff lead by ?, if any
3975 tmp = QString( dialog.folderCombo->currentText() + "/amarok-tmp-%1." + extension ).arg( count );
3977 } while( QFile::exists( tmp ) );
3978 tmpSrc = KUrl( tmp );
3980 KIO::FileCopyJob *job = 0;
3983 job = KIO::file_copy( src, tmpSrc, -1, false, false, false );
3987 job = KIO::file_move( src, tmpSrc, -1, false, false, false );
3989 connect( job, SIGNAL(result( KIO::Job * )), SLOT(fileOperationResult( KIO::Job * )) );
3990 m_waitForFileOperation = true;
3991 while( m_waitForFileOperation )
3993 if( m_moveFileJobCancelled )
3995 disconnect( job, SIGNAL(result( KIO::Job * )), this, SLOT(fileOperationResult( KIO::Job * )) );
3997 QString partFile = QString( "%1.part" ).arg( (job->destUrl()).path() );
3999 QFile file( partFile );
4000 if( file.exists() ) file.remove();
4002 m_waitForFileOperation = false;
4003 m_fileOperationFailed = true;
4008 kapp->processEvents( QEventLoop::AllEvents );
4011 if( m_fileOperationFailed )
4013 debug() << "failed to transfer " << src.url() << " to " << tmpSrc;
4015 m_moveFileJobCancelled = false;
4020 //Building destination here.
4021 MetaBundle mb( tmpSrc );
4022 QString dest = dialog.buildDestination( dialog.buildFormatString(), mb );
4024 debug() << "Destination: " << dest;
4026 if( !m_moveFileJobCancelled && tmpSrc.path() != dest ) //suppress error warning that file couldn't be moved
4028 if( !CollectionDB::instance()->moveFile( tmpSrc.url(), dest, overwrite, copy && localFile ) )
4031 QFile::remove( tmpSrc.path() );
4033 m_moveFileJobCancelled = false;
4038 //Use cover image for folder icon
4039 if( !m_moveFileJobCancelled && dialog.coverCheck->isChecked() && !mb.artist().isEmpty() && !mb.album().isEmpty() )
4041 KUrl dstURL = KUrl( dest );
4044 QString path = dstURL.directory();
4045 QString cover = CollectionDB::instance()->albumImage( mb.artist(), mb.album(), false, 1 );
4047 if( !QFile::exists(path + "/.directory") && !cover.endsWith( "nocover.png" ) )
4049 //QPixmap thumb; //Not amazon nice.
4050 //if ( thumb.load( cover ) ){
4051 //thumb.save(path + "/.front.png", "PNG", -1 ); //hide files
4053 KConfig config(path + "/.directory");
4054 config.setGroup("Desktop Entry");
4056 if( !config.hasKey("Icon") )
4058 config.writeEntry( "Icon", cover );
4061 //} //Not amazon nice.
4065 if( localFile && isDirInCollection( src.directory() ) && QDir().rmdir( src.directory() ) )
4067 debug() << "removed: " << src.directory();
4070 m_moveFileJobCancelled = false;
4077 CollectionDB::moveFile( const QString
&src
, const QString
&dest
, bool overwrite
, bool copy
)
4081 debug() << "Source and destination URLs are the same, aborting.";
4086 KUrl srcURL
= KUrl( src
);
4087 KUrl dstURL
= KUrl( dest
);
4093 // Make sure it is valid.
4094 if(!srcURL
.isValid() || !dstURL
.isValid())
4095 debug() << "Invalid URL ";
4097 // Get just the directory.
4099 dir
.setFileName(QString());
4101 // Create the directory.
4102 if(!KStandardDirs::exists(dir
.path()))
4103 if(!KStandardDirs::makeDir(dir
.path())) {
4104 debug() << "Unable to create directory " << dir
.path();
4107 m_fileOperationFailed
= false;
4108 KIO::FileCopyJob
*job
= 0;
4111 job
= KIO::file_copy( srcURL
, dstURL
, -1, overwrite
, false, false );
4115 job
= KIO::file_move( srcURL
, dstURL
, -1, overwrite
, false, false );
4117 connect( job
, SIGNAL(result( KIO::Job
* )), SLOT(fileOperationResult( KIO::Job
* )) );
4118 m_waitForFileOperation
= true;
4119 while( m_waitForFileOperation
)
4121 if( m_moveFileJobCancelled
)
4123 disconnect( job
, SIGNAL(result( KIO::Job
* )), this, SLOT(fileOperationResult( KIO::Job
* )) );
4125 QString partFile
= QString( "%1.part" ).arg( (job
->destUrl()).path() );
4127 QFile
file( partFile
);
4128 if( file
.exists() ) file
.remove();
4130 m_waitForFileOperation
= false;
4131 m_fileOperationFailed
= true;
4136 kapp
->processEvents( QEventLoop::AllEvents
);
4139 if( !m_fileOperationFailed
)
4143 MetaBundle
bundle( dstURL
);
4144 if( bundle
.isValidMedia() )
4146 addSong( &bundle
, true );
4152 emit
fileMoved( src
, dest
);
4153 migrateFile( srcURL
.path(), dstURL
.path() );
4155 if( isFileInCollection( srcURL
.path() ) )
4161 MetaBundle
bundle( dstURL
);
4162 if( bundle
.isValidMedia() )
4164 addSong( &bundle
, true );
4176 CollectionDB::updateDirStats( QString path
, const long datetime
, const bool temporary
)
4178 if ( path
.endsWith( "/" ) )
4179 path
= path
.left( path
.length() - 1 );
4181 int deviceid
= MountPointManager::instance()->getIdForUrl( path
);
4182 QString rpath
= MountPointManager::instance()->getRelativePath( deviceid
, path
);
4184 if (getDbConnectionType() == DbConnection::postgresql
)
4186 // REPLACE INTO is not valid SQL for postgres, so we need to check whether we
4187 // should UPDATE() or INSERT()
4188 QStringList values
= query( QString("SELECT * FROM directories%1 WHERE dir='%3' AND deviceid=%2;")
4189 .arg( temporary
? "_temp" : "")
4191 .arg( escapeString( rpath
) ) );
4193 if(values
.count() > 0 )
4195 query( QString( "UPDATE directories%1 SET changedate=%2 WHERE dir='%4'AND deviceid=%3;")
4196 .arg( temporary
? "_temp" : "" )
4199 .arg( escapeString( rpath
) ) );
4204 query( QString( "INSERT INTO directories%1 (dir, deviceid,changedate) VALUES ('%4', %3, '%2');")
4205 .arg( temporary
? "_temp" : "")
4208 .arg( escapeString( rpath
) ) );
4213 query( QString( "REPLACE INTO directories%1 ( dir, deviceid, changedate ) VALUES ( '%4', %3, %2 );" )
4214 .arg( temporary
? "_temp" : "" )
4217 .arg( escapeString( rpath
) ) );
4220 INotify::instance()->watchDir( path
);
4225 CollectionDB::removeSongsInDir( QString path
, QMap
<QString
,QString
> *tagsRemoved
)
4227 if ( path
.endsWith( "/" ) )
4228 path
= path
.left( path
.length() - 1 );
4229 int deviceid
= MountPointManager::instance()->getIdForUrl( path
);
4230 QString rpath
= MountPointManager::instance()->getRelativePath( deviceid
, path
);
4232 // Pass back the list of tags we actually delete if requested.
4236 = query( QString( "SELECT tags.deviceid, tags.url, uniqueid.uniqueid FROM tags "
4237 "LEFT JOIN uniqueid ON uniqueid.url = tags.url "
4238 "AND uniqueid.deviceid = tags.deviceid "
4239 "WHERE tags.dir = '%2' AND tags.deviceid = %1" )
4241 .arg( escapeString( rpath
) ) );
4242 QStringList::ConstIterator it
= result
.begin(), end
= result
.end();
4245 int deviceid2
= (*(it
++)).toInt();
4246 QString rpath2
= *(it
++);
4247 QString uniqueid
= *(it
++);
4248 (*tagsRemoved
)[uniqueid
] = MountPointManager::instance()->getAbsolutePath(
4249 deviceid2
, rpath2
);
4253 query( QString( "DELETE FROM tags WHERE dir = '%2' AND deviceid = %1;" )
4255 .arg( escapeString( rpath
) ) );
4257 query( QString( "DELETE FROM uniqueid WHERE dir = '%2' AND deviceid = %1;" )
4259 .arg( escapeString( rpath
) ) );
4264 CollectionDB::isDirInCollection( QString path
)
4266 if ( path
.endsWith( "/" ) )
4267 path
= path
.left( path
.length() - 1 );
4268 int deviceid
= MountPointManager::instance()->getIdForUrl( path
);
4269 QString rpath
= MountPointManager::instance()->getRelativePath( deviceid
, path
);
4271 QStringList values
=
4272 query( QString( "SELECT changedate FROM directories WHERE dir = '%2' AND deviceid = %1;" )
4274 .arg( escapeString( rpath
) ) );
4276 return !values
.isEmpty();
4281 CollectionDB::isFileInCollection( const QString
&url
)
4283 int deviceid
= MountPointManager::instance()->getIdForUrl( url
);
4284 QString rpath
= MountPointManager::instance()->getRelativePath( deviceid
, url
);
4286 QString sql
= QString( "SELECT url FROM tags WHERE url = '%2' AND deviceid = %1" )
4288 .arg( escapeString( rpath
) );
4289 if ( deviceid
== -1 )
4295 QString rpath2
= '.' + url
;
4296 sql
+= QString( " OR url = '%1' AND deviceid = -1;" )
4297 .arg( escapeString( rpath2
) );
4299 QStringList values
= query( sql
);
4301 return !values
.isEmpty();
4306 CollectionDB::removeSongs( const KUrl::List
& urls
)
4308 for( KUrl::List::ConstIterator it
= urls
.begin(), end
= urls
.end(); it
!= end
; ++it
)
4310 int deviceid
= MountPointManager::instance()->getIdForUrl( *it
);
4311 QString rpath
= MountPointManager::instance()->getRelativePath( deviceid
, (*it
).path() );
4313 query( QString( "DELETE FROM tags WHERE url = '%2' AND deviceid = %1;" )
4315 .arg( escapeString( rpath
) ) );
4316 query( QString( "DELETE FROM uniqueid WHERE url = '%2' AND deviceid = %1;" )
4318 .arg( escapeString( rpath
) ) );
4319 query( QString( "UPDATE statistics SET deleted = %1 WHERE url = '%3' AND deviceid = %2;" )
4322 .arg( escapeString( rpath
) ) );
4328 CollectionDB::similarArtists( const QString
&artist
, uint count
)
4332 values
= query( QString( "SELECT suggestion FROM related_artists WHERE artist = '%1' ORDER BY %2 LIMIT %3 OFFSET 0;" )
4333 .arg( escapeString( artist
), randomFunc(), QString::number( count
) ) );
4335 if ( values
.isEmpty() )
4336 Scrobbler::instance()->similarArtists( artist
);
4343 CollectionDB::sanitizeCompilations()
4345 query( QString( "UPDATE tags_temp SET sampler = %1 WHERE sampler IS NULL;").arg( boolF() ) );
4349 CollectionDB::checkCompilations( const QString
&path
, const bool temporary
)
4352 QStringList artists
;
4355 int deviceid
= MountPointManager::instance()->getIdForUrl( path
);
4356 QString rpath
= MountPointManager::instance()->getRelativePath( deviceid
, path
);
4358 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;" )
4359 .arg( temporary
? "_temp" : "" )
4361 .arg( escapeString( rpath
) ) );
4363 for ( int i
= 0; i
< albums
.count(); i
++ )
4365 if ( albums
[ i
].isEmpty() ) continue;
4367 const uint album_id
= albumID( albums
[ i
], false, temporary
, true );
4368 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;" )
4369 .arg( temporary
? "_temp" : "" )
4371 dirs
= query( QString( "SELECT DISTINCT dir FROM tags_temp WHERE album = '%1';" )
4374 if ( artists
.count() > dirs
.count() )
4376 debug() << "Detected compilation: " << albums
[ i
] << " - " << artists
.count() << ':' << dirs
.count();
4378 query( QString( "UPDATE tags_temp SET sampler = %1 WHERE album = '%2' AND sampler IS NULL;" )
4379 .arg(artists
.count() > dirs
.count() ? boolT() : boolF()).arg( album_id
) );
4384 CollectionDB::setCompilation( const KUrl::List
&urls
, bool enabled
, bool updateView
)
4386 Q_UNUSED( updateView
);
4387 for ( KUrl::List::const_iterator it
= urls
.begin(); it
!= urls
.end(); ++it
)
4389 QString
url( ( *it
).path() );
4391 int deviceid
= MountPointManager::instance()->getIdForUrl( url
);
4392 QString rpath
= MountPointManager::instance()->getRelativePath( deviceid
, url
);
4394 query( QString( "UPDATE tags SET sampler = %1 WHERE tags.url = '%2' AND tags.deviceid = %3;" )
4395 .arg( ( enabled
? boolT() : boolF() ), escapeString( rpath
), QString::number( deviceid
) ) );
4398 // Update the Collection-Browser view,
4399 // using QTimer to make sure we don't manipulate the GUI from a thread
4402 // if ( updateView )
4403 // QTimer::singleShot( 0, CollectionView::instance(), SLOT( renderView() ) );
4409 CollectionDB::removeDirFromCollection( QString path
)
4411 //if ( path.endsWith( "/" ) )
4412 // path = path.left( path.length() - 1 );
4413 int deviceid
= MountPointManager::instance()->getIdForUrl( path
);
4414 QString rpath
= MountPointManager::instance()->getRelativePath( deviceid
, path
);
4416 query( QString( "DELETE FROM directories WHERE dir = '%2' AND deviceid = %1;" )
4418 .arg( escapeString( rpath
) ) );
4423 CollectionDB::IDFromExactValue( QString table
, QString value
, bool autocreate
, bool temporary
/* = false */ )
4427 table
.append( "_temp" );
4430 QString
querystr( QString( "SELECT id FROM %1 WHERE name " ).arg( table
) );
4431 querystr
+= exactCondition( value
) + ';';
4432 QStringList result
= query( querystr
);
4433 if ( result
.isEmpty() )
4436 return QString::number( insert( QString( "INSERT INTO %1 ( name ) VALUES ( '%2' );" )
4437 .arg( table
, escapeString( value
) ),
4444 if ( result
.count() > 1 )
4445 debug() << "More than one entry in the " << table
<< " database for '" << value
<< '\'';
4446 return result
.first();
4451 CollectionDB::deleteRedundantName( const QString
&table
, const QString
&id
)
4453 QString
querystr( QString( "SELECT %1 FROM tags WHERE tags.%1 = %2 LIMIT 1;" ).arg( table
, id
) );
4454 QStringList result
= query( querystr
);
4455 if ( result
.isEmpty() )
4456 query( QString( "DELETE FROM %1 WHERE id = %2;" ).arg( table
,id
) );
4460 CollectionDB::deleteAllRedundant( const QString
&table
)
4462 //This works with MySQL4. I thought it might not do, due to the comment in copyTempTables
4463 query( QString( "DELETE FROM %1 WHERE id NOT IN ( SELECT %2 FROM tags )" ).arg( table
, table
) );
4468 CollectionDB::updateTags( const QString
&url
, const MetaBundle
&bundle
, const bool updateView
)
4470 Q_UNUSED( updateView
);
4473 qb
.addReturnValue( QueryBuilder::tabSong
, QueryBuilder::valTitle
);
4474 qb
.addReturnValue( QueryBuilder::tabArtist
, QueryBuilder::valName
);
4475 qb
.addReturnValue( QueryBuilder::tabComposer
, QueryBuilder::valName
);
4476 qb
.addReturnValue( QueryBuilder::tabAlbum
, QueryBuilder::valName
);
4477 qb
.addReturnValue( QueryBuilder::tabGenre
, QueryBuilder::valName
);
4478 qb
.addReturnValue( QueryBuilder::tabYear
, QueryBuilder::valName
);
4479 qb
.addReturnValue( QueryBuilder::tabSong
, QueryBuilder::valTrack
);
4480 qb
.addReturnValue( QueryBuilder::tabSong
, QueryBuilder::valComment
);
4481 qb
.addReturnValue( QueryBuilder::tabSong
, QueryBuilder::valDiscNumber
);
4482 qb
.addReturnValue( QueryBuilder::tabSong
, QueryBuilder::valFilesize
);
4483 qb
.addReturnValue( QueryBuilder::tabSong
, QueryBuilder::valFileType
);
4484 // [10] is above. [11] is below.
4485 qb
.addReturnValue( QueryBuilder::tabSong
, QueryBuilder::valBPM
);
4486 qb
.addReturnValue( QueryBuilder::tabArtist
, QueryBuilder::valID
);
4487 qb
.addReturnValue( QueryBuilder::tabComposer
, QueryBuilder::valID
);
4488 qb
.addReturnValue( QueryBuilder::tabAlbum
, QueryBuilder::valID
);
4489 qb
.addReturnValue( QueryBuilder::tabGenre
, QueryBuilder::valID
);
4490 qb
.addReturnValue( QueryBuilder::tabYear
, QueryBuilder::valID
);
4492 qb
.addUrlFilters ( QStringList( url
) );
4493 qb
.setOptions( QueryBuilder::optRemoveDuplicates
);
4494 QStringList values
= qb
.run();
4496 if ( values
.count() > 17 )
4498 error() << "Query returned more than 1 song. Aborting updating metadata" << endl
;
4502 if ( !values
.isEmpty() )
4504 bool art
=false, comp
=false, alb
=false, gen
=false, year
=false;
4506 QString command
= "UPDATE tags SET ";
4507 if ( values
[ 0 ] != bundle
.title() )
4508 command
+= "title = '" + escapeString( bundle
.title() ) + "', ";
4509 if ( values
[ 1 ] != bundle
.artist() )
4512 command
+= "artist = " + IDFromExactValue( "artist", bundle
.artist() ) + ", ";
4514 if ( values
[ 2 ] != bundle
.composer() )
4517 command
+= "composer = " + IDFromExactValue( "composer", bundle
.composer() ) + ", ";
4519 if ( values
[ 3 ] != bundle
.album() )
4522 command
+= "album = " + IDFromExactValue( "album", bundle
.album() ) + ", ";
4524 if ( values
[ 4 ] != bundle
.genre() )
4527 command
+= "genre = " + IDFromExactValue( "genre", bundle
.genre() ) + ", ";
4529 if ( values
[ 5 ] != QString::number( bundle
.year() ) )
4532 command
+= "year = " + IDFromExactValue( "year", QString::number( bundle
.year() ) ) + ", ";
4534 if ( values
[ 6 ] != QString::number( bundle
.track() ) )
4535 command
+= "track = " + QString::number( bundle
.track() ) + ", ";
4536 if ( values
[ 7 ] != bundle
.comment() )
4537 command
+= "comment = '" + escapeString( bundle
.comment() ) + "', ";
4538 if ( values
[ 8 ] != QString::number( bundle
.discNumber() ) )
4539 command
+= "discnumber = '" + QString::number( bundle
.discNumber() ) + "', ";
4540 if ( values
[ 9 ] != QString::number( bundle
.filesize() ) )
4541 command
+= "filesize = '" + QString::number( bundle
.filesize() ) + "', ";
4542 if ( values
[ 10 ] != QString::number( bundle
.fileType() ) )
4543 command
+= "filetype = '" + QString::number( bundle
.fileType() ) + "', ";
4544 if ( values
[ 11 ] != QString::number( bundle
.bpm() ) )
4545 command
+= "bpm = '" + QString::number( bundle
.bpm() ) + "', ";
4547 if ( "UPDATE tags SET " == command
)
4549 debug() << "No tags selected to be changed";
4553 int deviceid
= MountPointManager::instance()->getIdForUrl( url
);
4554 QString rpath
= MountPointManager::instance()->getRelativePath( deviceid
, url
);
4555 //We have to remove the trailing comma from command
4556 query( command
.left( command
.length() - 2 ) + " WHERE url = '" + escapeString( rpath
) +
4557 "' AND deviceid = " + QString::number( deviceid
) + ';' );
4560 //Check to see if we use the entry anymore. If not, delete it
4562 deleteRedundantName( "artist", values
[ 12 ] );
4564 deleteRedundantName( "composer", values
[ 13 ] );
4566 deleteRedundantName( "album", values
[ 14 ] );
4568 deleteRedundantName( "genre", values
[ 15 ] );
4570 deleteRedundantName( "year", values
[ 16 ] );
4572 // Update the Collection-Browser view,
4573 // using QTimer to make sure we don't manipulate the GUI from a thread
4576 // QTimer::singleShot( 0, CollectionView::instance(), SLOT( databaseChanged() ) );
4580 emit
tagsChanged( values
[12], values
[14] );
4583 //this method is totally useless now, so i'm not going to fix this
4584 /*if ( EngineController::instance()->bundle().url() == bundle.url() )
4586 debug() << "Current song edited, updating widgets: " << bundle.title();
4587 EngineController::instance()->currentTrackMetaDataChanged( bundle );
4590 emit
tagsChanged( bundle
);
4595 CollectionDB::updateURL( const QString
&url
, const bool updateView
)
4597 // don't use the KUrl ctor as it checks the db first
4599 bundle
.setPath( url
);
4600 bundle
.readTags( TagLib::AudioProperties::Fast
);
4602 updateTags( url
, bundle
, updateView
);
4603 doAFTStuff( &bundle
, false );
4607 CollectionDB::getUniqueId( const QString
&url
)
4609 int deviceid
= MountPointManager::instance()->getIdForUrl( url
);
4610 QString rpath
= MountPointManager::instance()->getRelativePath( deviceid
, url
);
4611 QStringList values
= query( QString( "SELECT uniqueid FROM uniqueid WHERE deviceid = %1 AND url = '%2';" )
4613 .arg( escapeString( rpath
) ));
4614 if( !values
.empty() )
4621 CollectionDB::setLyrics( const QString
&url
, const QString
&lyrics
, const QString
&uniqueid
)
4623 int deviceid
= MountPointManager::instance()->getIdForUrl( url
);
4624 QString rpath
= MountPointManager::instance()->getRelativePath( deviceid
, url
);
4626 QStringList values
= query(QString("SELECT lyrics FROM lyrics WHERE url = '%2' AND deviceid = %1;")
4627 .arg( deviceid
).arg( escapeString( rpath
) ) );
4628 if(values
.count() > 0)
4630 if ( !lyrics
.isEmpty() )
4631 query( QString( "UPDATE lyrics SET lyrics = '%1' WHERE url = '%3' AND deviceid = %2;" )
4632 .arg( escapeString( lyrics
), QString::number(deviceid
), escapeString( rpath
) ) );
4634 query( QString( "DELETE FROM lyrics WHERE url = '%2' AND deviceid = %1;" )
4635 .arg( deviceid
).arg( escapeString( rpath
) ) );
4639 insert( QString( "INSERT INTO lyrics (deviceid, url, lyrics, uniqueid) values ( %1, '%2', '%3', '%4' );" )
4640 .arg( QString::number(deviceid
), escapeString( rpath
), escapeString( lyrics
), escapeString( uniqueid
) ), NULL
);
4646 CollectionDB::getLyrics( const QString
&url
)
4648 int deviceid
= MountPointManager::instance()->getIdForUrl( url
);
4649 QString rpath
= MountPointManager::instance()->getRelativePath( deviceid
, url
);
4650 QStringList values
= query( QString( "SELECT lyrics FROM lyrics WHERE url = '%2' AND deviceid = %1;" )
4651 .arg( deviceid
).arg( escapeString( rpath
) ) );
4652 if ( !values
.isEmpty() )
4658 void CollectionDB::removeInvalidAmazonInfo( const QString
& md5sum
)
4660 query( QString( "DELETE FROM amazon WHERE filename='%1'" ).arg( md5sum
) );
4663 void CollectionDB::newAmazonReloadDate( const QString
& asin
, const QString
& locale
, const QString
& md5sum
)
4665 QStringList values
= query(QString("SELECT filename FROM amazon WHERE filename = '%1'")
4667 if(values
.count() > 0)
4669 query( QString("UPDATE amazon SET asin = '%1', locale = '%2', refetchdate = '%3' WHERE filename = '%4'")
4672 .arg(QDateTime::currentDateTime().addDays(80).toTime_t())
4677 insert( QString( "INSERT INTO amazon ( asin, locale, filename, refetchdate ) VALUES ( '%1', '%2', '%3', '%4');" )
4681 .arg(QDateTime::currentDateTime().addDays(80).toTime_t()), NULL
);
4685 QStringList
CollectionDB::staleImages()
4687 return query(QString("SELECT asin, locale, filename FROM amazon WHERE refetchdate < %1 ;")
4688 .arg(QDateTime::currentDateTime().toTime_t() ));
4692 CollectionDB::applySettings()
4694 bool recreateConnections
= false;
4695 if ( AmarokConfig::databaseEngine().toInt() != getDbConnectionType() )
4697 if ( AmarokConfig::databaseEngine().toInt() == DbConnection::mysql
)
4698 m_dbConnType
= DbConnection::mysql
;
4699 else if ( AmarokConfig::databaseEngine().toInt() == DbConnection::postgresql
)
4700 m_dbConnType
= DbConnection::postgresql
;
4701 else m_dbConnType
= DbConnection::sqlite
;
4702 recreateConnections
= true;
4704 else if ( AmarokConfig::databaseEngine().toInt() == DbConnection::mysql
)
4706 // Using MySQL, so check if MySQL settings were changed
4707 const MySqlConfig
*config
=
4708 static_cast<const MySqlConfig
*> ( m_dbConfig
);
4709 if ( AmarokConfig::mySqlHost() != config
->host() )
4711 recreateConnections
= true;
4713 else if ( AmarokConfig::mySqlPort() != config
->port() )
4715 recreateConnections
= true;
4717 else if ( AmarokConfig::mySqlDbName() != config
->database() )
4719 recreateConnections
= true;
4721 else if ( AmarokConfig::mySqlUser() != config
->username() )
4723 recreateConnections
= true;
4725 else if ( AmarokConfig::mySqlPassword() != config
->password() )
4727 recreateConnections
= true;
4730 else if ( AmarokConfig::databaseEngine().toInt() == DbConnection::postgresql
)
4732 const PostgresqlConfig
*config
=
4733 static_cast<const PostgresqlConfig
*> ( m_dbConfig
);
4734 if ( AmarokConfig::postgresqlHost() != config
->host() )
4736 recreateConnections
= true;
4738 else if ( AmarokConfig::postgresqlPort() != config
->port() )
4740 recreateConnections
= true;
4742 else if ( AmarokConfig::postgresqlDbName() != config
->database() )
4744 recreateConnections
= true;
4746 else if ( AmarokConfig::postgresqlUser() != config
->username() )
4748 recreateConnections
= true;
4750 else if ( AmarokConfig::postgresqlPassword() != config
->password() )
4752 recreateConnections
= true;
4756 if ( recreateConnections
)
4759 << "Database engine settings changed: "
4760 << "recreating DbConnections" << endl
;
4761 // If Database engine was changed, recreate DbConnections.
4764 //PORT 2.0 CollectionView::instance()->renderView();
4765 PlaylistBrowser::instance()->loadPodcastsFromDatabase();
4767 emit
databaseEngineChanged();
4771 DbConnection
* CollectionDB::getMyConnection()
4773 //after some thought, to be thread-safe, must lock at the beginning of this function,
4774 //not only if a new connection is made
4775 connectionMutex
->lock();
4777 DbConnection
*dbConn
;
4778 QThread
*currThread
= QThread::currentThread();
4780 if( threadConnections
->contains(currThread
) )
4782 QMap
<QThread
*, DbConnection
*>::Iterator it
= threadConnections
->find(currThread
);
4783 dbConn
= it
.value();
4784 connectionMutex
->unlock();
4789 if( m_dbConnType
== DbConnection::mysql
)
4791 dbConn
= new MySqlConnection( static_cast<MySqlConfig
*>( m_dbConfig
) );
4795 #ifdef USE_POSTGRESQL
4796 if( m_dbConnType
== DbConnection::postgresql
)
4798 dbConn
= new PostgresqlConnection( static_cast<PostgresqlConfig
*>( m_dbConfig
) );
4803 dbConn
= new SqliteConnection( static_cast<SqliteConfig
*>( m_dbConfig
) );
4806 threadConnections
->insert(currThread
, dbConn
);
4808 connectionMutex
->unlock();
4814 CollectionDB::releasePreviousConnection(QThread
*currThread
)
4816 //if something already exists, delete the object, and erase knowledge of it from the QMap.
4817 connectionMutex
->lock();
4818 DbConnection
*dbConn
;
4819 if (threadConnections
->contains(currThread
))
4821 QMap
<QThread
*, DbConnection
*>::Iterator it
= threadConnections
->find(currThread
);
4822 dbConn
= it
.value();
4824 threadConnections
->remove(currThread
);
4826 connectionMutex
->unlock();
4830 CollectionDB::isConnected()
4832 return getMyConnection()->isConnected();
4835 //////////////////////////////////////////////////////////////////////////////////////////
4837 //////////////////////////////////////////////////////////////////////////////////////////
4840 CollectionDB::md5sum( const QString
& artist
, const QString
& album
, const QString
& file
)
4842 KMD5
context( artist
.toLower().toLocal8Bit() + album
.toLower().toLocal8Bit() + file
.toLocal8Bit() );
4843 // debug() << "MD5 SUM for " << artist << ", " << album << ": " << context.hexDigest();
4844 return context
.hexDigest();
4848 CollectionDB::timerEvent( QTimerEvent
* )
4854 //////////////////////////////////////////////////////////////////////////////////////////
4856 //////////////////////////////////////////////////////////////////////////////////////////
4859 CollectionDB::fetchCover( QWidget
* parent
, const QString
& artist
, const QString
& album
, bool noedit
, Q3ListViewItem
* item
) //SLOT
4861 debug() << "Fetching cover for " << artist
<< " - " << album
;
4863 const bool isCompilation
= albumIsCompilation( QString::number( albumID( album
, false, false, true ) ) );
4864 CoverFetcher
* fetcher
;
4866 // avoid putting various artists in front of album title. this causes problems for locales other than US.
4867 fetcher
= new CoverFetcher( parent
, "", album
);
4869 fetcher
= new CoverFetcher( parent
, artist
, album
);
4872 itemCoverMapMutex
->lock();
4873 itemCoverMap
->insert( item
, fetcher
);
4874 itemCoverMapMutex
->unlock();
4876 connect( fetcher
, SIGNAL(result( CoverFetcher
* )), SLOT(coverFetcherResult( CoverFetcher
* )) );
4877 fetcher
->setUserCanEditQuery( !noedit
);
4878 fetcher
->startFetch();
4882 CollectionDB::scanMonitor() //SLOT
4884 if ( AmarokConfig::monitorChanges() )
4890 CollectionDB::startScan() //SLOT
4892 QStringList folders
= MountPointManager::instance()->collectionFolders();
4894 if ( folders
.isEmpty() )
4896 //dropTables( false );
4897 //createTables( false );
4898 clearTables( false );
4899 emit
scanDone( true );
4901 else if( PlaylistBrowser::instance() )
4904 ThreadManager::instance()->queueJob( new ScanController( this, false, folders
) );
4910 CollectionDB::stopScan() //SLOT
4912 ThreadManager::instance()->abortAllJobsNamed( "CollectionScanner" );
4916 //////////////////////////////////////////////////////////////////////////////////////////
4918 //////////////////////////////////////////////////////////////////////////////////////////
4921 CollectionDB::dirDirty( const QString
& path
)
4923 debug() << "Dirty: " << path
;
4927 ThreadManager::instance()->queueJob( new ScanController( this, false, dir
) );
4932 CollectionDB::coverFetcherResult( CoverFetcher
*fetcher
)
4934 if( fetcher
->wasError() ) {
4935 error() << fetcher
->errors() << endl
;
4936 emit
coverFetcherError( fetcher
->errors().front() );
4940 setAlbumImage( fetcher
->artist(), fetcher
->album(), fetcher
->image(), fetcher
->amazonURL(), fetcher
->asin() );
4941 emit
coverFetched( fetcher
->artist(), fetcher
->album() );
4944 //check the validity of the CollectionItem as it may have been deleted e.g. by a
4945 //collection scan while fetching the cover
4946 /* PORT 2.0 itemCoverMapMutex->lock();
4947 QMap<Q3ListViewItem*, CoverFetcher*>::Iterator it;
4948 for( it = itemCoverMap->begin(); it != itemCoverMap->end(); ++it )
4950 if( it.data() == fetcher )
4952 if( it.key()->isOpen() )
4953 static_cast<CollectionItem*>(it.key())->setPixmap( 0, QPixmap() );
4954 itemCoverMap->erase( it );
4957 itemCoverMapMutex
->unlock();
4961 * This query is fairly slow with sqlite, and often happens just
4962 * after the OSD is shown. Threading it restores responsivity.
4964 class SimilarArtistsInsertionJob
: public ThreadManager::DependentJob
4966 virtual bool doJob()
4968 CollectionDB::instance()->query( QString( "DELETE FROM related_artists WHERE artist = '%1';" ).arg( escapedArtist
) );
4970 const QString sql
= "INSERT INTO related_artists ( artist, suggestion, changedate ) VALUES ( '%1', '%2', 0 );";
4971 oldForeach( suggestions
)
4972 CollectionDB::instance()->insert( sql
4973 .arg( escapedArtist
,
4974 CollectionDB::instance()->escapeString( *it
) ), NULL
);
4979 virtual void completeJob() { emit
CollectionDB::instance()->similarArtistsFetched( artist
); }
4981 const QString artist
;
4982 const QString escapedArtist
;
4983 const QStringList suggestions
;
4986 SimilarArtistsInsertionJob( CollectionDB
*parent
, const QString
&s
, const QStringList
&list
)
4987 : ThreadManager::DependentJob( parent
, "SimilarArtistsInsertionJob" )
4989 , escapedArtist( parent
->escapeString( s
) )
4990 , suggestions( list
)
4995 CollectionDB::similarArtistsFetched( const QString
& artist
, const QStringList
& suggestions
)
4997 debug() << "Received similar artists\n";
4999 ThreadManager::instance()->queueJob( new SimilarArtistsInsertionJob( this, artist
, suggestions
) );
5003 CollectionDB::aftCheckPermanentTables( const QString
&currdeviceid
, const QString
&currid
, const QString
&currurl
)
5006 //debug() << "deviceid = " << currdeviceid << endl << "url = " << currurl << endl << "uid = " << currid;
5008 QStringList check1
, check2
;
5010 oldForeach( m_aftEnabledPersistentTables
)
5012 //debug() << "Checking " << (*it);;
5013 check1
= query( QString(
5014 "SELECT url, deviceid "
5016 "WHERE uniqueid = '%2';" )
5017 .arg( escapeString( *it
) )
5020 check2
= query( QString(
5021 "SELECT url, uniqueid "
5023 "WHERE deviceid = %2 AND url = '%3';" )
5024 .arg( escapeString( *it
) )
5028 if( !check1
.empty() )
5030 //debug() << "uniqueid found, updating url";
5031 query( QString( "UPDATE %1 SET deviceid = %2, url = '%4' WHERE uniqueid = '%3';" )
5032 .arg( escapeString( *it
) )
5037 else if( !check2
.empty() )
5039 //debug() << "url found, updating uniqueid";
5040 query( QString( "UPDATE %1 SET uniqueid = '%2' WHERE deviceid = %3 AND url = '%4';" )
5041 .arg( escapeString( *it
) )
5050 CollectionDB::aftMigratePermanentTablesUrl( const QString
& /*oldUrl*/, const QString
& newUrl
, const QString
& uniqueid
)
5053 int deviceid
= MountPointManager::instance()->getIdForUrl( newUrl
);
5054 QString rpath
= MountPointManager::instance()->getRelativePath( deviceid
, newUrl
);
5055 //NOTE: if ever do anything with "deleted" in the statistics table, set deleted to false in query
5056 //below; will need special case.
5057 //debug() << "deviceid = " << deviceid << endl << "newurl = " << newUrl << endl << "uid = " << uniqueid;
5058 oldForeach( m_aftEnabledPersistentTables
)
5060 query( QString( "DELETE FROM %1 WHERE deviceid = %2 AND url = '%3';" )
5061 .arg( escapeString( *it
) )
5063 .arg( escapeString( rpath
) ) );
5064 query( QString( "UPDATE %1 SET deviceid = %2, url = '%4' WHERE uniqueid = '%3';" )
5065 .arg( escapeString( *it
) )
5067 .arg( escapeString( uniqueid
) )
5068 .arg( escapeString( rpath
) ) );
5073 CollectionDB::aftMigratePermanentTablesUniqueId( const QString
& /*url*/, const QString
& oldid
, const QString
& newid
)
5076 //debug() << "oldid = " << oldid << endl << "newid = " << newid;
5077 //NOTE: if ever do anything with "deleted" in the statistics table, set deleted to false in query
5078 //below; will need special case.
5079 oldForeach( m_aftEnabledPersistentTables
)
5081 query( QString( "DELETE FROM %1 WHERE uniqueid = '%2';" )
5082 .arg( escapeString( *it
) )
5083 .arg( escapeString( newid
) ) );
5084 query( QString( "UPDATE %1 SET uniqueid = '%1' WHERE uniqueid = '%2';" )
5085 .arg( escapeString( *it
) )
5086 .arg( escapeString( newid
) )
5087 .arg( escapeString( oldid
) ) );
5092 //////////////////////////////////////////////////////////////////////////////////////////
5094 //////////////////////////////////////////////////////////////////////////////////////////
5097 CollectionDB::initialize()
5101 /// Create DBConfig instance:
5104 if ( m_dbConnType
== DbConnection::mysql
)
5106 QString appVersion
= Amarok::config( "General Options" ).readEntry( "Version" );
5107 QString passwd
= AmarokConfig::mySqlPassword2(); // stored as string type
5109 if( passwd
.isEmpty() )
5111 if( appVersion
.startsWith( "1.3" ) )
5113 /// This is because of the encrypted -> plaintext conversion
5114 passwd
= AmarokConfig::mySqlPassword(); // stored as password type
5115 AmarokConfig::setMySqlPassword2( passwd
);
5117 else if( appVersion
.startsWith( "1.4" ) )
5119 passwd
= Amarok::config( "MySql" ).readEntry( "MySqlPassword" ); //read the field as plaintext
5120 AmarokConfig::setMySqlPassword2( passwd
); // store it in plaintext field
5124 m_dbConfig
= new MySqlConfig(
5125 AmarokConfig::mySqlHost(),
5126 AmarokConfig::mySqlPort(),
5127 AmarokConfig::mySqlDbName(),
5128 AmarokConfig::mySqlUser(),
5133 #ifdef USE_POSTGRESQL
5134 if ( m_dbConnType
== DbConnection::postgresql
)
5136 QString appVersion
= Amarok::config( "General Options" ).readEntry( "Version" );
5137 QString passwd
= AmarokConfig::postgresqlPassword2();
5139 if( passwd
.isEmpty() )
5141 if( appVersion
.startsWith( "1.3" ) )
5143 /// This is because of the encrypted -> plaintext conversion
5144 passwd
= AmarokConfig::postgresqlPassword(); // stored as password type
5145 AmarokConfig::setPostgresqlPassword2( passwd
);
5147 else if( appVersion
.startsWith( "1.4" ) &&
5148 ( appVersion
.contains( "beta", false ) ||
5149 appVersion
.contains( "svn", false ) ) )
5151 passwd
= Amarok::config( "Postgresql" ).readEntry( "PostgresqlPassword" );
5152 AmarokConfig::setPostgresqlPassword2( passwd
);
5156 m_dbConfig
= new PostgresqlConfig(
5157 AmarokConfig::postgresqlHost(),
5158 AmarokConfig::postgresqlPort(),
5159 AmarokConfig::postgresqlDbName(),
5160 AmarokConfig::postgresqlUser(),
5166 m_dbConfig
= new SqliteConfig(
5167 Amarok::config( "Sqlite" ).readEntry( "location",
5168 Amarok::saveLocation() + "collection.db" ) );
5171 DbConnection
*dbConn
= getMyConnection();
5173 if ( !dbConn
->isConnected() || !dbConn
->isInitialized() )
5175 error() << "Failed to connect to or initialise database!" << endl
;
5176 Amarok::MessageQueue::instance()->addMessage( dbConn
->lastError() );
5182 //No tables seem to exist (as doing a count(url) didn't even return any number, even 0).
5183 warning() << "Tables seem to not exist." << endl
;
5184 warning() << "Attempting to create tables (this should be safe; ignore any errors)..." << endl
;
5185 createTables(false);
5186 createPersistentTables();
5187 createPodcastTables();
5189 warning() << "Tables should now definitely exist. (Stop ignoring errors)" << endl
;
5191 //Since we have created the tables, we need to make sure the version numbers are
5192 //set to the correct values. If this is not done now, the database update code may
5193 //run, which could corrupt things.
5194 Amarok::config( "Collection Browser" ).writeEntry( "Database Version", DATABASE_VERSION
);
5195 Amarok::config( "Collection Browser" ).writeEntry( "Database Stats Version", DATABASE_STATS_VERSION
);
5196 Amarok::config( "Collection Browser" ).writeEntry( "Database Persistent Tables Version", DATABASE_PERSISTENT_TABLES_VERSION
);
5197 Amarok::config( "Collection Browser" ).writeEntry( "Database Podcast Tables Version", DATABASE_PODCAST_TABLES_VERSION
); Amarok::config( "Collection Browser" ).writeEntry( "Database AFT Version", DATABASE_AFT_VERSION
);
5199 setAdminValue( "Database Version", QString::number( DATABASE_VERSION
) );
5200 setAdminValue( "Database Stats Version", QString::number( DATABASE_STATS_VERSION
) );
5201 setAdminValue( "Database Persistent Tables Version", QString::number( DATABASE_PERSISTENT_TABLES_VERSION
) );
5202 setAdminValue( "Database Podcast Tables Version", QString::number( DATABASE_PODCAST_TABLES_VERSION
) );
5203 setAdminValue( "Database AFT Version", QString::number( DATABASE_AFT_VERSION
) );
5207 // Due to a bug in our sqllite handling code, we have to recreate the indices.
5208 // We should rmeove this before 1.4.5
5209 if ( m_dbConnType
== DbConnection::sqlite
) {
5210 QStringList indices
= query( "SELECT name FROM sqlite_master WHERE type='index' ORDER BY name;" );
5211 if (!indices
.contains("url_tag")) {
5217 //updates for the Devices table go here
5218 //put all other update code into checkDatabase()
5219 //make sure that there is no call to MountPointManager in CollectionDB's ctor
5220 //or in methods called from the ctor.
5221 if ( adminValue( "Database Devices Version" ).isEmpty()
5222 && Amarok::config( "CollectionBrowser" ).readEntry( "Database Devices Version", 0 ) == 0 )
5224 createDevicesTable();
5226 else if ( adminValue( "Database Devices Version" ).toInt() != DATABASE_DEVICES_VERSION
5227 || Amarok::config( "Collection Browser" ).readEntry( "Database Devices Version", 0 ) != DATABASE_DEVICES_VERSION
)
5229 int prev
= adminValue( "Database Devices Version" ).toInt();
5231 if ( prev
> DATABASE_DEVICES_VERSION
|| prev
< 0 )
5233 error() << "Database devices version too new for this version of Amarok" << endl
;
5235 //dropDevicesTable();
5239 debug() << "Updating DEVICES table";
5240 //add future Devices update code here
5243 Amarok::config( "Collection Browser" ).writeEntry( "Database Devices Version", DATABASE_DEVICES_VERSION
);
5244 setAdminValue( "Database Devices Version", QString::number( DATABASE_DEVICES_VERSION
) );
5246 //make sure that all indices exist
5248 createPermanentIndices();
5254 CollectionDB::checkDatabase()
5259 //Inform the user that he should attach as many devices with music as possible
5260 //Hopefully this won't be necessary soon.
5262 //Currently broken, so disabled - seems to cause crashes as events are sent to
5263 //the Playlist - maybe it's not fully initialised?
5265 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." );
5266 int result = KMessageBox::warningContinueCancel( 0, text, "Database migration" );
5267 if ( result != KMessageBox::Continue )
5269 error() << "Dynamic Collection migration was aborted by user...exiting" << endl;
5274 bool needsUpdate
= ( adminValue( "Database Stats Version" ).toInt() != DATABASE_STATS_VERSION
5275 || Amarok::config( "Collection Browser" ).readEntry( "Database Stats Version", 0 ) != DATABASE_STATS_VERSION
5276 || Amarok::config( "Collection Browser" ).readEntry( "Database Version", 0 ) != DATABASE_VERSION
5277 || adminValue( "Database Version" ).toInt() != DATABASE_VERSION
5278 || Amarok::config( "Collection Browser" ).readEntry( "Database Persistent Tables Version", 0 ) != DATABASE_PERSISTENT_TABLES_VERSION
5279 || adminValue( "Database Persistent Tables Version" ).toInt() != DATABASE_PERSISTENT_TABLES_VERSION
5280 || Amarok::config( "Collection Browser" ).readEntry( "Database Podcast Tables Version", 0 ) != DATABASE_PODCAST_TABLES_VERSION
5281 || adminValue( "Database Podcast Tables Version" ).toInt() != DATABASE_PODCAST_TABLES_VERSION
5282 || Amarok::config( "Collection Browser" ).readEntry( "Database AFT Version", 0 ) != DATABASE_AFT_VERSION
5283 || adminValue( "Database AFT Version" ).toInt() != DATABASE_AFT_VERSION
);
5288 KDialog
*dialog
= new KDialog( 0 );
5289 dialog
->setCaption( i18n( "Updating database" ) );
5290 dialog
->setModal( false );
5291 dialog
->showButtonSeparator( false );
5294 /* TODO: remove the standard window controls from the dialog window, the user should not be able
5295 to close, minimize, maximize the dialog
5296 add additional text, e.g. Amarok is currently updating your database. This may take a while.
5299 Consider using a ProgressBarDialog
5301 QLabel
*label
= new QLabel( i18n( "Updating database" ), dialog
);
5302 dialog
->setMainWidget( label
);
5304 QTimer::singleShot( 0, dialog
, SLOT( show() ) );
5305 //process events in the main event loop so that the dialog actually gets shown
5306 kapp
->processEvents();
5307 debug() << "Beginning database update";
5309 updateStatsTables();
5311 updatePersistentTables();
5313 updatePodcastTables();
5315 //remove database file if version is incompatible
5316 if ( Amarok::config( "Collection Browser" ).readEntry( "Database Version", 0 ) != DATABASE_VERSION
5317 || adminValue( "Database Version" ).toInt() != DATABASE_VERSION
)
5319 debug() << "Rebuilding database!";
5321 createTables(false);
5325 emit
databaseUpdateDone();
5328 // TODO Should write to config in dtor, but it crashes...
5329 Amarok::config( "Collection Browser" ).writeEntry( "Database Version", DATABASE_VERSION
);
5330 Amarok::config( "Collection Browser" ).writeEntry( "Database Stats Version", DATABASE_STATS_VERSION
);
5331 Amarok::config( "Collection Browser" ).writeEntry( "Database Persistent Tables Version", DATABASE_PERSISTENT_TABLES_VERSION
);
5332 Amarok::config( "Collection Browser" ).writeEntry( "Database Podcast Tables Version", DATABASE_PODCAST_TABLES_VERSION
);
5333 Amarok::config( "Collection Browser" ).writeEntry( "Database AFT Version", DATABASE_AFT_VERSION
);
5335 setAdminValue( "Database Version", QString::number( DATABASE_VERSION
) );
5336 setAdminValue( "Database Stats Version", QString::number( DATABASE_STATS_VERSION
) );
5337 setAdminValue( "Database Persistent Tables Version", QString::number( DATABASE_PERSISTENT_TABLES_VERSION
) );
5338 setAdminValue( "Database Podcast Tables Version", QString::number( DATABASE_PODCAST_TABLES_VERSION
) );
5339 setAdminValue( "Database AFT Version", QString::number( DATABASE_AFT_VERSION
) );
5341 initDirOperations();
5345 CollectionDB::updateStatsTables()
5347 if ( adminValue( "Database Stats Version" ).toInt() != DATABASE_STATS_VERSION
5348 || Amarok::config( "Collection Browser" ).readEntry( "Database Stats Version", 0 ) != DATABASE_STATS_VERSION
)
5350 debug() << "Different database stats version detected! Stats table will be updated or rebuilt.";
5352 #if 0 // causes mysterious crashes
5353 if( getType() == DbConnection::sqlite
&& QFile::exists( Amarok::saveLocation()+"collection.db" ) )
5355 debug() << "Creating a backup of the database in "
5356 << Amarok::saveLocation()+"collection-backup.db" << '.' << endl
;
5358 bool copied
= KIO::NetAccess::file_copy( Amarok::saveLocation()+"collection.db",
5359 Amarok::saveLocation()+"collection-backup.db",
5360 -1 /*perms*/, true /*overwrite*/, false /*resume*/ );
5364 debug() << "Backup failed! Perhaps the volume is not writable.";
5365 debug() << "Error was: " << KIO::NetAccess::lastErrorString();
5370 int prev
= adminValue( "Database Stats Version" ).toInt();
5372 /* If config returns 3 or lower, it came from an Amarok version that was not aware of
5373 admin table, so we can't trust this table at all */
5374 if( !prev
|| ( Amarok::config( "Collection Browser" ).readEntry( "Database Stats Version", 0 )
5375 && Amarok::config( "Collection Browser" ).readEntry( "Database Stats Version", 0 ) <= 3 ) )
5376 prev
= Amarok::config( "Collection Browser" ).readEntry( "Database Stats Version", 0 );
5378 //pre somewhere in the 1.3-1.4 timeframe, the version wasn't stored in the DB, so try to guess it
5379 const QString q
= "SELECT COUNT( %1 ) FROM statistics;";
5380 if( !prev
&& query( q
.arg( "url" ) ).first().toInt()
5381 && query( q
.arg( "createdate" ) ).first().toInt()
5382 && query( q
.arg( "accessdate" ) ).first().toInt()
5383 && query( q
.arg( "percentage" ) ).first().toInt()
5384 && query( q
.arg( "playcounter" ) ).first().toInt() )
5389 if ( prev
< 3 ) //it is from before 1.2, or our poor user is otherwise fucked
5391 debug() << "Rebuilding stats-database!";
5395 else //Incrementally update the stats table to reach the present version
5397 if( prev
< 4 ) //every version from 1.2 forward had a stats version of 3
5399 debug() << "Updating stats-database!";
5400 query( "ALTER TABLE statistics ADD rating INTEGER DEFAULT 0;" );
5401 query( "CREATE INDEX rating_stats ON statistics( rating );" );
5402 query( "UPDATE statistics SET rating=0 WHERE " + boolT() + ';' );
5406 debug() << "Updating stats-database!";
5407 query( "UPDATE statistics SET rating = rating * 2;" );
5409 if( prev
< 8 ) //Versions 6, 7 and 8 all were all attempts to add columns for ATF. his code should do it all.
5411 query( QString( "CREATE TABLE statistics_fix ("
5412 "url " + textColumnType() + " UNIQUE,"
5413 "createdate INTEGER,"
5414 "accessdate INTEGER,"
5416 "rating INTEGER DEFAULT 0,"
5417 "playcounter INTEGER);" ) );
5419 insert( "INSERT INTO statistics_fix (url, createdate, accessdate, percentage, playcounter, rating)"
5420 "SELECT url, createdate, accessdate, percentage, playcounter, rating FROM statistics;"
5424 createStatsTableV8();
5426 insert( "INSERT INTO statistics (url, createdate, accessdate, percentage, playcounter, rating)"
5427 "SELECT url, createdate, accessdate, percentage, playcounter, rating FROM statistics_fix;"
5429 query( "DROP TABLE statistics_fix" );
5433 //Update for Dynamic Collection:
5435 //This is not technically for the stats table, but it is part of the same
5436 //update, so put it here anyway.
5437 MountPointManager::instance()->setCollectionFolders( Amarok::config( "Collection" ).readEntry( "Collection Folders", QStringList() ) );
5439 query( "ALTER TABLE statistics ADD deviceid INTEGER;" );
5441 //FIXME: (max) i know this is bad but its fast
5442 QStringList oldURLs
= query( "SELECT url FROM statistics;" );
5443 //it might be necessary to use batch updates to improve speed
5444 debug() << "Updating " << oldURLs
.count() << " rows in statistics";
5445 oldForeach( oldURLs
)
5447 bool exists
= QFile::exists( *it
);
5448 int deviceid
= exists
? MountPointManager::instance()->getIdForUrl( *it
) : -2;
5449 QString rpath
= exists
? MountPointManager::instance()->getRelativePath( deviceid
, *it
) : *it
;
5450 QString update
= QString( "UPDATE statistics SET deviceid = %1, url = '%2' WHERE " )
5452 .arg( escapeString( rpath
) );
5453 update
+= QString( "url = '%1';" ).arg( escapeString( *it
) );
5459 //re-using old method cause just a slight change to one column...
5460 //if people are upgrading from earlier than 11, just get the new column
5462 createStatsTableV10( true );
5463 query( "INSERT INTO statistics_fix_ten SELECT url,deviceid,createdate,"
5464 "accessdate,percentage,rating,playcounter,uniqueid,deleted FROM "
5467 createStatsTableV10( false );
5468 query( "INSERT INTO statistics SELECT * FROM statistics_fix_ten;" );
5469 query( "UPDATE statistics SET uniqueid=NULL;" );
5471 else if( prev
> DATABASE_STATS_VERSION
)
5473 error() << "Database statistics version too new for this version of Amarok. Quitting..." << endl
;
5481 CollectionDB::updatePersistentTables()
5483 QString PersistentVersion
= adminValue( "Database Persistent Tables Version" );
5484 if ( PersistentVersion
.isEmpty() )
5486 /* persistent tables didn't have a version on 1.3X and older, but let's be nice and try to
5487 copy/keep the good information instead of just deleting the tables */
5488 debug() << "Detected old schema for tables with important data. Amarok will convert the tables, ignore any \"table already exists\" errors.";
5489 createPersistentTables();
5491 debug() << "Trying to get lyrics from old db schema.";
5493 QStringList lyrics
= query( "SELECT url, lyrics FROM tags where tags.lyrics IS NOT NULL;" );
5495 for( int i
= 0; i
< lyrics
.count(); i
+= 2 )
5496 setLyrics( lyrics
[i
], lyrics
[i
+1] );
5498 debug() << "Building podcast tables";
5499 createPodcastTables();
5501 else if ( PersistentVersion
== "1" || PersistentVersion
== "2" )
5503 createPersistentTables(); /* From 1 to 2 nothing changed. There was just a bug on the code, and
5504 on some cases the table wouldn't be created.
5505 From 2 to 3, lyrics were made plain text, instead of HTML */
5506 debug() << "Converting Lyrics to Plain Text.";
5508 QStringList lyrics
= query( "SELECT url, lyrics FROM lyrics;" );
5510 for( int i
=0; i
< lyrics
.count(); i
+= 2 )
5511 setLyrics( lyrics
[i
], lyrics
[i
+1] );
5513 debug() << "Building podcast tables";
5514 createPodcastTables();
5516 else if ( PersistentVersion
.toInt() < 4 )
5518 debug() << "Building podcast tables";
5519 createPodcastTables();
5523 if ( PersistentVersion
.toInt() < 5 )
5525 debug() << "Updating podcast tables";
5526 query( "ALTER TABLE podcastchannels ADD image " + textColumnType() + ';' );
5527 query( "ALTER TABLE podcastepisodes ADD localurl " + textColumnType() + ';' );
5528 query( "ALTER TABLE podcastepisodes ADD subtitle " + textColumnType() + ';' );
5529 query( "ALTER TABLE podcastepisodes ADD size INTEGER;" );
5530 query( "ALTER TABLE podcastepisodes DROP comment;" );
5531 query( "ALTER TABLE podcastepisodes ADD comment " + longTextColumnType() + ';' );
5532 query( "CREATE INDEX localurl_podepisode ON podcastepisodes( localurl );" );
5534 if ( PersistentVersion
.toInt() < 6 )
5536 debug() << "Updating podcast tables";
5537 query( "ALTER TABLE podcastchannels ADD image " + textColumnType() + ';' );
5538 query( "ALTER TABLE podcastepisodes ADD subtitle " + textColumnType() + ';' );
5539 query( "ALTER TABLE podcastepisodes ADD size INTEGER;" );
5540 query( "ALTER TABLE podcastepisodes DROP comment;" );
5541 query( "ALTER TABLE podcastepisodes ADD comment " + longTextColumnType() + ';' );
5543 if ( PersistentVersion
.toInt() < 11 )
5545 debug() << "This is used to handle problems from uniqueid changeover and should not do anything";
5547 if ( PersistentVersion
.toInt() < 12 )
5549 debug() << "Adding playlists table...";
5550 createPersistentTablesV12();
5552 if ( PersistentVersion
.toInt() < 13 )
5554 //Update for Dynamic Collection:
5555 query( "ALTER TABLE lyrics ADD deviceid INTEGER;" );
5557 //FIXME: (max) i know this is bad but its fast
5558 QStringList oldURLs
= query( "SELECT url FROM lyrics;" );
5559 //it might be necessary to use batch updates to improve speed
5560 debug() << "Updating " << oldURLs
.count() << " rows in lyrics";
5561 oldForeach( oldURLs
)
5563 int deviceid
= MountPointManager::instance()->getIdForUrl( *it
);
5564 QString rpath
= MountPointManager::instance()->getRelativePath( deviceid
, *it
);
5565 QString update
= QString( "UPDATE lyrics SET deviceid = %1, url = '%2' WHERE " )
5567 .arg( escapeString( rpath
) );
5568 update
+= QString( "url = '%1';" ).arg( escapeString( *it
) );
5572 if ( PersistentVersion
.toInt() < 15 )
5574 createPersistentTablesV14( true );
5575 query( "INSERT INTO amazon_fix SELECT asin,locale,filename,refetchdate FROM amazon;" );
5576 query( "INSERT INTO lyrics_fix SELECT url,deviceid,lyrics FROM lyrics;" );
5577 query( "INSERT INTO playlists_fix SELECT playlist,url,tracknum FROM playlists;" );
5578 dropPersistentTablesV14();
5579 createPersistentTablesV14( false );
5580 query( "INSERT INTO amazon SELECT * FROM amazon_fix;" );
5581 query( "INSERT INTO lyrics SELECT * FROM lyrics_fix;" );
5582 query( "INSERT INTO playlists SELECT * FROM playlists_fix;" );
5584 if ( PersistentVersion
.toInt() < 17 )
5586 //drop old labels and label tables, they were never used anyway and just confuse things
5587 query( "DROP TABLE label;" );
5588 query( "DROP TABLE labels;" );
5589 query( "DROP TABLE tags_labels;" );
5590 //update for label support
5591 QString labelsAutoIncrement
= "";
5592 if ( getDbConnectionType() == DbConnection::postgresql
)
5594 query( QString( "CREATE SEQUENCE labels_seq;" ) );
5596 labelsAutoIncrement
= QString("DEFAULT nextval('labels_seq')");
5598 else if ( getDbConnectionType() == DbConnection::mysql
)
5600 labelsAutoIncrement
= "AUTO_INCREMENT";
5603 query( QString( "CREATE TABLE labels ("
5604 "id INTEGER PRIMARY KEY " + labelsAutoIncrement
+ ", "
5605 "name " + textColumnType() + ", "
5606 "type INTEGER);" ) );
5608 query( QString( "CREATE TABLE tags_labels ("
5610 "url " + exactTextColumnType() + ", "
5611 "uniqueid " + exactTextColumnType(32) + ", " //m:n relationship, DO NOT MAKE UNIQUE!
5612 "labelid INTEGER REFERENCES labels( id ) ON DELETE CASCADE );" ) );
5614 query( "CREATE UNIQUE INDEX labels_name ON labels( name, type );" );
5615 query( "CREATE INDEX tags_labels_uniqueid ON tags_labels( uniqueid );" ); //m:n relationship, DO NOT MAKE UNIQUE!
5616 query( "CREATE INDEX tags_labels_url ON tags_labels( url, deviceid );" ); //m:n relationship, DO NOT MAKE UNIQUE!
5618 if ( PersistentVersion
.toInt() < 18 )
5620 query( "ALTER TABLE lyrics ADD uniqueid " + exactTextColumnType(32) + ';' );
5621 query( "CREATE INDEX lyrics_uniqueid ON lyrics( uniqueid );" );
5623 if ( PersistentVersion
.toInt() < 19 )
5625 query( "CREATE INDEX tags_labels_labelid ON tags_labels( labelid );" ); //m:n relationship, DO NOT MAKE UNIQUE!
5627 //Up to date. Keep this number \/ in sync!
5628 if ( PersistentVersion
.toInt() > 19 || PersistentVersion
.toInt() < 0 )
5630 //Something is horribly wrong
5631 if ( adminValue( "Database Persistent Tables Version" ).toInt() != DATABASE_PERSISTENT_TABLES_VERSION
)
5633 error() << "There is a bug in Amarok: instead of destroying your valuable"
5634 << " database tables, I'm quitting" << endl
;
5637 debug() << "Rebuilding persistent tables database!";
5638 dropPersistentTables();
5639 createPersistentTables();
5646 CollectionDB::updatePodcastTables()
5648 QString PodcastVersion
= adminValue( "Database Podcast Tables Version" );
5649 if ( PodcastVersion
.toInt() < 2 )
5651 createPodcastTablesV2( true );
5652 query( "INSERT INTO podcastchannels_fix SELECT url,title,weblink,image,comment,"
5653 "copyright,parent,directory,autoscan,fetchtype,autotransfer,haspurge,"
5654 "purgecount FROM podcastchannels;" );
5655 query( "INSERT INTO podcastepisodes_fix SELECT id,url,localurl,parent,guid,title,"
5656 "subtitle,composer,comment,filetype,createdate,length,size,isNew FROM "
5657 "podcastepisodes;" );
5658 query( "INSERT INTO podcastfolders_fix SELECT id,name,parent,isOpen FROM podcastfolders;" );
5659 dropPodcastTablesV2();
5660 createPodcastTablesV2( false );
5661 query( "INSERT INTO podcastchannels SELECT * FROM podcastchannels_fix;" );
5662 query( "INSERT INTO podcastepisodes SELECT * FROM podcastepisodes_fix;" );
5663 query( "INSERT INTO podcastfolders SELECT * FROM podcastfolders_fix;" );
5666 //Keep this number in sync \/
5667 if ( PodcastVersion
.toInt() > 2 )
5669 error() << "Something is very wrong with the Podcast Tables. Aborting" << endl
;
5671 dropPodcastTables();
5672 createPodcastTables();
5677 CollectionDB::vacuum()
5679 if ( DbConnection::sqlite
== getDbConnectionType() ||
5680 DbConnection::postgresql
== getDbConnectionType() )
5682 //Clean up DB and free unused space.
5683 debug() << "Running VACUUM";
5689 CollectionDB::destroy()
5691 //do we need or want to delete the actual connection objects as well as clearing them from the QMap?
5692 //or does QMap's clear function delete them?
5693 //this situation is not at all likely to happen often, so leaving them might be okay to prevent a
5694 //thread from having its connection torn out from under it...not likely, but possible
5695 //and leaving it should not end up eating much memory at all
5697 connectionMutex
->lock();
5699 threadConnections
->clear();
5702 connectionMutex
->unlock();
5706 CollectionDB::scanModifiedDirs()
5708 if ( !m_scanInProgress
5709 //PORT 2.0 && ( !CollectionView::instance() || !CollectionView::instance()->isOrganizingFiles() )
5710 && ( !MediaBrowser::instance() || !MediaBrowser::instance()->isTranscoding() ) )
5712 //we check if a job is pending because we don't want to abort incremental collection readings
5713 if ( !ThreadManager::instance()->isJobPending( "CollectionScanner" ) && PlaylistBrowser::instance() )
5715 m_scanInProgress
= true;
5716 m_rescanRequired
= false;
5719 ThreadManager::instance()->onlyOneJob( new ScanController( this, true ) );
5723 m_rescanRequired
= true;
5728 CollectionDB::customEvent( QEvent
*e
)
5730 if ( e
->type() == (int)ScanController::JobFinishedEventType
)
5732 ScanController
* s
= static_cast<ScanController
*>( e
);
5733 m_scanInProgress
= false;
5735 if ( s
->isIncremental() )
5737 debug() << "JobFinishedEventType from Incremental ScanController received.\n";
5738 emit
scanDone( s
->hasChanged() );
5740 // check if something changed while we were scanning. in this case we should
5741 // rescan again, now.
5742 if ( m_rescanRequired
)
5743 QTimer::singleShot( 0, CollectionDB::instance(), SLOT( scanMonitor() ) );
5747 debug() << "JobFinishedEventType from ScanController received.\n";
5748 emit
scanDone( s
->wasSuccessful() );
5755 CollectionDB::loadHashFile( const QByteArray
& hash
, uint width
)
5757 //debug() << "loadHashFile: " << hash << " - " << width;
5759 QString full
= tagCoverDir().filePath( hash
);
5762 if ( QFileInfo( full
).isReadable() ) {
5763 //debug() << "loadHashFile: fullsize: " << full;
5767 if ( width
== 1 ) width
= AmarokConfig::coverPreviewSize();
5768 QByteArray widthKey
= makeWidthKey( width
);
5770 QString path
= cacheCoverDir().filePath( widthKey
+ hash
);
5771 if ( QFileInfo( path
).isReadable() ) {
5772 //debug() << "loadHashFile: scaled: " << path;
5774 } else if ( QFileInfo( full
).isReadable() ) {
5775 //debug() << "loadHashFile: scaling: " << full;
5776 QImage
image( full
);
5777 if ( image
.scaled( width
, width
, Qt::KeepAspectRatio
, Qt::SmoothTransformation
).save( path
, "PNG" ) ) {
5778 //debug() << "loadHashFile: scaled: " << path;
5788 CollectionDB::extractEmbeddedImage( const MetaBundle
&trackInformation
, QByteArray
& hash
)
5790 //debug() << "extractEmbeddedImage: " << hash << " - " << trackInformation.url().path();
5792 MetaBundle::EmbeddedImageList images
;
5793 trackInformation
.embeddedImages( images
);
5794 oldForeachType ( MetaBundle::EmbeddedImageList
, images
) {
5795 if ( hash
.isEmpty() || (*it
).hash() == hash
) {
5796 if ( (*it
).save( tagCoverDir() ) ) {
5797 //debug() << "extractEmbeddedImage: saved to " << tagCoverDir().path();
5798 hash
= (*it
).hash();
5807 CollectionDB::getLabels( const QString
&url
, const uint type
)
5809 int deviceid
= MountPointManager::instance()->getIdForUrl( url
);
5810 QString rpath
= MountPointManager::instance()->getRelativePath( deviceid
, url
);
5811 return query( QString( "SELECT labels.name FROM labels "
5812 "LEFT JOIN tags_labels ON labels.id = tags_labels.labelid "
5813 "WHERE labels.type = %1 AND tags_labels.deviceid = %2 AND tags_labels.url = '%3';" )
5814 .arg( type
).arg( deviceid
).arg( escapeString( rpath
) ) );
5818 CollectionDB::cleanLabels()
5821 QStringList labelIds
= query( "select labels.id "
5822 "from labels left join tags_labels on labels.id = tags_labels.labelid "
5823 "where tags_labels.labelid is NULL;" );
5824 if ( !labelIds
.isEmpty() )
5827 oldForeach( labelIds
)
5829 if ( !ids
.isEmpty() )
5833 query( QString( "DELETE FROM labels "
5834 "WHERE labels.id IN ( %1 );" )
5840 CollectionDB::setLabels( const QString
&url
, const QStringList
&labels
, const QString
&uid
, const uint type
)
5843 int deviceid
= MountPointManager::instance()->getIdForUrl( url
);
5844 QString rpath
= escapeString( MountPointManager::instance()->getRelativePath( deviceid
, url
) );
5845 QStringList labelIds
= query( QString( "SELECT id FROM labels WHERE type = %1;" ).arg( type
) );
5847 if ( !labelIds
.isEmpty() )
5849 oldForeach( labelIds
)
5851 if ( !ids
.isEmpty() )
5855 //TODO: max: add uniqueid handling
5856 query( QString( "DELETE FROM tags_labels "
5857 "WHERE tags_labels.labelid IN (%1) AND tags_labels.deviceid = %2 AND tags_labels.url = '%3';" )
5858 .arg( ids
, QString::number(deviceid
), rpath
) );
5861 oldForeach( labels
)
5863 int id
= query( QString( "SELECT id FROM labels WHERE type = %1 AND name = '%2';" )
5864 .arg( type
).arg( escapeString( *it
) ) ).first().toInt();
5867 id
= insert( QString( "INSERT INTO labels( name, type ) VALUES ( '%2', %1 );" )
5868 .arg( type
).arg( escapeString( *it
) ), "labels" );
5870 insert( QString( "INSERT INTO tags_labels( labelid, deviceid, url, uniqueid ) VALUES ( %1, %2, '%3', '%4' );" )
5871 .arg( QString::number(id
), QString::number(deviceid
), rpath
, escapeString( uid
) ), 0 );
5874 emit
labelsChanged( url
);
5878 CollectionDB::removeLabels( const QString
&url
, const QStringList
&labels
, const uint type
)
5881 int deviceid
= MountPointManager::instance()->getIdForUrl( url
);
5882 QString rpath
= escapeString( MountPointManager::instance()->getRelativePath( deviceid
, url
) );
5883 QString sql
= QString( "DELETE FROM tags_labels "
5884 "FROM tags_labels AS t LEFT JOIN labels AS l ON t.labelid = l.id "
5885 "WHERE l.type = %1 AND t.deviceid = %2 AND t.url = '%3' AND ( 0" )
5886 .arg( type
).arg( deviceid
).arg( rpath
);
5887 oldForeach( labels
)
5889 sql
+= QString( " OR l.name = '%1'" ).arg( escapeString( *it
) );
5894 emit
labelsChanged( url
);
5898 CollectionDB::addLabel( const QString
&url
, const QString
&label
, const QString
&uid
, const uint type
)
5901 int deviceid
= MountPointManager::instance()->getIdForUrl( url
);
5902 QString rpath
= escapeString( MountPointManager::instance()->getRelativePath( deviceid
, url
) );
5904 int id
= query( QString( "SELECT id FROM labels WHERE type = %1 AND name = '%2';" )
5905 .arg( type
).arg( escapeString( label
) ) ).first().toInt();
5906 bool labelAlreadyExists
= id
> 0;
5909 id
= insert( QString( "INSERT INTO labels( name, type ) VALUES ( '%2', %1 );" )
5910 .arg( type
).arg( escapeString( label
) ), "labels" );
5912 if ( labelAlreadyExists
)
5914 //we can return if the link between the tags row and the labels row already exists
5915 int count
= query( QString( "SELECT COUNT(*) FROM tags_labels WHERE labelid = %1 AND deviceid = %2 AND url = '%3';" )
5916 .arg( id
).arg( deviceid
).arg( rpath
) ).first().toInt();
5920 insert( QString( "INSERT INTO tags_labels( labelid, deviceid, url, uniqueid ) VALUES ( %1, %2, '%3', '%4' );" )
5921 .arg( QString::number(id
), QString::number(deviceid
), rpath
, escapeString( uid
) ), "tags_labels" );
5923 emit
labelsChanged( url
);
5928 CollectionDB::favoriteLabels( int type
, int count
)
5930 return query( QString( "SELECT labels.name, count( tags_labels.labelid ) "
5931 "FROM labels LEFT JOIN tags_labels ON labels.id = tags_labels.labelid "
5932 "WHERE labels.type = %1 GROUP BY labels.name "
5933 "ORDER BY count(tags_labels.labelid) DESC LIMIT %2;" )
5934 .arg( QString::number( type
), QString::number( count
) ) );
5938 CollectionDB::largeCoverDir() //static
5940 return QDir( Amarok::saveLocation( "albumcovers/large/" ) );
5945 CollectionDB::tagCoverDir() //static
5947 return QDir( Amarok::saveLocation( "albumcovers/tagcover/" ) );
5952 CollectionDB::cacheCoverDir() //static
5954 return QDir( Amarok::saveLocation( "albumcovers/cache/" ) );
5958 //////////////////////////////////////////////////////////////////////////////////////////
5959 // CLASS DbConnection
5960 //////////////////////////////////////////////////////////////////////////////////////////
5962 DbConnection::DbConnection()
5963 : m_initialized( false )
5967 //////////////////////////////////////////////////////////////////////////////////////////
5968 // CLASS SqliteConnection
5969 //////////////////////////////////////////////////////////////////////////////////////////
5971 SqliteConnection::SqliteConnection( const SqliteConfig
* config
)
5977 const QByteArray path
= QFile::encodeName( config
->dbFile() );
5979 // Open database file and check for correctness
5981 if ( file
.open( QIODevice::ReadOnly
) )
5984 format
= file
.readLine( 50 );
5985 if ( !format
.startsWith( "SQLite format 3" ) )
5987 warning() << "Database versions incompatible. Removing and rebuilding database.\n";
5989 else if ( sqlite3_open( path
, &m_db
) != SQLITE_OK
)
5991 warning() << "Database file corrupt. Removing and rebuilding database.\n";
5992 sqlite3_close( m_db
);
5995 m_initialized
= true;
5998 if ( !m_initialized
)
6000 // Remove old db file; create new
6001 QFile::remove( path
);
6002 if ( sqlite3_open( path
, &m_db
) == SQLITE_OK
)
6004 m_initialized
= true;
6007 if ( m_initialized
)
6009 if( sqlite3_create_function(m_db
, "rand", 0, SQLITE_UTF8
, NULL
, sqlite_rand
, NULL
, NULL
) != SQLITE_OK
)
6010 m_initialized
= false;
6011 if( sqlite3_create_function(m_db
, "power", 2, SQLITE_UTF8
, NULL
, sqlite_power
, NULL
, NULL
) != SQLITE_OK
)
6012 m_initialized
= false;
6013 if ( sqlite3_create_function(m_db
, "like", 2, SQLITE_UTF8
, NULL
, sqlite_like_new
, NULL
, NULL
) != SQLITE_OK
)
6014 m_initialized
= false;
6015 if ( sqlite3_create_function(m_db
, "like", 3, SQLITE_UTF8
, NULL
, sqlite_like_new
, NULL
, NULL
) != SQLITE_OK
)
6016 m_initialized
= false;
6019 //optimization for speeding up SQLite
6020 query( "PRAGMA default_synchronous = OFF;" );
6024 SqliteConnection::~SqliteConnection()
6026 if ( m_db
) sqlite3_close( m_db
);
6030 QStringList
SqliteConnection::query( const QString
& statement
, bool /*suppressDebug*/ )
6042 //compile SQL program to virtual machine, reattempting if busy
6046 ::usleep( 100000 ); // Sleep 100 msec
6047 debug() << "sqlite3_prepare: BUSY counter: " << busyCnt
;
6049 error
= sqlite3_prepare( m_db
, statement
.toUtf8(), -1, &stmt
, &tail
);
6051 while ( SQLITE_BUSY
==error
&& busyCnt
++ < 120 );
6053 if ( error
!= SQLITE_OK
)
6055 if ( SQLITE_BUSY
==error
)
6056 Debug::error() << "Gave up waiting for lock to clear" << endl
;
6057 Debug::error() << " sqlite3_compile error:" << endl
;
6058 Debug::error() << sqlite3_errmsg( m_db
) << endl
;
6059 Debug::error() << "on query: " << statement
<< endl
;
6060 values
= QStringList();
6066 int number
= sqlite3_column_count( stmt
);
6067 //execute virtual machine by iterating over rows
6070 error
= sqlite3_step( stmt
);
6072 if ( error
== SQLITE_BUSY
)
6074 if ( busyCnt
++ > 120 ) {
6075 Debug::error() << "Busy-counter has reached maximum. Aborting this sql statement!\n";
6078 ::usleep( 100000 ); // Sleep 100 msec
6079 debug() << "sqlite3_step: BUSY counter: " << busyCnt
;
6082 if ( error
== SQLITE_MISUSE
)
6083 debug() << "sqlite3_step: MISUSE";
6084 if ( error
== SQLITE_DONE
|| error
== SQLITE_ERROR
)
6087 //iterate over columns
6088 for ( int i
= 0; i
< number
; i
++ )
6090 values
<< QString::fromUtf8( reinterpret_cast<const char*>( sqlite3_column_text( stmt
, i
) ) );
6093 //deallocate vm resources
6094 rc
= sqlite3_finalize( stmt
);
6096 if ( error
!= SQLITE_DONE
&& rc
!= SQLITE_SCHEMA
)
6098 Debug::error() << "sqlite_step error.\n";
6099 Debug::error() << sqlite3_errmsg( m_db
) << endl
;
6100 Debug::error() << "on query: " << statement
<< endl
;
6101 values
= QStringList();
6103 if ( rc
== SQLITE_SCHEMA
)
6106 debug() << "SQLITE_SCHEMA error occurred on query: " << statement
;
6107 if ( retryCnt
< 10 )
6108 debug() << "Retrying now.";
6111 Debug::error() << "Retry-Count has reached maximum. Aborting this SQL statement!" << endl
;
6112 Debug::error() << "SQL statement: " << statement
<< endl
;
6113 values
= QStringList();
6118 while ( rc
== SQLITE_SCHEMA
&& retryCnt
< 10 );
6124 int SqliteConnection::insert( const QString
& statement
, const QString
& /* table */ )
6134 //compile SQL program to virtual machine, reattempting if busy
6138 ::usleep( 100000 ); // Sleep 100 msec
6139 debug() << "sqlite3_prepare: BUSY counter: " << busyCnt
;
6141 error
= sqlite3_prepare( m_db
, statement
.toUtf8(), -1, &stmt
, &tail
);
6143 while ( SQLITE_BUSY
==error
&& busyCnt
++ < 120 );
6145 if ( error
!= SQLITE_OK
)
6147 if ( SQLITE_BUSY
==error
)
6148 Debug::error() << "Gave up waiting for lock to clear" << endl
;
6149 Debug::error() << " sqlite3_compile error:" << endl
;
6150 Debug::error() << sqlite3_errmsg( m_db
) << endl
;
6151 Debug::error() << "on insert: " << statement
<< endl
;
6157 //execute virtual machine by iterating over rows
6160 error
= sqlite3_step( stmt
);
6162 if ( error
== SQLITE_BUSY
)
6164 if ( busyCnt
++ > 120 ) {
6165 Debug::error() << "Busy-counter has reached maximum. Aborting this sql statement!\n";
6168 ::usleep( 100000 ); // Sleep 100 msec
6169 debug() << "sqlite3_step: BUSY counter: " << busyCnt
;
6171 if ( error
== SQLITE_MISUSE
)
6172 debug() << "sqlite3_step: MISUSE";
6173 if ( error
== SQLITE_DONE
|| error
== SQLITE_ERROR
)
6176 //deallocate vm resources
6177 rc
= sqlite3_finalize( stmt
);
6179 if ( error
!= SQLITE_DONE
&& rc
!= SQLITE_SCHEMA
)
6181 Debug::error() << "sqlite_step error.\n";
6182 Debug::error() << sqlite3_errmsg( m_db
) << endl
;
6183 Debug::error() << "on insert: " << statement
<< endl
;
6185 if ( rc
== SQLITE_SCHEMA
)
6188 debug() << "SQLITE_SCHEMA error occurred on insert: " << statement
;
6189 if ( retryCnt
< 10 )
6190 debug() << "Retrying now.";
6193 Debug::error() << "Retry-Count has reached maximum. Aborting this SQL insert!" << endl
;
6194 Debug::error() << "SQL statement: " << statement
<< endl
;
6199 while ( SQLITE_SCHEMA
== rc
&& retryCnt
< 10 );
6200 return sqlite3_last_insert_rowid( m_db
);
6204 // this implements a RAND() function compatible with the MySQL RAND() (0-param-form without seed)
6205 void SqliteConnection::sqlite_rand(sqlite3_context
*context
, int /*argc*/, sqlite3_value
** /*argv*/)
6207 sqlite3_result_double( context
, static_cast<double>(KRandom::random()) / (RAND_MAX
+1.0) );
6210 // this implements a POWER() function compatible with the MySQL POWER()
6211 void SqliteConnection::sqlite_power(sqlite3_context
*context
, int argc
, sqlite3_value
**argv
)
6213 Q_ASSERT( argc
==2 );
6214 if( sqlite3_value_type(argv
[0])==SQLITE_NULL
|| sqlite3_value_type(argv
[1])==SQLITE_NULL
) {
6215 sqlite3_result_null(context
);
6218 double a
= sqlite3_value_double(argv
[0]);
6219 double b
= sqlite3_value_double(argv
[1]);
6220 sqlite3_result_double( context
, pow(a
,b
) );
6223 // this implements a LIKE() function that overrides the default string comparison function
6224 // Reason: default function is case-sensitive for utf8 strings (BUG: 116458, ...)
6225 void SqliteConnection::sqlite_like_new( sqlite3_context
*context
, int argc
, sqlite3_value
**argv
)
6228 const unsigned char *zA
= sqlite3_value_text( argv
[0] );
6229 const unsigned char *zB
= sqlite3_value_text( argv
[1] );
6231 QString pattern
= QString::fromUtf8( (const char*)zA
);
6232 QString text
= QString::fromUtf8( (const char*)zB
);
6234 int begin
= pattern
.startsWith( "%" ), end
= pattern
.endsWith( "%" );
6236 pattern
= pattern
.right( pattern
.length() - 1 );
6238 pattern
= pattern
.left( pattern
.length() - 1 );
6240 if( argc
== 3 ) // The function is given an escape character. In likeCondition() it defaults to '/'
6241 pattern
.replace( "/%", "%" ).replace( "/_", "_" ).replace( "//", "/" );
6244 if ( begin
&& end
) result
= ( text
.indexOf( pattern
, 0, Qt::CaseInsensitive
) != -1);
6245 else if ( begin
) result
= text
.endsWith( pattern
, Qt::CaseInsensitive
);
6246 else if ( end
) result
= text
.startsWith( pattern
, Qt::CaseInsensitive
);
6247 else result
= ( text
.toLower() == pattern
.toLower() );
6249 sqlite3_result_int( context
, result
);
6252 //////////////////////////////////////////////////////////////////////////////////////////
6253 // CLASS MySqlConnection
6254 //////////////////////////////////////////////////////////////////////////////////////////
6257 MySqlConnection::MySqlConnection( const MySqlConfig
* config
)
6259 , m_connected( false )
6264 m_db
= mysql_init(NULL
);
6267 // if ( config->username().isEmpty() )
6268 // pApp->slotConfigAmarok("MySql");
6270 if ( mysql_real_connect( m_db
, config
->host().toLatin1(),
6271 config
->username().toLatin1(),
6272 config
->password().toLatin1(),
6273 config
->database().toLatin1(),
6275 NULL
, CLIENT_COMPRESS
) )
6277 m_initialized
= true;
6279 #if MYSQL_VERSION_ID >= 40113
6280 // now set the right charset for the connection
6281 QStringList my_qslist
= query( "SHOW VARIABLES LIKE 'character_set_database'" );
6282 if( !my_qslist
.isEmpty() && !mysql_set_character_set( m_db
, const_cast<char *>( my_qslist
[1].toLatin1() ) ) )
6283 //charset was updated
6284 debug() << "Connection Charset is now: " << my_qslist
[1].toLatin1();
6286 error() << "Failed to set database charset\n";
6289 m_db
->reconnect
= 1; //setting reconnect flag for newer mysqld
6295 if ( mysql_real_connect(
6297 config
->host().toLatin1(),
6298 config
->username().toLatin1(),
6299 config
->password().toLatin1(),
6302 NULL
, CLIENT_COMPRESS
))
6304 if ( mysql_query(m_db
,
6305 QString( "CREATE DATABASE " + config
->database() ).toLatin1() ) )
6306 { m_connected
= true; m_initialized
= true; }
6308 { setMysqlError(); }
6316 error() << "Failed to allocate/initialize MySql struct\n";
6320 MySqlConnection::~MySqlConnection()
6322 if ( m_db
) mysql_close( m_db
);
6326 QStringList
MySqlConnection::query( const QString
& statement
, bool suppressDebug
)
6330 if ( !mysql_query( m_db
, statement
.toUtf8() ) )
6333 if ( ( result
= mysql_use_result( m_db
) ) )
6335 int number
= mysql_field_count( m_db
);
6337 while ( ( row
= mysql_fetch_row( result
) ) )
6339 for ( int i
= 0; i
< number
; i
++ )
6341 values
<< QString::fromUtf8( (const char*)row
[i
] );
6347 if ( mysql_field_count( m_db
) != 0 )
6349 if ( !suppressDebug
)
6350 debug() << "MYSQL QUERY FAILED: " << mysql_error( m_db
) << "\n" << "FAILED QUERY: " << statement
<< "\n";
6351 values
= QStringList();
6354 mysql_free_result( result
);
6358 if ( !suppressDebug
)
6359 debug() << "MYSQL QUERY FAILED: " << mysql_error( m_db
) << "\n" << "FAILED QUERY: " << statement
<< "\n";
6360 values
= QStringList();
6367 int MySqlConnection::insert( const QString
& statement
, const QString
& /* table */ )
6369 mysql_query( m_db
, statement
.toUtf8() );
6370 if ( mysql_errno( m_db
) )
6372 debug() << "MYSQL INSERT FAILED: " << mysql_error( m_db
) << "\n" << "FAILED INSERT: " << statement
;
6374 return mysql_insert_id( m_db
);
6379 MySqlConnection::setMysqlError()
6381 m_error
= i18n("MySQL reported the following error:<br>") + mysql_error(m_db
)
6382 + i18n("<p>You can configure MySQL in the Collection section under Settings->Configure Amarok</p>");
6387 //////////////////////////////////////////////////////////////////////////////////////////
6388 // CLASS PostgresqlConnection
6389 //////////////////////////////////////////////////////////////////////////////////////////
6391 #ifdef USE_POSTGRESQL
6392 PostgresqlConnection::PostgresqlConnection( const PostgresqlConfig
* config
)
6394 , m_connected( false )
6399 // if ( config->username().isEmpty() )
6400 // pApp->slotConfigAmarok("Postgresql");
6402 conninfo
= "host='" + config
->host() +
6403 "' port=" + QString::number( config
->port() ) +
6404 " dbname='" + config
->database() +
6405 "' user='" + config
->username() +
6406 "' password='" + config
->password() + '\'';
6408 m_db
= PQconnectdb( conninfo
.toUtf8() );
6412 debug() << "POSTGRESQL CONNECT FAILED: " << PQerrorMessage( m_db
) << "\n";
6413 error() << "Failed to allocate/initialize Postgresql struct\n";
6414 setPostgresqlError();
6418 if (PQstatus(m_db
) != CONNECTION_OK
)
6420 debug() << "POSTGRESQL CONNECT FAILED: " << PQerrorMessage( m_db
) << "\n";
6421 error() << "Failed to allocate/initialize Postgresql struct\n";
6422 setPostgresqlError();
6428 m_initialized
= true;
6433 PostgresqlConnection::~PostgresqlConnection()
6435 if ( m_db
) PQfinish( m_db
);
6439 QStringList
PostgresqlConnection::query( const QString
& statement
, bool suppressDebug
)
6443 ExecStatusType status
;
6445 result
= PQexec(m_db
, statement
.toUtf8());
6448 if ( !suppressDebug
)
6449 debug() << "POSTGRESQL QUERY FAILED: " << PQerrorMessage( m_db
) << "\n" << "FAILED QUERY: " << statement
<< "\n";
6453 status
= PQresultStatus(result
);
6454 if ((status
!= PGRES_COMMAND_OK
) && (status
!= PGRES_TUPLES_OK
))
6456 if ( !suppressDebug
)
6457 debug() << "POSTGRESQL QUERY FAILED: " << PQerrorMessage( m_db
) << "\n" << "FAILED QUERY: " << statement
<< "\n";
6462 int cols
= PQnfields( result
);
6463 int rows
= PQntuples( result
);
6464 QMap
<int, bool> discardCols
;
6465 for(int col
=0; col
< cols
; col
++) {
6466 if (QString(PQfname(result
, col
)) == QString("__discard") || QString(PQfname(result
, col
)) == QString("__random"))
6468 discardCols
[col
] = true;
6472 for(int row
=0; row
< rows
; row
++)
6474 for(int col
=0; col
< cols
; col
++)
6476 if (discardCols
[col
]) continue;
6478 values
<< QString::fromUtf8(PQgetvalue(result
, row
, col
));
6488 int PostgresqlConnection::insert( const QString
& statement
, const QString
& table
)
6491 ExecStatusType status
;
6495 result
= PQexec(m_db
, statement
.toUtf8());
6498 debug() << "POSTGRESQL INSERT FAILED: " << PQerrorMessage( m_db
) << "\n" << "FAILED SQL: " << statement
<< "\n";
6502 status
= PQresultStatus(result
);
6503 if (status
!= PGRES_COMMAND_OK
)
6505 debug() << "POSTGRESQL INSERT FAILED: " << PQerrorMessage( m_db
) << "\n" << "FAILED SQL: " << statement
<< "\n";
6511 if (table
== NULL
) return 0;
6513 QString _table
= table
;
6514 if (table
.find("_temp") > 0) _table
= table
.left(table
.find("_temp"));
6516 curvalSql
= QString("SELECT currval('%1_seq');").arg(_table
);
6517 result
= PQexec(m_db
, curvalSql
.toUtf8());
6520 debug() << "POSTGRESQL INSERT FAILED: " << PQerrorMessage( m_db
) << "\n" << "FAILED SQL: " << curvalSql
<< "\n";
6524 status
= PQresultStatus(result
);
6525 if (status
!= PGRES_TUPLES_OK
)
6527 debug() << "POSTGRESQL INSERT FAILED: " << PQerrorMessage( m_db
) << "\n" << "FAILED SQL: " << curvalSql
<< "\n";
6532 if ((PQnfields( result
) != 1) || (PQntuples( result
) != 1))
6534 debug() << "POSTGRESQL INSERT FAILED: " << PQerrorMessage( m_db
) << "\n" << "FAILED SQL: " << curvalSql
<< "\n";
6539 id
= QString::fromUtf8(PQgetvalue(result
, 0, 0)).toInt();
6546 void PostgresqlConnection::setPostgresqlError()
6548 m_error
= i18n("Postgresql reported the following error:<br>") + PQerrorMessage(m_db
)
6549 + i18n("<p>You can configure Postgresql in the Collection section under Settings->Configure Amarok</p>");
6555 //////////////////////////////////////////////////////////////////////////////////////////
6556 // CLASS SqliteConfig
6557 //////////////////////////////////////////////////////////////////////////////////////////
6559 SqliteConfig::SqliteConfig( const QString
& dbfile
)
6560 : m_dbfile( dbfile
)
6564 //////////////////////////////////////////////////////////////////////////////////////////
6565 // CLASS MySqlConfig
6566 //////////////////////////////////////////////////////////////////////////////////////////
6568 MySqlConfig::MySqlConfig(
6569 const QString
& host
,
6571 const QString
& database
,
6572 const QString
& username
,
6573 const QString
& password
)
6576 m_database( database
),
6577 m_username( username
),
6578 m_password( password
)
6582 //////////////////////////////////////////////////////////////////////////////////////////
6583 // CLASS PostgresqlConfig
6584 //////////////////////////////////////////////////////////////////////////////////////////
6586 PostgresqlConfig::PostgresqlConfig(
6587 const QString
& host
,
6589 const QString
& database
,
6590 const QString
& username
,
6591 const QString
& password
)
6594 m_database( database
),
6595 m_username( username
),
6596 m_password( password
)
6599 #include "collectiondb.moc"