[ensembl-dev] Schema patch for ensembl ontology 71 -> 72

[Jan Vogel]

Hi, 

again. some patching issues - I try to patch an ensembl-ontology  67 db to 74. 

There are issues with the patch_71_72_e.sql patch  - the table TERM does not exist ( but term ) - attached a patch file for the patch. 

Also, some other changes are missing ( there are no patch files for 67-> 68, 68->69, 69->70, 70->71 ). 

I've listed the changes below but haven't run any tests with the 74 api for this. Did anyone else do ? How does this work with the new ontology_id column in the relation and closure tables ? 

Maybe I should consider just downloading the new ontology 74 db … 

Regards, 

   Jan Vogel

File :  ensembl-74/ensembl/misc-scripts/ontology/sql/patch_71_72_e.sql 


ALTER TABLE TERM ADD COLUMN is_obsolete INT NOT NULL DEFAULT 0;

-- Patch identifier
INSERT INTO meta (meta_key, meta_value)   VALUES ('patch', 'patch_71_72_e.sql|is_obsolete');




Additional changes from 67 -> 74 which are not in patch files: 

table term is missing the "is_root" column : 

ALTER TABLE term ADD COLUMN is_root INT NOT NULL DEFAULT 0;

table relation and table closure is missing ontology_id column and subsequently the indexes have to be updated:

ALTER TABLE relation ADD COLUMN ontology_id INT UNSIGNED NOT  NULL;
ALTER TABLE closure  ADD COLUMN ontology_id INT UNSIGNED NOT  NULL;

DROP INDEX child_parent_idx ON relation; 
CREATE UNIQUE INDEX child_parent_idx ON relation   (child_term_id, parent_term_id, relation_type_id, intersection_of, ontology_id) ; 

DROP INDEX child_parent_idx ON closure; 
CREATE UNIQUE INDEX child_parent_idx ON closure    (child_term_id, parent_term_id, subparent_term_id, ontology_id)  ; 




 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.ensembl.org/pipermail/dev_ensembl.org/attachments/20140116/d3a72364/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: patch_71_72_e.sql.patch
Type: application/octet-stream
Size: 410 bytes
Desc: not available
URL: <http://mail.ensembl.org/pipermail/dev_ensembl.org/attachments/20140116/d3a72364/attachment.obj>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.ensembl.org/pipermail/dev_ensembl.org/attachments/20140116/d3a72364/attachment-0001.html>