Database structure

From Sense/Net Wiki
Jump to: navigation, search
  •  
  •  
  •  
  •  
  • 100%
  • 6.3
  • Enterprise
  • Community
  • Planned

Overview

Database structure

Sense/Net ECM has a feature-rich Content Repository that requires a database that is able to support a dynamic Content Type system that can be changed and extended even while the site is up and running. In this article developers and system operators can learn about the architecture of the database in Sense/Net ECM and the purpose of the tables. If you are looking for information about specific content or fields, we encourage you to take a look at our built-in SQL views below as they offer a user-friendly snapshot of the data stored in the tables.

Details

Sense/Net ECM database layer is designed using a data provider that is currently built on SQL Server. In the future it will be possible to replace it with a db provider for other database servers (even for NOSQL solutions). In this article we list and explain all the tables and views that can be found in the system currently.

Please modify the data in the database only if you know what you are doing. Making manual modifications in these tables may leave the database in an inconsistent state and it is unadvisable. Please edit the Content Repository only through our API.

Tables

In this section you'll find the description of our database tables grouped by feature.

If you are looking for information about specific content or property, please consider using one of the built-in SQL views (see them below) because they display the data from these tables in a more human readable format.

Content Repository schema

The tables in this section describe the dynamic Content Type system in the Content Repository. You can see the types, their properties and SQL data types here.

  • SchemaDataTypes: available SQL datatypes for properties
  • SchemaModification: contains only a single row, the last modification time of the schema
  • SchemaPropertySets: content types, content list types and their content handlers
  • SchemaPropertySetsPropertyTypes: relation table between property types and content types
  • SchemaPropertySetTypes: helper table for different prop set types
  • SchemaPropertyTypes: all registered properties (mainly coming from field definitions from CTDs)
Content Repository schema

Nodes, versions and properties

In this section we list all the tables that store content items (Nodes) and their metadata in multiple property tables.

  • Nodes: this is the main table that contains records for all content items stored in the system. The NodeId key column contains the value that you see as content id in upper layers of the system.
  • Versions: the records in this table represent all versions for content items (there may be multiple rows here for a single node, for example if versioning is switched ON in a document library).
  • FlatProperties: this table contains almost all the metadata for content items in a fixed number of columns created for simple data types (e.g. string, int or datetime columns). Be aware that there may be multiple rows for a single version in this table, marked by the Page column. This is necessary because of the dynamic content type system that registers physical database columns to fields, and the number of columns in the db is fixed. In case the columns for a specific type (e.g. string or datetime) exceeds the expected index for a version, we continue storing metadata on the next 'page'.
  • ReferenceProperties: connector table for reference field values (that connects a version to a referred node for a particular property)
  • BinaryProperties: metadata table for all binary values (size, content type, etc.)
    • Files: real binary data in case of default behavior, connected to the BinaryProperties table above. It is a separate table because multiple binary rows may reference the same File record (so that we do not have to copy the full binary in case of a checkout opration, which duplicates the binary row but references the same file row)
  • TextPropertiesNVarchar: LongText field values (bigger than 450 chars stored in the FlatProperties table, but smaller than 4000 chars) go into this separate table.
  • TextPropertiesNText: super-long LongText values (longer than 4000 chars) go here.
Nodes and versions

Indexing

  • IndexBackup: in case of small installations we store the compressed index folder in this table when the backup feature is used.
  • IndexBackup2: helper table for the backup feature.
  • IndexingActivity: a list of indexing activities happened in the system. For example when you upload or modify a document (or any content), a row will appear here stating the index operation that happened. Do not modify the contents of this table manually!
Indexing

Logging and messaging

  • LogEntries: this is the table where audit events are saved by default. You'll even find the changed fields and values in the FormattedMessage column, along with the content id and path.
  • LogCategoriesEntries: helper table for logging.
  • Messaging: these tables store information related to the Notification feature.
    • Messaging.Events
    • Messaging.LastProcessTime
    • Messaging.Messages
    • Messaging.Subscriptions
    • Messaging.Synchronization
  • JournalItems: records for the Journal feature.
Logging and messaging

Other tables

  • TreeLocks: contains temporary records when a long-running operation (e.g. rename or move) locks a subtree in the repository.
  • Packages: contains a list of installed packages. The SnAdmin tool adds records to this table and you can view versioning information about the portal on the user interface (on the Root Console).
  • WorkflowNotification: this table contains information for the Workflow feature (for example which workflow should be notified and revived when a certain content has changed).

Security

These tables store security information and are maintained by the Security Component. If you want to review permission information, please use the SQL views below.

  • EFEntities: list of entities. This table should contain the same ids and structure (as parent-child relationships) as the Nodes table.
  • EFEntries: all permission entries in the system.
  • EFMemberships: user-group relationships. A member can also be a group.
  • EFMessages: Security activities (that describe atomic changed in the security db) are stored in this table that are needed by the security component to work in an NLB environment.
Security

Workflow

The tables in the System.Activities.DurableInstancing schema are maintained by the Windows Workflow Foundation that we integrated into Sense/Net ECM. Please do not modify them manually.

Workflow

Views

The built-in views can help when you search for specific content because they take care of the joins and aliases and make troubleshooting a lot easier.

  • NodeInfoView: lists all versions for content items with human readable content type names and versioning/approving information. Versions of the same content appear as separate lines in this view.
  • PermissionInfoView: lists all permission settings with human readable permission names and also a composed value of those settings in a single column.
  • PropertyInfoView: lists property mappings for Content Type fields, Content List fields and strongly typed properties. This is useful when you are looking for a column in the database (e.g. in which column in the FlatProperties table a particular field value is stored). You will find content list fields by their generated internal name (e.g. #String_1) instead of the name visible on the UI. Please be aware of the Page column: it determines the row for a single version of a content (a version may be stored in multiple rows, see details above in the section for the FlatProperties table).
  • PropertySetsInfoView: lists all registered properties and their content types and data types in the system.
  • ReferencesInfoView: lists all reference field connections in the system with human readable names and paths for source and target content
  • SysSearchView: lists all content with their last major and minor versions (this is why it is possible to see two rows here for a single node).
  • SysSearchWithFlatsView: similar to SysSearchView but it also contains the columns from the FlatProperties table so that you can find particular field values. Usually we use this in conjuction with the PropertyInfoView (see above).


Related links

References

There are no external references for this article.