Changeset: b69176fba0d0 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=b69176fba0d0
Added Files:
sql/scripts/53_dump.sql
Modified Files:
dump.sql
dump_output.sql
sql/backends/monet5/CMakeLists.txt
sql/scripts/52_describe.sql
sql/scripts/CMakeLists.txt
Branch: monetdbe-proxy
Log Message:
Migrate most of the describe and dump functions to sql/scripts.
diffs (truncated from 1419 to 300 lines):
diff --git a/dump.sql b/dump.sql
--- a/dump.sql
+++ b/dump.sql
@@ -1,556 +1,8 @@
START TRANSACTION;
---We start with creating static versions of catalogue tables that are going to
be affected by this dump script itself.
-CREATE TEMPORARY TABLE _user_sequences AS SELECT * FROM sys.sequences;
-CREATE TEMPORARY TABLE _user_functions AS SELECT * FROM sys.functions f WHERE
NOT f.system;
-
-CREATE FUNCTION SQ (s STRING) RETURNS STRING BEGIN RETURN ' ''' || s || ''' ';
END;
-CREATE FUNCTION DQ (s STRING) RETURNS STRING BEGIN RETURN '"' || s || '"';
END; --TODO: Figure out why this breaks with the space
-CREATE FUNCTION FQTN(s STRING, t STRING) RETURNS STRING BEGIN RETURN DQ(s) ||
'.' || DQ(t); END;
-CREATE FUNCTION ALTER_TABLE(s STRING, t STRING) RETURNS STRING BEGIN RETURN
'ALTER TABLE ' || FQTN(s, t) || ' '; END;
-
---We need pcre to implement a header guard which means adding the schema of an
object explicitely to its identifier.
-CREATE FUNCTION replace_first(ori STRING, pat STRING, rep STRING, flg STRING)
RETURNS STRING EXTERNAL NAME "pcre"."replace_first";
-CREATE FUNCTION schema_guard(sch STRING, nme STRING, stmt STRING) RETURNS
STRING BEGIN
-RETURN
- SELECT replace_first(stmt, '(\\s*"?' || sch || '"?\\s*\\.|)\\s*"?' ||
nme || '"?\\s*', ' ' || FQTN(sch, nme) || ' ', 'imsx');
-END;
-
-CREATE FUNCTION dump_type(type STRING, digits INT, scale INT) RETURNS STRING
BEGIN
- RETURN
- CASE
- WHEN type = 'boolean' THEN 'BOOLEAN'
- WHEN type = 'int' THEN 'INTEGER'
- WHEN type = 'smallint' THEN 'SMALLINT'
- WHEN type = 'tinyiny' THEN 'TINYINT'
- WHEN type = 'bigint' THEN 'BIGINT'
- WHEN type = 'hugeint' THEN 'HUGEINT'
- WHEN type = 'date' THEN 'DATE'
- WHEN type = 'month_interval' THEN CASE
- WHEN digits = 1 THEN 'INTERVAL YEAR'
- WHEN digits = 2 THEN 'INTERVAL YEAR TO MONTH'
- ELSE 'INTERVAL MONTH' --ASSUMES digits = 3
- END
- WHEN type LIKE '%_INTERVAL' THEN CASE
- WHEN digits = 4 THEN 'INTERVAL DAY'
- WHEN digits = 5 THEN 'INTERVAL DAY TO HOUR'
- WHEN digits = 6 THEN 'INTERVAL DAY TO MINUTE'
- WHEN digits = 7 THEN 'INTERVAL DAY TO SECOND'
- WHEN digits = 8 THEN 'INTERVAL HOUR'
- WHEN digits = 9 THEN 'INTERVAL HOUR TO MINUTE'
- WHEN digits = 10 THEN 'INTERVAL HOUR TO SECOND'
- WHEN digits = 11 THEN 'INTERVAL MINUTE'
- WHEN digits = 12 THEN 'INTERVAL MINUTE TO SECOND'
- ELSE 'INTERVAL SECOND' --ASSUMES digits = 13
- END
- WHEN type = 'varchar' OR type = 'clob' THEN CASE
- WHEN digits = 0 THEN 'CHARACTER LARGE OBJECT'
- ELSE 'CHARACTER LARGE OBJECT(' || digits || ')'
--ASSUMES digits IS NOT NULL
- END
- WHEN type = 'blob' THEN CASE
- WHEN digits = 0 THEN 'BINARY LARGE OBJECT'
- ELSE 'BINARY LARGE OBJECT(' || digits || ')' --ASSUMES
digits IS NOT NULL
- END
- WHEN type = 'timestamp' THEN 'TIMESTAMP' || ifthenelse(digits
<> 7, '(' || (digits -1) || ') ', ' ')
- WHEN type = 'timestamptz' THEN 'TIMESTAMP' || ifthenelse(digits
<> 7, '(' || (digits -1) || ') ', ' ') || 'WITH TIME ZONE'
- WHEN type = 'time' THEN 'TIME' || ifthenelse(digits <> 1, '('
|| (digits -1) || ') ', ' ')
- WHEN type = 'timetz' THEN 'TIME' || ifthenelse(digits <> 1, '('
|| (digits -1) || ') ', ' ') || 'WITH TIME ZONE'
- WHEN type = 'real' THEN CASE
- WHEN digits = 24 AND scale=0 THEN 'REAL'
- WHEN scale=0 THEN 'FLOAT(' || digits || ')'
- ELSE 'FLOAT(' || digits || ',' || scale || ')'
- END
- WHEN type = 'double' THEN CASE
- WHEN digits = 53 AND scale=0 THEN 'DOUBLE'
- WHEN scale = 0 THEN 'FLOAT(' || digits || ')'
- ELSE 'FLOAT(' || digits || ',' || scale || ')'
- END
- WHEN type = 'decimal' THEN CASE
- WHEN (digits = 1 AND scale = 0) OR digits = 0 THEN
'DECIMAL'
- WHEN scale = 0 THEN 'DECIMAL(' || digits || ')'
- WHEN digits = 39 THEN 'DECIMAL(' || 38 || ',' || scale
|| ')'
- WHEN digits = 19 AND (SELECT COUNT(*) = 0 FROM
sys.types WHERE sqlname = 'hugeint' ) THEN 'DECIMAL(' || 18 || ',' || scale ||
')'
- ELSE 'DECIMAL(' || digits || ',' || scale || ')'
- END
- ELSE upper(type) || '(' || digits || ',' || scale || ')'
--TODO: might be a bit too simple
- END;
-END;
-
-CREATE FUNCTION dump_CONSTRAINT_type_name(id INT) RETURNS STRING BEGIN
- RETURN
- CASE
- WHEN id = 0 THEN 'PRIMARY KEY'
- WHEN id = 1 THEN 'UNIQUE'
- END;
-END;
-
-CREATE FUNCTION describe_constraints() RETURNS TABLE(s STRING, "table" STRING,
nr INT, col STRING, con STRING, type STRING) BEGIN
- RETURN
- SELECT s.name, t.name, kc.nr, kc.name, k.name,
dump_CONSTRAINT_type_name(k.type)
- FROM sys.schemas s, sys._tables t, sys.objects kc, sys.keys k
- WHERE kc.id = k.id
- AND k.table_id = t.id
- AND s.id = t.schema_id
- AND t.system = FALSE
- AND k.type in (0, 1)
- AND t.type IN (0, 6);
-END;
-
-CREATE FUNCTION dump_table_constraint_type() RETURNS TABLE(stm STRING) BEGIN
- RETURN
- SELECT
- 'ALTER TABLE ' || DQ(s) || '.' || DQ("table") ||
- ' ADD CONSTRAINT ' || DQ(con) || ' '||
- type || ' (' || GROUP_CONCAT(DQ(col), ', ') || ');'
- FROM describe_constraints() GROUP BY s, "table", con, type;
-END;
-
-CREATE FUNCTION describe_indices() RETURNS TABLE (i STRING, o INT, s STRING, t
STRING, c STRING, it STRING) BEGIN
-RETURN
- WITH it (id, idx) AS (VALUES (0, 'INDEX'), (4, 'IMPRINTS INDEX'), (5,
'ORDERED INDEX')) --UNIQUE INDEX wraps to INDEX.
- SELECT
- i.name,
- kc.nr, --TODO: Does this determine the concatenation order?
- s.name,
- t.name,
- c.name,
- it.idx
- FROM
- sys.idxs AS i LEFT JOIN sys.keys AS k ON i.name = k.name,
- sys.objects AS kc,
- sys._columns AS c,
- sys.schemas s,
- sys._tables AS t,
- it
- WHERE
- i.table_id = t.id
- AND i.id = kc.id
- AND kc.name = c.name
- AND t.id = c.table_id
- AND t.schema_id = s.id
- AND k.type IS NULL
- AND i.type = it.id
- ORDER BY i.name, kc.nr;
-END;
-
-CREATE FUNCTION dump_indices() RETURNS TABLE(stm STRING) BEGIN
- RETURN
- SELECT
- 'CREATE ' || it || ' ' ||
- DQ(i) || ' ON ' || DQ(s) || '.' || DQ(t) ||
- '(' || GROUP_CONCAT(c) || ');'
- FROM describe_indices() GROUP BY i, it, s, t;
-END;
-
-CREATE FUNCTION dump_column_definition(tid INT) RETURNS STRING BEGIN
- RETURN
- SELECT
- ' (' ||
- GROUP_CONCAT(
- DQ(c.name) || ' ' ||
- dump_type(c.type, c.type_digits, c.type_scale)
||
- ifthenelse(c."null" = 'false', ' NOT NULL', '')
- , ', ') || ')'
- FROM sys._columns c
- WHERE c.table_id = tid;
-END;
-
-CREATE FUNCTION dump_remote_table_expressions(s STRING, t STRING) RETURNS
STRING BEGIN
- RETURN SELECT ' ON ' || SQ(uri) || ' WITH USER ' || SQ(username) || '
ENCRYPTED PASSWORD ' || SQ("hash") FROM sys.remote_table_credentials(s ||'.' ||
t);
-END;
-
-CREATE FUNCTION dump_merge_table_partition_expressions(tid INT) RETURNS STRING
-BEGIN
- RETURN
- SELECT
- CASE WHEN tp.table_id IS NOT NULL THEN --updatable
merge table
- ' PARTITION BY ' ||
- CASE
- WHEN bit_and(tp.type, 2) = 2
- THEN 'VALUES '
- ELSE 'RANGE '
- END ||
- CASE
- WHEN bit_and(tp.type, 4) = 4 --column
expression
- THEN 'ON ' || '(' || (SELECT DQ(c.name)
|| ')' FROM sys.columns c WHERE c.id = tp.column_id)
- ELSE 'USING ' || '(' || tp.expression
|| ')' --generic expression
- END
- ELSE
--read only partition merge table.
- ''
- END
- FROM (VALUES (tid)) t(id) LEFT JOIN sys.table_partitions tp ON
t.id = tp.table_id;
-END;
-
-CREATE FUNCTION describe_column_defaults() RETURNS TABLE(sch STRING, tbl
STRING, col STRING, def STRING) BEGIN
-RETURN
- SELECT
- s.name,
- t.name,
- c.name,
- c."default"
- FROM schemas s, tables t, columns c
- WHERE
- s.id = t.schema_id AND
- t.id = c.table_id AND
- s.name <> 'tmp' AND
- NOT t.system AND
- c."default" IS NOT NULL;
-END;
-
-CREATE FUNCTION dump_column_defaults() RETURNS TABLE(stmt STRING) BEGIN
- RETURN
- SELECT 'ALTER TABLE ' || FQTN(sch, tbl) || ' ALTER COLUMN ' ||
DQ(col) || ' SET DEFAULT ' || def || ';'
- FROM describe_column_defaults();
-END;
-
---SELECT * FROM dump_foreign_keys();
-CREATE FUNCTION describe_foreign_keys() RETURNS TABLE(
- fk_s STRING, fk_t STRING, fk_c STRING,
- o INT, fk STRING,
- pk_s STRING, pk_t STRING, pk_c STRING,
- on_update STRING, on_delete STRING) BEGIN
-
- RETURN
- WITH action_type (id, act) AS (VALUES
- (0, 'NO ACTION'),
- (1, 'CASCADE'),
- (2, 'RESTRICT'),
- (3, 'SET NULL'),
- (4, 'SET DEFAULT'))
- SELECT
- fs.name AS fsname, fkt.name AS ktname, fkkc.name AS fcname,
- fkkc.nr AS o, fkk.name AS fkname,
- ps.name AS psname, pkt.name AS ptname, pkkc.name AS pcname,
- ou.act as on_update, od.act as on_delete
- FROM sys._tables fkt,
- sys.objects fkkc,
- sys.keys fkk,
- sys._tables pkt,
- sys.objects pkkc,
- sys.keys pkk,
- sys.schemas ps,
- sys.schemas fs,
- action_type ou,
- action_type od
-
- WHERE fkt.id = fkk.table_id
- AND pkt.id = pkk.table_id
- AND fkk.id = fkkc.id
- AND pkk.id = pkkc.id
- AND fkk.rkey = pkk.id
- AND fkkc.nr = pkkc.nr
- AND pkt.schema_id = ps.id
- AND fkt.schema_id = fs.id
- AND (fkk."action" & 255) = od.id
- AND ((fkk."action" >> 8) & 255) = ou.id
- ORDER BY fkk.name, fkkc.nr;
-END;
-
-CREATE FUNCTION dump_foreign_keys() RETURNS TABLE(stmt STRING) BEGIN
-RETURN
- SELECT
- 'ALTER TABLE ' || DQ(fk_s) || '.'|| DQ(fk_t) || ' ADD
CONSTRAINT ' || DQ(fk) || ' ' ||
- 'FOREIGN KEY(' || GROUP_CONCAT(DQ(fk_c), ',') ||') ' ||
- 'REFERENCES ' || DQ(pk_s) || '.' || DQ(pk_t) || '(' ||
GROUP_CONCAT(DQ(pk_c), ',') || ') ' ||
- 'ON DELETE ' || on_delete || ' ON UPDATE ' || on_update ||
- ';'
- FROM describe_foreign_keys() GROUP BY fk_s, fk_t, pk_s, pk_t, fk,
on_delete, on_update;
-END;
-
-CREATE FUNCTION describe_partition_tables()
-RETURNS TABLE(
- m_sname STRING,
- m_tname STRING,
- p_sname STRING,
- p_tname STRING,
- p_type STRING,
- pvalues STRING,
- minimum STRING,
- maximum STRING,
- with_nulls BOOLEAN) BEGIN
-RETURN
- SELECT
- m_sname,
- m_tname,
- p_sname,
- p_tname,
- CASE
- WHEN p_raw_type IS NULL THEN 'READ ONLY'
- WHEN (p_raw_type = 'VALUES' AND pvalues IS NULL) OR (p_raw_type =
'RANGE' AND minimum IS NULL AND maximum IS NULL AND with_nulls) THEN 'FOR NULLS'
- ELSE p_raw_type
- END AS p_type,
- pvalues,
- minimum,
- maximum,
- with_nulls
- FROM
- (WITH
- tp("type", table_id) AS
- (SELECT CASE WHEN (table_partitions."type" & 2) = 2 THEN
'VALUES' ELSE 'RANGE' END, table_partitions.table_id FROM table_partitions),
- subq(m_tid, p_mid, "type", m_sname, m_tname, p_sname, p_tname)
AS
- (SELECT m_t.id, p_m.id, m_t."type", m_s.name, m_t.name,
p_s.name, p_m.name
- FROM schemas m_s, sys._tables m_t, dependencies d, schemas p_s,
sys._tables p_m
- WHERE m_t."type" IN (3, 6)
- AND m_t.schema_id = m_s.id
- AND m_s.name <> 'tmp'
- AND m_t.system = FALSE
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
