As currently designed, CollectionSpace does not check identifier fields for uniqueness. In other words, it is possible to have two object records with the same identification number. There are two tickets in the system now related to this: CSPACE-3821 Create validator that enforces uniqueness of field contents - CollectionSpace https://issues.collectionspace.org/browse/CSPACE-3821 <https://issues.collectionspace.org/browse/CSPACE-3821> CSPACE-1585 Warning message when adding a duplicate identification number - CollectionSpace https://issues.collectionspace.org/browse/CSPACE-1585 <https://issues.collectionspace.org/browse/CSPACE-1585> Has anyone worked on code for either of these two issues? Any speculation on how difficult they would be to implement? Peter -- Peter Murray Cherry Hill Company

- CSPACE-3821 Create validator that enforces uniqueness of field
contents - CollectionSpace
https://issues.collectionspace.org/browse/CSPACE-3821
- CSPACE-1585 Warning message when adding a duplicate identification
number - CollectionSpace
https://issues.collectionspace.org/browse/CSPACE-1585

I can't speak to whether someone's done this, but at least on first reflection, validating uniqueness of identification numbers could be accomplished by customizing this method, to perform a query on the relevant ID field, right after the existing validation check for non-null values: https://github.com/collectionspace/services/blob/98c282a1f87bee659b77ad5884ac38a09810d506/services/collectionobject/service/src/main/java/org/collectionspace/services/collectionobject/nuxeo/CollectionObjectValidatorHandler.java#L131 You might be able to add a Nuxeo query that would retrieve all CollectionObjects records whose objectNumber field, in their collectionobjects_common schema, contained a value matching the supplied objectNumber. If the query's list of results contained one or more items, implying that the supplied number is a duplicate, you could invoke an error assertion similar to the one on line 135. There are wrapper methods in the CollectionSpace Services layer for performing such queries. Many of those can be found here: https://github.com/collectionspace/services/blob/master/services/common/src/main/java/org/collectionspace/services/nuxeo/client/java/RepositoryJavaClientImpl.java The query itself might look generically something like the following in NXQL, Nuxeo's SQL-like query language: "SELECT * FROM CollectionObject WHERE collectionobjects_common:objectNumber = ' ...' But it'd likely need some additional filtering to further constrain the scope of the search and some other setup. There are various convenience methods scattered about - in services framework code, and in examples elsewhere using that code - which you'd want to use for this setup - both for constructing the WHERE clause and the other parts of the query. If no one else has already done this (and can thus share their own example), and if you're interested in pursuing this, we can point you to something more concrete/specific. Aron On Mon, Aug 10, 2015 at 5:28 PM, Peter Murray <pmurray@chillco.com> wrote: > As currently designed, CollectionSpace does not check identifier fields > for uniqueness. In other words, it is possible to have two object records > with the same identification number. There are two tickets in the system > now related to this: > > > - CSPACE-3821 Create validator that enforces uniqueness of field > contents - CollectionSpace > https://issues.collectionspace.org/browse/CSPACE-3821 > - CSPACE-1585 Warning message when adding a duplicate identification > number - CollectionSpace > https://issues.collectionspace.org/browse/CSPACE-1585 > > > Has anyone worked on code for either of these two issues? Any speculation > on how difficult they would be to implement? > > > Peter > -- > Peter Murray > Cherry Hill Company > > _______________________________________________ > Talk mailing list > Talk@lists.collectionspace.org > > http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org > >

- CSPACE-3821 Create validator that enforces uniqueness of field
contents - CollectionSpace
https://issues.collectionspace.org/browse/CSPACE-3821
- CSPACE-1585 Warning message when adding a duplicate identification
number - CollectionSpace
https://issues.collectionspace.org/browse/CSPACE-1585

