Copied to clipboard

Flag this post as spam?

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


  • Ryios 122 posts 263 karma points
    Jul 17, 2015 @ 16:07
    Ryios
    0

    Add New Table with Foreign Keys to umbraco tables?

    I'm trying to create a table to map some customize permissions for members to umbracoContentNodes...

    However I cannot use the ForeignKey attribute to reference the cmsContent table because the ContentDto table and all the Dto tables in the RDBMS namespace are marked internal.

    Is there another way to do it without using the ForeignKey attribute on my poco?

    I know that this could cause deletes to fail in umbraco code, but I am handling that with a Content Deleting event. Right before a content node is deleted, code checks if there is an entry in my table and deletes the referring table entry from my table to prevent the delete in umbraco from failing on my foreign key constraint.

    The way I am getting around this at the moment is to reproduce the ContentDto class, just what I need for the referrences...

    /// <summary>
    /// This Poco represents the table TLCKB will create to keep track of what permissions Members have to TLCKB Content Nodes
    /// </summary>
    [TableName("TLCKB_NodeMemberPermissions")]
    [PrimaryKey("id", autoIncrement = true)]
    public class NodeMemberPermissions
    {
        #region Foreign Key Tables
        /// <summary>
        /// These are here only to allow creating foreign keys, Umbraco has all these tables internal so we can map foreign keys to them without doing this...
        /// </summary>
        [ExplicitColumns, TableName("umbracoNode"), PrimaryKey("id")]
        internal class NodeDto 
        {
            [PrimaryKeyColumn(Name="PK_structure", IdentitySeed=0x41a), Column("id")]
            public int NodeId { get; set; }
        }
    
        [ExplicitColumns, PrimaryKey("nodeId", autoIncrement = false), TableName("cmsMember")]
        internal class MemberDto
        {
            [PrimaryKeyColumn(AutoIncrement = false), ForeignKey(typeof(ContentDto), Column = "nodeId"), ForeignKey(typeof(NodeDto)), Column("nodeId")]
            public int NodeId { get; set; }
        }
    
        [PrimaryKey("pk"), TableName("cmsContent"), ExplicitColumns]
        internal class ContentDto
        {
            [Column("pk"), PrimaryKeyColumn]
            public int PrimaryKey { get; set; }
        }
        #endregion
    
        [Column("id")]
        [PrimaryKeyColumn(AutoIncrement = true)]
        public int Id { get; set; }
    
        [Column("nodeId"), ForeignKey(typeof(ContentDto))]
        public int ContentNodeId { get; set; }
    
        [Column("NodeType")]
        public TLCKBNodeType NodeType { get; set; }
    
        [Column("MemberId"), ForeignKey(typeof(MemberDto))]
        public int? MemberId { get; set; }
    
        [Column("Role")]
        public string Role { get; set; }
    
        [Column("Permissions")]
        public ulong Permissions { get; set; }        
    }
    

    I haven't tested this yet,

    But when created my table should prevent inserts that don't exist in the cmsContent or cmsMember table.

  • Sebastiaan Janssen 5060 posts 15522 karma points MVP admin hq
    Jul 18, 2015 @ 08:39
    Sebastiaan Janssen
    0

    Just as a note (I didn't read this properly, sorry it's the weekend ;) ) if you do change the schema of the Umbraco database (so if you add anything to our tables) then you will have problems upgrading to versions that have database changes. So think about that and please if you run into upgrade problems in the future and decide to report them then also let us know about schema changes you made to our tables. Thanks!

  • Ryios 122 posts 263 karma points
    Jul 18, 2015 @ 19:10
    Ryios
    0

    I reengineered this a tad.

    Instead of depending on node Id's, I depend on the unique ID now.

    I got to thinking about how courier is able to deploy content from one server to another. NodeId is autoincremented so Courier can't depend or move the content by node id, so it must be using the unique key.

    I followed the same practice because I want my table to be able to be migrated from one server to another as well.

    Now I still have the code that runs on the event to clean up my type, but there is no need for me to have a foreign key to the nodeId on umbracoNode anymore. And unique guids are unique, so I don't see a need for the FK there either.

    On top of that, members also have key's (guids) because they are actually based off a content type, as such each member has an entry in umbracoNode as well.

  • Ryan 24 posts 106 karma points
    Jul 11, 2017 @ 01:09
    Ryan
    0

    It's a bit late but this might help someone. This workaround is fine for me in an umbraco startup class:

            if (!db.TableExist("multiTenantedUsers"))
            {
                db.OpenSharedConnection();
                db.CreateTable<MultiTenantedUser>(false);
                var cmd = db.CreateCommand(db.Connection, @"
                    ALTER TABLE multiTenantedUsers WITH CHECK ADD
                        CONSTRAINT FK_MultiTenantedUser_UmbracoUser FOREIGN KEY (identityId)
                        REFERENCES umbracoUser(id),
                        CONSTRAINT FK_MultiTenantedUser_UmbracoNode FOREIGN KEY (orgNodeId)
                        REFERENCES umbracoNode(id)
                    ");
                cmd.ExecuteNonQueryWithRetry();
            }
    
Please Sign in or register to post replies

Write your reply to:

Draft