public void RemoveMediaItemAtIndex(int index)
{
if (PlaylistId == 0)
{
return;
}
ISQLiteConnection conn = null;
try
{
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
conn.BeginTransaction();
conn.ExecuteLogged("DELETE FROM PlaylistItem WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, index);
conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ItemPosition - 1 WHERE PlaylistId = ? AND ItemPosition > ?", PlaylistId, index);
conn.Commit();
}
catch (Exception e)
{
if (!ReferenceEquals(conn, null))
{
conn.Rollback();
}
logger.Error(e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
}
private Task <string> RunTaskInTransaction(Func <Task <string> > action)
{
return(Task.Run(async() =>
{
_sqliteConnection.BeginTransaction();
try
{
var result = await Task.Run(action);
if (!string.IsNullOrEmpty(result))
{
_sqliteConnection.Rollback();
}
else
{
_sqliteConnection.Commit();
}
return result;
}
catch (Exception ex)
{
_sqliteConnection.Rollback();
throw;
}
}));
}
/// <summary>
/// Get Cloud rows updated since lastSync and store them in LocalDB
/// </summary>
/// <typeparam name="T">Object type</typeparam>
/// <param name="lastSync">Date limit</param>
/// <param name="getID">Function to get Template ID</param>
async Task SyncLocalTable <T>(DateTime lastSync, Func <T, int> getID) where T : new()
{
// Trying to get all rows in the table T
//LastSynt Funcionality not Working YET------ Reason: Delete needs extra coding
var result = await MobileService.GetTable <T>().WithParameters(new Dictionary <string, string> {
{ "lastsync", lastSync.ToString("yyyy-MM-ddTHH:mm:sszzz", DateTimeFormatInfo.InvariantInfo) }
}).LoadAllAsync();
// var result = await MobileService.GetTable<T>().Take(_takeNRows).WithParameters(new Dictionary<string, string> { { "lastsync", lastSync.ToString("yyyy-MM-ddTHH:mm:sszzz", DateTimeFormatInfo.InvariantInfo) } }).ToListAsync();
string classname = typeof(T).Name;
_liteConnection.DeleteAll <T>();
/* //If id exists in local db delete
* foreach (var item in result)
* {
* _liteConnection.Delete(item);
* }*/
//Insert all
var storage = Mvx.Resolve <IMvxFileStore>();
// _liteConnection.Execute(string.Format("PRAGMA temp_store_directory = '{0}';", storage.NativePath(string.Empty)));
_liteConnection.BeginTransaction();
foreach (var tuple in result)
{
_liteConnection.Insert(tuple);
}
_liteConnection.Commit();
//Update sync table
var table_s = _liteConnection.Table <table_sync>().Where(t => t.table_name == classname).FirstOrDefault();
if (table_s != null)
{
table_s.synced_at = DateTime.UtcNow;
_liteConnection.Update(table_s);
}
else
{
Mvx.Trace("Table name not found in table_sync. Check SyncLocalTable Method line on WAMSRepositoryService");
throw new NullReferenceException();
}
}
public void MoveMediaItem(int fromIndex, int toIndex)
{
// make sure the input is within bounds and is not null
if (fromIndex >= PlaylistCount || fromIndex < 0 ||
toIndex < 0 || toIndex == fromIndex)
{
return;
}
logger.IfInfo("Moving media item");
ISQLiteConnection conn = null;
try {
// to do - better way of knowing whether or not a query has been successfully completed.
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
conn.BeginTransaction();
// Get the item out of the way to prevent constraint violations
conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = " + Int32.MaxValue + " WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, fromIndex);
if (fromIndex > toIndex)
{
// Do this as a reversed loop because a single update statement can have a constraint violation
for (int position = fromIndex - 1; position >= toIndex; position--)
{
logger.IfInfo("Updating position " + position + " to " + (position + 1));
conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ItemPosition + 1 WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, position);
}
// conditional rollback here
}
else
{
// Do this as a reversed loop because a single update statement can have a constraint violation
for (int position = fromIndex + 1; position <= toIndex; position++)
{
logger.IfInfo("Updating position " + position + " to " + (position + 1));
conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ItemPosition - 1 WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, position);
}
// conditional rollback here
}
conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ? WHERE PlaylistId = ? AND ItemPosition = ?", toIndex, PlaylistId, Int32.MaxValue);
// conditional rollback here
conn.Commit();
} catch (Exception e) {
if (!ReferenceEquals(conn, null))
{
conn.Rollback();
}
logger.Error(e);
} finally {
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
}
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);
}
}
public void InsertMediaItem(IMediaItem item, int index)
{
// make sure the input is within bounds and is not null
if (ReferenceEquals(item, null) || index > PlaylistCount || index < 0 || ReferenceEquals(PlaylistId, null))
{
return;
}
ISQLiteConnection conn = null;
try
{
int?id = Injection.Kernel.Get <IItemRepository>().GenerateItemId(ItemType.PlaylistItem);
if (!ReferenceEquals(id, null))
{
// to do - better way of knowing whether or not a query has been successfully completed.
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
conn.BeginTransaction();
for (int position = (int)PlaylistCount - 1; position >= index; position--)
{
logger.IfInfo("Updating position " + position + " to " + (position + 1));
conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ItemPosition + 1 WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, position);
}
// conditional rollback here
// Insert the new item
var playlistItem = new PlaylistItem();
playlistItem.PlaylistItemId = id;
playlistItem.PlaylistId = PlaylistId;
playlistItem.ItemType = item.ItemType;
playlistItem.ItemId = item.ItemId;
playlistItem.ItemPosition = index;
int affected = conn.Insert(playlistItem);
// conditional rollback here
if (affected > 0)
{
PlaylistCount++;
PlaylistDuration += (int)item.Duration;
LastUpdateTime = DateTime.UtcNow.ToUnixTime();
Md5Hash = CalculateHash();
conn.ExecuteLogged("UPDATE Playlist SET PlaylistName = ?, PlaylistCount = ?, PlaylistDuration = ?, Md5Hash = ?, LastUpdateTime = ? " +
"WHERE PlaylistId = ?", PlaylistName == null ? "" : PlaylistName, PlaylistCount, PlaylistDuration, Md5Hash, LastUpdateTime, PlaylistId);
conn.Commit();
}
else
{
conn.Rollback();
}
}
}
catch (Exception e)
{
if (!ReferenceEquals(conn, null))
{
conn.Rollback();
}
logger.Error(e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
}
