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)); }