Core updates – January 31, 2020

[ryan]

We’ve got several updates on the dev branch this week, but I want to finish one more of them before bumping the version to 3.0.150, so will likely do that next week. The biggest change this week is the addition of a new core class called FieldsTableTools. This will be used internally by the $fields API variable for manipulating field table schema in the database, and its methods are accessed from $fields->tableTools(); Though this is one you probably won't be using from the API unless developing Fieldtype modules, so if you just want to know what the benefits are, feel free to skip to the last two paragraphs. Initially these methods are primarily focused on managing unique indexes for fields, though will expand to manage other schema manipulations as well. An example of its utility is also included in this week’s commits—our email fields (FieldtypeEmail) now have the ability to enforce uniqueness at the DB level. 

If you edit an email field (Setup > Fields > email) and click on the Advanced tab, there’s now a checkbox there to enforce unique values, ensuring that a given email address cannot appear more than once (i.e. not appear on more than one page). The same ability will be added to text and integer fields (FieldtypeText and FieldtypeInteger) as well, but we’re starting with the email field because it’s needed for an update hopefully wrapping up next week: the optional ability to login to the admin with your email address. 

Having a unique index on a DB column is actually a pretty simple thing, but as it turns out, it takes a lot of code to support the ability in an existing installation. That’s because we have it as something you can toggle on and off (on that Advanced tab), and when you toggle ON a unique index, there can’t be any duplicate values in the existing data, or the index will fail to apply. So there’s a lot of supporting code in FieldsTableTools to do things like detect and warn about duplicate values, delete empty values before adding the index, identify when the index is present without querying the table, reporting error conditions in a manner that understandable and recoverable, as well as the actual schema manipulations that add or remove the index. 

I realize this sounds a bit technical (and that's partly why I'm not putting this in a more formal blog post), but I think most developers will at some point find it very useful in building sites and applications. Not only will it enable us to safely support login-by-email in the admin (coming next), but it’ll be useful in any situation where you need to prevent a value from repeating. Whether that is in preventing double bookings for a date, location, seat, etc., or preventing any kind of redundancy like post titles, author names, product titles, phone numbers, or codes (UPC, ISBN, ASINs, EIN, SSN, etc.), this feature can come in handy. And supporting it at the DB level is a lot more solid than supporting it at the code level.  

Right now it’s just supported in email fields, but all of the logic has been delegated to the newly added class so that we can easily support it in any other fields (with text and integer fields coming next). Following that, the $fields->tableTools(); will also be gaining methods for modifying other types of field schema. For instance, rather than just supporting the INT column type for integer fields, wouldn't it be nice to go to Setup > Fields > [some integer field] and select both unsigned and signed integers of different types: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, for any new or existing integer field? MySQL also offers a similar array of size variations for other types that would be useful in our text and textarea fields, among others. So you'll be continuing to see ProcessWire offer more options for existing FIeldtypes in future core versions, enabling you to more efficiently store and manage content in PW. But of course we'll do it in a way that keeps it simple, ensuring that you don't have to think about these things unless/until you want or need them. 

[MrSnoozles]

Wow, just wow. Unique values are going to be so useful, looking forward to it. And login by email sounds exciting too.

Quote

And supporting it at the DB level is a lot more solid than supporting it at the code level.  

?

[szabesz]

 Thanks for working on FieldsTableTools, supporting uniqueness at the DB level is an important addition to the system for sure!

On 1/31/2020 at 10:59 PM, ryan said:

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, for any new or existing integer field? 

However, while you are at it, please also consider this HIGHLY POPULAR request: https://github.com/processwire/processwire-requests/issues/126

Looks like this is the right moment for you to also implement a decimal fieldtype for the core.

[dotnetic]

decimal fieldtype would be GREAT

[Tiberium]

@ryan

I came across this forum post, after i searched for unique fields. In 3.0.236 there is, for me, still only the email field who has the unique option in the advanced tab. Was the idea abandoned for text and int fields?

[adrian]

@ryan - just following up on the comment by @Denis Schultz - now 4 years on, are you still planning on adding that "Unique" checkbox for Text and Integer fields?

 

[ryan]

@adrian I hadn't thought of that in awhile, is it something you have come up with a need for? It would probably be fairly simple for me to add. 

[adrian]

@ryan - I need it for coupon codes which are stored underneath categorizing branch parents, but which are accessed by end users via a urlsegment on the home template. I have been using a hook to ensure duplicates can't be accidentally created and that works fine so not critical that this is added for my needs but just wanted to see if you thought it was still something that should be added to the core.

[Tiberium]

@ryanIf I may:

I have a system that allow to add a Bot Alias to a Discord-Bot for a rpg chat group. The bots later are trigger in discord by a choose nickname. By near 80 players, some names are not unique. So I want to give a moderator the option to give manual a nick for the bot alias. For that, it would be nice to have a unique text field.

Also, i "connect" the forum with it. So a moderator can add the forum-id's to a character. Here would it be also nice as a protection to not post the same ID twice, that an integer field has the option to be unique.