(SQL) SQL (2010 год)

Пример обьектно-реляционного проектирования структуры данных в PostgreSQL

В отличие от классических реляционных СУБД, PostgreSQL является обьектно-реляционной.

Это несколько удивительно для программистов с опытом программирования MS SQL, однако и MS SQL не является в точности реляционной СУБД. Во-первых, там можно хранить в полях XML, а во вторых собственные CLR-типы. Фишка, однако заключается в том, CLR доступно лишь в самой дорогой версии MS SQL и поэтому на практике бесполезно. Это и невероятно дорого и на обычных публичных хостингах типа Паркинга вам никто не даст загрузить свои обьекты в SQL, несмотря на то, что там установлены дорогие версии SQL. Остается только личный выделенный сервер (со всеми вытекающими накладными расходами).

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

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

Каждый таблица, вьюшка и другие обьекты СУБД являются теми субьектами, из которых могут строится другие обьекты СУБД. В данном случает моя вьюшка CurrentState построена на двух элементарных типах - GUID, Timestamp и семи агрегатных типах, основанных на других вьюшках:


   1:  CREATE OR REPLACE VIEW "CurrentState" AS 
   2:   SELECT '139a7882-cf95-7c44-ac64-df4d18614cad'::uuid AS "TerminalID", now() AS now, 
   3:  ( SELECT "ИмпортПрименяемостиCount" AS "ИмпортПрименяемостиCount"  FROM "ИмпортПрименяемостиCount") AS "ИмпортПрименяемости", 
   4:  ( SELECT "ИмпортСкладскихОстатковCount" AS "ИмпортСкладскихОстатковCount" FROM "ИмпортСкладскихОстатковCount") AS "ИмпортСкладскихОстатков", 
   5:  ( SELECT "ИмпортТоваровCount" AS "ИмпортТоваровCount" FROM "ИмпортТоваровCount") AS "ИмпортТоваров", 
   6:  ( SELECT "TableCount" AS "TableCount" FROM "TableCount") AS "TableCount", 
   7:  ( SELECT "AllDiskCount" AS "AllDiskCount"  FROM "AllDiskCount") AS "AllDiskCount", 
   8:  ( SELECT "ЗаказыCount" AS "ЗаказыCount" FROM "ЗаказыCount") AS "ЗаказыCount", 
   9:  ( SELECT "ErrorCount" AS "ErrorCount" FROM "ErrorCount") AS "ErrorCount";

Это позволяет мне не заморачиваться на деталях каждого обьекта. Я на этом уровне абстракции не хочу перечислять все поля, которые хранит например вьюшка TableCount. Все содержимое вьюшки TableCount является одним полем во вьюшке CurrentState:



Я даже могу упаковать все содержимое вьюшки CurrentState в одно поле и оперировать этим полем целиком:



Могу вытащить из вьюшки один конкретный нужный мне тип:



Отличие обьектного подхода от реляционного в том, что при объектном подходе я могу не заморачиваться на том, что ТОЧНО лежит в обьекте TableCount - ну лежат там какие-то счетчики таблиц и все.



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

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



