db-schema/functional_classes.sql at master · antismash/db-schema · GitHub

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
CREATE TABLE IF NOT EXISTS antismash.functional_classes (
    functional_class_id	serial NOT NULL,
    name	text,
    parent_id	int4,
    CONSTRAINT functional_classes_pkey PRIMARY KEY (functional_class_id),
    CONSTRAINT functional_classes_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES antismash.functional_classes (functional_class_id)
);
COMMENT ON TABLE antismash.functional_classes IS
  'An index of possible functional classes';

--- Basic functional classes
INSERT INTO antismash.functional_classes (name, parent_id)
SELECT val.name, val.parent_id::int4
FROM (
    VALUES
        ('biosynthetic', NULL),
        ('regulator', NULL),
        ('transporter', NULL),
        ('other', NULL)
    ) val ( name, parent_id )
    ON CONFLICT DO NOTHING;


--- Advanced classes
INSERT INTO antismash.functional_classes (name, parent_id)
SELECT val.name, f.functional_class_id
FROM (
    VALUES
        ('bgc_seed', 'biosynthetic'),
        ('biosynthetic_smcog', 'biosynthetic')
    ) val ( name, parent_name )
LEFT JOIN antismash.functional_classes f ON val.parent_name = f.name
ON CONFLICT DO NOTHING;


--- Advanced classes based on EC groups
INSERT INTO antismash.functional_classes (name, parent_id)
SELECT val.name, f.functional_class_id
FROM (
    VALUES
        ('oxidoreductases', 'biosynthetic'),
        ('transferases', 'biosynthetic'),
        ('hydrolases', 'biosynthetic'),
        ('lyases', 'biosynthetic'),
        ('isomerases', 'biosynthetic'),
        ('ligases', 'biosynthetic'),
        ('translocases', 'biosynthetic')
    ) val ( name, parent_name )
LEFT JOIN antismash.functional_classes f ON val.parent_name = f.name
ON CONFLICT DO NOTHING;


--- Subfunctions based on EC groups
INSERT INTO antismash.functional_classes (name, parent_id)
SELECT val.name, f.functional_class_id
FROM (
    VALUES
        ('pptase', 'transferases'),
        ('acetyltransferase', 'transferases'),
        ('acylase', 'transferases'),
        ('acyltransferase', 'transferases'),
        ('aminotransferase', 'transferases'),
        ('carbamoyltransferase', 'transferases'),
        ('carboxylase', 'transferases'),
        ('formyltransferase', 'transferases'),
        ('glycosyltransferase', 'transferases'),
        ('halogenase', 'transferases'),
        ('kinase', 'transferases'),
        ('methyltransferase', 'transferases'),
        ('phosphorylase', 'transferases'),
        ('phosphotransferase', 'transferases'),
        ('sulfhydrylase', 'transferases'),
        ('sulfotransferase', 'transferases'),
        ('transferase', 'transferases'),
        ('transketolase', 'transferases'),
        ('cyclase', 'lyases'),
        ('decarboxylase', 'lyases'),
        ('dehydratase', 'lyases'),
        ('hydratase', 'lyases'),
        ('lyase', 'lyases'),
        ('dehydrogenase', 'oxidoreductases'),
        ('electron transfer', 'oxidoreductases'),
        ('oxidase', 'oxidoreductases'),
        ('oxidoreductase', 'oxidoreductases'),
        ('oxygenase', 'oxidoreductases'),
        ('phosphate reductase', 'oxidoreductases'),
        ('reductase', 'oxidoreductases'),
        ('ligase', 'ligases'),
        ('synthase', 'ligases'),
        ('synthetase', 'ligases'),
        ('amidase', 'hydrolases'),
        ('back_translocase', 'hydrolases'),
        ('deacetylase', 'hydrolases'),
        ('deaminase', 'hydrolases'),
        ('deformylase', 'hydrolases'),
        ('dehalogenase', 'hydrolases'),
        ('esterase', 'hydrolases'),
        ('glucanase', 'hydrolases'),
        ('glucosidase', 'hydrolases'),
        ('hydrolase', 'hydrolases'),
        ('peptidase', 'hydrolases'),
        ('phosphatase', 'hydrolases'),
        ('protease', 'hydrolases'),
        ('isomerase', 'isomerases'),
        ('mutase', 'isomerases'),
        ('racemase', 'isomerases'),
        ('permease', 'translocases'),
        ('transporter', 'translocases')
    ) val ( name, parent_name )
LEFT JOIN antismash.functional_classes f ON val.parent_name = f.name
ON CONFLICT DO NOTHING;