Hi Stefan,
thanks for your hints. I'll take a look for the C functions.
I can understand your question. My question must look wired.
For research I want to implement some different compression schemas in a column based DB.
Afterwards I want to check some performance issues. I want to implement some compression schemas that aren't implemented for now.
So my first step is to manipulate the inserted/updated data.
Best regards
Thomas
-----Ursprüngliche Nachricht-----
Von: developers-list Im Auftrag von Stefan Manegold
Gesendet: Mittwoch, 11. Juli 2018 11:17
An: Communication channel for developers of the MonetDB
Betreff: Re: Insert/update value manipulation
Hi Thomas,
please find below some hints how to fine the code related to inserts and updates.
However, the most important question is:
Why do you want/need to manipulate (or in fact *mess with*) the values that are to be inserted or updated in a database ???
This obviously invalidates SQL semantics and correctness ...
Best,
Stefan
========
sql>create table t (a int, b int);
operation successful
#-- EXPLAIN yields the physical (MAL) plan for the given query/statement
sql>EXPLAIN insert into t values (1,2),(3,4);
+---------------------------------------------------------------------------------------------------------------+
| mal |
+=======================================================================
+========================================+
| function user.s50_1():void; |
| X_5:void := querylog.define("explain insert into t values (1,2),(3,4);":str, "default_pipe":str, 22:int); |
| X_9:bat[:int] := bat.new(nil:int); -- tmeporary column for to-be-inserted "a" values |
| X_8:int := sql.mvc(); |
| X_13:bat[:int] := bat.append(X_9:bat[:int], 1:int, true:bit); -- first "a" value: "1" |
| X_17:bat[:int] := bat.append(X_13:bat[:int], 3:int, true:bit); -- second "a" value: "3" |
| X_18:bat[:int] := bat.new(nil:int); -- tmeporary column for to-be-inserted "b values |
| X_21:bat[:int] := bat.append(X_18:bat[:int], 2:int, true:bit); -- first "b" value: "2" |
| X_24:bat[:int] := bat.append(X_21:bat[:int], 4:int, true:bit); -- second "b" value: "4" |
| X_26:int := sql.append(X_8:int, "sys":str, "t":str, "a":str, X_17:bat[:int]); -- appending new "a" column|
| X_31:int := sql.append(X_26:int, "sys":str, "t":str, "b":str, X_24:bat[:int]); -- appending new "b" column|
| X_33:lng := aggr.count(X_24:bat[:int]); |
| sql.affectedRows(X_31:int, X_33:lng); |
| end user.s50_1; |
| # optimizer.mitosis() |
| # optimizer.dataflow() |
| #inline actions= 0 time=2 usec |
| #remap actions= 0 time=4 usec |
| #costmodel actions= 1 time=2 usec |
| #coercion actions= 0 time=2 usec |
| #evaluate actions= 8 time=83 usec |
| #emptybind actions= 0 time=1 usec |
| #pushselect actions= 0 time=11 usec |
| #aliases actions= 8 time=13 usec |
| #mergetable actions= 0 time=73 usec |
| #deadcode actions= 0 time=14 usec |
| #aliases actions= 0 time=0 usec |
| #constants actions= 4 time=11 usec |
| #commonTerms actions= 0 time=9 usec |
| #projectionpath actions= 0 time=6 usec |
| #deadcode actions= 0 time=11 usec |
| #reorder actions= 1 time=47 usec |
| #matpack actions= 0 time=0 usec |
| #multiplex actions= 0 time=3 usec |
| #profiler actions=1 time=1 usec |
| #candidates actions=1 time=2 usec |
| #deadcode actions= 0 time=10 usec |
| #wlc actions= 0 time=2 usec |
| #garbagecollector actions= 1 time=110 usec |
| #total actions=26 time=674 usec |
+---------------------------------------------------------------------------------------------------------------+
40 tuples
#-- find the C functions ("address") that implement the respective MAL functions
#-- You'd need to find the C functions in the source code yourself
sql>select * from sys.malfunctions() where "module" in ('bat','sql') and
sql>"function" = 'append';
+--------+----------+---------------------------------------------------------------------------------------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| module | function | signature | address | comment |
+========+==========+===================================================
+====================================+============================+=====
+=======================================================================
+==================================================================+
| bat | append | command bat.append(i:bat[:any_1], u:any_1):bat[:any_1] | BKCappend_val_wrap; | append the value u to i |
| bat | append | command bat.append(i:bat[:any_1], u:any_1, force:bit):bat[:any_1] | BKCappend_val_force_wrap; | append the value u to i |
| bat | append | command bat.append(i:bat[:any_1], u:bat[:any_1], s:bat[:oid], force:bit):bat[:any_1] | BKCappend_cand_force_wrap; | append the content of u with candidate list s to i |
| bat | append | command bat.append(i:bat[:any_1], u:bat[:any_1], s:bat[:oid]):bat[:any_1] | BKCappend_cand_wrap; | append the content of u with candidate list s to i |
| bat | append | command bat.append(i:bat[:any_1], u:bat[:any_1], force:bit):bat[:any_1] | BKCappend_force_wrap; | append the content of u to i |
| bat | append | command bat.append(i:bat[:any_1], u:bat[:any_1]):bat[:any_1] | BKCappend_wrap; | append the content of u to i |
| sql | append | pattern sql.append(mvc:int, sname:str, tname:str, cname:str, ins:any):int | mvc_append_wrap; | Append to the column tname.cname (possibly optimized to replace the insert bat of tname.cname. Returns sequence number for order dependence. |
+--------+----------+---------------------------------------------------------------------------------------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
7 tuples
sql>insert into t values (1,2),(3,4);
2 affected rows
sql>EXPLAIN update t set a = 9;
+--------------------------------------------------------------------------------------------------+
| mal |
+=======================================================================
+===========================+
| function user.s54_1():void; |
| X_2:void := querylog.define("explain update t set a = 9;":str, "default_pipe":str, 11:int); |
| X_5:int := sql.mvc(); |
| C_6:bat[:oid] := sql.tid(X_5:int, "sys":str, "t":str); |
| X_11:bat[:int] := algebra.project(C_6:bat[:oid], 9:int); -- creating new "a" column with "9" |
| X_13:int := sql.update(X_5:int, "sys":str, "t":str, "a":str, C_6:bat[:oid], X_11:bat[:int]); -- updating existing "a" column |
| X_15:lng := aggr.count(C_6:bat[:oid]); |
| sql.affectedRows(X_13:int, X_15:lng); |
| end user.s54_1; |
| # optimizer.mitosis() |
| # optimizer.dataflow() |
| #inline actions= 0 time=1 usec |
| #remap actions= 0 time=31 usec |
| #costmodel actions= 1 time=3 usec |
| #coercion actions= 0 time=2 usec |
| #evaluate actions= 2 time=45 usec |
| #emptybind actions= 0 time=0 usec |
| #pushselect actions= 0 time=8 usec |
| #aliases actions= 2 time=8 usec |
| #mergetable actions= 0 time=63 usec |
| #deadcode actions= 0 time=12 usec |
| #aliases actions= 0 time=0 usec |
| #constants actions= 1 time=7 usec |
| #commonTerms actions= 0 time=7 usec |
| #projectionpath actions= 0 time=5 usec |
| #deadcode actions= 0 time=10 usec |
| #reorder actions= 1 time=41 usec |
| #matpack actions= 0 time=0 usec |
| #multiplex actions= 0 time=3 usec |
| #profiler actions=1 time=1 usec |
| #candidates actions=1 time=1 usec |
| #deadcode actions= 0 time=9 usec |
| #wlc actions= 0 time=1 usec |
| #garbagecollector actions= 1 time=88 usec |
| #total actions=26 time=579 usec |
+--------------------------------------------------------------------------------------------------+
35 tuples
sql>explain update t set a = 9 where b < 7;
+-------------------------------------------------------------------------------------------------------------+
| mal |
+=======================================================================
+======================================+
| function user.s56_1():void; |
| X_3:void := querylog.define("explain update t set a = 9 where b < 7;":str, "default_pipe":str, 21:int); |
| barrier X_87:bit := language.dataflow(); |
| X_6:int := sql.mvc(); |
| X_10:bat[:int] := sql.bind(X_6:int, "sys":str, "t":str, "b":str, 0:int); |
| C_7:bat[:oid] := sql.tid(X_6:int, "sys":str, "t":str); |
| C_22:bat[:oid] := algebra.thetaselect(X_10:bat[:int], C_7:bat[:oid], 7:int, "<":str); |
| X_24:bat[:int] := algebra.projection(C_22:bat[:oid], X_10:bat[:int]); |
| X_28:bat[:int] := algebra.project(X_24:bat[:int], 9:int); -- creating new "a" column with "9" |
| language.pass(X_10:bat[:int]); |
| exit X_87:bit; |
| X_30:int := sql.update(X_6:int, "sys":str, "t":str, "a":str, C_22:bat[:oid], X_28:bat[:int]); -- updating existing "a" column |
| X_32:lng := aggr.count(C_22:bat[:oid]); |
| sql.affectedRows(X_30:int, X_32:lng); |
| end user.s56_1; |
| #inline actions= 0 time=1 usec |
| #remap actions= 0 time=4 usec |
| #costmodel actions= 1 time=3 usec |
| #coercion actions= 0 time=3 usec |
| #evaluate actions= 4 time=58 usec |
| #emptybind actions= 1 time=19 usec |
| #pushselect actions= 3 time=25 usec |
| #aliases actions= 7 time=14 usec |
| #mergetable actions= 0 time=69 usec |
| #deadcode actions= 2 time=14 usec |
| #aliases actions= 0 time=0 usec |
| #constants actions= 2 time=11 usec |
| #commonTerms actions= 0 time=9 usec |
| #projectionpath actions= 0 time=8 usec |
| #deadcode actions= 0 time=12 usec |
| #reorder actions= 1 time=58 usec |
| #matpack actions= 0 time=1 usec |
| #dataflow actions= 1 time=45 usec |
| #multiplex actions= 0 time=6 usec |
| #profiler actions=1 time=2 usec |
| #candidates actions=1 time=2 usec |
| #deadcode actions= 0 time=19 usec |
| #wlc actions= 0 time=1 usec |
| #garbagecollector actions= 1 time=180 usec |
| #total actions=28 time=912 usec |
+-------------------------------------------------------------------------------------------------------------+
40 tuples
sql>select * from sys.malfunctions() where "module" = 'algebra' and
sql>"function" = 'project';
+---------+----------+--------------------------------------------------------------+-----------------+-------------------------------+
| module | function | signature | address | comment |
+=========+==========+==================================================
+============+=================+===============================+
| algebra | project | pattern algebra.project(b:bat[:any_1],
| v:any_3):bat[:any_3] | ALGprojecttail; | Fill the tail with a
| constant |
+---------+----------+--------------------------------------------------------------+-----------------+-------------------------------+
1 tuple
sql>select * from sys.malfunctions() where "module" = 'sql' and
sql>"function" = 'update';
+--------+----------+--------------------------------------------------------------------------------------+------------------+--------------------------------------------------------------------------------------------+
| module | function | signature | address | comment |
+========+==========+===================================================
+===================================+==================+================
+=======================================================================
+=====+
| sql | update | pattern sql.update(mvc:int, sname:str, tname:str, cname:str, rids:any, upd:any):int | mvc_update_wrap; | Update the values of the column tname.cname. Returns sequence number for order dependence) |
+--------+----------+--------------------------------------------------------------------------------------+------------------+--------------------------------------------------------------------------------------------+
1 tuple
sql>
========
----- On Jul 10, 2018, at 3:44 PM, Schediwy, Thomas (itsc) Thomas.Schediwy@ITSC.de wrote:
Hi there,
I am looking for the function where I can manipulate the values from
an insert or update statement.
For now, I am using a Solaris 10 system. But I havent found the
functions where the insert or update will get done. I have looked
trogh the files stream.c, mapi.c and mclient.c. But everytime I tried
to change the values in the variables nothing happend.
Can somebody please help me?
Thanks in advise
Thomas
_______________________________________________
developers-list mailing list
developers-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/developers-list
--
| Stefan.Manegold@CWI.nl | DB Architectures (DA) |
| www.CWI.nl/~manegold/ | Science Park 123 (L321) |
| +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
_______________________________________________
developers-list mailing list
developers-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/developers-list
