cncdb

O'rganish uchun yozilgan SQL ma'lumotlar bazasi. Append-only log (Bitcask uslubi) ustiga to'liq SQL qatlami qurilgan: tokenizer → parser → executor → storage. Dependency yo'q, faqat Node.js.

Baza fayli kengaytmasi ixtiyoriy — .cncdb shunchaki standart (SQLite'ning .db / .sqlite kabi). node web.js 8080 mening_bazam.sqdb deb istalgan nom bering; Studio o'sha kengaytmani avtomatik tanadi.

Postgres / Oracle darajasidagi haqiqiy baza emas (query optimizer, MVCC, WAL replikatsiya, PL/SQL yo'q), lekin ularning asosiy g'oyalari — rollar/ruxsatlar (GRANT/REVOKE) ham — shu yerda ishlaydigan holda ko'rsatilgan.

Ishga tushirish

node web.js 8080 mydb.cncdb        # cncdb Studio — brauzer UI (pgAdmin uslubida)
node web.js 8080 mydb.cncdb --auth # ... rol + parol bilan kirish
node demo.js                       # to'liq namoyish (DDL, JOIN, agregat, tranzaksiya, ...)
node test.js                       # 107 ta test
node cli.js mydb.cncdb             # interaktiv SQL konsoli (REPL)
node server.js 5432 mydb.cncdb     # xom TCP server

npm start = node web.js, npm run demo, npm test, npm run cli.

cncdb Studio (veb interfeys)

node web.js → brauzerda http://localhost:8080. Material 3 (Google) uslubi, light/dark tema, dependency yo'q (ui/ — vanilla HTML/CSS/JS).

• Object tree — baza → jadvallar → ustunlar / indekslar
• SQL editor — ko'p tab, sintaksis bo'yash, autocomplete (jadval/ustun/kalit so'z), Ctrl+Enter = ishga tushirish
• Natija grid — saralash, bajarilish vaqti (ms), CSV / JSON eksport, grafik (bar/line/pie)
• Data browser — varaqlash, ustun bo'yicha filtr, katakni 2 marta bosib inline tahrirlash, qator qo'shish / o'chirish (PK bor jadvallarda)
• ER-diagramma — jadvallar + FK chiziqlari, siljitiladigan qutilar
• Visual "Create Table" — forma orqali ustun qo'shish, jonli SQL preview
• Import — CSV / JSON fayl → jadval (tur avtomatik aniqlanadi)
• SQL dump — butun bazani .sql fayl qilib yuklab olish
• Connection manager — app bar'dagi ro'yxatdan boshqa .cncdb ga o'tish
• Command palette — Ctrl+K, tez buyruqlar / jadvalga o'tish
• Tab holati saqlanadi — qayta yuklaganda ochiq tablar tiklanadi
• Structure / Dashboard / Transaction chip / Dark-Light tema

API: POST /api/exec {sql, params?}, GET /api/meta, POST /api/compact, POST /api/import, GET /api/dump, GET /api/databases, POST /api/open, POST /api/login, POST /api/logout, GET /api/whoami.

Xavfsizlik

• Server faqat 127.0.0.1 ga bog'lanadi (tashqi tarmoqdan yopiq). --host 0.0.0.0 bekor qiladi.
• node web.js 8080 db.cncdb --auth — Studio rol + parol so'raydi; har so'rov shu rol nomidan bajariladi; Compact/Import/Dump/baza almashtirish faqat superuser.
• Parollar scrypt bilan xeshlanadi. Parametrli so'rovlar (?) injection'dan himoya qiladi.
• UI fayllari Cache-Control: no-store bilan beriladi (eski keshlangan CSS/JS muammosi bo'lmaydi).
• To'liq foydalanuvchi qo'llanmasi: QOLLANMA.md.

Nimalar qo'llab-quvvatlanadi

| Bo'lim | SQL | |--------|-----| | DDL | CREATE TABLE, DROP TABLE [IF EXISTS], CREATE [UNIQUE] INDEX, DROP INDEX | | Turlar | INT/INTEGER, TEXT/VARCHAR, REAL/FLOAT, BOOL, TIMESTAMP, SERIAL | | Cheklovlar | PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, REFERENCES (FK), AUTOINCREMENT | | DML | INSERT (ko'p qator), UPDATE ... SET ... WHERE, DELETE ... WHERE | | SELECT | WHERE, GROUP BY, HAVING, ORDER BY (ASC/DESC, ko'p ustun), LIMIT/OFFSET, DISTINCT, ustun taxalluslari (AS) | | JOIN | INNER, LEFT, RIGHT, FULL, CROSS | | Operatorlar | = <> < <= > >=, AND OR NOT, IN, BETWEEN, LIKE (% _), IS [NOT] NULL, + - * / %, \|\| | | Agregatlar | COUNT (COUNT(*), COUNT(DISTINCT x)), SUM, AVG, MIN, MAX | | Funksiyalar | UPPER LOWER LENGTH TRIM SUBSTR ABS ROUND COALESCE TYPEOF NOW | | Tranzaksiya | BEGIN, COMMIT, ROLLBACK (atomic; ochiq tranzaksiya bo'lmasa har bir DML avtomatik atomic) | | Yordamchi | SHOW TABLES, DESCRIBE t, EXPLAIN <select> | | Sub-query | skalyar (SELECT ...), IN (SELECT ...), [NOT] EXISTS (...), korrelyatsion, FROM (SELECT ...) alias | | CASE | CASE WHEN ... THEN ... ELSE ... END va CASE expr WHEN ... | | To'plam | UNION [ALL], INTERSECT [ALL], EXCEPT [ALL] | | ALTER TABLE | ADD COLUMN, DROP COLUMN, RENAME COLUMN ... TO, RENAME TO | | VIEW | CREATE VIEW v AS SELECT ..., DROP VIEW | | Upsert | INSERT ... SELECT, INSERT ... ON CONFLICT (col) DO NOTHING \| DO UPDATE SET ... (EXCLUDED.col) | | Oyna funksiyalari | ROW_NUMBER() / RANK() / DENSE_RANK() OVER (PARTITION BY ... ORDER BY ...), SUM/COUNT/AVG/MIN/MAX(...) OVER (...) | | Parametrlar | ? — db.exec('... WHERE x = ?', [42]) (SQL injection'dan himoya) | | Rollar / ruxsatlar | CREATE USER ... [PASSWORD 'x'] [SUPERUSER], ALTER/DROP USER, GRANT SELECT,INSERT ON t TO u, REVOKE, SHOW USERS, SHOW GRANTS FOR u — enforcement executor'da |

Kod tuzilishi

database.js        <- ommaviy interfeys:  new Database(fayl).exec(sql)
src/
  tokenizer.js     <- SQL matn  -> tokenlar
  parser.js        <- tokenlar  -> AST (recursive descent)
  values.js        <- tip tizimi + uch qiymatli mantiq + ifoda hisoblash (CASE, subquery, param)
  executor.js      <- AST'ni bajaradi: SELECT quvuri, JOIN, GROUP BY, agregat, oyna, UNION, view
  storage.js       <- append-only log + RAM holati + tranzaksiya + ALTER + VIEW + compaction
  indexes.js       <- saralangan secondary index (range query uchun)
  unparse.js       <- AST -> SQL matn (view'lar va dump uchun)
  format.js        <- natijani terminal jadvali qilib chizish
cli.js             <- interaktiv REPL
web.js             <- cncdb Studio: HTTP API + import/dump/databases + statik server
ui/                <- brauzer UI (index.html, styles.css, app.js) — vanilla, dep yo'q
                      ERD, autocomplete, chart, command palette, filtr, import — hammasi shu 3 faylda
server.js / client.js  <- xom TCP server va mijoz
demo.js            <- namoyish,  test.js  <- testlar
db.js / example.js <- ESKI kalit-qiymat versiyasi (tegilmagan, `npm run kv-example`)

Kod sifatida ishlatish

const Database = require('./database');
const db = new Database('mydb.cncdb');

db.exec(`CREATE TABLE users (id INT PRIMARY KEY, name TEXT NOT NULL, age INT)`);
db.exec(`INSERT INTO users VALUES (1, 'Ali', 28), (2, 'Vali', 34)`);

const res = db.exec(`SELECT name, age FROM users WHERE age > 30 ORDER BY age`);
// res = { command:'SELECT', columns:['name','age'], rows:[{name:'Vali',age:34}], rowCount:1 }

db.close();

Qanday ishlaydi (asosiy g'oyalar)

• Yozish — har bir INSERT/UPDATE/DELETE diskdagi log faylning oxiriga bitta JSON qator sifatida qo'shiladi (sequential write + fsync). Eski ma'lumot o'chirilmaydi.
• O'qish — barcha qatorlar RAM'dagi Map da (pk -> qator). SELECT shu ustida quvur (pipeline) sifatida bajariladi.
• Crash recovery — dastur ishga tushganda log boshidan oxirigacha o'qilib, RAM holati qayta quriladi (storage._replay).
• Tranzaksiya — BEGIN da holatning to'liq nusxasi olinadi; COMMIT da barcha yozuvlar bitta {"op":"txn", ops:[...]} qatori bilan atomic yoziladi; ROLLBACK da holat nusxadan tiklanadi.
• Index — kalit bo'yicha saralangan massiv + ikkilik qidiruv. WHERE indexed_col = ? / BETWEEN bo'lsa executor to'liq skan o'rniga indeksdan foydalanadi (EXPLAIN ko'rsatadi). Haqiqiy bazalarda bu B-Tree bo'ladi.
• Compaction — db.compact() log faylни qaytadan yozadi, faqat amaldagi qatorlarni qoldiradi.

Keyingi qadamlar (yana kengaytirish uchun)

1. B-Tree index — hozirgi saralangan massiv o'rniga (katta jadvalда tezroq).
2. Ko'p mijozli server — hozir web.js/server.js bitta global holat bilan ishlaydi; har ulanishga alohida tranzaksiya konteksti + lock/MVCC.
3. Nested / SAVEPOINT tranzaksiyalar.
4. Query planner — JOIN tartibini va indeks tanlovini narx asosida optimallashtirish.
5. WAL + partial-page recovery — hozir buzilgan oxirgi qator shunchaki tashlanadi.
6. Trigger, FK ON DELETE CASCADE, ko'p ustunli PK / index.

Bajarilgan (v0.3): sub-query · CASE · UNION/INTERSECT/EXCEPT · ALTER TABLE · VIEW · INSERT…SELECT · ON CONFLICT · oyna funksiyalari · ? parametrlar · ERD · autocomplete · chart · import/dump · command palette.

Bajarilgan (v0.4): rollar/ruxsatlar (CREATE USER · GRANT/REVOKE · SHOW USERS/GRANTS · scrypt parol) · --auth login · localhost bind · sozlanadigan fayl kengaytmasi (.cncdb) · in-app qo'llanma · yangi logo · no-cache.