Just remembered: the method described here (customizing a ValidatorHandler in the Services layer) can validate uniqueness of field values for Cataloging records created or updated a) via CollectionSpace's user interface, and b) by code invoking the Services REST API. Imports performed using CollectionSpace's Import Service, however, talk directly to Nuxeo and bypass the CollectionSpace Services layer (and thus the latter's validation). (That's done for performance reasons.) So when doing bulk imports, you'd need to verify uniqueness of the set of object identifiers in your data in some other way, perhaps by looking for matches via SQL queries made directly to the 'collectionobjects_common' table, or else via advanced search queries using the Services REST API. On Mon, Aug 10, 2015 at 6:30 PM, Aron Roberts <aron@socrates.berkeley.edu> wrote: > I can't speak to whether someone's done this, but at least on first > reflection, validating uniqueness of identification numbers could be > accomplished by customizing this method, to perform a query on the relevant > ID field, right after the existing validation check for non-null values: > > > https://github.com/collectionspace/services/blob/98c282a1f87bee659b77ad5884ac38a09810d506/services/collectionobject/service/src/main/java/org/collectionspace/services/collectionobject/nuxeo/CollectionObjectValidatorHandler.java#L131 > > You might be able to add a Nuxeo query that would retrieve all > CollectionObjects records whose objectNumber field, in their > collectionobjects_common schema, contained a value matching the supplied > objectNumber. If the query's list of results contained one or more items, > implying that the supplied number is a duplicate, you could invoke an error > assertion similar to the one on line 135. > > There are wrapper methods in the CollectionSpace Services layer for > performing such queries. Many of those can be found here: > > > https://github.com/collectionspace/services/blob/master/services/common/src/main/java/org/collectionspace/services/nuxeo/client/java/RepositoryJavaClientImpl.java > > The query itself might look generically something like the following in > NXQL, Nuxeo's SQL-like query language: > > "SELECT * FROM CollectionObject WHERE > collectionobjects_common:objectNumber = ' ...' > > But it'd likely need some additional filtering to further constrain the > scope of the search and some other setup. There are various convenience > methods scattered about - in services framework code, and in examples > elsewhere using that code - which you'd want to use for this setup - both > for constructing the WHERE clause and the other parts of the query. > > If no one else has already done this (and can thus share their own > example), and if you're interested in pursuing this, we can point you to > something more concrete/specific. > > Aron > > On Mon, Aug 10, 2015 at 5:28 PM, Peter Murray <pmurray@chillco.com> wrote: > >> As currently designed, CollectionSpace does not check identifier fields >> for uniqueness. In other words, it is possible to have two object records >> with the same identification number. There are two tickets in the system >> now related to this: >> >> >> - CSPACE-3821 Create validator that enforces uniqueness of field >> contents - CollectionSpace >> https://issues.collectionspace.org/browse/CSPACE-3821 >> - CSPACE-1585 Warning message when adding a duplicate identification >> number - CollectionSpace >> https://issues.collectionspace.org/browse/CSPACE-1585 >> >> >> Has anyone worked on code for either of these two issues? Any >> speculation on how difficult they would be to implement? >> >> >> Peter >> -- >> Peter Murray >> Cherry Hill Company >> >> _______________________________________________ >> Talk mailing list >> Talk@lists.collectionspace.org >> >> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >> >> >

- CSPACE-3821 Create validator that enforces uniqueness of field
contents - CollectionSpace
https://issues.collectionspace.org/browse/CSPACE-3821
- CSPACE-1585 Warning message when adding a duplicate identification
number - CollectionSpace
https://issues.collectionspace.org/browse/CSPACE-1585

