Changeset: 026c7063eb55 for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=026c7063eb55 Added Files: sql/test/sciql2sql/Tests/01_create_01.sql sql/test/sciql2sql/Tests/01_create_02.sql sql/test/sciql2sql/Tests/01_create_03.sql sql/test/sciql2sql/Tests/01_create_04.sql sql/test/sciql2sql/Tests/01_create_05.sql sql/test/sciql2sql/Tests/01_create_06.sql sql/test/sciql2sql/Tests/01_create_07.sql sql/test/sciql2sql/Tests/01_create_08.sql sql/test/sciql2sql/Tests/01_create_09.sql sql/test/sciql2sql/Tests/01_create_10.sql sql/test/sciql2sql/Tests/01_create_11.sql sql/test/sciql2sql/Tests/01_create_12.sql sql/test/sciql2sql/Tests/01_create_13.sql sql/test/sciql2sql/Tests/01_create_14.sql sql/test/sciql2sql/Tests/01_create_15.sql sql/test/sciql2sql/Tests/01_create_16.sql sql/test/sciql2sql/Tests/01_create_17.sql sql/test/sciql2sql/Tests/01_create_18.sql sql/test/sciql2sql/Tests/01_create_19.sql sql/test/sciql2sql/Tests/01_create_20.sql sql/test/sciql2sql/Tests/01_create_21.sql sql/test/sciql2sql/Tests/01_create_22.sql sql/test/sciql2sql/Tests/01_create_23.sql sql/test/sciql2sql/Tests/02_update_01.sql sql/test/sciql2sql/Tests/02_update_02.sql sql/test/sciql2sql/Tests/02_update_03.sql sql/test/sciql2sql/Tests/02_update_04.sql sql/test/sciql2sql/Tests/02_update_05.sql sql/test/sciql2sql/Tests/03_delete_01.sql sql/test/sciql2sql/Tests/04_insert_01.sql sql/test/sciql2sql/Tests/04_insert_02.sql sql/test/sciql2sql/Tests/04_insert_03.sql sql/test/sciql2sql/Tests/04_insert_04.sql sql/test/sciql2sql/Tests/README Branch: SciQL-2 Log Message:
First batch of sciql-2-sql translations For each sciql query we use a closed by sql alternative to highlight the semantics. diffs (truncated from 1002 to 300 lines): diff --git a/sql/test/sciql2sql/Tests/01_create_01.sql b/sql/test/sciql2sql/Tests/01_create_01.sql new file mode 100644 --- /dev/null +++ b/sql/test/sciql2sql/Tests/01_create_01.sql @@ -0,0 +1,30 @@ +-- use the [size] shortcut for integer type dimensions +--!CREATE ARRAY ary(x TINYINT DIMENSION[4], y BIGINT DIMENSION[-5], v FLOAT DEFAULT 3.7); +--!SELECT * FROM ary; +--!DROP ARRAY ary; + +CREATE TABLE ary(x TINYINT CHECK(x >=0 && x < 4), y BIGINT CHECK( y>=-5 and y<0), v FLOAT DEFAULT 3.7); +INSERT INTO ary values +( 0, -4, 3.7 ), +( 0, -3, 3.7 ), +( 0, -2, 3.7 ), +( 0, -1, 3.7 ), +( 0, 0, 3.7 ), +( 1, -4, 3.7 ), +( 1, -3, 3.7 ), +( 1, -2, 3.7 ), +( 1, -1, 3.7 ), +( 1, 0, 3.7 ), +( 2, -4, 3.7 ), +( 2, -3, 3.7 ), +( 2, -2, 3.7 ), +( 2, -1, 3.7 ), +( 2, 0, 3.7 ), +( 3, -4, 3.7 ), +( 3, -3, 3.7 ), +( 3, -2, 3.7 ), +( 3, -1, 3.7 ), +( 3, 0, 3.7 ); + +SELECT * FROM ary; +DROP ARRAY ary; diff --git a/sql/test/sciql2sql/Tests/01_create_02.sql b/sql/test/sciql2sql/Tests/01_create_02.sql new file mode 100644 --- /dev/null +++ b/sql/test/sciql2sql/Tests/01_create_02.sql @@ -0,0 +1,12 @@ +-- create an unbounded array +--! CREATE ARRAY ary (x INTEGER DIMENSION, v FLOAT DEFAULT 3.7); +--! SELECT * FROM ary; +--! DROP ARRAY ary; + +-- Every dimension value is allowed. It has an undefined step size. +CREATE TABLE ary (x INTEGER, v FLOAT DEFAULT 3.7); + +-- what is the semantics of insertion of v=NULL +SELECT * FROM ary; +DROP TABLE ary; + diff --git a/sql/test/sciql2sql/Tests/01_create_03.sql b/sql/test/sciql2sql/Tests/01_create_03.sql new file mode 100644 --- /dev/null +++ b/sql/test/sciql2sql/Tests/01_create_03.sql @@ -0,0 +1,10 @@ +-- create an unbounded array +--! CREATE ARRAY ary (x INTEGER DIMENSION[*], v FLOAT DEFAULT 3.7); +--! SELECT * FROM ary; +--! DROP ARRAY ary; + +-- Every dimension value is allowed. Every step size is allowed. +CREATE TABLE ary (x INTEGER, v FLOAT DEFAULT 3.7); +SELECT * FROM ary; +DROP TABLE ary; + diff --git a/sql/test/sciql2sql/Tests/01_create_04.sql b/sql/test/sciql2sql/Tests/01_create_04.sql new file mode 100644 --- /dev/null +++ b/sql/test/sciql2sql/Tests/01_create_04.sql @@ -0,0 +1,21 @@ +-- create an unbounded array +--! CREATE ARRAY ary (x INTEGER DIMENSION[3:13], v FLOAT DEFAULT 3.7); +--! SELECT * FROM ary; +--! DROP ARRAY ary; + +CREATE TABLE ary (x INTEGER, v FLOAT DEFAULT 3.7 CHECK(x >= 3 and x < 13); +INSERT INTO ary values +( 3, 3.7 ), +( 4, 3.7 ), +( 5, 3.7 ), +( 6, 3.7 ), +( 7, 3.7 ), +( 8, 3.7 ), +( 9, 3.7 ), +( 10, 3.7 ), +( 11, 3.7 ), +( 12, 3.7 ); +SELECT * FROM ary; +DROP TABLE ary; + + diff --git a/sql/test/sciql2sql/Tests/01_create_05.sql b/sql/test/sciql2sql/Tests/01_create_05.sql new file mode 100644 --- /dev/null +++ b/sql/test/sciql2sql/Tests/01_create_05.sql @@ -0,0 +1,8 @@ +-- create an unbounded array +--! CREATE ARRAY ary (x INTEGER DIMENSION[*:13], v FLOAT DEFAULT 3.7); +--! SELECT * FROM ary; +--! DROP ARRAY ary; + +CREATE TABLE ary (x INTEGER CHECK(x <13), v FLOAT DEFAULT 3.7); +SELECT * FROM ary; +DROP TABLE ary; diff --git a/sql/test/sciql2sql/Tests/01_create_06.sql b/sql/test/sciql2sql/Tests/01_create_06.sql new file mode 100644 --- /dev/null +++ b/sql/test/sciql2sql/Tests/01_create_06.sql @@ -0,0 +1,8 @@ +-- create an unbounded array +--! CREATE ARRAY ary (x INTEGER DIMENSION[3:*], v FLOAT DEFAULT 3.7); +--! SELECT * FROM ary; +--! DROP ARRAY ary; + +CREATE TABLE ary (x INTEGER, v FLOAT DEFAULT 3.7 CHECK(x >= 3)); +SELECT * FROM ary; +DROP TABLE ary; diff --git a/sql/test/sciql2sql/Tests/01_create_07.sql b/sql/test/sciql2sql/Tests/01_create_07.sql new file mode 100644 --- /dev/null +++ b/sql/test/sciql2sql/Tests/01_create_07.sql @@ -0,0 +1,8 @@ +-- create an unbounded array +--! CREATE ARRAY ary (x INTEGER DIMENSION[*:3:13], v FLOAT DEFAULT 3.7); +--! SELECT * FROM ary; +--! DROP ARRAY ary; + +CREATE TABLE ary (x INTEGER CHECK (x < 13 and x % 3 == 0), v FLOAT DEFAULT 3.7); +SELECT * FROM ary; +DROP TABLE ary; diff --git a/sql/test/sciql2sql/Tests/01_create_08.sql b/sql/test/sciql2sql/Tests/01_create_08.sql new file mode 100644 --- /dev/null +++ b/sql/test/sciql2sql/Tests/01_create_08.sql @@ -0,0 +1,23 @@ +-- create an unbounded array +--! CREATE ARRAY ary (x INTEGER DIMENSION[3:*:13], v FLOAT DEFAULT 3.7); +--! SELECT * FROM ary; +--! DROP ARRAY ary; + +-- The actual filling is undefined, because the step size is not set. +-- The maximum would be obtained with step=1 +CREATE TABLE ary (x INTEGER CHECK(x >= 3 AND X < 13), v FLOAT DEFAULT 3.7); +INSERT INTO ary values +( 3, 3.7 ), +( 4, 3.7 ), +( 5, 3.7 ), +( 6, 3.7 ), +( 7, 3.7 ), +( 8, 3.7 ), +( 9, 3.7 ), +( 10, 3.7 ), +( 11, 3.7 ), +( 12, 3.7 ); +SELECT * FROM ary; +DROP TABLE ary; + + diff --git a/sql/test/sciql2sql/Tests/01_create_09.sql b/sql/test/sciql2sql/Tests/01_create_09.sql new file mode 100644 --- /dev/null +++ b/sql/test/sciql2sql/Tests/01_create_09.sql @@ -0,0 +1,13 @@ +-- create an unbounded array +CREATE ARRAY ary (x INTEGER DIMENSION[3:3:*], v FLOAT DEFAULT 3.7); +SELECT * FROM ary; +DROP ARRAY ary; + +-- The actual filling is undefined, because the step size is not set. +-- The maximum would be obtained with step=1 +CREATE TABLE ary (x INTEGER CHECK(x >= 3 AND x % 3=0), v FLOAT DEFAULT 3.7); +SELECT * FROM ary; +DROP TABLE ary; + + + diff --git a/sql/test/sciql2sql/Tests/01_create_10.sql b/sql/test/sciql2sql/Tests/01_create_10.sql new file mode 100644 --- /dev/null +++ b/sql/test/sciql2sql/Tests/01_create_10.sql @@ -0,0 +1,8 @@ +-- create an unbounded array +--! CREATE ARRAY ary (x INTEGER DIMENSION[*:*:13], v FLOAT DEFAULT 3.7); +--! SELECT * FROM ary; +--! DROP ARRAY ary; + +CREATE TABKE ary (x INTEGER CHECK( x < 13), v FLOAT DEFAULT 3.7); +SELECT * FROM ary; +DROP TABLE ary; diff --git a/sql/test/sciql2sql/Tests/01_create_11.sql b/sql/test/sciql2sql/Tests/01_create_11.sql new file mode 100644 --- /dev/null +++ b/sql/test/sciql2sql/Tests/01_create_11.sql @@ -0,0 +1,9 @@ +-- create an unbounded array +--! CREATE ARRAY ary (x INTEGER DIMENSION[*:3:*], v FLOAT DEFAULT 3.7); +--! SELECT * FROM ary; +--! DROP ARRAY ary; + +-- UNDEFINED because you don't know the starting point +CREATE TABLE ary (x INTEGER, v FLOAT DEFAULT 3.7); +SELECT * FROM ary; +DROP TABLE ary; diff --git a/sql/test/sciql2sql/Tests/01_create_12.sql b/sql/test/sciql2sql/Tests/01_create_12.sql new file mode 100644 --- /dev/null +++ b/sql/test/sciql2sql/Tests/01_create_12.sql @@ -0,0 +1,9 @@ +-- create an unbounded array +--! CREATE ARRAY ary (x INTEGER DIMENSION[3:*:*], v FLOAT DEFAULT 3.7); +--! SELECT * FROM ary; +--! DROP ARRAY ary; + +CREATE TABLE ary (x INTEGER CHECK(x >= 3), v FLOAT DEFAULT 3.7); +SELECT * FROM ary; +DROP TABLE ary; + diff --git a/sql/test/sciql2sql/Tests/01_create_13.sql b/sql/test/sciql2sql/Tests/01_create_13.sql new file mode 100644 --- /dev/null +++ b/sql/test/sciql2sql/Tests/01_create_13.sql @@ -0,0 +1,114 @@ +-- create an array with multiple dimensions and multiple cell values +--! CREATE ARRAY ary (dimx INT DIMENSION[17:3:27], dimy INT DIMENSION[31:2:40], dimz INT DIMENSION[0:5:19], v1 FLOAT DEFAULT 0.43, v2 FLOAT DEFAULT 3.3, v3 INT DEFAULT 999); +--! SELECT * FROM ary; +--! DROP ARRAY ary; + +CREATE TABLE ary (dimx INTEGER CHECK(x >= 17 AND x <27 AND x % 3 =2), + dimy INTEGER CHECK(x >= 31 AND x <40 AND x % 2 =1), + dimz INTEGER CHECK(x >= 0 AND x <19 AND x % 5 = 0), + v1 FLOAT DEFAULT 0.42, + v2 FLOAT DEFAULT 3.3, + v3 INTEGER DEFAULT 999); +INSERT INTO ary values +( 17, 31, 0, 0.43,3.3,999), +( 17, 31, 5, 0.43,3.3,999), +( 17, 31, 10, 0.43,3.3,999), +( 17, 31, 15, 0.43,3.3,999), + +( 17, 33, 0, 0.43,3.3,999), +( 17, 33, 5, 0.43,3.3,999), +( 17, 33, 10, 0.43,3.3,999), +( 17, 33, 15, 0.43,3.3,999), + +( 17, 35, 0, 0.43,3.3,999), +( 17, 35, 5, 0.43,3.3,999), +( 17, 35, 10, 0.43,3.3,999), +( 17, 35, 15, 0.43,3.3,999), + +( 17, 37, 0, 0.43,3.3,999), +( 17, 37, 5, 0.43,3.3,999), +( 17, 37, 10, 0.43,3.3,999), +( 17, 37, 15, 0.43,3.3,999), + +( 17, 39, 0, 0.43,3.3,999), +( 17, 39, 5, 0.43,3.3,999), +( 17, 39, 10, 0.43,3.3,999), +( 17, 39, 15, 0.43,3.3,999), + +( 20, 31, 0, 0.43,3.3,999), +( 20, 31, 5, 0.43,3.3,999), +( 20, 31, 10, 0.43,3.3,999), +( 20, 31, 15, 0.43,3.3,999), + +( 20, 33, 0, 0.43,3.3,999), +( 20, 33, 5, 0.43,3.3,999), +( 20, 33, 10, 0.43,3.3,999), +( 20, 33, 15, 0.43,3.3,999), + +( 20, 35, 0, 0.43,3.3,999), +( 20, 35, 5, 0.43,3.3,999), +( 20, 35, 10, 0.43,3.3,999), +( 20, 35, 15, 0.43,3.3,999), + +( 20, 37, 0, 0.43,3.3,999), +( 20, 37, 5, 0.43,3.3,999), +( 20, 37, 10, 0.43,3.3,999), +( 20, 37, 15, 0.43,3.3,999), + +( 20, 39, 0, 0.43,3.3,999), +( 20, 39, 5, 0.43,3.3,999), +( 20, 39, 10, 0.43,3.3,999), +( 20, 39, 15, 0.43,3.3,999), + +( 23, 31, 0, 0.43,3.3,999), +( 23, 31, 5, 0.43,3.3,999), +( 23, 31, 10, 0.43,3.3,999), +( 23, 31, 15, 0.43,3.3,999), + +( 23, 33, 0, 0.43,3.3,999), +( 23, 33, 5, 0.43,3.3,999), +( 23, 33, 10, 0.43,3.3,999), +( 23, 33, 15, 0.43,3.3,999), + +( 23, 35, 0, 0.43,3.3,999), +( 23, 35, 5, 0.43,3.3,999), +( 23, 35, 10, 0.43,3.3,999), +( 23, 35, 15, 0.43,3.3,999), _______________________________________________ checkin-list mailing list checkin-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/checkin-list
