CREATE DATABASE STATEMENT::=

CREATE DATABASE database_name
   [DISTRIBUTED INTO {list_of_nodes | ALL NODES}]

list_of_nodes::= (0,  N1, N2, ..., Nn)

Note that 0,N1,...,Nn are a list of distinct integers that must
contain 0.

mysql> CREATE DATABASE my_db DISTRIBUTED INTO (0,1,2,3);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE DATABASE  my_db2 DISTRIBUTED INTO (0,2,5);
ERROR 9068 (HY108):  Some grid(s) of the database not available now

mysql> CREATE DATABASE  my_db3 DISTRIBUTED INTO (1,1);
ERROR 1064 (42000):   The grid list of the database is not unique

mysql> CREATE DATABASE  my_db DISTRIBUTED INTO (1);
ERROR 1064 (42000):   The grid list must contain Grid 0

mysql> select * from definition_schema.schemata;
+--------------+--------------------+--------------+-------------+---------------------+----------+---------------+
| catalog_name | schema_name        | schema_owner | schemata_id | date_created        | no_grids | list_of_grids |
+--------------+--------------------+--------------+-------------+---------------------+----------+---------------+
| def          | definition_schema  | root         |          10 | 2020-11-30 21:21:09 |        1 | 0,            |
| def          | information_schema | root         |          11 | 2020-11-30 21:21:09 |        1 | 0,            |
| def          | mysql              | root         |          13 | 2020-11-30 21:21:09 |        1 | 0,            |
| def          | performance_schema | root         |          12 | 2020-11-30 21:21:09 |        1 | 0,            |
| def          | sys                | root         |          12 | 2020-11-30 21:21:09 |        1 | 0,            |
+--------------+--------------------+--------------+-------------+---------------------+----------+---------------+
5 rows in set (0.01 sec)

mysql> CREATE DATABASE my_db DISTRIBUTED INTO (0,1);
Query OK, 0 rows affected (0.05 sec)

mysql> select * from definition_schema.schemata;
+--------------+--------------------+--------------+-------------+---------------------+----------+---------------+
| catalog_name | schema_name        | schema_owner | schemata_id | date_created        | no_grids | list_of_grids |
+--------------+--------------------+--------------+-------------+---------------------+----------+---------------+
| def          | definition_schema  | root         |          10 | 2020-11-30 21:21:09 |        1 | 0,            |
| def          | information_schema | root         |          11 | 2020-11-30 21:21:09 |        1 | 0,            |
| def          | my_db              | my_db        |          14 | 2020-11-30 21:52:02 |        2 | 0,1,          |
| def          | mysql              | root         |          13 | 2020-11-30 21:21:09 |        1 | 0,            |
| def          | performance_schema | root         |          12 | 2020-11-30 21:21:09 |        1 | 0,            |
| def          | sys                | root         |          12 | 2020-11-30 21:21:09 |        1 | 0,            |
+--------------+--------------------+--------------+-------------+---------------------+----------+---------------+
6 rows in set (0.01 sec)

CREATE TABLE STATEMENT::=

CREATE [TEMPORARY] TABLE table_name
   (create_definition)
   [table_option]
   [partition_options]
   [grid_partitions]
   [cache_size_specification]

CREATE [TEMPORARY] TABLE table_name
   [(create_definition)]
   [partition_options]
   select_statement
   [grid_partitions]
   [cache_size_specification]


create_definition: the standard table-create clause of the form: 
   (col_name type, ...,  col_name type, ...)

table_option:
   [...]
   | ENGINE [=] engine_name

