bool CrateDAO::deleteCrate(const int crateId) {
    ScopedTransaction transaction(m_database);
    QSqlQuery query(m_database);
    query.prepare("DELETE FROM " CRATE_TRACKS_TABLE " WHERE crate_id = :id");
    query.bindValue(":id", crateId);

    if (!query.exec()) {
        LOG_FAILED_QUERY(query);
        return false;
    }

    query.prepare("DELETE FROM " CRATE_TABLE " WHERE id = :id");
    query.bindValue(":id", crateId);

    if (!query.exec()) {
        LOG_FAILED_QUERY(query);
        return false;
    }
    transaction.commit();

    emit(deleted(crateId));

    // Update in-memory map
    for (QMultiHash<int, int>::iterator it = m_cratesTrackIsIn.begin();
         it != m_cratesTrackIsIn.end();) {
        if (it.value() == crateId) {
            it = m_cratesTrackIsIn.erase(it);
        } else {
            it++;
        }
    }

    return true;
}

void PlaylistDAO::deletePlaylist(const int playlistId) {
    //qDebug() << "PlaylistDAO::deletePlaylist" << QThread::currentThread() << m_database.connectionName();
    ScopedTransaction transaction(m_database);

    // Get the playlist id for this
    QSqlQuery query(m_database);

    // Delete the row in the Playlists table.
    query.prepare("DELETE FROM Playlists "
                  "WHERE id= :id");
    query.bindValue(":id", playlistId);
    if (!query.exec()) {
        LOG_FAILED_QUERY(query);
        return;
    }

    // Delete the tracks in this playlist from the PlaylistTracks table.
    query.prepare("DELETE FROM PlaylistTracks "
                  "WHERE playlist_id = :id");
    query.bindValue(":id", playlistId);
    if (!query.exec()) {
        LOG_FAILED_QUERY(query);
        return;
    }

    transaction.commit();
    //TODO: Crap, we need to shuffle the positions of all the playlists?

    emit(deleted(playlistId));
}

void AnalysisDao::deleteAnalyses(const QList<TrackId>& trackIds) {
    QStringList idList;
    for (const auto& trackId: trackIds) {
        idList << trackId.toString();
    }
    QSqlQuery query(m_db);
    query.prepare(QString("SELECT track_analysis.id FROM track_analysis WHERE "
                          "track_id in (%1)").arg(idList.join(",")));
    if (!query.exec()) {
        LOG_FAILED_QUERY(query) << "couldn't delete analysis";
    }
    const int idColumn = query.record().indexOf("id");
    while (query.next()) {
        int id = query.value(idColumn).toInt();
        QString dataPath = getAnalysisStoragePath().absoluteFilePath(
                            QString::number(id));
        qDebug() << dataPath;
        deleteFile(dataPath);
    }
    query.prepare(QString("DELETE FROM track_analysis "
                          "WHERE track_id in (%1)").arg(idList.join(",")));
    if (!query.exec()) {
        LOG_FAILED_QUERY(query) << "couldn't delete analysis";
    }
}

void CrateDAO::populateCrateMembershipCache() {
    // get the count to allocate HashMap
    int tracksInCratesCount = 0;
    QSqlQuery query(m_database);
    query.prepare("SELECT COUNT(*) from " CRATE_TRACKS_TABLE);

    if (!query.exec()) {
        LOG_FAILED_QUERY(query);
    }

    tracksInCratesCount = query.value(0).toInt();

    m_cratesTrackIsIn.reserve(tracksInCratesCount);

    // now fetch all Tracks from all crates and insert them into the hashmap
    query.prepare("SELECT track_id, crate_id from " CRATE_TRACKS_TABLE);
    if (!query.exec()) {
        LOG_FAILED_QUERY(query);
    }

    const int trackIdColumn = query.record().indexOf("track_id");
    const int crateIdColumn = query.record().indexOf("crate_id");
    while (query.next()) {
        m_cratesTrackIsIn.insert(query.value(trackIdColumn).toInt(),
                                 query.value(crateIdColumn).toInt());
    }
}

