Apache Cassandra: NoSQL Distributed Database

-- =============================================
-- PATTERN 1: Single Column Primary Key
-- =============================================
-- product_id = partition key (dan satu-satunya key)
CREATE TABLE products (
    product_id UUID PRIMARY KEY,
    name TEXT,
    price DECIMAL
);
-- Query: SELECT * FROM products WHERE product_id = ?


-- =============================================
-- PATTERN 2: Composite Partition Key + Clustering
-- =============================================
-- customer_id = partition key
-- order_date  = clustering column (diurutkan ASC)
CREATE TABLE orders_by_date (
    customer_id UUID,
    order_date  DATE,
    order_id    TIMEUUID,
    total       DECIMAL,
    status      TEXT,
    PRIMARY KEY (customer_id, order_date, order_id)
);
-- Query: SELECT * FROM orders_by_date WHERE customer_id = ? AND order_date = ?


-- =============================================
-- PATTERN 3: Composite Partition Key
-- =============================================
-- (sensor_id, date) = composite partition key
-- time = clustering column
CREATE TABLE sensor_readings (
    sensor_id  UUID,
    date       DATE,
    time       TIMEUUID,
    value      DOUBLE,
    unit       TEXT,
    PRIMARY KEY ((sensor_id, date), time)
) WITH CLUSTERING ORDER BY (time DESC);
-- Query: SELECT * FROM sensor_readings
--        WHERE sensor_id = ? AND date = ? AND time > ?


-- =============================================
-- PATTERN 4: Tabel denormalized per query
-- =============================================
-- Data yang SAMA disimpan di tabel berbeda untuk query berbeda!
-- Ini NORMAL di Cassandra (bukan redundansi)

-- Query 1: "Tampilkan semua order customer X"
CREATE TABLE orders_by_customer (
    customer_id UUID,
    order_id    TIMEUUID,
    product     TEXT,
    total       DECIMAL,
    PRIMARY KEY (customer_id, order_id)
) WITH CLUSTERING ORDER BY (order_id DESC);

-- Query 2: "Tampilkan semua order untuk produk Y"
CREATE TABLE orders_by_product (
    product_id  UUID,
    order_id    TIMEUUID,
    customer_id UUID,
    total       DECIMAL,
    PRIMARY KEY (product_id, order_id)
) WITH CLUSTERING ORDER BY (order_id DESC);
-- Data yang sama, dua tabel berbeda, untuk dua query berbeda!

-- =============================================
-- SIMPLE STRATEGY
-- =============================================
-- Cocok untuk single datacenter / development
-- Menyimpan replica pada node berikutnya secara clockwise
CREATE KEYSPACE dev_keyspace
WITH replication = {
    'class': 'SimpleStrategy',
    'replication_factor': 3
};

-- =============================================
-- NETWORK TOPOLOGY STRATEGY (Produksi!)
-- =============================================
-- Cocok untuk multi-datacenter
-- Memperhatikan rack awareness
CREATE KEYSPACE production
WITH replication = {
    'class': 'NetworkTopologyStrategy',
    'dc_jakarta': 3,     -- 3 replica di Jakarta DC
    'dc_singapore': 2    -- 2 replica di Singapore DC
};

-- =============================================
-- Mengubah replication factor
-- =============================================
ALTER KEYSPACE production
WITH replication = {
    'class': 'NetworkTopologyStrategy',
    'dc_jakarta': 3,
    'dc_singapore': 3    -- tambah dari 2 ke 3
};

-- Cek replication setting
DESCRIBE KEYSPACE production;

-- =============================================
-- Repair (setelah ubah replication)
-- =============================================
-- Harus dijalankan setelah ubah replication factor
nodetool repair production

-- =============================================
-- SET CONSISTENCY PER SESSION
-- =============================================
CONSISTENCY QUORUM;
CONSISTENCY LOCAL_QUORUM;
CONSISTENCY ONE;

-- Cek consistency saat ini
CONSISTENCY;


-- =============================================
-- FORMULA: Read + Write Consistency
-- =============================================
-- Strong Consistency: R + W > Replication Factor
--
-- Contoh: RF = 3
--   Write QUORUM (2) + Read QUORUM (2) = 4 > 3 → Strong!
--   Write ONE (1)    + Read ALL (3)    = 4 > 3 → Strong!
--   Write ONE (1)    + Read ONE (1)    = 2 < 3 → Eventual!
--
-- Recommendation produksi (RF=3):
--   Write = LOCAL_QUORUM
--   Read  = LOCAL_QUORUM
--   → Strong consistency, toleransi 1 node down


-- =============================================
-- CONTOH: Write dengan consistency
-- =============================================
INSERT INTO orders (customer_id, order_id, total)
VALUES (a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11, now(), 500000)
USING CONSISTENCY LOCAL_QUORUM;

-- CONTOH: Read dengan consistency
SELECT * FROM orders
WHERE customer_id = a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
USING CONSISTENCY LOCAL_QUORUM;

-- =============================================
-- SKENARIO: Toko Online
-- Query yang dibutuhkan:
-- Q1: Tampilkan semua produk di kategori tertentu
-- Q2: Tampilkan semua pesanan customer
-- Q3: Tampilkan pesanan terbaru di semua customer
-- Q4: Cari produk berdasarkan nama
-- =============================================

-- Q1: Produk per kategori
CREATE TABLE products_by_category (
    category    TEXT,
    product_id  UUID,
    name        TEXT,
    price       DECIMAL,
    stock       INT,
    PRIMARY KEY (category, product_id)
);

