Corrected below as something went missing in the previous email.
···
From: Paul Wittle
Sent: 08 December 2020 11:58
To: Brad Hards <bradh@…4091…>; geoserver-devel@lists.sourceforge.net
Subject: RE: [Geoserver-devel] Primary key generator
So after talking with my database admin colleague they felt actually doing index creation in the databases would not be popular with database administrators but if the index was held within the GeoServer system that would be different. I’d have to look into the sql view code to see how the identifier setting works there; does it effectively store a primary key index within GeoServer.
She felt the more useful suggestion was the simpler one of just making it clearer when a dataset doesn’t have a primary key.
I don’t think images work on here so I will not screenshot things but of course this is just a discussion to see what people think but I’d put screenshots in should a feature request need to be completed.
Here is a text example though:
Feature Type Details
·
Property
|
Type
|
Nillable
|
Min/Max Occurences
|
UPRN
|
Double
|
true
|
0/1
|
CONCATENATED_ADDRESS
|
String
|
true
|
0/1
|
POSTCODE
|
String
|
true
|
0/1
|
PRIMARY_CLASSIFICATION
|
String
|
true
|
0/1
|
SECONDARY_CLASSIFICATION
|
String
|
true
|
0/1
|
TERTIARY_CLASSIFICATION
|
String
|
true
|
0/1
|
QUATERNARY_CLASSIFICATION
|
String
|
true
|
0/1
|
STATUS
|
String
|
true
|
0/1
|
GEOM
|
Geometry
|
true
|
0/1
|
This is a bunch of fields in a layer but it doesn’t indicate which of those fields is the primary key. My suggestion is to simply highlight that information in the page.
In QGIS for example it would say primary key above the table or it would be missing if there isn’t a primary key.
So, it might look like:
Property
|
Type
|
Nillable
|
Min/Max Occurences
|
UPRN
|
Double
|
true
|
0/1
|
CONCATENATED_ADDRESS
|
String
|
true
|
0/1
|
POSTCODE
|
String
|
true
|
0/1
|
PRIMARY_CLASSIFICATION
|
String
|
true
|
0/1
|
SECONDARY_CLASSIFICATION
|
String
|
true
|
0/1
|
TERTIARY_CLASSIFICATION
|
String
|
true
|
0/1
|
QUATERNARY_CLASSIFICATION
|
String
|
true
|
0/1
|
STATUS
|
String
|
true
|
0/1
|
GEOM
|
Geometry
|
true
|
0/1
|
P****Primary Key: UPRNry Key: UPRN
If it didn’t have one then it might look like this:
Property
|
Type
|
Nillable
|
Min/Max Occurences
|
UPRN
|
Double
|
true
|
0/1
|
CONCATENATED_ADDRESS
|
String
|
true
|
0/1
|
POSTCODE
|
String
|
true
|
0/1
|
PRIMARY_CLASSIFICATION
|
String
|
true
|
0/1
|
SECONDARY_CLASSIFICATION
|
String
|
true
|
0/1
|
TERTIARY_CLASSIFICATION
|
String
|
true
|
0/1
|
QUATERNARY_CLASSIFICATION
|
String
|
true
|
0/1
|
STATUS
|
String
|
true
|
0/1
|
GEOM
|
Geometry
|
true
|
0/1
|
P****Primary key missing; WFS paging will not work
Thanks for your comments though 
Cheers,
Paul
From: Brad Hards <bradh@…4091…>
Sent: 08 December 2020 08:23
To: Paul Wittle <P.Wittle@…4807…>; geoserver-devel@lists.sourceforge.net
Subject: RE: [Geoserver-devel] Primary key generator
I think you need to back up a little, and describe how you got to here. I’m not sure if this is a good idea or not, because I don’t understand the problem you are trying to solve.
Can you describe what you have seen, maybe in the form of a concrete, reproduceable example?
Brad
From: Paul Wittle <paul.wittle@…5627…>
Sent: Monday, 7 December 2020 9:17 PM
To: geoserver-devel@lists.sourceforge.net
Subject: [Geoserver-devel] Primary key generator
Hi everyone,
Please excuse me if this is a bad suggestion or has already been suggested but I was wondering if it might be worth doing a feature request to make it so that GeoServer can generate primary keys / indexes on database stores.
I suspect the popular view would be that it should be done on the database admin side but I wonder if you the people loading layers into GeoServer might sometime not have official database administrator roles. In theory the SQL to create an index is pretty simple and the database administrators can block the functionality by simply controlling the user account they connect with in GeoServer.
My thinking is that when you do an SQL view you can select the identifier but when you load a table or view directly you can’t. I wondered if it would be possible to add a similar option to the field list at the bottom of the layer creation page. I think this might help users to ensure their layers work with things like WFS 2.0 paging which I believe requires a primary key and also when interacting with QGIS which needs a primary key to work properly for both tables and views.
I also think it would be good to display the primary key as text above the field list and that way it could highlight when a primary key is not detected.
If people think it is a good suggestion then I will look into it more and perhaps try to have a go at mocking something up myself (although I’ve still struggled to find the time to get going with development). Of course I’ll also do a proper feature request but I thought I’d email out first to see how bad people think the idea is?
Thanks,
|
Paul Wittle
|
![]()
|
|
Business Solutions Analyst (GIS)
|
|
ICT Operations
|
|
Dorset Council
|
|
01305 228473
|
|
dorsetcouncil.gov.uk
|
This e-mail and any files transmitted with it are intended solely for the use of the individual or entity to whom they are addressed. It may contain unclassified but sensitive or protectively marked material and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All traffic may be subject to recording and/or monitoring in accordance with relevant legislation. Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of Dorset Council. Dorset Council does not accept service of documents by fax or other electronic means. Virus checking: Whilst all reasonable steps have been taken to ensure that this electronic communication and its attachments whether encoded, encrypted or otherwise supplied are free from computer viruses, Dorset Council accepts no liability in respect of any loss, cost, damage or expense suffered as a result of accessing this message or any of its attachments. For information on how Dorset Council processes your information, please see www.dorsetcouncil.gov.uk/416433
