Проектирование и разработка БД Oracle для информатизации объектов культуры

Содержание

Введение

1. Выбор модели БД

1.1 Исходные требования к проектируемой БД

1.2 Обзор основных моделей данных современных БД

1.2.1 Иерархические базы данных

1.2.2 Сетевая СУБД

1.2.3 Многомерная СУБД

1.2.4 Реляционная модель баз данных

1.2.5 Объектно-ориентированные СУБД

1.3 Реализация Объектно-ориентированного проектирования в реляционных БД

1.3.1 Объекты как таблицы. Модель ROT

1.3.2 Модификация ROT с учетом наследования

1.3.3 Модель А. Тенцера “База-данных-хранилище объектов”

1.3.4 Модификация модели Тенцера. Модель Entity-Attribute-Value

1.3.5 Сравнительный анализ методов реализации объектно-ориентированного проектирования в реляционных БД

1.3.6 Итоговый анализ эффективности методов объектно-ориентированного проектирования в реляционных СУБД

1.4 Обоснование выбора Entity-Attribute-Value в качестве метода проектирования Базы Данных

2. Разработка БД

2.1 Общая архитектура БД информационной системы

2.2 Модель системных данных

2.2.1 Общая архитектура системных объектов БД

2.2.2 Описание процесса генерации таблиц

2.2.3 Реализация функциональности добавления, редактирования и удаления объектов

2.2.4 Реализация функциональности тщательного контроля доступа на уровне объектов

2.2.5 Реализация аудита на изменение объектов

2.2.6 Реализация механизма поиска объектов

2.3 Модель пользовательских данных

2.3.1 Общая архитектура пользовательских объектов БД

2.3.2 ER-диаграмма фотодокументов архива

2.3.3 ER-диаграмма фонодокументов архива

2.3.4 ER-диаграмма остальных пользовательских объектов

2.3.5 Представления пользователя приложения

3. Апробация функционирования БД

3.1 Описание работы БД как основной части информационной системы

3.2 Тестовые примеры (test cases)

3.3 Сводная таблица тестирования (test log)

Заключение

Приложение 1. Листинги пакетов

Листинг пакета DATAMODIFICATION

Листинг пакета GENERATETABLES

Листинг пакета SEARCHDATA

Листинг пакета SECURITYDATA

Приложение 2. Расширенный список тестовых примеров

Список использованной литературы

Введение

В последнее время, в связи с новыми возможностями и вызовами времени, все острее ощущается потребность разных отраслей экономики в развитии информационных технологий, в том числе это касается и объектов культуры, где собраны воистину уникальные и бесценные документы. Кроме того применение информационных систем способствует улучшению функционирования объектов, увеличению производительности труда, расширения аудитории, увеличению информационной безопасности экспонатов объектов культуры. Как правило, к системам, проектируемым к сфере культуры предъявляются очень высокие требования по обеспечению сохранности информации и данных, их конфиденциальности, разграничению доступа пользователей к разным ресурсам этой системы. Вопрос производительности системы тоже не является праздным. Но все же главной задачей таких систем остается, конечно, сохранность информации и данных. Поэтому БД, как основная составляющая таких систем рассматривается, прежде всего, как хранилище данных. Это хранилище в то же время должно гарантировать сохранность и конфиденциальность хранимой в нем информации. база информационный таблица тенцер

В процессе разработки и внедрения системы я столкнулся с проблемой разграничения прав доступа для разных пользователей. Была предложена система разграничения прав пользователей на основе пакета SecurityData. Этот пакет, в зависимости от контекста подключения, предоставляет пользователю права владельца объекта или по умолчанию. Это важно, т.к. права устанавливаются динамически и их легко можно менять в процессе работы системы. Кроме того, нужно отметить, что в БД Oracle существует встроенный пакет DBMS_RLS в рамках реализации Fine Grained Access Control(FGAC), который осуществляет политику прав доступа на объекты. FGAC начиная с Oracle 8i позволяет во время выполнения динамически добавлять условие (конструкцию WHERE) ко всем запросам, обращенным к таблице или представлению баз данных. Можно проверить, кто выполнял запрос, с какого терминала и когда он выполнялся, а затем создать условие на основе данной информации. Однако средства тщательного контроля доступа доступны только в редакциях Enterprise и Personal Edition; в Standard Edition эти примеры не работают. На данный проект разработчики обладают лицензией на Oracle 10 Standart Edition, соответственно пришлось разрабатывать пакет SecurityData.

Любая информационная система не может состоять только из базы данных, в ней должен быть еще и интуитивно-понятный GUI-интерфейс. Данный интерфейс разрабатывался на языке Java c использованием различных J2EE фреймворков. Этот выбор сделан, потому что язык программирования Java - небольшая, простая для изучения система, оснащённая всесторонне расширяющимся набором API. Разработчики могут «написав однажды, запускать всюду», что даёт языку Java огромное преимущество перед другими языками на рынке. Кроме того, программы на Java на всех операционных системах при компиляции преобразуются к одному и тому же двоичному формату. Выигрыш по сравнению с ситуацией, когда для установки программы на нескольких платформах требуется писать и компилировать код отдельно для каждой платформы, очевиден. Программист может работать над приложением под одной платформой, сокращая при этом время и стоимость разработки, и быть уверенным, что его код будет работать везде. Возможность «написав однажды, запускать всюду», для многих программистов является достаточной причиной для перехода к языку Java от таких языков как C или С++, работоспособность приложений на которых зависит от платформы и, также, приложения являются «несетевыми». В дополнение к этому, возможно создание приложений, многократно использующих общедоступные объекты, что ещё более уменьшает стоимость разработок и позволяет разработчикам концентрировать свои усилия только на создании чего-то нового. Java хороша в основном мощными библиотеками, что в значительной степени избавляет разработчика от написания велосипедов. Ну и автоматическое управление памятью позволяет сосредоточиться на реализации самой задачи. Многие библиотеки, как из стандартной поставки, так и сторонних производителей проверены временем и продолжают совершенствоваться. Кроме того, нельзя не забывать что большинство библиотек и фреймворков свободных в доступе и бесплатны. Более того эти библиотеки, как правило, open-source. Также в Java наблюдается ориентация на Internet-задачи, сетевые распределенные приложения, что как раз и нужно при разработке информационной системы для базы данных разрабатываемой в работе.

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

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

Наконец, важно то, что проектируемая в дипломном проекте система не должна работать в режиме 24х7х365. Должна гарантироваться ее работа во время работы архива, а именно в рабочие дневные часы будних дней, а значит обновления базы данных, действия по ее администрированию, репликации и разработке бизнес-логики возможно, даже более того, необходимо проводить или в ночное время будних дней или в выходные и праздничные дни.

1. Выбор модели БД

1.1 Исходные требования к проектируемой БД

Разрабатываемая база данных (далее БД) предназначена для хранения и обработки информации архива, а именно аудиофайлов, фотофайлов и текстовой информации, которая характеризуется сильной разреженностью данных. Под разреженностью данных здесь понимается отношение занятых позиций данных к количеству всех возможных позиций.

БД должна обеспечивать:

1. Поддержку модификацию схемы.

2. СУБД: Oracle.Версия - 10.2.0.4 Standard Edition.

3. Поддержку 3 языков (русский, английский и немецкий), должна быть реализована возможность переключения между языками.

4. Поддержку безязыкового символьного типа, т.е. типа, значения которого уникально как для русской, так и английской и немецкой версии БД.

5. Поддержку аудита изменения и удаления всех объектов в СУБД.

6. Поддержку логирования изменения и удаления значений атрибутов объектов.

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

8. Генерацию представлений для клиентского приложения.

9. Генерацию XML документов для клиентского приложения.

10. Механизма поиска, как по объектам, так и по атрибутам объекта.

11. Поддержку работы информационной системы на ОС Solaris Operating System (x86-64).

12. Поддержку хранения в БД фото и аудиодокументов.

Относительный приоритет требований и их предполагаемая трудоемкость отражен в таблице 1.1. Отмечу, что приоритет тем выше, чем меньше оценка, которая стоит в столбце приоритет.

Таблица 1.1. Таблица исходных требований.

Кратко прокомментирую список исходных требований:

1. БД должна поддерживать модификацию схемы.

В СУБД Oracle при любой DDL операции производится окончание транзакции и блокировка всех таблиц, над которыми производится данная DDL операция.

2. СУБД: Oracle.Версия - 10.2.0.4 Standard Edition.

На данный момент разработчики БД в рамках проекта ограничены лицензией на СУБД Oracle Standard Edition.

4. Поддержка безязыкового символьного типа, т.е. типа, значения которого уникально как для русской, так и английской и немецкой версии БД.

Таким значением, например, является шифр, который уникален и одинаков на любом языке.

7. Поддержка политики прав доступа к объектам в зависимости от пользователя. Политика безопасности должна обеспечивать разные права на объекты для владельцев объекта и для пользователей, которые не являются.

В БД Oracle существует встроенный пакет DBMS_RLS в рамках реализации Fine Grained Access Control(FGAC), который осуществляет политику прав доступа на объекты. FGAC начиная с Oracle 8i позволяет во время выполнения динамически добавлять условие (конструкцию WHERE) ко всем запросам, обращенным к таблице или представлению баз данных. Можно проверить, кто выполнял запрос, с какого терминала и когда он выполнялся, а затем создать условие на основе данной информации. Однако средства тщательного контроля доступа доступны только в редакциях Enterprise и Personal Edition; в Standard Edition эти примеры не работают. Необходимо разработать пакет, который реализует политику тщательного контроля доступа (FGAC) на СУБД Oracle Standard Edition.

8. Генерация представлений для клиентского приложения.

Пользовательским схемам даются синонимы на представления пользовательских таблиц. В коде представлений есть фильтрация по разным параметрам предоставляемых данных, в частности по политике прав доступа.

9. Генерация XML документов для клиентского приложения.

Пользовательскому GUI-интерфейсу для динамического построения списков данных нужно возвращать динамически генерируемые XML-документы для их последующего вывода в клиентском приложении.

1.2 Обзор основных моделей данных современных БД

1.2.1 Иерархические базы данных

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

Между объектами существуют связи, каждый объект может включать в себя несколько объектов более низкого уровня. Такие объекты находятся в отношении предка (объект более близкий к корню) к потомку (объект более низкого уровня), при этом возможно, когда объект-предок не имеет потомков или имеет их несколько, тогда как у объекта-потомка обязательно только один предок. Объекты, имеющие общего предка, называются близнецами.

Например, если иерархическая база данных содержала информацию о покупателях и их заказах, то будет существовать объект «покупатель» (родитель) и объект «заказ» (дочерний). Объект «покупатель» будет иметь указатели от каждого заказчика к физическому расположению заказов покупателя в объект «заказ».

В этой модели запрос, направленный вниз по иерархии, прост (например: какие заказы принадлежат этому покупателю); однако запрос, направленный вверх по иерархии, более сложен (например, какой покупатель поместил этот заказ). Также, трудно представить неиерархические данные при использовании этой модели.

Иерархической базой данных является файловая система, состоящая из корневой директории, в которой имеется иерархия поддиректорий и файлов.

Преобразование концептуальной модели в иерархическую структуру данных во многом схоже с преобразованием ее в сетевую модель, но и имеет некоторые отличия в связи с тем, что иерархическая модель требует организации всех данных в виде дерева. Преобразование связи типа 'один ко многим' между предком и потомком осуществляется практически автоматически в том случае, если потомок имеет одного предка, и происходит это следующим образом. Каждый объект с его атрибутами, участвующий в такой связи, становится логическим сегментом. Между двумя логическими сегментами устанавливается связь типа 'один ко многим'. Сегмент со стороны 'много' становится потомком, а сегмент со стороны 'один' становится предком. Ситуация значительно усложняется, если потомок в связи имеет не одного, а двух и более предков. Так как подобное положение является невозможным для иерархической модели, то отражаемая структура данных нуждается в преобразованиях, которые сводятся к замене одного дерева, например, двумя (если имеется два предка). В результате такого преобразования в базе данных появляется избыточность, так как единственно возможный выход из этой ситуации -- дублирование данных. Такие ситуации в проектируемой системе неизбежны, именно по этой причине данная модель не является наиболее предпочтительной для всех.

1.2.2 Сетевая СУБД

В сетевой структуре каждый элемент может быть связан с любым другим элементом. Сетевые базы данных подобны иерархическим, за исключением того, что в них имеются указатели в обоих направлениях, которые соединяют родственную информацию. Несмотря на то, что эта модель решает некоторые проблемы, связанные с иерархической моделью, выполнение простых запросов остается достаточно сложным процессом. Также, поскольку логика процедуры выборки данных зависит от физической организации этих данных, то эта модель не является полностью независимой от приложения. Другими словами, если необходимо изменить структуру данных, то нужно изменить и приложение, что существенно усложнит поддержку приложения ввиду неизбежной модификации структуры объектов используемых в ней[6].

1.2.3 Многомерная СУБД

В СУБД, основанных на многомерном представлении данных, данные организованы не в форме реляционных таблиц, а в виде упорядоченных многомерных массивов: гиперкубов (все хранимые в базе данных ячейки должны иметь одинаковую мерность, то есть находиться в максимально полном базисе измерений) и/или витрин данных, представляющих собой предметно-ориентированные подмножества хранилища данных, спроектированные для удовлетворения нужд отдельной группы (сообщества) пользователей и удовлетворяющие требованиям защиты от несанкционированного доступа в организации; они обеспечивают более быструю реакцию на запросы сведений за счет того, что обращения поступают к относительно небольшим блокам данных, необходимых для конкретной группы пользователей. Для достижения сравнимой производительности реляционные системы требуют тщательной проработки схемы базы данных, определения способов индексации и специальной настройки. В случае многомерных баз данных, как правило, не требуется даже указание на то, по каким реквизитам (группам реквизитов) требуется индексация данных. Ограничения SQL остаются реальностью, что не позволяет реализовать в реляционных СУБД многие встроенные функции, легко обеспечиваемые в системах основанных на многомерном представлении данных. Вместе с тем, реляционные СУБД обеспечивают качественно более высокий уровень защиты данных и разграничения прав доступа, а также имеют более развитые средства администрирования и реальный опыт работы с большими и сверхбольшими базами данных. В то время, как для многомерных баз данных, в настоящее время отсутствуют единые стандарты на интерфейс, языки описания и манипулирования данными. Многомерные СУБД не поддерживают репликацию данных, наиболее часто используемую в качестве механизма загрузки.

Основным достоинством является многомерных СУБД то, что в случае использования многомерных СУБД поиск и выборка данных осуществляется значительно быстрее, чем при многомерном концептуальном взгляде на реляционную базу данных, так как многомерная база данных денормализована, содержит заранее агрегированные показатели и обеспечивает оптимизированный доступ к запрашиваемым ячейкам. Кроме того, многомерные СУБД легко справляются с задачами включения в информационную модель разнообразных встроенных функций, тогда как объективно существующие ограничения языка SQL делают выполнение этих задач на основе реляционных СУБД достаточно сложным, а иногда и невозможным.

К недостаткам можно отнести: необходимость привлечения высококвалифицированных программистов для малейших изменений структуры базы данных и невозможность для конечного пользователя самостоятельно анализировать данные в порядке, не предусмотренном программистами.

Такая модель не является пригодной для проектируемой БД информационной системы, т.к. аналогично сетевой модели, планируется довольно частое изменение структуры данных, что является непростым процессом при использовании многомерных СУБД ввиду сложности их настройки.

1.2.4 Реляционная модель баз данных

Подавляющее большинство научных исследований в области баз данных в течение последних 30 лет было посвящено (иногда косвенно) реляционной модели БД. Фактически, введение реляционной модели в 1969 и 1970 годах было, несомненно, наиболее важным событием во всей истории развития теории баз данных. К преимуществам реляционной модели следует отнести то, что она основана на определенных математических и логических принципах.

Кратко и не совсем точно можно определить, что реляционная система -- это система, основанная на описанных ниже принципах: данные рассматриваются пользователем как таблицы (и никак иначе). Пользователю предоставляются операторы (например, для выборки данных), позволяющие генерировать новые таблицы на основании уже существующих. Например, в системе обязательно должны присутствовать оператор сокращения, предназначенный для получения подмножества строк заданной таблицы, и оператор проекции, позволяющий получить подмножество ее столбцов. Однако подмножество строк и подмножество столбцов некоторой таблицы, безусловно, можно рассматривать как новые таблицы.

Причина, по которой такие системы называют реляционными, состоит в том, что английский термин 'relation' (отношение), по сути, представляет собой общепринятое математическое название для таблиц. Поэтому на практике термины отношение и таблица в большинстве случаев можно считать синонимами, по крайней мере, для неформальных целей[9].

Завершим этот раздел несколько более формальным определением реляционной модели, чтобы можно было ссылаться на него в дальнейшем (хотя на данном этапе такое определение будет несколько абстрактным и не очень четким). В первом приближении реляционная модель состоит из следующих пяти компонентов:

1. Неограниченный набор скалярных типов (включая, в частности, логический тип или истинностное значение).

2. Генератор типов отношений и соответствующая интерпретация для сгенерированных типов отношений.

3. Возможность определения переменных отношения для указанных сгенерированных типов отношений.

4. Операция реляционного присваивания для присваивания реляционных значений указанным переменным отношения.

5. Неограниченный набор общих реляционных операторов (реляционная алгебра) для получения значений отношений из других значений отношений.

Вполне очевидно, что реляционная модель -- это нечто большее, чем просто 'таблицы плюс операции сокращения, проекции и соединения', хотя ее неформально довольно часто характеризуют именно таким образом[10].

1.2.5 Объектно-ориентированные СУБД

Объектно-ориентированная база данных -- база данных, в которой данные оформлены в виде моделей объектов, включающих прикладные программы, которые управляются внешними событиями. Результатом совмещения возможностей (особенностей) баз данных и возможностей объектно-ориентированных языков программирования являются Объектно-ориентированные системы управления базами данных (ООСУБД). ООСУБД позволяет работать с объектами баз данных также, как с объектами в программировании на ООЯП.

В частности ООСУБД должны осуществлять поддержку сложных объектов. В системе должна быть предусмотрена возможность создания составных объектов. Также должна быть поддержка индивидуальности объектов, т.е. все объекты должны иметь уникальный идентификатор, который не зависит от значений их атрибутов. Важно то, что в таких СУБД набор типов данных должен быть расширяемым. Пользователь должен иметь средства создания новых типов данных на основе набора предопределенных системных типов. Более того, между способами использования системных и пользовательских типов данных не должно быть никаких различий.

Первой формализованной и общепризнанной моделью данных была реляционная модель Кодда. В этой модели, как и во всех следующих, выделялись три аспекта - структурный, целостный и манипуляционный. Структуры данных в реляционной модели основываются на плоских нормализованных отношениях, ограничения целостности выражаются с помощью средств логики первого порядка и, наконец, манипулирование данными осуществляется на основе реляционной алгебры или равносильного ей реляционного исчисления. Как отмечают многие исследователи, своим успехом реляционная модель данных во многом обязана тому, что опиралась на строгий математический аппарат теории множеств, отношений и логики первого порядка. Разработчики любой конкретной реляционной системы считали своим долгом показать соответствие своей конкретной модели данных общей реляционной модели, которая выступала в качестве меры 'реляционности' системы.

Основные трудности объектно-ориентированного моделирования данных проистекают из того, что такого развитого математического аппарата, на который могла бы опираться общая объектно-ориентированная модель данных, не существует. В большой степени поэтому до сих пор нет базовой объектно-ориентированной модели. С другой стороны, некоторые авторы утверждают, что общая объектно-ориентированная модель данных в классическом смысле и не может быть определена по причине непригодности классического понятия модели данных к парадигме объектной ориентированности.

Как отмечают многие исследователи и разработчики, объектно-ориентированная система БД представляет собой объединение системы программирования и СУБД (альтернативная, но не более проясняющая суть дела точка зрения состоит в том, что объектно-ориентированная СУБД - это СУБД, основанная на объектно-ориентированной модели данных) [11].

В отличие от случая реляционных систем, где при создании приложения приходится одновременно использовать ориентированный на работу со скалярными значениями процедурный язык программирования и ориентированный на работу cо множествами декларативный язык запросов (это принято называть потерей соответствия - impedance mismatch), языковая среда ООБД - это объектно-ориентированная система программирования, естественно включающая средства работы с долговременными объектами. 'Естественность' включения средств работы с БД в язык программирования означает, что работа с долговременными (хранимыми во внешней БД) объектами должна происходить на основе тех же синтаксических конструкций (и с той же семантикой), что и работа со временными, существующими только во время работы программы объектами.

Другим аспектом языкового окружения ООБД является потребность в языках запросов, которые можно было бы использовать в интерактивном режиме. Если доступ к объектам внешней БД в языках программирования ООБД носит в основном навигационный характер, то для языков запросов более удобен декларативный стиль. Декларативные языки запросов к ООБД менее развиты, чем языки программирования ООБД, и при их реализации возникают существенные проблемы.

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

1.3 Реализация Объектно-ориентированного проектирования в реляционных БД

Существует множество подходов к решению данной задачи, например, создания собственных средств хранения, поддерживающих объектные типы данных. Вообще, под хранением объектов в рамках настоящей работы будем подразумевать сохранение и восстановление их состояния, то есть значений атрибутов, из источников долговременного хранения информации. В литературе по объектно-ориентированному анализу и проектированию (ООАП) эти операции называются материализацией и дематериализацией объектов.

Рассмотрим задачу хранения объектов в реляционной базе данных. В этом случае выполняется так называемое O - R-преобразование, позволяющее перейти от объектной модели предметной области к реляционному способу хранения данных. Коротко рассмотрим несколько шаблонных решений этого вопроса и приведем результаты их сравнительного анализа.

Заранее оговорим, что реализация решений будет рассмотрена «в чистом виде», то есть отдельно от различных смежных вопросов, таких, как кэширование, блокировка, безопасность.

В качестве примера реализации анализируемых в [3] подходов рассмотрим справочную информационную систему «Электронный каталог» книжного магазина. В основу положена база данных реального интернет-магазина. Полное описание структуры представлено в виде диаграммы классов предметной области (рис. 1.1).

Рис. 1.1. Диаграмма классов предметной области.

База данных содержит информацию о 101 087 экземплярах товара, распределенных по 588 разделам, в том числе - 84 287 книг, 6 793 периодических изданий, 10 007 наименований кассет. База данных также содержит информацию о 5 243 издательствах и 42 857 авторах книг.

Основные измерения в [3] касались следующих операций:

- открытия всей базы данных (запуск программы);

- выборки всех данных по одному объекту «Книга» (максимальное количество атрибутов среди всех товаров);

- выборки всех данных по одному объекту «Кассета» (минимальное количество атрибутов среди всех товаров);

- выборки объекта-контейнера (справочника всех товаров со всеми их атрибутами);

- вычисление общей стоимости товаров, добавленных в корзину (взято 7 наименований с различным количеством);

- добавления нового объекта «Книга»;

- изменения данных объекта «Книга».

Сразу оговорим, что независимо от подхода существует необходимость в поддержке уникальных идентификаторов («id») каждого объекта, по крайней мере - в ветке с корнем «Товар».

1.3.1 Объекты как таблицы. Модель ROT

Первым из методов реализации объектно-ориентированного проектирования в реляционных БД рассмотрим подход, известный под названием Representing Objects as Tables (объекты как таблицы) [2] - ROT. Данный подход является наиболее «естественным» для реляционных баз данных. Суть его заключается в том, что каждому классу предметной области ставится в соответствие одна таблица реляционной базы данных с соответствующими атрибутами. Связи реализуются по соответствующим правилам проектирования реляционных баз данных. При этом таблицы для абстрактных классов не создаются, а атрибуты классов-предков присутствуют и в таблицах для классов-потомков.

