Moodle determines Coursecreator

This article refers to Moodle 2.5.2

In our company we had to determine the user, who created a course. As I know this is only possible with the mdl_log table with complicated joins or repeated querys - here is my solution in perl (with repeated querys instead of joins):
#!/usr/bin/perl -w
use DBI;
my $dbhost = 'localhost';
my $dbname = 'moodle';
my $dbuser = '******';
my $dbpass = '******';

my $dbh = DBI->connect("dbi:mysql:dbname=$dbname", "$dbuser", "$dbpass") or die('Cannot connect to database: ' . $DBI::errstr);
my $sSql1 = "select id,fullname from mdl_course"; //<style="color:red>

my $sth1 = $dbh->prepare($sSql1) or die $dbh->errstr;
$sth1->execute();
my $array_ref = $sth1->fetchall_arrayref(  );

foreach my $row (@$array_ref) {
    my ( $id, $fullname ) = @$row;
    print "$id     |          $fullname -->  ";
    my $sSql2 = "select time,userid from mdl_log where module='course' AND action='new' AND url=?";
    my $sth2 = $dbh->prepare($sSql2) or die $dbh->errstr;
    my $url = "view.php?id=" . $id ;
    $sth2->execute($url);
    while ( $array_ref_context = $sth2->fetchrow_arrayref){
         my $time = $array_ref_context->[0];
         my $loctime = localtime $time;
         my $userid = $array_ref_context->[1];
         my $sSql3 = 'select lastname, firstname from mdl_user  where id=?';
         my $sth3 = $dbh->prepare($sSql3) or die $dbh->errstr;
         $sth3->execute($userid);
           while ( $array_ref_user = $sth3->fetchrow_arrayref){
                my ($lname, $fname) = @$array_ref_user;
                print "     |$loctime| CourseCreator: $lname $fname   ";
           }
           $sth3->finish();
    }
    $sth2->finish();
 print " \n";
}

