alawymdb 0.2.9

Linear-scaling in-memory database optimized for ML workloads

AlawymDB

Almost Linear Any Way You Measure - A high-performance in-memory database that achieves near-linear O(n) scaling for operations that traditionally suffer from O(n log n) complexity.

🚀 Breakthrough Performance

AlawymDB (pronounced "ah-LAY-wim") lives up to its name - delivering almost linear performance any way you measure it:

Column Scaling Performance

Columns: 10 → 100 → 1000 → 2000
Cells/sec: 18.5M → 7.5M → 5.8M → 5.2M
Scaling: 1× → 10× → 100× → 200× (columns)
         1× → 4.1× → 5.3× → 6.1× (time)

Result: O(n) with minimal logarithmic factor - effectively linear! 🎯

🔧 Installation

pip install alawymdb

💡 Quick Start

import alawymdb as db

# Initialize database
db.create_database()

# Create schema and table
db.create_schema("main")
db.create_table(
    "main", 
    "users",
    [
        ("id", "UINT64", False),
        ("name", "STRING", False),
        ("age", "INT64", True),
        ("email", "STRING", True),
        ("score", "FLOAT64", True)
    ]
)

# Insert data
users = [
    (1, "Alice", 30, "[e-mail]", 95.5),
    (2, "Bob", 25, "[e-mail]", 87.3),
    (3, "Charlie", 35, "[e-mail]", 92.1),
]

for user_id, name, age, email, score in users:
    db.insert_row("main", "users", [
        ("id", user_id),
        ("name", name),
        ("age", age),
        ("email", email),
        ("score", score)
    ])

# Query with SQL
result = db.execute_sql("SELECT * FROM main.users")
print(result)

# SQL with WHERE clause
young_users = db.execute_sql("SELECT * FROM main.users WHERE age = 25")
print(f"Young users:\n{young_users}")

# Direct API queries
all_users = db.select_all("main", "users")
print(f"Total users: {db.count_rows('main', 'users')}")

📊 E-Commerce Analytics Example

A comprehensive example demonstrating SQL aggregations, JOINs, and set operations:

import alawymdb as db
import time

def setup_ecommerce_database():
    """Create an e-commerce database with products, customers, and sales data"""
    print("🏪 Setting up E-commerce Database...")
    
    # Initialize database
    db.create_database()
    db.create_schema("ecommerce")
    
    # Create products table
    db.create_table(
        "ecommerce", 
        "products",
        [
            ("product_id", "UINT64", False),
            ("name", "STRING", False),
            ("category", "STRING", False),
            ("price", "FLOAT64", False),
            ("stock", "INT64", False),
        ]
    )
    
    # Create customers table
    db.create_table(
        "ecommerce",
        "customers",
        [
            ("customer_id", "UINT64", False),
            ("name", "STRING", False),
            ("email", "STRING", False),
            ("city", "STRING", False),
            ("loyalty_points", "INT64", False),
        ]
    )
    
    # Create sales table
    db.create_table(
        "ecommerce",
        "sales",
        [
            ("sale_id", "UINT64", False),
            ("customer_id", "UINT64", False),
            ("product_id", "UINT64", False),
            ("quantity", "INT64", False),
            ("sale_amount", "FLOAT64", False),
        ]
    )
    
    # Insert sample products
    products = [
        (1, "Laptop Pro", "Electronics", 1299.99, 50),
        (2, "Wireless Mouse", "Electronics", 29.99, 200),
        (3, "USB-C Hub", "Electronics", 49.99, 150),
        (4, "Coffee Maker", "Appliances", 89.99, 75),
        (5, "Desk Lamp", "Furniture", 39.99, 120),
    ]
    
    for prod in products:
        db.insert_row("ecommerce", "products", [
            ("product_id", prod[0]),
            ("name", prod[1]),
            ("category", prod[2]),
            ("price", prod[3]),
            ("stock", prod[4]),
        ])
    
    # Insert sample customers
    customers = [
        (1, "Alice Johnson", "[e-mail]", "New York", 1500),
        (2, "Bob Smith", "[e-mail]", "Los Angeles", 800),
        (3, "Charlie Brown", "[e-mail]", "Chicago", 2000),
    ]
    
    for cust in customers:
        db.insert_row("ecommerce", "customers", [
            ("customer_id", cust[0]),
            ("name", cust[1]),
            ("email", cust[2]),
            ("city", cust[3]),
            ("loyalty_points", cust[4]),
        ])
    
    # Insert sample sales
    sales = [
        (1, 1, 1, 1, 1299.99),  # Alice bought a laptop
        (2, 1, 2, 2, 59.98),    # Alice bought 2 mice
        (3, 2, 3, 1, 49.99),    # Bob bought a USB hub
        (4, 3, 4, 1, 89.99),    # Charlie bought a coffee maker
    ]
    
    for sale in sales:
        db.insert_row("ecommerce", "sales", [
            ("sale_id", sale[0]),
            ("customer_id", sale[1]),
            ("product_id", sale[2]),
            ("quantity", sale[3]),
            ("sale_amount", sale[4]),
        ])
    
    print(f"✅ Database created with {db.count_rows('ecommerce', 'products')} products, "
          f"{db.count_rows('ecommerce', 'customers')} customers, "
          f"{db.count_rows('ecommerce', 'sales')} sales")

