Molecular Fragments and Fingerprints - Design and Use of Relational Databases in Chemistry - page 94

abit represents which bit in the key will be set if the structure contains
that fragment. The column named description is a brief description of
the fragment. Consider the result of the following SQL.
Select abit from fragments Where matches ('c1ccccc1O', smarts);
The result is two rows:
1
3
because phenol is matched by only two SMARTS from the table fragments,
namely those with abit=1 (phenyl) and abit=3 (alcohol). A slight modifica-
tion shows how a bit string can be created.
Select B'1'::bit(50)>>abit-1 from fragments
Where matches('c1ccccc1O', smarts);
This arbitrarily assumes the final result will be 50 bits, suitable for a
table of fragments having 50 or fewer rows. The result of this SQL is:
10000000000000000000000000000000000000000000000000
00100000000000000000000000000000000000000000000000
The first row is a bit string of length 50 having bit #1 set; the second row
has bit #3 set. This is getting closer to the desired single value key having
both bit #1 and bit #3 set. How can these rows be combined into a single
bit string with bit #1 and bit #3 set? An aggregate function similar to sum
would provide the correct result. But there is no standard SQL aggregate
function such as this that operates on bit strings. The Appendix to this
topic shows the deinition of such a function, called orsum . Using that
function, the final definition of the key function is:
Create Function key(text) Returns bit varying As $$
Select orsum(B'1'::bit(50)>>abit-1) from fragments
Where matches($1, smarts);$$ Language SQL;
The result of Select key('c1ccccc1O') is the single value:
10100000000000000000000000000000000000000000000000
This is the fragment key for phenol. The key function can be used to com-
pute and store values of the fragment key in tables of molecular structures.
It can also be used to compute values of fragment keys for substructures
to be used as a prescreen during a full substructure search using the
matches function.