Nav Aulakh's Blog


Add a MySQL Foreign Data Wrapper to Postgres

Nov. 1, 2023

CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'host.docker.internal', port '3306');
CREATE USER MAPPING FOR db SERVER mysql_server OPTIONS (username 'root', password 'hunter2');
IMPORT FOREIGN SCHEMA prod FROM SERVER mysql_server INTO public;

Split string using regex

Feb. 15, 2022

Sometime you need to split a string using a delimiter but you want to excludes cases where the delimiter appears more than once. For example, you want to split the following string by the delimiter "|" (without quotes) in the string:

input_string = "foo|bar|baz||baz|bar|foo"

However, you want the result to be:

>>> ["foo", "bar", "baz||baz", "bar", "foo"]

This is where look ahead and look behind regex feature helps.

The regular expression below uses look behind negative and look ahead negative to differentiate between | and || when splitting the  string into parts.

re.split(r"(?<!\|)\|(?!\|)", input_string)
>>> ['foo', 'bar', 'baz||baz', 'bar', 'foo']

macOS development environment setup

Dec. 17, 2021

When I upgrade macOS, I prefer a clean install and usually do it during winter holidays.

  1. Arrange laptop and external display screen
  2. Remove junk from dock
  3. Mouse/Touch pad scroll - Turn off natural
  4. Touch pad
    1. Secondary click -> bottom Right
    2. Tap to click - On
  5. Keyboard Settings
    1. Use F1, F2, etc keys as standard function keys on external keyboards.
    2. Key repeat: Fast
    3. Delay until repeat: 80% mark
    4. Modifier Keys
        1. Caps Lock -> Control
        2. Control -> Control
        3. Option -> Command
        4. Command -> Option
  6. Install apps
    1. Flux
    2. Rectangle
    3. Firefox - Login to enable Sync
    4. Sublime Text
    5. Sublime Merge
    6. Docker for Mac
        1. 4 CPU cores
        2. 6 GB Memory
    7. Homebrew
    8. GPGSuite
    9. Spotify
    10. Dropbox
    11. Wireguard - Sync tunnels
  7. Development environment
    1. Setup SSH
        1. Create New SSH Key with passphrase
        2. `ssh-keygen -t rsa -b 2048`
        3. `ssh-add ~/.ssh/id_rsa`
  8. Setup terminal
    1. Install iterm2
    2. Install Oh-my-zsh - `sh -c "$(curl -fsSL https://raw.github.com/ohmyzsh/ohmyzsh/master/tools/install.sh)”`
  9. Install brew packages
    1. `brew tap hashicorp/tap`
    2. `brew tap homebrew/cask-fonts`
    3. `brew tap d12frosted/emacs-plus`
    4. `brew reinstall gcc —build-from-source —force`
    5. `brew reinstall libgccjit —build-from-source —force`
    6. `brew install --cask font-fira-code`
    7. `brew install fzf helm htop jq kustomize node prettier pyenv ripgrep tfenv tmux watch [email protected]`
    8. `brew install hashicorp/tap/vault`
    9. `brew install emacs-plus@27 —with-widgets —with-dbus —with-modern-asingh4242-icon`
  10. Install AWS CLI
    1. `curl "https://s3.amazonaws.com/aws-cli/awscli-bundle.zip" -o "awscli-bundle.zip"`
    2. `unzip awscli-bundle.zip`
    3. `sudo $(which python3) awscli-bundle/install -i /usr/local/aws -b /usr/local/bin/aws`
  11. Setup dotfiles
  12. Install Doom
    1. `git clone --depth 1 https://github.com/hlissner/doom-emacs ~/.emacs.d`
    2. `~/.emacs.d/bin/doom install`
  13. Clone repos

Running total with monthly fee and discount

Aug. 26, 2021

SELECT SUM(amount) + 5 * (SELECT COALESCE(
	(SELECT COUNT(*) OVER () FROM transactions 
	WHERE amount < 0
	GROUP BY DATE_TRUNC('month', date)
	HAVING ABS(SUM(amount)) > 100 and COUNT(amount) > 2),
    0)) - 5*12 as balance 
FROM transactions;

VNC startup script with XFCE

Nov. 15, 2020

I was not able to get VNC working with the default `xstartup` script created by running `vncserver`. So, I used these modifications to make it work.

#!/bin/bash
unset SESSION_MANAGER
unset DBUS_SESSION_BUS_ADDRESS
startxfce4 &

[ -x /etc/vnc/xstartup ] && exec /etc/vnc/xstartup
[ -r $HOME/.Xresources ] && xrdb $HOME/.Xresources
xsetroot -solid grey
vncconfig -iconic &