Реализуемость: подход удобен для быстрой разработки программ, так как все операции над базой данных легко реализуются стандартными конструкциями SQL. Гибкость: очень низкая, практически любые изменения в структуре базы данных неизбежно приводят к необходимости исправления исходного кода. Особенности: нет необходимости в поддержке уникальных идентификаторов в пространстве всей базы данных, в принципе, можно обойтись даже без уникальных идентификаторов ветки «Товар». Объем базы данных и время выполнения почти всех операций - минимальные среди всех рассмотренных подходов [3].

1.3.2 Модификация ROT с учетом наследования

Модель ROT напрямую отображает классы предметной области в таблицы реляционной базы данных. Однако, как это было показано выше, это приводит к тому, что некоторые общие данные классов, находящихся в отношении наследования, оказываются расположены в разных таблицах и однотипную обработку данных приходится реализовывать для каждой таблицы в отдельности (в нашем примере - это класс «Товар» и его наследники).

Существует подход, позволяющий решить эту проблему. Суть данного подхода заключается в том, что для суперклассов, имеющих атрибуты или ссылки на другие объекты, создаются соответствующие таблицы, а в таблицы наследников помещаются только идентификатор и атрибуты, доопределенные в классах-наследниках. При этом между таблицей родителя и таблицей наследника устанавливается связь 1:1.

Реализуемость: данный подход в реализации немного сложнее предыдущего, большинство операций также реализуются заранее подготовленными запросами SQL. Гибкость: выше, чем у модели ROT, однако большинство изменений в структуре базы данных также требуют исправления исходного кода программы. Объем базы данных и время выполнения операций - чуть больше, чем у модели ROT [3].

1.3.3 Модель А. Тенцера “База-данных-хранилище объектов”

Главной идеей является неизменность схемы базы данных: по сути дела, предложенная в работе [3] схема является универсальной и готовой к использованию в любых объектно-ориентированных приложениях. К тому же заранее определена универсальная реализация методов материализации и дематериализации объектов.

Во-первых, описание свойств классов и связей между ними не дублируется у наследников, а восстанавливается благодаря зависимости Id -ParentId.

Во-вторых, у экземпляра объекта определены все свойства, и поэтому в упомянутые таблицы вносятся записи, соответствующие всем свойствам объекта.

В-третьих, в модели Тенцера отсутствует (явно не выражена) возможность создания атрибутов связи. В нашем примере - это атрибут «Количество», значение которого определяется только при сочетании одного конкретного экземпляра класса «Товар» с одним конкретным экземпляром класса «Корзина». Реализуем решение этого вопроса в виде ассоциативного класса «ТоварВКорзине» с атрибутом «Количество» (рис. 1.2).

Рис. 1.2. Представление атрибута связи в виде ассоциативного класса

В-четвертых, связи в этой модели реализованы только бинарные. В «классическом» проектировании баз данных существует возможность представления трех- и более сторонних связей. Трехстороннюю связь на диаграмме рис. 1.2 преобразуем в две бинарные путем введения дополнительного класса, описывающего связь. В данном случае классом связи может выступить сам ассоциативный класс, поэтому диаграмму на рис. 1.2 можно представить в следующем виде (рис. 1.3).

Рис. 1.3. Представление трехсторонней связи в виде совокупности бинарных отношений

Реализуемость: для реализации в программе данной модели необходимо, в первую очередь, реализовать подсистему управления данными, которая «на лету» собирает объекты. Подход применим при долговременной разработке либо при наличии уже реализованной подсистемы управления данными. Гибкость: высокая, практически все изменения в предметной области требуют внесения исправлений на уровне данных (конфигурирования) и не влияют на исходный код. Особенности - для универсальности программы имеется необходимость поддержки уникального идентификатора в пространстве всей базы данных. Разделение таблиц для атрибутов по типам приводит к необходимости выборки или поиска по всем таким таблицам. В таблицах такой базы данных очень удобно строить списки элементов (справочники). Объем базы данных и время выполнения операций - достаточно высокие, в большинстве тестов - максимальные [3].

1.3.4 Модификация модели Тенцера. Модель Entity-Attribute-Value

Рассмотрим возможную модификацию модели Тенцера. Внесенные изменения, с одной стороны, вызваны обобщением подхода: описание атрибутов всех объектов производится в одной таблице Attributes. Хранение значений всех атрибутов объектов производится в пределах одной таблицы. При этом возникают естественные проблемы, связанные с увеличением объема таких данных. Однако объем в данном случае является ценой за универсальность подхода.

С другой стороны, введение таблицы Containers продиктовано соображениями, вытекающими из практического опыта работы. Дело в том, что наиболее часто встречающимся видом ассоциации (кроме наследования) между классами является агрегация. Например, подсистема нормативно-справочной информации любой информационной системы обязательно будет содержать такой вид связи [4].

Наконец, другие виды связи в этой модели реализуются таким же образом, как и в исходном коде объектно-ориентированных программ - через атрибуты, являющиеся ссылками на другие объекты. В данном случае эти ссылки представляют собой внешние ключи, ссылающиеся на идентификатор соответствующего объекта. Значения этих атрибутов также располагаются в таблице ObjectData.

На рис. 1.4 представлена схема базы данных для предлагаемой модели.

Рис. 1.4. Схема базы данных модифицированной модели Тенцера.

Таблица Classes содержит описание классов системы. Атрибут Id - уникальный идентификатор, Parent_Id - ссылка на Id предка в этой же таблице, Caption - короткое (возможно, англоязычное - для исходного кода программы) название класса, Description - подробное описание класса.

Таблица Attributes содержит описание атрибутов классов. Id - уникальный идентификатор атрибута, Class_Id - ссылка на Id класса (в таблице Classes), к которому относится атрибут, Caption и Description - аналогично предыдущему. Для классов-наследников описание атрибутов не повторяется. Таким образом, для материализации описания класса необходимо восстановить всю структуру наследования «вверх» и выбрать описания всех атрибутов классов этого поддерева.

Таблица Objects содержит список экземпляров объектов. Id - уникальный идентификатор объекта, Class_Id - ссылка на Id класса (в таблице Classes), к которому относится экземпляр, Caption - краткое обозначение объекта (для быстрого вывода в списках), формируется программной системой, обычно соответствует значению одного (или комбинации нескольких) из свойств.

Таблица ObjectData содержит значения свойств объектов. Object_Id - ссылка на Id объекта (в таблице Objects), Attribute_Id - ссылка на Id атрибута (в таблице Attributes), Value - значение этого атрибута для данного объекта.

Таблица Containers содержит ключи ассоциации агрегирования. Здесь Container_Id - ссылка на Id объекта-владельца (в таблице Objects), Object_Id - ссылка на Id обекта (там же), содержащегося в контейнере. Сразу оговорим, что агрегирование в данном случае означает «содержит ссылку» и не подразумевает полное владение вложенными объектами.

Таким образом, таблицы Classes и Attributes содержат описание (метаданные), а остальные таблицы - сами данные.

Авторы измерений в [3] не стали касаться вопросов реализации описания типов атрибутов, контроля за совместимостью типов для ссылок и описания ролей классов в ассоциациях, так как это не является существенным для производимых измерений.

Реализуемость: аналогично предыдущей модели требует наличия подсистемы управления данными, облегчает ее реализацию благодаря представлению данных всех типов в одной таблице и требует лишь приведения типов. Гибкость: очень высокая, изменения в предметной области требуют внесения исправлений на уровне данных и не влияют на исходный код программы. Особенности - аналогичны модели Тенцера, для универсальности программы имеется необходимость поддержки уникального идентификатора в пространстве всей базы данных. Однако здесь эту проблему решить проще благодаря меньшему количеству таблиц. Объединение всех атрибутов в одну таблицу облегчает реализацию поиска и выборки их значений. Объем базы данных и время выполнения операций - очень высокие[3].

1.3.5 Сравнительный анализ методов реализации объектно-ориентированного проектирования в реляционных БД

Результаты измерения времени выполнения операций и объема базы данных приведены в табл. 1.2 и на рис. 1.5-1.14.

Таблица 1.2. Время выполнения операций и объемы баз данных

В [3] был произведен анализ рассмотренных выше методов реализации объектно-реляционного проектирования в реляционных БД. Ниже изложу основные итоги данного анализа.

Открытие всех таблиц базы данных. Суммарное время открытия всех таблиц базы данных в модели Тенцера и ее модификации значительно превышает этот показатель для ROT и ее модификации (рис. 1.5.). Это связано с «гигантскими» таблицами Objects, Links и т.д.

Рис. 1.5. Открытие всех таблиц базы данных, с

Выборка всех данных по одной Книге. На проведение выборки данных по одной Книге (рис. 1.6, 1.7) не влияет определение типа на этапе выполнения, так как в любом случае количество атрибутов остается одинаковым - максимальным. Хорошие результаты показывает модифицированная модель Тенцера, так как выборка самих значений производится по одной таблице ObjectData.

Рис. 1.6. Выборка всех данных по одной Книге без определения типа, с

Рис. 1.7. Выборка всех данных по одной Книге с определением типа, с

Выборка всех данных по одной Кассете. Общая картина практически не изменяется (см. рис. 1.8.) - остается достаточно большим, стоимость владения такой сложной информационной системой будет значительно ниже, чем выполненной в ROT, так как определяется лишь при универсальном запросе (без определения типа). В случае динамического определения типа товара для модели ROT с наследованием и модели Тенцера происходит резкое уменьшение времени выполнения операции, так как в этом случае запрос строится по меньшему количеству таблиц.

Рис. 1.8. Выборка всех данных по одной Кассете без определения типа, с

Рис. 1.9. Выборка всех данных по одной Кассете с определением типа, с

Выборка всех данных по всем товарам. Результатом такой операции является не просто список наименований, а полная информация о каждом товаре. Поэтому модель Тенцера проигрывает модели ROT, однако значительно опережает ROT с наследованием благодаря меньшему количеству таблиц в таком запросе (см. рис.1.10.).

Рис. 1.10. Выборка всех данных по всем товарам, с

Вычисление суммы товаров в корзине. Время выполнения данной операции в модели Тенцера и ее модификации значительно больше, чем в модели ROT. Связано это с необходимостью учета ссылок через таблицу Links (или Containers) и вытекающим отсюда «каскадированием» запроса. К тому же, в модифицированной модели время вычисления суммы еще больше из-за необходимости преобразования данных строковых типов в числовые.

Рис. 1.11. Вычисление суммы товаров в корзине, с

Добавление и изменение данных одной Книги. Увеличенное время выполнения данных операций в модели Тенцера и ее модификации по сравнению с ROT (см. рис.1.12-1.14), опять же, объясняется более длинными таблицами, в которые вносятся данные. Причиной большого разброса времени выполнения этих операций, видимо, является ненаблюдаемое кэширование данных.

Рис. 1.12. Добавление новой Книги, с

Рис. 1.13. Изменение данных по одной Книге, с

Рис. 1.14. Объем базы данных, МБ

1.3.6 Итоговый анализ эффективности методов объектно-ориентированного проектирования в реляционных СУБД

Полученные результаты показывают, что для создания небольших информационных систем, не требующих дальнейшего сопровождения, вполне подходит модель представления классов как таблиц (ROT): она позволяет быстро реализовать все необходимые операции над данными, при этом сами операции выполняются очень быстро, а размер базы данных получается минимальный. Для придания большей гибкости такой системе можно использовать модификацию модели ROT с наследованием (средние системы). При разработке сложных информационных систем, требующих длительного сопровождения и предполагающих дальнейшее развитие, рекомендуется использовать модифицированную модель Тенцера. При этом основной объем работ будет посвящен разработке подсистемы управления данными, оптимизации скорости выполнения операций (при проведении тестов задачи решались «в лоб», без рассмотрения проблемы оптимизации). И хотя время выполнения многих операций в такой модели все равно ценой подсистемы управления данными и ядром интерфейса. При этом практически все вопросы сопровождения программного продукта будут решаться на уровне конфигурирования специалистом квалификации «опытный пользователь» или «администратор ИС». В случае использования ROT, как было описано выше, стоимость сопровождения определяется работой более дорогого специалиста (возможно, нескольких) квалификации «программист».

В данной дипломной работе я ставлю перед собой цель спроектировать и реализовать базу данных (БД в дальнейшем) для одного из архивов нашего замечательного города.

1.4 Обоснование выбора Entity-Attribute-Value в качестве метода проектирования Базы Данных

В качестве метода проектирования БД, я выбрал набирающую популярность в наши дни модель Entity-Attribute-Value, более известную в нашей литературе как модифицированная модель Анатолия Тенцера. Приведу причины, по которым я решил остановиться на данной модели.

Во-первых, Гибкость модели. Теоретически не существует ограничений на количество атрибутов на сущность. Число параметров может быть увеличено, когда это необходимо без изменения схемы БД.

Во-вторых, Эффективная организация пространства для сильно рассеянных данных. Отпадает нужда в резервировании места для атрибутов, у которых значения - null. Данная модель предполагает возможное наличие NULL значений для атрибутов, например, сотрудники архива создали новый объект, начали заполнять значения атрибутов, но по каким-либо причинам не заполнили все атрибуты (например, на момент создания объекта было известно значение определенного атрибута). Однако пользователям БД необходимо чтобы данный объект находился в БД (например, чтобы заполнить атрибуты объекта позднее, когда появится соответствующая информация). Механизм полного заполнения значений всех атрибутов объектов недопустим по этой причине, т.к. это приведет к неудобству и невозможности работы пользователей. Однако у данного механизма, очевидно, есть слабое место, а именно вероятность появления дубликатов объектов. Для устранения данной проблемы, для плоских таблиц создаются уникальные индексы на атрибуты, которые определят уникальность данного объекта. Кроме того, необходимо отслеживать объекты, на которые ни один другой объект не ссылается, и удалять их в нужный момент (например, раскрытия дампа на production server). Для выполнения этой задачи спроектирована процедура DropUnusedObjects пакета SystemData.

В-третьих, по данным системных таблиц происходит генерация пользовательских таблиц. Такая модель имеет свои преимущества: например если есть необходимость добавить поле в таблицу или удалить это поле. Если это делать напрямую с помощью DDL операции под живыми пользователями - то произойдет нарушение корректной работы приложения. EAV модель предлагает вместо этого изменить описание таблицы, а затем провести в удобный момент процесс генерации БД. Таким образом, отсутствие потерь данных и бесперебойность работы приложения гарантируется. На мой взгляд, в принципе в production БД не допустимы DDL операции (только как административные задачи и обновления), так как транзакционный DDL - очень сложная, дорогая и потому эксклюзивная операция. Нельзя доверять обычным пользователям приложения права на такие операции, т.к. DDL во время работы блокирует таблицы.

В-четвертых, описание атрибутов всех объектов производится в одной таблице Attributes со структурой. Хранение значений всех атрибутов объектов производится в пределах одной таблицы. При этом возникают естественные проблемы, связанные с увеличением объема таких данных. Однако объем в данном случае является ценой за универсальность подхода.

В-пятых, модель хорошо используется со структурами данных XML(атрибут вставляется между начальными и конечными тегами XML). Это очень важно, т.к. в ближайшем будущем предполагается реализации интеграции информационной системы с внешними сервисами, язык XML для которых, является стандартом.

Кроме того, Entity-Attribute-Value model (EAV) - Сущность - Атрибут - Значение модель - модель данных, в которой одна строка хранит в себе одиночный факт. Модель EAV очень полезна в случаях, когда число параметров, которые потенциально будут применимы к сущности, изменится в большую или меньшую сторону по сравнению с тем, что на данный момент есть. Это представляется весьма полезным ввиду возможной реорганизации работы архива.

Наконец, другие виды связи в этой модели реализуются таким же образом, как и в исходном коде объектно-ориентированных программ - через атрибуты, являющиеся ссылками на другие объекты. В данном случае эти ссылки представляют собой внешние ключи, ссылающиеся на идентификатор соответствующего объекта.

При разработке сложных информационных систем, требующих длительного сопровождения и предполагающих дальнейшее развитие данная модель имеет преимущества. Основной объем работ будет посвящен разработке подсистемы управления данными, оптимизации скорости выполнения операций. И хотя время выполнения многих операций в такой модели все равно останется достаточно большим, стоимость владения такой сложной информационной системой будет довольно низкой, так как определяется лишь ценой подсистемы управления данными и ядром интерфейса, а значит новые проекты при разработанной подсистеме управления данных, будут относительно легки в разработке, а потому дешевы. При этом практически все вопросы сопровождения программного продукта будут решаться на уровне конфигурирования специалистом квалификации «опытный пользователь» или «администратор ИС».

Проектирование БД состоит из двух частей:

1. Проектирование системных таблиц, которые содержат в себе информацию обо всех несистемных таблиц, т.е. метаданные схемы.

2. Проектирование несистемных таблиц, которые содержат пользовательские данные.

На основе системных таблиц осуществляется генерация несистемных таблиц в функции doGenerate пакета GenerateInstance. Системные таблицы организованы по модели Entity-Attribute-Value, а несистемные таблицы реализованы в виде плоских таблиц.

Концептуально EAV таблица состоит из 3 составляющих:

1. ID Сущности/Объекта.

2. Атрибут/Параметр объекта.

3. Значение атрибута.

2. Разработка БД

2.1 Общая архитектура БД информационной системы

Рассмотрим в данной главе, каким образом я решал озвученную в 1 главе постановку задачи и требования системы. База данных состоит из двух частей: EAV-модели, состоящей их системных таблиц и обычной реляционной модели в виде плоских реляционных таблиц. Принципиально важно отметить то, что EAV-модели, состоящей их системных таблиц достаточно для полноценной работы Базы Данных, но в Базу данных были введены плоские реляционные таблицы по двум причинам: во-первых, сложность написания запросов к пользовательским данным в EAV-модели, во-вторых, высокая стоимость, а значит будет затрачиваться относительно большое время на выполнение запросов к пользовательским данным в EAV-модели. Соответственно для ускорения работы выполнения запросов к пользовательским данным была внедрена обычная реляционная модель в виде плоских пользовательских реляционных таблиц.

Составные части общей архитектуры БД отражены на рис2.1.

Рис. 2.1. Составные части архитектуры БД.

Итоговая ER-диаграмма с полным набором объектов БД, состоящей как из системных так и из пользовательских таблиц, представлена на рис. 2.2. Более детальное рассмотрение изложено в главах 2.2. “Модель системных данных” и 2.3. “Модель пользовательских данных”.

Рис. 2.2. ER-диаграмма общей архитектуры БД.

Перейдем к рассмотрению непосредственно архитектуры БД. Рассмотрим вначале модель системных данных, а затем модель пользовательских данных.

2.2 Модель системных данных

2.2.1 Общая архитектура системных объектов БД

В модели системных данных можно выделить: модель генерации таблиц, модель функциональности добавления, редактирования и удаления объектов, модель функциональности тщательного контроля доступа на уровне объектов, модель аудита и модель поискового механизма. ER-диаграмма модели системных объектов БД показана на рис. 2.3.

2.2.2 Описание процесса генерации таблиц

В модели EAV пользовательские таблицы создаются динамически на основе информации системных таблиц в ходе так называемого процесса генерации БД. Процесс генерации заключается в создании или пересоздании, т.е. удалении и создания вновь, схем всех несистемных пользователей БД. Таким образом, будут пересозданы все объекты пользовательских схем: вначале будут созданы пользовательские таблицы, затем на них пользовательские представления. Итак, приведу ER-диаграмму объектов, определяющую создание пользовательских таблиц (рис. 2.4).

Коротко опишу структуру объектов рис.2.4.:

Таблица Datatypes содержит в себе следующие поля:

1. id - первичный ключ.

2. name - наименование типа данных.

3. Usertablename - наименование словаря в котором хранятся значения атрибутов.

4. Oracledatatype - нейтив тип данных БД Oracle.

Таблица Objecttypes содержит в себе следующие поля:

1. id - первичный ключ.

2. Note - наименование объектного типа на русском языке.

3. Name - наименование объектного типа на английском языке.

Рис. 2.3. ER-диаграмма общей архитектуры системных объектов БД.

Ограничения, индексы, представления и синонимы. Коротко рассмотрим процесс генерации с точки зрения создания пользовательских таблиц. Важно то, что на этот момент должны быть созданы системные таблицы Datatypes, Attributes, Objecttypes и Objects, т.к. на их основе как раз и будут создаваться пользовательские таблицы.

Итак, приведу ER-диаграмму объектов, определяющую создание пользовательских таблиц (рис. 2.4).

Коротко опишу структуру объектов рис. 2.4.

Таблица Datatypes содержит в себе следующие поля:

5. id - первичный ключ.

6. name - наименование типа данных.

7. Usertablename - наименование словаря в котором хранятся значения атрибутов.

8. Oracledatatype - нейтив тип данных БД Oracle.

Таблица Objecttypes содержит в себе следующие поля:

4. id - первичный ключ.

5. Note - наименование объектного типа на русском языке.

6. Name - наименование объектного типа на английском языке.

Таблица Attributes содержит в себе следующие поля:

1. id - первичный ключ.

2. idObjectType - id объектного типа атрибута.

3. idDataType - id типа данных атрибута.

4. Length - длина типа данных (только для varchar2).

5. Name - наименование атрибута объектного типа на английском языке.

6. Note - наименование атрибута объектного типа на русском языке.

Таблица Objects содержит в себе следующие поля:

1. id - первичный ключ.

2. idObjectType - id объектного типа к которому принадлежит этот объект.

3. ID_OWNER - id владельца на данный объект. Внешний ключ к таблице Users.

4. ORIGHTS - право владельца на данный объект.

5. ARIGHTS - право всех пользователей кроме владельца на данный объект.

6. Created_user - пользователь, который создал объект.

7. Created_time - время, когда был создан объект.

8. Last_modified_user - пользователь, который последним изменял объект.

9. Last_modified_time - время, когда в последний раз объект изменялся.

Рис. 2.4. ER-диаграмма объектов, участвующих в генерации таблиц.

Для создания пользовательских таблиц был создан пакет generateTables. Код этого пакета приведен в приложении 1. Коротко рассмотрим спецификацию пакета.

SQL> CREATE OR REPLACE PACKAGE generateTables IS

2

3 procedure doGenerate;

4 procedure createParentTables;

5 procedure insertDataParentTables;

6

7 END generateTables;

8 Package created

Таблица 2.1. Комментарии к заголовку пакета generateTables.

Небольшие комментарии по реализации тела пакета generateTables:

Таблица 2.2. Комментарии к телу пакета generateTables.

2.2.3 Реализация функциональности добавления, редактирования и удаления объектов

Каждый объект в модели EAV состоит из набора присущих ему атрибутов. Эти атрибуты имеют свои значения. У каждого значения атрибута объекта есть свой тип данных. Каждый объект принадлежит конкретному объектному типу. Настройка параметров объекта (т.е. какому объектному типу принадлежит, какой у этого объекта набор атрибутов, типы данных этих атрибутов и т.д.) осуществляется программистом БД). ER-диаграмма объектов БД, участвующих в создании, редактирования или удалении объекта приведена на рис.2.5.

Рис. 2.5. ER-диаграмма объектов БД, участвующих в создании, редактирования или удалении объекта.

