LOWER(name) queries perform badly; add fullname and restrict name as lowercase.

Update include:

1. Add update script from D5.3: add new field "fullname", folk content from "name", and convert all "name" into lower. No invalid characters filter and convert during upgrade, or else user will not able to login once again. On the other hand, which means "name" restriction will only apply to new register user, or during user profile update.
2. Fix user registration. Will request for "Full name" during registration.
3. Update some menu and block title, display user's fullname rather than username, for more user friendly.

P.S. The restriction upgrade for "name" may require for some reconsideration. PROS include:

1. Simplify logical checking.
2. "username" will keep similar look-and-feel as other login system. E.g. MSN, Skype, eGroupWare, Google account, email, etc. Should be comfortable for normal end user.
3. Decrease the difficulties when combine with other authentication backend system. E.g. RADIUS, SMTP/IMAP, or other authentication system which employ email as username.

But it come with some CONS: We give up some existing flexibility about username. On the other hand, we may also need to consider about the username upgrade and transformation, if we hope to employ the new standard within a one-take action.

Patch update: shouldn't restrict username as email syntax, e.g. in case of ADS, username is required in domain\username syntax. So keep user_validate_name() logic, and add lowercase checking.

All existing username will convert as lowercase within database during upgrade from D5.3; on the other hand, all newly created account and user account update will stored as lowercase, too. Users are able to login with natural case: system will just filter it as lowercase. Actually, we don't really need to handle username in case sensitive: we always user LOWER() for filtering, handling all username in lowercase can solve the performance impact.

User's existing natural case username will folk as "full name" with no case or other limitation. It is just for display purpose. Some UI already update for this change (user block, user profile). With this new field, we can even provide search function according to full name (we miss this in drupal.org, as we use profile.module which don't have search function), or hide user ID from other users for more security.

Update, no logical update with LOWER() but mainly for full name usability:

1. admin/user/user: add "Full name" column. (Usability)
2. Blog, Node, Tracker, User blocks: use theme_fullname(), in order to hidden username from public. (Security and Privacy)
3. Themes: clone theme_username() as theme_fullname(), which will load user->fullname. Also update garland as example.

TODO:

1. Comment and Forum still display username to public, need update.
2. Search user with full name.
3. Really need theme_fullname()? or just hack theme_username()?

minor update to blog.pages.inc: display full name rather than username.

I guess so too. BTW, when compare with http://drupal.org/node/83738, if LOWER() is something that MUST fix within D6, and a new field is also a MUST for solving problem, I would even vote for adding a field which is meaningful for many cases (fullname) rather than something for compare ONLY (name_lower). We may find some better solution for replacing name_lower on tomorrow, but fullname should be what we really needed for. That's not easy to judge which solution is better: both are risky :'(

minor update: use users.fullname for contact, openid and tracker.

minor update: add users.fullname supporting for comment, both UI and admin UI.

