Unable to query related table in offline sqlite database replica

[Anonymous User]

I am working with the latest version of the Android SDK (2.2.3) and AGO Sync capabilities to provide a local, offline SQLite database.  I have created a feature service that includes a feature layer and related a table that is essentially a 'stand alone' table, rather than a related feature layer.  The relationship information is coming across fine in the SQLite database replica downloaded from the feature service.  I am able to query the feature layer for it's relationship, and use that to formulate a query of the related tables.  The problem is that it keeps coming up with an empty result when I query the related table, even though there are data in that table.  It seems like there is good support for related tables if they are feature layer tables, but not if they are stand alone business tables.  I am currently querying the related table by using the FeatureTable class, because that seems to be the only appropriate class within com.esri.core.geodatabase for querying an offline SQLite database.  I thought that there might be a generic 'Table' class that would support related tables that are not FeatureTables, but I don't see one in the API reference.  Querying the related table using the FeatureTable class does not appear to be working.  In the code snippet below, the results are correct for FlightlinePoints which is a Point Layer, but return an empty result for Mission, which is a table.  Does anyone know if query of non-feature tables is supported using ArcGIS Android version 2.2.3 API?  If so, can you provide API reference or code snippet showing this in action?  If not, is there any workaround other than rolling our own SQLite database using standard Android tools and coding the CRUD to sync it programatically with the standard AGS REST endpoint?

Any thoughts or suggestions would be much appreciated.

Thanks,

Kerry

[WillCrick]

Hi Kerry,

Querying standalone tables should work. You are using the correct class, standalone tables are FeatureTables but they just don't have a geometry field, the same way they are represented in the feature service. Ate you able to post your code and maybe even your feature service endpoints so we can take a look?
Thanks
Will

[Anonymous User]

Thanks for the reply on this issue, here are some more details about what we have tried and what we are finding:

We use the code below to get the FeatureLayers from the local geodatabase. 
geodatabase.getGeodatabaseTables() is used get the collection of tables (as GeodatabaseFeatureTable objects).

We set the feature service endpoint to public (shared to everyone) and it can be found at:
http://services1.arcgis.com/TODNt0c7hfy5HvPN/arcgis/rest/services/WaldoFlightlines/FeatureServer

I have attached a copy of the SQLite geodatabase that was created by the generate replica and downloaded to the device.

Here is a code snippet showing the basic steps we are using as an attempt to query the stand alone table.  The result of running this in our application is that for "Mission", the variable 'count' is set to 0 indicating that no features were found.  For "FlightlinePoints" the variable count is 74.  There is one record in the Mission table, and this can be seen by querying the feature service using the HTML query interface.  We have not been able to verify the local data in the SQLite database using anything other than the ArcGIS Android API.  Our standard tools like sqlite.exe, spatialite_gui, ArcMap, etc. aren't able to locate or display the feature tables.  Are there any tools that Esri knows of for interigating local SQLite geodatabases to support testing by confirming data values?


for (GeodatabaseFeatureTable gdbFeatureTable : geodatabase.getGeodatabaseTables()) {
               
                if (gdbFeatureTable.getTableName().compareToIgnoreCase("Mission") == 0){
                                FeatureLayer missionFeatureLayer = new FeatureLayer(gdbFeatureTable);
                }
               
                if (gdbFeatureTable.getTableName().compareToIgnoreCase("FlightlinePoints") == 0){
                                FeatureLayer flightlinePointFeatureLayer = new FeatureLayer(gdbFeatureTable);
                                mapView.addLayer(flightlinePointFeatureLayer);
                }
               
                selectAllFeatures(missionFeatureLayer.getFeatureTable());
                selectAllFeatures(flightlinePointFeatureLayer.getFeatureTable());
}

//This function is a basic selection function that should select all of the features in the table.

