Changeset: 71315e925a9b for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/71315e925a9b
Modified Files:
sql/backends/monet5/sql_upgrades.c
Branch: default
Log Message:
Merge with Aug2024 branch.
diffs (truncated from 1206 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
@@ -363,1128 +363,6 @@ sql_drop_shp(Client c)
}
static str
-sql_update_jul2021(Client c, mvc *sql)
-{
- size_t bufsize = 65536, pos = 0;
- char *buf = NULL, *err = NULL;
- res_table *output = NULL;
- BAT *b = NULL;
- sql_schema *s = mvc_bind_schema(sql, "sys");
- sql_table *t;
-
- if ((buf = GDKmalloc(bufsize)) == NULL)
- throw(SQL, __func__, SQLSTATE(HY013) MAL_MALLOC_FAIL);
-
- /* if the keyword STREAM is in the list of keywords, upgrade */
- pos += snprintf(buf + pos, bufsize - pos,
- "select keyword from sys.keywords where
keyword = 'STREAM';\n");
- assert(pos < bufsize);
- if ((err = SQLstatementIntern(c, buf, "update", true, false, &output)))
- goto bailout;
- if ((b = BATdescriptor(output->cols[0].b))) {
- if (BATcount(b) == 1) {
- /* 20_vacuum.sql */
- pos += snprintf(buf + pos, bufsize - pos,
- "drop procedure
sys.shrink(string, string) cascade;\n"
- "drop procedure
sys.reuse(string, string) cascade;\n"
- "drop procedure
sys.vacuum(string, string) cascade;\n");
-
- /* 22_clients.sql */
- pos += snprintf(buf + pos, bufsize - pos,
- "create function
sys.current_sessionid() returns int\n"
- "external name
clients.current_sessionid;\n"
- "grant execute on
function sys.current_sessionid to public;\n"
- "update sys.functions
set system = true where system <> true and schema_id = 2000 and name =
'current_sessionid' and type = %d;\n", (int) F_FUNC);
-
- /* 25_debug.sql */
- pos += snprintf(buf + pos, bufsize - pos,
- "drop procedure
sys.flush_log() cascade;\n");
-
- pos += snprintf(buf + pos, bufsize - pos,
- "drop function
sys.deltas(string) cascade;\n"
- "drop function
sys.deltas(string, string) cascade;\n"
- "drop function
sys.deltas(string, string, string) cascade;\n");
- pos += snprintf(buf + pos, bufsize - pos,
- "create function
sys.deltas (\"schema\" string)\n"
- "returns table (\"id\"
int, \"segments\" bigint, \"all\" bigint, \"inserted\" bigint, \"updates\"
bigint, \"deletes\" bigint, \"level\" int)\n"
- "external name
\"sql\".\"deltas\";\n"
- "create function
sys.deltas (\"schema\" string, \"table\" string)\n"
- "returns table (\"id\"
int, \"segments\" bigint, \"all\" bigint, \"inserted\" bigint, \"updates\"
bigint, \"deletes\" bigint, \"level\" int)\n"
- "external name
\"sql\".\"deltas\";\n"
- "create function
sys.deltas (\"schema\" string, \"table\" string, \"column\" string)\n"
- "returns table (\"id\"
int, \"segments\" bigint, \"all\" bigint, \"inserted\" bigint, \"updates\"
bigint, \"deletes\" bigint, \"level\" int)\n"
- "external name
\"sql\".\"deltas\";\n"
- "update sys.functions
set system = true"
- " where schema_id =
2000 and name = 'deltas';\n");
-
- /* 26_sysmon */
- t = mvc_bind_table(sql, s, "queue");
- t->system = 0; /* make it non-system else the drop view
will fail */
-
- pos += snprintf(buf + pos, bufsize - pos,
- "drop view sys.queue
cascade;\n"
- "drop function
sys.queue cascade;\n"
- "create function
sys.queue()\n"
- "returns table(\n"
- "\"tag\" bigint,\n"
- "\"sessionid\" int,\n"
- "\"username\" string,\n"
- "\"started\"
timestamp,\n"
- "\"status\" string,\n"
- "\"query\" string,\n"
- "\"finished\"
timestamp,\n"
- "\"maxworkers\" int,\n"
- "\"footprint\" int\n"
- ")\n"
- "external name
sysmon.queue;\n"
- "grant execute on
function sys.queue to public;\n"
- "create view sys.queue
as select * from sys.queue();\n"
- "grant select on
sys.queue to public;\n");
- pos += snprintf(buf + pos, bufsize - pos,
- "update sys.functions
set system = true where system <> true and schema_id = 2000"
- " and name = 'queue'
and type = %d;\n", (int) F_UNION);
- pos += snprintf(buf + pos, bufsize - pos,
- "update sys._tables set
system = true where schema_id = 2000"
- " and name =
'queue';\n");
-
- /* fix up dependencies for function getproj4 (if it
exists) */
- pos += snprintf(buf + pos, bufsize - pos,
- "delete from
sys.dependencies d where d.depend_id = (select id from sys.functions where name
= 'getproj4' and schema_id = 2000) and id in (select id from sys._columns where
name not in ('proj4text', 'srid'));\n");
-
- /* 41_json.sql */
- pos += snprintf(buf + pos, bufsize - pos,
- "drop function
json.isobject(string) cascade;\n"
- "drop function
json.isarray(string) cascade;\n"
- "drop function
json.isvalid(json) cascade;\n"
- "create function
json.isvalid(js json)\n"
- "returns bool begin
return true; end;\n"
- "grant execute on
function json.isvalid(json) to public;\n"
- "update sys.functions
set system = true"
- " where schema_id =
(select id from sys.schemas where name = 'json')"
- " and name =
'isvalid';\n");
-
- /* 51_sys_schema_extensions, remove stream table
entries and update window function description */
- pos += snprintf(buf + pos, bufsize - pos,
- "ALTER TABLE sys.keywords SET READ
WRITE;\n"
- "DELETE FROM sys.keywords where keyword
= 'STREAM';\n"
- "INSERT INTO sys.keywords VALUES
('BIG'), ('LITTLE'), ('NATIVE'), ('ENDIAN'), ('CURRENT_SCHEMA'),
('CURRENT_TIMEZONE'), ('IMPRINTS'), ('ORDERED'), ('PATH'), ('ROLE'), ('ROW'),
('VALUE');\n"
- "ALTER TABLE sys.table_types SET READ
WRITE;\n"
- "DELETE FROM sys.table_types where
table_type_id = 4;\n"
- "ALTER TABLE sys.function_types SET
READ WRITE;\n"
- "UPDATE sys.function_types SET
function_type_keyword = 'WINDOW' WHERE function_type_id = 6;\n");
-
- /* 52_describe.sql */
- pos += snprintf(buf + pos, bufsize - pos,
- "CREATE FUNCTION
sys.describe_type(ctype string, digits integer, tscale integer)\n"
- " RETURNS string\n"
- "BEGIN\n"
- " RETURN\n"
- " CASE ctype\n"
- " WHEN 'bigint' THEN 'BIGINT'\n"
- " WHEN 'blob' THEN\n"
- " CASE digits\n"
- " WHEN 0 THEN 'BINARY LARGE
OBJECT'\n"
- " ELSE 'BINARY LARGE OBJECT('
|| digits || ')'\n"
- " END\n"
- " WHEN 'boolean' THEN 'BOOLEAN'\n"
- " WHEN 'char' THEN\n"
- " CASE digits\n"
- " WHEN 1 THEN 'CHARACTER'\n"
- " 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"
- " 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"
- " 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"
- " WHEN 'geometry' THEN\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"
- " CASE tscale\n"
- " WHEN 0 THEN ''\n"
- " ELSE ',' || tscale\n"
- " END || ')'\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"
- " CASE tscale\n"
- " WHEN 0 THEN ''\n"
- " ELSE ',' || tscale\n"
- " END || ')'\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"
- " CASE tscale\n"
- " WHEN 0 THEN ''\n"
- " ELSE ',' || tscale\n"
- " END || ')'\n"
- " WHEN 32 THEN
'GEOMETRY(GEOMETRYCOLLECTION' ||\n"
- " CASE tscale\n"
- " WHEN 0 THEN ''\n"
- " ELSE ',' || tscale\n"
- " END || ')'\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"
- " 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"
- " 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"
- " WHEN 'smallint' THEN
'SMALLINT'\n"
- " WHEN 'time' THEN\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"
- " WHEN 'timestamptz' THEN\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"
- " WHEN 'tinyint' THEN 'TINYINT'\n"
- " WHEN 'varchar' THEN 'CHARACTER
VARYING(' || digits || ')'\n"
- " ELSE\n"
- " CASE\n"
- " WHEN lower(ctype) = ctype
THEN upper(ctype)\n"
- " ELSE '\"' || ctype || '\"'\n"
- " END || CASE digits\n"
- " WHEN 0 THEN ''\n"
- " ELSE '(' || digits || CASE
tscale\n"
- " WHEN 0 THEN ''\n"
- " ELSE ',' || tscale\n"
- " END || ')'\n"
- " END\n"
- " END;\n"
- "END;\n"
- "CREATE FUNCTION sys.SQ (s STRING)
RETURNS STRING BEGIN RETURN ' ''' || sys.replace(s,'''','''''') || ''' ';
END;\n"
- "CREATE FUNCTION sys.DQ (s STRING)
RETURNS STRING BEGIN RETURN '\"' || sys.replace(s,'\"','\"\"') || '\"'; END;
--TODO: Figure out why this breaks with the space\n"
- "CREATE FUNCTION sys.FQN(s STRING, t
STRING) RETURNS STRING BEGIN RETURN sys.DQ(s) || '.' || sys.DQ(t); END;\n"
- "CREATE FUNCTION sys.ALTER_TABLE(s
STRING, t STRING) RETURNS STRING BEGIN RETURN 'ALTER TABLE ' || sys.FQN(s, t)
|| ' '; END;\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 sys.replace_first(stmt,
'(\\\\s*\"?' || sch || '\"?\\\\s*\\\\.|)\\\\s*\"?' || nme || '\"?\\\\s*', ' '
|| sys.FQN(sch, nme) || ' ', 'imsx');\n"
- "END;\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"
- "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"
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