@Rob: Site security and user privacy can always improved by protecting user login ID from public; even some site don't need to care/consider about security/privacy, this won't be a bad idea for them. An in-house core/module hack can always solve the problem; BTW, this usually introduce more complicated maintenance issue :(

@catch: fullname field is now set as required, so every user need to provide that. Site upgrade from D5.3 will automatically folk existing username (with case styling) to fullname. On the other hand, if we don't hope to set fullname as required, we may hack theme_fullname(), check if $user->fullname exist, or else fail-as-default to theme_username(). We can adjust this handling with more discussion.

@Crell: I even brainstorming about a clone from eGroupWare, about case sensitive username issue (http://edin.no-ip.com/html/?q=node/348). Anyway, that is too far from D6 ;p

I guess my main focus is: name_lower can solve todays' problem, but it may be replaced by some other solution within a short future, e.g. fullname can work as an alternative. So if change is required for slow LOWER() issue, I would like to vote for a long term solution rather than a short one; it is just a bonus if fullname can benefit for more than we are hoping for :)

@catch: no change.

From the user point of view, they can use both "UserName", "USERNAME", "username" for their account login, even they save in whatever format in D5.3.

From the system and DB point of view, all record are converted as lowercase during upgrade, and all user input for authentication will also filtered as lowercase. So we don't need LOWER() compare during runtime.

@catch: Just give a simple test for my blog with D5.3: my username is saved as "hswong3i", and I am able to login with whatever "HSWONG3I", HSWONG3i", "HSWong3i", "hswong3i", etc. So there is no change with user behavior between D5.3 and D6 w/o patch :)

Update:

1. Use drupal_strtolower() instead of strtolower(), based on UTF-8 concern. Thanks Gabor's comment (http://drupal.org/node/174025#comment-625306). Need help from tester!
2. Update node and comment admin page as fullname ONLY (no double display). This is because we only concern username during: 1. user login, and 2. user admin. We don't need to care about user's login ID among other admin pages (they are just reference information, and link is provided so we can linkage back to user's profile).
3. Bug fix comment preview (partly, still have error message in some rare cases).

TODO (UI update):

1. Bug fix node and comment preview with using fullname.
2. Handle forum listing page with fullname.

Minor update: thanks to Gabor's comment (http://drupal.org/node/174025#comment-625544), no strtolower() nor drupal_strtolower() is used, but optimize the use of LOWER():

1. INSERT/UPDATE with name = LOWER('s'), so name will always filtered as lower case during input.
2. Keep LOWER(name) = LOWER('%s') in _user_edit_validate(): we don't really need to care about it performance impact during validation.
3. SELECT by name = LOWER('%s'), so indexing will keep its effect, on the other hand fully utilize LOWER() as user input filtering.

P.S. This change won't change the meaning of adding users.fullname: we hope to fully utilize indexing with a filed contain content in lower case, forcing users.name as lower case will lose all username styling. We still need either users.name_lower (for lower case compare only, where keep styling in users.name) or users.fullname (store existing styling with more flexibility, where filter users.name as lower case for compare). Again, I vote for adding a meaningful field, rather than a compare-only field for performance boost only :)

I think we may have some misunderstanding about the natural of this patch. Let's says in simple, this patch include 3 sections:

1. Folk users.name as users.fullname during upgrade. This keep user's custom username styling among D5.3.
2. Filter existing users.name as lower case, utilize LOWER(name) handling, where keep LOWER() unicode functionality; on the other hand, don't cancel indexing so boost up performance.
3. Update UI as using theme('fullname', $user), rather than old theme('username', $user).

The first 2 sections are just "slow LOWER() issue" related. They are similar as http://drupal.org/node/83738, which just handle the same problem with different solution and implementation.

On the other hand, we may just seems UI update as usability enhancement. That is not a new idea for normal user (e.g. MSN, Skype, or even drupal.org both provide "full name" field for custom username styling with no limitation). There is not critical programming nor logical change among core, which just simply switch the display handling. Finally, it can improve user privacy and site security.

As the core idea of this patch is just similar as http://drupal.org/node/83738, no critical change to normal user, where bonus features are just usability enhancement (which may still welcome even we are in final beta status), hope we may give a kindly reconsideration about this patch for D6.

Minor update:

1. Feature add: admin will able to search user by name, fullname and mail; normal search will only able fullname search.
2. Bug fix: comment_reply().
3. Bug fix: node_preview().
4. UI Update: dblog_overview(), dblog_top() and dblog_event().
5. UI Update: forum_get_forums(), forum_get_topics(), template_preprocess_forum_topic().
6. UI Update: poll_votes().
7. UI Update: most statistics pages.

TODO: ???

@Crell: I don't really care if we hope to postpone this for D7 or able to get in for D6: works are most likely done for UI update and debug, commit or not should just time dependent (if, this is a suitable solution...). BTW, if slow LOWER() issue is such critical that MUST fix within D6 cycle, I always hope to propose a better and complete solution, rather than a temporary name_lower field as a temporary solution.

Just hope to ask a simple question: will we postpone slow LOWER() issue (http://drupal.org/node/83738) with name_lower handling? If yes, name_lower will also postpone, so this issue must postpone, too; If no, we need to fix slow LOWER() within D6, therefore please don't postpone this counter solution which comes with better and complete handling: it is meaningful when brainstorming and compare for a better solution. Just focusing on a single solution will easily face deadlock and critical bug.

Moreover, this issue is just AS LATE AS chx's original proposal (Oct 8, http://drupal.org/node/181625), based on the definition of "feature freeze". I can't see the different between the natural of this 2 patches: we both touch DB schema, and hack a lot of codes for new handling. If this issue MUST postpone according to its late submit, please postpone BOTH slow LOWER() related issue :'(

P.S. Anyway, if name_lower is already committed, I will just take this easy, and update fullname issue for D7, based on required update for name_lower -> fullname conversation :)

My last and only request: please let this issue open for D6 until: 1. name_lower is committed for D6, or 2. name_lower is postponed for D7. I have no business about this issue up to now since works are most likely complete.

I am totally understanding the risky of this patch, and I am not such stubborn about committing this issue for D6; BTW, I am always stubborn when requesting an open-end technical discussion ;p

According to the progress of this fullname issue and http://drupal.org/node/174025, we are now able to sort out a better solution with utilizing LOWER(): http://drupal.org/node/83738#comment-625640. This counter proposal just enrich the view angle of slow LOWER() issue, but not means it strike away the chance of name_lower in D6...

On the other hand, as we are both handling slow LOWER() by alternative lower case column, so we are working with the same target. The only different is: name_lower is much strict forward but temporary; fullname is much static and solid but too huge for D6. May be we should give core committers a chance for choosing. That should be no harm and no risk if we are able to employ whatever name_lower or fullname in D6. They may have some other and different concern :)

P.S. if core committers judge "fullname should belongs to D7, based on XXX and YYY concern. Let's move focus to name_lower!", I will not question for anything. I hate slow LOWER() issue as you all, on the other hand asking for a complete and better solution. That's all.

Thanks for everyone. According to existing research founding and our completely understanding about fullname issue, hope we will able to commit fullname for D7 whenever it is open for public development :)

On the other hand, I guess we are now able to conclude name_lower as the most suitable solution for D6, by the counter prove of fullname for D7, based on its complete research founding and successful foreseeing. Yes, name_lower is very late within D6 development cycle; it also seems ugly, lossy and temporary solution. BTW, name_lower hidden most handling from UI, and there is no change to client behavior. It also give no impact to existing API, as no additional abstraction is required. And the most important is, name_lower should give us the most smooth upgrade path for D7 with minimum impact :)

