Some column names aren't escaped in the script generated with ObjectContext.CreateDatabaseScript()

[Dennis Wanke]

Hard to believe, but this ordinary SSDL type definition

Code: Select all

<EntityType Name="CatalogItem">
	<Key>
		<PropertyRef Name="Id" />
	</Key>
	<Property Name="Id" Type="int" Nullable="false" />
	<Property Name="SKU" Type="NVARCHAR2" MaxLength="100" />
	<Property Name="URL" Type="NVARCHAR2" MaxLength="100" />
</EntityType>

causes the following statement to be generated as a part of the database script (note SKU and URL columns are not escaped):

Code: Select all

CREATE TABLE "CatalogItem" ( 
  "Id" NUMBER(10) NOT NULL,
  SKU NVARCHAR2(100) NULL,
  URL NVARCHAR2(1000) NULL,
  PRIMARY KEY ("Id")
)

I think the two columns are not escaped because the corresponding SSDL properties are in uppercase. I'm not quite sure if this can lead to problems, but wouldn't it be safer to always escape all SQL identifiers? Just to be always on the safe side, avoiding any possible collisions with reserved words and the like.

[Shalex]

If there are no quotes around the name of your table/column, the resulting table/column name will be converted to upper case in Oracle. That's why we do not quote names in upper case. Why do you need quotation in this case?

[Dennis Wanke]

I think you misunderstood the problem. We have an SSDL schema (as a part of Entity Model) with some entity type defined. The type includes some properties with names in camel case and some in upper case. Once ObjectContext.CreateDatabaseScript() get called given a model with that SSDL schema, the provider (dotConnect) iterates over type definitions, encounters the mentioned one and generates a CREATE TABLE statement from it. While doing this, the provider deliberately escapes some column identifiers but not the others. My suggestion would be to always escape all SQL identifiers - this would make the generated script more reliable and simplify the provider logic the same time (as there is probable some extra logic to avoid collisions with reserved words and so on). I edited my original post to stress this point.

[Shalex]

I think the two columns are not escaped because the corresponding SSDL properties are in uppercase.

This is correct.

I'm not quite sure if this can lead to problems

Our current implementation quotes SQL identifiers only if this is required. Have you encountered any particular problems with quotation?

[Dennis Wanke]

No problems so far.