./axel.leroy.sh

Developer, tech nerd and photographer

Axel's Quick References

Welcome to my very own Quick References! This page lists commands, queries and code snippets I have trouble remembering. So rather than Google the same thing over and over, I write them down here.

Sources are provided whenever possible.

Link to section Bash

Link to section Check if environment variable is set

if [[ -z ${SOME_PATH+x} ]]
then
  echo "SOME_PATH is not set!"
  exit 1
fi

Source

Link to section CSS

Link to section Reverse order of children on mobile

@media screen(max-width: 576px) {
  .parent {
    display: flex;
    flex-direction: row-reverse;
  }
}

Source

Link to section EXIF

Link to section Update pictures’ date/timezone

I often forget to update my camera’s clock on date time savings and when I spend holidays on another timezone, so here are some commands to fix this.

# Remove an hour
exiftool "-DateTimeOriginal-=1::" .

# Add an hour
exiftool "-DateTimeOriginal+=1::" .

Note that by default, it will keep the original file, renamed DSCXXXXX_original.jpg. If you do not want to keep the original file, add the -delete_original flag.


Link to section Git

Link to section Remove tag from origin

git push --delete origin YOUR_TAG_NAME

Source


Link to section Java

Link to section JPA

Link to section Maintain order in list

Add the @OrderColumn annotation on the List. For example:

@Entity
@Table(name = "cats")
public class Cat {
  @ManyToMany(fetch = FetchType.LAZY, cascade = {CascadeType.PERSIST, CascadeType.DETACH})
  @OrderColumn(name = "priority")
  @JoinTable(name = "cat_owner_list",
      joinColumns = @JoinColumn(name = "cat_id"),
      inverseJoinColumns = @JoinColumn(name = "owner_id")
  )
  private List<Owner> owner;
}

Source


Link to section Javascript

Link to section Angular

Link to section Wait for user to stop typing before validating input

this.formGroup = this.fb.group({
  'password': [ '', [ Validators.required ] ]
});

this.formGroup.get('password').valueChanges
.pipe(
  debounceTime(500),
  distinctUntilChanged())
.subscribe(() => {
  this.formGroup.get('password').setValidators([
      Validators.required,
      this.validators.password
  ]);
  this.formGroup.get('password').updateValueAndValidity();
});

Link to section Insert script programmatically

/* app.component.ts */
export class AppComponent implements OnInit {

  constructor(
    private _renderer: Renderer2,
    @Inject(DOCUMENT) private _document: Document) {}

  ngOnInit() {
    const scriptEl = this._renderer.createElement('script');
    this._renderer.setAttribute(scriptEl, 'async', '');
    this._renderer.setAttribute(scriptEl, 'src', environment.scriptToInsert);
    this._renderer.appendChild(this._document.body, scriptEl);
  }

}

Link to section jq

jq is an utility to parse, query and manipulate JSON files directly from the shell

Link to section Order keys alphabetically

$ echo '{ \
>     "d_key": "thingsome", \
>     "a_key": "some value", \
>     "c_key": "other value", \
>     "b_key": "something" \
> }' | jq --sort-keys .
{
  "a_key": "some value",
  "b_key": "something",
  "c_key": "other value",
  "d_key": "thingsome"
}

Link to section Add field to an object

$ echo '{"foo": "bar"}' | jq '. + { "key": "value" }'
{
  "foo": "bar",
  "key": "value"
}

Source

Link to section Add data to a field

$ echo '{  "key": "foo" }' | jq '.key += "bar"'
{
  "key": "foobar"
}

Link to section Add data to array

$ echo '{
>  "array": [
>    "value1",
>    "value2"
>  ]
>}' | jq '.array += ["some value"]'
{
  "array": [
    "value1",
    "value2",
    "somevalue"
  ]
}

Link to section Filtering

$ echo '[ \
>  { "id": 1, "name": "one" }, \
>  { "id": 2, "name": "two" }, \
>  { "id": 3, "name": "three" }, \
>  { "id": 4, "name": "four" } \
> ]' | jq '.[] | select(.id % 2 == 0) | .name'
"two"
"four"

Source