public void selectAllFeatures(FeatureTable table){
                QueryParameters query = new QueryParameters();
                query.setWhere("1=1");

                String[] outFields = new String[1];
                outFields[0] = "*";
                query.setOutFields(outFields);

                table.queryFeatures(query, new CallbackListener<FeatureResult>() {

                                @Override
                                public void onCallback(FeatureResult result) {


                                                //  When I get the result of the selection, if the selected FeatureTable
                                                // is the Flightline layer, I get all results. If it is the Mission layer I get none.
                                                // I can only conclude that this is the case because the Mission layer is a
                                                 // stand-alone table and not a feature class. Is there any other way to select
                                                 // items from a stand-alone table using the disconnected geodatabase?
                                                  
                                                Long count = result.featureCount();
                                }
                });
}


Thanks in advance!

- Kerry

[RamaChintapalli]

Hi Kerry,

We cannot initialize a FeatureLayer from the GeodatabaseFeatureTable (if the table doesn't have geometry). So for GeodatabaseFeatureTable without geometry(in this case, the related table), you can use the GeodatabaseFeatureTable class to query the features without constructing the FeatureLayer .

We can also query the related features of a GeodatabaseFeature from the FeatureLayer by querying its related Tables as shown below,

 List<Relationship> relations = ((GeodatabaseFeatureTable) gdbFeature.getTable()).getRelationships();
 
 for (Relationship relationship : relations) {
  .....
  long[] objectIds = {gdbFeature.getId()};  

 RelatedQueryParameters relatedQuery = new RelatedQueryParameters();
 relatedQuery.setRelationshipId(relationship.getId());
 relatedQuery.setObjectIds(objectIds);
 ....
 Map<Long, FeatureResult> relatedFeatures = ((GeodatabaseFeatureTable) gdbFeature
        .getTable()).queryRelated(relatedQuery, null)
        
 ....

} 

BTW, something went wrong with your attachment, I get an empty geodatabase when I download it. I am using SQLiteSpy to view the data within the geodatabase

Thanks
Rama

[Anonymous User]

Rama,

Thanks for the reply and for suggesting SQLiteSpy.  This tool seems to do a much better job at providing access to the SQLite geodatabase than other SQLite tools we have used.  On further interrogation of the SQLite geodatabases that we are getting from the createReplica service, I can see that we aren't getting any records in our related table.  We have tried using a null input geometry as well as an envelope that includes the entire globe.  In both cases, the replica contains all of our feature layer records but no records in our related table.  Our feature service can be found here: services1.arcgis.com/TODNt0c7hfy5HvPN/ArcGIS/rest/services/WaldoFlightlines/FeatureServer/ .  Can you verify the expected behavior for createReplica with a) related and b) stand alone tables?  Does the cardinality of of related tables alter the expected results?  Are you able to obtain a replica from the above feature service createReplica endpoint which contains Mission records?

Thanks,

Kerry

[RamaChintapalli]

Kerry,
Looks like the service is secured, I am getting 'Token Required' message while trying to access the service. So I couldn't createReplica from your service.

Here is a link on preparing data for offline. See the section 'Attachments and relationship classes' to verify if your data is honoring the relation as mentioned in the article.
http://resources.arcgis.com/en/help/main/10.2/0154/0154000006m1000000.htm

Thanks
Rama

[Anonymous User]

Rama,

Thanks for the continued follow-up on this issue.  Well, we have been running a large number of tests of various data models, service call parameters, SQLite testing, etc. and I want to document what we are seeing and ask for confirmation of these findings.  Below is a list of things that we believe to be true about sync-enabled feature services based on our testing results:

1) Sync-enabled feature services must secured, they cannot be shared with everyone
2) Stand alone tables which are not included in relationships can be included in a feature service, but they will never have any data in them in a SQLite geodatabase replica
3) For stand alone tables to include data when creating a replica, they must meet the following conditions:
  a) Be the child of a feature class (that is they must be included in a relationship class that goes from a feature class as the origin to the stand alone table as the destination)
  b) There must be only 1 parent feature class - a stand alone table may not be the child (destination) of two feature classes.  Another way of saying that is that two feature classes can't point to the same stand alone table
  c) They must be the direct child in the relationship, not a grandchild.  Another way of saying that is you cannot daisy chain relationships where a feature class points to a stand alone table, and that table points to a second stand alone table.

When we obey all of these rules we are able to create a complex data model, publish as a sync-enabled feature service, create a replica and verify that all tables contain data.  Can you please confirm that these rules are all expected constraints on sync-enabled feature services?

