run.php

#!/usr/bin/php
<?php
/**
 * PhpSqlArrays Demo - Query arrays, CSV, and JSON using SQL
 */

require_once __DIR__ . '/phpsqlarrays.php';

echo "=== PhpSqlArrays Demo ===\n\n";

// ============================================
// Example 1: In-memory (no cache) with arrays
// ============================================
echo "--- Example 1: In-memory array tables ---\n";

$memoryDAL = new phpsqlarrays(null, false); // No caching, pure memory

// Create a table from an array
$states = [
    ['id' => 1, 'abbr' => 'NY', 'name' => 'New York', 'population' => 19453561],
    ['id' => 2, 'abbr' => 'CA', 'name' => 'California', 'population' => 39538223],
    ['id' => 3, 'abbr' => 'TX', 'name' => 'Texas', 'population' => 29145505],
    ['id' => 4, 'abbr' => 'FL', 'name' => 'Florida', 'population' => 21538187],
];

$memoryDAL->fromArray('states', $states);

// Query like you would with any database
$result = $memoryDAL->r("SELECT * FROM states WHERE population > ?", [20000000]);
echo "States with population > 20M:\n";
foreach ($result as $row) {
    echo "  - {$row['name']} ({$row['abbr']}): " . number_format($row['population']) . "\n";
}

// Use helper methods
$california = $memoryDAL->find('states', 2);
echo "\nFind by ID 2: {$california['name']}\n";

$texas = $memoryDAL->first('states', 'abbr', 'TX');
echo "First where abbr='TX': {$texas['name']}\n";

echo "Total states count: " . $memoryDAL->count('states') . "\n\n";

// ============================================
// Example 2: Cached SQLite with CSV file
// ============================================
echo "--- Example 2: CSV file support ---\n";

// Create a sample CSV file
$csvContent = "id,code,country,region
1,US,United States,North America
2,CA,Canada,North America
3,MX,Mexico,North America
4,GB,United Kingdom,Europe
5,DE,Germany,Europe
6,FR,France,Europe
7,JP,Japan,Asia
8,CN,China,Asia
9,AU,Australia,Oceania
";
file_put_contents(__DIR__ . '/countries.csv', $csvContent);

// Create cached DAL (will store .sqlite file in cache directory)
$cachedDAL = new phpsqlarrays(__DIR__ . '/cache', true);
$cachedDAL->fromCSV('countries', __DIR__ . '/countries.csv');

// Query the CSV data via SQL
$europeanCountries = $cachedDAL->r("SELECT * FROM countries WHERE region = ?", ['Europe']);
echo "European countries:\n";
foreach ($europeanCountries as $country) {
    echo "  - {$country['country']} ({$country['code']})\n";
}

// Count by region
$regions = $cachedDAL->r("SELECT region, COUNT(*) as count FROM countries GROUP BY region ORDER BY count DESC");
echo "\nCountries by region:\n";
foreach ($regions as $region) {
    echo "  - {$region['region']}: {$region['count']}\n";
}

echo "\n";

// ============================================
// Example 3: JSON file support
// ============================================
echo "--- Example 3: JSON file support ---\n";

// Create a sample JSON file
$jsonData = [
    'metadata' => ['version' => '1.0'],
    'products' => [
        ['id' => 1, 'name' => 'Laptop', 'price' => 999.99, 'category' => 'Electronics'],
        ['id' => 2, 'name' => 'Desk Chair', 'price' => 249.99, 'category' => 'Furniture'],
        ['id' => 3, 'name' => 'Coffee Mug', 'price' => 12.99, 'category' => 'Kitchen'],
        ['id' => 4, 'name' => 'Smartphone', 'price' => 699.99, 'category' => 'Electronics'],
        ['id' => 5, 'name' => 'Bookshelf', 'price' => 149.99, 'category' => 'Furniture'],
    ]
];
file_put_contents(__DIR__ . '/products.json', json_encode($jsonData, JSON_PRETTY_PRINT));

// Load JSON with path to nested array
$cachedDAL->fromJSON('products', __DIR__ . '/products.json', null, 'products');

