public async Task DeleteAll()
{
using (WriteLock.Write())
{
using (var connection = CreateConnection())
{
connection.RunInTransaction(db =>
{
var commandText = "delete from FileOrganizerResults";
db.Execute(commandText);
}, TransactionMode);
}
}
}
public void DeleteAllSmartMatch()
{
using (WriteLock.Write())
{
using (var connection = CreateConnection())
{
connection.RunInTransaction(db =>
{
var commandText = "delete from SmartMatch";
db.Execute(commandText);
}, TransactionMode);
}
}
}
public int RemoveUnknownUsers(List <string> known_user_ids)
{
string sql_query = "delete from PlaybackActivity " +
"where UserId not in ('" + string.Join("', '", known_user_ids) + "') ";
using (WriteLock.Write())
{
using var connection = CreateConnection();
connection.RunInTransaction(db =>
{
db.Execute(sql_query);
}, TransactionMode);
}
return(1);
}
/// <summary>
/// Persists the user data.
/// </summary>
/// <param name="userId">The user id.</param>
/// <param name="key">The key.</param>
/// <param name="userData">The user data.</param>
/// <param name="cancellationToken">The cancellation token.</param>
/// <returns>Task.</returns>
public async Task PersistUserData(Guid userId, string key, UserItemData userData, CancellationToken cancellationToken)
{
cancellationToken.ThrowIfCancellationRequested();
using (WriteLock.Write())
{
using (var connection = CreateConnection())
{
connection.RunInTransaction(db =>
{
SaveUserData(db, userId, key, userData);
}, TransactionMode);
}
}
}
public void AddComponentAsType(EntityComponentBase component, Type type)
{
using (UpgradeableReadLock.CreateLock(m_componentsSortedLock))
{
if (m_componentsSorted.ContainsKey(type.FullName.GetHashCode()))
{
return;
}
using (WriteLock.CreateLock(m_componentsSortedLock))
{
m_componentsSorted[type.FullName.GetHashCode()] = component;
}
}
}
/// <summary>
/// Opens the connection to the database
/// </summary>
/// <returns>Task.</returns>
public void Initialize(ReaderWriterLockSlim writeLock, ManagedConnection managedConnection)
{
_connection = managedConnection;
WriteLock.Dispose();
WriteLock = writeLock;
using (var connection = CreateConnection())
{
string[] queries =
{
"create table if not exists userdata (key nvarchar, userId GUID, rating float null, played bit, playCount int, isFavorite bit, playbackPositionTicks bigint, lastPlayedDate datetime null)",
"create table if not exists DataSettings (IsUserDataImported bit)",
"drop index if exists idx_userdata",
"drop index if exists idx_userdata1",
"drop index if exists idx_userdata2",
"drop index if exists userdataindex1",
"create unique index if not exists userdataindex on userdata (key, userId)",
"create index if not exists userdataindex2 on userdata (key, userId, played)",
"create index if not exists userdataindex3 on userdata (key, userId, playbackPositionTicks)",
"create index if not exists userdataindex4 on userdata (key, userId, isFavorite)",
"pragma shrink_memory"
};
connection.RunQueries(queries);
connection.RunInTransaction(db =>
{
var existingColumnNames = GetColumnNames(db, "userdata");
AddColumn(db, "userdata", "AudioStreamIndex", "int", existingColumnNames);
AddColumn(db, "userdata", "SubtitleStreamIndex", "int", existingColumnNames);
}, TransactionMode);
try
{
ImportUserDataIfNeeded(connection);
}
catch (Exception ex)
{
Logger.ErrorException("Error in ImportUserDataIfNeeded", ex);
}
}
}
public ZooKeeperLockProvider(string connectionString, ILoggerFactory loggerFactory)
{
throw new NotImplementedException();
_logger = loggerFactory.CreateLogger <ZooKeeperLockProvider>();
_connectionString = connectionString;
WriteLock wl = new WriteLock(GetSession(), @"/workflow_locks", null);
//try
//{
// //todo: clean this up
// //wl.Lock().Wait();
//}
//catch
//{
//}
}
/// <summary>
/// protected method allow the inheritor to add many types at once
/// </summary>
/// <param name="types"></param>
protected void AddTypes(IEnumerable <Type> types)
{
using (var l = new WriteLock(_lock))
{
foreach (var t in types)
{
EnsureCorrectType(t);
if (InstanceTypes.Contains(t))
{
throw new InvalidOperationException("The Type " + t + " already exists in the collection");
}
;
InstanceTypes.Add(t);
}
}
}
public bool Remove(Actor actor)
{
bool removeResult;
using (WriteLock.Enter(_actorsLock))
{
removeResult = _actors.Remove(actor.Id);
}
if (removeResult)
{
Unsubscribe(actor);
}
return(removeResult);
}
private void OnActorAdded(Actor actor)
{
if (!actor.TryGetComponents <TComponent1, TComponent2>(out var component1, out var component2))
{
return;
}
var wrapper = new Actor <TComponent1, TComponent2>(actor, component1, component2);
using (WriteLock.Enter(_lock))
{
_actors.Add(wrapper);
}
OnWrapperAdded(wrapper);
}
private void TryAdd(TKey local1, CacheKey <TKey, TValue> key1)
{
IDisposable disposable = WriteLock.TryEnter(this.readerWriterLockSlim, base.LockTimeout);
try
{
this.cacheKeys.Add(local1, key1);
}
finally
{
if (disposable != null)
{
disposable.Dispose();
}
}
}
private void OnActorComponentAdded(Actor actor, IComponent component)
{
if (!(component is TComponent found))
{
return;
}
var wrapper = new Actor <TComponent>(actor, found);
using (WriteLock.Enter(_lock))
{
_actors.Add(wrapper);
}
OnWrapperAdded(wrapper);
}
private void TryCacheItemRemove(TKey local1)
{
IDisposable disposable = WriteLock.TryEnter(this.readerWriterLockSlim, base.LockTimeout);
try
{
this.cacheKeys.Remove(local1);
}
finally
{
if (disposable != null)
{
disposable.Dispose();
}
}
}
private void InitializeInternal()
{
using (WriteLock.Write())
{
using var connection = CreateConnection();
_logger.LogInformation("Initialize PlaybackActivity Repository");
string sql_info = "pragma table_info('PlaybackActivity')";
List <string> cols = new List <string>();
foreach (var row in connection.Query(sql_info))
{
string table_schema = row[1].ToString().ToLower() + ":" + row[2].ToString().ToLower();
cols.Add(table_schema);
}
string actual_schema = string.Join("|", cols);
string required_schema = "datecreated:datetime|userid:text|itemid:text|itemtype:text|itemname:text|playbackmethod:text|clientname:text|devicename:text|playduration:int";
if (required_schema != actual_schema)
{
_logger.LogInformation("PlaybackActivity table schema miss match!");
_logger.LogInformation("Expected : {RequiredSchema}", required_schema);
_logger.LogInformation("Received : {ActualSchema}", actual_schema);
_logger.LogInformation("Dropping and recreating PlaybackActivity table");
connection.Execute("drop table if exists PlaybackActivity");
}
else
{
_logger.LogInformation("PlaybackActivity table schema OK");
_logger.LogInformation("Expected : {RequiredSchema}", required_schema);
_logger.LogInformation("Received : {ActualSchema}", actual_schema);
}
// ROWID
connection.Execute("create table if not exists PlaybackActivity (" +
"DateCreated DATETIME NOT NULL, " +
"UserId TEXT, " +
"ItemId TEXT, " +
"ItemType TEXT, " +
"ItemName TEXT, " +
"PlaybackMethod TEXT, " +
"ClientName TEXT, " +
"DeviceName TEXT, " +
"PlayDuration INT" +
")");
connection.Execute("create table if not exists UserList (UserId TEXT)");
}
}
public void AddComponents(params IComponent[] components)
{
using (WriteLock.Enter(_lock))
{
CollectionUtils.EnsureCapacity(ref _components, _componentsLength + components.Length);
foreach (var component in components)
{
CollectionUtils.Insert(ref _components, _componentsLength++, component);
}
}
foreach (var component in components)
{
OnComponentAdded(component);
}
}
public void RemoveCheckpoint(Guid checkpointId)
{
using (WriteLock.Write())
{
using (var connection = CreateConnection())
{
connection.RunInTransaction(db =>
{
using (var statement = db.PrepareStatement($"delete from {CheckpointsTable} where Guid=@Guid;"))
{
statement.TryBind("@Guid", checkpointId);
statement.MoveNext();
}
}, TransactionMode);
}
}
}
public async Task DeleteAll()
{
await WriteLock.WaitAsync().ConfigureAwait(false);
IDbTransaction transaction = null;
try
{
transaction = _connection.BeginTransaction();
_deleteAllCommand.Transaction = transaction;
_deleteAllCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (OperationCanceledException)
{
if (transaction != null)
{
transaction.Rollback();
}
throw;
}
catch (Exception e)
{
Logger.ErrorException("Failed to delete results", e);
if (transaction != null)
{
transaction.Rollback();
}
throw;
}
finally
{
if (transaction != null)
{
transaction.Dispose();
}
WriteLock.Release();
}
}
/// <summary>
/// Retrieve all users from the database
/// </summary>
/// <returns>IEnumerable{User}.</returns>
public List <User> RetrieveAllUsers()
{
var list = new List <User>();
using (WriteLock.Read())
{
using (var connection = CreateConnection(true))
{
foreach (var row in connection.Query("select id,guid,data from LocalUsersv2"))
{
list.Add(GetUser(row));
}
}
}
return(list);
}
/// <summary>
/// Retrieve all groups from the database
/// </summary>
/// <returns>IEnumerable{Group}.</returns>
public List <Group> RetrieveAllGroups()
{
var list = new List <Group>();
using (WriteLock.Read())
{
using (var connection = CreateConnection(true))
{
foreach (var row in connection.Query("SELECT Id,Name FROM Groups"))
{
list.Add(GetGroup(row));
}
}
}
return(list);
}
public List <string> GetTypeFilterList()
{
List <string> filter_Type_list = new List <string>();
using (WriteLock.Read())
{
using var connection = CreateConnection(true);
string sql_query = "select distinct ItemType from PlaybackActivity";
using var statement = connection.PrepareStatement(sql_query);
foreach (var row in statement.ExecuteQuery())
{
string type = row[0].ToString();
filter_Type_list.Add(type);
}
}
return(filter_Type_list);
}
/// <summary>
/// Retrieve all plains from the database
/// </summary>
/// <returns>IEnumerable{Group}.</returns>
public List <Plain> RetrieveAllPlains()
{
var list = new List <Plain>();
using (WriteLock.Read())
{
using (var connection = CreateConnection(true))
{
foreach (var row in connection.Query("SELECT Id,Name,MaxSimultaneousScreens FROM Plains"))
{
list.Add(GetPlain(row));
}
}
}
return(list);
}
public SortedDictionary <int, int> GetDurationHistogram(int days, DateTime endDate, string[] types)
{
/*
* SELECT CAST(PlayDuration / 300 as int) AS FiveMinBlock, COUNT(1) ActionCount
* FROM PlaybackActivity
* GROUP BY CAST(PlayDuration / 300 as int)
* ORDER BY CAST(PlayDuration / 300 as int) ASC;
*/
List <string> filters = new List <string>();
foreach (string filter in types)
{
filters.Add("'" + filter + "'");
}
SortedDictionary <int, int> report = new SortedDictionary <int, int>();
DateTime start_date = endDate.Subtract(new TimeSpan(days, 0, 0, 0));
string sql =
"SELECT CAST(PlayDuration / 300 as int) AS FiveMinBlock, COUNT(1) ActionCount " +
"FROM PlaybackActivity " +
"WHERE DateCreated >= @start_date AND DateCreated <= @end_date " +
"AND UserId not IN (select UserId from UserList) " +
"AND ItemType IN (" + string.Join(",", filters) + ") " +
"GROUP BY CAST(PlayDuration / 300 as int) " +
"ORDER BY CAST(PlayDuration / 300 as int) ASC";
using (WriteLock.Read())
{
using var connection = CreateConnection(true);
using var statement = connection.PrepareStatement(sql);
statement.TryBind("@start_date", start_date.ToString("yyyy-MM-dd 00:00:00"));
statement.TryBind("@end_date", endDate.ToString("yyyy-MM-dd 23:59:59"));
foreach (var row in statement.ExecuteQuery())
{
int block_num = row[0].ToInt();
int count = row[1].ToInt();
report.Add(block_num, count);
}
}
return(report);
}
public List <Dictionary <string, string> > GetUsageForUser(string date, string userId, string[] types)
{
List <string> filters = new List <string>();
foreach (string filter in types)
{
filters.Add("'" + filter + "'");
}
string sql_query = "SELECT DateCreated, ItemId, ItemType, ItemName, ClientName, PlaybackMethod, DeviceName, PlayDuration, rowid " +
"FROM PlaybackActivity " +
"WHERE DateCreated >= @date_from AND DateCreated <= @date_to " +
"AND UserId = @user_id " +
"AND ItemType IN (" + string.Join(",", filters) + ") " +
"ORDER BY DateCreated";
List <Dictionary <string, string> > items = new List <Dictionary <string, string> >();
using (WriteLock.Read())
{
using var connection = CreateConnection(true);
using var statement = connection.PrepareStatement(sql_query);
statement.TryBind("@date_from", date + " 00:00:00");
statement.TryBind("@date_to", date + " 23:59:59");
statement.TryBind("@user_id", userId);
foreach (var row in statement.ExecuteQuery())
{
Dictionary <string, string> item = new Dictionary <string, string>
{
["Time"] = row[0].ReadDateTime().ToLocalTime().ToString("HH:mm"),
["Id"] = row[1].ToString(),
["Type"] = row[2].ToString(),
["ItemName"] = row[3].ToString(),
["ClientName"] = row[4].ToString(),
["PlaybackMethod"] = row[5].ToString(),
["DeviceName"] = row[6].ToString(),
["PlayDuration"] = row[7].ToString(),
["RowId"] = row[8].ToString()
};
items.Add(item);
}
}
return(items);
}
public async Task DeleteCompleted()
{
using (WriteLock.Write())
{
using (var connection = CreateConnection())
{
connection.RunInTransaction(db =>
{
using (var statement = db.PrepareStatement("delete from FileOrganizerResults where Status = @Status"))
{
statement.TryBind("@Status", FileSortingStatus.Success.ToString());
statement.MoveNext();
}
}, TransactionMode);
}
}
}
/// <summary>
/// Tear down the object and dispose of resources.
/// </summary>
public void Dispose()
{
if (_SslStream != null)
{
_SslStream.Close();
_SslStream.Dispose();
_SslStream = null;
}
if (_NetworkStream != null)
{
_NetworkStream.Close();
_NetworkStream.Dispose();
_NetworkStream = null;
}
if (TokenSource != null)
{
if (!TokenSource.IsCancellationRequested)
{
TokenSource.Cancel();
}
TokenSource.Dispose();
TokenSource = null;
}
if (WriteLock != null)
{
WriteLock.Dispose();
WriteLock = null;
}
if (ReadLock != null)
{
ReadLock.Dispose();
ReadLock = null;
}
if (_TcpClient != null)
{
_TcpClient.Close();
_TcpClient.Dispose();
_TcpClient = null;
}
}
public List <Dictionary <string, object> > GetMoviesReport(int days, DateTime end_date)
{
List <Dictionary <string, object> > report = new List <Dictionary <string, object> >();
DateTime start_date = end_date.Subtract(new TimeSpan(days, 0, 0, 0));
Dictionary <String, Dictionary <string, int> > usage = new Dictionary <String, Dictionary <string, int> >();
string sql = "";
sql += "SELECT ItemName AS name, ";
sql += "COUNT(1) AS play_count, ";
sql += "SUM(PlayDuration) AS total_duarion ";
sql += "FROM PlaybackActivity ";
sql += "WHERE ItemType = 'Movie' ";
sql += "AND DateCreated >= @start_date AND DateCreated <= @end_date ";
sql += "AND UserId not IN (select UserId from UserList) ";
sql += "GROUP BY name";
using (WriteLock.Read())
{
using (var connection = CreateConnection(true))
{
using (var statement = connection.PrepareStatement(sql))
{
statement.TryBind("@start_date", start_date.ToString("yyyy-MM-dd 00:00:00"));
statement.TryBind("@end_date", end_date.ToString("yyyy-MM-dd 23:59:59"));
foreach (var row in statement.ExecuteQuery())
{
string item_label = row[0].ToString();
int action_count = row[1].ToInt();
int seconds_sum = row[2].ToInt();
Dictionary <string, object> row_data = new Dictionary <string, object>();
row_data.Add("label", item_label);
row_data.Add("count", action_count);
row_data.Add("time", seconds_sum);
report.Add(row_data);
}
}
}
}
return(report);
}
public void Update(AuthenticationInfo info, CancellationToken cancellationToken)
{
if (info == null)
{
throw new ArgumentNullException("info");
}
cancellationToken.ThrowIfCancellationRequested();
using (WriteLock.Write())
{
using (var connection = CreateConnection())
{
connection.RunInTransaction(db =>
{
using (var statement = db.PrepareStatement("replace into AccessTokens (Id, AccessToken, DeviceId, AppName, AppVersion, DeviceName, UserId, UserName, IsActive, DateCreated, DateLastActivity, DateRevoked) values (@Id, @AccessToken, @DeviceId, @AppName, @AppVersion, @DeviceName, @UserId, @UserName, @IsActive, @DateCreated, @DateLastActivity, @DateRevoked)"))
{
statement.TryBind("@Id", info.Id.ToGuidBlob());
statement.TryBind("@AccessToken", info.AccessToken);
statement.TryBind("@DeviceId", info.DeviceId);
statement.TryBind("@AppName", info.AppName);
statement.TryBind("@AppVersion", info.AppVersion);
statement.TryBind("@DeviceName", info.DeviceName);
statement.TryBind("@UserId", (info.UserId.Equals(Guid.Empty) ? null : info.UserId.ToString("N")));
statement.TryBind("@UserName", info.UserName);
statement.TryBind("@IsActive", info.IsActive);
statement.TryBind("@DateCreated", info.DateCreated.ToDateTimeParamValue());
statement.TryBind("@DateLastActivity", info.DateLastActivity.ToDateTimeParamValue());
if (info.DateRevoked.HasValue)
{
statement.TryBind("@DateRevoked", info.DateRevoked.Value.ToDateTimeParamValue());
}
else
{
statement.TryBindNull("@DateRevoked");
}
statement.MoveNext();
}
}, TransactionMode);
}
}
}
public List <string> GetUserList()
{
List <string> user_id_list = new List <string>();
using (WriteLock.Read())
{
using var connection = CreateConnection(true);
string sql_query = "select UserId from UserList";
using var statement = connection.PrepareStatement(sql_query);
foreach (var row in statement.ExecuteQuery())
{
string type = row[0].ToString();
user_id_list.Add(type);
}
}
return(user_id_list);
}
private void DisposeConnection()
{
try
{
lock (_disposeLock)
{
using (WriteLock.Write())
{
_connection?.Dispose();
_dbConnection?.Dispose();
}
}
}
catch (Exception ex)
{
_logger.LogError(ex, "Error disposing database", ex);
}
}
public void UpdatePlaybackAction(PlaybackInfo playInfo)
{
string sql_add = "update PlaybackActivity set PlayDuration = @PlayDuration where DateCreated = @DateCreated and UserId = @UserId and ItemId = @ItemId";
using (WriteLock.Write())
{
using var connection = CreateConnection();
connection.RunInTransaction(db =>
{
using var statement = db.PrepareStatement(sql_add);
statement.TryBind("@DateCreated", playInfo.Date.ToDateTimeParamValue());
statement.TryBind("@UserId", playInfo.UserId);
statement.TryBind("@ItemId", playInfo.ItemId);
statement.TryBind("@PlayDuration", playInfo.PlaybackDuration);
statement.MoveNext();
}, TransactionMode);
}
}
public LockWatcher(WriteLock outerInstance)
{
this.outerInstance = outerInstance;
}
public DisposableLock(WriteLock writeLock)
{
this.writeLock = writeLock;
}
public LockWatcher(WriteLock writeLock)
{
this.writeLock = writeLock;
}
public ZooKeeperOperationAnonymousInnerClass(WriteLock outerInstance)
{
this.outerInstance = outerInstance;
}
public LockZooKeeperOperation(WriteLock outerInstance)
{
this.outerInstance = outerInstance;
}