def run_analytics():
    """Demonstrate analytics queries with aggregations and JOINs"""
    
    # 1. Aggregation Functions
    print("\n📊 Aggregation Examples:")
    print("-" * 40)
    
    # COUNT
    result = db.execute_sql("SELECT COUNT(*) FROM ecommerce.sales")
    print(f"Total sales: {result}")
    
    # SUM
    result = db.execute_sql("SELECT SUM(sale_amount) FROM ecommerce.sales")
    print(f"Total revenue: {result}")
    
    # AVG
    result = db.execute_sql("SELECT AVG(price) FROM ecommerce.products")
    print(f"Average product price: {result}")
    
    # MIN/MAX
    result = db.execute_sql("SELECT MIN(price), MAX(price) FROM ecommerce.products")
    print(f"Price range: {result}")
    
    # 2. JOIN Operations
    print("\n🔗 JOIN Examples:")
    print("-" * 40)
    
    # Customer purchases with product details
    sql = """
    SELECT 
        c.name,
        p.name,
        s.quantity,
        s.sale_amount
    FROM ecommerce.sales s
    INNER JOIN ecommerce.customers c ON s.customer_id = c.customer_id
    INNER JOIN ecommerce.products p ON s.product_id = p.product_id
    """
    result = db.execute_sql(sql)
    print("Customer Purchase Details:")
    print(result)
    
    # 3. GROUP BY with aggregations
    print("\n📈 GROUP BY Examples:")
    print("-" * 40)
    
    sql = """
    SELECT 
        c.name,
        COUNT(s.sale_id),
        SUM(s.sale_amount)
    FROM ecommerce.customers c
    INNER JOIN ecommerce.sales s ON c.customer_id = c.customer_id
    GROUP BY c.customer_id, c.name
    """
    try:
        result = db.execute_sql(sql)
        print("Sales by Customer:")
        print(result)
    except:
        # Fallback if GROUP BY not fully supported
        print("GROUP BY example - showing individual sales instead")
        sql_alt = """
        SELECT c.name, s.sale_amount
        FROM ecommerce.customers c
        INNER JOIN ecommerce.sales s ON c.customer_id = c.customer_id
        """
        print(db.execute_sql(sql_alt))
    
    # 4. Set Operations
    print("\n🔄 Set Operation Examples:")
    print("-" * 40)
    
    # UNION - High-value items (products over $50 OR customers with 1000+ points)
    sql = """
    SELECT name FROM ecommerce.products WHERE price > 50
    UNION
    SELECT name FROM ecommerce.customers WHERE loyalty_points > 1000
    """
    try:
        result = db.execute_sql(sql)
        print("High-value items and VIP customers:")
        print(result)
    except:
        print("UNION example - showing separately:")
        print("Premium products:", db.execute_sql("SELECT name FROM ecommerce.products WHERE price > 50"))
        print("VIP customers:", db.execute_sql("SELECT name FROM ecommerce.customers WHERE loyalty_points > 1000"))

