Configuration

Configuration settings can be passed to the program with the following precedence:

  1. Flags passed to the program

  2. octo.conf

  3. ~/octo.conf

  4. $ydb_dist/plugin/octo/octo.conf

  5. Environment settings

Environment variables

The following environment variables must be set:

  • ydb_dist

  • ydb_gbldir

  • ydb_routines

The environment variables ydb_dist, ydb_gbldir, and ydb_routines can initially be set by sourcing ydb_env_set in your YottaDB installation directory.

Example setting of the environment variables (assuming default paths):

source /usr/local/lib/yottadb/r1.28/ydb_env_set
export ydb_routines=". $ydb_routines"

Global Variables

All Octo related globals are prefixed with ^%ydbocto. Using normal global mapping procedures for an existing application global directory (where you want to run Octo), map the global variable namespace ^%ydbocto* to a separate region (and its associated database file) that meets the below requirements (the below example commands assume the separate region is named OCTO).

  • NULL_SUBSCRIPTS must be set to ALWAYS.

  • KEY_SIZE must be tuned to your data - this can be set to the maximum allowed by YottaDB - "1019"

  • RECORD_SIZE must be tuned to your data/queries - a reasonable starting value is "300000"

Example:

GDE> add -segment OCTO -access_method=BG -file_name="$ydb_dir/$ydb_rel/g/octo.dat"
GDE> add -region OCTO -dynamic=OCTO -null_subscripts=ALWAYS -key_size=1019 -record_size=300000
GDE> add -name %ydbocto* -region=OCTO

Some of the globals used in Octo are:

  • ^%ydboctoocto: This global can refer to various functions, variables, Octo "read only" table values (postgres mappings, oneRowTable, etc.), and some counts. It needs to persist between sessions.

  • ^%ydboctoxref: This global contains cross-references, row counts and other statistical information. It needs to persist between sessions.

  • ^%ydboctoschema: This global contains information about the tables loaded into the database. It needs to persist between sessions.

Since most of the Octo variables need to persist between sessions, it is necessary that the region(s) mapping the ^%ydbocto* namespace have replication turned on in a replicated environment (or journaling turned on in a non-replicated environment).

Config files

Octo currently looks for a configuration file in the following directories:

  • $ydb_dist/plugin/octo/octo.conf

  • ~/octo.conf

  • ./octo.conf

If the same setting exists in more than one configuration file the setting in the later file (according to the list above) will prevail. An example config file can be found in $ydb_dist/plugin/octo/octo.conf.

Sample config file:

//////////////////////////////////////////////////////////////////
//								//
// Copyright (c) 2019-2023 YottaDB LLC and/or its subsidiaries.	//
// All rights reserved.						//
//								//
//	This source code contains the intellectual property	//
//	of its copyright holder(s), and is made available	//
//	under a license.  If you do not know the terms of	//
//	the license, please stop and do not read further.	//
//								//
//////////////////////////////////////////////////////////////////

/* Below setting specifies the SQL database Octo will emulate. The valid options are:
 * POSTGRES and MYSQL. The default is POSTGRES.
 */
emulate = "POSTGRES"

/* Below setting specifies the verbosity for logging. The valid options are, in order of decreasing verbosity:
 * TRACE, DEBUG, INFO, and ERROR. The default is ERROR.
 */
verbosity = "ERROR"

/* Below setting specifies the default table type of a table created by CREATE TABLE if neither READONLY or READWRITE
 * keywords are specified in the CREATE TABLE. Valid options are READONLY and READWRITE. The default is READWRITE.
 */
// tabletype = "READWRITE"

/* If octo_zroutines is set (default is unset), a process prepends it to ydb_routines (and in turn to $ZROUTINES). As
 * Octo places the M routines generated from SQL queries in the first source directory in $ydb_routines/$ZROUTINES and
 * YottaDB places the object code for M routines as specified by $ZROUTINES, octo_zroutines should not normally be
 * uncommented when Octo is installed as a YottaDB plugin under $ydb_dist. It is also unwise to set it in any octo.conf
 * that is shared between Octo environments, because that has the potential for the SQL for one Octo environment being
 * executed against another. Note that if YottaDB's ydb_env_set is being sourced to set up & manage an environment,
 * there is no need to assign a value to octo_zroutines.
 */
// octo_zroutines = "./"

