Використання штучного інтелекту для створення SQL-запитів до бази даних Moodle
Схема розділу
-
-
Щербина О.А.
Національний технічний університет України «Київський політехнічний інститут імені Ігоря Сікорського»
Використання штучного інтелекту для створення SQL-запитів до бази даних Moodle
Як відомо, сайт Moodle на сервері складається з файлів коду, файлів даних і бази даних. Знаючи структуру бази даних [1] і мову SQL-запитів до неї [2], можна одержати доступ до всіх наявних на платформі даних, і формувати на їх основі будь-які звіти. Для цього Moodle має плагіни Configurable Reports [3] і Ad-hoc database queries [4]. На допомогу користувачу є відповідна документація і велика підбірка запитів [5], які можуть використовуватися в готовому вигляді або служити користувачам прикладами при створення власних запитів. Доступні також зовнішні репозиторії [6], [7], з яких можна імпортувати SQL-запити у свій звіт.
Можливості цих плагінів дуже великі, однак основною перешкодою на шляху їх використання є те, що їх здатен створювати тільки користувач, що знає структуру і взаємозв’язок сотень таблиць бази даних Moodle і вміє оперувати їх даними за допомогою мови SQL-запитів.
Останнім часом перспективи подальшого розвитку LMS Moodle пов’язують з використанням штучного інтелекту (ШІ). Тож виникає питання, а чи не може ШІ взяти на себе написання таких запитів на основі завдань, які користувач формує природною мовою?
Нещодавно з’явився плагін [8], який саме цю функцію і має реалізувати, генеруючи за допомого ШІ SQL-запити для плагіна Configurable Reports.
Ми намагалися протестувати роботу цього плагіна, однак спроби підключитися до сервера розробника впродовж кількох днів виявилися безуспішними.
Як можна бачити зі статистики завантажень цього плагіна, він не користується популярністю серед користувачів Moodle, бо на сьогодні встановлений лише на 44 сайтах, хоча доступний для завантаження з січня 2024 р.
До того ж використання плагіна платне. Ціну розробники не оголошують, а визначають індивідуально для кожного покупця. Безкоштовно можна генерувати не більше 10 запитів на місяць.
Виникає питання: а що саме робить плагін, за що його користувачі платять гроші його розробникам?
Плагін дає змогу користувачу ввести запит природною мовою, який сервер розробника пересилає штучному інтелекту (якому саме розробники не повідомляють) і у відповідь користувач одержує свій запит вже мовою SQL, який можна зразу виконати натисканням кнопки.
Якщо так, то чи не можна обійтися без плагіна? Звісно, користувач може задати те саме питання безпосередньо ШІ, а згенерований ним запит скопіювати в Configurable Reports чи Ad-hoc database queries. Це буде надійне і безкоштовне рішення.
Випробуємо його на прикладі звіту про наповненість курсів та інтенсивність їх використання на сайті Відкритого міжнародного університету розвитку людини «Україна» , який адмініструє автор. Ось повний текст запиту, що генерує цей звіт:
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/index.php?categoryid=', cc.id,'">', cc.name, '</a>') AS "Кафедра",(SELECT cc1.name from prefix_course_categories AS cc1 where cc1.id = cc.parent) as "Факультет",
(SELECT (SELECT cc2.name from prefix_course_categories AS cc2 where cc2.id = cc1.parent) from prefix_course_categories AS cc1 where cc1.id = cc.parent) as "Інститут",
(SELECT DISTINCT concat(u.lastname, ' ', u.firstname)
FROM
prefix_role_assignments AS ra
JOIN prefix_user AS u ON ra.userid = u.id
JOIN prefix_context AS ctx ON ctx.id = ra.contextid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS "Викладач",
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS "Дисципліна",
concat('%%WWWROOT%%/grade/report/grader/index.php?id=',c.id,'') AS "Оцінки",
(SELECT COUNT(*) FROM prefix_logstore_standard_log AS lg WHERE lg.courseid=c.id) AS "Подій за рік",
(SELECT COUNT(*) FROM prefix_logstore_standard_log AS lg WHERE lg.courseid=c.id and DATEDIFF( NOW(),FROM_UNIXTIME(lg.timecreated)) <= 14) AS "За останні 2 тижні",
(SELECT COUNT(*)
FROM
prefix_role_assignments AS ra
JOIN prefix_user AS u ON ra.userid = u.id
JOIN prefix_context AS ctx ON ctx.id = ra.contextid
WHERE ra.roleid = 5 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS "Студентів",
(SELECT COUNT(*)
FROM
prefix_role_assignments AS ra
JOIN prefix_user AS u ON ra.userid = u.id
JOIN prefix_context AS ctx ON ctx.id = ra.contextid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
WHERE gi.itemtype='course' AND gi.courseid = c.id AND gg.finalgrade IS NOT NULL AND ra.roleid = 5 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS "Мають оцінки",
(SELECT COUNT(*) FROM prefix_url AS ur WHERE ur. course=c.id AND (ur.externalurl LIKE '%zoom.us/%' OR ur.externalurl LIKE '%meet.google.com%')) AS "Посилань на zoom тощо",
(SELECT COUNT(*) FROM prefix_resource AS rs WHERE rs. course=c.id AND rs.name LIKE 'Силабус%') AS "Силабусів",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'forum') AS "Форумів",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'label') AS "Нотаток",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'page') AS "Сторінок",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'resource') AS "Файлів",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'folder') AS "Папок",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'url') AS "Гіперпосилань",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'book') AS "Moodle-книг",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'data') AS "Баз даних",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'glossary') AS "Глосаріїв",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'lesson') AS "Уроків",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'assign') AS "Завдань",
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'quiz') AS "Тестів",
(SELECT Count(*) FROM prefix_question q INNER JOIN prefix_question_categories qc ON qc.id = q.category AND q.qtype <> 'random' AND q.parent = 0 INNER JOIN prefix_context ctx ON ctx.id = qc.contextid WHERE ctx.instanceid = c.id AND ctx.contextlevel = 50) +
(SELECT Count(*) FROM {question} q INNER JOIN {question_categories} qc ON q.category = qc.id AND q.qtype <> 'random' AND q.parent = '0' INNER JOIN {context} ctx ON qc.contextid = ctx.id AND ctx.contextlevel = '70' INNER JOIN {course_modules} cm ON ctx.instanceid = cm.id AND cm.module = (SELECT m.id FROM {modules} m WHERE m.name='quiz') WHERE cm.course = c.id)
AS 'Питань'
FROM prefix_course AS c
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE (cc.name LIKE 'Кафедр%' OR cc.name LIKE 'Циклов%' OR cc.name LIKE '%коледж%' OR cc.name LIKE '%Відділенн%') AND cc.name NOT LIKE '%*%' AND c.visible = '1' AND c.fullname NOT LIKE '%КАФЕДРА%' AND c.fullname NOT LIKE '%ЦИКЛОВА КОМІС%'
%%FILTER_CATEGORIES:c.category%%
GROUP BY c.id
ORDER BY cc.name, "Викладач" ASC
У цьому звіті кожний розміщений на сайті курс навчальної дисципліни представлений одним рядком, в якому наводяться такі дані:
- · Інститут,
- · Факультет,
- · Кафедра або циклова комісія,
- · Викладач,
- · Назва дисципліни,
- · Посилання на її журнал оцінок,
- · Подій за рік,
- · Подій за останні два тижні,
- · Кількість зареєстрованих студентів,
- · Скільки з них мають оцінки в журналі,
- · Кількість посилань на відеоконференції Zoom тощо,
- · Кількість розміщених у курсі силабусів,
- · Кількості розміщених у курсі ресурсів і діяльностей кожного типу: форум, нотатка, вебсторінка, файл, папка, гіперпосилання, книга, база даних, глосарій, урок, завдання, тест, а також загальна кількість тестових питань.
Університет «Україна» має відділення і філії в багатьох містах. Їх назви вказуються в полі Інститут. У полях Подій за рік і Подій за останні два тижні підраховується загальна кількість записів у логах, пов’язаних з цією дисципліною. Оскільки будь яка дія будь якого користувача в курсі Moodle створює відповідний запис у логах, то по їх кількості можна зробити висновок наскільки інтенсивно використовується цей курс. Звіт містить дані про наявність обов’язкових для кожного курсу елементів: посилань на відеоконференції, через які проводяться дистанційні заняття, та силабуси дисциплін. Також є дані про наповнення курсу різними видами ресурсів і діяльностей.
Такий звіт формується на сайті за допомогою плагіна Configurable Reports, після чого створена ним таблиця експортується в Excel, де до цих даних розраховуються і додаються проміжні підсумки - середні значення зазначених вище числових показників, що припадають на один курс у кожного викладача, кожної кафедри, факультету, інституту та університету в цілому.
Нещодавно виникла потреба додати в цей звіт дані про відвідуваність кожного курсу, що формуються плагіном відвідування [9].
Для її вирішення було прийняте рішення використати ШІ Grok, оскільки наші попередні спроби використання ChatGPT і Gemini для роботи з Moodle були не завжди вдалими. Ці засоби нерідко генерували не правильні, а лише правдоподібні відповіді, використовуючи, наприклад, назви неіснуючих плагінів чи неіснуючих параметрів налаштування сайту Moodle тощо.
Звісно, важко уявити, що зазначений вище SQL-запит звіту міг бути згенерований штучним інтелектом за один раз, у відповідь на єдине поставлене користувачем завдання. Скоріше йдеться про ітеративний процес, де на кожній ітерації завдання уточнюється, враховуються деталі, які раніше не враховувались, а виведені звітом дані перевіряються і тільки після цього робиться наступна ітерація. Так власне історично і формувався і поступово доповнювався цей SQL-запит впродовж усього часу його використання, а зараз нам треба додати в нього нові дані стосовно відвідування.
Зокрема на сайті університету «Україна», крім курсів навчальних дисциплін, використовуються також службові курси [10], з кожного з яких у курси дисциплін експортуються списки студентів однієї освітньої програми і одного року навчання, а в зворотному напрямі - імпортуються і збираються в одному журналі оцінок результуючі оцінки з усіх навчальних дисциплін, які ці студенти зараз вивчають. Звісно, дані зазначених службових та деяких інших курсів (наприклад, тих, які зараз не викладаються) не повинні потрапляти до цього звіту. Це реалізується завдяки використанню в запиті відповідних фільтрів. Наприклад, до звіту, потрапляють тільки ті курси, в назві категорії яких присутнє слово «кафедра», «циклова комісія» тощо. Зазначимо, що все це є специфікою цього конкретного сайту і всі подібні уточнення краще додавати до запиту поступово, крок за кроком.
Тому спочатку ми надали Grok повний текст наведеного вище запиту і поставили завдання додати у нього перед колонкою «Форумів» колонку в якій обчисюється і виводиться кількість модулів Відвідування у кожному курсі.
Одержавши завдання, Grok зазвичай формулює його своїми словами, щоб дати змогу користувачу пересвідчитись, що його зрозуміли правильно, а потім виконує це завдання, не просто наводячи готовий результат, а й надаючи до нього пояснення. Цих пояснень часто буває навіть занадто багато і тоді доводиться просити Grok бути лаконічнішим.
В нашому прикладі Grok правильно запропонував додати такий фрагмент:
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'attendance') AS "Модулів Відвідування",навівши як сам фрагмент так і оновлений текст запиту з цим фрагментом в потрібному місці. Перевірка в Configurable Reports показала, що звіт виводиться правильно і що значна частина курсів дійсно мають діяльність Відвідування у своєму складі, отже є можливість і доцільність кількісно оцінити відвідування студентами кожного курсу. Тому наш наступний запит до Grok був сформульований так (тут і далі цитати, взяті з нашого спілкування з Grok, виділені синім кольором):
«Додай у звіт, який я дав для зразка, стовпець з кількістю діяльностей Відвідування у курсі, а також підрахуй і виведи середній бал за відвідування для всіх записаних на кожний курс студентів».
На це Grok відповів зокрема таке:
«… Середній бал за відвідування: Додано стовпець: sql
(SELECT AVG(student_attendance_grade) FROM ( SELECT u.id AS student_id, SUM(ast.grade) / COUNT(DISTINCT asess.id) AS student_attendance_grade FROM prefix_user AS u JOIN prefix_role_assignments AS ra ON ra.userid = u.id JOIN prefix_context AS ctx ON ctx.id = ra.contextid JOIN prefix_attendance_log AS al ON al.studentid = u.id JOIN prefix_attendance_sessions AS asess ON al.sessionid = asess.id JOIN prefix_attendance AS a ON asess.attendanceid = a.id JOIN prefix_attendance_statuses AS ast ON al.statusid = ast.id WHERE ra.roleid = 5 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 GROUP BY u.id ) AS sub) AS "Середній бал за відвідування"
Логіка: Знаходимо всіх студентів (roleid = 5) у курсі (ctx.instanceid = c.id). Отримуємо записи відвідування з таблиці prefix_attendance_log для кожного студента. З’єднуємо з prefix_attendance_sessions і prefix_attendance для отримання сесій відвідування. Використовуємо prefix_attendance_statuses для отримання балів (ast.grade) за статуси відвідування (наприклад, присутність = 2, відсутність = 0). Обчислюємо середній бал для кожного студента (SUM(ast.grade) / COUNT(DISTINCT asess.id)), де чисельник — сума балів, а знаменник — кількість сесій. Обчислюємо середнє значення по всіх студентах у курсі за допомогою AVG. Збереження структури: Усі попередні стовпці залишилися без змін, щоб звіт залишався сумісним із вашим поточним форматом. Запит зберігає фільтри (%%FILTER_CATEGORIES:c.category%%), групування (GROUP BY c.id) та сортування (ORDER BY cc.name, "Викладач" ASC)»...
Тобто Grok фактично запропонував виконати обчислення, які уже і так виконуються плагіном Attendance. Тоді ми запитали у нього:
«А навіщо аналізувати відвідування по логах? Адже в журналі оцінок вже є поточний відсоток відвідування для кожного студента записаного на цей курс. Чому б не підрахувати середнє значення усіх цих оцінок»? і одержали на це таку відповідь:
«Ви абсолютно праві, що використання даних із журналу оцінок (gradebook) для підрахунку середнього відсотка відвідування є більш логічним і ефективним, якщо модуль «Відвідування» (attendance) налаштовано для збереження оцінок у журналі оцінок. Це дозволяє уникнути складних підрахунків на основі логів відвідування (prefix_attendance_log) і спрощує запит, оскільки оцінки за відвідування для кожного студента вже агреговані в таблиці prefix_grade_grades.
Давайте адаптуємо SQL-запит, щоб:
Залишити стовпець із кількістю діяльностей типу «Відвідування» у кожному курсі. Підрахувати середній відсоток відвідування для всіх студентів, записаних на курс, використовуючи дані з журналу оцінок (prefix_grade_grades).
Пояснення Кількість діяльностей «Відвідування»: Як і раніше, підраховуємо кількість модулів attendance у курсі за допомогою таблиць prefix_course_modules і prefix_modules.
Середній відсоток відвідування: У модулі «Відвідування» оцінки за відвідування для кожного студента зберігаються в журналі оцінок як елемент оцінювання (prefix_grade_items із типом mod і модулем attendance). Оцінка студента за відвідування (зазвичай у відсотках, наприклад, 85.5%) доступна в таблиці prefix_grade_grades у полі finalgrade або rawgrade. Ми обчислимо середнє значення finalgrade для всіх студентів із роллю student (roleid = 5) для елемента оцінювання, пов’язаного з модулем «Відвідування».
Переваги використання журналу оцінок: Дані вже агреговані (не потрібно підраховувати бали за сесії вручну). Запит простіший і швидший, оскільки не вимагає з’єднання з таблицями prefix_attendance_log, prefix_attendance_sessions тощо. Відсотки відвідування коректно відображають налаштування модуля (наприклад, максимальний бал за відвідування). Модифікований SQL-запит Ось оновлений SQL-запит, який додає:
Стовпець із кількістю діяльностей «Відвідування». Стовпець із середнім відсотком відвідування для всіх студентів курсу, взятим із журналу оцінок. Configurable Reports SQL Query with Attendance and Average Grade from Gradebook sql
Кількість діяльностей «Відвідування»: Залишено без змін із попередньої версії: sql
(SELECT COUNT(*) FROM prefix_course_modules AS cm INNER JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name = 'attendance') AS " Модулів Відвідування " Підраховує кількість модулів attendance у кожному курсі.
Середній відсоток відвідування: Новий стовпець: sql
(SELECT AVG(gg.finalgrade) FROM prefix_grade_grades AS gg JOIN prefix_grade_items AS gi ON gi.id = gg.itemid JOIN prefix_user AS u ON gg.userid = u.id JOIN prefix_role_assignments AS ra ON ra.userid = u.id JOIN prefix_context AS ctx ON ctx.id = ra.contextid WHERE gi.itemtype = 'mod' AND gi.itemmodule = 'attendance' AND gi.courseid = c.id AND ra.roleid = 5 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 AND gg.finalgrade IS NOT NULL) AS "Середній відсоток відвідування"
Логіка: Знаходимо оцінки за відвідування в таблиці prefix_grade_grades (gg.finalgrade), пов’язані з елементом оцінювання типу mod і модулем attendance (gi.itemtype = 'mod', gi.itemmodule = 'attendance'). Фільтруємо тільки студентів (ra.roleid = 5) у контексті курсу (ctx.instanceid = c.id, ctx.contextlevel = 50). Використовуємо AVG(gg.finalgrade) для обчислення середнього відсотка відвідування, враховуючи лише ненульові оцінки (gg.finalgrade IS NOT NULL).
Якщо в курсі кілька модулів attendance, середнє обчислюється для всіх оцінок, пов’язаних із цими модулями (можна уточнити, якщо потрібен окремий модуль)»...
Зауважимо, що наступне питання ми хотіли задати саме про курси, в яких є кілька модулів Відвідування. Однак нам не довелось цього робити, бо Grok сам про це подбав. Оскільки в журналі оцінок кожний модуль Відвідування представлений окремою оцінкою (наприклад, викладач бажає оцінити окремо відвідуваність лекцій і практичних занять), то у звіт має потрапляти середня арифметична цих двох оцінок.
Перевірка підтвердила правильну роботу згенерованих запитів.
Підводячи підсумки проведеного експерименту, можна зробити висновок, що Grok знає структуру баз даних Moodle, і не тільки ядра, а й додаткових плагінів, таких, як Attendance і може генерувати SQL-запити для Configurable Reports. При цьому він не тільки генерує запити, а й пояснює їх логіку, що сприяє кращому розумінню нами цих запитів, тобто якоюсь мірою підвищує нашу кваліфікацію в цій сфері. Разом з тим, як показав розглянутий вище приклад, природній інтелект може пропонувати кращі рішення, ніж штучний.
У зв’язку з цим виникає питання, чи може людина без знань структури бази даних Moodle і мови SQL-запитів створювати за допомогою штучного інтелекту потрібні їй звіти у Configurable Reports?
Цілком можна уявити ситуацію, коли користувач ставить завдання ШІ, а потім чисто механічно копіює згенерований SQL-запит у Configurable Reports і одержує бажаний результат. Однак, на нашу думку, таке можливо тільки в простих випадках. При побудові складніших звітів можуть виникати ускладнення, з якими непідготовлений користувач не знатиме що робити. Отже, щоб свідомо і з розумінням справи використовувати ШІ у цій роботі, треба мати принаймні базові знання про реляційні бази даних взагалі і базу даних Moodle зокрема, а також володіти основами SQL.
Список використаних джерел
[1] Database schema introduction. [Online]. Available: https://docs.moodle.org/dev/Database_schema_introduction
[2] Мулеса О.Ю. Основи мови запитів SQL. Ужгород, 2015. 48 с. [Електронний ресурс]. Режим доступу: https://dspace.uzhnu.edu.ua/jspui/bitstream/lib/8868/1/sql.pdf
[3] Configurable Reports. [Online]. Available: https://moodle.org/plugins/block_configurable_reports
[4] Reports: Ad-hoc database queries. [Online]. Available: https://moodle.org/plugins/report_customsql
[5] Ad-hoc contributed reports. [Online]. Available: https://docs.moodle.org/33/en/ad-hoc_contributed_reports
[6] jleyva/moodle-configurable_reports_repository. [Online]. Available: https://github.com/jleyva/moodle-configurable_reports_repository
[7] jleyva/moodle-custom_sql_report_queries. [Online]. Available: https://github.com/jleyva/moodle-custom_sql_report_queries
[8] LionAI Reports. [Online]. Available: https://moodle.org/plugins/local_lionai_reports
[9] Attendance. [Online]. Available: https://moodle.org/plugins/mod_attendance
[10] Щербина О.А. Автоматизація створення, наповнення і адміністрування категорій курсів сайту Moodle. // Інформаційні технології і засоби навчання. 2023, том 93, №1. С. 178 – 198. DOI: 10.33407/itlt.v93i1.5117. [Електронний ресурс]. Режим доступу: https://journal.iitta.gov.ua/index.php/itlt/article/view/5117/2103