Custom Views ============ Custom views are just a special type of scheme which obtain their fields and data from other schemes or user defined fields (custom columns). Data for a custom view can be obtained with the *process_custom_view_query* method in entero.ExtraFuncs.query_functions Representation in the *user_preferences* Table ---------------------------------------------- Custom Views are specified by an entry in the user_preferences table with type of 'custom_view' e.g. +---------+-----------+--------------------------------------------------+----------+--------------+--------------+ | **id** |**user_id**|**data** |**name** |**type** |**database** | +---------+-----------+--------------------------------------------------+----------+--------------+--------------+ | 46465 | 55 |{"experiment_columns":{..}","custom_columns":{..}}|View 101 |custom_view |senterica | +---------+-----------+--------------------------------------------------+----------+--------------+--------------+ The data is in json format consisting of experiment_columns and custom_columns * **experiment_columns** consists of list of entries containing the following * **name**: the name of the field (as specified in dataprams) * **scheme**: the name of the scheme (the description in the schemes table) * **datatype**: either text,integer or double * **label**: what the user will see * **non_locus**: should be "true". Can be omitted if the column is an allele * **display_order**: the order in the table * **custom_columns** a list of a the following * **id**: the id (in the user_preferences table) of the custom column * **display_order** :the order in the table A simple example .. code-block:: json { "experiment_columns":[ {"name":"st","scheme":"MLST_Achtman", "datatype":"integer", "label":"ST(Achtman 7 Gene)", "non_locus":"true", "display_order":1}, {"name":"STMMW_13251", "scheme":"wgMLST", "datatype":"integer", "label":"STMMW_13251 (katE)", "display_order":2} ], "custom_columns":[ {"id":"6145", "display_order":"0"} ] } Obtaining Data for Custom Views -------------------------------- Data for a custom view can be obtained from the following method .. code-block:: bash process_custom_view_query(database,exp,query,query_type,extra_data=None) In order for compatibility with other schemes, the parameter *exp* is none, instead the custom view is specified in the *extra_data* parameter. This can either be the id of the custom view or a dictionary describing the view (see above), this way a view can be created on the fly. The query parameter can only be custom columns (querying multiple experiment/schemes using different methods would just be to complex) . An example of a query which uses the custom columns ID would be .. code-block:: "6762" = 'blue' AND "5666" = 'red' Examples of Obtaining Data ^^^^^^^^^^^^^^^^^^^^^^^^^^ **Obtaining Fields From different Schemes for Specified Assembly IDs** e.g. In order to get 7 gene ST, rST and the allele value for STMMW_13251 from wGMLST for two assemblies whose ids are 100023 and 100024, the following code could be used .. code-block:: python from entero.ExtraFuncs.query_functions import process_custom_view_query extra_data={"experiment_columns": [{ "name":"st", "scheme":"MLST_Achtman" }, { "name":"STMMW_13251", "scheme":"wgMLST" }, { "name":"st", "scheme":"rMLST", }] } data = process_custom_view_query("senterica",None,"100023,100024","assembly_ids",extra_data) print data The returned dictionary will be .. code-block:: {100024: {'st_MLST_Achtman': 239, 'st_rMLST': 32559, 'STMMW_13251_wgMLST': 792 }, 100023: {'st_MLST_Achtman': 214, 'st_rMLST': 7970, 'STMMW_13251_wgMLST': 599} } **Querying a Custom View with a Custom Column** In order to query the 'typhi genotype' custom_view (id 4077) for all assemblies with a genotype (custom column id 4076) of 4.1 or sub genotypes e.g 4.1.2, 4.1.0 etc., the following code would be used. .. code-block:: python from entero.ExtraFuncs.query_functions import process_custom_view_query #SQL query using the custom column (id 4076) query = "\"4076\" LIKE '4.1%'" #The id of the custom_view extra_data=4077 data = process_custom_view_query("senterica",None,query,"query",4077) print data The returned data is a dictionary of assembly ids to all the fields in the typhi genotype custom view, which includes cgST,rST as well as 7 gene ST. .. code-block:: json {23555: {"st_MLST_Achtman": 1, "st_rMLST": 1416, "4076": "4.1.0", "st_cgMLST_v2": 31264 }, 23559: {"st_MLST_Achtman": 1, "st_rMLST": 1416, "4076": "4.1.0", "st_cgMLST_v2": 31264 }, } **Querying with a Custom Column and Specifying the Returned Fields** You can query using custom columns and specify any custom data to be returned. However the returned data has to include all the custom columns used in the query. For example if you wanted the serotype prediction for all typhi genotypes of 4.1.X (custom colum id 4076), the following code could be used .. code-block:: python from entero.ExtraFuncs.query_functions import process_custom_view_query #SQL query using the custom column (id 4076) query = "\"4076\" LIKE '4.1%'" #Retrieve predicted serovar extra_data={"experiment_columns":[{ "scheme":"SeroPred", "name":"serovar" }], "custom_columns":[{ "id":"4076" }] } data = process_custom_view_query("senterica",None,query,"query",extra_data) print data Not surprisingly all the returned data gives Typhi as the predicted serovar .. code-block:: json {23555: {"4076": "4.1.0", "serovar_SeroPred": "Typhi"}, 23559: {"4076": "4.1.0", "serovar_SeroPred": "Typhi"}. ... } Custom Columns -------------- Representation in the *user_preferences* Table ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Custom columns are specified by an entry in the user_preferences table with type of 'custom_column' e.g. +---------+-----------+-----------------------------------+----------+--------------+--------------+ | **id** |**user_id**|**data** |**name** |**type** |**database** | +---------+-----------+-----------------------------------+----------+--------------+--------------+ | 4646 | 55 |{"datatype":"text","label":"color"}|color |custom_column |senterica | +---------+-----------+-----------------------------------+----------+--------------+--------------+ The name field is redundant but usually the same as the label Adding Data to Custom Columns ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ The actual data for custom column is kept in the custom_columns field of the strain database and consists of a dictionary of id to value e.g. +---------+-----------+------+----+------------------------+ | **id** |**strain **|... |... |**custom_columns** | +---------+-----------+------+----+------------------------+ | 16465 | bug1 |... |... |{"4646":"red"} | +---------+-----------+------+----+------------------------+ Data for a custom column can be added by the static method update_custom_data in the CustomView class of entero.ExtraFuncs.workspace e.g. To add custom column data to strains whose ids are 13535 and 13539 .. code-block:: python from entero.ExtraFuncs.workspace import CustomView data = { "13535":{ "6762":"blue, "8721":"yes" }, "13539":{ "6762":"red, "8721":"no" } } CustomView.update_custom_data("senterica",data)