9 - STAPI - Speed Tables API

STAPI allows the Speed Tables API, originally implemented in ctables, to be used for a variety of table-like objects. This includes remote ctables through ctable_server and SQL databases. There are two main sets of routines in STAPI, and they're not normally used together.

st_server, a set of routines for automatically creating a ctable from an SQL table as a local read_only cache for the table, or as a workspace to be used for preparing rows to be inserted into the table. It's normally used in a ctable_server task providing a local cache for client processes.

st_client, which provides the general interface for creating STAPI objects identified by URIs.

st_server

package require st_server
::stapi::init ?options?

Options:

-dir work_directory

Root of directory tree for the ctables

-mode mode

Octal UNIX mode bits for new directories

-conn connection

Pgsql connection (if not specified, assumes DIO is being used and a DIO object named DIO exists and has already been connected to the database)

-cache minutes

How long to treat a cached tsv file as "good"

::stapi::set_conn connection

Set or reset the pgsql connection used by stapi in st_server and st_client_pgtcl.

::stapi::init_ctable name table_list where_clause ?columns|column...?

Initialize a cached speed table based on one or more SQL tables. If necessary, this builds a ctable based on the columns, and generates new SQL to read the table.

Parameters:

name

base name of speed table

table_list

list of SQL tables to extract data from. If it's empty then use the base name of the speed table as the name of the SQL table.

where_clause

An optional SQL "WHERE" clause to limit the rows selected into the speed table, or an empty string

columns

list of column definitions.

At least two columns must be defined -- the first is the speed table key, the rest are the fields of the ctable. If there is only one "column" argument, it's assumed to be a list of column arguments.

Column entries are each a list of {field type expr ?name value?...}

  • field - field name
  • type - SQL type
  • expr - SQL expression to derive the value
  • name value - speed table arguments for the field

(Only the field name is absolutely required.)

If the type is missing or blank, it's assumed to be varchar. If the expression is missing or blank, it's assumed to be the same as the field name.

In most cases the list of column definitions can be created by querying the SQL database itself using from_table:

::stapi::from_table table_name keys ?-option value?...

Generate a column list for init_ctable by querying the SQL database for the table definition.

keys

a list of columns that define the key for the table

Keys can be empty, to allow you to combine from_table lists with an appropriate "WHERE" clause to use init_ctable to create a view that spans tables.

Options:

-with column

Include column name in table. If any -with clauses are provided, only the named columns will be included.

-without column

Exclude column name from table. You must not provide both "-with" and "-without" options.

-index column

Make this column indexable. The index will actually be created after the cache is loaded.

-column {name type ?sql? ?args}

Add an explicit derived column. This can be used for the creation of ctables from SQL tables that have multi-column keys.

-table name

If specified, generate implicit column-name as "table.column" in the SQL. This allows for the cache to be created from a query on more than one table.

-prefix text

If specified, prefix column names with "$prefix"

::stapi::open_cached name ?pattern? ?-opt val?...

Open an initialized speed table, maintaining a local cache of the underlying SQL table in a .tsv file in the workdir.

Options

-pat pattern

Only read lines matching the pattern from the cache, if the cache is good. This is an optimization to avoid reading the entire table into memory when only a part of the table will be used . If the cache is old or missing, then the entire table will still be read into memory.

-time cache_timeout

Override the default cache timeout.

-col name

Name of column in the table that contains the last_changed time of each entry, if any. This is used as an optimization to only load modified lines when the schema supports that.

-index field_name

Name of a field to create an index on. Multiple -index entries are allowed.

-rowbyrow 1

Use the "row by row" method when importng the table. See Chapter 5 for the advantages and hazards of this mechanism.

-polling N

Enable polling (calling update) when importing SQL results, update is called every N rows.

::stapi::tune_cached ?-opt value?...

Modify some parameters of open_cached that can be safely modified on the fly. Currently only -rowbyrow and -polling can be specified.

::stapi::refresh_ctable ctable ?last_read? ?err?

Update new rows from SQL for speed table ctable.

If last_read is non-zero, use that rather than last modify time of the cache file.

If err is provided, it will return success or failure of the SQL request and put the error in $err, otherwise it will generate a Tcl error for SQL errors.

This uses the parameters set up in open_cached, and if there is no column in the table that can be used to determine the last change time, then the whole table will be re-read.

::stapi::save_ctable ctable ?tsv_file?

Save a table locally on disk. If the tsv_file is provided, it writes to that file. If not, it locates and locks the existing tsv file for the table, writes it, and unlocks it. This does not save the table back to the SQL data source.

::stapi::remove_tsv_file table_name
::stapi::remove_tcl_file table_name

Remove the cached tcl or tsv files, which will force the cache to be reread (if the tsv file is missing) or reconstructed using SQL queries (if the tcl file is missing). These are not normally used directly, but are available if the table is known to be out of date.

::stapi::open_raw_ctable name

Open an initialized speed table (as in open_cached) but don't fetch anything from SQL. This is used internally by open_cached, and is also useful for setting up temporary tables and workspaces.

st_client

st_client implements the ::stapi::connect front end for ctables and other speedtable API objects.

::stapi::connect uri ?-name value...?

Connect to a speed table server or other database providing a speed table interface via a URI. Returns an open speed table.

Options:

-key col

Define the column used to generate the key.

