view release on metacpan or  search on metacpan

docs/EN.AmberDB-vs-SQL_User-Guide.md  view on Meta::CPAN

[Home](index.html)  â€¢  [About](EN.About_AmberDB.html)  â€¢  [Quick Start](index.html#quick-start)  â€¢  [Tutorial](EN.AmberDB_User-Guide.html)  â€¢  [Benchmark](EN.AmberDB-vs-SQLite_Benchmark.html)  â€¢&...

---

# AmberDB for SQL Developers: A Comparative Practical Guide

> This guide is designed for software engineers coming from traditional relational database management systems (RDBMS / SQL) who want to quickly build applications with AmberDB. Rather than focusing on abstract theory or database philosophy, it adopt...

---

## Table of Contents

1. [Essential Practical Notes for Developers (Quick Intro)](#1-essential-practical-notes-for-developers-quick-intro)
2. [Basic CRUD Operations (DML)](#2-basic-crud-operations-dml)
   - [2.1 INSERT (Single Record)](#21-insert-single-record)
   - [2.2 BULK INSERT (Batch Ingestion)](#22-bulk-insert-batch-ingestion)
   - [2.3 SELECT by ID (Primary Key Point Read)](#23-select-by-id-primary-key-point-read)
   - [2.4 UPDATE by ID (Single Record Mutation)](#24-update-by-id-single-record-mutation)
   - [2.5 BULK UPDATE (Batch Mutation)](#25-bulk-update-batch-mutation)
   - [2.6 DELETE (Single Record Deletion & Soft-Delete)](#26-delete-single-record-deletion--soft-delete)
   - [2.7 BULK DELETE (Batch Deletion)](#27-bulk-delete-batch-deletion)
   - [2.8 COUNT(*) (Table Record Count)](#28-count-table-record-count)
3. [Querying, Filtering, and Search (SELECT, WHERE, LIKE)](#3-querying-filtering-and-search-select-where-like)
   - [3.1 Exact Match (WHERE field = value)](#31-exact-match-where-field--value)
   - [3.2 Multi-Value IN Lookup (WHERE id IN (...))](#32-multi-value-in-lookup-where-id-in-)
   - [3.3 Text Search (WHERE col LIKE '%...%' / FTS)](#33-text-search-where-col-like--fts)
   - [3.4 Compound Multi-Field Filtering (WHERE A = x AND B = y)](#34-compound-multi-field-filtering-where-a--x-and-b--y)
   - [3.5 Pagination (LIMIT & OFFSET)](#35-pagination-limit--offset)
4. [Sorting (ORDER BY) and Multilingual Collation](#4-sorting-order-by-and-multilingual-collation)
   - [4.1 Numeric and Text Sorting](#41-numeric-and-text-sorting)
   - [4.2 Multilingual and Turkish Character Collation](#42-multilingual-and-turkish-character-collation)
5. [Relationships and JOINs: The Core Architectural Difference](#5-relationships-and-joins-the-core-architectural-difference)
   - [5.1 SQL Normalized Multi-Table + JOIN Model](#51-sql-normalized-multi-table--join-model)
   - [5.2 AmberDB Embedded Document + match_block Inverted Index Model](#52-amberdb-embedded-document--match_block-inverted-index-model)
6. [Grouping and Filter Facet Counters (GROUP BY vs. Facet)](#6-grouping-and-filter-facet-counters-group-by-vs-facet)
7. [Transaction Safety and ACID (COMMIT & ROLLBACK)](#7-transaction-safety-and-acid-commit--rollback)
8. [Data Definition (DDL: CREATE TABLE vs. AmberDB Schema)](#8-data-definition-ddl-create-table-vs-amberdb-schema)
9. [Built-in AmberDB Capabilities Beyond Standard SQL](#9-built-in-amberdb-capabilities-beyond-standard-sql)
10. [Quick Reference Cheat Sheet](#10-quick-reference-cheat-sheet)
11. [Terminology Glossary](#11-terminology-glossary)

---

## 1. Essential Practical Notes for Developers (Quick Intro)

Before writing queries, keep these 4 operational rules in mind:

1. **No External Database Server or Daemon:** There is no `mysqld` or `postgres` background daemon to start, configure, or connect to over TCP. AmberDB is an embedded Perl object running directly inside your application process:
   ```perl
   use AmberDB;
   my $adb = AmberDB->new(
       cfg  => { user => 'admin', language => 'en' },
       path => { dbase_dir => './dbstore' }
   );
   ```
2. **Records Are Native Perl Arrays (`@record`):** An SQL table row corresponds to a native Perl array `($id, $field1, $field2, ...)`.
3. **Index 0 is ALWAYS the Primary Key ID:** The first element (`$record[0]`) is the unique identifier. Pass `0` or `undef` when inserting; `insert_id` assigns and returns the auto-incremented ID.
4. **Positional Block Indices Instead of Column Names:** Instead of named columns (`name`, `price`), AmberDB uses positional block indices (`1`, `2`, `3`...). Each block can hold scalars, nested `ARRAY` references, or `HASH` references directly.

> [!NOTE]
> For deep architectural mechanics, disk file specs, and benchmarks, refer to [About AmberDB](EN.About_AmberDB.html), [Comprehensive Developer Guide](EN.AmberDB_User-Guide.html), and [Large-Scale Benchmark](EN.AmberDB-vs-SQLite_Benchmark.html).

---

## 2. Basic CRUD Operations (DML)

### 2.1 INSERT (Single Record)

* **SQL:**
  ```sql
  INSERT INTO products (name, price, brand, category_id)
  VALUES ('Sony WH-1000XM5', 149.99, 'Sony', 5);
  ```

* **AmberDB:**
  ```perl
  # [0] ID (0: auto-assigned), [1] Name, [2] Price, [3] Brand, [4] Category ID
  my $id = $adb->insert_id("products", 0, "Sony WH-1000XM5", 149.99, "Sony", 5);
  ```

* **Explanation:** `insert_id` accepts the table identifier, the ID (0 for new records), followed by the field values. It returns the generated numeric ID. All configured inverted indexes are updated synchronously.
* **Reference:** [User Guide Section 3.1: insert_id](EN.AmberDB_User-Guide.html#31-single-record-insertion-insert_id)