Ms Sql краулер

Примечание

В статье приведен пример краулера, характеристики которого настраиваются индивидуально под каждого заказчика. Краулер поставляется только по запросу и не идет по умолчанию в стандартной сборке.

Краулер не поддерживает автоматическую миграцию модели при запуске сканирования. Перед первым запуском необходимо вручную импортировать модель из библиотеки.

Модель совместима с моделью DIS краулеров. Если на стенде уже используется модель DIS, импорт не требуется.

Извлекаемые объекты

В результате сканирования из источника извлекаются метаданные следующих типов объектов:

• Таблица

• Синоним

• Представление

• Триггер

• Функция

• Процедура

• Ключ

• Материализованное представление

Настройка разрешений для учетной записи

Для учетной записи пользователя базы данных MS SQL необходимо иметь право VIEW ANY DEFINITION.

Загрузка файлов и библиотек

Загрузка дополнительных файлов и библиотек не требуется.

Параметры подключения к источнику

С сервера, на котором развернут сканер, а также с сервера каталога данных должны быть открыты порты (по умолчанию порт базы данных 1433) до сервера базы данных сканируемого источника. IP адрес сервера и порт базы данных источника указываются в параметрах подключения (Таблица 1).

Таблица 1 – Параметры краулера Ms Sql

Системное имя (Отображаемое имя)	Формат	Пояснение/Примечание
resourceName (Наименование)	Строка	На основе значения данного поля формируется префикс external_id ( формат: <resourceName>://)
database (База данных)	Строка	Имя базы данных
host (Хост)	Строка	Хост базы данных
port (Порт)	Строка	Порт базы данных
ntlmAuthentication (Использовать доменную УЗ)	Флаг	Использовать для подключения доменную учетную запись Active Directory
domainName (Домен)	Строка	Название домена для подключения через учетную запись Active Directory
username (Имя пользователя)	Строка	Имя пользователя
password (Пароль)	Строка	Пароль пользователя
schema (Схема)	Строка	Схема, sql выражение. Зарезервированные слово включая символы "<schema>". Обычное sql условие (where). Данное условие добавляется к запросу: SELECT * from sys.schemas <выражение> Примеры выражений: - Включить список схем: <schema> in ('SCHEMA1', 'SCHEMA2') - Исключить список схем: <schema> not in ('SCHEMA3') - Похоже: <schema> like '%%SYS' or  <schema> like 'SYS%%' or <schema> like '%%SYS%%' - Несколько условий: <schema> in ('USER','TEST') or <schema> like 'SYS%%' and <schema> != 'SYSTEMD'
table (Таблицы и представление)	Строка	Sql Выражение. Зарезервированное слово включая символы "<table>" для таблиц и представлений. Синтаксис такой же как и для параметра "Схема". + Если load_system_objects = false, то добавляется условие ORACLE_MAINTAINED = 'N'
load_system_objects (Загружать системные объекты)	Чекбокс	Загружать системные объекты (системные схемы и их объекты).

Описания активов

• Системное имя актива и параметра складывается из префикса com_infa_ldm_relational_ и имен актива из таблицы активов ниже, например Table. Системное имя актива Table = com_infa_ldm_relational_Table.

• Системное имя актива констрейнтов складывается из префикса com_infa_ldm_constraint и имени актива из таблицы активов ниже, например ForeignKey. Системное имя актива ForeignKey = com_infa_ldm_constraint_ForeignKey.

• Параметры core_description и core_name не имеют префикс. Данные параметры присутствуют в каждом активе.

Актив Database

• Параметры в БД:

  • core_description

  • core_name: MsSql

• Row Mapper: MsSqlDatabaseRowMapper

• Sql выражение

SELECT @@VERSION

Актив Schema

• Параметры в БД:

  • core_description

  • core_name: TABLE_SCHEMA

• Row Mapper: MsSqlSchemaRowMapper

• Sql выражение:

SELECT name AS TABLE_SCHEMA from sys.schemas %s

где %1 - Выражение из параметра "Scanner Schema"

Актив Table

• Параметры в БД:

  • core_description

  • core_name: TABLE_NAME

  • com_infa_ldm_relational_NativeType: Base Table

• Row Mapper: MsSqlTableRowMapper

• Sql выражение:

WITH tables AS (" +
        "SELECT " +
        "TABLE_SCHEMA, " +
        "TABLE_NAME " +
        "FROM INFORMATION_SCHEMA.TABLES " +
        "WHERE TABLE_SCHEMA IN (%s) " +
        ")"

        SELECT * FROM tables %s

где %1 - Выражение из параметра "Scanner Schema"; %2 - Выражение из параметра "Scanner Table"

Актив View

• Параметры в БД:

  • core_description

  • core_name: TABLE_NAME

  • com_infa_ldm_relational_DDL: View DDL

• Row Mapper: MsSqlViewRowMapper

• Sql выражение:

WITH views AS (" +
        "SELECT " +
        "TABLE_SCHEMA, " +
        "TABLE_NAME " +
        "FROM INFORMATION_SCHEMA.VIEWS " +
        "WHERE TABLE_SCHEMA IN (%s) " +
        ")"

        SELECT * FROM views %s

SELECT * FROM views %2

где %1 - Выражение из параметра "Scanner Schema"; %2 - Выражение из параметра "Scanner Table"

Актив Column

• Параметры в БД:

  • core_description

  • core_name: COLUMN_NAME

  • Datatype: DATA_TYPE

  • Nullable: IS_NULLABLE

  • DatatypeLength:

  • CHARACTER_MAXIMUM_LENGTH

  • DatatypeScale: NUMERIC_SCALE

  • DefaultValue: COLUMN_DEFAULT

  • Position: ORDINAL_POSITION

• Row Mapper: MsSqlTableColumnRowMapper

• Sql выражение:

"WITH table_cols AS (" +
        "SELECT " +
        "TABLE_SCHEMA, " +
        "TABLE_NAME, " +
        "COLUMN_NAME, " +
        "ORDINAL_POSITION, " +
        "COLUMN_DEFAULT, " +
        "CHARACTER_MAXIMUM_LENGTH, " +
        "NUMERIC_SCALE, " +
        "IS_NULLABLE, " +
        "DATA_TYPE " +
        "FROM INFORMATION_SCHEMA.COLUMNS " +
        "WHERE TABLE_NAME IN
        (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_TYPE = 'BASE TABLE' ) " +
        "AND TABLE_SCHEMA in (%s) )";
        SELECT * FROM table_cols %s

где %1 - Выражение из параметра "Scanner Schema"; %2 - Выражение из параметра "Scanner Table"

Актив ViewColumn

• Параметры в БД:

  • core_description

  • core_name: COLUMN_NAME

  • Datatype: DATA_TYPE

  • Nullable: IS_NULLABLE

  • DatatypeLength:

  • CHARACTER_MAXIMUM_LENGTH

  • DatatypeScale: NUMERIC_SCALE

  • DefaultValue: COLUMN_DEFAULT

  • Position: ORDINAL_POSITION

• Row Mapper: MsSqlViewColumnRowMapper

• Sql выражение:

WITH view_cols AS (" +
        "SELECT " +
        "TABLE_SCHEMA, " +
        "TABLE_NAME, " +
        "COLUMN_NAME, " +
        "ORDINAL_POSITION, " +
        "COLUMN_DEFAULT, " +
        "CHARACTER_MAXIMUM_LENGTH, " +
        "NUMERIC_SCALE, " +
        "IS_NULLABLE, " +
        "DATA_TYPE " +
        "FROM INFORMATION_SCHEMA.COLUMNS " +
        "WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS ) " +
        "AND TABLE_SCHEMA IN (%s) " +
        ")";

        SELECT * FROM view_cols %s

где %1 - Выражение из параметра "Scanner Schema"; %2 - Выражение из параметра "Scanner Table"

Активы PrimaryKey, ForeignKey, UniqueConstraint, CheckConstraint

• Параметры в БД:

  • core_description

  • core_name: constraint_name

  • com_infa_ldm_constraint_origin: check constraint expression

• Row Mapper: MsSqlConstraintsRowMapper

• Sql выражение:

WITH constraints as ( " +
        "SELECT" +
        "    CONSTRAINT_SCHEMA, " +
        "    CONSTRAINT_TYPE, " +
        "    CONSTRAINT_NAME, " +
        "    TABLE_SCHEMA, " +
        "    TABLE_NAME " +
        "FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS " +
        "WHERE CONSTRAINT_TYPE in ('PRIMARY KEY', 'FOREIGN KEY', 'CHECK', 'UNIQUE') " +
        "   AND CONSTRAINT_SCHEMA in ( %s ) " +
        "   AND TABLE_NAME in ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES %s )" +
        ")"

SELECT * FROM constraints ORDER BY CONSTRAINT_SCHEMA, CONSTRAINT_TYPE, CONSTRAINT_NAME

где %1 - Выражение из параметра "Scanner Schema"; %2 - Выражение из параметра "Scanner Table"

Актив Function

• Параметры в БД:

  • core_description

  • core_name: object_name

  • ddl : ROUTINE_DEFINITION

• Row Mapper: MsSqlObjectsFunctionRowMapper

• Sql выражение:

"WITH objects AS (" +
        "SELECT " +
        "ROUTINE_SCHEMA, " +
        "ROUTINE_NAME, " +
        "ROUTINE_DEFINITION " +
        "FROM INFORMATION_SCHEMA.ROUTINES " +
        "WHERE ROUTINE_TYPE = 'FUNCTION' " +
        "AND ROUTINE_SCHEMA IN (%s) " +
        ")";
        SELECT * FROM objects

где %1 - Выражение из параметра "Scanner Schema"

Актив StoredProcedure

• Параметры в БД:

  • core_description

  • core_name: object_name

  • ddl : ROUTINE_DEFINITION

• Row Mapper: MsSqlObjectsProcedureRowMapper

• Sql выражение:

WITH objects AS (" +
        "SELECT " +
        "ROUTINE_SCHEMA, " +
        "ROUTINE_NAME, " +
        "ROUTINE_DEFINITION " +
        "FROM INFORMATION_SCHEMA.ROUTINES " +
        "WHERE ROUTINE_TYPE = 'PROCEDURE' " +
        "AND ROUTINE_SCHEMA IN (%s) " +
        ")"
         SELECT * FROM objects

где %1 - Выражение из параметра "Scanner Schema"

Актив Synonym

• Параметры в БД:

  • core_description

  • core_name: object_name

  • ddl: ROUTINE_DEFINITION

• Row Mapper: MsSqlObjectsSynonymRowMapper

• Sql выражение:

"WITH synonyms AS (" +
        "SELECT " +
        "s.name AS SYNONYM_NAME, " +
        "sc.name AS TABLE_SCHEMA, " +
        "'SYNONYM' AS object_type, " +
        "s.base_object_name AS BASE_OBJECT_NAME, " +
        "s.base_object_name AS ROUTINE_DEFINITION " +
        "FROM " +
        "sys.synonyms s " +
        "JOIN " +
        "sys.schemas sc ON s.schema_id = sc.schema_id " +
        "WHERE " +
        "sc.name IN (%s) " +
        ") ";
         SELECT * FROM synonyms

где %1 - Выражение из параметра "Scanner Schema"

Актив Trigger

• Параметры в БД:

  • core_description

  • core_name: object_name

  • ddl: ROUTINE_DEFINITION

• Row Mapper: MsSqlObjectsTriggerRowMapper

• Sql выражение:

"WITH triggers AS ( " +
        "SELECT " +
        "s.name AS TABLE_SCHEMA, " +
        "t.name AS trigger_name, " +
        "'TRIGGER' AS object_type, " +
        "OBJECT_DEFINITION(t.object_id) AS ROUTINE_DEFINITION, " +
        "t.type_desc AS trigger_type, " +
        "e.type_desc AS triggering_event " +
        "FROM sys.triggers t " +
        "JOIN sys.objects o ON t.parent_id = o.object_id " +
        "JOIN sys.schemas s ON o.schema_id = s.schema_id " +
        "JOIN sys.trigger_events e ON t.object_id = e.object_id " +
        "WHERE s.name IN (%s) " +
        "AND o.object_id IN (SELECT object_id FROM sys.tables ) " +
        ") ";

где %1 - Выражение из параметра "Scanner Schema"

Связи (Иерархия)

Древо активов:

+ База данных MsSql
└── + Схема X
    └── + Таблицы
        ├── + Таблица X
        |   ├── + Колонки
        |   |   └── + Колонка X
        |   ├── Foreign Key (Список всех FK таблицы)
        |   ├── Primary Key (Список всех PK таблицы)
        |   ├── Check Constraint (Список всех CC таблицы)
        |   └── Unique Constraint (Список всех UC таблицы)
        ├── + Представления
        |   └── + Представление X
        |       └── + Колонки
        |           └── Колонка X
        ├── Процедуры
        ├── Синонимы
        ├── Функции
        └── Триггеры

Таблица 2 – Параметры связей

Координаты всех связей: 1:0

Имя связи	Левый актив - Правый актив	Row Mapper
com_infa_ldm_relational_DatabaseSchema	Database - Schema	MsSqlSchemaLinkRowMapper
com_infa_ldm_relational_SchemaTable	Schema - Table	MsSqlTableLinkRowMapper
com_infa_ldm_relational_SchemaView	Schema - View	MsSqlViewLinkRowMapper
com_infa_ldm_relational_SchemaFunction	Schema - Function	MsSqlObjectsFunctionLinkRowMapper
com_infa_ldm_relational_SchemaStoredProcedure	Schema - StoredProcedure	MsSqlObjectsProcedureLinkRowMapper
com_infa_ldm_relational_SchemaSynonym	Schema - Synonym	MsSqlObjectsSynonymRowLinkMapper
com_infa_ldm_relational_SchemaTrigger	Schema - Trigger	MsSqlObjectsTriggerLinkRowMapper
com_infa_ldm_relational_ViewViewColumn	View - ViewColumn	MsSqlViewColumnLinkRowMapper
com_infa_ldm_relational_TableColumn	Table - Column	MsSqlTableColumnLinkRowMapper
com_infa_ldm_constraint_DataSetPrimaryKey	Table - PrimaryKey	MsSqlConstraintsLinkFromTableRowMapper
com_infa_ldm_constraint_DataSetForeignKey	Table - ForeignKey
com_infa_ldm_relational_TableUniqueConstraint	Table - UniqueConstraint
com_infa_ldm_relational_TableCheckConstraint	Table - CheckConstraint

Связи констрейнтов

Координаты связей констрейнтов:

• com_infa_ldm_constraint_ForeignKeyPrimaryKeyDataElement: 0:0,

• com_infa_ldm_constraint_PrimaryKeyDataElement: 0:0,

• com_infa_ldm_relational_UniqueConstraintColumn: 1:0,

• com_infa_ldm_relational_CheckConstraintColumn: 0:0.

Таблица 3 – Параметры связей констрейнтов

Имя связи	Левый актив - Правый актив	Row Mapper
com_infa_ldm_constraint_ForeignKeyPrimaryKeyDataElement	ForeignKey - Column	MsSqlConstraintsLinkFromColumnRowMapper OracleConstraintsLinkToColumnRowMapper
com_infa_ldm_constraint_PrimaryKeyDataElement	PrimaryKey - Column	MsSqlConstraintsLinkFromColumnRowMapper
com_infa_ldm_relational_UniqueConstraintColumn	UniqueConstraint - Column	MsSqlConstraintsLinkFromColumnRowMapper
com_infa_ldm_relational_CheckConstraintColumn	CheckConstraint - Column	MsSqlCo

Связи объектов

Координаты всех связей: 0:0

Таблица 4 – Параметры связей объектов

Имя связи	Левый актив	Правый актив
com_infa_ldm_relational_TableFunction	Table	Function
com_infa_ldm_relational_TableStoredProcedure		StoredProcedure
com_infa_ldm_relational_TableTrigger		Trigger
com_infa_ldm_relational_TableSynonym		Synonym
com_infa_ldm_relational_ViewFunction	View	Function
com_infa_ldm_relational_ViewStoredProcedure		StoredProcedure
com_infa_ldm_relational_ViewSynonym		Synonym
com_infa_ldm_relational_ViewTrigger		Trigger
com_infa_ldm_relational_FunctionFunction	Function	Function
com_infa_ldm_relational_FunctionStoredProcedure		StoredProcedure
com_infa_ldm_relational_FunctionSynonym		Synonym
com_infa_ldm_relational_FunctionTrigger		Trigger
com_infa_ldm_relational_StoredProcedureStoredProcedure	StoredProcedure	StoredProcedure
com_infa_ldm_relational_StoredProcedureFunction		Function
com_infa_ldm_relational_StoredProcedureSynonym		Synonym
com_infa_ldm_relational_StoredProcedureTrigger		Trigger
com_infa_ldm_relational_SynonymSynonym	Synonym	Synonym
com_infa_ldm_relational_SynonymFunction		Function
com_infa_ldm_relational_SynonymStoredProcedure		StoredProcedure
com_infa_ldm_relational_SynonymTrigger		Trigger