How to change inheritance from ContentPartRecord to ContentPartVersionRecord

Question

OrchardCMS developers/users,

I have

public class MyContentPartRecord : ContentPartRecord
{ ... }

I want to change it to

public class MyContentPartRecord : ContentPartVersionRecord
{ ... }

In order to be able to create drafts for this part.

I add to Migraions

SchemaBuilder.AlterTable(
            "MyContentPartRecord",
            table =>
            table
                .AddColumn<int>("ContentItemRecord_id")
            );

I run the app and the result is on get a content item of MyContentPart it creates an empty published version.

In db for MyContentPartRecord table:

Id  [..fields..]    ContentItemRecord_Id
657 NULL,..         67
67  'MYDATA',...    NULL

How to create a valid draftable MyContentPart?

UPDATE. I've tried with no success to add:

SchemaBuilder.ExecuteSql(@"
    UPDATE       MyContentPartRecord 
    SET                ContentItemRecord_id = t2.ContentItemRecord_id 
    from MyContentPartRecord t1 inner join Orchard_Framework_ContentItemVersionRecord t2 on t1.id= t2.id
");

as it seems that orchard can't work with old records from MyContentPartRecord table as they don't have ContentItemRecord_id set.

Answer 1

Here's a more generic solution that can go in your migration.cs file based on @Artjom and @PiotrSzmyd's soltuion. This solution handles the fact that the automatically generated primary key may be named differently for each user of your module. Also the table name may be prefixed if a user has defined a global database prefix (eg. when using multi-tenant).

    // Manually add the column that is required for the part to be a ContentPartVersionRecord
    SchemaBuilder.AlterTable("MyCustomPartRecord", table => table.AddColumn<int>("ContentItemRecord_id"));

    // Get table name
    var tablePrefix = String.IsNullOrEmpty(_shellSettings.DataTablePrefix) ? "" : _shellSettings.DataTablePrefix + "_";
    var tableName = tablePrefix + "MyModule_MyCustomPartRecord";

    // Drop the primary key
    SchemaBuilder.ExecuteSql(string.Format(@"
        DECLARE @primaryKeyName NVARCHAR(MAX)

        SELECT @primaryKeyName = constraint_name
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        WHERE CONSTRAINT_TYPE = 'Primary Key' and TABLE_NAME = '{0}'                

        EXEC(N'ALTER TABLE {0} DROP CONSTRAINT ' + @primaryKeyName)
    ", tableName));

    // Migrate IDs to ContentItemRecord_id
    SchemaBuilder.ExecuteSql(string.Format(@"
        INSERT INTO {0} (Id, ContentItemRecord_id, Category_Id, ItemCode, Name, Description, DisplayOrder, Location, MaintenanceFrequency, MaintenanceFrequencyMultiplier, MaintenanceStartDate, Notes, IsEnabled)
        SELECT V.Id as Id, T.Id as ContentItemRecord_id, Category_Id, ItemCode, Name, Description, DisplayOrder, Location, MaintenanceFrequency, MaintenanceFrequencyMultiplier, MaintenanceStartDate, Notes, IsEnabled
        FROM {0} T
        LEFT OUTER JOIN
        {1}Orchard_Framework_ContentItemVersionRecord AS V ON V.ID in
            (select top(1) Id 
                from {1}Orchard_Framework_ContentItemVersionRecord 
                where ContentItemRecord_id = T.ID 
                order by latest desc, id desc)
    ", tableName, tablePrefix));

    // Remove old rows (no ContentItemRecord_id value)
    SchemaBuilder.ExecuteSql(string.Format(@"
        DELETE FROM {0}
        WHERE ContentItemRecord_id is NULL
    ", tableName));

    // Re-add the primary key
    SchemaBuilder.ExecuteSql(string.Format(@"
        ALTER TABLE {0}
        ADD CONSTRAINT PK_{0}_Id PRIMARY KEY (Id)
    ", tableName));

Answer 2

With the Piotr's help here is a solution. The following to be put in Migrations.cs:

SchemaBuilder.AlterTable(
                "MyContentPart",
                table =>
                table
                    .AddColumn<int>("ContentItemRecord_id")
                );

            SchemaBuilder.ExecuteSql(@"
ALTER TABLE MyModuleName_MyContentPart
DROP CONSTRAINT PK__MyModuleName_W__3214EC072C83793F
");
            SchemaBuilder.ExecuteSql(@"
INSERT INTO MyModuleName_MyContentPart
                         (Id, ContentItemRecord_id, Field1, Field2)
SELECT        t3.Id AS id, t2.Id AS ContentItemRecord_id, t2.Field1, t2Field2
FROM            MyModuleName_MyContentPart AS t2 LEFT OUTER JOIN
                         Orchard_Framework_ContentItemVersionRecord AS t3 ON t2.Id = t3.ContentItemRecord_id
WHERE        (t3.Latest = 1) AND (NOT (t3.Id IS NULL))
");
            SchemaBuilder.ExecuteSql(@"
DELETE FROM MyModuleName_MyContentPart
WHERE ContentItemRecord_id is NULL
");
            SchemaBuilder.ExecuteSql(@"
ALTER TABLE MyModuleName_MyContentPart
ADD CONSTRAINT PK_MyModuleName_MyContentPart_ID PRIMARY KEY (Id)
");

UPDATE.

The final solution:

SchemaBuilder.AlterTable(
                "MyContentPart",
                table =>
                table
                    .AddColumn<int>("ContentItemRecord_id")
                );

            SchemaBuilder.ExecuteSql(@"
ALTER TABLE MyModuleName_MyContentPart
DROP CONSTRAINT PK__MyModule_W__3214EC072C83793F
");
            SchemaBuilder.ExecuteSql(@"
INSERT INTO MyModuleName_MyContentPart
 (Id, ContentItemRecord_id, Field1)
SELECT V.Id as Id
       ,T.Id as ContentItemRecord_id 
      ,Field1
FROM  [MyModuleName_MyContentPart] T
    LEFT OUTER JOIN
        Orchard_Framework_ContentItemVersionRecord AS V ON V.ID in
            (select top(1) Id 
                from Orchard_Framework_ContentItemVersionRecord 
                where ContentItemRecord_id = T.ID 
                order by latest desc, id desc)
");
            SchemaBuilder.ExecuteSql(@"
DELETE FROM MyModuleName_MyContentPart
WHERE ContentItemRecord_id is NULL
");
            SchemaBuilder.ExecuteSql(@"
ALTER TABLE MyModuleName_MyContentPart
ADD CONSTRAINT PK_MyModuleName_MyContentPart_ID PRIMARY KEY (Id)
");
        }

Answer 3

You won't be able to do that without a manual SQL script.

The Id means a different thing for those two:

• for ContentPartRecord it's a foreign key to an Id of a ContentItemRecord
• for ContentPartVersionRecord it's a foreign key to an Id of a ContentItemVersionRecord

So after adding a new column ContentItemRecord_id you need to

• first, copy existing data from Id column to ContentItemRecord_id and then
• fill Id column with proper ids of the Latest version of each of those items. Version records are kept in Orchard_Framework_ContentItemVersionRecord table.