MySQL views for easier data extraction

[jorainer]

This is the views that I'm currently using in my local MassBank playground:

create view spectra_data as select
       RECORD.ACCESSION as spectrum_id,
       RECORD.RECORD_TITLE as spectrum_name,
       RECORD.DATE as date,
       RECORD.AUTHORS as authors,
       RECORD.LICENSE as license,
       RECORD.COPYRIGHT as copyright,
       RECORD.PUBLICATION as publication,
       RECORD.AC_MASS_SPECTROMETRY_MS_TYPE as msLevel,
       RECORD.AC_MASS_SPECTROMETRY_ION_MODE as polarity,
       RECORD.PK_SPLASH as splash,
       RECORD.CH as compound_id,
       (select VALUE from MS_FOCUSED_ION where RECORD = RECORD.ACCESSION
       and SUBTAG = 'BASE_PEAK') as precursorIntensity,
       (select VALUE from MS_FOCUSED_ION where RECORD = RECORD.ACCESSION
       and SUBTAG = 'PRECURSOR_M/Z') as precursorMz,
       (select VALUE from MS_FOCUSED_ION where RECORD = RECORD.ACCESSION
       and SUBTAG = 'PRECURSOR_TYPE') as adduct,
       (select VALUE from AC_MASS_SPECTROMETRY where RECORD = RECORD.ACCESSION
       and SUBTAG = 'IONIZATION') as ionization,
       (select VALUE from AC_MASS_SPECTROMETRY where RECORD = RECORD.ACCESSION
       and SUBTAG = 'IONIZATION_VOLTAGE') as ionizationVoltage,
       (select VALUE from AC_MASS_SPECTROMETRY where RECORD = RECORD.ACCESSION
       and SUBTAG = 'FRAGMENTATION_MODE') as fragmentationMode,
       (select VALUE from AC_MASS_SPECTROMETRY where RECORD = RECORD.ACCESSION
       and SUBTAG = 'COLLISION_ENERGY') as collisionEnergy
from RECORD;

create view compound as select
       COMPOUND.ID as compound_id,
       COMPOUND.CH_FORMULA as formula,
       COMPOUND.CH_EXACT_MASS as exactmass,
       COMPOUND.CH_SMILES as smiles,
       COMPOUND.CH_IUPAC as inchi,
       NAME.CH_NAME as compound_name
from COMPOUND
join COMPOUND_NAME on (COMPOUND.ID = COMPOUND_NAME.COMPOUND)
join NAME on (COMPOUND_NAME.NAME = NAME.ID);

create view peaks as select
       PEAK.RECORD as spectrum_id,
       PEAK.PK_PEAK_MZ as mz,
       PEAK.PK_PEAK_INTENSITY as intensity
from PEAK;

create user 'massbank'@'%';
grant select on MassBank.peaks to 'massbank'@'%';
grant select on MassBank.spectra_data to 'massbank'@'%';
grant select on MassBank.compound to 'massbank'@'%';
grant show view on MassBank.peaks to 'massbank'@'%';
grant show view on MassBank.spectra_data to 'massbank'@'%';
grant show view on MassBank.spectra_data_full to 'massbank'@'%';

I'm by no means an SQL guru - so I guess these can be improved (feel free to do so!). I also add a user massbank that has only access to these views.

[jorainer]

An updated version of the views is below - I added additional fields that I missed above:

create view spectra_data as select
       RECORD.ACCESSION as spectrum_id,
       RECORD.RECORD_TITLE as spectrum_name,
       RECORD.DATE as date,
       RECORD.AUTHORS as authors,
       RECORD.LICENSE as license,
       RECORD.COPYRIGHT as copyright,
       RECORD.PUBLICATION as publication,
       RECORD.AC_MASS_SPECTROMETRY_MS_TYPE as msLevel,
       RECORD.AC_MASS_SPECTROMETRY_ION_MODE as polarity,
       RECORD.PK_SPLASH as splash,
       RECORD.CH as compound_id,
       (select VALUE from MS_FOCUSED_ION where RECORD = RECORD.ACCESSION
       and SUBTAG = 'BASE_PEAK') as precursorIntensity,
       (select VALUE from MS_FOCUSED_ION where RECORD = RECORD.ACCESSION
       and SUBTAG = 'PRECURSOR_M/Z') as precursorMz,
       (select VALUE from MS_FOCUSED_ION where RECORD = RECORD.ACCESSION
       and SUBTAG = 'PRECURSOR_TYPE') as adduct,
       (select VALUE from AC_MASS_SPECTROMETRY where RECORD = RECORD.ACCESSION
       and SUBTAG = 'IONIZATION') as ionization,
       (select VALUE from AC_MASS_SPECTROMETRY where RECORD = RECORD.ACCESSION
       and SUBTAG = 'IONIZATION_VOLTAGE') as ionizationVoltage,
       (select VALUE from AC_MASS_SPECTROMETRY where RECORD = RECORD.ACCESSION
       and SUBTAG = 'FRAGMENTATION_MODE') as fragmentationMode,
       (select VALUE from AC_MASS_SPECTROMETRY where RECORD = RECORD.ACCESSION
       and SUBTAG = 'COLLISION_ENERGY') as collisionEnergyText,
       (select INSTRUMENT.AC_INSTRUMENT from INSTRUMENT where
       INSTRUMENT.ID = RECORD.AC_INSTRUMENT) as instrument,
       (select INSTRUMENT.AC_INSTRUMENT_TYPE from INSTRUMENT where
       INSTRUMENT.ID = RECORD.AC_INSTRUMENT) as instrument_type 
from RECORD;

create view compound as select
       COMPOUND.ID as compound_id,
       COMPOUND.CH_FORMULA as formula,
       COMPOUND.CH_EXACT_MASS as exactmass,
       COMPOUND.CH_SMILES as smiles,
       COMPOUND.CH_IUPAC as inchi,
       (select DATABASE_ID from CH_LINK where CH_LINK.COMPOUND = COMPOUND.ID
       and CH_LINK.DATABASE_NAME = 'INCHIKEY') as inchikey,
       (select DATABASE_ID from CH_LINK where CH_LINK.COMPOUND = COMPOUND.ID
       and CH_LINK.DATABASE_NAME = 'CAS') as cas,
       (select DATABASE_ID from CH_LINK where CH_LINK.COMPOUND = COMPOUND.ID
       and CH_LINK.DATABASE_NAME = 'PUBCHEM') as pubchem,
       NAME.CH_NAME as compound_name
from COMPOUND
join COMPOUND_NAME on (COMPOUND.ID = COMPOUND_NAME.COMPOUND)
join NAME on (COMPOUND_NAME.NAME = NAME.ID);

create view peaks as select
       PEAK.RECORD as spectrum_id,
       PEAK.PK_PEAK_MZ as mz,
       PEAK.PK_PEAK_INTENSITY as intensity
from PEAK;

create user 'massbank'@'%';
grant select on MassBank.peaks to 'massbank'@'%';
grant select on MassBank.spectra_data to 'massbank'@'%';
grant select on MassBank.compound to 'massbank'@'%';
grant show view on MassBank.peaks to 'massbank'@'%';
grant show view on MassBank.spectra_data to 'massbank'@'%';
grant show view on MassBank.spectra_data_full to 'massbank'@'%';

[meier-rene]

These views are now integrated.