nebula_db 2.0.0 copy "nebula_db: ^2.0.0" to clipboard
nebula_db: ^2.0.0 copied to clipboard

Published 57 days ago • Latest: 2.0.4
[unknown platforms]
→

Metadata

A production-grade relational database engine written in pure Dart. Features include WAL, MVCC, B-Tree indexing, SQL execution, and crash recovery.

More...

example/main.dart

// example/main.dart
//
// Comprehensive NebulaDB example — covers every production feature:
//
//   CREATE TABLE / DROP TABLE (IF NOT EXISTS / IF EXISTS)
//   INSERT, SELECT, WHERE, LIKE, AND/OR, BETWEEN, IN, IS NULL
//   GROUP BY + aggregates (COUNT, SUM, AVG, MIN, MAX)
//   ORDER BY (ASC/DESC), LIMIT, OFFSET, DISTINCT
//   INNER JOIN, LEFT JOIN (multi-join)
//   UPDATE (with explicit rowId in WAL — Flaw 3 fixed)
//   DELETE (with explicit rowId in WAL — Flaw 3 fixed)
//   Explicit transactions: BEGIN / COMMIT / ROLLBACK
//   ANALYZE + EXPLAIN (CBO query plan)
//   VACUUM (reclaim old MVCC versions)
//   Checkpoint + Persistence (data survives restart)
//
// Run:
//   cd nebula-db && dart run example/main.dart
//
// On the second run you will see "data already exists" — persistence works.

// ignore_for_file: avoid_print

import '../nebula_db.dart';