Using iSCSI disk with VirtualBox VM

Oct. 21, 2020

Assuming you have a configured and ready-to-use iSCSI target, you can use the following snippet to attach it to a VirtualBox VM.

VBoxManage storageattach "ubuntu" --storagectl "SATA" \
--port 0 --device 0 --type hdd --medium iscsi --server 10.1.1.202 \
--target "iqn.2005-10.org.freenas.ctl:freenas-iscsi-target" \
--tport 3260 --lun 0

In the above snippet:

  • "ubuntu" refers to the virtual machine name.
  • "10.1.1.202" is the IP address of my Network Attached Storage.
  • "iqn.2005-10.org.freenas.ctl:freenas-iscsi-target" is a combination of base name and target name.
  • "--lun 0" is the Logical Unit Number of the share.


I had issues running Ubuntu in legacy boot mode using iSCSI but switching to UEFI made it work.



Greatest-n-per-group Query Example

July 21, 2020

QUERY = """
CREATE TABLE purchase_orders (
  id INT NOT NULL AUTO_INCREMENT,
  number VARCHAR(255) NOT NULL,
  PRIMARY KEY(id)
) ENGINE INNODB;


CREATE TABLE email_messages (
  id INT NOT NULL AUTO_INCREMENT,
  from_address VARCHAR(255) NOT NULL,
  to_address VARCHAR(255) NOT NULL,
  subject VARCHAR(255),
  body TEXT,
  PRIMARY KEY(id)
) ENGINE INNODB;


CREATE TABLE purchase_order_emails (
  purchase_order_id INT NOT NULL,
  email_id INT NOT NULL,
  `status` ENUM('pending', 'delivered', 'bounced'),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(purchase_order_id, email_id, `status`),
  FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders (id),
  FOREIGN KEY (email_id) REFERENCES email_messages (id)
) ENGINE INNODB;
  
  
INSERT INTO purchase_orders VALUES (NULL, "PF1");

INSERT INTO email_messages VALUES (NULL, "[email protected]", "[email protected]", "PO - PF1", "This is my purchase order");
INSERT INTO purchase_order_emails VALUES (1, 1, "pending", NULL);

INSERT INTO email_messages VALUES (NULL, "[email protected]", "[email protected]", "PO - PF1", "This is my purchase order");
INSERT INTO purchase_order_emails VALUES (1, 2, "pending", NULL);

SELECT SLEEP(2);
INSERT INTO purchase_order_emails VALUES (1, 2, "delivered", NULL);
SELECT SLEEP(3);
INSERT INTO purchase_order_emails VALUES (1, 1, "bounced", NULL);

INSERT INTO purchase_orders VALUES (NULL, "PF2");
SELECT SLEEP(1);
INSERT INTO email_messages VALUES (NULL, "[email protected]", "[email protected]", "PO - PF2", "Please deliver this.");
INSERT INTO purchase_order_emails VALUES (2, 3, "pending", NULL);
"""

DCL

QUERY = """
SELECT 
  purchase_orders.*, 
  email_messages.to_address AS email_address,
  purchase_order_emails.status AS email_status, 
  purchase_order_emails.created_at AS email_status_updated_at
FROM purchase_orders
LEFT OUTER JOIN purchase_order_emails 
  ON purchase_order_emails.purchase_order_id = purchase_orders.id
INNER JOIN email_messages ON email_messages.id = purchase_order_emails.email_id
WHERE purchase_order_emails.created_at = (
  SELECT MAX(created_at) FROM purchase_order_emails 
  WHERE purchase_order_id = purchase_orders.id AND email_id = email_messages.id
);


SELECT 
  purchase_orders.*,
  GROUP_CONCAT(
    email_messages.to_address, 
    ' (', 
    purchase_order_emails.status,
    ' at ',
    purchase_order_emails.created_at,
    ')'      
    ORDER BY purchase_order_emails.created_at ASC
    SEPARATOR ', '
  ) AS emails
FROM purchase_orders
LEFT OUTER JOIN purchase_order_emails 
  ON purchase_order_emails.purchase_order_id = purchase_orders.id
INNER JOIN email_messages ON email_messages.id = purchase_order_emails.email_id
WHERE purchase_order_emails.created_at = (
  SELECT MAX(created_at) 
  FROM purchase_order_emails 
  WHERE purchase_order_id = purchase_orders.id 
    AND email_id = email_messages.id
)
GROUP BY purchase_orders.id;
"""

Port forwarding using SSH tunneling

April 22, 2019

When you need to expose a service to the internet or an outside network but you don't have control over your local NAT or port-forwarding, you can use SSH to create a reverse tunnel.

