Data::Babel translates biological identifiers based on information contained in a database. Each Data::Babel object provides a unique mapping over a set of identifier types. The system as a whole can contain multiple Data::Babel objects; these may share some or all identifier types, and may provide the same or different mappings over the shared types.

The principal method is "translate" which converts identifiers of one type into identifiers of one or more output types. In typical usage, you call "translate" with a list of input ids to convert. You can also call it without any input ids (or with the special option "input_ids_all" set) to generate a complete mapping of the input type to the output types. This is convenient if you want to hang onto the mapping for repeated use. You can also filter the output based on values of other identifier types.

Comparisons are done in a case insensitive manner. This includes input ids, filters, and internal comparisons used to join database tables. For example, when translating the gene symbol 'HTT' (the human Huntington Disease gene), you will also get information on gene symbol 'Htt' (the mouse and rat ortholog of the human gene) assuming, of course, this information is in the database.

CAVEAT: Some features of Data::Babel are overly specific to the procedure we use to construct the underlying Babel database. We note such cases when they arise in the documentation below.

One typically defines these components using configuration files whose basic format is defined in Config::IniFiles. See examples in "Configuration files" and the examples directory of the distribution.

Each MapTable represents a relational table stored in the database and provides a mapping over a subset of the Babel's IdTypes; the ensemble of MapTables must, of course, cover all the IdTypes. The ensemble of MapTables must also be non-redundant as explained in "Technical details".

MapTables must always contain current identifiers, even for IdTypes that have histories (more precisely, for IdTypes whose Masters have histories). The query or program that loads the database is responsible for mapping old identifiers to current ones (presumably via the history).

"translate" checks the input IdType to see if its Master has history information. If so, "translate" automatically applies the history to all input ids. It does the same for filters.

You need not explicitly define Masters for all IdTypes; Babel will create 'implicit' Masters for any IdTypes lacking explicit ones. An implicit Master has a list of valid identifiers but no history and could be implemented as a view over all MapTables containing the IdType. In the current implementation, we use views for IdTypes contained in single MapTables but construct actual tables for IdTypes contained in multiple MapTables.

Our configuration files use 'ini' format as described in Config::IniFiles: 'ini' format files consist of a number of sections, each preceded with the section name in square brackets, followed by parameter names and their values.

There are separate config files for IdTypes, Masters, and MapTables. There are complete example files in the distribution. Here are some excerpts:

The section name is the IdType name. The parameters (all optional) are

display_name. human readable name for this type

referent. the type of things to which this type of identifier refers

defdb. the database, if any, responsible for assigning this type of identifier

meta. some identifiers are purely synthetic (eg, Entrez gene IDs) while others have some mnemonic content; legal values are

eid (meaning synthetic)

symbol

name

description

format. Perl format of valid identifiers

sql_type. SQL data type

As of version 1.11, it is also possible to specify "history" for an IdType. Previously, you could only specify "history" for the IdType's Master.

Master

[gene_entrez_master]
history=1

The section name is the Master name; the name of the IdType is the same but without the '_master'. If there is no history, the section can be empty, eg,

[probe_id_master]

As of version 1.11, it is also possible to specify "history" for an IdType. Previously, you could only specify "history" for the IdType's Master.

A Master without history is implemented as a one column table whose column has the same name as the IdType.

A Master with history is implemented as a two column table: one column has the same name as the IdType and the other has the prefix '_X_' prepended to the IdType. The '_X_' column contains ids that were valid in the past or are valid now. Each row maps the '_X_' id to its current value, if any, or NULL. For ids that are valid now, the table contains a row in which the '_X_' and current versions are the same.

By default, the "translate" method does not return any output for input identifiers that do not connect to any identifiers of the desired output types; these are output rows in which the output columns are all NULL. You can instruct "translate" to include these rows in the result by setting the "validate" option.

If you set the "validate" option, the output will contain at least one row for each input identifier, and an additional column that indicates whether the input identifier is valid.

