tripal_chado.chado_vx_x.inc 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522
  1. <?php
  2. function tripal_chado_add_tripal_gff_temp_table() {
  3. $schema = array(
  4. 'table' => 'tripal_gff_temp',
  5. 'fields' => array(
  6. 'feature_id' => array(
  7. 'type' => 'int',
  8. 'not null' => TRUE,
  9. ),
  10. 'organism_id' => array(
  11. 'type' => 'int',
  12. 'not null' => TRUE,
  13. ),
  14. 'uniquename' => array(
  15. 'type' => 'text',
  16. 'not null' => TRUE,
  17. ),
  18. 'type_name' => array(
  19. 'type' => 'varchar',
  20. 'length' => '1024',
  21. 'not null' => TRUE,
  22. ),
  23. ),
  24. 'indexes' => array(
  25. 'tripal_gff_temp_idx0' => array('feature_id'),
  26. 'tripal_gff_temp_idx0' => array('organism_id'),
  27. 'tripal_gff_temp_idx1' => array('uniquename'),
  28. ),
  29. 'unique keys' => array(
  30. 'tripal_gff_temp_uq0' => array('feature_id'),
  31. 'tripal_gff_temp_uq1' => array('uniquename', 'organism_id', 'type_name'),
  32. ),
  33. );
  34. chado_create_custom_table('tripal_gff_temp', $schema, TRUE, NULL, FALSE);
  35. }
  36. /**
  37. *
  38. */
  39. function tripal_chado_add_tripal_gffcds_temp_table() {
  40. $schema = array(
  41. 'table' => 'tripal_gffcds_temp',
  42. 'fields' => array(
  43. 'feature_id' => array(
  44. 'type' => 'int',
  45. 'not null' => TRUE,
  46. ),
  47. 'parent_id' => array(
  48. 'type' => 'int',
  49. 'not null' => TRUE,
  50. ),
  51. 'phase' => array(
  52. 'type' => 'int',
  53. 'not null' => FALSE,
  54. ),
  55. 'strand' => array(
  56. 'type' => 'int',
  57. 'not null' => TRUE,
  58. ),
  59. 'fmin' => array(
  60. 'type' => 'int',
  61. 'not null' => TRUE,
  62. ),
  63. 'fmax' => array(
  64. 'type' => 'int',
  65. 'not null' => TRUE,
  66. ),
  67. ),
  68. 'indexes' => array(
  69. 'tripal_gff_temp_idx0' => array('feature_id'),
  70. 'tripal_gff_temp_idx0' => array('parent_id'),
  71. ),
  72. );
  73. chado_create_custom_table('tripal_gffcds_temp', $schema, TRUE, NULL, FALSE);
  74. }
  75. /**
  76. *
  77. */
  78. function tripal_chado_add_tripal_gffprotein_temp_table() {
  79. $schema = array(
  80. 'table' => 'tripal_gffprotein_temp',
  81. 'fields' => array(
  82. 'feature_id' => array(
  83. 'type' => 'int',
  84. 'not null' => TRUE,
  85. ),
  86. 'parent_id' => array(
  87. 'type' => 'int',
  88. 'not null' => TRUE,
  89. ),
  90. 'fmin' => array(
  91. 'type' => 'int',
  92. 'not null' => TRUE,
  93. ),
  94. 'fmax' => array(
  95. 'type' => 'int',
  96. 'not null' => TRUE,
  97. ),
  98. ),
  99. 'indexes' => array(
  100. 'tripal_gff_temp_idx0' => array('feature_id'),
  101. 'tripal_gff_temp_idx0' => array('parent_id'),
  102. ),
  103. 'unique keys' => array(
  104. 'tripal_gff_temp_uq0' => array('feature_id'),
  105. ),
  106. );
  107. chado_create_custom_table('tripal_gffprotein_temp', $schema, TRUE, NULL, FALSE);
  108. }
  109. /**
  110. * Creates a temporary table to store obo details while loading an obo file
  111. *
  112. */
  113. function tripal_chado_add_tripal_obo_temp_table() {
  114. // the tripal_obo_temp table is used for temporary housing of records when loading OBO files
  115. // we create it here using plain SQL because we want it to be in the chado schema but we
  116. // do not want to use the Tripal Custom Table API because we don't want it to appear in the
  117. // list of custom tables. It needs to be available for the Tripal Chado API so we create it
  118. // here and then define it in the tripal_cv/api/tripal_cv.schema.api.inc
  119. if (!chado_table_exists('tripal_obo_temp')) {
  120. $sql = "
  121. CREATE TABLE {tripal_obo_temp} (
  122. id character varying(255) NOT NULL,
  123. stanza text NOT NULL,
  124. type character varying(50) NOT NULL,
  125. CONSTRAINT tripal_obo_temp_uq0 UNIQUE (id)
  126. );
  127. ";
  128. chado_query($sql);
  129. $sql = "CREATE INDEX tripal_obo_temp_idx0 ON {tripal_obo_temp} USING btree (id)";
  130. chado_query($sql);
  131. $sql = "CREATE INDEX tripal_obo_temp_idx1 ON {tripal_obo_temp} USING btree (type)";
  132. chado_query($sql);
  133. }
  134. }
  135. /**
  136. * Creates a materialized view that stores the type & number of stocks per organism
  137. *
  138. * @ingroup tripal_stock
  139. */
  140. function tripal_chado_add_organism_stock_count_mview() {
  141. $view_name = 'organism_stock_count';
  142. $comment = 'Stores the type and number of stocks per organism';
  143. $schema = array(
  144. 'description' => $comment,
  145. 'table' => $view_name,
  146. 'fields' => array(
  147. 'organism_id' => array(
  148. 'size' => 'big',
  149. 'type' => 'int',
  150. 'not null' => TRUE,
  151. ),
  152. 'genus' => array(
  153. 'type' => 'varchar',
  154. 'length' => '255',
  155. 'not null' => TRUE,
  156. ),
  157. 'species' => array(
  158. 'type' => 'varchar',
  159. 'length' => '255',
  160. 'not null' => TRUE,
  161. ),
  162. 'common_name' => array(
  163. 'type' => 'varchar',
  164. 'length' => '255',
  165. 'not null' => FALSE,
  166. ),
  167. 'num_stocks' => array(
  168. 'type' => 'int',
  169. 'not null' => TRUE,
  170. ),
  171. 'cvterm_id' => array(
  172. 'size' => 'big',
  173. 'type' => 'int',
  174. 'not null' => TRUE,
  175. ),
  176. 'stock_type' => array(
  177. 'type' => 'varchar',
  178. 'length' => '255',
  179. 'not null' => TRUE,
  180. ),
  181. ),
  182. 'indexes' => array(
  183. 'organism_stock_count_idx1' => array('organism_id'),
  184. 'organism_stock_count_idx2' => array('cvterm_id'),
  185. 'organism_stock_count_idx3' => array('stock_type'),
  186. ),
  187. );
  188. $sql = "
  189. SELECT
  190. O.organism_id, O.genus, O.species, O.common_name,
  191. count(S.stock_id) as num_stocks,
  192. CVT.cvterm_id, CVT.name as stock_type
  193. FROM organism O
  194. INNER JOIN stock S ON O.Organism_id = S.organism_id
  195. INNER JOIN cvterm CVT ON S.type_id = CVT.cvterm_id
  196. GROUP BY
  197. O.Organism_id, O.genus, O.species, O.common_name, CVT.cvterm_id, CVT.name
  198. ";
  199. chado_add_mview($view_name, 'tripal_stock', $schema, $sql, $comment, FALSE);
  200. }
  201. /**
  202. * Adds a materialized view keeping track of the type of features associated with each library
  203. *
  204. * @ingroup tripal_library
  205. */
  206. function tripal_chado_add_library_feature_count_mview(){
  207. $view_name = 'library_feature_count';
  208. $comment = 'Provides count of feature by type that are associated with all libraries';
  209. $schema = array(
  210. 'table' => $view_name,
  211. 'description' => $comment,
  212. 'fields' => array(
  213. 'library_id' => array(
  214. 'size' => 'big',
  215. 'type' => 'int',
  216. 'not null' => TRUE,
  217. ),
  218. 'name' => array(
  219. 'type' => 'varchar',
  220. 'length' => 255,
  221. 'not null' => TRUE,
  222. ),
  223. 'num_features' => array(
  224. 'type' => 'int',
  225. 'not null' => TRUE,
  226. ),
  227. 'feature_type' => array(
  228. 'type' => 'varchar',
  229. 'length' => 255,
  230. 'not null' => TRUE,
  231. ),
  232. ),
  233. 'indexes' => array(
  234. 'library_feature_count_idx1' => array('library_id'),
  235. ),
  236. );
  237. $sql = "
  238. SELECT
  239. L.library_id, L.name,
  240. count(F.feature_id) as num_features,
  241. CVT.name as feature_type
  242. FROM library L
  243. INNER JOIN library_feature LF ON LF.library_id = L.library_id
  244. INNER JOIN feature F ON LF.feature_id = F.feature_id
  245. INNER JOIN cvterm CVT ON F.type_id = CVT.cvterm_id
  246. GROUP BY L.library_id, L.name, CVT.name
  247. ";
  248. chado_add_mview($view_name, 'tripal_library', $schema, $sql, $comment, FALSE);
  249. }
  250. /**
  251. *
  252. */
  253. /**
  254. * Creates a materialized view that stores the type & number of features per organism
  255. *
  256. * @ingroup tripal_feature
  257. */
  258. function tripal_chado_add_organism_feature_count_mview() {
  259. $view_name = 'organism_feature_count';
  260. $comment = 'Stores the type and number of features per organism';
  261. $schema = array(
  262. 'description' => $comment,
  263. 'table' => $view_name,
  264. 'fields' => array(
  265. 'organism_id' => array(
  266. 'size' => 'big',
  267. 'type' => 'int',
  268. 'not null' => TRUE,
  269. ),
  270. 'genus' => array(
  271. 'type' => 'varchar',
  272. 'length' => '255',
  273. 'not null' => TRUE,
  274. ),
  275. 'species' => array(
  276. 'type' => 'varchar',
  277. 'length' => '255',
  278. 'not null' => TRUE,
  279. ),
  280. 'common_name' => array(
  281. 'type' => 'varchar',
  282. 'length' => '255',
  283. 'not null' => FALSE,
  284. ),
  285. 'num_features' => array(
  286. 'type' => 'int',
  287. 'not null' => TRUE,
  288. ),
  289. 'cvterm_id' => array(
  290. 'size' => 'big',
  291. 'type' => 'int',
  292. 'not null' => TRUE,
  293. ),
  294. 'feature_type' => array(
  295. 'type' => 'varchar',
  296. 'length' => '255',
  297. 'not null' => TRUE,
  298. ),
  299. ),
  300. 'indexes' => array(
  301. 'organism_feature_count_idx1' => array('organism_id'),
  302. 'organism_feature_count_idx2' => array('cvterm_id'),
  303. 'organism_feature_count_idx3' => array('feature_type'),
  304. ),
  305. );
  306. $sql = "
  307. SELECT
  308. O.organism_id, O.genus, O.species, O.common_name,
  309. count(F.feature_id) as num_features,
  310. CVT.cvterm_id, CVT.name as feature_type
  311. FROM organism O
  312. INNER JOIN feature F ON O.Organism_id = F.organism_id
  313. INNER JOIN cvterm CVT ON F.type_id = CVT.cvterm_id
  314. GROUP BY
  315. O.Organism_id, O.genus, O.species, O.common_name, CVT.cvterm_id, CVT.name
  316. ";
  317. chado_add_mview($view_name, 'tripal_feature', $schema, $sql, $comment, FALSE);
  318. }
  319. /**
  320. * Creates a view showing the link between an organism & it's analysis through associated features.
  321. *
  322. */
  323. function tripal_chado_add_analysis_organism_mview() {
  324. $view_name = 'analysis_organism';
  325. $comment = t('This view is for associating an organism (via it\'s associated features) to an analysis.');
  326. // this is the SQL used to identify the organism to which an analsysis
  327. // has been used. This is obtained though the analysisfeature -> feature -> organism
  328. // joins
  329. $sql = "
  330. SELECT DISTINCT A.analysis_id, O.organism_id
  331. FROM analysis A
  332. INNER JOIN analysisfeature AF ON A.analysis_id = AF.analysis_id
  333. INNER JOIN feature F ON AF.feature_id = F.feature_id
  334. INNER JOIN organism O ON O.organism_id = F.organism_id
  335. ";
  336. // the schema array for describing this view
  337. $schema = array(
  338. 'table' => $view_name,
  339. 'description' => $comment,
  340. 'fields' => array(
  341. 'analysis_id' => array(
  342. 'size' => 'big',
  343. 'type' => 'int',
  344. 'not null' => TRUE,
  345. ),
  346. 'organism_id' => array(
  347. 'size' => 'big',
  348. 'type' => 'int',
  349. 'not null' => TRUE,
  350. ),
  351. ),
  352. 'indexes' => array(
  353. 'networkmod_qtl_indx0' => array('analysis_id'),
  354. 'networkmod_qtl_indx1' => array('organism_id'),
  355. ),
  356. 'foreign keys' => array(
  357. 'analysis' => array(
  358. 'table' => 'analysis',
  359. 'columns' => array(
  360. 'analysis_id' => 'analysis_id',
  361. ),
  362. ),
  363. 'organism' => array(
  364. 'table' => 'organism',
  365. 'columns' => array(
  366. 'organism_id' => 'organism_id',
  367. ),
  368. ),
  369. ),
  370. );
  371. // add the view
  372. chado_add_mview($view_name, 'tripal_analysis', $schema, $sql, $comment, FALSE);
  373. }
  374. /**
  375. * Add a materialized view that maps cv to db records.
  376. *
  377. * This is needed for viewing cv trees
  378. *
  379. */
  380. function tripal_chado_add_db2cv_mview_mview() {
  381. $mv_name = 'db2cv_mview';
  382. $comment = 'A table for quick lookup of the vocabularies and the databases they are associated with.';
  383. $schema = array(
  384. 'table' => $mv_name,
  385. 'description' => $comment,
  386. 'fields' => array (
  387. 'cv_id' => array (
  388. 'type' => 'int',
  389. 'not null' => true,
  390. ),
  391. 'cvname' => array (
  392. 'type' => 'varchar',
  393. 'length' => '255',
  394. 'not null' => true,
  395. ),
  396. 'db_id' => array (
  397. 'type' => 'int',
  398. 'not null' => true,
  399. ),
  400. 'dbname' => array (
  401. 'type' => 'varchar',
  402. 'length' => '255',
  403. 'not null' => true,
  404. ),
  405. 'num_terms' => array (
  406. 'type' => 'int',
  407. 'not null' => true,
  408. ),
  409. ),
  410. 'indexes' => array (
  411. 'cv_id_idx' => array ('cv_id'),
  412. 'cvname_idx' => array ('cvname'),
  413. 'db_id_idx' => array ('db_id'),
  414. 'dbname_idx' => array ('db_id'),
  415. ),
  416. );
  417. $sql = "
  418. SELECT DISTINCT CV.cv_id, CV.name as cvname, DB.db_id, DB.name as dbname,
  419. COUNT(CVT.cvterm_id) as num_terms
  420. FROM cv CV
  421. INNER JOIN cvterm CVT on CVT.cv_id = CV.cv_id
  422. INNER JOIN dbxref DBX on DBX.dbxref_id = CVT.dbxref_id
  423. INNER JOIN db DB on DB.db_id = DBX.db_id
  424. WHERE CVT.is_relationshiptype = 0 and CVT.is_obsolete = 0
  425. GROUP BY CV.cv_id, CV.name, DB.db_id, DB.name
  426. ORDER BY DB.name
  427. ";
  428. // Create the MView
  429. chado_add_mview($mv_name, 'tripal_chado', $schema, $sql, $comment, FALSE);
  430. }
  431. /**
  432. * Add a materialized view of root terms for all chado cvs.
  433. *
  434. * This is needed for viewing cv trees
  435. *
  436. */
  437. function tripal_chado_add_cv_root_mview_mview() {
  438. $mv_name = 'cv_root_mview';
  439. $comment = 'A list of the root terms for all controlled vocabularies. This is needed for viewing CV trees';
  440. $schema = array(
  441. 'table' => $mv_name,
  442. 'description' => $comment,
  443. 'fields' => array(
  444. 'name' => array(
  445. 'type' => 'varchar',
  446. 'length' => 255,
  447. 'not null' => TRUE,
  448. ),
  449. 'cvterm_id' => array(
  450. 'size' => 'big',
  451. 'type' => 'int',
  452. 'not null' => TRUE,
  453. ),
  454. 'cv_id' => array(
  455. 'size' => 'big',
  456. 'type' => 'int',
  457. 'not null' => TRUE,
  458. ),
  459. 'cv_name' => array(
  460. 'type' => 'varchar',
  461. 'length' => 255,
  462. 'not null' => TRUE,
  463. ),
  464. ),
  465. 'indexes' => array(
  466. 'cv_root_mview_indx1' => array('cvterm_id'),
  467. 'cv_root_mview_indx2' => array('cv_id'),
  468. ),
  469. );
  470. $sql = "
  471. SELECT DISTINCT CVT.name, CVT.cvterm_id, CV.cv_id, CV.name
  472. FROM cvterm CVT
  473. LEFT JOIN cvterm_relationship CVTR ON CVT.cvterm_id = CVTR.subject_id
  474. INNER JOIN cvterm_relationship CVTR2 ON CVT.cvterm_id = CVTR2.object_id
  475. INNER JOIN cv CV on CV.cv_id = CVT.cv_id
  476. WHERE CVTR.subject_id is NULL and
  477. CVT.is_relationshiptype = 0 and CVT.is_obsolete = 0
  478. ";
  479. // Create the MView
  480. chado_add_mview($mv_name, 'tripal_chado', $schema, $sql, $comment, FALSE);
  481. }