Changeset: 790e4080ac17 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=790e4080ac17
Added Files:
sql/test/emptydb-upgrade-chain-hge/Tests/check.SQL.py.src
sql/test/emptydb-upgrade-chain/Tests/check.SQL.py.src
sql/test/emptydb-upgrade-hge/Tests/check.SQL.py.src
sql/test/emptydb-upgrade/Tests/check.SQL.py.src
sql/test/emptydb/Tests/check.SQL.py.src
Removed Files:
sql/test/emptydb-upgrade-chain-hge/Tests/check.SQL.py
sql/test/emptydb-upgrade-chain/Tests/check.SQL.py
sql/test/emptydb-upgrade-hge/Tests/check.SQL.py
sql/test/emptydb-upgrade/Tests/check.SQL.py
sql/test/emptydb/Tests/check.SQL.py
Modified Files:
clients/Tests/SQL-dump.SQL.py
clients/Tests/SQL-dump.stable.out
clients/Tests/SQL-dump.stable.out.int128
sql/test/emptydb-upgrade-chain-hge/Tests/check.stable.out.int128
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain/Tests/check.stable.out
sql/test/emptydb-upgrade-chain/Tests/check.stable.out.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-hge/Tests/check.stable.out.int128
sql/test/emptydb-upgrade/Tests/check.stable.out
sql/test/emptydb-upgrade/Tests/check.stable.out.int128
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.int128
sql/test/emptydb/updatetests
Branch: Jun2016
Log Message:
Merge with Jul2015 branch.
diffs (truncated from 2994 to 300 lines):
diff --git a/clients/Tests/SQL-dump.SQL.py b/clients/Tests/SQL-dump.SQL.py
--- a/clients/Tests/SQL-dump.SQL.py
+++ b/clients/Tests/SQL-dump.SQL.py
@@ -62,14 +62,16 @@ sys.stderr.write(err)
# they are too volatile, and if it makes sense, dump an identifier
# from a referenced table
out = '''
+-- helper function
+create function pcre_replace(origin string, pat string, repl string, flags
string) returns string external name pcre.replace;
-- schemas
select name, authorization, owner, system from sys.schemas order by name;
-- _tables
-select s.name, t.name, t.query, t.type, t.system, t.commit_action, t.access
from sys._tables t left outer join sys.schemas s on t.schema_id = s.id order by
s.name, t.name;
+select s.name, t.name,
replace(replace(pcre_replace(pcre_replace(pcre_replace(t.query, '--.*\n', '',
''), '[ \t\n]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )', ')') as query,
t.type, t.system, t.commit_action, t.access from sys._tables t left outer join
sys.schemas s on t.schema_id = s.id order by s.name, t.name;
-- _columns
select t.name, c.name, c.type, c.type_digits, c.type_scale, c."default",
c."null", c.number, c.storage from sys._tables t, sys._columns c where t.id =
c.table_id order by t.name, c.number;
-- functions
-select s.name, f.name, f.func, f.mod, f.language, f.type, f.side_effect,
f.varres, f.vararg from sys.functions f left outer join sys.schemas s on
f.schema_id = s.id order by s.name, f.name, f.func;
+select s.name, f.name,
replace(replace(pcre_replace(pcre_replace(pcre_replace(f.func, '--.*\n', '',
''), '[ \t\n]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )', ')') as query,
f.mod, f.language, f.type, f.side_effect, f.varres, f.vararg from sys.functions
f left outer join sys.schemas s on f.schema_id = s.id order by s.name, f.name,
query;
-- args
select f.name, a.name, a.type, a.type_digits, a.type_scale, a.inout, a.number
from sys.args a left outer join sys.functions f on a.func_id = f.id order by
f.name, a.func_id, a.number;
-- auths
@@ -113,6 +115,8 @@ select keyword from sys.keywords order b
select table_type_id, table_type_name from sys.table_types order by
table_type_id, table_type_name;
-- dependency_types
select dependency_type_id, dependency_type_name from sys.dependency_types
order by dependency_type_id, dependency_type_name;
+-- drop helper function
+drop function pcre_replace(string, string, string, string);
'''
sys.stdout.write(out)
@@ -122,10 +126,5 @@ clt = process.client('sql', interactive
out, err = clt.communicate(out)
-# do some normalization of the output:
-# remove SQL comments, collapse multiple white space into a single space
-out = re.sub(r'--.*?(?:\\n)+', '', out)
-out = re.sub(r'(?:\\n|\\t| )+', ' ', out)
-
sys.stdout.write(out)
sys.stderr.write(err)
diff --git a/clients/Tests/SQL-dump.stable.out
b/clients/Tests/SQL-dump.stable.out
--- a/clients/Tests/SQL-dump.stable.out
+++ b/clients/Tests/SQL-dump.stable.out
@@ -1224,14 +1224,20 @@ create function zorder_decode_x(z oid) r
create function zorder_decode_y(z oid) returns integer external name
zorder.decode_y;
create function zorder_encode(x integer, y integer) returns oid external name
zorder.encode;
+-- helper function
+create function pcre_replace(origin string, pat string, repl string, flags
string) returns string external name pcre.replace;
-- schemas
select name, authorization, owner, system from sys.schemas order by name;
-- _tables
-select s.name, t.name, t.query, t.type, t.system, t.commit_action, t.access
from sys._tables t left outer join sys.schemas s on t.schema_id = s.id order by
s.name, t.name;
+select s.name, t.name,
replace(replace(pcre_replace(pcre_replace(pcre_replace(t.query, '--.*
+', '', ''), '[
+]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )', ')') as query, t.type,
t.system, t.commit_action, t.access from sys._tables t left outer join
sys.schemas s on t.schema_id = s.id order by s.name, t.name;
-- _columns
select t.name, c.name, c.type, c.type_digits, c.type_scale, c."default",
c."null", c.number, c.storage from sys._tables t, sys._columns c where t.id =
c.table_id order by t.name, c.number;
-- functions
-select s.name, f.name, f.func, f.mod, f.language, f.type, f.side_effect,
f.varres, f.vararg from sys.functions f left outer join sys.schemas s on
f.schema_id = s.id order by s.name, f.name, f.func;
+select s.name, f.name,
replace(replace(pcre_replace(pcre_replace(pcre_replace(f.func, '--.*
+', '', ''), '[
+]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )', ')') as query, f.mod,
f.language, f.type, f.side_effect, f.varres, f.vararg from sys.functions f left
outer join sys.schemas s on f.schema_id = s.id order by s.name, f.name, query;
-- args
select f.name, a.name, a.type, a.type_digits, a.type_scale, a.inout, a.number
from sys.args a left outer join sys.functions f on a.func_id = f.id order by
f.name, a.func_id, a.number;
-- auths
@@ -1275,6 +1281,9 @@ select keyword from sys.keywords order b
select table_type_id, table_type_name from sys.table_types order by
table_type_id, table_type_name;
-- dependency_types
select dependency_type_id, dependency_type_name from sys.dependency_types
order by dependency_type_id, dependency_type_name;
+-- drop helper function
+drop function pcre_replace(string, string, string, string);
+#create function pcre_replace(origin string, pat string, repl string, flags
string) returns string external name pcre.replace;
#select name, authorization, owner, system from sys.schemas order by name;
% sys.schemas, sys.schemas, sys.schemas, sys.schemas # table_name
% name, authorization, owner, system # name
@@ -1285,11 +1294,13 @@ select dependency_type_id, dependency_ty
[ "profiler", 3, 3, true ]
[ "sys", 2, 3, true ]
[ "tmp", 2, 3, true ]
-#select s.name, t.name, t.query, t.type, t.system, t.commit_action, t.access
from sys._tables t left outer join sys.schemas s on t.schema_id = s.id order by
s.name, t.name;
-% .s, .t, .t, .t, .t, .t, .t # table_name
+#select s.name, t.name,
replace(replace(pcre_replace(pcre_replace(pcre_replace(t.query, '--.*
+#', '', ''), '[
+#]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )', ')') as query, t.type,
t.system, t.commit_action, t.access from sys._tables t left outer join
sys.schemas s on t.schema_id = s.id order by s.name, t.name;
+% .s, .t, .L, .t, .t, .t, .t # table_name
% name, name, query, type, system, commit_action, access # name
% varchar, varchar, varchar, smallint, boolean,
smallint, smallint # type
-% 3, 17, 613, 1, 5, 1, 1 # length
+% 3, 17, 597, 1, 5, 1, 1 # length
[ "bam", "export", NULL, 0, true, 0, 0 ]
[ "bam", "files", NULL, 0, true, 0, 0 ]
[ "bam", "pg", NULL, 0, true, 0, 0 ]
@@ -1306,7 +1317,7 @@ select dependency_type_id, dependency_ty
[ "sys", "dependency_types", NULL, 0, true, 0, 0
]
[ "sys", "environment", "create view sys.environment as select * from
sys.environment();", 1, true, 0, 0 ]
[ "sys", "functions", NULL, 0, true, 0, 0 ]
-[ "sys", "geometry_columns", "create view geometry_columns as select
e.value as f_table_catalog, s.name as f_table_schema, y.f_table_name,
y.f_geometry_column, y.coord_dimension, y.srid, y.type from schemas s,
environment e, ( select t.schema_id, t.name as f_table_name, x.name as
f_geometry_column, has_z(info)+has_m(info)+2 as coord_dimension, srid,
get_type(info, 0) as type from tables t, ( select name, table_id, type_digits
as info, type_scale as srid from columns where type in ( select distinct
sqlname from types where systemname='wkb') ) as x where t.id=x.table_id ) y
where y.schema_id=s.id and e.name='gdk_dbname';", 1, true, 0, 0
]
+[ "sys", "geometry_columns", "create view geometry_columns as select
e.value as f_table_catalog, s.name as f_table_schema, y.f_table_name,
y.f_geometry_column, y.coord_dimension, y.srid, y.type from schemas s,
environment e, (select t.schema_id, t.name as f_table_name, x.name as
f_geometry_column, has_z(info)+has_m(info)+2 as coord_dimension, srid,
get_type(info, 0) as type from tables t, (select name, table_id, type_digits as
info, type_scale as srid from columns where type in (select distinct sqlname
from types where systemname='wkb')) as x where t.id=x.table_id) y where
y.schema_id=s.id and e.name='gdk_dbname';", 1, true, 0, 0
]
[ "sys", "idxs", NULL, 0, true, 0, 0 ]
[ "sys", "keys", NULL, 0, true, 0, 0 ]
[ "sys", "keywords", NULL, 0, true, 0, 0 ]
@@ -1724,11 +1735,13 @@ select dependency_type_id, dependency_ty
[ "users", "name", "varchar", 1024, 0, NULL, true, 0,
NULL ]
[ "users", "fullname", "varchar", 2024, 0, NULL, true,
1, NULL ]
[ "users", "default_schema", "int", 9, 0, NULL, true,
2, NULL ]
-#select s.name, f.name, f.func, f.mod, f.language, f.type, f.side_effect,
f.varres, f.vararg from sys.functions f left outer join sys.schemas s on
f.schema_id = s.id order by s.name, f.name, f.func;
-% .s, .f, .f, .f, .f, .f, .f, .f, .f # table_name
-% name, name, func, mod, language, type, side_effect,
varres, vararg # name
+#select s.name, f.name,
replace(replace(pcre_replace(pcre_replace(pcre_replace(f.func, '--.*
+#', '', ''), '[
+#]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )', ')') as query, f.mod,
f.language, f.type, f.side_effect, f.varres, f.vararg from sys.functions f left
outer join sys.schemas s on f.schema_id = s.id order by s.name, f.name, query;
+% .s, .f, .L, .f, .f, .f, .f, .f, .f # table_name
+% name, name, query, mod, language, type, side_effect,
varres, vararg # name
% varchar, varchar, varchar, varchar, int, int,
boolean, boolean, boolean # type
-% 8, 35, 816, 9, 1, 1, 5, 5, 5 # length
+% 8, 35, 794, 9, 1, 1, 5, 5, 5 # length
[ "bam", "bam_drop_file", "create procedure
bam.bam_drop_file(file_id bigint, dbschema smallint) external name
bam.bam_drop_file;", "bam", 1, 2, true, false, false ]
[ "bam", "bam_export", "create procedure bam.bam_export(output_path
string) external name bam.bam_export;", "bam", 1, 2, true,
false, false ]
[ "bam", "bam_flag", "create function bam.bam_flag(flag smallint,
name string) returns boolean external name bam.bam_flag;", "bam", 1, 1,
false, false, false ]
@@ -1759,8 +1772,8 @@ select dependency_type_id, dependency_ty
[ "json", "text", "create function json.text(js json) returns string
external name json.text;", "json", 1, 1, false, false, false ]
[ "json", "text", "create function json.text(js json, e string) returns
string external name json.text;", "json", 1, 1, false, false, false
]
[ "json", "text", "create function json.text(js string) returns string
external name json.text;", "json", 1, 1, false, false, false ]
-[ "json", "tojsonarray", "create aggregate json.tojsonarray( x double )
returns string external name aggr.jsonaggr;", "aggr", 1, 3, false,
false, false ]
-[ "json", "tojsonarray", "create aggregate json.tojsonarray( x string )
returns string external name aggr.jsonaggr;", "aggr", 1, 3, false,
false, false ]
+[ "json", "tojsonarray", "create aggregate json.tojsonarray(x double)
returns string external name aggr.jsonaggr;", "aggr", 1, 3,
false, false, false ]
+[ "json", "tojsonarray", "create aggregate json.tojsonarray(x string)
returns string external name aggr.jsonaggr;", "aggr", 1, 3,
false, false, false ]
[ "json", "valuearray", "create function json.valuearray(js json)
returns json external name json.valuearray;", "json", 1, 1, false,
false, false ]
[ "profiler", "setheartbeat", "create procedure profiler.setheartbeat(beat
int) external name profiler.setheartbeat;", "profiler", 1, 2,
true, false, false ]
[ "profiler", "setpoolsize", "create procedure profiler.setpoolsize(poolsize
int) external name profiler.setpoolsize;", "profiler", 1, 2,
true, false, false ]
@@ -1904,7 +1917,7 @@ select dependency_type_id, dependency_ty
[ "sys", "dayofmonth", "day", "mtime", 0, 1, false,
false, false ]
[ "sys", "dayofweek", "dayofweek", "mtime", 0, 1,
false, false, false ]
[ "sys", "dayofyear", "dayofyear", "mtime", 0, 1,
false, false, false ]
-[ "sys", "db_users", "CREATE FUNCTION db_users () RETURNS TABLE(
name varchar(2048)) EXTERNAL NAME sql.db_users;", "sql", 2, 5,
false, false, false ]
+[ "sys", "db_users", "CREATE FUNCTION db_users () RETURNS TABLE(name
varchar(2048)) EXTERNAL NAME sql.db_users;", "sql", 2, 5, false,
false, false ]
[ "sys", "debug", "create function sys.debug(debug int) returns
integer external name mdb.\"setDebug\";", "mdb", 1, 1, false,
false, false ]
[ "sys", "degrees", "create function degrees(r double) returns
double return r*180/pi();", "user", 2, 1, false, false, false ]
[ "sys", "dense_rank", "dense_rank", "sql", 0, 6, false,
false, false ]
@@ -1936,13 +1949,13 @@ select dependency_type_id, dependency_ty
[ "sys", "editdistance2", "editdistance2", "txtsim",
0, 1, false, false, false ]
[ "sys", "editdistance2", "editdistance2", "txtsim",
0, 1, false, false, false ]
[ "sys", "editdistance2", "editdistance2", "txtsim",
0, 1, false, false, false ]
-[ "sys", "env", "CREATE FUNCTION env () RETURNS TABLE( name
varchar(1024), value varchar(2048)) EXTERNAL NAME sql.sql_environment;",
"sql", 2, 5, false, false, false ]
+[ "sys", "env", "CREATE FUNCTION env () RETURNS TABLE(name
varchar(1024), value varchar(2048)) EXTERNAL NAME sql.sql_environment;",
"sql", 2, 5, false, false, false ]
[ "sys", "environment", "create function sys.environment() returns
table (\"name\" string, value string) external name sql.sql_environment;",
"sql", 1, 5, false, false, false ]
[ "sys", "epoch", "create function sys.\"epoch\"(sec bigint)
returns timestamp external name timestamp.\"epoch\";", "timestamp", 1,
1, false, false, false ]
[ "sys", "epoch", "create function sys.\"epoch\"(sec int) returns
timestamp external name timestamp.\"epoch\";", "timestamp", 1, 1,
false, false, false ]
[ "sys", "epoch", "create function sys.\"epoch\"(ts timestamp
with time zone) returns int external name timestamp.\"epoch\";",
"timestamp", 1, 1, false, false, false ]
[ "sys", "epoch", "create function sys.\"epoch\"(ts timestamp)
returns int external name timestamp.\"epoch\";", "timestamp", 1, 1,
false, false, false ]
-[ "sys", "evalalgebra", "create procedure sys.evalalgebra( ra_stmt
string, opt bool) external name sql.\"evalAlgebra\";", "sql", 1, 2,
true, false, false ]
+[ "sys", "evalalgebra", "create procedure sys.evalalgebra(ra_stmt
string, opt bool) external name sql.\"evalAlgebra\";", "sql", 1,
2, true, false, false ]
[ "sys", "exist", "exist", "aggr", 0, 3, false,
false, false ]
[ "sys", "exp", "exp", "mmath", 0, 1, false, false,
false ]
[ "sys", "exp", "exp", "mmath", 0, 1, false, false,
false ]
@@ -1969,7 +1982,7 @@ select dependency_type_id, dependency_ty
[ "sys", "geometrytype", "create function geometrytype(geom geometry)
returns string external name geom.\"GeometryType1\";", "geom", 1, 1,
false, false, false ]
[ "sys", "get_type", "create function get_type(info integer, format
integer) returns string external name geom.\"getType\";", "geom", 1,
1, false, false, false ]
[ "sys", "get_value_for", "get_value", "sql", 0, 1,
false, false, false ]
-[ "sys", "getanchor", "create function getanchor( theurl url )
returns string external name url.\"getAnchor\";", "url", 1, 1,
false, false, false ]
+[ "sys", "getanchor", "create function getanchor(theurl url) returns
string external name url.\"getAnchor\";", "url", 1, 1, false,
false, false ]
[ "sys", "getbasename", "create function getbasename(theurl url)
returns string external name url.\"getBasename\";", "url", 1, 1,
false, false, false ]
[ "sys", "getcontent", "create function getcontent(theurl url) returns
string external name url.\"getContent\";", "url", 1, 1, false,
false, false ]
[ "sys", "getcontext", "create function getcontext(theurl url) returns
string external name url.\"getContext\";", "url", 1, 1, false,
false, false ]
@@ -2005,7 +2018,7 @@ select dependency_type_id, dependency_ty
[ "sys", "ilike", "ilike", "algebra", 0, 1,
false, false, false ]
[ "sys", "ilike", "ilike", "algebra", 0, 1,
false, false, false ]
[ "sys", "ilike", "ilike", "algebra", 0, 1,
false, false, false ]
-[ "sys", "imprintsize", "create function sys.imprintsize(i bigint, nme
string) returns bigint begin if nme = 'boolean' or nme = 'tinyint' or nme =
'smallint' or nme = 'int' or nme = 'bigint' or nme = 'hugeint' or nme =
'decimal' or nme = 'date' or nme = 'timestamp' or nme = 'real' or nme =
'double' then return cast( i * 0.12 as bigint); end if ; return 0; end;",
"user", 2, 1, false, false, false ]
+[ "sys", "imprintsize", "create function sys.imprintsize(i bigint, nme
string) returns bigint begin if nme = 'boolean' or nme = 'tinyint' or nme =
'smallint' or nme = 'int' or nme = 'bigint' or nme = 'hugeint' or nme =
'decimal' or nme = 'date' or nme = 'timestamp' or nme = 'real' or nme =
'double' then return cast(i * 0.12 as bigint); end if ; return 0; end;",
"user", 2, 1, false, false, false ]
[ "sys", "in", "in", "calc", 0, 1, false, false, false
]
[ "sys", "index", "index", "calc", 0, 1, false,
false, false ]
[ "sys", "index", "index", "calc", 0, 1, false,
false, false ]
@@ -2157,7 +2170,7 @@ select dependency_type_id, dependency_ty
[ "sys", "month", "month", "mtime", 0, 1,
false, false, false ]
[ "sys", "ms_round", "create function ms_round(num double, prc int,
truncat int) returns double begin if (truncat = 0) then return round(num, prc);
else return ms_trunc(num, prc); end if; end;", "user", 2, 1, false,
false, false ]
[ "sys", "ms_str", "create function ms_str(num float, prc int,
truncat int) returns string begin return cast(num as string); end;",
"user", 2, 1, false, false, false ]
-[ "sys", "ms_stuff", "create function ms_stuff( s1 varchar(32), st
int, len int, s3 varchar(32)) returns varchar(32) begin declare res
varchar(32), aux varchar(32); declare ofset int; if ( st < 0 or st >
length(s1)) then return ''; end if; set ofset = 1; set res =
substring(s1,ofset,st-1); set res = res || s3; set ofset = st + len; set aux =
substring(s1,ofset,length(s1)-ofset+1); set res = res || aux; return res;
end;", "user", 2, 1, false, false, false ]
+[ "sys", "ms_stuff", "create function ms_stuff(s1 varchar(32), st
int, len int, s3 varchar(32)) returns varchar(32) begin declare res
varchar(32), aux varchar(32); declare ofset int; if (st < 0 or st > length(s1))
then return ''; end if; set ofset = 1; set res = substring(s1,ofset,st-1); set
res = res || s3; set ofset = st + len; set aux =
substring(s1,ofset,length(s1)-ofset+1); set res = res || aux; return res;
end;", "user", 2, 1, false, false, false ]
[ "sys", "ms_trunc", "create function ms_trunc(num double, prc int)
returns double external name sql.ms_trunc;", "sql", 1, 1, false,
false, false ]
[ "sys", "netcdf_attach", "create procedure netcdf_attach(fname
varchar(256)) external name netcdf.attach;", "netcdf", 1, 2,
true, false, false ]
[ "sys", "netcdf_importvar", "create procedure netcdf_importvar(fid
integer, varnname varchar(256)) external name netcdf.importvariable;",
"netcdf", 1, 2, true, false, false ]
@@ -2195,6 +2208,7 @@ select dependency_type_id, dependency_ty
[ "sys", "patindex", "patindex", "pcre", 0, 1, false,
false, false ]
[ "sys", "pause", "create procedure sys.pause(tag bigint)
external name sql.sysmon_pause;", "sql", 1, 2, true, false,
false ]
[ "sys", "pause", "create procedure sys.pause(tag int) external
name sql.sysmon_pause;", "sql", 1, 2, true, false, false ]
+[ "sys", "pcre_replace", "create function pcre_replace(origin string,
pat string, repl string, flags string) returns string external name
pcre.replace;", "pcre", 1, 1, false, false, false ]
[ "sys", "pi", "pi", "mmath", 0, 1, false, false,
false ]
[ "sys", "power", "pow", "mmath", 0, 1, false,
false, false ]
[ "sys", "power", "pow", "mmath", 0, 1, false,
false, false ]
@@ -2224,18 +2238,18 @@ select dependency_type_id, dependency_ty
[ "sys", "quantile", "create aggregate quantile(val wrd, q double)
returns wrd external name \"aggr\".\"quantile\";", "aggr", 1, 3,
false, false, false ]
[ "sys", "querycache", "create function sys.querycache() returns table
(query string, count int) external name sql.dump_cache;", "sql", 1,
5, false, false, false ]
[ "sys", "querylog", "create procedure sys.querylog(filename string)
external name sql.logfile;", "sql", 1, 2, true, false, false
]
-[ "sys", "querylog_calls", "create function sys.querylog_calls()
returns table( id oid, \"start\" timestamp, \"stop\" timestamp, arguments
string, tuples wrd, run bigint, ship bigint, cpu int, io int ) external name
sql.querylog_calls;", "sql", 1, 5, false, false, false ]
-[ "sys", "querylog_catalog", "create function sys.querylog_catalog()
returns table( id oid, owner string, defined timestamp, query string, pipe
string, \"plan\" string, mal int, optimize bigint ) external name
sql.querylog_catalog;", "sql", 1, 5, false, false, false ]
+[ "sys", "querylog_calls", "create function sys.querylog_calls()
returns table(id oid, \"start\" timestamp, \"stop\" timestamp, arguments
string, tuples wrd, run bigint, ship bigint, cpu int, io int) external name
sql.querylog_calls;", "sql", 1, 5, false, false, false ]
+[ "sys", "querylog_catalog", "create function sys.querylog_catalog()
returns table(id oid, owner string, defined timestamp, query string, pipe
string, \"plan\" string, mal int, optimize bigint) external name
sql.querylog_catalog;", "sql", 1, 5, false, false, false ]
[ "sys", "querylog_disable", "create procedure
sys.querylog_disable() external name sql.querylog_disable;", "sql", 1,
2, true, false, false ]
[ "sys", "querylog_empty", "create procedure sys.querylog_empty()
external name sql.querylog_empty;", "sql", 1, 2, true, false,
false ]
[ "sys", "querylog_enable", "create procedure sys.querylog_enable()
external name sql.querylog_enable;", "sql", 1, 2, true, false,
false ]
[ "sys", "querylog_enable", "create procedure
sys.querylog_enable(threshold smallint) external name
sql.querylog_enable_threshold;", "sql", 1, 2, true, false,
false ]
-[ "sys", "queue", "create function sys.queue() returns table(
qtag bigint, \"user\" string, started timestamp, estimate timestamp, progress
int, status string, tag oid, query string ) external name sql.sysmon_queue;",
"sql", 1, 5, false, false, false ]
+[ "sys", "queue", "create function sys.queue() returns table(qtag
bigint, \"user\" string, started timestamp, estimate timestamp, progress int,
status string, tag oid, query string) external name sql.sysmon_queue;",
"sql", 1, 5, false, false, false ]
[ "sys", "radians", "create function radians(d double) returns
double return d*pi()/180;", "user", 2, 1, false, false, false ]
[ "sys", "rand", "rand", "mmath", 0, 1, true, false,
false ]
[ "sys", "rand", "sqlrand", "mmath", 0, 1, true,
false, false ]
[ "sys", "rank", "rank", "sql", 0, 6, false, false, false
]
-[ "sys", "rejects", "create function sys.rejects() returns table(
rowid bigint, fldid int, \"message\" string, \"input\" string ) external name
sql.copy_rejects;", "sql", 1, 5, false, false, false ]
+[ "sys", "rejects", "create function sys.rejects() returns
table(rowid bigint, fldid int, \"message\" string, \"input\" string) external
name sql.copy_rejects;", "sql", 1, 5, false, false, false ]
[ "sys", "repeat", "repeat", "str", 0, 1, false,
false, false ]
[ "sys", "repeat", "repeat", "str", 0, 1, false,
false, false ]
[ "sys", "repeat", "repeat", "str", 0, 1, false,
false, false ]
@@ -3262,12 +3276,12 @@ select dependency_type_id, dependency_ty
[ "sys", "stddev_samp", "create aggregate stddev_samp(val wrd) returns
double external name \"aggr\".\"stdev\";", "aggr", 1, 3, false,
false, false ]
[ "sys", "stop", "create procedure sys.stop(tag bigint) external name
sql.sysmon_stop;", "sql", 1, 2, true, false, false ]
[ "sys", "stop", "create procedure sys.stop(tag int) external name
sql.sysmon_stop;", "sql", 1, 2, true, false, false ]
-[ "sys", "storage", "create function sys.\"storage\"( sname string)
returns table ( \"schema\" string, \"table\" string, \"column\" string,
\"type\" string, \"mode\" string, location string, \"count\" bigint, typewidth
int, columnsize bigint, heapsize bigint, hashes bigint, phash boolean, imprints
bigint, sorted boolean ) external name sql.\"storage\";", "sql", 1,
5, false, false, false ]
-[ "sys", "storage", "create function sys.\"storage\"( sname string,
tname string) returns table ( \"schema\" string, \"table\" string, \"column\"
string, \"type\" string, \"mode\" string, location string, \"count\" bigint,
typewidth int, columnsize bigint, heapsize bigint, hashes bigint, phash
boolean, imprints bigint, sorted boolean ) external name sql.\"storage\";",
"sql", 1, 5, false, false, false ]
-[ "sys", "storage", "create function sys.\"storage\"( sname string,
tname string, cname string) returns table ( \"schema\" string, \"table\"
string, \"column\" string, \"type\" string, \"mode\" string, location string,
\"count\" bigint, typewidth int, columnsize bigint, heapsize bigint, hashes
bigint, phash boolean, imprints bigint, sorted boolean ) external name
sql.\"storage\";", "sql", 1, 5, false, false, false ]
-[ "sys", "storage", "create function sys.\"storage\"() returns
table ( \"schema\" string, \"table\" string, \"column\" string, \"type\"
string, \"mode\" string, location string, \"count\" bigint, typewidth int,
columnsize bigint, heapsize bigint, hashes bigint, phash boolean, imprints
bigint, sorted boolean ) external name sql.\"storage\";", "sql", 1,
5, false, false, false ]
-[ "sys", "storagemodel", "create function sys.storagemodel() returns
table ( \"schema\" string, \"table\" string, \"column\" string, \"type\"
string, \"count\" bigint, columnsize bigint, heapsize bigint, hashes bigint,
imprints bigint, sorted boolean) begin return select i.\"schema\", i.\"table\",
i.\"column\", i.\"type\", i.\"count\", columnsize(i.\"type\", i.count,
i.\"distinct\"), heapsize(i.\"type\", i.\"distinct\", i.\"atomwidth\"),
hashsize(i.\"reference\", i.\"count\"), imprintsize(i.\"count\",i.\"type\"),
i.sorted from sys.storagemodelinput i; end;", "user", 2, 5,
false, false, false ]
-[ "sys", "storagemodelinit", "create procedure
sys.storagemodelinit() begin delete from sys.storagemodelinput; insert into
sys.storagemodelinput select x.\"schema\", x.\"table\", x.\"column\",
x.\"type\", x.typewidth, x.count, 0, x.typewidth, false, x.sorted from
sys.\"storage\"() x; update sys.storagemodelinput set reference = true where
concat(concat(\"schema\",\"table\"), \"column\") in ( select concat(
concat(\"fkschema\".\"name\", \"fktable\".\"name\"), \"fkkeycol\".\"name\" )
from \"sys\".\"keys\" as \"fkkey\", \"sys\".\"objects\" as \"fkkeycol\",
\"sys\".\"tables\" as \"fktable\", \"sys\".\"schemas\" as \"fkschema\" where
\"fktable\".\"id\" = \"fkkey\".\"table_id\" and \"fkkey\".\"id\" =
\"fkkeycol\".\"id\" and \"fkschema\".\"id\" = \"fktable\".\"schema_id\" and
\"fkkey\".\"rkey\" > -1); update sys.storagemodelinput set \"distinct\" =
\"count\" where \"type\" = 'varchar' or \"type\"='clob'; end;", "user",
2, 2, true, false, false ]
+[ "sys", "storage", "create function sys.\"storage\"() returns
table (\"schema\" string, \"table\" string, \"column\" string, \"type\" string,
\"mode\" string, location string, \"count\" bigint, typewidth int, columnsize
bigint, heapsize bigint, hashes bigint, phash boolean, imprints bigint, sorted
boolean) external name sql.\"storage\";", "sql", 1, 5, false,
false, false ]
+[ "sys", "storage", "create function sys.\"storage\"(sname string)
returns table (\"schema\" string, \"table\" string, \"column\" string, \"type\"
string, \"mode\" string, location string, \"count\" bigint, typewidth int,
columnsize bigint, heapsize bigint, hashes bigint, phash boolean, imprints
bigint, sorted boolean) external name sql.\"storage\";", "sql", 1, 5,
false, false, false ]
+[ "sys", "storage", "create function sys.\"storage\"(sname string,
tname string) returns table (\"schema\" string, \"table\" string, \"column\"
string, \"type\" string, \"mode\" string, location string, \"count\" bigint,
typewidth int, columnsize bigint, heapsize bigint, hashes bigint, phash
boolean, imprints bigint, sorted boolean) external name sql.\"storage\";",
"sql", 1, 5, false, false, false ]
+[ "sys", "storage", "create function sys.\"storage\"(sname string,
tname string, cname string) returns table (\"schema\" string, \"table\" string,
\"column\" string, \"type\" string, \"mode\" string, location string, \"count\"
bigint, typewidth int, columnsize bigint, heapsize bigint, hashes bigint, phash
boolean, imprints bigint, sorted boolean) external name sql.\"storage\";",
"sql", 1, 5, false, false, false ]
+[ "sys", "storagemodel", "create function sys.storagemodel() returns
table (\"schema\" string, \"table\" string, \"column\" string, \"type\" string,
\"count\" bigint, columnsize bigint, heapsize bigint, hashes bigint, imprints
bigint, sorted boolean) begin return select i.\"schema\", i.\"table\",
i.\"column\", i.\"type\", i.\"count\", columnsize(i.\"type\", i.count,
i.\"distinct\"), heapsize(i.\"type\", i.\"distinct\", i.\"atomwidth\"),
hashsize(i.\"reference\", i.\"count\"), imprintsize(i.\"count\",i.\"type\"),
i.sorted from sys.storagemodelinput i; end;", "user", 2, 5,
false, false, false ]
+[ "sys", "storagemodelinit", "create procedure
sys.storagemodelinit() begin delete from sys.storagemodelinput; insert into
sys.storagemodelinput select x.\"schema\", x.\"table\", x.\"column\",
x.\"type\", x.typewidth, x.count, 0, x.typewidth, false, x.sorted from
sys.\"storage\"() x; update sys.storagemodelinput set reference = true where
concat(concat(\"schema\",\"table\"), \"column\") in (select
concat(concat(\"fkschema\".\"name\", \"fktable\".\"name\"),
\"fkkeycol\".\"name\") from \"sys\".\"keys\" as \"fkkey\", \"sys\".\"objects\"
as \"fkkeycol\", \"sys\".\"tables\" as \"fktable\", \"sys\".\"schemas\" as
\"fkschema\" where \"fktable\".\"id\" = \"fkkey\".\"table_id\" and
\"fkkey\".\"id\" = \"fkkeycol\".\"id\" and \"fkschema\".\"id\" =
\"fktable\".\"schema_id\" and \"fkkey\".\"rkey\" > -1); update
sys.storagemodelinput set \"distinct\" = \"count\" where \"type\" = 'varchar'
or \"type\"='clob'; end;", "user", 2, 2, true, false, false ]
[ "sys", "str_to_date", "create function str_to_date(s string, format
string) returns date external name mtime.\"str_to_date\";", "mtime",
1, 1, false, false, false ]
[ "sys", "str_to_time", "create function str_to_time(s string, format
string) returns time external name mtime.\"str_to_time\";", "mtime",
1, 1, false, false, false ]
[ "sys", "str_to_timestamp", "create function str_to_timestamp(s
string, format string) returns timestamp external name
mtime.\"str_to_timestamp\";", "mtime", 1, 1, false,
false, false ]
@@ -3307,7 +3321,7 @@ select dependency_type_id, dependency_ty
[ "sys", "time_to_str", "create function time_to_str(d time, format
string) returns string external name mtime.\"time_to_str\";", "mtime",
1, 1, false, false, false ]
[ "sys", "times", "create procedure times() external name
sql.times;", "sql", 1, 2, true, false, false ]
[ "sys", "timestamp_to_str", "create function timestamp_to_str(d
timestamp, format string) returns string external name
mtime.\"timestamp_to_str\";", "mtime", 1, 1, false,
false, false ]
-[ "sys", "tracelog", "create function sys.tracelog() returns table (
event integer, clk varchar(20), pc varchar(50), thread int, ticks bigint, rrsmb
bigint, vmmb bigint, reads bigint, writes bigint, minflt bigint, majflt bigint,
nvcsw bigint, stmt string ) external name sql.dump_trace;", "sql", 1,
5, false, false, false ]
+[ "sys", "tracelog", "create function sys.tracelog() returns table
(event integer, clk varchar(20), pc varchar(50), thread int, ticks bigint,
rrsmb bigint, vmmb bigint, reads bigint, writes bigint, minflt bigint, majflt
bigint, nvcsw bigint, stmt string) external name sql.dump_trace;", "sql",
1, 5, false, false, false ]
[ "sys", "trim", "trim", "str", 0, 1, false, false, false
]
[ "sys", "trim", "trim", "str", 0, 1, false, false, false
]
[ "sys", "trim", "trim", "str", 0, 1, false, false, false
]
@@ -3325,7 +3339,7 @@ select dependency_type_id, dependency_ty
[ "sys", "upper", "toUpper", "str", 0, 1, false,
false, false ]
[ "sys", "uuid", "create function sys.uuid() returns uuid external name
uuid.\"new\";", "uuid", 1, 1, false, false, false ]
[ "sys", "vacuum", "create procedure vacuum(sys string, tab
string) external name sql.vacuum;", "sql", 1, 2, true, false,
false ]
-[ "sys", "var", "CREATE FUNCTION var() RETURNS TABLE( name
varchar(1024)) EXTERNAL NAME sql.sql_variables;", "sql", 2, 5,
false, false, false ]
+[ "sys", "var", "CREATE FUNCTION var() RETURNS TABLE(name
varchar(1024)) EXTERNAL NAME sql.sql_variables;", "sql", 2, 5,
false, false, false ]
[ "sys", "var_pop", "create aggregate var_pop(val bigint) returns
double external name \"aggr\".\"variancep\";", "aggr", 1, 3,
false, false, false ]
[ "sys", "var_pop", "create aggregate var_pop(val date) returns
double external name \"aggr\".\"variancep\";", "aggr", 1, 3,
false, false, false ]
[ "sys", "var_pop", "create aggregate var_pop(val double) returns
double external name \"aggr\".\"variancep\";", "aggr", 1, 3,
false, false, false ]
@@ -4564,6 +4578,11 @@ select dependency_type_id, dependency_ty
[ "patindex", "arg_2", "clob", 0, 0, 1, 2 ]
[ "pause", "tag", "int", 32, 0, 1, 0 ]
[ "pause", "tag", "bigint", 64, 0, 1, 0 ]
+[ "pcre_replace", "result", "clob", 0, 0, 0, 0
]
+[ "pcre_replace", "origin", "clob", 0, 0, 1, 1
]
+[ "pcre_replace", "pat", "clob", 0, 0, 1, 2 ]
+[ "pcre_replace", "repl", "clob", 0, 0, 1, 3 ]
+[ "pcre_replace", "flags", "clob", 0, 0, 1, 4
]
[ "pi", "res_0", "double", 53, 0, 0, 0
]
[ "power", "res_0", "real", 24, 0, 0, 0 ]
[ "power", "arg_1", "real", 24, 0, 1, 1 ]
diff --git a/clients/Tests/SQL-dump.stable.out.int128
b/clients/Tests/SQL-dump.stable.out.int128
--- a/clients/Tests/SQL-dump.stable.out.int128
+++ b/clients/Tests/SQL-dump.stable.out.int128
@@ -1234,14 +1234,20 @@ create function zorder_decode_x(z oid) r
create function zorder_decode_y(z oid) returns integer external name
zorder.decode_y;
create function zorder_encode(x integer, y integer) returns oid external name
zorder.encode;
+-- helper function
+create function pcre_replace(origin string, pat string, repl string, flags
string) returns string external name pcre.replace;
-- schemas
select name, authorization, owner, system from sys.schemas order by name;
-- _tables
-select s.name, t.name, t.query, t.type, t.system, t.commit_action, t.access
from sys._tables t left outer join sys.schemas s on t.schema_id = s.id order by
s.name, t.name;
+select s.name, t.name,
replace(replace(pcre_replace(pcre_replace(pcre_replace(t.query, '--.*
+', '', ''), '[
+]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )', ')') as query, t.type,
t.system, t.commit_action, t.access from sys._tables t left outer join
sys.schemas s on t.schema_id = s.id order by s.name, t.name;
-- _columns
select t.name, c.name, c.type, c.type_digits, c.type_scale, c."default",
c."null", c.number, c.storage from sys._tables t, sys._columns c where t.id =
c.table_id order by t.name, c.number;
-- functions
-select s.name, f.name, f.func, f.mod, f.language, f.type, f.side_effect,
f.varres, f.vararg from sys.functions f left outer join sys.schemas s on
f.schema_id = s.id order by s.name, f.name, f.func;
+select s.name, f.name,
replace(replace(pcre_replace(pcre_replace(pcre_replace(f.func, '--.*
+', '', ''), '[
+]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )', ')') as query, f.mod,
f.language, f.type, f.side_effect, f.varres, f.vararg from sys.functions f left
outer join sys.schemas s on f.schema_id = s.id order by s.name, f.name, query;
-- args
select f.name, a.name, a.type, a.type_digits, a.type_scale, a.inout, a.number
from sys.args a left outer join sys.functions f on a.func_id = f.id order by
f.name, a.func_id, a.number;
-- auths
@@ -1285,6 +1291,9 @@ select keyword from sys.keywords order b
select table_type_id, table_type_name from sys.table_types order by
table_type_id, table_type_name;
-- dependency_types
select dependency_type_id, dependency_type_name from sys.dependency_types
order by dependency_type_id, dependency_type_name;
+-- drop helper function
+drop function pcre_replace(string, string, string, string);
+#create function pcre_replace(origin string, pat string, repl string, flags
string) returns string external name pcre.replace;
#select name, authorization, owner, system from sys.schemas order by name;
% sys.schemas, sys.schemas, sys.schemas, sys.schemas # table_name
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