Структура и назначение таблиц Objects, Datatypes, Objecttypes и Attributes были рассмотрены чуть выше в описании процесса генерации таблиц. В этой диаграмме новой является таблица Userstringvalues. Опишем ее здесь. Таблица Userstringvalues -это таблица-справочник. Она содержит значения атрибутов, у которых тип данных String(VARCHAR). В этой таблице есть все поля, которые одназначно определят принадлежность данного значения конкретному атрибуту конкретного объекта, а именно:

1. ID - первичный ключ.

2. ID_OBJECT - определеяет ID объекта, которому принадлежит значение атрибута.

3. ID_ATTRIBUTE - определяет ID атрибута, значение которого определено в справочике.

4. VALUE - значение атрибута.

5. Created_user - пользователь, который создал объект.

6. Created_time - время, когда был создан объект.

7. Last_modified_user - пользователь, который последним изменял объект.

8. Last_modified_time - время, когда в последний раз объект изменялся.

Таким образом, рассмотрели структуру одного справочника Userstringvalues для типа данных String(VARCHAR). Однако в БД есть и другие типы данных: CLOB, DATE, DICTIONARY, MULTILANG и NUMBER. Для них будут аналогичные по структуре справочники: USERCLOBVALUES, USERDATEVALUES, USERDICTIONARYVALUES, USERMULTILANGVALUES и USERNUMBERVALUES. Стоит отметить, что в качестве значения Value справочника USERDICTIONARYVALUES будет указано id существующего объекта.

2.2.4 Реализация функциональности тщательного контроля доступа на уровне объектов

В процессе разработки и внедрения системы я столкнулся с проблемой разграничения прав доступа для разных пользователей. Была предложена система разграничения прав пользователей на основе пакета SecurityData. Этот пакет, в зависимости от контекста подключения, предоставляет пользователю права владельца объекта или по умолчанию. Это важно, т.к. права устанавливаются динамически и их легко можно менять в процессе работы системы. Кроме того, нужно отметить, что в БД Oracle существует встроенный пакет DBMS_RLS в рамках реализации Fine Grained Access Control(FGAC), который осуществляет политику прав доступа на объекты. FGAC начиная с Oracle 8i позволяет во время выполнения динамически добавлять условие (конструкцию WHERE) ко всем запросам, обращенным к таблице или представлению баз данных. Можно проверить, кто выполнял запрос, с какого терминала и когда он выполнялся, а затем создать условие на основе данной информации. Однако средства тщательного контроля доступа доступны только в редакциях Enterprise и Personal Edition; в Standard Edition эти примеры не работают. Как раз на момент разработки у нас была на данный проект лицензия на Oracle 10 Standart Edition, поэтому необходимо было разработать пакет SecurityData.

Итак, приведу ER-диаграмму объектов, определяющую политику тщательного контроля доступа на уровне объектов (рис. 2.6.).

Рис. 2.6. ER-диаграмма объектов тщательного контроля доступа.

В ER-диаграмму объектов тщательного контроля доступа входят 3 таблицы: OBJECTS, USERS и OBJECTPRIVILEGES.

Таблица OBJECTS была рассмотрена выше. Поэтому рассмотрим структуру таблиц USERS и OBJECTPRIVILEGES. Таблица USERS предназначена для хранения информации о пользователях информационной системы. Ее структура:

1. ID - первичный ключ.

2. NAME - имя пользователя, т.е. логин.

3. PASSWORD - пароль пользователя.

4. NOTE - краткие комментарии о пользователе.

5. Created_user - пользователь, который создал объект.

6. Created_time - время, когда был создан объект.

7. Last_modified_user - пользователь, который последним изменял объект.

8. Last_modified_time - время, когда в последний раз объект изменялся.

Таблица OBJECTPRIVILEGES предназначена для хранения информации о видах прав на объект. Ее структура:

1. ID - первичный ключ.

2. NAME - наименование привелегии

3. NOTE - краткие о привелегии.

Для реализации вышеозвученной задачи была принята следующая архитектура определения права на объект:

1. Если пользователь, вызывающий объект, является его владельцем, т.е. его логин совпадает с полем owner, указанным в таблице objects, тогда пользователю назначаются права владельца объекта, т.е. значение поля orights таблицы objects. Иначе идем в п.2.

2. Пользователю устанавливается право по умолчанию - значение поля arights таблицы objects для данного объекта.

Принято 3 категории прав:

Таблица 2.3. Классификация прав на объекты.

Для вышеозвученной политики был реализован пакет SECURITYDATA. Его спецификация:

SQL> CREATE OR REPLACE PACKAGE SECURITYDATA

2 AS

3 FUNCTION checkRights(idObject NUMBER) RETURN NUMBER;

4 FUNCTION checkRights(idObject NUMBER, oright NUMBER, owner VARCHAR2, aright NUMBER) RETURN NUMBER;

5 END SECURITYDATA;

6 /

Package created

Таблица 2.4. Комментарии к заголовку пакета SECURITYDATA.

Для отображения на клиенте необходимо данные о правах из таблиц объектов, определяющих политику тщательного контроля доступа рис.2.2. представить в виде XML-документа. Для этого существует пакет Datamodification, его листинг приведен в приложении 1. Выходной документ должен содержать информацию о владельце, правах владельца, пользовательских группах в которые входит вызывающий, права пользовательских групп на этот объект и права пользовательских групп по умолчанию, а также право по умолчанию - т.е. для пользователей, которые не являются владельцами и не входят в пользовательские группы. Соответственно этому приложению выводится следующий XML-документ:

<object_rights ID_OBJECT='33760'>

<owner>VMG</owner>

<owner_right>2</owner_right>

<others_right>1</others_right>

</object_rights>

Верхний тег object_rights содержит один атрибут - это id объекта. В нем 2 вложенных элемента: owner, owner_rights. В теге owner указывается информация о владельце объекта, в теге owner_rights - права владельца на объект, в теге others_right - право по умолчанию - т.е. для пользователей, которые не являются владельцами и не входят в пользовательские группы.

2.2.5 Реализация аудита на изменение объектов

В процессе коммерческой эксплуатации приложения часто возникают такие моменты, когда необходимо просмотреть кто, когда и как изменил клиентские данные и если, возможно, то и восстановить их. Это требование было отражено в техническом задании к работе. Для реализации этого требования я ввел процедуру аудита пользовательских объектов (т.е. данных пользовательских таблиц). При изменении, добавлении или удалении объектов данные происходит соответствующая DML-операция в таблице objects и user*values(т.е. таблицах справочниках - userclobvalues, userdatevalues, userdictionaryvalues, usermultilangvalues, usernumbervalues, userstringvalues). Для логирования были написаны триггеры, срабатывающие на каждую DML-операцию вышеперечисленных таблиц. В теле таких триггеров в зависимости от типа DML-операции(INSERT, UPDATE, DELETE) и типа данных изменненого атрибута осуществляется соответствующая запись в целевую таблицу лога (userclobvalueslog, userdatevalueslog, userdictionaryvalueslog, usermultilangvalueslog, usernumbervalueslog, userstringvalueslog), а также производится запись в пользовательскую плоскую таблицу соответсвующего объектного типа с целью немедленного изменения данных, показываемых пользователям информационной системы.

В системе используется 6 типов данных: это Clob, Date, Dictionary, Multilang, Number, String. Метаинформация этих типов содержится в системной таблице datatypes. Соответствие типов данных приложения - нейтив типам СУБД Oracle приведено в таблице 2.4.

Таблица 2.4. Соответствие типов данных приложения - нейтив типам СУБД Oracle

Таблицы логов, т.е. user*valueslog по своей структуре близки друг к другу и дабы не повторяться можно рассмотреть таблицу Userstringvalues, остальные аналогичны ей по структуре. Итак:

SQL> desc userstringvalueslog

Name Type Nullable Default Comments

ID NUMBER

ID_OBJECT NUMBER Y

ID_ATTRIBUTE NUMBER Y

OLD_VALUE VARCHAR2(4000) Y

NEW_VALUE VARCHAR2(4000) Y

MODIFIED_USER VARCHAR2(50) Y user

MODIFIED_TIME DATE Y sysdate

TYPE_OF_OPERATION VARCHAR2(20)

Рис. 2.7. ER-диаграмма аудита таблицы Userstringvalues.

ER-диаграмма объектов аудита данной таблицы приведена на рис.2.7. Кратко опишу основные поля таблицы Userstringvalueslog.

1. ID - первичный ключ.

2. ID_OBJECT - foreign key на id таблицы objects. Определяет объект, на который ввелся аудит.

3. ID_ATTRIBUTE - foreign key на id таблицы attributes. Определяет атрибут объекта, на который ввелся аудит.

4. OLD_VALUE - старое значение атрибута.

5. NEW_VALUE - новое значение атрибута.

6. MODIFIED_USER - пользователь, изменивший атрибут.

7. MODIFIED_TIME - время изменения атрибута.

8. TYPE_OF_OPERATION - тип операции, т.е. INSERT, UPDATE или DELETE.

Как было упомянуто выше с целью логирования были написаны триггеры, срабатывающие на каждую DML-операцию вышеперечисленных таблиц. В теле таких триггеров в зависимости от типа DML-операции(INSERT, UPDATE, DELETE) и типа данных изменненого атрибута осуществляется соответствующая запись в целевую таблицу лога (userclobvalueslog, userdatevalueslog, userdictionaryvalueslog, usermultilangvalueslog, usernumbervalueslog, userstringvalueslog), а также производится запись в пользовательскую плоскую таблицу соответсвующего объектного типа с целью немедленного изменения данных, показываемых пользователям информационной системы. Приведу код триггера USERSTRINGVALUES_AUDIT, который осуществляет логирование строковых данных в таблицу USERSTRINGVALUESLOG:

SQL> CREATE OR REPLACE TRIGGER USERSTRINGVALUES_AUID

2 AFTER INSERT OR UPDATE OR DELETE ON USERSTRINGVALUES FOR EACH ROW

3 BEGIN

4 IF inserting THEN

5 INSERT INTO USERSTRINGVALUESLOG(ID, ID_OBJECT, ID_ATTRIBUTE, OLD_VALUE, NEW_VALUE, TYPE_OF_OPERATION)

6 VALUES(SequencesData.createId(), :NEW.IDOBJECT, :NEW.IDATTRIBUTE, :OLD.value, :NEW.value, 'INSERT');

7 ELSIF updating THEN

8 INSERT INTO USERSTRINGVALUESLOG(ID, ID_OBJECT, ID_ATTRIBUTE, OLD_VALUE, NEW_VALUE, TYPE_OF_OPERATION)

9 VALUES(SequencesData.createId(), :NEW.IDOBJECT, :NEW.IDATTRIBUTE, :OLD.value, :NEW.value, 'UPDATE');

10 ELSIF deleting THEN

11 INSERT INTO USERSTRINGVALUESLOG(ID, ID_OBJECT, ID_ATTRIBUTE, OLD_VALUE, NEW_VALUE, TYPE_OF_OPERATION)

12 VALUES(SequencesData.createId(), :NEW.IDOBJECT, :NEW.IDATTRIBUTE, :OLD.value, :NEW.value, 'DELETE');

13 END IF;

14 END;

15 /

Триггеры на остальные таблицы лога (userclobvalueslog, userdatevalueslog, userdictionaryvalueslog, usermultilangvalueslog, usernumbervalueslog) аналогичны триггеры USERSTRINGVALUES_AUDIT. На поля MODIFIED_TIME и MODIFIED_USER установлены значения по умолчанию, которые определены как sysdate и user соответственно.

Для отображения на клиенте необходимо данные из таблиц логирования представить в виде XML-документов. Для этого существует пакет Datamodification, его листинг приведен в приложении 1. Аудит проводится по 3 параметрам: по атрибуту, по объекту и объектному типу. Соответственно этому приложению выводится 3 типа XML-документов. Например, для аудита выходной документ будет таким:

<attribute_summary ID='643968'>

<transaction MODIFIED_TIME='19-11-2009 19:41:56' MODIFIED_USER='VMG'>

<attribute ID_ATTRIBUTE='643968' TYPE_OF_OPERATION='INSERT' ATTRIBUTE_NOTE='имя' ATTRIBUTE_DATATYPE='MULTILANG'>

<old_value_attribute></old_value_attribute>

<new_value_attribute>1</new_value_attribute>

</attribute>

</transaction>

</attribute_summary>

Верхний тег attribute_summary содержит один атрибут - это id атрибута. В нем вложенный элемент transaction, в котором 2 атрибута: MODIFIED_TIME - время транзакции, MODIFIED_USER - пользователь, осуществивший транзакцию. В элементе transaction - есть вложенный элемент attribute, который содержит 4 атрибута: ID_ATTRIBUTE - id атрибута, TYPE_OF_OPERATION - тип совершенной операции, ATTRIBUTE_NOTE - имя атрибута, ATTRIBUTE_DATATYPE - тип данных атрибута, а также содержит 2 вложенных элемента - old_value_attribute(определяет значение этого атрибута до транзакции) и new_value_attribute(определяет значение этого атрибута после транзакции).

Структура XML для объекта и объектного типа будет другой, более сложной. Так как объект представляет собой совокупность атрибутов - то нужно собрать аудит транзакций всех атрибутов объекта и представить в виде вложенных тегов XML-документа. Таким образом, будет добавлен верхний тег object_summary, содержащий id объекта, а в нем будет коллекция XML-элементов аудита по всем атрибутам данного объекта. Пример такого документа:

<object_summary ID='51994'>

<transaction MODIFIED_TIME='19-11-2009 19:36:56' MODIFIED_USER='VMG'>

<attribute ID_ATTRIBUTE='643968' TYPE_OF_OPERATION='UPDATE' ATTRIBUTE_NOTE='Name' ATTRIBUTE_DATATYPE='MULTILANG'>

<old_value_attribute>9</old_value_attribute>

<new_value_attribute>12</new_value_attribute>

</attribute>

</transaction>

<transaction MODIFIED_TIME='19-11-2009 18:12:35' MODIFIED_USER='VMG'>

<attribute ID_ATTRIBUTE='643968' TYPE_OF_OPERATION='UPDATE' ATTRIBUTE_NOTE='Name' ATTRIBUTE_DATATYPE='MULTILANG'>

<old_value_attribute>7</old_value_attribute>

<new_value_attribute>9</new_value_attribute>

</attribute>

</transaction>

</object_summary>

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

<object_type IDOBJECTTYPE='1'>

<objects_summary>

<transaction MODIFIED_TIME='19-11-2009 19:36:56' MODIFIED_USER='VMG'>

<object_info object_id='51994'>

<attribute ID_ATTRIBUTE='643968' TYPE_OF_OPERATION='UPDATE' ATTRIBUTE_NOTE='Name' ATTRIBUTE_DATATYPE='MULTILANG'>

<old_value_attribute>9</old_value_attribute>

<new_value_attribute>12</new_value_attribute>

</attribute>

</object_info>

</transaction>

<transaction MODIFIED_TIME='19-11-2009 18:12:35' MODIFIED_USER='VMG'>

<object_info object_id='51994'>

<attribute ID_ATTRIBUTE='643968' TYPE_OF_OPERATION='UPDATE' ATTRIBUTE_NOTE='Name' ATTRIBUTE_DATATYPE='MULTILANG'>

<old_value_attribute>7</old_value_attribute>

<new_value_attribute>9</new_value_attribute>

</attribute>

</object_info>

</transaction>

</objects_summary>

</object_type>

Так как объект представляет собой совокупность атрибутов - то нужно собрать аудит транзакций всех атрибутов объекта и представить в виде вложенных тегов XML-документа. Таким образом, будет добавлен верхний тег object_type, содержащий id объектного типа, а в нем будет находиться коллекция XML-элементов аудита по всем объектам данного объектного типа.

2.2.6 Реализация механизма поиска объектов

Поисковый механизм должен осуществлять поиск по объектам по определенным заранее неизвестным параметрам, задаваемым пользователями во время их работы. Так, например, пользователь захочет найти все фотодокументы выпущенные после 1930 до 1950 года, в Северо-Западном регионе или найти рукописи в которых есть определенное слово и т.д. Как видно, здесь будет осуществляться поиск по атрибутам с определенными критериями. Эти критерии описаны в таблице conditions. Ее структура:

1. ID - первичный ключ.

2. Смысловое описание параметра поиска.

На данный момент в таблице conditions заданы следующие параметры поиска:

Таблица 2.5. Возможные поисковые параметры.

Понятно, что не всем атрибутам должны быть доступны все параметры поиска. По строковым атрибутам скорее всего будет осуществляться поиск по параметрам: “Содержит слово”, “Не содержит слово”, “Слово начинается” и т.д., однако не будет поиска по параметрам “Больше”или “Меньше или равно”. И для числовых типов должен осуществляться поиск по по параметрам “Больше”или “Меньше или равно”, но не будет осущестляться поиск по параметрам “Содержит слово”,“Не содержит слово”, “Слово начинается”. Эти соответствия задаются в таблице Conditiondatatypes, имеющей следующую структуру:

1. ID - первичный ключ.

2. IDATTRIBUTE - атрибут для которого задается соответствие.

3. IDCONDITION - поисковый параметр доступный для атрибута.

4. FDEFAULT - поисковый параметр для атрибута по умолчанию.

Таким образом, ER-диаграмма объектов, определяющих параметры поиска, приведена на рис. 2.8. Структура и назначение таблиц Attributes и Datatypes приведены выше, поэтому здесь их описывать смысла нету.

Рис. 2.8. ER-диаграмма объектов, определяющих параметры поиска.

Выбранные поисковые атрибуты необходимо где-то сохранить, чтобы в процессе поиска искать объекты, удовлетворяющие этим поисковым параметрам. С этой целью была создана временная таблица на время транзакции (т.е. использовалась конструкция temporary table SEARCHPARAMETERS on commit delete rows -которая позволяет создавать временные таблицы на уровне транзакции, так что при COMMIT или ROLLBACK данные из этой таблицы удаляются[5]). Таблица SEARCHPARAMETERS имеет следующую структуру:

1. ID поискового параметра - первичный ключ.

2. IDSEARCHOBJECTTYPE - id объектного типа, по которому осуществляется поиск.

3. IDATTRIBUTE - id атрибута, для которого задается поисковый параметр. Foreign Key для таблицы Attributes.

4. IDCONDITION - id поискового атрибута для параметра. Foreign Key для таблицы Conditions.

5. IDLANGUAGE - id языка, по которому будет осуществляться поиск. Foreign Key для таблицы Languages.

6. VALUE - значение поискового параметра(так если будет производиться поиск по параметру “Слово начинается с “утре”” - то значение VALUE будет “утре”, если по параметру “Больше 20” - то значение VALUE будет “20” и т.д.).

После выбора и инициализации поисковых параметров, а значит заполнения полей таблицы SEARCHPARAMETERS будет произведен поиск по объектам объектного типа поля IDSEARCHOBJECTTYPE. Поиск осуществляется с помощью пакета SearchData - его листинг в приложении 1.

Рис. 2.9. ER-диаграмма объектов, осуществляющие поисковый механизм.

Найденные значения вставляются во временную таблицу TEMPFOUNDOBJECTS. Она аналогично таблице SEARCHPARAMETERS является временной на уровне транзакции (т.е. с использованием конструкции on commit delete rows). Таблица TEMPFOUNDOBJECTS имеет следующую структуру:

1. ID - это ID найденного объекта.

2. OBJECTSERIAL - это серийный номер найденного объекта, задает порядок вывода найденный объектов.

Учитывая вышесказанное, ER-диаграмма объектов, осуществляющие поисковый механизм, будет следующей (см. рис. 2.9.).

2.3 Модель пользовательских данных

2.3.1 Общая архитектура пользовательских объектов БД

Общая архитектура пользовательских объектов БД приведена на рис. 2.10.

Рис. 2.10. Общая архитектура пользовательских объектов БД.

Архитектура пользовательских объектов БД состоит из 3 основных частей:

1. Объектов определяющих хранение фотодокументов архива.

2. Объектов определяющих хранение фонодокументов архива.

3. Всех остальных пользовательских объектов.

Подробное описание каждой из частей архитектуры пользовательских объектов БД привожу ниже.

2.3.2 ER-диаграмма фотодокументов архива

Один из основных типов документов, хранимых в БД являются фотодокументы. ER-диаграмма определяющая хранимые в БД фотодокументы приведена на рис. 2.11. Кратко опишу структуру входящих в эту ER-диаграмму объектов БД:

Рис. 2.11. ER-диаграмма фотодокументов архива.

Таблица Photodocuments определяет хранящиеся в БД фотодокументы архива. В таблице есть следующий колонки:

1. ID - первичный ключ. Определяет id фотодокумента, является внешним ключом к id таблицы objects.

2. SHOOTAUTHOR - Автор съемки. Существуют 3 колонки SHOOTAUTHOR: SHOOTAUTHOR1, SHOOTAUTHOR2, SHOOTAUTHOR3 соответственно для хранения инициалов автора съемки на русском, английском и немецком языках.

3. DEED - id акта приема-передачи фотодокумента на хранение.

4. ANNOTATION - аннотация.

5. KIND - определяет id вида фотодокумента.

6. SHOOTDATE - Дата съемки.

7. IMAGE - само изображение.

8. NUMBERNEGATIVES - количество негативов.

9. NOTESHOOTPLACE - Комментарий к месту съемки.

10. INSURANCECODE - Номер страхового договора на фотодокумент архива.

11. NOTE - Примечание.

12. DOCSIZE - id размера фотодокумента.

13. COMPILER - id cоставителя записи.

14. CODE -шифр фотодокумента.

Таблица SIZES определяет размеры документов, хранящиеся в БД. ЕЕ структура:

1. ID - первичный ключ.

2. NAME1 - размер документа на русском языке.

3. NAME2 - размер документа на английском языке.

4. NAME3 - размер документа на немецком языке.

Таблица STAFF определяет сотрудников, работающих в архиве. Структура таблицы:

1. ID - первичный ключ.

2. NAME1 - ФИО сотрудника на русском языке.

3. NAME2 - ФИО сотрудника на английском языке.

4. NAME3 - ФИО сотрудника на немецком языке.

5. AKKNAME1 - размер документа на русском языке.

6. AKKNAME2 - размер документа на английском языке.

7. AKKNAME3 - размер документа на немецком языке.

8. POSITION - id должности, занимаемой сотрудником.

Таблица Positions определяет должности, которые занимают сотрудники архива. Структура таблицы:

1. ID - первичный ключ.

2. NAME1 - Название должности на русском языке

3. NAME2 - Название должности на английском языке

4. NAME3 - Название должности на немецком языке.

Таблица ReceivingDeeds определяет акты приема-передачи. Структура таблицы:

1. ID - первичный ключ.

2. DEEDNUMBER - номер акта.

3. DEEDDATE - дата оформления акта.

4. INSPECTOR - id приемщика акта.

Таблица Inspectors определяет приемщиков актов. Структура таблицы:

1. ID - первичный ключ.

2. NAME1 - ФИО приемщика на русском языке.

3. NAME2 - ФИО приемщика на английском языке.

4. NAME3 - ФИО приемщика на немецком языке.

2.3.3 ER-диаграмма фонодокументов архива

Один из основных типов документов, хранимых в БД являются фонодокументы. ER-диаграмма определяющая хранимые в БД фонодокументы приведена на рис. 2.12. Кратко опишу структуру входящих в эту ER-диаграмму объектов БД:

Таблица Phonodocuments определяет хранящиеся в БД фонодокументы архива. В таблице есть следующий колонки:

1. ID - первичный ключ. Определяет id фонодокумента, является внешним ключом к id таблицы objects.

