These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
Previous page12
 

SDE and IEC for Inferno 1.0
Author
Previous Topic Next Topic
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#21 - 2012-06-04 16:00:54 UTC
http://stackoverflow.com/questions/10086532/postgresql-how-can-i-convert-all-columns-in-my-database-to-case-insensitive


\t on
select 'ALTER TABLE '||'"'||table_name||'"'||' RENAME COLUMN '||'"'||column_name||'"'||' TO ' || lower(column_name)||';'
from information_schema.columns
where table_schema = 'public' and lower(column_name) != column_name;
\g /tmp/go_to_lower
\i /tmp/go_to_lower


\o /tmp/go_to_lower
select 'ALTER TABLE '||'"'||tablename||'"'||' RENAME TO ' ||
lower(tablename)||';' from pg_tables where schemaname = 'public';
psql -U username database < /tmp/go_to_lower

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Coreth Oake
Bacon Chip Cookie Ice Cream Sammich
#22 - 2012-06-04 17:30:54 UTC
I suppose that will do, thanks.
Khorkrak
KarmaFleet
Goonswarm Federation
#23 - 2012-06-04 17:59:33 UTC  |  Edited by: Khorkrak
I went through much of the same hassle initially. Converting to all lowercase though results in even more ridiculous names such as mapsolarsytems and invuniquenames. I put up with that for a few weeks and then finally had enough of it. I ended up abandoning their ugly naming convention and simply changed everything to lowercase, underscore separated sans the Hungarian notation-esque prefix to convert their schema into something much more tolerable.

invSolarSystems becomes solar_systems
invTypes becomes types

and so on. No more case sensitivity issues, less typing / space consumption in code, less clutter, more readable. Win. The only issue is the invNames table becoming names which is a reserved word in SQL-92 and SQL-99 but not in SQL-2003 interestingly. Works fine in MySQL and PostgreSQL.

SQL Reserved Words

Developer of http://www.decloaked.com and http://sourceforge.net/projects/pykb/
diabeteman
Diabete Studios
#24 - 2012-06-26 19:38:30 UTC
Coreth Oake wrote:
All the identifiers in the psql conversion are quoted, which makes them case sensitive. This is a problem because to reference case sensitive identifiers you have to use quotes everywhere, for example, SELECT * FROM invTypes doesn't work, you would have to use SELECT * FROM "invTypes" instead.

Yeah this is more a problem of the pgsql interpreter which IS case sensitive but has a strange behaviour : when you don't put double quotes, the interpreter considers that everything is lowercase (out of the blue...).

It means that invBlueprintTypes will be interpreted as invblueprinttypes

I guess you will have to add double quotes everywhere in your SQL statements (which is a good practice anyway ^^)

Initiator and CTO of Eve Corp. Management
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#25 - 2012-06-26 19:41:51 UTC  |  Edited by: Steve Ronuken
Oracle does much the same thing. but it uppercases everything that's not quoted.



New extracts

https://forums.eveonline.com/default.aspx?g=posts&t=126288&find=unread

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Previous page12