EVOLUTION-MANAGER

Edit File: json.test

--source include/not_embedded.inc
--source include/have_partition.inc

let $MYSQLD_DATADIR= `select @@datadir`;

--copy_file $MTR_SUITE_DIR/std_data/biblio.json $MYSQLD_DATADIR/test/biblio.json
--copy_file $MTR_SUITE_DIR/std_data/bib0.json $MYSQLD_DATADIR/test/bib0.json
--copy_file $MTR_SUITE_DIR/std_data/expense.json $MYSQLD_DATADIR/test/expense.json
--copy_file $MTR_SUITE_DIR/std_data/mulexp3.json $MYSQLD_DATADIR/test/mulexp3.json
--copy_file $MTR_SUITE_DIR/std_data/mulexp4.json $MYSQLD_DATADIR/test/mulexp4.json
--copy_file $MTR_SUITE_DIR/std_data/mulexp5.json $MYSQLD_DATADIR/test/mulexp5.json

--echo #
--echo # Testing doc samples
--echo #
CREATE TABLE t1
(
  ISBN CHAR(15),
  LANG CHAR(2),
  SUBJECT CHAR(32),
  AUTHOR CHAR(64),
  TITLE CHAR(32),
  TRANSLATION CHAR(32),
  TRANSLATOR CHAR(80),
  PUBLISHER CHAR(32),
  DATEPUB int(4)
) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json';
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Testing Jpath. Get the number of authors
--echo #
CREATE TABLE t1
(
  ISBN CHAR(15),
  Language CHAR(2) FIELD_FORMAT='$.LANG',
  Subject CHAR(32) FIELD_FORMAT='$.SUBJECT',
  Authors INT(2) FIELD_FORMAT='$.AUTHOR[#]',
  Title CHAR(32) FIELD_FORMAT='$.TITLE',
  Translation CHAR(32) FIELD_FORMAT='$.TRANSLATION',
  Translator CHAR(80) FIELD_FORMAT='$.TRANSLATOR',
  Publisher CHAR(20) FIELD_FORMAT='$.PUBLISHER.NAME',
  Location CHAR(16) FIELD_FORMAT='$.PUBLISHER.PLACE',
  Year int(4) FIELD_FORMAT='$.DATEPUB'
)
ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json';
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Concatenates the authors
--echo #
CREATE TABLE t1
(
  ISBN CHAR(15),
  Language CHAR(2) FIELD_FORMAT='$.LANG',
  Subject CHAR(32) FIELD_FORMAT='$.SUBJECT',
  AuthorFN CHAR(128) FIELD_FORMAT='$.AUTHOR[" and "].FIRSTNAME',
  AuthorLN CHAR(128) FIELD_FORMAT='$.AUTHOR[" and "].LASTNAME',
  Title CHAR(32) FIELD_FORMAT='$.TITLE',
  Translation CHAR(32) FIELD_FORMAT='$.TRANSLATION',
  Translator CHAR(80) FIELD_FORMAT='$.TRANSLATOR',
  Publisher CHAR(20) FIELD_FORMAT='$.PUBLISHER.NAME',
  Location CHAR(16) FIELD_FORMAT='$.PUBLISHER.PLACE',
  Year int(4) FIELD_FORMAT='$.DATEPUB'
)
ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json';
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Testing expanding authors
--echo #
CREATE TABLE t1
(
  ISBN CHAR(15),
  Language CHAR(2) FIELD_FORMAT='$.LANG',
  Subject CHAR(32) FIELD_FORMAT='$.SUBJECT',
  AuthorFN CHAR(128) FIELD_FORMAT='$.AUTHOR[*].FIRSTNAME',
  AuthorLN CHAR(128) FIELD_FORMAT='$.AUTHOR[*].LASTNAME',
  Title CHAR(32) FIELD_FORMAT='$.TITLE',
  Translation CHAR(32) FIELD_FORMAT='$.TRANSLATION',
  Translator CHAR(80) FIELD_FORMAT='$.TRANSLATOR',
  Publisher CHAR(20) FIELD_FORMAT='$.PUBLISHER.NAME',
  Location CHAR(16) FIELD_FORMAT='$.PUBLISHER.PLACE',
  Year int(4) FIELD_FORMAT='$.DATEPUB'
)
ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json';
SELECT * FROM t1;
UPDATE t1 SET AuthorFN = 'Philippe' WHERE AuthorLN = 'Knab';
SELECT * FROM t1 WHERE ISBN = '9782212090819';

