// TODO: Rewrite this to be more efficient
public IList <Song> SinglesForAlbumArtistId(int albumArtistId)
{
ISQLiteConnection conn = null;
try {
conn = database.GetSqliteConnection();
IList <Song> songs;
songs = conn.Query <Song>("SELECT ItemId FROM Song WHERE AlbumArtistId = ? AND AlbumId IS NULL", albumArtistId);
if (songs.Count > 0)
{
IList <int> songIds = new List <int>();
foreach (Song song in songs)
{
songIds.Add((int)song.ItemId);
}
return(songRepository.SongsForIds(songIds));
}
else
{
return(new List <Song>());
}
} catch (Exception e) {
logger.Error(e);
} finally {
database.CloseSqliteConnection(conn);
}
// We had an exception somehow, so return an empty list
return(new List <Song>());
}
public PLevel GetLevel(string chapterName, string levelName)
{
PChapter _pchapter = GetChapter(chapterName);
List <PLevel> levels = _connection.Query <PLevel>(string.Format("select * from PLevel where ChapterId = {0} AND Name = {1}", _pchapter.Id, levelName));
return(levels [0]);
}
// Retrieve a list of objects of type T using query string and prepared arguments
public static IList <T> GetList <T>(this IDatabase database, string query, params object[] args) where T : new()
{
ISQLiteConnection conn = null;
try
{
// Get database connection, use query to generate object of type T
conn = database.GetSqliteConnection();
// If result found, return it
return(conn.Query <T>(query, args));
}
catch (Exception e)
{
logger.Error("query: " + query);
logger.Error(e);
}
finally
{
database.CloseSqliteConnection(conn);
}
// If no result, return blank instance
return(new List <T>());
}
public IList <Session> ListOfSessions()
{
ISQLiteConnection conn = null;
try {
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
return(conn.Query <Session>("SELECT RowId AS RowId, * FROM Session WHERE UserId = ?", this.UserId));
} catch (Exception e) {
logger.Error(e);
} finally {
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
return(new List <Session>());
}
public IList <QueryLog> QueryLogsSinceId(int queryId)
{
// Return all queries >= this id
ISQLiteConnection conn = null;
try {
// Gather a list of queries from the query log, which can be used to synchronize a local database
conn = GetQueryLogSqliteConnection();
return(conn.Query <QueryLog>("SELECT * FROM QueryLog WHERE QueryId >= ?", queryId));
} catch (Exception e) {
logger.Error(e);
} finally {
// Ensure database closed
CloseQueryLogSqliteConnection(conn);
}
return(new List <QueryLog>());
}
public void RemoveMediaItemAtIndexes(IList <int> indices)
{
ISQLiteConnection conn = null;
try
{
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
conn.BeginTransaction();
// delete the items at the indicated indices
foreach (int index in indices)
{
logger.IfInfo("Deleting row at ItemPosition: " + index);
conn.ExecuteLogged("DELETE FROM PlaylistItem WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, index);
}
// select the id of all members of the playlist
var result = conn.Query <PlaylistItem>("SELECT * FROM PlaylistItem WHERE PlaylistId = ? ORDER BY ItemPosition", PlaylistId);
// update the values of each index in the array to be the new index
for (int i = 0; i < result.Count; i++)
{
var item = result[i];
conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ? WHERE PlaylistItemId = ? AND PlaylistId = ?", i, item.PlaylistItemId, PlaylistId);
}
conn.Commit();
}
catch (Exception e)
{
if (!ReferenceEquals(conn, null))
{
conn.Rollback();
}
logger.Error(e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
}
private Folder CreateFolder(string path, bool mediafolder)
{
if (path == null || path == "")
{
// No path so just return a folder
return(new Folder());
}
ISQLiteConnection conn = null;
try
{
// Trim all trailing slashes from paths, to prevent potential constraint issues
path = path.TrimEnd('/', '\\');
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
IList <Folder> result = conn.Query <Folder>("SELECT * FROM Folder WHERE FolderPath = ? AND MediaFolderId IS NULL", path);
foreach (Folder f in result)
{
if (path.Equals(f.FolderPath))
{
return(f);
}
}
}
catch (Exception e)
{
logger.Error(e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
// If not in database, return a folder object with the specified parameters
Folder folder = new Folder();
folder.FolderPath = path;
folder.FolderName = Path.GetFileName(path);
return(folder);
}
public bool SongNeedsUpdating(string filePath, int?folderId, out bool isNew, out int?itemId)
{
// We don't need to instantiate another folder to know what the folder id is. This should be known when the method is called.
string fileName = Path.GetFileName(filePath);
long lastModified = System.IO.File.GetLastWriteTime(filePath).ToUnixTime();
bool needsUpdating = true;
isNew = true;
itemId = null;
ISQLiteConnection conn = null;
try
{
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
IEnumerable result = conn.Query <Song>("SELECT * FROM Song WHERE FolderId = ? AND FileName = ? LIMIT 1", folderId, fileName);
foreach (Song song in result)
{
isNew = false;
itemId = song.ItemId;
if (song.LastModified == lastModified)
{
needsUpdating = false;
}
return(needsUpdating);
}
}
catch (Exception e)
{
logger.Error(e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
return(needsUpdating);
}
public List <T> QueryItems(string query, params object[] args)
{
string actualQuery = String.Format(query, args);
return(_connection.Query <T>(actualQuery));
}