aboutsummaryrefslogtreecommitdiff
path: root/migrations/auth
diff options
context:
space:
mode:
authorFrederick Muriuki Muriithi2023-02-21 16:21:23 +0300
committerFrederick Muriuki Muriithi2023-02-21 16:24:30 +0300
commita5f83724d3075680e6d200086e3400ac938cc525 (patch)
tree4bc983fef56554708b333dcbbafe78ea7a6dcd1e /migrations/auth
parent08e8e36e256d1893967c98b366395279b39c1e72 (diff)
downloadgenenetwork3-a5f83724d3075680e6d200086e3400ac938cc525.tar.gz
auth: resources: Attach resource data and list unlinked data.
Load the data that is attached to a particular resource together with the resource. List any unlinked data: useful when linking data to resources.
Diffstat (limited to 'migrations/auth')
-rw-r--r--migrations/auth/20230216_01_dgWjv-create-linked-group-data-table.py10
-rw-r--r--migrations/auth/20230216_02_0ZHSl-make-dataset-id-and-trait-id-foreign-keys-in-tables.py57
2 files changed, 45 insertions, 22 deletions
diff --git a/migrations/auth/20230216_01_dgWjv-create-linked-group-data-table.py b/migrations/auth/20230216_01_dgWjv-create-linked-group-data-table.py
index b54942c..890eeef 100644
--- a/migrations/auth/20230216_01_dgWjv-create-linked-group-data-table.py
+++ b/migrations/auth/20230216_01_dgWjv-create-linked-group-data-table.py
@@ -11,13 +11,15 @@ steps = [
"""
CREATE TABLE IF NOT EXISTS linked_group_data(
group_id TEXT NOT NULL,
+ dataset_type TEXT NOT NULL,
dataset_or_trait_id TEXT NOT NULL,
- name TEXT NOT NULL,
- type TEXT NOT NULL,
- PRIMARY KEY(group_id, dataset_or_trait_id),
+ dataset_name TEXT NOT NULL,
+ dataset_fullname TEXT NOT NULL,
+ accession_id TEXT DEFAULT NULL,
+ PRIMARY KEY(group_id, dataset_type, dataset_or_trait_id),
FOREIGN KEY (group_id) REFERENCES groups(group_id)
ON UPDATE CASCADE ON DELETE RESTRICT,
- CHECK (type IN ('mRNA', 'Genotype', 'Phenotype'))
+ CHECK (dataset_type IN ('mRNA', 'Genotype', 'Phenotype'))
) WITHOUT ROWID
""",
"DROP TABLE IF EXISTS linked_group_data")
diff --git a/migrations/auth/20230216_02_0ZHSl-make-dataset-id-and-trait-id-foreign-keys-in-tables.py b/migrations/auth/20230216_02_0ZHSl-make-dataset-id-and-trait-id-foreign-keys-in-tables.py
index d6dc4a9..b8a57fc 100644
--- a/migrations/auth/20230216_02_0ZHSl-make-dataset-id-and-trait-id-foreign-keys-in-tables.py
+++ b/migrations/auth/20230216_02_0ZHSl-make-dataset-id-and-trait-id-foreign-keys-in-tables.py
@@ -26,20 +26,25 @@ def add_foreign_key_to_mrna_resources(conn):
CREATE TABLE IF NOT EXISTS mrna_resources(
group_id TEXT NOT NULL,
resource_id TEXT PRIMARY KEY,
+ dataset_type TEXT NOT NULL DEFAULT "mRNA"
+ CHECK (dataset_type="mRNA"),
dataset_id TEXT NOT NULL UNIQUE,
FOREIGN KEY(group_id, resource_id)
REFERENCES resources(group_id, resource_id)
ON UPDATE CASCADE ON DELETE RESTRICT,
- FOREIGN KEY (dataset_id)
- REFERENCES linked_group_data(dataset_or_trait_id)
+ FOREIGN KEY (group_id, dataset_type, dataset_id)
+ REFERENCES
+ linked_group_data(group_id, dataset_type, dataset_or_trait_id)
ON UPDATE CASCADE ON DELETE CASCADE
) WITHOUT ROWID
""")
cursor.execute(
"SELECT group_id, resource_id, dataset_id FROM mrna_resources_bkp")
rows = ((row[0], row[1], row[2]) for row in cursor.fetchall())
- cursor.executemany("INSERT INTO mrna_resources VALUES (?, ?, ?)",
- rows)
+ cursor.executemany(
+ "INSERT INTO mrna_resources(group_id, resource_id, dataset_id) "
+ "VALUES (?, ?, ?)",
+ rows)
cursor.execute("DROP TABLE mrna_resources_bkp")
def drop_foreign_key_from_mrna_resources(conn):
@@ -61,8 +66,10 @@ def drop_foreign_key_from_mrna_resources(conn):
cursor.execute(
"SELECT group_id, resource_id, dataset_id FROM mrna_resources_bkp")
rows = ((row[0], row[1], row[2]) for row in cursor.fetchall())
- cursor.executemany("INSERT INTO mrna_resources VALUES (?, ?, ?)",
- rows)
+ cursor.executemany(
+ "INSERT INTO mrna_resources(group_id, resource_id, dataset_id) "
+ "VALUES (?, ?, ?)",
+ rows)
cursor.execute("DROP TABLE mrna_resources_bkp")
def add_foreign_key_to_geno_resources(conn):
@@ -75,12 +82,15 @@ def add_foreign_key_to_geno_resources(conn):
CREATE TABLE IF NOT EXISTS genotype_resources(
group_id TEXT NOT NULL,
resource_id TEXT PRIMARY KEY,
+ dataset_type TEXT NOT NULL DEFAULT "Genotype"
+ CHECK (dataset_type="Genotype"),
trait_id TEXT NOT NULL UNIQUE,
FOREIGN KEY(group_id, resource_id)
REFERENCES resources(group_id, resource_id)
ON UPDATE CASCADE ON DELETE RESTRICT,
- FOREIGN KEY (trait_id)
- REFERENCES linked_group_data(dataset_or_trait_id)
+ FOREIGN KEY (group_id, dataset_type, trait_id)
+ REFERENCES
+ linked_group_data(group_id, dataset_type, dataset_or_trait_id)
ON UPDATE CASCADE ON DELETE CASCADE
) WITHOUT ROWID
""")
@@ -88,8 +98,10 @@ def add_foreign_key_to_geno_resources(conn):
"SELECT group_id, resource_id, trait_id "
"FROM genotype_resources_bkp")
rows = ((row[0], row[1], row[2]) for row in cursor.fetchall())
- cursor.executemany("INSERT INTO genotype_resources VALUES (?, ?, ?)",
- rows)
+ cursor.executemany(
+ "INSERT INTO genotype_resources(group_id, resource_id, trait_id) "
+ "VALUES (?, ?, ?)",
+ rows)
cursor.execute("DROP TABLE genotype_resources_bkp")
def drop_foreign_key_from_geno_resources(conn):
@@ -112,8 +124,10 @@ def drop_foreign_key_from_geno_resources(conn):
"SELECT group_id, resource_id, trait_id "
"FROM genotype_resources_bkp")
rows = ((row[0], row[1], row[2]) for row in cursor.fetchall())
- cursor.executemany("INSERT INTO genotype_resources VALUES (?, ?, ?)",
- rows)
+ cursor.executemany(
+ "INSERT INTO genotype_resources(group_id, resource_id, trait_id) "
+ "VALUES (?, ?, ?)",
+ rows)
cursor.execute("DROP TABLE genotype_resources_bkp")
def add_foreign_key_to_pheno_resources(conn):
@@ -126,12 +140,15 @@ def add_foreign_key_to_pheno_resources(conn):
CREATE TABLE IF NOT EXISTS phenotype_resources(
group_id TEXT NOT NULL,
resource_id TEXT PRIMARY KEY,
+ dataset_type TEXT NOT NULL DEFAULT "Phenotype"
+ CHECK (dataset_type="Phenotype"),
trait_id TEXT NOT NULL UNIQUE,
FOREIGN KEY(group_id, resource_id)
REFERENCES resources(group_id, resource_id)
ON UPDATE CASCADE ON DELETE RESTRICT,
- FOREIGN KEY (trait_id)
- REFERENCES linked_group_data(dataset_or_trait_id)
+ FOREIGN KEY (group_id, dataset_type, trait_id)
+ REFERENCES
+ linked_group_data(group_id, dataset_type, dataset_or_trait_id)
ON UPDATE CASCADE ON DELETE CASCADE
) WITHOUT ROWID
""")
@@ -139,8 +156,10 @@ def add_foreign_key_to_pheno_resources(conn):
"SELECT group_id, resource_id, trait_id "
"FROM phenotype_resources_bkp")
rows = ((row[0], row[1], row[2]) for row in cursor.fetchall())
- cursor.executemany("INSERT INTO phenotype_resources VALUES (?, ?, ?)",
- rows)
+ cursor.executemany(
+ "INSERT INTO phenotype_resources(group_id, resource_id, trait_id) "
+ "VALUES (?, ?, ?)",
+ rows)
cursor.execute("DROP TABLE phenotype_resources_bkp")
def drop_foreign_key_from_pheno_resources(conn):
@@ -163,8 +182,10 @@ def drop_foreign_key_from_pheno_resources(conn):
"SELECT group_id, resource_id, trait_id "
"FROM phenotype_resources_bkp")
rows = ((row[0], row[1], row[2]) for row in cursor.fetchall())
- cursor.executemany("INSERT INTO phenotype_resources VALUES (?, ?, ?)",
- rows)
+ cursor.executemany(
+ "INSERT INTO phenotype_resources(group_id, resource_id, trait_id) "
+ "VALUES (?, ?, ?)",
+ rows)
cursor.execute("DROP TABLE phenotype_resources_bkp")
from yoyo import step