Changes between Version 6 and Version 7 of DespoinaLog/2010/12/09
- Timestamp:
- 2010-12-09T12:51:30+01:00 (14 years ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
DespoinaLog/2010/12/09
v6 v7 1 1 == Inconsistency with biobank table (pheno & gcc project) == 2 == problem: ==2 == problem: == 3 3 The bbmri.xml describes the table as follows :[[BR]]<entity name="Biobank" extends="Panel">[[BR]] <description>Describes one biobank cohort</description>[[BR]] <field name="Acronym" nillable="true" description="Acronym for this[[BR]]biobank" />[[BR]] <field name="Institutes" nillable="true" type="mref"[[BR]]xref_entity="Institute" mref_name="!BiobankInstitute"[[BR]]description="Institute that are custodian of this biobank" />[[BR]] <field name="Publications" type="mref" xref_entity="Publication"[[BR]]description="Publications for this biobank" />[[BR]] <field name="Category" type="xref" xref_entity="!OntologyTerm" />[[BR]] <field name="Type" type="enum" enum_options="[core,support,other]" />[[BR]] <field name="Contacts" type="mref" xref_entity="Contact"[[BR]] mref_name="!BiobankContact" description="Contact for this biobank" />[[BR]] <field name="Topics" type="mref" xref_entity="!OntologyTerm" />[[BR]] <field name="Materials" type="mref" xref_entity="!OntologyTerm" />[[BR]] <field name="Size" nillable="true" />[[BR]] <field name="!LastUpdate" type="date" auto="true" />[[BR]] <field name="Description" type="text" nillable="true" />[[BR]] </entity>[[BR]][[BR]]Similarly the BBMRIupdateDatabase tries to create these fields. But[[BR]]the created table in DB is missing the fields:[[BR]][[BR]]Institutes (mref), Publications(mref), Contacts (mref), Topics[[BR]](mref), Materials (mref).[[BR]][[BR]]Is this how was supposed to work? Because it causes a problem in mysql[[BR]]input from the previous scheme (in pheno). 4 4 5 == Solution == 5 == == 6 == Approach 1)Solution == 6 7 * create new table biobank from pheno project as follows: 7 8 8 mysql> create table biobank_new select Acronym, Category, Type, Size, !LastUpdate , Description, id from biobank ;9 mysql> create table biobank_new select Acronym, Category, Type, Size, !LastUpdate , Description, id from biobank ; 9 10 10 11 (only fields that exist in biobank in gcc project) … … 16 17 * mysql5 -u root -p bbmri < biobank_new.mysql 17 18 18 == select query retrieving records in molgenis form == 19 == == 20 == Approach 2)select query retrieving records in molgenis form == 19 21 === We need data inside INNER/LEFT JOIN tables === 20 22 select count(*) as num_rows FROM Biobank INNER JOIN … … 26 28 '''!ObservationElement''' ON (Biobank.id = !ObservationElement.id) LEFT JOIN 27 29 28 '''Investigation''' AS xref_Investigation ON xref_Investigation.id = !ObservationElement.Investigation LEFT JOIN 30 ''' Investigation''' AS xref_Investigation ON xref_Investigation.id = !ObservationElement .Investigation LEFT JOIN 29 31 30 '''!OntologyTerm''' AS xref_ontologyReference ON xref_ontologyReference.id = !ObservationElement.ontologyReference LEFT JOIN 32 '''!OntologyTerm''' AS xref_ontologyReference ON xref_ontologyReference.id = !ObservationElement.ontologyReference LEFT JOIN 31 33 32 '''!OntologyTerm''' AS xref_Species ON xref_Species.id = Panel.Species LEFT JOIN 34 '''!OntologyTerm''' AS xref_Species ON xref_Species.id = Panel.Species LEFT JOIN 33 35 34 '''!OntologyTerm''' AS xref_PanelType ON xref_PanelType.id = Panel.!PanelType LEFT JOIN 36 '''!OntologyTerm''' AS xref_PanelType ON xref_PanelType.id = Panel.!PanelType LEFT JOIN 35 37 36 38 '''!OntologyTerm''' AS xref_Category ON xref_Category.id = Biobank.Category WHERE !ObservationElement.!__Type = 'Biobank' 39 40 == Approach 3) Fix the xls data file according to the consistencies between these entities. ==
