Re: [Monetdb-developers] How to register new "bulk" function with SQL?

22 Oct 2009

Thanks, Martin!
the below snipped is now reduced to a single statement:
|     _25:bat[:oid,:wkb]  := batgeom.point(_16,_24);
Stefan
On Thu, Oct 22, 2009 at 06:36:00PM +0200, Martin Kersten wrote:
...
The remap optimizer gives an overview and solution to this problem.
It has currently some hardwired assumptions, e.g. it would search
for bat bulk operations only.
If you would rename geom.point into batgeom.point for the bulk
version, you should  have reached your goal.
Stefan Manegold wrote:
...
Hoi Niels (and other SQL experts),
I implemented the "bulk" version of ogc constructor function
point(x:dbl,y:dbl) :wkb
i.e.
point(x:bat[:oid,:dbl],y:bat[:oid,:dbl]) :bat[:oid,:wkb]
in the geom module (see below).
How do I get SQL to use this function, e.g., in a scenario like this:
CREATE TABLE coords (x double, y double);
CREATE TABLE points (p point);
INSERT INTO points SELECT POINT(x,y) FROM coords;
For now, EXPLAIN still shows the explicit MAL interation calling the
original scalar function:
[...]
|     _67 := bat.new(nil:oid,nil:wkb);                                                                                                                                                                                                               |
| barrier (_71,_72,_73) := bat.newIterator(_16);                                                                                                                                                                                                     |
|     _75 := algebra.find(_24,_72);                                                                                                                                                                                                                  |
|     _77 := geom.point(_73,_75);                                                                                                                                                                                                                    |
|     bat.insert(_67,_72,_77);                                                                                                                                                                                                                       |
|     redo (_71,_72,_73) := bat.hasMoreElements(_16);                                                                                                                                                                                                |
| exit (_71,_72,_73);                                                                                                                                                                                                                                |
|     _25:bat[:oid,:wkb]  := _67;                                                                                                                                                                                                                    |
[...]
Thanks in advance!
Stefan
ps: Eventually, we should consider implemeting bulk versions of all scalar
    functions in geom in the same (or a similar) way --- assuming that this
    might improve the performance ...
On Thu, Oct 22, 2009 at 04:15:30PM +0000, Stefan Manegold wrote:
...
Update of /cvsroot/monetdb/geom/src/monetdb5
In directory 23jxhf1.ch3.sourceforge.com:/tmp/cvs-serv25812/geom/src/monetdb5
Modified Files:
   geom.mx 
Log Message:
implemented bulk version of ogc constructor function
point(x:dbl,y:dbl) :wkb
i.e.
point(x:bat[:oid,:dbl],y:bat[:oid,:dbl]) :bat[:oid,:wkb]
Open question:
How to make SQL use this bulk function instead of an explicit
MAL iterator over the scalar function, e.g., in queries like
INSERT INTO points SELECT POINT(x, y) FROM coords;
Index: geom.mx
RCS file: /cvsroot/monetdb/geom/src/monetdb5/geom.mx,v
retrieving revision 1.29
retrieving revision 1.30
diff -u -d -r1.29 -r1.30

--- geom.mx	26 Jun 2009 07:37:52 -0000	1.29
+++ geom.mx	22 Oct 2009 16:15:28 -0000	1.30
@@ -170,6 +170,10 @@
 address wkbcreatepoint
 comment "Construct a point from two geometries";
+command point(x:bat[:oid,:dbl],y:bat[:oid,:dbl]) :bat[:oid,:wkb]
+address wkbcreatepoint_bat
+comment "Construct a point-BAT from two geometry-BATs";



@+ ogc information methods
 @mal
 command X(g:wkb) :dbl
@@ -1010,6 +1014,76 @@
 }
geom_export str
+wkbcreatepoint_bat(int *out, int *x,int *y);



+str
+wkbcreatepoint_bat(int *out, int *ix,int *iy)
+{

BAT *bo = NULL, *bx = NULL, *by = NULL;
dbl *x = NULL, *y = NULL;
BUN i, o;
wkb *p = NULL;

if ((bx = BATdescriptor(*ix)) == NULL) {
throw(MAL, "geom.point", RUNTIME_OBJECT_MISSING);


}
if ((by = BATdescriptor(*iy)) == NULL) {
BBPreleaseref(bx->batCacheid);


throw(MAL, "geom.point", RUNTIME_OBJECT_MISSING);


}
if (!BAThdense(bx) || !BAThdense(by) || bx->hseqbase != by->hseqbase || BATcount(bx) != BATcount(by)) {
BBPreleaseref(bx->batCacheid);


BBPreleaseref(by->batCacheid);


throw(MAL, "geom.point", "both arguments must have dense and aligned heads");


}

if ((bo = BATnew(TYPE_void, ATOMindex("wkb"), BATcount(bx))) == NULL) {
BBPreleaseref(bx->batCacheid);


BBPreleaseref(by->batCacheid);


throw(MAL, "geom.point", MAL_MALLOC_FAIL);


}
BATseqbase(bo, bx->hseqbase);
bo->hdense = TRUE;
bo->hsorted = TRUE;
bo->H->nonil = TRUE;
BATkey(bo, TRUE);

x = (dbl*) Tloc(bx, BUNfirst(bx));
y = (dbl*) Tloc(by, BUNfirst(bx));
for (i = 0, o = BUNlast(bo); i < BATcount(bx); i++, o++) {
str err = NULL;


if ((err = wkbcreatepoint(&p, &x[i], &y[i])) != MAL_SUCCEED) {


	BBPreleaseref(bx->batCacheid);


	BBPreleaseref(by->batCacheid);


	BBPreleaseref(bo->batCacheid);


	throw(MAL, "geom.point", err);


}


tfastins_nocheck(bo, o, p, Tsize(bo));


GDKfree(p);


p = NULL;


}

BATsetcount(bo, BATcount(bx));
bo->batDirty = TRUE;
bo->tdense = FALSE;
bo->tsorted = FALSE;
bo->T->nonil = FALSE;
BATkey(BATmirror(bo), FALSE);

BBPreleaseref(bx->batCacheid);
BBPreleaseref(by->batCacheid);
BBPkeepref(*out = bo->batCacheid);
return MAL_SUCCEED;

bunins_failed:
if (p) GDKfree(p);
BBPreleaseref(bx->batCacheid);
BBPreleaseref(by->batCacheid);
BBPreleaseref(bo->batCacheid);
throw(MAL, "geom.point", "bunins failed");

+}



+geom_export str
 mbroverlaps( bit *out, mbr *b1, mbr *b2);
str

Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay 
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Monetdb-sql-checkins mailing list
Monetdb-sql-checkins@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-sql-checkins

Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay 
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Monetdb-developers mailing list
Monetdb-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-developers
-- 
| Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl |
| CWI,  P.O.Box 94079 | http://www.cwi.nl/~manegold/  |
| 1090 GB Amsterdam   | Tel.: +31 (20) 592-4212       |
| The Netherlands     | Fax : +31 (20) 592-4312       |


    