If no output IdTypes are specified, "translate" returns a row containing one element, namely, the input identifier, for each input id that exists in the corresponding Master table. If the "validate" option is set, the output will contain one row for each input identifier; this is essentially a (possibly re-ordered) copy of the input list with duplicates removed.

A partial duplicate is a row that contains less information than another row and is therefore redundant. More precisely, a row is a partial duplicate of another row if for all fields (1) the rows are identical or, (2) the field in the partial duplicate is NULL. In the example below, the second row is a partial duplicate of the first.

By default, "translate" removes partial duplicates. The algorithm for removing partial duplicates may be slow for queries with a large number of output columns in cases where a given input id matches a large number of output ids. To retain partial duplicates, you can specify the 'keep_pdups' option to "translate".

A basic Babel property is that translations are stable. You can add output types to a query without changing the answer for the types you had before, you can remove output types from the query without changing the answer for the ones that remain, and if you "reverse direction" and swap the input type with one of the outputs, you get everything that was in the original answer.

We accomplish this by requiring that the database of MapTables satisfies the universal relation property (a well-known concept in relational database theory), and that "translate" retrieves a sub-table of the universal relational. Concretely, the universal relational is the natural full outer join of all the MapTables. "translate" performs natural left out joins starting with the Master table for the input IdType and then including enough tables to connect the input, output, and filter IdTypes. Left outer joins suffice, because "translate" starts with the Master.

We further require that the database of MapTables be non-redundant. The basic idea is that a given IdType may not be present in multiple MapTables, unless it is being used as join column. More technically, we require that the MapTables form a tree schema (another well-known concept in relational database theory), and any pair of MapTables have at most one IdType in common. As a consequence, there is essentially a single path between any pair of IdTypes.

To represent the connections between IdTypes and MapTables we use an undirected graph whose nodes represent IdTypes and MapTables, and whose edges go between each MapTable and the IdTypes it contains. In this representation, a non-redundant schema is a tree.

"translate" uses this graph to find the MapTables it must join to connect the input, output, and filter IdTypes. The algorithms is simple: start at the leaves and recursively prune back branches that do not contain the input or output IdTypes.

Title : translate
Usage : $table=$babel->translate
(input_idtype=>'gene_entrez',
input_ids=>[1,2,3],
filters=>{chip_affy=>'hgu133a'},
output_idtypes=>[qw(transcript_refseq transcript_ensembl)],
limit=>100)
Function: Translate the input ids to ids of the output types
Returns : table represented as an ARRAY of ARRAYS. Each inner ARRAY is one row
of the result. The first element of each row is an input id. If the
validate option is set, the second element of each row indicates
whether the input id is valid. The rest are outputs in the same order
as output_idtypes
Args : input_idtype name of Data::Babel::IdType object or object
input_ids id or ARRAY of ids to be translated. If absent or
undef, all ids of the input type are translated. If an
empty array, ie, [], no ids are translated and the
result will be empty.
input_ids_all boolean. If true, all ids of the input type are
translated. Same as omitting input_ids or setting it
to undef but more explicit.
output_idtypes ARRAY of names of Data::Babel::IdType objects or
objects
filters specification of conditions limiting the output; see
below.
validate boolean. If true, the output will contain at least one
row for each input id and an additional column
indicating whether the input id is valid.
limit maximum number of rows to retrieve
count boolean. If true, return number of output rows rather
than the rows themselves. Equivalent to "count"
method.
keep_pdups boolean. If true, partial duplicates are not removed
from the result.

If input_ids is absent or undef, it translates all ids of the input type.

Duplicate input_ids are ignored.

If input_ids is an empty ARRAY, ie, [], the result will be empty.

It is an error to set both input_ids and input_ids_all.

It is legal to specify a filter on the input idtype. This constrains the input ids to ones that pass the filter and may be especially useful when processing all input ids,

Input and filter ids can be old (valid in the past) or current (valid now). Output ids are always current.

