Inconsistent category type in taxonomy tree

[grololo06]

It looks like a Phylo taxon should not be child of a Morpho one, however:

ecotaxa=# select txo.id, coalesce(txo.nbrobj,0)+coalesce(txo.nbrobjcum,0), txo.name, ptxo.id, ptxo.name 
from taxonomy txo, taxonomy ptxo 
where (txo.nbrobj>0 or txo.nbrobjcum>0)
  and ptxo.id=txo.parent_id 
 and txo.taxotype='P' 
 and ptxo.taxotype='M' order by txo.id;
  id   | ?column? |                  name                  |  id   |     name     
-------+----------+----------------------------------------+-------+---------------
 38408 |       46 | dark septate                           | 23188 | dark
 85069 |   401316 | othertocheck                           | 85011 | other
 85154 |     2026 | tempCeratoneis closterium              | 84959 | temporary
 85158 |      179 | tempChaetoceros contortus              | 84959 | temporary
 85161 |    53975 | tempCryptophyceae                      | 84959 | temporary
 85163 |       39 | tempKatodinium glaucum                 | 84959 | temporary
 85168 |     2293 | tempPrasinophyceae                     | 84959 | temporary
 85174 |        4 | tempCoccosphaerales                    | 84959 | temporary
 85183 |    12767 | tempflagellates                        | 84959 | temporary
 85206 |      100 | cyst 1                                 | 85096 | cyst
 85207 |      169 | cyst 2                                 | 85096 | cyst
 85208 |      715 | cyst 3                                 | 85096 | cyst
 85209 |      560 | cyst 4                                 | 85096 | cyst
 85210 |      150 | cyst 5                                 | 85096 | cyst
 85211 |       99 | cyst 6                                 | 85096 | cyst
 85212 |       60 | cyst 7                                 | 85096 | cyst
 85213 |     5665 | Coccolithes                            | 85068 | part
 85214 |      200 | Setae Corethron                        | 85068 | part
 85215 |     2677 | Setae                                  | 85068 | part
 85216 |     2923 | shells                                 | 85068 | part
 85235 |     2866 | centric 1 temp                         | 85009 | centric
 85236 |     2255 | centric 2 temp                         | 85009 | centric
 85237 |      688 | centric 3 temp                         | 85009 | centric
 85238 |     2817 | centric 4 temp                         | 85009 | centric
 85239 |      591 | centric 5 temp                         | 85009 | centric
 85240 |      231 | centric 6 temp                         | 85009 | centric
 85241 |      627 | centric 7 temp                         | 85009 | centric
 85242 |      175 | centric 8 temp                         | 85009 | centric
 85243 |     1005 | centric 9 temp                         | 85009 | centric
 85244 |      205 | centric 10 temp                        | 85009 | centric
 85245 |      167 | centric 11 temp                        | 85009 | centric
 85246 |      173 | centric 12 temp                        | 85009 | centric
 85247 |      100 | centric 13 temp                        | 85009 | centric
 85248 |     1974 | centric 14 temp                        | 85009 | centric
 85249 |      999 | pennate 1 temp                         | 85010 | pennate
 85250 |      325 | pennate 2 temp                         | 85010 | pennate
 85251 |     2930 | pennate 3 temp                         | 85010 | pennate
 85252 |      946 | pennate 4 temp                         | 85010 | pennate
 85253 |      175 | pennate 5 temp                         | 85010 | pennate
 85254 |     1431 | pennate 6 temp                         | 85010 | pennate
 85256 |      120 | pennate 8 temp                         | 85010 | pennate
 85257 |     1245 | pennate 9 temp                         | 85010 | pennate
 85258 |      761 | pennate 10 temp                        | 85010 | pennate
 85259 |      242 | pennate 11 temp                        | 85010 | pennate
 85260 |       62 | pennate 12 temp                        | 85010 | pennate
 85261 |       81 | pennate 13 temp                        | 85010 | pennate
 85322 |      700 | auto 5 temp                            | 85097 | autotroph
 85323 |      198 | auto 1 temp                            | 85097 | autotroph
 85324 |      650 | auto 2 temp                            | 85097 | autotroph
 85325 |     2500 | auto 3 temp                            | 85097 | autotroph
 85326 |      143 | auto 4 temp                            | 85097 | autotroph
 85327 |      100 | hetero 1 temp                          | 85098 | heterotroph
 85328 |      750 | hetero 2 temp                          | 85098 | heterotroph
 85329 |      100 | hetero 3 temp                          | 85098 | heterotroph
 85330 |       50 | hetero 4 temp                          | 85098 | heterotroph
 85331 |       50 | hetero 5 temp                          | 85098 | heterotroph
 85332 |      100 | hetero 6 temp                          | 85098 | heterotroph
 85333 |      150 | hetero 7 temp                          | 85098 | heterotroph
 85334 |      100 | hetero 8 temp                          | 85098 | heterotroph
 85335 |      450 | hetero 9 temp                          | 85098 | heterotroph
 92027 |     1091 | tempTorodinium robustum                | 84959 | temporary
 92031 |        1 | tempPeridiniella catenata              | 84959 | temporary
 92335 |      157 | Fossula arctica                        | 92329 | temporary
 92336 |      163 | Fragilariopsis doliolus                | 92329 | temporary
 92339 |        2 | Guinardia cylindrus                    | 92329 | temporary
 92340 |        4 | Hemiaulus hauckii                      | 92329 | temporary
 92341 |        1 | Hemiaulus indicus                      | 92329 | temporary
 92343 |       51 | Hemidiscus cuneiformis                 | 92329 | temporary
 92344 |      129 | Leptocylindrus mediterraneus           | 92329 | temporary
 92345 |        8 | Lioloma delicatulum                    | 92329 | temporary
 92347 |        2 | Lioloma pacificum                      | 92329 | temporary
 92349 |       85 | Nanoneis hasleae                       | 92329 | temporary
 92357 |        1 | Neodelphineis indica                   | 92329 | temporary
 92358 |        2 | Nitzschia bicapitata                   | 92329 | temporary
 92361 |        1 | Plagiotropis lepidoptera               | 92329 | temporary
 92367 |        1 | Rhizosolenia bergonii                  | 92329 | temporary
 92369 |        4 | Rhizosolenia chunii                    | 92329 | temporary
 92375 |        1 | Rhizosolenia sima f sima               | 92329 | temporary
 92378 |        1 | Rossithidium petersenii                | 92329 | temporary
 92379 |       17 | Roperia tesselata                      | 92329 | temporary
 92380 |        2 | Shionodiscus oestrupii                 | 92329 | temporary
 92382 |        1 | Shionodiscus oestrupii var. venrickae  | 92329 | temporary
 92386 |       12 | Thalassionema pseudonitzschioides      | 92329 | temporary
 92387 |        8 | Thalassiosira antarctica var. borealis | 92329 | temporary
 92389 |        2 | Bacterosira constricta                 | 92329 | temporary
 92390 |        1 | Thalassiosira decipiens                | 92329 | temporary
 92393 |        1 | Thalassiosira gracilis var. gracilis   | 92329 | temporary
 92394 |        3 | Thalassiosira hyalina                  | 92329 | temporary
 92397 |        1 | Thalassiosira subtilis                 | 92329 | temporary
 92398 |        6 | Thalassiosira tealata                  | 92329 | temporary
 92399 |        3 | Thalassiosira tubifera                 | 92329 | temporary
 92402 |       80 | Thalassiothrix sp.                     | 92329 | temporary
 92407 |        1 | Actiniscus pentasterias                | 92329 | temporary
 92416 |       25 | Blepharocysta splendor-maris           | 92329 | temporary
 92424 |        5 | Ceratocorys gourretii                  | 92329 | temporary
 92425 |        2 | Ceratocorys sp.                        | 92329 | temporary
 92427 |        3 | Citharistes regius                     | 92329 | temporary
 92428 |      102 | Cladopyxis brachiolata                 | 92329 | temporary
 92429 |        3 | Cladopyxis caryophyllum                | 92329 | temporary
 92431 |       34 | Corythodinium sp.                      | 92329 | temporary
 92432 |        3 | Corythodinium constrictum              | 92329 | temporary
 92435 |       27 | Corythodinium tesselatum               | 92329 | temporary
 92440 |        4 | Dinophysis ovum                        | 92329 | temporary
 92443 |        1 | Dinophysis sacculus                    | 92329 | temporary
 92444 |        5 | Dinophysis schuettii                   | 92329 | temporary
 92450 |       62 | Goniodoma polyedricum                  | 92329 | temporary
 92453 |        7 | Gonyaulax birostris                    | 92329 | temporary
 92458 |        4 | Gonyaulax fragilis                     | 92329 | temporary
 92459 |       11 | Gonyaulax fusiformis                   | 92329 | temporary
 92463 |        1 | Gonyaulax monacantha                   | 92329 | temporary
 92467 |        4 | Gonyaulax sphaeroidea                  | 92329 | temporary
 92479 |        4 | Histioneis elongata                    | 92329 | temporary
 92482 |        1 | Histioneis mediterranea                | 92329 | temporary
 92498 |        8 | Histioneis variabilis                  | 92329 | temporary
 92499 |        7 | Mesoporos perforatus                   | 92329 | temporary
 92501 |      148 | Micracanthodinium quadrispinum         | 92329 | temporary
 92508 |        8 | Oxytoxum crassum                       | 92329 | temporary
 92509 |        4 | Oxytoxum curvatum                      | 92329 | temporary
 92511 |        2 | Oxytoxum galdiolus                     | 92329 | temporary
 92514 |       12 | Oxytoxum milneri                       | 92329 | temporary
 92515 |        5 | Oxytoxum obliquum                      | 92329 | temporary
 92517 |        1 | Oxytoxum punctulatum                   | 92329 | temporary
 92519 |       17 | Oxytoxum sceptrum                      | 92329 | temporary
 92520 |        5 | Oxytoxum scolopax                      | 92329 | temporary
 92524 |       25 | Oxytoxum variabile                     | 92329 | temporary
 92526 |       32 | Palaeophalacroma unicinctum            | 92329 | temporary
 92534 |       14 | Prorocentrum balticum                  | 92329 | temporary
 92537 |        4 | Prorocentrum nux                       | 92329 | temporary
 92538 |       18 | Prorocentrum rostratum                 | 92329 | temporary
 92540 |        1 | Protoceratium areolatum                | 92329 | temporary
 92541 |       30 | Protoceratium spinulosum               | 92329 | temporary
 92544 |       11 | Protoperidinium brevipes               | 92329 | temporary
 92545 |        3 | Protoperidinium crassipyrum            | 92329 | temporary
 92547 |        1 | Protoperidinium crassipes              | 92329 | temporary
 92551 |        6 | Protoperidinium granii                 | 92329 | temporary
 92552 |       15 | Protoperidinium incertum               | 92329 | temporary
 92555 |        9 | Protoperidinium marukawai              | 92329 | temporary
 92566 |       27 | Protoperidinium steinii                | 92329 | temporary
 92568 |        1 | Protoperidinium subsphaericum          | 92329 | temporary
 92571 |        6 | Ptychodiscus noctiluca                 | 92329 | temporary
 92572 |        3 | Pyrocystis elegans                     | 92329 | temporary
 92581 |        2 | Tripos arcticus                        | 92329 | temporary
 92582 |        3 | Tripos arietinus                       | 92329 | temporary
 92584 |        3 | Tripos azoricus                        | 92329 | temporary
 92586 |        8 | Tripos brevis                          | 92329 | temporary
 92587 |       10 | Tripos candelabrus                     | 92329 | temporary
 92588 |        8 | Tripos carriensis                      | 92329 | temporary
 92591 |        1 | Tripos contortum                       | 92329 | temporary
 92592 |       38 | Tripos contrarius                      | 92329 | temporary
 92593 |       66 | Tripos declinatus                      | 92329 | temporary
 92594 |        2 | Tripos digitatus                       | 92329 | temporary
 92595 |        1 | Tripos divaricatus                     | 92329 | temporary
 92598 |      320 | Tripos furca                           | 92329 | temporary
 92600 |      400 | Tripos fusus                           | 92329 | temporary
 92603 |       59 | Tripos gibberius                       | 92329 | temporary
 92605 |        1 | Tripos hexacanthus                     | 92329 | temporary
 92608 |        2 | Tripos lineatus                        | 92329 | temporary
 92611 |        2 | Tripos macroceros                      | 92329 | temporary
 92612 |       13 | Tripos massiliensis                    | 92329 | temporary
 92613 |        7 | Tripos minutus                         | 92329 | temporary
 92614 |      169 | Tripos muelleri                        | 92329 | temporary
 92617 |      215 | Tripos pentagonus                      | 92329 | temporary
 92620 |        4 | Tripos praelongus                      | 92329 | temporary
 92624 |      444 | Tripos teres                           | 92329 | temporary
 92625 |       46 | Tripos trichoceros                     | 92329 | temporary
 92627 |        1 | Calcidiscus leptoporus                 | 92329 | temporary
 92628 |        3 | Calciopappus sp.                       | 92329 | temporary
 92640 |        1 | Hyalolithus neolepis                   | 92329 | temporary
 92648 |        1 | Rhabdosphaera claviger                 | 92329 | temporary
 92651 |        1 | Dinobryon balticum                     | 92329 | temporary
 92660 |        4 | Octactis octonaria                     | 92329 | temporary
 92677 |     4416 | Radiozoa                               | 92329 | temporary
 92678 |     2361 | Undetermined flagellate                | 92329 | temporary
 92679 |        6 | Coccolithophores                       | 92329 | temporary
 92887 |      217 | Trochophora                            | 92838 | trochozoa
 93165 |        1 | Xanthichthys                           | 92893 | balistidae
 93194 |        1 | Balistes                               | 92893 | balistidae
 93274 |        2 | Holocentrus                            | 92906 | holocentridae
