CREATE TABLE family_set (
       fam_set_id       serial,
       name             text NOT NULL,
       description      text NOT NULL,
       PRIMARY KEY (fam_set_id),
       UNIQUE (name, description)
);

CREATE TABLE family (
        family_id       serial,
        name            text NOT NULL,
        abbrev          text NOT NULL,
        description     text,
        PRIMARY KEY (family_id)
);
-- case insensitive unique
CREATE UNIQUE INDEX family_abbrev_unique on family (lower(abbrev)); 

CREATE TABLE family_member (
       seq_id           integer NOT NULL REFERENCES prot_seq ON DELETE RESTRICT,
       fam_set_id       integer NOT NULL REFERENCES family_set ON DELETE RESTRICT,
       family_id        integer NOT NULL REFERENCES family ON DELETE RESTRICT,
       PRIMARY KEY (seq_id, fam_set_id)
);


INSERT INTO family_set
(name, description)
VALUES
('plos_20', 'Original 20 families in PLoS paper, indexed to Swissprot 50.1'),
('ppod_20', 'Original 20 families, indexed to PPOD (5 May 2009).  *Likely to contain some false negatives*.'),
('hgnc_apr10', 'All downloaded HGNC families, indexed to PPOD (5 May 2009) complete dataset'),
('hgnc_apr10_selected', 'Selected HGNC families, indexed to PPOD (5 May 2009) complete dataset');

INSERT INTO family 
(family_name, family_abbrev)
VALUES
('Acyl-CoA Synthetase Long-Chain', 'ACSL'),
('A Disintegrin and Metalloprotease', 'ADAM'),
('Dischevelled', 'DVL'),
('Fibroblast Growth Factor','FGF'),
('Forkhead Box', 'FOX'),
('GATA Binding Protein', 'GATA'),
('Tyrosine Kinase', 'TYK'),
('Atypical Kinase', 'AK'),
('Kinase', 'Kinase'),
('Kinesin', 'Kinesin'),
('Killer Cell Immunoglobulin-like Receptor', 'KIR'),
('Laminin', 'Laminin'),
('Myosin', 'Myosin'),
('Notch', 'Notch'),
('Phosphodiesterases', 'PDE'),
('Semaphoin', 'SEMA'),
('T-Box', 'Tbox'),
('Tumor Necrosis Factor', 'TNF'),
('Tumor Necrosis Factor Receptor', 'TNFR'),
('Tumor Necrosis Factor Receptor Associated Factor', 'TRAF'),
('Ubiquitin Specific Protease', 'USP'),
('Wingless-related MMTV Integration Site', 'WNT')


-- T-box, from hgnc, using swissprot ids
-- MGA is only in trembl
INSERT INTO family_member
SELECT seq_id, (SELECT family_id from family where family_abbrev='Tbx')
FROM hgnc_20070521
JOIN prot_seq_version ON (primary_acc=swissprot_id_mapped)
JOIN prot_seq_source_ver USING (source_ver_id)
JOIN prot_seq_source USING (source_id)
WHERE source_name='swissprot'
AND version='50.9'
AND gene_family_name like '%TBX%'



-- Tyrosine Kinase
SELECT seq_id, (SELECT family_id from family where family_abbrev='TYK')
FROM prot_seq_set_member
JOIN sp_cross_ref USING (seq_id)
JOIN prot_seq_source ON (sp_cross_ref.source_id=prot_seq_source.source_id)
WHERE set_id=79
AND source_name='Pfam'
AND (id_0 = 'PF07714');

-- Typical Kinase
SELECT seq_id, (SELECT family_id from family where family_abbrev='Kinase')
FROM prot_seq_set_member
JOIN sp_cross_ref USING (seq_id)
JOIN prot_seq_source ON (sp_cross_ref.source_id=prot_seq_source.source_id)
WHERE set_id=79
AND source_name='Pfam'
AND (id_0 = 'PF07714'
OR id_0 = 'PF00069')

-- Atypical Kinase
SELECT seq_id, (SELECT family_id from family where family_abbrev='AK')
FROM prot_seq_set_member
JOIN sp_cross_ref USING (seq_id)
JOIN prot_seq_source ON (sp_cross_ref.source_id=prot_seq_source.source_id)
WHERE set_id=79
AND source_name='Pfam'
AND (id_0 = 'PF07714'
OR id_0 = 'PF00069'
OR id_0 = 'PF06293'
OR id_0 = 'PF03881'
OR id_0 = 'PF02958'
OR id_0 = 'PF07914'
OR id_0 = 'PF01633'
OR id_0 = 'PF04655'
OR id_0 = 'PF01636'
OR id_0 = 'PF03109'
OR id_0 = 'PF05445'
OR id_0 = 'PF01163'
OR id_0 = 'PF06176')

