Android Question Should db column names be defined as lower case?

[Widget]

Is there a B4A standard to define column names as lowercase in SQLite (or any database used by B4A)?

I only ask this because I keep shooting myself in the foot.

Today I defined my column names as global constants and I use these constants in my code. Now I can use cCustomerNo instead of hard coding the column name as "CustomerNo" throughout my program. Not only will this reduce errors caused by a typo, but it makes for renaming the column name much easier at a later date.

Then I got a (not so) brilliant idea today. I used a global replace to remove the ".ToLowerCase" from all of my maps that store column information because now I will use the column name constant which has the correct case. This means I no longer have to look at ugly code like:

Map.Get(cCustomerNo.ToLowerCase) or Map.Put(cCustomerNo.ToLowerCase, Value)

and instead use:

Map.Get(cCustomerNo) or Map.Put(cCustomerNo, Value)

which I find a lot more readable. Then I discovered this "brilliant" idea has left me with a few less toes on my left foot.

Why you ask? Well I forgot the DBUtils code uses:
locRowMap.Put(locCursor.GetColumnName(i).ToLowerCase, locCursor.GetString2(i))
to return column values in the map. (The column names are set to lowercase in the map.)

Ok, after I realized this, I discover my left shoe don't fit as tightly as they did before (toes are missing).

My alternative are:

1. Put the .ToLowerCase back into each of my Map.Get/Map.Put statements and chalk it up to lessons learned.
2. Write my own MapStr class that automatically executes .ToLowerCase when referring to the map key which is always a string in this case.
3. Change all of my column names in the database to lowercase as well as my column name constants. Then I can do away with ".ToLowerCase" when referring to column names.
4. Change DBUtils to return the case sensitive version of the column name. This may cause problems down the line if Erel updates DBUtils with new subs that expect the column names to be in lower case or if other libraries reference DBUtils and expect the column names to be returned in lower case.

What is the best solution? How do you handle the sensitivity of column names? How am I ever going to count to 20 ever again?

TIA

 

[EnriqueGonzalez]

Hi!

me my self never allow a single capital letter in my tables and columns, it is not mandatory, but makes lifes so much easier.

So, with my perspective exposed:

1.- Bad idea!
2.- Good idea!
3.- Best idea!
4.- somewhat good idea!

DbUitls, it is very helpful but it is even better that it is not a library but a class, that means that even when erel change it, you can change too.

 

[Widget]

I took your advice and went with option #3. At least now the problem with forgetting to use ".ToLowercase" when referencing a column/table name in a map won't cause my program to fail. I will do the same with my customviews that use map keys that store property values in a map.

I thought about creating my own MapStr class and that would have worked but adding another sub call to every field/table/property reference would slow the program down because these values are often referenced in loops and can be called hundreds or even thousands of times. It would have been nice if Java had a Delphi equivalent of a TStringList class that has case insensitive searching and is fast, but until that day comes along I am resigned to store map keys as lower case.

 

[Cableguy]

Interesting post... May I add a question...?
What is the best way to deal with composite column names... Like "First name" or "DateOfCreation"...
If I do all lower case, it becomes harder to read the column names...
Like "dateofcreation" (ok, this one is not that difficult, but some column names can originate different words while reading if not capitalised)

 

[Widget]

Interesting post... May I add a question...?
What is the best way to deal with composite column names... Like "First name" or "DateOfCreation"...
If I do all lower case, it becomes harder to read the column names...
Like "dateofcreation" (ok, this one is not that difficult, but some column names can originate different words while reading if not capitalised)

This is how I did it.
Since my column names are now all lower case, I changed "DateOfCreation" to "date_of_creation". This will create longer column names, but is necessary to create readable identifiers. I will need to do the same with Table Names and View Names.

Using lower case identifiers seems to be a PIA, and not all databases handle case sensitivity of their identifiers exactly the same. According to SQL:2008 and SQL-99 standards, database identifiers should be case insensitive by default, but when identifiers are quoted they become case sensitive, except for SQLite which keeps it case insensitive. Does that make sense? (are you tearing your hair out yet?)

Here is an excellent article that explains the problems of case sensitivity for database identifiers:
http://www.alberton.info/dbms_identifiers_and_case_sensitivity.html

 

[EnriqueGonzalez]

"DateOfCreation" to "date_of_creation"

yes, forgot to tell you that "_" is a good naming standard.

The problem is a bit beyond the Databases, for example: mariaDB states that when it is stored on linux, you have to adhere to case sensitiviness, but you can switch it via a parameter. But if mariaDB is stored on Windows it will be case insensitive but cannot switch to the other state.

 

[Widget]

yes, forgot to tell you that "_" is a good naming standard.

The problem is a bit beyond the Databases, for example: mariaDB states that when it is stored on linux, you have to adhere to case sensitiviness, but you can switch it via a parameter. But if mariaDB is stored on Windows it will be case insensitive but cannot switch to the other state.

I find it amazing that database programmers have any hair left at all.