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.
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!
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.
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...
I haven't tested this yet,
But when created my table should prevent inserts that don't exist in the cmsContent or cmsMember table.
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!
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.
It's a bit late but this might help someone. This workaround is fine for me in an umbraco startup class:
is working on a reply...