partition_options:
   PARTITION BY 
     {[LINEAR] HASH (expr)}
     | [LINEAR] KEY [ALGORITH={1|2}] (column_list)
     | RANGE{(expr)|COLUMNS(column_list)} 
     | LIST{(expr)|COLUMNS(column_list)} 
   [PARTITIONS num]
   [SUBPARTITION BY
      { [LINEAR] HASH(expr) }
      | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES 
            {LESS THAN {(expr | value_list) | MAXVALUE} 
            | 
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]
        [(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]

select_statement:
    SELECT ...   (Some valid select statement)

grid_partitions:
    {PARTITION BY GRID (column_name) TO N NODES WITH  mod (N)}
    |{PARTITION BY GRID (column_name) TO N NODES INTO node_specification
     |STORED AT GRID ( N )}

node_specification: (0,1,...,N-1)
     Note that node_specification (0,1,...,N-1) is the set N of any distinct
valid node ids. By a valid node id M, if 0 <= M <= MAX_ID.

cache_size_specification:
     CACHE SIZE ( GB, MB )

mysql> CREATE TABLE base_table(
         t1 int primary key,
         t2 varchar(100),
         t3 int
       ) PARTITION BY GRID (t1) TO 2 NODES WITH  mod (2);
Query OK

mysql> insert into base_table values(10, 'just do it', 100);
Query OK, 1 row affected (0.05 sec)

mysql> insert into base_table values(11, 'yes we can', 101);
Query OK, 1 row affected (0.00 sec)

mysql> insert into base_table values(20, 'just say no', 201);
Query OK, 1 row affected (0.00 sec)

mysql> insert into base_table values(21, 'be cool', 202);
Query OK, 1 row affected (0.00 sec)

mysql> select * from base_table;
+----+-------------+------+
| t1 | t2          | t3   |
+----+-------------+------+
| 10 | just do it  |  100 |
| 20 | just say no |  201 |
| 11 | yes we can  |  101 |
| 21 | be cool     |  202 |
+----+-------------+------+
4 rows in set (0.01 sec)

mysql> CREATE TABLE sample_db(
         t1 int,
         t2 varchar(100),
         t3 int
       ) SELECT * FROM base_table,
       PARTITION BY GRID (t1) TO 2 NODES WITH  mod (2);
Query OK, 0 rows affected (0.20 sec)

mysql>  SELECT * FROM sample_db;

+------+-------------+------+
| t1   | t2          | t3   |
+------+-------------+------+
|   10 | just do it  |  100 |
|   20 | just say no |  201 |
|   11 | yes we can  |  101 |
|   21 | be cool     |  202 |
+------+-------------+------+

mysql> CREATE TEMPORARY TABLE temp1 (
         t1 int, 
         t2 varchar(100), 
         t3 int
       ) SELECT * FROM base_table;


mysql> SELECT * FROM temp1;

+------+-------------+------+
| t1   | t2          | t3   |
+------+-------------+------+
|   10 | just do it  |  100 |
|   20 | just say no |  201 |
|   11 | yes we can  |  101 |
|   21 | be cool     |  202 |
+------+-------------+------+
4 rows in set (0.04 sec)

mysql> CREATE TEMPORARY TABLE temp2 (
         t1 int, 
         t2 varchar(100)
       ) SELECT t1, t2 FROM base_table;
Query OK, 4 rows affected (0.05 sec)

mysql> SELECT * FROM temp2;

+------+-------------+
| t1   | t2          |
+------+-------------+
|   10 | just do it  |
|   20 | just say no |
|   11 | yes we can  |
|   21 | be cool     |
+------+-------------+
4 rows in set (0.05 sec)

create table warehouse (
	w_id smallint,
	w_name varchar(10),
	w_street_1 varchar(20),
	w_street_2 varchar(20),
	w_city varchar(20),
	w_state varchar(2),
	w_zip varchar(9),
	w_tax tinyint,
	w_ytd integer,
	constraint warehouse_primary_key 
	primary key ( w_id)
)
PARTITION BY HASH (w_id) PARTITIONS 3,
PARTITION BY GRID (w_id) TO 2 NODES WITH  mod (2);

create table item (
	i_id integer,
	i_im_id integer,
	i_name varchar(24),
	i_price tinyint,
	i_data varchar(50),
	constraint item_primary_key
	primary key (i_id)
)
stored at grid(1);

mysql> select * from definition_schema.tables where table_schema='sqltest';
+---------------+--------------+------------+------------+--------------+--------------+------------+-----------+
| table_catalog | table_schema | table_name | table_type | table_degree | is_updatable | cache_size | global_id |
+---------------+--------------+------------+------------+--------------+--------------+------------+-----------+
| def           | sqltest      | base_table | table      |            3 | updatable    |          0 |        25 |
| def           | sqltest      | item       | table      |            5 | no           |          0 |        30 |
| def           | sqltest      | sample_db  | table      |            3 | updatable    |          0 |        26 |
| def           | sqltest      | temp1      | table      |            3 | updatable    |          0 |        27 |
| def           | sqltest      | temp2      | table      |            2 | updatable    |          0 |        28 |
| def           | sqltest      | warehouse  | table      |            5 | updatable    |          0 |        29 |
+---------------+--------------+------------+------------+--------------+--------------+------------+-----------+
6 rows in set (0.00 sec)

mysql> select * from definition_schema.table_distribution;
+---------------+--------------+------------+-------------+---------+---------------+-------------+------------+------------------+-----------------+
| table_catalog | table_schema | table_name | no_of_grids | grid_id | list_of_grids | grid_column | expression | second_parameter | third_parameter |
+---------------+--------------+------------+-------------+---------+---------------+-------------+------------+------------------+-----------------+
| def           | sqltest      | base_table |           2 |      -1 | 0,1,          | t1          | mod        |             NULL |            NULL |
| def           | sqltest      | item       |           1 |       1 | 1,            | NULL        | NULL       |             NULL |            NULL |
| def           | sqltest      | sample_db  |           2 |      -1 | 0,1,          | t1          | mod        |             NULL |            NULL |
| def           | sqltest      | temp1      |           1 |       0 | 0,            | NULL        | NULL       |             NULL |            NULL |
| def           | sqltest      | temp2      |           1 |       0 | 0,            | NULL        | NULL       |             NULL |            NULL |
| def           | sqltest      | warehouse  |           2 |      -1 | 0,1,          | w_id        | mod        |             NULL |            NULL |
+---------------+--------------+------------+-------------+---------+---------------+-------------+------------+------------------+-----------------+
6 rows in set (0.01 sec)

mysql> create table students (sid char(10), sname varchar(100));
Query OK, 0 rows affected (0.16 sec)

mysql> insert into students values( '1234567890', 'Sarah');
Query OK, 1 row affected (0.05 sec)

mysql> insert into students values( '12345', 'Tom');
Query OK, 1 row affected (0.01 sec)

mysql> select * from students;
+------------+-------+
| sid        | sname |
+------------+-------+
| 12345      | Tom   |
| 1234567890 | Sarah |
+------------+-------+
2 rows in set (0.00 sec)

mysql> select * from students where sid = '12345     ';
+-------+-------+
| sid   | sname |
+-------+-------+
| 12345 | Tom   |
+-------+-------+
1 row in set (0.00 sec)

mysql> select * from students where sid = '12345';
+-------+-------+
| sid   | sname |
+-------+-------+
| 12345 | Tom   |
+-------+-------+
1 row in set (0.00 sec)

   student(sid, name, major, gpa)
   course(cid, title, description)
   registration(cid, sid, grade)

select * from student;
+-------+-------+----------+------+
| sid   | name  | major    | gpa  |
+-------+-------+----------+------+
|  5500 | Susan | Law      | NULL |
| 12001 | Sarah | Math     | NULL |
| 12345 | Peter | NULL     | NULL |
| 54321 | Bob   | Business | NULL |
+-------+-------+----------+------+


select * from course; 
+-------+----------------------+-------------------------+
| cid   | title                | description             |
+-------+----------------------+-------------------------+
| AR100 | Cartoon Drawing      | A comic book            |
| BS499 | Marketing            | How to make quick bucks |
| CS291 | Introduction to DBMS | first database course   |
| MA101 | Calculus             | An easy course          |
+-------+----------------------+-------------------------+


select * from registration;
+-------+-------+-------+
| cid   | sid   | grade |
+-------+-------+-------+
| BS499 | 54321 | B     |
| CS291 | 12001 | NULL  |
| CS291 | 54321 | A     |
| MA101 | 12001 | D     |
+-------+-------+-------+

/*
 *  to set up the initial database for some examples
 */
DROP TABLE student;
DROP TABLE course;
DROP TABLE registration;

CREATE TABLE student (
	sid    INT,
        name   VARCHAR(64) NOT NULL,
	major  VARCHAR(10),
	gpa    NUMERIC(5, 2),
     	PRIMARY KEY(sid)
) partition by grid (sid) to 2 nodes with mod (2);

CREATE TABLE course (
        cid    CHAR(5),
        title  VARCHAR(64) NOT NULL,
        description VARCHAR(1024),
 	PRIMARY KEY(cid)
);

CREATE TABLE registration (
       cid     CHAR(5),
       sid     INT,
       grade     CHAR(1),
       PRIMARY KEY(cid, sid),
       CONSTRAINT sid_foreign_key
         FOREIGN KEY (sid) REFERENCES student(sid),
       CONSTRAINT grade_constraint
        CHECK ( grade IN ('A', 'B', 'C', 'D', 'F'))
);

INSERT INTO student (sid, name, major) VALUES (12001,'Sarah', 'Math');
INSERT INTO student (sid, name, major) VALUES (12345,'Peter', null);
INSERT INTO student (sid, name, major) VALUES (54321,'Bob', 'Business');
INSERT INTO student (sid, name, major) VALUES (5500, 'Susan', 'Law');

INSERT INTO course VALUES ('CS291','Introduction to DBMS','first database course');
INSERT INTO course VALUES ('MA101', 'Calculus', 'An easy course');
INSERT INTO course VALUES ('BS499', 'Marketing', 'How to make quick bucks');
INSERT INTO course VALUES ('AR100', 'Cartoon Drawing', 'A comic book');


INSERT INTO registration VALUES('CS291', 12001, null);
INSERT INTO registration VALUES('CS291', 54321, 'A');
INSERT INTO registration VALUES('MA101', 12001, 'D');
INSERT INTO registration VALUES('BS499', 54321, 'B');

mysql> CREATE SEQUENCE item_id START WITH 10 INCREMENT BY 2 MAXVALUE 2000000000;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT NEXTVAL FROM item_id;
+---------+
| NEXTVAL |
+---------+
|      12 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT NEXTVAL FROM item_id;
+---------+
| NEXTVAL |
+---------+
|      14 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT NEXTVAL FROM item_id;
+---------+
| NEXTVAL |
+---------+
|      16 |
+---------+
1 row in set (0.00 sec)

PROCEDURE DISTRIBUTION CLAUSE::=
   DISTRIBUTED [BY PARAMETER (parameter_name)] TO N NODES [WITH mod (N) | INTO (N1,N2,...,NN))]

SET BASE NODE 3;

create table Student ( 
     s_id int , 
     s_name varchar(64), 
     address varchar(128),  
     major varchar(32)
) 
PARTITION BY GRID (s_id) TO 2 NODES WITH  mod (2);

create table Scholarship ( 
     s_id int ,   
     scholarship varchar(64) ,  
     amount int
)
PARTITION BY GRID (s_id) TO 2 NODES WITH  mod (2);

insert into Student values (101,'Peter','Edmonton','CS');
insert into Student values (102,'Sarah','Calgary','CS');
insert into Student values (103,'Tom','Edmonton','Math');
insert into Student values (104,'Michelle','Toronto','Math');
insert into Scholarship values (102,'Ruthford',3000);
insert into Scholarship values (103,'Ruthford',3000);
insert into Scholarship values (103,'NSERC',6000);

mysql> SELECT s.s_id, s_name, scholarship,amount
       FROM   Student s, Scholarship p
       WHERE  s.s_id = p.s_id;

+------+--------+-------------+--------+
| s_id | s_name | scholarship | amount |
+------+--------+-------------+--------+
|  102 | Sarah  | Ruthford    |   3000 |
|  103 | Tom    | NSERC       |   6000 |
|  103 | Tom    | Ruthford    |   3000 |
+------+--------+-------------+--------+
3 rows in set (0.01 sec)

mysql> SELECT count(*)
       FROM   Student
       WHERE  s_id in (SELECT s_id FROM Scholarship );
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

mysql> SELECT *
       FROM   Student
       WHERE  s_id in (SELECT s_id FROM Scholarship where amount > 3000);
+------+--------+----------+-------+
| s_id | s_name | address  | major |
+------+--------+----------+-------+
|  103 | Tom    | Edmonton | Math  |
+------+--------+----------+-------+
1 row in set (0.04 sec)

CREATE TABLE Student ( 
         s_id int , 
         s_name varchar(64), 
         address varchar(128),  
         major varchar(32)
) 
PARTITION BY GRID (s_id) TO 2 NODES WITH  mod (2);

CREATE TABLE Scholarship ( 
         s_id int ,   
         scholarship varchar(64) ,  
         amount int
)
PARTITION BY GRID (s_id) TO 2 NODES WITH  mod (2);

insert into Student values (101,'Peter','Edmonton','CS');
insert into Student values (102,'Sarah','Calgary','CS');
insert into Student values (103,'Tom','Edmonton','Math');
insert into Student values (104,'Michelle','Toronto','Math');
insert into Scholarship values (102,'Ruthford',3000);
insert into Scholarship values (103,'Ruthford',3000);
insert into Scholarship values (103,'NSERC',6000);

+------+----------+----------+-------+
| s_id | s_name   | address  | major |
+------+----------+----------+-------+
|  102 | Sarah    | Calgary  | CS    |
|  104 | Michelle | Toronto  | Math  |
|  101 | Peter    | Edmonton | CS    |
|  103 | Tom      | Edmonton | Math  |
+------+----------+----------+-------+

+------+-------------+--------+
| s_id | scholarship | amount |
+------+-------------+--------+
|  102 | Ruthford    |   3000 |
|  103 | NSERC       |   6000 |
|  103 | Ruthford    |   3000 |
+------+-------------+--------+

mysql> CREATE TEMPORARY TABLE temp (s_id int,s_name varchar(100),total int) stored at grid(0)
       SELECT s.s_id,s.s_name, sum(h.amount) as total
       FROM   student s, scholarship h
       WHERE  s.s_id = h.s_id
       GROUP BY s.s_id, s.s_name;
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT s_id, s_name 
       FROM   temp 
       WHERE  total >= ALL (SELECT total FROM temp);
       
+------+--------+
| s_id | s_name |
+------+--------+
|  103 | Tom    |
+------+--------+
2 rows in set (0.00 sec)

create table Student ( 
    s_id int , 
    s_name varchar(64), 
    address varchar(128),  
    major varchar(32)
) 
PARTITION BY GRID (s_id) TO 2 NODES WITH  mod (2);

create table Scholarship (
    s_id int ,   
    scholarship varchar(64) ,  
    amount int
) 
PARTITION BY GRID (s_id) TO 2 NODES WITH  mod (2);

insert into Student values (101,'Peter','Edmonton','CS');
insert into Student values (102,'Sarah','Calgary','CS');
insert into Student values (103,'Tom','Edmonton','Math');
insert into Student values (104,'Michelle','Toronto','Math');
insert into Scholarship values (102,'Ruthford',3000);
insert into Scholarship values (103,'Ruthford',3000);
insert into Scholarship values (103,'NSERC',6000);

mysql> SELECT s.s_id, h.amount
       FROM   Student s left join Scholarship h on s.s_id=h.s_id;
       
+------+--------+
| s_id | amount |
+------+--------+
|  102 |   3000 |
|  104 |   NULL |
|  103 |   6000 |
|  103 |   3000 |
|  101 |   NULL |
+------+--------+
5 rows in set (0.00 sec)

mysql> SELECT s.s_id, sum(h.amount)
       FROM   Student s left join Scholarship h on s.s_id=h.s_id
       GROUP BY s.s_id;
       
+------+---------------+
| s_id | sum(h.amount) |
+------+---------------+
|  102 |          3000 |
|  104 |          NULL |
|  101 |          NULL |
|  103 |          9000 |
+------+---------------+
4 rows in set (0.00 sec)

mysql> SELECT s.s_id, h.amount
       FROM   Student s right join Scholarship h on s.s_id=h.s_id;

+------+--------+
| s_id | amount |
+------+--------+
|  102 |   3000 |
|  103 |   6000 |
|  103 |   3000 |
+------+--------+
3 rows in set (0.01 sec)


mysql> SELECT s.s_id, h.amount
       FROM   Student s inner join Scholarship h on s.s_id=h.s_id;

+------+--------+
| s_id | amount |
+------+--------+
|  102 |   3000 |
|  103 |   6000 |
|  103 |   3000 |
+------+--------+
3 rows in set (0.01 sec)

LOAD [AT GRID <id number>]  DATA

INFILE 'file_name'
   [REPLACE | IGNORE]
   INTO TABLE  table_name
   [FIELDS TERMINATED BY 'char']
   [LINES TERMINATED BY 'char']

mysql> CREATE TABLE pet (
         name    varchar(100) primary key,
         owner   varchar(100) not null,
         species varchar(30),
         sex     char(1),
         birth   date,
         death   date
)
PARTITION BY GRID (name) TO 2 NODES WITH  mod (2);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO pet VALUES('Sarah', 'Sarah', 'goat', 'f', '1979-08-31','2015-02-28');
Query OK, 1 row affected (0.01 sec)


mysql> LOAD DATA INFILE '~/pet.txt' INTO TABLE pet;
Query OK, 8 rows affected (0.02 sec)

mysql> select * from pet;
+----------+---------+---------+------+------------+------------+
| name     | owner   | species | sex  | birth      | death      |
+----------+---------+---------+------+------------+------------+
| Bowser   | Diane   | dog     | m    | 1979-08-31 | 1995-07-29 |
| Buffy    | Harold  | dog     | f    | 1989-05-13 | 0000-00-00 |
| Chirpy   | Gwen    | bird    | f    | 1998-09-11 | 0000-00-00 |
| Claws    | Gwen    | cat     | m    | 1994-03-17 | 0000-00-00 |
| Fang     | Benny   | dog     | m    | 1990-08-27 | 0000-00-00 |
| Fluffy   | nHarold | cat     | f    | 1993-02-04 | 0000-00-00 |
| Sarah    | Sarah   | goat    | f    | 1979-08-31 | 2015-02-28 |
| Slim     | Benny   | snake   | m    | 1996-04-29 | 0000-00-00 |
| Whistler | Gwen    | bird    |      | 1997-12-09 | 0000-00-00 |
+----------+---------+---------+------+------------+------------+
9 rows in set (0.00 sec)

mysql> create table ttt(
          t1 int primary key,
          t2 varchar(100),
          t3 int
       ) 
       PARTITION BY GRID (t1) TO 2 NODES WITH  mod (2);
Query OK, 0 rows affected (0.02 sec)

mysql> create table ttt2 (
          t1 int primary key,
          t2 varchar(100),
          t3 int
       )  PARTITION BY GRID (t1) TO 2 NODES WITH  mod (2);
Query OK, 0 rows affected (0.03 sec)

mysql> create table ttt3 (
          t1 int primary key,
          t2 varchar(100)
       )  
       PARTITION BY GRID (t1) TO 2 NODES WITH  mod (2);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into ttt values(10, 'just do it', 100);
Query OK, 1 row affected (0.03 sec)

mysql> insert into ttt values(11, 'yes we can', 101);
Query OK, 1 row affected (0.01 sec)

mysql> insert into ttt values(20, 'just say no', 201);
Query OK, 1 row affected (0.00 sec)

mysql> insert into ttt values(21, 'change', 202);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> insert into ttt2 values(10, 'be happy', 100);
Query OK, 1 row affected (0.04 sec)

mysql> insert into ttt2 values(11, 'be cool', 101);
Query OK, 1 row affected (0.01 sec)

mysql> insert into ttt2 values(20, 'xyz says', 201);
Query OK, 1 row affected (0.00 sec)

mysql> insert into ttt2 values(21, 'abc di it', 202);
Query OK, 1 row affected (0.00 sec)

mysql> DELIMITER $$
mysql> CREATE PROCEDURE curdemo()
       BEGIN
         DECLARE done INT DEFAULT FALSE;
         DECLARE b varchar(100);
         DECLARE c varchar(100);
         DECLARE a INT;
         DECLARE cur1 CURSOR FOR SELECT t1,t2 FROM ttt;
         DECLARE cur2 CURSOR FOR SELECT t2 FROM ttt2;
         DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
       
         OPEN cur1;
         OPEN cur2;
       
         read_loop: LOOP
           FETCH cur1 INTO a, b;
           FETCH cur2 INTO c;
           IF done THEN
             LEAVE read_loop;
           END IF;
           IF b < c THEN
             INSERT INTO ttt3 VALUES (a,b);
           ELSE
             INSERT INTO ttt3 VALUES (a,c);
           END IF;
         END LOOP;
       
         CLOSE cur1;
         CLOSE cur2;
       END
       DISTRIBUTED  TO 2 NODES with mod (2) $$
       
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> select * from ttt3;
Empty set (0.02 sec)

mysql> call curdemo();
Query OK, 0 rows affected (0.00 sec)

mysql> select * from ttt3;
+----+-------------+
| t1 | t2          |
+----+-------------+
| 10 | be happy    |
| 20 | just say no |
| 11 | be cool     |
| 21 | abc di it   |
+----+-------------+
4 rows in set (0.00 sec)

/*
 *  A sample program to demo how to use store procedures
 */
mysql> DROP TABLE IF EXISTS  test;
Query OK, 0 rows affected (0.00 sec)

mysql> drop procedure if exists sp1;
Query OK, 0 rows affected (0.00 sec)

mysql> drop procedure if exists sp2;
Query OK, 0 rows affected (0.00 sec)

mysql> drop procedure if exists sp3;
Query OK, 0 rows affected (0.00 sec)

mysql> drop procedure if exists sp4;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //

mysql> CREATE TABLE test(
       id int(11) 
       )
       partition by grid (id) to 2 nodes with mod (2)  //
Query OK, 0 rows affected (0.13 sec)

mysql> create procedure sp1(in p int)
       begin
       declare v1 int;
       set v1 = p;
       insert into test(id) values(v1);
       end
       DISTRIBUTED BY PARAMETER (p) TO 2 NODES with mod (2) //
Query OK, 0 rows affected (0.00 sec)

mysql> call sp1(1)//
Query OK, 0 rows affected (0.01 sec)
           
mysql> select * from test order by id//
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.09 sec)

