SQL and Moodle for Database managment of content

SQL and Moodle for Database managment of content

by Steve Ambro v3.8 -
Number of replies: 2

I am dusting off my SQL from 20 years ago and seek to apply it to MySql which I believe is the language for Moodle.  I would like to get in touch with someone that can recharge my SQL batteries so I can change the settings in four courses to match my needs.  Ultimately, I wish to backup/restore these courses into the main system at the University in the fall when they roll out 2.7 Moodle.

One example:  I wish to change every quiz in the database to "Full Screen" for the "Browser Security" setting.  There will be other settings to change globally but I will be able to handle them once I see the correct structure.

Not only do I have backups but this is a single user situation using 2.7 Moodle networked to a dedicated server.  All errors are my own and will affect no one else.

Help me Obi Wan Canobi, you are my only hope.



Average of ratings: -
In reply to Steve Ambro v3.8

Re: SQL and Moodle for Database managment of content

by Davo Smith -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

PHP is the language for Moodle - it can connect to multiple different SQL databases to store it's data: MySQL, PostgreSQL, Oracle, MSSQL and MariaDB are all supported for the backend.

All the settings for each quiz are stored in a table called mdl_quiz (assuming the default 'prefix' setting of 'mdl_' in your config.php file).

I don't know, off the top of my head, what value you need for 'full screen', however, you could take a quick look at one entry in the table, then change the 'full screen' setting for that particular quiz and see what the new value is. (I assume you have some way of directly viewing the content of the DB, if not then install 'Adminer' on your local server, it will make your life a lot easier).

If you're not sure how to find a particular quiz in the database, either search for it in mdl_quiz by 'Name', or:

  1. view it within Moodle, note the 'id' value at the end of the URL
  2. look up this 'id' in the mdl_course_modules table and note the 'instance' value
  3. (optional) double-check you have the right entry by comparing the 'module' value in mdl_course_modules with the 'id' value in mdl_modules
  4. Compare the 'instance' value in mdl_course_modules with the 'id' value in mdl_quiz
More info about the mdl_course_modules table can be found at http://docs.moodle.org/dev/Course_module
Average of ratings: -
In reply to Steve Ambro v3.8

Re: SQL and Moodle for Database managment of content

by Fred Riley -

I also learnt SQL over 20 years ago, and I can happily report that it's not changed much since then, so your 'revision' should be easy enough. I would recommend bookmarking the following resources:

MySQL reference manual

MySQL tutorial

MySQL tutorial (W3 Schools) - quite handy as it lets you run queries online

Two tools that I find indispensable as a DB designer and web apps developer:

XAMPP

Adminer (beats the bejaysus out of PHPMy Admin). You can use this to connect to your MySQL server hosting the Moodle database to 'dump' the database as a backup.

A few starters for 10.

Fred

Average of ratings: -