--echo #
--echo # To add an author a new table must be created
--echo #
CREATE TABLE t2 (
FIRSTNAME CHAR(32),
LASTNAME CHAR(32))
ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='biblio.json' OPTION_LIST='Object=$[1].AUTHOR';
SELECT * FROM t2;
INSERT INTO t2 VALUES('Charles','Dickens');
SELECT * FROM t1;
DROP TABLE t1;
DROP TABLE t2;

--echo #
--echo # Check the biblio file has the good format
--echo #
CREATE TABLE t1
(
  line char(255)
)
ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='biblio.json';
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Testing a pretty=0 file
--echo #
CREATE TABLE t1
(
  ISBN CHAR(15) NOT NULL,
  Language CHAR(2) FIELD_FORMAT='$.LANG',
  Subject CHAR(32) FIELD_FORMAT='$.SUBJECT',
  AuthorFN CHAR(128) FIELD_FORMAT='$.AUTHOR[*].FIRSTNAME',
  AuthorLN CHAR(128) FIELD_FORMAT='$.AUTHOR[*].LASTNAME',
  Title CHAR(32) FIELD_FORMAT='$.TITLE',
  Translation CHAR(32) FIELD_FORMAT='$.TRANSLATED.PREFIX',
  TranslatorFN CHAR(80) FIELD_FORMAT='$.TRANSLATED.TRANSLATOR.FIRSTNAME',
  TranslatorLN CHAR(80) FIELD_FORMAT='$.TRANSLATED.TRANSLATOR.LASTNAME',
  Publisher CHAR(20) FIELD_FORMAT='$.PUBLISHER.NAME',
  Location CHAR(16) FIELD_FORMAT='$.PUBLISHER.PLACE',
  Year int(4) FIELD_FORMAT='$.DATEPUB',
  INDEX IX(ISBN)
)
ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='bib0.json' LRECL=320 OPTION_LIST='Pretty=0';
SHOW INDEX FROM t1;
SELECT * FROM t1;
DESCRIBE SELECT * FROM t1 WHERE ISBN = '9782212090819';
--error ER_GET_ERRMSG
UPDATE t1 SET AuthorFN = 'Philippe' WHERE ISBN = '9782212090819';
DROP TABLE t1;

