Version 9 - Database Changes

I am in the middle of an upgrade and I have 412 Custom Page Types. Everything was going along swimmingly until I noticed all of my joined views are gone and I cannot import them since they will not work. Upon reviewing the Breaking changes I See "All page related views and the View_COM_SKU views were removed with the exception of View_CMS_Tree_Joined, which is used as the base for querying of page data. " So basically all of my custom page types won't work until I can manually go in and recreate views. I would of thought that maybe a tool for something so drastic would of parsed through everything and made the corrections to my custom page type views and then left them in the upgraded database. Not sure how cool this is at the moment. I guess I will have to write my own parsing and script to hopefully modify my current views and then create them in the updated database.

Yes many of the default views were removed which helped with performance of the database. Before going in and recreating them, I'd suggest looking into the documentation with version 9 and seeing what the replacements are so you won't have additional work now and later on.

The code upgrade tool is worthless when it comes to the views and such. I ran it for my custom controls and it identified two of them which I resolved. As you know Brenden, I have over 1 million pages in my system. These are spread across 420 or so custom page types. I am half way through fixing what no longer works. It is more work than I intended at the moment.

I understand you have a lot of pages and page types. What I'm stating is you might look into a supported version or change in order to get what you want accomplished faster and not cause problems later on. If you're creating views in the database directly, there's a good chance your're going to end up creating a breaking change to your instance which will not be supported. I'd suggest contacting Kentico directly for their recommendation.

Yes, I understand you're almost half way through fixing it but it was documented and with a site that has that much content, I wouldn't have upgraded it do a vanilla major version of any CMS. I would have waited until a few hotfixes were out, ensure you have all issues documented with the upgrade and your instance planned for upgrade and ensure you have a good testing plan in place.

For future reference here are the release notes for Kentico 9. Check out the database section, it has all the changes to the database as well as the views you're referencing. The very first bullet point states "All page related views and the View_COM_SKU views were removed with the exception of View_CMS_Tree_Joined, which is used as the base for querying of page data".

A solution might be to create a custom query in the page type and include the page type class and FK value like below. Then use the ObjectQuery to call it and pass your values to it. I do this will all my queries, even queries to external sources. This way the can be modified within the UI and the database remains untouched.

I would just like to point out that custom Page types are working normally after upgrade, what we changed was that we removed page related views as was already mentioned. This is one of the reasons why we encourage our customers to use DocumentQuery API – you don’t have to worry about changes in database architecture. Of course if you have written your custom queries using these views, they will not work after upgrade.

There shouldn’t be any performance impact either, since Document query can be parametrized to retrieve just the data you really need. You don’t have to work with TreeNode either, you can work with results as a DataSet data type.

Anyway, we are not exactly sure what your setup is and how you created and used your joined views. If you could provide us with some more details, we could suggest how to remedy the problem and how to avoid situations like these in the future. You can shoot us an email to support@kentico.com if you have any questions or more details.

The upgrade was completed in the middle of last night and everything is working now correctly. I do appreciate the answers. I have a lot of grids that the end user can filter and query data in. Some of the grids have 50,000 + records in them, and they work extremely fast. The website is more of a web application. I use Devexpress controls and grid liberally through out. I also create a lot of grids on the fly, so they were running against the page type joined views, which was convenient and extremely fast. Document query API is not a good candidate for this scenario. I have written a mild abstraction layer just for my grids now. I need the grids to populate fast, 782ms for 50,000 records into a paging grid is what I am getting now and is fine. Additionally, I ported the whole site onto Azure yesterday which is giving me faster response times as well. Version 9, loads a lot faster as well in general. Thanks for the input.