Author: essmann
Publication Date: 1/13/2011 9:21

In case anybody is wondering about the database structure FirstSpirit creates in the SQL-Tablespace for FirstSpirit controlled datasources, here is a rough description. 

Important Notes:

• This post is only for for information purposes. You should never change the datasets in a FirstSpirit controlled datasource via SQL-statements or other interfaces than FirstSpirit Clients.
• The post describes the actual structure and semantic. It may change with any new version of FirstSpirit.
• In general the FirstSpirit controlled datasources in the content store should be treated as a black box.

Timestamps:

timestamps are in unix time

minimum value = 0

maximum value = 9223372036854775807 (MAX_LONG)

FirstSpirit Columns:

FS_ID - FirstSpirit internal ID set by FirstSpirit according to the actual Transactioncounter Value (Table TRANSACTION_COUNTER, Row COUNTER). The Transaction counter (see below) is incremented afterwards.   

FS_RELEASE_TO - Timestamp until this dataset was the released version. 0 -> never released, MAX_LONG -> actual released version

FS_VALID_FROM - Timestamp from when this version was the current version.

FS_VALID_TO - Timestamp until when this version was the current version. MAX_LONG -> current version

RELEASED_BY - ID of the user who released this version

CHANGED_BY - ID of the user who changed/saved this version

WF_ID - if an workflow was started on this dataset, the ID of the worklow object

WF_COL - if an workflow was started on this dataset, the color of the actual worklow state is saved here. null, else.

Transaction Counter

The transaction counter is a system table managing the used/free FS_IDs. The persistent value of the transaction counter value increments by 64. So you will find only values which are a multiple of 64 within the TRANSACTION_COUNTER table.

Hint: The table TRANSACTION_COUNTER must have exact one entry. If you have more than one value by accident, contact the FirstSpirit helpdesk to fix this.

Foreign Key Relations

1:1

Each adjacent table gets a row named according to the table name containing the ID of the foreign dataset. The table name may be shortened due to database restrictions for the length of row names.

row name default scheme: <shortened table name>_FS_ID. This name can be changed when creating the FK.

1:N

The "N"-table stores the ID of the foreign dataset in the "1"-table in an special row. The table names may be shortened due to database restrictions for the length of row names.

row name scheme: <shortened table name>_FS_ID This name can be changed when creating the FK.

N:M

For any associated dataset comination a dataset is stored in a hidden table. The table is named in the following way:

RT_<shortened name table1>_<shortened name table2>_<first letter of tabel 1>List. This name can be changed when creating the FK. Within the table both associated datasets are written in a row named after the tabel names.

Tags: datasources, datenbank, dokumentation, firstspirit