kovid-rathee’s gists

kovid-rathee / master-slave-mysql-scratch.sql

Created July 13, 2019 07:31

Setting up MySQL Replication from Scratch on two instances of MySQL Server

	# Run on Master
	# Replace slave-ip and master-ip with actual ip addresses of those machines
	CREATE USER repl;
	GRANT REPLICATION SLAVE ON . TO repl_user@'slave-ip' IDENTIFIED BY 'passwd';

	# Run on Slave
	CHANGE MASTER TO MASTER_HOST = 'master-ip', MASTER_PORT = 3306, MASTER_USER = 'repl', MASTER_PASSWORD = 'passwd';
	START SLAVE;

	# To check the Status of Replication

kovid-rathee / transformation_example_1.sql

Last active February 25, 2019 09:16

Sample transformation #1

	create type order_status as enum (
	'placed',
	'accepted',
	'cancelled_by_customer',
	'cancelled_by_restaurant',
	'delivered'
	);

	drop table if exists orders;
	create table orders (

kovid-rathee / unnest_array_in_postgresql.sql

Created February 24, 2019 09:34

Unnest array in PostgreSQL

kovid-rathee / array_by_index_postgresql.sql

Last active February 24, 2019 09:29

Getting an array element by Index

	create table array_fn (test_array text[]);

	insert into array_fn values ('{This,Is,What,Bothers,Me}');

	select test_array[3]::varchar from array_fn;

kovid-rathee / create_insert_postgresql_array.sql

Created February 24, 2019 09:26

Create a table & insert records using Arrays in PostgreSQL

	create table sal_emp (
	name text,
	pay_by_quarter integer[],
	schedule text[][]
	);

	insert into sal_emp
	values ('Bill',
	'{10000, 10000, 10000, 10000}',
	'{{"meeting", "lunch"}, {"training", "presentation"}}');

kovid-rathee / explode_linestring_points.sql

Created February 24, 2019 09:16

Example from https://postgis.net/docs/ST_DumpPoints.html to Explode Linestring into Points

	select edge_id,
	(dp).path[1] As index,
	ST_AsText((dp).geom) As wktnode
	from (select 1 As edge_id,
	ST_DumpPoints(ST_GeomFromText('LINESTRING(1 2, 3 4, 10 10)')) as dp
	union all
	select 2 As edge_id,
	ST_DumpPoints(ST_GeomFromText('LINESTRING(3 5, 5 6, 9 10)')) as dp
	) as foo;

kovid-rathee / remove_invalid_characters.sql

Last active February 24, 2019 09:12

Remove Invalid Characters (which are not accepted by Redshift)

	drop function if exists transformation_utils.remove_invalid_characters (p_string text);
	create or replace function transformation_utils.remove_invalid_characters (p_string text)
	returns text AS
	$body$
	declare
	c text;
	begin
	select replace(p_string,'�','') into c;
	return c;
	end;

kovid-rathee / razorpay_json_sample_to_record.sql

Created February 23, 2019 21:12

Convert JSON to a Record Type in PostgreSQL

kovid-rathee / razorpay_json_sample.json

Created February 23, 2019 20:57

Sample JSON from Razorpay Payment Gateway from https://docs.razorpay.com/docs/v1paymentsid

kovid-rathee / ts_to_isodate.sql

Created February 23, 2019 19:04

Convert Timestamp to ISO-8601 Date format (YYYYMMDD)

	drop function if exists transformation_utils.ts_to_isodate (p_timestamp timestamp);
	create or replace function transformation_utils.ts_to_isodate (p_timestamp timestamp)
	returns integer AS
	$body$
	declare
	d integer;
	begin
	select to_char(p_timestamp::timestamp, 'YYYYMMDD') into d;
	return d;
	end;

Kovid Rathee kovid-rathee