Permalink
Fetching contributors…
Cannot retrieve contributors at this time
205 lines (184 sloc) 8.72 KB
Ordinarily ocelotgui is a client for
MySQL/MariaDB, and all documentation
assumes that. However, it is possible
to connect to Tarantool 1.8.1 or Tarantool 2.0 or later (with SQL) instead.
You get all the same features except the debugger.
The ocelotgui connection to Tarantool is alpha
and is not produced by Tarantool and has no warranty.
You need the latest Tarantool "SQL" server.
Tarantool's official "SQL" releases are 1.8.1 and 2.0 and 2.1.
Release 1.8.1 https://github.com/tarantool/tarantool/releases/tag/1.8.1 is slightly obsolete.
Release 2.0 or 2.1 will be available soon, look for it on https://github.com/tarantool/tarantool/releases,
but at time of writing is not there yet.
For a more current version, download from github.com/tarantool/tarantool:
git clone -b 2.1 https://github.com/tarantool/tarantool.git ~/tarantool-2.1
and build as instructed in the version-2.0 manual.
Usually you do not need to install the Tarantool client (tarantool.so) library,
but it is possible to use it if you build ocelotgui with "cmake -DTHIRD_PARTY=1".
If you did that, then this is how to get tarantool.so.
The tarantool-dev package does not have it any more.
Clone and follow the instructions at
github.com/tarantool/tarantool-c ...
cd ~
git clone https://github.com/tarantool/msgpuck.git tarantool-msgpuck
cd tarantool-msgpuck
cmake .
make
sudo make install
cd ~
git clone https://github.com/tarantool/tarantool-c tarantool-c
cd tarantool-c
cp ~/tarantool-msgpuck/msgpuck.h third_party/msgpuck/msgpuck.h
cmake .
make
sudo make install
WARNING: in the past the tarantool-c folks have changed
structs in the public API. If they do it again, ocelotgui
will crash.
WARNING: On some distros the installation will be to a
directory that is not on the distro's default path.
For example, if libtarantool.so ends up on /usr/local/lib,
you will have to say this before you start ocelotgui:
export LD_RUN_PATH=/usr/local/lib
Or you can add --ld_run_path=/usr/local/lib on the
command line where you start ocelotgui.
On Windows you do not need to install a
Tarantool library, its code is embedded in ocelotgui.exe.
You need the latest ocelotgui client.
The Release 1.07 version is okay,
but some things might not be up to date.
It is better to build it from source.
Download from github.com/ocelot-inc/ocelotgui.
Start the Tarantool server, and say:
box.cfg{listen=3301}
-- Second connect if you want LUA '...' to work
net_box = require('net.box')
ocelot_conn2=net_box.new('localhost:3301')
ocelot_conn2:eval('return 5')
box.schema.user.grant('guest','read,write,execute','universe')
NB: user 'guest' can read and write but not create. Therefore
for demonstration purposes it is far better to be user 'admin'.
To assign a password to user 'admin', say:
box.schema.user.passwd('admin')
Start ocelotgui thus:
ocelotgui --ocelot_dbms='tarantool' --port=3301 --host='localhost' --user='admin' --password='admin'
The initial screen should come up saying "OK", you're connected.
Type some SQL statements in the statement widget
at the bottom of the screen.
CREATE TABLE test1 (s1 INT PRIMARY KEY, s2 CHAR(5));
INSERT INTO test1 VALUES (1,'a'),(2,'b'),(3,'c');
UPDATE test1 set s2 = s2 || '!';
SELECT * FROM test1;
You'll see the usual hints appearing as you type.
You'll see the usual grid display when you type
Enter, or control-E.
(By the way, if you prefer the HTML-based grid
display, start ocelotgui with the additional
option --html. It will look quite different.
But you can't drag and resize with --html.)
Now type any other SQL statements, as described
in the tarantool manual. (At time of writing this
manual is not available but it's similar to SQLite.
The tutorial https://www.tarantool.io/en/doc/2.0/tutorials/sql_tutorial/ SQL statements work.)
Now type
LUA 'return box.space._index:select()';
or simply
return box.space._index:select();
This will evaluate the expression, without SQL.
The expression must return a result set.
The result will be tabular (rows and columns),
even though box.space._index was created with NoSQL.
Bonus feature: A client statement,
CREATE SERVER id ... OPTIONS (PORT ..., HOST ..., USER ..., PASSWORD ...);
allows a second connection. Usually this is a connection
to a different server.
Later you can use the second connection to create an SQL table
that's populated from a NoSQL space, preserving
all the data and converting it to fit in table rows,
preserving names if they were made with a Tarantool
format clause.
* The advantage is that the second server does no work
except (lua box.space.x:select); the main server makes a
temporary copy of the result set and the main server
does all work required for select-list computations,
group by, order by, etc. That should enhance throughput.
And, since it's now an SQL table, you can create indexes
on it and do SQL manipulations without needing NoSQL.
* The limitation is that you are now working on a copy
instead of the original; it might quickly go out of date.
* The big limitation is that the first column of the new
table is automatically and always the PRIMARY KEY.
Therefore if there is any duplication in the space,
the CREATE TABLE statement will fail.
E.g.
On #1 (server/lua):
box.cfg{listen=3301}
box.schema.user.grant('guest','read,write,execute','universe')
box.sql.execute([[create table a (s1 int primary key, s2 varchar);]])
box.sql.execute([[insert into a values (1,'wombat');]])
On #2 (server/lua)
box.cfg{listen=3302}
box.schema.user.grant('guest','read,write,execute','universe')
box.sql.execute([[CREATE TABLE t2 (x1 INT PRIMARY key, x2 VARCHAR);]])
box.sql.execute([[INSERT INTO t2 VALUES (0, 'Hi!');]])
On ocelotgui connection:
CREATE SERVER id FOREIGN DATA WRAPPER ocelot_tarantool OPTIONS (PORT 3302, HOST 'localhost', USER 'guest');
CREATE TABLE y4 SERVER id LUA 'return box.space._space:select()';
SELECT * FROM y4;
-- It does not have to be a second server, so simplify the example.
-- It does not have to be LUA '...', it can be RETURN lua-expression.
-- We can have MariaDB=main and Tarantool=remote, or Tarantool=main and Tarantool=remote
Images:
Doubtless you have image (.png or .gif or .jpg) files on your system.
For this example, change the three "box.space.timages:insert" lines,
changing the file names to file names that are on your system, or
changing the directory to wherever you installed ocelotgui.
Then "copy" the example code here and "paste" it into the ocelotgui
statement widget. (Sometimes it is better to copy and paste statements
one at a time rather than all at once.)
(Important: "timages" is a quoted identifier, the quote marks are necessary.)
-- Lua function to set a variable to a file contents: based on fio_read.lua:
function load_file(file_name)
local fio = require('fio')
local errno = require('errno')
local f = fio.open(file_name, {'O_RDONLY' })
if not f then
error("Failed to open file: "..errno.strerror())
end
local data = f:read(1000000)
f:close()
return data
end;
DROP TABLE "timages";
create table "timages" (s1 int PRIMARY KEY, s2 longblob, s3 char(5));
box.space.timages:insert{1, load_file('/home/pgulutzan/ocelotgui/shot1.jpg'), 'shot1'};
box.space.timages:insert{2, load_file('/home/pgulutzan/ocelotgui/shot2.jpg'), 'shot2'};
box.space.timages:insert{3, load_file('/home/pgulutzan/ocelotgui/shot3.png'), 'shot3'};
SET ocelot_extra_rule_1_display_as = 'image';
SELECT * FROM "timages";
Alternative: (details are left to the reader's imagination) We could instead use:
function f() return 5 end;
box.internal.sql_create_function("f",f,0);
CREATE TABLE test (s1 INT PRIMARY KEY);
INSERT INTO test VALUES (f());
SELECT s1, s1 * f() FROM test;
Rules concerning ocelotgui when connecting to tarantool:
* All statements must end with ; (or something established by DELIMITER statement).
This applies to Lua as well as SQL.
* If you want a result set for a Lua request, you must say "return".
For example "return box.space.T:select();" rather than "box.space.T:select();"
* SQL-style comments /* ... */ will not be considered errors inside Lua statements,
but will not be passed to the server.
* Statements may contain [[...]] strings, but not =[[...]]= strings.
* Defaults are MySQL/MariaDB defaults:
--delimiter is off
but ansi_quotes is on.
* BUG? When ocelotgui is displaying an image, cpu time rises.
* Decisions to right-justify, or display as images, are automatic rather than dependent on data type.
* SQL "verbs", for example COMMIT, should not be used as Lua identifiers.
* If you use SQL, you need Tarantool 1.8 or (preferably) 2.0. If you only use Lua, you can use Tarantool 1.7 or 1.9.
* We don't accept identifiers longer than 64 characters.
If the Tarantool server version release date is after the date of the
ocelotgui release, then there will probably be problems because the parsers
are different. Sometimes this can be solved by downloading ocelotgui source
and building again.