SYNAPCORES

SQLv2 Standard Specification

Version 2.1 - September 2025
Status: Official Release

Part I: Foundation

1. Introduction

1.1 Purpose and Scope

This specification defines the SYNAPCORES SQLv2 language, an AI-native extension of standard SQL that integrates advanced machine learning, vector operations, and multimedia processing capabilities directly into the database query language. SYNAPCORES SQLv2 is designed to enable seamless integration of traditional relational database operations with modern AI/ML workflows.

This document serves as the authoritative reference for:

Database developers implementing SYNAPCORES -compatible systems
Application developers using SYNAPCORES in production environments
System administrators managing SYNAPCORES deployments
Data scientists leveraging SYNAPCORES 's AI capabilities

1.2 Relationship to SQL Standards

SYNAPCORES SQLv2 extends ANSI SQL:2016 with additional features while maintaining compatibility with core SQL operations. The language includes:

Full support for SQL:2016 Foundation features
Extensions for AI/ML operations not covered by standard SQL
Native multimedia data types and operations
Vector algebra and similarity search capabilities

1.3 Document Organization

This specification is organized into seven parts:

Part I: Foundation - Introduction, references, and conventions
Part II: Data Model and Types - Type system and schema objects
Part III: SQL Language - DDL, DML, TCL, and DCL
Part IV: Functions and Operators - Complete function reference
Part V: Advanced Features - AI/ML, backup, recipes, and integrations
Part VI: Implementation - Architecture and APIs
Part VII: Conformance - Requirements and limitations

1.4 Audience and Prerequisites

This specification assumes familiarity with:

Standard SQL syntax and concepts
Basic understanding of AI/ML concepts
RESTful API design principles
Database administration concepts

SynapCores System specs is changing continuosly as we sprint to release.

2. Normative References

2.1 SQL Standards Referenced

ISO/IEC 9075:2016 (SQL:2016) - Database Language SQL
ISO/IEC 13249-3:2016 - SQL Multimedia and Application Packages

2.2 AI/ML Framework Standards

ONNX 1.14.0 - Open Neural Network Exchange
OpenAI API Specification v1

2.3 Multimedia Format Standards

ISO/IEC 14496-14:2020 - MP4 file format
ISO/IEC 15948:2004 - PNG specification
RFC 6716 - Opus Audio Codec

2.4 Security and Encryption Standards

FIPS 197 - AES encryption standard
RFC 7519 - JSON Web Token (JWT)
RFC 5246 - TLS v1.2

3. Terms, Definitions, and Conventions

3.1 Terminology

Term	Definition
AI-Native	Functionality integrated directly into the database engine
Embedding	Dense vector representation of data
Recipe	Templated SQL workflow with parameter substitution
Tensor	Multi-dimensional array used in ML operations
Tenant	Isolated namespace for multi-tenant operations

3.2 Notation Conventions

Syntax Notation:
[ ]     - Optional elements
{ }     - Required choice
|       - Alternative options
...     - Repeatable elements
CAPS    - SQL keywords
lowercase - User-defined identifiers
'literal' - String literals

3.3 Syntax Diagrams

Syntax is presented in Extended Backus-Naur Form (EBNF):

statement ::= select_statement | insert_statement | update_statement
select_statement ::= SELECT [DISTINCT] column_list FROM table_reference

3.4 Implementation Status Indicators

✅ [Implemented] - Fully implemented and production-ready
⚠️ [Partial] - Partially implemented with limitations
🚧 [Planned] - Designed but not yet implemented
❌ [Not Supported] - Not planned for implementation

3.5 Conformance Levels

[Core] - Required for basic SYNAPCORES SQLv2 conformance
[Enhanced] - Required for enhanced conformance
[Optional] - Optional extensions

Part II: Data Model and Types

4. Data Types

4.1 Standard SQL Data Types

4.1.1 Numeric Types

Type	Range	Storage	Status	Conformance
`BOOLEAN`	TRUE/FALSE	1 byte	✅ [Implemented]	[Core]
`SMALLINT`	-32,768 to 32,767	2 bytes	✅ [Implemented]	[Core]
`INTEGER`	-2³¹ to 2³¹-1	4 bytes	✅ [Implemented]	[Core]
`BIGINT`	-2⁶³ to 2⁶³-1	8 bytes	✅ [Implemented]	[Core]
`REAL`	6 decimal digits precision	4 bytes	✅ [Implemented]	[Core]
`DOUBLE`	15 decimal digits precision	8 bytes	✅ [Implemented]	[Core]
`DECIMAL(p,s)`	Up to 38 digits	Variable	✅ [Implemented]	[Core]

Syntax:

CREATE TABLE numeric_example (
    id INTEGER PRIMARY KEY,
    quantity SMALLINT,
    price DECIMAL(10,2),
    tax_rate REAL,
    is_active BOOLEAN DEFAULT TRUE
);

4.1.2 Character String Types

Type	Description	Status	Conformance
`CHAR(n)`	Fixed-length string	✅ [Implemented]	[Core]
`VARCHAR(n)`	Variable-length string with limit	✅ [Implemented]	[Core]
`TEXT`	Variable-length string, unlimited	✅ [Implemented]	[Core]

4.1.3 Binary String Types

Type	Description	Status	Conformance
`BYTEA`	Variable-length binary data	✅ [Implemented]	[Core]

4.1.4 Date/Time Types

Type	Description	Status	Conformance
`DATE`	Calendar date (YYYY-MM-DD)	✅ [Implemented]	[Core]
`TIME`	Time of day (HH:MM:SS[.fraction])	✅ [Implemented]	[Core]
`TIMESTAMP`	Date and time	✅ [Implemented]	[Core]

4.1.5 Boolean Type

Type	Values	Status	Conformance
`BOOLEAN`	TRUE, FALSE, NULL	✅ [Implemented]	[Core]

4.2 Extended Data Types

4.2.1 JSON/JSONB Types

