Концепция БД MIS3 — различия между версиями

Текущая версия на 11:32, 21 апреля 2022

Целостность

База сама должна поддерживать целостность данных – это возможно с помощью стандартных средств constraint и trigger.

Foreign Key на другие схемы допускается только в состоянии Disable. (подумать почему, и может быть сделать более жесткое требование).

TODO Нужен скрипт который помогает найти недостающие FK. смотри на SVN "ER\DOC\DB\SCRIPTS\Columns - Кандидаты на FK.SQL"

Самодокументирование

Количество комментариев, Foregn Key должно и правил именования должно быть достаточным для понимания БД. Поэтому предъявляются следующие требования

см pg_script в которой есть скрипты для правильного именования объектов.

см скрипты на svn IBIS\trunk\ER3\DB\LocalScripts\

TODO Также необходимо добавить скрипты для проверки достаточности комментариев на аналогичные АИДС:

• "DOC\DB\SCRIPTS\Column - Правит комментарии к ID.sql"
• "DOC\DB\SCRIPTS\Column - Столбцы без комментария.sql"
• "DOC\DB\SCRIPTS\Columns - Неправильные комментарии к ID.sql"
• "DOC\DB\SCRIPTS\Table - Неправильные комментарии к таблицам.sql"

Соглашение об именовании

Таблицы, процедуры, пакеты именуются без префиксов в единственном числе на английском языке. Для вторичных объектов - последовательностей, ключей, индексов и так далее - правила, выводящие их имя из имени основного объекта.

1. Таблицы

1.1. Таблицы именуются существительными в ед. числе на английском языке маленькими буквами. (Рассматривать как название сущности). Не использовать в именовании этих объектов символ «$»

1.2. Все таблицы и столбцы должны иметь комментарий. Для таблицы в комментарии обязательно должно быть написано кто автор таблицы. (После строчки “Author:” ). Если автор у таблицы изменился, то старого автора оставлять.

1.3. Каждая таблица должна иметь первичный ключ, типа serial (smallserial) или bigserial (для таблиц в которых предполагается больше 1 млн записей).

1.4. Первичные ключи делаем через serial (smallserial) или bigserial

1.5. Столбцы, ссылающиеся на другую таблицы именовать по правилу: %TABLE%_ID

1.6. Таблицы содержащие исторические данные %TABLE%_H

1.7. Временные таблицы %TABLE%_TMP

1.8. Таблицы созданные для каких либо тестов TEST_%TABLE%

1.9. Таблицы копии %TABLE%_YYMMDD

1.10. Не рекомендуется в названиях столбцов использовать отрицательную частицу (not и.т)

2. Индексы

Внимание: на SVN существует скрипт, который правильно переименовывает все индексы (trunk/ER3/DB/LocalScripts/Index_name.sql)

2.1. Индексы не уникальные (NORMAL) именуются по правилу IX_%TABLE%$%FIELDS%.

2.2. Индексы уникальные именовать по правилу IU_%TABLE%$%FIELDS%.

2.3. Индексы функциональные (FUNCTION-BASED NORMAL) именовать по правилу IF_%TABLE%$%FUNC%.

2.4. Индексы первичного ключа именуются так же как и сам первичный ключ PK_%TABLE%$FIELD%

3. Констаринты

3.1. Ссылки (References) именуем по правилу FK_%TABLE%$%REFTABLEF%$%FIELDS%.

3.2. Первичный ключ именуются по правилу PK_%TABLE%$%FIELD%

4. Триггеры

4.1. Триггеры именуются по правилу %TABLE%_tr_%Суффикс%. Где суффикс определяет тип триггера и может иметь длину от 2 до 5 символов.

• Первая буква определяет момент срабатывания триггера (Timing) и может имеет значение A или B (After, Before)
• ДалееObject Event - комбинация букв I,U,D
• Если триггер уровня оператора, то прибавляем в конце S

4.2. Триггерные функции именуются по правилу %TABLE%_tr_%имя из триггера%'_fn

для проверки именования триггеров существует функция mm.dev_ddl_check_trigger()

для проверки именования триггерных функций существует функция mm.dev_ddl_check_trigger_func()

5. Другие объекты

