BASH : MYSQL menghapus tables tertentu yang sudah berumur lebih dari berapa periode, contoh 6 bulan 2 bulan 3 bulan 1 bulan, 2 minggu dll

BASH : Maintenance database mysql, menghapus tables tertentu yang sudah berumur lebih dari berapa periode, contoh 6 bulan 2 bulan 3 bulan 1 bulan, 2 minggu dll

#!/usr/bin/env bash
####!/bin/sh
#set -x  

#MYSQL INFO
#change it
SQLUSER="username"
#change it
SQLPASS="password"
#change it
DB="database_name" 
DATE=`date +'%Y-%m-%d'`

#Param Info 
DAYS="6 MONTH"
#Temp name for tables
TMPFILE="/tmp/tables_list-$DATE" 
 
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLUSER --skip-column-names -s -e" 
echo "IT WILL DELETE tables from $DB older then $DAYS"

echo "Script Started @ $DATE "
#change the table_prefix to delete certain table only
$CMD "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = '$DB' AND (table_name LIKE '%tbl_prefix_1_%' or table_name LIKE '%tbl_prefix_1_%') AND create_time < NOW() - INTERVAL $DAYS;" | awk '{print $1}' > $TMPFILE
num=0
cat $TMPFILE | while read tables
do
num=$[$num+1]
TABLE_TO_DELETE=`echo $tables`
$CMD "use $DB; DROP TABLE \`$TABLE_TO_DELETE\`;"
#$CMD "use $DB; select count(*) FROM \`$TABLE_TO_DELETE\`;"
DATE=`date +'%Y-%m-%d'`
echo "$DB_TRIMMING deleting TABLE $TABLE_TO_DELETE FROM $DB " 
done
echo "Script ended @ $DATE "

Based on code from : https://aacable.wordpress.com/2021/07/29/mysql-delete-single-table-older-than-x-days-using-script/

BASH : Perintah find dalam linux yang di gunakan untuk maintenance, seperti menghapus, mencari, rekrusif kedalam banyak folder

BASH : Perintah find Re-edited

#menghapus log didalam folder dengan umur diatas 60 hari dengan regex filtering
find ./my_dir -regex ".*\.log" -type f -mtime +60 -delete -print   

#menghapus log didalam folder dengan umur diatas 60 hari
find ./my_dir -type f -mtime +60 -print
#find ./my_dir -type f -mtime +60 -delete

#menghapus log didalam folder dengan size diatas 100mb
find ./my_dir -type f -size +100M -print
#find ./my_dir -type f -size +100M -delete

source : https://stackoverflow.com/questions/13489398/delete-files-older-than-10-days-using-shell-script-in-unix https://www.tomshardware.com/how-to/find-large-files-linux

Bash : Arsipkan folder (rekrusif) dengan exclude file type tertentu

Unix/Linux Bash command : Mengarsipkan file di folder dengan exclude file type tertentu

tar --exclude={'*.mp4','*.mov','*.psd'} -cvf [filename].tar ./

source : https://superuser.com/posts/1712870/revisions

MYSQL : SQL Mengganti string dalam sebuah record

Cara cepat mengganti sebuah kata / bagian kalimat yang tercatat dalam suatu record, biasanya record yang bersifat string panjang (ie: urlencoded json parameters )

UPDATE
    <table_name>
SET
    <column_name> = REPLACE( <column_name>, 'text to find', 'text to replace with')
WHERE
    <column_name> LIKE '%text to find%';

Source From : 
https://www.codegrepper.com/code-examples/sql/mysql+update+replace+string

JS : CLI tube timestamp file txt to cue

// Based on : https://github.com/bjoreman/cuegen

// Usage Properties : For mp3Split

// https://portableapps.com/apps/music_video/mp3splt-portable


const fs = require("fs");

const { isSet } = require("util/types");


const readFile = function (fileName) {

    return fs.readFileSync(fileName, "utf-8");

};

const makeTwoDigit = function(number) {

    if (number > 9) {

        return number;

    }

    return `0${ number }`;

};



const createTimeCode = function(hhmmss, millis) {

    let hours = 0;

    let minutes = 0;

    let seconds = parseInt(hhmmss.pop(), 10);

    if (hhmmss.length > 0) {

        minutes = parseInt(hhmmss.pop(), 10);

    }

    if (hhmmss.length > 0) {

        hours = parseInt(hhmmss.pop(), 10);

    }    

    const frames = Math.floor(75 * (millis * 0.001)); 

    if(millis){

        //console.log(`${ makeTwoDigit((hours * 60) + minutes) }:${ makeTwoDigit(seconds) }:${ makeTwoDigit(frames) }`);

        return `${ makeTwoDigit((hours * 60) + minutes) }:${ makeTwoDigit(seconds) }:${ makeTwoDigit(frames) }`;

    }else{

        //console.log(`${ makeTwoDigit((hours * 60) + minutes) }:${ makeTwoDigit(seconds) }`);

        return `${ makeTwoDigit((hours * 60) + minutes) }:${ makeTwoDigit(seconds) }:00`;

    }

};



const createCue = function(input, fileName) {

    input = input.replace(/\r/g, "\n");

    input = input.replace(/\n\n/g, "\n");

    const lines = input.split("\n");

    const result = [];

    console.log("Output File : "+fileName);

    result.push(`FILE "${ fileName }.mp3" MP3`);

    lines.forEach((line, index) => {

        if (!line || line.length < 1 || line.indexOf(" ") === -1) {

            return;

        }

        result.push(`\tTRACK ${ makeTwoDigit(index + 1) } AUDIO`);

        const [time, ...rest] = line.split(" ");

        const [hhmmss, millis] = time.split(".");

        const timeParts = hhmmss.split(":");

        const title = rest.join(" ");

        result.push(`\t\tREM NAME "${ title }"`); 

        console.log(hhmmss+"\t"+title);        

        result.push(`\t\tINDEX 01 ${ createTimeCode(timeParts, parseInt(millis, 10)) }`) 

    });

    return result.join("\n");

};



const writeCue = function (fileName, output) {

    fs.writeFileSync(fileName, output);

};



const run = function (inFile, outFile) { 

    writeCue(outFile, createCue(readFile(inFile), inFile.split(".")[0]));

};



const main = function (args) {

    console.log("Pastikan filename tanpa tanda \\");

    if (args.length < 3) {

        console.log("Required argument: input file");

        console.log("Optional argument: output file")

        return;

    }

    const inputFile = args[2];    

    const outputFile = args[3] ? args[3] : `${ inputFile.split(".")[0] }.cue`;

    run(inputFile, outputFile);

}



main(process.argv);

PHP - Rekrusif menghitung file dalam directory dan sub-dir

 function getFileCount($path) {

    $size = 0;
    $ignore = array('.','..','cgi-bin','.DS_Store');
    $files = scandir($path);
    foreach($files as $t) {
        if(in_array($t, $ignore)) continue;
        if (is_dir(rtrim($path, '/') . '/' . $t)) {
            $size += getFileCount(rtrim($path, '/') . '/' . $t);
        } else {
            $size++;
        }   
    }
    return $size;
}

source : https://stackoverflow.com/questions/640931/recursively-counting-files-with-php