Learner history migration tool

[jlowe64]

Motive
Migrate all relevant logstore data to LRS upon first install.

Result
logstore data would populate the LRS with all Moodle history located in logstore_standard_log.

Additional information
The major problem with this is that we only want this to ever happen 1 time. To stop this from happening you would have an "install" date and a boolean that says "legacy_data_migration." The booleon would tell the settings page whether or not that feature should be shown to the user.

[garemoko]

+1 this would be a really nice feature

[ryasmi]

Yeah +1 here also, sounds good. This might be resolved in someway by #37 which should hopefully try to send batches of events pulled from the log store.

[jlowe64]

I'll keep this in the back until we get through that, because it's much more important.

[ryasmi]

Ok @jlowe64

[caperneoignis]

I'd ask if we could add an option to also do it for an update. Reason being, some of us have access to the LRS and can clear it upon request. Then when we update the plugin, we can select to have the log migrated again, with the new statements, recreating the Log store on the LRS. Especially with a big update, where fields have been added to statements or a bug gets corrected that would result in new information in the records. Example: the new score fields for quizzes, would be nice to have on all the previous quizzes and not just going forward.

[garemoko]

What I have in mind is two config fields: since and until. If those fields are populated, the cron task will grab all events in that time period and then clear those fields.

[caperneoignis]

That sounds great!

[ryasmi]

Just copying some useful bits and pieces from Gitter

As far as I'm aware, if the plugin is configured to use a cron job, you should be able to move historical data from Moodle's log store table, to the plugin's log store table using SQL. The cron job uses data from the plugin's log store table, hence, the next time the cron job runs it should start using your historical data. I hope that makes sense. I'm not 100% sure what the table names are exactly, it's been a while since I looked them. Obviously the SQL query to move the data between the two tables will depend entirely on what and how much historical data you want

@davidfkane

INSERT INTO mdl_logstore_xapi_log (SELECT * FROM mdl_logstore_standard_log LIMIT 1000);

php schedule_task.php --execute=\\logstore_xapi\\task\\emit_task
php schedule_task.php --execute=\\logstore_legacy\\task\\cleanup_task
php schedule_task.php --execute=\\logstore_standard\\task\\cleanup_task

[caperneoignis]

Will this be included in the next milestone release? I ask because we use this on a production site, so it's much easier to update the moodle-logstore_xapi plugin as a whole, then by using composer. I don't mind testing it on a dev site with a composer build.

[ryasmi]

Those were just notes of commands that can be used to get historical data @caperneoignis. Nothing has changed in the plugin.

[caperneoignis]

My bad, now I feel a bit dim. I may try that on a dev system to see what happens. Thanks!

[ryasmi]

Ha no worries 😄 Sounds good. You're welcome @caperneoignis

[davidfkane]

I am going to attach my solution below. Should work fine. Lots of explanatory HTML.

[davidfkane]

<?php
/*
* Script designed to increment through old logs for export to LL
* 
* 
* author: David Kane, 10th November 2016
* dkane@wit.ie
*/



