summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--database.cpp110
-rw-r--r--database.h27
-rw-r--r--music.cpp107
-rw-r--r--music.h7
4 files changed, 155 insertions, 96 deletions
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 <boost/algorithm/string/join.hpp>
+#include <boost/format.hpp>
+
+Database::Database() {
+ sql.open(config::vm["audist.database"].as<std::string>());
+}
+
+Database::~Database() {
+ sql.close();
+}
+
+// NOTE: LIKE is case-insensitive by default on sqlite
+std::vector<MusicListing::p> Database::find(std::map<std::string, std::string> search) {
+ soci::session sql(config::vm["audist.database"].as<std::string>());
+ 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<std::string> 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<MusicListing::p> results;
+ while(st.fetch()) {
+ MusicListing::p ml(new MusicTrack(filename));
+ results.push_back(ml);
+ }
+ sql.close();
+
+ return results;
+}
+
+std::vector<MusicListing::p> Database::find(std::string search) {
+ soci::session sql(config::vm["audist.database"].as<std::string>());
+
+ search = "%"+search+"%";
+ soci::rowset<std::string> 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<MusicListing::p> results;
+ for(soci::rowset<std::string>::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 <soci/soci.h>
+#include <boost/filesystem.hpp>
+
+#include <vector>
+#include <map>
+#include <string>
+
+class Database {
+ private:
+ soci::session sql;
+
+ public:
+ Database();
+ virtual ~Database();
+
+ std::vector<MusicListing::p> find(std::map<std::string, std::string> search);
+ std::vector<MusicListing::p> 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 <boost/format.hpp>
#include <boost/algorithm/string/predicate.hpp>
@@ -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<MusicListing::p> music::find(std::map<std::string, std::string> search) {
- soci::session sql(config::vm["audist.database"].as<std::string>());
- 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<std::string> 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<MusicListing::p> 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<MusicListing::p> music::find(std::string search) {
- soci::session sql(config::vm["audist.database"].as<std::string>());
-
- search = "%"+search+"%";
- soci::rowset<std::string> 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<MusicListing::p> results;
- for(soci::rowset<std::string>::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<std::string>());
-
- 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<MusicListing::p> find(const std::map<std::string, std::string> search);
+ std::vector<MusicListing::p> find(std::map<std::string, std::string> search);
std::vector<MusicListing::p> find(std::string search);
void begin_update(const std::string path);
};