CoCalc Shared FilesPublic / DatabaseQueries.mdOpen in CoCalc with one click!
Author: Hal Snyder
Views : 68
Description: Using the query API call

CoCalc API Tutorial - Database Queries

1. The CoCalc schema file

CoCalc keeps information about accounts, projects, and other data in a database. The API provides limited access to this database. To see which files and fields are accessible, see CoCalc source file db-schema.coffee.

There is a complete explanation of how the schema is setup in the comments at the top of the schema file, just after the licensing boilerplate. Take a moment to read that part of the file now.

Here's an example, with excerpts from the schema file. Information about projects is kept in the projects table. Here's the beginning of the projects table definition:

schema.projects =
    primary_key: 'project_id'
    fields :
        project_id  :
            type : 'uuid',
            desc : 'The project id, which is the primary key that determines the project.'

Further down, indented under schema.projects, you find the list of fields which an API user can read (get) and write (set):

    user_query:
        get :
            pg_where     : 'projects'
            pg_changefeed: 'projects'
            throttle_changes : 2000
            fields :
                project_id     : null
                title          : ''
                description    : ''
...
        set :
            fields :
                project_id     : 'project_write'
                title          : true
                description    : true

There is extensive additional information in the comments, including further access limitations, explanations of how a field is used, and default values.

The query API call can be used to get or set values in the database. The rule is, if any attribute in the query object is null, the query is a get; if all values specified for fields are not null, the query is a set.

2. Getting values from the database

To get values from a table using the API, you provide table name, key to the record you're interested in, and fields you would like to retrieve. Think of the query payload as a template for the record you'd like to retrieve, including only the fields you're interested in. In a get query, at least one of the fields in the query record must be null. Fields passed in as null will be filled in with data in the response.

Open example notebook PYTHON/03-get-query.ipynb. Note that a value of null in the JSON query object is indicated by None in the Python code.

Get one record. The first two cells set up imports and credentials. Make sure the account you are using has at least one project and run the cell marked 'Query A'. It gets title and description for one project. This is not particularly useful when you have more than one project, because it doesn't specify which project has its details returned.

Get multiple records. To return more than one record to be returned, use brackets around the record object in the query. Before running the next cell, make sure the account you are using has two or more projects. Run the cell marked 'Query B' to get a listing of all projects. Do you see the difference in query payloads?

Filter multiple records. To filter results, that is, to select only records with fields that match certain values, you can specify field names and values you want matched. In the example file, edit the payload in cell marked Query C to select just one of your projects, based on one or more of the fields project_id, title, or description - making sure at least one field is still null (None), and run the cell.

Note: CoCalc query is similar to Facebook's GraphQL.

3. Setting values in the database

To set values for a record in the database, provide a unique key and specify the settings for all fields you want to modify.

Open example notebook PYTHON/04-set-query.ipynb. This notebook includes two examples of updating a database table.

Set the value of a field. Run the first two cells of the notebook, then look at Query A. The first call to the API sets the new project description. The second call displays the result. Enter the project_id for one of your projects and a new description you would like to set for that project and run the cell. Note that the response to a successful set query has an empty message object.

Set one value in a map field. Some fields in the CoCalc database have values that are complex objects. Here's an example from the 'accounts' table of the 'editor_settings' field:

"editor_settings": {"auto_close_brackets": True,
                    "auto_close_xml_tags": True,
                    "bindings": "standard",
                    "code_folding": True,
                    "electric_chars": True,
                    "extra_button_bar": True,
                    "first_line_number": 1,
                    "indent_unit": 4,
                    "jupyter_classic": False,
                    "line_numbers": True,
                    "line_wrapping": True,
                    "match_brackets": True,
                    "match_xml_tags": True,
                    "multiple_cursors": True,
                    "show_trailing_whitespace": True,
                    "smart_indent": True,
                    "spaces_instead_of_tabs": True,
                    "strip_trailing_whitespace": False,
                    "tab_size": 4,
                    "theme": "default",
                    "track_revisions": True,
                    "undo_depth": 300}}}`

These fields have a 'type' attribute of 'map'. Check the type of the 'editor_settings' field in the schema file now.

The CoCalc API makes it easy to set just one or (a few) selected attributes of a map field. Settings specified in the set query are merged into the rest of the map. Run Query B and verify that you can set just one attribute in 'editor_settings' and the change is merged into the rest of the settings.

4. More practice

Try the following queries to test your understanding. You'll find answers, coded as curl commands, in the API documentation for query:

get

  • Get project id, given title and description.
  • Get users (i.e. owner and all collaborators), given the project id.
  • Show project upgrades.

set

  • Make a path public (publish a file).
  • Add an upgrade to a project.

Navigate