CODEWITHSUNDEEP
sensenet
Anas Tina
Anas Tina

Reputation: 339

Mapping field purpose in SchemaPropertyTypes table

I am trying Sense/Net Community edition features.

I defined and installed content type called "Vacation Request" successfully.

I want to know what is the purpose of "mapping" field in table: SchemaPropertyTypes

Many thanks,

Upvotes: 0

Views: 57

Answers (1)

Miklós Tóth
Miklós Tóth

Reputation: 1511

I really, really hope you are asking this only out of curiosity, and not because you want to change something manually in the db - because it is not recommended :). Please always access the content repository through the API, do not query or modify the db directly.

Property types and values

Simple property values (like int or short text values) are stored in the FlatProperties table. This is a fixed-with table, containing a predefined number of columns dedicated to different types (e.g. x pieces of string columns, y pieces of int columns - see column names in the table).

Property definitions are stored in the SchemaPropertyTypes table, as you have found out.

The zero-based Mapping field in the SchemaPropertyTypes table defines the column index in the FlatProperties table for a particular property. E.g. a value of a string property with mapping 6 will be stored in the FlatProperties table's 'nvarchar_7' column (note the index is shifted by one, because the column name index is one-based).

If you take a look at the PropertyInfoView view (not table), it may help clarifying this: the last column of the view is a computed column that displays the column name that you can look up in the flat properties table.

(there are other useful SQL views there that display data in a more readable way)

Property 'overflow'

It is possible to register more properties of the same type (e.g. int) than can fit in one row in the FlatProperties table. Solution: Sense/Net stores these nodes in multiple rows - this is why there is a Page column there. Although MS SQL Server supports a huge number of columns for some time, this design has been kept for compatibility reasons.

This is why you see mapping values in PropertyInfoView like 249 with column name nvarchar_10: the value is stored on page 3, which means that content occupies 3 records in the FlatProperties table.

'Other' property types

You may have noticed that in case of reference or long text properties there is no mapping. This is because we do not store them in the FlatProperties table, they have their own tables like ReferenceProperties or TextPropertiesNText.

Upvotes: 1

Related Questions