Диалект FileDB SQL

Эта страница — единый справочник по SQL, который понимает FileDB (движок v2.47.0): все конструкции и примеры собраны в одном месте. Подробности по каждому разделу отдельно — на страницах DDL, DML, SELECT, Функции и выражения, Транзакции.

Типы данных

INT, FLOAT, TEXT, BOOL. Под капотом каждое значение сериализуется в JSON; значения крупнее ~2 KB автоматически уходят на overflow-страницы — в одной ячейке можно хранить десятки MB.

DDL: таблицы, ограничения, индексы

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name TEXT NOT NULL,
  age INT,
  created_at TEXT
);

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  status TEXT DEFAULT 'new',
  amount FLOAT CHECK (amount >= 0),
  FOREIGN KEY (customer_id) REFERENCES users (id) ON DELETE RESTRICT
);

DROP TABLE users;

FOREIGN KEY и CHECK применяются движком (enforced) начиная с v2.38.0 — раньше только парсились. Ограничения FK: одна колонка (составные FK не реализованы), ссылка обязана указывать на PRIMARY KEY или UNIQUE-колонку в уже существующей таблице.

Индексы

CREATE INDEX idx_customers_city ON customers (city);
CREATE UNIQUE INDEX idx_users_email ON users (email);
CREATE INDEX idx_bookings_room_day ON bookings (room, day); -- составной индекс
DROP INDEX idx_users_name;

ALTER TABLE

ALTER TABLE users ADD COLUMN nickname TEXT;
ALTER TABLE users DROP COLUMN nickname;
ALTER TABLE users RENAME TO accounts;
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES users (id);

Views

Обновляемые представления, с v2.34 (CREATE OR REPLACE — с v2.35).

CREATE OR REPLACE VIEW product_revenue AS
  SELECT product_id, SUM(quantity * unit_price) AS revenue
  FROM order_items
  GROUP BY product_id;

SELECT product_id, revenue FROM product_revenue ORDER BY revenue DESC LIMIT 5;

SELECT products.name, product_revenue.revenue
  FROM products JOIN product_revenue ON products.id = product_revenue.product_id
  ORDER BY product_revenue.revenue DESC LIMIT 5;

DROP VIEW IF EXISTS product_revenue;

DML: INSERT / UPDATE / DELETE

INSERT INTO users (name, age) VALUES ('Alice', 30);
INSERT INTO users (name, age) VALUES ('Bob', 25), ('Carol', 40); -- multi-row

UPDATE users SET age = age + 1 WHERE name = 'Alice';
UPDATE customers SET city = 'Сочи' WHERE name = 'Тест';

DELETE FROM users WHERE age < 18;
DELETE FROM customers WHERE name = 'Тест';

WHERE (в SELECT/UPDATE/DELETE) поддерживает любую комбинацию =, !=/<>, <, >, <=, >=, AND/OR/NOT, IN, LIKE/ILIKE, IS [NOT] NULL.

Upsert (диалект-зависимо)

Синтаксис upsert зависит от того, по какому протоколу пришёл запрос:

-- через PostgreSQL wire-протокол:
INSERT INTO users (id, name) VALUES (1, 'Alice')
  ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
INSERT INTO users (id, name) VALUES (1, 'Alice') ON CONFLICT (id) DO NOTHING;

-- через MySQL wire-протокол:
INSERT INTO users (id, name) VALUES (1, 'Alice')
  ON DUPLICATE KEY UPDATE name = VALUES(name);

SELECT: базовый синтаксис

SELECT name, age FROM users WHERE age > 18 ORDER BY age DESC LIMIT 10 OFFSET 20;
SELECT DISTINCT department FROM employees;
SELECT * FROM products WHERE price > 10000;
SELECT * FROM products ORDER BY price DESC LIMIT 3;

Агрегация

SELECT COUNT(*), SUM(quantity * unit_price), AVG(unit_price), MIN(unit_price), MAX(unit_price)
  FROM order_items;

SELECT customer_id, COUNT(*) AS cnt
  FROM orders
  GROUP BY customer_id
  HAVING COUNT(*) > 1;

SELECT product_id, SUM(quantity * unit_price) AS revenue
  FROM order_items
  GROUP BY product_id
  HAVING SUM(quantity * unit_price) >= 50000
  ORDER BY product_id;

JOIN

INNER JOIN — на N таблиц; LEFT/RIGHT/FULL OUTER JOIN — только для двух таблиц.

SELECT u.name, p.title, c.body
  FROM users u
  INNER JOIN posts p ON u.id = p.author_id
  INNER JOIN comments c ON p.id = c.post_id;

SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;
SELECT * FROM orders LEFT JOIN customers ON orders.customer_id = customers.id;
SELECT * FROM orders RIGHT JOIN customers ON orders.customer_id = customers.id;
SELECT * FROM orders FULL JOIN customers ON orders.customer_id = customers.id;

Подзапросы и производные таблицы

SELECT * FROM users WHERE id IN (SELECT user_id FROM admins);
SELECT * FROM orders WHERE customer_id = (SELECT id FROM customers WHERE name = 'Алиса Иванов');
SELECT * FROM order_items WHERE unit_price > (SELECT AVG(unit_price) FROM order_items);

