mmCQL supported commands documentation?

[CV-GPhL]

Is there more documentation about the commands mmCQL supports? Or a set of examples (e.g. count how many residues have an altConf, how many hydrogens there are, how many OCC<1 etc)?

[drlemmus]

Hi Clemens, there is not that much documentation, but I'll dig up some examples later and post them here.

[mhekkel]

Perhaps I should spend some time on documenting this tool, as well as extending its functionality. It is on my to-do list

[mhekkel]

man mmCQL is all there is right now, and it is limited...

[drlemmus]

echo "SELECT label_atom_id FROM atom_site WHERE label_comp_id = 'HOH';" | /zata/tools/mmCQL $WORKDIR/cache.cif | grep -c O is used to count waters. Count is not implemented yet, but could be added.
echo "SELECT label_atom_id FROM atom_site WHERE type_symbol = 'S';" | /zata/tools/mmCQL tempdir/1cbs/1cbs.xyz.cif list all sulfur atoms.
You can also use cif-grep for things: ./cif-grep -v -i _atom_site.type_symbol H prepped.cif | wc - counts the non-hydrogen atoms.
echo "SELECT label_seq_id,label_asym_id FROM atom_site WHERE NOT label_alt_id = '.';" | /zata/tools/mmCQL /DATA/mmCIF/b8/2b8h.cif.gz | sort -u | wc -l counts the number of residues with an alt id (off by one because of the table head).

[mhekkel]

So, SELECT, UPDATE and DELETE are implemented. INSERT not yet.

It is a side project I'm happy to work on, and there are lots of things that can be added. But I'd like to know what is needed. From Robbies comment I understand that a SELECT COUNT(*) FROM xxx is e.g. required.

[drlemmus]

INSERT would also be nice to have to add a software record in the end.

[CV-GPhL]

Thanks for the quick replies!

I haven't looked at it yet in anger, but just out of the top of my head (and thinking a bit like SFTOOLS):

SELECT * FROM atom_site           # all rows/columns from atom-site category
SELECT WHERE label_alt_id = 'A'   # keep records with this feature
SELECT WHERE occupancy < 0.5      # boolean AND to previous selection
SELECT INVERT                     # invert selection
SET occupancy = 0.99              # now (re)setting all occupancies of the active records

which I would probably currently do for PDB using something like

awk '/^ATOM|^HETATM/{
  ac=substr($0,17,1);o =substr($0,55,6)-0.0
  if(ac!="A"||o>=0.5) {printf("%s  0.99%s\n",substr($0,1,54),substr($0,61))} else {print}
  next
}
{print}' in.pdb > out.pdb

How can one INSERT (add if it doesn't exist yet, but do nothing if it does exist) or UPDATE (replace if it exists, but do nothing if it doesn't) a single row, column or value? Anyway, if whatever is currently implemented could be documented with this kind of examples (maybe even with a tutorial using a real PDB entry) it would be very much appreciated. Afterwards one could add additional requests/ideas as they come along (maybe with an example how one would do that e.g. with PDB files).

[drlemmus]

UPDATE and SET are already implemented. For instance, I use this to make sure the cell dimensions on the reflection data an model are the same:
#Reset the cell dimensions

  ./mmCQL -V --force 1abc.xyz.cif 1abc_cell.cif <<eof >& mmCQL.log
    UPDATE cell SET length_a = $RAAXIS, length_b = $RBAXIS, length_c = $RCAXIS, angle_alpha = $RALPHA, angle_beta = $RBETA, angle_gamma = $RGAMMA;
eof

[drlemmus]

I don't think numerical comparison works yet.

[mhekkel]

UPDATE and SET are already implemented. For instance, I use this to make sure the cell dimensions on the reflection data an model are the same: #Reset the cell dimensions

  ./mmCQL -V --force 1abc.xyz.cif 1abc_cell.cif <<eof >& mmCQL.log
    UPDATE cell SET length_a = $RAAXIS, length_b = $RBAXIS, length_c = $RCAXIS, angle_alpha = $RALPHA, angle_beta = $RBETA, angle_gamma = $RGAMMA;
eof

Please note that Robbie uses tcsh...

[mhekkel]

I don't think numerical comparison works yet.

They should work, if they don't you will have to submit a bug report since the code has an implementation for this.

[drlemmus]

In bash:

rjoosten@laue:/zata$ echo 'SELECT label_atom_id,label_asym_id FROM atom_site WHERE id < 400;' | tools/mmCQL tempdir/100d/100d.xyz.cif
label_atom_id   label_asym_id
rjoosten@laue:/zata$ echo 'SELECT label_atom_id,label_asym_id FROM atom_site WHERE id = 400;' | tools/mmCQL tempdir/100d/100d.xyz.cif
label_atom_id   label_asym_id
N7      B

I'll make an issue.

[mhekkel]

libcifpp will get full SQL support using the SQLite library. The 'develop' branch of cif-tools contains a new mmCQL that uses this feature. That means almost all of the SQL standard is implemented as described here: https://sqlite.org/lang.html

The mmCQL tool has an interactive mode and a script mode.

Suggestions for improvement are welcome. Beware, this is still development code.