{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 在PostgreSQL中进行分子相似性搜索"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 一、分子指纹计算\n",
    "本文介绍在windows环境下，使用rdkit函数在postgresql数据库中进行相似性搜索。环境搭建、数据表准备不再赘述，可以参考[postgresql_substructure_search.ipynb]中的介绍。在上述工作基础上，继续进行指纹计算、建立索引。操作之前先看看在postgresql中支持的指纹函数：\n",
    "- layered_fp(mol)：另一种rdkit原创指纹，官方文档的解释是它一种子结构指纹，与rdkit拓扑子图的生成步骤一致，但根据子图生成指纹向量的过程有所不同。在子结构指纹类别中，layered指纹的表现不如pattern指纹，因此不像其他指纹这么被人所熟知。\n",
    "- torsionbv_fp(mol)：对分子计算topological-torsion的bfp（bit fingerprint）型指纹\n",
    "- torsion_fp(mol)：计算topological-torsion的sfp（sparse fingerprint）型指纹\n",
    "- morganbv_fp(mol, int default 2)：计算指定半径（默认为2）的morgan型bfp指纹\n",
    "- morgan_fp(mol, int default 2)：计算morgan的sfp指纹\n",
    "- featmorganbv_fp(mol, int default 2)：计算morgan FCFP（用官能团归类）的bfp指纹\n",
    "- featmorgan_fp(mol, int default 2)：计算morgan FCFP的sfp指纹\n",
    "- atompairbv_fp(mol)：atompair的bfp指纹\n",
    "- atompair_fp(mol)：atompair的sfp指纹\n",
    "- rdkit_fp(mol)：rdkit的拓扑指纹\n",
    "- maccs_fp(mol)：预定义的MACCS指纹\n",
    "\n",
    "\n",
    "接下来在postgresql中计算分子指纹\n",
    "- 根据rdk.mols中的m列（存放分子的mol对象），计算torsion、morgan ECFP、morgan FCFP指纹，分别命名为torsionbv、mfp2、ffp2，并放入到rdk.fps表中\n",
    "\n",
    "```sql\n",
    "select id, torsionbv_fp(m) as torsionbv, morganbv_fp(m) as mfp2, featmorganbv_fp(m) as ffp2 into rdk.fps from rdk.mols;\n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "- 分别对torsionbv、mfp2、ffp2建立索引\n",
    "\n",
    "```sql\n",
    "create index fps_ttbv_idx on rdk.fps using gist(torsionbv);\n",
    "create index fps_mfp2_idx on rdk.fps using gist(mfp2);\n",
    "create index fps_ffp2_idx on rdk.fps using gist(ffp2);\n",
    "```\n",
    "\n",
    "# 二、相似性搜索\n",
    "进行相似性比对和搜索需要用到相似性搜索相关的操作符：\n",
    "- 百分号\"%\"：使用tanimoto相似性作为标准进行相似性搜索，返回的结果是给定分子与库中分子的相似性是否超过阈值（通过rdkit.tanimoto_threshold设置），用在where条件后，可以得到达到一定相似性的分子\n",
    "- 井号\"#\"，与上面的操作符类似，使用dice相似性，阈值可通过rdkit.dice_threshold进行设置。\n",
    "- 带尖括号的百分号\"<%>\"：使用tanimoto最近邻搜索\n",
    "- 带尖括号的井号\"<%>\"：使用dice最近邻搜索。\n",
    "\n",
    "\n",
    "- 接下来使用tanimoto系数作为筛选标准，对新输入分子进行相似性搜索\n",
    "```sql\n",
    "select count(*) from rdk.fps where mfp2%morganbv_fp('O=C1CN=C(c2ccccn2)c2ccccc2N1');\n",
    "```\n",
    "\n",
    "\n",
    "- 通过设置相似性的阈值，来对结果进行限制\n",
    "```sql\n",
    "set rdkit.tanimoto_threshold=0.3;\n",
    "```\n",
    "\n",
    "\n",
    "- <%>的使用\n",
    "```sql\n",
    "select id, mfp2<%>morganbv_fp('O=C1CN=C(c2ccccn2)c2ccccc2N1') knn from rdk.fps order by knn asc;\n",
    "```\n",
    "\n",
    "# 三、自定义搜索函数\n",
    "如果自带的函数无法满足需求，还可以通过create or replace function来创建一个新的函数。用下面的自定义函数为例进行介绍：\n",
    "- 自定义函数的名称为get_mfp2_neighbors，该函数可以接收一个text类型的参数smiles\n",
    "- 自定义函数的返回值为一个table，包含了int类型的id，mol类型的m，双精浮点类型的similarity\n",
    "- 该函数结果是由两个表按id连接形成，第一个表来自rdk.fps，该表中包含了id，m，similarity，其中similarity是给定分子与库中分子morgan指纹的tanimoto相似性系数。第二个表来自rdk.mols\n",
    "- 两个表合并后，做一步筛选，只有与给定分子的相似性到达阈值才能被展示。最后再排个序返回\n",
    "\n",
    "```sql\n",
    "create or replace function get_mfp2_neighbors(smiles text)\n",
    "returns table(id int, m mol, similarity double precision) as\n",
    "$$\n",
    "select id, m, tanimoto_sml(morganbv_fp(mol_from_smiles($1::cstring)), mfp2) as similarity from rdk.fps join rdk.mols using (id)\n",
    "where morganbv_fp(mol_from_smiles($1::cstring))%mfp2\n",
    "order by morganbv_fp(mol_from_smiles($1::cstring))<%>mfp2;\n",
    "$$ language sql stable;\n",
    "```\n",
    "\n",
    "\n",
    "- 测试一下自定义函数\n",
    "```sql\n",
    "select * from get_mfp2_neighbors('O=C1CN=C(c2ccccn2)c2ccccc2N1') limit 10;\n",
    "```\n",
    "\n",
    "\n",
    "- 因为自定义函数中用到了tanimoto系数，同样可以调整阈值来限制结果，感兴趣的可以自己尝试\n",
    "\n",
    "本文参考自[rdkit官方文档](http://www.rdkit.org/docs/Cartridge.html)。"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}