absorbing / cql-core
Core library for the C-QL CSV Query Language
Requires
None
Requires (Dev)
- phpstan/phpstan: ^2.1
- phpunit/phpunit: ^12.1
- squizlabs/php_codesniffer: ^3.12
Suggests
None
Provides
None
Conflicts
None
Replaces
None
This package is auto-updated.
Last update: 2026-11-13 21:37:57 UTC
README
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
DateTimeconstructor
Limitations
Not Yet Supported
The following SQL features are not currently implemented:
Aggregation:
GROUP BY- Grouping rowsHAVING- Filter grouped resultsCOUNT(),SUM(),AVG(),MIN(),MAX()- Aggregate functionsDISTINCT- Remove duplicates
Sorting & Limiting:
ORDER BY- Sort resultsLIMIT- Limit number of resultsOFFSET- Skip results
Advanced Filtering:
LIKE- Pattern matchingBETWEEN- Range queries
Data Modification:
INSERT- Add rowsUPDATE- Modify rowsDELETE- 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()orCQL::auto() - Usage:
new Interpreter($ast, streaming: null)ornew 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:
- Fork the repository
- Create a feature branch
- Write tests for new functionality
- Ensure all tests pass
- Submit a pull request
License
MIT License - see LICENSE file for details
Credits
Developed by the Absorbing team.
Support
- Issues: GitHub Issues
- Discussions: GitHub Discussions
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.