Nav Aulakh's Blog


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 utf8 COLLATE utf8_unicode_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