-keys {col col ...}

Define the columns used to generate the key.

-keysep "string"

Define the separator used to build the key.

For underlying services where key column information is not readily exposed, one of -key or -keys/-keysep should be provided. Depending on the underlying object, -keys may not be compatible and STAPI will need to create a wrapper function.

If neither is provided, some STAPI capabilities may not be available.

::stapi::register method transport_handler

register a transport method for ::stapi::connect.

Using a local ctable directly

package require st_client
local:///ctable_name

This is a dummy method that just passes the ctable through. It can be useful during development or debugging, or to share a consistent API between C speedtables and STAPI speedtables.

Loading a precompiled ctable package and using an instance of that ctable.

package require st_client
package:///table[/path]

This loads the package "Table" (from the path if provided), creates an instance of the table named "table", and returns that.

Using a ctable server via sttp (client/server)

package require st_client
sttp://[host:port]/[dir/]table[/stuff][?stuff]

Using a ctable server via sttp (shared memory)

package require st_shared
shared://port/[dir/]table[/stuff][?stuff]

Access a speed table server on localhost, using shared memory for the "search" method and sttp: for other methods.

The speed table must reside on the same machine for shared memory table access to be used. Concurrent access and update of shared memory speed tables is supported and provides a mechanism to use multiple processors to access a table concurrently. Like, really concurrently, whereas pure client/server table access is inherently single threaded.

The ctable built by the server must be in auto_path, or in the directory defined by the "-build" option.

An additional method "detach" is available for this transport. The "detach" method closes the reader side of the socket, so only the shared memory table is retained. After this operation, only the "search" method will be available.

Using a PostgreSQL database directly

package require st_client_pgtcl
::stapi::set_conn connection ...or...
::stapi::set_DIO ?db? ?user?

sql://connection/table[/col[:type]/col...][?param&param...]

Create a stapi interface to a PostgreSQL table

connection:

Not implemented yet, will be something on the order of: [user[:password]]@[host:]database

cols:

If no keys defined, first column is assumed to be the key.

params:
column=sql_code
Define the SQL required to perform the selection
_key=sql_code
_key=column
Define the SQL for the key. If this is a simple column name no explicit "_key" will be created.
_keys=column:column:...
Define the key in terms of a list of columns.

This uses the methods defined in st_server.

Examples:

sql:///users?_key=login

Pull in all the columns from "users", using login as the key.

sql:///users/login/password

Pull in login and password from "users", using login as the key.

Unimplemented methods:
import, import_postgres_result, export, statistics, reset, write_tabsep, needs_quoting, names, read_tabsep.

Using a Cassandra table directly

package require st_client_cassandra
(optional) ::stapi::set_cassandra_connection ?-user username? ?-host hostname?... ?-pass password? ?-port port?

cass://connection/keyspace.table/col[:type/...][?param&param...]

Create a STAPI connection to a cassandra table.

connection:
[user[:password]]@[host,host...[:port]]

If this is provided, it is created and torn down on a per-table basis, so it is possible to connect to multiple Cassandra clusters from the same program.

cols, params:

Columns and key info are pulled in from the Cassandra connection, these fields are primarily used to override the name and type of tables if absolutely necessary.

If the connection info is not provided via set_cassandra_connection or in the URL, it will be pulled in from the environment variables $CASSTCL_USERNAME, $CASSTCL_CONTACT_POINTS, and $CASSTCL_PASSWORD.

Most of the commands available for a ctable are implemented, except for those not supported by Cassandra. In particular, none of the "match" operations in search are implemented, and there are other limitations on what fields can be included in the search constraints.

There is an additional pair of search operators, "contains" and "containskey", to allow searching on the contents of collections.

There is an additional search option, "-allow_filtering", which can be used if absolutely necessary to perform inefficient searches. This is not recommended

It is also not receommended to use the "$table count" option, because getting a complete row count for a Cassandra table is expensive.

Unimplemented methods:
import, import_postgres_result, export, statistics, reset, write_tabsep, foreach, needs_quoting, names, read_tabsep.
Unimplemented search operations:
null, notnull, imatch, match, notmatch, xmatch, match_case, notmatch_case, umatch, lmatch.

Using an already opened speed table

package require st_client
name (no method)

If the URI is not URI format, it assumes it's an object that provides stapi semantics already... typically a ctable, an already-opened ctable_client connection, or the result of a previous call to ::stapi::connect. It queries the object using the methods command, and if necessary creates a wrapper around the ctable to implement the extra methods that STTP provides.

Required methods to avoid the creation of a wrapper:

stapi::extend - the sttp API extensions to ctables

These extensions may be required for packages like STdisplay, which may need methods that are not be provided by all speedtable-compatible packages, so ::stapi::extend creates a wrapper object when needed.

::stapi::extend::connect object key-list ?-keysep string?

This is also called internally by ::stapi::connect if the "-key" or "-keys" option is provided.

If the object was created by ::stapi::extend::connect, or if it can use the methods call to determine that the object provides all the necessary methods, then the STAPI object is returned immediately. That makes it always safe to use this on an opened speedtable.

Note: this does not to change any parameters of an existing STAPI object.

Otherwise, this behaves identically to calling ::stapi::connect with the -key/-keys argument, and creates a wrapper object that understands at least the key, makekey, and store methods.