Changeset: dca306d18680 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=dca306d18680
Added Files:
sql/test/sys-schema/Tests/utilities.sql
sql/test/sys-schema/Tests/utilities.stable.err
sql/test/sys-schema/Tests/utilities.stable.out
Modified Files:
sql/test/sys-schema/Tests/All
Branch: Nov2019
Log Message:
Test some possible sys utility functions
diffs (200 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
@@ -11,3 +11,5 @@ HAVE_NETCDF?netcdf_tables_checks
HAVE_SAMTOOLS?bam_tables_checks
HAVE_FITS&HAVE_GEOM&HAVE_LIDAR&HAVE_SHP&HAVE_NETCDF&HAVE_SAMTOOLS?systemfunctions
+
+utilities
diff --git a/sql/test/sys-schema/Tests/utilities.sql
b/sql/test/sys-schema/Tests/utilities.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/sys-schema/Tests/utilities.sql
@@ -0,0 +1,48 @@
+-- utility functions on sys schema objects
+
+-- utility function to find the id of an existing schema name.
+-- It will return NULL if schema name does not exist.
+CREATE OR REPLACE function schema_id(sname varchar(1024)) RETURNS INT
+BEGIN
+ RETURN SELECT min(id) from sys.schemas where name = sname;
+END;
+
+SELECT schema_id('sys');
+SELECT schema_id('json') > 2000;
+SELECT schema_id('hsfdjkhksf does not exist');
+
+
+-- utility function to find the id of an existing table name in a specific
schema.
+-- It will return NULL if table name does not exist in specified schema or
schema name does not exist.
+CREATE OR REPLACE function table_id(sname varchar(1024), tname varchar(1024))
RETURNS INT
+BEGIN
+ RETURN SELECT min(id) from sys.tables where name = tname AND schema_id =
(SELECT id from sys.schemas where name = sname);
+END;
+
+SELECT table_id('sys','tables') > 2000;
+SELECT table_id(current_schema,'columns') > 2000;
+SELECT name, type, type_digits, type_scale, "null", number from columns where
table_id = table_id('sys','tables');
+SELECT table_id('sys','hsfdjkhksf does not exist');
+
+-- utility function to find the id of an existing table name in the current
schema.
+-- It will return NULL if table name does not exist in the current schema.
+CREATE OR REPLACE function table_id(tname varchar(1024)) RETURNS INT
+BEGIN
+ RETURN SELECT min(id) from sys.tables where name = tname AND schema_id =
(SELECT id from sys.schemas where name = current_schema);
+END;
+
+SELECT current_schema;
+SELECT table_id('tables') > 2000;
+SELECT table_id('columns') > 2000;
+SELECT name, type, type_digits, type_scale, "null", number from columns where
table_id = table_id('tables');
+SELECT table_id('hsfdjkhksf does not exist');
+
+
+
+\dftv
+-- cleanup utilities
+DROP ALL function table_id;
+DROP function schema_id;
+
+\dftv
+
diff --git a/sql/test/sys-schema/Tests/utilities.stable.err
b/sql/test/sys-schema/Tests/utilities.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/sys-schema/Tests/utilities.stable.err
@@ -0,0 +1,12 @@
+stderr of test 'utilities` in directory 'sql/test/sys-schema` itself:
+
+
+# 17:20:48 >
+# 17:20:48 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-251920" "--port=33727"
+# 17:20:48 >
+
+
+# 17:20:48 >
+# 17:20:48 > "Done."
+# 17:20:48 >
+
diff --git a/sql/test/sys-schema/Tests/utilities.stable.out
b/sql/test/sys-schema/Tests/utilities.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/sys-schema/Tests/utilities.stable.out
@@ -0,0 +1,116 @@
+stdout of test 'utilities` in directory 'sql/test/sys-schema` itself:
+
+
+# 17:33:45 >
+# 17:33:45 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-253132" "--port=35072"
+# 17:33:45 >
+
+#CREATE OR REPLACE function schema_id(sname varchar(1024)) RETURNS INT
+#BEGIN
+# RETURN SELECT min(id) from sys.schemas where name = sname;
+#END;
+#SELECT schema_id('sys');
+% .L2 # table_name
+% L2 # name
+% int # type
+% 4 # length
+[ 2000 ]
+#SELECT schema_id('json') > 2000;
+% .L2 # table_name
+% L2 # name
+% boolean # type
+% 5 # length
+[ true ]
+#SELECT schema_id('hsfdjkhksf does not exist');
+% .L2 # table_name
+% L2 # name
+% int # type
+% 1 # length
+[ NULL ]
+#CREATE OR REPLACE function table_id(sname varchar(1024), tname varchar(1024))
RETURNS INT
+#BEGIN
+# RETURN SELECT min(id) from sys.tables where name = tname AND schema_id =
(SELECT id from sys.schemas where name = sname);
+#END;
+#SELECT table_id('sys','tables') > 2000;
+% .L2 # table_name
+% L2 # name
+% boolean # type
+% 5 # length
+[ true ]
+#SELECT table_id(current_schema,'columns') > 2000;
+% .L2 # table_name
+% L2 # name
+% boolean # type
+% 5 # length
+[ true ]
+#SELECT name, type, type_digits, type_scale, "null", number from columns where
table_id = table_id('sys','tables');
+% .columns, .columns, .columns, .columns, .columns,
.columns # table_name
+% name, type, type_digits, type_scale, null, number # name
+% varchar, varchar, int, int, boolean, int # type
+% 13, 8, 7, 1, 5, 1 # length
+[ "id", "int", 32, 0, true, 0 ]
+[ "name", "varchar", 1024, 0, true, 1 ]
+[ "schema_id", "int", 32, 0, true, 2 ]
+[ "query", "varchar", 1048576, 0, true, 3 ]
+[ "type", "smallint", 16, 0, true, 4 ]
+[ "system", "boolean", 1, 0, true, 5 ]
+[ "commit_action", "smallint", 16, 0, true, 6 ]
+[ "access", "smallint", 16, 0, true, 7 ]
+[ "temporary", "smallint", 16, 0, true, 8 ]
+#SELECT table_id('sys','hsfdjkhksf does not exist');
+% .L2 # table_name
+% L2 # name
+% int # type
+% 1 # length
+[ NULL ]
+#CREATE OR REPLACE function table_id(tname varchar(1024)) RETURNS INT
+#BEGIN
+# RETURN SELECT min(id) from sys.tables where name = tname AND schema_id =
(SELECT id from sys.schemas where name = current_schema);
+#END;
+#SELECT current_schema;
+% .L2 # table_name
+% L2 # name
+% varchar # type
+% 3 # length
+[ "sys" ]
+#SELECT table_id('tables') > 2000;
+% .L2 # table_name
+% L2 # name
+% boolean # type
+% 5 # length
+[ true ]
+#SELECT table_id('columns') > 2000;
+% .L2 # table_name
+% L2 # name
+% boolean # type
+% 5 # length
+[ true ]
+#SELECT name, type, type_digits, type_scale, "null", number from columns where
table_id = table_id('tables');
+% .columns, .columns, .columns, .columns, .columns,
.columns # table_name
+% name, type, type_digits, type_scale, null, number # name
+% varchar, varchar, int, int, boolean, int # type
+% 13, 8, 7, 1, 5, 1 # length
+[ "id", "int", 32, 0, true, 0 ]
+[ "name", "varchar", 1024, 0, true, 1 ]
+[ "schema_id", "int", 32, 0, true, 2 ]
+[ "query", "varchar", 1048576, 0, true, 3 ]
+[ "type", "smallint", 16, 0, true, 4 ]
+[ "system", "boolean", 1, 0, true, 5 ]
+[ "commit_action", "smallint", 16, 0, true, 6 ]
+[ "access", "smallint", 16, 0, true, 7 ]
+[ "temporary", "smallint", 16, 0, true, 8 ]
+#SELECT table_id('hsfdjkhksf does not exist');
+% .L2 # table_name
+% L2 # name
+% int # type
+% 1 # length
+[ NULL ]
+FUNCTION sys.schema_id
+FUNCTION sys.table_id
+#DROP ALL function table_id;
+#DROP function schema_id;
+
+# 17:33:45 >
+# 17:33:45 > "Done."
+# 17:33:45 >
+
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
