tl;dr
Below are just links to the SQL code snippets, make sure to pin and make use of them!
Why would I write SQL queries again?
It's been over a year since Kentico delivered a massive content structure modernization Refresh. The underlying database schema underwent significant changes to support the rapid growth of the Xperience by Kentico product.
In their daily tasks, developers don't usually need to remember the structure of the database tables and how they relate to each other. Kentico provides robust and flexible APIs that make it easy to query the data they need: Content Hub items, Web Pages, Objects, and Database Tables. But what if you execute the query, and the data returned is not what you expect? And why may this happen?
Occasionally, I find myself writing the same SQL queries to check what is stored in the database vs what I see (or don't see?) on the screen. Kentico provides a great documentation article explaining what information is stored in which database table.
This article is just a cheat sheet for those of you, who are about to:
- inspect what's going on with the imported data by Xperience by
Kentico Migration Toolkit
(https://github.com/Kentico/xperience-by-kentico-migration-toolkit)
- resolve CI/CD issues when a member of your team forgets to
commit some of the XML files
- investigate a loss of data after the change of content model
- checking the data after the update, and on many other occasions
Content Hub item properties
Let's start with easy things, this is basic information about the content hub item - a coffee product "El Salvador Finca San Jose" from the Dancing Goat website:
Here is the summary of the tables we need to join:
- CMS_ContentItem - basic item global info, one record across all languages
- CMS_ContentItemCommonData - basic item language-specific info, one record per language, also contains all reusable field schema
fields from all content types
- CMS_ContentItemLanguageMetadata - published and scheduled status, created/modified by who and when
- CMS_ContentLanguage - all languages in the system
- CMS_Class - all content and object types registered in the system (both Kentico and your custom ones)
Notice that I'm using LEFT joins here, and it's for a reason. This will help you identify missing or orphaned records if there were any. Missing or orphaned records may appear in your database due to incomplete data migration or CI/CD restore with some files missing.
Custom content type fields
Moving on, let's add our custom content type fields to the query:
Each custom content type has a dedicated SQL table to store custom field data, usually named _. We need to join it with the following condition: ContentItemDataCommonDataID foreign key, and filter out our specific content type 'DancingGoat.Coffee':
Reusable field schemas
You will immediately notice that our last query returns fewer fields than you can see in the user interface. This is because the Coffee content type has some of its fields inherited from Product fields reusable field schema:
These fields need to be queried from CMS_ContentItemCommonData table we joined earlier:
Content hub assets (images)
The next thing you will notice is how Xperience by Kentico stores references to images (or any other assets) stored in Content Hub. Documentation guides recommend storing images in the Content Hub, and Dancing Goat follows this guide.
The previous query will return the following result for the ProductFieldsImage field:
[{"Identifier":"279bb55f-81e3-4d93-9cff-8372dc34cdc5"}]
That is a unique identifier of our image - yet another item from the Content Hub of DancingGoat.Image type, and with the query below we can get some useful metadata information, and can even locate this asset on disk when we need to inspect the binary:
Taxonomy
Other fields from the previous queries where you will find unique identifiers are CoffeeProcessing and CoffeeTastes, these are Taxonomy fields, and they store a list of TagGUID records from CMS_Tag table:
This way we can join this data if needed:
Web Pages
And finally, web pages from the Web channel. In Xperience by Kentico every web page is a little bit of content item.
We just need to join a couple more tables:
- CMS_WebPageItem - basic CMS tree global info, one record across all languages
- CMS_WebPageUrlPath - URLs table, one record for each language
Other helpful tools
The queries above are beneficial while debugging data issues. But what if you need to check what SQL queries Xperience generates when you call query APIs? In this case, integration with MiniProfiler would be very helpful! Check out this nuget package by Sean G. Wright. I think this is one of the must-have packages you should install for every project!
Don't be afraid of making your hands dirty again by writing some SQL queries to identify data issues within your Xperience by Kentico solution. Hopefully, these little code snippets together with Kentico's detailed documentation will give you some good boosting when you start investigating things!
Author: Dmitry Bastron, Solution Architect at ByteMinds / Kentico MVP