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

13 posts by 5 authors in: Forums > CMS Builder
Last Post: February 9 (RSS)

By Dave - January 24 - edited: February 7

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! :)

Dave Edis - Senior Developer
interactivetools.com

CMSB v3.65 Alpha (New Field Types)

By mark99 - January 25

Two new field features that I've always really wanted to see, nice. I'll have to set aside some time to play :)

CMSB v3.65 Beta (New Field Types)

By Dave - January 26

Hi All,

We just released Beta 1 with fixes for PHP 8.1+ that prevented installing. Post any questions or feedback, thanks!

Dave Edis - Senior Developer
interactivetools.com

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

By Dave - January 26

Hi All,

Beta 2 is now out with some minor fixes and support for "Offline Mode".

Here's how it works, download the latest beta here: https://www.interactivetools.com/download/

And then download and install the "Offline Mode" plugin: https://www.interactivetools.com/plugins/offline-mode/

Once you enable that plugin it will use local copies of all the CDN resources (bootstrap, jquery, tinymce, google fonts, etc)

Thanks for the feedback so far, feel free to post here or email me directly. Thanks!

Dave Edis - Senior Developer
interactivetools.com

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

By Deborah - January 30

Hi. I may be premature posting this, but found a couple of plugin compatability issues.

Save and Copy version 1.10
After pressing the "Save & Copy" button, the button remains depressed in the active state, and a copy is not created.

Character Counter Version 1.01 (author The CMS Builder Cookbook)
The characters are not limited while typing. The status info that usually appears below the textbox on the right (0 Characters | 0 Characters Left | 0 Words | Character Limit:20) is missing. The modal does popup and stop submission if too many characters are typed.

I look forward to experimenting with the two new field types.

~ Deborah

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

By KennyH - January 30

Hi Dave -

I just installed this on a development website I use for this purpose and have some feedback.

I can confirm I am having the same issues Deborah mentions with Save & Copy and the Character Counter plugins. If I click Save & Copy, nothing happens. If I click Save after that, it makes a copy of it like the Save & Copy button should do.
Going back a few versions, en.js in webadmin/3rdParty/TinyMCE4/langs has been missing. I've just been adding it back from previous versions
FontAwesome only shows solid (fas) icons and won't show duotone (fad) icons. I fixed this install by modifying /lib/menus/header.php to delete the CDN entries for font awesome and adding my own Pro Kit entry (<script src="https://kit.fontawesome.com/xxxxxxxxxxxx.js" crossorigin="anonymous"></script>). This seems to fix the problem and icons show up as expected. I have 132+ websites right now and all of them use the duotone icons, so that would be a big change if I could only use solid icons.
Not really a big deal, but under General Settings, I show Control Panel: None detected (when using DirectAdmin)
I uploaded some images that have info fields and after uploading, the modify screen should appear. I got the "We are temporarily experiencing high website traffic or technical difficulties. Please try again later." error. Specifically:
The database error given was:
DB not connected, can't escape 'support@sagentic.com'

I cleared the error off the screen and then clicked "modify" and was able to add the content to the info1, info2, info3, info4, etc fields.
Some of the image upload areas show a really big field of blank space (see attachment)
Some of the layout has become more compact. For example, the line spacing between checkboxes has become more condensed (see screenshot). This not a bug per se, but it is different than usual. This affects all of the screens where content can be entered into a field. It might be a change in the Bootstrap padding if you switched versions.
When uploading images from the Media Library, it normally would autofill the dimensions depending which version of the image you choose. Nothing shows up in those boxes now. If I add the image with or without those images, nothing gets embedded into the content. That may be a symptom of my next issue:
My wysiwyg_custom.css and wysiwyg_custom.php are not being recognized / loaded with the WYSIWYG field.

I haven't tested out the new field types yet, but I was wondering if you could make some basic sample schemas that use some Generated Column fields and another one that shows how foreign key works. The examples mentioned in the overview are helpful, but a basic use schema would clear up some confusion for this added capability.

Since this a pure development website, I can let you experiment with it or poke around as needed to see what I am seeing. Just let me know.

Thanks for working hard on this! I am exceited about the new features and capabilities.

Kenny H

Attachments:

CMS Screenshots.pdf 178K

