TIDESQL
░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░

A pluggable write and space optimized storage engine for MariaDB using TidesDB.

The pluggable engine is tested and supported on MariaDB 11.x, 12.x.


INSTALLATION
░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░

You can choose to run install.sh, for instructions run ./install.sh --help

To install in a specific location(e.g /data being NVMe disk)
./install.sh --mariadb-prefix /data/mariadb --tidesdb-prefix /data/tidesdb --build-dir /data/tidesql-build


The install script will build TidesDB and MariaDB from source.
You can specify the versions if you want.  The script will install everything
and make TidesDB available as an engine to utilize.

To skip storage engines you don't need (saves build time and reduces warnings):

./install.sh --list-engines                       # see what can be skipped
./install.sh --skip-engines mroonga,rocksdb,connect,spider,oqgraph,columnstore

Skippable engines:
  archive        Archive storage engine (read-only row-format tables)
  blackhole      Blackhole engine (accepts writes, stores nothing)
  columnstore    MariaDB ColumnStore (columnar analytics engine)
  connect        CONNECT engine (access external data sources)
  example        Example storage engine (test/demo only)
  federated      Legacy Federated engine (MODULE_ONLY)
  federatedx     FederatedX engine (query remote MySQL/MariaDB tables)
  mroonga        Mroonga full-text search engine (requires Groonga)
  oqgraph        Open Query GRAPH engine (graph computation)
  rocksdb        MyRocks / RocksDB LSM-tree engine
  sequence       Sequence engine (virtual auto-increment sequences)
  sphinx         SphinxSE engine (Sphinx full-text search integration)
  spider         Spider engine (sharding / federation)

InnoDB, Aria, MyISAM, and CSV cannot be skipped (server depends on them).

Below are manual install instructions for those who want to configure everything
themselves, though with the install script you can modify configuration files
after the fact.

LINUX (Ubuntu/Debian)
░░░░░░░░░░░░░░░░░░░░░░░░

1. Install dependencies:

   sudo apt update
   sudo apt install -y build-essential cmake ninja-build bison flex \
     libzstd-dev liblz4-dev libsnappy-dev libncurses-dev libssl-dev \
     libxml2-dev libevent-dev libcurl4-openssl-dev pkg-config

2. Install TidesDB library:

   git clone --depth 1 https://github.com/tidesdb/tidesdb.git tidesdb-lib
   cd tidesdb-lib && mkdir build && cd build
   cmake ..
   make -j$(nproc)
   sudo make install
   sudo ldconfig

3. Clone MariaDB and copy TidesDB storage engine:

   git clone --depth 1 --branch mariadb-12.2.2 https://github.com/MariaDB/server.git mariadb-server
   cd mariadb-server
   git submodule update --init --recursive
   cp -r /path/to/tidesql/tidesdb storage/
   cp -r /path/to/tidesql/mysql-test/suite/tidesdb mysql-test/suite/

4. Build MariaDB:

   mkdir build && cd build
   cmake ..
   make -j$(nproc)

5. Run tests (from the build directory):

   cd mysql-test
   perl mtr --suite=tidesdb --parallel=4 --force


MACOS
░░░░░░░░░░░░░░░░░░░░░░░░

1. Install dependencies:

   brew install cmake ninja bison snappy lz4 zstd openssl@3

2. Install TidesDB library:

   # Ensure Xcode SDK is used (not CommandLineTools)
   sudo xcode-select -s /Applications/Xcode.app/Contents/Developer
   export SDKROOT=$(xcrun --show-sdk-path)

   git clone --depth 1 https://github.com/tidesdb/tidesdb.git tidesdb-lib
   cd tidesdb-lib && mkdir build && cd build
   cmake .. -DCMAKE_OSX_SYSROOT=${SDKROOT} -DOPENSSL_ROOT_DIR=$(brew --prefix openssl@3)
   make -j$(sysctl -n hw.ncpu)
   sudo make install

