Staging custom page type tables

wrong account

Right account!

Best way is to never insert items directly into the Database, since Kentico has no way to track those changes. Instead use the Kentico API to dynamically create your documents and insert them. Here's 2 code snippets, first is just a normal non-workflow, the 2nd is if you have workflow turned on

// Creates a new instance of the Tree provider
TreeProvider tree = new TreeProvider(MembershipContext.AuthenticatedUser);

// Gets the current site's root "/" page, which will serve as the parent page
TreeNode parentPage = tree.SelectSingleNode(SiteContext.CurrentSiteName, "/", "en-us");

if (parentPage != null)
{
    // Creates a new page of the "CMS.MenuItem" page type
    TreeNode newPage = TreeNode.New(SystemDocumentTypes.MenuItem, tree);

    // Sets the properties of the new page
    newPage.DocumentName = "Articles";
    newPage.DocumentCulture = "en-us";
    newPage.SetValue("SomeCustomValue","etc");
    // Inserts the new page as a child of the parent page
    newPage.Insert(parentPage);
}

And with Workflow turned on

// Creates an instance of the Tree provider
TreeProvider tree = new TreeProvider(MembershipContext.AuthenticatedUser);
VersionManager manager = VersionManager.GetInstance(tree);

// Gets the published version of child pages stored under the "/Article/" path
// The pages are retrieved from the Dancing Goat site and in the "en-us" culture
var pages = tree.SelectNodes()
    .Path("/Articles/", PathTypeEnum.Children)
    .WhereLike("DocumentName", "Coffee%")
    .OnSite("DancingGoat")
    .Culture("en-us");

// Updates the "DocumentName" and "ArticleTitle" fields of each retrieved page
foreach (TreeNode page in pages)
{
manager.CheckOut(page);
    page.DocumentName = "Updated article name";
    page.SetValue("ArticleTitle", "Updated article title");

    // Updates the page in the database
    page.Update();
    manager.CheckIn(page, null, null);
    WorkflowManager workflowManager = WorkflowManager.GetInstance(tree);
        WorkflowInfo workflow = workflowManager.GetNodeWorkflow(page);
        manager.ApplyLatestVersion(page);
}

Thanks, Trevor!

Unfortunately changing the import functionality is not an option at this point. I wasn't the one that created it but I have to work with what I have. My options are either start running the import directly on live, or find a way to sync the tables. I was thinking maybe some kind of handler that runs a sql query to compare the tables after the documents that exist in the content tree finish staging.. seems kind of messy though.

If they are objects on the tree, you could try to run a complete sync, it's an expensive operation and takes some time depending on structure, and again the data may be there but the meta-data may not be for some of these things.

But that would be the next best thing, look at the staging module and run a full sync automatically or manually.

Hi Sandro,

AFAIk, as long as you are using Kentico API to create new content in your website it will get recorded in the Kentico staging queue and fron there you can use to sync data between two servers. This will make sure that your tables are in sync.

However, since you are using SQL queries and not Kentico API, there could be a case that you have not updated all possible XML schemas that is considered as a legitimate record change by kentico.

I faced a similar problem a few months back when I will doing an ETL and wanted to bring some products from pdf scrapping to Kentico CMS stored as page types.

Your best bet would be to use Kentico API to update record and then use staging to sync data between multiple servers.

Thank you. My two cents.

Cheers Chetan

Sandro,

Trevor is on the right path with this but if you can't do a complete sync then you need to understand the overall structure of a page. This will then help you to create a process to "fill in those blanks" when they come up. For instance in order for a page to be complete you need a record in 4 different tables:

• cms_tree
• cms_document
• cms_class
• <custom page type> i.e.: content_menuitem

Then you also have to think about if you're storing version history or applying workflow too. This adds another level of complexity to the query. Because no matter how much you update those 4 tables I mentioned, if you don't update the cms_versionhistory or cms_workflowhistory which is linked in the cms_document table you won't be updating or receiving the correct data.

So yes the API is the best method to perform these updates BUT if this is not possible, get a better understanding of the overall data schema and structure and attempt to correct what is wrong with a script or an API call.

Thanks guys, great answers. That is exactly what I would suggest for a Kentico project that has a functional architecture, but I'm afraid this one does not. I'm not allowed to explain the specifics, but providing an overview is not easy.. the imported XML documents are to documents in the content tree what options are for e-commerce products. There are multiple options that are exactly the same, except for the foreign key that links it to a document in the content tree. Only one of the documents in each set is added to the content tree so that it can be searchable, the others are added to the database table for use in a custom Web part (just a repeater-type web part).

Already two things wrong here:

All of the foreign keys for a set of documents could have been added to one field, in one of the documents. This way nothing would be inserted into the database without the API and there would only be one copy of an Option.

If #1 is not an option, the records could have been stored in a custom table and a) we could index them b) we could stage them.

The reason why not all documents are added to the tree is because 1) there are 1000s of them, 2) their only purpose is to show up in a custom Web part which uses SQL queries. When you select a document that has these options belonging to it, the Web part runs a SQL query on the table and uses the current document foreign key in the WHERE condition. We don't really care if they are not proper objects in Kentico because this is their only use. Except this approach is causing issues with deployments, and their budget is not big enough to restructure this from the ground up.

To make matters worse, the import script doesn't clean up after itself. If an option is removed from the XML file during the next import, the old record won't be removed from the database..

It is a nightmare, and that's why I'm here :)