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

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


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

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-11.4 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:

   ./mysql-test/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-11.4 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 mariadb-build && cd mariadb-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" \
     -DPLUGIN_TIDESDB=DYNAMIC \
     -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:

   ./mysql-test/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-11.4 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 ^
     -DPLUGIN_TIDESDB=DYNAMIC ^
     -DCMAKE_PREFIX_PATH="C:/tidesdb;C:/vcpkg/installed/x64-windows"
   cmake --build . --config RelWithDebInfo --parallel

   Note: CONNECT plugin is disabled 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 PLUGIN tidesdb SONAME 'ha_tidesdb.so';   -- Linux/macOS
   INSTALL PLUGIN tidesdb SONAME 'ha_tidesdb.dll';  -- Windows


CI/CD NOTES
-----------

For GitHub Actions workflows, see .github/workflows/mariadb-test.yml for
complete examples. Key findings:

- Linux: Straightforward build with system packages
- macOS: Must use Xcode SDK (not CommandLineTools) and suppress
  nullability-completeness warnings from Apple system headers
- Windows: Use vcpkg binary caching with VCPKG_DEFAULT_BINARY_CACHE
  environment variable for faster CI builds


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

Core:
- ACID transactions with commit, rollback, savepoints
- MVCC (Multi-Version Concurrency Control)
- LSM-tree storage with optional B+tree format (write-optimized)
- Compression (LZ4, ZSTD, Snappy)
- Bloom filters for fast key lookups
- Block cache for frequently accessed data
- Lock-free atomic operations for scalability

Optimizer Integration:
- Index Condition Pushdown (ICP)
- Multi-Range Read (DS-MRR) for batch key lookups
- Rowid Filter Pushdown for semi-joins
- Covering index scans (keyread optimization)
- Handler cloning for parallel operations
- LSM-tree specific cost model

Advanced:
- XA distributed transactions (two-phase commit)
- TTL (time-to-live) per-row expiration
- Spatial indexes (Z-order/Morton code)
- Foreign key constraints (CASCADE, SET NULL, self-referencing)
- Foreign key parent list for ALTER TABLE safety
- Full-text search indexing
- Virtual/generated columns
- Online backup
- Partitioning (RANGE, LIST, HASH, KEY)
- Data-at-rest encryption
- Online DDL (instant metadata, inplace indexes, copy for columns)
- Compatible data check to avoid unnecessary rebuilds
- Change buffer for secondary indexes
- Bulk insert optimization with intermediate commits
- Persistent statistics (ANALYZE TABLE persists to metadata)


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

All variables are prefixed with tidesdb_

Storage:
  data_dir                  Data directory (default: <datadir>/tidesdb)
  block_cache_size          Block cache size in bytes (default: 256MB)
  write_buffer_size         Memtable size before flush (default: 64MB)
  max_open_sstables         Max cached SSTable structures (default: 256)

Threads:
  flush_threads             Background flush threads (default: 2)
  compaction_threads        Background compaction threads (default: 2)

Compression:
  enable_compression        Enable compression (default: ON)
  compression_algo          Algorithm: none/snappy/lz4/zstd/lz4_fast (default: lz4)

Bloom Filters:
  enable_bloom_filter       Enable bloom filters (default: ON)
  bloom_fpr                 False positive rate 0.0-1.0 (default: 0.01)

Durability:
  sync_mode                 Sync mode: none/interval/full (default: full)
  sync_interval_us          Sync interval microseconds (default: 128000)

Isolation:
  default_isolation         read_uncommitted/read_committed/repeatable_read/snapshot/serializable (default: read_committed)

LSM Tree:
  use_btree                 Use B+tree format for column families (default: ON)
  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     Skip list probability (default: 0.25)

Block Indexes:
  enable_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)

Value Log:
  klog_value_threshold      Values larger than this go to vlog (default: 512)

Resources:
  min_disk_space            Minimum disk space required (default: 100MB)

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

Full-Text:
  ft_min_word_len           Minimum word length for FT index (default: 4)
  ft_max_word_len           Maximum word length for FT index (default: 84)

Logging:
  log_level                 debug/info/warn/error/fatal/none (default: info)
  log_to_file               Log to file instead of stderr (default: OFF)
  log_truncation_at         Log file truncation size in bytes (default: 24MB)

Encryption:
  enable_encryption         Enable data-at-rest encryption (default: OFF)
  encryption_key_id         Encryption key ID (default: 1)

Change Buffer:
  enable_change_buffer      Enable change buffer for secondary indexes (default: ON)
  change_buffer_max_size    Max pending entries before flush (default: 1024)

SSI (Serializable Snapshot Isolation):
  active_txn_buffer_size    Buffer size for SSI conflict detection (default: 64KB)


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

Run all TidesDB tests:

  ./mysql-test/mtr --suite=tidesdb

Run specific test:

  ./mysql-test/mtr --suite=tidesdb basic

Run with verbose output:

  ./mysql-test/mtr --suite=tidesdb --verbose

Available tests (40 total):
  auto_increment      - Auto-increment columns
  backup              - Backup operations
  basic               - Basic CRUD operations
  bulk_insert         - Bulk insert performance
  clustered_index     - Clustered index behavior
  compatible_data     - check_if_incompatible_data / metadata-only ALTERs
  composite_keys      - Composite primary/secondary keys
  condition_pushdown  - Table Condition Pushdown (WHERE to engine)
  consistent_snapshot - START TRANSACTION WITH CONSISTENT SNAPSHOT
  datatypes           - Data type handling
  ddl                 - DDL operations (CREATE, DROP, RENAME)
  edge_cases          - Edge case handling
  encryption          - Encryption configuration
  fk_cascade          - Foreign key CASCADE/SET NULL/self-referencing
  fk_parent_list      - get_parent_foreign_key_list / ALTER on FK tables
  foreign_keys        - Foreign key definitions
  fulltext            - Full-text search
  hidden_pk           - Hidden primary key (tables without PK)
  icp_mrr             - Index Condition Pushdown and Multi-Range Read
  index_operations    - Index create/drop
  index_scan          - Index scan operations
  inplace_alter       - In-place ALTER TABLE
  large_data          - Large data handling
  maintenance         - ANALYZE, CHECK TABLE
  online_ddl          - Online ALTER TABLE
  partitioning        - Table partitioning
  persistent_stats    - Persistent statistics (ANALYZE persists to metadata)
  preload_keys        - LOAD INDEX INTO CACHE
  rowid_filter         - Rowid filter pushdown for semi-joins
  secondary_index     - Secondary index operations
  skip_locked         - SELECT FOR UPDATE SKIP LOCKED
  spatial             - Spatial data types
  spatial_geometry    - Geometry functions
  spatial_index       - Spatial indexing
  table_operations    - Table operations
  tablespace          - Tablespace operations
  transactions        - Transaction commit/rollback
  ttl                 - Time-to-live expiration
  virtual_columns     - Generated columns
  xa_transactions     - XA distributed transactions


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
  make -j$(nproc) tidesdb

  # Run tests with UBSAN 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