From 67a0e925ff1efc839ecb067d605bb45ebebf866a Mon Sep 17 00:00:00 2001 From: Jon Bergli Heier Date: Fri, 4 Feb 2011 20:45:04 +0100 Subject: Moved all SQL code to database.cpp. --- database.cpp | 110 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ database.h | 27 +++++++++++++++ music.cpp | 107 +++++++-------------------------------------------------- music.h | 7 ++-- 4 files changed, 155 insertions(+), 96 deletions(-) create mode 100644 database.cpp create mode 100644 database.h diff --git a/database.cpp b/database.cpp new file mode 100644 index 0000000..ea87e17 --- /dev/null +++ b/database.cpp @@ -0,0 +1,110 @@ +#include "database.h" +#include "config.h" + +#include +#include + +Database::Database() { + sql.open(config::vm["audist.database"].as()); +} + +Database::~Database() { + sql.close(); +} + +// NOTE: LIKE is case-insensitive by default on sqlite +std::vector Database::find(std::map search) { + soci::session sql(config::vm["audist.database"].as()); + soci::statement st(sql); + std::string filename, artist, album, title; + st.exchange(soci::into(filename)); + std::string query = "SELECT file_name FROM tracks WHERE "; + std::vector where_conditions; + + if(search.find("artist") != search.end()) { + where_conditions.push_back("artist_id IN (SELECT id FROM artists WHERE name LIKE :artist)"); + artist = "%"+search["artist"]+"%"; + st.exchange(soci::use(artist, "artist")); + } + + if(search.find("album") != search.end()) { + where_conditions.push_back("album_id IN (SELECT id FROM albums WHERE name LIKE :album)"); + album = "%"+search["album"]+"%"; + st.exchange(soci::use(album, "album")); + } + + if(search.find("title") != search.end()) { + where_conditions.push_back("name LIKE :title"); + artist = "%"+search["title"]+"%"; + st.exchange(soci::use(title, "title")); + } + + query += boost::algorithm::join(where_conditions, " AND "); + st.alloc(); + st.prepare(query); + st.define_and_bind(); + st.execute(true); + + std::vector results; + while(st.fetch()) { + MusicListing::p ml(new MusicTrack(filename)); + results.push_back(ml); + } + sql.close(); + + return results; +} + +std::vector Database::find(std::string search) { + soci::session sql(config::vm["audist.database"].as()); + + search = "%"+search+"%"; + soci::rowset rs = (sql.prepare << "SELECT DISTINCT file_name FROM tracks WHERE name LIKE :search OR file_name LIKE :search OR " + "artist_id IN (SELECT id FROM artists WHERE name LIKE :search) OR album_id IN (SELECT id FROM albums WHERE name LIKE :search)", + soci::use(search, "search")); + + std::vector results; + for(soci::rowset::const_iterator it = rs.begin(); it != rs.end(); it++) { + MusicListing::p ml(new MusicTrack(*it)); + results.push_back(ml); + } + + return results; +} + +// NOTE: last_insert_rowid() is sqlite-specific +void Database::update(fs::path track, Tag::p tag) { + int64_t artist_id = 0, album_id = 0, track_id = 0; + + if(tag->has_field("artist")) { + sql << "SELECT id FROM artists WHERE name = :name", soci::use(tag->fields["artist"]), soci::into(artist_id); + if(!sql.got_data()) { + sql << "INSERT INTO artists (name) VALUES (:name)", soci::use(tag->fields["artist"]); + sql << "SELECT last_insert_rowid()", soci::into(artist_id); + } + } + + if(tag->has_field("album")) { + std::string query(boost::str(boost::format("SELECT id FROM albums WHERE %s AND name = :name") % + (artist_id ? boost::str(boost::format("artist_id = %d") % artist_id) : "artist_id IS NULL"))); + sql << query, soci::use(tag->fields["album"]), soci::into(album_id); + if(!sql.got_data()) { + soci::indicator ind = (artist_id ? soci::i_ok : soci::i_null); + sql << "INSERT INTO albums (artist_id, name) VALUES (:artist_id, :name)", soci::use(artist_id, ind), soci::use(tag->fields["album"]); + sql << "SELECT last_insert_rowid()", soci::into(album_id); + } + } + + if(tag->has_field("title")) { + std::string query(boost::str(boost::format("SELECT id FROM tracks WHERE %s AND %s AND name = :name") % + (artist_id ? boost::str(boost::format("artist_id = %d") % artist_id) : "artist_id IS NULL") % + (album_id ? boost::str(boost::format("album_id = %d") % album_id) : "album_id IS NULL"))); + sql << query, soci::use(tag->fields["title"]), soci::into(track_id); + if(!sql.got_data()) { + soci::indicator artist_ind = (artist_id ? soci::i_ok : soci::i_null), + album_ind = (album_id ? soci::i_ok : soci::i_null); + sql << "INSERT INTO tracks (artist_id, album_id, name, file_name) VALUES (:artist_id, :album_id, :name, :file_name)", + soci::use(artist_id, artist_ind), soci::use(album_id, album_ind), soci::use(tag->fields["title"]), soci::use(track.string()); + } + } +} diff --git a/database.h b/database.h new file mode 100644 index 0000000..a6ec5ad --- /dev/null +++ b/database.h @@ -0,0 +1,27 @@ +#ifndef DATABASE_H +#define DATABASE_H + +#include "music.h" +#include "tag.h" + +#include +#include + +#include +#include +#include + +class Database { + private: + soci::session sql; + + public: + Database(); + virtual ~Database(); + + std::vector find(std::map search); + std::vector find(std::string search); + void update(fs::path track, Tag::p tag); +}; + +#endif diff --git a/music.cpp b/music.cpp index b9cb05d..de5575f 100644 --- a/music.cpp +++ b/music.cpp @@ -3,6 +3,7 @@ #include "encoder.h" #include "tag.h" #include "config.h" +#include "database.h" #include #include @@ -77,65 +78,16 @@ MusicDirectory::p music::get_directory(const std::string& path) { * Does a search on specific fields given by \p search. */ std::vector music::find(std::map search) { - soci::session sql(config::vm["audist.database"].as()); - soci::statement st(sql); - std::string filename, artist, album, title; - st.exchange(soci::into(filename)); - std::string query = "SELECT file_name FROM tracks WHERE "; - std::vector where_conditions; - - if(search.find("artist") != search.end()) { - where_conditions.push_back("artist_id IN (SELECT id FROM artists WHERE name LIKE :artist)"); - artist = "%"+search["artist"]+"%"; - st.exchange(soci::use(artist, "artist")); - } - - if(search.find("album") != search.end()) { - where_conditions.push_back("album_id IN (SELECT id FROM albums WHERE name LIKE :album)"); - album = "%"+search["album"]+"%"; - st.exchange(soci::use(album, "album")); - } - - if(search.find("title") != search.end()) { - where_conditions.push_back("name LIKE :title"); - artist = "%"+search["title"]+"%"; - st.exchange(soci::use(title, "title")); - } - - query += boost::algorithm::join(where_conditions, " AND "); - st.alloc(); - st.prepare(query); - st.define_and_bind(); - st.execute(true); - - std::vector results; - while(st.fetch()) { - MusicListing::p ml(new MusicTrack(filename)); - results.push_back(ml); - } - sql.close(); - - return results; + Database db; + return db.find(search); } /** Find tracks in the database. * Returns tracks where title, artist, album or filename matches \p search. */ std::vector music::find(std::string search) { - soci::session sql(config::vm["audist.database"].as()); - - search = "%"+search+"%"; - soci::rowset rs = (sql.prepare << "SELECT DISTINCT file_name FROM tracks WHERE name LIKE :search OR file_name LIKE :search OR " - "artist_id IN (SELECT id FROM artists WHERE name LIKE :search) OR album_id IN (SELECT id FROM albums WHERE name LIKE :search)", - soci::use(search, "search")); - - std::vector results; - for(soci::rowset::const_iterator it = rs.begin(); it != rs.end(); it++) { - MusicListing::p ml(new MusicTrack(*it)); - results.push_back(ml); - } - - return results; + Database db; + return db.find(search); } /** Initiate an update on \p path and its subdirs. @@ -145,60 +97,27 @@ void music::begin_update(const std::string path) { MusicDirectory::p dir = get_directory(path); std::cout << boost::format("updater(%s) called") % path << std::endl; if(dir) { - dir->update(); + Database db; + dir->update(db); } } /** Recursively update \p dir and its subdirectories. */ -void MusicDirectory::update() { - // TODO: Fix engine-specific SQL syntax inside this function. - soci::session sql(config::vm["audist.database"].as()); - - BOOST_FOREACH(fs::path t, dir.tracks) { - std::cout << "track " << t << std::endl; - Tag::p tag = Tag::load(t.string()); +void MusicDirectory::update(Database& db) { + BOOST_FOREACH(fs::path track, tracks) { + std::cout << "track " << track << std::endl; + Tag::p tag = Tag::load(track.string()); BOOST_FOREACH(Tag::Fields::value_type& f, tag->fields) { std::cout << boost::format(" %s: %s") % f.first % f.second << std::endl; } - int artist_id = 0, album_id = 0, track_id = 0; - - if(tag->has_field("artist")) { - sql << "SELECT id FROM artists WHERE name = :name", soci::use(tag->fields["artist"]), soci::into(artist_id); - if(!sql.got_data()) - sql << "INSERT INTO artists (name) VALUES (:name) RETURNING id", soci::use(tag->fields["artist"]), soci::into(artist_id); - } - - if(tag->has_field("album")) { - std::string query(boost::str(boost::format("SELECT id FROM albums WHERE %s AND name = :name") % - (artist_id ? boost::str(boost::format("artist_id = %d") % artist_id) : "artist_id IS NULL"))); - sql << query, soci::use(tag->fields["album"]), soci::into(album_id); - if(!sql.got_data()) { - soci::indicator ind = (artist_id ? soci::i_ok : soci::i_null); - sql << "INSERT INTO albums (artist_id, name) VALUES (:artist_id, :name) RETURNING id", - soci::use(artist_id, ind), soci::use(tag->fields["album"]), soci::into(album_id); - } - } - - if(tag->has_field("title")) { - std::string query(boost::str(boost::format("SELECT id FROM tracks WHERE %s AND %s AND name = :name") % - (artist_id ? boost::str(boost::format("artist_id = %d") % artist_id) : "artist_id IS NULL") % - (album_id ? boost::str(boost::format("album_id = %d") % album_id) : "album_id IS NULL"))); - sql << query, soci::use(tag->fields["title"]), soci::into(track_id); - if(!sql.got_data()) { - soci::indicator artist_ind = (artist_id ? soci::i_ok : soci::i_null), - album_ind = (album_id ? soci::i_ok : soci::i_null); - sql << "INSERT INTO tracks (artist_id, album_id, name, file_name) VALUES (:artist_id, :album_id, :name, :file_name)", - soci::use(artist_id, artist_ind), soci::use(album_id, album_ind), soci::use(tag->fields["title"]), soci::use(t.string()); - } - } + db.update(track, tag); } - sql.close(); for(PathListings::iterator it = directories.begin(); it != directories.end(); it++) { MusicDirectory dir(*it); - dir.update(); + dir.update(db); } } diff --git a/music.h b/music.h index db69f5a..17f99f2 100644 --- a/music.h +++ b/music.h @@ -25,6 +25,9 @@ class MusicTrack : public MusicListing { virtual void render(HTTP::Connection::p req); }; +// Forward declaration for MusicDirectory::update() +class Database; + //! Represents a directory. class MusicDirectory : public MusicListing { public: @@ -35,7 +38,7 @@ class MusicDirectory : public MusicListing { MusicDirectory(const fs::path root); virtual void render(HTTP::Connection::p req); - void update(); + void update(Database& db); }; namespace music { @@ -44,7 +47,7 @@ namespace music { MusicListing::p get(const HTTP::Connection::PathList& path); MusicListing::p get(const std::string& path); MusicDirectory::p get_directory(const std::string& path); - std::vector find(const std::map search); + std::vector find(std::map search); std::vector find(std::string search); void begin_update(const std::string path); }; -- cgit v1.2.3