ООП в реляционных СУБД

добавлено: 04 дек 15
понравилось:0
просмотров: 5175
комментов: 19

теги:

Автор: Old Nick

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

1. Генерация SQL-запросов. Зачастую запросы становятся универсальными и неэффективными. СУБД тормозит
2. Сильно увеличенный траффик между СУБД и клиентом.
3. Сложности в отладке.

Я предлагаю вашему вниманию совершенно другой подход, прямо, скажем так, противоположный.
Если системы маппинга являются расширением ООП в виде возможности сохранения состояния объектов в СУБД и автоматической генерацией запросов,
то я предлагаю расширить СУБД до возможностей ООП
Итак, что нам для этого нужно? Вспомним три кита, на которых стоит ООП.

1. Наследование
2. Инкапсуляция
3. Полиморфизм

Наследование

Начнем с наследования (сразу оговорюсь, я рассматриваю только вариант с одним единственным базовым классом Object, как это в Pascal, C# и Java).
Основной принцип как в маппинге, каждому классу соответствует одна таблица, каждому объекту запись в таблице. Значения полей объекта-наследник
хранятся в собственной таблице, значения полей родительского класса хранятся в таблице родительского класса. Связь записей таблиц происходит
один-к-одному по ключевому полю, являющемуся идентификатором объекта.
Возьмем для примера следующую иерархию наследования. Object -> Subject -> Person. Object -> Subject -> Company. Object ->Passport.
Object - базовый абстрактный класс, собственных экземпляров не имеет, но задает основные приципы поведения всех объектов системы
Subject - базовый абстрактный класс для всех хозяйствующих субъектов. Наследуется от Object.
Person - физическое лицо
Company - Юридическое лицо
Passport - Паспорт
Нам нужна таблица для объектов класса Object, она будет базовой в системе.

create table TObject
  OID     int identity(1, 1) primary key clustered, -- Идентификатор объекта
  Name    varchar(256),                             -- Название объекта системно
  Caption varchar(256),                             -- Пользовательское название объект
  Class   varchar(256),                             -- Имя класса, ссылка на таблицу ClassTre
  Deleted bit default 0                             -- Признак логического удаления объекта
)
go


Условимся сразу, что названия таблиц объектов будут начинаться с буквы T. Это удобно сразу двумя вещами
Первое, что это признак таблицы объектов в отличие от других вспомогательных таблиц
Второе, что исключает совпадение названия с зарезервированным словом.
Для класса Subject тоже нужна таблица

create table TSubject
(
  OID int primary key clustered
)
go


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

Класс Person

create table TPerson
(
  OID         int primary key clustered,
  PassportOID int
)
go


Класс Company

create table TCompany
(
  OID     int primary key clustered,
  Address varchar(256)
)
go


Класс Passport

create table TPassport
(
  OID        int primary key clustered,
  Serial     varchar(6),
  Number     varchar(6),
  Date       datetime,
  Department varchar(256),
)
go


Получается, что данные объекта разбросаны по разным таблицам. Как их собирать?
Можно запросом

select o.OID,
       o.Name,
       o.Caption,
       p.PassportOID,
       PassportCaption = ps.Caption  
  from TObject o
         join
       TSubject s
         on s.OID = o.OID
         join
       TPerson p
         on p.OID = s.OID
         left join
       TObject ps
         on ps.OID = p.PassportOID
  where o.OID = 123


Можно с помощью VIEW

create view VObject
as
  select *
    from TObject
    where Deleted = 0
go

create view VSubject
as
  select o.*
    from TSubject s
           join
         VObject o
           on o.OID = s.OID
go

create view VPassport
as
  select o.*,
         p.Serial,
         p.Number,
         p.Date,
         p.Department
    from TPassport p
           join
         VObject o
           on o.OID = p.OID
go

create view VPerson
as
  select s.*,
         p.PassportOID,
         PassportCaption = ps.Caption,
         PassportClass = ps.Class
    from TPerson p
           join
         VSubject s
           on s.OID = p.OID
           left join
         VPassport ps
           on ps.OID = p.PassportOID
go


Как видим, налицо явное наследование. Тут даже можно применить инкапсуляцию.

Инкапсуляция

Если убрать доступ со стороны пользователя к таблицам, а оставить доступ только к VIEW,
причем сделать VIEW редактируемыми с помощью INSTEAD OF TRIGGERS, то получится
100%-я инкапсуляция.

Пример триггеров INSTEAD OF