// Settings related to the rocto process
rocto = {
  /* Specify IP address and port to listen on for connections.
   * The default is 0.0.0.0 which allows external connections;
   * configure your firewall and/or network routes to limit access.
   * To restrict access to just the local machine, set it to
   * 127.0.0.1.
   */
  address = "0.0.0.0"
  port = 1337
  // Set to use DNS to resolve numeric IP addresses
  use_dns = false
  // Set to true to enable usage of the Nagle algorithm on TCP connections and disable the TCP_NODELAY socket option.
  // Default is false (i.e. no delay) as it speeds up rocto response times
  tcp_delay = false
  // Authentication methods; supported options are "md5"
  authentication_method = "md5"
  // Set to 'true' to enable SSL/TLS session encryption for remote connections when clients requests it,
  // or to 'false' to disable SSL/TLS entirely. Note that even when set to 'true' unencrypted
  // connections are accepted when the client requests them. To force SSL/TLS connections, use ssl_required.
  ssl_on = false
  // Set to 'true' to require SSL/TLS session encryption for all remote connections and
  // refuse any client connection that does not request or support SSL/TLS encryption.
  // If set to 'false', unencrypted connections will be accepted.
  // Note that ssl_on must be set to 'true' when ssl_required is set to 'true'.
  ssl_required = false
}

/* Settings controlling YottaDB; these get set as environment variables during startup
 * Defined environment variables will take precedence
 */
/*yottadb = {
  ydb_gbldir = "yottadb.gld"
  ydb_ci = "calltab.ci"
  ydb_routines = "."
}*/

tls: {
  // Max number of certificate authorities to check when verifying certificate
  verify-depth: 7;
  // Name of certificate authority file
  CAfile: "server.crt";
  // Path to certificate authority file
  CApath: "/home/jon/YDBOcto/build/";
  // Session timeout limit, in seconds
  session-timeout: 600;

  /* List of certificate/key pairs specified by identifiers.
   * Currently only the OCTOSERVER identifier is supported.
   */
  OCTOSERVER: {
    format: "PEM";
    cert: "server.crt";
    key: "server.key";
  };
};

/* Octo History location. By default it's in "~/.octo_history".
 * You can override it here.
 * More information can be found here: https://docs.yottadb.com/Octo/history.html
 */
octo_history = "~/.octo_history"

/* Octo History Maximum Length. By default it's 500. You can override it
 * here.
 * More information can be found here: https://docs.yottadb.com/Octo/history.html
 */
octo_history_max_length = 500

A few of the configuration settings are described below.

emulate

Octo supports partial emulation of multiple SQL database products. The emulate configuration option may be used to specify which SQL database Octo will attempt emulate at process initialization time. Currently supported options are: POSTGRES and MYSQL. The default is POSTGRES. If you wish to emulate MariaDB, choose MYSQL.

octo_zroutines

