Changeset: 9af3c1d02f06 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/9af3c1d02f06
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.32bit
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.ppc64
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.ppc64.int128
sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.32bit
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
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.stable.out.int128
sql/test/testdb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out.32bit
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out
sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out.32bit
sql/test/testdb-previous-upgrade/Tests/upgrade.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:
Properly upgrade for UNLOGGED TABLEs.
diffs (truncated from 17102 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
@@ -2542,7 +2542,7 @@ sql_update_jul2021(Client c, mvc *sql)
" ''\n"
" END opt\n"
" FROM sys.schemas s,
sys.table_types ts, sys.tables t\n"
- " WHERE ts.table_type_name IN
('TABLE', 'VIEW', 'MERGE TABLE', 'REMOTE TABLE', 'REPLICA TABLE', 'UNLOGGED
TABLE')\n"
+ " WHERE ts.table_type_name IN
('TABLE', 'VIEW', 'MERGE TABLE', 'REMOTE TABLE', 'REPLICA TABLE')\n"
" AND t.system = FALSE\n"
" AND s.id = t.schema_id\n"
" AND ts.table_type_id =
t.type\n"
@@ -3545,7 +3545,7 @@ sql_update_jan2022(Client c, mvc *sql)
" ''\n"
" END opt\n"
" FROM sys.schemas s,
sys.table_types ts, sys.tables t\n"
- " WHERE ts.table_type_name IN
('TABLE', 'VIEW', 'MERGE TABLE', 'REMOTE TABLE', 'REPLICA TABLE', 'UNLOGGED
TABLE')\n"
+ " WHERE ts.table_type_name IN
('TABLE', 'VIEW', 'MERGE TABLE', 'REMOTE TABLE', 'REPLICA TABLE')\n"
" AND t.system = FALSE\n"
" AND s.id =
t.schema_id\n"
" AND ts.table_type_id =
t.type\n"
@@ -4620,6 +4620,10 @@ sql_update_default(Client c, mvc *sql)
t->system = 0;
t = mvc_bind_table(sql, s, "dump_start_sequences");
t->system = 0;
+ t = mvc_bind_table(sql, s, "describe_tables");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_tables");
+ t->system = 0;
pos = 0;
pos += snprintf(buf + pos, bufsize - pos,
@@ -4630,203 +4634,244 @@ sql_update_default(Client c, mvc *sql)
"drop view sys.dump_partition_tables;\n"
"drop view sys.describe_partition_tables;\n"
"drop view sys.dump_sequences;\n"
- "drop view sys.dump_start_sequences;\n");
+ "drop view sys.dump_start_sequences;\n"
+ "drop view sys.dump_tables;\n"
+ "drop view sys.describe_tables;\n");
pos += snprintf(buf + pos, bufsize - pos,
"CREATE VIEW sys.describe_partition_tables AS\n"
- " SELECT\n"
- " m_sch,\n"
- " m_tbl,\n"
- " p_sch,\n"
- " p_tbl,\n"
- " CASE\n"
- " WHEN p_raw_type IS NULL THEN
'READ ONLY'\n"
- " 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'\n"
- " ELSE p_raw_type\n"
- " END AS tpe,\n"
- " pvalues,\n"
- " minimum,\n"
- " maximum,\n"
- " with_nulls\n"
- " FROM\n"
- " (WITH\n"
- " tp(\"type\", table_id) AS\n"
- " (SELECT
ifthenelse((table_partitions.\"type\" & 2) = 2, 'VALUES', 'RANGE'),
table_partitions.table_id FROM sys.table_partitions),\n"
- " subq(m_tid, p_mid, \"type\", m_sch,
m_tbl, p_sch, p_tbl) AS\n"
- " (SELECT m_t.id, p_m.id, m_t.\"type\",
m_s.name, m_t.name, p_s.name, p_m.name\n"
- " FROM sys.schemas m_s, sys._tables m_t,
sys.dependencies d, sys.schemas p_s, sys._tables p_m\n"
- " WHERE m_t.\"type\" IN (3, 6)\n"
- " AND m_t.schema_id = m_s.id\n"
- " AND m_s.name <> 'tmp'\n"
- " AND m_t.system = FALSE\n"
- " AND m_t.id = d.depend_id\n"
- " AND d.id = p_m.id\n"
- " AND p_m.schema_id = p_s.id\n"
- " ORDER BY m_t.id, p_m.id),\n"
- " vals(id,vals) as\n"
- " (SELECT vp.table_id,
GROUP_CONCAT(vp.value, ',') FROM sys.value_partitions vp GROUP BY
vp.table_id)\n"
- " SELECT\n"
- " subq.m_sch,\n"
- " subq.m_tbl,\n"
- " subq.p_sch,\n"
- " subq.p_tbl,\n"
- " tp.\"type\" AS p_raw_type,\n"
- " CASE WHEN tp.\"type\" = 'VALUES'\n"
- " THEN (SELECT vals.vals FROM
vals WHERE vals.id = subq.p_mid)\n"
- " ELSE NULL\n"
- " END AS pvalues,\n"
- " CASE WHEN tp.\"type\" = 'RANGE'\n"
- " THEN (SELECT minimum FROM
sys.range_partitions rp WHERE rp.table_id = subq.p_mid)\n"
- " ELSE NULL\n"
- " END AS minimum,\n"
- " CASE WHEN tp.\"type\" = 'RANGE'\n"
- " THEN (SELECT maximum FROM
sys.range_partitions rp WHERE rp.table_id = subq.p_mid)\n"
- " ELSE NULL\n"
- " END AS maximum,\n"
- " CASE WHEN tp.\"type\" = 'VALUES'\n"
- " THEN EXISTS(SELECT vp.value
FROM sys.value_partitions vp WHERE vp.table_id = subq.p_mid AND vp.value IS
NULL)\n"
- " ELSE (SELECT rp.with_nulls FROM
sys.range_partitions rp WHERE rp.table_id = subq.p_mid)\n"
- " END AS with_nulls\n"
- " FROM\n"
- " subq LEFT OUTER JOIN tp\n"
- " ON subq.m_tid = tp.table_id) AS
tmp_pi;\n"
+ " SELECT\n"
+ " m_sch,\n"
+ " m_tbl,\n"
+ " p_sch,\n"
+ " p_tbl,\n"
+ " CASE\n"
+ " WHEN p_raw_type IS NULL THEN 'READ ONLY'\n"
+ " 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'\n"
+ " ELSE p_raw_type\n"
+ " END AS tpe,\n"
+ " pvalues,\n"
+ " minimum,\n"
+ " maximum,\n"
+ " with_nulls\n"
+ " FROM\n"
+ " (WITH\n"
+ " tp(\"type\", table_id) AS\n"
+ " (SELECT ifthenelse((table_partitions.\"type\" & 2) =
2, 'VALUES', 'RANGE'), table_partitions.table_id FROM sys.table_partitions),\n"
+ " subq(m_tid, p_mid, \"type\", m_sch, m_tbl, p_sch,
p_tbl) AS\n"
+ " (SELECT m_t.id, p_m.id, m_t.\"type\", m_s.name,
m_t.name, p_s.name, p_m.name\n"
+ " FROM sys.schemas m_s, sys._tables m_t,
sys.dependencies d, sys.schemas p_s, sys._tables p_m\n"
+ " WHERE m_t.\"type\" IN (3, 6)\n"
+ " AND m_t.schema_id = m_s.id\n"
+ " AND m_s.name <> 'tmp'\n"
+ " AND m_t.system = FALSE\n"
+ " AND m_t.id = d.depend_id\n"
+ " AND d.id = p_m.id\n"
+ " AND p_m.schema_id = p_s.id\n"
+ " ORDER BY m_t.id, p_m.id),\n"
+ " vals(id,vals) as\n"
+ " (SELECT vp.table_id, GROUP_CONCAT(vp.value, ',') FROM
sys.value_partitions vp GROUP BY vp.table_id)\n"
+ " SELECT\n"
+ " subq.m_sch,\n"
+ " subq.m_tbl,\n"
+ " subq.p_sch,\n"
+ " subq.p_tbl,\n"
+ " tp.\"type\" AS p_raw_type,\n"
+ " CASE WHEN tp.\"type\" = 'VALUES'\n"
+ " THEN (SELECT vals.vals FROM vals WHERE vals.id =
subq.p_mid)\n"
+ " ELSE NULL\n"
+ " END AS pvalues,\n"
+ " CASE WHEN tp.\"type\" = 'RANGE'\n"
+ " THEN (SELECT minimum FROM sys.range_partitions rp
WHERE rp.table_id = subq.p_mid)\n"
+ " ELSE NULL\n"
+ " END AS minimum,\n"
+ " CASE WHEN tp.\"type\" = 'RANGE'\n"
+ " THEN (SELECT maximum FROM sys.range_partitions rp
WHERE rp.table_id = subq.p_mid)\n"
+ " ELSE NULL\n"
+ " END AS maximum,\n"
+ " CASE WHEN tp.\"type\" = 'VALUES'\n"
+ " THEN EXISTS(SELECT vp.value FROM sys.value_partitions
vp WHERE vp.table_id = subq.p_mid AND vp.value IS NULL)\n"
+ " ELSE (SELECT rp.with_nulls FROM sys.range_partitions
rp WHERE rp.table_id = subq.p_mid)\n"
+ " END AS with_nulls\n"
+ " FROM\n"
+ " subq LEFT OUTER JOIN tp\n"
+ " ON subq.m_tid = tp.table_id) AS tmp_pi;\n"
"GRANT SELECT ON sys.describe_partition_tables TO
PUBLIC;\n"
"CREATE VIEW sys.dump_partition_tables AS\n"
"SELECT\n"
- " 'ALTER TABLE ' || sys.FQN(m_sch, m_tbl) || '
ADD TABLE ' || sys.FQN(p_sch, p_tbl) ||\n"
- " CASE\n"
- " WHEN tpe = 'VALUES' THEN ' AS PARTITION IN ('
|| pvalues || ')'\n"
- " WHEN tpe = 'RANGE' THEN ' AS PARTITION FROM '
|| ifthenelse(minimum IS NOT NULL, sys.SQ(minimum), 'RANGE MINVALUE') || ' TO '
|| ifthenelse(maximum IS NOT NULL, sys.SQ(maximum), 'RANGE MAXVALUE')\n"
- " WHEN tpe = 'FOR NULLS' THEN ' AS PARTITION FOR
NULL VALUES'\n"
- " ELSE '' --'READ ONLY'\n"
- " END ||\n"
- " CASE WHEN tpe in ('VALUES', 'RANGE') AND
with_nulls THEN ' WITH NULL VALUES' ELSE '' END ||\n"
- " ';' stmt,\n"
- " m_sch merge_schema_name,\n"
- " m_tbl merge_table_name,\n"
- " p_sch partition_schema_name,\n"
- " p_tbl partition_table_name\n"
- " FROM sys.describe_partition_tables;\n"
+ " 'ALTER TABLE ' || sys.FQN(m_sch, m_tbl) || ' ADD
TABLE ' || sys.FQN(p_sch, p_tbl) ||\n"
+ " CASE\n"
+ " WHEN tpe = 'VALUES' THEN ' AS PARTITION IN (' ||
pvalues || ')'\n"
+ " WHEN tpe = 'RANGE' THEN ' AS PARTITION FROM ' ||
ifthenelse(minimum IS NOT NULL, sys.SQ(minimum), 'RANGE MINVALUE') || ' TO ' ||
ifthenelse(maximum IS NOT NULL, sys.SQ(maximum), 'RANGE MAXVALUE')\n"
+ " WHEN tpe = 'FOR NULLS' THEN ' AS PARTITION FOR NULL
VALUES'\n"
+ " ELSE '' --'READ ONLY'\n"
+ " END ||\n"
+ " CASE WHEN tpe in ('VALUES', 'RANGE') AND with_nulls
THEN ' WITH NULL VALUES' ELSE '' END ||\n"
+ " ';' stmt,\n"
+ " m_sch merge_schema_name,\n"
+ " m_tbl merge_table_name,\n"
+ " p_sch partition_schema_name,\n"
+ " p_tbl partition_table_name\n"
+ " FROM sys.describe_partition_tables;\n"
"CREATE VIEW sys.dump_sequences AS\n"
- " SELECT\n"
- " 'CREATE SEQUENCE ' || sys.FQN(sch, seq) || ' AS
BIGINT;' stmt,\n"
- " sch schema_name,\n"
- " seq seqname\n"
- " FROM sys.describe_sequences;\n"
+ " SELECT\n"
+ " 'CREATE SEQUENCE ' || sys.FQN(sch, seq) || ' AS
BIGINT;' stmt,\n"
+ " sch schema_name,\n"
+ " seq seqname\n"
+ " FROM sys.describe_sequences;\n"
"CREATE VIEW sys.dump_start_sequences AS\n"
- " SELECT 'ALTER SEQUENCE ' || sys.FQN(sch, seq) ||\n"
- " CASE WHEN s = 0 THEN '' ELSE ' RESTART WITH
' || rs END ||\n"
- " CASE WHEN inc = 1 THEN '' ELSE ' INCREMENT
BY ' || inc END ||\n"
- " CASE WHEN nomin THEN ' NO MINVALUE' WHEN rmi
IS NULL THEN '' ELSE ' MINVALUE ' || rmi END ||\n"
- " CASE WHEN nomax THEN ' NO MAXVALUE' WHEN rma
IS NULL THEN '' ELSE ' MAXVALUE ' || rma END ||\n"
- " CASE WHEN \"cache\" = 1 THEN '' ELSE ' CACHE
' || \"cache\" END ||\n"
- " CASE WHEN \"cycle\" THEN '' ELSE ' NO' END
|| ' CYCLE;' stmt,\n"
- " sch schema_name,\n"
- " seq sequence_name\n"
- " FROM sys.describe_sequences;\n"
+ " SELECT 'ALTER SEQUENCE ' || sys.FQN(sch, seq) ||\n"
+ " CASE WHEN s = 0 THEN '' ELSE ' RESTART WITH ' || rs
END ||\n"
+ " CASE WHEN inc = 1 THEN '' ELSE ' INCREMENT BY ' ||
inc END ||\n"
+ " CASE WHEN nomin THEN ' NO MINVALUE' WHEN rmi IS NULL
THEN '' ELSE ' MINVALUE ' || rmi END ||\n"
+ " CASE WHEN nomax THEN ' NO MAXVALUE' WHEN rma IS NULL
THEN '' ELSE ' MAXVALUE ' || rma END ||\n"
+ " CASE WHEN \"cache\" = 1 THEN '' ELSE ' CACHE ' ||
\"cache\" END ||\n"
+ " CASE WHEN \"cycle\" THEN '' ELSE ' NO' END || '
CYCLE;' stmt,\n"
+ " sch schema_name,\n"
+ " seq sequence_name\n"
+ " FROM sys.describe_sequences;\n"
"CREATE PROCEDURE sys.dump_table_data(sch STRING, tbl
STRING)\n"
"BEGIN\n"
- " DECLARE tid INT;\n"
- " SET tid = (SELECT MIN(t.id) FROM sys.tables t,
sys.schemas s WHERE t.name = tbl AND t.schema_id = s.id AND s.name = sch);\n"
- " IF tid IS NOT NULL THEN\n"
- " DECLARE k INT;\n"
- " DECLARE m INT;\n"
- " SET k = (SELECT MIN(c.id) FROM sys.columns c WHERE
c.table_id = tid);\n"
- " SET m = (SELECT MAX(c.id) FROM sys.columns c WHERE
c.table_id = tid);\n"
- " IF k IS NOT NULL AND m IS NOT NULL THEN\n"
- " DECLARE cname STRING;\n"
- " DECLARE ctype STRING;\n"
- " DECLARE _cnt INT;\n"
- " SET cname = (SELECT c.name FROM sys.columns c
WHERE c.id = k);\n"
- " SET ctype = (SELECT c.type FROM sys.columns c
WHERE c.id = k);\n"
- " SET _cnt = (SELECT count FROM sys.storage(sch,
tbl, cname));\n"
- " IF _cnt > 0 THEN\n"
- " DECLARE COPY_INTO_STMT STRING;\n"
- " DECLARE SELECT_DATA_STMT STRING;\n"
- " SET COPY_INTO_STMT = 'COPY ' || _cnt || '
RECORDS INTO ' || sys.FQN(sch, tbl) || '(' || sys.DQ(cname);\n"
- " SET SELECT_DATA_STMT = 'SELECT (SELECT
COUNT(*) FROM sys.dump_statements) + RANK() OVER(), ' || sys.prepare_esc(cname,
ctype);\n"
- " WHILE (k < m) DO\n"
- " SET k = (SELECT MIN(c.id) FROM sys.columns c
WHERE c.table_id = tid AND c.id > k);\n"
- " SET cname = (SELECT c.name FROM sys.columns
c WHERE c.id = k);\n"
- " SET ctype = (SELECT c.type FROM sys.columns
c WHERE c.id = k);\n"
- " SET COPY_INTO_STMT = (COPY_INTO_STMT || ', '
|| sys.DQ(cname));\n"
- " SET SELECT_DATA_STMT = (SELECT_DATA_STMT ||
'|| ''|'' || ' || sys.prepare_esc(cname, ctype));\n"
- " END WHILE;\n"
- " SET COPY_INTO_STMT = (COPY_INTO_STMT || ')
FROM STDIN USING DELIMITERS ''|'',E''\\\\n'',''\"'';');\n"
- " SET SELECT_DATA_STMT = (SELECT_DATA_STMT || '
FROM ' || sys.FQN(sch, tbl));\n"
- " INSERT INTO sys.dump_statements VALUES
((SELECT COUNT(*) FROM sys.dump_statements) + 1, COPY_INTO_STMT);\n"
- " CALL sys.EVAL('INSERT INTO sys.dump_statements
' || SELECT_DATA_STMT || ';');\n"
- " END IF;\n"
- " END IF;\n"
- " END IF;\n"
+ " DECLARE tid INT;\n"
+ " SET tid = (SELECT MIN(t.id) FROM sys.tables t,
sys.schemas s WHERE t.name = tbl AND t.schema_id = s.id AND s.name = sch);\n"
+ " IF tid IS NOT NULL THEN\n"
+ " DECLARE k INT;\n"
+ " DECLARE m INT;\n"
+ " SET k = (SELECT MIN(c.id) FROM sys.columns c WHERE
c.table_id = tid);\n"
+ " SET m = (SELECT MAX(c.id) FROM sys.columns c WHERE
c.table_id = tid);\n"
+ " IF k IS NOT NULL AND m IS NOT NULL THEN\n"
+ " DECLARE cname STRING;\n"
+ " DECLARE ctype STRING;\n"
+ " DECLARE _cnt INT;\n"
+ " SET cname = (SELECT c.name FROM sys.columns c WHERE
c.id = k);\n"
+ " SET ctype = (SELECT c.type FROM sys.columns c WHERE
c.id = k);\n"
+ " SET _cnt = (SELECT count FROM sys.storage(sch, tbl,
cname));\n"
+ " IF _cnt > 0 THEN\n"
+ " DECLARE COPY_INTO_STMT STRING;\n"
+ " DECLARE SELECT_DATA_STMT STRING;\n"
+ " SET COPY_INTO_STMT = 'COPY ' || _cnt || ' RECORDS
INTO ' || sys.FQN(sch, tbl) || '(' || sys.DQ(cname);\n"
+ " SET SELECT_DATA_STMT = 'SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), ' || sys.prepare_esc(cname, ctype);\n"
+ " WHILE (k < m) DO\n"
+ " SET k = (SELECT MIN(c.id) FROM sys.columns c WHERE
c.table_id = tid AND c.id > k);\n"
+ " SET cname = (SELECT c.name FROM sys.columns c WHERE
c.id = k);\n"
+ " SET ctype = (SELECT c.type FROM sys.columns c WHERE
c.id = k);\n"
+ " SET COPY_INTO_STMT = (COPY_INTO_STMT || ', ' ||
sys.DQ(cname));\n"
+ " SET SELECT_DATA_STMT = (SELECT_DATA_STMT || '|| ''|''
|| ' || sys.prepare_esc(cname, ctype));\n"
+ " END WHILE;\n"
+ " SET COPY_INTO_STMT = (COPY_INTO_STMT || ') FROM STDIN
USING DELIMITERS ''|'',E''\\\\n'',''\"'';');\n"
+ " SET SELECT_DATA_STMT = (SELECT_DATA_STMT || ' FROM '
|| sys.FQN(sch, tbl));\n"
+ " INSERT INTO sys.dump_statements VALUES ((SELECT
COUNT(*) FROM sys.dump_statements) + 1, COPY_INTO_STMT);\n"
+ " CALL sys.EVAL('INSERT INTO sys.dump_statements ' ||
SELECT_DATA_STMT || ';');\n"
+ " END IF;\n"
+ " END IF;\n"
+ " END IF;\n"
"END;\n"
"CREATE PROCEDURE sys.dump_table_data()\n"
"BEGIN\n"
- " DECLARE i INT;\n"
- " SET i = (SELECT MIN(t.id) FROM sys.tables t,
sys.table_types ts WHERE t.type = ts.table_type_id AND ts.table_type_name =
'TABLE' AND NOT t.system);\n"
- " IF i IS NOT NULL THEN\n"
- " DECLARE M INT;\n"
- " SET M = (SELECT MAX(t.id) FROM sys.tables t,
sys.table_types ts WHERE t.type = ts.table_type_id AND ts.table_type_name =
'TABLE' AND NOT t.system);\n"
- " DECLARE sch STRING;\n"
- " DECLARE tbl STRING;\n"
- " WHILE i IS NOT NULL AND i <= M DO\n"
- " SET sch = (SELECT s.name FROM sys.tables t,
sys.schemas s WHERE s.id = t.schema_id AND t.id = i);\n"
- " SET tbl = (SELECT t.name FROM sys.tables t,
sys.schemas s WHERE s.id = t.schema_id AND t.id = i);\n"
- " CALL sys.dump_table_data(sch, tbl);\n"
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
