public LatchApplication GetApplication()
{
var query = Sql.Builder
.Select("TOP 1 *")
.From("LatchApplication");
var application = db.FirstOrDefault <LatchApplication>(query);
return(application);
}
public TState GetByKey(int status)
{
var query = new Sql($"SELECT * FROM TinifierState WHERE Status = {status}");
var state = _database.FirstOrDefault <TState>(query);
return(state);
}
public TinyPNGResponseHistory GetByKey(int id)
{
var query = new Sql($"SELECT * FROM TinifierResponseHistory WHERE ImageId = {id}");
var history = _database.FirstOrDefault <TinyPNGResponseHistory>(query);
return(history);
}
public TImageStatistic GetStatistic()
{
var query = new Sql("SELECT * FROM TinifierImagesStatistic");
var statistic = _database.FirstOrDefault <TImageStatistic>(query);
return(statistic);
}
public TSetting GetSettings()
{
var query = new Sql("SELECT * FROM TinifierUserSettings ORDER BY Id DESC");
var setting = _database.FirstOrDefault <TSetting>(query);
return(setting);
}
private void CreateAndSaveMediaXml(XElement xml, int id, UmbracoDatabase db)
{
var poco = new ContentXmlDto {
NodeId = id, Xml = xml.ToString(SaveOptions.None)
};
var exists = db.FirstOrDefault <ContentXmlDto>("WHERE nodeId = @Id", new { Id = id }) != null;
int result = exists ? db.Update(poco) : Convert.ToInt32(db.Insert(poco));
}
/// <summary>
/// Get Statistic
/// </summary>
/// <returns>TImageStatistic</returns>
public TImageStatistic GetStatistic()
{
try
{
var query = new Sql("SELECT * FROM TinifierImagesStatistic");
return(_database.FirstOrDefault <TImageStatistic>(query));
}
catch (System.Exception e)
{
return(null);
}
}
/// <summary>
/// Get state with status
/// </summary>
/// <param name="status">status Id</param>
/// <returns>TState</returns>
public TState Get(int status)
{
try
{
var query = new Sql("SELECT * FROM TinifierState WHERE Status = @0", status);
return(_database.FirstOrDefault <TState>(query));
}
catch (Exception e)
{
return(null);
}
}
/// <summary>
/// Export the settings as an importable model
/// </summary>
/// <returns>Object of type <see cref="WorkflowSettingsExport"/></returns>
public WorkflowSettingsExport ExportSettings()
{
var poco = _database.FirstOrDefault <WorkflowSettingsPoco>("SELECT * FROM WorkflowSettings");
if (poco != null)
{
return(new WorkflowSettingsExport
{
DefaultApprover = poco.DefaultApprover,
EditUrl = poco.EditUrl,
Email = poco.Email,
ExcludeNodes = poco.ExcludeNodes,
FlowType = poco.FlowType,
SendNotifications = poco.SendNotifications,
SiteUrl = poco.SiteUrl
});
}
return(new WorkflowSettingsExport());
}
public bool PreviousMigrationsHaveFinishedCleanly()
{
try
{
UmbracoDatabase.OpenSharedConnection();
using (var transaction = GetTransaction())
{
var result = UmbracoDatabase.FirstOrDefault <MigrationHistory>("WHERE Completed = 0");
var previousMigrationsHaveFinishedCleanly = result == null;
transaction.Complete();
return(previousMigrationsHaveFinishedCleanly);
}
}
finally
{
UmbracoDatabase.CloseSharedConnection();
}
}
public bool HasMigrationExecuted(IUmbracoMigration migration)
{
var migrationName = migration.GetType().Name;
try
{
UmbracoDatabase.OpenSharedConnection();
using (var transaction = GetTransaction())
{
var migrationHistory = UmbracoDatabase.FirstOrDefault <MigrationHistory>("WHERE Name = @Name", new { Name = migrationName });
var hasMigrationExecuted = migrationHistory != null;
Log.Debug(string.Format("HasMigrationExecuted for {0} is {1}", migrationName, hasMigrationExecuted));
transaction.Complete();
return(hasMigrationExecuted);
}
}
finally
{
UmbracoDatabase.CloseSharedConnection();
}
}
private static void MigrateVortoArchetypeDataIdsToUdis(UmbracoDatabase database)
{
// Find content picker datatypes
string contentPickerSql = @"SELECT umbracoNode.uniqueID
FROM cmsDataType
JOIN umbracoNode ON umbracoNode.id = cmsDataType.nodeId
WHERE cmsDataType.propertyEditorAlias = 'Umbraco.ContentPicker2'";
var contentPickers = database.Query <Guid>(contentPickerSql).ToList();
foreach (var contentPicker in contentPickers)
{
// Find archetypes using content pickers
string archetypeSql = $@"SELECT umbracoNode.uniqueID, cmsDataTypePreValues.value
FROM cmsPropertyType
JOIN cmsDataType ON cmsDataType.nodeId = cmsPropertyType.dataTypeId
JOIN cmsDataTypePreValues ON cmsDataTypePreValues.datatypeNodeId = cmsDataType.nodeId AND cmsDataTypePreValues.alias = 'archetypeConfig'
JOIN umbracoNode ON umbracoNode.id = cmsPropertyType.dataTypeId
WHERE cmsDataType.propertyEditorAlias IN ('Imulus.Archetype')
AND cmsDataTypePreValues.value LIKE '%""dataTypeGuid"": ""{contentPicker}""%'";
var archetypes = database.Query <ArchetypeConfigRow>(archetypeSql).ToList();
foreach (var archetype in archetypes)
{
// Find Vorto properties of archetypes using content pickers
var sql = $@"SELECT cmsPropertyData.id, cmsPropertyData.contentNodeId, cmsPropertyType.alias, dataNvarchar, dataNtext, dataInt, cmsDocument.*
FROM cmsPropertyData
JOIN cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid
JOIN cmsDataType ON cmsDataType.nodeId = cmsPropertyType.dataTypeId
JOIN cmsDataTypePreValues ON cmsDataTypePreValues.datatypeNodeId = cmsDataType.nodeId AND cmsDataTypePreValues.alias = 'dataType'
JOIN cmsContentVersion ON cmsContentVersion.VersionId = cmsPropertyData.versionId
JOIN umbracoNode ON umbracoNode.id = cmsContentVersion.ContentId
JOIN cmsDocument ON cmsDocument.nodeId = umbracoNode.id
WHERE cmsDataType.propertyEditorAlias IN ('Our.Umbraco.Vorto')
AND cmsDataTypePreValues.value LIKE '%""propertyEditorAlias"": ""Imulus.Archetype""%'
AND cmsDataTypePreValues.value LIKE '%""guid"": ""{archetype.UniqueID}""%'
AND(dataNtext IS NOT NULL)
AND(cmsDocument.published = 1 OR cmsDocument.newest = 1 OR cmsDocument.updateDate > (SELECT updateDate FROM cmsDocument AS innerDoc WHERE innerDoc.nodeId = cmsDocument.nodeId AND innerDoc.published = 1 AND newest = 1))
ORDER BY contentNodeId, cmsDataType.propertyEditorAlias";
var vortoDataToMigrate = database.Query <Row>(sql).ToList();
var config = JsonConvert.DeserializeObject <Archetype.Models.ArchetypePreValue>(archetype.Value);
var propertyAliases = config.Fieldsets.SelectMany(fieldset => fieldset.Properties)
.Where(property => property.DataTypeGuid == contentPicker)
.Select(property => property.Alias);
if (vortoDataToMigrate.Any())
{
foreach (var propertyData in vortoDataToMigrate)
{
string udiValue;
if (!string.IsNullOrEmpty(propertyData.dataNtext))
{
// Vorto multilingual values
var vortoValue = JsonConvert.DeserializeObject <Our.Umbraco.Vorto.Models.VortoValue>(propertyData.dataNtext);
var udiValues = new Dictionary <string, object>();
foreach (var value in vortoValue.Values)
{
// Archetype fieldsets
var archetypeValue = JsonConvert.DeserializeObject <Archetype.Models.ArchetypeModel>(value.Value.ToString());
var fieldsets = archetypeValue.Fieldsets
.Where(fieldset => fieldset.Properties.Any(property =>
propertyAliases.Contains(property.Alias)));
foreach (var fieldset in fieldsets)
{
// Properties using content picker
var properties = fieldset.Properties.Where(property =>
propertyAliases.Contains(property.Alias));
foreach (var property in properties)
{
var intValue = property.GetValue <int>();
if (intValue > 0)
{
var uniqueId = database.FirstOrDefault <Guid>(
$"SELECT uniqueId FROM umbracoNode WHERE id = @0", intValue);
property.Value = $"umb://document/{uniqueId:N}";
}
else
{
property.Value = null;
}
}
}
udiValues[value.Key] = ToDataValue(archetypeValue);
}
vortoValue.Values = udiValues;
udiValue = ToDataValue(vortoValue);
}
else
{
LogHelper.Info(typeof(ContentPickerIdToUdiMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) skipping property {propertyData.alias} - null dataNtext");
continue;
}
LogHelper.Info(typeof(ContentPickerIdToUdiMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) converting property {propertyData.alias} from {propertyData.dataNtext} to {udiValue}");
database.Execute("UPDATE cmsPropertyData SET dataNtext=@0 WHERE id=@1", udiValue, propertyData.id);
}
}
}
}
}
public Person GetByName(string personName)
{
return(_database.FirstOrDefault <Person>("WHERE [Name] = @0", personName));
}
/// <summary>
/// Get former state of a certain media
/// </summary>
/// <param name="id">Id of a media</param>
/// <returns>TinifierMediaHistory</returns>
public TinifierMediaHistory Get(int id)
{
var query = new Sql($"SELECT MediaId, FormerPath FROM {_tableName} WHERE MediaId = {id}");
return(_database.FirstOrDefault <TinifierMediaHistory>(query));
}
/// <summary>
/// Creates the Configuration table
/// </summary>
public override void Up()
{
// Environment
Alter.Table <Environment103>().AddColumn(nameof(Environment103.SortOrder)).AsInt32().NotNullable().WithDefaultValue(999999);
Alter.Table <Environment103>().AddColumn(nameof(Environment103.Enable)).AsBoolean().NotNullable().WithDefaultValue(true);
Alter.Table <Environment103>().AddColumn(nameof(Environment103.ContinueProcessing)).AsBoolean().NotNullable().WithDefaultValue(true);
Alter.Table <Environment103>().AddColumn(nameof(Environment103.ColorIndicator)).AsString(7).NotNullable().WithDefaultValue("#df7f48");
// Journal
Delete.ForeignKey("FK_" + nameof(Shield) + "_" + nameof(Journal) + "_" + nameof(Dto.Configuration)).OnTable <Journal101>();
Create.Index("IX_" + nameof(Shield) + "_" + nameof(Journal103.Datestamp)).OnTable <Journal103>()
.OnColumn(nameof(Journal103.Datestamp)).Ascending().WithOptions().NonClustered();
// Configuration
Delete.Index("IX_" + nameof(Shield) + "_" + nameof(Configuration101.AppId)).OnTable <Configuration101>();
Create.Index("IX_" + nameof(Shield) + "_" + nameof(Configuration103.AppId)).OnTable <Configuration103>()
.OnColumn(nameof(Configuration103.AppId)).Ascending().WithOptions().NonClustered();
// Check if Backoffice Access has been installed, as we may
// need to update the configuration to match the new class
var sql = new Sql();
sql.Where <Configuration>(x => x.AppId == "BackofficeAccess");
var config = _database.FirstOrDefault <Configuration>(sql);
if (config == null)
{
return;
}
var definition = new
{
backendAccessUrl = "",
ipAddressesAccess = 0,
ipAddresses = new IpEntry103[0],
unauthorisedAction = TransferTypes.Redirect,
unauthorisedUrlType = UmbracoUrlTypes.Url,
unauthorisedUrl = "",
unauthorisedUrlXPath = "",
unauthorisedUrlContentPicker = ""
};
// Deserialize the current config to an anonymous object
var oldData = JsonConvert.DeserializeAnonymousType(config.Value, definition);
// Copy the configuration to the new anonymous object
var newData = new
{
oldData.backendAccessUrl,
oldData.ipAddressesAccess,
oldData.ipAddresses,
oldData.unauthorisedAction,
urlType = new UrlType103
{
UrlSelector = oldData.unauthorisedUrlType,
StrUrl = oldData.unauthorisedUrl,
XPathUrl = oldData.unauthorisedUrlXPath,
ContentPickerUrl = oldData.unauthorisedUrlContentPicker
}
};
// serialize the new configuration to the db entry's value
config.Value = JsonConvert.SerializeObject(newData, Formatting.None);
// Update the entry within the DB.
_database.Update(config);
}
/// <summary>
/// Get state with status
/// </summary>
/// <param name="status">status Id</param>
/// <returns>TState</returns>
public TState Get(int status)
{
var query = new Sql("SELECT * FROM TinifierState WHERE Status = @0", status);
return(_database.FirstOrDefault <TState>(query));
}
/// <summary>
/// Get history by Id
/// </summary>
/// <param name="id">history Id</param>
/// <returns>TinyPNGResponseHistory</returns>
public TinyPNGResponseHistory Get(int id)
{
var query = new Sql("SELECT * FROM TinifierResponseHistory WHERE ImageId = @0", id);
return(_database.FirstOrDefault <TinyPNGResponseHistory>(query));
}