Changeset: c3bce7e7ea37 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=c3bce7e7ea37
Modified Files:
sql/src/test/sciql/Tests/Msciql00.mal
sql/src/test/sciql/Tests/Msciql01.mal
sql/src/test/sciql/Tests/Msciql02.mal
sql/src/test/sciql/Tests/Msciql03.mal
sql/src/test/sciql/Tests/Msciql04.mal
sql/src/test/sciql/Tests/Msciql05.mal
sql/src/test/sciql/Tests/Msciql06.mal
sql/src/test/sciql/Tests/Msciql07.mal
sql/src/test/sciql/Tests/Msciql08.mal
sql/src/test/sciql/Tests/Msciql09.mal
sql/src/test/sciql/Tests/Msciql10.mal
sql/src/test/sciql/Tests/Msciql12.mal
sql/src/test/sciql/Tests/Msciql13.mal
sql/src/test/sciql/Tests/Msciql14.mal
sql/src/test/sciql/Tests/Msciql15.mal
sql/src/test/sciql/Tests/Msciql16.mal
sql/src/test/sciql/Tests/Msciql17.mal
sql/src/test/sciql/Tests/Msciql18.mal
sql/src/test/sciql/Tests/Msciql19.mal
sql/src/test/sciql/Tests/Msciql20.mal
sql/src/test/sciql/Tests/Msciql21.mal
sql/src/test/sciql/Tests/Msciql22.mal
sql/src/test/sciql/Tests/Msciql23.mal
sql/src/test/sciql/Tests/Msciql24.mal
sql/src/test/sciql/Tests/Msciql25.mal
sql/src/test/sciql/Tests/Msciql26.mal
sql/src/test/sciql/Tests/Msciql27.mal
sql/src/test/sciql/Tests/Msciql28.mal
sql/src/test/sciql/Tests/Msciql29.mal
sql/src/test/sciql/Tests/Msciql30.mal
sql/src/test/sciql/Tests/Msciql31.mal
Branch: sciql
Log Message:
MAL versions of sciql queries
diffs (truncated from 325 to 300 lines):
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql00.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql00.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,7 @@
+--CREATE ARRAY A1( x integer DIMENSION[4], val float DEFAULT 0.0);
+CREATE table A1( x integer, val float DEFAULT 0.0);
+--CREATE ARRAY A2 ( x integer DIMENSION[0:4:1], val float DEFAULT 0.0);
+CREATE TABLE A2 ( x integer, val float DEFAULT 0.0);
+CREATE SEQUENCE range AS integer START WITH 0 INCREMENT BY 1 MAXVALUE 3;
+--CREATE ARRAY A3 ( x integer DIMENSION range, val float DEFAULT 0.0);
+CREATE ARRAY A3 ( x integer , val float DEFAULT 0.0);
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql01.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql01.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,8 @@
+--CREATE ARRAY matrix ( x integer DIMENSION[4], y integer DIMENSION[4], val
float DEFAULT 0.0 );
+CREATE TABLE matrix ( x integer, y integer, val float DEFAULT 0.0 );
+--CREATE ARRAY grid( x integer DIMENSION[4] CHECK(mod(x,2) = 0), y integer
DIMENSION[4], val float DEFAULT 0.0);
+CREATE TABLE grid( x integer, y integer , val float DEFAULT 0.0);
+--CREATE ARRAY diagonal( x integer DIMENSION[4], y integer DIMENSION[4]
CHECK(x = y), val float );
+CREATE TABLE diagonal( x integer, y integer, val float );
+--CREATE ARRAY sparse( x integer DIMENSION[4], y integer DIMENSION[4], val
float DEFAULT 0.0 CHECK(val>0));
+CREATE TABLE sparse( x integer, y integer, val float DEFAULT 0.0
CHECK(val>0));
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql02.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql02.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,6 @@
+--CREATE TABLE experiment(
+-- run date DIMENSION[ TIMESTAMP '2010-01-01':*: INTERVAL'1' day],
+-- payload float ARRAY[4][4] DEFAULT 0.0 );
+CREATE TABLE experiment(
+ run date,
+ payload float DEFAULT 0.0 );
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql03.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql03.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,53 @@
+# CREATE ARRAY stripes( x int dimension[4], y int dimension[4], val float
DEFAULT 0.0);
+#create table stripes (x int, y int, val float);
+#insert into stripes values(0,0,0.0), (0,1,1.0), (1,0,2.0), (1,1,3.0);
+
+# base arrays are initialized once in most situations considered! (appends are
allowed)
+
+#UPDATE stripes SET val = x + y;
+# What is the storage model ? explicit dimensions? or implicit?
+
+# explicit model, after definition we have to instanciate the array
+ # create the x- and y- bats, initialize with their value
+ #pattern array.newIndex(min:any_0,max:any_0,step:any_0):bat[:oid,:any_0]
+ #pattern
array.newIndex(xmin:any_0,xmax:any_0,xstep:any_0,ymin:any_1,ymax:any_1,ystep:any_1)(x:bat[:oid,:any_0],y:bat[:oid,:any_1])
+ #idem z, t
+ #pattern array.setDefault(idx:bat[:oid,:any_0],
def:any_1):bat[:oid,:any_1];
+
+ # for a fixed array the post dimension operations are to be called
explicitly
+ #(x,y):= array.newIndex(0,4,1,0,4,1);
+ #val := array.setDefault(x,0.0);
+ # create the val bat
+
+function user.s1_1{autoCommit=true}():void;
+ _2 := sql.mvc();
+#if we have a dimension with fixed bounds then there can not be updates
+ (_3:bat[:oid,:int],_23:bat[:oid,:int]) :=
array.dimension(_2,"sys","stripes","x",0,4,1,"y",0,4,1);
+ _4:bat[:int,:oid] := bat.reverse(_3);
+ _5:bat[:oid,:oid] := bat.mirror(_4);
+ _32:bat[:oid,:int] := batcalc.+(_3,_23);
+ _33 := batcalc.dbl(_32);
+ _34 := algebra.join(_5,_33);
+ _37:= aggr.count(_34);
+ sql.update(_2,"sys","stripes","val",_34);
+ sql.affectedRows(_37,"");
+end s1_1;
+
+#-- for stripes2 it becomes
+function user.s1_1{autoCommit=true}():void;
+ _2 := sql.mvc();
+#if we have a dimension with free bounds then we need an explicit
representation of x
+ (_3:bat[:oid,:int],_23:bat[:oid,:int]) :=
array.dimension(_2,"sys","stripes","x",null,null,null,"y",null,null,null);
+ _4:bat[:int,:oid] := bat.reverse(_3);
+ _5:bat[:oid,:oid] := bat.mirror(_4);
+ _32:bat[:oid,:int] := batcalc.+(_3,_23);
+ _33 := batcalc.dbl(_32);
+ _34 := algebra.join(_5,_33);
+ _37:= aggr.count(_34);
+ sql.update(_2,"sys","stripes","val",_34);
+ sql.affectedRows(_37,"");
+end s1_1;
+
+#UPDATE grid
+#SET val = CASE WHEN x > y THEN x + y WHEN X<y THEN x - y ELSE 0 END;
+#UPDATE diagonal SET val = x +y; UPDATE sparse SET val = mod(rand(),16);
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql04.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql04.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,1 @@
+INSERT INTO grid VALUES(1,1,25);
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql05.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql05.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,1 @@
+UPDATE experiment SET payload= NULL WHERE payload[x][y].val < 0;
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql06.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql06.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,26 @@
+#SELECT ARRAY (1,2,3,4);
+#SELECT ARRAY((1,2),(3,4));
+#SELECT x, y, val FROM matrix WHERE val >2;
+
+#SELECT x, y, val FROM stripes WHERE val >2;
+function user.s0_0{autoCommit=true}():void;
+ _2 := sql.mvc();
+ _9:bat[:oid,:dbl] := sql.bind(_2,"sys","stripes","val",0);
+ _10 := algebra.thetauselect(_9,2:dbl,">");
+ _12 := algebra.markT(_10,0@0:oid);
+ _13 := bat.reverse(_12);
+ _5:bat[:oid,:int] := array.dimension(_2,"sys","stripes","x",0,0,4,1);
+ _14 := algebra.leftjoin(_13,_5);
+ _18 := algebra.leftjoin(_13,_9);
+ _16:bat[:oid,:int] := array.dimension(_2,"sys","stripes","y",0,0,4,1);
+ _17 := algebra.leftjoin(_13,_16);
+ _19 := sql.resultSet(3,1,_14);
+ sql.rsColumn(_19,"sys.stripes","x","int",32,0,_14);
+ sql.rsColumn(_19,"sys.stripes","y","int",32,0,_17);
+ sql.rsColumn(_19,"sys.stripes","val":str,"double",53,0,_18);
+ _33 := io.stdout();
+ sql.exportResult(_33,_19);
+end s0_0;
+
+#SELECT [x], [y], val FROM matrix WHERE val >2;
+#SELECT [T.k], [y], val FROM matrix JOIN T ON matrix.x = T.i;
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql07.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql07.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,5 @@
+-- postgresql arrays
+
+create table pga(
+ v1 integer array[4],
+ v3 integer array[2][2]);
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql08.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql08.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,2 @@
+SELECT matrix[1][1].v;
+SELECT sparse[0:2][0:2].v;
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql09.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql09.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,5 @@
+SET vector[0:2].v = (expr1,expr2);
+SET vector[x].val = CASE WHEN vector[x].val < 0 THEN 0
+ WHEN vector[x].val >10 THEN 10 * x END ;
+
+
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql10.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql10.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,6 @@
+CREATE ARRAY vmatrix (
+ x integer DIMENSION[-1:5],
+ y integer DIMENSION[-1:5],
+ w float DEFAULT 0);
+INSERT INTO vmatrix SELECT [i], [j], val FROM matrix;
+INSERT INTO vmatrix SELECT [j], [i], val FROM matrix;
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql12.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql12.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,3 @@
+SELECT [x], [y], avg(v) FROM matrix GROUP BY matrix[x:x+2][y:y+2];
+SELECT [x], [y], avg(v) FROM matrix GROUP BY DISTINCT matrix[x:x+2][y:y+2];
+SELECT [x], [y], avg(v) FROM vmatrix GROUP BY matrix[x-1:x+1][y-1:y+1];
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql13.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql13.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,1 @@
+SELECT [x],sum(val) FROM matrix GROUP BY DISTINCT matrix[x][y: *];
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql14.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql14.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,3 @@
+SELECT x, y, (matrix[x-1][y].v + matrix[x+1][y].v + matrix[x][y-1].v +
matrix[x][y+1].v + matrix[x][y].v)/5
+FROM matrix[0:5][0:5]
+GROUP BY matrix[x][y], matrix[x-1][y], matrix[x+1][y], matrix[x][y-1],
matrix[x][y+1];
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql15.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql15.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,2 @@
+-- SELECT distance(A, ?V), A.* FROM matrix AS A GROUP BY matrix[x][*] AS A;
+SELECT distance(A, A), A.* FROM matrix AS A GROUP BY matrix[x][*];
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql16.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql16.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,1 @@
+ALTER ARRAY img ALTER x DIMENSION[-5:*];
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql17.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql17.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,5 @@
+ALTER ARRAY matrix ADD r float DEFAULT sqrt( power(x,2) + power(y,2));
+ALTER ARRAY matrix ADD theta float
+ DEFAULT (CASE WHEN x=0 AND y=0 THEN 0
+ WHEN x> 0 THEN arcsin( CAST( x AS float) / r)
+ WHEN x< 0 THEN -arcsin( CAST( x AS float) / r) + PI() END);
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql18.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql18.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,4 @@
+CREATE ARRAY tmp(x integer DIMENSION, y integer DIMENSION, val float);
+INSERT INTO tmp SELECT x, y, avg(v)
+FROM matrix
+GROUP BY DISTINCT matrix[x:x+2][y:y+2];
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql19.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql19.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,18 @@
+CREATE SEQUENCE rng AS integer START WITH 0 INCREMENT BY 1 MAXVALUE 7;
+
+CREATE ARRAY white (
+ i integer DIMENSION[rng],
+ j integer DIMENSION[rng],
+ color char(5) DEFAULT 'white'
+);
+CREATE ARRAY black (LIKE white);
+CREATE ARRAY chessboard(
+ i integer DIMENSION[rng],
+ j integer DIMENSION[rng],
+ white char(5));
+INSERT INTO chessboard
+SELECT [i], [j], color FROM white
+ WHERE ( i * 8 + j) / 2 = 0
+UNION
+SELECT [i], [j], color FROM black
+ WHERE ( i * 8 + j) / 2 ;
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql20.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql20.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,7 @@
+CREATE SEQUENCE seq AS integer START WITH 0 INCREMENT BY 1 MAXVALUE 10;
+CREATE FUNCTION random(n integer)
+RETURNS ARRAY( i integer DIMENSION[seq], val float)
+ RETURN SELECT seq, rand() FROM SEQUENCES seq;
+CREATE FUNCTION transpose ( a ARRAY( i integer DIMENSION, j integer DIMENSION,
v float))
+RETURNS ARRAY( i integer DIMENSION, j integer DIMENSION, v float)
+BEGIN RETURN SELECT [i],[j], a[j][i].v FROM a; END;
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql21.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql21.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,3 @@
+CREATE FUNCTION markov( a ARRAY( x int DIMENSION, y int DIMENSION, f float),
steps integer)
+RETURNS ARRAY( x int DIMENSION, y int DIMENSION, f float)
+EXTERNAL NAME ’markov.loop’;
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql22.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql22.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,2 @@
+CREATE ARRAY landsat ( channel integer DIMENSION[7], x integer
DIMENSION[1024], y integer DIMENSION[1024], v integer);
+UPDATE landsat SET v = noise(v,delta) WHERE channel = 6 and mod(x,6) = 1;
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql23.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql23.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,11 @@
+CREATE FUNCTION intens2radiance(b int, lmin real, lmax real) RETURNS real
RETURN (lmax-lmin) * b /255.0 + lmin;
+
+CREATE ARRAY ndvi ( x int DIMENSION[1024], y int DIMENSION[1024], b1 real, b2
real, v real);
+UPDATE ndvi
+ SET b1 = (SELECT intens2radiance(v, lmin, lmax)
+ FROM landsat WHERE channel = 1
+ AND landsat.x = ndvi.x AND landsat.y = ndvi.y),
+ b2 = (SELECT intens2radiance(v, lmin, lmax)
+ FROM landsat WHERE channel =2
+ AND landsat.x = ndvi.x AND landsat.y = ndvi.y),
+ v=(b2-b1)/(b2+b1);
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql24.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql24.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,1 @@
+SELECT [x], [y], avg(v) FROM vmatrix GROUP BY vmatrix[x-1:x+1][y-1:y+1] HAVING
avg(v) BETWEEN 10 AND 100;
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql25.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql25.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,2 @@
+UPDATE img SET v = (SELECT d.v + e.v * power(-1,x) FROM d, e
+WHERE img.y = d.y and img.y = e.y AND d.x = img.x/2 AND e.x = img.x/2);
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql26.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql26.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,3 @@
+UPDATE img SET v = (SELECT d.v + e.v * power(-1,x)
+FROM d, e
+WHERE img.y = d.y and img.y = e.y AND d.x = img.x/2 AND e.x = img.x/2);
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql27.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql27.mal Fri Jan 14 14:46:02 2011 +0100
@@ -0,0 +1,3 @@
+CREATE ARRAY m ( x int DIMENSION[1024], v int );
+UPDATE m
+ SET v = (SELECT sum(a.v * b.v) FROM a,b WHERE m.x = a.x and a.y = b.k);
diff -r 163a4aedba1f -r c3bce7e7ea37 sql/src/test/sciql/Tests/Msciql28.mal
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/sciql/Tests/Msciql28.mal Fri Jan 14 14:46:02 2011 +0100
_______________________________________________
Checkin-list mailing list
[email protected]
http://mail.monetdb.org/mailman/listinfo/checkin-list
