add gcrma data and affymetrix probe annotation loaders

[dbsnp.git] / schema / extra_schema / gcrma_table.sql

diff --git a/schema/extra_schema/gcrma_table.sql b/schema/extra_schema/gcrma_table.sql
new file mode 100644 (file)
index 0000000..d76383c
--- /dev/null
+++ b/schema/extra_schema/gcrma_table.sql
@@ -0,0 +1,46 @@
+DROP TABLE affy_annotation;
+DROP TABLE gcrma_expression;
+DROP TABLE affy_probe;
+DROP TABLE gcrma_samples;
+
+CREATE TABLE gcrma_samples (
+       id SERIAL NOT NULL, -- PRIMARY KEY,
+       tissue TEXT NOT NULL
+);
+
+CREATE TABLE affy_probe (
+      id SERIAL NOT NULL, -- PRIMARY KEY,
+      probe TEXT NOT NULL
+);
+
+CREATE TABLE affy_annotation (
+       id SERIAL NOT NULL, -- PRIMARY KEY,
+       probe INT NOT NULL,-- REFERENCES affy_probe,
+       gene_symbol TEXT,
+       gene_name TEXT,
+       species TEXT,
+       array_name TEXT,
+       entrez_id TEXT,
+       refseq_prot TEXT,
+       refseq_transcript TEXT
+);
+
+CREATE TABLE gcrma_expression (
+       id SERIAL NOT NULL, -- PRIMARY KEY,
+       probe INT NOT NULL,-- REFERENCES affy_probe, 
+       sample INT NOT NULL,-- REFERENCES gcrma_samples,
+       expression FLOAT NOT NULL
+);
+
+CREATE VIEW gcrma AS 
+       SELECT ge.id AS id,
+       gs.tissue AS tissue,
+       ap.probe AS probe,
+       ge.expression AS expression,
+       aa.gene_symbol AS symbol
+       FROM gcrma_expression ge 
+       JOIN affy_probe ap ON ge.probe=ap.id
+       JOIN gcrma_samples gs ON ge.sample=gs.id
+       LEFT OUTER JOIN affy_annotation aa ON ap.id=aa.probe;
+       
+