Отображение этой формы осуществляется вот таким простейшим кодом - вы можете увидеть как обращаться к таким сложным агрегированным полям в своем коде:


   1:      Private Sub TerminalStatus_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
   2:          Try
   3:              Dim Db As New SQL_Postgres
   4:              Db.CheckConnect()
   5:              Dim RDR1 As Npgsql.NpgsqlDataReader = Db.PG.ExecRDR("select * from ""CurrentState""")
   6:              If RDR1.Read Then
   7:                  lTerminalID.Text = RDR1("TerminalID").ToString
   8:                  lDate.Text = RDR1("now")
   9:                  Dim ИмпортПрименяемости As String()
  10:                  Dim ИмпортСкладскихОстатков As String()
  11:                  Dim ИмпортТоваров As String()
  12:                  Dim TableCount As String()
  13:                  Dim AllDiskCount As String()
  14:                  Dim ВсеЗаказы As String()
  15:                  Dim ErrorCount As String()
  16:                  ИмпортПрименяемости = RDR1("ИмпортПрименяемости").ToString.Replace("(", "").Replace(")", "").Split(",")
  17:                  ИмпортСкладскихОстатков = RDR1("ИмпортСкладскихОстатков").ToString.Replace("(", "").Replace(")", "").Split(",")
  18:                  ИмпортТоваров = RDR1("ИмпортТоваров").ToString.Replace("(", "").Replace(")", "").Split(",")
  19:                  TableCount = RDR1("TableCount").ToString.Replace("(", "").Replace(")", "").Split(",")
  20:                  AllDiskCount = RDR1("AllDiskCount").ToString.Replace("(", "").Replace(")", "").Split(",")
  21:                  ВсеЗаказы = RDR1("ЗаказыCount").ToString.Replace("(", "").Replace(")", "").Split(",")
  22:                  ErrorCount = RDR1("ErrorCount").ToString.Replace("(", "").Replace(")", "").Split(",")
  23:                  limport1.Text = ИмпортПрименяемости(0)
  24:                  limport2.Text = ИмпортПрименяемости(1)
  25:                  limport7.Text = ИмпортПрименяемости(2)
  26:                  limport8.Text = ИмпортПрименяемости(3)
  27:                  limport3.Text = ИмпортСкладскихОстатков(0)
  28:                  limport4.Text = ИмпортСкладскихОстатков(1)
  29:                  limport9.Text = ИмпортСкладскихОстатков(2)
  30:                  limport10.Text = ИмпортСкладскихОстатков(3)
  31:                  limport5.Text = ИмпортТоваров(0)
  32:                  limport6.Text = ИмпортТоваров(1)
  33:                  limport11.Text = ИмпортТоваров(2)
  34:                  limport12.Text = ИмпортТоваров(3)
  35:                  lkol1.Text = ВсеЗаказы(4)
  36:                  lkol2.Text = ВсеЗаказы(5)
  37:                  lkol3.Text = ВсеЗаказы(0)
  38:                  lkol7.Text = ВсеЗаказы(1)
  39:                  lkol4.Text = ВсеЗаказы(6)
  40:                  lkol5.Text = ВсеЗаказы(7)
  41:                  lkol6.Text = ВсеЗаказы(2)
  42:                  lkol8.Text = ВсеЗаказы(3)
  43:                  lDiskCount.Text = RDR1("AllDiskCount")
  44:                  Lcount.Text = RDR1("TableCount")
  45:                  lerr1.Text = ErrorCount(2)
  46:                  lerr2.Text = ErrorCount(3)
  47:                  lerr3.Text = ErrorCount(0)
  48:                  lerr4.Text = ErrorCount(1)
  49:              End If
  50:              RDR1.Close()
  51:              Me.Show()
  52:          Catch ex As Exception
  53:              MsgBox(ex.Message)
  54:          End Try
  55:      End Sub

Этот код увеличился бы по длине минимум впятеро, если бы мне полностью пришлось бы вычивать каждый счетчик из отдельного поля. А так я вычитал все из базы в семь строчек (15-22).

Таблица для периодического сохранения состояния терминала (CurrentState) выглядит вот так - она построена в точности на тех же типах, что и вьюшка CurrentState - и содержит семь агрегированных полей, а не пятьдесят элементарных полей:


   1:  CREATE TABLE "TerminalMonitor"
   2:  (
   3:    i serial NOT NULL,
   4:    crdate timestamp without time zone,
   5:    "TerminalID" uuid NOT NULL,
   6:    "Data" timestamp without time zone,
   7:    "ИмпортПрименяемости" "ИмпортПрименяемостиCount",
   8:    "ИмпортСкладскихОстатков" "ИмпортСкладскихОстатковCount",
   9:    "ИмпортТоваров" "ИмпортТоваровCount",
  10:    "TableCount" "TableCount",
  11:    "AllDiskCount" "AllDiskCount",
  12:    "ЗаказыCount" "ЗаказыCount",
  13:    "ErrorCount" "ErrorCount",
  14:    CONSTRAINT "TerminalMonitor_pkey" PRIMARY KEY (i)
  15:  )
  16:  WITH (
  17:    OIDS=FALSE
  18:  );
  19:  ALTER TABLE "TerminalMonitor" OWNER TO postgres;

Сокращается весь код - например в процедуре локального сохранения можно опять упомянуть всего семь агрегатов, а не пятьдесят отдельных элементарных полей:


   1:  CREATE FUNCTION "LocalMonitoring"() RETURNS void
   2:      LANGUAGE sql
   3:      AS $$
   4:  insert into "TerminalMonitor" 
   5:  ("Data",
   6:  "TerminalID",
   7:  "ИмпортПрименяемости_i",
   8:  "ИмпортПрименяемости_ДатаИмпорта",
   9:  "ИмпортСкладскихОстатков_i",
  10:  "ИмпортСкладскихОстатков_ДатаИмпо",
  11:  "ИмпортТоваров_i",
  12:  "ИмпортТоваров_ДатаИмпорта",
  13:  "ЗаказыДисков_i",
  14:  "ЗаказыШин_i")
  15:  Select now,
  16:  (select cast("TerminalID" as character varying) from "CurrentState"),
  17:  "ИмпортПрименяемости_i",
  18:  "ИмпортПрименяемости_ДатаИмпорта",
  19:  "ИмпортСкладскихОстатков_i",
  20:  "ИмпортСкладскихОстатков_ДатаИмпо",
  21:  "ИмпортТоваров_i",
  22:  "ИмпортТоваров_ДатаИмпорта",
  23:  "ЗаказыДисков_i",
  24:  "ЗаказыШин_i" from "CurrentState";
  25:  $$;

