Changeset: b8b6b8db8b6c for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=b8b6b8db8b6c Added Files: sql/test/sciql2sql/Tests/update_unbound_step_array.sql sql/test/sciql2sql/Tests/vector_fixed_tiles.sql Modified Files: sql/test/sciql2sql/Tests/All sql/test/sciql2sql/Tests/create_fixed_arrays.sql Branch: SciQL-2 Log Message:
Vector tiling and unbounded array update diffs (210 lines): diff --git a/sql/test/sciql2sql/Tests/All b/sql/test/sciql2sql/Tests/All --- a/sql/test/sciql2sql/Tests/All +++ b/sql/test/sciql2sql/Tests/All @@ -14,8 +14,11 @@ update_step_int_array update_unbound_step_array # selections with target is relation - +#show slice/dice over arrays +# # selection with array is arrays # tiling based on dimensional attributes +# vector manipulations +vector_fixed_tiles # tiling based on mix of dimensional attribues diff --git a/sql/test/sciql2sql/Tests/create_fixed_arrays.sql b/sql/test/sciql2sql/Tests/create_fixed_arrays.sql --- a/sql/test/sciql2sql/Tests/create_fixed_arrays.sql +++ b/sql/test/sciql2sql/Tests/create_fixed_arrays.sql @@ -11,7 +11,7 @@ INSERT INTO array1D values ( 2, 1, 0), ( 3, 1, 0); SELECT * FROM array1D; -DROP ARRAY array1D; +DROP TABLE array1D; -- a 2 Dimensional fixed array CREATE ARRAY array2D(x TINYINT DIMENSION[4], y BIGINT DIMENSION[4], v INTEGER DEFAULT 2); @@ -39,7 +39,7 @@ INSERT INTO array2D values ( 3, 3, 2 ); SELECT * FROM array2D; -DROP ARRAY array2D; +DROP TABLE array2D; -- a 3D data cube CREATE ARRAY array3D(x INT DIMENSION[3], y INT DIMENSION[3], z INT DIMENSION [3], v INT DEFAULT 3); @@ -87,7 +87,7 @@ INSERT INTO array3D values ( 3, 2, 0, 3); SELECT * FROM array3D; -DROP ARRAY array3D; +DROP TABLE array3D; -- Semantic arrors diff --git a/sql/test/sciql2sql/Tests/update_unbound_step_array.sql b/sql/test/sciql2sql/Tests/update_unbound_step_array.sql new file mode 100644 --- /dev/null +++ b/sql/test/sciql2sql/Tests/update_unbound_step_array.sql @@ -0,0 +1,60 @@ +-- unbounded step size manipulations +CREATE ARRAY array1Dint(x INTEGER DIMENSION[0:*:4], v INTEGER DEFAULT 1); +SELECT * FROM array1Dint; +DROP ARRAY array1Dint; + +-- relational equivalent use a general dimension table to administer the valid bounds +-- alternative generic dimension representation, works for any type, requires view updates +CREATE TABLE array1Dintdim(idx INTEGER PRIMARY KEY, x INTEGER ); +CREATE TABLE array1Dintval(idx INTEGER REFERENCES array1Dintdim(idx), v INTEGER DEFAULT 1); +CREATE VIEW array1Dint +AS SELECT x,v FROM array1Dintdim, array1Dintval WHERE array1Dintdim.idx = array1Dintval.idx; + +INSERT INTO array1Dintdim VALUES (0,0),(1,4); +INSERT INTO array1Dintval VALUES (0,1),(1,1); +SELECT * FROM array1Dintdim; +DROP VIEW array1Dint; +DROP TABLE array1Dintval; +DROP TABLE array1Dintdim; + +-- INSERT, UPDATE and DELETE cells +CREATE ARRAY array1Dint(x INTEGER DIMENSION[0:*:4], v INTEGER DEFAULT 1); +INSERT INTO array1Dint VALUES(2,2); +SELECT * FROM array1Dint; +UPDATE array1Dint SET v = 44 WHERE x < 2; +SELECT * FROM array1Dint; +DELETE FROM array1Dint WHERE x = 2; +DELETE FROM array1Dint WHERE x = 3; +DROP ARRAY array1Dint; + +-- relational equivalent use a general dimension table to administer the valid bounds +CREATE TABLE array1Dintdim(idx INTEGER PRIMARY KEY, x INTEGER ); +CREATE TABLE array1Dintval(idx INTEGER REFERENCES array1Dintdim(idx), v INTEGER DEFAULT 1); +CREATE VIEW array1Dint +AS SELECT x,v FROM array1Dintdim, array1Dintval WHERE array1Dintdim.idx = array1Dintval.idx; + +INSERT INTO array1Dintdim VALUES (0,0),(1,4); +INSERT INTO array1Dintval VALUES (0,1),(1,1); + +-- insert the missing element +INSERT INTO array1Dintdim VALUES(2,2); +INSERT INTO array1Dintval VALUES(2,2); +SELECT * FROM array1Dint ORDER BY x ; + +-- update all valid cells +UPDATE array1Dintval SET v = 44 WHERE idx in (SELECT idx FROM array1Dintdim WHERE x <2); +SELECT * FROM array1Dint ORDER BY x ; + +-- delete cells +DELETE FROM array1Dintval WHERE idx in (SELECT idx FROM array1Dintdim WHERE x = 2 AND x < 4); +DELETE FROM array1Dintdim WHERE x = 2 AND x < 4; +DELETE FROM array1Dintval WHERE idx in (SELECT idx FROM array1Dintdim WHERE x = 3 AND x < 4); +DELETE FROM array1Dintdim WHERE x = 3 AND x < 4; +SELECT * FROM array1Dint ORDER BY x ; + +DROP VIEW array1Dint; +DROP TABLE array1Dintval; +DROP TABLE array1Dintdim; +-- Relational mapping, use the same scheme as above, but with only one bound set + +-- semantic errors preferrably catched diff --git a/sql/test/sciql2sql/Tests/vector_fixed_tiles.sql b/sql/test/sciql2sql/Tests/vector_fixed_tiles.sql new file mode 100644 --- /dev/null +++ b/sql/test/sciql2sql/Tests/vector_fixed_tiles.sql @@ -0,0 +1,94 @@ +-- Test tiling over a 1D fixed array +CREATE ARRAY array1D(x INTEGER DIMENSION[7], v INTEGER DEFAULT 1, w INTEGER DEFAULT 0); + +-- relational equivalent +CREATE TABLE vector(x INTEGER CHECK(x >=0 and x < 7), v INTEGER DEFAULT 1, w INTEGER DEFAULT 0); +INSERT INTO vector values +( 0, 1, 1), +( 1, 1, 2), +( 2, 2, 1), +( 3, 2, 5), +( 4, 3, 7), +( 5, 3, 7), +( 6, 4, 1); + +-- simple aggregation over a dimension +SELECT x, count(*) +FROM array1D +GROUP BY array1D[x : 1 : x+1]; + +-- this can be abrevated to +SELECT x, count(*) +FROM array1D +GROUP BY array1D[x]; + +-- relational equivalent of both +-- every array can be looked upon as a table +SELECT x, count(*) +FROM vector +GROUP BY x; + +-- a 2-size window summation +SELECT x, sum(v) +FROM array1D +GROUP BY array1D[x : 1 : x+2]; + +-- relational equivalent could use an arrayshift +-- SELECT A.x, A.v+B.b FROM shift(vector,x,0) as A, shift(vector,x,1) as B +-- WHERE A.idx = B.idx +SELECT A.x, A.v+B.v +FROM vector as A, vector as B +WHERE A.x+1 = B.x; + +-- a 3-size window summarization +SELECT x, sum(v) +FROM array1D +GROUP BY array1D[x : 1 : x+3]; + +-- relational equivalent could use an arrayshift +SELECT A.x, A.v+B.v+ C.v +FROM vector as A, vector as B, vector as C +WHERE A.x+1 = B.x and A.x+2 = C.x; + +-- using left specified 2-windows +SELECT x, sum(v) +FROM array1D +GROUP BY array1D[x-1 : 1 : x+1]; + +-- relational equivalent merely shuffles the predicate +SELECT A.x, A.v+B.v +FROM vector as A, vector as B +WHERE A.x-1 = B.x; + +-- more elaborate slices +SELECT x, sum(v) +FROM array1D +GROUP BY array1D[x : 1 : x+4]; + +-- casting a more generic predicate into relational +-- use a relative offset map to indicate group elements +-- RUNTIME ERROR, produces NULL sums !! + +CREATE FUNCTION aggr(first INTEGER,step INTEGER, fin INTEGER) +RETURNS TABLE (x INTEGER, s INTEGER) +BEGIN + CREATE LOCAL TEMPORARY TABLE pivotset(x INTEGER); + INSERT INTO pivotset VALUES(0),(1),(2),(3); + DECLARE pivot INTEGER; + SET pivot = 0; + CREATE LOCAL TEMPORARY TABLE answer(x INTEGER, s INTEGER); + + WHILE (pivot < 4) DO + INSERT INTO answer(x,s) (SELECT pivot, sum(vector.v) FROM vector, pivotset WHERE vector.x = pivotset.x); + UPDATE pivotset SET x=x+1; + SET pivot = pivot+1; + END WHILE; + RETURN answer; +END; + +SELECT * FROM aggr(0,1,4); + +DROP FUNCTION aggr; +DROP ARRAY array1D; +DROP TABLE vector; + _______________________________________________ checkin-list mailing list checkin-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/checkin-list
