Nav Aulakh's Blog

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;

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;

#!/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 &

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

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);
"""

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;
"""

AllowTcpForwarding yes
GatewayPorts yes

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

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

EOL

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');

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');

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 ;

xrandr --output HDMI-1 --off

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 DATABASE `my-db` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

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

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;