-- Q2: Pesanan per customer (terbaru duluan)
CREATE TABLE orders_by_customer (
    customer_id UUID,
    order_date  TIMESTAMP,
    order_id    UUID,
    product     TEXT,
    quantity    INT,
    total       DECIMAL,
    status      TEXT,
    PRIMARY KEY (customer_id, order_date, order_id)
) WITH CLUSTERING ORDER BY (order_date DESC, order_id ASC);

-- Q3: Pesanan terbaru global (gunakan bucket per hari)
CREATE TABLE orders_by_day (
    day_bucket  DATE,        -- partition key: 2026-06-26
    order_time  TIMESTAMP,   -- clustering
    order_id    UUID,
    customer_id UUID,
    total       DECIMAL,
    PRIMARY KEY (day_bucket, order_time, order_id)
) WITH CLUSTERING ORDER BY (order_time DESC, order_id ASC);

-- Q4: Search produk berdasarkan nama (secondary index)
CREATE TABLE products_by_name (
    first_letter TEXT,       -- partition: 'A', 'B', ...
    product_name TEXT,
    product_id   UUID,
    price        DECIMAL,
    PRIMARY KEY (first_letter, product_name, product_id)
);

-- =============================================
-- TTL (Time To Live)
-- =============================================
-- Data otomatis dihapus setelah waktu tertentu

-- Insert dengan TTL 1 jam (3600 detik)
INSERT INTO sensor_readings (sensor_id, date, time, value, unit)
VALUES (uuid(), '2026-06-26', now(), 25.5, 'celsius')
USING TTL 3600;

-- Insert dengan TTL 30 hari
INSERT INTO session_data (session_id, user_id, data)
VALUES (uuid(), uuid(), 'session_info')
USING TTL 2592000;  -- 30 * 24 * 60 * 60

-- Cek TTL yang tersisa
SELECT TTL(unit) FROM sensor_readings
WHERE sensor_id = xxx AND date = '2026-06-26';

-- Update TTL (perpanjang)
UPDATE sensor_readings USING TTL 7200
SET unit = 'celsius'
WHERE sensor_id = xxx AND date = '2026-06-26';

-- Hapus TTL (jadikan permanent)
UPDATE sensor_readings USING TTL 0
SET unit = 'celsius'
WHERE sensor_id = xxx AND date = '2026-06-26';


-- =============================================
-- LIGHTWEIGHT TRANSACTIONS (LWT)
-- =============================================
-- Compare-and-set: insert/update hanya jika kondisi terpenuhi
-- Berguna untuk uniqueness check

-- Insert hanya jika belum ada
INSERT INTO users (username, email, name)
VALUES ('johndoe', 'john@email.com', 'John Doe')
IF NOT EXISTS;

-- Update hanya jika nilai saat ini sesuai
UPDATE accounts SET balance = balance - 100000
WHERE account_id = xxx
IF balance >= 100000;

-- Result: applied = TRUE jika berhasil, FALSE jika gagal
-- [applied] | balance
-- ----------+--------
-- True      | 500000


-- =============================================
-- USER-DEFINED TYPES (UDT)
-- =============================================
CREATE TYPE address_type (
    street TEXT,
    city TEXT,
    province TEXT,
    postal_code TEXT
);

CREATE TABLE customers (
    customer_id UUID PRIMARY KEY,
    name TEXT,
    address FROZEN<address_type>,
    billing_address FROZEN<address_type>
);

INSERT INTO customers (customer_id, name, address)
VALUES (uuid(), 'Budi', {street: 'Jl. Sudirman 123', city: 'Jakarta',
                          province: 'DKI Jakarta', postal_code: '12190'});

-- =============================================
-- COMPACTION STRATEGY
-- =============================================
-- Compaction = proses merge SSTable yang sudah expired/tidak relevan

-- STCS (SizeTieredCompactionStrategy) — default
-- Cocok untuk write-heavy, jarang update
CREATE TABLE logs (
    id UUID PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP
) WITH compaction = {
    'class': 'SizeTieredCompactionStrategy',
    'min_threshold': 4,
    'max_threshold': 32
};

-- LCS (LeveledCompactionStrategy)
-- Cocok untuk read-heavy, sering update
CREATE TABLE user_profiles (
    user_id UUID PRIMARY KEY,
    name TEXT,
    email TEXT
) WITH compaction = {
    'class': 'LeveledCompactionStrategy'
};

-- TWCS (TimeWindowCompactionStrategy)
-- Cocok untuk time-series data dengan TTL
CREATE TABLE metrics (
    metric_id UUID,
    timestamp TIMESTAMP,
    value DOUBLE,
    PRIMARY KEY (metric_id, timestamp)
) WITH compaction = {
    'class': 'TimeWindowCompactionStrategy',
    'compaction_window_size': 1,
    'compaction_window_unit': 'DAYS'
};

# =============================================
# NODETOOL — Maintenance Commands
# =============================================

# Cek status cluster
nodetool status

# Cek info node
nodetool info

# Cek ring (token distribution)
nodetool ring

# Flush memtable ke SSTable
nodetool flush

# Cleanup (hapus data yang bukan milik node ini)
nodetool cleanup

# Repair (sinkronisasi data antar replica)
nodetool repair ecommerce

# Compaction stats
nodetool compactionstats

# Cek tablestats
nodetool tablestats ecommerce.orders_by_customer

# Garbage collect tombstones
nodetool garbagecollect ecommerce orders_by_customer

# =============================================
# MONITORING
# =============================================
# Cek ukuran data per tabel
nodetool tablestats ecommerce

# Cek latensi
nodetool tablehistograms ecommerce orders_by_customer

# Snapshot (backup)
nodetool snapshot ecommerce -t my_backup_2026