[fdo-users] Adding new featureclass from existing table

[Brent Robinson]

Hi,

One other option I forgot to mention would be to convert the database to a non-FDO one. However, if the FDO metadata is being used to provide some functionality not available through non-FDO databases, then this would not be an option.

If feasible, converting the database is fairly simple.  As a first step, the metadata can be disabled by renaming f_schemainfo to something else. Once this is done, a class will automatically be generated for each table, when the datastore is opened via FDO. After that, new tables can simply be added, with no extra effort to expose them through FDO. If everything works ok, the f_ tables can eventually be backed up and removed altogether. 

Brent.

-----Original Message-----
From: fdo-users-bounces at lists.osgeo.org [mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of Bhogi
Sent: Friday, October 16, 2009 1:14 PM
To: FDO Users Mail List
Subject: Re: [fdo-users] Adding new featureclass from existing table

Brent,

thank you very much for your lengthy advice!
Fortunately all my FDO scenarios are very simple, so I will take your 2) advice 
at the bottom.
At the moment, I simply left classid set to 0, and featureclasses still worked 
fine. I suspected this field is ignored if my featureclasses remain simple. You 
confirmed this, great!

Thanks again.


Brent Robinson wrote:
> Hi,
> 
> If all of your geometries have the same coordinate system then one way to populate the f_ tables automatically would be to do the following:
> 
>     - create the table(s) in the FDO enabled database.
>     - create a second non-FDO database
>     - copy the table(s) to the second database
>     - go back to the FDO enabled database and add the revisionnumber and classid columns to the table(s).
>     - Use the DescribeSchema and DescribeSchemaMappings commands to generate the feature class and
>       Physical mappings, for each table, from the second database. The Physical Mappings keep track of the 
>       correspondences between the generated feature class and properties and its table and columns. Use 
>       ApplySchema to apply these to the FDO enabled database. This can be done by the following sample 
>       code:
> 
>     FdoPtr<IConnectionManager> manager = FdoFeatureAccessManager::GetConnectionManager ();
>     FdoPtr<FdoIConnection> conn1 = manager->CreateConnection (L"OSGeo.SQLServerSpatial");
>     // Customize connect string for your installation
>     conn1->SetConnectionString(   
>         L"service=<sql_server_instance>;Username=<user>;password=<password>;datastore=<second_database>" );
>     conn1->Open();
> 
>     // Get the feature schemas from the second database
>     FdoPtr<FdoIDescribeSchema>  pDescCmd = 
>         (FdoIDescribeSchema*) conn1->CreateCommand(FdoCommandType_DescribeSchema);
>     FdoFeatureSchemasP schemas = pDescCmd->Execute();
> 
>     // Get the physical mappings from the second database
>     FdoPtr<FdoIDescribeSchemaMapping>  pDescMappingsCmd = 
>         (FdoIDescribeSchemaMapping*) conn1->CreateCommand(FdoCommandType_DescribeSchemaMapping);
>     pDescMappingsCmd->SetIncludeDefaults( true );
>     FdoSchemaMappingsP mappings = pDescMappingsCmd->Execute();
> 
>     FdoPtr<FdoIConnection> conn2 = manager->CreateConnection (L"OSGeo.SQLServerSpatial");
>     // Customize connect string for your installation
>     conn2->SetConnectionString( 
>         L"service=<sql_server_instance>;Username=<user>;password=<password>;datastore=<fdo_database>" );
>     conn2->Open();
> 
>     FdoPtr<FdoIApplySchema>  applyCmd = 
>         (FdoIApplySchema*) conn2->CreateCommand(FdoCommandType_ApplySchema);
>     applyCmd->SetIgnoreStates(true);
> 
>     for ( int i = 0; i < schemas->GetCount(); i++ ) 
>     {
>         FdoFeatureSchemaP schema = schemas->GetItem(i);
>         applyCmd->SetFeatureSchema(schema);
> 
>         // Apply corresponding physical mappings. These contain mappings between classes and tables,
>         // and properties and columns. This causes the feature classes to be latched onto existing
>         // tables rather than creating new tables.
>         FdoPhysicalSchemaMappingP mapping = mappings->GetItem(conn2, schema->GetName() );
>         applyCmd->SetPhysicalMapping( mapping );
>         applyCmd->Execute();
>     }
> 
> The above uses the FDO unmanaged API but if you use the managed API, the code will be similar.
> 
> This will only work if the FDO datastore has only one spatial context, and all geometries in the new table have the same coordinate system as this spatial context. If there are multiple spatial contexts, I'll need to dig up some more code to handle this. 
> 
> The above can be done for multiple new tables. The steps do not have to be repeated for each individual table.
> 
> There are a couple of options for dealing with the classid and revisionnumber:
> 
> 1) Set default values for these columns. The default for revisionnumber must be set to 0. For classid, check the f_classdefinition row for the table. The default value to use will be in f_classdefinition.classid. Now you can insert rows without having to explicitly set these columns.
> 
> 2) If you don't plan to use class inheritance or the optimistic locking support provided by revisionnumber, then classid and revisionnumber can be deleted from the table. The corresponding properties must also be removed. This can be done by going into f_attributedefinition and removing the two rows where tablename is your table and columnname is "classid" or revisionnumber". FDO is able to function without these two system properties being present in a feature class. 
> 
> The steps to handle classid and revisionnumber can potentially be eliminated by some enhancements to the SQLServerSpatial provider. I'll log a trac ticket for this.
> 
> Brent.
> 
> -----Original Message-----
> From: fdo-users-bounces at lists.osgeo.org [mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of Bhogi
> Sent: Friday, October 16, 2009 5:14 AM
> To: FDO Users Mail List
> Subject: Re: [fdo-users] Adding new featureclass from existing table
> 
> Hi,
> 
> Yes, I know about this, tryed it and indeed it works. But the database in use by 
> FDO will always have all f_ tables. When these tables are present the provider 
> unfortunately does no auto detection.
> 
> Thanks.
> 
> 
> Orest Halustchak wrote:
>> Hi,
>>
>> For the SQL Server provider, the fdo metadata tables are optional. You could just create tables that have geometry or geography columns into a new empty SQL Server database and the provider will detect them as feature classes. Have you tried that?
>>
>> Thanks,
>> Orest.
>>
>> -----Original Message-----
>> From: fdo-users-bounces at lists.osgeo.org [mailto:fdo-users-bounces at lists.osgeo.org] On Behalf Of bhogi
>> Sent: Thursday, October 15, 2009 6:47 AM
>> To: fdo-users at lists.osgeo.org
>> Subject: [fdo-users] Adding new featureclass from existing table
>>
>>
>> Hi,
>>
>> I'm adapting a few apps that export GIS data to SQL server 2008. The goal is
>> to make exported data also available to FDO.
>>
>> In the beginning I solved the problem of making database tables available to
>> FDO by inserting class definitions etc. in f_ tables and adding classid and
>> revisionnumber fields to the tables. Althou working this "hack" is not
>> acceptable in the long run.
>>
>> I want to do this the correct FDO way, but don't know how.
>>
>> There are two approaches I was considering:
>>
>> 1) Create a table first and make it an FDO featureclass later
>> BUT If I create a new feature class intended for the already existing table,
>> the fdo will make a new differently named table.
>> I could change the values in f_ tables, but that's reverting to "hacking"
>> again.
>>
>> 2) Make an FDO featureclass and insert new features in corresponding table
>> via SQL as I did before
>> BUT classid and revisionnumber need to be set. If I set these values via SQL
>> I see this as reverting to "hacking" again.
>>
>> Is there a way to acomplish this?
>> Have I overlooked something?
>> Is the 2) way considered as a valid way to insert features in featureclass.
>> What if the FDO internals will change regarding these fields...
>>
>> Adding new features the FDO way is not an option, there's just too much code
>> to adapt.
>>
>> Thanks.
>>
> _______________________________________________
> fdo-users mailing list
> fdo-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/fdo-users
> _______________________________________________
> fdo-users mailing list
> fdo-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/fdo-users
> 
_______________________________________________
fdo-users mailing list
fdo-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/fdo-users