June 2007 - developers-list

[Monetdb-developers] new release
by Sjoerd Mullender 15 Jun '07

15 Jun '07

Some problems have been found in the X.18.0 release that we've created in both SQL and XQuery. Because of that we're planning to do a minor release of just those two packages on Tuesday. In SQL, the error was a crash of the server when adding a foreign key to an existing table. In XQuery, the problem was mainly a performance issue (and missing pftijah functionality in the Windows builds). -- Sjoerd Mullender

1 0

[Monetdb-developers] Release is done
by Sjoerd Mullender 14 Jun '07

14 Jun '07

The new release (X.18.0 for various values of X, and 5.0.0) is available on Sourceforge. The CVS Stable branch is once again open for bug fixing (and *only* bug fixing). As soon as the web site has been updated, you can see the full release notes at http://monetdb.cwi.nl/projects/monetdb/Development/Releases/Version4.18/ind… Niels, can you update the web site? Martin, can you send the announcements? -- Sjoerd Mullender

2 1

[Monetdb-developers] Dealing with large tables
by Peter Boncz 14 Jun '07

14 Jun '07

Hi Augustin, The crash is a bug. You have not told us about your platform (Darwin, I presume) and whether you have compiled with 64-bits enabled. Considering the data is 'large', it may be an out-of-memory problem but then in any case it is a bug, because lack of resources should lead to the query failing rather than crashing. So, first provide us the missing platform info (is it 64 bits????), and second, recompile with debugging enabled (configure with: --enable-debug=yes --enable-optimize=no). Since this problem seems highly reproducable, then run again following my instructions: (0) start Mserver in gdb (gdb --args Mserver ...args''') (1) modify the MIL script to set the debugmask 19 (2) also insert some extra info() calls (3) when it crashes, type in the gdb console: bt (4) send us all the output Your modified MIL script should resemble: debugmask(19); var var_0 := [=](col1,col2).select(true).mirror(); var_0.info().print(); col1.info().print(); col1.delete(var_0); commit(); col2.info().print(); col2.delete(var_0); commit(); col3.info().print(); col3.delete(var_0); commit(); thanks in advance, Peter ------------------------------ Message: 6 Date: Thu, 14 Jun 2007 11:59:24 -0400 From: Agustin Schapira <schapira(a)cs.umass.edu> Subject: [Monetdb-developers] Dealing with large tables To: monetdb-developers(a)lists.sourceforge.net Message-ID: <9FAD9406-07DC-454A-9AB0-A9C9DFD05153(a)cs.umass.edu> Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Dear Monet developers, Once again, here I am, writing for help ;-) We are working with a 'large' table, with 250 million rows, 11 columns. This takes up a lot of memory space, as you can imagine: a single column of 250 million 'ints' requires almost a gigabyte, and there's several of those; in fact, I think that the entire table takes up a bit over 6GB in memory (some columns are strings, and can be compressed). The problem is that we have a machine with 8GB, and every time we try to do something with that table, Monet (4.16.2) crashes. For example, I've been trying to delete rows from the table where two columns have the same value: var var_0 := [=](col1,col2).select(true).mirror(); col1.delete(var_0); commit(); col2.delete(var_0); commit(); col3.delete(var_0); commit(); ... After deleting from a few of those columns, Monet dies: !ERROR: BATSIGcrash: Mserver internal error (Segmentation fault), please restart. !ERROR: (One potential cause could be that your disk might be full...) Do you know if there a fix for this? In principle, Monet should be able to unload the BATs that it has already deleted from, to release memory. Also, would doing a semijoin() instead of a delete() help? (I though that a semijoin would need to create a new BAT, thereby using even more memory!). And, in general: is Monet 5 capable of accessing BATs that don't entirely fit in memory? Would the new version fix this problem? Maybe it's time for us to adapt it... Thanks again for all your help, and regards from Amherst, -- Agustin ------------------------------ Message: 7 Date: Thu, 14 Jun 2007 18:29:05 +0200 From: Martin Kersten <Martin.Kersten(a)cwi.nl> Subject: Re: [Monetdb-developers] Dealing with large tables To: Agustin Schapira <schapira(a)cs.umass.edu> Cc: monetdb-developers(a)lists.sourceforge.net Message-ID: <46716CD1.8080104(a)cwi.nl> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Agustin Schapira wrote: > Dear Monet developers, > > Once again, here I am, writing for help ;-) > > We are working with a 'large' table, with 250 million rows, 11 > columns. This takes up a lot of memory space, as you can imagine: a > single column of 250 million 'ints' requires almost a gigabyte, and I would say 1GB is a minimum. What if you are on a 64bit machine? And also, if this table requires a hash index, then add another 1GB/table. > there's several of those; in fact, I think that the entire table > takes up a bit over 6GB in memory (some columns are strings, and can > be compressed). The problem is that we have a machine with 8GB, and > every time we try to do something with that table, Monet (4.16.2) > crashes. For example, I've been trying to delete rows from the table > where two columns have the same value: > > var var_0 := [=](col1,col2).select(true).mirror(); Hard to deduce what's happening. > col1.delete(var_0); > commit(); > col2.delete(var_0); > commit(); > col3.delete(var_0); > commit(); I would first try to make sure if the commit is not sitting in the way. Do you need to commit here ? > ... > > After deleting from a few of those columns, Monet dies: > > !ERROR: BATSIGcrash: Mserver internal error (Segmentation fault), > please restart. > !ERROR: (One potential cause could be that your disk might be full...) > > > Do you know if there a fix for this? In principle, Monet should be > able to unload the BATs that it has already deleted from, to release > memory. Also, would doing a semijoin() instead of a delete() help? (I > though that a semijoin would need to create a new BAT, thereby using > even more memory!). And, in general: is Monet 5 capable of accessing > BATs that don't entirely fit in memory? Would the new version fix > this problem? Maybe it's time for us to adapt it... > > Thanks again for all your help, and regards from Amherst, > > -- Agustin > > ------------------------------------------------------------------------- > This SF.net email is sponsored by DB2 Express > Download DB2 Express C - the FREE version of DB2 express and take > control of your XML. No limits. Just data. Click to get it now. > http://sourceforge.net/powerbar/db2/ > _______________________________________________ > Monetdb-developers mailing list > Monetdb-developers(a)lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-developers ------------------------------ ------------------------------------------------------------------------- This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ ------------------------------ _______________________________________________ Monetdb-developers mailing list Monetdb-developers(a)lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-developers End of Monetdb-developers Digest, Vol 13, Issue 6 *************************************************

1 0

[Monetdb-developers] Dealing with large tables
by Agustin Schapira 14 Jun '07

14 Jun '07

Dear Monet developers, Once again, here I am, writing for help ;-) We are working with a 'large' table, with 250 million rows, 11 columns. This takes up a lot of memory space, as you can imagine: a single column of 250 million 'ints' requires almost a gigabyte, and there's several of those; in fact, I think that the entire table takes up a bit over 6GB in memory (some columns are strings, and can be compressed). The problem is that we have a machine with 8GB, and every time we try to do something with that table, Monet (4.16.2) crashes. For example, I've been trying to delete rows from the table where two columns have the same value: var var_0 := [=](col1,col2).select(true).mirror(); col1.delete(var_0); commit(); col2.delete(var_0); commit(); col3.delete(var_0); commit(); ... After deleting from a few of those columns, Monet dies: !ERROR: BATSIGcrash: Mserver internal error (Segmentation fault), please restart. !ERROR: (One potential cause could be that your disk might be full...) Do you know if there a fix for this? In principle, Monet should be able to unload the BATs that it has already deleted from, to release memory. Also, would doing a semijoin() instead of a delete() help? (I though that a semijoin would need to create a new BAT, thereby using even more memory!). And, in general: is Monet 5 capable of accessing BATs that don't entirely fit in memory? Would the new version fix this problem? Maybe it's time for us to adapt it... Thanks again for all your help, and regards from Amherst, -- Agustin

3 3

Re: [Monetdb-developers] [Monetdb-pf-checkins] pathfinder MonetDB-XQuery.spec.in, XQuery_0-18, 1.21, 1.21.2.1
by Stefan Manegold 13 Jun '07

13 Jun '07

On Mon, Jun 11, 2007 at 01:37:14PM +0000, Sjoerd Mullender wrote: > Update of /cvsroot/monetdb/pathfinder > In directory sc8-pr-cvs16.sourceforge.net:/tmp/cvs-serv2101 > > Modified Files: > Tag: XQuery_0-18 > MonetDB-XQuery.spec.in > Log Message: > There is no need to specify that the version of MonetDB4-server needs > to be below 5.0 because it is by definition. > Also, make the minimum requirement 4.18. good point --- I apperantly overlooked that one when updateing the vertoo configuration --- should we also handle this via vertoo? Stefan > Index: MonetDB-XQuery.spec.in > =================================================================== > RCS file: /cvsroot/monetdb/pathfinder/MonetDB-XQuery.spec.in,v > retrieving revision 1.21 > retrieving revision 1.21.2.1 > diff -u -d -r1.21 -r1.21.2.1 > --- MonetDB-XQuery.spec.in 11 Feb 2007 22:04:35 -0000 1.21 > +++ MonetDB-XQuery.spec.in 11 Jun 2007 13:37:11 -0000 1.21.2.1 > @@ -39,7 +39,7 @@ > %package devel > Summary: MonetDB XQuery development package > Group: System > -Requires: MonetDB4-server>=4.15<5.0 > +Requires: MonetDB4-server >= 4.18 > > %description devel > Add the MonetDB devel description here > > > ------------------------------------------------------------------------- > This SF.net email is sponsored by DB2 Express > Download DB2 Express C - the FREE version of DB2 express and take > control of your XML. No limits. Just data. Click to get it now. > http://sourceforge.net/powerbar/db2/ > _______________________________________________ > Monetdb-pf-checkins mailing list > Monetdb-pf-checkins(a)lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-pf-checkins > > -- | 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 |

3 3

Re: [Monetdb-developers] [Monetdb-checkins] buildtools/conf MonetDB.m4, MonetDB_1-18, 1.16.2.6, 1.16.2.7
by Stefan Manegold 13 Jun '07

13 Jun '07

On Fri, Jun 08, 2007 at 02:03:41PM +0000, Sjoerd Mullender wrote: > Update of /cvsroot/monetdb/buildtools/conf > In directory sc8-pr-cvs16.sourceforge.net:/tmp/cvs-serv19133 > > Modified Files: > Tag: MonetDB_1-18 > MonetDB.m4 > Log Message: > Ported to Fedora (no Core) 7. Thanks, Sjoerd! ... did you also update TestTools/.Mconfig.rc & TestTools/MdoServer accordingly? (sorry, due bad network and/but good and intensive networking, I haven't followed all check in message...) It's not curcial, until we have an F67 machine in our testing pool, but we shuld not "forget" it ... Greetings from a hot, busy, foggy Beijing! Stefan > > Index: MonetDB.m4 > =================================================================== > RCS file: /cvsroot/monetdb/buildtools/conf/MonetDB.m4,v > retrieving revision 1.16.2.6 > retrieving revision 1.16.2.7 > diff -u -d -r1.16.2.6 -r1.16.2.7 > --- MonetDB.m4 8 Jun 2007 08:19:19 -0000 1.16.2.6 > +++ MonetDB.m4 8 Jun 2007 14:03:38 -0000 1.16.2.7 > @@ -412,7 +412,7 @@ > dnl Please keep this aligned / in sync with TestTools/.Mconfig.rc & TestTools/MdoServer ! > if test -s /etc/fedora-release ; then > LINUX_DIST="`cat /etc/fedora-release | head -n1 \ > - | sed 's|^.*$Fedora$ Core.* release $[[0-9]][[^ \n]]*$$ .*$*$|\1:\2|'`" > + | sed 's|^.*$Fedora$.* release $[[0-9]][[^ \n]]*$$ .*$*$|\1:\2|'`" > elif test -s /etc/centos-release ; then > LINUX_DIST="`cat /etc/centos-release | head -n1 \ > | sed 's|^$CentOS$ release $[[0-9]][[^ \n]]*$$ .*$*$|\1:\2|'`" > > > ------------------------------------------------------------------------- > This SF.net email is sponsored by DB2 Express > Download DB2 Express C - the FREE version of DB2 express and take > control of your XML. No limits. Just data. Click to get it now. > http://sourceforge.net/powerbar/db2/ > _______________________________________________ > Monetdb-checkins mailing list > Monetdb-checkins(a)lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-checkins > > -- | 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 |

1 0

[Monetdb-developers] Compilation of M5 stable in windows
by Romulo Goncalves 11 Jun '07

11 Jun '07

During the compilation I got: copy ".\..\..\src\mal\Makefile.msc" "mal\Makefile" 1 file(s) copied. cd "mal" && nmake /nologo "prefix=C:\cygwin\home\goncalve\MonetDB\MonetDB_SQL\Stable\MonetDB5\NT" all cl -GF -W3 -wd4273 -wd4102 -MD -nologo -Zi -Ox -I. -I.\..\.. "-IC:\Pthreads\include" -I.\..\..\..\src\mal -I"C:\cygwi \home\goncalve\MonetDB\MonetDB_SQL\Stable\MonetDB\NT\include" -I"C:\cygwin\home\goncalve\MonetDB\MonetDB_SQL\Stable\MonetDB\N \include\common" -I"C:\cygwin\home\goncalve\MonetDB\MonetDB_SQL\Stable\MonetDB\NT\include\gdk" -I"C:\cygwin\home\goncalve\Mon tDB\MonetDB_SQL\Stable\MonetDB\NT\include\monet" -I"C:\cygwin\home\goncalve\MonetDB\MonetDB_SQL\Stable\MonetDB\NT\include\pla n" -I"C:\cygwin\home\goncalve\MonetDB\MonetDB_SQL\Stable\MonetDB\NT\include\mapi" "-I..\optimizer" "-I..\scheduler" "-I..\m dules\kernel" -DLIBMAL -DCOMPILE_DL_MAL -Fo"mal.obj" -c "mal.c" mal.c ..\..\mal_config.h(23) : fatal error C1083: Cannot open include file: 'monetdb_winconfig.h': No such file or directory NMAKE : fatal error U1077: 'cl' : return code '0x2' Stop. NMAKE : fatal error U1077: 'cd' : return code '0x2' Stop. NMAKE : fatal error U1077: 'cd' : return code '0x2' Stop. NMAKE : fatal error U1077: '"E:\Visual Studio .NET 2003\VC7\BIN\nmake.exe"' : return code '0x2' I am using the same environment as the one used for the cvs branch. Any idea in how to resolve this problem? Regards, Romulo

3 6

Re: [Monetdb-developers] Compilation of M5 stable in windows
by Romulo Goncalves 11 Jun '07

11 Jun '07

Niels Nes wrote: > On Mon, Jun 11, 2007 at 09:50:37PM +0200, Romulo Goncalves wrote: >> Sjoerd Mullender wrote: >>> On 06/11/2007 08:43 PM, Romulo Goncalves wrote: >>>> Sjoerd Mullender wrote: >>>>> On 06/11/2007 06:46 PM, Romulo Goncalves wrote: >>>> I just checked out everything. I think it is a PATH problem. >>> Did you also re-bootstrap? Another standard requirement if there has >>> been a change in buildtools. >> When you check out a clean version you also need a re-boostraap? I do >> not think so. > If new files are added (ie Makefile.ag changed) or buildtools > is changed it is needed. When I say checkout clean source I mean everything (buildtools, clients, MonetDB, MonetDB5, sql). Then bootstraap, configure make and make install ;) As I said the problem it is for sure some wrong PATH that I set. ;) It is a stupid error from my part. So discussion is closed .... :) Regards, Romulo > > Niels >> Regards, >> Romulo >> >> ------------------------------------------------------------------------- >> This SF.net email is sponsored by DB2 Express >> Download DB2 Express C - the FREE version of DB2 express and take >> control of your XML. No limits. Just data. Click to get it now. >> http://sourceforge.net/powerbar/db2/ >> _______________________________________________ >> Monetdb-developers mailing list >> Monetdb-developers(a)lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/monetdb-developers >

1 0

[Monetdb-developers] M5 strict and optimized compilation...
by Romulo Goncalves 11 Jun '07

11 Jun '07

During the compilation I got the following error: cc1: warnings being treated as errors /ufs/goncalve/MonetDB/MonetDB5/src/modules/mal/bpm.mx: In function 'BPMadapt': /ufs/goncalve/MonetDB/MonetDB5/src/modules/mal/bpm.mx:1587: warning: 'vp' may be used uninitialized in this function make[7]: *** [lib_bpm_la-bpm.lo] Error 1 make[7]: Leaving directory `/net/amelia/export/scratch1/goncalve/MonetDB/Stable/MonetDB5/build/src/modules/mal' The function head is here: static str BPMadaptImpl(int *ret, bat *bid, ptr low, ptr hgh, bat *rs, int policy ) { BAT *b,*bs, *bs1, *bs2; Partition ps, p, p2; int (*cmp) (ptr, ptr); ptr nilptr; ValPtr vp; Can I initialize it to NULL? If someone disagree please fix it with the correct value. Regards, Romulo

1 0

Re: [Monetdb-developers] [Monetdb-sql-checkins] sql/src/test/Triggers cascaded_triggers.sql, SQL_2-18, 1.2, 1.2.4.1 recursive_triggers.sql, SQL_2-18, 1.2, 1.2.4.1 referencing_clause_new.sql, SQL_2-18, 1.4, 1.4.4.1 referencing_clause_old.sql, SQL_2-18, 1.4, 1.4.4.1
by Romulo Goncalves 08 Jun '07

08 Jun '07

Romulo Goncalves wrote: > Update of /cvsroot/monetdb/sql/src/test/Triggers > In directory sc8-pr-cvs16.sourceforge.net:/tmp/cvs-serv21172 > > Modified Files: > Tag: SQL_2-18 > cascaded_triggers.sql recursive_triggers.sql > referencing_clause_new.sql referencing_clause_old.sql > Log Message: > The new tests for UPDATE/DELETE CASCADE The new tests for the Trigger tests... Sorry for the wrong log message > > > > > Index: referencing_clause_new.sql > =================================================================== > RCS file: /cvsroot/monetdb/sql/src/test/Triggers/referencing_clause_new.sql,v > retrieving revision 1.4 > retrieving revision 1.4.4.1 > diff -u -d -r1.4 -r1.4.4.1 > --- referencing_clause_new.sql 7 Dec 2006 15:26:06 -0000 1.4 > +++ referencing_clause_new.sql 8 Jun 2007 12:47:50 -0000 1.4.4.1 > @@ -1,101 +1,199 @@ > +--this test only tests the sintax > --the semantic should also be tested after the syntax test > > create table t1 (id int, name varchar(1024)); > +create table t2 (id int, name varchar(1024)); > > ---test when the trigger event is UPDATE > +--test when trigger event is UPDATE > insert into t1 values(10, 'monetdb'); > insert into t1 values(20, 'monet'); > > > create trigger test1 > after update on t1 referencing new row as new_row > - for each row insert into t1 values(0, 'update_new_row'); > + for each row insert into t2 values(0, 'update_new_row'); > > create trigger test2 > after update on t1 referencing new row new_row > - for each row insert into t1 values(1, 'update_new_row'); > + for each row insert into t2 values(1, 'update_new_row'); > > create trigger test3 > after update on t1 referencing new as new_row > - for each row insert into t1 values(2, 'update_new_row'); > + for each row insert into t2 values(2, 'update_new_row'); > > create trigger test4 > after update on t1 referencing new new_row > - for each row insert into t1 values(3, 'update_new_row'); > + for each row insert into t2 values(3, 'update_new_row'); > > > update t1 set name = 'mo' where id = 10; > > select * from t1; > +select * from t2; > + > +delete from t1 where id > -1; > +delete from t2 where id > -1; > + > +drop trigger test1; > +drop trigger test2; > +drop trigger test3; > +drop trigger test4; > + > +--test when trigger event is DELETE > +insert into t1 values(10, 'monetdb'); > +insert into t1 values(20, 'monet'); > + > +create trigger test1 > + after delete on t1 referencing new row as new_row > + for each row insert into t2 values(0, 'delete_new_row'); > + > +create trigger test2 > + after delete on t1 referencing new row new_row > + for each row insert into t2 values(1, 'delete_new_row'); > + > +create trigger test3 > + after delete on t1 referencing new as new_row > + for each row insert into t2 values(2, 'delete_new_row'); > + > +create trigger test4 > + after delete on t1 referencing new new_row > + for each row insert into t2 values(3, 'delete_new_row'); > + > > delete from t1 where id >-1; > > +select * from t1; > +select * from t2; > + > drop trigger test1; > drop trigger test2; > drop trigger test3; > drop trigger test4; > > ---test when the trigger event is INSERT > +delete from t2 where id >-1; > + > +--test error messages > +--new row and new table are not allowed if the Trigger event is INSERT > + > insert into t1 values(10, 'monetdb'); > > create trigger test1 > after insert on t1 referencing new row as new_row > - for each row insert into t1 values(0, 'insert_new_row'); > + for each row insert into t2 values(0, 'insert_new_row'); > > create trigger test2 > after insert on t1 referencing new row new_row > - for each row insert into t1 values(1, 'insert_new_row'); > + for each row insert into t2 values(1, 'insert_new_row'); > > create trigger test3 > after insert on t1 referencing new as new_row > - for each row insert into t1 values(2, 'insert_new_row'); > + for each row insert into t2 values(2, 'insert_new_row'); > > create trigger test4 > after insert on t1 referencing new new_row > - for each row insert into t1 values(3, 'insert_new_row'); > + for each row insert into t2 values(3, 'insert_new_row'); > + > > insert into t1 values(20, 'monet'); > > select * from t1; > +select * from t2; > > -delete from t1 where id > -1; > +delete from t1 where id >-1; > +delete from t2 where id >-1; > > drop trigger test1; > drop trigger test2; > drop trigger test3; > drop trigger test4; > > ---test error messages > ---new row and new table are not allowed if the Trigger event is DELETE > +--test with new row and new table and mixed > > insert into t1 values(10, 'monetdb'); > insert into t1 values(20, 'monet'); > > create trigger test1 > - after delete on t1 referencing new row as new_row > - for each row insert into t1 values(0, 'delete_new_row'); > + after update on t1 referencing new row as new_row new table as new_table > + for each row insert into t2 values(0, 'insert_new_row_table'); > > create trigger test2 > - after delete on t1 referencing new row new_row > - for each row insert into t1 values(1, 'delete_new_row'); > + after update on t1 referencing new row new_row new row as new_row > + for each row insert into t2 values(1, 'insert_new_new_row'); > > create trigger test3 > - after delete on t1 referencing new as new_row > - for each row insert into t1 values(2, 'delete_new_row'); > + after update on t1 referencing new table as new_table new table as new_table > + for each row insert into t2 values(2, 'insert_new__new_table'); > > create trigger test4 > - after delete on t1 referencing new new_row > - for each row insert into t1 values(3, 'delete_new_row'); > + after update on t1 referencing new row as new_row new table as new_table > + for each row insert into t2 values(3, 'insert_new_row_new_table'); > > +create trigger test5 > + after update on t1 referencing new table as new_table new row as new_row > + for each row insert into t2 values(4, 'insert_new_table_new_row'); > > -delete from t1 where id >1; > + > +update t1 set name = 'mo' where id = 10; > > select * from t1; > +select * from t2; > + > +delete from t1 where id >-1; > +delete from t2 where id >-1; > + > > drop trigger test1; > drop trigger test2; > drop trigger test3; > drop trigger test4; > +drop trigger test5; > + > +--test stanger combinations > + > +insert into t1 values(10, 'monetdb'); > + > +create trigger test1 > + after update on t1 referencing new row as new_row new table as new_table > + for each row insert into t2 values(0, 'update_new_row_new_table'); > + > +create trigger test2 > + after insert on t1 referencing new row new_row new row as new_row > + for each row insert into t2 values(1, 'insert_new_new_row'); > + > +create trigger test3 > + after delete on t1 referencing new row new_row new row as new_row > + for each row insert into t2 values(2, 'delete_new_new_row'); > + > +create trigger test4 > + after delete on t1 referencing new row as new_row new table as new_table > + for each row insert into t2 values(3, 'delete_new_row_new_table'); > + > +create trigger test5 > + after insert on t1 referencing new table as new_table new row as new_row > + for each row insert into t2 values(4, 'insert_new_table_new_row'); > + > +insert into t1 values(20, 'monet'); > +select * from t1; > +select * from t2; > + > +update t1 set name = 'mo' where id = 10; > +select * from t1; > +select * from t2; > + > +delete from t1 where id >5; > +select * from t1; > +select * from t2; > + > + > +drop trigger test1; > +drop trigger test2; > +drop trigger test3; > +drop trigger test4; > +drop trigger test5; > + > +delete from t1 where id >-1; > +delete from t2 where id >-1; > > --Cleanup > drop table t1; > +drop table t2; > > Index: cascaded_triggers.sql > =================================================================== > RCS file: /cvsroot/monetdb/sql/src/test/Triggers/cascaded_triggers.sql,v > retrieving revision 1.2 > retrieving revision 1.2.4.1 > diff -u -d -r1.2 -r1.2.4.1 > --- cascaded_triggers.sql 6 Dec 2006 10:27:38 -0000 1.2 > +++ cascaded_triggers.sql 8 Jun 2007 12:47:49 -0000 1.2.4.1 > @@ -1,53 +1,31 @@ > ---test the semantic of cascaded triggers > create table t1(id int, name varchar(1024), age int); > - > create table t2(id int, age int); > > create trigger test_0 after insert on t1 > - update t1 set id = 1, name = 'monetdb', age = 24 where id =1; > - > -insert into t1 values(1, 'mo', 25); > - > -select * from t1; > - > -select * from t2; > - > -create trigger test_1 after insert on t1 > insert into t2 select id,age from t1; > > -insert into t1 values(2, 'mo', 26); > - > -select * from t1; > - > -select * from t2; > - > -create trigger test_2 after insert on t1 > - insert into t2 values(1,23); > - > -insert into t1 values(3, 'mo', 27); > +insert into t1 values(1, 'mo', 25); > > select * from t1; > - > select * from t2; > > -create trigger test_3 after delete on t1 > - delete from t1 where id =3; > +create trigger test_1 after delete on t1 > + insert into t1 values(3, 'mo', 27); > > delete from t1 where id = 1; > > select * from t1; > - > select * from t2; > > -create trigger test_4 after update on t1 > - update t1 > - set age = 27 > - where id = 2; > +create trigger test_2 after update on t1 > + delete from t2; > + > +create trigger test_3 after delete on t2 > + insert into t1 values(1, 'mo', 25); > > update t1 set name = 'monet' where id = 2; > > select * from t1; > - > select * from t2; > > drop trigger test_0; > @@ -58,8 +36,6 @@ > > drop trigger test_3; > > -drop trigger test_4; > - > drop table t1; > > drop table t2; > > Index: referencing_clause_old.sql > =================================================================== > RCS file: /cvsroot/monetdb/sql/src/test/Triggers/referencing_clause_old.sql,v > retrieving revision 1.4 > retrieving revision 1.4.4.1 > diff -u -d -r1.4 -r1.4.4.1 > --- referencing_clause_old.sql 8 Dec 2006 12:17:42 -0000 1.4 > +++ referencing_clause_old.sql 8 Jun 2007 12:47:50 -0000 1.4.4.1 > @@ -2,6 +2,7 @@ > --the semantic should also be tested after the syntax test > > create table t1 (id int, name varchar(1024)); > +create table t2 (id int, name varchar(1024)); > > --test when trigger event is UPDATE > insert into t1 values(10, 'monetdb'); > @@ -10,26 +11,28 @@ > > create trigger test1 > after update on t1 referencing old row as old_row > - for each row insert into t1 values(0, 'update_old_row'); > + for each row insert into t2 values(0, 'update_old_row'); > > create trigger test2 > after update on t1 referencing old row old_row > - for each row insert into t1 values(1, 'update_old_row'); > + for each row insert into t2 values(1, 'update_old_row'); > > create trigger test3 > after update on t1 referencing old as old_row > - for each row insert into t1 values(2, 'update_old_row'); > + for each row insert into t2 values(2, 'update_old_row'); > > create trigger test4 > after update on t1 referencing old old_row > - for each row insert into t1 values(3, 'update_old_row'); > + for each row insert into t2 values(3, 'update_old_row'); > > > update t1 set name = 'mo' where id = 10; > > select * from t1; > +select * from t2; > > delete from t1 where id > -1; > +delete from t2 where id > -1; > > drop trigger test1; > drop trigger test2; > @@ -42,31 +45,32 @@ > > create trigger test1 > after delete on t1 referencing old row as old_row > - for each row insert into t1 values(0, 'delete_old_row'); > + for each row insert into t2 values(0, 'delete_old_row'); > > create trigger test2 > after delete on t1 referencing old row old_row > - for each row insert into t1 values(1, 'delete_old_row'); > + for each row insert into t2 values(1, 'delete_old_row'); > > create trigger test3 > after delete on t1 referencing old as old_row > - for each row insert into t1 values(2, 'delete_old_row'); > + for each row insert into t2 values(2, 'delete_old_row'); > > create trigger test4 > after delete on t1 referencing old old_row > - for each row insert into t1 values(3, 'delete_old_row'); > + for each row insert into t2 values(3, 'delete_old_row'); > > > delete from t1 where id >-1; > > select * from t1; > +select * from t2; > > drop trigger test1; > drop trigger test2; > drop trigger test3; > drop trigger test4; > > -delete from t1 where id >-1; > +delete from t2 where id >-1; > > --test error messages > --old row and old table are not allowed if the Trigger event is INSERT > @@ -75,26 +79,28 @@ > > create trigger test1 > after insert on t1 referencing old row as old_row > - for each row insert into t1 values(0, 'insert_old_row'); > + for each row insert into t2 values(0, 'insert_old_row'); > > create trigger test2 > after insert on t1 referencing old row old_row > - for each row insert into t1 values(1, 'insert_old_row'); > + for each row insert into t2 values(1, 'insert_old_row'); > > create trigger test3 > after insert on t1 referencing old as old_row > - for each row insert into t1 values(2, 'insert_old_row'); > + for each row insert into t2 values(2, 'insert_old_row'); > > create trigger test4 > after insert on t1 referencing old old_row > - for each row insert into t1 values(3, 'insert_old_row'); > + for each row insert into t2 values(3, 'insert_old_row'); > > > insert into t1 values(20, 'monet'); > > select * from t1; > +select * from t2; > > delete from t1 where id >-1; > +delete from t2 where id >-1; > > drop trigger test1; > drop trigger test2; > @@ -108,30 +114,33 @@ > > create trigger test1 > after update on t1 referencing old row as old_row old table as old_table > - for each row insert into t1 values(0, 'insert_old_row_table'); > + for each row insert into t2 values(0, 'insert_old_row_table'); > > create trigger test2 > after update on t1 referencing old row old_row new row as new_row > - for each row insert into t1 values(1, 'insert_old_new_row'); > + for each row insert into t2 values(1, 'insert_old_new_row'); > > create trigger test3 > after update on t1 referencing old table as old_table new table as new_table > - for each row insert into t1 values(2, 'insert_old__new_table'); > + for each row insert into t2 values(2, 'insert_old__new_table'); > > create trigger test4 > after update on t1 referencing old row as old_row new table as new_table > - for each row insert into t1 values(3, 'insert_old_row_new_table'); > + for each row insert into t2 values(3, 'insert_old_row_new_table'); > > create trigger test5 > after update on t1 referencing old table as old_table new row as new_row > - for each row insert into t1 values(3, 'insert_old_table_new_row'); > + for each row insert into t2 values(4, 'insert_old_table_new_row'); > > > update t1 set name = 'mo' where id = 10; > > select * from t1; > +select * from t2; > > delete from t1 where id >-1; > +delete from t2 where id >-1; > + > > drop trigger test1; > drop trigger test2; > @@ -145,32 +154,35 @@ > > create trigger test1 > after update on t1 referencing old row as old_row new table as new_table > - for each row insert into t1 values(0, 'update_old_row__new_table'); > + for each row insert into t2 values(0, 'update_old_row_new_table'); > > create trigger test2 > after insert on t1 referencing old row old_row new row as new_row > - for each row insert into t1 values(1, 'insert_old_new_row'); > + for each row insert into t2 values(1, 'insert_old_new_row'); > > create trigger test3 > after delete on t1 referencing old row old_row new row as new_row > - for each row insert into t1 values(1, 'delete_old_new_row'); > + for each row insert into t2 values(2, 'delete_old_new_row'); > > create trigger test4 > after delete on t1 referencing old row as old_row new table as new_table > - for each row insert into t1 values(3, 'delete_old_row_new_table'); > + for each row insert into t2 values(3, 'delete_old_row_new_table'); > > create trigger test5 > after insert on t1 referencing old table as old_table new row as new_row > - for each row insert into t1 values(3, 'insert_old_table_new_row'); > + for each row insert into t2 values(4, 'insert_old_table_new_row'); > > insert into t1 values(20, 'monet'); > select * from t1; > +select * from t2; > > update t1 set name = 'mo' where id = 10; > select * from t1; > +select * from t2; > > delete from t1 where id >5; > select * from t1; > +select * from t2; > > > drop trigger test1; > @@ -180,6 +192,8 @@ > drop trigger test5; > > delete from t1 where id >-1; > +delete from t2 where id >-1; > > --Cleanup > drop table t1; > +drop table t2; > > Index: recursive_triggers.sql > =================================================================== > RCS file: /cvsroot/monetdb/sql/src/test/Triggers/recursive_triggers.sql,v > retrieving revision 1.2 > retrieving revision 1.2.4.1 > diff -u -d -r1.2 -r1.2.4.1 > --- recursive_triggers.sql 6 Dec 2006 10:27:38 -0000 1.2 > +++ recursive_triggers.sql 8 Jun 2007 12:47:49 -0000 1.2.4.1 > @@ -1,57 +1,62 @@ > create table t1(id int, name varchar(1024), age int); > create table t2(id int, age int); > > +--the trigger calls itself > create trigger test_0 after insert on t1 > - insert into t1 values(1, 'monetdb', 24); > - > -insert into t1 values(1, 'mo', 25); > + insert into t1 values(3, 'mo', 27); > > -select * from t1; > -select * from t2; > +drop trigger test_0; > > -create trigger test_1 after insert on t1 > +--recursivity of 2 levels > +create trigger test_0 after insert on t1 > insert into t2 select id,age from t1; > +create trigger test_1 after insert on t2 > + insert into t1 values(3, 'mo', 27); > > -insert into t1 values(2, 'mo', 26); > > -select * from t1; > -select * from t2; > +drop trigger test_0; > +drop trigger test_1; > > -create trigger test_2 before insert on t1 > - insert into t2 values(1,23); > +--recursivity of n levels > +create trigger test_0 after insert on t1 > + insert into t2 select id,age from t1; > > -insert into t1 values(3, 'mo', 27); > +create trigger test_1 after insert on t2 > + delete from t2; > > -select * from t1; > -select * from t2; > +create trigger test_2 after delete on t2 > + insert into t1 values(3, 'mo', 27); > > -create trigger test_3 after delete on t1 > - delete from t1 where id =3; > > -delete from t1 where id = 1; > +drop trigger test_0; > +drop trigger test_1; > +drop trigger test_2; > > -select * from t1; > -select * from t2; > +--recursivity with procedure calls > +create PROCEDURE p1(id int, age int) > +BEGIN > + insert into t2 values(id, age); > +END; > > -create trigger test_4 after update on t1 > - update t1 > - set age = 27 > - where id = 2; > +create PROCEDURE p1() > +BEGIN > + declare id int, age int; > + set id = 1; > + set age = 23; > + call p1(id, age); > +END; > > -update t1 set name = 'monet' where id = 2; > +create trigger test_0 after insert on t2 > +BEGIN ATOMIC > + insert into t1 values(1, 'monetdb', 24); > + call p1(); > +END; > > -select * from t1; > -select * from t2; > +--insert into t2 values(0, 24); > > drop trigger test_0; > > -drop trigger test_1; > - > -drop trigger test_2; > - > -drop trigger test_3; > - > -drop trigger test_4; > +drop ALL procedure p1; > > drop table t1; > > > > ------------------------------------------------------------------------- > This SF.net email is sponsored by DB2 Express > Download DB2 Express C - the FREE version of DB2 express and take > control of your XML. No limits. Just data. Click to get it now. > http://sourceforge.net/powerbar/db2/ > _______________________________________________ > Monetdb-sql-checkins mailing list > Monetdb-sql-checkins(a)lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-sql-checkins

1 0