necouchman commented on PR #902: URL: https://github.com/apache/guacamole-client/pull/902#issuecomment-2018789727
@mike-jumper @jmuehlner Appreciate any feedback you have on this most recent round of changes. I've spent some time trying to re-work the various JDBC modules to handle configurable case sensitivity settings. For each of the databases: * PostgreSQL is case-sensitive by default - the widely-accepted way to make it case-insensitive is to convert everything to either lower or upper-case when comparing. I opted for `LOWER()`. * MySQL is case-insensitive by default, and the community seems quite split over the best way to accomplish case-sensitive searches. The _easiest_ way, and the way I opted to go, is to use the `BINARY` keyword in the `WHERE` and `JOIN` clauses that need to be converted to case-sensitive comparisons. The _better_ way is to change the collation used in the queries; however, the challenge, here, is that it requires some knowledge of what collation is being used to begin with, which (I don't think) can be programmatically detected in a way that we can use. Maybe I'm wrong, and there is a way to do this?? The `BINARY` method has two pitfalls that I read about: 1) it ends up bypassing indexing, so there are some other creative things you have to do to insure you make use of that, and 2) it has some potential pitfalls when double-byte character sets are in use. * SQLServer - haven't even dealt with this one, yet - figured I'd get consensus on how to handle Postgres and MySQL before tackling Microsoft. From what I've read, the only way to do this is to change the collation during query time, which is going to have the same challenge as that route with MySQL - you have to know something of what collation is in use to begin with. Aside from database-specific issues, the other big thing to look at is how I've actually changed the interfaces that MyBatis uses with its mapper files to generate the required code... * I was hoping to come up with some way to use something within the XML mapper files that could query the injected `JDBCEnvironment` object and call the `getCaseSensitiveUsernames()` method without having to modify the arguments of any of the queries. I could not figure out a way - if I've overlooked it, PLEASE tell me so that I can clean this up. * Having not been able to figure out a way to do injection into the XML mapper files, I went with just modifying the arguments of the classes that need to know about the case-sensitivity, which leans to the side of breaking interface inheritance over passing useless parameters to methods that don't need it. * The other thing to consider would be if we want to (continue to) limit the case sensitivity setting to _only_ usernames (as I've done, here), or if we want to make it where we're configuring all string comparison within a given DB environment (usernames, group names, connection names...whatever strings get compared). Or somewhere in between. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: dev-unsubscr...@guacamole.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
