/
0044_adjust_smallvariantstats.py
133 lines (121 loc) · 5.05 KB
/
0044_adjust_smallvariantstats.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
# -*- coding: utf-8 -*-
"""Adjust the small variants statistics materialized view to the new schema.
This is done by recreating it.
"""
from django.conf import settings
from django.db import migrations, models
operations = [
migrations.DeleteModel(name="SmallVariantSummary"),
migrations.CreateModel(
name="SmallVariantSummary",
fields=[
(
"id",
models.AutoField(
auto_created=True, primary_key=True, serialize=False, verbose_name="ID"
),
),
("release", models.CharField(max_length=32)),
("chromosome", models.CharField(max_length=32)),
("start", models.IntegerField()),
("end", models.IntegerField()),
("bin", models.IntegerField()),
("reference", models.CharField(max_length=512)),
("alternative", models.CharField(max_length=512)),
("count_hom_ref", models.IntegerField()),
("count_het", models.IntegerField()),
("count_hom_alt", models.IntegerField()),
("count_hemi_ref", models.IntegerField()),
("count_hemi_alt", models.IntegerField()),
],
options={"db_table": "variants_smallvariantsummary", "managed": settings.IS_TESTING},
),
]
if not settings.IS_TESTING:
operations.append(
migrations.RunSQL(
"""
DROP MATERIALIZED VIEW IF EXISTS variants_smallvariantsummary;
CREATE MATERIALIZED VIEW variants_smallvariantsummary
AS
SELECT
row_number() OVER (PARTITION BY true) AS id,
release,
chromosome,
start,
"end",
bin,
reference,
alternative,
sum(num_hom_ref) AS count_hom_ref,
sum(num_het) AS count_het,
sum(num_hom_alt) AS count_hom_alt,
sum(num_hemi_ref) AS count_hemi_ref,
sum(num_hemi_alt) AS count_hemi_alt
FROM (
SELECT DISTINCT
variants.release,
variants.chromosome,
variants.start,
variants."end",
variants.bin,
variants.reference,
variants.alternative,
variants.num_hom_ref,
variants.num_het,
variants.num_hom_alt,
variants.num_hemi_ref,
variants.num_hemi_alt,
variants.case_id
FROM variants_smallvariant AS variants
) AS variants_per_case
GROUP BY (release, chromosome, start, "end", bin, reference, alternative)
WITH NO DATA;
CREATE UNIQUE INDEX variants_smallvariantsummary_id ON variants_smallvariantsummary(id);
CREATE INDEX variants_smallvariantsummary_coord ON variants_smallvariantsummary(
release, chromosome, start, "end", bin, reference, alternative
);
""",
"""
DROP MATERIALIZED VIEW IF EXISTS variants_smallvariantsummary;
CREATE MATERIALIZED VIEW variants_smallvariantsummary
AS
SELECT
row_number() OVER (PARTITION BY true) AS id,
release,
chromosome,
start,
reference,
alternative,
sum(num_hom_ref) AS count_hom_ref,
sum(num_het) AS count_het,
sum(num_hom_alt) AS count_hom_alt,
sum(num_hemi_ref) AS count_hemi_ref,
sum(num_hemi_alt) AS count_hemi_alt
FROM (
SELECT DISTINCT
variants.release,
variants.chromosome,
variants.start,
variants.reference,
variants.alternative,
variants.num_hom_ref,
variants.num_het,
variants.num_hom_alt,
variants.num_hemi_ref,
variants.num_hemi_alt,
variants.case_id
FROM variants_smallvariant AS variants
) AS variants_per_case
GROUP BY (release, chromosome, start, reference, alternative)
WITH DATA;
CREATE UNIQUE INDEX variants_smallvariantsummary_id ON variants_smallvariantsummary(id);
CREATE INDEX variants_smallvariantsummary_coord ON variants_smallvariantsummary(
release, chromosome, start, reference, alternative
);
""",
)
)
class Migration(migrations.Migration):
dependencies = [("variants", "0043_synccaselistbgjob_synccaseresultmessage")]
operations = operations