The potentially hard part of this is that in order to be correct, the validation and the save must be atomic. I don't know if they currently are. If not, maybe there's an appropriate higher-level method that can be made synchronized? If you don't expect much concurrent record creation, and/or duplicate object numbers are not the end of the world, it may be ok not to have. Ray On Mon, Aug 10, 2015 at 7:05 PM, Aron Roberts <aron@socrates.berkeley.edu> wrote: > Just remembered: the method described here (customizing a > ValidatorHandler in the Services layer) can validate uniqueness of field > values for Cataloging records created or updated a) via CollectionSpace's > user interface, and b) by code invoking the Services REST API. > > Imports performed using CollectionSpace's Import Service, however, talk > directly to Nuxeo and bypass the CollectionSpace Services layer (and thus > the latter's validation). (That's done for performance reasons.) So when > doing bulk imports, you'd need to verify uniqueness of the set of object > identifiers in your data in some other way, perhaps by looking for matches > via SQL queries made directly to the 'collectionobjects_common' table, or > else via advanced search queries using the Services REST API. > > On Mon, Aug 10, 2015 at 6:30 PM, Aron Roberts <aron@socrates.berkeley.edu> > wrote: > >> I can't speak to whether someone's done this, but at least on first >> reflection, validating uniqueness of identification numbers could be >> accomplished by customizing this method, to perform a query on the relevant >> ID field, right after the existing validation check for non-null values: >> >> >> https://github.com/collectionspace/services/blob/98c282a1f87bee659b77ad5884ac38a09810d506/services/collectionobject/service/src/main/java/org/collectionspace/services/collectionobject/nuxeo/CollectionObjectValidatorHandler.java#L131 >> >> You might be able to add a Nuxeo query that would retrieve all >> CollectionObjects records whose objectNumber field, in their >> collectionobjects_common schema, contained a value matching the supplied >> objectNumber. If the query's list of results contained one or more items, >> implying that the supplied number is a duplicate, you could invoke an error >> assertion similar to the one on line 135. >> >> There are wrapper methods in the CollectionSpace Services layer for >> performing such queries. Many of those can be found here: >> >> >> https://github.com/collectionspace/services/blob/master/services/common/src/main/java/org/collectionspace/services/nuxeo/client/java/RepositoryJavaClientImpl.java >> >> The query itself might look generically something like the following in >> NXQL, Nuxeo's SQL-like query language: >> >> "SELECT * FROM CollectionObject WHERE >> collectionobjects_common:objectNumber = ' ...' >> >> But it'd likely need some additional filtering to further constrain the >> scope of the search and some other setup. There are various convenience >> methods scattered about - in services framework code, and in examples >> elsewhere using that code - which you'd want to use for this setup - both >> for constructing the WHERE clause and the other parts of the query. >> >> If no one else has already done this (and can thus share their own >> example), and if you're interested in pursuing this, we can point you to >> something more concrete/specific. >> >> Aron >> >> On Mon, Aug 10, 2015 at 5:28 PM, Peter Murray <pmurray@chillco.com> >> wrote: >> >>> As currently designed, CollectionSpace does not check identifier fields >>> for uniqueness. In other words, it is possible to have two object records >>> with the same identification number. There are two tickets in the system >>> now related to this: >>> >>> >>> - CSPACE-3821 Create validator that enforces uniqueness of field >>> contents - CollectionSpace >>> https://issues.collectionspace.org/browse/CSPACE-3821 >>> - CSPACE-1585 Warning message when adding a duplicate identification >>> number - CollectionSpace >>> https://issues.collectionspace.org/browse/CSPACE-1585 >>> >>> >>> Has anyone worked on code for either of these two issues? Any >>> speculation on how difficult they would be to implement? >>> >>> >>> Peter >>> -- >>> Peter Murray >>> Cherry Hill Company >>> >>> _______________________________________________ >>> Talk mailing list >>> Talk@lists.collectionspace.org >>> >>> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >>> >>> >> > > _______________________________________________ > Talk mailing list > Talk@lists.collectionspace.org > > http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org > >

Thanks (again!) Aron and Ray. Ray, your comment about concurrency triggered a thought of another race condition. If application-generated number patterns are used, there is a big amount of time between when that number is returned from the IdGenerator service (https://github.com/collectionspace/application/blob/master/cspi-file/src/main/java/org/collectionspace/chain/csp/persistence/file/IdGenerator.java#L115 <https://github.com/collectionspace/application/blob/master/cspi-file/src/main/java/org/collectionspace/chain/csp/persistence/file/IdGenerator.java#L115> if I'm not mistaken) to when the record is persisted in the database. At least in a quick view of the code I don't see where a stub record is saved in Nuxeo with the generated identifier. Am I missing something? Peter > On Aug 10, 2015, at 11:40 PM, Ray Lee <rhlee@berkeley.edu> wrote: > > The potentially hard part of this is that in order to be correct, the validation and the save must be atomic. I don't know if they currently are. If not, maybe there's an appropriate higher-level method that can be made synchronized? If you don't expect much concurrent record creation, and/or duplicate object numbers are not the end of the world, it may be ok not to have. > > Ray > > On Mon, Aug 10, 2015 at 7:05 PM, Aron Roberts <aron@socrates.berkeley.edu <mailto:aron@socrates.berkeley.edu>> wrote: > Just remembered: the method described here (customizing a ValidatorHandler in the Services layer) can validate uniqueness of field values for Cataloging records created or updated a) via CollectionSpace's user interface, and b) by code invoking the Services REST API. > > Imports performed using CollectionSpace's Import Service, however, talk directly to Nuxeo and bypass the CollectionSpace Services layer (and thus the latter's validation). (That's done for performance reasons.) So when doing bulk imports, you'd need to verify uniqueness of the set of object identifiers in your data in some other way, perhaps by looking for matches via SQL queries made directly to the 'collectionobjects_common' table, or else via advanced search queries using the Services REST API. > > On Mon, Aug 10, 2015 at 6:30 PM, Aron Roberts <aron@socrates.berkeley.edu <mailto:aron@socrates.berkeley.edu>> wrote: > I can't speak to whether someone's done this, but at least on first reflection, validating uniqueness of identification numbers could be accomplished by customizing this method, to perform a query on the relevant ID field, right after the existing validation check for non-null values: > > https://github.com/collectionspace/services/blob/98c282a1f87bee659b77ad5884ac38a09810d506/services/collectionobject/service/src/main/java/org/collectionspace/services/collectionobject/nuxeo/CollectionObjectValidatorHandler.java#L131 <https://github.com/collectionspace/services/blob/98c282a1f87bee659b77ad5884ac38a09810d506/services/collectionobject/service/src/main/java/org/collectionspace/services/collectionobject/nuxeo/CollectionObjectValidatorHandler.java#L131> > > You might be able to add a Nuxeo query that would retrieve all CollectionObjects records whose objectNumber field, in their collectionobjects_common schema, contained a value matching the supplied objectNumber. If the query's list of results contained one or more items, implying that the supplied number is a duplicate, you could invoke an error assertion similar to the one on line 135. > > There are wrapper methods in the CollectionSpace Services layer for performing such queries. Many of those can be found here: > > https://github.com/collectionspace/services/blob/master/services/common/src/main/java/org/collectionspace/services/nuxeo/client/java/RepositoryJavaClientImpl.java <https://github.com/collectionspace/services/blob/master/services/common/src/main/java/org/collectionspace/services/nuxeo/client/java/RepositoryJavaClientImpl.java> > > The query itself might look generically something like the following in NXQL, Nuxeo's SQL-like query language: > > "SELECT * FROM CollectionObject WHERE collectionobjects_common:objectNumber = ' ...' > > But it'd likely need some additional filtering to further constrain the scope of the search and some other setup. There are various convenience methods scattered about - in services framework code, and in examples elsewhere using that code - which you'd want to use for this setup - both for constructing the WHERE clause and the other parts of the query. > > If no one else has already done this (and can thus share their own example), and if you're interested in pursuing this, we can point you to something more concrete/specific. > > Aron > > On Mon, Aug 10, 2015 at 5:28 PM, Peter Murray <pmurray@chillco.com <mailto:pmurray@chillco.com>> wrote: > As currently designed, CollectionSpace does not check identifier fields for uniqueness. In other words, it is possible to have two object records with the same identification number. There are two tickets in the system now related to this: > > CSPACE-3821 Create validator that enforces uniqueness of field contents - CollectionSpace https://issues.collectionspace.org/browse/CSPACE-3821 <https://issues.collectionspace.org/browse/CSPACE-3821> > CSPACE-1585 Warning message when adding a duplicate identification number - CollectionSpace https://issues.collectionspace.org/browse/CSPACE-1585 <https://issues.collectionspace.org/browse/CSPACE-1585> > > Has anyone worked on code for either of these two issues? Any speculation on how difficult they would be to implement? > > > Peter -- Peter Murray Dev/Ops Lead and Project Manager Cherry Hill Company

- CSPACE-3821 Create validator that enforces uniqueness of field
contents - CollectionSpace
https://issues.collectionspace.org/browse/CSPACE-3821
- CSPACE-1585 Warning message when adding a duplicate
identification number - CollectionSpace
https://issues.collectionspace.org/browse/CSPACE-1585

Peter, Few CSpace operations (initiated from the UI) are atomic in the sense Ray intends. The "concurrency problem" is ubiquitous, though we seem to have rarely run into it in practice. See e.g. https://issues.collectionspace.org/browse/PAHMA-920 John On Tue, Aug 11, 2015 at 8:11 AM, Peter Murray <pmurray@chillco.com> wrote: > Thanks (again!) Aron and Ray. Ray, your comment about concurrency > triggered a thought of another race condition. If application-generated > number patterns are used, there is a big amount of time between when that > number is returned from the IdGenerator service ( > https://github.com/collectionspace/application/blob/master/cspi-file/src/main/java/org/collectionspace/chain/csp/persistence/file/IdGenerator.java#L115 if > I'm not mistaken) to when the record is persisted in the database. At > least in a quick view of the code I don't see where a stub record is saved > in Nuxeo with the generated identifier. > > Am I missing something? > > > Peter > > > On Aug 10, 2015, at 11:40 PM, Ray Lee <rhlee@berkeley.edu> wrote: > > The potentially hard part of this is that in order to be correct, the > validation and the save must be atomic. I don't know if they currently are. > If not, maybe there's an appropriate higher-level method that can be made > synchronized? If you don't expect much concurrent record creation, and/or > duplicate object numbers are not the end of the world, it may be ok not to > have. > > Ray > > On Mon, Aug 10, 2015 at 7:05 PM, Aron Roberts <aron@socrates.berkeley.edu> > wrote: > >> Just remembered: the method described here (customizing a >> ValidatorHandler in the Services layer) can validate uniqueness of field >> values for Cataloging records created or updated a) via CollectionSpace's >> user interface, and b) by code invoking the Services REST API. >> >> Imports performed using CollectionSpace's Import Service, however, talk >> directly to Nuxeo and bypass the CollectionSpace Services layer (and thus >> the latter's validation). (That's done for performance reasons.) So when >> doing bulk imports, you'd need to verify uniqueness of the set of object >> identifiers in your data in some other way, perhaps by looking for matches >> via SQL queries made directly to the 'collectionobjects_common' table, or >> else via advanced search queries using the Services REST API. >> >> On Mon, Aug 10, 2015 at 6:30 PM, Aron Roberts <aron@socrates.berkeley.edu >> > wrote: >> >>> I can't speak to whether someone's done this, but at least on first >>> reflection, validating uniqueness of identification numbers could be >>> accomplished by customizing this method, to perform a query on the relevant >>> ID field, right after the existing validation check for non-null values: >>> >>> >>> https://github.com/collectionspace/services/blob/98c282a1f87bee659b77ad5884ac38a09810d506/services/collectionobject/service/src/main/java/org/collectionspace/services/collectionobject/nuxeo/CollectionObjectValidatorHandler.java#L131 >>> >>> You might be able to add a Nuxeo query that would retrieve all >>> CollectionObjects records whose objectNumber field, in their >>> collectionobjects_common schema, contained a value matching the supplied >>> objectNumber. If the query's list of results contained one or more items, >>> implying that the supplied number is a duplicate, you could invoke an error >>> assertion similar to the one on line 135. >>> >>> There are wrapper methods in the CollectionSpace Services layer for >>> performing such queries. Many of those can be found here: >>> >>> >>> https://github.com/collectionspace/services/blob/master/services/common/src/main/java/org/collectionspace/services/nuxeo/client/java/RepositoryJavaClientImpl.java >>> >>> The query itself might look generically something like the following >>> in NXQL, Nuxeo's SQL-like query language: >>> >>> "SELECT * FROM CollectionObject WHERE >>> collectionobjects_common:objectNumber = ' ...' >>> >>> But it'd likely need some additional filtering to further constrain >>> the scope of the search and some other setup. There are various convenience >>> methods scattered about - in services framework code, and in examples >>> elsewhere using that code - which you'd want to use for this setup - both >>> for constructing the WHERE clause and the other parts of the query. >>> >>> If no one else has already done this (and can thus share their own >>> example), and if you're interested in pursuing this, we can point you to >>> something more concrete/specific. >>> >>> Aron >>> >>> On Mon, Aug 10, 2015 at 5:28 PM, Peter Murray <pmurray@chillco.com> >>> wrote: >>> >>>> As currently designed, CollectionSpace does not check identifier fields >>>> for uniqueness. In other words, it is possible to have two object records >>>> with the same identification number. There are two tickets in the system >>>> now related to this: >>>> >>>> >>>> - CSPACE-3821 Create validator that enforces uniqueness of field >>>> contents - CollectionSpace >>>> https://issues.collectionspace.org/browse/CSPACE-3821 >>>> - CSPACE-1585 Warning message when adding a duplicate >>>> identification number - CollectionSpace >>>> https://issues.collectionspace.org/browse/CSPACE-1585 >>>> >>>> >>>> Has anyone worked on code for either of these two issues? Any >>>> speculation on how difficult they would be to implement? >>>> >>>> >>>> Peter >>>> >>> > > -- > Peter Murray > Dev/Ops Lead and Project Manager > Cherry Hill Company > > > > _______________________________________________ > Talk mailing list > Talk@lists.collectionspace.org > > http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org > >

Peter, If uniqueness for object numbers (or any fields) is a hard requirement for you, I suppose these might be some options to consider and try: 1. if you're starting with a fresh (empty) data repository, you could add field constraint(s) to the corresponding database column(s) 2. if you already have data with non-unique rows, you could create a new index with a uniqueness constraint on that column. The unknown here is how the current CollectionSpace code would handle a uniqueness violation. Ideally, it would handle it gracefully. If it didn't, we'd need to create a JIRA and fix it. -Richard ________________________________ From: Talk <talk-bounces@lists.collectionspace.org> on behalf of John B. LOWE <jblowe@berkeley.edu> Sent: Tuesday, August 11, 2015 9:05 AM To: Peter Murray Cc: CollectionSpace Talk List Subject: Re: [Talk] Anyone working on unique value detection for identifier fields? Peter, Few CSpace operations (initiated from the UI) are atomic in the sense Ray intends. The "concurrency problem" is ubiquitous, though we seem to have rarely run into it in practice. See e.g. https://issues.collectionspace.org/browse/PAHMA-920 John On Tue, Aug 11, 2015 at 8:11 AM, Peter Murray <pmurray@chillco.com<mailto:pmurray@chillco.com>> wrote: Thanks (again!) Aron and Ray. Ray, your comment about concurrency triggered a thought of another race condition. If application-generated number patterns are used, there is a big amount of time between when that number is returned from the IdGenerator service (https://github.com/collectionspace/application/blob/master/cspi-file/src/main/java/org/collectionspace/chain/csp/persistence/file/IdGenerator.java#L115 if I'm not mistaken) to when the record is persisted in the database. At least in a quick view of the code I don't see where a stub record is saved in Nuxeo with the generated identifier. Am I missing something? Peter On Aug 10, 2015, at 11:40 PM, Ray Lee <rhlee@berkeley.edu<mailto:rhlee@berkeley.edu>> wrote: The potentially hard part of this is that in order to be correct, the validation and the save must be atomic. I don't know if they currently are. If not, maybe there's an appropriate higher-level method that can be made synchronized? If you don't expect much concurrent record creation, and/or duplicate object numbers are not the end of the world, it may be ok not to have. Ray On Mon, Aug 10, 2015 at 7:05 PM, Aron Roberts <aron@socrates.berkeley.edu<mailto:aron@socrates.berkeley.edu>> wrote: Just remembered: the method described here (customizing a ValidatorHandler in the Services layer) can validate uniqueness of field values for Cataloging records created or updated a) via CollectionSpace's user interface, and b) by code invoking the Services REST API. Imports performed using CollectionSpace's Import Service, however, talk directly to Nuxeo and bypass the CollectionSpace Services layer (and thus the latter's validation). (That's done for performance reasons.) So when doing bulk imports, you'd need to verify uniqueness of the set of object identifiers in your data in some other way, perhaps by looking for matches via SQL queries made directly to the 'collectionobjects_common' table, or else via advanced search queries using the Services REST API. On Mon, Aug 10, 2015 at 6:30 PM, Aron Roberts <aron@socrates.berkeley.edu<mailto:aron@socrates.berkeley.edu>> wrote: I can't speak to whether someone's done this, but at least on first reflection, validating uniqueness of identification numbers could be accomplished by customizing this method, to perform a query on the relevant ID field, right after the existing validation check for non-null values: https://github.com/collectionspace/services/blob/98c282a1f87bee659b77ad5884ac38a09810d506/services/collectionobject/service/src/main/java/org/collectionspace/services/collectionobject/nuxeo/CollectionObjectValidatorHandler.java#L131 You might be able to add a Nuxeo query that would retrieve all CollectionObjects records whose objectNumber field, in their collectionobjects_common schema, contained a value matching the supplied objectNumber. If the query's list of results contained one or more items, implying that the supplied number is a duplicate, you could invoke an error assertion similar to the one on line 135. There are wrapper methods in the CollectionSpace Services layer for performing such queries. Many of those can be found here: https://github.com/collectionspace/services/blob/master/services/common/src/main/java/org/collectionspace/services/nuxeo/client/java/RepositoryJavaClientImpl.java The query itself might look generically something like the following in NXQL, Nuxeo's SQL-like query language: "SELECT * FROM CollectionObject WHERE collectionobjects_common:objectNumber = ' ...' But it'd likely need some additional filtering to further constrain the scope of the search and some other setup. There are various convenience methods scattered about - in services framework code, and in examples elsewhere using that code - which you'd want to use for this setup - both for constructing the WHERE clause and the other parts of the query. If no one else has already done this (and can thus share their own example), and if you're interested in pursuing this, we can point you to something more concrete/specific. Aron On Mon, Aug 10, 2015 at 5:28 PM, Peter Murray <pmurray@chillco.com<mailto:pmurray@chillco.com>> wrote: As currently designed, CollectionSpace does not check identifier fields for uniqueness. In other words, it is possible to have two object records with the same identification number. There are two tickets in the system now related to this: * CSPACE-3821 Create validator that enforces uniqueness of field contents - CollectionSpace https://issues.collectionspace.org/browse/CSPACE-3821 * CSPACE-1585 Warning message when adding a duplicate identification number - CollectionSpace https://issues.collectionspace.org/browse/CSPACE-1585 Has anyone worked on code for either of these two issues? Any speculation on how difficult they would be to implement? Peter -- Peter Murray Dev/Ops Lead and Project Manager Cherry Hill Company _______________________________________________ Talk mailing list Talk@lists.collectionspace.org<mailto:Talk@lists.collectionspace.org> http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org

