Changeset: d755d10114b1 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=d755d10114b1
Modified Files:
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64.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.32bit
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
sql/test/emptydb/Tests/check.SQL.py
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.32bit
sql/test/emptydb/Tests/check.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.32bit
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade/Tests/upgrade.stable.out
sql/test/testdb-upgrade/Tests/upgrade.stable.out.32bit
sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128
Branch: default
Log Message:
Fix check code to better normalize queries; approve upgrade tests.
diffs (truncated from 19984 to 300 lines):
diff --git a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
@@ -4228,113 +4228,113 @@ BEGIN
ELSE 'CHARACTER(' || digits || ')'
END
WHEN 'clob' THEN
- CASE digits
- WHEN 0 THEN 'CHARACTER LARGE OBJECT'
- ELSE 'CHARACTER LARGE OBJECT(' || digits || ')'
- END
+ CASE digits
+ WHEN 0 THEN 'CHARACTER LARGE OBJECT'
+ ELSE 'CHARACTER LARGE OBJECT(' || digits || ')'
+ END
WHEN 'date' THEN 'DATE'
WHEN 'day_interval' THEN 'INTERVAL DAY'
WHEN ctype = 'decimal' THEN
- CASE
- WHEN (digits = 1 AND tscale = 0) OR digits = 0 THEN 'DECIMAL'
- WHEN tscale = 0 THEN 'DECIMAL(' || digits || ')'
- WHEN digits = 39 THEN 'DECIMAL(' || 38 || ',' || tscale || ')'
- WHEN digits = 19 AND (SELECT COUNT(*) = 0 FROM sys.types WHERE
sqlname = 'hugeint' ) THEN 'DECIMAL(' || 18 || ',' || tscale || ')'
- ELSE 'DECIMAL(' || digits || ',' || tscale || ')'
- END
+ CASE
+ WHEN (digits = 1 AND tscale = 0) OR digits = 0 THEN 'DECIMAL'
+ WHEN tscale = 0 THEN 'DECIMAL(' || digits || ')'
+ WHEN digits = 39 THEN 'DECIMAL(' || 38 || ',' || tscale || ')'
+ WHEN digits = 19 AND (SELECT COUNT(*) = 0 FROM sys.types WHERE sqlname =
'hugeint' ) THEN 'DECIMAL(' || 18 || ',' || tscale || ')'
+ ELSE 'DECIMAL(' || digits || ',' || tscale || ')'
+ END
WHEN 'double' THEN
- CASE
- WHEN digits = 53 and tscale = 0 THEN 'DOUBLE'
- WHEN tscale = 0 THEN 'FLOAT(' || digits || ')'
- ELSE 'FLOAT(' || digits || ',' || tscale || ')'
- END
+ CASE
+ WHEN digits = 53 and tscale = 0 THEN 'DOUBLE'
+ WHEN tscale = 0 THEN 'FLOAT(' || digits || ')'
+ ELSE 'FLOAT(' || digits || ',' || tscale || ')'
+ END
WHEN 'geometry' THEN
- CASE digits
- WHEN 4 THEN 'GEOMETRY(POINT' ||
+ CASE digits
+ WHEN 4 THEN 'GEOMETRY(POINT' ||
CASE tscale
WHEN 0 THEN ''
ELSE ',' || tscale
END || ')'
- WHEN 8 THEN 'GEOMETRY(LINESTRING' ||
+ WHEN 8 THEN 'GEOMETRY(LINESTRING' ||
CASE tscale
WHEN 0 THEN ''
ELSE ',' || tscale
END || ')'
- WHEN 16 THEN 'GEOMETRY(POLYGON' ||
+ WHEN 16 THEN 'GEOMETRY(POLYGON' ||
CASE tscale
WHEN 0 THEN ''
ELSE ',' || tscale
END || ')'
- WHEN 20 THEN 'GEOMETRY(MULTIPOINT' ||
+ WHEN 20 THEN 'GEOMETRY(MULTIPOINT' ||
CASE tscale
WHEN 0 THEN ''
ELSE ',' || tscale
END || ')'
- WHEN 24 THEN 'GEOMETRY(MULTILINESTRING' ||
+ WHEN 24 THEN 'GEOMETRY(MULTILINESTRING' ||
CASE tscale
WHEN 0 THEN ''
ELSE ',' || tscale
END || ')'
- WHEN 28 THEN 'GEOMETRY(MULTIPOLYGON' ||
+ WHEN 28 THEN 'GEOMETRY(MULTIPOLYGON' ||
CASE tscale
WHEN 0 THEN ''
ELSE ',' || tscale
END || ')'
- WHEN 32 THEN 'GEOMETRY(GEOMETRYCOLLECTION' ||
+ WHEN 32 THEN 'GEOMETRY(GEOMETRYCOLLECTION' ||
CASE tscale
WHEN 0 THEN ''
ELSE ',' || tscale
END || ')'
- ELSE 'GEOMETRY'
+ ELSE 'GEOMETRY'
END
WHEN 'hugeint' THEN 'HUGEINT'
WHEN 'int' THEN 'INTEGER'
WHEN 'month_interval' THEN
- CASE digits
- WHEN 1 THEN 'INTERVAL YEAR'
- WHEN 2 THEN 'INTERVAL YEAR TO MONTH'
- WHEN 3 THEN 'INTERVAL MONTH'
- END
+ CASE digits
+ WHEN 1 THEN 'INTERVAL YEAR'
+ WHEN 2 THEN 'INTERVAL YEAR TO MONTH'
+ WHEN 3 THEN 'INTERVAL MONTH'
+ END
WHEN 'real' THEN
- CASE
- WHEN digits = 24 and tscale = 0 THEN 'REAL'
- WHEN tscale = 0 THEN 'FLOAT(' || digits || ')'
- ELSE 'FLOAT(' || digits || ',' || tscale || ')'
- END
+ CASE
+ WHEN digits = 24 and tscale = 0 THEN 'REAL'
+ WHEN tscale = 0 THEN 'FLOAT(' || digits || ')'
+ ELSE 'FLOAT(' || digits || ',' || tscale || ')'
+ 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
+ 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 'smallint' THEN 'SMALLINT'
WHEN 'time' THEN
- CASE digits
- WHEN 1 THEN 'TIME'
- ELSE 'TIME(' || (digits - 1) || ')'
- END
+ CASE digits
+ WHEN 1 THEN 'TIME'
+ ELSE 'TIME(' || (digits - 1) || ')'
+ END
WHEN 'timestamp' THEN
- CASE digits
- WHEN 7 THEN 'TIMESTAMP'
- ELSE 'TIMESTAMP(' || (digits - 1) || ')'
- END
+ CASE digits
+ WHEN 7 THEN 'TIMESTAMP'
+ ELSE 'TIMESTAMP(' || (digits - 1) || ')'
+ END
WHEN 'timestamptz' THEN
- CASE digits
- WHEN 7 THEN 'TIMESTAMP'
- ELSE 'TIMESTAMP(' || (digits - 1) || ')'
- END || ' WITH TIME ZONE'
+ CASE digits
+ WHEN 7 THEN 'TIMESTAMP'
+ ELSE 'TIMESTAMP(' || (digits - 1) || ')'
+ END || ' WITH TIME ZONE'
WHEN 'timetz' THEN
- CASE digits
- WHEN 1 THEN 'TIME'
- ELSE 'TIME(' || (digits - 1) || ')'
- END || ' WITH TIME ZONE'
+ CASE digits
+ WHEN 1 THEN 'TIME'
+ ELSE 'TIME(' || (digits - 1) || ')'
+ END || ' WITH TIME ZONE'
WHEN 'tinyint' THEN 'TINYINT'
WHEN 'varchar' THEN 'CHARACTER VARYING(' || digits || ')'
ELSE
@@ -4342,49 +4342,768 @@ BEGIN
WHEN lower(ctype) = ctype THEN upper(ctype)
ELSE '"' || ctype || '"'
END || CASE digits
- WHEN 0 THEN ''
+ WHEN 0 THEN ''
ELSE '(' || digits || CASE tscale
- WHEN 0 THEN ''
+ WHEN 0 THEN ''
ELSE ',' || tscale
END || ')'
- END
+ END
END;
END;
+
+CREATE FUNCTION sys.SQ (s STRING) RETURNS STRING BEGIN RETURN ' ''' || s ||
''' '; END;
+CREATE FUNCTION sys.DQ (s STRING) RETURNS STRING BEGIN RETURN '"' || s || '"';
END; --TODO: Figure out why this breaks with the space
+CREATE FUNCTION sys.FQN(s STRING, t STRING) RETURNS STRING BEGIN RETURN DQ(s)
|| '.' || DQ(t); END;
+CREATE FUNCTION sys.ALTER_TABLE(s STRING, t STRING) RETURNS STRING BEGIN
RETURN 'ALTER TABLE ' || FQN(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 sys.replace_first(ori STRING, pat STRING, rep STRING, flg
STRING) RETURNS STRING EXTERNAL NAME "pcre"."replace_first";
+CREATE FUNCTION sys.schema_guard(sch STRING, nme STRING, stmt STRING) RETURNS
STRING BEGIN
+RETURN
+ SELECT replace_first(stmt, '(\\s*"?' || sch || '"?\\s*\\.|)\\s*"?' || nme
|| '"?\\s*', ' ' || FQN(sch, nme) || ' ', 'imsx');
+END;
+
+CREATE VIEW sys.describe_constraints AS
+ SELECT
+ s.name sch,
+ t.name tbl,
+ kc.name col,
+ k.name con,
+ CASE WHEN k.type = 0 THEN 'PRIMARY KEY' WHEN k.type = 1 THEN 'UNIQUE'
END tpe
+ 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);
+
+CREATE VIEW sys.describe_indices AS
+ WITH it (id, idx) AS (VALUES (0, 'INDEX'), (4, 'IMPRINTS INDEX'), (5,
'ORDERED INDEX')) --UNIQUE INDEX wraps to INDEX.
+ SELECT
+ i.name ind,
+ s.name sch,
+ t.name tbl,
+ c.name col,
+ it.idx tpe
+ 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;
+
+CREATE VIEW sys.describe_column_defaults AS
+ SELECT
+ s.name sch,
+ t.name tbl,
+ c.name col,
+ c."default" def
+ 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;
+
+CREATE VIEW sys.describe_foreign_keys AS
+ WITH action_type (id, act) AS (VALUES
+ (0, 'NO ACTION'),
+ (1, 'CASCADE'),
+ (2, 'RESTRICT'),
+ (3, 'SET NULL'),
+ (4, 'SET DEFAULT'))
+ SELECT
+ fs.name fk_s,
+ fkt.name fk_t,
+ fkkc.name fk_c,
+ fkkc.nr o,
+ fkk.name fk,
+ ps.name pk_s,
+ pkt.name pk_t,
+ pkkc.name pk_c,
+ ou.act on_update,
+ od.act 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
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
