[Fdo-trac] [fdo-trac] #550: SQL Spatial - poor error message when trying to insert into a table without a primary key

[FDO]

#550: SQL Spatial - poor error message when trying to insert into a table without
a primary key
---------------------------+-----------------------------
 Reporter:  chriserickson  |        Owner:  brentrobinson
     Type:  defect         |       Status:  closed
 Priority:  major          |    Milestone:  4.2.0
Component:  FDO API        |      Version:  3.4.0
 Severity:  3              |   Resolution:  fixed
 Keywords:                 |  External ID:
---------------------------+-----------------------------
Changes (by jng):

 * resolution:   => fixed
 * status:  new => closed

Comment:

 In [changeset:"8402" 8402]:
 {{{#!CommitTicketReference repository="" revision="8402"
 Ticket #974, #550: SQL Server Spatial dotted spatial context and missing
 primary key error

 Two SQL Server Spatial defects, both reported against this provider.

 Ticket #974 ("Creating a spatial context with a dot (.) in the name makes
 it
 un-destroyable") reports that a spatial context whose name contains a dot
 cannot
 be destroyed. In a datastore without a meta-schema a spatial context is
 recorded
 as a geometry column of the special f_scinfo table, named after the
 context, so
 its spatial index name inherits the dots: "Spatial.Context.With.Dots"
 becomes
 f_scinfo_spatial.context.with.dots_si_srid=26741. That name is qualified
 with an
 owner - the qualifier of the containing table's name ("dbo.f_scinfo"), or,
 when
 that name is itself unqualified, the "dbo" that
 FdoSmPhSqsMgr::GetRealDbObjectName() prepends to unqualified names. A
 spatial
 index is scoped to its table in SQL Server, so the DDL must use the
 unqualified
 name, and the code produced it with name.Right(L"."), which returns
 everything
 after the first dot. That is only correct for a name that actually carries
 a
 qualifier: applied to the name read back from sys.indexes, which does not,
 it
 discarded the leading part of the name as well. The name the DROP
 statement was
 built from therefore no longer matched the name the index had been created
 with,
 and the destroy failed with "Cannot drop the index
 'dbo.f_scinfo.with.dots_si_srid=26741', because it does not exist or you
 do not
 have permission", leaving the spatial context in place.

 The owner qualifier is now removed only when the name actually starts with
 it, so
 one rule holds for both a name that carries a qualifier (new index) and a
 name
 that does not (read back from the database, which is how a later session
 sees the
 index). The qualifier is taken from the containing table's name, falling
 back to
 "dbo" for the unqualified f_scinfo name, and the remainder of the name -
 dots
 included - is kept, which is what makes the same index name reachable from
 the
 CREATE and the DROP statement.

 Ticket #550 ("SQL Spatial - poor error message when trying to insert into
 a table
 without a primary key") reports that inserting into a table with no
 primary key
 fails with "Creating/Updating a standalone instance for class 'X' is not
 allowed", which gives no hint of the cause. A table without a primary key
 reverse-engineers to a class without an identity property, which is the
 condition
 CheckClass() rejects, and the message named the class but never the table,
 although the missing primary key on that table is the whole problem. When
 the
 class is backed by a table the exception now names the table and says its
 primary
 key is missing; the previous message is kept for a class that has no
 database
 object, where there is no table to name.

 Provider:
 *
 Providers/GenericRdbms/Src/SQLServerSpatial/SchemaMgr/Ph/SpatialIndex.cpp:
   SqsUnqualifiedIndexName() removes the owner qualifier only when the name
   carries it, taking the qualifier from the containing table's name and
   defaulting to "dbo" for an unqualified table name.
   FdoSmPhSqsSpatialIndex::GetDDLName() and Add() use it in place of the
   name.Right(L".") they used before; the name given to SQL Server for an
 index
   without dots is unchanged.
 * Providers/GenericRdbms/Src/Fdo/Schema/FdoRdbmsSchemaUtil.cpp:
 CheckClass()
   throws the new message when the class has no identity property and is
 backed by
   a database object, and the previous one otherwise.
 * Providers/GenericRdbms/Nls/fdordbmsmsg.mc: adds FDORDBMS_551
 (MessageId=2776),
   "Creating/Updating a standalone instance for class '%1$ls' is not
 allowed
   because table '%2$ls' has no primary key." Existing ids are untouched;
 the
   header the message compiler generates from this file
 (Inc/Nls/fdordbmsmsg.h) is
   written by the build and is not under version control.

 Tests:
 * SqlServerFdoSpatialContextTest::testDottedSpatialContext creates a
 spatial
   context named "Spatial.Context.With.Dots", checks that it is listed,
 destroys
   it and checks that it is gone. The destroy step is what failed before
 this
   change, with the DROP INDEX error quoted above.
 * SqlServerFdoInsertTest::testDefect550 builds the ticket's table
 (char(36) id,
   geometry and varchar columns, no primary key), sets it as the class of
 an
   FdoIInsert and asserts that the exception reports the missing primary
 key.

 Verified with the SQL Server Spatial suites in the Debug configuration:
 FdoSpatialContextTest OK (6), FdoInsertTest OK (14), the two together OK
 (20),
 FdoUpdateTest and FdoDeleteTest OK (15), and FdoApplySchemaTest OK (5) -
 the last
 being the suite that exercises spatial index names without dots and the
 regression gate for the qualifier change. Both new tests were also run
 against
 the source they replace and failed there: testDottedSpatialContext with
 the DROP
 INDEX error, and testDefect550 with the message that did not identify the
 primary
 key. A sweep of sys.indexes across all 44 fdo_* test databases confirms
 that no
 spatial index name kept an owner qualifier, which is the property the DROP
 statement depends on.

 Fixes #974
 Fixes #550
 }}}
-- 
Ticket URL: <https://trac.osgeo.org/fdo/ticket/550#comment:3>
FDO <http://fdo.osgeo.org/>
Feature Data Objects