mysql> create procedure sp2(out p int)                                                   
       begin
       select max(id) into p from test;
       end//

mysql> call sp2(@pv)//
Query OK, 0 rows affected (0.01 sec)

mysql> select @pv as '@pv' //
+-----+
| @pv |
+-----+
| 1   |
+-----+
1 row in set (0.00 sec)

mysql> create procedure sp3(in p1 int,out p2 int)
       begin
         if p1=1 then
           set @v=10;
         else
           set @v=20;
         end if;
         insert into test(id) values(@v);
         select max(id) into p2 from test;
       end
       DISTRIBUTED BY PARAMETER (p1) TO 2 NODES with mod (2) //
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test order by id //
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

mysql> call sp3(2,@ret) //
Query OK, 0 rows affected (0.01 sec)

mysql> select @ret as '@ref' //
+------+
| @ref |
+------+
| 20   |
+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test ORDER BY id //
+------+
| id   |
+------+
|    1 |
|   20 |
+------+
2 rows in set (0.01 sec)

mysql> create procedure sp4(inout p4 int)
       begin
         if p4 = 4 then
           set @pg = 400;
         else
           set @pg = 500;
         end if; 
         select @pg into p4;
       end // 
ERROR 1234 (68000):  the INOUT parameter is NOT supported for this version