By default, "translate" does not return rows in which the output columns are all NULL. Setting "validate" changes this and ensures that every input id will appear in the output.

If "count" and "limit" both set, the result is the number of output rows after the limit is applied and will always be <= the limit.

If "validate" and "limit" both set, the result may not contain all input ids if to do so would produce more rows than the limit. This defeats one of the purposes of "validate", namely to ensure that all input ids appear in the output.

If "count" and "validate" both set, the result is the number of output rows including ones added by "validate", ie, rows with in which all output columns are NULL.

If "validate" and 'filters' both set, the result may contain input ids excluded by the filter. These rows will have NULLs in all output columns.

If no output idtypes are specified, the output will contain one row for each valid input id (by default) or one row for each id whether valid or not (if "validate" is set).

Comparisons are case insensitive. This includes input ids, filters, and internal comparisons used to join database tables. For example, when translating the gene symbol 'HTT' (the human Huntington Disease gene), you will also get information on gene symbol 'Htt' (the mouse and rat ortholog of the human gene) assuming, of course, this information is in the database.

The "filters" argument is typically a HASH or ARRAY of idtypes and conditions on those idtypes. See "Summary of filters argument" for a full description of what can be used as the "filters" argument. The idtypes can be names of IdType objects or objects themselves. The conditions can be ids or SQL fragments. We process this information to create SQL expressions that can be used in the WHERE clause of the query generated by "translate".

If a filter condition is undef, all ids of the given type are acceptable. This limits the output to rows for which the filter type is not NULL. This usage is analogous to what it means for "input_ids" to be undef. For example,

generates a table of all Entrez Gene ids and gene symbols which either appear in KEGG pathway 4610 or appear in no KEGG pathway.

It may seem strange for undef to have opposite meanings depending on context, but it is "the right thing" in practice.

An empty SQL fragment, ie, \"", means FALSE. If that's the only condition for a given type, the result will be empty. If there are other conditions, eg, we have an ARRAY of conditions, the empty SQL fragment has no effect, because an ARRAY represents the OR of its elements and ORing FALSE to anything is a nop.

a single SQL fragment, eg, chip_affy=>\"LIKE 'hgu133a'". The '\' before the first quote generates a reference to the string, which is what tells the software you want a SQL fragment instead of an id.

Data::Babel::Filter object. Not terribly useful in practice but included for completeness.

undef, eg, pathway_kegg_id=>undef. This means that all ids of the filter idtype are acceptable and only excludes rows for which the filter idtype is NULL.

ARRAY of the above. The general effect is to OR the elements of the ARRAY. The exception is undef: in an ARRAY, undef means that NULL is acceptable. For example

chip_affy=>[\"LIKE 'hgu%'",'mgu74a']

means

chip_affy LIKE 'hgu%' OR chip_affy = 'mgu74a'

and

pathway_kegg_id=>[undef,4610]

means

pathway_kegg_id IS NOT NULL OR pathway_kegg_id = 4610

The "filters" argument as a whole generally contains multiple idtype=>condition pairs. Each generates a Data::Babel::Filter object and the ensemble generates an ARRAY of these objects. The semantics is to AND these together. For example,

chip_affy=>[\"LIKE 'hgu%'",'mgu74a'], pathway_kegg_id=>[undef,4610]

means

(chip_affy LIKE 'hgu%' OR chip_affy = 'mgu74a') AND
(pathway_kegg_id IS NOT NULL OR pathway_kegg_id = 4610)

In succinct terms: we OR the conditions for each individual idtype and AND the conditions across different idtypes.

Filter conditions can contain arbitrarily complex SQL fragments, although we expect most cases to be simple. Simple cases, like the examples above, use the filter's idtype in a single condition. The next step in complexity is to use the idtype in multiple conditions. Here's an example.

chip_affy=>\"LIKE 'hgu%' AND : != 'hgu133b'"

The ':' after the 'AND' is called an embedded idtype marker and tells the code to insert the filter idtype at that point in the query. The ':' is optional at the beginning of the SQL fragment, and we've omitted it in all examples so far. It is also possible to spell out the idtype name after the ':' as in this example

