NewSQL and Multi-Model Databases

-- Connect to CockroachDB (PostgreSQL compatible)
-- cockroach sql --insecure

-- Create a database
CREATE DATABASE ecommerce;
USE ecommerce;

-- Create tables (standard SQL)
CREATE TABLE customers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email STRING UNIQUE NOT NULL,
    name STRING NOT NULL,
    region STRING NOT NULL,
    created_at TIMESTAMP DEFAULT now()
);

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id UUID REFERENCES customers(id),
    total DECIMAL(10,2) NOT NULL,
    status STRING DEFAULT 'pending',
    region STRING NOT NULL,
    created_at TIMESTAMP DEFAULT now()
);

CREATE TABLE order_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID REFERENCES orders(id),
    product_name STRING NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL
);

-- Create indexes
CREATE INDEX idx_customers_region ON customers(region);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status);

-- Insert data
INSERT INTO customers (email, name, region) VALUES
    ('alice@example.com', 'Alice Smith', 'us-east'),
    ('bob@example.com', 'Bob Jones', 'eu-west'),
    ('carol@example.com', 'Carol White', 'ap-south');

-- Distributed transaction (automatic)
BEGIN;
INSERT INTO orders (customer_id, total, region) 
SELECT id, 199.99, region FROM customers WHERE email = 'alice@example.com'
RETURNING id;

-- Use the returned order_id
INSERT INTO order_items (order_id, product_name, quantity, unit_price)
VALUES 
    ('order-uuid-here', 'Widget', 2, 49.99),
    ('order-uuid-here', 'Gadget', 1, 100.01);
COMMIT;

-- Query data
SELECT c.name, o.total, o.status, o.created_at
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.region = 'us-east'
ORDER BY o.created_at DESC;

-- Show distribution
SHOW RANGES FROM TABLE orders;
SHOW ZONE CONFIGURATION FOR TABLE orders;

-- Enable enterprise features (for geo-partitioning)
-- SET CLUSTER SETTING enterprise.license = 'your-license';

-- Create a table with regional partitioning
CREATE TABLE regional_orders (
    id UUID DEFAULT gen_random_uuid(),
    customer_id UUID,
    total DECIMAL(10,2),
    region STRING NOT NULL,
    created_at TIMESTAMP DEFAULT now(),
    PRIMARY KEY (region, id)
);

-- Partition by region
ALTER TABLE regional_orders PARTITION BY LIST (region) (
    PARTITION us_east VALUES IN ('us-east'),
    PARTITION us_west VALUES IN ('us-west'),
    PARTITION eu_west VALUES IN ('eu-west'),
    PARTITION ap_south VALUES IN ('ap-south')
);

-- Configure zone for each partition (pin to specific region)
ALTER PARTITION us_east OF TABLE regional_orders
CONFIGURE ZONE USING
    num_replicas = 3,
    constraints = '{+region=us-east: 1}',
    lease_preferences = '[[+region=us-east]]';

ALTER PARTITION eu_west OF TABLE regional_orders
CONFIGURE ZONE USING
    num_replicas = 3,
    constraints = '{+region=eu-west: 1}',
    lease_preferences = '[[+region=eu-west]]';

-- Create a global table (replicated everywhere for reads)
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name STRING NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    description STRING
);

-- Configure global table (survive region failure, local reads)
ALTER TABLE products CONFIGURE ZONE USING
    num_replicas = 5,
    constraints = '{+region=us-east: 1, +region=us-west: 1, +region=eu-west: 1, +region=ap-south: 1, +region=ap-north: 1}',
    lease_preferences = '[[+region=us-east], [+region=eu-west], [+region=ap-south]]';

-- Query with region awareness
-- This query will be fast if executed in us-east
SELECT * FROM regional_orders
WHERE region = 'us-east'
ORDER BY created_at DESC
LIMIT 100;

-- TiDB is MySQL compatible
-- Connect: mysql -h 127.0.0.1 -P 4000 -u root

CREATE DATABASE ecommerce;
USE ecommerce;

