Arctos: long attribues can't be column headers

[dustymc]

Oracle has a 30-character limit on table column names. We allow long attribute types and then they break everything

UAM@ARCTOS> select ATTRIBUTE_TYPE ,length(ATTRIBUTE_TYPE) from ctattribute_type where length(ATTRIBUTE_TYPE) > 30;

ATTRIBUTE_TYPE
------------------------------------------------------------------------------------------------------------------------
LENGTH(ATTRIBUTE_TYPE)
----------------------
thin-layer chromatography compound
            34


1 row selected.

Three options:

1) Ignore long things. I just removed it from field-level docs so it's not available as a search term and can't be included in downloads.

2) change datatype to 30 characters, find a shorter name

3) do something like

UAM@ARCTOS> select substr(regexp_replace(ATTRIBUTE_TYPE,'[^A-Za-z]','_'),0,30) from ctattribute_type where length(ATTRIBUTE_TYPE) > 30;

SUBSTR(REGEXP_REPLACE(ATTRIBUTE_TYPE,'[^A-ZA-Z]','_'),0,30)
------------------------------------------------------------------------------------------------------------------------
thin_layer_chromatography_comp

1 row selected.

except that has the potential to be non-unique, so..

UAM@ARCTOS> select substr(regexp_replace(ATTRIBUTE_TYPE,'[^A-Za-z]','_'),0,24) || '_' ||  dbms_random.string('L', 5) from ctattribute_type where length(ATTRIBUTE_TYPE) > 30;

SUBSTR(REGEXP_REPLACE(ATTRIBUTE_TYPE,'[^A-ZA-Z]','_'),0,24)||'_'||DBMS_RANDOM.STRING('L',5)
------------------------------------------------------------------------------------------------------------------------
thin_layer_chromatograph_vxtdo

is probably better - and slightly more confusing.

[Jegelewicz]

change datatype to 30 characters, find a shorter name

I think this makes the most sense.

Why is "compound" included in the name? I don't know anything about this process, but a quick Google brings up just "thin layer chromatography". Can we change the attribute type to just that?

[dustymc]

UAM@ARCTOS> select ATTRIBUTE_VALUE from attributes where ATTRIBUTE_TYPE='thin-layer chromatography compound';

ATTRIBUTE_VALUE
------------------------------------------------------------------------------------------------------------------------
atranorin
lecanoric
atranorin
caperatic acid?
fumarprotocetraric acid and unknown
protocetraric

6 rows selected.

Identified compound present in a given mixture, using the thin-layer chromatography technique

[Jegelewicz]

Oh, I get it now.

How about using the Acronym "TLC Compund" with TLC explained in the definition?

[campmlc]

I think it would be best to keep "chromatography" as part of the name. What
about "chromatography compound", with "thin layer" in the definition or
required to be added to remarks?

On Wed, Oct 10, 2018 at 10:47 AM Teresa Mayfield-Meyer <
[email protected]> wrote:

Oh, I get it now.

How about using the Acronym "TLC Compund" with TLC explained in the
definition?

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/ArctosDB/arctos/issues/1730#issuecomment-428646037,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AOH0hDh8brkVHS_0unxoOnxfCU6nPd2eks5ujiSSgaJpZM4XVfma
.

[Jegelewicz]

or TL chromatography compound?

[campmlc]

"thin layer" could also go under "method". Any way that we can link
"method" to the attribute name so that if a particular attribute type is
selected, method becomes required and the user must select from a dropdown
of options?

On Wed, Oct 10, 2018 at 10:54 AM Teresa Mayfield-Meyer <
[email protected]> wrote:

or TL chromatography compound?

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/ArctosDB/arctos/issues/1730#issuecomment-428648871,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AOH0hC1u_rEfsuGResRu9ysCb_Mv-Vesks5ujiZVgaJpZM4XVfma
.

[dustymc]

Are there non-thin-layer chromatography techniques?

"thin layer" (or better yet https://dx.doi.org/10.1021/ed040p31) and whatever else might lead to these data seem like reasonable methods.

I don't think we can or should require method - the data say something vague and it's all you've got, or you have some publication describing very specific {whatevers} instead of a vague category, or ...

I think I like "chromatography compound" but I'm way out of my depth on this.

[campmlc]

Which collections are using this attribute?

On Wed, Oct 10, 2018 at 11:06 AM dustymc [e-mail] wrote:

Are there non-thin-layer chromatography techniques?

"thin layer" (or better yet https://dx.doi.org/10.1021/ed040p31) and
whatever else might lead to these data seem like reasonable methods.

I don't think we can or should require method - the data say something
vague and it's all you've got, or you have some publication describing very
specific {whatevers} instead of a vague category, or ...

I think I like "chromatography compound" but I'm way out of my depth on
this.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/ArctosDB/arctos/issues/1730#issuecomment-428652761,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AOH0hDn1plWWuQsvnM2UugYbVUrTVIfYks5ujikhgaJpZM4XVfma
.

[dustymc]

select
  guid_prefix,
  count(*) c
from
  collection,
  cataloged_item,
  attributes
where
  collection.collection_id=cataloged_item.collection_id and
  cataloged_item.collection_object_id=attributes.collection_object_id and
  attributes.attribute_type='thin-layer chromatography compound'
group by
  guid_prefix
order by
  count(*) desc
 16  ;

GUID_PREFIX                               C
------------------------------------------------------------ ----------
UTEP:Herb                                 8

1 row selected.

@Jegelewicz @mvzhuang

[Jegelewicz]

I vote for "chromatography compound" with thin layer in the definition, but I will defer to @mvzhuang if she wants something different.

[dustymc]

Can we finalize this?

And this is apparently no longer a limitation in the latest Oracle, so marking infrastructure limited.

[Jegelewicz]

Can we finalize this?

Yes

[mvzhuang]

fine with me

On Wed, Nov 7, 2018 at 10:10 AM Teresa Mayfield-Meyer <
[email protected]> wrote:

Can we finalize this?

Yes

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/ArctosDB/arctos/issues/1730#issuecomment-436701361,
or mute the thread
https://github.com/notifications/unsubscribe-auth/Ak6duQNA6veQk2OucESDfGJv3BIqMqTQks5usxQBgaJpZM4XVfma
.

--

Vicky (Mingna) Zhuang Ph. D.

UTEP Biodiversity Collections Manager

B209 Biology Building

Biological Sciences

University of Texas El Paso

500 University Ave

El Paso, TX, 79912

Phone: [phone]

Instagram: @utepbc twitter: @utepbc Facebook: Biodiversity Collections

[dustymc]

done