X2DBI – User manual - Centre de BioInformatique de Bordeaux
Contents
1. Description 1 Datasource selection list Databases Tabs for Query building Attributes and Filters for query launching for results consulting Results and for browsing the database Browse Selection list of predefined queries Selection of attributes to include in the query result columns List of selected attributes displayed in the same order than within the result table Datasets selection list for extending the general query filters to add constraints on the query query conditions GOIN n ony amp U Reset button for all the datasets and attributes Button to import a query from an XML file previously exported or manually generated 10 Button to export the query built as an XML file Calculate the number of results returned for the query The number of results is displayed in the textbox beside 12 HELP button 11 version 1 2 7 X2DBI User manual Release date 06 11 2011 Page 4 14 3 Database selection X2DBI can provide access to several databases but only one at a time So the first step is to select the database you want to query 4 Authentication The private databases are governed by access rights To can access to the private data of a database you must enter a username and password corresponding to a database account so that X2DBI can authenticate you to the chosen database Authentication Required2. The server services cbib u bordeaux2 fr 80 requires a username and password The server says Please enter your login and password User Name Password Authentication Cancel By default X2DBI provides you access to the public data of the selected database So you don t need to use the authentication form if you only want to access to the public data 5 Attributes selection Then you can select the attributes from the different datasets on to the Attributes panel in the Query tab They will be displayed in the results table as columns To ease the selection of version 1 2 7 X2DBI User manual Release date 06 11 2011 Page 5 14 attributes they are grouped in logic datasets Usually they are gathered around a central entity and following a logical process e g OMICs experiments A dataset is always displayed by default Generally it concerns the attributes describing the main entities like experimental design C Attributes samples ig C taxon id E species LIPS accession Ll tissue PG accession E developmental stage E age C env condition E genotype E background study type project id project name experiment id experiment name individual E sample name Select all none You can add other datasets using the selection box Add a dataset located below the last dataset field By clicking all at the bottom left of each dataset all att
3. You can sort your results table by selecting the column to sort through the field Sortby Note In the command line version ofX2DBI it is possible to sort the table on several columns inserting multiple OrderBy tags in the XML query file see section 9 To launch the query simply click on Launch query just below the tabs bar After a few time results appear as a table with in columns the attributes selected in the X2 D B l 7 U ser manu al Release dele Oar 1201 H Page 9 14 Query tab Exports cv Rows 4 found taxon p L experiment developmental pona d Greenhouse Tomato ipo rr S scat Lycopersicon ER 042 fruit oat 10 Hortis Metabolomics 17 PO 0009034 perncarp PO 000T038 ie Falmiro gigne 2009 eean eye pna S Greenhouse Tomato S S green Lycopersicon nep S l Palmira and FR ae fruit 4081 leni 10 Hortis Metabolomics 18 Ciotki PO 0008034 pencarp PO 0007033 l lele Clotide WT house 2009 on study l Greenhouse Tomato r green Lycopersicon Falmiro and FR 04 fruit 4081 10 Hortis Metabolomics 18 x DPO 0009034 pericarp DO 0007033 Palmira WT house esculentum Clotilde ripening complete L S Greenhouse Tomato Palmira wath FR 04 fruit green 4081 ES y 10 Hortis Metabolomics 19 nutrient solution PO 0009034 pericarp PO 0007033 lele Falmiro WT house 2009 recycling oan study Note You can stop a query by clicking the STOP button that appears at the top right of the page If an URL link has been associated with
4. lt Attribute name meryb id gt lt Attribute name compound name gt Attribute name user synonym gt lt Attribute name nmr multiplicity gt Attribute name nmr shift gt Attribute name nmr shift description gt lt Attribute name compound description gt lt Dataset gt lt OrderBy name compound name gt lt gt You can see 2 datasets Dataset each containing one or more attributes Attribute There is only one constraint Filter placed on the first dataset Dataset name samples The data sorting is done according to the species column OrderBy name species We have to read the XML query exemple above as SELECT Attributes meryb_id compound_name user_synonym nmr_multiplicity nmr shift nmr_description compound_description WHERE Filters projet_name Tomato Metabolomics 2006 ORDER BY compound_name X2DBI User manual It gives the results as below MRE MRE MRB MREZ1 MRES MRB63 MREGS MREGG cutted 11 4 Aminobutancate GABA 4 Aminobutancate GABA 4 Aminobutancate GABA 5 Oxoproline pyroglutamic acid Doublet of doublets 4 18 Acetyichoine Acetylcholine Alanine Alanine Aspartate Aspartate Interface to browse the database Multiplet 1 92 Triplet 2 5 Triplet 3 01 Singlet 3 22 Doublet 146 Multiplet 2 92 2 61 Multiplet Rows 43 found nmr shit description compound description C3H2 N C 5 H3 3 C 3 H3 C 3 H2 1 2 C 3 H2 vers
5. 1 2 7 X2DBI User manual Release date 06 11 2011 Page 10 14 8 Import Export query In X2DBI you can save your query filters attributes in XML format using the Export query button Then you can load your XML queryin X2DBI using the Import query button Your XML FILE Parcourir Upload 9 Predefined queries If you have any queries frequently used it is best to ask the website administrator who setup the application to put it in the list of predefined queries One can see the predefined queries as shortcuts for importation which internally works in the same way Results Browse 5 rows Predefined Queries lt Results C Attributes Predefined Queries samples 4 Projects Overview Compounds by Project Quantification by Compound Bt2 Maize Kernel Dataset developmental stage age Tomato Metabolomics _2006 Dataset genotype backg taxon id species tissue PG ac Thus you can simply by selecting a query in this list set the corresponding datasets and attributes within the interface possibly make some modification e g values of filter conditions and submit it 10 XML query format This section concerns a technical part of the software The aim of this section is to describe the structure and the content of the XML query file if you want to create or edit it yourself before importing directly the query in X2DBI A XML query has the following
6. some attributes values of these attributes appear as such and you can click on them to access the linked resources c Data export It is possible to export results in CSV format button on the horizontal bar above the results table for processing with other tools 4 rows taxon_id species project_id project_name experiment_id experiment_name ps_accession tissue pg_accession developmental_stage a ge genotype background study_type http www cbhib u bordeaux2 fr MERYB res ontology TAX0O 4081 Lycopersicon esculentum http www cbib u bordeaux2 fr MERYB res project 10 Greenhouse Tomato Hortis Metabolomics 2009 http www cbib u bordeaux2 fr MERYB res experiment 17 Palmiro without nutrient solution recycling http www cbib u bordeaux2 fr MERYB res ontology PS PO 0009084 pericarp http www cbib u bordeaux2 fr MERYB res ontology PG P0 0007038 FR 04 fruit ripening complete Palmiro WT green house study http www cbhib u bordeaux2 fr MERYB res ontology TAX0O 4081 Lycopersicon esculentum http www cbib u bordeaux2 fr MERYB res project 10 Greenhouse Tomato Hortis Metabolomics 2009 http www cbib u bordeaux2 fr MERYB res experiment 18 Palmiro and Clotilde http www cbib u bordeaux2 fr MERYB res ontology PS PO 0009084 pericarp http www cbib u bordeaux2 fr MERYB res ontology PG P0 0007038 FR 04 fruit ripening complete Clotilde WT green house study http www cbhib u bordeaux2 fr MERYB res on
7. structure Query gt Dataset gt Attribute Filter Query gt OrderBy X2 D BI 7 U ser manu al Release dele ba 12011 Page 11 14 The Filter tag can contain different XML attributes depending on the type of filter chosen by the user Filter min max for the filter between Filter item pour les for the filters in set numeric and in set character Here is a XML query template lt Query vVirtualSchemaName default datasetConfigversion 1 2 6 gt Dataset name intertface detfault gt lt Attribute name gt lt Attribute name gt Filter type name value gt lt Datagzet gt Dataset name __ intertace default gt lt Dataset gt lt OrderBy name gt lt Query gt The Query tag defines the query itself The datasetConfigVersion setting of 1 2 6 indicates that you are using XML compatible with the dataset configuration being queried There can be one or multiple dataset tags depending on the query There can be multiple Attribute and Filter tags each indicating the name of an attribute or filter to apply Filter tags also accept a value to apply to the filter Attributes in the result columns returned will be sorted first by the order of the Dataset tags in which they appear then by the order of the Attribute tags within the Dataset tags Each entity has XML attributes Tag List of XML attributes Query virtualSchemaName datasetCon
8. Attribute Description appearing as a popup faxonid NCBI Taxon D TO PS accession Growth and Dev Stage Accession POC developmental stage Growth and Dev Stage POC age Pe envy condition Treatment Genotype Background CV Condition of Study EO Individu Plant Name Plant Sample Code or Name 6 Filters selection Once your attributes are selected you can use filters to add constraints on your query by clicking on the Add a Filter button on to the Filters panel in the Query tab This step is optional If you want to limit your results to a certain range values of attributes you can create one or more filters By default a blanked form appears El Filters Filter 1 a lt exact match It is framed in red to indicate that in this state the constraint will not be included in your query Then you need to fill the form the selection field on the left and the text field on the right if you want your constraint to be taken into account It switches in blue to indicate that the constraint will be integrated into your query El Filters Filter 1 project name lt lt exact match lt Tomato Metabolomics 2006 version 1 2 7 X2DBI User manual Release date 06 11 2011 Page 7 14 For some attributes an auto completion is enabled in the text field if the following conditions are true e The values of the attribute can be browsed in the Browse tab e The exact valu
9. X2 D B l g U Se r ma n u al Release Pa oi l Page 1 14 CONTENTS L Amor DDI eee EA A EE AEE EEEE AEA E E 2 P PLZ e Te A A E A A E O E A E ne A A nee 3 r DTE 2 T O A RE E EEA EE E EET 4 c AEN O e E E E 4 I ADSS E OI eI A E A 4 R E E vaste 2167 1 0 E E E N A A E E A eee 6 de CO lt T 8 a ee e E E E E R E E A A N D ROU A E E EE N EP R a 016 TTT 9 o EPON UEI acres ernest clge cee am EE EE E E E NE 10 2 Prede ino ES U 10 1O PRIVY Orma eier a EErEE E EEN EA ESE 10 11 Interface to browse the database sss sese 13 X2 D BI 7 U ser manu al Release dei ba 12011 Page 2 14 1 Aim of X2DBI X2DBI is a web application designed as a query oriented data management system for the construction of complex queries on a database without knowing its structure X2DBI stands for XML to DBI and DBI for DataBase Interface Because it is impossible to foresee all possible types of queries in advance X2DBI can help DB application developers by allowing users to build custom queries on databases themselves X2DBI does not require any knowledge from users about tables fields views and other database issues and avoids complicated SQL statements Just include end user oriented query builder X2DBI GUI into your web site The principle of X2DBI is to convert a database relational into Datasets which can be accessed 7 via its standardized web browser interface ii via web service APIs Query results are displayed in tabular format and a
10. alyzed data for the sake of confidentiality of private data but also because of the big amount of data to display
11. e is selected in the select type field You can add another filter by clicking the gt A dd a Filter button You also can delete a filter by using the Delete button in each form This button appears only if at least 2 filter forms are displayed Filter 1 project name exact match Tomato Metabolomics 2006 Filter 2 compound name gt lt exact match gt Chlorogenate Once an attribute set is selected in the Query tab you can filter the query on any attribute of this set even if this attribute is not selected in the query There are several types of filters Type Description exact match Filter to an exact numeric or alphanumeric value between Filter between two numeric values Filter on a numeric or alphanumeric value e g XX which begins by XX contains XX or ends by XX For an advanced use you can write the value directly with the character like e XX_ search for values ending with XX XX search for values beginning with XX e XXD search for values containing XX Filter on a set of numeric values separated by a 5 It also filters ranges in set numeric of values with the separator Ex 1 1 1 2 3 5 3 6 defines the two ranges 1 1 1 2 and 3 5 3 6 Filter on a set of string values separated by a For an advanced use you can write strings with the character like the like filter e XX search for values ending wi
12. fig Version Dataset name subset name interface has the value default name name of the selected attributes Attribute Unlike the interface attribute names do not have space They are replaced by the character _ Example project name in the web interface become project_name in the XML file Filter type filter category The categories are attribute as exact match like version 1 2 7 X2DBI User manual Release date 06 11 2011 Page 12 14 interval as between set as in set numeric setlike as in set character greater as greater than less as less than name attribute name value filter value if the filter 1s different than interval set andsetlike value filter minimum value if the filter type isinterval value filter maximum value if the filter type is interval value filter value item aro Mies if the filter type is setlike See below an example to illustrate the file structure of an XML query OrderBy name attribute name to sort only the use of the command line for the XML query can take into account more than one tag OrderBy ax lt vVirtualSchemaName default datasetConfigversion 1 2 6 gt lt Dataset name samples interface default gt Filter type attribut name project name value Tomato Metabolomics 2006 gt lt Dataset gt lt Dataset name compounds interface default gt
13. ion 1 2 7 Release date 06 11 2011 Page 13 14 X2DBI can also browse the database by subset buttons on the horizontal bar below the tabs and by attribute in order to display and fetch the list of values of an attribute in the database Query Results Cs ee Ke rows Z Query Results mb Subset gt samples Links Subset s NMRBucket Compounds Attributes taxon id Species PS accession tissue PG accession developmental stage age env condition genotype background study type project id project name experiment id experiment name individual sample name Attribute gt species Subset D ipti Values samples NCBI Taxon Name TO Arabidopsis thaliana Beta vulgans Brassica oleracea var italica Cucumis melo Daphne gnidium Elaeis guineensis Fragana x ananassa Lycopersicon esculentum Medicago truncatula Oryza sativa Ostreococcus taun Phoenix dactylifera Prunus persica Vanilla planifola Vitis vinifera Zea mays To access to a dataset you can click the group of interest in the bar at the top of the image version 1 2 7 X2DBI User manual Release date 06 11 2011 Page 14 14 above The row Links in the table represents the datasets e g Compounds related to the displayed dataset samples Each attribute of the list is clickable For some attributes the list of values is not displayed attributes not Browsable This concerns in particular the an
14. re exportable in a format such as Excel CSV This tool needs to be configured by somebody with skills in Perl and SQL scripting see http sites google com site x2dbiorg for installation instructions Currently supported RDBMS platforms are MySQL and PostgreSQL Funding Initially developed by the CBiB Bordeaux Bioinformatics Center and funded within the ANR PROTICworkshop Project 2008 2010 Demo Site http services cbib u bordeaux2 fr x2db1 Availability http sites zoogle com site x2dbiorg Optimized for Mozilla Firefox Google Chrome amp Internet Explorer 9 Licence X2DBI software is completely Open Source licensed under the GNU General Public License and freely available to anyone to use and redistribute without restriction Contacts Daniel Jacob daniel jacob u bordeaux2 fr Laurent Gil lgil ebi ac uk 1 http www cbib u bordeaux2 fr en 2 http moulon inra fr index php en tranverse team atelier de bioinformatique projects 97 version 1 2 7 X2DBI User manual Release date 06 11 2011 Page 3 14 2 Interface overview You will find below a short description of the web interface of X2DBI The web address to access to your X2DBI is as follows http lt your_web_site gt x2dbi For example http www cbib u bordeaux2 ft x2dbi1 Results Browse ADA Predefined Quenes lt Results DataSource MSRI GMD Attributes 5 urrent selection c
15. ributes of the group will be checked Conversely by clicking none all attributes of the group will be unchecked samples igj taxon id E species LIPS accession tissue PG accession a developmental stage E age L env condition E genotype El background E study type E project id EJ project name E experiment id E experiment name C individual E sample name Select all none compounds igj El compound name C accession id me ryb id user synonym E compound description C nmr shift C nmr shift description C amr multiplicity E experiments set name Select all none Add a dataset However you cannot display any group of attributes at any place they are related to each other with an order defined by the website administrator who setup the application 9 DataSource MeRy B B The list of selected attributes is dynamically displayed in the left panel field Current selection below the authentication field It shows you what will be order of the columns in the result table Current selection Thus the first column of the result table will be meryb_id the second column will be compound name and so on meryb id compound name user synonym nmr multiplicity nmr shift nmr shift description compound description version 1 2 7 X2DBI User manual Release date 06 11 2011 Page 6 14 2 Example of help form ig
16. th XX XX search for values beginning with XX e XxX search for values containing XX in set character Ex ose ase defines two groups respectively strings ending by ose and ase greater than Filter on values strictly greater gt than the given value less than Filter on values strictly less lt than the given value version 1 2 7 X2DBI User manual Release date 06 11 2011 Page 8 14 NOT Negation which inverts the filter conditions 7 Query a Count Before submit the query and display all the results you can as a first step see the number of results found by clicking on the button COUNT The number of results can be seen in the upper right of the page s aT 6399 rows If you change the attributes or the filters after submitting a COUNT the number into the count field will be coloured in red NT s399 rows It indicates that the count result does not correspond necessarily to the current query and need to be updated To obtain the number of results returned by your new query simply click again to the COUNT button Note You can stop a COUNT query by clicking the STOP button that appears at the top right of the page b Results Once attributes were selected and filters were added you can submit the application by clicking on the Launch query button in the top of the Results tab Query Browse d Query Sort by species
17. tology TAXO 4081 Lycopersicon esculentum http www cbib u bordeaux2 fr MERYB res project 10 Greenhouse Tomato Hortis Metabolomics 2009 http www cbib u bordeaux2 fr MERYB res experiment 18 Palmiro and Clotilde http www cbib u bordeaux2 fr MERYB res ontology PS PO 0009084 pericarp http www cbib u bordeaux2 fr MERYB res ontology PG P0 0007038 FR 04 fruit ripening complete Palmiro WT green house study http www cbhib u bordeaux2 fr MERYB res ontology TAX0O 4081 Lycopersicon esculentum http www cbib u bordeaux2 fr MERYB res project 10 Greenhouse Tomato Hortis Metabolomics 2009 http www cbib u bordeaux2 fr MERYB res experiment 19 Palmiro with nutrient solution recycling http www cbib u bordeaux2 fr MERYB res ontology PS PO 0009084 pericarp http www cbib u bordeaux2 fr MERYB res ontology PG P0 0007038 FR 04 fruit ripening complete Palmiro WTI green house study You can then return to the Query tab to modify and or refine your search To launch your new query simply click again on the Launch button in the Results tab Note In the case of URLs links have been associated with some attributes when the query is submitted through the command line interface CLI the first part of the URL appears betwween two characters as shown here Otherwise when exporting through the Web interface GUD this part of URL will not be included version
Download Pdf Manuals
Related Search