-- Create table with TiDB-specific features
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_RANDOM,
    customer_id BIGINT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_customer (customer_id),
    INDEX idx_status (status)
);

-- AUTO_RANDOM for distributed key generation
-- Avoids hot spots compared to AUTO_INCREMENT

-- Partitioning by range (time-based)
CREATE TABLE events (
    id BIGINT,
    event_type VARCHAR(50),
    data JSON,
    created_at DATETIME,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
    PARTITION p2024_01 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01')),
    PARTITION p2024_02 VALUES LESS THAN (UNIX_TIMESTAMP('2024-03-01')),
    PARTITION p2024_03 VALUES LESS THAN (UNIX_TIMESTAMP('2024-04-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- TiFlash for analytical queries
-- Replicate table to columnar storage
ALTER TABLE orders SET TIFLASH REPLICA 1;

-- Hybrid query (automatically uses TiFlash for analytics)
SELECT 
    DATE(created_at) as order_date,
    COUNT(*) as order_count,
    SUM(total) as revenue
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY DATE(created_at)
ORDER BY order_date;

-- Check if query used TiFlash
EXPLAIN ANALYZE SELECT ...;

-- Clustered index (TiDB 5.0+)
CREATE TABLE users (
    id BIGINT PRIMARY KEY CLUSTERED,
    email VARCHAR(255) UNIQUE,
    name VARCHAR(100)
);

-- Placement rules for multi-region
CREATE PLACEMENT POLICY policy_us_west
    LEADER_CONSTRAINTS="[+region=us-west]"
    FOLLOWER_CONSTRAINTS="[+region=us-west]"
    FOLLOWERS=2;

ALTER TABLE orders PLACEMENT POLICY = policy_us_west;

// ArangoDB AQL examples

// Create collections
db._create("users");
db._create("products");
db._createEdgeCollection("purchases");
db._createEdgeCollection("follows");

// Insert documents
db.users.save({ _key: "alice", name: "Alice", email: "alice@example.com" });
db.users.save({ _key: "bob", name: "Bob", email: "bob@example.com" });
db.products.save({ _key: "laptop", name: "Pro Laptop", price: 1299.99 });
db.products.save({ _key: "headphones", name: "Wireless Headphones", price: 199.99 });

// Create edges (graph relationships)
db.purchases.save({ _from: "users/alice", _to: "products/laptop", date: "2024-01-15", quantity: 1 });
db.purchases.save({ _from: "users/alice", _to: "products/headphones", date: "2024-01-16", quantity: 2 });
db.purchases.save({ _from: "users/bob", _to: "products/headphones", date: "2024-01-17", quantity: 1 });
db.follows.save({ _from: "users/alice", _to: "users/bob" });

// Document query
FOR user IN users
    FILTER user.name == "Alice"
    RETURN user

// Join query (document style)
FOR user IN users
    FOR purchase IN purchases
        FILTER purchase._from == user._id
        FOR product IN products
            FILTER product._id == purchase._to
            RETURN {
                user: user.name,
                product: product.name,
                quantity: purchase.quantity,
                total: purchase.quantity * product.price
            }

// Graph traversal
FOR vertex, edge, path IN 1..2 OUTBOUND "users/alice" purchases, follows
    RETURN { vertex, edge, path }

// Recommendation: What did friends buy that I haven't?
FOR friend IN 1..1 OUTBOUND "users/alice" follows
    FOR product IN 1..1 OUTBOUND friend purchases
        LET alicePurchased = (
            FOR p IN 1..1 OUTBOUND "users/alice" purchases
                RETURN p._id
        )
        FILTER product._id NOT IN alicePurchased
        COLLECT prod = product WITH COUNT INTO purchaseCount
        RETURN { product: prod.name, recommendedBy: purchaseCount }

// Aggregations
FOR purchase IN purchases
    COLLECT product = DOCUMENT(purchase._to).name
    AGGREGATE total = SUM(purchase.quantity)
    RETURN { product, total_sold: total }

// Full-text search (if index exists)
FOR product IN products
    FILTER ANALYZER(PHRASE(product.name, "wireless"), "text_en")
    RETURN product

// Geospatial (if geo index exists)
FOR store IN stores
    FILTER GEO_DISTANCE([store.location.lng, store.location.lat], [-73.935242, 40.730610]) < 5000
    RETURN store

// Fauna Query Language examples

// Create a collection
CreateCollection({ name: "users" })
CreateCollection({ name: "orders" })

// Create indexes
CreateIndex({
  name: "users_by_email",
  source: Collection("users"),
  terms: [{ field: ["data", "email"] }],
  unique: true
})

CreateIndex({
  name: "orders_by_user",
  source: Collection("orders"),
  terms: [{ field: ["data", "user_ref"] }]
})

// Create a document
Create(Collection("users"), {
  data: {
    email: "alice@example.com",
    name: "Alice Smith",
    created_at: Now()
  }
})

// Read document
Get(Ref(Collection("users"), "user_id"))

// Query by index
Match(Index("users_by_email"), "alice@example.com")

// Paginated query
Paginate(Documents(Collection("users")), { size: 10 })

// Join-like query
Map(
  Paginate(Match(Index("orders_by_user"), Ref(Collection("users"), "user_id"))),
  Lambda("ref", Get(Var("ref")))
)

// Transaction (automatic ACID)
Do(
  Create(Collection("orders"), {
    data: {
      user_ref: Ref(Collection("users"), "user_id"),
      total: 199.99,
      status: "pending"
    }
  }),
  Update(Ref(Collection("users"), "user_id"), {
    data: {
      order_count: Add(Select(["data", "order_count"], Get(Ref(Collection("users"), "user_id")), 0), 1)
    }
  })
)

// UDF (User Defined Function)
CreateFunction({
  name: "create_order_with_items",
  body: Query(
    Lambda(
      ["user_id", "items", "total"],
      Let(
        {
          user: Get(Ref(Collection("users"), Var("user_id"))),
          order: Create(Collection("orders"), {
            data: {
              user_ref: Select("ref", Var("user")),
              total: Var("total"),
              items: Var("items"),
              status: "pending",
              created_at: Now()
            }
          })
        },
        Var("order")
      )
    )
  )
})

// Call UDF
Call(Function("create_order_with_items"), [
  "user_id",
  [{ product: "Widget", quantity: 2 }],
  59.98
])

-- Create database
CREATE DATABASE global_ecommerce;
USE global_ecommerce;

-- Global products table (read from anywhere, write to primary)
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sku STRING UNIQUE NOT NULL,
    name STRING NOT NULL,
    description STRING,
    base_price DECIMAL(10,2) NOT NULL
);

-- Regional inventory (each region manages its own)
CREATE TABLE inventory (
    product_id UUID REFERENCES products(id),
    region STRING NOT NULL,
    quantity INT NOT NULL DEFAULT 0,
    reserved INT NOT NULL DEFAULT 0,
    PRIMARY KEY (region, product_id)
);

-- Regional orders
CREATE TABLE orders (
    id UUID DEFAULT gen_random_uuid(),
    customer_id UUID NOT NULL,
    region STRING NOT NULL,
    status STRING DEFAULT 'pending',
    total DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT now(),
    PRIMARY KEY (region, id)
);

CREATE TABLE order_items (
    order_id UUID NOT NULL,
    order_region STRING NOT NULL,
    product_id UUID REFERENCES products(id),
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_region, order_id) REFERENCES orders(region, id),
    PRIMARY KEY (order_region, order_id, product_id)
);

-- Create order with inventory check (transaction)
BEGIN;
-- Check inventory
SELECT quantity - reserved as available
FROM inventory
WHERE product_id = 'product-uuid' AND region = 'us-east'
FOR UPDATE;

-- Reserve inventory
UPDATE inventory
SET reserved = reserved + 1
WHERE product_id = 'product-uuid' AND region = 'us-east';

-- Create order
INSERT INTO orders (customer_id, region, total)
VALUES ('customer-uuid', 'us-east', 99.99)
RETURNING id;

-- Add items
INSERT INTO order_items (order_id, order_region, product_id, quantity, unit_price)
VALUES ('order-uuid', 'us-east', 'product-uuid', 1, 99.99);

COMMIT;

-- Regional sales report (fast, local data)
SELECT 
    DATE_TRUNC('day', created_at) as day,
    COUNT(*) as order_count,
    SUM(total) as revenue
FROM orders
WHERE region = 'us-east'
    AND created_at >= now() - INTERVAL '30 days'
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY day DESC;

-- Global sales report (cross-region, may be slower)
SELECT 
    region,
    COUNT(*) as order_count,
    SUM(total) as revenue
FROM orders
WHERE created_at >= now() - INTERVAL '7 days'
GROUP BY region
ORDER BY revenue DESC;

// Create collections
db._create("users");
db._create("posts");
db._create("comments");
db._createEdgeCollection("follows");
db._createEdgeCollection("likes");
db._createEdgeCollection("authored");

// Create indexes
db.users.ensureIndex({ type: "hash", fields: ["username"], unique: true });
db.posts.ensureIndex({ type: "skiplist", fields: ["created_at"] });

// Create user
db.users.save({
    _key: "alice",
    username: "alice",
    name: "Alice Smith",
    bio: "Developer and coffee enthusiast",
    follower_count: 0,
    following_count: 0,
    created_at: new Date()
});

// Create post with relationship
let post = db.posts.save({
    content: "Just launched my new project!",
    media: [],
    like_count: 0,
    comment_count: 0,
    created_at: new Date()
});
db.authored.save({ _from: "users/alice", _to: post._id });

// Follow user
db._query(`
    LET follower = DOCUMENT("users/bob")
    LET following = DOCUMENT("users/alice")
    INSERT { _from: follower._id, _to: following._id, created_at: DATE_NOW() } INTO follows
    UPDATE follower WITH { following_count: follower.following_count + 1 } IN users
    UPDATE following WITH { follower_count: following.follower_count + 1 } IN users
`);

// Get user feed (posts from followed users)
db._query(`
    FOR followed IN 1..1 OUTBOUND "users/bob" follows
        FOR post IN 1..1 OUTBOUND followed authored
            SORT post.created_at DESC
            LIMIT 20
            LET author = FIRST(
                FOR u IN 1..1 INBOUND post authored
                    RETURN { username: u.username, name: u.name }
            )
            RETURN MERGE(post, { author: author })
`);

// Get post with engagement
db._query(`
    LET post = DOCUMENT("posts/post_id")
    LET author = FIRST(
        FOR u IN 1..1 INBOUND post authored
            RETURN { username: u.username, name: u.name }
    )
    LET comments = (
        FOR c IN comments
            FILTER c.post_id == post._id
            SORT c.created_at DESC
            LIMIT 10
            RETURN c
    )
    LET liked_by_me = LENGTH(
        FOR like IN likes
            FILTER like._from == "users/bob" AND like._to == post._id
            LIMIT 1
            RETURN 1
    ) > 0
    RETURN MERGE(post, {
        author: author,
        comments: comments,
        liked_by_me: liked_by_me
    })
`);

// Find mutual followers (graph query)
db._query(`
    LET user1_following = (FOR f IN 1..1 OUTBOUND "users/alice" follows RETURN f._id)
    LET user2_following = (FOR f IN 1..1 OUTBOUND "users/bob" follows RETURN f._id)
    FOR user_id IN INTERSECTION(user1_following, user2_following)
        RETURN DOCUMENT(user_id)
`);

// Suggested follows (friends of friends not already followed)
db._query(`
    FOR friend IN 1..1 OUTBOUND "users/alice" follows
        FOR suggested IN 1..1 OUTBOUND friend follows
            FILTER suggested._id != "users/alice"
            LET already_following = LENGTH(
                FOR f IN follows
                    FILTER f._from == "users/alice" AND f._to == suggested._id
                    LIMIT 1
                    RETURN 1
            ) > 0
            FILTER !already_following
            COLLECT user = suggested WITH COUNT INTO mutual_count
            SORT mutual_count DESC
            LIMIT 10
            RETURN { user: user, mutual_friends: mutual_count }
`);