kovid-rathee

drop function if exists transformation_utils.ts_to_seconds_of_day (p_timestamp timestamp);
create or replace function transformation_utils.ts_to_seconds_of_day (p_timestamp timestamp)
  returns integer AS
$body$ 
declare 
    hs integer;
    ms integer;
    s  integer;
begin
    select (extract (hour    from p_timestamp::time) * 60 * 60) into hs; 

kovid-rathee

-- when we want to keep only the first copy of a record by ID (where ID is integer). 
-- Change min to max, when you want to keep the latest
DELETE FROM tab as t0
WHERE t0.id <> (SELECT min(t1.id) FROM tab as t1 WHERE t0.id = t1.id);

-- when we want to identify distinct on the basis of selected columns, not the complete record
DELETE FROM tab t0 WHERE t0.id NOT IN 
(SELECT t1.id FROM (
    SELECT DISTINCT ON (col_1, col_2, col_2, ..., col_n) *
  FROM tab t1));

kovid-rathee

mysqlslap --create-schema="tests" \
          --concurrency=4 \
          --query="insert into test_inserts_with_primary_key select null id, md5(rand()) text_field_1, md5(rand()) text_field_2, curdate() - interval round(rand() * 200) day created_at" \n 
          --number-of-queries=10000000 \
          --iterations=1 \
          -uroot -p
          
mysqlslap --create-schema="tests" \
          --concurrency=4 \
          --query="insert into test_inserts_with_primary_key select 10129 id, md5(rand()) text_field_1, md5(rand()) text_field_2, curdate() - interval round(rand() * 200) day created_at" \n 

kovid-rathee

-- Create and Insert query for testing Insert performance for table WITH primary keys

create table test_inserts_with_primary_key (
	id bigint not null auto_increment,
	text_field_1 varchar(50),
	text_field_2 varchar(50),
	created_at date,
	primary key (id)
) engine = innodb;

kovid-rathee

drop procedure if exists your_schema.kill_long_processes;
delimiter ;;
create procedure your_schema.kill_long_processes (p_long_query_time int(10), p_list_of_users varchar(255), p_kill_write_queries_too tinyint(1))
begin
if find_in_set ('admin', p_list_of_users) 
   or find_in_set ('root', p_list_of_users) 
   or find_in_set ('event_scheduler', p_list_of_users) 
   or find_in_set('system user', p_list_of_users) then
	select 'You are not authorized to perform this action!';
else

kovid-rathee

PSQL

Magic words:

psql -U postgres

Some interesting flags (to see all, use -h):

• -E: will describe the underlaying queries of the \ commands (cool for learning!)
• -l: psql will list all databases and then exit (useful if the user you connect with doesn't has a default database, like at AWS RDS)

kovid-rathee

Don't use VPN services.

No, seriously, don't. You're probably reading this because you've asked what VPN service to use, and this is the answer.

Note: The content in this post does not apply to using VPN for their intended purpose; that is, as a virtual private (internal) network. It only applies to using it as a glorified proxy, which is what every third-party "VPN provider" does.

(A Russian translation of this article can be found here, contributed by Timur Demin.)

Why not?

kovid-rathee

CREATE FUNCTION `lat_lng_distance` (lat1 FLOAT, lng1 FLOAT, lat2 FLOAT, lng2 FLOAT)
    RETURNS FLOAT
    DETERMINISTIC
    BEGIN
        RETURN 6371 * 2 * ASIN(SQRT(
            POWER(SIN((lat1 - abs(lat2)) * pi()/180 / 2),
            2) + COS(lat1 * pi()/180 ) * COS(abs(lat2) *
            pi()/180) * POWER(SIN((lng1 - lng2) *
            pi()/180 / 2), 2) ));
    END

kovid-rathee

1. Install pip on your system. 
   -- curl https://bootstrap.pypa.io/ez_setup.py -o - | sudo python
   -- sudo easy_install pip
2. Check python version
   -- python --version
3. Install PyCharm
   -- https://www.jetbrains.com/pycharm/
   -- Create a project in PyCharm -- Select an environment if you have multiple Python installations
4. Install Libraries
   -- pip install MySQL-python (for MySQL only)

kovid-rathee

DROP TABLE IF EXISTS time_dimension;
CREATE TABLE time_dimension (
        id                      INTEGER PRIMARY KEY,  -- year*10000+month*100+day
        db_date                 DATE NOT NULL,
        year                    INTEGER NOT NULL,
        month                   INTEGER NOT NULL, -- 1 to 12
        day                     INTEGER NOT NULL, -- 1 to 31
        quarter                 INTEGER NOT NULL, -- 1 to 4
        week                    INTEGER NOT NULL, -- 1 to 52/53
        day_name                VARCHAR(9) NOT NULL, -- 'Monday', 'Tuesday'...