Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Bo Jacobsen 601 posts 2399 karma points
    Mar 06, 2019 @ 17:55
    Bo Jacobsen
    0

    How to get databaseschemahelper in umbraco 8

    Hi all.

    How do i convert this code to work with Umbraco 8 IComponent

    using Umbraco.Core;
    using Umbraco.Core.Persistence;
    
    public class TableCreationEvents : ApplicationEventHandler
    {
        protected override void ApplicationStarted(UmbracoApplicationBase umbracoApplication, ApplicationContext applicationContext)
        {
            var databaseContext = applicationContext.DatabaseContext;
            var databaseSchemaHelper = new DatabaseSchemaHelper(databaseContext.Database, applicationContext.ProfilingLogger.Logger, databaseContext.SqlSyntax);
    
            if (!databaseSchemaHelper.TableExist<CustomTable>())
            {
                databaseSchemaHelper.CreateTable<CustomTable>(true);
            }
        }
    }
    

    I got this.

    using Umbraco.Core;
    using Umbraco.Core.Composing;
    using Umbraco.Core.Logging;
    
    public class InstallerComposer : IUserComposer
    {
        public void Compose(Composition composition)
        {
            composition.Components().Append<CreateTableComponent>();
        }
    }
    
    public class CreateTableComponent : IComponent
    {
        private ILogger _logger;
    
        public CreateTableComponent(ILogger logger)
        {
            _logger = logger;
        }
    
        public void Initialize()
        {
    
        }
    
        public void Terminate()
        {
    
        }
    }
    
  • Marcio Goularte 388 posts 1360 karma points
    Mar 07, 2019 @ 18:21
  • Bo Jacobsen 601 posts 2399 karma points
    Mar 14, 2019 @ 19:32
    Bo Jacobsen
    0

    Hi Marcio.

    Thanks for posting. When i do use Migrate it seems that foreignkeys do not work.

    First the Migration

    using Project.Data;
    using Umbraco.Core.Migrations;
    
    namespace Project.Migration
    {
        public class CreateTableMigrationBase : MigrationBase
        {
            public CreateTableMigrationBase(IMigrationContext context) : base(context) { }
    
            public override void Migrate()
            {
                if (!TableExists("pdStatus"))
                {
                    Create.Table<Status>().Do();
                }
    
                if (!TableExists("pdAssignments"))
                {
                    Create.Table<Assignment>().Do();
                }
            }
        }
    
        public class CreateTableMigrationPlan : MigrationPlan
        {
            public CreateTableMigrationPlan() : base("ProjectBlackMagic")
            {
                From(string.Empty).To<CreateTableMigrationBase>("1.0");
            }
        }
    }
    

    Then the Components

    using Project.Migration;
    using Umbraco.Core;
    using Umbraco.Core.Composing;
    using Umbraco.Core.Logging;
    using Umbraco.Core.Migrations;
    using Umbraco.Core.Migrations.Upgrade;
    using Umbraco.Core.Scoping;
    using Umbraco.Core.Services;
    
    namespace Project.Components
    {
        public class CreateTableComponent : IComponent
        {
            private readonly IScopeProvider _scopeProvider;
            private readonly IMigrationBuilder _migrationBuilder;
            private readonly IKeyValueService _keyValueService;
            private readonly ILogger _logger;
    
    
            public CreateTableComponent(IScopeProvider scopeProvider, IMigrationBuilder migrationBuilder, IKeyValueService keyValueService, ILogger logger)
            {
                _scopeProvider = scopeProvider;
                _migrationBuilder = migrationBuilder;
                _keyValueService = keyValueService;
                _logger = logger;
            }
    
            public void Initialize()
            {
                var upgrader = new Upgrader(new CreateTableMigrationPlan());
                upgrader.Execute(_scopeProvider, _migrationBuilder, _keyValueService, _logger);
            }
    
            public void Terminate() {}
        }
    
        public class InstallerComposer : IUserComposer
        {
            public void Compose(Composition composition)
            {
                composition.Components().Append<CreateTableComponent>();
            }
        }
    }
    

    Now the Data

    using NPoco;
    using Umbraco.Core.Persistence.DatabaseAnnotations;
    
    namespace Project.Data
    {
        [TableName("pdStatus")]
        [PrimaryKey("Id", AutoIncrement = true)]
        public class Status
        {
            public int Id { get; set; }
    
            [Length(80)]
            [NullSetting(NullSetting = NullSettings.NotNull)]
            public string Name { get; set; }
        }
    
        [TableName("pdAssignments")]
        [PrimaryKey("Id", AutoIncrement = true)]
        public class Assignment
        {
            public int Id { get; set; }
    
            [NullSetting(NullSetting = NullSettings.NotNull)]
            public string Note { get; set; }
    
            [Reference(ReferenceType.Foreign, ColumnName = "FkStatusId", ReferenceMemberName = "Id")]
            public Status Status { get; set; }
        }
    }
    

    I also tried this

    public class Assignment
    {
        public int Id { get; set; }
    
        [ForeignKey(typeof(Status), Column = "Id", Name = "FK_pdAssignments_pdStatus")]
        public int FkStatusId { get; set; }
    
        [ResultColumn]
        public Status Status { get; set; }
    }
    
    public class Assignment
    {
        public int Id { get; set; }
    
        [Reference(ReferenceType.Foreign, ColumnName = "Id", ReferenceMemberName = "Id")]
        public Status Status { get; set; }
    }
    

    All given the same error:

    [BootFailedException: Boot failed: Umbraco cannot run. See Umbraco's log file for more details.
    
    -> Umbraco.Core.Exceptions.BootFailedException: Boot failed.
    
    -> System.InvalidOperationException: The sequence contains no corresponding elements
       at System.Linq.Enumerable.First[TSource](IEnumerable`1 source, Func`2 predicate)
       at Umbraco.Core.Persistence.SqlSyntax.SqlSyntaxProviderBase`1.FormatType(ColumnDefinition column)
       at Umbraco.Core.Persistence.SqlSyntax.SqlSyntaxProviderBase`1.<>c__DisplayClass54_0.<Format>b__0(Func`2 action)
       at System.Linq.Enumerable.WhereSelectListIterator`2.MoveNext()
       at System.Linq.Enumerable.WhereEnumerableIterator`1.MoveNext()
       at System.String.Join(String separator, IEnumerable`1 values)
       at Umbraco.Core.Persistence.SqlSyntax.SqlSyntaxProviderBase`1.Format(ColumnDefinition column)
       at Umbraco.Core.Persistence.SqlSyntax.SqlSyntaxProviderBase`1.Format(IEnumerable`1 columns)
       at Umbraco.Core.Persistence.SqlSyntax.SqlSyntaxProviderBase`1.Format(TableDefinition table)
       at Umbraco.Core.Migrations.Expressions.Create.Table.CreateTableOfDtoBuilder.Do()
       at Project.Migration.CreateTableMigrationBase.Migrate()
       at Umbraco.Core.Migrations.MigrationBase.Umbraco.Core.Migrations.IMigration.Migrate()
       at Umbraco.Core.Migrations.MigrationPlan.Execute(IScope scope, String fromState, IMigrationBuilder migrationBuilder, ILogger logger)
       at Umbraco.Core.Migrations.Upgrade.Upgrader.Execute(IScopeProvider scopeProvider, IMigrationBuilder migrationBuilder, IKeyValueService keyValueService, ILogger logger)
       at Project.Components.CreateTableComponent.Initialize()
       at Umbraco.Core.Composing.ComponentCollection.Initialize()
       at Umbraco.Core.Runtime.CoreRuntime.Boot(IRegister register, DisposableTimer timer)]
       Umbraco.Core.Exceptions.BootFailedException.Rethrow(BootFailedException bootFailedException) +226
       Umbraco.Web.<>c.<Init>b__23_0(Object sender, EventArgs args) +35
       System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +200
       System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) +132
       System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +73
    
  • Dee 118 posts 338 karma points
    Mar 11, 2023 @ 08:44
    Dee
    0

    any solutions for this?

  • Sam Beynon 8 posts 98 karma points
    Mar 13, 2023 @ 08:13
    Sam Beynon
    0

    The underlying issue here is that Bo was using the incorrect attributes for migrations, he was utilizing the NPOCO query attributes, but as the migrations are not part of NPOCO, the attributes were ineffective.

    Taking his example you would implement it in the following way -

    [TableName("pdStatus")]
    [PrimaryKey("Id", AutoIncrement = true)]
    public class Status
    {
        public int Id { get; set; }
    
        [Length(80)]
        [NullSetting(NullSetting = NullSettings.NotNull)]
        public string Name { get; set; }
    }
    
    [TableName("pdAssignments")]
    [PrimaryKey("Id", AutoIncrement = true)]
    public class Assignment
    {
        public int Id { get; set; }
    
        [NullSetting(NullSetting = NullSettings.NotNull)]
        public string Note { get; set; }
    
        [ForeignKey(typeof(Status))] //This handles the foreign key stuff for migrations.
        public int FKStatusId { get; set; }
    
        [ResultColumn] //This will prevent the migrations from attempting to create a field in the database called "Status" of type "status" which cannot be mapped to a DB column type.
        [Reference(ReferenceType.Foreign, ColumnName = "FkStatusId", ReferenceMemberName = "Id")] //This tells NPOCO how to recognise the relationship when you are querying.
        public Status Status { get; set; }
    }
    
Please Sign in or register to post replies

Write your reply to:

Draft