DROP TABLE IF EXISTS s_present; DROP TABLE IF EXISTS rsc_present; DROP TABLE IF EXISTS x_not_present; DROP TABLE IF EXISTS x_present; DROP TABLE IF EXISTS g_present; DROP TABLE IF EXISTS r_present; DROP TABLE IF EXISTS r_uncat; DROP TABLE IF EXISTS rsc_tmp; DROP TABLE IF EXISTS rsc_new; CREATE TABLE g_present AS SELECT link_sg.s_id FROM link_sg, gene_list WHERE gene_list.gi_feature = link_sg.g_id; CREATE TABLE x_not_present AS SELECT DISTINCT link_sx.x_id FROM link_sx LEFT JOIN g_present ON link_sx.s_id = g_present.s_id WHERE g_present.s_id IS NULL; CREATE TABLE x_present AS SELECT dat_x.x_id FROM dat_x LEFT JOIN x_not_present ON dat_x.x_id = x_not_present.x_id WHERE x_not_present.x_id IS NULL; CREATE TABLE r_uncat AS SELECT dat_reactions.r_id FROM dat_Reactions LEFT JOIN link_rx ON dat_reactions.r_id = link_rx.r_id WHERE link_rx.r_id IS NULL; CREATE TABLE r_present AS SELECT DISTINCT dat_Reactions.sbml_id, dat_Reactions.r_id, dat_Reactions.reversible FROM dat_reactions, x_present, link_rx WHERE dat_Reactions.r_id = link_rx.r_id AND x_present.x_id = link_rx.x_id UNION SELECT dat_Reactions.sbml_id, dat_Reactions.r_id, dat_Reactions.reversible FROM dat_reactions, r_uncat WHERE r_uncat.r_id = dat_reactions.r_id ORDER BY sbml_id; ALTER TABLE r_present ADD id_m INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id_m); CREATE TABLE rsc_present AS SELECT r_present.r_id, s_id, c_id, stoichiometry FROM link_rsc, r_present WHERE link_rsc.r_id = r_present.r_id; CREATE TABLE s_present AS SELECT DISTINCT dat_species.sbml_id, dat_species.s_id, c_id FROM rsc_present, r_present, dat_species WHERE rsc_present.r_id = r_present.r_id AND dat_species.s_id = rsc_present.s_id ORDER BY c_id DESC, s_id; ALTER TABLE s_present ADD id_m INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id_m); CREATE TABLE rsc_tmp AS SELECT r_present.id_m AS r_id_m, s_id, c_id, stoichiometry FROM r_present LEFT JOIN rsc_present ON r_present.r_id = rsc_present.r_id WHERE rsc_present.stoichiometry IS NOT NULL; CREATE TABLE rsc_new AS SELECT r_id_m, s_present.id_m AS s_id_m, stoichiometry FROM s_present LEFT JOIN rsc_tmp ON s_present.s_id = rsc_tmp.s_id AND s_present.c_id = rsc_tmp.c_id WHERE rsc_tmp.stoichiometry IS NOT NULL;