Search by

absorbing / cql-core

Absorbing

Core library for the C-QL CSV Query Language

Package info

github.com/Absorbing/cql-core

pkg:composer/absorbing/cql-core

Statistics

Installs: 4

Dependents: 0

Suggesters: 0

Stars: 0

Open Issues: 0

v0.2.1 2026-09-16 11:26 UTC

README

Build Status Version PHP License

CQL (CSV Query Language) is a SQL-like query language designed specifically for querying and manipulating CSV files in PHP. It provides a familiar SQL syntax for filtering, joining, and transforming CSV data without requiring a database.

Important

As of v0.2.0, CQL supports write operations (INSERT, UPDATE, DELETE) alongside queries. UPDATE and DELETE rewrite files atomically via a temporary file. See CHANGELOG.md for details.

Features

  • SQL-like Syntax - Write queries using familiar SELECT, FROM, WHERE, JOIN, and GROUP BY statements
  • Write Operations - INSERT, UPDATE, and DELETE with atomic file rewrites and streaming support
  • Aggregate Functions - COUNT, SUM, AVG, MIN, MAX for data analysis
  • Date Functions - YEAR, MONTH, DAY, DATE for date-based analysis
  • Multiple Data Sources - Define and query multiple CSV files in a single query
  • JOIN Support - Perform INNER, LEFT, and RIGHT joins between CSV files
  • Streaming Mode - Process large files (>50MB) with minimal memory usage
  • Expression Engine - Support for mathematical and logical expressions in WHERE clauses
  • Rich WHERE Conditions - AND/OR/NOT with parentheses, IN/NOT IN value lists, and EXISTS presence tests
  • Column Aliasing - Rename columns in your result set
  • Wildcard Selection - Select all columns or prefix-specific columns with * syntax
  • Header Detection - Automatically handle CSVs with or without headers
  • Simple Facade API - Clean, intuitive interface with convenience methods
  • Type-Safe - Built with PHP 8.4+ features including readonly classes and enums
  • Extensible - Modular operator system for easy extension
  • Well Tested - 120 tests with 300 assertions covering core functionality

Requirements

  • PHP 8.4 or higher
  • Composer

Installation

composer require absorbing/cql-core

Quick Start

<?php

require 'vendor/autoload.php';

use CQL\CQL;

// Create a CQL instance
$cql = new CQL();