Future<void> main() async {
  _banner('NebulaDB — Production Example (v4)');

  // ══════════════════════════════════════════════════════════════════════
  //  1. Open the database
  // ══════════════════════════════════════════════════════════════════════
  _section('1. Open database');
  final db = await NebulaDB.open('./nebula_data');
  print('  Database opened at: ${db.dir}');

  // ══════════════════════════════════════════════════════════════════════
  //  2. Create tables (IF NOT EXISTS — safe for repeated runs)
  // ══════════════════════════════════════════════════════════════════════
  _section('2. CREATE TABLES');

  await db.execute('''
    CREATE TABLE IF NOT EXISTS users (
      id   INT  PRIMARY KEY,
      name TEXT,
      age  INT
    )
  ''');
  print('  Table "users" ready.');

  await db.execute('''
    CREATE TABLE IF NOT EXISTS orders (
      id      INT  PRIMARY KEY,
      user_id INT  INDEX,
      product TEXT,
      price   REAL
    )
  ''');
  print('  Table "orders" ready.');

  await db.execute('''
    CREATE TABLE IF NOT EXISTS audit_log (
      id       INT  PRIMARY KEY,
      event    TEXT,
      user_id  INT
    )
  ''');
  print('  Table "audit_log" ready.');

  // ══════════════════════════════════════════════════════════════════════
  //  3. INSERT (skip if data already exists from a previous run)
  // ══════════════════════════════════════════════════════════════════════
  _section('3. INSERT');

  final existingUsers = (await db.execute('SELECT * FROM users')).rows.length;
  if (existingUsers == 0) {
    // Users
    for (final row in [
      "(1, 'Ahmed',  25)",
      "(2, 'Sara',   30)",
      "(3, 'Ahmad',  22)",
      "(4, 'Omar',   28)",
      "(5, 'Layla',  35)",
      "(6, 'Nadia',  40)",
    ]) {
      await db.execute('INSERT INTO users (id, name, age) VALUES $row');
    }
    // Orders
    for (final row in [
      "(1, 1, 'Laptop',   1200.0)",
      "(2, 1, 'Mouse',      25.0)",
      "(3, 2, 'Keyboard',   75.0)",
      "(4, 4, 'Monitor',   300.0)",
      "(5, 5, 'Tablet',    450.0)",
      "(6, 2, 'Webcam',     90.0)",
    ]) {
      await db.execute('INSERT INTO orders (id, user_id, product, price) VALUES $row');
    }
    print('  Inserted 6 users + 6 orders.');
  } else {
    print('  Data already exists ($existingUsers users) — skipping insert.');
  }

  // ══════════════════════════════════════════════════════════════════════
  //  4. SELECT — basic queries
  // ══════════════════════════════════════════════════════════════════════
  _section('4. SELECT');

  _print('SELECT * FROM users');
  (await db.execute('SELECT * FROM users')).prettyPrint();

  _print('SELECT * FROM orders');
  (await db.execute('SELECT * FROM orders')).prettyPrint();

  // ══════════════════════════════════════════════════════════════════════
  //  5. WHERE — comparisons + logical operators
  // ══════════════════════════════════════════════════════════════════════
  _section('5. WHERE');

  _print("WHERE age = 25");
  (await db.execute('SELECT * FROM users WHERE age = 25')).prettyPrint();

  _print("WHERE age > 28");
  (await db.execute('SELECT * FROM users WHERE age > 28')).prettyPrint();

  _print("WHERE age >= 25 AND age <= 30");
  (await db.execute('SELECT * FROM users WHERE age >= 25 AND age <= 30')).prettyPrint();

  _print("WHERE age < 25 OR age > 35");
  (await db.execute('SELECT * FROM users WHERE age < 25 OR age > 35')).prettyPrint();

  // ══════════════════════════════════════════════════════════════════════
  //  6. LIKE
  // ══════════════════════════════════════════════════════════════════════
  _section('6. LIKE');

  _print("WHERE name LIKE 'Ah%'");
  (await db.execute("SELECT * FROM users WHERE name LIKE 'Ah%'")).prettyPrint();

  _print("WHERE name LIKE '%a%'");
  (await db.execute("SELECT * FROM users WHERE name LIKE '%a%'")).prettyPrint();

  // ══════════════════════════════════════════════════════════════════════
  //  7. BETWEEN / IN / IS NULL
  // ══════════════════════════════════════════════════════════════════════
  _section('7. BETWEEN / IN / IS NULL');

  _print('WHERE age BETWEEN 25 AND 32');
  (await db.execute('SELECT * FROM users WHERE age BETWEEN 25 AND 32')).prettyPrint();

  _print("WHERE name IN ('Ahmed', 'Omar', 'Layla')");
  (await db.execute("SELECT * FROM users WHERE name IN ('Ahmed', 'Omar', 'Layla')")).prettyPrint();

  _print('WHERE age IS NOT NULL');
  (await db.execute('SELECT id, name FROM users WHERE age IS NOT NULL')).prettyPrint();

  // ══════════════════════════════════════════════════════════════════════
  //  8. ORDER BY / LIMIT / OFFSET / DISTINCT
  // ══════════════════════════════════════════════════════════════════════
  _section('8. ORDER BY / LIMIT / OFFSET / DISTINCT');

  _print('ORDER BY age DESC');
  (await db.execute('SELECT * FROM users ORDER BY age DESC')).prettyPrint();

  _print('ORDER BY age ASC LIMIT 3');
  (await db.execute('SELECT * FROM users ORDER BY age ASC LIMIT 3')).prettyPrint();

  _print('ORDER BY age ASC LIMIT 2 OFFSET 2');
  (await db.execute('SELECT * FROM users ORDER BY age ASC LIMIT 2 OFFSET 2')).prettyPrint();

  _print('SELECT DISTINCT age FROM users ORDER BY age');
  (await db.execute('SELECT DISTINCT age FROM users ORDER BY age')).prettyPrint();

  // ══════════════════════════════════════════════════════════════════════
  //  9. GROUP BY + Aggregates
  // ══════════════════════════════════════════════════════════════════════
  _section('9. GROUP BY + Aggregates');

  _print('COUNT(*) FROM users');
  (await db.execute('SELECT COUNT(*) FROM users')).prettyPrint();

  _print('SUM / AVG / MIN / MAX of age');
  (await db.execute(
    'SELECT SUM(age), AVG(age), MIN(age), MAX(age) FROM users',
  )).prettyPrint();

  // ══════════════════════════════════════════════════════════════════════
  //  10. INNER JOIN
  // ══════════════════════════════════════════════════════════════════════
  _section('10. INNER JOIN');

  _print('users INNER JOIN orders ON users.id = orders.user_id');
  (await db.execute(
    'SELECT * FROM users INNER JOIN orders ON users.id = orders.user_id',
  )).prettyPrint();

  // ══════════════════════════════════════════════════════════════════════
  //  11. LEFT JOIN
  // ══════════════════════════════════════════════════════════════════════
  _section('11. LEFT JOIN');

  _print('users LEFT JOIN orders ON users.id = orders.user_id');
  (await db.execute(
    'SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id',
  )).prettyPrint();

  // ══════════════════════════════════════════════════════════════════════
  //  12. UPDATE  (FIX Flaw 3: WAL now stores explicit rowId)
  // ══════════════════════════════════════════════════════════════════════
  _section("12. UPDATE (Flaw 3 fixed — explicit rowId in WAL)");

  _print("UPDATE users SET age = 26 WHERE name = 'Ahmed'");
  final upd = await db.execute("UPDATE users SET age = 26 WHERE name = 'Ahmed'");
  print('  Affected rows: ${upd.affectedRows}');
  (await db.execute("SELECT * FROM users WHERE name = 'Ahmed'")).prettyPrint();

  _print("UPDATE orders SET price = 29.99 WHERE id = 2");
  await db.execute('UPDATE orders SET price = 29.99 WHERE id = 2');
  (await db.execute('SELECT * FROM orders WHERE id = 2')).prettyPrint();

  // ══════════════════════════════════════════════════════════════════════
  //  13. DELETE  (FIX Flaw 3: WAL now stores explicit rowId)
  // ══════════════════════════════════════════════════════════════════════
  _section("13. DELETE (Flaw 3 fixed — explicit rowId in WAL)");

  _print("DELETE FROM users WHERE id = 3");
  final del = await db.execute('DELETE FROM users WHERE id = 3');
  print('  Affected rows: ${del.affectedRows}');
  (await db.execute('SELECT * FROM users')).prettyPrint();

  // ══════════════════════════════════════════════════════════════════════
  //  14. Explicit transaction: BEGIN / COMMIT
  // ══════════════════════════════════════════════════════════════════════
  _section('14. Explicit transaction: BEGIN / COMMIT');

  await db.execute('BEGIN');
  await db.execute('INSERT INTO audit_log (id, event, user_id) VALUES (1, \'UPDATE age\', 1)');
  await db.execute('INSERT INTO audit_log (id, event, user_id) VALUES (2, \'DELETE user\', 3)');
  await db.execute('COMMIT');
  _print('SELECT * FROM audit_log (after COMMIT)');
  (await db.execute('SELECT * FROM audit_log')).prettyPrint();

  // ══════════════════════════════════════════════════════════════════════
  //  15. Explicit transaction: BEGIN / ROLLBACK
  // ══════════════════════════════════════════════════════════════════════
  _section('15. Explicit transaction: BEGIN / ROLLBACK');

  await db.execute('BEGIN');
  await db.execute('INSERT INTO audit_log (id, event, user_id) VALUES (99, \'PHANTOM\', 0)');
  // Verify phantom row IS visible within the transaction
  final withinTxn = (await db.execute('SELECT * FROM audit_log WHERE id = 99')).rows;
  print('  Rows visible within txn: ${withinTxn.length}  (should be 1)');
  await db.execute('ROLLBACK');
  // Verify phantom row is NOT visible after rollback
  final afterRollback = (await db.execute('SELECT * FROM audit_log WHERE id = 99')).rows;
  print('  Rows after ROLLBACK: ${afterRollback.length}  (should be 0)');

  // ══════════════════════════════════════════════════════════════════════
  //  16. transaction() helper
  // ══════════════════════════════════════════════════════════════════════
  _section('16. transaction() helper');

  await db.transaction(() async {
    await db.execute('INSERT INTO audit_log (id, event, user_id) VALUES (3, \'BULK-A\', 2)');
    await db.execute('INSERT INTO audit_log (id, event, user_id) VALUES (4, \'BULK-B\', 4)');
  });
  _print('SELECT * FROM audit_log (4 committed entries expected)');
  (await db.execute('SELECT * FROM audit_log')).prettyPrint();

  // ══════════════════════════════════════════════════════════════════════
  //  17. ANALYZE + EXPLAIN (CBO statistics)
  // ══════════════════════════════════════════════════════════════════════
  _section('17. ANALYZE + EXPLAIN');

  await db.analyze('users');
  await db.analyze('orders');
  print('  Stats available: users=${db.hasStats("users")}, orders=${db.hasStats("orders")}');

  _print('EXPLAIN SELECT * FROM users WHERE id = 1');
  (await db.execute('EXPLAIN SELECT * FROM users WHERE id = 1')).prettyPrint();

  _print('EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25');
  (await db.execute('EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25')).prettyPrint();

  // ══════════════════════════════════════════════════════════════════════
  //  18. VACUUM (reclaim old MVCC versions)
  // ══════════════════════════════════════════════════════════════════════
  _section('18. VACUUM');

  await db.vacuum();
  print('  Vacuum complete.');

  // ══════════════════════════════════════════════════════════════════════
  //  19. Error handling
  // ══════════════════════════════════════════════════════════════════════
  _section('19. Error handling');

  try {
    await db.execute('SELECT * FROM nonexistent_table');
    print('  ERROR: should have thrown!');
  } catch (e) {
    print('  Caught expected error (nonexistent table): OK');
  }

  try {
    await db.execute('CREATE TABLE users (id INT)'); // no IF NOT EXISTS
    print('  ERROR: should have thrown!');
  } catch (e) {
    print('  Caught expected error (duplicate table without IF NOT EXISTS): OK');
  }

  try {
    await db.execute(
        'INSERT INTO users (id, name, age) VALUES (1, \'Dup\', 99)');
    print('  Duplicate primary key inserted (engine allows — no PK constraint yet).');
  } catch (e) {
    print('  Caught expected error (duplicate pk): $e');
  }

  // ══════════════════════════════════════════════════════════════════════
  //  20. Checkpoint + Close  (data will survive restart)
  // ══════════════════════════════════════════════════════════════════════
  _section('20. CHECKPOINT + CLOSE');

  print('  Running explicit checkpoint...');
  await db.checkpoint();
  print('  Checkpoint complete. Dirty pages flushed, WAL truncated.');

  await db.close();
  print('  Database closed safely.');

  // ══════════════════════════════════════════════════════════════════════
  //  21. Verify persistence: reopen and query
  // ══════════════════════════════════════════════════════════════════════
  _section('21. Reopen + verify persistence');

  final db2 = await NebulaDB.open('./nebula_data');
  final userCount = (await db2.execute('SELECT COUNT(*) FROM users')).rows;
  print('  After reopen — COUNT(*) FROM users: ${userCount.first.values.first}');

  final orderCount = (await db2.execute('SELECT COUNT(*) FROM orders')).rows;
  print('  After reopen — COUNT(*) FROM orders: ${orderCount.first.values.first}');

  await db2.close();
  print('  Persistence verified successfully.');

  _banner('All done! Run again — data persists.');
}

// ── Helpers ──────────────────────────────────────────────────────────────────

void _banner(String msg) {
  final line = '='.padRight(64, '=');
  print('\n$line');
  print('  $msg');
  print(line);
}

void _section(String msg) {
  print('\n${'-'.padRight(64, '-')}');
  print('  $msg');
  print('-'.padRight(64, '-'));
}

void _print(String sql) {
  print('\n  SQL: $sql');
}

← Metadata

3
likes
0
points
21
downloads

Publisher

unverified uploader

Weekly Downloads

Metadata

A production-grade relational database engine written in pure Dart. Features include WAL, MVCC, B-Tree indexing, SQL execution, and crash recovery.

Repository (GitHub)
View/report issues

Topics

#database #sql #storage #embedded #engine

License

unknown (license)

More

Packages that depend on nebula_db

Back