3. Clone MariaDB and copy TidesDB storage engine:

   git clone --depth 1 --branch mariadb-12.2.2 https://github.com/MariaDB/server.git mariadb-server
   cd mariadb-server
   git submodule update --init --recursive
   cp -r /path/to/tidesql/tidesdb storage/
   cp -r /path/to/tidesql/mysql-test/suite/tidesdb mysql-test/suite/

4. Build MariaDB:

   # Remove CommandLineTools SDK to prevent header conflicts
   sudo rm -rf /Library/Developer/CommandLineTools/SDKs/MacOSX*.sdk

   export SDKROOT=$(xcrun --show-sdk-path)
   export CC=$(xcrun -find clang)
   export CXX=$(xcrun -find clang++)

   mkdir build && cd build
   cmake .. \
     -DCMAKE_C_COMPILER=${CC} \
     -DCMAKE_CXX_COMPILER=${CXX} \
     -DCMAKE_OSX_SYSROOT=${SDKROOT} \
     -DCMAKE_C_FLAGS="-Wno-nullability-completeness" \
     -DCMAKE_CXX_FLAGS="-Wno-nullability-completeness" \
     -DWITH_SSL=bundled \
     -DWITH_PCRE=bundled \
     -G Ninja
   cmake --build . --parallel $(sysctl -n hw.ncpu)

   Note: The CommandLineTools SDK removal is needed because CMake may find
   headers in /Library/Developer/CommandLineTools/SDKs/MacOSX*.sdk which
   causes C/C++ header path conflicts with libc++.

5. Run tests (from the build directory):

   cd mysql-test
   perl mtr --suite=tidesdb --parallel=4 --force


WINDOWS
░░░░░░░░░░░░░░░░░░░░░░░░

1. Install prerequisites:

   - Visual Studio 2022 with C++ workload
   - CMake (choco install cmake)
   - Strawberry Perl (choco install strawberryperl)
   - WinFlexBison (download from GitHub releases)

2. Install vcpkg dependencies:

   cd C:\vcpkg
   git pull
   .\vcpkg.exe install zstd:x64-windows lz4:x64-windows snappy:x64-windows pthreads:x64-windows

3. Install TidesDB library:

   git clone --depth 1 https://github.com/tidesdb/tidesdb.git tidesdb-lib
   cd tidesdb-lib
   mkdir build && cd build
   cmake .. -G "Visual Studio 17 2022" -A x64 ^
     -DCMAKE_TOOLCHAIN_FILE=C:/vcpkg/scripts/buildsystems/vcpkg.cmake ^
     -DCMAKE_INSTALL_PREFIX=C:/tidesdb
   cmake --build . --config Release
   cmake --install . --config Release

4. Clone MariaDB and copy TidesDB storage engine:

   git clone --depth 1 --branch mariadb-12.2.2 https://github.com/MariaDB/server.git mariadb-server
   cd mariadb-server
   git submodule update --init --recursive
   xcopy /E /I path\to\tidesql\tidesdb storage\tidesdb
   xcopy /E /I path\to\tidesql\mysql-test\suite\tidesdb mysql-test\suite\tidesdb

5. Build MariaDB:

   mkdir build && cd build
   cmake .. -G "Visual Studio 17 2022" -A x64 ^
     -DCMAKE_PREFIX_PATH="C:/tidesdb;C:/vcpkg/installed/x64-windows" ^
     -DCONNECT_DYNAMIC=NO
   cmake --build . --config RelWithDebInfo --parallel

   Note: CONNECT plugin is disabled (-DCONNECT_DYNAMIC=NO) because it
   requires libxml2 headers that may conflict with vcpkg installations.

6. Run tests:

   cd mysql-test
   perl mtr --suite=tidesdb --parallel=4 --force


ENABLE PLUGIN
░░░░░░░░░░░░░░░░░░░░░░░░

After building, enable the plugin in MariaDB:

   INSTALL SONAME 'ha_tidesdb';  -- works on all platforms



