[fdo-users] Re: SL-King SpatiaLite FDO Provider - cannot create multiple tables

[Haris Kurtagic]

Hi Ryan,

Thank you for your emails and well described problems.

I believe your approach is ok. I could quickly either set capability
SupportsSchemaModification to false or I could check for "added flag"
for classes in schema and create only those.
It still wouldn't be full SupportsSchemaModification but could help at
least with most common case of adding new table.

Haris

On Wed, Mar 23, 2011 at 7:29 PM, Ryan Proulx <ryan.proulx at safe.com> wrote:
> Perhaps this is not quite the right forum, but despite being successful
> using the new SL-King SpatiaLite FDO Provider (version 0.1.2 as made
> available here) to read from SpatiaLite databases we have encountered a few
> issues writing to SpatiaLite.
>
> One of the primary issues in using this provider is that we were unable to
> write to more than one table to a schema. An SQL error is raised when
> attempting to create the second table indicating that the first table
> already exists. In this example creating two identical tables named 'states'
> and 'states2' respectively the 'states' table is written correctly first and
> then the following exception is thrown for the second time we apply schema
> for the 'states2' table:
>
> 'class FdoException exception: Error prepare statement 'CREATE TABLE states
> ( AREA DOUBLE,POP1990 DOUBLE,POP2000 DOUBLE,POP90_SQMI DOUBLE,PRIMARYINDEX
> INTEGER,STATE_ABBR TEXT,STATE_FIPS TEXT,STATE_NAME TEXT,SUB_REGION TEXT,
> PRIMARY KEY ( PRIMARYINDEX ) ) ,! sqlite_error='table states already exists'
>
> It appears that though the provider claims 'SupportsSchemaModification', all
> features for a schema are expected to be provided at once as it attempts an
> SQL create table statement for each table currently in the schema.
> Thus, if a schema is built up and applied one table at a time, as we do in
> our application, the above error results. Similarly there is no support for
> dropping tables when they are removed from the schema.
>
> Is this the wrong approach (it does seem to work nicely in other providers)?
> Is this planned on being fixed soon?
>
> For now we have coded around the creation issue but have had to disable
> table dropping.
>
> Thanks
> ryan
>