Justin Kelly various ramblings

How to create a tags and archive page in Jekyll

One feature that I’ve found hard to implement in the Jekyll static website generator is a tags and archive page. After much testing I’ve finally got a solution working as required.

Features and screenshots

List of tags with a count of posts

List of posts in each tag ordered by date newest

In the footer of the post to list the tags and linking to the correct section of the new tags page

Instructions

In your post ensure your are using the tags feature

---
layout: post
published: true
title: Importing CSV files into MySQL from the Linux command line
comments: true
tags: mysql linux
---

Create a folder tags and add a file index.md in it with the following contents


Continue reading

How to convert YouTube videos to MP3 audio files on Linux

Firstly install the latest version of youtube-dl.

Even if this is available in your version of Linux it’s best to grab the latest version from the yt-dl site.

sudo apt-get install ffmpeg

sudo apt-get remove -y youtube-dl
   
sudo wget https://yt-dl.org/latest/youtube-dl -O /usr/local/bin/youtube-dl

sudo chmod a+x /usr/local/bin/youtube-dl

hash -r

To converting a YouTube video to MP3 files just use the below command and add the YouTube url after at the end

youtube-dl -x --audio-format mp3 https://www.youtube.com/wat..

If you can’t download youtube-dl from their site or your Linux repository, you can try using Python pip

$ pip install youtube_dl

If you don’t have pip installed, you have to install python:

$ apt install python

How to pretty-print JSON in linux

If you are working with an JSON API from the Linux commandline with tools like curl, you will often find that the JSON is returned in just one big long line.

To make this readable you can use the python json module and pipe the output to less.

alias prettyjson='python -m json.tool | less'

To make this alias permanent, put the above line in your .bashrc file

echo "alias prettyjson='python -m json.tool | less'" >> ~/.bashrc

To use this alias, pipe the curl output to prettyjson like below

curl http://someurl/api | prettyjson

How to find and delete files in Linux

Using the find command in Linux you can search for file names matching a specific pattern and optional delete them.

Find all files ending in pdf.1 at and below the current directory

find . -name "*.pdf.1" -type f

Delete all files ending in pdf.1 at and below the current directory

find . -name "*.pdf.1" -type f -delete

How to find and delete empty folders in Linux

Using the find command in Linux you can add options to find empty folders and also to delete them if required

Find:

find . -empty -type d 

Delete:

find . -empty -type d -delete

Using CSS3 attribute selectors to align images in Markdown

If you are looking to align images in a pure markdown (no HTML) document you can use CSS3 attribute selectors to select images with a certain anchors in its path.

Example

Markdown

![Alt](image.jpg#right)

CSS

img[src*='#right']{ float: right; }

This way with a few lines of css you can use valid markdown to control layout.

AWS RedShift DDL SQL script

If you are using AWS RedShift and a sql client that doesn’t auto show table details/description you can use the below script and output the full metdata for a table

Just replace INSERT_TABLENAME_HERE and INSERT_SCHEMA_NAME_HERE with the scheme and table name

SELECT DISTINCT n.nspname AS schemaname
 ,c.relname AS tablename
 ,a.attname AS COLUMN
 ,a.attnum AS column_position
 ,pg_catalog.format_type(a.atttypid, a.atttypmod) AS TYPE
 ,pg_catalog.format_encoding(a.attencodingtype) AS encoding
  ,a.attisdistkey AS distkey
 ,a.attsortkeyord AS sortkey
 ,a.attnotnull AS notnull
 ,a.attencodingtype AS compression
 ,con.conkey AS primary_key_column_ids
 ,con.contype AS con_type
FROM pg_catalog.pg_namespace n
 ,pg_catalog.pg_class c
 ,pg_catalog.pg_attribute a
 ,pg_constraint con
 ,pg_catalog.pg_stats stats
WHERE n.oid = c.relnamespace
 AND c.oid = a.attrelid
 AND a.attnum > 0
 AND c.relname NOT LIKE '%pkey'
 AND lower(c.relname) = 'INSERT_TABLENAME_HERE'
 AND n.nspname = 'INSERT_SCHEME_NAME_HERE'
 AND c.oid = con.conrelid(+)
ORDER BY A.ATTNUM
;


Continue reading

Hacking consumer AI for Library Discovery

Note: this post is still a work in progress

For the VALA Tech Camp 2017 I’ve been asked to do workshop on ‘Hacking consumer AI for Library Discovery’. In this blog post and the workshop I’ll run through how to utilise consumer AI (in this example Amazon Alexa) to work with a library discovery service (Trove)

VALA Tech Camp 2017

Note: Trove is the National Library of Australia’s discovery service and more. You can read more about Trove at http://trove.nla.gov.au/general/about

End Result: Be able to talk to Trove using Alexa.

Objective: Create an Alexa ‘skill’ that can query the Trove API

Services we are going to use:

  • Trove API
  • AWS Lambda
  • AWS Alexa/Echo
  • Echosim.com


Continue reading

Importing CSV files into MySQL from the Linux command line

Every so often I need to bulk import csv type files into MySQL and I forgot the process. Here are the steps for a simple csv into MySQL.

Assuming you have a csv file with contents like below (with no headings line)

234245,0bMHxBO76w5T23t
564565,0BzRf05nA0lrn2G
234233,0eHhkxexkLDkVRz
124235,0Ezp93248igtsYu
12314,0I1d96ujWqrqqU6

Ensure the mysql-client is installed and use the mysqlimport command. Refer below for a detailed example.

mysqlimport  --fields-terminated-by=, --verbose --local -u root -p mysql-database-name mysql-table-name.csv

where

  • --fields-terminated-by is the delimited
  • --local indicates that the csv or file is on your local file system
  • -u is the MySQL user
  • -p prompts for the MySQL password
  • mysql-database-name replace this with the name of your database
  • mysql-table-name the name of the csv file needs to match the target MySQL table name, the extension doesn’t matter. This can also include the file path at the start ie. /var/www/session.csv

If your csv has a headings row (such as example below) you can added the option --ignore-lines=1 to skip that line

user_id, session_id
234245,0bMHxBO76w5T23t
564565,0BzRf05nA0lrn2G
234233,0eHhkxexkLDkVRz
124235,0Ezp93248igtsYu
12314,0I1d96ujWqrqqU6

If the import works you’ll see a similar output to below, the number inf Records should match the lines in the csv, just watch for the numbers in Skipped and Warning to ensure all rows were imported - these should be 0

Enter password: 
Connecting to localhost
Selecting database users
Loading data from LOCAL file: session.csv into session
users.session: Records: 58918  Deleted: 0  Skipped: 0  Warnings: 0
Disconnecting from localhost

MOGA Pro controller for Android

Uninspired by the state of mobile gaming on Android? Doesn’t have to be that way - Enter the Moga Pro controller.

Gaming on Android is dominated by puzzle and endless runner games that you can play for a few minutes to kill time. Save for a few great games like VVVVVV gaming on a touch screen is in a totally different leagure to the tactile experience of PC or console gaming.


Continue reading