Fwd: Case Sensitivity and Joins

[Jakub Vrana]

-------- Forwarded Message --------
Subject: Case Sensitivity and Joins
Date: Sat, 11 Apr 2015 00:11:36 -0400
From: Dave Quinn-Jacobs <d...@cogentqi.com>
To: vr...@php.net



Hello Jakub,

I have started using NotORM for a project, and I love it for it’s
simplicity of use. I’m having case sensitivity problems, however. I’ve
gotten around most of them by using the structure discovery and methods.
My table names are InCamelCase with a leading upper case letter, and
my columNames lead with a lower case letter. My id fields are
inCamelCaseId, where the first letter of the table name is lower and Id
is appended to arrive at a foreign key.

Nearly everything is working except that when I reference a joined
table, it returns null on the Linux side, but works just fine on a Mac
OS X server. On the Linux side the lower_case_table_names is set to 2
in my.cnf.

$module=$planRecord->Module;// This line returns empty.
$module=$this->api->db->Module()->where('id=?',$planRecord["moduleId"])->fetch();//
This line works.

I was wondering if you have any idea why this is happening, and if there
is a fix I can make? I’d hate to lose the nice ability you have put in
to do joins as in the first line.

Thank you very much for your time, and your contribution of this software.

Dave Quinn-Jacobs, CTO
607-227-7351
<http://cogentqi.com>

[Allysson David]

I suppose you're running MySQL.

I had a similar problem once while changing database between Windows and Linux server.

Turns out Linux IS case sensitive.

MySQL documentation says about lower_case_tables_names = 2:

Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1.

 

I suppose Mac OS X isn't case sensitive while Linux is.

Almighty solution? Change lower_case_tables_names to 1 or abandon Linux server.

Possible alternative solution:

I don't have easy access to a Linux hosted database at this moment, but what if you changed from the Structure Discovery to a Custom Structure with your own rules?

Note: I wasn't sure if your Primary Key is the default `TableName.id` or `TableName.tableNameId`. If the column name for the Primary Key follows the same rule as the Foreign Key, please change the first param in the Structure Constructor accordingly.

class myStructure extends NotORM_Structure_Convention {
    function getReferencedColumn($name, $table) {
        $r = parent::getReferencedColumn($name, $table);
        $r[0] = strtolower($r[0]);
        return $r;
    }
}

...

$db = new NotORM($pdo, new myStructure('id', '%sid'));

Please test and report.

- A. David

[Allysson David]

So, it means the scrap structure I suggested didn't work? Damm.

Well, it's sad you ended up going for the default naming, but if it's working I guess that's good enough.

Glad to help.

A. David