`<do action_="sql">`

<do action_="sql"> provides a programmatic API to the SQL metadata store. (Available as of version 17.29)

Description

The SQL metadata store consists of a hierarchy of SQL objects. At the top of the hierarchy is the catalog. The default catalog is tenten. If you omit catalog, the API assumes catalog_="tenten". Within the catalog are schemas. Schemas can contain tables and views. Tables contain columns.

You can look at the SQL hierarchy with list_="catalogs", list_="schemas", list_="tables", list_="columns", or list_="views". These methods take as arguments the appropriate hierarchy above the object. For example, list_="columns" takes as arguments schema_ and table_ (catalog_ is assumed to be "tenten" and is omitted).

Similar to list_=, there is a get_=, add_=, put_=, and del_= method for each object type. The objname_ argument contains the name of the object being gotten, added, put, or deleted. These methods also take as arguments the appropriate hierarchy above the object. For example, get_="table" takes as its arguments objname_ (the table name to "get") and schema_ (the hierarchy above table).

Regardless of the object type, with get_= you use value_= to specify the variable to receive the gotten object, with put_= you specify the properties to be updated with data_= (or use individual attributes), and with add_= you always add an empty/dummy object.

The API deals with live metadata from the store, which may have changed since the user's session began and thus may differ from the objects seen by SQL queries in the session (as reported by

SELECT *
        from information_schema.schemata/tables/columns

). You can call

<do
        action_=”sql” reload_=”catalog”/>

to refresh the entire database catalog.

Syntax

<do action_="sql" [METHOD]="[OBJECTTYPE]"/>

Methods

The following are the basic methods of <do action_="sql">, which are described in detail in this section. The methods below are categorized by SQL object type: catalog, schema, table, column, and view.

get_="catalog": <do action_="sql" get_="catalog" catalog_="[CATALOG]" value_="@r"/> populates @r with a specified database catalog.
For more information, see get_="catalog".
list_="catalogs": <do action_="sql" list_="catalogs" value_"@r"/> populates @r with a list of the names of all available SQL catalogs.
For more information, see list_="catalogs".
reload_="catalog": <do action_="sql" reload_="catalog"/> refreshes the entire database catalog in the caller's session.
For more information, see reload_="catalog".

add_="schema": <do action_="sql" add_="schema" objname_="[SCHEMA]" desc_="[DESCRIPTION]" users_="[USERS]" admins_="[ADMINS]"/> creates a new empty schema with the name [SCHEMA].
For more information, see add_="schema".
del_="schema": <do action_="sql" del_="schema" objname_="[SCHEMA]"/> deletes a schema from the catalog.
For more information, see del_="schema".
get_="schema": <do action_="sql" get_="schema" objname_="[SCHEMA]" value_="@r"/> populates @r with a specified schema.
For more information, see get_="schema".
put_="schema": <do action_="sql" put_="schema" objname_="[SCHEMA]" data_="{...}"/> updates a schema with the properties contained in data_.
For more information, see put_="schema".
list_="schemas": <do action_="sql" list_="schemas" catalog_="[CATALOG]" value_"@r"/> populates @r with a list of the names of all available schemas to which the user has access in the specified catalog.
For more information, see list_="schemas".

add_="table": <do action_="sql" add_="table" schema_="[SCHEMA]" objname_="[TABLE]"/> adds an empty table to a specified schema.
For more information, see add_="table".
del_="table": <do action_="sql" del_="table" schema_="[SCHEMA]" objname_="[TABLE]"/> deletes a table from the specified schema.
For more information, see del_="table".
get_="table": <do action_="sql" get_="table" schema_="[SCHEMA]" objname_="[TABLE]" value_="@r"/> populates @r with metadata for the specified table from the specified schema.
For more information, see get_="table".
put_="table": <do action_="sql" put_="table" schema_="[SCHEMA]" objname_="[TABLE]" data_="{...}"/> updates a table with the specified properties.
For more information, see put_="table".
list_="tables": <do action_="sql" list_="tables" schema_="[SCHEMA]" value_="@r"/> populates @r with a list of the names of all available tables in the specified schema.
For more information, see list_="tables".
analyze_="table": <do action_="sql" analyze_="table" path_="[PATH]" value_="@r"/> analyzes the 1010data table at [PATH] and populates @r with complete SQL metadata for the table.
For more information, see analyze_="table".

add_="column": <do action_="sql" add_="column" schema_="[SCHEMA]" table_="[TABLE]" objname_="[COLUMN]"/> creates a new dummy column in the specified table.
For more information, see add_="column".
del_="column": <do action_="sql" del_="column" schema_="[SCHEMA]" table_="[TABLE]" objname_="[COLUMN]"/> deletes a column from the specified table.
For more information, see del_="column".
get_="column": <do action_="sql" get_="column" schema_="[SCHEMA]" table_="[TABLE]" objname_="[COLUMN]" value_="@r"/> populates @r with metadata for the specified column from the specified table in the specified schema.
For more information, see get_="column".
put_="column": <do action_="sql" put_="column" schema_="[SCHEMA]" table_="[TABLE]" objname_="[COLUMN]" data_="{...}"/> updates a column with the specified properties.
For more information, see put_="column".
list_="columns": <do action_="sql" list_="columns" schema_="[SCHEMA]" table_="[TABLE]" value_"@r"/> populates @r with a list of the names of all available columns in the specified table.
For more information, see list_="columns".
analyze_="column": <do action_="sql" analyze_="column" path_="[PATH]" col_="[COLNAME]" value_="@r"/> analyzes the 1010data table at [PATH] and populates @r with complete SQL metadata for the single column.
For more information, see analyze_="column".

add_="view": <do action_="sql" add_="view" schema_="[SCHEMA]" objname_="[TABLE]"/> adds an empty table to a specified schema.
For more information, see add_="view".
del_="view": <do action_="sql" del_="view" schema_="[SCHEMA]" objname_="[TABLE]"/> deletes a view from the specified schema.
For more information, see del_="view".
get_="view": <do action_="sql" get_="view" schema_="[SCHEMA]" objname_="[TABLE]" value_="@r"/> populates @r with metadata for the specified view from the specified schema.
For more information, see get_="view".
put_="view": <do action_="sql" put_="view" schema_="[SCHEMA]" objname_="[TABLE]" data_="{...}"/> updates a table with the specified properties.
For more information, see put_="view".
list_="views": <do action_="sql" list_="views" schema_="[SCHEMA]" value_="@r"/> populates @r with a list of the names of all available views in the specified schema.
For more information, see list_="views".
analyze_="view": <do action_="sql" analyze_="view" sql_="[SQL]" value_="@r"/> populates @r with complete SQL metadata for the view, based on the SQL query [SQL].
For more information, see analyze_="view".