add routines to load encode data

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

diff --git a/schema/extra_schema/encode_schema.sql b/schema/extra_schema/encode_schema.sql
new file mode 100644 (file)
index 0000000..f636e24
--- /dev/null
+++ b/schema/extra_schema/encode_schema.sql
@@ -0,0 +1,39 @@
+DROP TABLE encdoe_track_info CASCADE;
+DROP TABLE encode_tf_binding_tracks CASCADE;
+DROP TABLE encode_tracks CASCADE;
+DROP TABLE encode_track_info_keys CASCADE;
+
+CREATE TABLE encode_tracks (
+       id SERIAL PRIMARY KEY,
+       track_name TEXT UNIQUE
+);
+
+CREATE TABLE encode_track_info_keys (
+       id SERIAL PRIMARY KEY,
+       info_key TEXT UNIQUE
+);
+
+CREATE TABLE encode_track_info (
+       id SERIAL PRIMARY KEY,
+       track_id INT NOT NULL REFERENCES encode_tracks,
+       info_key_id INT NOT NULL REFERENCES encode_track_info_keys,
+       info_value TEXT NOT NULL
+);
+
+CREATE UNIQUE INDEX ON encode_track_info(track_id,info_key_id);
+CREATE INDEX ON encode_track_info(info_key_id,info_value);
+
+CREATE TABLE encode_tf_binding_tracks (
+       id SERIAL PRIMARY KEY,
+       track_id INT NOT NULL REFERENCES encode_tracks,
+       chr TEXT NOT NULL,
+       start INT,
+       stop INT,
+       name TEXT NOT NULL DEFAULT '',
+       score INT NOT NULL,
+       strand TEXT NOT NULL DEFAULT '.',
+       signalValue FLOAT,
+       pValue FLOAT,
+       qValue FLOAT,
+       peak INT
+);