A Construction Toolkit For Online Biological Databases

1. Lacey-Anne Sanderson A Construction Toolkit For Online Biological Databases

2. Tripal • What is Tripal? • Features • Highlighting those in the upcoming release: Tripal 0.4 • Extensions • Those currently available and some under development • How you can contribute

3. What is Tripal? What is Tripal? Tripal Drupal Chado

4. What is Tripal? What is Tripal?(From a Biologist’s Point of View) • An open-source Biological Database that • Is easy to set up with few requirements • Lower IT Costs • Reliably stores your data without much more work than Excel Sheets • Upload data into chado completely through the web-interface • Display tables of data that are sortable, filterable and only contain the columns you care about • Facilitates sharing of data… • But only with the people you are ready to share it with

5. What is Tripal? Why Drupal? • Widely used and supported. • A flexible, expandable platform • Start with a fully functional, professional website then simply add functionality to handle Biological Data • Handles User Management & Permission Control out of the box • Searching • Taxonomy/Tags • User Comments • Contact Forms • Forums • Menu’s • User Profiles • File Management

6. What is Tripal? Why Drupal? • 100’s of “modules” to extend the functionality of your website • Drupal Views: Custom SQL queries and tables • CCK: Add your own content to any page • Panels: Customize the layout of any page • Pathauto: Create path alias’ • Wysywyg Editors • Webforms • CAPTCHA’s

7. What is Tripal? Why Drupal? • Fully Theme-able with 1000’s of themes freely available • Change the look-and-feel of your site with the click of a button

8. What is Tripal? Easy Installation • Install Drupal • Then just download Tripal and any extension modules and enable them through the Drupal site • Will be able to install a pre-configured Tripal using a Drush installation script/profile • Drush is the command-line functionality for Drupal v 0.4

9. Tripal Features Features • One-click Chado Installer • Details Pages for Main Chado Content Types • Complex listings of data • Searching of ChadoContent • Job Management • Materialized Views Support • Data Loaders • Custom Page Layouts • Developers API

10. Tripal Features Detail Pages • Webpages specific to a single record in chado • Generated based on information in chado • Allow direct editing of a single record • Permissions determine which users are allowed to edit content • Enabled for • Organisms • Features • Libraries • Analysis’ • Stocks

11. Organism Tripal Features Data from Organism table in Chado Custom content added specifically to this page Optional feature summary block added by Tripal: counts feature types in Chado.

12. Libraries Tripal Features Shows all libraries (e.g. genomic BAC, EST, FOSMID, etc) available for a species

13. Tripal Features Features Data taken from the Chado ‘feature’ table. EST’s in the contig alignment GO terms annotated to this feature. Pulled directly from Chado.

14. Tripal Features Stocks Data taken from the Chado ‘stock’ table. Properties (‘stockprop’) External Database References (‘dbxref’ <= ‘stock_dbxref’) Stock Relationships (‘stock_relationship’)

15. Tripal Features Complex Data Listing • Integration of Chado with the Drupal Views Module • Create custom SQL queries through the web-interface • Formatting of the results into a variety of formats including lists, tables, and RSS feeds • Sorting, Filtering (admin set values, user provided values and/or variables from the path) • Exporting of tables to Excel, fasta, etc. • Permissions handling

16. Tripal Features Complex Data Listing • Create custom SQL queries through the web-interface

17. Tripal Features Complex Data Listing • And produces this table

18. Tripal Features v 0.4 Custom Views Integration • All Chado tables are integrated by default • Can change field names and help text to be more specific to your site • Specify custom data handlers allowing you to customize • The way fields are displayed • The form elements used for filters (advanced searching) • New sort criteria

19. Tripal Features Searching • Full-text built-in Drupal search • Slow to index, but fast to search • Available for features, libraries, analysis’ and organisms Download FASTA file of results

20. Tripal Features Searching v 0.4 • Advanced custom searches using • Uses Drupal Views Filters • Administrators can set up their own search forms • through the web interface • to search a base: • Chado table • or Materialized view • and related tables

21. Tripal Features Searching v 0.4 • Advanced custom searches using • Example: Advanced Search for features by organism, type, and/or analysis it was generated in

22. Tripal Features Materialized Views • Can be used to greatly speed up querying of chado content by pre-joining tables • Useful for complex listings or searches

23. Tripal Features Materialized Views • Creation of custom Materialized Views • Specify fields and a query to generate • All done through the web interface

24. Tripal Features Materialized Views • Update materialized views at the click of a button • Uses the job management system

25. Tripal Features Materialized Views v 0.4 • Integrate with Drupal Views to allow for listings of data and/or advanced searching • Through the web interface • Specify field names (human-readable), help text and custom handlers • Specify relationships to chado tables

26. Tripal Features Data Loaders • Format-specific loaders • FASTA and GFF3 for loading feature data • OBO for loading ontologies

27. Tripal Features Data Loaders v 0.4 • Generic Tab-delimited Loader • Allows loading of any tab-delimited file • Can insert into multiple tables, multiple times • Create a saved loading profile that can be applied to many files

28. Tripal Features Data Loaders v 0.4 • Generic Tab-delimited Loader • Can specify in the loading profile • Chado table/field to load a given column into • Foreign key relationships between records • Constant values • for a loading profile • specific to a given file • Regular Expressions to transform data before loading

29. Tripal Features Data Loaders v 0.4 • Generic Tab-delimited Loader • For each file create a loading job • Keeps track of progress, details, number of records inserted • Allows you to enter file-specific constants