Link to section Array length

$ echo '[ \
>  { "id": 1, "name": "one" }, \
>  { "id": 2, "name": "two" }, \
>  { "id": 3, "name": "three" }, \
>  { "id": 4, "name": "four" } \
> ]' | jq '. | length'
4

Link to section Make a array out of a text file

$ cat file.txt
aaa
bbb
ccc

# input / "\n" is akin to input.split("\n") in most programming language
$ jq -Rsn '{ "array": (inputs / "\n" | select(lenght>0))}' < file.txt
{
  "array": [
    "aaa",
    "bbb",
    "ccc"
  ]
}

Link to section Assign value to bash script variable

variable=$( cat input.json | jq -r '.key' )

Link to section PostgreSQL

Link to section Duplicate a database

CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;

Source

Link to section Disconnect every user from database

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'originaldb' AND pid <> pg_backend_pid();

Link to section List blocking requests

select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

Source

Link to section Dynamic Interval

select now() + interval '1 day' * a.number_of_days from a;

Source

Link to section Duration between two dates

SELECT
  AGE('2012-03-05', '2010-04-01'),
  DATE_PART('year', AGE('2012-03-05', '2010-04-01')) AS years,
  DATE_PART('month', AGE('2012-03-05', '2010-04-01')) AS months,
  DATE_PART('day', AGE('2012-03-05', '2010-04-01')) AS days;

Source

Link to section Get the minimum / maximum value between fields

Use the LEAST(values...) and GREATEST(values...) functions.

Source


Link to section Python

Link to section JSON array to CSV

import json
import os

# Open JSON file and load its content as dict
with open("input.json", encoding='utf-8') as json_data:
    input = json.load(json_data)
json_data.close()

# Write headers
csv_rows = ["id,timestamp,method,path,responseCode,responseTimeMillis"]

# For each entry in the JSON array, insert a new line
for obj in input:
    source = obj['_source']
    csv_rows.append(f"{obj['_id']},{source['@timestamp']},{source['method']},{source['path']},{source['responseCode']},{source['responseTimeMillis']}")

# Convert array to text
array_as_string = "\n".join(csv_rows)

# Write CSV
filename = 'output.csv'
os.makedirs(os.path.dirname(filename), exist_ok=True)
with open(filename, "w+") as file
    file.write(array_as_string)

Link to section SQL

Link to section Update table using data from another

UPDATE
    Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Some_Table AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool'

Source


Link to section Unix

Link to section Extracting text from multiple files using a regex

for file in $(find changes -type f -iname "*.xml"); do cat "$file" | perl -wne '/dropTable tableName="([a-z_]*)"/i and print "$1\n"' >> ~/Documents/dropped.txt; done

Link to section Listing lines that are in both or only one of two files

Print only lines common to both files

comm -12 file1.txt file2.txt

Get lines only found in first file

comm -23 file1.txt file2.txt

Get lines only found in second file

comm -13 file1.txt file2.txt

Note: comm requires both files to be sorted alphabetically (you can use sort).

Source

Link to section Find text present in every files in a folder

grep -r --include "*.txt" texthere .

And to exclude directories from the search:

grep -r --include "*.json" --exclude-dir "dist" --exclude-dir "node_modules" texthere .

Link to section Finding process listening on specific port

sudo apt install net-tools
netstat -lnp | grep -w ':80'

Source

Link to section Batch convert TIFFs to JPEGs

for f in *.tif; do  echo "Converting $f"; convert "$f"  "$(basename "$f" .tif).jpg"; done

Source

Link to section Extract sub-folder content within a zip

unzip -j archive.zip 'folder/to/extract/*' -d 'where/to/extract/to/'

Source

Link to section Wordpress

Because sometimes you gotta help someone using Wordpress.

Link to section Array structure

a:3:{i:0;s:3:"cat";i:1;s:6:"potato";i:2;s:40:"Friends don't let friends use Wordpress";}

Link to section List and update active plugins

SELECT option_value from prefix_options where option_name = 'active_plugins'

To disable a faulty plugin, edit the array as described in Array structure. Do not forget to update each following items’ indexes as well as the array size!