2. DEED - id акта приема-передачи фотодокумента на хранение.

3. CODE -шифр фотодокумента. Тип атрибута MULTILANG - т.е. значение шифра одинаково на всех языках, поддерживаемых БД.

4. NAME1 - Наименование фонозаписи на русском языке.

5. NAME2 - Наименование фонозаписи на английском языке.

6. NAME3 - Наименование фонозаписи на немецком языках.

7. TYPE - тип фонозаписи, внешний ключ к таблице MUSICTYPES.

8. CREATIONDATE - дата создания фонозаписи.

9. CREATIONPLACE - место создания фонозаписи, внешний ключ к таблице MUSICPRODUCTIONPLACES.

10. SHOOTAUTHOR - Автор съемки. Существуют 3 колонки SHOOTAUTHOR: SHOOTAUTHOR1, SHOOTAUTHOR2, SHOOTAUTHOR3 соответственно для хранения инициалов автора съемки на русском, английском и немецком языках.

11. NOTE1 - Примечание к фонодокументу на русском языке.

12. NOTE2 - Примечание к фонодокументу на английском языке.

13. NOTE3 - Примечание к фонодокументу на немецком языке.

14. COMPILER - составитель фонозаписи, внешний ключ к таблице STAFF.

15. COMPILEDATE - дата составления фонозаписи.

Рис.2.12. ER-диаграмма хранения фонодокументов архива.

Таблица MUSICTYPES определяет типы фонодокументов, хранимых в БД. Ее структура:

1. ID - первичный ключ.

2. NAME1 - Наименование типа фонодокумента на русском языке.

3. NAME2 - Наименование типа фонодокумента на английском языке.

4. NAME3 - Наименование типа фонодокумента на немецком языке.

Таблица MUSICPRODUCTIONPLACES определяет места издания фонодокументов, хранимых в БД. Ее структура:

1. ID - первичный ключ.

2. NAME1 - Наименование места издания фонодокумента на русском языке.

3. NAME2 - Наименование места издания фонодокумента на английском языке.

4. NAME3 - Наименование места издания фонодокумента на немецком языке.

Таблицы Insectors, Receivingdeeds, Staff и Positions описаны выше в разделе 2.7.1. ER-диаграмма фотодокументов архива.

2.3.4 ER-диаграмма остальных пользовательских объектов

Кроме фонодокументов и фотодокументов в БД хранятся новости, информация о выставках архива и веб-страницы сайта архива. ER-диаграмма вышеперечисленных пользовательских данных приведена на рис. 2.13. Кратко опишу структуру входящих в эту ER-диаграмму объектов БД:

Таблица EXHIBITIONS определяет информацию о проведенных или планируемых к проведению выставках. Ее структура:

1. ID - первичный ключ.

2. NAME1 - Наименование выставки на русском языке.

3. NAME2 - Наименование выставки на английском языке.

4. NAME3 - Наименование выставки на немецком языке.

5. NOTE1 - Примечание о проводимой выставке на русском языке.

6. NOTE1 - Примечание о проводимой выставке на английском языке.

7. NOTE1 - Примечание о проводимой выставке на немецком языке.

8. COMPILEDATE - дата составления записи. На значение поля установлено значение по умолчанию в виде текущего времени и даты.

Таблица NEWS содержит информацию о новостях архива, публикуемых на сайте для посетителей архива. Ее структура:

1. ID - первичный ключ.

2. HEADER1 - Заголовок новости на сайте на русском языке.

3. HEADER2 - Заголовок новости на сайте на английском языке.

4. HEADER3 - Заголовок новости на сайте немецком языке.

5. CONTENT1 - Содержимое статьи о новости на сайте на русском языке.

6. CONTENT2 - Содержимое статьи о новости на сайте на английском языке.

7. CONTENT3 - Содержимое статьи о новости на сайте на немецком языке.

8. COMPILEDATE - дата составления новости. На значение поля установлено значение по умолчанию в виде текущего времени и даты.

Рис. 2.13. ER-диаграмма объектов БД, определящих хранимую информацию о выставках, новостях и вебстраницах веб-сайта.

Таблица WEBPAGES содержит информацию о веб-страницах сайта архива. Ее структура:

1. ID - первичный ключ.

2. NAME1 - Наименование веб-страницы на русском языке.

3. NAME2 - Наименование веб-страницы на английском языке.

4. NAME3 - Наименование веб-страницы на немецком языке.

5. CONTENT1 - Информация о содержимом веб-сайта на русском языке.

6. CONTENT2 - Информация о содержимом веб-сайта на английском языке.

7. CONTENT3 - Информация о содержимом веб-сайта на немецком языке.

2.3.5 Представления пользователя приложения

Перейдем теперь к рассмотрению работы политики тщательного контроля доступа к объектам. Напомню, что в 7 пункте списка исходных требований сказано, что в БД необходимо разработать политику прав доступа к объектам в зависимости от пользователя. Политика безопасности должна обеспечивать разные права на объекты для владельцев объекта, для пользовательских групп и для пользователей, которые не являются ни владельцами ни входят в пользовательские группы.

Важно отметить, что пользователь информационной системы, подключаясь к БД из клиентского приложения не имеет гранта на просмотр пользовательских таблиц, он имеет доступ только к представлениям, которые сгенерированы на пользовательских таблицах. Это сделано для функционирования политики тщательного контроля доступа к объектам. Поясню: создается представление к пользовательской таблице с фильтрацией по функции checkrights пакета SECURITYDATA, которому передается в качестве параметра id объекта, для которого нужно определить доступ. Функция возвращает право пользователя на данный объект. Например, для таблицы NEWS будет создано представление NEWS_VIEW1(см. рис.2.14.).

Рис. 2.14. Представления пользователя приложения, определяющие хранимые в БД новости.

Код представления будет таков:

SQL> CREATE OR REPLACE VIEW NEWS_VIEW1

2 AS SELECT ID, HEADER1, BRIEFDESCRIPTION1, FULLDESCRIPTION1, COMPILEDATE

3 FROM NEWS

4 WHERE SECURITYDATA.checkRights(ID)>0

View created

Стоит обратить внимание что, в названии представления последним символом указана цифра1 - это обозначение языка системы, для которого представление сгенерировано. Т.к. в БД должна быть реализована поддержка 3 языков - то и представлений будет 3(см. таб.2.6.)

Таблица 2.6. Соответствие названия представления языку приложения.

Код представления соответственно для английского языка БД будет таков:

SQL> CREATE OR REPLACE VIEW NEWS_VIEW2

2 AS SELECT ID, HEADER1, BRIEFDESCRIPTION2, FULLDESCRIPTION2, COMPILEDATE

3 FROM NEWS

4 WHERE SECURITYDATA.checkRights(ID)>0

View created

Код представления соответственно для немецкого языка БД будет таков:

SQL> CREATE OR REPLACE VIEW NEWS_VIEW3

2 AS SELECT ID, HEADER3, BRIEFDESCRIPTION3, FULLDESCRIPTION3, COMPILEDATE

3 FROM NEWS

4 WHERE SECURITYDATA.checkRights(ID)>0

View created

Логика определения права пользователя на объект описана выше в главе “Реализация функциональности тщательного контроля доступа на уровне объектов.

Представления фонодокументов

Аналогично новостям для фонодокументов тоже будут сгенерированы 3 представления: Phonodocuments_View1, Phonodocuments _View2, Phonodocuments _View3(см.рис. 2.15.)

Рис.2.15. Представления пользователя приложения, определяющие хранимые в БД фонодокументы.

Представления фотодокументов

Для фотодокументов будут сгенерированы 3 представления: Photodocuments_View1, Photodocuments _View2, Photodocuments _View3(см.рис.2.16.)

Рис. 2.16. Представления пользователя приложения, определяющие хранимые в БД фотодокументы.

Представления веб-страниц.

Аналогично новостям для веб-страниц тоже будут сгенерированы 3 представления: Webpages_View1, Webpages_View2, Webpages_View3(см. рис.2.17.)

Рис. 2.17. Представления пользователя приложения, определяющие хранимые в БД веб-страницы.

Представления выставок.

Для выстовок будут сгенерированы 3 представления: Exhibitions_View1, Exhibitions_View2, Exhibitions _View3(см.рис.2.18.)

Рис. 2.18. Представления пользователя приложения, определяющие хранимые в БД выставки.

3. Апробация функционирования БД

3.1 Описание работы БД как основной части информационной системы

При загрузке информационной системы загружается окно логирования, в котором предлагается пройти процедуру аутентификации. В этом окне необходимо ввести логин и пароль. Введенные данные будут проверены на соответствие пары логин/пароль учетной записи в базе данных. При неуспешной процедуре аутентификации данное окно появится снова с запросом логина/пароля. В случае успешной авторизации появится доступ к интерфейсу информационной системы. В этом интерфейсе будет предоставлена возможность выбора объектного типа, с которым пользователь хочет работать (см. рис.3.1.). В проектируемой информационной системе пользователю следующие объектные типы: Акты приема-передачи, веб-страницы, новости, фотодокументы, фонодокументы.

Рис 3.1. Внешний вид интерфейса информационной системы.

Выбор объектного типа.

В клиентском приложении согласно техническому заданию должен быть реализован вывод объектов как по определенным параметрам так и всех объектов данного объектного типа. Выбор вида вывода объектов задается соответствующим списком (см. рис.3.2.).

Рис. 3.2. Выбор механизма показа объектов (все объекты или только те, что удовлетворяют определенным критериям)

В случае выбора меню список будет осуществлен вывод в виде списка всех объектов выбранного объектного типа. Если же пользователь выбрал вывод объектов в виде параметра поиска - то ему будет предложено набрать поисковую форму, в которой он должен выбрать согласно алгоритму изложенному в главе “Реализация механизма поиска” атрибуты объекта, по которым ввести поиск (см. рис.3.3.) и задать поисковые условия, по которым осуществлять поиск по атрибутам объектов (см. рис. 3.4.). В этой форме фактически задаются поля таблицы SearchParameters описанной выше.

Рис. 3.3. Выбор атрибутов по которым искать объекты.

Рис. 3.4. Выбор поисковых условий по которым искать объекты.

После указания атрибутов и поисковых условий необходимо определить шаблон значения атрибута по которому ввести поиск (см. рис. 3.5.). Значение поискового параметра (так если будет производиться поиск по параметру “Слово начинается с “утре”” - то значение атрибута в данной поисковой форме (которое задаст значение поля VALUE таблицы SearchParameters) будет “утре”, если по параметру “Больше 20” - то значение VALUE будет “20” и т.д.).

Рис. 3.5. Выбор поисковых параметров.

После задания поисковых параметров и выбора атрибутов, по которым ввести поиск, будет произведен поиск объектов и осуществлен вывод всех объектов, которые удовлетворяют условиям поиска (см. рис. 3.6.). Вывод объектов в виде списка будет осуществлен в форме такого же интерфейса.

Рис. 3.6. Выбор механизма показа объектов(все объекты или только те что удовлетворяют определенным критериям)

Также в техническом задании необходимо обеспечить поддержку как минимум 2 языков: английского и русского. Для переключения языка пользовательского интерфейса сделано соответствующий список (см. рис. 3.7.), который определит idlanguage таблиц objects, attributes и справочников, по которым будет ввестись работа информационной системы.

Рис. 3.7. Меню выбора языка информационной системы.

После вывода объектов в виде списка пользователю должна быть предоставлена возможность редактирования объекта. Алгоритм редактирования объекта описан выше в главе “Реализация функциональности добавления, редактирования и удаления объектов”.

Здесь я привожу (см. рис. 3.8.) вид пользовательского интерфейса при редактировании произвольного объекта, например, объекта новость с заголовком “Выставки петербурга конец XIX - начало ХХ века” объектного типа новости. В этой форме с помощью * пользователь определяет обязательные для заполнения атрибуты объекта. Если они остаются незаполненными то при сохранении объекта, система выведет информационное окно с сообщением об ошибке.

Рис. 3.8. Редактирование объекта новость с заголовком “Выставки петербурга конец XIX - начало ХХ века” объектного типа новости.

Стоит отдельно упоминуть про аудит изменения пользовательских данных. На данный момент со стороны клиентского приложения поддержки нету, аудит осуществляется целиком со стороны БД. Восстановление потерянных данных осуществляется программистом БД. Приведу типичный пример.

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

SELECT briefdescrription1 FROM news WHERE id = 1292983;

Запрос вернет следующее:

'Июль - традиционная пора всякого рода заплывов в нашем городе. Пусть даже вода в Неве не слишком теплая, но теплее уже не станет, А плавать хочется'.

А затем обновил поле briefdescrription1 следующим образом:

UPDATE news

SET briefdescrription1 = to_clob(''Июль, август - традиционная пора всякого рода заплывов в нашем городе. Пусть даже вода в Неве не слишком теплая, но теплее уже не станет, А плавать хочется.'')

WHERE id = 1292983;

COMMIT;

Соответственно вышеупомянутый запрос вернет следующее:

'Июль, август - традиционная пора всякого рода заплывов в нашем городе. Пусть даже вода в Неве не слишком теплая, но теплее уже не станет, А плавать хочется'.

Cодержимое данного поля изменилось. Была произведена DML-операция над таблицей USERCLOBVALUES, а значит сработал триггер USERCLOBVALUES_AUDIT, который зафиксирует изменения в таблице USERCLOBVALUESLOG.

Пользователь системы замечает логическую ошибку в данных и хочет вернуть предыдущее значение. Для этого программист БД осуществляет запрос к логу clob данных:

SELECT ID, ID_OBJECT, ID_ATTRIBUTE, OLD_VALUE, NEW_VALUE, MODIFIED_USER, MODIFIED_TIME, TYPE_OF_OPERATION

FROM USERCLOBVALUESLOG

WHERE OLD_VALUE = to_clob('Июль - традиционная пора всякого рода заплывов в нашем городе. Пусть даже вода в Неве не слишком теплая, но теплее уже не станет, А плавать хочется.');

На данный запрос из БД вернутся следующие данные (см. рис.3.9.):

Рис.3.9. Возвращаемые данные из ДБ при запросе в аудит.

На основании этих данных можно осуществить DML-операцию, которая восстановит пользовательские данные. Ее код будет следующим:

UPDATE news

SET briefdescrription1 = to_clob(''Июль - традиционная пора всякого рода заплывов в нашем городе. Пусть даже вода в Неве не слишком теплая, но теплее уже не станет, А плавать хочется.'')

WHERE id = 1292983;

COMMIT;

Тогда исходный запрос к данному объекту новости вернет иходные данные:

SELECT briefdescrription1 FROM news WHERE id = 1292983;

'Июль - традиционная пора всякого рода заплывов в нашем городе. Пусть даже вода в Неве не слишком теплая, но теплее уже не станет, А плавать хочется'.

Перейдем теперь к рассмотрению работы политики тщательного контроля доступа к объектам. Напомню, в списке исходных требований 2 пунктом исходя из наличия лицензии на конкретный проект СУБД определена как Oracle версии - 10.2.0.3 Standard Edition. Кроме того, в 7 пункте списка сказано, что в БД необходимо разработать политику прав доступа к объектам в зависимости от пользователя. Политика безопасности должна обеспечивать разные права на объекты для владельцев объекта, для пользовательских групп и для пользователей, которые не являются ни владельцами ни входят в пользовательские группы. Продемонстрирую работу разработанного пакета SECURITYDATA.

Важно отметить, что пользователь информационной системы, подключаясь к БД из клиентского приложения не имеет гранта на просмотр пользовательских таблиц, он имеет доступ только к представлениям, которые сгенерированы на пользовательских таблицах. Это сделано для функционирования политики тщательного контроля доступа к объектам. Поясню: создается представление к пользовательской таблице с фильтрацией по функции checkrights пакета SECURITYDATA, которому передается в качестве параметра id объекта, для которого нужно определить доступ. Функция возвращает право пользователя на данный объект. Например, для таблицы NEWS будет создано представление NEWS_VIEW. Код представления будет таков:

SQL> CREATE OR REPLACE VIEW NEWS_VIEW

2 AS SELECT * FROM NEWS

3 WHERE SECURITYDATA.checkRights(ID)>0

View created

Логика определения права пользователя на объект описана выше в главе “Реализация функциональности тщательного контроля доступа на уровне объектов.

Итак, возьмем 2 произвольных объекта в таблице NEWS, скажем с id = 2000899675 и id = 2000899693:

SQL> select id from news where id = 2000899675 or id = 2000899693

ID

2000899675

2000899693

По умолчанию для этих объектов устанавливается владелец - пользователь, который его создал, право владельца принимается равным 2 (т.е. право на просмотр и редактирование объекта), а право для всех остальных пользователей тоже равным 2.

Проверим данные о владельце, праве владельца и праве всех остальных пользователей на данный объект:

SQL> COLUMN ID FORMAT A10

SQL> COLUMN owner FORMAT A10

SQL> COLUMN ORIGHTS FORMAT A7

SQL> COLUMN ARIGHTS FORMAT A7

SQL> select id, owner, orights, arights from objects where id = 2000899675 or id = 2000899693 or id = 2000899783;

ID OWNER ORIGHTS ARIGHTS

2000899675 PETROV 2 2

2000899693 PETROV 2 2

2000899783 PETROV 2 2

Таким образом, убеждаемся, что владелец всех 3 объектов - пользователь PETROV, владельцу дано право 2 на таблицу, всем остальным пользователям - тоже право 2. Изменим это следующим образом (см. табл. 3.1.):

Таблица 3.1.

В качестве всех остальных пользователей использую пользователя SIDOROV. Значение его прав определено в колонке RIGHTS OF SIDOROV. Код который осуществит конструкцию прав, указанной в табл.3.1., будет следующим:

SQL> update objects set orights = 0 where id = 2000899783;

SQL> update objects set arights = 0 where id = 2000899693;

SQL> select id, owner, orights, arights from objects where id = 2000899675 or id = 2000899693 or id = 2000899783;

ID OWNER ORIGHTS ARIGHTS

2000899675 PETROV 2 2

2000899693 PETROV 2 0

2000899783 PETROV 0 2

Соответственно доступ для пользователя Petrov будет следующим:

Connected to Oracle Database 10g Release 10.2.0.3.0

Connected as petrov

SQL> select id from NEWS_VIEW where id = 2000899675 or id = 2000899693 or id = 2000899783;

ID

2000899675

2000899793

Соответственно доступ для пользователя Sidorov будет следующим:

Connected to Oracle Database 10g Release 10.2.0.3.0

Connected as sidorov

SQL> select id from NEWS_VIEW where id = 2000899675 or id = 2000899693 or id = 2000899783;

ID

2000899675

2000899783

Теперь проверим право 1. Напомню, что пользователю с таким правом на объект дается только право на просмотр объекта, на редактирование такого права нет. Установим права для пользователя PETROV права на объекты как показано в таблице 3.2.

Таблица 3.2.

Соответственно доступ для пользователя Petrov будет следующим:

Connected to Oracle Database 10g Release 10.2.0.3.0

Connected as petrov

SQL> select id from NEWS_VIEW where id = 2000899675 or id = 1292983 or id = 2000899783;

ID

2000899675

2000899793

В приложении для пользователя Petrov на объект 1292983 будет установлено право 1, т.е. пользователь только сможет просматривать объект (см. рис.3.10.).

Рис.3.10. Просмотр объекта без права на редактирование объекта.

Если же в приложении для пользователя Petrov на объект 1292983 будет установлено право 2, то пользователь сможет просматривать и редактировать объект (см. рис.3.11.).

Connected to Oracle Database 10g Release 10.2.0.3.0

Connected as petrov

SQL> update objects set arights = 2 where id = 1292983;

SQL> select id, owner, orights, arights from objects where id = 1292983;

ID OWNER ORIGHTS ARIGHTS

1292983 PETROV 2 2

Рис.3.11. Просмотр объекта с правом на редактирование объекта.

3.2 Тестовые примеры (test cases)

Ввиду большого количества тестовых примеров проводимых в процесс разработки БД информационной системы, приведу в таблице 3.3. только наиболее интересные тест кейсы. Возможные приоритеты тест-кейсов в порядке убывания их статуса: Blocker, Critical, Major, Minor, Trivial.

Таблица 3.3. Test cases.

3.3 Сводная таблица тестирования (test log)

На основе данных этой таблицы таблицы 3.3. Test cases составлен test log. Данные тестлога приведены в таблице 3.4. На основании данных тестлога можно проследить примерный график разработки БД. В таблице если стоит Ok - то тогда тест-кейс успешно выполнен, иначе нет. Все тест-кейсы были выполнены, по итогам тест-лога видно, что БД разработана.

Таблица3.4. Test Log.

Заключение

B данной дипломной работе была спроектирована и разработана База Данных для информационной системы архива, в которой должны храниться и обрабатываться айдиофайлы, фотофайлы и текстовая информация, характеризуемая сильной разреженностью данных. В ходе разработки была предложена и выбрана в качестве модели БД - модель Entity-Attribute-Value. В данной работе был решен ряд интересных задач, а именно: реализация системы разграничения прав пользователей на объекты с помощью пользовательских представлений и пакета SecurityData. Кроме того, со стороны БД система обеспечена всей необходимой функциональностью: в частности, поисковым механизмом, поддержка логирования пользовательских данных и восстановления утерянных данных.

Реализованная БД позволяет модифицировать пользовательские объекты без блокировки системы. Напомню, что в СУБД Oracle при любой DDL операции производится окончание транзакции и блокировка всех таблиц, над которыми производится данная DDL операция. Эта проблема успешно обойдена использованием EAV-модели. К EAV-модели для ускорения работы выполнения запросов к пользовательским данным была добавлена обычная реляционная модель в виде плоских реляционных таблиц.

База данных осуществляет полноценную поддержку 3 языков (русский, английский и немецкий), а также безязыкового символьного типа, т.е. типа, значения которого уникально как для русской, так и английской и немецкой версии БД. Этот тип нужен, например, для шифра, который уникален и одинаков на любом языке. Кроме того, БД генерирует XML документы для клиентского приложения, т.к. пользовательскому GUI-интерфейсу информационной системы для динамического построения списков данных нужно возвращать динамически генерируемые XML-документы для их последующего вывода в клиентском приложении.

В целом, считаю, что поставленные задачи в списке исходных требований были выполнены, а дипломную работу - завершенной.

Список использованной литературы

1. Змеев О.А., Новиков Д.В., Моисеев А.Н. К вопросу проектирования уровня хранения в виде ООРБД // Вестник ТГУ. Приложение №1 (II), сентябрь 2002. Доклады IV Всероссийской конференции с международным участием «Новые информационные технологии в исследовании сложных структур», Томск, 10-13 сентября 2002. С. 363-367.

2. Ларман К. Применение UML и шаблонов проектирования. 2-е изд. М.: Изд. дом «Вильямс», 2002. 624 с.

3. Тенцер А. База данных - хранилище объектов // КомпьютерПресс. 2001. №8.

4. Войтиков К.Ю., Змеев О.А., Моисеев А.Н. Объектный подход к проблеме проектирова-ния подсистемы нормативно-справочной информации // Обработка данных и управление в сложных системах. Томск: Изд-во Том. ун-та, 2002. Вып. 4. С. 13-20.

5. Том Кайт. Oracle для профессионалов. Книга 1. Архитектура и основные особенности. Третье издание, переработанное и дополненное: Пер. с англ./ Том Кайт - СПб.: ООО “ДиаСофтЮП”, 2005 - С.312.

6. Дейт, К. Дж. Введение в системы баз данных = Introduction to Database Systems. -- 8-е изд. -- М.: “Вильямс”, 2006. -- 1328 с.

