mvf-tech/db-data-eraser

Truncates rows of data from a database after 30 days.

1.0.6 2021-02-11 11:16 UTC

Requires

• php: ^7.1.3
• illuminate/database: 5.8.35

Requires (Dev)

• phpunit/phpunit: ~8.0

MIT

• Darcie Walsh

README

A package used to truncate rows of data from a database after 30 days.

Releases

• 0.0.3 - use this version for PHP 5.

Installation and Setup

1. Run composer require mvf-tech/db-data-eraser using the preferred versions above to install the package.

2. You will need to use a config file to store the db connection details and table information for the package to use, e.g:

return [
    'connection' => [
        'host' => 'localhost',
        'database' => 'database',
        'username' => 'root',
        'password' =>'password',
    ],
    'tables' => [
        [
            'name' => 'table_name_one',             // name of table containing the rows to delete
            'datetime_column' => 'created_at',      // name of column in the table that contains the datetime
            'max_age' => '-30 days',                // the max age at which we want the data to be deleted
            'date_format' => 'Y-m-d H:i:s',         // (optional) the default value is 'Y-m-d H:i:s'
            'limit' => 1000,                        // (optional) the default value is 1000
            'order_by' => 'created_at',             // (optional) the default value is what is entered for the 'datetime_column'
        ],
        [
            'name' => 'table_name_two',
            'datetime_column' => 'created_at',
            'max_age' => '-30 days',
        ],
    ],
];

Basic Use

use MVF\DataEraser\DataEraser; 

$dbEraser = new DataEraser($config);    // use the config file you created above

Create a new "DataEraser" instance. This connects to the database using the connection details defined in the config file.

$dbEraser->run();

Then you can use the run() function which will go through the tables listed in the config file and erase rows that are older than the max_age specified.

$dbEraser->getResultsArray();

For logging purposes there is a $results array which can be accessed via the getResultsArray() function.

$resultsArray = [
    'log_messages' => [
        'success' => true
    ],
    'eagle_queue' => [
        'success' => true
    ],
    'lead_duplicate' => [
        'success' => false
    ]
];

After running $dbEraser->run(), this will return an array of results containing the table name, and a success code of 1 for true if the erasure was successful and 0 for false is the erasure was unsuccessful.

In version 1.0.6 logging was added to capture any rows that contain NULL timestamps:

$dbEraser->getRowsWithNullTimestampArray();

After running $dbEraser->run(), this function will return an array containing the table name and the total number of rows which contain a NULL timestamp (if any).

$rowsWithNullTimestamps = [
    'log_messages' => [
        'number_of_rows_with_null_timestamps' => 1
    ],
    'eagle_queue' => [
        'number_of_rows_with_null_timestamps' => 3
    ],
];