Проектирование пространственной базы данных.

[Dimka]

С динамическими запросами хуже. Права кода тела хранимой процедуры совершенно не распространяются на тело динамического запроса Даже если внутри хранимой процедуры можно выполнять ALTER TABLE, нельзя будет это сделать через EXECUTE('ALTER TABLE ... '). Исполнение кода динамического запроса рассматривается как исполнение кода от имени пользователя. В этом случае с точки зрения прав нет никакой разницы между тем, выполняется ли ALTER TABLE пользователем напрямую или путём вызова хранимой процедуры.

В таком случае предлагаю обратить внимание на роли приложений баз данных. Может стоит подумать о разделении приложения на два: конфигуратор базы данных и всё остальное?

[remedius]

Например? У меня есть пользователь который создает свои таблички. Потом бац и он хочет сделать ее пространственной. А соответсвенно, чтобы сделать ее пространственной - необходимо  записать в его табличку FK на базовые таблички где хранится вся геометрия, к которым соответсвтенно он доступа по идеи не должен иметь. Как Вы предлагаете это сделать?

Спасибо

[remedius]

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

[Dimka]

Потом бац и он хочет сделать ее пространственной.

...У меня усиливается ощущение оторванности описываемого от реальности.

Во-первых, о системе какого масштаба идёт речь?

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

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

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

[remedius]

...У меня усиливается ощущение оторванности описываемого от реальности.
Во-первых, о системе какого масштаба идёт речь?

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

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

Извиняюсь, я привыкла объясняться таким языком.
Значит так, пока будет 2 типа пользователя. Непосредственно, тот который создает таблички, делает их пространственными и создает геометрию (по средством графического интерфейса), которая хранится на сервере во внутреннем представлении (внутреннее представление - набор таблиц). И второй пользователсь - который может только просматривать созданные первым таблички и выполнять какие-то запросы (например, показать mbr геометрии) по средствам того же графического приложения.
Немного о первом пользователе:  Он имеет право помимо создания своих таблиц на сервере, также сделать свою таблицу пространственной: что значит: вызвать хранимую процедуру на сервере, которая добавит столбец в пользовательскую таблицу, который будет являться FK на id геометрии в таблице на сервере, которая хранит всю геометрию (внутреннее представление). Далее, при просмотре, второй пользователь не будет видеть этот столбец (он ему фактически не нужен), а будет видеть уже геометрическую интерпретацию данной пространственной таблицы.
Так вот, по идеи даже первому пользователб нужно урезать права во внесении изменений в базовые таблицы (внутреннее пердставление геометрии на сервере).
вот тут и стоит проблема, описанная выше.
Все ли я описала?

[Dimka]

Можно разделить владельцев таблиц... По умолчанию таблица MS SQL Server принадлежит dbo, но можно явно задать владельца. Потом определить, что владелец имеет право выполнять любые действия только со своими таблицами. Так отделяется владелец таблиц ГИС (разработчик) от владельца пользовательских таблиц (конфигуратора). Ну а просто пользователь всей системы может быть ограничен в правах по сравнению с конфигуратором.

[remedius]

ДА, но как это решает проблему?
проблема: владелец пользовательских таблиц(пользователь первый), когда делает свою таблицу пространственной, записывает FK на таблицы ГИC, что конечно же у него не удается сделать!

[remedius]