Точно так же сокращаются все прочие операции например операция передачи этого состояния на сервер тоже становится в три раза короче, чем с плоским перечислением всех пятидесяти полей:


   1:  CREATE OR REPLACE FUNCTION "TerminalMonitoring"(character varying)
   2:    RETURNS integer AS
   3:  $BODY$
   4:  DECLARE
   5:      ConnectionString ALIAS FOR $1;
   6:      str1  character varying(4000);
   7:   
   8:          "_TerminalID" character varying(250);
   9:      _now character varying(250);
  10:      "_ИмпортПрименяемости"  character varying(250);
  11:      "_ИмпортСкладскихОстатков" character varying(250);
  12:      "_ИмпортТоваров" character varying(250);
  13:      "_Table" character varying(250);
  14:      "_AllDisk"  character varying(250);
  15:      "_Заказы" character varying(250);
  16:      "_Debug" character varying(250);
  17:      
  18:  BEGIN
  19:  --select "TerminalMonitoring"('host=10.10.10.3 dbname=Disk user=postgres password=ffffff');
  20:   
  21:      Select 
  22:          "TerminalID",
  23:          now,
  24:          "ИмпортПрименяемости", 
  25:          "ИмпортСкладскихОстатков", 
  26:          "ИмпортТоваров", 
  27:          "TableCount", 
  28:          "AllDiskCount", 
  29:          "ВсеЗаказы", 
  30:          "Debug1"
  31:      into 
  32:          "_TerminalID",
  33:          _now,
  34:          "_ИмпортПрименяемости",
  35:          "_ИмпортСкладскихОстатков",
  36:          "_ИмпортТоваров",
  37:          "_Table",
  38:          "_AllDisk",
  39:          "_Заказы",
  40:          "_Debug"
  41:      from "CurrentState";
  42:   
  43:   
  44:      str1:=
  45:          'insert into "TerminalMonitor" 
  46:          (crdate, 
  47:          "TerminalID", 
  48:          "Data", 
  49:          "ИмпортПрименяемости",
  50:          "ИмпортСкладскихОстатков", 
  51:          "ИмпортТоваров", 
  52:          "TableCount", 
  53:          "AllDiskCount", 
  54:          "ВсеЗаказы", 
  55:          "Debug1")
  56:      VALUES ( now(),'
  57:      || '''' ||    "_TerminalID" || '''::uuid,'
  58:      || '''' ||    _now || '''::timestamp without time zone,'
  59:      || '''' ||    "_ИмпортПрименяемости" || ''','
  60:      || '''' ||    "_ИмпортСкладскихОстатков" || ''','
  61:      || '''' ||    "_ИмпортТоваров" || ''','
  62:      || '''' ||    "_Table" || ''','
  63:      || '''' ||    "_AllDisk" || ''','
  64:      || '''' ||    "_Заказы"  || ''','
  65:      || '''' ||    "_Debug"   || ''');';
  66:   
  67:      --insert into "TerminalError"(crdate,txt) values (now(),SQLSTATE || ' : ' || SQLERRM) ;
  68:   
  69:      if (str1 is NULL) then
  70:   
  71:          RAISE EXCEPTION 'string is null';
  72:          return 2;
  73:   
  74:      else    
  75:          perform dblink_exec(ConnectionString, str1,false);
  76:          return 0;
  77:      end if;
  78:   
  79:  exception
  80:      when others then
  81:      insert into "TerminalError"(crdate,txt) values (now(),SQLSTATE || ' : ' || SQLERRM) ;
  82:      return -1;
  83:  END;
  84:  $BODY$
  85:    LANGUAGE 'plpgsql' VOLATILE
  86:    COST 100;
  87:  ALTER FUNCTION "TerminalMonitoring"(character varying) OWNER TO postgres;

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

Например свойства стандартного профиля ASP.NET хранятся в MS SQL именно в виде сложного обьекта - примерно как описано выше. Только в PostgreSQL это естественный подзод к проектированию - а в MS SQL реально распарсить сложные хранимые обьекты на уровне SQL возможно только с помощью сборок. Пример такой сборки для парсинга ASP.NET-профилей я опубликовал - Сборка для работы с данными стандартных ASP.NET-профилей на уровне SQL.

Надеюсь, мне удалось убедить в полезности обьектного подхода к проектированию СУБД. Узнать больше о PostgresqL вы можете здесь Используем PostgreSQL вместо MS SQL в проектах на NET и ASP.NET.



Comments ( )
<00>  <01>  <02>  <03>  <04>  <05>  <06>  <07>  <08>  <09>  <10>  <11>  <12>  <13>  <14>  <15>  <16>  <17>  <18>  <19>  <20>  <21>  <22>  <23
Link to this page: //www.vb-net.com/PostgreSQL_Object/index.htm
<SITEMAP>  <MVC>  <ASP>  <NET>  <DATA>  <KIOSK>  <FLEX>  <SQL>  <NOTES>  <LINUX>  <MONO>  <FREEWARE>  <DOCS>  <ENG>  <CHAT ME>  <ABOUT ME>  < THANKS ME>