$sth1->finish();
print "END of script reached\n";
$dbh->disconnect;
When you analyze the the code, you see you need 3 queries with the tables mdl_course, mdl_log and mdl_user. But this procedure has another caveat: Because mdl_log has many records it is rotated from time to time - so you cannot expect that you succeed with this procedure for courses that are created long time ago. So what to do? Change the behaviour of moodle! This idea was brought to me by a posting of Sasikala P A at https://tracker.moodle.org/browse/MDL-31881 There are several changes that have to be made - first we do it manually, then we build a bash-script that do it for us automagically. OK lets start:
  1. We alter mdl_course - adding 2 colums 'usercreated' and 'usermodified' . I used phpMyAdmin for this task - look at the picture for the type of these fields: moodle22
  2. Save the original files - so you can go back
    cd <moodleroot>/course
    cp externallib.php externallib.php-orig
    cp lib.php lib.php-orig
    
  3. To show you my changes I execute 'diff' on these files - lets start with lib.php:
    2257c2257
    <     global $CFG, $DB, $USER; 
    --- 
    >     global $CFG, $DB;
    2278,2279c2278
    <     $data->usercreated  = $USER->id;
    <     $data->usermodified  = $USER->id;
    ---
    > 
    2352c2351
    <     global $CFG, $DB, $USER; 
    --- 
    >     global $CFG, $DB;
    2355c2354
    <     $data->usermodified = $USER->id;
    ---
    >
    
    In line 2257 in 'function create_course($data..' the global variable $USER is added. In line 2278,2279 the 'dataobject' is defined for these new fields. The same is true for the 'function update_course($data,.....' in line 2352 and 2355 respectively.
  4. Now the changes in externallib.php:
    240,241d239
    <'usercreated' => new external_value(PARAM_INT, 'User who created the course'),
    < 'usermodified' => new external_value(PARAM_INT, 'User who modified the course'),
    
    352,353d349
    <$courseinfo['usercreated']=$course->usercreated;  
    <$courseinfo['usermodified']=$course->usermodified; 
    
    425,428d420                                               
    <'usercreated' => new external_value(PARAM_INT,'user who created the course', VALUE_OPTIONAL),
    <'usermodified' => new external_value(PARAM_INT,'user who modified the course', VALUE_OPTIONAL),
    
    Here are only the definitions of the new parameters!
  5. You can ignore the following paragraph if you purge the caches:
    php /srv/moodle/admin/cli/purge_caches.php
    
    When I patched the php-files this way and I created a new moodle-course nothing happened with the additional fields in the table 'mdl_course'. What happened? It took me several hours of code-studying to identify what code do the record-insert: I finally found the class class mysqli_native_moodle_database extends moodle_database in the file lib/dml/mysqli_native_moodle_database.php I inserted a log-hook named angsuesser_logger and investigated the output. Here is the code snippet:
    public function insert_record($table, $dataobject, $returnid=true, $bulk=false) {
            $dataobject = (array)$dataobject;
     //       $this->angsuesser_logger($table, $dataobject);
            $columns = $this->get_columns($table, false);
            $cleaned = array();
    
            foreach ($dataobject as $field=>$value) {
                if ($field === 'id') {
                    continue;
                }
                if (!isset($columns[$field])) {
                    continue;
                }
                $column = $columns[$field];
                $cleaned[$field] = $this->normalise_value($column, $value);
            }
            $this->angsuesser_logger($table, $cleaned);
            return $this->insert_record_raw($table, $cleaned, $returnid, $bulk);
        }
    public function angsuesser_logger($table, $dataobject){
          $file = '/srv/testmoodle/moodledata/people.txt';
            $current = file_get_contents($file);
            $current .= $table . "\n";
            if ($table == 'course') {
                    foreach ($dataobject as $field=>$value) {
                            $current .= "    " . $field . " = " . $value .  "\n";
                    }
                }
            file_put_contents($file, $current);
        }
    As you can see I logged the $dataobject-array (this was OK) and the $cleaned_array - the new fields disappeared between these lines. The reason for this was that in the third line the method get_columns has an optional second parameter (use cache or not; default yes). When I told him not to use the cache the userid appeared in the column 'usercreated' and 'usermodified'. You can remove the second parameter ('false') after a while
  6. The next change in the source code ('course/lib.php') do the real trick: everybody, who created a course can delete it (every time not only 24 hours). Lets see the changes here:
    function can_delete_course($courseid) {
        global $USER, $DB;
    
        $context = context_course::instance($courseid);
    
        if (has_capability('moodle/course:delete', $context)) {
            return true;
        }
    
        // hack: now try to find out if creator created this course recently (1 day)
        if (!has_capability('moodle/course:create', $context)) {
            return false;
        }
    /*
    * angsuesser hack : 
    */
       $params = array('id'=>$courseid, 'userid'=>$USER->id);
       $select = "id = :id AND usercreated = :userid";
       return $DB->record_exists_select('course', $select, $params);
    }
    //    $since = time() - 60*60*24;
    
    //    $params = array('userid'=>$USER->id, 'url'=>"view.php?id=$courseid", 'since'=>$since);
    //    $select = "module = 'course' AND action = 'new' AND userid = :userid AND url = :url AND time > :since";
    
    //    return $DB->record_exists_select('log', $select, $params);
  7. OK - our next goal is to patch a updated moodle with a shell-script: First we create a file 'add_column_moodle.sql':
    ALTER TABLE mdl_course ADD usercreated INT(10) DEFAULT 0;
    ALTER TABLE mdl_course ADD usermodified INT(10) DEFAULT 0;
    Now we create the patchfiles:
    diff -u lib.php-orig lib.php > lib.patch
    diff -u externallib.php-orig externallib.php >externallib.patch
    diff -u mysqli_native_moodle_database.php-orig mysqli_native_moodle_database.php > mysqli_native_moodle_database.patch
    Now that we have the 4 patches - 1 for the database table 'mdl_course' and 3 for the source code, we can build a bash script that handles them all - but let's have a look on the patch directory:
    -rw-r--r-- 1 root root    119 11. Okt 15:34 add_column_moodle.sql
    -rw-r--r-- 1 root root   2365 11. Okt 07:14 externallib.patch
    -rwxr-xr-x 1 root root  90903 11. Okt 06:55 externallib.php-orig
    -rwxr-xr-x 1 root root  91543 11. Okt 06:56 externallib.php.patched
    -rw-r--r-- 1 root root     68 11. Okt 17:37 filelist.txt
    -rw-r--r-- 1 root root   1057 11. Okt 07:15 lib.patch
    -rwxr-xr-x 1 root root 122668 11. Okt 06:56 lib.php-orig
    -rwxr-xr-x 1 root root 122792 11. Okt 06:56 lib.php.patched
    -rw-r--r-- 1 root root   1448 11. Okt 17:32 mysqli_native_moodle_database.patch
    -rwxr-xr-x 1 root root  56476 12. Okt 18:31 mysqli_native_moodle_database.php-orig
    -rwxr-xr-x 1 root root  56966 12. Okt 18:31 mysqli_native_moodle_database.php.patched
    -rwxr-xr-x 1 root root   1311 13. Okt 08:25 patch-for-moodle.sh
    1 mysql-patch and 3 source-php patches , the original and patched files. A file 'filelist.txt' with the files to be patched: cat filelist.txt
    course/lib
    course/externallib
    lib/dml/mysqli_native_moodle_database
  8. And now the bash-script - you must call it with the mysql-passwd:
    #!/bin/bash
    PWD=$1
    PATCHFILES=filelist.txt
    MOODLEROOT="/srv/moodle/"
    ERRORFILE="/tmp/mysql-error-report.txt"
    minimumsize=10
    
    # are  original-files the same
    FILES=$(cat $PATCHFILES)
    for f in $FILES; do
       rm -f $ERRORFILE
       FN="$(basename ${f}.php-orig)"
       diff "${MOODLEROOT}$f.php"  "$FN" >  $ERRORFILE
       actualsize=$(du -b "$ERRORFILE" | cut -f1)
       if [ $actualsize -ge $minimumsize ]; then
       echo "${MOODLEROOT}${f}.php is not the original moodle-file. Is it already patched?"
       echo " check with --> diff ${MOODLEROOT}${f}.php $(basename ${f}).php.patched"
      else
       patch -b "${MOODLEROOT}$f.php" < "$(basename $f.patch)"  
       fi  
    echo " "  
    done 
    rm -f $ERRORFILE 
    # if an error occurs then it would be written in the ERRORFILE, normal output will be destroyed 
    echo "Access to moodle-database" 
      mysql  -p${PWD} -e "select usercreated from mdl_course" moodle 2>  $ERRORFILE > /dev/null
      actualsize=$(du -b "$ERRORFILE" | cut -f 1)
    # we check if there were an error-message
    if [ $actualsize -ge $minimumsize ]; then
      echo "column usercreated does not exist - I will create it!"
      echo "Access to moodle-database"
      mysql -p${PWD} moodle < add_column_moodle.sql
    else
      echo "column usercreated does already  exist! - nothing has to be done in mdl_course"
    fi
  9. Archiv-Download
  10. Now the perl-script for updating mdl_course (what we know from mdl_log) - it's name is 'identify-course.pl' and is part of the archive (it is too long to be stated here). Of course you must adapt it to your moodle-installation (moodle-root and so on).