PostgreSQL notes
From I571/ChE531 2007 Class Wiki
This example uses the gNova CHORD package with PostgreSQL.
Create a new database to store SMILES, name, logp and fingerprint
create table djwildtest (smiles VARCHAR(200), name VARCHAR(50), logp real, fkey BIT(166));
Check it is there
\d djwildtest
Insert our 8 molecules into the database
INSERT INTO djwildtest (smiles, name, logp) VALUES ( 'CC(=O)Nc1ccc(O)cc1', 'Acetaminophen', 0.27 ); INSERT INTO djwildtest (smiles, name, logp) VALUES ( 'CC(C)NCC(O)COc1ccccc1CC=C', 'Alprenolol', 2.81 ); INSERT INTO djwildtest (smiles, name, logp) VALUES ( 'CC(N)Cc1ccccc1', 'Amphetamine', 1.76 ); INSERT INTO djwildtest (smiles, name, logp) VALUES ( 'CC(CS)C(=O)N1CCCC1C(=O)O', 'Captopril', 0.84 ); INSERT INTO djwildtest (smiles, name, logp) VALUES ( 'CN(C)CCCN1c2ccccc2Sc3ccc(Cl)cc13', 'Chlorpromazine', 5.20 ); INSERT INTO djwildtest (smiles, name, logp) VALUES ( 'OC(=O)Cc1ccccc1Nc2c(Cl)cccc2Cl', 'Diclofenac', 4.02 ); INSERT INTO djwildtest (smiles, name, logp) VALUES ( 'NCC1(CC(=O)O)CCCCC1', 'Gabapentin', -1.37 ); INSERT INTO djwildtest (smiles, name, logp) VALUES ( 'COC(=O)c1ccccc1O', 'Salicylate', 2.60 );
Use the gNoval public166keys function to create fingerprints from the SMILES and put them in the fkey field
update djwildtest set fkey = public166keys(smiles);
Take a look at what we have
select smiles,name,logp from djwildtest; select * from djwildtest;
Do a substructure search for structures containing a Carboxyllic Acid
select smiles,name,logp from djwildtest where matches(smiles, '*C(=O)O');
Refine search to only those with LogP > 1
select smiles,name,logp from djwildtest where (matches(smiles, '*C(=O)O') AND (logp>1.0));
Do a similarity search with Aspirin
select smiles,name,logp from djwildtest where tanimoto(fkey, public166keys('CC(=O)Oc1ccccc1C(=O)O')) > 0.6;