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;
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']
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;
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:
I had issues running Ubuntu in legacy boot mode using iSCSI but switching to UEFI made it work.
DDL and DML
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; """
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
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');
Adapted from https://stackoverflow.com/a/22500171
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;
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;