CMSB v3.65 Beta (New Field Types)

By Dave - January 31 - edited: January 31

Hi All,

Thanks for all the great feedback that you've posted here and emailed me directly. I've just released beta 3 with the following changes:

Offline Mode plugin - We've bundled this plugin. Once enabled it will cache all CDN files and serve them locally
Re-added: /3rdParty/TinyMCE4/langs/en.js
Admin > General > Server Info: Added autodetect code for DirectAdmin control panel
Tab Groups: Fixed issue where upload iframes were too tall and not being resized
For "DB not connected" errors, I've added some code so these will be logged so we can track them down

We've updated the following plugins to work with the latest beta (and the updated jQuery it uses):

Other things to note:

We made a plugin for anyone who uses custom Font Awesome Kits to substitute in your URL. Message me if you want that
We did make checkbox spacing less for some UI and features coming in the next version
- To revert this edit /3rdParty/clipone/css/main.css
- Search for ".radio label, .checkbox label " and change margin-bottom back to 5px
- Search for: .form-group-checkbox and remove that block
- Let me know if this causes problems for a lot of people.
- Also, note that we'll be upgrading to Bootstrap 5 this year so these changes will all be temporary

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

Let me know if any previous issues persist or if you find any new ones, and thanks so much for your help and feedback!

Dave Edis - Senior Developer
interactivetools.com

CMSB v3.65 Beta (New Field Types)

By Dave - February 1

Hi everyone,

Beta version 4 is now available for download. This release fixes an issue with the checkbox CSS spacing that was introduced and fixes the issue that was preventing people from downloading beta 3 (the file got uploaded to the wrong place).

You can get the latest beta build here: https://www.interactivetools.com/download/

If you have any final fixes, bug reports, or issues before we release, let us know.

Thanks!

Dave Edis - Senior Developer
interactivetools.com

CMSB v3.65 Beta (New Field Types)

By Dave - February 5 - edited: February 5

Hi All,

Beta version 5 is now available for download. Here's a list of fixes:

Wysiwyg fields now show a notice if /lib/wysiwyg_custom.php is out of date and needs updating
Wysiwyg fields now display content correctly with CDN and local libraries
Backup & Restore now handles Generated Columns without errors
Backup & Restore now restores tables with foreign keys without errors
Permalinks plugin menu options now display correctly
Database Editor no longer shows delete links for "accounts" and "_media" tables
Foreign Keys now show links to target tables and warnings if unlinked
We removed some invalid dynamic date-based examples for Generated Columns in MySQL
Improved error messages and reporting
Misc fixes and optimizations

I've attached some test schemas to help test the new features. Unzip them and upload to /data/schema/, then click "Database Editor" to import:

Test Customers
Test Projects (SET NULL)
Test Projects (RESTRICT)
Test Projects (CASCADE)

These sections are configured to demonstrate Generated Columns and Foreign Keys.

Steps to try:

Deleting "Test Customers" will error due to Foreign Keys linking to that table
Note the "Full Name", "Discount Level", and "Last Contacted Quarter" Generated Columns derived from other fields
Customers and Projects with matching numbers are linked using Foreign Keys (configured differently for each project section)
Delete Test User #2 and see Project 2's "Customer Id" set to null. Ensures you are never left with an invalid id.
Delete Test User #8 and see Project 8 erased (CASCADE). Ensures orphan records are not left behind.
Delete Test User #5 and see error (RESTRICT). Ensures data isn't accidentally erased when it's still needed by other records.

These features utilize advanced MySQL functionality to enforce data integrity without manual coding. Don't worry if you don't immediately see where you can put them to use, over time they will become very useful, especially for complex sites.

Post any questions or feedback. You can get the latest beta here: https://www.interactivetools.com/download/

Let us know about any final fixes or issues before we release.

Thanks!

Dave Edis - Senior Developer
interactivetools.com

Attachments:

fk_test_schema_data.zip 7K

CMSB v3.65 Beta (New Field Types)

By Dave - February 7

Hi All,

I've just released Beta 6 which fixes an issue that sometimes caused Admin Settings to not save correctly.

If there are no other major issues we'll release this as the official version in the next few days.

Let me know if you find any bugs in the meantime.

Thanks!

Dave Edis - Senior Developer
interactivetools.com