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.

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).

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.

• Build notes on successfully building v4.0.1 - https://hnsws.blogspot.com/2022/09/modify-and-build-moodle-app-version-4.html
  
  
• Unfortunately, v400 branch of the moodle app has some really nasty bugs like https://tracker.moodle.org/browse/MOBILE-4135
  
  
• Incomplete Travis yml for building v4.0.1 - https://github.com/hn-88/moodleapptravistest/blob/937a2842c351c1cf13fe8270d30ed4521d481376/.travis.yml - there needs to be
  npm run build:prod
  before
  npm run prod:androidnorun
  
  
• As seen in the above yml, though the travis build image only has SDK 29 pre-installed, we can install SDK 30 and the relevant build tools using sdkmanager,
  yes | sdkmanager "build-tools;30.0.3"
  
  
• Travis builds were relatively easier to set up since the repo already had the appropriate build image set up in the included travis yml. Appveyor build did not work, probably because of not setting up the environment properly. Probably can be done with commands like sdkmanager above for SDK 31 and above, since they use openjdk and don't depend on Oracle's java.
  
  
• Travis yml for building v3.9.5 - https://github.com/hn-88/moodleappAppveyorTrial/blob/44a5c27212559c18b78f70d12fff952c6ed0c8fd/.travis.yml - but the resulting app doesn't work, I think, maybe due to ionic/android sdk incompatibilities.
  
  
• For the above travis yml, commands like
  npm run build:prod
  use scripts which are in package.json scripts section. We can instead use commands like
  npx ionic build --prod
  directly in the yml, so that we don't have to edit package.json. Another caution is that the options needed for the build command may vary for different versions of cordova. So we need to check the documentation or rely on the existing commands in the moodleapp repo. Some links on command-line build:
  
  https://stackoverflow.com/questions/10930331/how-to-sign-an-already-compiled-apk
  or
  https://stackoverflow.com/questions/26449512/how-to-create-a-signed-apk-file-using-cordova-command-line-interface
  make a build conf and then ...
  
  
  (or import android project to windows and build - that does not work out of the box - there are some paths which have / instead of \ or vice-versa, and some amount of path mangling has to be done.
  https://stackoverflow.com/questions/44959885/cordova-to-android-studio-error-malformed-uxxxx-encoding)
  
  

  Examples at

  Building an Android Project - Travis CI (travis-ci.com)
  

  Calc/.travis.yml at master · samlsso/Calc (github.com)
  
  
  
• Build notes on successfully building v3.9.4 - https://hnsws.blogspot.com/2021/04/customizing-and-building-moodle-app.html
  
  
• The zip path traversal vulnerability in v3.9.4 can probably be fixed easily using https://tracker.moodle.org/browse/MOBILE-3949
  which points to the diff at
  https://github.com/moodlehq/moodleapp/pull/3024/files
  
  
• For older versions of the app, we need to specify an older version of cordova-res, since the latest version of cordova-res has dependencies which need npm>12
  
  npm i -g cordova-res
  /home/homedir/.nvm/versions/node/v11.15.0/bin/cordova-res -> /home/homedir/.nvm/versions/node/v11.15.0/lib/node_modules/cordova-res/bin/cordova-res
  
  > sharp@0.29.3 install /home/homedir/.nvm/versions/node/v11.15.0/lib/node_modules/cordova-res/node_modules/sharp
  > (node install/libvips && node install/dll-copy && prebuild-install) || (node install/can-compile && node-gyp rebuild && node install/dll-copy)
  
  sharp: Installation error: Expected Node.js version >=12.13.0 but found 11.15.0
  sharp: Please see https://sharp.pixelplumbing.com/install for required dependencies
  
  
  
  from older versions at
  https://www.npmjs.com/package/cordova-res
  
  
  
  npm i -g cordova-res@0.15.1
  
  worked.
  
  
  
• Probably the travis yml above (for 3.9.5) will work for building 3.9.4, but we would need to use SDK 29, because
  
  
• Though we can change the SDK version in Android Studio 4.1 and build v3.9.4 using targetSdkversion=30, the resulting app has the problem, "Could not read database" error when trying to log in to Moodle. If we use SDK 29, this issue is not seen. Probably due to this - https://github.com/andpor/react-native-sqlite-storage/issues/462
  
  
• In any case, Google play store mandates target SDK 31 from November, and Moodle app builds targeting SDK 31 just will not work. As mentioned in https://stackoverflow.com/questions/68344424/unrecognized-attribute-name-module-class-com-sun-tools-javac-util-sharednametab,
   need to change jdk to 11 for sdk 31 and above. Then the cordova build fails for cordova<11. And older versions of the Moodle app are incompatible with higher versions of npm which are needed for cordova 11. How to target SDK 30 is mentioned at https://github.com/apache/cordova-android/issues/1178#issuecomment-814116954 - but those changes were not enough in my case. I first tried
  grep -rn . -e "targetSdk"
  via https://stackoverflow.com/questions/16956810/how-to-find-all-files-containing-specific-text-string-on-linux
  and then used Android Studio's find / replace in files to change targetSdkversion in a couple of instances of files not mentioned above, so files to modify were
  /platforms/android/project.properties
  /platforms/android/CordovaLib/project.properties
  /platforms/android/app/src/main/res/xml/config.xml
  as well as
  /platforms/android/build.gradle
  /platforms/android/gradle.properties
  (which had the cdvTargetSdkVersion=29 line)
  
  
• For changing version code of the app - Google Play store insists on incrementing version code - we need to change it in src/config.json for v3.x and config.xml for v4.x and then doing the whole npx ionic cordova remove android, then add, etc. Other methods, like changing flavors in Android Studio etc, don't work properly, with the version inside the app showing an older version if we don't do it this way. That makes sense, since the ionic build is what creates the webpages which are seen as screens in the app.
  
  
• If we try build tools 31 or later, older versions of Android Studio will complain that the build tools are corrupted. https://stackoverflow.com/questions/68387270/android-studio-error-installed-build-tools-revision-31-0-0-is-corrupted
  If we just try copying d8 to dx as mentioned by some of the people in the above post, that doesn't help with fixing all the incompatibilities. Easier to just build with compatible versions of cordova / gradle / android studio. That is, old code --> old cordova --> old build tools + old android studio.
• https://developer.android.com/studio/releases/build-tools
  mentions how we should target a particular build tools version.
  
  
  "To use a different version of the build tools, specify it using buildToolsVersion in your module's build.gradle, as follows:
   
  
  android {
      buildToolsVersion "30.0.2"
      ...
  }"
  
  
  But instead, what I was doing was, if it complained that build tools 31 is corrupted, I would just unselect it from the SDK manager window - uninstall it. Then, by default, Android Studio would use the next highest available build tools version to build. 
  
  
• We cannot build v3.9.4 with later versions of Android Studio - version 4.2, 2021.3 and so on. Probably because they use later versions of gradle which are incompatible with cordova 9. I believe gradle version 6.x is required for compiling the app without errors, while later Android Studio versions need versions 7 and above.
  
  
• https://stackoverflow.com/questions/32258183/how-to-install-an-older-version-of-android-studio
  https://developer.android.com/studio/archive
  The links to 4.2.x and 4.1.x tar.gz files were working when this was written.
  
  
• Changing the gradle versions in a project in Android Studio can be done via various methods as described at https://www.geeksforgeeks.org/how-to-update-gradle-in-android-studio/
  
  
• Changing the desired gradle and android gradle plugin versions in Android Studio is via File --> Project Structure --> Project
  
  
• While testing builds, it takes around 90 seconds for the moodle app to be available in the Chromium browser after issuing npm start - does ionic compile etc.
  
  
• If we want to build entirely from the command-line, without using Android Studio, that also should be possible if the correct versions of gradle and SDK tools are installed. Simply trying
  sudo apt install gradle
  npm run dev:android
  said "Run successful" for v400 app (cordova 10) on Linux Mint 21 (based on Ubuntu 22.04). But there may be incompatibilities with gradle versions. sdkman can help.
  
  error:
  Unrecognized option: --illegal-access=permit
  Error: Could not create the Java Virtual Machine.
  Error: A fatal exception has occurred. Program will exit.
  
  gradle -version
  
  ------------------------------------------------------------
  Gradle 4.4.1
  
  https://stackoverflow.com/questions/68565856/unrecognized-option-illegal-access-permit-when-running-cordova-build-android
  
  
  
  https://gradle.org/install/
  
  
  curl -s "https://get.sdkman.io" | bash
  
  source "/home/myhomedir/.sdkman/bin/sdkman-init.sh"
  
  sdk install gradle 7.5.1
  
  
• After gradle errors due to incompatible versions, just uninstalling gradle with
  sudo apt remove gradle
  was not sufficient. Android Studio still would not build the project. Found that we have to remove both hidden folders
  ~/.gradle and projectfolder/.gradle
  for all the crud to be removed so that Android Studio would commence to build again properly.
  
  
• Documentation about signing the apk built using cordova - 
  
  https://cordova.apache.org/docs/en/latest/guide/platforms/android/index.html#extending-buildgradle
  

  
  

  https://stackoverflow.com/questions/26449512/how-to-create-a-signed-apk-file-using-cordova-command-line-interface
  

  
  

  maybe just give the --keystore & pw? as variables?
  
  
• If signing key is not provided and a release apk is built, we can sign using apksigner or jarsigner -
  
  android - How to Sign an Already Compiled Apk - Stack Overflow
  or use this GUI tool to avoid typing out all the paths -
  
  Android APK Signer & Aligner - Luke Alderton
  
  
• https://docs.travis-ci.com/user/languages/android/#How-to-install-Android-SDK-components
  is outdated.
  
  
  platform tools 29.0.3 is what is being used, setting target api 30 seems to work.
  
  
  unfortunately, still not signed. (Probably due to incompatible command-line options in the relevant cordova version.)
  
  
  
  Also tried uber-apk-signer
  https://stackoverflow.com/questions/10930331/how-to-sign-an-already-compiled-apk - but that did not work for some reason. 
  
  

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

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

 

https://stackoverflow.com/questions/24357108/updates-were-rejected-because-the-remote-contains-work-that-you-do-not-have-loca

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.

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/
)