// Execute a query
$results = $cql->execute("
    DEFINE 'users.csv' AS users WITH HEADERS
    SELECT name, age
    FROM users
    WHERE age > 25
");

// Iterate over results
foreach ($results as $row) {
    echo "{$row['name']} is {$row['age']} years old\n";
}

// Or use convenience methods
$array = $cql->query($query);        // Returns array
$first = $cql->first($query);        // Returns first row
$count = $cql->count($query);        // Returns count

Minimum Query

The simplest valid CQL query requires:

DEFINE 'file.csv' AS alias WITH HEADERS
SELECT *
FROM alias

Complete Query

A query with all optional features:

DEFINE 'users.csv' AS users WITH HEADERS
DEFINE 'orders.csv' AS orders WITH HEADERS
SELECT users.name, orders.product, orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.amount > 100

Query Syntax

Language Reference

CQL queries follow a SQL-like syntax with specific requirements:

Component Required Description
DEFINE Yes At least one data source definition
SELECT Yes Column selection
FROM Yes Primary data source
WHERE No Filter conditions
JOIN No Join additional data sources
GROUP BY No Group rows for aggregation
AS No Aliases for tables and columns
INSERT INTO - Append rows to a data source
UPDATE ... SET - Modify rows in place (atomic rewrite)
DELETE FROM - Remove rows (atomic rewrite)

Query Structure:

DEFINE 'file.csv' AS alias [WITH|WITHOUT HEADERS] [COLUMNS (col1, col2, ...)]
[DEFINE 'file2.csv' AS alias2 ...]
SELECT column1, column2, ... | *
FROM alias
[JOIN alias2 ON alias.key = alias2.key]
[WHERE condition]

Write Statement Structure:

-- Insert (explicit columns; missing columns are written as empty strings)
DEFINE 'file.csv' AS alias WITH HEADERS
INSERT INTO alias (col1, col2) VALUES ('a', 1), ('b', 2)

-- Insert (positional; values must match the file's column order)
DEFINE 'file.csv' AS alias WITH HEADERS
INSERT INTO alias VALUES (1, 'a', 'b')

-- Update (assignments support full expressions)
DEFINE 'file.csv' AS alias WITH HEADERS
UPDATE alias SET col1 = 'x', col2 = col2 + 1 WHERE col3 > 10

-- Delete (omitting WHERE removes all rows; the header line is preserved)
DEFINE 'file.csv' AS alias WITH HEADERS
DELETE FROM alias WHERE col1 = 'x'

Write statements return the number of affected rows:

$cql = new CQL();

$affected = $cql->statement("
    DEFINE 'users.csv' AS users WITH HEADERS
    UPDATE users SET age = age + 1 WHERE name = 'Alice'
");

// execute() also accepts write statements, returning a Collection:
// [['affected_rows' => 1]]

UPDATE and DELETE rewrite the file through a temporary file in the same directory followed by an atomic rename, so a failure part-way through never corrupts the original. Large files are processed row-by-row using the same streaming rules as queries. INSERT appends under an exclusive flock.

CQL API

The CQL class provides a simple interface for executing queries:

use CQL\CQL;

// Basic usage
$cql = new CQL();
$results = $cql->execute($query);

// Convenience methods
$array = $cql->query($query);   // Returns array
$first = $cql->first($query);   // Returns first row
$count = $cql->count($query);   // Returns row count

// Static factory methods
$cql = CQL::auto();              // Automatic mode (default)
$cql = CQL::streaming();         // Always stream
$cql = CQL::normal();            // Never stream
$cql = CQL::auto(10 * 1024 * 1024); // Custom 10MB threshold

// Configuration
$cql = new CQL([
    'streaming' => true,
    'autoStreamingThreshold' => 20 * 1024 * 1024
]);

// Fluent configuration
$cql = (new CQL())
    ->setStreaming(true)
    ->setAutoStreamingThreshold(30 * 1024 * 1024);

DEFINE Statement

The DEFINE statement declares a CSV data source and assigns it an alias.

Syntax:

DEFINE 'path/to/file.csv' AS alias [WITH|WITHOUT HEADERS] [COLUMNS (col1, col2, ...)]

Parameters:

Parameter Required Description Default
'path/to/file.csv' Yes Path to CSV file (single or double quotes) -
AS alias No Table alias Auto-generated from filename
WITH HEADERS No CSV has header row -
WITHOUT HEADERS No CSV has no headers (generates column_1, column_2, etc.) Default if neither specified
COLUMNS (...) No Explicitly define column names Auto-generated or from headers

Examples:

-- Minimal: CSV with headers
DEFINE 'users.csv' AS users WITH HEADERS

-- CSV without headers (auto-generates column names)
DEFINE 'data.csv' AS data WITHOUT HEADERS

-- Explicitly define column names
DEFINE 'data.csv' AS data WITHOUT HEADERS COLUMNS (id, name, email)

-- Auto-generate alias from filename (becomes 'users')
DEFINE 'users.csv' WITH HEADERS

-- Multiple data sources
DEFINE 'users.csv' AS users WITH HEADERS
DEFINE 'orders.csv' AS orders WITH HEADERS

Rules:

  • At least one DEFINE is required per query
  • Aliases must be unique within a query
  • Aliases are case-sensitive
  • File paths are relative to execution directory

SELECT Statement

Select specific columns or use wildcards to select all columns.

Syntax:

SELECT column1, column2, ... | * | alias.*
SELECT column AS alias

Options:

Option Description Example
column Select specific column by name SELECT name
alias.column Select column with table qualifier SELECT users.name
* Select all columns from all tables SELECT *
alias.* Select all columns from specific table SELECT users.*
column AS alias Rename column in results SELECT name AS full_name

Examples:

-- Select specific columns
SELECT name, age FROM users

-- Select all columns
SELECT * FROM users

-- Select all columns from specific table (useful in JOINs)
SELECT users.*, orders.total FROM users JOIN orders ON users.id = orders.user_id

-- Column aliasing
SELECT name AS full_name, age AS years FROM users

-- Qualified column names
SELECT users.name, users.email FROM users

-- Mix of qualified and unqualified
SELECT users.name, age FROM users

Rules:

  • At least one column or wildcard is required
  • Column names are case-sensitive
  • In JOINs, ambiguous columns should be qualified with table alias
  • Wildcards can be mixed with specific columns

FROM Statement

Specify the primary data source for the query.

Syntax:

FROM alias

Parameters:

Parameter Required Description
alias Yes Must match a DEFINE alias

Examples:

FROM users
FROM data
FROM my_table

Rules:

  • Exactly one FROM clause is required
  • Alias must be defined in a DEFINE statement
  • Alias is case-sensitive

WHERE Clause

Filter rows based on conditions using comparison and logical operators.

Syntax:

WHERE condition
WHERE column operator value
WHERE expression operator expression

Supported Operators:

Category Operator Description
Comparison = Equal
!=, <> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
IN (...) Value list membership
NOT IN (...) Negated membership
Logical AND Logical AND
OR Logical OR
NOT Negate a condition or group
Existence EXISTS Value is present (not null, not empty string)
Mathematical + Addition
- Subtraction
* Multiplication
/ Division
% Modulus
^ Power (exponentiation)

Examples:

-- Simple comparison
WHERE age > 18

-- Logical operators
WHERE age > 18 AND status = 'active'
WHERE age < 18 OR age > 65

-- Mathematical expressions
WHERE price * quantity > 1000
WHERE (salary + bonus) > 100000

-- String comparison
WHERE name = 'John'
WHERE status != 'inactive'

-- Parentheses for precedence
WHERE (age > 18 AND age < 65) OR status = 'premium'

-- Value list membership
WHERE age IN (25, 30, 35)
WHERE city NOT IN ('Bristol', 'Bath')

-- Negation
WHERE NOT status = 'inactive'
WHERE NOT (age < 18 OR age > 65)

-- Existence (non-null, non-empty; '0' counts as existing)
WHERE EXISTS email
WHERE NOT EXISTS email AND age >= 18

Conditions follow standard SQL precedence: OR binds loosest, then AND, then NOT, then comparison predicates. Use parentheses to override. Trailing tokens after a complete statement raise a SyntaxException rather than being silently ignored.

Rules:

  • WHERE clause is optional
  • Only one condition is currently supported (no complex AND/OR chains)
  • Column names must exist in the data source
  • String values must be in single quotes
  • Numeric values don't need quotes

Limitations:

  • Complex nested conditions not yet supported
  • IN operator not yet implemented
  • LIKE operator not yet implemented
  • BETWEEN operator not yet implemented

JOIN Clause

Join multiple CSV files together.

Syntax:

[INNER|LEFT|RIGHT] JOIN right_alias ON left_alias.key = right_alias.key

Join Types:

Join Type Description
INNER JOIN (default) Returns rows with matches in both tables
LEFT JOIN Returns all rows from left table, matched rows from right
RIGHT JOIN Returns all rows from right table, matched rows from left

Parameters:

Parameter Required Description
right_alias Yes Alias of table to join (must be defined)
ON Yes Join condition keyword
left_alias.key Yes Column from left table
right_alias.key Yes Column from right table

Examples:

-- Inner join (default)
DEFINE 'users.csv' AS users WITH HEADERS
DEFINE 'orders.csv' AS orders WITH HEADERS
SELECT users.name, orders.product
FROM users
JOIN orders ON users.id = orders.user_id

-- Left join
SELECT users.name, orders.product
FROM users
LEFT JOIN orders ON users.id = orders.user_id

-- Multiple joins
DEFINE 'users.csv' AS users WITH HEADERS
DEFINE 'orders.csv' AS orders WITH HEADERS
DEFINE 'products.csv' AS products WITH HEADERS
SELECT users.name, orders.order_id, products.name
FROM users
JOIN orders ON users.id = orders.user_id
JOIN products ON orders.product_id = products.id

Rules:

  • JOIN is optional
  • Multiple JOINs are supported
  • Both tables must be defined with DEFINE
  • Join keys must exist in both tables
  • Join condition must use = operator
  • Column names in results are namespaced (e.g., users.name, orders.product)

Limitations:

  • Only equality joins supported (no >, <, etc.)
  • No self-joins
  • FULL OUTER JOIN not yet implemented

Aggregate Functions

Perform calculations across multiple rows.

Syntax:

SELECT function(column) [AS alias]
SELECT function(*)

Supported Functions:

Function Description Example
COUNT(*) Count all rows COUNT(*) AS total
COUNT(column) Count non-null values COUNT(name) AS users
SUM(column) Sum of numeric values SUM(amount) AS total_sales
AVG(column) Average of numeric values AVG(price) AS avg_price
MIN(column) Minimum value MIN(age) AS youngest
MAX(column) Maximum value MAX(salary) AS highest_paid

Examples:

-- Overall statistics (no GROUP BY)
DEFINE 'sales.csv' AS sales WITH HEADERS
SELECT COUNT(*) AS total_orders,
       SUM(amount) AS total_revenue,
       AVG(amount) AS avg_order_value
FROM sales

-- Group by category
SELECT category,
       COUNT(*) AS order_count,
       SUM(quantity) AS total_quantity
FROM sales
GROUP BY category

-- Multiple aggregates with GROUP BY
SELECT region,
       COUNT(*) AS orders,
       AVG(price) AS avg_price,
       MAX(quantity) AS max_quantity
FROM sales
GROUP BY region

-- Filter before aggregating
SELECT category, SUM(amount) AS revenue
FROM sales
WHERE amount > 100
GROUP BY category

Rules:

  • Aggregate functions can be used with or without GROUP BY
  • Without GROUP BY, aggregates apply to entire dataset
  • With GROUP BY, aggregates apply to each group
  • Can mix aggregates with GROUP BY columns in SELECT
  • Aliases are recommended for aggregate results

Limitations:

  • HAVING clause not yet implemented
  • Cannot filter on aggregate results (use PHP post-processing)
  • Cannot use aggregates in WHERE clause

Date Functions

Extract date components and perform date-based analysis.

Syntax:

SELECT function(date_column) [AS alias]
WHERE function(date_column) operator value
GROUP BY function(date_column)

Supported Functions:

Function Description Returns Example
YEAR(date) Extract year Integer (2024) YEAR(order_date)
MONTH(date) Extract month Integer (1-12) MONTH(order_date)
DAY(date) Extract day Integer (1-31) DAY(order_date)
DATE(date) Format as date String (YYYY-MM-DD) DATE(created_at)

Examples:

-- Extract date components
DEFINE 'orders.csv' AS orders WITH HEADERS
SELECT customer,
       order_date,
       YEAR(order_date) AS year,
       MONTH(order_date) AS month,
       DAY(order_date) AS day
FROM orders

-- Group by month
SELECT MONTH(order_date) AS month,
       COUNT(*) AS order_count,
       SUM(amount) AS revenue
FROM orders
GROUP BY MONTH(order_date)

-- Filter by date component
SELECT customer, amount
FROM orders
WHERE MONTH(order_date) = 2

-- Quarterly analysis
SELECT COUNT(*) AS q1_orders,
       SUM(amount) AS q1_revenue
FROM orders
WHERE MONTH(order_date) <= 3

Rules:

  • Date functions work with standard date formats (YYYY-MM-DD, etc.)
  • Can be used in SELECT, WHERE, and GROUP BY clauses
  • Returns NULL for invalid dates
  • Date parsing uses PHP's DateTime class

Supported Date Formats:

  • YYYY-MM-DD (2024-01-15)
  • YYYY/MM/DD (2024/01/15)
  • DD-MM-YYYY (15-01-2024)
  • Any format supported by PHP's DateTime constructor

Limitations

Not Yet Supported

The following SQL features are not currently implemented:

Aggregation:

  • GROUP BY - Grouping rows
  • HAVING - Filter grouped results
  • COUNT(), SUM(), AVG(), MIN(), MAX() - Aggregate functions
  • DISTINCT - Remove duplicates

Sorting & Limiting:

  • ORDER BY - Sort results
  • LIMIT - Limit number of results
  • OFFSET - Skip results

Advanced Filtering:

  • LIKE - Pattern matching
  • BETWEEN - Range queries

Data Modification:

  • INSERT - Add rows
  • UPDATE - Modify rows
  • DELETE - Remove rows

Advanced Joins:

  • FULL OUTER JOIN - Full outer join
  • Self-joins
  • Non-equality joins (>, <, etc.)

Other:

  • Subqueries
  • UNION - Combine queries
  • Window functions
  • CTEs (Common Table Expressions)

Workarounds

Many of these features can be implemented in PHP after querying:

$cql = new CQL();
$results = $cql->query($query);

// Sorting
usort($results, fn($a, $b) => $a['age'] <=> $b['age']);

// Limiting
$limited = array_slice($results, 0, 10);

// Distinct
$unique = array_unique($results, SORT_REGULAR);

// Aggregation
$total = array_sum(array_column($results, 'amount'));
$average = $total / count($results);

// Grouping
$grouped = [];
foreach ($results as $row) {
    $grouped[$row['category']][] = $row;
}

Complete Examples

Example 1: Basic Filtering

<?php

use CQL\CQL;

// users.csv:
// id,name,age,status
// 1,Alice,30,active
// 2,Bob,25,inactive
// 3,Charlie,35,active

$cql = new CQL();

$results = $cql->execute("
    DEFINE 'users.csv' AS users WITH HEADERS
    SELECT name, age
    FROM users
    WHERE age >= 30 AND status = 'active'
");

// Output:
// {"name":"Alice","age":"30"}
// {"name":"Charlie","age":"35"}

Example 2: Joining Multiple Files

<?php

use CQL\CQL;

// users.csv:
// id,name
// 1,Alice
// 2,Bob

// orders.csv:
// order_id,user_id,product,amount
// 101,1,Laptop,1200
// 102,1,Mouse,25
// 103,2,Keyboard,75

$cql = new CQL();

$results = $cql->execute("
    DEFINE 'users.csv' AS users WITH HEADERS
    DEFINE 'orders.csv' AS orders WITH HEADERS
    SELECT users.name, orders.product, orders.amount
    FROM users
    JOIN orders ON users.id = orders.user_id
    WHERE orders.amount > 50
");

// Output:
// {"users.name":"Alice","orders.product":"Laptop","orders.amount":"1200"}
// {"users.name":"Bob","orders.product":"Keyboard","orders.amount":"75"}

Example 3: Column Aliasing

<?php

use CQL\CQL;

$cql = new CQL();

$results = $cql->execute("
    DEFINE 'employees.csv' AS emp WITH HEADERS
    SELECT emp.first_name AS name, emp.salary AS pay
    FROM emp
    WHERE emp.department = 'Engineering'
");

// Output uses aliased column names:
// {"name":"John","pay":"95000"}
// {"name":"Jane","pay":"105000"}

Example 4: Mathematical Expressions

<?php

use CQL\CQL;

// products.csv:
// name,price,quantity
// Widget,10.50,100
// Gadget,25.00,50
// Doohickey,5.75,200

$cql = new CQL();

$results = $cql->execute("
    DEFINE 'products.csv' AS products WITH HEADERS
    SELECT name, price, quantity
    FROM products
    WHERE price * quantity > 1000
");

// Output:
// {"name":"Gadget","price":"25.00","quantity":"50"}
// {"name":"Doohickey","price":"5.75","quantity":"200"}

Example 5: Streaming Mode for Large Files

<?php

use CQL\CQL;

// For large CSV files (>50MB), use streaming mode to reduce memory usage

// Explicit streaming
$cql = CQL::streaming();
$results = $cql->execute("
    DEFINE 'large_dataset.csv' AS data WITH HEADERS
    SELECT id, name, value
    FROM data
    WHERE value > 1000
");

// Automatic mode (recommended) - decides based on file size
$cql = CQL::auto();
$results = $cql->execute($query);

// Custom threshold
$cql = CQL::auto(10 * 1024 * 1024); // 10MB threshold
$results = $cql->execute($query);

Architecture

CQL Core is built with a clean, modular architecture consisting of three main components:

1. Lexer (Tokenization)

Breaks down query strings into tokens (keywords, identifiers, operators, etc.)

2. Parser (Syntax Analysis)

Converts tokens into an Abstract Syntax Tree (AST) representing the query structure

3. Interpreter (Execution)

Executes the parsed query against CSV data sources and returns results

4. Data Layer

  • CSVDataSource - Handles CSV file loading with streaming support
  • Collection - Functional data manipulation with filter, map, and iteration

All of these components are accessed through the simple CQL facade class, which handles the entire pipeline automatically.

Exception Handling

CQL Core provides specific exceptions for different error scenarios:

Exception Purpose
DataSourceException CSV file errors (not found, permissions, format issues)
InterpreterException Execution errors (undefined aliases, ambiguous columns)
LexerException Tokenization errors
ParserException Parsing errors (unexpected tokens, malformed syntax)
SyntaxException Query syntax errors (invalid keywords, structure)
use CQL\CQL;
use CQL\Exceptions\{
    DataSourceException,
    InterpreterException,
    SyntaxException
};

try {
    $cql = new CQL();
    $results = $cql->execute($query);
} catch (DataSourceException $e) {
    // Handle file not found, permission errors, etc.
    echo "Data source error: " . $e->getMessage();
} catch (SyntaxException $e) {
    // Handle invalid query syntax
    echo "Syntax error: " . $e->getMessage();
} catch (InterpreterException $e) {
    // Handle execution errors
    echo "Execution error: " . $e->getMessage();
}

Advanced Usage

Custom Operators

Extend the operator system by implementing operator interfaces:

use CQL\Engine\Operators\BaseComparisonOperator;
use CQL\Engine\Operators\Contracts\ComparisonOperatorInterface;

class LikeOperator extends BaseComparisonOperator implements ComparisonOperatorInterface
{
    public static function symbols(): array
    {
        return ['LIKE'];
    }

    public static function evaluate(mixed $left, mixed $right): bool
    {
        $pattern = str_replace('%', '.*', preg_quote($right, '/'));
        return (bool) preg_match("/^{$pattern}$/i", $left);
    }
}

// Register the operator
use CQL\Engine\Operators\Registry\OperatorRegistry;
OperatorRegistry::register(LikeOperator::class);

Working with Results

Results are returned as a Collection object with helpful methods:

$cql = new CQL();
$results = $cql->execute($query);

// Get result count
$count = $results->count();

// Convert to array
$array = $results->toArray();

// Get first result
$first = $results->first();

// Chain operations
$filtered = $results
    ->filter(fn($row) => $row['age'] > 30)
    ->map(fn($row) => ['name' => strtoupper($row['name'])]);

// Iterate
foreach ($results as $row) {
    // Process each row
}

// Or use convenience methods directly
$array = $cql->query($query);   // Returns array
$first = $cql->first($query);   // Returns first row
$count = $cql->count($query);   // Returns count

Performance Considerations

Memory Management

CQL offers three modes for handling CSV files:

1. Normal Mode - Explicitly loads entire CSV into memory

  • Best for: Small to medium files (<50MB)
  • Usage: CQL::normal()
  • Pros: Faster query execution, supports complex operations
  • Cons: High memory usage for large files

2. Streaming Mode - Explicitly processes rows one at a time

  • Best for: Large files (>50MB)
  • Usage: CQL::streaming()
  • Pros: Minimal memory footprint, handles files of any size
  • Cons: Slightly slower for small files, still loads results into memory

3. Automatic Mode - Automatically selects based on file size

  • Best for: Unknown file sizes, dynamic workloads (recommended)
  • Usage: new CQL() or CQL::auto()
  • Usage: new Interpreter($ast, streaming: null) or new Interpreter($ast)
  • Default threshold: 50 MB (configurable)
  • Pros: No manual decision needed, optimal for most cases
  • Cons: Requires file size check

Mode Selection Examples:

use CQL\CQL;

// Explicit normal mode (always load into memory)
$cql = CQL::normal();

// Explicit streaming mode (always stream)
$cql = CQL::streaming();

// Automatic mode with default 50MB threshold (recommended)
$cql = new CQL();
// or
$cql = CQL::auto();

// Automatic mode with custom 10MB threshold
$cql = CQL::auto(10 * 1024 * 1024);

// Configuration via constructor
$cql = new CQL([
    'streaming' => true,
    'autoStreamingThreshold' => 20 * 1024 * 1024
]);

Performance Comparison:

File Size Mode Memory Usage Speed
1000 rows (~22KB) Normal ~500 KB Fast
1000 rows (~22KB) Streaming ~0 KB Very Fast
100,000 rows (~2MB) Normal ~50 MB Fast
100,000 rows (~2MB) Streaming ~5 MB Fast
1,000,000 rows (~20MB) Normal ~500 MB Slow
1,000,000 rows (~20MB) Streaming ~50 MB Medium

Other Performance Tips

  • Joins: Join operations create in-memory indexes. Multiple joins on large datasets may consume significant memory even in streaming mode.
  • Filtering: WHERE clauses are applied during data loading in streaming mode, reducing memory usage.
  • File Format: Ensure CSV files are well-formed to avoid parsing errors.
  • Caching: Consider caching parsed ASTs for frequently-executed queries.

Limitations

  • No Sorting: ORDER BY is not yet implemented
  • No Limits: LIMIT and OFFSET are not yet implemented
  • No Subqueries: Nested queries are not supported
  • No DDL: CREATE TABLE and DROP TABLE are not yet implemented

Roadmap

Future features under consideration:

  • Aggregation functions (COUNT, SUM, AVG, MIN, MAX)
  • GROUP BY and HAVING clauses
  • ORDER BY with ASC/DESC
  • LIMIT and OFFSET
  • Complex WHERE conditions with nested logic (AND/OR/NOT, parentheses) - added in v0.2.0
  • Subqueries
  • UNION operations
  • DISTINCT keyword
  • IN / NOT IN with value lists, EXISTS - added in v0.2.0
  • Additional operators (LIKE, BETWEEN)
  • Write operations (INSERT, UPDATE, DELETE) - added in v0.2.0
  • DDL operations (CREATE TABLE, DROP TABLE)
  • JSON and other data format support

Testing

Run the test suite:

composer test

Run static analysis:

vendor/bin/phpstan analyze

Run code style checks:

vendor/bin/phpcs --standard=.phpcs.xml src

Contributing

Contributions are welcome! Please:

  1. Fork the repository
  2. Create a feature branch
  3. Write tests for new functionality
  4. Ensure all tests pass
  5. Submit a pull request

License

MIT License - see LICENSE file for details

Credits

Developed by the Absorbing team.

Support

Parsing and diagnostic errors

The tokenizer consumes the entire query and rejects unknown characters and unterminated strings/comments. SQL strings accept single or double quotes and escape a quote by doubling it: 'O''Brien'. Backslashes remain literal. -- line comments and /* block comments */ are supported outside strings.

Expression operands in the public AST now use LiteralNode and ColumnReferenceNode. Code inspecting the AST should read value or name respectively instead of assuming every operand is a string. Literals preserve integer, float, boolean and null types. CSV fields still use the existing string storage conventions, including empty strings for null writes.

All existing exception classes extend CQLException (and remain RuntimeException subclasses). They expose errorCode, a stable string such as CQL_LEXER_ERROR, CQL_PARSER_ERROR, CQL_SYNTAX_ERROR, CQL_EXECUTION_ERROR, or CQL_SOURCE_ERROR; position, a zero-based query byte offset when available; and context, which can contain path, alias, and row for a CSV error. CSV row is the zero-based data-row index, excluding the header. Existing numeric exception codes and chained exceptions are retained.

Prepared queries

prepare() parses and validates a statement without opening its CSV sources. Each execution uses fresh source data and a complete new set of bindings. The parsed AST is reused; neither query text nor parameters are interpolated.

$prepared = $cql->prepare(
    "DEFINE 'users.csv' AS users WITH HEADERS " .
    'SELECT name FROM users WHERE age >= :minimum'
);
$rows = $prepared->query(['minimum' => 18]);
$older = $prepared->query(['minimum' => 65]);

execute() returns the existing Collection; query() returns its array; and statement() accepts only INSERT, UPDATE or DELETE and returns affected rows. Named bindings accept keys with or without a leading colon. Positional ? bindings use a zero-based list. Do not mix styles or reuse a named marker in one query. Every marker represents one scalar value or null, including inside an IN list or date function. Identifiers, file paths and SQL fragments cannot be bound. Missing, extra, duplicate or non-scalar bindings raise ParameterException (CQL_PARAMETER_ERROR) before execution. Numeric strings remain strings; CSV writes retain the existing string conversion conventions.

Registered data sources

Register sources on a CQL instance and reference their aliases without a DEFINE prefix. Registrations do not open files and are isolated between instances. Registering the same alias replaces its previous factory; unregisterSource($alias) removes it. An inline DEFINE overrides the registered source for that statement only. Duplicate inline aliases are rejected.

$cql->registerCsv('users', '/data/users.csv', headers: true);
$cql->registerCsv('events', '/data/events.tsv', delimiter: "\t");
$rows = $cql->query('SELECT name FROM users');

registerSource($alias, $factory) accepts a callable returning a fresh DataSourceInterface implementation on each execution. Custom sources return unqualified column keys; CQL adds the registered alias while executing the query. Implement SchemaDataSourceInterface to supply ordered column names, including for empty sources, and WritableDataSourceInterface to support mutations. Read-only sources reject write operations explicitly. CSV registration takes a literal filesystem path, with no SQL quoting required.

Structured results

Use run($query, $parameters = []) or $prepared->run($parameters) for a QueryResult. The existing execute(), query() and statement() return types remain unchanged.

$result = $cql->run('SELECT name AS person FROM users WHERE age >= ?', [18]);
$columns = $result->columns(); // Ordered ResultColumn objects
foreach ($result->rows() as $row) {
    echo $row['person'];
}
$result->close();

Each column exposes name, sourceAlias and sourceColumn. Computed values have no source field. Metadata is available even when no rows match; wildcard metadata for an empty custom source requires SchemaDataSourceInterface. Output names must be unique in this API; use explicit AS aliases to distinguish columns. Ambiguous and unknown projected fields are rejected when the schema is known.

fetch() returns the next associative row or null at the end. rows() and fetchAll() consume the remaining cursor and use consecutive numeric row indexes. Result cursors are independent, forward-only and cannot be rewound. close() releases the remaining data and is safe to call repeatedly. A SELECT has isQuery() === true and affectedRows() === null. Mutations have no rows or columns and expose the affected count, including zero. Grouped queries and mixed date-function/column projections retain their declared aliases.

Cursor streaming and memory

With streaming enabled, run() reads CSV headers to describe the result but reads data rows only when fetched. Simple SELECT, WHERE, aliases, wildcards and date-function projection run one row at a time. first() stops at the first match and closes the cursor. Fetching does not read the following row in advance, so later CSV errors are reported when that row is requested.

Use CQL::streaming() to force this behaviour, or use automatic mode with its configured file-size threshold. CQL::normal() still loads CSV input into memory. execute(), query() and fetchAll() deliberately materialize their returned rows; consume rows() or fetch() for bounded-memory output. Joins, GROUP BY and aggregates currently use the buffered execution path. They are not bounded-memory operations.

Custom sources can implement StreamingDataSourceInterface: load() supplies metadata without consuming the rows, and streamRows() yields unqualified rows. Source generators should release their resources in a finally block. Closing or exhausting a result releases its active generator. A partially consumed result should be closed explicitly when retained by the application.

Coordinated CSV writes

CQL mutations acquire an exclusive lock before loading the rows or headers, and keep it until the mutation finishes. INSERT, UPDATE and DELETE share a stable <canonical-csv-path>.cql.lock sidecar, so replacing the CSV inode does not replace the lock. Relative paths and symlinks resolve to the canonical path. Keep lock files in place while the data source is in use; the parent directory must permit their creation. Hard-link aliases and external writers that ignore this protocol are not coordinated.

UPDATE and DELETE write a temporary file in the CSV directory, check writes, preserve permissions and rename it into place. Exceptions discard the unfinished temporary file and release the lock. INSERT validates the entire batch before appending and restores the original file length on a caught write failure. Readers do not acquire transaction snapshots, and a process crash during append is not covered by exception rollback.

CSVDataSource::withWriteLock($operation) can wrap direct source operations; use that same source instance inside the callback. A direct rewriteFrom() only protects the replacement itself: include any reads used to calculate its rows in the callback. Custom writable sources may implement LockingWritableDataSourceInterface to coordinate the whole CQL mutation using their own locking mechanism. Sources without that interface retain their existing concurrency behaviour. This is per-file writer coordination, with no multi-statement or multi-file transactions.

Development checks

Install the locked development dependencies and run all four checks before opening or merging a pull request:

composer install
composer check

composer check runs PHPUnit, PHPStan level 8, PHP_CodeSniffer and PHPMD. Individual commands are composer test, composer phpstan, composer phpcs and composer phpmd. All four checks also run on pull requests to master. PHPMD uses the codesize, cleancode, controversial, design, naming and unusedcode rulesets with their standard thresholds. Its version is locked in composer.lock. PDepend uses an in-memory analysis cache so edited methods cannot inherit stale metrics from an earlier scan.

Scoped PHPMD exceptions are documented at the affected declarations. They preserve the public facade, existing configuration flags and named arguments, and intentional static factory/registry operations. The built-in operator provider also documents its role as the composition root for the operator implementations. New grammar and execution components are internal; applications should continue using the public CQL, PreparedQuery and result APIs.