Add Materialized Views support

[robfrank]

Add materialized views to ArcadeDB — a schema-level abstraction that stores the results of
a defining SQL SELECT query as a real DocumentType, with support for full refresh,
incremental (ON COMMIT) refresh, periodic refresh, and full SQL DDL syntax.

Materialized views trade storage for compute: pre-computing and persisting query results so
that repeated reads are fast, while providing mechanisms to keep the cached data in sync
with source data.

SQL Syntax

-- Create with manual refresh (default)

  CREATE MATERIALIZED VIEW SalesSummary
    AS SELECT customer, SUM(amount) AS total FROM Order GROUP BY customer;

-- Create with incremental refresh on source changes

CREATE MATERIALIZED VIEW ActiveUsers
  AS SELECT name, email FROM User WHERE active = true
  REFRESH ON COMMIT;

-- Create with periodic refresh

CREATE MATERIALIZED VIEW DailyStats
   AS SELECT date, COUNT(*) AS cnt FROM Event GROUP BY date
   REFRESH EVERY 5 MINUTE;

-- Refresh, drop, alter

  REFRESH MATERIALIZED VIEW SalesSummary;
  DROP MATERIALIZED VIEW [IF EXISTS] SalesSummary;
  ALTER MATERIALIZED VIEW SalesSummary REFRESH EVERY 1 HOUR;

-- Query like any other type

  SELECT * FROM SalesSummary WHERE total > 1000 ORDER BY total DESC;

Java API

  database.getSchema().buildMaterializedView()
      .withName("SalesSummary")
      .withQuery("SELECT customer, SUM(amount) AS total FROM Order GROUP BY customer")
      .withRefreshMode(MaterializedViewRefreshMode.MANUAL)
      .create();

  database.getSchema().getMaterializedView("SalesSummary").refresh();

Refresh Modes

• Mode: MANUAL
  Behavior: Only refreshed on explicit REFRESH MATERIALIZED VIEW command or .refresh() call
• Mode: ON COMMIT
  Behavior: Automatic: after a transaction commits changes to source types, the view is
  updated via post-commit callbacks. Simple queries get per-record incremental updates;
  complex queries (aggregation/GROUP BY/JOIN) trigger a full refresh
• Mode: PERIODIC
  Behavior: Background scheduler refreshes at a configurable interval (e.g., every 5 minutes)

Architecture

• Backing type: Each view is backed by a real DocumentType with standard buckets — queries
  use the normal execution pipeline with no query rewriting needed. Indexes can be added to
  the backing type.
• Schema persistence: View metadata (query, source types, refresh mode, status) stored in a
  "materializedViews" section of schema.json, separate from the backing type definition.
  Survives database close/reopen.
• Post-commit callbacks: A new general-purpose addAfterCommitCallback(Runnable) mechanism
  on TransactionContext enables batched, post-commit view updates. One transaction with 1,000
  inserts triggers a single refresh, not 1,000.
• Crash recovery: If the database crashes during a refresh (BUILDING status), the view is
  marked STALE on reopen and auto-refreshed.
• Incremental refresh classification: Defining queries are classified as "simple" (single
  type, no aggregation/GROUP BY/JOIN) or "complex" at creation time. Simple queries get
  efficient per-record updates via _sourceRid tracking; complex queries fall back to full
  refresh.
• Safety: The backing type cannot be dropped directly — DROP MATERIALIZED VIEW must be used
  instead.

[lvca]

A nice thing would be supporting queries in all the languages we have that return a Resultset, like OpenCypher:

.withQuery("opencypher", "SELECT customer, SUM(amount) AS total FROM Order GROUP BY customer")