Type	Description	Status	Conformance
`JSON`	Text JSON storage	✅ [Implemented]	[Enhanced]
`JSONB`	Binary JSON storage (optimized)	✅ [Implemented]	[Enhanced]

Operations:

-- JSON operations
SELECT JSON_EXTRACT(metadata, '$.version') FROM configs;
SELECT * FROM products WHERE JSON_CONTAINS(attributes, '{"color": "red"}');

4.2.2 UUID Type

Type	Description	Status	Conformance
`UUID`	128-bit universally unique identifier	✅ [Implemented]	[Enhanced]

4.3 AI-Native Data Types

4.3.1 Vector Type

Type	Description	Status	Conformance
`VECTOR(dimensions)`	Dense vector for embeddings	✅ [Implemented]	[Enhanced]

Syntax:

CREATE TABLE embeddings (
    id INTEGER PRIMARY KEY,
    content TEXT,
    embedding VECTOR(1536)  -- OpenAI embedding dimension
);

Operations:

-- Vector similarity operators
SELECT * FROM embeddings 
WHERE embedding <=> query_vector < 0.5;  -- Cosine similarity

SELECT * FROM embeddings 
WHERE embedding <-> query_vector < 1.0;  -- Euclidean distance

SELECT * FROM embeddings 
WHERE embedding <#> query_vector > 0.8;  -- Inner product

4.4 Multimedia Data Types

4.4.1 Audio Types and Formats

Type	Supported Formats	Status	Conformance
`AUDIO(format)`	MP3, WAV, FLAC, AAC, OGG	✅ [Implemented]	[Optional]

Syntax:

CREATE TABLE audio_files (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255),
    audio_data AUDIO(MP3),
    lossless_data AUDIO(FLAC)
);

4.4.2 Video Types and Formats

Type	Supported Formats	Status	Conformance
`VIDEO(format)`	MP4, AVI, MKV, WEBM, MOV	✅ [Implemented]	[Optional]

4.4.3 Image Types and Formats

Type	Supported Formats	Status	Conformance
`IMAGE(format)`	JPEG, PNG, WEBP, GIF, BMP	✅ [Implemented]	[Optional]

4.4.4 Document Types

Type	Description	Status	Conformance
`PDF`	PDF document storage	✅ [Implemented]	[Optional]

5. Schema Objects

5.1 Databases

Syntax:

CREATE DATABASE [IF NOT EXISTS] database_name;
DROP DATABASE [IF EXISTS] database_name [CASCADE];
USE database_name;
SHOW DATABASES [LIKE 'pattern'];

Status: ✅ [Implemented] | Conformance: [Core]

5.2 Tables

5.2.1 Regular Tables

Syntax:

CREATE TABLE [IF NOT EXISTS] table_name (
    column_name data_type [column_constraint],
    ...
    [table_constraint]
);

Constraints:

PRIMARY KEY ✅ [Implemented]
UNIQUE ✅ [Implemented]
NOT NULL ✅ [Implemented]
CHECK (expression) ✅ [Implemented]
DEFAULT value ✅ [Implemented]
REFERENCES table(column) ✅ [Implemented]

5.2.2 Partitioned Tables

Syntax:

-- Range partitioning
CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    sale_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2024_q1 PARTITION OF sales
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

-- List partitioning
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    country VARCHAR(50)
) PARTITION BY LIST (country);

CREATE TABLE customers_usa PARTITION OF customers
FOR VALUES IN ('USA', 'US');

-- Hash partitioning
CREATE TABLE user_activity (
    user_id INTEGER,
    data JSON
) PARTITION BY HASH (user_id);

CREATE TABLE user_activity_0 PARTITION OF user_activity
FOR VALUES WITH (MODULUS 4, REMAINDER 0);

Status: ✅ [Implemented] | Conformance: [Enhanced]

5.2.3 System Tables

Virtual Metadata Tables:

-- AI Model Information
SELECT * FROM AI.MODELS;
SELECT * FROM AI.EXPERIMENTS;
SELECT * FROM AI.PROVIDERS;

-- System Information
SELECT * FROM SYSTEM.INDEXES;
SELECT * FROM SYSTEM.FEATURES;

-- Information Schema
SELECT * FROM information_schema.tables;
SELECT * FROM information_schema.columns;
SELECT * FROM information_schema.partitions;

Status: ✅ [Implemented] | Conformance: [Enhanced]

5.3 Indexes

Syntax:

CREATE [UNIQUE] INDEX [IF NOT EXISTS] index_name 
ON table_name (column [ASC|DESC], ...);

DROP INDEX [IF EXISTS] index_name;

Index Types:

B-Tree indexes ✅ [Implemented]
Hash indexes ✅ [Implemented]
Vector indexes (IVF) ✅ [Implemented]

5.4 Constraints

Table-level Constraints:

CONSTRAINT constraint_name PRIMARY KEY (columns)
CONSTRAINT constraint_name UNIQUE (columns)
CONSTRAINT constraint_name CHECK (expression)
CONSTRAINT constraint_name FOREIGN KEY (columns) REFERENCES table(columns)

5.5 Triggers

Syntax:

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
[FOR EACH ROW]
[WHEN condition]
EXECUTE PROCEDURE procedure_name (arguments);

Status: ⚠️ [Partial] - Basic implementation | Conformance: [Enhanced]

5.6 Stored Procedures

Syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name (arguments)
AS $
BEGIN
    -- procedure body
END;
$ LANGUAGE plpgsql;

CALL procedure_name (arguments);

Status: ⚠️ [Partial] - Parser ready, executor incomplete | Conformance: [Optional]

Part III: SQL Language

6. Data Definition Language (DDL)

6.1 CREATE Statements

6.1.1 CREATE DATABASE

Syntax:

CREATE DATABASE [IF NOT EXISTS] database_name;

Parameters:

IF NOT EXISTS: Optional clause to avoid error if database exists
database_name: Name of the new database

Status: ✅ [Implemented] | Conformance: [Core]

6.1.2 CREATE TABLE

Syntax:

CREATE TABLE [IF NOT EXISTS] table_name (
    column_definition [, ...]
    [, table_constraint [, ...]]
) [PARTITION BY {RANGE|LIST|HASH} (column)];

Column Definition:

column_name data_type 
    [COLLATE collation]
    [column_constraint [...]]

Column Constraints:

NOT NULL
NULL
CHECK (expression)
DEFAULT default_expr
UNIQUE
PRIMARY KEY
REFERENCES reftable [(refcolumn)]

Status: ✅ [Implemented] | Conformance: [Core]

6.1.3 CREATE INDEX

Syntax:

CREATE [UNIQUE] INDEX [CONCURRENTLY] [IF NOT EXISTS] index_name
ON table_name [USING method]
(column_name [ASC | DESC] [NULLS {FIRST | LAST}] [, ...]);

Index Methods:

BTREE (default)
HASH
VECTOR (for similarity search)

Status: ✅ [Implemented] | Conformance: [Core]

6.2 ALTER Statements

Syntax:

ALTER TABLE table_name action;

Actions:

ADD COLUMN column_name data_type [column_constraint]
DROP COLUMN column_name
RENAME COLUMN old_name TO new_name
ALTER COLUMN column_name TYPE new_data_type
ADD table_constraint
DROP CONSTRAINT constraint_name

Status: ✅ [Implemented] | Conformance: [Core]

6.3 DROP Statements

Syntax:

DROP {DATABASE | TABLE | INDEX | TRIGGER | PROCEDURE} 
    [IF EXISTS] object_name [CASCADE | RESTRICT];

Status: ✅ [Implemented] | Conformance: [Core]

6.4 Table Partitioning DDL

6.4.1 RANGE Partitioning

Syntax:

CREATE TABLE table_name (...) PARTITION BY RANGE (column);

CREATE TABLE partition_name PARTITION OF parent_table
FOR VALUES FROM (start_value) TO (end_value);

Example:

-- Create partitioned table
CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    sale_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (sale_date);

-- Create quarterly partitions
CREATE TABLE sales_2024_q1 PARTITION OF sales
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE sales_2024_q2 PARTITION OF sales
FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

Status: ✅ [Implemented] | Conformance: [Enhanced]

6.4.2 LIST Partitioning

Syntax:

CREATE TABLE table_name (...) PARTITION BY LIST (column);

CREATE TABLE partition_name PARTITION OF parent_table
FOR VALUES IN (value_list);

Status: ✅ [Implemented] | Conformance: [Enhanced]

6.4.3 HASH Partitioning

Syntax:

CREATE TABLE table_name (...) PARTITION BY HASH (column);

CREATE TABLE partition_name PARTITION OF parent_table
FOR VALUES WITH (MODULUS num_partitions, REMAINDER partition_num);

Status: ✅ [Implemented] | Conformance: [Enhanced]

7. Data Manipulation Language (DML)

7.1 INSERT Statement

Syntax:

INSERT INTO table_name [(column_list)]
VALUES (value_list) [, (value_list) ...]
[ON CONFLICT conflict_target conflict_action];

INSERT INTO table_name [(column_list)]
SELECT ... ;

INSERT OR REPLACE INTO table_name VALUES (...);

Conflict Actions:

ON CONFLICT DO NOTHING
ON CONFLICT DO UPDATE SET ...
ON CONFLICT IGNORE

Status: ✅ [Implemented] | Conformance: [Core]

7.2 UPDATE Statement

Syntax:

UPDATE table_name
SET column = value [, column = value ...]
[FROM from_list]
[WHERE condition];

Status: ✅ [Implemented] | Conformance: [Core]

7.3 DELETE Statement

Syntax:

DELETE FROM table_name
[WHERE condition];

Status: ✅ [Implemented] | Conformance: [Core]

7.4 SELECT Statement

7.4.1 Basic SELECT

Syntax:

SELECT [ALL | DISTINCT] select_list
FROM table_reference [, ...]
[WHERE condition]
[GROUP BY grouping_element [, ...]]
[HAVING condition]
[ORDER BY expression [ASC | DESC] [NULLS {FIRST | LAST}] [, ...]]
[LIMIT count]
[OFFSET start];

Status: ✅ [Implemented] | Conformance: [Core]

7.4.2 Joins

Supported Join Types:

-- INNER JOIN
SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;

-- LEFT JOIN
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id;

-- RIGHT JOIN
SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id;

-- FULL OUTER JOIN
SELECT * FROM t1 FULL JOIN t2 ON t1.id = t2.id;

-- CROSS JOIN
SELECT * FROM t1 CROSS JOIN t2;

Status: ✅ [Implemented] | Conformance: [Core]

7.4.3 Subqueries

Supported Subquery Types:

-- Scalar subquery
SELECT name, (SELECT AVG(salary) FROM employees) as avg_salary
FROM employees;

-- IN subquery
SELECT * FROM orders 
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'USA');

-- EXISTS subquery
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Status: ✅ [Implemented] | Conformance: [Core]

7.4.4 Common Table Expressions (CTEs)

Syntax:

WITH cte_name [(column_list)] AS (
    SELECT ...
)
SELECT ... FROM cte_name;

Example:

WITH regional_sales AS (
    SELECT region, SUM(amount) as total_sales
    FROM sales
    GROUP BY region
)
SELECT * FROM regional_sales WHERE total_sales > 1000000;

Status: ✅ [Implemented] | Conformance: [Enhanced]

7.4.5 Window Functions

Syntax:

function_name() OVER (
    [PARTITION BY expression [, ...]]
    [ORDER BY expression [ASC | DESC] [, ...]]
    [frame_clause]
)

Frame Clause:

{RANGE | ROWS} BETWEEN 
    {UNBOUNDED PRECEDING | value PRECEDING | CURRENT ROW} 
    AND 
    {CURRENT ROW | value FOLLOWING | UNBOUNDED FOLLOWING}

Status: ✅ [Implemented] | Conformance: [Enhanced]

8. Transaction Control Language (TCL)

8.1 BEGIN TRANSACTION

Syntax:

BEGIN [TRANSACTION | WORK];

Status: ✅ [Implemented] | Conformance: [Core]

8.2 COMMIT

Syntax:

COMMIT [TRANSACTION | WORK];

Status: ✅ [Implemented] | Conformance: [Core]

8.3 ROLLBACK

Syntax:

ROLLBACK [TRANSACTION | WORK];

Status: ✅ [Implemented] | Conformance: [Core]

8.4 Transaction Isolation Levels

Supported Levels:

READ UNCOMMITTED ⚠️ [Partial]
READ COMMITTED ✅ [Implemented]
REPEATABLE READ ✅ [Implemented]
SERIALIZABLE ✅ [Implemented]

9. Data Control Language (DCL)

9.1 User Management

Syntax:

CREATE USER username WITH PASSWORD 'password';
ALTER USER username SET property = value;
DROP USER username;

Status: ✅ [Implemented] via API | Conformance: [Enhanced]

9.2 Access Control

Role-Based Access Control:

GRANT privilege ON object TO role;
REVOKE privilege ON object FROM role;

Status: ✅ [Implemented] via API | Conformance: [Enhanced]

9.3 Tenant Isolation

All operations are automatically isolated by tenant ID. Tenant context is established at connection time via JWT authentication.

Status: ✅ [Implemented] | Conformance: [Core]

Part IV: Functions and Operators

10. Standard SQL Functions

10.1 Aggregate Functions

Function	Syntax	Description	Status
`COUNT`	`COUNT(*)` or `COUNT(column)`	Count rows	✅ [Implemented]
`SUM`	`SUM(column)`	Sum of values	✅ [Implemented]
`AVG`	`AVG(column)`	Average value	✅ [Implemented]
`MIN`	`MIN(column)`	Minimum value	✅ [Implemented]
`MAX`	`MAX(column)`	Maximum value	✅ [Implemented]

Examples:

SELECT COUNT(*) FROM users;
SELECT AVG(salary) FROM employees;
SELECT department, SUM(budget) FROM departments GROUP BY department;

10.2 String Functions

Function	Syntax	Description	Status
`UPPER`	`UPPER(string)`	Convert to uppercase	✅ [Implemented]
`LOWER`	`LOWER(string)`	Convert to lowercase	✅ [Implemented]
`LENGTH`	`LENGTH(string)`	String length	✅ [Implemented]
`SUBSTRING`	`SUBSTRING(string, start [, length])`	Extract substring	✅ [Implemented]
`CONCAT`	`CONCAT(string1, string2, ...)`	Concatenate strings	✅ [Implemented]
`TRIM`	`TRIM(string)`	Remove whitespace	✅ [Implemented]
`REPLACE`	`REPLACE(string, from, to)`	Replace substring	✅ [Implemented]
`SPLIT`	`SPLIT(string, delimiter)`	Split into array	✅ [Implemented]
`LEFT`	`LEFT(string, n)`	First n characters	✅ [Implemented]
`RIGHT`	`RIGHT(string, n)`	Last n characters	✅ [Implemented]
`LPAD`	`LPAD(string, length, fill)`	Left pad string	✅ [Implemented]
`RPAD`	`RPAD(string, length, fill)`	Right pad string	✅ [Implemented]
`REPEAT`	`REPEAT(string, count)`	Repeat string	✅ [Implemented]
`REVERSE`	`REVERSE(string)`	Reverse string	✅ [Implemented]
`POSITION`	`POSITION(substring IN string)`	Find position	✅ [Implemented]
`ASCII`	`ASCII(char)`	ASCII code	✅ [Implemented]
`CHR`	`CHR(code)`	Character from code	✅ [Implemented]
`INITCAP`	`INITCAP(string)`	Capitalize words	✅ [Implemented]
`MD5`	`MD5(string)`	MD5 hash	✅ [Implemented]
`SHA1`	`SHA1(string)`	SHA1 hash	✅ [Implemented]
`SHA256`	`SHA256(string)`	SHA256 hash	✅ [Implemented]

10.3 Numeric Functions

Function	Syntax	Description	Status
`ABS`	`ABS(number)`	Absolute value	✅ [Implemented]
`ROUND`	`ROUND(number [, precision])`	Round to precision	✅ [Implemented]
`CEIL`	`CEIL(number)`	Round up	✅ [Implemented]
`FLOOR`	`FLOOR(number)`	Round down	✅ [Implemented]
`SQRT`	`SQRT(number)`	Square root	✅ [Implemented]
`POWER`	`POWER(base, exponent)`	Exponentiation	✅ [Implemented]
`MOD`	`MOD(dividend, divisor)`	Modulo	✅ [Implemented]
`EXP`	`EXP(number)`	Exponential	✅ [Implemented]
`LOG`	`LOG(number)`	Natural logarithm	✅ [Implemented]
`LOG10`	`LOG10(number)`	Base-10 logarithm	✅ [Implemented]
`SIGN`	`SIGN(number)`	Sign (-1, 0, 1)	✅ [Implemented]
`TRUNCATE`	`TRUNCATE(number, decimals)`	Truncate decimals	✅ [Implemented]
`PI`	`PI()`	Pi constant	✅ [Implemented]
`RANDOM`	`RANDOM()`	Random number	✅ [Implemented]
`SIN`	`SIN(radians)`	Sine	✅ [Implemented]
`COS`	`COS(radians)`	Cosine	✅ [Implemented]
`TAN`	`TAN(radians)`	Tangent	✅ [Implemented]
`ASIN`	`ASIN(number)`	Arcsine	✅ [Implemented]
`ACOS`	`ACOS(number)`	Arccosine	✅ [Implemented]
`ATAN`	`ATAN(number)`	Arctangent	✅ [Implemented]
`DEGREES`	`DEGREES(radians)`	Convert to degrees	✅ [Implemented]
`RADIANS`	`RADIANS(degrees)`	Convert to radians	✅ [Implemented]

10.4 Date/Time Functions

