ROcto

ROcto is the Remote Octo server. It is an implementation of the Postgres server, and receives SQL queries from any clients that can communicate with Postgres over the network. These queries are passed to Octo, which interacts with the database and passes results back through ROcto to the clients.

ROcto Usage

Starting & Stopping ROcto

You can start ROcto by simply typing rocto.

To terminate the ROcto instance, you may use CTRL+C. Note that the rocto instance may not exit immediately upon sending CTRL+C if there are still active client connections. In that case, rocto will gracefully exit once all client connections are closed.

Command Line Flags

rocto can take the following command line flags.

Note

Mandatory arguments for long options are also mandatory for short options.

1. -a, --allowschemachanges

   Note

   The -a/--allowschemachanges option is off by default and must be explicitly enabled since normal users will not need to change the schema. When using this option, implement security measures appropriate to the environment, e.g. network controls to restrict access to the listening TCP port to a specific host or subnet.

   Allows ROcto to make changes to the schema (CREATE TABLE and DROP TABLE) and to modify existing tables (INSERT, UPDATE, and DELETE).

2. -c, --config-file=<filepath>

   Note

   Octo looks for configuration files in default locations, e.g. $ydb_dist/plugin/octo/octo.conf. If a configuration file is specified on the command line, this will override any configuration specified in files from the default locations.

   Use specified configuration file instead of the default.

3. -e, --emulate=<db_name>

   Specify the SQL database that Octo should emulate. Currently supported emulations are MYSQL and POSTGRES. If you wish to emulate MariaDB, choose MYSQL.

4. -h, --help

   Display the help message and exit.

5. -p, --port=<number>

   Listen on the specified port.

6. -v, --verbose=<number>

   Specify amount of information to output when running commands specifying a numeric value from zero to five or adding additional ‘v’ characters. See Verbose Launching Option for more information on verbosity levels.

7. -r, --version

   Display version information and exit.

8. -w, --readwrite

   Allow users with read-write permissions to run INSERT, UPDATE, and DELETE.

9. -r, --release

   Display release information and exit.

Connecting using SQuirreL SQL

Introduction

SQuirreL SQL is an open-source Java SQL Client program for any JDBC compliant database. This documentation will describe in detail how to connect it to ROcto.

Pre-requisite steps:

1. Make sure you know the IP address/port for your ROcto server. The port can be configured for ROcto in the config file.

2. ROcto requires the creation of a user name and password. Follow the directions given in the Octo Administration Guide to add a new user.

Note that users must be created with adequate permissions to run certain types of SQL statements, i.e. CREATE, DROP, INSERT, UPDATE, and DELETE. See the Octo Administration Guide for more information on user permissions.

The overall steps are as follows:

1. Install Java on your computer.

2. Install Squirrel SQL with the PostgreSQL driver

3. Launch Squirrel SQL

4. Configure the PostgreSQL driver to use the installed driver files.

5. Create a connection (called an Alias) using the PostgreSQL driver.

6. Connect using the Alias

Keep in mind that an Alias in Squirrel really means a connection, or more properly, a type of connection using a specific driver.

Detailed Steps

Java is required to be installed in order to use SQuirrel SQL. Install Java if not already installed. Note that the Oracle version has some licensing limitations, but OpenJDK does not; but the OpenJDK version does not include auto-updating capabilities on some platforms.

Download JAR from Squirrel SQL’s website.

Launch the installer program as an administrator/root. On Windows, see here for some guidance, as it is not on the right-click menu for jars.

Press Next three times going through these screens, NOTE where you are installing it, and STOP at the last one

• Welcome Screen

• Information Screen

• Installation Path Screen (NOTE DOWN THE INSTALL PATH)

• Extra Packs Screen (STOP HERE)

At the Extra Packs Screen, scroll down and check “Optional Plugin - PostgreSQL”, as shown in the following figure, then press Next.

Check “Optional Plugin - PostgreSQL”

Installation will proceed. Press Next after that to create shortcuts, then Next, then Done.

Launch SquirrelSQL using the shortcut that got created on your desktop or menu.

The first time you launch it, you will be greeted with a Welcome Screen. Go ahead and close that. This is what you should see now.

SquirrelSQL before any configuration

Click on Windows menu > View Drivers

SquirrelSQL View Drivers 1

Scroll down until you see “PostgreSQL”, and then click on it.

SquirrelSQL View Drivers 2

Click on Drivers menu > Modify Driver

SquirrelSQL Modify Postgres Driver - Main Screen

Click on the “Extra Class Path” tab

SquirrelSQL Modify Postgres Driver - Extra Path Tab

Click on the “Add” button. A file open dialog will present itself.

SquirrelSQL Modify Postgres Driver - Add button dialog

Remember the SquirrelSQL install path you were asked to note down? We need it now. Navigate to that install path, then to “plugins”, then to “postgres”, then to “lib”.

SquirrelSQL Modify Postgres Driver - Navigation

Within the “lib” directory, you will find two files. We want the one called “postgresql-nn.n.n.jar”. Click on that.

SquirrelSQL Modify Postgres Driver - Select

Press “open”.

SquirrelSQL Modify Postgres Driver - After Open

Next, press “List Drivers”. You will see the “Class Name” fill out.

SquirrelSQL Modify Postgres Driver - List Drivers

Then, you will see this after you press OK. Notice the check mark next to the driver. That’s what we want to see.

SquirrelSQL Modify Postgres Driver - Completion

Next, create an alias for your ROcto server, including the server IP address and port number. First, click on the Windows menu > Aliases.

SquirrelSQL Add Alias - Side Bar

Then, click on Aliases, new Alias.

SquirrelSQL Add Alias - Main Screen

In here, fill the fields as follows:

• Name: Any name will do. ROcto for now.

• Driver: Should be pre-selected to PostgreSQL.

• URL: Should be in the format jdbc:postgresql://{ip_address}:{port}/{db_name}. Replace ip_address and port with proper values. Octo does not currently support multiple databases exposed from a single ROcto process, so db_name can be anything.

• Username: ROcto username set-up in pre-requisites section.

• Password: ROcto password set-up in pre-requisites section.

Here’s a sample fully filled out dialog:

SquirrelSQL Add Alias - Main Screen Filled Out

You should press “Test” and then “Connect” on the Test Dialog to test your connection. Once you are done, press OK. Once you do that, you will be immediately presented with another dialog to connect to ROcto:

SquirrelSQL ROcto Connection Prompt

Now press “Connect”. If you have a big schema, you will get this warning that it’s taking too long to load. It’s okay to ignore this warning. Press “Close”.

SquirrelSQL ROcto Load Time Warning

At this point, you will see the main screen. In this screen, you can explore the schema for the tables in Octo.

SquirrelSQL ROcto Connected At Last!

To write SQL statements, switch to the SQL tab. Drag down the divider to give yourself more editing space.

SquirrelSQL ROcto SQL Tab

Suppose there is a table “names” with records in it:

CREATE TABLE names (id INTEGER PRIMARY KEY, firstName VARCHAR, lastName VARCHAR);

A simple query in SQuirreL SQL could be:

SELECT * FROM names;

Result after the query

To make querying easier, Octo supports “Auto-Complete”. To initiate it, type TABLENAME., then press CTRL-SPACE. E.g.

Octo Auto-Complete with Squirrel