V2 Manifest ‐ Schema - CameronD73/529renew GitHub Wiki

tester ID (idalias)

CREATE TABLE IF NOT EXISTS "idalias" (
	"id_1"	INTEGER NOT NULL,
	"id_2"	INTEGER NOT NULL,
	"name"	TEXT NOT NULL,
	"date"	TEXT NOT NULL,
	"company_id"	INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS "idalias_ids" ON "idalias" (
	"id_1",
	"id_2"
);
CREATE UNIQUE INDEX IF NOT EXISTS "idalias_ids_names" ON "idalias" (
	"id_1",
	"id_2",
	"name",
	"company_id"
);

segment pair (ibdsegs)

CREATE TABLE IF NOT EXISTS "ibdsegs" (
	"id1_1"	INTEGER NOT NULL,
	"id1_2"	INTEGER NOT NULL,
	"id2_1"	INTEGER NOT NULL,
	"id2_2"	INTEGER NOT NULL,
	"chromosome"	INTEGER NOT NULL,
	"start"	INTEGER NOT NULL,
	"end"	INTEGER NOT NULL,
	"centimorgans"	REAL NOT NULL,
	"snps"	INTEGER NOT NULL,
	"phase1"	INTEGER,
	"relationship1"	TEXT,
	"phase2"	INTEGER,
	"relationship2"	TEXT,
	"date"	TEXT NOT NULL,
	"comment"	TEXT,
	"build"	INTEGER NOT NULL DEFAULT 36
);


CREATE INDEX IF NOT EXISTS "ibdsegs_id1" ON "ibdsegs" (
	"id1_1",
	"id1_2"
);
CREATE INDEX IF NOT EXISTS "ibdsegs_id2" ON "ibdsegs" (
	"id2_1",
	"id2_2"
);
CREATE INDEX IF NOT EXISTS "ibdsegs_ids" ON "ibdsegs" (
	"id1_1",
	"id1_2",
	"id2_1",
	"id2_2"
);
CREATE INDEX IF NOT EXISTS "ibssegs_chromosomes" ON "ibdsegs" (
	"chromosome"
);
CREATE INDEX IF NOT EXISTS "ibssegs_chromosomes_id1" ON "ibdsegs" (
	"id1_1",
	"id1_2",
	"chromosome"
);
CREATE INDEX IF NOT EXISTS "ibssegs_chromosomes_id2" ON "ibdsegs" (
	"id2_1",
	"id2_2",
	"chromosome"
);
CREATE UNIQUE INDEX IF NOT EXISTS "ibssegs_ids_chromosomes_segs" ON "ibdsegs" (
	"id1_1",
	"id1_2",
	"id2_1",
	"id2_2",
	"chromosome",
	"start",
	"end",
	"build"
);