# Run the example
setup_ecommerce_database()
run_analytics()

🎯 Working Example: Toy JOIN Operations

import alawymdb as db
import time

def setup_toy_database():
    """Create toy database with customers and orders tables"""
    print("🔧 Setting up toy database...")
    
    db.create_database()
    db.create_schema("toy")
    
    # Create customers table
    db.create_table(
        "toy", 
        "customers",
        [
            ("customer_id", "UINT64", False),
            ("name", "STRING", False),
            ("country", "STRING", False),
            ("join_date", "STRING", False),
        ]
    )
    
    # Create orders table
    db.create_table(
        "toy",
        "orders",
        [
            ("order_id", "UINT64", False),
            ("customer_id", "UINT64", False),
            ("product", "STRING", False),
            ("amount", "FLOAT64", False),
            ("order_date", "STRING", False),
        ]
    )
    
    # Insert customers
    customers = [
        (1, "Alice", "USA", "2023-01-15"),
        (2, "Bob", "Canada", "2023-02-20"),
        (3, "Charlie", "UK", "2023-03-10"),
        (4, "Diana", "Germany", "2023-04-05"),
        (5, "Eve", "France", "2023-05-12"),
    ]
    
    for cust in customers:
        db.insert_row("toy", "customers", [
            ("customer_id", cust[0]),
            ("name", cust[1]),
            ("country", cust[2]),
            ("join_date", cust[3]),
        ])
    
    # Insert orders (some customers have multiple orders, some have none)
    orders = [
        (101, 1, "Laptop", 1200.0, "2024-01-10"),
        (102, 1, "Mouse", 25.0, "2024-01-15"),
        (103, 2, "Keyboard", 75.0, "2024-01-20"),
        (104, 3, "Monitor", 350.0, "2024-02-01"),
        (105, 1, "Headphones", 150.0, "2024-02-15"),
        (106, 3, "Webcam", 80.0, "2024-03-01"),
        (107, 2, "USB Drive", 30.0, "2024-03-10"),
        # Note: Diana (4) and Eve (5) have no orders
    ]
    
    for order in orders:
        db.insert_row("toy", "orders", [
            ("order_id", order[0]),
            ("customer_id", order[1]),
            ("product", order[2]),
            ("amount", order[3]),
            ("order_date", order[4]),
        ])
    
    print("✅ Toy database created successfully!")
    print(f"   - Customers: {db.count_rows('toy', 'customers')}")
    print(f"   - Orders: {db.count_rows('toy', 'orders')}")

def demonstrate_joins():
    """Demonstrate various JOIN operations"""
    
    print("\n" + "="*80)
    print("JOIN DEMONSTRATIONS")
    print("="*80)
    
    # 1. INNER JOIN
    print("\n1️⃣ INNER JOIN - Customers with their orders")
    print("-" * 60)
    sql = """
    SELECT 
        c.name,
        c.country,
        o.product,
        o.amount
    FROM toy.customers c
    INNER JOIN toy.orders o ON c.customer_id = o.customer_id
    ORDER BY c.name, o.amount DESC
    """
    result = db.execute_sql(sql)
    print(result)
    
    # 2. LEFT JOIN
    print("\n2️⃣ LEFT JOIN - All customers, including those without orders")
    print("-" * 60)
    sql = """
    SELECT 
        c.name,
        c.country,
        o.order_id,
        o.product,
        o.amount
    FROM toy.customers c
    LEFT JOIN toy.orders o ON c.customer_id = o.customer_id
    ORDER BY c.name
    """
    result = db.execute_sql(sql)
    print(result)
    
    # 3. RIGHT JOIN
    print("\n3️⃣ RIGHT JOIN - All orders with customer details")
    print("-" * 60)
    sql = """
    SELECT 
        c.name,
        c.country,
        o.order_id,
        o.product,
        o.amount
    FROM toy.customers c
    RIGHT JOIN toy.orders o ON c.customer_id = o.customer_id
    ORDER BY o.order_id
    """
    result = db.execute_sql(sql)
    print(result)
    
    # 4. CROSS JOIN (Cartesian product)
    print("\n4️⃣ CROSS JOIN - Every customer with every order (Cartesian product)")
    print("-" * 60)
    sql = """
    SELECT 
        c.name,
        o.product
    FROM toy.customers c
    CROSS JOIN toy.orders o
    """
    result = db.execute_sql(sql)
    print(f"Total combinations: {result.count('Product_')} rows")
    print("(Showing first few rows only...)")
    # Show just first few lines
    lines = result.split('\n')[:10]
    print('\n'.join(lines))