void BaseExternalPlaylistModel::setPlaylist(QString playlist_path) {
    QSqlQuery finder_query(m_database);
    finder_query.prepare(QString("SELECT id from %1 where name=:name").arg(m_playlistsTable));
    finder_query.bindValue(":name", playlist_path);

    if (!finder_query.exec()) {
        LOG_FAILED_QUERY(finder_query) << "Error getting id for playlist:" << playlist_path;
        return;
    }

    // TODO(XXX): Why not last-insert id?
    int playlistId = -1;
    QSqlRecord finder_query_record = finder_query.record();
    while (finder_query.next()) {
        playlistId = finder_query.value(
                finder_query_record.indexOf("id")).toInt();
    }

    if (playlistId == -1) {
        qDebug() << "ERROR: Could not get the playlist ID for playlist:" << playlist_path;
        return;
    }

    QString playlistViewTable = QString("%1_%2").arg(m_playlistTracksTable,
                                                     QString::number(playlistId));

    QStringList columns;
    columns << "track_id";
    columns << "position";
    columns << "'' AS " + LIBRARYTABLE_PREVIEW;


    QSqlQuery query(m_database);
    FieldEscaper f(m_database);
    QString queryString = QString(
        "CREATE TEMPORARY VIEW IF NOT EXISTS %1 AS "
        "SELECT %2 FROM %3 WHERE playlist_id = %4")
            .arg(f.escapeString(playlistViewTable),
                 columns.join(","),
                 m_playlistTracksTable,
                 QString::number(playlistId));
    query.prepare(queryString);

    if (!query.exec()) {
        LOG_FAILED_QUERY(query) << "Error creating temporary view for playlist.";
        return;
    }

    columns[2] = LIBRARYTABLE_PREVIEW;
    setTable(playlistViewTable, columns[0], columns, m_trackSource);
    setDefaultSort(fieldIndex(ColumnCache::COLUMN_PLAYLISTTRACKSTABLE_POSITION),
                   Qt::AscendingOrder);
    setSearch("");
}

int CrateDAO::getCrateIdByName(const QString& name) {
    QString pattern("(.*)\x20(\\(([1-9]\\d*|0)\\))");
    QRegExp rxnum(pattern);

    QSqlQuery query(m_database);
    query.prepare("SELECT id,name FROM " CRATE_TABLE);

    if (query.exec()) {
        while (query.next()) {
            int queryID = query.value(0).toInt();
        	QString queryName = query.value(1).toString();

            if (queryName == name) {
                return queryID;
            } else if (rxnum.exactMatch(queryName)) {
                QString originalName = rxnum.cap(1);
                if (originalName == name) {
                    return queryID;
                }
            }
        }
        return -1;
    } else {
        LOG_FAILED_QUERY(query);
    }
    return -1;
}

BaseExternalTrackModel::BaseExternalTrackModel(QObject* parent,
                                               TrackCollection* pTrackCollection,
                                               const char* settingsNamespace,
                                               const QString& trackTable,
                                               QSharedPointer<BaseTrackCache> trackSource)
        : BaseSqlTableModel(parent, pTrackCollection, settingsNamespace) {
    QString viewTable = trackTable + "_view";
    QStringList columns;
    columns << "id";
    columns << "'' AS " + LIBRARYTABLE_PREVIEW;

    QSqlQuery query(m_database);
    FieldEscaper f(m_database);
    QString queryString = QString(
        "CREATE TEMPORARY VIEW IF NOT EXISTS %1 AS "
        "SELECT %2 FROM %3")
            .arg(f.escapeString(viewTable),
                 columns.join(","),
                 f.escapeString(trackTable));
    query.prepare(queryString);

    if (!query.exec()) {
        LOG_FAILED_QUERY(query) <<
                "Error creating temporary view for" << trackTable;
        return;
    }

    columns[1] = LIBRARYTABLE_PREVIEW;
    setTable(viewTable, columns[0], columns, trackSource);
    setDefaultSort(fieldIndex("artist"), Qt::AscendingOrder);
}

int PlaylistDAO::getPlaylistIdFromName(QString name) {
    //qDebug() << "PlaylistDAO::getPlaylistIdFromName" << QThread::currentThread() << m_database.connectionName();
    // since now showing the number of the tracks in every playlist is through change the "name" column
    // in "playlists" table, so here we need a regular expression to match.
    QString pattern("(.*)\x20(\\(([1-9]\\d*|0)\\))");
    QRegExp rxnum(pattern);

    QSqlQuery query(m_database);
    query.prepare("SELECT id,name FROM Playlists");

    if (query.exec()) {
        while (query.next()) {
            int queryID = query.value(0).toInt();
        	QString queryName = query.value(1).toString();

            //qDebug() << "queryID:" << queryID
            //         << "queryName:" << queryName;

            if (queryName == name) {
                return queryID;
            } else if (rxnum.exactMatch(queryName)) {
                QString originalName = rxnum.cap(1);
                if (originalName == name) {
                    return queryID;
                }
            }
        }
        return -1;
    } else {
        LOG_FAILED_QUERY(query);
    }
    return -1;
}

