Changeset: b6c570c57a6a for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=b6c570c57a6a
Modified Files:
geom/sql/40_geom.sql
geom/sql/conformance/Tests/T3.stable.out.int128
sql/backends/monet5/sql_upgrades.c
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.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.int128
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.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.int128
Branch: default
Log Message:
Column coord_dimension of view geometry_columns is an integer.
diffs (truncated from 673 to 300 lines):
diff --git a/geom/sql/40_geom.sql b/geom/sql/40_geom.sql
--- a/geom/sql/40_geom.sql
+++ b/geom/sql/40_geom.sql
@@ -34,7 +34,7 @@ create view geometry_columns as
select t.schema_id,
t.name as f_table_name,
x.name as f_geometry_column,
- has_z(info)+has_m(info)+2 as coord_dimension,
+ cast(has_z(info)+has_m(info)+2 as integer) as
coord_dimension,
srid, get_type(info, 0) as type
from tables t, (
select name, table_id, type_digits AS info, type_scale
AS srid
diff --git a/geom/sql/conformance/Tests/T3.stable.out.int128
b/geom/sql/conformance/Tests/T3.stable.out.int128
--- a/geom/sql/conformance/Tests/T3.stable.out.int128
+++ b/geom/sql/conformance/Tests/T3.stable.out.int128
@@ -29,7 +29,7 @@ Ready.
#SELECT coord_dimension FROM geometry_columns WHERE f_table_name = 'streams';
% .geometry_columns # table_name
% coord_dimension # name
-% hugeint # type
+% int # type
% 1 # length
[ 2 ]
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
@@ -916,6 +916,54 @@ sql_update_default(Client c, mvc *sql)
return err; /* usually MAL_SUCCEED */
}
+static str
+sql_update_default_geom(Client c, mvc *sql, sql_table *t)
+{
+ size_t bufsize = 10000, pos = 0;
+ char *buf = GDKmalloc(bufsize), *err = NULL;
+ char *schema = stack_get_string(sql, "current_schema");
+
+ if( buf== NULL)
+ throw(SQL, "sql_update_jul2017", SQLSTATE(HY001)
MAL_MALLOC_FAIL);
+ pos += snprintf(buf + pos, bufsize - pos, "set schema \"sys\";\n");
+
+ t->system = 0;
+ pos += snprintf(buf + pos, bufsize - pos,
+ "drop view sys.geometry_columns;\n"
+ "create view geometry_columns as\n"
+ "\tselect e.value as f_table_catalog,\n"
+ "\t\ts.name as f_table_schema,\n"
+ "\t\ty.f_table_name, y.f_geometry_column,
y.coord_dimension, y.srid, y.type\n"
+ "\tfrom schemas s, environment e, (\n"
+ "\t\tselect t.schema_id,\n"
+ "\t\t\tt.name as f_table_name,\n"
+ "\t\t\tx.name as f_geometry_column,\n"
+ "\t\t\tcast(has_z(info)+has_m(info)+2 as integer) as
coord_dimension,\n"
+ "\t\t\tsrid, get_type(info, 0) as type\n"
+ "\t\tfrom tables t, (\n"
+ "\t\t\tselect name, table_id, type_digits AS info,
type_scale AS srid\n"
+ "\t\t\tfrom columns\n"
+ "\t\t\twhere type in ( select distinct sqlname from
types where systemname='wkb')\n"
+ "\t\t\t) as x\n"
+ "\t\twhere t.id=x.table_id\n"
+ "\t\t) y\n"
+ "\twhere y.schema_id=s.id and e.name='gdk_dbname';\n"
+ "GRANT SELECT ON geometry_columns TO PUBLIC;\n"
+ "update sys._tables set system = true where name in
('geometry_columns') and schema_id = (select id from schemas where name =
'sys');\n");
+
+ pos += snprintf(buf + pos, bufsize - pos,
+ "delete from sys.systemfunctions where function_id not
in (select id from sys.functions);\n");
+
+ if (schema)
+ pos += snprintf(buf + pos, bufsize - pos, "set schema
\"%s\";\n", schema);
+
+ assert(pos < bufsize);
+ printf("Running database upgrade commands:\n%s\n", buf);
+ err = SQLstatementIntern(c, &buf, "update", 1, 0, NULL);
+ GDKfree(buf);
+ return err; /* usually MAL_SUCCEED */
+}
+
void
SQLupgrades(Client c, mvc *m)
{
@@ -923,6 +971,8 @@ SQLupgrades(Client c, mvc *m)
sql_subfunc *f;
char *err;
sql_schema *s = mvc_bind_schema(m, "sys");
+ sql_table *t;
+ sql_column *col;
#ifdef HAVE_HGE
if (have_hge) {
@@ -1016,4 +1066,12 @@ SQLupgrades(Client c, mvc *m)
}
}
+ if ((t = mvc_bind_table(m, s, "geometry_columns")) != NULL &&
+ (col = mvc_bind_column(m, t, "coord_dimension")) != NULL &&
+ strcmp(col->type.type->sqlname, "int") != 0) {
+ if ((err = sql_update_default_geom(c, m, t)) != NULL) {
+ fprintf(stderr, "!%s\n", err);
+ freeException(err);
+ }
+ }
}
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
@@ -76,6 +76,32 @@ insert into sys.systemfunctions (select
delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
set schema "sys";
+Running database upgrade commands:
+set schema "sys";
+drop view sys.geometry_columns;
+create view geometry_columns as
+ select e.value as f_table_catalog,
+ s.name as f_table_schema,
+ y.f_table_name, y.f_geometry_column, y.coord_dimension, y.srid,
y.type
+ from schemas s, environment e, (
+ select t.schema_id,
+ t.name as f_table_name,
+ x.name as f_geometry_column,
+ cast(has_z(info)+has_m(info)+2 as integer) as
coord_dimension,
+ srid, get_type(info, 0) as type
+ from tables t, (
+ select name, table_id, type_digits AS info, type_scale
AS srid
+ from columns
+ where type in ( select distinct sqlname from types
where systemname='wkb')
+ ) as x
+ where t.id=x.table_id
+ ) y
+ where y.schema_id=s.id and e.name='gdk_dbname';
+GRANT SELECT ON geometry_columns TO PUBLIC;
+update sys._tables set system = true where name in ('geometry_columns') and
schema_id = (select id from schemas where name = 'sys');
+delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
+set schema "sys";
+
# 13:50:24 >
# 13:50:24 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-7858" "--port=33066"
diff --git a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
@@ -76,6 +76,32 @@ insert into sys.systemfunctions (select
delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
set schema "sys";
+Running database upgrade commands:
+set schema "sys";
+drop view sys.geometry_columns;
+create view geometry_columns as
+ select e.value as f_table_catalog,
+ s.name as f_table_schema,
+ y.f_table_name, y.f_geometry_column, y.coord_dimension, y.srid,
y.type
+ from schemas s, environment e, (
+ select t.schema_id,
+ t.name as f_table_name,
+ x.name as f_geometry_column,
+ cast(has_z(info)+has_m(info)+2 as integer) as
coord_dimension,
+ srid, get_type(info, 0) as type
+ from tables t, (
+ select name, table_id, type_digits AS info, type_scale
AS srid
+ from columns
+ where type in ( select distinct sqlname from types
where systemname='wkb')
+ ) as x
+ where t.id=x.table_id
+ ) y
+ where y.schema_id=s.id and e.name='gdk_dbname';
+GRANT SELECT ON geometry_columns TO PUBLIC;
+update sys._tables set system = true where name in ('geometry_columns') and
schema_id = (select id from schemas where name = 'sys');
+delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
+set schema "sys";
+
# 14:06:25 >
# 14:06:25 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-32766" "--port=33975"
diff --git a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
@@ -5401,6 +5401,32 @@ insert into sys.systemfunctions (select
delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
set schema "sys";
+Running database upgrade commands:
+set schema "sys";
+drop view sys.geometry_columns;
+create view geometry_columns as
+ select e.value as f_table_catalog,
+ s.name as f_table_schema,
+ y.f_table_name, y.f_geometry_column, y.coord_dimension, y.srid,
y.type
+ from schemas s, environment e, (
+ select t.schema_id,
+ t.name as f_table_name,
+ x.name as f_geometry_column,
+ cast(has_z(info)+has_m(info)+2 as integer) as
coord_dimension,
+ srid, get_type(info, 0) as type
+ from tables t, (
+ select name, table_id, type_digits AS info, type_scale
AS srid
+ from columns
+ where type in ( select distinct sqlname from types
where systemname='wkb')
+ ) as x
+ where t.id=x.table_id
+ ) y
+ where y.schema_id=s.id and e.name='gdk_dbname';
+GRANT SELECT ON geometry_columns TO PUBLIC;
+update sys._tables set system = true where name in ('geometry_columns') and
schema_id = (select id from schemas where name = 'sys');
+delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
+set schema "sys";
+
# 16:53:35 >
# 16:53:35 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-30908" "--port=39660"
diff --git a/sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
@@ -76,6 +76,32 @@ insert into sys.systemfunctions (select
delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
set schema "sys";
+Running database upgrade commands:
+set schema "sys";
+drop view sys.geometry_columns;
+create view geometry_columns as
+ select e.value as f_table_catalog,
+ s.name as f_table_schema,
+ y.f_table_name, y.f_geometry_column, y.coord_dimension, y.srid,
y.type
+ from schemas s, environment e, (
+ select t.schema_id,
+ t.name as f_table_name,
+ x.name as f_geometry_column,
+ cast(has_z(info)+has_m(info)+2 as integer) as
coord_dimension,
+ srid, get_type(info, 0) as type
+ from tables t, (
+ select name, table_id, type_digits AS info, type_scale
AS srid
+ from columns
+ where type in ( select distinct sqlname from types
where systemname='wkb')
+ ) as x
+ where t.id=x.table_id
+ ) y
+ where y.schema_id=s.id and e.name='gdk_dbname';
+GRANT SELECT ON geometry_columns TO PUBLIC;
+update sys._tables set system = true where name in ('geometry_columns') and
schema_id = (select id from schemas where name = 'sys');
+delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
+set schema "sys";
+
# 13:50:25 >
# 13:50:25 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-7858" "--port=33066"
diff --git a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out
b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out
--- a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out
@@ -76,6 +76,32 @@ insert into sys.systemfunctions (select
delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
set schema "sys";
+Running database upgrade commands:
+set schema "sys";
+drop view sys.geometry_columns;
+create view geometry_columns as
+ select e.value as f_table_catalog,
+ s.name as f_table_schema,
+ y.f_table_name, y.f_geometry_column, y.coord_dimension, y.srid,
y.type
+ from schemas s, environment e, (
+ select t.schema_id,
+ t.name as f_table_name,
+ x.name as f_geometry_column,
+ cast(has_z(info)+has_m(info)+2 as integer) as
coord_dimension,
+ srid, get_type(info, 0) as type
+ from tables t, (
+ select name, table_id, type_digits AS info, type_scale
AS srid
+ from columns
+ where type in ( select distinct sqlname from types
where systemname='wkb')
+ ) as x
+ where t.id=x.table_id
+ ) y
+ where y.schema_id=s.id and e.name='gdk_dbname';
+GRANT SELECT ON geometry_columns TO PUBLIC;
+update sys._tables set system = true where name in ('geometry_columns') and
schema_id = (select id from schemas where name = 'sys');
+delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
+set schema "sys";
+
# 14:06:25 >
# 14:06:25 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-32766" "--port=33975"
diff --git a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
@@ -5401,6 +5401,32 @@ insert into sys.systemfunctions (select
delete from sys.systemfunctions where function_id not in (select id from
sys.functions);
set schema "sys";
+Running database upgrade commands:
+set schema "sys";
+drop view sys.geometry_columns;
+create view geometry_columns as
+ select e.value as f_table_catalog,
+ s.name as f_table_schema,
+ y.f_table_name, y.f_geometry_column, y.coord_dimension, y.srid,
y.type
+ from schemas s, environment e, (
+ select t.schema_id,
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
