Read and write protein structure mmcif files using SQL
Maintainer(s):
sverhoeven
Compatibility Matrix
This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.
| Platform | 1.4.4 | 1.4.5 | 1.5.5 | 1.5.6 | 2.0.0 |
|---|---|---|---|---|---|
| Linux AMD64 | |||||
| Linux ARM64 | |||||
| macOS AMD64 | |||||
| macOS ARM64 | |||||
| Windows AMD64 | |||||
| Windows ARM64 | |||||
| WebAssembly |
Installing and Loading
INSTALL mmcif FROM community;
LOAD mmcif;
Example
-- single table
SELECT count(*) FROM mmcif_scan('https://files.rcsb.org/download/1AMB.cif.gz', 'atom_site');
-- 438
-- All tables in file
ATTACH 'https://files.rcsb.org/download/1AMB.cif.gz' AS mmcifdb (TYPE mmcif);
USE mmcifdb;
SELECT count(*) FROM information_schema.tables WHERE table_catalog = 'mmcifdb';
-- 43
SELECT count(*) FROM atom_site;
-- 438
-- Positions of Zinc atoms
SELECT Cartn_x, Cartn_y, Cartn_z FROM atom_site WHERE type_symbol = 'ZN';
-- Write mode
ATTACH '1amb_updated.cif' AS wdb (TYPE mmcif, READ_WRITE TRUE);
USE wdb;
BEGIN;
INSERT INTO atom_site (label_atom_id, Cartn_x, type_symbol) VALUES ('O1', 3.5, 'O');
UPDATE atom_site SET type_symbol='ZZ' WHERE label_atom_id='O1';
DELETE FROM atom_site WHERE label_atom_id='O1';
COMMIT; -- writes the mutated tables back to the attached file
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| mmcif_blocks | table | List data block names in file order, without the data_ prefix. | NULL | [SELECT * FROM mmcif_blocks('structures.cif');] |
| mmcif_columns | table | List the categories and columns in an mmCIF file, with their dictionary documentation links and inferred types. Select a block with data_block := 'name'; defaults to the first block. | NULL | [SELECT * FROM mmcif_columns('https://files.rcsb.org/download/1AMB.cif.gz');] |
| mmcif_relationships | table | List the parent/child key relationships between the categories in an mmCIF file, each side as a (table, column) pair. Select a block with data_block := 'name'; defaults to the first block. | NULL | [SELECT * FROM mmcif_relationships('https://files.rcsb.org/download/1AMB.cif.gz');] |
| mmcif_scan | table | Scan one mmCIF category from a file path, glob, or list of paths/globs. Plain CIF and gzip inputs are supported. Multiple matched files add a filename column containing the concrete source path. Select a block with data_block := 'name'; defaults to the first block. Use column_source := 'dictionary' (default) for all bundled category columns and lazy parallel readers capped by threads. Missing items are typed NULLs and absent known categories yield no rows. Unknown categories or items in the requested category error; use column_source := 'files' to discover the column union from input files and include custom items. | NULL | [SELECT * FROM mmcif_scan('https://files.rcsb.org/download/1AMB.cif.gz', 'atom_site');, SELECT count() FROM mmcif_scan('structures/**/.cif.gz', 'atom_site');] |
| mmcif_tables | table | List the categories in an mmCIF file with their dictionary documentation links and column counts, single-row constraints, descriptions, category groups and mandatory flags. Dictionary metadata is independent of observed rows; unavailable metadata is NULL. Select a block with data_block := 'name'; defaults to the first block. | NULL | [SELECT * FROM mmcif_tables('https://files.rcsb.org/download/1AMB.cif.gz');] |
Overloaded Functions
This extension does not add any function overloads.
Added Types
This extension does not add any types.
Added Settings
| name | description | input_type | scope | aliases | typed_value |
|---|---|---|---|---|---|
| temp_directory | Set the directory to which temporary files are written. | VARCHAR | GLOBAL | [] | post.db.tmp |