Thank you,

Kerry

[RamaChintapalli]

Kerry,

Please find the comments for each of your questions.
1) Sync-enabled feature services must secured, they cannot be shared with everyone

Sync enabled feature services are like any other item in your organization content. There are no special privileges, they will honour the organization security settings.

2) Stand alone tables which are not included in relationships can be included in a feature service, but they will never have any data in them in a SQLite geodatabase replica

Answer to this question should clear lot of your other questions.

You should set a LayerQuery (where clause) on the table when you call create replica to include data from standalone table(if not related by default).

http://resources.arcgis.com/en/help/arcgis-rest-api/#/Using_sync/02r3000000rs000000/

Click on the screenshot attached:
[ATTACH=CONFIG]34811[/ATTACH]

3) For stand alone tables to include data when creating a replica, they must meet the following conditions:
a) Be the child of a feature class (that is they must be included in a relationship class that goes from a feature class as the origin to the stand alone table as the destination)

This is not a requirement

b) There must be only 1 parent feature class - a stand alone table may not be the child (destination) of two feature classes. Another way of saying that is that two feature classes can't point to the same stand alone table

This one also should not be a requirement

c) They must be the direct child in the relationship, not a grandchild. Another way of saying that is you cannot daisy chain relationships where a feature class points to a stand alone table, and that table points to a second stand alone table.

This should be OK as long as all the datasets are in the service. If the feature class and the 2 stand alone tables are in the service and create replica includes all 3, they will be included

Thanks
Rama

[Anonymous User]

Rama,

Unfortunately, we have already tried all of your suggestions and are not able to reproduce the functionality you describe.  The list of hypothesis we provided are based on extensive trial and error and what we actually found to work and not work.  I am hopeful that you know of some way to make this functionality work as it would be a huge benefit to us and our clients if this worked the way you describe.  Can you attempt to publish a few services that could demonstrate this functionality in working feature services?  If so, please publish 2 public (shared with everyone) sync-enabled feature services: in the first service, please include one feature class, one 'stand alone' table that participates in a relationship and one 'stand alone' table that does not participate in a relationship.  In the second, please include 2 feature classes and a single 'stand alone' table that is a the destination table in two relationships, one from each of the feature classes.  For each of these examples, please ensure that there is at least one record available in each table.  Finally, please provide an example for each of how to create a replica which includes records in each of these tables.  The easiest way to communicate this might be a screengrab of the createReplica REST endpoint rendered as an HTML page with all of the settings populated and ready to be called.  I am hoping we can close the loop on this and either verify that it truly is working as you describe or identify and document any issues in with this capability.

Thank you,

Kerry

[RamaChintapalli]

Kerry,

Here are the findings for the both the scenarios that you have asked for.

1)In the first service, please include one feature class, one 'stand alone' table that participates in a relationship and one 'stand alone' table that does not participate in a relationship.

Please see the rest info for the service in the attachment.
[ATTACH=CONFIG]34823[/ATTACH]
In the above screenshot, layer '0' is feature class, layer '3' is the table related to layer '0' by globalid and layer '4' is a standalone table(no relation)

Attaching the createReplica REST html page.
[ATTACH=CONFIG]34824[/ATTACH]

The downloaded database has the data from layer '0', related table layer '3' and standalone table(no relation) layer '4'. Notice the  layerQuery for the standalone table(no relation)

2) In the second, please include 2 feature classes and a single 'stand alone' table that is a the destination table in two relationships, one from each of the feature classes

This looks like an issue with the hosted service when the dataFormat is SQLite(working fine with JSON). Notified to the server/online team about it. It is working fine with the On-premise Server.

Please see the rest info for the service in the attachment.
[ATTACH=CONFIG]34825[/ATTACH]
  In the above screenshot, layer '0' is feature class, layer '1' is another feature class and layer '2' is related to both layer '0'(by globalid) and layer '1'(by user defined field)
[ATTACH=CONFIG]34827[/ATTACH]
  Attaching the createReplica REST html page .
[ATTACH=CONFIG]34826[/ATTACH]

Let me know if you are seeing any different behavior than this with your own services.

Thanks
Rama