mysql> create procedure sp4(in p4 int,out p5 int)
       begin
         if p4 = 4 then
           set @pg = 400;
         else
           set @pg = 500;
         end if; 
         select @pg into p5;
       end // 
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> set @pp = '6';
Query OK, 0 rows affected (0.00 sec)

mysql> call sp4(@pp, @pp2);
Query OK, 0 rows affected (0.01 sec)

mysql> select @pp2 as '@pp2'
+------+
| @pp2 |
+------+
| 500  |
+------+
1 row in set (0.00 sec)

mysql> set @pp =4;
Query OK, 0 rows affected (0.00 sec)

nmysql>  call sp4(@pp, @pp2);
Query OK, 0 rows affected (0.07 sec)

mysql> select @pp2 as '@pp2'
+------+
| @pp2 |
+------+
| 400  |
+------+
1 row in set (0.01 sec)

mysql> select @pp as '@pp'
+-----+
| @pp |
+-----+
| 4   |
+-----+
1 row in set (0.00 sec)

mysql> CREATE TABLE test1(a1 INT);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE test2(a2 INT);
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ||

mysql> CREATE TRIGGER testref BEFORE INSERT ON test1
         FOR EACH ROW
         BEGIN
           INSERT INTO test2 SET a2 = NEW.a1;
         END ||
Query OK, 0 rows affected (0.04 sec)

mysql> delimiter ;

mysql> INSERT INTO test1 VALUES (2);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO test1 VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test1 VALUES (7);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test1 VALUES (8);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test1 VALUES (4);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test1 VALUES (4);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test1;
+------+
| a1   |
+------+
|    1 |
|    2 |
|    4 |
|    4 |
|    7 |
|    8 |
+------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM test2;
+------+
| a2   |
+------+
|    1 |
|    2 |
|    4 |
|    4 |
|    7 |
|    8 |
+------+
6 rows in set (0.02 sec)

mysql> 

mysql> CREATE TABLE employee(
         e_id int, 
         e_name varchar(64), 
         position varchar(64), 
         salary decimal(10,2) 
       ) 
       PARTITION BY GRID (e_id) TO 2 NODES WITH  mod (2);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE supervision( 
         e_id int,
         e_name varchar(64), 
         s_name varchar(64) 
       ) 
       PARTITION BY GRID (e_id) TO 2 NODES WITH  mod (2); 
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter |

mysql> create trigger employee_supervisor
       before insert  on employee
       for each row
       begin
          declare   dummy int;
          declare   msg varchar(200); 
          select count(*) into dummy from supervision
          where new.e_name = supervision.e_name
                and supervision.s_name is not NULL;
                 
          if ( dummy < 1 )
          then
            set msg = 'employee must has a supervisor';  
                   SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg; 
          end if;
       end;
       |
Query OK, 0 rows affected (0.04 sec)

mysql> delimiter ;

mysql> INSERT INTO supervision VALUES (1,'tom','peter');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO employee VALUES (1,'tom','engineer',1000.00);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO employee VALUES (2,'shelly','engineer',1000.00);
ERROR 1644 (HY000):  employee must has a supervisor 

mysql> select * from supervision;
+------+--------+--------+
| e_id | e_name | s_name |
+------+--------+--------+
|    1 | tom    | peter  |
+------+--------+--------+
1 row in set (0.04 sec)

mysql> select * from employee;
+------+--------+----------+---------+
| e_id | e_name | position | salary  |
+------+--------+----------+---------+
|    1 | tom    | engineer | 1000.00 |
+------+--------+----------+---------+
1 row in set (0.04 sec)

create table picture (
     photo_id integer, 
     title varchar(48), 
     place varchar(48),
     sm_image blob,
     image blob,
     constraint picture_primary_key_constraint primary key(photo_id)
)

import java.io.*;
import java.sql.*;

/**
 *  A simple example to demonstrate how to use JDBC PreparedStatement
 *  to insert a Blob into a table. The table PICTURE used in the example
 *  is created with
 *    create table picture (
 *        photo_id integer, 
 *        title varchar(48), 
 *        place varchar(48),
 *        sm_image blob,
 *        image blob,
 *        constraint picture_primary_key_constraint primary key(photo_id) )
 *
 *
 *  Compile:  javac InsertLobs.java
 *      Run: java -cp ./:/usr/share/java/mysql-connector-java-5.1.26-bin.jar InsertLobs 10 /home/sf_user/images img
 *  Command line arguments
 *    10:  the number of rows to be inserted
 *    /home/sf_user/testing:  the directory containing all the images files
 *    img:  the file name, i.e.,  sm_img1.jpg  img1.jpg,  sm_img2.jpg, img2.jpg, ..., 
 *
 */
public class InsertLobs {