30. Tripal Features Customizable Page Layouts • Expose Chado data to panels in the form of blocks • Allows tripal administrators to arrange chado content on details pages • Decide if you want the Sequence Features page to only contain basic details and other details such as properties, relationships, annotation appear as tabs • Or combine everything onto a single page • Panels supports custom layouts with any combination of rows and columns

31. Tripal Features Customizable Page Layouts • Put content in any region you want • Panels supports custom layouts with any combination of rows and columns

32. Tripal Features The Tripal API • At the Tripal-core level: • Functions to submit and update job status for the Jobs Management system • Functions to easily add materialized views for your extensions. • Functions for adding custom CV terms needed for modules or applications • At the Chado-centric module level: • Generic Insert/Update/Delete for Chado tables • Supports ALL chado tables –not just those supported by Tripal • Pie Charts and expandable tree browser for showing features with assigned ontologies • At the Analysis module level: • Functions for registering new analysis modules • Use of Drupal hooks for integrating new analyses

33. Tripal Features Tripal API: Select/Insert/Update • tripal_core_chado_select • arraytripal_core_chado_select (string $tablename, array $columns, array $values) • tripal_core_chado_insert • arraytripal_core_chado_insert (string $tablename, array $values) • tripal_core_chado_update • arraytripal_core_chado_update (string $tablename, array $match, array $values)

34. Tripal Features Tripal API: Example Select • Usage: $columns = array( ‘feature_id’, ‘name’, ‘uniquename’ ); $values = array( ‘organism_id’ => array(‘genus’ => ‘Lens’), ‘type_id’ => array( ‘cv_id’ => array(‘name’ => ‘sequence’), ‘name’ => ‘gene’, ), ‘dbxref_id’ => array( ‘db_id’ => array(‘name’ => ‘NCBI’), ), ); $result = tripal_core_chado_select('feature',$columns,$values); • The above example, returns an array of all Lentil genes with NCBI accessions • Updates and Inserts follow a similar scheme

35. Tripal Extensions Tripal Extensions Tripal can be extended at the Application and Analysis Module layers, or where Chado-centric modules are missing. Anyone may develop Applications and Analysis modules Anyone may help with development of Chado-centric modules but in coordination with core Tripal developers.

36. Tripal Extensions Tripal Extensions • Tripal Extensions are made available through the Tripal SourceForge Site • http://tripal.sourceforge.net/?q=extensions • Some extensions coming soon include: • Breeder’s Toolbox Application • Genotype Experiments • Webservices • GMOD RPC, Tripal-specific REST • GBrowse Integration • Current release is for GBrowse 1.x

37. Tripal Extensions Tripal Extensions • Analysis Modules: • There are already modules developed for supporting the following analysis’: • BLAST • GO • Interpro • KEGG • Unigene • In version 0.2 these were include in core Tripal but have been moved to a separate Drupal Package

38. Tripal Extensions How to Contribute • Tripal is still maturing but anyone can extend it to suit their needs. • These extensions can be shared with others and can be made available by on the Tripal website: http://tripal.sourceforge.net • If you are interested in developing an extension feel free to email the mailing list: gmod-tripal@lists.sourceforge.net

39. Contributing Organizations University of Saskatchewan Lacey-Anne Sanderson Kirstin Bett, Ph.D Clemson University Genomics Institute Meg Staton, Ph.D Ontario Institute for Cancer Research GMOD Coordinator, Scott Cain, Ph.D Emory University Previous GMOD Help Desk, Dave Clements Main Bioinformatics Lab Stephen Ficklin (project lead) Chun-Huai Chen Taein Lee Dorrie Main, Ph.D Il-Hyung Cho, Ph.D. Sook Jung, Ph.D

40. Funding Sources • Development of Tripal has been supported by components of several funded projects, including: • Current Funding • Tree Fruit GDR: Translating Genomics into Advances in Horticulture: USDA Specialty Crops Research Initiative, September 2009 – August 2013. • An Integrated Web-based Relational Database for the Curation of Cacao Genetic and Genomic Data: USDA-ARS SCA, January 2009 - January 2013. • Developing an Online Toolbox for Tree Fruit Breeding: Washington Tree Fruit Research Commission, April 2009 – March 2012. • RosBREED: Enabling Marker-assisted Breeding in Rosaceae: USDA Specialty Crops Research Initiative, September 2009 – August 2013 • Genomics-Assisted Plant Breeding for Cool Season Food Legumes: University of Idaho Special Grants, USDA NIFA, May 2010 – April 2013 • Loblolly Pine Genome Sequencing: USDA DOE, January 2011-January 2016 • PURENET: Agriculture and Agri-Food Canada, May 2009 – March 2011 • iMAP: Saskatchewan Pulse Growers Association, September 2010 – September 2013 • Comparative Genomics of Environmental Stress Responses in North American Hardwoods: NSF Plant Genome Research Program, February 2011 - January 2015 • Past Funding • Genomic Tool Development for the Fagaceae, NSF Award #0605135 • Clemson University Genomics Institute (CUGI) • Clemson’s Cyberinfrastructure and Technology Integration Group (CITI)

41. Thank You! Sourceforge: http://tripal.sourceforge.net Mailing Lists: http://gmod.org/wiki/GMOD_Mailing_Lists GMOD Tripal Pages: http://gmod.org/wiki/Tripal