SynapCores AIDB - Complete Features

1.1 Complete SQL Support

Standard SQL Operations:

• CREATE, DROP, ALTER tables with full DDL support
• INSERT, UPDATE, DELETE, SELECT with full DML support
• Complex JOIN operations (INNER, LEFT, RIGHT, FULL, CROSS)
• Subqueries and nested queries
• Common Table Expressions (CTEs) with WITH clause
• Recursive CTEs for hierarchical data
• UNION, INTERSECT, EXCEPT set operations
• Window functions (ROW_NUMBER, RANK, LAG, LEAD, etc.)
• GROUP BY with HAVING clauses
• ORDER BY with multiple columns and directions

Data Types:

Standard: INTEGER, BIGINT, REAL, DOUBLE, TEXT, VARCHAR, CHAR, BOOLEAN

Advanced: JSON, JSONB, UUID, TIMESTAMP, DATE, TIME, DECIMAL, BYTEA

AI-Native: VECTOR(dimensions), AUDIO, VIDEO, IMAGE, PDF

Constraints & Indexing:

• PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK constraints
• DEFAULT values and AUTO INCREMENT
• B-tree indexes for standard columns
• Vector indexes (HNSW) for similarity search
• Composite indexes across multiple columns

1.2 Advanced SQL Features

Stored Procedures, Triggers, Views, Transactions, Window Functions, and Statistical Functions are all supported.

Stored Procedures:

• CREATE PROCEDURE with IN/OUT/INOUT parameters
• Control flow: IF/THEN/ELSE, WHILE loops, FOR loops
• Variable declarations and assignments
• Error handling with RAISE statements
• HTTP requests to external APIs
• Shell command execution (with security controls)
• Procedure overloading by parameter types

Triggers:

• BEFORE/AFTER INSERT/UPDATE/DELETE triggers
• Row-level and statement-level triggers
• Access to NEW and OLD row values
• Conditional triggers with WHEN clauses
• Trigger chaining and execution ordering
• Persistent trigger storage with multi-tenant isolation

2.1 Vector Storage & Retrieval

-- Cosine distance
embedding <=> query_vector

-- Euclidean distance
embedding <-> query_vector

-- Inner product
embedding <#> query_vector

2.2 Vector Indexing

HNSW and Flat Indexes are supported for Approximate and Exact Nearest Neighbor searches respectively.

2.3 Embedding Generation

-- Generate embedding from text
SELECT EMBED('wireless headphones');
SELECT EMBED(product_description);

-- Specify model
SELECT EMBED(text, 'minilm');
SELECT EMBED(text, 'bert-base');

2.4 Semantic Search

-- Find similar products
SELECT product_name,
       COSINE_SIMILARITY(embedding, EMBED('laptop')) as score
FROM products
WHERE COSINE_SIMILARITY(embedding, EMBED('laptop')) > 0.7
ORDER BY score DESC
LIMIT 10;

3.1 Natural Language Processing

-- Sentiment analysis
SELECT SENTIMENT_ANALYSIS(review_text) FROM reviews;

-- Extract named entities
SELECT EXTRACT_ENTITIES(document_text) FROM documents;

-- Keyword extraction
SELECT EXTRACT_KEYWORDS(article_content, 5) FROM articles;

-- Text summarization
SELECT SUMMARIZE(long_text, 200) FROM articles;

-- Generate text with AI
SELECT GENERATE('Write a product description for '||product_name, options)
FROM product_templates;

3.2 Multimedia AI Functions

Synapcores supports audio, video, image and PDF processing functions like transcription, frame extraction, OCR, and more.

3.3 Vector Operations

Full support for vector algebra and similarity metrics in SQL.

4.1 SQL-Based Machine Learning

-- Train a classification model
CREATE EXPERIMENT churn_prediction AS
SELECT customer_id, age, tenure, monthly_charges, churned
FROM customers
WITH (
    task_type='binary_classification',
    target_column='churned',
    max_trials=50,
    algorithms=['random_forest', 'xgboost', 'neural_network']
);

Supported tasks include Classification, Regression, Time Series Forecasting, Clustering, and Anomaly Detection.

4.2 ML Algorithms & 4.3 AutoML Features

A wide range of algorithms and automated feature engineering capabilities are available.

-- View experiments
SHOW MODELS;

-- Start/stop experiments
START EXPERIMENT experiment_name;
STOP EXPERIMENT experiment_name;

-- Describe experiment results
DESCRIBE EXPERIMENT experiment_name;

-- Deploy best model
DEPLOY MODEL best_model FROM EXPERIMENT churn_prediction;

-- Make predictions
PREDICT churn_probability
USING churn_model
AS SELECT * FROM new_customers;

5.1 Conversational Queries

ASK "Show me all customers who made purchases last month"
ASK "What are the top 5 products by revenue?"
ASK "Find similar products to laptop computers"

5.2 Intelligent Query Generation

Schema Context Awareness:

• Automatic table and column name inference
• Relationship detection (foreign keys, joins)
• Domain-specific terminology mapping
• Common aliases and synonyms

Query Patterns:

• Aggregation queries ('total sales by category')
• Ranking queries ('top 10 customers by spend')
• Comparison queries ('compare revenue year over year')
• Trend analysis ('sales trend over time')
• Semantic search ('find products similar to X')

Dual-Mode Processing:

• LLM-based - uses AI service for complex queries
• Pattern-based - fast rule-based generation for simple queries
• Automatic fallback if LLM unavailable

6.1 Cost-Based Optimizer

Optimization Levels:

• Level 0: No optimization
• Level 1: Rule-based optimization only
• Level 2: Cost-based optimization (default, recommended)
• Level 3: Advanced optimizations with verbose logging

Phase 1 Rules (Always Applied):

• Constant folding - evaluates expressions at compile time
• Predicate simplification - simplifies WHERE conditions
• Predicate pushdown - moves filters closer to data source
• Projection pushdown - selects only needed columns early
• Merge filters - combines multiple WHERE clauses

Phase 2 Rules (Cost-Based):

• Join reordering - optimal join order with cost estimation
• Index selection - chooses best indexes automatically
• Join algorithm selection - picks hash/merge/nested loop joins

Phase 3 Rules (Advanced):

• Common subexpression elimination - avoids recomputing expressions
• Subquery decorrelation - optimizes correlated subqueries
• Window function optimization
• Aggregate optimization

6.2 Index Advisor

Automatic Index Recommendations:

• Analyzes query workload
• Suggests indexes for slow queries
• Estimates performance improvement
• Considers index maintenance cost

6.3 Query Plan Caching

Execution Plan Caching:

• Saves compiled query plans
• Cache invalidation on schema changes
• Plan reuse for repeated queries
• Reduces compilation overhead

7.1 SQL Dialect

AIDB SQL Syntax offers Standard SQL compliance, AI function extensions, vector operators, multimedia data types, and natural language query support.

7.2 Client SDKs

Connection options include a native socket protocol, a REST API, and WebSocket for real-time updates. SDKs are available for Python, Node.js/TypeScript, and Rust.

8.1 AI-Powered Applications

Enable semantic search, product recommendations, and Retrieval-Augmented Generation (RAG) for knowledge base Q&A, chatbots, and document analysis.

8.2 Predictive Analytics

Perform churn prediction, customer lifetime value forecasting, and sales projections directly within the database.