SQLTables - sorengranfeldt/sqlma GitHub Wiki

The MA supports two tables, a single-value table and an optional multi-value table. You can use most any type of columns for each table.

The different object types can share the columns and you can exclude some columns from certain object types by specifying this in the schema XML.

NOTE - the name of data columns must be unique in your tables for your object types, i.e. you cannot have a column named 'accountname' in both the single-value table and the multi-value table and use that with the same object type.

For increased performance, it is recommended to index the key, backreference and watermark columns in both tables.

Single-value table

This table is mandatory and the table is required to have one column that has unique values and functions as the primary key for the records.

Purpose	Type	Data type	Recommended name	Description
Primary key	Mandatory	any	{"_id"}	A column that has a unique value for every record. Typically, this column is of type string, uniqueidentifier or int/bigint.
Object class	Optional	string	{"_objectclass"}	A column that holds the type of object for the record/row.
Watermark	Optional	rowversion or datetime	{"_watermark"}	If delta import is to be supported, a column of either type rowversion or datetime must exist and be updated on changes to the record. The column type must be either rowversion or datetime, no other type is currently supported.
IsDeleted	Optional	bit	{"_isdeleted"}	If soft-delete is supported, a column of type bit must exist. If a record is soft-deleted, this value of this column is set to true (1) and if the record is not deleted, the value is either NULL or false (0).

Besides the columns specified above, you can have any number of column with information that your records need. You can use all data types for the columns and the MA will convert the different data types to the appropriate FIM/MIM datatype. Using the schema XML, you can override the default data types suggested by the MA.

Multi-value table

This is a optional table and has any number of columns that are marked as multi-valued. The table must have a column that has the same type as the primary key column in the single-value table and this column is used as a back reference to an object in the single-value table.

Purpose	Type	Data type	Recommended name	Description
Backreference	Mandatory	same as primary key in single-value table	{"_refid"}	Hold the primary key of the record from the single-value table that has this multivalue record.
Watermark	Optional	Same as corresponding column from single-value table	{"_watermark"}	If delta import is to be supported, a column of either type rowversion or datetime must exist and be updated on changes to the record. The column type must be either rowversion or datetime, no other type is currently supported.
IsDeleted	Optional	bit	MUST have same name as column in single-value table	If soft-delete is supported, a column of type bit must exist. If a record is soft-deleted, this value of this column is set to true (1) and if the record is not deleted, the value is either NULL or false (0).

Besides the columns specified above, you can have any number of column with information that your records need. You can use all data types for the columns and the MA will convert the different data types to the appropriate FIM/MIM datatype. Using the schema XML, you can override the default data types suggested by the MA.

Sample tables

Below are scripts to create a sample single-value and multi-value table with all relevant attributes.

CREATE TABLE [dbo](dbo).[object](object)(
	[_id](_id) [uniqueidentifier](uniqueidentifier) NOT NULL CONSTRAINT [DF_objects__id](DF_objects__id)  DEFAULT (newid()),
	[_objectclass](_objectclass) [nvarchar](nvarchar)(50) NOT NULL,
	[_watermark](_watermark) [timestamp](timestamp) NOT NULL,
	[_isdeleted](_isdeleted) [bit](bit) NULL,
	[accountname](accountname) [nvarchar](nvarchar)(50) NULL,
	[firstname](firstname) [nvarchar](nvarchar)(50) NULL,
	[lastname](lastname) [nvarchar](nvarchar)(50) NULL,
	[displayname](displayname) [nvarchar](nvarchar)(250) NULL,
	[managerid](managerid) [uniqueidentifier](uniqueidentifier) NULL,
	[employeenumber](employeenumber) [int](int) NULL,
	[managedby](managedby) [uniqueidentifier](uniqueidentifier) NULL,
 CONSTRAINT [PK_object](PK_object) PRIMARY KEY CLUSTERED 
(
	[_id](_id) ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY](PRIMARY)
) ON [PRIMARY](PRIMARY)

CREATE TABLE [dbo](dbo).[objectmv](objectmv)(
	[_watermark](_watermark) [timestamp](timestamp) NOT NULL,
	[_isdeleted](_isdeleted) [bit](bit) NULL,
	[_backrefid](_backrefid) [uniqueidentifier](uniqueidentifier) NOT NULL,
	[member](member) [uniqueidentifier](uniqueidentifier) NULL,
	[proxyaddresses](proxyaddresses) [nvarchar](nvarchar)(250) NULL,
	[departmentname](departmentname) [nvarchar](nvarchar)(250) NULL,
	[othertelephone](othertelephone) [nvarchar](nvarchar)(50) NULL
) ON [PRIMARY](PRIMARY)