Changeset: 27024ece68fa for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=27024ece68fa
Modified Files:
dump.sql
dump_output.sql
Branch: monetdbe-proxy
Log Message:
Set column default values with ALTER TABLE statement to prevent dependency
issues.
diffs (76 lines):
diff --git a/dump.sql b/dump.sql
--- a/dump.sql
+++ b/dump.sql
@@ -151,8 +151,7 @@ CREATE FUNCTION dump_column_definition(t
GROUP_CONCAT(
DQ(c.name) || ' ' ||
dump_type(c.type, c.type_digits, c.type_scale)
||
- ifthenelse(c."null" = 'false', ' NOT NULL', '')
||
- ifthenelse(c."default" IS NOT NULL, ' DEFAULT '
|| c."default", '')
+ ifthenelse(c."null" = 'false', ' NOT NULL', '')
, ', ') || ')'
FROM sys._columns c
WHERE c.table_id = tid;
@@ -184,6 +183,28 @@ BEGIN
FROM (VALUES (tid)) t(id) LEFT JOIN sys.table_partitions tp ON
t.id = tp.table_id;
END;
+CREATE FUNCTION describe_column_defaults() RETURNS TABLE(sch STRING, tbl
STRING, col STRING, def STRING) BEGIN
+RETURN
+ SELECT
+ s.name,
+ t.name,
+ c.name,
+ c."default"
+ 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;
+END;
+
+CREATE FUNCTION dump_column_defaults() RETURNS TABLE(stmt STRING) BEGIN
+ RETURN
+ SELECT 'ALTER TABLE ' || FQTN(sch, tbl) || ' ALTER COLUMN ' ||
DQ(col) || ' SET DEFAULT ' || def || ';'
+ FROM describe_column_defaults();
+END;
+
--SELECT * FROM dump_foreign_keys();
CREATE FUNCTION describe_foreign_keys() RETURNS TABLE(
fk_s STRING, fk_t STRING, fk_c STRING,
@@ -493,6 +514,7 @@ BEGIN
SET dummy_result = restart_sequence('tmp', '_auto_increment', offs);
--END OF COMPLICATED DEPENDENCY STUFF.
+ INSERT INTO dump_statements(s) SELECT * FROM dump_column_defaults();
INSERT INTO dump_statements(s) SELECT * FROM
dump_table_constraint_type();
INSERT INTO dump_statements(s) SELECT * FROM dump_indices();
INSERT INTO dump_statements(s) SELECT * FROM dump_foreign_keys();
@@ -520,5 +542,4 @@ END;
CALL dump_database(TRUE);
SELECT s FROM dump_statements order by o;
-
ROLLBACK;
diff --git a/dump_output.sql b/dump_output.sql
--- a/dump_output.sql
+++ b/dump_output.sql
@@ -21,7 +21,7 @@ CREATE TABLE "sys"."test" ("s" CHARACTER
CREATE TABLE "sys"."bla" ("s" CHARACTER LARGE OBJECT(10));
CREATE TABLE "sys"."bob" ("ts" TIMESTAMP(3) );
CREATE TABLE "sys"."fdgdf" ("tsz" TIMESTAMP(4) WITH TIME ZONE);
-CREATE TABLE "sys"."yoyo" ("tsz" CHARACTER LARGE OBJECT DEFAULT 'BLABOLO');
+CREATE TABLE "sys"."yoyo" ("tsz" CHARACTER LARGE OBJECT);
CREATE TABLE "sys"."bolo" ("s" CHARACTER LARGE OBJECT NOT NULL, "v" CHARACTER
LARGE OBJECT NOT NULL);
CREATE TABLE "sys"."rolo" ("v" CHARACTER LARGE OBJECT NOT NULL);
CREATE TABLE "sys"."ungolo" ("x" INTEGER, "y" INTEGER, "z" INTEGER);
@@ -45,6 +45,7 @@ CREATE TABLE "sys"."first_decade" ("stam
CREATE TABLE "sys"."second_decade" ("stamp" TIMESTAMP , "val" INTEGER);
CREATE TABLE "sys"."third_decade" ("stamp" TIMESTAMP , "val" INTEGER);
CREATE TABLE "sys"."p1" ("i" INTEGER);
+ALTER TABLE "sys"."yoyo" ALTER COLUMN "tsz" SET DEFAULT 'BLABOLO';
ALTER TABLE "sys"."bolo" ADD CONSTRAINT "cpk" PRIMARY KEY ("s", "v");
ALTER TABLE "sys"."rolo" ADD CONSTRAINT "rolo_v_pkey" PRIMARY KEY ("v");
ALTER TABLE "sys"."ungolo" ADD CONSTRAINT "ungolo_x_y_unique" UNIQUE ("x",
"y");
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