7. Томас Коннолли, Каролин Бегг Базы данных. Проектирование, реализация и сопровождение. Теория и практика = Database Systems: A Practical Approach to Design, Implementation, and Management Third Edition. -- 3-е изд. -- М. Вильямс”:, 2003. -- 1436 с.

8. Кузнецов Сергей Дмитриевич “Основы баз данных” -- 1-е изд. -- М.: «Интернет-университет информационных технологий - ИНТУИТ.ру», 2005. -- 488 с.

9. Дейт, К. Дж. Введение в системы баз данных, 8-е издание.: Пер. с англ. -- М.: Издательский дом 'Вильямс', 2005. - 103-104 с.

10. Дейт, К. Дж. Введение в системы баз данных, 8-е издание.: Пер. с англ. -- М.: Издательский дом 'Вильямс', 2005. - 108 с.

11. С.Д. Кузнецов. Основы современных баз данных, информационно-аналитические материалы Центра информационных материалов. Глава 22.

12. Дуглас Крамер. Платформа Java. Официальное издание. - JavaSoft, Sun Microsystems Inc. 5-7 c.

Приложение 1. Листинги пакетов

Листинг пакета DATAMODIFICATION

Пакет DATAMODIFICATION предназначен для вывода аудита по объекту, объектному типу или атрибуту в виде XML-документа, а также подсчета количества транзакций проводимых над атрибутом, объектом или объектным типом, вывода прав на объект в виде XML-документа. Вывод аудита в виде XML-документа возможен в постраничном виде - это необходимо для отображения большого количества транзакций в приложении. Код заголовка пакета и комментарии к заголовку пакета (см. таб.Пр.1.1.) ниже:

Таблица Пр.1.1. Комментарии к заголовку пакета DATAMODIFICATION.

SQL> create or replace package DATAMODIFICATION as

2

3 FUNCTION showAuditByAttribute(nIDAttribute attributes.id%TYPE, nIdLanguage NUMBER) RETURN XMLTYPE;

4

5 function showAuditByObject(nIDObject objects.id%TYPE, nIdLanguage NUMBER) RETURN XMLTYPE;

6 function showAuditByObjectType(nIDObjectType objecttypes.id%TYPE, nIdLanguage NUMBER) RETURN XMLTYPE;

7 function countTransactionsByAttribute(nIDAttribute attributes.id%TYPE, nIdLanguage NUMBER) RETURN NUMBER;

8 function countTransactionsByObject(nIDObject objects.id%TYPE, nIdLanguage NUMBER) RETURN NUMBER;

9 function countTransactionsByObjectType(nIDObjectType objecttypes.id%TYPE, nIdLanguage NUMBER) RETURN NUMBER;

10 FUNCTION pageAuditByAttribute(nIDAttribute attributes.id%TYPE, nIdLanguage NUMBER, nPageNumber NUMBER, nAmountPerPage NUMBER) RETURN XMLTYPE;

11 FUNCTION pageAuditByObject(nIDObject objects.id%TYPE, nIdLanguage NUMBER, nPageNumber NUMBER, nAmountPerPage NUMBER) RETURN XMLTYPE;

12 FUNCTION pageAuditByObjectType(nIDObjectType objecttypes.id%TYPE, nIdLanguage NUMBER, nPageNumber NUMBER, nAmountPerPage NUMBER) RETURN XMLTYPE;

13 function showObjectRights(nIDObject objects.id%TYPE) RETURN XMLTYPE;

14 FUNCTION showFoundObjectRights RETURN XMLTYPE;

15

16 end DATAMODIFICATION;

17 /

Тело пакета DATAMODIFICATION представлено ниже:

SQL> create or replace package body DATAMODIFICATION as

2

3 FUNCTION showAuditByAttribute(nIDAttribute attributes.id%TYPE, nIdLanguage NUMBER) RETURN XMLTYPE AS

4 nDataType attributes.iddatatype%TYPE;

5 xDocument XMLTYPE;

6 vName datatypes.name%TYPE;

7 BEGIN

8 SELECT idDataType INTO nDataType FROM attributes WHERE id = nIDAttribute;

9 SELECT name INTO vName FROM datatypes WHERE id = nDataType;

10 IF vName = 'MULTILANG' THEN

11

12 SELECT

13 XMLElement('attribute_summary', XMLAttributes(nIDAttribute AS id),

14 XMLAgg(XMLElement('transaction',

15 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

16 XMLAgg(

17 XMLConcat(

18 XMLElement('attribute',

19 XMLAttributes(

20 nIDAttribute AS id_attribute,

21 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

22 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

23 'MULTILANG' AS attribute_datatype

24),

25 XMLElement('old_value_attribute',A.old_value),

26 XMLElement('new_value_attribute',A.new_value)

27)

28)--XMLConcat

29)--XMLAgg

30)--XMLAttributes

31 order by A.modified_time desc

32)

33)

34 INTO xDocument

35 FROM (

36 SELECT a.*, uml.*, s.*, rownum r

37 FROM attributes a, usermultilangvalueslog uml, systemdatavalues s

38 WHERE a.id = nIDAttribute and uml.id_attribute = nIDAttribute AND uml.id_attribute = s.idparent AND s.idlanguage = nIdLanguage

39)A

40 GROUP BY A.modified_time, A.MODIFIED_USER;

41

42 RETURN xDocument;

43 ELSIF vName = 'VARCHAR2' THEN

44

45 SELECT

46 XMLElement('attribute_summary', XMLAttributes(nIDAttribute AS id),

47 XMLAgg(XMLElement('transaction',

48 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

49 XMLAgg(

50 XMLConcat(

51 XMLElement('attribute',

52 XMLAttributes(

53 nIDAttribute AS id_attribute,

54 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

55 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

56 'STRING' AS attribute_datatype

57),

58 XMLElement('old_value_attribute',A.old_value),

59 XMLElement('new_value_attribute',A.new_value)

60)

61)--XMLConcat

62)--XMLAgg

63)--XMLAttributes

64 order by A.modified_time desc

65)

66)

67 INTO xDocument

68 FROM (

69 SELECT a.*, usl.*, s.*, rownum r

70 FROM attributes a, userstringvalueslog usl, systemdatavalues s

71 WHERE a.id = nIDAttribute and usl.id_attribute = nIDAttribute AND usl.id_attribute = s.idparent AND s.idlanguage = 1

72)A

73 GROUP BY A.modified_time, A.MODIFIED_USER;

74

75 RETURN xDocument;

76

77 ELSIF vName = 'NUMBER' THEN

78

79 SELECT

80 XMLElement('attribute_summary', XMLAttributes(nIDAttribute AS id),

81 XMLAgg(XMLElement('transaction',

82 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

83 XMLAgg(

84 XMLConcat(

85 XMLElement('attribute',

86 XMLAttributes(

87 nIDAttribute AS id_attribute,

88 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

89 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

90 'NUMBER' AS attribute_datatype

91),

92 XMLElement('old_value_attribute',A.old_value),

93 XMLElement('new_value_attribute',A.new_value)

94)

95)--XMLConcat

96)--XMLAgg

97)--XMLAttributes

98 order by A.modified_time desc

99)

100)

101 INTO xDocument

102 FROM (

103 SELECT a.*, usl.*, s.*, rownum r

104 FROM attributes a, usernumbervalueslog usl, systemdatavalues s

105 WHERE a.id = nIDAttribute and usl.id_attribute = nIDAttribute AND usl.id_attribute = s.idparent AND s.idlanguage = 1

106)A

107 GROUP BY A.modified_time, A.MODIFIED_USER;

108

109 RETURN xDocument;

110

111 ELSIF vName = 'DATE' THEN

112

113 SELECT

114 XMLElement('attribute_summary', XMLAttributes(nIDAttribute AS id),

115 XMLAgg(XMLElement('transaction',

116 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

117 XMLAgg(

118 XMLConcat(

119 XMLElement('attribute',

120 XMLAttributes(

121 nIDAttribute AS id_attribute,

122 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

123 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

124 'DATE' AS attribute_datatype

125),

126 XMLElement('old_value_attribute',A.old_value),

127 XMLElement('new_value_attribute',A.new_value)

128)

129)--XMLConcat

130)--XMLAgg

131)--XMLAttributes

132 order by A.modified_time desc

133)

134)

135 INTO xDocument

136 FROM (

137 SELECT a.*, usl.*, s.*, rownum r

138 FROM attributes a, userdatevalueslog usl, systemdatavalues s

139 WHERE a.id = nIDAttribute and usl.id_attribute = nIDAttribute AND usl.id_attribute = s.idparent AND s.idlanguage = 1

140)A

141 GROUP BY A.modified_time, A.MODIFIED_USER;

142

143 RETURN xDocument;

144

145 ELSIF vName = 'CLOB' THEN

146

147 SELECT

148 XMLElement('attribute_summary', XMLAttributes(nIDAttribute AS id),

149 XMLAgg(XMLElement('transaction',

150 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

151 XMLAgg(

152 XMLConcat(

153 XMLElement('attribute',

154 XMLAttributes(

155 nIDAttribute AS id_attribute,

156 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

157 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

158 'CLOB' AS attribute_datatype

159),

160 XMLElement('old_value_attribute',A.old_value),

161 XMLElement('new_value_attribute',A.new_value)

162)

163)--XMLConcat

164)--XMLAgg

165)--XMLAttributes

166 order by A.modified_time desc

167)

168)

169 INTO xDocument

170 FROM (

171 SELECT a.*, usl.*, s.*, rownum r

172 FROM attributes a, userclobvalueslog usl, systemdatavalues s

173 WHERE a.id = nIDAttribute and usl.id_attribute = nIDAttribute AND usl.id_attribute = s.idparent AND s.idlanguage = 1

174)A

175 GROUP BY A.modified_time, A.MODIFIED_USER;

176

177 RETURN xDocument;

178

179 ELSIF vName = 'DICTIONARY' THEN

180

181 SELECT

182 XMLElement('attribute_summary', XMLAttributes(nIDAttribute AS id),

183 XMLAgg(XMLElement('transaction',

184 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

185 XMLAgg(

186 XMLConcat(

187 XMLElement('attribute',

188 XMLAttributes(

189 nIDAttribute AS id_attribute,

190 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

191 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

192 'DICTIONARY' AS attribute_datatype

193),

194 XMLElement('old_value_attribute',A.old_value),

195 XMLElement('new_value_attribute',A.new_value)

196)

197)--XMLConcat

198)--XMLAgg

199)--XMLAttributes

200 order by A.modified_time desc

201)

202)

203 INTO xDocument

204 FROM (

205 SELECT a.*, usl.*, s.*, rownum r

206 FROM attributes a, userdictionaryvalueslog usl, systemdatavalues s

207 WHERE a.id = nIDAttribute and usl.id_attribute = nIDAttribute AND usl.id_attribute = s.idparent AND s.idlanguage = 1

208)A

209 GROUP BY A.modified_time, A.MODIFIED_USER;

210

211 RETURN xDocument;

212

213 END IF;

214 RETURN NULL;

215 END showAuditByAttribute;

216

217 FUNCTION showAuditByObject(nIDObject objects.id%TYPE, nIdLanguage NUMBER) RETURN XMLTYPE AS

218 xDocument XMLTYPE;

219 BEGIN

220

221 SELECT

222 XMLElement('object_summary', XMLAttributes(nIDObject AS id),

223 XMLAgg(XMLElement('transaction',

224 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

225 XMLAgg(

226 XMLConcat(

227 XMLElement('attribute',

228 XMLAttributes(

229 A.new_idattribute AS id_attribute,

230 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

231 A.type_of_operation AS type_of_operation, A.attribute_note AS attribute_note,

232 A.attribute_datatype AS attribute_datatype, A.datatype_length AS datatype_length

233),

234 XMLElement('old_value_attribute',A.old_value),

235 XMLElement('new_value_attribute',A.new_value)

236),

237 XMLElement('object',

238 XMLAttributes(

239 A.OLD_IDPARENTOBJECT AS OLD_IDPARENTOBJECT,

240 A.NEW_IDPARENTOBJECT AS NEW_IDPARENTOBJECT,

241 A.OBJECTTYPE NEW_OBJECTTYPE,

242 A.OLD_OWNER OLD_OWNER,

243 A.NEW_OWNER NEW_OWNER,

244 A.OLD_ORIGHTS OLD_ORIGHTS,

245 A.NEW_ORIGHTS NEW_ORIGHTS,

246 A.OLD_ARIGHTS OLD_ARIGHTS,

247 A.NEW_ARIGHTS NEW_ARIGHTS,

248 A.OPERATION OPERATION,

249 A.OBJECTNOTE OBJECTNOTE

250)

251)

252)--XMLConcat

253)--XMLAgg

254)--XMLAttributes

255 order by A.modified_time desc

256)

257)

258 INTO xDocument

259 FROM (

260 --usermultilangvalueslog

261 SELECT uml.id_attribute AS new_idattribute,

262 to_clob(uml.old_value) AS old_value,

263 to_clob(uml.new_value) AS new_value,

264 uml.old_serialnumber AS old_serialnumber,

265 uml.new_serialnumber AS new_serialnumber,

266 uml.modified_user AS modified_user,

267 uml.modified_time AS modified_time,

268 uml.type_of_operation AS type_of_operation,

269 s.value AS attribute_note,

270 'MULTILANG' AS attribute_datatype,

271 a.length AS datatype_length,

272 null OLD_IDPARENTOBJECT,

273 null NEW_IDPARENTOBJECT,

274 null OBJECTTYPE,

275 null OLD_OWNER,

276 null NEW_OWNER,

277 null OLD_ORIGHTS,

278 null NEW_ORIGHTS,

279 null OLD_ARIGHTS,

280 null NEW_ARIGHTS,

281 null OPERATION,

282 null OBJECTNOTE

283 FROM objects o, attributes a, objecttypes ot, usermultilangvalueslog uml, systemdatavalues s

284 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

285 AND

286 (uml.id_object = o.id and uml.id_attribute = a.id)

287

288 UNION ALL

289 --userstringvalueslog

290 SELECT uml.id_attribute AS new_idattribute,

291 to_clob(uml.old_value) AS old_value,

292 to_clob(uml.new_value) AS new_value,

293 uml.old_serialnumber AS old_serialnumber,

294 uml.new_serialnumber AS new_serialnumber,

295 uml.modified_user AS modified_user,

296 uml.modified_time AS modified_time,

297 uml.type_of_operation AS type_of_operation,

298 s.value AS attribute_note,

299 'STRING' AS attribute_datatype,

300 a.length AS datatype_length,

301 null OLD_IDPARENTOBJECT,

302 null NEW_IDPARENTOBJECT,

303 null OBJECTTYPE,

304 null OLD_OWNER,

305 null NEW_OWNER,

306 null OLD_ORIGHTS,

307 null NEW_ORIGHTS,

308 null OLD_ARIGHTS,

309 null NEW_ARIGHTS,

310 null OPERATION,

311 null OBJECTNOTE

312 FROM objects o, attributes a, objecttypes ot, userstringvalueslog uml, systemdatavalues s

313 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

314 AND

315 (uml.id_object = o.id and uml.id_attribute = a.id)

316

317 UNION ALL

318 --userdatevalueslog

319 SELECT uml.id_attribute AS new_idattribute,

320 to_clob(uml.old_value) AS old_value,

321 to_clob(uml.new_value) AS new_value,

322 uml.old_serialnumber AS old_serialnumber,

323 uml.new_serialnumber AS new_serialnumber,

324 uml.modified_user AS modified_user,

325 uml.modified_time AS modified_time,

326 uml.type_of_operation AS type_of_operation,

327 s.value AS attribute_note,

328 'DATE' AS attribute_datatype,

329 a.length AS datatype_length,

330 null OLD_IDPARENTOBJECT,

331 null NEW_IDPARENTOBJECT,

332 null OBJECTTYPE,

333 null OLD_OWNER,

334 null NEW_OWNER,

335 null OLD_ORIGHTS,

336 null NEW_ORIGHTS,

337 null OLD_ARIGHTS,

338 null NEW_ARIGHTS,

339 null OPERATION,

340 null OBJECTNOTE

341 FROM objects o, attributes a, objecttypes ot, userdatevalueslog uml, systemdatavalues s

342 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

343 AND

344 (uml.id_object = o.id and uml.id_attribute = a.id)

345

346 UNION ALL

347 --userclobvalueslog

348 SELECT uml.id_attribute AS new_idattribute,

349 to_clob(uml.old_value) AS old_value,

350 to_clob(uml.new_value) AS new_value,

351 uml.old_serialnumber AS old_serialnumber,

352 uml.new_serialnumber AS new_serialnumber,

353 uml.modified_user AS modified_user,

354 uml.modified_time AS modified_time,

355 uml.type_of_operation AS type_of_operation,

356 s.value AS attribute_note,

357 'CLOB' AS attribute_datatype,

358 a.length AS datatype_length,

359 null OLD_IDPARENTOBJECT,

360 null NEW_IDPARENTOBJECT,

361 null OBJECTTYPE,

362 null OLD_OWNER,

363 null NEW_OWNER,

364 null OLD_ORIGHTS,

365 null NEW_ORIGHTS,

366 null OLD_ARIGHTS,

367 null NEW_ARIGHTS,

368 null OPERATION,

369 null OBJECTNOTE

370 FROM objects o, attributes a, objecttypes ot, userclobvalueslog uml, systemdatavalues s

371 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

372 AND

373 (uml.id_object = o.id and uml.id_attribute = a.id)

374

375 UNION ALL

376 --usernumbervalueslog

377 SELECT uml.id_attribute AS new_idattribute,

378 to_clob(uml.old_value) AS old_value,

379 to_clob(uml.new_value) AS new_value,

380 uml.old_serialnumber AS old_serialnumber,

381 uml.new_serialnumber AS new_serialnumber,

382 uml.modified_user AS modified_user,

383 uml.modified_time AS modified_time,

384 uml.type_of_operation AS type_of_operation,

385 s.value AS attribute_note,

386 'CLOB' AS attribute_datatype,

387 a.length AS datatype_length,

388 null OLD_IDPARENTOBJECT,

389 null OBJECTTYPE,

390 null NEW_OBJECTTYPE,

391 null OLD_OWNER,

392 null NEW_OWNER,

393 null OLD_ORIGHTS,

394 null NEW_ORIGHTS,

395 null OLD_ARIGHTS,

396 null NEW_ARIGHTS,

397 null OPERATION,

398 null OBJECTNOTE

399 FROM objects o, attributes a, objecttypes ot, usernumbervalueslog uml, systemdatavalues s

400 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

401 AND

402 (uml.id_object = o.id and uml.id_attribute = a.id)

403

404 UNION ALL

405 --userdictionaryvalueslog

406 SELECT uml.id_attribute AS new_idattribute,

407 to_clob(uml.old_value) AS old_value,

408 to_clob(uml.new_value) AS new_value,

409 uml.old_serialnumber AS old_serialnumber,

410 uml.new_serialnumber AS new_serialnumber,

411 uml.modified_user AS modified_user,

412 uml.modified_time AS modified_time,

413 uml.type_of_operation AS type_of_operation,

414 s.value AS attribute_note,

415 'CLOB' AS attribute_datatype,

416 a.length AS datatype_length,

417 null OLD_IDPARENTOBJECT,

418 null NEW_IDPARENTOBJECT,

419 null OBJECTTYPE,

420 null OLD_OWNER,

421 null NEW_OWNER,

422 null OLD_ORIGHTS,

423 null NEW_ORIGHTS,

424 null OLD_ARIGHTS,

425 null NEW_ARIGHTS,

426 null OPERATION,

427 null OBJECTNOTE

428 FROM objects o, attributes a, objecttypes ot, userdictionaryvalueslog uml, systemdatavalues s

429 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

430 AND

431 (uml.id_object = o.id and uml.id_attribute = a.id)

432

433 UNION ALL

434

435 SELECT null AS new_idattribute,

436 null AS old_value,

437 null AS new_value,

438 null AS old_serialnumber,

439 null AS new_serialnumber,

440 OL.MODIFIED_USER AS modified_user,

441 OL.MODIFIED_TIME AS modified_time,

442 null AS type_of_operation,

443 null AS attribute_note,

444 null AS attribute_datatype,

445 null AS datatype_length,

446 OL.OLD_IDPARENTOBJECT OLD_IDPARENTOBJECT,

447 OL.NEW_IDPARENTOBJECT NEW_IDPARENTOBJECT,

448 OL.NEW_OBJECTTYPE NEW_OBJECTTYPE,

449 OL.OLD_OWNER OLD_OWNER,

450 OL.NEW_OWNER NEW_OWNER,

451 OL.OLD_ORIGHTS OLD_ORIGHTS,

452 OL.NEW_ORIGHTS NEW_ORIGHTS,

453 OL.OLD_ARIGHTS OLD_ARIGHTS,

454 OL.NEW_ARIGHTS NEW_ARIGHTS,

455 OL.TYPE_OF_OPERATION OPERATION,

456 s.value OBJECTNOTE

457 FROM objectslog OL, systemdatavalues s

458 WHERE OL.ID_OBJECT = nIDObject and s.idparent = OL.ID_OBJECT AND S.IDLANGUAGE = nIdLanguage

459

460) A

461 GROUP BY A.modified_time, A.MODIFIED_USER;

462

463 RETURN xDocument;

464

465 END showAuditByObject;

466

467 function showAuditByObjectType(nIDObjectType objecttypes.id%TYPE, nIdLanguage NUMBER) RETURN XMLTYPE IS

468 xDocument XMLTYPE;

469 BEGIN

470

471 SELECT XMLElement('object_type',

472 XMLAttributes(1 AS idObjectType),

473 XMLElement('objects_summary',

474 XMLAgg(XMLElement('transaction',

475 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

476 XMLAgg(

477 XMLElement('object_info',

478 XMLAttributes(A.id AS 'object_id'),

479 XMLElement('attribute',

480 XMLAttributes(

481 A.new_idattribute AS id_attribute,

482 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

483 A.type_of_operation AS type_of_operation, A.attribute_note AS attribute_note,

484 A.attribute_datatype AS attribute_datatype, A.datatype_length AS datatype_length

485),

486 XMLElement('old_value_attribute',A.old_value),

487 XMLElement('new_value_attribute',A.new_value)

488),

489 XMLElement('object_change',

490 XMLAttributes(

491 A.OLD_IDPARENTOBJECT AS OLD_IDPARENTOBJECT,

492 A.NEW_IDPARENTOBJECT AS NEW_IDPARENTOBJECT,

493 A.OBJECTTYPE NEW_OBJECTTYPE,

494 A.OLD_OWNER OLD_OWNER,

495 A.NEW_OWNER NEW_OWNER,

496 A.OLD_ORIGHTS OLD_ORIGHTS,

497 A.NEW_ORIGHTS NEW_ORIGHTS,

498 A.OLD_ARIGHTS OLD_ARIGHTS,

499 A.NEW_ARIGHTS NEW_ARIGHTS,

500 A.OPERATION OPERATION,

501 A.OBJECTNOTE OBJECTNOTE

502)

503)

504)--XMLConcat

505)--XMLAgg

506)--XMLAttributes

507 order by A.modified_time desc

508)

509)

510)

511 INTO xDocument

512 --showAuditByObject(id, nIdLanguage)

513 --) AS objects)

