Valentina Database Identifiers Case Sensitive
Starting from v.4.0 case-sensitive identifiers are supported. There is new persistent database property - IdentsCaseSensitive which can be set to true or false. Default state is false.
SET PROPERTY IdentsCaseSensitive OF DATABASE TO true; GET PROPERTY IdentsCaseSensitive OF DATABASE; SHOW DATABASE PROPERTIES;
Here, by database identifiers we mean following:
- Table names
- View names
- Field names
- Trigger names
- Link names
- IndexStyles names
Please note - stored procedures are always case-insensitive. It means you can not do something like:
SET PROPERTY IdentsCaseSensitive OF DATABASE TO true; CREATE PROCEDURE sp1() BEGIN END: -- Next query return error - sp1 name is not unique. CREATE PROCEDURE SP1() BEGIN END:
When IdentsCaseSensitive is set to false everything works as in early versions - i.e. “t1” and “T1” table names are considered as the same names. Having table “t1” you will get an exception trying to create table “T1”, also it is no difference which case you use in the query and so on.
SELECT * FROM t1; SELECT * FROM T1;
In other words any identifiers are case insensitive.
You may set IdentsCaseSensitive to true which means - starting from now you should use identifiers exactly in the case they were stored in the schema. In contrast to Oracle (stores idents in upper case) and MySQL (stores idents in lower case) Valentina database stores any identifier in originally given case never mind which value is set to IdentsCaseSensitive.
Example: This query will work without any problem:
SET PROPERTY IdentsCaseSensitive OF DATABASE TO false; CREATE TABLE t1 ( f1 Long ); SELECT * FROM T1;
But now it is not. “T1” table is not found (because there is a single table “t1” - not “T1”)
SET PROPERTY IdentsCaseSensitive OF DATABASE TO true; SELECT * FROM T1;
Having IdentsCaseSensitive you may have “T1” table along with “t1”.
CREATE TABLE t1 ( f1 Long ); SET PROPERTY IdentsCaseSensitive OF DATABASE TO true; CREATE TABLE T1 ( f1 Long );
Attention, having “t1” and “T1” you will be not able to switch IdentsCaseSensitive to false. In this case you should make all database identifiers case-insensitive-uniquely-named before.