[Building Sakai] 2.9.0 DB Conversion, MySQL script error

[Steve Swinsburg]

The recommendation to run lower case table names on MySQL has been around since 2007, which corresponds to Sakai 2.4:
https://confluence.sakaiproject.org/pages/diffpagesbyversion.action?pageId=37290295&selectedPageVersions=10&selectedPageVersions=9

Note also that I am not advocating that developers do as they please. I am talking about NEW developers not knowing the unwritten rules you are talking about, and inadvertently doing the 'wrong' thing. How can someone follow the rules if they are not communicated anywhere?

There is already a discussion on the TCC about standardising SQL case going forward, then the guidelines will be written down and bugs can be correctly marked as such. 

regards,
Steve

On 26/01/2013, at 5:54 PM, Stephen Marquard <stephen.marquard at uct.ac.za> wrote:

> As of Sakai 2.6 / 2009, lower case table names was not a recommendation, e.g.:
> 
> https://confluence.sakaiproject.org/pages/viewpage.action?pageId=37292839
> 
> As you'll know, Sakai mysql production deployments go all the way back to 2005 (possibly earlier).
> 
> As per:
> 
> http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html
> 
> "If you plan to set the lower_case_table_names system variable to 1 on Unix, you must first convert your old database and table names to lowercase before stopping mysqld and restarting it with the new variable setting."
> 
> So until such time as there is agreement that all Sakai tables should be converted to lower case for all production deployments and there's a conversion script to this effect in a major upgrade (e.g. 2.10.0), please stop advocating the idea that developers can do anything they like with table and column name case, and this is somehow a production / DBA problem.
> 
> Regards
> Stephen
> 
> From: Steve Swinsburg [steve.swinsburg at gmail.com]
> Sent: 26 January 2013 05:41 AM
> To: Stephen Marquard
> Cc: sakai-dev at collab.sakaiproject.org; Sam Ottenhoff
> Subject: Re: [Building Sakai] 2.9.0 DB Conversion, MySQL script error
> 
> We are going around in circles. The recommendation clearly states that you should use lower case table names for your setup. There is NO recommendation or rule that states what case SQL should be written in. 
> 
> Since there is no requirement for case sensitivity in SQL, a developer could join the project and write a tool that (unfortunately) contains the wrong case SQL. But because they were following the existing recommendation, there is no problem. Its only a problem for the minority that don't follow the existing recommendation.
> 
> You can't enforce something or call it a blocker without having a guideline as to what is expected. 
> 
> 
> On Fri, Jan 25, 2013 at 8:55 PM, Stephen Marquard <stephen.marquard at uct.ac.za> wrote:
> I’m with Sam on this one. Developers need to use the correct case for table and column names  (i.e. the exact case in which it was created on a case-sensitive environment), in code and conversion scripts.
> 
>  
> To say “there’s no guarantee that developers will do this in future” is a classic case of shifting the burden, and basically like saying developers are lazy and as a Sakai community we have no ability to meet basic quality standards, which would be a sad day.
> 
>  
> Cheers
> Stephen
> 
>  
> ---
> 
> Stephen Marquard, Acting Director
> 
> Centre for Educational Technology, University of Cape Town
> http://www.cet.uct.ac.za
> stephen.marquard at uct.ac.za 
> Phone: +27-21-650-5037 Cell: +27-83-500-5290
> 
>  
> From: sakai-dev-bounces at collab.sakaiproject.org [mailto:sakai-dev-bounces at collab.sakaiproject.org] On Behalf Of Sam Ottenhoff
> Sent: 25 January 2013 03:29 AM
> To: Steve Swinsburg; Stephen Marquard
> Cc: sakai-dev at collab.sakaiproject.org
> 
> 
> Subject: Re: [Building Sakai] 2.9.0 DB Conversion, MySQL script error
> 
>  
>  
>  
> On Thu, Jan 24, 2013 at 7:47 PM, Steve Swinsburg <steve.swinsburg at gmail.com> wrote:
> 
> So how is a new developer meant to know all of these unwritten rules if they aren't described anywhere?
> 
>  
> As a community, we've decided to support MySQL and Oracle.  I don't like fixing Oracle syntax issues especially because I don't run Oracle.  But I help fix them because I want institutions that run Oracle to use Sakai.  If Oracle changes syntax that breaks Sakai queries, we'll fix Sakai.  It's yet another thing that we developers need to learn and adapt to.  If you use opposite cases with MySQL, someone will eventually catch it and correct it.  Here are the rules of our dependency, MySQL: http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html
> 
>  
>  
> I disagree that there are too many requirements. 
> 
>  
>  
> I can't imagine you are in the majority.
> 
>  
>  
> My point is that if the rule you proposed was enforced on table creation, not just updates, you wouldn't have that mess. 
> 
>  
>  
> The rule I proposed was consistency, that's all. I'm too busy right now to make consistent all table names in Sakai CLE. Until the day I have the time, or someone else volunteers, I will continue to fix JIRAs like the one originally reported in this thread.
> 
>  
> --Sam
> 
> UNIVERSITY OF CAPE TOWN 
> 
> This e-mail is subject to the UCT ICT policies and e-mail disclaimer published on our website at http://www.uct.ac.za/about/policies/emaildisclaimer/ or obtainable from+27 21 650 9111. This e-mail is intended only for the person(s) to whom it is addressed. If the e-mail has reached you in error, please notify the author. If you are not the intended recipient of the e-mail you may not use, disclose, copy, redirect or print the content. If this e-mail is not related to the business of UCT it is sent by the sender in the sender's individual capacity.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20130129/2831387b/attachment.html