514 FROM (

515 SELECT B.*, rownum r

516 FROM (

517 --usermultilangvalueslog

518 SELECT

519 o.id AS id,

520 uml.id_attribute AS new_idattribute,

521 to_clob(uml.old_value) AS old_value,

522 to_clob(uml.new_value) AS new_value,

523 uml.old_serialnumber AS old_serialnumber,

524 uml.new_serialnumber AS new_serialnumber,

525 uml.modified_user AS modified_user,

526 uml.modified_time AS modified_time,

527 uml.type_of_operation AS type_of_operation,

528 s.value AS attribute_note,

529 'MULTILANG' AS attribute_datatype,

530 a.length AS datatype_length,

531 null OLD_IDPARENTOBJECT,

532 null NEW_IDPARENTOBJECT,

533 null OBJECTTYPE,

534 null OLD_OWNER,

535 null NEW_OWNER,

536 null OLD_ORIGHTS,

537 null NEW_ORIGHTS,

538 null OLD_ARIGHTS,

539 null NEW_ARIGHTS,

540 null OPERATION,

541 null OBJECTNOTE

542 FROM objects o, attributes a, objecttypes ot, usermultilangvalueslog uml, systemdatavalues s

543 WHERE o.idobjecttype = nIDObjectType and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

544 AND

545 (uml.id_object = o.id and uml.id_attribute = a.id)

546

547 UNION ALL

548 --userstringvalueslog

549 SELECT

550 o.id AS id,

551 uml.id_attribute AS new_idattribute,

552 to_clob(uml.old_value) AS old_value,

553 to_clob(uml.new_value) AS new_value,

554 uml.old_serialnumber AS old_serialnumber,

555 uml.new_serialnumber AS new_serialnumber,

556 uml.modified_user AS modified_user,

557 uml.modified_time AS modified_time,

558 uml.type_of_operation AS type_of_operation,

559 s.value AS attribute_note,

560 'STRING' AS attribute_datatype,

561 a.length AS datatype_length,

562 null OLD_IDPARENTOBJECT,

563 null NEW_IDPARENTOBJECT,

564 null OBJECTTYPE,

565 null OLD_OWNER,

566 null NEW_OWNER,

567 null OLD_ORIGHTS,

568 null NEW_ORIGHTS,

569 null OLD_ARIGHTS,

570 null NEW_ARIGHTS,

571 null OPERATION,

572 null OBJECTNOTE

573 FROM objects o, attributes a, objecttypes ot, userstringvalueslog uml, systemdatavalues s

574 WHERE o.idobjecttype = nIDObjectType and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

575 AND

576 (uml.id_object = o.id and uml.id_attribute = a.id)

577

578 UNION ALL

579 --userdatevalueslog

580 SELECT

581 o.id AS id,

582 uml.id_attribute AS new_idattribute,

583 to_clob(uml.old_value) AS old_value,

584 to_clob(uml.new_value) AS new_value,

585 uml.old_serialnumber AS old_serialnumber,

586 uml.new_serialnumber AS new_serialnumber,

587 uml.modified_user AS modified_user,

588 uml.modified_time AS modified_time,

589 uml.type_of_operation AS type_of_operation,

590 s.value AS attribute_note,

591 'DATE' AS attribute_datatype,

592 a.length AS datatype_length,

593 null OLD_IDPARENTOBJECT,

594 null NEW_IDPARENTOBJECT,

595 null OBJECTTYPE,

596 null OLD_OWNER,

597 null NEW_OWNER,

598 null OLD_ORIGHTS,

599 null NEW_ORIGHTS,

600 null OLD_ARIGHTS,

601 null NEW_ARIGHTS,

602 null OPERATION,

603 null OBJECTNOTE

604 FROM objects o, attributes a, objecttypes ot, userdatevalueslog uml, systemdatavalues s

605 WHERE o.idobjecttype = nIDObjectType and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

606 AND

607 (uml.id_object = o.id and uml.id_attribute = a.id)

608

609 UNION ALL

610 --userclobvalueslog

611 SELECT

612 o.id AS id,

613 uml.id_attribute AS new_idattribute,

614 to_clob(uml.old_value) AS old_value,

615 to_clob(uml.new_value) AS new_value,

616 uml.old_serialnumber AS old_serialnumber,

617 uml.new_serialnumber AS new_serialnumber,

618 uml.modified_user AS modified_user,

619 uml.modified_time AS modified_time,

620 uml.type_of_operation AS type_of_operation,

621 s.value AS attribute_note,

622 'CLOB' AS attribute_datatype,

623 a.length AS datatype_length,

624 null OLD_IDPARENTOBJECT,

625 null NEW_IDPARENTOBJECT,

626 null OBJECTTYPE,

627 null OLD_OWNER,

628 null NEW_OWNER,

629 null OLD_ORIGHTS,

630 null NEW_ORIGHTS,

631 null OLD_ARIGHTS,

632 null NEW_ARIGHTS,

633 null OPERATION,

634 null OBJECTNOTE

635 FROM objects o, attributes a, objecttypes ot, userclobvalueslog uml, systemdatavalues s

636 WHERE o.idobjecttype = nIDObjectType and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

637 AND

638 (uml.id_object = o.id and uml.id_attribute = a.id)

639

640 UNION ALL

641 --usernumbervalueslog

642 SELECT

643 o.id AS id,

644 uml.id_attribute AS new_idattribute,

645 to_clob(uml.old_value) AS old_value,

646 to_clob(uml.new_value) AS new_value,

647 uml.old_serialnumber AS old_serialnumber,

648 uml.new_serialnumber AS new_serialnumber,

649 uml.modified_user AS modified_user,

650 uml.modified_time AS modified_time,

651 uml.type_of_operation AS type_of_operation,

652 s.value AS attribute_note,

653 'CLOB' AS attribute_datatype,

654 a.length AS datatype_length,

655 null OLD_IDPARENTOBJECT,

656 null OBJECTTYPE,

657 null NEW_OBJECTTYPE,

658 null OLD_OWNER,

659 null NEW_OWNER,

660 null OLD_ORIGHTS,

661 null NEW_ORIGHTS,

662 null OLD_ARIGHTS,

663 null NEW_ARIGHTS,

664 null OPERATION,

665 null OBJECTNOTE

666 FROM objects o, attributes a, objecttypes ot, usernumbervalueslog uml, systemdatavalues s

667 WHERE o.idobjecttype = nIDObjectType and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

668 AND

669 (uml.id_object = o.id and uml.id_attribute = a.id)

670

671 UNION ALL

672 --userdictionaryvalueslog

673 SELECT

674 o.id AS id,

675 uml.id_attribute AS new_idattribute,

676 to_clob(uml.old_value) AS old_value,

677 to_clob(uml.new_value) AS new_value,

678 uml.old_serialnumber AS old_serialnumber,

679 uml.new_serialnumber AS new_serialnumber,

680 uml.modified_user AS modified_user,

681 uml.modified_time AS modified_time,

682 uml.type_of_operation AS type_of_operation,

683 s.value AS attribute_note,

684 'CLOB' AS attribute_datatype,

685 a.length AS datatype_length,

686 null OLD_IDPARENTOBJECT,

687 null NEW_IDPARENTOBJECT,

688 null OBJECTTYPE,

689 null OLD_OWNER,

690 null NEW_OWNER,

691 null OLD_ORIGHTS,

692 null NEW_ORIGHTS,

693 null OLD_ARIGHTS,

694 null NEW_ARIGHTS,

695 null OPERATION,

696 null OBJECTNOTE

697 FROM objects o, attributes a, objecttypes ot, userdictionaryvalueslog uml, systemdatavalues s

698 WHERE o.idobjecttype = nIDObjectType and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

699 AND

700 (uml.id_object = o.id and uml.id_attribute = a.id)

701

702 UNION ALL

703

704 SELECT

705 o.id AS id,

706 null AS new_idattribute,

707 null AS old_value,

708 null AS new_value,

709 null AS old_serialnumber,

710 null AS new_serialnumber,

711 OL.MODIFIED_USER AS modified_user,

712 OL.MODIFIED_TIME AS modified_time,

713 null AS type_of_operation,

714 null AS attribute_note,

715 null AS attribute_datatype,

716 null AS datatype_length,

717 OL.OLD_IDPARENTOBJECT OLD_IDPARENTOBJECT,

718 OL.NEW_IDPARENTOBJECT NEW_IDPARENTOBJECT,

719 OL.NEW_OBJECTTYPE NEW_OBJECTTYPE,

720 OL.OLD_OWNER OLD_OWNER,

721 OL.NEW_OWNER NEW_OWNER,

722 OL.OLD_ORIGHTS OLD_ORIGHTS,

723 OL.NEW_ORIGHTS NEW_ORIGHTS,

724 OL.OLD_ARIGHTS OLD_ARIGHTS,

725 OL.NEW_ARIGHTS NEW_ARIGHTS,

726 OL.TYPE_OF_OPERATION OPERATION,

727 s.value OBJECTNOTE

728 FROM objectslog OL, systemdatavalues s, objects o

729 WHERE o.idobjecttype = nIDObjectType AND OL.ID_OBJECT = o.id and s.idparent = OL.ID_OBJECT AND S.IDLANGUAGE = nIdLanguage

730 -- ORDER BY modified_time

731

732) B)A

733 GROUP BY A.modified_time, A.MODIFIED_USER

734 ORDER BY A.modified_time;

735

736 RETURN xDocument;

737

738 END showAuditByObjectType;

739

740 function countTransactionsByAttribute(nIDAttribute attributes.id%TYPE, nIdLanguage NUMBER) RETURN NUMBER IS

741 nAmountTransactions NUMBER;

742 nDataType attributes.iddatatype%TYPE;

743 vName datatypes.name%TYPE;

744 BEGIN

745 SELECT idDataType INTO nDataType FROM attributes WHERE id = nIDAttribute;

746 SELECT name INTO vName FROM datatypes WHERE id = nDataType;

747 IF vName = 'MULTILANG' THEN

748

749 SELECT count(*)

750 INTO nAmountTransactions

751 FROM

752 (SELECT modified_time

753 FROM usermultilangvalueslog u, systemdatavalues s

754 WHERE u.id_attribute = nIDAttribute AND u.id_attribute = s.idparent AND s.idlanguage = nIdLanguage

755 GROUP BY u.modified_time) A;

756

757 RETURN nAmountTransactions;

758

759 ELSIF vName = 'VARCHAR2' THEN

760

761 SELECT count(*)

762 INTO nAmountTransactions

763 FROM

764 (SELECT modified_time

765 FROM userstringvalueslog u, systemdatavalues s

766 WHERE u.id_attribute = nIDAttribute AND u.id_attribute = s.idparent AND s.idlanguage = nIdLanguage

767 GROUP BY u.modified_time) A;

768

769 RETURN nAmountTransactions;

770

771 ELSIF vName = 'NUMBER' THEN

772

773 SELECT count(*)

774 INTO nAmountTransactions

775 FROM

776 (SELECT modified_time

777 FROM usernumbervalueslog u, systemdatavalues s

778 WHERE u.id_attribute = nIDAttribute AND u.id_attribute = s.idparent AND s.idlanguage = nIdLanguage

779 GROUP BY u.modified_time) A;

780

781 RETURN nAmountTransactions;

782

783 ELSIF vName = 'DATE' THEN

784

785 SELECT count(*)

786 INTO nAmountTransactions

787 FROM

788 (SELECT modified_time

789 FROM userdatevalueslog u, systemdatavalues s

790 WHERE u.id_attribute = nIDAttribute AND u.id_attribute = s.idparent AND s.idlanguage = nIdLanguage

791 GROUP BY u.modified_time) A;

792

793 RETURN nAmountTransactions;

794

795 ELSIF vName = 'CLOB' THEN

796

797 SELECT count(*)

798 INTO nAmountTransactions

799 FROM

800 (SELECT modified_time

801 FROM userclobvalueslog u, systemdatavalues s

802 WHERE u.id_attribute = nIDAttribute AND u.id_attribute = s.idparent AND s.idlanguage = nIdLanguage

803 GROUP BY u.modified_time) A;

804

805 RETURN nAmountTransactions;

806

807 ELSIF vName = 'DICTIONARY' THEN

808

809 SELECT count(*)

810 INTO nAmountTransactions

811 FROM

812 (SELECT modified_time

813 FROM userdictionaryvalueslog u, systemdatavalues s

814 WHERE u.id_attribute = nIDAttribute AND u.id_attribute = s.idparent AND s.idlanguage = nIdLanguage

815 GROUP BY u.modified_time) A;

816

817 RETURN nAmountTransactions;

818

819 END IF;

820 RETURN 0;

821

822 RETURN nAmountTransactions;

823 EXCEPTION WHEN NO_DATA_FOUND THEN

824 RETURN 0;

825 END countTransactionsByAttribute;

826

827 function countTransactionsByObject(nIDObject objects.id%TYPE, nIdLanguage NUMBER) RETURN NUMBER IS

828 nAmountTransactions NUMBER;

829 BEGIN

830 SELECT count(*) INTO nAmountTransactions FROM (

831 SELECT a.modified_time

832 FROM (

833 --usermultilangvalueslog

834 SELECT uml.id_attribute AS new_idattribute,

835 to_clob(uml.old_value) AS old_value,

836 to_clob(uml.new_value) AS new_value,

837 uml.old_serialnumber AS old_serialnumber,

838 uml.new_serialnumber AS new_serialnumber,

839 uml.modified_user AS modified_user,

840 uml.modified_time AS modified_time,

841 uml.type_of_operation AS type_of_operation,

842 s.value AS attribute_note,

843 'MULTILANG' AS attribute_datatype,

844 a.length AS datatype_length,

845 null OLD_IDPARENTOBJECT,

846 null NEW_IDPARENTOBJECT,

847 null OBJECTTYPE,

848 null OLD_OWNER,

849 null NEW_OWNER,

850 null OLD_ORIGHTS,

851 null NEW_ORIGHTS,

852 null OLD_ARIGHTS,

853 null NEW_ARIGHTS,

854 null OPERATION,

855 null OBJECTNOTE

856 FROM objects o, attributes a, objecttypes ot, usermultilangvalueslog uml, systemdatavalues s

857 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

858 AND

859 (uml.id_object = o.id and uml.id_attribute = a.id)

860

861 UNION ALL

862 --userstringvalueslog

863 SELECT uml.id_attribute AS new_idattribute,

864 to_clob(uml.old_value) AS old_value,

865 to_clob(uml.new_value) AS new_value,

866 uml.old_serialnumber AS old_serialnumber,

867 uml.new_serialnumber AS new_serialnumber,

868 uml.modified_user AS modified_user,

869 uml.modified_time AS modified_time,

870 uml.type_of_operation AS type_of_operation,

871 s.value AS attribute_note,

872 'STRING' AS attribute_datatype,

873 a.length AS datatype_length,

874 null OLD_IDPARENTOBJECT,

875 null NEW_IDPARENTOBJECT,

876 null OBJECTTYPE,

877 null OLD_OWNER,

878 null NEW_OWNER,

879 null OLD_ORIGHTS,

880 null NEW_ORIGHTS,

881 null OLD_ARIGHTS,

882 null NEW_ARIGHTS,

883 null OPERATION,

884 null OBJECTNOTE

885 FROM objects o, attributes a, objecttypes ot, userstringvalueslog uml, systemdatavalues s

886 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

887 AND

888 (uml.id_object = o.id and uml.id_attribute = a.id)

889

890 UNION ALL

891 --userdatevalueslog

892 SELECT uml.id_attribute AS new_idattribute,

893 to_clob(uml.old_value) AS old_value,

894 to_clob(uml.new_value) AS new_value,

895 uml.old_serialnumber AS old_serialnumber,

896 uml.new_serialnumber AS new_serialnumber,

897 uml.modified_user AS modified_user,

898 uml.modified_time AS modified_time,

899 uml.type_of_operation AS type_of_operation,

900 s.value AS attribute_note,

901 'DATE' AS attribute_datatype,

902 a.length AS datatype_length,

903 null OLD_IDPARENTOBJECT,

904 null NEW_IDPARENTOBJECT,

905 null OBJECTTYPE,

906 null OLD_OWNER,

907 null NEW_OWNER,

908 null OLD_ORIGHTS,

909 null NEW_ORIGHTS,

910 null OLD_ARIGHTS,

911 null NEW_ARIGHTS,

912 null OPERATION,

913 null OBJECTNOTE

914 FROM objects o, attributes a, objecttypes ot, userdatevalueslog uml, systemdatavalues s

915 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

916 AND

917 (uml.id_object = o.id and uml.id_attribute = a.id)

918

919 UNION ALL

920 --userclobvalueslog

921 SELECT uml.id_attribute AS new_idattribute,

922 to_clob(uml.old_value) AS old_value,

923 to_clob(uml.new_value) AS new_value,

924 uml.old_serialnumber AS old_serialnumber,

925 uml.new_serialnumber AS new_serialnumber,

926 uml.modified_user AS modified_user,

927 uml.modified_time AS modified_time,

928 uml.type_of_operation AS type_of_operation,

929 s.value AS attribute_note,

930 'CLOB' AS attribute_datatype,

931 a.length AS datatype_length,

932 null OLD_IDPARENTOBJECT,

933 null NEW_IDPARENTOBJECT,

934 null OBJECTTYPE,

935 null OLD_OWNER,

936 null NEW_OWNER,

937 null OLD_ORIGHTS,

938 null NEW_ORIGHTS,

939 null OLD_ARIGHTS,

940 null NEW_ARIGHTS,

941 null OPERATION,

942 null OBJECTNOTE

943 FROM objects o, attributes a, objecttypes ot, userclobvalueslog uml, systemdatavalues s

944 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

945 AND

946 (uml.id_object = o.id and uml.id_attribute = a.id)

947

948 UNION ALL

949 --usernumbervalueslog

950 SELECT uml.id_attribute AS new_idattribute,

951 to_clob(uml.old_value) AS old_value,

952 to_clob(uml.new_value) AS new_value,

953 uml.old_serialnumber AS old_serialnumber,

954 uml.new_serialnumber AS new_serialnumber,

955 uml.modified_user AS modified_user,

956 uml.modified_time AS modified_time,

957 uml.type_of_operation AS type_of_operation,

958 s.value AS attribute_note,

959 'CLOB' AS attribute_datatype,

960 a.length AS datatype_length,

961 null OLD_IDPARENTOBJECT,

962 null OBJECTTYPE,

963 null NEW_OBJECTTYPE,

964 null OLD_OWNER,

965 null NEW_OWNER,

966 null OLD_ORIGHTS,

967 null NEW_ORIGHTS,

968 null OLD_ARIGHTS,

969 null NEW_ARIGHTS,

970 null OPERATION,

971 null OBJECTNOTE

972 FROM objects o, attributes a, objecttypes ot, usernumbervalueslog uml, systemdatavalues s

973 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

974 AND

975 (uml.id_object = o.id and uml.id_attribute = a.id)

976

977 UNION ALL

978 --userdictionaryvalueslog

979 SELECT uml.id_attribute AS new_idattribute,

980 to_clob(uml.old_value) AS old_value,

981 to_clob(uml.new_value) AS new_value,

982 uml.old_serialnumber AS old_serialnumber,

983 uml.new_serialnumber AS new_serialnumber,

984 uml.modified_user AS modified_user,

985 uml.modified_time AS modified_time,

986 uml.type_of_operation AS type_of_operation,

987 s.value AS attribute_note,

988 'CLOB' AS attribute_datatype,

989 a.length AS datatype_length,

990 null OLD_IDPARENTOBJECT,

991 null NEW_IDPARENTOBJECT,

992 null OBJECTTYPE,

993 null OLD_OWNER,

994 null NEW_OWNER,

995 null OLD_ORIGHTS,

996 null NEW_ORIGHTS,

997 null OLD_ARIGHTS,

998 null NEW_ARIGHTS,

999 null OPERATION,

1000 null OBJECTNOTE

1001 FROM objects o, attributes a, objecttypes ot, userdictionaryvalueslog uml, systemdatavalues s

1002 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

1003 AND

1004 (uml.id_object = o.id and uml.id_attribute = a.id)

1005

1006 UNION ALL

1007

1008 SELECT null AS new_idattribute,

1009 null AS old_value,

1010 null AS new_value,

1011 null AS old_serialnumber,

1012 null AS new_serialnumber,

1013 OL.MODIFIED_USER AS modified_user,

1014 OL.MODIFIED_TIME AS modified_time,

1015 null AS type_of_operation,

1016 null AS attribute_note,

1017 null AS attribute_datatype,

1018 null AS datatype_length,

1019 OL.OLD_IDPARENTOBJECT OLD_IDPARENTOBJECT,

1020 OL.NEW_IDPARENTOBJECT NEW_IDPARENTOBJECT,

1021 OL.NEW_OBJECTTYPE NEW_OBJECTTYPE,

1022 OL.OLD_OWNER OLD_OWNER,

1023 OL.NEW_OWNER NEW_OWNER,

1024 OL.OLD_ORIGHTS OLD_ORIGHTS,

1025 OL.NEW_ORIGHTS NEW_ORIGHTS,

1026 OL.OLD_ARIGHTS OLD_ARIGHTS,

1027 OL.NEW_ARIGHTS NEW_ARIGHTS,

1028 OL.TYPE_OF_OPERATION OPERATION,

1029 s.value OBJECTNOTE

1030 FROM objectslog OL, systemdatavalues s

1031 WHERE OL.ID_OBJECT = nIDObject and s.idparent = OL.ID_OBJECT AND S.IDLANGUAGE = nIdLanguage

1032

1033) A

1034 GROUP BY A.modified_time) B;

1035

1036 RETURN nAmountTransactions;

1037 END countTransactionsByObject;

1038

1039 function countTransactionsByObjectType(nIDObjectType objecttypes.id%TYPE, nIdLanguage NUMBER) RETURN NUMBER IS

1040 nAmountTransactions NUMBER;

1041 BEGIN

1042 SELECT sum(countTransactionsByObject(id, nIdLanguage))

1043 INTO nAmountTransactions

1044 FROM objects o

1045 WHERE o.idobjecttype = nIDObjectType

1046 AND (EXISTS (SELECT * FROM userclobvalueslog u WHERE u.id_object = o.id)

1047 OR EXISTS (SELECT * FROM userdatevalueslog u WHERE u.id_object = o.id)

1048 OR EXISTS (SELECT * FROM userdictionaryvalueslog u WHERE u.id_object = o.id)

1049 OR EXISTS (SELECT * FROM usermultilangvalueslog u WHERE u.id_object = o.id)

1050 OR EXISTS (SELECT * FROM usernumbervalueslog u WHERE u.id_object = o.id)

1051 OR EXISTS (SELECT * FROM userstringvalueslog u WHERE u.id_object = o.id));

1052 RETURN nAmountTransactions;

1053 END countTransactionsByObjectType;

1054

1055 FUNCTION pageAuditByAttribute(nIDAttribute attributes.id%TYPE, nIdLanguage NUMBER, nPageNumber NUMBER, nAmountPerPage NUMBER) RETURN XMLTYPE AS

1056 nDataType attributes.iddatatype%TYPE;

1057 xDocument XMLTYPE;

1058 vName datatypes.name%TYPE;

1059 BEGIN

1060 SELECT idDataType INTO nDataType FROM attributes WHERE id = nIDAttribute;

1061 SELECT name INTO vName FROM datatypes WHERE id = nDataType;

1062 IF vName = 'MULTILANG' THEN

1063

1064 SELECT

