November 2017 - developers-list

dbplot for R
by Hannes Mühleisen 30 Nov '17

30 Nov '17

Hi all, There is a new R package, dbplot [1], which pushes aggregations needed vor viz into the database. This partially works fine with MonetDB[Lite], but most examples [2] fail due to the lack of window aggregate support (min etc.). So perhaps this is a good reason to implement those. Hint-hint, Solutions. Hannes [1] https://github.com/edgararuiz/dbplot <https://github.com/edgararuiz/dbplot> [2] https://gist.github.com/hannesmuehleisen/d18a0f2f494db69e157abd753d6bf31d <https://gist.github.com/hannesmuehleisen/d18a0f2f494db69e157abd753d6bf31d>

1 0

Re: read BLOB column on tail heap and loop over rows
by Anton Kravchenko 13 Nov '17

13 Nov '17

Ok, here is a workaround [though I suspect that performance is going to degrade (with respect to CHAR column for example[1]) because BUNtail is being called for each row...] sql_column *col = mvc_bind_column(m, tbl, colname[icol]); BAT *b = table_funcs.full_column(m->session->tr, col); //read the entire column into continuous memory on the tail heap of the BAT b BATiter *iterators = NULL; blob *blob_record; int ib; iterators = GDKzalloc(sizeof(BATiter)); iterators[0] = bat_iterator(b); // blob column iterator for (irow=0; irow < nrow; irow++) { blob_record = (blob*) BUNtail(iterators[0], irow); for (ib=0; ib < blob_record->nitems; ib++) { printf("blob data = %c, %02x\n", blob_record->data[ib], blob_record->data[ib]); } } p.s. [1] //For CHAR column it would look like this: sql_column *col = mvc_bind_column(m, tbl, colname[icol]); BAT *b = table_funcs.full_column(m->session->tr, col); //read the entire column into continuous memory on the tail heap of the BAT b char *tvbase, *cptr; unsigned char *readucrows; tvbase = (char *) b->T.vheap->base + GDK_VAROFFSET; readucrows = (unsigned char *) b->T.heap.base; for (irow=0; irow < nrow; irow++) { cptr = tvbase + *readucrows++; } Anton On Fri, Nov 10, 2017 at 4:13 PM, Anton Kravchenko < kravchenko.anton86(a)gmail.com> wrote: > I wonder how one would read BLOB column and loop over its rows? > > BAT *b = table_funcs.full_column(m->session->tr, blob_col_name); > //blob *readblobrows = (blob*) b->T.heap.base; > //unsigned char *readblobrows = (unsigned char*) b->T.heap.base; > > p.s. > For INT column it would be go like this: > int * readintrows = (int *) b->T.heap.base; > for (irow=0; irow < nrow; irow++) { > intvalue = *readintrows++; > } > > Thanks, > Anton >

1 0

Re: MonetDB: Jul2017 - Properly respect DEFAULT values in the Python...
by Stefan Manegold 13 Nov '17

13 Nov '17