Function	Syntax	Description	Status
`NOW`	`NOW()`	Current timestamp	✅ [Implemented]
`CURRENT_DATE`	`CURRENT_DATE`	Current date	✅ [Implemented]
`CURRENT_TIME`	`CURRENT_TIME`	Current time	✅ [Implemented]
`YEAR`	`YEAR(date)`	Extract year	✅ [Implemented]
`MONTH`	`MONTH(date)`	Extract month	✅ [Implemented]
`DAY`	`DAY(date)`	Extract day	✅ [Implemented]
`HOUR`	`HOUR(time)`	Extract hour	✅ [Implemented]
`MINUTE`	`MINUTE(time)`	Extract minute	✅ [Implemented]
`SECOND`	`SECOND(time)`	Extract second	✅ [Implemented]
`DATE_FORMAT`	`DATE_FORMAT(date, format)`	Format date	✅ [Implemented]
`STR_TO_DATE`	`STR_TO_DATE(string, format)`	Parse date	✅ [Implemented]
`DATE_ADD`	`DATE_ADD(date, interval, type)`	Add interval	✅ [Implemented]
`DATE_SUB`	`DATE_SUB(date, interval, type)`	Subtract interval	✅ [Implemented]
`DATEDIFF`	`DATEDIFF(date1, date2)`	Days between	✅ [Implemented]
`DAYNAME`	`DAYNAME(date)`	Day name	✅ [Implemented]
`MONTHNAME`	`MONTHNAME(date)`	Month name	✅ [Implemented]
`QUARTER`	`QUARTER(date)`	Quarter (1-4)	✅ [Implemented]
`WEEK`	`WEEK(date)`	Week number	✅ [Implemented]
`DAYOFWEEK`	`DAYOFWEEK(date)`	Day of week	✅ [Implemented]
`DAYOFYEAR`	`DAYOFYEAR(date)`	Day of year	✅ [Implemented]
`LAST_DAY`	`LAST_DAY(date)`	Last day of month	✅ [Implemented]
`UNIX_TIMESTAMP`	`UNIX_TIMESTAMP(date)`	Convert to Unix time	✅ [Implemented]

10.5 Conversion Functions

Function	Syntax	Description	Status
`CAST`	`CAST(value AS type)`	Type conversion	✅ [Implemented]
`CONVERT`	`CONVERT(value, type)`	Type conversion	✅ [Implemented]

10.6 Conditional Functions

Function	Syntax	Description	Status
`CASE`	`CASE WHEN condition THEN result END`	Conditional logic	✅ [Implemented]
`COALESCE`	`COALESCE(value1, value2, ...)`	First non-null	✅ [Implemented]
`NULLIF`	`NULLIF(value1, value2)`	Null if equal	✅ [Implemented]
`GREATEST`	`GREATEST(value1, value2, ...)`	Maximum value	✅ [Implemented]
`LEAST`	`LEAST(value1, value2, ...)`	Minimum value	✅ [Implemented]
`IF`	`IF(condition, true_value, false_value)`	Simple conditional	✅ [Implemented]
`IFNULL`	`IFNULL(value, default)`	Default if null	✅ [Implemented]

11. Statistical and Analytical Functions

11.1 Statistical Functions

Function	Syntax	Description	Status
`STDDEV`	`STDDEV(column)`	Sample standard deviation	✅ [Implemented]
`STDDEV_POP`	`STDDEV_POP(column)`	Population standard deviation	✅ [Implemented]
`VARIANCE`	`VARIANCE(column)`	Sample variance	✅ [Implemented]
`VAR_POP`	`VAR_POP(column)`	Population variance	✅ [Implemented]
`MEDIAN`	`MEDIAN(column)`	Median value	✅ [Implemented]
`MODE`	`MODE(column)`	Most frequent value	✅ [Implemented]
`CORR`	`CORR(x, y)`	Correlation coefficient	✅ [Implemented]

11.2 Window Functions

Function	Syntax	Description	Status
`ROW_NUMBER`	`ROW_NUMBER() OVER (...)`	Sequential row number	✅ [Implemented]
`RANK`	`RANK() OVER (...)`	Rank with gaps	✅ [Implemented]
`DENSE_RANK`	`DENSE_RANK() OVER (...)`	Rank without gaps	✅ [Implemented]
`LAG`	`LAG(column, offset, default) OVER (...)`	Previous row value	✅ [Implemented]
`LEAD`	`LEAD(column, offset, default) OVER (...)`	Next row value	✅ [Implemented]
`FIRST_VALUE`	`FIRST_VALUE(column) OVER (...)`	First value in window	✅ [Implemented]
`LAST_VALUE`	`LAST_VALUE(column) OVER (...)`	Last value in window	✅ [Implemented]
`NTH_VALUE`	`NTH_VALUE(column, n) OVER (...)`	Nth value in window	✅ [Implemented]

11.3 Regression Functions

Function	Syntax	Description	Status
`REGR_SLOPE`	`REGR_SLOPE(y, x)`	Slope of regression line	✅ [Implemented]
`REGR_INTERCEPT`	`REGR_INTERCEPT(y, x)`	Y-intercept	✅ [Implemented]
`REGR_R2`	`REGR_R2(y, x)`	R-squared value	✅ [Implemented]

11.4 Percentile Functions

Function	Syntax	Description	Status
`PERCENTILE_CONT`	`PERCENTILE_CONT(fraction, column)`	Continuous percentile	✅ [Implemented]
`PERCENTILE_DISC`	`PERCENTILE_DISC(fraction, column)`	Discrete percentile	✅ [Implemented]

12. AI/ML Functions

12.1 Embedding Functions

Function	Syntax	Description	Status
`EMBED`	`EMBED(text)`	Generate text embedding	✅ [Implemented]
`EMBEDDING`	`EMBEDDING(text)`	Alias for EMBED	✅ [Implemented]

Example:

-- Generate embeddings
INSERT INTO documents (content, embedding) 
VALUES ('AI is transformative', EMBED('AI is transformative'));

-- Find similar documents
SELECT * FROM documents 
ORDER BY embedding <=> EMBED('machine learning') 
LIMIT 10;