    public static void main( String[] args)  {

	//  Take three arguments from the command line
	System.out.println("Number of Command Line Arguments: " + args.length);
	if (args.length != 3)  {
	    System.out.println("The number of command line arguments must be 3");
	    return;
	}

	int no_rows=Integer.parseInt(args[0]);
	System.out.println("Directory: " + args[1]);
	System.out.println("File Name: " + args[2]);
	System.out.println("We are going to insert "+ no_rows + " rows with lobs to table picture");
	
	//  change the following parameters to connect to other databases
	String username = "sqltest";
	String password = "sqltest";
	String drivername = "com.mysql.jdbc.Driver";
	String dbstring = "jdbc:mysql://127.0.0.1:8010";
	int index;

	String indexString=null;
	
	Connection conn;
	try {
	    // to connect to the database
	    conn = getConnected(drivername,dbstring, username,password);
	    System.out.println( "It has connected to the database");

	    //  create a preparedStatement with 
	    //   ?  represents the lobs to be inserted
	    PreparedStatement stmt = conn.prepareStatement(
              "insert into sqltest.picture values (?,'Eiffel Tower','Paris',?,? )" );

	    System.out.println( "Set up prepareStatement");

	    for (index=1;index<=no_rows;index++) {

		stmt.setString(1,Integer.toString(index+200));
		
		// Set the first parameter 
		File file = new File(args[1]+"/sm_"+args[2]+index+".jpg");
		stmt.setBinaryStream(2,new FileInputStream(file),(int)file.length());

		System.out.println( "Set Binary Stream 1 ");
		// set the second parameter
		file = new File(args[1]+"/"+args[2]+index+".jpg");
		stmt.setBinaryStream(3,new FileInputStream(file),(int)file.length());
		
		System.out.println( "Set Binary Stream 2 ");

		// execute the insert statement
		stmt.executeUpdate();
	  
		System.out.println( "Insertion of Row " + index + " succeeds");
	    }
	    
	    conn.close();
	} catch( Exception ex ) {
	    System.out.print( "Failed to  ");
	    System.out.println( ex.getMessage());
	}
    }

    /*
     *   To connect to the specified database
     */
    private static Connection getConnected( String drivername,
					    String dbstring,
					    String username, 
					    String password  ) 
	throws Exception {
	Class drvClass = Class.forName(drivername);
	DriverManager.registerDriver((Driver) drvClass.newInstance());
	return( DriverManager.getConnection(dbstring,username,password));
    }
} 

/**
 *  A simple example to demonstrate how to use JDBC PreparedStatement
 *  to retrieve a Blob from a table. The table PICTURE used in the example
 *  is created with
 *    create table picture (
 *        photo_id integer, 
 *        title varchar(48), 
 *        place varchar(48),
 *        sm_image blob,
 *        image blob,
 *        constraint picture_primary_key_constraint primary key(photo_id) )
 *
 *  It is based on a program downloaded from
 *    https://stackoverflow.com/questions/39231153/download-blob-data-from-mysql-database-via-java
 *
 *  Modified on March 15, 2019 using MySQL JDBC Driver shown below.
 *
 *  Compile:  javac InsertLobs.java
 *      Run: java -cp ./:/usr/share/java/mysql-connector-java-5.1.26-bin.jar GetLobs
 *
 *  @author  Li-Yan Yuan
 *
 */
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.ResultSetMetaData;

 public class GetLobs {

    public static void main(String[] args) {

	String user = "sqltest";
	String pass = "sqltest";
	String drivername = "com.mysql.jdbc.Driver";
	String host = "jdbc:mysql://localhost:8010";
	//    	String SQL = "SELECT image FROM picture";
	String SQL = "SELECT * FROM sqltest.picture"; 

	Connection conn = null;
	java.sql.PreparedStatement smt = null;
	InputStream input = null;
	FileOutputStream output = null;
	ResultSet rs = null;
	Integer index=1;
	Integer row_count=0;
	String column_name=null;
	int type;
	
	try {
	    Class.forName("com.mysql.jdbc.Driver");
	    System.out.println("Connecting...");
	    
	    conn = DriverManager.getConnection(host, user, pass);
	    System.out.println("Connection successful..\nNow creating query...");

	    smt = conn.prepareStatement(SQL);

	    System.out.println("b4 executionQuery() done");
	    rs = smt.executeQuery();
	    
	    System.out.println("executionQuery() done");

	    System.out.println("Getting file please be patient..");

	    
	    ResultSetMetaData rsetMetaData = rs.getMetaData();
	    int degree = rsetMetaData.getColumnCount();
	    System.out.println("Degree of the result set " + degree);

	    /*
	     *   We shall store each lob into one local file of the form
	     *    BIT,BINARY,VARBINARY,BLOB,LONGVARBINARY:
	     */
	    System.out.println("java.sql.Types " + java.sql.Types.LONGVARBINARY + ", " +  java.sql.Types.BLOB );
	    while (rs.next()) {
		row_count++;
		
		for (index=1;index<=degree;index++) {

		    column_name = rsetMetaData.getColumnLabel(index);
		    type = rsetMetaData.getColumnType(index);

		    System.out.println("Row: " + row_count + " Column: " + column_name + ",  Type: " + type + ",  Column Order: " + index);
				      
		    if (type==java.sql.Types.LONGVARBINARY || type==java.sql.Types.LONGVARCHAR||
			type==java.sql.Types.BLOB||type==java.sql.Types.BINARY )  {

			
			input = rs.getBinaryStream(column_name); //get it from col name
	    
			output = new FileOutputStream(new File("/home/lyuan/images/pic"+row_count+"_"+index+".jpg"));
			System.out.println("new file name "+ "/home/lyuan/images/pic"+row_count+"_"+index+".jpg");
			int r = 0;
			while ((r = input.read()) != -1) {
			    output.write(r);
			}
		    
			input.close();
			output.flush();
			output.close();
		    }
		}
	    }
	    System.out.println("File writing complete !");

	} catch (ClassNotFoundException e) {
	    System.err.println("Class not found!");
	    e.printStackTrace();
	} catch (SQLException e) {
	    System.err.println("Connection failed!");   
	    e.printStackTrace();
	} catch (FileNotFoundException e) {
	    System.err.println("File not found!");
	    e.printStackTrace();
	} catch (IOException e) {
	    System.err.println("File writing error..!");
	    e.printStackTrace();
	}finally {
	    if(rs != null){
		try {
		    input.close();
		    output.flush();
		    output.close();
		    smt.close();
		    conn.close();
		} catch (SQLException e) {
		    System.err.println("Connot close connecton!");
		} catch (IOException e) {
		    // TODO Auto-generated catch block
                e.printStackTrace();
		}
	    }
	}
    }
}

mysql> CREATE TABLE employee( 
         e_id int, 
         e_name varchar(64), 
         position varchar(64), 
         salary decimal(10,2) 
       ) 
       PARTITION BY GRID (e_id) TO 2 NODES WITH  mod (2);