// Query products
$electronics = $cachedDAL->r("SELECT * FROM products WHERE category = ? ORDER BY price DESC", ['Electronics']);
echo "Electronics (sorted by price):\n";
foreach ($electronics as $product) {
    echo "  - {$product['name']}: $" . number_format($product['price'], 2) . "\n";
}

// Aggregate queries
$categoryStats = $cachedDAL->r("
    SELECT category,
           COUNT(*) as count,
           AVG(price) as avg_price,
           MIN(price) as min_price,
           MAX(price) as max_price
    FROM products
    GROUP BY category
");
echo "\nCategory statistics:\n";
foreach ($categoryStats as $stat) {
    echo "  {$stat['category']}: {$stat['count']} items, avg $" . number_format($stat['avg_price'], 2) . "\n";
}

echo "\n";

// ============================================
// Example 4: Custom schema definition
// ============================================
echo "--- Example 4: Custom schema ---\n";

$roles = [
    ['id' => 'admin', 'label' => 'Administrator', 'level' => 100],
    ['id' => 'editor', 'label' => 'Editor', 'level' => 50],
    ['id' => 'viewer', 'label' => 'Viewer', 'level' => 10],
];

// Define schema explicitly (useful when types can't be auto-detected)
$schema = [
    'id' => 'string',
    'label' => 'string',
    'level' => 'integer'
];

$memoryDAL->fromArray('roles', $roles, $schema);

$admins = $memoryDAL->where('roles', 'level', 100);
echo "High-level roles (level=100):\n";
foreach ($admins as $role) {
    echo "  - {$role['id']}: {$role['label']}\n";
}

// ============================================
// Example 5: Multiple tables with JOIN
// ============================================
echo "\n--- Example 5: JOIN across tables ---\n";

$users = [
    ['id' => 1, 'name' => 'Alice', 'role_id' => 'admin', 'state_id' => 1],
    ['id' => 2, 'name' => 'Bob', 'role_id' => 'editor', 'state_id' => 2],
    ['id' => 3, 'name' => 'Charlie', 'role_id' => 'viewer', 'state_id' => 1],
];

$memoryDAL->fromArray('users', $users);

// Join users with roles
$userRoles = $memoryDAL->r("
    SELECT u.name, r.label as role, s.name as state
    FROM users u
    JOIN roles r ON u.role_id = r.id
    JOIN states s ON u.state_id = s.id
    ORDER BY r.level DESC
");
echo "Users with their roles and states:\n";
foreach ($userRoles as $user) {
    echo "  - {$user['name']}: {$user['role']} from {$user['state']}\n";
}

// ============================================
// Example 6: Direct SQL write operations
// ============================================
echo "\n--- Example 6: Write operations ---\n";

// Insert a new row
$memoryDAL->w("INSERT INTO states (id, abbr, name, population) VALUES (?, ?, ?, ?)",
    [5, 'PA', 'Pennsylvania', 13002700]);
echo "Inserted Pennsylvania\n";

// Update a row
$memoryDAL->w("UPDATE states SET population = ? WHERE abbr = ?", [39600000, 'CA']);
echo "Updated California's population\n";

// Verify
$updatedCA = $memoryDAL->first('states', 'abbr', 'CA');
echo "California's new population: " . number_format($updatedCA['population']) . "\n";

echo "States count after insert: " . $memoryDAL->count('states') . "\n";

// ============================================
// Utility information
// ============================================
echo "\n--- Utility Methods ---\n";
echo "Registered tables in memory DAL: " . implode(', ', $memoryDAL->getTables()) . "\n";
echo "Registered tables in cached DAL: " . implode(', ', $cachedDAL->getTables()) . "\n";

// Get schema info
echo "\nSchema for 'states' table:\n";
$statesSchema = $memoryDAL->getSchema('states');
foreach ($statesSchema as $col) {
    echo "  - {$col['name']} ({$col['type']})\n";
}

// Cleanup demo files
unlink(__DIR__ . '/countries.csv');
unlink(__DIR__ . '/products.json');
// Note: cache directory and .sqlite file are kept for demonstration

echo "\n=== Demo Complete ===\n";