1065 XMLElement('attribute_summary', XMLAttributes(nIDAttribute AS id),

1066 XMLAgg(XMLElement('transaction',

1067 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

1068 XMLAgg(

1069 XMLConcat(

1070 XMLElement('attribute',

1071 XMLAttributes(

1072 nIDAttribute AS id_attribute,

1073 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

1074 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

1075 'MULTILANG' AS attribute_datatype

1076),

1077 XMLElement('old_value_attribute',A.old_value),

1078 XMLElement('new_value_attribute',A.new_value)

1079)

1080)--XMLConcat

1081)--XMLAgg

1082)--XMLAttributes

1083 order by A.modified_time desc

1084)

1085)

1086 INTO xDocument

1087 FROM (

1088 SELECT a.*, uml.*, s.*, rownum r

1089 FROM attributes a, usermultilangvalueslog uml, systemdatavalues s

1090 WHERE a.id = nIDAttribute and uml.id_attribute = nIDAttribute AND uml.id_attribute = s.idparent AND s.idlanguage = nIdLanguage

1091)A

1092 WHERE (A.r between ((nPageNumber-1)*nAmountPerPage+1) and (nPageNumber*nAmountPerPage))

1093 GROUP BY A.modified_time, A.MODIFIED_USER;

1094

1095 RETURN xDocument;

1096 ELSIF vName = 'VARCHAR2' THEN

1097

1098 SELECT

1099 XMLElement('attribute_summary', XMLAttributes(nIDAttribute AS id),

1100 XMLAgg(XMLElement('transaction',

1101 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

1102 XMLAgg(

1103 XMLConcat(

1104 XMLElement('attribute',

1105 XMLAttributes(

1106 nIDAttribute AS id_attribute,

1107 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

1108 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

1109 'STRING' AS attribute_datatype

1110),

1111 XMLElement('old_value_attribute',A.old_value),

1112 XMLElement('new_value_attribute',A.new_value)

1113)

1114)--XMLConcat

1115)--XMLAgg

1116)--XMLAttributes

1117 order by A.modified_time desc

1118)

1119)

1120 INTO xDocument

1121 FROM (

1122 SELECT a.*, usl.*, s.*, rownum r

1123 FROM attributes a, userstringvalueslog usl, systemdatavalues s

1124 WHERE a.id = nIDAttribute and usl.id_attribute = nIDAttribute AND usl.id_attribute = s.idparent AND s.idlanguage = 1

1125)A

1126 WHERE (A.r between ((nPageNumber-1)*nAmountPerPage+1) and (nPageNumber*nAmountPerPage))

1127 GROUP BY A.modified_time, A.MODIFIED_USER;

1128

1129 RETURN xDocument;

1130

1131 ELSIF vName = 'NUMBER' THEN

1132

1133 SELECT

1134 XMLElement('attribute_summary', XMLAttributes(nIDAttribute AS id),

1135 XMLAgg(XMLElement('transaction',

1136 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

1137 XMLAgg(

1138 XMLConcat(

1139 XMLElement('attribute',

1140 XMLAttributes(

1141 nIDAttribute AS id_attribute,

1142 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

1143 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

1144 'NUMBER' AS attribute_datatype

1145),

1146 XMLElement('old_value_attribute',A.old_value),

1147 XMLElement('new_value_attribute',A.new_value)

1148)

1149)--XMLConcat

1150)--XMLAgg

1151)--XMLAttributes

1152 order by A.modified_time desc

1153)

1154)

1155 INTO xDocument

1156 FROM (

1157 SELECT a.*, usl.*, s.*, rownum r

1158 FROM attributes a, usernumbervalueslog usl, systemdatavalues s

1159 WHERE a.id = nIDAttribute and usl.id_attribute = nIDAttribute AND usl.id_attribute = s.idparent AND s.idlanguage = 1

1160)A

1161 WHERE (A.r between ((nPageNumber-1)*nAmountPerPage+1) and (nPageNumber*nAmountPerPage))

1162 GROUP BY A.modified_time, A.MODIFIED_USER;

1163

1164 RETURN xDocument;

1165

1166 ELSIF vName = 'DATE' THEN

1167

1168 SELECT

1169 XMLElement('attribute_summary', XMLAttributes(nIDAttribute AS id),

1170 XMLAgg(XMLElement('transaction',

1171 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

1172 XMLAgg(

1173 XMLConcat(

1174 XMLElement('attribute',

1175 XMLAttributes(

1176 nIDAttribute AS id_attribute,

1177 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

1178 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

1179 'DATE' AS attribute_datatype

1180),

1181 XMLElement('old_value_attribute',A.old_value),

1182 XMLElement('new_value_attribute',A.new_value)

1183)

1184)--XMLConcat

1185)--XMLAgg

1186)--XMLAttributes

1187 order by A.modified_time desc

1188)

1189)

1190 INTO xDocument

1191 FROM (

1192 SELECT a.*, usl.*, s.*, rownum r

1193 FROM attributes a, userdatevalueslog usl, systemdatavalues s

1194 WHERE a.id = nIDAttribute and usl.id_attribute = nIDAttribute AND usl.id_attribute = s.idparent AND s.idlanguage = 1

1195)A

1196 WHERE (A.r between ((nPageNumber-1)*nAmountPerPage+1) and (nPageNumber*nAmountPerPage))

1197 GROUP BY A.modified_time, A.MODIFIED_USER;

1198

1199 RETURN xDocument;

1200

1201 ELSIF vName = 'CLOB' THEN

1202

1203 SELECT

1204 XMLElement('attribute_summary', XMLAttributes(nIDAttribute AS id),

1205 XMLAgg(XMLElement('transaction',

1206 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

1207 XMLAgg(

1208 XMLConcat(

1209 XMLElement('attribute',

1210 XMLAttributes(

1211 nIDAttribute AS id_attribute,

1212 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

1213 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

1214 'CLOB' AS attribute_datatype

1215),

1216 XMLElement('old_value_attribute',A.old_value),

1217 XMLElement('new_value_attribute',A.new_value)

1218)

1219)--XMLConcat

1220)--XMLAgg

1221)--XMLAttributes

1222 order by A.modified_time desc

1223)

1224)

1225 INTO xDocument

1226 FROM (

1227 SELECT a.*, usl.*, s.*, rownum r

1228 FROM attributes a, userclobvalueslog usl, systemdatavalues s

1229 WHERE a.id = nIDAttribute and usl.id_attribute = nIDAttribute AND usl.id_attribute = s.idparent AND s.idlanguage = 1

1230)A

1231 WHERE (A.r between ((nPageNumber-1)*nAmountPerPage+1) and (nPageNumber*nAmountPerPage))

1232 GROUP BY A.modified_time, A.MODIFIED_USER;

1233

1234 RETURN xDocument;

1235

1236 ELSIF vName = 'DICTIONARY' THEN

1237

1238 SELECT

1239 XMLElement('attribute_summary', XMLAttributes(nIDAttribute AS id),

1240 XMLAgg(XMLElement('transaction',

1241 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

1242 XMLAgg(

1243 XMLConcat(

1244 XMLElement('attribute',

1245 XMLAttributes(

1246 nIDAttribute AS id_attribute,

1247 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

1248 A.type_of_operation AS type_of_operation, A.note AS attribute_note,

1249 'DICTIONARY' AS attribute_datatype

1250),

1251 XMLElement('old_value_attribute',A.old_value),

1252 XMLElement('new_value_attribute',A.new_value)

1253)

1254)--XMLConcat

1255)--XMLAgg

1256)--XMLAttributes

1257 order by A.modified_time desc

1258)

1259)

1260 INTO xDocument

1261 FROM (

1262 SELECT a.*, usl.*, s.*, rownum r

1263 FROM attributes a, userdictionaryvalueslog usl, systemdatavalues s

1264 WHERE a.id = nIDAttribute and usl.id_attribute = nIDAttribute AND usl.id_attribute = s.idparent AND s.idlanguage = 1

1265)A

1266 WHERE (A.r between ((nPageNumber-1)*nAmountPerPage+1) and (nPageNumber*nAmountPerPage))

1267 GROUP BY A.modified_time, A.MODIFIED_USER;

1268

1269 RETURN xDocument;

1270

1271 END IF;

1272 RETURN NULL;

1273 END pageAuditByAttribute;

1274

1275 FUNCTION pageAuditByObject(nIDObject objects.id%TYPE, nIdLanguage NUMBER, nPageNumber NUMBER, nAmountPerPage NUMBER) RETURN XMLTYPE AS

1276 xDocument XMLTYPE;

1277 BEGIN

1278

1279 SELECT

1280 XMLElement('object_summary', XMLAttributes(nIDObject AS id),

1281 XMLAgg(XMLElement('transaction',

1282 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

1283 XMLAgg(

1284 XMLConcat(

1285 XMLElement('attribute',

1286 XMLAttributes(

1287 A.new_idattribute AS id_attribute,

1288 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

1289 A.type_of_operation AS type_of_operation, A.attribute_note AS attribute_note,

1290 A.attribute_datatype AS attribute_datatype, A.datatype_length AS datatype_length

1291),

1292 XMLElement('old_value_attribute',A.old_value),

1293 XMLElement('new_value_attribute',A.new_value)

1294),

1295 XMLElement('object',

1296 XMLAttributes(

1297 A.OLD_IDPARENTOBJECT AS OLD_IDPARENTOBJECT,

1298 A.NEW_IDPARENTOBJECT AS NEW_IDPARENTOBJECT,

1299 A.OBJECTTYPE NEW_OBJECTTYPE,

1300 A.OLD_OWNER OLD_OWNER,

1301 A.NEW_OWNER NEW_OWNER,

1302 A.OLD_ORIGHTS OLD_ORIGHTS,

1303 A.NEW_ORIGHTS NEW_ORIGHTS,

1304 A.OLD_ARIGHTS OLD_ARIGHTS,

1305 A.NEW_ARIGHTS NEW_ARIGHTS,

1306 A.OPERATION OPERATION,

1307 A.OBJECTNOTE OBJECTNOTE

1308)

1309)

1310)--XMLConcat

1311)--XMLAgg

1312)--XMLAttributes

1313 order by A.modified_time desc

1314)

1315)

1316 INTO xDocument

1317 FROM (

1318 SELECT B.*, rownum r

1319 FROM (

1320 --usermultilangvalueslog

1321 SELECT uml.id_attribute AS new_idattribute,

1322 to_clob(uml.old_value) AS old_value,

1323 to_clob(uml.new_value) AS new_value,

1324 uml.old_serialnumber AS old_serialnumber,

1325 uml.new_serialnumber AS new_serialnumber,

1326 uml.modified_user AS modified_user,

1327 uml.modified_time AS modified_time,

1328 uml.type_of_operation AS type_of_operation,

1329 s.value AS attribute_note,

1330 'MULTILANG' AS attribute_datatype,

1331 a.length AS datatype_length,

1332 null OLD_IDPARENTOBJECT,

1333 null NEW_IDPARENTOBJECT,

1334 null OBJECTTYPE,

1335 null OLD_OWNER,

1336 null NEW_OWNER,

1337 null OLD_ORIGHTS,

1338 null NEW_ORIGHTS,

1339 null OLD_ARIGHTS,

1340 null NEW_ARIGHTS,

1341 null OPERATION,

1342 null OBJECTNOTE

1343 FROM objects o, attributes a, objecttypes ot, usermultilangvalueslog uml, systemdatavalues s

1344 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

1345 AND

1346 (uml.id_object = o.id and uml.id_attribute = a.id)

1347

1348 UNION ALL

1349 --userstringvalueslog

1350 SELECT uml.id_attribute AS new_idattribute,

1351 to_clob(uml.old_value) AS old_value,

1352 to_clob(uml.new_value) AS new_value,

1353 uml.old_serialnumber AS old_serialnumber,

1354 uml.new_serialnumber AS new_serialnumber,

1355 uml.modified_user AS modified_user,

1356 uml.modified_time AS modified_time,

1357 uml.type_of_operation AS type_of_operation,

1358 s.value AS attribute_note,

1359 'STRING' AS attribute_datatype,

1360 a.length AS datatype_length,

1361 null OLD_IDPARENTOBJECT,

1362 null NEW_IDPARENTOBJECT,

1363 null OBJECTTYPE,

1364 null OLD_OWNER,

1365 null NEW_OWNER,

1366 null OLD_ORIGHTS,

1367 null NEW_ORIGHTS,

1368 null OLD_ARIGHTS,

1369 null NEW_ARIGHTS,

1370 null OPERATION,

1371 null OBJECTNOTE

1372 FROM objects o, attributes a, objecttypes ot, userstringvalueslog uml, systemdatavalues s

1373 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

1374 AND

1375 (uml.id_object = o.id and uml.id_attribute = a.id)

1376

1377 UNION ALL

1378 --userdatevalueslog

1379 SELECT uml.id_attribute AS new_idattribute,

1380 to_clob(uml.old_value) AS old_value,

1381 to_clob(uml.new_value) AS new_value,

1382 uml.old_serialnumber AS old_serialnumber,

1383 uml.new_serialnumber AS new_serialnumber,

1384 uml.modified_user AS modified_user,

1385 uml.modified_time AS modified_time,

1386 uml.type_of_operation AS type_of_operation,

1387 s.value AS attribute_note,

1388 'DATE' AS attribute_datatype,

1389 a.length AS datatype_length,

1390 null OLD_IDPARENTOBJECT,

1391 null NEW_IDPARENTOBJECT,

1392 null OBJECTTYPE,

1393 null OLD_OWNER,

1394 null NEW_OWNER,

1395 null OLD_ORIGHTS,

1396 null NEW_ORIGHTS,

1397 null OLD_ARIGHTS,

1398 null NEW_ARIGHTS,

1399 null OPERATION,

1400 null OBJECTNOTE

1401 FROM objects o, attributes a, objecttypes ot, userdatevalueslog uml, systemdatavalues s

1402 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

1403 AND

1404 (uml.id_object = o.id and uml.id_attribute = a.id)

1405

1406 UNION ALL

1407 --userclobvalueslog

1408 SELECT uml.id_attribute AS new_idattribute,

1409 to_clob(uml.old_value) AS old_value,

1410 to_clob(uml.new_value) AS new_value,

1411 uml.old_serialnumber AS old_serialnumber,

1412 uml.new_serialnumber AS new_serialnumber,

1413 uml.modified_user AS modified_user,

1414 uml.modified_time AS modified_time,

1415 uml.type_of_operation AS type_of_operation,

1416 s.value AS attribute_note,

1417 'CLOB' AS attribute_datatype,

1418 a.length AS datatype_length,

1419 null OLD_IDPARENTOBJECT,

1420 null NEW_IDPARENTOBJECT,

1421 null OBJECTTYPE,

1422 null OLD_OWNER,

1423 null NEW_OWNER,

1424 null OLD_ORIGHTS,

1425 null NEW_ORIGHTS,

1426 null OLD_ARIGHTS,

1427 null NEW_ARIGHTS,

1428 null OPERATION,

1429 null OBJECTNOTE

1430 FROM objects o, attributes a, objecttypes ot, userclobvalueslog uml, systemdatavalues s

1431 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

1432 AND

1433 (uml.id_object = o.id and uml.id_attribute = a.id)

1434

1435 UNION ALL

1436 --usernumbervalueslog

1437 SELECT uml.id_attribute AS new_idattribute,

1438 to_clob(uml.old_value) AS old_value,

1439 to_clob(uml.new_value) AS new_value,

1440 uml.old_serialnumber AS old_serialnumber,

1441 uml.new_serialnumber AS new_serialnumber,

1442 uml.modified_user AS modified_user,

1443 uml.modified_time AS modified_time,

1444 uml.type_of_operation AS type_of_operation,

1445 s.value AS attribute_note,

1446 'CLOB' AS attribute_datatype,

1447 a.length AS datatype_length,

1448 null OLD_IDPARENTOBJECT,

1449 null OBJECTTYPE,

1450 null NEW_OBJECTTYPE,

1451 null OLD_OWNER,

1452 null NEW_OWNER,

1453 null OLD_ORIGHTS,

1454 null NEW_ORIGHTS,

1455 null OLD_ARIGHTS,

1456 null NEW_ARIGHTS,

1457 null OPERATION,

1458 null OBJECTNOTE

1459 FROM objects o, attributes a, objecttypes ot, usernumbervalueslog uml, systemdatavalues s

1460 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

1461 AND

1462 (uml.id_object = o.id and uml.id_attribute = a.id)

1463

1464 UNION ALL

1465 --userdictionaryvalueslog

1466 SELECT uml.id_attribute AS new_idattribute,

1467 to_clob(uml.old_value) AS old_value,

1468 to_clob(uml.new_value) AS new_value,

1469 uml.old_serialnumber AS old_serialnumber,

1470 uml.new_serialnumber AS new_serialnumber,

1471 uml.modified_user AS modified_user,

1472 uml.modified_time AS modified_time,

1473 uml.type_of_operation AS type_of_operation,

1474 s.value AS attribute_note,

1475 'CLOB' AS attribute_datatype,

1476 a.length AS datatype_length,

1477 null OLD_IDPARENTOBJECT,

1478 null NEW_IDPARENTOBJECT,

1479 null OBJECTTYPE,

1480 null OLD_OWNER,

1481 null NEW_OWNER,

1482 null OLD_ORIGHTS,

1483 null NEW_ORIGHTS,

1484 null OLD_ARIGHTS,

1485 null NEW_ARIGHTS,

1486 null OPERATION,

1487 null OBJECTNOTE

1488 FROM objects o, attributes a, objecttypes ot, userdictionaryvalueslog uml, systemdatavalues s

1489 WHERE o.id = nIDObject and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

1490 AND

1491 (uml.id_object = o.id and uml.id_attribute = a.id)

1492

1493 UNION ALL

1494

1495 SELECT null AS new_idattribute,

1496 null AS old_value,

1497 null AS new_value,

1498 null AS old_serialnumber,

1499 null AS new_serialnumber,

1500 OL.MODIFIED_USER AS modified_user,

1501 OL.MODIFIED_TIME AS modified_time,

1502 null AS type_of_operation,

1503 null AS attribute_note,

1504 null AS attribute_datatype,

1505 null AS datatype_length,

1506 OL.OLD_IDPARENTOBJECT OLD_IDPARENTOBJECT,

1507 OL.NEW_IDPARENTOBJECT NEW_IDPARENTOBJECT,

1508 OL.NEW_OBJECTTYPE NEW_OBJECTTYPE,

1509 OL.OLD_OWNER OLD_OWNER,

1510 OL.NEW_OWNER NEW_OWNER,

1511 OL.OLD_ORIGHTS OLD_ORIGHTS,

1512 OL.NEW_ORIGHTS NEW_ORIGHTS,

1513 OL.OLD_ARIGHTS OLD_ARIGHTS,

1514 OL.NEW_ARIGHTS NEW_ARIGHTS,

1515 OL.TYPE_OF_OPERATION OPERATION,

1516 s.value OBJECTNOTE

1517 FROM objectslog OL, systemdatavalues s

1518 WHERE OL.ID_OBJECT = nIDObject and s.idparent = OL.ID_OBJECT AND S.IDLANGUAGE = nIdLanguage

1519 -- ORDER BY modified_time

1520

1521) B)A

1522 WHERE (A.r between ((nPageNumber-1)*nAmountPerPage+1) and (nPageNumber*nAmountPerPage))

1523 GROUP BY A.modified_time, A.MODIFIED_USER

1524 -- HAVING count(A.MODIFIED_TIME) between ((nPageNumber-1)*nAmountPerPage+1) and (nPageNumber*nAmountPerPage)--;

1525 ORDER BY A.modified_time;

1526

1527 RETURN xDocument;

1528

1529 END pageAuditByObject;

1530

1531 FUNCTION pageAuditByObjectType(nIDObjectType objecttypes.id%TYPE, nIdLanguage NUMBER, nPageNumber NUMBER, nAmountPerPage NUMBER) RETURN XMLTYPE IS

1532 xDocument XMLTYPE;

1533 BEGIN

1534

1535 --SELECT --XMLElement('object_type',

1536 -- XMLAttributes(nIDObjectType AS idObjectType),

1537 -- XMLElement('a',

1538

1539 SELECT XMLElement('object_type',

1540 XMLAttributes(1 AS idObjectType),

1541 XMLElement('objects_summary',

1542 XMLAgg(XMLElement('transaction',

1543 XMLAttributes(to_char(A.modified_time, 'dd-mm-yyyy hh24:mi:ss') AS modified_time, A.MODIFIED_USER AS MODIFIED_USER),

1544 XMLAgg(

1545 XMLElement('object_info',

1546 XMLAttributes(A.id AS 'object_id'),

1547 XMLElement('attribute',

1548 XMLAttributes(

1549 A.new_idattribute AS id_attribute,

1550 A.old_serialnumber AS old_serialnumber, A.new_serialnumber AS new_serialnumber,

1551 A.type_of_operation AS type_of_operation, A.attribute_note AS attribute_note,

1552 A.attribute_datatype AS attribute_datatype, A.datatype_length AS datatype_length

1553),

1554 XMLElement('old_value_attribute',A.old_value),

1555 XMLElement('new_value_attribute',A.new_value)

1556),

1557 XMLElement('object_change',

1558 XMLAttributes(

1559 A.OLD_IDPARENTOBJECT AS OLD_IDPARENTOBJECT,

1560 A.NEW_IDPARENTOBJECT AS NEW_IDPARENTOBJECT,

1561 A.OBJECTTYPE NEW_OBJECTTYPE,

1562 A.OLD_OWNER OLD_OWNER,

1563 A.NEW_OWNER NEW_OWNER,

1564 A.OLD_ORIGHTS OLD_ORIGHTS,

1565 A.NEW_ORIGHTS NEW_ORIGHTS,

1566 A.OLD_ARIGHTS OLD_ARIGHTS,

1567 A.NEW_ARIGHTS NEW_ARIGHTS,

1568 A.OPERATION OPERATION,

1569 A.OBJECTNOTE OBJECTNOTE

1570)

1571)

1572)--XMLConcat

1573)--XMLAgg

1574)--XMLAttributes

1575 order by A.modified_time desc

1576)

1577)

1578)

1579 INTO xDocument

1580 --showAuditByObject(id, nIdLanguage)

1581 --) AS objects)