12.2 Similarity Functions

Function	Syntax	Description	Status
`COSINE_SIMILARITY`	`COSINE_SIMILARITY(vec1, vec2)`	Cosine similarity	✅ [Implemented]
`EUCLIDEAN_DISTANCE`	`EUCLIDEAN_DISTANCE(vec1, vec2)`	Euclidean distance	✅ [Implemented]
`DOT_PRODUCT`	`DOT_PRODUCT(vec1, vec2)`	Dot product	✅ [Implemented]
`MANHATTAN_DISTANCE`	`MANHATTAN_DISTANCE(vec1, vec2)`	Manhattan distance	✅ [Implemented]
`SEMANTIC_MATCH`	`SEMANTIC_MATCH(query, text, threshold)`	Semantic matching	✅ [Implemented]

12.3 NLP Functions

12.3.1 Text Summarization

Function	Syntax	Description	Status
`SUMMARIZE`	`SUMMARIZE(text [, max_length])`	Generate summary	✅ [Implemented]

12.3.2 Entity Extraction

Function	Syntax	Description	Status
`EXTRACT_ENTITIES`	`EXTRACT_ENTITIES(text [, types])`	Extract named entities	✅ [Implemented]
`EXTRACT_KEYWORDS`	`EXTRACT_KEYWORDS(text, count)`	Extract keywords	✅ [Implemented]

12.3.3 Sentiment Analysis

Function	Syntax	Description	Status
`SENTIMENT`	`SENTIMENT(text)`	Analyze sentiment	✅ [Implemented]
`SENTIMENT_ANALYSIS`	`SENTIMENT_ANALYSIS(text)`	Detailed sentiment	✅ [Implemented]

12.3.4 Translation

Function	Syntax	Description	Status
`TRANSLATE`	`TRANSLATE(text, target_language)`	Translate text	✅ [Implemented]

12.4 Generation Functions

Function	Syntax	Description	Status
`GENERATE`	`GENERATE(prompt [, options])`	Generate text	✅ [Implemented]

Example:

-- Generate product descriptions
SELECT name, GENERATE('Write a product description for: ' || name) as description
FROM products;

-- Generate with options
SELECT GENERATE('Summarize this article', '{"max_tokens": 100}') FROM articles;

12.5 Classification Functions

Function	Syntax	Description	Status
`CLASSIFY`	`CLASSIFY(text, categories)`	Classify text	✅ [Implemented]
`PREDICT`	`PREDICT(input)`	Make prediction	✅ [Implemented]

13. Vector Operations

13.1 Vector Operators

Operator	Syntax	Description	Status
`<=>`	`vec1 <=> vec2`	Cosine similarity	✅ [Implemented]
`<->`	`vec1 <-> vec2`	Euclidean distance	✅ [Implemented]
`<#>`	`vec1 <#> vec2`	Inner product	✅ [Implemented]

13.2 Vector Functions

Function	Syntax	Description	Status
`VECTOR_ADD`	`VECTOR_ADD(vec1, vec2)`	Vector addition	✅ [Implemented]
`VECTOR_SUBTRACT`	`VECTOR_SUBTRACT(vec1, vec2)`	Vector subtraction	✅ [Implemented]
`VECTOR_MULTIPLY`	`VECTOR_MULTIPLY(vec, scalar)`	Scalar multiplication	✅ [Implemented]
`VECTOR_MAGNITUDE`	`VECTOR_MAGNITUDE(vec)`	Vector magnitude	✅ [Implemented]
`VECTOR_NORMALIZE`	`VECTOR_NORMALIZE(vec)`	Normalize vector	✅ [Implemented]
`VECTOR_DOT`	`VECTOR_DOT(vec1, vec2)`	Dot product	✅ [Implemented]
`VECTOR_CENTROID`	`VECTOR_CENTROID(vec_column)`	Centroid of vectors	✅ [Implemented]

13.3 Vector Search

Function	Syntax	Description	Status
`VECTOR_SEARCH`	`VECTOR_SEARCH(embedding, query, k)`	k-NN search	✅ [Implemented]
`SEMANTIC_SEARCH`	`SEMANTIC_SEARCH(embedding, query, k)`	Semantic search	✅ [Implemented]

14. Multimedia Functions

14.1 Audio Processing

Function	Syntax	Description	Status
`TRANSCRIBE`	`TRANSCRIBE(audio [, model])`	Transcribe audio	✅ [Implemented]
`DURATION`	`DURATION(audio)`	Audio duration	✅ [Implemented]
`EXTRACT_AUDIO`	`EXTRACT_AUDIO(video)`	Extract from video	✅ [Implemented]

14.2 Video Processing

Function	Syntax	Description	Status
`EXTRACT_FRAMES`	`EXTRACT_FRAMES(video, interval)`	Extract frames	✅ [Implemented]
`DIMENSIONS`	`DIMENSIONS(video)`	Video dimensions	✅ [Implemented]
`GENERATE_THUMBNAIL`	`GENERATE_THUMBNAIL(video [, w, h])`	Create thumbnail	✅ [Implemented]

14.3 Image Processing

Function	Syntax	Description	Status
`RESIZE_IMAGE`	`RESIZE_IMAGE(image, width, height)`	Resize image	✅ [Implemented]
`CONVERT_FORMAT`	`CONVERT_FORMAT(image, format)`	Convert format	✅ [Implemented]

14.4 Document Processing

Function	Syntax	Description	Status
`EXTRACT_TEXT`	`EXTRACT_TEXT(document)`	Extract text/OCR	✅ [Implemented]
`OCR_CONFIDENCE`	`OCR_CONFIDENCE(image)`	OCR confidence score	✅ [Implemented]

14.5 Multimedia Analysis