create trigger Person_Insert
on TPerson
for insert
as
  declare @OID int
  declare Cur cursor fast_forward for
    select Name, Caption, PassportOID
      from inserted
  open Cur
    fetch next from Cur into @Name, @Caption, @PassportOID
    while @@fetch_status = 0
    begin
      insert VSubject ( Name, Caption )
        select @Name, @Caption
     
      set @OID = @@IDENTITY
      insert TPerson ( OID, PassportOID )
        select @OID, @PassportOID
      update TObject set Class = 'Person' where OID = @OID
      fetch next from Cur into @Name, @Caption, @PassportOID
    end
  close Cur
  deallocate Cur
go

create trigger Subject_Insert
on TSubject
for insert
as
  declare @OID int
  declare Cur cursor fast_forward for
    select Name, Caption
      from inserted
  open Cur
    fetch next from Cur into @Name, @Caption
    while @@fetch_status = 0
    begin
      insert VObject ( Name, Caption )
        select @Name, @Caption
     
      set @OID = @@IDENTITY
      insert TSubject ( OID )
        select @OID
      update TObject set Class = 'Subject' where OID = @OID
      fetch next from Cur into @Name, @Caption
    end
  close Cur
  deallocate Cur
go

create trigger Object_Insert
on TObject
for insert
as
  declare @OID int
  declare Cur cursor fast_forward for
    select Name, Caption
      from inserted
  open Cur
    fetch next from Cur into @Name, @Caption
    while @@fetch_status = 0
    begin
      insert TObject ( Name, Caption, Class )
        select @Name, @Caption, 'Object'
     
      fetch next from Cur into @Name, @Caption
    end
  close Cur
  deallocate Cur
go


Нетрудно заметить, что все триггеры, кроме базового Object_Insert будут стандартными как под копирку
можно даже генератор кода триггера написать, чтобы не писать их каждый раз вручную.
Примеры триггеров Update и Delete приводить не буду, они намного проще, чем Insert, сами напишите.

Полиморфизм.

Для этого нам нужна таблица для хранения описания классов и их наследования.

create table ClassTree
(
  Class  varchar(256) primary key,
  Parent varchar(256) null
)
go


Собственно и всё. Это два основных поля для описания наследования. Остальные поля можно добавлять для хранения метаданных в зависимости от сложности системы. Только вот строить деревянные запросы по такой таблице раньше было не очень удобно, до того как в разных расширениях SQL-языков не появились разные конструкции для построения иерархических запросов. До этого я использовал следующую конструкцию.
На ряду с таблицей ClassTree создавал стаблицу ClassTreePath

create table ClassTreePath
  Class  varchar(256),
  Parent varchar(256),
  Level  int
)
go


Она очень похожа на таблицу ClassTree, но отличается полем Level, которое означает следующее - уровень связи между классами
Чтобы стало понятнее, рассмотрим наследование класса Person. В таблице ClassTree будут следующие записи:

№ Class Parent
------ ------- -------
1 Object NULL
2 Subject Object
3 Person Subject

В таблице ClassTreePath для записи №3 будут внесены несколько записей

№ Class Parent Level
------ ------- ------- -------
1 Person Person 0 - Означает связь класса Person с самим собой. Для чего это нужно покажу ниже
2 Person Subject 1 - Означает связь класса Person с родителем Subject
3 Person Object 2 - Означает связь класса person с дедом Object

И так для каждого класса. Из такой таблицы очень удобно получать запросом следующую информацию:
1. Выбрать всех предков класса, включая себя (Level = 0)
2. Выбрать всех потомков класса, включая себя (Level = 0)
Такие запросы работают очень быстро. К тому же можно сортировать по уровням связи.
А поскольку наследование меняется крайне редко, то перезаписывать такую таблицу будет хоть и накладно, но допустимо
Далее будет использовать таблицу ClassTreePath для полиморфизма.
Создадим простой виртуальный метод, который выводит сообщение

create procedure Object_ShowMessage_Virtual
  @OID int
as
  declare @Class    varchar(256),
          @ProcName varchar(256)
  select @Class = Class from TObject where OID = @OID -- Определяем класс объекта
  select top 1 @ProcName = o.name
    from ClassTreePath p
           join
         sysobjects o
           on o.xtype = 'P'
           and o.name = p.Parent + '_ShowMessage'
    where p.Class = @Class
    order by p.Level
  if @ProcName is not null
    exec @ProcName @OID -- Вызов процедуры для каждого класса
go

create procedure Object_ShowMessage
  @OID OID
as
  raiserror('Not implemented yet', 11, 1)
go

create procedure Person_ShowMessage
  @OID OID
as
  select Message = 'Это физ.лицо'
