SQLiteMP is a proof-of-concept SQL implementation of the materialized paths (MPs) tree model, embedded within an SQLite database.
- Hierarchical Category Model: Manages category systems with single-parent tree categories.
- Flexible Item Association: Associates items with multiple categories, enabling more versatile data organization.
- Referential Integrity: Incorporates foreign keys to ensure consistency of the hierarchy structure and item association data.
- Cascading Foreign Keys: Leverages cascading rules to streamline hierarchy management and ensure referential integrity.
- Conflict Resolution Clause: Simplifies operations involving complex SQL logic (e.g., tree move or copy)
- Common Materialized Paths Operations: Supports creation, deletion, movement, copying, importing, and exporting.
- JSON-Based API: Offers a minimalistic SQL interface for seamless interaction.
- Encapsulated SQL Logic: Improves modularity and reduces code coupling by embedding SQL logic within database views and triggers.
- Simplified SQL Management: Reduces the application's need to handle complex SQL code directly.
- Structured and Maintainable Code: Leverages ordinary and recursive common table expressions (CTEs) for clear and maintainable code.
- Pseudo-Parameterized Views and Triggers: Implements parameterization through auxiliary buffer tables for added flexibility.
- Recursive Triggers for DRY Code: Facilitates development of complex SQL logic (not yet implemented).
- Standard SQLite Compatibility: Ensures portability and ease of use by relying on preinstalled binaries.
- Step-by-Step Tutorial: Offers a practical guide to setting up a demo database using the provided schema and dummy data modules.
The project directory is structured as a Python project, with plans to use Python for testing and demo purposes in the future. Currently, the project primarily consists of organized SQL code, which is embedded in the documentation and included in modules located under sqlitemp/src/sqlitemp/sql. These SQL modules replicate the documented code and can be directly imported into an SQLite database. Additionally, the directory contains JSON and SQL modules with dummy data for manual testing. While I recognize the importance of setting up proper automated testing, only limited manual testing has been conducted so far.
The project documentation is located in the sqlitemp/docs directory, with the entry file being Overview.md. These files can be viewed using GitHub’s file explorer by opening them in a browser. Most documents are organized using Markdown headings. When such a file is opened in GitHub’s file explorer, the command bar at the top displays a TOC (Table of Contents) icon as the rightmost icon. This README.md file includes Markdown headings, so the TOC icon should appear in the command bar. By default, the TOC sidebar is hidden, but clicking the icon reveals it, enabling convenient navigation. Individual documents can also be accessed through the file explorer sidebar on the left or by following the Previous/Next links at the bottom of each document.
The materialized paths (MPs) model is a common approach for storing hierarchical data in relational databases. With certain general restrictions, core MP functionality can be implemented in SQL using stored procedures, providing a higher-level abstraction for applications. However, this project targets the standard SQLite library, which lacks native support for stored procedures. Consequently, the primary objective of this project is to explore alternative methods for encapsulating MP functionality using the advanced features of the standard SQLite library. The project specifically aims to rely exclusively on library-supported code (primarily SQL) while establishing an efficient code management strategy.
The SQLiteMP project demonstrates a proof-of-concept implementation of an MP data model in SQLite. Building on the concepts and code presented in my earlier SQLite SQL Tutorial - which includes SQL snippets for performing common MP operations on SQLite databases - this project leverages views and triggers to encapsulate MP functionality directly within the database. These features provide capabilities similar to stored procedures, while the built-in JSON functionality enables the creation of a higher-level API, minimizing the application’s direct interaction with SQL.
Although this project extends the GitHub-Pages-based SQLite SQL Tutorial, an important motivation for creating a separate repository was to simplify setup and maintenance by avoiding GitHub Pages. The standard GitHub file browser offers a sufficiently user-friendly interface for navigation of small documentation bases, including automatic TOC generation for Markdown-formatted files.
See docs for further details.