Changeset: 22336d8b2076 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=22336d8b2076 Modified Files: sql/backends/monet5/sql_upgrades.c sql/scripts/76_dump.sql Branch: monetdbe-proxy Log Message:
Add upgrade code for describe and dump.
diffs (truncated from 1006 to 300 lines):
diff --git a/sql/backends/monet5/sql_upgrades.c
b/sql/backends/monet5/sql_upgrades.c
--- a/sql/backends/monet5/sql_upgrades.c
+++ b/sql/backends/monet5/sql_upgrades.c
@@ -2549,7 +2549,7 @@ sql_update_oct2020_sp1(Client c, mvc *sq
static str
sql_update_default(Client c, mvc *sql, const char *prev_schema, bool
*systabfixed)
{
- size_t bufsize = 8192, pos = 0;
+ size_t bufsize = 65536, pos = 0;
char *buf = NULL, *err = NULL;
res_table *output = NULL;
BAT *b = NULL;
@@ -2602,113 +2602,113 @@ sql_update_default(Client c, mvc *sql, c
" ELSE 'CHARACTER(' || digits
|| ')'\n"
" END\n"
" WHEN 'clob' THEN\n"
- " CASE digits\n"
- " WHEN 0 THEN 'CHARACTER LARGE
OBJECT'\n"
- " ELSE 'CHARACTER LARGE
OBJECT(' || digits || ')'\n"
- " END\n"
+ " CASE digits\n"
+ " WHEN 0 THEN 'CHARACTER LARGE
OBJECT'\n"
+ " ELSE 'CHARACTER LARGE OBJECT('
|| digits || ')'\n"
+ " END\n"
" WHEN 'date' THEN 'DATE'\n"
" WHEN 'day_interval' THEN
'INTERVAL DAY'\n"
" WHEN ctype = 'decimal' THEN\n"
- " CASE\n"
- " WHEN (digits = 1 AND tscale
= 0) OR digits = 0 THEN 'DECIMAL'\n"
- " WHEN tscale = 0 THEN
'DECIMAL(' || digits || ')'\n"
- " WHEN digits = 39 THEN
'DECIMAL(' || 38 || ',' || tscale || ')'\n"
- " WHEN digits = 19 AND (SELECT
COUNT(*) = 0 FROM sys.types WHERE sqlname = 'hugeint' ) THEN 'DECIMAL(' || 18
|| ',' || tscale || ')'\n"
- " ELSE 'DECIMAL(' || digits ||
',' || tscale || ')'\n"
- " END\n"
+ " CASE\n"
+ " WHEN (digits = 1 AND tscale = 0)
OR digits = 0 THEN 'DECIMAL'\n"
+ " WHEN tscale = 0 THEN 'DECIMAL('
|| digits || ')'\n"
+ " WHEN digits = 39 THEN 'DECIMAL('
|| 38 || ',' || tscale || ')'\n"
+ " WHEN digits = 19 AND (SELECT
COUNT(*) = 0 FROM sys.types WHERE sqlname = 'hugeint' ) THEN 'DECIMAL(' || 18
|| ',' || tscale || ')'\n"
+ " ELSE 'DECIMAL(' || digits || ','
|| tscale || ')'\n"
+ " END\n"
" WHEN 'double' THEN\n"
- " CASE\n"
- " WHEN digits = 53 and tscale
= 0 THEN 'DOUBLE'\n"
- " WHEN tscale = 0 THEN
'FLOAT(' || digits || ')'\n"
- " ELSE 'FLOAT(' || digits ||
',' || tscale || ')'\n"
- " END\n"
+ " CASE\n"
+ " WHEN digits = 53 and tscale = 0
THEN 'DOUBLE'\n"
+ " WHEN tscale = 0 THEN 'FLOAT(' ||
digits || ')'\n"
+ " ELSE 'FLOAT(' || digits || ','
|| tscale || ')'\n"
+ " END\n"
" WHEN 'geometry' THEN\n"
- " CASE digits\n"
- " WHEN 4 THEN 'GEOMETRY(POINT'
||\n"
+ " CASE digits\n"
+ " WHEN 4 THEN 'GEOMETRY(POINT'
||\n"
" CASE tscale\n"
" WHEN 0 THEN ''\n"
" ELSE ',' || tscale\n"
" END || ')'\n"
- " WHEN 8 THEN
'GEOMETRY(LINESTRING' ||\n"
+ " WHEN 8 THEN
'GEOMETRY(LINESTRING' ||\n"
" CASE tscale\n"
" WHEN 0 THEN ''\n"
" ELSE ',' || tscale\n"
" END || ')'\n"
- " WHEN 16 THEN
'GEOMETRY(POLYGON' ||\n"
+ " WHEN 16 THEN 'GEOMETRY(POLYGON'
||\n"
" CASE tscale\n"
" WHEN 0 THEN ''\n"
" ELSE ',' || tscale\n"
" END || ')'\n"
- " WHEN 20 THEN
'GEOMETRY(MULTIPOINT' ||\n"
+ " WHEN 20 THEN
'GEOMETRY(MULTIPOINT' ||\n"
" CASE tscale\n"
" WHEN 0 THEN ''\n"
" ELSE ',' || tscale\n"
" END || ')'\n"
- " WHEN 24 THEN
'GEOMETRY(MULTILINESTRING' ||\n"
+ " WHEN 24 THEN
'GEOMETRY(MULTILINESTRING' ||\n"
" CASE tscale\n"
" WHEN 0 THEN ''\n"
" ELSE ',' || tscale\n"
" END || ')'\n"
- " WHEN 28 THEN
'GEOMETRY(MULTIPOLYGON' ||\n"
+ " WHEN 28 THEN
'GEOMETRY(MULTIPOLYGON' ||\n"
" CASE tscale\n"
" WHEN 0 THEN ''\n"
" ELSE ',' || tscale\n"
" END || ')'\n"
- " WHEN 32 THEN
'GEOMETRY(GEOMETRYCOLLECTION' ||\n"
+ " WHEN 32 THEN
'GEOMETRY(GEOMETRYCOLLECTION' ||\n"
" CASE tscale\n"
" WHEN 0 THEN ''\n"
" ELSE ',' || tscale\n"
" END || ')'\n"
- " ELSE 'GEOMETRY'\n"
+ " ELSE 'GEOMETRY'\n"
" END\n"
" WHEN 'hugeint' THEN 'HUGEINT'\n"
" WHEN 'int' THEN 'INTEGER'\n"
" WHEN 'month_interval' THEN\n"
- " CASE digits\n"
- " WHEN 1 THEN 'INTERVAL
YEAR'\n"
- " WHEN 2 THEN 'INTERVAL YEAR
TO MONTH'\n"
- " WHEN 3 THEN 'INTERVAL
MONTH'\n"
- " END\n"
+ " CASE digits\n"
+ " WHEN 1 THEN 'INTERVAL YEAR'\n"
+ " WHEN 2 THEN 'INTERVAL YEAR TO
MONTH'\n"
+ " WHEN 3 THEN 'INTERVAL MONTH'\n"
+ " END\n"
" WHEN 'real' THEN\n"
- " CASE\n"
- " WHEN digits = 24 and tscale
= 0 THEN 'REAL'\n"
- " WHEN tscale = 0 THEN
'FLOAT(' || digits || ')'\n"
- " ELSE 'FLOAT(' || digits ||
',' || tscale || ')'\n"
- " END\n"
+ " CASE\n"
+ " WHEN digits = 24 and tscale = 0
THEN 'REAL'\n"
+ " WHEN tscale = 0 THEN 'FLOAT(' ||
digits || ')'\n"
+ " ELSE 'FLOAT(' || digits || ','
|| tscale || ')'\n"
+ " END\n"
" WHEN 'sec_interval' THEN\n"
- " CASE digits\n"
- " WHEN 4 THEN 'INTERVAL DAY'\n"
- " WHEN 5 THEN 'INTERVAL DAY TO
HOUR'\n"
- " WHEN 6 THEN 'INTERVAL DAY TO
MINUTE'\n"
- " WHEN 7 THEN 'INTERVAL DAY TO
SECOND'\n"
- " WHEN 8 THEN 'INTERVAL
HOUR'\n"
- " WHEN 9 THEN 'INTERVAL HOUR
TO MINUTE'\n"
- " WHEN 10 THEN 'INTERVAL HOUR
TO SECOND'\n"
- " WHEN 11 THEN 'INTERVAL
MINUTE'\n"
- " WHEN 12 THEN 'INTERVAL
MINUTE TO SECOND'\n"
- " WHEN 13 THEN 'INTERVAL
SECOND'\n"
- " END\n"
+ " CASE digits\n"
+ " WHEN 4 THEN 'INTERVAL DAY'\n"
+ " WHEN 5 THEN 'INTERVAL DAY TO
HOUR'\n"
+ " WHEN 6 THEN 'INTERVAL DAY TO
MINUTE'\n"
+ " WHEN 7 THEN 'INTERVAL DAY TO
SECOND'\n"
+ " WHEN 8 THEN 'INTERVAL HOUR'\n"
+ " WHEN 9 THEN 'INTERVAL HOUR TO
MINUTE'\n"
+ " WHEN 10 THEN 'INTERVAL HOUR TO
SECOND'\n"
+ " WHEN 11 THEN 'INTERVAL MINUTE'\n"
+ " WHEN 12 THEN 'INTERVAL MINUTE TO
SECOND'\n"
+ " WHEN 13 THEN 'INTERVAL SECOND'\n"
+ " END\n"
" WHEN 'smallint' THEN
'SMALLINT'\n"
" WHEN 'time' THEN\n"
- " CASE digits\n"
- " WHEN 1 THEN 'TIME'\n"
- " ELSE 'TIME(' || (digits - 1)
|| ')'\n"
- " END\n"
+ " CASE digits\n"
+ " WHEN 1 THEN 'TIME'\n"
+ " ELSE 'TIME(' || (digits - 1) ||
')'\n"
+ " END\n"
" WHEN 'timestamp' THEN\n"
- " CASE digits\n"
- " WHEN 7 THEN 'TIMESTAMP'\n"
- " ELSE 'TIMESTAMP(' || (digits
- 1) || ')'\n"
- " END\n"
+ " CASE digits\n"
+ " WHEN 7 THEN 'TIMESTAMP'\n"
+ " ELSE 'TIMESTAMP(' || (digits -
1) || ')'\n"
+ " END\n"
" WHEN 'timestamptz' THEN\n"
- " CASE digits\n"
- " WHEN 7 THEN 'TIMESTAMP'\n"
- " ELSE 'TIMESTAMP(' || (digits
- 1) || ')'\n"
- " END || ' WITH TIME ZONE'\n"
+ " CASE digits\n"
+ " WHEN 7 THEN 'TIMESTAMP'\n"
+ " ELSE 'TIMESTAMP(' || (digits -
1) || ')'\n"
+ " END || ' WITH TIME ZONE'\n"
" WHEN 'timetz' THEN\n"
- " CASE digits\n"
- " WHEN 1 THEN 'TIME'\n"
- " ELSE 'TIME(' || (digits - 1)
|| ')'\n"
- " END || ' WITH TIME ZONE'\n"
+ " CASE digits\n"
+ " WHEN 1 THEN 'TIME'\n"
+ " ELSE 'TIME(' || (digits - 1) ||
')'\n"
+ " END || ' WITH TIME ZONE'\n"
" WHEN 'tinyint' THEN 'TINYINT'\n"
" WHEN 'varchar' THEN 'CHARACTER
VARYING(' || digits || ')'\n"
" ELSE\n"
@@ -2716,52 +2716,774 @@ sql_update_default(Client c, mvc *sql, c
" WHEN lower(ctype) = ctype
THEN upper(ctype)\n"
" ELSE '\"' || ctype || '\"'\n"
" END || CASE digits\n"
- " WHEN 0 THEN ''\n"
+ " WHEN 0 THEN ''\n"
" ELSE '(' || digits || CASE
tscale\n"
- " WHEN 0 THEN ''\n"
+ " WHEN 0 THEN ''\n"
" ELSE ',' || tscale\n"
" END || ')'\n"
- " END\n"
+ " END\n"
" END;\n"
"END;\n"
+ "\n"
+ "CREATE FUNCTION sys.SQ (s STRING)
RETURNS STRING BEGIN RETURN ' ''' || s || ''' '; END;\n"
+ "CREATE FUNCTION sys.DQ (s STRING)
RETURNS STRING BEGIN RETURN '\"' || s || '\"'; END; --TODO: Figure out why this
breaks with the space\n"
+ "CREATE FUNCTION sys.FQN(s STRING, t
STRING) RETURNS STRING BEGIN RETURN DQ(s) || '.' || DQ(t); END;\n"
+ "CREATE FUNCTION sys.ALTER_TABLE(s
STRING, t STRING) RETURNS STRING BEGIN RETURN 'ALTER TABLE ' || FQN(s, t) || '
'; END;\n"
+ "\n"
+ "--We need pcre to implement a header
guard which means adding the schema of an object explicitely to its
identifier.\n"
+ "CREATE FUNCTION sys.replace_first(ori
STRING, pat STRING, rep STRING, flg STRING) RETURNS STRING EXTERNAL NAME
\"pcre\".\"replace_first\";\n"
+ "CREATE FUNCTION sys.schema_guard(sch
STRING, nme STRING, stmt STRING) RETURNS STRING BEGIN\n"
+ "RETURN\n"
+ " SELECT replace_first(stmt,
'(\\\\s*\"?' || sch || '\"?\\\\s*\\\\.|)\\\\s*\"?' || nme || '\"?\\\\s*', ' '
|| FQN(sch, nme) || ' ', 'imsx');\n"
+ "END;\n"
+ "\n"
+ "CREATE VIEW sys.describe_constraints
AS\n"
+ " SELECT\n"
+ " s.name sch,\n"
+ " t.name tbl,\n"
+ " kc.name col,\n"
+ " k.name con,\n"
+ " CASE WHEN k.type = 0 THEN
'PRIMARY KEY' WHEN k.type = 1 THEN 'UNIQUE' END tpe\n"
+ " FROM sys.schemas s, sys._tables t,
sys.objects kc, sys.keys k\n"
+ " WHERE kc.id = k.id\n"
+ " AND k.table_id = t.id\n"
+ " AND s.id = t.schema_id\n"
+ " AND t.system = FALSE\n"
+ " AND k.type in (0, 1)\n"
+ " AND t.type IN (0, 6);\n"
+ "\n"
+ "CREATE VIEW sys.describe_indices AS\n"
+ " WITH it (id, idx) AS (VALUES (0,
'INDEX'), (4, 'IMPRINTS INDEX'), (5, 'ORDERED INDEX')) --UNIQUE INDEX wraps to
INDEX.\n"
+ " SELECT\n"
+ " i.name ind,\n"
+ " s.name sch,\n"
+ " t.name tbl,\n"
+ " c.name col,\n"
+ " it.idx tpe\n"
+ " FROM\n"
+ " sys.idxs AS i LEFT JOIN
sys.keys AS k ON i.name = k.name,\n"
+ " sys.objects AS kc,\n"
+ " sys._columns AS c,\n"
+ " sys.schemas s,\n"
+ " sys._tables AS t,\n"
+ " it\n"
+ " WHERE\n"
+ " i.table_id = t.id\n"
+ " AND i.id = kc.id\n"
+ " AND kc.name = c.name\n"
+ " AND t.id = c.table_id\n"
+ " AND t.schema_id = s.id\n"
+ " AND k.type IS NULL\n"
+ " AND i.type = it.id\n"
+ " ORDER BY i.name, kc.nr;\n"
+ "\n"
+ "CREATE VIEW
sys.describe_column_defaults AS\n"
+ " SELECT\n"
+ " s.name sch,\n"
+ " t.name tbl,\n"
+ " c.name col,\n"
+ " c.\"default\" def\n"
+ " FROM schemas s, tables t, columns
c\n"
+ " WHERE\n"
+ " s.id = t.schema_id AND\n"
+ " t.id = c.table_id AND\n"
+ " s.name <> 'tmp' AND\n"
+ " NOT t.system AND\n"
+ " c.\"default\" IS NOT NULL;\n"
+ "\n"
+ "CREATE VIEW sys.describe_foreign_keys
AS\n"
+ " WITH action_type (id, act) AS
(VALUES\n"
+ " (0, 'NO ACTION'),\n"
+ " (1, 'CASCADE'),\n"
+ " (2, 'RESTRICT'),\n"
+ " (3, 'SET NULL'),\n"
+ " (4, 'SET DEFAULT'))\n"
+ " SELECT\n"
+ " fs.name fk_s,\n"
+ " fkt.name fk_t,\n"
+ " fkkc.name fk_c,\n"
+ " fkkc.nr o,\n"
+ " fkk.name fk,\n"
+ " ps.name pk_s,\n"
+ " pkt.name pk_t,\n"
+ " pkkc.name pk_c,\n"
+ " ou.act on_update,\n"
+ " od.act on_delete\n"
+ " FROM sys._tables fkt,\n"
+ " sys.objects fkkc,\n"
+ " sys.keys fkk,\n"
+ " sys._tables pkt,\n"
+ " sys.objects pkkc,\n"
+ " sys.keys pkk,\n"
+ " sys.schemas ps,\n"
+ " sys.schemas fs,\n"
+ " action_type ou,\n"
+ " action_type od\n"
+ " WHERE fkt.id = fkk.table_id\n"
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
