public static IDbCommand DeleteUserAdditionalDataCommand(ITransaction transaction, Guid profileId, int?dataNo, string dataKey)
{
IDbCommand result = transaction.CreateCommand();
result.CommandText = "DELETE FROM USER_ADDITIONAL_DATA WHERE PROFILE_ID=@PROFILE_ID";
if (dataNo.HasValue)
{
result.CommandText += " AND DATA_NO=@DATA_NO";
}
if (!string.IsNullOrEmpty(dataKey))
{
result.CommandText += " AND DATA_KEY=@DATA_KEY";
}
ISQLDatabase database = transaction.Database;
database.AddParameter(result, "PROFILE_ID", profileId, typeof(Guid));
if (dataNo.HasValue)
{
database.AddParameter(result, "DATA_NO", dataNo.Value, typeof(int));
}
if (!string.IsNullOrEmpty(dataKey))
{
database.AddParameter(result, "DATA_KEY", dataKey, typeof(string));
}
return(result);
}
public void DeleteSubSchema(string subSchemaName, int currentVersionMajor, int currentVersionMinor, string deleteScriptFilePath)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>(false);
using (ITransaction transaction = database.BeginTransaction(IsolationLevel.Serializable))
{
int versionMajor;
int versionMinor;
bool schemaPresent = GetSubSchemaVersion(subSchemaName, out versionMajor, out versionMinor);
if (!schemaPresent)
{
return;
}
if (currentVersionMajor == versionMajor && currentVersionMinor == versionMinor)
{
using (TextReader reader = new SqlScriptPreprocessor(deleteScriptFilePath))
ExecuteBatch(transaction, new InstructionList(reader));
}
else
{
throw new ArgumentException(string.Format("The current version of sub schema '{0}' is {1}.{2}, but the schema deletion script works for version {3}.{4}",
subSchemaName, versionMajor, versionMinor, currentVersionMajor, currentVersionMajor));
}
using (IDbCommand command = MediaPortal_Basis_Schema.DeleteSubSchemaCommand(transaction, subSchemaName))
command.ExecuteNonQuery();
transaction.Commit();
}
}
protected ICollection <UserProfile> GetProfiles(Guid?profileId, string name)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
int profileIdIndex;
int nameIndex;
using (IDbCommand command = UserProfileDataManagement_SubSchema.SelectUserProfilesCommand(transaction, profileId, name,
out profileIdIndex, out nameIndex))
{
ICollection <UserProfile> result = new List <UserProfile>();
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Guid profileId_ = database.ReadDBValue <Guid>(reader, profileIdIndex);
string name_ = database.ReadDBValue <string>(reader, nameIndex);
result.Add(new UserProfile(profileId_, name_));
}
}
return(result);
}
}
finally
{
transaction.Dispose();
}
}
public async Task <Guid> CreateProfileAsync(string profileName)
{
//Profile might already exist.
var result = await GetProfileByNameAsync(profileName);
if (result.Success)
{
return(result.Result.ProfileId);
}
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
Guid profileId = Guid.NewGuid();
try
{
using (IDbCommand command = UserProfileDataManagement_SubSchema.CreateUserProfileCommand(transaction, profileId, profileName))
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception e)
{
ServiceRegistration.Get <ILogger>().Error("UserProfileDataManagement: Error creating user profile '{0}')", e, profileName);
transaction.Rollback();
throw;
}
return(profileId);
}
protected static string PreprocessScript(TextReader reader, IDictionary <string, string> customPlaceholders = null)
{
string orig = reader.ReadToEnd();
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
return(Preprocess(orig, database, customPlaceholders));
}
public Guid CreateProfile(string profileName)
{
//Profile might already exist.
UserProfile existingProfile;
if (GetProfileByName(profileName, out existingProfile))
{
return(existingProfile.ProfileId);
}
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
Guid profileId = Guid.NewGuid();
try
{
using (IDbCommand command = UserProfileDataManagement_SubSchema.CreateUserProfileCommand(transaction, profileId, profileName))
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception e)
{
ServiceRegistration.Get <ILogger>().Error("UserProfileDataManagement: Error creating user profile '{0}')", e, profileName);
transaction.Rollback();
throw;
}
return(profileId);
}
protected static string GetType(Type type, ISQLDatabase database)
{
string result = database.GetSQLType(type);
if (result == null)
throw new InvalidDataException(".net type '{0}' isn't supported by the installed database '{1}', version {2}", type.Name, database.DatabaseType, database.DatabaseVersion);
return result;
}
public Task <bool> SetUserMediaItemDataAsync(Guid profileId, Guid mediaItemId, string key, string data)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
bool result;
using (IDbCommand command = UserProfileDataManagement_SubSchema.DeleteUserMediaItemDataCommand(transaction, profileId, mediaItemId, key))
command.ExecuteNonQuery();
// Allow "delete only", if new data is null. This is used to delete no longer required data.
if (!string.IsNullOrEmpty(data))
{
using (IDbCommand command = UserProfileDataManagement_SubSchema.CreateUserMediaItemDataCommand(transaction, profileId, mediaItemId, key, data))
result = command.ExecuteNonQuery() > 0;
}
else
{
result = true;
}
transaction.Commit();
return(Task.FromResult(result));
}
catch (Exception e)
{
ServiceRegistration.Get <ILogger>().Error("UserProfileDataManagement: Error setting media item data '{0}' for media item '{1}' in profile '{2}'", e, key, mediaItemId, profileId);
transaction.Rollback();
throw;
}
}
protected static string PreprocessScript(TextReader reader)
{
string orig = reader.ReadToEnd();
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
return(Preprocess(orig, database));
}
public Task <AsyncResult <string> > GetUserAdditionalDataAsync(Guid profileId, string key, int dataNo = 0)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
int dataIndex;
using (IDbCommand command = UserProfileDataManagement_SubSchema.SelectUserAdditionalDataCommand(transaction, profileId,
key, dataNo, out dataIndex))
{
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
var data = database.ReadDBValue <string>(reader, dataIndex);
return(Task.FromResult(new AsyncResult <string>(true, data)));
}
}
}
return(Task.FromResult(new AsyncResult <string>(false, null)));
}
finally
{
transaction.Dispose();
}
}
private static ICollection <Share> GetMediaSources(ISQLDatabase database)
{
IList <Share> sources = new List <Share>();
ITransaction transaction = database.BeginTransaction();
IDbCommand sourcesCommand = transaction.CreateCommand();
sourcesCommand.CommandText = "select share_id,base_resource_path,name from shares s";
IDataReader sourcesReader = sourcesCommand.ExecuteReader();
while (sourcesReader.Read())
{
Guid sourceId = sourcesReader.GetGuid(sourcesReader.GetOrdinal("share_id"));
ResourcePath resourcePath = ResourcePath.Deserialize(sourcesReader.GetString(sourcesReader.GetOrdinal("base_resource_path")));
IList <string> categories = new List <string>();
IDbCommand categoriesCommand = transaction.CreateCommand();
categoriesCommand.CommandText = "select categoryname from shares_categories where share_id=@share_id";
database.AddParameter(categoriesCommand, "share_id", sourceId, typeof(Guid));
IDataReader categoriesReader = categoriesCommand.ExecuteReader();
while (categoriesReader.Read())
{
categories.Add(categoriesReader.GetString(categoriesReader.GetOrdinal("categoryname")));
}
categoriesReader.Close();
string name = sourcesReader.GetString(sourcesReader.GetOrdinal("name"));
sources.Add(new Share(sourceId, null, resourcePath, name, true, categories));
}
sourcesReader.Close();
return(sources);
}
public void Startup()
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>(false);
if (database == null)
{
throw new IllegalCallException("There is no database present in the system");
}
// Prepare schema
if (!database.TableExists(MediaPortal_Basis_Schema.MEDIAPORTAL_BASIS_TABLE_NAME))
{
ServiceRegistration.Get <ILogger>().Info("DatabaseManager: Creating subschema '{0}'", MediaPortal_Basis_Schema.SUBSCHEMA_NAME);
using (TextReader reader = new SqlScriptPreprocessor(MediaPortal_Basis_Schema.SubSchemaCreateScriptPath))
ExecuteBatch(database, new InstructionList(reader));
}
// Hint: Table MEDIAPORTAL_BASIS contains a sub schema entry for "MEDIAPORTAL_BASIS" with version number 1.0
int versionMajor;
int versionMinor;
if (!GetSubSchemaVersion(MediaPortal_Basis_Schema.SUBSCHEMA_NAME, out versionMajor, out versionMinor))
{
throw new UnexpectedStateException("{0} schema is not present or corrupted", MediaPortal_Basis_Schema.SUBSCHEMA_NAME);
}
ServiceRegistration.Get <ILogger>().Info("DatabaseManager: Subschema '{0}' present in version {1}.{2}",
MediaPortal_Basis_Schema.SUBSCHEMA_NAME, versionMajor, versionMinor);
}
public static IDbCommand DeleteUserMediaItemDataCommand(ITransaction transaction, Guid profileId, Guid?mediaItemId, string dataKey)
{
IDbCommand result = transaction.CreateCommand();
result.CommandText = "DELETE FROM USER_MEDIA_ITEM_DATA WHERE PROFILE_ID=@PROFILE_ID";
if (mediaItemId.HasValue)
{
result.CommandText += " AND MEDIA_ITEM_ID=@MEDIA_ITEM_ID";
}
if (!string.IsNullOrEmpty(dataKey))
{
result.CommandText += " AND DATA_KEY=@DATA_KEY";
}
ISQLDatabase database = transaction.Database;
database.AddParameter(result, "PROFILE_ID", profileId, typeof(Guid));
if (mediaItemId.HasValue)
{
database.AddParameter(result, "MEDIA_ITEM_ID", mediaItemId.Value, typeof(Guid));
}
if (!string.IsNullOrEmpty(dataKey))
{
database.AddParameter(result, "DATA_KEY", dataKey, typeof(string));
}
return(result);
}
public bool GetSubSchemaVersion(string subSchemaName, out int versionMajor, out int versionMinor)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>(false);
versionMajor = 0;
versionMinor = 0;
int versionMajorParameterIndex;
int versionMinorParameterIndex;
using (ITransaction transaction = database.BeginTransaction())
{
using (IDbCommand command = MediaPortal_Basis_Schema.SelectVersionBySubschemaCommand(transaction, subSchemaName,
out versionMajorParameterIndex, out versionMinorParameterIndex))
using (IDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow))
{
if (reader.Read())
{
// Versions are marked NOT NULL, so it is safe not to check for DBNull
versionMajor = reader.GetInt32(versionMajorParameterIndex);
versionMinor = reader.GetInt32(versionMinorParameterIndex);
return(true);
}
return(false);
}
}
}
private void AddMultipleMIAs(ISQLDatabase database, ITransaction transaction, IEnumerable <MediaItemAspectMetadata> selectedMIAs, IList <Guid> ids, IDictionary <Guid, ICollection <MultipleMediaItemAspect> > multipleMiaValues)
{
ILogger logger = ServiceRegistration.Get <ILogger>();
foreach (MultipleMediaItemAspectMetadata miam in selectedMIAs.Where(x => x is MultipleMediaItemAspectMetadata))
{
//logger.Debug("Getting {0} rows for {1}", ids.Count, miam.Name);
IDictionary <MediaItemAspectMetadata.AttributeSpecification, QueryAttribute> attributes =
new Dictionary <MediaItemAspectMetadata.AttributeSpecification, QueryAttribute>();
foreach (MediaItemAspectMetadata.AttributeSpecification attr in miam.AttributeSpecifications.Values)
{
if (attr.Cardinality == Cardinality.Inline || attr.Cardinality == Cardinality.ManyToOne)
{
attributes[attr] = new QueryAttribute(attr);
}
}
AddMultipleMIAResults(database, transaction, miam, attributes, new MultipleMIAQueryBuilder(_miaManagement, attributes.Values, miam, ids.ToArray()), multipleMiaValues);
if (miam.AspectId == RelationshipAspect.ASPECT_ID)
{
// Special case for relationships where the IDs being processed could be at the linked end
AddMultipleMIAResults(database, transaction, miam, attributes, new InverseRelationshipQueryBuilder(_miaManagement, attributes.Values, ids.ToArray()), multipleMiaValues);
}
}
}
public bool GetUserAdditionalData(Guid profileId, string key, out string data)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
int dataIndex;
using (IDbCommand command = UserProfileDataManagement_SubSchema.SelectUserAdditionalDataCommand(transaction, profileId,
key, out dataIndex))
{
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
data = database.ReadDBValue <string>(reader, dataIndex);
return(true);
}
}
}
data = null;
return(false);
}
finally
{
transaction.Dispose();
}
}
public void AttachClient(string clientSystemId)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
using (IDbCommand command = ClientManager_SubSchema.InsertAttachedClientCommand(transaction, clientSystemId, null, null))
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception e)
{
ServiceRegistration.Get <ILogger>().Error("ClientManager: Error attaching client '{0}'", e, clientSystemId);
transaction.Rollback();
throw;
}
ServiceRegistration.Get <ILogger>().Info("ClientManager: Client with system ID '{0}' attached", clientSystemId);
// Establish the UPnP connection to the client, if available in the network
IDictionary <string, MPClientMetadata> attachedClients = ReadAttachedClientsFromDB();
lock (_syncObj)
_attachedClients = attachedClients;
_controlPoint.AddAttachedClient(clientSystemId);
ClientManagerMessaging.SendClientAttachmentChangeMessage(ClientManagerMessaging.MessageType.ClientAttached, clientSystemId);
}
public void DetachClientAndRemoveShares(string clientSystemId)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
using (IDbCommand command = ClientManager_SubSchema.DeleteAttachedClientCommand(transaction, clientSystemId))
command.ExecuteNonQuery();
IMediaLibrary mediaLibrary = ServiceRegistration.Get <IMediaLibrary>();
mediaLibrary.DeleteMediaItemOrPath(clientSystemId, null, true);
mediaLibrary.RemoveSharesOfSystem(clientSystemId);
transaction.Commit();
}
catch (Exception e)
{
ServiceRegistration.Get <ILogger>().Error("ClientManager: Error detaching client '{0}'", e, clientSystemId);
transaction.Rollback();
throw;
}
ServiceRegistration.Get <ILogger>().Info("ClientManager: Client with system ID '{0}' detached", clientSystemId);
// Last action: Remove the client from the collection of attached clients and disconnect the client connection, if connected
_attachedClients = ReadAttachedClientsFromDB();
_controlPoint.RemoveAttachedClient(clientSystemId);
ClientManagerMessaging.SendClientAttachmentChangeMessage(ClientManagerMessaging.MessageType.ClientDetached, clientSystemId);
}
public int Execute()
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
using (IDbCommand command = transaction.CreateCommand())
{
string countAlias;
string statementStr;
IList <BindVar> bindVars;
MainQueryBuilder builder = new MainQueryBuilder(_miaManagement, new QueryAttribute[] {}, null,
_necessaryRequestedMIATypes, new MediaItemAspectMetadata[] {}, _filter, null);
IDictionary <QueryAttribute, string> qa2a;
builder.GenerateSqlGroupByStatement(out countAlias, out qa2a, out statementStr, out bindVars);
command.CommandText = statementStr;
foreach (BindVar bindVar in bindVars)
{
database.AddParameter(command, bindVar.Name, bindVar.Value, bindVar.VariableType);
}
return(Convert.ToInt32(command.ExecuteScalar()));
}
}
finally
{
transaction.Dispose();
}
}
/// <summary>
/// Returns a dictionary which maps the system ids of all attached clients to their last hostname.
/// </summary>
/// <returns>Dictionary with system ids mapped to host names.</returns>
protected IDictionary <string, MPClientMetadata> ReadAttachedClientsFromDB()
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
int systemIdIndex;
int lastHostNameIndex;
int lastClientNameIndex;
IDictionary <string, MPClientMetadata> result = new Dictionary <string, MPClientMetadata>();
using (IDbCommand command = ClientManager_SubSchema.SelectAttachedClientsCommand(transaction, out systemIdIndex,
out lastHostNameIndex, out lastClientNameIndex))
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string clientSystemId = database.ReadDBValue <string>(reader, systemIdIndex);
string lastClientHostName = database.ReadDBValue <string>(reader, lastHostNameIndex);
SystemName lastHostName = lastClientHostName == null ? null : new SystemName(lastClientHostName);
string lastClientName = database.ReadDBValue <string>(reader, lastClientNameIndex);
result.Add(clientSystemId, new MPClientMetadata(clientSystemId, lastHostName, lastClientName));
}
}
return(result);
}
finally
{
transaction.Dispose();
}
}
protected IEnumerable <(Guid Id, string Name, DateTime Date)> GetMiaTypes(bool useBackupTable = false)
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>(false);
using (ITransaction transaction = database.BeginTransaction())
{
List <(Guid Id, string Name, DateTime Date)> miams = new List <(Guid Id, string Name, DateTime Date)>();
int idIndex;
int nameIndex;
int dateIndex;
IDbCommand command;
if (useBackupTable)
{
command = SelectAllBackupMediaItemAspectMetadataNameAndCreationDatesCommand(transaction, out idIndex, out nameIndex, out dateIndex);
}
else
{
command = MediaLibrary_SubSchema.SelectAllMediaItemAspectMetadataNameAndCreationDatesCommand(transaction, out idIndex, out nameIndex, out dateIndex);
}
using (command)
using (IDataReader reader = command.ExecuteReader())
{
ICollection <string> result = new List <string>();
while (reader.Read())
{
miams.Add((reader.GetGuid(idIndex), reader.GetString(nameIndex), reader.GetDateTime(dateIndex)));
}
}
return(miams);
}
}
public HistorySearch(DataRow row, ISQLDatabase database)
{
StartTime = new DateTime(long.Parse(row["startTime"].ToString()));
EndTime = new DateTime(long.Parse(row["endTime"].ToString()));
ProcessedFilesCount = uint.Parse(row["processedFilesCount"].ToString());
_id = int.Parse(row["search_id"].ToString());
_database = database;
}
public HistorySearch(DataRow row, ISQLDatabase database)
{
StartTime = new DateTime(long.Parse(row["startTime"].ToString()));
EndTime = new DateTime(long.Parse(row["endTime"].ToString()));
ProcessedFilesCount = uint.Parse(row["processedFilesCount"].ToString());
_id = int.Parse(row["search_id"].ToString());
_database = database;
}
public SQLTable(String Name, ISQLTableKey TableKey, ISQLDatabase Database) : this(Name, Database)
{
if (TableKey == null)
{
throw new ArgumentOutOfRangeException("SQL Table TableKey Cannot Be Null Or Whitespace.");
}
this.TableKey = TableKey;
}
public IEnumerable <String> GetTableNames(ISQLDatabase SQLDatabase)
{
DataTable Schema = ConnectionManager.GetSchema(SQLDatabase.ConnectionCredentials, EMSSQLSchemaType.DBTables);
foreach (String TableName in Schema.GetColumnNameValues("TABLE_NAME"))
{
yield return(TableName);
}
}
public HomogenousMap Execute()
{
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>();
ITransaction transaction = database.BeginTransaction();
try
{
using (IDbCommand command = transaction.CreateCommand())
{
string valueAlias;
string groupSizeAlias;
string statementStr;
IList <BindVar> bindVars;
if (_selectAttribute.Cardinality == Cardinality.Inline || _selectAttribute.Cardinality == Cardinality.ManyToOne)
{
QueryAttribute selectAttributeQA = new QueryAttribute(_selectAttribute);
MainQueryBuilder builder = new MainQueryBuilder(_miaManagement,
new QueryAttribute[] { selectAttributeQA }, _selectProjectionFunction,
_necessaryRequestedMIATypes, new MediaItemAspectMetadata[] {}, _filter, null);
IDictionary <QueryAttribute, string> qa2a;
builder.GenerateSqlGroupByStatement(out groupSizeAlias, out qa2a, out statementStr, out bindVars);
valueAlias = qa2a[selectAttributeQA];
}
else
{
ComplexAttributeQueryBuilder builder = new ComplexAttributeQueryBuilder(_miaManagement, _selectAttribute,
_selectProjectionFunction, _necessaryRequestedMIATypes, _filter);
builder.GenerateSqlGroupByStatement(_selectAttributeFilter, out valueAlias, out groupSizeAlias,
out statementStr, out bindVars);
}
command.CommandText = statementStr;
foreach (BindVar bindVar in bindVars)
{
database.AddParameter(command, bindVar.Name, bindVar.Value, bindVar.VariableType);
}
Type valueType = _projectionValueType ?? _selectAttribute.AttributeType;
HomogenousMap result = new HomogenousMap(valueType, typeof(int));
using (IDataReader reader = command.ExecuteReader())
{
int valueCol = reader.GetOrdinal(valueAlias);
int groupSizeCol = reader.GetOrdinal(groupSizeAlias);
while (reader.Read())
{
result.Add(database.ReadDBValue(valueType, reader, valueCol),
database.ReadDBValue <int>(reader, groupSizeCol));
}
}
return(result);
}
}
finally
{
transaction.Dispose();
}
}
private void InitAsync()
{
ServiceRegistration.Get <ILogger>().Info("SlimTvService: Initialising");
Task.Delay(MAX_INIT_MS).ContinueWith((t) =>
{
if (_initComplete.Task.Status != TaskStatus.RanToCompletion)
{
_initComplete.TrySetResult(false);
ServiceRegistration.Get <ILogger>().Error("SlimTvService: Initialization timed out.");
}
});
ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>(false);
if (database == null)
{
ServiceRegistration.Get <ILogger>().Error("SlimTvService: Database not available.");
_initComplete.TrySetResult(false);
return;
}
using (var transaction = database.BeginTransaction())
{
// Prepare TV database if required.
PrepareTvDatabase(transaction);
PrepareConnection(transaction);
}
// Initialize integration into host system (MP2-Server)
PrepareIntegrationProvider();
// Needs to be done after the IntegrationProvider is registered, so the TVCORE folder is defined.
PrepareProgramData();
// Register required filters
PrepareFilterRegistrations();
// Get all current connected clients, so we can later detect disconnections
UpdateClientList();
// Run the actual TV core thread(s)
InitTvCore();
if (_abortInit)
{
ServiceRegistration.Get <ILogger>().Error("SlimTvService: Initialization aborted.");
_initComplete.TrySetResult(false);
DeInit();
return;
}
// Prepare the MP2 integration
PrepareMediaSources();
ServiceRegistration.Get <ILogger>().Info("SlimTvService: Initialised");
_initComplete.TrySetResult(true);
}
public static IDbCommand DeleteSharesOfSystemCommand(ITransaction transaction, string systemId)
{
IDbCommand result = transaction.CreateCommand();
result.CommandText = "DELETE FROM SHARES WHERE SYSTEM_ID = @SYSTEM_ID";
ISQLDatabase database = transaction.Database;
database.AddParameter(result, "SYSTEM_ID", systemId, typeof(string));
return(result);
}
public static IDbCommand DeleteMediaItemAspectMetadataCommand(ITransaction transaction, Guid aspectId)
{
IDbCommand result = transaction.CreateCommand();
result.CommandText = "DELETE FROM MIA_TYPES WHERE MIAM_ID=@MIAM_ID";
ISQLDatabase database = transaction.Database;
database.AddParameter(result, "MIAM_ID", aspectId, typeof(Guid));
return(result);
}
public static IDbCommand DeleteAttachedClientCommand(ITransaction transaction, string systemId)
{
IDbCommand result = transaction.CreateCommand();
result.CommandText = "DELETE FROM ATTACHED_CLIENTS WHERE SYSTEM_ID = @SYSTEM_ID";
ISQLDatabase database = transaction.Database;
database.AddParameter(result, "SYSTEM_ID", systemId, typeof(string));
return(result);
}
public static IDbCommand DeleteUserProfileCommand(ITransaction transaction, Guid profileId)
{
IDbCommand result = transaction.CreateCommand();
result.CommandText = "DELETE FROM USER_PROFILES WHERE PROFILE_ID=@PROFILE_ID";
ISQLDatabase database = transaction.Database;
database.AddParameter(result, "PROFILE_ID", profileId, typeof(Guid));
return(result);
}
public static IDbCommand DeleteSubSchemaCommand(ITransaction transaction, string subSchemaName)
{
IDbCommand result = transaction.CreateCommand();
result.CommandText = "DELETE FROM MEDIAPORTAL_BASIS WHERE SUBSCHEMA_NAME=@SUBSCHEMA_NAME";
ISQLDatabase database = transaction.Database;
database.AddParameter(result, "SUBSCHEMA_NAME", subSchemaName, typeof(string));
return(result);
}
protected Guid AddMediaItem(ISQLDatabase database, ITransaction transaction)
{
Guid mediaItemId;
using (IDbCommand command = MediaLibrary_SubSchema.InsertMediaItemCommand(transaction, out mediaItemId))
command.ExecuteNonQuery();
return mediaItemId;
}
protected static string Preprocess(string origScript, ISQLDatabase database)
{
// Replace simple types
StringBuilder result = new StringBuilder(origScript);
result = result.Replace("%TIMESTAMP%", GetType(typeof(DateTime), database)).
Replace("%CHAR%", GetType(typeof(Char), database)).
Replace("%BOOLEAN%", GetType(typeof(Boolean), database)).
Replace("%SINGLE%", GetType(typeof(Single), database)).
Replace("%DOUBLE%", GetType(typeof(Double), database)).
Replace("%SMALLINT%", GetType(typeof(Int16), database)).
Replace("%INTEGER%", GetType(typeof(Int32), database)).
Replace("%BIGINT%", GetType(typeof(Int64), database)).
Replace("%GUID%", GetType(typeof(Guid), database)).
Replace("%BINARY%", GetType(typeof(byte[]), database));
// For extended replacements: First collect all patterns to be replaced...
IDictionary<string, string> replacements = new Dictionary<string, string>();
string interimStr = result.ToString();
// %STRING([N])%
Match match = new Regex(@"%STRING\((\d*)\)%").Match(interimStr);
while (match.Success)
{
string pattern = match.Value;
if (!replacements.ContainsKey(pattern))
{
uint length = uint.Parse(match.Groups[1].Value);
replacements.Add(pattern, database.GetSQLVarLengthStringType(length));
}
match = match.NextMatch();
}
// %STRING_FIXED([N])%
match = new Regex(@"%STRING_FIXED\((\d*)\)%").Match(interimStr);
while (match.Success)
{
string pattern = match.Value;
if (!replacements.ContainsKey(pattern))
{
uint length = uint.Parse(match.Groups[1].Value);
replacements.Add(pattern, database.GetSQLFixedLengthStringType(length));
}
match = match.NextMatch();
}
// %CREATE_NEW_GUID% / %GET_LAST_GUID%
string lastGuid = null;
match = new Regex(@"(%CREATE_NEW_GUID%)|(%GET_LAST_GUID%)").Match(interimStr);
while (match.Success)
{
Group g;
if ((g = match.Groups[1]).Success) // %CREATE_NEW_GUID% matched
result.Replace("%CREATE_NEW_GUID%", lastGuid = Guid.NewGuid().ToString("B"), g.Index, g.Length);
else if ((g = match.Groups[2]).Success) // %GET_LAST_GUID% matched
result.Replace("%GET_LAST_GUID%", lastGuid, g.Index, g.Length);
match = match.NextMatch();
}
// ... then do the actual replacements
result = replacements.Aggregate(result, (current, replacement) => current.Replace(replacement.Key, replacement.Value));
return result.ToString();
}
public SQLCETransaction(ISQLDatabase database, IDbConnection connection, SqlCeTransaction transaction)
{
_database = database;
_connection = connection;
_transaction = transaction;
}
protected object ReadObject(ISQLDatabase database, IDataReader reader, int colIndex, MediaItemAspectMetadata.AttributeSpecification spec)
{
// Because the IDataReader interface doesn't provide a getter method which takes the desired return type,
// we have to write this method
Type type = spec.AttributeType;
try
{
return database.ReadDBValue(type, reader, colIndex);
}
catch (ArgumentException)
{
throw new NotSupportedException(string.Format(
"The datatype '{0}' of attribute '{1}' in media item aspect type '{2}' (id '{3}') is not supported", type, spec.AttributeName, spec.ParentMIAM.Name, spec.ParentMIAM.AspectId));
}
}
public SQLDataSet(ISQLDatabase database, string sql, string name = "")
{
_database = database;
_sql = sql;
_name = name;
}
public MySQLTransaction(ISQLDatabase database, IDbConnection connection, MySqlTransaction transaction)
{
_database = database;
_connection = connection;
_transaction = transaction;
}
public void ExecuteBatch(ISQLDatabase database, InstructionList instructions)
{
using (ITransaction transaction = database.BeginTransaction())
{
foreach (string instr in instructions)
using (IDbCommand cmd = transaction.CreateCommand())
{
cmd.CommandText = instr;
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
}