chip_affy=>\"LIKE 'hgu%' AND :chip_affy != 'hgu133b'"

The next jump in complexity is to use multiple idtypes in the same SQL fragment. For example,

chip_affy=>\"(:chip_affy LIKE 'hgu%' AND :chip_affy != 'hgu133b') AND
(:pathway_kegg_id IS NOT NULL OR :pathway_kegg_id = 4610)"

Looking at this example, you might wonder why we need to bother with the filter idtype ('chip_affy=>'). Indeed, once you decide to spell out the idtypes in the SQL, there is no need to specify the filter idtype. This leads to two special cases:

''=>\"(:chip_affy LIKE 'hgu%' AND :chip_affy != 'hgu133b') AND
(:pathway_kegg_id IS NOT NULL OR :pathway_kegg_id = 4610)"

Bear in mind that you can only have one of these! Also remember that using 'undef' as the key may not work as expected, because Perl automatically quotes the word on the left hand side of the '=>' operator.

filters=>"(:chip_affy LIKE 'hgu%' AND :chip_affy != 'hgu133b') AND
(:pathway_kegg_id IS NOT NULL OR :pathway_kegg_id = 4610)"

An empty string or a reference to an empty string means "no filter". This is the same as any other "empty" argument to "filters". Bear in mind that an empty SQL fragment, ie, \"", in any other context means FALSE.

It should be clear that all the filter syntax we've presented up to this point is mere sugar coating for this case. If you're happy writing this sort of SQL, you can skip the rest.

CAUTION: We do not parse the SQL! Syntax errors will be caught by the DBMS and will generate error messages that may not be entirely intuitive. Sorry. If you include an idtype without the ':' mark, we won't see it and may not generate enough joins to connect the idtype to the rest of the query.

"translate" automatically applies histories, when they exist, to input and filter ids. In other words, input and filter ids can be ones that were valid in the past but are not valid now. Output ids, however, are always current.

CAUTION: If the input type is also used as an output, the result can contain rows in which the output id does not equal the input id. This will occur if the input id is old and is mapped to a different current value. Likewise, if a filter type is used as an output, the result can contain rows in which the output id does not match the filter.

Title : count
Usage : $number=$babel->count
(input_idtype=>'gene_entrez',
input_ids=>[1,2,3],
filters=>{chip_affy=>'hgu133a'},
output_idtypes=>[qw(transcript_refseq transcript_ensembl)])
Function: Count number of output rows that would be generated by "translate"
Returns : number
Args : same as "translate"

"count" is a wrapper for "translate" that sets the "count" argument to a true value.

Title : validate
Usage : $table=$babel->validate
(input_idtype=>'gene_entrez',
input_ids=>[1,2,3])
Function: Tell which input ids are valid now or in the past, and the mapping
from old to current values
Returns : table represented as an ARRAY of ARRAYS. Each inner ARRAY is one row
of the result. If output_idtypes is omiited (the usual case), the
elements of each row are
0) input id as given
1) validity status. 1 for valid; 0 for invalid
2) current value of the id or undef if it has no current value; may
be the same as the original id
If output_idtypes is set, the result is ther same as "translate" with
the "validate" option set
Args : same as "translate"

"validate" looks up the given input ids in the Master tables for the given input type and returns a table indicating which ids are valid. For types with history information, the method also indicates the current value of the id. For types that have no history, the current value will always equal the given id if the id is valid.

"validate" can also retrieve a complete table of valid ids (along with history information) for the type.

"validate" is a wrapper for "translate" that (1) sets the "validate" argument to a true value and (2) sets the output_idtypes argument to the input_idtype unless the user explicitly set it. All other "translate" arguments (filters, count) are legal here and work but are of dubious value.

For rows whose validity status is 1 (valid), the given id and current value indicate the history: if the elements are equal, the given id is current; else if the current value is defined, the given id has been replaced by the new one; else the given id was valid in the past but has no current value.