That is a good idea, Richard. I'll try that path and see how the exception is handled. Peter > On Aug 11, 2015, at 12:57 PM, Richard Millet <richard.millet@lyrasis.org> wrote: > > Peter, > > If uniqueness for object numbers (or any fields) is a hard requirement for you, I suppose these might be some options to consider and try: > > 1. if you're starting with a fresh (empty) data repository, you could add field constraint(s) to the corresponding database column(s) > 2. if you already have data with non-unique rows, you could create a new index with a uniqueness constraint on that column. > > The unknown here is how the current CollectionSpace code would handle a uniqueness violation. Ideally, it would handle it gracefully. If it didn't, we'd need to create a JIRA and fix it. > > -Richard > > > From: Talk <talk-bounces@lists.collectionspace.org> on behalf of John B. LOWE <jblowe@berkeley.edu> > Sent: Tuesday, August 11, 2015 9:05 AM > To: Peter Murray > Cc: CollectionSpace Talk List > Subject: Re: [Talk] Anyone working on unique value detection for identifier fields? > > Peter, > > Few CSpace operations (initiated from the UI) are atomic in the sense Ray intends. The "concurrency problem" is ubiquitous, though we seem to have rarely run into it in practice. See e.g. https://issues.collectionspace.org/browse/PAHMA-920 <https://issues.collectionspace.org/browse/PAHMA-920> > > John > > On Tue, Aug 11, 2015 at 8:11 AM, Peter Murray <pmurray@chillco.com <mailto:pmurray@chillco.com>> wrote: > Thanks (again!) Aron and Ray. Ray, your comment about concurrency triggered a thought of another race condition. If application-generated number patterns are used, there is a big amount of time between when that number is returned from the IdGenerator service (https://github.com/collectionspace/application/blob/master/cspi-file/src/main/java/org/collectionspace/chain/csp/persistence/file/IdGenerator.java#L115 <https://github.com/collectionspace/application/blob/master/cspi-file/src/main/java/org/collectionspace/chain/csp/persistence/file/IdGenerator.java#L115> if I'm not mistaken) to when the record is persisted in the database. At least in a quick view of the code I don't see where a stub record is saved in Nuxeo with the generated identifier. > > Am I missing something? > > > Peter > > >> On Aug 10, 2015, at 11:40 PM, Ray Lee <rhlee@berkeley.edu <mailto:rhlee@berkeley.edu>> wrote: >> >> The potentially hard part of this is that in order to be correct, the validation and the save must be atomic. I don't know if they currently are. If not, maybe there's an appropriate higher-level method that can be made synchronized? If you don't expect much concurrent record creation, and/or duplicate object numbers are not the end of the world, it may be ok not to have. >> >> Ray >> >> On Mon, Aug 10, 2015 at 7:05 PM, Aron Roberts <aron@socrates.berkeley.edu <mailto:aron@socrates.berkeley.edu>> wrote: >> Just remembered: the method described here (customizing a ValidatorHandler in the Services layer) can validate uniqueness of field values for Cataloging records created or updated a) via CollectionSpace's user interface, and b) by code invoking the Services REST API. >> >> Imports performed using CollectionSpace's Import Service, however, talk directly to Nuxeo and bypass the CollectionSpace Services layer (and thus the latter's validation). (That's done for performance reasons.) So when doing bulk imports, you'd need to verify uniqueness of the set of object identifiers in your data in some other way, perhaps by looking for matches via SQL queries made directly to the 'collectionobjects_common' table, or else via advanced search queries using the Services REST API. >> >> On Mon, Aug 10, 2015 at 6:30 PM, Aron Roberts <aron@socrates.berkeley.edu <mailto:aron@socrates.berkeley.edu>> wrote: >> I can't speak to whether someone's done this, but at least on first reflection, validating uniqueness of identification numbers could be accomplished by customizing this method, to perform a query on the relevant ID field, right after the existing validation check for non-null values: >> >> https://github.com/collectionspace/services/blob/98c282a1f87bee659b77ad5884ac38a09810d506/services/collectionobject/service/src/main/java/org/collectionspace/services/collectionobject/nuxeo/CollectionObjectValidatorHandler.java#L131 <https://github.com/collectionspace/services/blob/98c282a1f87bee659b77ad5884ac38a09810d506/services/collectionobject/service/src/main/java/org/collectionspace/services/collectionobject/nuxeo/CollectionObjectValidatorHandler.java#L131> >> >> You might be able to add a Nuxeo query that would retrieve all CollectionObjects records whose objectNumber field, in their collectionobjects_common schema, contained a value matching the supplied objectNumber. If the query's list of results contained one or more items, implying that the supplied number is a duplicate, you could invoke an error assertion similar to the one on line 135. >> >> There are wrapper methods in the CollectionSpace Services layer for performing such queries. Many of those can be found here: >> >> https://github.com/collectionspace/services/blob/master/services/common/src/main/java/org/collectionspace/services/nuxeo/client/java/RepositoryJavaClientImpl.java <https://github.com/collectionspace/services/blob/master/services/common/src/main/java/org/collectionspace/services/nuxeo/client/java/RepositoryJavaClientImpl.java> >> >> The query itself might look generically something like the following in NXQL, Nuxeo's SQL-like query language: >> >> "SELECT * FROM CollectionObject WHERE collectionobjects_common:objectNumber = ' ...' >> >> But it'd likely need some additional filtering to further constrain the scope of the search and some other setup. There are various convenience methods scattered about - in services framework code, and in examples elsewhere using that code - which you'd want to use for this setup - both for constructing the WHERE clause and the other parts of the query. >> >> If no one else has already done this (and can thus share their own example), and if you're interested in pursuing this, we can point you to something more concrete/specific. >> >> Aron >> >> On Mon, Aug 10, 2015 at 5:28 PM, Peter Murray <pmurray@chillco.com <mailto:pmurray@chillco.com>> wrote: >> As currently designed, CollectionSpace does not check identifier fields for uniqueness. In other words, it is possible to have two object records with the same identification number. There are two tickets in the system now related to this: >> >> CSPACE-3821 Create validator that enforces uniqueness of field contents - CollectionSpace https://issues.collectionspace.org/browse/CSPACE-3821 <https://issues.collectionspace.org/browse/CSPACE-3821> >> CSPACE-1585 Warning message when adding a duplicate identification number - CollectionSpace https://issues.collectionspace.org/browse/CSPACE-1585 <https://issues.collectionspace.org/browse/CSPACE-1585> >> >> Has anyone worked on code for either of these two issues? Any speculation on how difficult they would be to implement? >> >> >> Peter -- Peter Murray Dev/Ops Lead and Project Manager Cherry Hill Company