Query OK, 0 rows affected (0.02 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO employee VALUES (1,'tom','engineer',1000.00);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO employee VALUES (2,'shelly','engineer',1000.00);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from employee;
+------+--------+----------+---------+
| e_id | e_name | position | salary  |
+------+--------+----------+---------+
|    2 | shelly | engineer | 1000.00 |
|    1 | tom    | engineer | 1000.00 |
+------+--------+----------+---------+
2 rows in set (0.04 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO employee VALUES (3,'tom','engineer',1000.00);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUES (4,'shelly','engineer',1000.00);
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from employee;
+------+--------+----------+---------+
| e_id | e_name | position | salary  |
+------+--------+----------+---------+
|    2 | shelly | engineer | 1000.00 |
|    1 | tom    | engineer | 1000.00 |
+------+--------+----------+---------+
2 rows in set (0.04 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO employee VALUES (3,'tom','engineer',1000.00);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUES (4,'shelly','engineer',1000.00);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select *  from employee;
+------+--------+----------+---------+
| e_id | e_name | position | salary  |
+------+--------+----------+---------+
|    2 | shelly | engineer | 1000.00 |
|    4 | shelly | engineer | 1000.00 |
|    1 | tom    | engineer | 1000.00 |
|    3 | tom    | engineer | 1000.00 |
+------+--------+----------+---------+
4 rows in set (0.04 sec)

 
Linux> tar -zxvf rubatodb_dist.tar.gz

Linux> cd bin

Linux> one_step_setup

Linux ./one_step_setup 
----------------------------------------------------------

   We are going to configure the RubatoDB server 

----------------------------------------------------------

   Enter monitor socket:  
   The   monitor socket:  8010
     Enter y to confirm:  y

----------------------------------------------------------

   The Monitor socket port is 8010 

----------------------------------------------------------


   Enter number of grid nodes:  4
   The   number of grid nodes:  4
     Enter y to confirm:  y

----------------------------------------------------------

   The number of Grid Nodes is 4 

----------------------------------------------------------


   Enter replication factor:  1
   The   replication factor:  1
     Enter y to confirm:  y

----------------------------------------------------------

   The Replication Factor is 1 

----------------------------------------------------------


----------------------------------------------------------
   Now, for each node, enter IP, Port, and Installation directory
----------------------------------------------------------


----------------------------------------------------------
   Node 0:  Replication 0 
           Enter the IP address:  
          Enter the port number:  
          Enter the replication port number:  
            Enter  installation:  

   Please confirm the following information 
   ------------------------------ 
        ip_address: 127.0.0.1
              port: 5000
 replication  port: 4000
      installation: /home/yuan/RubatoDB0_0
   ------------------------------ 

           Enter y to confirm::  y

----------------------------------------------------------


----------------------------------------------------------
   Node 1:  Replication 0 
           Enter the IP address:  
          Enter the port number:  
          Enter the replication port number:  
            Enter  installation:  

   Please confirm the following information 
   ------------------------------ 
        ip_address: 127.0.0.1
              port: 5100
 replication  port: 4100
      installation: /home/yuan/RubatoDB1_0
   ------------------------------ 

           Enter y to confirm::  y

----------------------------------------------------------


----------------------------------------------------------
   Node 2:  Replication 0 
           Enter the IP address:  
          Enter the port number:  
          Enter the replication port number:  
            Enter  installation:  

   Please confirm the following information 
   ------------------------------ 
        ip_address: 127.0.0.1
              port: 5200
 replication  port: 4200
      installation: /home/yuan/RubatoDB2_0
   ------------------------------ 

           Enter y to confirm::  y

----------------------------------------------------------


----------------------------------------------------------
   Node 3:  Replication 0 
           Enter the IP address:  
          Enter the port number:  
          Enter the replication port number:  
            Enter  installation:  

   Please confirm the following information 
   ------------------------------ 
        ip_address: 127.0.0.1
              port: 5300
 replication  port: 4300
      installation: /home/yuan/RubatoDB3_0
   ------------------------------ 

           Enter y to confirm::  y

----------------------------------------------------------

---------------------------------------------------------------------------------
 Node ID  Replication ID   IP Address   Port   R-Port     Installation Directory 
---------------------------------------------------------------------------------
       0        0         127.0.0.1     5000      4000     /home/yuan/RubatoDB0_0 
       1        0         127.0.0.1     5100      4100     /home/yuan/RubatoDB1_0 
       2        0         127.0.0.1     5200      4200     /home/yuan/RubatoDB2_0 
       3        0         127.0.0.1     5300      4300     /home/yuan/RubatoDB3_0 
---------------------------------------------------------------------------------

   Enter y to confirm the info:  y

Linux> 

Linux> tar -zxvf rubatodb_dist.tar.gz

Linux> cd RubatoDB_dist

Linux> ./auto_installation.sh

(Then follows the on-screen instruction to finish the whole process.)

mysql> show tables in definition_schema;
+-----------------------------+
| Tables_in_definition_schema |
+-----------------------------+
| checkpoint_record           |
| configuration_values        |
| create_table_stmt           |
| grid_nodes                  |
| grid_sockets                |
| index_files                 |
| key_column_usage            |
| partition_parent            |
| referential_constraints     |
| replication_sockets         |
| replications                |
| schemata                    |
| sqlstate_message            |
| system_limitation           |
| table_constraints           |
| table_distribution          |
| table_partitions            |
| tables                      |
| users                       |
+-----------------------------+
19 rows in set (0.00 sec)

mysql> select * from definition_schema.replications;
+---------+----------------+------------+---------------+-----------------+--------+------------+
| grid_id | replication_id | ip_address | client_socket | internal_socket | status | process_id |
+---------+----------------+------------+---------------+-----------------+--------+------------+
|       0 |              0 | 10.10.0.2  |          8010 |            5000 |      1 |      26780 |
|       0 |              1 | 10.10.0.2  |          8020 |            5010 |      2 |      27949 |
|       1 |              0 | 10.10.0.2  |             0 |            5100 |      1 |      26952 |
|       1 |              1 | 10.10.0.2  |             0 |            5110 |      2 |      27072 |
|       2 |              0 | 10.10.0.2  |             0 |            5200 |      1 |      27082 |
|       2 |              1 | 10.10.0.2  |             0 |            5210 |      2 |      27090 |
|       3 |              0 | 10.10.0.2  |             0 |            5300 |      2 |      27092 |
|       3 |              1 | 10.10.0.2  |             0 |            5310 |      1 |      27079 |
+---------+----------------+------------+---------------+-----------------+--------+------------+
8 rows in set (0.05 sec)
hen

mysql> select * from definition_schema.grid_nodes;
+---------+------------+-----------+---------------+----------------+
| grid_id | ip_address | grid_name | client_socket | current_status |
+---------+------------+-----------+---------------+----------------+
|       0 | 127.0.0.1  | Node0     |          8010 |              1 |
|       1 | 127.0.0.1  | Node1     |             0 |              1 |
|       2 | 127.0.0.1  | Node2     |             0 |              0 |
+---------+------------+-----------+---------------+----------------+

mysql> select * from definition_schema.schemata;
+--------------+-------------------+--------------+---------------------+----------+---------------+
| catalog_name | schema_name       | schema_owner | date_created        | no_grids | list_of_grids |
+--------------+-------------------+--------------+---------------------+----------+---------------+
| def          | definition_schema | root         | 2017-09-05 05:59:23 |        1 | 0,            |
| def          | mytest            | mytest2      | 2017-09-05 06:36:43 |        2 | 0,1,          |
| def          | test              | root         | 2017-09-05 05:59:23 |        1 | 0,            |
+--------------+-------------------+--------------+---------------------+----------+---------------+
3 rows in set (0.02 sec)

Linux:> logicsql -h

Usage: logicsql [-options]

where options include
   -d                  specify the installation directory name, 
   -p <port_number>    specify the port number used by the server, 
                       the default is specifed by the configuation file
   -n                  the grid node ID
   -c                  create the initial database
   -r                  display all the output on screen
   -h                  print this message

Linux:>

Linux> mysql --host 127.0.0.1 --port 8010 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.6.23-Rubato DB V4  MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

mysql> 

CREATE USER user_name [ IDENTIFIED BY 'password string' ]

   ALTER USER user_name IDENTIFIED BY 'password_string'.

Grant Statement ::= GRANT < priv_type > ON [TABLE] < priv_level > TO user_name [WITH GRANT OPTION]

priv_type ::= ALL | SELECT | INSERT | DELETE | UPDATE | CREATE | CREATE_USER | CREATE_VIEW | DROP | ALTER | INDEX

priv_level ::= * | *.* | db_name.* | db_name.table_name | table_name

GRANT ALL ON sarah_db.* TO sarah;

GRANT SELECT ON db1.invoice TO sarah; 

Revoke Statement ::= REVOKE < priv_type > ON [TABLE] < priv_level > FROM user_name 

REVOKE ALL [PRIVILEGES], GRANT OPTION FROM User_name

REVOKE SELECT ON sarah_db.* FROM sarah;

REVOKE ALL PRIVILEGS, GRANT OPTION  ON FROM sarah; 

import java.io.*;
import java.sql.*;
/**
 *  A simple example to demonstrate how to use JDBC to connect to RubatoDB
 *  @author  Li-Yan Yuan
 *  
 *  Compile:   javac SimpleJava.java
 *      Run:   java -cp ./:/usr/share/java/mysql-connector-java-5.1.26-bin.jar SimpleJava
 */
public class SimpleJava {

    public static void main( String[] args)  {

	//  change the following parameters to connect to other databases
	String username = "root";
	String password = "manager";
	String drivername = "com.mysql.jdbc.Driver";
	String dbstring = "jdbc:mysql://127.0.0.1:8010";
	String query;
	int width=30;
	Connection conn;
	try {
	    // to connect to the database
	    conn = getConnected(drivername,dbstring, username,password);
	    System.out.println( "\n\nIt has been connected to the database\n\n");

	    // A simple query
	    query = "SELECT schema_name, date_created, No_grids FROM definition_schema.schemata";	    
	    System.out.println("Query:  " + query+"\n\n");

	    // Create the JDBC Statement 
	    Statement stmt = conn.createStatement();

	    // Execute the given query
	    ResultSet rset = stmt.executeQuery(query);

            // get the meta data of the result set
            ResultSetMetaData rsetMetaData = rset.getMetaData();
            int no_columns = rsetMetaData.getColumnCount();

            // print the label line
            System.out.println();
            for ( int i = 1; i <= no_columns; i++ ) {
                String label = rsetMetaData.getColumnLabel(i);
                System.out.print(label);
                for ( int j = 0; j < width - label.length(); j++ )
                    System.out.print(" ");
            }

            System.out.println();
            for ( int i = 1; i <= no_columns * width; i++ )
                System.out.print("-");
            System.out.println();

            // display the result tuples
            while (rset.next()) {
                for ( int i = 1; i <= no_columns; i++ ) {
                    String s = rset.getString(i);
                    System.out.print(s);
                    for (int j = 0; j < width - s.length(); j++ )
                        System.out.print(" ");
                }
                System.out.println();
            }
	    
	    System.out.println();
            for ( int i = 1; i <= no_columns * width; i++ )
                System.out.print("-");
            System.out.println();
	    System.out.println();
	    
           // close the result set, the statement, and the connection
            rset.close();
            stmt.close();

	    conn.close();
	} catch( Exception ex ) {
	    System.out.print( "Failed to  ");
	    System.out.println( ex.getMessage());
	}
    }

    /*
     *   To connect to the specified database
     */
    private static Connection getConnected( String drivername,
					    String dbstring,
					    String username, 
					    String password  ) 
	throws Exception {
	Class drvClass = Class.forName(drivername);
	DriverManager.registerDriver((Driver) drvClass.newInstance());
	return( DriverManager.getConnection(dbstring,username,password));
    }
}

#!/usr/bin/python3
#
#  Usage:  python3 sf_test.py
#
import pymysql

# Open database connection
db = pymysql.connect("127.0.0.1","root","manager","sqltest",8010 )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = "SELECT date_created,no_grids,schema_name FROM definition_schema.schemata " 

print("\n\n     Query:  ", sql,"\n")

try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   print("  -------------------------------------------------------------------------------------")
   print("    Date_created                   no_grids                     Database Name          ")
   print("  -------------------------------------------------------------------------------------")
   
   for row in results:
      # print(row)
      # Now print fetched result
      print ("   ",  row[0], "               ", row[1], "                 ", row[2]) 


   print("  -------------------------------------------------------------------------------------\n")      
except:
   print ("Error: unable to fetch data")

# disconnect from server
db.close()

#
#  A Python3 program to demonstrate how to
#  (1) create a user and a database of the same name,
#  (2) create a table,
#  (3) insert a new row to a table created, and
#  (4) load a csv file into the newly created table in (2)
#
#  Usage:  python3 sf_loading.py
#
#!/usr/bin/python3
import pymysql

#  Specify the full path of the CSV data file that must be accessible by the RubaoDB server
#  A sample data file is given at the end of this file
csv_file="/home/lyuan/rubatodb.3.0/package3/sf_test/cases1node/pet.txt"

# specify the name of a user name and an sql schema (it is also called database by MySQL)
# 
schema_name="sqltest"

#  Define a function to create a user and a schema with the given name
#  It also grants all privileges to the user on the schema of the same name
#
def create_schema(user_name):
    stmt1 = "DROP USER IF EXISTS " + user_name
    stmt2 = "DROP DATABASE IF EXISTS " + user_name
    stmt3 = "CREATE USER " + user_name
    stmt4 = "CREATE DATABASE " + user_name    
    stmt5 = "GRANT ALL on "+user_name+".* to " + user_name
    try:
        cursor.execute(stmt1)
        cursor.execute(stmt2)
        cursor.execute(stmt3)
        cursor.execute(stmt4)
        cursor.execute(stmt5)
    except:
        print ("Error: unable to drop/create user/database " + user_name)

        
#  Open a database connection
db = pymysql.connect("127.0.0.1","root","manager","definiton_schema",8010)
cursor = db.cursor()

#  Set auto commit on
cursor.execute("set autocommit=1")

#  Create an SQL schema  and a user of the given name
create_schema(schema_name)

#  Specify the database to use 
cursor.execute("USE " + schema_name)

#  Create a table named pet
#  (This is an example from the MySQL documentation)
create_table_stmt="CREATE TABLE pet ( \
        name    varchar(32) primary key, \
        owner   varchar(64) not null, \
        species varchar(30), \
        sex     char(1), \
        birth   date, \
        death   date \
)"
cursor.execute(create_table_stmt)

# Insert a new row into Table pet
cursor.execute("INSERT INTO pet VALUES('Sarah', 'Sarah', 'goat', 'f', '1979-08-31','2015-02-28')")

# Load a CSV file into the table
load_stmt="LOAD DATA LOCAL INFILE '" + csv_file + "' INTO TABLE pet FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'"

try:
    cursor.execute(load_stmt)
    print("\n\nthe csv file " + csv_file + "has been loaded into the table pet\n\n")
except:
    print ("Error: unable to load the given csv file")


# disconnect from server
db.close()

#
#  the sample CSV file for this program
#
# Fluffy,Harold,cat,f,1993-02-04,101,,	 
# Claws,Gwen,cat,m,1994-03-17,202,, 
# Buffy,Harold,dog,f,1989-05-13,303,,
# Fang,Benny,dog,m,1990-08-27,404,,
# Bowser,Diane,dog,m,1979-08-31,505,1995-07-29,
# Chirpy,Gwen,bird,f,1998-09-11,606,,
# Whistler,Gwen,bird,f,1997-12-09,707,,
# Slim,Benny,snake,m,1996-04-29,808,,

        
Linux>  cd ~/RubatoDB0_0/bin/
Linux>  ./one_step_loadtest -u

Linux> bin/one_step_loadtest -u
----------------------------------------------------------
  We are going to conduct the load Test 
           Enter the IP address:  
          Enter the port number:  
               Enter  user name:  
                Enter  password:  
      Enter the number of nodes:  4
Enter the number of warehouses:  1000

Please confirm the following information 
------------------------------ 
       ip_address: 127.0.0.1
             port: 8010
        user name: tpcone
      no of nodes: 4
 no of warehouses: 1000
------------------------------ 

Enter y to confirm the info:  y

It has been connected to the RubatoDB server as root

  The RubatoDB server has 4 nodes, and the number of nodes used for testing is 4 

----------------------------------------------------------

Populate the CSV files with 1000 warehouses

----------------------------------------------------------
......................................................................................................................
......................................................................................................................
......................................................................................................................
......................................................................................................................
......................................................................................................................
......................................................................................................................
......................................................................................................................
......................................................................................................................
........................................................

All the CSV files are created and stored at the directory /tmp/loadtest_files/ 

Table stock has been created
----------------------------------------------------------
   Load stock table using csv files in /tmp/loadtest_files and run query to see performance
----------------------------------------------------------
 Query  1: LOAD DATA INFILE '/tmp/loadtest_files/stock.csv' INTO TABLE stock  FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' 
(Load)  100000000 rows in set (435499 millisec)


 Query  2: select count(*) from stock 
+----------+
| count(*) |
+----------+
|100000000 |
+----------+
 1 rows in set (50474 millisec)


 Query  3: select * from stock where s_w_id=1 and s_i_id=1000 
+--------------------------------------------------------------------------------------------------------------+
| s_w_id | s_i_id | s_quantity |     s_dist_01           |  ...  | s_ytd | s_order_cnt | s_remote_cnt | s_data |
+--------------------------------------------------------------------------------------------------------------+
|      1 |   1000 |         58 |mbpqpheustdeqqytpdyniubz |  ...  |     0 |           0 |            0 |ldxjf...|
+--------------------------------------------------------------------------------------------------------------+

 1 rows in set (2 millisec)


 Query  4: select count(*) from stock where s_w_id=1 and s_i_id>1000 
+----------+
| count(*) |
+----------+
|    99000 |
+----------+
 1 rows in set (169 millisec)


 Query  5: select count(*), max(s_i_id),sum(s_quantity)  from stock 
+------------------------------------------+
| count(*) | max(s_i_id) | sum(s_quantity) |
+------------------------------------------+
|100000000 |      100000 |      5500316360 |
+------------------------------------------+
 1 rows in set (69427 millisec)


 Query  6: select count(*), max(s_i_id),sum(s_quantity),s_w_id  from stock group by s_w_id 
+---------------------------------------------------+
| count(*) | max(s_i_id) | sum(s_quantity) | s_w_id |
+---------------------------------------------------+
|   100000 |      100000 |         5495286 |      1 |
|   100000 |      100000 |         5507308 |      5 |
|   100000 |      100000 |         5486376 |      9 |
|                                                   |
|        ...                                        |
|                                                   |
+---------------------------------------------------+
 1000 rows in set (122437 millisec)


 Query  7: select count(*), max(s_i_id),sum(s_quantity),s_w_id  from stock group by s_w_id  having sum(s_quantity) <5500000  
+---------------------------------------------------+
| count(*) | max(s_i_id) | sum(s_quantity) | s_w_id |
+---------------------------------------------------+
|   100000 |      100000 |         5495286 |      1 |
|   100000 |      100000 |         5486376 |      9 |
|   100000 |      100000 |         5497697 |     13 |
|                                                   |
|        ...                                        |
|                                                   |
+---------------------------------------------------+
 484 rows in set (130158 millisec)


 Query  8: select count(*) from stock where s_w_id <=s_i_id 
+----------+
| count(*) |
+----------+
| 99500500 |
+----------+
 1 rows in set (61473 millisec)


 Query  9: select count(*) from stock where s_w_id <s_i_id or s_w_id >s_i_id 
+----------+
| count(*) |
+----------+
| 99999000 |
+----------+
 1 rows in set (76051 millisec)


 Query 10: select s_w_id+s_i_id,sum(s_quantity) from stock group by s_w_id+s_i_id 
+---------------------------------+
| s_w_id+s_i_id | sum(s_quantity) |
+---------------------------------+
|             2 |              81 |
|             3 |             116 |
|             4 |             204 |
|                                 |
|        ...                      |
|                                 |
+---------------------------------+
 100003 rows in set (156311 millisec)


 Query 11: select s_w_id,s_i_id,s_quantity,s_dist_01,s_ytd,s_order_cnt,s_remote_cnt from stock 
+---------------------------------------------------------------------------------------------+
| s_w_id | s_i_id | s_quantity |               s_dist_01 | s_ytd | s_order_cnt | s_remote_cnt |
+---------------------------------------------------------------------------------------------+
|      1 |      1 |         81 |jihujsdbyamkwygzrxphzoab |     0 |           0 |            0 |
|      1 |      2 |         17 |wksghxubfbeqvpnerkekfpyp |     0 |           0 |            0 |
|      1 |      3 |         97 |uindhmomokobneomjpdhnnnm |     0 |           0 |            0 |
|                                                                                             |
|                    ...                                                                      |
|                                                                                             |
+---------------------------------------------------------------------------------------------+
 400000 rows in set (200 millisec)

Linux>  ./one_step_loadtest -h

Linux>  RubatoDB_dist/bin/one_step_tpctest -g4 -m 129.128.255.123 -p 8010 -w1000 -b manager

[9]   TEST SUMMARY      
     -----------------------------------------------------------------------

              Number of RubatoDB Grids:       4                              
                     Number of Servers:       2                              
                  Number of Warehouses:    1000                               
          Number of Concurrent Clients:   10000                               
     Number of Transactions per Minute:   27448                               

                                  tpmC:   12351                            

     ---------------------------------------------------------

Linux>  ./one_step_tpctest -h

        
Linux>  /home/lyuan/RubatoDB0_0/bin/one_step_test -g2 -w400

-------------  Records of the TPC-C Test using one_step_tpctest  ----------

[1]  Database 'tpcone' and User 'tpcone' have been created 

[2]  We are going to use the CSV files stored in '/mnt/storage/lyuan/tmp/tpctest400'

[3]  All 12 TPC tables have been created using SQL file 'setup.sql' 

[4]  The following tables will be loaded 
      item, warehouse, district, stock2, customer2, history, 
      orders, new_order, order_line, stock1, customer1, customer3, 

[5]  Start the TPC-C Test with 2 grids, 400 warehouses, 4000 clients, in around 150 minutes
       1 .........  20.........  40.........  60.........  80......... 100
         ......... 120......... 140......... 160......... 180......... 200
         ......... 220......... 240......... 260......... 280......... 300
         ......... 320......... 340......... 360......... 380......... 400
                          .  .  .
         ......... 320......... 340......... 360......... 380......... 400
      10 .........  20.........  40.........  60.........  80......... 100
         ......... 120......... 140......... 160......... 180......... 200
         ......... 220......... 240......... 260......... 280......... 300
         ......... 320......... 340......... 360......... 380......... 400
        All 4000 clients have been connected into the testing server 

      ..................................................
      ............................................

    Now waiting for all clients (threads) to finish, one at a time --------- 
     1.  ...................................................
         ..................................................
         ..................................................
                          .  .  .
    10.  ..................................................
         .................................................
             ( All Clients' Test have been done ) 

[6]    Retrieve the Checkpoint Records  
+--------------------------------------------------------------------------------------------------+
| chk_time            | clients | transaction_id | roll_backs | committed | transaction_per_minute |
+--------------------------------------------------------------------------------------------------+
| 2021-04-24 17:31:59 |       2 |             70 |          0 |         0 |                      0 |
| 2021-04-24 17:47:13 |    4001 |         118257 |        119 |    108048 |                   7100 |
| 2021-04-24 18:17:15 |    4001 |         446281 |        620 |    329145 |                  10979 |
| 2021-04-24 18:47:16 |    4001 |         772369 |        668 |    325495 |                  10866 |
| 2021-04-24 19:17:29 |    4001 |        1103387 |        672 |    328441 |                  10891 |
| 2021-04-24 19:47:30 |    3988 |        1430659 |        678 |    328457 |                  10965 |
| 2021-04-24 20:17:30 |      82 |        1611424 |        212 |    176854 |                   5902 |
| 2021-04-24 20:27:20 |       2 |        1612095 |          0 |       585 |                     59 |
+--------------------------------------------------------------------------------------------------+

[7]   Notes to the checkpoint records displayed in [6] 
    ----------------------------------------------------------
    Note that each row represents the testing data extracted from the last 30 minutes (between two chk_times)
    Thus the Testing Result can be characterized by the last column, transaction_per_minute,  of any row R, if 
     (1) clients[R] is larger than 4000, and 
     (2) clients[Rp], where Rp is the previous row of R, is also larger than 4000
    Assum that R is choosen such that transaction_per_minute[R] = 10800, 
    By the TPC-C standard,  tpmC of the testing is  4860, that is 45% of 10800. 
    This is because tpmC is specified as the number of New-Order transactions conducted during 30 minutes,
    while the number of New-Order transactions must be 45% of all transactions. 
    ----------------------------------------------------------

[8]  Conduct the TPC-Check to see if all 10 conditions are satisfied.
     The total number of warehouses is 400

    Test Conditions 1, 8, and 9:
    Consistency Condition 1 is satisfied
    Consistency Condition 8 is satisfied
    Consistency Condition 9 is satisfied

    Test Condition 11: 
    Consistency condition 11 is satisfied

    Test Conditions 2 and 3:
    Consistency Condition 2 is satisfied
    Consistency Condition 3 is satisfied

    Test Condition 4:
    Consistency Condition 4 is satisfied

    Test Conditions 5 and 7
    Consistency Condition 5 is satisfied
    Consistency Condition 7 is satisfied

    Test Condition 6: It may take a while.
    Consistency Condition 6 is satisfied

    Test Conditions 10 and 12: this may take quite a while.
    Consistency Condition 10 is satisfied
    Consistency Condition 12 is satisfied


[9]   TEST SUMMARY      
     -----------------------------------------------------------------------

              Number of RubatoDB Grids:      2                              
                     Number of Servers:      2                              
                  Number of Warehouses:    400                               
          Number of Concurrent Clients:   4000                               
     Number of Transactions per Minute:  10979                               

                                  tpmC:   4940                            

     ---------------------------------------------------------

[10]  Displays the Grid and Replication Info

+---------------------------------------+
| grid_id | replication_id | ip_address |
+---------------------------------------+
|       0 |              0 |  127.0.0.1 |
|       1 |              0 |  127.0.0.1 |
+---------------------------------------+