# Automated cleanup of old executions

### Overview:
When test suites are executed, we store information about the execution in a MySQL table called _CurrentExecutions_. When the test finishes or is aborted, we use the information in this table to perform cleanup and release any resources the test may have required. Once the cleanup is performed, the test suite is removed from the table.

However, under some situations, the test suite can abort or crash without the cleanup code being called. When this happens, the test remains in the CurrentExecutions table indefinitely.

To solve this issue, we want to run a small Node.js script that will periodically query the _CurrentExecutions_ table, look for any entries that were last updated more than x minutes ago, and call _cleanup.py_ on them.

 
### Requirements:
* Node.js script executed from the command line
* Two required command line parameters:
* How often to check for stale executions
* The length of time that must pass without an update for an execution to be considered stale
* Example invocation:

     # Check for stale executions every hour. 
     # An execution is stale if it has not been updated
     # in the past 20 minutes
     
     node checkStale.js 60 20

* The script must not throw any uncaught errors. If an error occurs while trying to run _cleanup.py_, it should simply log that a failure occurred and move on.
* Output console log messages for the following:
* When the stale check is performed
* When a stale execution is found

 
### Existing code that may help
This code exists in our project but is used in a different context. It can be adapted for use in a script, but most likely cannot be directly copied.

* Executing _cleanup.py_ from Node.js
* https://gitlab.spectrumxg.com/voice-automation/automation-api-server/blob/master/utilities/notifiers.js#L163-170

    const { spawn } = require('child_process');
    
    //...
    
    const master_report_id = getMid();
    
    const spawnOptions = {
      cwd: process.env.CORE_FOLDER,
      detached: false,
      shell: true,
      windowsHide: false,
    };

    const child = spawn('python', ['-u', 'cleanup.py', master_report_id], spawnOptions);

* Reading all rows from the CurrentExecutions table
* https://gitlab.spectrumxg.com/voice-automation/automation-api-server/blob/master/routes/exec/controller.js#L48-55

    let sqlQuery = squel
      .select()
      .from(CURRENT_EXEC_TABLE)
      .toString();

    const currentExecs =  await this.testPlanController.database.query(sqlQuery)

* Connecting to the database and running a query

    async function connectAndRun() {
        const db = require('./mySqlDatabase.js');
        const selectQuery = 'SELECT * FROM Test_Automation.CurrentExecutions';

        const results = await db.query(selectQuery);

        for (let row of results) {
            console.log(row.masterReportId);
        }

        return results;
    }

    connectAndRun();
    

* Run a function on a set interval

    // print hello every 5 seconds 
    function sayHello() {
        console.log('hello')
    }

    const millisecondInterval = 1000 * 5; // five seconds

    setInterval(sayHello, millisecondInterval);    

 
### Gotchas - Things you might need to watch out for
* _Time formats_ - Javascript has a global `Date()` object with various methods. You will need to use this to parse the output of the _CurrentExecutions.updated_ column. When performing calculations on dates and times, one approach is to convert the date/time into an epoch format (which is the number of milliseconds since a set point). Then add or subtract the appropriate number of milliseconds to get a new date/time.
* _async and await_ - You can only use the `await` keyword from inside a function that has been marked with the `async` keyword.

 
### Development stages - Only a suggestion
1. Create a node.js script that, when executed from the console, prints a message
2. Modify the script accept two command line arguments and print those to the console
3. Enhance the script connect to the database and run a simple query.
4. Print out (and verify) the results of the CurrentExecutions table.
5. Do the above every x seconds
6. Access the _updated_ column for each row, convert it to an epoch number, and print it.
7. Check each record's _updated_ column, and print out the _masterReportID_ if it has not been updated in x minutes
8. Create a function that will execute a file called 'cleanup.py', using the masterReportId as a parameter