int CrateDAO::addTracksToCrate(const int crateId, QList<int>* trackIdList) {
    ScopedTransaction transaction(m_database);
    QSqlQuery query(m_database);
    query.prepare("INSERT INTO " CRATE_TRACKS_TABLE " (crate_id, track_id) VALUES (:crate_id, :track_id)");

    for (int i = 0; i < trackIdList->size(); ++i) {
        query.bindValue(":crate_id", crateId);
        query.bindValue(":track_id", trackIdList->at(i));
        if (!query.exec()) {
            LOG_FAILED_QUERY(query);
            // We must emit only those trackID that were added so we need to
            // remove the failed ones.
            trackIdList->removeAt(i);
            --i; // account for reduced size of list
        }
    }
    transaction.commit();

    // Emitting the trackAdded signals for each trackID outside the transaction
    foreach(int trackId, *trackIdList) {
        emit(trackAdded(crateId, trackId));
    }

    emit(changed(crateId));

    // Return the number of tracks successfully added
    return trackIdList->size();
}

QList<Cue*> CueDAO::getCuesForTrack(const int trackId) const {
    //qDebug() << "CueDAO::getCuesForTrack" << QThread::currentThread() << m_database.connectionName();
    QList<Cue*> cues;
    QSqlQuery query(m_database);
    query.prepare("SELECT * FROM " CUE_TABLE " WHERE track_id = :id");
    query.bindValue(":id", trackId);
    if (query.exec()) {
        while (query.next()) {
            Cue* cue = NULL;
            int cueId = query.value(query.record().indexOf("id")).toInt();
            if (m_cues.contains(cueId)) {
                cue = m_cues[cueId];
            }
            if (cue == NULL) {
                cue = cueFromRow(query);
            }
            if (cue != NULL) {
                cues.push_back(cue);
            }
        }
    } else {
        LOG_FAILED_QUERY(query);
    }
    return cues;
}

void LibraryTableModel::setTableModel(int id){
    Q_UNUSED(id);
    QStringList columns;
    columns << "library."+LIBRARYTABLE_ID << "'' as preview";

    QString tableName = "library_view";

    QSqlQuery query(m_pTrackCollection->getDatabase());
    QString queryString = "CREATE TEMPORARY VIEW IF NOT EXISTS "+tableName+" AS "
            "SELECT " + columns.join(", ") +
            " FROM library INNER JOIN track_locations "
            "ON library.location = track_locations.id "
            "WHERE (" + LibraryTableModel::DEFAULT_LIBRARYFILTER + ")";
    query.prepare(queryString);
    if (!query.exec()) {
        LOG_FAILED_QUERY(query);
    }

    QStringList tableColumns;
    tableColumns << LIBRARYTABLE_ID;
    tableColumns << "preview";
    setTable(tableName, LIBRARYTABLE_ID, tableColumns,
             m_pTrackCollection->getTrackSource("default"));

    // BaseSqlTabelModel will setup the header info
    initHeaderData();

    setSearch("");
    setDefaultSort(fieldIndex("artist"), Qt::AscendingOrder);
}