FEATURES
░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░

Core:
- MVCC transactions with per-table isolation (autocommit uses READ_COMMITTED;
  multi-statement transactions use the table's configured level)
- SQL savepoints (SAVEPOINT / ROLLBACK TO / RELEASE SAVEPOINT)
- START TRANSACTION WITH CONSISTENT SNAPSHOT
- Lock-free concurrency (no THR_LOCK, TidesDB handles isolation internally)
- Optional pessimistic row locking (tidesdb_pessimistic_locking=ON) for
  workloads that depend on row-level serialization (e.g. TPC-C).
  Locks are acquired by SELECT ... FOR UPDATE, UPDATE, DELETE, and INSERT
- LSM-tree storage with optional B+tree SSTable format
- Compression (NONE, LZ4, LZ4_FAST, ZSTD, Snappy)
- Bloom filters for fast key lookups
- Block cache for frequently accessed data
- Primary key (single and composite) and secondary index support
- Index Condition Pushdown (ICP) for secondary index scans
- REPLACE INTO and INSERT ... ON DUPLICATE KEY UPDATE
- AUTO_INCREMENT with O(1) atomic counter (no iterator per INSERT)
- TTL (time-to-live) per-row and per-table expiration
- Virtual/generated columns
- Online backup (SET GLOBAL tidesdb_backup_dir = '/path')
- Hard-link checkpoint (SET GLOBAL tidesdb_checkpoint_dir = '/path')
- OPTIMIZE TABLE (synchronous purge + compact via tidesdb_purge_cf)
- SHOW ENGINE TIDESDB STATUS (DB stats, memory, cache, conflict info)
- SHOW GLOBAL STATUS LIKE 'tidesdb%' (19 machine-readable status variables
  for monitoring tools: Prometheus, PMM, Datadog, etc.)
- Partitioning (RANGE, LIST, HASH, KEY)
- Data-at-rest encryption (MariaDB encryption plugin integration)
- Online DDL (instant metadata, inplace add/drop index, copy for columns)
- TRUNCATE TABLE as O(1) drop+recreate (instant regardless of table size)
- ANALYZE TABLE with detailed CF statistics (levels, keys, sizes, cache hit rate)
- Cached optimizer statistics (refreshed every 2 seconds from TidesDB)
- Full-text search (FULLTEXT indexes with BM25 ranking, natural language
  and boolean mode, charset-aware tokenizer, configurable BM25 parameters)
- Vector search (VECTOR indexes via MariaDB's MHNSW approximate nearest
  neighbor, Euclidean and cosine distance, INSERT/UPDATE/DELETE support)
- Spatial indexes (SPATIAL KEY with Hilbert curve encoding on LSM,
  MBRIntersects/MBRContains/MBRWithin/MBREquals/MBRDisjoint predicates)

Cloud / Object Store:
- S3-compatible object store backend (AWS S3, MinIO, GCS)
- Four-tier hot/warm/cold/frozen storage hierarchy
- Block-level range_get for point lookups on frozen SSTables (single HTTP request)
- Parallel SSTable prefetch for iterators
- Read-only replica mode with MANIFEST sync and WAL replay
- Primary promotion via SET GLOBAL tidesdb_promote_primary = ON
- Configurable WAL sync (per-commit for RPO=0, or bytes-threshold)
- LRU local file cache with paired klog/vlog eviction
- Upload retry with verification, download retry with backoff
- Kubernetes deployment with automated failover (see k8s/ directory)


CONFIGURATION
░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░

TidesDB stores its data as a sibling of the MariaDB data directory:
  <parent_of_datadir>/tidesdb_data


SYSTEM VARIABLES (SET GLOBAL tidesdb_...)
░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░

Read-only (set at startup):

  flush_threads             Background flush threads (default: 4)
  compaction_threads        Background compaction threads (default: 4)
  log_level                 DEBUG/INFO/WARN/ERROR/FATAL/NONE (default: DEBUG)
  block_cache_size          Global block cache in bytes (default: 256MB)
  max_open_sstables         Max cached SSTable files (default: 256)
  max_memory_usage          Global memory limit in bytes; 0 = auto (default: 0)
  data_home_dir             Override TidesDB data directory (default: auto)
  log_to_file               Write logs to file vs stderr (default: ON)
  log_truncation_at         Log file truncation size (default: 24MB; 0 = off)
  unified_memtable          Single shared WAL+memtable across all CFs (default: ON)
  unified_memtable_write_buffer_size  Write buffer for unified memtable (default: 128MB)
  unified_memtable_sync_mode         NONE/INTERVAL/FULL for unified WAL (default: FULL)
  unified_memtable_sync_interval     Sync interval in µs for INTERVAL mode (default: 128000)
  object_store_backend      LOCAL or S3 (default: LOCAL)
  s3_endpoint               S3 endpoint (e.g. s3.amazonaws.com)
  s3_bucket                 S3 bucket name
  s3_prefix                 S3 key prefix (e.g. production/db1/)
  s3_access_key             S3 access key ID
  s3_secret_key             S3 secret access key
  s3_region                 S3 region (NULL for MinIO)
  s3_use_ssl                Use HTTPS (default: ON)
  s3_path_style             Path-style URLs for MinIO (default: OFF)
  objstore_local_cache_max  Local cache size limit (default: 0 = unlimited)
  objstore_wal_sync_threshold  WAL sync byte threshold (default: 1MB)
  objstore_wal_sync_on_commit  Upload WAL on every commit (default: OFF)
  replica_mode              Read-only replica (default: OFF)
  replica_sync_interval     MANIFEST poll interval in us (default: 5000000)

Dynamic (SET GLOBAL at runtime):

  backup_dir                Set to a path to trigger online backup
  checkpoint_dir            Set to a path to trigger hard-link checkpoint
  print_all_conflicts       Log all TDB_ERR_CONFLICT events (default: OFF)
  pessimistic_locking       Enable plugin-level row locks for SELECT...FOR UPDATE,
                            UPDATE, DELETE, and INSERT (default: OFF). All
                            write-intent statements acquire per-row locks,
                            including autocommit. Uses a partitioned hash-table
                            lock manager with wait-for-graph deadlock detection.
                            Locks can cover non-existing PK values. Enable for
                            TPC-C or workloads needing row-level serialization.
  promote_primary           Set to ON to promote a replica to primary
                            (trigger variable, resets to OFF after promotion)
  fts_min_word_len          Minimum word length for FTS indexing (default: 3)
  fts_max_word_len          Maximum word length for FTS indexing (default: 84)
  fts_bm25_k1               BM25 k1 term-frequency saturation (default: 1.2)
  fts_bm25_b                BM25 b document-length normalization (default: 0.75)

Session (SET SESSION tidesdb_...):

  ttl                       Per-session TTL override in seconds (default: 0)
  skip_unique_check         Skip PK/unique checks on INSERT (default: OFF)
  default_compression       Default compression for new tables
  default_write_buffer_size Default write buffer for new tables (128MB)
  default_sync_mode         Default sync mode for new tables (FULL)
  (and other default_* variables for all table options)

Logging: TidesDB writes to <tidesdb_data>/LOG by default with automatic
truncation at 24 MB.  Set log_level to WARN or higher in production to
reduce log volume.


TABLE OPTIONS (CREATE TABLE ... ENGINE=TidesDB <option>=<value>)
░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░

These are per-table options set at creation time and applied to the column
family configuration.  ALTER TABLE ... <option>=<value> updates both the .frm
and the live column family via tidesdb_cf_update_runtime_config().

Storage:
  WRITE_BUFFER_SIZE         Memtable size before flush (default: 128MB)
  MIN_DISK_SPACE            Minimum free disk space (default: 100MB)
  KLOG_VALUE_THRESHOLD      Values larger than this go to vlog (default: 512)

Compression:
  COMPRESSION               NONE/SNAPPY/LZ4/ZSTD/LZ4_FAST (default: LZ4)

Bloom Filters:
  BLOOM_FILTER              Enable bloom filters (default: ON)
  BLOOM_FPR                 FPR in parts per 10000; 100 = 1% (default: 100)

Durability:
  SYNC_MODE                 NONE/INTERVAL/FULL (default: FULL)
  SYNC_INTERVAL_US          Sync interval in microseconds (default: 128000)

Isolation:
  ISOLATION_LEVEL           READ_UNCOMMITTED/READ_COMMITTED/REPEATABLE_READ/
                            SNAPSHOT/SERIALIZABLE (default: REPEATABLE_READ)

LSM Tree:
  USE_BTREE                 Use B+tree SSTable format (default: OFF)
  LEVEL_SIZE_RATIO          Level size multiplier (default: 10)
  MIN_LEVELS                Minimum LSM levels (default: 5)
  DIVIDING_LEVEL_OFFSET     Compaction dividing level offset (default: 2)
  L1_FILE_COUNT_TRIGGER     L1 file count trigger for compaction (default: 4)
  L0_QUEUE_STALL_THRESHOLD  L0 queue stall threshold (default: 20)

Skip List:
  SKIP_LIST_MAX_LEVEL       Max skip list level (default: 12)
  SKIP_LIST_PROBABILITY     Percentage; 25 = 0.25 (default: 25)

Block Indexes:
  BLOCK_INDEXES             Enable block indexes (default: ON)
  INDEX_SAMPLE_RATIO        Sample ratio for block index (default: 1)
  BLOCK_INDEX_PREFIX_LEN    Block index prefix length (default: 16)

TTL:
  TTL                       Default TTL in seconds, 0 = none (default: 0)

Encryption:
  ENCRYPTED                 Enable data-at-rest encryption (default: OFF)
  ENCRYPTION_KEY_ID         Encryption key ID (default: 1)


FIELD OPTIONS (per-column)
░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░

  TTL                       Marks column as per-row TTL source (seconds)

Example:
  CREATE TABLE t (
    id INT PRIMARY KEY,
    data VARCHAR(100),
    expires INT TTL=1
  ) ENGINE=TIDESDB TTL=3600 SYNC_MODE='NONE' COMPRESSION='ZSTD';


TESTING
░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░

Run all TidesDB tests (from the build directory):

  cd mysql-test
  perl mtr --suite=tidesdb

Run specific test:

  perl mtr --suite=tidesdb tidesdb_crud

Available tests (46 total, 260+ cases):
  tidesdb_alter_crash         Crash safety during ALTER TABLE operations
  tidesdb_analyze             ANALYZE TABLE with CF statistics output
  tidesdb_auto_increment      AUTO_INCREMENT edge cases (gaps, rollback, explicit values)
  tidesdb_backup              Online backup via tidesdb_backup_dir
  tidesdb_concurrent_conflict Concurrent write-write conflict handling
  tidesdb_concurrent_errors   Multi-connection error handling and recovery
  tidesdb_checkpoint          Hard-link checkpoint via tidesdb_checkpoint_dir
  tidesdb_consistent_snapshot START TRANSACTION WITH CONSISTENT SNAPSHOT
  tidesdb_crud                Basic CRUD operations
  tidesdb_data_home_dir       tidesdb_data_home_dir sysvar
  tidesdb_drop_create         Repeated DROP/CREATE/TRUNCATE cycles
  tidesdb_encryption          Data-at-rest encryption
  tidesdb_engine_convert      InnoDB <-> TidesDB engine migration (ALTER TABLE ENGINE)
  tidesdb_engine_status       SHOW ENGINE TIDESDB STATUS
  tidesdb_fulltext            Full-text search (FULLTEXT indexes, BM25, boolean mode)
  tidesdb_fulltext_phrase     FTS phrase queries ("exact phrase") and wildcard edge cases
  tidesdb_hidden_pk           Tables without explicit PK (hidden auto-generated row ID)
  tidesdb_index_stats         Index statistics and optimizer cost model
  tidesdb_info_schema         information_schema integration
  tidesdb_insert_conflict     Duplicate key detection and handling
  tidesdb_isolation           Isolation level behavior
  tidesdb_json                JSON querying + generated-column JSON indexing
  tidesdb_large_blob          Large BLOB/TEXT values (> 64KB, klog_value_threshold)
  tidesdb_load_data           Bulk insert path (multi-row INSERT, INSERT...SELECT)
  tidesdb_mixed_engine        Mixed-engine transactions (TidesDB + InnoDB in same txn)
  tidesdb_object_store        Object store integration (CRUD, indexes, txns, bulk, compaction)
  tidesdb_online_ddl          Online DDL (instant, inplace, copy)
  tidesdb_options             Table and field options
  tidesdb_partition           RANGE/LIST/HASH/KEY partitioning
  tidesdb_per_index_btree     Per-index USE_BTREE option
  tidesdb_pessimistic_forupdate    SELECT FOR UPDATE serialization (pessimistic locking)
  tidesdb_pessimistic_insert_lock  Pessimistic locking edge cases (non-existing rows, INSERT)
  tidesdb_pk_index            Primary key and secondary index scans
  tidesdb_rename              Table rename
  tidesdb_replace_iodku       REPLACE INTO and INSERT ON DUPLICATE KEY UPDATE
  tidesdb_savepoint           SQL SAVEPOINT / ROLLBACK TO / RELEASE SAVEPOINT
  tidesdb_spatial             Spatial indexes (Hilbert curve, MBR predicates)
  tidesdb_sql                 40 SQL cases (aggregates, JOINs, subqueries, etc.)
  tidesdb_status_vars         SHOW GLOBAL STATUS LIKE 'tidesdb%' monitoring variables
  tidesdb_stress              Concurrent transactions, conflicts, truncate cycles
  tidesdb_tpcc_contention     TPC-C district counter contention (pessimistic locking)
  tidesdb_ttl                 Time-to-live expiration
  tidesdb_unified_memtable    Unified memtable mode (shared WAL, cross-CF consistency)
  tidesdb_vcol                Virtual/generated columns
  tidesdb_vector              Vector search (MHNSW ANN, INSERT/UPDATE/DELETE)
  tidesdb_write_pressure          oltp_write_only pressure (multi-connection)

Run with verbose output:

  perl mtr --suite=tidesdb --verbose


QUICK TEST
░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░

  CREATE TABLE t (id INT PRIMARY KEY, data VARCHAR(100)) ENGINE=TidesDB;
  INSERT INTO t VALUES (1, 'hello'), (2, 'world');
  SELECT * FROM t;
  DROP TABLE t;


SANITIZER BUILDS
░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░

The TidesDB plugin supports plugin-level UBSAN/ASAN without rebuilding the
entire MariaDB server:

  cmake .. -DTIDESDB_WITH_UBSAN=ON   # UBSAN only
  cmake .. -DTIDESDB_WITH_ASAN=ON    # ASAN only
  cmake .. -DTIDESDB_WITH_ASAN=ON -DTIDESDB_WITH_UBSAN=ON  # both
  make -j$(nproc) tidesdb

  # Run tests with sanitizer error logging:
  UBSAN_OPTIONS=print_stacktrace=1:halt_on_error=0 \
    perl ./mtr --suite=tidesdb --parallel=4

For full ASAN coverage (requires full server rebuild):

  cmake .. -DWITH_ASAN=ON -DWITH_UBSAN=ON -DCMAKE_BUILD_TYPE=Debug
  make -j$(nproc)


LICENSE
░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░

GNU General Public License v2