Пример обьектно-реляционного проектирования структуры данных в 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.
)
|
|