v0.31.0

SQLPage is a tool that allows users to create dynamic, SQL-powered websites with minimal coding effort.

This release, v0.31.0, introduces enhanced table functionalities, deeper database integration, smarter search features, and improved debugging with detailed error logs. It also upgrades security and optimizes chart performance, making it easier to build secure data-driven websites.

🚀 New Features

Improved Components

• Columns Component

  • Markdown-supported descriptions (description_md) allow richer formatting.
  • Add simple text items without needing JSON handling.
  • Optionally skip displaying items (null as item).
  • 

• Table Component

  • New freeze headers and columns feature improves usability with large tables.
  • Enhanced search logic ensures more precise matches (e.g., "xy" no longer matches separate x and y cells in adjacent columns).
  • Search box visibility is retained during horizontal scrolling.
    Technical: Adds freeze_headers, freeze_columns, and improves the internal search algorithm.
  • 

• Form Component

  • Added an empty option (empty_option) to dropdowns, enabling placeholder-like behavior.
    • 
  • Improved handling of large form submissions with configurable size limits (max_uploaded_file_size, default 5MB).
    Technical: There used to be a hardcoded limit to 16kB for all forms.

---

Database Enhancements

• Support for New Data Types:

  • Microsoft SQL Server now supports BIT columns.
  • Improved handling of DATETIMEOFFSET in MSSQL and TIMESTAMPTZ in PostgreSQL, preserving their timezones instead of converting them to UTC.

• Better JSON Handling:

  • Accept nested JSON objects and arrays as function parameters.
    Useful for advanced usage like calling external APIs using sqlpage.fetch with complex data structures.

• SQL Parser Update:

  • Upgraded to v0.52.0 with new features:
    • Added support for:
      • advanced JSON_TABLE usage in MySQL for working with JSON arrays.
      • EXECUTE statements with parameters in MSSQL for running stored procedures.
      • MSSQL’s TRY_CONVERT function for type conversion.
      • ANY, ALL, and SOME subqueries (e.g., SELECT * FROM t WHERE a = ANY (SELECT b FROM t2)).
      • LIMIT max_rows, offset syntax in SQLite.
      • Assigning column names aliases using = in MSSQL (e.g., SELECT col_name = value).
  • Fixes a bug where the parser would fail parse a SET clause for a variable named role.

---

Security and Performance

• Encrypted Login Support for MSSQL:

  • Ensures secure connections with flexible encryption modes:
    • No encryption (?encrypt=not_supported): For legacy systems and environments where SSL is blocked
    • Partial encryption (?encrypt=off): Protects login credentials but not data packets.
    • Full encryption (?encrypt=on): Secures both login and data.
      Technical: Controlled using the encrypt parameter (not_supported, off, or strict) in mssql connection strings.

• Chart Library Optimization:

  • Updated ApexCharts to v4.0.0.
  • Fixed duplicate library loads, speeding up pages with multiple charts.
  • Fixed a bug where timeline chart tooltips displayed the wrong labels.

---

🛠 Bug Fixes

Database and Compatibility Fixes

• Microsoft SQL Server:

  • Fixed decoding issues for less common data types.
  • Resolved bugs in reading VARCHAR columns from non-European collations.
  • Correctly handles REAL values.

• SQLite:

  • Eliminated spurious warnings when using SQLPage functions with JSON arguments.
    Technical: Avoids warnings like The column _sqlpage_f0_a1 is missing.

Component Fixes

• Card Component:

  • Fixed layout issues with embedded content (e.g., removed double borders).
    • 
  • Corrected misaligned loading spinners.

• Form Dropdowns:

  • Resolved state retention after form resets, ensuring dropdowns reset correctly.

Usability Enhancements

• Removed unnecessary padding around tables for cleaner layouts.
• Increased spacing between items in the columns component for improved readability.
• Database errors are now consistently logged and displayed with more actionable details.
  • 
    Technical: Ensures warnings in the browser and console for faster debugging.

---