CMSB v3.65 Beta (New Field Types) - UPDATE: Beta 6 Released

Hi all,

*UPDATE*  We just released Beta 5!  Updates are at the bottom of the thread.

There are a LOT of code changes in this one.  We've completely rewritten the field system and significantly modernized the codebase.  

Here are some of the highlights: 

• New field type "Generated Columns": These are a type of "virtual" column that is generated with a MySQL expression from other columns in the same row. These generated columns behave just like any other columns in your database and update themselves automatically when needed.  You can display them on the list page, search or sort on them, and display them in viewer code. Here are some common use cases and example MySQL code:
  • Address Formatting: "123 Main St, San Francisco, CA" CONCAT_WS(', ', street, city, state)
  • Price Calculations: "Total: $100.00" CONCAT('Total: $', FORMAT((qty * unit_price) + shipping + tax, 2))
  • Status Determination: "Order Status: Complete" IF(delivery_date IS NOT NULL, 'Complete', 'Pending')
  • Date Extraction: "2024 Q1" CONCAT(YEAR(publish_date), ' Q', QUARTER(publish_date))
  • String Transformation: "UPPERCASE" UPPER(original_column)
  • Conditional Formatting: "Sales Level: High" IF(sales > 10000, 'High', 'Low')
  • Multiple Conditions: "Discount Level: Platinum"
    CASE
    WHEN annual_sales >= 100000 THEN 'Platinum'
    WHEN annual_sales >= 50000 THEN 'Gold'
    WHEN annual_sales >= 20000 THEN 'Silver'
    WHEN annual_sales > 0 THEN 'Bronze'
    ELSE 'No Rating'
    END
• New field type "Foreign Keys":  A foreign key creates a link to a related record in another table. It does this by storing the primary key ('id' or 'num') of that associated record. Foreign keys require that the referenced record exists. They can also prevent accidental deletions and the creation of orphan records in referenced data.  Some of the things you can do with Foreign Keys include: 
  • Block deletion: If you link an order to a user record, you can make it so you can't delete the user record while there are still orders linked to it.  This avoids creating orphan records and accidental data loss by blocking the deletion of records that are still in use.
  • Cascade deletes: This is the opposite, it automatically deletes records when the records they link to get deleted.  Say you had forum posts that link to a user record, you can make it so that when you delete the user all of the related posts automatically get deleted.  This removes orphaned records and cleans up linked data.
  • Set to NULL: When you want to ensure you're linked to a valid record, but also can delete it, this option automatically resets the linked value to null.  
• Live SQL monitor: This feature was to help us with our own development on CMSB.  Under Admin > General > Server Info > Database, you'll find a link.  It will open up a new window with a live feed of SQL queries going through the server.  Queries are only tracked while you have this page open and all logs are removed once you close the page.  This can be helpful when you want to debug a MySQL issue to see what's actually being received by the server.  It also logs MySQL errors.
• MySQL Source Tab: This one is under: CMS Setup > Database Editor > "any section".  It's a tab along the top and shows you the exact current setup of your MySQL tables and their settings.  Again, helpful for us during development and debugging.
• Cloud Mode: We've switched to using content delivery networks (CDNs) for shared libraries and reduced the program size by over 10 megs and over 200 files.  This means faster uploads when doing installs and faster access when using pre-cached libraries (bootstrap, etc) 
• Offline Mode: We'll be releasing a plugin shortly "Offline Mode" that has a local copy of all the CDN files if you need to run disconnected from the internet or don't want to rely on CDNs.

Here's the complete changelog:

*** January 24, 2024 - Version 3.65 (New Field Types)

SERVER REQUIREMENTS: PHP 8.0+ and MySQL 5.7+ (or MariaDB 10.2+)

NEW FEATURES
- New Field Type: "Generated Columns" let you create virtual columns that are calculated from other fields in the same row
- New Field Type: "Foreign Keys" for relational links between records, ensuring data integrity and preventing orphan records
- Live SQL Monitor: Monitor SQL queries in realtime, found under: Admin > General > Server Info > Database Server
- CDN Support: Reduced CMS codebase by 10 megs by switching to CDNs for common library delivery
- Offline Mode: Added support for new plugin with all CDN files bundled for offline use
- Setup > Database Editor > Table Editor: Added "MySQL Source" tab for referencing MySQL table definitions

MINOR CHANGES
- Session Names: Cookie name is now set under: Admin > General > Advanced, change it if you have multiple installs on the same domain
- ZenDB: Added $results->row($pos) to get a row based on its position, e.g. $firstRow = $results->row(0)
- ZenDB: Added $results->col($nameOrPos) to get a list of column values by name or position
- ZenDB: Added $row->col($nameOrPos) to get single row column value by name or position
- ZenDB: Added $row->isFirst, $row->isLast
- ZenDB: Added $field->toString() to force a string representation of a field value
- ZenDB: Added DBException class for displaying standardized error messages
- ZenDB: Added DB::query()->orThrow($message) chainable method to throw exceptions with a custom error message
- ZenDB: Added SQL Comment Directive /* @ZENDB NO_SMART_JOINS */ for disabling smart join functionality
- Upgraded PHP Mailer from v6.8.1 to v6.9.1

REMOVED FEATURES
- Removed experimental ViewerAPI code (not updated or available through CMS interface since 2017)
- Removed demo mode: It took significant resources to maintain and was not widely used (previous versions can still be used for demo sites)
- Removed bundled libraries: Now loaded via CDNs and available in Offline Mode plugin
- Removed feature that disabled autocomplete for browsers that ignore HTML5 autocomplete standard (as it no longer works)

BUG FIXES
- Database Tables: Convert all tables to ROW_FORMAT=DYNAMIC if not already, to avoid "Row size too large" errors with InnoDB
- Misc Code and other minor improvements.

You can download the latest beta here: https://www.interactivetools.com/order/download.php

Please feel free to ask any questions or post any feedback, questions, concerns, or bugs you find!  We love to talk about CMS Builder and web software.

Thanks! :)