For types that have no history, all valid ids are current. If the given id is valid, the given id and current value will be equal; else the current value will be undef.

For rows whose status is 0 (invalid), the current value will always be undef.

The "translate" arguments 'filters' and "count" are legal here and work but are of dubious value.

Babel creates 'implicit' Masters for any IdTypes lacking explicit ones. An implicit Master has a list of valid identifiers and could be implemented as a view over all MapTables containing the IdType. In the current implementation, we use views for IdTypes contained in single MapTables but construct actual tables for IdTypes contained in multiple MapTables.

Objects have names and ids: names are strings like 'gene_entrez' and are unique for a given class of object; ids have a short form of the type prepended to the name, eg, 'idtype:gene_entrez', and are unique across all classes. We use ids as nodes in schema and query graphs. In most cases, applications should should use names.

The methods in this section map names or ids to component objects, or (as a trivial convenience), convert ids to names.

Title : name2idtype
Usage : $idtype=$babel->name2idtype('gene_entrez')
Function: Get the IdType object given its name
Returns : Data::Babel::IdType object or undef
Args : name of object
Notes : only looks at this Babel's component objects

Title : name2master
Usage : $master=$babel->name2master('gene_entrez_master')
Function: Get the Master object given its name
Returns : Data::Babel::Master object or undef
Args : name of object
Notes : only looks at this Babel's component objects

Title : name2maptable
Usage : $maptable=$babel->name2maptable('maptable_012')
Function: Get the MapTable object given its name
Returns : Data::Babel::MapTable object or undef
Args : name of object
Notes : only looks at this Babel's component objects

name eg, 'gene_entrez_master'
id name prefixed with 'master::', eg, 'master:::gene_entrez_master'
idtype Data::Babel::IdType object for which this is the Master
implicit boolean indicating whether Master is implicit
explicit opposite of implicit
view boolean indicating whether Master is implemented as a view
history boolean indicating whether Master contains history information.
tablename synonym for name
inputs, namespace, query
DEPRECATED - intended for use by our database construction
procedure but not actually used

name eg, 'gene_entrez_master'
id name prefixed with 'maptable', eg, 'maptable:::gene_entrez_master'
idtypes ARRAY of Data::Babel::IdType objects contained by this MapTable
inputs, namespace, query
DEPRECATED - intended for use by our database construction
procedure but not actually used

A Data::Babel::Filter object represents a condition limiting the output of a Data::Babel "translate", "validate", or "count" query. (Hereafter, we will refer only to "translate", but everything applies to the other methods, too). In typical usage, code in Data:Babel generates Filter objects automatically based on the "filters" argument to "translate". Application code rarely needs to create these objects directly.

Recall that the "filters" argument to "translate" typically consists of idtype=>conditions pairs. The code generates a Filter object for each of these pairs.

In typical usage, "conditions" is the right hand side of an "idtype=>conditions" pair in the "filters" argument to "translate". It can also be the complete value of the "filters" argument when it is set to a string or string reference. "conditions' can contain ids that are combined for use in SQL IN clauses or fragments of actual SQL.

SQL fragments may contain 'embedded IdTypes'. These are IdTypes names prefixed by a marker, typically ':', for example ':gene_symbol' (without the quotes!!). An example of such a fragment is

:gene_symbol LIKE 'casp%' OR :gene_symbol = 'Htt'

If "filter_idtype" is set (it usually is), you can use the marker without the name to denote the "filter_idtype". For example, if "filter_idtye" is "gene_symbol", you could write the previous SQL fragment as

: LIKE 'casp%' OR : = 'Htt'

By default, we treat string conditions as ids, and references to strings as SQL fragments. You can change this via the "treat_string_as" and "treat_stringref_as" arguments.

By default, we prepend the "filter_idtype" argument to SQL fragments unless an embedded IdType (with or without a name) is the first thing in the fragment. For example, if "filter_idtye" is "gene_symbol", you can express the SQL clause

