Docker 入門
Moodle サイトを構築する
Moodle のデータベースを掘り下げる
はじめに
ここでは Moodle データベースの設計について，いくつかポイントを絞って掘り下げていきます．なお，前のページまでと同じ作業が完了している物とします．さらに，教師「teacher01」アカウントでログインし，下図のとおり「憲法」コースのセクション名を「セクション1」〜「セクション4」に変更し，「セクション1」には「小テスト」の活動を追加，「セクション2」には「小テスト」「ページ」「課題」という活動を追加した状態で以下の作業を進めることとします．
データベースへの接続とテーブルの一覧
まず，Docker コンテナとして起動しているデータベースへの接続方法を再確認しておきます．なお，MySQL の管理者パスワードは .env に記載されているはずです．
% docker compose exec -it moodle_mysql /bin/bash ⏎ bash-5.1# mysql -u root -p ⏎ Enter password: （パスワード：表示されない）⏎ Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 708 Server version: 9.4.0 MySQL Community Server - GPL Copyright (c) 2000, 2025, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
データベースの一覧を表示するコマンドは次のとおりです．
SHOW DATABASES;
実際にデータベースの一覧を確認します．
mysql> SHOW DATABASES; ⏎
+--------------------+
| Database |
+--------------------+
| information_schema |
| moodleDB |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.006 sec)
mysql>
利用するデータベースを変更するコマンドは次のとおりです．
USE moodleDB;
実際にデータベースを変更します．
mysql> USE moodleDB; ⏎
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
テーブルの一覧を確認するコマンドは次のとおりです．
SHOW TABLES;
実際にテーブルの一覧を確認します．500個近いテーブルが存在することが分かります．このページではハイライトされたテーブルに絞って掘り下げていきます．
mysql> SHOW TABLES; ⏎ +----------------------------------+ | Tables_in_moodleDB | +----------------------------------+ | mdl_adminpresets | | mdl_adminpresets_app | | mdl_adminpresets_app_it | | mdl_adminpresets_app_it_a | | mdl_adminpresets_app_plug | | mdl_adminpresets_it | | mdl_adminpresets_it_a | | mdl_adminpresets_plug | | mdl_ai_action_explain_text | | mdl_ai_action_generate_image | | mdl_ai_action_generate_text | | mdl_ai_action_register | | mdl_ai_action_summarise_text | | mdl_ai_policy_register | | mdl_ai_providers | | mdl_analytics_indicator_calc | | mdl_analytics_models | | mdl_analytics_models_log | | mdl_analytics_predict_samples | | mdl_analytics_prediction_actions | | mdl_analytics_predictions | | mdl_analytics_train_samples | | mdl_analytics_used_analysables | | mdl_analytics_used_files | | mdl_assign | | mdl_assign_grades | | mdl_assign_overrides | | mdl_assign_plugin_config | | mdl_assign_submission | | mdl_assign_user_flags | | mdl_assign_user_mapping | | mdl_assignfeedback_comments | | mdl_assignfeedback_editpdf_annot | | mdl_assignfeedback_editpdf_cmnt | | mdl_assignfeedback_editpdf_quick | | mdl_assignfeedback_editpdf_rot | | mdl_assignfeedback_file | | mdl_assignsubmission_file | | mdl_assignsubmission_onlinetext | | mdl_auth_lti_linked_login | | mdl_auth_oauth2_linked_login | | mdl_backup_controllers | | mdl_backup_courses | | mdl_backup_logs | | mdl_badge | | mdl_badge_alignment | | mdl_badge_backpack | | mdl_badge_backpack_oauth2 | | mdl_badge_criteria | | mdl_badge_criteria_met | | mdl_badge_criteria_param | | mdl_badge_endorsement | | mdl_badge_external | | mdl_badge_external_backpack | | mdl_badge_external_identifier | | mdl_badge_issued | | mdl_badge_manual_award | | mdl_badge_related | | mdl_bigbluebuttonbn | | mdl_bigbluebuttonbn_logs | | mdl_bigbluebuttonbn_recordings | | mdl_block | | mdl_block_instances | | mdl_block_positions | | mdl_block_recent_activity | | mdl_block_recentlyaccesseditems | | mdl_block_rss_client | | mdl_blog_association | | mdl_blog_external | | mdl_book | | mdl_book_chapters | | mdl_cache_filters | | mdl_cache_flags | | mdl_capabilities | | mdl_choice | | mdl_choice_answers | | mdl_choice_options | | mdl_cohort | | mdl_cohort_members | | mdl_comments | | mdl_communication | | mdl_communication_customlink | | mdl_communication_user | | mdl_competency | | mdl_competency_coursecomp | | mdl_competency_coursecompsetting | | mdl_competency_evidence | | mdl_competency_framework | | mdl_competency_modulecomp | | mdl_competency_plan | | mdl_competency_plancomp | | mdl_competency_relatedcomp | | mdl_competency_template | | mdl_competency_templatecohort | | mdl_competency_templatecomp | | mdl_competency_usercomp | | mdl_competency_usercompcourse | | mdl_competency_usercompplan | | mdl_competency_userevidence | | mdl_competency_userevidencecomp | | mdl_config | | mdl_config_log | | mdl_config_plugins | | mdl_contentbank_content | | mdl_context | | mdl_context_temp | | mdl_course | | mdl_course_categories | | mdl_course_completion_aggr_methd | | mdl_course_completion_crit_compl | | mdl_course_completion_criteria | | mdl_course_completion_defaults | | mdl_course_completions | | mdl_course_format_options | | mdl_course_modules | | mdl_course_modules_completion | | mdl_course_modules_viewed | | mdl_course_published | | mdl_course_request | | mdl_course_sections | | mdl_customfield_category | | mdl_customfield_data | | mdl_customfield_field | | mdl_customfield_shared | | mdl_data | | mdl_data_content | | mdl_data_fields | | mdl_data_records | | mdl_enrol | | mdl_enrol_flatfile | | mdl_enrol_lti_app_registration | | mdl_enrol_lti_context | | mdl_enrol_lti_deployment | | mdl_enrol_lti_lti2_consumer | | mdl_enrol_lti_lti2_context | | mdl_enrol_lti_lti2_nonce | | mdl_enrol_lti_lti2_resource_link | | mdl_enrol_lti_lti2_share_key | | mdl_enrol_lti_lti2_tool_proxy | | mdl_enrol_lti_lti2_user_result | | mdl_enrol_lti_resource_link | | mdl_enrol_lti_tool_consumer_map | | mdl_enrol_lti_tools | | mdl_enrol_lti_user_resource_link | | mdl_enrol_lti_users | | mdl_enrol_paypal | | mdl_event | | mdl_event_subscriptions | | mdl_events_handlers | | mdl_events_queue | | mdl_events_queue_handlers | | mdl_external_functions | | mdl_external_services | | mdl_external_services_functions | | mdl_external_services_users | | mdl_external_tokens | | mdl_favourite | | mdl_feedback | | mdl_feedback_completed | | mdl_feedback_completedtmp | | mdl_feedback_item | | mdl_feedback_sitecourse_map | | mdl_feedback_template | | mdl_feedback_value | | mdl_feedback_valuetmp | | mdl_file_conversion | | mdl_files | | mdl_files_reference | | mdl_filter_active | | mdl_filter_config | | mdl_folder | | mdl_forum | | mdl_forum_digests | | mdl_forum_discussion_subs | | mdl_forum_discussions | | mdl_forum_grades | | mdl_forum_posts | | mdl_forum_queue | | mdl_forum_read | | mdl_forum_subscriptions | | mdl_forum_track_prefs | | mdl_glossary | | mdl_glossary_alias | | mdl_glossary_categories | | mdl_glossary_entries | | mdl_glossary_entries_categories | | mdl_glossary_formats | | mdl_grade_categories | | mdl_grade_categories_history | | mdl_grade_grades | | mdl_grade_grades_history | | mdl_grade_import_newitem | | mdl_grade_import_values | | mdl_grade_items | | mdl_grade_items_history | | mdl_grade_letters | | mdl_grade_outcomes | | mdl_grade_outcomes_courses | | mdl_grade_outcomes_history | | mdl_grade_settings | | mdl_gradepenalty_duedate_rule | | mdl_grading_areas | | mdl_grading_definitions | | mdl_grading_instances | | mdl_gradingform_guide_comments | | mdl_gradingform_guide_criteria | | mdl_gradingform_guide_fillings | | mdl_gradingform_rubric_criteria | | mdl_gradingform_rubric_fillings | | mdl_gradingform_rubric_levels | | mdl_groupings | | mdl_groupings_groups | | mdl_groups | | mdl_groups_members | | mdl_h5p | | mdl_h5p_contents_libraries | | mdl_h5p_libraries | | mdl_h5p_libraries_cachedassets | | mdl_h5p_library_dependencies | | mdl_h5pactivity | | mdl_h5pactivity_attempts | | mdl_h5pactivity_attempts_results | | mdl_imscp | | mdl_infected_files | | mdl_label | | mdl_lesson | | mdl_lesson_answers | | mdl_lesson_attempts | | mdl_lesson_branch | | mdl_lesson_grades | | mdl_lesson_overrides | | mdl_lesson_pages | | mdl_lesson_timer | | mdl_license | | mdl_lock_db | | mdl_log | | mdl_log_display | | mdl_log_queries | | mdl_logstore_standard_log | | mdl_lti | | mdl_lti_access_tokens | | mdl_lti_coursevisible | | mdl_lti_submission | | mdl_lti_tool_proxies | | mdl_lti_tool_settings | | mdl_lti_types | | mdl_lti_types_categories | | mdl_lti_types_config | | mdl_ltiservice_gradebookservices | | mdl_matrix_room | | mdl_message | | mdl_message_airnotifier_devices | | mdl_message_contact_requests | | mdl_message_contacts | | mdl_message_conversation_actions | | mdl_message_conversation_members | | mdl_message_conversations | | mdl_message_email_messages | | mdl_message_popup | | mdl_message_popup_notifications | | mdl_message_processors | | mdl_message_providers | | mdl_message_read | | mdl_message_user_actions | | mdl_message_users_blocked | | mdl_messageinbound_datakeys | | mdl_messageinbound_handlers | | mdl_messageinbound_messagelist | | mdl_messages | | mdl_mnet_application | | mdl_mnet_host | | mdl_mnet_host2service | | mdl_mnet_log | | mdl_mnet_remote_rpc | | mdl_mnet_remote_service2rpc | | mdl_mnet_rpc | | mdl_mnet_service | | mdl_mnet_service2rpc | | mdl_mnet_session | | mdl_mnet_sso_access_control | | mdl_modules | | mdl_moodlenet_share_progress | | mdl_my_pages | | mdl_notifications | | mdl_oauth2_access_token | | mdl_oauth2_endpoint | | mdl_oauth2_issuer | | mdl_oauth2_refresh_token | | mdl_oauth2_system_account | | mdl_oauth2_user_field_mapping | | mdl_page | | mdl_paygw_paypal | | mdl_payment_accounts | | mdl_payment_gateways | | mdl_payments | | mdl_portfolio_instance | | mdl_portfolio_instance_config | | mdl_portfolio_instance_user | | mdl_portfolio_log | | mdl_portfolio_tempdata | | mdl_post | | mdl_profiling | | mdl_qbank | | mdl_qtype_ddimageortext | | mdl_qtype_ddimageortext_drags | | mdl_qtype_ddimageortext_drops | | mdl_qtype_ddmarker | | mdl_qtype_ddmarker_drags | | mdl_qtype_ddmarker_drops | | mdl_qtype_essay_options | | mdl_qtype_match_options | | mdl_qtype_match_subquestions | | mdl_qtype_multichoice_options | | mdl_qtype_ordering_options | | mdl_qtype_randomsamatch_options | | mdl_qtype_shortanswer_options | | mdl_question | | mdl_question_answers | | mdl_question_attempt_step_data | | mdl_question_attempt_steps | | mdl_question_attempts | | mdl_question_bank_entries | | mdl_question_calculated | | mdl_question_calculated_options | | mdl_question_categories | | mdl_question_dataset_definitions | | mdl_question_dataset_items | | mdl_question_datasets | | mdl_question_ddwtos | | mdl_question_gapselect | | mdl_question_hints | | mdl_question_multianswer | | mdl_question_numerical | | mdl_question_numerical_options | | mdl_question_numerical_units | | mdl_question_references | | mdl_question_response_analysis | | mdl_question_response_count | | mdl_question_set_references | | mdl_question_statistics | | mdl_question_truefalse | | mdl_question_usages | | mdl_question_versions | | mdl_quiz | | mdl_quiz_attempts | | mdl_quiz_feedback | | mdl_quiz_grade_items | | mdl_quiz_grades | | mdl_quiz_overrides | | mdl_quiz_overview_regrades | | mdl_quiz_reports | | mdl_quiz_sections | | mdl_quiz_slots | | mdl_quiz_statistics | | mdl_quizaccess_seb_quizsettings | | mdl_quizaccess_seb_template | | mdl_rating | | mdl_registration_hubs | | mdl_reportbuilder_audience | | mdl_reportbuilder_column | | mdl_reportbuilder_filter | | mdl_reportbuilder_report | | mdl_reportbuilder_schedule | | mdl_reportbuilder_user_filter | | mdl_repository | | mdl_repository_instance_config | | mdl_repository_instances | | mdl_repository_onedrive_access | | mdl_resource | | mdl_resource_old | | mdl_role | | mdl_role_allow_assign | | mdl_role_allow_override | | mdl_role_allow_switch | | mdl_role_allow_view | | mdl_role_assignments | | mdl_role_capabilities | | mdl_role_context_levels | | mdl_role_names | | mdl_scale | | mdl_scale_history | | mdl_scorm | | mdl_scorm_aicc_session | | mdl_scorm_attempt | | mdl_scorm_element | | mdl_scorm_scoes | | mdl_scorm_scoes_data | | mdl_scorm_scoes_value | | mdl_scorm_seq_mapinfo | | mdl_scorm_seq_objective | | mdl_scorm_seq_rolluprule | | mdl_scorm_seq_rolluprulecond | | mdl_scorm_seq_rulecond | | mdl_scorm_seq_ruleconds | | mdl_search_index_requests | | mdl_search_simpledb_index | | mdl_sessions | | mdl_shortlink | | mdl_sms_gateways | | mdl_sms_messages | | mdl_stats_daily | | mdl_stats_monthly | | mdl_stats_user_daily | | mdl_stats_user_monthly | | mdl_stats_user_weekly | | mdl_stats_weekly | | mdl_stored_progress | | mdl_subsection | | mdl_tag | | mdl_tag_area | | mdl_tag_coll | | mdl_tag_correlation | | mdl_tag_instance | | mdl_task_adhoc | | mdl_task_log | | mdl_task_scheduled | | mdl_tiny_autosave | | mdl_tool_brickfield_areas | | mdl_tool_brickfield_cache_acts | | mdl_tool_brickfield_cache_check | | mdl_tool_brickfield_checks | | mdl_tool_brickfield_content | | mdl_tool_brickfield_errors | | mdl_tool_brickfield_process | | mdl_tool_brickfield_results | | mdl_tool_brickfield_schedule | | mdl_tool_brickfield_summary | | mdl_tool_cohortroles | | mdl_tool_customlang | | mdl_tool_customlang_components | | mdl_tool_dataprivacy_category | | mdl_tool_dataprivacy_contextlist | | mdl_tool_dataprivacy_ctxexpired | | mdl_tool_dataprivacy_ctxinstance | | mdl_tool_dataprivacy_ctxlevel | | mdl_tool_dataprivacy_ctxlst_ctx | | mdl_tool_dataprivacy_purpose | | mdl_tool_dataprivacy_purposerole | | mdl_tool_dataprivacy_request | | mdl_tool_dataprivacy_rqst_ctxlst | | mdl_tool_mfa | | mdl_tool_mfa_auth | | mdl_tool_mfa_secrets | | mdl_tool_monitor_events | | mdl_tool_monitor_history | | mdl_tool_monitor_rules | | mdl_tool_monitor_subscriptions | | mdl_tool_policy | | mdl_tool_policy_acceptances | | mdl_tool_policy_versions | | mdl_tool_recyclebin_category | | mdl_tool_recyclebin_course | | mdl_tool_usertours_steps | | mdl_tool_usertours_tours | | mdl_upgrade_log | | mdl_url | | mdl_user | | mdl_user_devices | | mdl_user_enrolments | | mdl_user_info_category | | mdl_user_info_data | | mdl_user_info_field | | mdl_user_lastaccess | | mdl_user_password_history | | mdl_user_password_resets | | mdl_user_preferences | | mdl_user_private_key | | mdl_wiki | | mdl_wiki_links | | mdl_wiki_locks | | mdl_wiki_pages | | mdl_wiki_subwikis | | mdl_wiki_synonyms | | mdl_wiki_versions | | mdl_workshop | | mdl_workshop_aggregations | | mdl_workshop_assessments | | mdl_workshop_grades | | mdl_workshop_submissions | | mdl_workshopallocation_scheduled | | mdl_workshopeval_best_settings | | mdl_workshopform_accumulative | | mdl_workshopform_comments | | mdl_workshopform_numerrors | | mdl_workshopform_numerrors_map | | mdl_workshopform_rubric | | mdl_workshopform_rubric_config | | mdl_workshopform_rubric_levels | | mdl_xapi_states | +----------------------------------+ 489 rows in set (0.008 sec) mysql>
主なテーブルとその役割
主なテーブルとその役割について表にまとめておきます．なお，テーブル名の先頭にはデフォルト値である mdl_ プレフィックスが付与されていますが，インストール時の設定 (14) で変更している場合はそのプレフィックスに読替えが必要です．
| 領域 | テーブル | 主な役割 |
|---|---|---|
| コースカテゴリ | mdl_course_categories | 学部・専門科目などのカテゴリ |
| コース | mdl_course | コース基本情報 |
| ユーザ | mdl_user | ユーザ基本情報 |
| コンテキスト | mdl_context | 役割付与のコンテキスト（サイト／カテゴリ／コース／モジュール） |
| 役割 (role) | mdl_role, mdl_role_assignments | 権限ロールと割り当て |
| 登録 (rnrol) | mdl_enrol, mdl_user_enrolments | コースの登録方法・個々の受講登録 |
| セクション | mdl_course_sections | コースに設置された（授業日ごとなどに分ける）セクション |
| 活動／モジュール | mdl_course_modules, mdl_modules | コースに配置された各活動の紐付け |
| 小テスト | mdl_quiz | コースに配置された小テスト |
| 課題 | mdl_assign | コースに配置された課題 |
フィールドも厳選した状態で主要なテーブルについて，E-R図 (Entity-Relationship図) を示しておきます．なお，後で説明するとおり，Moodle のデータベース自体にはリレーションシップと外部キー制約はほとんど設定されておらず，Moodle のアプリケーション側で処理されています．
なお，上のE-R図は次の Markdown 形式のテキストファイルから作成しています．具体的には，Visual Studio Code に「Markdown Preview Mermaid Support」と「Markdown PDF」という拡張機能をインストールし，PDF 形式でエクスポートしています．（その後，Adobe Illustrator にて SVG 形式に変換しています．）
moodle_er.md
```mermaid
erDiagram
%% =============================
%% テーブルとその主要なフィールド
%% =============================
mdl_course_categories {
bigint id PK
varchar name
varchar idnumber
bigint coursecount
}
mdl_course {
bigint id PK
bigint category
varchar fullname
varchar shortname
tinyint visible
}
mdl_context {
bigint id PK
bigint contextlevel
bigint instanceid
varchar path
tinyint depth
}
mdl_role {
bigint id PK
varchar name
varchar shortname
}
mdl_role_assignments {
bigint id PK
bigint roleid
bigint contextid
bigint userid
bigint timemodified
}
mdl_user {
bigint id PK
tinyint deleted
varchar username
varchar firstname
varchar lastname
varchar email
varchar country
varchar timezone
}
mdl_enrol {
bigint id PK
varchar enrol
bigint status
bigint courseid
bigint sortorder
bigint roleid
}
mdl_user_enrolments {
bigint id PK
bigint status
bigint enrolid
bigint userid
bigint timestart
bigint timeend
bigint modifierid
}
mdl_quiz {
bigint id PK
bigint course
varchar name
bigint timeopen
bigint timeclose
bigint timelimit
decimal grade
}
mdl_assign {
bigint id PK
bigint course
varchar name
longtext intro
bigint duedate
bigint timeclose
bigint allowsubmissionsfromdate
bigint grade
}
mdl_modules {
bigint id PK
varchar name
bigint cron
bigint lastcron
varchar search
tinyint visible
}
mdl_course_modules {
bigint id PK
bigint course
bigint module
bigint instance
bigint section
varchar idnumber
tinyint visible
}
mdl_course_sections {
bigint id PK
bigint course
bigint section
varchar name
longtext sequence
tinyint visible
}
%% =============================
%% リレーションシップ（アプリ側で保持）
%% =============================
%% ユーザーとロール割り当て（コンテキスト単位）
mdl_user ||--o{ mdl_role_assignments : "assigned (by role)"
mdl_role ||--o{ mdl_role_assignments : "has many assignments"
mdl_context ||--o{ mdl_role_assignments : "in context"
%% コースとカテゴリの関係
mdl_course_categories ||--o{ mdl_course : "has many courses"
%% コースとコンテキストの関係
%% context(contextlevel=50, instanceid=mdl_course.id)
mdl_course ||--o{ mdl_context : "has course-context (50)"
%% 登録（Enrol）とユーザーの関係
mdl_course ||--o{ mdl_enrol : "has enrol methods"
mdl_enrol ||--o{ mdl_user_enrolments : "has many user enrolments"
mdl_user ||--o{ mdl_user_enrolments : "enrolled via"
%% コースとクイズ
mdl_course ||--o{ mdl_quiz: "has many quizes"
%% コースと課題
mdl_course ||--o{ mdl_assign: "has many assigns"
%% コースとセクション
mdl_course ||--o{ mdl_course_sections : "has many sections"
%% 活動（モジュール）とコースの関係
mdl_course ||--o{ mdl_course_modules : "has activities"
mdl_modules ||--o{ mdl_course_modules : "typed by module"
```
カテゴリ一覧 (mdl_course_categories)
それでは，主要なテーブルを順番に掘り下げていきます．カテゴリ一覧は mdl_course_categories テーブルに保存されています．DESC 文でテーブル構造を確認します．
DESC mdl_course_categories;
mysql> DESC mdl_course_categories; ⏎
+-------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+----------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | | |
| idnumber | varchar(100) | YES | | NULL | |
| description | longtext | YES | | NULL | |
| descriptionformat | tinyint | NO | | 0 | |
| parent | bigint | NO | MUL | 0 | |
| sortorder | bigint | NO | | 0 | |
| coursecount | bigint | NO | | 0 | |
| visible | tinyint(1) | NO | | 1 | |
| visibleold | tinyint(1) | NO | | 1 | |
| timemodified | bigint | NO | | 0 | |
| depth | bigint | NO | | 0 | |
| path | varchar(255) | NO | | | |
| theme | varchar(50) | YES | | NULL | |
+-------------------+--------------+------+-----+---------+----------------+
14 rows in set (0.006 sec)
mysql>
次にテーブル作成時に利用されたコマンドを確認してみます．
SHOW CREATE TABLE mdl_course_categories\G
mysql> SHOW CREATE TABLE mdl_course_categories\G ⏎
*************************** 1. row ***************************
Table: mdl_course_categories
Create Table: CREATE TABLE `mdl_course_categories` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`idnumber` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
`descriptionformat` tinyint NOT NULL DEFAULT '0',
`parent` bigint NOT NULL DEFAULT '0',
`sortorder` bigint NOT NULL DEFAULT '0',
`coursecount` bigint NOT NULL DEFAULT '0',
`visible` tinyint(1) NOT NULL DEFAULT '1',
`visibleold` tinyint(1) NOT NULL DEFAULT '1',
`timemodified` bigint NOT NULL DEFAULT '0',
`depth` bigint NOT NULL DEFAULT '0',
`path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`theme` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `mdl_courcate_par_ix` (`parent`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPRESSED COMMENT='Course categories'
1 row in set (0.001 sec)
mysql>
登録済みのカテゴリを一覧で表示します．
SELECT * FROM mdl_course_categories\G
mysql> SELECT * FROM mdl_course_categories\G ⏎
*************************** 1. row ***************************
id: 2
name: 法学部
idnumber: cat_law
description:
descriptionformat: 1
parent: 0
sortorder: 10000
coursecount: 2
visible: 1
visibleold: 1
timemodified: 1767231538
depth: 1
path: /2
theme: NULL
*************************** 2. row ***************************
id: 3
name: 経営学部
idnumber: cat_ba
description:
descriptionformat: 1
parent: 0
sortorder: 20000
coursecount: 2
visible: 1
visibleold: 1
timemodified: 1767231563
depth: 1
path: /3
theme: NULL
*************************** 3. row ***************************
id: 4
name: 共通教育科目
idnumber: cat_ge
description:
descriptionformat: 1
parent: 0
sortorder: 30000
coursecount: 3
visible: 1
visibleold: 1
timemodified: 1767231750
depth: 1
path: /4
theme: NULL
3 rows in set (0.001 sec)
mysql>
フィールドを最低限に絞ってカテゴリの一覧を見てみましょう．各カテゴリに何個のコースが割り当てられているかが coursecount に保存されています．
SELECT id, name, idnumber, coursecount FROM mdl_course_categories;
mysql> SELECT id, name, idnumber, coursecount
-> FROM mdl_course_categories; ⏎
+----+--------------------+----------+-------------+
| id | name | idnumber | coursecount |
+----+--------------------+----------+-------------+
| 2 | 法学部 | cat_law | 2 |
| 3 | 経営学部 | cat_ba | 2 |
| 4 | 共通教育科目 | cat_ge | 3 |
+----+--------------------+----------+-------------+
3 rows in set (0.007 sec)
mysql>
コース一覧 (mdl_course)
次はコース（講義科目）の一覧を確認してみます．まず，テーブルの定義を確認します．
DESC mdl_course;
mysql> DESC mdl_course; ⏎
+--------------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------+------+-----+---------+----------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| category | bigint | NO | MUL | 0 | |
| sortorder | bigint | NO | MUL | 0 | |
| fullname | varchar(1333) | NO | | | |
| shortname | varchar(255) | NO | MUL | | |
| idnumber | varchar(100) | NO | MUL | | |
| summary | longtext | YES | | NULL | |
| summaryformat | tinyint | NO | | 0 | |
| format | varchar(21) | NO | | topics | |
| showgrades | tinyint | NO | | 1 | |
| newsitems | mediumint | NO | | 1 | |
| startdate | bigint | NO | | 0 | |
| enddate | bigint | NO | | 0 | |
| relativedatesmode | tinyint(1) | NO | | 0 | |
| marker | bigint | NO | | 0 | |
| maxbytes | bigint | NO | | 0 | |
| legacyfiles | smallint | NO | | 0 | |
| showreports | smallint | NO | | 0 | |
| visible | tinyint(1) | NO | | 1 | |
| visibleold | tinyint(1) | NO | | 1 | |
| downloadcontent | tinyint(1) | YES | | NULL | |
| groupmode | smallint | NO | | 0 | |
| groupmodeforce | smallint | NO | | 0 | |
| defaultgroupingid | bigint | NO | | 0 | |
| lang | varchar(30) | NO | | | |
| calendartype | varchar(30) | NO | | | |
| theme | varchar(50) | NO | | | |
| timecreated | bigint | NO | | 0 | |
| timemodified | bigint | NO | | 0 | |
| requested | tinyint(1) | NO | | 0 | |
| enablecompletion | tinyint(1) | NO | | 0 | |
| completionnotify | tinyint(1) | NO | | 0 | |
| cacherev | bigint | NO | | 0 | |
| originalcourseid | bigint | YES | MUL | NULL | |
| showactivitydates | tinyint(1) | NO | | 0 | |
| showcompletionconditions | tinyint(1) | YES | | NULL | |
| pdfexportfont | varchar(50) | YES | | NULL | |
| enableaitools | tinyint(1) | YES | | NULL | |
+--------------------------+---------------+------+-----+---------+----------------+
38 rows in set (0.014 sec)
mysql>
次はテーブル作成時のコマンドです．
SHOW CREATE TABLE mdl_course\G
mysql> SHOW CREATE TABLE mdl_course\G ⏎
*************************** 1. row ***************************
Table: mdl_course
Create Table: CREATE TABLE `mdl_course` (
`id` bigint NOT NULL AUTO_INCREMENT,
`category` bigint NOT NULL DEFAULT '0',
`sortorder` bigint NOT NULL DEFAULT '0',
`fullname` varchar(1333) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`shortname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`idnumber` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`summary` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
`summaryformat` tinyint NOT NULL DEFAULT '0',
`format` varchar(21) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'topics',
`showgrades` tinyint NOT NULL DEFAULT '1',
`newsitems` mediumint NOT NULL DEFAULT '1',
`startdate` bigint NOT NULL DEFAULT '0',
`enddate` bigint NOT NULL DEFAULT '0',
`relativedatesmode` tinyint(1) NOT NULL DEFAULT '0',
`marker` bigint NOT NULL DEFAULT '0',
`maxbytes` bigint NOT NULL DEFAULT '0',
`legacyfiles` smallint NOT NULL DEFAULT '0',
`showreports` smallint NOT NULL DEFAULT '0',
`visible` tinyint(1) NOT NULL DEFAULT '1',
`visibleold` tinyint(1) NOT NULL DEFAULT '1',
`downloadcontent` tinyint(1) DEFAULT NULL,
`groupmode` smallint NOT NULL DEFAULT '0',
`groupmodeforce` smallint NOT NULL DEFAULT '0',
`defaultgroupingid` bigint NOT NULL DEFAULT '0',
`lang` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`calendartype` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`theme` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`timecreated` bigint NOT NULL DEFAULT '0',
`timemodified` bigint NOT NULL DEFAULT '0',
`requested` tinyint(1) NOT NULL DEFAULT '0',
`enablecompletion` tinyint(1) NOT NULL DEFAULT '0',
`completionnotify` tinyint(1) NOT NULL DEFAULT '0',
`cacherev` bigint NOT NULL DEFAULT '0',
`originalcourseid` bigint DEFAULT NULL,
`showactivitydates` tinyint(1) NOT NULL DEFAULT '0',
`showcompletionconditions` tinyint(1) DEFAULT NULL,
`pdfexportfont` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`enableaitools` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `mdl_cour_cat_ix` (`category`),
KEY `mdl_cour_idn_ix` (`idnumber`),
KEY `mdl_cour_sho_ix` (`shortname`),
KEY `mdl_cour_sor_ix` (`sortorder`),
KEY `mdl_cour_ori_ix` (`originalcourseid`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPRESSED COMMENT='Central course table'
1 row in set (0.001 sec)
mysql>
コース (mdl_course) テーブルのすべてのフィールドを2件に絞って表示してみます．
SELECT * FROM mdl_course LIMIT 2 \G
mysql> SELECT *
-> FROM mdl_course
-> LIMIT 2 \G ⏎
*************************** 1. row ***************************
id: 1
category: 0
sortorder: 1
fullname: Moodleテストサイト
shortname: Moodle
idnumber:
summary:
summaryformat: 0
format: site
showgrades: 1
newsitems: 3
startdate: 0
enddate: 0
relativedatesmode: 0
marker: 0
maxbytes: 0
legacyfiles: 0
showreports: 0
visible: 1
visibleold: 1
downloadcontent: NULL
groupmode: 0
groupmodeforce: 0
defaultgroupingid: 0
lang:
calendartype:
theme:
timecreated: 1767230041
timemodified: 1767231409
requested: 0
enablecompletion: 0
completionnotify: 0
cacherev: 1767230087
originalcourseid: NULL
showactivitydates: 0
showcompletionconditions: NULL
pdfexportfont: NULL
enableaitools: NULL
*************************** 2. row ***************************
id: 2
category: 2
sortorder: 10002
fullname: 憲法
shortname: 憲法
idnumber:
summary: NULL
summaryformat: 0
format: topics
showgrades: 1
newsitems: 5
startdate: 1743433200
enddate: 1774882800
relativedatesmode: 0
marker: 0
maxbytes: 0
legacyfiles: 0
showreports: 0
visible: 1
visibleold: 1
downloadcontent: NULL
groupmode: 0
groupmodeforce: 0
defaultgroupingid: 0
lang:
calendartype:
theme:
timecreated: 1767231781
timemodified: 1767231781
requested: 0
enablecompletion: 1
completionnotify: 0
cacherev: 1767231787
originalcourseid: NULL
showactivitydates: 1
showcompletionconditions: 1
pdfexportfont: NULL
enableaitools: NULL
2 rows in set (0.004 sec)
mysql>
フィールドを絞ってすべてのコースを表示してみましょう．このとき，カテゴリ id のフィールド（つまり，外部キー）が含まれていることに注意してください．一方で，テーブル作成時のコマンドを見ると，リレーションシップに関する設定がされていないことも分かります．Moodle のデータベースでは外部キー制約 (FK) をほとんど使わず，アプリケーション側でリレーションを管理する方針になっています．本来，データベース側で参照整合性をチェックした方が安全だと考えられますが，柔軟性を高く，プラグイン追加やスキーマ変更が容易になるために，そのような方策にしているようです．
SELECT id, category, shortname, fullname, visible FROM mdl_course;
mysql> SELECT id, category, shortname, fullname, visible
-> FROM mdl_course; ⏎
+----+----------+-----------------+--------------------------+---------+
| id | category | shortname | fullname | visible |
+----+----------+-----------------+--------------------------+---------+
| 1 | 0 | Moodle | Moodleテストサイト | 1 |
| 2 | 2 | 憲法 | 憲法 | 1 |
| 3 | 2 | 民法 | 民法 | 1 |
| 4 | 3 | 経営学総論 | 経営学総論 | 1 |
| 5 | 3 | 簿記論 | 簿記論 | 1 |
| 6 | 4 | 基礎英語 | 基礎英語 | 1 |
| 7 | 4 | 実用英語 | 実用英語 | 1 |
| 8 | 4 | ICT実習 | ICT実習 | 1 |
+----+----------+-----------------+--------------------------+---------+
8 rows in set (0.009 sec)
mysql>
コース一覧（カテゴリ付き）
ここで，コーステーブル (mdl_course) とカテゴリテーブル (mdl_course_categories) を結合し，コース一覧にカテゴリ情報も表示できるようにしてみます．これによって，各コース（講義科目）がどのカテゴリ（学部専門科目／共通教育科目）に所属するかが明らかになりました．
SELECT c.id, c.shortname, c.fullname,
cat.id AS categoryid,
cat.name AS categoryname
FROM mdl_course AS c
INNER JOIN mdl_course_categories AS cat ON cat.id = c.category;
mysql> SELECT c.id, c.shortname, c.fullname,
-> cat.id AS categoryid,
-> cat.name AS categoryname
-> FROM mdl_course AS c
-> INNER JOIN mdl_course_categories AS cat ON cat.id = c.category; ⏎
+----+-----------------+-----------------+------------+--------------------+
| id | shortname | fullname | categoryid | categoryname |
+----+-----------------+-----------------+------------+--------------------+
| 2 | 憲法 | 憲法 | 2 | 法学部 |
| 3 | 民法 | 民法 | 2 | 法学部 |
| 4 | 経営学総論 | 経営学総論 | 3 | 経営学部 |
| 5 | 簿記論 | 簿記論 | 3 | 経営学部 |
| 6 | 基礎英語 | 基礎英語 | 4 | 共通教育科目 |
| 7 | 実用英語 | 実用英語 | 4 | 共通教育科目 |
| 8 | ICT実習 | ICT実習 | 4 | 共通教育科目 |
+----+-----------------+-----------------+------------+--------------------+
7 rows in set (0.001 sec)
mysql>
ユーザの一覧 (mdl_user)
ここではユーザを一覧で確認してみましょう．まず，テーブルの定義を表示します．
DESC mdl_user;
mysql> DESC mdl_user; ⏎
+-------------------+--------------+------+-----+-----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+-----------+----------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| auth | varchar(20) | NO | MUL | manual | |
| confirmed | tinyint(1) | NO | MUL | 0 | |
| policyagreed | tinyint(1) | NO | | 0 | |
| deleted | tinyint(1) | NO | MUL | 0 | |
| suspended | tinyint(1) | NO | | 0 | |
| mnethostid | bigint | NO | MUL | 0 | |
| username | varchar(100) | NO | | | |
| password | varchar(255) | NO | | | |
| idnumber | varchar(255) | NO | MUL | | |
| firstname | varchar(100) | NO | MUL | | |
| lastname | varchar(100) | NO | MUL | | |
| email | varchar(100) | NO | MUL | | |
| emailstop | tinyint(1) | NO | | 0 | |
| phone1 | varchar(20) | NO | | | |
| phone2 | varchar(20) | NO | | | |
| institution | varchar(255) | NO | | | |
| department | varchar(255) | NO | | | |
| address | varchar(255) | NO | | | |
| city | varchar(120) | NO | MUL | | |
| country | varchar(2) | NO | MUL | | |
| lang | varchar(30) | NO | | en | |
| calendartype | varchar(30) | NO | | gregorian | |
| theme | varchar(50) | NO | | | |
| timezone | varchar(100) | NO | | 99 | |
| firstaccess | bigint | NO | | 0 | |
| lastaccess | bigint | NO | MUL | 0 | |
| lastlogin | bigint | NO | | 0 | |
| currentlogin | bigint | NO | | 0 | |
| lastip | varchar(45) | NO | | | |
| secret | varchar(15) | NO | | | |
| picture | bigint | NO | | 0 | |
| description | longtext | YES | | NULL | |
| descriptionformat | tinyint | NO | | 1 | |
| mailformat | tinyint(1) | NO | | 1 | |
| maildigest | tinyint(1) | NO | | 0 | |
| maildisplay | tinyint | NO | | 2 | |
| autosubscribe | tinyint(1) | NO | | 1 | |
| trackforums | tinyint(1) | NO | | 0 | |
| timecreated | bigint | NO | | 0 | |
| timemodified | bigint | NO | | 0 | |
| trustbitmask | bigint | NO | | 0 | |
| imagealt | varchar(255) | YES | | NULL | |
| lastnamephonetic | varchar(255) | YES | MUL | NULL | |
| firstnamephonetic | varchar(255) | YES | MUL | NULL | |
| middlename | varchar(255) | YES | MUL | NULL | |
| alternatename | varchar(255) | YES | MUL | NULL | |
| moodlenetprofile | varchar(255) | YES | | NULL | |
+-------------------+--------------+------+-----+-----------+----------------+
48 rows in set (0.009 sec)
mysql>
テーブル作成時のコマンドも見ておきます．
SHOW CREATE TABLE mdl_user\G
mysql> SHOW CREATE TABLE mdl_user\G ⏎
*************************** 1. row ***************************
Table: mdl_user
Create Table: CREATE TABLE `mdl_user` (
`id` bigint NOT NULL AUTO_INCREMENT,
`auth` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'manual',
`confirmed` tinyint(1) NOT NULL DEFAULT '0',
`policyagreed` tinyint(1) NOT NULL DEFAULT '0',
`deleted` tinyint(1) NOT NULL DEFAULT '0',
`suspended` tinyint(1) NOT NULL DEFAULT '0',
`mnethostid` bigint NOT NULL DEFAULT '0',
`username` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`idnumber` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`firstname` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`lastname` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`emailstop` tinyint(1) NOT NULL DEFAULT '0',
`phone1` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`phone2` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`institution` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`department` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`city` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`country` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`lang` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'en',
`calendartype` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'gregorian',
`theme` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`timezone` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '99',
`firstaccess` bigint NOT NULL DEFAULT '0',
`lastaccess` bigint NOT NULL DEFAULT '0',
`lastlogin` bigint NOT NULL DEFAULT '0',
`currentlogin` bigint NOT NULL DEFAULT '0',
`lastip` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`secret` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`picture` bigint NOT NULL DEFAULT '0',
`description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
`descriptionformat` tinyint NOT NULL DEFAULT '1',
`mailformat` tinyint(1) NOT NULL DEFAULT '1',
`maildigest` tinyint(1) NOT NULL DEFAULT '0',
`maildisplay` tinyint NOT NULL DEFAULT '2',
`autosubscribe` tinyint(1) NOT NULL DEFAULT '1',
`trackforums` tinyint(1) NOT NULL DEFAULT '0',
`timecreated` bigint NOT NULL DEFAULT '0',
`timemodified` bigint NOT NULL DEFAULT '0',
`trustbitmask` bigint NOT NULL DEFAULT '0',
`imagealt` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`lastnamephonetic` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`firstnamephonetic` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`middlename` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`alternatename` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`moodlenetprofile` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `mdl_user_mneuse_uix` (`mnethostid`,`username`),
KEY `mdl_user_del_ix` (`deleted`),
KEY `mdl_user_con_ix` (`confirmed`),
KEY `mdl_user_fir_ix` (`firstname`),
KEY `mdl_user_las_ix` (`lastname`),
KEY `mdl_user_cit_ix` (`city`),
KEY `mdl_user_cou_ix` (`country`),
KEY `mdl_user_las2_ix` (`lastaccess`),
KEY `mdl_user_ema_ix` (`email`),
KEY `mdl_user_aut_ix` (`auth`),
KEY `mdl_user_idn_ix` (`idnumber`),
KEY `mdl_user_fir2_ix` (`firstnamephonetic`),
KEY `mdl_user_las3_ix` (`lastnamephonetic`),
KEY `mdl_user_mid_ix` (`middlename`),
KEY `mdl_user_alt_ix` (`alternatename`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPRESSED COMMENT='One record for each person'
1 row in set (0.003 sec)
mysql>
最初の3件に限定して，すべてのフィールドを表示してみます．このときパスワード (password フィールド) はハッシュ化されていることに注意してください．
SELECT * FROM mdl_user LIMIT 3\G
mysql> SELECT * FROM mdl_user LIMIT 3\G ⏎
*************************** 1. row ***************************
id: 1
auth: manual
confirmed: 1
policyagreed: 0
deleted: 0
suspended: 0
mnethostid: 1
username: guest
password: $6$rounds=10000$kzvMPDrA8zm0j7Cr$MrXE4R6Z1ru3YgsqR1cgr2PsGOJjx76/vvR3VaGjbQoAGeUx5Juk3xTsRNg1QK5h9lms2qhZJVkVxWsFvrOLn/
idnumber:
firstname: ゲストユーザ
lastname:
email: root@localhost
emailstop: 0
phone1:
phone2:
institution:
department:
address:
city:
country:
lang: ja
calendartype: gregorian
theme:
timezone: 99
firstaccess: 0
lastaccess: 0
lastlogin: 0
currentlogin: 0
lastip:
secret:
picture: 0
description: このユーザはいくつかのコースに閲覧のみ許可された特別ユーザです。
descriptionformat: 1
mailformat: 1
maildigest: 0
maildisplay: 2
autosubscribe: 1
trackforums: 0
timecreated: 0
timemodified: 1767230041
trustbitmask: 0
imagealt: NULL
lastnamephonetic: NULL
firstnamephonetic: NULL
middlename: NULL
alternatename: NULL
moodlenetprofile: NULL
*************************** 2. row ***************************
id: 2
auth: manual
confirmed: 1
policyagreed: 0
deleted: 0
suspended: 0
mnethostid: 1
username: admin
password: $6$rounds=10000$ksJAXccXxolejvyj$E1tk9PJ0gALJvnApe/WVezWStBRbCaMINU.MDOuEFhC8jEeYbLubhr2/JldHeMYTBrds.D4K/YTgckWNpJ09n.
idnumber:
firstname: 管理
lastname: ユーザ
email: ?????????????@gmail.com
emailstop: 0
phone1:
phone2:
institution:
department:
address:
city:
country:
lang: ja
calendartype: gregorian
theme:
timezone: 99
firstaccess: 1767230448
lastaccess: 1767237828
lastlogin: 1767233107
currentlogin: 1767237828
lastip: 172.29.0.1
secret:
picture: 0
description: NULL
descriptionformat: 1
mailformat: 1
maildigest: 0
maildisplay: 1
autosubscribe: 1
trackforums: 0
timecreated: 0
timemodified: 1767230041
trustbitmask: 0
imagealt: NULL
lastnamephonetic: NULL
firstnamephonetic: NULL
middlename: NULL
alternatename: NULL
moodlenetprofile: NULL
*************************** 3. row ***************************
id: 3
auth: manual
confirmed: 1
policyagreed: 0
deleted: 0
suspended: 0
mnethostid: 1
username: teacher01
password: $6$rounds=5000$KUKGzluZ6Saz7R8w$EQ7z5IUiJfz88Yojt2bYcFk8nwt5mUxer2fkhjaRfvOHqR3fX3gLfFeWBkO8Szv0c6Za1F.gE3Z1XMrtT9cTr0
idnumber:
firstname: Teacher
lastname: t01
email: teacher01@example.com
emailstop: 0
phone1:
phone2:
institution:
department:
address:
city:
country: JP
lang: ja
calendartype: gregorian
theme:
timezone: Asia/Tokyo
firstaccess: 1767232827
lastaccess: 1767233003
lastlogin: 1767232827
currentlogin: 1767233003
lastip: 172.29.0.1
secret:
picture: 0
description:
descriptionformat: 1
mailformat: 1
maildigest: 0
maildisplay: 2
autosubscribe: 1
trackforums: 0
timecreated: 1767231934
timemodified: 1767231934
trustbitmask: 0
imagealt: NULL
lastnamephonetic: NULL
firstnamephonetic: NULL
middlename: NULL
alternatename: NULL
moodlenetprofile: NULL
3 rows in set (0.001 sec)
mysql>
フィールドを限定してユーザの情報を8件だけ表示してみます．このとき，管理者ユーザ，教員ユーザと学生ユーザが同じテーブルにまとめて保存されていることにも注意してください．
SELECT id, username, firstname, lastname, email, lang, timezone FROM mdl_user LIMIT 8;
mysql> SELECT id, username, firstname, lastname, email, lang, timezone
-> FROM mdl_user
-> LIMIT 8; ⏎
+----+-----------+--------------------+-----------+-------------------------+------+------------+
| id | username | firstname | lastname | email | lang | timezone |
+----+-----------+--------------------+-----------+-------------------------+------+------------+
| 1 | guest | ゲストユーザ | | root@localhost | ja | 99 |
| 2 | admin | 管理 | ユーザ | ?????????????@gmail.com | ja | 99 |
| 3 | teacher01 | Teacher | t01 | teacher01@example.com | ja | Asia/Tokyo |
| 4 | teacher02 | Teacher | t02 | teacher02@example.com | ja | Asia/Tokyo |
| 5 | teacher03 | Teacher | t03 | teacher03@example.com | ja | Asia/Tokyo |
| 6 | student01 | Student | s01 | student01@example.com | ja | Asia/Tokyo |
| 7 | student02 | Student | s02 | student02@example.com | ja | Asia/Tokyo |
| 8 | student03 | Student | s03 | student03@example.com | ja | Asia/Tokyo |
+----+-----------+--------------------+-----------+-------------------------+------+------------+
8 rows in set (0.001 sec)
mysql>
登録 (mdl_enrol)
テーブル mdl_enrol にはコースごとにどのようなユーザ登録方法を許可しているかの情報が保存されています．まずはテーブルの定義を確認します．
DESC mdl_enrol;
mysql> DESC mdl_enrol; ⏎
+-----------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+----------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| enrol | varchar(20) | NO | MUL | | |
| status | bigint | NO | | 0 | |
| courseid | bigint | NO | MUL | NULL | |
| sortorder | bigint | NO | | 0 | |
| name | varchar(255) | YES | | NULL | |
| enrolperiod | bigint | YES | | 0 | |
| enrolstartdate | bigint | YES | | 0 | |
| enrolenddate | bigint | YES | | 0 | |
| expirynotify | tinyint(1) | YES | | 0 | |
| expirythreshold | bigint | YES | | 0 | |
| notifyall | tinyint(1) | YES | | 0 | |
| password | varchar(50) | YES | | NULL | |
| cost | varchar(20) | YES | | NULL | |
| currency | varchar(3) | YES | | NULL | |
| roleid | bigint | YES | MUL | 0 | |
| customint1 | bigint | YES | | NULL | |
| customint2 | bigint | YES | | NULL | |
| customint3 | bigint | YES | | NULL | |
| customint4 | bigint | YES | | NULL | |
| customint5 | bigint | YES | | NULL | |
| customint6 | bigint | YES | | NULL | |
| customint7 | bigint | YES | | NULL | |
| customint8 | bigint | YES | | NULL | |
| customchar1 | varchar(255) | YES | | NULL | |
| customchar2 | varchar(255) | YES | | NULL | |
| customchar3 | varchar(1333) | YES | | NULL | |
| customdec1 | decimal(12,7) | YES | | NULL | |
| customdec2 | decimal(12,7) | YES | | NULL | |
| customtext1 | longtext | YES | | NULL | |
| customtext2 | longtext | YES | | NULL | |
| customtext3 | longtext | YES | | NULL | |
| customtext4 | longtext | YES | | NULL | |
| timecreated | bigint | NO | | 0 | |
| timemodified | bigint | NO | | 0 | |
+-----------------+---------------+------+-----+---------+----------------+
35 rows in set (0.012 sec)
mysql>
次にテーブル作成時に利用されたコマンドを確認してみます．
SHOW CREATE TABLE mdl_enrol\G
mysql> SHOW CREATE TABLE mdl_enrol\G ⏎
*************************** 1. row ***************************
Table: mdl_enrol
Create Table: CREATE TABLE `mdl_enrol` (
`id` bigint NOT NULL AUTO_INCREMENT,
`enrol` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`status` bigint NOT NULL DEFAULT '0',
`courseid` bigint NOT NULL,
`sortorder` bigint NOT NULL DEFAULT '0',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`enrolperiod` bigint DEFAULT '0',
`enrolstartdate` bigint DEFAULT '0',
`enrolenddate` bigint DEFAULT '0',
`expirynotify` tinyint(1) DEFAULT '0',
`expirythreshold` bigint DEFAULT '0',
`notifyall` tinyint(1) DEFAULT '0',
`password` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`cost` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`currency` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`roleid` bigint DEFAULT '0',
`customint1` bigint DEFAULT NULL,
`customint2` bigint DEFAULT NULL,
`customint3` bigint DEFAULT NULL,
`customint4` bigint DEFAULT NULL,
`customint5` bigint DEFAULT NULL,
`customint6` bigint DEFAULT NULL,
`customint7` bigint DEFAULT NULL,
`customint8` bigint DEFAULT NULL,
`customchar1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`customchar2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`customchar3` varchar(1333) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`customdec1` decimal(12,7) DEFAULT NULL,
`customdec2` decimal(12,7) DEFAULT NULL,
`customtext1` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
`customtext2` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
`customtext3` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
`customtext4` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
`timecreated` bigint NOT NULL DEFAULT '0',
`timemodified` bigint NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `mdl_enro_enr_ix` (`enrol`),
KEY `mdl_enro_cou_ix` (`courseid`),
KEY `mdl_enro_rol_ix` (`roleid`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPRESSED COMMENT='Instances of enrolment plugins used in courses, fields marke'
1 row in set (0.001 sec)
mysql>
フィールドを限定してすべてのレコードを表示してみます．以前のページでは共通教育科目「基礎英語: courseid = 6」，「実用英語: courseid = 7」，「ICT実習： courseid = 8」についてコーホート同期の設定を行いました．例えばそのような情報もこのテーブルに保存されています．
SELECT id, enrol, status, courseid, sortorder, roleid FROM mdl_enrol;
mysql> SELECT id, enrol, status, courseid, sortorder, roleid
-> FROM mdl_enrol; ⏎
+----+--------+--------+----------+-----------+--------+
| id | enrol | status | courseid | sortorder | roleid |
+----+--------+--------+----------+-----------+--------+
| 1 | manual | 0 | 2 | 0 | 5 |
| 2 | guest | 1 | 2 | 1 | 0 |
| 3 | self | 1 | 2 | 2 | 5 |
| 4 | manual | 0 | 3 | 0 | 5 |
| 5 | guest | 1 | 3 | 1 | 0 |
| 6 | self | 1 | 3 | 2 | 5 |
| 7 | manual | 0 | 4 | 0 | 5 |
| 8 | guest | 1 | 4 | 1 | 0 |
| 9 | self | 1 | 4 | 2 | 5 |
| 10 | manual | 0 | 5 | 0 | 5 |
| 11 | guest | 1 | 5 | 1 | 0 |
| 12 | self | 1 | 5 | 2 | 5 |
| 13 | manual | 0 | 6 | 0 | 5 |
| 14 | guest | 1 | 6 | 1 | 0 |
| 15 | self | 1 | 6 | 2 | 5 |
| 16 | manual | 0 | 7 | 0 | 5 |
| 17 | guest | 1 | 7 | 1 | 0 |
| 18 | self | 1 | 7 | 2 | 5 |
| 19 | manual | 0 | 8 | 0 | 5 |
| 20 | guest | 1 | 8 | 1 | 0 |
| 21 | self | 1 | 8 | 2 | 5 |
| 22 | cohort | 0 | 6 | 3 | 5 |
| 23 | cohort | 0 | 6 | 4 | 5 |
| 24 | cohort | 0 | 6 | 5 | 5 |
| 25 | cohort | 0 | 7 | 3 | 5 |
| 26 | cohort | 0 | 7 | 4 | 5 |
| 27 | cohort | 0 | 8 | 3 | 5 |
| 28 | cohort | 0 | 8 | 4 | 5 |
| 29 | cohort | 0 | 8 | 5 | 5 |
+----+--------+--------+----------+-----------+--------+
29 rows in set (0.012 sec)
mysql>
ユーザ登録 (mdl_user_enrolments)
まず，テーブル mdl_user_enrolments の定義を確認します．
DESC mdl_user_enrolments;
mysql> DESC mdl_user_enrolments; ⏎
+--------------+--------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------+------+-----+------------+----------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| status | bigint | NO | | 0 | |
| enrolid | bigint | NO | MUL | NULL | |
| userid | bigint | NO | MUL | NULL | |
| timestart | bigint | NO | | 0 | |
| timeend | bigint | NO | | 2147483647 | |
| modifierid | bigint | NO | MUL | 0 | |
| timecreated | bigint | NO | | 0 | |
| timemodified | bigint | NO | | 0 | |
+--------------+--------+------+-----+------------+----------------+
9 rows in set (0.012 sec)
mysql>
次にテーブル作成時に利用されたコマンドを確認してみます．
SHOW CREATE TABLE mdl_user_enrolments\G
mysql> SHOW CREATE TABLE mdl_user_enrolments\G ⏎
*************************** 1. row ***************************
Table: mdl_user_enrolments
Create Table: CREATE TABLE `mdl_user_enrolments` (
`id` bigint NOT NULL AUTO_INCREMENT,
`status` bigint NOT NULL DEFAULT '0',
`enrolid` bigint NOT NULL,
`userid` bigint NOT NULL,
`timestart` bigint NOT NULL DEFAULT '0',
`timeend` bigint NOT NULL DEFAULT '2147483647',
`modifierid` bigint NOT NULL DEFAULT '0',
`timecreated` bigint NOT NULL DEFAULT '0',
`timemodified` bigint NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `mdl_userenro_enruse_uix` (`enrolid`,`userid`),
KEY `mdl_userenro_enr_ix` (`enrolid`),
KEY `mdl_userenro_use_ix` (`userid`),
KEY `mdl_userenro_mod_ix` (`modifierid`)
) ENGINE=InnoDB AUTO_INCREMENT=248 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPRESSED COMMENT='Users participating in courses (aka enrolled users) - everyb'
1 row in set (0.001 sec)
mysql>
最初の5件に限定してすべてのフィールドを表示してみます．
SELECT * FROM mdl_user_enrolments LIMIT 5;
mysql> SELECT *
-> FROM mdl_user_enrolments LIMIT 5; ⏎
+----+--------+---------+--------+------------+---------+------------+-------------+--------------+
| id | status | enrolid | userid | timestart | timeend | modifierid | timecreated | timemodified |
+----+--------+---------+--------+------------+---------+------------+-------------+--------------+
| 1 | 0 | 1 | 3 | 1767193200 | 0 | 2 | 1767232052 | 1767232052 |
| 2 | 0 | 4 | 3 | 1767193200 | 0 | 2 | 1767232052 | 1767232052 |
| 3 | 0 | 7 | 4 | 1767193200 | 0 | 2 | 1767232052 | 1767232052 |
| 4 | 0 | 10 | 4 | 1767193200 | 0 | 2 | 1767232052 | 1767232052 |
| 5 | 0 | 13 | 5 | 1767193200 | 0 | 2 | 1767232052 | 1767232052 |
+----+--------+---------+--------+------------+---------+------------+-------------+--------------+
5 rows in set (0.002 sec)
mysql>
フィールドを限定してすべてのレコードを表示してみます．
SELECT id, status, enrolid, userid, timestart, timeend, modifierid FROM mdl_user_enrolments;
これは，教員の割当総数と学生の履修登録の総数の合計に一致することに注意しよう．つまり，だれがどのコースに割り当てられているかが記録されている．具体的には，憲法(1+25)，民法(1+2)，経営学総論(1+26)，簿記論(1+20)，基礎英語(1+50)，実用英語(1+50)，ICT実習(1+50)の合計229と一致しています．ただし，これだけでは，コースに割り当てられていることが分かるだけで，ロール（役割：つまり，教員として割り当てられているのか，学生として割り当てられているのか）は分かりません．
mysql> SELECT id, status, enrolid, userid, timestart, timeend, modifierid
-> FROM mdl_user_enrolments; ⏎
+-----+--------+---------+--------+------------+---------+------------+
| id | status | enrolid | userid | timestart | timeend | modifierid |
+-----+--------+---------+--------+------------+---------+------------+
| 1 | 0 | 1 | 3 | 1767193200 | 0 | 2 |
| 2 | 0 | 4 | 3 | 1767193200 | 0 | 2 |
| 3 | 0 | 7 | 4 | 1767193200 | 0 | 2 |
| 4 | 0 | 10 | 4 | 1767193200 | 0 | 2 |
| 5 | 0 | 13 | 5 | 1767193200 | 0 | 2 |
| 6 | 0 | 16 | 5 | 1767193200 | 0 | 2 |
| 7 | 0 | 19 | 4 | 1767193200 | 0 | 2 |
| 8 | 0 | 1 | 6 | 1767193200 | 0 | 2 |
| 9 | 0 | 1 | 7 | 1767193200 | 0 | 2 |
| 10 | 0 | 1 | 8 | 1767193200 | 0 | 2 |
| 11 | 0 | 1 | 9 | 1767193200 | 0 | 2 |
| 12 | 0 | 1 | 10 | 1767193200 | 0 | 2 |
| 13 | 0 | 1 | 11 | 1767193200 | 0 | 2 |
| 14 | 0 | 1 | 12 | 1767193200 | 0 | 2 |
| 15 | 0 | 1 | 13 | 1767193200 | 0 | 2 |
| 16 | 0 | 1 | 14 | 1767193200 | 0 | 2 |
| 17 | 0 | 1 | 15 | 1767193200 | 0 | 2 |
| 18 | 0 | 1 | 16 | 1767193200 | 0 | 2 |
| 19 | 0 | 1 | 17 | 1767193200 | 0 | 2 |
| 20 | 0 | 1 | 18 | 1767193200 | 0 | 2 |
| 21 | 0 | 1 | 19 | 1767193200 | 0 | 2 |
| 22 | 0 | 1 | 20 | 1767193200 | 0 | 2 |
| 23 | 0 | 1 | 21 | 1767193200 | 0 | 2 |
| 24 | 0 | 1 | 22 | 1767193200 | 0 | 2 |
| 25 | 0 | 1 | 23 | 1767193200 | 0 | 2 |
| 26 | 0 | 1 | 24 | 1767193200 | 0 | 2 |
| 27 | 0 | 1 | 25 | 1767193200 | 0 | 2 |
| 28 | 0 | 1 | 26 | 1767193200 | 0 | 2 |
| 29 | 0 | 1 | 27 | 1767193200 | 0 | 2 |
| 30 | 0 | 1 | 28 | 1767193200 | 0 | 2 |
| 31 | 0 | 1 | 29 | 1767193200 | 0 | 2 |
| 32 | 0 | 1 | 30 | 1767193200 | 0 | 2 |
| 33 | 0 | 4 | 6 | 1767193200 | 0 | 2 |
| 34 | 0 | 4 | 7 | 1767193200 | 0 | 2 |
| 53 | 0 | 7 | 31 | 1767193200 | 0 | 2 |
| 54 | 0 | 7 | 32 | 1767193200 | 0 | 2 |
| 55 | 0 | 7 | 33 | 1767193200 | 0 | 2 |
| 56 | 0 | 7 | 34 | 1767193200 | 0 | 2 |
| 57 | 0 | 7 | 35 | 1767193200 | 0 | 2 |
| 58 | 0 | 7 | 36 | 1767193200 | 0 | 2 |
| 59 | 0 | 7 | 37 | 1767193200 | 0 | 2 |
| 60 | 0 | 7 | 38 | 1767193200 | 0 | 2 |
| 61 | 0 | 7 | 39 | 1767193200 | 0 | 2 |
| 62 | 0 | 7 | 40 | 1767193200 | 0 | 2 |
| 63 | 0 | 7 | 41 | 1767193200 | 0 | 2 |
| 64 | 0 | 7 | 42 | 1767193200 | 0 | 2 |
| 65 | 0 | 7 | 43 | 1767193200 | 0 | 2 |
| 66 | 0 | 7 | 44 | 1767193200 | 0 | 2 |
| 67 | 0 | 7 | 45 | 1767193200 | 0 | 2 |
| 68 | 0 | 7 | 46 | 1767193200 | 0 | 2 |
| 69 | 0 | 7 | 47 | 1767193200 | 0 | 2 |
| 70 | 0 | 7 | 48 | 1767193200 | 0 | 2 |
| 71 | 0 | 7 | 49 | 1767193200 | 0 | 2 |
| 72 | 0 | 7 | 50 | 1767193200 | 0 | 2 |
| 73 | 0 | 7 | 51 | 1767193200 | 0 | 2 |
| 74 | 0 | 7 | 52 | 1767193200 | 0 | 2 |
| 75 | 0 | 7 | 53 | 1767193200 | 0 | 2 |
| 76 | 0 | 7 | 54 | 1767193200 | 0 | 2 |
| 77 | 0 | 7 | 55 | 1767193200 | 0 | 2 |
| 78 | 0 | 10 | 31 | 1767193200 | 0 | 2 |
| 79 | 0 | 10 | 32 | 1767193200 | 0 | 2 |
| 80 | 0 | 10 | 33 | 1767193200 | 0 | 2 |
| 81 | 0 | 10 | 34 | 1767193200 | 0 | 2 |
| 82 | 0 | 10 | 35 | 1767193200 | 0 | 2 |
| 83 | 0 | 10 | 36 | 1767193200 | 0 | 2 |
| 84 | 0 | 10 | 37 | 1767193200 | 0 | 2 |
| 85 | 0 | 10 | 38 | 1767193200 | 0 | 2 |
| 86 | 0 | 10 | 39 | 1767193200 | 0 | 2 |
| 87 | 0 | 10 | 40 | 1767193200 | 0 | 2 |
| 88 | 0 | 10 | 41 | 1767193200 | 0 | 2 |
| 89 | 0 | 10 | 42 | 1767193200 | 0 | 2 |
| 90 | 0 | 10 | 43 | 1767193200 | 0 | 2 |
| 91 | 0 | 10 | 44 | 1767193200 | 0 | 2 |
| 92 | 0 | 10 | 45 | 1767193200 | 0 | 2 |
| 93 | 0 | 10 | 46 | 1767193200 | 0 | 2 |
| 94 | 0 | 10 | 47 | 1767193200 | 0 | 2 |
| 95 | 0 | 10 | 48 | 1767193200 | 0 | 2 |
| 96 | 0 | 10 | 49 | 1767193200 | 0 | 2 |
| 97 | 0 | 10 | 50 | 1767193200 | 0 | 2 |
| 98 | 0 | 22 | 6 | 0 | 0 | 2 |
| 99 | 0 | 25 | 6 | 0 | 0 | 2 |
| 100 | 0 | 27 | 6 | 0 | 0 | 2 |
| 101 | 0 | 22 | 7 | 0 | 0 | 2 |
| 102 | 0 | 25 | 7 | 0 | 0 | 2 |
| 103 | 0 | 27 | 7 | 0 | 0 | 2 |
| 104 | 0 | 22 | 8 | 0 | 0 | 2 |
| 105 | 0 | 25 | 8 | 0 | 0 | 2 |
| 106 | 0 | 27 | 8 | 0 | 0 | 2 |
| 107 | 0 | 22 | 9 | 0 | 0 | 2 |
| 108 | 0 | 25 | 9 | 0 | 0 | 2 |
| 109 | 0 | 27 | 9 | 0 | 0 | 2 |
| 110 | 0 | 22 | 10 | 0 | 0 | 2 |
| 111 | 0 | 25 | 10 | 0 | 0 | 2 |
| 112 | 0 | 27 | 10 | 0 | 0 | 2 |
| 113 | 0 | 22 | 11 | 0 | 0 | 2 |
| 114 | 0 | 25 | 11 | 0 | 0 | 2 |
| 115 | 0 | 27 | 11 | 0 | 0 | 2 |
| 116 | 0 | 22 | 12 | 0 | 0 | 2 |
| 117 | 0 | 25 | 12 | 0 | 0 | 2 |
| 118 | 0 | 27 | 12 | 0 | 0 | 2 |
| 119 | 0 | 22 | 13 | 0 | 0 | 2 |
| 120 | 0 | 25 | 13 | 0 | 0 | 2 |
| 121 | 0 | 27 | 13 | 0 | 0 | 2 |
| 122 | 0 | 22 | 14 | 0 | 0 | 2 |
| 123 | 0 | 25 | 14 | 0 | 0 | 2 |
| 124 | 0 | 27 | 14 | 0 | 0 | 2 |
| 125 | 0 | 22 | 15 | 0 | 0 | 2 |
| 126 | 0 | 25 | 15 | 0 | 0 | 2 |
| 127 | 0 | 27 | 15 | 0 | 0 | 2 |
| 128 | 0 | 22 | 16 | 0 | 0 | 2 |
| 129 | 0 | 25 | 16 | 0 | 0 | 2 |
| 130 | 0 | 27 | 16 | 0 | 0 | 2 |
| 131 | 0 | 22 | 17 | 0 | 0 | 2 |
| 132 | 0 | 25 | 17 | 0 | 0 | 2 |
| 133 | 0 | 27 | 17 | 0 | 0 | 2 |
| 134 | 0 | 22 | 18 | 0 | 0 | 2 |
| 135 | 0 | 25 | 18 | 0 | 0 | 2 |
| 136 | 0 | 27 | 18 | 0 | 0 | 2 |
| 137 | 0 | 22 | 19 | 0 | 0 | 2 |
| 138 | 0 | 25 | 19 | 0 | 0 | 2 |
| 139 | 0 | 27 | 19 | 0 | 0 | 2 |
| 140 | 0 | 22 | 20 | 0 | 0 | 2 |
| 141 | 0 | 25 | 20 | 0 | 0 | 2 |
| 142 | 0 | 27 | 20 | 0 | 0 | 2 |
| 143 | 0 | 22 | 21 | 0 | 0 | 2 |
| 144 | 0 | 25 | 21 | 0 | 0 | 2 |
| 145 | 0 | 27 | 21 | 0 | 0 | 2 |
| 146 | 0 | 22 | 22 | 0 | 0 | 2 |
| 147 | 0 | 25 | 22 | 0 | 0 | 2 |
| 148 | 0 | 27 | 22 | 0 | 0 | 2 |
| 149 | 0 | 22 | 23 | 0 | 0 | 2 |
| 150 | 0 | 25 | 23 | 0 | 0 | 2 |
| 151 | 0 | 27 | 23 | 0 | 0 | 2 |
| 152 | 0 | 22 | 24 | 0 | 0 | 2 |
| 153 | 0 | 25 | 24 | 0 | 0 | 2 |
| 154 | 0 | 27 | 24 | 0 | 0 | 2 |
| 155 | 0 | 22 | 25 | 0 | 0 | 2 |
| 156 | 0 | 25 | 25 | 0 | 0 | 2 |
| 157 | 0 | 27 | 25 | 0 | 0 | 2 |
| 158 | 0 | 22 | 26 | 0 | 0 | 2 |
| 159 | 0 | 25 | 26 | 0 | 0 | 2 |
| 160 | 0 | 27 | 26 | 0 | 0 | 2 |
| 161 | 0 | 22 | 27 | 0 | 0 | 2 |
| 162 | 0 | 25 | 27 | 0 | 0 | 2 |
| 163 | 0 | 27 | 27 | 0 | 0 | 2 |
| 164 | 0 | 22 | 28 | 0 | 0 | 2 |
| 165 | 0 | 25 | 28 | 0 | 0 | 2 |
| 166 | 0 | 27 | 28 | 0 | 0 | 2 |
| 167 | 0 | 22 | 29 | 0 | 0 | 2 |
| 168 | 0 | 25 | 29 | 0 | 0 | 2 |
| 169 | 0 | 27 | 29 | 0 | 0 | 2 |
| 170 | 0 | 22 | 30 | 0 | 0 | 2 |
| 171 | 0 | 25 | 30 | 0 | 0 | 2 |
| 172 | 0 | 27 | 30 | 0 | 0 | 2 |
| 173 | 0 | 26 | 31 | 0 | 0 | 2 |
| 174 | 0 | 24 | 31 | 0 | 0 | 2 |
| 175 | 0 | 28 | 31 | 0 | 0 | 2 |
| 176 | 0 | 26 | 32 | 0 | 0 | 2 |
| 177 | 0 | 23 | 32 | 0 | 0 | 2 |
| 178 | 0 | 29 | 32 | 0 | 0 | 2 |
| 179 | 0 | 26 | 33 | 0 | 0 | 2 |
| 180 | 0 | 24 | 33 | 0 | 0 | 2 |
| 181 | 0 | 28 | 33 | 0 | 0 | 2 |
| 182 | 0 | 26 | 34 | 0 | 0 | 2 |
| 183 | 0 | 23 | 34 | 0 | 0 | 2 |
| 184 | 0 | 29 | 34 | 0 | 0 | 2 |
| 185 | 0 | 26 | 35 | 0 | 0 | 2 |
| 186 | 0 | 24 | 35 | 0 | 0 | 2 |
| 187 | 0 | 28 | 35 | 0 | 0 | 2 |
| 188 | 0 | 26 | 36 | 0 | 0 | 2 |
| 189 | 0 | 23 | 36 | 0 | 0 | 2 |
| 190 | 0 | 29 | 36 | 0 | 0 | 2 |
| 191 | 0 | 26 | 37 | 0 | 0 | 2 |
| 192 | 0 | 24 | 37 | 0 | 0 | 2 |
| 193 | 0 | 28 | 37 | 0 | 0 | 2 |
| 194 | 0 | 26 | 38 | 0 | 0 | 2 |
| 195 | 0 | 23 | 38 | 0 | 0 | 2 |
| 196 | 0 | 29 | 38 | 0 | 0 | 2 |
| 197 | 0 | 26 | 39 | 0 | 0 | 2 |
| 198 | 0 | 24 | 39 | 0 | 0 | 2 |
| 199 | 0 | 28 | 39 | 0 | 0 | 2 |
| 200 | 0 | 26 | 40 | 0 | 0 | 2 |
| 201 | 0 | 23 | 40 | 0 | 0 | 2 |
| 202 | 0 | 29 | 40 | 0 | 0 | 2 |
| 203 | 0 | 26 | 41 | 0 | 0 | 2 |
| 204 | 0 | 24 | 41 | 0 | 0 | 2 |
| 205 | 0 | 28 | 41 | 0 | 0 | 2 |
| 206 | 0 | 26 | 42 | 0 | 0 | 2 |
| 207 | 0 | 23 | 42 | 0 | 0 | 2 |
| 208 | 0 | 29 | 42 | 0 | 0 | 2 |
| 209 | 0 | 26 | 43 | 0 | 0 | 2 |
| 210 | 0 | 24 | 43 | 0 | 0 | 2 |
| 211 | 0 | 28 | 43 | 0 | 0 | 2 |
| 212 | 0 | 26 | 44 | 0 | 0 | 2 |
| 213 | 0 | 23 | 44 | 0 | 0 | 2 |
| 214 | 0 | 29 | 44 | 0 | 0 | 2 |
| 215 | 0 | 26 | 45 | 0 | 0 | 2 |
| 216 | 0 | 24 | 45 | 0 | 0 | 2 |
| 217 | 0 | 28 | 45 | 0 | 0 | 2 |
| 218 | 0 | 26 | 46 | 0 | 0 | 2 |
| 219 | 0 | 23 | 46 | 0 | 0 | 2 |
| 220 | 0 | 29 | 46 | 0 | 0 | 2 |
| 221 | 0 | 26 | 47 | 0 | 0 | 2 |
| 222 | 0 | 24 | 47 | 0 | 0 | 2 |
| 223 | 0 | 28 | 47 | 0 | 0 | 2 |
| 224 | 0 | 26 | 48 | 0 | 0 | 2 |
| 225 | 0 | 23 | 48 | 0 | 0 | 2 |
| 226 | 0 | 29 | 48 | 0 | 0 | 2 |
| 227 | 0 | 26 | 49 | 0 | 0 | 2 |
| 228 | 0 | 24 | 49 | 0 | 0 | 2 |
| 229 | 0 | 28 | 49 | 0 | 0 | 2 |
| 230 | 0 | 26 | 50 | 0 | 0 | 2 |
| 231 | 0 | 23 | 50 | 0 | 0 | 2 |
| 232 | 0 | 29 | 50 | 0 | 0 | 2 |
| 233 | 0 | 26 | 51 | 0 | 0 | 2 |
| 234 | 0 | 24 | 51 | 0 | 0 | 2 |
| 235 | 0 | 28 | 51 | 0 | 0 | 2 |
| 236 | 0 | 26 | 52 | 0 | 0 | 2 |
| 237 | 0 | 23 | 52 | 0 | 0 | 2 |
| 238 | 0 | 29 | 52 | 0 | 0 | 2 |
| 239 | 0 | 26 | 53 | 0 | 0 | 2 |
| 240 | 0 | 24 | 53 | 0 | 0 | 2 |
| 241 | 0 | 28 | 53 | 0 | 0 | 2 |
| 242 | 0 | 26 | 54 | 0 | 0 | 2 |
| 243 | 0 | 23 | 54 | 0 | 0 | 2 |
| 244 | 0 | 29 | 54 | 0 | 0 | 2 |
| 245 | 0 | 26 | 55 | 0 | 0 | 2 |
| 246 | 0 | 24 | 55 | 0 | 0 | 2 |
| 247 | 0 | 28 | 55 | 0 | 0 | 2 |
+-----+--------+---------+--------+------------+---------+------------+
229 rows in set (0.001 sec)
mysql>
コースに登録されている参加者一覧
ここまででコースに登録されている参加者を一覧で取得するための準備が整いました．4個のテーブル (mdl_user, mdl_user_enrolments, mdl_enrol, mdl_course) を結合して，すべてのコース（科目）に登録されている参加者（教員および学生，その他）を表示してみます．
SELECT c.shortname, u.id AS userid, u.username, u.firstname, u.lastname, u.email, e.enrol AS enrol_method FROM mdl_user AS u INNER JOIN mdl_user_enrolments AS ue ON ue.userid = u.id INNER JOIN mdl_enrol AS e ON e.id = ue.enrolid INNER JOIN mdl_course AS c ON c.id = e.courseid ORDER BY c.shortname, u.id;
mysql> SELECT c.shortname, u.id AS userid, u.username, u.firstname, u.lastname, u.email, e.enrol AS enrol_method
-> FROM mdl_user AS u
-> INNER JOIN mdl_user_enrolments AS ue ON ue.userid = u.id
-> INNER JOIN mdl_enrol AS e ON e.id = ue.enrolid
-> INNER JOIN mdl_course AS c ON c.id = e.courseid
-> ORDER BY c.shortname, u.id; ⏎
+-----------------+--------+-----------+-----------+----------+-----------------------+--------------+
| shortname | userid | username | firstname | lastname | email | enrol_method |
+-----------------+--------+-----------+-----------+----------+-----------------------+--------------+
| ICT実習 | 4 | teacher02 | Teacher | t02 | teacher02@example.com | manual |
| ICT実習 | 6 | student01 | Student | s01 | student01@example.com | cohort |
| ICT実習 | 7 | student02 | Student | s02 | student02@example.com | cohort |
| ICT実習 | 8 | student03 | Student | s03 | student03@example.com | cohort |
| ICT実習 | 9 | student04 | Student | s04 | student04@example.com | cohort |
| ICT実習 | 10 | student05 | Student | s05 | student05@example.com | cohort |
| ICT実習 | 11 | student06 | Student | s06 | student06@example.com | cohort |
| ICT実習 | 12 | student07 | Student | s07 | student07@example.com | cohort |
| ICT実習 | 13 | student08 | Student | s08 | student08@example.com | cohort |
| ICT実習 | 14 | student09 | Student | s09 | student09@example.com | cohort |
| ICT実習 | 15 | student10 | Student | s10 | student10@example.com | cohort |
| ICT実習 | 16 | student11 | Student | s11 | student11@example.com | cohort |
| ICT実習 | 17 | student12 | Student | s12 | student12@example.com | cohort |
| ICT実習 | 18 | student13 | Student | s13 | student13@example.com | cohort |
| ICT実習 | 19 | student14 | Student | s14 | student14@example.com | cohort |
| ICT実習 | 20 | student15 | Student | s15 | student15@example.com | cohort |
| ICT実習 | 21 | student16 | Student | s16 | student16@example.com | cohort |
| ICT実習 | 22 | student17 | Student | s17 | student17@example.com | cohort |
| ICT実習 | 23 | student18 | Student | s18 | student18@example.com | cohort |
| ICT実習 | 24 | student19 | Student | s19 | student19@example.com | cohort |
| ICT実習 | 25 | student20 | Student | s20 | student20@example.com | cohort |
| ICT実習 | 26 | student21 | Student | s21 | student21@example.com | cohort |
| ICT実習 | 27 | student22 | Student | s22 | student22@example.com | cohort |
| ICT実習 | 28 | student23 | Student | s23 | student23@example.com | cohort |
| ICT実習 | 29 | student24 | Student | s24 | student24@example.com | cohort |
| ICT実習 | 30 | student25 | Student | s25 | student25@example.com | cohort |
| ICT実習 | 31 | student26 | Student | s26 | student26@example.com | cohort |
| ICT実習 | 32 | student27 | Student | s27 | student27@example.com | cohort |
| ICT実習 | 33 | student28 | Student | s28 | student28@example.com | cohort |
| ICT実習 | 34 | student29 | Student | s29 | student29@example.com | cohort |
| ICT実習 | 35 | student30 | Student | s30 | student30@example.com | cohort |
| ICT実習 | 36 | student31 | Student | s31 | student31@example.com | cohort |
| ICT実習 | 37 | student32 | Student | s32 | student32@example.com | cohort |
| ICT実習 | 38 | student33 | Student | s33 | student33@example.com | cohort |
| ICT実習 | 39 | student34 | Student | s34 | student34@example.com | cohort |
| ICT実習 | 40 | student35 | Student | s35 | student35@example.com | cohort |
| ICT実習 | 41 | student36 | Student | s36 | student36@example.com | cohort |
| ICT実習 | 42 | student37 | Student | s37 | student37@example.com | cohort |
| ICT実習 | 43 | student38 | Student | s38 | student38@example.com | cohort |
| ICT実習 | 44 | student39 | Student | s39 | student39@example.com | cohort |
| ICT実習 | 45 | student40 | Student | s40 | student40@example.com | cohort |
| ICT実習 | 46 | student41 | Student | s41 | student41@example.com | cohort |
| ICT実習 | 47 | student42 | Student | s42 | student42@example.com | cohort |
| ICT実習 | 48 | student43 | Student | s43 | student43@example.com | cohort |
| ICT実習 | 49 | student44 | Student | s44 | student44@example.com | cohort |
| ICT実習 | 50 | student45 | Student | s45 | student45@example.com | cohort |
| ICT実習 | 51 | student46 | Student | s46 | student46@example.com | cohort |
| ICT実習 | 52 | student47 | Student | s47 | student47@example.com | cohort |
| ICT実習 | 53 | student48 | Student | s48 | student48@example.com | cohort |
| ICT実習 | 54 | student49 | Student | s49 | student49@example.com | cohort |
| ICT実習 | 55 | student50 | Student | s50 | student50@example.com | cohort |
| 基礎英語 | 5 | teacher03 | Teacher | t03 | teacher03@example.com | manual |
| 基礎英語 | 6 | student01 | Student | s01 | student01@example.com | cohort |
| 基礎英語 | 7 | student02 | Student | s02 | student02@example.com | cohort |
| 基礎英語 | 8 | student03 | Student | s03 | student03@example.com | cohort |
| 基礎英語 | 9 | student04 | Student | s04 | student04@example.com | cohort |
| 基礎英語 | 10 | student05 | Student | s05 | student05@example.com | cohort |
| 基礎英語 | 11 | student06 | Student | s06 | student06@example.com | cohort |
| 基礎英語 | 12 | student07 | Student | s07 | student07@example.com | cohort |
| 基礎英語 | 13 | student08 | Student | s08 | student08@example.com | cohort |
| 基礎英語 | 14 | student09 | Student | s09 | student09@example.com | cohort |
| 基礎英語 | 15 | student10 | Student | s10 | student10@example.com | cohort |
| 基礎英語 | 16 | student11 | Student | s11 | student11@example.com | cohort |
| 基礎英語 | 17 | student12 | Student | s12 | student12@example.com | cohort |
| 基礎英語 | 18 | student13 | Student | s13 | student13@example.com | cohort |
| 基礎英語 | 19 | student14 | Student | s14 | student14@example.com | cohort |
| 基礎英語 | 20 | student15 | Student | s15 | student15@example.com | cohort |
| 基礎英語 | 21 | student16 | Student | s16 | student16@example.com | cohort |
| 基礎英語 | 22 | student17 | Student | s17 | student17@example.com | cohort |
| 基礎英語 | 23 | student18 | Student | s18 | student18@example.com | cohort |
| 基礎英語 | 24 | student19 | Student | s19 | student19@example.com | cohort |
| 基礎英語 | 25 | student20 | Student | s20 | student20@example.com | cohort |
| 基礎英語 | 26 | student21 | Student | s21 | student21@example.com | cohort |
| 基礎英語 | 27 | student22 | Student | s22 | student22@example.com | cohort |
| 基礎英語 | 28 | student23 | Student | s23 | student23@example.com | cohort |
| 基礎英語 | 29 | student24 | Student | s24 | student24@example.com | cohort |
| 基礎英語 | 30 | student25 | Student | s25 | student25@example.com | cohort |
| 基礎英語 | 31 | student26 | Student | s26 | student26@example.com | cohort |
| 基礎英語 | 32 | student27 | Student | s27 | student27@example.com | cohort |
| 基礎英語 | 33 | student28 | Student | s28 | student28@example.com | cohort |
| 基礎英語 | 34 | student29 | Student | s29 | student29@example.com | cohort |
| 基礎英語 | 35 | student30 | Student | s30 | student30@example.com | cohort |
| 基礎英語 | 36 | student31 | Student | s31 | student31@example.com | cohort |
| 基礎英語 | 37 | student32 | Student | s32 | student32@example.com | cohort |
| 基礎英語 | 38 | student33 | Student | s33 | student33@example.com | cohort |
| 基礎英語 | 39 | student34 | Student | s34 | student34@example.com | cohort |
| 基礎英語 | 40 | student35 | Student | s35 | student35@example.com | cohort |
| 基礎英語 | 41 | student36 | Student | s36 | student36@example.com | cohort |
| 基礎英語 | 42 | student37 | Student | s37 | student37@example.com | cohort |
| 基礎英語 | 43 | student38 | Student | s38 | student38@example.com | cohort |
| 基礎英語 | 44 | student39 | Student | s39 | student39@example.com | cohort |
| 基礎英語 | 45 | student40 | Student | s40 | student40@example.com | cohort |
| 基礎英語 | 46 | student41 | Student | s41 | student41@example.com | cohort |
| 基礎英語 | 47 | student42 | Student | s42 | student42@example.com | cohort |
| 基礎英語 | 48 | student43 | Student | s43 | student43@example.com | cohort |
| 基礎英語 | 49 | student44 | Student | s44 | student44@example.com | cohort |
| 基礎英語 | 50 | student45 | Student | s45 | student45@example.com | cohort |
| 基礎英語 | 51 | student46 | Student | s46 | student46@example.com | cohort |
| 基礎英語 | 52 | student47 | Student | s47 | student47@example.com | cohort |
| 基礎英語 | 53 | student48 | Student | s48 | student48@example.com | cohort |
| 基礎英語 | 54 | student49 | Student | s49 | student49@example.com | cohort |
| 基礎英語 | 55 | student50 | Student | s50 | student50@example.com | cohort |
| 実用英語 | 5 | teacher03 | Teacher | t03 | teacher03@example.com | manual |
| 実用英語 | 6 | student01 | Student | s01 | student01@example.com | cohort |
| 実用英語 | 7 | student02 | Student | s02 | student02@example.com | cohort |
| 実用英語 | 8 | student03 | Student | s03 | student03@example.com | cohort |
| 実用英語 | 9 | student04 | Student | s04 | student04@example.com | cohort |
| 実用英語 | 10 | student05 | Student | s05 | student05@example.com | cohort |
| 実用英語 | 11 | student06 | Student | s06 | student06@example.com | cohort |
| 実用英語 | 12 | student07 | Student | s07 | student07@example.com | cohort |
| 実用英語 | 13 | student08 | Student | s08 | student08@example.com | cohort |
| 実用英語 | 14 | student09 | Student | s09 | student09@example.com | cohort |
| 実用英語 | 15 | student10 | Student | s10 | student10@example.com | cohort |
| 実用英語 | 16 | student11 | Student | s11 | student11@example.com | cohort |
| 実用英語 | 17 | student12 | Student | s12 | student12@example.com | cohort |
| 実用英語 | 18 | student13 | Student | s13 | student13@example.com | cohort |
| 実用英語 | 19 | student14 | Student | s14 | student14@example.com | cohort |
| 実用英語 | 20 | student15 | Student | s15 | student15@example.com | cohort |
| 実用英語 | 21 | student16 | Student | s16 | student16@example.com | cohort |
| 実用英語 | 22 | student17 | Student | s17 | student17@example.com | cohort |
| 実用英語 | 23 | student18 | Student | s18 | student18@example.com | cohort |
| 実用英語 | 24 | student19 | Student | s19 | student19@example.com | cohort |
| 実用英語 | 25 | student20 | Student | s20 | student20@example.com | cohort |
| 実用英語 | 26 | student21 | Student | s21 | student21@example.com | cohort |
| 実用英語 | 27 | student22 | Student | s22 | student22@example.com | cohort |
| 実用英語 | 28 | student23 | Student | s23 | student23@example.com | cohort |
| 実用英語 | 29 | student24 | Student | s24 | student24@example.com | cohort |
| 実用英語 | 30 | student25 | Student | s25 | student25@example.com | cohort |
| 実用英語 | 31 | student26 | Student | s26 | student26@example.com | cohort |
| 実用英語 | 32 | student27 | Student | s27 | student27@example.com | cohort |
| 実用英語 | 33 | student28 | Student | s28 | student28@example.com | cohort |
| 実用英語 | 34 | student29 | Student | s29 | student29@example.com | cohort |
| 実用英語 | 35 | student30 | Student | s30 | student30@example.com | cohort |
| 実用英語 | 36 | student31 | Student | s31 | student31@example.com | cohort |
| 実用英語 | 37 | student32 | Student | s32 | student32@example.com | cohort |
| 実用英語 | 38 | student33 | Student | s33 | student33@example.com | cohort |
| 実用英語 | 39 | student34 | Student | s34 | student34@example.com | cohort |
| 実用英語 | 40 | student35 | Student | s35 | student35@example.com | cohort |
| 実用英語 | 41 | student36 | Student | s36 | student36@example.com | cohort |
| 実用英語 | 42 | student37 | Student | s37 | student37@example.com | cohort |
| 実用英語 | 43 | student38 | Student | s38 | student38@example.com | cohort |
| 実用英語 | 44 | student39 | Student | s39 | student39@example.com | cohort |
| 実用英語 | 45 | student40 | Student | s40 | student40@example.com | cohort |
| 実用英語 | 46 | student41 | Student | s41 | student41@example.com | cohort |
| 実用英語 | 47 | student42 | Student | s42 | student42@example.com | cohort |
| 実用英語 | 48 | student43 | Student | s43 | student43@example.com | cohort |
| 実用英語 | 49 | student44 | Student | s44 | student44@example.com | cohort |
| 実用英語 | 50 | student45 | Student | s45 | student45@example.com | cohort |
| 実用英語 | 51 | student46 | Student | s46 | student46@example.com | cohort |
| 実用英語 | 52 | student47 | Student | s47 | student47@example.com | cohort |
| 実用英語 | 53 | student48 | Student | s48 | student48@example.com | cohort |
| 実用英語 | 54 | student49 | Student | s49 | student49@example.com | cohort |
| 実用英語 | 55 | student50 | Student | s50 | student50@example.com | cohort |
| 憲法 | 3 | teacher01 | Teacher | t01 | teacher01@example.com | manual |
| 憲法 | 6 | student01 | Student | s01 | student01@example.com | manual |
| 憲法 | 7 | student02 | Student | s02 | student02@example.com | manual |
| 憲法 | 8 | student03 | Student | s03 | student03@example.com | manual |
| 憲法 | 9 | student04 | Student | s04 | student04@example.com | manual |
| 憲法 | 10 | student05 | Student | s05 | student05@example.com | manual |
| 憲法 | 11 | student06 | Student | s06 | student06@example.com | manual |
| 憲法 | 12 | student07 | Student | s07 | student07@example.com | manual |
| 憲法 | 13 | student08 | Student | s08 | student08@example.com | manual |
| 憲法 | 14 | student09 | Student | s09 | student09@example.com | manual |
| 憲法 | 15 | student10 | Student | s10 | student10@example.com | manual |
| 憲法 | 16 | student11 | Student | s11 | student11@example.com | manual |
| 憲法 | 17 | student12 | Student | s12 | student12@example.com | manual |
| 憲法 | 18 | student13 | Student | s13 | student13@example.com | manual |
| 憲法 | 19 | student14 | Student | s14 | student14@example.com | manual |
| 憲法 | 20 | student15 | Student | s15 | student15@example.com | manual |
| 憲法 | 21 | student16 | Student | s16 | student16@example.com | manual |
| 憲法 | 22 | student17 | Student | s17 | student17@example.com | manual |
| 憲法 | 23 | student18 | Student | s18 | student18@example.com | manual |
| 憲法 | 24 | student19 | Student | s19 | student19@example.com | manual |
| 憲法 | 25 | student20 | Student | s20 | student20@example.com | manual |
| 憲法 | 26 | student21 | Student | s21 | student21@example.com | manual |
| 憲法 | 27 | student22 | Student | s22 | student22@example.com | manual |
| 憲法 | 28 | student23 | Student | s23 | student23@example.com | manual |
| 憲法 | 29 | student24 | Student | s24 | student24@example.com | manual |
| 憲法 | 30 | student25 | Student | s25 | student25@example.com | manual |
| 民法 | 3 | teacher01 | Teacher | t01 | teacher01@example.com | manual |
| 民法 | 6 | student01 | Student | s01 | student01@example.com | manual |
| 民法 | 7 | student02 | Student | s02 | student02@example.com | manual |
| 簿記論 | 4 | teacher02 | Teacher | t02 | teacher02@example.com | manual |
| 簿記論 | 31 | student26 | Student | s26 | student26@example.com | manual |
| 簿記論 | 32 | student27 | Student | s27 | student27@example.com | manual |
| 簿記論 | 33 | student28 | Student | s28 | student28@example.com | manual |
| 簿記論 | 34 | student29 | Student | s29 | student29@example.com | manual |
| 簿記論 | 35 | student30 | Student | s30 | student30@example.com | manual |
| 簿記論 | 36 | student31 | Student | s31 | student31@example.com | manual |
| 簿記論 | 37 | student32 | Student | s32 | student32@example.com | manual |
| 簿記論 | 38 | student33 | Student | s33 | student33@example.com | manual |
| 簿記論 | 39 | student34 | Student | s34 | student34@example.com | manual |
| 簿記論 | 40 | student35 | Student | s35 | student35@example.com | manual |
| 簿記論 | 41 | student36 | Student | s36 | student36@example.com | manual |
| 簿記論 | 42 | student37 | Student | s37 | student37@example.com | manual |
| 簿記論 | 43 | student38 | Student | s38 | student38@example.com | manual |
| 簿記論 | 44 | student39 | Student | s39 | student39@example.com | manual |
| 簿記論 | 45 | student40 | Student | s40 | student40@example.com | manual |
| 簿記論 | 46 | student41 | Student | s41 | student41@example.com | manual |
| 簿記論 | 47 | student42 | Student | s42 | student42@example.com | manual |
| 簿記論 | 48 | student43 | Student | s43 | student43@example.com | manual |
| 簿記論 | 49 | student44 | Student | s44 | student44@example.com | manual |
| 簿記論 | 50 | student45 | Student | s45 | student45@example.com | manual |
| 経営学総論 | 4 | teacher02 | Teacher | t02 | teacher02@example.com | manual |
| 経営学総論 | 31 | student26 | Student | s26 | student26@example.com | manual |
| 経営学総論 | 32 | student27 | Student | s27 | student27@example.com | manual |
| 経営学総論 | 33 | student28 | Student | s28 | student28@example.com | manual |
| 経営学総論 | 34 | student29 | Student | s29 | student29@example.com | manual |
| 経営学総論 | 35 | student30 | Student | s30 | student30@example.com | manual |
| 経営学総論 | 36 | student31 | Student | s31 | student31@example.com | manual |
| 経営学総論 | 37 | student32 | Student | s32 | student32@example.com | manual |
| 経営学総論 | 38 | student33 | Student | s33 | student33@example.com | manual |
| 経営学総論 | 39 | student34 | Student | s34 | student34@example.com | manual |
| 経営学総論 | 40 | student35 | Student | s35 | student35@example.com | manual |
| 経営学総論 | 41 | student36 | Student | s36 | student36@example.com | manual |
| 経営学総論 | 42 | student37 | Student | s37 | student37@example.com | manual |
| 経営学総論 | 43 | student38 | Student | s38 | student38@example.com | manual |
| 経営学総論 | 44 | student39 | Student | s39 | student39@example.com | manual |
| 経営学総論 | 45 | student40 | Student | s40 | student40@example.com | manual |
| 経営学総論 | 46 | student41 | Student | s41 | student41@example.com | manual |
| 経営学総論 | 47 | student42 | Student | s42 | student42@example.com | manual |
| 経営学総論 | 48 | student43 | Student | s43 | student43@example.com | manual |
| 経営学総論 | 49 | student44 | Student | s44 | student44@example.com | manual |
| 経営学総論 | 50 | student45 | Student | s45 | student45@example.com | manual |
| 経営学総論 | 51 | student46 | Student | s46 | student46@example.com | manual |
| 経営学総論 | 52 | student47 | Student | s47 | student47@example.com | manual |
| 経営学総論 | 53 | student48 | Student | s48 | student48@example.com | manual |
| 経営学総論 | 54 | student49 | Student | s49 | student49@example.com | manual |
| 経営学総論 | 55 | student50 | Student | s50 | student50@example.com | manual |
+-----------------+--------+-----------+-----------+----------+-----------------------+--------------+
229 rows in set (0.003 sec)
mysql>
「憲法」コースに登録されている参加者一覧
上のSQL に WHERE 句を追加し，ORDER を調整するだけで，「憲法」コースに登録されている参加者を一覧で取得することができます．ただし，まだユーザごとのロール（教員であるか，学生であるか，その他であるか）は分からないことに注意してください．
SELECT c.shortname, u.id AS userid, u.username, u.firstname, u.lastname, u.email, e.enrol AS enrol_method FROM mdl_user AS u INNER JOIN mdl_user_enrolments AS ue ON ue.userid = u.id INNER JOIN mdl_enrol AS e ON e.id = ue.enrolid INNER JOIN mdl_course AS c ON c.id = e.courseid WHERE c.shortname = '憲法' ORDER BY u.id;
mysql> SELECT c.shortname, u.id AS userid, u.username, u.firstname, u.lastname, u.email, e.enrol AS enrol_method
-> FROM mdl_user AS u
-> INNER JOIN mdl_user_enrolments AS ue ON ue.userid = u.id
-> INNER JOIN mdl_enrol AS e ON e.id = ue.enrolid
-> INNER JOIN mdl_course AS c ON c.id = e.courseid
-> WHERE c.shortname = '憲法'
-> ORDER BY u.id; ⏎
+-----------+--------+-----------+-----------+----------+-----------------------+--------------+
| shortname | userid | username | firstname | lastname | email | enrol_method |
+-----------+--------+-----------+-----------+----------+-----------------------+--------------+
| 憲法 | 3 | teacher01 | Teacher | t01 | teacher01@example.com | manual |
| 憲法 | 6 | student01 | Student | s01 | student01@example.com | manual |
| 憲法 | 7 | student02 | Student | s02 | student02@example.com | manual |
| 憲法 | 8 | student03 | Student | s03 | student03@example.com | manual |
| 憲法 | 9 | student04 | Student | s04 | student04@example.com | manual |
| 憲法 | 10 | student05 | Student | s05 | student05@example.com | manual |
| 憲法 | 11 | student06 | Student | s06 | student06@example.com | manual |
| 憲法 | 12 | student07 | Student | s07 | student07@example.com | manual |
| 憲法 | 13 | student08 | Student | s08 | student08@example.com | manual |
| 憲法 | 14 | student09 | Student | s09 | student09@example.com | manual |
| 憲法 | 15 | student10 | Student | s10 | student10@example.com | manual |
| 憲法 | 16 | student11 | Student | s11 | student11@example.com | manual |
| 憲法 | 17 | student12 | Student | s12 | student12@example.com | manual |
| 憲法 | 18 | student13 | Student | s13 | student13@example.com | manual |
| 憲法 | 19 | student14 | Student | s14 | student14@example.com | manual |
| 憲法 | 20 | student15 | Student | s15 | student15@example.com | manual |
| 憲法 | 21 | student16 | Student | s16 | student16@example.com | manual |
| 憲法 | 22 | student17 | Student | s17 | student17@example.com | manual |
| 憲法 | 23 | student18 | Student | s18 | student18@example.com | manual |
| 憲法 | 24 | student19 | Student | s19 | student19@example.com | manual |
| 憲法 | 25 | student20 | Student | s20 | student20@example.com | manual |
| 憲法 | 26 | student21 | Student | s21 | student21@example.com | manual |
| 憲法 | 27 | student22 | Student | s22 | student22@example.com | manual |
| 憲法 | 28 | student23 | Student | s23 | student23@example.com | manual |
| 憲法 | 29 | student24 | Student | s24 | student24@example.com | manual |
| 憲法 | 30 | student25 | Student | s25 | student25@example.com | manual |
+-----------+--------+-----------+-----------+----------+-----------------------+--------------+
26 rows in set (0.001 sec)
mysql>
コースコンテキスト (mdl_context)
コースコンテキストは直感的には分かりにくいかもしれません．まず，テーブルの定義を確認します．
DESC mdl_context;
mysql> DESC mdl_context; ⏎
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| contextlevel | bigint | NO | MUL | 0 | |
| instanceid | bigint | NO | MUL | 0 | |
| path | varchar(255) | YES | MUL | NULL | |
| depth | tinyint | NO | | 0 | |
| locked | tinyint | NO | | 0 | |
+--------------+--------------+------+-----+---------+----------------+
6 rows in set (0.009 sec)
次にテーブル作成時に利用されたコマンドを確認してみます．
SHOW CREATE TABLE mdl_context\G
mysql> SHOW CREATE TABLE mdl_context\G ⏎
*************************** 1. row ***************************
Table: mdl_context
Create Table: CREATE TABLE `mdl_context` (
`id` bigint NOT NULL AUTO_INCREMENT,
`contextlevel` bigint NOT NULL DEFAULT '0',
`instanceid` bigint NOT NULL DEFAULT '0',
`path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`depth` tinyint NOT NULL DEFAULT '0',
`locked` tinyint NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `mdl_cont_conins_uix` (`contextlevel`,`instanceid`),
KEY `mdl_cont_ins_ix` (`instanceid`),
KEY `mdl_cont_pat_ix` (`path`)
) ENGINE=InnoDB AUTO_INCREMENT=92 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPRESSED COMMENT='one of these must be set'
1 row in set (0.004 sec)
mysql>
すべてのレコードを表示してみましょう．
SELECT * FROM mdl_context;
次の contextlevel の主な値は 10=サイト，30=ユーザ，40=カテゴリ，50=コース，70=モジュール，80=ブロック です．次の結果には contextlevel=50 のレコードが7個（id=17...23）あることが分かります．これはコース（講義科目）が7個あることと一致しています．また，id=21 は contextlevel=50 の instanceid=6 であることから，「基礎英語」のコースに該当し，path が 1/16/21 となっていることから，「サイト」/「共通教育科目」カテゴリ/「基礎英語」コース という構造であることを意味しています．
mysql> SELECT * FROM mdl_context; ⏎ +----+--------------+------------+-------------+-------+--------+ | id | contextlevel | instanceid | path | depth | locked | +----+--------------+------------+-------------+-------+--------+ | 1 | 10 | 0 | /1 | 1 | 0 | | 2 | 50 | 1 | /1/2 | 2 | 0 | | 4 | 30 | 1 | /1/4 | 2 | 0 | | 5 | 30 | 2 | /1/5 | 2 | 0 | | 6 | 80 | 1 | /1/6 | 2 | 0 | | 7 | 80 | 2 | /1/7 | 2 | 0 | | 8 | 80 | 3 | /1/8 | 2 | 0 | | 9 | 80 | 4 | /1/9 | 2 | 0 | | 10 | 80 | 5 | /1/10 | 2 | 0 | | 11 | 80 | 6 | /1/5/11 | 3 | 0 | | 12 | 80 | 7 | /1/5/12 | 3 | 0 | | 13 | 80 | 8 | /1/5/13 | 3 | 0 | | 14 | 40 | 2 | /1/14 | 2 | 0 | | 15 | 40 | 3 | /1/15 | 2 | 0 | | 16 | 40 | 4 | /1/16 | 2 | 0 | | 17 | 50 | 2 | /1/14/17 | 3 | 0 | | 18 | 50 | 3 | /1/14/18 | 3 | 0 | | 19 | 50 | 4 | /1/15/19 | 3 | 0 | | 20 | 50 | 5 | /1/15/20 | 3 | 0 | | 21 | 50 | 6 | /1/16/21 | 3 | 0 | | 22 | 50 | 7 | /1/16/22 | 3 | 0 | | 23 | 50 | 8 | /1/16/23 | 3 | 0 | | 24 | 30 | 3 | /1/24 | 2 | 0 | | 25 | 30 | 4 | /1/25 | 2 | 0 | | 26 | 30 | 5 | /1/26 | 2 | 0 | | 27 | 30 | 6 | /1/27 | 2 | 0 | | 28 | 30 | 7 | /1/28 | 2 | 0 | | 29 | 30 | 8 | /1/29 | 2 | 0 | | 30 | 30 | 9 | /1/30 | 2 | 0 | | 31 | 30 | 10 | /1/31 | 2 | 0 | | 32 | 30 | 11 | /1/32 | 2 | 0 | | 33 | 30 | 12 | /1/33 | 2 | 0 | | 34 | 30 | 13 | /1/34 | 2 | 0 | | 35 | 30 | 14 | /1/35 | 2 | 0 | | 36 | 30 | 15 | /1/36 | 2 | 0 | | 37 | 30 | 16 | /1/37 | 2 | 0 | | 38 | 30 | 17 | /1/38 | 2 | 0 | | 39 | 30 | 18 | /1/39 | 2 | 0 | | 40 | 30 | 19 | /1/40 | 2 | 0 | | 41 | 30 | 20 | /1/41 | 2 | 0 | | 42 | 30 | 21 | /1/42 | 2 | 0 | | 43 | 30 | 22 | /1/43 | 2 | 0 | | 44 | 30 | 23 | /1/44 | 2 | 0 | | 45 | 30 | 24 | /1/45 | 2 | 0 | | 46 | 30 | 25 | /1/46 | 2 | 0 | | 47 | 30 | 26 | /1/47 | 2 | 0 | | 48 | 30 | 27 | /1/48 | 2 | 0 | | 49 | 30 | 28 | /1/49 | 2 | 0 | | 50 | 30 | 29 | /1/50 | 2 | 0 | | 51 | 30 | 30 | /1/51 | 2 | 0 | | 52 | 30 | 31 | /1/52 | 2 | 0 | | 53 | 30 | 32 | /1/53 | 2 | 0 | | 54 | 30 | 33 | /1/54 | 2 | 0 | | 55 | 30 | 34 | /1/55 | 2 | 0 | | 56 | 30 | 35 | /1/56 | 2 | 0 | | 57 | 30 | 36 | /1/57 | 2 | 0 | | 58 | 30 | 37 | /1/58 | 2 | 0 | | 59 | 30 | 38 | /1/59 | 2 | 0 | | 60 | 30 | 39 | /1/60 | 2 | 0 | | 61 | 30 | 40 | /1/61 | 2 | 0 | | 62 | 30 | 41 | /1/62 | 2 | 0 | | 63 | 30 | 42 | /1/63 | 2 | 0 | | 64 | 30 | 43 | /1/64 | 2 | 0 | | 65 | 30 | 44 | /1/65 | 2 | 0 | | 66 | 30 | 45 | /1/66 | 2 | 0 | | 67 | 30 | 46 | /1/67 | 2 | 0 | | 68 | 30 | 47 | /1/68 | 2 | 0 | | 69 | 30 | 48 | /1/69 | 2 | 0 | | 70 | 30 | 49 | /1/70 | 2 | 0 | | 71 | 30 | 50 | /1/71 | 2 | 0 | | 72 | 30 | 51 | /1/72 | 2 | 0 | | 73 | 30 | 52 | /1/73 | 2 | 0 | | 74 | 30 | 53 | /1/74 | 2 | 0 | | 75 | 30 | 54 | /1/75 | 2 | 0 | | 76 | 30 | 55 | /1/76 | 2 | 0 | | 77 | 70 | 5 | /1/16/21/77 | 4 | 0 | | 78 | 70 | 6 | /1/16/22/78 | 4 | 0 | | 79 | 70 | 7 | /1/16/23/79 | 4 | 0 | | 80 | 80 | 9 | /1/24/80 | 3 | 0 | | 81 | 80 | 10 | /1/24/81 | 3 | 0 | | 82 | 80 | 11 | /1/24/82 | 3 | 0 | | 83 | 70 | 2 | /1/14/18/83 | 4 | 0 | | 84 | 80 | 12 | /1/27/84 | 3 | 0 | | 85 | 80 | 13 | /1/27/85 | 3 | 0 | | 86 | 80 | 14 | /1/27/86 | 3 | 0 | | 87 | 70 | 1 | /1/14/17/87 | 4 | 0 | | 88 | 70 | 8 | /1/14/17/88 | 4 | 0 | | 89 | 70 | 9 | /1/14/17/89 | 4 | 0 | | 90 | 70 | 10 | /1/14/17/90 | 4 | 0 | | 91 | 70 | 11 | /1/14/17/91 | 4 | 0 | +----+--------------+------------+-------------+-------+--------+ 90 rows in set (0.002 sec) mysql>
役割（ロール: mdl_role）
役割 (mdl_role) は Moodle 上に定義されたロールの一覧を保存するテーブルです．まずはテーブルの定義を確認します．
DESC mdl_role;
mysql> DESC mdl_role; ⏎
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | | |
| shortname | varchar(100) | NO | UNI | | |
| description | longtext | NO | | NULL | |
| sortorder | bigint | NO | UNI | 0 | |
| archetype | varchar(30) | NO | | | |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.003 sec)
mysql>
次にテーブル作成時に利用されたコマンドを確認してみます．
SHOW CREATE TABLE mdl_role\G
mysql> SHOW CREATE TABLE mdl_role\G ⏎
*************************** 1. row ***************************
Table: mdl_role
Create Table: CREATE TABLE `mdl_role` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`shortname` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`sortorder` bigint NOT NULL DEFAULT '0',
`archetype` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `mdl_role_sor_uix` (`sortorder`),
UNIQUE KEY `mdl_role_sho_uix` (`shortname`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPRESSED COMMENT='moodle roles'
1 row in set (0.002 sec)
mysql>
すべてのロールを確認してみます．教師 (editingteacher) や学生 (student) 意外にも，権限が異なるロールがあらかじめ準備されています．
SELECT * FROM mdl_role;
mysql> SELECT * FROM mdl_role; ⏎
+----+------+----------------+-------------+-----------+----------------+
| id | name | shortname | description | sortorder | archetype |
+----+------+----------------+-------------+-----------+----------------+
| 1 | | manager | | 1 | manager |
| 2 | | coursecreator | | 2 | coursecreator |
| 3 | | editingteacher | | 3 | editingteacher |
| 4 | | teacher | | 4 | teacher |
| 5 | | student | | 5 | student |
| 6 | | guest | | 6 | guest |
| 7 | | user | | 7 | user |
| 8 | | frontpage | | 8 | frontpage |
+----+------+----------------+-------------+-----------+----------------+
8 rows in set (0.002 sec)
mysql>
ロールの割り当て (mdl_role_assignments)
ロールの割り当て (mdl_role_assignments) はどのユーザをどのコンテキストにどのようなロールを割り当てているか，それを定義するためのテーブルです．まず，テーブルの定義を確認します．
DESC mdl_role_assignments;
mysql> DESC mdl_role_assignments; ⏎
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| roleid | bigint | NO | MUL | 0 | |
| contextid | bigint | NO | MUL | 0 | |
| userid | bigint | NO | MUL | 0 | |
| timemodified | bigint | NO | | 0 | |
| modifierid | bigint | NO | | 0 | |
| component | varchar(100) | NO | MUL | | |
| itemid | bigint | NO | | 0 | |
| sortorder | bigint | NO | MUL | 0 | |
+--------------+--------------+------+-----+---------+----------------+
9 rows in set (0.009 sec)
mysql>
次にテーブル作成時に利用されたコマンドを確認してみます．
SHOW CREATE TABLE mdl_role_assignments\G
mysql> SHOW CREATE TABLE mdl_role_assignments\G ⏎
*************************** 1. row ***************************
Table: mdl_role_assignments
Create Table: CREATE TABLE `mdl_role_assignments` (
`id` bigint NOT NULL AUTO_INCREMENT,
`roleid` bigint NOT NULL DEFAULT '0',
`contextid` bigint NOT NULL DEFAULT '0',
`userid` bigint NOT NULL DEFAULT '0',
`timemodified` bigint NOT NULL DEFAULT '0',
`modifierid` bigint NOT NULL DEFAULT '0',
`component` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`itemid` bigint NOT NULL DEFAULT '0',
`sortorder` bigint NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `mdl_roleassi_sor_ix` (`sortorder`),
KEY `mdl_roleassi_rolcon_ix` (`roleid`,`contextid`),
KEY `mdl_roleassi_useconrol_ix` (`userid`,`contextid`,`roleid`),
KEY `mdl_roleassi_comiteuse_ix` (`component`,`itemid`,`userid`),
KEY `mdl_roleassi_rol_ix` (`roleid`),
KEY `mdl_roleassi_con_ix` (`contextid`),
KEY `mdl_roleassi_use_ix` (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=248 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPRESSED COMMENT='assigning roles in different context'
1 row in set (0.002 sec)
mysql>
フィールドを限定してすべてのレコードを表示してみます．
SELECT id, roleid, contextid, userid, component, itemid FROM mdl_role_assignments;
この結果から，次のことが分かります．すなわち，userid=3 の teacher01 は contextid=17,18 の「憲法」と「民法」について，roleid=3 の教師 (editingteacher) のロールが与えられています．一方で，userid=6 の student01 は contextid=17,18,21,22,23 の「憲法」「民法」「基礎英語」「実用英語」「ICT実習」というコースについて roleid=5 の学生 (student) のロールが与えられています．
mysql> SELECT id, roleid, contextid, userid, component, itemid -> FROM mdl_role_assignments; ⏎ +-----+--------+-----------+--------+--------------+--------+ | id | roleid | contextid | userid | component | itemid | +-----+--------+-----------+--------+--------------+--------+ | 1 | 3 | 17 | 3 | | 0 | | 2 | 3 | 18 | 3 | | 0 | | 3 | 3 | 19 | 4 | | 0 | | 4 | 3 | 20 | 4 | | 0 | | 5 | 3 | 21 | 5 | | 0 | | 6 | 3 | 22 | 5 | | 0 | | 7 | 3 | 23 | 4 | | 0 | | 8 | 5 | 17 | 6 | | 0 | | 9 | 5 | 17 | 7 | | 0 | | 10 | 5 | 17 | 8 | | 0 | | 11 | 5 | 17 | 9 | | 0 | | 12 | 5 | 17 | 10 | | 0 | | 13 | 5 | 17 | 11 | | 0 | | 14 | 5 | 17 | 12 | | 0 | | 15 | 5 | 17 | 13 | | 0 | | 16 | 5 | 17 | 14 | | 0 | | 17 | 5 | 17 | 15 | | 0 | | 18 | 5 | 17 | 16 | | 0 | | 19 | 5 | 17 | 17 | | 0 | | 20 | 5 | 17 | 18 | | 0 | | 21 | 5 | 17 | 19 | | 0 | | 22 | 5 | 17 | 20 | | 0 | | 23 | 5 | 17 | 21 | | 0 | | 24 | 5 | 17 | 22 | | 0 | | 25 | 5 | 17 | 23 | | 0 | | 26 | 5 | 17 | 24 | | 0 | | 27 | 5 | 17 | 25 | | 0 | | 28 | 5 | 17 | 26 | | 0 | | 29 | 5 | 17 | 27 | | 0 | | 30 | 5 | 17 | 28 | | 0 | | 31 | 5 | 17 | 29 | | 0 | | 32 | 5 | 17 | 30 | | 0 | | 33 | 5 | 18 | 6 | | 0 | | 34 | 5 | 18 | 7 | | 0 | | 53 | 5 | 19 | 31 | | 0 | | 54 | 5 | 19 | 32 | | 0 | | 55 | 5 | 19 | 33 | | 0 | | 56 | 5 | 19 | 34 | | 0 | | 57 | 5 | 19 | 35 | | 0 | | 58 | 5 | 19 | 36 | | 0 | | 59 | 5 | 19 | 37 | | 0 | | 60 | 5 | 19 | 38 | | 0 | | 61 | 5 | 19 | 39 | | 0 | | 62 | 5 | 19 | 40 | | 0 | | 63 | 5 | 19 | 41 | | 0 | | 64 | 5 | 19 | 42 | | 0 | | 65 | 5 | 19 | 43 | | 0 | | 66 | 5 | 19 | 44 | | 0 | | 67 | 5 | 19 | 45 | | 0 | | 68 | 5 | 19 | 46 | | 0 | | 69 | 5 | 19 | 47 | | 0 | | 70 | 5 | 19 | 48 | | 0 | | 71 | 5 | 19 | 49 | | 0 | | 72 | 5 | 19 | 50 | | 0 | | 73 | 5 | 19 | 51 | | 0 | | 74 | 5 | 19 | 52 | | 0 | | 75 | 5 | 19 | 53 | | 0 | | 76 | 5 | 19 | 54 | | 0 | | 77 | 5 | 19 | 55 | | 0 | | 78 | 5 | 20 | 31 | | 0 | | 79 | 5 | 20 | 32 | | 0 | | 80 | 5 | 20 | 33 | | 0 | | 81 | 5 | 20 | 34 | | 0 | | 82 | 5 | 20 | 35 | | 0 | | 83 | 5 | 20 | 36 | | 0 | | 84 | 5 | 20 | 37 | | 0 | | 85 | 5 | 20 | 38 | | 0 | | 86 | 5 | 20 | 39 | | 0 | | 87 | 5 | 20 | 40 | | 0 | | 88 | 5 | 20 | 41 | | 0 | | 89 | 5 | 20 | 42 | | 0 | | 90 | 5 | 20 | 43 | | 0 | | 91 | 5 | 20 | 44 | | 0 | | 92 | 5 | 20 | 45 | | 0 | | 93 | 5 | 20 | 46 | | 0 | | 94 | 5 | 20 | 47 | | 0 | | 95 | 5 | 20 | 48 | | 0 | | 96 | 5 | 20 | 49 | | 0 | | 97 | 5 | 20 | 50 | | 0 | | 98 | 5 | 21 | 6 | enrol_cohort | 22 | | 99 | 5 | 22 | 6 | enrol_cohort | 25 | | 100 | 5 | 23 | 6 | enrol_cohort | 27 | | 101 | 5 | 21 | 7 | enrol_cohort | 22 | | 102 | 5 | 22 | 7 | enrol_cohort | 25 | | 103 | 5 | 23 | 7 | enrol_cohort | 27 | | 104 | 5 | 21 | 8 | enrol_cohort | 22 | | 105 | 5 | 22 | 8 | enrol_cohort | 25 | | 106 | 5 | 23 | 8 | enrol_cohort | 27 | | 107 | 5 | 21 | 9 | enrol_cohort | 22 | | 108 | 5 | 22 | 9 | enrol_cohort | 25 | | 109 | 5 | 23 | 9 | enrol_cohort | 27 | | 110 | 5 | 21 | 10 | enrol_cohort | 22 | | 111 | 5 | 22 | 10 | enrol_cohort | 25 | | 112 | 5 | 23 | 10 | enrol_cohort | 27 | | 113 | 5 | 21 | 11 | enrol_cohort | 22 | | 114 | 5 | 22 | 11 | enrol_cohort | 25 | | 115 | 5 | 23 | 11 | enrol_cohort | 27 | | 116 | 5 | 21 | 12 | enrol_cohort | 22 | | 117 | 5 | 22 | 12 | enrol_cohort | 25 | | 118 | 5 | 23 | 12 | enrol_cohort | 27 | | 119 | 5 | 21 | 13 | enrol_cohort | 22 | | 120 | 5 | 22 | 13 | enrol_cohort | 25 | | 121 | 5 | 23 | 13 | enrol_cohort | 27 | | 122 | 5 | 21 | 14 | enrol_cohort | 22 | | 123 | 5 | 22 | 14 | enrol_cohort | 25 | | 124 | 5 | 23 | 14 | enrol_cohort | 27 | | 125 | 5 | 21 | 15 | enrol_cohort | 22 | | 126 | 5 | 22 | 15 | enrol_cohort | 25 | | 127 | 5 | 23 | 15 | enrol_cohort | 27 | | 128 | 5 | 21 | 16 | enrol_cohort | 22 | | 129 | 5 | 22 | 16 | enrol_cohort | 25 | | 130 | 5 | 23 | 16 | enrol_cohort | 27 | | 131 | 5 | 21 | 17 | enrol_cohort | 22 | | 132 | 5 | 22 | 17 | enrol_cohort | 25 | | 133 | 5 | 23 | 17 | enrol_cohort | 27 | | 134 | 5 | 21 | 18 | enrol_cohort | 22 | | 135 | 5 | 22 | 18 | enrol_cohort | 25 | | 136 | 5 | 23 | 18 | enrol_cohort | 27 | | 137 | 5 | 21 | 19 | enrol_cohort | 22 | | 138 | 5 | 22 | 19 | enrol_cohort | 25 | | 139 | 5 | 23 | 19 | enrol_cohort | 27 | | 140 | 5 | 21 | 20 | enrol_cohort | 22 | | 141 | 5 | 22 | 20 | enrol_cohort | 25 | | 142 | 5 | 23 | 20 | enrol_cohort | 27 | | 143 | 5 | 21 | 21 | enrol_cohort | 22 | | 144 | 5 | 22 | 21 | enrol_cohort | 25 | | 145 | 5 | 23 | 21 | enrol_cohort | 27 | | 146 | 5 | 21 | 22 | enrol_cohort | 22 | | 147 | 5 | 22 | 22 | enrol_cohort | 25 | | 148 | 5 | 23 | 22 | enrol_cohort | 27 | | 149 | 5 | 21 | 23 | enrol_cohort | 22 | | 150 | 5 | 22 | 23 | enrol_cohort | 25 | | 151 | 5 | 23 | 23 | enrol_cohort | 27 | | 152 | 5 | 21 | 24 | enrol_cohort | 22 | | 153 | 5 | 22 | 24 | enrol_cohort | 25 | | 154 | 5 | 23 | 24 | enrol_cohort | 27 | | 155 | 5 | 21 | 25 | enrol_cohort | 22 | | 156 | 5 | 22 | 25 | enrol_cohort | 25 | | 157 | 5 | 23 | 25 | enrol_cohort | 27 | | 158 | 5 | 21 | 26 | enrol_cohort | 22 | | 159 | 5 | 22 | 26 | enrol_cohort | 25 | | 160 | 5 | 23 | 26 | enrol_cohort | 27 | | 161 | 5 | 21 | 27 | enrol_cohort | 22 | | 162 | 5 | 22 | 27 | enrol_cohort | 25 | | 163 | 5 | 23 | 27 | enrol_cohort | 27 | | 164 | 5 | 21 | 28 | enrol_cohort | 22 | | 165 | 5 | 22 | 28 | enrol_cohort | 25 | | 166 | 5 | 23 | 28 | enrol_cohort | 27 | | 167 | 5 | 21 | 29 | enrol_cohort | 22 | | 168 | 5 | 22 | 29 | enrol_cohort | 25 | | 169 | 5 | 23 | 29 | enrol_cohort | 27 | | 170 | 5 | 21 | 30 | enrol_cohort | 22 | | 171 | 5 | 22 | 30 | enrol_cohort | 25 | | 172 | 5 | 23 | 30 | enrol_cohort | 27 | | 173 | 5 | 22 | 31 | enrol_cohort | 26 | | 174 | 5 | 21 | 31 | enrol_cohort | 24 | | 175 | 5 | 23 | 31 | enrol_cohort | 28 | | 176 | 5 | 22 | 32 | enrol_cohort | 26 | | 177 | 5 | 21 | 32 | enrol_cohort | 23 | | 178 | 5 | 23 | 32 | enrol_cohort | 29 | | 179 | 5 | 22 | 33 | enrol_cohort | 26 | | 180 | 5 | 21 | 33 | enrol_cohort | 24 | | 181 | 5 | 23 | 33 | enrol_cohort | 28 | | 182 | 5 | 22 | 34 | enrol_cohort | 26 | | 183 | 5 | 21 | 34 | enrol_cohort | 23 | | 184 | 5 | 23 | 34 | enrol_cohort | 29 | | 185 | 5 | 22 | 35 | enrol_cohort | 26 | | 186 | 5 | 21 | 35 | enrol_cohort | 24 | | 187 | 5 | 23 | 35 | enrol_cohort | 28 | | 188 | 5 | 22 | 36 | enrol_cohort | 26 | | 189 | 5 | 21 | 36 | enrol_cohort | 23 | | 190 | 5 | 23 | 36 | enrol_cohort | 29 | | 191 | 5 | 22 | 37 | enrol_cohort | 26 | | 192 | 5 | 21 | 37 | enrol_cohort | 24 | | 193 | 5 | 23 | 37 | enrol_cohort | 28 | | 194 | 5 | 22 | 38 | enrol_cohort | 26 | | 195 | 5 | 21 | 38 | enrol_cohort | 23 | | 196 | 5 | 23 | 38 | enrol_cohort | 29 | | 197 | 5 | 22 | 39 | enrol_cohort | 26 | | 198 | 5 | 21 | 39 | enrol_cohort | 24 | | 199 | 5 | 23 | 39 | enrol_cohort | 28 | | 200 | 5 | 22 | 40 | enrol_cohort | 26 | | 201 | 5 | 21 | 40 | enrol_cohort | 23 | | 202 | 5 | 23 | 40 | enrol_cohort | 29 | | 203 | 5 | 22 | 41 | enrol_cohort | 26 | | 204 | 5 | 21 | 41 | enrol_cohort | 24 | | 205 | 5 | 23 | 41 | enrol_cohort | 28 | | 206 | 5 | 22 | 42 | enrol_cohort | 26 | | 207 | 5 | 21 | 42 | enrol_cohort | 23 | | 208 | 5 | 23 | 42 | enrol_cohort | 29 | | 209 | 5 | 22 | 43 | enrol_cohort | 26 | | 210 | 5 | 21 | 43 | enrol_cohort | 24 | | 211 | 5 | 23 | 43 | enrol_cohort | 28 | | 212 | 5 | 22 | 44 | enrol_cohort | 26 | | 213 | 5 | 21 | 44 | enrol_cohort | 23 | | 214 | 5 | 23 | 44 | enrol_cohort | 29 | | 215 | 5 | 22 | 45 | enrol_cohort | 26 | | 216 | 5 | 21 | 45 | enrol_cohort | 24 | | 217 | 5 | 23 | 45 | enrol_cohort | 28 | | 218 | 5 | 22 | 46 | enrol_cohort | 26 | | 219 | 5 | 21 | 46 | enrol_cohort | 23 | | 220 | 5 | 23 | 46 | enrol_cohort | 29 | | 221 | 5 | 22 | 47 | enrol_cohort | 26 | | 222 | 5 | 21 | 47 | enrol_cohort | 24 | | 223 | 5 | 23 | 47 | enrol_cohort | 28 | | 224 | 5 | 22 | 48 | enrol_cohort | 26 | | 225 | 5 | 21 | 48 | enrol_cohort | 23 | | 226 | 5 | 23 | 48 | enrol_cohort | 29 | | 227 | 5 | 22 | 49 | enrol_cohort | 26 | | 228 | 5 | 21 | 49 | enrol_cohort | 24 | | 229 | 5 | 23 | 49 | enrol_cohort | 28 | | 230 | 5 | 22 | 50 | enrol_cohort | 26 | | 231 | 5 | 21 | 50 | enrol_cohort | 23 | | 232 | 5 | 23 | 50 | enrol_cohort | 29 | | 233 | 5 | 22 | 51 | enrol_cohort | 26 | | 234 | 5 | 21 | 51 | enrol_cohort | 24 | | 235 | 5 | 23 | 51 | enrol_cohort | 28 | | 236 | 5 | 22 | 52 | enrol_cohort | 26 | | 237 | 5 | 21 | 52 | enrol_cohort | 23 | | 238 | 5 | 23 | 52 | enrol_cohort | 29 | | 239 | 5 | 22 | 53 | enrol_cohort | 26 | | 240 | 5 | 21 | 53 | enrol_cohort | 24 | | 241 | 5 | 23 | 53 | enrol_cohort | 28 | | 242 | 5 | 22 | 54 | enrol_cohort | 26 | | 243 | 5 | 21 | 54 | enrol_cohort | 23 | | 244 | 5 | 23 | 54 | enrol_cohort | 29 | | 245 | 5 | 22 | 55 | enrol_cohort | 26 | | 246 | 5 | 21 | 55 | enrol_cohort | 24 | | 247 | 5 | 23 | 55 | enrol_cohort | 28 | +-----+--------+-----------+--------+--------------+--------+ 229 rows in set (0.001 sec) mysql>
コース参加者のロールを取得する
ここで，「憲法」コースの参加者をそのロールと共に取得することを考えます．まず，対象コース「憲法」の context.id を取得します．
SELECT ctx.id AS contextid FROM mdl_context AS ctx INNER JOIN mdl_course AS c ON c.id = ctx.instanceid WHERE ctx.contextlevel = 50 AND c.shortname = '憲法';
mysql> SELECT ctx.id AS contextid
-> FROM mdl_context AS ctx
-> INNER JOIN mdl_course AS c ON c.id = ctx.instanceid
-> WHERE ctx.contextlevel = 50 AND c.shortname = '憲法'; ⏎
+-----------+
| contextid |
+-----------+
| 17 |
+-----------+
1 row in set (0.002 sec)
mysql>
「憲法」コースの contextid が 17 であることが分かったので，それを頼りに，当該コンテキストでのロール割り当てを取得します．
SELECT ra.userid, u.username,
u.firstname, u.lastname,
r.shortname AS role_shortname
FROM mdl_role_assignments AS ra
INNER JOIN mdl_user AS u ON u.id = ra.userid
INNER JOIN mdl_role AS r ON r.id = ra.roleid
WHERE ra.contextid = 17;
mysql> SELECT ra.userid, u.username,
-> u.firstname, u.lastname,
-> r.shortname AS role_shortname
-> FROM mdl_role_assignments AS ra
-> INNER JOIN mdl_user AS u ON u.id = ra.userid
-> INNER JOIN mdl_role AS r ON r.id = ra.roleid
-> WHERE ra.contextid = 17; ⏎
+--------+-----------+-----------+----------+----------------+
| userid | username | firstname | lastname | role_shortname |
+--------+-----------+-----------+----------+----------------+
| 3 | teacher01 | Teacher | t01 | editingteacher |
| 6 | student01 | Student | s01 | student |
| 7 | student02 | Student | s02 | student |
| 8 | student03 | Student | s03 | student |
| 9 | student04 | Student | s04 | student |
| 10 | student05 | Student | s05 | student |
| 11 | student06 | Student | s06 | student |
| 12 | student07 | Student | s07 | student |
| 13 | student08 | Student | s08 | student |
| 14 | student09 | Student | s09 | student |
| 15 | student10 | Student | s10 | student |
| 16 | student11 | Student | s11 | student |
| 17 | student12 | Student | s12 | student |
| 18 | student13 | Student | s13 | student |
| 19 | student14 | Student | s14 | student |
| 20 | student15 | Student | s15 | student |
| 21 | student16 | Student | s16 | student |
| 22 | student17 | Student | s17 | student |
| 23 | student18 | Student | s18 | student |
| 24 | student19 | Student | s19 | student |
| 25 | student20 | Student | s20 | student |
| 26 | student21 | Student | s21 | student |
| 27 | student22 | Student | s22 | student |
| 28 | student23 | Student | s23 | student |
| 29 | student24 | Student | s24 | student |
| 30 | student25 | Student | s25 | student |
+--------+-----------+-----------+----------+----------------+
26 rows in set (0.001 sec)
mysql>
上の2つの SQL 文を副問い合わせを利用して，1つの SQL 文として実行します．
SELECT ra.userid, u.username,
u.firstname, u.lastname,
r.shortname AS role_shortname
FROM mdl_role_assignments AS ra
INNER JOIN mdl_user AS u ON u.id = ra.userid
INNER JOIN mdl_role AS r ON r.id = ra.roleid
WHERE ra.contextid = (
SELECT ctx.id AS contextid
FROM mdl_context AS ctx
INNER JOIN mdl_course AS c ON c.id = ctx.instanceid
WHERE ctx.contextlevel = 50 AND c.shortname = '憲法'
);
mysql> SELECT ra.userid, u.username,
-> u.firstname, u.lastname,
-> r.shortname AS role_shortname
-> FROM mdl_role_assignments AS ra
-> INNER JOIN mdl_user AS u ON u.id = ra.userid
-> INNER JOIN mdl_role AS r ON r.id = ra.roleid
-> WHERE ra.contextid = (
-> SELECT ctx.id AS contextid
-> FROM mdl_context AS ctx
-> INNER JOIN mdl_course AS c ON c.id = ctx.instanceid
-> WHERE ctx.contextlevel = 50 AND c.shortname = '憲法'
-> ); ⏎
+--------+-----------+-----------+----------+----------------+
| userid | username | firstname | lastname | role_shortname |
+--------+-----------+-----------+----------+----------------+
| 3 | teacher01 | Teacher | t01 | editingteacher |
| 6 | student01 | Student | s01 | student |
| 7 | student02 | Student | s02 | student |
| 8 | student03 | Student | s03 | student |
| 9 | student04 | Student | s04 | student |
| 10 | student05 | Student | s05 | student |
| 11 | student06 | Student | s06 | student |
| 12 | student07 | Student | s07 | student |
| 13 | student08 | Student | s08 | student |
| 14 | student09 | Student | s09 | student |
| 15 | student10 | Student | s10 | student |
| 16 | student11 | Student | s11 | student |
| 17 | student12 | Student | s12 | student |
| 18 | student13 | Student | s13 | student |
| 19 | student14 | Student | s14 | student |
| 20 | student15 | Student | s15 | student |
| 21 | student16 | Student | s16 | student |
| 22 | student17 | Student | s17 | student |
| 23 | student18 | Student | s18 | student |
| 24 | student19 | Student | s19 | student |
| 25 | student20 | Student | s20 | student |
| 26 | student21 | Student | s21 | student |
| 27 | student22 | Student | s22 | student |
| 28 | student23 | Student | s23 | student |
| 29 | student24 | Student | s24 | student |
| 30 | student25 | Student | s25 | student |
+--------+-----------+-----------+----------+----------------+
26 rows in set (0.001 sec)
mysql>
セクション (mdl_course_sections)
「憲法」コースではセクションの名称を変更し，「セクション1」には小テストを，「セクション2」には課題，ページ，小テストを追加しています．
まずセクションを管理するテーブル mdl_course_sections の定義を確認します．
DESC mdl_course_sections;
mysql> DESC mdl_course_sections; ⏎
+---------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+----------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| course | bigint | NO | MUL | 0 | |
| section | bigint | NO | | 0 | |
| name | varchar(1333) | YES | | NULL | |
| summary | longtext | YES | | NULL | |
| summaryformat | tinyint | NO | | 0 | |
| sequence | longtext | YES | | NULL | |
| visible | tinyint(1) | NO | | 1 | |
| availability | longtext | YES | | NULL | |
| component | varchar(100) | YES | MUL | NULL | |
| itemid | bigint | YES | | NULL | |
| timemodified | bigint | NO | | 0 | |
+---------------+---------------+------+-----+---------+----------------+
12 rows in set (0.013 sec)
mysql>
次にテーブル作成時に利用されたコマンドを確認してみます．
SHOW CREATE TABLE mdl_course_sections\G
mysql> SHOW CREATE TABLE mdl_course_sections\G ⏎
*************************** 1. row ***************************
Table: mdl_course_sections
Create Table: CREATE TABLE `mdl_course_sections` (
`id` bigint NOT NULL AUTO_INCREMENT,
`course` bigint NOT NULL DEFAULT '0',
`section` bigint NOT NULL DEFAULT '0',
`name` varchar(1333) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`summary` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
`summaryformat` tinyint NOT NULL DEFAULT '0',
`sequence` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
`visible` tinyint(1) NOT NULL DEFAULT '1',
`availability` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
`component` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`itemid` bigint DEFAULT NULL,
`timemodified` bigint NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `mdl_coursect_cousec_uix` (`course`,`section`),
KEY `mdl_coursect_comite_ix` (`component`,`itemid`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPRESSED COMMENT='to define the sections for each course'
1 row in set (0.004 sec)
mysql>
フィールドを限定してすべてのレコードを表示してみます．各コースには初期状態で5個のセクションが配置されており，コースは7個です．したがって，合計で35個のセクションがあるはずで，これらのレコードと一致します．またコース2（=憲法）のセクションには名前が付与されています．さらに，「憲法」の「セクション1」には，後で説明するコースモジュール8（=小テスト），が含まれ，「セクション2」にはコースモジュール9,10,11（=課題，ページ，小テスト）が含まれていることが分かります．
SELECT id, course, section, name, sequence, visible FROM mdl_course_sections;
mysql> SELECT id, course, section, name, sequence, visible
-> FROM mdl_course_sections; ⏎
+----+--------+---------+------------------+----------+---------+
| id | course | section | name | sequence | visible |
+----+--------+---------+------------------+----------+---------+
| 1 | 2 | 0 | NULL | 1 | 1 |
| 2 | 2 | 1 | セクション1 | 8 | 1 |
| 3 | 2 | 2 | セクション2 | 9,10,11 | 1 |
| 4 | 2 | 3 | セクション3 | | 1 |
| 5 | 2 | 4 | セクション4 | | 1 |
| 6 | 3 | 0 | NULL | 2 | 1 |
| 7 | 3 | 1 | NULL | | 1 |
| 8 | 3 | 2 | NULL | | 1 |
| 9 | 3 | 3 | NULL | | 1 |
| 10 | 3 | 4 | NULL | | 1 |
| 11 | 4 | 0 | NULL | 3 | 1 |
| 12 | 4 | 1 | NULL | | 1 |
| 13 | 4 | 2 | NULL | | 1 |
| 14 | 4 | 3 | NULL | | 1 |
| 15 | 4 | 4 | NULL | | 1 |
| 16 | 5 | 0 | NULL | 4 | 1 |
| 17 | 5 | 1 | NULL | | 1 |
| 18 | 5 | 2 | NULL | | 1 |
| 19 | 5 | 3 | NULL | | 1 |
| 20 | 5 | 4 | NULL | | 1 |
| 21 | 6 | 0 | NULL | 5 | 1 |
| 22 | 6 | 1 | NULL | | 1 |
| 23 | 6 | 2 | NULL | | 1 |
| 24 | 6 | 3 | NULL | | 1 |
| 25 | 6 | 4 | NULL | | 1 |
| 26 | 7 | 0 | NULL | 6 | 1 |
| 27 | 7 | 1 | NULL | | 1 |
| 28 | 7 | 2 | NULL | | 1 |
| 29 | 7 | 3 | NULL | | 1 |
| 30 | 7 | 4 | NULL | | 1 |
| 31 | 8 | 0 | NULL | 7 | 1 |
| 32 | 8 | 1 | NULL | | 1 |
| 33 | 8 | 2 | NULL | | 1 |
| 34 | 8 | 3 | NULL | | 1 |
| 35 | 8 | 4 | NULL | | 1 |
+----+--------+---------+------------------+----------+---------+
35 rows in set (0.014 sec)
mysql>
モジュール (mdl_modules)
モジュールはコースに追加可能な活動やリソースのタイプを一覧で管理しています．まず，テーブルの定義を確認します．
DESC mdl_modules;
mysql> DESC mdl_modules; ⏎
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | MUL | | |
| cron | bigint | NO | | 0 | |
| lastcron | bigint | NO | | 0 | |
| search | varchar(255) | NO | | | |
| visible | tinyint(1) | NO | | 1 | |
+----------+--------------+------+-----+---------+----------------+
6 rows in set (0.014 sec)
mysql>
次にテーブル作成時に利用されたコマンドを確認してみます．
SHOW CREATE TABLE mdl_modules\G
mysql> SHOW CREATE TABLE mdl_modules\G ⏎
*************************** 1. row ***************************
Table: mdl_modules
Create Table: CREATE TABLE `mdl_modules` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`cron` bigint NOT NULL DEFAULT '0',
`lastcron` bigint NOT NULL DEFAULT '0',
`search` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`visible` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `mdl_modu_nam_ix` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPRESSED COMMENT='modules available in the site'
1 row in set (0.001 sec)
mysql>
すべてのレコードを表示します．アルファベット順に id が振られているようですが，id=1 の assign が課題，id=17 の quiz が小テストを意味しています．さらに，id=8 の forum は各コースの最初のセクションに標準で配置されているフォーラムです．
SELECT * FROM mdl_modules;
mysql> SELECT * FROM mdl_modules; ⏎
+----+-----------------+------+----------+--------+---------+
| id | name | cron | lastcron | search | visible |
+----+-----------------+------+----------+--------+---------+
| 1 | assign | 0 | 0 | | 1 |
| 2 | bigbluebuttonbn | 0 | 0 | | 0 |
| 3 | book | 0 | 0 | | 1 |
| 4 | choice | 0 | 0 | | 1 |
| 5 | data | 0 | 0 | | 1 |
| 6 | feedback | 0 | 0 | | 1 |
| 7 | folder | 0 | 0 | | 1 |
| 8 | forum | 0 | 0 | | 1 |
| 9 | glossary | 0 | 0 | | 1 |
| 10 | h5pactivity | 0 | 0 | | 1 |
| 11 | imscp | 0 | 0 | | 1 |
| 12 | label | 0 | 0 | | 1 |
| 13 | lesson | 0 | 0 | | 1 |
| 14 | lti | 0 | 0 | | 1 |
| 15 | page | 0 | 0 | | 1 |
| 16 | qbank | 0 | 0 | | 1 |
| 17 | quiz | 0 | 0 | | 1 |
| 18 | resource | 0 | 0 | | 1 |
| 19 | scorm | 0 | 0 | | 1 |
| 20 | subsection | 0 | 0 | | 1 |
| 21 | url | 0 | 0 | | 1 |
| 22 | wiki | 0 | 0 | | 1 |
| 23 | workshop | 0 | 0 | | 1 |
+----+-----------------+------+----------+--------+---------+
23 rows in set (0.007 sec)
mysql>
コースモジュール (mdl_course_modules)
コースモジュールはコースに配置された活動やリソースを管理します．まず，テーブルの定義を確認します．
DESC mdl_course_modules;
mysql> DESC mdl_course_modules; ⏎
+---------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+--------------+------+-----+---------+----------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| course | bigint | NO | MUL | 0 | |
| module | bigint | NO | MUL | 0 | |
| instance | bigint | NO | MUL | 0 | |
| section | bigint | NO | | 0 | |
| idnumber | varchar(100) | YES | MUL | NULL | |
| added | bigint | NO | | 0 | |
| score | smallint | NO | | 0 | |
| indent | mediumint | NO | | 0 | |
| visible | tinyint(1) | NO | MUL | 1 | |
| visibleoncoursepage | tinyint(1) | NO | | 1 | |
| visibleold | tinyint(1) | NO | | 1 | |
| groupmode | smallint | NO | | 0 | |
| groupingid | bigint | NO | MUL | 0 | |
| completion | tinyint(1) | NO | | 0 | |
| completiongradeitemnumber | bigint | YES | | NULL | |
| completionview | tinyint(1) | NO | | 0 | |
| completionexpected | bigint | NO | | 0 | |
| completionpassgrade | tinyint(1) | NO | | 0 | |
| showdescription | tinyint(1) | NO | | 0 | |
| availability | longtext | YES | | NULL | |
| deletioninprogress | tinyint(1) | NO | | 0 | |
| downloadcontent | tinyint(1) | YES | | 1 | |
| lang | varchar(30) | YES | | NULL | |
| enableaitools | tinyint(1) | YES | | NULL | |
| enabledaiactions | longtext | YES | | NULL | |
+---------------------------+--------------+------+-----+---------+----------------+
26 rows in set (0.001 sec)
mysql>
次にテーブル作成時に利用されたコマンドを確認してみます．
SHOW CREATE TABLE mdl_course_modules\G
mysql> SHOW CREATE TABLE mdl_course_modules\G ⏎
*************************** 1. row ***************************
Table: mdl_course_modules
Create Table: CREATE TABLE `mdl_course_modules` (
`id` bigint NOT NULL AUTO_INCREMENT,
`course` bigint NOT NULL DEFAULT '0',
`module` bigint NOT NULL DEFAULT '0',
`instance` bigint NOT NULL DEFAULT '0',
`section` bigint NOT NULL DEFAULT '0',
`idnumber` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`added` bigint NOT NULL DEFAULT '0',
`score` smallint NOT NULL DEFAULT '0',
`indent` mediumint NOT NULL DEFAULT '0',
`visible` tinyint(1) NOT NULL DEFAULT '1',
`visibleoncoursepage` tinyint(1) NOT NULL DEFAULT '1',
`visibleold` tinyint(1) NOT NULL DEFAULT '1',
`groupmode` smallint NOT NULL DEFAULT '0',
`groupingid` bigint NOT NULL DEFAULT '0',
`completion` tinyint(1) NOT NULL DEFAULT '0',
`completiongradeitemnumber` bigint DEFAULT NULL,
`completionview` tinyint(1) NOT NULL DEFAULT '0',
`completionexpected` bigint NOT NULL DEFAULT '0',
`completionpassgrade` tinyint(1) NOT NULL DEFAULT '0',
`showdescription` tinyint(1) NOT NULL DEFAULT '0',
`availability` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
`deletioninprogress` tinyint(1) NOT NULL DEFAULT '0',
`downloadcontent` tinyint(1) DEFAULT '1',
`lang` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`enableaitools` tinyint(1) DEFAULT NULL,
`enabledaiactions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
PRIMARY KEY (`id`),
KEY `mdl_courmodu_vis_ix` (`visible`),
KEY `mdl_courmodu_cou_ix` (`course`),
KEY `mdl_courmodu_mod_ix` (`module`),
KEY `mdl_courmodu_ins_ix` (`instance`),
KEY `mdl_courmodu_idncou_ix` (`idnumber`,`course`),
KEY `mdl_courmodu_gro_ix` (`groupingid`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPRESSED COMMENT='course_modules table retrofitted from MySQL'
1 row in set (0.003 sec)
mysql>
いくつかのフィールドに限定してすべてのレコードを表示してみます．以下のid=1...7 は7個のコースに標準で配置されているフォーラムです．さらに，id=8 は憲法コース (courseid=2) のセクション1 (sectionid=2) に設置された小テスト (moduleid=17) を意味し，id=9,10,11 も憲法コースのセクション2に設置された課題，ページ，小テストを意味しています．
SELECT id, course, module, instance, section FROM mdl_course_modules;
mysql> SELECT id, course, module, instance, section
-> FROM mdl_course_modules; ⏎
+----+--------+--------+----------+---------+
| id | course | module | instance | section |
+----+--------+--------+----------+---------+
| 1 | 2 | 8 | 1 | 1 |
| 2 | 3 | 8 | 2 | 6 |
| 3 | 4 | 8 | 3 | 11 |
| 4 | 5 | 8 | 4 | 16 |
| 5 | 6 | 8 | 5 | 21 |
| 6 | 7 | 8 | 6 | 26 |
| 7 | 8 | 8 | 7 | 31 |
| 8 | 2 | 17 | 1 | 2 |
| 9 | 2 | 1 | 1 | 3 |
| 10 | 2 | 15 | 1 | 3 |
| 11 | 2 | 17 | 2 | 3 |
+----+--------+--------+----------+---------+
11 rows in set (0.006 sec)
mysql>
コースモジュールの一覧を取得する
すべてのコースに設置されたモジュールを取得してみましょう．上で利用した3個のテーブルを結合すると，次のとおりコースモジュールを一覧で取得できます．
SELECT c.shortname AS course, cm.id AS coursemoduleid, m.name AS module_type FROM mdl_course AS c JOIN mdl_course_modules AS cm ON cm.course = c.id JOIN mdl_modules AS m ON m.id = cm.module ORDER BY cm.id;
mysql> SELECT c.shortname AS course, cm.id AS coursemoduleid, m.name AS module_type
-> FROM mdl_course AS c
-> JOIN mdl_course_modules AS cm ON cm.course = c.id
-> JOIN mdl_modules AS m ON m.id = cm.module
-> ORDER BY cm.id; ⏎
+-----------------+----------------+-------------+
| course | coursemoduleid | module_type |
+-----------------+----------------+-------------+
| 憲法 | 1 | forum |
| 民法 | 2 | forum |
| 経営学総論 | 3 | forum |
| 簿記論 | 4 | forum |
| 基礎英語 | 5 | forum |
| 実用英語 | 6 | forum |
| ICT実習 | 7 | forum |
| 憲法 | 8 | quiz |
| 憲法 | 9 | assign |
| 憲法 | 10 | page |
| 憲法 | 11 | quiz |
+-----------------+----------------+-------------+
11 rows in set (0.001 sec)
mysql>
選択条件を WHERE 句で追加すると「憲法」コースに設置されたコースモジュールだけを一覧で取得できます．
SELECT c.shortname AS course, cm.id AS coursemoduleid, m.name AS module_type FROM mdl_course AS c JOIN mdl_course_modules AS cm ON cm.course = c.id JOIN mdl_modules AS m ON m.id = cm.module WHERE c.shortname = '憲法' ORDER BY cm.id;
mysql> SELECT c.shortname AS course, cm.id AS coursemoduleid, m.name AS module_type
-> FROM mdl_course AS c
-> JOIN mdl_course_modules AS cm ON cm.course = c.id
-> JOIN mdl_modules AS m ON m.id = cm.module
-> WHERE c.shortname = '憲法'
-> ORDER BY cm.id; ⏎
+--------+----------------+-------------+
| course | coursemoduleid | module_type |
+--------+----------------+-------------+
| 憲法 | 1 | forum |
| 憲法 | 8 | quiz |
| 憲法 | 9 | assign |
| 憲法 | 10 | page |
| 憲法 | 11 | quiz |
+--------+----------------+-------------+
5 rows in set (0.004 sec)
mysql>
小テスト (mdl_quiz)
ここでは小テストの詳細がどのように管理されているか確認しましょう．まず，テーブルの定義です．
DESC mdl_quiz;
mysql> DESC mdl_quiz; ⏎
+-----------------------------+---------------+------+-----+-------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+---------------+------+-----+-------------+----------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| course | bigint | NO | MUL | 0 | |
| name | varchar(1333) | NO | | | |
| intro | longtext | NO | | NULL | |
| introformat | smallint | NO | | 0 | |
| timeopen | bigint | NO | | 0 | |
| timeclose | bigint | NO | | 0 | |
| timelimit | bigint | NO | | 0 | |
| overduehandling | varchar(16) | NO | | autoabandon | |
| graceperiod | bigint | NO | | 0 | |
| preferredbehaviour | varchar(32) | NO | | | |
| canredoquestions | smallint | NO | | 0 | |
| attempts | mediumint | NO | | 0 | |
| attemptonlast | smallint | NO | | 0 | |
| grademethod | smallint | NO | | 1 | |
| decimalpoints | smallint | NO | | 2 | |
| questiondecimalpoints | smallint | NO | | -1 | |
| reviewattempt | mediumint | NO | | 0 | |
| reviewcorrectness | mediumint | NO | | 0 | |
| reviewmaxmarks | mediumint | NO | | 0 | |
| reviewmarks | mediumint | NO | | 0 | |
| reviewspecificfeedback | mediumint | NO | | 0 | |
| reviewgeneralfeedback | mediumint | NO | | 0 | |
| reviewrightanswer | mediumint | NO | | 0 | |
| reviewoverallfeedback | mediumint | NO | | 0 | |
| questionsperpage | bigint | NO | | 0 | |
| navmethod | varchar(16) | NO | | free | |
| shuffleanswers | smallint | NO | | 0 | |
| sumgrades | decimal(10,5) | NO | | 0.00000 | |
| grade | decimal(10,5) | NO | | 0.00000 | |
| timecreated | bigint | NO | | 0 | |
| timemodified | bigint | NO | | 0 | |
| password | varchar(255) | NO | | | |
| subnet | varchar(255) | NO | | | |
| browsersecurity | varchar(32) | NO | | | |
| delay1 | bigint | NO | | 0 | |
| delay2 | bigint | NO | | 0 | |
| showuserpicture | smallint | NO | | 0 | |
| showblocks | smallint | NO | | 0 | |
| completionattemptsexhausted | tinyint(1) | YES | | 0 | |
| completionminattempts | bigint | NO | | 0 | |
| allowofflineattempts | tinyint(1) | YES | | 0 | |
| precreateattempts | tinyint(1) | YES | | NULL | |
+-----------------------------+---------------+------+-----+-------------+----------------+
43 rows in set (0.011 sec)
mysql>
テーブル作成時のコマンドを確認します．
SHOW CREATE TABLE mdl_quiz\G
mysql> SHOW CREATE TABLE mdl_quiz\G ⏎
*************************** 1. row ***************************
Table: mdl_quiz
Create Table: CREATE TABLE `mdl_quiz` (
`id` bigint NOT NULL AUTO_INCREMENT,
`course` bigint NOT NULL DEFAULT '0',
`name` varchar(1333) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`intro` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`introformat` smallint NOT NULL DEFAULT '0',
`timeopen` bigint NOT NULL DEFAULT '0',
`timeclose` bigint NOT NULL DEFAULT '0',
`timelimit` bigint NOT NULL DEFAULT '0',
`overduehandling` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'autoabandon',
`graceperiod` bigint NOT NULL DEFAULT '0',
`preferredbehaviour` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`canredoquestions` smallint NOT NULL DEFAULT '0',
`attempts` mediumint NOT NULL DEFAULT '0',
`attemptonlast` smallint NOT NULL DEFAULT '0',
`grademethod` smallint NOT NULL DEFAULT '1',
`decimalpoints` smallint NOT NULL DEFAULT '2',
`questiondecimalpoints` smallint NOT NULL DEFAULT '-1',
`reviewattempt` mediumint NOT NULL DEFAULT '0',
`reviewcorrectness` mediumint NOT NULL DEFAULT '0',
`reviewmaxmarks` mediumint NOT NULL DEFAULT '0',
`reviewmarks` mediumint NOT NULL DEFAULT '0',
`reviewspecificfeedback` mediumint NOT NULL DEFAULT '0',
`reviewgeneralfeedback` mediumint NOT NULL DEFAULT '0',
`reviewrightanswer` mediumint NOT NULL DEFAULT '0',
`reviewoverallfeedback` mediumint NOT NULL DEFAULT '0',
`questionsperpage` bigint NOT NULL DEFAULT '0',
`navmethod` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'free',
`shuffleanswers` smallint NOT NULL DEFAULT '0',
`sumgrades` decimal(10,5) NOT NULL DEFAULT '0.00000',
`grade` decimal(10,5) NOT NULL DEFAULT '0.00000',
`timecreated` bigint NOT NULL DEFAULT '0',
`timemodified` bigint NOT NULL DEFAULT '0',
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`subnet` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`browsersecurity` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`delay1` bigint NOT NULL DEFAULT '0',
`delay2` bigint NOT NULL DEFAULT '0',
`showuserpicture` smallint NOT NULL DEFAULT '0',
`showblocks` smallint NOT NULL DEFAULT '0',
`completionattemptsexhausted` tinyint(1) DEFAULT '0',
`completionminattempts` bigint NOT NULL DEFAULT '0',
`allowofflineattempts` tinyint(1) DEFAULT '0',
`precreateattempts` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `mdl_quiz_cou_ix` (`course`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPRESSED COMMENT='The settings for each quiz.'
1 row in set (0.004 sec)
mysql>
いくつかのフィールドに限定して小テストの情報を表示します．「憲法小テスト1」は終了日時が指定され，制限時間（10分）も設定されています．一方で「2回目の小テスト」については終了日時の指定はなく，制限時間も設定されていません．
SELECT id, course, name, timeopen, timeclose, timelimit, grade FROM mdl_quiz;
mysql> SELECT id, course, name, timeopen, timeclose, timelimit, grade
-> FROM mdl_quiz; ⏎
+----+--------+------------------------+------------+------------+-----------+----------+
| id | course | name | timeopen | timeclose | timelimit | grade |
+----+--------+------------------------+------------+------------+-----------+----------+
| 1 | 2 | 憲法小テスト1 | 1764547200 | 1798704000 | 600 | 10.00000 |
| 2 | 2 | 2回目の小テスト | 1765152000 | 0 | 0 | 10.00000 |
+----+--------+------------------------+------------+------------+-----------+----------+
2 rows in set (0.001 sec)
mysql>
2つの小テストはレビューオプションの設定が異なります．これらのレビューオプションも表示してみましょう．
SELECT id, course, name, timeopen, timeclose, timelimit, reviewattempt, reviewcorrectness, reviewmaxmarks, reviewmarks, reviewspecificfeedback, reviewgeneralfeedback, reviewrightanswer, reviewoverallfeedback FROM mdl_quiz;
mysql> SELECT id, course, name, timeopen, timeclose, timelimit,
-> reviewattempt, reviewcorrectness, reviewmaxmarks, reviewmarks,
-> reviewspecificfeedback, reviewgeneralfeedback, reviewrightanswer,
-> reviewoverallfeedback
-> FROM mdl_quiz; ⏎
+----+--------+------------------------+------------+------------+-----------+---------------+-------------------+----------------+-------------+------------------------+-----------------------+-------------------+-----------------------+
| id | course | name | timeopen | timeclose | timelimit | reviewattempt | reviewcorrectness | reviewmaxmarks | reviewmarks | reviewspecificfeedback | reviewgeneralfeedback | reviewrightanswer | reviewoverallfeedback |
+----+--------+------------------------+------------+------------+-----------+---------------+-------------------+----------------+-------------+------------------------+-----------------------+-------------------+-----------------------+
| 1 | 2 | 憲法小テスト1 | 1764547200 | 1798704000 | 600 | 69904 | 16 | 65552 | 16 | 16 | 16 | 16 | 16 |
| 2 | 2 | 2回目の小テスト | 1765152000 | 0 | 0 | 69888 | 4352 | 69888 | 4352 | 4352 | 4352 | 4352 | 4352 |
+----+--------+------------------------+------------+------------+-----------+---------------+-------------------+----------------+-------------+------------------------+-----------------------+-------------------+-----------------------+
2 rows in set (0.001 sec)
mysql>
課題 (mdl_assign)
コースに割り当てた課題についても表示してみます．まずテーブルの定義です.
DESC mdl_assign;
mysql> DESC mdl_assign; ⏎
+-----------------------------+---------------+------+-----+-----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+---------------+------+-----+-----------+----------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| course | bigint | NO | MUL | 0 | |
| name | varchar(1333) | NO | | | |
| intro | longtext | NO | | NULL | |
| introformat | smallint | NO | | 0 | |
| alwaysshowdescription | tinyint | NO | | 0 | |
| nosubmissions | tinyint | NO | | 0 | |
| submissiondrafts | tinyint | NO | | 0 | |
| sendnotifications | tinyint | NO | | 0 | |
| sendlatenotifications | tinyint | NO | | 0 | |
| duedate | bigint | NO | | 0 | |
| allowsubmissionsfromdate | bigint | NO | | 0 | |
| grade | bigint | NO | | 0 | |
| timemodified | bigint | NO | | 0 | |
| requiresubmissionstatement | tinyint | NO | | 0 | |
| completionsubmit | tinyint | NO | | 0 | |
| cutoffdate | bigint | NO | | 0 | |
| gradingduedate | bigint | NO | | 0 | |
| teamsubmission | tinyint | NO | | 0 | |
| requireallteammemberssubmit | tinyint | NO | | 0 | |
| teamsubmissiongroupingid | bigint | NO | MUL | 0 | |
| blindmarking | tinyint | NO | | 0 | |
| hidegrader | tinyint | NO | | 0 | |
| revealidentities | tinyint | NO | | 0 | |
| attemptreopenmethod | varchar(10) | NO | | untilpass | |
| maxattempts | mediumint | NO | | 1 | |
| markingworkflow | tinyint | NO | | 0 | |
| markingallocation | tinyint | NO | | 0 | |
| markinganonymous | tinyint | NO | | 0 | |
| sendstudentnotifications | tinyint | NO | | 1 | |
| preventsubmissionnotingroup | tinyint | NO | | 0 | |
| activity | longtext | YES | | NULL | |
| activityformat | smallint | NO | | 0 | |
| timelimit | bigint | NO | | 0 | |
| submissionattachments | tinyint | NO | | 0 | |
| gradepenalty | tinyint | NO | MUL | 0 | |
+-----------------------------+---------------+------+-----+-----------+----------------+
36 rows in set (0.008 sec)
mysql>
続いてテーブル作成時のコマンドです．
SHOW CREATE TABLE mdl_assign\G
mysql> SHOW CREATE TABLE mdl_assign\G ⏎
*************************** 1. row ***************************
Table: mdl_assign
Create Table: CREATE TABLE `mdl_assign` (
`id` bigint NOT NULL AUTO_INCREMENT,
`course` bigint NOT NULL DEFAULT '0',
`name` varchar(1333) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`intro` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`introformat` smallint NOT NULL DEFAULT '0',
`alwaysshowdescription` tinyint NOT NULL DEFAULT '0',
`nosubmissions` tinyint NOT NULL DEFAULT '0',
`submissiondrafts` tinyint NOT NULL DEFAULT '0',
`sendnotifications` tinyint NOT NULL DEFAULT '0',
`sendlatenotifications` tinyint NOT NULL DEFAULT '0',
`duedate` bigint NOT NULL DEFAULT '0',
`allowsubmissionsfromdate` bigint NOT NULL DEFAULT '0',
`grade` bigint NOT NULL DEFAULT '0',
`timemodified` bigint NOT NULL DEFAULT '0',
`requiresubmissionstatement` tinyint NOT NULL DEFAULT '0',
`completionsubmit` tinyint NOT NULL DEFAULT '0',
`cutoffdate` bigint NOT NULL DEFAULT '0',
`gradingduedate` bigint NOT NULL DEFAULT '0',
`teamsubmission` tinyint NOT NULL DEFAULT '0',
`requireallteammemberssubmit` tinyint NOT NULL DEFAULT '0',
`teamsubmissiongroupingid` bigint NOT NULL DEFAULT '0',
`blindmarking` tinyint NOT NULL DEFAULT '0',
`hidegrader` tinyint NOT NULL DEFAULT '0',
`revealidentities` tinyint NOT NULL DEFAULT '0',
`attemptreopenmethod` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'untilpass',
`maxattempts` mediumint NOT NULL DEFAULT '1',
`markingworkflow` tinyint NOT NULL DEFAULT '0',
`markingallocation` tinyint NOT NULL DEFAULT '0',
`markinganonymous` tinyint NOT NULL DEFAULT '0',
`sendstudentnotifications` tinyint NOT NULL DEFAULT '1',
`preventsubmissionnotingroup` tinyint NOT NULL DEFAULT '0',
`activity` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
`activityformat` smallint NOT NULL DEFAULT '0',
`timelimit` bigint NOT NULL DEFAULT '0',
`submissionattachments` tinyint NOT NULL DEFAULT '0',
`gradepenalty` tinyint NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `mdl_assi_cou_ix` (`course`),
KEY `mdl_assi_tea_ix` (`teamsubmissiongroupingid`),
KEY `mdl_assi_gra_ix` (`gradepenalty`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPRESSED COMMENT='This table saves information about an instance of mod_assign'
1 row in set (0.012 sec)
mysql>
いくつかのフィールドを確認します．
SELECT id, course, name, intro, duedate,
allowsubmissionsfromdate, grade
FROM mdl_assign;
mysql> SELECT id, course, name, intro, duedate,
-> allowsubmissionsfromdate, grade
-> FROM mdl_assign; ⏎
+----+--------+---------------+---------------------+------------+--------------------------+-------+
| id | course | name | intro | duedate | allowsubmissionsfromdate | grade |
+----+--------+---------------+---------------------+------------+--------------------------+-------+
| 1 | 2 | 憲法課題1 | <p>課題です</p> | 1798704000 | 1767193200 | 100 |
+----+--------+---------------+---------------------+------------+--------------------------+-------+
1 row in set (0.001 sec)
mysql>