Причем, не сам записывает - а вызывает хранимую процедуру, которая создает столбец c FK на таблицы ГИС. Но сделать это не удается, так как не хватает прав. Может дать ему права на редактирование таблиц ГИС (но правильно ли это? - по-моему нет:(

[Dimka]

Не понял.

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

И зачем пользователю права на таблицы ГИС, если он не редактирует структуру этих таблиц?

Далее, не совсем понимаю (точнее, совсем не понимаю), в чём заключается проблема с правами - что именно не работает?

[remedius]

Пользователю нужно пометить свою таблицу как пространственную. Как это он делает: вызывает хранимую процедуру, которая создает в пользовательской таблице колонку и помечает ее как FK на столбец одной из ГИС таблицы. Но получается, что прав у него не хватает на выполнение этой процедуры. Потому что она создает FK на таблиц ГИС.

[Dimka]

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

Могу предположить, что прав на ALTER оказалось недостаточно. В самом деле, GRANT ALTER даёт право модифицировать таблицу, но не устанавливать связи. Для установки связей используется GRANT REFERENCES - см. BOL на команду GRANT.

[remedius]

Могу предположить, что прав на ALTER оказалось недостаточно. В самом деле, GRANT ALTER даёт право модифицировать таблицу, но не устанавливать связи. Для установки связей используется GRANT REFERENCES - см. BOL на команду GRANT.

Спасибо. Все получилось! (да именно об этом я и говорила

Меня волнует еще один вопрос переносимости.
Если базовая часть ГИС у меня основывается на Хранимых процедурах (я пока пишу под SQL SERVER) есть ли смысл говорить о переносимости?
Например, мой печальный опыт изучения MYSQL за 30 мин, привел меня к выводу, что у него много чего не так, как у SQL SERVER - в отношении написания хранимых процедур. Ну а если говорить о других СУБД, могу предположить, что мои хранимые процедуры вообще не переносимы!?

[Dimka]

Ну а если говорить о других СУБД, могу предположить, что мои хранимые процедуры вообще не переносимы!?

Это вопрос: а) организации объектов СУБД (базы, таблицы, поля, пользователи, роли, группы и т.п.), б) синтаксиса.

Смотря что понимать под переносимостью. В общем случае, естественно, для переноса на другую СУБД необходимо будет перебрать весь SQL-код. Вопрос заключается в том, облегчает ли разделение "клиент-сервер" и использование хранимых процедур независимую модификацию клиента и сервера. Полагаю, что если умерить аппетиты, не пользоваться всеми возможностями (вроде return value), не усложнять решение, инкапсулировать разделение прав пользователей внутри СУБД, не пользоваться внешними хранимыми процедурами, планировать для клиентов использование каких-нибудь стандартных библиотек работы с данными (например, ODBC или ADO под Windows), можно построить достаточно универсальное решение, перенос которого на разные платформы будет иметь минимальную стоимость.

Да, ещё хорошо бы в архитектуре клиентского приложения выделить Data Layer, в котором инкапсулировать работу с БД.

[remedius]

Да, ещё хорошо бы в архитектуре клиентского приложения выделить Data Layer, в котором инкапсулировать работу с БД.

Уже выделен, и даже почти написан.

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

Спасибо

[x77]

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

[remedius]

x77,  проверка осуществляется "НА ВСЯКИЙ СЛУЧАЙ" - я всегдна так делаю. Если спросите почему- "Я не знаю"
Вообще грубо говоря, непосредственно между сервером и клиенским приложением у меня есть еще одна прослойка (работа с БД). Ну мало, ли, вдруг как-нить туда попадет от клиентского приложения левое название. Ибо, пока клиентское приложение пишу я. Потом вдруг кто-то друго захочет написать клиентское рпилоржение - я жу за него не могу отвечать. Или тут дело в другом?

[remedius]

Может конечно и не принято в БД проверять корректност данных, но меня просто приучили все и везде проверять и предусматривать:(

[x77]

remedius, давайте по порядку. есть база данных, представляющая собой набор взаимосвязанных сущностей и взаимодействий между ними. на уровне базы данных количество таблиц, вообще говоря, детерменировано и конечно. т.е. любой триггер или любая процедура "знает", какие таблицы есть в БД. это - низший уровень асбтракции. ваш вопрос подразумевает, что в базе "откуда ни возьмись" появляется новая таблица, о существовании которой процедуре может быть неизвестно. я себе этот момент представляю очень плохо. зачем в процедуру передавать имя таблицы параметром, почему процедура сама не знает, с какой таблицей она должна работать? даже если у вас таблицы создаются в процессе эксплуатации, ну так и процедуры, отвечающие за вставку/изменение/удаление данных создавайте вместе с этой таблицей, вот и всё. в Data Layer'е у вас будет проверка на возможность вызова тех или иных процедур ("нет ручек - нет конфеток"), а клиент вообще не будет знать ни про какие таблицы.

пояснить, что я имею в виду?

[x77]

грубо говоря, ваш вариант:

1. проверить существование процедуры
2. вызвать процедуру с именем таблицы
3. проверить существование таблицы
4. выполнить процедуру.

мой вариант:
1. проверить существование процедуры
2. выполнить процедуру.

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

[Dimka]

x77, лучше я поясню, что remedius имеет в виду

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

Другое дело, что remedius неестественно подходит к проблеме .

Если в хранимую процедуру передана неправильная таблица, хранимая процедура благополучно упадёт. Никакие REFERENCES на несуществующие таблицы не могут быть установлены. Это совершенно не нужно контролировать - сама СУБД проконтролирует. И нет никакой разницы, программист ли предусмотрительно валит хранимую процедуру в случае ошибки, либо это делает СУБД.

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

[x77]

dimka, а, я понял. мне показалось, что речь идёт о процедурах-"обёртках".

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

[x77]

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

[Dimka]

Честно говоря (в третий раз возвращаясь к этой теме), я до сих пор не вижу приложения. Точнее продукта.

Программный продукт - это инструмент решения пользователями продукта каких-то своих проблем/задач. Поэтому хорошо бы выделить этих пользователей, чтобы попытаться увидеть их проблемы/задачи, и то, как разрабатываемый продукт эти проблемы задачи решает/не решает/создаёт/мешает решать.

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

Архитектура: расширение пользовательской базы данных в виде таблиц и хранимых процедур на СУБД + клиентское(ие) приложение(я), обеспечивающее(ие) связывание пользовательских данных с ГИС-расширением и визуализацию данных.

Кто отвечает за базу данных? Администратор базы данных. Его функция - внедрение ГИС-расширения в пользовательскую базу данных и настройка связей между данными данных. Права у него административные, и он может всё. Спрашивается, для чего весь этот сыр-бор с правами, попытки спрятать таблицы, запретить доступ к ним и пр., если это на деле никак пользователя не ограничивает? Рядовой пользователь базы данных (не администратор) просто не сможет установить систему из-за нехватки прав, администратора система никак не ограничит.

Т.е. с практической точки зрения эта работа пустая и имеет смысл только с учебной точки зрения - познакомится с особенностями СУБД. Нет?

Далее, требования к пользовательской базе данных не описаны. Неявно полагается, что эта база данных "хорошая" - грамотно спроектирована, нормализована и т.д. На практике это чаще всего не так. Есть "правильные" базы данных, но чаще всего в корпоративных системах наблюдается хаос, "свалки данных", где с помощью специализированных продуктов ведутся "раскопки". И вполне может отсутствовать даже простейшая нормализация до 1-й формы (т.е. легко можно встретить поля вроде DocsID, хранящие значения вида "832,483,955" - где идентификаторы записаны в строчку через запятую).

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

Поэтому ряд вопросов:

1) Как во всё это добавлять столбцы со ссылками на объекты ГИС, чтобы гарантировать устойчивую работу, возможность установить правильные связи между пользовательскими объектами и объектами ГИС?
2) В случае добавления пользовательских данных кто должен обеспечивать связь этих данных с объектами ГИС? Это что, пользователю нужно писать свои хранимые процедуры? Или вручную с помощью приложения устанавливать связи своих новых объектов с объектами ГИС? Допустим, вручную, но если мы имеем распределённую базу данных, скажем, Россельхознадзора - систему учёта пересечения сельхозпродуктами границы страны, которая собирает данные со всех таможен, где за сутки с помощью репликаций в таблицы автоматически добавляются миллионы записей, никто вручную такую работу выполнять не сможет физически.

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

