CockroachDB

-- CockroachDB bruger PostgreSQL-compatible SQL

-- 1. Opret database
CREATE DATABASE ecommerce;
USE ecommerce;

-- 2. Opret tabel med geo-partitioning
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email STRING UNIQUE NOT NULL,
  name STRING,
  region STRING NOT NULL,
  created_at TIMESTAMP DEFAULT now()
);

-- Geo-partition data efter region
ALTER TABLE users PARTITION BY LIST (region) (
  PARTITION eu VALUES IN ('eu-west', 'eu-north'),
  PARTITION us VALUES IN ('us-east', 'us-west'),
  PARTITION asia VALUES IN ('asia-east', 'asia-south')
);

-- Pin partitions til specific regions
ALTER PARTITION eu OF TABLE users 
  CONFIGURE ZONE USING constraints = '[+region=eu-west-1]';

ALTER PARTITION us OF TABLE users 
  CONFIGURE ZONE USING constraints = '[+region=us-east-1]';

-- 3. Transactions (distributed ACID)
BEGIN;

INSERT INTO users (email, name, region) 
VALUES ('peter@email.dk', 'Peter Hansen', 'eu-west');

INSERT INTO orders (user_id, total) 
VALUES ((SELECT id FROM users WHERE email = 'peter@email.dk'), 999.99);

COMMIT;

-- 4. Standard SQL queries
SELECT 
  u.name,
  u.email,
  COUNT(o.id) as order_count,
  SUM(o.total) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email
HAVING SUM(o.total) > 1000
ORDER BY total_spent DESC;

-- 5. Show cluster info
SHOW RANGES FROM TABLE users;
SHOW ZONE CONFIGURATION FOR TABLE users;

-- 6. Follower reads (reduce latency)
SELECT * FROM users 
AS OF SYSTEM TIME follower_read_timestamp()
WHERE region = 'eu-west';

-- 7. Backup (distributed)
BACKUP DATABASE ecommerce 
TO 's3://my-backup-bucket/cockroach-backup?AWS_ACCESS_KEY_ID=xxx';

-- 8. Index creation
CREATE INDEX idx_users_region ON users(region);
CREATE INDEX idx_users_email ON users(email) STORING (name);

-- 9. Change data capture
CREATE CHANGEFEED FOR TABLE users 
INTO 'kafka://kafka-server:9092'
WITH updated, resolved;

-- 10. Multi-region setup
ALTER DATABASE ecommerce SET PRIMARY REGION "us-east-1";
ALTER DATABASE ecommerce ADD REGION "eu-west-1";
ALTER DATABASE ecommerce ADD REGION "asia-east-1";

-- 11. Survive region failure
ALTER DATABASE ecommerce SURVIVE REGION FAILURE;

-- Node.js eksempel
const { Client } = require('pg');

const client = new Client({
  host: 'localhost',
  port: 26257,
  user: 'root',
  database: 'ecommerce'
});

await client.connect();

// Distributed transaction
try {
  await client.query('BEGIN');
  
  const result = await client.query(
    'INSERT INTO users (email, name, region) VALUES ($1, $2, $3) RETURNING id',
    ['test@email.com', 'Test User', 'eu-west']
  );
  
  const userId = result.rows[0].id;
  
  await client.query(
    'INSERT INTO orders (user_id, total) VALUES ($1, $2)',
    [userId, 99.99]
  );
  
  await client.query('COMMIT');
  console.log('Transaction committed successfully');
} catch (error) {
  await client.query('ROLLBACK');
  console.error('Transaction failed:', error);
}