void BaseSqlTableModel::select() {
    if (!m_bInitialized) {
        return;
    }
    // We should be able to detect when a select() would be a no-op. The DAO's
    // do not currently broadcast signals for when common things happen. In the
    // future, we can turn this check on and avoid a lot of needless
    // select()'s. rryan 9/2011
    // if (!m_bDirty) {
    //     if (sDebug) {
    //         qDebug() << this << "Skipping non-dirty select()";
    //     }
    //     return;
    // }

    if (sDebug) {
        qDebug() << this << "select()";
    }

    QTime time;
    time.start();

    QString columns = m_tableColumnsJoined;
    QString orderBy = orderByClause();
    QString queryString = QString("SELECT %1 FROM %2 %3")
            .arg(columns, m_tableName, orderBy);

    if (sDebug) {
        qDebug() << this << "select() executing:" << queryString;
    }

    QSqlQuery query(m_database);
    // This causes a memory savings since QSqlCachedResult (what QtSQLite uses)
    // won't allocate a giant in-memory table that we won't use at all.
    query.setForwardOnly(true);
    query.prepare(queryString);

    if (!query.exec()) {
        LOG_FAILED_QUERY(query);
        return;
    }

    // Remove all the rows from the table. We wait to do this until after the
    // table query has succeeded. See Bug #1090888.
    // TODO(rryan) we could edit the table in place instead of clearing it?
    if (m_rowInfo.size() > 0) {
        beginRemoveRows(QModelIndex(), 0, m_rowInfo.size()-1);
        m_rowInfo.clear();
        m_trackIdToRows.clear();
        endRemoveRows();
    }

    QSqlRecord record = query.record();
    int idColumn = record.indexOf(m_idColumn);

    QLinkedList<int> tableColumnIndices;
    foreach (QString column, m_tableColumns) {
        Q_ASSERT(record.indexOf(column) == m_tableColumnIndex[column]);
        tableColumnIndices.push_back(record.indexOf(column));
    }

int PlaylistDAO::createPlaylist(QString name, HiddenType hidden)
{
    // qDebug() << "PlaylistDAO::createPlaylist"
    //          << QThread::currentThread()
    //          << m_database.connectionName();
    //Start the transaction
    ScopedTransaction transaction(m_database);

    //Find out the highest position for the existing playlists so we know what
    //position this playlist should have.
    QSqlQuery query(m_database);
    query.prepare("SELECT max(position) as posmax FROM Playlists");

    if (!query.exec()) {
        LOG_FAILED_QUERY(query);
        return -1;
    }

    //Get the id of the last playlist.
    int position = 0;
    if (query.next()) {
        position = query.value(query.record().indexOf("posmax")).toInt();
        position++; //Append after the last playlist.
    }

    //qDebug() << "Inserting playlist" << name << "at position" << position;

    query.prepare("INSERT INTO Playlists (name, position, hidden, date_created, date_modified) "
                  "VALUES (:name, :position, :hidden,  CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)");
    query.bindValue(":name", name);
    query.bindValue(":position", position);
    query.bindValue(":hidden", static_cast<int>(hidden));

    if (!query.exec()) {
        LOG_FAILED_QUERY(query);
        return -1;
    }

    int playlistId = query.lastInsertId().toInt();
    //Commit the transaction
    transaction.commit();
    updatePlaylistsTitleNum();
    emit(added(playlistId));
    return playlistId;
}

unsigned int CrateDAO::crateCount() {
    QSqlQuery query(m_database);
    query.prepare("SELECT count(*) FROM " CRATE_TABLE);

    if (!query.exec() || !query.next()) {
        LOG_FAILED_QUERY(query);
        return 0;
    }
    return query.value(0).toInt();
}

LibraryScanner::~LibraryScanner() {
    // IMPORTANT NOTE: This code runs in the GUI thread, so it should _NOT_ use
    //                the m_trackDao that lives inside this class. It should use
    //                the DAOs that live in m_pTrackCollection.

    if (isRunning()) {
        // Cancel any running library scan...
        cancel();
        wait(); // Wait for thread to finish
    }

    // Do housekeeping on the LibraryHashes table.
    ScopedTransaction transaction(m_pCollection->getDatabase());

    // Mark the corresponding file locations in the track_locations table as deleted
    // if we find one or more deleted directories.
    QStringList deletedDirs;
    QSqlQuery query(m_pCollection->getDatabase());
    query.prepare("SELECT directory_path FROM LibraryHashes "
                  "WHERE directory_deleted=1");
    if (query.exec()) {
        const int directoryPathColumn = query.record().indexOf("directory_path");
        while (query.next()) {
            QString directory = query.value(directoryPathColumn).toString();
            deletedDirs << directory;
        }
    } else {
        LOG_FAILED_QUERY(query) << "Couldn't SELECT deleted directories.";
    }

    // Delete any directories that have been marked as deleted...
    query.finish();
    query.exec("DELETE FROM LibraryHashes "
               "WHERE directory_deleted=1");

    // Print out any SQL error, if there was one.
    if (query.lastError().isValid()) {
        LOG_FAILED_QUERY(query);
    }

    foreach (QString dir, deletedDirs) {
        m_pCollection->getTrackDAO().markTrackLocationsAsDeleted(dir);
    }

void LibraryHashDAO::removeDeletedDirectoryHashes()
{
    QSqlQuery query(m_database);
    query.prepare("DELETE FROM LibraryHashes WHERE "
               "directory_deleted=:directory_deleted");
    query.bindValue(":directory_deleted", 1);
    if (!query.exec()) {
        LOG_FAILED_QUERY(query);
    }
}

void BansheePlaylistModel::dropTempTable() {
    if (m_playlistId >= 0) {
        // Clear old playlist
        m_playlistId = -1;
        QSqlQuery query(m_pTrackCollection->database());
        QString strQuery("DROP TABLE IF EXISTS %1");
        if (!query.exec(strQuery.arg(m_tempTableName))) {
            LOG_FAILED_QUERY(query);
        }
    }
}

void PlaylistDAO::renamePlaylist(const int playlistId, const QString& newName) {
    QSqlQuery query(m_database);
    query.prepare("UPDATE Playlists SET name = :name WHERE id = :id");
    query.bindValue(":name", newName);
    query.bindValue(":id", playlistId);
    if (!query.exec()) {
        LOG_FAILED_QUERY(query);
        return;
    }
    emit(renamed(playlistId, newName));
}

int DirectoryDAO::removeDirectory(const QString& dir) {
    QSqlQuery query(m_database);
    query.prepare("DELETE FROM " % DIRECTORYDAO_TABLE  % " WHERE "
                   % DIRECTORYDAO_DIR % "= :dir");
    query.bindValue(":dir", dir);
    if (!query.exec()) {
        LOG_FAILED_QUERY(query) << "purging dir (" % dir % ") failed";
        return SQL_ERROR;
    }
    return ALL_FINE;
}

void PlaylistTableModel::setTableModel(int playlistId) {
    //qDebug() << "PlaylistTableModel::setTableModel" << playlistId;
    if (m_iPlaylistId == playlistId) {
        qDebug() << "Already focused on playlist " << playlistId;
        return;
    }

    m_iPlaylistId = playlistId;
    QString playlistTableName = "playlist_" + QString::number(m_iPlaylistId);
    QSqlQuery query(m_database);
    FieldEscaper escaper(m_database);

    QStringList columns;
    columns << PLAYLISTTRACKSTABLE_TRACKID + " AS " + LIBRARYTABLE_ID
            << PLAYLISTTRACKSTABLE_POSITION
            << PLAYLISTTRACKSTABLE_DATETIMEADDED
            << "'' AS " + LIBRARYTABLE_PREVIEW
            // For sorting the cover art column we give LIBRARYTABLE_COVERART
            // the same value as the cover hash.
            << LIBRARYTABLE_COVERART_HASH + " AS " + LIBRARYTABLE_COVERART;

    // We drop files that have been explicitly deleted from mixxx
    // (mixxx_deleted=0) from the view. There was a bug in <= 1.9.0 where
    // removed files were not removed from playlists, so some users will have
    // libraries where this is the case.
    QString queryString = QString("CREATE TEMPORARY VIEW IF NOT EXISTS %1 AS "
                                  "SELECT %2 FROM PlaylistTracks "
                                  "INNER JOIN library ON library.id = PlaylistTracks.track_id "
                                  "WHERE PlaylistTracks.playlist_id = %3")
                          .arg(escaper.escapeString(playlistTableName),
                               columns.join(","),
                               QString::number(playlistId));
    if (!m_showAll) {
        queryString.append(" AND library.mixxx_deleted = 0");
    }
    query.prepare(queryString);
    if (!query.exec()) {
        LOG_FAILED_QUERY(query);
    }

    columns[0] = LIBRARYTABLE_ID;
    // columns[1] = PLAYLISTTRACKSTABLE_POSITION from above
    // columns[2] = PLAYLISTTRACKSTABLE_DATETIMEADDED from above
    columns[3] = LIBRARYTABLE_PREVIEW;
    columns[4] = LIBRARYTABLE_COVERART;
    setTable(playlistTableName, LIBRARYTABLE_ID, columns,
            m_pTrackCollection->getTrackSource());
    setSearch("");
    setDefaultSort(fieldIndex(ColumnCache::COLUMN_PLAYLISTTRACKSTABLE_POSITION), Qt::AscendingOrder);
    setSort(defaultSortColumn(), defaultSortOrder());

    connect(&m_playlistDao, SIGNAL(changed(int)),
            this, SLOT(playlistChanged(int)));
}