def main():
    """Run the toy JOIN example"""
    print("🚀 AlawymDB Toy JOIN Example")
    print("="*80)
    
    setup_toy_database()
    demonstrate_joins()
    
    print("\n" + "="*80)
    print("✅ Toy JOIN demonstration complete!")

if __name__ == "__main__":
    main()

🎯 Working Example: Analytics with Pandas Integration

import alawymdb as db
import numpy as np
import pandas as pd

# Setup
db.create_database()
db.create_schema("test_schema")

# Create employees table
db.create_table(
    "test_schema",
    "employees",
    [
        ("id", "UINT64", False),
        ("name", "STRING", False),
        ("age", "UINT64", True),
        ("salary", "FLOAT64", True),
        ("department", "STRING", True)
    ]
)

# Insert test data
employees = [
    (1, "Alice Johnson", 28, 75000.0, "Engineering"),
    (2, "Bob Smith", 35, 85000.0, "Sales"),
    (3, "Charlie Brown", 42, 95000.0, "Engineering"),
    (4, "Diana Prince", 31, 78000.0, "Marketing"),
    (5, "Eve Adams", 26, 72000.0, "Sales"),
]

for emp in employees:
    db.insert_row("test_schema", "employees", [
        ("id", emp[0]),
        ("name", emp[1]),
        ("age", emp[2]),
        ("salary", emp[3]),
        ("department", emp[4])
    ])

# Convert to Pandas DataFrame
df = db.to_pandas("test_schema", "employees")
print("DataFrame shape:", df.shape)
print("\nDataFrame head:")
print(df.head())

# Pandas operations
print(f"\nAverage salary: ${df['salary'].mean():,.2f}")
print("\nSalary by department:")
print(df.groupby('department')['salary'].agg(['mean', 'count']))

# Get as NumPy array
ages = db.to_numpy("test_schema", "employees", "age")
print(f"\nAges array: {ages}")
print(f"Mean age: {np.mean(ages):.1f}")

# Get data as dictionary
data_dict = db.select_as_dict("test_schema", "employees")
print(f"\nColumns available: {list(data_dict.keys())}")

📊 Create Table from Pandas DataFrame

import alawymdb as db
import pandas as pd
import numpy as np

db.create_database()
db.create_schema("data")

# Create a DataFrame
df = pd.DataFrame({
    'product_id': np.arange(1, 101),
    'product_name': [f'Product_{i}' for i in range(1, 101)],
    'price': np.random.uniform(10, 100, 100).round(2),
    'quantity': np.random.randint(1, 100, 100),
    'in_stock': np.random.choice([0, 1], 100)  # Use 0/1 instead of True/False
})

# Import DataFrame to AlawymDB
result = db.from_pandas(df, "data", "products")
print(result)

# Verify by reading back
df_verify = db.to_pandas("data", "products")
print(f"Imported {len(df_verify)} rows with {len(df_verify.columns)} columns")
print(df_verify.head())

# Query the imported data
result = db.execute_sql("SELECT * FROM data.products WHERE price > 50.0")
print(f"Products with price > 50: {result}")

📈 Wide Table Example (Working Version)

import alawymdb as db

db.create_database()
db.create_schema("wide")

# Create table with many columns
num_columns = 100
columns = [("id", "UINT64", False)]
columns += [(f"metric_{i}", "FLOAT64", True) for i in range(num_columns)]

db.create_table("wide", "metrics", columns)

# Insert data
for row_id in range(100):
    values = [("id", row_id)]
    values += [(f"metric_{i}", float(row_id * 0.1 + i)) for i in range(num_columns)]
    db.insert_row("wide", "metrics", values)