Octo requires that $ydb_dist/plugin/o/_ydbocto.so and $ydb_dist/plugin/o/_ydbposix.so ($ydb_dist/plugin/o/utf8/_ydbocto.so and $ydb_dist/plugin/o/utf8/_ydbposix.so when using Octo in YottaDB's UTF-8 mode) be included in $ydb_routines. This is necessary not only for running the octo and rocto executables, but also for correctly updating and maintaining the YottaDB triggers that are used to maintain cross references for Octo. Accordingly these paths should exist in ydb_routines in your normal environment setup scripts.

The octo_zroutines configuration setting allows one to prefix ydb_routines env var with one or more paths specified in the configuration file.

Note

The source /usr/local/etc/ydb_env_set command sets these up automatically for environments with the default structure under $ydb_dir (defaulting to $HOME/.yottadb).

tabletype

The tabletype configuration setting determines the type of a table. It can take on one of the following values.

  • "READONLY": This table type allows greater flexibility in mapping SQL tables to pre-existing M global nodes by allowing the specification of keywords like START, STARTINCLUDE, END, PIECE, GLOBAL or column-level DELIM in the CREATE TABLE command. But queries that update tables like INSERT INTO, DELETE FROM etc. are not allowed in this type of table.

  • "READWRITE": This table type does not offer as much flexibility as READONLY type of tables in mapping SQL tables to pre-existing M global nodes. Keywords like START, END are not allowed in a CREATE TABLE command if READWRITE table type is also specified. But queries that update tables like INSERT INTO, DELETE FROM etc. are allowed in this type of table.

If the tabletype configuration setting is unspecified in the configuration file, READWRITE is the default value.

A DROP TABLE command on a READWRITE table drops the table as well as kills all underlying global nodes that stored the table data. On the other hand, a DROP TABLE command on a READONLY table only drops the table and leaves the underlying global nodes that stored the table data untouched.

verbosity

The verbosity configuration setting controls logging verbosity. It can take on one of the following values.

  • "TRACE": A TRACE message is useful to help identify issues in the code. The equivalent verbosity option is --verbose=3.

  • "INFO": An INFO message is used to relay information to the user. The equivalent verbosity option is --verbose=2.

  • "DEBUG": A DEBUG message helps users debug configuration issues. The equivalent verbosity option is --verbose=1.

  • "ERROR" : An ERROR message informs the user that an error has occurred. The equivalent verbosity option is --verbose=0.

See Verbose Launching Option for more information on verbosity levels.

octo_history

Octo History location. If not specified, it will be in ~/.octo_history by default. You can override it here.

octo_history_max_length

Maximum number of entries to save for Octo history. If not specified, it will be 500 by default. Note: while Octo is running, there is no history trimming being performed, so you can have more history while running Octo than the limit specified here.

TLS/SSL Configuration

Note

The instructions provided in this section will help in setting up a self-signed certificate for TLS, but not for setting up TLS in production. Also, a full TLS setup will require certificates signed by a known and trusted certificate authority.

Enabling TLS/SSL requires several additional steps beyond installing the YottaDB encryption plugin - it requires creating a Certificate Authority (CA), generating a TLS/SSL certificate, and making additional changes to octo.conf.

Generate CA key and certificate

# In a directory in which you want to store all the certificates for Octo
# Be sure to create a strong passphrase for the CA
openssl genpkey -algorithm RSA -pkeyopt rsa_keygen_bits:2048 -out CA.key
# This creates a CA valid for 1-year and interactively prompts for additional information
openssl req -new -nodes -key CA.key -days 365 -x509 -out CA.crt

Create server key and certificate request

# This creates a 2048 bit private key
openssl genpkey -algorithm RSA -pkeyopt rsa_keygen_bits:2048 -out server.key
# This creates the certificate signing request
openssl req -new -key server.key -out server.csr

Sign certificate based on request and local CA

# Asks the CA to sign the certificate with a 1-Year validity time
openssl x509 -req -in server.csr -CA CA.crt -CAkey CA.key -CAcreateserial -out server.crt -days 365
# Mask the password for the certificate in a way YottaDB understands
$ydb_dist/plugin/gtmcrypt/maskpass
# This will need to be added to any startup scripts for octo/rocto
export ydb_tls_passwd_OCTOSERVER=[Masked Password from maskpass]
export ydb_crypt_config=/path/to/octo.conf

Update Octo configuration file

$ydb_dist/plugin/octo/octo.conf contains an outline of the minimum configuration settings.

For TLS/SSL a configuration file is required, based on the changes below.

  1. In the rocto section, the ssl_on configuration setting must be set to true.

  2. A tls section must be present and generally conform to the requirements specified for the TLS plugin itself. Other notes:

    • Octo doesn't use any of the dh* settings, so those can be omitted.

    • The format specifier can also be omitted, as long as the certs are in PEM format.

    • The CAfile and CApath fields are mandatory and must point to valid files/locations with a full path.

    • A subsection named OCTOSERVER with key, and cert settings specifying the names of the private key and cert files.

  3. The ydb_tls_passwd_OCTOSERVER and ydb_crypt_config environment variables must be set correctly.

If you source /usr/local/etc/ydb_env_set it provides reasonable default values of environment variables. Review the $ydb_dist/plugin/octo/octo.conf file to configure your own environment.

Usage

Before running Octo/ROcto make sure that the required YottaDB variables are set either by creating your own script or run source /usr/local/etc/ydb_env_set.

To use the command-line SQL interpreter run: $ydb_dist/plugin/bin/octo.

To use rocto, the PostgreSQL protocol compatible server, run $ydb_dist/plugin/bin/rocto -p XXXX where -p XXXX optionally specifies a TCP port at which rocto is to listen for connections. The default port number is 1337.