part cleanup

[dustymc]

Some stuff that jumped out at me while I was looking at parts:

 specimen in sections (slide)
 specimen subsample (70% ethanol)
 specimen subsample (95% ethanol)
 specimen subsample (frozen)

@campmlc you created all of those. They seem entirely redundant with existing parts - "whole organism" or "tissue" perhaps - and derived_from. Can they be somehow made more consistent, or better described, or something?

There are a fair number of "body part[s]" (I don't think we have any non-body parts....) and "body" which are probably redundant with "whole organism" or perhaps "unknown" - anything that can be done about that?

These aren't used - anyone have immediate plans for them, or can I delete?

arctosprod@arctos>> select part_name from ctspecimen_part_name where not exists (select part_name from specimen_part where specimen_part.part_name=ctspecimen_part_name.part_name) order by part_name;
               part_name               
---------------------------------------
 body (isopropanol)
 body part (dry)
 body parts (isopropanol)
 body parts (isopropanol)
 brain (lysis buffer)
 brain (RNAlater)
 buckler
 byssus
 egg sac (70% ethanol)
 egg sac (dry)
 eye (RNAlater)
 gonad (RNAlater)
 heart, kidney (lysis buffer)
 holdfast
 holdfast
 hypostome
 large intestine, feces (frozen)
 muscle, spleen
 pelvis (frozen)
 pleurocentrum
 rostellar hooks (slide)
 scolex (slide)
 skin (isopropanol)
 skin (isopropanol)
 sperm
 spermatophore
 stomach content (formalin)
 swab, oral (RNAlater)
 tail (RNAlater)
 trapezium
 whole organism (4% buffered formalin)
 whole organism (Bouin's fluid)
 whole organism (glutaraldehyde)
 whole organism (lysis buffer)
(34 rows)

These are used 10 or fewer times - can any of them be merged with more "popular" parts?

arctosprod@arctos>> select part_name, count(*) from specimen_part group by part_name having count(*) < 10 order by part_name;
                    part_name                     | count 
--------------------------------------------------+-------
 acanthocephala (frozen)                          |     1
 alisphenoid                                      |     2
 ambergris                                        |     1
 antenna                                          |     3
 arm                                              |     3
 basibranchial                                    |     1
 bill content (dry)                               |     3
 blood (Alsever's solution)                       |     3
 blood (DMSO)                                     |     7
 blood plasma                                     |     1
 blood serum, muscle (frozen)                     |     8
 body (95% ethanol)                               |     1
 body (cleared and stained)                       |     6
 body (frozen)                                    |     5
 body parts (formalin)                            |     1
 bone (frozen)                                    |     7
 brain (95% ethanol)                              |     3
 brain (ethanol)                                  |     1
 brain, eye (frozen)                              |     1
 brain (unknown)                                  |     2
 brood patch (ethanol)                            |     1
 bulla                                            |     1
 caecum (frozen)                                  |     6
 calcareous worm tube                             |     7
 carapace, sectioned                              |     1
 carapace (slide)                                 |     1
 carcass (formalin)                               |     2
 carcass (mummified)                              |     1
 cephalic clasper                                 |     3
 cestode (70% ethanol, frozen)                    |     4
 cestode (alcohol)                                |     1
 cestode (formalin-fixed, 70% ethanol)            |     2
 cheek content (dry)                              |     2
 cheek pouch                                      |     2
 chela                                            |     1
 coal ball                                        |     7
 crop                                             |     3
 crop (95% ethanol)                               |     5
 crop content (70% ethanol)                       |     8
 crop (ethanol)                                   |     3
 crop (frozen)                                    |     4
 cyst (70% ethanol)                               |     3
 cyst (95% ethanol)                               |     6
 diaphragm                                        |     3
 DNA (dry)                                        |     3
 ear clip (Alsever's solution)                    |     1
 ear clip (dry)                                   |     6
 ectoparasite (formalin-fixed, 70% ethanol)       |     2
 ectoparasite (K2Cr2O7)                           |     2
 egg (95% ethanol)                                |     1
 egg (frozen)                                     |     7
 embryo (dry)                                     |     5
 embryo (ethanol-fixed)                           |     5
 embryo (ethanol/glycerin)                        |     2
 embryo (formalin)                                |     4
 embryo (isopropanol, formalin-fixed)             |     1
 endoparasite (ethanol-fixed)                     |     6
 exuviae (70% ethanol)                            |     3
 eye (ethanol)                                    |     2
 feces (70% ethanol)                              |     6
 feces (95% ethanol)                              |     5
 femur, sectioned                                 |     2
 fetus (cleared and stained)                      |     2
 fetus (frozen)                                   |     6
 fetus (isopropanol, formalin-fixed)              |     1
 foot, front                                      |     2
 foot, hind                                       |     3
 gastrointestinal tract (95% ethanol)             |     3
 gastrointestinal tract (ethanol-fixed)           |     4
 gastrolith                                       |     8
 glans penis (70% ethanol)                        |     3
 glans penis (95% ethanol)                        |     3
 gonad (5% buffered formalin)                     |     2
 gonad (ethanol-fixed)                            |     1
 gonad (formalin)                                 |     2
 head (formalin)                                  |     1
 head (frozen)                                    |     1
 heart, kidney, liver                             |     7
 heart, kidney, liver, lung (frozen)              |     8
 heart, kidney, liver, lung, spleen               |     9
 heart, kidney, liver, lung, spleen (95% ethanol) |     4
 heart, kidney, liver, lung, spleen (ethanol)     |     3
 heart, kidney, liver, spleen                     |     4
 heart, kidney, lung (95% ethanol)                |     3
 heart, kidney, lung (ethanol)                    |     1
 heart, kidney, lung (ethanol-fixed)              |     1
 heart, kidney, lung (ethanol/glycerin)           |     1
 heart, kidney, spleen                            |     4
 heart, kidney, spleen (dry)                      |     1
 heart, liver (lysis buffer)                      |     2
 heart, liver, muscle (alcohol)                   |     1
 heart, liver, muscle (lysis buffer)              |     1
 heart, lung                                      |     4
 heart, lung (ethanol-fixed)                      |     4
 hindgut content (70% ethanol)                    |     5
 hindlimb (dry)                                   |     2
 hindlimb (frozen)                                |     2
 hindquarters (latex vascular injection)          |     3
 hoof wall                                        |     3
 humerus, sectioned                               |     2
 hyoid (ethanol)                                  |     1
 hyoid (frozen)                                   |     1
 hypural                                          |     4
 incus                                            |     1
 interparietal                                    |     1
 intestine (ethanol/glycerin)                     |     6
 intestine (formalin)                             |     2
 intestine (K2Cr2O7)                              |     1
 kidney (latex vascular injection)                |     2
 kidney, lung, muscle (frozen)                    |     1
 kidney (slide)                                   |     2
 large intestine (RNAlater)                       |     1
 leg                                              |     1
 leg (95% ethanol)                                |     3
 leg bone (frozen)                                |     8
 liver (DMSO)                                     |     1
 liver (isopropanol, formalin-fixed)              |     1
 lung (dry)                                       |     1
 lung (formalin)                                  |     3
 lung (isopropanol, formalin-fixed)               |     1
 lung, muscle (frozen)                            |     3
 lymph node (ethanol)                             |     1
 malar                                            |     2
 malleus                                          |     5
 mammary tissue                                   |     1
 mammary tissue (frozen)                          |     5
 mandible, upper                                  |     1
 mantle                                           |     1
 mastoid                                          |     5
 maxilla (ethanol)                                |     2
 muscle (lysis buffer)                            |     2
 needle                                           |     2
 nematode (alcohol)                               |     4
 occipital                                        |     6
 oogonium                                         |     7
 os clitoris (glycerin)                           |     6
 ovary (70% ethanol)                              |     8
 ovary (95% ethanol)                              |     1
 ovary (RNAlater)                                 |     1
 pancreas (RNAlater)                              |     1
 parasphenoid                                     |     8
 penis (ethanol)                                  |     4
 pereiopod                                        |     1
 periosteum (frozen)                              |     2
 phalange                                         |     4
 phallus (95% ethanol)                            |     4
 pinned nest                                      |     3
 pinned tissues                                   |     5
 podial (frozen)                                  |     3
 podial, hind                                     |     1
 pollen                                           |     1
 postorbitofrontal                                |     1
 preoperculum                                     |     1
 prootic                                          |     7
 pygostyle                                        |     3
 pyloric caeca (formalin-fixed, 70% ethanol)      |     2
 reproductive tract                               |     6
 reproductive tract (ethanol-fixed)               |     4
 reproductive tract (formalin)                    |     8
 rhizolith                                        |     5
 rhizome                                          |     2
 rib, caudal                                      |     6
 sclerotic plate                                  |     2
 SEM stub (ethanol)                               |     1
 serum (dry)                                      |     1
 skeletal element (dry)                           |     1
 skeleton, appendicular                           |     4
 skeleton, axial                                  |     3
 skeleton (ethanol)                               |     7
 skeleton, partial (dry)                          |     2
 skeleton, postcranial (70% ethanol)              |     1
 skeleton, postcranial (DMSO)                     |     2
 skeleton, postcranial (ethanol)                  |     8
 skeleton, postcranial (ethanol/glycerin)         |     1
 skeleton, postcranial (glycerin)                 |     3
 skeleton, postcranial, mounted                   |     2
 skin (50% isopropanol)                           |     2
 skin (dry)                                       |     5
 skin, shed (95% ethanol)                         |     1
 skull (95% ethanol)                              |     2
 skull (alcohol)                                  |     9
 skull, mounted                                   |     7
 skull (phosphate buffer)                         |     1
 small intestine (RNAlater)                       |     1
 specimen subsample (frozen)                      |     7
 sperm (ethanol)                                  |     1
 sphenethmoid                                     |     5
 sphenoid                                         |     1
 spine (frozen)                                   |     2
 spleen (DMSO/EDTA)                               |     1
 spleen (dry)                                     |     1
 spleen (formalin)                                |     2
 spleen (lysis buffer)                            |     3
 spore                                            |     5
 stalk                                            |     3
 stapes                                           |     2
 sternal                                          |     4
 stomach content (bouins fluid)                   |     1
 stomach content (ethanol-fixed)                  |     1
 stomach content (isopropanol)                    |     4
 stomach plus crop (70% ethanol)                  |     8
 stomach plus crop content (95% ethanol)          |     1
 stomach plus crop content (dry)                  |     3
 stomach plus stomach content (70% ethanol)       |     2
 stomach (RNAlater)                               |     1
 supratemporal                                    |     2
 swab, nasal (frozen)                             |     1
 swab, oral and cloacal (frozen)                  |     5
 swab, oral (formalin)                            |     1
 syrinx                                           |     8
 syrinx (95% ethanol)                             |     5
 syrinx (cleared and stained)                     |     8
 syrinx (dry)                                     |     1
 syrinx (frozen)                                  |     1
 tabular                                          |     4
 tail (ethanol-fixed)                             |     2
 tail tip (dry)                                   |     2
 tail tip (ethanol)                               |     1
 testis (70% ethanol)                             |     9
 testis (ethanol-fixed)                           |     1
 testis (formalin-fixed, 70% ethanol)             |     4
 thorax                                           |     3
 tissue (Alsever's solution)                      |     3
 tissue block (paraffin)                          |     3
 tissue (slide)                                   |     4
 toe pad                                          |     6
 toe pad (frozen)                                 |     2
 tooth, canine (frozen)                           |     1
 tooth, carnassial                                |     3
 tooth (ethanol)                                  |     2
 trachea (70% ethanol)                            |     7
 tumor (frozen)                                   |     8
 umbilical cord (frozen)                          |     2
 unknown (alcohol)                                |     8
 unknown (ethanol/glycerin)                       |     1
 unknown (slide)                                  |     4
 uterus                                           |     4
 uterus (frozen)                                  |     6
 vaginal plug (70% ethanol)                       |     1
 vaginal plug (dry)                               |     1
 vertebral disc                                   |     2
 vibrissa (ethanol, formalin-fixed)               |     1
 vibrissa (frozen)                                |     9
 viscera (formalin)                               |     1
 viscera (frozen)                                 |     2
 whole organism (freeze-dried)                    |     9
 whole organism (glycerin)                        |     6
 whole organism (RNAlater)                        |     3
 whole organism, skinned (ethanol)                |     5
(249 rows)

[Jegelewicz]

I plan to work on this - but it's going to be a week or so before I get to it...

[dustymc]

There is denormalization between these parts and http://arctos.database.museum/info/ctDocumentation.cfm?table=ctanatomical_direction

create table temp_prt_ana (p varchar);
CREATE OR REPLACE function temp_test() returns void AS $body$
DECLARE
  r record;
  p record;
begin
  for r in (select anatomical_direction from ctanatomical_direction) loop
    for p in (select distinct part_name from ctspecimen_part_name where part_name like '%' || r.anatomical_direction || '%') loop
      insert into temp_prt_ana(p) values(p.part_name);
    end loop;
  end loop;
end;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;

select temp_test();
select * from temp_prt_ana;
drop table temp_prt_ana;
drop function temp_test;

 skeleton, postcranial
 skeleton, postcranial (70% ethanol)
 skeleton, postcranial (DMSO)
 skeleton, postcranial (dry)
 skeleton, postcranial (ethanol)
 skeleton, postcranial (ethanol/glycerin)
 skeleton, postcranial (frozen)
 skeleton, postcranial (glycerin)
 skeleton, postcranial, mounted
 whole organism, postcranial (ethanol)
 whole organism, postcranial (ethanol, formalin-fixed)
 whole organism, postcranial (formalin)
 centrum, caudal
 neural arch, caudal
 rib, caudal
 vertebra, caudal
 denticle, rostral
 skeleton, appendicular
 skeleton, axial
 centrum, cervical
 neural arch, cervical
 rib, cervical
 vertebra, cervical
 vertebra, thoracic
 vertebra, lumbar
 rib, dorsal
 swab, dorsal
 centrum, dorsal
 vertebra, dorsal
 forelimb
 forelimb (dry)
 foot, hind
 hindgut content (70% ethanol)
 hindgut content (ethanol)
 hindlimb
 hindlimb (dry)
 hindlimb (frozen)
 hindquarters (latex vascular injection)
 podial, hind
 flower
 mandible, upper
 sternal
 metatarsal
 tarsal

[dustymc]

Upping priority for all code table messes, lest they invite more mess.

[Jegelewicz]

Sigh

[Jegelewicz]

I'm going to close this as a duplicate of #3708