Skip to content

Instantly share code, notes, and snippets.

@fredrikw
Created May 19, 2015 09:54
Show Gist options
  • Save fredrikw/991afe40f00cbca0f258 to your computer and use it in GitHub Desktop.
Save fredrikw/991afe40f00cbca0f258 to your computer and use it in GitHub Desktop.
Test script for MATCH_SUBSTRUCT in mychem
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));
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment