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.
Starting & Stopping ROcto¶
You can start ROcto by simply typing
To terminate the ROcto instance, you may use
CTRL+C. Note that the rocto instance may not exit immediately upon sending
CTRL+Cif there are still active client connections. In that case, rocto will gracefully exit once all client connections are closed.
Command Line Flags¶
roctocan take the following command line flags.
Mandatory arguments for long options are also mandatory for short options.
-a/--allowschemachangesoption 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).
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.
Specify the SQL database that Octo should emulate. Currently supported emulations are MYSQL and POSTGRES. If you wish to emulate MariaDB, choose MYSQL.
Display the help message and exit.
Listen on the specified port.
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.
Display version information and exit.
Allow users with read-write permissions to run INSERT, UPDATE, and DELETE.
Display release information and exit.
Connecting using SQuirreL SQL¶
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.
Make sure you know the IP address/port for your ROcto server. The port can be configured for ROcto in the config file.
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:
Install Java on your computer.
Install Squirrel SQL with the PostgreSQL driver
Launch Squirrel SQL
Configure the PostgreSQL driver to use the installed driver files.
Create a connection (called an Alias) using the PostgreSQL driver.
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.
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
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
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
portwith proper values. Octo does not currently support multiple databases exposed from a single ROcto process, so
db_namecan 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