I worked out the inheritance hierarchy I wanted to use, and that seems to have
fixed tables that cross the spectrum and experiment tables, but there are two
other tables (proteinident and peptideident) that still show the error. I'll
include a simplified version of the schema (main tables only) at the end of
this message.
Queries that require only the experiment table work fine, as do queries that
require only the spectrum table. Queries that require the proteinident and
peptideident tables work, but only if they don't use the columns they inherited
from the spectrum table.
CREATE TABLE PRIDE_BIOMART_SCHEMA_NAME.pride__experiment__main (
experiment_id_key INTEGER UNSIGNED NOT NULL,
experiment_ac VARCHAR(8) NOT NULL,
experiment_title TEXT NOT NULL,
experiment_short_title VARCHAR(255) NULL,
sample_name VARCHAR(255) NULL,
sample_description_comment TEXT NULL,
PRIMARY KEY(experiment_id_key)
);
CREATE TABLE PRIDE_BIOMART_SCHEMA_NAME.pride__spectrum__main (
spectrum_id_key INTEGER UNSIGNED NOT NULL,
ms_level INTEGER NOT NULL,
range_start DOUBLE PRECISION NULL,
range_stop DOUBLE PRECISION NULL,
mz_data TEXT NULL,
intensity TEXT NULL,
-- inherited from experiment
experiment_id_key INTEGER UNSIGNED NOT NULL,
experiment_ac VARCHAR(8) NOT NULL,
experiment_title TEXT NOT NULL,
experiment_short_title VARCHAR(255) NULL,
sample_name VARCHAR(255) NULL,
sample_description_comment TEXT NULL,
PRIMARY KEY(experiment_id_key, spectrum_id_key)
);
CREATE TABLE PRIDE_BIOMART_SCHEMA_NAME.pride__proteinident__main (
ident_id_key INTEGER NOT NULL,
submitted_accession VARCHAR(50) NOT NULL,
submitted_ac_protein_database VARCHAR(150) NULL,
submitted_ac_protein_db_version VARCHAR(30) NULL,
protein_sequence TEXT NULL,
protein_spectrum_ref VARCHAR(100) NULL,
protein_score DECIMAL(20,10) NULL,
protein_threshold VARCHAR(255) NULL,
search_engine VARCHAR(255) NULL,
-- inherited from spectrum
spectrum_id_key INTEGER UNSIGNED NOT NULL,
ms_level INTEGER NOT NULL,
range_start DOUBLE PRECISION NULL,
range_stop DOUBLE PRECISION NULL,
mz_data TEXT NULL,
intensity TEXT NULL,
-- inherited from experiment
experiment_id_key INTEGER UNSIGNED NOT NULL,
experiment_ac VARCHAR(8) NOT NULL,
experiment_title TEXT NOT NULL,
experiment_short_title VARCHAR(255) NULL,
sample_name VARCHAR(255) NULL,
sample_description_comment TEXT NULL,
PRIMARY KEY(experiment_id_key, spectrum_id_key, ident_id_key)
);
CREATE TABLE PRIDE_BIOMART_SCHEMA_NAME.pride__peptideident__main (
peptide_id_key INTEGER NOT NULL,
prob DOUBLE PRECISION NULL,
mascot DOUBLE PRECISION NULL,
sequest DOUBLE PRECISION NULL,
xc DOUBLE PRECISION NULL,
dcn DOUBLE PRECISION NULL,
sp DOUBLE PRECISION NULL,
rsp DOUBLE PRECISION NULL,
ions DOUBLE PRECISION NULL,
mass DOUBLE PRECISION NULL,
charge DOUBLE PRECISION NULL,
peptide_sequence VARCHAR(255) NULL,
start_coord INTEGER NULL,
end_coord INTEGER NULL,
peptide_spectrum_ref VARCHAR(255) NULL,
-- inherited from proteinident
ident_id_key INTEGER NOT NULL,
submitted_accession VARCHAR(50) NOT NULL,
submitted_ac_protein_database VARCHAR(150) NULL,
submitted_ac_protein_db_version VARCHAR(30) NULL,
protein_sequence TEXT NULL,
protein_spectrum_ref VARCHAR(100) NULL,
protein_score DECIMAL(20,10) NULL,
protein_threshold VARCHAR(255) NULL,
search_engine VARCHAR(255) NULL,
-- inherited from spectrum
spectrum_id_key INTEGER UNSIGNED NOT NULL,
ms_level INTEGER NOT NULL,
range_start DOUBLE PRECISION NULL,
range_stop DOUBLE PRECISION NULL,
mz_data TEXT NULL,
intensity TEXT NULL,
-- inherited from experiment
experiment_id_key INTEGER UNSIGNED NOT NULL,
experiment_ac VARCHAR(8) NOT NULL,
experiment_title TEXT NOT NULL,
experiment_short_title VARCHAR(255) NULL,
sample_name VARCHAR(255) NULL,
sample_description_comment TEXT NULL,
PRIMARY KEY(experiment_id_key, spectrum_id_key, ident_id_key,
peptide_id_key)
);
From: Arek Kasprzyk [mailto:[email protected]]
Sent: Wednesday, December 21, 2011 4:03 PM
To: Greenman, Ronald (NIH/CIT) [C]
Cc: [email protected]
Subject: Re: [BioMart Users] Adding a new attribute set to an existing mart
Hi Ronald,
the SQL path through mart tables is resolved during run time based on the main
table hierachy and the keys present in the dm tables. Dependent on the
particular combination of dimensions different mains maybe be used. If you have
your schema handy and could send me a few examples of queries that work and
some of those that don't I'll try to look into it in more detail. Perhaps I
will be able to come up with a better advice
a
On Wed, Dec 21, 2011 at 9:08 AM, Greenman, Ronald (NIH/CIT) [C]
<[email protected]<mailto:[email protected]>> wrote:
Thanks. It looks like I actually do need to rework the hierarchy a little.
However, the spectra already do inherit one of the keys from higher in the
hierarchy: the one from experiment__main. I mention this because after I sent
yesterday's e-mail, I found that the bug wasn't as simple as I'd first thought:
I can use queries that cross two tables for some attributes, but not others.
Extending the example from the previous message, a query that involves some
attributes from spectrum__main and also experiment__main.institution works, but
a query that involves experiment__main.experiment_ac does not. What I find
strange about this is that experiment__main.institution isn't actually part of
experiment__main; it's in a dimension table (contact__dm, specifically). But
that one works, while experiment_ac, which is actually in experiment__main,
doesn't.
From: Arek Kasprzyk
[mailto:[email protected]<mailto:[email protected]>]
Sent: Tuesday, December 20, 2011 7:04 PM
To: Greenman, Ronald (NIH/CIT) [C]
Cc: [email protected]<mailto:[email protected]>
Subject: Re: [BioMart Users] Adding a new attribute set to an existing mart
Hi Ronald,
there are certain rules when creating marts with multiple mains. You are
probably aware of this, I just want to make sure that we exclude any potential
problems with those:
Each 'submain' must 'inherit' all the columns from the the previous main up
including the keys eg.
if the first main is 'gene__main' that has three columns including 'gene_key',
then the next submain e.g transcript__main needs to have all the three columns
from 'gene__main' plus any additional columns let's say 5 from transcript and
its own key let's say 'transcript_key'. The next submain let's say
'protein__main would than have to have 8 columns including two keys 'gene_key'
and 'transcript_key' plus its own key let's say 'protein_key' and any
addtitional columns for protein__main.
hope this helps,
a
On Tue, Dec 20, 2011 at 4:57 PM, Greenman, Ronald (NIH/CIT) [C]
<[email protected]<mailto:[email protected]>> wrote:
I'm extending the existing pride mart. Double underscores are used for table
names, single underscores for keys. My attributes were added later: the ones on
existing main tables worked fine, but this new main table didn't.
I've actually made some progress since my last message: I needed to explicitly
add pride__spectrum__main to the list of main tables and spectrum_id_key to the
list of primary keys. This causes queries on that table to show up normally, as
long as I only use only attributes and filters involving that table. However,
if I use attributes or filters involving BOTH that table AND another table
(say, pride__experiment__main), I get similar errors, only this time it says it
can't find "main" when referring to fields on the tables that worked before.
Queries involving those old tables still work, as long as I don't try to mix
them with the old tables.
Single table, old: OK
Multiple tables, old only: OK
Single table, new: OK
Multiple tables, old and new: FAILED
(I only have one new table, do I can't test what happens with multiple new
tables)
For example, let's say I have a query that filters on
pride__sequence__main.ms_level and returns pride__sequence__main.ms_level,
pride__sequence__main.range_start, and pride__sequence__main.range_stop. This
works fine. If I add pride__experiment__main.experiment_ac, however, then
martview says that it can't find the column 'main.experiment_ac' in 'field
list'. This also happens in martexplorer.
From: Arek Kasprzyk
[mailto:[email protected]<mailto:[email protected]>]
Sent: Tuesday, December 20, 2011 11:50 AM
To: Greenman, Ronald (NIH/CIT) [C]
Cc: [email protected]<mailto:[email protected]>
Subject: Re: [BioMart Users] Adding a new attribute set to an existing mart
Hi Ronald,
A few questions to help me with a 'diagnosis' of the problem:
Are you creating your mart from scratch or are you extending the existing pride
mart?
Do you use double underscores for table names and single underscores for 'key'
names?
Did your attributes show up in the naive configuration or did you add them
manually later?
a
On Tue, Dec 20, 2011 at 9:24 AM, Greenman, Ronald (NIH/CIT) [C]
<[email protected]<mailto:[email protected]>> wrote:
I'm running a mart (based on the one used by PRIDE), but I need to add a new
table to it. I'm currently calling it pride__spectrum__main, and I don't think
I need to add any dimension tables for now. I'm having some trouble getting the
API to recognize it, though. Although the filters and attributes I created show
up in martview, attempting to use them results in a critical error: it says
that it can't find the table 'main', which doesn't sound like it falls into the
BioMart naming scheme.
I made a pride__spectrum__main table, with a primary key as the column
'spectrum__id__key', and added spectrum_id_ref columns in other tables as
needed. I used marteditor to create an attribute set keyed to that table as
well as a set of filters for it. Is there something else I need to do to make
this work?
_______________________________________________
Users mailing list
[email protected]<mailto:[email protected]>
https://lists.biomart.org/mailman/listinfo/users
_______________________________________________
Users mailing list
[email protected]
https://lists.biomart.org/mailman/listinfo/users