--echo #
--echo # A file with 2 arrays
--echo #
CREATE TABLE t1 (
WHO CHAR(12),
WEEK INT(2) FIELD_FORMAT='$.WEEK[*].NUMBER',
WHAT CHAR(32) FIELD_FORMAT='$.WEEK[].EXPENSE["+"].WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='$.WEEK[].EXPENSE[+].AMOUNT')
ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Now it can be fully expanded
--echo #
CREATE TABLE t1 (
WHO CHAR(12),
WEEK INT(2) FIELD_FORMAT='$.WEEK[*].NUMBER',
WHAT CHAR(32) FIELD_FORMAT='$.WEEK[*].EXPENSE[*].WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='$.WEEK[*].EXPENSE[*].AMOUNT')
ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
#--error ER_GET_ERRMSG
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # A table showing many calculated results
--echo #
CREATE TABLE t1 (
WHO CHAR(12) NOT NULL,
WEEKS CHAR(12) NOT NULL FIELD_FORMAT='$.WEEK[", "].NUMBER',
SUMS CHAR(64) NOT NULL FIELD_FORMAT='$.WEEK["+"].EXPENSE[+].AMOUNT',
SUM DOUBLE(8,2) NOT NULL FIELD_FORMAT='$.WEEK[+].EXPENSE[+].AMOUNT',
AVGS CHAR(64) NOT NULL FIELD_FORMAT='$.WEEK["+"].EXPENSE[!].AMOUNT',
SUMAVG DOUBLE(8,2) NOT NULL FIELD_FORMAT='$.WEEK[+].EXPENSE[!].AMOUNT',
AVGSUM DOUBLE(8,2) NOT NULL FIELD_FORMAT='$.WEEK[!].EXPENSE[+].AMOUNT',
AVGAVG DOUBLE(8,2) NOT NULL FIELD_FORMAT='$.WEEK[!].EXPENSE[!].AMOUNT',
AVERAGE DOUBLE(8,2) NOT NULL FIELD_FORMAT='$.WEEK[!].EXPENSE[*].AMOUNT')
ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Expand expense in 3 one week tables
--echo #
CREATE TABLE t2 (
WHO CHAR(12),
WEEK INT(2) FIELD_FORMAT='$.WEEK[0].NUMBER',
WHAT CHAR(32) FIELD_FORMAT='$.WEEK[0].EXPENSE[*].WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='$.WEEK[0].EXPENSE[*].AMOUNT')
ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
SELECT * FROM t2;

CREATE TABLE t3 (
WHO CHAR(12),
WEEK INT(2) FIELD_FORMAT='$.WEEK[1].NUMBER',
WHAT CHAR(32) FIELD_FORMAT='$.WEEK[1].EXPENSE[*].WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='$.WEEK[1].EXPENSE[*].AMOUNT')
ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
SELECT * FROM t3;

CREATE TABLE t4 (
WHO CHAR(12),
WEEK INT(2) FIELD_FORMAT='$.WEEK[2].NUMBER',
WHAT CHAR(32) FIELD_FORMAT='$.WEEK[2].EXPENSE[*].WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='$.WEEK[2].EXPENSE[*].AMOUNT')
ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='expense.json';
SELECT * FROM t4;

--echo #
--echo # The expanded table is made as a TBL table
--echo #
CREATE TABLE t1 (
WHO CHAR(12),
WEEK INT(2),
WHAT CHAR(32),
AMOUNT DOUBLE(8,2))
ENGINE=CONNECT TABLE_TYPE=TBL TABLE_LIST='t2,t3,t4';
SELECT * FROM t1;
DROP TABLE t1, t2, t3, t4;

--echo #
--echo # Three partial JSON tables
--echo #
CREATE TABLE t2 (
WHO CHAR(12),
WEEK INT(2),
WHAT CHAR(32) FIELD_FORMAT='$.EXPENSE[*].WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='$.EXPENSE.[*].AMOUNT')
ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp3.json';
SELECT * FROM t2;

CREATE TABLE t3 (
WHO CHAR(12),
WEEK INT(2),
WHAT CHAR(32) FIELD_FORMAT='$.EXPENSE[*].WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='$.EXPENSE.[*].AMOUNT')
ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp4.json';
SELECT * FROM t3;

CREATE TABLE t4 (
WHO CHAR(12),
WEEK INT(2),
WHAT CHAR(32) FIELD_FORMAT='$.EXPENSE[*].WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='$.EXPENSE.[*].AMOUNT')
ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp5.json';
SELECT * FROM t4;

--echo #
--echo # The complete table can be a multiple JSON table
--echo #
CREATE TABLE t1 (
WHO CHAR(12),
WEEK INT(2),
WHAT CHAR(32) FIELD_FORMAT='$.EXPENSE[*].WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='$.EXPENSE.[*].AMOUNT')
ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp*.json' MULTIPLE=1;
SELECT * FROM t1 ORDER BY WHO, WEEK, WHAT, AMOUNT;
DROP TABLE t1;

--echo #
--echo # Or also a partition JSON table
--echo #
CREATE TABLE t1 (
WHO CHAR(12),
WEEK INT(2),
WHAT CHAR(32) FIELD_FORMAT='$.EXPENSE[*].WHAT',
AMOUNT DOUBLE(8,2) FIELD_FORMAT='$.EXPENSE.[*].AMOUNT')
ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='mulexp%s.json';
ALTER TABLE t1
PARTITION BY LIST COLUMNS(WEEK) (
PARTITION `3` VALUES IN(3),
PARTITION `4` VALUES IN(4),
PARTITION `5` VALUES IN(5));
SHOW WARNINGS;
SELECT * FROM t1;
SELECT * FROM t1 WHERE WEEK = 4;
DROP TABLE t1, t2, t3, t4;

#
# Clean up
#
--remove_file $MYSQLD_DATADIR/test/biblio.json
--remove_file $MYSQLD_DATADIR/test/bib0.dnx
--remove_file $MYSQLD_DATADIR/test/bib0.json
--remove_file $MYSQLD_DATADIR/test/expense.json
--remove_file $MYSQLD_DATADIR/test/mulexp3.json
--remove_file $MYSQLD_DATADIR/test/mulexp4.json
--remove_file $MYSQLD_DATADIR/test/mulexp5.json