-- производная таблица: FROM (SELECT ...) alias
SELECT d.department, d.avg_salary
  FROM (SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department) d
  WHERE d.avg_salary > 80000;

Коррелированные EXISTS/scalar-подзапросы декоррелируются планировщиком автоматически, где это возможно. Производная таблица с одним JOIN поддерживается; цепочка из 3+ таблиц с производной таблицей внутри — пока нет.

Window functions

Поддерживаются: ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, а также агрегаты (SUM/AVG/COUNT/MIN/MAX) в роли оконных функций — с PARTITION BY, ORDER BY и явными ROWS BETWEEN ... AND ... фреймами. Окно можно ставить поверх обычного SELECT, поверх JOIN и поверх GROUP BY.

SELECT name, department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employees;

SELECT id, quantity, SUM(quantity) OVER (ORDER BY id) AS running_qty
  FROM order_items LIMIT 10;

SELECT name, salary, LAG(salary, 1) OVER (ORDER BY salary) AS prev_salary
  FROM employees;

Не поддерживается: RANGE с числовым offset (нужна peer-group семантика) — только ROWS-фреймы.

CTE (`WITH ...`)

Один или несколько CTE в запросе, JOIN между CTE — без ограничения на количество JOIN'ов (лимит «максимум 1 JOIN» снят в v2.40.1). CTE — некорректирующий (WITH RECURSIVE не поддерживается), тело CTE — только простой SELECT.

WITH totals AS (
  SELECT product_id, SUM(quantity * unit_price) AS revenue
  FROM order_items GROUP BY product_id
)
SELECT * FROM totals ORDER BY revenue DESC LIMIT 5;

WITH totals AS (
  SELECT product_id, SUM(quantity * unit_price) AS revenue
  FROM order_items GROUP BY product_id
)
SELECT products.name, totals.revenue
  FROM products JOIN totals ON products.id = totals.product_id
  ORDER BY totals.revenue DESC LIMIT 5;

WITH line_rev AS (
  SELECT order_id, quantity * unit_price AS rev FROM order_items
),
order_totals AS (
  SELECT order_id, SUM(rev) AS total FROM line_rev GROUP BY order_id
)
SELECT AVG(total) AS avg_order_value FROM order_totals;

UNION / UNION ALL

UNION схлопывает дубликаты в накопленном результате, UNION ALL — конкатенирует без дедупликации. Имена колонок берутся из первой ветки; количество колонок в ветках должно совпадать.

SELECT city FROM customers
UNION
SELECT city FROM suppliers
ORDER BY city;

SELECT id, 'order' AS kind FROM orders
UNION ALL
SELECT id, 'refund' AS kind FROM refunds;

Функции и операторы

SELECT
  UPPER(name),
  LENGTH(body),
  COALESCE(nickname, name, 'anonymous'),
  CONCAT(first, ' ', last),
  SUBSTRING(body, 1, 100),
  TRIM(LOWER(email)),
  ROUND(price * 1.2, 2),
  CASE
    WHEN age < 18 THEN 'minor'
    WHEN age < 65 THEN 'adult'
    ELSE 'senior'
  END,
  NOW()
FROM users;

Функции: UPPER, LOWER, LENGTH, COALESCE, IFNULL, NULLIF, CONCAT, CONCAT_WS, SUBSTRING, TRIM, LTRIM, RTRIM, REPLACE, ABS, ROUND, FLOOR, CEIL, MOD, POWER, SQRT, NOW, CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME.

Операторы: +, -, *, /, %, || (конкатенация), =, !=/<>, <, >, <=, >=, AND, OR, NOT, IN, LIKE/ILIKE, IS [NOT] NULL.

Транзакции и уровни изоляции

BEGIN;
INSERT INTO accounts (id, balance) VALUES (1, 100);
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT; -- или ROLLBACK

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- ...
COMMIT;

С v2.38 поддерживаются 4 уровня изоляции: READ UNCOMMITTED, READ COMMITTED (по умолчанию), REPEATABLE READ/SNAPSHOT (BEGIN фиксирует снапшот), SERIALIZABLE (алиас SNAPSHOT). MVCC — undo-based (v2.38). Запись в базу сериализуется по SQLite-подобной модели: db-wide writer lock (DEFERRED/IMMEDIATE), конкурентные writer'ы не переплетаются. WAL с fsync на каждом commit гарантирует, что закоммиченная транзакция переживёт сбой процесса.

Диалектные особенности wire-протоколов

FileDB отдаёт один и тот же движок по HTTP/RPC, PostgreSQL wire-протоколу и MySQL wire-протоколу — но строковые литералы экранируются по правилам того протокола, по которому пришёл запрос: PostgreSQL понимает только удвоенную одинарную кавычку (обратный слеш в PG-строках — обычный символ), MySQL — полный набор бэкслеш-эскейпов (для кавычек, перевода строки, табуляции, нуля и т.д.) плюс удвоенную одинарную кавычку. С v2.44 это гарантированно единообразно как для одиночного, так и для многострочного INSERT ... VALUES (...), (...), и для литералов в ON DUPLICATE KEY UPDATE.