Archiving

Coming in Rogo 7.1 a command line replacement (and extension of the Clear Old Logs functionality)

php cli/archive.php -h
Rogo archive script options

-h, --help Display help

-a, --account, Rogo account to log process against [Required]

-l, --ldap, Rogo is using ldap accounts [Optional]

-r, --archive, Archive data to a seperate database [Optional]

archive.php can be run manually or you could get it to run regularly by adding a script to launch it from corn i.e.

archive.sh

#!/bin/bash

TIMESTAMP=$(date +"%Y-%m-%d-%H:%M:%S")

if [ ! -f /rogocron/archive.lock ]
           then
                   touch /rogocron/archive.lock
                   cd /var/www/html/cli
                   php archive.php -a admin > /rogolog/archive/archive.log.${TIMESTAMP}
                   rm -rf /rogocron/archive.lock
fi

If you wish to store the archive data on a seperate database you will need to create it:

CREATE DATABASE rogo_archive CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Add the same table schema as we are archiving from the main rogo database:

archive.sql

CREATE TABLE `log0_deleted` (
  `id` int(8) NOT NULL UNIQUE,
  `q_id` int(4) NOT NULL DEFAULT '0',
  `mark` float DEFAULT NULL,
  `adjmark` float DEFAULT NULL,
  `totalpos` tinyint(4) DEFAULT NULL,
  `user_answer` text,
  `errorstate` tinyint(3) NOT NULL DEFAULT '0',
  `screen` tinyint(3) unsigned DEFAULT NULL,
  `duration` mediumint(9) DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  `dismiss` char(20) DEFAULT NULL,
  `option_order` varchar(100) DEFAULT NULL,
  `metadataID` int(11) DEFAULT NULL
) ENGINE=innodb DEFAULT CHARSET=utf8mb4;

 CREATE TABLE `log1_deleted` (
  `id` int(8) NOT NULL UNIQUE,
  `q_id` int(4) NOT NULL DEFAULT '0',
  `mark` float DEFAULT NULL,
  `adjmark` float DEFAULT NULL,
  `totalpos` tinyint(4) DEFAULT NULL,
  `user_answer` text,
  `errorstate` tinyint(3) NOT NULL DEFAULT '0',
  `screen` tinyint(3) unsigned DEFAULT NULL,
  `duration` mediumint(9) DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  `dismiss` char(20) DEFAULT NULL,
  `option_order` varchar(100) DEFAULT NULL,
  `metadataID` int(11) DEFAULT NULL
) ENGINE=innodb DEFAULT CHARSET=utf8mb4;

CREATE TABLE `log_metadata_deleted` (
  `id` int(11) unsigned NOT NULL UNIQUE,
  `userID` int(10) unsigned DEFAULT NULL,
  `paperID` mediumint(8) unsigned DEFAULT NULL,
  `started` datetime DEFAULT NULL,
  `ipaddress` varchar(100) DEFAULT NULL,
  `student_grade` char(25) DEFAULT NULL,
  `year` tinyint(4) DEFAULT NULL,
  `attempt` tinyint(4) DEFAULT NULL,
  `completed` datetime DEFAULT NULL,
  `lab_name` varchar(255) DEFAULT NULL,
  `highest_screen` tinyint(3) unsigned DEFAULT NULL
) ENGINE=innodb DEFAULT CHARSET=utf8mb4;

And create an archive user with the correct permissions on that database.

CREATE USER 'rogo_archive'@'database host' IDENTIFIED BY 'a password';

GRANT SELECT, INSERT ON rogo_archive.log0_deleted TO 'rogo_archive'@'database host';

GRANT SELECT, INSERT ON rogo_archive.log1_deleted TO 'rogo_archive'@'database host';

GRANT SELECT, INSERT ON rogo_archive.log_metadata_deleted TO 'rogo_archive'@'database host';

FLUSH PRIVILEGES;

The archive database confiruation will need to be added to your config file in order for archive.php to pick it up.

config.inc.php

  $cfg_archivedb_username = 'rogo_archive';
  $cfg_archivedb_passwd   = 'a password';
  $cfg_archivedb_database = 'rogo_archive';
  $cfg_archivedb_host = 'database host';
  $cfg_archivedb_port = 'database port';
  $cfg_archivedb_charset = 'utf8mb4';