Создание учетных данных
В этом разделе описывается создание учетных данных в SQL Server с помощью SQL Server Management Studio или Transact-SQL.
Учетные данные позволяют пользователям проверки подлинности SQL Server иметь удостоверение за пределами SQL Server. Это в первую очередь необходимо для выполнения программного кода в сборках с набором разрешений EXTERNAL_ACCESS. Учетные данные также можно использовать, если пользователю проверки подлинности SQL Server требуется доступ к ресурсу домена, например расположению файла для хранения резервной копии.
Учетные данные можно сопоставить с одним именем входа SQL Server, а имя входа SQL Server можно сопоставить только с одним учетным данными одновременно. После создания учетных данных в окне Свойства имени входа (страница «Общие») сопоставьте их с именем входа.
Перед началом
Ограничения
- Если для поставщика учетные данные не сопоставлены с учетными данными для входа, используются учетные данные, сопоставленные с учетными данными для входа в службу SQL Server.
- Имени входа может быть сопоставлено несколько учетных данных, если они используются для отдельных поставщиков. У каждого поставщика должен быть только один набор учетных данных, сопоставленных одному имени входа. Одни и те же учетные данные могут быть сопоставлены нескольким именам входа.
Безопасность
Разрешения
Требует разрешения ALTER ANY CREDENTIAL для создания или изменения учетных данных и разрешения ALTER ANY LOGIN для сопоставления имени входа с учетными данными.
Использование среды SQL Server Management Studio
Создание учетных данных
- В обозревателе объектов раскройте папку Безопасность .
- Щелкните правой кнопкой мыши папку Учетные данные и выберите команду Создать учетные данные. .
- В диалоговом окне Создание учетных данных в поле Учетное имя введите имя для учетных данных.
- В поле «Удостоверение» введите имя учетной записи, используемой для исходящих подключений (при выходе из контекста SQL Server). Как правило, это будет учетная запись пользователя Windows, но удостоверение может быть и учетной записью другого типа. Либо нажмите кнопку с многоточием (. ), чтобы открыть диалоговое окно Выбор пользователя или группы.
- В полях Пароль и Подтверждение пароля введите пароль учетной записи, указанной в поле Удостоверение . Если в поле Удостоверение указана учетная запись пользователя Windows, то это будет пароль Windows. Поле Пароль может быть пустым, если пароль не требуется.
- Установите флажок Использовать поставщика функций шифрования, чтобы учетные данные проверялись поставщиком расширенного управления ключами. Дополнительные сведения см. в разделе «Расширяемое управление ключами» (EKM)
- Нажмите ОК.
Использование Transact-SQL
Создание учетных данных
- В обозревателе объектов подключитесь к экземпляру ядра СУБД.
- На стандартной панели выберите пункт Создать запрос.
- Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.
-- Creates the credential called "AlterEgo.". -- The credential contains the Windows user "Mary5" and a password. CREATE CREDENTIAL AlterEgo WITH IDENTITY = 'Mary5', SECRET = ''; GO Дополнительные сведения см. в статье CREATE CREDENTIAL (Transact-SQL).
Практикум по языку SQL
C помощью управляющей программы SQL Server Management Studio либо программы DBeaver cоединитесь с сервером баз данных, используя индивидуальные имя и пароль, которые следует получить у преподавателя. СУБД Microsoft SQL Server находится по адресу 172.20.180.239. Из домена компьютерных классов КФТТ сервер баз данных также доступен по имени server. Для входа в СУБД выберите режим «SQL Server Authentication». Ваш вариант (в тех заданиях, где требуется работа по вариантам) соответствует последней цифре вашего логина. Все действия, которые вы будете совершать, должны осуществляться только с помощью SQL-инструкций. Результаты работы оформляются в виде отчёта: к каждой написанной команде на языке SQL делается скриншот с результатами выполнения этой команды. В начале работы нажмите кнопку New Query на панели инструментов и перейдите к базе данных pubs с помощью команды USE pubs. Все выполняемые SQL-инструкции сохраняйте в текстовом файле на вашем рабочем носителе с помощью команды меню File/Save. При нажатии на кнопку Execute или Debug запрос будет отправлен на выполнение СУБД. В случае проблем с отладкой используйте только вариант Execute. Если вы сомневаетесь в синтаксисе команды, откройте online-помощь. При работе с SQL Server Management Studio запускаются на выполнение все команды, которые вы записали в файл запросов. Чтобы выполнить только один запрос, выделите его мышкой и нажмите на кнопку Execute. Кроме того, можно закомментировать неиспользуемые запросы с помощью символов /* несколько строк */, либо с помощью двойного дефиса — (в этом случае комментируются символы от знака — до конца строки).
Задания:
Часть 1. Команда SELECT
1. Подключитесь к базе данных Pubs. Структура базы данных Pubs. Выведите на экран полное содержимое таблиц authors, titles, titleauthor, publishers, pub_info. Выведите последнюю таблицу так, чтобы имена столбцов были на русском языке. Запросы к одной таблице 2. Создайте запрос, который покажет фамилии и имена авторов, проживающих в городе Salt Lake City. Следующий запрос должен показать, сколько авторов проживает в этом городе. 3. Напишите запрос, выводящий отсортированный по алфавиту список авторов, номера телефонов которых содержат цифру вашего варианта. 4. Создайте запрос, который покажет, сколько различных штатов представлено в таблице Authors. 5. Подсчитайте с помощью запроса, сколько авторов проживает в каждом штате. 6. Выведите отсортированный по алфавиту список тем, по которым выпускались книги (повторы не допускаются). 7. Напишите запрос, выводящий отсортированный по убыванию стоимости список названий книг и их цену (в рублях), игнорируя книги, цена на которые не указана. 8. Напишите запрос, выводящий названия книг, выпущенных по темам «popular_comp» и «psychology». 9. Есть ли названия книг, содержащие первые две буквы вашего имени (в английской транскрипции)? 10(варианты 0-4). Напишите запрос, выводящий название книги, у которой был самый высокий объём продаж. 10(варианты 5-9). Напишите запрос, выводящий тему, по которой издано наибольшее количество книг. Запросы к связанным таблицам 11. Создайте запрос, выводящий список названий книг вместе с названием выпустившего книгу издательства. 12. Создайте запрос, выводящий следующую таблицу: фамилия, имя автора, город проживания автора, название книги автора, год выпуска книги. Таблица должна быть отсортирована по фамилии автора. 13. Напишите запрос, определяющий, какие книги написаны автором Albert Ringer. 14. Покажите, какие книги имеют более одного автора. 15 (варианты 0-4). Напишите запрос, подсчитывающий, сколько написал книг и сколько всего денег заработал на них каждый из авторов. Доход автора от одной книги есть стоимость книги * объём продаж * % гонорара для всех авторов * % гонорара для одного автора (если их несколько). 15 (варианты 5-9). Представьте, что необходимо отправить рекламную информацию от издательств авторам, которые публиковались после 1992 года. Для этого создайте запрос, формирующий таблицу, содержащую следующие столбцы: фамилия, имя автора, адрес и город автора, название издательства, где публиковался автор, рекламная информация издательства (из таблицы pub_info). Чуть более сложные запросы 16. Напишите запрос, определяющий, какие книги написаны совместно авторами Ringer Anne и Ringer Albert. 17. Вывести список авторов, которые публиковались в разных издательствах.
Часть 2. Команды CREATE, ALTER, INSERT, UPDATE, DELETE
1. Перейдите в свою базу данных (имя вашей базы данных совпадает с вашим логином). С помощью команды CREATE TABLE создайте аналог таблицы Authors, с некоторыми изменениями. Поле au_id в вашей таблице будет иметь целый тип, будет счётчиком и первичным ключом, а поле state будет отсутствовать. Кроме того, переделайте все типы varchar на типы nvarchar. 2. После создания таблицы с помощью команды INSERT скопируйте содержание таблицы Authors из базы данных pubs в созданную вами таблицу. Также с помощью команды INSERT введите в созданную таблицу нескольких авторов из России. 3. Командой CREATE TABLE создайте таблицу Countries, содержащую два поля: id целого типа и name типа nvarchar(200). Введите в эту таблицу как минимум две страны: США и Россия. С помощью команды ALTER TABLE измените таблицу Authors: создайте новое поле idCountry, которое будет являться внешним ключом к таблице Countries. 4. C помощью нескольких команд UPDATE установите значения этого поля так, чтобы авторы «попали» в разные страны. Критерий отбора может быть любым; в поле idCountry не должно остаться значений NULL. Подсчитайте с помощью SELECT-запроса, сколько авторов находится в каждой стране. 5. С помощью нескольких команд UPDATE замените названия городов в таблице Authors для введённых вами стран так, чтобы эти названия соответствовали городам указанной страны. Например, все авторы, которые проживали в Oakland, будут жить, например, в Paris, если для этих авторов указана в качестве страны Франция. 6. C помощью команды SELECT . INTO сделайте копию таблицы Authors с именем AuthorsCopy. С помощью команды DELETE из таблицы AuthorsCopy удалите всех авторов, в поле zip которых содержится цифра вашего варианта. С помощью команды ALTER TABLE удалите из этой же таблицы поле contract. И, наконец, с помощью команды UPDATE в таблице Authors замените в значении поля zip первую цифру на цифру вашего варианта для тех авторов, кого не оказалось в таблице AuthorsCopy.
Заключительные замечания
Переходя к выполнению курсового проекта вы можете удалить все таблицы, которые создали в вашей базе данных при выполнении вышеприведённых заданий. После завершения курса «Базы данных» вы также можете удалить всё содержимое вашей базы данных. В начале следующего семестра содержимое всех студенческих баз данных очищается. Поэтому, если у вас оказался долг по практике, заранее позаботьтесь о локальной копии содержимого вашей базы данных.
Дисциплина, точность, внимание к деталям, часть вторая (OLAP, SSAS)
В этой статье я продолжу рассказ о своем опыте работы с Microsoft Analysis Services. В дополнение к предыдущей статье, я хочу написать про нестандартные решения, которые были сделаны в последнем проекте. Эти решения более тесно сблизили меня с Microsoft Analysis Services, я стал больше его уважать и делать с его помощью то, что ранее мне казалось невероятным.
Сумма средних за период
Заказчик просил сумму средних значений по каждому элементу за период, как показано на картинке ниже:
То есть идея в том, чтобы в разрезе элементов elem1, elem2, …, elemN куб считал среднее по каждому элементу за выбранный период, во всех остальных случаях считалась сумма. Требуется, как бы поменять поведение куба для одного измерения, а для всех остальных оставить стандартное (в описываемом случае стандартное поведение это СУММА).
Варианты решений:
Для этого варианта надо создать скрытое измерение [ELEM COPY], просто создав копию [ELEM] и поставив ему свойство Visible на False. Далее в разделе куба “Calculations” выбрать “New Calculated Member”, как показано на рисунке:
и в появившемся окне для ввода выражений написать:
iif ( not isleaf([ELEM].[ELEM SK].currentmember), sum(EXISTING [ELEM COPY].[ELEM SK].currentmember.Children,[Measures].[FCT VAL]), [Measures].[FCT VAL] ) , где [ELEM COPY].[ELEM SK] – это ключевой атрибут скрытого измерения.
[Measures].[FCT VAL] – это AvarageOfChildren агрегация, т.е. при сборке куба создали измерение типа AvarageOfChildren и он его автоматически рассчитал. Пример того как создать измерение с AvarageOfChildren агрегацией показан на рисунке ниже:
Что надо сделать:
Переписать селект для таблицы фактов:
SELECT [DATE] ,ID_CO ,ID_CUST ,ID_SYS ,ID_VOL ,ID_QUAL ,GB_used_DATA FROM [000_REP].NAS_FACTS SELECT [DATE] ,ID_CO ,ID_CUST ,ID_SYS ,ID_VOL ,ID_QUAL ,GB_used_DATA ,CONVERT(VARCHAR(10), [DATE], 120) + '|' + CAST(ID_VOL AS VARCHAR(MAX)) AS VolDate ,NULL AS Avg_GB_used_DATA FROM [000_REP].NAS_FACTS , где GB_used_DATA это факт, который мы хотим добавить в куб. Нестандартное поведение мы хотим сделать для измерения с ключом ID_VOL, для остальных измерений с ключами ID_CO, ID_CUST, ID_SYS и ID_QUAL поведение должно быть стандартным и в нашей задаче всё должно просто суммироваться. Только по ID_VOL должно считаться среднее за период по каждому элементу, а итог по ID_VOL элементам так же должен суммироваться, должна получиться сумма средних значений по ID_VOL.
Во втором запросе добавили 2 колонки:
-
1-ая колонка уникально определят связку даты с ID элементами, по которым надо считать сумму средних значений. В кубе по этой колонке добавляется измерение с функцией агрегации DistinctCount. Пример на рисунке ниже:
Далее в разделе куба “Calculations” выбрать “Script View”, и в окне для редактирования скрипта надо вставить такой код:
SCOPE([Measures].[Avg GB Used DATA]); SCOPE([ID_VOL Items].[ID VOL].[ID VOL].MEMBERS); THIS = [Measures].[Sum GB Used DATA]/[Measures].[Vol Date Distinct Count]; END SCOPE; END SCOPE; где [ID_VOL Items] это измерение, ключ у которого ID_VOL.
На рисунке показана последовательность действий для этого шага:
Statistical Average
Спустя какое-то время заказчик вернулся к теме подсчета средних величин. На этот раз, он потребовал не сумму средних, а классическое среднее, то есть тот же функционал, что и у функции AVERAGE в Excel. Заказчик постоянно оперировал понятием “Statistical Average”, отсюда название этой главы.
Требовалось рассчитать среднее значение по всему диапазону. Берётся среднее по всем элементам за каждый день, суммируется и делится на количество дней в периоде. В итоге получается усредненное значение на один элемент за период. Было предложено следующее решение:
CREATE MEMBER CURRENTCUBE.[Measures].[Avg GB Used DATA (AvgAll Only valid days)] as [Measures].[Sum GB Used DATA]/[Measures].[Count VCMDB Only valid days], VISIBLE = 1 ; CREATE MEMBER CURRENTCUBE.[Measures].[Count VCMDB Only valid days] as Count(NonEmpty(, [Measures].[Sum GB Free Data] )), VISIBLE = 1 ; 
В данном решении брались только дни, где были значения у элементов. Так же использовался трюк со скрытым измерением (это измерения [DIM Business Time HD].[DAY] и [DIM NAS Provider Configuration Item HD].[NAS Volume CMDBID]). Количество дней, когда были значения, я получил с помощью crossjoin.
Если же надо получить среднее по всем значениям за все дни, где отсутствие значения за какой либо день приравнивается к 0, то я использовал следующее выражение:
CREATE MEMBER CURRENTCUBE.[Measures].[Count VCMDB All days] as [Measures].[NAS Volume CMDBID Distinct Count] * [Measures].[NAS BTIME Count], VISIBLE = 1 ; CREATE MEMBER CURRENTCUBE.[Measures].[Count VCMDB All days] as [Measures].[NAS Volume CMDBID Distinct Count] * [Measures].[NAS BTIME Count], VISIBLE = 1 ; где [Measures].[NAS Volume CMDBID Distinct Count] и [Measures].[NAS BTIME Count] это меры куба, построенные по таблицам для измерений (временное измерение и измерение элемента):
Ещё одна полезная функция
В процессе работы с кубом выдвигались требования, в которых расчет значений менялся от того, на каком уровне иерархии его хотели видеть. То есть, если выбраны дни, то мы видим, среднее за период, если месяцы то сумму. Это было сделано с помощью функции level:
CREATE MEMBER CURRENTCUBE.[Measures].[ML] as case when [DIM Business Time].[HIERARCHY CAL].currentmember.level is [DIM Business Time].[HIERARCHY CAL].[YEAR] then 3 when [DIM Business Time].[HIERARCHY CAL].currentmember.level is [DIM Business Time].[HIERARCHY CAL].[PERIOD KAL] then 2 when [DIM Business Time].[HIERARCHY CAL].currentmember.level is [DIM Business Time].[HIERARCHY CAL].[DAY] then 1 else 4 end, VISIBLE = 1; Итог
Честно говоря, когда я видел требования по расчету средних значений, мне хотелось смеяться и плакать одновременно. Смеяться от того, что порой среднее значение было либо больше всех значений в выбранном диапазоне (как в примере с суммой средних), либо меньше. Случай с меньше я не стал описывать в статье, так как не смог понять смысл — заказчик требовал делить значение за каждый день на количество дней в месяце. А плакать приходилось от того, что эти требования надо было как-то реализовывать.
Попытки ограничится стандартным AvarageOfChildren, которая суммирует все показатели за день, затем складывает эти суммы и делит на количество дней в периоде, успеха не имели. Заказчик настойчиво требовал соответствия реальности его картине мира. Все наши решения подвергались жесткому и скрупулёзному тестированию. Вопрос о доверии между нами и клиентом отпал на ранних этапах взаимодействия. Он постоянно искал подвоха и решение, вместо простого и интуитивно понятного, превратилось в нечто странное и монструозное. Но во всем есть своя польза. Благодаря таким требованиям, пришлось более детально разобраться с работой OLAP куба и научится работать с произвольными агрегациями. Чтобы этот опыт был не напрасен и помогал в будущем решать похожие задачи не только мне, я решил поделиться им с Вами. В общем, как в анекдоте про сантехников:
К сантехнику прикрепили практиканта. Вызывают на выезд. Приезжают. Канализационный люк. Из него течет дерьмо. Сантехник подходит к люку и ныряет.
Через минуту выныривает, кричит:
— Ключ на 19.
Снова ныряет. Через полминуты выныривает:
— Прокладку No.6.
Опять ныряет. Выныривает:
— Ключ на 26.
Ныряет. Через минуту выныривает. Выходит, отряхивается и закуривает. Сел, отдышался и говорит практиканту:
— Вот так. Учись, студент! А то так и будешь всю жизнь ключи подавать.
Общий форум
В моём случае имеется большая древовидная структура.
Первый уровень каталога – уровни образования (специалитет, бакалавриат, магистратура, аспирантура, СПО).
Второй уровень вложенности – формы обучения (очная, заочная, очно-заочная).
Третий уровень вложенности – курсы (1-6 в зависимости от уровня образования и формы обучения).
На 4 уровне идут дисциплины (в рамках системы moodle это курсы).
Наименование глобальных групп
В наименовании группы содержится шифр направления подготовки, форма обучения, курс.
Примеры: 360501_ОЧНО_1КУРС, 360501_ОЧНО_2КУРС, 360402_ОЧНО-ЗАОЧНО_1КУРС;
Зачисление на курс преподавателей и обучающихся
Преподавателей к дисциплине добавляем через способ «Зачисление вручную».
Обучающихся добавляем через способ «Синхронизация с глобальной группой».
Зачисление студентов на курс выше
В зачислении студентов на курс всё просто, создаем .csv файл с полями username, cohort1, cohortN. После загрузки все обучающиеся получают доступ к дисциплинам, которые соответствуют учебному плану.
Резервное копирование всей системы moodle
В конце учебного года, перед отчислением студентов с прошлого курса, производим резервное копирование всей системы.
Отчисление студентов с прошлого курса
Проблема в следующем, ГГ которые мы используем, это своего рода контейнеры, которые мы в конце учебного года очищаем и наполняем обучающимися которые переходят на курс выше. Это делаем для того, чтобы каждый год по новой не прикреплять ГГ ко всем дисциплинам.
Этапы перевода с курса на курс
Этап 1: отчислить всех студентов с текущей ГГ.
Этап 2: прикрепить студентов в ГГ на курс выше используя «.csv».
Варианты решения этапа 1:
1) Открываем site.ru/cohort/index.php и вручную заходя в каждую ГГ отчисляем обучающихся.
2) Устанавливаем на сервер moosh, и используя команду «moosh cohort-unenrol» отчисляем студентов. Минус этого способа в том, что здесь необходимо знать ID каждого пользователя, которого мы отчисляем.
Вопрос — Какие есть еще способы отчисления обучающихся из глобальной группы?
Сумма оценок: —
Постоянная ссылка Показать сообщение-родителя Ответить
В ответ на Roman Li
Re: Открепление обучающихся из глобальной группы через SQL запрос
от Alex Djachenko — среда, 3 ноября 2021, 17:04
Если требуется автоматизировать зачисление в глобальные группы, то лучше делайте это через REST-api, либо напишите плагин, который делает это через вызов программного API Moodle. Лезть в базу и что-то там править категорически не рекомендую. Это антипаттерн и это может убить вашу базу (и скорее-всего убьет). Поскольку Moodle — модульная система, консистентность базы поддерживается на уровне программного кода, а не на уровне каскадных зависимостейц, процедур и функций в самой базе, структура базы может произвольно меняться от версии к версии. Единственный способ модифицировать данные, чтобы они всегда правильно отразились на всех связанных таблицах и база не потеряла консистентность — делать это только через вызов соответствующих функций в системе (либо через REST-api, которое вызывает функции в коде, либо через инструменты синхронизации, в том числе с внешними БД, которые пишут не напрямую в базу, а вызывают соответствующие функции Moodle).
Сумма оценок: —
Постоянная ссылка Показать сообщение-родителя Ответить
В ответ на Alex Djachenko
Re: Открепление обучающихся из глобальной группы через SQL запрос
от Roman Li — среда, 3 ноября 2021, 18:43
С зачислением в глобальную группу проблем нет, есть проблема с отчислением всех обучающихся из глобальной группы. С sql запросами теперь понятно.
По поводу REST-api.
Для работы с ГГ нам нужен класс core_cohort, для отчисления одного пользователя, который состоит в ГГ используем метод cohort_remove_member ($cohortid, $userid).
Примерные этапы
1) Получить список всех пользователей, кто состоит в определенной ГГ. (В документации данного метода не нашел. Скорее всего метод есть тут /cohort/assign.php).
2) Сформировать массив ID пользователей.
3) Отчислить пользователей используя метод cohort_remove_member в цикле.
Это всё упаковать в плагин, либо используя веб-сервисы через API управлять через стороннюю систему.
В правильном направлении думаю?