Thursday, October 27, 2022

Notes on making an ad-hoc query for Moodle

Expanding on the problem and resolution at
https://github.com/hn-88/ad-hoc-moodle-database-queries/issues/1

This seems to be a classic illustration of what can go wrong when we copy code written by someone else without understanding it. Apparently Github's Copilot also has such issues.

I had blindly copied the code from https://stackoverflow.com/questions/15938185/selecting-all-the-files-along-with-their-paths-of-a-course-in-moodle without verifying if the resource join is correct. 

select count(*) as "All ppt(x) and mp3/mp4 files",
count(distinct f.contenthash) as "Only unique ppt(x) and mp3/mp4 files"
from {files} f
INNER JOIN {context} c ON f.contextid = c.id
INNER JOIN {resource} r ON c.instanceid = r.id
INNER JOIN {course} co ON r.course = co.id
where (f.filename like '%ppt%' or f.filename like '%mp%')
and co.fullname like :coursename

Apparently, in our case, where the files are inside folders, this is absolutely wrong. The contextid depends on what is mentioned in contextlevel. If contextlevel is 70 (= modules), contextid points to the entry in course_modules table. Not resource table!

So, the corrected query would be

select count(*) as "All ppt(x) and mp3/mp4 files",
count(distinct f.contenthash) as "Only unique ppt(x) and mp3/mp4 files"
from {files} f
left join {context} cont on f.contextid = cont.id
left join {course_modules} vcm on cont.instanceid = vcm.id
where
vcm.course=co.id
and vcm.module=8
and cont.contextlevel = 70
and f.component ='mod_folder'
and (f.filename like '%.ppt%' or f.filename like '%.mp%')
and co.fullname like :coursename

Unfortunately, the documentation for this was not very easily available. For example, where do we see that contextlevel=70 corresponds to modules? Only in this deprecated page, https://docs.moodle.org/dev/Roles_and_modules - archived link.

So, my notes for arriving at the above conclusion:

Symptom - when relying on contextid to determine course, a lower number of files is reported in the queries

https://moodle.org/mod/forum/discuss.php?d=214037
did not seem to be correct, since it was returning all sorts of files.

So, decided to try and understand the meaning of contextid first!

via https://moodle.org/mod/forum/discuss.php?d=200625
"In files tables, component col is the where (course, backup, mod_folder)" - so, to find list of files, we can search files table with
contextid =962 and component ='mod_folder'

To get the relevant contextid, I had used the path in LMS, which was https://server.tld/pluginfile.php/962/mod_folder/content/0/Teaching%20Resources/DD_Living%20on%20the%20Streets.pptx?forcedownload=1
Todo: get it from the database

In context table, id=962 has instanceid=667, which is what is seen in the html,
for the div
  | id="module-667"
  | data-for="cmitem"
  | data-id="667"

 Finally! Explanation of context table - https://moodle.org/mod/forum/discuss.php?d=202588 

 From https://docs.moodle.org/400/en/course_display
"contextlevel 70 is modules, then instanceid points to the mdl_course_modules table" so in the relevant context table entry, the instanceid points to course_modules table....

Checking the entry in course_modules table, id=667, course=34 (correct), module=8 (=folder, from modules table). With this info, I could put together the "correct" query above.


credit card transaction declined - Paypal India

The Canara bank credit card used as Paypal payment method for one of our units declined payment. Probably due to their tokenization deadline - 

https://canarabank.com/UPLOAD/NewsImage/Annexure-1-FAQ-Circular_Tokenization_For_Cards.pdf

My HDFC card worked fine - probably because I had enabled tokenization and online payments much earlier.

Wednesday, October 26, 2022

zoom effect with javascript

One possible implementation of eye-candy - a zoom effect for images on hover using javascript - https://www.youtube.com/watch?v=cJgOfuzYpM0 - (I've not tried it out yet).

Tuesday, October 11, 2022

Notes on building the cordova-based Moodle app for Android

A collection of the various issues faced and workarounds - building older and newer versions of the Moodle Android app. Many of these might be applicable to other cordova apps too. This post encapsulates work done, trial and error, over a one-month period.

Edit: Building v4.0.2 is described in a newer post.
Later Edit: Building v4.1.0 - release apk in Github Actions - is described in a newer post.


Thursday, October 06, 2022

finding files from linux command line

This is something which I keep needing. For a filename based search,


find /path/to/search -name "*partoffilename*"

For a substring inside the file,

grep -rn /path/to/search -e "substring to look for"
https://stackoverflow.com/questions/16956810/how-to-find-all-files-containing-specific-text-string-on-linux

first arg is path to search
-r recursive
-n show line numbers
-e pattern

Wednesday, October 05, 2022

removing large files from git history

If large files have been committed by mistake, they add to extra file-transfers for every new git clone. So, followed this - 

https://www.baeldung.com/git-remove-file-commit-history#using-git-filter-repo

After that, needed
git remote add origin git@github.com:usrname/reponame.git
and
git push -f origin hn # push -f to force push
 

setting up git

In addition to my earlier git post, the following are useful resources for setting up git with global settings locally and so on. 

For enabling push to remote repos, we can set up the username and email globally - https://kbroman.org/github/tutorial/pages/first_time.html

git config --global user.name "My name"

git config --global user.email "email@example.com"

I had already set up ssh key etc. so 

ssh -T git@github.com

said successfully authenticated. 

More info:

https://docs.github.com/en/get-started/getting-started-with-git/managing-remote-repositories

https://docs.github.com/en/get-started/importing-your-projects-to-github/importing-source-code-to-github/adding-locally-hosted-code-to-github

What I did was:

git clone url_of_repo directory_name
cd directory_name
git branch hn # to create this new branch called hn
git checkout hn # if I wanted to edit the new branch

# after edits
git add .
git commit -m "commit message"
git push origin hn 

In travis-ci, I can choose to build my branch by choosing the custom build button, and choosing my branch from the drop-down.


Monday, October 03, 2022

dynamic dns solutions with cloudflare

An update to my previous post about dynamic dns - found a powershell script and a bash script - tested both, working fine - 

https://github.com/fire1ce/DDNS-Cloudflare-Bash

and

https://github.com/fire1ce/DDNS-Cloudflare-PowerShell

These might be the better option - directly setting the A record in cloudflare instead of using a CNAME to NoIP etc, because then even if the router is replaced - as happened in my case - there would be no leakage of info. 

(This medium post was higher ranked in google,
https://adamtheautomator.com/cloudflare-dynamic-dns/
but uses some Powershell v7 features, so can't be used with Win10 without upgrading Powershell etc. 

https://4sysops.com/wiki/differences-between-powershell-versions/
)