Так что ведение обсуждения по схеме:
- Мне надо вот такое техническое решение.
- Такое техническое решение невозможно.
- Что же делать?
заводит в тупик. Если некое техническое решение невозможно, нужно отказаться от решения и вернуться к проблеме - искать способ решения проблемы, а не средство реализации того или иного технического решения любыми путями. Но поиск решения проблемы как раз и невозможен, потому что сформулированной проблемы нет, постановки задачи тоже нет, поэтому и советовать нечего - возникают тупики.

P.S. На правах IMHO.

[Dimka]

Мне ещё вот какая мысль пришла. Довольно часто в крупных БД оперируют представлениями (views). Причём таблицы общие, а группы представления могут быть настроены для работы с разными группами пользователей (этакие "витрины данных" в миниатюре). Естественным желанием пользователей будет связать представления с ГИС. В рамках существующего решения такое, получается, невозможно. Вынос ключа в таблицы, в принципе, возможен. Но представления с агрегатами уже могут вызвать технические проблемы, если агрегирование происходит не по ключевым полям таблиц.

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

[x77]

 

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

[Dimka]

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

Ну... это далеко не "изначальный" вопрос Это, скорее, следствие Причём на него было дано 2 разных ответа.

Я же о причинах - это другая подтема .

[remedius]

x77, лучше я поясню, что remedius имеет в виду

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

Другое дело, что remedius неестественно подходит к проблеме .

Если в хранимую процедуру передана неправильная таблица, хранимая процедура благополучно упадёт. Никакие REFERENCES на несуществующие таблицы не могут быть установлены. Это совершенно не нужно контролировать - сама СУБД проконтролирует. И нет никакой разницы, программист ли предусмотрительно валит хранимую процедуру в случае ошибки, либо это делает СУБД.

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

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

[x77]

[remedius]

Честно говоря (в третий раз возвращаясь к этой теме), я до сих пор не вижу приложения. Точнее продукта.
...

dimka, Уффф.. мне бы Вас в качестве начальника, или хотя бы науч. руководителя....Щас начну плакаться. Но не в этом суть.

Да, это мое самое дырявое место: не умею я ставить ТЗ перед собой:( Так что, могу тока развести руки.
Но все же попробую объяснить все на пальцах:
Да, вроде в начале Вы все правильно написали (про програмный продукт, архитектура...и т.д.
Кто такой администратор, я думала, этот тот кто имеет право создавать таблички (не вручную!), устанавливать права, предоставлять данные таблички с данными пользователю и т.д. Но я считаю, никаких прав таблицы ГИС он не должен иметь. От сюда и зашел вопрос.  Да, на счет "Не вручную", имеет ввиду опять же предоставить интерфейс для создания таблиц (чем собчтвенно говоря я и занялась
Кто такой пользователь, ну в принципе понятно - проматривает данные, и возможно как-то с ними потом работает.
По поводу ввода непосредственно геометрии (я об этом тоже задумывалась, после чего пришла, что самое удобное это подсовывать тот же xml файл приложению с описанной геометрии, которое по средством предоставленных фукнций моего datalayer так называемого (по простому dll), которая в свою очередь используя хранимые процедуры внечет всю необходимую информацию в таблицы ГИС.
Для чего нужны пользовательские таблицы - вроде тоже понятно, чтобы связать свою информацию с данными ГИС.

[remedius]

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