Download the PHP package absorbing/cql-core without Composer

CQL Core

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

Quick Start

Minimum Query

The simplest valid CQL query requires:

Complete Query

A query with all optional features:

Query Syntax

Language Reference

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

Query Structure:

Write Statement Structure:

Write statements return the number of affected rows:

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:

DEFINE Statement

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

Syntax:

Parameters:

Examples:

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:

Options:

Examples:

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:

Parameters:

Examples:

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:

Supported Operators:

Examples:

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:

Join Types:

Parameters:

Examples:

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:

Supported Functions:

Examples:

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:

Supported Functions:

Examples:

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:

Complete Examples

Example 1: Basic Filtering

Example 2: Joining Multiple Files

Example 3: Column Aliasing

Example 4: Mathematical Expressions

Example 5: Streaming Mode for Large Files

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:

Advanced Usage

Custom Operators

Extend the operator system by implementing operator interfaces:

Working with Results

Results are returned as a Collection object with helpful methods:

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:

Performance Comparison:

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
• [x] Complex WHERE conditions with nested logic (AND/OR/NOT, parentheses) - added in v0.2.0
• [ ] Subqueries
• [ ] UNION operations
• [ ] DISTINCT keyword
• [x] IN / NOT IN with value lists, EXISTS - added in v0.2.0
• [ ] Additional operators (LIKE, BETWEEN)
• [x] 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:

Run static analysis:

Run code style checks:

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

• Issues: GitHub Issues
• Discussions: GitHub Discussions