# Query using direct API (more reliable for wide tables)
all_data = db.select_all("wide", "metrics")
print(f"Inserted {len(all_data)} rows")

# Convert to Pandas for analysis
df = db.to_pandas("wide", "metrics")
print(f"DataFrame shape: {df.shape}")
print(f"Columns: {df.columns[:5].tolist()} ... {df.columns[-5:].tolist()}")

# Get specific column as NumPy array
metric_0 = db.to_numpy("wide", "metrics", "metric_0")
print(f"Metric_0 stats: mean={metric_0.mean():.2f}, std={metric_0.std():.2f}")

🚀 Performance Test

import alawymdb as db
import pandas as pd
import numpy as np
import time

db.create_database()
db.create_schema("perf")

# Create a large DataFrame
n_rows = 10000
df_large = pd.DataFrame({
    'id': np.arange(n_rows),
    'value1': np.random.randn(n_rows),
    'value2': np.random.randn(n_rows) * 100,
    'category': np.random.choice(['A', 'B', 'C', 'D', 'E'], n_rows),
    'flag': np.random.choice([0, 1], n_rows)
})

# Time the import
start = time.time()
db.from_pandas(df_large, "perf", "large_table")
import_time = time.time() - start
print(f"Import {n_rows} rows: {import_time:.3f}s ({n_rows/import_time:.0f} rows/sec)")

# Time the export
start = time.time()
df_export = db.to_pandas("perf", "large_table")
export_time = time.time() - start
print(f"Export to Pandas: {export_time:.3f}s ({n_rows/export_time:.0f} rows/sec)")

# Verify
print(f"Shape verification: {df_export.shape}")

🏗️ Why "Almost Linear Any Way You Measure"?

The name AlawymDB reflects our core achievement:

• Column scaling: O(n) with tiny logarithmic factor (log₂₅₆)
• Row scaling: Pure O(n) for scans
• Memory usage: Linear with data size
• Wide tables: Tested up to 5000 columns with maintained performance

📊 Current SQL Support

✅ Working SQL Features

• SELECT * FROM table
• SELECT column1, column2 FROM table
• SELECT * FROM table WHERE column = value
• SELECT * FROM table WHERE column > value
• Aggregation Functions: COUNT(), SUM(), AVG(), MIN(), MAX()
• JOIN Operations: INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN
• Set Operations: UNION, INTERSECT, EXCEPT
• GROUP BY with aggregations
• ORDER BY with ASC/DESC
• **LIMIT` clause
• Subqueries with IN operator

⚠️ SQL Limitations

• Type matching is strict (use 50.0 for FLOAT64, 50 for INT64)
• Column aliases (AS) not yet supported
• ORDER BY is not supported by design at the moment
• CASE statements coming soon
• String does not guarantee 100% precision (internally treated as u64)

🎨 API Reference

# Core operations
db.create_database()
db.create_schema(schema_name)
db.create_table(schema, table, columns)
db.insert_row(schema, table, values)

# Query operations
db.select_all(schema, table)
db.select_where(schema, table, columns, where_col, where_val)
db.count_rows(schema, table)
db.execute_sql(sql_query)  # Full SQL support including JOINs, aggregations, set operations

# Data science integrations
db.to_pandas(schema, table)           # Export to DataFrame
db.to_numpy(schema, table, column)    # Export column to NumPy
db.from_pandas(df, schema, table)     # Import from DataFrame
db.select_as_dict(schema, table)      # Get as Python dict

🚦 Performance Characteristics

Operation	Complexity	Verified Scale
INSERT	O(1)	2M rows × 2K columns
SELECT *	O(n)	10K rows × 5K columns
WHERE clause	O(n)	1M rows tested
JOIN	O(n×m)	Tables up to 100K rows
GROUP BY	O(n)	100K groups tested
Aggregations	O(n)	1M rows tested
to_pandas()	O(n)	100K rows tested
from_pandas()	O(n)	100K rows tested
Column scaling	~O(n)	Up to 5000 columns

📜 License

MIT License

---

AlawymDB: Almost Linear Any Way You Measure - because performance should scale with your data, not against it.