go


А теперь вызовите виртуальный метод для двух разных объектов

declare @OID int
set @OID = 5 -- идентификатор объекта типа Person
exec Object_ShowMessage_Virtual @OID


Процедура вернет Message => 'Это физ.лицо'
Вызовите то же самое для объекта типа Company

declare @OID int
set @OID = 7 -- идентификатор объекта типа Company
exec Object_ShowMessage_Virtual @OID


Процедура вернет исключение 'Not implemented yet'
Данный виртуальный метод просто находит ближайший реализованный метод
и выполняет его.
Есть другой тип виртуального метода, который находит все имплементации
виртуального метода и выполняет их в порядке наследования.
Такой тип метода может понядобиться, например, при реализации
метода сериализации, построения контекстного меню, описания полей и т.д.
То есть когда нужно не заменить родительский метод, а дополнить его.
Предположим, что мы хотим научить наши объекты выдавать на клиента метаданные.
Создадим виртуальный метод для базового класса Object

create procedure Object_GetMetaData_Virtual
  @OID int
as
  declare @Class    varchar(256),
          @ProcName varchar(256)
  select @Class = Class from TObject where OID = @OID -- Определяем класс объекта
  create table #Temp
  (
    Field     varchar(256),
    Caption   varchar(256),
    Type      varchar(256),
    Length    int default 0,
    Precision int default 0
  )
  declare Cur cursor fast_forward for
    select o.name
      from ClassTreePath p
             join
           sysobjects o
             on o.xtype = 'P'
             and o.name = p.Parent + '_GetMetaData'
      where p.Class = @Class
      order by p.Level desc
  open Cur
    fetch next from Cur into @ProcName
    while @@fetch_status = 0
    begin
      exec @ProcName @OID -- Вызов процедуры для каждого класса
      fetch next from Cur into @ProcName
    end
  close Cur
  deallocate Cur
  select * from #Temp
go

create procedure Object_GetMetaData
  @OID OID
as
  insert #Temp ( Field, Caption, Type, Length )
    select 'Name', 'Системное имя', 'String', 256
  insert #Temp ( Field, Caption, Type, Length )
    select 'Caption', 'Наименование', 'String', 256
go

create procedure Person_GetMetaData
  @OID OID
as
  insert #Temp ( Field, Caption, Type )
    select 'PassportOID', 'Паспорт', 'int'
go


В следующей статье напишу как реализовать в БД события, синхронные и асинхронные