Function	Syntax	Description	Status
`MULTIMEDIA_SIMILARITY`	`MULTIMEDIA_SIMILARITY(media1, media2)`	Compare media	✅ [Implemented]
`PROCESS_MULTIMEDIA`	`PROCESS_MULTIMEDIA(data)`	Auto-process media	✅ [Implemented]
`EXTRACT_METADATA`	`EXTRACT_METADATA(media)`	Get metadata	✅ [Implemented]
`DETECT_FORMAT`	`DETECT_FORMAT(data)`	Detect file format	✅ [Implemented]

Part V: Advanced Features

15. AutoML Extensions

15.1 Experiment Management

Create Experiment:

CREATE AUTOML EXPERIMENT experiment_name
USING (SELECT * FROM training_data)
TARGET target_column
OPTIONS (
    task_type = 'classification',
    algorithms = ['random_forest', 'gradient_boosting', 'neural_network'],
    max_trials = 50,
    validation_split = 0.2
);

Available Algorithms:

Algorithm	Best For	Speed	Accuracy	Status
`logistic_regression`	Binary classification	Fast	Good	✅ [Implemented]
`linear_regression`	Simple regression	Fast	Good for linear	✅ [Implemented]
`random_forest`	General purpose	Medium	High	✅ [Implemented]
`gradient_boosting`	High accuracy	Slow	Very High	✅ [Implemented]
`neural_network`	Complex patterns	Slow	High	✅ [Implemented]
`knn`	Local patterns	Fast	Medium	✅ [Implemented]
`svm`	Binary classification	Medium	High	✅ [Implemented]
`naive_bayes`	Text classification	Very Fast	Medium	✅ [Implemented]

15.2 Model Training

TRAIN MODEL model_name
FROM training_data
TARGET target_column
TYPE classification
OPTIONS (algorithm='RandomForest', max_depth=10);

15.3 Model Deployment

-- Deploy model from experiment
DEPLOY MODEL model_name
FROM experiment_name;

-- Show deployed models
SHOW MODELS;

-- Describe model
DESCRIBE MODEL model_name;

15.4 Prediction

-- Make predictions
PREDICT prediction_column
USING model_name
FROM (SELECT * FROM new_data);

Status: ✅ [Implemented] | Conformance: [Enhanced]

16. Natural Language to SQL

16.1 ASK Statement

Syntax:

ASK "natural language query";

Example:

ASK "Show all customers from USA who made purchases in 2024";
-- Generates: SELECT * FROM customers WHERE country = 'USA' 
--            AND id IN (SELECT customer_id FROM orders 
--                      WHERE YEAR(order_date) = 2024)

16.2 EXPLAIN NATURAL

Syntax:

EXPLAIN NATURAL "natural language query";

Example:

EXPLAIN NATURAL "Find top 10 products by revenue this quarter";
-- Returns both the generated SQL and explanation of the logic

16.3 Context Management

ASK "Show sales by region" WITH CONTEXT (
    tables = ['sales', 'regions'],
    terminology = ['revenue' = 'SUM(amount)']
);

16.4 Confidence Scoring

The system provides confidence scores for generated queries:

High confidence (>0.8): Execute automatically
Medium confidence (0.5-0.8): Show query for confirmation
Low confidence (<0.5): Suggest alternatives

Status: ✅ [Implemented] | Conformance: [Enhanced]

17. Backup and Recovery

17.1 BACKUP Operations

Syntax:

BACKUP {DATABASE | TABLES table_list} TO 'path'
WITH (
    TYPE = {'FULL' | 'INCREMENTAL' | 'DIFFERENTIAL'},
    COMPRESSION = {'NONE' | 'GZIP' | 'LZ4' | 'ZSTD'},
    ENCRYPTION = {TRUE | FALSE}
);

Examples:

-- Full database backup
BACKUP DATABASE TO '/backup/full_backup.synapcores
' 
WITH (TYPE = 'FULL', COMPRESSION = 'ZSTD', ENCRYPTION = TRUE);

