Changeset: 76a5a95a2548 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/76a5a95a2548
Modified Files:
sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.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/upgrade.stable.out.int128
sql/test/emptydb-upgrade/Tests/upgrade.stable.out
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-hge/Tests/dump.stable.out
sql/test/testdb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade/Tests/dump.stable.out
sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out
sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-hge/Tests/dump.stable.out
sql/test/testdb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade/Tests/dump.stable.out
sql/test/testdb-upgrade/Tests/upgrade.stable.out
sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128
Branch: Aug2024
Log Message:
Upgrade from Aug2024 release.
diffs (truncated from 13892 to 300 lines):
diff --git
a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
---
a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
+++
b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
@@ -1,627 +1,6 @@
Running database upgrade commands to update system tables.
Running database upgrade commands:
-drop procedure if exists SHPattach(string) cascade;
-drop procedure if exists SHPload(integer) cascade;
-drop procedure if exists SHPload(integer, geometry) cascade;
-
-Running database upgrade commands:
-create procedure SHPLoad(fname string, schemaname string, tablename string)
external name shp.load;
-create procedure SHPLoad(fname string, tablename string) external name
shp.load;
-update sys.functions set system = true where schema_id = 2000 and name in
('shpload');
-Running database upgrade commands:
-drop function if exists sys.st_intersects(geometry, geometry) cascade;
-drop function if exists sys.st_dwithin(geometry, geometry, double) cascade;
-drop view if exists sys.geometry_columns cascade;
-drop function if exists sys.st_collect(geometry, geometry) cascade;
-drop aggregate if exists sys.st_collect(geometry) cascade;
-drop aggregate if exists sys.st_makeline(geometry) cascade;
-create view sys.geometry_columns as
- select cast(null as varchar(1)) as f_table_catalog,
- s.name as f_table_schema,
- t.name as f_table_name,
- c.name as f_geometry_column,
- cast(has_z(c.type_digits) + has_m(c.type_digits) +2 as integer) as
coord_dimension,
- c.type_scale as srid,
- get_type(c.type_digits, 0) as geometry_type
- from sys.columns c, sys.tables t, sys.schemas s
- where c.table_id = t.id and t.schema_id = s.id
- and c.type in (select sqlname from sys.types where systemname in ('wkb',
'wkba'));
-GRANT SELECT ON sys.geometry_columns TO PUBLIC;
-CREATE FUNCTION ST_Collect(geom1 Geometry, geom2 Geometry) RETURNS Geometry
EXTERNAL NAME geom."Collect";
-GRANT EXECUTE ON FUNCTION ST_Collect(Geometry, Geometry) TO PUBLIC;
-CREATE AGGREGATE ST_Collect(geom Geometry) RETURNS Geometry external name
aggr."Collect";
-GRANT EXECUTE ON AGGREGATE ST_Collect(Geometry) TO PUBLIC;
-CREATE FUNCTION ST_DistanceGeographic(geom1 Geometry, geom2 Geometry) RETURNS
double EXTERNAL NAME geom."DistanceGeographic";
-GRANT EXECUTE ON FUNCTION ST_DistanceGeographic(Geometry, Geometry) TO PUBLIC;
-CREATE FILTER FUNCTION ST_DWithinGeographic(geom1 Geometry, geom2 Geometry,
distance double) EXTERNAL NAME geom."DWithinGeographic";
-GRANT EXECUTE ON FILTER ST_DWithinGeographic(Geometry, Geometry, double) TO
PUBLIC;
-CREATE FILTER FUNCTION ST_DWithin(geom1 Geometry, geom2 Geometry, distance
double) EXTERNAL NAME rtree."DWithin";
-GRANT EXECUTE ON FILTER ST_DWithin(Geometry, Geometry, double) TO PUBLIC;
-CREATE FILTER FUNCTION ST_DWithin_NoIndex(geom1 Geometry, geom2 Geometry,
distance double) EXTERNAL NAME geom."DWithin_noindex";
-GRANT EXECUTE ON FILTER ST_DWithin_NoIndex(Geometry, Geometry, double) TO
PUBLIC;
-CREATE FUNCTION ST_DWithin2(geom1 Geometry, geom2 Geometry, bbox1 mbr, bbox2
mbr, dst double) RETURNS boolean EXTERNAL NAME geom."DWithin2";
-GRANT EXECUTE ON FUNCTION ST_DWithin2(Geometry, Geometry, mbr, mbr, double) TO
PUBLIC;
-CREATE FILTER FUNCTION ST_IntersectsGeographic(geom1 Geometry, geom2 Geometry)
EXTERNAL NAME geom."IntersectsGeographic";
-GRANT EXECUTE ON FILTER ST_IntersectsGeographic(Geometry, Geometry) TO PUBLIC;
-CREATE FILTER FUNCTION ST_Intersects(geom1 Geometry, geom2 Geometry) EXTERNAL
NAME rtree."Intersects";
-GRANT EXECUTE ON FILTER ST_Intersects(Geometry, Geometry) TO PUBLIC;
-CREATE FILTER FUNCTION ST_Intersects_NoIndex(geom1 Geometry, geom2 Geometry)
EXTERNAL NAME geom."Intersects_noindex";
-GRANT EXECUTE ON FILTER ST_Intersects_NoIndex(Geometry, Geometry) TO PUBLIC;
-CREATE AGGREGATE ST_MakeLine(geom Geometry) RETURNS Geometry external name
aggr."MakeLine";
-GRANT EXECUTE ON AGGREGATE ST_MakeLine(Geometry) TO PUBLIC;
-update sys.functions set system = true where system <> true and schema_id =
2000 and name in ('st_collect', 'st_distancegeographic',
'st_dwithingeographic', 'st_dwithin', 'st_dwithin_noindex', 'st_dwithin2',
'st_intersectsgeographic', 'st_intersects', 'st_intersects_noindex',
'st_makeline');
-update sys._tables set system = true where system <> true and schema_id = 2000
and name = 'geometry_columns';
-
-Running database upgrade commands:
-drop function sys.similarity(string, string) cascade;
-
-Running database upgrade commands:
-CREATE VIEW sys.describe_accessible_tables AS
- SELECT
- schemas.name AS schema,
- tables.name AS table,
- tt.table_type_name AS table_type,
- pc.privilege_code_name AS privs,
- p.privileges AS privs_code
- FROM privileges p
- JOIN sys.roles ON p.auth_id = roles.id
- JOIN sys.tables ON p.obj_id = tables.id
- JOIN sys.table_types tt ON tables.type = tt.table_type_id
- JOIN sys.schemas ON tables.schema_id = schemas.id
- JOIN sys.privilege_codes pc ON p.privileges = pc.privilege_code_id
- WHERE roles.name = current_role;
-GRANT SELECT ON sys.describe_accessible_tables TO PUBLIC;
-update sys._tables set system = true where system <> true and schema_id = 2000
and name = 'describe_accessible_tables';
-alter table sys.function_languages set read write;
-delete from sys.function_languages where language_keyword like 'PYTHON%_MAP';
-update sys.functions set language = language - 1 where language in (7, 11);
-update sys.functions set mod = 'pyapi3' where mod in ('pyapi', 'pyapi3map');
-commit;
-
-Running database upgrade commands:
-alter table sys.function_languages set read only;
-
-Running database upgrade commands:
-DROP FUNCTION IF EXISTS sys.dump_database(BOOLEAN) CASCADE;
-DROP VIEW IF EXISTS sys.dump_comments CASCADE;
-DROP VIEW IF EXISTS sys.describe_comments CASCADE;
-CREATE VIEW sys.describe_comments AS
- SELECT o.id AS id, o.tpe AS tpe, o.nme AS fqn, cm.remark AS rem
- FROM (
- SELECT id, 'SCHEMA', sys.DQ(name) FROM sys.schemas WHERE NOT
system
- UNION ALL
- SELECT t.id, ifthenelse(ts.table_type_name = 'VIEW', 'VIEW',
'TABLE'), sys.FQN(s.name, t.name)
- FROM sys.schemas s JOIN sys._tables t ON s.id = t.schema_id
JOIN sys.table_types ts ON t.type = ts.table_type_id
- WHERE NOT t.system
- UNION ALL
- SELECT c.id, 'COLUMN', sys.FQN(s.name, t.name) || '.' ||
sys.DQ(c.name) FROM sys.columns c, sys._tables t, sys.schemas s WHERE NOT
t.system AND c.table_id = t.id AND t.schema_id = s.id
- UNION ALL
- SELECT idx.id, 'INDEX', sys.FQN(s.name, idx.name) FROM sys.idxs
idx, sys._tables t, sys.schemas s WHERE NOT t.system AND idx.table_id = t.id
AND t.schema_id = s.id
- UNION ALL
- SELECT seq.id, 'SEQUENCE', sys.FQN(s.name, seq.name) FROM
sys.sequences seq, sys.schemas s WHERE seq.schema_id = s.id
- UNION ALL
- SELECT f.id, ft.function_type_keyword, qf.nme FROM
sys.functions f, sys.function_types ft, sys.schemas s,
sys.fully_qualified_functions qf
- WHERE NOT f.system AND f.type = ft.function_type_id AND
f.schema_id = s.id AND qf.id = f.id
- ) AS o(id, tpe, nme)
- JOIN sys.comments cm ON cm.id = o.id;
-GRANT SELECT ON sys.describe_comments TO PUBLIC;
-CREATE VIEW sys.dump_comments AS
- SELECT 'COMMENT ON ' || c.tpe || ' ' || c.fqn || ' IS ' || sys.SQ(c.rem) ||
';' stmt FROM sys.describe_comments c;
-CREATE FUNCTION sys.dump_database(describe BOOLEAN) RETURNS TABLE(o int, stmt
STRING)
-BEGIN
- SET SCHEMA sys;
- TRUNCATE sys.dump_statements;
- INSERT INTO sys.dump_statements VALUES (1, 'START TRANSACTION;');
- INSERT INTO sys.dump_statements VALUES (2, 'SET SCHEMA "sys";');
- INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_create_roles;
- INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_create_users;
- INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_create_schemas;
- INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_user_defined_types;
- INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_add_schemas_to_users;
- INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_grant_user_privileges;
- INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_sequences;
- --functions and table-likes can be interdependent. They should be inserted
in the order of their catalogue id.
- INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(ORDER BY stmts.o), stmts.s
- FROM (
- SELECT f.o, f.stmt FROM
sys.dump_functions f
- UNION ALL
- SELECT t.o, t.stmt FROM sys.dump_tables t
- ) AS stmts(o, s);
- IF NOT DESCRIBE THEN
- CALL sys.dump_table_data();
- END IF;
- INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_start_sequences;
- INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_column_defaults;
- INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_table_constraint_type;
- INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_indices;
- INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_foreign_keys;
- INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_partition_tables;
- INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_triggers;
- INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_comments;
- INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_table_grants;
- INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_column_grants;
- INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_function_grants;
- INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM
sys.dump_statements) + 1, 'COMMIT;');
- RETURN sys.dump_statements;
-END;
-update sys._tables set system = true where schema_id = 2000 and name in
('describe_comments','dump_comments');
-update sys.functions set system = true where system <> true and schema_id =
2000 and name = 'dump_database' and type = 5;
-
-Running database upgrade commands:
-CREATE FUNCTION sys.sql_datatype(mtype varchar(999), digits integer, tscale
integer, nameonly boolean, shortname boolean)
- RETURNS varchar(1024)
-BEGIN
- RETURN
- CASE mtype
- WHEN 'char' THEN sys.ifthenelse(nameonly OR digits <= 1,
sys.ifthenelse(shortname, 'CHAR', 'CHARACTER'), sys.ifthenelse(shortname,
'CHAR(', 'CHARACTER(') || digits || ')')
- WHEN 'varchar' THEN sys.ifthenelse(nameonly OR digits = 0,
sys.ifthenelse(shortname, 'VARCHAR', 'CHARACTER VARYING'),
sys.ifthenelse(shortname, 'VARCHAR(', 'CHARACTER VARYING(') || digits || ')')
- WHEN 'clob' THEN sys.ifthenelse(nameonly OR digits = 0,
sys.ifthenelse(shortname, 'CLOB', 'CHARACTER LARGE OBJECT'),
sys.ifthenelse(shortname, 'CLOB(', 'CHARACTER LARGE OBJECT(') || digits || ')')
- WHEN 'blob' THEN sys.ifthenelse(nameonly OR digits = 0,
sys.ifthenelse(shortname, 'BLOB', 'BINARY LARGE OBJECT'),
sys.ifthenelse(shortname, 'BLOB(', 'BINARY LARGE OBJECT(') || digits || ')')
- WHEN 'int' THEN 'INTEGER'
- WHEN 'bigint' THEN 'BIGINT'
- WHEN 'smallint' THEN 'SMALLINT'
- WHEN 'tinyint' THEN 'TINYINT'
- WHEN 'hugeint' THEN 'HUGEINT'
- WHEN 'boolean' THEN 'BOOLEAN'
- WHEN 'date' THEN 'DATE'
- WHEN 'time' THEN sys.ifthenelse(nameonly OR digits = 1, 'TIME', 'TIME(' ||
(digits -1) || ')')
- WHEN 'timestamp' THEN sys.ifthenelse(nameonly OR digits = 7, 'TIMESTAMP',
'TIMESTAMP(' || (digits -1) || ')')
- WHEN 'timestamptz' THEN sys.ifthenelse(nameonly OR digits = 7, 'TIMESTAMP
WITH TIME ZONE', 'TIMESTAMP(' || (digits -1) || ') WITH TIME ZONE')
- WHEN 'timetz' THEN sys.ifthenelse(nameonly OR digits = 1, 'TIME WITH TIME
ZONE', 'TIME(' || (digits -1) || ') WITH TIME ZONE')
- WHEN 'decimal' THEN sys.ifthenelse(nameonly OR digits = 0, 'DECIMAL',
'DECIMAL(' || digits || sys.ifthenelse(tscale = 0, '', ',' || tscale) || ')')
- WHEN 'double' THEN sys.ifthenelse(nameonly OR (digits = 53 AND tscale =
0), sys.ifthenelse(shortname, 'DOUBLE', 'DOUBLE PRECISION'), 'FLOAT(' || digits
|| ')')
- WHEN 'real' THEN sys.ifthenelse(nameonly OR (digits = 24 AND tscale = 0),
'REAL', 'FLOAT(' || digits || ')')
- WHEN 'day_interval' THEN 'INTERVAL DAY'
- WHEN 'month_interval' THEN CASE digits WHEN 1 THEN 'INTERVAL YEAR' WHEN 2
THEN 'INTERVAL YEAR TO MONTH' WHEN 3 THEN 'INTERVAL MONTH' END
- WHEN 'sec_interval' THEN
- CASE digits
- WHEN 4 THEN 'INTERVAL DAY'
- WHEN 5 THEN 'INTERVAL DAY TO HOUR'
- WHEN 6 THEN 'INTERVAL DAY TO MINUTE'
- WHEN 7 THEN 'INTERVAL DAY TO SECOND'
- WHEN 8 THEN 'INTERVAL HOUR'
- WHEN 9 THEN 'INTERVAL HOUR TO MINUTE'
- WHEN 10 THEN 'INTERVAL HOUR TO SECOND'
- WHEN 11 THEN 'INTERVAL MINUTE'
- WHEN 12 THEN 'INTERVAL MINUTE TO SECOND'
- WHEN 13 THEN 'INTERVAL SECOND'
- END
- WHEN 'oid' THEN 'OID'
- WHEN 'json' THEN sys.ifthenelse(nameonly OR digits = 0, 'JSON', 'JSON(' ||
digits || ')')
- WHEN 'url' THEN sys.ifthenelse(nameonly OR digits = 0, 'URL', 'URL(' ||
digits || ')')
- WHEN 'xml' THEN sys.ifthenelse(nameonly OR digits = 0, 'XML', 'XML(' ||
digits || ')')
- WHEN 'geometry' THEN
- sys.ifthenelse(nameonly, 'GEOMETRY',
- CASE digits
- WHEN 4 THEN 'GEOMETRY(POINT' || sys.ifthenelse(tscale = 0, ')', ',' ||
tscale || ')')
- WHEN 8 THEN 'GEOMETRY(LINESTRING' || sys.ifthenelse(tscale = 0, ')',
',' || tscale || ')')
- WHEN 16 THEN 'GEOMETRY(POLYGON' || sys.ifthenelse(tscale = 0, ')', ','
|| tscale || ')')
- WHEN 20 THEN 'GEOMETRY(MULTIPOINT' || sys.ifthenelse(tscale = 0, ')',
',' || tscale || ')')
- WHEN 24 THEN 'GEOMETRY(MULTILINESTRING' || sys.ifthenelse(tscale = 0,
')', ',' || tscale || ')')
- WHEN 28 THEN 'GEOMETRY(MULTIPOLYGON' || sys.ifthenelse(tscale = 0, ')',
',' || tscale || ')')
- WHEN 32 THEN 'GEOMETRY(GEOMETRYCOLLECTION' || sys.ifthenelse(tscale =
0, ')', ',' || tscale || ')')
- ELSE 'GEOMETRY'
- END)
- ELSE sys.ifthenelse(mtype = lower(mtype), upper(mtype), '"' || mtype ||
'"') || sys.ifthenelse(nameonly OR digits = 0, '', '(' || digits ||
sys.ifthenelse(tscale = 0, '', ',' || tscale) || ')')
- END;
-END;
-GRANT EXECUTE ON FUNCTION sys.sql_datatype(varchar(999), integer, integer,
boolean, boolean) TO PUBLIC;
-update sys.functions set system = true where system <> true and schema_id =
2000 and name = 'sql_datatype' and type = 1 and language = 2;
-
-Running database upgrade commands:
-CREATE SCHEMA INFORMATION_SCHEMA;
-COMMENT ON SCHEMA INFORMATION_SCHEMA IS 'ISO/IEC 9075-11 SQL/Schemata';
-update sys.schemas set system = true where name = 'information_schema';
-CREATE VIEW INFORMATION_SCHEMA.CHARACTER_SETS AS SELECT
- cast(NULL AS varchar(1)) AS CHARACTER_SET_CATALOG,
- cast(NULL AS varchar(1)) AS CHARACTER_SET_SCHEMA,
- cast('UTF-8' AS varchar(16)) AS CHARACTER_SET_NAME,
- cast('ISO/IEC 10646:2021' AS varchar(20)) AS CHARACTER_REPERTOIRE,
- cast('UTF-8' AS varchar(16)) AS FORM_OF_USE,
- cast(NULL AS varchar(1)) AS DEFAULT_COLLATE_CATALOG,
- cast(NULL AS varchar(1)) AS DEFAULT_COLLATE_SCHEMA,
- cast(NULL AS varchar(1)) AS DEFAULT_COLLATE_NAME;
-GRANT SELECT ON TABLE INFORMATION_SCHEMA.CHARACTER_SETS TO PUBLIC WITH GRANT
OPTION;
-CREATE VIEW INFORMATION_SCHEMA.SCHEMATA AS SELECT
- cast(NULL AS varchar(1)) AS CATALOG_NAME,
- s."name" AS SCHEMA_NAME,
- a."name" AS SCHEMA_OWNER,
- cast(NULL AS varchar(1)) AS DEFAULT_CHARACTER_SET_CATALOG,
- cast(NULL AS varchar(1)) AS DEFAULT_CHARACTER_SET_SCHEMA,
- cast('UTF-8' AS varchar(16)) AS DEFAULT_CHARACTER_SET_NAME,
- cast(NULL AS varchar(1)) AS SQL_PATH,
- s."id" AS schema_id,
- s."system" AS is_system,
- cm."remark" AS comments
- FROM sys."schemas" s
- INNER JOIN sys."auths" a ON s."owner" = a."id"
- LEFT OUTER JOIN sys."comments" cm ON s."id" = cm."id"
- ORDER BY s."name";
-GRANT SELECT ON TABLE INFORMATION_SCHEMA.SCHEMATA TO PUBLIC WITH GRANT OPTION;
-CREATE VIEW INFORMATION_SCHEMA.TABLES AS SELECT
- cast(NULL AS varchar(1)) AS TABLE_CATALOG,
- s."name" AS TABLE_SCHEMA,
- t."name" AS TABLE_NAME,
- tt."table_type_name" AS TABLE_TYPE,
- cast(NULL AS varchar(1)) AS SELF_REFERENCING_COLUMN_NAME,
- cast(NULL AS varchar(1)) AS REFERENCE_GENERATION,
- cast(NULL AS varchar(1)) AS USER_DEFINED_TYPE_CATALOG,
- cast(NULL AS varchar(1)) AS USER_DEFINED_TYPE_SCHEMA,
- cast(NULL AS varchar(1)) AS USER_DEFINED_TYPE_NAME,
- cast(sys.ifthenelse((t."type" IN (0, 3, 7, 20, 30) AND t."access" IN (0,
2)), 'YES', 'NO') AS varchar(3)) AS IS_INSERTABLE_INTO,
- cast('NO' AS varchar(3)) AS IS_TYPED,
- cast((CASE t."commit_action" WHEN 1 THEN 'DELETE' WHEN 2 THEN 'PRESERVE'
WHEN 3 THEN 'DROP' ELSE NULL END) AS varchar(10)) AS COMMIT_ACTION,
- t."schema_id" AS schema_id,
- t."id" AS table_id,
- t."type" AS table_type_id,
- st."count" AS row_count,
- t."system" AS is_system,
- sys.ifthenelse(t."type" IN (1, 11), TRUE, FALSE) AS is_view,
- t."query" AS query_def,
- cm."remark" AS comments
- FROM sys."tables" t
- INNER JOIN sys."schemas" s ON t."schema_id" = s."id"
- INNER JOIN sys."table_types" tt ON t."type" = tt."table_type_id"
- LEFT OUTER JOIN sys."comments" cm ON t."id" = cm."id"
- LEFT OUTER JOIN (SELECT DISTINCT "schema", "table", "count" FROM
sys."statistics"()) st ON (s."name" = st."schema" AND t."name" = st."table")
- ORDER BY s."name", t."name";
-GRANT SELECT ON TABLE INFORMATION_SCHEMA.TABLES TO PUBLIC WITH GRANT OPTION;
-CREATE VIEW INFORMATION_SCHEMA.VIEWS AS SELECT
- cast(NULL AS varchar(1)) AS TABLE_CATALOG,
- s."name" AS TABLE_SCHEMA,
- t."name" AS TABLE_NAME,
- t."query" AS VIEW_DEFINITION,
- cast('NONE' AS varchar(10)) AS CHECK_OPTION,
- cast('NO' AS varchar(3)) AS IS_UPDATABLE,
- cast('NO' AS varchar(3)) AS INSERTABLE_INTO,
- cast('NO' AS varchar(3)) AS IS_TRIGGER_UPDATABLE,
- cast('NO' AS varchar(3)) AS IS_TRIGGER_DELETABLE,
- cast('NO' AS varchar(3)) AS IS_TRIGGER_INSERTABLE_INTO,
- t."schema_id" AS schema_id,
- t."id" AS table_id,
- cast(sys.ifthenelse(t."system", t."type" + 10 , t."type") AS smallint) AS
table_type_id,
- t."system" AS is_system,
- cm."remark" AS comments
- FROM sys."_tables" t
- INNER JOIN sys."schemas" s ON t."schema_id" = s."id"
- LEFT OUTER JOIN sys."comments" cm ON t."id" = cm."id"
- WHERE t."type" = 1
- ORDER BY s."name", t."name";
-GRANT SELECT ON TABLE INFORMATION_SCHEMA.VIEWS TO PUBLIC WITH GRANT OPTION;
-CREATE VIEW INFORMATION_SCHEMA.COLUMNS AS SELECT
- cast(NULL AS varchar(1)) AS TABLE_CATALOG,
- s."name" AS TABLE_SCHEMA,
- t."name" AS TABLE_NAME,
- c."name" AS COLUMN_NAME,
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
