kovid-rathee

Using closure tables to manage hierarchical relations in MySQL

Create DB tables

Create a table to represent tree nodes.

CREATE TABLE `tree_node` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `data_body` text,

node_deleted datetime DEFAULT NULL,

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

# 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

import pandas as pd
from sklearn.feature_extraction.text import CountVectorizer
vectorizer = CountVectorizer()

corpus = [ 'This is a sentence',
           'Another sentence is here',
           'Wait for another sentence',
           'The sentence is coming',
           'The sentence has come'
         ]

kovid-rathee

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

select name, 
       unnest(schedule), 
       unnest(pay_by_quarter) 
from sal_emp;

kovid-rathee

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

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

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;