gene_symbol LIKE 'casp%'

with any of these fragments

LIKE 'casp%'
: LIKE 'casp%'
:gene_symbol LIKE 'casp%'

You can change the prepending behavior via the "prepend_idtype" argument. If set to any true value except 'auto' we always prepend, and if false we never prepend.

The "conditions" argument can contain arbitrarily complex SQL, but we expect most cases to be simple. Simple cases, like the example above, use the "filter_idtype" in a single condition. In such cases, you don't need to use embedded IdTypes. You can also express ORs of simple conditions without resorting to embedded IdTypes by putting the conditions in an ARRAY; see "Details on conditions". For example, if "filter_idtye" is "gene_symbol", you can express the SQL clause

The "conditions" argument may be one or an ARRAY of the following. An ARRAY represents the OR of its elements.

string. Id or SQL fragment depending on the value of the "treat_string_as" argument. The default is "id".

reference to string. SQL fragment or id depending on the value of the "treat_stringref_as" argument. The default is "SQL".

Data::Babel::Filter object. This has little utility by itself, but in an ARRAY it causes the old object to be ORed onto the rest of the conditions.

undef. When used standalone, it is equivalent to the SQL fragment "IS NOT NULL". In typical cases this means that all ids of "filter_idtype" are acceptable, similar to what it means for "input_ids" to be undef in "translate". In an ARRAY it has the opposite meaning, which in typical usage lets the output contain rows for which "filter_idtype" is NULL. It may seem strange for undef to have opposite meanings depending on context, but is natural in practice.

If "conditions" contains multiple ids, we combine them into a single SQL IN clause. For example, if "filter_idtye" is "gene_symbol" and "conditions" is ['Htt','Casp6','Ins2'], we generate

gene_symbol IN ('Htt','Casp6','Ins2')

The "conditions" argument can be "empty" in several ways.

empty id. This is completely normal and generates SQL to match an empty string.

empty SQL fragment, typically encoded as a reference to an empty string, ie, \''. This generates the SQL FALSE. When used in an ARRAY, this has no effect, because an ARRAY represents the OR of its elements and ORing FALSE to anything is a nop.

To process complex SQL conditions, we need to identify the IdTypes used by the condition for two purposes. (1) "translate" needs these to to find the MapTables it must join to connect the IdTypes, and (2) for IdTypes with histories, we have to prepend the IdType name with '_X_' whenever the IdType is compared to a constant so that the history mapping will be applied.

To do this without embedded IdType markers, we would need to find or develop a SQL parser that creates a parse tree that we can examine to find the IdTypes, modify to handle histories, and convert back to SQL after being modified. Because SQL parsing is technically challenging, maintainability is a crucial concern: it would be very unpleasant to incorporate a module that works for our purposes today but ceases to do so in a future release.

We investigated several CPAN modules that do SQL parsing.

SQL::Statement seems to be under active development. The SQL dialect it supports is incomplete but probably adequate for our needs. The parse tree it produces is easy to work with but is not documented and presumably might change in future releases. The showstopper is that it has no method for converting the parse tree back to SQL.

DBIx::MyParsePP does most of what we want but hasn't been updated in years, which raises worries about long term maintainability. It implements the MySQL 5.0 SQL dialect, which is fine for our purposes. It is slow to load because the grammar is big,

DBIx::MyParse is a C implementation of DBIx::MyParsePP. We didn't test it, because it requires access to MySQL source, which you have to patch (!!). It seems unlikely that this could be incorporated into the normal CPAN installation process.

SQL::Abstract::Parser looks pretty good, but the documentation cautions against relying on the structure of the parse tree at this point. This might offer a future solution when the developers declare the parse tree format to be stable.

We considered developing our own parser for a mini-SQL dialect limited to clauses separated by AND, OR, and NOT. Even this is hard because of SQL constructs like 'BETWEEN n1 AND n2'.

Our conclusion is that while it is inelegant to require embedded IdType markers, this is the only practical solution at present.