Thanks so much. When this is sorted out, I'm sure many others would use it. I like the uniqueness constraint/index in the DB method. Susan Stone On Tue, Aug 11, 2015 at 10:32 AM, Peter Murray <pmurray@chillco.com> wrote: > That is a good idea, Richard. I'll try that path and see how the exception > is handled. > > > Peter > > On Aug 11, 2015, at 12:57 PM, Richard Millet <richard.millet@lyrasis.org> > wrote: > > Peter, > > If uniqueness for object numbers (or any fields) is a hard requirement for > you, I suppose these might be some options to consider and try: > > 1. if you're starting with a fresh (empty) data repository, you could add > field constraint(s) to the corresponding database column(s) > 2. if you already have data with non-unique rows, you could create a new > index with a uniqueness constraint on that column. > > The unknown here is how the current CollectionSpace code would handle a > uniqueness violation. Ideally, it would handle it gracefully. If it > didn't, we'd need to create a JIRA and fix it. > > -Richard > > > ________________________________ > From: Talk <talk-bounces@lists.collectionspace.org> on behalf of John B. > LOWE <jblowe@berkeley.edu> > Sent: Tuesday, August 11, 2015 9:05 AM > To: Peter Murray > Cc: CollectionSpace Talk List > Subject: Re: [Talk] Anyone working on unique value detection for identifier > fields? > > Peter, > > Few CSpace operations (initiated from the UI) are atomic in the sense Ray > intends. The "concurrency problem" is ubiquitous, though we seem to have > rarely run into it in practice. See e.g. > https://issues.collectionspace.org/browse/PAHMA-920 > > John > > On Tue, Aug 11, 2015 at 8:11 AM, Peter Murray <pmurray@chillco.com> wrote: >> >> Thanks (again!) Aron and Ray. Ray, your comment about concurrency >> triggered a thought of another race condition. If application-generated >> number patterns are used, there is a big amount of time between when that >> number is returned from the IdGenerator service >> (https://github.com/collectionspace/application/blob/master/cspi-file/src/main/java/org/collectionspace/chain/csp/persistence/file/IdGenerator.java#L115 >> if I'm not mistaken) to when the record is persisted in the database. At >> least in a quick view of the code I don't see where a stub record is saved >> in Nuxeo with the generated identifier. >> >> Am I missing something? >> >> >> Peter >> >> >> On Aug 10, 2015, at 11:40 PM, Ray Lee <rhlee@berkeley.edu> wrote: >> >> The potentially hard part of this is that in order to be correct, the >> validation and the save must be atomic. I don't know if they currently are. >> If not, maybe there's an appropriate higher-level method that can be made >> synchronized? If you don't expect much concurrent record creation, and/or >> duplicate object numbers are not the end of the world, it may be ok not to >> have. >> >> Ray >> >> On Mon, Aug 10, 2015 at 7:05 PM, Aron Roberts <aron@socrates.berkeley.edu> >> wrote: >>> >>> Just remembered: the method described here (customizing a >>> ValidatorHandler in the Services layer) can validate uniqueness of field >>> values for Cataloging records created or updated a) via CollectionSpace's >>> user interface, and b) by code invoking the Services REST API. >>> >>> Imports performed using CollectionSpace's Import Service, however, talk >>> directly to Nuxeo and bypass the CollectionSpace Services layer (and thus >>> the latter's validation). (That's done for performance reasons.) So when >>> doing bulk imports, you'd need to verify uniqueness of the set of object >>> identifiers in your data in some other way, perhaps by looking for matches >>> via SQL queries made directly to the 'collectionobjects_common' table, or >>> else via advanced search queries using the Services REST API. >>> >>> On Mon, Aug 10, 2015 at 6:30 PM, Aron Roberts >>> <aron@socrates.berkeley.edu> wrote: >>>> >>>> I can't speak to whether someone's done this, but at least on first >>>> reflection, validating uniqueness of identification numbers could be >>>> accomplished by customizing this method, to perform a query on the relevant >>>> ID field, right after the existing validation check for non-null values: >>>> >>>> >>>> https://github.com/collectionspace/services/blob/98c282a1f87bee659b77ad5884ac38a09810d506/services/collectionobject/service/src/main/java/org/collectionspace/services/collectionobject/nuxeo/CollectionObjectValidatorHandler.java#L131 >>>> >>>> You might be able to add a Nuxeo query that would retrieve all >>>> CollectionObjects records whose objectNumber field, in their >>>> collectionobjects_common schema, contained a value matching the supplied >>>> objectNumber. If the query's list of results contained one or more items, >>>> implying that the supplied number is a duplicate, you could invoke an error >>>> assertion similar to the one on line 135. >>>> >>>> There are wrapper methods in the CollectionSpace Services layer for >>>> performing such queries. Many of those can be found here: >>>> >>>> >>>> https://github.com/collectionspace/services/blob/master/services/common/src/main/java/org/collectionspace/services/nuxeo/client/java/RepositoryJavaClientImpl.java >>>> >>>> The query itself might look generically something like the following >>>> in NXQL, Nuxeo's SQL-like query language: >>>> >>>> "SELECT * FROM CollectionObject WHERE >>>> collectionobjects_common:objectNumber = ' ...' >>>> >>>> But it'd likely need some additional filtering to further constrain >>>> the scope of the search and some other setup. There are various convenience >>>> methods scattered about - in services framework code, and in examples >>>> elsewhere using that code - which you'd want to use for this setup - both >>>> for constructing the WHERE clause and the other parts of the query. >>>> >>>> If no one else has already done this (and can thus share their own >>>> example), and if you're interested in pursuing this, we can point you to >>>> something more concrete/specific. >>>> >>>> Aron >>>> >>>> On Mon, Aug 10, 2015 at 5:28 PM, Peter Murray <pmurray@chillco.com> >>>> wrote: >>>>> >>>>> As currently designed, CollectionSpace does not check identifier fields >>>>> for uniqueness. In other words, it is possible to have two object records >>>>> with the same identification number. There are two tickets in the system >>>>> now related to this: >>>>> >>>>> CSPACE-3821 Create validator that enforces uniqueness of field contents >>>>> - CollectionSpace https://issues.collectionspace.org/browse/CSPACE-3821 >>>>> CSPACE-1585 Warning message when adding a duplicate identification >>>>> number - CollectionSpace >>>>> https://issues.collectionspace.org/browse/CSPACE-1585 >>>>> >>>>> >>>>> Has anyone worked on code for either of these two issues? Any >>>>> speculation on how difficult they would be to implement? >>>>> >>>>> >>>>> Peter > > > > -- > Peter Murray > Dev/Ops Lead and Project Manager > Cherry Hill Company > > > > _______________________________________________ > Talk mailing list > Talk@lists.collectionspace.org > http://lists.collectionspace.org/mailman/listinfo/talk_lists.collectionspace.org >