Changeset: 97dcb07cf407 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=97dcb07cf407
Added Files:
sql/scripts/51_sys_schema_extension.sql
Modified Files:
sql/scripts/Makefile.ag
sql/server/sql_mvc.c
Branch: default
Log Message:
Create additonal system tables with data and extend sys.tables view with new
column: table_type
These new tables will be used in future information_schema views
diffs (90 lines):
diff --git a/sql/scripts/51_sys_schema_extension.sql
b/sql/scripts/51_sys_schema_extension.sql
new file mode 100644
--- /dev/null
+++ b/sql/scripts/51_sys_schema_extension.sql
@@ -0,0 +1,54 @@
+-- create additional tables in "sys" schema
+
+CREATE TABLE sys.keywords (
+ keyword VARCHAR(40) NOT NULL PRIMARY KEY);
+
+INSERT INTO sys.keywords (keyword) VALUES
+('ADMIN'), ('AFTER'), ('AGGREGATE'), ('ALWAYS'), ('ASYMMETRIC'), ('ATOMIC'),
('AUTO_INCREMENT'),
+('BEFORE'), ('BIGINT'), ('BIGSERIAL'), ('BINARY'), ('BLOB'),
+('CALL'), ('CHAIN'), ('CLOB'), ('COMMITTED'), ('COPY'), ('CORR'),
('CUME_DIST'), ('CURRENT_ROLE'), ('CYCLE'),
+('DATABASE'), ('DELIMITERS'), ('DENSE_RANK'), ('DO'),
+('EACH'), ('ELSEIF'), ('ENCRYPTED'), ('EVERY'), ('EXCLUDE'),
+('FOLLOWING'), ('FUNCTION'),
+('GENERATED'),
+('IF'), ('ILIKE'), ('INCREMENT'),
+('LAG'), ('LEAD'), ('LIMIT'), ('LOCALTIME'), ('LOCALTIMESTAMP'), ('LOCKED'),
+('MAXVALUE'), ('MEDIAN'), ('MEDIUMINT'), ('MERGE'), ('MINVALUE'),
+('NEW'), ('NOCYCLE'), ('NOMAXVALUE'), ('NOMINVALUE'), ('NOW'),
+('OFFSET'), ('OLD'), ('OTHERS'), ('OVER'),
+('PARTITION'), ('PERCENT_RANK'), ('PLAN'), ('PRECEDING'), ('PROD'),
+('QUANTILE'),
+('RANGE'), ('RANK'), ('RECORDS'), ('REFERENCING'), ('REMOTE'), ('RENAME'),
('REPEATABLE'), ('REPLICA'),
+('RESTART'), ('RETURN'), ('RETURNS'), ('ROWS'), ('ROW_NUMBER'),
+('SAMPLE'), ('SAVEPOINT'), ('SCHEMA'), ('SEQUENCE'), ('SERIAL'),
('SERIALIZABLE'), ('SIMPLE'),
+('START'), ('STATEMENT'), ('STDIN'), ('STDOUT'), ('STREAM'), ('STRING'),
('SYMMETRIC'),
+('TIES'), ('TINYINT'), ('TRIGGER'),
+('UNBOUNDED'), ('UNCOMMITTED'), ('UNENCRYPTED'),
+('WHILE'),
+('XMLAGG'), ('XMLATTRIBUTES'), ('XMLCOMMENT'), ('XMLCONCAT'), ('XMLDOCUMENT'),
('XMLELEMENT'), ('XMLFOREST'),
+('XMLNAMESPACES'), ('XMLPARSE'), ('XMLPI'), ('XMLQUERY'), ('XMLSCHEMA'),
('XMLTEXT'), ('XMLVALIDATE');
+
+
+CREATE TABLE sys.table_types (
+ table_type_id SMALLINT NOT NULL PRIMARY KEY,
+ table_type_name VARCHAR(25) NOT NULL UNIQUE);
+
+INSERT INTO sys.table_types (table_type_id, table_type_name) VALUES
+-- values from sys._tables.type: 0=Table, 1=View, 2=Generated, 3=Merge, etc.
+ (0, 'TABLE'), (1, 'VIEW'), /* (2, 'GENERATED'), */ (3, 'MERGE TABLE'), (4,
'STREAM TABLE'), (5, 'REMOTE TABLE'), (6, 'REPLICA TABLE'),
+-- synthetically constructed system obj variants (added 10 to sys._tables.type
value when sys._tables.system is true).
+ (10, 'SYSTEM TABLE'), (11, 'SYSTEM VIEW'),
+-- synthetically constructed temporary variants (added 20 or 30 to
sys._tables.type value depending on values of temporary and commit_action).
+ (20, 'GLOBAL TEMPORARY TABLE'),
+ (30, 'LOCAL TEMPORARY TABLE');
+
+
+CREATE TABLE sys.dependency_types (
+ dependency_type_id SMALLINT NOT NULL PRIMARY KEY,
+ dependency_type_name VARCHAR(15) NOT NULL UNIQUE);
+
+INSERT INTO sys.dependency_types (dependency_type_id, dependency_type_name)
VALUES
+-- values taken from sql_catalog.h
+ (1, 'SCHEMA'), (2, 'TABLE'), (3, 'COLUMN'), (4, 'KEY'), (5, 'VIEW'), (6,
'USER'), (7, 'FUNCTION'), (8, 'TRIGGER'),
+ (9, 'OWNER'), (10, 'INDEX'), (11, 'FKEY'), (12, 'SEQUENCE'), (13,
'PROCEDURE'), (14, 'BE_DROPPED');
+
diff --git a/sql/scripts/Makefile.ag b/sql/scripts/Makefile.ag
--- a/sql/scripts/Makefile.ag
+++ b/sql/scripts/Makefile.ag
@@ -29,6 +29,7 @@ headers_sql = {
40_json.sql \
41_md5sum.sql \
45_uuid.sql \
+ 51_sys_schema_extension.sql \
75_storagemodel.sql \
80_statistics.sql \
99_system.sql
diff --git a/sql/server/sql_mvc.c b/sql/server/sql_mvc.c
--- a/sql/server/sql_mvc.c
+++ b/sql/server/sql_mvc.c
@@ -61,7 +61,7 @@ mvc_init(int debug, store_type store, in
mvc_drop_table(m, s, t, 0);
}
- t = mvc_create_view(m, s, "tables", SQL_PERSIST, "SELECT * FROM
(SELECT p.*, 0 AS \"temporary\" FROM \"sys\".\"_tables\" AS p UNION ALL SELECT
t.*, 1 AS \"temporary\" FROM \"tmp\".\"_tables\" AS t) AS tables where
tables.type <> 2;", 1);
+ t = mvc_create_view(m, s, "tables", SQL_PERSIST, "SELECT * FROM
(SELECT p.*, 0 AS \"temporary\", CAST(CASE WHEN system THEN type + 10 /* system
table/view */ ELSE (CASE WHEN commit_action = 0 THEN type /* table/view */ ELSE
type + 20 /* global temp table */ END) END AS SMALLINT) AS table_type FROM
\"sys\".\"_tables\" AS p UNION ALL SELECT t.*, 1 AS \"temporary\", CAST(type +
30 /* local temp table */ AS SMALLINT) AS table_type FROM \"tmp\".\"_tables\"
AS t) AS tables where tables.type <> 2;", 1);
mvc_create_column_(m, t, "id", "int", 32);
mvc_create_column_(m, t, "name", "varchar", 1024);
mvc_create_column_(m, t, "schema_id", "int", 32);
@@ -71,6 +71,7 @@ mvc_init(int debug, store_type store, in
mvc_create_column_(m, t, "commit_action", "smallint", 16);
mvc_create_column_(m, t, "access", "smallint", 16);
mvc_create_column_(m, t, "temporary", "smallint", 16);
+ mvc_create_column_(m, t, "table_type", "smallint", 16);
if (!first) {
int pub = ROLE_PUBLIC;
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
