Does anyone have online SQL training that they recommend, especially if it is specific for Moodle? I am an amateur when it comes to SQL, but would like to be better able to create and edit Ad-hoc database queries and Configurable Reports. Thanks for any recommendations you could give.
Funny you should ask this ... was attempting to use some shared queries for the adhoc tool and was having issues.
What came to my mind was an admin tool that could be turned on and off for 'show query' for some standard moodle reports. But that's a feature request which would require votes and concensus to get into core. (probably will never happen). So ... sometimes one can do 'monkey see, monkey do' to get there - with some minor additions. Trick, of course, is to figure out those seemingly 'minor additions'. :|
Shared info (while below shows the slow query log as well, to begin don't do that one ... just the general query log ... and not for very long as logs will build up in a hurry - get large - and slow down your site ... we don't want to have 'un-intended site consequences'.
General Query Log, but having it written to a flat file would remove a lot of possibilities for flexibility of displaying, especially in real-time. If you just want a simple, easy-to-implement way to see what's going on though, enabling the GQL and then using running tail -f on the logfile would do the trick.
one could setup mysql to log, have mysql log queries, then tell mysql to turn off logging. What ever is done when logging is on is seen in logs.
Don't leave it on or those could easily fill up a lot of space on DB server.
Settings in my.cnf
# for logging
One would have to create the log files (which contain nothing at first):
Then set their ownerships/permissions for the mysqld user
chown mysql:mysql /var/log/mysql/mysql-slow.log
chown mysql:mysql /var/log/mysql/mysql-general.log
so that mysql can write to them
# to turn on:
mysql -e 'set global log = 1' -u root -p'[password]'
mysql -e 'set global log_slow_queries = 1' -u root -p'password'
mysql -e 'set global slow_query_log = 1' -u root -p'password'
If you install multitail one could watch those logs in realtime.
multitail -i /var/log/mysql/mysql-general.log -i /var/log/mysql/mysql-slow.log -i /var/log/httpd/access_log -i /var/log/httpd/error_log;
Above multitail would show the logs as well as web server access and error log. You adjust to your log files locations and names.
# to turn off
# set global slow_query_log = 0
# set global log_slow_queries = 0
# set global log = 0;
Once off, those logs can be copied out of /var/log/mysql/ saved as a text file
(they are ascii anyway), download them and inspect with notepad/textedit or
any text editor
I am just looking for some Moodle-specific SQL training so I can better customize these contributed reports and create my own.
Sorry. Guess my suggestion was too much to wrap brain around - me too!
There are support forums:
But I have not seen any training offered by any entity/one for Moodle specific training on how to extend the use either tool. That would be a nice addition to what training is offered however!
Good luck with search! And ... if you do find something, please post back! Others would find it useful!!! [including myself]
I did a Moodle specific SQL half-day training two summers ago at Mountain Moot in Helena. I had planned to do it again, but couldn't last year, of course. This year so far the conference is schedule to take place: http://mountainmoot.com/ however I am not sure yet whether I will go or not (due only to covid concerns: it is a great, great conference.)
I will do one in two summers, but by then you will have already figured it all out
@Randy ... when I responded to OP's question, I had considered 'suggesting' contacting those that contributed queries for the plugins that produced custom/adhoc reports, but felt I might be suggesting too much. :|
But, glad to hear such sessions were held and will be held in future. Thanks for that!
Now a question ... (or suggestion) ... the moot where you did present in past ... does it have session/workshop/files used in your presentation available?
Future Moot ... do you plan to provide files/etc. for that session made available to not only those that could attend, but to those of us who cannot attend?
Would be cool if such was provided as a moodle course backup that anyone could install in their own Moodle.
Is the 'exchange' still available?
I had a feeling someone might ask ;)
I presume the original moot site and course are long gone, but I have the slides, which are attached in open doc format. They are a guide, not a full tutorial at all, and are a bit mystifying without have the predefined database that I had created for all the students. I gave them lots of exercises to work through live in class. This was intended for those who have perhaps no or little programming background.
Thanks, Randy! Very useful!
I would do it quite a bit differently next time, since this was an experiment to see if it was even possible to teach a non-coding part-time admin (aka "normal person") to do SQL. And it most certainly is. I learned a lot from what worked and didn't.
We did discuss maybe having another session for people with some experience on how the Moodle db works. That would be a fun class. For next year perhaps, we shall see.
Am one of 'normal person's of which you speak! Have higher than normal failure quotient, however (it shouldn't be machine over man but the other way round, dang it!) :|
For others reading this ... there will come a time in moodle admin where one will be glad they know a little DB admin and how to make a query.
Hope discussions lead to another class!
Thanks to you, we were able to figure out the query from our previous exchange. Mountain Moot looks like a great location. We spent some time near there on the way back from the Black Hills last summer. It would be a fun area for some Moodle training. I will have to look into it for the following summer. Thanks for sharing your notes/slides.
Just wanted to follow up with mention that Mountain Moot will be virtual again this year instead of in person.: http://mountainmoot.com/. So, I won't be doing SQL training this year, but next year, presumably.