Test script for MATCH_SUBSTRUCT in mychem

testmychemmatch.sql

CREATE DATABASE mychem_match_test;
USE mychem_match_test;
CREATE TABLE `compound_mol` (
   `compound_id` int(11) NOT NULL AUTO_INCREMENT,
   `mol` text,
   PRIMARY KEY (`compound_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `compound_mol` (`mol`) VALUES (SMILES_TO_MOLECULE('c1ccccc1')), (SMILES_TO_MOLECULE('c1ccccc1O')), (SMILES_TO_MOLECULE('c1cnccc1'));
CREATE TABLE `compound_ser_mol` (
   `compound_id` int(11) NOT NULL,
   `serializedOBmol` blob,
   PRIMARY KEY (`compound_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `compound_ser_mol` (`compound_id`, `serializedOBmol`) SELECT `compound_id`, MOLECULE_TO_SERIALIZEDOBMOL(`mol`) FROM `compound_mol`;

SELECT 'SELECT MATCH_SUBSTRUCT(\'c1ccccc1\', MOLECULE_TO_SERIALIZEDOBMOL(SMILES_TO_MOLECULE(\'c1ccccc1O\'))) AS match_from_smiles FROM `compound_mol`' as '1'\G
SELECT MATCH_SUBSTRUCT('c1ccccc1', MOLECULE_TO_SERIALIZEDOBMOL(SMILES_TO_MOLECULE('c1ccccc1O'))) AS match_from_smiles FROM `compound_mol`;

SELECT 'SELECT MATCH_SUBSTRUCT(\'c1ccccc1\', MOLECULE_TO_SERIALIZEDOBMOL(`mol`)) AS match_from_moleculefield FROM `compound_mol`' as '2'\G
SELECT MATCH_SUBSTRUCT('c1ccccc1', MOLECULE_TO_SERIALIZEDOBMOL(`mol`)) AS match_from_moleculefield FROM `compound_mol`;

SELECT 'SELECT MATCH_SUBSTRUCT(\'c1ccccc1\', `serializedOBmol`) AS match_from_sermolfield FROM `compound_ser_mol`' as '3'\G
SELECT MATCH_SUBSTRUCT('c1ccccc1', `serializedOBmol`) AS match_from_sermolfield FROM `compound_ser_mol`;

SELECT 'SET @mol = SMILES_TO_MOLECULE(\'c1ccccc1O\');' as '4', 'SELECT MATCH_SUBSTRUCT(\'c1ccccc1\', MOLECULE_TO_SERIALIZEDOBMOL(@mol));' as '5'\G
SET @mol = SMILES_TO_MOLECULE('c1ccccc1O');
SELECT MATCH_SUBSTRUCT('c1ccccc1', MOLECULE_TO_SERIALIZEDOBMOL(@mol));