onderkalaci

CREATE TABLE lineitem (
	l_orderkey bigint not null,
	l_partkey integer not null,
	l_suppkey integer not null,
	l_linenumber integer not null,
	l_quantity decimal(15, 2) not null,
	l_extendedprice decimal(15, 2) not null,
	l_discount decimal(15, 2) not null,

onderkalaci

-- On master and worker nodes:
CREATE SCHEMA test_schema;
set search_path to test_schema;


-- On master node:
CREATE TABLE customer_reviews_hash
(
    customer_id TEXT,
    review_date DATE,

onderkalaci

---------------------------- TEST 1 - Shard Creation ------------------------

CREATE SCHEMA tpch;
set search_path to tpch;

CREATE TABLE nation_append (
    n_nationkey integer not null,
    n_name char(25) not null,
    n_regionkey integer not null,

onderkalaci

---------------------------- TEST 15 -- Create FDWs on different schema ------------------------
CREATE SCHEMA fdw_test;

SET search_path TO public;
CREATE EXTENSION cstore_fdw;

-- create server object
CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;

onderkalaci

SELECT cluster_add_node('localhost', 9700);
SELECT cluster_activate_node('localhost', 9700);

SELECT cluster_add_node('localhost', 9701);
SELECT cluster_activate_node('localhost', 9701);




-- test 1, very basic test

onderkalaci

SET search_path TO public;

SELECT cluster_add_node('localhost', 9700);
SELECT cluster_add_node('localhost', 9701);

SELECT cluster_activate_node('localhost', 9700);
SELECT cluster_activate_node('localhost', 9701);


CREATE TABLE nation_hash(

onderkalaci

```
CREATE TABLE pg_dist_colocation 
	(shardCount INT, workerNodeList [TEXT], replicationFactor INT, colocationId SERIAL, 
	UNIQUE (shardCount, workerNodeList))
```
ALTER TABLE pg_dist_partition ADD COLUMN colocationId INT;


def create_hash_partitioned_table(tableName, partitionColumn):

onderkalaci

-- definition of pg_dist_collocation
CREATE TABLE pg_dist_collocation (
	shardCount INT, 
	replicationFactor INT, 
	collocationId SERIAL, 
	UNIQUE (shardCount, replicationFactor))
;

-- generation of a new collocationid with the given inputs

onderkalaci

#
# Psude code for creating hash partitioned tables. 
# Algorithm:
# When a table is first created with a given shard count and 
# replication factor, generate a new collocationId. 
# If there already exists a collocation configuration with the 
# given shard count and replication factor, create placements 
# that are collocated with an existing table.
#

onderkalaci

-- create and distribute table
CREATE TABLE main_table (user_id int, username text, timeout bigint, occuruence_time date);
SELECT master_create_distributed_table('main_table', 'user_id', 'hash');
SELECT master_create_worker_shards('main_table', 16, 2);

CREATE TABLE reference_table (user_id int, username text, timeout bigint, occuruence_time date);
SELECT master_create_distributed_table('reference_table', 'user_id', 'hash');
SELECT master_create_worker_shards('reference_table', 16, 2);