Mark, thanks for taking care of this!! Unfortunately, it does not seem to work correctly, yet. For strings, as in your pyloader01.sql test (see below), the used/inserted default value appears to include the single-quotes "'" form the SQL syntax --- I assume you approved those by accident --- and for integers "strange values" are inserted; see test sql/test/BugTracker-2017/Tests/python_loader_clobbers_default_with_null.Bug-6464.sql Best, Stefan ----- On Nov 13, 2017, at 5:47 PM, Mark Raasveldt commits+m.raasveldt=cwi.nl(a)monetdb.org wrote: > Changeset: 4303f7489624 for MonetDB > URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=4303f7489624 > Modified Files: > sql/backends/monet5/Tests/pyloader01.sql > sql/backends/monet5/Tests/pyloader01.stable.out > sql/backends/monet5/UDF/pyapi/emit.c > sql/backends/monet5/UDF/pyapi/pyloader.c > sql/include/sql_catalog.h > sql/server/rel_updates.c > Branch: Jul2017 > Log Message: > > Properly respect DEFAULT values in the Python loader. > > > diffs (145 lines): > > diff --git a/sql/backends/monet5/Tests/pyloader01.sql > b/sql/backends/monet5/Tests/pyloader01.sql > --- a/sql/backends/monet5/Tests/pyloader01.sql > +++ b/sql/backends/monet5/Tests/pyloader01.sql > @@ -1,7 +1,7 @@ > > START TRANSACTION; > > -CREATE TABLE mytable(a DOUBLE, d int, s STRING); > +CREATE TABLE mytable(a DOUBLE, d int, s STRING DEFAULT 'hello'); > > CREATE LOADER myfunc() LANGUAGE PYTHON { > _emit.emit({'a':42,'d':1}) > @@ -23,10 +23,10 @@ SELECT name,func,mod,language,type,side_ > > > -- there is a reason for this, functions with 0, 1, 2 and 3+ arguments are > handled differently. > +COPY LOADER INTO mytable FROM myfunc(); > COPY LOADER INTO mytable FROM myfunc3(46, 'asdf', 3.2); > COPY LOADER INTO mytable FROM myfunc2(45, 'asdf'); > COPY LOADER INTO mytable FROM myfunc1(44); > -COPY LOADER INTO mytable FROM myfunc(); > > SELECT * FROM mytable; > > diff --git a/sql/backends/monet5/Tests/pyloader01.stable.out > b/sql/backends/monet5/Tests/pyloader01.stable.out > --- a/sql/backends/monet5/Tests/pyloader01.stable.out > +++ b/sql/backends/monet5/Tests/pyloader01.stable.out > @@ -56,11 +56,11 @@ Ready. > % sys.mytable, sys.mytable, sys.mytable # table_name > % a, d, s # name > % double, int, clob # type > -% 24, 1, 5 # length > +% 24, 1, 7 # length > +[ 42, 1, "'hello'" ] > [ 46, 4, "hello" ] > -[ 45, 3, NULL ] > -[ 44, 2, NULL ] > -[ 42, 1, NULL ] > +[ 45, 3, "'hello'" ] > +[ 44, 2, "'hello'" ] > #DROP TABLE mytable; > #DROP ALL LOADER myfunc; > #CREATE LOADER myfunc() LANGUAGE PYTHON { > diff --git a/sql/backends/monet5/UDF/pyapi/emit.c > b/sql/backends/monet5/UDF/pyapi/emit.c > --- a/sql/backends/monet5/UDF/pyapi/emit.c > +++ b/sql/backends/monet5/UDF/pyapi/emit.c > @@ -199,6 +199,7 @@ PyObject *PyEmit_Emit(PyEmitObject *self > > self->cols[self->ncols].b = COLnew(0, bat_type, 0, TRANSIENT); > self->cols[self->ncols].name = GDKstrdup(val); > + self->cols[self->ncols].def = NULL; > if (self->nvals > 0) { > // insert NULL values up until the current entry > for (ai = 0; ai < self->nvals; ai++) { > @@ -209,9 +210,9 @@ PyObject *PyEmit_Emit(PyEmitObject *self > goto wrapup; > } > } > - self->cols[i].b->tnil = 1; > - self->cols[i].b->tnonil = 0; > - BATsetcount(self->cols[i].b, self->nvals); > + self->cols[self->ncols].b->tnil = 1; > + self->cols[self->ncols].b->tnonil = 0; > + BATsetcount(self->cols[self->ncols].b, self->nvals); > } > self->ncols++; > } > @@ -337,14 +338,21 @@ PyObject *PyEmit_Emit(PyEmitObject *self > self->cols[i].b->tnonil = 1 - self->cols[i].b->tnil; > } > } else { > + void* nill_value = ATOMnil(self->cols[i].b->ttype); > + void* default_value = self->cols[i].def ? > + self->cols[i].def : > + nill_value; > for (ai = 0; ai < (size_t)el_count; ai++) { > - if (BUNappend(self->cols[i].b, ATOMnil(self->cols[i].b->ttype), > + if (BUNappend(self->cols[i].b, > + default_value, > 0) != GDK_SUCCEED) { > goto wrapup; > } > } > - self->cols[i].b->tnil = 1; > - self->cols[i].b->tnonil = 0; > + if (BATatoms[self->cols[i].b->ttype].atomCmp(default_value, nill_value) == > 0) { > + self->cols[i].b->tnil = 1; > + self->cols[i].b->tnonil = 0; > + } > } > BATsetcount(self->cols[i].b, self->nvals + el_count); > } > diff --git a/sql/backends/monet5/UDF/pyapi/pyloader.c > b/sql/backends/monet5/UDF/pyapi/pyloader.c > --- a/sql/backends/monet5/UDF/pyapi/pyloader.c > +++ b/sql/backends/monet5/UDF/pyapi/pyloader.c > @@ -146,6 +146,9 @@ PYFUNCNAME(PyAPIevalLoader)(Client cntxt > assert(i < pci->retc); > cols[i].name = GDKstrdup(*((char **)n->data)); > n = n->next; > + assert(n); > + cols[i].def = n->data; > + n = n->next; > cols[i].b = > COLnew(0, getBatType(getArgType(mb, pci, i)), 0, TRANSIENT); > cols[i].b->tnil = 0; > diff --git a/sql/include/sql_catalog.h b/sql/include/sql_catalog.h > --- a/sql/include/sql_catalog.h > +++ b/sql/include/sql_catalog.h > @@ -599,6 +599,7 @@ extern node *find_sql_func_node(sql_sche > typedef struct { > BAT *b; > char* name; > + void* def; > } sql_emit_col; > > #endif /* SQL_CATALOG_H */ > diff --git a/sql/server/rel_updates.c b/sql/server/rel_updates.c > --- a/sql/server/rel_updates.c > +++ b/sql/server/rel_updates.c > @@ -1173,7 +1173,7 @@ table_column_types(sql_allocator *sa, sq > } > > static list * > -table_column_names(sql_allocator *sa, sql_table *t) > +table_column_names_and_defaults(sql_allocator *sa, sql_table *t) > { > node *n; > list *types = sa_list(sa); > @@ -1181,6 +1181,7 @@ table_column_names(sql_allocator *sa, sq > if (t->columns.set) for (n = t->columns.set->h; n; n = n->next) { > sql_column *c = n->data; > append(types, &c->base.name); > + append(types, c->def); > } > return types; > } > @@ -1577,7 +1578,7 @@ copyfromloader(mvc *sql, dlist *qname, s > return NULL; > } > ((sql_subfunc*) import->f)->res = table_column_types(sql->sa, t); > - ((sql_subfunc*) import->f)->colnames = table_column_names(sql->sa, t); > + ((sql_subfunc*) import->f)->colnames = > table_column_names_and_defaults(sql->sa, t); > > for (n = t->columns.set->h; n; n = n->next) { > sql_column *c = n->data; > _______________________________________________ > checkin-list mailing list > checkin-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/checkin-list -- | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |

1 0

MonetDB Integration Question
by David Broneske 13 Nov '17

13 Nov '17

Dear developers of MonetDB, I hope you are the right recipients of this mail that can help us in getting some more insights into MonetDB’s Code. Currently, me and my student (Florian) want to integrate our index structure Elf (www.elf.ovgu.de <http://www.elf.ovgu.de/>) into MonetDB as a proof of concept and to show its competitiveness to fast main-memory scans. However, we’ve got a hard time to understand some part of the code — we hope that you could help us with this. While the kernel (gdk) is well documented, we hardly find any documentation that may help us in answering the following questions: Which function takes care of filling the BATs when inserting into a table (i.e., sql_table/sql_rel)? COPY INTO creates a MAL-procedure copy_into, which is preparing the copy, but does not finally execute the copy. Which function is executed for this? Regarding ChangeSets: what is dset and nelm used for? Any pointer or explanation for these questions would be great! Regards, David and Florian M.Sc. David Broneske Otto-von-Guericke-Universität Magdeburg Department of Technical and Business Information Systems <http://wwwiti.cs.uni-magdeburg.de/> Databases and Software Engineering <http://wwwiti.cs.uni-magdeburg.de/iti_db/index_en.php> P.O. Box 4120, D-39016 Magdeburg, Germany Office: Building 29, Room112 Tel: +49 391 67-52994 <tel:+49 391 67-52994> Fax: +49 391 67-12020 <fax:+49 391 67-12020>

2 1