Copied to clipboard

Flag this post as spam?

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


  • Anders Burla Johansen 2560 posts 8255 karma points
    Mar 12, 2016 @ 12:17
    Anders Burla Johansen
    0

    Guide for custom tables in UaaS

    So we have a client that use UaaS. They have now asked for a feature that requires a couple of custom DB tables. So the big question - how do you do this with UaaS?? Normally I would just connect to the DB, create the two tables and use the PetaPoco in Umbraco to add entries and fetch them. A complete guide and/or code examples would be great. AND also a guide from the start of connecting to the DB and create the tables in dev environment and how to push the schema to live etc. The data will NOT need to be transfered - it is only the DB tables and the schema of it.

    Kind regards

    Anders

  • Dave Woestenborghs 3113 posts 10097 karma points MVP 3x admin c-trib
    Mar 12, 2016 @ 12:32
    Dave Woestenborghs
    0

    Hi Anders,

    As mentioned on twitter we use the migration runner that ships with Umbraco. This is what also is used to handle Umbraco db upgrades. I haven't tried it on UAAS yet, but it should work otherwise umbraco can't do upgrades themselves.

    The code we have in startup looks like this :

            var currentVersion = new SemVersion(0, 0, 0);
    
            // get all migrations already executed on DB
        var migrations =
            ApplicationContext.Current.Services.MigrationEntryService.GetAll(
                "YourApplicationName");
    
            // get the latest migration executed on DB
        var latestMigration = migrations.OrderByDescending(x => x.Version).FirstOrDefault();
    
        if (latestMigration != null)
        {
            currentVersion = latestMigration.Version;
        }
    
        var dbversion = new Version("1.0.4");
    
        var targetVersion = new SemVersion(
            dbversion.Major,
            dbversion.Minor,
            dbversion.Build,
            string.Empty,
            dbversion.Revision > 0 ? dbversion.Revision.ToInvariantString() : null);
    
        if (targetVersion == currentVersion)
        {
            // we are up to date
            return;
        }
    
        var migrationsRunner = new MigrationRunner(
           ApplicationContext.Current.Services.MigrationEntryService,
           ApplicationContext.Current.ProfilingLogger.Logger,
           currentVersion,
           targetVersion,
           "YourApplicationName");
    
        try
        {
            migrationsRunner.Execute(UmbracoContext.Current.Application.DatabaseContext.Database);
        }
        catch (System.Web.HttpException e)
        {
            // because umbraco runs some other migrations after the migration runner is executed we get httpexception
            // we cacht this error, but don't do anything
                // fixed in future versions see : http://issues.umbraco.org/issue/U4-8077
        }
        catch (Exception e)
        {
            // we catch all other errors
            LogHelper.Error<DatabaseRepository>("Error running migrations", e);
        }
    

    A migration will look like this :

    using Umbraco.Core.Logging;
        using Umbraco.Core.Persistence.Migrations;
        using Umbraco.Core.Persistence.SqlSyntax;
    
        /// <summary>
        /// The add read column to contact table.
        /// </summary>
       [Migration("1.0.2", 1, Common.Constants.Database.Migration.ApplicationName)]
        public class AddReadColumnToContactTable : MigrationBase
        {
            /// <summary>
            /// Initializes a new instance of the <see cref="AddReadColumnToContactTable"/> class.
            /// </summary>
            /// <param name="sqlSyntax">
            /// The sql syntax.
            /// </param>
            /// <param name="logger">
            /// The logger.
            /// </param>
            public AddReadColumnToContactTable(ISqlSyntaxProvider sqlSyntax, ILogger logger)
                : base(sqlSyntax, logger)
            {
            }
    
            /// <summary>
            /// Upgrade instructions
            /// </summary>
            public override void Up()
            {
                this.Alter.Table(Common.Constants.Database.ContactFormTable.TableName)
                    .AddColumn(Common.Constants.Database.ContactFormTable.IsRead)
                    .AsBoolean()
                    .Nullable()
                    .WithDefaultValue(0);
    
                this.Execute.Sql("UPDATE [customContactForm] SET IsRead = 0");
    
                this.Alter.Table(Common.Constants.Database.ContactFormTable.TableName)
                    .AlterColumn(Common.Constants.Database.ContactFormTable.IsRead)
                    .AsBoolean()
                    .NotNullable();            
            }
    
            /// <summary>
            /// Downgrade instructions
            /// </summary>
            public override void Down()
            {
                this.Delete.Column(Common.Constants.Database.ContactFormTable.IsRead)
                   .FromTable(Common.Constants.Database.ContactFormTable.TableName);
            }
        }
    }
    

    You need to inherit from MigrationBase and add the Migration attribute to your class. You can have multiple migration classes in one version by the way.

    Have a look at the Umbraco Migrations to see some more examples : https://github.com/umbraco/Umbraco-CMS/tree/dev-v7/src/Umbraco.Core/Persistence/Migrations/Upgrades

    Dave

  • Sebastiaan Janssen 4669 posts 13067 karma points MVP admin hq
    Mar 12, 2016 @ 12:36
    Sebastiaan Janssen
    0

    You can "just connect to the DB, create the two tables and use the PetaPoco in Umbraco to add entries and fetch them". :-)

    In your case I guess your question is mostly "how do I deploy that"? So that the tables are also created in the next environment (dev, staging, live)? How would you do this common scenario normally?

    I am just teasing a little bit from our twitter converstation about it. Although I AM curious how you normally do this (as it should be completely the same on a UaaS site as on any other site).

    So.. how? Well, you have the connection string for each environment and can connect to that to create tables.

    The better way to do this, without manual intervention is to check on startup if the tables exist and if not, to create them. Umbraco uses migrations for that. So when Umbraco detects that a site has been upgraded, the migrations runner runs to alter/add tables and do other database migrations. We want to open up the migrations framework for Umbraco plugins as well, but this is not yet available. So you can do a poor-man's migration:

    • Add an application startup handler
    • Check if there's a marker in App_Data\TEMP, the marker can be a totally empty file with a predictable filename, for example: TeaCommerceInstalled.txt
    • If the file is there: great, you don't need to do anything, everything is already installed
      • The benefit of using a marker file is that you have a very inexpensive File.Exists operation instead of a very expensive database query to figure out if tables exist
      • Putting these marker files in App_Data\TEMP also makes sure that the marker file doesn't get committed to source control, so it does not exist on the environment this is deployed to until you create it from your startup handler
    • Speaking of startup handlers: if the marker file is NOT detected you can "just connect to the DB, create the two tables and use the PetaPoco in Umbraco to add entries and fetch them"

    We do this poor man's migration for this very site (Our Umbraco) so have a look at the source for those for inspiration: https://github.com/umbraco/OurUmbraco/blob/cbb752c93cddb4c974321714b603638a24195626/OurUmbraco/Our/MigrationsHandler.cs

  • Anders Burla Johansen 2560 posts 8255 karma points
    Mar 12, 2016 @ 12:40
    Anders Burla Johansen
    0

    Hi Dave

    THANKS! So the first code you have. What .cs file is that in? You state startup - is it in an Umbraco event, or .NET startup event - could you maybe edit the source so its possible to see where it is located in a class? and maybe also where it is placed on disk.

    Do you by chance have an example of the creation of a new table with a couple of columns? As that is what I need :)

  • Dave Woestenborghs 3113 posts 10097 karma points MVP 3x admin c-trib
    Mar 12, 2016 @ 12:54
    Dave Woestenborghs
    1

    Hi Anders,

    Here is an example of a create migration :

    [Migration("1.0.0", 1, Migration.ApplicationName)]
    public class CreatedContactTable : MigrationBase
    {
    
        public CreatedContactTable(ISqlSyntaxProvider sqlSyntax, ILogger logger)
            : base(sqlSyntax, logger)
        {
        }
    
    
        public override void Up()
        {
            this.Create.Table(ContactFormTable.TableName)
                .WithColumn(ContactFormTable.IdColumn).AsInt32().Identity().PrimaryKey("PK_" + ContactFormTable.TableName)
                .WithColumn(ContactFormTable.NameColumn).AsString(100).NotNullable()
                .WithColumn(ContactFormTable.EmailColumn).AsString(255).NotNullable()
                .WithColumn(ContactFormTable.MessageColumn).AsString(512).NotNullable();
        }
    
    
        public override void Down()
        {
            this.Delete.Table(ContactFormTable.TableName);
        }
    }
    

    The code is ran when umbraco is started :

    internal class BootManager : ApplicationEventHandler
        {
    
            protected override void ApplicationStarted(UmbracoApplicationBase umbracoApplication, ApplicationContext applicationContext)
            {
                    // insert code from previous post here to execute       
            }
    
       }
    

    Dave

  • Sebastiaan Janssen 4669 posts 13067 karma points MVP admin hq
    Mar 12, 2016 @ 12:46
    Sebastiaan Janssen
    0

    Dave's solution is also great, forgot that most of this stuff is now pretty much available. The migrations sometimes (especially in Umbraco Core) will need to run before anything in Umbraco starts though, so sometimes they might run too late (depending on how your code relies on database schema existing. This might also be a problem for the poor man's approach.

    Ideally we want to have the Umbraco installer run plugin migrations as well, so you'll get the upgrade screen when you deploy and it will run the custom migrations as well as Umbraco's own migration.

    Extra ideally we want to be able to run those in an unattended mode as well so that people who happen to visit that site while the upgrade is running, they don't get redirected to the login screen.

    Note that you can execute ANY code in Up and Down so it's not limited to database queries. Also note that you will want to check if tables exist before you try to create them.

  • Anders Burla Johansen 2560 posts 8255 karma points
    Mar 12, 2016 @ 13:04
    Anders Burla Johansen
    0

    Great answers!

    @Sebastiaan - Normally we connect to live DB and only need to change that one DB. Don't have a setup where people have local databases, so we haven't had the need to figure these things out until now for a client site :)

  • David Brendel 738 posts 2778 karma points c-trib
    Mar 12, 2016 @ 21:21
    David Brendel
    0

    Just being curious if there are any methods to also transfer data in custom tables from one environment to another.

    Table creation is clear and i had used the migrations also for while now but never figured out how you could transfer data.

  • Dave Woestenborghs 3113 posts 10097 karma points MVP 3x admin c-trib
    Mar 14, 2016 @ 07:26
    Dave Woestenborghs
    0

    Hi David,

    Didn't have the need yet for transfering data from one environment to another between deploys.

    Some migrations we created populate some tables after they have been created.

    We do this by using this in the migration :

    this.Execute.Sql("SQL statement goes here")
    

    It seems it's also possible to execute code during a migration with this.Execute.Code.

    But haven't tried this yet.

    You can also hookin in to the events of the Umbraco Migration runner by implementing a class that inherits from https://github.com/umbraco/Umbraco-CMS/blob/f13bc07ddc1d67da828c3beab39ddf216e15ccb0/src/Umbraco.Web/Strategies/Migrations/MigrationStartupHander.cs

    But again haven't tried that yet. I have created a issue which is fixed and will be in the next version of umbraco so you can target your migration events to a specific "application" : http://issues.umbraco.org/issue/U4-8077

    Maybe this can solve your problem

    Dave

Please Sign in or register to post replies

Write your reply to:

Draft