-- Incremental backup
BACKUP DATABASE TO '/backup/incremental.synapcores
' 
WITH (TYPE = 'INCREMENTAL', BASE_BACKUP = '/backup/full_backup.synapcores
');

-- Table backup
BACKUP TABLES users, orders TO '/backup/tables.synapcores
';

17.2 RESTORE Operations

Syntax:

RESTORE {DATABASE | TABLES table_list} FROM 'path'
WITH (
    OVERWRITE = {TRUE | FALSE},
    VERIFY_CHECKSUMS = {TRUE | FALSE}
);

17.3 CLONE Operations

-- Clone database
CLONE DATABASE source_db TO target_db;

-- Clone table with filter
CLONE TABLE sales TO sales_backup WHERE date > '2024-01-01';

Status: ✅ [Implemented] | Conformance: [Enhanced]

Part VII: Conformance and Compliance

25. Conformance Levels

25.1 Core Conformance

Required features for basic SYNAPCORES SQLv2 compliance:

Data Types: All standard SQL types
DDL: CREATE, ALTER, DROP for tables and indexes
DML: INSERT, UPDATE, DELETE, SELECT
Transactions: BEGIN, COMMIT, ROLLBACK
Basic Functions: Standard SQL functions
Authentication: JWT-based auth
Tenant Isolation: Multi-tenancy support

25.2 Enhanced Conformance

Additional features for enhanced compliance:

AI Functions: Embedding, NLP, generation
Vector Operations: Similarity search
Partitioning: RANGE, LIST, HASH
Backup/Restore: Full and incremental
Natural Language: ASK and EXPLAIN NATURAL
System Metadata: Virtual tables
Data Sync: External database sync

25.3 Full Conformance

All optional features implemented:

AutoML: Complete ML pipeline
Multimedia: Audio, video, image processing
Recipes: Templated workflows
Integrations: All external systems
Advanced Analytics: Window functions, statistics

Appendices

Appendix A: Complete Function Reference

[See Part IV for complete function listings with signatures and examples]

Appendix B: Error Codes and Messages

B.1 Error Code Categories

Range	Category	Description
1000-1999	Parser Errors	SQL syntax and parsing
2000-2999	Execution Errors	Query execution failures
3000-3999	Storage Errors	Database storage issues
4000-4999	Network Errors	Connection and API errors
5000-5999	AI/ML Errors	AI service failures
6000-6999	Auth Errors	Authentication/authorization
7000-7999	System Errors	System-level failures

B.2 Common Error Codes

Code	Message	Description
1001	Syntax Error	Invalid SQL syntax
2001	Table Not Found	Referenced table doesn't exist
2002	Column Not Found	Referenced column doesn't exist
3001	Storage Full	Insufficient storage space
4001	Connection Failed	Cannot connect to service
5001	Model Not Found	AI model unavailable
6001	Unauthorized	Invalid authentication
7001	Internal Error	Unexpected system error

Appendix C: Migration Guide

C.1 From Standard SQL

-- Standard SQL
SELECT * FROM users WHERE age > 18;

-- SYNAPCORES
 SQLv2 (compatible)
SELECT * FROM users WHERE age > 18;

-- With AI extension
SELECT *, SENTIMENT(bio) as sentiment 
FROM users WHERE age > 18;

C.2 From PostgreSQL

Key differences:

Vector type syntax: VECTOR(n) instead of pgvector
AI functions built-in
Different system tables

C.3 From MySQL

Key differences:

Multimedia types native
No need for stored procedures for AI
Different date functions

Appendix D: Best Practices

D.1 Query Optimization

Use appropriate indexes for frequently queried columns
Partition large tables by date or category
Cache embeddings to avoid recomputation
Batch vector operations for efficiency
Use streaming for large result sets

D.2 Partitioning Strategies

Time-based data: Use RANGE partitioning by date
Geographic data: Use LIST partitioning by region
User data: Use HASH partitioning by user_id
Hybrid approach: Combine partitioning strategies

D.3 AI Function Usage

Cache embeddings: Store computed embeddings
Batch processing: Process multiple items together
Model selection: Choose appropriate models for tasks
Threshold tuning: Adjust similarity thresholds
Async processing: Use async for long operations

Appendix E: Glossary

Term	Definition
**SYNAPCORES
**	AI-native Database
AST	Abstract Syntax Tree
CTE	Common Table Expression
DDL	Data Definition Language
DML	Data Manipulation Language
Embedding	Vector representation of data
IVF	Inverted File Index
JWT	JSON Web Token
k-NN	k-Nearest Neighbors
NLP	Natural Language Processing
OCR	Optical Character Recognition
RBAC	Role-Based Access Control
Recipe	Templated SQL workflow
TCL	Transaction Control Language
UDF	User-Defined Function
Vector	Fixed-dimension array of numbers

Appendix F: Change History

Version 2.1 (September 2025)

Added complete data synchronization
Enhanced system metadata tables
Improved partitioning with catalog
Full backup/restore implementation
Natural language to SQL
Recipe system with AI generation
Comprehensive multimedia support

Version 2.0 (January 2025)

Complete rewrite based on implementation
Added AutoML integration
Enhanced vector operations
Multimedia data types

Version 1.0 (December 2024)

Initial specification
Basic SQL support
Core AI functions

Index

[Alphabetical index of major topics - to be generated]

End of SYNAPCORES SQLv2 Standard Specification

This specification is released under the Creative Commons Attribution 4.0 International (CC BY 4.0) license.

You are free to copy, modify, and distribute this specification, provided that proper attribution is given to the original author(s).

The SynapCores database implementation of SQLv2 is a separate proprietary product and is not covered by this license. Unauthorized use, reproduction, or distribution of SynapCores' proprietary software is strictly prohibited.

For details, visit: https://creativecommons.org/licenses/by/4.0/

Uh oh!

FilesExpand file tree

SQLv2Specs.md

Latest commit

History

SQLv2Specs.md

File metadata and controls

SYNAPCORES

Part I: Foundation

1. Introduction

1.1 Purpose and Scope

1.2 Relationship to SQL Standards

1.3 Document Organization

1.4 Audience and Prerequisites

SynapCores System specs is changing continuosly as we sprint to release.

2. Normative References

2.1 SQL Standards Referenced

2.2 AI/ML Framework Standards

2.3 Multimedia Format Standards

2.4 Security and Encryption Standards

3. Terms, Definitions, and Conventions

3.1 Terminology

3.2 Notation Conventions

3.3 Syntax Diagrams

3.4 Implementation Status Indicators

3.5 Conformance Levels

Part II: Data Model and Types

4. Data Types

4.1 Standard SQL Data Types

4.1.1 Numeric Types

4.1.2 Character String Types

4.1.3 Binary String Types

4.1.4 Date/Time Types

4.1.5 Boolean Type

4.2 Extended Data Types

4.2.1 JSON/JSONB Types

4.2.2 UUID Type

4.3 AI-Native Data Types

4.3.1 Vector Type

4.4 Multimedia Data Types

4.4.1 Audio Types and Formats

4.4.2 Video Types and Formats

4.4.3 Image Types and Formats

4.4.4 Document Types

5. Schema Objects

5.1 Databases

5.2 Tables

5.2.1 Regular Tables

5.2.2 Partitioned Tables

5.2.3 System Tables

5.3 Indexes

5.4 Constraints

5.5 Triggers

5.6 Stored Procedures

Part III: SQL Language

6. Data Definition Language (DDL)

6.1 CREATE Statements

6.1.1 CREATE DATABASE

6.1.2 CREATE TABLE

6.1.3 CREATE INDEX

6.2 ALTER Statements

6.3 DROP Statements

6.4 Table Partitioning DDL

6.4.1 RANGE Partitioning

6.4.2 LIST Partitioning

6.4.3 HASH Partitioning

7. Data Manipulation Language (DML)

7.1 INSERT Statement

7.2 UPDATE Statement

7.3 DELETE Statement

7.4 SELECT Statement

7.4.1 Basic SELECT

7.4.2 Joins

7.4.3 Subqueries

7.4.4 Common Table Expressions (CTEs)

7.4.5 Window Functions

8. Transaction Control Language (TCL)

8.1 BEGIN TRANSACTION

8.2 COMMIT

8.3 ROLLBACK