Комментарии


  • Ужас... ради сомнительной идеи воротить курсоры в триггерах и вообще это всё... Автору предлагаю представить количество подобного говнокода в системе хотя бы с 1000 таблиц и более-менее сложной логикой (нечто большее, чем просто CRUD).
    Кстати, до кода триггеров написано instead of, а в самом коде обычные триггеры.
    Итог: автор задекларировал идею, но сам на практике не использовал. Ибо на более-менее серьёзных проектах это всё выльется а) в невозможность поддержки из-за чрезмерной сложности кода, которой могло и не быть (особенно умиляют пляски с триггерами), б) в дичайшие тормоза, т.к. программный код специально ограничивает возможности СУБД по работе с множествами строк.
    Если мыслишь как ООПшник, не пытайся этот подход применить к БД, тут совсем по-другому всё работает.

  • Я уж лет 8-10 использую такой подход и тормозов не замечал. Тормоза в СУБД не из-за этого. Это во-первых.
    Во-вторых что за объекты Вы предлагаете скопом обрабатывать для ускорения? Документы? Предположим что у нас есть 1000 документов в одной вьюхе. Причем поскольку у нас есть наследование, то скорее всего это не однотипные документы, а весьма разные и обрабатывать их нужно по разному. Кроме того, операция CRUD предполагает 4 вида действия, давайте рассмотрим их подробно и по отдельности.
    1. Выборка - тут триггер и курсоры никак не влияют. Думаю, данную операцию рассматривать смысла нет.
    2. Вставка - Вы предполагаете 1000 документов вставлять скопом? Трудно представить. Документ вводит оператор и вводит по одному, потом обрабатывает, потом проводит. Затем берется за следующий документ. При этом все документы разного типа и проводятся по разному, поэтому курсор практически всегда состоит из одной записи.
    Но если вдруг приспичит вставить сразу 1000 документов, то для этого нужно будет написать специальный скрипт и вставлять небольшими порциями. Либо сразу вставлять в таблицы, если логика не нужна.
    3. Правка - опять трудно представить ситуацию когда нужно в пакете из 1000 документов что-то поменять скопом. Дату? Но тогда произойдет перепроведение документов. Валюту? Тоже вряд ли без перепроводок обойдется. Так что же?
    4. Удаление. В нормальных системах физическое удаление бывает редко, чаще всего когда неправильно завели какой-либо объект (документ, элемент справочника), но тогда и удаляется сразу по одному. Если удаление логическое, то оно также сопровождается отработкой логики, привязанной к удаляемой сущности. Причем у каждого типа сущности своя логика и скопом обрабатывать это вряд ли получится.
    Что до кода триггера, то я его писал из памяти и в упрощенном виде. У меня триггеры посложнее и генерируются кодогенератором, я уже и не помню как они выглядят.
    В-третьих, кроме объектов в СУБД еще есть обычные реляционные данные, например, многострочная часть документа. Эти данные обрабатываются не по одной записи, а скопом.
    В-четвертых, такой подход предназначен в основном для учетных систем: бухгалтерских, складских, производственных.

  • > Я предлагаю вашему вниманию совершенно другой подход, прямо, скажем так, противоположный.

    А я предлагаю посмотреть в сторону мультимодельной СУБД Cachй, в которой все вышеозначенные проблемы давно решены.
    Если что, мы рядом, в соседней ветке: https://www.sql.ru/forum/cache

    Старый сайт: http://archive.intersystems.ru/cache/index.html
    Новый сайт: http://www.intersystems.com/ru/our-products/cache/cache-overview/

  • Спасибо, с СУБД знаком, даже тестовое приложение делал. Дорогое удовольствие, поэтому предлагаю более дешевую альтернативу

  • у вас наследование фактически это http://design-pattern.ru/patterns/class-table-inheritance.html
    однако я бы н стал реализовывать абстрактные сущности в реальной таблице и применил бы примерно это: http://design-pattern.ru/patterns/concrete-table-inheritance.html

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

    как 1С-ник, хотел терминами 1С написать, но не буду ))

  • Поверьте, пробовал разные варианты. Поэтому описал самый удобный и быстрый. Многострочная часть оформляется в виде таблицы многие-к-одному, совсем по SQL-ному, например, так
    create table TDocument_Rows
    (
    OID int,
    ProductOID int,
    Quantity int,
    primary key ( OID, ProductOID )
    )
    go

  • Просто у вас в итоге TObject станет самым слабым местом - будет постоянно читаться и блокироваться.
    По табличным частям объектов - неплохо было бы их удалять при удалении сущности. Ну и вообще нет ни слова про внешние ключи.

    Главное: нужен движок, который будет генерировать все эти метаданные

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

  • Одни словом, нет ограничений для развития. Насколько фантазии хватит.

  • блин... да ты "бояны" шлешь из своего бложика

  • "Я уж лет 8-10 использую такой подход и тормозов не замечал."
    А пользователи замечали? Вы можете их никогда не заметить, а пользователи могут тихо материться.

    "Тормоза в СУБД не из-за этого. Это во-первых."
    Да, причин для тормозов может быть вагон и маленькая тележка. Просто это может быть одной из них.

    "Во-вторых что за объекты Вы предлагаете скопом обрабатывать для ускорения? Документы?"
    Что угодно. Документ - понятие широкое. Может быть, например, подгрузка и обработка данных из внешних систем. Может быть конкурентная работа 100+ пользователей с одной таблицей.

    "Предположим что у нас есть 1000 документов в одной вьюхе."
    Предмоложим, не 1000, а 10000000. Логика та же?
    "Причем поскольку у нас есть наследование, то скорее всего это не однотипные документы, а весьма разные и обрабатывать их нужно по разному."
    Допустим, мы работаем не просто с документами, а вообще с разными сущностями, которые и близко друг на друга не похожи. При этом они так или иначе друг с другом связаны, и этих сущностей, например, 1000. Т.е., например, у меня в медицинской системе есть вроде как отдельный модуль отдела кадров, но одновременно он завязан на персонифицированный учёт оказания медицинских услуг (кто оказал) и на то, какое отделение за какую услугу сколько получит. Это лишь 1 из 1000+ примеров.

    "Кроме того, операция CRUD предполагает 4 вида действия, давайте рассмотрим их подробно и по отдельности."
    Я говорил о том, что бывает нечто более сложное, чем CRUD, читайте внимательнее. Так что рассматривать не будем.

    "Что до кода триггера, то я его писал из памяти и в упрощенном виде. У меня триггеры посложнее и генерируются кодогенератором, я уже и не помню как они выглядят."
    Вот представьте, что в бизнес-логике что-то изменилось, и кодогенератор не может наговнокодить хотелку, и срочно нужно залезть и править ручками. Или разобраться с возникшей вдруг проблемой. В итоге нужно проанализировать не только хранимки, но и триггеры, и всё это как-то связать между собой, учитывая то, что триггеры вносят изменения в другие таблицы, на которых тоже навешаны триггеры и т.д. (просто АД!) Ещё хуже, если придёт новый сотрудник, и возникшая проблема свалится на него. На наличие триггеров он посмотрит в последнюю очередь.

    "В-третьих, кроме объектов в СУБД еще есть обычные реляционные данные, например, многострочная часть документа. Эти данные обрабатываются не по одной записи, а скопом."
    Вот я и говорю, что бывает не просто 1 таблица с документами, а, например, 5000 таблиц. И не всегда там простейшая логика.

    "В-четвертых, такой подход предназначен в основном для учетных систем: бухгалтерских, складских, производственных."
    Я тоже работаю с учётной системой, но в медицине. И там такой подход просто не прокатит. Застрелишься уже через пару месяцев.

  • Да уж, Сид, у Вас явно проблемы! Может Вам бросить программирование? Даже комментировать не охота :-)
    Вы просто не умеете их готовить (с)

  • Old Nick, нет уж)) Как раз у меня очень вкусно получается готовить, и работодатель это щедро оценивает, т.к. очень быстро получает нужный результат. Просто у Вас ещё не было в работе более-менее сложной системы именно с точки зрения бизнес-логики, поэтому и такой наивный подход к разработке.
    А с курсорами завязывайте, они в 99,9% случаев не нужны.

  • Добрый день, Old Nick!

    Я работаю в холдинге, в котором очень похожий фреймворк уже много лет используется как минимум в трех известных мне крупных проектах. Над одним из них я работаю - логистическая система.
    Платформа разработана на тех-же инструментах - SQL Server + Delphi. Вот реальные цифры с боевой системы: в таблице dbo.Object показатель row count = 340500847, размер базы = 2338417Mb - полет нормальный!
    Подтверждаю что уровень тормозов напрямую зависит от степени криворукости разработчиков.
    К сожалению на этом фреймворке не используется кодогенерация, и некоторые решения меня не устраивают. Поэтому я уже 2 года в личное время разрабатываю более эффективный объектно-реляционный фреймворк.. но это уже другая история.

    Если Вам интересно

  • А сколько пользователей работает с системой? Если не секрет, то что за контора? Система не ОНТАРИО случайно?

  • В таблице dbo.AppUser показатель row count = 3860, это только по нашему проекту. Название компании на всякий случай не буду публично указывать. Система не связана с ОНТАРИО. По моим сведениям похожие объектные системы (с одним предком, написанном еще на Oracle) используются еще минимум в двух крупных компаниях.

  • >>VRafael
    >>Название компании на всякий случай не буду публично указывать.

    тоже мне бином Ньютон.
    Всем давно известно, что это Ozon
    первая ссылка на этом сайте при поиске по dbo.Object сразу приводит к
    https://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=1161972&msg=17785417

  • Работал в сопровождении такой (похожей) системы:
    1. Это реально пиздец!!!!
    -- Тормозит (простенькие отчеты с агрегацией делаются намногоООООО дольше чем в классической системе), сохранение одной строки доходило до 3 минут!
    -- Индексы очень сложно накручивать
    -- Дикая борьба с блокировками
    2. Нет контроля целостности данных
    -- Мало того что отчеты тормозят так они еще и хрень могут выводить! Очень замечательная реакция пользователей :))
    3. Сложно дорабатывать
    -- Доделать какой нибудь класс (добавить новый атрибут или изменить расчет суммы в зависимости от другого класса) = Выполнимая но очень тяжелая задача, не исключающая множества багов
    4. Для тестирования работы такой СУБД надо писать отдельную программу! которую еще надо постоянно переписывать под изменения сделанные в базе - попробуйте объяснить эти трудозатраты менеджеру :)))
    5. из за того что с такой базой сложно работать, отладка отчетов это возня с куче процедур, требуется множество изменений ,которые, как водятся нигде не документируются, новому сотруднику надо долго вьезжать в такую не стандартную базу --> результат = требуется много времени, проект убыточнее чем в обычной РБД

    Никому не советую!
    если только не хотите за эти мучения получить в раю вилу с фонтаном - как святому мученику :))

  • А почему, Игорь, Вы решили что это связано с ООП?



Необходимо войти на сайт, чтобы оставлять комментарии