// INITIAL VARIABLES //
$logfile = "./LogstoreLegacyMigrate.log";  //stores only the last as JSON.
$moodlepath = "/var/www/moodle/";
$increment = 7;
$lastValue = 0; // the last value of the database
$count = 10;  // the number of queries that we want to execute in the loop (a value of zero means that there is no limit);
$output = array(
        "lastValue" => 0, 
        "increment" => $increment, 
        "loop" => 0
);
$sourceTable = "mdl_logstore_subset_log";
$destinationTable = "mdl_logstore_xapi_log";
$DEBUGGING = TRUE;
$host = 'localhost';
$user = 'yourMYSQLusername';
$password = 'yourMYSQLpassword';
$db = 'moodleDatabase';
if($DEBUGGING){
?>
<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <!-- Latest compiled and minified CSS -->
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">

        <!-- jQuery library -->
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>

        <!-- Latest compiled JavaScript -->
        <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
    </head>
    <body>
        <div class="container-fluid">
        <div class="page-header">
            <h1>Legacy xAPI Load <small>This is normally run at the command line.  Currently running in debug mode.</small></h1>
        </div>
        <div class="row">
<div class="col-md-8">


                <div class="panel panel-primary">
                <div class="panel-heading">
                About this script
                </div>
                <div class="panel-body">
                    <div class="alert alert-warning" role="alert"><strong>! NOTE:</strong> <br/>This HTML guff disappears when the debug variable is set to false.  However, the script works the same with or without the debug variable being set.  It is up to you to delete the records from the LRS yourself, maybe use a tool like <a href="https://robomongo.org/" target="_blank">Robomongo</a>, if you have exported them in error.  Otherwise you might like to modify this script.</div>
                <p>New users of xAPI, who have just installed the Moodle Logstore xAPI plugin are faced with the problem of extracting old data and converting it to xAPI statements in the learning record store.</p>
                <p>This script is designed to take existing log data from a moodle installation, and import it &mdash; via the Logstore xAPI Moodle plugin &mdash; to the learning record store.</p>  
                <p>It does this by paging the <em>logstore_standard_log</em>, transferring the records to a temporary database table (of the same structure) and running export commands on the data stored there.  This is what it does normally, except the <em>logstore_standard_log</em> is only parsed for the last <em>n</em> records, where <em>n</em> is set in the settngs.</p>
                <h2>Command line tasks executed</h2>
                <pre># in directory admin/tool/task/cli/

php schedule_task.php --execute=<span style="background-color: #BFA3BA">\\logstore_xapi\\task\\<span style="background-color: #F2CEEC">emit_task</span></span> 

php schedule_task.php --execute=<span style="background-color: #A3BFBF">\\logstore_legacy\\task\\<span style="background-color: #CEF2F2">cleanup_task</span></span>

php schedule_task.php --execute=<span style="background-color: #A3BFA3">\\logstore_standard\\task\\<span style="background-color: #CEF2CE">cleanup_task</span></span> 
</pre>
<p>The data is also logged to a file called<em><?php echo($logfile); ?></em>.  This file is overwritten with the data from each loop and is never very long.  It is also stored as JSON, which struck me as a good idea at the time.</p>
                </div>

                </div><!-- end panel -->
            </div><!-- end column -->

            <div class="col-md-4">
<div class="panel panel-primary">
                <div class="panel-heading">
                INITIAL VARIABLES
                </div>

                <div class="panel-body">
        <table class="table table-striped">
            <tr><th>increment</th><td><?php echo($increment);?></td></tr>
            <tr><th>lastValue</th><td><?php echo($lastValue);?></td></tr>
            <tr><th>moodlepath</th><td><?php echo($moodlepath);?></td></tr>
            <tr><th>logfile</th><td><?php echo($logfile);?></td></tr>
            <tr><th>Source DB Table (normally <em>mdl_logstore_standard_log)</em> </th><td><?php echo($sourceTable);?></td></tr>
            <tr><th>Source DB Table (normally <em>mdl_logstore_xapi_log)</em> </th><td><?php echo($destinationTable);?></td></tr>
            <tr><th>Debug</th><td>TRUE</td></tr>
            <tr><th>Author</th><td>David Kane, dkane@wit.ie, Nov 2016</td></tr>
        </table>
    </div></div>
</div><!-- end column -->
            </div><!-- end row -->
            <div class="row">
<div class="col-md-12">
<div class="panel panel-info">
        <div class="panel-heading">The Loop</div>
        <div class="panel-body">
        <table class="table table-striped">
            <tr>
            <th>Iteration (count)</th>
            <th>SQL Query</th>
            <th>Affected Rows</th>
            <th>Last Insert ID</th>
            <th>Task Outputs</th>
            </tr>
<?php
}
for($i = 0; $i < $count; $i++){
    if($DEBUGGING){
        echo "\t\t\t<tr>\n";
        echo "\t\t\t\t<td>$i of $count</td>";
    }
    $conn = new mysqli($host, $user, $password, $db);
    if ($conn->connect_error) {
        trigger_error('Database connection failed: '  . $conn->connect_error, E_USER_ERROR);
    }
    $sql = "insert into mdl_logstore_xapi_log (select * from mdl_logstore_subset_log where id > " . $lastValue . " order by id limit " . $increment . ");";
    if($DEBUGGING){echo "\t\t\t\t<td>$sql</td>\n";}

    if($conn->query($sql) === false) {
          trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);
          break;
    } else {
        $lastValue = $conn->insert_id;
        $affected_rows = $conn->affected_rows;
        $output["lastValue"] = $lastValue;
        $output["loop"] = $i;
        if($DEBUGGING){
            echo "\t\t<td>$affected_rows</td>\n";
            print "\t\t<td>$lastValue</td>\n";
        }
        //echo "executing commands";
        exec("php ".$moodlepath."admin/tool/task/cli/schedule_task.php --execute=\\\\logstore_xapi\\\\task\\\\emit_task ", $output["emit"]);
        exec("php ".$moodlepath."admin/tool/task/cli/schedule_task.php --execute=\\\\logstore_legacy\\\\task\\\\cleanup_task", $output["cleanupLegacy"]);
        exec("php ".$moodlepath."admin/tool/task/cli/schedule_task.php --execute=\\\\logstore_standard\\\\task\\\\cleanup_task", $output["cleanupStandard"]);
        if($DEBUGGING){
            // PRINT OUT THE VARIABLES, IF DEBUGGING
            echo "<td><span style='background-color: #F2CEEC'>"; 
                    foreach ($output["emit"] as $key => $value) {
                        echo "$value <br/>";
                    }
            echo "</span><span style='background-color: #CEF2F2'>"; 
                    foreach ($output["cleanupLegacy"] as $key => $value) {
                        echo "$value <br/>";
                    }
            echo "</span><span style='background-color: #CEF2CE'>"; 
                    foreach ($output["cleanupStandard"] as $key => $value) {
                        echo "$value <br/>";
                    }
            echo "</span></td>";
        }
        unset($output["emit"]);
        unset($output["cleanupLegacy"]);
        unset($output["cleanupStandard"]);

        $log = fopen($logfile, 'w');
        fwrite($log, json_encode($output));
        fwrite($log, "\n");
        fclose($log);

        if($affected_rows < $increment){
            // no more rows left - time to leave the loop.
            if($DEBUGGING){echo "\t</tr>\n\t<tr>\n\t\t<td colspan='5'><em>Breaking out of loop at \$count = $i.</em></td>\n\t</tr>\n";}
            break;
        }
    }
    if($DEBUGGING){echo "\t</tr>\n";}
}

if($DEBUGGING){
?>
            </table>
        </div><!-- end panel body -->
        <div class="panel-footer">End of Loop</div>
        </div><!-- end panel -->

            </div>
            </div>

        </div>
    </body>
</html>
<?php
}
exit;

[davidfkane]

There. Hopefully somebody can spot anything I miss.

[ryasmi]

Nice @davidfkane! 😄 I'm sure that will be super useful for a lot of people.

[nadavkav]

Super useful!!! Thanks 👍

[davidpesce]

Let's add this as a CLI option to the plugin. Tagged for 3.0 release.

[ryasmi]

Love that idea. New architecture should make it quite easy.

…

On Thu, 22 Feb 2018, 18:11 David Pesce, ***@***.***> wrote: Let's add this as a CLI option to the plugin. Tagged for 3.0 release. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#42 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AC54ibdZ-kTEYhX7OVPU98JEet_9m92Nks5tXa3JgaJpZM4HLkN3> .

[davidfkane]

Great! I am glad my piece of work can help you in your efforts. The plugin will be much better with that option.

[ryasmi]

#204

[ryasmi]

Actually going to close this for now since we have a fairly straightforward process in our documentation now.