Inspecting raw database data in Xperience by Kentico

tl;dr

Below are just links to the SQL code snippets, make sure to pin and make use of them!

• Basic Content Hub item query
• Custom content item fields query
• Reusable field schema content item query
• Content Hub assets query
• Content Hub with Taxonomy query
• Web Page query

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
• 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 <namespace>_<customTypeName>. 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!