5.1. SEQUENCE Правило выглядит так %TABLE%_%COLUMN%_SEQ.

5.2. Views  : <name>_V

5.3. Materialized Views: <name>_MV

5.4. Types  : <name>_T

5.5. Directories  : <name>_DIR

5.6. External Tables  : <name>_EXT

6. PqSQL Variables

6.1. Package Global Variables: g_variable_name
6.2. Local Variables  : v_variable_name
6.3. Types  : t_type_name
6.4. Cursors  : c_cursor_name
6.5. Exceptions  : e_exception_name
6.6. Input Parameters  : i_parameter_name
6.7. Outut Parameters  : o_parameter_name
6.8. In/Out Parameters  : io_parameter_name

7. Процедуры, функции

7.0. Имя функции должно начинаться с имени сущности. Например addr_clone
7.1. При объявлении переменных в заголовке обязателен префикс p_. Это избавит от неоднозначности при компиляции
7.2. Переменные внутри PL/SQL блока должны иметь префикс v_. Это избавит от неоднозначности при компиляции
7.3. Процедуры изменяющие данные в таблицах, должны содержать DO
7.4. Функции, возвращающие значение должные должны содержать GET

8. Объекты связанные с аудитом данных

• Весь аудит храниться в схеме audit.Для преобразования данных из hstore в русские названия используются %table_name_hstore_transform%. См статью Аудит в MIS3

9. Сводная таблица правил

10. Сокращения

Если при именовании подчиненных объектов (FK и прочее), выходим за ограничение длины, то использовать следующее правило:

• APPLICATIONS = APPL (4)
• APPLICATION_FUNCTIONS = APFU (2:2)
• APPLICATON_FUNCTION_ROLES = APFR (2:1:1)
• APPLICATION_FUNCTION_ROLE_BANANAS = AFRB (1:1:1:1)

По возможности использовать стандартные сокращения при названии сущностей  :

см pg_script в которой есть скрипты для правильного именования объектов.

Безопасность

Авторизация пользователей осуществляется с помощью стандартных средств Postgresql. При этом:

• скрывается истинный пароль и пользователя.
• Пользователь SOFTMASTER имеет очень сложный пароль и под ним никто не должен логиниться.
• Логины (ВСЕГДА БОЛЬШИМИ БУКВАМИ!) postgresql формируются по правилу X+<логин большими буквами в системе МИС>
• Пользователь SOFTMASTER (БОЛЬШИМИ БУКВАМИ) обладает всеми правами (из под него ставиться БД)

Роли POSTGRESQL

Разделение схем

Название схем должны быть в нижнем регистре. Все данные МИС должны храниться в схеме mm. Данные аудита должны храниться в схеме audit. Архивные данные в схеме ht.

Табличные пространства

Данные и индексы хранить в разных таблспейсах USR_DATA и USR _INDX . Данные аудита хранить в таблеспейсах LOG_DATA и LOG_INDX. Архивные данные в схемах HST_DATA и HST_INDX.

AUDIT

Описание механизма аудита смотреть в комментариях к функциям audit.audit_table см отдельную статью Аудит в MIS3

Резервное копирование

• pg_basebackup + wal archive на неделю (для возможности отката базы на любое время в течении недели назад)
• pg_dump раз в неделю с аккуратно настроенными политиками хранения архивов (на случай если попросят восстановить базу чтобы что то посмотреть например на начало 2010 года).
• hot standby на случай сбоя мастер сервера

Ссылки

• http://habrahabr.ru/post/197742/

Разделение данных на оперативные данные и архивные.

Таблицы, которые относятся к архивным данным хранить в схеме zz, таблспейсы HST_DATA, HST_INDX

Верификация

Последняя версия скриптов для верификации именования и расположения объектов находятся на SVN IBIS/trunk/ER3/DB/LocalScripts На данный момент это:

• правильные имена constraint (Constraint_name.sql) - делает сразу правильные
• правильные имена index (Index_name.sql) (делает сразу правильные)
• функции у которых нет комментария Check_comments\funct_desc_check.sql
• таблицы у которых нет комментариев Check_comments\table_desc_check.sql
• для всех объектов овнер должен быть MIS_USER (select mm.mis_user_owner)

Ссылки

• http://www.sqlstyle.guide