With those figures (0.01% * 0.001%) it seems indeed that there is little need to optimize this. However, it still doesn't feel right, we just know it's gonna be an issue some day...

On the other hand, using LOWER eliminates the use of indexes by the database, so that solution will probably also run into performance issues at some point.

Furthermore:

plg wrote:

If you're sure the SQL function "LOWER" is 100% compatible with MySQL/PostgreSQL/SQLite then it's fine, let's go with it, but be sure :-)

I'm not sure, I just noticed that the function is supported in all three systems.
But a little search showed me this: sqlite issues. The same problems that strtolower has.

I guess I should settle with the current commit. That 0.0001% of users can probably change the code themselves, optimizing for their back-end.
(Is it an option to wrap the search_case_username function in if(!is_callable('search_case_username')){[...]} such that it can easily be overridden?)

scalable means that the script won't crash when jumping from 10 to 10,000,000 users in the database.

stim wrote:

I mean to say that I strongly disagree with this solution because it is not scalable at all. Therefore, I don't like to settle with the last commit. Because: if it is not scalable, it's not a actually a feature of the software, since (power)users will be obliged to turn it off eventually, as their traffic grows.

I think everybody agrees that the best code should be prefered :-) .

Just for my personal knowledge: what do you mean by "it is not scalable at all"? (I'm not a programmer, but I'm working on it ;-) !)

Lower in sqlite:
http://www.sqlite.org/lang_corefunc.html
mysql
http://dev.mysql.com/doc/refman/5.0/en/ … tion_lower
postgres:
http://developer.postgresql.org/pgdocs/ … tring.html
General:
http://oreilly.com/catalog/sqlnut/chapt … html#27402

I mean to say that I strongly disagree with this solution because it is not scalable at all. Therefore, I don't like to settle with the last commit. Because: if it is not scalable, it's not a actually a feature of the software, since (power)users will be obliged to turn it off eventually, as their traffic grows(*).


I can understand that it might be undesirable to rely on 'LOWER' (if there exists considerable doubt about its performance). However, that shouldn't be an excuse not to make piwigo scalable.
Therefore I (jokingly) suggested to apply the 'backward' method I shared earlier. (Since, although it is backward in every sense of the word, it is scalable and can solve the issue.)

(*) I don't want to be harsh or anything. And I do see how this might never become an issue for the average user. But it's just bad quality of software to leave it unscalable like this, and I don't believe it's something that can't be solved.
I was very happy to find a community/album that fitted my needs (I've looked for it several times but never found anything good), and I'd like to continue to be of good quality if I'm going to use it. :)

Lower is actually supported in all three.

flop25 wrote:

it's too rare !

You just made sure of that. With this performance issue, I guess no-one who notices it will use this.

I'm almost inclined to go for the backward way, if LOWER is out of the question. :-S
But this commit just takes away all scalability. If it is really that rare that this performance decrease is to be permitted, I don't believe it should be configurable.

-Tim

flop25 wrote:

@luc that's why using LOWER only is better because it's multi-byte safe.

OK, perfect, then :-)) .

stim wrote:

I updated my code in [Bugtracker] bug 1835, there was another thing with count() and pwg_db_fetch_assoc($q) I messed up.

Yes, it seems better this way :-) .

stim wrote:

Yes, I rely on LOWER and str_to_lower to do the same thing. I don't know if they do...
But we can make sure to rely on LOWER only:

Code:

  $q = pwg_query("
    SELECT `".$conf['user_fields']['username']."` AS username
    FROM `".USERS_TABLE."`
    WHERE LOWER(`".$conf['user_fields']['username']."`) = LOWER(\"".pwg_db_real_escape_string($username)."\");
  ");

This one seems fine to me.

stim wrote:

The other option is to do it backwards ;).
Query all the users that match the hashed password, and go look for case ambiguities there... Then you can rely on str_to_lower only. But that would freak every-bodies mind out.

Yes :-D ! I prefer previous solution -- but we'll see what will be chosen at the end; I'm not part of the team.

flop25 wrote:

the problem is for special uppercase

Indeed! That's one of the reason for extension:513 to exist.

flop25 wrote:

the best solution - use mb_strtolower()

It does be quite a solution; yet mb_strtolower() is provided by PHP extension mbstring, thus is there a risk that on some servers this extension is not active. In this case mb_strtolower() generates a notice. For LCAS Whiler proposed a code allowing to get free from mbstring:

/**
 * Split a multi-byte string in a array of each of its characters.
 * Many thanks to Whiler ;-) in
 * http://fr.piwigo.org/forum/viewtopic.php?pid=162907#p162907
 *
 * @param string s: string to split
 *
 * @return : array of each character of s
*/
function LCAS_mbStringToArray($s) {
  return preg_split('//u', $s, -1, PREG_SPLIT_NO_EMPTY);
}