1582 FROM (

1583 SELECT B.*, rownum r

1584 FROM (

1585 --usermultilangvalueslog

1586 SELECT

1587 o.id AS id,

1588 uml.id_attribute AS new_idattribute,

1589 to_clob(uml.old_value) AS old_value,

1590 to_clob(uml.new_value) AS new_value,

1591 uml.old_serialnumber AS old_serialnumber,

1592 uml.new_serialnumber AS new_serialnumber,

1593 uml.modified_user AS modified_user,

1594 uml.modified_time AS modified_time,

1595 uml.type_of_operation AS type_of_operation,

1596 s.value AS attribute_note,

1597 'MULTILANG' AS attribute_datatype,

1598 a.length AS datatype_length,

1599 null OLD_IDPARENTOBJECT,

1600 null NEW_IDPARENTOBJECT,

1601 null OBJECTTYPE,

1602 null OLD_OWNER,

1603 null NEW_OWNER,

1604 null OLD_ORIGHTS,

1605 null NEW_ORIGHTS,

1606 null OLD_ARIGHTS,

1607 null NEW_ARIGHTS,

1608 null OPERATION,

1609 null OBJECTNOTE

1610 FROM objects o, attributes a, objecttypes ot, usermultilangvalueslog uml, systemdatavalues s

1611 WHERE o.idobjecttype = nIDObjectType and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

1612 AND

1613 (uml.id_object = o.id and uml.id_attribute = a.id)

1614

1615 UNION ALL

1616 --userstringvalueslog

1617 SELECT

1618 o.id AS id,

1619 uml.id_attribute AS new_idattribute,

1620 to_clob(uml.old_value) AS old_value,

1621 to_clob(uml.new_value) AS new_value,

1622 uml.old_serialnumber AS old_serialnumber,

1623 uml.new_serialnumber AS new_serialnumber,

1624 uml.modified_user AS modified_user,

1625 uml.modified_time AS modified_time,

1626 uml.type_of_operation AS type_of_operation,

1627 s.value AS attribute_note,

1628 'STRING' AS attribute_datatype,

1629 a.length AS datatype_length,

1630 null OLD_IDPARENTOBJECT,

1631 null NEW_IDPARENTOBJECT,

1632 null OBJECTTYPE,

1633 null OLD_OWNER,

1634 null NEW_OWNER,

1635 null OLD_ORIGHTS,

1636 null NEW_ORIGHTS,

1637 null OLD_ARIGHTS,

1638 null NEW_ARIGHTS,

1639 null OPERATION,

1640 null OBJECTNOTE

1641 FROM objects o, attributes a, objecttypes ot, userstringvalueslog uml, systemdatavalues s

1642 WHERE o.idobjecttype = nIDObjectType and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

1643 AND

1644 (uml.id_object = o.id and uml.id_attribute = a.id)

1645

1646 UNION ALL

1647 --userdatevalueslog

1648 SELECT

1649 o.id AS id,

1650 uml.id_attribute AS new_idattribute,

1651 to_clob(uml.old_value) AS old_value,

1652 to_clob(uml.new_value) AS new_value,

1653 uml.old_serialnumber AS old_serialnumber,

1654 uml.new_serialnumber AS new_serialnumber,

1655 uml.modified_user AS modified_user,

1656 uml.modified_time AS modified_time,

1657 uml.type_of_operation AS type_of_operation,

1658 s.value AS attribute_note,

1659 'DATE' AS attribute_datatype,

1660 a.length AS datatype_length,

1661 null OLD_IDPARENTOBJECT,

1662 null NEW_IDPARENTOBJECT,

1663 null OBJECTTYPE,

1664 null OLD_OWNER,

1665 null NEW_OWNER,

1666 null OLD_ORIGHTS,

1667 null NEW_ORIGHTS,

1668 null OLD_ARIGHTS,

1669 null NEW_ARIGHTS,

1670 null OPERATION,

1671 null OBJECTNOTE

1672 FROM objects o, attributes a, objecttypes ot, userdatevalueslog uml, systemdatavalues s

1673 WHERE o.idobjecttype = nIDObjectType and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

1674 AND

1675 (uml.id_object = o.id and uml.id_attribute = a.id)

1676

1677 UNION ALL

1678 --userclobvalueslog

1679 SELECT

1680 o.id AS id,

1681 uml.id_attribute AS new_idattribute,

1682 to_clob(uml.old_value) AS old_value,

1683 to_clob(uml.new_value) AS new_value,

1684 uml.old_serialnumber AS old_serialnumber,

1685 uml.new_serialnumber AS new_serialnumber,

1686 uml.modified_user AS modified_user,

1687 uml.modified_time AS modified_time,

1688 uml.type_of_operation AS type_of_operation,

1689 s.value AS attribute_note,

1690 'CLOB' AS attribute_datatype,

1691 a.length AS datatype_length,

1692 null OLD_IDPARENTOBJECT,

1693 null NEW_IDPARENTOBJECT,

1694 null OBJECTTYPE,

1695 null OLD_OWNER,

1696 null NEW_OWNER,

1697 null OLD_ORIGHTS,

1698 null NEW_ORIGHTS,

1699 null OLD_ARIGHTS,

1700 null NEW_ARIGHTS,

1701 null OPERATION,

1702 null OBJECTNOTE

1703 FROM objects o, attributes a, objecttypes ot, userclobvalueslog uml, systemdatavalues s

1704 WHERE o.idobjecttype = nIDObjectType and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

1705 AND

1706 (uml.id_object = o.id and uml.id_attribute = a.id)

1707

1708 UNION ALL

1709 --usernumbervalueslog

1710 SELECT

1711 o.id AS id,

1712 uml.id_attribute AS new_idattribute,

1713 to_clob(uml.old_value) AS old_value,

1714 to_clob(uml.new_value) AS new_value,

1715 uml.old_serialnumber AS old_serialnumber,

1716 uml.new_serialnumber AS new_serialnumber,

1717 uml.modified_user AS modified_user,

1718 uml.modified_time AS modified_time,

1719 uml.type_of_operation AS type_of_operation,

1720 s.value AS attribute_note,

1721 'CLOB' AS attribute_datatype,

1722 a.length AS datatype_length,

1723 null OLD_IDPARENTOBJECT,

1724 null OBJECTTYPE,

1725 null NEW_OBJECTTYPE,

1726 null OLD_OWNER,

1727 null NEW_OWNER,

1728 null OLD_ORIGHTS,

1729 null NEW_ORIGHTS,

1730 null OLD_ARIGHTS,

1731 null NEW_ARIGHTS,

1732 null OPERATION,

1733 null OBJECTNOTE

1734 FROM objects o, attributes a, objecttypes ot, usernumbervalueslog uml, systemdatavalues s

1735 WHERE o.idobjecttype = nIDObjectType and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

1736 AND

1737 (uml.id_object = o.id and uml.id_attribute = a.id)

1738

1739 UNION ALL

1740 --userdictionaryvalueslog

1741 SELECT

1742 o.id AS id,

1743 uml.id_attribute AS new_idattribute,

1744 to_clob(uml.old_value) AS old_value,

1745 to_clob(uml.new_value) AS new_value,

1746 uml.old_serialnumber AS old_serialnumber,

1747 uml.new_serialnumber AS new_serialnumber,

1748 uml.modified_user AS modified_user,

1749 uml.modified_time AS modified_time,

1750 uml.type_of_operation AS type_of_operation,

1751 s.value AS attribute_note,

1752 'CLOB' AS attribute_datatype,

1753 a.length AS datatype_length,

1754 null OLD_IDPARENTOBJECT,

1755 null NEW_IDPARENTOBJECT,

1756 null OBJECTTYPE,

1757 null OLD_OWNER,

1758 null NEW_OWNER,

1759 null OLD_ORIGHTS,

1760 null NEW_ORIGHTS,

1761 null OLD_ARIGHTS,

1762 null NEW_ARIGHTS,

1763 null OPERATION,

1764 null OBJECTNOTE

1765 FROM objects o, attributes a, objecttypes ot, userdictionaryvalueslog uml, systemdatavalues s

1766 WHERE o.idobjecttype = nIDObjectType and o.idobjecttype = ot.id and a.idobjecttype = ot.id and s.idparent = a.id AND S.IDLANGUAGE = nIdLanguage

1767 AND

1768 (uml.id_object = o.id and uml.id_attribute = a.id)

1769

1770 UNION ALL

1771

1772 SELECT

1773 o.id AS id,

1774 null AS new_idattribute,

1775 null AS old_value,

1776 null AS new_value,

1777 null AS old_serialnumber,

1778 null AS new_serialnumber,

1779 OL.MODIFIED_USER AS modified_user,

1780 OL.MODIFIED_TIME AS modified_time,

1781 null AS type_of_operation,

1782 null AS attribute_note,

1783 null AS attribute_datatype,

1784 null AS datatype_length,

1785 OL.OLD_IDPARENTOBJECT OLD_IDPARENTOBJECT,

1786 OL.NEW_IDPARENTOBJECT NEW_IDPARENTOBJECT,

1787 OL.NEW_OBJECTTYPE NEW_OBJECTTYPE,

1788 OL.OLD_OWNER OLD_OWNER,

1789 OL.NEW_OWNER NEW_OWNER,

1790 OL.OLD_ORIGHTS OLD_ORIGHTS,

1791 OL.NEW_ORIGHTS NEW_ORIGHTS,

1792 OL.OLD_ARIGHTS OLD_ARIGHTS,

1793 OL.NEW_ARIGHTS NEW_ARIGHTS,

1794 OL.TYPE_OF_OPERATION OPERATION,

1795 s.value OBJECTNOTE

1796 FROM objectslog OL, systemdatavalues s, objects o

1797 WHERE o.idobjecttype = nIDObjectType AND OL.ID_OBJECT = o.id and s.idparent = OL.ID_OBJECT AND S.IDLANGUAGE = nIdLanguage

1798 -- ORDER BY modified_time

1799

1800) B)A

1801 WHERE

1802 (A.r between ((nPageNumber-1)*nAmountPerPage+1) and (nPageNumber*nAmountPerPage))

1803 GROUP BY A.modified_time, A.MODIFIED_USER--, A.ID

1804 ORDER BY A.modified_time;

1805

1806 RETURN xDocument;

1807

1808 END pageAuditByObjectType;

1809

1810 FUNCTION addGroupRights(nIDObject objects.id%TYPE) RETURN XMLTYPE IS

1811 xDocument XMLTYPE;

1812 BEGIN

1813 SELECT

1814 XMLAgg(

1815 XMLElement('usergroup',

1816 XMLAttributes(ug.name AS group_name

1817,uug.grights AS 'group_default_right'

1818,oug.grights AS 'group_object_right')

1819)

1820)

1821 INTO xDocument

1822 FROM users u, usergroups ug, users_usergroups uug, objects_usergroups oug

1823 WHERE u.id = uug.id_user and uug.id_usergroup = oug.id_usergroup(+) and u.name = user

1824 and (oug.id_object = nIDObject or oug.id_object is null)

1825 and ug.id = uug.id_usergroup;

1826 RETURN xDocument;

1827 END addGroupRights;

1828

1829 FUNCTION showObjectRights(nIDObject objects.id%TYPE) RETURN XMLTYPE IS

1830 xDocument XMLTYPE;

1831 BEGIN

1832 SELECT

1833 XMLElement('object_rights',

1834 XMLAttributes(nIDObject AS id_object),

1835 XMLElement('owner', o.owner),

1836 XMLElement('owner_right', o.orights),

1837 XMLElement('usergroups',addGroupRights(nIDObject)),

1838 XMLElement('others_right', o.arights)

1839)

1840 INTO xDocument

1841 FROM objects o

1842 WHERE o.id = nIDObject;

1843

1844 RETURN xDocument;

1845 END showObjectRights;

1846

1847

1848 FUNCTION showFoundObjectRights RETURN XMLTYPE IS

1849 xDocument XMLTYPE;

1850 BEGIN

1851 SELECT

1852 XMLElement('Found_object_rights',

1853 XMLAgg(

1854 XMLElement('object',

1855 XMLAttributes(o.id AS id_object),

1856 XMLElement('owner', o.owner),

1857 XMLElement('owner_right', o.orights),

1858 XMLElement('usergroups',datamodification.addGroupRights(o.id)),

1859 XMLElement('others_right', o.arights)

1860)

1861)

1862)

1863 INTO xDocument

1864 FROM objects o, tempfoundobjects t

1865 WHERE o.id = t.id;

1866

1867 RETURN xDocument;

1868 END showFoundObjectRights;

1869

1870 end DATAMODIFICATION;

1871 /

Листинг пакета GENERATETABLES

Пакет GENERATETABLES предназначен для генерации пользовательских таблиц. Таким образом, будут пересозданы все объекты пользовательских схем: вначале будут созданы пользовательские таблицы, затем на них пользовательские представления. Код заголовка пакета и комментарии к заголовку пакета (см. таб.Пр.1.2.) ниже:

Таблица Пр1.2. Комментарии к заголовку пакета generateTables.

SQL> CREATE OR REPLACE PACKAGE generateTables IS

2

3 procedure doGenerate;

4 procedure createParentTables;

5 procedure insertDataParentTables;

6

7 END generateTables;

8 /

Package created

Небольшие комментарии по реализации тела пакета generateTables:

Таблица Пр1.3. Комментарии к телу пакета generateTables.

SQL> CREATE OR REPLACE PACKAGE BODY generateTables IS

2

3 procedure dropParentTables is

4 vSQLCode VARCHAR2(4000);

5 eTableNotExists EXCEPTION;

6 PRAGMA EXCEPTION_INIT(eTableNotExists, -00942);

7 begin

8 for curTables IN (select name from objecttypes) LOOP

9 BEGIN

10 vSQLCode := 'DROP TABLE '||curTables.Name;

11 EXECUTE IMMEDIATE vSQLCode;

12 DBMS_OUTPUT.put_line(vSQLCode);

13 EXCEPTION

14 WHEN eTableNotExists THEN

15 --catch the exception that the table doesn't exist and continue dropping the schema tables

16 DBMS_OUTPUT.put_line('TABLE '||curTables.Name||' doesn''t exist and therefore can''t be dropped');

17 END;

18 END LOOP;

19 end dropParentTables;

20

21 procedure createParentTables is

22 cSQLCode VARCHAR2(4000);

23 vName VARCHAR2(4000);

24 begin

25 for curTables IN (select name, id from objecttypes) LOOP

26 cSQLCode := 'CREATE TABLE '||curTables.Name||'(ID NUMBER PRIMARY KEY';

27 for curAttributes IN (select name, iddatatype, length from attributes where idobjecttype = curTables.id) LOOP

28 select name into vName from datatypes where id = curAttributes.Iddatatype;

29 cSQLCode := cSQLCode || ',' || curAttributes.Name || ' ' || vName;

30 if vName = 'VARCHAR2' THEN

31 cSQLCode := cSQLCode ||'(' || curAttributes.Length ||')';

32 END IF;

33 end loop;

34 cSQLCode := cSQLCode ||')';

35 --DBMS_OUTPUT.put_line(cSQLCode);

36 EXECUTE IMMEDIATE cSQLCode;

37 END LOOP;

38 end createParentTables;

39

40 procedure insertDataParentTables is

41 cSQLCode VARCHAR2(4000);

42 cTableCode VARCHAR2(2000);

43 vValue VARCHAR2(200);

44 begin

45 for curTables IN (select name, id from objecttypes) LOOP

46 cTableCode := 'INSERT INTO '||curTables.Name||'(ID';

47 for curAttributes IN (select name, iddatatype, length from attributes where idobjecttype = curTables.id) LOOP

48 cTableCode := cTableCode ||',' || curAttributes.Name;

49 end loop;

50 for curObjects IN (SELECT id from objects where idobjecttype = curTables.id) LOOP

51 cSQLCode := cTableCode || ') VALUES (' || curObjects.id;

52 for curAttributes IN (select name, iddatatype, length, id from attributes where idobjecttype = curTables.id) LOOP

53 select value into vvalue from userstringvalues where idobject = curObjects.id and idattribute = curAttributes.id;

54 cSQLCode := cSQLCode || ', ''' || vvalue || '''';

55 end loop;

56 cSQLCode := cSQLCode || ')';

57 EXECUTE IMMEDIATE cSQLCode;

58 END LOOP;

59 END LOOP;

60 COMMIT;

61 end insertDataParentTables;

62

63 procedure doGenerate IS

64 BEGIN

65 dropParentTables;

66 createParentTables;

67 insertDataParentTables;

68 END doGenerate;

69

70 END generateTables;

71 /

Package body created

Листинг пакета SEARCHDATA

Пакет SEARCHDATA предназначен для поиска объектов по набору атрибутов и заданным поисковых параметрам по этим атрибутам. Код заголовка пакета и комментарии к заголовку пакета (см. таб.Пр.1.4.) ниже:

Таблица Пр.1.4. Комментарии к заголовку пакета SEARCHDATA.

SQL> create or replace package SEARCHDATA as

2 function insertSearchParameters (nIDAttribute NUMBER,nIDCondition NUMBER,vValue VARCHAR2,nIDLanguage NUMBER) return NUMBER;

3 procedure clearSearchParameters;

4 function getObjectPageIndex (nIDObject NUMBER,nPageSize NUMBER) return NUMBER;

5 function doSearch (nIDObjectType NUMBER) return NUMBER;

6 function getSearchParamNum return NUMBER;

7 function getFoundObjectNum return NUMBER;

8 function getFoundObjectNum (nIDObjectType NUMBER) return NUMBER;

9 function getConditionType (nIdCondition NUMBER) return VARCHAR2;

10 end SEARCHDATA;

11 /

Листинг пакета SEARCHDATA приведен ниже. Отмечу, что в теле пакета есть private-функция getQueryText(31-57 строки). Эта функция предназначена для формирования кода запроса для поиска по объектам.

SQL> create or replace package body SEARCHDATA as

2

3 function insertSearchParameters (nIDAttribute NUMBER,nIDCondition NUMBER,vValue VARCHAR2,nIDLanguage NUMBER) return NUMBER as

4 BEGIN

5

6 INSERT INTO SEARCHPARAMETERS (IDATTRIBUTE,IDCONDITION,IDLANGUAGE,VALUE,IDSEARCHOBJECTTYPE)

7 SELECT nIDAttribute,nIDCondition,nIDLanguage, NVL(vValue, 0), ID

8 FROM attributes WHERE idobjecttype = nIDAttribute;

9 RETURN SQL%ROWCOUNT;

10

11 END;

12

13 procedure clearSearchParameters as

14 begin

15 DELETE FROM SEARCHPARAMETERS;

16 end;

17

18 function getObjectPageIndex (nIDObject NUMBER,nPageSize NUMBER) return NUMBER as

19 nPageNumber NUMBER;

20 nSerial NUMBER;

21 BEGIN

22 -- get object serial number

23 SELECT A.OBJECTSERIAL INTO nSerial FROM TEMPFOUNDOBJECTS A WHERE A.ID = nIDObject;

24 -- get page number

25 SELECT CEIL(COUNT(*)/nPageSize) INTO nPageNumber FROM TEMPFOUNDOBJECTS A

26 WHERE A.OBJECTSERIAL <= nSerial;

27 RETURN nPageNumber;

28 EXCEPTION WHEN NO_DATA_FOUND THEN RETURN -1;

29 END;

30

31 function getQueryText (nIDObjectType NUMBER) return CLOB as

32 vBaseTableName VARCHAR2(50) := ObjectsData.getObjectTypeSystemName(nIDObjectType);

33 cSQLText CLOB;

34 BEGIN

35

36 SELECT A.NAME

37 INTO vBaseTableName

38 FROM OBJECTTYPES A

39 WHERE A.ID = nIDOBjectType;

40

41 cSQLText := 'INSERT INTO TEMPFOUNDOBJECTS (ID,OBJECTSERIAL,HIERARCHICALSERIAL,FUSEOBJECT) ' ||

42 'SELECT ID,row_number() over (order by rnum) OBJECTSERIAL,row_number() over (order by rnum) HIERARCHICALSERIAL,1 FROM (SELECT ';

43 IF cSQLText IS NOT NULL THEN

44 -- hint for connect sort and search tables

45 cSQLText := cSQLText || '/*+ USE_NL(M) */ '

46 || vBaseTableName || '.ID, ROWNUM RNUM FROM ' || vBaseTableName || ',(' || cSQLText || ') M';

47 ELSE

48 cSQLText := cSQLText || vBaseTableName || '.ID, ROWNUM RNUM FROM ' || vBaseTableName;

49 END IF;

50

51

52 -- sorting

53 cSQLText := cSQLText || ' ORDER BY 1';

54 -- close query

55 cSQLText := cSQLText || ')';

56 RETURN cSQLText;

57 END;

58

59 function doSearch (nIDObjectType NUMBER) return NUMBER as

60 cSQLCode CLOB;

61 BEGIN

62

63 cSQLCode := getQueryText(nIDObjectType);

64

65 -- execute search query

66 EXECUTE IMMEDIATE cSQLCode;

67

68 RETURN SQL%ROWCOUNT;

69

70 END;

71

72 function getSearchParamNum return NUMBER as

73 nSearchParamNum NUMBER;

74 BEGIN

75 SELECT COUNT(*) INTO nSearchParamNum FROM SEARCHPARAMETERS A;

76 RETURN nSearchParamNum;

77 END;

78

79 function getConditionType (nIdCondition NUMBER) return VARCHAR2 as

80 cValue CONDITIONS.VALUE%TYPE;

81 BEGIN

82 SELECT C.VALUE INTO cValue FROM CONDITIONS C WHERE C.ID = nIDCondition;

83 RETURN cValue;

84 EXCEPTION WHEN NO_DATA_FOUND THEN

85 RETURN NULL;

86 END;

87

88 function getFoundObjectNum return NUMBER as

89 nObjectsNum NUMBER;

90 BEGIN

91 SELECT COUNT(*) INTO nObjectsNum

92 FROM TEMPFOUNDOBJECTS A WHERE A.FUSEOBJECT = 1;

93 RETURN nObjectsNum;

94 END;

95

96 function getFoundObjectNum (nIDObjectType NUMBER) return NUMBER as

97 nObjectsNum NUMBER;

98 BEGIN

99 SELECT

100 COUNT(*) INTO nObjectsNum

101 FROM

102 tempfoundobjects tf,

103 objects obj

104 WHERE

105 tf.id = obj.id

106 AND obj.idobjecttype = nIDObjectType

107 AND tf.fuseobject = 1;

108 RETURN nObjectsNum;

109 END;

110

111 end SEARCHDATA;

112 /

Листинг пакета SECURITYDATA

Пакет SECURITYDATA реализует политику тщательного контроля доступа, т.е. в зависимости от контекста подключения, предоставляет пользователю на объект права владельца или по умолчанию. Код заголовка пакета и комментарии к заголовку пакета (см. таб.Пр.1.5.) ниже:

Таблица Пр.1.5. Комментарии к заголовку пакета SECURITYDATA.

SQL> CREATE OR REPLACE PACKAGE SECURITYDATA

2 AS

3 FUNCTION checkRights(idObject NUMBER) RETURN NUMBER;

4 FUNCTION checkRights(idObject NUMBER, oright NUMBER, owner VARCHAR2, aright NUMBER) RETURN NUMBER;

5 END SECURITYDATA;

6 /

Package created

Код тела пакета SECURITYDATA:

Таблица Пр.1.6. Комментарии к телу пакета SECURITYDATA.

SQL> CREATE OR REPLACE PACKAGE BODY SECURITYDATA

2 AS

3

4

5 FUNCTION checkRights(idObject NUMBER) RETURN NUMBER

6 AS

7 nRight objectprivileges.id%TYPE;

8 vOwner objects.owner%TYPE;

9 BEGIN

10

11 SELECT owner INTO vOwner FROM objects WHERE id = idObject;

12

13 IF SYS_CONTEXT ('USERENV', 'SESSION_USER') = vOwner THEN

14 SELECT orights INTO nRight FROM objects WHERE id = idObject;

15 RETURN nRight;

16 END IF;

17

18 SELECT arights INTO nRight FROM objects WHERE id = idObject;

19 RETURN nRight;

20

21 END checkRights;

22

23 FUNCTION checkRights(idObject NUMBER, oright NUMBER, owner VARCHAR2, aright NUMBER) RETURN NUMBER

24 AS

25 BEGIN

26

27 IF SYS_CONTEXT ('USERENV', 'SESSION_USER')= owner THEN

28 RETURN oright;

29 END IF;

30

31 RETURN aright;

32

33 END checkRights;

34

35 END SECURITYDATA;

36 /

Приложение 2. Расширенный список тестовых примеров

В таблице Пр2.1. приведены тест-кейсы, которые проводились при разработке и тестировании базы данных. Возможные приоритеты тест-кейсов в порядке убывания их статуса: Blocker, Critical, Major, Minor, Trivial. К сожалению, ввиду относительно большого, проведенных тест-кейсов, здесь я размещаю только тест-кейсы у которых относительный приоритет Major и выше.

Таблица Пр2.1. Test cases.