I guess we should move our focus to name_lower for D6, and let's fix the slow LOWER() on time, with the most suitable solution on hand; most other solution may introduce much complicated impact for D6, and difficult upgrade path for D7 (if we are considering fullname in D7).

Why would we need to disable it? From my point of view, let's take MSN messenger as an example:

• name (drupal) == login email address (MSN) == user login ID which should always keep private based on security concern.
• fullname (drupal) == Display Name (MSN) == nickname == free form screen name with no limitation.

That's why this patch should delay until D7: we may need to modify where and when should display fullname only, or together with user login ID. This is not something about the correctness of programming logic, but layout and usability which needed for more discussion :)

Take eGroupWare as example. As login name may authenticated by either SQL (default), SQL/SSL, LDAP, ADS, Mail, HTTP, NIS or PAM backend, user login ID is always preferred as simple as possibile (limitation is related with what authentication backend is chosen). On the other hand, both screen name and login ID are display on top right conner after user login. Screen name is also used when sending email notification.

Option for display is a good idea. I will give some effort of this later.

sourceforge.net is the most ideal handling that I can imagine, too. BTW, as we try toooo much effort for making users.name as flexible as possible in the past, it is not easy for rolling back the status to ideal. I try to explain this "down grade of username" idea with some of my friends once before, but most of them are objecting about this. I guess preserve old username styling with fullname, on the other hand filter existing username as lower case should be the most suitable solution based on our current status :'(

Critical update:

1. Remove theme_fullname(): merge function into theme_username() for centralized handling. Keep all existing theme_username() usage.
2. Upgrade theme_username(): display fullname rather than name if available (It is now enforced. As a long run, I will add a site-wide username display styling option within admin/user/settings, similar idea as that in admin/settings/date-time).
3. Upgrade theme_username(): add $options in API input, so able to display plain text format username, as a replacement of existing check_plain($account->name). This change will give a great help to the previous centralized style handling.
4. UI Update: replace existing check_plain($account->name) by theme('username', $account, array('plain' => TRUE)). (This progress may backport for D6)
5. UI Update: remove all fullname styling among administration pages. Since most administration pages require tables with table sort feature, it is not suitable for replacing display format by theme_username() if it may display some format other than plain name. Combine the fullname with table sort may break the overall layout design.

TODO:

1. Optional "enhanced strict mode" (restrict with [a-z0-9-_.]) and "email localpart mode" (restrict as email localpart limitation) for username validation, beside existing "lower case free form mode". Site upgrade from previous version will set as "lower case free form mode" after upgrade, in order to keep a valid login authentication; site with fresh new install will set as "enhanced strict mode", in order to provide maximum compatibility with 3rd part system, e.g. CVS. Change restriction into a not compatibile mode will not convert ANY existing user login ID; BTW, a message will always prompt up after user login, in order to remind some update of username is required for their account.
2. Optional username styling. E.g. fullname [name], fullname, name, name [fullname], etc. This hack should all done within theme_username() for centralized handling.

Bugfix and feature update:

1. Merge progress result from http://drupal.org/node/192056.
2. Add Optional site-wide username styling in admin/user/settings (clone from eGroupWare). Default styling with !fullname, !username, !fullname [!username] and [!username] !fullname. Administrator may define custom styling.

TODO:

1. Javescript clone from system.js have bug (but run correctly). Need help!
2. Code tidy up.