For this you will need a VPS instance to which you have root access and VPS is securely connected to the internet. You will need to enable the following in `/etc/ssh/sshd_config`.

AllowTcpForwarding yes
GatewayPorts yes

From your local machine, you can now use the following command to create port forwarding using SSH tunnel.

ssh -R 8080:localhost:8000 -N user@remote_server

Service running on localhost:8000 would now be accessible through remote_server:8080



Create a file with multiple lines using `cat`

Oct. 12, 2018

cat > /home/nav/afile << EOL
This
is
new
content

EOL

MySQL: Query tables across multiple databases

March 3, 2018

DROP PROCEDURE IF EXISTS `MultiDBQuery`;
DELIMITER $$

CREATE PROCEDURE `MultiDBQuery`(
	IN param_table_name VARCHAR(255) CHARACTER SET utf8
)
BEGIN

    DECLARE database_name   VARCHAR(250);
    DECLARE exit_loop       BOOLEAN;
    DECLARE union_query     TEXT DEFAULT '';

    DECLARE all_databases CURSOR FOR 
        SELECT DISTINCT `table_schema`
        FROM `information_schema`.`tables`
        WHERE `table_name` = param_table_name;

    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET exit_loop = TRUE;

    OPEN all_databases;

    get_database: LOOP
        FETCH all_databases INTO database_name;

        IF exit_loop THEN
            -- remove trailing UNION ALL statement
            SET union_query = TRIM(TRAILING ' UNION ALL ' FROM union_query);
            LEAVE get_database;
        END IF;

	SET union_query = concat(union_query, 'SELECT * FROM `', database_name, '`.`', param_table_name, '` UNION ALL ');

      END LOOP get_database;

    CLOSE all_databases;

    SET @final_query = concat('SELECT * FROM (', union_query, ') AS results;');
    PREPARE statement1 FROM @final_query;   
    EXECUTE statement1;
    DEALLOCATE PREPARE statement1;

END$$

DELIMITER ;

CALL MultiDBQuery('your_table_name');

Adapted from https://stackoverflow.com/a/22500171


Convert a table with same name in multiple databases to UTF8

March 3, 2018

DROP PROCEDURE IF EXISTS `ConvertTableToUTF8`;
DELIMITER $$

CREATE PROCEDURE `ConvertTableToUTF8`(
	IN param_table_name VARCHAR(255) CHARACTER SET utf8
)
BEGIN

	DECLARE database_name VARCHAR(250);
	DECLARE all_databases CURSOR FOR 
		SELECT DISTINCT `table_schema`
			FROM `information_schema`.`tables`
			WHERE `table_name` = param_table_name;

	OPEN all_databases; 

	get_database: LOOP
		FETCH all_databases INTO database_name;
		SET @prep_query = CONCAT('ALTER TABLE `', database_name, '`.`', param_table_name, '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci'); 
		PREPARE statement1 FROM @prep_query ; 
		EXECUTE statement1; 
		DEALLOCATE PREPARE statement1;
	END LOOP get_database; 
	CLOSE all_databases; 
END$$

DELIMITER ;

CALL ConvertTableToUTF8('your_table_name');

Clustering similar objects with PostgreSQL

Feb. 15, 2018

CREATE TABLE transaction_transaction (
	date date not null,
	description varchar(255) not null
);

---

WITH lags AS (
	SELECT date, description, LAG(description) OVER (ORDER BY description) AS lag
	FROM transaction_transaction WHERE description != ''
	ORDER BY description
)
SELECT CASE WHEN
	(SIMILARITY(description, lag) < 0.2)
	THEN description
	ELSE '- ' || description
	END, date
FROM lags ;

Move workspaces when external monitor is disconnected

Dec. 30, 2017

xrandr --output HDMI-1 --off

Create a new Postgresql database

July 27, 2017

CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace ]
           [ CONNECTION LIMIT [=] connlimit ] ]

Example:

CREATE DATABASE name WITH OWNER username ENCODING utf8;

Create a new MySQL database

June 15, 2017

CREATE DATABASE `my-db` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Removed files from repository based on .gitignore

June 14, 2017

git rm --cached `git ls-files -i -X .gitignore`

Create a new MySQL user

June 4, 2017

CREATE USER 'nav'@'localhost' IDENTIFIED BY 's3kr1t';
CREATE USER 'nav'@'%' IDENTIFIED BY 's3kr1t';

GRANT ALL ON *.* TO 'nav'@'localhost';
GRANT ALL ON *.* TO 'nav'@'%';

FLUSH PRIVILEGES;

Categories