Coverage for src/python/ensembl/xrefs/xref_update_db_model.py: 100%
192 statements
« prev ^ index » next coverage.py v7.15.1, created at 2026-07-13 09:48 +0000
1# See the NOTICE file distributed with this work for additional information
2# regarding copyright ownership.
3#
4# Licensed under the Apache License, Version 2.0 (the "License");
5# you may not use this file except in compliance with the License.
6# You may obtain a copy of the License at
7# http://www.apache.org/licenses/LICENSE-2.0
8#
9# Unless required by applicable law or agreed to in writing, software
10# distributed under the License is distributed on an "AS IS" BASIS,
11# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12# See the License for the specific language governing permissions and
13# limitations under the License.
14"""Xref Update database ORM."""
16# Ignore some pylint and mypy checks due to the nature of SQLAlchemy ORMs
17# pylint: disable=missing-class-docstring,too-many-lines
18# mypy: disable-error-code="misc, valid-type"
20from sqlalchemy import Column, Index, Enum, DateTime, text
21from sqlalchemy.dialects.mysql import (
22 INTEGER,
23 VARCHAR,
24 TEXT,
25 MEDIUMTEXT,
26 TINYINT,
27 CHAR,
28 SMALLINT,
29 DOUBLE,
30)
31from sqlalchemy.orm import declarative_base
33Base = declarative_base()
36class Xref(Base):
37 __tablename__ = "xref"
38 __table_args__ = (
39 Index(
40 "acession_idx",
41 "accession",
42 "source_id",
43 "species_id",
44 "label",
45 unique=True,
46 mysql_length={"accession": 100, "label": 100},
47 ),
48 Index("species_source_idx", "species_id", "source_id"),
49 )
51 xref_id: Column = Column(INTEGER(10, unsigned=True), primary_key=True, autoincrement=True)
52 accession: Column = Column(VARCHAR(255), nullable=False)
53 version: Column = Column(INTEGER(10, unsigned=True))
54 label: Column = Column(VARCHAR(255))
55 description: Column = Column(TEXT)
56 source_id: Column = Column(INTEGER(10, unsigned=True), nullable=False)
57 species_id: Column = Column(INTEGER(10, unsigned=True), nullable=False)
58 info_type: Column = Column(
59 Enum(
60 "NONE",
61 "PROJECTION",
62 "MISC",
63 "DEPENDENT",
64 "DIRECT",
65 "SEQUENCE_MATCH",
66 "INFERRED_PAIR",
67 "PROBE",
68 "UNMAPPED",
69 "COORDINATE_OVERLAP",
70 "CHECKSUM",
71 ),
72 nullable=False,
73 server_default=text("'NONE'"),
74 )
75 info_text: Column = Column(VARCHAR(255), nullable=False, server_default=text("''"))
76 dumped: Column = Column(
77 Enum(
78 "MAPPED",
79 "NO_DUMP_ANOTHER_PRIORITY",
80 "UNMAPPED_NO_MAPPING",
81 "UNMAPPED_NO_MASTER",
82 "UNMAPPED_MASTER_FAILED",
83 "UNMAPPED_NO_STABLE_ID",
84 "UNMAPPED_INTERPRO",
85 )
86 )
89class PrimaryXref(Base):
90 __tablename__ = "primary_xref"
92 xref_id: Column = Column(INTEGER(10, unsigned=True), primary_key=True)
93 sequence: Column = Column(MEDIUMTEXT)
94 sequence_type: Column = Column(Enum("dna", "peptide"))
95 status: Column = Column(Enum("experimental", "predicted"))
98class DependentXref(Base):
99 __tablename__ = "dependent_xref"
101 object_xref_id: Column = Column(INTEGER(10, unsigned=True), index=True)
102 master_xref_id: Column = Column(INTEGER(10, unsigned=True), index=True, primary_key=True)
103 dependent_xref_id: Column = Column(INTEGER(10, unsigned=True), index=True, primary_key=True)
104 linkage_annotation: Column = Column(VARCHAR(255))
105 linkage_source_id: Column = Column(INTEGER(10, unsigned=True), nullable=False, primary_key=True)
108class Synonym(Base):
109 __tablename__ = "synonym"
111 xref_id: Column = Column(INTEGER(10, unsigned=True), index=True, primary_key=True)
112 synonym: Column = Column(VARCHAR(255), index=True, primary_key=True)
115class Source(Base):
116 __tablename__ = "source"
118 source_id: Column = Column(INTEGER(10, unsigned=True), primary_key=True, autoincrement=True)
119 name: Column = Column(VARCHAR(255), nullable=False, index=True)
120 status: Column = Column(
121 Enum("KNOWN", "XREF", "PRED", "ORTH", "PSEUDO", "LOWEVIDENCE", "NOIDEA"),
122 nullable=False,
123 server_default=text("'NOIDEA'"),
124 )
125 source_release: Column = Column(VARCHAR(255))
126 ordered: Column = Column(INTEGER(10, unsigned=True), nullable=False)
127 priority: Column = Column(INTEGER(5, unsigned=True), server_default=text("1"))
128 priority_description: Column = Column(VARCHAR(40), server_default=text("''"))
131class SourceURL(Base):
132 __tablename__ = "source_url"
134 source_url_id: Column = Column(INTEGER(10, unsigned=True), primary_key=True, autoincrement=True)
135 source_id: Column = Column(INTEGER(10, unsigned=True), nullable=False, index=True)
136 species_id: Column = Column(INTEGER(10, unsigned=True), nullable=False)
137 parser: Column = Column(VARCHAR(255))
140class GeneDirectXref(Base):
141 __tablename__ = "gene_direct_xref"
143 general_xref_id: Column = Column(INTEGER(10, unsigned=True), index=True, primary_key=True)
144 ensembl_stable_id: Column = Column(VARCHAR(255), index=True, primary_key=True)
145 linkage_xref: Column = Column(VARCHAR(255))
148class TranscriptDirectXref(Base):
149 __tablename__ = "transcript_direct_xref"
151 general_xref_id: Column = Column(INTEGER(10, unsigned=True), index=True, primary_key=True)
152 ensembl_stable_id: Column = Column(VARCHAR(255), index=True, primary_key=True)
153 linkage_xref: Column = Column(VARCHAR(255))
156class TranslationDirectXref(Base):
157 __tablename__ = "translation_direct_xref"
159 general_xref_id: Column = Column(INTEGER(10, unsigned=True), index=True, primary_key=True)
160 ensembl_stable_id: Column = Column(VARCHAR(255), index=True, primary_key=True)
161 linkage_xref: Column = Column(VARCHAR(255))
164class Species(Base):
165 __tablename__ = "species"
167 species_id: Column = Column(INTEGER(10, unsigned=True), nullable=False, index=True, primary_key=True)
168 taxonomy_id: Column = Column(INTEGER(10, unsigned=True), nullable=False, index=True, primary_key=True)
169 name: Column = Column(VARCHAR(255), nullable=False, index=True)
170 aliases: Column = Column(VARCHAR(255))
173class Pairs(Base):
174 __tablename__ = "pairs"
176 pair_id: Column = Column(INTEGER(10, unsigned=True), primary_key=True, autoincrement=True)
177 source_id: Column = Column(INTEGER(10, unsigned=True), nullable=False)
178 accession1: Column = Column(VARCHAR(255), nullable=False, index=True)
179 accession2: Column = Column(VARCHAR(255), nullable=False, index=True)
182class CoordinateXref(Base):
183 __tablename__ = "coordinate_xref"
184 __table_args__ = (
185 Index("start_pos_idx", "species_id", "chromosome", "strand", "txStart"),
186 Index("end_pos_idx", "species_id", "chromosome", "strand", "txEnd"),
187 )
189 coord_xref_id: Column = Column(INTEGER(10, unsigned=True), primary_key=True, autoincrement=True)
190 source_id: Column = Column(INTEGER(10, unsigned=True), nullable=False)
191 species_id: Column = Column(INTEGER(10, unsigned=True), nullable=False)
192 accession: Column = Column(VARCHAR(255), nullable=False)
193 chromosome: Column = Column(VARCHAR(255), nullable=False)
194 strand: Column = Column(TINYINT(2), nullable=False)
195 txStart: Column = Column(INTEGER(10), nullable=False)
196 txEnd: Column = Column(INTEGER(10), nullable=False)
197 cdsStart: Column = Column(INTEGER(10))
198 cdsEnd: Column = Column(INTEGER(10))
199 exonStarts: Column = Column(TEXT, nullable=False)
200 exonEnds: Column = Column(TEXT, nullable=False)
203class ChecksumXref(Base):
204 __tablename__ = "checksum_xref"
205 __table_args__ = (Index("checksum_idx", "checksum", mysql_length=10),)
207 checksum_xref_id: Column = Column(INTEGER(10, unsigned=True), primary_key=True, autoincrement=True)
208 source_id: Column = Column(INTEGER(10, unsigned=True), nullable=False)
209 accession: Column = Column(CHAR(14), nullable=False)
210 checksum: Column = Column(CHAR(32), nullable=False)
213class Mapping(Base):
214 __tablename__ = "mapping"
216 job_id: Column = Column(INTEGER(10, unsigned=True), primary_key=True)
217 type: Column = Column(Enum("dna", "peptide", "UCSC"))
218 command_line: Column = Column(TEXT)
219 percent_query_cutoff: Column = Column(INTEGER(10, unsigned=True))
220 percent_target_cutoff: Column = Column(INTEGER(10, unsigned=True))
221 method: Column = Column(VARCHAR(255))
222 array_size: Column = Column(INTEGER(10, unsigned=True))
225class MappingJobs(Base):
226 __tablename__ = "mapping_jobs"
228 mapping_job_id: Column = Column(INTEGER(10), primary_key=True, autoincrement=True)
229 root_dir: Column = Column(TEXT)
230 map_file: Column = Column(VARCHAR(255))
231 status: Column = Column(Enum("SUBMITTED", "FAILED", "SUCCESS"))
232 out_file: Column = Column(VARCHAR(255))
233 err_file: Column = Column(VARCHAR(255))
234 array_number: Column = Column(INTEGER(10, unsigned=True))
235 job_id: Column = Column(INTEGER(10, unsigned=True))
236 failed_reason: Column = Column(VARCHAR(255))
237 object_xref_start: Column = Column(INTEGER(10, unsigned=True))
238 object_xref_end: Column = Column(INTEGER(10, unsigned=True))
241class GeneTranscriptTranslation(Base):
242 __tablename__ = "gene_transcript_translation"
244 gene_id: Column = Column(INTEGER(10, unsigned=True), nullable=False, index=True)
245 transcript_id: Column = Column(INTEGER(10, unsigned=True), primary_key=True)
246 translation_id: Column = Column(INTEGER(10, unsigned=True), index=True)
249class ProcessStatus(Base):
250 __tablename__ = "process_status"
252 id: Column = Column(INTEGER(10, unsigned=True), primary_key=True, autoincrement=True)
253 status: Column = Column(
254 Enum(
255 "xref_created",
256 "parsing_started",
257 "parsing_finished",
258 "alt_alleles_added",
259 "xref_fasta_dumped",
260 "core_fasta_dumped",
261 "core_data_loaded",
262 "mapping_submitted",
263 "mapping_finished",
264 "mapping_processed",
265 "direct_xrefs_parsed",
266 "priorities_flagged",
267 "processed_pairs",
268 "biomart_test_finished",
269 "source_level_move_finished",
270 "alt_alleles_processed",
271 "official_naming_done",
272 "checksum_xrefs_started",
273 "checksum_xrefs_finished",
274 "coordinate_xrefs_started",
275 "coordinate_xref_finished",
276 "tests_started",
277 "tests_failed",
278 "tests_finished",
279 "core_loaded",
280 "display_xrefs_done",
281 "gene_descriptions_done",
282 "direct_stable_id_check_done",
283 "xrefs_counts_check_done",
284 "name_change_check_done",
285 )
286 )
287 date: Column = Column(DateTime, nullable=False)
290class DisplayXrefPriority(Base):
291 __tablename__ = "display_xref_priority"
293 ensembl_object_type: Column = Column(VARCHAR(100), primary_key=True)
294 source_id: Column = Column(INTEGER(10, unsigned=True), primary_key=True)
295 priority: Column = Column(SMALLINT(unsigned=True), nullable=False)
298class GeneDescPriority(Base):
299 __tablename__ = "gene_desc_priority"
301 source_id: Column = Column(INTEGER(10, unsigned=True), primary_key=True)
302 priority: Column = Column(SMALLINT(unsigned=True), nullable=False)
305class AltAllele(Base):
306 __tablename__ = "alt_allele"
308 alt_allele_id: Column = Column(INTEGER(10, unsigned=True), primary_key=True)
309 gene_id: Column = Column(INTEGER(10, unsigned=True), index=True, primary_key=True)
310 is_reference: Column = Column(INTEGER(2, unsigned=True), server_default=text("0"))
313class GeneStableId(Base):
314 __tablename__ = "gene_stable_id"
316 internal_id: Column = Column(INTEGER(10, unsigned=True), nullable=False, index=True)
317 stable_id: Column = Column(VARCHAR(128), primary_key=True)
318 display_xref_id: Column = Column(INTEGER(10, unsigned=True))
319 desc_set: Column = Column(INTEGER(10, unsigned=True), server_default=text("0"))
322class TranscriptStableId(Base):
323 __tablename__ = "transcript_stable_id"
325 internal_id: Column = Column(INTEGER(10, unsigned=True), nullable=False, index=True)
326 stable_id: Column = Column(VARCHAR(128), primary_key=True)
327 display_xref_id: Column = Column(INTEGER(10, unsigned=True))
328 biotype: Column = Column(VARCHAR(40), nullable=False)
331class TranslationStableId(Base):
332 __tablename__ = "translation_stable_id"
334 internal_id: Column = Column(INTEGER(10, unsigned=True), primary_key=True)
335 stable_id: Column = Column(VARCHAR(128), nullable=False, index=True)
338class ObjectXref(Base):
339 __tablename__ = "object_xref"
340 __table_args__ = (
341 Index(
342 "unique_idx",
343 "ensembl_object_type",
344 "ensembl_id",
345 "xref_id",
346 "ox_status",
347 "master_xref_id",
348 unique=True,
349 ),
350 Index("oxref_idx", "object_xref_id", "xref_id", "ensembl_object_type", "ensembl_id"),
351 Index("xref_idx", "xref_id", "ensembl_object_type"),
352 )
354 object_xref_id: Column = Column(INTEGER(10, unsigned=True), primary_key=True, autoincrement=True)
355 ensembl_id: Column = Column(INTEGER(10, unsigned=True), nullable=False)
356 ensembl_object_type: Column = Column(
357 Enum("RawContig", "Transcript", "Gene", "Translation"), nullable=False
358 )
359 xref_id: Column = Column(INTEGER(10, unsigned=True), nullable=False)
360 linkage_annotation: Column = Column(VARCHAR(255))
361 linkage_type: Column = Column(
362 Enum(
363 "PROJECTION",
364 "MISC",
365 "DEPENDENT",
366 "DIRECT",
367 "SEQUENCE_MATCH",
368 "INFERRED_PAIR",
369 "PROBE",
370 "UNMAPPED",
371 "COORDINATE_OVERLAP",
372 "CHECKSUM",
373 )
374 )
375 ox_status: Column = Column(
376 Enum("DUMP_OUT", "FAILED_PRIORITY", "FAILED_CUTOFF", "NO_DISPLAY", "MULTI_DELETE"),
377 nullable=False,
378 server_default=text("'DUMP_OUT'"),
379 )
380 unused_priority: Column = Column(INTEGER(10, unsigned=True))
381 master_xref_id: Column = Column(INTEGER(10, unsigned=True))
384class IdentityXref(Base):
385 __tablename__ = "identity_xref"
387 object_xref_id: Column = Column(INTEGER(10, unsigned=True), primary_key=True)
388 query_identity: Column = Column(INTEGER(5))
389 target_identity: Column = Column(INTEGER(5))
390 hit_start: Column = Column(INTEGER(10))
391 hit_end: Column = Column(INTEGER(10))
392 translation_start: Column = Column(INTEGER(10))
393 translation_end: Column = Column(INTEGER(10))
394 cigar_line: Column = Column(TEXT)
395 score: Column = Column(DOUBLE)
396 evalue: Column = Column(DOUBLE)
399class Meta(Base):
400 __tablename__ = "meta"
401 __table_args__ = (
402 Index("species_key_value_idx", "meta_id", "species_id", "meta_key", "meta_value", unique=True),
403 Index("species_value_idx", "species_id", "meta_value"),
404 )
406 meta_id: Column = Column(INTEGER(10), primary_key=True, autoincrement=True)
407 species_id: Column = Column(INTEGER(10, unsigned=True), server_default=text("1"))
408 meta_key: Column = Column(VARCHAR(40), nullable=False)
409 meta_value: Column = Column(VARCHAR(255, binary=True), nullable=False)
410 date: Column = Column(DateTime, nullable=False)