Changeset: fe679689c934 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=fe679689c934
Added Files:
sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.sql
sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.stable.err
sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.stable.out
Modified Files:
sql/test/sys-schema/Tests/All
Branch: Dec2016
Log Message:
Adding known referential integrity check test script for sys (and tmp) tables.
The foreign keys in sys.dependencies are complex as they depend on the value of
depend_type and may refer to multiple table per column!
This requires more analysis before they can be defined and added. See TODO
comments.
diffs (truncated from 593 to 300 lines):
diff --git a/sql/test/sys-schema/Tests/All b/sql/test/sys-schema/Tests/All
--- a/sql/test/sys-schema/Tests/All
+++ b/sql/test/sys-schema/Tests/All
@@ -1,2 +1,3 @@
check_PrimaryKey_uniqueness
check_AlternateKey_uniqueness
+check_ForeignKey_referential_integrity
diff --git
a/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.sql
b/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.sql
@@ -0,0 +1,123 @@
+-- check all standard sys (and tmp) tables on Referential integrity
+-- All queries should return NO rows (so no invalid references found).
+SELECT * FROM sys.schemas WHERE authorization NOT IN (SELECT id FROM
sys.auths);
+SELECT * FROM sys.schemas WHERE owner NOT IN (SELECT id FROM sys.auths);
+
+SELECT * FROM sys.tables WHERE schema_id NOT IN (SELECT id FROM sys.schemas);
+SELECT * FROM sys._tables WHERE schema_id NOT IN (SELECT id FROM sys.schemas);
+SELECT * FROM tmp._tables WHERE schema_id NOT IN (SELECT id FROM sys.schemas);
+SELECT * FROM sys.tables WHERE type NOT IN (SELECT table_type_id FROM
sys.table_types);
+SELECT * FROM sys._tables WHERE type NOT IN (SELECT table_type_id FROM
sys.table_types);
+SELECT * FROM tmp._tables WHERE type NOT IN (SELECT table_type_id FROM
sys.table_types);
+
+SELECT * FROM sys.columns WHERE table_id NOT IN (SELECT id FROM sys.tables);
+SELECT * FROM sys._columns WHERE table_id NOT IN (SELECT id FROM sys._tables);
+SELECT * FROM tmp._columns WHERE table_id NOT IN (SELECT id FROM tmp._tables);
+SELECT * FROM sys.columns WHERE type NOT IN (SELECT sqlname FROM sys.types);
+SELECT * FROM sys._columns WHERE type NOT IN (SELECT sqlname FROM sys.types);
+SELECT * FROM sys._columns WHERE type NOT IN (SELECT sqlname FROM sys.types);
+
+SELECT * FROM sys.functions WHERE schema_id NOT IN (SELECT id FROM
sys.schemas);
+-- SELECT * FROM sys.functions WHERE type NOT IN (SELECT id FROM
sys.function_types); -- table sys.function_types does not yet exist
+SELECT * FROM sys.functions WHERE type NOT IN (1,2,3,4,5,6,7); -- replace
this check when table sys.function_types becomes available
+-- SELECT * FROM sys.functions WHERE language NOT IN (SELECT language_id FROM
sys.function_languages); -- table sys.function_languages does not yet exist
+SELECT * FROM sys.functions WHERE language NOT IN (0,1,2,3,4,5,6,7); --
replace this check when table sys.function_languages becomes available
+
+SELECT * FROM sys.systemfunctions WHERE function_id NOT IN (SELECT id FROM
sys.functions);
+-- systemfunctions should refer only to functions in MonetDB system schemas
(on Dec2016 these are: sys, json, profiler and bam)
+SELECT * FROM sys.systemfunctions WHERE function_id NOT IN (SELECT id FROM
sys.functions WHERE schema_id IN (SELECT id FROM sys.schemas WHERE name IN
('sys','json','profiler','bam')));
+
+SELECT * FROM sys.args WHERE func_id NOT IN (SELECT id FROM sys.functions);
+SELECT * FROM sys.args WHERE type NOT IN (SELECT sqlname FROM sys.types);
+
+SELECT * FROM sys.types WHERE schema_id NOT IN (SELECT id FROM sys.schemas);
+SELECT * FROM sys.types WHERE schema_id NOT IN (SELECT id FROM sys.schemas)
AND schema_id <> 0;
+
+SELECT * FROM sys.keys WHERE table_id NOT IN (SELECT id FROM sys.tables);
+SELECT * FROM sys.keys WHERE table_id NOT IN (SELECT id FROM sys._tables);
+SELECT * FROM tmp.keys WHERE table_id NOT IN (SELECT id FROM tmp._tables);
+-- SELECT * FROM sys.keys WHERE type NOT IN (SELECT key_type_id FROM
sys.key_types); -- table sys.key_types does not yet exist
+-- SELECT * FROM tmp.keys WHERE type NOT IN (SELECT key_type_id FROM
sys.key_types); -- table sys.key_types does not yet exist
+SELECT * FROM sys.keys WHERE type NOT IN (0, 1, 2); -- replace this check
when table sys.key_types becomes available
+SELECT * FROM tmp.keys WHERE type NOT IN (0, 1, 2); -- replace this check
when table sys.key_types becomes available
+
+SELECT * FROM sys.idxs WHERE table_id NOT IN (SELECT id FROM sys.tables);
+SELECT * FROM sys.idxs WHERE table_id NOT IN (SELECT id FROM sys._tables);
+SELECT * FROM tmp.idxs WHERE table_id NOT IN (SELECT id FROM tmp._tables);
+--SELECT * FROM sys.idxs WHERE type NOT IN (SELECT index_type_id FROM
sys.index_types); -- table sys.index_types does not yet exist
+--SELECT * FROM tmp.idxs WHERE type NOT IN (SELECT index_type_id FROM
sys.index_types); -- table sys.index_types does not yet exist
+SELECT * FROM sys.idxs WHERE type NOT IN (0, 1, 2); -- replace this check
when table sys.index_types becomes available
+SELECT * FROM tmp.idxs WHERE type NOT IN (0, 1, 2); -- replace this check
when table sys.index_types becomes available
+
+SELECT * FROM sys.sequences WHERE schema_id NOT IN (SELECT id FROM
sys.schemas);
+
+SELECT * FROM sys.triggers WHERE table_id NOT IN (SELECT id FROM sys.tables);
+SELECT * FROM sys.triggers WHERE table_id NOT IN (SELECT id FROM sys._tables);
+SELECT * FROM tmp.triggers WHERE table_id NOT IN (SELECT id FROM tmp._tables);
+
+SELECT * FROM sys.dependencies WHERE depend_type NOT IN (SELECT
dependency_type_id FROM sys.dependency_types);
+
+-- to view the used depend_types run: SELECT depend_type, COUNT(*) AS count
FROM sys.dependencies GROUP BY depend_type ORDER BY depend_type;
+-- Key dependency on columns
+SELECT * FROM sys.dependencies WHERE depend_type = 4 and id NOT IN (SELECT id
FROM sys.columns);
+SELECT * FROM sys.dependencies WHERE depend_type = 4 and depend_id NOT IN
(SELECT id FROM sys.keys WHERE type IN (0,1));
+-- View dependency on tables (incl other views)
+-- SELECT * FROM sys.dependencies WHERE depend_type = 5 and id NOT IN (SELECT
id FROM sys.tables); -- TODO: refine check as it lists 7 rows
+SELECT * FROM sys.dependencies WHERE depend_type = 5 and depend_id NOT IN
(SELECT id FROM sys.tables);
+-- Function dependency on columns (from views)
+--SELECT * FROM sys.dependencies WHERE depend_type = 7 and id NOT IN (SELECT
id FROM sys.columns); -- TODO: refine check as it lists 57 rows
+SELECT * FROM sys.dependencies WHERE depend_type = 7 and depend_id NOT IN
(SELECT id FROM sys.functions);
+-- Index dependency on columns
+SELECT * FROM sys.dependencies WHERE depend_type = 10 and id NOT IN (SELECT id
FROM sys.columns);
+SELECT * FROM sys.dependencies WHERE depend_type = 10 and depend_id NOT IN
(SELECT id FROM sys.idxs);
+-- FKey dependency on columns
+--SELECT * FROM sys.dependencies WHERE depend_type = 11 and id NOT IN (SELECT
id FROM sys.columns); -- TODO: refine check as it lists 3 rows
+SELECT * FROM sys.dependencies WHERE depend_type = 11 and depend_id NOT IN
(SELECT id FROM sys.keys WHERE type IN (2));
+-- Procedure dependency on columns (from views)
+--SELECT * FROM sys.dependencies WHERE depend_type = 13 and id NOT IN (SELECT
id FROM sys.columns); -- TODO: refine check as it lists 5 rows
+SELECT * FROM sys.dependencies WHERE depend_type = 13 and depend_id NOT IN
(SELECT id FROM sys.functions);
+-- Type dependency on columns
+--SELECT * FROM sys.dependencies WHERE depend_type = 15 and id NOT IN (SELECT
id FROM sys.columns); -- TODO: change check as it lists all 46 rows
+--SELECT * FROM sys.dependencies WHERE depend_type = 15 and depend_id NOT IN
(SELECT id FROM sys.types); -- TODO: change check as it lists all 46 rows
+
+SELECT * FROM sys.auths WHERE grantor NOT IN (SELECT id FROM sys.auths) AND
grantor > 0;
+SELECT * FROM sys.users WHERE default_schema NOT IN (SELECT id FROM
sys.schemas);
+SELECT * FROM sys.db_user_info WHERE default_schema NOT IN (SELECT id FROM
sys.schemas);
+
+--SELECT * FROM sys.user_role WHERE login_id NOT IN (SELECT name FROM
sys.users); -- how is user_role.login_id connected to users.name? They have
different data types/domains
+SELECT * FROM sys.user_role WHERE role_id NOT IN (SELECT id FROM sys.auths);
+SELECT * FROM sys.privileges WHERE auth_id NOT IN (SELECT id FROM sys.auths);
+SELECT * FROM sys.privileges WHERE grantor NOT IN (SELECT id FROM sys.auths)
AND grantor > 0;
+-- SELECT * FROM sys.privileges WHERE privileges NOT IN (SELECT
privilege_code_id FROM sys.privilege_codes); -- 1 and 16 -- table
sys.privilege_codes does not yet exist
+SELECT * FROM sys.privileges WHERE privileges NOT IN
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,32); -- replace this check when table
sys.privilege_codes becomes available
+
+SELECT * FROM sys.querylog_catalog WHERE owner NOT IN (SELECT name FROM
sys.users);
+SELECT * FROM sys.querylog_calls WHERE id NOT IN (SELECT id FROM
sys.querylog_catalog);
+SELECT * FROM sys.querylog_history WHERE id NOT IN (SELECT id FROM
sys.querylog_catalog);
+
+SELECT * FROM sys.queue WHERE tag NOT IN (SELECT qtag FROM sys.queue);
+SELECT * FROM sys.queue WHERE "user" NOT IN (SELECT name FROM sys.users);
+
+SELECT * FROM sys.sessions WHERE "user" NOT IN (SELECT name FROM sys.users);
+
+SELECT * FROM sys.statistics WHERE column_id NOT IN (SELECT id FROM
sys._columns);
+SELECT * FROM sys.statistics WHERE type NOT IN (SELECT sqlname FROM sys.types);
+
+SELECT * FROM sys.storage WHERE schema NOT IN (SELECT name FROM sys.schemas);
+SELECT * FROM sys.storage WHERE table NOT IN (SELECT name FROM sys._tables);
+SELECT * FROM sys.storage WHERE column NOT IN (SELECT name FROM sys._columns
UNION ALL SELECT name FROM sys.keys);
+SELECT * FROM sys.storage WHERE type NOT IN (SELECT sqlname FROM sys.types);
+
+SELECT * FROM sys.storagemodel WHERE schema NOT IN (SELECT name FROM
sys.schemas);
+SELECT * FROM sys.storagemodel WHERE table NOT IN (SELECT name FROM
sys._tables);
+SELECT * FROM sys.storagemodel WHERE column NOT IN (SELECT name FROM
sys._columns UNION ALL SELECT name FROM sys.keys);
+SELECT * FROM sys.storagemodel WHERE type NOT IN (SELECT sqlname FROM
sys.types);
+
+SELECT * FROM sys.storagemodelinput WHERE schema NOT IN (SELECT name FROM
sys.schemas);
+SELECT * FROM sys.storagemodelinput WHERE table NOT IN (SELECT name FROM
sys._tables);
+SELECT * FROM sys.storagemodelinput WHERE column NOT IN (SELECT name FROM
sys._columns UNION ALL SELECT name FROM sys.keys);
+SELECT * FROM sys.storagemodelinput WHERE type NOT IN (SELECT sqlname FROM
sys.types);
+
+SELECT * FROM sys.tablestoragemodel WHERE schema NOT IN (SELECT name FROM
sys.schemas);
+SELECT * FROM sys.tablestoragemodel WHERE table NOT IN (SELECT name FROM
sys._tables);
+
diff --git
a/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.stable.err
b/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.stable.err
new file mode 100644
--- /dev/null
+++
b/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.stable.err
@@ -0,0 +1,34 @@
+stderr of test 'check_ForeignKey_referential_integrity` in directory
'sql/test/sys-schema` itself:
+
+
+# 16:15:38 >
+# 16:15:38 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=34676" "--set"
"mapi_usock=/var/tmp/mtest-32669/.s.monetdb.34676" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/export/scratch2/dinther/INSTALL/var/MonetDB/mTests_sql_test_sys-schema"
+# 16:15:38 >
+
+# builtin opt gdk_dbpath =
/export/scratch2/dinther/INSTALL/var/monetdb5/dbfarm/demo
+# builtin opt gdk_debug = 0
+# builtin opt gdk_vmtrim = no
+# builtin opt monet_prompt = >
+# builtin opt monet_daemon = no
+# builtin opt mapi_port = 50000
+# builtin opt mapi_open = false
+# builtin opt mapi_autosense = false
+# builtin opt sql_optimizer = default_pipe
+# builtin opt sql_debug = 0
+# cmdline opt gdk_nr_threads = 0
+# cmdline opt mapi_open = true
+# cmdline opt mapi_port = 34676
+# cmdline opt mapi_usock = /var/tmp/mtest-32669/.s.monetdb.34676
+# cmdline opt monet_prompt =
+# cmdline opt gdk_dbpath =
/export/scratch2/dinther/INSTALL/var/MonetDB/mTests_sql_test_sys-schema
+# cmdline opt gdk_debug = 536870922
+
+# 16:15:38 >
+# 16:15:38 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-32669" "--port=34676"
+# 16:15:38 >
+
+
+# 16:15:38 >
+# 16:15:38 > "Done."
+# 16:15:38 >
+
diff --git
a/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.stable.out
b/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.stable.out
new file mode 100644
--- /dev/null
+++
b/sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.stable.out
@@ -0,0 +1,414 @@
+stdout of test 'check_ForeignKey_referential_integrity` in directory
'sql/test/sys-schema` itself:
+
+
+# 16:15:38 >
+# 16:15:38 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=34676" "--set"
"mapi_usock=/var/tmp/mtest-32669/.s.monetdb.34676" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/export/scratch2/dinther/INSTALL/var/MonetDB/mTests_sql_test_sys-schema"
+# 16:15:38 >
+
+# MonetDB 5 server v11.25.4
+# This is an unreleased version
+# Serving database 'mTests_sql_test_sys-schema', using 8 threads
+# Compiled for x86_64-unknown-linux-gnu/64bit with 128bit integers
+# Found 15.589 GiB available main-memory.
+# Copyright (c) 1993-July 2008 CWI.
+# Copyright (c) August 2008-2017 MonetDB B.V., all rights reserved
+# Visit http://www.monetdb.org/ for further information
+# Listening for connection requests on mapi:monetdb://toulouse.da.cwi.nl:34676/
+# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-32669/.s.monetdb.34676
+# MonetDB/GIS module loaded
+# MonetDB/SQL module loaded
+
+Ready.
+#WARNING To speedup user.columnsize a bulk operator implementation is needed
+# X_61:bat[:lng] :=
mal.multiplex("user":str,"columnsize":str,X_46:bat[:str],X_53:bat[:lng],X_60:bat[:lng]);
+#WARNING To speedup user.heapsize a bulk operator implementation is needed
+# X_71:bat[:lng] :=
mal.multiplex("user":str,"heapsize":str,X_46:bat[:str],X_60:bat[:lng],X_70:bat[:int]);
+#WARNING To speedup user.hashsize a bulk operator implementation is needed
+# X_80:bat[:lng] :=
mal.multiplex("user":str,"hashsize":str,X_79:bat[:bit],X_53:bat[:lng]);
+#WARNING To speedup user.imprintsize a bulk operator implementation is needed
+# X_82:bat[:lng] :=
mal.multiplex("user":str,"imprintsize":str,X_53:bat[:lng],X_46:bat[:str]);
+
+# 16:15:38 >
+# 16:15:38 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-32669" "--port=34676"
+# 16:15:38 >
+
+#SELECT * FROM sys.schemas WHERE authorization NOT IN (SELECT id FROM
sys.auths);
+% sys.schemas, sys.schemas, sys.schemas, sys.schemas, sys.schemas #
table_name
+% id, name, authorization, owner, system # name
+% int, varchar, int, int, boolean # type
+% 1, 0, 1, 1, 5 # length
+#SELECT * FROM sys.schemas WHERE owner NOT IN (SELECT id FROM sys.auths);
+% sys.schemas, sys.schemas, sys.schemas, sys.schemas, sys.schemas #
table_name
+% id, name, authorization, owner, system # name
+% int, varchar, int, int, boolean # type
+% 1, 0, 1, 1, 5 # length
+#SELECT * FROM sys.tables WHERE schema_id NOT IN (SELECT id FROM sys.schemas);
+% .tables, .tables, .tables, .tables, .tables,
.tables, .tables, .tables, .tables # table_name
+% id, name, schema_id, query, type, system, commit_action, access,
temporary # name
+% int, varchar, int, varchar, smallint, boolean,
smallint, smallint, tinyint # type
+% 1, 0, 1, 0, 1, 5, 1, 1, 1 # length
+#SELECT * FROM sys._tables WHERE schema_id NOT IN (SELECT id FROM sys.schemas);
+% sys._tables, sys._tables, sys._tables, sys._tables, sys._tables,
sys._tables, sys._tables, sys._tables # table_name
+% id, name, schema_id, query, type, system, commit_action, access
# name
+% int, varchar, int, varchar, smallint, boolean,
smallint, smallint # type
+% 1, 0, 1, 0, 1, 5, 1, 1 # length
+#SELECT * FROM tmp._tables WHERE schema_id NOT IN (SELECT id FROM sys.schemas);
+% tmp._tables, tmp._tables, tmp._tables, tmp._tables, tmp._tables,
tmp._tables, tmp._tables, tmp._tables # table_name
+% id, name, schema_id, query, type, system, commit_action, access
# name
+% int, varchar, int, varchar, smallint, boolean,
smallint, smallint # type
+% 1, 0, 1, 0, 1, 5, 1, 1 # length
+#SELECT * FROM sys.tables WHERE type NOT IN (SELECT table_type_id FROM
sys.table_types);
+% .tables, .tables, .tables, .tables, .tables,
.tables, .tables, .tables, .tables # table_name
+% id, name, schema_id, query, type, system, commit_action, access,
temporary # name
+% int, varchar, int, varchar, smallint, boolean,
smallint, smallint, tinyint # type
+% 1, 0, 1, 0, 1, 5, 1, 1, 1 # length
+#SELECT * FROM sys._tables WHERE type NOT IN (SELECT table_type_id FROM
sys.table_types);
+% sys._tables, sys._tables, sys._tables, sys._tables, sys._tables,
sys._tables, sys._tables, sys._tables # table_name
+% id, name, schema_id, query, type, system, commit_action, access
# name
+% int, varchar, int, varchar, smallint, boolean,
smallint, smallint # type
+% 1, 0, 1, 0, 1, 5, 1, 1 # length
+#SELECT * FROM tmp._tables WHERE type NOT IN (SELECT table_type_id FROM
sys.table_types);
+% tmp._tables, tmp._tables, tmp._tables, tmp._tables, tmp._tables,
tmp._tables, tmp._tables, tmp._tables # table_name
+% id, name, schema_id, query, type, system, commit_action, access
# name
+% int, varchar, int, varchar, smallint, boolean,
smallint, smallint # type
+% 1, 0, 1, 0, 1, 5, 1, 1 # length
+#SELECT * FROM sys.columns WHERE table_id NOT IN (SELECT id FROM sys.tables);
+% .columns, .columns, .columns, .columns, .columns,
.columns, .columns, .columns, .columns, .columns #
table_name
+% id, name, type, type_digits, type_scale, table_id,
default, null, number, storage # name
+% int, varchar, varchar, int, int, int, varchar,
boolean, int, varchar # type
+% 1, 0, 0, 1, 1, 1, 0, 5, 1, 0 #
length
+#SELECT * FROM sys._columns WHERE table_id NOT IN (SELECT id FROM sys._tables);
+% sys._columns, sys._columns, sys._columns, sys._columns,
sys._columns, sys._columns, sys._columns, sys._columns, sys._columns,
sys._columns # table_name
+% id, name, type, type_digits, type_scale, table_id,
default, null, number, storage # name
+% int, varchar, varchar, int, int, int, varchar,
boolean, int, varchar # type
+% 1, 0, 0, 1, 1, 1, 0, 5, 1, 0 #
length
+#SELECT * FROM tmp._columns WHERE table_id NOT IN (SELECT id FROM tmp._tables);
+% tmp._columns, tmp._columns, tmp._columns, tmp._columns,
tmp._columns, tmp._columns, tmp._columns, tmp._columns, tmp._columns,
tmp._columns # table_name
+% id, name, type, type_digits, type_scale, table_id,
default, null, number, storage # name
+% int, varchar, varchar, int, int, int, varchar,
boolean, int, varchar # type
+% 1, 0, 0, 1, 1, 1, 0, 5, 1, 0 #
length
+#SELECT * FROM sys.columns WHERE type NOT IN (SELECT sqlname FROM sys.types);
+% .columns, .columns, .columns, .columns, .columns,
.columns, .columns, .columns, .columns, .columns #
table_name
+% id, name, type, type_digits, type_scale, table_id,
default, null, number, storage # name
+% int, varchar, varchar, int, int, int, varchar,
boolean, int, varchar # type
+% 1, 0, 0, 1, 1, 1, 0, 5, 1, 0 #
length
+#SELECT * FROM sys._columns WHERE type NOT IN (SELECT sqlname FROM sys.types);
+% sys._columns, sys._columns, sys._columns, sys._columns,
sys._columns, sys._columns, sys._columns, sys._columns, sys._columns,
sys._columns # table_name
+% id, name, type, type_digits, type_scale, table_id,
default, null, number, storage # name
+% int, varchar, varchar, int, int, int, varchar,
boolean, int, varchar # type
+% 1, 0, 0, 1, 1, 1, 0, 5, 1, 0 #
length
+#SELECT * FROM sys._columns WHERE type NOT IN (SELECT sqlname FROM sys.types);
+% sys._columns, sys._columns, sys._columns, sys._columns,
sys._columns, sys._columns, sys._columns, sys._columns, sys._columns,
sys._columns # table_name
+% id, name, type, type_digits, type_scale, table_id,
default, null, number, storage # name
+% int, varchar, varchar, int, int, int, varchar,
boolean, int, varchar # type
+% 1, 0, 0, 1, 1, 1, 0, 5, 1, 0 #
length
+#SELECT * FROM sys.functions WHERE schema_id NOT IN (SELECT id FROM
sys.schemas);
+% sys.functions, sys.functions, sys.functions, sys.functions,
sys.functions, sys.functions, sys.functions, sys.functions, sys.functions,
sys.functions # table_name
+% id, name, func, mod, language, type, side_effect, varres,
vararg, schema_id # name
+% int, varchar, varchar, varchar, int, int,
boolean, boolean, boolean, int # type
+% 1, 0, 0, 0, 1, 1, 5, 5, 5, 1 #
length
+#SELECT * FROM sys.functions WHERE type NOT IN (1,2,3,4,5,6,7); -- replace
this check when table sys.function_types becomes available
+% sys.functions, sys.functions, sys.functions, sys.functions,
sys.functions, sys.functions, sys.functions, sys.functions, sys.functions,
sys.functions # table_name
+% id, name, func, mod, language, type, side_effect, varres,
vararg, schema_id # name
+% int, varchar, varchar, varchar, int, int,
boolean, boolean, boolean, int # type
+% 1, 0, 0, 0, 1, 1, 5, 5, 5, 1 #
length
+#SELECT * FROM sys.functions WHERE language NOT IN (0,1,2,3,4,5,6,7); --
replace this check when table sys.function_languages becomes available
+% sys.functions, sys.functions, sys.functions, sys.functions,
sys.functions, sys.functions, sys.functions, sys.functions, sys.functions,
sys.functions # table_name
+% id, name, func, mod, language, type, side_effect, varres,
vararg, schema_id # name
+% int, varchar, varchar, varchar, int, int,
boolean, boolean, boolean, int # type
+% 1, 0, 0, 0, 1, 1, 5, 5, 5, 1 #
length
+#SELECT * FROM sys.systemfunctions WHERE function_id NOT IN (SELECT id FROM
sys.functions);
+% sys.systemfunctions # table_name
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