(178 lignes)

@jiho , if it's OK with you then let's forbid the bad pattern creation.
BTW, any other rule in this area?

[jiho]

Yes, the pattern should be forbidden.

In the cases above:

• most of those child of temporary were created there to be quick and dirty, waiting for UniEuk/WoRMS to come along
• the children of pennate, autotroph etc. should be morpho
• balistidae, holocentridae should be phylo.

[grololo06]

balistidae, holocentridae -> corrected onto EcoTaxoserver, and propagated fine to EcoTaxa.
living>temporary should be Morpho, which solves the temporary issues.
The rest is in WoRMS move Google sheet

[grololo06]

Log of execution onto ecotaxoserver. As the direct SQL does not propagate, same commands were ran onto EcoTaxa DB.

ecotaxoserver=# update taxonomy set taxotype='M' where id in (17263,17264,27675,27676,13364,13363,85234,85323,85324,85325,85326,85322,92688,92689,92230,92231,92232,85235,85244,85245,85246,85247,85248,85236,85237,85238,85239,85240,85241,85242,85243,85230,85231,85195,85196,85197,92273,92266,92304,92272,92265,11880,11879,11878,11877,11876,92271,92264,92274,92267,92306,85302,85311,85312,85313,85314,85315,85316,85317,85318,85319,85303,85304,85305,85306,85307,85308,85309,85310,85213,92323,92316,92315,92317,92320,92322,92321,92275,92268,92307,92255,85202,85203,85204,85206,85207,85208,85209,85210,85211,85212,38408,85217,85340,85346,85341,85347,85342,85348,85343,85349,85344,85350,85345,85351,92237,85337,93602,92682,92277,92270,92253,92309,92258,92312,92247,92313,92327,85263,85264,24576,85275,85276,85277,85278,85279,85280,85281,85282,85283,85266,85267,85268,85269,85270,85271,85272,85273,85284,85285,85286,85287,58306,58319,58322,85299,85300,85301,85327,85328,85329,85330,85331,85332,85333,85334,85335,58458,92269,92252,92276,92314,92256,92308,93601,92245,92236,85069,85249,85258,85259,85260,85261,85250,85251,85252,85253,85254,85255,85256,85257,27304,27303,27302,85289,85290,85291,85292,85293,85294,85295,85297,91419,85218,85219,85220,85221,85222,85223,85198,85199,85320,85321,85215,85214,85216,85226,85225,85228,85227,85224,85036,85038,85039,85037,85148,85154,85158,85229,85174,85161,85183,85163,92031,85168,92027,85298,93528,92587,92591,92603,92887,8982,92678,26325,51958,6);
UPDATE 237
Temps : 172,201 ms
ecotaxoserver=# update taxonomy set taxotype='P' where id=92329;
UPDATE 1
Temps : 0,656 ms
ecotaxoserver=# select txo.id, coalesce(txo.nbrobj,0)+coalesce(txo.nbrobjcum,0), txo.name, ptxo.id, ptxo.name 
ecotaxoserver-# from taxonomy txo, taxonomy ptxo 
ecotaxoserver-# where (txo.nbrobj>0 or txo.nbrobjcum>0)
ecotaxoserver-#   and ptxo.id=txo.parent_id 
ecotaxoserver-#  and txo.taxotype='P' 
ecotaxoserver-#  and ptxo.taxotype='M' order by txo.id;
 id | ?column? | name | id | name 
----+----------+------+----+------
(0 ligne)

Temps : 64,544 ms